<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

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

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

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

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

    /**
     * Monthly outpatient visits.
     *
     * This version is schema-aware: some branches do not have CLINIC_ID in
     * RCP_SERVICE_RESERVATION. The repository detects where the column exists
     * and builds only valid SQL, so Oracle will not fail with ORA-00904.
     *
     * @return array<int, array<string, mixed>>
     */
    public function monthlyOutpatientVisits(int $year, int $compareYear): array
    {
        $filter = $this->outpatientFilterSql();
        $sql = "
            WITH p AS (
                SELECT :current_year current_year, :compare_year compare_year FROM dual
            ),
            months AS (
                SELECT code month_id, name_en month_name
                FROM sec_lookup_detail
                WHERE parent_id = 1
            ),
            agg AS (
                SELECT
                    EXTRACT(YEAR FROM r.expecteddonedate) year_id,
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                    COUNT(1) value
                FROM rcp_visit v
                JOIN rcp_service_reservation r
                    ON v.hosp_id = r.hosp_id
                   AND v.patientid = r.patientid
                   AND v.visit_id = r.visit_id
                CROSS JOIN p
                WHERE v.classcode = 2
                  AND EXTRACT(YEAR FROM r.expecteddonedate) IN (p.current_year, p.compare_year)
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                  {$filter}
                GROUP BY
                    EXTRACT(YEAR FROM r.expecteddonedate),
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            )
            SELECT
                m.month_id,
                m.month_name,
                NVL(SUM(CASE WHEN a.year_id = p.current_year THEN a.value ELSE 0 END), 0) current_value,
                NVL(SUM(CASE WHEN a.year_id = p.compare_year THEN a.value ELSE 0 END), 0) compare_value
            FROM months m
            CROSS JOIN p
            LEFT JOIN agg a ON a.month_id = m.month_id
            GROUP BY m.month_id, m.month_name
            ORDER BY m.month_id
        ";

        return $this->monthlyRows($sql, $year, $compareYear);
    }

    /** @return array<int, array<string, mixed>> */
    public function monthlyEmergencyVisits(int $year, int $compareYear): array
    {
        $filter = $this->emergencyFilterSql();
        $sql = "
            WITH p AS (
                SELECT :current_year current_year, :compare_year compare_year FROM dual
            ),
            months AS (
                SELECT code month_id, name_en month_name
                FROM sec_lookup_detail
                WHERE parent_id = 1
            ),
            agg AS (
                SELECT
                    EXTRACT(YEAR FROM r.expecteddonedate) year_id,
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                    COUNT(1) value
                FROM rcp_visit v
                JOIN rcp_service_reservation r
                    ON v.hosp_id = r.hosp_id
                   AND v.patientid = r.patientid
                   AND v.visit_id = r.visit_id
                CROSS JOIN p
                WHERE v.classcode = 2
                  AND EXTRACT(YEAR FROM r.expecteddonedate) IN (p.current_year, p.compare_year)
                  AND r.servstatus NOT IN ('C', 'F')
                  AND r.sched_serial IS NOT NULL
                  {$filter}
                GROUP BY
                    EXTRACT(YEAR FROM r.expecteddonedate),
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            )
            SELECT
                m.month_id,
                m.month_name,
                NVL(SUM(CASE WHEN a.year_id = p.current_year THEN a.value ELSE 0 END), 0) current_value,
                NVL(SUM(CASE WHEN a.year_id = p.compare_year THEN a.value ELSE 0 END), 0) compare_value
            FROM months m
            CROSS JOIN p
            LEFT JOIN agg a ON a.month_id = m.month_id
            GROUP BY m.month_id, m.month_name
            ORDER BY m.month_id
        ";

        return $this->monthlyRows($sql, $year, $compareYear);
    }

    /** @return array<int, array<string, mixed>> */
    public function monthlyAdmissions(int $year, int $compareYear): array
    {
        $sql = "
            WITH p AS (
                SELECT :current_year current_year, :compare_year compare_year FROM dual
            ),
            months AS (
                SELECT code month_id, name_en month_name
                FROM sec_lookup_detail
                WHERE parent_id = 1
            ),
            agg AS (
                SELECT
                    EXTRACT(YEAR FROM v.visit_start_date) year_id,
                    TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM')) month_id,
                    COUNT(1) value
                FROM rcp_entryexit e
                JOIN rcp_visit v
                    ON v.hosp_id = e.branchid
                   AND v.patientid = e.patientid
                   AND v.visit_id = e.visit_id
                CROSS JOIN p
                WHERE v.hosp_id = 1
                  AND NVL(e.baby_normal_bed, 1) = 1
                  AND EXTRACT(YEAR FROM v.visit_start_date) IN (p.current_year, p.compare_year)
                GROUP BY
                    EXTRACT(YEAR FROM v.visit_start_date),
                    TO_NUMBER(TO_CHAR(v.visit_start_date, 'MM'))
            )
            SELECT
                m.month_id,
                m.month_name,
                NVL(SUM(CASE WHEN a.year_id = p.current_year THEN a.value ELSE 0 END), 0) current_value,
                NVL(SUM(CASE WHEN a.year_id = p.compare_year THEN a.value ELSE 0 END), 0) compare_value
            FROM months m
            CROSS JOIN p
            LEFT JOIN agg a ON a.month_id = m.month_id
            GROUP BY m.month_id, m.month_name
            ORDER BY m.month_id
        ";

        return $this->monthlyRows($sql, $year, $compareYear);
    }

    /** @return array<int, array<string, mixed>> */
    public function followupMix(int $year): array
    {
        $resType = $this->columnExpression('RES_TYPE', [
            'RCP_SERVICE_RESERVATION' => 'r',
            'RCP_VISIT' => 'v',
        ]);

        if ($resType === null) {
            return [
                ['category' => 'new', 'value' => 0],
                ['category' => 'followup', 'value' => 0],
            ];
        }

        $sql = "
            SELECT
                CASE WHEN {$resType} = 1 THEN 'followup' ELSE 'new' END category,
                COUNT(1) value
            FROM rcp_service_reservation r
            JOIN rcp_visit v
                ON v.hosp_id = r.hosp_id
               AND v.visit_id = r.visit_id
               AND v.patientid = r.patientid
            WHERE r.hosp_id IN (1)
              AND EXTRACT(YEAR FROM r.expecteddonedate) = :year
              AND r.servstatus NOT IN ('C', 'F')
              AND r.sched_serial IS NOT NULL
            GROUP BY CASE WHEN {$resType} = 1 THEN 'followup' ELSE 'new' END
        ";

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

    /** @return array<int, array<string, mixed>> */
    public function dailyOutpatientTrend(int $days = 14): array
    {
        $days = max(7, min(60, $days));
        $filter = $this->outpatientFilterSql();
        $sql = "
            SELECT
                TRUNC(r.expecteddonedate) day_id,
                TO_CHAR(TRUNC(r.expecteddonedate), 'DD Mon') day_label,
                COUNT(1) value
            FROM rcp_visit v
            JOIN rcp_service_reservation r
                ON v.hosp_id = r.hosp_id
               AND v.patientid = r.patientid
               AND v.visit_id = r.visit_id
            WHERE v.classcode = 2
              AND TRUNC(r.expecteddonedate) >= TRUNC(SYSDATE) - :days_back
              AND TRUNC(r.expecteddonedate) <= TRUNC(SYSDATE)
              AND r.servstatus NOT IN ('C', 'F')
              AND r.sched_serial IS NOT NULL
              {$filter}
            GROUP BY TRUNC(r.expecteddonedate), TO_CHAR(TRUNC(r.expecteddonedate), 'DD Mon')
            ORDER BY TRUNC(r.expecteddonedate)
        ";

        return Database::fetchAll($this->hospitalCode, $sql, ['days_back' => $days - 1]);
    }

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

        foreach (['YEARID', 'SPECIAL_SPEC_ID', 'SPECIAL_SPEC_EN_NAME', 'TOTAL_OP_VISITS'] as $column) {
            if (!$this->columnExists('KPI_DOCTOR_PERF_DETAIL_JOB', $column)) {
                return [];
            }
        }

        $sql = "
            SELECT specialty_name, value
            FROM (
                SELECT
                    special_spec_en_name specialty_name,
                    SUM(DISTINCT total_op_visits) value
                FROM kpi_doctor_perf_detail_job
                WHERE yearid = :year
                GROUP BY special_spec_id, special_spec_en_name
                HAVING SUM(DISTINCT total_op_visits) > 0
                ORDER BY SUM(DISTINCT total_op_visits) DESC
            )
            WHERE ROWNUM <= 10
        ";

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

    /** @return array<int, array<string, mixed>> */
    public function emptyMonthlyRows(): array
    {
        $rows = [];
        $months = [
            1 => 'January', 2 => 'February', 3 => 'March', 4 => 'April',
            5 => 'May', 6 => 'June', 7 => 'July', 8 => 'August',
            9 => 'September', 10 => 'October', 11 => 'November', 12 => 'December',
        ];
        foreach ($months as $id => $name) {
            $rows[] = [
                'month_id' => $id,
                'month_name' => $name,
                'current_value' => 0.0,
                'compare_value' => 0.0,
            ];
        }
        return $rows;
    }

    private function outpatientFilterSql(): string
    {
        $emergencyType = $this->columnExpression('OUTPTA_EMERGENCY_TYPE', [
            'RCP_SERVICE_RESERVATION' => 'r',
            'RCP_VISIT' => 'v',
        ]);
        $clinicId = $this->columnExpression('CLINIC_ID', [
            'RCP_SERVICE_RESERVATION' => 'r',
            'RCP_VISIT' => 'v',
        ]);

        $parts = [];
        if ($emergencyType !== null) {
            $parts[] = 'NVL(' . $emergencyType . ', 0) <> 1';
        }
        if ($clinicId !== null) {
            $parts[] = 'NVL(' . $clinicId . ', 0) NOT IN (16, 297, 298)';
        }

        if (!$parts) {
            return '';
        }

        return 'AND (' . implode(' AND ', $parts) . ')';
    }

    private function emergencyFilterSql(): string
    {
        $emergencyType = $this->columnExpression('OUTPTA_EMERGENCY_TYPE', [
            'RCP_SERVICE_RESERVATION' => 'r',
            'RCP_VISIT' => 'v',
        ]);
        $clinicId = $this->columnExpression('CLINIC_ID', [
            'RCP_SERVICE_RESERVATION' => 'r',
            'RCP_VISIT' => 'v',
        ]);

        $parts = [];
        if ($emergencyType !== null) {
            $parts[] = 'NVL(' . $emergencyType . ', 0) = 1';
        }
        if ($clinicId !== null) {
            $parts[] = 'NVL(' . $clinicId . ', 0) IN (16, 297, 298)';
        }

        if (!$parts) {
            // No emergency discriminator exists in this branch schema.
            // Return zero ER data instead of breaking the whole Visits workspace.
            return 'AND 1 = 0';
        }

        return 'AND (' . implode(' OR ', $parts) . ')';
    }

    /**
     * @param array<string, string> $tableAliasMap table name => SQL alias
     */
    private function columnExpression(string $column, array $tableAliasMap): ?string
    {
        foreach ($tableAliasMap as $table => $alias) {
            if ($this->columnExists($table, $column)) {
                return $alias . '.' . strtolower($column);
            }
        }
        return null;
    }

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

    /**
     * @return array<int, array<string, mixed>>
     */
    private function monthlyRows(string $sql, int $year, int $compareYear): array
    {
        $rows = Database::fetchAll($this->hospitalCode, $sql, [
            'current_year' => $year,
            'compare_year' => $compareYear,
        ]);

        return array_map(function (array $row): array {
            return [
                'month_id' => (int) ($row['month_id'] ?? 0),
                'month_name' => trim((string) ($row['month_name'] ?? '')),
                'current_value' => (float) ($row['current_value'] ?? 0),
                'compare_value' => (float) ($row['compare_value'] ?? 0),
            ];
        }, $rows);
    }
}
