<?php

declare(strict_types=1);

namespace App\Repositories;

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

final class DiarCommandCenterRepository
{
    /** @var array<string,string> */
    private const SITE_CONNECTIONS = [
        'NJCH' => 'njch',
        'MNH' => 'mnh',
    ];

    /**
     * Returns a light, read-only operational overview from DIAR-owned tables.
     * No MM* table is queried by this module.
     *
     * @param array<int,string> $allowedSites
     * @return array<string,mixed>
     */
    public function overview(array $allowedSites): array
    {
        $sites = [];
        foreach ($this->normalizeSites($allowedSites) as $site) {
            try {
                $sites[$site] = $this->siteOverview($site);
            } catch (Throwable $e) {
                $sites[$site] = [
                    'site_code' => $site,
                    'status' => 'UNAVAILABLE',
                    'error' => $e->getMessage(),
                    'readiness_code' => 'UNAVAILABLE',
                    'coverage_percent' => 0,
                    'fact_rows' => 0,
                    'movement_type_count' => 0,
                    'profile_count' => 0,
                    'engine_case_count' => 0,
                    'recent_runs' => [],
                ];
            }
        }

        $summary = [
            'allowed_hospitals' => count($sites),
            'connected_hospitals' => 0,
            'ready_hospitals' => 0,
            'in_progress_hospitals' => 0,
            'attention_hospitals' => 0,
            'fact_rows' => 0,
            'movement_type_count' => 0,
            'movement_unique_count' => 0,
            'movement_entry_count' => 0,
            'movement_dictionary_aligned' => true,
            'profile_count' => 0,
            'profile_ready_count' => 0,
            'profile_limited_count' => 0,
            'profile_sparse_count' => 0,
            'profile_no_activity_count' => 0,
            'profile_live_hospitals' => 0,
            'engine_case_count' => 0,
            'running_runs' => 0,
            'failed_runs' => 0,
            'passed_runs' => 0,
            'total_runs' => 0,
            'data_incomplete' => false,
        ];

        $movementCounts = [];
        $movementHashes = [];
        foreach ($sites as $siteData) {
            if (($siteData['status'] ?? '') === 'AVAILABLE') {
                $summary['connected_hospitals']++;
            }
            $readiness = (string)($siteData['readiness_code'] ?? 'UNAVAILABLE');
            if ($readiness === 'READY') {
                $summary['ready_hospitals']++;
            } elseif ($readiness === 'IN_PROGRESS' || $readiness === 'PARTIAL' || $readiness === 'NOT_STARTED') {
                $summary['in_progress_hospitals']++;
                $summary['data_incomplete'] = true;
            } elseif ($readiness === 'ATTENTION' || $readiness === 'UNAVAILABLE') {
                $summary['attention_hospitals']++;
                $summary['data_incomplete'] = true;
            }
            $summary['fact_rows'] += (int)($siteData['fact_rows'] ?? 0);
            $siteMovementCount = (int)($siteData['movement_type_count'] ?? 0);
            $summary['movement_type_count'] += $siteMovementCount;
            $summary['movement_entry_count'] += $siteMovementCount;
            if ($siteMovementCount > 0) {
                $movementCounts[] = $siteMovementCount;
            }
            $siteMovementMap = is_array($siteData['movement_map'] ?? null) ? $siteData['movement_map'] : [];
            $siteMapHash = trim((string)($siteMovementMap['map_hash'] ?? ''));
            if ($siteMapHash !== '') {
                $movementHashes[] = $siteMapHash;
            }
            $summary['profile_count'] += (int)($siteData['profile_count'] ?? 0);
            $summary['profile_ready_count'] += (int)($siteData['profile_ready_count'] ?? 0);
            $summary['profile_limited_count'] += (int)($siteData['profile_limited_count'] ?? 0);
            $summary['profile_sparse_count'] += (int)($siteData['profile_sparse_count'] ?? 0);
            $summary['profile_no_activity_count'] += (int)($siteData['profile_no_activity_count'] ?? 0);
            if (in_array((string)(($siteData['profile_stage']['status'] ?? '')), ['LIVE', 'EMPTY_READY'], true)) {
                $summary['profile_live_hospitals']++;
            }
            $summary['engine_case_count'] += (int)($siteData['engine_case_count'] ?? 0);
            $summary['running_runs'] += (int)($siteData['running_runs'] ?? 0);
            $summary['failed_runs'] += (int)($siteData['failed_runs'] ?? 0);
            $siteRunSummary = is_array($siteData['run_summary'] ?? null) ? $siteData['run_summary'] : [];
            $summary['passed_runs'] += (int)($siteRunSummary['passed_runs'] ?? 0);
            $summary['total_runs'] += (int)($siteRunSummary['total_runs'] ?? 0);
        }
        $summary['movement_unique_count'] = $movementCounts === [] ? 0 : max($movementCounts);
        $summary['movement_dictionary_aligned'] = $movementHashes === [] || count(array_unique($movementHashes)) === 1;

        return [
            'summary' => $summary,
            'sites' => $sites,
            'pipeline' => [
                ['code' => 'FOUNDATION', 'status' => 'PASS'],
                ['code' => 'DAILY_FACT', 'status' => $summary['data_incomplete'] ? 'IN_PROGRESS' : 'PASS'],
                ['code' => 'STATISTICAL_PROFILES', 'status' => $summary['profile_count'] > 0 ? 'AVAILABLE' : ($summary['profile_live_hospitals'] > 0 ? 'IN_PROGRESS' : 'PENDING')],
                ['code' => 'ANOMALY_DETECTION', 'status' => 'PREPARED'],
                ['code' => 'CASE_EVIDENCE', 'status' => 'PREPARED'],
                ['code' => 'NIGHTLY_AUTOMATION', 'status' => 'PENDING'],
            ],
        ];
    }

