<?php  
ob_start();
require_once('tcpdf/tcpdf.php');
require_once('db_connection.php');

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['report_class'])) {
    $class = $_POST['report_class'];
    $term  = $_POST['report_term'] ?? 'N/A';
    $year  = $_POST['report_year'] ?? 'N/A';

    // ✅ SQL query including total students per class, term, year
    $stmt = $pdo->prepare("
        SELECT r.*, 
               (SELECT COUNT(*) FROM RANKED_RESULTS r2 
                WHERE r2.CLASS = r.CLASS AND r2.TERM = r.TERM AND r2.YEAR = r.YEAR) AS TOTAL_STUDENTS
        FROM RANKED_RESULTS r
        WHERE r.CLASS = ? AND r.TERM = ? AND r.YEAR = ?
        ORDER BY r.POSITION ASC
    ");
    $stmt->execute([$class, $term, $year]);
    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (!$students) {
        ob_end_clean();
        die('No Reports found for this class.');
    }

    $pdf = new TCPDF('P', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
    $pdf->SetCreator('Luzira COU Primary School');
    $pdf->SetAuthor('Luzira COU Primary School');
    $pdf->SetTitle("Class Report - $class");
    $pdf->setPrintHeader(false);
    $pdf->setPrintFooter(false);
    $pdf->SetMargins(5, 4, 5);
    $pdf->SetAutoPageBreak(TRUE, 10);
    $pdf->SetFont('helvetica', '', 10);

    // ✅ Mapping subject codes to names
    $subjectNames = [
        'MTC'  => 'Mathematics',
        'ENG'  => 'English',
        'LIT1' => 'Literacy 1',
        'LIT2' => 'Literacy 2',
        'RE'   => 'Religious Education',
        'SST'  => 'Social Studies',
        'SCI'  => 'Science'
    ];

    // ✅ Comment ranges per subject
    $commentsMap = [
        'MTC' => [[0,39,'Improve'], [40,54,'Tried'], [55,69,'Fair'], [70,89,'Very Good'], [90,100,'Excellent']],
        'ENG' => [[0,39,'Improve'], [40,54,'Tried'], [55,69,'Fair'], [70,89,'Very Good'], [90,100,'Excellent']],
        'SST' => [[0,39,'Improve'], [40,54,'Tried'], [55,69,'Fair'], [70,89,'Very Good'], [90,100,'Excellent']],
        'SCI' => [[0,39,'Improve'], [40,54,'Tried'], [55,69,'Fair'], [70,89,'Very Good'], [90,100,'Excellent']]
    ];

    // ✅ Teacher initials per class
    $initials = [
        'P1' => ['MTC'=>'KA','ENG'=>'MA','SST'=>'JJ','SCI'=>'LL'],
        'P2' => ['MTC'=>'KK','ENG'=>'HH','SST'=>'QQ','SCI'=>'DD'],
        'P3' => ['MTC'=>'MN','ENG'=>'PA','SST'=>'RR','SCI'=>'EE'],
        'P4' => ['MTC'=>'KS','ENG'=>'S','SST'=>'AS','SCI'=>'DM'],
        'P5' => ['MTC'=>'NR','ENG'=>'NR','SST'=>'AD','SCI'=>'RI'],
        'P6' => ['MTC'=>'AH','ENG'=>'AJ','SST'=>'MI','SCI'=>'NM'],
        'P7' => ['MTC'=>'OD','ENG'=>'SE','SST'=>'AP','SCI'=>'BY']
    ];

    // ✅ Comment generator
    function getComment($subject, $mark, $commentsMap) {
        if (!isset($commentsMap[$subject])) return '';
        foreach ($commentsMap[$subject] as $range) {
            if ($mark >= $range[0] && $mark <= $range[1]) return $range[2];
        }
        return '';
    }

    foreach ($students as $student) {
        $pdf->AddPage();
        $pdf->Rect(5, 5, $pdf->getPageWidth() - 10, $pdf->getPageHeight() - 10);

        // ✅ Header
        $pdf->Image('logo.png', 7, 5, 20);
        $pdf->SetFont('helvetica', 'B', 14);
        $pdf->Cell(0, 5, 'LUZIRA CHURCH OF UGANDA PRIMARY SCHOOL', 0, 1, 'C');
        $pdf->SetFont('helvetica', 'B', 7);
        $pdf->Cell(0, 5, "P.O.Box 20061, Kampala - Uganda Email: luziracouprim@ymail.com Website: https://luziracouprimaryschool.com", 0, 1, 'C');
        $pdf->SetFont('helvetica', 'B', 10);
        $pdf->Cell(0, 5, "STUDENT'S TERMLY PROGRESSIVE REPORT - $term $year", 0, 1, 'C');
        $pdf->Ln(8);

        // ✅ Student details
        $student_id    = $student['STUDENT_ID'] ?? 'N/A';
        $student_name  = $student['STUDENT_NAME'] ?? 'N/A';
        $student_class = $student['CLASS'] ?? 'N/A';
        $student_sex   = $student['SEX'] ?? 'N/A';
        $student_pos   = $student['POSITION'] ?? 'N/A';
        $total_students = $student['TOTAL_STUDENTS'] ?? 'N/A';

        $pdf->Ln(5);
        $pdf->writeHTML("<div style='background-color:#dbe4f3; padding:5px; font-weight:bold; font-size:12pt; color:#003366;'>Pupil's Details</div>", true, false, false, false, '');
        $pdf->writeHTML("
        <div style='line-height:1.5; font-size:10pt; padding-left:5px; padding-top:4px;'>
        <strong>ID:</strong> $student_id<br>
        <strong>Name:</strong> $student_name<br>
        <strong>Class:</strong> $student_class<br>
        <strong>Sex:</strong> $student_sex<br>
        <strong>Position:</strong> $student_pos out of $total_students
        </div>", true, false, false, false, '');

        // ✅ MIDTERM
         $pdf->Ln(5);
        $stmtMid = $pdo->prepare("SELECT * FROM midterm_results WHERE STUDENT_ID = ? AND CLASS = ? AND TERM = ? AND YEAR = ?");
        $stmtMid->execute([$student['STUDENT_ID'], $student['CLASS'], $term, $year]);
        $midterm = $stmtMid->fetch(PDO::FETCH_ASSOC);

        $midterm_total = $midterm['TOTAL_MARKS'] ?? '';
        $midterm_agg   = $midterm['TOTAL_AGG'] ?? '';
        $midterm_div   = $midterm['DIVISION'] ?? '';

        if ($midterm) {
            $pdf->writeHTML('<h3 style="color:#007BFF;">MIDTERM RESULTS</h3>', true, false, false, false, '');
            $midtermTable = '<table border="1" cellpadding="4">
            <thead><tr style="background-color:#eee;">
                <th>LEARNING AREA</th><th>MARKS OBTAINED</th><th>OUT OF</th>
                <th>AGG</th><th>TEACHER\'S COMMENT</th><th>INITIAL</th>
            </tr></thead><tbody>';
            foreach ($commentsMap as $code => $ranges) {
                $mark = $midterm[$code] ?? 0;
                $agg  = $midterm[$code.'_AGG'] ?? '';
                $comment = getComment($code, $mark, $commentsMap);
                $initial = $initials[$class][$code] ?? '';
                $subjectName = $subjectNames[$code] ?? $code;
                $midtermTable .= "<tr>
                    <td>$subjectName</td><td>$mark</td><td>100</td>
                    <td>$agg</td><td>$comment</td><td>$initial</td>
                </tr>";
            }
            $midtermTable .= "<tr style='background-color:#f0f0f0; font-weight:bold; font-family:cursive;'> 
                <td style='border:1px dashed black;'>TOTAL MARKS</td>
                <td style='border:1px dashed black;'>$midterm_total</td>
                <td style='border:1px dashed black;'>TOTAL AGG</td>
                <td style='border:1px dashed black;'>$midterm_agg</td>
                <td style='border:1px dashed black;'>DIVISION $midterm_div</td>
                <td style='border:1px dashed black;'></td>
            </tr></tbody></table>";
            $pdf->writeHTML($midtermTable, true, false, false, false, '');
        }

        // ✅ END OF TERM
        $eot_total = $student['TOTAL_MARKS'] ?? '';
        $eot_agg   = $student['TOTAL_AGG'] ?? '';
        $eot_div   = $student['DIVISION'] ?? '';

        $pdf->writeHTML('<h3 style="color:#28A745;">END OF TERM RESULTS</h3>', true, false, false, false, '');
        $eotTable = '<table border="1" cellpadding="4">
        <thead><tr style="background-color:#eee;">
            <th>LEARNING AREA</th><th>MARKS OBTAINED</th><th>OUT OF</th>
            <th>AGG</th><th>TEACHER\'S COMMENT</th><th>INITIAL</th>
        </tr></thead><tbody>';
        foreach ($commentsMap as $code => $ranges) {
            $mark = $student[$code] ?? 0;
            $agg  = $student[$code.'_AGG'] ?? '';
            $comment = getComment($code, $mark, $commentsMap);
            $initial = $initials[$class][$code] ?? '';
            $subjectName = $subjectNames[$code] ?? $code;
            $eotTable .= "<tr>
                <td>$subjectName</td><td>$mark</td><td>100</td>
                <td>$agg</td><td>$comment</td><td>$initial</td>
            </tr>";
        }
        $eotTable .= "
        <tr style='border: 1px solid black; font-weight: bold; text-align: center;'>
            <td style='border-left: 1px solid black;'>TOTAL MARKS</td>
            <td>$eot_total</td>
            <td>TOTAL AGG</td>
            <td>$eot_agg</td>
            <td>DIVISION $eot_div</td>
            <td style='border-right: 1px solid black;'></td>
        </tr></tbody></table>";
        $pdf->writeHTML($eotTable, true, false, false, false, '');

        // ✅ Class teacher remarks
        $pdf->Ln(5);
        $pdf->writeHTML("<strong>Class Teacher's Comment:</strong> " . ($student['REMARKS'] ?? ''), true, false, false, false, '');

        // ✅ Requirements
        $pdf->Ln(5);
        $pdf->SetFont('helvetica', 11);
        $pdf->writeHTML('<strong>Requirements</strong>', true, false, false, false, '');
        $requirementsTable = '<table border="1" cellpadding="2">
            <tr><th width="65%">Item</th><th width="35%">Status</th></tr>
            <tr><td>PARENTS CONTRIBUTION: P1-P2 Ugx75,000 P3-P5 Ugx85,000  and   P6-P7 UGX115,000</td><td>Should be Paid at Bank with Pay Code</td></tr>
            <tr><td>A REAM OF DOUBLE A/DOLOPHINE</td><td>Should be Provided by parent</td></tr>
            <tr><td>4 ROLLS OF TOILET PAPER</td><td>Should be Provided by parent</td></tr>
        </table>';
        $pdf->writeHTML($requirementsTable, true, false, false, false, '');

        // ✅ Headteacher comment
        $div = $student['DIVISION'];
        if ($div == '1') {
            $ht_comment = 'Excellent performance. Keep it up.';
        } elseif ($div == '2') {
            $ht_comment = 'Good work. Aim higher.';
        } elseif ($div == '3') {
            $ht_comment = 'Fair performance. Needs improvement.';
        } elseif ($div == '4' || $div == 'U') {
            $ht_comment = 'Work harder next time.';
        } else {
            $ht_comment = 'Avoid missing exams.';
        }
        $pdf->writeHTML('<strong>Headteacher\'s Comment:</strong> ' . $ht_comment, true, false, false, false, '');

        // ✅ Signature
        $yBefore = $pdf->GetY();
        $pdf->Image('signature.png', $pdf->GetX(), $yBefore, 20);
        $pdf->SetY($yBefore + 20);
        $pdf->Cell(20, 3, 'Headteacher', 0, 1, 'L');

        // ✅ Footer disclaimer
        $pdf->Ln(1);
        $pdf->SetFont('', 'BI');
        $pdf->SetTextColor(255, 255, 255);
        $pdf->SetFillColor(0, 0, 0);
        $pdf->Cell(0, 6, 'Disclaimer: These results can be verified via our website https://luziracouprimaryschool.com/', 0, 1, 'R', true);
        $pdf->SetTextColor(0, 0, 0);
        $pdf->SetFont('', '');
    }

    ob_end_clean();
    $pdf->Output("Class_Report_$class.pdf", 'I');
    exit;
}
?>