<?php
session_name('super_admin_session');
if (!is_dir(dirname(__DIR__) . '/session_data/super')) { mkdir(dirname(__DIR__) . '/session_data/super', 0700, true); } session_save_path(dirname(__DIR__) . '/session_data/super');
session_set_cookie_params(['path' => '/', 'httponly' => true, 'samesite' => 'Strict']);
session_start();
error_reporting(0);
include('includes/dbconnection.php');

if (!isset($_SESSION['sid']) || strlen($_SESSION['sid']) == 0) {
    header('location:logout.php');
    exit();
}

require_once('vendor/autoload.php');

use Dotenv\Dotenv;
use Mpdf\Mpdf;

// Load environment variables
$dotenv = Dotenv::createImmutable(__DIR__);
$dotenv->load();

// Function to get the appropriate suffix for the position
function getPositionSuffix($position)
{
    if ($position % 100 >= 11 && $position % 100 <= 13) {
        return $position . 'th';
    }
    switch ($position % 10) {
        case 1:
            return $position . 'st';
        case 2:
            return $position . 'nd';
        case 3:
            return $position . 'rd';
        default:
            return $position . 'th';
    }
}

// Grading Logic
function getGrade($score, $class)
{
    if (strpos($class, 'JSS') !== false) {
        if ($score >= 70) return 'A';
        if ($score >= 50) return 'C';
        if ($score >= 40) return 'P';
        return 'F';
    } elseif (strpos($class, 'SS') !== false) {
        if ($score >= 75) return 'A1';
        if ($score >= 70) return 'B2';
        if ($score >= 65) return 'B3';
        if ($score >= 60) return 'C4';
        if ($score >= 55) return 'C5';
        if ($score >= 50) return 'C6';
        if ($score >= 45) return 'D7';
        if ($score >= 40) return 'E8';
        return 'F9';
    } else {
        if ($score >= 70) return 'A';
        if ($score >= 50) return 'C';
        if ($score >= 40) return 'P';
        return 'F';
    }
}

// Remark generator
function generateRemark($percentage)
{
    if ($percentage >= 75) {
        return 'Outstanding Performance!';
    } elseif ($percentage >= 60) {
        return 'Commendable performance!';
    } elseif ($percentage >= 50) {
        return 'Satisfactory Performance!';
    } elseif ($percentage >= 40) {
        return 'Marginal Performance!';
    } else {
        return 'Unsatisfactory Performance!';
    }
}

// Get filter parameters from URL
$session_id = $_GET['session_id'] ?? null;
$term_id = $_GET['term_id'] ?? null;
$class = $_GET['class'] ?? null;
$section = $_GET['section'] ?? null;

// Validate required parameters
if (!$session_id || !$term_id || !$class) {
    die("Session ID, Term ID, and Class are required parameters");
}

// Get selected names
$selected_session = mysqli_fetch_assoc(mysqli_query($con, "SELECT session_name FROM school_sessions WHERE id = '$session_id'"))['session_name'] ?? 'N/A';
$selected_term = mysqli_fetch_assoc(mysqli_query($con, "SELECT term_name FROM school_terms WHERE id = '$term_id'"))['term_name'] ?? 'N/A';

// Build students query based on section - ADD stream and demote_status
$students_query = "SELECT id, studentName, class, section, gender, age, studentImage, stream, demote_status FROM students WHERE class = '$class'";
if ($section && $section != 'All') {
    $students_query .= " AND section = '$section'";
}
$students_result = $con->query($students_query);

if (!$students_result || $students_result->num_rows == 0) {
    die("No students found in the selected class/section");
}

// Get environment variables
$school_name = $_ENV['SCHOOL_NAME_1'];
$logo = $_ENV['WEBSITE_LOGO'] ?? '';
$address = $_ENV['id_address'] ?? '';
$school_email = $_ENV['SCHOOL_EMAIL'] ?? '';
$signature = $_ENV['PRINCIPAL_SIGNATURE'] ?? '';
$website = $_ENV['SCHOOL_WEBSITE'] ?? '';

// Create unique download folder
$downloadFolder = 'temp_downloads_' . uniqid();
if (!file_exists($downloadFolder)) {
    mkdir($downloadFolder, 0777, true);
}

// Debug counter
$processed_count = 0;
$skipped_count = 0;

