<?php
session_start();
require_once '../config/database.php';

// Check total users
$stmt = $pdo->query("SELECT COUNT(*) as total FROM users");
$total_users = $stmt->fetch()['total'];

// Check users by role
$stmt = $pdo->query("SELECT role, COUNT(*) as count FROM users GROUP BY role");
$role_counts = $stmt->fetchAll();

// Check total students
$stmt = $pdo->query("SELECT COUNT(*) as total FROM students");
$total_students = $stmt->fetch()['total'];

// Check students with user_id
$stmt = $pdo->query("SELECT COUNT(*) as total FROM students WHERE user_id IS NOT NULL");
$students_with_user = $stmt->fetch()['total'];

// Check students without user_id
$stmt = $pdo->query("SELECT COUNT(*) as total FROM students WHERE user_id IS NULL");
$students_without_user = $stmt->fetch()['total'];

// Get some sample users
$stmt = $pdo->query("SELECT id, username, role, email FROM users LIMIT 10");
$sample_users = $stmt->fetchAll();

// Get some sample students
$stmt = $pdo->query("SELECT id, student_id, first_name, last_name, user_id FROM students LIMIT 10");
$sample_students = $stmt->fetchAll();

echo "<h2>Database Check Results</h2>";
echo "<h3>User Table Stats:</h3>";
echo "<p>Total users: " . $total_users . "</p>";

echo "<h4>Users by Role:</h4>";
foreach ($role_counts as $role_count) {
    echo "<p>" . $role_count['role'] . ": " . $role_count['count'] . "</p>";
}

echo "<h3>Student Table Stats:</h3>";
echo "<p>Total students: " . $total_students . "</p>";
echo "<p>Students with user_id: " . $students_with_user . "</p>";
echo "<p>Students without user_id: " . $students_without_user . "</p>";

echo "<h3>Sample Users:</h3>";
echo "<pre>";
print_r($sample_users);
echo "</pre>";

echo "<h3>Sample Students:</h3>";
echo "<pre>";
print_r($sample_students);
echo "</pre>";
?>