    /**
     * @param array<string,string> $filters
     * @param array<int,string> $allowedSites
     * @return array{rows:array<int,array<string,mixed>>,total:int,page:int,per_page:int}
     */
    public function runs(array $filters, array $allowedSites, int $page, int $perPage): array
    {
        $sites = $this->normalizeSites($allowedSites);
        $requestedSite = strtoupper(trim((string)($filters['site'] ?? '')));
        if ($requestedSite !== '') {
            if (!in_array($requestedSite, $sites, true)) {
                throw new RuntimeException('SITE_NOT_ALLOWED');
            }
            return $this->runsForSite($requestedSite, $filters, $page, $perPage);
        }

        $allRows = [];
        $total = 0;
        $fetchLimit = min(250, max(100, $page * $perPage + $perPage));
        foreach ($sites as $site) {
            $siteResult = $this->runsForSite($site, $filters, 1, $fetchLimit);
            $total += (int)$siteResult['total'];
            foreach ($siteResult['rows'] as $row) {
                $allRows[] = $row;
            }
        }

        usort($allRows, static function (array $a, array $b): int {
            $aTime = strtotime((string)($a['created_at_iso'] ?? $a['started_at_iso'] ?? '1970-01-01')) ?: 0;
            $bTime = strtotime((string)($b['created_at_iso'] ?? $b['started_at_iso'] ?? '1970-01-01')) ?: 0;
            if ($aTime === $bTime) {
                return (int)($b['stat_run_id'] ?? 0) <=> (int)($a['stat_run_id'] ?? 0);
            }
            return $bTime <=> $aTime;
        });

        $offset = max(0, ($page - 1) * $perPage);
        return [
            'rows' => array_slice($allRows, $offset, $perPage),
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
        ];
    }

    /**
     * @param array<string,string> $filters
     * @param array<int,string> $allowedSites
     * @return array<string,mixed>
     */
    public function movementDictionary(array $filters, array $allowedSites, int $page, int $perPage): array
    {
        $site = $this->resolveRequiredSite((string)($filters['site'] ?? ''), $allowedSites);
        $connection = $this->connection($site);
        $where = ['m.IS_CURRENT=1'];
        $params = [];

        $q = trim((string)($filters['q'] ?? ''));
        if ($q !== '') {
            $where[] = "(TO_CHAR(m.STORETRANSCODE)=:q_code OR UPPER(NVL(m.TRANS_TYPE_EN,' ')) LIKE :q_text OR UPPER(NVL(m.TRANS_TYPE_AR,' ')) LIKE :q_text)";
            $params['q_code'] = $q;
            $params['q_text'] = '%' . strtoupper($q) . '%';
        }
        $effect = trim((string)($filters['effect'] ?? ''));
        if ($effect !== '' && in_array($effect, ['0', '1', '2'], true)) {
            $where[] = 'm.STORETRANSEFFECT=:effect';
            $params['effect'] = (int)$effect;
        }
        $status = strtoupper(trim((string)($filters['mapping_status'] ?? '')));
        if ($status !== '' && in_array($status, ['SOURCE_ONLY', 'CLASSIFIED', 'RETIRED'], true)) {
            $where[] = 'm.MAPPING_STATUS=:mapping_status';
            $params['mapping_status'] = $status;
        }

        $whereSql = implode(' AND ', $where);
        $countRow = Database::fetchOne($connection, "SELECT COUNT(*) total_rows FROM DASH_DIAR_MOVE_TYPE_MAP m WHERE {$whereSql}", $params);
        $total = (int)($countRow['total_rows'] ?? 0);
        $start = (($page - 1) * $perPage) + 1;
        $end = min($total, $start + $perPage - 1);
        $rows = [];
        if ($total > 0) {
            $queryParams = $params;
            $queryParams['row_start'] = $start;
            $queryParams['row_end'] = $end;
            $rows = Database::fetchAll($connection, "SELECT * FROM (
                SELECT q.*, ROW_NUMBER() OVER (ORDER BY q.STORETRANSCODE) rn
                FROM (
                    SELECT m.MOVE_MAP_VERSION_ID,m.STORETRANSCODE,m.STORETRANSEFFECT,
                        m.TRANS_TYPE_AR,m.TRANS_TYPE_EN,m.MOVEMENT_GROUP_CODE,m.DIRECTION_CODE,
                        m.MAPPING_STATUS,m.SOURCE_ROW_HASH,
                        TO_CHAR(m.VALID_FROM_DATE,'YYYY-MM-DD') valid_from_date,
                        TO_CHAR(m.VALID_TO_DATE,'YYYY-MM-DD') valid_to_date,
                        TO_CHAR(m.UPDATED_AT,'YYYY-MM-DD HH24:MI:SS') updated_at
                    FROM DASH_DIAR_MOVE_TYPE_MAP m
                    WHERE {$whereSql}
                ) q
            ) WHERE rn BETWEEN :row_start AND :row_end", $queryParams);
        }

        $version = Database::fetchOne($connection, "SELECT * FROM (
            SELECT MOVE_MAP_VERSION_ID,HOSPITAL_CODE,SOURCE_DB,MAP_HASH,SOURCE_TYPE_COUNT,STATUS_CODE,
                TO_CHAR(CAPTURED_AT,'YYYY-MM-DD HH24:MI:SS') captured_at,
                TO_CHAR(ACTIVATED_AT,'YYYY-MM-DD HH24:MI:SS') activated_at
            FROM DASH_DIAR_MOVE_MAP_VERS
            WHERE STATUS_CODE='ACTIVE'
            ORDER BY ACTIVATED_AT DESC,MOVE_MAP_VERSION_ID DESC
        ) WHERE ROWNUM=1");

        return [
            'site' => $site,
            'version' => is_array($version) ? $version : [],
            'rows' => $rows,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'filters' => $filters,
        ];
    }

