<?php

declare(strict_types=1);

namespace App\Services;

use PharData;
use RuntimeException;
use Throwable;
use ZipArchive;

/**
 * Professional XLSX export for the active inpatient daily revenue report.
 *
 * Design goals:
 * - Keep the first sheet familiar to finance staff who used the manual workbook.
 * - Keep only the requested executive summary and familiar daily revenue sheet.
 * - Avoid third-party runtime dependencies and remain compatible with PHP 7.4+.
 * - Oracle writes: NONE.
 */
final class XlsxExportService
{
    private const STYLE_BODY = 0;
    private const STYLE_HEADER = 1;
    private const STYLE_TITLE = 2;
    private const STYLE_SECTION = 3;
    private const STYLE_CURRENCY = 4;
    private const STYLE_INTEGER = 5;
    private const STYLE_DATE = 6;
    private const STYLE_TIME = 7;
    private const STYLE_SUMMARY_LABEL = 8;
    private const STYLE_SUMMARY_VALUE = 9;
    private const STYLE_GRAND_LABEL = 10;
    private const STYLE_GRAND_CURRENCY = 11;
    private const STYLE_STATUS_OK = 12;
    private const STYLE_STATUS_REVIEW = 13;
    private const STYLE_KPI_LABEL = 14;
    private const STYLE_KPI_CURRENCY = 15;
    private const STYLE_KPI_INTEGER = 16;
    private const STYLE_PERCENT = 17;
    private const STYLE_SUBHEADER = 18;
    private const STYLE_BORDER_TEXT = 19;
    private const STYLE_BORDER_CURRENCY = 20;
    private const STYLE_BORDER_INTEGER = 21;
    private const STYLE_BORDER_DATE = 22;
    private const STYLE_BORDER_TIME = 23;
    private const STYLE_BORDER_STATUS_OK = 24;
    private const STYLE_BORDER_STATUS_REVIEW = 25;
    private const STYLE_BORDER_METHOD = 26;
    private const STYLE_NOTE = 27;

    /** @param array<string,mixed> $report */
    public function streamFinalWorkbook(array $report, string $filename = 'final.xlsx'): void
    {
        $tmpBase = tempnam(sys_get_temp_dir(), 'ddb_xlsx_');
        if ($tmpBase === false) {
            throw new RuntimeException('Unable to create temporary export file.');
        }
        @unlink($tmpBase);
        $tmp = $tmpBase . '.xlsx';

        try {
            $this->createWorkbook($tmp, $report);

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment; filename="' . preg_replace('/[^A-Za-z0-9._-]/', '_', $filename) . '"');
            header('Content-Length: ' . (string)filesize($tmp));
            header('Cache-Control: private, max-age=0, must-revalidate');
            readfile($tmp);
        } finally {
            @unlink($tmp);
        }
    }

    /** @param array<string,mixed> $report */
    public function createFinalWorkbookFile(string $path, array $report): void
    {
        $this->createWorkbook($path, $report);
    }

    /** @param array<string,mixed> $report */
    private function createWorkbook(string $path, array $report): void
    {
        $summarySheet = $this->buildSummarySheet($report);
        $dailySheet = $this->buildManualFriendlyDailyRevenueSheet($report);

        $chart = $summarySheet['chart'] ?? null;
        $hasChart = is_array($chart) && (int)($chart['count'] ?? 0) > 0;

        $entries = [
            '[Content_Types].xml' => $this->contentTypes($hasChart),
            '_rels/.rels' => $this->rootRels(),
            'xl/workbook.xml' => $this->workbookXml(),
            'xl/_rels/workbook.xml.rels' => $this->workbookRels(),
            'xl/styles.xml' => $this->stylesXml(),
            'xl/worksheets/sheet1.xml' => $this->sheetXml($summarySheet),
            'xl/worksheets/sheet2.xml' => $this->sheetXml($dailySheet),
        ];

        if ($hasChart && is_array($chart)) {
            $entries['xl/worksheets/_rels/sheet1.xml.rels'] = $this->summarySheetRels();
            $entries['xl/drawings/drawing1.xml'] = $this->summaryDrawingXml();
            $entries['xl/drawings/_rels/drawing1.xml.rels'] = $this->summaryDrawingRels();
            $entries['xl/charts/chart1.xml'] = $this->payerChartXml($chart);
        }

        $this->writeZipPackage($path, $entries);
    }

