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

$student_id = 6279;
$session = '2025';
$exam_type = 'test';

echo "=== DEBUG: Student 6279 Results ===\n\n";

// Check if student exists
$stmt = $pdo->prepare("SELECT * FROM students WHERE id = ?");
$stmt->execute([$student_id]);
$student = $stmt->fetch(PDO::FETCH_ASSOC);

if (!$student) {
    echo "❌ Student with ID $student_id does not exist in database.\n";
    exit;
}

echo "✅ Student found:\n";
echo "   Name: " . $student['first_name'] . ' ' . $student['last_name'] . "\n";
echo "   Class ID: " . $student['class_id'] . "\n";
echo "   Roll: " . $student['roll_number'] . "\n";
echo "   Status: " . $student['status'] . "\n\n";

// Check student's subjects
$stmt = $pdo->prepare("
    SELECT ss.subject_id, s.name as subject_name, s.code as subject_code, ss.is_fourth_subject
    FROM student_subjects ss
    JOIN subjects s ON ss.subject_id = s.id
    WHERE ss.student_id = ? AND s.status = 'active'
    ORDER BY s.name
");
$stmt->execute([$student_id]);
$subjects = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "📚 Student subjects (" . count($subjects) . "):\n";
foreach ($subjects as $subject) {
    echo "   - " . $subject['subject_name'] . " (ID: " . $subject['subject_id'] . ", Fourth: " . ($subject['is_fourth_subject'] ? 'Yes' : 'No') . ")\n";
}
echo "\n";

// Check if results exist for this student/session/exam_type
$stmt = $pdo->prepare("
    SELECT r.*, sub.name as subject_name, sub.code as subject_code, ss.is_fourth_subject
    FROM results r
    LEFT JOIN subjects sub ON r.subject_id = sub.id
    LEFT JOIN student_subjects ss ON r.subject_id = ss.subject_id AND r.student_id = ss.student_id
    WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?
    ORDER BY sub.name
");
$stmt->execute([$student_id, $session, $exam_type]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "📊 Results for session '$session', exam_type '$exam_type' (" . count($results) . "):\n";
if (empty($results)) {
    echo "   ❌ No results found for this student in this session/exam combination.\n\n";

    // Check what sessions/exam_types exist for this student
    $stmt = $pdo->query("SELECT DISTINCT session, exam_type, COUNT(*) as count FROM results WHERE student_id = $student_id GROUP BY session, exam_type ORDER BY session, exam_type");
    $available_combinations = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo "📅 Available session/exam combinations for this student:\n";
    if (empty($available_combinations)) {
        echo "   ❌ No results found for this student at all.\n";
    } else {
        foreach ($available_combinations as $combo) {
            echo "   - Session: " . $combo['session'] . ", Exam Type: " . $combo['exam_type'] . " (" . $combo['count'] . " results)\n";
        }
    }
} else {
    foreach ($results as $result) {
        echo "   - " . $result['subject_name'] . ": " . $result['marks_obtained'] . "/" . $result['full_marks'] . " (Grade: " . $result['grade'] . ")\n";
    }
}

echo "\n=== END DEBUG ===\n";
?>