<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

final class DoctorsRepository
{
    /** @var string */
    private $hospitalCode;

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

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

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

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

    /** @return array<string, mixed> */
    public function summary(int $year, int $compareYear): array
    {
        if (!$this->hasCoreTable()) {
            return $this->emptySummary($year, $compareYear);
        }

        $sql = "
            WITH p AS (
                SELECT :current_year current_year, :compare_year compare_year FROM dual
            ),
            per_doctor AS (
                SELECT
                    yearid,
                    " . $this->doctorGroupSql() . " doctor_key,
                    " . $this->sumDistinctSql('TOTAL_OP_VISITS') . " total_op_visits,
                    " . $this->sumDistinctSql('TOTAL_INPAT_VISITS') . " total_inpat_visits,
                    " . $this->sumDistinctSql('TOT_REV') . " total_revenue,
                    " . $this->sumDistinctSql('MOH') . " moh_revenue,
                    " . $this->sumDistinctSql('DRUG_REV') . " drug_revenue,
                    " . $this->sumDistinctSql('NET_REV_AFTER_DRUG_MOH') . " net_revenue
                FROM kpi_doctor_perf_detail_job
                CROSS JOIN p
                WHERE yearid IN (p.current_year, p.compare_year)
                GROUP BY yearid, " . $this->doctorGroupSql() . "
            )
            SELECT
                NVL(SUM(CASE WHEN yearid = p.current_year THEN total_op_visits ELSE 0 END), 0) current_op_visits,
                NVL(SUM(CASE WHEN yearid = p.compare_year THEN total_op_visits ELSE 0 END), 0) compare_op_visits,
                NVL(SUM(CASE WHEN yearid = p.current_year THEN total_inpat_visits ELSE 0 END), 0) current_inpat_visits,
                NVL(SUM(CASE WHEN yearid = p.compare_year THEN total_inpat_visits ELSE 0 END), 0) compare_inpat_visits,
                NVL(SUM(CASE WHEN yearid = p.current_year THEN total_revenue ELSE 0 END), 0) current_revenue,
                NVL(SUM(CASE WHEN yearid = p.compare_year THEN total_revenue ELSE 0 END), 0) compare_revenue,
                NVL(SUM(CASE WHEN yearid = p.current_year THEN moh_revenue ELSE 0 END), 0) current_moh,
                NVL(SUM(CASE WHEN yearid = p.compare_year THEN moh_revenue ELSE 0 END), 0) compare_moh,
                NVL(SUM(CASE WHEN yearid = p.current_year THEN drug_revenue ELSE 0 END), 0) current_drug,
                NVL(SUM(CASE WHEN yearid = p.current_year THEN net_revenue ELSE 0 END), 0) current_net,
                COUNT(DISTINCT CASE WHEN yearid = p.current_year THEN doctor_key END) active_doctors
            FROM per_doctor
            CROSS JOIN p
        ";

        $row = Database::fetchOne($this->hospitalCode, $sql, [
            'current_year' => $year,
            'compare_year' => $compareYear,
        ]);

        return [
            'current_op_visits' => (float)($row['current_op_visits'] ?? 0),
            'compare_op_visits' => (float)($row['compare_op_visits'] ?? 0),
            'current_inpat_visits' => (float)($row['current_inpat_visits'] ?? 0),
            'compare_inpat_visits' => (float)($row['compare_inpat_visits'] ?? 0),
            'current_revenue' => (float)($row['current_revenue'] ?? 0),
            'compare_revenue' => (float)($row['compare_revenue'] ?? 0),
            'current_moh' => (float)($row['current_moh'] ?? 0),
            'compare_moh' => (float)($row['compare_moh'] ?? 0),
            'current_drug' => (float)($row['current_drug'] ?? 0),
            'current_net' => (float)($row['current_net'] ?? 0),
            'active_doctors' => (float)($row['active_doctors'] ?? 0),
            'year' => $year,
            'compare_year' => $compareYear,
        ];
    }

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

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