    /**
     * @param array<string,mixed> $report
     * @return array<string,mixed>
     */
    private function buildSummarySheet(array $report): array
    {
        $summary = is_array($report['summary'] ?? null) ? $report['summary'] : [];
        $units = is_array($report['units'] ?? null) ? $report['units'] : [];
        $companies = is_array($report['companies'] ?? null) ? $report['companies'] : [];
        $hospitalCode = strtoupper((string)($report['hospital_code'] ?? 'NJCH'));
        $hospitalName = trim((string)($report['hospital_name'] ?? ''));
        $generatedAt = (string)($summary['generated_at'] ?? date('Y-m-d H:i:s'));

        $rows = [];
        $merges = [
            'A1:J1', 'A2:J2',
            'A4:B4', 'C4:D4', 'E4:F4', 'G4:H4', 'I4:J4',
            'A5:B5', 'C5:D5', 'E5:F5', 'G5:H5', 'I5:J5',
            'A9:E9', 'F9:J9',
        ];
        $rowHeights = [1 => 30, 2 => 22, 4 => 22, 5 => 34, 9 => 24];

        $rows[] = $this->row([
            $this->cell($hospitalCode . ' - Daily Inpatient Revenue Executive Summary', self::STYLE_TITLE),
        ], 10);
        $rows[] = $this->row([
            $this->cell('Generated at ' . $generatedAt . ($hospitalName !== '' ? ' | ' . $hospitalName : ''), self::STYLE_NOTE),
        ], 10);
        $rows[] = $this->row([], 10);

        $rows[] = $this->row([
            $this->cell('Patients in Rooms', self::STYLE_KPI_LABEL), null,
            $this->cell('Total Daily Revenue (SR)', self::STYLE_KPI_LABEL), null,
            $this->cell('Average per Patient (SR)', self::STYLE_KPI_LABEL), null,
            $this->cell('Automatic Coverage', self::STYLE_KPI_LABEL), null,
            $this->cell('Needs Review', self::STYLE_KPI_LABEL), null,
        ], 10);
        $rows[] = $this->row([
            $this->cell((int)($summary['patient_count'] ?? 0), self::STYLE_KPI_INTEGER), null,
            $this->cell((float)($summary['daily_revenue'] ?? 0), self::STYLE_KPI_CURRENCY), null,
            $this->cell((float)($summary['average_revenue'] ?? 0), self::STYLE_KPI_CURRENCY), null,
            $this->cell(((float)($summary['automatic_pct'] ?? 0)) / 100, self::STYLE_PERCENT), null,
            $this->cell((int)($summary['review_count'] ?? 0), self::STYLE_KPI_INTEGER), null,
        ], 10);
        $rows[] = $this->row([], 10);
        $rows[] = $this->row([], 10);
        $rows[] = $this->row([], 10);

        $rows[] = $this->row([
            $this->cell('Revenue by Care Area', self::STYLE_SECTION), null, null, null, null,
            $this->cell('Revenue by Financial Class', self::STYLE_SECTION), null, null, null, null,
        ], 10);
        $rows[] = $this->row([
            $this->cell('Unit', self::STYLE_HEADER),
            $this->cell('Patients', self::STYLE_HEADER),
            $this->cell('Beds', self::STYLE_HEADER),
            $this->cell('Occupancy %', self::STYLE_HEADER),
            $this->cell('Daily Revenue (SR)', self::STYLE_HEADER),
            $this->cell('Class ID', self::STYLE_HEADER),
            $this->cell('Financial Class', self::STYLE_HEADER),
            $this->cell('Patients', self::STYLE_HEADER),
            $this->cell('Daily Revenue (SR)', self::STYLE_HEADER),
            $this->cell('Share %', self::STYLE_HEADER),
        ], 10);

        $maxRows = max(count($units), count($companies), 1);
        $totalRevenue = (float)($summary['daily_revenue'] ?? 0);
        $payerStartRow = 11;
        $payerCount = 0;
        $payerNames = [];
        $payerValues = [];

        for ($i = 0; $i < $maxRows; $i++) {
            $unit = isset($units[$i]) && is_array($units[$i]) ? $units[$i] : [];
            $company = isset($companies[$i]) && is_array($companies[$i]) ? $companies[$i] : [];
            $patients = (int)($unit['patients'] ?? 0);
            $beds = (int)($unit['beds'] ?? 0);
            $companyRevenue = (float)($company['daily_revenue'] ?? 0);

            if ($company !== []) {
                $payerCount++;
                $payerNames[] = (string)($company['financial_class'] ?? '');
                $payerValues[] = $companyRevenue;
            }

            $rows[] = $this->row([
                $unit !== [] ? $this->cell((string)($unit['unit_name'] ?? ''), self::STYLE_BORDER_TEXT) : null,
                $unit !== [] ? $this->cell($patients, self::STYLE_BORDER_INTEGER) : null,
                $unit !== [] ? $this->cell($beds, self::STYLE_BORDER_INTEGER) : null,
                $unit !== [] ? $this->cell($beds > 0 ? ($patients / $beds) : 0, self::STYLE_PERCENT) : null,
                $unit !== [] ? $this->cell((float)($unit['daily_revenue'] ?? 0), self::STYLE_BORDER_CURRENCY) : null,
                $company !== [] ? $this->cell((int)($company['financlassid'] ?? 0), self::STYLE_BORDER_INTEGER) : null,
                $company !== [] ? $this->cell((string)($company['financial_class'] ?? ''), self::STYLE_BORDER_TEXT) : null,
                $company !== [] ? $this->cell((int)($company['patients'] ?? 0), self::STYLE_BORDER_INTEGER) : null,
                $company !== [] ? $this->cell($companyRevenue, self::STYLE_BORDER_CURRENCY) : null,
                $company !== [] ? $this->cell($totalRevenue > 0 ? ($companyRevenue / $totalRevenue) : 0, self::STYLE_PERCENT) : null,
            ], 10);
        }

        $afterTables = 10 + $maxRows + 2;
        $rows[] = $this->row([], 10);
        $rows[] = $this->row([
            $this->cell('Approved Calculation Rules', self::STYLE_SECTION), null, null, null, null, null, null, null, null, null,
        ], 10);
        $merges[] = 'A' . $afterTables . ':J' . $afterTables;
        $rowHeights[$afterTables] = 24;

        $ruleRows = [
            ['365', 'CENTER FOR NATIONAL HEALTH INSURANCE', 'Total LOS <= 30: 4,746.87 SR | Total LOS > 30: 2,373.00 SR'],
            ['170', 'TCS', 'LOS 1-3: 2,250 SR | LOS 4-8: 1,950 SR | LOS 9+: 1,750 SR'],
            ['1', 'CASH', 'Accumulated patient share / LOS from visit start date'],
            ['Other', 'Insurance', 'Accumulated company share / LOS from visit start date'],
        ];
        foreach ($ruleRows as $rule) {
            $rows[] = $this->row([
                $this->cell($rule[0], self::STYLE_SUBHEADER),
                $this->cell($rule[1], self::STYLE_BORDER_TEXT),
                $this->cell($rule[2], self::STYLE_BORDER_METHOD),
            ], 10);
            $rowNumber = count($rows);
            $merges[] = 'C' . $rowNumber . ':J' . $rowNumber;
        }

        $chart = $payerCount > 0 ? [
            'start_row' => $payerStartRow,
            'end_row' => $payerStartRow + $payerCount - 1,
            'count' => min($payerCount, 10),
            'categories' => array_slice($payerNames, 0, 10),
            'values' => array_slice($payerValues, 0, 10),
        ] : null;

        return [
            'rows' => $rows,
            'merges' => $merges,
            'widths' => [1 => 24, 2 => 12, 3 => 10, 4 => 14, 5 => 20, 6 => 10, 7 => 34, 8 => 12, 9 => 20, 10 => 12, 11 => 2, 12 => 14, 13 => 14, 14 => 14, 15 => 14, 16 => 14, 17 => 14, 18 => 14, 19 => 14, 20 => 14],
            'freeze_rows' => 10,
            'auto_filter' => 'A10:J' . (10 + $maxRows),
            'row_heights' => $rowHeights,
            'landscape' => true,
            'drawing' => $chart !== null,
            'chart' => $chart,
        ];
    }

