<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

final class VisitsRepository
{
    /** @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);
    }

    /**
     * Current selected-branch implementation of legacy gmov_s.
     *
     * @return array<int, array<string, mixed>>
     */
    public function monthlyOutpatientVisits(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        return $this->monthlyVisitRows('outpatient', $year, $compareYear, $fromDate, $toDate);
    }

    /**
     * Current selected-branch implementation of legacy perv_s.
     *
     * @return array<int, array<string, mixed>>
     */
    public function monthlyEmergencyVisits(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        return $this->monthlyVisitRows('emergency', $year, $compareYear, $fromDate, $toDate);
    }

    /**
     * Consolidated monthly OPD + Emergency bundle for the Visits page.
     *
     * It preserves the existing outpatient/emergency classification contracts
     * while scanning the reservation source once instead of twice.
     *
     * @return array{opd:array<int,array<string,mixed>>,er:array<int,array<string,mixed>>}
     */
    public function monthlyVisitBundle(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $conditions = $this->visitClassificationConditions();
        $opdCondition = $conditions['outpatient'];
        $erCondition = $conditions['emergency'];

        if ($fromDate !== null && $toDate !== null) {
            $compareFrom = RevenueRepository::shiftDateToYear($fromDate, $compareYear);
            $compareTo = RevenueRepository::shiftDateToYear($toDate, $compareYear);

            $sql = "
                WITH months AS (
                    SELECT code month_id, name_en month_name
                    FROM sec_lookup_detail
                    WHERE parent_id = 1
                ),
                agg AS (
                    SELECT
                        CASE
                            WHEN r.expecteddonedate >= TO_DATE(:current_from, 'YYYY-MM-DD')
                             AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD') + 1
                            THEN 'CURRENT'
                            ELSE 'COMPARE'
                        END period_key,
                        TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                        SUM(CASE WHEN {$opdCondition} THEN 1 ELSE 0 END) opd_value,
                        SUM(CASE WHEN {$erCondition} THEN 1 ELSE 0 END) er_value
                    FROM rcp_visit v
                    JOIN rcp_service_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(:current_from, 'YYYY-MM-DD')
                                AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD') + 1
                            )
                            OR
                            (
                                r.expecteddonedate >= TO_DATE(:compare_from, 'YYYY-MM-DD')
                                AND r.expecteddonedate < TO_DATE(:compare_to, 'YYYY-MM-DD') + 1
                            )
                      )
                      AND r.servstatus NOT IN ('C', 'F')
                      AND r.sched_serial IS NOT NULL
                    GROUP BY
                        CASE
                            WHEN r.expecteddonedate >= TO_DATE(:current_from, 'YYYY-MM-DD')
                             AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD') + 1
                            THEN 'CURRENT'
                            ELSE 'COMPARE'
                        END,
                        TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
                )
                SELECT
                    m.month_id,
                    m.month_name,
                    NVL(MAX(CASE WHEN a.period_key = 'CURRENT' THEN a.opd_value END), 0) opd_current_value,
                    NVL(MAX(CASE WHEN a.period_key = 'COMPARE' THEN a.opd_value END), 0) opd_compare_value,
                    NVL(MAX(CASE WHEN a.period_key = 'CURRENT' THEN a.er_value END), 0) er_current_value,
                    NVL(MAX(CASE WHEN a.period_key = 'COMPARE' THEN a.er_value END), 0) er_compare_value
                FROM months m
                LEFT JOIN agg a ON a.month_id = m.month_id
                GROUP BY m.month_id, m.month_name
                ORDER BY m.month_id
            ";

            $rows = Database::fetchAll($this->hospitalCode, $sql, [
                'current_from' => $fromDate,
                'current_to' => $toDate,
                'compare_from' => $compareFrom,
                'compare_to' => $compareTo,
            ]);

