<?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 RANKED_MIDTERM_LOWER
$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 midterm lower results found for class $class, term $term, year $year.");
}

// Define headers (no REMARKS)
$headers = ['STUDENT_ID', 'STUDENT_NAME', 'CLASS', 'MTC', 'ENG', 'LIT1', 'LIT2', 'RE', 'TOTAL_MARKS', 'POSITION'];

// Calculate dynamic column widths (in mm) based on max length of content or header
$colWidths = [];
foreach ($headers as $header) {
    $maxLen = strlen(str_replace('_', ' ', $header)); // header length

    foreach ($results as $row) {
        if (isset($row[$header])) {
            $maxLen = max($maxLen, strlen((string)$row[$header]));
        }
    }
    // scale length to mm, approx 2.8 mm per character + 6 mm padding for good spacing
    $colWidths[$header] = ($maxLen * 2.8) + 6;
}

// Sum widths, if total exceeds page width (A4 width = 210 mm, margins ~14 mm total)
// Scale down proportionally to fit page width comfortably
$marginTotal = 14; // left + right margins total
$pageWidth = 210 - $marginTotal;

$totalWidth = array_sum($colWidths);
if ($totalWidth > $pageWidth) {
    $scale = $pageWidth / $totalWidth;
    foreach ($colWidths as $key => $width) {
        $colWidths[$key] = $width * $scale;
    }
}

// Create PDF
$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("Midterm 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();
}

// Output PDF
$pdf->Output("Midterm_Lower_Class_{$class}_Term_{$term}_{$year}.pdf", 'I');
exit;
?>