
<?php
	ob_start();
    session_start();
chdir('../');
	require_once("public/connection.php");
	require_once("public/functions.php");
	require_once('assets/vendor/tcpdf/tcpdf.php');
?>
<?php
	//////////////// DEFAULT NULL VALUES ///////////////
	$error = array();
	$date = date('d/M/Y');
	$select = "--select--";
	$time = date('h:m:s');
?>
<?php
    ////////// POST ACTION TO VIEW ALL THE UPLOADED ADMINISTRATIVE INFORMATION ////////////
    
	// Initialize default variables
	$school_name = '';
	$school_motto = '';
	$school_logo = '';
	$school_favicon = '';
	$school_stamp = '';

	// Query the database
	$query = "SELECT * FROM `administratives`";
	$run_query = mysqli_query($connection, $query);

	if ($run_query && mysqli_num_rows($run_query) > 0) {
		$result = mysqli_fetch_assoc($run_query);
		$school_name = $result['school_name'];
		$school_motto = $result['school_motto'];
		$school_logo = $result['school_logo'];
		$school_favicon = $result['school_favicon'];
		$school_stamp = $result['school_stamp'];
	}


if (!isset($_GET['class'], $_GET['term'], $_GET['session'])) {
    die("Error: Missing parameters.");
}

$class = $_GET['class'];
$term = $_GET['term'];
$session = $_GET['session'];

// Fetch subjects
$subjects = [];
$subject_query = "SELECT DISTINCT subjects FROM results1 WHERE class='$class' AND term='$term' AND session='$session'";
$run_subject_query = mysqli_query($connection, $subject_query);
while ($row = mysqli_fetch_assoc($run_subject_query)) {
    $subjects[] = $row['subjects'];
}

// Fetch students and calculate total scores
$students = [];
$query_results = "SELECT reg_number, name FROM results1 
                  WHERE class='$class' AND term='$term' AND session='$session' 
                  GROUP BY reg_number ORDER BY reg_number ASC";
$run_results = mysqli_query($connection, $query_results);

