<?php

declare(strict_types=1);

namespace App\Services;

use DOMDocument;
use DOMElement;
use DOMNode;
use DOMXPath;
use RuntimeException;

/**
 * Generates the approved twelve-sheet workbook by updating the exact golden
 * OOXML package. Styling, chart objects, drawings, theme and sheet order are
 * retained from the approved report.
 */
final class DiarApprovedWorkbookExportService
{
    private const NS = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';

    /** @param array<string,mixed> $report */
    public function create(string $path, array $report): void
    {
        $template = base_path('resources/templates/diar/MNH_Review_Report_APPROVED.xlsx');
        if (!is_file($template)) {
            throw new RuntimeException('DIAR_APPROVED_WORKBOOK_TEMPLATE_MISSING');
        }
        $sha = strtolower((string)hash_file('sha256', $template));
        if ($sha !== DiarReportCatalog::APPROVED_WORKBOOK_SHA256) {
            throw new RuntimeException('DIAR_APPROVED_WORKBOOK_SHA_MISMATCH');
        }

        $entries = DiarOoxmlPackage::read($template);
        $sheetRows = $this->sheetRows($report);
        $sheetUpdates = $this->sheetUpdates($report);
        $sheetColumns = [2 => 'S', 3 => 'T', 4 => 'Z', 5 => 'W', 6 => 'M', 7 => 'L', 8 => 'M', 9 => 'K', 11 => 'AH'];

        foreach ($sheetUpdates as $sheetNo => $updates) {
            $key = 'xl/worksheets/sheet' . $sheetNo . '.xml';
            if (!isset($entries[$key])) {
                throw new RuntimeException('DIAR_APPROVED_SHEET_XML_MISSING|' . $sheetNo);
            }
            $entries[$key] = $this->updateCells($entries[$key], $updates);
        }
        foreach ($sheetRows as $sheetNo => $rows) {
            $key = 'xl/worksheets/sheet' . $sheetNo . '.xml';
            $entries[$key] = $this->replaceDataRows($entries[$key], $rows, (string)$sheetColumns[$sheetNo]);
        }

        $entries['xl/charts/chart1.xml'] = $this->chartRange($entries['xl/charts/chart1.xml'] ?? '', 'Statistical Basis', 33, 35, 'A', 'B');
        $dailyLast = max(5, 4 + count($sheetRows[2]));
        $storeLast = max(5, 4 + count($sheetRows[6]));
        $movementLast = max(5, 4 + count($sheetRows[7]));
        $entries['xl/charts/chart2.xml'] = $this->chartRange($entries['xl/charts/chart2.xml'] ?? '', 'Daily Distribution', 5, $dailyLast, 'A', 'C');
        $entries['xl/charts/chart3.xml'] = $this->chartRange($entries['xl/charts/chart3.xml'] ?? '', 'Store Analysis', 5, $storeLast, 'B', 'C');
        $entries['xl/charts/chart4.xml'] = $this->chartRange($entries['xl/charts/chart4.xml'] ?? '', 'Movement Type Analysis', 5, $movementLast, 'B', 'D');

        if (isset($entries['xl/workbook.xml'])) {
            $entries['xl/workbook.xml'] = $this->forceRecalculation($entries['xl/workbook.xml']);
        }
        if (isset($entries['docProps/core.xml'])) {
            $entries['docProps/core.xml'] = preg_replace(
                '~<dcterms:modified[^>]*>.*?</dcterms:modified>~s',
                '<dcterms:modified xsi:type="dcterms:W3CDTF">' . gmdate('Y-m-d\TH:i:s\Z') . '</dcterms:modified>',
                $entries['docProps/core.xml']
            ) ?: $entries['docProps/core.xml'];
        }

        DiarOoxmlPackage::write($path, $entries);
    }

