<?php

declare(strict_types=1);

date_default_timezone_set('Asia/Colombo');

require_once __DIR__ . '/../includes/auth.php';
require_once __DIR__ . '/../config/db.php';
require_once __DIR__ . '/../includes/role_filter.php';
require_once __DIR__ . '/../includes/report_helper.php';
require_once __DIR__ . '/../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

if (!isset($_SESSION['role']) || !in_array($_SESSION['role'], ['admin', 'zonal_director'], true)) {
    http_response_code(403);
    exit('Access denied.');
}

$dataset = buildReportDataset($conn, $_GET);
$fields = $dataset['fields'];
$equipment = $dataset['equipment'];
$surveys = $dataset['surveys'];
$equipmentTotals = $dataset['equipment_totals'];

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle('Survey Report');

$rowNumber = 1;
$columnNumber = 1;

foreach ($fields as $field) {
    $sheet->setCellValue([$columnNumber++, $rowNumber], ucwords(str_replace('_', ' ', $field)));
}

foreach ($equipment as $equipmentName) {
    $sheet->setCellValue([$columnNumber++, $rowNumber], $equipmentName . ' Working');
    $sheet->setCellValue([$columnNumber++, $rowNumber], $equipmentName . ' Not Working');
    $sheet->setCellValue([$columnNumber++, $rowNumber], $equipmentName . ' Repair');
}

$highestHeaderColumn = $columnNumber - 1;
$sheet->getStyle([1, 1, $highestHeaderColumn, 1])->getFont()->setBold(true);

$rowNumber = 2;

foreach ($surveys as $survey) {
    $columnNumber = 1;

    foreach ($fields as $field) {
        $sheet->setCellValue([$columnNumber++, $rowNumber], $survey[$field] ?? '');
    }

    $surveyId = (int)$survey['id'];

    foreach ($equipment as $equipmentId => $equipmentName) {
        $totals = $equipmentTotals[$surveyId][$equipmentId] ?? [
            'working' => 0,
            'not_working' => 0,
            'repair' => 0
        ];

        $sheet->setCellValue([$columnNumber++, $rowNumber], $totals['working']);
        $sheet->setCellValue([$columnNumber++, $rowNumber], $totals['not_working']);
        $sheet->setCellValue([$columnNumber++, $rowNumber], $totals['repair']);
    }

    $rowNumber++;
}

for ($column = 1; $column <= $highestHeaderColumn; $column++) {
    $sheet->getColumnDimensionByColumn($column)->setAutoSize(true);
}

$sheet->freezePane('A2');

$fileName = 'survey_report_' . date('Ymd_His') . '.xlsx';

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

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