<?php

declare(strict_types=1);

namespace App\Repositories;

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

final class DiarInvestigationRepository
{
    /** @var string */
    private $controlBranch = 'njch';

    /** @var bool|null */
    private $workflowSchemaReadyCache = null;

    /**
     * Confirms that the additive DIAR-6B.4 workflow objects are available.
     * The result is cached for the current request so pre-migration pages retain
     * the legacy read-only queue instead of failing with ORA-00904/ORA-00942.
     */
    public function workflowSchemaReady(): bool
    {
        if ($this->workflowSchemaReadyCache !== null) {
            return $this->workflowSchemaReadyCache;
        }
        try {
            $row = Database::fetchOne($this->controlBranch, "SELECT
                (SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME='DASH_DIAR_CASES' AND COLUMN_NAME='WORKFLOW_STAGE_CODE') stage_column,
                (SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME IN (
                    'DASH_DIAR_CASE_INFO_REQUESTS','DASH_DIAR_CASE_FINDINGS',
                    'DASH_DIAR_CASE_APPROVALS','DASH_DIAR_CORRECTIVE_ACTIONS'
                )) workflow_tables
            FROM dual");
            $this->workflowSchemaReadyCache = (int)($row['stage_column'] ?? 0) === 1
                && (int)($row['workflow_tables'] ?? 0) === 4;
        } catch (Throwable $error) {
            $this->workflowSchemaReadyCache = false;
        }
        return $this->workflowSchemaReadyCache;
    }

    /** @return array<string,mixed> */
    public function summary(array $allowedSites = [], array $accessScope = []): array
    {
        [$scopeSql, $params] = $this->siteScope($allowedSites, 'c');
        [$accessSql, $accessParams] = $this->caseAccessScope($accessScope, 'a');
        $scopeSql .= $accessSql;
        $params = array_merge($params, $accessParams);
        $ready = $this->workflowSchemaReady();
        $stageExpr = $ready
            ? "c.WORKFLOW_STAGE_CODE"
            : "CASE WHEN c.STATUS_CODE='CLOSED' THEN 'CLOSED' WHEN c.STATUS_CODE='ESCALATED' THEN 'SUPERVISOR_REVIEW' WHEN c.STATUS_CODE='EVIDENCE_REQUESTED' THEN 'INFORMATION_REQUIRED' WHEN c.STATUS_CODE='UNDER_REVIEW' THEN 'UNDER_REVIEW' WHEN a.ASSIGNMENT_ID IS NOT NULL THEN 'ASSIGNED' ELSE 'NEW' END";
        $row = Database::fetchOne($this->controlBranch, "SELECT
            COUNT(*) total_cases,
            SUM(CASE WHEN " . $stageExpr . "='NEW' THEN 1 ELSE 0 END) new_cases,
            SUM(CASE WHEN " . $stageExpr . "='ASSIGNED' THEN 1 ELSE 0 END) assigned_cases,
            SUM(CASE WHEN " . $stageExpr . " IN ('UNDER_REVIEW','INFORMATION_REQUIRED','FINDING_PROPOSED','SUPERVISOR_REVIEW','MANAGEMENT_APPROVAL','CORRECTIVE_ACTION') THEN 1 ELSE 0 END) active_cases,
            SUM(CASE WHEN " . $stageExpr . "='SUPERVISOR_REVIEW' THEN 1 ELSE 0 END) supervisor_review_cases,
            SUM(CASE WHEN " . $stageExpr . "='MANAGEMENT_APPROVAL' THEN 1 ELSE 0 END) management_approval_cases,
            SUM(CASE WHEN " . $stageExpr . "='CORRECTIVE_ACTION' THEN 1 ELSE 0 END) corrective_action_cases,
            SUM(CASE WHEN " . $stageExpr . "='CLOSED' THEN 1 ELSE 0 END) closed_cases,
            SUM(CASE WHEN c.SEVERITY_CODE='CRITICAL' AND " . $stageExpr . "<>'CLOSED' THEN 1 ELSE 0 END) critical_open,
            SUM(CASE WHEN c.SEVERITY_CODE='HIGH' AND " . $stageExpr . "<>'CLOSED' THEN 1 ELSE 0 END) high_open,
            SUM(CASE WHEN c.ENTITY_TYPE='EVENT' THEN 1 ELSE 0 END) event_cases,
            SUM(CASE WHEN a.ASSIGNMENT_ID IS NULL AND " . $stageExpr . "<>'CLOSED' THEN 1 ELSE 0 END) unassigned_open,
            SUM(CASE WHEN c.DUE_AT IS NOT NULL AND c.DUE_AT<SYSDATE AND " . $stageExpr . "<>'CLOSED' THEN 1 ELSE 0 END) overdue_cases,
            SUM(CASE WHEN c.DUE_AT IS NOT NULL AND c.DUE_AT>=SYSDATE AND c.DUE_AT<SYSDATE+1 AND " . $stageExpr . "<>'CLOSED' THEN 1 ELSE 0 END) due_soon_cases
        FROM DASH_DIAR_CASES c
        LEFT JOIN DASH_DIAR_CASE_ASSIGNMENTS a ON a.CASE_ID=c.CASE_ID AND a.STATUS_CODE='ACTIVE'
        WHERE 1=1 " . $scopeSql, $params);
        $out = is_array($row) ? $row : [];
        $out['workflow_schema_ready'] = $ready ? 1 : 0;
        return $out;
    }

    /**
     * @return array{rows:array<int,array<string,mixed>>,total:int,page:int,per_page:int}
     */
    public function queue(array $filters, array $allowedSites, int $page, int $perPage, array $accessScope = []): array
    {
        [$where, $params] = $this->buildFilters($filters, $allowedSites);
        [$accessSql, $accessParams] = $this->caseAccessScope($accessScope, 'a');
        $where .= $accessSql;
        $params = array_merge($params, $accessParams);
        $ready = $this->workflowSchemaReady();
        $stageExpr = $ready
            ? "c.WORKFLOW_STAGE_CODE"
            : "CASE WHEN c.STATUS_CODE='CLOSED' THEN 'CLOSED' WHEN c.STATUS_CODE='ESCALATED' THEN 'SUPERVISOR_REVIEW' WHEN c.STATUS_CODE='EVIDENCE_REQUESTED' THEN 'INFORMATION_REQUIRED' WHEN c.STATUS_CODE='UNDER_REVIEW' THEN 'UNDER_REVIEW' WHEN a.ASSIGNMENT_ID IS NOT NULL THEN 'ASSIGNED' ELSE 'NEW' END";
        $count = Database::fetchOne($this->controlBranch, "SELECT COUNT(*) total_rows
            FROM DASH_DIAR_CASES c
            LEFT JOIN DASH_DIAR_CASE_ASSIGNMENTS a ON a.CASE_ID=c.CASE_ID AND a.STATUS_CODE='ACTIVE'
            WHERE 1=1 " . $where, $params);
        $total = (int)($count['total_rows'] ?? 0);
        $page = max(1, $page);
        $perPage = max(20, min(100, $perPage));
        $start = (($page - 1) * $perPage) + 1;
        $end = min($total, $start + $perPage - 1);
        if ($total === 0) {
            return ['rows' => [], 'total' => 0, 'page' => 1, 'per_page' => $perPage];
        }

        $params['row_start'] = $start;
        $params['row_end'] = $end;
        $workflowColumns = $ready
            ? "c.WORKFLOW_STAGE_CODE,c.SUPERVISOR_USERNAME,c.MANAGEMENT_APPROVER,c.REOPEN_COUNT,c.LAST_ACTION_AT,"
            : $stageExpr . " workflow_stage_code,CAST(NULL AS VARCHAR2(128)) supervisor_username,CAST(NULL AS VARCHAR2(128)) management_approver,0 reopen_count,c.UPDATED_AT last_action_at,";
        $sql = "SELECT * FROM (
            SELECT q.*, ROW_NUMBER() OVER (
                ORDER BY
                    CASE WHEN q.DUE_AT IS NOT NULL AND q.DUE_AT<SYSDATE AND q.WORKFLOW_STAGE_CODE<>'CLOSED' THEN 0 ELSE 1 END,
                    CASE q.SEVERITY_CODE WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 2 WHEN 'MEDIUM' THEN 3 WHEN 'LOW' THEN 4 ELSE 5 END,
                    CASE q.WORKFLOW_STAGE_CODE WHEN 'MANAGEMENT_APPROVAL' THEN 1 WHEN 'SUPERVISOR_REVIEW' THEN 2 WHEN 'INFORMATION_REQUIRED' THEN 3 WHEN 'NEW' THEN 4 ELSE 5 END,
                    q.CALENDAR_DAY DESC, q.CASE_ID DESC
            ) rn
            FROM (
                SELECT c.CASE_ID,c.CASE_CODE,c.ENTITY_TYPE,c.SITE_CODE,c.STORE_CODE,
                    c.STORE_NAME_AR,c.STORE_NAME_EN,c.CALENDAR_DAY,c.ITEM_CODE,
                    c.ITEM_NAME_AR,c.ITEM_NAME_EN,c.RISK_SCORE,c.SEVERITY_CODE,
                    c.STATUS_CODE," . $workflowColumns . "c.DUE_AT,c.EVENT_TYPE,c.EVENT_FAMILY,c.MEMBER_COUNT,
                    c.SUPPRESSED_MEMBER_COUNT,c.OUTLIER_COUNT,c.REVIEW_REASON,
                    c.UPDATED_AT,a.ASSIGNED_TO,a.ASSIGNED_AT,
                    CASE
                        WHEN " . $stageExpr . "='CLOSED' THEN 'COMPLETED'
                        WHEN c.DUE_AT IS NULL THEN 'NO_DUE_DATE'
                        WHEN c.DUE_AT<SYSDATE THEN 'OVERDUE'
                        WHEN c.DUE_AT<SYSDATE+1 THEN 'DUE_SOON'
                        ELSE 'ON_TRACK'
                    END SLA_STATUS_CODE
                FROM DASH_DIAR_CASES c
                LEFT JOIN DASH_DIAR_CASE_ASSIGNMENTS a
                  ON a.CASE_ID=c.CASE_ID AND a.STATUS_CODE='ACTIVE'
                WHERE 1=1 " . $where . "
            ) q
        ) WHERE rn BETWEEN :row_start AND :row_end";
        $rows = Database::fetchAll($this->controlBranch, $sql, $params);
        return ['rows' => $rows, 'total' => $total, 'page' => $page, 'per_page' => $perPage];
    }

    /** @return array<string,mixed>|null */
    public function find(string $caseCode, array $allowedSites, array $accessScope = [])
    {
        [$scopeSql, $params] = $this->siteScope($allowedSites, 'c');
        [$accessSql, $accessParams] = $this->caseAccessScope($accessScope, 'a');
        $scopeSql .= $accessSql;
        $params = array_merge($params, $accessParams);
        $params['case_code'] = $caseCode;
        return Database::fetchOne($this->controlBranch, "SELECT c.*,
            a.ASSIGNMENT_ID active_assignment_id,a.ASSIGNED_TO,a.ASSIGNED_BY,a.ASSIGNED_AT,
            p.CASE_CODE parent_event_code,p.EVENT_TYPE parent_event_type,
            rv.VERSION_NO rule_version_no
        FROM DASH_DIAR_CASES c
        LEFT JOIN DASH_DIAR_CASE_ASSIGNMENTS a ON a.CASE_ID=c.CASE_ID AND a.STATUS_CODE='ACTIVE'
        LEFT JOIN DASH_DIAR_CASES p ON p.CASE_ID=c.PARENT_EVENT_CASE_ID
        LEFT JOIN DASH_DIAR_RULE_VERSIONS rv ON rv.VERSION_ID=c.RULE_VERSION_ID
        WHERE c.CASE_CODE=:case_code " . $scopeSql, $params);
    }

    /** @return array<string,mixed> */
    public function detail(string $caseCode, array $allowedSites, array $accessScope = []): array
    {
        $case = $this->find($caseCode, $allowedSites, $accessScope);
        if (!$case) {
            throw new RuntimeException('CASE_NOT_FOUND_OR_NOT_ALLOWED');
        }
        $caseId = (int)$case['case_id'];
        $source = null;
        if (!empty($case['source_case_id'])) {
            $source = Database::fetchOne($this->controlBranch, "SELECT * FROM DASH_DIAR_SOURCE_CASES WHERE SOURCE_CASE_ID=:id", [
                'id' => (int)$case['source_case_id'],
            ]);
        }

        $eventMembers = [];
        if ((string)$case['entity_type'] === 'EVENT') {
            $eventMembers = Database::fetchAll($this->controlBranch, "SELECT
                SOURCE_CASE_CODE,ITEM_CODE,ITEM_NAME_AR,ITEM_NAME_EN,RAW_UNIT_CODE,
                RISK_SCORE,SEVERITY_CODE,SIGNALS,MEMBER_DISPOSITION,
                OUTBOUND_RAW_QTY,NET_RAW_QTY,RECON_ADD_QTY,RECON_SUB_QTY,
                INVENTORY_EVENTS,CORRELATION_REASON
            FROM DASH_DIAR_SOURCE_CASES
            WHERE EVENT_CODE=:event_code
            ORDER BY
                CASE MEMBER_DISPOSITION WHEN 'OUTLIER_WITHIN_BATCH' THEN 1 WHEN 'SUPPRESSED_BATCH_MEMBER' THEN 2 ELSE 3 END,
                RISK_SCORE DESC, ITEM_CODE", ['event_code' => $caseCode]);
        }

        foreach ($eventMembers as &$eventMember) {
            $rawSignals = (string)($eventMember['signals'] ?? '');
            $eventMember['signal_labels_en'] = implode(', ', DiarSignalExplainer::labels($rawSignals, 'en'));
            $eventMember['signal_labels_ar'] = implode('، ', DiarSignalExplainer::labels($rawSignals, 'ar'));
        }
        unset($eventMember);

        $sourceReferenceResult = $this->sourceMovementReferences($case, is_array($source) ? $source : []);
        $sourceTransactions = is_array($sourceReferenceResult['rows'] ?? null) ? $sourceReferenceResult['rows'] : [];
        $signalExplanations = DiarSignalExplainer::explain(is_array($source) ? $source : [], $sourceTransactions);

        $workflowReady = $this->workflowSchemaReady();
        $informationRequests = [];
        $findings = [];
        $approvals = [];
        $correctiveActions = [];
        if ($workflowReady) {
            $informationRequests = Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_INFO_REQUESTS WHERE CASE_ID=:id ORDER BY REQUESTED_AT DESC,REQUEST_ID DESC", ['id' => $caseId]);
            $findings = Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_FINDINGS WHERE CASE_ID=:id ORDER BY PROPOSED_AT DESC,FINDING_ID DESC", ['id' => $caseId]);
            $approvals = Database::fetchAll($this->controlBranch, "SELECT a.*,f.OUTCOME_CODE,f.SUMMARY_TEXT FROM DASH_DIAR_CASE_APPROVALS a LEFT JOIN DASH_DIAR_CASE_FINDINGS f ON f.FINDING_ID=a.FINDING_ID WHERE a.CASE_ID=:id ORDER BY a.DECIDED_AT DESC,a.APPROVAL_ID DESC", ['id' => $caseId]);
            $correctiveActions = Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CORRECTIVE_ACTIONS WHERE CASE_ID=:id ORDER BY CREATED_AT DESC,ACTION_ID DESC", ['id' => $caseId]);
        }

        return [
            'case' => $case,
            'source' => $source,
            'event_members' => $eventMembers,
            'source_transactions' => $sourceTransactions,
            'source_transaction_total' => (int)($sourceReferenceResult['total'] ?? count($sourceTransactions)),
            'source_transaction_limit' => (int)($sourceReferenceResult['limit'] ?? 500),
            'source_transaction_error' => (string)($sourceReferenceResult['error'] ?? ''),
            'signal_explanations' => $signalExplanations,
            'history' => Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_HISTORY WHERE CASE_ID=:id ORDER BY PERFORMED_AT DESC,HISTORY_ID DESC", ['id' => $caseId]),
            'notes' => Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_NOTES WHERE CASE_ID=:id ORDER BY CREATED_AT DESC,NOTE_ID DESC", ['id' => $caseId]),
            'evidence' => Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_EVIDENCE WHERE CASE_ID=:id ORDER BY ADDED_AT DESC,EVIDENCE_ID DESC", ['id' => $caseId]),
            'assignments' => Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_ASSIGNMENTS WHERE CASE_ID=:id ORDER BY ASSIGNED_AT DESC,ASSIGNMENT_ID DESC", ['id' => $caseId]),
            'workflow_schema_ready' => $workflowReady,
            'information_requests' => $informationRequests,
            'findings' => $findings,
            'approvals' => $approvals,
            'corrective_actions' => $correctiveActions,
            'workflow_stage' => (string)($case['workflow_stage_code'] ?? $this->legacyWorkflowStage($case)),
            'sla_status' => $this->slaStatus($case),
        ];
    }

    /**
     * Builds the immutable evidence workspace from the case-bound source row,
     * exact movement references and audit records. This method is read-only:
     * it never updates the case, snapshot, source evidence or operational MM tables.
     *
     * @return array<string,mixed>
     */
    public function frozenEvidence(string $caseCode, array $allowedSites, array $accessScope = []): array
    {
        $data = $this->detail($caseCode, $allowedSites, $accessScope);
        $case = is_array($data['case'] ?? null) ? $data['case'] : [];
        $source = is_array($data['source'] ?? null) ? $data['source'] : [];
        $transactions = is_array($data['source_transactions'] ?? null) ? $data['source_transactions'] : [];

        $identity = [
            'hospital_code' => (string)($source['hospital_code'] ?? $case['site_code'] ?? ''),
            'source_db' => (string)($source['source_db'] ?? $case['source_db'] ?? ''),
            'month_snapshot_id' => (string)($source['month_snapshot_id'] ?? $case['month_snapshot_id'] ?? ''),
            'month_case_id' => (string)($source['month_case_id'] ?? $case['source_case_id'] ?? $case['case_id'] ?? ''),
            'case_code' => (string)($case['case_code'] ?? $caseCode),
        ];

        $statistical = [
            'observed_value' => $source['net_raw_qty'] ?? $source['outbound_raw_qty'] ?? null,
            'historical_average' => $source['baseline_avg_qty'] ?? $source['mean_qty'] ?? null,
            'median' => $source['baseline_median_qty'] ?? $source['median_qty'] ?? null,
            'p95' => $source['baseline_p95_qty'] ?? $source['p95_qty'] ?? null,
            'p99' => $source['baseline_p99_qty'] ?? $source['p99_qty'] ?? null,
            'historical_maximum' => $source['baseline_max_qty'] ?? $source['max_qty'] ?? null,
            'mad' => $source['baseline_mad_qty'] ?? $source['mad_qty'] ?? null,
            'z_score' => $source['z_score'] ?? null,
            'frequency' => $source['recent_frequency'] ?? $source['movement_frequency'] ?? null,
            'days_since_previous' => $source['days_since_previous'] ?? null,
            'baseline_days' => $source['baseline_days'] ?? $source['baseline_observation_days'] ?? null,
            'profile_status' => $source['profile_status'] ?? $source['profile_readiness'] ?? null,
            'risk_score' => $case['risk_score'] ?? $source['risk_score'] ?? null,
            'signals' => $source['signals'] ?? null,
        ];

        $users = [];
        foreach ($transactions as $row) {
            $user = trim((string)($row['user_name'] ?? $row['effective_user_id'] ?? $row['created_by'] ?? $row['trans_user'] ?? $row['username'] ?? ''));
            if ($user === '') continue;
            if (!isset($users[$user])) {
                $users[$user] = ['user_name' => $user, 'transaction_count' => 0, 'absolute_quantity' => 0.0, 'first_at' => null, 'last_at' => null];
            }
            $users[$user]['transaction_count']++;
            $qty = (float)($row['raw_qty'] ?? $row['trans_qty'] ?? $row['quantity'] ?? 0);
            $users[$user]['absolute_quantity'] += abs($qty);
            $at = (string)($row['transaction_at'] ?? $row['trans_datetime'] ?? $row['trans_date'] ?? $row['created_at'] ?? '');
            if ($at !== '') {
                if ($users[$user]['first_at'] === null || strcmp($at, (string)$users[$user]['first_at']) < 0) $users[$user]['first_at'] = $at;
                if ($users[$user]['last_at'] === null || strcmp($at, (string)$users[$user]['last_at']) > 0) $users[$user]['last_at'] = $at;
            }
        }
        usort($users, static fn(array $a, array $b): int => $b['transaction_count'] <=> $a['transaction_count']);

        $related = [];
        foreach ((array)($data['event_members'] ?? []) as $member) {
            $related[] = [
                'case_code' => (string)($member['source_case_code'] ?? ''),
                'item_code' => (string)($member['item_code'] ?? ''),
                'item_name_en' => (string)($member['item_name_en'] ?? ''),
                'item_name_ar' => (string)($member['item_name_ar'] ?? ''),
                'severity_code' => (string)($member['severity_code'] ?? ''),
                'risk_score' => $member['risk_score'] ?? null,
                'relationship' => (string)($member['correlation_reason'] ?? $member['member_disposition'] ?? ''),
            ];
        }

        $data['frozen_identity'] = $identity;
        $data['statistical_evidence'] = $statistical;
        $data['user_associations'] = array_values($users);
        $data['related_cases'] = $related;
        $data['frozen_contract'] = [
            'read_only' => true,
            'case_bound' => true,
            'snapshot_bound' => $identity['month_snapshot_id'] !== '',
            'source_tables_modified' => false,
            'database_link_used' => false,
            'generated_at' => date('Y-m-d H:i:s'),
        ];
        return $data;
    }


    /**
     * Opens one frozen monthly case by the immutable identity:
     * HOSPITAL_CODE + SOURCE_DB + MONTH_SNAPSHOT_ID + MONTH_CASE_ID.
     *
     * This method is read-only, uses the hospital's direct connection and never
     * writes to DIAR snapshots, investigation tables or MM operational tables.
     *
     * @return array<string,mixed>
     */
    public function frozenEvidenceByIdentity(
        string $hospitalCode,
        string $sourceDb,
        int $monthSnapshotId,
        int $monthCaseId,
        array $allowedSites
    ): array {
        $hospitalCode = strtoupper(trim($hospitalCode));
        $sourceDb = strtoupper(trim($sourceDb));
        if ($monthSnapshotId < 1 || $monthCaseId < 1) {
            throw new RuntimeException('FROZEN_EVIDENCE_IDENTITY_INCOMPLETE');
        }

        $allowed = [];
        foreach ($allowedSites as $site) {
            $site = strtoupper(trim((string)$site));
            if (in_array($site, ['NJCH','MNH'], true) && !in_array($site, $allowed, true)) {
                $allowed[] = $site;
            }
        }
        if (!in_array($hospitalCode, $allowed, true)) {
            throw new RuntimeException('FROZEN_EVIDENCE_SITE_NOT_ALLOWED');
        }

        $resolver = new DiarConnectionResolver();
        $connection = $resolver->connectionFor($hospitalCode);
        $dbIdentity = Database::fetchOne($connection, "SELECT
            PKG_DASH_DIAR_ENGINE.FN_HOSPITAL_CODE hospital_code,
            PKG_DASH_DIAR_ENGINE.FN_SOURCE_DB source_db
        FROM dual");
        if (
            strtoupper((string)($dbIdentity['hospital_code'] ?? '')) !== $hospitalCode
            || strtoupper((string)($dbIdentity['source_db'] ?? '')) !== $sourceDb
        ) {
            throw new RuntimeException('FROZEN_EVIDENCE_SOURCE_IDENTITY_MISMATCH');
        }

        $monthly = Database::fetchOne($connection, "SELECT
            c.MONTH_CASE_ID,c.MONTH_SNAPSHOT_ID,c.CANDIDATE_ID,c.CANDIDATE_HASH,
            TO_CHAR(c.CALENDAR_DAY,'YYYY-MM-DD') calendar_day,
            c.ANALYSIS_RUN_ID,c.STAT_RUN_ID,c.HOSPITAL_CODE,c.SOURCE_DB,
            c.BRANCH_ID,c.STORE_CODE,c.ITEM_CODE,c.RAW_UNIT_CODE,
            c.STORETRANSCODE,c.STORETRANSEFFECT,c.SEVERITY_CODE,c.ANOMALY_SCORE,
            c.INVENTORY_COST_ABS,c.TRANSACTION_COUNT,c.MOVEMENT_LINES,
            c.PROFILE_METHOD_VERSION,c.PROFILE_METHOD_HASH,c.PROFILE_HASH,
            c.DETECTION_METHOD_VERSION,c.DETECTION_METHOD_HASH,
            c.RULESET_VERSION_ID,c.RULESET_HASH,c.MOVE_MAP_VERSION_ID,c.MOVE_MAP_HASH,
            c.SOURCE_LINE_HASH,
            s.VERSION_NO snapshot_version,s.VERSION_TYPE snapshot_version_type,
            s.STATUS_CODE snapshot_status,s.SNAPSHOT_HASH,
            TO_CHAR(s.MONTH_START,'YYYY-MM') snapshot_month,
            TO_CHAR(s.DATA_UPDATED_THROUGH,'YYYY-MM-DD') data_updated_through,
            TO_CHAR(s.CLOSED_AT,'YYYY-MM-DD HH24:MI:SS') snapshot_closed_at,
            s.DAYS_EXPECTED,s.DAYS_ANALYZED,s.MISSING_DAYS
        FROM DASH_DIAR_MONTH_CASES c
        JOIN DASH_DIAR_MONTH_SNAPSHOTS s ON s.MONTH_SNAPSHOT_ID=c.MONTH_SNAPSHOT_ID
        WHERE c.MONTH_SNAPSHOT_ID=:snapshot_id
          AND c.MONTH_CASE_ID=:month_case_id
          AND c.HOSPITAL_CODE=:hospital_code
          AND c.SOURCE_DB=:source_db
          AND c.STORE_CODE NOT IN (56,57)", [
            'snapshot_id' => $monthSnapshotId,
            'month_case_id' => $monthCaseId,
            'hospital_code' => $hospitalCode,
            'source_db' => $sourceDb,
        ]);
        if (!is_array($monthly) || $monthly === []) {
            throw new RuntimeException('FROZEN_MONTHLY_CASE_NOT_FOUND_OR_NOT_ALLOWED');
        }

        $candidate = Database::fetchOne($connection, "SELECT
            CANDIDATE_ID,CANDIDATE_HASH,READINESS_CODE,CONFIDENCE_CODE,
            CANDIDATE_THRESHOLD,MATERIAL_SIGNAL_COUNT,TRIGGER_CODES,
            OBS_RAW_QTY_SUM,OBS_ABS_QTY_SUM,OBS_RAW_VALUE_TOTAL,OBS_ABS_VALUE_TOTAL,
            OBS_TRANSACTION_COUNT,OBS_MOVEMENT_LINES,OBS_MAX_ABS_LINE_QTY,
            DAYS_SINCE_ACTIVITY,QTY_SCORE,VALUE_SCORE,TXN_SCORE,LINE_SCORE,DORMANCY_SCORE
        FROM DASH_DIAR_ANOM_CANDIDATES
        WHERE CANDIDATE_ID=:candidate_id
          AND HOSPITAL_CODE=:hospital_code
          AND SOURCE_DB=:source_db", [
            'candidate_id' => (int)$monthly['candidate_id'],
            'hospital_code' => $hospitalCode,
            'source_db' => $sourceDb,
        ]);
        $candidate = is_array($candidate) ? $candidate : [];

        $signals = Database::fetchAll($connection, "SELECT
            SIGNAL_CODE,SIGNAL_WEIGHT,OBSERVED_VALUE,CENTER_VALUE,MAD_VALUE,
            P95_VALUE,P99_VALUE,HISTORICAL_MAX_VALUE,ROBUST_Z,COMPONENT_SCORE,IS_MATERIAL
        FROM DASH_DIAR_ANOM_SIGNALS
        WHERE CANDIDATE_ID=:candidate_id
        ORDER BY SIGNAL_CODE", ['candidate_id' => (int)$monthly['candidate_id']]);

        $day = Database::fetchOne($connection, "SELECT
            TO_CHAR(BASELINE_FROM_DATE,'YYYY-MM-DD') baseline_from_date,
            TO_CHAR(BASELINE_TO_DATE,'YYYY-MM-DD') baseline_to_date,
            TO_CHAR(RECENT_FROM_DATE,'YYYY-MM-DD') recent_from_date,
            TO_CHAR(RECENT_TO_DATE,'YYYY-MM-DD') recent_to_date,
            EVALUATED_ROWS,PROFILED_ROWS,EXCLUDED_ROWS
        FROM DASH_DIAR_MONTH_DAYS
        WHERE MONTH_SNAPSHOT_ID=:snapshot_id
          AND CALENDAR_DAY=TO_DATE(:calendar_day,'YYYY-MM-DD')", [
            'snapshot_id' => $monthSnapshotId,
            'calendar_day' => (string)$monthly['calendar_day'],
        ]);
        $day = is_array($day) ? $day : [];

        $item = Database::fetchOne($connection, "SELECT ITEMARNAME item_name_ar,ITEMENNAME item_name_en
            FROM MMITEMSMAINDATA WHERE ITEMCODE=:item_code AND ROWNUM=1", [
            'item_code' => (int)$monthly['item_code'],
        ]);
        $item = is_array($item) ? $item : [];

        $store = Database::fetchOne($connection, "SELECT STOREARNAME store_name_ar,STOREENNAME store_name_en
            FROM MMSTOREMAINDATA
            WHERE BRANCHID=:branch_id AND STORECODE=:store_code AND ROWNUM=1", [
            'branch_id' => (int)$monthly['branch_id'],
            'store_code' => (int)$monthly['store_code'],
        ]);
        $store = is_array($store) ? $store : [];

        $investigation = Database::fetchOne($this->controlBranch, "SELECT * FROM (
            SELECT CASE_CODE,STATUS_CODE,CASE_ID
            FROM DASH_DIAR_CASES
            WHERE SITE_CODE=:site_code
              AND ENTITY_TYPE='CASE'
              AND STORE_CODE=:store_code
              AND ITEM_CODE=:item_code
              AND TRUNC(CALENDAR_DAY)=TO_DATE(:calendar_day,'YYYY-MM-DD')
            ORDER BY CASE_ID DESC
        ) WHERE ROWNUM=1", [
            'site_code' => $hospitalCode,
            'store_code' => (string)$monthly['store_code'],
            'item_code' => (string)$monthly['item_code'],
            'calendar_day' => (string)$monthly['calendar_day'],
        ]);
        $investigation = is_array($investigation) ? $investigation : [];