        $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)' : "''";
        $groupExpr = $this->doctorGroupSql();
        $limit = max(5, min(25, $limit));

        $sql = "
            SELECT doctor_key, doctor_name, specialty_name, value, op_visits, inpat_visits, moh_value, net_value
            FROM (
                SELECT
                    {$groupExpr} doctor_key,
                    {$nameExpr} doctor_name,
                    {$specialtyExpr} specialty_name,
                    " . $this->sumDistinctSql($metric) . " value,
                    " . $this->sumDistinctSql('TOTAL_OP_VISITS') . " op_visits,
                    " . $this->sumDistinctSql('TOTAL_INPAT_VISITS') . " inpat_visits,
                    " . $this->sumDistinctSql('MOH') . " moh_value,
                    " . $this->sumDistinctSql('NET_REV_AFTER_DRUG_MOH') . " net_value
                FROM kpi_doctor_perf_detail_job
                WHERE yearid = :year
                GROUP BY {$groupExpr}
                HAVING " . $this->sumDistinctSql($metric) . " > 0
                ORDER BY " . $this->sumDistinctSql($metric) . " DESC, {$groupExpr}
            )
            WHERE ROWNUM <= :row_limit
        ";

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

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

    /** @return array<int, array<string, mixed>> */
    public function specialties(int $year, string $metric = 'TOT_REV', 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 [];
        }

        $groupExpr = $this->columnExists($this->table, 'SPECIAL_SPEC_ID') ? 'special_spec_id, special_spec_en_name' : 'special_spec_en_name';
        $limit = max(5, min(25, $limit));

        $sql = "
            SELECT specialty_name, value, op_visits, revenue
            FROM (
                SELECT
                    special_spec_en_name specialty_name,
                    " . $this->sumDistinctSql($metric) . " value,
                    " . $this->sumDistinctSql('TOTAL_OP_VISITS') . " op_visits,
                    " . $this->sumDistinctSql('TOT_REV') . " revenue
                FROM kpi_doctor_perf_detail_job
                WHERE yearid = :year
                GROUP BY {$groupExpr}
                HAVING " . $this->sumDistinctSql($metric) . " > 0
                ORDER BY " . $this->sumDistinctSql($metric) . " DESC
            )
            WHERE ROWNUM <= :row_limit
        ";

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

        return array_map(function (array $row): array {
            return [
                'label' => trim((string)($row['specialty_name'] ?? '')),
                'value' => (float)($row['value'] ?? 0),
                'op_visits' => (float)($row['op_visits'] ?? 0),
                'revenue' => (float)($row['revenue'] ?? 0),
            ];
        }, $rows);
    }

    /** @return array<int, array<string, mixed>> */
    public function revenueMix(int $year): array
    {
        if (!$this->hasCoreTable()) {
            return [];
        }

        $columns = [
            'tot_rev' => ['column' => 'TOT_REV', 'label' => 'Total revenue'],
            'moh' => ['column' => 'MOH', 'label' => 'MOH'],
            'drug' => ['column' => 'DRUG_REV', 'label' => 'Drug revenue'],
            'net' => ['column' => 'NET_REV_AFTER_DRUG_MOH', 'label' => 'Net after drug/MOH'],
        ];

        $parts = [];
        foreach ($columns as $key => $meta) {
            if ($this->columnExists($this->table, $meta['column'])) {
                $parts[] = "SELECT '" . $key . "' metric_key, '" . $meta['label'] . "' metric_label, " . $this->sumDistinctSql($meta['column']) . " value FROM kpi_doctor_perf_detail_job k CROSS JOIN p WHERE k.yearid = p.report_year";
            }
        }

        if (!$parts) {
            return [];
        }

        $sql = "WITH p AS (SELECT :year report_year FROM dual)\n" . implode("\nUNION ALL\n", $parts);
        $rows = Database::fetchAll($this->hospitalCode, $sql, ['year' => $year]);
        return array_map(function (array $row): array {
            return [
                'key' => (string)($row['metric_key'] ?? ''),
                'label' => (string)($row['metric_label'] ?? ''),
                'value' => (float)($row['value'] ?? 0),
            ];
        }, $rows);
    }


    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);
        $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 = :year
                GROUP BY {$groupExpr}
            )
        ";

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

    /** @return array<int, int> */
    public function availableYears(string $metric = 'TOT_REV', int $limit = 12): array
    {
        if (!$this->hasCoreTable()) {
            return [];
        }

        $metric = $this->normalizeMetric($metric);
        $limit = max(3, min(30, $limit));
        $having = '';

        if ($this->columnExists($this->table, $metric)) {
            $having = 'HAVING ' . $this->sumDistinctSql($metric) . ' <> 0';
        }

        $sql = "
            SELECT yearid
            FROM (
                SELECT yearid
                FROM kpi_doctor_perf_detail_job
                WHERE yearid IS NOT NULL
                GROUP BY yearid
                {$having}
                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, mixed>> */
    public function availableMetrics(): array
    {
        $defs = [
            ['key' => 'TOT_REV', 'label_key' => 'doctors.metric_revenue'],
            ['key' => 'NET_REV_AFTER_DRUG_MOH', 'label_key' => 'doctors.metric_net'],
            ['key' => 'TOTAL_OP_VISITS', 'label_key' => 'doctors.metric_opd'],
            ['key' => 'TOTAL_INPAT_VISITS', 'label_key' => 'doctors.metric_inpatient'],
            ['key' => 'MOH', 'label_key' => 'doctors.metric_moh'],
            ['key' => 'DRUG_REV', 'label_key' => 'doctors.metric_drug'],
        ];

        if (!$this->hasCoreTable()) {
            return [];
        }

        $out = [];
        foreach ($defs as $def) {
            if ($this->columnExists($this->table, $def['key'])) {
                $out[] = $def;
            }
        }
        return $out;
    }

    /** @return array<string, mixed> */
    private function emptySummary(int $year, int $compareYear): array
    {
        return [
            'current_op_visits' => 0.0,
            'compare_op_visits' => 0.0,
            'current_inpat_visits' => 0.0,
            'compare_inpat_visits' => 0.0,
            'current_revenue' => 0.0,
            'compare_revenue' => 0.0,
            'current_moh' => 0.0,
            'compare_moh' => 0.0,
            'current_drug' => 0.0,
            'current_net' => 0.0,
            'active_doctors' => 0.0,
            'year' => $year,
            'compare_year' => $compareYear,
        ];
    }

    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 = ['TOT_REV', 'NET_REV_AFTER_DRUG_MOH', 'TOTAL_OP_VISITS', 'TOTAL_INPAT_VISITS', 'MOH', 'DRUG_REV'];
        return in_array($metric, $allowed, true) ? $metric : 'TOT_REV';
    }

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

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