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

$session_name = '2025';
$class_id = '10';
$group_name = 'HUMANITIES';
$subject_id = '35';

echo "Checking subject assignments for:\n";
echo "- Session: $session_name\n";
echo "- Class ID: $class_id\n";
echo "- Group: $group_name\n";
echo "- Subject ID: $subject_id\n\n";

// Check if subject ID 35 exists
$stmt = $pdo->prepare("SELECT id, name, code FROM subjects WHERE id = ?");
$stmt->execute([$subject_id]);
$subject = $stmt->fetch();
if ($subject) {
    echo "Subject $subject_id exists: {$subject['name']} ({$subject['code']})\n";
} else {
    echo "Subject $subject_id does NOT exist!\n";
    exit(1);
}

// Check how many students in this group/class/session are assigned to this subject
$stmt = $pdo->prepare("SELECT COUNT(*) as count 
                       FROM students s 
                       JOIN student_subjects ss ON s.id = ss.student_id 
                       WHERE s.session = ? AND s.class_id = ? AND s.group_name = ? AND ss.subject_id = ?");
$stmt->execute([$session_name, $class_id, $group_name, $subject_id]);
$result = $stmt->fetch();
echo "Students in session=$session_name, class=$class_id, group=$group_name assigned to subject $subject_id: {$result['count']}\n";

// Let's also check what subjects ARE assigned to these students
echo "\nSubjects assigned to students in this group:\n";
$stmt = $pdo->prepare("SELECT DISTINCT sub.id, sub.name, sub.code, COUNT(ss.student_id) as student_count
                       FROM students s
                       JOIN student_subjects ss ON s.id = ss.student_id
                       JOIN subjects sub ON ss.subject_id = sub.id
                       WHERE s.session = ? AND s.class_id = ? AND s.group_name = ?
                       GROUP BY sub.id, sub.name, sub.code
                       ORDER BY sub.name");
$stmt->execute([$session_name, $class_id, $group_name]);
$subjects = $stmt->fetchAll();

foreach ($subjects as $subj) {
    $marker = ($subj['id'] == $subject_id) ? " <-- TARGET" : "";
    echo "- ID: {$subj['id']}, Name: {$subj['name']} ({$subj['code']}) - Students: {$subj['student_count']}$marker\n";
}

// Let's also check if there's an issue with the specific query used in the dropdown
echo "\nChecking the subject dropdown query:\n";
$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]);
$dropdown_subjects = $stmt->fetchAll();
echo "Subjects found for dropdown: " . count($dropdown_subjects) . "\n";

$target_found = false;
foreach ($dropdown_subjects as $subj) {
    if ($subj['id'] == $subject_id) {
        $target_found = true;
        echo "Target subject $subject_id found in dropdown: {$subj['name']} ({$subj['code']})\n";
        break;
    }
}
if (!$target_found) {
    echo "Target subject $subject_id NOT found in dropdown!\n";
    echo "Dropdown subjects:\n";
    foreach ($dropdown_subjects as $subj) {
        echo "- ID: {$subj['id']}, Name: {$subj['name']} ({$subj['code']})\n";
    }
}
?>