<?php

declare(strict_types=1);

namespace App\Services;

/**
 * Current implementation of the recovered legacy gmov business contract.
 *
 * H11.0-P4.5 policy:
 * - NJCH and MNH are blocking QA/live-parity branches.
 * - JCKH is DEFERRED_NOT_READY and is not queried in this phase.
 * - JCKH still remains in future database-update propagation scope.
 */
final class MultiBranchPatientActivityService
{
    /** @var BranchOracleAccessResolver */
    private $resolver;

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

    /**
     * @return array<int, array<string, mixed>>
     */
    public function monthlyOutpatientVisits(
        int $year,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $branchRows = [];

        foreach (BranchReadinessPolicy::blockingQaBranches() as $hospitalCode) {
            $branchRows[$hospitalCode] = $this->rowsForBranch(
                $hospitalCode,
                $year,
                $fromDate,
                $toDate
            );
        }

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

    /**
     * @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();
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    private function rowsForBranch(
        string $hospitalCode,
        int $year,
        ?string $fromDate,
        ?string $toDate
    ): array {
        $where = 'EXTRACT(YEAR FROM r.expecteddonedate) = :gmov_year';
        $params = ['gmov_year' => $year];

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

        return $this->resolver->fetchAll(
            $hospitalCode,
            function (string $suffix) use ($where): string {
                return $this->buildSql($suffix, $where);
            },
            $params
        );
    }

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

        return "
            WITH months AS (
                SELECT code month_id, name_en month_name
                FROM {$months}
                WHERE parent_id = 1
            ),
            activity AS (
                SELECT
                    COUNT(1) month_wise_count,
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id
                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 {$where}
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            )
            SELECT
                m.month_id,
                m.month_name,
                NVL(a.month_wise_count, 0) value
            FROM months m
            LEFT JOIN activity a ON a.month_id = m.month_id
            ORDER BY m.month_id
        ";
    }

    /**
     * @param array<string, array<int, array<string, mixed>>> $branchRows
     * @return array<int, array<string, mixed>>
     */
    private function mergeBranches(array $branchRows): array
    {
        $out = [];
        $activeBranches = BranchReadinessPolicy::blockingQaBranches();

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

            foreach ($activeBranches 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['value'] ?? 0);
                    break;
                }
            }

            foreach ($activeBranches as $hospitalCode) {
                if (is_numeric($row[$hospitalCode] ?? null)) {
                    $row['total'] += (float)$row[$hospitalCode];
                }
            }

            $out[] = $row;
        }

        return $out;
    }
}
