<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\BranchReadiness;
use App\Services\BranchOracleAccessResolver;
use InvalidArgumentException;

final class BranchComparisonRepository
{
    /** @var BranchOracleAccessResolver */
    private $resolver;

    /** @var array<int, string> */
    private const METRICS = [
        'outpatient',
        'admissions',
        'emergency',
        'operations',
        'laboratory',
        'radiology',
    ];

    public function __construct()
    {
        $this->resolver = new BranchOracleAccessResolver();
    }

    /** @return array<int, string> */
    public static function metricKeys(): array
    {
        return self::METRICS;
    }

    /**
     * Executes all six recovered legacy domains for one approved branch and
     * one explicit period in one read-only Oracle statement.
     *
     * @return array<int, array<string, mixed>>
     */
    public function periodRows(
        string $hospitalCode,
        string $fromDate,
        string $toDate
    ): array {
        $hospitalCode = \normalize_hospital_code($hospitalCode);
        if (!BranchReadiness::isLive($hospitalCode)) {
            throw new InvalidArgumentException(
                'BRANCH_NOT_APPROVED_FOR_LIVE_COMPARISON branch=' . strtoupper($hospitalCode)
            );
        }

        return $this->resolver->fetchAll(
            $hospitalCode,
            static function (string $suffix): string {
                return self::buildSql($suffix);
            },
            [
                'branch_compare_from' => $fromDate,
                'branch_compare_to' => $toDate,
            ]
        );
    }

    /**
     * @return array<string, array<string, mixed>>
     */
    public function accessPaths(): array
    {
        return $this->resolver->accessPaths();
    }

    /**
     * @return array<string, mixed>
     */
    public function connectivityDiagnostics(): array
    {
        return $this->resolver->diagnostics();
    }

    private static function buildSql(string $suffix): string
    {
        $visit = 'RCP_VISIT' . $suffix;
        $reservation = 'RCP_SERVICE_RESERVATION' . $suffix;
        $entryExit = 'RCP_ENTRYEXIT' . $suffix;
        $operationSchedule = 'OPER_OPERATION_SCHEDULE' . $suffix;
        $labReservation = 'RCP_LAB_SERVICE_RESERVATION' . $suffix;
        $radReservation = 'RCP_RAD_SERVICE_RESERVATION' . $suffix;
        $months = 'SEC_LOOKUP_DETAIL' . $suffix;

        return "
            WITH
            months AS (
                SELECT TO_NUMBER(code) month_id, TRIM(name_en) month_name
                FROM {$months}
                WHERE parent_id = 1
                  AND TO_NUMBER(code) BETWEEN 1 AND 12
            ),
            outpatient_activity AS (
                SELECT
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                    COUNT(1) value
                FROM {$visit} v
                JOIN {$reservation} r
                  ON v.hosp_id = r.hosp_id
                 AND v.patientid = r.patientid
                 AND v.visit_id = r.visit_id
                WHERE v.classcode = 2
                  AND r.expecteddonedate >= TO_DATE(:branch_compare_from, 'YYYY-MM-DD')
                  AND r.expecteddonedate < TO_DATE(:branch_compare_to, 'YYYY-MM-DD') + 1
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            ),
            admission_activity AS (
                SELECT
                    TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM')) month_id,
                    COUNT(1) value
                FROM {$entryExit} e
                JOIN {$visit} v
                  ON v.hosp_id = e.branchid
                 AND v.patientid = e.patientid
                 AND v.visit_id = e.visit_id
                WHERE v.hosp_id = 1
                  AND NVL(e.baby_normal_bed, 1) = 1
                  AND v.visit_start_date >= TO_DATE(:branch_compare_from, 'YYYY-MM-DD')
                  AND v.visit_start_date < TO_DATE(:branch_compare_to, 'YYYY-MM-DD') + 1
                GROUP BY TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM'))
            ),
            emergency_activity AS (
                SELECT
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                    COUNT(1) value
                FROM {$visit} v
                JOIN {$reservation} r
                  ON v.hosp_id = r.hosp_id
                 AND v.patientid = r.patientid
                 AND v.visit_id = r.visit_id
                WHERE v.classcode = 2
                  AND r.expecteddonedate >= TO_DATE(:branch_compare_from, 'YYYY-MM-DD')
                  AND r.expecteddonedate < TO_DATE(:branch_compare_to, 'YYYY-MM-DD') + 1
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                  -- Preserve the exact legacy discriminator without forcing
                  -- either column onto RCP_SERVICE_RESERVATION. In the live
                  -- Oracle schemas CLINIC_ID is not r.CLINIC_ID; the original
                  -- dashboard deliberately used the unique unqualified columns.
                  AND (
                      NVL(outpta_emergency_type, 0) = 1
                      OR clinic_id IN (16, 297, 298)
                  )
                GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            ),
            operation_activity AS (
                SELECT
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                    COUNT(1) value
                FROM {$reservation} r
                JOIN {$operationSchedule} o
                  ON r.ser = o.ser
                WHERE r.servstatus <> 'C'
                  AND r.expecteddonedate >= TO_DATE(:branch_compare_from, 'YYYY-MM-DD')
                  AND r.expecteddonedate < TO_DATE(:branch_compare_to, 'YYYY-MM-DD') + 1
                GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            ),
            laboratory_activity AS (
                SELECT
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                    COUNT(1) value
                FROM {$labReservation} r
                WHERE r.expecteddonedate <= SYSDATE
                  AND r.expecteddonedate >= TO_DATE(:branch_compare_from, 'YYYY-MM-DD')
                  AND r.expecteddonedate < TO_DATE(:branch_compare_to, 'YYYY-MM-DD') + 1
                GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            ),
            radiology_activity AS (
                SELECT
                    TO_NUMBER(TO_CHAR(r.machine_sch_from, 'MM')) month_id,
                    COUNT(1) value
                FROM {$radReservation} r
                WHERE r.machine_sch_from <= SYSDATE
                  AND r.machine_sch_from >= TO_DATE(:branch_compare_from, 'YYYY-MM-DD')
                  AND r.machine_sch_from < TO_DATE(:branch_compare_to, 'YYYY-MM-DD') + 1
                GROUP BY TO_NUMBER(TO_CHAR(r.machine_sch_from, 'MM'))
            )
            SELECT 'outpatient' metric_key, m.month_id, m.month_name, NVL(a.value, 0) value
            FROM months m
            LEFT JOIN outpatient_activity a ON a.month_id = m.month_id
            UNION ALL
            SELECT 'admissions', m.month_id, m.month_name, NVL(a.value, 0)
            FROM months m
            LEFT JOIN admission_activity a ON a.month_id = m.month_id
            UNION ALL
            SELECT 'emergency', m.month_id, m.month_name, NVL(a.value, 0)
            FROM months m
            LEFT JOIN emergency_activity a ON a.month_id = m.month_id
            UNION ALL
            SELECT 'operations', m.month_id, m.month_name, NVL(a.value, 0)
            FROM months m
            LEFT JOIN operation_activity a ON a.month_id = m.month_id
            UNION ALL
            SELECT 'laboratory', m.month_id, m.month_name, NVL(a.value, 0)
            FROM months m
            LEFT JOIN laboratory_activity a ON a.month_id = m.month_id
            UNION ALL
            SELECT 'radiology', m.month_id, m.month_name, NVL(a.value, 0)
            FROM months m
            LEFT JOIN radiology_activity a ON a.month_id = m.month_id
            -- Oracle compound-query compatibility:
            -- positional ordering avoids ORA-00904 on the first-select alias.
            ORDER BY 1, 2
        ";
    }
}