        $caseCode = 'MC-' . (string)$monthCaseId;
        $source = array_merge($monthly, $candidate, $day, $item, $store);
        $case = [
            'case_code' => $caseCode,
            'entity_type' => 'CASE',
            'site_code' => $hospitalCode,
            'source_db' => $sourceDb,
            'branch_id' => $monthly['branch_id'],
            'store_code' => $monthly['store_code'],
            'store_name_ar' => (string)($store['store_name_ar'] ?? ''),
            'store_name_en' => (string)($store['store_name_en'] ?? ''),
            'calendar_day' => (string)$monthly['calendar_day'],
            'item_code' => $monthly['item_code'],
            'item_name_ar' => (string)($item['item_name_ar'] ?? ''),
            'item_name_en' => (string)($item['item_name_en'] ?? ''),
            'raw_unit_code' => $monthly['raw_unit_code'],
            'storetranscode' => $monthly['storetranscode'],
            'storetranseffect' => $monthly['storetranseffect'],
            'risk_score' => $monthly['anomaly_score'],
            'severity_code' => (string)$monthly['severity_code'],
            'status_code' => (string)($investigation['status_code'] ?? 'FROZEN'),
            'review_reason' => (string)($candidate['trigger_codes'] ?? 'FROZEN_MONTHLY_ANOMALY'),
            'source_case_id' => $monthCaseId,
            'month_snapshot_id' => $monthSnapshotId,
            'month_case_id' => $monthCaseId,
            'candidate_id' => $monthly['candidate_id'],
            'rule_version_no' => $monthly['ruleset_version_id'],
        ];

