<?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)],
        ];
    }

    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;
        }
    }
}
