<?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_template23($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']);
    });
    
    // Start output buffering
    ob_start();
    $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);

    // Create second sheet (Co-Scholastic)
    $sheet2 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'CO-SCHOLASTIC');
    $spreadsheet->addSheet($sheet2, 1);
    $spreadsheet->setActiveSheetIndex(1);

    // Generate content for second sheet
    generate_CoScholastic_Sheet23($spreadsheet->getActiveSheet(), $data);

    // Create third sheet (Analysis)
    $sheet3 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'PERSONALITY AND SOCIAL TRAITS');
    $spreadsheet->addSheet($sheet3, 2);
    $spreadsheet->setActiveSheetIndex(2);

    // Generate content for third sheet
    generate_personality_social_traits23($spreadsheet->getActiveSheet(), $data);

    // Return to first sheet
    $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)
{
    // ===== Document properties =====
    $sheet->getParent()->getProperties()
        ->setCreator("Rejoice Learning")
        ->setTitle("Marksheet Export - Template 23")
        ->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]]
    ];

    $signatureStyle = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical'   => Alignment::VERTICAL_BOTTOM
        ],
        // 'borders' => ['bottom' => ['borderStyle' => Border::BORDER_MEDIUM]]
    ];

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

    // ===== Subjects and headers =====
    $subjects   = ['ENGLISH', 'PUNJABI', 'HINDI', 'MATHEMATICS', 'E.V.S', 'GENERAL KNOWLEDGE', 'COMPUTER EDUCATION'];
    $subHeaders = ['Periodic Test (5)', 'Portfolio (5)', 'Multiple Assessment (5)', 'SEA (5)', 'Term Exam (80)', 'Total (100)', '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');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', 'SECTION');


    // 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 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 ($data as $index => $studentData) {
        $jsonData = json_decode($studentData['data'], true);
        $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']);

        // Subjects data
        $subjectMapping = [
            'ENGLISH' => range(1, 7),
            'PUNJABI' => range(8, 14),
            'HINDI'   => range(15, 21),
            'MATHEMATICS' => range(22, 28),
            'E.V.S'  => range(29, 35),
            'GENERAL KNOWLEDGE' => range(36, 42),
            'COMPUTER EDUCATION' => range(43, 49)
        ];

        foreach ($subjects as $subject) {
            $keys = $subjectMapping[$subject];
            foreach ($keys as $k) {
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData[$k] ?? '');
            }
        }

        // Overall data
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['51'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['52'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['53'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData[''] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['50'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['70'] ?? '');

        $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 ($subjects as $subject) {
        $subjectCols[] = $currentCol;
        $currentCol += 7; // Each subject has 7 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);
    }
}

function generate_CoScholastic_Sheet23($sheet, $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]]
    ];

    $signatureStyle = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical'   => Alignment::VERTICAL_BOTTOM
        ],
        // 'borders' => ['bottom' => ['borderStyle' => Border::BORDER_MEDIUM]]
    ];

    // ===== Titles =====
    $sheet->setCellValue('A1', $data[0]['institution_info']['institution_name'] . ' , ' . $data[0]['branch_info']['name']);
    $sheet->setCellValue('A2', 'CO-SCHOLASTIC FINAL (TERM -1 ) SESSION :' . $data[0]['academic_session']);
    $sheet->setCellValue('A3', 'CLASS: ' . $data[0]['class_name']);

    // ===== Headers =====
    $headers = [
        'ROLL. NO',
        'ADM. NO',
        'NAME',
        'General Awareness',
        'Computer Science',
        'Sports Discipline',
        'Art & Craft',
        'Club Activities',
        'Performing Art - Dance & Music',
        'Work Education',
        'Value Education'
    ];

    // Set headers
    $colIndex = 1;
    foreach ($headers as $header) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', $header);
    }

    // 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}5")->applyFromArray($headerStyle);

    $row = 6;
    foreach ($data as $index => $studentData) {
        $jsonData = json_decode($studentData['data'], true);
        $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, $jsonData['54'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['56'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['58'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['60'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['62'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['64'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['66'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['68'] ?? '');

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

    $signatureRow = $row + 2;

    $sheet->setCellValue('B' . $signatureRow, 'CLASS INCHARGE:');
    $sheet->setCellValue('E' . $signatureRow, 'WING INCHARGE:');
    $sheet->setCellValue('H' . $signatureRow, 'PRINCIPAL:');

    // Apply signature styles
    $signatureCells = ['B' . $signatureRow, 'E' . $signatureRow, 'H' . $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(15);
    }
}

function generate_personality_social_traits23($sheet, $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]]
    ];

    $signatureStyle = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_LEFT,
            'vertical'   => Alignment::VERTICAL_BOTTOM
        ],
        // 'borders' => ['bottom' => ['borderStyle' => Border::BORDER_MEDIUM]]
    ];

    // ===== Titles =====
    $sheet->setCellValue('A1', $data[0]['institution_info']['institution_name'] . ' , ' . $data[0]['branch_info']['name']);
    $sheet->setCellValue('A2', ' PERSONALITY AND SOCIAL TRAITS (TERM -1 ) SESSION :' . $data[0]['academic_session']);
    $sheet->setCellValue('A3', 'CLASS: ' . $data[0]['class_name']);

    // ===== Headers =====
    $headers = [
        'ROLL. NO',
        'ADM. NO',
        'NAME',
        'Team Spirit',
        'Neatness',
        'Courtesy',
        'Confidence',
        'Punctuality',
        'Social & Emotional Development',
        'Conversation & Communication'
    ];

    // Set headers
    $colIndex = 1;
    foreach ($headers as $header) {
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($colIndex++) . '5', $header);
    }

    // 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}5")->applyFromArray($headerStyle);

    $row = 6;
    foreach ($data as $index => $studentData) {
        $jsonData = json_decode($studentData['data'], true);
        $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, $jsonData['55'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['57'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['59'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['61'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['63'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['65'] ?? '');
        $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $jsonData['67'] ?? '');

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

    $signatureRow = $row + 3;

    $sheet->setCellValue('B' . $signatureRow, 'CLASS INCHARGE:');
    $sheet->setCellValue('E' . $signatureRow, 'WING INCHARGE:');
    $sheet->setCellValue('H' . $signatureRow, 'PRINCIPAL:');

    // Apply signature styles
    $signatureCells = ['B' . $signatureRow, 'E' . $signatureRow, 'H' . $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(15);
    }
}
