<?php

declare(strict_types=1);

namespace App\Services;

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

/**
 * Template-first XLSX exporter for Daily Inpatient Revenue.
 *
 * Phase B contract:
 * - The approved Phase A workbook is the visual master.
 * - Runtime code injects current report data into the template package.
 * - Native Excel chart/drawing parts are preserved from the template.
 * - No Oracle writes and no calculation-rule changes.
 * - PHP 7.4 compatible and does not require DOM/SimpleXML extensions.
 */
final class PreviousTemplateXlsxExportService
{
    private string $templatePath;

    public function __construct(string $templatePath)
    {
        $this->templatePath = $templatePath;
    }

    /** @param array<string,mixed> $report */
    public function create(string $path, array $report): void
    {
        if (!is_file($this->templatePath) || filesize($this->templatePath) < 5000) {
            throw new RuntimeException('Daily Revenue master Excel template is missing or invalid.');
        }

        @unlink($path);

        if (class_exists(ZipArchive::class)) {
            if (!@copy($this->templatePath, $path)) {
                throw new RuntimeException('Unable to copy Daily Revenue master Excel template.');
            }
            $zip = new ZipArchive();
            if ($zip->open($path) !== true) {
                @unlink($path);
                throw new RuntimeException('Unable to open copied Daily Revenue master Excel template.');
            }
            try {
                $this->populateZipArchive($zip, $report);
            } finally {
                $zip->close();
            }
        } elseif (class_exists(PharData::class)) {
            $zipPath = $path . '.zip';
            @unlink($zipPath);
            if (!@copy($this->templatePath, $zipPath)) {
                throw new RuntimeException('Unable to stage Daily Revenue master Excel template for PharData.');
            }
            try {
                $archive = new PharData($zipPath);
                $this->populatePharArchive($archive, $report);
                unset($archive);
                if (!@rename($zipPath, $path)) {
                    throw new RuntimeException('Unable to finalize template-first Excel workbook file.');
                }
            } catch (Throwable $e) {
                @unlink($zipPath);
                @unlink($path);
                throw new RuntimeException('Unable to populate Daily Revenue master Excel template using the available archive engine.', 0, $e);
            }
        } else {
            throw new RuntimeException('A ZIP-capable PHP archive engine is required for template-first Excel export.');
        }

        if (!is_file($path) || filesize($path) < 5000) {
            @unlink($path);
            throw new RuntimeException('Template-first Excel export did not produce a valid XLSX file.');
        }
    }

    /** @param array<string,mixed> $report */
    private function populateZipArchive(ZipArchive $zip, array $report): void
    {
        $sheet1 = $this->requiredZipPart($zip, 'xl/worksheets/sheet1.xml');
        $sheet2 = $this->requiredZipPart($zip, 'xl/worksheets/sheet2.xml');
        $sheet3 = $this->requiredZipPart($zip, 'xl/worksheets/sheet3.xml');
        $chart1 = $this->requiredZipPart($zip, 'xl/drawings/charts/chart1.xml');
        $workbook = $this->requiredZipPart($zip, 'xl/workbook.xml');

        $zip->addFromString('xl/worksheets/sheet1.xml', $this->populateSummary($sheet1, $report));
        $zip->addFromString('xl/worksheets/sheet2.xml', $this->populateDailyRevenue($sheet2, $report));
        $zip->addFromString('xl/worksheets/sheet3.xml', $this->populateRevenueChartSheet($sheet3, $report));
        $zip->addFromString('xl/drawings/charts/chart1.xml', $this->populateRevenueChartCache($chart1, $report));
        $zip->addFromString('xl/workbook.xml', $this->ensureFullCalculationOnLoad($workbook));
    }

    /** @param array<string,mixed> $report */
    private function populatePharArchive(PharData $archive, array $report): void
    {
        $sheet1 = $this->requiredPharPart($archive, 'xl/worksheets/sheet1.xml');
        $sheet2 = $this->requiredPharPart($archive, 'xl/worksheets/sheet2.xml');
        $sheet3 = $this->requiredPharPart($archive, 'xl/worksheets/sheet3.xml');
        $chart1 = $this->requiredPharPart($archive, 'xl/drawings/charts/chart1.xml');
        $workbook = $this->requiredPharPart($archive, 'xl/workbook.xml');

        $archive['xl/worksheets/sheet1.xml'] = $this->populateSummary($sheet1, $report);
        $archive['xl/worksheets/sheet2.xml'] = $this->populateDailyRevenue($sheet2, $report);
        $archive['xl/worksheets/sheet3.xml'] = $this->populateRevenueChartSheet($sheet3, $report);
        $archive['xl/drawings/charts/chart1.xml'] = $this->populateRevenueChartCache($chart1, $report);
        $archive['xl/workbook.xml'] = $this->ensureFullCalculationOnLoad($workbook);
    }

