<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Phase 10.2 — Executive KPI Summary Layer
 * ----------------------------------------
 * This repository refreshes and reads a tiny KPI summary table. The home page
 * reads this table only, so it stays fast while live/legacy aggregations are
 * moved to a scheduled refresh job.
 */
final class ExecutiveSummaryRepository
{
    /** @var string */
    private $hospitalCode;

    /** @var array<string,bool> */
    private $tableCache = [];

    /** @var array<string,bool> */
    private $columnCache = [];

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

    /**
     * Phase 10.3.5 — Operational Day Rollover Policy
     * ------------------------------------------------
     * Hospitals often do not treat 00:00 as the operational cut-off. The executive
     * dashboard therefore uses an Operational Day. Example with start hour = 7:
     *   00:00..06:59 => previous operational day
     *   07:00..23:59 => current operational day
     * This prevents the dashboard from showing empty daily KPIs immediately after midnight.
     *
     * @return array<string,mixed>
     */
    public function operationalDayInfo(): array
    {
        $hour = $this->operationalDayStartHour();
        $dayExpr = $this->operationalDayExpr();
        $startExpr = $this->operationalWindowStartExpr();
        $endExpr = $this->operationalWindowEndExpr();

        try {
            $row = Database::fetchOne($this->hospitalCode, "
                SELECT
                    TO_CHAR({$dayExpr}, 'YYYY-MM-DD') operational_date,
                    TO_CHAR({$startExpr}, 'YYYY-MM-DD HH24:MI') window_start,
                    TO_CHAR({$endExpr}, 'YYYY-MM-DD HH24:MI') window_end,
                    TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI') current_time,
                    CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')) < {$hour} THEN 1 ELSE 0 END is_rollover
                FROM dual
            ", []);

            if ($row) {
                return [
                    'start_hour' => $hour,
                    'operational_date' => (string)($row['operational_date'] ?? ''),
                    'window_start' => (string)($row['window_start'] ?? ''),
                    'window_end' => (string)($row['window_end'] ?? ''),
                    'current_time' => (string)($row['current_time'] ?? ''),
                    'is_rollover' => ((int)($row['is_rollover'] ?? 0)) === 1,
                ];
            }
        } catch (Throwable $e) {
            $this->log('operationalDayInfo', $e);
        }

        $now = time();
        $effective = $now - ($hour * 3600);
        $day = date('Y-m-d', $effective);
        return [
            'start_hour' => $hour,
            'operational_date' => $day,
            'window_start' => $day . ' ' . str_pad((string)$hour, 2, '0', STR_PAD_LEFT) . ':00',
            'window_end' => date('Y-m-d H:i', strtotime($day . ' +' . ($hour + 24) . ' hours')),
            'current_time' => date('Y-m-d H:i', $now),
            'is_rollover' => ((int)date('G', $now)) < $hour,
        ];
    }

    private function operationalDayStartHour(): int
    {
        $hour = (int)\env('EXECUTIVE_OPERATIONAL_DAY_START_HOUR', '7');
        if ($hour < 0) { return 0; }
        if ($hour > 23) { return 23; }
        return $hour;
    }

    private function operationalDayExpr(): string
    {
        $hour = $this->operationalDayStartHour();
        return "TRUNC(SYSDATE - ({$hour}/24))";
    }

    private function operationalWindowStartExpr(): string
    {
        $hour = $this->operationalDayStartHour();
        return "({$this->operationalDayExpr()} + ({$hour}/24))";
    }

    private function operationalWindowEndExpr(): string
    {
        return "({$this->operationalWindowStartExpr()} + 1)";
    }

    private function operationalRangeSql(string $dateExpression): string
    {
        return "{$dateExpression} >= {$this->operationalWindowStartExpr()} AND {$dateExpression} < {$this->operationalWindowEndExpr()}";
    }

    /** @return array<string,mixed>|null */
    public function latestForToday()
    {
        if (!$this->tableExists('DASH_EXEC_SUMMARY_KPI')) {
            return null;
        }

        $summaryDateExpr = $this->operationalDayExpr();
        $sql = "
            SELECT
                kpi_code,
                kpi_value,
                kpi_text,
                kpi_status,
                source_name,
                TO_CHAR(updated_at, 'YYYY-MM-DD HH24:MI') updated_at_text
            FROM dash_exec_summary_kpi
            WHERE hospital_code = :hospital_code
              AND summary_date = {$summaryDateExpr}
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'hospital_code' => strtoupper($this->hospitalCode),
        ]);

        if (!$rows) {
            return null;
        }

        $map = [];
        $lastUpdated = null;
        foreach ($rows as $row) {
            $code = strtolower((string)($row['kpi_code'] ?? ''));
            if ($code === '') { continue; }
            $map[$code] = $row;
            $ts = (string)($row['updated_at_text'] ?? '');
            if ($ts !== '' && ($lastUpdated === null || strcmp($ts, $lastUpdated) > 0)) {
                $lastUpdated = $ts;
            }
        }

