<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Independent execution path for the confirmed legacy Patient Activity contract.
 *
 * It intentionally does not call VisitsRepository. Phase 10.10 compares this
 * legacy-contract path with the current production repository against the same
 * read-only Oracle source.
 */
final class LegacyPatientActivityRepository
{
    /** @var string */
    private $hospitalCode;

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

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

    /**
     * Legacy gmov_s: selected-branch outpatient visits with ER excluded.
     *
     * @return array<int, array<string, mixed>>
     */
    public function monthlyOutpatientVisits(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        return $this->monthlyReservationActivity('outpatient', $year, $compareYear, $fromDate, $toDate);
    }

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

    /**
     * Legacy padm_s: selected-branch admissions, excluding non-normal baby beds.
     *
     * @return array<int, array<string, mixed>>
     */
    public function monthlyAdmissions(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $current = $fromDate !== null && $toDate !== null
            ? $this->admissionPeriodRows($fromDate, $toDate)
            : $this->admissionYearRows($year);

        if ($fromDate !== null && $toDate !== null) {
            $compare = $this->admissionPeriodRows(
                RevenueRepository::shiftDateToYear($fromDate, $compareYear),
                RevenueRepository::shiftDateToYear($toDate, $compareYear)
            );
        } else {
            $compare = $this->admissionYearRows($compareYear);
        }

        return $this->mergeMonthRows($current, $compare);
    }