    private function requiredZipPart(ZipArchive $zip, string $name): string
    {
        $content = $zip->getFromName($name);
        if (!is_string($content) || $content === '') {
            throw new RuntimeException('Required master workbook part is missing: ' . $name);
        }
        return $content;
    }

    private function requiredPharPart(PharData $archive, string $name): string
    {
        if (!isset($archive[$name])) {
            throw new RuntimeException('Required master workbook part is missing: ' . $name);
        }
        $content = (string)$archive[$name]->getContent();
        if ($content === '') {
            throw new RuntimeException('Required master workbook part is empty: ' . $name);
        }
        return $content;
    }

    /** @param array<string,mixed> $report */
    private function populateSummary(string $xml, array $report): string
    {
        $summary = is_array($report['summary'] ?? null) ? $report['summary'] : [];
        $units = is_array($report['units'] ?? null) ? $report['units'] : [];
        $companies = is_array($report['companies'] ?? null) ? $report['companies'] : [];
        $patients = is_array($report['patients'] ?? null) ? $report['patients'] : [];

        $hospitalCode = strtoupper((string)($report['hospital_code'] ?? 'NJCH'));
        $hospitalName = trim((string)($report['hospital_name'] ?? 'Hospital Branch'));
        $generatedAt = (string)($summary['generated_at'] ?? date('Y-m-d H:i:s'));
        $displayDate = substr($generatedAt, 0, 10);
        $patientCount = (int)($summary['patient_count'] ?? count($patients));
        $dailyRevenue = (float)($summary['daily_revenue'] ?? 0.0);
        $avgRevenue = (float)($summary['average_revenue'] ?? ($patientCount > 0 ? $dailyRevenue / $patientCount : 0.0));
        $avgLos = $this->averageLos($patients);

        $rows = $this->extractRows($xml);
        $row11 = $this->requiredRow($rows, 11);
        $row19 = $this->requiredRow($rows, 19);
        $row23 = $this->requiredRow($rows, 23);
        $row24 = $this->requiredRow($rows, 24);

        $xml = $this->setMergedRangeValue($xml, 'A1:B2', $hospitalCode . "\nExecutive Revenue", 'str');
        $xml = $this->setMergedRangeValue($xml, 'C1:H2', "Executive Summary\nDaily Inpatient Revenue Overview", 'str');
        $xml = $this->setMergedRangeValue($xml, 'I1:K2', $displayDate . "\n" . ($hospitalName !== '' ? $hospitalName : 'Hospital Branch'), 'str');
        $xml = $this->setMergedRangeValue($xml, 'A5:B7', $patientCount, 'n');
        $xml = $this->setMergedRangeValue($xml, 'C5:D7', $dailyRevenue, 'n');
        $xml = $this->setMergedRangeValue($xml, 'E5:F7', $avgRevenue, 'n');
        $xml = $this->setMergedRangeValue($xml, 'G5:H7', $avgLos, 'n');
        $xml = $this->setMergedRangeValue($xml, 'I5:K7', count($units), 'n');

        $staticRows = [];
        for ($i = 1; $i <= 10; $i++) {
            if (isset($rows[$i])) { $staticRows[] = $rows[$i]; }
        }

        $dynamicRows = [];
        $maxRows = max(count($units), count($companies), 1);
        $totalBeds = 0;
        $totalUnitPatients = 0;

        for ($i = 0; $i < $maxRows; $i++) {
            $excelRow = 11 + $i;
            $row = $this->cloneRowXml($row11, $excelRow);
            $unit = isset($units[$i]) && is_array($units[$i]) ? $units[$i] : null;
            $company = isset($companies[$i]) && is_array($companies[$i]) ? $companies[$i] : null;

            if (is_array($unit)) {
                $unitPatients = (int)($unit['patients'] ?? 0);
                $beds = (int)($unit['beds'] ?? 0);
                $unitRevenue = (float)($unit['daily_revenue'] ?? 0.0);
                $unitAverage = $unitPatients > 0 ? $unitRevenue / $unitPatients : 0.0;
                $occupancy = $beds > 0 ? $unitPatients / $beds : 0.0;
                $totalBeds += $beds;
                $totalUnitPatients += $unitPatients;

                $row = $this->setRowCellValue($row, 'A', $excelRow, (string)($unit['unit_name'] ?? ''), 'str');
                $row = $this->setRowCellValue($row, 'B', $excelRow, $unitPatients, 'n');
                $row = $this->setRowCellValue($row, 'C', $excelRow, $beds, 'n');
                $row = $this->setRowCellValue($row, 'D', $excelRow, $occupancy, 'n');
                $row = $this->setRowCellValue($row, 'E', $excelRow, $unitRevenue, 'n');
                $row = $this->setRowCellValue($row, 'F', $excelRow, $unitAverage, 'n');
            } else {
                foreach (['A','B','C','D','E','F'] as $col) { $row = $this->clearRowCellValue($row, $col, $excelRow); }
            }

            if (is_array($company)) {
                $companyRevenue = (float)($company['daily_revenue'] ?? 0.0);
                $companyShare = $dailyRevenue > 0 ? $companyRevenue / $dailyRevenue : 0.0;
                $row = $this->setRowCellValue($row, 'G', $excelRow, $i + 1, 'n');
                $row = $this->setRowCellValue($row, 'H', $excelRow, (string)($company['financial_class'] ?? ''), 'str');
                $row = $this->setRowCellValue($row, 'I', $excelRow, (int)($company['patients'] ?? 0), 'n');
                $row = $this->setRowCellValue($row, 'J', $excelRow, $companyRevenue, 'n');
                $row = $this->setRowCellValue($row, 'K', $excelRow, $companyShare, 'n');
            } else {
                foreach (['G','H','I','J','K'] as $col) { $row = $this->clearRowCellValue($row, $col, $excelRow); }
            }
            $dynamicRows[] = $row;
        }

        $totalRowNumber = 11 + $maxRows;
        $totalRow = $this->cloneRowXml($row19, $totalRowNumber);
        $occupancyTotal = $totalBeds > 0 ? $totalUnitPatients / $totalBeds : 0.0;
        $totalValues = [
            'A' => ['TOTAL','str'], 'B' => [$patientCount,'n'], 'C' => [$totalBeds,'n'],
            'D' => [$occupancyTotal,'n'], 'E' => [$dailyRevenue,'n'], 'F' => [$avgRevenue,'n'],
            'G' => ['TOTAL','str'], 'I' => [$patientCount,'n'], 'J' => [$dailyRevenue,'n'], 'K' => [1.0,'n'],
        ];
        foreach ($totalValues as $col => $pair) { $totalRow = $this->setRowCellValue($totalRow, $col, $totalRowNumber, $pair[0], $pair[1]); }
        $totalRow = $this->clearRowCellValue($totalRow, 'H', $totalRowNumber);
        $dynamicRows[] = $totalRow;

        $footerRow = $totalRowNumber + 4;
        $footer1 = $this->cloneRowXml($row23, $footerRow);
        $footer2 = $this->cloneRowXml($row24, $footerRow + 1);
        $footer1 = $this->setRowCellValue($footer1, 'A', $footerRow, 'Executive Inpatient Revenue Dashboard   •   Values in SAR   •   Designed for leadership review', 'str');
        $dynamicRows[] = $footer1;
        $dynamicRows[] = $footer2;

        $xml = $this->replaceSheetDataRows($xml, array_merge($staticRows, $dynamicRows));
        $xml = $this->replaceMergeCellsFromRow($xml, 11, ['A' . $footerRow . ':K' . ($footerRow + 1)]);
        $xml = $this->updateConditionalFormattingRange($xml, 'D11:D' . (10 + max(count($units), 1)));
        return $xml;
    }

