<?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_template31(array $data)
{
    ob_start();

    $spreadsheet = new Spreadsheet();
    $spreadsheet->removeSheetByIndex(0);

    $sheet1 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'TERM-I');
    $spreadsheet->addSheet($sheet1, 0);
    generate_template31_term1_gazette($sheet1, $data);

    $sheet2 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'TERM-II');
    $spreadsheet->addSheet($sheet2, 1);
    generate_template31_term2_gazette($sheet2, $data);

    $spreadsheet->setActiveSheetIndex(0);

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

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

function t30_border()
{
    return ['allBorders' => ['borderStyle' => Border::BORDER_THIN]];
}

function t30_header_style()
{
    return [
        'font' => ['bold' => true],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER
        ],
        'borders' => t30_border(),
        'fill' => [
            'fillType' => Fill::FILL_SOLID,
            'startColor' => ['argb' => 'FFEFEFEF']
        ]
    ];
}

function t30_cell_style()
{
    return [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER
        ],
        'borders' => t30_border()
    ];
}

function generate_template31_term1_gazette($sheet, $data)
{
    if (empty($data)) return;

    // STYLES
    $titleStyle = [
        'font' => ['bold' => true, 'size' => 13],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER
        ]
    ];

    $headerStyle = [
        'font' => ['bold' => true, 'size' => 10],
        '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
        ],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
    ];

    // COLUMN WIDTH 
    $sheet->getColumnDimension('A')->setWidth(6);
    $sheet->getColumnDimension('B')->setWidth(12);
    $sheet->getColumnDimension('C')->setWidth(22);

    for ($i = 4; $i <= 120; $i++) {
        $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i))->setWidth(13);
    }

    // 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']);

    // SUBJECT CONFIG
    $subjectsBeforeGT = ['ENGLISH', 'HINDI', 'PUNJABI', 'MATHS', 'SCIENCE', 'S.ST'];
    $subjectsAfterGT  = ['DRAWING', 'COMPUTER', 'Aptitude'];

    $subjectSubHeaders = [
        'PT (40/4=10)',
        'Subject Enrichment 5',
        'Note Book 5',
        'Half Yearly 80',
        'Grand Total 100',
        'Grade'
    ];

    $gTotalHeaders = ['PT TOTAL', 'Subject Enrichment TOTAL', 'Note Book TOTAL'];

    $rowMain = 5;
    $rowSub  = 6;

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

    $col = 4;

    // FIRST 6 SUBJECTS
    foreach ($subjectsBeforeGT as $subject) {
        $start = $col;
        foreach ($subjectSubHeaders as $h) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowSub, $h);
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($start) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col - 1) . $rowMain);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($start) . $rowMain, $subject);
    }

    // G. TOTAL
    $gtStart = $col;
    foreach ($gTotalHeaders as $h) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowSub, $h);
    }
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($gtStart) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col - 1) . $rowMain);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($gtStart) . $rowMain, 'G. TOTAL');

    foreach ($subjectsAfterGT as $subject) {
        $start = $col;
        foreach ($subjectSubHeaders as $h) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowSub, $h);
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($start) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col - 1) . $rowMain);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($start) . $rowMain, $subject);
    }

    $sheet->mergeCells(Coordinate::stringFromColumnIndex($col) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col) . $rowSub);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowMain, 'PERCENTAGE');

    // HEALTH STATUS
    $hsStart = $col;
    foreach (['HEIGHT', 'WEIGHT', 'BLOOD GROUP'] as $h) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowSub, $h);
        $sheet->getRowDimension(5)->setRowHeight(35);
        $sheet->getRowDimension(6)->setRowHeight(45);
    }
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($hsStart) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col - 1) . $rowMain);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($hsStart) . $rowMain, 'HEALTH STATUS');

    // ATTENDANCE
    $attStart = $col;
    foreach (['TERM-I', 'TERM-II', 'TOTAL'] as $a) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowSub, $a);
    }
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($attStart) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col - 1) . $rowMain);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($attStart) . $rowMain, 'ATTENDANCE');

    foreach (['COMPETITION', "CLASS TEACHER'S REMARKS"] as $f) {
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($col) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col) . $rowSub);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowMain, $f);
    }

    $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);

    $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']);

        // First 6 subjects (78–113)
        for ($idx = 78; $idx <= 113; $idx++) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[$idx] ?? '');
        }

        // G. TOTAL (114–116)
        for ($idx = 114; $idx <= 116; $idx++) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[$idx] ?? '');
        }

        // Remaining subjects (118–135)
        for ($idx = 118; $idx <= 135; $idx++) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[$idx] ?? '');
        }

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

        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[137] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[138] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[139] ?? '');

        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ($marks[143] ?? '') . '/' . ($marks[140] ?? ''));
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ($marks[144] ?? '') . '/' . ($marks[141] ?? ''));
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ($marks[145] ?? '') . '/' . ($marks[142] ?? ''));

        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ($marks[149] ?? '') . ', ' . ($marks[150] ?? ''));
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[151] ?? '');

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

