<?php

declare(strict_types=1);

namespace App\Repositories;

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

/**
 * DIAR-6B read model for the executive overview.
 *
 * Read boundary:
 * - Reads DIAR-owned monthly/run tables only.
 * - Never queries or writes operational MM* tables.
 * - Never uses a database link.
 * - Keeps hospital/source/snapshot identity on every merged row.
 */
final class DiarExecutiveOverviewRepository
{
    /** @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 overview(array $allowedSites, array $filters): array
    {
        $requestedSites = $this->resolver->requestedSites((string)($filters['site'] ?? 'ALL'), $allowedSites);
        $period = $this->normalizePeriod((string)($filters['period'] ?? 'latest_closed'));
        $explicitMonth = $this->normalizeMonth((string)($filters['month'] ?? ''));

        $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 = $explicitMonth !== ''
            ? $explicitMonth
            : $this->selectDefaultMonth($catalogs, $period);

        $siteCards = [];
        foreach ($requestedSites as $site) {
            if (!isset($catalogs[$site])) {
                $siteCards[$site] = $unavailable[$site];
                continue;
            }
            try {
                $siteCards[$site] = $this->siteSnapshot($site, $selectedMonth, $period, $catalogs[$site]);
            } catch (Throwable $e) {
                $siteCards[$site] = [
                    'site_code' => $site,
                    'status_code' => 'UNAVAILABLE',
                    'message_code' => $this->safeMessageCode($e),
                    'month_options' => $catalogs[$site]['month_options'],
                ];
            }
        }

        return $this->combine($requestedSites, $siteCards, $catalogs, $period, $selectedMonth);
    }

    /** @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_DAYS',
            'DASH_DIAR_MONTH_CASES','DASH_DIAR_OP_RUNS','DASH_DIAR_OP_LOCK'
        )");
        if ((int)($required['object_count'] ?? 0) !== 6) {
            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,
            PKG_DASH_DIAR_ENGINE.FN_SCHEMA_VERSION engine_version,
            PKG_DASH_DIAR_OPS.FN_VERSION ops_version
        FROM dual");
        if (strtoupper((string)($identity['hospital_code'] ?? '')) !== $site) {
            throw new RuntimeException('DIAR_SITE_IDENTITY_MISMATCH');
        }

        $monthOptions = Database::fetchAll($connection, "SELECT * FROM (
            SELECT TO_CHAR(s.MONTH_START,'YYYY-MM') month_key,
                   TO_CHAR(s.MONTH_START,'YYYY-MM-DD') month_start,
                   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' => (string)($identity['source_db'] ?? ''),
        ]);

        return [
            'site_code' => $site,
            'connection' => $connection,
            'source_db' => (string)($identity['source_db'] ?? ''),
            'engine_version' => (string)($identity['engine_version'] ?? ''),
            'ops_version' => (string)($identity['ops_version'] ?? ''),
            'month_options' => $monthOptions,
        ];
    }

    /**
     * @param array<string,mixed> $catalog
     * @return array<string,mixed>
     */
    private function siteSnapshot(string $site, string $month, string $period, array $catalog): array
    {
        $connection = (string)$catalog['connection'];
        $sourceDb = (string)$catalog['source_db'];
        $month = $this->resolveSiteMonth($month, $period, (array)$catalog['month_options']);
        if ($month === '') {
            return [
                'site_code' => $site,
                'source_db' => $sourceDb,
                'status_code' => 'NO_SNAPSHOT',
                'message_code' => 'NO_MONTHLY_SNAPSHOT',
                'month_options' => $catalog['month_options'],
            ];
        }

        $header = Database::fetchOne($connection, "SELECT s.MONTH_SNAPSHOT_ID,s.HOSPITAL_CODE,s.SOURCE_DB,
            TO_CHAR(s.MONTH_START,'YYYY-MM') month_key,
            TO_CHAR(s.MONTH_START,'YYYY-MM-DD') month_start,
            s.VERSION_NO,s.VERSION_TYPE,s.STATUS_CODE,
            TO_CHAR(s.DATA_UPDATED_THROUGH,'YYYY-MM-DD') data_updated_through,
            s.DAYS_EXPECTED,s.DAYS_ANALYZED,s.MISSING_DAYS,
            s.EVALUATED_ROWS,s.PROFILED_ROWS,s.EXCLUDED_ROWS,
            s.DETECTED_COUNT,s.SUPPRESSED_COUNT,s.CRITICAL_COUNT,s.HIGH_COUNT,s.MEDIUM_COUNT,
            s.INVENTORY_COST_ABS,s.CANDIDATE_TXN_COUNT,s.CANDIDATE_LINE_COUNT,
            s.PROFILE_METHOD_VERSION,s.DETECTION_METHOD_VERSION,s.RULESET_HASH,s.MOVE_MAP_HASH,s.SNAPSHOT_HASH,
            TO_CHAR(s.CREATED_AT,'YYYY-MM-DD HH24:MI:SS') created_at,
            TO_CHAR(s.CLOSED_AT,'YYYY-MM-DD HH24:MI:SS') closed_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
          AND c.MONTH_START=TO_DATE(:month_start,'YYYY-MM-DD')", [
            'hospital_code' => $site,
            'source_db' => $sourceDb,
            'month_start' => $month . '-01',
        ]);
        if (!is_array($header)) {
            return [
                'site_code' => $site,
                'source_db' => $sourceDb,
                'status_code' => 'NO_SNAPSHOT',
                'message_code' => 'NO_SNAPSHOT_FOR_SELECTED_MONTH',
                'selected_month' => $month,
                'month_options' => $catalog['month_options'],
            ];
        }

