<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;

/**
 * Independent execution path for the exact legacy dashboard functions:
 * bcor, get_units_effic and get_units_effic_b.
 */
final class LegacyBedsRepository
{
    /** @var string */
    private $hospitalCode;

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

    /** @return array<int, array{status:string,rate:float}> */
    public function currentOccupancyRows(): array
    {
        $sql = "
            SELECT 'Busy' Status, COUNT(1) rate
            FROM RCP_BED_TRANS,
                 STP_PLACES STP_PLACES_A,
                 STP_PLACES STP_PLACES_B,
                 STP_HRC,
                 RCP_ENTRYEXIT
            WHERE (RCP_BED_TRANS.PATIENTID = RCP_ENTRYEXIT.PATIENTID (+))
              AND (RCP_BED_TRANS.HOSP_ID = RCP_ENTRYEXIT.BRANCHID (+))
              AND (RCP_BED_TRANS.VISIT_ID = RCP_ENTRYEXIT.VISIT_ID (+))
              AND STP_HRC.UNIT_TYPE = '2'
              AND STP_PLACES_A.PLACE_ID1 = RCP_BED_TRANS.BEDID
              AND STP_PLACES_A.HOSP_ID = RCP_BED_TRANS.HOSP_ID
              AND STP_PLACES_B.PLACE_ID1 = STP_PLACES_A.PARENT_PLACE_ID
              AND STP_PLACES_A.UNIT_ID = STP_HRC.UNIT_ID
              AND RCP_BED_TRANS.COMP_PAT_FLAG = 'P'
              AND STP_PLACES_A.PLACE_LEVEL = 4
              AND RCP_BED_TRANS.ENDDATE IS NULL
              AND STP_PLACES_A.HOSP_ID IN (1)
              AND STP_PLACES_B.VIEW_OCCUPATION = 1
              AND STP_PLACES_A.UNIT_ID NOT IN (7301)
              AND NVL(RCP_ENTRYEXIT.BABY_NORMAL_BED, 1) = 1
            UNION ALL
            SELECT 'Empty' Status,
                   (SELECT COUNT(1)
                    FROM STP_PLACES STP_PLACES_A,
                         STP_PLACES STP_PLACES_B,
                         STP_BEDS,
                         STP_HRC
                    WHERE STP_HRC.UNIT_TYPE = '2'
                      AND STP_PLACES_A.PLACE_ID1 = STP_BEDS.BEDID (+)
                      AND STP_PLACES_A.HOSP_ID = STP_BEDS.HOSP_ID (+)
                      AND STP_PLACES_A.PARENT_PLACE_ID = STP_PLACES_B.PLACE_ID1
                      AND STP_PLACES_A.UNIT_ID = STP_HRC.UNIT_ID
                      AND STP_PLACES_A.PLACE_LEVEL = 4
                      AND STP_PLACES_A.HOSP_ID IN (1)
                      AND STP_PLACES_B.VIEW_OCCUPATION = 1
                      AND STP_BEDS.ACTIVE_BED = 1
                      AND STP_PLACES_A.UNIT_ID NOT IN (7301)
                      AND NOT (STP_PLACES_A.UNIT_ID IN (3112) AND STP_PLACES_B.PLACE_ID1 = 215)
                      AND NVL(STP_BEDS.BABY_NORMAL_BED, 1) = 1)
                   -
                   (SELECT COUNT(1)
                    FROM RCP_BED_TRANS,
                         STP_PLACES STP_PLACES_A,
                         STP_PLACES STP_PLACES_B,
                         STP_HRC
                    WHERE STP_HRC.UNIT_TYPE = '2'
                      AND STP_PLACES_A.PLACE_ID1 = RCP_BED_TRANS.BEDID
                      AND STP_PLACES_A.HOSP_ID = RCP_BED_TRANS.HOSP_ID
                      AND STP_PLACES_B.PLACE_ID1 = STP_PLACES_A.PARENT_PLACE_ID
                      AND STP_PLACES_A.UNIT_ID = STP_HRC.UNIT_ID
                      AND RCP_BED_TRANS.COMP_PAT_FLAG = 'P'
                      AND STP_PLACES_A.PLACE_LEVEL = 4
                      AND RCP_BED_TRANS.ENDDATE IS NULL
                      AND STP_PLACES_A.HOSP_ID IN (1)
                      AND STP_PLACES_B.VIEW_OCCUPATION = 1
                      AND STP_PLACES_A.UNIT_ID NOT IN (7301)
                      AND NVL(STP_PLACES_A.BABY_NORMAL_BED, 1) = 1) rate
            FROM dual
        ";
        $rows = Database::fetchAll($this->hospitalCode, $sql);
        return array_map(static function (array $row): array {
            return ['status' => trim((string)($row['status'] ?? '')), 'rate' => (float)($row['rate'] ?? 0)];
        }, $rows);
    }

