<?php

require_once __DIR__ . '/vendor/autoload.php';
require 'config/db.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;

// === Input ===
$sort = $_POST['sort_method'] ?? 'by_name';
$start = $_POST['start_date'] ?? null;
$end = $_POST['end_date'] ?? null;
$output = $_POST['output_format'] ?? 'excel'; // default to Excel

if (!$start || !$end) {
    die("Date range is required.");
}

// === Build Query ===
$query = "SELECT * FROM Students WHERE date_added BETWEEN ? AND ?";
switch ($sort) {
    case 'by_section':
        $query .= " ORDER BY section_code ASC, last_name ASC, first_name ASC";
        break;
    case 'by_control':
        $query .= " ORDER BY COALESCE(de964_number, ade1317_number) ASC";
        break;
    default:
        $query .= " ORDER BY last_name ASC, first_name ASC";
}

$stmt = $pdo->prepare($query);
$stmt->execute([$start . " 00:00:00", $end . " 23:59:59"]);
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);

// === Create Spreadsheet ===
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$drawing = new Drawing();
$drawing->setName('Driving School Logo');
$drawing->setDescription('Driving School Logo');
$drawing->setPath(__DIR__ . '/assets/TnDLogo.png'); // adjust if your logo is elsewhere
$drawing->setHeight(80); // adjust as needed
$drawing->setCoordinates('A1');
$drawing->setWorksheet($sheet);

$sheet->setTitle('Student Records');

// === Headers and Rows ===
$headers = ['Last Name', 'First Name', 'Email', 'Section', 'DE-964 #', 'ADE-1317 #', 'Duplicate DE-964', 'Duplicate 1317', 'Date Added'];
$sheet->fromArray($headers, NULL, 'A1');

$rowNum = 2;
foreach ($students as $student) {
    $sheet->fromArray([
        $student['last_name'] ?? '',
        $student['first_name'] ?? '',
        $student['email'] ?? '',
        $student['section_code'] ?? '',
        $student['de964_number'] ?? '',
        $student['ade1317_number'] ?? '',
        $student['duplicate_de964'] ?? '',
        $student['duplicate_1317'] ?? '',
        $student['date_added'] ?? ''
    ], NULL, "A{$rowNum}");
    $rowNum++;
}

// === Formatting ===
foreach (range('A', 'I') as $col) {
    $sheet->getColumnDimension($col)->setAutoSize(true);
}
$sheet->getStyle('A1:I1')->applyFromArray([
    'font' => ['bold' => true],
    'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F2F2F2']],
    'borders' => ['bottom' => ['borderStyle' => Border::BORDER_THIN]],
]);
$dataRowCount = $rowNum - 1;
$sheet->getStyle("A1:I{$dataRowCount}")->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
$sheet->getStyle("E2:G{$dataRowCount}")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

// === Save to File (PDF or Excel) ===
$timestamp = date('Ymd_His');
if ($output === 'pdf') {
    $filename = "StudentRecords_{$timestamp}.pdf";
    $filepath = __DIR__ . "/exports/{$filename}";
    \PhpOffice\PhpSpreadsheet\IOFactory::registerWriter('Pdf', Mpdf::class);
    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Pdf');

// Add custom headers/footers
$writer->setPreCalculateFormulas(false);
$writer->setSheetIndex(0); // only if multiple sheets are used

// $writer->setTemporaryFolder(sys_get_temp_dir()); // helps with server performance
// $writer->setPdfRendererName('mpdf'); 

$mpdf = new \Mpdf\Mpdf();
$mpdf->SetHTMLHeader('
  <div style="text-align: left; font-weight: bold; font-size: 14px;">
      <img src="assets/TnDLogo.png" height="40" style="vertical-align: middle;"> TandD Driving Academy<br>
      <span style="font-size: 12px;">Student Records Report: ' . htmlspecialchars($start) . ' to ' . htmlspecialchars($end) . '</span>
  </div>
  <hr>
');

$mpdf->SetHTMLFooter('
  <hr>
  <div style="text-align: center; font-size: 10px;">
      Page {PAGENO} | tandddrivingacademy.com
  </div>
');

    $writer->save($filepath);
} else {
    $filename = "StudentRecords_{$timestamp}.xlsx";
    $filepath = __DIR__ . "/exports/{$filename}";
    $writer = new Xlsx($spreadsheet);
    $writer->save($filepath);
}

// === Output Link ===
echo "Download ready: <a href='exports/{$filename}' target='_blank'>{$filename}</a>";
?>


