<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Phase 10.15 current selected-branch Specialty Finance implementation.
 *
 * Source boundary:
 * - Dispatcher keys moh_revn, drug_rev and net_rev are confirmed by the legacy inventory.
 * - The full historical function bodies are not present in the supplied legacy archive.
 * - KPI_DOCTOR_PERF_DETAIL_JOB is the established doctor-finance source already proven in Phase 10.13.
 * - This repository provides the current implementation while LegacySpecialtyFinanceRepository
 *   executes an independent old-style SQL path for live zero-difference validation.
 */
final class SpecialtyFinanceRepository
{
    /** @var string */
    private $hospitalCode;

    /** @var string */
    private $table = 'KPI_DOCTOR_PERF_DETAIL_JOB';

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

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

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

    /** @return array<string, mixed> */
    public function summary(int $year, int $compareYear, int $startMonth = 1, int $endMonth = 12): array
    {
        return [
            'year' => $year,
            'compare_year' => $compareYear,
            'current_moh' => $this->annualMetricTotal($year, 'MOH', $startMonth, $endMonth),
            'compare_moh' => $this->annualMetricTotal($compareYear, 'MOH', $startMonth, $endMonth),
            'current_drug' => $this->annualMetricTotal($year, 'DRUG_REV', $startMonth, $endMonth),
            'compare_drug' => $this->annualMetricTotal($compareYear, 'DRUG_REV', $startMonth, $endMonth),
            'current_net' => $this->annualMetricTotal($year, 'NET_REV_AFTER_DRUG_MOH', $startMonth, $endMonth),
            'compare_net' => $this->annualMetricTotal($compareYear, 'NET_REV_AFTER_DRUG_MOH', $startMonth, $endMonth),
        ];
    }

    public function annualMetricTotal(int $year, string $metric, int $startMonth = 1, int $endMonth = 12): float
    {
        if (!$this->hasCoreTable()) {
            return 0.0;
        }

        $metric = $this->normalizeMetric($metric);
        if (!$this->columnExists($this->table, $metric)) {
            return 0.0;
        }

        $groupExpr = $this->doctorGroupSql();
        $metricSql = $this->sumDistinctSql($metric);
        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);

        // Current implementation: explicit CTE contract, doctor-level deduplication, final sum.
        $sql = "
            WITH per_doctor AS (
                SELECT
                    {$groupExpr} doctor_key,
                    {$metricSql} metric_value
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :year
                  {$monthFilter}
                GROUP BY {$groupExpr}
            )
            SELECT NVL(SUM(metric_value), 0) total_value
            FROM per_doctor
        ";

