<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use App\Services\DiarConnectionResolver;
use RuntimeException;
use Throwable;

/**
 * DIAR-6B.2 monthly case search read model.
 *
 * Exact identity: HOSPITAL_CODE + SOURCE_DB + MONTH_SNAPSHOT_ID + MONTH_CASE_ID.
 * Read boundary: DIAR-owned monthly tables only; no MM* access; no DB link; no writes.
 */
final class DiarCasesRepository
{
    private const EXPORT_LIMIT = 5000;

    /** @var DiarConnectionResolver */
    private $resolver;

    public function __construct(?DiarConnectionResolver $resolver = null)
    {
        $this->resolver = $resolver ?: new DiarConnectionResolver();
    }

    /**
     * @param array<int,string> $allowedSites
     * @param array<string,string> $filters
     * @return array<string,mixed>
     */
    public function search(array $allowedSites, array $filters, int $page, int $perPage): array
    {
        $normalized = $this->normalizeFilters($filters);
        $page = max(1, $page);
        $perPage = in_array($perPage, [25, 50, 100], true) ? $perPage : 25;
        $offset = ($page - 1) * $perPage;
        $targetRows = $offset + $perPage;

        $result = $this->collect($allowedSites, $normalized, $targetRows);
        $rows = array_slice((array)$result['rows'], $offset, $perPage);
        $total = (int)$result['total'];
        $totalPages = max(1, (int)ceil($total / $perPage));
        if ($page > $totalPages && $total > 0) {
            $page = $totalPages;
            $offset = ($page - 1) * $perPage;
            $targetRows = $offset + $perPage;
            $result = $this->collect($allowedSites, $normalized, $targetRows);
            $rows = array_slice((array)$result['rows'], $offset, $perPage);
        }

        $from = $total === 0 ? 0 : $offset + 1;
        $to = $total === 0 ? 0 : min($total, $offset + count($rows));

        $result['rows'] = $rows;
        $result['filters'] = $normalized;
        $result['page'] = $page;
        $result['per_page'] = $perPage;
        $result['total_pages'] = max(1, (int)ceil($total / $perPage));
        $result['from_row'] = $from;
        $result['to_row'] = $to;
        return $result;
    }

    /**
     * @param array<int,string> $allowedSites
     * @param array<string,string> $filters
     * @return array<string,mixed>
     */
    public function export(array $allowedSites, array $filters): array
    {
        $normalized = $this->normalizeFilters($filters);
        $result = $this->collect($allowedSites, $normalized, self::EXPORT_LIMIT);
        $result['filters'] = $normalized;
        $result['export_limit'] = self::EXPORT_LIMIT;
        $result['truncated'] = (int)$result['total'] > self::EXPORT_LIMIT;
        return $result;
    }