    /**
     * @param array<string,string> $filters
     * @param array<int,string> $allowedSites
     * @return array<string,mixed>
     */
    public function dailyFacts(array $filters, array $allowedSites, int $page, int $perPage): array
    {
        $site = $this->resolveRequiredSite((string)($filters['site'] ?? ''), $allowedSites);
        $connection = $this->connection($site);
        $where = [
            "f.CALENDAR_DAY>=TO_DATE(:date_from,'YYYY-MM-DD')",
            "f.CALENDAR_DAY<TO_DATE(:date_to,'YYYY-MM-DD')+1",
        ];
        $params = [
            'date_from' => (string)$filters['date_from'],
            'date_to' => (string)$filters['date_to'],
        ];

        $numericFilters = [
            'branch' => ['column' => 'f.BRANCH_ID', 'bind' => 'branch_id'],
            'store' => ['column' => 'f.STORE_CODE', 'bind' => 'store_code'],
            'item' => ['column' => 'f.ITEM_CODE', 'bind' => 'item_code'],
            'unit' => ['column' => 'f.RAW_UNIT_CODE', 'bind' => 'unit_code'],
            'transcode' => ['column' => 'f.STORETRANSCODE', 'bind' => 'transcode'],
            'effect' => ['column' => 'f.STORETRANSEFFECT', 'bind' => 'effect'],
        ];
        foreach ($numericFilters as $filterKey => $definition) {
            $value = trim((string)($filters[$filterKey] ?? ''));
            if ($value !== '' && preg_match('/^-?\d+$/', $value)) {
                $where[] = $definition['column'] . '=:' . $definition['bind'];
                $params[$definition['bind']] = (int)$value;
            }
        }

        $whereSql = implode(' AND ', $where);
        $countRow = Database::fetchOne($connection, "SELECT COUNT(*) total_rows FROM DASH_DIAR_DAILY_FACT f WHERE {$whereSql}", $params);
        $total = (int)($countRow['total_rows'] ?? 0);
        $summary = Database::fetchOne($connection, "SELECT
            COUNT(*) fact_rows,
            NVL(SUM(f.MOVEMENT_LINES),0) movement_lines,
            NVL(SUM(f.TRANSACTION_COUNT),0) transaction_count,
            NVL(SUM(f.RAW_QTY_SUM),0) raw_qty_sum,
            NVL(SUM(f.ABS_RAW_QTY_SUM),0) abs_raw_qty_sum,
            MAX(TO_CHAR(f.UPDATED_AT,'YYYY-MM-DD HH24:MI:SS')) last_updated_at
        FROM DASH_DIAR_DAILY_FACT f WHERE {$whereSql}", $params);

        $start = (($page - 1) * $perPage) + 1;
        $end = min($total, $start + $perPage - 1);
        $rows = [];
        if ($total > 0) {
            $queryParams = $params;
            $queryParams['row_start'] = $start;
            $queryParams['row_end'] = $end;
            $rows = Database::fetchAll($connection, "SELECT * FROM (
                SELECT q.*, ROW_NUMBER() OVER (
                    ORDER BY q.CALENDAR_DAY DESC,q.BRANCH_ID,q.STORE_CODE,q.ITEM_CODE,q.RAW_UNIT_CODE,q.STORETRANSCODE
                ) rn
                FROM (
                    SELECT f.DAILY_FACT_ID,f.HOSPITAL_CODE,f.SOURCE_DB,f.BRANCH_ID,f.STORE_CODE,
                        f.ITEM_CODE,f.RAW_UNIT_CODE,f.STORETRANSCODE,f.STORETRANSEFFECT,
                        TO_CHAR(f.CALENDAR_DAY,'YYYY-MM-DD') calendar_day,
                        TO_CHAR(f.FIRST_TRANS_AT,'YYYY-MM-DD HH24:MI:SS') first_trans_at,
                        TO_CHAR(f.LAST_TRANS_AT,'YYYY-MM-DD HH24:MI:SS') last_trans_at,
                        f.MOVEMENT_LINES,f.TRANSACTION_COUNT,f.DISTINCT_USERS,
                        f.RAW_QTY_SUM,f.ABS_RAW_QTY_SUM,f.POSITIVE_RAW_QTY_SUM,f.NEGATIVE_RAW_QTY_ABS,
                        f.RAW_VALUE_TOTAL,f.AVG_RAW_LINE_QTY,f.MAX_ABS_LINE_QTY,
                        f.INVENTORY_EVENT_COUNT,f.AFTER_HOURS_LINE_COUNT,
                        f.SOURCE_LINE_HASH,
                        TO_CHAR(f.UPDATED_AT,'YYYY-MM-DD HH24:MI:SS') updated_at,
                        m.TRANS_TYPE_AR,m.TRANS_TYPE_EN,m.DIRECTION_CODE,m.MAPPING_STATUS
                    FROM DASH_DIAR_DAILY_FACT f
                    LEFT JOIN DASH_DIAR_MOVE_TYPE_MAP m
                      ON m.STORETRANSCODE=f.STORETRANSCODE AND m.IS_CURRENT=1
                    WHERE {$whereSql}
                ) q
            ) WHERE rn BETWEEN :row_start AND :row_end", $queryParams);
        }

        $options = $this->dailyFactOptions($connection, (string)$filters['date_from'], (string)$filters['date_to']);

        return [
            'site' => $site,
            'rows' => $rows,
            'summary' => is_array($summary) ? $summary : [],
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'filters' => $filters,
            'options' => $options,
        ];
    }


    /**
     * Read-only current statistical profile workspace.
     * It never queries MM* tables and remains safe while DIAR-5D is being applied.
     *
     * @param array<string,string> $filters
     * @param array<int,string> $allowedSites
     * @return array<string,mixed>
     */
    public function statisticalProfiles(array $filters, array $allowedSites, int $page, int $perPage): array
    {
        $site = $this->resolveRequiredSite((string)($filters['site'] ?? ''), $allowedSites);
        $connection = $this->connection($site);
        $stage = $this->profileStageSummary($connection);
        $empty = [
            'site' => $site,
            'availability' => $stage,
            'summary' => $stage,
            'rows' => [],
            'total' => 0,
            'page' => $page,
            'per_page' => $perPage,
            'filters' => $filters,
            'options' => ['branches' => [], 'stores' => [], 'units' => [], 'transcodes' => []],
        ];
        if (($stage['status'] ?? '') === 'PENDING_5D') {
            return $empty;
        }

        $where = ["p.PROFILE_METHOD_VERSION='5D.1'", 'LENGTH(p.PROFILE_METHOD_HASH)=64'];
        $params = [];
        $readiness = strtoupper(trim((string)($filters['readiness'] ?? '')));
        if ($readiness !== '' && in_array($readiness, ['READY', 'LIMITED', 'SPARSE', 'NO_ACTIVITY'], true)) {
            $where[] = 'p.READINESS_CODE=:readiness_code';
            $params['readiness_code'] = $readiness;
        }

        $numericFilters = [
            'branch' => ['column' => 'p.BRANCH_ID', 'bind' => 'branch_id'],
            'store' => ['column' => 'p.STORE_CODE', 'bind' => 'store_code'],
            'item' => ['column' => 'p.ITEM_CODE', 'bind' => 'item_code'],
            'unit' => ['column' => 'p.RAW_UNIT_CODE', 'bind' => 'unit_code'],
            'transcode' => ['column' => 'p.STORETRANSCODE', 'bind' => 'transcode'],
            'effect' => ['column' => 'p.STORETRANSEFFECT', 'bind' => 'effect'],
        ];
        foreach ($numericFilters as $filterKey => $definition) {
            $value = trim((string)($filters[$filterKey] ?? ''));
            if ($value !== '' && preg_match('/^-?\d+$/', $value)) {
                $where[] = $definition['column'] . '=:' . $definition['bind'];
                $params[$definition['bind']] = (int)$value;
            }
        }

        $q = trim((string)($filters['q'] ?? ''));
        if ($q !== '') {
            $where[] = "(TO_CHAR(p.ITEM_CODE)=:q_exact OR TO_CHAR(p.STORE_CODE)=:q_exact OR TO_CHAR(p.BRANCH_ID)=:q_exact OR UPPER(p.PROFILE_HASH) LIKE :q_text)";
            $params['q_exact'] = $q;
            $params['q_text'] = '%' . strtoupper($q) . '%';
        }

        $whereSql = implode(' AND ', $where);
        $count = Database::fetchOne(
            $connection,
            "SELECT COUNT(*) total_rows FROM DASH_DIAR_STAT_CURRENT p WHERE {$whereSql}",
            $params
        );
        $total = (int)($count['total_rows'] ?? 0);
        $start = (($page - 1) * $perPage) + 1;
        $end = min($total, $start + $perPage - 1);
        $rows = [];
        if ($total > 0) {
            $queryParams = $params;
            $queryParams['row_start'] = $start;
            $queryParams['row_end'] = $end;
            $rows = Database::fetchAll($connection, "SELECT * FROM (
                SELECT q.*, ROW_NUMBER() OVER (
                    ORDER BY q.AS_OF_DATE DESC,q.BRANCH_ID,q.STORE_CODE,q.ITEM_CODE,
                             q.RAW_UNIT_CODE,q.STORETRANSCODE,q.STORETRANSEFFECT
                ) rn
                FROM (
                    SELECT p.STAT_PROFILE_ID,p.HOSPITAL_CODE,p.SOURCE_DB,p.BRANCH_ID,p.STORE_CODE,p.ITEM_CODE,
                        p.RAW_UNIT_CODE,p.STORETRANSCODE,p.STORETRANSEFFECT,
                        TO_CHAR(p.AS_OF_DATE,'YYYY-MM-DD') as_of_date,
                        p.STAT_RUN_ID,p.READINESS_CODE,
                        TO_CHAR(p.BASELINE_FROM_DATE,'YYYY-MM-DD') baseline_from_date,
                        TO_CHAR(p.BASELINE_TO_DATE,'YYYY-MM-DD') baseline_to_date,
                        p.OBSERVATION_DAYS,p.ACTIVE_DAYS,p.ZERO_DAYS,
                        p.PRIMARY_ACTIVE_DAYS,p.RECENT_ACTIVE_DAYS,p.SPARSE_ACTIVE_DAYS,
                        TO_CHAR(p.LAST_ACTIVITY_DATE,'YYYY-MM-DD') last_activity_date,
                        p.QTY_MEAN,p.QTY_STDDEV,p.QTY_MEDIAN,p.QTY_MAD,p.QTY_P95,p.QTY_P99,p.QTY_MAX,
                        p.ABS_QTY_MEAN,p.ABS_QTY_P95,p.ABS_QTY_P99,p.ABS_QTY_MAX,
                        p.TXN_MEAN,p.TXN_STDDEV,p.TXN_MEDIAN,p.TXN_P95,p.TXN_P99,p.TXN_MAX,
                        p.LINE_ABS_MEAN,p.LINE_ABS_P95,p.LINE_ABS_P99,p.LINE_ABS_MAX,
                        p.VALUE_MEAN,p.VALUE_STDDEV,p.VALUE_MEDIAN,p.VALUE_MAD,p.VALUE_P95,p.VALUE_P99,p.VALUE_MAX,
                        p.ABS_VALUE_MEAN,p.ABS_VALUE_P95,p.ABS_VALUE_P99,p.ABS_VALUE_MAX,
                        p.RECENT_QTY_MEAN,p.RECENT_QTY_P95,p.RECENT_VALUE_MEAN,p.RECENT_VALUE_P95,
                        p.PROFILE_METHOD_VERSION,p.PROFILE_METHOD_HASH,p.PROFILE_HASH,
                        p.RULESET_VERSION_ID,p.RULESET_HASH,p.MOVE_MAP_VERSION_ID,p.MOVE_MAP_HASH,
                        p.MOVEMENT_GROUP_CODE,p.DIRECTION_CODE,
                        TO_CHAR(p.UPDATED_AT,'YYYY-MM-DD HH24:MI:SS') updated_at
                    FROM DASH_DIAR_STAT_CURRENT p
                    WHERE {$whereSql}
                ) q
            ) WHERE rn BETWEEN :row_start AND :row_end", $queryParams);
        }

        $stage['filtered_count'] = $total;
        return [
            'site' => $site,
            'availability' => $stage,
            'summary' => $stage,
            'rows' => $rows,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'filters' => $filters,
            'options' => $this->profileOptions($connection),
        ];
    }

    /**
     * Presents the approved local DIAR-5E-R0 contract without inventing live candidates.
     * The page becomes a live queue only after a separate DIAR-5E-R1 deployment defines
     * and verifies the physical candidate store.
     *
     * @param array<int,string> $allowedSites
     * @return array<string,mixed>
     */
    public function anomalyWorkspaceStatus(array $allowedSites, string $requestedSite = ''): array
    {
        $sites = $this->normalizeSites($allowedSites);
        $requestedSite = strtoupper(trim($requestedSite));
        if ($requestedSite !== '') {
            if (!in_array($requestedSite, $sites, true)) {
                throw new RuntimeException('SITE_NOT_ALLOWED');
            }
            $sites = [$requestedSite];
        }

        $siteRows = [];
        foreach ($sites as $site) {
            try {
                $siteRows[$site] = $this->profileStageSummary($this->connection($site));
            } catch (Throwable $e) {
                $siteRows[$site] = [
                    'status' => 'UNAVAILABLE',
                    'profile_count' => 0,
                    'ready_count' => 0,
                    'limited_count' => 0,
                    'sparse_count' => 0,
                    'no_activity_count' => 0,
                    'error' => $e->getMessage(),
                ];
            }
        }

        return [
            'status' => 'PENDING_DIAR_5E_R1',
            'result_mode' => 'CONTRACT_ONLY_NO_LIVE_CANDIDATES',
            'sites' => $siteRows,
            'candidate_count' => 0,
            'contract' => [
                'release' => 'DIAR-5E-R0',
                'result' => 'PASS_LOCAL_ONLY',
                'live_release_required' => 'DIAR-5E-R1',
                'drug_scope' => 'MMITEMSMAINDATA.ITEMSTORETYPE=2',
                'signals' => [
                    'QUANTITY_MAGNITUDE', 'FINANCIAL_VALUE_MAGNITUDE', 'TRANSACTION_FREQUENCY',
                    'MAX_LINE_QUANTITY', 'DORMANCY_REACTIVATION', 'MULTI_SIGNAL_RISK',
                ],
                'readiness' => ['READY', 'LIMITED', 'SPARSE', 'NO_ACTIVITY'],
                'required_bindings' => [
                    'STAT_RUN_ID', 'RULESET_VERSION_ID', 'RULESET_HASH', 'MOVE_MAP_VERSION_ID',
                    'MOVE_MAP_HASH', 'PROFILE_METHOD_VERSION', 'PROFILE_METHOD_HASH', 'PROFILE_HASH',
                ],
                'exact_grain' => [
                    'HOSPITAL_CODE', 'SOURCE_DB', 'BRANCH_ID', 'STORE_CODE', 'ITEM_CODE',
                    'RAW_UNIT_CODE', 'STORETRANSCODE', 'STORETRANSEFFECT', 'TARGET_DAY',
                ],
            ],
        ];
    }

    /** @return array<string,mixed> */
    private function siteOverview(string $site): array
    {
        $connection = $this->connection($site);
        $identity = Database::fetchOne($connection, "SELECT
            PKG_DASH_DIAR_ENGINE.FN_SCHEMA_VERSION package_version,
            PKG_DASH_DIAR_ENGINE.FN_HOSPITAL_CODE package_hospital,
            PKG_DASH_DIAR_ENGINE.FN_SOURCE_DB package_source_db
        FROM dual");
        $schema = Database::fetchOne($connection, "SELECT * FROM (
            SELECT PATCH_ID,SCHEMA_VERSION,CONTRACT_SHA256,SOURCE_BUNDLE_SHA256,STATUS_CODE,
                HOSPITAL_CODE,SOURCE_DB,TO_CHAR(APPLIED_AT,'YYYY-MM-DD HH24:MI:SS') applied_at,
                TO_CHAR(VERIFIED_AT,'YYYY-MM-DD HH24:MI:SS') verified_at
            FROM DASH_DIAR_SCHEMA_VERSION
            WHERE STATUS_CODE='ACTIVE'
            ORDER BY APPLIED_AT DESC
        ) WHERE ROWNUM=1");
        $fact = Database::fetchOne($connection, "SELECT
            COUNT(*) fact_rows,
            TO_CHAR(MIN(CALENDAR_DAY),'YYYY-MM-DD') first_fact_day,
            TO_CHAR(MAX(CALENDAR_DAY),'YYYY-MM-DD') last_fact_day,
            COUNT(DISTINCT CALENDAR_DAY) active_calendar_days,
            MAX(TO_CHAR(UPDATED_AT,'YYYY-MM-DD HH24:MI:SS')) last_fact_update
        FROM DASH_DIAR_DAILY_FACT");
        $latestRun = Database::fetchOne($connection, "SELECT * FROM (
            SELECT STAT_RUN_ID,RUN_TYPE,STATUS_CODE,CURRENT_STEP,
                TO_CHAR(TARGET_FROM_DATE,'YYYY-MM-DD') target_from_date,
                TO_CHAR(TARGET_TO_DATE,'YYYY-MM-DD') target_to_date,
                TO_CHAR(STARTED_AT,'YYYY-MM-DD HH24:MI:SS') started_at,
                TO_CHAR(COMPLETED_AT,'YYYY-MM-DD HH24:MI:SS') completed_at,
                ERROR_CODE,ERROR_MESSAGE,
                TO_CHAR(CREATED_AT,'YYYY-MM-DD HH24:MI:SS') created_at
            FROM DASH_DIAR_STAT_RUNS
            ORDER BY STAT_RUN_ID DESC
        ) WHERE ROWNUM=1");
        $runSummary = Database::fetchOne($connection, "SELECT
            COUNT(*) total_runs,
            SUM(CASE WHEN STATUS_CODE='RUNNING' THEN 1 ELSE 0 END) running_runs,
            SUM(CASE WHEN STATUS_CODE='PASS' THEN 1 ELSE 0 END) passed_runs,
            SUM(CASE WHEN STATUS_CODE='FAILED' THEN 1 ELSE 0 END) failed_runs,
            SUM(CASE WHEN RUN_TYPE='BACKFILL' AND STATUS_CODE='PASS' THEN 1 ELSE 0 END) passed_backfill_runs,
            SUM(CASE WHEN RUN_TYPE='BACKFILL' AND STATUS_CODE='RUNNING' THEN 1 ELSE 0 END) running_backfill_runs,
            SUM(CASE WHEN RUN_TYPE='BACKFILL' AND STATUS_CODE='FAILED' THEN 1 ELSE 0 END) failed_backfill_runs,
            TO_CHAR(MIN(CASE WHEN RUN_TYPE='BACKFILL' AND STATUS_CODE='PASS' THEN TARGET_FROM_DATE END),'YYYY-MM-DD') backfill_from_date,
            TO_CHAR(MAX(CASE WHEN RUN_TYPE='BACKFILL' AND STATUS_CODE='PASS' THEN TARGET_TO_DATE END),'YYYY-MM-DD') backfill_to_date
        FROM DASH_DIAR_STAT_RUNS");
        $map = Database::fetchOne($connection, "SELECT * FROM (
            SELECT MOVE_MAP_VERSION_ID,MAP_HASH,SOURCE_TYPE_COUNT,STATUS_CODE,
                TO_CHAR(CAPTURED_AT,'YYYY-MM-DD HH24:MI:SS') captured_at,
                TO_CHAR(ACTIVATED_AT,'YYYY-MM-DD HH24:MI:SS') activated_at
            FROM DASH_DIAR_MOVE_MAP_VERS
            WHERE STATUS_CODE='ACTIVE'
            ORDER BY ACTIVATED_AT DESC,MOVE_MAP_VERSION_ID DESC
        ) WHERE ROWNUM=1");
        $profileStage = $this->profileStageSummary($connection);
        $downstream = Database::fetchOne($connection, "SELECT
            (SELECT COUNT(*) FROM DASH_DIAR_ENGINE_CASES) engine_case_count,
            (SELECT COUNT(*) FROM DASH_DIAR_ANALYSIS_RUNS WHERE STATUS_CODE='RUNNING') running_analysis_runs
        FROM dual");
        $expected = Database::fetchOne($connection, "SELECT
            TO_CHAR(TRUNC(SYSDATE)-399,'YYYY-MM-DD') expected_first_day,
            TO_CHAR(TRUNC(SYSDATE)-1,'YYYY-MM-DD') expected_last_day,
            TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') database_now
        FROM dual");
        $recentRuns = Database::fetchAll($connection, "SELECT * FROM (
            SELECT STAT_RUN_ID,RUN_TYPE,STATUS_CODE,CURRENT_STEP,
                TO_CHAR(TARGET_FROM_DATE,'YYYY-MM-DD') target_from_date,
                TO_CHAR(TARGET_TO_DATE,'YYYY-MM-DD') target_to_date,
                TO_CHAR(STARTED_AT,'YYYY-MM-DD HH24:MI:SS') started_at,
                TO_CHAR(COMPLETED_AT,'YYYY-MM-DD HH24:MI:SS') completed_at,
                ERROR_CODE,
                TO_CHAR(CREATED_AT,'YYYY-MM-DD HH24:MI:SS') created_at
            FROM DASH_DIAR_STAT_RUNS
            ORDER BY STAT_RUN_ID DESC
        ) WHERE ROWNUM<=5");

        $factRows = (int)($fact['fact_rows'] ?? 0);
        $runningRuns = (int)($runSummary['running_runs'] ?? 0) + (int)($downstream['running_analysis_runs'] ?? 0);
        $failedRuns = (int)($runSummary['failed_runs'] ?? 0);
        $firstFact = (string)($fact['first_fact_day'] ?? '');
        $lastFact = (string)($fact['last_fact_day'] ?? '');
        $expectedFirst = (string)($expected['expected_first_day'] ?? '');
        $expectedLast = (string)($expected['expected_last_day'] ?? '');

        $coverageComplete = $firstFact !== '' && $lastFact !== '' && $expectedFirst !== '' && $expectedLast !== ''
            && $firstFact <= $expectedFirst && $lastFact >= $expectedLast;
        $readiness = 'NOT_STARTED';
        $readinessDetail = '';
        if ($runningRuns > 0 && $coverageComplete) {
            $readiness = 'IN_PROGRESS';
            $readinessDetail = 'FINAL_VALIDATION';
        } elseif ($runningRuns > 0) {
            $readiness = 'IN_PROGRESS';
        } elseif ($factRows === 0) {
            $readiness = 'NOT_STARTED';
        } elseif ($coverageComplete) {
            $readiness = 'READY';
        } elseif ((string)($latestRun['status_code'] ?? '') === 'FAILED') {
            $readiness = 'ATTENTION';
        } else {
            $readiness = 'PARTIAL';
        }

        $coveragePercent = 0;
        if ($firstFact !== '' && $lastFact !== '') {
            $firstTs = strtotime($firstFact);
            $lastTs = strtotime($lastFact);
            if ($firstTs !== false && $lastTs !== false && $lastTs >= $firstTs) {
                $coveragePercent = min(100, max(0, (int)round((($lastTs - $firstTs) / 86400 + 1) / 400 * 100)));
            }
        }

        return [
            'site_code' => $site,
            'status' => 'AVAILABLE',
            'readiness_code' => $readiness,
            'readiness_detail_code' => $readinessDetail,
            'coverage_complete' => $coverageComplete,
            'coverage_percent' => $coveragePercent,
            'fact_rows' => $factRows,
            'first_fact_day' => $firstFact,
            'last_fact_day' => $lastFact,
            'active_calendar_days' => (int)($fact['active_calendar_days'] ?? 0),
            'last_fact_update' => (string)($fact['last_fact_update'] ?? ''),
            'movement_type_count' => (int)($map['source_type_count'] ?? 0),
            'profile_count' => (int)($profileStage['profile_count'] ?? 0),
            'profile_ready_count' => (int)($profileStage['ready_count'] ?? 0),
            'profile_limited_count' => (int)($profileStage['limited_count'] ?? 0),
            'profile_sparse_count' => (int)($profileStage['sparse_count'] ?? 0),
            'profile_no_activity_count' => (int)($profileStage['no_activity_count'] ?? 0),
            'profile_stage' => $profileStage,
            'engine_case_count' => (int)($downstream['engine_case_count'] ?? 0),
            'running_runs' => $runningRuns,
            'failed_runs' => $failedRuns,
            'package_version' => (string)($identity['package_version'] ?? ''),
            'package_hospital' => (string)($identity['package_hospital'] ?? ''),
            'source_db' => (string)($identity['package_source_db'] ?? ''),
            'schema' => is_array($schema) ? $schema : [],
            'latest_run' => is_array($latestRun) ? $latestRun : [],
            'run_summary' => is_array($runSummary) ? $runSummary : [],
            'movement_map' => is_array($map) ? $map : [],
            'expected' => is_array($expected) ? $expected : [],
            'recent_runs' => $recentRuns,
            'error' => '',
        ];
    }

    /**
     * @param array<string,string> $filters
     * @return array{rows:array<int,array<string,mixed>>,total:int,page:int,per_page:int}
     */
    private function runsForSite(string $site, array $filters, int $page, int $perPage): array
    {
        $connection = $this->connection($site);
        $where = ['1=1'];
        $params = [];
        $status = strtoupper(trim((string)($filters['status'] ?? '')));
        if ($status !== '' && in_array($status, ['PENDING', 'RUNNING', 'PASS', 'FAILED', 'CANCELLED'], true)) {
            $where[] = 'r.STATUS_CODE=:status_code';
            $params['status_code'] = $status;
        }
        $runType = strtoupper(trim((string)($filters['run_type'] ?? '')));
        if ($runType !== '' && in_array($runType, ['NIGHTLY', 'MANUAL', 'BACKFILL', 'REPROCESS', 'TEST'], true)) {
            $where[] = 'r.RUN_TYPE=:run_type';
            $params['run_type'] = $runType;
        }
        $q = trim((string)($filters['q'] ?? ''));
        if ($q !== '') {
            $where[] = "(TO_CHAR(r.STAT_RUN_ID)=:q_exact OR UPPER(NVL(r.CURRENT_STEP,' ')) LIKE :q_text OR UPPER(NVL(r.ERROR_CODE,' ')) LIKE :q_text)";
            $params['q_exact'] = $q;
            $params['q_text'] = '%' . strtoupper($q) . '%';
        }
        $whereSql = implode(' AND ', $where);
        $count = Database::fetchOne($connection, "SELECT COUNT(*) total_rows FROM DASH_DIAR_STAT_RUNS r WHERE {$whereSql}", $params);
        $total = (int)($count['total_rows'] ?? 0);
        $start = (($page - 1) * $perPage) + 1;
        $end = min($total, $start + $perPage - 1);
        $rows = [];
        if ($total > 0) {
            $queryParams = $params;
            $queryParams['row_start'] = $start;
            $queryParams['row_end'] = $end;
            $rows = Database::fetchAll($connection, "SELECT * FROM (
                SELECT q.*, ROW_NUMBER() OVER (ORDER BY q.STAT_RUN_ID DESC) rn
                FROM (
                    SELECT r.STAT_RUN_ID,r.HOSPITAL_CODE,r.SOURCE_DB,r.RUN_TYPE,r.STAT_GRAIN_CODE,
                        TO_CHAR(r.TARGET_FROM_DATE,'YYYY-MM-DD') target_from_date,
                        TO_CHAR(r.TARGET_TO_DATE,'YYYY-MM-DD') target_to_date,
                        TO_CHAR(r.BASELINE_FROM_DATE,'YYYY-MM-DD') baseline_from_date,
                        TO_CHAR(r.BASELINE_TO_DATE,'YYYY-MM-DD') baseline_to_date,
                        r.STATUS_CODE,r.CURRENT_STEP,
                        TO_CHAR(r.STARTED_AT,'YYYY-MM-DD HH24:MI:SS') started_at,
                        TO_CHAR(r.COMPLETED_AT,'YYYY-MM-DD HH24:MI:SS') completed_at,
                        r.REQUESTED_BY,r.ERROR_CODE,r.ERROR_MESSAGE,
                        TO_CHAR(r.CREATED_AT,'YYYY-MM-DD HH24:MI:SS') created_at,
                        TO_CHAR(r.CREATED_AT,'YYYY-MM-DD\"T\"HH24:MI:SS') created_at_iso,
                        TO_CHAR(r.STARTED_AT,'YYYY-MM-DD\"T\"HH24:MI:SS') started_at_iso
                    FROM DASH_DIAR_STAT_RUNS r
                    WHERE {$whereSql}
                ) q
            ) WHERE rn BETWEEN :row_start AND :row_end", $queryParams);
        }
        return ['rows' => $rows, 'total' => $total, 'page' => $page, 'per_page' => $perPage];
    }

    /** @return array<string,array<int,array<string,mixed>>> */
    private function dailyFactOptions(string $connection, string $dateFrom, string $dateTo): array
    {
        $params = ['date_from' => $dateFrom, 'date_to' => $dateTo];
        $base = " FROM DASH_DIAR_DAILY_FACT WHERE CALENDAR_DAY>=TO_DATE(:date_from,'YYYY-MM-DD') AND CALENDAR_DAY<TO_DATE(:date_to,'YYYY-MM-DD')+1";
        return [
            'branches' => Database::fetchAll($connection, "SELECT BRANCH_ID value_code FROM (SELECT DISTINCT BRANCH_ID{$base} ORDER BY BRANCH_ID) WHERE ROWNUM<=100", $params),
            'stores' => Database::fetchAll($connection, "SELECT STORE_CODE value_code FROM (SELECT DISTINCT STORE_CODE{$base} ORDER BY STORE_CODE) WHERE ROWNUM<=500", $params),
            'units' => Database::fetchAll($connection, "SELECT RAW_UNIT_CODE value_code FROM (SELECT DISTINCT RAW_UNIT_CODE{$base} ORDER BY RAW_UNIT_CODE) WHERE ROWNUM<=200", $params),
            'transcodes' => Database::fetchAll($connection, "SELECT STORETRANSCODE value_code FROM (SELECT DISTINCT STORETRANSCODE{$base} ORDER BY STORETRANSCODE) WHERE ROWNUM<=100", $params),
        ];
    }


    /** @return array<string,mixed> */
    private function profileStageSummary(string $connection): array
    {
        $required = [
            'HOSPITAL_CODE', 'SOURCE_DB', 'BRANCH_ID', 'STORE_CODE', 'ITEM_CODE',
            'RAW_UNIT_CODE', 'STORETRANSCODE', 'STORETRANSEFFECT', 'AS_OF_DATE',
            'STAT_RUN_ID', 'READINESS_CODE', 'PROFILE_HASH', 'PROFILE_METHOD_VERSION',
            'PROFILE_METHOD_HASH', 'PRIMARY_ACTIVE_DAYS', 'SPARSE_ACTIVE_DAYS',
            'RECENT_ACTIVE_DAYS', 'LAST_ACTIVITY_DATE', 'VALUE_MEAN', 'VALUE_P95',
            'ABS_VALUE_P95', 'RECENT_VALUE_MEAN', 'RECENT_VALUE_P95',
        ];
        $columns = $this->objectColumns($connection, 'DASH_DIAR_STAT_CURRENT');
        $missing = array_values(array_diff($required, $columns));
        $packageVersion = '';
        try {
            $identity = Database::fetchOne(
                $connection,
                'SELECT PKG_DASH_DIAR_ENGINE.FN_SCHEMA_VERSION package_version FROM dual'
            );
            $packageVersion = trim((string)($identity['package_version'] ?? ''));
        } catch (Throwable $ignored) {
            $packageVersion = '';
        }

        if ($columns === [] || $missing !== [] || $packageVersion !== '5D.1') {
            return [
                'status' => 'PENDING_5D',
                'package_version' => $packageVersion,
                'profile_count' => 0,
                'ready_count' => 0,
                'limited_count' => 0,
                'sparse_count' => 0,
                'no_activity_count' => 0,
                'as_of_date' => '',
                'latest_stat_run_id' => 0,
                'profile_method_version' => '',
                'profile_method_hash' => '',
                'last_updated_at' => '',
                'missing_columns' => $missing,
            ];
        }

        $row = Database::fetchOne($connection, "SELECT
            COUNT(*) profile_count,
            NVL(SUM(CASE WHEN READINESS_CODE='READY' THEN 1 ELSE 0 END),0) ready_count,
            NVL(SUM(CASE WHEN READINESS_CODE='LIMITED' THEN 1 ELSE 0 END),0) limited_count,
            NVL(SUM(CASE WHEN READINESS_CODE='SPARSE' THEN 1 ELSE 0 END),0) sparse_count,
            NVL(SUM(CASE WHEN READINESS_CODE='NO_ACTIVITY' THEN 1 ELSE 0 END),0) no_activity_count,
            TO_CHAR(MAX(AS_OF_DATE),'YYYY-MM-DD') as_of_date,
            NVL(MAX(STAT_RUN_ID) KEEP (DENSE_RANK LAST ORDER BY AS_OF_DATE,STAT_RUN_ID),0) latest_stat_run_id,
            MAX(PROFILE_METHOD_VERSION) KEEP (DENSE_RANK LAST ORDER BY AS_OF_DATE,STAT_RUN_ID) profile_method_version,
            MAX(PROFILE_METHOD_HASH) KEEP (DENSE_RANK LAST ORDER BY AS_OF_DATE,STAT_RUN_ID) profile_method_hash,
            TO_CHAR(MAX(UPDATED_AT),'YYYY-MM-DD HH24:MI:SS') last_updated_at
        FROM DASH_DIAR_STAT_CURRENT
        WHERE PROFILE_METHOD_VERSION='5D.1'
          AND LENGTH(PROFILE_METHOD_HASH)=64");

        $profileCount = (int)($row['profile_count'] ?? 0);
        return [
            'status' => $profileCount > 0 ? 'LIVE' : 'EMPTY_READY',
            'package_version' => $packageVersion,
            'profile_count' => $profileCount,
            'ready_count' => (int)($row['ready_count'] ?? 0),
            'limited_count' => (int)($row['limited_count'] ?? 0),
            'sparse_count' => (int)($row['sparse_count'] ?? 0),
            'no_activity_count' => (int)($row['no_activity_count'] ?? 0),
            'as_of_date' => (string)($row['as_of_date'] ?? ''),
            'latest_stat_run_id' => (int)($row['latest_stat_run_id'] ?? 0),
            'profile_method_version' => (string)($row['profile_method_version'] ?? ''),
            'profile_method_hash' => (string)($row['profile_method_hash'] ?? ''),
            'last_updated_at' => (string)($row['last_updated_at'] ?? ''),
            'missing_columns' => [],
        ];
    }

    /** @return array<int,string> */
    private function objectColumns(string $connection, string $table): array
    {
        $rows = Database::fetchAll(
            $connection,
            'SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME=:table_name ORDER BY COLUMN_ID',
            ['table_name' => strtoupper(trim($table))]
        );
        $columns = [];
        foreach ($rows as $row) {
            $column = strtoupper(trim((string)($row['column_name'] ?? '')));
            if ($column !== '') {
                $columns[] = $column;
            }
        }
        return $columns;
    }

    /** @return array<string,array<int,array<string,mixed>>> */
    private function profileOptions(string $connection): array
    {
        return [
            'branches' => Database::fetchAll($connection, 'SELECT BRANCH_ID value_code FROM (SELECT DISTINCT BRANCH_ID FROM DASH_DIAR_STAT_CURRENT ORDER BY BRANCH_ID) WHERE ROWNUM<=100'),
            'stores' => Database::fetchAll($connection, 'SELECT STORE_CODE value_code FROM (SELECT DISTINCT STORE_CODE FROM DASH_DIAR_STAT_CURRENT ORDER BY STORE_CODE) WHERE ROWNUM<=500'),
            'units' => Database::fetchAll($connection, 'SELECT RAW_UNIT_CODE value_code FROM (SELECT DISTINCT RAW_UNIT_CODE FROM DASH_DIAR_STAT_CURRENT ORDER BY RAW_UNIT_CODE) WHERE ROWNUM<=200'),
            'transcodes' => Database::fetchAll($connection, 'SELECT STORETRANSCODE value_code FROM (SELECT DISTINCT STORETRANSCODE FROM DASH_DIAR_STAT_CURRENT ORDER BY STORETRANSCODE) WHERE ROWNUM<=100'),
        ];
    }

    /** @param array<int,string> $allowedSites */
    private function resolveRequiredSite(string $requested, array $allowedSites): string
    {
        $sites = $this->normalizeSites($allowedSites);
        if ($sites === []) {
            throw new RuntimeException('NO_ALLOWED_SITES');
        }
        $requested = strtoupper(trim($requested));
        if ($requested === '') {
            return $sites[0];
        }
        if (!in_array($requested, $sites, true)) {
            throw new RuntimeException('SITE_NOT_ALLOWED');
        }
        return $requested;
    }

    /** @param array<int,string> $sites @return array<int,string> */
    private function normalizeSites(array $sites): array
    {
        $out = [];
        foreach ($sites as $site) {
            $site = strtoupper(trim((string)$site));
            if (isset(self::SITE_CONNECTIONS[$site]) && !in_array($site, $out, true)) {
                $out[] = $site;
            }
        }
        return $out;
    }

    private function connection(string $site): string
    {
        $site = strtoupper(trim($site));
        if (!isset(self::SITE_CONNECTIONS[$site])) {
            throw new RuntimeException('UNSUPPORTED_SITE');
        }
        return self::SITE_CONNECTIONS[$site];
    }
}