while ($student = mysqli_fetch_assoc($run_results)) {
    $reg_no = trim($student['reg_number']);
    $full_name = trim($student['name']);

    if (empty($reg_no) || empty($full_name)) {
        continue;
    }

    $name_parts = explode(' ', $full_name);
    $last_name = array_pop($name_parts);
    $initials = '';
    foreach ($name_parts as $part) {
        if (strlen($part) > 0) {
            $initials .= strtoupper(substr($part, 0, 1)) . '.';
        }
    }
    $abbreviated_name = $initials . ' ' . ucfirst($last_name);

    $total_marks = 0;
    $total_subjects = 0;

    foreach ($subjects as $subject) {
        $res = mysqli_query($connection, "SELECT ca1, ca2, exam FROM results1 
                                           WHERE reg_number = '$reg_no' AND subjects = '$subject' 
                                           AND class = '$class' AND term = '$term' AND session = '$session'");
        $row = mysqli_fetch_assoc($res);
        $ca1 = $row['ca1'] ?? 0;
        $ca2 = $row['ca2'] ?? 0;
        $exam = $row['exam'] ?? 0;
        $total_marks += ($ca1 + $ca2 + $exam);
        $total_subjects++;
    }

    $average = $total_subjects > 0 ? round($total_marks / $total_subjects, 2) : 0;

    $students[] = [
        'reg_no' => $reg_no,
        'name' => $abbreviated_name,
        'total' => $total_marks,
        'average' => $average,
    ];
}

usort($students, function($a, $b) {
    return $b['total'] <=> $a['total'];
});

function ordinal($number) {
    if (!in_array(($number % 100), [11, 12, 13])) {
        switch ($number % 10) {
            case 1: return $number . 'st';
            case 2: return $number . 'nd';
            case 3: return $number . 'rd';
        }
    }
    return $number . 'th';
}

$prev_total = null;
$position = 0;
$rank = 0;
foreach ($students as $index => &$student) {
    $rank++;
    if ($student['total'] !== $prev_total) {
        $position = $rank;
        $prev_total = $student['total'];
    }
    $student['position'] = ordinal($position);
}
unset($student);

// TCPDF setup
$pdf = new TCPDF('L', 'mm', 'A4', true, 'UTF-8', false);
$pdf->SetTitle("Class Result Broadsheet - $class - $term - $session");
$pdf->SetHeaderData('', 0, "$school_name Class Result Broadsheet", "$class | $term | $session");
$pdf->setHeaderFont(['helvetica', '', 10]);
$pdf->setFooterFont(['helvetica', '', 8]);
$pdf->SetMargins(10, 20, 10);
$pdf->SetAutoPageBreak(TRUE, 15);
$pdf->AddPage();
$pdf->SetFont('helvetica', '', 6);

// Column sizes
$snWidth = 6;
$admWidth = 11;
$nameWidth = 25;
$subjectCellWidth = 6;
$headerHeight = 5;

// First Header Row
$pdf->Cell($snWidth, $headerHeight * 2, 'S/N', 1, 0, 'C');
$pdf->Cell($admWidth, $headerHeight * 2, 'Adm No', 1, 0, 'C');
$pdf->Cell($nameWidth, $headerHeight * 2, 'Name', 1, 0, 'C');

foreach ($subjects as $subject) {
    $short = strtoupper(substr($subject, 0, 3));
    $pdf->Cell($subjectCellWidth * 3, $headerHeight, $short, 1, 0, 'C');
}

$pdf->Cell(8, $headerHeight * 2, 'Tot', 1, 0, 'C');
$pdf->Cell(8, $headerHeight * 2, 'Avg', 1, 0, 'C');
$pdf->Cell(8, $headerHeight * 2, 'Pos', 1, 1, 'C');

// Second Header Row
$pdf->Cell($snWidth + $admWidth + $nameWidth, 0, '', 0, 0);
foreach ($subjects as $_) {
    $pdf->Cell($subjectCellWidth, $headerHeight, 'CA1', 1, 0, 'C');
    $pdf->Cell($subjectCellWidth, $headerHeight, 'CA2', 1, 0, 'C');
    $pdf->Cell($subjectCellWidth, $headerHeight, 'EX', 1, 0, 'C');
}
$pdf->Ln();

// Body Rows
$i = 0;
foreach ($students as $student) {
    if (empty($student['reg_no']) || empty($student['name'])) {
        continue;
    }

    $i++;
    $reg_no = $student['reg_no'];
    $pdf->Cell($snWidth, 6, $i, 1, 0, 'C');
    $pdf->Cell($admWidth, 6, $reg_no, 1, 0, 'C');
    $pdf->Cell($nameWidth, 6, $student['name'], 1, 0, 'L');

    foreach ($subjects as $subject) {
        $res = mysqli_query($connection, "SELECT ca1, ca2, exam FROM results1 
                                           WHERE reg_number = '$reg_no' AND subjects = '$subject' 
                                           AND class = '$class' AND term = '$term' AND session = '$session'");
        $row = mysqli_fetch_assoc($res);
        $ca1 = $row['ca1'] ?? 0;
        $ca2 = $row['ca2'] ?? 0;
        $exam = $row['exam'] ?? 0;

        $pdf->Cell($subjectCellWidth, 6, $ca1, 1, 0, 'C');
        $pdf->Cell($subjectCellWidth, 6, $ca2, 1, 0, 'C');
        $pdf->Cell($subjectCellWidth, 6, $exam, 1, 0, 'C');
    }

    $pdf->Cell(8, 6, $student['total'], 1, 0, 'C');
    $pdf->Cell(8, 6, $student['average'], 1, 0, 'C');
    $pdf->Cell(8, 6, $student['position'], 1, 1, 'C');
}

// Signature Section
$pdf->Ln(10);
$pdf->SetFont('helvetica', '', 8);
$pdf->Cell(90, 10, 'Class Teacher:', 0, 0, 'L');
$pdf->Cell(90, 10, 'V.P Academic:', 0, 1, 'L');
$pdf->Cell(90, 10, 'Signature:_________________________', 0, 0, 'L');
$pdf->Cell(90, 10, 'Signature:_________________________', 0, 1, 'L');

$pdf->Output("Broadsheet_{$class}_{$term}_{$session}.pdf", 'I');
?>
