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

echo "<h2>Session Detection Test</h2>";

$sessions = [];

echo "<h3>Method 1: Academic Sessions Table</h3>";
try {
    $stmt = $pdo->query("SELECT DISTINCT session_name FROM academic_sessions WHERE status = 'active' ORDER BY session_name DESC");
    $db_sessions = $stmt->fetchAll(PDO::FETCH_COLUMN);
    if (!empty($db_sessions)) {
        echo "Found sessions: " . implode(", ", $db_sessions) . "<br>";
        $sessions = array_merge($sessions, $db_sessions);
    } else {
        echo "No sessions found in academic_sessions table<br>";
    }
} catch (Exception $e) {
    echo "academic_sessions table error: " . $e->getMessage() . "<br>";
}

echo "<h3>Method 2: Classes Academic Year</h3>";
try {
    $stmt = $pdo->query("SELECT DISTINCT academic_year FROM classes WHERE academic_year IS NOT NULL AND academic_year != '' ORDER BY academic_year DESC");
    $class_sessions = $stmt->fetchAll(PDO::FETCH_COLUMN);
    if (!empty($class_sessions)) {
        echo "Found academic years: " . implode(", ", $class_sessions) . "<br>";
        $sessions = array_merge($sessions, $class_sessions);
    } else {
        echo "No academic years found in classes table<br>";
    }
} catch (Exception $e) {
    echo "Classes academic_year error: " . $e->getMessage() . "<br>";
}

echo "<h3>Method 3: Student Admission Years</h3>";
try {
    $stmt = $pdo->query("SELECT DISTINCT YEAR(admission_date) as year FROM students WHERE admission_date IS NOT NULL ORDER BY admission_date DESC LIMIT 5");
    $admission_years = $stmt->fetchAll(PDO::FETCH_COLUMN);
    if (!empty($admission_years)) {
        echo "Found admission years: " . implode(", ", $admission_years) . "<br>";
        foreach ($admission_years as $year) {
            if ($year) {
                $sessions[] = $year;
                $sessions[] = $year . '-' . (($year % 100) + 1);
            }
        }
    } else {
        echo "No admission years found<br>";
    }
} catch (Exception $e) {
    echo "Admission date error: " . $e->getMessage() . "<br>";
}

echo "<h3>Method 4: Class Names with Years</h3>";
try {
    $stmt = $pdo->query("SELECT DISTINCT name FROM classes WHERE name REGEXP '[0-9]{4}' ORDER BY name DESC");
    $class_names_with_years = $stmt->fetchAll(PDO::FETCH_COLUMN);
    if (!empty($class_names_with_years)) {
        echo "Classes with years in names:<br>";
        foreach ($class_names_with_years as $name) {
            echo "- " . htmlspecialchars($name) . "<br>";
            preg_match_all('/\d{4}/', $name, $year_matches);
            foreach ($year_matches[0] as $year) {
                echo "  Found year: " . $year . "<br>";
                $sessions[] = $year;
                $sessions[] = $year . '-' . (($year % 100) + 1);
            }
        }
    } else {
        echo "No class names with years found<br>";
    }
} catch (Exception $e) {
    echo "Class names regex error: " . $e->getMessage() . "<br>";
}

echo "<h3>Final Session List (deduplicated):</h3>";
$sessions = array_unique($sessions);
sort($sessions);
$sessions = array_reverse($sessions);

if (!empty($sessions)) {
    echo "All detected sessions:<br>";
    foreach ($sessions as $session) {
        echo "- " . htmlspecialchars($session) . "<br>";
    }
} else {
    echo "No sessions detected from any method<br>";
}

echo "<h3>Test Session Filtering</h3>";
if (!empty($sessions)) {
    $test_session = $sessions[0];
    echo "Testing filter with session: " . htmlspecialchars($test_session) . "<br>";
    
    try {
        // Test the actual filtering query
        $sql = "SELECT COUNT(*) as count FROM students WHERE class_id IN (SELECT id FROM classes WHERE name LIKE ?)";
        $stmt = $pdo->prepare($sql);
        $stmt->execute(['%' . $test_session . '%']);
        $count = $stmt->fetch()['count'];
        echo "Students found with this session filter: " . $count . "<br>";
    } catch (Exception $e) {
        echo "Filter test error: " . $e->getMessage() . "<br>";
    }
}
?>