        $movementResult = $this->sourceMovementReferences($case, $source);
        $transactions = $this->normalizeEvidenceMovements((array)($movementResult['rows'] ?? []), $case, $source);
        $related = $this->relatedMonthlyCases(
            $connection,
            $hospitalCode,
            $sourceDb,
            $monthSnapshotId,
            $monthCaseId,
            (int)$monthly['store_code'],
            (int)$monthly['item_code'],
            (string)$monthly['calendar_day']
        );

        $history = [];
        $notes = [];
        $evidence = [];
        $assignments = [];
        if (!empty($investigation['case_id'])) {
            $id = (int)$investigation['case_id'];
            $history = Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_HISTORY WHERE CASE_ID=:id ORDER BY PERFORMED_AT DESC,HISTORY_ID DESC", ['id' => $id]);
            $notes = Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_NOTES WHERE CASE_ID=:id ORDER BY CREATED_AT DESC,NOTE_ID DESC", ['id' => $id]);
            $evidence = Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_EVIDENCE WHERE CASE_ID=:id ORDER BY ADDED_AT DESC,EVIDENCE_ID DESC", ['id' => $id]);
            $assignments = Database::fetchAll($this->controlBranch, "SELECT * FROM DASH_DIAR_CASE_ASSIGNMENTS WHERE CASE_ID=:id ORDER BY ASSIGNED_AT DESC,ASSIGNMENT_ID DESC", ['id' => $id]);
        }

