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

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