    /**
     * @param array<int,string> $allowedSites
     * @param array<string,string> $filters
     * @return array<string,mixed>
     */
    private function collect(array $allowedSites, array $filters, int $targetRows): array
    {
        $requestedSites = $this->resolver->requestedSites((string)$filters['site'], $allowedSites);
        $catalogs = [];
        $unavailable = [];
        foreach ($requestedSites as $site) {
            try {
                $catalogs[$site] = $this->siteCatalog($site);
            } catch (Throwable $e) {
                $unavailable[$site] = [
                    'site_code' => $site,
                    'status_code' => 'PENDING_6A',
                    'message_code' => $this->safeMessageCode($e),
                ];
            }
        }

        $selectedMonth = (string)$filters['month'];
        if ($selectedMonth === '') {
            $selectedMonth = $this->selectDefaultMonth($catalogs);
        }

        $rows = [];
        $summary = $this->emptySummary();
        $siteStatus = [];
        $snapshotRefs = [];
        $optionSets = ['branches' => [], 'trans_codes' => [], 'trans_effects' => []];

        foreach ($requestedSites as $site) {
            if (!isset($catalogs[$site])) {
                $siteStatus[$site] = $unavailable[$site];
                continue;
            }
            try {
                $snapshot = $this->snapshotForMonth($site, $catalogs[$site], $selectedMonth);
                if ($snapshot === []) {
                    $siteStatus[$site] = [
                        'site_code' => $site,
                        'source_db' => (string)$catalogs[$site]['source_db'],
                        'status_code' => 'NO_SNAPSHOT',
                        'message_code' => 'NO_SNAPSHOT_FOR_SELECTED_MONTH',
                    ];
                    continue;
                }
                $siteStatus[$site] = [
                    'site_code' => $site,
                    'source_db' => (string)$catalogs[$site]['source_db'],
                    'status_code' => 'AVAILABLE',
                    'snapshot_id' => (int)$snapshot['month_snapshot_id'],
                    'version_no' => (int)$snapshot['version_no'],
                    'month_status' => (string)$snapshot['status_code'],
                ];
                $snapshotRefs[] = [
                    'hospital_code' => $site,
                    'source_db' => (string)$catalogs[$site]['source_db'],
                    'month_snapshot_id' => (int)$snapshot['month_snapshot_id'],
                    'version_no' => (int)$snapshot['version_no'],
                    'snapshot_hash' => (string)$snapshot['snapshot_hash'],
                ];

                $aggregate = $this->siteAggregate((string)$catalogs[$site]['connection'], (int)$snapshot['month_snapshot_id'], $filters);
                $this->addAggregate($summary, $aggregate);
                foreach ($this->siteRows(
                    (string)$catalogs[$site]['connection'],
                    $site,
                    (string)$catalogs[$site]['source_db'],
                    $snapshot,
                    $filters,
                    $targetRows
                ) as $row) {
                    $rows[] = $row;
                }
                $options = $this->siteFilterOptions((string)$catalogs[$site]['connection'], (int)$snapshot['month_snapshot_id']);
                foreach ($options as $key => $values) {
                    foreach ($values as $value) {
                        $optionSets[$key][(string)$value] = $value;
                    }
                }
            } catch (Throwable $e) {
                $siteStatus[$site] = [
                    'site_code' => $site,
                    'source_db' => (string)$catalogs[$site]['source_db'],
                    'status_code' => 'UNAVAILABLE',
                    'message_code' => $this->safeMessageCode($e),
                ];
            }
        }

        usort($rows, $this->comparator((string)$filters['sort']));
        $rows = array_slice($rows, 0, $targetRows);
        foreach ($optionSets as $key => $values) {
            ksort($values, SORT_NATURAL);
            $optionSets[$key] = array_values($values);
        }

        $monthOptions = [];
        foreach ($catalogs as $catalog) {
            foreach ((array)$catalog['month_options'] as $option) {
                $key = (string)($option['month_key'] ?? '');
                if ($key !== '' && !isset($monthOptions[$key])) {
                    $monthOptions[$key] = $option;
                }
            }
        }
        krsort($monthOptions);

        $available = [];
        $notAvailable = [];
        foreach ($siteStatus as $site => $status) {
            if (($status['status_code'] ?? '') === 'AVAILABLE') {
                $available[] = $site;
            } else {
                $notAvailable[] = $site;
            }
        }

        return [
            'rows' => $rows,
            'total' => (int)$summary['total_count'],
            'summary' => $summary,
            'requested_sites' => $requestedSites,
            'available_sites' => $available,
            'unavailable_sites' => $notAvailable,
            'site_status' => $siteStatus,
            'partial_coverage' => count($available) !== count($requestedSites),
            'selected_month' => $selectedMonth,
            'month_options' => array_values($monthOptions),
            'filter_options' => $optionSets,
            'snapshot_refs' => $snapshotRefs,
            'contract' => [
                'read_only' => true,
                'database_link' => false,
                'source_tables' => ['DASH_DIAR_MONTH_CURRENT', 'DASH_DIAR_MONTH_SNAPSHOTS', 'DASH_DIAR_MONTH_CASES'],
                'excluded_stores' => [56, 57],
                'identity' => 'HOSPITAL_CODE|SOURCE_DB|MONTH_SNAPSHOT_ID|MONTH_CASE_ID',
                'grain' => 'HOSPITAL|SOURCE_DB|BRANCH|STORE|ITEM|RAW_UNIT|STORETRANSCODE|STORETRANSEFFECT',
                'export_limit' => self::EXPORT_LIMIT,
            ],
        ];
    }