    /** @param array<string,mixed> $report */
    private function populateDailyRevenue(string $xml, array $report): string
    {
        $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'] ?? 'Hospital Branch'));
        $generatedAt = (string)($summary['generated_at'] ?? date('Y-m-d H:i:s'));
        $displayDate = substr($generatedAt, 0, 10);
        $patientCount = count($patients);
        $dailyRevenue = (float)($summary['daily_revenue'] ?? 0.0);
        $avgRevenue = $patientCount > 0 ? $dailyRevenue / $patientCount : 0.0;
        $avgLos = $this->averageLos($patients);

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

        $rows = $this->extractRows($xml);
        $prototypes = [[9,10,11,19],[21,22,23,31],[33,34,35,43]];
        foreach ($prototypes as $set) {
            foreach ($set as $number) { $this->requiredRow($rows, $number); }
        }
        $footer45 = $this->requiredRow($rows, 45);
        $footer46 = $this->requiredRow($rows, 46);

        $xml = $this->setMergedRangeValue($xml, 'A1:C2', $hospitalCode . "\nOperations", 'str');
        $xml = $this->setMergedRangeValue($xml, 'D1:J2', "Daily Inpatient Revenue\nOperational Detail", 'str');
        $xml = $this->setMergedRangeValue($xml, 'K1:M2', $displayDate . "\n" . ($hospitalName !== '' ? $hospitalName : 'Hospital Branch'), 'str');
        $xml = $this->setMergedRangeValue($xml, 'A5:C7', $patientCount, 'n');
        $xml = $this->setMergedRangeValue($xml, 'D5:F7', $dailyRevenue, 'n');
        $xml = $this->setMergedRangeValue($xml, 'G5:I7', $avgRevenue, 'n');
        $xml = $this->setMergedRangeValue($xml, 'J5:M7', $avgLos, 'n');

        $staticRows = [];
        for ($i = 1; $i <= 7; $i++) { if (isset($rows[$i])) { $staticRows[] = $rows[$i]; } }
        $dynamicRows = [];
        $dynamicMerges = [];
        $headers = ['Attending Doctor','Financial Class','LOS','Nationality','Patient Name','Patient ID','Time','Date','Daily Revenue','Bed','Room','Initial Admission','Total LOS'];
        $currentRow = 9;
        $grandRevenue = 0.0;
        $unitIndex = 0;

        foreach ($groups as $unitName => $unitPatients) {
            $proto = $prototypes[$unitIndex % 3];
            $unitIndex++;

            $section = $this->cloneRowXml($rows[$proto[0]], $currentRow);
            $section = $this->setRowCellValue($section, 'A', $currentRow, (string)$unitName, 'str');
            foreach (range('B','M') as $col) { $section = $this->clearRowCellValue($section, $col, $currentRow); }
            $dynamicRows[] = $section;
            $dynamicMerges[] = 'A' . $currentRow . ':M' . $currentRow;
            $currentRow++;

            $header = $this->cloneRowXml($rows[$proto[1]], $currentRow);
            foreach ($headers as $index => $label) {
                $header = $this->setRowCellValue($header, $this->columnName($index + 1), $currentRow, $label, 'str');
            }
            $dynamicRows[] = $header;
            $currentRow++;

            $unitRevenue = 0.0;
            $bedCount = 0;
            foreach ($unitPatients as $patient) {
                if (!is_array($patient)) { continue; }
                $data = $this->cloneRowXml($rows[$proto[2]], $currentRow);
                $revenue = (float)($patient['daily_revenue'] ?? 0.0);
                $unitRevenue += $revenue;
                $grandRevenue += $revenue;
                $bedCount = max($bedCount, (int)($patient['bed_counts'] ?? 0));

                $values = [
                    [(string)($patient['attending_doctor'] ?? ''),'str'],
                    [(string)($patient['financial_class'] ?? ''),'str'],
                    [(int)($patient['los'] ?? 0),'n'],
                    [(string)($patient['nationality'] ?? ''),'str'],
                    [(string)($patient['patient_name'] ?? ''),'str'],
                    [(string)($patient['patientid'] ?? ''),'str'],
                    [$this->excelTimeSerial($patient['admission_time'] ?? ''),'n'],
                    [$this->excelDateSerial($patient['admission_date'] ?? ''),'n'],
                    [$revenue,'n'],
                    [(string)($patient['bed'] ?? ''),'str'],
                    [(string)($patient['room'] ?? ''),'str'],
                    [$this->excelDateSerial($patient['los_start_date'] ?? ''),'n'],
                    [(int)($patient['total_los'] ?? 0),'n'],
                ];
                foreach ($values as $index => $pair) {
                    $data = $this->setRowCellValue($data, $this->columnName($index + 1), $currentRow, $pair[0], $pair[1]);
                }
                $dynamicRows[] = $data;
                $currentRow++;
            }

            $unitPatientCount = count($unitPatients);
            $occupancy = $bedCount > 0 ? $unitPatientCount / $bedCount : 0.0;
            $unitAverage = $unitPatientCount > 0 ? $unitRevenue / $unitPatientCount : 0.0;

            $summaryRow = $this->cloneRowXml($rows[$proto[3]], $currentRow);
            $summaryRow = $this->setRowCellValue($summaryRow, 'A', $currentRow, 'UNIT DAILY REVENUE TOTAL', 'str');
            $summaryRow = $this->setRowCellValue($summaryRow, 'G', $currentRow, $unitPatientCount . ' Patients  •  ' . $bedCount . ' Beds  •  ' . number_format($occupancy * 100, 1) . '% Occupancy', 'str');
            $summaryRow = $this->setRowCellValue($summaryRow, 'K', $currentRow, round($unitRevenue, 2), 'n');
            $dynamicRows[] = $summaryRow;
            $dynamicMerges = array_merge($dynamicMerges, $this->summaryMergeRefs($currentRow));
            $currentRow++;

            $averageRow = $this->cloneRowXml($rows[$proto[3]], $currentRow);
            $averageRow = $this->setRowCellValue($averageRow, 'A', $currentRow, 'AVERAGE DAILY REVENUE / PATIENT', 'str');
            $averageRow = $this->clearRowCellValue($averageRow, 'G', $currentRow);
            $averageRow = $this->setRowCellValue($averageRow, 'K', $currentRow, round($unitAverage, 2), 'n');
            $dynamicRows[] = $averageRow;
            $dynamicMerges = array_merge($dynamicMerges, $this->summaryMergeRefs($currentRow));
            $currentRow += 2;
        }

        if ($groups === []) {
            $notice = $this->cloneRowXml($rows[9], $currentRow);
            $notice = $this->setRowCellValue($notice, 'A', $currentRow, 'No active inpatients are available for this snapshot.', 'str');
            $dynamicRows[] = $notice;
            $dynamicMerges[] = 'A' . $currentRow . ':M' . $currentRow;
            $currentRow += 2;
        }

        $grand = $this->cloneRowXml($rows[19], $currentRow);
        $grand = $this->setRowCellValue($grand, 'A', $currentRow, 'GRAND TOTAL DAILY REVENUE', 'str');
        $grand = $this->clearRowCellValue($grand, 'G', $currentRow);
        $grand = $this->setRowCellValue($grand, 'K', $currentRow, round($grandRevenue, 2), 'n');
        $dynamicRows[] = $grand;
        $dynamicMerges = array_merge($dynamicMerges, $this->summaryMergeRefs($currentRow));
        $currentRow += 3;

        $footer1 = $this->cloneRowXml($footer45, $currentRow);
        $footer2 = $this->cloneRowXml($footer46, $currentRow + 1);
        $footer1 = $this->setRowCellValue($footer1, 'A', $currentRow, 'Executive Inpatient Revenue Dashboard   •   Values in SAR   •   All units populated dynamically from current report data', 'str');
        $dynamicRows[] = $footer1;
        $dynamicRows[] = $footer2;
        $dynamicMerges[] = 'A' . $currentRow . ':M' . ($currentRow + 1);

        $xml = $this->replaceSheetDataRows($xml, array_merge($staticRows, $dynamicRows));
        $xml = $this->replaceMergeCellsFromRow($xml, 9, $dynamicMerges);
        return $xml;
    }

