<?php

declare(strict_types=1);

namespace App\Repositories;

use App\Core\Database;
use Throwable;

/**
 * Frozen-snapshot report read model for DIAR-6B.5-R2.2.
 *
 * Reads each hospital directly and merges in PHP. It never uses a database
 * link and never writes Oracle/source medication data.
 */
final class DiarReportDataRepository
{
    /**
     * @param array<int,string> $allowedSites
     * @param array<string,string> $filters
     * @return array<string,mixed>
     */
    public function build(array $allowedSites, array $filters, bool $includeMovementDetails = false): array
    {
        $overview = (new DiarExecutiveOverviewRepository())->overview($allowedSites, $filters);
        $report = [
            'release' => 'DIAR-6B.5-R2.2',
            'scope_code' => strtoupper((string)($overview['filters']['site'] ?? $filters['site'] ?? 'ALL')),
            'selected_month' => (string)($overview['selected_month'] ?? ''),
            'month_status' => (string)($overview['month_status'] ?? ''),
            'updated_through' => (string)($overview['updated_through'] ?? ''),
            'summary' => (array)($overview['summary'] ?? []),
            'site_cards' => (array)($overview['site_cards'] ?? []),
            'snapshot_refs' => (array)($overview['snapshot_refs'] ?? []),
            'month_options' => (array)($overview['month_options'] ?? []),
            'contract' => (array)($overview['contract'] ?? []),
            'cases' => [],
            'critical_cases' => [],
            'movement_details' => [],
            'daily_distribution' => [],
            'store_analysis' => [],
            'movement_analysis' => [],
            'item_analysis' => [],
            'user_associations' => [],
            'statistical_basis' => [],
            'statistical_comparisons' => [],
            'top_stores' => [],
            'top_items' => [],
            'top_movements' => [],
            'insights' => [],
            'recommendations' => [],
            'errors' => [],
            'generated_at' => date('Y-m-d H:i:s'),
        ];

        $allCases = [];
        $allMovements = [];
        $allSignals = [];
        $dailyRows = [];

        foreach ((array)($overview['site_cards'] ?? []) as $key => $card) {
            if (!is_array($card) || strtoupper((string)($card['status_code'] ?? '')) !== 'AVAILABLE') {
                continue;
            }
            $site = strtoupper((string)($card['site_code'] ?? $key));
            $header = (array)($card['header'] ?? []);
            $snapshotId = (int)($header['month_snapshot_id'] ?? 0);
            if ($snapshotId <= 0 || !in_array($site, ['MNH', 'NJCH'], true)) {
                continue;
            }
            try {
                $cases = $this->siteCases($site, $snapshotId);
                foreach ($cases as &$row) {
                    $row['site_code'] = $site;
                    $row['source_db'] = (string)($card['source_db'] ?? $header['source_db'] ?? '');
                }
                unset($row);
                $signals = $this->siteSignals($site, $snapshotId);
                foreach ($signals as &$signal) {
                    $signal['site_code'] = $site;
                }
                unset($signal);
                $daily = $this->siteDaily($site, $snapshotId);
                foreach ($daily as &$day) {
                    $day['site_code'] = $site;
                }
                unset($day);
                $movements = $includeMovementDetails ? $this->siteMovementDetails($site, $snapshotId) : [];
                foreach ($movements as &$movement) {
                    $movement['site_code'] = $site;
                }
                unset($movement);
                $allCases = array_merge($allCases, $cases);
                $allSignals = array_merge($allSignals, $signals);
                $dailyRows = array_merge($dailyRows, $daily);
                $allMovements = array_merge($allMovements, $movements);
            } catch (Throwable $e) {
                $report['errors'][] = $site . ':' . $this->safeError($e);
            }
        }

        $signalMap = $this->signalMap($allSignals);
        $movementMap = $this->movementMap($allMovements);
        $cases = $this->decorateCases($allCases, $signalMap, $movementMap, (string)$report['scope_code']);
        $report['cases'] = $cases;
        $report['critical_cases'] = array_values(array_filter($cases, static function (array $row): bool {
            return strtoupper((string)($row['priority'] ?? '')) === 'CRITICAL';
        }));
        $report['movement_details'] = $this->decorateMovements($allMovements, $cases, (string)$report['scope_code']);
        $report['daily_distribution'] = $this->combineDaily($dailyRows, $cases);
        $report['store_analysis'] = $this->storeAnalysis($cases, $allMovements);
        $report['movement_analysis'] = $this->movementAnalysis($cases);
        $report['item_analysis'] = $this->itemAnalysis($cases);
        $report['user_associations'] = $this->userAnalysis($allMovements, $cases);
        $report['statistical_comparisons'] = $this->statisticalComparisons($cases, $signalMap);
        $report['statistical_basis'] = $this->statisticalBasis($cases, $signalMap, $dailyRows, (array)$report['summary']);
        $report['top_stores'] = array_slice((array)$report['store_analysis'], 0, 5);
        $report['top_items'] = array_slice((array)$report['item_analysis'], 0, 5);
        $report['top_movements'] = array_slice((array)$report['movement_analysis'], 0, 5);
        $this->enrichSummary($report);
        $report['insights'] = $this->insights($report);
        $report['recommendations'] = $this->recommendations($report);
        $report['snapshot_library'] = $this->snapshotLibrary((array)$report['site_cards']);
        return $report;
    }

