<?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_template11($data)
{
    $spreadsheet = new Spreadsheet();

    // Remove default sheet
    $spreadsheet->removeSheetByIndex(0);

    // Create first sheet (Marksheet)
    $sheet1 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'MARKSHEET');
    $spreadsheet->addSheet($sheet1, 0);
    $spreadsheet->setActiveSheetIndex(0);

    // Generate content for first sheet
    generateMarksheet11($spreadsheet->getActiveSheet(), $data);

    // Return to first sheet
    $spreadsheet->setActiveSheetIndex(0);

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

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

function generateMarksheet11($sheet, $data)
{
    // ===== Document properties =====
    $sheet->getParent()->getProperties()
        ->setCreator("Rejoice Learning")
        ->setTitle("Marksheet Export - Template 11")
        ->setSubject("Student Marksheet Data");

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

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

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

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

    // --- Top Titles (Rows 1-3) ---
    $sheet->setCellValue('A1', $data[0]['institution_info']['institution_name'] . ' , ' . $data[0]['branch_info']['name']);
    $sheet->setCellValue('A2', 'RESULT OF UNIT TEST (' . $data[0]['academic_session'] . ')');
    $sheet->setCellValue('A3', 'CLASS - ' . $data[0]['class_name'] . ' : SECTION - ' . $data[0]['section_name']);

    $sheet->getRowDimension(4)->setRowHeight(15);

    // --- Template 11 evaluation definitions ---
    $template11Evaluations = [
        'ENGLISH' => [
            'Can write neatly' => 1,
            'Can narrate stories effectively' => 2,
            'Can recite poems with expressions' => 3,
            'Can recognize and read properly' => 4,
            'Written' => 5,
            'Oral' => 6,
        ],
        'MATHEMATICS' => [
            'Can identify the numbers correctly' => 7,
            'Can grasp the combination of numbers' => 8,
            'Can count and write in sequence' => 9,
            'Written' => 10,
            'Oral' => 11,
        ],
        'PUNJABI' => [
            'Can express the word' => 12,
            'Can write properly' => 13,
            'Written' => 14,
            'Oral' => 15,
        ],
        'E.V.S. / General Knowledge' => [
            'Can identify' => 16,
            'Can write properly' => 17,
            'Can express / understand the word / pictures' => 18,
            'Written' => 19,
            'Oral' => 20,
        ],
        'ART & CRAFT' => [
            'Enjoys drawing, colouring & pasting' => 21,
            'Follows instructions carefully' => 22,
        ],
        'RHYMES' => [
            'Written' => 23,
            'Oral' => 24,
        ],
        'STORY' => [
            'Written' => 25,
            'Oral' => 26,
        ],
        'Conversation' => [
            'Can give conversation answer' => 27,
        ],
        'Remarks if any' => [
            'Remarks' => 28
        ],
    ];

    $subjectColumnAllocation = [
        'ENGLISH' => 6,
        'MATHEMATICS' => 5,
        'PUNJABI' => 4,
        'E.V.S. / General Knowledge' => 5,
        'ART & CRAFT' => 2,
        'RHYMES' => 2,
        'STORY' => 2,
        'Conversation' => 1,
        'Remarks if any' => 1,
    ];

    $excelColStarts = [
        'R. NO' => 1,
        'ADM. NO' => 2,
        'NAME OF THE STUDENT' => 3,
        'ENGLISH' => 4,
        'MATHEMATICS' => 10,
        'PUNJABI' => 15,
        'E.V.S. / General Knowledge' => 19,
        'ART & CRAFT' => 24,
        'RHYMES' => 26,
        'STORY' => 28,
        'Conversation' => 30,
        'Remarks if any' => 31,
    ];

    // --- Header Structure (Rows 5-6) ---
    $currentCol = 1;

    foreach (['R. NO', 'ADM. NO', 'NAME OF THE STUDENT'] as $header) {
        $colLetter = Coordinate::stringFromColumnIndex($currentCol);
        $sheet->setCellValue($colLetter . '5', $header);
        $sheet->mergeCells($colLetter . '5:' . $colLetter . '6');
        $sheet->getStyle($colLetter . '5')->applyFromArray($studentInfoColHeaderStyle);
        $currentCol++;
    }

    foreach ($subjectColumnAllocation as $subjectName => $allocatedCols) {
        $startColForSubject = $currentCol;
        $subHeadersToUse = $template11Evaluations[$subjectName] ?? [];
        $headerCounter = 0;

        foreach ($subHeadersToUse as $subHeaderTitle => $dataKey) {
            if ($headerCounter < $allocatedCols) {
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($currentCol) . '6', $subHeaderTitle);
                $headerCounter++;
                $currentCol++;
            } else {
                break;
            }
        }

        while ($headerCounter < $allocatedCols) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($currentCol) . '6', '');
            $headerCounter++;
            $currentCol++;
        }

        $endColForSubject = $currentCol - 1;
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startColForSubject) . '5', $subjectName);
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startColForSubject) . '5:' . Coordinate::stringFromColumnIndex($endColForSubject) . '5');
    }

    $lastHeaderColIndex = $currentCol - 1;
    $lastHeaderColLetter = Coordinate::stringFromColumnIndex($lastHeaderColIndex);

    $sheet->mergeCells("A1:{$lastHeaderColLetter}1");
    $sheet->mergeCells("A2:{$lastHeaderColLetter}2");
    $sheet->mergeCells("A3:{$lastHeaderColLetter}3");
    $sheet->getStyle("A1:{$lastHeaderColLetter}3")->applyFromArray($titleStyle);

    $sheet->getStyle("A5:{$lastHeaderColLetter}6")->applyFromArray($headerStyle);

    $remarksStartCol = $excelColStarts['Remarks if any'];
    if ($remarksStartCol) {
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($remarksStartCol) . '5:' . Coordinate::stringFromColumnIndex($remarksStartCol) . '6');
    }

    $sheet->getColumnDimension(Coordinate::stringFromColumnIndex(1))->setWidth(8);
    $sheet->getColumnDimension(Coordinate::stringFromColumnIndex(2))->setWidth(14);
    $sheet->getColumnDimension(Coordinate::stringFromColumnIndex(3))->setWidth(28);

    // --- Data Rows ---
    $currentRow = 7;
    foreach ($data as $index => $studentData) {
        $jsonData  = json_decode($studentData['data'], true);
        $analytics = is_string($studentData['analytics'])
            ? json_decode($studentData['analytics'], true)
            : ($studentData['analytics'] ?? []);

        $sheet->setCellValue('A' . $currentRow, $index + 1);
        $sheet->setCellValue('B' . $currentRow, $studentData['admission_number']);
        $sheet->setCellValue('C' . $currentRow, $studentData['student_name']);
        $sheet->getStyle("A{$currentRow}")->applyFromArray($dataCellStyle);
        $sheet->getStyle("B{$currentRow}")->applyFromArray($dataCellStyle);
        $sheet->getStyle("C{$currentRow}")->applyFromArray($studentDataLeftAlignStyle);

        foreach ($template11Evaluations as $subjectName => $items) {
            if (!isset($excelColStarts[$subjectName])) {
                continue;
            }
            $baseCol = $excelColStarts[$subjectName];
            $colOffset = 0;
            $allocated = $subjectColumnAllocation[$subjectName] ?? PHP_INT_MAX;

            foreach ($items as $itemDescription => $dataKey) {
                if ($colOffset >= $allocated) break;

                $targetCol = $baseCol + $colOffset;
                $value = is_numeric($dataKey) ? ($jsonData[$dataKey] ?? '') : '';

                $cellAddress = Coordinate::stringFromColumnIndex($targetCol) . $currentRow;
                $sheet->setCellValue($cellAddress, $value);
                $sheet->getStyle($cellAddress)->applyFromArray($dataCellStyle);

                $colOffset++;
            }
        }

        $remarksCol = $excelColStarts['Remarks if any'];
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($remarksCol) . $currentRow, $jsonData[28] ?? '');
        $sheet->getStyle(Coordinate::stringFromColumnIndex($remarksCol) . $currentRow)->applyFromArray($dataCellStyle);

        $currentRow++;
    }

    for ($col = 1; $col <= $lastHeaderColIndex; $col++) {
        $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($col))->setAutoSize(true);
    }
}
?>