        return [
            'case' => $case,
            'source' => $source,
            'frozen_identity' => [
                'hospital_code' => $hospitalCode,
                'source_db' => $sourceDb,
                'month_snapshot_id' => (string)$monthSnapshotId,
                'month_case_id' => (string)$monthCaseId,
                'case_code' => $caseCode,
            ],
            'statistical_evidence' => $this->monthlyStatisticalEvidence($monthly, $candidate, $signals, $day),
            'source_transactions' => $transactions,
            'source_transaction_total' => (int)($movementResult['total'] ?? count($transactions)),
            'source_transaction_limit' => (int)($movementResult['limit'] ?? 500),
            'source_transaction_error' => (string)($movementResult['error'] ?? ''),
            'related_cases' => $related,
            'user_associations' => $this->buildUserAssociations($transactions),
            'history' => $history,
            'notes' => $notes,
            'evidence' => $evidence,
            'assignments' => $assignments,
            'signal_explanations' => $this->signalExplanationsFromRows($signals),
            'investigation_case_code' => (string)($investigation['case_code'] ?? ''),
            'frozen_contract' => [
                'read_only' => true,
                'case_bound' => true,
                'snapshot_bound' => true,
                'source_tables_modified' => false,
                'database_link_used' => false,
                'snapshot_status' => (string)($monthly['snapshot_status'] ?? ''),
                'snapshot_hash' => (string)($monthly['snapshot_hash'] ?? ''),
                'generated_at' => date('Y-m-d H:i:s'),
            ],
        ];
    }

    /** @param array<int,array<string,mixed>> $signals @return array<string,mixed> */
    private function monthlyStatisticalEvidence(array $monthly, array $candidate, array $signals, array $day): array
    {
        $indexed = [];
        foreach ($signals as $signal) {
            $indexed[strtoupper((string)($signal['signal_code'] ?? ''))] = $signal;
        }
        $qty = is_array($indexed['QTY_MAGNITUDE'] ?? null) ? $indexed['QTY_MAGNITUDE'] : [];
        return [
            'observed_value' => $candidate['obs_abs_qty_sum'] ?? $qty['observed_value'] ?? null,
            'historical_average' => null,
            'median' => $qty['center_value'] ?? null,
            'p95' => $qty['p95_value'] ?? null,
            'p99' => $qty['p99_value'] ?? null,
            'historical_maximum' => $qty['historical_max_value'] ?? null,
            'mad' => $qty['mad_value'] ?? null,
            'z_score' => $qty['robust_z'] ?? null,
            'frequency' => $candidate['obs_transaction_count'] ?? $monthly['transaction_count'] ?? null,
            'days_since_previous' => $candidate['days_since_activity'] ?? null,
            'baseline_days' => $this->dateSpanDays((string)($day['baseline_from_date'] ?? ''), (string)($day['baseline_to_date'] ?? '')),
            'profile_status' => $candidate['readiness_code'] ?? null,
            'risk_score' => $monthly['anomaly_score'] ?? null,
            'signals' => $candidate['trigger_codes'] ?? null,
        ];
    }

    /** @return array<int,array<string,mixed>> */
    private function relatedMonthlyCases(
        string $connection,
        string $hospitalCode,
        string $sourceDb,
        int $snapshotId,
        int $monthCaseId,
        int $storeCode,
        int $itemCode,
        string $calendarDay
    ): array {
        $rows = Database::fetchAll($connection, "SELECT * FROM (
            SELECT MONTH_CASE_ID,ITEM_CODE,SEVERITY_CODE,ANOMALY_SCORE,STORE_CODE,
                   TO_CHAR(CALENDAR_DAY,'YYYY-MM-DD') calendar_day,
                   CASE
                     WHEN STORE_CODE=:store_code AND ITEM_CODE=:item_code THEN 'SAME_STORE_AND_ITEM'
                     WHEN ITEM_CODE=:item_code THEN 'SAME_ITEM'
                     WHEN STORE_CODE=:store_code THEN 'SAME_STORE'
                     ELSE 'NEARBY_CASE'
                   END relationship
            FROM DASH_DIAR_MONTH_CASES
            WHERE MONTH_SNAPSHOT_ID=:snapshot_id
              AND MONTH_CASE_ID<>:month_case_id
              AND HOSPITAL_CODE=:hospital_code
              AND SOURCE_DB=:source_db
              AND STORE_CODE NOT IN (56,57)
              AND (
                    ITEM_CODE=:item_code
                 OR STORE_CODE=:store_code
                 OR CALENDAR_DAY BETWEEN TO_DATE(:calendar_day,'YYYY-MM-DD')-1 AND TO_DATE(:calendar_day,'YYYY-MM-DD')+1
              )
            ORDER BY
                CASE WHEN STORE_CODE=:store_code AND ITEM_CODE=:item_code THEN 1
                     WHEN ITEM_CODE=:item_code THEN 2
                     WHEN STORE_CODE=:store_code THEN 3 ELSE 4 END,
                ANOMALY_SCORE DESC,MONTH_CASE_ID DESC
        ) WHERE ROWNUM<=12", [
            'store_code' => $storeCode,
            'item_code' => $itemCode,
            'snapshot_id' => $snapshotId,
            'month_case_id' => $monthCaseId,
            'hospital_code' => $hospitalCode,
            'source_db' => $sourceDb,
            'calendar_day' => $calendarDay,
        ]);
        $out = [];
        foreach ($rows as $row) {
            $out[] = [
                'case_code' => 'MC-' . (string)($row['month_case_id'] ?? ''),
                'month_case_id' => (string)($row['month_case_id'] ?? ''),
                'item_code' => (string)($row['item_code'] ?? ''),
                'item_name_en' => '',
                'item_name_ar' => '',
                'severity_code' => (string)($row['severity_code'] ?? ''),
                'risk_score' => $row['anomaly_score'] ?? null,
                'relationship' => (string)($row['relationship'] ?? ''),
                'store_code' => (string)($row['store_code'] ?? ''),
                'calendar_day' => (string)($row['calendar_day'] ?? ''),
            ];
        }
        return $out;
    }

    /** @param array<int,array<string,mixed>> $rows @return array<int,array<string,mixed>> */
    private function normalizeEvidenceMovements(array $rows, array $case, array $source): array
    {
        $out = [];
        foreach ($rows as $row) {
            $row['transaction_at'] = $row['trans_datetime'] ?? $row['transaction_at'] ?? '';
            $row['transaction_no'] = $row['trans_serial'] ?? $row['transaction_no'] ?? '';
            $row['user_name'] = $row['effective_user_id'] ?? $row['user_name'] ?? '';
            $row['item_code'] = $case['item_code'] ?? $row['item_code'] ?? '';
            $row['raw_unit_code'] = $source['raw_unit_code'] ?? $row['raw_unit_code'] ?? '';
            $row['storetranseffect'] = $row['effect_raw'] ?? $source['storetranseffect'] ?? '';
            $row['reference_no'] = $row['reference_key'] ?? $row['reference_no'] ?? '';
            $out[] = $row;
        }
        return $out;
    }

    /** @param array<int,array<string,mixed>> $transactions @return array<int,array<string,mixed>> */
    private function buildUserAssociations(array $transactions): array
    {
        $users = [];
        foreach ($transactions as $row) {
            $user = trim((string)($row['user_name'] ?? $row['effective_user_id'] ?? $row['created_by'] ?? $row['username'] ?? ''));
            if ($user === '') continue;
            if (!isset($users[$user])) {
                $users[$user] = ['user_name'=>$user,'transaction_count'=>0,'absolute_quantity'=>0.0,'first_at'=>null,'last_at'=>null];
            }
            $users[$user]['transaction_count']++;
            $users[$user]['absolute_quantity'] += abs((float)($row['raw_qty'] ?? $row['quantity'] ?? 0));
            $at = (string)($row['transaction_at'] ?? $row['trans_datetime'] ?? $row['created_at'] ?? '');
            if ($at !== '') {
                if ($users[$user]['first_at'] === null || strcmp($at, (string)$users[$user]['first_at']) < 0) $users[$user]['first_at'] = $at;
                if ($users[$user]['last_at'] === null || strcmp($at, (string)$users[$user]['last_at']) > 0) $users[$user]['last_at'] = $at;
            }
        }
        $out = array_values($users);
        usort($out, static function(array $a,array $b): int {
            return (int)$b['transaction_count'] <=> (int)$a['transaction_count'];
        });
        return $out;
    }

    /** @param array<int,array<string,mixed>> $signals @return array<int,array<string,mixed>> */
    private function signalExplanationsFromRows(array $signals): array
    {
        $labels = [
            'QTY_MAGNITUDE'=>['Quantity magnitude','حج�
 الك�
ية'],
            'VALUE_MAGNITUDE'=>['Associated value magnitude','حج�
 القي�
ة ال�
رتبطة'],
            'TXN_FREQUENCY'=>['Transaction frequency','تكرار ال�
عا�
لات'],
            'LINE_SIZE'=>['Movement line size','حج�
 سطر الحركة'],
            'DORMANCY_REACTIVATION'=>['Dormancy reactivation','عودة النشاط بعد توقف'],
        ];
        $out = [];
        foreach ($signals as $signal) {
            $code = strtoupper((string)($signal['signal_code'] ?? ''));
            if ($code === '') continue;
            $label = $labels[$code] ?? [$code,$code];
            $out[] = [
                'code'=>$code,'title_en'=>$label[0],'title_ar'=>$label[1],
                'explanation_en'=>'Observed value '.(string)($signal['observed_value']??'—').' compared with upper historical range '.(string)($signal['p95_value']??'—').' and exceptional historical range '.(string)($signal['p99_value']??'—').'.',
                'explanation_ar'=>'القي�
ة ال�
رصودة '.(string)($signal['observed_value']??'—').' �
قارنة بالنطاق التاريخي الأعلى '.(string)($signal['p95_value']??'—').' والنطاق التاريخي الاستثنائي '.(string)($signal['p99_value']??'—').'.',
            ];
        }
        return $out;
    }

    private function dateSpanDays(string $from, string $to)
    {
        if ($from === '' || $to === '') return null;
        try {
            $a = new \DateTimeImmutable(substr($from,0,10));
            $b = new \DateTimeImmutable(substr($to,0,10));
            return (int)$a->diff($b)->days + 1;
        } catch (Throwable $error) {
            return null;
        }
    }

    /** @return array<int,array<string,mixed>> */
    public function investigators(): array
    {
        return Database::fetchAll($this->controlBranch, "SELECT username FROM (
            SELECT DISTINCT u.NAME username
            FROM SECURITY_USERS u
            WHERE NVL(u.DASHBOARD_ROLE,0)=1
               OR EXISTS (
                    SELECT 1 FROM DASH_ACL_USER_ROLES ur
                    WHERE UPPER(ur.USERNAME)=UPPER(u.NAME) AND NVL(ur.IS_ACTIVE,1)=1
               )
            ORDER BY username
        ) WHERE ROWNUM<=500");
    }

    public function assign(string $caseCode, string $assignedTo, string $reason, string $actor, array $allowedSites): void
    {
        $case = $this->find($caseCode, $allowedSites, $accessScope);
        if (!$case) {
            throw new RuntimeException('CASE_NOT_FOUND_OR_NOT_ALLOWED');
        }
        $user = Database::fetchOne($this->controlBranch, "SELECT NAME username FROM SECURITY_USERS WHERE UPPER(NAME)=UPPER(:name) AND ROWNUM=1", ['name' => $assignedTo]);
        if (!$user) {
            throw new RuntimeException('ASSIGNEE_NOT_FOUND');
        }
        if (!$this->assigneeCanAccessSite((string)$user['username'], (string)$case['site_code'])) {
            throw new RuntimeException('ASSIGNEE_SITE_ACCESS_REQUIRED');
        }
        $caseId = (int)$case['case_id'];
        $oldStage = (string)($case['workflow_stage_code'] ?? $this->legacyWorkflowStage($case));
        $newStage = $oldStage === 'NEW' ? 'ASSIGNED' : $oldStage;
        $caseUpdate = $this->workflowSchemaReady()
            ? "UPDATE DASH_DIAR_CASES SET WORKFLOW_STAGE_CODE=:new_stage,DUE_AT=NVL(DUE_AT,SYSDATE+CASE SEVERITY_CODE WHEN 'CRITICAL' THEN 1 WHEN 'HIGH' THEN 3 WHEN 'MEDIUM' THEN 7 ELSE 14 END),LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id"
            : "UPDATE DASH_DIAR_CASES SET UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id";
        $caseParams = ['actor' => $actor,'case_id' => $caseId];
        if ($this->workflowSchemaReady()) {
            $caseParams['new_stage'] = $newStage;
        }
        $detail = $this->historyJson(['old_stage'=>$oldStage,'new_stage'=>$newStage,'assigned_to'=>(string)$user['username']]);
        $statements = [
            ['sql' => "UPDATE DASH_DIAR_CASE_ASSIGNMENTS SET STATUS_CODE='ENDED',ENDED_AT=SYSDATE WHERE CASE_ID=:case_id AND STATUS_CODE='ACTIVE'", 'params' => ['case_id' => $caseId]],
            ['sql' => "INSERT INTO DASH_DIAR_CASE_ASSIGNMENTS (ASSIGNMENT_ID,CASE_ID,ASSIGNED_TO,ASSIGNED_BY,ASSIGNED_AT,STATUS_CODE,ASSIGNMENT_REASON) VALUES (DASH_DIAR_ASSIGN_SEQ.NEXTVAL,:case_id,:assigned_to,:assigned_by,SYSDATE,'ACTIVE',:reason)", 'params' => ['case_id' => $caseId,'assigned_to' => (string)$user['username'],'assigned_by' => $actor,'reason' => $this->clip($reason,1000)]],
            ['sql' => "INSERT INTO DASH_DIAR_CASE_HISTORY (HISTORY_ID,CASE_ID,ACTION_CODE,OLD_STATUS_CODE,NEW_STATUS_CODE,PERFORMED_BY,PERFORMED_AT,NOTE_TEXT,DETAIL_JSON) VALUES (DASH_DIAR_HISTORY_SEQ.NEXTVAL,:case_id,'ASSIGNED',:old_status,:new_status,:actor,SYSDATE,:note_text,:detail_json)", 'params' => ['case_id'=>$caseId,'old_status'=>(string)$case['status_code'],'new_status'=>(string)$case['status_code'],'actor'=>$actor,'note_text'=>'Assigned to '.(string)$user['username'].($reason!==''?'. '.$this->clip($reason,900):''),'detail_json'=>$detail]],
            ['sql' => $caseUpdate, 'params' => $caseParams],
        ];
        Database::executeTransaction($this->controlBranch, $statements);
    }

    public function transition(string $caseCode, string $newStatus, ?string $outcome, string $summary, ?string $escalationLevel, string $actor, array $allowedSites): void
    {
        $case = $this->find($caseCode, $allowedSites);
        if (!$case) {
            throw new RuntimeException('CASE_NOT_FOUND_OR_NOT_ALLOWED');
        }
        $oldStatus = (string)$case['status_code'];
        $newStatus = strtoupper(trim($newStatus));
        $allowed = $this->allowedTransitions($oldStatus);
        if (!in_array($newStatus, $allowed, true)) {
            throw new RuntimeException('STATUS_TRANSITION_NOT_ALLOWED_' . $oldStatus . '_TO_' . $newStatus);
        }

        $outcome = $outcome !== null && trim($outcome) !== '' ? strtoupper(trim($outcome)) : null;
        $validOutcomes = ['AUTHORIZED_ACTIVITY','OPERATIONAL_ERROR','DOCUMENTATION_GAP','SYSTEM_DATA_ISSUE','POLICY_VIOLATION','POTENTIAL_MISUSE','INSUFFICIENT_EVIDENCE'];
        if ($outcome !== null && !in_array($outcome, $validOutcomes, true)) {
            throw new RuntimeException('INVALID_INVESTIGATION_OUTCOME');
        }
        if (in_array($newStatus, ['EXPLAINED_ACTIVITY','CONFIRMED_ANOMALY','FALSE_POSITIVE','CLOSED'], true) && $outcome === null) {
            throw new RuntimeException('INVESTIGATION_OUTCOME_REQUIRED');
        }
        if (in_array($newStatus, ['EXPLAINED_ACTIVITY','CONFIRMED_ANOMALY','FALSE_POSITIVE','ESCALATED','CLOSED'], true) && trim($summary) === '') {
            throw new RuntimeException('RESOLUTION_OR_ESCALATION_SUMMARY_REQUIRED');
        }
        $escalationLevel = $escalationLevel !== null && trim($escalationLevel) !== '' ? strtoupper(trim($escalationLevel)) : null;
        if ($newStatus === 'ESCALATED' && !in_array($escalationLevel, ['LOW','MEDIUM','HIGH','CRITICAL'], true)) {
            throw new RuntimeException('ESCALATION_LEVEL_REQUIRED');
        }

        $caseId = (int)$case['case_id'];
        $historyDetail = json_encode([
            'previous_resolution_summary' => $this->clip((string)($case['resolution_summary'] ?? ''), 1200),
            'previous_escalation_level' => $case['escalation_level'] ?? null,
            'previous_closed_by' => $case['closed_by'] ?? null,
            'previous_closed_at' => $case['closed_at'] ?? null,
            'new_resolution_summary' => $this->clip($summary, 1200),
            'new_escalation_level' => $escalationLevel,
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        if (!is_string($historyDetail)) {
            $historyDetail = '{}';
        }
        $historyDetail = $this->clip($historyDetail, 3900);

        $statements = [
            ['sql' => "UPDATE DASH_DIAR_CASES SET
                STATUS_CODE=:new_status,INVESTIGATION_OUTCOME=:outcome,
                RESOLUTION_SUMMARY=:summary,ESCALATION_LEVEL=:escalation_level,
                CLOSED_BY=CASE WHEN :new_status='CLOSED' THEN :actor ELSE NULL END,
                CLOSED_AT=CASE WHEN :new_status='CLOSED' THEN SYSDATE ELSE NULL END,
                UPDATED_BY=:actor,UPDATED_AT=SYSDATE
                WHERE CASE_ID=:case_id", 'params' => [
                    'new_status' => $newStatus,'outcome' => $outcome,'summary' => $this->clip($summary,4000),
                    'escalation_level' => $escalationLevel,'actor' => $actor,'case_id' => $caseId,
                ]],
            ['sql' => "INSERT INTO DASH_DIAR_CASE_HISTORY (
                HISTORY_ID,CASE_ID,ACTION_CODE,OLD_STATUS_CODE,NEW_STATUS_CODE,
                OLD_OUTCOME,NEW_OUTCOME,PERFORMED_BY,PERFORMED_AT,NOTE_TEXT,DETAIL_JSON
            ) VALUES (
                DASH_DIAR_HISTORY_SEQ.NEXTVAL,:case_id,'STATUS_CHANGED',:old_status,:new_status,
                :old_outcome,:new_outcome,:actor,SYSDATE,:note_text,:detail_json
            )", 'params' => [
                'case_id' => $caseId,'old_status' => $oldStatus,'new_status' => $newStatus,
                'old_outcome' => $case['investigation_outcome'] ?? null,'new_outcome' => $outcome,
                'actor' => $actor,'note_text' => $this->clip($summary,4000),
                'detail_json' => $historyDetail,
            ]],
        ];
        Database::executeTransaction($this->controlBranch, $statements);
    }

    public function addNote(string $caseCode, string $noteType, string $note, bool $isPrivate, string $actor, array $allowedSites): void
    {
        $case = $this->find($caseCode, $allowedSites);
        if (!$case) {
            throw new RuntimeException('CASE_NOT_FOUND_OR_NOT_ALLOWED');
        }
        $noteType = strtoupper(trim($noteType));
        if (!in_array($noteType, ['REVIEW','EVIDENCE_REQUEST','ACTION','MANAGEMENT','GENERAL'], true)) {
            throw new RuntimeException('INVALID_NOTE_TYPE');
        }
        if (trim($note) === '') {
            throw new RuntimeException('NOTE_TEXT_REQUIRED');
        }
        $caseId = (int)$case['case_id'];
        Database::executeTransaction($this->controlBranch, [
            ['sql' => "INSERT INTO DASH_DIAR_CASE_NOTES (
                NOTE_ID,CASE_ID,NOTE_TYPE,NOTE_TEXT,IS_PRIVATE,CREATED_BY,CREATED_AT
            ) VALUES (
                DASH_DIAR_NOTE_SEQ.NEXTVAL,:case_id,:note_type,:note_text,:is_private,:actor,SYSDATE
            )", 'params' => ['case_id' => $caseId,'note_type' => $noteType,'note_text' => $this->clip($note,4000),'is_private' => $isPrivate ? 1 : 0,'actor' => $actor]],
            ['sql' => "INSERT INTO DASH_DIAR_CASE_HISTORY (
                HISTORY_ID,CASE_ID,ACTION_CODE,OLD_STATUS_CODE,NEW_STATUS_CODE,PERFORMED_BY,PERFORMED_AT,NOTE_TEXT
            ) VALUES (
                DASH_DIAR_HISTORY_SEQ.NEXTVAL,:case_id,'NOTE_ADDED',:status_code,:status_code,:actor,SYSDATE,:note_text
            )", 'params' => ['case_id' => $caseId,'status_code' => (string)$case['status_code'],'actor' => $actor,'note_text' => 'Note type: ' . $noteType]],
            ['sql' => "UPDATE DASH_DIAR_CASES SET UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id", 'params' => ['actor' => $actor,'case_id' => $caseId]],
        ]);
    }

    public function addEvidence(string $caseCode, array $input, string $actor, array $allowedSites): void
    {
        $case = $this->find($caseCode, $allowedSites);
        if (!$case) {
            throw new RuntimeException('CASE_NOT_FOUND_OR_NOT_ALLOWED');
        }
        $type = strtoupper(trim((string)($input['evidence_type'] ?? '')));
        $valid = ['TRANSACTION_REPORT','APPROVAL','INVENTORY_SHEET','USER_STATEMENT','SYSTEM_LOG','POLICY_DOCUMENT','OTHER'];
        if (!in_array($type, $valid, true)) {
            throw new RuntimeException('INVALID_EVIDENCE_TYPE');
        }
        $description = trim((string)($input['description'] ?? ''));
        if ($description === '') {
            throw new RuntimeException('EVIDENCE_DESCRIPTION_REQUIRED');
        }
        $sha = strtolower(trim((string)($input['file_sha256'] ?? '')));
        if ($sha !== '' && !preg_match('/^[a-f0-9]{64}$/', $sha)) {
            throw new RuntimeException('INVALID_EVIDENCE_SHA256');
        }
        $caseId = (int)$case['case_id'];
        Database::executeTransaction($this->controlBranch, [
            ['sql' => "INSERT INTO DASH_DIAR_CASE_EVIDENCE (
                EVIDENCE_ID,CASE_ID,EVIDENCE_TYPE,REFERENCE_NO,DESCRIPTION_TEXT,
                FILE_NAME,STORAGE_PATH,FILE_SHA256,ADDED_BY,ADDED_AT
            ) VALUES (
                DASH_DIAR_EVID_SEQ.NEXTVAL,:case_id,:evidence_type,:reference_no,:description,
                :file_name,:storage_path,:file_sha256,:actor,SYSDATE
            )", 'params' => [
                'case_id' => $caseId,'evidence_type' => $type,
                'reference_no' => $this->nullableClip((string)($input['reference_no'] ?? ''),200),
                'description' => $this->clip($description,2000),
                'file_name' => $this->nullableClip((string)($input['file_name'] ?? ''),260),
                'storage_path' => $this->nullableClip((string)($input['storage_path'] ?? ''),1000),
                'file_sha256' => $sha !== '' ? $sha : null,'actor' => $actor,
            ]],
            ['sql' => "INSERT INTO DASH_DIAR_CASE_HISTORY (
                HISTORY_ID,CASE_ID,ACTION_CODE,OLD_STATUS_CODE,NEW_STATUS_CODE,PERFORMED_BY,PERFORMED_AT,NOTE_TEXT
            ) VALUES (
                DASH_DIAR_HISTORY_SEQ.NEXTVAL,:case_id,'EVIDENCE_ADDED',:status_code,:status_code,:actor,SYSDATE,:note_text
            )", 'params' => ['case_id' => $caseId,'status_code' => (string)$case['status_code'],'actor' => $actor,'note_text' => 'Evidence type: ' . $type]],
            ['sql' => "UPDATE DASH_DIAR_CASES SET UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id", 'params' => ['actor' => $actor,'case_id' => $caseId]],
        ]);
    }


    public function startReview(string $caseCode, string $actor, array $allowedSites): void
    {
        $case = $this->requireWorkflowCase($caseCode, $allowedSites);
        $stage = $this->currentStage($case);
        if (!in_array($stage, ['ASSIGNED','INFORMATION_REQUIRED'], true)) {
            throw new RuntimeException('WORKFLOW_STAGE_NOT_ALLOWED_' . $stage . '_TO_UNDER_REVIEW');
        }
        $this->changeWorkflowStage($case, 'UNDER_REVIEW', 'REVIEW_STARTED', $actor, 'Investigation review started.');
    }

    public function requestInformation(string $caseCode, string $requestedFrom, string $requestText, string $dueDate, string $actor, array $allowedSites): void
    {
        $case = $this->requireWorkflowCase($caseCode, $allowedSites);
        $stage = $this->currentStage($case);
        if (!in_array($stage, ['UNDER_REVIEW','INFORMATION_REQUIRED'], true)) {
            throw new RuntimeException('WORKFLOW_STAGE_NOT_ALLOWED_' . $stage . '_TO_INFORMATION_REQUIRED');
        }
        if (trim($requestedFrom) === '' || trim($requestText) === '') {
            throw new RuntimeException('INFORMATION_REQUEST_FIELDS_REQUIRED');
        }
        $due = $this->oracleDate($dueDate);
        $caseId = (int)$case['case_id'];
        Database::executeTransaction($this->controlBranch, [
            ['sql'=>"INSERT INTO DASH_DIAR_CASE_INFO_REQUESTS (REQUEST_ID,CASE_ID,REQUESTED_FROM,REQUEST_TEXT,STATUS_CODE,REQUESTED_BY,REQUESTED_AT,DUE_AT) VALUES (DASH_DIAR_INFO_REQ_SEQ.NEXTVAL,:case_id,:requested_from,:request_text,'OPEN',:actor,SYSDATE,TO_DATE(:due_at,'YYYY-MM-DD HH24:MI:SS'))", 'params'=>['case_id'=>$caseId,'requested_from'=>$this->clip($requestedFrom,200),'request_text'=>$this->clip($requestText,4000),'actor'=>$actor,'due_at'=>$due]],
            ['sql'=>"UPDATE DASH_DIAR_CASES SET WORKFLOW_STAGE_CODE='INFORMATION_REQUIRED',STATUS_CODE='EVIDENCE_REQUESTED',LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id", 'params'=>['actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,'INFORMATION_REQUESTED',(string)$case['status_code'],'EVIDENCE_REQUESTED',$actor,$requestText,['old_stage'=>$stage,'new_stage'=>'INFORMATION_REQUIRED','requested_from'=>$requestedFrom,'due_at'=>$dueDate]),
        ]);
    }

    public function respondInformation(string $caseCode, int $requestId, string $responseText, string $actor, array $allowedSites): void
    {
        $case = $this->requireWorkflowCase($caseCode, $allowedSites);
        if ($requestId < 1 || trim($responseText) === '') {
            throw new RuntimeException('INFORMATION_RESPONSE_REQUIRED');
        }
        $caseId = (int)$case['case_id'];
        $open = Database::fetchOne($this->controlBranch, "SELECT REQUEST_ID FROM DASH_DIAR_CASE_INFO_REQUESTS WHERE REQUEST_ID=:request_id AND CASE_ID=:case_id AND STATUS_CODE='OPEN'", ['request_id'=>$requestId,'case_id'=>$caseId]);
        if (!$open) {
            throw new RuntimeException('OPEN_INFORMATION_REQUEST_NOT_FOUND');
        }
        Database::executeTransaction($this->controlBranch, [
            ['sql'=>"UPDATE DASH_DIAR_CASE_INFO_REQUESTS SET STATUS_CODE='RESPONDED',RESPONSE_TEXT=:response_text,RESPONDED_BY=:actor,RESPONDED_AT=SYSDATE WHERE REQUEST_ID=:request_id AND CASE_ID=:case_id", 'params'=>['response_text'=>$this->clip($responseText,4000),'actor'=>$actor,'request_id'=>$requestId,'case_id'=>$caseId]],
            ['sql'=>"UPDATE DASH_DIAR_CASES SET WORKFLOW_STAGE_CODE='UNDER_REVIEW',STATUS_CODE='UNDER_REVIEW',LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id", 'params'=>['actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,'INFORMATION_RESPONDED',(string)$case['status_code'],'UNDER_REVIEW',$actor,$responseText,['old_stage'=>$this->currentStage($case),'new_stage'=>'UNDER_REVIEW','request_id'=>$requestId]),
        ]);
    }

    public function proposeFinding(string $caseCode, string $outcome, string $summary, string $rootCause, string $recommendedAction, bool $requiresCorrectiveAction, string $actor, array $allowedSites): void
    {
        $case = $this->requireWorkflowCase($caseCode, $allowedSites);
        $stage = $this->currentStage($case);
        if (!in_array($stage, ['UNDER_REVIEW','FINDING_PROPOSED'], true)) {
            throw new RuntimeException('WORKFLOW_STAGE_NOT_ALLOWED_' . $stage . '_TO_FINDING_PROPOSED');
        }
        $outcome = strtoupper(trim($outcome));
        $valid = ['AUTHORIZED_ACTIVITY','OPERATIONAL_ERROR','DOCUMENTATION_GAP','SYSTEM_DATA_ISSUE','POLICY_VIOLATION','POTENTIAL_MISUSE','INSUFFICIENT_EVIDENCE'];
        if (!in_array($outcome, $valid, true) || trim($summary)==='' || trim($rootCause)==='') {
            throw new RuntimeException('FINDING_FIELDS_REQUIRED');
        }
        $caseId=(int)$case['case_id'];
        Database::executeTransaction($this->controlBranch, [
            ['sql'=>"UPDATE DASH_DIAR_CASE_FINDINGS SET STATUS_CODE='SUPERSEDED',UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id AND STATUS_CODE IN ('DRAFT','SUBMITTED','RETURNED')", 'params'=>['actor'=>$actor,'case_id'=>$caseId]],
            ['sql'=>"INSERT INTO DASH_DIAR_CASE_FINDINGS (FINDING_ID,CASE_ID,OUTCOME_CODE,SUMMARY_TEXT,ROOT_CAUSE_TEXT,RECOMMENDED_ACTION_TEXT,REQUIRES_CORRECTIVE_ACTION,STATUS_CODE,PROPOSED_BY,PROPOSED_AT,UPDATED_BY,UPDATED_AT) VALUES (DASH_DIAR_FINDING_SEQ.NEXTVAL,:case_id,:outcome,:summary_text,:root_cause,:recommended_action,:requires_action,'SUBMITTED',:actor,SYSDATE,:actor,SYSDATE)", 'params'=>['case_id'=>$caseId,'outcome'=>$outcome,'summary_text'=>$this->clip($summary,4000),'root_cause'=>$this->clip($rootCause,4000),'recommended_action'=>$this->nullableClip($recommendedAction,4000),'requires_action'=>$requiresCorrectiveAction?1:0,'actor'=>$actor]],
            ['sql'=>"UPDATE DASH_DIAR_CASES SET WORKFLOW_STAGE_CODE='FINDING_PROPOSED',INVESTIGATION_OUTCOME=:outcome,RESOLUTION_SUMMARY=:summary_text,LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id", 'params'=>['outcome'=>$outcome,'summary_text'=>$this->clip($summary,4000),'actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,'FINDING_PROPOSED',(string)$case['status_code'],(string)$case['status_code'],$actor,$summary,['old_stage'=>$stage,'new_stage'=>'FINDING_PROPOSED','outcome'=>$outcome,'requires_corrective_action'=>$requiresCorrectiveAction]),
        ]);
    }

    public function startSupervisorReview(string $caseCode, string $actor, array $allowedSites): void
    {
        $case=$this->requireWorkflowCase($caseCode,$allowedSites);
        if($this->currentStage($case)!=='FINDING_PROPOSED') throw new RuntimeException('SUPERVISOR_REVIEW_REQUIRES_PROPOSED_FINDING');
        $this->changeWorkflowStage($case,'SUPERVISOR_REVIEW','SUPERVISOR_REVIEW_STARTED',$actor,'Supervisor review started.','ESCALATED');
    }

    public function supervisorDecision(string $caseCode, string $decision, string $comment, string $actor, array $allowedSites): void
    {
        $case=$this->requireWorkflowCase($caseCode,$allowedSites);
        if($this->currentStage($case)!=='SUPERVISOR_REVIEW') throw new RuntimeException('SUPERVISOR_DECISION_STAGE_REQUIRED');
        $decision=strtoupper(trim($decision));
        if(!in_array($decision,['APPROVED','RETURNED','REJECTED'],true)||trim($comment)==='') throw new RuntimeException('SUPERVISOR_DECISION_FIELDS_REQUIRED');
        $caseId=(int)$case['case_id'];
        $finding=Database::fetchOne($this->controlBranch,"SELECT * FROM (SELECT * FROM DASH_DIAR_CASE_FINDINGS WHERE CASE_ID=:case_id AND STATUS_CODE='SUBMITTED' ORDER BY FINDING_ID DESC) WHERE ROWNUM=1",['case_id'=>$caseId]);
        if(!$finding) throw new RuntimeException('SUBMITTED_FINDING_REQUIRED');
        $newStage=$decision==='APPROVED'?'MANAGEMENT_APPROVAL':'UNDER_REVIEW';
        $findingStatus=$decision==='APPROVED'?'SUPERVISOR_APPROVED':($decision==='REJECTED'?'REJECTED':'RETURNED');
        Database::executeTransaction($this->controlBranch,[
            ['sql'=>"INSERT INTO DASH_DIAR_CASE_APPROVALS (APPROVAL_ID,CASE_ID,FINDING_ID,APPROVAL_LEVEL,DECISION_CODE,DECISION_COMMENT,DECIDED_BY,DECIDED_AT) VALUES (DASH_DIAR_APPROVAL_SEQ.NEXTVAL,:case_id,:finding_id,'SUPERVISOR',:decision,:comment_text,:actor,SYSDATE)",'params'=>['case_id'=>$caseId,'finding_id'=>(int)$finding['finding_id'],'decision'=>$decision,'comment_text'=>$this->clip($comment,4000),'actor'=>$actor]],
            ['sql'=>"UPDATE DASH_DIAR_CASE_FINDINGS SET STATUS_CODE=:finding_status,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE FINDING_ID=:finding_id",'params'=>['finding_status'=>$findingStatus,'actor'=>$actor,'finding_id'=>(int)$finding['finding_id']]],
            ['sql'=>"UPDATE DASH_DIAR_CASES SET WORKFLOW_STAGE_CODE=:new_stage,SUPERVISOR_USERNAME=:actor,STATUS_CODE=CASE WHEN :new_stage='UNDER_REVIEW' THEN 'UNDER_REVIEW' ELSE 'ESCALATED' END,LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id",'params'=>['new_stage'=>$newStage,'actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,'SUPERVISOR_'.$decision,(string)$case['status_code'],$newStage==='UNDER_REVIEW'?'UNDER_REVIEW':'ESCALATED',$actor,$comment,['old_stage'=>'SUPERVISOR_REVIEW','new_stage'=>$newStage,'finding_id'=>(int)$finding['finding_id']]),
        ]);
    }

    public function managementDecision(string $caseCode, string $decision, string $comment, string $actor, array $allowedSites): void
    {
        $case=$this->requireWorkflowCase($caseCode,$allowedSites);
        if($this->currentStage($case)!=='MANAGEMENT_APPROVAL') throw new RuntimeException('MANAGEMENT_APPROVAL_STAGE_REQUIRED');
        $decision=strtoupper(trim($decision));
        if(!in_array($decision,['APPROVED','RETURNED','REJECTED'],true)||trim($comment)==='') throw new RuntimeException('MANAGEMENT_DECISION_FIELDS_REQUIRED');
        $caseId=(int)$case['case_id'];
        $finding=Database::fetchOne($this->controlBranch,"SELECT * FROM (SELECT * FROM DASH_DIAR_CASE_FINDINGS WHERE CASE_ID=:case_id AND STATUS_CODE='SUPERVISOR_APPROVED' ORDER BY FINDING_ID DESC) WHERE ROWNUM=1",['case_id'=>$caseId]);
        if(!$finding) throw new RuntimeException('SUPERVISOR_APPROVED_FINDING_REQUIRED');
        $requires=(int)($finding['requires_corrective_action']??0)===1;
        $newStage=$decision==='APPROVED'?($requires?'CORRECTIVE_ACTION':'CLOSED'):'UNDER_REVIEW';
        $newStatus=$newStage==='CLOSED'?'CLOSED':($newStage==='UNDER_REVIEW'?'UNDER_REVIEW':'CONFIRMED_ANOMALY');
        $findingStatus=$decision==='APPROVED'?'APPROVED':($decision==='REJECTED'?'REJECTED':'RETURNED');
        Database::executeTransaction($this->controlBranch,[
            ['sql'=>"INSERT INTO DASH_DIAR_CASE_APPROVALS (APPROVAL_ID,CASE_ID,FINDING_ID,APPROVAL_LEVEL,DECISION_CODE,DECISION_COMMENT,DECIDED_BY,DECIDED_AT) VALUES (DASH_DIAR_APPROVAL_SEQ.NEXTVAL,:case_id,:finding_id,'MANAGEMENT',:decision,:comment_text,:actor,SYSDATE)",'params'=>['case_id'=>$caseId,'finding_id'=>(int)$finding['finding_id'],'decision'=>$decision,'comment_text'=>$this->clip($comment,4000),'actor'=>$actor]],
            ['sql'=>"UPDATE DASH_DIAR_CASE_FINDINGS SET STATUS_CODE=:finding_status,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE FINDING_ID=:finding_id",'params'=>['finding_status'=>$findingStatus,'actor'=>$actor,'finding_id'=>(int)$finding['finding_id']]],
            ['sql'=>"UPDATE DASH_DIAR_CASES SET WORKFLOW_STAGE_CODE=:new_stage,MANAGEMENT_APPROVER=:actor,STATUS_CODE=:new_status,CLOSED_BY=CASE WHEN :new_stage='CLOSED' THEN :actor ELSE NULL END,CLOSED_AT=CASE WHEN :new_stage='CLOSED' THEN SYSDATE ELSE NULL END,LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id",'params'=>['new_stage'=>$newStage,'new_status'=>$newStatus,'actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,'MANAGEMENT_'.$decision,(string)$case['status_code'],$newStatus,$actor,$comment,['old_stage'=>'MANAGEMENT_APPROVAL','new_stage'=>$newStage,'finding_id'=>(int)$finding['finding_id'],'requires_corrective_action'=>$requires]),
        ]);
    }

    public function createCorrectiveAction(string $caseCode, string $title, string $description, string $owner, string $dueDate, string $actor, array $allowedSites): void
    {
        $case=$this->requireWorkflowCase($caseCode,$allowedSites);
        if($this->currentStage($case)!=='CORRECTIVE_ACTION') throw new RuntimeException('CORRECTIVE_ACTION_STAGE_REQUIRED');
        if(trim($title)===''||trim($owner)==='') throw new RuntimeException('CORRECTIVE_ACTION_FIELDS_REQUIRED');
        $caseId=(int)$case['case_id'];
        Database::executeTransaction($this->controlBranch,[
            ['sql'=>"INSERT INTO DASH_DIAR_CORRECTIVE_ACTIONS (ACTION_ID,CASE_ID,ACTION_TITLE,ACTION_DESCRIPTION,ACTION_OWNER,DUE_AT,STATUS_CODE,CREATED_BY,CREATED_AT) VALUES (DASH_DIAR_CORR_ACTION_SEQ.NEXTVAL,:case_id,:title_text,:description_text,:owner_name,TO_DATE(:due_at,'YYYY-MM-DD HH24:MI:SS'),'OPEN',:actor,SYSDATE)",'params'=>['case_id'=>$caseId,'title_text'=>$this->clip($title,500),'description_text'=>$this->nullableClip($description,4000),'owner_name'=>$this->clip($owner,128),'due_at'=>$this->oracleDate($dueDate),'actor'=>$actor]],
            ['sql'=>"UPDATE DASH_DIAR_CASES SET LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id",'params'=>['actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,'CORRECTIVE_ACTION_CREATED',(string)$case['status_code'],(string)$case['status_code'],$actor,$title,['stage'=>'CORRECTIVE_ACTION','owner'=>$owner,'due_at'=>$dueDate]),
        ]);
    }

    public function updateCorrectiveAction(string $caseCode, int $actionId, string $status, string $completionNote, string $actor, array $allowedSites): void
    {
        $case=$this->requireWorkflowCase($caseCode,$allowedSites);
        $status=strtoupper(trim($status));
        if($actionId<1||!in_array($status,['OPEN','IN_PROGRESS','COMPLETED','CANCELLED'],true)) throw new RuntimeException('INVALID_CORRECTIVE_ACTION_UPDATE');
        if($status==='COMPLETED'&&trim($completionNote)==='') throw new RuntimeException('CORRECTIVE_COMPLETION_NOTE_REQUIRED');
        $caseId=(int)$case['case_id'];
        $row=Database::fetchOne($this->controlBranch,"SELECT ACTION_ID FROM DASH_DIAR_CORRECTIVE_ACTIONS WHERE ACTION_ID=:action_id AND CASE_ID=:case_id",['action_id'=>$actionId,'case_id'=>$caseId]);
        if(!$row) throw new RuntimeException('CORRECTIVE_ACTION_NOT_FOUND');
        Database::executeTransaction($this->controlBranch,[
            ['sql'=>"UPDATE DASH_DIAR_CORRECTIVE_ACTIONS SET STATUS_CODE=:status_code,COMPLETION_NOTE=:completion_note,COMPLETED_BY=CASE WHEN :status_complete='COMPLETED' THEN :actor ELSE NULL END,COMPLETED_AT=CASE WHEN :status_complete='COMPLETED' THEN SYSDATE ELSE NULL END WHERE ACTION_ID=:action_id AND CASE_ID=:case_id",'params'=>['status_code'=>$status,'completion_note'=>$this->nullableClip($completionNote,4000),'status_complete'=>$status,'actor'=>$actor,'action_id'=>$actionId,'case_id'=>$caseId]],
            ['sql'=>"UPDATE DASH_DIAR_CASES SET LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id",'params'=>['actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,'CORRECTIVE_ACTION_'.$status,(string)$case['status_code'],(string)$case['status_code'],$actor,$completionNote,['stage'=>'CORRECTIVE_ACTION','action_id'=>$actionId]),
        ]);
    }

    public function closeCase(string $caseCode, string $summary, string $actor, array $allowedSites): void
    {
        $case=$this->requireWorkflowCase($caseCode,$allowedSites);
        $stage=$this->currentStage($case);
        if(!in_array($stage,['CORRECTIVE_ACTION','MANAGEMENT_APPROVAL'],true)) throw new RuntimeException('CASE_CLOSE_STAGE_NOT_ALLOWED');
        if(trim($summary)==='') throw new RuntimeException('CLOSURE_SUMMARY_REQUIRED');
        $caseId=(int)$case['case_id'];
        if($stage==='CORRECTIVE_ACTION'){
            $open=Database::fetchOne($this->controlBranch,"SELECT COUNT(*) open_count FROM DASH_DIAR_CORRECTIVE_ACTIONS WHERE CASE_ID=:case_id AND STATUS_CODE IN ('OPEN','IN_PROGRESS')",['case_id'=>$caseId]);
            if((int)($open['open_count']??0)>0) throw new RuntimeException('OPEN_CORRECTIVE_ACTIONS_PREVENT_CLOSE');
        }
        Database::executeTransaction($this->controlBranch,[
            ['sql'=>"UPDATE DASH_DIAR_CASES SET WORKFLOW_STAGE_CODE='CLOSED',STATUS_CODE='CLOSED',RESOLUTION_SUMMARY=:summary_text,CLOSED_BY=:actor,CLOSED_AT=SYSDATE,LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id",'params'=>['summary_text'=>$this->clip($summary,4000),'actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,'CASE_CLOSED',(string)$case['status_code'],'CLOSED',$actor,$summary,['old_stage'=>$stage,'new_stage'=>'CLOSED']),
        ]);
    }

    public function reopenCase(string $caseCode, string $reason, string $actor, array $allowedSites): void
    {
        $case=$this->requireWorkflowCase($caseCode,$allowedSites);
        if($this->currentStage($case)!=='CLOSED'||trim($reason)==='') throw new RuntimeException('REOPEN_REASON_REQUIRED');
        $caseId=(int)$case['case_id'];
        Database::executeTransaction($this->controlBranch,[
            ['sql'=>"UPDATE DASH_DIAR_CASES SET WORKFLOW_STAGE_CODE='UNDER_REVIEW',STATUS_CODE='UNDER_REVIEW',REOPEN_COUNT=NVL(REOPEN_COUNT,0)+1,CLOSED_BY=NULL,CLOSED_AT=NULL,LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id",'params'=>['actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,'CASE_REOPENED','CLOSED','UNDER_REVIEW',$actor,$reason,['old_stage'=>'CLOSED','new_stage'=>'UNDER_REVIEW']),
        ]);
    }

    /** @return array<string,mixed> */
    private function requireWorkflowCase(string $caseCode, array $allowedSites, array $accessScope = []): array
    {
        if(!$this->workflowSchemaReady()) throw new RuntimeException('WORKFLOW_SCHEMA_NOT_READY');
        $case=$this->find($caseCode,$allowedSites);
        if(!$case) throw new RuntimeException('CASE_NOT_FOUND_OR_NOT_ALLOWED');
        return $case;
    }

    private function currentStage(array $case): string
    {
        return strtoupper((string)($case['workflow_stage_code']??$this->legacyWorkflowStage($case)));
    }

    private function legacyWorkflowStage(array $case): string
    {
        $status=strtoupper((string)($case['status_code']??'NEW'));
        if($status==='CLOSED') return 'CLOSED';
        if($status==='ESCALATED') return 'SUPERVISOR_REVIEW';
        if($status==='EVIDENCE_REQUESTED') return 'INFORMATION_REQUIRED';
        if(in_array($status,['EXPLAINED_ACTIVITY','CONFIRMED_ANOMALY','FALSE_POSITIVE'],true)) return 'FINDING_PROPOSED';
        if($status==='UNDER_REVIEW') return 'UNDER_REVIEW';
        return !empty($case['assigned_to'])?'ASSIGNED':'NEW';
    }

    private function slaStatus(array $case): string
    {
        if($this->currentStage($case)==='CLOSED') return 'COMPLETED';
        $due=trim((string)($case['due_at']??''));
        if($due==='') return 'NO_DUE_DATE';
        $ts=strtotime($due);
        if($ts===false) return 'NO_DUE_DATE';
        if($ts<time()) return 'OVERDUE';
        if($ts<time()+86400) return 'DUE_SOON';
        return 'ON_TRACK';
    }

    private function changeWorkflowStage(array $case,string $newStage,string $action,string $actor,string $note,string $legacyStatus='UNDER_REVIEW'): void
    {
        $caseId=(int)$case['case_id'];$oldStage=$this->currentStage($case);
        Database::executeTransaction($this->controlBranch,[
            ['sql'=>"UPDATE DASH_DIAR_CASES SET WORKFLOW_STAGE_CODE=:new_stage,STATUS_CODE=:legacy_status,LAST_ACTION_AT=SYSDATE,UPDATED_BY=:actor,UPDATED_AT=SYSDATE WHERE CASE_ID=:case_id",'params'=>['new_stage'=>$newStage,'legacy_status'=>$legacyStatus,'actor'=>$actor,'case_id'=>$caseId]],
            $this->historyStatement($caseId,$action,(string)$case['status_code'],$legacyStatus,$actor,$note,['old_stage'=>$oldStage,'new_stage'=>$newStage]),
        ]);
    }

    /** @return array{sql:string,params:array<string,mixed>} */
    private function historyStatement(int $caseId,string $action,string $oldStatus,string $newStatus,string $actor,string $note,array $detail=[]): array
    {
        return ['sql'=>"INSERT INTO DASH_DIAR_CASE_HISTORY (HISTORY_ID,CASE_ID,ACTION_CODE,OLD_STATUS_CODE,NEW_STATUS_CODE,PERFORMED_BY,PERFORMED_AT,NOTE_TEXT,DETAIL_JSON) VALUES (DASH_DIAR_HISTORY_SEQ.NEXTVAL,:case_id,:action_code,:old_status,:new_status,:actor,SYSDATE,:note_text,:detail_json)",'params'=>['case_id'=>$caseId,'action_code'=>$this->clip($action,60),'old_status'=>$oldStatus,'new_status'=>$newStatus,'actor'=>$actor,'note_text'=>$this->clip($note,4000),'detail_json'=>$this->historyJson($detail)]];
    }

    private function historyJson(array $detail): string
    {
        $json=json_encode($detail,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
        return $this->clip(is_string($json)?$json:'{}',3900);
    }

    private function oracleDate(string $value)
    {
        $value=trim($value);
        if($value==='') return null;
        $ts=strtotime($value);
        if($ts===false) throw new RuntimeException('INVALID_DUE_DATE');
        return date('Y-m-d H:i:s',$ts);
    }

    /**
     * Retrieves exact, read-only hospital-system movement references for the
     * selected case or correlated event. No result is persisted and no source
     * operational table is modified.
     *
     * @param array<string,mixed> $case
     * @param array<string,mixed> $source
     * @return array{rows:array<int,array<string,mixed>>,total:int,limit:int,error:string}
     */
    private function sourceMovementReferences(array $case, array $source): array
    {
        $limit = 500;
        $siteCode = strtolower(trim((string)($case['site_code'] ?? '')));
        if (!in_array($siteCode, ['njch', 'mnh'], true)) {
            return ['rows' => [], 'total' => 0, 'limit' => $limit, 'error' => 'SOURCE_BRANCH_NOT_SUPPORTED'];
        }

        $branchId = trim((string)($case['branch_id'] ?? ''));
        $storeCode = trim((string)($case['store_code'] ?? ''));
        $calendarDay = trim((string)($case['calendar_day'] ?? ''));
        if ($branchId === '' || $storeCode === '' || $calendarDay === '') {
            return ['rows' => [], 'total' => 0, 'limit' => $limit, 'error' => 'SOURCE_REFERENCE_KEY_INCOMPLETE'];
        }

        $params = [
            'branch_id' => $branchId,
            'store_code' => $storeCode,
            'calendar_day' => substr($calendarDay, 0, 10),
        ];
        $filters = [
            'TO_CHAR(T.BRANCHID)=:branch_id',
            'TO_CHAR(T.STORECODE)=:store_code',
            "T.TRANSDATE>=TO_DATE(:calendar_day,'YYYY-MM-DD')",
            "T.TRANSDATE<TO_DATE(:calendar_day,'YYYY-MM-DD')+1",
            'T.TRANSSTATUS=1',
            'I.ITEMSTORETYPE=2',
        ];

        $storeTransCode = trim((string)($source['storetranscode'] ?? $case['storetranscode'] ?? ''));
        if ($storeTransCode !== '') {
            $params['storetranscode'] = $storeTransCode;
            $filters[] = 'TO_CHAR(T.STORETRANSCODE)=:storetranscode';
        }
        $storeTransEffect = trim((string)($source['storetranseffect'] ?? $case['storetranseffect'] ?? ''));
        if ($storeTransEffect !== '') {
            $params['storetranseffect'] = $storeTransEffect;
            $filters[] = 'TO_CHAR(NVL(TT.STORETRANSEFFECT,-999))=:storetranseffect';
        }

        $entityType = strtoupper(trim((string)($case['entity_type'] ?? 'CASE')));
        if ($entityType === 'CASE') {
            $itemCode = trim((string)($case['item_code'] ?? ''));
            if ($itemCode === '') {
                return ['rows' => [], 'total' => 0, 'limit' => $limit, 'error' => 'SOURCE_ITEM_CODE_MISSING'];
            }
            $params['item_code'] = $itemCode;
            $filters[] = 'TO_CHAR(TI.ITEMCODE)=:item_code';
            $unitCode = trim((string)($case['raw_unit_code'] ?? ''));
            if ($unitCode !== '') {
                $params['unit_code'] = $unitCode;
                $filters[] = 'TO_CHAR(TI.UNITCODE)=:unit_code';
            } else {
                $filters[] = 'TI.UNITCODE IS NULL';
            }
        } elseif (strtoupper(trim((string)($case['event_family'] ?? ''))) === 'INVENTORY_RECON') {
            $filters[] = 'T.STORETRANSCODE IN (11,17,18)';
        }

        $movementFamily = "CASE
            WHEN T.STORETRANSCODE=11 THEN 'INVENTORY'
            WHEN T.STORETRANSCODE=17 THEN 'RECON_ADD'
            WHEN T.STORETRANSCODE=18 THEN 'RECON_SUB'
            WHEN T.STORETRANSCODE=9 THEN 'TRANSFER_OUT'
            WHEN T.STORETRANSCODE=1 THEN 'OPENING_ADD'
            WHEN T.STORETRANSCODE IN (2,25,26) THEN 'RECEIPT_IN'
            WHEN T.STORETRANSCODE IN (4,5,6,16,20,22,23,29,30,32) THEN 'RETURN_IN'
            WHEN T.STORETRANSCODE IN (3,27) THEN 'RETURN_OUT'
            WHEN T.STORETRANSCODE IN (7,8,10,12,13,14,19,21,24,28,31) THEN 'ISSUE_OUT'
            WHEN TT.STORETRANSEFFECT=1 THEN 'OTHER_IN'
            WHEN TT.STORETRANSEFFECT=2 THEN 'OTHER_OUT'
            WHEN TT.STORETRANSEFFECT=0 THEN 'NEUTRAL_OTHER'
            ELSE 'UNKNOWN' END";

        $baseSql = "SELECT
            TO_CHAR(T.BRANCHID)||':'||TO_CHAR(T.STORETRANSCODE)||':'||TO_CHAR(T.TRANSSERIAL) reference_key,
            T.TRANSSERIAL trans_serial,
            T.STORETRANSCODE store_trans_code,
            TT.STORETRANSARNAME movement_name_ar,
            TT.STORETRANSENNAME movement_name_en,
            " . $movementFamily . " movement_family,
            TT.STORETRANSEFFECT effect_raw,
            CASE TT.STORETRANSEFFECT WHEN 1 THEN 'IN' WHEN 2 THEN 'OUT' WHEN 0 THEN 'NEUTRAL' ELSE 'UNKNOWN' END effect_direction,
            TO_CHAR(T.TRANSDATE,'YYYY-MM-DD HH24:MI:SS') trans_datetime,
            T.STORECODE store_code,
            S.STOREARNAME store_name_ar,
            S.STOREENNAME store_name_en,
            NVL(TI.USERID,T.USERID) effective_user_id,
            T.TRANSSTATUS trans_status_raw,
            COUNT(*) line_count,
            MIN(TI.SER) first_line_ser,
            MAX(TI.SER) last_line_ser,
            SUM(NVL(TI.ACTQUANTITY,0)) raw_qty,
            SUM(NVL(TI.ACTQUANTITY,0)*NVL(TI.UNITCOSTPRICE,0)) raw_value,
            SUM(CASE WHEN TO_NUMBER(TO_CHAR(T.TRANSDATE,'HH24')) BETWEEN 0 AND 5
                       OR TO_NUMBER(TO_CHAR(T.TRANSDATE,'HH24')) BETWEEN 18 AND 23
                     THEN 1 ELSE 0 END) after_hours_lines
        FROM MMSTORETRANSACTIONS T
        JOIN MMSTORETRANSITEMS TI
          ON TI.BRANCHID=T.BRANCHID
         AND TI.STORETRANSCODE=T.STORETRANSCODE
         AND TI.TRANSSERIAL=T.TRANSSERIAL
        JOIN MMITEMSMAINDATA I ON I.ITEMCODE=TI.ITEMCODE
        LEFT JOIN MMSTORETRANSTYPE TT ON TT.STORETRANSCODE=T.STORETRANSCODE
        LEFT JOIN MMSTOREMAINDATA S ON S.BRANCHID=T.BRANCHID AND S.STORECODE=T.STORECODE
        WHERE " . implode(' AND ', $filters) . "
        GROUP BY T.BRANCHID,T.TRANSSERIAL,T.STORETRANSCODE,
            TT.STORETRANSARNAME,TT.STORETRANSENNAME,TT.STORETRANSEFFECT,
            T.TRANSDATE,T.STORECODE,S.STOREARNAME,S.STOREENNAME,
            NVL(TI.USERID,T.USERID),T.TRANSSTATUS";

        try {
            $countRow = Database::fetchOne($siteCode, 'SELECT COUNT(*) total_rows FROM (' . $baseSql . ')', $params);
            $rows = Database::fetchAll($siteCode, 'SELECT * FROM (' . $baseSql . ' ORDER BY trans_datetime,store_trans_code,trans_serial) WHERE ROWNUM<=' . $limit, $params);
            return [
                'rows' => is_array($rows) ? $rows : [],
                'total' => (int)($countRow['total_rows'] ?? count($rows)),
                'limit' => $limit,
                'error' => '',
            ];
        } catch (Throwable $error) {
            return [
                'rows' => [],
                'total' => 0,
                'limit' => $limit,
                'error' => 'SOURCE_REFERENCE_READ_UNAVAILABLE',
            ];
        }
    }

    private function assigneeCanAccessSite(string $username, string $siteCode): bool
    {
        $siteCode = strtolower(trim($siteCode));
        if (!in_array($siteCode, ['njch','mnh'], true)) {
            return false;
        }
        $row = Database::fetchOne($this->controlBranch, "SELECT CASE WHEN
            EXISTS (
                SELECT 1
                FROM DASH_ACL_USER_ROLES ur
                JOIN DASH_ACL_ROLES r ON r.ROLE_CODE=ur.ROLE_CODE
                WHERE UPPER(ur.USERNAME)=UPPER(:username_role)
                  AND NVL(ur.IS_ACTIVE,1)=1
                  AND NVL(r.IS_ACTIVE,1)=1
                  AND (
                        UPPER(r.ROLE_CODE)='SITE_ADMIN'
                        OR NVL(r.ALL_BRANCHES,0)=1
                        OR EXISTS (
                            SELECT 1 FROM DASH_ACL_USER_BRANCHES ub
                            WHERE UPPER(ub.USERNAME)=UPPER(ur.USERNAME)
                              AND LOWER(ub.HOSPITAL_CODE)=:site_code
                              AND NVL(ub.IS_ACTIVE,1)=1
                        )
                  )
            )
            OR (
                NOT EXISTS (
                    SELECT 1 FROM DASH_ACL_USER_ROLES ur2
                    WHERE UPPER(ur2.USERNAME)=UPPER(:username_no_role)
                      AND NVL(ur2.IS_ACTIVE,1)=1
                )
                AND EXISTS (
                    SELECT 1 FROM SECURITY_USERS su
                    WHERE UPPER(su.NAME)=UPPER(:username_legacy)
                      AND NVL(su.DASHBOARD_ROLE,0)=1
                )
            )
            THEN 1 ELSE 0 END access_ok
        FROM dual", [
            'username_role' => $username,
            'site_code' => $siteCode,
            'username_no_role' => $username,
            'username_legacy' => $username,
        ]);
        return (int)($row['access_ok'] ?? 0) === 1;
    }

    /** @return array<int,string> */
    public function allowedTransitions(string $status): array
    {
        $map = [
            'NEW' => ['UNDER_REVIEW'],
            'UNDER_REVIEW' => ['EVIDENCE_REQUESTED','EXPLAINED_ACTIVITY','CONFIRMED_ANOMALY','FALSE_POSITIVE','ESCALATED'],
            'EVIDENCE_REQUESTED' => ['UNDER_REVIEW','EXPLAINED_ACTIVITY','CONFIRMED_ANOMALY','FALSE_POSITIVE','ESCALATED'],
            'EXPLAINED_ACTIVITY' => ['CLOSED','UNDER_REVIEW'],
            'CONFIRMED_ANOMALY' => ['ESCALATED','CLOSED','UNDER_REVIEW'],
            'FALSE_POSITIVE' => ['CLOSED','UNDER_REVIEW'],
            'ESCALATED' => ['CLOSED','UNDER_REVIEW'],
            'CLOSED' => ['UNDER_REVIEW'],
        ];
        return $map[strtoupper($status)] ?? [];
    }

    /** @return array{0:string,1:array<string,mixed>} */
    private function buildFilters(array $filters, array $allowedSites): array
    {
        [$where, $params] = $this->siteScope($allowedSites, 'c');
        foreach (['status' => 'STATUS_CODE','severity' => 'SEVERITY_CODE','entity' => 'ENTITY_TYPE','site' => 'SITE_CODE'] as $key => $column) {
            $value = strtoupper(trim((string)($filters[$key] ?? '')));
            if ($value !== '') {
                $where .= ' AND c.' . $column . '=:' . $key;
                $params[$key] = $value;
            }
        }
        if($this->workflowSchemaReady()){
            $stage=strtoupper(trim((string)($filters['stage']??'')));
            if($stage!==''){$where.=' AND c.WORKFLOW_STAGE_CODE=:stage';$params['stage']=$stage;}
            $sla=strtoupper(trim((string)($filters['sla']??'')));
            if($sla==='OVERDUE') $where.=" AND c.DUE_AT IS NOT NULL AND c.DUE_AT<SYSDATE AND c.WORKFLOW_STAGE_CODE<>'CLOSED'";
            elseif($sla==='DUE_SOON') $where.=" AND c.DUE_AT IS NOT NULL AND c.DUE_AT>=SYSDATE AND c.DUE_AT<SYSDATE+1 AND c.WORKFLOW_STAGE_CODE<>'CLOSED'";
            elseif($sla==='ON_TRACK') $where.=" AND (c.DUE_AT IS NULL OR c.DUE_AT>=SYSDATE+1) AND c.WORKFLOW_STAGE_CODE<>'CLOSED'";
        }
        $store = trim((string)($filters['store'] ?? ''));
        if ($store !== '') {
            $where .= ' AND c.STORE_CODE=:store';
            $params['store'] = $store;
        }
        $assignee = trim((string)($filters['assignee'] ?? ''));
        if ($assignee === '__UNASSIGNED__') {
            $where .= ' AND a.ASSIGNMENT_ID IS NULL';
        } elseif ($assignee !== '') {
            $where .= ' AND UPPER(a.ASSIGNED_TO)=UPPER(:assignee)';
            $params['assignee'] = $assignee;
        }
        $search = trim((string)($filters['q'] ?? ''));
        if ($search !== '') {
            $where .= " AND (UPPER(c.CASE_CODE) LIKE UPPER(:search)
                OR UPPER(NVL(c.ITEM_CODE,' ')) LIKE UPPER(:search)
                OR UPPER(NVL(c.ITEM_NAME_EN,' ')) LIKE UPPER(:search)
                OR UPPER(NVL(c.ITEM_NAME_AR,' ')) LIKE UPPER(:search)
                OR UPPER(NVL(c.STORE_NAME_EN,' ')) LIKE UPPER(:search)
                OR UPPER(NVL(c.STORE_NAME_AR,' ')) LIKE UPPER(:search))";
            $params['search'] = '%' . $search . '%';
        }
        return [$where, $params];
    }

    /** @return array{0:string,1:array<string,mixed>} */
    private function caseAccessScope(array $accessScope, string $assignmentAlias): array
    {
        $mode = strtolower(trim((string)($accessScope['mode'] ?? 'all')));
        if ($mode === 'none') {
            return [' AND 1=0', []];
        }
        if ($mode === 'assigned') {
            $username = trim((string)($accessScope['username'] ?? ''));
            if ($username === '') {
                return [' AND 1=0', []];
            }
            return [' AND UPPER(' . $assignmentAlias . '.ASSIGNED_TO)=UPPER(:diar_scope_username)', [
                'diar_scope_username' => $username,
            ]];
        }
        return ['', []];
    }

    /** @return array{0:string,1:array<string,mixed>} */
    private function siteScope(array $allowedSites, string $alias): array
    {
        $sites = [];
        foreach ($allowedSites as $site) {
            $site = strtoupper(trim((string)$site));
            if (in_array($site, ['NJCH','MNH'], true) && !in_array($site, $sites, true)) {
                $sites[] = $site;
            }
        }
        if ($sites === []) {
            return [' AND 1=0', []];
        }
        $parts = [];
        $params = [];
        foreach ($sites as $i => $site) {
            $key = 'allowed_site_' . $i;
            $parts[] = ':' . $key;
            $params[$key] = $site;
        }
        return [' AND ' . $alias . '.SITE_CODE IN (' . implode(',', $parts) . ')', $params];
    }

    private function clip(string $value, int $limit): string
    {
        $value = trim($value);
        if (strlen($value) <= $limit) {
            return $value;
        }
        $cut = substr($value, 0, $limit);
        while ($cut !== '' && preg_match('//u', $cut) !== 1) {
            $cut = substr($cut, 0, -1);
        }
        return $cut;
    }

    private function nullableClip(string $value, int $limit): ?string
    {
        $value = trim($value);
        return $value === '' ? null : $this->clip($value, $limit);
    }
}
