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

// Get total users count
$stmt = $pdo->query("SELECT COUNT(*) as total FROM users");
$result = $stmt->fetch();
echo "Total users: " . $result['total'] . "\n";

// Get user counts by role
$stmt = $pdo->query("SELECT role, COUNT(*) as count FROM users GROUP BY role");
$results = $stmt->fetchAll();
echo "\nUsers by role:\n";
foreach ($results as $row) {
    echo $row['role'] . ": " . $row['count'] . "\n";
}

// Get student count from students table
$stmt = $pdo->query("SELECT COUNT(*) as total FROM students");
$result = $stmt->fetch();
echo "\nTotal students in students table: " . $result['total'] . "\n";

// Check for users without corresponding student/teacher records
$stmt = $pdo->query("SELECT COUNT(*) as count FROM users u LEFT JOIN students s ON u.id = s.user_id LEFT JOIN teachers t ON u.id = t.user_id WHERE u.role = 'student' AND s.id IS NULL");
$result = $stmt->fetch();
echo "\nStudent users without student records: " . $result['count'] . "\n";

$stmt = $pdo->query("SELECT COUNT(*) as count FROM users u LEFT JOIN students s ON u.id = s.user_id LEFT JOIN teachers t ON u.id = t.user_id WHERE u.role = 'teacher' AND t.id IS NULL");
$result = $stmt->fetch();
echo "Teacher users without teacher records: " . $result['count'] . "\n";
?>