    /** @param array<string,mixed> $report */
    private function populateRevenueChartSheet(string $xml, array $report): string
    {
        $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'] ?? 'Hospital Branch'));
        $generatedAt = (string)($summary['generated_at'] ?? date('Y-m-d H:i:s'));
        $displayDate = substr($generatedAt, 0, 10);
        $dailyRevenue = (float)($summary['daily_revenue'] ?? 0.0);

        $xml = $this->setMergedRangeValue($xml, 'A1:C2', $hospitalCode . "\nExecutive Analytics", 'str');
        $xml = $this->setMergedRangeValue($xml, 'D1:I2', "Top Payers by Daily Revenue\nExecutive Distribution", 'str');
        $xml = $this->setMergedRangeValue($xml, 'J1:L2', $displayDate . "\n" . ($hospitalName !== '' ? $hospitalName : 'Hospital Branch'), 'str');

        $top10 = array_slice($companies, 0, 10);
        for ($i = 0; $i < 10; $i++) {
            $row = 4 + $i;
            $company = isset($top10[$i]) && is_array($top10[$i]) ? $top10[$i] : null;
            if (is_array($company)) {
                $revenue = (float)($company['daily_revenue'] ?? 0.0);
                $share = $dailyRevenue > 0 ? $revenue / $dailyRevenue : 0.0;
                $xml = $this->setCellValue($xml, 'N' . $row, (string)($company['financial_class'] ?? ''), 'str');
                $xml = $this->setCellValue($xml, 'O' . $row, $revenue, 'n');
                $xml = $this->setCellValue($xml, 'P' . $row, $share, 'n');
                $xml = $this->setCellValue($xml, 'Q' . $row, (int)($company['patients'] ?? 0), 'n');
            } else {
                foreach (['N','O','P','Q'] as $col) { $xml = $this->clearCellValue($xml, $col . $row); }
            }
        }

        $topPayer = isset($top10[0]) && is_array($top10[0]) ? (string)($top10[0]['financial_class'] ?? '') : 'N/A';
        $xml = $this->setMergedRangeValue($xml, 'A24:D27', $dailyRevenue, 'n');
        $xml = $this->setMergedRangeValue($xml, 'E24:H27', $topPayer, 'str');
        $xml = $this->setMergedRangeValue($xml, 'I24:L27', count($companies), 'n');

        for ($i = 0; $i < 5; $i++) {
            $row = 31 + $i;
            $company = isset($companies[$i]) && is_array($companies[$i]) ? $companies[$i] : null;
            if (is_array($company)) {
                $revenue = (float)($company['daily_revenue'] ?? 0.0);
                $share = $dailyRevenue > 0 ? $revenue / $dailyRevenue : 0.0;
                $xml = $this->setCellValue($xml, 'A' . $row, $i + 1, 'n');
                $xml = $this->setCellValue($xml, 'B' . $row, (string)($company['financial_class'] ?? ''), 'str');
                $xml = $this->setCellValue($xml, 'C' . $row, $revenue, 'n');
                $xml = $this->setCellValue($xml, 'D' . $row, $share, 'n');
            } else {
                foreach (['A','B','C','D'] as $col) { $xml = $this->clearCellValue($xml, $col . $row); }
            }
        }

        return $xml;
    }

