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

echo "<h2>Database Examination</h2>";

// Check students table structure
echo "<h3>Students Table Structure:</h3>";
$stmt = $pdo->query("DESCRIBE students");
echo "<pre>";
while ($row = $stmt->fetch()) {
    print_r($row);
}
echo "</pre>";

// Check users table structure
echo "<h3>Users Table Structure:</h3>";
$stmt = $pdo->query("DESCRIBE users");
echo "<pre>";
while ($row = $stmt->fetch()) {
    print_r($row);
}
echo "</pre>";

// Check sample student data
echo "<h3>Sample Student Records:</h3>";
$stmt = $pdo->query("SELECT id, user_id, first_name, last_name FROM students LIMIT 10");
echo "<pre>";
while ($row = $stmt->fetch()) {
    print_r($row);
}
echo "</pre>";

// Check sample user data with student role
echo "<h3>Sample User Records (Student Role):</h3>";
$stmt = $pdo->query("SELECT id, username, role FROM users WHERE role = 'student' LIMIT 10");
echo "<pre>";
while ($row = $stmt->fetch()) {
    print_r($row);
}
echo "</pre>";

// Check joined data
echo "<h3>Joined User-Student Data:</h3>";
$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
");
echo "<pre>";
while ($row = $stmt->fetch()) {
    print_r($row);
}
echo "</pre>";

// Check how many users with student role have no corresponding student record
echo "<h3>Users with Student Role but No Student Record:</h3>";
$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
");
$results = $stmt->fetchAll();
echo "<p>Count: " . count($results) . "</p>";
echo "<pre>";
print_r($results);
echo "</pre>";
?>