    /** @param array<string,mixed> $report @return array<int,array<string,mixed>> */
    private function sheetUpdates(array $report): array
    {
        $summary = (array)($report['summary'] ?? []);
        $scope = $this->scopeLabel($report);
        $month = (string)($report['selected_month'] ?? '');
        $cases = max(1, (int)($summary['case_count'] ?? $summary['detected_count'] ?? 0));
        $critical = (int)($summary['critical_count'] ?? 0);
        $high = (int)($summary['high_count'] ?? 0);
        $medium = (int)($summary['medium_count'] ?? 0);
        $groups = (int)($summary['movement_groups_reviewed'] ?? 0);
        $cost = (float)($summary['inventory_cost_abs'] ?? 0);
        $days = (int)($summary['completed_days'] ?? count((array)($report['daily_distribution'] ?? [])));
        $insights = array_values((array)($report['insights'] ?? []));
        $recommendations = array_values((array)($report['recommendations'] ?? []));
        $topStores = array_values((array)($report['top_stores'] ?? []));
        $topMovements = array_values((array)($report['top_movements'] ?? []));
        $basis = (array)($report['statistical_basis'] ?? []);
        $history = (array)($basis['history'] ?? []);
        $priority = (array)($basis['priority'] ?? []);
        $updates = [];

        $updates[1] = [
            'A1' => 'DIAR Review Report — Priority Drug Movements',
            'A3' => $scope . ' | Frozen month ' . $month . ' | Each movement compared with the preceding 365 days | Stores 56 and 57 excluded | All financial values at inventory cost',
            'A6' => $cases, 'D6' => $critical, 'G6' => $high, 'J6' => $medium,
            'A11' => $days, 'D11' => $groups, 'G11' => $groups > 0 ? $cases / $groups : 0, 'J11' => $cost,
            'A16' => (string)($insights[0] ?? ''), 'A17' => (string)($insights[1] ?? ''),
            'A18' => (string)($insights[2] ?? ''), 'A19' => (string)($insights[4] ?? ''),
            'A20' => 'The report covers ' . (int)($summary['item_count'] ?? 0) . ' items across ' . (int)($summary['store_count'] ?? 0) . ' governed store scopes. Stores 56 and 57 are excluded.',
            'A21' => (string)($insights[5] ?? ''),
            'A24' => '1. ' . (string)($recommendations[0] ?? ''), 'A25' => '2. ' . (string)($recommendations[1] ?? ''),
            'A26' => '3. ' . (string)($recommendations[2] ?? ''), 'A27' => '4. ' . (string)($recommendations[3] ?? ''),
            'A28' => '5. ' . (string)($recommendations[4] ?? ''),
            'A77' => 'Financial-value definitions: Unit Cost is the inventory cost recorded at movement time. Line Value at Cost equals Line Quantity × Unit Cost. Case Value at Cost is the sum of associated movement values shown as positive amounts for scale. It is neither a selling-price value nor a confirmed loss.',
        ];
        for ($i = 0; $i < 5; $i++) {
            $row = 71 + $i;
            $store = (array)($topStores[$i] ?? []);
            $movement = (array)($topMovements[$i] ?? []);
            $updates[1]['A' . $row] = (string)($store['store'] ?? '');
            $updates[1]['B' . $row] = (int)($store['cases'] ?? 0);
            $updates[1]['C' . $row] = $cases > 0 ? (int)($store['cases'] ?? 0) / $cases : 0;
            $updates[1]['D' . $row] = (int)($store['critical'] ?? 0);
            $updates[1]['E' . $row] = (float)($store['cost_value'] ?? 0);
            $updates[1]['F' . $row] = (int)($store['items'] ?? 0);
            $updates[1]['G' . $row] = (string)($movement['movement_type'] ?? '');
            $updates[1]['H' . $row] = (int)($movement['cases'] ?? 0);
            $updates[1]['I' . $row] = $cases > 0 ? (int)($movement['cases'] ?? 0) / $cases : 0;
            $updates[1]['J' . $row] = (int)($movement['critical'] ?? 0);
            $updates[1]['K' . $row] = (float)($movement['cost_value'] ?? 0);
            $updates[1]['L' . $row] = (int)($movement['stores'] ?? 0);
        }

        $updates[10] = [
            'A1' => 'Statistical Basis Used to Identify Cases',
            'A3' => $scope . ' | Frozen month ' . $month . ' | Plain-language explanation of the 365-day comparison and interpretation.',
            'A6' => 365, 'D6' => $groups, 'G6' => $cases, 'J6' => $days,
            'B26' => (int)($history['Strong history'] ?? 0), 'C26' => $cases > 0 ? (int)($history['Strong history'] ?? 0) / $cases : 0,
            'B27' => (int)($history['Limited history'] ?? 0), 'C27' => $cases > 0 ? (int)($history['Limited history'] ?? 0) / $cases : 0,
            'B28' => (int)($history['Sparse activity'] ?? 0), 'C28' => $cases > 0 ? (int)($history['Sparse activity'] ?? 0) / $cases : 0,
            'B29' => (int)($history['No prior activity'] ?? 0), 'C29' => $cases > 0 ? (int)($history['No prior activity'] ?? 0) / $cases : 0,
            'B33' => (int)($priority['CRITICAL']['cases'] ?? $critical), 'C33' => $cases > 0 ? (int)($priority['CRITICAL']['cases'] ?? $critical) / $cases : 0, 'D33' => (float)($priority['CRITICAL']['cost'] ?? 0),
            'B34' => (int)($priority['HIGH']['cases'] ?? $high), 'C34' => $cases > 0 ? (int)($priority['HIGH']['cases'] ?? $high) / $cases : 0, 'D34' => (float)($priority['HIGH']['cost'] ?? 0),
            'B35' => (int)($priority['MEDIUM']['cases'] ?? $medium), 'C35' => $cases > 0 ? (int)($priority['MEDIUM']['cases'] ?? $medium) / $cases : 0, 'D35' => (float)($priority['MEDIUM']['cost'] ?? 0),
            'B44' => 'Line Quantity × Unit Cost. This relationship is preserved for every exported movement-detail row.',
            'B46' => 'SAR ' . number_format($cost, 2) . ' at inventory cost. It is not revenue, selling price or a confirmed loss.',
        ];
        foreach (array_values((array)($basis['indicators'] ?? [])) as $i => $row) {
            if ($i > 4) break;
            $r = 18 + $i;
            $updates[10]['A' . $r] = (string)($row['indicator'] ?? '');
            $updates[10]['B' . $r] = (string)($row['meaning'] ?? '');
            $updates[10]['C' . $r] = (int)($row['above_p95'] ?? 0);
            $updates[10]['D' . $r] = (int)($row['above_p99'] ?? 0);
            $updates[10]['E' . $r] = (int)($row['above_max'] ?? 0);
            $updates[10]['F' . $r] = (string)($row['interpretation'] ?? '');
        }

        $updates[12] = [
            'A1' => 'Report Methodology and Interpretation Controls',
            'A3' => 'A plain-language reference for management and reviewers covering scope, comparison method and interpretation limits.',
            'A5' => 'Important: Appearance in the report means that a movement deserves review compared with prior behaviour; it is not proof of manipulation, theft or financial loss.',
            'B9' => $scope . '.',
            'B10' => 'Frozen month ' . $month . ' with ' . $days . ' completed review days.',
            'B11' => 'The 365 days preceding each review day; the review day itself is excluded from the comparison.',
            'B12' => 'Drug items only, based on ITEMSTORETYPE = 2.',
            'B13' => 'Stores 56 and 57 are excluded from the comparison and final report.',
            'B14' => 'Each item is compared within the same hospital, source database, branch, store, raw unit, movement code and movement effect.',
            'B15' => 'Quantity, inventory-cost value, transaction count, largest movement and time since the last similar activity.',
            'B16' => number_format($groups) . ' daily movement groups were reviewed across ' . $days . ' completed days.',
            'B17' => number_format($cases) . ' cases require review: ' . number_format($critical) . ' critical, ' . number_format($high) . ' high and ' . number_format($medium) . ' medium.',
            'B18' => 'All values are calculated at inventory cost. Unit cost is the cost recorded at movement time, and line value equals line quantity multiplied by unit cost.',
            'B19' => 'The total of SAR ' . number_format($cost, 2) . ' represents financial scale at inventory cost; it is not selling price or a confirmed loss.',
            'B20' => 'Mass inventory-count and adjustment activity should be reviewed as one operational event before individual exceptions are separated.',
            'B21' => 'A user identifier supports tracing movements, documents and access rights; it does not imply responsibility or misconduct.',
            'B22' => 'Begin with critical cases, then higher-cost cases, followed by cases linked to mass events or unusual repetition.',
        ];

        foreach ([2 => 'Daily Distribution', 3 => 'Critical Cases', 4 => 'All Cases', 5 => 'Movement Details', 6 => 'Store Analysis', 7 => 'Movement Type Analysis', 8 => 'Item Analysis', 9 => 'User Associations', 11 => 'Statistical Comparisons'] as $sheetNo => $name) {
            $updates[$sheetNo] = [
                'A1' => $name,
                'A3' => $scope . ' | Frozen month ' . $month . ' | Snapshot-bound, read-only evidence.',
            ];
        }
        return $updates;
    }