    /** @param array<string,mixed> $report */
    private function populateRevenueChartCache(string $xml, array $report): string
    {
        $companies = is_array($report['companies'] ?? null) ? array_slice($report['companies'], 0, 10) : [];
        $labels = [];
        $values = [];
        foreach ($companies as $company) {
            if (!is_array($company)) { continue; }
            $labels[] = (string)($company['financial_class'] ?? '');
            $values[] = (float)($company['daily_revenue'] ?? 0.0);
        }
        $strCache = '<c:ptCount val="' . count($labels) . '"/>';
        foreach ($labels as $index => $label) {
            $strCache .= '<c:pt idx="' . $index . '"><c:v>' . $this->xml((string)$label) . '</c:v></c:pt>';
        }
        $numCache = '<c:formatCode>#,##0.00</c:formatCode><c:ptCount val="' . count($values) . '"/>';
        foreach ($values as $index => $value) {
            $numCache .= '<c:pt idx="' . $index . '"><c:v>' . $this->xmlNumber((float)$value) . '</c:v></c:pt>';
        }
        $xml = preg_replace('/<c:strCache>.*?<\/c:strCache>/s', '<c:strCache>' . $strCache . '</c:strCache>', $xml, 1) ?? $xml;
        $xml = preg_replace('/<c:numCache>.*?<\/c:numCache>/s', '<c:numCache>' . $numCache . '</c:numCache>', $xml, 1) ?? $xml;
        return $xml;
    }

