<?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_template15($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
    generateMarksheet15($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="Template15_Marksheet.xlsx"');
    header('Cache-Control: max-age=0');

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

function generateMarksheet15($sheet, $data)
{
    // ===== Document properties =====
    $sheet->getParent()->getProperties()
        ->setCreator("Rejoice Learning")
        ->setTitle("Marksheet Export - Template 15")
        ->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' => 11],
        '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']]
    ];

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

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

    // ===== Subjects and headers =====
    $subjects   = ['English', 'Mathematics', 'Science', 'Social Science', 'Punjabi', 'Hindi'];
    $subHeaders = ['Maximum Marks', 'Marks Obtained', 'Percentage', 'Grade'];

    // Fixed student info columns
    $colIndex = 1;
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', 'R. NO');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', 'ADM. NO');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', 'NAME OF THE STUDENT');

    // Subjects with subheaders
    foreach ($subjects as $subject) {
        $startCol = $colIndex;
        foreach ($subHeaders as $sub) {
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '6', $sub);
        }
        $endCol = $colIndex - 1;
        $sheet->mergeCells(Coordinate::stringFromColumnIndex($startCol) . '5:' . Coordinate::stringFromColumnIndex($endCol) . '5');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($startCol) . '5', $subject);
    }

    // Overall headers
    $overallHeaders = ['Total Marks', 'Percentage', 'Grade', 'Remarks'];
    foreach ($overallHeaders as $head) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', $head);
    }

    // Merge title rows across all columns
    $lastColLetter = Coordinate::stringFromColumnIndex($colIndex - 1);
    $sheet->mergeCells("A1:{$lastColLetter}1");
    $sheet->mergeCells("A2:{$lastColLetter}2");
    $sheet->mergeCells("A3:{$lastColLetter}3");

    // Apply styles
    $sheet->getStyle("A1:A3")->applyFromArray($titleStyle);
    $sheet->getStyle("A5:{$lastColLetter}6")->applyFromArray($headerStyle);

    // ===== Fill student data =====
    $row = 6;
    foreach ($data as $index => $studentData) {
        $jsonData  = json_decode($studentData['data'], true);
        $analytics = is_string($studentData['analytics'])
            ? json_decode($studentData['analytics'], true)
            : ($studentData['analytics'] ?? []);

        $col = 1;

        // Student info
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $index + 1); // R. NO
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $studentData['admission_number']);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $studentData['student_name']);

        // Subject data
        $subjectMap = [
            'English'        => "1",
            'Mathematics'    => "2",
            'Science'        => "3",
            'Social Science' => "4",
            'Punjabi'        => "5",
            'Hindi'          => "6"
        ];

        foreach ($subjectMap as $subject => $key) {
            $obtained = $jsonData[$key] ?? '';

            // Max marks
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, 20);

            // Marks obtained
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $obtained);

            // Percentage with % symbol
            if ($obtained !== '') {
                $percentage = round(($obtained / 20), 4); // store as fraction
                $cell = Coordinate::stringFromColumnIndex($col++) . $row;
                $sheet->setCellValue($cell, $percentage);
                $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.00%');
            } else {
                $col++;
            }

            // Grade (match subject in analytics)
            $grade = '';
            if (!empty($analytics['subject_rankings'])) {
                foreach ($analytics['subject_rankings'] as $rank) {
                    if (strcasecmp(trim($rank['subject']), trim($subject)) === 0) {
                        $p = $rank['percentage'];
                        if ($p >= 90) $grade = 'A';
                        elseif ($p >= 75) $grade = 'B';
                        elseif ($p >= 60) $grade = 'C';
                        else $grade = 'D';
                        break;
                    }
                }
            }
            $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $grade);
        }

        // Overall
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData["7"] ?? ''); // Total Marks

        // Overall percentage with % symbol
        if (!empty($analytics['percentage'])) {
            $cell = Coordinate::stringFromColumnIndex($col++) . $row;
            $sheet->setCellValue($cell, $analytics['percentage'] / 100);
            $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.00%');
        } else {
            $col++;
        }

        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $analytics['grade'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData["8"] ?? ''); // Remarks

        // Apply row style
        $sheet->getStyle("A{$row}:{$lastColLetter}{$row}")->applyFromArray($cellStyle);
        $row++;
    }
}
?>
