<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Independent read-only reference path for Admissions and Outpatient Visits
 * by specialty. This class does not call SpecialtyFinanceRepository.
 */
final class LegacySpecialtyActivityRepository
{
    /** @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<int, array<string, mixed>>
     */
    public function specialtyActivityForYear(
        int $year,
        int $startMonth = 1,
        int $endMonth = 12
    ): array {
        $this->assertContract();

        $doctorExpr = $this->doctorGroupSql();
        $specialtyExpr = $this->specialtyGroupSql();
        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);

        $sql = "
            SELECT
                specialty_key,
                MAX(specialty_name) specialty_name,
                NVL(SUM(outpatient_visits), 0) outpatient_visits,
                NVL(SUM(admissions), 0) admissions
            FROM (
                SELECT
                    {$specialtyExpr} specialty_key,
                    MAX(SPECIAL_SPEC_EN_NAME) specialty_name,
                    {$doctorExpr} doctor_key,
                    NVL(SUM(DISTINCT NVL(TOTAL_OP_VISITS, 0)), 0) outpatient_visits,
                    NVL(SUM(DISTINCT NVL(TOTAL_INPAT_VISITS, 0)), 0) admissions
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :legacy_year
                  {$monthFilter}
                GROUP BY {$specialtyExpr}, {$doctorExpr}
            )
            GROUP BY specialty_key
            HAVING ABS(SUM(outpatient_visits)) + ABS(SUM(admissions)) <> 0
            ORDER BY specialty_name, specialty_key
        ";

        $rows = Database::fetchAll(
            $this->hospitalCode,
            $sql,
            $this->monthParams($year, $startMonth, $endMonth, 'legacy_year')
        );

        return array_map(static function (array $row): array {
            return [
                'id' => trim((string)($row['specialty_key'] ?? '')),
                'label' => trim((string)($row['specialty_name'] ?? 'Unknown specialty')),
                'outpatient_visits' => (float)($row['outpatient_visits'] ?? 0),
                'admissions' => (float)($row['admissions'] ?? 0),
            ];
        }, $rows);
    }

    /**
     * @return array<int, array<string, mixed>>
     */
    public function specialtyActivityMonthlyForYear(
        int $year,
        int $startMonth = 1,
        int $endMonth = 12
    ): array {
        $this->assertContract();

        $doctorExpr = $this->doctorGroupSql();
        $specialtyExpr = $this->specialtyGroupSql();
        $monthFilter = $this->monthFilterSql($startMonth, $endMonth);

        $sql = "
            SELECT
                specialty_key,
                MAX(specialty_name) specialty_name,
                month_id,
                NVL(SUM(outpatient_visits), 0) outpatient_visits,
                NVL(SUM(admissions), 0) admissions
            FROM (
                SELECT
                    {$specialtyExpr} specialty_key,
                    MAX(SPECIAL_SPEC_EN_NAME) specialty_name,
                    MONTHID month_id,
                    {$doctorExpr} doctor_key,
                    NVL(SUM(DISTINCT NVL(TOTAL_OP_VISITS, 0)), 0) outpatient_visits,
                    NVL(SUM(DISTINCT NVL(TOTAL_INPAT_VISITS, 0)), 0) admissions
                FROM KPI_DOCTOR_PERF_DETAIL_JOB
                WHERE YEARID = :legacy_year
                  {$monthFilter}
                GROUP BY {$specialtyExpr}, MONTHID, {$doctorExpr}
            )
            GROUP BY specialty_key, month_id
            HAVING ABS(SUM(outpatient_visits)) + ABS(SUM(admissions)) <> 0
            ORDER BY specialty_name, specialty_key, month_id
        ";

        $rows = Database::fetchAll(
            $this->hospitalCode,
            $sql,
            $this->monthParams($year, $startMonth, $endMonth, 'legacy_year')
        );

        return array_map(static function (array $row): array {
            return [
                'id' => trim((string)($row['specialty_key'] ?? '')),
                'label' => trim((string)($row['specialty_name'] ?? 'Unknown specialty')),
                'month_id' => (int)($row['month_id'] ?? 0),
                'outpatient_visits' => (float)($row['outpatient_visits'] ?? 0),
                'admissions' => (float)($row['admissions'] ?? 0),
            ];
        }, $rows);
    }

    /** @return array<string, mixed> */
    public function sourceContract(): array
    {
        $required = [
            'YEARID',
            'MONTHID',
            'SPECIAL_SPEC_ID',
            'SPECIAL_SPEC_EN_NAME',
            'TOTAL_OP_VISITS',
            'TOTAL_INPAT_VISITS',
        ];
        $columns = [];
        foreach ($required as $column) {
            $columns[$column] = $this->tableExists($this->table)
                && $this->columnExists($this->table, $column);
        }

        $doctorColumn = null;
        foreach (['EMP_ID', 'DOCTOR_ID', 'EMP_EN_DATA'] as $candidate) {
            if ($this->tableExists($this->table) && $this->columnExists($this->table, $candidate)) {
                $doctorColumn = $candidate;
                break;
            }
        }

        $missing = [];
        foreach ($columns as $column => $available) {
            if (!$available) {
                $missing[] = $column;
            }
        }
        if ($doctorColumn === null) {
            $missing[] = 'DOCTOR_IDENTITY';
        }

        return [
            'table' => $this->table,
            'columns' => $columns,
            'doctor_identity_column' => $doctorColumn,
            'missing' => $missing,
            'ready' => $missing === [],
            'oracle_writes' => 'NONE',
        ];
    }

    private function assertContract(): void
    {
        $contract = $this->sourceContract();
        if (empty($contract['ready'])) {
            throw new \RuntimeException(
                'LEGACY_SPECIALTY_ACTIVITY_SOURCE_CONTRACT_UNAVAILABLE missing='
                . implode(',', (array)($contract['missing'] ?? []))
            );
        }
    }

    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';
        }
        throw new \RuntimeException('LEGACY_SPECIALTY_ACTIVITY_DOCTOR_IDENTITY_UNAVAILABLE');
    }

    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';
        }
        throw new \RuntimeException('LEGACY_SPECIALTY_ACTIVITY_SPECIALTY_IDENTITY_UNAVAILABLE');
    }

    private function monthFilterSql(int $startMonth, int $endMonth): string
    {
        $startMonth = max(1, min(12, $startMonth));
        $endMonth = max(0, min(12, $endMonth));

        if ($endMonth < $startMonth) {
            return 'AND 1 = 0';
        }
        if ($startMonth === 1 && $endMonth === 12) {
            return '';
        }

        return 'AND MONTHID BETWEEN :start_month AND :end_month';
    }

    /** @return array<string, int> */
    private function monthParams(
        int $year,
        int $startMonth,
        int $endMonth,
        string $yearKey
    ): array {
        $params = [$yearKey => $year];
        if (!($startMonth === 1 && $endMonth === 12)) {
            $params['start_month'] = $startMonth;
            $params['end_month'] = $endMonth;
        }
        return $params;
    }

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

        try {
            $rows = Database::fetchAll($this->hospitalCode, "
                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
            ", [
                '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];
        }

        try {
            $rows = Database::fetchAll($this->hospitalCode, "
                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
            ", [
                'table_name' => $table,
                'owner_name' => $schema !== '' ? $schema : null,
            ]);
            $this->tableCache[$cacheKey] = !empty($rows);
        } catch (Throwable $e) {
            $this->tableCache[$cacheKey] = false;
        }

        return $this->tableCache[$cacheKey];
    }
}
