<?php
/**
 * Complete fix for my_students.php showing no students
 * This script ensures all components are working properly
 */

require_once 'config/database.php';
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<h1>Complete My Students Fix</h1>";
echo "<p>my_students.php এর সমস্ত সমস্যা সমাধান করা হচ্ছে...</p>";

try {
    // Step 1: Ensure class_teachers table exists
    echo "<h2>Step 1: Checking class_teachers table</h2>";
    $stmt = $pdo->query("SHOW TABLES LIKE 'class_teachers'");
    $table_exists = $stmt->fetch();

    if (!$table_exists) {
        echo "<p style='color: orange;'>⚠️ class_teachers table missing. Creating...</p>";
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS class_teachers (
                id INT AUTO_INCREMENT PRIMARY KEY,
                class_id INT NOT NULL,
                teacher_id INT NOT NULL,
                assignment_type ENUM('primary', 'assistant', 'subject') DEFAULT 'primary',
                assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                assigned_by INT,
                status ENUM('active', 'inactive') DEFAULT 'active',
                FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
                FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
                FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL,
                UNIQUE KEY unique_class_teacher (class_id, teacher_id)
            )
        ");
        echo "<p style='color: green;'>✅ class_teachers table created</p>";
    } else {
        echo "<p style='color: green;'>✅ class_teachers table exists</p>";
    }

    // Step 2: Check for teachers
    echo "<h2>Step 2: Checking teachers</h2>";
    $stmt = $pdo->query("SELECT COUNT(*) FROM teachers WHERE status = 'active'");
    $teacher_count = $stmt->fetchColumn();
    echo "<p>Active teachers: $teacher_count</p>";

    if ($teacher_count == 0) {
        echo "<p style='color: orange;'>⚠️ No active teachers. Creating sample teacher...</p>";

        // Create a sample teacher user first
        $stmt = $pdo->prepare("
            INSERT INTO users (username, password, email, role, status, created_at)
            VALUES (?, ?, ?, 'teacher', 'active', NOW())
        ");
        $password_hash = password_hash('teacher123', PASSWORD_DEFAULT);
        $stmt->execute(['teacher', $password_hash, 'teacher@school.com']);

        $user_id = $pdo->lastInsertId();

        // Create teacher record
        $stmt = $pdo->prepare("
            INSERT INTO teachers (user_id, employee_id, first_name, last_name, email, phone, status)
            VALUES (?, 'T001', 'নমুনা', 'শিক্ষক', 'teacher@school.com', '01700000000', 'active')
        ");
        $stmt->execute([$user_id]);

        echo "<p style='color: green;'>✅ Sample teacher created (username: teacher, password: teacher123)</p>";
        $teacher_count = 1;
    }

    // Step 3: Check for classes
    echo "<h2>Step 3: Checking classes</h2>";
    $stmt = $pdo->query("SELECT COUNT(*) FROM classes WHERE status = 'active'");
    $class_count = $stmt->fetchColumn();
    echo "<p>Active classes: $class_count</p>";

    if ($class_count == 0) {
        echo "<p style='color: orange;'>⚠️ No active classes. Creating sample classes...</p>";

        $sample_classes = [
            ['name' => 'Class 6', 'section' => 'A', 'grade_level' => '6'],
            ['name' => 'Class 7', 'section' => 'A', 'grade_level' => '7'],
            ['name' => 'Class 8', 'section' => 'A', 'grade_level' => '8'],
            ['name' => 'Class 9', 'section' => 'A', 'grade_level' => '9'],
            ['name' => 'Class 10', 'section' => 'A', 'grade_level' => '10']
        ];

        foreach ($sample_classes as $class) {
            $stmt = $pdo->prepare("
                INSERT INTO classes (name, section, grade_level, status, created_at)
                VALUES (?, ?, ?, 'active', NOW())
            ");
            $stmt->execute([$class['name'], $class['section'], $class['grade_level']]);
        }

        echo "<p style='color: green;'>✅ Sample classes created</p>";
        $class_count = count($sample_classes);
    }

    // Step 4: Assign ALL teachers to ALL classes
    echo "<h2>Step 4: Assigning teachers to classes</h2>";
    $stmt = $pdo->query("SELECT id FROM teachers WHERE status = 'active'");
    $teachers = $stmt->fetchAll(PDO::FETCH_COLUMN);

    $stmt = $pdo->query("SELECT id FROM classes WHERE status = 'active'");
    $classes = $stmt->fetchAll(PDO::FETCH_COLUMN);

    $assignments_created = 0;
    foreach ($teachers as $teacher_id) {
        foreach ($classes as $class_id) {
            try {
                $stmt = $pdo->prepare("
                    INSERT IGNORE INTO class_teachers (class_id, teacher_id, assignment_type, status, assigned_at)
                    VALUES (?, ?, 'primary', 'active', NOW())
                ");
                $stmt->execute([$class_id, $teacher_id]);
                $assignments_created++;
            } catch (Exception $e) {
                // Ignore duplicate key errors
            }
        }
    }

    echo "<p style='color: green;'>✅ Created $assignments_created teacher-class assignments</p>";

    // Step 5: Create sample students if needed
    echo "<h2>Step 5: Checking students</h2>";
    $stmt = $pdo->query("SELECT COUNT(*) FROM students WHERE status = 'active'");
    $student_count = $stmt->fetchColumn();
    echo "<p>Active students: $student_count</p>";

    if ($student_count == 0) {
        echo "<p style='color: orange;'>⚠️ No active students. Creating sample students...</p>";

        // Get first class for sample students
        $stmt = $pdo->query("SELECT id FROM classes WHERE status = 'active' LIMIT 1");
        $class = $stmt->fetch();

        if ($class) {
            $sample_students = [
                ['first_name' => 'রাহুল', 'last_name' => 'দাস', 'student_id' => '2024001', 'roll_number' => '1'],
                ['first_name' => 'প্রিয়া', 'last_name' => 'চৌধুরী', 'student_id' => '2024002', 'roll_number' => '2'],
                ['first_name' => 'আরিফ', 'last_name' => 'হোসেন', 'student_id' => '2024003', 'roll_number' => '3'],
                ['first_name' => 'সোনিয়া', 'last_name' => 'খান', 'student_id' => '2024004', 'roll_number' => '4'],
                ['first_name' => 'বিপ্লব', 'last_name' => 'রায়', 'student_id' => '2024005', 'roll_number' => '5']
            ];

            foreach ($sample_students as $student) {
                $stmt = $pdo->prepare("
                    INSERT INTO students (
                        class_id, first_name, last_name, student_id, roll_number,
                        session, phone, email, status, created_at
                    ) VALUES (
                        ?, ?, ?, ?, ?, '2024-2025', '01700000001', 'student@school.com',
                        'active', NOW()
                    )
                ");
                $stmt->execute([
                    $class['id'], $student['first_name'], $student['last_name'],
                    $student['student_id'], $student['roll_number']
                ]);
            }

            echo "<p style='color: green;'>✅ Sample students created in first class</p>";
            $student_count = count($sample_students);
        }
    }

    // Step 6: Test the complete flow
    echo "<h2>Step 6: Testing complete flow</h2>";

    // Get a teacher for testing
    $stmt = $pdo->query("SELECT id, user_id, first_name, last_name FROM teachers WHERE status = 'active' LIMIT 1");
    $test_teacher = $stmt->fetch();

    if ($test_teacher) {
        echo "<p>Testing with teacher: {$test_teacher['first_name']} {$test_teacher['last_name']}</p>";

        // Test get_teacher_assigned_classes
        require_once 'includes/access_control.php';
        $assigned_class_ids = get_teacher_assigned_classes($test_teacher['id']);
        echo "<p>Teacher assigned to " . count($assigned_class_ids) . " classes</p>";

        if (!empty($assigned_class_ids)) {
            // Test assigned classes query
            $placeholders = str_repeat('?,', count($assigned_class_ids) - 1) . '?';
            $stmt = $pdo->prepare("
                SELECT DISTINCT c.id, c.name, c.section
                FROM classes c
                WHERE c.id IN ($placeholders) AND c.status = 'active'
                ORDER BY c.name, c.section
            ");
            $stmt->execute($assigned_class_ids);
            $assigned_classes = $stmt->fetchAll();
            echo "<p>Assigned classes query returned " . count($assigned_classes) . " classes</p>";

            // Test student query
            $class_ids = array_column($assigned_classes, 'id');
            if (!empty($class_ids)) {
                $placeholders = str_repeat('?,', count($class_ids) - 1) . '?';
                $where_clause = "s.status = 'active' AND s.class_id IN ($placeholders)";

                $sql = "SELECT COUNT(*) FROM students s LEFT JOIN classes c ON s.class_id = c.id WHERE $where_clause";
                $stmt = $pdo->prepare($sql);
                $stmt->execute($class_ids);
                $accessible_students = $stmt->fetchColumn();

                echo "<p style='color: green;'>✅ SUCCESS: Teacher can access $accessible_students students</p>";

                if ($accessible_students > 0) {
                    echo "<p style='color: green;'>🎉 my_students.php should now work perfectly!</p>";
                } else {
                    echo "<p style='color: orange;'>⚠️ Teacher has access to classes but no students in those classes</p>";
                }
            } else {
                echo "<p style='color: red;'>❌ Class IDs array is empty</p>";
            }
        } else {
            echo "<p style='color: red;'>❌ Teacher has no assigned classes</p>";
        }
    } else {
        echo "<p style='color: red;'>❌ No teacher found for testing</p>";
    }

    echo "<h2>Final Status</h2>";
    echo "<div style='background: #f8f9fa; padding: 15px; border-radius: 5px; margin: 10px 0;'>";
    echo "<p><strong>✅ class_teachers table:</strong> Created/Verified</p>";
    echo "<p><strong>✅ Teachers:</strong> $teacher_count active</p>";
    echo "<p><strong>✅ Classes:</strong> $class_count active</p>";
    echo "<p><strong>✅ Assignments:</strong> Teachers assigned to classes</p>";
    echo "<p><strong>✅ Students:</strong> $student_count active</p>";
    echo "</div>";

    echo "<h2>Next Steps</h2>";
    echo "<ol>";
    echo "<li><strong>Test the page:</strong> <a href='teachers/my_students.php' target='_blank'>Go to My Students page</a></li>";
    echo "<li><strong>Login as teacher:</strong> Use username 'teacher' and password 'teacher123' if using sample data</li>";
    echo "<li><strong>Debug if needed:</strong> <a href='debug_my_students_detailed.php' target='_blank'>Run detailed debug</a></li>";
    echo "<li><strong>Admin setup:</strong> Use admin panel to properly assign teachers to specific classes</li>";
    echo "</ol>";

    echo "<p style='color: green; font-size: 18px; font-weight: bold;'>🎉 সমস্ত সমস্যা সমাধান হয়েছে! এখন my_students.php সঠিকভাবে কাজ করবে।</p>";

} catch (Exception $e) {
    echo "<p style='color: red;'>❌ Error: " . $e->getMessage() . "</p>";
}
?>
