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

echo "<h2>Database Diagnostic: Users and Students Relationship</h2>";

try {
    // Check total users
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM users");
    $total_users = $stmt->fetch()['total'];
    echo "<p><strong>Total users in database:</strong> " . $total_users . "</p>";

    // Check users by role
    $stmt = $pdo->query("SELECT role, COUNT(*) as count FROM users GROUP BY role");
    $role_counts = $stmt->fetchAll();
    echo "<p><strong>Users by role:</strong></p><ul>";
    foreach ($role_counts as $role_count) {
        echo "<li>" . $role_count['role'] . ": " . $role_count['count'] . "</li>";
    }
    echo "</ul>";

    // Check total students
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM students");
    $total_students = $stmt->fetch()['total'];
    echo "<p><strong>Total students in database:</strong> " . $total_students . "</p>";

    // Check students with user_id (linked to user accounts)
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM students WHERE user_id IS NOT NULL AND user_id != ''");
    $students_with_user = $stmt->fetch()['total'];
    echo "<p><strong>Students with user accounts:</strong> " . $students_with_user . "</p>";

    // Check students without user_id (not linked to user accounts)
    $stmt = $pdo->query("SELECT COUNT(*) as total FROM students WHERE user_id IS NULL OR user_id = ''");
    $students_without_user = $stmt->fetch()['total'];
    echo "<p><strong>Students without user accounts:</strong> " . $students_without_user . "</p>";

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

    // Get some sample data to understand the relationship
    if ($total_students > 0) {
        echo "<h3>Sample Students Data:</h3>";
        $stmt = $pdo->query("SELECT id, first_name, last_name, student_id, user_id FROM students LIMIT 10");
        $sample_students = $stmt->fetchAll();
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>ID</th><th>Name</th><th>Student ID</th><th>User ID</th><th>User Role</th></tr>";
        foreach ($sample_students as $student) {
            // Get the user role if linked
            $user_role = 'N/A';
            if ($student['user_id']) {
                $user_stmt = $pdo->prepare("SELECT role FROM users WHERE id = ?");
                $user_stmt->execute([$student['user_id']]);
                $user = $user_stmt->fetch();
                if ($user) {
                    $user_role = $user['role'];
                }
            }
            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['user_id'] ? $student['user_id'] : 'NULL') . "</td>";
            echo "<td>" . $user_role . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }

    // Get sample user data
    if ($total_users > 0) {
        echo "<h3>Sample Users Data:</h3>";
        $stmt = $pdo->query("SELECT id, username, role, email FROM users LIMIT 10");
        $sample_users = $stmt->fetchAll();
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr><th>ID</th><th>Username</th><th>Role</th><th>Email</th></tr>";
        foreach ($sample_users as $user) {
            echo "<tr>";
            echo "<td>" . $user['id'] . "</td>";
            echo "<td>" . $user['username'] . "</td>";
            echo "<td>" . $user['role'] . "</td>";
            echo "<td>" . $user['email'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }

    // Check the actual query used in users.php
    echo "<h3>Simulating the users.php query:</h3>";
    $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
        ORDER BY u.created_at DESC
        LIMIT 20
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    $result = $stmt->fetchAll();
    
    echo "<p><strong>Results from users.php query (first 20):</strong> " . count($result) . " records</p>";
    if (count($result) > 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 ($result as $record) {
            echo "<tr>";
            echo "<td>" . $record['id'] . "</td>";
            echo "<td>" . $record['username'] . "</td>";
            echo "<td>" . $record['role'] . "</td>";
            echo "<td>" . $record['full_name'] . "</td>";
            echo "<td>" . $record['email'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }

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