<?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_template29(array $data)
{
  ob_start();

  $spreadsheet = new Spreadsheet();
  $spreadsheet->removeSheetByIndex(0);

  $sheet1 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'TERM-I');
  $spreadsheet->addSheet($sheet1, 0);
  generate_template29_term1_gazette($sheet1, $data);

  $sheet2 = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'TERM-II');
  $spreadsheet->addSheet($sheet2, 1);
  generate_template29_term2_gazette($sheet2, $data);

  $spreadsheet->setActiveSheetIndex(0);

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

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

/* =====================================================
   COMMON STYLES
===================================================== */
function t29_border()
{
  return ['allBorders' => ['borderStyle' => Border::BORDER_THIN]];
}

function t29_header_style()
{
  return [
    'font' => ['bold' => true],
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
      'vertical'   => Alignment::VERTICAL_CENTER
    ],
    'borders' => t29_border(),
    'fill' => [
      'fillType' => Fill::FILL_SOLID,
      'startColor' => ['argb' => 'FFEFEFEF']
    ]
  ];
}

function t29_cell_style()
{
  return [
    'alignment' => [
      'horizontal' => Alignment::HORIZONTAL_CENTER,
      'vertical'   => Alignment::VERTICAL_CENTER
    ],
    'borders' => t29_border()
  ];
}

function generate_template29_term1_gazette($sheet, $data)
{
  if (empty($data)) {
    return;
  }

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

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

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

  //   columns width
  $sheet->getColumnDimension('A')->setWidth(6);   // R.No
  $sheet->getColumnDimension('B')->setWidth(12);  // Adm No
  $sheet->getColumnDimension('C')->setWidth(22);  // Name

  //   for ($i = 4; $i <= 200; $i++) {
  //     $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i))->setWidth(13);
  //   }
  for ($i = 4; $i <= 80; $i++) {
    $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($i))->setWidth(13);
  }


  //   top titles
  $sheet->setCellValue(
    'A1',
    $data[0]['institution_info']['institution_name'] . ' , ' .
      $data[0]['branch_info']['name']
  );

  $sheet->setCellValue(
    'A2',
    $data[0]['examination_name'] . ' (' . $data[0]['academic_session'] . ')'
  );

  $sheet->setCellValue(
    'A3',
    'CLASS : ' . $data[0]['class_name'] . ' - SECTION : ' . $data[0]['section_name']
  );

  $subjects = [
    'ENGLISH',
    'HINDI',
    'PUNJABI',
    'MATHS',
    'EVS',
    'GRAND TOTAL',
    'DRAWING',
    // 'COMPUTER',
    'G.K'
  ];

  $subHeaders = [
    'PT-1 (40)',
    'Ass (5)',
    'N.B (5)',
    'Marks /50',
    'PT-2 (40)',
    'Ass (5)',
    'N.B (5)',
    'Marks /50'
  ];

  //   header build
  $rowMain = 5;
  $rowSub  = 6;

  // Fixed columns
  $sheet->setCellValue('A5', 'R. NO');
  $sheet->setCellValue('B5', 'ADM. NO');
  $sheet->setCellValue('C5', 'NAME OF THE STUDENT');

  $sheet->mergeCells('A5:A6');
  $sheet->mergeCells('B5:B6');
  $sheet->mergeCells('C5:C6');

  $col = 4;

  // Subjects
  foreach ($subjects as $subject) {
    $startCol = $col;

    foreach ($subHeaders as $sh) {
      $sheet->setCellValue(
        Coordinate::stringFromColumnIndex($col) . $rowSub,
        $sh
      );
      $col++;
    }

    $endCol = $col - 1;
    $sheet->mergeCells(
      Coordinate::stringFromColumnIndex($startCol) . $rowMain . ':' .
        Coordinate::stringFromColumnIndex($endCol) . $rowMain
    );

    $sheet->setCellValue(
      Coordinate::stringFromColumnIndex($startCol) . $rowMain,
      $subject
    );
  }

  $sheet->mergeCells(
    Coordinate::stringFromColumnIndex($col) . $rowMain . ':' .
      Coordinate::stringFromColumnIndex($col) . $rowSub
  );
  $sheet->setCellValue(
    Coordinate::stringFromColumnIndex($col) . $rowMain,
    'PERCENTAGE'
  );
  $col++;

  $statusStart = $col;
  foreach (['HEIGHT', 'WEIGHT', 'BLOOD GROUP'] as $s) {
    $sheet->setCellValue(
      Coordinate::stringFromColumnIndex($col) . $rowSub,
      $s
    );
    $col++;
  }

  $sheet->mergeCells(
    Coordinate::stringFromColumnIndex($statusStart) . $rowMain . ':' .
      Coordinate::stringFromColumnIndex($col - 1) . $rowMain
  );
  $sheet->setCellValue(
    Coordinate::stringFromColumnIndex($statusStart) . $rowMain,
    'HEALTH STATUS'
  );

  $attStart = $col;
  foreach (['TERM-I', 'TERM-II', 'Total'] as $a) {
    $sheet->setCellValue(
      Coordinate::stringFromColumnIndex($col) . $rowSub,
      $a
    );
    $col++;
  }

  $sheet->mergeCells(
    Coordinate::stringFromColumnIndex($attStart) . $rowMain . ':' .
      Coordinate::stringFromColumnIndex($col - 1) . $rowMain
  );
  $sheet->setCellValue(
    Coordinate::stringFromColumnIndex($attStart) . $rowMain,
    'ATTENDANCE'
  );

  $sheet->mergeCells(
    Coordinate::stringFromColumnIndex($col) . $rowMain . ':' .
      Coordinate::stringFromColumnIndex($col) . $rowSub
  );
  $sheet->setCellValue(
    Coordinate::stringFromColumnIndex($col) . $rowMain,
    'COMPETITION'
  );
  $col++;

  $sheet->mergeCells(
    Coordinate::stringFromColumnIndex($col) . $rowMain . ':' .
      Coordinate::stringFromColumnIndex($col) . $rowSub
  );
  $sheet->setCellValue(
    Coordinate::stringFromColumnIndex($col) . $rowMain,
    "CLASS TEACHER'S REMARKS"
  );
  $col++;

  $sheet->getRowDimension(5)->setRowHeight(35);
  $sheet->getRowDimension(6)->setRowHeight(45);

  $lastCol = Coordinate::stringFromColumnIndex($col - 1);

  //   MERGE & STYLE TITLES
  $sheet->mergeCells("A1:{$lastCol}1");
  $sheet->mergeCells("A2:{$lastCol}2");
  $sheet->mergeCells("A3:{$lastCol}3");

  $sheet->getStyle("A1:A3")->applyFromArray($titleStyle);
  $sheet->getStyle("A5:{$lastCol}6")->applyFromArray($headerStyle);

  $row = 7;
  foreach ($data as $i => $student) {

    $marks = json_decode($student['data'], true) ?? [];
    $col = 1;

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

    foreach ($subjects as $sIndex => $s) {
      $base = 109 + ($sIndex * 8);
      for ($k = 0; $k < 8; $k++) {
        $sheet->setCellValue(
          Coordinate::stringFromColumnIndex($col++) . $row,
          $marks[$base + $k] ?? ''
        );
      }
    }

    // $cell = Coordinate::stringFromColumnIndex($col++) . $row;
    // $sheet->setCellValue($cell, ($marks[173] ?? 0) / 100);
    // $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.00%');

    $rawPercentage = $marks[173] ?? 0;

    // force numeric safety
    $percentage = is_numeric($rawPercentage) ? (float)$rawPercentage : 0;

    $cell = Coordinate::stringFromColumnIndex($col++) . $row;
    $sheet->setCellValue($cell, $percentage / 100);
    $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.00%');

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[174] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[175] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[176] ?? '');

    // $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[195] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ($marks[180] ?? '') . '/' . ($marks[177] ?? ''));
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ($marks[181] ?? '') . '/' . ($marks[178] ?? ''));
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ($marks[182] ?? '') . '/' . ($marks[179] ?? ''));

    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, ($marks[186] ?? '') . ', ' . ($marks[187] ?? ''));
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[188] ?? '');

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