    /** @param array<string,mixed> $report @return array<int,array<int,array<int,mixed>>> */
    private function sheetRows(array $report): array
    {
        $rows = [];
        foreach ((array)($report['daily_distribution'] ?? []) as $r) {
            $rows[2][] = [
                $r['date'] ?? '', $r['movement_groups_reviewed'] ?? 0, $r['cases'] ?? 0, $r['critical'] ?? 0,
                $r['high'] ?? 0, $r['medium'] ?? 0, $r['case_share'] ?? 0, $r['reference_records'] ?? 0,
                $r['strong_history'] ?? 0, $r['limited_history'] ?? 0, $r['sparse_activity'] ?? 0,
                $r['no_prior_activity'] ?? 0, $r['excluded_cases'] ?? 0, $r['suppressed'] ?? 0,
                $r['inventory_count_cases'] ?? 0, $r['adjustment_cases'] ?? 0, $r['patient_dispensing'] ?? 0,
                $r['cost_value'] ?? 0, $r['operational_note'] ?? '',
            ];
        }
        foreach ((array)($report['critical_cases'] ?? []) as $r) {
            $rows[3][] = [
                $r['rank'] ?? 0, $r['case_id'] ?? '', $r['calendar_day'] ?? '', $r['score'] ?? 0,
                $r['alert_threshold'] ?? 0, $r['amount_above_threshold'] ?? 0, $r['confidence'] ?? '',
                $r['history_strength'] ?? '', $r['store_display'] ?? '', $r['item_display'] ?? '',
                $r['unit_display'] ?? '', $r['movement_display'] ?? '', $r['direction'] ?? '', $r['quantity'] ?? 0,
                $r['case_value'] ?? 0, $r['transactions'] ?? 0, $r['lines'] ?? 0, $r['reason'] ?? '',
                $r['recommended_action'] ?? '', $r['associated_users'] ?? '',
            ];
        }
        foreach ((array)($report['cases'] ?? []) as $r) {
            $rows[4][] = [
                $r['rank'] ?? 0, $r['case_id'] ?? '', $r['calendar_day'] ?? '', $r['priority'] ?? '', $r['score'] ?? 0,
                $r['alert_threshold'] ?? 0, $r['amount_above_threshold'] ?? 0, $r['confidence'] ?? '', $r['history_strength'] ?? '',
                $r['store_display'] ?? '', $r['item_display'] ?? '', $r['unit_display'] ?? '', $r['movement_display'] ?? '',
                $r['direction'] ?? '', $r['quantity'] ?? 0, $r['case_value'] ?? 0, $r['transactions'] ?? 0,
                $r['lines'] ?? 0, $r['reason'] ?? '', $r['review_context'] ?? '', $r['recommended_action'] ?? '',
                $r['associated_users'] ?? '', $r['review_status'] ?? '', $r['review_owner'] ?? '',
                $r['review_result'] ?? '', $r['management_notes'] ?? '',
            ];
        }
        foreach ((array)($report['movement_details'] ?? []) as $r) {
            $rows[5][] = [
                $r['case_id'] ?? '', $r['case_day'] ?? '', $r['priority'] ?? '', $r['score'] ?? 0,
                $r['store_display'] ?? '', $r['item_display'] ?? '', $r['unit_display'] ?? '', $r['movement_display'] ?? '',
                $r['storetranseffect'] ?? '', $r['direction'] ?? '', $r['movement_number'] ?? '', $r['line_number'] ?? '',
                $r['document_number'] ?? '', $r['movement_datetime'] ?? '', $r['document_date'] ?? '', $r['user_id'] ?? '',
                $r['user_description'] ?? '', $r['line_quantity'] ?? 0, $r['requested_quantity'] ?? 0,
                $r['unit_cost'] ?? 0, $r['line_value'] ?? 0, $r['review_context'] ?? '', $r['reason'] ?? '',
            ];
        }
        foreach ((array)($report['store_analysis'] ?? []) as $r) {
            $rows[6][] = [
                $r['store_code'] ?? '', $r['store'] ?? '', $r['cases'] ?? 0, $r['critical'] ?? 0, $r['high'] ?? 0,
                $r['medium'] ?? 0, $r['share'] ?? 0, $r['cost_value'] ?? 0, $r['quantity'] ?? 0,
                $r['movement_lines'] ?? 0, $r['items'] ?? 0, $r['users'] ?? 0, $r['dominant_movement'] ?? '',
            ];
        }
        foreach ((array)($report['movement_analysis'] ?? []) as $r) {
            $rows[7][] = [
                $r['movement_code'] ?? '', $r['movement_type'] ?? '', $r['direction'] ?? '', $r['cases'] ?? 0,
                $r['critical'] ?? 0, $r['high'] ?? 0, $r['medium'] ?? 0, $r['share'] ?? 0,
                $r['cost_value'] ?? 0, $r['stores'] ?? 0, $r['items'] ?? 0, $r['mass_event_cases'] ?? 0,
            ];
        }
        foreach ((array)($report['item_analysis'] ?? []) as $r) {
            $rows[8][] = [
                $r['item_code'] ?? '', $r['item'] ?? '', $r['cases'] ?? 0, $r['critical'] ?? 0, $r['high'] ?? 0,
                $r['medium'] ?? 0, $r['stores'] ?? 0, $r['movement_types'] ?? 0, $r['quantity'] ?? 0,
                $r['cost_value'] ?? 0, $r['highest_score'] ?? 0, $r['first_day'] ?? '', $r['last_day'] ?? '',
            ];
        }
        foreach ((array)($report['user_associations'] ?? []) as $r) {
            $rows[9][] = [
                (($report['scope_code'] ?? '') === 'ALL' ? ($r['site_code'] ?? '') . ' · ' : '') . ($r['user'] ?? ''),
                $r['unique_cases'] ?? 0, $r['critical'] ?? 0, $r['high'] ?? 0, $r['medium'] ?? 0,
                $r['movement_lines'] ?? 0, $r['line_value'] ?? 0, $r['stores'] ?? 0, $r['items'] ?? 0,
                $r['first_day'] ?? '', $r['last_day'] ?? '',
            ];
        }
        foreach ((array)($report['statistical_comparisons'] ?? []) as $r) {
            $rows[11][] = [
                $r['case_id'] ?? '', $r['date'] ?? '', $r['priority'] ?? '', $r['score'] ?? 0, $r['store'] ?? '',
                $r['item'] ?? '', $r['movement_type'] ?? '', $r['history_strength'] ?? '', $r['qty_obs'] ?? null,
                $r['qty_typical'] ?? null, $r['qty_p95'] ?? null, $r['qty_p99'] ?? null, $r['qty_max'] ?? null,
                $r['qty_contribution'] ?? null, $r['value_obs'] ?? null, $r['value_typical'] ?? null,
                $r['value_p95'] ?? null, $r['value_p99'] ?? null, $r['value_max'] ?? null, $r['value_contribution'] ?? null,
                $r['txn_obs'] ?? null, $r['txn_typical'] ?? null, $r['txn_p95'] ?? null, $r['txn_p99'] ?? null,
                $r['txn_max'] ?? null, $r['txn_contribution'] ?? null, $r['line_obs'] ?? null, $r['line_typical'] ?? null,
                $r['line_p95'] ?? null, $r['line_p99'] ?? null, $r['line_max'] ?? null, $r['line_contribution'] ?? null,
                $r['days_since_activity'] ?? null, $r['dormancy_contribution'] ?? null,
            ];
        }
        foreach ([2,3,4,5,6,7,8,9,11] as $sheetNo) {
            if (!isset($rows[$sheetNo])) $rows[$sheetNo] = [];
        }
        return $rows;
    }

