<?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.");
}

// Setup
$headers = ['STUDENT_NAME', 'SEX', 'MTC', 'MTC_AGG', 'ENG', 'ENG_AGG', 'SST', 'SST_AGG', 'SCI', 'SCI_AGG', 'TOTAL_MARKS', 'TOTAL_AGG', 'DIVISION'];
$colWidths = [
    'STUDENT_NAME' => 45, '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
];

// Analysis Arrays
$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' => []];

// Initialize subject aggregates
foreach ($subject_aggs as $subject => &$counts) {
    foreach ($agg_values as $val) $counts[$val] = 0;
    $counts['X'] = 0;
}
unset($counts); // Important to break reference

// Count Divisions and Subject Aggregates
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 Generation
$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("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);

$pdf->SetFont('helvetica', 'B', 7);
foreach ($headers as $header) {
    $pdf->Cell($colWidths[$header], 6, str_replace('_', ' ', $header), 1, 0, 'C');
}
$pdf->Ln();

$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();
}

// Page 2: Analysis
$pdf->AddPage();
$pdf->SetFont('helvetica', 'B', 10);
$pdf->Cell(0, 10, "EOT Analysis - Class $class - Term $term - Year $year", 0, 1, 'L');
$pdf->Ln(4);

// Table 1: Division Count
$pdf->SetFont('helvetica', 'B', 8);
$pdf->Cell(0, 6, "Table 1: Division Summary", 0, 1);
$pdf->SetFont('helvetica', '', 8);
$pdf->Cell(25, 6, "Division", 1);
$pdf->Cell(25, 6, "Count", 1);
$pdf->Ln();

foreach ($division_counts as $div => $count) {
    $pdf->Cell(25, 6, $div, 1);
    $pdf->Cell(25, 6, $count, 1);
    $pdf->Ln();
}
$pdf->Cell(25, 6, "Total Sat", 1);
$pdf->Cell(25, 6, array_sum($division_counts), 1);
$pdf->Ln(10);

// Table 2: Subject Aggregate Distribution
$pdf->SetFont('helvetica', 'B', 8);
$pdf->Cell(0, 6, "Table 2: EOT Aggregate Distribution per Subject", 0, 1);

$pdf->SetFont('helvetica', 'B', 7);
$pdf->Cell(15, 6, 'Agg', 1);
foreach (array_keys($subject_aggs) as $subject) {
    $pdf->Cell(25, 6, str_replace('_AGG', '', $subject), 1);
}
$pdf->Ln();

$pdf->SetFont('helvetica', '', 7);
foreach (array_merge($agg_values, ['X']) as $agg) {
    $pdf->Cell(15, 6, $agg, 1);
    foreach ($subject_aggs as $subject => $counts) {
        $pdf->Cell(25, 6, $counts[$agg], 1);
    }
    $pdf->Ln();
}

// Total per subject
$pdf->SetFont('helvetica', 'B', 7);
$pdf->Cell(15, 6, 'Total', 1);
foreach ($subject_aggs as $counts) {
    $pdf->Cell(25, 6, array_sum($counts), 1);
}
$pdf->Ln(5);

// 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->Output("EOT_Class_{$class}_Term_{$term}_{$year}.pdf", 'I');
exit;
?>