    /** @return array<string,mixed> */
    private function siteCatalog(string $site): array
    {
        $connection = $this->resolver->connectionFor($site);
        $required = Database::fetchOne($connection, "SELECT COUNT(*) object_count FROM USER_TABLES WHERE TABLE_NAME IN (
            'DASH_DIAR_MONTH_SNAPSHOTS','DASH_DIAR_MONTH_CURRENT','DASH_DIAR_MONTH_CASES'
        )");
        if ((int)($required['object_count'] ?? 0) !== 3) {
            throw new RuntimeException('DIAR6A_OBJECTS_PENDING');
        }
        $identity = Database::fetchOne($connection, "SELECT
            PKG_DASH_DIAR_ENGINE.FN_HOSPITAL_CODE hospital_code,
            PKG_DASH_DIAR_ENGINE.FN_SOURCE_DB source_db
        FROM dual");
        if (strtoupper((string)($identity['hospital_code'] ?? '')) !== $site) {
            throw new RuntimeException('DIAR_SITE_IDENTITY_MISMATCH');
        }
        $sourceDb = (string)($identity['source_db'] ?? '');
        $months = Database::fetchAll($connection, "SELECT * FROM (
            SELECT TO_CHAR(s.MONTH_START,'YYYY-MM') month_key,
                   s.MONTH_SNAPSHOT_ID,s.VERSION_NO,s.VERSION_TYPE,s.STATUS_CODE,
                   TO_CHAR(s.DATA_UPDATED_THROUGH,'YYYY-MM-DD') data_updated_through,
                   s.MISSING_DAYS,TO_CHAR(NVL(s.CLOSED_AT,s.CREATED_AT),'YYYY-MM-DD HH24:MI:SS') updated_at
            FROM DASH_DIAR_MONTH_CURRENT c
            JOIN DASH_DIAR_MONTH_SNAPSHOTS s ON s.MONTH_SNAPSHOT_ID=c.MONTH_SNAPSHOT_ID
            WHERE c.HOSPITAL_CODE=:hospital_code AND c.SOURCE_DB=:source_db
            ORDER BY s.MONTH_START DESC,s.VERSION_NO DESC
        ) WHERE ROWNUM<=24", ['hospital_code' => $site, 'source_db' => $sourceDb]);
        return [
            'site_code' => $site,
            'connection' => $connection,
            'source_db' => $sourceDb,
            'month_options' => $months,
        ];
    }

    /** @param array<string,mixed> $catalog @return array<string,mixed> */
    private function snapshotForMonth(string $site, array $catalog, string $month): array
    {
        if ($month === '') {
            return [];
        }
        $row = Database::fetchOne((string)$catalog['connection'], "SELECT
            s.MONTH_SNAPSHOT_ID,s.VERSION_NO,s.VERSION_TYPE,s.STATUS_CODE,s.SNAPSHOT_HASH,
            TO_CHAR(s.MONTH_START,'YYYY-MM') month_key,
            TO_CHAR(s.DATA_UPDATED_THROUGH,'YYYY-MM-DD') data_updated_through,
            s.MISSING_DAYS
        FROM DASH_DIAR_MONTH_CURRENT c
        JOIN DASH_DIAR_MONTH_SNAPSHOTS s ON s.MONTH_SNAPSHOT_ID=c.MONTH_SNAPSHOT_ID
        WHERE c.HOSPITAL_CODE=:hospital_code AND c.SOURCE_DB=:source_db
          AND c.MONTH_START=TO_DATE(:month_start,'YYYY-MM-DD')", [
            'hospital_code' => $site,
            'source_db' => (string)$catalog['source_db'],
            'month_start' => $month . '-01',
        ]);
        return is_array($row) ? $row : [];
    }