    /** @param array<string,mixed> $updates */
    private function updateCells(string $xml, array $updates): string
    {
        $doc = $this->load($xml);
        $xpath = $this->xpath($doc);
        foreach ($updates as $ref => $value) {
            $cell = $xpath->query('//m:c[@r="' . $ref . '"]')->item(0);
            if (!$cell instanceof DOMElement) {
                continue;
            }
            $this->setCellValue($doc, $cell, $value);
        }
        return $doc->saveXML() ?: $xml;
    }

    /** @param array<int,array<int,mixed>> $rows */
    private function replaceDataRows(string $xml, array $rows, string $endColumn): string
    {
        $doc = $this->load($xml);
        $xpath = $this->xpath($doc);
        $sheetData = $xpath->query('//m:sheetData')->item(0);
        $template = $xpath->query('//m:row[@r="5"]')->item(0);
        if (!$sheetData instanceof DOMElement || !$template instanceof DOMElement) {
            throw new RuntimeException('DIAR_APPROVED_SHEET_ROW_TEMPLATE_MISSING');
        }
        $remove = [];
        foreach ($xpath->query('//m:sheetData/m:row') as $row) {
            if ($row instanceof DOMElement && (int)$row->getAttribute('r') >= 5) $remove[] = $row;
        }
        foreach ($remove as $row) $sheetData->removeChild($row);

        $rowNumber = 5;
        foreach ($rows as $values) {
            /** @var DOMElement $row */
            $row = $template->cloneNode(true);
            $row->setAttribute('r', (string)$rowNumber);
            $cells = [];
            foreach ($row->getElementsByTagNameNS(self::NS, 'c') as $cellNode) {
                if ($cellNode instanceof DOMElement) $cells[] = $cellNode;
            }
            foreach ($cells as $index => $cell) {
                $column = $this->columnName($index + 1);
                $cell->setAttribute('r', $column . $rowNumber);
                $this->setCellValue($doc, $cell, $values[$index] ?? null);
            }
            $sheetData->appendChild($row);
            $rowNumber++;
        }
        if ($rows === []) {
            $row = $template->cloneNode(true);
            $row->setAttribute('r', '5');
            foreach ($row->getElementsByTagNameNS(self::NS, 'c') as $cell) {
                if ($cell instanceof DOMElement) $this->setCellValue($doc, $cell, null);
            }
            $sheetData->appendChild($row);
        }
        $last = max(5, $rowNumber - 1);
        $dimension = $xpath->query('//m:dimension')->item(0);
        if ($dimension instanceof DOMElement) $dimension->setAttribute('ref', 'A1:' . $endColumn . $last);
        $autoFilter = $xpath->query('//m:autoFilter')->item(0);
        if ($autoFilter instanceof DOMElement) $autoFilter->setAttribute('ref', 'A4:' . $endColumn . $last);
        return $doc->saveXML() ?: $xml;
    }