    /** @return array<int,array<string,mixed>> */
    private function siteCases(string $site, int $snapshotId): array
    {
        return Database::fetchAll(strtolower($site), "SELECT
            MC.MONTH_CASE_ID,MC.MONTH_SNAPSHOT_ID,TO_CHAR(MC.CALENDAR_DAY,'YYYY-MM-DD') CALENDAR_DAY,
            MC.CANDIDATE_ID,MC.CANDIDATE_HASH,MC.HOSPITAL_CODE,MC.SOURCE_DB,MC.BRANCH_ID,
            MC.STORE_CODE,MC.ITEM_CODE,MC.RAW_UNIT_CODE,MC.STORETRANSCODE,MC.STORETRANSEFFECT,
            MC.SEVERITY_CODE,MC.ANOMALY_SCORE,MC.INVENTORY_COST_ABS,MC.TRANSACTION_COUNT,MC.MOVEMENT_LINES,
            C.READINESS_CODE,C.CONFIDENCE_CODE,C.CANDIDATE_THRESHOLD,C.MATERIAL_SIGNAL_COUNT,C.TRIGGER_CODES,
            C.OBS_RAW_QTY_SUM,C.OBS_ABS_QTY_SUM,C.OBS_RAW_VALUE_TOTAL,C.OBS_ABS_VALUE_TOTAL,
            C.OBS_TRANSACTION_COUNT,C.OBS_MOVEMENT_LINES,C.OBS_MAX_ABS_LINE_QTY,C.DAYS_SINCE_ACTIVITY,
            C.QTY_SCORE,C.VALUE_SCORE,C.TXN_SCORE,C.LINE_SCORE,C.DORMANCY_SCORE,
            I.ITEMARNAME ITEM_NAME_AR,I.ITEMENNAME ITEM_NAME_EN,
            S.STOREARNAME STORE_NAME_AR,S.STOREENNAME STORE_NAME_EN,
            TT.STORETRANSARNAME MOVEMENT_NAME_AR,TT.STORETRANSENNAME MOVEMENT_NAME_EN,
            TT.STORETRANSEFFECT EFFECT_RAW
        FROM DASH_DIAR_MONTH_CASES MC
        LEFT JOIN DASH_DIAR_ANOM_CANDIDATES C ON C.CANDIDATE_ID=MC.CANDIDATE_ID
        LEFT JOIN MMITEMSMAINDATA I ON I.ITEMCODE=MC.ITEM_CODE
        LEFT JOIN MMSTOREMAINDATA S ON S.BRANCHID=MC.BRANCH_ID AND S.STORECODE=MC.STORE_CODE
        LEFT JOIN MMSTORETRANSTYPE TT ON TT.STORETRANSCODE=MC.STORETRANSCODE
        WHERE MC.MONTH_SNAPSHOT_ID=:snapshot_id
          AND (MC.STORE_CODE IS NULL OR MC.STORE_CODE NOT IN (56,57))
        ORDER BY CASE MC.SEVERITY_CODE WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 ELSE 3 END,
                 MC.ANOMALY_SCORE DESC,MC.INVENTORY_COST_ABS DESC,MC.MONTH_CASE_ID", [
            'snapshot_id' => $snapshotId,
        ]);
    }

    /** @return array<int,array<string,mixed>> */
    private function siteSignals(string $site, int $snapshotId): array
    {
        return Database::fetchAll(strtolower($site), "SELECT MC.MONTH_CASE_ID,MC.CANDIDATE_ID,
            S.SIGNAL_CODE,S.SIGNAL_WEIGHT,S.OBSERVED_VALUE,S.CENTER_VALUE,S.MAD_VALUE,
            S.P95_VALUE,S.P99_VALUE,S.HISTORICAL_MAX_VALUE,S.ROBUST_Z,S.COMPONENT_SCORE,S.IS_MATERIAL
        FROM DASH_DIAR_MONTH_CASES MC
        JOIN DASH_DIAR_ANOM_SIGNALS S ON S.CANDIDATE_ID=MC.CANDIDATE_ID
        WHERE MC.MONTH_SNAPSHOT_ID=:snapshot_id
          AND (MC.STORE_CODE IS NULL OR MC.STORE_CODE NOT IN (56,57))
        ORDER BY MC.MONTH_CASE_ID,S.SIGNAL_CODE", ['snapshot_id' => $snapshotId]);
    }

    /** @return array<int,array<string,mixed>> */
    private function siteDaily(string $site, int $snapshotId): array
    {
        return Database::fetchAll(strtolower($site), "SELECT
            TO_CHAR(CALENDAR_DAY,'YYYY-MM-DD') CALENDAR_DAY,DAY_STATUS,
            EVALUATED_ROWS,PROFILED_ROWS,EXCLUDED_ROWS,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]);
    }

    /** @return array<int,array<string,mixed>> */
    private function siteMovementDetails(string $site, int $snapshotId): array
    {
        return Database::fetchAll(strtolower($site), "SELECT
            MC.MONTH_CASE_ID,TO_CHAR(MC.CALENDAR_DAY,'YYYY-MM-DD') CASE_DAY,
            MC.SEVERITY_CODE,MC.ANOMALY_SCORE,MC.STORE_CODE,MC.ITEM_CODE,MC.RAW_UNIT_CODE,
            MC.STORETRANSCODE,MC.STORETRANSEFFECT,C.TRIGGER_CODES,
            T.TRANSSERIAL MOVEMENT_NUMBER,TI.SER LINE_NUMBER,
            TO_CHAR(T.TRANSSERIAL) DOCUMENT_NUMBER,
            TO_CHAR(T.TRANSDATE,'YYYY-MM-DD HH24:MI:SS') MOVEMENT_DATETIME,
            TO_CHAR(T.TRANSDATE,'YYYY-MM-DD') DOCUMENT_DATE,
            NVL(TI.USERID,T.USERID) USER_ID,
            TI.ACTQUANTITY LINE_QUANTITY,TI.REQQUANTITY REQUESTED_QUANTITY,
            TI.UNITCOSTPRICE UNIT_COST,
            NVL(TI.ACTQUANTITY,0)*NVL(TI.UNITCOSTPRICE,0) LINE_VALUE,
            TT.STORETRANSARNAME MOVEMENT_NAME_AR,TT.STORETRANSENNAME MOVEMENT_NAME_EN,
            S.STOREARNAME STORE_NAME_AR,S.STOREENNAME STORE_NAME_EN,
            I.ITEMARNAME ITEM_NAME_AR,I.ITEMENNAME ITEM_NAME_EN
        FROM DASH_DIAR_MONTH_CASES MC
        LEFT JOIN DASH_DIAR_ANOM_CANDIDATES C ON C.CANDIDATE_ID=MC.CANDIDATE_ID
        JOIN MMSTORETRANSACTIONS T
          ON T.BRANCHID=MC.BRANCH_ID
         AND T.STORECODE=MC.STORE_CODE
         AND T.STORETRANSCODE=MC.STORETRANSCODE
         AND T.TRANSDATE>=MC.CALENDAR_DAY
         AND T.TRANSDATE<MC.CALENDAR_DAY+1
        JOIN MMSTORETRANSITEMS TI
          ON TI.BRANCHID=T.BRANCHID
         AND TI.STORETRANSCODE=T.STORETRANSCODE
         AND TI.TRANSSERIAL=T.TRANSSERIAL
         AND TI.ITEMCODE=MC.ITEM_CODE
         AND ((TI.UNITCODE=MC.RAW_UNIT_CODE) OR (TI.UNITCODE IS NULL AND MC.RAW_UNIT_CODE IS NULL))
        LEFT JOIN MMSTORETRANSTYPE TT ON TT.STORETRANSCODE=T.STORETRANSCODE
        LEFT JOIN MMSTOREMAINDATA S ON S.BRANCHID=T.BRANCHID AND S.STORECODE=T.STORECODE
        LEFT JOIN MMITEMSMAINDATA I ON I.ITEMCODE=TI.ITEMCODE
        WHERE MC.MONTH_SNAPSHOT_ID=:snapshot_id
          AND (MC.STORE_CODE IS NULL OR MC.STORE_CODE NOT IN (56,57))
          AND I.ITEMSTORETYPE=2
          AND NVL(T.TRANSSTATUS,1)=1
        ORDER BY MC.MONTH_CASE_ID,T.TRANSDATE,T.TRANSSERIAL,TI.SER", ['snapshot_id' => $snapshotId]);
    }

    /** @param array<int,array<string,mixed>> $signals @return array<string,array<string,array<string,mixed>>> */
    private function signalMap(array $signals): array
    {
        $map = [];
        foreach ($signals as $row) {
            $key = strtoupper((string)($row['site_code'] ?? '')) . '|' . (string)($row['month_case_id'] ?? '');
            $code = strtoupper((string)($row['signal_code'] ?? ''));
            if ($key === '|' || $code === '') {
                continue;
            }
            $map[$key][$code] = $row;
        }
        return $map;
    }

    /** @param array<int,array<string,mixed>> $movements @return array<string,array<string,mixed>> */
    private function movementMap(array $movements): array
    {
        $map = [];
        foreach ($movements as $row) {
            $key = strtoupper((string)($row['site_code'] ?? '')) . '|' . (string)($row['month_case_id'] ?? '');
            if (!isset($map[$key])) {
                $map[$key] = ['users' => [], 'line_count' => 0, 'quantity' => 0.0, 'value' => 0.0];
            }
            $user = trim((string)($row['user_id'] ?? ''));
            if ($user !== '') {
                $map[$key]['users'][$user] = true;
            }
            $map[$key]['line_count']++;
            $map[$key]['quantity'] += abs((float)($row['line_quantity'] ?? 0));
            $map[$key]['value'] += abs((float)($row['line_value'] ?? 0));
        }
        return $map;
    }

    /** @param array<int,array<string,mixed>> $rows @param array<string,array<string,array<string,mixed>>> $signalMap @param array<string,array<string,mixed>> $movementMap @return array<int,array<string,mixed>> */
    private function decorateCases(array $rows, array $signalMap, array $movementMap, string $scope): array
    {
        $out = [];
        $rank = 0;
        foreach ($rows as $row) {
            $rank++;
            $site = strtoupper((string)($row['site_code'] ?? $row['hospital_code'] ?? ''));
            $id = (int)($row['month_case_id'] ?? 0);
            $key = $site . '|' . $id;
            $signals = (array)($signalMap[$key] ?? []);
            $movement = (array)($movementMap[$key] ?? []);
            $priority = strtoupper((string)($row['severity_code'] ?? 'MEDIUM'));
            $score = (float)($row['anomaly_score'] ?? 0);
            $threshold = (float)($row['candidate_threshold'] ?? 0);
            $storeName = $this->name($row, 'store_name');
            $itemName = $this->name($row, 'item_name');
            $movementName = $this->name($row, 'movement_name');
            $users = array_keys((array)($movement['users'] ?? []));
            sort($users);
            $reason = $this->reasonText((string)($row['trigger_codes'] ?? ''), $signals);
            $caseId = ($scope === 'ALL' ? $site . '-' : '') . 'MC-' . $id;
            $direction = $this->direction((int)($row['storetranseffect'] ?? $row['effect_raw'] ?? 0));
            $out[] = array_merge($row, [
                'rank' => $rank,
                'case_id' => $caseId,
                'priority' => $priority,
                'score' => $score,
                'alert_threshold' => $threshold,
                'amount_above_threshold' => max(0.0, $score - $threshold),
                'confidence' => strtoupper((string)($row['confidence_code'] ?? '')),
                'history_strength' => $this->historyStrength((string)($row['readiness_code'] ?? '')),
                'store_display' => trim(($scope === 'ALL' ? $site . ' · ' : '') . ($storeName !== '' ? $storeName : (string)($row['store_code'] ?? ''))),
                'item_display' => trim(($itemName !== '' ? $itemName : (string)($row['item_code'] ?? ''))),
                'unit_display' => (string)($row['raw_unit_code'] ?? ''),
                'movement_display' => $movementName !== '' ? $movementName : (string)($row['storetranscode'] ?? ''),
                'direction' => $direction,
                'quantity' => abs((float)($row['obs_abs_qty_sum'] ?? $movement['quantity'] ?? 0)),
                'case_value' => abs((float)($row['inventory_cost_abs'] ?? $movement['value'] ?? 0)),
                'transactions' => (int)($row['transaction_count'] ?? $row['obs_transaction_count'] ?? 0),
                'lines' => (int)($row['movement_lines'] ?? $row['obs_movement_lines'] ?? $movement['line_count'] ?? 0),
                'reason' => $reason,
                'review_context' => $this->reviewContext($priority, $reason),
                'recommended_action' => $this->recommendedAction($priority, $direction),
                'associated_users' => implode(', ', $users),
                'review_status' => 'Not started',
                'review_owner' => '',
                'review_result' => '',
                'management_notes' => '',
                'signals' => $signals,
            ]);
        }
        return $out;
    }

    /** @param array<int,array<string,mixed>> $rows @param array<int,array<string,mixed>> $cases @return array<int,array<string,mixed>> */
    private function decorateMovements(array $rows, array $cases, string $scope): array
    {
        $caseMap = [];
        foreach ($cases as $case) {
            $caseMap[strtoupper((string)($case['site_code'] ?? '')) . '|' . (string)($case['month_case_id'] ?? '')] = $case;
        }
        $out = [];
        foreach ($rows as $row) {
            $key = strtoupper((string)($row['site_code'] ?? '')) . '|' . (string)($row['month_case_id'] ?? '');
            $case = (array)($caseMap[$key] ?? []);
            $out[] = array_merge($row, [
                'case_id' => (string)($case['case_id'] ?? (($scope === 'ALL' ? (string)($row['site_code'] ?? '') . '-' : '') . 'MC-' . (string)($row['month_case_id'] ?? ''))),
                'priority' => (string)($case['priority'] ?? $row['severity_code'] ?? ''),
                'score' => (float)($case['score'] ?? $row['anomaly_score'] ?? 0),
                'store_display' => (string)($case['store_display'] ?? $this->name($row, 'store_name')),
                'item_display' => (string)($case['item_display'] ?? $this->name($row, 'item_name')),
                'unit_display' => (string)($row['raw_unit_code'] ?? ''),
                'movement_display' => (string)($case['movement_display'] ?? $this->name($row, 'movement_name')),
                'direction' => (string)($case['direction'] ?? $this->direction((int)($row['storetranseffect'] ?? 0))),
                'review_context' => (string)($case['review_context'] ?? ''),
                'reason' => (string)($case['reason'] ?? ''),
                'user_description' => '',
            ]);
        }
        return $out;
    }

    /** @param array<int,array<string,mixed>> $daily @param array<int,array<string,mixed>> $cases @return array<int,array<string,mixed>> */
    private function combineDaily(array $daily, array $cases): array
    {
        $map = [];
        foreach ($daily as $row) {
            $day = (string)($row['calendar_day'] ?? '');
            if ($day === '') {
                continue;
            }
            if (!isset($map[$day])) {
                $map[$day] = [
                    'date' => $day, 'movement_groups_reviewed' => 0, 'cases' => 0,
                    'critical' => 0, 'high' => 0, 'medium' => 0, 'reference_records' => 0,
                    'strong_history' => 0, 'limited_history' => 0, 'sparse_activity' => 0,
                    'no_prior_activity' => 0, 'excluded_cases' => 0, 'suppressed' => 0,
                    'inventory_count_cases' => 0, 'adjustment_cases' => 0, 'patient_dispensing' => 0,
                    'cost_value' => 0.0,
                ];
            }
            $map[$day]['movement_groups_reviewed'] += (int)($row['evaluated_rows'] ?? 0);
            $map[$day]['cases'] += (int)($row['detected_count'] ?? 0);
            $map[$day]['critical'] += (int)($row['critical_count'] ?? 0);
            $map[$day]['high'] += (int)($row['high_count'] ?? 0);
            $map[$day]['medium'] += (int)($row['medium_count'] ?? 0);
            $map[$day]['reference_records'] += (int)($row['profiled_rows'] ?? 0);
            $map[$day]['excluded_cases'] += (int)($row['excluded_rows'] ?? 0);
            $map[$day]['suppressed'] += (int)($row['suppressed_count'] ?? 0);
            $map[$day]['cost_value'] += (float)($row['inventory_cost_abs'] ?? 0);
        }
        foreach ($cases as $case) {
            $day = (string)($case['calendar_day'] ?? '');
            if (!isset($map[$day])) {
                continue;
            }
            $history = (string)($case['history_strength'] ?? '');
            if ($history === 'Strong history') $map[$day]['strong_history']++;
            elseif ($history === 'Limited history') $map[$day]['limited_history']++;
            elseif ($history === 'Sparse activity') $map[$day]['sparse_activity']++;
            else $map[$day]['no_prior_activity']++;
            $code = (int)($case['storetranscode'] ?? 0);
            $name = strtolower((string)($case['movement_display'] ?? ''));
            if ($code === 11 || strpos($name, 'inventory') !== false || strpos($name, 'count') !== false) {
                $map[$day]['inventory_count_cases']++;
            }
            if (in_array($code, [17,18], true) || strpos($name, 'adjust') !== false || strpos($name, 'recon') !== false) {
                $map[$day]['adjustment_cases']++;
            }
            if (strpos($name, 'patient') !== false || strpos($name, 'مريض') !== false) {
                $map[$day]['patient_dispensing']++;
            }
        }
        foreach ($map as &$row) {
            $reviewed = max(1, (int)$row['movement_groups_reviewed']);
            $row['case_share'] = (int)$row['cases'] / $reviewed;
            $row['operational_note'] = $this->dailyNote($row);
        }
        unset($row);
        ksort($map);
        return array_values($map);
    }

    /** @param array<int,array<string,mixed>> $cases @param array<int,array<string,mixed>> $movements @return array<int,array<string,mixed>> */
    private function storeAnalysis(array $cases, array $movements): array
    {
        $map = [];
        foreach ($cases as $case) {
            $key = strtoupper((string)($case['site_code'] ?? '')) . '|' . (string)($case['store_code'] ?? '');
            if (!isset($map[$key])) {
                $map[$key] = [
                    'site_code' => (string)($case['site_code'] ?? ''), 'store_code' => (string)($case['store_code'] ?? ''),
                    'store' => (string)($case['store_display'] ?? ''), 'cases' => 0, 'critical' => 0, 'high' => 0, 'medium' => 0,
                    'cost_value' => 0.0, 'quantity' => 0.0, 'movement_lines' => 0, 'items_map' => [], 'users_map' => [], 'movements_map' => [],
                ];
            }
            $map[$key]['cases']++;
            $priority = strtolower((string)($case['priority'] ?? 'medium'));
            if (isset($map[$key][$priority])) $map[$key][$priority]++;
            $map[$key]['cost_value'] += (float)($case['case_value'] ?? 0);
            $map[$key]['quantity'] += (float)($case['quantity'] ?? 0);
            $map[$key]['movement_lines'] += (int)($case['lines'] ?? 0);
            $map[$key]['items_map'][(string)($case['item_code'] ?? '')] = true;
            $map[$key]['movements_map'][(string)($case['movement_display'] ?? '')] = (int)($map[$key]['movements_map'][(string)($case['movement_display'] ?? '')] ?? 0) + 1;
        }
        foreach ($movements as $movement) {
            $key = strtoupper((string)($movement['site_code'] ?? '')) . '|' . (string)($movement['store_code'] ?? '');
            if (isset($map[$key]) && trim((string)($movement['user_id'] ?? '')) !== '') {
                $map[$key]['users_map'][(string)$movement['user_id']] = true;
            }
        }
        $total = max(1, count($cases));
        $rows = [];
        foreach ($map as $row) {
            arsort($row['movements_map']);
            $row['share'] = (int)$row['cases'] / $total;
            $row['items'] = count($row['items_map']);
            $row['users'] = count($row['users_map']);
            $row['dominant_movement'] = (string)(array_key_first($row['movements_map']) ?? '');
            unset($row['items_map'], $row['users_map'], $row['movements_map']);
            $rows[] = $row;
        }
        usort($rows, static function (array $a, array $b): int {
            return ((int)$b['cases'] <=> (int)$a['cases']) ?: ((float)$b['cost_value'] <=> (float)$a['cost_value']);
        });
        return $rows;
    }

    /** @param array<int,array<string,mixed>> $cases @return array<int,array<string,mixed>> */
    private function movementAnalysis(array $cases): array
    {
        $map = [];
        foreach ($cases as $case) {
            $key = strtoupper((string)($case['site_code'] ?? '')) . '|' . (string)($case['storetranscode'] ?? '') . '|' . (string)($case['direction'] ?? '');
            if (!isset($map[$key])) {
                $map[$key] = [
                    'site_code' => (string)($case['site_code'] ?? ''), 'movement_code' => (string)($case['storetranscode'] ?? ''),
                    'movement_type' => (string)($case['movement_display'] ?? ''), 'direction' => (string)($case['direction'] ?? ''),
                    'cases' => 0, 'critical' => 0, 'high' => 0, 'medium' => 0, 'cost_value' => 0.0,
                    'stores_map' => [], 'items_map' => [], 'mass_event_cases' => 0,
                ];
            }
            $map[$key]['cases']++;
            $priority = strtolower((string)($case['priority'] ?? 'medium'));
            if (isset($map[$key][$priority])) $map[$key][$priority]++;
            $map[$key]['cost_value'] += (float)($case['case_value'] ?? 0);
            $map[$key]['stores_map'][(string)($case['store_code'] ?? '')] = true;
            $map[$key]['items_map'][(string)($case['item_code'] ?? '')] = true;
            if (strpos(strtoupper((string)($case['trigger_codes'] ?? '')), 'EVENT') !== false) $map[$key]['mass_event_cases']++;
        }
        $total = max(1, count($cases));
        $rows = [];
        foreach ($map as $row) {
            $row['share'] = (int)$row['cases'] / $total;
            $row['stores'] = count($row['stores_map']);
            $row['items'] = count($row['items_map']);
            unset($row['stores_map'], $row['items_map']);
            $rows[] = $row;
        }
        usort($rows, static function (array $a, array $b): int {
            return ((int)$b['cases'] <=> (int)$a['cases']) ?: ((float)$b['cost_value'] <=> (float)$a['cost_value']);
        });
        return $rows;
    }

    /** @param array<int,array<string,mixed>> $cases @return array<int,array<string,mixed>> */
    private function itemAnalysis(array $cases): array
    {
        $map = [];
        foreach ($cases as $case) {
            $key = strtoupper((string)($case['site_code'] ?? '')) . '|' . (string)($case['item_code'] ?? '');
            if (!isset($map[$key])) {
                $map[$key] = [
                    'site_code' => (string)($case['site_code'] ?? ''), 'item_code' => (string)($case['item_code'] ?? ''),
                    'item' => (string)($case['item_display'] ?? ''), 'cases' => 0, 'critical' => 0, 'high' => 0, 'medium' => 0,
                    'stores_map' => [], 'movements_map' => [], 'quantity' => 0.0, 'cost_value' => 0.0, 'highest_score' => 0.0,
                    'first_day' => (string)($case['calendar_day'] ?? ''), 'last_day' => (string)($case['calendar_day'] ?? ''),
                ];
            }
            $map[$key]['cases']++;
            $priority = strtolower((string)($case['priority'] ?? 'medium'));
            if (isset($map[$key][$priority])) $map[$key][$priority]++;
            $map[$key]['stores_map'][(string)($case['store_code'] ?? '')] = true;
            $map[$key]['movements_map'][(string)($case['storetranscode'] ?? '')] = true;
            $map[$key]['quantity'] += (float)($case['quantity'] ?? 0);
            $map[$key]['cost_value'] += (float)($case['case_value'] ?? 0);
            $map[$key]['highest_score'] = max((float)$map[$key]['highest_score'], (float)($case['score'] ?? 0));
            $day = (string)($case['calendar_day'] ?? '');
            if ($day !== '') {
                $map[$key]['first_day'] = min((string)$map[$key]['first_day'], $day);
                $map[$key]['last_day'] = max((string)$map[$key]['last_day'], $day);
            }
        }
        $rows = [];
        foreach ($map as $row) {
            $row['stores'] = count($row['stores_map']);
            $row['movement_types'] = count($row['movements_map']);
            unset($row['stores_map'], $row['movements_map']);
            $rows[] = $row;
        }
        usort($rows, static function (array $a, array $b): int {
            return ((int)$b['cases'] <=> (int)$a['cases']) ?: ((float)$b['cost_value'] <=> (float)$a['cost_value']);
        });
        return $rows;
    }

    /** @param array<int,array<string,mixed>> $movements @param array<int,array<string,mixed>> $cases @return array<int,array<string,mixed>> */
    private function userAnalysis(array $movements, array $cases): array
    {
        $caseMap = [];
        foreach ($cases as $case) {
            $caseMap[strtoupper((string)($case['site_code'] ?? '')) . '|' . (string)($case['month_case_id'] ?? '')] = $case;
        }
        $map = [];
        foreach ($movements as $row) {
            $user = trim((string)($row['user_id'] ?? ''));
            if ($user === '') continue;
            $site = strtoupper((string)($row['site_code'] ?? ''));
            $key = $site . '|' . $user;
            $caseKey = $site . '|' . (string)($row['month_case_id'] ?? '');
            $case = (array)($caseMap[$caseKey] ?? []);
            if (!isset($map[$key])) {
                $map[$key] = [
                    'site_code' => $site, 'user' => $user, 'cases_map' => [], 'critical_map' => [], 'high_map' => [], 'medium_map' => [],
                    'movement_lines' => 0, 'line_value' => 0.0, 'stores_map' => [], 'items_map' => [],
                    'first_day' => (string)($row['document_date'] ?? ''), 'last_day' => (string)($row['document_date'] ?? ''),
                ];
            }
            $map[$key]['cases_map'][$caseKey] = true;
            $priority = strtolower((string)($case['priority'] ?? 'medium'));
            if (isset($map[$key][$priority . '_map'])) $map[$key][$priority . '_map'][$caseKey] = true;
            $map[$key]['movement_lines']++;
            $map[$key]['line_value'] += abs((float)($row['line_value'] ?? 0));
            $map[$key]['stores_map'][(string)($row['store_code'] ?? '')] = true;
            $map[$key]['items_map'][(string)($row['item_code'] ?? '')] = true;
            $day = (string)($row['document_date'] ?? '');
            if ($day !== '') {
                $map[$key]['first_day'] = min((string)$map[$key]['first_day'], $day);
                $map[$key]['last_day'] = max((string)$map[$key]['last_day'], $day);
            }
        }
        $rows = [];
        foreach ($map as $row) {
            $row['unique_cases'] = count($row['cases_map']);
            $row['critical'] = count($row['critical_map']);
            $row['high'] = count($row['high_map']);
            $row['medium'] = count($row['medium_map']);
            $row['stores'] = count($row['stores_map']);
            $row['items'] = count($row['items_map']);
            unset($row['cases_map'], $row['critical_map'], $row['high_map'], $row['medium_map'], $row['stores_map'], $row['items_map']);
            $rows[] = $row;
        }
        usort($rows, static function (array $a, array $b): int {
            return ((int)$b['unique_cases'] <=> (int)$a['unique_cases']) ?: ((float)$b['line_value'] <=> (float)$a['line_value']);
        });
        return $rows;
    }

    /** @param array<int,array<string,mixed>> $cases @param array<string,array<string,array<string,mixed>>> $signalMap @return array<int,array<string,mixed>> */
    private function statisticalComparisons(array $cases, array $signalMap): array
    {
        $out = [];
        foreach ($cases as $case) {
            $key = strtoupper((string)($case['site_code'] ?? '')) . '|' . (string)($case['month_case_id'] ?? '');
            $signals = (array)($signalMap[$key] ?? []);
            $qty = (array)($signals['QTY_MAGNITUDE'] ?? []);
            $value = (array)($signals['VALUE_MAGNITUDE'] ?? []);
            $txn = (array)($signals['TXN_FREQUENCY'] ?? []);
            $line = (array)($signals['LINE_SIZE'] ?? []);
            $dormancy = (array)($signals['DORMANCY_REACTIVATION'] ?? []);
            $out[] = [
                'case_id' => (string)($case['case_id'] ?? ''), 'date' => (string)($case['calendar_day'] ?? ''),
                'priority' => (string)($case['priority'] ?? ''), 'score' => (float)($case['score'] ?? 0),
                'store' => (string)($case['store_display'] ?? ''), 'item' => (string)($case['item_display'] ?? ''),
                'movement_type' => (string)($case['movement_display'] ?? ''), 'history_strength' => (string)($case['history_strength'] ?? ''),
                'qty_obs' => $this->metric($qty, 'observed_value', $case['quantity'] ?? 0), 'qty_typical' => $this->metric($qty, 'center_value'),
                'qty_p95' => $this->metric($qty, 'p95_value'), 'qty_p99' => $this->metric($qty, 'p99_value'),
                'qty_max' => $this->metric($qty, 'historical_max_value'), 'qty_contribution' => $this->metric($qty, 'component_score'),
                'value_obs' => $this->metric($value, 'observed_value', $case['case_value'] ?? 0), 'value_typical' => $this->metric($value, 'center_value'),
                'value_p95' => $this->metric($value, 'p95_value'), 'value_p99' => $this->metric($value, 'p99_value'),
                'value_max' => $this->metric($value, 'historical_max_value'), 'value_contribution' => $this->metric($value, 'component_score'),
                'txn_obs' => $this->metric($txn, 'observed_value', $case['transactions'] ?? 0), 'txn_typical' => $this->metric($txn, 'center_value'),
                'txn_p95' => $this->metric($txn, 'p95_value'), 'txn_p99' => $this->metric($txn, 'p99_value'),
                'txn_max' => $this->metric($txn, 'historical_max_value'), 'txn_contribution' => $this->metric($txn, 'component_score'),
                'line_obs' => $this->metric($line, 'observed_value', $case['obs_max_abs_line_qty'] ?? 0), 'line_typical' => $this->metric($line, 'center_value'),
                'line_p95' => $this->metric($line, 'p95_value'), 'line_p99' => $this->metric($line, 'p99_value'),
                'line_max' => $this->metric($line, 'historical_max_value'), 'line_contribution' => $this->metric($line, 'component_score'),
                'days_since_activity' => $this->metric($dormancy, 'observed_value', $case['days_since_activity'] ?? 0),
                'dormancy_contribution' => $this->metric($dormancy, 'component_score'),
            ];
        }
        return $out;
    }

    /** @param array<int,array<string,mixed>> $cases @param array<string,array<string,array<string,mixed>>> $signalMap @param array<int,array<string,mixed>> $daily @param array<string,mixed> $summary @return array<string,mixed> */
    private function statisticalBasis(array $cases, array $signalMap, array $daily, array $summary): array
    {
        $indicators = [
            'QTY_MAGNITUDE' => ['label' => 'Quantity', 'meaning' => 'Observed quantity compared with the typical quantity over the preceding year.'],
            'VALUE_MAGNITUDE' => ['label' => 'Cost Value', 'meaning' => 'Observed inventory-cost value compared with prior history.'],
            'TXN_FREQUENCY' => ['label' => 'Transaction Count', 'meaning' => 'Number of daily movements compared with the typical count.'],
            'LINE_SIZE' => ['label' => 'Largest Movement', 'meaning' => 'Largest individual movement compared with the normal line size.'],
            'DORMANCY_REACTIVATION' => ['label' => 'Resumed Activity', 'meaning' => 'Time elapsed since the last similar activity.'],
        ];
        $rows = [];
        foreach ($indicators as $code => $definition) {
            $p95 = 0; $p99 = 0; $max = 0;
            foreach ($signalMap as $caseSignals) {
                if (!isset($caseSignals[$code])) continue;
                $signal = (array)$caseSignals[$code];
                $observed = (float)($signal['observed_value'] ?? 0);
                $p95v = $signal['p95_value'] === null ? null : (float)$signal['p95_value'];
                $p99v = $signal['p99_value'] === null ? null : (float)$signal['p99_value'];
                $maxv = $signal['historical_max_value'] === null ? null : (float)$signal['historical_max_value'];
                if ($p95v !== null && $observed > $p95v) $p95++;
                if ($p99v !== null && $observed > $p99v) $p99++;
                if ($maxv !== null && $observed > $maxv) $max++;
            }
            $rows[] = [
                'indicator' => $definition['label'], 'meaning' => $definition['meaning'],
                'above_p95' => $p95, 'above_p99' => $p99, 'above_max' => $max,
                'interpretation' => $this->indicatorInterpretation($code),
            ];
        }
        $history = ['Strong history' => 0, 'Limited history' => 0, 'Sparse activity' => 0, 'No prior activity' => 0];
        $priority = ['CRITICAL' => ['cases' => 0, 'cost' => 0.0], 'HIGH' => ['cases' => 0, 'cost' => 0.0], 'MEDIUM' => ['cases' => 0, 'cost' => 0.0]];
        foreach ($cases as $case) {
            $h = (string)($case['history_strength'] ?? 'No prior activity');
            if (isset($history[$h])) $history[$h]++;
            $p = strtoupper((string)($case['priority'] ?? 'MEDIUM'));
            if (isset($priority[$p])) {
                $priority[$p]['cases']++;
                $priority[$p]['cost'] += (float)($case['case_value'] ?? 0);
            }
        }
        return [
            'baseline_days' => 365,
            'movement_groups_reviewed' => array_sum(array_map(static function (array $r): int { return (int)($r['evaluated_rows'] ?? 0); }, $daily)),
            'cases' => count($cases),
            'completed_days' => count($daily),
            'indicators' => $rows,
            'history' => $history,
            'priority' => $priority,
            'total_cost' => (float)($summary['inventory_cost_abs'] ?? 0),
        ];
    }

    /** @param array<string,mixed> $report */
    private function enrichSummary(array &$report): void
    {
        $summary = (array)$report['summary'];
        $summary['case_count'] = count((array)$report['cases']);
        $summary['critical_count'] = count((array)$report['critical_cases']);
        $summary['store_count'] = count((array)$report['store_analysis']);
        $summary['item_count'] = count((array)$report['item_analysis']);
        $summary['user_count'] = count((array)$report['user_associations']);
        $summary['movement_type_count'] = count((array)$report['movement_analysis']);
        $summary['completed_days'] = count((array)$report['daily_distribution']);
        $summary['movement_groups_reviewed'] = array_sum(array_map(static function (array $row): int {
            return (int)($row['movement_groups_reviewed'] ?? 0);
        }, (array)$report['daily_distribution']));
        $summary['case_share'] = $summary['movement_groups_reviewed'] > 0 ? $summary['case_count'] / $summary['movement_groups_reviewed'] : 0.0;
        $report['summary'] = $summary;
    }

    /** @param array<string,mixed> $report @return array<int,string> */
    private function insights(array $report): array
    {
        $summary = (array)($report['summary'] ?? []);
        $cases = max(1, (int)($summary['case_count'] ?? 0));
        $critical = (int)($summary['critical_count'] ?? 0);
        $topStore = (array)($report['top_stores'][0] ?? []);
        $topMovement = (array)($report['top_movements'][0] ?? []);
        $topDay = $this->topDay((array)($report['daily_distribution'] ?? []));
        return [
            number_format((float)($summary['case_share'] ?? 0) * 100, 2) . '% of reviewed movement groups produced a frozen review case.',
            $critical . ' critical cases represent ' . number_format(($critical / $cases) * 100, 1) . '% of the review population.',
            ($topStore !== [] ? (string)($topStore['store'] ?? 'The leading store') . ' contains ' . (int)($topStore['cases'] ?? 0) . ' cases and should be reviewed first.' : 'No store concentration was available.'),
            ($topMovement !== [] ? (string)($topMovement['movement_type'] ?? 'The leading movement type') . ' is the movement type most associated with cases.' : 'No movement-type concentration was available.'),
            ($topDay !== [] ? (string)($topDay['date'] ?? '') . ' was the peak review day with ' . (int)($topDay['cases'] ?? 0) . ' cases.' : 'No daily peak was available.'),
            'The associated value of SAR ' . number_format((float)($summary['inventory_cost_abs'] ?? 0), 2) . ' is inventory-cost scale, not confirmed financial loss.',
        ];
    }

    /** @param array<string,mixed> $report @return array<int,string> */
    private function recommendations(array $report): array
    {
        $topStore = (array)($report['top_stores'][0] ?? []);
        $topMovement = (array)($report['top_movements'][0] ?? []);
        return [
            'Open critical cases first and match source documents, approvals, balances and associated users.',
            'Review high-volume inventory-count or adjustment activity as one operational event before separating individual exceptions.',
            'Begin with ' . ((string)($topStore['store'] ?? '') !== '' ? (string)$topStore['store'] : 'the leading store') . ' because it has the highest case concentration.',
            'Focus on ' . ((string)($topMovement['movement_type'] ?? '') !== '' ? (string)$topMovement['movement_type'] : 'the leading movement type') . ' and compare requests, issues and subsequent returns.',
            'Record the decision, owner, result and management notes in the All Cases sheet to preserve the follow-up trail.',
        ];
    }

    /** @param array<string,mixed> $siteCards @return array<int,array<string,mixed>> */
    private function snapshotLibrary(array $siteCards): array
    {
        $rows = [];
        foreach ($siteCards as $key => $card) {
            if (!is_array($card) || strtoupper((string)($card['status_code'] ?? '')) !== 'AVAILABLE') continue;
            $header = (array)($card['header'] ?? []);
            $rows[] = [
                'site_code' => strtoupper((string)($card['site_code'] ?? $key)),
                'source_db' => (string)($card['source_db'] ?? $header['source_db'] ?? ''),
                'snapshot_id' => (int)($header['month_snapshot_id'] ?? 0),
                'version_no' => (int)($header['version_no'] ?? 0),
                'status' => (string)($header['status_code'] ?? ''),
                'month' => (string)($header['month_key'] ?? ''),
                'cases' => (int)($header['detected_count'] ?? 0),
                'critical' => (int)($header['critical_count'] ?? 0),
                'high' => (int)($header['high_count'] ?? 0),
                'medium' => (int)($header['medium_count'] ?? 0),
                'associated_cost' => (float)($header['inventory_cost_abs'] ?? 0),
                'transactions' => (int)($header['candidate_txn_count'] ?? 0),
                'snapshot_hash' => (string)($header['snapshot_hash'] ?? ''),
                'updated_through' => (string)($header['data_updated_through'] ?? ''),
            ];
        }
        return $rows;
    }

    /** @param array<string,mixed> $row */
    private function name(array $row, string $prefix): string
    {
        $en = trim((string)($row[$prefix . '_en'] ?? ''));
        $ar = trim((string)($row[$prefix . '_ar'] ?? ''));
        return $en !== '' ? $en : $ar;
    }

    private function direction(int $effect): string
    {
        if ($effect === 1) return 'IN';
        if ($effect === 2) return 'OUT';
        if ($effect === 0) return 'NEUTRAL';
        return 'UNKNOWN';
    }

    private function historyStrength(string $readiness): string
    {
        $readiness = strtoupper(trim($readiness));
        if (in_array($readiness, ['READY', 'STRONG', 'FULL'], true)) return 'Strong history';
        if (in_array($readiness, ['LIMITED', 'RECENT'], true)) return 'Limited history';
        if (in_array($readiness, ['SPARSE', 'LOW_ACTIVITY'], true)) return 'Sparse activity';
        return 'No prior activity';
    }

    /** @param array<string,array<string,mixed>> $signals */
    private function reasonText(string $triggerCodes, array $signals): string
    {
        $labels = [
            'QTY_MAGNITUDE' => 'unusual quantity', 'VALUE_MAGNITUDE' => 'high inventory-cost value',
            'TXN_FREQUENCY' => 'unusual transaction frequency', 'LINE_SIZE' => 'unusual movement-line size',
            'DORMANCY_REACTIVATION' => 'activity resumed after inactivity',
        ];
        $codes = array_filter(array_map('trim', explode(',', strtoupper($triggerCodes))));
        foreach (array_keys($signals) as $code) {
            if (!in_array($code, $codes, true) && (int)($signals[$code]['is_material'] ?? 0) === 1) $codes[] = $code;
        }
        $text = [];
        foreach ($codes as $code) {
            if (isset($labels[$code])) $text[] = $labels[$code];
        }
        return $text === [] ? 'Multiple statistical review signals' : ucfirst(implode(', ', array_unique($text)));
    }

    private function reviewContext(string $priority, string $reason): string
    {
        if ($priority === 'CRITICAL') return 'Urgent review: ' . $reason . '.';
        if ($priority === 'HIGH') return 'Timely review: ' . $reason . '.';
        return 'Controlled monitoring: ' . $reason . '.';
    }

    private function recommendedAction(string $priority, string $direction): string
    {
        if ($priority === 'CRITICAL') return 'Verify supporting documents, approvals, balance impact and associated users within 24 hours.';
        if ($direction === 'OUT') return 'Match the issue/dispensing request with authorisation and any subsequent return.';
        if ($direction === 'IN') return 'Verify receipt/return source, valuation and resulting store balance.';
        return 'Review the movement context and document the management decision.';
    }

    /** @param array<string,mixed> $row */
    private function dailyNote(array $row): string
    {
        if ((int)($row['inventory_count_cases'] ?? 0) + (int)($row['adjustment_cases'] ?? 0) >= 8) {
            return 'Review inventory-count and adjustment activity as one operational event.';
        }
        if ((int)($row['critical'] ?? 0) > 0) return 'Critical cases are present; begin with document and approval verification.';
        if ((int)($row['cases'] ?? 0) === 0) return 'No cases required review.';
        return 'Review the leading stores and movement types for this day.';
    }

    private function indicatorInterpretation(string $code): string
    {
        $map = [
            'QTY_MAGNITUDE' => 'The further quantity rises above prior behaviour, the higher the review priority.',
            'VALUE_MAGNITUDE' => 'Directs attention to greater financial scale without treating the amount as confirmed loss.',
            'TXN_FREQUENCY' => 'Identifies unusual repetition even when individual movements are small.',
            'LINE_SIZE' => 'Highlights unusually large individual movements.',
            'DORMANCY_REACTIVATION' => 'Activity after a long gap increases the need to verify the reason and documents.',
        ];
        return (string)($map[$code] ?? 'Requires management interpretation with source evidence.');
    }

    /** @param array<string,mixed> $row */
    private function metric(array $row, string $key, $fallback = null)
    {
        if (array_key_exists($key, $row) && $row[$key] !== null && $row[$key] !== '') return (float)$row[$key];
        return $fallback === null || $fallback === '' ? null : (float)$fallback;
    }

    /** @param array<int,array<string,mixed>> $daily @return array<string,mixed> */
    private function topDay(array $daily): array
    {
        if ($daily === []) return [];
        usort($daily, static function (array $a, array $b): int {
            return ((int)($b['cases'] ?? 0) <=> (int)($a['cases'] ?? 0)) ?: ((float)($b['cost_value'] ?? 0) <=> (float)($a['cost_value'] ?? 0));
        });
        return (array)($daily[0] ?? []);
    }

    private function safeError(Throwable $e): string
    {
        $message = strtoupper(preg_replace('/[^A-Z0-9_]+/i', '_', $e->getMessage()) ?: 'READ_FAILED');
        return substr($message, 0, 180);
    }
}
