<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

final class BedsRepository
{
    /** @var string */
    private $hospitalCode;

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

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

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

    /** @return array<string, float> */
    public function currentOccupancy(): array
    {
        $totalBeds = $this->totalActiveBeds();
        $occupiedBeds = $this->currentlyOccupiedBeds();
        if ($totalBeds > 0 && $occupiedBeds > $totalBeds) {
            $totalBeds = $occupiedBeds;
        }
        $availableBeds = max(0.0, $totalBeds - $occupiedBeds);
        $rate = $totalBeds > 0 ? ($occupiedBeds / $totalBeds) * 100 : null;

        return [
            'total_beds' => $totalBeds,
            'occupied_beds' => $occupiedBeds,
            'available_beds' => $availableBeds,
            'occupancy_rate' => $rate,
            'active_units' => $this->activeUnitCount(),
        ];
    }

    /** @return array<string, float> */
    public function annualSummary(int $year, int $compareYear): array
    {
        if (!$this->hasBedTransCore()) {
            return [
                'current_hours' => 0.0,
                'compare_hours' => 0.0,
                'current_utilized_beds' => 0.0,
                'compare_utilized_beds' => 0.0,
                'current_possible_hours' => 0.0,
                'compare_possible_hours' => 0.0,
                'current_efficiency' => null,
                'compare_efficiency' => null,
            ];
        }

        $onExtra = $this->bedTransActiveSql('t', true);
        $sql = "
            WITH p AS (
                SELECT :current_year current_year, :compare_year compare_year FROM dual
            ),
            periods AS (
                SELECT p.current_year yearid,
                       TO_DATE(p.current_year || '-01-01', 'YYYY-MM-DD') start_dt,
                       CASE
                           WHEN p.current_year = EXTRACT(YEAR FROM SYSDATE) THEN SYSDATE
                           ELSE ADD_MONTHS(TO_DATE(p.current_year || '-01-01', 'YYYY-MM-DD'), 12)
                       END end_dt
                FROM p
                UNION ALL
                SELECT p.compare_year yearid,
                       TO_DATE(p.compare_year || '-01-01', 'YYYY-MM-DD') start_dt,
                       CASE
                           WHEN p.compare_year = EXTRACT(YEAR FROM SYSDATE) THEN SYSDATE
                           ELSE ADD_MONTHS(TO_DATE(p.compare_year || '-01-01', 'YYYY-MM-DD'), 12)
                       END end_dt
                FROM p
            )
            SELECT
                pr.yearid,
                NVL(SUM((LEAST(NVL(t.enddate, pr.end_dt), pr.end_dt) - GREATEST(t.startdate, pr.start_dt)) * 24), 0) occupied_hours,
                COUNT(DISTINCT t.bedid) utilized_beds
            FROM periods pr
            LEFT JOIN rcp_bed_trans t
              ON t.startdate < pr.end_dt
             AND NVL(t.enddate, pr.end_dt) > pr.start_dt
             {$onExtra}
            GROUP BY pr.yearid
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'current_year' => $year,
            'compare_year' => $compareYear,
        ]);

        $byYear = [];
        foreach ($rows as $row) {
            $byYear[(int)($row['yearid'] ?? 0)] = [
                'hours' => (float)($row['occupied_hours'] ?? 0),
                'beds' => (float)($row['utilized_beds'] ?? 0),
            ];
        }

        $totalBeds = max(0.0, $this->totalActiveBeds());
        $currentPossible = $totalBeds * $this->periodHours($year);
        $comparePossible = $totalBeds * $this->periodHours($compareYear);
        $currentHours = (float)($byYear[$year]['hours'] ?? 0);
        $compareHours = (float)($byYear[$compareYear]['hours'] ?? 0);

        return [
            'current_hours' => $currentHours,
            'compare_hours' => $compareHours,
            'current_utilized_beds' => (float)($byYear[$year]['beds'] ?? 0),
            'compare_utilized_beds' => (float)($byYear[$compareYear]['beds'] ?? 0),
            'current_possible_hours' => $currentPossible,
            'compare_possible_hours' => $comparePossible,
            'current_efficiency' => $currentPossible > 0 ? ($currentHours / $currentPossible) * 100 : null,
            'compare_efficiency' => $comparePossible > 0 ? ($compareHours / $comparePossible) * 100 : null,
        ];
    }

    /** @return array<int, int> */
    public function availableYears(int $limit = 12): array
    {
        if (!$this->hasBedTransCore()) {
            return [];
        }

        $where = $this->bedTransActiveSql('t', false);
        $limit = max(3, min(30, $limit));
        $sql = "
            SELECT yearid
            FROM (
                SELECT EXTRACT(YEAR FROM t.startdate) yearid,
                       COUNT(1) row_count
                FROM rcp_bed_trans t
                WHERE t.startdate IS NOT NULL
                {$where}
                GROUP BY EXTRACT(YEAR FROM t.startdate)
                ORDER BY EXTRACT(YEAR FROM t.startdate) DESC
            )
            WHERE ROWNUM <= :row_limit
        ";

        try {
            $rows = Database::fetchAll($this->hospitalCode, $sql, [
                'row_limit' => $limit,
            ]);
        } catch (Throwable $e) {
            return [];
        }

        $years = [];
        foreach ($rows as $row) {
            $value = (int)($row['yearid'] ?? 0);
            if ($value > 0 && !in_array($value, $years, true)) {
                $years[] = $value;
            }
        }
        return $years;
    }

    /** @return array<int, array<string, mixed>> */
    public function unitOccupancy(int $year, int $limit = 12): array
    {
        if (!$this->hasBedTransCore() || !$this->tableExists('STP_BEDS') || !$this->columnExists('STP_BEDS', 'BEDID')) {
            return [];
        }

        $limit = max(5, min(30, $limit));
        $bedWhere = $this->stpBedsActiveSql('b');
        $transWhere = $this->bedTransActiveSql('t', false);
        $joins = $this->bedDimensionJoins('b');
        $unitIdExpr = $this->unitIdExpr();
        $unitLabelExpr = $this->unitLabelExpr();

        $sql = "
            WITH p AS (
                SELECT TO_DATE(:year || '-01-01', 'YYYY-MM-DD') start_dt,
                       CASE
                           WHEN :year = EXTRACT(YEAR FROM SYSDATE) THEN SYSDATE
                           ELSE ADD_MONTHS(TO_DATE(:year || '-01-01', 'YYYY-MM-DD'), 12)
                       END end_dt
                FROM dual
            ),
            bed_units AS (
                SELECT
                    b.bedid,
                    {$unitIdExpr} unit_id,
                    {$unitLabelExpr} unit_label
                FROM stp_beds b
                {$joins}
                WHERE 1 = 1
                {$bedWhere}
                GROUP BY b.bedid, {$unitIdExpr}
            ),
            occ AS (
                SELECT
                    t.bedid,
                    SUM((LEAST(NVL(t.enddate, p.end_dt), p.end_dt) - GREATEST(t.startdate, p.start_dt)) * 24) occupied_hours
                FROM rcp_bed_trans t
                CROSS JOIN p
                WHERE t.startdate < p.end_dt
                  AND NVL(t.enddate, p.end_dt) > p.start_dt
                {$transWhere}
                GROUP BY t.bedid
            ),
            ph AS (
                SELECT (end_dt - start_dt) * 24 period_hours FROM p
            )
            SELECT unit_id, unit_label, bed_count, occupied_hours, possible_hours,
                   ROUND(CASE WHEN possible_hours > 0 THEN (occupied_hours / possible_hours) * 100 ELSE 0 END, 2) occupancy_pct
            FROM (
                SELECT
                    bu.unit_id,
                    NVL(MAX(bu.unit_label), 'Unknown unit') unit_label,
                    COUNT(DISTINCT bu.bedid) bed_count,
                    NVL(SUM(occ.occupied_hours), 0) occupied_hours,
                    ph.period_hours * COUNT(DISTINCT bu.bedid) possible_hours
                FROM bed_units bu
                LEFT JOIN occ ON occ.bedid = bu.bedid
                CROSS JOIN ph
                GROUP BY bu.unit_id, ph.period_hours
                ORDER BY ROUND(CASE WHEN ph.period_hours * COUNT(DISTINCT bu.bedid) > 0 THEN (NVL(SUM(occ.occupied_hours), 0) / (ph.period_hours * COUNT(DISTINCT bu.bedid))) * 100 ELSE 0 END, 2) DESC
            )
            WHERE ROWNUM <= :row_limit
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'year' => $year,
            'row_limit' => $limit,
        ]);

        return array_map(function (array $row): array {
            return [
                'id' => (string)($row['unit_id'] ?? ''),
                'label' => trim((string)($row['unit_label'] ?? 'Unknown unit')),
                'bed_count' => (float)($row['bed_count'] ?? 0),
                'occupied_hours' => (float)($row['occupied_hours'] ?? 0),
                'possible_hours' => (float)($row['possible_hours'] ?? 0),
                'value' => (float)($row['occupancy_pct'] ?? 0),
            ];
        }, $rows);
    }

    /** @return array<int, array<string, mixed>> */
    public function topBeds(int $year, int $limit = 15): array
    {
        if (!$this->hasBedTransCore() || !$this->tableExists('STP_BEDS') || !$this->columnExists('STP_BEDS', 'BEDID')) {
            return [];
        }

        $limit = max(5, min(50, $limit));
        $bedWhere = $this->stpBedsActiveSql('b');
        $transWhere = $this->bedTransActiveSql('t', false);
        $joins = $this->bedDimensionJoins('b');
        $unitLabelExpr = $this->unitLabelExpr();
        $bedLabelExpr = $this->bedLabelExpr();

        $sql = "
            WITH p AS (
                SELECT TO_DATE(:year || '-01-01', 'YYYY-MM-DD') start_dt,
                       CASE
                           WHEN :year = EXTRACT(YEAR FROM SYSDATE) THEN SYSDATE
                           ELSE ADD_MONTHS(TO_DATE(:year || '-01-01', 'YYYY-MM-DD'), 12)
                       END end_dt
                FROM dual
            ),
            bed_base AS (
                SELECT
                    b.bedid,
                    {$bedLabelExpr} bed_label,
                    {$unitLabelExpr} unit_label
                FROM stp_beds b
                {$joins}
                WHERE 1 = 1
                {$bedWhere}
                GROUP BY b.bedid
            ),
            occ AS (
                SELECT
                    t.bedid,
                    SUM((LEAST(NVL(t.enddate, p.end_dt), p.end_dt) - GREATEST(t.startdate, p.start_dt)) * 24) occupied_hours
                FROM rcp_bed_trans t
                CROSS JOIN p
                WHERE t.startdate < p.end_dt
                  AND NVL(t.enddate, p.end_dt) > p.start_dt
                {$transWhere}
                GROUP BY t.bedid
            ),
            ph AS (
                SELECT (end_dt - start_dt) * 24 period_hours FROM p
            )
            SELECT bedid, bed_label, unit_label, occupied_hours,
                   ROUND(CASE WHEN period_hours > 0 THEN (occupied_hours / period_hours) * 100 ELSE 0 END, 2) occupancy_pct
            FROM (
                SELECT
                    bb.bedid,
                    NVL(MAX(bb.bed_label), 'Bed ' || bb.bedid) bed_label,
                    NVL(MAX(bb.unit_label), 'Unknown unit') unit_label,
                    NVL(MAX(occ.occupied_hours), 0) occupied_hours,
                    ph.period_hours period_hours
                FROM bed_base bb
                LEFT JOIN occ ON occ.bedid = bb.bedid
                CROSS JOIN ph
                GROUP BY bb.bedid, ph.period_hours
                ORDER BY NVL(MAX(occ.occupied_hours), 0) DESC
            )
            WHERE ROWNUM <= :row_limit
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'year' => $year,
            'row_limit' => $limit,
        ]);

        return array_map(function (array $row): array {
            return [
                'id' => (string)($row['bedid'] ?? ''),
                'label' => trim((string)($row['bed_label'] ?? '')),
                'secondary' => trim((string)($row['unit_label'] ?? '')),
                'occupied_hours' => (float)($row['occupied_hours'] ?? 0),
                'value' => (float)($row['occupancy_pct'] ?? 0),
            ];
        }, $rows);
    }

    /** @return array<int, array<string, mixed>> */
    public function occupancyMixRows(array $current): array
    {
        return [
            ['label' => 'Occupied', 'value' => (float)($current['occupied_beds'] ?? 0)],
            ['label' => 'Available', 'value' => (float)($current['available_beds'] ?? 0)],
        ];
    }


    /**
     * Phase 10.14 current implementation of the legacy bcor contract.
     * This intentionally preserves the legacy hospital/unit exclusions and snapshot semantics.
     * @return array<int, array{status:string,rate:float}>
     */
    public function legacyCompatibleCurrentOccupancyRows(): array
    {
        $sql = "
            SELECT 'Busy' status, COUNT(1) rate
            FROM rcp_bed_trans t
            JOIN stp_places pa
              ON pa.place_id1 = t.bedid
             AND pa.hosp_id = t.hosp_id
            JOIN stp_places pb
              ON pb.place_id1 = pa.parent_place_id
            JOIN stp_hrc h
              ON h.unit_id = pa.unit_id
            LEFT JOIN rcp_entryexit ee
              ON ee.patientid = t.patientid
             AND ee.branchid = t.hosp_id
             AND ee.visit_id = t.visit_id
            WHERE h.unit_type = '2'
              AND t.comp_pat_flag = 'P'
              AND pa.place_level = 4
              AND t.enddate IS NULL
              AND pa.hosp_id IN (1)
              AND pb.view_occupation = 1
              AND pa.unit_id NOT IN (7301)
              AND NVL(ee.baby_normal_bed, 1) = 1
            UNION ALL
            SELECT 'Empty' status,
                   (
                       SELECT COUNT(1)
                       FROM stp_places pa
                       JOIN stp_places pb ON pb.place_id1 = pa.parent_place_id
                       JOIN stp_hrc h ON h.unit_id = pa.unit_id
                       LEFT JOIN stp_beds b
                         ON b.bedid = pa.place_id1
                        AND b.hosp_id = pa.hosp_id
                       WHERE h.unit_type = '2'
                         AND pa.place_level = 4
                         AND pa.hosp_id IN (1)
                         AND pb.view_occupation = 1
                         AND b.active_bed = 1
                         AND pa.unit_id NOT IN (7301)
                         AND NOT (pa.unit_id IN (3112) AND pb.place_id1 = 215)
                         AND NVL(b.baby_normal_bed, 1) = 1
                   )
                   -
                   (
                       SELECT COUNT(1)
                       FROM rcp_bed_trans t
                       JOIN stp_places pa
                         ON pa.place_id1 = t.bedid
                        AND pa.hosp_id = t.hosp_id
                       JOIN stp_places pb ON pb.place_id1 = pa.parent_place_id
                       JOIN stp_hrc h ON h.unit_id = pa.unit_id
                       WHERE h.unit_type = '2'
                         AND t.comp_pat_flag = 'P'
                         AND pa.place_level = 4
                         AND t.enddate IS NULL
                         AND pa.hosp_id IN (1)
                         AND pb.view_occupation = 1
                         AND pa.unit_id NOT IN (7301)
                         AND NVL(pa.baby_normal_bed, 1) = 1
                   ) rate
            FROM dual
        ";
        return $this->normalizeSnapshotRows(Database::fetchAll($this->hospitalCode, $sql));
    }

    /**
     * Current implementation of the legacy get_units_effic contract.
     * @return array<int, array<string, mixed>>
     */
    public function legacyCompatibleUnitEfficiencyRows(
        int $year,
        ?string $fromDate = null,
        ?string $toDate = null,
        int $unitId = 0
    ): array {
        $period = self::legacyPeriodContract($year, $fromDate, $toDate);
        $dateWhere = $period['mode'] === 'range'
            ? "AND TO_DATE(t.startdate) >= TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(t.startdate) <= TO_DATE(:to_date, 'YYYY-MM-DD')"
            : 'AND EXTRACT(YEAR FROM t.startdate) = :year';

        $sql = "
            WITH bed_occupancy AS (
                SELECT t.bedid,
                       p.unit_id,
                       SUM((t.enddate - t.startdate) * 24) total_hours_occupied
                FROM rcp_bed_trans t
                JOIN stp_beds b ON t.bedid = b.bedid
                JOIN stp_places p ON b.roomid = p.place_id1
                WHERE (p.unit_id = :unit_id OR :unit_id = 0)
                  {$dateWhere}
                GROUP BY t.bedid, p.unit_id
            ),
            unit_summary AS (
                SELECT unit_id,
                       COUNT(DISTINCT bedid) bed_count,
                       SUM(total_hours_occupied) total_hours_occupied
                FROM bed_occupancy
                GROUP BY unit_id
            ),
            total_period_hours AS (
                SELECT (TO_DATE(:period_end, 'YYYY-MM-DD') - TO_DATE(:period_start, 'YYYY-MM-DD')) * 24 total_hours_period
                FROM dual
            )
            SELECT unit_summary.unit_id,
                   (SELECT unit_en_name FROM stp_hrc WHERE stp_hrc.unit_id = unit_summary.unit_id) bed_floor,
                   unit_summary.bed_count,
                   ROUND(unit_summary.total_hours_occupied, 2) total_hours_occupied,
                   total_period_hours.total_hours_period * unit_summary.bed_count total_hours_period,
                   (total_period_hours.total_hours_period * unit_summary.bed_count) - ROUND(unit_summary.total_hours_occupied, 2) empty_hours,
                   ROUND((unit_summary.total_hours_occupied / (total_period_hours.total_hours_period * unit_summary.bed_count)) * 100, 2) bed_efficiency_percentage
            FROM unit_summary, total_period_hours
            ORDER BY bed_efficiency_percentage DESC
        ";

        $params = [
            'year' => $year,
            'unit_id' => $unitId,
            'period_start' => $period['period_start'],
            'period_end' => $period['period_end'],
        ];
        if ($period['mode'] === 'range') {
            $params['from_date'] = $period['from_date'];
            $params['to_date'] = $period['to_date'];
        }
        return $this->normalizeUnitEfficiencyRows(Database::fetchAll($this->hospitalCode, $sql, $params));
    }

    /**
     * Current implementation of the legacy get_units_effic_b contract.
     * @return array<int, array<string, mixed>>
     */
    public function legacyCompatibleBedEfficiencyRows(
        int $year,
        int $unitId,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        if ($unitId <= 0) {
            return [];
        }
        $period = self::legacyPeriodContract($year, $fromDate, $toDate);
        $dateWhere = $period['mode'] === 'range'
            ? "AND TO_DATE(t.startdate) >= TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(t.startdate) <= TO_DATE(:to_date, 'YYYY-MM-DD')"
            : 'AND EXTRACT(YEAR FROM t.startdate) = :year';

        $sql = "
            WITH bed_occupancy AS (
                SELECT t.bedid,
                       p.place_en_name,
                       p.unit_id,
                       SUM((t.enddate - t.startdate) * 24) total_hours_occupied
                FROM rcp_bed_trans t
                JOIN stp_beds b ON t.bedid = b.bedid
                JOIN stp_places p ON b.roomid = p.place_id1
                WHERE p.unit_id = :unit_id
                  {$dateWhere}
                GROUP BY t.bedid, p.place_en_name, p.unit_id
            ),
            total_period_hours AS (
                SELECT (TO_DATE(:period_end, 'YYYY-MM-DD') - TO_DATE(:period_start, 'YYYY-MM-DD')) * 24 total_hours_period
                FROM dual
            )
            SELECT bed_occupancy.place_en_name || ' - ' ||
                       (SELECT place_en_name FROM stp_places WHERE place_id1 = bed_occupancy.bedid) bed_name,
                   bed_occupancy.unit_id,
                   (SELECT unit_en_name FROM stp_hrc WHERE unit_id = bed_occupancy.unit_id) bed_floor,
                   ROUND(bed_occupancy.total_hours_occupied, 2) total_hours_occupied,
                   total_period_hours.total_hours_period,
                   ROUND((bed_occupancy.total_hours_occupied / total_period_hours.total_hours_period) * 100, 2) bed_efficiency_percentage
            FROM bed_occupancy, total_period_hours
            ORDER BY unit_id, bed_efficiency_percentage DESC
        ";

        $params = [
            'year' => $year,
            'unit_id' => $unitId,
            'period_start' => $period['period_start'],
            'period_end' => $period['period_end'],
        ];
        if ($period['mode'] === 'range') {
            $params['from_date'] = $period['from_date'];
            $params['to_date'] = $period['to_date'];
        }
        return $this->normalizeBedEfficiencyRows(Database::fetchAll($this->hospitalCode, $sql, $params));
    }

    /** @return array{mode:string,period_start:string,period_end:string,from_date:?string,to_date:?string} */
    public static function legacyPeriodContract(int $year, ?string $fromDate = null, ?string $toDate = null): array
    {
        if ($fromDate !== null && $toDate !== null && $fromDate !== '' && $toDate !== '') {
            return [
                'mode' => 'range',
                'period_start' => $fromDate,
                'period_end' => $toDate,
                'from_date' => $fromDate,
                'to_date' => $toDate,
            ];
        }
        $end = $year === (int)date('Y') ? date('Y-m-d') : sprintf('%04d-12-31', $year);
        return [
            'mode' => 'year',
            'period_start' => sprintf('%04d-01-01', $year),
            'period_end' => $end,
            'from_date' => null,
            'to_date' => null,
        ];
    }

    /** @param array<int,array<string,mixed>> $rows @return array<int,array{status:string,rate:float}> */
    private function normalizeSnapshotRows(array $rows): array
    {
        return array_map(static function (array $row): array {
            return ['status' => trim((string)($row['status'] ?? '')), 'rate' => (float)($row['rate'] ?? 0)];
        }, $rows);
    }

    /** @param array<int,array<string,mixed>> $rows @return array<int,array<string,mixed>> */
    private function normalizeUnitEfficiencyRows(array $rows): array
    {
        return array_map(static function (array $row): array {
            return [
                'unit_id' => (int)($row['unit_id'] ?? 0),
                'bed_floor' => trim((string)($row['bed_floor'] ?? '')),
                'bed_count' => (float)($row['bed_count'] ?? 0),
                'total_hours_occupied' => (float)($row['total_hours_occupied'] ?? 0),
                'total_hours_period' => (float)($row['total_hours_period'] ?? 0),
                'empty_hours' => (float)($row['empty_hours'] ?? 0),
                'bed_efficiency_percentage' => (float)($row['bed_efficiency_percentage'] ?? 0),
            ];
        }, $rows);
    }

    /** @param array<int,array<string,mixed>> $rows @return array<int,array<string,mixed>> */
    private function normalizeBedEfficiencyRows(array $rows): array
    {
        return array_map(static function (array $row): array {
            return [
                'bed_name' => trim((string)($row['bed_name'] ?? '')),
                'unit_id' => (int)($row['unit_id'] ?? 0),
                'bed_floor' => trim((string)($row['bed_floor'] ?? '')),
                'total_hours_occupied' => (float)($row['total_hours_occupied'] ?? 0),
                'total_hours_period' => (float)($row['total_hours_period'] ?? 0),
                'bed_efficiency_percentage' => (float)($row['bed_efficiency_percentage'] ?? 0),
            ];
        }, $rows);
    }

    private function hasBedTransCore(): bool
    {
        return $this->tableExists('RCP_BED_TRANS')
            && $this->columnExists('RCP_BED_TRANS', 'BEDID')
            && $this->columnExists('RCP_BED_TRANS', 'STARTDATE')
            && $this->columnExists('RCP_BED_TRANS', 'ENDDATE');
    }

    private function totalActiveBeds(): float
    {
        if ($this->tableExists('STP_BEDS') && $this->columnExists('STP_BEDS', 'BEDID')) {
            $where = $this->stpBedsActiveSql('b');
            $sql = "SELECT COUNT(DISTINCT b.bedid) value FROM stp_beds b WHERE 1 = 1 {$where}";
            return $this->scalarFloat($sql, []);
        }

        if ($this->tableExists('RCP_BED_TRANS') && $this->columnExists('RCP_BED_TRANS', 'BEDID')) {
            return $this->scalarFloat('SELECT COUNT(DISTINCT bedid) value FROM rcp_bed_trans', []);
        }

        return 0.0;
    }

    private function currentlyOccupiedBeds(): float
    {
        if (!$this->tableExists('RCP_BED_TRANS') || !$this->columnExists('RCP_BED_TRANS', 'BEDID')) {
            return 0.0;
        }

        $where = '';
        if ($this->columnExists('RCP_BED_TRANS', 'ENDDATE')) {
            $where .= ' AND t.enddate IS NULL';
        }
        if ($this->columnExists('RCP_BED_TRANS', 'COMP_PAT_FLAG')) {
            $where .= " AND NVL(t.comp_pat_flag, 'P') = 'P'";
        }

        $sql = "SELECT COUNT(DISTINCT t.bedid) value FROM rcp_bed_trans t WHERE 1 = 1 {$where}";
        return $this->scalarFloat($sql, []);
    }

    private function activeUnitCount(): float
    {
        if ($this->tableExists('STP_BEDS') && $this->columnExists('STP_BEDS', 'BEDID') && $this->tableExists('STP_PLACES') && $this->columnExists('STP_BEDS', 'ROOMID') && $this->columnExists('STP_PLACES', 'PLACE_ID1') && $this->columnExists('STP_PLACES', 'UNIT_ID')) {
            $where = $this->stpBedsActiveSql('b');
            $sql = "
                SELECT COUNT(DISTINCT pl.unit_id) value
                FROM stp_beds b
                JOIN stp_places pl ON b.roomid = pl.place_id1
                WHERE pl.unit_id IS NOT NULL
                {$where}
            ";
            return $this->scalarFloat($sql, []);
        }
        return 0.0;
    }

    /**
     * @return string SQL condition. When $forJoin is true the returned expression starts with AND and is safe inside JOIN ON.
     */
    private function bedTransActiveSql(string $alias, bool $forJoin): string
    {
        $conditions = '';
        if ($this->columnExists('RCP_BED_TRANS', 'COMP_PAT_FLAG')) {
            $conditions .= " AND NVL({$alias}.comp_pat_flag, 'P') = 'P'";
        }
        return $conditions;
    }

    private function stpBedsActiveSql(string $alias): string
    {
        $conditions = '';
        if ($this->columnExists('STP_BEDS', 'ACTIVE_BED')) {
            $conditions .= " AND NVL({$alias}.active_bed, 1) = 1";
        }
        if ($this->columnExists('STP_BEDS', 'BABY_NORMAL_BED')) {
            $conditions .= " AND NVL({$alias}.baby_normal_bed, 1) = 1";
        }
        return $conditions;
    }

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

        $canJoinBedPlace = $this->tableExists('STP_PLACES') && $this->columnExists('STP_PLACES', 'PLACE_ID1');
        if ($canJoinBedPlace) {
            $joins .= "\n                LEFT JOIN stp_places bp ON {$bedAlias}.bedid = bp.place_id1";
        }

        $canJoinHrc = $this->tableExists('STP_HRC') && $this->columnExists('STP_HRC', 'UNIT_ID') && $this->columnExists('STP_PLACES', 'UNIT_ID');
        if ($canJoinHrc && $canJoinRoom) {
            $joins .= "\n                LEFT JOIN stp_hrc h ON h.unit_id = pl.unit_id";
        }
        return $joins;
    }

    private function unitIdExpr(): string
    {
        if ($this->tableExists('STP_PLACES') && $this->columnExists('STP_PLACES', 'UNIT_ID') && $this->columnExists('STP_BEDS', 'ROOMID')) {
            return 'NVL(pl.unit_id, 0)';
        }
        return '0';
    }

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

    private function bedLabelExpr(): string
    {
        $hasRoomName = $this->tableExists('STP_PLACES') && $this->columnExists('STP_PLACES', 'PLACE_EN_NAME') && $this->columnExists('STP_BEDS', 'ROOMID');
        if ($hasRoomName) {
            return "NVL(MAX(pl.place_en_name || CASE WHEN bp.place_en_name IS NOT NULL THEN ' - ' || bp.place_en_name ELSE '' END), 'Bed ' || b.bedid)";
        }
        return "'Bed ' || b.bedid";
    }

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

    private function periodHours(int $year): float
    {
        $start = new \DateTimeImmutable(sprintf('%04d-01-01 00:00:00', $year));
        $currentYear = (int)date('Y');
        $end = $year === $currentYear
            ? new \DateTimeImmutable('now')
            : $start->modify('+1 year');
        return max(0.0, ($end->getTimestamp() - $start->getTimestamp()) / 3600);
    }

    private function columnExists(string $table, string $column): bool
    {
        $schema = strtoupper((string)\config('hospitals.' . $this->hospitalCode . '.database.schema', ''));
        $cacheKey = $schema . '.' . strtoupper($table) . '.' . strtoupper($column);
        if (array_key_exists($cacheKey, $this->columnCache)) {
            return $this->columnCache[$cacheKey];
        }

        $sql = "
            SELECT 1 ok
            FROM all_tab_columns
            WHERE table_name = UPPER(:table_name)
              AND column_name = UPPER(:column_name)
              AND (:owner_name IS NULL OR owner = UPPER(:owner_name))
              AND ROWNUM <= 1
        ";

        try {
            $rows = Database::fetchAll($this->hospitalCode, $sql, [
                'table_name' => $table,
                'column_name' => $column,
                'owner_name' => $schema !== '' ? $schema : null,
            ]);
            $this->columnCache[$cacheKey] = !empty($rows);
            return $this->columnCache[$cacheKey];
        } catch (Throwable $e) {
            $this->columnCache[$cacheKey] = false;
            return false;
        }
    }

    private function tableExists(string $table): bool
    {
        $schema = strtoupper((string)\config('hospitals.' . $this->hospitalCode . '.database.schema', ''));
        $cacheKey = $schema . '.' . strtoupper($table);
        if (array_key_exists($cacheKey, $this->tableCache)) {
            return $this->tableCache[$cacheKey];
        }

        $sql = "
            SELECT 1 ok
            FROM all_tables
            WHERE table_name = UPPER(:table_name)
              AND (:owner_name IS NULL OR owner = UPPER(:owner_name))
              AND ROWNUM <= 1
        ";

        try {
            $rows = Database::fetchAll($this->hospitalCode, $sql, [
                'table_name' => $table,
                'owner_name' => $schema !== '' ? $schema : null,
            ]);
            $this->tableCache[$cacheKey] = !empty($rows);
            return $this->tableCache[$cacheKey];
        } catch (Throwable $e) {
            $this->tableCache[$cacheKey] = false;
            return false;
        }
    }
}