        $row = Database::fetchOne($this->hospitalCode, $sql, $this->monthParams($year, $startMonth, $endMonth));
        return (float)($row['total_value'] ?? 0);
    }

    /**
     * Exact current implementation of the recovered historical
     * special_prof / get_special_prof contract.
     *
     * Historical business meaning is taken from the SQL source itself:
     * SUM(DISTINCT TOT_REV) by specialty for one year, only values > 50000.
     *
     * @return array<int, array<string, mixed>>
     */
    public function specialtyTotalRevenueRanking(int $year, int $startMonth = 1, int $endMonth = 12): array
    {
        foreach (['SPECIAL_SPEC_ID', 'SPECIAL_SPEC_EN_NAME', 'TOT_REV', 'YEARID'] as $requiredColumn) {
            if (!$this->hasCoreTable() || !$this->columnExists($this->table, $requiredColumn)) {
                throw new \RuntimeException(
                    'SPECIALTY_TOTAL_REVENUE_SOURCE_CONTRACT_UNAVAILABLE column=' . $requiredColumn
                );
            }
        }

        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);

        $sql = "
            WITH specialty_totals AS (
                SELECT
                    SPECIAL_SPEC_ID specialty_id,
                    SPECIAL_SPEC_EN_NAME specialty_name,
                    SUM(DISTINCT TOT_REV) metric_value
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :year
                  {$monthFilter}
                GROUP BY SPECIAL_SPEC_ID, SPECIAL_SPEC_EN_NAME
            )
            SELECT specialty_id, specialty_name, metric_value
            FROM specialty_totals
            WHERE metric_value > :minimum_value
            ORDER BY metric_value DESC
        ";

        $params = $this->monthParams($year, $startMonth, $endMonth);
        $params['minimum_value'] = 50000;
        $rows = Database::fetchAll($this->hospitalCode, $sql, $params);

        return array_map(static function (array $row): array {
            return [
                'id' => trim((string)($row['specialty_id'] ?? '')),
                'label' => trim((string)($row['specialty_name'] ?? 'Unknown specialty')),
                'value' => (float)($row['metric_value'] ?? 0),
            ];
        }, $rows);
    }

    /**
     * Exact current implementation of the recovered historical
     * special_net_rev / net_rev -> get_net_rev contract.
     *
     * @return array<int, array<string, mixed>>
     */
    public function specialtyNetRevenueRanking(int $year, int $startMonth = 1, int $endMonth = 12): array
    {
        foreach ([
            'SPECIAL_SPEC_ID',
            'SPECIAL_SPEC_EN_NAME',
            'NET_REV_AFTER_DRUG_MOH',
            'YEARID',
        ] as $requiredColumn) {
            if (!$this->hasCoreTable() || !$this->columnExists($this->table, $requiredColumn)) {
                throw new \RuntimeException(
                    'SPECIALTY_NET_REVENUE_SOURCE_CONTRACT_UNAVAILABLE column=' . $requiredColumn
                );
            }
        }

        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);

        $sql = "
            WITH specialty_net_revenue AS (
                SELECT
                    SPECIAL_SPEC_ID specialty_id,
                    SPECIAL_SPEC_EN_NAME specialty_name,
                    SUM(DISTINCT NET_REV_AFTER_DRUG_MOH) metric_value
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :year
                  {$monthFilter}
                GROUP BY SPECIAL_SPEC_ID, SPECIAL_SPEC_EN_NAME
            )
            SELECT specialty_id, specialty_name, metric_value
            FROM specialty_net_revenue
            WHERE metric_value > :minimum_value
            ORDER BY metric_value DESC
        ";

        $params = $this->monthParams($year, $startMonth, $endMonth);
        $params['minimum_value'] = 50000;
        $rows = Database::fetchAll($this->hospitalCode, $sql, $params);

        return array_map(static function (array $row): array {
            return [
                'id' => trim((string)($row['specialty_id'] ?? '')),
                'label' => trim((string)($row['specialty_name'] ?? 'Unknown specialty')),
                'value' => (float)($row['metric_value'] ?? 0),
            ];
        }, $rows);
    }

    /** @return array<int, array<string, mixed>> */
    public function specialtyRanking(int $year, string $metric, int $limit = 10, int $startMonth = 1, int $endMonth = 12): array
    {
        if (!$this->hasCoreTable() || !$this->columnExists($this->table, 'SPECIAL_SPEC_EN_NAME')) {
            return [];
        }

        $metric = $this->normalizeMetric($metric);
        if (!$this->columnExists($this->table, $metric)) {
            return [];
        }

        $doctorExpr = $this->doctorGroupSql();
        $specialtyKeyExpr = $this->specialtyGroupSql();
        $metricSql = $this->sumDistinctSql($metric);
        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);
        $limit = max(5, min(25, $limit));

        $sql = "
            SELECT specialty_key, specialty_name, metric_value
            FROM (
                SELECT
                    specialty_key,
                    MAX(specialty_name) specialty_name,
                    SUM(doctor_metric_value) metric_value
                FROM (
                    SELECT
                        {$specialtyKeyExpr} specialty_key,
                        MAX(SPECIAL_SPEC_EN_NAME) specialty_name,
                        {$doctorExpr} doctor_key,
                        {$metricSql} doctor_metric_value
                    FROM KPI_DOCTOR_PERF_DETAIL_JOB
                    WHERE YEARID = :year
                      {$monthFilter}
                    GROUP BY {$specialtyKeyExpr}, {$doctorExpr}
                )
                GROUP BY specialty_key
                HAVING SUM(doctor_metric_value) <> 0
                ORDER BY SUM(doctor_metric_value) DESC, specialty_key
            )
            WHERE ROWNUM <= :row_limit
        ";

        $params = $this->monthParams($year, $startMonth, $endMonth);
        $params['row_limit'] = $limit;
        $rows = Database::fetchAll($this->hospitalCode, $sql, $params);

        return array_map(static function (array $row): array {
            return [
                'id' => trim((string)($row['specialty_key'] ?? '')),
                'label' => trim((string)($row['specialty_name'] ?? 'Unknown specialty')),
                'value' => (float)($row['metric_value'] ?? 0),
            ];
        }, $rows);
    }

    /** @return array<int, array<string, mixed>> */
    public function specialtyMatrix(int $year, int $limit = 12, int $startMonth = 1, int $endMonth = 12): array
    {
        if (!$this->hasCoreTable() || !$this->columnExists($this->table, 'SPECIAL_SPEC_EN_NAME')) {
            return [];
        }

        $doctorExpr = $this->doctorGroupSql();
        $specialtyKeyExpr = $this->specialtyGroupSql();
        $moh = $this->sumDistinctSql('MOH');
        $drug = $this->sumDistinctSql('DRUG_REV');
        $net = $this->sumDistinctSql('NET_REV_AFTER_DRUG_MOH');
        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);
        $limit = max(5, min(25, $limit));

        $sql = "
            SELECT specialty_key, specialty_name, moh_value, drug_value, net_value
            FROM (
                SELECT
                    specialty_key,
                    MAX(specialty_name) specialty_name,
                    SUM(doctor_moh) moh_value,
                    SUM(doctor_drug) drug_value,
                    SUM(doctor_net) net_value
                FROM (
                    SELECT
                        {$specialtyKeyExpr} specialty_key,
                        MAX(SPECIAL_SPEC_EN_NAME) specialty_name,
                        {$doctorExpr} doctor_key,
                        {$moh} doctor_moh,
                        {$drug} doctor_drug,
                        {$net} doctor_net
                    FROM KPI_DOCTOR_PERF_DETAIL_JOB
                    WHERE YEARID = :year
                      {$monthFilter}
                    GROUP BY {$specialtyKeyExpr}, {$doctorExpr}
                )
                GROUP BY specialty_key
                HAVING ABS(SUM(doctor_moh)) + ABS(SUM(doctor_drug)) + ABS(SUM(doctor_net)) <> 0
                ORDER BY ABS(SUM(doctor_net)) DESC, specialty_key
            )
            WHERE ROWNUM <= :row_limit
        ";

        $params = $this->monthParams($year, $startMonth, $endMonth);
        $params['row_limit'] = $limit;
        $rows = Database::fetchAll($this->hospitalCode, $sql, $params);

        return array_map(static function (array $row): array {
            return [
                'id' => trim((string)($row['specialty_key'] ?? '')),
                'label' => trim((string)($row['specialty_name'] ?? 'Unknown specialty')),
                'moh_value' => (float)($row['moh_value'] ?? 0),
                'drug_value' => (float)($row['drug_value'] ?? 0),
                'net_value' => (float)($row['net_value'] ?? 0),
            ];
        }, $rows);
    }

    /**
     * Admissions and outpatient source contract used by Specialty Finance.
     *
     * No metric is silently replaced by zero when a required source column is
     * missing. The caller must stop the recovery gate and report the missing
     * contract explicitly.
     *
     * @return array<string, mixed>
     */
    public function activitySourceContract(): array
    {
        $required = [
            'YEARID',
            'MONTHID',
            'SPECIAL_SPEC_ID',
            'SPECIAL_SPEC_EN_NAME',
            'TOTAL_OP_VISITS',
            'TOTAL_INPAT_VISITS',
        ];
        $columns = [];

        foreach ($required as $column) {
            $columns[$column] = $this->hasCoreTable()
                && $this->columnExists($this->table, $column);
        }

        $doctorColumn = null;
        foreach (['EMP_ID', 'DOCTOR_ID', 'EMP_EN_DATA'] as $candidate) {
            if ($this->hasCoreTable() && $this->columnExists($this->table, $candidate)) {
                $doctorColumn = $candidate;
                break;
            }
        }

        $missing = [];
        foreach ($columns as $column => $available) {
            if (!$available) {
                $missing[] = $column;
            }
        }
        if ($doctorColumn === null) {
            $missing[] = 'DOCTOR_IDENTITY';
        }

        return [
            'table' => $this->table,
            'columns' => $columns,
            'doctor_identity_column' => $doctorColumn,
            'missing' => $missing,
            'ready' => $missing === [],
            'aggregation' => 'SPECIALTY + DOCTOR deduplication before specialty totals',
            'oracle_writes' => 'NONE',
        ];
    }

    /**
     * Matched-period Admissions and Outpatient Visits by specialty.
     *
     * @return array<int, array<string, mixed>>
     */
    public function specialtyActivityComparison(
        int $year,
        int $compareYear,
        int $startMonth = 1,
        int $endMonth = 12
    ): array {
        $this->assertActivitySourceContract();

        $doctorExpr = $this->doctorGroupSql();
        $specialtyKeyExpr = $this->specialtyGroupSql();
        $opdSql = $this->sumDistinctSql('TOTAL_OP_VISITS');
        $admissionsSql = $this->sumDistinctSql('TOTAL_INPAT_VISITS');
        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);

        $sql = "
            WITH parameters AS (
                SELECT
                    :current_year current_year,
                    :compare_year compare_year
                FROM dual
            ),
            per_doctor_specialty AS (
                SELECT
                    YEARID,
                    {$specialtyKeyExpr} specialty_key,
                    MAX(SPECIAL_SPEC_EN_NAME) specialty_name,
                    {$doctorExpr} doctor_key,
                    {$opdSql} outpatient_visits,
                    {$admissionsSql} admissions
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                CROSS JOIN parameters
                WHERE YEARID IN (parameters.current_year, parameters.compare_year)
                  {$monthFilter}
                GROUP BY YEARID, {$specialtyKeyExpr}, {$doctorExpr}
            ),
            specialty_totals AS (
                SELECT
                    specialty_key,
                    MAX(specialty_name) specialty_name,
                    NVL(SUM(CASE WHEN YEARID = parameters.current_year THEN outpatient_visits ELSE 0 END), 0) current_outpatient_visits,
                    NVL(SUM(CASE WHEN YEARID = parameters.compare_year THEN outpatient_visits ELSE 0 END), 0) compare_outpatient_visits,
                    NVL(SUM(CASE WHEN YEARID = parameters.current_year THEN admissions ELSE 0 END), 0) current_admissions,
                    NVL(SUM(CASE WHEN YEARID = parameters.compare_year THEN admissions ELSE 0 END), 0) compare_admissions
                FROM per_doctor_specialty
                CROSS JOIN parameters
                GROUP BY specialty_key
            )
            SELECT
                specialty_key,
                specialty_name,
                current_outpatient_visits,
                compare_outpatient_visits,
                current_admissions,
                compare_admissions
            FROM specialty_totals
            WHERE ABS(current_outpatient_visits)
                + ABS(compare_outpatient_visits)
                + ABS(current_admissions)
                + ABS(compare_admissions) <> 0
            ORDER BY
                ABS(current_outpatient_visits) + ABS(current_admissions) DESC,
                specialty_name,
                specialty_key
        ";

        $params = [
            'current_year' => $year,
            'compare_year' => $compareYear,
        ];
        if (!($startMonth === 1 && $endMonth === 12)) {
            $params['start_month'] = $startMonth;
            $params['end_month'] = $endMonth;
        }

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

        return array_map(static function (array $row): array {
            return [
                'id' => trim((string)($row['specialty_key'] ?? '')),
                'label' => trim((string)($row['specialty_name'] ?? 'Unknown specialty')),
                'current_outpatient_visits' => (float)($row['current_outpatient_visits'] ?? 0),
                'compare_outpatient_visits' => (float)($row['compare_outpatient_visits'] ?? 0),
                'current_admissions' => (float)($row['current_admissions'] ?? 0),
                'compare_admissions' => (float)($row['compare_admissions'] ?? 0),
            ];
        }, $rows);
    }

    /**
     * Monthly Admissions and Outpatient Visits by specialty for the two
     * matched periods.
     *
     * @return array<int, array<string, mixed>>
     */
    public function specialtyActivityMonthlyComparison(
        int $year,
        int $compareYear,
        int $startMonth = 1,
        int $endMonth = 12
    ): array {
        $this->assertActivitySourceContract();

        $doctorExpr = $this->doctorGroupSql();
        $specialtyKeyExpr = $this->specialtyGroupSql();
        $opdSql = $this->sumDistinctSql('TOTAL_OP_VISITS');
        $admissionsSql = $this->sumDistinctSql('TOTAL_INPAT_VISITS');
        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);

        $sql = "
            WITH parameters AS (
                SELECT
                    :current_year current_year,
                    :compare_year compare_year
                FROM dual
            ),
            per_doctor_specialty_month AS (
                SELECT
                    YEARID,
                    MONTHID,
                    {$specialtyKeyExpr} specialty_key,
                    MAX(SPECIAL_SPEC_EN_NAME) specialty_name,
                    {$doctorExpr} doctor_key,
                    {$opdSql} outpatient_visits,
                    {$admissionsSql} admissions
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                CROSS JOIN parameters
                WHERE YEARID IN (parameters.current_year, parameters.compare_year)
                  {$monthFilter}
                GROUP BY YEARID, MONTHID, {$specialtyKeyExpr}, {$doctorExpr}
            )
            SELECT
                specialty_key,
                MAX(specialty_name) specialty_name,
                MONTHID month_id,
                NVL(SUM(CASE WHEN YEARID = parameters.current_year THEN outpatient_visits ELSE 0 END), 0) current_outpatient_visits,
                NVL(SUM(CASE WHEN YEARID = parameters.compare_year THEN outpatient_visits ELSE 0 END), 0) compare_outpatient_visits,
                NVL(SUM(CASE WHEN YEARID = parameters.current_year THEN admissions ELSE 0 END), 0) current_admissions,
                NVL(SUM(CASE WHEN YEARID = parameters.compare_year THEN admissions ELSE 0 END), 0) compare_admissions
            FROM per_doctor_specialty_month
            CROSS JOIN parameters
            GROUP BY specialty_key, MONTHID
            HAVING ABS(SUM(CASE WHEN YEARID = parameters.current_year THEN outpatient_visits ELSE 0 END))
                + ABS(SUM(CASE WHEN YEARID = parameters.compare_year THEN outpatient_visits ELSE 0 END))
                + ABS(SUM(CASE WHEN YEARID = parameters.current_year THEN admissions ELSE 0 END))
                + ABS(SUM(CASE WHEN YEARID = parameters.compare_year THEN admissions ELSE 0 END)) <> 0
            ORDER BY specialty_name, specialty_key, MONTHID
        ";

        $params = [
            'current_year' => $year,
            'compare_year' => $compareYear,
        ];
        if (!($startMonth === 1 && $endMonth === 12)) {
            $params['start_month'] = $startMonth;
            $params['end_month'] = $endMonth;
        }

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

        return array_map(static function (array $row): array {
            return [
                'id' => trim((string)($row['specialty_key'] ?? '')),
                'label' => trim((string)($row['specialty_name'] ?? 'Unknown specialty')),
                'month_id' => (int)($row['month_id'] ?? 0),
                'current_outpatient_visits' => (float)($row['current_outpatient_visits'] ?? 0),
                'compare_outpatient_visits' => (float)($row['compare_outpatient_visits'] ?? 0),
                'current_admissions' => (float)($row['current_admissions'] ?? 0),
                'compare_admissions' => (float)($row['compare_admissions'] ?? 0),
            ];
        }, $rows);
    }

    /**
     * Period totals using the same established doctor-level contract as the
     * Doctors workspace. Used only for reconciliation gates.
     *
     * @return array<string, float>
     */
    public function activityDoctorTotals(
        int $year,
        int $startMonth = 1,
        int $endMonth = 12
    ): array {
        $this->assertActivitySourceContract();

        return [
            'outpatient_visits' => $this->annualActivityMetricTotal(
                $year,
                'TOTAL_OP_VISITS',
                $startMonth,
                $endMonth
            ),
            'admissions' => $this->annualActivityMetricTotal(
                $year,
                'TOTAL_INPAT_VISITS',
                $startMonth,
                $endMonth
            ),
        ];
    }

    private function annualActivityMetricTotal(
        int $year,
        string $metric,
        int $startMonth,
        int $endMonth
    ): float {
        if (!in_array($metric, ['TOTAL_OP_VISITS', 'TOTAL_INPAT_VISITS'], true)) {
            throw new \InvalidArgumentException('INVALID_SPECIALTY_ACTIVITY_METRIC');
        }

        $doctorExpr = $this->doctorGroupSql();
        $metricSql = $this->sumDistinctSql($metric);
        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);

        $sql = "
            SELECT NVL(SUM(metric_value), 0) total_value
            FROM (
                SELECT
                    {$doctorExpr} doctor_key,
                    {$metricSql} metric_value
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :year
                  {$monthFilter}
                GROUP BY {$doctorExpr}
            )
        ";

        $row = Database::fetchOne(
            $this->hospitalCode,
            $sql,
            $this->monthParams($year, $startMonth, $endMonth)
        );

        return (float)($row['total_value'] ?? 0);
    }

    private function assertActivitySourceContract(): void
    {
        $contract = $this->activitySourceContract();
        if (empty($contract['ready'])) {
            throw new \RuntimeException(
                'SPECIALTY_ACTIVITY_SOURCE_CONTRACT_UNAVAILABLE missing='
                . implode(',', (array)($contract['missing'] ?? []))
            );
        }
    }

    /** @return array<string,mixed> */
    public function yearCoverage(int $year): array
    {
        if (!$this->hasCoreTable() || $year <= 0) {
            return [
                'year' => $year,
                'row_count' => 0,
                'specialty_count' => 0,
                'doctor_count' => 0,
                'has_data' => false,
            ];
        }

        $doctorExpr = $this->doctorGroupSql();
        $specialtyExpr = $this->specialtyGroupSql();

        $row = Database::fetchOne($this->hospitalCode, "
            SELECT
                COUNT(*) row_count,
                COUNT(DISTINCT {$specialtyExpr}) specialty_count,
                COUNT(DISTINCT {$doctorExpr}) doctor_count
            FROM KPI_DOCTOR_PERF_DETAIL_JOB
            WHERE YEARID = :year
        ", ['year' => $year]) ?: [];

        $rowCount = (int)($row['row_count'] ?? 0);

        return [
            'year' => $year,
            'row_count' => $rowCount,
            'specialty_count' => (int)($row['specialty_count'] ?? 0),
            'doctor_count' => (int)($row['doctor_count'] ?? 0),
            'has_data' => $rowCount > 0,
        ];
    }

    /** @return array<string,mixed> */
    public function monthCoverage(int $year): array
    {
        if (!$this->hasCoreTable() || !$this->columnExists($this->table, 'MONTHID')) {
            return [
                'year' => $year,
                'min_month' => null,
                'max_month' => null,
                'distinct_months' => 0,
                'months' => [],
            ];
        }

        $rows = Database::fetchAll($this->hospitalCode, "
            SELECT MONTHID, COUNT(*) row_count
            FROM KPI_DOCTOR_PERF_DETAIL_JOB
            WHERE YEARID = :year
              AND MONTHID BETWEEN 1 AND 12
            GROUP BY MONTHID
            ORDER BY MONTHID
        ", ['year' => $year]);

        $months = [];
        foreach ($rows as $row) {
            $month = (int)($row['monthid'] ?? 0);
            if ($month >= 1 && $month <= 12) {
                $months[$month] = (int)($row['row_count'] ?? 0);
            }
        }

        $keys = array_keys($months);
        return [
            'year' => $year,
            'min_month' => $keys ? min($keys) : null,
            'max_month' => $keys ? max($keys) : null,
            'distinct_months' => count($keys),
            'months' => $months,
        ];
    }

    private function monthFilterSql(int $startMonth, int $endMonth): string
    {
        $startMonth = max(1, min(12, $startMonth));
        $endMonth = max(0, min(12, $endMonth));

        if ($endMonth < $startMonth) {
            return 'AND 1 = 0';
        }

        if ($startMonth === 1 && $endMonth === 12) {
            return '';
        }

        if (!$this->columnExists($this->table, 'MONTHID')) {
            throw new \RuntimeException('SPECIALTY_FINANCE_MONTH_GRAIN_UNAVAILABLE');
        }

        return 'AND MONTHID BETWEEN :start_month AND :end_month';
    }

    /** @return array<string,int> */
    private function monthParams(int $year, int $startMonth, int $endMonth): array
    {
        $params = ['year' => $year];
        $startMonth = max(1, min(12, $startMonth));
        $endMonth = max(0, min(12, $endMonth));

        if ($endMonth >= $startMonth && !($startMonth === 1 && $endMonth === 12)) {
            $params['start_month'] = $startMonth;
            $params['end_month'] = $endMonth;
        }

        return $params;
    }

    /** @return array<int, int> */
    public function availableYears(int $limit = 20): array
    {
        if (!$this->hasCoreTable()) {
            return [];
        }
        $limit = max(3, min(30, $limit));
        $sql = "
            SELECT yearid
            FROM (
                SELECT yearid
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE yearid IS NOT NULL
                GROUP BY yearid
                ORDER BY yearid DESC
            )
            WHERE ROWNUM <= :row_limit
        ";
        try {
            $rows = Database::fetchAll($this->hospitalCode, $sql, ['row_limit' => $limit]);
        } catch (Throwable $e) {
            return [];
        }
        $years = [];
        foreach ($rows as $row) {
            $value = (int)($row['yearid'] ?? 0);
            if ($value > 0 && !in_array($value, $years, true)) {
                $years[] = $value;
            }
        }
        return $years;
    }

    /** @return array<int, array<string, string>> */
    public function availableMetrics(): array
    {
        $defs = [
            ['key' => 'MOH', 'label' => 'MOH revenue'],
            ['key' => 'DRUG_REV', 'label' => 'Drug revenue'],
            ['key' => 'NET_REV_AFTER_DRUG_MOH', 'label' => 'Net after drug / MOH'],
        ];
        if (!$this->hasCoreTable()) {
            return [];
        }
        $out = [];
        foreach ($defs as $row) {
            if ($this->columnExists($this->table, $row['key'])) {
                $out[] = $row;
            }
        }
        return $out;
    }

    private function hasCoreTable(): bool
    {
        return $this->tableExists($this->table) && $this->columnExists($this->table, 'YEARID');
    }

    private function normalizeMetric(string $metric): string
    {
        $metric = strtoupper(trim($metric));
        $allowed = ['MOH', 'DRUG_REV', 'NET_REV_AFTER_DRUG_MOH'];
        return in_array($metric, $allowed, true) ? $metric : 'NET_REV_AFTER_DRUG_MOH';
    }

    private function doctorGroupSql(): string
    {
        if ($this->columnExists($this->table, 'EMP_ID')) {
            return 'EMP_ID';
        }
        if ($this->columnExists($this->table, 'DOCTOR_ID')) {
            return 'DOCTOR_ID';
        }
        if ($this->columnExists($this->table, 'EMP_EN_DATA')) {
            return 'EMP_EN_DATA';
        }
        return "'UNKNOWN'";
    }

    private function specialtyGroupSql(): string
    {
        if ($this->columnExists($this->table, 'SPECIAL_SPEC_ID')) {
            return 'SPECIAL_SPEC_ID';
        }
        if ($this->columnExists($this->table, 'SPECIAL_SPEC_EN_NAME')) {
            return 'SPECIAL_SPEC_EN_NAME';
        }
        return "'UNKNOWN'";
    }

    private function sumDistinctSql(string $column): string
    {
        if (!$this->columnExists($this->table, $column)) {
            return '0';
        }
        return 'NVL(SUM(DISTINCT NVL(' . strtoupper($column) . ', 0)), 0)';
    }

    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);
        } catch (Throwable $e) {
            $this->columnCache[$cacheKey] = false;
        }
        return $this->columnCache[$cacheKey];
    }

    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);
        } catch (Throwable $e) {
            $this->tableCache[$cacheKey] = false;
        }
        return $this->tableCache[$cacheKey];
    }
}