    /** @param array<string,string> $filters @return array<string,mixed> */
    private function siteAggregate(string $connection, int $snapshotId, array $filters): array
    {
        $params = [];
        $where = $this->whereSql($snapshotId, $filters, $params);
        $row = Database::fetchOne($connection, "SELECT
            COUNT(*) total_count,
            SUM(CASE WHEN c.SEVERITY_CODE='CRITICAL' THEN 1 ELSE 0 END) critical_count,
            SUM(CASE WHEN c.SEVERITY_CODE='HIGH' THEN 1 ELSE 0 END) high_count,
            SUM(CASE WHEN c.SEVERITY_CODE='MEDIUM' THEN 1 ELSE 0 END) medium_count,
            NVL(SUM(c.INVENTORY_COST_ABS),0) inventory_cost_abs,
            NVL(SUM(c.TRANSACTION_COUNT),0) transaction_count,
            NVL(SUM(c.MOVEMENT_LINES),0) movement_lines,
            COUNT(DISTINCT c.STORE_CODE) store_count,
            COUNT(DISTINCT c.ITEM_CODE) item_count
        FROM DASH_DIAR_MONTH_CASES c WHERE {$where}", $params);
        return is_array($row) ? $row : $this->emptySummary();
    }

    /**
     * @param array<string,mixed> $snapshot
     * @param array<string,string> $filters
     * @return array<int,array<string,mixed>>
     */
    private function siteRows(string $connection, string $site, string $sourceDb, array $snapshot, array $filters, int $targetRows): array
    {
        $params = [];
        $where = $this->whereSql((int)$snapshot['month_snapshot_id'], $filters, $params);
        $order = $this->orderSql((string)$filters['sort']);
        $params['row_to'] = max(1, $targetRows);
        $sql = "SELECT * FROM (
            SELECT c.MONTH_CASE_ID,c.MONTH_SNAPSHOT_ID,c.CANDIDATE_ID,c.CANDIDATE_HASH,
                   TO_CHAR(c.CALENDAR_DAY,'YYYY-MM-DD') calendar_day,
                   c.BRANCH_ID,c.STORE_CODE,c.ITEM_CODE,c.RAW_UNIT_CODE,
                   c.STORETRANSCODE,c.STORETRANSEFFECT,c.SEVERITY_CODE,c.ANOMALY_SCORE,
                   c.INVENTORY_COST_ABS,c.TRANSACTION_COUNT,c.MOVEMENT_LINES,c.STAT_RUN_ID,c.ANALYSIS_RUN_ID,
                   c.PROFILE_METHOD_VERSION,c.DETECTION_METHOD_VERSION,c.RULESET_VERSION_ID,c.MOVE_MAP_VERSION_ID,
                   ROW_NUMBER() OVER (ORDER BY {$order}) row_no
            FROM DASH_DIAR_MONTH_CASES c
            WHERE {$where}
        ) WHERE row_no<=:row_to ORDER BY row_no";
        $rows = Database::fetchAll($connection, $sql, $params);
        foreach ($rows as &$row) {
            $row['hospital_code'] = $site;
            $row['source_db'] = $sourceDb;
            $row['month_key'] = (string)$snapshot['month_key'];
            $row['snapshot_version'] = (int)$snapshot['version_no'];
            $row['snapshot_status'] = (string)$snapshot['status_code'];
            $row['identity_key'] = $site . '|' . $sourceDb . '|' . (string)$row['month_snapshot_id'] . '|' . (string)$row['month_case_id'];
            $row['severity_rank'] = $this->severityRank((string)$row['severity_code']);
        }
        unset($row);
        return $rows;
    }