        return [
            'rows' => $map,
            'last_updated' => $lastUpdated ?: date('Y-m-d H:i'),
            'is_stale' => $this->isStale($lastUpdated),
            'operational' => $this->operationalDayInfo(),
        ];
    }

    /** @return array<string,mixed> */
    public function refreshToday(): array
    {
        if (!$this->tableExists('DASH_EXEC_SUMMARY_KPI')) {
            throw new \RuntimeException('Summary table DASH_EXEC_SUMMARY_KPI does not exist. Run database/executive_kpi_summary_layer.sql first.');
        }

        $items = [];
        $this->addOperationalDayItems($items);
        $this->addFlowItems($items);
        $this->addFinanceItems($items);
        $this->addCapacityItems($items);
        $this->addClinicalItems($items);

        $summaryDateExpr = $this->operationalDayExpr();
        Database::execute($this->hospitalCode, "
            DELETE FROM dash_exec_summary_kpi
            WHERE hospital_code = :hospital_code
              AND summary_date = {$summaryDateExpr}
        ", ['hospital_code' => strtoupper($this->hospitalCode)]);

        foreach ($items as $item) {
            Database::execute($this->hospitalCode, "
                INSERT INTO dash_exec_summary_kpi
                    (hospital_code, summary_date, kpi_code, kpi_value, kpi_text, kpi_status, source_name, updated_at)
                VALUES
                    (:hospital_code, {$summaryDateExpr}, :kpi_code, :kpi_value, :kpi_text, :kpi_status, :source_name, SYSDATE)
            ", [
                'hospital_code' => strtoupper($this->hospitalCode),
                'kpi_code' => $item['code'],
                'kpi_value' => $item['value'],
                'kpi_text' => $item['text'],
                'kpi_status' => $item['status'],
                'source_name' => $item['source'],
            ]);
        }

        return [
            'hospital' => $this->hospitalCode,
            'inserted' => count($items),
            'updated_at' => date('Y-m-d H:i:s'),
            'operational' => $this->operationalDayInfo(),
        ];
    }

    /**
     * Phase 10.3.4 diagnostic helper.
     * Returns the raw bed units and the final three executive capacity slots.
     * @return array<string,mixed>
     */
    public function capacityUnitMappingPreview(): array
    {
        $units = $this->safeArray(function () { return $this->unitCurrentOccupancySummary(); }) ?: [];
        return [
            'raw_units' => $units,
            'mapped_units' => $this->resolveCapacityUnitSlots($units),
        ];
    }

    /** @param array<int,array<string,mixed>> $items */
    private function addOperationalDayItems(array &$items): void
    {
        $info = $this->operationalDayInfo();
        $this->add($items, 'operational_day', null, (string)($info['operational_date'] ?? ''), 'ok', 'SYSDATE operational calendar');
        $this->add($items, 'operational_start_hour', (float)($info['start_hour'] ?? $this->operationalDayStartHour()), null, 'ok', 'EXECUTIVE_OPERATIONAL_DAY_START_HOUR');
        $this->add($items, 'operational_rollover', !empty($info['is_rollover']) ? 1.0 : 0.0, !empty($info['is_rollover']) ? 'Night rollover active' : 'Calendar day active', 'ok', 'SYSDATE operational calendar');
        $this->add($items, 'operational_window', null, (string)($info['window_start'] ?? '') . ' -> ' . (string)($info['window_end'] ?? ''), 'ok', 'SYSDATE operational calendar');
    }

    /** @param array<int,array<string,mixed>> $items */
    private function addFlowItems(array &$items): void
    {
        $opd = $this->safeNumber(function () { return $this->opdVisitsToday(); });
        $er = $this->safeNumber(function () { return $this->erVisitsToday(); });
        $admissions = $this->safeNumber(function () { return $this->admissionsToday(); });
        $discharges = $this->safeNumber(function () { return $this->dischargesToday(); });
        $scheduledOr = $this->safeNumber(function () { return $this->operationsToday(false); });
        $performedOr = $this->safeNumber(function () { return $this->operationsToday(true); });

        $this->add($items, 'opd', $opd, null, $opd === null ? 'pending' : 'ok', 'RCP_SERVICE_RESERVATION');
        $this->add($items, 'er', $er, null, $er === null ? 'pending' : 'ok', 'RCP_SERVICE_RESERVATION');
        $this->add($items, 'admissions', $admissions, null, $admissions === null ? 'pending' : 'ok', 'RCP_ENTRYEXIT');
        $this->add($items, 'discharges', $discharges, null, $discharges === null ? 'pending' : 'ok', 'RCP_ENTRYEXIT');
        $this->add($items, 'scheduled_operations', $scheduledOr, null, $scheduledOr === null ? 'pending' : 'ok', 'OPER_OPERATION_SCHEDULE');
        $this->add($items, 'performed_operations', $performedOr, null, $performedOr === null ? 'pending' : 'ok', 'OPER_OPERATION_SCHEDULE');
    }

    /** @param array<int,array<string,mixed>> $items */
    private function addFinanceItems(array &$items): void
    {
        $finance = $this->financeSnapshot();
        foreach ($finance as $code => $value) {
            $this->add($items, $code, $value, null, 'ok', 'T_INP_OUTP_REVENUE_BATCH');
        }
    }

    /** @param array<int,array<string,mixed>> $items */
    private function addCapacityItems(array &$items): void
    {
        /*
         * Phase 10.2.8 — Beds Summary Binding
         * ------------------------------------
         * The home page must stay fast, so we calculate bed KPIs only inside
         * the refresh job and store the final numbers in DASH_EXEC_SUMMARY_KPI.
         * The dashboard itself reads the tiny summary table only.
         */
        $capacity = $this->safeArray(function () { return $this->bedCapacitySnapshot(); });

        if ($capacity) {
            $this->add($items, 'occupancy_rate', $capacity['occupancy_rate'], null, 'ok', 'STP_BEDS/RCP_BED_TRANS');
            $this->add($items, 'total_beds', $capacity['total_beds'], null, 'ok', 'STP_BEDS');
            $this->add($items, 'occupied_beds', $capacity['occupied_beds'], null, 'ok', 'RCP_BED_TRANS');
            $this->add($items, 'available_beds', $capacity['available_beds'], null, 'ok', 'STP_BEDS/RCP_BED_TRANS');
            $this->add($items, 'blocked_beds', $capacity['blocked_beds'], null, $capacity['blocked_beds'] === null ? 'pending' : 'ok', 'STP_BEDS');
        } else {
            $this->add($items, 'occupancy_rate', null, null, 'pending', 'STP_BEDS/RCP_BED_TRANS');
            $this->add($items, 'total_beds', null, null, 'pending', 'STP_BEDS');
            $this->add($items, 'occupied_beds', null, null, 'pending', 'RCP_BED_TRANS');
            $this->add($items, 'available_beds', null, null, 'pending', 'STP_BEDS/RCP_BED_TRANS');
            $this->add($items, 'blocked_beds', null, null, 'pending', 'STP_BEDS');
        }

        $expectedDischarges = $this->safeNumber(function () { return $this->expectedDischargesToday(); });
        $this->add($items, 'expected_discharges', $expectedDischarges ?? 0, null, 'ok', 'RCP_VISIT');

        $units = $this->safeArray(function () { return $this->unitCurrentOccupancySummary(); }) ?: [];
        $mappedUnits = $this->resolveCapacityUnitSlots($units);

        foreach (['unit_icu', 'unit_medical', 'unit_surgical'] as $unitCode) {
            $slot = $mappedUnits[$unitCode] ?? ['label' => str_replace('unit_', '', $unitCode), 'value' => null, 'status' => 'pending'];
            $value = $slot['value'] ?? null;
            $label = (string)($slot['label'] ?? str_replace('unit_', '', $unitCode));
            $this->add($items, $unitCode, $value, $label, $value === null ? 'pending' : 'ok', 'STP_BEDS/STP_HRC/RCP_BED_TRANS');
        }
    }


    /** @return array<string,mixed>|null */
    private function bedCapacitySnapshot()
    {
        $total = $this->totalActiveBeds();
        $occupied = $this->currentlyOccupiedBeds();
        $blocked = $this->blockedBeds();

        if ($total === null && $occupied === null) {
            return null;
        }

        $totalBeds = (float)($total ?? 0.0);
        $occupiedBeds = (float)($occupied ?? 0.0);
        $blockedBeds = (float)($blocked ?? 0.0);

        // Diagnostic-confirmed NJCH source:
        // STP_BEDS has 261 beds and RCP_BED_TRANS currently has occupied beds.
        // Never allow the denominator to be smaller than the current in-use beds.
        if (($occupiedBeds + $blockedBeds) > $totalBeds) {
            $totalBeds = $occupiedBeds + $blockedBeds;
        }

        if ($totalBeds <= 0) {
            return null;
        }

        $availableBeds = max(0.0, $totalBeds - $occupiedBeds - $blockedBeds);
        $occupancy = round(($occupiedBeds / $totalBeds) * 100, 2);

        return [
            'total_beds' => $totalBeds,
            'occupied_beds' => $occupiedBeds,
            'available_beds' => $availableBeds,
            'occupancy_rate' => $occupancy,
            'blocked_beds' => $blocked,
        ];
    }

    private function totalActiveBeds(): ?float
    {
        $bedsTable = $this->firstExistingTable(['STP_BEDS', 'STP_BED', 'V_STP_BEDS', 'VW_STP_BEDS']);
        if ($bedsTable === null) { return null; }

        $bedColumn = $this->firstColumn($bedsTable, ['BEDID', 'BED_ID', 'BED_CODE', 'BED_SERIAL', 'BEDNO', 'BED_NO', 'ID']);
        if ($bedColumn === null) { return null; }

        $value = $this->countDistinctWithOptionalFilter($bedsTable, $bedColumn, $this->bedActiveFilters($bedsTable, 'b'));
        if ($value !== null && $value > 0) { return $value; }

        // Safe fallback for legacy branches where ACTIVE_BED/BABY_NORMAL_BED uses non-standard values.
        $all = $this->countDistinctWithOptionalFilter($bedsTable, $bedColumn, $this->bedDateFilters($bedsTable, 'b'));
        return ($all !== null && $all > 0) ? $all : null;
    }

    private function currentlyOccupiedBeds(): ?float
    {
        $transTable = $this->firstExistingTable(['RCP_BED_TRANS', 'RCP_BED_TRANSACTION', 'RCP_BED_TRANSACTIONS']);
        if ($transTable === null) { return null; }

        $bedColumn = $this->firstColumn($transTable, ['BEDID', 'BED_ID', 'BED_CODE', 'BED_SERIAL', 'BEDNO', 'BED_NO']);
        if ($bedColumn === null) { return null; }

        $alias = 't';
        $where = $this->currentBedTransWhereForTable($transTable, $alias);
        $sql = "SELECT COUNT(DISTINCT {$alias}.{$bedColumn}) value FROM {$transTable} {$alias} WHERE 1 = 1 {$where}";
        $value = $this->scalar($sql, []);
        if ($value > 0) { return $value; }

        // Fallback: current bed rows are commonly represented by a NULL end date.
        $endColumn = $this->firstColumn($transTable, ['ENDDATE', 'END_DATE', 'TO_DATE', 'DATE_TO', 'EXIT_DATE', 'DISCHARGE_DATE', 'BED_END_DATE', 'ENDTIME']);
        if ($endColumn !== null) {
            $fallbackWhere = " AND {$alias}.{$endColumn} IS NULL";
            if ($this->columnExists($transTable, 'COMP_PAT_FLAG')) {
                $fallbackWhere .= " AND NVL(UPPER(TRIM(TO_CHAR({$alias}.comp_pat_flag))), 'P') = 'P'";
            }
            $fallback = $this->scalar("SELECT COUNT(DISTINCT {$alias}.{$bedColumn}) value FROM {$transTable} {$alias} WHERE 1 = 1 {$fallbackWhere}", []);
            if ($fallback > 0) { return $fallback; }
        }

        return $value >= 0 ? $value : null;
    }

    private function blockedBeds(): ?float
    {
        $bedsTable = $this->firstExistingTable(['STP_BEDS', 'STP_BED', 'V_STP_BEDS', 'VW_STP_BEDS']);
        if ($bedsTable === null) { return null; }

        $bedColumn = $this->firstColumn($bedsTable, ['BEDID', 'BED_ID', 'BED_CODE', 'BED_SERIAL', 'BEDNO', 'BED_NO', 'ID']);
        if ($bedColumn === null) { return null; }

        $flags = [];
        foreach (['BED_BLOCK_FLAG', 'BLOCKED_BED', 'IS_BLOCKED', 'BED_BLOCKED', 'BLOCK_FLAG', 'BLOCKED_FLAG', 'OUT_OF_SERVICE', 'OUTSERVICE', 'MNT_FLAG'] as $column) {
            if ($this->columnExists($bedsTable, $column)) {
                $flags[] = $this->truthySql('b.' . strtolower($column));
            }
        }

        if (!$flags && $this->columnExists($bedsTable, 'BED_BLOCK_DATETIME')) {
            $flags[] = 'b.bed_block_datetime IS NOT NULL';
        }

        if (!$flags) { return null; }

        $where = implode('', $this->bedDateFilters($bedsTable, 'b'));
        $where .= ' AND (' . implode(' OR ', $flags) . ')';

        $value = $this->scalar("SELECT COUNT(DISTINCT b.{$bedColumn}) value FROM {$bedsTable} b WHERE 1 = 1 {$where}", []);
        return $value >= 0 ? $value : null;
    }

    private function currentBedTransWhereForTable(string $table, string $alias): string
    {
        $where = '';
        $startColumn = $this->firstColumn($table, ['STARTDATE', 'START_DATE', 'FROM_DATE', 'DATE_FROM', 'ENTRY_DATE', 'ADMISSION_DATE', 'BED_START_DATE', 'STARTTIME']);
        $endColumn = $this->firstColumn($table, ['ENDDATE', 'END_DATE', 'TO_DATE', 'DATE_TO', 'EXIT_DATE', 'DISCHARGE_DATE', 'BED_END_DATE', 'ENDTIME']);

        if ($startColumn !== null) {
            $where .= " AND ({$alias}.{$startColumn} IS NULL OR {$alias}.{$startColumn} <= SYSDATE)";
        }
        if ($endColumn !== null) {
            $where .= " AND ({$alias}.{$endColumn} IS NULL OR {$alias}.{$endColumn} > SYSDATE)";
        }

        if ($this->columnExists($table, 'COMP_PAT_FLAG')) {
            $where .= " AND NVL(UPPER(TRIM(TO_CHAR({$alias}.comp_pat_flag))), 'P') = 'P'";
        }
        if ($this->columnExists($table, 'BABY_NORMAL_BED')) {
            $where .= " AND " . $this->positiveFlagSql("{$alias}.baby_normal_bed", '1');
        }
        if ($this->columnExists($table, 'BLOCKED')) {
            $where .= " AND NOT (" . $this->truthySql("{$alias}.blocked") . ")";
        }
        if ($this->columnExists($table, 'CANCEL_FLAG')) {
            $where .= " AND NOT (" . $this->truthySql("{$alias}.cancel_flag") . ")";
        }
        if ($this->columnExists($table, 'STATUS')) {
            $where .= " AND NVL(UPPER(TRIM(TO_CHAR({$alias}.status))), 'A') NOT IN ('C','CANCEL','CANCELLED')";
        }
        return $where;
    }

    private function currentBedTransWhere(string $alias): string
    {
        return $this->currentBedTransWhereForTable('RCP_BED_TRANS', $alias);
    }

    private function stpBedsActiveSql(string $alias): string
    {
        return implode('', $this->bedActiveFilters('STP_BEDS', $alias));
    }

    /** @return array<int,string> */
    private function bedActiveFilters(string $table, string $alias = 'b'): array
    {
        $filters = $this->bedDateFilters($table, $alias);

        if ($this->columnExists($table, 'ACTIVE_BED')) {
            $filters[] = " AND " . $this->positiveFlagSql("{$alias}.active_bed", '1');
        }
        if ($this->columnExists($table, 'ACTIVE_FLAG')) {
            $filters[] = " AND " . $this->positiveFlagSql("{$alias}.active_flag", '1');
        }
        if ($this->columnExists($table, 'IS_ACTIVE')) {
            $filters[] = " AND " . $this->positiveFlagSql("{$alias}.is_active", '1');
        }
        if ($this->columnExists($table, 'BABY_NORMAL_BED')) {
            $filters[] = " AND " . $this->positiveFlagSql("{$alias}.baby_normal_bed", '1');
        }
        if ($this->columnExists($table, 'STOP_FLAG')) {
            $filters[] = " AND NOT (" . $this->truthySql("{$alias}.stop_flag") . ")";
        }
        if ($this->columnExists($table, 'CANCEL_FLAG')) {
            $filters[] = " AND NOT (" . $this->truthySql("{$alias}.cancel_flag") . ")";
        }

        return $filters;
    }

    /** @return array<int,string> */
    private function bedDateFilters(string $table, string $alias = 'b'): array
    {
        $filters = [];
        $startColumn = $this->firstColumn($table, ['STARTDATE', 'START_DATE', 'FROM_DATE', 'DATE_FROM']);
        $endColumn = $this->firstColumn($table, ['ENDDATE', 'END_DATE', 'TO_DATE', 'DATE_TO']);

        if ($startColumn !== null) {
            $filters[] = " AND ({$alias}.{$startColumn} IS NULL OR {$alias}.{$startColumn} <= SYSDATE)";
        }
        if ($endColumn !== null) {
            $filters[] = " AND ({$alias}.{$endColumn} IS NULL OR {$alias}.{$endColumn} > SYSDATE)";
        }
        return $filters;
    }

    /** @param array<int,string> $filters */
    private function countDistinctWithOptionalFilter(string $table, string $column, array $filters): ?float
    {
        try {
            $where = implode('', $filters);
            $value = $this->scalar("SELECT COUNT(DISTINCT b.{$column}) value FROM {$table} b WHERE 1 = 1 {$where}", []);
            return $value >= 0 ? $value : null;
        } catch (Throwable $e) {
            $this->log('countDistinctWithOptionalFilter.' . $table, $e);
            return null;
        }
    }

    /** @param array<int,string> $tables */
    private function firstExistingTable(array $tables): ?string
    {
        foreach ($tables as $table) {
            if ($this->tableExists($table)) { return $table; }
        }
        return null;
    }

    /** @param array<int,string> $columns */
    private function firstColumn(string $table, array $columns): ?string
    {
        foreach ($columns as $column) {
            if ($this->columnExists($table, $column)) { return strtolower($column); }
        }
        return null;
    }

    private function truthySql(string $expr): string
    {
        return "NVL(UPPER(TRIM(TO_CHAR({$expr}))), '0') IN ('1','Y','YES','TRUE','T')";
    }

    private function positiveFlagSql(string $expr, string $default = '1'): string
    {
        $default = strtoupper($default) === '0' ? '0' : '1';
        return "NVL(UPPER(TRIM(TO_CHAR({$expr}))), '{$default}') IN ('1','Y','YES','TRUE','T','A','ACTIVE')";
    }

    /** @return array<int,array<string,mixed>> */
    private function unitCurrentOccupancySummary(): array
    {
        if (!$this->tableExists('STP_BEDS') || !$this->columnExists('STP_BEDS', 'BEDID')) { return []; }

        $bedWhere = $this->stpBedsActiveSql('b');
        $joins = $this->bedDimensionJoins('b');
        $unitId = $this->unitIdExpr();
        $unitLabel = $this->unitLabelExpr();
        $currentTransWhere = $this->currentBedTransWhere('t');

        $sql = "
            WITH active_beds AS (
                SELECT b.bedid,
                       {$unitId} unit_id,
                       {$unitLabel} unit_label
                FROM stp_beds b
                {$joins}
                WHERE 1 = 1
                {$bedWhere}
            ),
            occupied AS (
                SELECT DISTINCT t.bedid
                FROM rcp_bed_trans t
                WHERE 1 = 1
                {$currentTransWhere}
            )
            SELECT unit_id,
                   NVL(MAX(unit_label), 'Unknown unit') unit_label,
                   COUNT(DISTINCT ab.bedid) total_beds,
                   COUNT(DISTINCT o.bedid) occupied_beds,
                   ROUND(CASE WHEN COUNT(DISTINCT ab.bedid) > 0 THEN COUNT(DISTINCT o.bedid) / COUNT(DISTINCT ab.bedid) * 100 ELSE 0 END, 2) occupancy_rate
            FROM active_beds ab
            LEFT JOIN occupied o ON o.bedid = ab.bedid
            GROUP BY unit_id
            ORDER BY occupancy_rate DESC
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, []);
        $out = [];
        foreach ($rows as $row) {
            $out[] = [
                'id' => (string)($row['unit_id'] ?? ''),
                'label' => trim((string)($row['unit_label'] ?? 'Unknown unit')),
                'total_beds' => (float)($row['total_beds'] ?? 0),
                'occupied_beds' => (float)($row['occupied_beds'] ?? 0),
                'value' => (float)($row['occupancy_rate'] ?? 0),
            ];
        }
        return $out;
    }

    /**
     * Resolve the three executive unit rows shown in Capacity & Occupancy.
     * It first uses semantic matching on STP_HRC/STP_PLACES labels; if a branch
     * uses non-standard ward names, it safely falls back to the largest remaining
     * active wards so the dashboard stays informative instead of showing pending.
     *
     * @param array<int,array<string,mixed>> $units
     * @return array<string,array<string,mixed>>
     */
    private function resolveCapacityUnitSlots(array $units): array
    {
        $slots = [
            'unit_icu' => ['label' => 'ICU', 'value' => null, 'status' => 'pending'],
            'unit_medical' => ['label' => 'Medical', 'value' => null, 'status' => 'pending'],
            'unit_surgical' => ['label' => 'Surgical', 'value' => null, 'status' => 'pending'],
        ];

        $used = [];
        $definitions = [
            'unit_icu' => [
                'label' => 'ICU',
                'needles' => ['ICU', 'I.C.U', 'I C U', 'INTENSIVE', 'CRITICAL', 'CCU', 'C.C.U', 'CORONARY', 'عناية', 'رعايه', 'مركزة', 'مركزه', 'حرجة', 'حرجه'],
            ],
            'unit_medical' => [
                'label' => 'Medical',
                'needles' => ['MEDICAL', 'MEDICINE', 'INTERNAL', 'INTERN', 'GENERAL WARD', 'ADULT WARD', 'NURSING', 'NURS', 'WARD', 'باطن', 'باطنة', 'باطنه', 'داخلي', 'داخلى'],
            ],
            'unit_surgical' => [
                'label' => 'Surgical',
                'needles' => ['SURGICAL', 'SURGERY', 'SURG', 'OPERATIVE', 'ORTHO', 'UROLOGY', 'ENT', 'جراح', 'جراحة', 'جراحه', 'عمليات'],
            ],
        ];

        foreach ($definitions as $slotCode => $def) {
            $matched = [];
            foreach ($units as $idx => $unit) {
                $key = $this->unitIdentity($unit, (int)$idx);
                if (isset($used[$key])) { continue; }
                if ($this->labelContainsAny((string)($unit['label'] ?? ''), $def['needles'])) {
                    $matched[$key] = $unit;
                }
            }

            if ($matched) {
                foreach (array_keys($matched) as $key) { $used[$key] = true; }
                $slots[$slotCode] = $this->aggregateUnitRows(array_values($matched), (string)$def['label'], false);
            }
        }

        $remaining = [];
        foreach ($units as $idx => $unit) {
            $key = $this->unitIdentity($unit, (int)$idx);
            if (!isset($used[$key]) && (float)($unit['total_beds'] ?? 0) > 0) {
                $remaining[$key] = $unit;
            }
        }

        uasort($remaining, function (array $a, array $b): int {
            $scoreA = ((float)($a['total_beds'] ?? 0) * 1000) + ((float)($a['occupied_beds'] ?? 0) * 10) + (float)($a['value'] ?? 0);
            $scoreB = ((float)($b['total_beds'] ?? 0) * 1000) + ((float)($b['occupied_beds'] ?? 0) * 10) + (float)($b['value'] ?? 0);
            if ($scoreA === $scoreB) { return 0; }
            return $scoreA < $scoreB ? 1 : -1;
        });

        foreach (['unit_medical', 'unit_surgical', 'unit_icu'] as $slotCode) {
            if (($slots[$slotCode]['value'] ?? null) !== null) { continue; }
            $unit = array_shift($remaining);
            if (!$unit) { continue; }
            $label = $this->shortUnitLabel((string)($unit['label'] ?? str_replace('unit_', '', $slotCode)));
            $slots[$slotCode] = $this->aggregateUnitRows([$unit], $label, true);
        }

        return $slots;
    }

    /** @param array<int,array<string,mixed>> $rows */
    private function aggregateUnitRows(array $rows, string $fallbackLabel, bool $useActualLabel): array
    {
        $total = 0.0;
        $occupied = 0.0;
        $values = [];
        $labels = [];

        foreach ($rows as $row) {
            $total += (float)($row['total_beds'] ?? 0);
            $occupied += (float)($row['occupied_beds'] ?? 0);
            if (isset($row['value']) && $row['value'] !== null && $row['value'] !== '') {
                $values[] = (float)$row['value'];
            }
            $label = trim((string)($row['label'] ?? ''));
            if ($label !== '') { $labels[] = $label; }
        }

        if ($total > 0) {
            $value = round(($occupied / $total) * 100, 2);
        } elseif ($values) {
            $value = round(array_sum($values) / count($values), 2);
        } else {
            $value = null;
        }

        $label = $fallbackLabel;
        if ($useActualLabel && $labels) {
            $label = $this->shortUnitLabel($labels[0]);
        }

        return [
            'label' => $label,
            'value' => $value,
            'total_beds' => $total,
            'occupied_beds' => $occupied,
            'status' => $value === null ? 'pending' : 'ok',
        ];
    }

    private function unitIdentity(array $unit, int $idx): string
    {
        $id = trim((string)($unit['id'] ?? ''));
        if ($id !== '') { return 'id:' . $id; }
        $label = $this->normalizeUnitLabel((string)($unit['label'] ?? ''));
        return $label !== '' ? 'label:' . $label : 'row:' . (string)$idx;
    }

    private function labelContainsAny(string $label, array $needles): bool
    {
        $label = $this->normalizeUnitLabel($label);
        if ($label === '') { return false; }
        foreach ($needles as $needle) {
            $needle = $this->normalizeUnitLabel((string)$needle);
            if ($needle !== '' && strpos($label, $needle) !== false) {
                return true;
            }
        }
        return false;
    }

    private function normalizeUnitLabel(string $label): string
    {
        $label = trim($label);
        if ($label === '') { return ''; }
        if (function_exists('mb_strtoupper')) {
            $label = mb_strtoupper($label, 'UTF-8');
        } else {
            $label = strtoupper($label);
        }
        $label = str_replace(['.', '-', '_', '/', '\\', '(', ')', '[', ']'], ' ', $label);
        return preg_replace('/\s+/u', ' ', $label) ?: $label;
    }

    private function shortUnitLabel(string $label): string
    {
        $label = trim(preg_replace('/\s+/u', ' ', $label) ?: $label);
        if ($label === '') { return 'Unit'; }
        if (function_exists('mb_strlen') && mb_strlen($label, 'UTF-8') > 18) {
            return mb_substr($label, 0, 17, 'UTF-8') . '…';
        }
        if (!function_exists('mb_strlen') && strlen($label) > 18) {
            return substr($label, 0, 17) . '…';
        }
        return $label;
    }

    /** @param array<int,array<string,mixed>> $units */
    private function pickUnitValue(array $units, array $needles): ?float
    {
        foreach ($units as $unit) {
            $label = strtoupper((string)($unit['label'] ?? ''));
            foreach ($needles as $needle) {
                if ($label !== '' && strpos($label, strtoupper($needle)) !== false) {
                    return (float)($unit['value'] ?? 0);
                }
            }
        }
        return null;
    }

    private function bedDimensionJoins(string $bedAlias): string
    {
        $joins = '';
        $hasPlaces = $this->tableExists('STP_PLACES');
        $hasHrc = $this->tableExists('STP_HRC');

        if ($hasPlaces && $this->columnExists('STP_BEDS', 'ROOMID') && $this->columnExists('STP_PLACES', 'PLACE_ID1')) {
            $joins .= "\n                LEFT JOIN stp_places pl ON {$bedAlias}.roomid = pl.place_id1";
        }

        if ($hasHrc && $this->columnExists('STP_HRC', 'UNIT_ID')) {
            $joinUnitExpr = null;
            if ($hasPlaces && $this->columnExists('STP_PLACES', 'UNIT_ID') && $this->columnExists('STP_BEDS', 'ROOMID')) {
                $joinUnitExpr = 'pl.unit_id';
            }
            if ($this->columnExists('STP_BEDS', 'UNIT_ID')) {
                $joinUnitExpr = $joinUnitExpr === null ? "{$bedAlias}.unit_id" : "COALESCE({$joinUnitExpr}, {$bedAlias}.unit_id)";
            }
            if ($joinUnitExpr !== null) {
                $joins .= "\n                LEFT JOIN stp_hrc h ON h.unit_id = {$joinUnitExpr}";
            }
        }

        return $joins;
    }

    private function unitIdExpr(): string
    {
        $parts = [];
        if ($this->tableExists('STP_PLACES') && $this->columnExists('STP_PLACES', 'UNIT_ID') && $this->columnExists('STP_BEDS', 'ROOMID')) {
            $parts[] = 'pl.unit_id';
        }
        if ($this->columnExists('STP_BEDS', 'UNIT_ID')) {
            $parts[] = 'b.unit_id';
        }
        if (!$parts) { return '0'; }
        if (count($parts) === 1) { return 'NVL(' . $parts[0] . ', 0)'; }
        return 'NVL(COALESCE(' . implode(', ', $parts) . '), 0)';
    }

    private function unitLabelExpr(): string
    {
        $parts = [];
        if ($this->tableExists('STP_HRC') && $this->columnExists('STP_HRC', 'UNIT_EN_NAME')) {
            $parts[] = 'h.unit_en_name';
        }
        if ($this->tableExists('STP_HRC') && $this->columnExists('STP_HRC', 'UNIT_AR_NAME')) {
            $parts[] = 'h.unit_ar_name';
        }
        if ($this->tableExists('STP_PLACES') && $this->columnExists('STP_PLACES', 'PLACE_EN_NAME') && $this->columnExists('STP_BEDS', 'ROOMID')) {
            $parts[] = 'pl.place_en_name';
        }
        if ($this->tableExists('STP_PLACES') && $this->columnExists('STP_PLACES', 'PLACE_AR_NAME') && $this->columnExists('STP_BEDS', 'ROOMID')) {
            $parts[] = 'pl.place_ar_name';
        }
        if (!$parts) { return "'Unknown unit'"; }
        return 'COALESCE(' . implode(', ', $parts) . ", 'Unknown unit')";
    }

    /** @param array<int,array<string,mixed>> $items */
    private function addClinicalItems(array &$items): void
    {
        $year = $this->latestDoctorYear();
        $this->add($items, 'doctor_year', (float)$year, null, 'ok', 'KPI_DOCTOR_PERF_DETAIL_JOB');

        if ($year <= 0 || !$this->tableExists('KPI_DOCTOR_PERF_DETAIL_JOB')) {
            $this->add($items, 'active_doctors', null, null, 'pending', 'KPI_DOCTOR_PERF_DETAIL_JOB');
            return;
        }

        $active = $this->safeNumber(function () use ($year): float {
            return $this->scalar("SELECT COUNT(DISTINCT " . $this->doctorKeySql() . ") value FROM kpi_doctor_perf_detail_job WHERE yearid = :year", ['year' => $year]);
        });
        $this->add($items, 'active_doctors', $active, null, $active === null ? 'pending' : 'ok', 'KPI_DOCTOR_PERF_DETAIL_JOB');

        $top = $this->topDoctor($year);
        if ($top) {
            $this->add($items, 'top_doctor', (float)($top['value'] ?? 0), (string)($top['label'] ?? ''), 'ok', 'KPI_DOCTOR_PERF_DETAIL_JOB');
        }

        $specialties = $this->topSpecialties($year, 5);
        $i = 1;
        foreach ($specialties as $spec) {
            $this->add($items, 'top_specialty_' . $i, (float)($spec['value'] ?? 0), (string)($spec['label'] ?? ''), 'ok', 'KPI_DOCTOR_PERF_DETAIL_JOB');
            $i++;
        }
    }

    private function opdVisitsToday(): ?float
    {
        if (!$this->tableExists('RCP_SERVICE_RESERVATION') || !$this->tableExists('RCP_VISIT')) { return null; }
        if (!$this->columnExists('RCP_SERVICE_RESERVATION', 'EXPECTEDDONEDATE')) { return null; }
        $range = $this->operationalRangeSql('r.expecteddonedate');

        $sql = "
            SELECT COUNT(1) value
            FROM rcp_visit v, rcp_service_reservation r
            WHERE v.hosp_id = r.hosp_id
              AND v.patientid = r.patientid
              AND v.visit_id = r.visit_id
              AND {$range}
              AND NVL(r.servstatus, 'N') NOT IN ('C','F')
              AND r.sched_serial IS NOT NULL
        ";
        if ($this->columnExists('RCP_SERVICE_RESERVATION', 'CLASSCODE')) {
            $sql .= " AND r.classcode = 2";
        }
        $value = $this->scalar($sql, []);
        if ($value == 0.0) {
            $fallbackSql = "
                SELECT COUNT(1) value
                FROM rcp_service_reservation r
                WHERE {$range}
                  AND NVL(r.servstatus, 'N') NOT IN ('C','F')
                  AND r.sched_serial IS NOT NULL
            ";
            if ($this->columnExists('RCP_SERVICE_RESERVATION', 'CLASSCODE')) {
                $fallbackSql .= " AND r.classcode = 2";
            }
            $value = max($value, $this->scalar($fallbackSql, []));
        }
        return $value;
    }

    private function erVisitsToday(): ?float
    {
        if (!$this->tableExists('RCP_SERVICE_RESERVATION') || !$this->tableExists('RCP_VISIT')) { return null; }
        if (!$this->columnExists('RCP_SERVICE_RESERVATION', 'EXPECTEDDONEDATE')) { return null; }
        $range = $this->operationalRangeSql('r.expecteddonedate');

        $erParts = [];
        if ($this->columnExists('RCP_SERVICE_RESERVATION', 'OUTPTA_EMERGENCY_TYPE')) {
            $erParts[] = 'NVL(r.outpta_emergency_type,0) = 1';
        }
        if ($this->columnExists('RCP_VISIT', 'OUTPTA_EMERGENCY_TYPE')) {
            $erParts[] = 'NVL(v.outpta_emergency_type,0) = 1';
        }
        if ($this->columnExists('RCP_SERVICE_RESERVATION', 'CLINIC_ID')) {
            $erParts[] = 'r.clinic_id IN (16, 297, 298)';
        }
        if ($this->columnExists('RCP_VISIT', 'CLINIC_ID')) {
            $erParts[] = 'v.clinic_id IN (16, 297, 298)';
        }
        if (!$erParts) { return null; }

        $sql = "
            SELECT COUNT(1) value
            FROM rcp_visit v, rcp_service_reservation r
            WHERE v.hosp_id = r.hosp_id
              AND v.patientid = r.patientid
              AND v.visit_id = r.visit_id
              AND {$range}
              AND NVL(r.servstatus, 'N') NOT IN ('C','F')
              AND r.sched_serial IS NOT NULL
              AND (" . implode(' OR ', $erParts) . ")
        ";
        if ($this->columnExists('RCP_SERVICE_RESERVATION', 'CLASSCODE')) {
            $sql .= " AND r.classcode = 2";
        }
        $value = $this->scalar($sql, []);
        if ($value == 0.0) {
            $rParts = [];
            if ($this->columnExists('RCP_SERVICE_RESERVATION', 'OUTPTA_EMERGENCY_TYPE')) {
                $rParts[] = 'NVL(r.outpta_emergency_type,0) = 1';
            }
            if ($this->columnExists('RCP_SERVICE_RESERVATION', 'CLINIC_ID')) {
                $rParts[] = 'r.clinic_id IN (16, 297, 298)';
            }
            if ($rParts) {
                $fallbackSql = "
                    SELECT COUNT(1) value
                    FROM rcp_service_reservation r
                    WHERE {$range}
                      AND NVL(r.servstatus, 'N') NOT IN ('C','F')
                      AND r.sched_serial IS NOT NULL
                      AND (" . implode(' OR ', $rParts) . ")
                ";
                if ($this->columnExists('RCP_SERVICE_RESERVATION', 'CLASSCODE')) {
                    $fallbackSql .= " AND r.classcode = 2";
                }
                $value = max($value, $this->scalar($fallbackSql, []));
            }
        }
        return $value;
    }

    private function admissionsToday(): ?float
    {
        if (!$this->tableExists('RCP_VISIT') || !$this->tableExists('RCP_ENTRYEXIT')) { return null; }
        if (!$this->columnExists('RCP_VISIT', 'VISIT_START_DATE')) { return null; }
        $range = $this->operationalRangeSql('v.visit_start_date');
        $sql = "
            SELECT COUNT(1) value
            FROM rcp_visit v, rcp_entryexit e
            WHERE v.patientid = e.patientid
              AND v.visit_id = e.visit_id
              AND {$range}
        ";
        if ($this->columnExists('RCP_ENTRYEXIT', 'BABY_NORMAL_BED')) {
            $sql .= " AND NVL(e.baby_normal_bed,1) = 1";
        }
        return $this->scalar($sql, []);
    }

    private function dischargesToday(): ?float
    {
        if (!$this->tableExists('RCP_VISIT') || !$this->tableExists('RCP_ENTRYEXIT')) { return null; }
        if (!$this->columnExists('RCP_VISIT', 'VISIT_END_DATE')) { return null; }
        $range = $this->operationalRangeSql('v.visit_end_date');
        $sql = "
            SELECT COUNT(1) value
            FROM rcp_visit v, rcp_entryexit e
            WHERE v.patientid = e.patientid
              AND v.visit_id = e.visit_id
              AND {$range}
        ";
        if ($this->columnExists('RCP_ENTRYEXIT', 'BABY_NORMAL_BED')) {
            $sql .= " AND NVL(e.baby_normal_bed,1) = 1";
        }
        return $this->scalar($sql, []);
    }

    private function operationsToday(bool $performed): ?float
    {
        if (!$this->tableExists('RCP_SERVICE_RESERVATION') || !$this->tableExists('OPER_OPERATION_SCHEDULE')) { return null; }
        if (!$this->columnExists('RCP_SERVICE_RESERVATION', 'EXPECTEDDONEDATE')) { return null; }
        $statusCondition = $performed ? "r.servstatus = 'D'" : "NVL(r.servstatus, 'N') <> 'C'";
        $range = $this->operationalRangeSql('r.expecteddonedate');
        return $this->scalar(""
            . "SELECT COUNT(1) value FROM rcp_service_reservation r, oper_operation_schedule o "
            . "WHERE r.ser = o.ser "
            . "AND {$statusCondition} "
            . "AND {$range}", []);
    }

    private function expectedDischargesToday(): ?float
    {
        if ($this->tableExists('RCP_ENTRYEXIT') && $this->columnExists('RCP_ENTRYEXIT', 'EXPECTDISCDATE')) {
            $range = $this->operationalRangeSql('e.expectdiscdate');
            $sql = "SELECT COUNT(1) value FROM rcp_entryexit e WHERE {$range}";
            if ($this->columnExists('RCP_ENTRYEXIT', 'DISCHARGEDATE')) {
                $sql .= " AND e.dischargedate IS NULL";
            }
            if ($this->columnExists('RCP_ENTRYEXIT', 'BABY_NORMAL_BED')) {
                $sql .= " AND NVL(e.baby_normal_bed,1) = 1";
            }
            return $this->scalar($sql, []);
        }

        if ($this->tableExists('RCP_VISIT') && $this->columnExists('RCP_VISIT', 'PLANNED_DIS_DATE')) {
            $range = $this->operationalRangeSql('v.planned_dis_date');
            $sql = "SELECT COUNT(1) value FROM rcp_visit v WHERE {$range}";
            if ($this->columnExists('RCP_VISIT', 'VISIT_END_DATE')) {
                $sql .= " AND v.visit_end_date IS NULL";
            }
            return $this->scalar($sql, []);
        }

        return null;
    }

    /** @return array<string,float> */
    private function financeSnapshot(): array
    {
        $empty = [
            'today_total' => 0.0,
            'month_total' => 0.0,
            'cash' => 0.0,
            'insurance' => 0.0,
            'moh' => 0.0,
            'net' => 0.0,
        ];
        if (!$this->tableExists('T_INP_OUTP_REVENUE_BATCH')) { return $empty; }
        if (!$this->columnExists('T_INP_OUTP_REVENUE_BATCH', 'VISIT_END_DATE')) { return $empty; }

        $mohCustomer = (string)\config('hospitals.' . $this->hospitalCode . '.moh_customer_code', '');

        /*
         * Phase 10.3.6 — Operational finance day alignment
         * -------------------------------------------------
         * T_INP_OUTP_REVENUE_BATCH is a financial batch/source table. In several
         * legacy HIS installations VISIT_END_DATE is stored as an accounting date
         * with no reliable time component. Using the 07:00 -> 07:00 operational
         * window can therefore return zero immediately after midnight even while
         * visits/admissions correctly roll over to the previous operational date.
         *
         * For finance "Today Revenue" we align to the displayed operational DATE:
         *   operational_date 00:00 -> operational_date + 1 00:00
         * This restores the previous-day financial snapshot during night rollover
         * and avoids misleading SAR 0 cards after midnight.
         */
        $financeDayStart = $this->operationalDayExpr();
        $financeDayEnd = "({$this->operationalDayExpr()} + 1)";
        $monthStart = "TRUNC(SYSDATE, 'MM')";
        $monthEnd = "ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)";
        $monthRange = "visit_end_date >= {$monthStart} AND visit_end_date < {$monthEnd}";
        $opRange = "visit_end_date >= {$financeDayStart} AND visit_end_date < {$financeDayEnd}";

        $sql = "
            SELECT
                NVL(SUM(CASE WHEN {$opRange} THEN NVL(credit_value,0)+NVL(cash_value,0) ELSE 0 END), 0) today_total,
                NVL(SUM(CASE WHEN {$monthRange} THEN NVL(credit_value,0)+NVL(cash_value,0) ELSE 0 END), 0) month_total,
                NVL(SUM(CASE WHEN {$monthRange} THEN NVL(cash_value,0) ELSE 0 END), 0) cash,
                NVL(SUM(CASE WHEN {$monthRange} THEN NVL(credit_value,0) ELSE 0 END), 0) insurance,
                NVL(SUM(CASE WHEN :moh_customer IS NOT NULL AND {$monthRange} AND customer_code = :moh_customer THEN NVL(credit_value,0)+NVL(cash_value,0) ELSE 0 END), 0) moh
            FROM t_inp_outp_revenue_batch
            WHERE (({$monthRange}) OR ({$opRange}))
        ";
        if ($this->columnExists('T_INP_OUTP_REVENUE_BATCH', 'YEARID')) {
            $sql .= " AND yearid IN (:year_id, TO_NUMBER(TO_CHAR({$this->operationalDayExpr()}, 'YYYY')))";
            $params = ['year_id' => (int)date('Y'), 'moh_customer' => $mohCustomer !== '' ? $mohCustomer : null];
        } else {
            $params = ['moh_customer' => $mohCustomer !== '' ? $mohCustomer : null];
        }

        try {
            $row = Database::fetchOne($this->hospitalCode, $sql, $params);
            if (!$row) { return $empty; }
            $cash = (float)($row['cash'] ?? 0);
            $insurance = (float)($row['insurance'] ?? 0);
            $moh = (float)($row['moh'] ?? 0);
            return [
                'today_total' => (float)($row['today_total'] ?? 0),
                'month_total' => (float)($row['month_total'] ?? 0),
                'cash' => $cash,
                'insurance' => $insurance,
                'moh' => $moh,
                'net' => max(0.0, $cash + $insurance - $moh),
            ];
        } catch (Throwable $e) {
            $this->log('financeSnapshot', $e);
            return $empty;
        }
    }

    private function latestDoctorYear(): int
    {
        if (!$this->tableExists('KPI_DOCTOR_PERF_DETAIL_JOB') || !$this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', 'YEARID')) { return 0; }
        try {
            $metric = $this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', 'TOT_REV') ? 'HAVING SUM(NVL(tot_rev,0)) <> 0' : '';
            $row = Database::fetchOne($this->hospitalCode, "
                SELECT yearid value FROM (
                    SELECT yearid FROM kpi_doctor_perf_detail_job
                    WHERE yearid IS NOT NULL
                    GROUP BY yearid
                    {$metric}
                    ORDER BY yearid DESC
                ) WHERE ROWNUM <= 1
            ", []);
            return (int)($row['value'] ?? 0);
        } catch (Throwable $e) {
            $this->log('latestDoctorYear', $e);
            return 0;
        }
    }

    /** @return array<string,mixed>|null */
    private function topDoctor(int $year)
    {
        if (!$this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', 'NET_REV_AFTER_DRUG_MOH')) { return null; }
        $nameExpr = $this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', 'EMP_EN_DATA') ? 'MAX(emp_en_data)' : "'Unknown doctor'";
        try {
            $row = Database::fetchOne($this->hospitalCode, "
                SELECT doctor_name, value FROM (
                    SELECT {$nameExpr} doctor_name, SUM(NVL(net_rev_after_drug_moh,0)) value
                    FROM kpi_doctor_perf_detail_job
                    WHERE yearid = :year
                    GROUP BY " . $this->doctorKeySql() . "
                    HAVING SUM(NVL(net_rev_after_drug_moh,0)) > 0
                    ORDER BY SUM(NVL(net_rev_after_drug_moh,0)) DESC
                ) WHERE ROWNUM <= 1
            ", ['year' => $year]);
            if (!$row) { return null; }
            return ['label' => (string)($row['doctor_name'] ?? ''), 'value' => (float)($row['value'] ?? 0)];
        } catch (Throwable $e) {
            $this->log('topDoctor', $e);
            return null;
        }
    }

    /** @return array<int,array<string,mixed>> */
    private function topSpecialties(int $year, int $limit): array
    {
        if (!$this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', 'SPECIAL_SPEC_EN_NAME')) { return []; }
        if (!$this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', 'TOT_REV')) { return []; }
        try {
            $rows = Database::fetchAll($this->hospitalCode, "
                SELECT specialty_name, value FROM (
                    SELECT special_spec_en_name specialty_name, SUM(NVL(tot_rev,0)) value
                    FROM kpi_doctor_perf_detail_job
                    WHERE yearid = :year
                    GROUP BY special_spec_en_name
                    HAVING SUM(NVL(tot_rev,0)) > 0
                    ORDER BY SUM(NVL(tot_rev,0)) DESC
                ) WHERE ROWNUM <= :row_limit
            ", ['year' => $year, 'row_limit' => $limit]);
            $out = [];
            foreach ($rows as $row) {
                $out[] = ['label' => (string)($row['specialty_name'] ?? ''), 'value' => (float)($row['value'] ?? 0)];
            }
            return $out;
        } catch (Throwable $e) {
            $this->log('topSpecialties', $e);
            return [];
        }
    }

    private function doctorKeySql(): string
    {
        if ($this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', 'EMP_ID')) { return 'emp_id'; }
        if ($this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', 'DOCTOR_ID')) { return 'doctor_id'; }
        if ($this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', 'EMP_EN_DATA')) { return 'emp_en_data'; }
        return 'ROWID';
    }

    /** @param array<int,array<string,mixed>> $items */
    private function add(array &$items, string $code, $value, $text = null, string $status = 'ok', string $source = ''): void
    {
        $items[] = [
            'code' => $code,
            'value' => $value === null ? null : (float)$value,
            'text' => $text === null ? null : (string)$text,
            'status' => $status,
            'source' => $source,
        ];
    }

    private function scalar(string $sql, array $params): float
    {
        $row = Database::fetchOne($this->hospitalCode, $sql, $params);
        return (float)($row['value'] ?? 0);
    }

    private function safeNumber(callable $fn): ?float
    {
        try {
            $value = $fn();
            return $value === null ? null : (float)$value;
        } catch (Throwable $e) {
            $this->log('safeNumber', $e);
            return null;
        }
    }

    /** @return array<mixed>|null */
    private function safeArray(callable $fn)
    {
        try {
            $value = $fn();
            return is_array($value) ? $value : null;
        } catch (Throwable $e) {
            $this->log('safeArray', $e);
            return null;
        }
    }

    private function tableExists(string $table): bool
    {
        $key = strtoupper($table);
        if (array_key_exists($key, $this->tableCache)) { return $this->tableCache[$key]; }
        try {
            $schema = (string)\config('hospitals.' . $this->hospitalCode . '.database.schema', '');
            $sql = $schema !== ''
                ? "SELECT COUNT(1) value FROM all_tables WHERE owner = UPPER(:schema) AND table_name = UPPER(:table_name)"
                : "SELECT COUNT(1) value FROM user_tables WHERE table_name = UPPER(:table_name)";
            $params = $schema !== '' ? ['schema' => $schema, 'table_name' => $table] : ['table_name' => $table];
            $row = Database::fetchOne($this->hospitalCode, $sql, $params);
            $this->tableCache[$key] = ((int)($row['value'] ?? 0)) > 0;
        } catch (Throwable $e) {
            $this->tableCache[$key] = false;
        }
        return $this->tableCache[$key];
    }

    private function columnExists(string $table, string $column): bool
    {
        $key = strtoupper($table . '.' . $column);
        if (array_key_exists($key, $this->columnCache)) { return $this->columnCache[$key]; }
        try {
            $schema = (string)\config('hospitals.' . $this->hospitalCode . '.database.schema', '');
            $sql = $schema !== ''
                ? "SELECT COUNT(1) value FROM all_tab_columns WHERE owner = UPPER(:schema) AND table_name = UPPER(:table_name) AND column_name = UPPER(:column_name)"
                : "SELECT COUNT(1) value FROM user_tab_columns WHERE table_name = UPPER(:table_name) AND column_name = UPPER(:column_name)";
            $params = $schema !== '' ? ['schema' => $schema, 'table_name' => $table, 'column_name' => $column] : ['table_name' => $table, 'column_name' => $column];
            $row = Database::fetchOne($this->hospitalCode, $sql, $params);
            $this->columnCache[$key] = ((int)($row['value'] ?? 0)) > 0;
        } catch (Throwable $e) {
            $this->columnCache[$key] = false;
        }
        return $this->columnCache[$key];
    }

    private function isStale($lastUpdated): bool
    {
        if (!$lastUpdated) { return true; }
        $maxAge = (int)\env('EXECUTIVE_SUMMARY_MAX_AGE_MINUTES', '180');
        $ts = strtotime((string)$lastUpdated);
        if (!$ts) { return true; }
        return (time() - $ts) > ($maxAge * 60);
    }

    private function log(string $context, Throwable $e): void
    {
        $dir = function_exists('storage_path') ? storage_path('logs') : __DIR__;
        if (!is_dir($dir)) { @mkdir($dir, 0775, true); }
        @error_log('[' . date('c') . '] ExecutiveSummaryRepository.' . $context . ': ' . $e->getMessage() . PHP_EOL, 3, $dir . '/app.log');
    }
}
