<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use DateTimeImmutable;
use Throwable;

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

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

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

    /**
     * Current selected-branch implementation of legacy popr_s.
     * Legacy rule: count reservations linked to OPER_OPERATION_SCHEDULE
     * where RCP_SERVICE_RESERVATION.SERVSTATUS <> 'C'.
     *
     * @return array<int, array<string, mixed>>
     */
    public function monthlyLegacyOperations(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        return $this->monthlyStatusRows('scheduled', $year, $compareYear, $fromDate, $toDate);
    }

    /** @return array<int, array<string, mixed>> */
    public function monthlyPerformedOperations(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        return $this->monthlyStatusRows('performed', $year, $compareYear, $fromDate, $toDate);
    }

    /** @return array<int, array<string, mixed>> */
    public function monthlyCancelledOperations(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        return $this->monthlyStatusRows('cancelled', $year, $compareYear, $fromDate, $toDate);
    }

    /**
     * @return array<string, float>
     */
    public function summary(
        int $year,
        int $compareYear,
        ?string $fromDate = null,
        ?string $toDate = null
    ): array {
        $scheduledRows = $this->monthlyLegacyOperations($year, $compareYear, $fromDate, $toDate);
        $performedRows = $this->monthlyPerformedOperations($year, $compareYear, $fromDate, $toDate);
        $cancelledRows = $this->monthlyCancelledOperations($year, $compareYear, $fromDate, $toDate);

        $scheduledCurrent = $this->sumRows($scheduledRows, 'current_value');
        $scheduledCompare = $this->sumRows($scheduledRows, 'compare_value');
        $performedCurrent = $this->sumRows($performedRows, 'current_value');
        $performedCompare = $this->sumRows($performedRows, 'compare_value');
        $cancelledCurrent = $this->sumRows($cancelledRows, 'current_value');
        $cancelledCompare = $this->sumRows($cancelledRows, 'compare_value');

        return [
            'scheduled_current' => $scheduledCurrent,
            'scheduled_compare' => $scheduledCompare,
            'performed_current' => $performedCurrent,
            'performed_compare' => $performedCompare,
            'cancelled_current' => $cancelledCurrent,
            'cancelled_compare' => $cancelledCompare,
            'completion_rate_current' => $scheduledCurrent > 0 ? ($performedCurrent / $scheduledCurrent) * 100 : 0.0,
            'completion_rate_compare' => $scheduledCompare > 0 ? ($performedCompare / $scheduledCompare) * 100 : 0.0,
            'cancellation_rate_current' => ($scheduledCurrent + $cancelledCurrent) > 0 ? ($cancelledCurrent / ($scheduledCurrent + $cancelledCurrent)) * 100 : 0.0,
            'cancellation_rate_compare' => ($scheduledCompare + $cancelledCompare) > 0 ? ($cancelledCompare / ($scheduledCompare + $cancelledCompare)) * 100 : 0.0,
        ];
    }

    /** @return array<int, array<string, mixed>> */
    public function topSpecialties(
        int $year,
        ?string $fromDate = null,
        ?string $toDate = null,
        int $limit = 10
    ): array {
        $limit = max(1, min(25, $limit));
        list($where, $params) = $this->periodWhere($year, $fromDate, $toDate, 'r.expecteddonedate');
        $sql = "
            SELECT specialty_id, specialty_name, value
            FROM (
                SELECT
                    ms.special_spec_id specialty_id,
                    NVL(ss.special_spec_en_name, 'Specialty ' || TO_CHAR(ms.special_spec_id)) specialty_name,
                    COUNT(1) value
                FROM rcp_service_reservation r
                JOIN oper_operation_schedule o ON o.ser = r.ser
                JOIN prs_emp e ON e.emp_id = r.donedoctorid
                JOIN prs_medical_staff ms ON ms.emp_id = e.emp_id
                JOIN stp_special_spec ss ON ss.special_spec_id = ms.special_spec_id
                WHERE r.servstatus <> 'C'
                  AND {$where}
                GROUP BY ms.special_spec_id, ss.special_spec_en_name
                ORDER BY COUNT(1) DESC
            )
            WHERE ROWNUM <= {$limit}
        ";
        return $this->normalizeRankRows(Database::fetchAll($this->hospitalCode, $sql, $params), 'specialty_name');
    }

    /** @return array<int, array<string, mixed>> */
    public function topDoctors(
        int $year,
        ?string $fromDate = null,
        ?string $toDate = null,
        int $limit = 10
    ): array {
        $limit = max(1, min(25, $limit));
        list($where, $params) = $this->periodWhere($year, $fromDate, $toDate, 'r.expecteddonedate');
        $sql = "
            SELECT doctor_id, doctor_name, value
            FROM (
                SELECT
                    e.emp_id doctor_id,
                    NVL(e.emp_en_data, 'Doctor ' || TO_CHAR(e.emp_id)) doctor_name,
                    COUNT(1) value
                FROM rcp_service_reservation r
                JOIN oper_operation_schedule o ON o.ser = r.ser
                JOIN prs_emp e ON e.emp_id = r.donedoctorid
                WHERE r.servstatus <> 'C'
                  AND {$where}
                GROUP BY e.emp_id, e.emp_en_data
                ORDER BY COUNT(1) DESC
            )
            WHERE ROWNUM <= {$limit}
        ";
        return $this->normalizeRankRows(Database::fetchAll($this->hospitalCode, $sql, $params), 'doctor_name');
    }

    /**
     * Uses a safe schema-capability lookup because legacy Oracle installations
     * do not expose one universal operating-room column name.
     *
     * @return array<int, array<string, mixed>>
     */
    public function topOperatingRooms(
        int $year,
        ?string $fromDate = null,
        ?string $toDate = null,
        int $limit = 10
    ): array {
        $limit = max(1, min(25, $limit));
        $room = $this->firstAvailableRoomExpression();
        if ($room === null) {
            return [];
        }

        list($where, $params) = $this->periodWhere($year, $fromDate, $toDate, 'r.expecteddonedate');
        $expr = $room['expression'];
        $sql = "
            SELECT room_key, room_name, value
            FROM (
                SELECT
                    TO_CHAR({$expr}) room_key,
                    'OR ' || TO_CHAR({$expr}) room_name,
                    COUNT(1) value
                FROM rcp_service_reservation r
                JOIN oper_operation_schedule o ON o.ser = r.ser
                WHERE r.servstatus <> 'C'
                  AND {$expr} IS NOT NULL
                  AND {$where}
                GROUP BY {$expr}
                ORDER BY COUNT(1) DESC
            )
            WHERE ROWNUM <= {$limit}
        ";
        return $this->normalizeRankRows(Database::fetchAll($this->hospitalCode, $sql, $params), 'room_name');
    }

    /** @return array<int, int> */
    public function availableYears(int $limit = 20): array
    {
        $limit = max(1, min(50, $limit));
        $sql = "
            SELECT year_id
            FROM (
                SELECT DISTINCT EXTRACT(YEAR FROM r.expecteddonedate) year_id
                FROM rcp_service_reservation r
                JOIN oper_operation_schedule o ON o.ser = r.ser
                WHERE r.servstatus <> 'C'
                  AND r.expecteddonedate IS NOT NULL
                ORDER BY EXTRACT(YEAR FROM r.expecteddonedate) DESC
            )
            WHERE ROWNUM <= {$limit}
        ";
        $rows = Database::fetchAll($this->hospitalCode, $sql);
        $out = [];
        foreach ($rows as $row) {
            $value = (int)($row['year_id'] ?? 0);
            if ($value > 0) {
                $out[] = $value;
            }
        }
        return array_values(array_unique($out));
    }

    /** @return array<int, array<string, mixed>> */
    public function emptyMonthlyRows(): array
    {
        $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',
        ];
        $rows = [];
        foreach ($months as $monthId => $monthName) {
            $rows[] = [
                'month_id' => $monthId,
                'month_name' => $monthName,
                'current_value' => 0.0,
                'compare_value' => 0.0,
            ];
        }
        return $rows;
    }

    public static function shiftDateToYear(string $date, int $targetYear): string
    {
        $source = DateTimeImmutable::createFromFormat('!Y-m-d', $date);
        if (!$source) {
            return sprintf('%04d-01-01', $targetYear);
        }

        $month = (int)$source->format('m');
        $day = (int)$source->format('d');
        while ($day >= 1) {
            $candidate = sprintf('%04d-%02d-%02d', $targetYear, $month, $day);
            $parsed = DateTimeImmutable::createFromFormat('!Y-m-d', $candidate);
            if ($parsed && $parsed->format('Y-m-d') === $candidate) {
                return $candidate;
            }
            $day--;
        }
        return sprintf('%04d-%02d-01', $targetYear, $month);
    }

    /** @return array<int, array<string, mixed>> */
    private function monthlyStatusRows(
        string $mode,
        int $year,
        int $compareYear,
        ?string $fromDate,
        ?string $toDate
    ): array {
        $statusCondition = $this->statusCondition($mode);

        if ($fromDate !== null && $toDate !== null) {
            $compareFrom = self::shiftDateToYear($fromDate, $compareYear);
            $compareTo = self::shiftDateToYear($toDate, $compareYear);
            $sql = "
                WITH months AS (
                    SELECT code month_id, name_en month_name
                    FROM sec_lookup_detail
                    WHERE parent_id = 1
                ),
                agg AS (
                    SELECT
                        'CURRENT' period_key,
                        TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                        COUNT(1) value
                    FROM rcp_service_reservation r
                    JOIN oper_operation_schedule o ON o.ser = r.ser
                    WHERE {$statusCondition}
                      AND r.expecteddonedate >= TO_DATE(:current_from, 'YYYY-MM-DD')
                      AND r.expecteddonedate < TO_DATE(:current_to, 'YYYY-MM-DD') + 1
                    GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))

                    UNION ALL

                    SELECT
                        'COMPARE' period_key,
                        TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM')) month_id,
                        COUNT(1) value
                    FROM rcp_service_reservation r
                    JOIN oper_operation_schedule o ON o.ser = r.ser
                    WHERE {$statusCondition}
                      AND r.expecteddonedate >= TO_DATE(:compare_from, 'YYYY-MM-DD')
                      AND r.expecteddonedate < TO_DATE(:compare_to, 'YYYY-MM-DD') + 1
                    GROUP BY TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
                )
                SELECT
                    m.month_id,
                    m.month_name,
                    NVL(MAX(CASE WHEN a.period_key = 'CURRENT' THEN a.value END), 0) current_value,
                    NVL(MAX(CASE WHEN a.period_key = 'COMPARE' THEN a.value END), 0) compare_value
                FROM months m
                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->normalizeMonthlyRows(Database::fetchAll($this->hospitalCode, $sql, [
                'current_from' => $fromDate,
                'current_to' => $toDate,
                'compare_from' => $compareFrom,
                'compare_to' => $compareTo,
            ]));
        }

        $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_service_reservation r
                JOIN oper_operation_schedule o ON o.ser = r.ser
                CROSS JOIN p
                WHERE {$statusCondition}
                  AND EXTRACT(YEAR FROM r.expecteddonedate) IN (p.current_year, p.compare_year)
                GROUP BY
                    EXTRACT(YEAR FROM r.expecteddonedate),
                    TO_NUMBER(TO_CHAR(r.expecteddonedate, 'MM'))
            )
            SELECT
                m.month_id,
                m.month_name,
                NVL(MAX(CASE WHEN a.year_id = p.current_year THEN a.value END), 0) current_value,
                NVL(MAX(CASE WHEN a.year_id = p.compare_year THEN a.value 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->normalizeMonthlyRows(Database::fetchAll($this->hospitalCode, $sql, [
            'current_year' => $year,
            'compare_year' => $compareYear,
        ]));
    }

    private function statusCondition(string $mode): string
    {
        if ($mode === 'performed') {
            return "r.servstatus = 'D'";
        }
        if ($mode === 'cancelled') {
            return "r.servstatus = 'C'";
        }
        return "r.servstatus <> 'C'";
    }

    /**
     * @return array{0:string,1:array<string,mixed>}
     */
    private function periodWhere(int $year, ?string $fromDate, ?string $toDate, string $column): array
    {
        if ($fromDate !== null && $toDate !== null) {
            return [
                "{$column} >= TO_DATE(:from_date, 'YYYY-MM-DD') AND {$column} < TO_DATE(:to_date, 'YYYY-MM-DD') + 1",
                ['from_date' => $fromDate, 'to_date' => $toDate],
            ];
        }
        return ["EXTRACT(YEAR FROM {$column}) = :year", ['year' => $year]];
    }

    /** @return array<string, string>|null */
    private function firstAvailableRoomExpression(): ?array
    {
        $candidates = [
            ['table' => 'OPER_OPERATION_SCHEDULE', 'alias' => 'o', 'column' => 'OPER_ROOM_ID'],
            ['table' => 'OPER_OPERATION_SCHEDULE', 'alias' => 'o', 'column' => 'OPERATING_ROOM_ID'],
            ['table' => 'OPER_OPERATION_SCHEDULE', 'alias' => 'o', 'column' => 'OR_ROOM_ID'],
            ['table' => 'OPER_OPERATION_SCHEDULE', 'alias' => 'o', 'column' => 'ROOM_ID'],
            ['table' => 'OPER_OPERATION_SCHEDULE', 'alias' => 'o', 'column' => 'ROOMID'],
            ['table' => 'RCP_SERVICE_RESERVATION', 'alias' => 'r', 'column' => 'OPER_ROOM_ID'],
            ['table' => 'RCP_SERVICE_RESERVATION', 'alias' => 'r', 'column' => 'OPERATING_ROOM_ID'],
            ['table' => 'RCP_SERVICE_RESERVATION', 'alias' => 'r', 'column' => 'OR_ROOM_ID'],
            ['table' => 'RCP_SERVICE_RESERVATION', 'alias' => 'r', 'column' => 'ROOM_ID'],
            ['table' => 'RCP_SERVICE_RESERVATION', 'alias' => 'r', 'column' => 'ROOMID'],
        ];

        foreach ($candidates as $candidate) {
            if ($this->columnExists($candidate['table'], $candidate['column'])) {
                return [
                    'expression' => $candidate['alias'] . '.' . strtolower($candidate['column']),
                    'source' => $candidate['table'] . '.' . $candidate['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);
        } catch (Throwable $e) {
            $this->columnCache[$cacheKey] = false;
        }
        return $this->columnCache[$cacheKey];
    }

    /** @param array<int, array<string, mixed>> $rows */
    private function sumRows(array $rows, string $key): float
    {
        $sum = 0.0;
        foreach ($rows as $row) {
            $sum += (float)($row[$key] ?? 0);
        }
        return $sum;
    }

    /** @return array<int, array<string, mixed>> */
    private function normalizeMonthlyRows(array $rows): array
    {
        return array_map(static 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);
    }

    /** @return array<int, array<string, mixed>> */
    private function normalizeRankRows(array $rows, string $labelKey): array
    {
        return array_map(static function (array $row) use ($labelKey): array {
            return [
                'label' => trim((string)($row[$labelKey] ?? '')),
                'value' => (float)($row['value'] ?? 0),
            ];
        }, $rows);
    }
}
