<?php
session_start(); // Start the session to access session variables

require_once 'config.php'; // Include the database configuration file. Ensure this path is correct.

// Define role constants.
if (!defined('ROLE_SUPER_ADMIN')) define('ROLE_SUPER_ADMIN', 1);
if (!defined('ROLE_ADMIN')) define('ROLE_ADMIN', 2);
if (!defined('ROLE_TEACHER')) define('ROLE_TEACHER', 3);
if (!defined('ROLE_STUDENT')) define('ROLE_STUDENT', 4);
if (!defined('ROLE_PARENT')) define('ROLE_PARENT', 5);
if (!defined('ROLE_STAFF')) define('ROLE_STAFF', 6);

// --- Language Handling and Localization Dictionary ---
$available_languages = ['en', 'lug', 'sw']; // English, Luganda, Kiswahili
$default_language = 'en';

// Check if a language is selected via GET parameter; otherwise, use session or default.
if (isset($_GET['lang']) && in_array($_GET['lang'], $available_languages)) {
    $_SESSION['lang'] = $_GET['lang'];
} elseif (!isset($_SESSION['lang'])) {
    $_SESSION['lang'] = $default_language;
}

$current_lang = $_SESSION['lang'];

/**
 * Loads language-specific strings.
 * @param string $lang The current language code.
 * @return array An associative array of localized strings.
 */
function load_language_data($lang) {
    $data = [
        'en' => [
            'dashboard_title_prefix' => 'Student Dashboard',
            'sidebar_dashboard' => 'Dashboard',
            'sidebar_profile' => 'Profile',
            'sidebar_reports' => 'Reports',
            'sidebar_assignments' => 'Assignments',
            'sidebar_messages' => 'Messages',
            'sidebar_settings' => 'Settings',
            'sidebar_logout' => 'Logout',
            'school_name_not_found' => 'School Name Not Found',
            'search_placeholder' => 'Search reports',
            'welcome_message_prefix' => 'Welcome',
            'welcome_message_suffix' => '!',
            'logged_in_as' => 'Logged in as:',
            'divine_reports' => 'DIVINE REPORTS',
            'reports_heading' => 'My Academic Reports',
            'select_academic_year' => 'Select Academic Year',
            'select_term' => 'Select Term',
            'select_exam_type' => 'Select Exam Type', // New
            'view_report_button' => 'View Report',
            'download_report_button' => 'Download Report Card', // New
            'academic_year_column' => 'Academic Year',
            'term_column' => 'Term',
            'exam_type_column' => 'Exam Type',
            'subject_column' => 'Subject',
            'score_column' => 'Score',
            'grade_column' => 'Grade',
            'remarks_column' => 'Remarks',
            'overall_performance' => 'Overall Performance', // New
            'total_score_label' => 'Total Score:', // New
            'average_score_label' => 'Average Score:', // New
            'overall_grade_label' => 'Overall Grade:', // New
            'overall_remarks_label' => 'Overall Remarks:', // New
            'no_reports_found' => 'No academic reports found for the selected criteria. Please try different filters.',
            'select_filters_message' => 'Please select Academic Year, Term, and Exam Type to view your report card.', // New
            'student_name_label' => 'Student Name:', // New
            'admission_no_label' => 'Admission No.:', // New
            'class_label' => 'Class:', // New
            'stream_label' => 'Stream:', // New
            'parent_guardian_details' => 'Parent/Guardian Details:', // New
            'parent_name_label' => 'Name:', // New
            'parent_phone_label' => 'Phone:', // New
            'parent_email_label' => 'Email:' // New
        ],
        'lug' => [
            'dashboard_title_prefix' => 'Diisiboodi y’Omuyizi',
            'sidebar_dashboard' => 'Diisiboodi',
            'sidebar_profile' => 'Purofayiro',
            'sidebar_reports' => 'Lipooti',
            'sidebar_assignments' => 'Ebitongole',
            'sidebar_messages' => 'Obubaka',
            'sidebar_settings' => 'Enteekateeka',
            'sidebar_logout' => 'Log Out',
            'school_name_not_found' => 'Erinnya ly’Essomero Terinnaba Kulabika',
            'search_placeholder' => 'Noonya lipooti',
            'welcome_message_prefix' => 'Mpulira Olamusiddwa',
            'welcome_message_suffix' => '!',
            'logged_in_as' => 'Oyinze okukakasa nga:',
            'divine_reports' => 'RIPOTA ZA MUNGU',
            'reports_heading' => 'Lipooti Zange Ez’Omusomo',
            'select_academic_year' => 'Londa Omwaka Gw’Omusomo',
            'select_term' => 'Londa Ekkambi',
            'select_exam_type' => 'Londa Ebigezo', // New
            'view_report_button' => 'Laba Lipooti',
            'download_report_button' => 'Tekamu Lipooti y’Akaadi', // New
            'academic_year_column' => 'Omwaka Gw’Omusomo',
            'term_column' => 'Ekkambi',
            'exam_type_column' => 'Ebigezo',
            'subject_column' => 'Eky’okusomesebwa',
            'score_column' => 'Akaadi',
            'grade_column' => 'Ebipimo',
            'remarks_column' => 'Ebyokwogera',
            'overall_performance' => 'Enkola yonna', // New
            'total_score_label' => 'Akaadi yonna:', // New
            'average_score_label' => 'Akaadi wakati:', // New
            'overall_grade_label' => 'Akaadi yonna:', // New
            'overall_remarks_label' => 'Ebyokwogera byonna:', // New
            'no_reports_found' => 'Tewali lipooti z’amasomo zisangiddwa ku byalondeddwa. Gezaako engeri endala.',
            'select_filters_message' => 'Londa Omwaka Gw’Omusomo, Ekkambi, n’Ebigezo okulaba lipooti yo.', // New
            'student_name_label' => 'Erinnya ly’Omuyizi:', // New
            'admission_no_label' => 'No. y’okuyingizibwa:', // New
            'class_label' => 'Ekibiina:', // New
            'stream_label' => 'Omudda:', // New
            'parent_guardian_details' => 'Ebikwata ku Muzadde/Mulabirira:', // New
            'parent_name_label' => 'Erinnya:', // New
            'parent_phone_label' => 'Essimu:', // New
            'parent_email_label' => 'E-mail:' // New
        ],
        'sw' => [
            'dashboard_title_prefix' => 'Dashibodi ya Mwanafunzi',
            'sidebar_dashboard' => 'Dashibodi',
            'sidebar_profile' => 'Wasifu',
            'sidebar_reports' => 'Ripoti',
            'sidebar_assignments' => 'Kazi',
            'sidebar_messages' => 'Ujumbe',
            'sidebar_settings' => 'Mipangilio',
            'sidebar_logout' => 'Ondoka',
            'school_name_not_found' => 'Jina la Shule Halikupatikana',
            'search_placeholder' => 'Tafuta ripoti',
            'welcome_message_prefix' => 'Karibu',
            'welcome_message_suffix' => '!',
            'logged_in_as' => 'Umeingia kama:',
            'divine_reports' => 'RIPOTI ZA MUNGU',
            'reports_heading' => 'Ripoti Zangu za Masomo',
            'select_academic_year' => 'Chagua Mwaka wa Masomo',
            'select_term' => 'Chagua Muhula',
            'select_exam_type' => 'Chagua Aina ya Mtihani', // New
            'view_report_button' => 'Tazama Ripoti',
            'download_report_button' => 'Pakua Kadi ya Ripoti', // New
            'academic_year_column' => 'Mwaka wa Masomo',
            'term_column' => 'Muhula',
            'exam_type_column' => 'Aina ya Mtihani',
            'subject_column' => 'Somo',
            'score_column' => 'Alama',
            'grade_column' => 'Daraja',
            'remarks_column' => 'Maelezo',
            'overall_performance' => 'Utendaji wa Jumla', // New
            'total_score_label' => 'Jumla ya Alama:', // New
            'average_score_label' => 'Wastani wa Alama:', // New
            'overall_grade_label' => 'Daraja la Jumla:', // New
            'overall_remarks_label' => 'Maelezo ya Jumla:', // New
            'no_reports_found' => 'Hakuna ripoti za masomo zilizopatikana kwa vigezo vilivyochaguliwa. Tafadhali jaribu vichujio tofauti.',
            'select_filters_message' => 'Tafadhali chagua Mwaka wa Masomo, Muhula, na Aina ya Mtihani ili kuona kadi yako ya ripoti.', // New
            'student_name_label' => 'Jina la Mwanafunzi:', // New
            'admission_no_label' => 'Namba ya Uandikishaji:', // New
            'class_label' => 'Darasa:', // New
            'stream_label' => 'Mkondo:', // New
            'parent_guardian_details' => 'Maelezo ya Mzazi/Mlezi:', // New
            'parent_name_label' => 'Jina:', // New
            'parent_phone_label' => 'Simu:', // New
            'parent_email_label' => 'Barua pepe:' // New
        ]
    ];
    return $data[$lang] ?? $data['en']; // Fallback to English if language not found
}

