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

// Test the subject filtering logic as used in student_filter.php
$session_name = '2025';
$class_id = '10';
$group_name = 'HUMANITIES';

echo "Testing subject filtering logic for:\n";
echo "- Session: $session_name\n";
echo "- Class ID: $class_id\n";
echo "- Group: $group_name\n\n";

// This is the logic from student_filter.php lines 55-64
$stmt = $pdo->prepare("SELECT DISTINCT sub.id, sub.name, sub.code
                       FROM student_subjects ss
                       JOIN students st ON ss.student_id = st.id
                       JOIN subjects sub ON sub.id = ss.subject_id
                       WHERE st.status='active' AND sub.status='active'
                         AND st.session = ? AND st.class_id = ? AND COALESCE(st.group_name,'') = ?
                       ORDER BY sub.name");
$stmt->execute([$session_name, $class_id, $group_name]);
$class_subjects = $stmt->fetchAll();

echo "Subjects found for this session/class/group combination:\n";
echo "Count: " . count($class_subjects) . "\n";
foreach ($class_subjects as $subject) {
    echo "- ID: {$subject['id']}, Name: {$subject['name']} ({$subject['code']})\n";
    
    // Check how many students are assigned to this specific subject in this group
    $stmt2 = $pdo->prepare("SELECT COUNT(*) as count
                           FROM student_subjects ss
                           JOIN students st ON ss.student_id = st.id
                           WHERE st.session = ? AND st.class_id = ? AND st.group_name = ? AND ss.subject_id = ?");
    $stmt2->execute([$session_name, $class_id, $group_name, $subject['id']]);
    $result = $stmt2->fetch();
    echo "  Students assigned: {$result['count']}\n";
}

echo "\nChecking if there are students with session 2025-26:\n";
$stmt = $pdo->prepare("SELECT COUNT(*) as count FROM students WHERE session = ?");
$stmt->execute(['2025-26']);
$result = $stmt->fetch();
echo "Students with session '2025-26': {$result['count']}\n";

echo "\nChecking sessions in dropdown:\n";
$stmt = $pdo->query("SELECT session_name FROM academic_sessions WHERE status='active' ORDER BY session_name DESC");
$sessions = $stmt->fetchAll();
foreach ($sessions as $session) {
    echo "- {$session['session_name']}\n";
}
?>