<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use DateTimeImmutable;

/**
 * Current selected-branch implementation for legacy Lab & Radiology monthly activity.
 * Read-only: SELECT statements only.
 */
final class DiagnosticsRepository
{
    /** @var string */
    private $hospitalCode;

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

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

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

    /** @return array<int, int> */
    public function availableYears(int $limit = 20): array
    {
        $sql = "
            SELECT year_id
            FROM (
                SELECT DISTINCT EXTRACT(YEAR FROM expecteddonedate) year_id
                FROM rcp_lab_service_reservation
                WHERE expecteddonedate IS NOT NULL
                  AND expecteddonedate <= SYSDATE

                UNION

                SELECT DISTINCT EXTRACT(YEAR FROM machine_sch_from) year_id
                FROM rcp_rad_service_reservation
                WHERE machine_sch_from IS NOT NULL
                  AND machine_sch_from <= SYSDATE
            )
            WHERE year_id IS NOT NULL
            ORDER BY year_id DESC
        ";
        $rows = Database::fetchAll($this->hospitalCode, $sql);
        $years = [];
        foreach ($rows as $row) {
            $value = (int)($row['year_id'] ?? 0);
            if ($value > 0) {
                $years[] = $value;
            }
        }
        $years = array_values(array_unique($years));
        return array_slice($years, 0, max(1, $limit));
    }

    /** @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 monthlyFromSource(
        string $table,
        string $dateColumn,
        int $year,
        int $compareYear,
        ?string $fromDate,
        ?string $toDate
    ): array {
        $allowed = [
            'rcp_lab_service_reservation' => 'expecteddonedate',
            'rcp_rad_service_reservation' => 'machine_sch_from',
        ];
        if (!isset($allowed[$table]) || $allowed[$table] !== $dateColumn) {
            throw new \InvalidArgumentException('Unsupported diagnostics source contract.');
        }

        $dateExpr = 'd.' . $dateColumn;
        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({$dateExpr}, 'MM')) month_id,
                        COUNT(1) value
                    FROM {$table} d
                    WHERE {$dateExpr} IS NOT NULL
                      AND {$dateExpr} <= SYSDATE
                      AND {$dateExpr} >= TO_DATE(:current_from, 'YYYY-MM-DD')
                      AND {$dateExpr} < TO_DATE(:current_to, 'YYYY-MM-DD') + 1
                    GROUP BY TO_NUMBER(TO_CHAR({$dateExpr}, 'MM'))

                    UNION ALL

                    SELECT
                        'COMPARE' period_key,
                        TO_NUMBER(TO_CHAR({$dateExpr}, 'MM')) month_id,
                        COUNT(1) value
                    FROM {$table} d
                    WHERE {$dateExpr} IS NOT NULL
                      AND {$dateExpr} <= SYSDATE
                      AND {$dateExpr} >= TO_DATE(:compare_from, 'YYYY-MM-DD')
                      AND {$dateExpr} < TO_DATE(:compare_to, 'YYYY-MM-DD') + 1
                    GROUP BY TO_NUMBER(TO_CHAR({$dateExpr}, '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 {$dateExpr}) year_id,
                    TO_NUMBER(TO_CHAR({$dateExpr}, 'MM')) month_id,
                    COUNT(1) value
                FROM {$table} d
                CROSS JOIN p
                WHERE {$dateExpr} IS NOT NULL
                  AND {$dateExpr} <= SYSDATE
                  AND EXTRACT(YEAR FROM {$dateExpr}) IN (p.current_year, p.compare_year)
                GROUP BY
                    EXTRACT(YEAR FROM {$dateExpr}),
                    TO_NUMBER(TO_CHAR({$dateExpr}, '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,
        ]));
    }

    /**
     * @param array<int, array<string, mixed>> $rows
     * @return array<int, array<string, mixed>>
     */
    private function normalizeMonthlyRows(array $rows): array
    {
        $byMonth = [];
        foreach ($rows as $row) {
            $monthId = (int)($row['month_id'] ?? 0);
            if ($monthId < 1 || $monthId > 12) {
                continue;
            }
            $byMonth[$monthId] = [
                'month_id' => $monthId,
                'month_name' => trim((string)($row['month_name'] ?? '')),
                'current_value' => (float)($row['current_value'] ?? 0),
                'compare_value' => (float)($row['compare_value'] ?? 0),
            ];
        }

        $fallback = $this->emptyMonthlyRows();
        foreach ($fallback as &$row) {
            $monthId = (int)$row['month_id'];
            if (isset($byMonth[$monthId])) {
                $row = $byMonth[$monthId];
            }
        }
        unset($row);
        return $fallback;
    }
}
