<?php
chdir('../');
session_start();
require_once('public/connection.php');
require 'vendor/autoload.php'; // PhpSpreadsheet

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

if (!isset($_SESSION['staff_id'])) {
    header("Location: ../index.php");
    exit;
}

// Collect filters
$filter_class   = $_GET['class'] ?? '';
$filter_term    = $_GET['term'] ?? '';
$filter_session = $_GET['session'] ?? '';
$filter_subject = $_GET['subject_id'] ?? '';
$filter_exam_type = $_GET['exam_type'] ?? '';

$where = [];
if ($filter_class) $where[] = "r.class = '" . $conn->real_escape_string($filter_class) . "'";
if ($filter_term) $where[] = "r.term = '" . $conn->real_escape_string($filter_term) . "'";
if ($filter_session) $where[] = "r.session = '" . $conn->real_escape_string($filter_session) . "'";
if ($filter_subject) $where[] = "r.subject = (SELECT subjects FROM school_subjects WHERE id = '" . $conn->real_escape_string($filter_subject) . "')";
if ($filter_exam_type) $where[] = "r.exam_type = '" . $conn->real_escape_string($filter_exam_type) . "'";
$where_sql = $where ? 'WHERE ' . implode(' AND ', $where) : '';

$sql = "
    SELECT 
        r.*, s.firstname, s.lastname, s.othername, s.reg_number, s.class AS student_class
    FROM exam_group_results r
    JOIN students s ON r.student_id = s.id
    $where_sql
    ORDER BY r.student_id DESC
";
$results = $conn->query($sql);

// Spreadsheet
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle("Exam Results");

$headers = ['Student', 'Reg. Number', 'Class', 'Subject', 'Term', 'Session', 'Exam Type', 'OBJ', 'Theory', 'Total', 'Status'];
$sheet->fromArray($headers, NULL, 'A1');

$rowNum = 2;
while ($row = $results->fetch_assoc()) {
    $name = $row['lastname'] . ' ' . $row['firstname'] . ' ' . $row['othername'];
    $sheet->fromArray([
        $name,
        $row['reg_number'],
        $row['student_class'],
        $row['subject'],
        $row['term'],
        $row['session'],
        strtoupper($row['exam_type']),
        $row['mcq_score'],
        $row['theory_score'],
        $row['total'],
        $row['status']
    ], NULL, 'A' . $rowNum);
    $rowNum++;
}

// Output
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="exam_results.xlsx"');
header('Cache-Control: max-age=0');

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