        $snapshotId = (int)$header['month_snapshot_id'];
        $trend = Database::fetchAll($connection, "SELECT
            TO_CHAR(CALENDAR_DAY,'YYYY-MM-DD') calendar_day,
            DAY_STATUS,DETECTED_COUNT,SUPPRESSED_COUNT,CRITICAL_COUNT,HIGH_COUNT,MEDIUM_COUNT,
            INVENTORY_COST_ABS,CANDIDATE_TXN_COUNT,CANDIDATE_LINE_COUNT
        FROM DASH_DIAR_MONTH_DAYS
        WHERE MONTH_SNAPSHOT_ID=:snapshot_id
        ORDER BY CALENDAR_DAY", ['snapshot_id' => $snapshotId]);

        $topStores = Database::fetchAll($connection, "SELECT * FROM (
            SELECT STORE_CODE,COUNT(*) case_count,
                   SUM(CASE WHEN SEVERITY_CODE='CRITICAL' THEN 1 ELSE 0 END) critical_count,
                   SUM(CASE WHEN SEVERITY_CODE='HIGH' THEN 1 ELSE 0 END) high_count,
                   SUM(CASE WHEN SEVERITY_CODE='MEDIUM' THEN 1 ELSE 0 END) medium_count,
                   NVL(SUM(INVENTORY_COST_ABS),0) inventory_cost_abs,
                   NVL(SUM(TRANSACTION_COUNT),0) transaction_count,
                   NVL(SUM(MOVEMENT_LINES),0) movement_lines
            FROM DASH_DIAR_MONTH_CASES
            WHERE MONTH_SNAPSHOT_ID=:snapshot_id
            GROUP BY STORE_CODE
            ORDER BY case_count DESC,inventory_cost_abs DESC,STORE_CODE
        ) WHERE ROWNUM<=8", ['snapshot_id' => $snapshotId]);

        $topItems = Database::fetchAll($connection, "SELECT * FROM (
            SELECT ITEM_CODE,COUNT(*) case_count,
                   SUM(CASE WHEN SEVERITY_CODE='CRITICAL' THEN 1 ELSE 0 END) critical_count,
                   SUM(CASE WHEN SEVERITY_CODE='HIGH' THEN 1 ELSE 0 END) high_count,
                   SUM(CASE WHEN SEVERITY_CODE='MEDIUM' THEN 1 ELSE 0 END) medium_count,
                   NVL(SUM(INVENTORY_COST_ABS),0) inventory_cost_abs,
                   NVL(SUM(TRANSACTION_COUNT),0) transaction_count,
                   NVL(SUM(MOVEMENT_LINES),0) movement_lines
            FROM DASH_DIAR_MONTH_CASES
            WHERE MONTH_SNAPSHOT_ID=:snapshot_id
            GROUP BY ITEM_CODE
            ORDER BY case_count DESC,inventory_cost_abs DESC,ITEM_CODE
        ) WHERE ROWNUM<=8", ['snapshot_id' => $snapshotId]);

        $dimensions = Database::fetchOne($connection, "SELECT
            COUNT(DISTINCT BRANCH_ID) branch_count,
            COUNT(DISTINCT STORE_CODE) store_count,
            COUNT(DISTINCT ITEM_CODE) item_count,
            COUNT(DISTINCT RAW_UNIT_CODE) unit_count,
            SUM(CASE WHEN STORE_CODE IN (56,57) THEN 1 ELSE 0 END) bad_store_cases
        FROM DASH_DIAR_MONTH_CASES WHERE MONTH_SNAPSHOT_ID=:snapshot_id", ['snapshot_id' => $snapshotId]);

        $operations = Database::fetchOne($connection, "SELECT
            (SELECT LOCK_STATUS FROM DASH_DIAR_OP_LOCK WHERE LOCK_NAME='DIAR_WRITER') lock_status,
            (SELECT COUNT(*) FROM USER_SCHEDULER_JOBS WHERE JOB_NAME='JOB_DASH_DIAR_NIGHTLY' AND ENABLED='TRUE') nightly_enabled,
            (SELECT COUNT(*) FROM USER_SCHEDULER_JOBS WHERE JOB_NAME='JOB_DASH_DIAR_MONTH_CLOSE' AND ENABLED='TRUE') month_close_enabled,
            (SELECT COUNT(*) FROM DASH_DIAR_OP_RUNS WHERE STATUS_CODE='RUNNING') running_ops,
            (SELECT COUNT(*) FROM DASH_DIAR_OP_RUNS WHERE STATUS_CODE='FAILED' AND CREATED_AT>=SYSDATE-2) recent_failed_ops
        FROM dual");

        $latestRun = Database::fetchOne($connection, "SELECT * FROM (
            SELECT OP_RUN_ID,RUN_KIND,STATUS_CODE,CURRENT_STEP,RECOVERY_STATUS,
                   TO_CHAR(BUSINESS_DATE,'YYYY-MM-DD') business_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
            FROM DASH_DIAR_OP_RUNS ORDER BY OP_RUN_ID DESC
        ) WHERE ROWNUM=1");

        return [
            'site_code' => $site,
            'source_db' => $sourceDb,
            'status_code' => 'AVAILABLE',
            'identity_key' => $site . '|' . $sourceDb . '|' . $snapshotId,
            'selected_month' => $month,
            'header' => $header,
            'trend' => $trend,
            'top_stores' => $topStores,
            'top_items' => $topItems,
            'dimensions' => is_array($dimensions) ? $dimensions : [],
            'operations' => is_array($operations) ? $operations : [],
            'latest_run' => is_array($latestRun) ? $latestRun : [],
            'engine_version' => (string)$catalog['engine_version'],
            'ops_version' => (string)$catalog['ops_version'],
            'month_options' => $catalog['month_options'],
        ];
    }

    /**
     * @param array<int,string> $requestedSites
     * @param array<string,array<string,mixed>> $siteCards
     * @param array<string,array<string,mixed>> $catalogs
     * @return array<string,mixed>
     */
    private function combine(array $requestedSites, array $siteCards, array $catalogs, string $period, string $selectedMonth): array
    {
        $summary = [
            'hospital_count' => 0,
            'detected_count' => 0,
            'suppressed_count' => 0,
            'critical_count' => 0,
            'high_count' => 0,
            'medium_count' => 0,
            'inventory_cost_abs' => 0.0,
            'candidate_txn_count' => 0,
            'candidate_line_count' => 0,
            'store_count' => 0,
            'item_count' => 0,
            'missing_days' => 0,
            'bad_store_cases' => 0,
        ];
        $trendByDay = [];
        $topStores = [];
        $topItems = [];
        $availableSites = [];
        $unavailableSites = [];
        $snapshotRefs = [];
        $updatedThrough = [];
        $monthStatuses = [];

        foreach ($siteCards as $site => $card) {
            if (($card['status_code'] ?? '') !== 'AVAILABLE') {
                $unavailableSites[] = $site;
                continue;
            }
            $availableSites[] = $site;
            $header = (array)($card['header'] ?? []);
            $dimensions = (array)($card['dimensions'] ?? []);
            $summary['hospital_count']++;
            foreach (['detected_count','suppressed_count','critical_count','high_count','medium_count','candidate_txn_count','candidate_line_count','missing_days'] as $key) {
                $summary[$key] += (int)($header[$key] ?? 0);
            }
            $summary['inventory_cost_abs'] += (float)($header['inventory_cost_abs'] ?? 0);
            $summary['store_count'] += (int)($dimensions['store_count'] ?? 0);
            $summary['item_count'] += (int)($dimensions['item_count'] ?? 0);
            $summary['bad_store_cases'] += (int)($dimensions['bad_store_cases'] ?? 0);
            $snapshotRefs[] = [
                'hospital_code' => $site,
                'source_db' => (string)($card['source_db'] ?? ''),
                'month_snapshot_id' => (int)($header['month_snapshot_id'] ?? 0),
                'version_no' => (int)($header['version_no'] ?? 0),
                'snapshot_hash' => (string)($header['snapshot_hash'] ?? ''),
            ];
            if (!empty($header['data_updated_through'])) {
                $updatedThrough[] = (string)$header['data_updated_through'];
            }
            $monthStatuses[] = (string)($header['status_code'] ?? '');

            foreach ((array)($card['trend'] ?? []) as $row) {
                $day = (string)($row['calendar_day'] ?? '');
                if ($day === '') {
                    continue;
                }
                if (!isset($trendByDay[$day])) {
                    $trendByDay[$day] = [
                        'calendar_day' => $day,
                        'detected_count' => 0,
                        'critical_count' => 0,
                        'high_count' => 0,
                        'medium_count' => 0,
                        'inventory_cost_abs' => 0.0,
                    ];
                }
                foreach (['detected_count','critical_count','high_count','medium_count'] as $key) {
                    $trendByDay[$day][$key] += (int)($row[$key] ?? 0);
                }
                $trendByDay[$day]['inventory_cost_abs'] += (float)($row['inventory_cost_abs'] ?? 0);
            }

            foreach ((array)($card['top_stores'] ?? []) as $row) {
                $row['hospital_code'] = $site;
                $row['source_db'] = (string)($card['source_db'] ?? '');
                $row['identity_key'] = $site . '|' . $row['source_db'] . '|' . (string)($row['store_code'] ?? '');
                $topStores[] = $row;
            }
            foreach ((array)($card['top_items'] ?? []) as $row) {
                $row['hospital_code'] = $site;
                $row['source_db'] = (string)($card['source_db'] ?? '');
                $row['identity_key'] = $site . '|' . $row['source_db'] . '|' . (string)($row['item_code'] ?? '');
                $topItems[] = $row;
            }
        }

        ksort($trendByDay);
        $sortRisk = static function (array $a, array $b): int {
            $cases = (int)($b['case_count'] ?? 0) <=> (int)($a['case_count'] ?? 0);
            if ($cases !== 0) {
                return $cases;
            }
            return (float)($b['inventory_cost_abs'] ?? 0) <=> (float)($a['inventory_cost_abs'] ?? 0);
        };
        usort($topStores, $sortRisk);
        usort($topItems, $sortRisk);

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

        return [
            'filters' => [
                'site' => count($requestedSites) === 1 ? $requestedSites[0] : 'ALL',
                'period' => $period,
                'month' => $selectedMonth,
            ],
            'requested_sites' => $requestedSites,
            'available_sites' => $availableSites,
            'unavailable_sites' => $unavailableSites,
            'partial_coverage' => count($availableSites) !== count($requestedSites),
            'scope_status' => count($availableSites) === count($requestedSites) ? 'COMPLETE' : 'PARTIAL',
            'selected_month' => $selectedMonth,
            'month_status' => count(array_unique(array_filter($monthStatuses))) === 1 ? (string)($monthStatuses[0] ?? '') : 'MIXED',
            'updated_through' => $updatedThrough === [] ? '' : min($updatedThrough),
            'summary' => $summary,
            'site_cards' => $siteCards,
            'trend' => array_values($trendByDay),
            'top_stores' => array_slice($topStores, 0, 10),
            'top_items' => array_slice($topItems, 0, 10),
            'month_options' => array_values($monthOptions),
            'snapshot_refs' => $snapshotRefs,
            'contract' => [
                'read_only' => true,
                'database_link' => false,
                'primary_baseline_days' => 365,
                'daily_fact_retention_days' => 400,
                'late_refresh_days' => 7,
                'excluded_stores' => [56, 57],
                'grain' => 'HOSPITAL|SOURCE_DB|BRANCH|STORE|ITEM|RAW_UNIT|STORETRANSCODE|STORETRANSEFFECT',
            ],
        ];
    }

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

    /** @param array<int,array<string,mixed>> $options */
    private function resolveSiteMonth(string $selectedMonth, string $period, array $options): string
    {
        if ($selectedMonth !== '') {
            return $selectedMonth;
        }
        foreach ($options as $row) {
            $status = strtoupper((string)($row['status_code'] ?? ''));
            if (($period === 'current_open' && $status === 'OPEN') || ($period === 'latest_closed' && $status === 'CLOSED')) {
                return $this->normalizeMonth((string)($row['month_key'] ?? ''));
            }
        }
        return '';
    }

    private function normalizePeriod(string $period): string
    {
        $period = strtolower(trim($period));
        return in_array($period, ['latest_closed', 'current_open'], true) ? $period : 'latest_closed';
    }

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

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