<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

require_once '../../../vendor/autoload.php';

function generateExcelReport(
    $conn_main,
    $conn_specific,
    $analysis,
    $institution_id,
    $academic_id,
    $class_id,
    $section_id,
    $marksheet_id
) {

    // ========================================
    // FETCH HEADER DETAILS
    // ========================================
    $inst = $conn_main->prepare("SELECT institution_name FROM institutions WHERE id = ?");
    $inst->execute([$institution_id]);
    $institution_name = $inst->fetchColumn() ?? "Institution";

    $ac = $conn_specific->prepare("SELECT name FROM academic_year WHERE id = ?");
    $ac->execute([$academic_id]);
    $academic_year = $ac->fetchColumn() ?? "Academic Year";

    $cls = $conn_specific->prepare("SELECT classname FROM class_info WHERE id = ?");
    $cls->execute([$class_id]);
    $class_name = $cls->fetchColumn() ?? "Class";

    $sec = $conn_specific->prepare("SELECT section_name FROM section_info WHERE id = ?");
    $sec->execute([$section_id]);
    $section_name = $sec->fetchColumn() ?? "";

    $ms = $conn_specific->prepare("SELECT examination_name FROM marksheet WHERE id = ?");
    $ms->execute([$marksheet_id]);
    $marksheet_name = $ms->fetchColumn() ?? "Exam";


    // ========================================
    // CREATE SHEET
    // ========================================
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle("Analysis Report");


    // ========================================
    // HEADER FORMAT YOU REQUESTED
    // ========================================

    // A1 → Institution Name
    $sheet->mergeCells("A1:L1");
    $sheet->setCellValue("A1", $institution_name);
    $sheet->getStyle("A1")->getFont()->setSize(16)->setBold(true);
    $sheet->getStyle("A1")->getAlignment()->setHorizontal('center');

    // A2 → Main Title
    $sheet->mergeCells("A2:L2");
    $sheet->setCellValue("A2", "Marks Analysis Report");
    $sheet->getStyle("A2")->getFont()->setSize(14)->setBold(true);
    $sheet->getStyle("A2")->getAlignment()->setHorizontal('center');

    // A3 → Academic Year / Exam Name
    $sheet->mergeCells("A3:L3");
    $sheet->setCellValue("A3", "Academic Year: $academic_year / $marksheet_name");
    $sheet->getStyle("A3")->getFont()->setBold(true);
    $sheet->getStyle("A3")->getAlignment()->setHorizontal('center');

    // A4 → Class / Section
    $sheet->mergeCells("A4:L4");
    $sheet->setCellValue("A4", "Class: $class_name / $section_name");
    $sheet->getStyle("A4")->getFont()->setBold(true);
    $sheet->getStyle("A4")->getAlignment()->setHorizontal('center');

    // Row 5 → Blank Row
    $blankRow = 5;

    // TABLE COLUMN HEADERS (STARTS AT ROW 6)
    $headerRow = 6;

    $columns = [
        "A" => "Subject",
        "B" => "Total Students",
        "C" => "PASS",
        "D" => "FAIL",
        "E" => "A1",
        "F" => "A2",
        "G" => "B1",
        "H" => "B2",
        "I" => "C1",
        "J" => "C2",
        "K" => "D",
        "L" => "E"
    ];

    foreach ($columns as $col => $label) {
        $sheet->setCellValue("$col$headerRow", $label);
    }

    $sheet->getStyle("A$headerRow:L$headerRow")->getFont()->setBold(true);


    // FILL DATA (ROW 7 ONWARDS)
    $row = $headerRow + 1;

    foreach ($analysis as $subject => $data) {

        $sheet->setCellValue("A$row", $subject);
        $sheet->setCellValue("B$row", $data['total_students'] ?? 0);
        $sheet->setCellValue("C$row", $data['PASS'] ?? 0);
        $sheet->setCellValue("D$row", $data['FAIL'] ?? 0);
        $sheet->setCellValue("E$row", $data['A1'] ?? 0);
        $sheet->setCellValue("F$row", $data['A2'] ?? 0);
        $sheet->setCellValue("G$row", $data['B1'] ?? 0);
        $sheet->setCellValue("H$row", $data['B2'] ?? 0);
        $sheet->setCellValue("I$row", $data['C1'] ?? 0);
        $sheet->setCellValue("J$row", $data['C2'] ?? 0);
        $sheet->setCellValue("K$row", $data['D'] ?? 0);
        $sheet->setCellValue("L$row", $data['E'] ?? 0);

        $row++;
    }


    // Auto-size
    foreach (range('A','L') as $col) {
        $sheet->getColumnDimension($col)->setAutoSize(true);
    }

    // OUTPUT
    $filename = "Subject_Analysis_Report" . ".xlsx";

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

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