    /**
     * Legacy acty / get_admission_category_type.
     *
     * Full historical body recovered from the user-provided legacy dispatcher
     * source. This path intentionally preserves old-style Oracle join syntax and
     * remains independent from VisitsRepository for exact live parity testing.
     *
     * @return array<int, array{category:string,value:float}>
     */
    public function admissionCategoryMix(
        int $year,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $where = 'EXTRACT(YEAR FROM v.visit_start_date) = :legacy_acty_year';
        $params = ['legacy_acty_year' => $year];

        if ($fromDate !== null && $toDate !== null) {
            $where = "v.visit_start_date >= TO_DATE(:legacy_acty_from_date, 'YYYY-MM-DD')\n"
                   . "              AND v.visit_start_date < TO_DATE(:legacy_acty_to_date, 'YYYY-MM-DD') + 1";
            $params = [
                'legacy_acty_from_date' => $fromDate,
                'legacy_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'
                END category,
                COUNT(1) period_value
            FROM rcp_visit v,
                 reg_comp_category c,
                 stp_patcat_type pt
            WHERE c.pat_cat_type = pt.cat_type_id (+)
              AND v.hosp_id = c.hosp_id
              AND v.financlassid = c.financlassid
              AND v.patcatid = c.patcatid
              AND v.main_patcatid = c.main_patcatid
              AND v.hosp_id IN (1)
              AND {$where}
              AND v.classcode = 1
            GROUP BY pt.cat_type
        ";

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

    /**
     * Legacy out_cash_cr / get_out_patient_cash_credit.
     *
     * Exact historical body recovered from the old dispatcher source. This
     * path intentionally preserves the old Oracle outer-join syntax, the
     * historical current-day boundary, and the Cash-vs-Credit mapping.
     *
     * @return array<int, array{category:string,value:float}>
     */
    public function outpatientCashCreditToday(): array
    {
        $sql = "
            SELECT
                CASE
                    WHEN cc_q.cat_type = 1 THEN 'Cash'
                    ELSE 'Credit'
                END category,
                cc_q.rate
            FROM (
                SELECT
                    pt.cat_type,
                    COUNT(*) rate
                FROM rcp_service_reservation r,
                     rcp_visit v,
                     stp_places p,
                     reg_comp_category c,
                     stp_patcat_type pt
                WHERE pt.cat_type_id (+) = c.pat_cat_type
                  AND v.visit_id = r.visit_id
                  AND v.patientid = r.patientid
                  AND p.place_id1 = r.place_id
                  AND v.hosp_id = c.hosp_id
                  AND v.patcatid = c.patcatid
                  AND v.main_patcatid = c.main_patcatid
                  AND r.hosp_id IN (1)
                  AND r.expecteddonedate >= TO_DATE(SYSDATE)
                  AND r.expecteddonedate <= TO_TIMESTAMP(
                      TO_DATE(SYSDATE) || ' 11:59:59 PM',
                      'DD/MM/RR HH12:MI:SSXFF PM'
                  )
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                  AND TO_NUMBER(v.financlassid) = TO_NUMBER(c.financlassid)
                GROUP BY pt.cat_type
            ) cc_q
        ";

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

    /**
     * Legacy vfr: New versus Follow-up visit mix for the selected period.
     *
     * @return array<int, array<string, mixed>>
     */
    public function followupMix(int $year, ?string $fromDate = null, ?string $toDate = null): array
    {
        $where = 'EXTRACT(YEAR FROM r.expecteddonedate) = :legacy_year';
        $params = ['legacy_year' => $year];

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

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

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

    /**
     * @return array<int, array<string, mixed>>
     */
    private function monthlyReservationActivity(
        string $type,
        int $year,
        int $compareYear,
        ?string $fromDate,
        ?string $toDate
    ): array {
        $current = $fromDate !== null && $toDate !== null
            ? $this->reservationPeriodRows($type, $fromDate, $toDate)
            : $this->reservationYearRows($type, $year);

        if ($fromDate !== null && $toDate !== null) {
            $compare = $this->reservationPeriodRows(
                $type,
                RevenueRepository::shiftDateToYear($fromDate, $compareYear),
                RevenueRepository::shiftDateToYear($toDate, $compareYear)
            );
        } else {
            $compare = $this->reservationYearRows($type, $compareYear);
        }

        return $this->mergeMonthRows($current, $compare);
    }

    /** @return array<int, array<string, mixed>> */
    private function reservationYearRows(string $type, int $year): array
    {
        $filter = $this->legacyEmergencyDiscriminator($type);
        $sql = "
            SELECT
                months.code,
                months.name_en month_name,
                NVL(activity.month_wise_count, 0) period_value
            FROM (SELECT code, name_en FROM sec_lookup_detail WHERE parent_id = 1) months
            LEFT JOIN (
                SELECT
                    COUNT(1) month_wise_count,
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_no
                FROM rcp_visit v,
                     rcp_service_reservation r
                WHERE v.classcode = 2
                  AND EXTRACT(YEAR FROM r.expecteddonedate) = :legacy_year
                  AND v.hosp_id = r.hosp_id
                  AND v.patientid = r.patientid
                  AND v.visit_id = r.visit_id
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                  {$filter}
                GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            ) activity ON activity.month_no = months.code
            ORDER BY months.code
        ";

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

    /** @return array<int, array<string, mixed>> */
    private function reservationPeriodRows(string $type, string $fromDate, string $toDate): array
    {
        $filter = $this->legacyEmergencyDiscriminator($type);
        $sql = "
            SELECT
                months.code,
                months.name_en month_name,
                NVL(activity.month_wise_count, 0) period_value
            FROM (SELECT code, name_en FROM sec_lookup_detail WHERE parent_id = 1) months
            LEFT JOIN (
                SELECT
                    COUNT(1) month_wise_count,
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_no
                FROM rcp_visit v,
                     rcp_service_reservation r
                WHERE v.classcode = 2
                  AND r.expecteddonedate >= TO_DATE(:legacy_from_date, 'YYYY-MM-DD')
                  AND r.expecteddonedate < TO_DATE(:legacy_to_date, 'YYYY-MM-DD') + 1
                  AND v.hosp_id = r.hosp_id
                  AND v.patientid = r.patientid
                  AND v.visit_id = r.visit_id
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                  {$filter}
                GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            ) activity ON activity.month_no = months.code
            ORDER BY months.code
        ";

        return Database::fetchAll($this->hospitalCode, $sql, [
            'legacy_from_date' => $fromDate,
            'legacy_to_date' => $toDate,
        ]);
    }

    /** @return array<int, array<string, mixed>> */
    private function admissionYearRows(int $year): array
    {
        $sql = "
            SELECT
                months.code,
                months.name_en month_name,
                NVL(activity.month_wise_adm_count, 0) period_value
            FROM (SELECT code, name_en FROM sec_lookup_detail WHERE parent_id = 1) months
            LEFT JOIN (
                SELECT
                    COUNT(1) month_wise_adm_count,
                    TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM')) month_no
                FROM rcp_entryexit e,
                     rcp_visit v
                WHERE v.hosp_id = e.branchid
                  AND v.patientid = e.patientid
                  AND v.visit_id = e.visit_id
                  AND v.hosp_id = 1
                  AND NVL(e.baby_normal_bed, 1) = 1
                  AND EXTRACT(YEAR FROM v.visit_start_date) = :legacy_year
                GROUP BY TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM'))
            ) activity ON activity.month_no = months.code
            ORDER BY months.code
        ";

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

    /** @return array<int, array<string, mixed>> */
    private function admissionPeriodRows(string $fromDate, string $toDate): array
    {
        $sql = "
            SELECT
                months.code,
                months.name_en month_name,
                NVL(activity.month_wise_adm_count, 0) period_value
            FROM (SELECT code, name_en FROM sec_lookup_detail WHERE parent_id = 1) months
            LEFT JOIN (
                SELECT
                    COUNT(1) month_wise_adm_count,
                    TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM')) month_no
                FROM rcp_entryexit e,
                     rcp_visit v
                WHERE v.hosp_id = e.branchid
                  AND v.patientid = e.patientid
                  AND v.visit_id = e.visit_id
                  AND v.hosp_id = 1
                  AND NVL(e.baby_normal_bed, 1) = 1
                  AND v.visit_start_date >= TO_DATE(:legacy_from_date, 'YYYY-MM-DD')
                  AND v.visit_start_date < TO_DATE(:legacy_to_date, 'YYYY-MM-DD') + 1
                GROUP BY TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM'))
            ) activity ON activity.month_no = months.code
            ORDER BY months.code
        ";

        return Database::fetchAll($this->hospitalCode, $sql, [
            'legacy_from_date' => $fromDate,
            'legacy_to_date' => $toDate,
        ]);
    }

    private function legacyEmergencyDiscriminator(string $type): string
    {
        $emergencyType = $this->legacyColumnExpression('OUTPTA_EMERGENCY_TYPE');
        $clinicId = $this->legacyColumnExpression('CLINIC_ID');

        if ($type === 'emergency') {
            $parts = [];
            if ($emergencyType !== null) {
                $parts[] = 'NVL(' . $emergencyType . ', 0) = 1';
            }
            if ($clinicId !== null) {
                $parts[] = $clinicId . ' IN (16, 297, 298)';
            }
            return $parts ? 'AND (' . implode(' OR ', $parts) . ')' : 'AND 1 = 0';
        }

        $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 legacyColumnExpression(string $column): ?string
    {
        foreach (['RCP_VISIT' => 'v', 'RCP_SERVICE_RESERVATION' => 'r'] 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(:legacy_table_name)
              AND column_name = UPPER(:legacy_column_name)
              AND (:legacy_owner_name IS NULL OR owner = UPPER(:legacy_owner_name))
              AND ROWNUM <= 1
        ";

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

        return $this->columnCache[$cacheKey];
    }

    /**
     * @param array<int, array<string, mixed>> $current
     * @param array<int, array<string, mixed>> $compare
     * @return array<int, array<string, mixed>>
     */
    private function mergeMonthRows(array $current, array $compare): array
    {
        $compareByCode = [];
        foreach ($compare as $row) {
            $compareByCode[(int)($row['code'] ?? 0)] = $row;
        }

        $rows = [];
        foreach ($current as $row) {
            $code = (int)($row['code'] ?? 0);
            $other = $compareByCode[$code] ?? [];
            $rows[] = [
                'month_id' => $code,
                'month_name' => trim((string)($row['month_name'] ?? $other['month_name'] ?? '')),
                'current_value' => (float)($row['period_value'] ?? 0),
                'compare_value' => (float)($other['period_value'] ?? 0),
            ];
        }
        return $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['period_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['rate'] ?? 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['period_value'] ?? 0);
        }
        return array_values($out);
    }
}
