<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

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

function generateExcelReport(
    $conn_main,
    $conn_specific,
    $analysis,         // SUBJECT-WISE ANALYSIS ARRAY
    $institution_id,
    $academic_id,
    $class_id,
    $section_id,
    $marksheet_id
) {

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

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

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

    $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() ?? "";


    // SUBJECT LIST = KEYS OF ANALYSIS ARRAY
    $subjects = array_keys($analysis);

    // Fixed rows (grade-level)
    $gradeRows = [
        "NO OF STUDENTS",
        "A1",
        "A2",
        "B1",
        "B2",
        "C1",
        "C2",
        "D",
        "E",
        "PASS",
        "FAIL"
    ];

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

    // HEADER FORMAT
    $lastColumn = Coordinate::stringFromColumnIndex(count($subjects) + 1);

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

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

    // Academic + Exam
    $sheet->mergeCells("A3:$lastColumn" . "3");
    $sheet->setCellValue("A3", "$marksheet_name - ($academic_year)");
    $sheet->getStyle("A3")->getFont()->setSize(13)->setBold(true);
    $sheet->getStyle("A3")->getAlignment()->setHorizontal('center');

    // Class / Section
    $sheet->mergeCells("A4:$lastColumn" . "4");
    $sheet->setCellValue("A4", "Class: $class_name - Section: $section_name");
    $sheet->getStyle("A4")->getFont()->setSize(12)->setBold(true);
    $sheet->getStyle("A4")->getAlignment()->setHorizontal('center');


    // TABLE HEADER (ROW 6)
    $sheet->setCellValue("A6", "GRADES / SUBJECT");
    $sheet->getStyle("A6")->getFont()->setBold(true);

    $col = 2; // column B
    foreach ($subjects as $sub) {
        $colString = Coordinate::stringFromColumnIndex($col);
        $sheet->setCellValue($colString . "6", $sub);
        $col++;
    }

    // APPLY GREY HEADER BACKGROUND
    $headerRange = "A6:" . Coordinate::stringFromColumnIndex($col - 1) . "6";
    $sheet->getStyle($headerRange)->getFill()->setFillType(Fill::FILL_SOLID);
    $sheet->getStyle($headerRange)->getFill()->getStartColor()->setRGB('D9D9D9');

    // Make header bold
    $sheet->getStyle($headerRange)->getFont()->setBold(true);


    // TABLE BODY (ROW 7 onwards)
    $row = 7;

    foreach ($gradeRows as $gradeLabel) {

        $sheet->setCellValue("A$row", $gradeLabel);

        $col = 2;
        foreach ($subjects as $sub) {

            if ($gradeLabel === "NO OF STUDENTS") {
                $value = $analysis[$sub]['total_students'] ?? 0;
            } elseif ($gradeLabel === "PASS") {
                $value = $analysis[$sub]['PASS'] ?? 0;
            } elseif ($gradeLabel === "FAIL") {
                $value = $analysis[$sub]['FAIL'] ?? 0;
            } else {
                $value = $analysis[$sub][$gradeLabel] ?? 0;
            }

            $colString = Coordinate::stringFromColumnIndex($col);
            $sheet->setCellValue($colString . $row, $value);

            $col++;
        }

        $row++;
    }


    // FULL TABLE BORDER
    $lastColumn = Coordinate::stringFromColumnIndex(count($subjects) + 1);
    $lastRow = $row - 1;

    $tableRange = "A6:" . $lastColumn . $lastRow;

    // All thin borders
    $sheet->getStyle($tableRange)->getBorders()->getAllBorders()
          ->setBorderStyle(Border::BORDER_THIN);

    // Outer thick border
    $sheet->getStyle($tableRange)->getBorders()->getOutline()
          ->setBorderStyle(Border::BORDER_MEDIUM);


    // AUTO WIDTH
    for ($i = 1; $i <= count($subjects) + 1; $i++) {
        $colString = Coordinate::stringFromColumnIndex($i);
        $sheet->getColumnDimension($colString)->setAutoSize(true);
    }

    // OUTPUT FILE
    $filename = "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;
}
?>
