<?php
require_once('tcpdf/tcpdf.php');
require_once('db_connection.php');

// Validate input
if (!isset($_POST['CLASS'], $_POST['TERM'], $_POST['YEAR'])) {
    die('Class, Term, or Year not specified.');
}

$class = $_POST['CLASS'];
$term = $_POST['TERM'];
$year = $_POST['YEAR'];

// Fetch data from the view EOT_RANKED_RESULTS and only Sponsored students
$stmt = $pdo->prepare("
    SELECT r.* 
    FROM EOT_RANKED_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 end of term results found for class $class, term $term, year $year.");
}

// Add REMARKS to headers
$headers = ['STUDENT_ID', 'STUDENT_NAME', 'CLASS', 'MTC', 'ENG', 'LIT1', 'LIT2', 'RE', 'TOTAL_MARKS', 'POSITION', 'REMARKS'];

// Calculate dynamic column widths
$colWidths = [];
foreach ($headers as $header) {
    $maxLen = strlen(str_replace('_', ' ', $header));
    foreach ($results as $row) {
        if (isset($row[$header])) {
            $maxLen = max($maxLen, strlen((string)$row[$header]));
        }
    }
    $colWidths[$header] = ($maxLen * 2.8) + 6;
}

$marginTotal = 14;
$pageWidth = 210 - $marginTotal;
$totalWidth = array_sum($colWidths);
if ($totalWidth > $pageWidth) {
    $scale = $pageWidth / $totalWidth;
    foreach ($colWidths as $key => $width) {
        $colWidths[$key] = $width * $scale;
    }
}

// Create PDF (Portrait A4)
$pdf = new TCPDF('P', PDF_UNIT, 'A4', true, 'UTF-8', false);
$pdf->SetCreator('Luzira COU Primary School');
$pdf->SetAuthor('Luzira COU Primary School');
$pdf->SetTitle("End of Term Mark List - $class - $term $year");
$pdf->SetMargins(7, 25, 7);
$pdf->SetAutoPageBreak(TRUE, 10);
$pdf->SetFont('helvetica', '', 9);
$pdf->AddPage();

// Add Logo
$logoPath = 'logo.png';
if (file_exists($logoPath)) {
    $pdf->Image($logoPath, 10, 5, 20);
}
$pdf->SetY(10);
$pdf->SetFont('helvetica', 'B', 12);
$pdf->Cell(0, 10, "LUZIRA CHURCH OF UGANDA PRIMARY SCHOOL", 0, 1, 'C');
$pdf->SetFont('helvetica', '', 12);
$pdf->Cell(0, 10, "END OF TERM MARK LIST - CLASS: $class   TERM: $term   YEAR: $year", 0, 1, 'C');
$pdf->Ln(2);

// Table Header
$pdf->SetFont('helvetica', 'B', 10);
foreach ($headers as $header) {
    $label = str_replace('_', ' ', $header);
    $pdf->Cell($colWidths[$header], 8, $label, 1, 0, 'C');
}
$pdf->Ln();

// Table Rows
$pdf->SetFont('helvetica', '', 9);
foreach ($results as $row) {
    foreach ($headers as $header) {
        $align = ($header === 'STUDENT_NAME') ? 'L' : 'C';
        $pdf->Cell($colWidths[$header], 8, $row[$header], 1, 0, $align);
    }
    $pdf->Ln();
}

// Footer note
$pdf->SetY(-15);
$pdf->SetFont('helvetica', 'I', 8);
$pdf->Cell(0, 10, 'These results can be verified via https://luziracouprimaryschool.com/', 0, 0, 'C');

// Output PDF
$pdf->Output("EndOfTerm_Class_{$class}_Term_{$term}_{$year}.pdf", 'I');
exit;
?>