    private function ensureFullCalculationOnLoad(string $xml): string
    {
        if (preg_match('/<calcPr\b[^>]*\/>/', $xml) === 1) {
            return preg_replace('/<calcPr\b[^>]*\/>/', '<calcPr calcMode="auto" fullCalcOnLoad="1" forceFullCalc="1"/>', $xml, 1) ?? $xml;
        }
        if (preg_match('/<x:calcPr\b[^>]*\/>/', $xml) === 1) {
            return preg_replace('/<x:calcPr\b[^>]*\/>/', '<x:calcPr calcMode="auto" fullCalcOnLoad="1" forceFullCalc="1"/>', $xml, 1) ?? $xml;
        }
        $tag = strpos($xml, '<x:workbook') !== false ? '</x:workbook>' : '</workbook>';
        $calc = strpos($xml, '<x:workbook') !== false ? '<x:calcPr calcMode="auto" fullCalcOnLoad="1" forceFullCalc="1"/>' : '<calcPr calcMode="auto" fullCalcOnLoad="1" forceFullCalc="1"/>';
        return str_replace($tag, $calc . $tag, $xml);
    }

    /** @return array<int,string> */
    private function extractRows(string $xml): array
    {
        $rows = [];
        if (preg_match_all('/<x:row\b[^>]*\br="(\d+)"[^>]*>.*?<\/x:row>/s', $xml, $matches, PREG_SET_ORDER)) {
            foreach ($matches as $match) { $rows[(int)$match[1]] = $match[0]; }
        }
        if ($rows === []) { throw new RuntimeException('Master workbook worksheet contains no rows.'); }
        return $rows;
    }