function generate_template29_term2_gazette($sheet, $data)
{
  if (empty($data)) {
    return;
  }

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

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

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

  $sheet->getColumnDimension('A')->setWidth(6);   // R.No
  $sheet->getColumnDimension('B')->setWidth(12);  // Adm No
  $sheet->getColumnDimension('C')->setWidth(22);

  for ($i = 4; $i <= 120; $i++) {
    $sheet->getColumnDimension(
      Coordinate::stringFromColumnIndex($i)
    )->setWidth(13);
  }

  $sheet->getRowDimension(5)->setRowHeight(35);
  $sheet->getRowDimension(6)->setRowHeight(45);

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

  /* ================= CONFIG ================= */
  $subjects = [
    'ENGLISH',
    'HINDI',
    'PUNJABI',
    'MATHS',
    'EVS',
    'DRAWING',
    'G.K. / APT'
  ];

  $subHeaders = [
    'PT-3 (40)',
    'Ass (5)',
    'N.B (5)',
    'M/50',
    'PT-4 (40)',
    'Ass (5)',
    'N.B (5)',
    'M/50',
    'M/200',
    'Grade'
  ];

  $rowMain = 5;
  $rowSub  = 6;

  /* ================= FIXED COLUMNS ================= */
  $sheet->setCellValue('A5', 'R. NO');
  $sheet->setCellValue('B5', 'ADM. NO');
  $sheet->setCellValue('C5', 'NAME OF THE STUDENT');
  $sheet->mergeCells('A5:A6');
  $sheet->mergeCells('B5:B6');
  $sheet->mergeCells('C5:C6');

  $col = 4;

  /* ================= SUBJECT HEADERS ================= */
  foreach ($subjects as $subject) {
    $start = $col;

    foreach ($subHeaders as $h) {
      $sheet->setCellValue(
        Coordinate::stringFromColumnIndex($col++) . $rowSub,
        $h
      );
    }

    $sheet->mergeCells(
      Coordinate::stringFromColumnIndex($start) . $rowMain . ':' .
        Coordinate::stringFromColumnIndex($col - 1) . $rowMain
    );
    $sheet->setCellValue(
      Coordinate::stringFromColumnIndex($start) . $rowMain,
      $subject
    );
  }

  $sheet->mergeCells(
    Coordinate::stringFromColumnIndex($col) . $rowMain . ':' .
      Coordinate::stringFromColumnIndex($col) . $rowSub
  );
  $sheet->setCellValue(
    Coordinate::stringFromColumnIndex($col) . $rowMain,
    'PERCENTAGE'
  );
  $col++; // IMPORTANT

  $psStart = $col;
  foreach (['UNIFORM', 'CIRCULAR FOLLOW UP', 'HOMEWORK/ACT.SUBMISSION', 'PTM/ATTENDANCE'] as $ps) {
    $sheet->mergeCells(
      Coordinate::stringFromColumnIndex($col) . $rowSub . ':' .
        Coordinate::stringFromColumnIndex($col + 1) . $rowSub
    );
    $sheet->setCellValue(
      Coordinate::stringFromColumnIndex($col) . $rowSub,
      $ps
    );
    $sheet->getRowDimension(5)->setRowHeight(35);
    $sheet->getRowDimension(6)->setRowHeight(45);
    $col += 2;
  }
  $sheet->mergeCells(
    Coordinate::stringFromColumnIndex($psStart) . $rowMain . ':' .
      Coordinate::stringFromColumnIndex($col - 1) . $rowMain
  );
  $sheet->setCellValue(
    Coordinate::stringFromColumnIndex($psStart) . $rowMain,
    'PARENTAL SUPPORT'
  );

  /* ================= TA ================= */
  $taStart = $col;
  foreach (['PERSONAL HYGIENE', 'PUNCTUALITY', 'VALUE SYSTEM', 'SELF-CONFIDENCE'] as $ta) {
    $sheet->mergeCells(
      Coordinate::stringFromColumnIndex($col) . $rowSub . ':' .
        Coordinate::stringFromColumnIndex($col + 1) . $rowSub
    );
    $sheet->setCellValue(
      Coordinate::stringFromColumnIndex($col) . $rowSub,
      $ta
    );
    $sheet->getRowDimension(5)->setRowHeight(35);
    $sheet->getRowDimension(6)->setRowHeight(45);
    $col += 2;
  }
  $sheet->mergeCells(
    Coordinate::stringFromColumnIndex($taStart) . $rowMain . ':' .
      Coordinate::stringFromColumnIndex($col - 1) . $rowMain
  );
  $sheet->setCellValue(
    Coordinate::stringFromColumnIndex($taStart) . $rowMain,
    'TEACHERS ASSESSMENT'
  );

  /* ================= FINAL COLUMNS ================= */
  foreach (['CLASS TEACHER REMARKS', 'PROMOTED TO CLASS'] as $last) {
    $sheet->mergeCells(
      Coordinate::stringFromColumnIndex($col) . $rowMain . ':' .
        Coordinate::stringFromColumnIndex($col) . $rowSub
    );
    $sheet->setCellValue(
      Coordinate::stringFromColumnIndex($col++) . $rowMain,
      $last
    );
  }

  $lastCol = Coordinate::stringFromColumnIndex($col - 1);

  /* ================= STYLING ================= */
  $sheet->mergeCells("A1:{$lastCol}1");
  $sheet->mergeCells("A2:{$lastCol}2");
  $sheet->mergeCells("A3:{$lastCol}3");
  $sheet->getStyle("A1:A3")->applyFromArray($titleStyle);
  $sheet->getStyle("A5:{$lastCol}6")->applyFromArray($headerStyle);

  /* ================= DATA ================= */
  $row = 7;
  foreach ($data as $i => $student) {
    $marks = json_decode($student['data'], true) ?? [];
    $col = 1;

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

    $idx = 1;
    for ($s = 0; $s < 5; $s++) {
      for ($k = 0; $k < 10; $k++) {
        $sheet->setCellValue(
          Coordinate::stringFromColumnIndex($col++) . $row,
          $marks[$idx++] ?? ''
        );
      }
    }

    $idx = 61;
    for ($s = 0; $s < 2; $s++) {
      for ($k = 0; $k < 10; $k++) {
        $sheet->setCellValue(
          Coordinate::stringFromColumnIndex($col++) . $row,
          $marks[$idx++] ?? ''
        );
      }
    }

    $rawPercentage = $marks[81] ?? 0;
    $percentage = is_numeric($rawPercentage) ? (float)$rawPercentage : 0;

    $cell = Coordinate::stringFromColumnIndex($col++) . $row;
    $sheet->setCellValue($cell, $percentage / 100);
    $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.00%');



    // PS + TA (16 cells)
    for ($i = 91; $i <= 106; $i++) {
      $sheet->setCellValue(
        Coordinate::stringFromColumnIndex($col++) . $row,
        $marks[$i] ?? ''
      );
    }

    // CTR + PTC
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[107] ?? '');
    $sheet->setCellValue(Coordinate::stringFromColumnIndex($col++) . $row, $marks[108] ?? '');

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