            return $this->normalizeMonthlyVisitBundle($rows);
        }

        $currentFrom = sprintf('%04d-01-01', $year);
        $currentTo = sprintf('%04d-01-01', $year + 1);
        $compareFrom = sprintf('%04d-01-01', $compareYear);
        $compareTo = sprintf('%04d-01-01', $compareYear + 1);

        $sql = "
            WITH months AS (
                SELECT code month_id, name_en month_name
                FROM sec_lookup_detail
                WHERE parent_id = 1
            ),
            agg AS (
                SELECT
                    CASE
                        WHEN r.expecteddonedate >= TO_DATE(:current_from, 'YYYY-MM-DD')
                         AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD')
                        THEN 'CURRENT'
                        ELSE 'COMPARE'
                    END period_key,
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                    SUM(CASE WHEN {$opdCondition} THEN 1 ELSE 0 END) opd_value,
                    SUM(CASE WHEN {$erCondition} THEN 1 ELSE 0 END) er_value
                FROM rcp_visit v
                JOIN rcp_service_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(:current_from, 'YYYY-MM-DD')
                            AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD')
                        )
                        OR
                        (
                            r.expecteddonedate >= TO_DATE(:compare_from, 'YYYY-MM-DD')
                            AND r.expecteddonedate < TO_DATE(:compare_to, 'YYYY-MM-DD')
                        )
                  )
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                GROUP BY
                    CASE
                        WHEN r.expecteddonedate >= TO_DATE(:current_from, 'YYYY-MM-DD')
                         AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD')
                        THEN 'CURRENT'
                        ELSE 'COMPARE'
                    END,
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            )
            SELECT
                m.month_id,
                m.month_name,
                NVL(MAX(CASE WHEN a.period_key = 'CURRENT' THEN a.opd_value END), 0) opd_current_value,
                NVL(MAX(CASE WHEN a.period_key = 'COMPARE' THEN a.opd_value END), 0) opd_compare_value,
                NVL(MAX(CASE WHEN a.period_key = 'CURRENT' THEN a.er_value END), 0) er_current_value,
                NVL(MAX(CASE WHEN a.period_key = 'COMPARE' THEN a.er_value END), 0) er_compare_value
            FROM months m
            LEFT JOIN agg a ON a.month_id = m.month_id
            GROUP BY m.month_id, m.month_name
            ORDER BY m.month_id
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'current_from' => $currentFrom,
            'current_to' => $currentTo,
            'compare_from' => $compareFrom,
            'compare_to' => $compareTo,
        ]);

        return $this->normalizeMonthlyVisitBundle($rows);
    }

    /**
     * Current selected-branch implementation of legacy padm_s.
     *
     * @return array<int, array<string, mixed>>
     */
    public function monthlyAdmissions(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        if ($fromDate !== null && $toDate !== null) {
            $compareFrom = RevenueRepository::shiftDateToYear($fromDate, $compareYear);
            $compareTo = RevenueRepository::shiftDateToYear($toDate, $compareYear);
            $sql = "
                WITH months AS (
                    SELECT code month_id, name_en month_name
                    FROM sec_lookup_detail
                    WHERE parent_id = 1
                ),
                agg AS (
                    SELECT
                        'CURRENT' period_key,
                        TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM')) month_id,
                        COUNT(1) value
                    FROM rcp_entryexit e
                    JOIN rcp_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(:current_from, 'YYYY-MM-DD')
                      AND v.visit_start_date < TO_DATE(:current_to, 'YYYY-MM-DD') + 1
                    GROUP BY TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM'))

                    UNION ALL

                    SELECT
                        'COMPARE' period_key,
                        TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM')) month_id,
                        COUNT(1) value
                    FROM rcp_entryexit e
                    JOIN rcp_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(:compare_from, 'YYYY-MM-DD')
                      AND v.visit_start_date < TO_DATE(:compare_to, 'YYYY-MM-DD') + 1
                    GROUP BY TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM'))
                )
                SELECT
                    m.month_id,
                    m.month_name,
                    NVL(MAX(CASE WHEN a.period_key = 'CURRENT' THEN a.value END), 0) current_value,
                    NVL(MAX(CASE WHEN a.period_key = 'COMPARE' THEN a.value END), 0) compare_value
                FROM months m
                LEFT JOIN agg a ON a.month_id = m.month_id
                GROUP BY m.month_id, m.month_name
                ORDER BY m.month_id
            ";

            return $this->normalizeMonthlyRows(Database::fetchAll($this->hospitalCode, $sql, [
                'current_from' => $fromDate,
                'current_to' => $toDate,
                'compare_from' => $compareFrom,
                'compare_to' => $compareTo,
            ]));
        }

        $currentFrom = sprintf('%04d-01-01', $year);
        $currentTo = sprintf('%04d-01-01', $year + 1);
        $compareFrom = sprintf('%04d-01-01', $compareYear);
        $compareTo = sprintf('%04d-01-01', $compareYear + 1);

        $sql = "
            WITH months AS (
                SELECT code month_id, name_en month_name
                FROM sec_lookup_detail
                WHERE parent_id = 1
            ),
            agg AS (
                SELECT
                    CASE
                        WHEN v.visit_start_date >= TO_DATE(:current_from, 'YYYY-MM-DD')
                         AND v.visit_start_date < TO_DATE(:current_to, 'YYYY-MM-DD')
                        THEN 'CURRENT'
                        ELSE 'COMPARE'
                    END period_key,
                    TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM')) month_id,
                    COUNT(1) value
                FROM rcp_entryexit e
                JOIN rcp_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(:current_from, 'YYYY-MM-DD')
                            AND v.visit_start_date < TO_DATE(:current_to, 'YYYY-MM-DD')
                        )
                        OR
                        (
                            v.visit_start_date >= TO_DATE(:compare_from, 'YYYY-MM-DD')
                            AND v.visit_start_date < TO_DATE(:compare_to, 'YYYY-MM-DD')
                        )
                  )
                GROUP BY
                    CASE
                        WHEN v.visit_start_date >= TO_DATE(:current_from, 'YYYY-MM-DD')
                         AND v.visit_start_date < TO_DATE(:current_to, 'YYYY-MM-DD')
                        THEN 'CURRENT'
                        ELSE 'COMPARE'
                    END,
                    TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM'))
            )
            SELECT
                m.month_id,
                m.month_name,
                NVL(MAX(CASE WHEN a.period_key = 'CURRENT' THEN a.value END), 0) current_value,
                NVL(MAX(CASE WHEN a.period_key = 'COMPARE' THEN a.value END), 0) compare_value
            FROM months m
            LEFT JOIN agg a ON a.month_id = m.month_id
            GROUP BY m.month_id, m.month_name
            ORDER BY m.month_id
        ";

        return $this->normalizeMonthlyRows(Database::fetchAll($this->hospitalCode, $sql, [
            'current_from' => $currentFrom,
            'current_to' => $currentTo,
            'compare_from' => $compareFrom,
            'compare_to' => $compareTo,
        ]));
    }

    /**
     * Current implementation of legacy vfr. The selected period is summarized
     * as New versus Follow-up activity.
     *
     * @return array<int, array<string, mixed>>
     */
    public function followupMix(int $year, ?string $fromDate = null, ?string $toDate = null): array
    {
        $where = "r.expecteddonedate >= TO_DATE(:year_from, 'YYYY-MM-DD')\n"
               . "              AND r.expecteddonedate < TO_DATE(:year_to, 'YYYY-MM-DD')";
        $params = [
            'year_from' => sprintf('%04d-01-01', $year),
            'year_to' => sprintf('%04d-01-01', $year + 1),
        ];

        if ($fromDate !== null && $toDate !== null) {
            $where = "r.expecteddonedate >= TO_DATE(:from_date, 'YYYY-MM-DD')\n"
                   . "              AND r.expecteddonedate < TO_DATE(:to_date, 'YYYY-MM-DD') + 1";
            $params = [
                'from_date' => $fromDate,
                'to_date' => $toDate,
            ];
        }

        $sql = "
            SELECT
                CASE WHEN r.res_type = 1 THEN 'followup' ELSE 'new' END category,
                COUNT(1) value
            FROM rcp_service_reservation r
            JOIN rcp_visit v
              ON v.visit_id = r.visit_id
             AND v.patientid = r.patientid
            WHERE r.hosp_id IN (1)
              AND {$where}
              AND r.servstatus NOT IN ('C', 'F')
              AND r.sched_serial IS NOT NULL
            GROUP BY CASE WHEN r.res_type = 1 THEN 'followup' ELSE 'new' END
        ";

        $rows = Database::fetchAll($this->hospitalCode, $sql, $params);
        return $this->normalizeMixRows($rows);
    }

    /**
     * Current implementation of the recovered legacy acty / get_admission_category_type contract.
     *
     * The historical contract groups inpatient admissions (CLASSCODE = 1)
     * by patient category type: Cash, Credit, and Insurance. The selected
     * hospital database keeps the legacy HOSP_ID = 1 contract inside each
     * branch connection.
     *
     * @return array<int, array{category:string,value:float}>
     */
    public function admissionCategoryMix(
        int $year,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $where = "v.visit_start_date >= TO_DATE(:acty_year_from, 'YYYY-MM-DD')\n"
               . "              AND v.visit_start_date < TO_DATE(:acty_year_to, 'YYYY-MM-DD')";
        $params = [
            'acty_year_from' => sprintf('%04d-01-01', $year),
            'acty_year_to' => sprintf('%04d-01-01', $year + 1),
        ];

        if ($fromDate !== null && $toDate !== null) {
            $where = "v.visit_start_date >= TO_DATE(:acty_from_date, 'YYYY-MM-DD')\n"
                   . "              AND v.visit_start_date < TO_DATE(:acty_to_date, 'YYYY-MM-DD') + 1";
            $params = [
                'acty_from_date' => $fromDate,
                'acty_to_date' => $toDate,
            ];
        }

        $sql = "
            SELECT
                CASE
                    WHEN pt.cat_type = 1 THEN 'cash'
                    WHEN pt.cat_type = 2 THEN 'credit'
                    WHEN pt.cat_type = 3 THEN 'insurance'
                    ELSE 'other'
                END category,
                COUNT(1) value
            FROM rcp_visit v
            JOIN reg_comp_category c
              ON v.hosp_id = c.hosp_id
             AND v.financlassid = c.financlassid
             AND v.patcatid = c.patcatid
             AND v.main_patcatid = c.main_patcatid
            LEFT JOIN stp_patcat_type pt
              ON c.pat_cat_type = pt.cat_type_id
            WHERE v.hosp_id = 1
              AND {$where}
              AND v.classcode = 1
            GROUP BY pt.cat_type
        ";

        return $this->normalizeAdmissionCategoryRows(
            Database::fetchAll($this->hospitalCode, $sql, $params)
        );
    }

    /**
     * Current implementation of the recovered legacy
     * out_cash_cr / get_out_patient_cash_credit contract.
     *
     * Historical time scope is TODAY only. The legacy year/date dispatcher
     * parameters were accepted but were not used by the function body.
     *
     * @return array<int, array{category:string,value:float}>
     */
    public function outpatientCashCreditToday(): array
    {
        $sql = "
            SELECT
                CASE
                    WHEN pt.cat_type = 1 THEN 'cash'
                    ELSE 'credit'
                END category,
                COUNT(*) value
            FROM rcp_service_reservation r
            JOIN rcp_visit v
              ON v.visit_id = r.visit_id
             AND v.patientid = r.patientid
            JOIN stp_places p
              ON p.place_id1 = r.place_id
            JOIN reg_comp_category c
              ON v.hosp_id = c.hosp_id
             AND v.patcatid = c.patcatid
             AND v.main_patcatid = c.main_patcatid
             AND TO_NUMBER(v.financlassid) = TO_NUMBER(c.financlassid)
            LEFT JOIN stp_patcat_type pt
              ON pt.cat_type_id = c.pat_cat_type
            WHERE r.hosp_id IN (1)
              AND r.expecteddonedate >= TRUNC(SYSDATE)
              AND r.expecteddonedate < TRUNC(SYSDATE) + 1
              AND r.servstatus NOT IN ('C', 'F')
              AND r.sched_serial IS NOT NULL
            GROUP BY pt.cat_type
        ";

        return $this->normalizeOutpatientCashCreditRows(
            Database::fetchAll($this->hospitalCode, $sql)
        );
    }

    /** @return array<int, array<string, mixed>> */
    public function dailyOutpatientTrend(int $days = 14): array
    {
        $days = max(7, min(60, $days));
        $filter = $this->outpatientFilterSql();
        $sql = "
            SELECT
                TRUNC(r.expecteddonedate) day_id,
                TO_CHAR(TRUNC(r.expecteddonedate), 'DD Mon') day_label,
                COUNT(1) value
            FROM rcp_visit v
            JOIN rcp_service_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 >= TRUNC(SYSDATE) - :days_back
              AND r.expecteddonedate < TRUNC(SYSDATE) + 1
              AND r.servstatus NOT IN ('C', 'F')
              AND r.sched_serial IS NOT NULL
              {$filter}
            GROUP BY TRUNC(r.expecteddonedate), TO_CHAR(TRUNC(r.expecteddonedate), 'DD Mon')
            ORDER BY TRUNC(r.expecteddonedate)
        ";

        return Database::fetchAll($this->hospitalCode, $sql, ['days_back' => $days - 1]);
    }

    /** @return array<int, array<string, mixed>> */
    public function topSpecialtiesByOpVisits(int $year): array
    {
        if (!$this->tableExists('KPI_DOCTOR_PERF_DETAIL_JOB')) {
            return [];
        }

        foreach (['YEARID', 'SPECIAL_SPEC_ID', 'SPECIAL_SPEC_EN_NAME', 'TOTAL_OP_VISITS'] as $column) {
            if (!$this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', $column)) {
                return [];
            }
        }

        $sql = "
            SELECT specialty_name, value
            FROM (
                SELECT
                    special_spec_en_name specialty_name,
                    SUM(DISTINCT total_op_visits) value
                FROM kpi_doctor_perf_detail_job
                WHERE yearid = :year
                GROUP BY special_spec_id, special_spec_en_name
                HAVING SUM(DISTINCT total_op_visits) > 0
                ORDER BY SUM(DISTINCT total_op_visits) DESC
            )
            WHERE ROWNUM <= 10
        ";

        return Database::fetchAll($this->hospitalCode, $sql, ['year' => $year]);
    }

    /** @return array<int, array<string, mixed>> */
    public function emptyMonthlyRows(): array
    {
        $rows = [];
        $months = [
            1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April',
            5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August',
            9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December',
        ];
        foreach ($months as $id => $name) {
            $rows[] = [
                'month_id' => $id,
                'month_name' => $name,
                'current_value' => 0.0,
                'compare_value' => 0.0,
            ];
        }
        return $rows;
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function monthlyVisitRows(
        string $type,
        int $year,
        int $compareYear,
        ?string $fromDate,
        ?string $toDate
    ): array {
        $filter = $type === 'emergency' ? $this->emergencyFilterSql() : $this->outpatientFilterSql();

        if ($fromDate !== null && $toDate !== null) {
            $compareFrom = RevenueRepository::shiftDateToYear($fromDate, $compareYear);
            $compareTo = RevenueRepository::shiftDateToYear($toDate, $compareYear);
            $sql = "
                WITH months AS (
                    SELECT code month_id, name_en month_name
                    FROM sec_lookup_detail
                    WHERE parent_id = 1
                ),
                agg AS (
                    SELECT
                        'CURRENT' period_key,
                        TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                        COUNT(1) value
                    FROM rcp_visit v
                    JOIN rcp_service_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(:current_from, 'YYYY-MM-DD')
                      AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD') + 1
                      AND r.servstatus NOT IN ('C', 'F')
                      AND r.sched_serial IS NOT NULL
                      {$filter}
                    GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))

                    UNION ALL

                    SELECT
                        'COMPARE' period_key,
                        TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                        COUNT(1) value
                    FROM rcp_visit v
                    JOIN rcp_service_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(:compare_from, 'YYYY-MM-DD')
                      AND r.expecteddonedate < TO_DATE(:compare_to, 'YYYY-MM-DD') + 1
                      AND r.servstatus NOT IN ('C', 'F')
                      AND r.sched_serial IS NOT NULL
                      {$filter}
                    GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
                )
                SELECT
                    m.month_id,
                    m.month_name,
                    NVL(MAX(CASE WHEN a.period_key = 'CURRENT' THEN a.value END), 0) current_value,
                    NVL(MAX(CASE WHEN a.period_key = 'COMPARE' THEN a.value END), 0) compare_value
                FROM months m
                LEFT JOIN agg a ON a.month_id = m.month_id
                GROUP BY m.month_id, m.month_name
                ORDER BY m.month_id
            ";

            return $this->normalizeMonthlyRows(Database::fetchAll($this->hospitalCode, $sql, [
                'current_from' => $fromDate,
                'current_to' => $toDate,
                'compare_from' => $compareFrom,
                'compare_to' => $compareTo,
            ]));
        }

        $currentFrom = sprintf('%04d-01-01', $year);
        $currentTo = sprintf('%04d-01-01', $year + 1);
        $compareFrom = sprintf('%04d-01-01', $compareYear);
        $compareTo = sprintf('%04d-01-01', $compareYear + 1);

        $sql = "
            WITH months AS (
                SELECT code month_id, name_en month_name
                FROM sec_lookup_detail
                WHERE parent_id = 1
            ),
            agg AS (
                SELECT
                    CASE
                        WHEN r.expecteddonedate >= TO_DATE(:current_from, 'YYYY-MM-DD')
                         AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD')
                        THEN 'CURRENT'
                        ELSE 'COMPARE'
                    END period_key,
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                    COUNT(1) value
                FROM rcp_visit v
                JOIN rcp_service_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(:current_from, 'YYYY-MM-DD')
                            AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD')
                        )
                        OR
                        (
                            r.expecteddonedate >= TO_DATE(:compare_from, 'YYYY-MM-DD')
                            AND r.expecteddonedate < TO_DATE(:compare_to, 'YYYY-MM-DD')
                        )
                  )
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                  {$filter}
                GROUP BY
                    CASE
                        WHEN r.expecteddonedate >= TO_DATE(:current_from, 'YYYY-MM-DD')
                         AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD')
                        THEN 'CURRENT'
                        ELSE 'COMPARE'
                    END,
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            )
            SELECT
                m.month_id,
                m.month_name,
                NVL(MAX(CASE WHEN a.period_key = 'CURRENT' THEN a.value END), 0) current_value,
                NVL(MAX(CASE WHEN a.period_key = 'COMPARE' THEN a.value END), 0) compare_value
            FROM months m
            LEFT JOIN agg a ON a.month_id = m.month_id
            GROUP BY m.month_id, m.month_name
            ORDER BY m.month_id
        ";

        return $this->normalizeMonthlyRows(Database::fetchAll($this->hospitalCode, $sql, [
            'current_from' => $currentFrom,
            'current_to' => $currentTo,
            'compare_from' => $compareFrom,
            'compare_to' => $compareTo,
        ]));
    }

    public static function shiftDateToYear(string $date, int $targetYear): string
    {
        return RevenueRepository::shiftDateToYear($date, $targetYear);
    }

    /**
     * @return array{outpatient:string,emergency:string}
     */
    private function visitClassificationConditions(): array
    {
        $emergencyType = $this->columnExpression('OUTPTA_EMERGENCY_TYPE', [
            'RCP_VISIT' => 'v',
            'RCP_SERVICE_RESERVATION' => 'r',
        ]);
        $clinicId = $this->columnExpression('CLINIC_ID', [
            'RCP_VISIT' => 'v',
            'RCP_SERVICE_RESERVATION' => 'r',
        ]);

        $outpatientParts = [];
        $emergencyParts = [];

        if ($emergencyType !== null) {
            $outpatientParts[] = 'NVL(' . $emergencyType . ', 0) <> 1';
            $emergencyParts[] = 'NVL(' . $emergencyType . ', 0) = 1';
        }
        if ($clinicId !== null) {
            $outpatientParts[] = $clinicId . ' NOT IN (16, 297, 298)';
            $emergencyParts[] = 'NVL(' . $clinicId . ', 0) IN (16, 297, 298)';
        }

        return [
            'outpatient' => $outpatientParts ? '(' . implode(' AND ', $outpatientParts) . ')' : '1 = 1',
            'emergency' => $emergencyParts ? '(' . implode(' OR ', $emergencyParts) . ')' : '1 = 0',
        ];
    }

    private function outpatientFilterSql(): string
    {
        $emergencyType = $this->columnExpression('OUTPTA_EMERGENCY_TYPE', [
            'RCP_VISIT' => 'v',
            'RCP_SERVICE_RESERVATION' => 'r',
        ]);
        $clinicId = $this->columnExpression('CLINIC_ID', [
            'RCP_VISIT' => 'v',
            'RCP_SERVICE_RESERVATION' => 'r',
        ]);

        $parts = [];
        if ($emergencyType !== null) {
            $parts[] = 'NVL(' . $emergencyType . ', 0) <> 1';
        }
        if ($clinicId !== null) {
            $parts[] = $clinicId . ' NOT IN (16, 297, 298)';
        }

        return $parts ? 'AND (' . implode(' AND ', $parts) . ')' : '';
    }

    private function emergencyFilterSql(): string
    {
        $emergencyType = $this->columnExpression('OUTPTA_EMERGENCY_TYPE', [
            'RCP_VISIT' => 'v',
            'RCP_SERVICE_RESERVATION' => 'r',
        ]);
        $clinicId = $this->columnExpression('CLINIC_ID', [
            'RCP_VISIT' => 'v',
            'RCP_SERVICE_RESERVATION' => 'r',
        ]);

        $parts = [];
        if ($emergencyType !== null) {
            $parts[] = 'NVL(' . $emergencyType . ', 0) = 1';
        }
        if ($clinicId !== null) {
            $parts[] = 'NVL(' . $clinicId . ', 0) IN (16, 297, 298)';
        }

        return $parts ? 'AND (' . implode(' OR ', $parts) . ')' : 'AND 1 = 0';
    }

    /**
     * @param array<string, string> $tableAliasMap table name => SQL alias
     */
    private function columnExpression(string $column, array $tableAliasMap): ?string
    {
        foreach ($tableAliasMap as $table => $alias) {
            if ($this->columnExists($table, $column)) {
                return $alias . '.' . strtolower($column);
            }
        }
        return null;
    }

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

    /** @return array<int, array<string, mixed>> */
    private function monthlyRows(string $sql, int $year, int $compareYear): array
    {
        return $this->normalizeMonthlyRows(Database::fetchAll($this->hospitalCode, $sql, [
            'current_year' => $year,
            'compare_year' => $compareYear,
        ]));
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array{opd:array<int,array<string,mixed>>,er:array<int,array<string,mixed>>}
     */
    private function normalizeMonthlyVisitBundle(array $rows): array
    {
        $opd = [];
        $er = [];

        foreach ($rows as $row) {
            $common = [
                'month_id' => (int)($row['month_id'] ?? $row['code'] ?? 0),
                'month_name' => trim((string)($row['month_name'] ?? '')),
            ];

            $opd[] = $common + [
                'current_value' => (float)($row['opd_current_value'] ?? 0),
                'compare_value' => (float)($row['opd_compare_value'] ?? 0),
            ];
            $er[] = $common + [
                'current_value' => (float)($row['er_current_value'] ?? 0),
                'compare_value' => (float)($row['er_compare_value'] ?? 0),
            ];
        }

        return ['opd' => $opd, 'er' => $er];
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<int, array<string, mixed>>
     */
    private function normalizeMonthlyRows(array $rows): array
    {
        return array_map(static function (array $row): array {
            return [
                'month_id' => (int)($row['month_id'] ?? $row['code'] ?? 0),
                'month_name' => trim((string)($row['month_name'] ?? '')),
                'current_value' => (float)($row['current_value'] ?? 0),
                'compare_value' => (float)($row['compare_value'] ?? 0),
            ];
        }, $rows);
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<int, array<string, mixed>>
     */
    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<int, array{category:string,value:float}>
     */
    private function normalizeAdmissionCategoryRows(array $rows): array
    {
        $out = [
            'cash' => ['category' => 'cash', 'value' => 0.0],
            'credit' => ['category' => 'credit', 'value' => 0.0],
            'insurance' => ['category' => 'insurance', 'value' => 0.0],
        ];

        foreach ($rows as $row) {
            $category = strtolower(trim((string)($row['category'] ?? 'other')));
            if (!array_key_exists($category, $out)) {
                $category = 'other';
                if (!isset($out[$category])) {
                    $out[$category] = ['category' => 'other', 'value' => 0.0];
                }
            }
            $out[$category]['value'] += (float)($row['value'] ?? 0);
        }

        return array_values($out);
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<int, array{category:string,value:float}>
     */
    private function normalizeOutpatientCashCreditRows(array $rows): array
    {
        $out = [
            'cash' => ['category' => 'cash', 'value' => 0.0],
            'credit' => ['category' => 'credit', 'value' => 0.0],
        ];

        foreach ($rows as $row) {
            $category = strtolower(trim((string)($row['category'] ?? 'credit')));
            $key = $category === 'cash' ? 'cash' : 'credit';
            $out[$key]['value'] += (float)($row['value'] ?? 0);
        }

        return array_values($out);
    }

    private function normalizeMixRows(array $rows): array
    {
        $out = [
            'new' => ['category' => 'new', 'value' => 0.0],
            'followup' => ['category' => 'followup', 'value' => 0.0],
        ];
        foreach ($rows as $row) {
            $category = strtolower(trim((string)($row['category'] ?? 'new')));
            $key = $category === 'followup' ? 'followup' : 'new';
            $out[$key]['value'] += (float)($row['value'] ?? 0);
        }
        return array_values($out);
    }
}