    /**
     * @param array<string,mixed> $report
     * @return array<string,mixed>
     */
    private function buildManualFriendlyDailyRevenueSheet(array $report): array
    {
        $summary = is_array($report['summary'] ?? null) ? $report['summary'] : [];
        $patients = is_array($report['patients'] ?? null) ? $report['patients'] : [];
        $hospitalCode = strtoupper((string)($report['hospital_code'] ?? 'NJCH'));
        $hospitalName = trim((string)($report['hospital_name'] ?? ''));
        $generatedAt = (string)($summary['generated_at'] ?? date('Y-m-d H:i:s'));

        $groups = [];
        foreach ($patients as $patient) {
            if (!is_array($patient)) {
                continue;
            }
            $unitName = trim((string)($patient['unit_name'] ?? ''));
            if ($unitName === '') {
                $unitName = 'Unassigned';
            }
            if (!isset($groups[$unitName])) {
                $groups[$unitName] = [];
            }
            $groups[$unitName][] = $patient;
        }

        $rows = [];
        $merges = ['A1:M1', 'A2:M2'];
        $rowHeights = [1 => 30, 2 => 22];
        $rows[] = $this->row([
            $this->cell($hospitalCode . ' - Inpatient Daily Revenue', self::STYLE_TITLE),
        ], 13);
        $rows[] = $this->row([
            $this->cell('Generated at ' . $generatedAt . ($hospitalName !== '' ? ' | ' . $hospitalName : ''), self::STYLE_NOTE),
        ], 13);
        $rows[] = $this->row([], 13);

        $headers = [
            'Attending Doctor', 'Financial Class', 'LOS', 'Nationality', 'Patient Name', 'Patient ID',
            'Time', 'Date', 'Daily Revenue', 'Bed', 'Room', 'Initial Admission', 'Total LOS',
        ];

        $grandRevenue = 0.0;
        foreach ($groups as $unitName => $unitPatients) {
            $sectionRow = count($rows) + 1;
            $rows[] = $this->row([$this->cell($unitName, self::STYLE_SECTION)], 13);
            $merges[] = 'A' . $sectionRow . ':M' . $sectionRow;
            $rowHeights[$sectionRow] = 24;

            $headerCells = [];
            foreach ($headers as $header) {
                $headerCells[] = $this->cell($header, self::STYLE_HEADER);
            }
            $rows[] = $this->row($headerCells, 13);

            $unitRevenue = 0.0;
            $bedCounts = 0;
            foreach ($unitPatients as $patient) {
                $dailyRevenue = (float)($patient['daily_revenue'] ?? 0);
                $unitRevenue += $dailyRevenue;
                $grandRevenue += $dailyRevenue;
                $bedCounts = max($bedCounts, (int)($patient['bed_counts'] ?? 0));

                $rows[] = $this->row([
                    $this->cell((string)($patient['attending_doctor'] ?? ''), self::STYLE_BORDER_TEXT),
                    $this->cell((string)($patient['financial_class'] ?? ''), self::STYLE_BORDER_TEXT),
                    $this->cell((int)($patient['los'] ?? 0), self::STYLE_BORDER_INTEGER),
                    $this->cell((string)($patient['nationality'] ?? ''), self::STYLE_BORDER_TEXT),
                    $this->cell((string)($patient['patient_name'] ?? ''), self::STYLE_BORDER_TEXT),
                    $this->cell((string)($patient['patientid'] ?? ''), self::STYLE_BORDER_TEXT),
                    $this->timeCell($patient['admission_time'] ?? ''),
                    $this->dateCell($patient['admission_date'] ?? ''),
                    $this->cell($dailyRevenue, self::STYLE_BORDER_CURRENCY),
                    $this->cell((string)($patient['bed'] ?? ''), self::STYLE_BORDER_TEXT),
                    $this->cell((string)($patient['room'] ?? ''), self::STYLE_BORDER_TEXT),
                    $this->dateCell($patient['los_start_date'] ?? ''),
                    $this->cell((int)($patient['total_los'] ?? 0), self::STYLE_BORDER_INTEGER),
                ], 13);
            }

            $patientCount = count($unitPatients);
            $occupancy = $bedCounts > 0 ? ($patientCount / $bedCounts) : 0;
            $rows[] = $this->row([
                $this->cell('No Of Patients', self::STYLE_SUMMARY_LABEL), null,
                $this->cell($patientCount, self::STYLE_SUMMARY_VALUE),
                $this->cell('No Of Beds', self::STYLE_SUMMARY_LABEL), null,
                $this->cell($bedCounts, self::STYLE_SUMMARY_VALUE),
                $this->cell('Occupancy', self::STYLE_SUMMARY_LABEL),
                $this->cell($occupancy, self::STYLE_PERCENT),
            ], 13);

            $unitTotalRow = count($rows) + 1;
            $rows[] = $this->row([
                $this->cell('Unit Daily Revenue Total', self::STYLE_GRAND_LABEL), null, null, null, null, null, null, null,
                $this->cell(round($unitRevenue, 2), self::STYLE_GRAND_CURRENCY),
            ], 13);
            $merges[] = 'A' . $unitTotalRow . ':H' . $unitTotalRow;
            $rowHeights[$unitTotalRow] = 24;
            $rows[] = $this->row([], 13);
        }

        if ($groups === []) {
            $rows[] = $this->row([$this->cell('No active inpatients are available for this snapshot.', self::STYLE_NOTE)], 13);
            $merges[] = 'A4:M4';
        }

        $grandRow = count($rows) + 1;
        $rows[] = $this->row([
            $this->cell('Grand Total Daily Revenue', self::STYLE_GRAND_LABEL), null, null, null, null, null, null, null,
            $this->cell(round($grandRevenue, 2), self::STYLE_GRAND_CURRENCY),
        ], 13);
        $merges[] = 'A' . $grandRow . ':H' . $grandRow;
        $merges[] = 'I' . $grandRow . ':M' . $grandRow;
        $rowHeights[$grandRow] = 26;

        return [
            'rows' => $rows,
            'merges' => $merges,
            'widths' => [1 => 31, 2 => 38, 3 => 9, 4 => 17, 5 => 34, 6 => 14, 7 => 11, 8 => 13, 9 => 18, 10 => 11, 11 => 18, 12 => 16, 13 => 11],
            'freeze_rows' => 0,
            'auto_filter' => null,
            'row_heights' => $rowHeights,
            'landscape' => true,
            'drawing' => false,
        ];
    }