$_L = load_language_data($current_lang);

/**
 * Function to check the user's role and redirect if they are unauthorized to access the current page.
 * @param int $required_role_code The role code required to access this page.
 */
function check_role_access($required_role_code) {
    if (!isset($_SESSION["loggedin"]) || $_SESSION["loggedin"] !== true || !isset($_SESSION["school_id"])) {
        header("location: login.php");
        exit;
    }

    if ($_SESSION["role"] !== $required_role_code) {
        switch ($_SESSION["role"]) {
            case ROLE_SUPER_ADMIN:
                header("location: super_admin_dashboard.php");
                break;
            case ROLE_ADMIN:
                header("location: admin_dashboard.php");
                break;
            case ROLE_TEACHER:
                header("location: teacher_dashboard.php");
                break;
            default:
                header("location: login.php");
                break;
        }
        exit;
    }
}

check_role_access(ROLE_STUDENT);

$currentPage = 'reports';

$student_user_id = $_SESSION["id"];
$school_id = $_SESSION["school_id"];
$username = htmlspecialchars($_SESSION["username"]);
$user_role_code = $_SESSION["role"];

$student_photo = "images/default_student_photo.png";
$school_name = $_L['school_name_not_found'];
$school_logo_path = "images/default_school_logo.png";

$role_name = "Unknown";
switch ($user_role_code) {
    case ROLE_SUPER_ADMIN: $role_name = "Super Admin"; break;
    case ROLE_ADMIN:         $role_name = "Admin";        break;
    case ROLE_TEACHER:       $role_name = "Teacher";      break;
    case ROLE_STUDENT:       $role_name = "Student";      break;
    case ROLE_PARENT:        $role_name = "Parent";       break;
    case ROLE_STAFF:         $role_name = "Staff";        break;
}