    /** @return array<string,array<int,int>> */
    private function siteFilterOptions(string $connection, int $snapshotId): array
    {
        $rows = Database::fetchAll($connection, "SELECT DISTINCT BRANCH_ID,STORETRANSCODE,STORETRANSEFFECT
            FROM DASH_DIAR_MONTH_CASES
            WHERE MONTH_SNAPSHOT_ID=:snapshot_id AND STORE_CODE NOT IN (56,57)
            ORDER BY BRANCH_ID,STORETRANSCODE,STORETRANSEFFECT", ['snapshot_id' => $snapshotId]);
        $out = ['branches' => [], 'trans_codes' => [], 'trans_effects' => []];
        foreach ($rows as $row) {
            $out['branches'][] = (int)$row['branch_id'];
            $out['trans_codes'][] = (int)$row['storetranscode'];
            $out['trans_effects'][] = (int)$row['storetranseffect'];
        }
        foreach ($out as $key => $values) {
            $out[$key] = array_values(array_unique($values, SORT_REGULAR));
        }
        return $out;
    }

    /** @param array<string,string> $filters @param array<string,mixed> $params */
    private function whereSql(int $snapshotId, array $filters, array &$params): string
    {
        $parts = ['c.MONTH_SNAPSHOT_ID=:snapshot_id', 'c.STORE_CODE NOT IN (56,57)'];
        $params['snapshot_id'] = $snapshotId;
        $simple = [
            'severity' => ['c.SEVERITY_CODE', 'text'],
            'branch' => ['c.BRANCH_ID', 'number'],
            'store' => ['c.STORE_CODE', 'number'],
            'item' => ['c.ITEM_CODE', 'number'],
            'unit' => ['c.RAW_UNIT_CODE', 'number'],
            'trans_code' => ['c.STORETRANSCODE', 'number'],
            'trans_effect' => ['c.STORETRANSEFFECT', 'number'],
        ];
        foreach ($simple as $key => $definition) {
            if ((string)$filters[$key] === '') {
                continue;
            }
            $parts[] = $definition[0] . '=:' . $key;
            $params[$key] = $definition[1] === 'number' ? (int)$filters[$key] : (string)$filters[$key];
        }
        if ((string)$filters['date_from'] !== '') {
            $parts[] = "c.CALENDAR_DAY>=TO_DATE(:date_from,'YYYY-MM-DD')";
            $params['date_from'] = $filters['date_from'];
        }
        if ((string)$filters['date_to'] !== '') {
            $parts[] = "c.CALENDAR_DAY<TO_DATE(:date_to,'YYYY-MM-DD')+1";
            $params['date_to'] = $filters['date_to'];
        }
        if ((string)$filters['min_score'] !== '') {
            $parts[] = 'c.ANOMALY_SCORE>=:min_score';
            $params['min_score'] = (float)$filters['min_score'];
        }
        if ((string)$filters['q'] !== '') {
            $parts[] = "(TO_CHAR(c.MONTH_CASE_ID) LIKE :q OR TO_CHAR(c.CANDIDATE_ID) LIKE :q
                OR TO_CHAR(c.STORE_CODE) LIKE :q OR TO_CHAR(c.ITEM_CODE) LIKE :q
                OR UPPER(c.CANDIDATE_HASH) LIKE :q)";
            $escaped = str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], strtoupper((string)$filters['q']));
            $params['q'] = '%' . $escaped . '%';
        }
        return implode(' AND ', $parts);
    }

    private function orderSql(string $sort): string
    {
        $severity = "CASE c.SEVERITY_CODE WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 ELSE 3 END";
        $map = [
            'priority' => $severity . ',c.ANOMALY_SCORE DESC,c.CALENDAR_DAY DESC,c.MONTH_CASE_ID DESC',
            'newest' => 'c.CALENDAR_DAY DESC,' . $severity . ',c.ANOMALY_SCORE DESC,c.MONTH_CASE_ID DESC',
            'score' => 'c.ANOMALY_SCORE DESC,' . $severity . ',c.CALENDAR_DAY DESC,c.MONTH_CASE_ID DESC',
            'value' => 'c.INVENTORY_COST_ABS DESC,' . $severity . ',c.ANOMALY_SCORE DESC,c.MONTH_CASE_ID DESC',
            'transactions' => 'c.TRANSACTION_COUNT DESC,' . $severity . ',c.ANOMALY_SCORE DESC,c.MONTH_CASE_ID DESC',
        ];
        return $map[$sort] ?? $map['priority'];
    }

    /** @return callable(array<string,mixed>,array<string,mixed>):int */
    private function comparator(string $sort): callable
    {
        return function (array $a, array $b) use ($sort): int {
            $fields = [];
            if ($sort === 'newest') {
                $fields = [['calendar_day', 'desc', 'string'], ['severity_rank', 'asc', 'number'], ['anomaly_score', 'desc', 'number'], ['month_case_id', 'desc', 'number']];
            } elseif ($sort === 'score') {
                $fields = [['anomaly_score', 'desc', 'number'], ['severity_rank', 'asc', 'number'], ['calendar_day', 'desc', 'string'], ['month_case_id', 'desc', 'number']];
            } elseif ($sort === 'value') {
                $fields = [['inventory_cost_abs', 'desc', 'number'], ['severity_rank', 'asc', 'number'], ['anomaly_score', 'desc', 'number'], ['month_case_id', 'desc', 'number']];
            } elseif ($sort === 'transactions') {
                $fields = [['transaction_count', 'desc', 'number'], ['severity_rank', 'asc', 'number'], ['anomaly_score', 'desc', 'number'], ['month_case_id', 'desc', 'number']];
            } else {
                $fields = [['severity_rank', 'asc', 'number'], ['anomaly_score', 'desc', 'number'], ['calendar_day', 'desc', 'string'], ['month_case_id', 'desc', 'number']];
            }
            foreach ($fields as $field) {
                $av = $field[2] === 'number' ? (float)($a[$field[0]] ?? 0) : (string)($a[$field[0]] ?? '');
                $bv = $field[2] === 'number' ? (float)($b[$field[0]] ?? 0) : (string)($b[$field[0]] ?? '');
                $cmp = $av <=> $bv;
                if ($cmp !== 0) {
                    return $field[1] === 'desc' ? -$cmp : $cmp;
                }
            }
            return strcmp((string)($a['identity_key'] ?? ''), (string)($b['identity_key'] ?? ''));
        };
    }

    /** @param array<string,mixed> $summary @param array<string,mixed> $row */
    private function addAggregate(array &$summary, array $row): void
    {
        foreach (['total_count','critical_count','high_count','medium_count','transaction_count','movement_lines','store_count','item_count'] as $key) {
            $summary[$key] += (int)($row[$key] ?? 0);
        }
        $summary['inventory_cost_abs'] += (float)($row['inventory_cost_abs'] ?? 0);
    }

    /** @return array<string,mixed> */
    private function emptySummary(): array
    {
        return [
            'total_count' => 0,
            'critical_count' => 0,
            'high_count' => 0,
            'medium_count' => 0,
            'inventory_cost_abs' => 0.0,
            'transaction_count' => 0,
            'movement_lines' => 0,
            'store_count' => 0,
            'item_count' => 0,
        ];
    }

    /** @param array<string,array<string,mixed>> $catalogs */
    private function selectDefaultMonth(array $catalogs): string
    {
        $sets = [];
        foreach ($catalogs as $catalog) {
            $closed = [];
            $fallback = [];
            foreach ((array)$catalog['month_options'] as $row) {
                $month = $this->normalizeMonth((string)($row['month_key'] ?? ''));
                if ($month === '') {
                    continue;
                }
                $fallback[] = $month;
                if (strtoupper((string)($row['status_code'] ?? '')) === 'CLOSED') {
                    $closed[] = $month;
                }
            }
            $months = $closed !== [] ? $closed : $fallback;
            rsort($months);
            if ($months !== []) {
                $sets[] = array_values(array_unique($months));
            }
        }
        if ($sets === []) {
            return '';
        }
        $common = $sets[0];
        foreach (array_slice($sets, 1) as $months) {
            $common = array_values(array_intersect($common, $months));
        }
        if ($common !== []) {
            rsort($common);
            return $common[0];
        }
        $all = [];
        foreach ($sets as $months) {
            $all = array_merge($all, $months);
        }
        rsort($all);
        return (string)($all[0] ?? '');
    }

    /** @param array<string,string> $filters @return array<string,string> */
    private function normalizeFilters(array $filters): array
    {
        $site = strtoupper(trim((string)($filters['site'] ?? 'ALL')));
        $month = $this->normalizeMonth((string)($filters['month'] ?? ''));
        $severity = strtoupper(trim((string)($filters['severity'] ?? '')));
        if (!in_array($severity, ['', 'CRITICAL', 'HIGH', 'MEDIUM'], true)) {
            $severity = '';
        }
        $sort = strtolower(trim((string)($filters['sort'] ?? 'priority')));
        if (!in_array($sort, ['priority', 'newest', 'score', 'value', 'transactions'], true)) {
            $sort = 'priority';
        }
        $out = [
            'site' => $site === '' ? 'ALL' : $site,
            'month' => $month,
            'q' => substr(trim((string)($filters['q'] ?? '')), 0, 64),
            'severity' => $severity,
            'branch' => $this->normalizeInteger((string)($filters['branch'] ?? '')),
            'store' => $this->normalizeInteger((string)($filters['store'] ?? '')),
            'item' => $this->normalizeInteger((string)($filters['item'] ?? '')),
            'unit' => $this->normalizeInteger((string)($filters['unit'] ?? '')),
            'trans_code' => $this->normalizeInteger((string)($filters['trans_code'] ?? '')),
            'trans_effect' => $this->normalizeInteger((string)($filters['trans_effect'] ?? '')),
            'date_from' => $this->normalizeDate((string)($filters['date_from'] ?? '')),
            'date_to' => $this->normalizeDate((string)($filters['date_to'] ?? '')),
            'min_score' => $this->normalizeDecimal((string)($filters['min_score'] ?? '')),
            'sort' => $sort,
        ];
        if ($out['date_from'] !== '' && $out['date_to'] !== '' && $out['date_from'] > $out['date_to']) {
            $tmp = $out['date_from'];
            $out['date_from'] = $out['date_to'];
            $out['date_to'] = $tmp;
        }
        return $out;
    }

    private function normalizeMonth(string $value): string
    {
        $value = trim($value);
        return preg_match('/^20\\d{2}-(0[1-9]|1[0-2])$/', $value) ? $value : '';
    }

    private function normalizeDate(string $value): string
    {
        $value = trim($value);
        return preg_match('/^20\\d{2}-(0[1-9]|1[0-2])-([0-2]\\d|3[01])$/', $value) ? $value : '';
    }

    private function normalizeInteger(string $value): string
    {
        $value = trim($value);
        return preg_match('/^-?\\d{1,12}$/', $value) ? (string)(int)$value : '';
    }

    private function normalizeDecimal(string $value): string
    {
        $value = trim($value);
        if (!preg_match('/^\\d{1,6}(\\.\\d{1,4})?$/', $value)) {
            return '';
        }
        return (string)(float)$value;
    }

    private function severityRank(string $severity): int
    {
        return $severity === 'CRITICAL' ? 1 : ($severity === 'HIGH' ? 2 : 3);
    }

    private function safeMessageCode(Throwable $e): string
    {
        $message = strtoupper($e->getMessage());
        if (strpos($message, 'DIAR6A_OBJECTS_PENDING') !== false || strpos($message, 'ORA-00942') !== false) {
            return 'DIAR6A_PENDING';
        }
        if (strpos($message, 'IDENTITY') !== false) {
            return 'IDENTITY_MISMATCH';
        }
        return 'SOURCE_UNAVAILABLE';
    }
}