    private function setCellValue(DOMDocument $doc, DOMElement $cell, $value): void
    {
        $remove = [];
        foreach ($cell->childNodes as $child) {
            if ($child instanceof DOMElement && in_array($child->localName, ['v','is','f'], true)) $remove[] = $child;
        }
        foreach ($remove as $child) $cell->removeChild($child);
        if ($value === null || $value === '') {
            $cell->removeAttribute('t');
            return;
        }
        if (is_int($value) || is_float($value)) {
            $cell->removeAttribute('t');
            $v = $doc->createElementNS(self::NS, 'v');
            $v->appendChild($doc->createTextNode($this->numberText((float)$value)));
            $cell->appendChild($v);
            return;
        }
        $cell->setAttribute('t', 'inlineStr');
        $is = $doc->createElementNS(self::NS, 'is');
        $t = $doc->createElementNS(self::NS, 't');
        $text = (string)$value;
        if ($text !== trim($text)) $t->setAttribute('xml:space', 'preserve');
        $t->appendChild($doc->createTextNode($text));
        $is->appendChild($t);
        $cell->appendChild($is);
    }

    private function chartRange(string $xml, string $sheet, int $start, int $end, string $categoryColumn, string $valueColumn): string
    {
        if ($xml === '') return $xml;
        $escaped = str_replace("'", "''", $sheet);
        $category = "'" . $escaped . "'!\$" . $categoryColumn . '\$' . $start . ':\$' . $categoryColumn . '\$' . $end;
        $value = "'" . $escaped . "'!\$" . $valueColumn . '\$' . $start . ':\$' . $valueColumn . '\$' . $end;
        $formulas = [$category, $value];
        $index = 0;
        return preg_replace_callback('~<c:f>.*?</c:f>~s', static function () use (&$index, $formulas): string {
            $formula = $formulas[min($index, count($formulas) - 1)];
            $index++;
            return '<c:f>' . htmlspecialchars($formula, ENT_XML1 | ENT_COMPAT, 'UTF-8') . '</c:f>';
        }, $xml) ?: $xml;
    }