    /**
     * @param array<string,mixed> $sheet
     */
    private function sheetXml(array $sheet): string
    {
        $rows = is_array($sheet['rows'] ?? null) ? $sheet['rows'] : [];
        $merges = is_array($sheet['merges'] ?? null) ? $sheet['merges'] : [];
        $widths = is_array($sheet['widths'] ?? null) ? $sheet['widths'] : [];
        $freezeRows = (int)($sheet['freeze_rows'] ?? 0);
        $autoFilter = is_string($sheet['auto_filter'] ?? null) ? (string)$sheet['auto_filter'] : '';
        $rowHeights = is_array($sheet['row_heights'] ?? null) ? $sheet['row_heights'] : [];
        $drawing = !empty($sheet['drawing']);
        $landscape = !empty($sheet['landscape']);

        $xml = '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
        $xml .= '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"';
        if ($drawing) {
            $xml .= ' xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"';
        }
        $xml .= '>';
        $xml .= '<sheetViews><sheetView workbookViewId="0" showGridLines="0">';
        if ($freezeRows > 0) {
            $xml .= '<pane ySplit="' . $freezeRows . '" topLeftCell="A' . ($freezeRows + 1) . '" activePane="bottomLeft" state="frozen"/>';
        }
        $xml .= '</sheetView></sheetViews>';
        $xml .= '<sheetFormatPr defaultRowHeight="20"/>';

        if ($widths !== []) {
            $xml .= '<cols>';
            foreach ($widths as $column => $width) {
                $xml .= '<col min="' . (int)$column . '" max="' . (int)$column . '" width="' . $this->xmlNumber((float)$width) . '" customWidth="1"/>';
            }
            $xml .= '</cols>';
        }

        $xml .= '<sheetData>';
        foreach ($rows as $rowIndex => $row) {
            $excelRow = $rowIndex + 1;
            $height = isset($rowHeights[$excelRow]) ? (float)$rowHeights[$excelRow] : 0.0;
            $xml .= '<row r="' . $excelRow . '"' . ($height > 0 ? ' ht="' . $this->xmlNumber($height) . '" customHeight="1"' : '') . '>';
            if (is_array($row)) {
                foreach ($row as $columnIndex => $cell) {
                    if (!is_array($cell) || !array_key_exists('v', $cell)) {
                        continue;
                    }
                    $value = $cell['v'];
                    if ($value === null || $value === '') {
                        continue;
                    }
                    $ref = $this->columnName($columnIndex + 1) . $excelRow;
                    $style = (int)($cell['s'] ?? self::STYLE_BODY);
                    $type = (string)($cell['t'] ?? $this->inferCellType($value));
                    if ($type === 'n') {
                        $xml .= '<c r="' . $ref . '" s="' . $style . '" t="n"><v>' . $this->xmlNumber((float)$value) . '</v></c>';
                    } else {
                        $xml .= '<c r="' . $ref . '" s="' . $style . '" t="inlineStr"><is><t xml:space="preserve">' . $this->xml((string)$value) . '</t></is></c>';
                    }
                }
            }
            $xml .= '</row>';
        }
        $xml .= '</sheetData>';

        if ($autoFilter !== '') {
            $xml .= '<autoFilter ref="' . $this->xml($autoFilter) . '"/>';
        }
        if ($merges !== []) {
            $xml .= '<mergeCells count="' . count($merges) . '">';
            foreach ($merges as $merge) {
                $xml .= '<mergeCell ref="' . $this->xml((string)$merge) . '"/>';
            }
            $xml .= '</mergeCells>';
        }

        $xml .= '<pageMargins left="0.3" right="0.3" top="0.5" bottom="0.5" header="0.2" footer="0.2"/>';
        $xml .= '<pageSetup orientation="' . ($landscape ? 'landscape' : 'portrait') . '" fitToWidth="1" fitToHeight="0" paperSize="9"/>';
        if ($drawing) {
            $xml .= '<drawing r:id="rId1"/>';
        }
        $xml .= '</worksheet>';
        return $xml;
    }

