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

// Check what students exist in the database
echo "Checking students in database...\n";

// Show all distinct sessions
echo "\n--- Available Sessions ---\n";
$stmt = $pdo->query("SELECT DISTINCT session FROM students WHERE session IS NOT NULL AND session != '' ORDER BY session");
$sessions = $stmt->fetchAll(PDO::FETCH_COLUMN);
foreach ($sessions as $session) {
    echo "Session: $session\n";
}

// Show all classes
echo "\n--- Available Classes ---\n";
$stmt = $pdo->query("SELECT id, name, section FROM classes ORDER BY name, section");
$classes = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($classes as $class) {
    echo "Class ID: {$class['id']}, Name: {$class['name']}, Section: {$class['section']}\n";
}

// Check for active students
echo "\n--- Active Students Count by Session/Class ---\n";
$stmt = $pdo->query("SELECT session, class_id, COUNT(*) as count FROM students WHERE status = 'active' GROUP BY session, class_id ORDER BY session, class_id");
$active_students = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($active_students as $row) {
    echo "Session: {$row['session']}, Class ID: {$row['class_id']}, Active Students: {$row['count']}\n";
}

// Check for all students (any status)
echo "\n--- All Students Count by Session/Class ---\n";
$stmt = $pdo->query("SELECT session, class_id, status, COUNT(*) as count FROM students GROUP BY session, class_id, status ORDER BY session, class_id, status");
$all_students = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($all_students as $row) {
    echo "Session: {$row['session']}, Class ID: {$row['class_id']}, Status: {$row['status']}, Count: {$row['count']}\n";
}

echo "\n--- Sample Students ---\n";
$stmt = $pdo->query("SELECT id, student_id, first_name, last_name, session, class_id, status FROM students LIMIT 10");
$sample_students = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($sample_students as $student) {
    echo "ID: {$student['id']}, Student ID: {$student['student_id']}, Name: {$student['first_name']} {$student['last_name']}, Session: {$student['session']}, Class ID: {$student['class_id']}, Status: {$student['status']}\n";
}
?>