function generate_template31_term2_gazette($sheet, $data)
{
    if (empty($data)) return;

    // STYLES
    $titleStyle = [
        'font' => ['bold' => true, 'size' => 13],
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical'   => Alignment::VERTICAL_CENTER
        ]
    ];

    $headerStyle = [
        'font' => ['bold' => true, 'size' => 10],
        '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
        ],
        'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
    ];

    // COLUMN WIDTH 
    $sheet->getColumnDimension('A')->setWidth(6);
    $sheet->getColumnDimension('B')->setWidth(12);
    $sheet->getColumnDimension('C')->setWidth(22);

    for ($i = 4; $i <= 120; $i++) {
        $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i))->setWidth(13);
    }

    // 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']);

    // SUBJECT CONFIG
    $subjectsBeforeGT = ['ENGLISH', 'HINDI', 'PUNJABI', 'MATHS', 'SCIENCE', 'S.ST'];
    $subjectsAfterGT  = ['DRAWING', 'COMPUTER', 'APTITUDE'];

    $subjectSubHeaders = [
        'PT (40/4=10)',
        'Subject Enrichment 5',
        'Note Book 5',
        'Half Yearly 80',
        'Grand Total 100',
        'Grade'
    ];

    $gTotalHeaders = ['PT TOTAL', 'Subject Enrichment TOTAL', 'Note Book TOTAL'];

    $rowMain = 5;
    $rowSub  = 6;

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

    $col = 4;

    // FIRST 6 SUBJECTS
    foreach ($subjectsBeforeGT as $subject) {
        $start = $col;
        foreach ($subjectSubHeaders as $h) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowSub, $h);
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($start) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col - 1) . $rowMain);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($start) . $rowMain, $subject);
    }

    // G. TOTAL
    $gtStart = $col;
    foreach ($gTotalHeaders as $h) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowSub, $h);
    }
    $sheet->mergeCells(Coordinate::stringFromColumnIndex($gtStart) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col - 1) . $rowMain);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($gtStart) . $rowMain, 'G. TOTAL');

    foreach ($subjectsAfterGT as $subject) {
        $start = $col;
        foreach ($subjectSubHeaders as $h) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowSub, $h);
        }
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($start) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col - 1) . $rowMain);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($start) . $rowMain, $subject);
    }

    $sheet->mergeCells(Coordinate::stringFromColumnIndex($col) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col) . $rowSub);
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowMain, 'PERCENTAGE');

    // HEALTH STATUS
    $psStart = $col;
    foreach (['UNIFORM','CIRCULAR FOLLOW UP','HOMEWORK/ACT.SUBMISSION','PTM/ATTENDANCE'] as $ps) {
        $sheet->mergeCells(
            Coordinate::stringFromColumnIndex($col) . $rowSub . ':' .
            Coordinate::stringFromColumnIndex($col + 1) . $rowSub
        );
        $sheet->setCellValue(
            Coordinate::stringFromColumnIndex($col) . $rowSub,
            $ps
        );
        $sheet->getRowDimension(5)->setRowHeight(35);
        $sheet->getRowDimension(6)->setRowHeight(45);
        $col += 2;
    }
    
    $sheet->mergeCells(
        Coordinate::stringFromColumnIndex($psStart) . $rowMain . ':' .
        Coordinate::stringFromColumnIndex($col - 1) . $rowMain
    );
    $sheet->setCellValue(
        Coordinate::stringFromColumnIndex($psStart) . $rowMain,
        'PARENTAL SUPPORT'
    );

    $taStart = $col;
    foreach (['PERSONAL HYGIENE','PUNCTUALITY','VALUE SYSTEM','SELF-CONFIDENCE'] as $ta) {
        $sheet->mergeCells(
            Coordinate::stringFromColumnIndex($col) . $rowSub . ':' .
            Coordinate::stringFromColumnIndex($col + 1) . $rowSub
        );
        $sheet->setCellValue(
            Coordinate::stringFromColumnIndex($col) . $rowSub,
            $ta
        );
        $sheet->getRowDimension(5)->setRowHeight(35);
        $sheet->getRowDimension(6)->setRowHeight(45);
        $col += 2;
    }
    $sheet->mergeCells(
        Coordinate::stringFromColumnIndex($taStart) . $rowMain . ':' .
        Coordinate::stringFromColumnIndex($col - 1) . $rowMain
    );
    $sheet->setCellValue(
        Coordinate::stringFromColumnIndex($taStart) . $rowMain,
        'TEACHERS ASSESSMENT'
    );

    foreach (["CLASS TEACHER'S REMARKS", 'PROMOTED TO CLASS'] as $f) {
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($col) . $rowMain . ':' . Coordinate::stringFromColumnIndex($col) . $rowSub);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $rowMain, $f);
    }

    $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);

    $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 ($idx = 1; $idx <= 36; $idx++) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[$idx] ?? '');
        }

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

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

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

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

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

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