<?php
session_start();

// Include access control functions
require_once 'includes/access_control.php';
require_once 'config/database.php';

// Enable error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "<h1>Debug: Student Query in my_students.php</h1>";

try {
    // Check authentication
    if (!isset($_SESSION['user_id']) || !is_teacher()) {
        echo "<p style='color: red;'>ERROR: Not logged in as teacher</p>";
        exit;
    }

    // Get teacher ID
    $stmt = $pdo->prepare("SELECT id FROM teachers WHERE user_id = ?");
    $stmt->execute([$_SESSION['user_id']]);
    $teacher = $stmt->fetch();

    if (!$teacher) {
        echo "<p style='color: red;'>ERROR: No teacher record found</p>";
        exit;
    }

    $teacher_id = $teacher['id'];
    echo "<p>Teacher ID: $teacher_id</p>";

    // Get assigned class IDs
    $assigned_class_ids = get_teacher_assigned_classes($teacher_id);
    echo "<p>Assigned class IDs: " . implode(', ', $assigned_class_ids) . "</p>";

    // Simulate the logic from my_students.php
    $assigned_classes = [];
    if (!empty($assigned_class_ids)) {
        $placeholders = str_repeat('?,', count($assigned_class_ids) - 1) . '?';
        $stmt = $pdo->prepare("
            SELECT DISTINCT c.id, c.name, c.section, 'assigned' as assignment_type
            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 found: " . count($assigned_classes) . "</p>";
    }

    // Get class IDs for filtering
    $class_ids = array_column($assigned_classes, 'id');
    echo "<p>Class IDs array: " . implode(', ', $class_ids) . "</p>";

    // Pagination and search (default values)
    $page = 1;
    $limit = 10;
    $offset = 0;
    $search = '';
    $class_filter = 0;

    // Build query conditions (same logic as my_students.php)
    $where_conditions = ["s.status = 'active'"];
    $params = [];

    // Filter by teacher's assigned classes
    if (!empty($class_ids)) {
        $placeholders = str_repeat('?,', count($class_ids) - 1) . '?';
        $where_conditions[] = "s.class_id IN ($placeholders)";
        $params = array_merge($params, $class_ids);
        echo "<p style='color: green;'>✓ Added class filter to query</p>";
    } else {
        $where_conditions[] = "1=0"; // Always false condition
        echo "<p style='color: red;'>❌ No class IDs - query will return no results!</p>";
    }

    // Search condition
    if (!empty($search)) {
        $where_conditions[] = "(s.first_name LIKE ? OR s.last_name LIKE ? OR s.student_id LIKE ? OR s.roll_number LIKE ?)";
        $params[] = "%$search%";
        $params[] = "%$search%";
        $params[] = "%$search%";
        $params[] = "%$search%";
    }

    // Class filter
    if ($class_filter > 0) {
        $where_conditions[] = "s.class_id = ?";
        $params[] = $class_filter;
    }

    $where_clause = implode(' AND ', $where_conditions);
    echo "<p>WHERE clause: $where_clause</p>";
    echo "<p>Parameters: " . implode(', ', $params) . "</p>";

    // Get total count
    $count_sql = "SELECT COUNT(*) FROM students s LEFT JOIN classes c ON s.class_id = c.id WHERE $where_clause";
    echo "<p>Count SQL: $count_sql</p>";

    $count_stmt = $pdo->prepare($count_sql);
    $count_stmt->execute($params);
    $total_records = $count_stmt->fetchColumn();
    echo "<p>Total records found: $total_records</p>";

    // Get students
    $order_clause = "ORDER BY s.roll_number ASC";
    $sql = "SELECT s.*, c.name as class_name, c.section as class_section FROM students s LEFT JOIN classes c ON s.class_id = c.id WHERE $where_clause $order_clause LIMIT ? OFFSET ?";
    echo "<p>Student SQL: $sql</p>";

    $execute_params = array_merge($params, [$limit, $offset]);
    echo "<p>Execute parameters: " . implode(', ', $execute_params) . "</p>";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($execute_params);
    $students = $stmt->fetchAll();

    echo "<p style='color: blue; font-weight: bold;'>Students found: " . count($students) . "</p>";

    if (!empty($students)) {
        echo "<h3>Sample Students:</h3>";
        echo "<table border='1'>";
        echo "<tr><th>ID</th><th>Name</th><th>Student ID</th><th>Class</th><th>Roll</th><th>Status</th></tr>";
        foreach (array_slice($students, 0, 5) as $student) {
            echo "<tr>";
            echo "<td>{$student['id']}</td>";
            echo "<td>{$student['first_name']} {$student['last_name']}</td>";
            echo "<td>{$student['student_id']}</td>";
            echo "<td>{$student['class_name']} {$student['class_section']}</td>";
            echo "<td>{$student['roll_number']}</td>";
            echo "<td>{$student['status']}</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p style='color: red;'>No students found! This is why the table is empty.</p>";
    }

    // Check raw students in assigned classes
    echo "<h2>Raw Check: Students in Assigned Classes</h2>";
    if (!empty($class_ids)) {
        $placeholders = str_repeat('?,', count($class_ids) - 1) . '?';
        $raw_sql = "SELECT COUNT(*) FROM students WHERE class_id IN ($placeholders) AND status = 'active'";
        $stmt = $pdo->prepare($raw_sql);
        $stmt->execute($class_ids);
        $raw_count = $stmt->fetchColumn();
        echo "<p>Raw student count in assigned classes: $raw_count</p>";
    }

} catch (Exception $e) {
    echo "<p style='color: red;'>ERROR: " . $e->getMessage() . "</p>";
    echo "<p>Stack trace: " . $e->getTraceAsString() . "</p>";
}

echo "<h2>Diagnosis</h2>";
if (empty($assigned_class_ids)) {
    echo "<p style='color: red;'>❌ Teacher has no assigned classes</p>";
    echo "<p>SOLUTION: Run the assignment fix scripts</p>";
} elseif (empty($class_ids)) {
    echo "<p style='color: red;'>❌ Assigned classes query returned no results</p>";
    echo "<p>SOLUTION: Check if classes exist and are active</p>";
} elseif ($total_records == 0) {
    echo "<p style='color: red;'>❌ No students in assigned classes</p>";
    echo "<p>SOLUTION: Add students to the classes</p>";
} else {
    echo "<p style='color: green;'>✅ Everything should work! Check my_students.php</p>";
}
?>
