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

echo "<h2>Session Filter Debug</h2>";

// Check what session values exist in the database
echo "<h3>Available Sessions in Students Table:</h3>";
try {
    $stmt = $pdo->query("SELECT DISTINCT session FROM students WHERE session IS NOT NULL AND session != '' ORDER BY session");
    $db_sessions = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    if (!empty($db_sessions)) {
        echo "Sessions found in students table:<br>";
        foreach ($db_sessions as $session) {
            echo "- " . htmlspecialchars($session) . "<br>";
        }
    } else {
        echo "No sessions found in students table<br>";
    }
} catch (Exception $e) {
    echo "Error getting sessions from students table: " . $e->getMessage() . "<br>";
}

// Check what's in the session dropdown
echo "<h3>Current Session Dropdown Values:</h3>";
try {
    // Simulate the session detection logic from student_customer_list.php
    $sessions = [];
    
    // Method 1: Academic sessions table
    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);
        $sessions = array_merge($sessions, $db_sessions);
    } catch (Exception $e) {
        echo "academic_sessions table error: " . $e->getMessage() . "<br>";
    }
    
    // Method 2: Class names with years
    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);
        foreach ($class_names_with_years as $name) {
            preg_match_all('/\d{4}/', $name, $year_matches);
            foreach ($year_matches[0] as $year) {
                $sessions[] = $year;
                $sessions[] = $year . '-' . (($year % 100) + 1);
            }
        }
    } catch (Exception $e) {
        echo "Class names regex error: " . $e->getMessage() . "<br>";
    }
    
    // Method 3: Admission dates
    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);
        foreach ($admission_years as $year) {
            if ($year) {
                $sessions[] = $year;
                $sessions[] = $year . '-' . (($year % 100) + 1);
            }
        }
    } catch (Exception $e) {
        echo "Admission date error: " . $e->getMessage() . "<br>";
    }
    
    $sessions = array_unique($sessions);
    sort($sessions);
    $sessions = array_reverse($sessions);
    
    echo "Detected sessions for dropdown:<br>";
    foreach ($sessions as $session) {
        echo "- " . htmlspecialchars($session) . "<br>";
    }
    
} catch (Exception $e) {
    echo "Error in session detection: " . $e->getMessage() . "<br>";
}

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