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

try {
    $student_id = 6279;
    $session = 2025;
    $exam_type = 'final';

    echo "Debug: Testing marksheet query for student $student_id, session $session, exam_type $exam_type\n\n";

    // First, check if student exists
    $stmt = $pdo->prepare("SELECT s.*, c.name as class_name, c.section FROM students s JOIN classes c ON s.class_id = c.id WHERE s.id = ?");
    $stmt->execute([$student_id]);
    $student = $stmt->fetch();
    echo "Student: " . ($student ? $student['first_name'] . ' ' . $student['last_name'] . ' (Class: ' . $student['class_name'] . ')' : 'Not found') . "\n\n";

    if ($student) {
        // The actual query from marksheet
        $stmt = $pdo->prepare("
            SELECT
                COALESCE(r.id, NULL) as result_id,
                COALESCE(r.marks_obtained, 0) as marks_obtained,
                COALESCE(r.cq_marks, 0) as cq_marks,
                COALESCE(r.mcq_marks, 0) as mcq_marks,
                COALESCE(r.practical_marks, 0) as practical_marks,
                COALESCE(r.assignment_marks, 0) as assignment_marks,
                COALESCE(r.attendance_marks, 0) as attendance_marks,
                COALESCE(r.grade, 'F') as grade,
                COALESCE(r.remarks, '') as remarks,
                ss.subject_id,
                sub.name as subject_name,
                sub.code as subject_code,
                ss.is_fourth_subject
            FROM student_subjects ss
            LEFT JOIN subjects sub ON ss.subject_id = sub.id
            LEFT JOIN (
                SELECT *
                FROM results
                WHERE student_id = ? AND session = ? AND exam_type = ?
                AND id IN (
                    SELECT MAX(id)
                    FROM results
                    WHERE student_id = ? AND session = ? AND exam_type = ?
                    GROUP BY subject_id
                )
            ) r ON ss.student_id = r.student_id AND ss.subject_id = r.subject_id
            WHERE ss.student_id = ? AND sub.status = 'active'
            ORDER BY CASE WHEN ss.is_fourth_subject = 1 THEN 1 ELSE 0 END, sub.name
        ");
        $stmt->execute([$student_id, $session, $exam_type, $student_id, $session, $exam_type, $student_id]);
        $results = $stmt->fetchAll();

        echo "Query returned " . count($results) . " subjects:\n";
        foreach ($results as $result) {
            echo "  - {$result['subject_name']} (ID: {$result['subject_id']}, 4th: {$result['is_fourth_subject']}, Result ID: " . ($result['result_id'] ?: 'NULL') . ", Grade: {$result['grade']})\n";
        }

        // Also check what subjects are in the results table directly
        echo "\nDirect results query:\n";
        $stmt = $pdo->prepare("SELECT r.*, s.name as subject_name FROM results r JOIN subjects s ON r.subject_id = s.id WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ? ORDER BY s.name");
        $stmt->execute([$student_id, $session, $exam_type]);
        $direct_results = $stmt->fetchAll();

        echo "Direct results count: " . count($direct_results) . "\n";
        foreach ($direct_results as $result) {
            echo "  - {$result['subject_name']} (Grade: {$result['grade']})\n";
        }
    }

} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage() . "\n";
    echo 'File: ' . $e->getFile() . ':' . $e->getLine() . "\n";
}
?>