    /** @param array<int,mixed> $cells @return array<int,mixed> */
    private function row(array $cells, int $width): array
    {
        return array_pad($cells, $width, null);
    }

    /** @return array{v:mixed,s:int,t:string} */
    private function cell($value, int $style = self::STYLE_BODY, ?string $type = null): array
    {
        return [
            'v' => $value,
            's' => $style,
            't' => $type ?? $this->inferCellType($value),
        ];
    }

    /** @return array{v:mixed,s:int,t:string} */
    private function dateCell($value): array
    {
        $serial = $this->excelDateSerial($value);
        return $serial === null
            ? $this->cell((string)$value, self::STYLE_BORDER_TEXT, 's')
            : $this->cell($serial, self::STYLE_BORDER_DATE, 'n');
    }

    /** @return array{v:mixed,s:int,t:string} */
    private function timeCell($value): array
    {
        $serial = $this->excelTimeSerial($value);
        return $serial === null
            ? $this->cell((string)$value, self::STYLE_BORDER_TEXT, 's')
            : $this->cell($serial, self::STYLE_BORDER_TIME, 'n');
    }

    private function inferCellType($value): string
    {
        return (is_int($value) || is_float($value)) ? 'n' : 's';
    }

    private function excelDateSerial($value): ?float
    {
        try {
            $utc = new \DateTimeZone('UTC');
            if ($value instanceof \DateTimeInterface) {
                $dateText = $value->format('Y-m-d');
            } else {
                $text = trim((string)$value);
                if ($text === '') {
                    return null;
                }
                $parsed = new \DateTimeImmutable($text, $utc);
                $dateText = $parsed->format('Y-m-d');
            }

            $date = new \DateTimeImmutable($dateText, $utc);
            $base = new \DateTimeImmutable('1899-12-30', $utc);
            $diff = $base->diff($date);
            if ($diff->days === false) {
                return null;
            }
            return (float)$diff->days;
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function excelTimeSerial($value): ?float
    {
        $text = trim((string)$value);
        if ($text === '') {
            return null;
        }
        if (preg_match('/^(\d{1,2}):(\d{2})(?::(\d{2}))?$/', $text, $m) !== 1) {
            return null;
        }
        $hours = (int)$m[1];
        $minutes = (int)$m[2];
        $seconds = isset($m[3]) ? (int)$m[3] : 0;
        if ($hours > 23 || $minutes > 59 || $seconds > 59) {
            return null;
        }
        return (($hours * 3600) + ($minutes * 60) + $seconds) / 86400;
    }

    /** @param array<string,string> $entries */
    private function writeZipPackage(string $path, array $entries): void
    {
        @unlink($path);

        if (class_exists(ZipArchive::class)) {
            $zip = new ZipArchive();
            if ($zip->open($path, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
                throw new RuntimeException('Unable to create Excel workbook.');
            }
            foreach ($entries as $name => $content) {
                $zip->addFromString($name, $content);
            }
            $zip->close();
            return;
        }

        if (class_exists(PharData::class)) {
            $zipPath = $path . '.zip';
            @unlink($zipPath);
            try {
                $archive = new PharData($zipPath);
                foreach ($entries as $name => $content) {
                    $archive[$name] = $content;
                }
                unset($archive);
                if (!@rename($zipPath, $path)) {
                    throw new RuntimeException('Unable to finalize Excel workbook file.');
                }
                return;
            } catch (Throwable $e) {
                @unlink($zipPath);
                @unlink($path);
                throw new RuntimeException('Unable to create Excel workbook using the available archive engine.', 0, $e);
            }
        }

        throw new RuntimeException('A ZIP-capable PHP archive engine is required to create the Excel workbook.');
    }

    private function contentTypes(bool $hasChart): string
    {
        $chartParts = $hasChart
            ? '<Override PartName="/xl/drawings/drawing1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>'
                . '<Override PartName="/xl/charts/chart1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawingml.chart+xml"/>'
            : '';

        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
            . '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>'
            . '<Default Extension="xml" ContentType="application/xml"/>'
            . '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>'
            . '<Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
            . '<Override PartName="/xl/worksheets/sheet2.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
            . '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>'
            . $chartParts
            . '</Types>';
    }

    private function rootRels(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
            . '</Relationships>';
    }

    private function workbookXml(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
            . '<bookViews><workbookView xWindow="0" yWindow="0" windowWidth="24000" windowHeight="14000"/></bookViews>'
            . '<sheets>'
            . '<sheet name="Summary" sheetId="1" r:id="rId1"/>'
            . '<sheet name="Daily Revenue" sheetId="2" r:id="rId2"/>'
            . '</sheets>'
            . '</workbook>';
    }

    private function workbookRels(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>'
            . '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml"/>'
            . '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
            . '</Relationships>';
    }

    private function summarySheetRels(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing" Target="../drawings/drawing1.xml"/>'
            . '</Relationships>';
    }

    private function summaryDrawingRels(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
            . '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart" Target="../charts/chart1.xml"/>'
            . '</Relationships>';
    }

    private function summaryDrawingXml(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">'
            . '<xdr:twoCellAnchor>'
            . '<xdr:from><xdr:col>11</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>2</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from>'
            . '<xdr:to><xdr:col>19</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>20</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:to>'
            . '<xdr:graphicFrame macro=""><xdr:nvGraphicFramePr><xdr:cNvPr id="2" name="Payer Revenue Chart"/><xdr:cNvGraphicFramePr/></xdr:nvGraphicFramePr>'
            . '<xdr:xfrm/><a:graphic><a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/chart">'
            . '<c:chart xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1"/>'
            . '</a:graphicData></a:graphic></xdr:graphicFrame><xdr:clientData/>'
            . '</xdr:twoCellAnchor></xdr:wsDr>';
    }

    /** @param array<string,mixed> $chart */
    private function payerChartXml(array $chart): string
    {
        $count = max(0, min(10, (int)($chart['count'] ?? 0)));
        $startRow = (int)($chart['start_row'] ?? 11);
        $endRow = $startRow + $count - 1;
        $categories = is_array($chart['categories'] ?? null) ? array_slice($chart['categories'], 0, $count) : [];
        $values = is_array($chart['values'] ?? null) ? array_slice($chart['values'], 0, $count) : [];

        $catCache = '<c:strCache><c:ptCount val="' . $count . '"/>';
        $valCache = '<c:numCache><c:formatCode>#,##0.00</c:formatCode><c:ptCount val="' . $count . '"/>';
        for ($i = 0; $i < $count; $i++) {
            $catCache .= '<c:pt idx="' . $i . '"><c:v>' . $this->xml((string)($categories[$i] ?? '')) . '</c:v></c:pt>';
            $valCache .= '<c:pt idx="' . $i . '"><c:v>' . $this->xmlNumber((float)($values[$i] ?? 0)) . '</c:v></c:pt>';
        }
        $catCache .= '</c:strCache>';
        $valCache .= '</c:numCache>';

        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<c:chartSpace xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart" xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main">'
            . '<c:chart><c:title><c:tx><c:rich><a:bodyPr/><a:lstStyle/><a:p><a:r><a:rPr lang="en-US" sz="1200" b="1"/><a:t>Top Payers by Daily Revenue</a:t></a:r></a:p></c:rich></c:tx><c:layout/><c:overlay val="0"/></c:title>'
            . '<c:plotArea><c:layout/><c:barChart><c:barDir val="bar"/><c:grouping val="clustered"/><c:varyColors val="0"/>'
            . '<c:ser><c:idx val="0"/><c:order val="0"/><c:tx><c:v>Daily Revenue (SR)</c:v></c:tx>'
            . '<c:spPr><a:solidFill><a:srgbClr val="0F766E"/></a:solidFill></c:spPr>'
            . '<c:cat><c:strRef><c:f>Summary!$G$' . $startRow . ':$G$' . $endRow . '</c:f>' . $catCache . '</c:strRef></c:cat>'
            . '<c:val><c:numRef><c:f>Summary!$I$' . $startRow . ':$I$' . $endRow . '</c:f>' . $valCache . '</c:numRef></c:val>'
            . '</c:ser><c:dLbls><c:showVal val="1"/><c:showCatName val="0"/><c:showLegendKey val="0"/></c:dLbls>'
            . '<c:axId val="48650112"/><c:axId val="48672768"/></c:barChart>'
            . '<c:catAx><c:axId val="48650112"/><c:scaling><c:orientation val="minMax"/></c:scaling><c:delete val="0"/><c:axPos val="l"/><c:tickLblPos val="nextTo"/><c:crossAx val="48672768"/><c:crosses val="autoZero"/><c:auto val="1"/><c:lblAlgn val="ctr"/><c:lblOffset val="100"/></c:catAx>'
            . '<c:valAx><c:axId val="48672768"/><c:scaling><c:orientation val="minMax"/></c:scaling><c:delete val="0"/><c:axPos val="b"/><c:numFmt formatCode="#,##0" sourceLinked="0"/><c:tickLblPos val="nextTo"/><c:crossAx val="48650112"/><c:crosses val="autoZero"/><c:crossBetween val="between"/></c:valAx>'
            . '</c:plotArea><c:legend><c:legendPos val="b"/><c:overlay val="0"/></c:legend><c:plotVisOnly val="1"/><c:dispBlanksAs val="zero"/></c:chart>'
            . '<c:printSettings><c:headerFooter/><c:pageMargins b="0.75" l="0.7" r="0.7" t="0.75" header="0.3" footer="0.3"/><c:pageSetup/></c:printSettings>'
            . '</c:chartSpace>';
    }

    private function stylesXml(): string
    {
        return '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
            . '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
            . '<numFmts count="4"><numFmt numFmtId="164" formatCode="#,##0.00"/><numFmt numFmtId="165" formatCode="0.0%"/><numFmt numFmtId="166" formatCode="dd-mmm-yyyy"/><numFmt numFmtId="167" formatCode="hh:mm"/></numFmts>'
            . '<fonts count="7">'
            . '<font><sz val="10"/><name val="Aptos"/><family val="2"/></font>'
            . '<font><b/><color rgb="FFFFFFFF"/><sz val="10"/><name val="Aptos"/></font>'
            . '<font><b/><color rgb="FFFFFFFF"/><sz val="16"/><name val="Aptos Display"/></font>'
            . '<font><b/><color rgb="FFFFFFFF"/><sz val="12"/><name val="Aptos Display"/></font>'
            . '<font><b/><color rgb="FF12355B"/><sz val="10"/><name val="Aptos"/></font>'
            . '<font><b/><color rgb="FF12355B"/><sz val="14"/><name val="Aptos Display"/></font>'
            . '<font><i/><color rgb="FF64748B"/><sz val="9"/><name val="Aptos"/></font>'
            . '</fonts>'
            . '<fills count="10">'
            . '<fill><patternFill patternType="none"/></fill>'
            . '<fill><patternFill patternType="gray125"/></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF12355B"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF0F766E"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFDDEBF7"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFE2F0D9"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFFCE4D6"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF7FBFF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFE8F5F2"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF2F2F2"/><bgColor indexed="64"/></patternFill></fill>'
            . '</fills>'
            . '<borders count="2">'
            . '<border><left/><right/><top/><bottom/><diagonal/></border>'
            . '<border><left style="thin"><color rgb="FFD9E2F3"/></left><right style="thin"><color rgb="FFD9E2F3"/></right><top style="thin"><color rgb="FFD9E2F3"/></top><bottom style="thin"><color rgb="FFD9E2F3"/></bottom><diagonal/></border>'
            . '</borders>'
            . '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
            . '<cellXfs count="28">'
            . '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"><alignment vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="1" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="2" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="3" fillId="3" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center"/></xf>'
            . '<xf numFmtId="164" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" applyAlignment="1"><alignment horizontal="right" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="166" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="167" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="4" fillId="4" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="4" fillId="4" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="1" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right" vertical="center"/></xf>'
            . '<xf numFmtId="164" fontId="1" fillId="2" borderId="1" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="4" fillId="5" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="4" fillId="6" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="4" fillId="8" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="5" fillId="8" borderId="1" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="5" fillId="8" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="165" fontId="4" fillId="4" borderId="1" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="4" fillId="7" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1" applyAlignment="1"><alignment vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="166" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="167" fontId="0" fillId="0" borderId="1" xfId="0" applyNumberFormat="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="4" fillId="5" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="4" fillId="6" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1" applyAlignment="1"><alignment vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="6" fillId="9" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '</cellXfs>'
            . '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
            . '</styleSheet>';
    }

    private function columnName(int $index): string
    {
        $name = '';
        while ($index > 0) {
            $index--;
            $name = chr(65 + ($index % 26)) . $name;
            $index = intdiv($index, 26);
        }
        return $name;
    }

    private function xmlNumber(float $value): string
    {
        if (!is_finite($value)) {
            return '0';
        }
        $text = number_format($value, 12, '.', '');
        return rtrim(rtrim($text, '0'), '.');
    }

    private function xml(string $value): string
    {
        return htmlspecialchars($value, ENT_XML1 | ENT_QUOTES, 'UTF-8');
    }
}
