<?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;

/**
 * Generates an Excel marksheet report in template 28 format.
 *
 * @param array $data The student data and analytics.
 */
function marksheet_gazette_report_template28($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
    generateMarksheet28($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="Template28_Marksheet.xlsx"');
    header('Cache-Control: max-age=0');

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

/**
 * Fills the Excel sheet with marksheet data for Template 28.
 *
 * @param \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet The active Excel worksheet.
 * @param array $data The student data and analytics.
 */
function generateMarksheet28($sheet, $data)
{
    // ===== Document properties =====
    $sheet->getParent()->getProperties()
        ->setCreator("Rejoice Learning")
        ->setTitle("Marksheet Export - Template 28")
        ->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]]
    ];

    // Assuming a single student for this example as per the provided data
    $studentData = $data[0] ?? null;
    if (!$studentData) {
        return; // Exit if no data is found
    }

    $jsonData = json_decode($studentData['data'], true);
    $analytics = is_string($studentData['analytics'])
        ? json_decode($studentData['analytics'], true)
        : ($studentData['analytics'] ?? []);

    // Get the dynamic subjects from the analytics data
    $subjects = $analytics['subject_rankings'] ?? [];
    
    // ===== Titles =====
    
    $institutionName = $data[0]['institution_info']['institution_name'] ?? 'GARDEN VALLEY INTERNATIONAL SCHOOL';
    $institutionBranch = $data[0]['branch_info']['name'] ?? 'BELA';
    $academicSession = $data[0]['academic_session'] ?? '2025-26';
    $className = $data[0]['class_name'] ?? ($jsonData['1'] ?? 'Class');
    $sectionName = $data[0]['section_name'] ?? '-';
    $reportTitle = 'RESULT OF UNIT TEST EXAMINATION(' . $academicSession . ')';

    $sheet->setCellValue('A1', $institutionName . ' , ' . $institutionBranch);
    $sheet->setCellValue('A2', $reportTitle);
    $sheet->setCellValue('A3', 'CLASS: ' . ucwords($className) . ' - SECTION: ' . $sectionName);
    // ===== Headers =====
    $subHeaders = ['MAX MARKS', 'MARKS OBTAINED', 'Percentage', 'Grade'];
    $overallHeaders = ['Overall Marks', 'Percentage', 'Grade', 'Attendance', 'Remarks'];

    // 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');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', 'STREAM'); 
    
    // Dynamic 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['name']);
    }
    
    // Overall headers
    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 to headers
    $sheet->getStyle("A1:A3")->applyFromArray($titleStyle);
    $sheet->getStyle("A5:{$lastColLetter}6")->applyFromArray($headerStyle);
    
    // ===== Fill student data =====
    $row = 7;
    $col = 1;

    // Student info
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, '1');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $studentData['admission_number'] ?? 'ADM. NO');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $studentData['student_name'] ?? 'Student Name');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ucwords($jsonData['1'] ?? ''));
    
    // Subject data - DYNAMIC (using analytics data)
    foreach ($subjects as $subject) {
        $maxMarks = $subject['total'] ?? 20;
        $obtained = $subject['obtained'] ?? '';
        $percentage = $subject['percentage'] ?? '';
        $grade = $subject['grade'] ?? '';

        // Max marks
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $maxMarks);
        
        // Marks obtained
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $obtained);
        
        // Percentage with % symbol
        if ($percentage !== '') {
            $cell = Coordinate::stringFromColumnIndex($col++) . $row;
            $sheet->setCellValue($cell, $percentage / 100);
            $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0%');
        } else {
            $col++;
        }
        
        // Grade from analytics
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $grade);
    }
    
    // Overall
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $analytics['total_obtained'] ?? '');
    
    // 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%');
    } else {
        $col++;
    }
    
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $analytics['grade'] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData["34"] ?? ''); // Attendance: key 34
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData["35"] ?? ''); // Remarks: key 35
    
    // Apply row style
    $sheet->getStyle("A{$row}:{$lastColLetter}{$row}")->applyFromArray($cellStyle);
}
?>