<?php
require_once 'config/database.php';

echo "<h2>Debug: Student Users Issue</h2>";

try {
    // Check all users with role = 'student'
    $stmt = $pdo->prepare("SELECT COUNT(*) as total FROM users WHERE role = ?");
    $stmt->execute(['student']);
    $student_users_count = $stmt->fetch()['total'];
    echo "<p><strong>Number of users with role 'student':</strong> " . $student_users_count . "</p>";

    if ($student_users_count > 0) {
        echo "<h3>Student Users Details:</h3>";
        $stmt = $pdo->prepare("SELECT id, username, email, role, status, created_at FROM users WHERE role = ? ORDER BY created_at DESC");
        $stmt->execute(['student']);
        $student_users = $stmt->fetchAll();
        
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>ID</th><th>Username</th><th>Email</th><th>Status</th><th>Created At</th></tr>";
        foreach ($student_users as $user) {
            echo "<tr>";
            echo "<td>" . $user['id'] . "</td>";
            echo "<td>" . $user['username'] . "</td>";
            echo "<td>" . $user['email'] . "</td>";
            echo "<td>" . $user['status'] . "</td>";
            echo "<td>" . $user['created_at'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p><strong>No users found with role 'student'</strong></p>";
    }

    // Check all users to see the distribution
    echo "<h3>All Users Distribution:</h3>";
    $stmt = $pdo->query("SELECT role, COUNT(*) as count FROM users GROUP BY role");
    $all_users = $stmt->fetchAll();
    
    echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
    echo "<tr><th>Role</th><th>Count</th></tr>";
    foreach ($all_users as $user) {
        echo "<tr>";
        echo "<td>" . $user['role'] . "</td>";
        echo "<td>" . $user['count'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";

    // Check if there are students in the students table without user accounts
    echo "<h3>Students without User Accounts:</h3>";
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM students WHERE user_id IS NULL OR user_id = 0 OR user_id = ''");
    $students_without_accounts = $stmt->fetch()['total'];
    echo "<p><strong>Students without user accounts:</strong> " . $students_without_accounts . "</p>";

    // Check the actual query that runs in users.php
    echo "<h3>Testing the users.php query with role filter:</h3>";
    $role_filter = 'student'; // This simulates when the role filter is set to student
    
    $params = [];
    $where_conditions = [];

    if (!empty($role_filter)) {
        $where_conditions[] = "u.role = ?";
        $params[] = $role_filter;
    }

    $where_clause = !empty($where_conditions) ? 'WHERE ' . implode(' AND ', $where_conditions) : '';

    $sql = "
        SELECT u.*, 
               CASE 
                   WHEN u.role = 'student' THEN CONCAT(s.first_name, ' ', s.last_name)
                   WHEN u.role = 'teacher' THEN CONCAT(t.first_name, ' ', t.last_name)
                   ELSE u.username
               END as full_name
        FROM users u
        LEFT JOIN students s ON u.id = s.user_id
        LEFT JOIN teachers t ON u.id = t.user_id
        $where_clause
        ORDER BY u.created_at DESC
    ";

    echo "<p><strong>Query:</strong> " . htmlspecialchars($sql) . "</p>";
    echo "<p><strong>Parameters:</strong> " . print_r($params, true) . "</p>";

    $stmt = $pdo->prepare($sql);
    $stmt->execute($params);
    $results = $stmt->fetchAll();

    echo "<p><strong>Query Results (with role filter = 'student'):</strong> " . count($results) . " records</p>";

    if (count($results) > 0) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>ID</th><th>Username</th><th>Full Name</th><th>Email</th><th>Status</th></tr>";
        foreach ($results as $result) {
            echo "<tr>";
            echo "<td>" . $result['id'] . "</td>";
            echo "<td>" . $result['username'] . "</td>";
            echo "<td>" . $result['full_name'] . "</td>";
            echo "<td>" . $result['email'] . "</td>";
            echo "<td>" . $result['status'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    } else {
        echo "<p><strong>No results returned with role filter = 'student'</strong></p>";
    }

    // Check if the role filter might be causing the issue
    echo "<h3>Testing without role filter:</h3>";
    $sql_all = "
        SELECT u.*, 
               CASE 
                   WHEN u.role = 'student' THEN CONCAT(s.first_name, ' ', s.last_name)
                   WHEN u.role = 'teacher' THEN CONCAT(t.first_name, ' ', t.last_name)
                   ELSE u.username
               END as full_name
        FROM users u
        LEFT JOIN students s ON u.id = s.user_id
        LEFT JOIN teachers t ON u.id = t.user_id
        ORDER BY u.created_at DESC
    ";

    $stmt_all = $pdo->prepare($sql_all);
    $stmt_all->execute();
    $all_results = $stmt_all->fetchAll();

    echo "<p><strong>Query Results (without role filter):</strong> " . count($all_results) . " records</p>";

    if (count($all_results) > 0) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>ID</th><th>Username</th><th>Role</th><th>Full Name</th><th>Email</th></tr>";
        foreach ($all_results as $result) {
            echo "<tr>";
            echo "<td>" . $result['id'] . "</td>";
            echo "<td>" . $result['username'] . "</td>";
            echo "<td>" . $result['role'] . "</td>";
            echo "<td>" . $result['full_name'] . "</td>";
            echo "<td>" . $result['email'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }

    echo "<h3>Potential Solutions:</h3>";
    echo "<ol>";
    echo "<li>If there are no users with role 'student', you need to create student user accounts</li>";
    echo "<li>Check if the role filter dropdown on users.php page is set to 'student'</li>";
    echo "<li>If students exist in the students table but not in users table, you need to create user accounts for them</li>";
    echo "<li>Check if JavaScript is correctly showing/hiding the student filter fields</li>";
    echo "</ol>";

} catch (Exception $e) {
    echo "<p>Error: " . $e->getMessage() . "</p>";
}
?>