    /** @param array<int,string> $rows */
    private function requiredRow(array $rows, int $rowNumber): string
    {
        if (!isset($rows[$rowNumber])) { throw new RuntimeException('Master workbook prototype row missing: ' . $rowNumber); }
        return $rows[$rowNumber];
    }

    /** @param array<int,string> $rows */
    private function replaceSheetDataRows(string $xml, array $rows): string
    {
        $content = implode('', $rows);
        $updated = preg_replace('/<x:sheetData>.*?<\/x:sheetData>/s', '<x:sheetData>' . $content . '</x:sheetData>', $xml, 1);
        if (!is_string($updated) || $updated === $xml) { throw new RuntimeException('Unable to replace master workbook sheet data.'); }
        return $updated;
    }

    private function cloneRowXml(string $rowXml, int $newRow): string
    {
        $oldRow = 0;
        if (preg_match('/<x:row\b[^>]*\br="(\d+)"/', $rowXml, $m) !== 1) {
            throw new RuntimeException('Invalid master workbook prototype row.');
        }
        $oldRow = (int)$m[1];
        $rowXml = preg_replace_callback('/(<x:row\b[^>]*\br=")' . $oldRow . '("[^>]*>)/', static function (array $m) use ($newRow): string {
            return $m[1] . $newRow . $m[2];
        }, $rowXml, 1) ?? $rowXml;
        $rowXml = preg_replace_callback('/(<x:c\b[^>]*\br=")([A-Z]+)' . $oldRow . '("[^>]*>)/', static function (array $m) use ($newRow): string {
            return $m[1] . $m[2] . $newRow . $m[3];
        }, $rowXml) ?? $rowXml;
        return $rowXml;
    }

    private function setRowCellValue(string $rowXml, string $column, int $row, $value, string $type): string
    {
        return $this->replaceCellInXml($rowXml, strtoupper($column) . $row, $value, $type);
    }

    private function clearRowCellValue(string $rowXml, string $column, int $row): string
    {
        return $this->replaceCellInXml($rowXml, strtoupper($column) . $row, '', 'str');
    }

    private function setCellValue(string $xml, string $ref, $value, string $type): string
    {
        return $this->replaceCellInXml($xml, strtoupper($ref), $value, $type);
    }

    private function clearCellValue(string $xml, string $ref): string
    {
        return $this->replaceCellInXml($xml, strtoupper($ref), '', 'str');
    }

    private function replaceCellInXml(string $xml, string $ref, $value, string $type): string
    {
        $quoted = preg_quote($ref, '/');
        $pattern = '/<x:c\b([^>]*\br="' . $quoted . '"[^>]*)>(.*?)<\/x:c>/s';
        if (preg_match($pattern, $xml, $m) !== 1) {
            $selfClosing = '/<x:c\b([^>]*\br="' . $quoted . '"[^>]*)\/>/s';
            if (preg_match($selfClosing, $xml, $m) !== 1) {
                throw new RuntimeException('Master workbook cell missing: ' . $ref);
            }
            $attrs = $m[1];
            $replacement = $this->buildCellXml($attrs, $ref, $value, $type);
            return preg_replace($selfClosing, $replacement, $xml, 1) ?? $xml;
        }
        $attrs = $m[1];
        $replacement = $this->buildCellXml($attrs, $ref, $value, $type);
        return preg_replace($pattern, $replacement, $xml, 1) ?? $xml;
    }

    private function buildCellXml(string $attrs, string $ref, $value, string $type): string
    {
        $attrs = preg_replace('/\s+t="[^"]*"/', '', $attrs) ?? $attrs;
        $attrs = preg_replace('/\s+r="[^"]*"/', '', $attrs) ?? $attrs;
        $attrs = preg_replace('/\/\s*$/', '', rtrim($attrs)) ?? rtrim($attrs);
        $cellType = $type === 'n' ? 'n' : 'str';
        $text = $cellType === 'n' ? $this->xmlNumber((float)$value) : $this->xml((string)$value);
        return '<x:c r="' . $ref . '"' . $attrs . ' t="' . $cellType . '"><x:v>' . $text . '</x:v></x:c>';
    }

