<?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): array
    {
        return [
            'year' => $year,
            'compare_year' => $compareYear,
            'current_moh' => $this->annualMetricTotal($year, 'MOH'),
            'compare_moh' => $this->annualMetricTotal($compareYear, 'MOH'),
            'current_drug' => $this->annualMetricTotal($year, 'DRUG_REV'),
            'compare_drug' => $this->annualMetricTotal($compareYear, 'DRUG_REV'),
            'current_net' => $this->annualMetricTotal($year, 'NET_REV_AFTER_DRUG_MOH'),
            'compare_net' => $this->annualMetricTotal($compareYear, 'NET_REV_AFTER_DRUG_MOH'),
        ];
    }

    public function annualMetricTotal(int $year, string $metric): 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);

        // 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
                GROUP BY {$groupExpr}
            )
            SELECT NVL(SUM(metric_value), 0) total_value
            FROM per_doctor
        ";

        $row = Database::fetchOne($this->hospitalCode, $sql, ['year' => $year]);
        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): 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
                );
            }
        }

        $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
                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
        ";

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

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

        $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
                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
        ";

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

        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): 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);
        $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
                    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
        ";

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

        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): 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');
        $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
                    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
        ";

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

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

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