<?php
require_once('tcpdf/tcpdf.php');
require_once('db_connection.php');

if (!isset($_POST['CLASS'], $_POST['TERM'], $_POST['YEAR'])) {
    die('Class, Term, or Year not specified.');
}

$class = $_POST['CLASS'];
$term = $_POST['TERM'];
$year = $_POST['YEAR'];

$stmt = $pdo->prepare("
    SELECT r.* 
    FROM RESULTS r
    INNER JOIN STUDENTS s ON r.STUDENT_ID = s.STUDENT_ID
    WHERE r.CLASS = ? AND r.TERM = ? AND r.YEAR = ? AND s.STATUS = 'Sponsored'
    ORDER BY r.STUDENT_NAME
");
$stmt->execute([$class, $term, $year]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!$results) {
    die("No results found for class $class, term $term, year $year.");
}

// Headers including REMARKS
$headers = [
    'STUDENT_NAME', 'SEX', 'MTC', 'MTC_AGG', 'ENG', 'ENG_AGG',
    'SST', 'SST_AGG', 'SCI', 'SCI_AGG',
    'TOTAL_MARKS', 'TOTAL_AGG', 'DIVISION', 'REMARKS'
];

// Column widths adjusted for landscape
$colWidths = [
    'STUDENT_NAME' => 42, 'SEX' => 10, 'MTC' => 10, 'MTC_AGG' => 12,
    'ENG' => 10, 'ENG_AGG' => 12, 'SST' => 10, 'SST_AGG' => 12,
    'SCI' => 10, 'SCI_AGG' => 12, 'TOTAL_MARKS' => 14, 'TOTAL_AGG' => 14,
    'DIVISION' => 14, 'REMARKS' => 30
];

// Prepare Analysis Tables
$division_counts = ['1'=>0, '2'=>0, '3'=>0, '4'=>0, 'U'=>0, 'X'=>0];
$agg_values = range(1, 9);
$subject_aggs = ['MTC_AGG' => [], 'ENG_AGG' => [], 'SST_AGG' => [], 'SCI_AGG' => []];
foreach ($subject_aggs as $subject => &$counts) {
    foreach ($agg_values as $val) $counts[$val] = 0;
    $counts['X'] = 0;
}
unset($counts); // break reference

foreach ($results as $row) {
    $div = $row['DIVISION'];
    if (isset($division_counts[$div])) $division_counts[$div]++;

    foreach (array_keys($subject_aggs) as $subject) {
        $agg = strtoupper(trim($row[$subject]));
        if (isset($subject_aggs[$subject][$agg])) {
            $subject_aggs[$subject][$agg]++;
        }
    }
}

// PDF in Landscape
$pdf = new TCPDF('L', PDF_UNIT, 'A4', true, 'UTF-8', false);
$pdf->SetCreator('Luzira COU Primary School');
$pdf->SetAuthor('Luzira COU Primary School');
$pdf->SetTitle("E.O.T Results - Class $class - Term $term $year");
$pdf->SetMargins(7, 20, 7);
$pdf->SetAutoPageBreak(TRUE, 10);
$pdf->SetFont('helvetica', '', 7);

// PAGE 1: MAIN RESULTS
$pdf->AddPage();
if (file_exists('logo.png')) {
    $pdf->Image('logo.png', 7, 5, 20);
}
$pdf->SetY(10);
$pdf->SetFont('helvetica', 'B', 11);
$pdf->Cell(0, 8, "Luzira COU Primary School", 0, 1, 'C');
$pdf->SetFont('helvetica', '', 9);
$pdf->Cell(0, 7, "Class: $class - Term: $term - Year: $year (End Of Term Results)", 0, 1, 'C');
$pdf->Ln(3);

// Table Header
$pdf->SetFont('helvetica', 'B', 7);
foreach ($headers as $header) {
    $pdf->Cell($colWidths[$header], 6, str_replace('_', ' ', $header), 1, 0, 'C');
}
$pdf->Ln();

// Table Rows
$pdf->SetFont('helvetica', '', 7);
foreach ($results as $row) {
    foreach ($headers as $header) {
        $align = ($header === 'STUDENT_NAME') ? 'L' : 'C';
        $pdf->Cell($colWidths[$header], 6, $row[$header], 1, 0, $align);
    }
    $pdf->Ln();
}



// Footer
$pdf->SetY(-15);
$pdf->SetFont('helvetica', 'I', 7);
$pdf->Cell(0, 5, 'These results can be verified via https://luziracouprimaryschool.com/', 0, 0, 'C');

// Output PDF
$pdf->Output("EOT_Class_{$class}_Term_{$term}_{$year}.pdf", 'I');
exit;
?>