    private function forceRecalculation(string $xml): string
    {
        if (strpos($xml, '<calcPr') !== false) {
            return preg_replace('~<calcPr[^>]*/>~', '<calcPr calcId="191029" fullCalcOnLoad="1" forceFullCalc="1" calcMode="auto"/>', $xml) ?: $xml;
        }
        return str_replace('</workbook>', '<calcPr calcId="191029" fullCalcOnLoad="1" forceFullCalc="1" calcMode="auto"/></workbook>', $xml);
    }

    private function load(string $xml): DOMDocument
    {
        $doc = new DOMDocument('1.0', 'UTF-8');
        $doc->preserveWhiteSpace = false;
        $doc->formatOutput = false;
        if (!$doc->loadXML($xml, LIBXML_NONET | LIBXML_COMPACT)) {
            throw new RuntimeException('DIAR_APPROVED_XML_PARSE_FAILED');
        }
        return $doc;
    }

    private function xpath(DOMDocument $doc): DOMXPath
    {
        $xpath = new DOMXPath($doc);
        $xpath->registerNamespace('m', self::NS);
        return $xpath;
    }

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

    private function numberText(float $value): string
    {
        if (is_nan($value) || is_infinite($value)) return '0';
        $text = sprintf('%.12F', $value);
        return rtrim(rtrim($text, '0'), '.');
    }

    /** @param array<string,mixed> $report */
    private function scopeLabel(array $report): string
    {
        $scope = strtoupper((string)($report['scope_code'] ?? 'ALL'));
        if ($scope === 'MNH') return 'MNH Hospital';
        if ($scope === 'NJCH') return 'NJCH Hospital';
        return 'HMH Combined — MNH and NJCH';
    }
}
