<?php
if (session_status() === PHP_SESSION_NONE) {
    session_start();
}

require_once("public/connection.php");
require_once("public/functions.php");

// 1. Inactivity Session Timeout (30 minutes)
$timeout_duration = 1800;
if (isset($_SESSION['last_activity']) && (time() - $_SESSION['last_activity']) > $timeout_duration) {
    session_unset();
    session_destroy();
    header("Location: ../index.php?timeout=1");
    exit();
}
$_SESSION['last_activity'] = time();

// Ensure user is logged in
if (!isset($_SESSION['role'])) {
    header("Location: ../index.php");
    exit();
}

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

$class = inject_checker($connection, $_GET['class']);
$term = inject_checker($connection, $_GET['term']);
$session = inject_checker($connection, $_GET['session']);

// Fetch school info
$school_name = 'Eduflow Academy';
$admin_q = mysqli_query($connection, "SELECT school_name FROM `administratives` LIMIT 1");
if ($admin_q && $admin_row = mysqli_fetch_assoc($admin_q)) {
    if (!empty($admin_row['school_name'])) {
        $school_name = $admin_row['school_name'];
    }
}

// Fetch distinct subjects
$subjects = [];
$sub_stmt = $connection->prepare("SELECT DISTINCT subjects FROM results1 WHERE class = ? AND term = ? AND session = ? ORDER BY subjects ASC");
$sub_stmt->bind_param("sss", $class, $term, $session);
$sub_stmt->execute();
$sub_res = $sub_stmt->get_result();
while ($s_row = $sub_res->fetch_assoc()) {
    if (!empty($s_row['subjects'])) {
        $subjects[] = $s_row['subjects'];
    }
}
$sub_stmt->close();

if (empty($subjects)) {
    die("No subject results found for {$class} - {$term} ({$session}).");
}

// Fetch all students in this class results
$students_data = [];
$stu_stmt = $connection->prepare("SELECT reg_number, name FROM results1 WHERE class = ? AND term = ? AND session = ? GROUP BY reg_number ORDER BY reg_number ASC");
$stu_stmt->bind_param("sss", $class, $term, $session);
$stu_stmt->execute();
$stu_res = $stu_stmt->get_result();

while ($st = $stu_res->fetch_assoc()) {
    $reg_no = trim($st['reg_number']);
    $st_name = trim($st['name']);
    if (empty($reg_no)) continue;

    $student_scores = [];
    $total_marks = 0;
    $subject_count = 0;

    foreach ($subjects as $subj) {
        $score_stmt = $connection->prepare("SELECT ca1, ca2, exam FROM results1 WHERE reg_number = ? AND subjects = ? AND class = ? AND term = ? AND session = ? LIMIT 1");
        $score_stmt->bind_param("sssss", $reg_no, $subj, $class, $term, $session);
        $score_stmt->execute();
        $sc_res = $score_stmt->get_result();
        $sc = $sc_res->fetch_assoc();
        $score_stmt->close();

        $ca1 = isset($sc['ca1']) ? (float)$sc['ca1'] : 0;
        $ca2 = isset($sc['ca2']) ? (float)$sc['ca2'] : 0;
        $exam = isset($sc['exam']) ? (float)$sc['exam'] : 0;
        $subj_total = $ca1 + $ca2 + $exam;

        $student_scores[$subj] = [
            'ca1' => $ca1,
            'ca2' => $ca2,
            'exam' => $exam,
            'total' => $subj_total
        ];

        $total_marks += $subj_total;
        $subject_count++;
    }

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

    $students_data[] = [
        'reg_number' => $reg_no,
        'name' => $st_name,
        'scores' => $student_scores,
        'total' => $total_marks,
        'average' => $average,
        'position' => ''
    ];
}
$stu_stmt->close();

if (empty($students_data)) {
    die("No student score records found for {$class} - {$term} ({$session}).");
}

