<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use RuntimeException;
use Throwable;

/**
 * Independent execution path for the historical gmov function.
 *
 * The old Oracle outer-join SQL shape is preserved. This repository never calls
 * the current multi-branch service. Direct remote connections are preferred;
 * Oracle listener/naming failures may use the established NJCH read-only DB links.
 */
final class LegacyMultiBranchPatientActivityRepository
{
    /** @var array<int, string> */
    private const BRANCHES = ['njch', 'jckh', 'mnh'];

    /** @var array<string, string> */
    private const DB_LINKS = [
        'jckh' => 'rcpmaster_jck',
        'mnh' => 'rcpmaster_mnh',
    ];

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

    /**
     * @return array<int, array{month_id:int,month_name:string,njch:float,jckh:float,mnh:float,total:float}>
     */
    public function monthlyOutpatientVisits(
        int $year,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $this->accessPaths = [];
        $branchRows = [];

        foreach (self::BRANCHES as $hospitalCode) {
            $branchRows[$hospitalCode] = $this->legacyRowsForBranch(
                $hospitalCode,
                $year,
                $fromDate,
                $toDate
            );
        }

        return $this->mergeBranches($branchRows);
    }

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

    /**
     * Exact recovered gmov SQL contract:
     * - RCP_VISIT.CLASSCODE = 2
     * - no emergency exclusion
     * - SERVSTATUS NOT IN ('C','F')
     * - SCHED_SERIAL IS NOT NULL
     *
     * @return array<int, array<string, mixed>>
     */
    private function legacyRowsForBranch(
        string $hospitalCode,
        int $year,
        ?string $fromDate,
        ?string $toDate
    ): array {
        $where = 'EXTRACT(YEAR FROM r.expecteddonedate) = :legacy_gmov_year';
        $params = ['legacy_gmov_year' => $year];

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

        $directSql = $this->buildLegacySql('', $where);

        try {
            $rows = Database::fetchAll($hospitalCode, $directSql, $params);
            $this->accessPaths[$hospitalCode] = [
                'mode' => 'DIRECT',
                'connection' => $hospitalCode,
                'database_link' => '',
            ];
            return $rows;
        } catch (Throwable $directError) {
            if ($hospitalCode === 'njch' || !$this->isConnectionFailure($directError)) {
                throw new RuntimeException(
                    'GMOV legacy path failed for branch ' . strtoupper($hospitalCode)
                    . ' using DIRECT connection: ' . $directError->getMessage(),
                    0,
                    $directError
                );
            }

            $dbLink = self::DB_LINKS[$hospitalCode] ?? '';
            if ($dbLink === '') {
                throw new RuntimeException(
                    'GMOV legacy path has no read-only DB-link fallback for branch '
                    . strtoupper($hospitalCode) . '. Direct error: ' . $directError->getMessage(),
                    0,
                    $directError
                );
            }

            try {
                $fallbackSql = $this->buildLegacySql('@' . $dbLink, $where);
                $rows = Database::fetchAll('njch', $fallbackSql, $params);
                $this->accessPaths[$hospitalCode] = [
                    'mode' => 'NJCH_DB_LINK_FALLBACK',
                    'connection' => 'njch',
                    'database_link' => $dbLink,
                ];
                return $rows;
            } catch (Throwable $fallbackError) {
                throw new RuntimeException(
                    'GMOV legacy path failed for branch ' . strtoupper($hospitalCode)
                    . '. DIRECT error: ' . $directError->getMessage()
                    . ' | DB-LINK ' . $dbLink . ' error: ' . $fallbackError->getMessage(),
                    0,
                    $fallbackError
                );
            }
        }
    }

    private function buildLegacySql(string $suffix, string $where): string
    {
        $visit = 'rcp_visit' . $suffix;
        $reservation = 'rcp_service_reservation' . $suffix;
        $months = 'sec_lookup_detail' . $suffix;

        return "
            SELECT
                months.code month_id,
                months.name_en month_name,
                NVL(activity.month_wise_count, 0) period_value
            FROM
                (
                    SELECT
                        COUNT(1) month_wise_count,
                        TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_no
                    FROM {$visit} v,
                         {$reservation} r
                    WHERE v.classcode = 2
                      AND {$where}
                      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
                    GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
                ) activity,
                (
                    SELECT code, name_en
                    FROM {$months}
                    WHERE parent_id = 1
                ) months
            WHERE activity.month_no (+) = months.code
            ORDER BY months.code
        ";
    }

    private function isConnectionFailure(Throwable $error): bool
    {
        $message = strtoupper($error->getMessage());

        foreach ([
            'ORA-12514',
            'ORA-12154',
            'ORA-12541',
            'ORA-12545',
            'ORA-12504',
            'LISTENER DOES NOT CURRENTLY KNOW OF SERVICE',
            'MISSING ORACLE SETTING',
            'HOSPITAL DATABASE CONFIG NOT FOUND',
        ] as $needle) {
            if (strpos($message, $needle) !== false) {
                return true;
            }
        }

        return false;
    }

    /**
     * @param array<string, array<int, array<string, mixed>>> $branchRows
     * @return array<int, array{month_id:int,month_name:string,njch:float,jckh:float,mnh:float,total:float}>
     */
    private function mergeBranches(array $branchRows): array
    {
        $out = [];

        for ($month = 1; $month <= 12; $month++) {
            $row = [
                'month_id' => $month,
                'month_name' => '',
                'njch' => 0.0,
                'jckh' => 0.0,
                'mnh' => 0.0,
                'total' => 0.0,
            ];

            foreach (self::BRANCHES as $hospitalCode) {
                foreach ($branchRows[$hospitalCode] ?? [] as $candidate) {
                    if ((int)($candidate['month_id'] ?? 0) !== $month) {
                        continue;
                    }
                    if ($row['month_name'] === '') {
                        $row['month_name'] = trim((string)($candidate['month_name'] ?? ''));
                    }
                    $row[$hospitalCode] = (float)($candidate['period_value'] ?? 0);
                    break;
                }
            }

            $row['total'] = $row['njch'] + $row['jckh'] + $row['mnh'];
            $out[] = $row;
        }

        return $out;
    }
}
