<?php

declare(strict_types=1);

namespace App\Services;

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

/**
 * Preserved legacy R9 code-driven XLSX export engine for explicit rollback.
 *
 * Design goals:
 * - Keep the first sheet familiar to finance staff who used the manual workbook.
 * - Deliver a modern executive design across Summary, Daily Revenue, and Revenue Chart sheets.
 * - Avoid third-party runtime dependencies and remain compatible with PHP 7.4+.
 * - Oracle writes: NONE.
 */
final class LegacyXlsxExportService
{
    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;
    private const STYLE_TITLE_SUMMARY = 28;
    private const STYLE_TITLE_DAILY = 29;
    private const STYLE_TITLE_CHART = 30;
    private const STYLE_SECTION_SUMMARY = 31;
    private const STYLE_SECTION_DAILY = 32;
    private const STYLE_SECTION_CHART = 33;
    private const STYLE_KPI_LABEL_SUMMARY = 34;
    private const STYLE_KPI_VALUE_SUMMARY = 35;
    private const STYLE_KPI_LABEL_DAILY = 36;
    private const STYLE_KPI_VALUE_DAILY = 37;
    private const STYLE_KPI_LABEL_CHART = 38;
    private const STYLE_KPI_VALUE_CHART = 39;
    private const STYLE_HEADER_SUMMARY = 40;
    private const STYLE_HEADER_DAILY = 41;
    private const STYLE_HEADER_CHART = 42;
    private const STYLE_TOTAL_SUMMARY = 43;
    private const STYLE_TOTAL_DAILY = 44;
    private const STYLE_TOTAL_CHART = 45;
    // R7 visual-fidelity styles: diverse, calm, and balanced KPI palettes.
    private const STYLE_SUMMARY_KPI_1_LABEL = 46;
    private const STYLE_SUMMARY_KPI_1_VALUE = 47;
    private const STYLE_SUMMARY_KPI_2_LABEL = 48;
    private const STYLE_SUMMARY_KPI_2_VALUE = 49;
    private const STYLE_SUMMARY_KPI_3_LABEL = 50;
    private const STYLE_SUMMARY_KPI_3_VALUE = 51;
    private const STYLE_SUMMARY_KPI_4_LABEL = 52;
    private const STYLE_SUMMARY_KPI_4_VALUE = 53;
    private const STYLE_SUMMARY_KPI_5_LABEL = 54;
    private const STYLE_SUMMARY_KPI_5_VALUE = 55;
    private const STYLE_DAILY_KPI_1_LABEL = 56;
    private const STYLE_DAILY_KPI_1_VALUE = 57;
    private const STYLE_DAILY_KPI_2_LABEL = 58;
    private const STYLE_DAILY_KPI_2_VALUE = 59;
    private const STYLE_DAILY_KPI_3_LABEL = 60;
    private const STYLE_DAILY_KPI_3_VALUE = 61;
    private const STYLE_DAILY_KPI_4_LABEL = 62;
    private const STYLE_DAILY_KPI_4_VALUE = 63;
    private const STYLE_CHART_KPI_1_LABEL = 64;
    private const STYLE_CHART_KPI_1_VALUE = 65;
    private const STYLE_CHART_KPI_2_LABEL = 66;
    private const STYLE_CHART_KPI_2_VALUE = 67;
    private const STYLE_CHART_KPI_3_LABEL = 68;
    private const STYLE_CHART_KPI_3_VALUE = 69;
    private const STYLE_UNIT_SECTION_BLUE = 70;
    private const STYLE_UNIT_SECTION_TEAL = 71;
    private const STYLE_UNIT_SECTION_PURPLE = 72;
    // R9 executive visual fidelity styles: Excel-safe cell-based bars; no drawing XML.
    private const STYLE_CHART_BAR_PURPLE = 73;
    private const STYLE_CHART_BAR_BLUE = 74;
    private const STYLE_CHART_BAR_TEAL = 75;
    private const STYLE_CHART_BAR_AMBER = 76;
    private const STYLE_CHART_BAR_ROSE = 77;
    private const STYLE_CHART_BAR_CYAN = 78;
    private const STYLE_CHART_BAR_EMERALD = 79;
    private const STYLE_CHART_BAR_ORANGE = 80;
    private const STYLE_CHART_BAR_SKY = 81;
    private const STYLE_CHART_BAR_MAGENTA = 82;

