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

try {
    // Get a student ID from class 10
    $stmt = $pdo->prepare('SELECT id, first_name, last_name, roll_number FROM students WHERE class_id = ? AND status = "active" ORDER BY roll_number LIMIT 1');
    $stmt->execute([10]);
    $test_student = $stmt->fetch();
    $student_id = $test_student['id'];
    $session = '2025';
    $exam_type = 'final';

    echo "Testing subject retrieval for student ID: $student_id, session: $session, exam_type: $exam_type\n\n";

    // Get student details
    $stmt = $pdo->prepare("
        SELECT s.*, c.name as class_name, c.section, s.group_name
        FROM students s
        JOIN classes c ON s.class_id = c.id
        WHERE s.id = ?
    ");
    $stmt->execute([$student_id]);
    $student = $stmt->fetch();

    if (!$student) {
        echo "Student not found\n";
        exit;
    }

    echo "Student: {$student['first_name']} {$student['last_name']} (ID: {$student['id']}, Roll: {$student['roll_number']})\n";
    echo "Class: {$student['class_name']} {$student['section']}, Group: {$student['group_name']}\n\n";

    // Get all subjects selected by the student from student_subjects table (as in bulk_print_marksheets.php)
    $stmt = $pdo->prepare(
        "SELECT ss.subject_id, s.name as subject_name, s.code as subject_code, ss.is_fourth_subject\n" .
        "FROM student_subjects ss\n" .
        "JOIN subjects s ON ss.subject_id = s.id\n" .
        "WHERE ss.student_id = ?\n" .
        "ORDER BY s.name\n"
    );
    $stmt->execute([$student_id]);
    $all_student_subjects = $stmt->fetchAll();

    echo "Subjects from student_subjects table: " . count($all_student_subjects) . "\n";
    foreach ($all_student_subjects as $subject) {
        echo "- {$subject['subject_name']} (ID: {$subject['subject_id']}, is_fourth: " . ($subject['is_fourth_subject'] ? 'Yes' : 'No') . ")\n";
    }
    echo "\n";

    // Get results for the student (as in bulk_print_marksheets.php)
    $stmt = $pdo->prepare(
        "SELECT r.*, sub.name as subject_name, sub.code as subject_code\n" .
        "FROM results r\n" .
        "LEFT JOIN subjects sub ON r.subject_id = sub.id\n" .
        "WHERE r.student_id = ? AND r.session = ? AND r.exam_type = ?\n" .
        "AND r.id IN (\n" .
        "    SELECT MAX(id)\n" .
        "    FROM results\n" .
        "    WHERE student_id = ? AND session = ? AND exam_type = ?\n" .
        "    GROUP BY subject_id\n" .
        ")\n" .
        "ORDER BY sub.name\n"
    );
    $stmt->execute([$student_id, $session, $exam_type, $student_id, $session, $exam_type]);
    $results_with_marks = $stmt->fetchAll();

    echo "Results from database: " . count($results_with_marks) . "\n";
    foreach ($results_with_marks as $result) {
        echo "- {$result['subject_name']} (ID: {$result['subject_id']}, Marks: {$result['marks_obtained']})\n";
    }
    echo "\n";

    // Create a map of results by subject_id for easy lookup
    $results_map = [];
    foreach ($results_with_marks as $result) {
        $results_map[$result['subject_id']] = $result;
    }

    // Create combined results array with all subjects
    $results = [];
    $has_missing_marks = false;

    foreach ($all_student_subjects as $subject) {
        $subject_id = $subject['subject_id'];

        // If student has marks for this subject, use the result data
        if (isset($results_map[$subject_id])) {
            $result = $results_map[$subject_id];
            // Add the is_fourth_subject flag from the student_subjects table
            $result['is_fourth_subject'] = $subject['is_fourth_subject'];
            $results[] = $result;
            echo "Subject {$subject['subject_name']} - Has results\n";
        } else {
            echo "Subject {$subject['subject_name']} - No results found\n";
            $has_missing_marks = true;
        }
    }

    echo "\nFinal combined results: " . count($results) . " subjects\n";
    echo "Missing marks: " . ($has_missing_marks ? 'Yes' : 'No') . "\n";

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