<?php
ini_set('display_errors', 1);

require_once $base_path_to_learning . "vendor/autoload.php";

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

function marksheet_gazette_report_template36(array $data)
{
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $sheet->setTitle('GAZETTE REPORT');

    generate_template36__gazette($sheet, $data);

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

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

function generate_template36__gazette($sheet, $data)
{
    $titleStyle = [
        'font' => ['bold' => true, 'size' => 13],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER
        ]
    ];

    $headerStyle = [
        'font' => ['bold' => true],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER,
            'wrapText'   => true
        ],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]],
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => ['argb' => 'FFEFEFEF']
        ]
    ];

    $cellStyle = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER,
            'wrapText'   => true
        ],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
    ];

    // Top Titles
    $sheet->setCellValue('A1', $data[0]['institution_info']['institution_name'] . ' , ' . $data[0]['branch_info']['name']);
    $sheet->setCellValue('A2', $data[0]['examination_name'] . ' (' . $data[0]['academic_session'] . ')');
    $sheet->setCellValue('A3', 'CLASS : ' . $data[0]['class_name'] . ' - SECTION : ' . $data[0]['section_name']);

    $subjects = ['ENGLISH','HINDI','PUNJABI','MATHS','EVS'];
    $components = ['Periodic Test (25)','Subject Enrichment (20)','Notebook (5)','Half Yearly (50)','Total (100)'];

    $rowMain = 5;
    $rowSub  = 6;

    // Fixed columns
    $sheet->setCellValue('A5', 'R.NO');
    $sheet->setCellValue('B5', 'ADM NO');
    $sheet->setCellValue('C5', 'STUDENT NAME');
    $sheet->mergeCells('A5:A6');
    $sheet->mergeCells('B5:B6');
    $sheet->mergeCells('C5:C6');

    $col = 4;

    // Subject headers
    foreach ($subjects as $subject) {
        $start = $col;
        foreach ($components as $comp) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowSub, $comp);
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($start) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col-1) . $rowMain);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($start) . $rowMain, $subject);
    }

    // Attendance
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($col) . "5:" . Coordinate::stringFromColumnIndex($col) . "6");
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . "5", "ATTENDANCE");

    // Total
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($col) . "5:" . Coordinate::stringFromColumnIndex($col) . "6");
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . "5", "TOTAL MARKS");

    // Percentage
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($col) . "5:" . Coordinate::stringFromColumnIndex($col) . "6");
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . "5", "PERCENTAGE");

    // Grade
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($col) . "5:" . Coordinate::stringFromColumnIndex($col) . "6");
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . "5", "GRADE");

    // Grade Subject Areas
    $gradeSubjects = ['GK','ICT','SPORTS'];
    $startGrade = $col;
    foreach ($gradeSubjects as $gs) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . "6", $gs);
    }
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($startGrade) . "5:" . Coordinate::stringFromColumnIndex($col-1) . "5");
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($startGrade) . "5", "GRADE SUBJECT AREAS");

    // Skill Areas
    $skillSubjects = ['ART','SPEAKING SKILLS','OVERALL CONDUCT'];
    $startSkill = $col;
    foreach ($skillSubjects as $ss) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . "6", $ss);
    }
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($startSkill) . "5:" . Coordinate::stringFromColumnIndex($col-1) . "5");
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($startSkill) . "5", "SKILL SUBJECT AREAS");

    // Remarks
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($col) . "5:" . Coordinate::stringFromColumnIndex($col) . "6");
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . "5", "REMARKS");

    $lastCol = Coordinate::stringFromColumnIndex($col-1);

    $sheet->mergeCells("A1:{$lastCol}1");
    $sheet->mergeCells("A2:{$lastCol}2");
    $sheet->mergeCells("A3:{$lastCol}3");

    $sheet->getStyle("A1:A3")->applyFromArray($titleStyle);
    $sheet->getStyle("A5:{$lastCol}6")->applyFromArray($headerStyle);

    // DATA
    $row = 7;
    foreach ($data as $i => $student) {

        $marks = json_decode($student['data'], true) ?? [];
        $col = 1;

        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $i+1);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $student['admission_number']);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $student['student_name']);

        for ($j = 1; $j <= 25; $j++) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[$j] ?? '');
        }

        // Attendance
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ($marks[26] ?? '') . ' / ' . ($marks[27] ?? ''));

        // Total
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[28] ?? '');

        // Percentage
        $cell = Coordinate::stringFromColumnIndex($col++) . $row;
        $sheet->setCellValue($cell, ($marks[29] ?? 0) / 100);
        $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.00%');

        // Grade
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[30] ?? '');

        // Grade Subjects
        for ($j = 31; $j <= 33; $j++) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[$j] ?? '');
        }

        // Skill Subjects
        for ($j = 34; $j <= 36; $j++) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[$j] ?? '');
        }

        // Remarks
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[37] ?? '');

        $sheet->getStyle("A{$row}:{$lastCol}{$row}")->applyFromArray($cellStyle);
        $row++;
    }
}