<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Independent execution path for the dispatcher-confirmed legacy doctor functions:
 * - get_doc_rev
 * - get_total_op_visits
 * - get_total_admission
 *
 * The legacy inventory identifies KPI_DOCTOR_PERF_DETAIL_JOB as the source contract
 * for these selected-branch doctor functions. This repository intentionally does not
 * call DoctorsRepository so the QA gate can compare independent SQL paths.
 */
final class LegacyDoctorsRepository
{
    /** @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 totalOutpatientVisits(int $year): float
    {
        return $this->annualDoctorMetric($year, 'TOTAL_OP_VISITS');
    }

    public function totalAdmissions(int $year): float
    {
        return $this->annualDoctorMetric($year, 'TOTAL_INPAT_VISITS');
    }

    /** @return array<int, array<string, mixed>> */
    public function doctorRevenueRanking(int $year, int $limit = 10): array
    {
        if (!$this->hasCoreTable() || !$this->columnExists($this->table, 'TOT_REV')) {
            return [];
        }

        $groupExpr = $this->doctorGroupSql();
        $nameExpr = $this->columnExists($this->table, 'EMP_EN_DATA') ? 'MAX(emp_en_data)' : "'Unknown doctor'";
        $specialtyExpr = $this->columnExists($this->table, 'SPECIAL_SPEC_EN_NAME') ? 'MAX(special_spec_en_name)' : "''";
        $limit = max(5, min(25, $limit));

        // Direct selected-branch legacy ranking contract for get_doc_rev.
        $sql = "
            SELECT doctor_key, doctor_name, specialty_name, metric_value
            FROM (
                SELECT
                    {$groupExpr} doctor_key,
                    {$nameExpr} doctor_name,
                    {$specialtyExpr} specialty_name,
                    " . $this->sumDistinctSql('TOT_REV') . " metric_value
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :legacy_year
                GROUP BY {$groupExpr}
                HAVING " . $this->sumDistinctSql('TOT_REV') . " > 0
                ORDER BY " . $this->sumDistinctSql('TOT_REV') . " DESC, {$groupExpr}
            )
            WHERE ROWNUM <= :legacy_limit
        ";

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

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

    private function annualDoctorMetric(int $year, string $metric): float
    {
        $metric = strtoupper(trim($metric));
        if (!$this->hasCoreTable() || !$this->columnExists($this->table, $metric)) {
            return 0.0;
        }

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

        // The legacy doctor KPI table can contain multiple rows for one doctor.
        // Preserve the established doctor-level deduplication contract, then sum.
        $sql = "
            SELECT NVL(SUM(metric_value), 0) total_value
            FROM (
                SELECT
                    {$groupExpr} doctor_key,
                    {$metricSql} metric_value
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :legacy_year
                GROUP BY {$groupExpr}
            )
        ";

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