    /** @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);
        $chartSheet = $this->buildRevenueChartSheet($report);

        $chart = null;
        $hasChart = false;

        $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),
            'xl/worksheets/sheet3.xml' => $this->sheetXml($chartSheet),
        ];

        if ($hasChart && is_array($chart)) {
            $entries['xl/worksheets/_rels/sheet3.xml.rels'] = $this->chartSheetRels();
            $entries['xl/drawings/drawing1.xml'] = $this->chartDrawingXml();
            $entries['xl/drawings/_rels/drawing1.xml.rels'] = $this->chartDrawingRels();
            $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'] : [];
        $patientsSource = 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'));
        $displayDate = substr($generatedAt, 0, 10);

        $patientCount = (int)($summary['patient_count'] ?? count($patientsSource));
        $dailyRevenue = (float)($summary['daily_revenue'] ?? 0);
        $averageRevenue = (float)($summary['average_revenue'] ?? ($patientCount > 0 ? $dailyRevenue / $patientCount : 0));
        $avgLos = 0.0;
        $losCount = 0;
        foreach ($patientsSource as $patient) {
            if (!is_array($patient)) { continue; }
            $los = (float)($patient['total_los'] ?? $patient['los'] ?? 0);
            if ($los > 0) { $avgLos += $los; $losCount++; }
        }
        $avgLos = $losCount > 0 ? ($avgLos / $losCount) : 0.0;

        $rows = [];
        $merges = [
            'A1:B2', 'C1:H2', 'I1:K2',
            'A4:B4', 'C4:D4', 'E4:F4', 'G4:H4', 'I4:K4',
            'A5:B6', 'C5:D6', 'E5:F6', 'G5:H6', 'I5:K6',
            'A8:F8', 'G8:K8',
        ];
        $rowHeights = [1 => 28, 2 => 28, 3 => 8, 4 => 26, 5 => 34, 6 => 12, 7 => 8, 8 => 28, 9 => 30];

        $rows[] = $this->row([
            $this->cell($hospitalCode . "\nExecutive Inpatient Revenue", self::STYLE_TITLE_SUMMARY), null,
            $this->cell("Executive Summary\nDaily Inpatient Revenue Overview", self::STYLE_TITLE_SUMMARY), null, null, null, null, null,
            $this->cell($displayDate . "\n" . ($hospitalName !== '' ? $hospitalName : 'Hospital Branch'), self::STYLE_TITLE_SUMMARY), null, null,
        ], 11);
        $rows[] = $this->row([], 11);
        $rows[] = $this->row([], 11);

        $rows[] = $this->row([
            $this->cell('Total Patients', self::STYLE_SUMMARY_KPI_1_LABEL), null,
            $this->cell('Total Daily Revenue', self::STYLE_SUMMARY_KPI_2_LABEL), null,
            $this->cell('Average Daily Revenue', self::STYLE_SUMMARY_KPI_3_LABEL), null,
            $this->cell('Average LOS (All)', self::STYLE_SUMMARY_KPI_4_LABEL), null,
            $this->cell('Active Care Areas', self::STYLE_SUMMARY_KPI_5_LABEL), null, null,
        ], 11);
        $rows[] = $this->row([
            $this->cell($patientCount, self::STYLE_SUMMARY_KPI_1_VALUE), null,
            $this->cell($dailyRevenue, self::STYLE_SUMMARY_KPI_2_VALUE), null,
            $this->cell($averageRevenue, self::STYLE_SUMMARY_KPI_3_VALUE), null,
            $this->cell($avgLos, self::STYLE_SUMMARY_KPI_4_VALUE), null,
            $this->cell(count($units), self::STYLE_SUMMARY_KPI_5_VALUE), null, null,
        ], 11);
        $rows[] = $this->row([], 11);
        $rows[] = $this->row([], 11);

        $rows[] = $this->row([
            $this->cell('Revenue by Care Area', self::STYLE_SECTION_SUMMARY), null, null, null, null, null,
            $this->cell('Top Financial Classes by Daily Revenue', self::STYLE_SECTION_CHART), null, null, null, null,
        ], 11);
        $rows[] = $this->row([
            $this->cell('Unit', self::STYLE_HEADER_SUMMARY),
            $this->cell('Patients', self::STYLE_HEADER_SUMMARY),
            $this->cell('Beds', self::STYLE_HEADER_SUMMARY),
            $this->cell('Occupancy %', self::STYLE_HEADER_SUMMARY),
            $this->cell('Daily Revenue (SR)', self::STYLE_HEADER_SUMMARY),
            $this->cell('Average / Patient (SR)', self::STYLE_HEADER_SUMMARY),
            $this->cell('#', self::STYLE_HEADER_CHART),
            $this->cell('Financial Class', self::STYLE_HEADER_CHART),
            $this->cell('Patients', self::STYLE_HEADER_CHART),
            $this->cell('Daily Revenue (SR)', self::STYLE_HEADER_CHART),
            $this->cell('Share %', self::STYLE_HEADER_CHART),
        ], 11);

        $maxRows = max(count($units), count($companies), 1);
        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] : [];
            $unitPatients = (int)($unit['patients'] ?? 0);
            $beds = (int)($unit['beds'] ?? 0);
            $unitRevenue = (float)($unit['daily_revenue'] ?? 0);
            $unitAverage = $unitPatients > 0 ? ($unitRevenue / $unitPatients) : 0.0;
            $companyRevenue = (float)($company['daily_revenue'] ?? 0);

            $rows[] = $this->row([
                $unit !== [] ? $this->cell((string)($unit['unit_name'] ?? ''), self::STYLE_BORDER_TEXT) : null,
                $unit !== [] ? $this->cell($unitPatients, self::STYLE_BORDER_INTEGER) : null,
                $unit !== [] ? $this->cell($beds, self::STYLE_BORDER_INTEGER) : null,
                $unit !== [] ? $this->cell($beds > 0 ? ($unitPatients / $beds) : 0, self::STYLE_PERCENT) : null,
                $unit !== [] ? $this->cell($unitRevenue, self::STYLE_BORDER_CURRENCY) : null,
                $unit !== [] ? $this->cell($unitAverage, self::STYLE_BORDER_CURRENCY) : null,
                $company !== [] ? $this->cell($i + 1, 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($dailyRevenue > 0 ? ($companyRevenue / $dailyRevenue) : 0, self::STYLE_PERCENT) : null,
            ], 11);
        }

        $totalRow = count($rows) + 1;
        $rows[] = $this->row([
            $this->cell('TOTAL', self::STYLE_TOTAL_SUMMARY),
            $this->cell($patientCount, self::STYLE_TOTAL_SUMMARY),
            $this->cell(array_sum(array_map(static function ($u) { return is_array($u) ? (int)($u['beds'] ?? 0) : 0; }, $units)), self::STYLE_TOTAL_SUMMARY),
            $this->cell(array_sum(array_map(static function ($u) { return is_array($u) ? (int)($u['beds'] ?? 0) : 0; }, $units)) > 0 ? ($patientCount / array_sum(array_map(static function ($u) { return is_array($u) ? (int)($u['beds'] ?? 0) : 0; }, $units))) : 0, self::STYLE_TOTAL_SUMMARY),
            $this->cell($dailyRevenue, self::STYLE_TOTAL_SUMMARY),
            $this->cell($averageRevenue, self::STYLE_TOTAL_SUMMARY),
            $this->cell('TOTAL', self::STYLE_TOTAL_CHART),
            null,
            $this->cell($patientCount, self::STYLE_TOTAL_CHART),
            $this->cell($dailyRevenue, self::STYLE_TOTAL_CHART),
            $this->cell(1.0, self::STYLE_TOTAL_CHART),
        ], 11);
        $merges[] = 'G' . $totalRow . ':H' . $totalRow;
        $rowHeights[$totalRow] = 28;

        return [
            'rows' => $rows,
            'merges' => $merges,
            'widths' => [1 => 26, 2 => 12, 3 => 10, 4 => 14, 5 => 18, 6 => 20, 7 => 8, 8 => 34, 9 => 12, 10 => 18, 11 => 12],
            'freeze_rows' => 0,
            'auto_filter' => null,
            'row_heights' => $rowHeights,
            'landscape' => true,
            'drawing' => false,
            'chart' => null,
            'tab_color' => '0F766E',
        ];
    }


    private function buildRevenueChartSheet(array $report): array
    {
        $summary = is_array($report['summary'] ?? null) ? $report['summary'] : [];
        $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'));
        $displayDate = substr($generatedAt, 0, 10);
        $totalRevenue = (float)($summary['daily_revenue'] ?? 0);

        usort($companies, static function ($a, $b): int {
            $av = is_array($a) ? (float)($a['daily_revenue'] ?? 0) : 0.0;
            $bv = is_array($b) ? (float)($b['daily_revenue'] ?? 0) : 0.0;
            return $bv <=> $av;
        });
        $topCompanies = array_slice($companies, 0, 10);

        $rows = [];
        $merges = ['A1:C2', 'D1:AG2', 'AH1:AL2', 'A4:AL4'];
        $rowHeights = [1 => 28, 2 => 28, 3 => 8, 4 => 28, 5 => 8];

        $rows[] = $this->row([
            $this->cell($hospitalCode . "
Executive Revenue", self::STYLE_TITLE_CHART), null, null,
            $this->cell("Top Payers by Daily Revenue
Executive Distribution", self::STYLE_TITLE_CHART),
        ], 38);
        $rows[] = $this->row([], 38);
        $rows[] = $this->row([], 38);
        $rows[] = $this->row([$this->cell('Daily Revenue Distribution (SAR)', self::STYLE_SECTION_CHART)], 38);
        $rows[] = $this->row([], 38);

        $topPayer = '';
        $topPayerShare = 0.0;
        $maxRevenue = 0.0;
        if (isset($topCompanies[0]) && is_array($topCompanies[0])) {
            $topPayer = trim((string)($topCompanies[0]['financial_class'] ?? ''));
            $maxRevenue = (float)($topCompanies[0]['daily_revenue'] ?? 0);
            $topPayerShare = $totalRevenue > 0 ? ($maxRevenue / $totalRevenue) : 0.0;
        }

        $barStyles = [
            self::STYLE_CHART_BAR_PURPLE, self::STYLE_CHART_BAR_BLUE, self::STYLE_CHART_BAR_TEAL,
            self::STYLE_CHART_BAR_AMBER, self::STYLE_CHART_BAR_ROSE, self::STYLE_CHART_BAR_CYAN,
            self::STYLE_CHART_BAR_EMERALD, self::STYLE_CHART_BAR_ORANGE, self::STYLE_CHART_BAR_SKY,
            self::STYLE_CHART_BAR_MAGENTA,
        ];

        if ($topCompanies === []) {
            $rows[] = $this->row([$this->cell('No payer data is available for this snapshot.', self::STYLE_NOTE)], 38);
            $merges[] = 'A6:AL6';
            $rowHeights[6] = 32;
        } else {
            $rowNum = 6;
            foreach ($topCompanies as $index => $company) {
                if (!is_array($company)) { continue; }
                $name = trim((string)($company['financial_class'] ?? ''));
                $value = (float)($company['daily_revenue'] ?? 0);
                $share = $totalRevenue > 0 ? ($value / $totalRevenue) : 0.0;
                $segments = $maxRevenue > 0 ? max(1, min(30, (int)round(($value / $maxRevenue) * 30))) : 1;
                $barStyle = $barStyles[$index % count($barStyles)];

                $cells = [$this->cell($this->shortChartLabel($name), self::STYLE_BORDER_TEXT), null, null];
                for ($segment = 1; $segment <= 30; $segment++) {
                    $cells[] = $segment <= $segments ? $this->cell(0, $barStyle) : $this->cell('', self::STYLE_BODY);
                }
                $cells[] = $this->cell($value, self::STYLE_BORDER_CURRENCY);
                $cells[] = null;
                $cells[] = null;
                $cells[] = $this->cell($share, self::STYLE_PERCENT);
                $cells[] = null;
                $rows[] = $this->row($cells, 38);
                $merges[] = 'A' . $rowNum . ':C' . $rowNum;
                $merges[] = 'AH' . $rowNum . ':AJ' . $rowNum;
                $merges[] = 'AK' . $rowNum . ':AL' . $rowNum;
                $rowHeights[$rowNum] = 25;
                $rowNum++;
            }
        }

        $kpiStart = max(18, 7 + count($topCompanies));
        while (count($rows) < ($kpiStart - 1)) { $rows[] = $this->row([], 38); }
        $merges[] = 'A' . $kpiStart . ':L' . $kpiStart;
        $merges[] = 'M' . $kpiStart . ':Z' . $kpiStart;
        $merges[] = 'AA' . $kpiStart . ':AL' . $kpiStart;
        $merges[] = 'A' . ($kpiStart + 1) . ':L' . ($kpiStart + 2);
        $merges[] = 'M' . ($kpiStart + 1) . ':Z' . ($kpiStart + 2);
        $merges[] = 'AA' . ($kpiStart + 1) . ':AL' . ($kpiStart + 2);

        $rows[] = $this->row([
            $this->cell('Total Daily Revenue', self::STYLE_CHART_KPI_1_LABEL),
        ], 38);
        $rows[count($rows)-1][12] = $this->cell('Top Payer', self::STYLE_CHART_KPI_2_LABEL);
        $rows[count($rows)-1][26] = $this->cell('Payers Count', self::STYLE_CHART_KPI_3_LABEL);
        $rows[] = $this->row([$this->cell($totalRevenue, self::STYLE_CHART_KPI_1_VALUE)], 38);
        $rows[count($rows)-1][12] = $this->cell($this->shortChartLabel($topPayer), self::STYLE_CHART_KPI_2_VALUE);
        $rows[count($rows)-1][26] = $this->cell(count($companies), self::STYLE_CHART_KPI_3_VALUE);
        $rows[] = $this->row([], 38);
        $rowHeights[$kpiStart] = 25;
        $rowHeights[$kpiStart + 1] = 36;
        $rowHeights[$kpiStart + 2] = 10;

        $tableTitleRow = $kpiStart + 5;
        while (count($rows) < ($tableTitleRow - 1)) { $rows[] = $this->row([], 38); }
        $rows[] = $this->row([$this->cell('Top 5 Payers', self::STYLE_SECTION_CHART)], 38);
        $merges[] = 'A' . $tableTitleRow . ':AL' . $tableTitleRow;
        $rowHeights[$tableTitleRow] = 25;
        $headerRow = $tableTitleRow + 1;
        $rows[] = $this->row([
            $this->cell('Rank', self::STYLE_HEADER_CHART),
            $this->cell('Financial Class', self::STYLE_HEADER_CHART),
            null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
            $this->cell('Daily Revenue (SAR)', self::STYLE_HEADER_CHART),
            null, null, null, null, null, null,
            $this->cell('Share %', self::STYLE_HEADER_CHART), null, null, null, null,
            $this->cell('Patients', self::STYLE_HEADER_CHART), null, null, null, null, null,
        ], 38);
        $merges[] = 'B' . $headerRow . ':T' . $headerRow;
        $merges[] = 'U' . $headerRow . ':AA' . $headerRow;
        $merges[] = 'AB' . $headerRow . ':AF' . $headerRow;
        $merges[] = 'AG' . $headerRow . ':AL' . $headerRow;

        $rowNum = $headerRow + 1;
        foreach (array_slice($companies, 0, 5) as $index => $company) {
            if (!is_array($company)) { continue; }
            $value = (float)($company['daily_revenue'] ?? 0);
            $rows[] = $this->row([
                $this->cell($index + 1, self::STYLE_BORDER_INTEGER),
                $this->cell((string)($company['financial_class'] ?? ''), self::STYLE_BORDER_TEXT),
            ], 38);
            $rows[count($rows)-1][20] = $this->cell($value, self::STYLE_BORDER_CURRENCY);
            $rows[count($rows)-1][27] = $this->cell($totalRevenue > 0 ? ($value / $totalRevenue) : 0, self::STYLE_PERCENT);
            $rows[count($rows)-1][32] = $this->cell((int)($company['patients'] ?? 0), self::STYLE_BORDER_INTEGER);
            $merges[] = 'B' . $rowNum . ':T' . $rowNum;
            $merges[] = 'U' . $rowNum . ':AA' . $rowNum;
            $merges[] = 'AB' . $rowNum . ':AF' . $rowNum;
            $merges[] = 'AG' . $rowNum . ':AL' . $rowNum;
            $rowHeights[$rowNum] = 23;
            $rowNum++;
        }

        return [
            'rows' => $rows,
            'merges' => $merges,
            'widths' => [
                1 => 14, 2 => 14, 3 => 14,
                4 => 1.65, 5 => 1.65, 6 => 1.65, 7 => 1.65, 8 => 1.65, 9 => 1.65, 10 => 1.65, 11 => 1.65, 12 => 1.65, 13 => 1.65,
                14 => 1.65, 15 => 1.65, 16 => 1.65, 17 => 1.65, 18 => 1.65, 19 => 1.65, 20 => 1.65, 21 => 1.65, 22 => 1.65, 23 => 1.65,
                24 => 1.65, 25 => 1.65, 26 => 1.65, 27 => 1.65, 28 => 1.65, 29 => 1.65, 30 => 1.65, 31 => 1.65, 32 => 1.65, 33 => 1.65,
                34 => 9, 35 => 9, 36 => 9, 37 => 8, 38 => 8,
            ],
            'freeze_rows' => 0,
            'auto_filter' => null,
            'row_heights' => $rowHeights,
            'landscape' => true,
            'drawing' => false,
            'chart' => null,
            'tab_color' => '5B2BAE',
        ];
    }


    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',
            'A4:C4', 'D4:F4', 'G4:I4', 'J4:M4',
            'A5:C6', 'D5:F6', 'G5:I6', 'J5:M6',
        ];
        $rowHeights = [1 => 34, 2 => 22, 3 => 8, 4 => 25, 5 => 36, 6 => 10, 7 => 8];
        $rows[] = $this->row([
            $this->cell($hospitalCode . ' — Daily Inpatient Revenue — Details', self::STYLE_TITLE_DAILY),
        ], 13);
        $rows[] = $this->row([
            $this->cell('Generated at ' . $generatedAt . ($hospitalName !== '' ? '  |  Branch: ' . $hospitalName : ''), self::STYLE_NOTE),
        ], 13);
        $rows[] = $this->row([], 13);

        $patientCount = count($patients);
        $totalDailyRevenue = (float)($summary['daily_revenue'] ?? 0);
        $averageDailyRevenue = $patientCount > 0 ? ($totalDailyRevenue / $patientCount) : 0.0;
        $losSum = 0.0; $losCount = 0;
        foreach ($patients as $patient) {
            if (!is_array($patient)) { continue; }
            $los = (float)($patient['total_los'] ?? $patient['los'] ?? 0);
            if ($los > 0) { $losSum += $los; $losCount++; }
        }
        $averageLos = $losCount > 0 ? ($losSum / $losCount) : 0.0;

        $rows[] = $this->row([
            $this->cell('Total Patients', self::STYLE_DAILY_KPI_1_LABEL), null, null,
            $this->cell('Total Daily Revenue', self::STYLE_DAILY_KPI_2_LABEL), null, null,
            $this->cell('Average Daily Revenue', self::STYLE_DAILY_KPI_3_LABEL), null, null,
            $this->cell('Average LOS (All)', self::STYLE_DAILY_KPI_4_LABEL), null, null, null,
        ], 13);
        $rows[] = $this->row([
            $this->cell($patientCount, self::STYLE_DAILY_KPI_1_VALUE), null, null,
            $this->cell($totalDailyRevenue, self::STYLE_DAILY_KPI_2_VALUE), null, null,
            $this->cell($averageDailyRevenue, self::STYLE_DAILY_KPI_3_VALUE), null, null,
            $this->cell($averageLos, self::STYLE_DAILY_KPI_4_VALUE), null, null, null,
        ], 13);
        $rows[] = $this->row([], 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;
        $unitSectionIndex = 0;
        $unitSectionStyles = [self::STYLE_UNIT_SECTION_BLUE, self::STYLE_UNIT_SECTION_TEAL, self::STYLE_UNIT_SECTION_PURPLE];
        $unitHeaderStyles = [self::STYLE_HEADER_DAILY, self::STYLE_HEADER_SUMMARY, self::STYLE_HEADER_CHART];
        $unitTotalStyles = [self::STYLE_TOTAL_DAILY, self::STYLE_TOTAL_SUMMARY, self::STYLE_TOTAL_CHART];
        foreach ($groups as $unitName => $unitPatients) {
            $sectionRow = count($rows) + 1;
            $paletteIndex = $unitSectionIndex % count($unitSectionStyles);
            $sectionStyle = $unitSectionStyles[$paletteIndex];
            $headerStyle = $unitHeaderStyles[$paletteIndex];
            $totalStyle = $unitTotalStyles[$paletteIndex];
            $unitSectionIndex++;
            $rows[] = $this->row([$this->cell($unitName, $sectionStyle)], 13);
            $merges[] = 'A' . $sectionRow . ':M' . $sectionRow;
            $rowHeights[$sectionRow] = 26;

            $headerCells = [];
            foreach ($headers as $header) { $headerCells[] = $this->cell($header, $headerStyle); }
            $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);
            }

            $patientCountUnit = count($unitPatients);
            $occupancy = $bedCounts > 0 ? ($patientCountUnit / $bedCounts) : 0;
            $rows[] = $this->row([
                $this->cell('Patients', self::STYLE_SUMMARY_LABEL), null,
                $this->cell($patientCountUnit, self::STYLE_SUMMARY_VALUE),
                $this->cell('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', $totalStyle), null, null, null, null, null, null, null,
                $this->cell(round($unitRevenue, 2), $totalStyle),
            ], 13);
            $merges[] = 'A' . $unitTotalRow . ':H' . $unitTotalRow;
            $merges[] = 'I' . $unitTotalRow . ':M' . $unitTotalRow;
            $rowHeights[$unitTotalRow] = 25;

            $unitAverage = $patientCountUnit > 0 ? ($unitRevenue / $patientCountUnit) : 0.0;
            $unitAverageRow = count($rows) + 1;
            $rows[] = $this->row([
                $this->cell('Average Daily Revenue per Patient', self::STYLE_SUMMARY_LABEL), null, null, null, null, null, null, null,
                $this->cell(round($unitAverage, 2), self::STYLE_GRAND_CURRENCY),
            ], 13);
            $merges[] = 'A' . $unitAverageRow . ':H' . $unitAverageRow;
            $merges[] = 'I' . $unitAverageRow . ':M' . $unitAverageRow;
            $rowHeights[$unitAverageRow] = 22;
            $rows[] = $this->row([], 13);
        }

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

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

        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,
            'tab_color' => '2563EB',
        ];
    }

    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 .= '>';
        $tabColor = strtoupper((string)($sheet['tab_color'] ?? ''));
        if (preg_match('/^[0-9A-F]{6}$/', $tabColor) === 1) {
            $xml .= '<sheetPr><tabColor rgb="FF' . $tabColor . '"/></sheetPr>';
        }
        $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 shortChartLabel(string $name): string
    {
        $name = trim((string)preg_replace('/\s+/', ' ', $name));
        if ($name === '') {
            return '';
        }
        if (strlen($name) <= 28) {
            return $name;
        }
        return rtrim(substr($name, 0, 27)) . '…';
    }

    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/worksheets/sheet3.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"/>'
            . '<sheet name="Revenue Chart" sheetId="3" r:id="rId3"/>'
            . '</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/worksheet" Target="worksheets/sheet3.xml"/>'
            . '<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>'
            . '</Relationships>';
    }

    private function chartSheetRels(): 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 chartDrawingRels(): 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 chartDrawingXml(): 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>0</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>3</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from>'
            . '<xdr:to><xdr:col>14</xdr:col><xdr:colOff>0</xdr:colOff><xdr:row>30</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>';

        $palette = ['7C3AED', '2563EB', '0D9488', 'F59E0B', 'EC4899', '14B8A6', '8B5CF6', '3B82F6', '10B981', 'F97316'];
        $dataPoints = '';
        for ($i = 0; $i < $count; $i++) {
            $color = $palette[$i % count($palette)];
            $dataPoints .= '<c:dPt><c:idx val="' . $i . '"/><c:spPr><a:solidFill><a:srgbClr val="' . $color . '"/></a:solidFill></c:spPr></c:dPt>';
        }

        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="1400" b="1"/><a:t>Top Payers by Daily Revenue (SAR)</a:t></a:r></a:p></c:rich></c:tx><c:layout/><c:overlay val="0"/></c:title>'
            . '<c:plotArea><c:layout><c:manualLayout><c:layoutTarget val="inner"/><c:xMode val="edge"/><c:yMode val="edge"/><c:x val="0.04"/><c:y val="0.02"/><c:w val="0.92"/><c:h val="0.86"/></c:manualLayout></c:layout><c:barChart><c:barDir val="bar"/><c:grouping val="clustered"/><c:varyColors val="1"/><c:gapWidth val="48"/>'
            . '<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="7C3AED"/></a:solidFill></c:spPr>' . $dataPoints
            . '<c:cat><c:strRef><c:f>&apos;Revenue Chart&apos;!$A$' . $startRow . ':$A$' . $endRow . '</c:f>' . $catCache . '</c:strRef></c:cat>'
            . '<c:val><c:numRef><c:f>&apos;Revenue Chart&apos;!$B$' . $startRow . ':$B$' . $endRow . '</c:f>' . $valCache . '</c:numRef></c:val>'
            . '</c:ser><c:dLbls><c:dLblPos val="outEnd"/><c:numFmt formatCode="#,##0" sourceLinked="0"/><c:showLegendKey val="0"/><c:showVal val="1"/><c:showCatName val="0"/><c:showSerName val="0"/><c:showPercent val="0"/><c:showBubbleSize 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:plotVisOnly val="1"/><c:dispBlanksAs val="zero"/><c:spPr><a:solidFill><a:srgbClr val="FFFFFF"/></a:solidFill><a:ln w="12700"><a:solidFill><a:srgbClr val="DDD6FE"/></a:solidFill></a:ln></c:spPr></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="6"><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"/><numFmt numFmtId="168" formatCode="0.0"/><numFmt numFmtId="169" formatCode=";;;"/></numFmts>'
            . '<fonts count="9">'
            . '<font><sz val="10"/><name val="Aptos"/><family val="2"/><color rgb="FF172554"/></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="FF172554"/><sz val="10"/><name val="Aptos"/></font>'
            . '<font><b/><color rgb="FF172554"/><sz val="14"/><name val="Aptos Display"/></font>'
            . '<font><i/><color rgb="FF64748B"/><sz val="9"/><name val="Aptos"/></font>'
            . '<font><b/><color rgb="FF172554"/><sz val="15"/><name val="Aptos Display"/></font>'
            . '<font><b/><color rgb="FF172554"/><sz val="11"/><name val="Aptos"/></font>'
            . '</fonts>'
            . '<fills count="41">'
            . '<fill><patternFill patternType="none"/></fill>'
            . '<fill><patternFill patternType="gray125"/></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF173F67"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF064E66"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFEAF8F8"/><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="FFF8FAFC"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFE8F5F2"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF1F5F9"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF0B5ED7"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF1F6FF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF4C1D95"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF6F2FF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF0F7C76"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF1557C0"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF6D28D9"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFFFFFFF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFE8F8F5"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFEAF4FF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFFFF4D8"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF0ECFF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFE7F8EC"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF2FBFA"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF1F6FF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFFFF8E8"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF8F5FF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF1FBF4"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFFFF3F5"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF3F6FF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF1FCFD"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFFBF7FF"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFFFF8F0"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF8FAFC"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFF59E0B"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFE11D48"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF0891B2"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF059669"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFEA580C"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FF0284C7"/><bgColor indexed="64"/></patternFill></fill>'
            . '<fill><patternFill patternType="solid"><fgColor rgb="FFDB2777"/><bgColor indexed="64"/></patternFill></fill>'
            . '</fills>' 
            . '<borders count="5">'
            . '<border><left/><right/><top/><bottom/><diagonal/></border>'
            . '<border><left style="thin"><color rgb="FFDCE6F1"/></left><right style="thin"><color rgb="FFDCE6F1"/></right><top style="thin"><color rgb="FFDCE6F1"/></top><bottom style="thin"><color rgb="FFDCE6F1"/></bottom><diagonal/></border>'
            . '<border><left style="thin"><color rgb="FF99D5CF"/></left><right style="thin"><color rgb="FF99D5CF"/></right><top style="thin"><color rgb="FF99D5CF"/></top><bottom style="thin"><color rgb="FF99D5CF"/></bottom><diagonal/></border>'
            . '<border><left style="thin"><color rgb="FFB9D3FA"/></left><right style="thin"><color rgb="FFB9D3FA"/></right><top style="thin"><color rgb="FFB9D3FA"/></top><bottom style="thin"><color rgb="FFB9D3FA"/></bottom><diagonal/></border>'
            . '<border><left style="thin"><color rgb="FFD4C2F4"/></left><right style="thin"><color rgb="FFD4C2F4"/></right><top style="thin"><color rgb="FFD4C2F4"/></top><bottom style="thin"><color rgb="FFD4C2F4"/></bottom><diagonal/></border>'
            . '</borders>'
            . '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
            . '<cellXfs count="83">'
            . '<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="17" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="0" fillId="17" borderId="1" xfId="0" applyNumberFormat="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="right" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="0" fillId="17" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="166" fontId="0" fillId="17" borderId="1" xfId="0" applyNumberFormat="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="167" fontId="0" fillId="17" borderId="1" xfId="0" applyNumberFormat="1" applyFill="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="17" borderId="1" xfId="0" applyFill="1" 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>'
            # 28 summary title
            . '<xf numFmtId="0" fontId="2" fillId="3" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            # 29 daily title
            . '<xf numFmtId="0" fontId="2" fillId="10" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            # 30 chart title
            . '<xf numFmtId="0" fontId="2" fillId="12" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            # 31-33 section bars
            . '<xf numFmtId="0" fontId="3" fillId="14" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="3" fillId="15" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="3" fillId="16" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center"/></xf>'
            # 34-39 KPI labels/values
            . '<xf numFmtId="0" fontId="8" fillId="4" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="7" fillId="17" borderId="2" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="11" borderId="3" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="7" fillId="17" borderId="3" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="13" borderId="4" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="7" fillId="17" borderId="4" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            # 40-42 table headers
            . '<xf numFmtId="0" fontId="1" fillId="14" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="1" fillId="15" borderId="3" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="1" fillId="16" borderId="4" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            # 43-45 totals
            . '<xf numFmtId="164" fontId="1" fillId="14" borderId="2" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="1" fillId="15" borderId="3" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="1" fillId="16" borderId="4" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            # 46-55 Summary KPI cards: mint, amber, sky, lilac, emerald.
            . '<xf numFmtId="0" fontId="8" fillId="18" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="7" fillId="23" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="20" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="7" fillId="25" borderId="2" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="19" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="7" fillId="24" borderId="2" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="21" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="168" fontId="7" fillId="26" borderId="2" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="22" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="7" fillId="27" borderId="2" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            # 56-63 Daily Revenue KPI cards.
            . '<xf numFmtId="0" fontId="8" fillId="24" borderId="3" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="7" fillId="17" borderId="3" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="19" borderId="3" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="7" fillId="30" borderId="3" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="29" borderId="3" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="7" fillId="26" borderId="3" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="28" borderId="3" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="168" fontId="7" fillId="33" borderId="3" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            # 64-69 Chart KPI cards.
            . '<xf numFmtId="0" fontId="8" fillId="26" borderId="4" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="164" fontId="7" fillId="31" borderId="4" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="18" borderId="4" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="7" fillId="23" borderId="4" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="8" fillId="20" borderId="4" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center" wrapText="1"/></xf>'
            . '<xf numFmtId="0" fontId="7" fillId="25" borderId="4" xfId="0" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1"><alignment horizontal="center" vertical="center"/></xf>'
            # 70-72 alternating unit section bars.
            . '<xf numFmtId="0" fontId="3" fillId="15" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="3" fillId="14" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center"/></xf>'
            . '<xf numFmtId="0" fontId="3" fillId="16" borderId="0" xfId="0" applyFont="1" applyFill="1" applyAlignment="1"><alignment horizontal="left" vertical="center"/></xf>'
            # 73-82 R8 Excel-safe multicolor bar blocks (native cells, no drawing XML).
            . '<xf numFmtId="169" fontId="0" fillId="12" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '<xf numFmtId="169" fontId="0" fillId="10" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '<xf numFmtId="169" fontId="0" fillId="14" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '<xf numFmtId="169" fontId="0" fillId="34" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '<xf numFmtId="169" fontId="0" fillId="35" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '<xf numFmtId="169" fontId="0" fillId="36" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '<xf numFmtId="169" fontId="0" fillId="37" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '<xf numFmtId="169" fontId="0" fillId="38" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '<xf numFmtId="169" fontId="0" fillId="39" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '<xf numFmtId="169" fontId="0" fillId="40" borderId="0" xfId="0" applyNumberFormat="1" applyFill="1"/>'
            . '</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');
    }
}