// Calculate Class Ranks & Positions
usort($students_data, function($a, $b) {
    if ($b['total'] == $a['total']) {
        return $b['average'] <=> $a['average'];
    }
    return ($b['total'] > $a['total']) ? 1 : -1;
});

function get_ordinal_suffix($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';
}

$rank = 0;
$pos = 0;
$prev_score = null;
foreach ($students_data as &$sd) {
    $rank++;
    if ($sd['total'] !== $prev_score) {
        $pos = $rank;
        $prev_score = $sd['total'];
    }
    $sd['position'] = get_ordinal_suffix($pos);
}
unset($sd);

// Prepare CSV Output
$clean_filename = preg_replace('/[^a-zA-Z0-9_-]/', '_', "Broadsheet_{$class}_{$term}_{$session}") . ".csv";

header('Content-Type: text/csv; charset=UTF-8');
header('Content-Disposition: attachment; filename="' . $clean_filename . '"');
header('Pragma: no-cache');
header('Expires: 0');

$output = fopen('php://output', 'w');

// UTF-8 BOM for Excel compatibility
fprintf($output, chr(0xEF).chr(0xBB).chr(0xBF));

// Title rows
fputcsv($output, [strtoupper($school_name) . " - CLASS RESULT BROADSHEET"]);
fputcsv($output, ["Class: {$class}", "Term: {$term}", "Session: {$session}", "Date Exported: " . date('d/M/Y H:i')]);
fputcsv($output, []); // Blank line

// Table Header Row 1
$header_row_1 = ['S/N', 'Adm No', 'Student Name'];
foreach ($subjects as $subj) {
    $header_row_1[] = strtoupper($subj);
    $header_row_1[] = '';
    $header_row_1[] = '';
    $header_row_1[] = '';
}
$header_row_1[] = 'Grand Total';
$header_row_1[] = 'Average';
$header_row_1[] = 'Position';
fputcsv($output, $header_row_1);

// Table Header Row 2 (Sub-headers for subjects)
$header_row_2 = ['', '', ''];
foreach ($subjects as $subj) {
    $header_row_2[] = 'CA1';
    $header_row_2[] = 'CA2';
    $header_row_2[] = 'EXAM';
    $header_row_2[] = 'TOTAL';
}
$header_row_2[] = '';
$header_row_2[] = '';
$header_row_2[] = '';
fputcsv($output, $header_row_2);

// Student Data Rows
$sn = 1;
$all_totals = [];
$all_averages = [];

foreach ($students_data as $row) {
    $data_row = [
        $sn++,
        $row['reg_number'],
        $row['name']
    ];

    foreach ($subjects as $subj) {
        $sc = $row['scores'][$subj] ?? ['ca1' => 0, 'ca2' => 0, 'exam' => 0, 'total' => 0];
        $data_row[] = $sc['ca1'];
        $data_row[] = $sc['ca2'];
        $data_row[] = $sc['exam'];
        $data_row[] = $sc['total'];
    }

    $data_row[] = $row['total'];
    $data_row[] = $row['average'];
    $data_row[] = $row['position'];

    $all_totals[] = $row['total'];
    $all_averages[] = $row['average'];

    fputcsv($output, $data_row);
}

// Summary Statistics
fputcsv($output, []);
$total_students = count($students_data);
$max_avg = !empty($all_averages) ? max($all_averages) : 0;
$min_avg = !empty($all_averages) ? min($all_averages) : 0;
$class_mean = !empty($all_averages) ? round(array_sum($all_averages) / $total_students, 2) : 0;

fputcsv($output, ['CLASS SUMMARY STATISTICS']);
fputcsv($output, ['Total Students Enrolled in Broadsheet:', $total_students]);
fputcsv($output, ['Total Subjects Computed:', count($subjects)]);
fputcsv($output, ['Highest Average Score:', $max_avg . '%']);
fputcsv($output, ['Lowest Average Score:', $min_avg . '%']);
fputcsv($output, ['Class Mean Average:', $class_mean . '%']);

fclose($output);
exit();
?>