// Fetch student and school data for header display
if (isset($conn) && $conn instanceof mysqli) {
    $sql_student_details = "SELECT photo FROM users WHERE id = ?";
    if ($stmt_student = $conn->prepare($sql_student_details)) {
        $stmt_student->bind_param("i", $student_user_id);
        if ($stmt_student->execute()) {
            $stmt_student->bind_result($photo_db);
            if ($stmt_student->fetch()) {
                $student_photo = !empty($photo_db) ? htmlspecialchars($photo_db) : 'images/default_student_photo.png';
            }
        }
        $stmt_student->close();
    }

    $sql_school_details = "SELECT school_name, logo FROM schools WHERE school_id = ?";
    if ($stmt_school = $conn->prepare($sql_school_details)) {
        $stmt_school->bind_param("i", $school_id);
        if ($stmt_school->execute()) {
            $stmt_school->bind_result($school_name_db, $school_logo_db);
            if ($stmt_school->fetch()) {
                $school_name = htmlspecialchars($school_name_db);
                $school_logo_path = !empty($school_logo_db) ? htmlspecialchars($school_logo_db) : 'images/default_school_logo.png';
            }
        }
        $stmt_school->close();
    }

    // Fetch unique academic years and terms for dropdowns
    $academicYears = [];
    $terms = [];
    $exams = []; // To store exam types

    // Fetch Academic Years
    $sql_years = "SELECT DISTINCT academic_year FROM academic_years_terms WHERE school_id = ? ORDER BY academic_year DESC";
    if ($stmt_years = $conn->prepare($sql_years)) {
        $stmt_years->bind_param("i", $school_id);
        if ($stmt_years->execute()) {
            $result_years = $stmt_years->get_result();
            while ($row = $result_years->fetch_assoc()) {
                $academicYears[] = $row['academic_year'];
            }
        }
        $stmt_years->close();
    }

    // Fetch Terms
    $sql_terms = "SELECT DISTINCT term_name FROM academic_years_terms WHERE school_id = ? ORDER BY term_name ASC";
    if ($stmt_terms = $conn->prepare($sql_terms)) {
        $stmt_terms->bind_param("i", $school_id);
        if ($stmt_terms->execute()) {
            $result_terms = $stmt_terms->get_result();
            while ($row = $result_terms->fetch_assoc()) {
                $terms[] = $row['term_name'];
            }
        }
        $stmt_terms->close();
    }

    // Fetch Exam Types
    $sql_exams = "SELECT exam_id, exam_name FROM exams WHERE school_id = ? ORDER BY exam_name ASC"; // Assuming exams are linked to school_id
    if ($stmt_exams = $conn->prepare($sql_exams)) {
        $stmt_exams->bind_param("i", $school_id);
        if ($stmt_exams->execute()) {
            $result_exams = $stmt_exams->get_result();
            while ($row = $result_exams->fetch_assoc()) {
                $exams[] = ['id' => $row['exam_id'], 'name' => $row['exam_name']];
            }
        }
        $stmt_exams->close();
    }

    // Handle AJAX request for filtered reports and overall performance
    if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['action']) && $_POST['action'] == 'fetch_report_card') {
        header('Content-Type: application/json');
        ini_set('display_errors', 0);
        error_reporting(E_ALL & ~E_NOTICE & ~E_WARNING);

        if (!isset($conn) || !$conn instanceof mysqli) {
            require 'config.php';
            if (!isset($conn) || !$conn instanceof mysqli) {
                echo json_encode(['success' => false, 'message' => 'Database connection error for AJAX request.']);
                exit;
            }
        }

        try {
            $selectedYear = htmlspecialchars(filter_input(INPUT_POST, 'academic_year', FILTER_SANITIZE_FULL_SPECIAL_CHARS) ?: '');
            $selectedTerm = htmlspecialchars(filter_input(INPUT_POST, 'term', FILTER_SANITIZE_FULL_SPECIAL_CHARS) ?: '');
            $selectedExamId = filter_input(INPUT_POST, 'exam_id', FILTER_VALIDATE_INT);

            if (empty($selectedYear) || empty($selectedTerm) || empty($selectedExamId)) {
                echo json_encode(['success' => false, 'message' => $_L['select_filters_message']]);
                $conn->close();
                exit;
            }

            $reports = [];
            $total_marks_sum = 0;
            $total_subjects_count = 0;
            $student_class_id = null;
            $student_report_details = []; // Initialize student details for the report

            // Fetch student's full details including class, stream, parent info
            $sql_student_report_details = "
                SELECT
                    u.ADM,
                    u.photo,
                    u.username,
                    u.class_id,
                    c.class_name,
                    st.stream_name,
                    u.parent_name,
                    u.parent_phone,
                    u.parent_email
                FROM
                    users u
                LEFT JOIN
                    classes c ON u.class_id = c.class_id
                LEFT JOIN
                    streams st ON u.stream_id = st.stream_id
                WHERE
                    u.id = ? AND u.school_id = ?;
            ";
            if ($stmt_student_report_details = $conn->prepare($sql_student_report_details)) {
                $stmt_student_report_details->bind_param("ii", $student_user_id, $school_id);
                if ($stmt_student_report_details->execute()) {
                    $result_student_report_details = $stmt_student_report_details->get_result();
                    if ($row = $result_student_report_details->fetch_assoc()) {
                        $student_report_details = $row;
                        $student_class_id = $row['class_id']; // Get class_id from this query for grading scales
                    }
                }
                $stmt_student_report_details->close();
            }

            if ($student_class_id === null) {
                echo json_encode(['success' => false, 'message' => 'Student class information not found.']);
                $conn->close();
                exit;
            }

            // Add school and exam name for display purposes (already available globally or fetched)
            $student_report_details['school_name'] = $school_name;
            $student_report_details['school_logo_path'] = $school_logo_path;
            // Fetch exam name here for display
            $exam_name = '';
            $sql_exam_name_fetch = "SELECT exam_name FROM exams WHERE exam_id = ? AND school_id = ?";
            if ($stmt_exam_name_fetch = $conn->prepare($sql_exam_name_fetch)) {
                $stmt_exam_name_fetch->bind_param("ii", $selectedExamId, $school_id);
                if ($stmt_exam_name_fetch->execute()) {
                    $stmt_exam_name_fetch->bind_result($exam_name_db);
                    if ($stmt_exam_name_fetch->fetch()) {
                        $exam_name = $exam_name_db;
                    }
                }
                $stmt_exam_name_fetch->close();
            }
            $student_report_details['exam_name'] = $exam_name;


            // Fetch marks and associated grading for the student, year, term, and exam
            $sql_reports = "
                SELECT
                    m.subject_id,
                    s.subject_name,
                    m.mark,
                    gs.grade_letter,
                    gs.remarks
                FROM
                    marks m
                JOIN
                    subjects s ON m.subject_id = s.subject_id
                LEFT JOIN
                    grading_scales gs ON m.school_id = gs.school_id
                        AND m.academic_year = gs.academic_year
                        AND m.term = gs.term_name
                        AND (gs.class_id = 0 OR gs.class_id = ?)
                        AND (gs.subject_id = 0 OR gs.subject_id = m.subject_id)
                        AND m.mark BETWEEN gs.min_score AND gs.max_score
                WHERE
                    m.student_id = ?
                    AND m.school_id = ?
                    AND m.academic_year = ?
                    AND m.term = ?
                    AND m.exam_id = ?
                ORDER BY
                    s.subject_name ASC;
            ";
            if ($stmt_reports = $conn->prepare($sql_reports)) {
                $stmt_reports->bind_param("iiissi", $student_class_id, $student_user_id, $school_id, $selectedYear, $selectedTerm, $selectedExamId);
                if ($stmt_reports->execute()) {
                    $result_reports = $stmt_reports->get_result();
                    while ($row = $result_reports->fetch_assoc()) {
                        $reports[] = $row;
                        $total_marks_sum += $row['mark'];
                        $total_subjects_count++;
                    }
                } else {
                    error_log("Execute failed for reports query: " . $stmt_reports->error);
                    echo json_encode(['success' => false, 'message' => 'Error executing reports query.']);
                    $conn->close();
                    exit;
                }
                $stmt_reports->close();
            } else {
                error_log("Prepare failed for reports query: " . $conn->error);
                echo json_encode(['success' => false, 'message' => 'Error preparing reports query.']);
                $conn->close();
                exit;
            }

            $overall_average = $total_subjects_count > 0 ? round($total_marks_sum / $total_subjects_count, 2) : 0;
            $overall_grade_letter = 'N/A';
            $overall_remarks = 'N/A';

            // Determine overall grade and remarks based on average score (general grading scale)
            $sql_overall_grading = "
                SELECT grade_letter, remarks
                FROM grading_scales
                WHERE school_id = ?
                    AND academic_year = ?
                    AND term_name = ?
                    AND class_id = 0 AND subject_id = 0 -- General grading scale
                    AND ? BETWEEN min_score AND max_score
                LIMIT 1;
            ";
            if ($stmt_overall_grading = $conn->prepare($sql_overall_grading)) {
                $stmt_overall_grading->bind_param("issi", $school_id, $selectedYear, $selectedTerm, $overall_average);
                if ($stmt_overall_grading->execute()) {
                    $stmt_overall_grading->bind_result($grade_letter_db, $remarks_db);
                    if ($stmt_overall_grading->fetch()) {
                        $overall_grade_letter = $grade_letter_db;
                        $overall_remarks = $remarks_db;
                    }
                }
                $stmt_overall_grading->close();
            }

            echo json_encode([
                'success' => true,
                'reports' => $reports,
                'overall_performance' => [
                    'total_score' => $total_marks_sum,
                    'average_score' => $overall_average,
                    'overall_grade' => $overall_grade_letter,
                    'overall_remarks' => $overall_remarks
                ],
                'student_details' => $student_report_details // Include student details
            ]);

        } catch (Exception $e) {
            error_log("AJAX request error in student_reports.php: " . $e->getMessage());
            echo json_encode(['success' => false, 'message' => 'An unexpected error occurred.']);
        } finally {
            if (isset($conn) && $conn instanceof mysqli) {
                // Keep the connection open for the final HTML render, only close here if we are exiting the script
                // $conn->close();
            }
        }
        exit;
    }

    // Handle AJAX request for downloading PDF report card
    if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['action']) && $_POST['action'] == 'download_report_card') {
        ini_set('display_errors', 0);
        error_reporting(E_ALL & ~E_NOTICE & ~E_WARNING);

        if (!isset($conn) || !$conn instanceof mysqli) {
            require 'config.php';
            if (!isset($conn) || !$conn instanceof mysqli) {
                error_log('Database connection error for download request.');
                // Fallback to text error if headers already sent
                echo 'Error: Database connection failed.';
                exit;
            }
        }

        $selectedYear = htmlspecialchars(filter_input(INPUT_POST, 'academic_year', FILTER_SANITIZE_FULL_SPECIAL_CHARS) ?: '');
        $selectedTerm = htmlspecialchars(filter_input(INPUT_POST, 'term', FILTER_SANITIZE_FULL_SPECIAL_CHARS) ?: '');
        $selectedExamId = filter_input(INPUT_POST, 'exam_id', FILTER_VALIDATE_INT);

        if (empty($selectedYear) || empty($selectedTerm) || empty($selectedExamId)) {
            // Send back a plain text error for download
            echo 'Error: Missing parameters for report card generation.';
            $conn->close();
            exit;
        }

        // Re-fetch report data for PDF generation (same logic as fetch_report_card)
        $reports_for_pdf = [];
        $total_marks_sum_pdf = 0;
        $total_subjects_count_pdf = 0;
        $student_class_id_pdf = null;
        $exam_name_pdf = '';
        $student_report_details_pdf = []; // Initialize student details for PDF

        // Fetch student's full details for PDF including class, stream, parent info
        $sql_student_report_details_pdf = "
            SELECT
                u.ADM,
                u.photo,
                u.username,
                u.class_id,
                c.class_name,
                st.stream_name,
                u.parent_name,
                u.parent_phone,
                u.parent_email
            FROM
                users u
            LEFT JOIN
                classes c ON u.class_id = c.class_id
            LEFT JOIN
                streams st ON u.stream_id = st.stream_id
            WHERE
                u.id = ? AND u.school_id = ?;
        ";
        if ($stmt_student_report_details_pdf = $conn->prepare($sql_student_report_details_pdf)) {
            $stmt_student_report_details_pdf->bind_param("ii", $student_user_id, $school_id);
            if ($stmt_student_report_details_pdf->execute()) {
                $result_student_report_details_pdf = $stmt_student_report_details_pdf->get_result();
                if ($row = $result_student_report_details_pdf->fetch_assoc()) {
                    $student_report_details_pdf = $row;
                    $student_class_id_pdf = $row['class_id']; // Use this class_id for grading scales
                }
            }
            $stmt_student_report_details_pdf->close();
        }

        if ($student_class_id_pdf === null) {
            echo 'Error: Student class information not found for PDF generation.';
            $conn->close();
            exit;
        }

        // Get school name and logo path (already globally available)
        $student_report_details_pdf['school_name'] = $school_name;
        $student_report_details_pdf['school_logo_path'] = $school_logo_path;

        // Get exam name
        $sql_exam_name = "SELECT exam_name FROM exams WHERE exam_id = ? AND school_id = ?";
        if ($stmt_exam_name = $conn->prepare($sql_exam_name)) {
            $stmt_exam_name->bind_param("ii", $selectedExamId, $school_id);
            if ($stmt_exam_name->execute()) {
                $stmt_exam_name->bind_result($exam_name_db);
                if ($stmt_exam_name->fetch()) {
                    $exam_name_pdf = $exam_name_db;
                }
            }
            $stmt_exam_name->close();
        }


        $sql_reports_pdf = "
            SELECT
                m.subject_id,
                s.subject_name,
                m.mark,
                gs.grade_letter,
                gs.remarks
            FROM
                marks m
            JOIN
                subjects s ON m.subject_id = s.subject_id
            LEFT JOIN
                grading_scales gs ON m.school_id = gs.school_id
                    AND m.academic_year = gs.academic_year
                    AND m.term = gs.term_name
                    AND (gs.class_id = 0 OR gs.class_id = ?)
                    AND (gs.subject_id = 0 OR gs.subject_id = m.subject_id)
                    AND m.mark BETWEEN gs.min_score AND gs.max_score
            WHERE
                m.student_id = ?
                AND m.school_id = ?
                AND m.academic_year = ?
                AND m.term = ?
                AND m.exam_id = ?
            ORDER BY
                s.subject_name ASC;
        ";
        if ($stmt_reports_pdf = $conn->prepare($sql_reports_pdf)) {
            $stmt_reports_pdf->bind_param("iiissi", $student_class_id_pdf, $student_user_id, $school_id, $selectedYear, $selectedTerm, $selectedExamId);
            if ($stmt_reports_pdf->execute()) {
                $result_reports_pdf = $stmt_reports_pdf->get_result();
                while ($row = $result_reports_pdf->fetch_assoc()) {
                    $reports_for_pdf[] = $row;
                    $total_marks_sum_pdf += $row['mark'];
                    $total_subjects_count_pdf++;
                }
            } else {
                error_log("Execute failed for PDF reports query: " . $stmt_reports_pdf->error);
                echo 'Error: Failed to fetch report data for PDF.';
                $conn->close();
                exit;
            }
            $stmt_reports_pdf->close();
        } else {
            error_log("Prepare failed for PDF reports query: " . $conn->error);
            echo 'Error: Failed to prepare PDF report query.';
            $conn->close();
            exit;
        }

        $overall_average_pdf = $total_subjects_count_pdf > 0 ? round($total_marks_sum_pdf / $total_subjects_count_pdf, 2) : 0;
        $overall_grade_letter_pdf = 'N/A';
        $overall_remarks_pdf = 'N/A';

        // Determine overall grade and remarks based on average score (general grading scale) for PDF
        $sql_overall_grading_pdf = "
            SELECT grade_letter, remarks
            FROM grading_scales
            WHERE school_id = ?
                AND academic_year = ?
                AND term_name = ?
                AND class_id = 0 AND subject_id = 0 -- General grading scale
                AND ? BETWEEN min_score AND max_score
            LIMIT 1;
        ";
        if ($stmt_overall_grading_pdf = $conn->prepare($sql_overall_grading_pdf)) {
            $stmt_overall_grading_pdf->bind_param("issi", $school_id, $selectedYear, $selectedTerm, $overall_average_pdf);
            if ($stmt_overall_grading_pdf->execute()) {
                $stmt_overall_grading_pdf->bind_result($grade_letter_db, $remarks_db);
                if ($stmt_overall_grading_pdf->fetch()) {
                    $overall_grade_letter_pdf = $grade_letter_db;
                    $overall_remarks_pdf = $remarks_db;
                }
            }
            $stmt_overall_grading_pdf->close();
        }

        $conn->close(); // Close connection before outputting file

        // --- CONCEPTUAL PDF GENERATION ---
        // Since we cannot run FPDF/TCPDF here, we will output a message indicating
        // what the PDF should contain in a real environment.

        $pdf_content = "
            <!DOCTYPE html>
            <html lang=\"en\">
            <head>
                <meta charset=\"UTF-8\">
                <title>Report Card PDF Preview</title>
                <style>
                    body { font-family: sans-serif; padding: 20px; }
                    .header { text-align: center; border-bottom: 2px solid #333; margin-bottom: 20px; }
                    .details, .summary { margin-bottom: 20px; border: 1px solid #ccc; padding: 10px; border-radius: 5px; }
                    table { width: 100%; border-collapse: collapse; }
                    th, td { border: 1px solid #ccc; padding: 8px; text-align: left; }
                    .performance { background-color: #f4f4f4; font-weight: bold; }
                    .disclaimer { font-size: 0.8em; color: #666; margin-top: 30px; }
                </style>
            </head>
            <body>
                <div class=\"header\">
                    <img src=\"" . htmlspecialchars($student_report_details_pdf['school_logo_path']) . "\" alt=\"School Logo\" style=\"height: 50px;\">
                    <h1>" . htmlspecialchars($student_report_details_pdf['school_name']) . "</h1>
                    <h2>{$_L['reports_heading']} - " . htmlspecialchars($exam_name_pdf) . "</h2>
                    <p><strong>{$_L['academic_year_column']}:</strong> " . htmlspecialchars($selectedYear) . " | <strong>{$_L['term_column']}:</strong> " . htmlspecialchars($selectedTerm) . "</p>
                </div>

                <div class=\"details\">
                    <h3>Student Details</h3>
                    <p><strong>{$_L['student_name_label']}</strong> " . htmlspecialchars($student_report_details_pdf['username']) . "</p>
                    <p><strong>{$_L['admission_no_label']}</strong> " . htmlspecialchars($student_report_details_pdf['ADM'] ?: 'N/A') . "</p>
                    <p><strong>{$_L['class_label']}</strong> " . htmlspecialchars($student_report_details_pdf['class_name'] ?: 'N/A') . " | <strong>{$_L['stream_label']}</strong> " . htmlspecialchars($student_report_details_pdf['stream_name'] ?: 'N/A') . "</p>
                </div>

                <table>
                    <thead>
                        <tr>
                            <th>{$_L['subject_column']}</th>
                            <th>{$_L['score_column']}</th>
                            <th>{$_L['grade_column']}</th>
                            <th>{$_L['remarks_column']}</th>
                        </tr>
                    </thead>
                    <tbody>
        ";

        if (count($reports_for_pdf) > 0) {
            foreach ($reports_for_pdf as $report) {
                $pdf_content .= "
                    <tr>
                        <td>" . htmlspecialchars($report['subject_name']) . "</td>
                        <td>" . htmlspecialchars($report['mark']) . "</td>
                        <td>" . htmlspecialchars($report['grade_letter'] ?: 'N/A') . "</td>
                        <td>" . htmlspecialchars($report['remarks'] ?: 'N/A') . "</td>
                    </tr>
                ";
            }
        } else {
             $pdf_content .= "<tr><td colspan='4'>{$_L['no_reports_found']}</td></tr>";
        }

        $pdf_content .= "
                    </tbody>
                </table>

                <div class=\"summary\">
                    <h3>{$_L['overall_performance']}</h3>
                    <p class='performance'>{$_L['total_score_label']} " . htmlspecialchars($total_marks_sum_pdf) . "</p>
                    <p class='performance'>{$_L['average_score_label']} " . htmlspecialchars($overall_average_pdf) . "</p>
                    <p class='performance'>{$_L['overall_grade_label']} " . htmlspecialchars($overall_grade_letter_pdf) . "</p>
                    <p class='performance'>{$_L['overall_remarks_label']} " . htmlspecialchars($overall_remarks_pdf) . "</p>
                </div>

                <div class='disclaimer'>
                    <p>Note: This is a system-generated report card. Final validity depends on school sign-off.</p>
                </div>
            </body>
            </html>
        ";


        // In a real server environment, you would use a library to convert $pdf_content (HTML) to PDF and output headers.
        // For this environment, we just output the HTML content, which the user can manually print or convert to PDF.
        header('Content-Type: text/html');
        echo $pdf_content;
        exit;
    }
}
// Closing the DB connection if it's still open and we are proceeding to HTML output.
if (isset($conn) && $conn instanceof mysqli) {
    $conn->close();
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo $_L['sidebar_reports']; ?> - <?php echo $school_name; ?></title>
    <link rel="stylesheet" href="style.css">
    <link rel="icon" href="<?php echo $school_logo_path; ?>" type="image/png">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.15.4/css/all.min.css">
    <script src="https://cdn.tailwindcss.com"></script>
    <style>
        /* Fixed Header and Sidebar Styles */

        .dashboard-container {
            display: flex;
            height: 100vh;
            overflow: hidden;
        }

        .sidebar {
            position: fixed;
            left: 0;
            top: 0;
            height: 100vh;
            width: 250px;
            overflow-y: auto;
            z-index: 1000;
            box-shadow: 2px 0 8px rgba(0, 0, 0, 0.1);
        }

        .main-content {
            margin-left: 250px;
            width: calc(100% - 250px);
            height: 100vh;
            display: flex;
            flex-direction: column;
            overflow: hidden;
        }

        .header {
            position: fixed;
            top: 0;
            left: 250px;
            right: 0;
            z-index: 999;
            background-color: #ffffff;
            box-shadow: 0 2px 8px rgba(0, 0, 0, 0.1);
        }

        /* Scrollable content area with fade effect */

        .content-wrapper {
            flex: 1;
            overflow-y: auto;
            overflow-x: hidden;
            margin-top: 80px; /* Adjust based on header height */
            position: relative;
        }

        /* Fade overlay at the top */
        .content-wrapper::before {
            content: '';
            position: sticky;
            top: 0;
            left: 0;
            right: 0;
            height: 40px;
            background: linear-gradient(to bottom, rgba(255, 255, 255, 1) 0%, rgba(255, 255, 255, 0) 100%);
            pointer-events: none;
            z-index: 10;
        }

        /* Smooth scrolling */
        .content-wrapper {
            scroll-behavior: smooth;
        }

        /* Custom scrollbar */
        .content-wrapper::-webkit-scrollbar {
            width: 8px;
        }

        .content-wrapper::-webkit-scrollbar-track {
            background: #f1f1f1;
        }

        .content-wrapper::-webkit-scrollbar-thumb {
            background: #888;
            border-radius: 4px;
        }

        .content-wrapper::-webkit-scrollbar-thumb:hover {
            background: #555;
        }

        /* Responsive adjustments */
        @media (max-width: 768px) {
            .sidebar {
                transform: translateX(-100%);
                transition: transform 0.3s ease-in-out;
            }

            .sidebar.active {
                transform: translateX(0);
            }

            .main-content {
                margin-left: 0;
                width: 100%;
            }

            .header {
                left: 0;
            }
        }

        /* ========== REPORTS SPECIFIC STYLES ========== */
        .page-title {
            font-size: 28px;
            color: #1e3a8a;
            margin-bottom: 20px;
            padding-bottom: 10px;
            border-bottom: 2px solid #e5e7eb;
        }

        .filter-section {
            background: white;
            padding: 25px;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            margin-bottom: 30px;
        }

        .filter-section h2 {
            font-size: 18px;
            color: #1e3a8a;
            margin-bottom: 20px;
        }

        .filter-form {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 15px;
        }

        .filter-form select,
        .filter-form button {
            padding: 12px;
            border: 1px solid #d1d5db;
            border-radius: 6px;
            font-size: 14px;
        }

        .filter-form select {
            background: white;
            cursor: pointer;
        }

        .filter-form button {
            background: #10b981;
            color: white;
            font-weight: bold;
            cursor: pointer;
            transition: background 0.3s ease;
        }

        .filter-form button:hover {
            background: #059669;
        }

        .report-output {
            background: white;
            padding: 30px;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
            min-height: 400px;
        }

        .initial-message {
            text-align: center;
            color: #9ca3af;
            font-size: 16px;
            padding: 60px 20px;
        }

        .student-info-card {
            background: #f9fafb;
            padding: 20px;
            border-radius: 8px;
            margin-bottom: 25px;
            border: 1px solid #e5e7eb;
        }

        .student-info-card h2 {
            font-size: 20px;
            color: #1e3a8a;
            margin-bottom: 15px;
            padding-bottom: 10px;
            border-bottom: 2px solid #e5e7eb;
        }

        .student-details {
            display: flex;
            align-items: center;
            gap: 20px;
        }

        .student-details img {
            width: 80px;
            height: 80px;
            border-radius: 50%;
            object-fit: cover;
            border: 3px solid #10b981;
        }

        .student-details-text p {
            margin: 5px 0;
            color: #4b5563;
        }

        .student-details-text .name {
            font-size: 18px;
            font-weight: bold;
            color: #1e3a8a;
        }

        .report-table-wrapper {
            overflow-x: auto;
            margin-bottom: 25px;
        }

        .report-table {
            width: 100%;
            border-collapse: collapse;
        }

        .report-table thead {
            background: #1e3a8a;
            color: white;
        }

        .report-table th,
        .report-table td {
            padding: 12px;
            text-align: left;
            border: 1px solid #e5e7eb;
        }

        .report-table tbody tr:hover {
            background: #f9fafb;
        }

        .overall-performance {
            background: #f0fdf4;
            padding: 20px;
            border-radius: 8px;
            border: 2px solid #10b981;
            margin-bottom: 25px;
        }

        .overall-performance h2 {
            font-size: 18px;
            color: #10b981;
            margin-bottom: 15px;
        }

        .performance-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(150px, 1fr));
            gap: 15px;
        }

        .performance-item {
            background: white;
            padding: 15px;
            border-radius: 6px;
            text-align: center;
        }

        .performance-item label {
            display: block;
            font-size: 12px;
            color: #6b7280;
            margin-bottom: 5px;
        }

        .performance-item .value {
            font-size: 20px;
            font-weight: bold;
            color: #1e3a8a;
        }

        .download-btn {
            width: 100%;
            padding: 15px;
            background: #f59e0b;
            color: white;
            border: none;
            border-radius: 8px;
            font-size: 16px;
            font-weight: bold;
            cursor: pointer;
            transition: background 0.3s ease;
        }

        .download-btn:hover {
            background: #d97706;
        }

        .download-btn i {
            margin-right: 8px;
        }

        .loading-overlay {
            position: fixed;
            top: 0;
            left: 0;
            right: 0;
            bottom: 0;
            background: rgba(0, 0, 0, 0.7);
            display: flex;
            align-items: center;
            justify-content: center;
            z-index: 9999;
        }

        .loading-content {
            text-align: center;
            color: white;
        }

        .spinner {
            border: 4px solid rgba(255, 255, 255, 0.3);
            border-top: 4px solid white;
            border-radius: 50%;
            width: 50px;
            height: 50px;
            animation: spin 1s linear infinite;
            margin: 0 auto 15px;
        }

        @keyframes spin {
            0% { transform: rotate(0deg); }
            100% { transform: rotate(360deg); }
        }

        .modal {
            position: fixed;
            top: 0;
            left: 0;
            right: 0;
            bottom: 0;
            background: rgba(0, 0, 0, 0.5);
            display: flex;
            align-items: center;
            justify-content: center;
            z-index: 10000;
        }

        .modal-content {
            background: white;
            padding: 30px;
            border-radius: 10px;
            max-width: 400px;
            width: 90%;
        }

        .modal-content h3 {
            color: #1e3a8a;
            margin-bottom: 15px;
        }

        .modal-content p {
            color: #4b5563;
            margin-bottom: 20px;
        }

        .modal-content button {
            width: 100%;
            padding: 12px;
            background: #10b981;
            color: white;
            border: none;
            border-radius: 6px;
            font-weight: bold;
            cursor: pointer;
        }

        .modal-content button:hover {
            background: #059669;
        }

        .hidden {
            display: none !important;
        }

        /* Responsive Design */
        @media (max-width: 768px) {
            .sidebar {
                width: 200px;
            }

            .header {
                left: 200px;
            }

            .main-content {
                margin-left: 200px;
            }
        }

        @media (max-width: 576px) {
            .sidebar {
                transform: translateX(-100%);
                transition: transform 0.3s ease;
            }

            .sidebar.active {
                transform: translateX(0);
            }

            .header {
                left: 0;
            }

            .main-content {
                margin-left: 0;
            }
        }
    </style>
</head>
<body>
    <div class="dashboard-container" id="dashboardContainer">
        <aside class="sidebar" id="sidebar">
            <div class="logo">
                <img src="images/logo.png" alt="divine Logo">
                <?php echo $_L['divine_reports']; ?>
            </div>
            <nav>
                <ul>
                    <li class="<?php echo ($currentPage == 'dashboard' ? 'active' : ''); ?>"><a href="student_dashboard.php"><i class="fas fa-th-large"></i> <?php echo $_L['sidebar_dashboard']; ?></a></li>
                    <li class="<?php echo ($currentPage == 'profile' ? 'active' : ''); ?>"><a href="student_profile.php"><i class="fas fa-user-circle"></i> <?php echo $_L['sidebar_profile']; ?></a></li>
                    <li class="<?php echo ($currentPage == 'reports' ? 'active' : ''); ?>"><a href="student_reports.php"><i class="fas fa-file-invoice"></i> <?php echo $_L['sidebar_reports']; ?></a></li>
                    <li class="<?php echo ($currentPage == 'assignments' ? 'active' : ''); ?>"><a href="student_assignments.php"><i class="fas fa-file-alt"></i> <?php echo $_L['sidebar_assignments']; ?></a></li>
                    <li class="<?php echo ($currentPage == 'messages' ? 'active' : ''); ?>"><a href="student_messages.php"><i class="fas fa-envelope"></i> <?php echo $_L['sidebar_messages']; ?></a></li>
                    <li class="<?php echo ($currentPage == 'settings' ? 'active' : ''); ?>"><a href="student_settings.php"><i class="fas fa-cog"></i> <?php echo $_L['sidebar_settings']; ?></a></li>
                    <li><a href="logout.php"><i class="fas fa-sign-out-alt"></i> <?php echo $_L['sidebar_logout']; ?></a></li>
                </ul>
            </nav>
        </aside>

        <div class="main-content">
            <header class="header">
                <button class="menu-toggle" id="menuToggle">
                    <i class="fas fa-bars"></i>
                </button>
                <div class="school-info">
                    <img src="<?php echo $school_logo_path; ?>" alt="<?php echo $school_name; ?> Logo">
                    <h1><?php echo $school_name; ?></h1>
                </div>
                <div class="header-right">
                    <div class="language-select">
                        <select id="languageSelector" onchange="changeLanguage(this.value)">
                           <option value="en" <?php if ($current_lang === 'en') echo 'selected'; ?>>🇬🇧 English</option>
                           <option value="lug" <?php if ($current_lang === 'lug') echo 'selected'; ?>>🇺🇬 Luganda</option>
                           <option value="sw" <?php if ($current_lang === 'sw') echo 'selected'; ?>>🇹🇿 Kiswahili</option>
                        </select>
                    </div>
                    <div class="search-bar">
                        <i class="fas fa-search"></i>
                        <input type="text" placeholder="<?php echo $_L['search_placeholder']; ?>">
                    </div>
                    <div class="notifications">
                        <i class="fas fa-bell"></i>
                        <span class="badge">3</span>
                    </div>
                    <div class="user-profile">
                        <img src="<?php echo $student_photo; ?>" alt="User Photo">
                        <div>
                            <div class="user-name"><?php echo $username; ?></div>
                            <div class="user-email"><?php echo $_L['logged_in_as']; ?> <?php echo $role_name; ?></div>
                        </div>
                        <i class="fas fa-chevron-down"></i>
                    </div>
                </div>
            </header>

            <div class="content-wrapper">
                <div class="container mx-auto p-6 lg:p-8">

                <!-- Report Filters Section -->
                <section class="bg-white rounded-lg shadow-md p-6 mb-6">
                    <h2 class="text-xl font-semibold text-gray-800 mb-4">Filter Report</h2>
                    <form id="report-filter-form" class="grid grid-cols-1 md:grid-cols-4 gap-4">
                        <!-- Academic Year Dropdown -->
                        <select id="academic_year" name="academic_year" required class="px-4 py-2 border border-gray-300 rounded-md focus:ring-2 focus:ring-blue-500">
                            <option value="" disabled selected><?= $_L['select_academic_year'] ?></option>
                            <?php foreach ($academicYears as $year): ?>
                                <option value="<?= htmlspecialchars($year) ?>"><?= htmlspecialchars($year) ?></option>
                            <?php endforeach; ?>
                        </select>

                        <!-- Term Dropdown -->
                        <select id="term" name="term" required class="px-4 py-2 border border-gray-300 rounded-md focus:ring-2 focus:ring-blue-500">
                            <option value="" disabled selected><?= $_L['select_term'] ?></option>
                            <?php foreach ($terms as $term): ?>
                                <option value="<?= htmlspecialchars($term) ?>"><?= htmlspecialchars($term) ?></option>
                            <?php endforeach; ?>
                        </select>

                        <!-- Exam Type Dropdown -->
                        <select id="exam_id" name="exam_id" required class="px-4 py-2 border border-gray-300 rounded-md focus:ring-2 focus:ring-blue-500">
                            <option value="" disabled selected><?= $_L['select_exam_type'] ?></option>
                            <?php foreach ($exams as $exam): ?>
                                <option value="<?= htmlspecialchars($exam['id']) ?>"><?= htmlspecialchars($exam['name']) ?></option>
                            <?php endforeach; ?>
                        </select>

                        <!-- View Report Button -->
                        <button type="submit" id="view-report-btn" class="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded-md transition duration-150">
                            <?= $_L['view_report_button'] ?>
                        </button>
                    </form>
                </section>

                <!-- Dynamic Report Output Area -->
                <section id="report-output" class="bg-white rounded-lg shadow-md p-6">
                    <p id="initial-message" class="text-gray-500 text-center py-12">
                        <?= $_L['select_filters_message'] ?>
                    </p>
                    <!-- Report content will be injected here by JavaScript -->
                </section>
            </div>
            </div>
        </div>
    </div>

    <!-- Loading Indicator -->
    <div id="loading-indicator" class="hidden fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center z-50">
        <div class="bg-white rounded-lg p-6 text-center">
            <div class="inline-block animate-spin rounded-full h-12 w-12 border-b-2 border-blue-600 mb-4"></div>
            <p class="text-gray-700 font-semibold">Loading Report...</p>
        </div>
    </div>

    <!-- Modal for Messages -->
    <div id="message-modal" class="hidden fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center z-50">
        <div class="bg-white rounded-lg shadow-xl p-6 max-w-sm w-full mx-4">
            <h3 class="text-lg font-bold text-gray-800 mb-3">Notice</h3>
            <p id="modal-text" class="text-gray-600 mb-4"></p>
            <button onclick="document.getElementById('message-modal').classList.add('hidden');" class="w-full bg-blue-600 hover:bg-blue-700 text-white font-semibold py-2 rounded-md transition duration-150">
                Close
            </button>
        </div>
    </div>

    <script>
        function changeLanguage(lang) {
            window.location.href = `?lang=${lang}`;
        }

        document.addEventListener('DOMContentLoaded', function() {
            const menuToggle = document.getElementById('menuToggle');
            const sidebar = document.getElementById('sidebar');
            const dashboardContainer = document.getElementById('dashboardContainer');

            if (menuToggle && sidebar && dashboardContainer) {
                menuToggle.addEventListener('click', function() {
                    sidebar.classList.toggle('active');
                    dashboardContainer.classList.toggle('sidebar-open');
                });

                dashboardContainer.addEventListener('click', function(event) {
                    if (sidebar.classList.contains('active') && !sidebar.contains(event.target) && !menuToggle.contains(event.target)) {
                        sidebar.classList.remove('active');
                        dashboardContainer.classList.remove('sidebar-open');
                    }
                });
            }
        });

        const messageModal = document.getElementById('message-modal');
        const modalText = document.getElementById('modal-text');
        const loadingIndicator = document.getElementById('loading-indicator');
        const reportOutput = document.getElementById('report-output');
        const initialMessage = document.getElementById('initial-message');

        /**
         * Custom function to display messages, replacing alert().
         * @param {string} message The message to display.
         */
        function displayMessage(message) {
            modalText.textContent = message;
            messageModal.classList.remove('hidden');
        }

        /**
         * Shows or hides the loading spinner.
         * @param {boolean} show
         */
        function toggleLoading(show) {
            if (show) {
                loadingIndicator.classList.remove('hidden');
                reportOutput.innerHTML = '';
                if (initialMessage) initialMessage.style.display = 'none';
            } else {
                loadingIndicator.classList.add('hidden');
            }
        }

        /**
         * Renders the academic report card dynamically.
         * @param {Object} data The report data (reports, overall_performance, student_details).
         */
        function renderReport(data) {
            const reports = data.reports;
            const overall = data.overall_performance;
            const student = data.student_details;
            const L = <?= json_encode($_L) ?>; // Localized strings

            if (reports.length === 0) {
                reportOutput.innerHTML = `<p class="text-red-500 text-center py-12">${L.no_reports_found}</p>`;
                return;
            }

            // 1. Student Info Card
            const studentInfoHTML = `
                <div class="bg-gradient-to-r from-blue-50 to-indigo-50 rounded-lg p-6 mb-6 border border-blue-200">
                    <h2 class="text-2xl font-bold text-blue-800 mb-4">${student.school_name}</h2>
                    <p class="text-sm text-gray-600 mb-4">
                        <span class="font-semibold">${L.reports_heading}</span> - <span class="text-blue-600">${student.exam_name}</span><br>
                        ${L.term_column}: ${document.getElementById('term').value} | ${L.academic_year_column}: ${document.getElementById('academic_year').value}
                    </p>
                    <div class="flex items-center gap-4">
                        <img src="${student.photo || 'images/default_student_photo.png'}"
                            onerror="this.onerror=null;this.src='images/default_student_photo.png';"
                            class="w-20 h-20 rounded-full object-cover border-4 border-blue-300" alt="Student Photo">
                        <div>
                            <p class="text-xl font-bold text-gray-800">${student.username}</p>
                            <p class="text-sm text-gray-600">${L.admission_no_label} ${student.ADM || 'N/A'}</p>
                            <p class="text-sm text-gray-600">${L.class_label} ${student.class_name || 'N/A'} | ${L.stream_label} ${student.stream_name || 'N/A'}</p>
                        </div>
                    </div>
                </div>
            `;

            // 2. Subject Marks Table
            let tableRows = reports.map(r => `
                <tr class="border-b hover:bg-gray-50 transition">
                    <td class="px-4 py-3 font-medium text-gray-800">${r.subject_name}</td>
                    <td class="px-4 py-3 text-blue-600 font-bold">${r.mark}</td>
                    <td class="px-4 py-3 text-green-600 font-semibold">${r.grade_letter || 'N/A'}</td>
                    <td class="px-4 py-3 text-gray-600 text-sm">${r.remarks || 'N/A'}</td>
                </tr>
            `).join('');

            const reportTableHTML = `
                <div class="overflow-x-auto mb-6">
                    <table class="w-full border-collapse bg-white rounded-lg overflow-hidden shadow-sm">
                        <thead class="bg-blue-600 text-white">
                            <tr>
                                <th class="px-4 py-3 text-left font-semibold">${L.subject_column}</th>
                                <th class="px-4 py-3 text-left font-semibold">${L.score_column}</th>
                                <th class="px-4 py-3 text-left font-semibold">${L.grade_column}</th>
                                <th class="px-4 py-3 text-left font-semibold">${L.remarks_column}</th>
                            </tr>
                        </thead>
                        <tbody>
                            ${tableRows}
                        </tbody>
                    </table>
                </div>
            `;

            // 3. Overall Performance Summary
            const overallSummaryHTML = `
                <div class="bg-green-50 border-2 border-green-200 rounded-lg p-6 mb-6">
                    <h2 class="text-xl font-bold text-green-700 mb-4">${L.overall_performance}</h2>
                    <div class="grid grid-cols-2 md:grid-cols-4 gap-4">
                        <div class="bg-white rounded-lg p-4 text-center shadow-sm">
                            <p class="text-xs text-gray-500 mb-1">${L.total_score_label}</p>
                            <p class="text-2xl font-bold text-blue-600">${overall.total_score}</p>
                        </div>
                        <div class="bg-white rounded-lg p-4 text-center shadow-sm">
                            <p class="text-xs text-gray-500 mb-1">${L.average_score_label}</p>
                            <p class="text-2xl font-bold text-blue-600">${overall.average_score}</p>
                        </div>
                        <div class="bg-white rounded-lg p-4 text-center shadow-sm">
                            <p class="text-xs text-gray-500 mb-1">${L.overall_grade_label}</p>
                            <p class="text-2xl font-bold text-green-600">${overall.overall_grade}</p>
                        </div>
                        <div class="bg-white rounded-lg p-4 text-center shadow-sm">
                            <p class="text-xs text-gray-500 mb-1">${L.overall_remarks_label}</p>
                            <p class="text-lg font-bold text-green-600">${overall.overall_remarks}</p>
                        </div>
                    </div>
                </div>
            `;

            // 4. Download Button
            const downloadButtonHTML = `
                <button id="download-report-btn" class="w-full bg-orange-500 hover:bg-orange-600 text-white font-bold py-3 px-6 rounded-lg transition duration-150 shadow-md">
                    <i class="fas fa-download mr-2"></i> ${L.download_report_button}
                </button>
            `;

            reportOutput.innerHTML = studentInfoHTML + reportTableHTML + overallSummaryHTML + downloadButtonHTML;
            // Re-attach download listener
            document.getElementById('download-report-btn').addEventListener('click', handleDownload);
        }

        /**
         * Handles the form submission to fetch the report data via AJAX.
         * @param {Event} e
         */
        async function handleViewReport(e) {
            e.preventDefault();

            const form = document.getElementById('report-filter-form');
            const formData = new FormData(form);
            formData.append('action', 'fetch_report_card');

            const year = formData.get('academic_year');
            const term = formData.get('term');
            const exam = formData.get('exam_id');

            if (!year || !term || !exam) {
                displayMessage(<?= json_encode($_L['select_filters_message']) ?>);
                return;
            }

            toggleLoading(true);

            try {
                const response = await fetch('student_reports.php', {
                    method: 'POST',
                    body: formData
                });

                if (!response.ok) {
                    throw new Error('Network response was not ok.');
                }

                const result = await response.json();

                if (result.success) {
                    renderReport(result);
                } else {
                    // Display specific error message from PHP or generic fallback
                    reportOutput.innerHTML = `<p class="text-red-400 text-lg text-center p-6">${result.message || 'Failed to fetch report data.'}</p>`;
                }

            } catch (error) {
                console.error('AJAX Error:', error);
                displayMessage('An error occurred while fetching the report. Please try again later.');
                reportOutput.innerHTML = `<p class="text-red-400 text-lg text-center p-6">Error loading report data.</p>`;
            } finally {
                toggleLoading(false);
            }
        }

        /**
         * Handles the download button click.
         */
        function handleDownload() {
            const form = document.getElementById('report-filter-form');
            const year = form.elements['academic_year'].value;
            const term = form.elements['term'].value;
            const exam = form.elements['exam_id'].value;

            if (!year || !term || !exam) {
                displayMessage('Please view the report first before downloading.');
                return;
            }

            // Temporarily change form action/target for non-AJAX POST request (simulating file download)
            const originalAction = form.action;
            const originalMethod = form.method;
            const originalTarget = form.target;

            // Create a temporary hidden input for the download action
            const actionInput = document.createElement('input');
            actionInput.type = 'hidden';
            actionInput.name = 'action';
            actionInput.value = 'download_report_card';
            form.appendChild(actionInput);

            // Execute the non-AJAX submission
            form.action = 'student_reports.php';
            form.method = 'POST';
            form.target = '_self'; // Open in the same window
            form.submit();

            // Clean up the form after a short delay (allowing the browser to start download)
            setTimeout(() => {
                form.action = originalAction;
                form.method = originalMethod;
                form.target = originalTarget;
                form.removeChild(actionInput);
            }, 500);

            // NOTE: In a real environment, the PHP file would output the PDF headers and binary data here.
            // Since we can't do that, we rely on the browser's response handling.
            displayMessage('Download initiated. If the file does not appear, check your filters or try viewing the report first.');
        }


        // Event listeners setup
        document.addEventListener('DOMContentLoaded', () => {
            document.getElementById('report-filter-form').addEventListener('submit', handleViewReport);

            // Set current language selection on initial load (optional, useful if URL param is cleared)
            const currentLang = '<?= $current_lang ?>';
            document.querySelectorAll(`a[href*="?lang="]`).forEach(link => {
                if (link.href.includes(`lang=${currentLang}`)) {
                    link.classList.add('font-bold', 'text-accent-yellow');
                    link.classList.remove('text-gray-400');
                } else {
                    link.classList.add('text-gray-400');
                    link.classList.remove('font-bold', 'text-accent-yellow');
                }
            });
        });

    </script>
</body>
</html>
