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

echo "<h2>Student Data Debug</h2>";

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

    // Check student users
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM users WHERE role = 'student'");
    $student_count = $stmt->fetch()['count'];
    echo "<p>Student Users: " . $student_count . "</p>";

    // Check students table
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM students");
    $students_table_count = $stmt->fetch()['count'];
    echo "<p>Students in students table: " . $students_table_count . "</p>";

    // Check users with student role but no student record
    $stmt = $pdo->query("
        SELECT u.id, u.username, u.role 
        FROM users u 
        LEFT JOIN students s ON u.id = s.user_id 
        WHERE u.role = 'student' AND s.user_id IS NULL
        LIMIT 10
    ");
    $missing_students = $stmt->fetchAll();
    echo "<p>Users with student role but no student record: " . count($missing_students) . "</p>";
    if (!empty($missing_students)) {
        echo "<pre>";
        print_r($missing_students);
        echo "</pre>";
    }

    // Check sample student data
    $stmt = $pdo->query("
        SELECT u.id, u.username, u.role, s.first_name, s.last_name 
        FROM users u 
        LEFT JOIN students s ON u.id = s.user_id 
        WHERE u.role = 'student' 
        LIMIT 10
    ");
    $student_data = $stmt->fetchAll();
    echo "<p>Sample student data:</p>";
    echo "<pre>";
    print_r($student_data);
    echo "</pre>";
    
    // Check for empty names in students table
    $stmt = $pdo->query("
        SELECT u.id, u.username, s.first_name, s.last_name 
        FROM users u 
        JOIN students s ON u.id = s.user_id 
        WHERE (s.first_name IS NULL OR s.first_name = '') 
        OR (s.last_name IS NULL OR s.last_name = '')
        LIMIT 10
    ");
    $empty_names = $stmt->fetchAll();
    echo "<p>Students with empty names:</p>";
    echo "<pre>";
    print_r($empty_names);
    echo "</pre>";

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