<?php
/**
 * Fix for my_students.php page not showing content
 * This script diagnoses and fixes common issues
 */

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

echo "<h1>My Students Page - Diagnostic & Fix</h1>";

try {
    // 1. Check if class_teachers table exists
    echo "<h2>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: red;'>❌ class_teachers table does not exist. Creating it...</p>";

        // Create the table
        $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 'assistant',
                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>";

        // Migrate existing data from classes.class_teacher_id
        echo "<p>Migrating existing teacher assignments...</p>";
        $stmt = $pdo->query("SELECT id, class_teacher_id FROM classes WHERE class_teacher_id IS NOT NULL");
        $classes = $stmt->fetchAll();

        $migrated = 0;
        foreach ($classes as $class) {
            try {
                $stmt = $pdo->prepare("
                    INSERT INTO class_teachers (class_id, teacher_id, assignment_type, assigned_at, status)
                    VALUES (?, ?, 'primary', NOW(), 'active')
                ");
                $stmt->execute([$class['id'], $class['class_teacher_id']]);
                $migrated++;
            } catch (Exception $e) {
                echo "<p style='color: orange;'>⚠️ Warning: Could not migrate class ID {$class['id']}: " . $e->getMessage() . "</p>";
            }
        }
        echo "<p style='color: green;'>✅ Migrated $migrated existing teacher assignments</p>";
    } else {
        echo "<p style='color: green;'>✅ class_teachers table exists</p>";
    }

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

    if ($teacher_count == 0) {
        echo "<p style='color: red;'>❌ No active teachers found. Creating a sample teacher...</p>";

        // Create a sample teacher (assuming admin user exists)
        $stmt = $pdo->prepare("SELECT id FROM users WHERE role = 'admin' LIMIT 1");
        $stmt->execute();
        $admin = $stmt->fetch();

        if ($admin) {
            $stmt = $pdo->prepare("
                INSERT INTO teachers (user_id, employee_id, first_name, last_name, email, status)
                VALUES (?, 'T001', 'Test', 'Teacher', 'teacher@school.com', 'active')
            ");
            $stmt->execute([$admin['id']]);
            echo "<p style='color: green;'>✅ Sample teacher created</p>";
            $teacher_count = 1;
        }
    }

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

    if ($class_count == 0) {
        echo "<p style='color: red;'>❌ No active classes found. Classes should be created by default...</p>";
    }

    // 4. Check teacher-class assignments
    echo "<h2>4. Checking teacher-class assignments</h2>";
    $stmt = $pdo->query("SELECT COUNT(*) FROM class_teachers WHERE status = 'active'");
    $assignment_count = $stmt->fetchColumn();
    echo "<p>Total active teacher-class assignments: $assignment_count</p>";

    if ($assignment_count == 0 && $teacher_count > 0 && $class_count > 0) {
        echo "<p style='color: orange;'>⚠️ No teacher-class assignments found. Creating sample assignments...</p>";

        // Get first teacher and first class
        $stmt = $pdo->query("SELECT id FROM teachers WHERE status = 'active' LIMIT 1");
        $teacher = $stmt->fetch();

        $stmt = $pdo->query("SELECT id FROM classes WHERE status = 'active' LIMIT 1");
        $class = $stmt->fetch();

        if ($teacher && $class) {
            $stmt = $pdo->prepare("
                INSERT INTO class_teachers (class_id, teacher_id, assignment_type, assigned_at, status)
                VALUES (?, ?, 'primary', NOW(), 'active')
            ");
            $stmt->execute([$class['id'], $teacher['id']]);
            echo "<p style='color: green;'>✅ Sample teacher-class assignment created</p>";
        }
    }

    // 5. Check for students
    echo "<h2>5. Checking students</h2>";
    $stmt = $pdo->query("SELECT COUNT(*) FROM students WHERE status = 'active'");
    $student_count = $stmt->fetchColumn();
    echo "<p>Total active students: $student_count</p>";

    if ($student_count == 0) {
        echo "<p style='color: orange;'>⚠️ No active students found. You need to add students to see them in my_students.php</p>";
    }

    // 6. Test the main query logic
    echo "<h2>6. Testing main query logic</h2>";

    // Get a teacher (assuming we have one now)
    $stmt = $pdo->query("SELECT id, user_id FROM teachers WHERE status = 'active' LIMIT 1");
    $test_teacher = $stmt->fetch();

    if ($test_teacher) {
        echo "<p>Testing with teacher ID: {$test_teacher['id']}, user_id: {$test_teacher['user_id']}</p>";

        // Test get_teacher_assigned_classes function
        require_once 'includes/access_control.php';

        $assigned_class_ids = get_teacher_assigned_classes($test_teacher['id']);
        echo "<p>Assigned class IDs: " . implode(', ', $assigned_class_ids) . "</p>";

        if (!empty($assigned_class_ids)) {
            // Test student query
            $placeholders = str_repeat('?,', count($assigned_class_ids) - 1) . '?';
            $sql = "SELECT COUNT(*) FROM students s LEFT JOIN classes c ON s.class_id = c.id WHERE s.status = 'active' AND s.class_id IN ($placeholders)";
            $stmt = $pdo->prepare($sql);
            $stmt->execute($assigned_class_ids);
            $student_count = $stmt->fetchColumn();

            echo "<p style='color: green;'>✅ Query successful. Students in assigned classes: $student_count</p>";

            if ($student_count == 0) {
                echo "<p style='color: orange;'>⚠️ No students in the assigned classes. Add students to see them in my_students.php</p>";
            }
        } else {
            echo "<p style='color: red;'>❌ No classes assigned to this teacher</p>";
        }
    } else {
        echo "<p style='color: red;'>❌ No teacher found to test with</p>";
    }

    echo "<h2>Summary</h2>";
    echo "<p>If you've completed the above steps and still see no content on my_students.php:</p>";
    echo "<ol>";
    echo "<li>Make sure you're logged in as a teacher</li>";
    echo "<li>Ensure your teacher account has classes assigned in the class_teachers table</li>";
    echo "<li>Add students to the assigned classes</li>";
    echo "<li>Try accessing: <a href='debug_my_students.php' target='_blank'>debug_my_students.php</a> for detailed diagnostics</li>";
    echo "</ol>";

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