<?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_template33($data)
{
    usort($data, function($a, $b) {
        // First sort by section
        $sectionCompare = strcmp($a['section_name'], $b['section_name']);
        if ($sectionCompare !== 0) {
            return $sectionCompare;
        }
        
        // Then by student_name
        $nameCompare = strcmp($a['student_name'], $b['student_name']);
        if ($nameCompare !== 0) {
            return $nameCompare;
        }
        
        // Finally by admission_number
        return strcmp($a['admission_number'], $b['admission_number']);
    });
    
    $spreadsheet = new Spreadsheet();

    // Remove the 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
    generateMarksheet($spreadsheet->getActiveSheet(), $data);

    $spreadsheet->setActiveSheetIndex(0);

    $writer = new Xlsx($spreadsheet);

    // Set appropriate headers for download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="gazette_report.xlsx"');
    header('Cache-Control: max-age=0');
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Pragma: public');

    // Clear any previous output
    ob_end_clean();

    // Output the Excel file directly
    $writer->save('php://output');
    
    // Ensure no further output
    exit;
}

function generateMarksheet($sheet, $data)
{
    $sheet->getParent()->getProperties()
        ->setCreator("Rejoice Learning")
        ->setTitle("Marksheet Export - 11th & 12th")
        ->setSubject("Student Marksheet Data");

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

    $signatureStyle = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical'   => Alignment::VERTICAL_BOTTOM
        ],
    ];

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

    // ===== Collect all possible subjects from all students =====
    $allSubjects = [];
    $studentSubjectData = [];

    foreach ($data as $index => $studentData) {
        $jsonData = json_decode($studentData['data'], true);
        $studentSubjects = [];
        
        // Extract subjects from the JSON data structure
        for ($i = 2; $i <= 30; $i += 4) {
            if (isset($jsonData[$i]) && !empty($jsonData[$i]) && $jsonData[$i] !== '') {
                $subject = $jsonData[$i];
                if (!in_array($subject, $allSubjects)) {
                    $allSubjects[] = $subject;
                }
                $studentSubjects[] = $subject;
            }
        }
        
        // Store student data with subject information
        $studentSubjectData[$index] = [
            'student_info' => $studentData,
            'json_data' => $jsonData,
            'subjects' => $studentSubjects
        ];
    }

    $subHeaders = ['Theory Total Marks', 'Marks Obtained'];

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

    // Subjects with subheaders - use all collected subjects
    foreach ($allSubjects 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 after subjects
    $overallHeaders = ['Overall Marks', 'Percentage', 'Grade', 'Rank', 'Attendance', '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 = 7;
    foreach ($studentSubjectData as $index => $studentInfo) {
        $jsonData = $studentInfo['json_data'];
        $studentSubjects = $studentInfo['subjects'];
        $studentData = $studentInfo['student_info'];
        
        $col = 1;

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

        // For each subject in the master list, find and insert the corresponding data
        foreach ($allSubjects as $subject) {
            $subjectFound = false;
            
            // Look for this subject in the student's data
            for ($i = 2; $i <= 30; $i += 4) {
                if (isset($jsonData[$i]) && $jsonData[$i] === $subject) {
                    // Found the subject, now get the corresponding data columns
                    $theoryTotal = $jsonData[$i + 2] ?? '';
                    $theoryObtained = $jsonData[$i + 3] ?? '';
                    
                    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $theoryTotal);
                    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $theoryObtained);
                    
                    $subjectFound = true;
                    break;
                }
            }
            
            // If subject not found for this student, insert empty cells
            if (!$subjectFound) {
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, '');
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, '');
            }
        }
        
        $analyticsData = json_decode($studentData['analytics'], true);
        $percentage = isset($analyticsData['percentage']) ? round($analyticsData['percentage'], 2) : '';

        // Overall data
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $analyticsData['total_obtained'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $percentage);
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $analyticsData['grade'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData[''] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['34'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['35'] ?? '');

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

    $signatureRow = $row + 2;

    // Get the subject columns
    $subjectCols = [];
    $currentCol = 4; // Start after student info columns (A, B, C)
    foreach ($allSubjects as $subject) {
        $subjectCols[] = $currentCol;
        $currentCol += 2; // Each subject has 4 sub-columns
    }

    $sheet->setCellValue('B' . $signatureRow, 'Sign of C.I:');
    $sheet->setCellValue('B' . ($signatureRow + 1), 'Sign of W.I:');
    $sheet->setCellValue('B' . ($signatureRow + 2), 'Sign of PRINCIPAL:');

    // Add "Sign of S.T." below each subject
    foreach ($subjectCols as $colIndex) {
        $colLetter = Coordinate::stringFromColumnIndex($colIndex);
        $sheet->setCellValue($colLetter . $signatureRow, 'Sign of S.T:');
    }

    $signatureRange = 'A' . $signatureRow . ':' . $lastColLetter . ($signatureRow + 1);
    $sheet->getStyle($signatureRange)->applyFromArray([
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical'   => Alignment::VERTICAL_BOTTOM
        ]
    ]);

    $signatureCells = [
        'B' . $signatureRow, 
        'B' . ($signatureRow + 1), 
        'B' . ($signatureRow + 2) 
    ];

    // Add underlines for S.T. under each subject
    foreach ($subjectCols as $colIndex) {
        $colLetter = Coordinate::stringFromColumnIndex($colIndex);
        $signatureCells[] = $colLetter . $signatureRow;
    }

    foreach ($signatureCells as $cell) {
        $sheet->getStyle($cell)->applyFromArray($signatureStyle);
    }

    // ===== Column widths =====
    $sheet->getColumnDimension('A')->setWidth(8);
    $sheet->getColumnDimension('B')->setWidth(10);
    $sheet->getColumnDimension('C')->setWidth(25);
    for ($i = 4; $i <= Coordinate::columnIndexFromString($lastColLetter); $i++) {
        $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i))->setWidth(12);
    }
}