// Process each student
while ($student = $students_result->fetch_assoc()) {
    $student_id = $student['id'];
    
    // Get student-specific payment information
    $student_class = $student['class'];
    $student_stream = $student['stream'] ?? '';
    $student_repeat = $student['demote_status'] ?? 0;
    
    // Check payment status for this specific student - FIXED: Use term/session names, not IDs
    $payQuery = "SELECT pay_for FROM payment 
            WHERE student_id = ? 
            AND class = ? 
            AND school_type = ?
            AND session = ?
            AND Term = ?
            AND pay_for LIKE 'School Fees%'
            LIMIT 1";
    $PaidStmt = $con->prepare($payQuery);
    $PaidStmt->bind_param("issss", $student_id, $student_class, $student_stream, $selected_session, $selected_term);
    $PaidStmt->execute();
    $PaidResult = $PaidStmt->get_result();
    $PaidRow = $PaidResult->fetch_assoc();
    $pay_for = $PaidRow['pay_for'] ?? '';

    if ($pay_for == 'School Fees') {
        $student_type = "Old";
    } elseif ($pay_for == 'School Fees (New Student)') {
        $student_type = "New";
    } else {
        // Skip this student if no payment record found
        $skipped_count++;
        continue;
    }

    // Get default fee for this student - FIXED: Use term/session names, not IDs
    $feeQuery = "SELECT amount FROM fee_records 
    WHERE class = ? 
    AND stream = ? 
    AND name = ? 
    AND student_type = ? 
    AND session = ? 
    AND term = ? 
    ORDER BY created_at DESC 
    LIMIT 1";
    $stmt = $con->prepare($feeQuery);
    $stmt->bind_param("ssssss", $student_class, $student_stream, $pay_for, $student_type, $selected_session, $selected_term);
    $stmt->execute();
    $feeResult = $stmt->get_result();
    $feeRow = $feeResult->fetch_assoc();
    $totalFee = $feeRow['amount'] ?? 0;
    $currentTermTotalFees = $totalFee;

    // Handle special fee cases - FIXED: Use term name, not ID
    if (trim($student_class) === 'SS 1' && $student_repeat === 1 && $selected_term === 'First Term') {
        // Special fee logic for SS1 repeat students
        $feeQuery = "SELECT amount FROM fee_records 
        WHERE class = ? 
        AND stream = ? 
        AND name = ? 
        AND student_type = ? 
        AND session = ? 
        AND term = ? 
        ORDER BY created_at DESC 
        LIMIT 1";
        $stmt = $con->prepare($feeQuery);
        $stmt->bind_param("ssssss", $student_class, $student_stream, $pay_for, $student_type, $selected_session, $selected_term);
        $stmt->execute();
        $feeResult = $stmt->get_result();
        $feeRow = $feeResult->fetch_assoc();
        $totalFee1 = $feeRow['amount'] ?? $totalFee;
        $currentTermTotalFees = $totalFee1;
    }
    
    // FIXED: Use term name, not ID
    if ($selected_term === 'First Term' && in_array($student_class, ['JSS 3', 'SS 1', 'SS 3'])) {
        $special_fee_names = [
            'JSS 3' => 'First Term JSS 3 School Fees',
            'SS 1'  => 'First Term SS 1 School Fees',
            'SS 3'  => 'First Term SS 3 School Fees'
        ];
        $special_fee_name = $special_fee_names[$student_class];
        $feeQuery = "SELECT amount FROM fees 
        WHERE class = ? 
        AND stream = ? 
        AND student_type = 'Old' 
        AND name = ? 
        LIMIT 1";
        $stmt = $con->prepare($feeQuery);
        $stmt->bind_param("sss", $student_class, $student_stream, $special_fee_name);
        $stmt->execute();
        $feeResult = $stmt->get_result();
        $feeRow = $feeResult->fetch_assoc();
        $currentTermTotalFees = $feeRow['amount'] ?? $totalFee;
    }

    // Calculate total paid amount - FIXED: Use term/session names, not IDs
    $totalPaidQuery = "SELECT SUM(amount) as total_paid FROM payment 
            WHERE student_id = ? 
            AND class = ? 
            AND pay_for =? 
            AND school_type = ?
            AND session = ?
            AND Term = ?";
    $totalPaidStmt = $con->prepare($totalPaidQuery);
    $totalPaidStmt->bind_param("isssss", $student_id, $student_class, $pay_for, $student_stream, $selected_session, $selected_term);
    $totalPaidStmt->execute();
    $totalPaidResult = $totalPaidStmt->get_result();
    $totalPaidRow = $totalPaidResult->fetch_assoc();
    $totalPaidAmount = $totalPaidRow['total_paid'] ?? 0;

    // ✅ Check if student paid in full
    if ($totalPaidAmount < $currentTermTotalFees) {
        // Skip this student – they haven't paid in full
        $skipped_count++;
        continue;
    }

    // If payment is complete, proceed with PDF generation
    $student_section = $student['section'] ?? '';
    $studentImage = "studentimages/" . htmlspecialchars($student['studentImage'] ?? '');
    $studentName = htmlspecialchars($student['studentName'] ?? 'Unknown');
    
    // Fetch Psychomotor
    $psychomotor_sql = "SELECT * FROM psychomotor WHERE student_id = $student_id AND term = '$selected_term' AND session ='$selected_session'";
    $psychomotor_result = $con->query($psychomotor_sql);
    $psychomotor = ($psychomotor_result && $psychomotor_result->num_rows > 0) ? $psychomotor_result->fetch_assoc() : [];

    // Fetch Affective
    $affective_sql = "SELECT * FROM affective WHERE student_id = $student_id AND term = '$selected_term' AND session ='$selected_session'";
    $affective_result = $con->query($affective_sql);
    $affective = ($affective_result && $affective_result->num_rows > 0) ? $affective_result->fetch_assoc() : [];

    // 1. Get School Calendar for the term + session
    $sqlCalendar = "
        SELECT c.resumption_date, c.closing_date
        FROM school_calendar c
        WHERE c.term_id = ? AND c.session_id = ?
        LIMIT 1
    ";
    $stmtCalendar = $con->prepare($sqlCalendar);
    $stmtCalendar->bind_param("ii", $term_id, $session_id);
    $stmtCalendar->execute();
    $resCalendar = $stmtCalendar->get_result();
    $calendar = $resCalendar->fetch_assoc();

    // 2. Get all public holidays for this term + session
    $holidayDates = [];
    $sqlHolidays = "
        SELECT holiday_date 
        FROM public_holidays 
        WHERE term_id = ? AND session_id = ?
    ";
    $stmtHoliday = $con->prepare($sqlHolidays);
    $stmtHoliday->bind_param("ii", $term_id, $session_id);
    $stmtHoliday->execute();
    $resHoliday = $stmtHoliday->get_result();
    while ($row = $resHoliday->fetch_assoc()) {
        $holidayDates[] = $row['holiday_date'];
    }

    // 3. Calculate Total Open Days (weekdays excluding holidays)
    $totalDays = 0;
    if ($calendar) {
        $startDate = new DateTime($calendar['resumption_date']);
        $endDate   = new DateTime($calendar['closing_date']);

        while ($startDate <= $endDate) {
            $dayOfWeek = $startDate->format("N"); // 1=Mon, 7=Sun
            $dateStr   = $startDate->format("Y-m-d");

            if ($dayOfWeek < 6 && !in_array($dateStr, $holidayDates)) {
                $totalDays++;
            }
            $startDate->modify("+1 day");
        }
    }

    // 4. Count student's days present
    $sql1 = "SELECT COUNT(*) AS total_attendance 
             FROM attendance 
             WHERE student_id = ? 
               AND term_id = ? 
               AND session_id = ? 
               AND status = 'Present'";
    $stmt1 = $con->prepare($sql1);
    $stmt1->bind_param("iii", $student_id, $term_id, $session_id);
    $stmt1->execute();
    $res1 = $stmt1->get_result();
    $totalAttendance = $res1->fetch_assoc()['total_attendance'] ?? 0;

    // Number of Students
    $sql2 = "SELECT COUNT(*) AS num_students FROM students WHERE class = ? AND section = ?";
    $stmt2 = $con->prepare($sql2);
    $stmt2->bind_param("ss", $class, $section);
    $stmt2->execute();
    $res2 = $stmt2->get_result();
    $numStudents = $res2->fetch_assoc()['num_students'] ?? 0;

    $sql_avg_age = "SELECT AVG(TIMESTAMPDIFF(YEAR, age, CURDATE())) AS average_age FROM students WHERE class = '$class' AND section = '$section'";
    $result_avg_age = $con->query($sql_avg_age);
    $row_avg_age = $result_avg_age->fetch_assoc();
    $average_age = $row_avg_age['average_age'] ?? 0;

    // Fetch the next resumption date
    $nextQuery = "
        SELECT session_id, term_id, resumption_date, vacating_date, result_type
        FROM result_publish_schedule
        WHERE result_type = 'Endterm'
          AND session_id = '$session_id'
          AND term_id = '$term_id'
        ORDER BY resumption_date ASC
        LIMIT 1
    ";

    $nextResult = mysqli_query($con, $nextQuery);

    if ($nextResult && mysqli_num_rows($nextResult) > 0) {
        $nextRow = mysqli_fetch_assoc($nextResult);

        $nextSessionId   = $nextRow['session_id'];
        $nextTermId      = $nextRow['term_id'];
        $nextTermName    = "Term ID: " . $nextTermId; // raw ID, since no join
        $nextStartDate   = $nextRow['resumption_date'] ?? 'N/A';
        $nextEndDate     = $nextRow['vacating_date'] ?? 'N/A';
        $nextResultType  = $nextRow['result_type'];
    } else {
        $nextTermName   = 'N/A';
        $nextStartDate  = 'N/A';
        $nextEndDate    = 'N/A';
    }

    $sql = "SELECT DATE(publish_datetime) AS publish_date 
    FROM result_publish_schedule 
    WHERE session_id = ? AND term_id = ? AND result_type = 'Endterm'";

    $stmt = $con->prepare($sql);
    $stmt->bind_param("ii", $session_id, $term_id);
    $stmt->execute();
    $result = $stmt->get_result();
    $publish_date = 'N/A'; // Default value if no publish date is found
    if ($row = $result->fetch_assoc()) {
        $publish_date = $row['publish_date'];
    }

    // Round age
    $average_age = ceil($average_age);

    // Get the position in the class
    $positionQuery = "
      SELECT student_id, SUM(total_score) AS total
      FROM results 
      WHERE session_id = '$session_id' AND term_id = '$term_id' AND class = '$class' AND status = 'Approved'
      GROUP BY student_id
      ORDER BY total DESC
    ";
    $positionResult = mysqli_query($con, $positionQuery);
    $position = 1;
    $previous_score = null;

    while ($row = mysqli_fetch_assoc($positionResult)) {
        if ($row['student_id'] == $student_id) {
            break;
        }

        if ($row['total'] != $previous_score) {
            $position++;
        }

        $previous_score = $row['total'];
    }

    $position_with_suffix = getPositionSuffix($position);

     // SECTION POSITION
$sectionQuery = $con->prepare("
    SELECT student_id, SUM(total_score) AS total
    FROM results 
    WHERE session_id = ? 
      AND term_id = ? 
      AND class = ?
      AND section = ?
      AND status = 'Approved'
    GROUP BY student_id
    ORDER BY total DESC
");

$sectionQuery->bind_param("iiss", $session_id, $term_id, $class, $section);
$sectionQuery->execute();
$sectionResult = $sectionQuery->get_result();

$section_position = 1;
$section_previous_score = null;

while ($row = $sectionResult->fetch_assoc()) {

    if ($row['student_id'] == $student_id) {
        break;
    }

    if ($row['total'] != $section_previous_score) {
        $section_position++;
    }

    $section_previous_score = $row['total'];
}

$section_pos_with_suffix = getPositionSuffix($section_position);   

    // Fetch all terms for the session, ordered correctly
    $terms_sql = "SELECT id AS term_id, term_name FROM school_terms WHERE session_id = '$session_id' ORDER BY id ASC";
    $terms_result = $con->query($terms_sql);

    $terms = [];
    $term_order = [];
    $term_names = [];

    if ($terms_result && $terms_result->num_rows > 0) {
        while ($row = $terms_result->fetch_assoc()) {
            $terms[] = $row;
            $term_order[] = $row['term_id'];
            $term_names[$row['term_id']] = $row['term_name'];
        }
    }

    $current_term_index = array_search($term_id, $term_order);
    $previous_term_ids = [];

    if ($current_term_index !== false) {
        for ($i = 0; $i < $current_term_index; $i++) {
            $previous_term_ids[] = $term_order[$i];
        }
    }

    $all_terms_to_fetch = array_merge($previous_term_ids, [$term_id]);
    $term_ids_in = implode(",", $all_terms_to_fetch);

    // Initialize result variables
    $results = [];
    $total_score = 0;
    $total_subjects = 0;
    $mark_obtainable = 0;
    $percentage = 0;
    $average_score = 0;

    // Fetch academic results
    $results_sql = "SELECT term_id, subject, test_score, test_score2, class_performance, weekly_assessment, exam_score, total_score,remarks,
     test_absent,test2_absent,class_performance_absent,weekly_assessment_absent,exam_absent
                    FROM results 
                    WHERE student_id = '$student_id' 
                    AND session_id = '$session_id' 
                    AND term_id IN ($term_ids_in)
                    AND class = '$class'
                    AND status = 'Approved'";

    $results_result = $con->query($results_sql);
    $current_term_id = $term_id;

    $subjects_results = [];

    if ($results_result && $results_result->num_rows > 0) {
        while ($row = $results_result->fetch_assoc()) {
            $result_term_id = $row['term_id']; // actual row term
            $subject = $row['subject'];

            if (!isset($subjects_results[$subject])) {
                $subjects_results[$subject] = [
                    'terms' => [] // store each term's scores here
                ];
            }

            $subjects_results[$subject]['terms'][$result_term_id] = $row; // assign by term ID
        }

        // Calculate Total and Averages
        $total_subjects = count($subjects_results);
        $mark_obtainable = $total_subjects * 100;
        $current_term_id = $term_id; // the term you want total for

        foreach ($subjects_results as $subject_data) {
            if (isset($subject_data['terms'][$current_term_id])) {
                $score = $subject_data['terms'][$current_term_id]['total_score'];

                // Treat absent or invalid as 0
                if ($score === null || $score === '' || strtoupper($score) === 'AB') {
                    $score = 0;
                }

                $total_score += (int)$score;
            }
        }

        $percentage = $total_subjects ? round(($total_score / $mark_obtainable) * 100, 2) : 0;
        $average_score = $total_subjects ? round($total_score / $total_subjects, 2) : 0;
    }

    $principal_remarks = generateRemark($percentage);

    // Calculate colspan dynamically
        $base_columns = 7;
        $previous_term_count = count($previous_term_ids);

        $total_colspan = ($previous_term_count < 1)
            ? $base_columns
            : $base_columns + (($previous_term_count >= 2) ? ($previous_term_count + 2) : 1);

    ob_start();
?>
    <!DOCTYPE html>
    <html lang="en">

    <head>
        <meta charset="UTF-8">
        <title>Student Report</title>
        <style>
            body {
                font-family: "Times New Roman", serif;
                font-size: 14pt;
                margin: 5px;
                padding: 5px;
                line-height: 2.0;
            }
            

            h1,
            h2,
            h3 {
                font-weight: bold;
                margin: 5px 0;
            }

            table {
                border-collapse: collapse;
                width: 100%;
                margin-bottom: 8px;
                page-break-inside: avoid;
            }

            th,
            td {
                border: 1px solid #000;
                padding: 10px;
                text-align: left;
                font-size: 14pt;
            }

            th {
                font-weight: bold;
                background-color: #f2f2f2;
                text-align: center;
            }

            .header-table td {
                border: none;
                padding: 5px;
            }

            /* Add to your existing styles */
            .academic-table {
                table-layout: fixed; /* This ensures equal column distribution */
                width: 100%;
            }

            .academic-table th,
            .academic-table td {
                width: auto;
                font-size: 14pt !important; /* Force consistent font size */
                padding: 10px;
                word-wrap: break-word; /* Handle long content */
                height: 70px;

            }

            /* Specifically target the subject column to be wider */
            .academic-table th:first-child,
            .academic-table td:first-child {
                width: 20%; /* Adjust as needed */
            }

            /* Make other columns equal width */
            .academic-table th:not(:first-child),
            .academic-table td:not(:first-child) {
                width: calc(80% / <?php echo $total_colspan - 1; ?>);
            }

            .behavior-table {
                width: 100%;
            }

            .grades-table {
                width: 100%;
            }

            .slant-header {
                height: 60px;
                position: relative;
                word-wrap: break-word; /* Handle long content */
            }

            .slant-header span {
                position: absolute;
                bottom: 15px;
                left: 5px;
                transform: rotate(-45deg);
                transform-origin: 0 0;
                width: 100px;
                word-wrap: break-word; /* Handle long content */
            }

            .signature-img {
                width: 80px;
                height: 40px;
            }

            .no-border {
                border: none !important;
            }

            .text-left {
                text-align: left !important;
            }

            .text-center {
                text-align: center !important;
            }

            .text-right {
                text-align: right !important;
            }

            .report-title {
                font-weight: bold;
                border: 1px solid #000;
                display: inline-block;
                padding: 2px 10px;
                margin: 5px 0;
                font-size: 14pt;
            }

            .school-header {
                border-bottom: 1px solid #000;
                padding-bottom: 5px;
                margin-bottom: 5px;
            }

            .main-content {
                width: 100%;
            }

            .compact {
                font-size: 14pt;
            }
        </style>
    </head>

    <body>
        <table width="100%" cellpadding="0" cellspacing="0" border="0" class="main-content" style="border:none;">
            <tr>
                <td style="border:none;">
                    <!-- School Header -->
                    <table width="100%" class="school-header" style="border:none;">
                        <tr>
                            <td width="100" valign="middle" style="border:none;">
                                <img src="<?php echo $logo; ?>" width="100" alt="School Logo">
                            </td>
                            <td align="center" style="border:none;">
                                            <h1 class="mb-0 fw-bold" style="font-weight:bold; font-size: 50px;"><?php echo strtoupper($school_name) ?></h1>
                                <p style="margin: 2px 0; font-weight: bold;"><?php echo $address; ?></p>
                                <p style="margin: 2px 0; font-weight: bold;">
                                    Website: <span style="text-decoration: underline"><?php echo $website; ?></span> |
                                    E-mail: <span style="text-decoration: underline"><?php echo $school_email; ?></span>
                                </p>
                            </td>
                            <td width="100" style="border:none;"></td>
                        </tr>
                    </table>

                    <!-- Report Title -->
                    <table width="100%">
                        <tr>
                            <td align="center" style="border:none;">
                                <span class="report-title" style ="font-size: 26px;"><?php echo strtoupper($selected_term); ?> REPORT</span>
                            </td>
                        </tr>
                    </table>

                    <!-- Student Information Table -->
                    <table width="100%" class="header-table">
                        <tr>
                            <td><strong>Name:</strong> <?php echo htmlentities($student['studentName'] ?? 'N/A'); ?></td>
                            <td><strong>Term:</strong> <?php echo $selected_term; ?></td>
                            <td><strong>Individual Attendance:</strong> <?php echo $totalAttendance; ?></td>
                        </tr>
                        <tr>
                            <td><strong>Class:</strong> <?= $class . ' - ' . $student['section']; ?></td>
                            <td><strong>Session:</strong> <?php echo $selected_session; ?></td>
                            <td><strong>Total Attendance This Term:</strong> <?php echo $totalDays; ?></td>

                        </tr>
                        <tr>
                            <td><strong>Gender:</strong> <?php echo $student['gender'] ?? 'N/A'; ?></td>
                            <td><strong>No. in Class:</strong> <?php echo $numStudents; ?></td>
                            <td><strong>Next Resumption Date:</strong> <?php echo date("F j, Y", strtotime($nextStartDate)); ?></td>
                        </tr>
                        <tr>
                            <td>
                                            <strong>Student's Age:</strong> 
                                            <?= (!empty($student['age']) && date_diff(date_create($student['age']), date_create('today'))->y > 0) 
                                                ? date_diff(date_create($student['age']), date_create('today'))->y 
                                                : "N/A"; ?>
                            </td>  
                            <td><strong>Average Age:</strong> N/A </td>                            
                            <td><strong>Next Vacation Date:</strong> <?php echo date("F j, Y", strtotime($nextEndDate)); ?></td>
                        </tr>
                    </table>

                    <!-- Main Content Tables -->
                    <table width="100%" cellpadding="0" cellspacing="0" border="0">
                        <tr>
                            <td width="65%" valign="top" style="border:none;">
                                <!-- ACADEMICS TABLE -->
                                <table class="academic-table">
                                    <thead>
                                        <tr>
                                            <th colspan="<?php echo $total_colspan; ?>">COGNITIVE DOMAIN (ACADEMICS)</th>
                                        </tr>
                                        <tr>
                                            <th class="text-left" style="width: 15%;">Subjects</th>
                                                    <th class="slant-header">C.A. Test</th>
                                                    <th class="slant-header">Ex. & Projects</th>
                                                    <th class="slant-header">Assignment</th>
                                                    <th class="slant-header">Exam</th>
                                                    <th class="slant-header">Total</th>
                                                    <th class="slant-header">Grade</th>

                                            <?php foreach ($previous_term_ids as $prev_id) { ?>
                                                <th class="slant-header"><span><?php echo htmlspecialchars($term_names[$prev_id] ?? ''); ?></span></th>
                                            <?php } ?>

                                            <?php if ($previous_term_count >=2) { ?>
                                                <th class="slant-header"><span>Cumm. Total</span></th>
                                                <th class="slant-header"><span>Cumm. Average</span></th>
                                            <?php } ?>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <tr>
                                            <td class="text-left">Marks Obtainable</td>
                                            <td class="text-center"><b><i>10</i><b></td>
                                            <td class="text-center"><b><i>15</i><b></td>
                                            <td class="text-center"><b><i>5</i><b></td>
                                            <td class="text-center"><b><i>70</i><b></td>
                                            <td class="text-center"><b><i>100</i><b></td>
                                            <td class="text-center"><b><i></i><b></td>

                                            <?php foreach ($previous_term_ids as $prev_id) { ?>
                                                <td class="text-center"><b><i>100</i><b></td>
                                            <?php } ?>

                                            <?php if (count($previous_term_ids) >=2) { ?>
                                                <td class="text-center"><b><i>300</i><b></td>
                                                <td class="text-center"><b><i>100</i><b></td>
                                            <?php } ?>
                                        </tr>

                                <?php foreach ($subjects_results as $subject => $data) :
                                    $current = $data['terms'][$current_term_id] ?? [];

                                    // Display values (show 'AB' if absent)
                                    $display_test_score1 = ($current['test_absent'] ?? 0) ? 'AB' : ($current['test_score'] ?? 0);
                                    $display_class_performance = ($current['class_performance_absent'] ?? 0) ? 'AB' : ($current['class_performance'] ?? 0);
                                    $display_weekly_assessment = ($current['weekly_assessment_absent'] ?? 0) ? 'AB' : ($current['weekly_assessment'] ?? 0);
                                    $display_exam_score = ($current['exam_absent'] ?? 0) ? 'AB' : ($current['exam_score'] ?? 0);

                                    // Numeric values for calculations
                                    $calc_test_score1 = ($current['test_absent'] ?? 0) ? 0 : ($current['test_score'] ?? 0);
                                    $calc_class_performance = ($current['class_performance_absent'] ?? 0) ? 0 : ($current['class_performance'] ?? 0);
                                    $calc_weekly_assessment = ($current['weekly_assessment_absent'] ?? 0) ? 0 : ($current['weekly_assessment'] ?? 0);
                                    $calc_exam_score = ($current['exam_absent'] ?? 0) ? 0 : ($current['exam_score'] ?? 0);

                                    // Total score1
                                    $total_score1 = $calc_test_score1 + $calc_class_performance + $calc_weekly_assessment + $calc_exam_score;

                                    $remarks = $current['remarks'] ?? 'N/A';

                                    // Prepare previous scores
                                    $previous_scores = [];
                                    foreach ($previous_term_ids as $prev_id) {
                                        $prev = $data['terms'][$prev_id] ?? [];
                                        $prev_total = 0;
                                        $prev_total += ($prev['test_score'] ?? 0);
                                        $prev_total += ($prev['class_performance'] ?? 0);
                                        $prev_total += ($prev['weekly_assessment'] ?? 0);
                                        $prev_total += ($prev['exam_score'] ?? 0);
                                        $previous_scores[] = $prev_total;
                                    }

                                    // Cumulative total and average
                                    $all_scores = array_merge($previous_scores, [$total_score1]);
                                    $terms_count = count(array_filter($all_scores, fn($score) => $score > 0));
                                    $cumm_total = array_sum($all_scores);
                                    $cumm_average = $terms_count > 0 ? round($cumm_total / $terms_count, 2) : 0;
                                ?>

                                            <tr>
                                                <td class="text-left"><?php echo htmlspecialchars($subject); ?></td>
                                                <td class="text-center"><?php echo htmlspecialchars($display_test_score1); ?></td>
                                                <td class="text-center"><?php echo htmlspecialchars($display_class_performance); ?></td>
                                                <td class="text-center"><?php echo htmlspecialchars($display_weekly_assessment); ?></td>
                                                <td class="text-center"><?php echo htmlspecialchars($display_exam_score); ?></td>
                                                <td class="text-center"><?php echo htmlspecialchars($total_score1); ?></td>
                                                <td class="text-center"><?php echo getGrade($total_score1,$class); ?></td>

                                                <?php foreach ($previous_scores as $prev_score) { ?>
                                                    <td class="text-center"><?php echo htmlspecialchars($prev_score); ?></td>
                                                <?php } ?>

                                                <?php if (count($previous_scores) >=2) { ?>
                                                    <td class="text-center"><?php echo htmlspecialchars($cumm_total); ?></td>
                                                    <td class="text-center"><?php echo htmlspecialchars($cumm_average); ?></td>
                                                <?php } ?>
                                            </tr>
                                        <?php endforeach; ?>
                                    </tbody>
                                </table>

                                <!-- Summary Table -->
                                <table width="100%">
                                    <thead>
                                        <tr>
                                            <th><?php echo (count($previous_term_ids) >= 2) ? 'Total Marks Obtainable' : 'Total Marks Obtainable'; ?></th>
                                            <th><?php echo (count($previous_term_ids) >= 2) ? 'Marks Obtained' : 'Marks Obtained'; ?></th>
                                            <th><?php echo (count($previous_term_ids) >= 2) ? '% Obtained' : '% Obtained'; ?></th>
                                            <th>Position in Class</th>
                                            <th>Overall Position</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <tr>
                                            <?php
                                            // Calculate cumulative data if we're in the third term (or more than one term exists)
                                            if (count($previous_term_ids) >= 2) {

                                                // Map admission term from text to number
                                                $admission_term_text = $student['admission_term'] ?? 'First Term';
                                                $term_mapping = [
                                                    'First Term'  => 1,
                                                    'Second Term' => 2,
                                                    'Third Term'  => 3
                                                ];
                                                $admission_term_id = $term_mapping[$admission_term_text] ?? 1;

                                                // Filter only the terms the student actually attended
                                                $attended_terms = array_filter($all_terms_to_fetch, function($t) use ($admission_term_id) {
                                                    return $t >= $admission_term_id;
                                                });
                                                $terms_attended_count = count($attended_terms);

                                                // Calculate cumulative marks obtainable (subjects * 100 * terms attended)
                                                $cumulative_mark_obtainable = $total_subjects * 100 * $terms_attended_count;

                                                // Calculate cumulative marks obtained across attended terms
                                                $cumulative_total_score = 0;
                                                foreach ($subjects_results as $subject_data) {
                                                    foreach ($attended_terms as $term_id) {
                                                        if (isset($subject_data['terms'][$term_id])) {
                                                            $score = $subject_data['terms'][$term_id]['total_score'];
                                                            if ($score === null || $score === '' || strtoupper($score) === 'AB') {
                                                                $score = 0;
                                                            }
                                                            $cumulative_total_score += (int)$score;
                                                        }
                                                    }
                                                }

                                                // Calculate cumulative percentage
                                                $cumulative_percentage = $cumulative_mark_obtainable ? 
                                                    round(($cumulative_total_score / $cumulative_mark_obtainable) * 100, 2) : 0;

                                                // Get position based on average per attended term
                                                $cumulativePositionQuery = "
                                                    SELECT student_id, 
                                                        SUM(total_score) AS total,
                                                        COUNT(DISTINCT term_id) AS terms_attended
                                                    FROM results 
                                                    WHERE session_id = '$session_id' 
                                                    AND term_id IN ($term_ids_in) 
                                                    AND class = '$class' 
                                                    AND status = 'Approved'
                                                    GROUP BY student_id
                                                    ORDER BY (SUM(total_score) / COUNT(DISTINCT term_id)) DESC
                                                ";
                                                $cumulativePositionResult = mysqli_query($con, $cumulativePositionQuery);
                                                $cumulative_position = 1;
                                                $previous_cumulative_avg = null;

                                                while ($row = mysqli_fetch_assoc($cumulativePositionResult)) {
                                                    $student_avg = $row['terms_attended'] > 0 ? $row['total'] / $row['terms_attended'] : 0;

                                                    if ($row['student_id'] == $student_id) {
                                                        break;
                                                    }

                                                    if ($student_avg != $previous_cumulative_avg) {
                                                        $cumulative_position++;
                                                    }

                                                    $previous_cumulative_avg = $student_avg;
                                                }

                                                $cumulative_position_with_suffix = getPositionSuffix($cumulative_position);

                                                  $sectionCumQuery = $con->prepare("
                                                    SELECT 
                                                        student_id,
                                                        SUM(total_score) AS total,
                                                        COUNT(DISTINCT term_id) AS terms_attended
                                                    FROM results
                                                    WHERE session_id = ?
                                                    AND term_id IN ($term_ids_in)
                                                    AND class = ?
                                                    AND section = ?
                                                    AND status = 'Approved'
                                                    GROUP BY student_id
                                                    ORDER BY (SUM(total_score) / COUNT(DISTINCT term_id)) DESC
                                                ");

                                                $sectionCumQuery->bind_param("iss", $session_id, $class, $section);
                                                $sectionCumQuery->execute();
                                                $sectionCumResult = $sectionCumQuery->get_result();

                                                $section_cum_position = 1;
                                                $section_prev_avg = null;

                                                while ($row = $sectionCumResult->fetch_assoc()) {

                                                    $avg = $row['terms_attended'] > 0 
                                                        ? $row['total'] / $row['terms_attended'] 
                                                        : 0;

                                                    if ($row['student_id'] == $student_id) {
                                                        break;
                                                    }

                                                    if ($avg != $section_prev_avg) {
                                                        $section_cum_position++;
                                                    }

                                                    $section_prev_avg = $avg;
                                                }

                                                $section_cum_position_with_suffix = getPositionSuffix($section_cum_position);                                              


                                                // Display cumulative data
                                                echo '<td class="text-center">' . $cumulative_mark_obtainable . '</td>';
                                                echo '<td class="text-center">' . $cumulative_total_score . '</td>';
                                                echo '<td class="text-center">' . $cumulative_percentage . '</td>';
                                                echo '<td class="text-center">' . $section_cum_position_with_suffix . '</td>';
                                                echo '<td class="text-center">' . $cumulative_position_with_suffix . '</td>';

                                            } else {
                                                // Display term data (for single term cases)
                                                echo '<td class="text-center">' . $mark_obtainable . '</td>';
                                                echo '<td class="text-center">' . $total_score . '</td>';
                                                echo '<td class="text-center">' . $percentage . '</td>';
                                                echo '<td class="text-center">' . $section_pos_with_suffix . '</td>';
                                                echo '<td class="text-center">' . $position_with_suffix . '</td>';
                                            }
                                            ?>
                                            </tr>
                                    </tbody>
                                </table>

                                <!-- Remarks and Signature -->
                                <table width="100%" class="header-table">
                                    <tr>
                                        <td><strong>Class Teacher's Remarks: <?php echo htmlspecialchars($remarks); ?></strong></td>
                                    </tr>
                                    <tr>
                                        <td><strong>Principal's Remarks: <?php echo htmlspecialchars($principal_remarks); ?></strong></td>
                                    </tr>
                                    <tr>
                                        <td><strong>Date: <?php echo date("F j, Y", strtotime($publish_date)); ?></strong></td>
                                    </tr>
                                </table>
                            </td>

                            <td width="35%" valign="top" style="border:none;">
                                <!-- PSYCHOMOTOR & AFFECTIVE TABLE -->
                                <table class="behavior-table">
                                    <thead>
                                        <tr>
                                            <th class="text-left">PSYCHOMOTOR DOMAIN (SKILLS)</th>
                                            <th>5</th>
                                            <th>4</th>
                                            <th>3</th>
                                            <th>2</th>
                                            <th>1</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php
                                        $psychomotor_traits = [
                                            "Fluency" => $psychomotor['fluency'] ?? '',
                                            "Handwriting" => $psychomotor['handwriting'] ?? '',
                                            "Lab Workshops" => $psychomotor['lab_workshops'] ?? '',
                                            "Musical Skills" => $psychomotor['musical_skills'] ?? '',
                                            "Sports" => $psychomotor['sports'] ?? ''
                                        ];
                                        ?>

                                        <?php foreach ($psychomotor_traits as $trait_name => $trait_value) { ?>
                                            <tr>
                                                <td class="text-left"><?php echo htmlspecialchars($trait_name); ?></td>
                                                <?php for ($i = 5; $i >= 1; $i--) { ?>
                                                    <td class="text-center">
                                                        <?php if ($trait_value == $i): ?>
                                                            <span style="font-family: 'DejaVu Sans', sans-serif;">&#10003;</span>
                                                        <?php endif; ?>
                                                    </td>
                                                <?php } ?>
                                            </tr>
                                        <?php } ?>

                                        <tr>
                                            <th class="text-left">AFFECTIVE DOMAIN (BEHAVIOUR)</th>
                                            <th>5</th>
                                            <th>4</th>
                                            <th>3</th>
                                            <th>2</th>
                                            <th>1</th>
                                        </tr>

                                        <?php
                                        $affective_traits = [
                                            "Punctuality" => $affective['punctuality'] ?? '',
                                            "Reliability" => $affective['reliability'] ?? '',
                                            "Politeness/Honesty" => $affective['politeness_honesty'] ?? '',
                                            "Charisma" => $affective['charisma'] ?? '',
                                            "Organizational Ability" => $affective['organizational_ability'] ?? '',
                                            "Self-Control" => $affective['self_control'] ?? ''
                                        ];

                                        foreach ($affective_traits as $trait_name => $trait_value) { ?>
                                            <tr>
                                                <td class="text-left"><?php echo htmlspecialchars($trait_name); ?></td>
                                                <?php for ($i = 5; $i >= 1; $i--) { ?>
                                                    <td class="text-center">
                                                        <?php if ($trait_value == $i): ?>
                                                            <span style="font-family: 'DejaVu Sans', sans-serif;">&#10003;</span>
                                                        <?php endif; ?>
                                                    </td>
                                                <?php } ?>
                                            </tr>
                                        <?php } ?>
                                    </tbody>
                                </table>

                                <!-- Grades Table -->
                                <?php if (strpos($class, 'JSS') !== false): ?>
                                    <table class="grades-table">
                                        <thead>
                                            <tr>
                                                <th colspan="2">GRADES RATING</th>
                                                <th colspan="2">PSYCHO-AFFECTIVE RATING</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <tr>
                                                <td>A</td>
                                                <td>70 - 100</td>
                                                <td>5</td>
                                                <td>Excellent</td>
                                            </tr>
                                            <tr>
                                                <td>C</td>
                                                <td>50 - 69</td>
                                                <td>4</td>
                                                <td>High Level</td>
                                            </tr>
                                            <tr>
                                                <td>P</td>
                                                <td>40 - 49</td>
                                                <td>3</td>
                                                <td>Acceptable Level</td>
                                            </tr>
                                            <tr>
                                                <td>F</td>
                                                <td>0 - 39</td>
                                                <td>2</td>
                                                <td>Below Acceptable Level</td>
                                            </tr>
                                            <tr>
                                                <td colspan="2" class="no-border"></td>
                                                <td>1</td>
                                                <td>No Regard to traits</td>
                                            </tr>
                                        </tbody>
                                    </table>
                                <?php elseif (strpos($class, 'SS') !== false): ?>
                                    <table class="grades-table">
                                        <thead>
                                            <tr>
                                                <th colspan="2">GRADES RATING</th>
                                                <th colspan="2">PSYCHO-AFFECTIVE RATING</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <tr>
                                                <td>A1</td>
                                                <td>75 - 100</td>
                                                <td>5</td>
                                                <td>Excellent</td>
                                            </tr>
                                            <tr>
                                                <td>B2</td>
                                                <td>70 - 74</td>
                                                <td>4</td>
                                                <td>High Level</td>
                                            </tr>
                                            <tr>
                                                <td>B3</td>
                                                <td>65 - 69</td>
                                                <td>3</td>
                                                <td>Acceptable Level</td>
                                            </tr>
                                            <tr>
                                                <td>C4</td>
                                                <td>60 - 64</td>
                                                <td>2</td>
                                                <td>Below Acceptable Level</td>
                                            </tr>
                                            <tr>
                                                <td>C5</td>
                                                <td>55 - 59</td>
                                                <td>1</td>
                                                <td>No Regard to traits</td>
                                            </tr>
                                            <tr>
                                                <td>C6</td>
                                                <td>50 - 54</td>
                                                <td colspan="2" class="no-border"></td>
                                            </tr>
                                            <tr>
                                                <td>D7</td>
                                                <td>45 - 49</td>
                                                <td colspan="2" class="no-border"></td>
                                            </tr>
                                            <tr>
                                                <td>E8</td>
                                                <td>40 - 44</td>
                                                <td colspan="2" class="no-border"></td>
                                            </tr>
                                            <tr>
                                                <td>F9</td>
                                                <td>0 - 39</td>
                                                <td colspan="2" class="no-border"></td>
                                            </tr>
                                        </tbody>
                                    </table>
                                <?php else : ?>
                                    <table class="grades-table">
                                        <thead>
                                            <tr>
                                                <th colspan="2">GRADES RATING</th>
                                                <th colspan="2">PSYCHO-AFFECTIVE RATING</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <tr>
                                                <td>A</td>
                                                <td>70 - 100</td>
                                                <td>5</td>
                                                <td>Excellent</td>
                                            </tr>
                                            <tr>
                                                <td>C</td>
                                                <td>50 - 69</td>
                                                <td>4</td>
                                                <td>High Level</td>
                                            </tr>
                                            <tr>
                                                <td>P</td>
                                                <td>40 - 59</td>
                                                <td>3</td>
                                                <td>Acceptable Level</td>
                                            </tr>
                                            <tr>
                                                <td>F</td>
                                                <td>0 - 39</td>
                                                <td>2</td>
                                                <td>Below Acceptable Level</td>
                                            </tr>
                                            <tr>
                                                <td colspan="2" class="no-border"></td>
                                                <td>1</td>
                                                <td>No Regard to traits</td>
                                            </tr>
                                        </tbody>
                                    </table>
                                <?php endif; ?>
                            </td>
                        </tr>
                    </table>
                </td>
            </tr>
        </table>
    </body>

    </html>
<?php
    $content = ob_get_clean();
    $filePath = $downloadFolder . '/result_' . preg_replace('/[^a-zA-Z0-9]/', '_', $studentName) . '.pdf';

    try {
        $mpdf = new Mpdf();
        // Set Watermark Image
        $mpdf->SetWatermarkText('Not for Official Use', 0.1); // Text and opacity
        $mpdf->showWatermarkText = true;                      // Enable it
        $mpdf->watermarkTextAlpha = 0.1;                      // Faint (0.1 = very transparent)
        $mpdf->watermark_font = 'DejaVuSans';                 // Ensure font is supported
        $mpdf->WriteHTML($content);
        $mpdf->Output($filePath, 'F');
    } catch (Exception $e) {
        error_log('Error generating PDF for ' . $studentName . ': ' . $e->getMessage());
        continue;
    }
}

// Create ZIP archive of all generated PDFs
$zipFilename = 'Fully_Paid_' . $class . ($section ? '_' . $section : '') . '_' . $selected_term . '.zip';
$zipFile = $downloadFolder . '/' . $zipFilename;
$zip = new ZipArchive();

if ($zip->open($zipFile, ZipArchive::CREATE) === TRUE) {
    $pdfFiles = glob($downloadFolder . '/*.pdf');
    // Exclude the zip file itself from being added
    $pdfFiles = array_filter($pdfFiles, function ($file) use ($zipFilename) {
        return basename($file) !== $zipFilename;
    });

    if (count($pdfFiles) > 0) {
        foreach ($pdfFiles as $file) {
            $zip->addFile($file, basename($file));
        }
        $zip->close();

        // Clear output buffers
        while (ob_get_level()) {
            ob_end_clean();
        }

        // Set headers and output the ZIP file
        header('Content-Type: application/zip');
        header('Content-Disposition: attachment; filename="' . $zipFilename . '"');
        header('Content-Length: ' . filesize($zipFile));
        readfile($zipFile);

        // Clean up - remove all files
        array_map('unlink', glob($downloadFolder . '/*'));
        rmdir($downloadFolder);
        exit;
    } else {
        // Clean up if no PDFs were generated
        if (file_exists($downloadFolder)) {
            array_map('unlink', glob($downloadFolder . '/*'));
            rmdir($downloadFolder);
        }
        die('No PDFs generated. Please check if there are students in the selected class/section.');
    }
} else {
    die('Failed to create ZIP archive. Please check directory permissions.');
}
?>