<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Independent reconstructed legacy execution path for dispatcher-confirmed functions:
 * - moh_revn  -> get_moh_revn
 * - drug_rev  -> get_drug_rev
 * - net_rev   -> get_net_rev
 *
 * Evidence boundary: the full original function bodies are not present in the supplied
 * legacy archive. The reconstructed contract uses the established annual doctor KPI source
 * KPI_DOCTOR_PERF_DETAIL_JOB and preserves the same doctor-level deduplication contract
 * already validated for the doctor KPI module. This class never calls SpecialtyFinanceRepository.
 */
final class LegacySpecialtyFinanceRepository
{
    /** @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);
    }

    /**
     * Exact historical special_prof / get_special_prof execution path.
     *
     * This preserves the recovered old nested-query contract and does not
     * call SpecialtyFinanceRepository.
     *
     * @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(
                    'LEGACY_SPECIALTY_TOTAL_REVENUE_SOURCE_CONTRACT_UNAVAILABLE column=' . $requiredColumn
                );
            }
        }

        $sql = "
            SELECT Month_Wise_Count, Month_Name, Specialty_Id
            FROM (
                SELECT
                    SUM(DISTINCT TOT_REV) Month_Wise_Count,
                    SPECIAL_SPEC_EN_NAME Month_Name,
                    SPECIAL_SPEC_ID Specialty_Id
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :legacy_year
                GROUP BY SPECIAL_SPEC_ID, SPECIAL_SPEC_EN_NAME
            )
            WHERE Month_Wise_Count > 50000
            ORDER BY Month_Wise_Count DESC
        ";

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

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

    /**
     * Exact historical get_net_rev execution path used by both dispatcher
     * aliases: special_net_rev and net_rev.
     *
     * @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(
                    'LEGACY_SPECIALTY_NET_REVENUE_SOURCE_CONTRACT_UNAVAILABLE column=' . $requiredColumn
                );
            }
        }

        $sql = "
            SELECT Month_Wise_Count, Month_Name, Specialty_Id
            FROM (
                SELECT
                    SUM(DISTINCT net_rev_after_drug_moh) Month_Wise_Count,
                    SPECIAL_SPEC_EN_NAME Month_Name,
                    SPECIAL_SPEC_ID Specialty_Id
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :legacy_year
                GROUP BY SPECIAL_SPEC_ID, SPECIAL_SPEC_EN_NAME
            )
            WHERE Month_Wise_Count > 50000
            ORDER BY Month_Wise_Count DESC
        ";

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

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

    public function mohRevenue(int $year): float
    {
        return $this->legacyAnnualMetric($year, 'MOH');
    }

    public function drugRevenue(int $year): float
    {
        return $this->legacyAnnualMetric($year, 'DRUG_REV');
    }

    public function netRevenue(int $year): float
    {
        return $this->legacyAnnualMetric($year, 'NET_REV_AFTER_DRUG_MOH');
    }

    private function legacyAnnualMetric(int $year, string $metric): float
    {
        $metric = strtoupper(trim($metric));
        $allowed = ['MOH', 'DRUG_REV', 'NET_REV_AFTER_DRUG_MOH'];
        if (!in_array($metric, $allowed, true) || !$this->hasCoreTable() || !$this->columnExists($this->table, $metric)) {
            return 0.0;
        }

        $doctorExpr = $this->doctorGroupSql();
        $metricSql = $this->sumDistinctSql($metric);

        // Independent old-style nested SELECT contract.
        $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 = :legacy_year
                GROUP BY {$doctorExpr}
            )
        ";

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

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

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