    /** @return array<int, array<string,mixed>> */
    public function unitEfficiencyRows(int $year, ?string $fromDate = null, ?string $toDate = null, int $unitId = 0): array
    {
        $period = BedsRepository::legacyPeriodContract($year, $fromDate, $toDate);
        $where = $period['mode'] === 'range'
            ? "AND TO_DATE(rcp_bed_trans.startdate) >= TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(rcp_bed_trans.startdate) <= TO_DATE(:to_date, 'YYYY-MM-DD')"
            : 'AND EXTRACT(YEAR FROM rcp_bed_trans.startdate) = :year';

        $sql = "
            WITH bed_occupancy AS (
                SELECT rcp_bed_trans.bedid,
                       stp_places.unit_id,
                       SUM((rcp_bed_trans.enddate - rcp_bed_trans.startdate) * 24) total_hours_occupied
                FROM rcp_bed_trans
                JOIN stp_beds ON rcp_bed_trans.bedid = stp_beds.bedid
                JOIN stp_places ON stp_beds.roomid = stp_places.place_id1
                WHERE (stp_places.unit_id = :unit_id OR :unit_id = 0)
                  {$where}
                GROUP BY rcp_bed_trans.bedid, stp_places.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->normalizeUnits(Database::fetchAll($this->hospitalCode, $sql, $params));
    }

    /** @return array<int, array<string,mixed>> */
    public function bedEfficiencyRows(int $year, int $unitId, ?string $fromDate = null, ?string $toDate = null): array
    {
        if ($unitId <= 0) {
            return [];
        }
        $period = BedsRepository::legacyPeriodContract($year, $fromDate, $toDate);
        $where = $period['mode'] === 'range'
            ? "AND TO_DATE(rcp_bed_trans.startdate) >= TO_DATE(:from_date, 'YYYY-MM-DD') AND TO_DATE(rcp_bed_trans.startdate) <= TO_DATE(:to_date, 'YYYY-MM-DD')"
            : 'AND EXTRACT(YEAR FROM rcp_bed_trans.startdate) = :year';

        $sql = "
            WITH bed_occupancy AS (
                SELECT rcp_bed_trans.bedid,
                       stp_places.place_en_name,
                       stp_places.unit_id,
                       SUM((rcp_bed_trans.enddate - rcp_bed_trans.startdate) * 24) total_hours_occupied
                FROM rcp_bed_trans
                JOIN stp_beds ON rcp_bed_trans.bedid = stp_beds.bedid
                JOIN stp_places ON stp_beds.roomid = stp_places.place_id1
                WHERE stp_places.unit_id = :unit_id
                  {$where}
                GROUP BY rcp_bed_trans.bedid, stp_places.place_en_name, stp_places.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->normalizeBeds(Database::fetchAll($this->hospitalCode, $sql, $params));
    }

    /** @param array<int,array<string,mixed>> $rows @return array<int,array<string,mixed>> */
    private function normalizeUnits(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 normalizeBeds(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);
    }
}