    private function setMergedRangeValue(string $xml, string $range, $value, string $type): string
    {
        $start = explode(':', strtoupper($range), 2)[0];
        return $this->setCellValue($xml, $start, $value, $type);
    }

    /** @param array<int,string> $newRefs */
    private function replaceMergeCellsFromRow(string $xml, int $firstRow, array $newRefs): string
    {
        if (preg_match('/<x:mergeCells>(.*?)<\/x:mergeCells>/s', $xml, $m) !== 1) {
            throw new RuntimeException('Master workbook mergeCells contract missing.');
        }
        $existing = [];
        if (preg_match_all('/<x:mergeCell ref="([^"]+)"\s*\/>/', $m[1], $mm)) {
            foreach ($mm[1] as $ref) {
                $start = explode(':', $ref, 2)[0];
                [, $row] = $this->splitCellRef($start);
                if ($row < $firstRow) { $existing[] = $ref; }
            }
        }
        $all = array_merge($existing, $newRefs);
        $body = '';
        foreach ($all as $ref) { $body .= '<x:mergeCell ref="' . $this->xml($ref) . '" />'; }
        return preg_replace('/<x:mergeCells>.*?<\/x:mergeCells>/s', '<x:mergeCells>' . $body . '</x:mergeCells>', $xml, 1) ?? $xml;
    }

    private function updateConditionalFormattingRange(string $xml, string $range): string
    {
        $xml = preg_replace('/(<x:conditionalFormatting\b[^>]*\bsqref=")[^"]*(")/', '$1' . $range . '$2', $xml) ?? $xml;
        $xml = preg_replace('/(<xm:sqref>).*?(<\/xm:sqref>)/s', '$1' . $range . '$2', $xml) ?? $xml;
        return $xml;
    }

    /** @return array<int,string> */
    private function summaryMergeRefs(int $row): array
    {
        return ['A' . $row . ':F' . $row, 'G' . $row . ':J' . $row, 'K' . $row . ':M' . $row];
    }

    /** @return array{0:string,1:int} */
    private function splitCellRef(string $ref): array
    {
        if (preg_match('/^([A-Z]+)(\d+)$/', strtoupper($ref), $m) !== 1) {
            throw new RuntimeException('Invalid cell reference in master workbook: ' . $ref);
        }
        return [$m[1], (int)$m[2]];
    }

    private function columnIndex(string $column): int
    {
        $column = strtoupper($column);
        $value = 0;
        for ($i = 0, $length = strlen($column); $i < $length; $i++) {
            $value = ($value * 26) + (ord($column[$i]) - 64);
        }
        return $value;
    }

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

    /** @param array<int,mixed> $patients */
    private function averageLos(array $patients): float
    {
        $sum = 0.0;
        $count = 0;
        foreach ($patients as $patient) {
            if (!is_array($patient)) { continue; }
            $los = (float)($patient['total_los'] ?? $patient['los'] ?? 0);
            if ($los > 0) { $sum += $los; $count++; }
        }
        return $count > 0 ? $sum / $count : 0.0;
    }

    private function excelDateSerial($value): float
    {
        if (is_numeric($value)) { return (float)$value; }
        $text = trim((string)$value);
        if ($text === '') { return 0.0; }
        $timestamp = strtotime($text);
        if ($timestamp === false) { return 0.0; }
        return ((int)floor($timestamp / 86400)) + 25569;
    }

    private function excelTimeSerial($value): float
    {
        if (is_numeric($value)) { return (float)$value; }
        $text = trim((string)$value);
        if ($text === '') { return 0.0; }
        if (preg_match('/^(\d{1,2}):(\d{2})(?::(\d{2}))?$/', $text, $m) === 1) {
            $seconds = ((int)$m[1] * 3600) + ((int)$m[2] * 60) + (isset($m[3]) ? (int)$m[3] : 0);
            return $seconds / 86400;
        }
        $timestamp = strtotime($text);
        if ($timestamp === false) { return 0.0; }
        return (((int)date('G', $timestamp) * 3600) + ((int)date('i', $timestamp) * 60) + (int)date('s', $timestamp)) / 86400;
    }

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

    private function xmlNumber(float $value): string
    {
        if (!is_finite($value)) { return '0'; }
        $text = rtrim(rtrim(sprintf('%.12F', $value), '0'), '.');
        return $text === '-0' || $text === '' ? '0' : $text;
    }
}
