<?php

declare(strict_types=1);

require dirname(__DIR__) . '/app/bootstrap.php';

use App\Core\Database;
$branch = 'njch';
$seedRoot = __DIR__ . DIRECTORY_SEPARATOR . 'diar4b-seed';
$result = [
    'schema' => 'DIAR4B_INVESTIGATION_APPLY_V1',
    'captured_at' => date(DATE_ATOM),
    'target_database' => 'ORANJC',
    'target_schema' => 'WATANY',
    'source_run_code' => '',
    'status' => 'FAIL',
    'idempotent' => false,
    'counts' => [],
    'gates' => [],
    'oracle_operational_writes' => 'NONE',
    'control_table_writes' => 'DASH_DIAR_*_ONLY',
];

function ddl(string $branch, string $sql): void
{
    Database::execute($branch, $sql);
}

function createTable(string $branch, string $name, string $definition): void
{
    $safe = str_replace("'", "''", 'CREATE TABLE ' . $name . ' (' . $definition . ')');
    ddl($branch, "BEGIN EXECUTE IMMEDIATE '" . $safe . "'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -955 THEN RAISE; END IF; END;");
}

function createSequence(string $branch, string $name): void
{
    ddl($branch, "BEGIN EXECUTE IMMEDIATE 'CREATE SEQUENCE " . $name . " START WITH 1 INCREMENT BY 1 NOCACHE'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -955 THEN RAISE; END IF; END;");
}

function createIndex(string $branch, string $name, string $sql): void
{
    $safe = str_replace("'", "''", $sql);
    ddl($branch, "DECLARE n NUMBER; BEGIN SELECT COUNT(*) INTO n FROM USER_INDEXES WHERE INDEX_NAME='" . strtoupper($name) . "'; IF n=0 THEN EXECUTE IMMEDIATE '" . $safe . "'; END IF; END;");
}

function readJsonFile(string $path): array
{
    if (!is_file($path)) {
        throw new RuntimeException('Required source file not found: ' . $path);
    }
    $data = json_decode((string)file_get_contents($path), true);
    if (!is_array($data)) {
        throw new RuntimeException('Invalid JSON source file: ' . $path);
    }
    return $data;
}

/** @return array<int,array<string,string>> */
function readCsv(string $path): array
{
    if (!is_file($path)) {
        throw new RuntimeException('Required CSV source file not found: ' . $path);
    }
    $handle = fopen($path, 'rb');
    if (!$handle) {
        throw new RuntimeException('Unable to open CSV: ' . $path);
    }
    $header = fgetcsv($handle);
    if (!is_array($header)) {
        fclose($handle);
        throw new RuntimeException('CSV header missing: ' . $path);
    }
    if (isset($header[0])) {
        $header[0] = preg_replace('/^\xEF\xBB\xBF/', '', (string)$header[0]);
    }
    $rows = [];
    while (($values = fgetcsv($handle)) !== false) {
        if ($values === [null] || $values === []) {
            continue;
        }
        $values = array_pad($values, count($header), '');
        $row = [];
        foreach ($header as $i => $column) {
            $row[(string)$column] = isset($values[$i]) ? trim((string)$values[$i]) : '';
        }
        $rows[] = $row;
    }
    fclose($handle);
    return $rows;
}

function safeSubstr(string $value, int $limit): string
{
    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;
}

function textValue(array $row, string $key, int $limit = 4000): ?string
{
    $value = trim((string)($row[$key] ?? ''));
    if ($value === '') {
        return null;
    }
    return safeSubstr($value, $limit);
}

function numberValue(array $row, string $key): ?string
{
    $value = trim((string)($row[$key] ?? ''));
    if ($value === '' || !is_numeric($value)) {
        return null;
    }
    return $value;
}

function sourceRowHash(array $row): string
{
    ksort($row);
    return hash('sha256', json_encode($row, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES));
}

/** @param array<int,array{sql:string,params:array<string,mixed>}> $statements */
function executeChunks(string $branch, array $statements, int $chunkSize = 100): void
{
    foreach (array_chunk($statements, $chunkSize) as $chunk) {
        Database::executeTransaction($branch, $chunk);
    }
}

function cleanupImport(string $branch, int $importRunId): void
{
    $caseSelect = 'SELECT CASE_ID FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:import_run_id';
    foreach ([
        'DELETE FROM DASH_DIAR_CASE_EVIDENCE WHERE CASE_ID IN (' . $caseSelect . ')',
        'DELETE FROM DASH_DIAR_CASE_NOTES WHERE CASE_ID IN (' . $caseSelect . ')',
        'DELETE FROM DASH_DIAR_CASE_HISTORY WHERE CASE_ID IN (' . $caseSelect . ')',
        'DELETE FROM DASH_DIAR_CASE_ASSIGNMENTS WHERE CASE_ID IN (' . $caseSelect . ')',
        'DELETE FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:import_run_id',
        'DELETE FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:import_run_id',
        'DELETE FROM DASH_DIAR_IMPORT_RUNS WHERE IMPORT_RUN_ID=:import_run_id',
    ] as $sql) {
        Database::execute($branch, $sql, ['import_run_id' => $importRunId]);
    }
}

function installSchema(string $branch): void
{
    createTable($branch, 'DASH_DIAR_IMPORT_RUNS', "
        IMPORT_RUN_ID NUMBER NOT NULL, SOURCE_RUN_CODE VARCHAR2(100) NOT NULL,
        SOURCE_ENGINE_REV VARCHAR2(40) NOT NULL, SOURCE_SHA256 VARCHAR2(64) NOT NULL,
        SOURCE_FILE_NAME VARCHAR2(260) NOT NULL, QUEUE_ENTITY_COUNT NUMBER DEFAULT 0 NOT NULL,
        SOURCE_CASE_COUNT NUMBER DEFAULT 0 NOT NULL, EVENT_COUNT NUMBER DEFAULT 0 NOT NULL,
        EVENT_MEMBER_COUNT NUMBER DEFAULT 0 NOT NULL, SUPPRESSED_COUNT NUMBER DEFAULT 0 NOT NULL,
        STATUS_CODE VARCHAR2(20) NOT NULL, STARTED_AT DATE DEFAULT SYSDATE NOT NULL,
        COMPLETED_AT DATE, IMPORTED_BY VARCHAR2(128) NOT NULL, ERROR_MESSAGE VARCHAR2(2000),
        CONSTRAINT PK_DIAR_IMPORT_RUNS PRIMARY KEY (IMPORT_RUN_ID),
        CONSTRAINT UX_DIAR_IMPORT_SOURCE UNIQUE (SOURCE_RUN_CODE),
        CONSTRAINT CK_DIAR_IMPORT_STATUS CHECK (STATUS_CODE IN ('RUNNING','PASS','FAIL','ROLLED_BACK'))
    ");
    createTable($branch, 'DASH_DIAR_SOURCE_CASES', "
        SOURCE_CASE_ID NUMBER NOT NULL, IMPORT_RUN_ID NUMBER NOT NULL,
        SOURCE_CASE_CODE VARCHAR2(40) NOT NULL, SOURCE_ROW_SHA256 VARCHAR2(64) NOT NULL,
        SITE_CODE VARCHAR2(20) NOT NULL, BRANCH_ID VARCHAR2(30), STORE_CODE VARCHAR2(40),
        STORE_NAME_AR VARCHAR2(250), STORE_NAME_EN VARCHAR2(250), ITEM_CODE VARCHAR2(60),
        ITEM_NAME_AR VARCHAR2(500), ITEM_NAME_EN VARCHAR2(500), RAW_UNIT_CODE VARCHAR2(40),
        RAW_UNIT_NAME_AR VARCHAR2(200), RAW_UNIT_NAME_EN VARCHAR2(200), CALENDAR_DAY DATE NOT NULL,
        FIRST_TRANS_AT DATE, LAST_TRANS_AT DATE, MOVEMENT_LINES NUMBER, TRANSACTION_COUNT NUMBER,
        DISTINCT_USERS NUMBER, INBOUND_RAW_QTY NUMBER, OUTBOUND_RAW_QTY NUMBER, NET_RAW_QTY NUMBER,
        RAW_VALUE_TOTAL NUMBER, RETURN_IN_QTY NUMBER, RETURN_OUT_QTY NUMBER, TRANSFER_OUT_QTY NUMBER,
        RECON_ADD_QTY NUMBER, RECON_SUB_QTY NUMBER, INVENTORY_EVENTS NUMBER,
        BASELINE_STATUS VARCHAR2(30), RISK_SCORE NUMBER NOT NULL, SEVERITY_CODE VARCHAR2(20) NOT NULL,
        EVIDENCE_STRENGTH VARCHAR2(30), SIGNALS VARCHAR2(4000), REVIEW_ACTION VARCHAR2(200),
        EVENT_CODE VARCHAR2(40), EVENT_TYPE VARCHAR2(60), EVENT_FAMILY VARCHAR2(40),
        EVENT_QUALIFIED VARCHAR2(3), EVENT_MEMBER_COUNT NUMBER, MEMBER_DISPOSITION VARCHAR2(50),
        CORRELATION_REASON VARCHAR2(2000), CREATED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DIAR_SOURCE_CASES PRIMARY KEY (SOURCE_CASE_ID),
        CONSTRAINT FK_DIAR_SRC_IMPORT FOREIGN KEY (IMPORT_RUN_ID) REFERENCES DASH_DIAR_IMPORT_RUNS(IMPORT_RUN_ID),
        CONSTRAINT UX_DIAR_SOURCE_CODE UNIQUE (SOURCE_CASE_CODE),
        CONSTRAINT CK_DIAR_SRC_SEV CHECK (SEVERITY_CODE IN ('INFO','LOW','MEDIUM','HIGH','CRITICAL'))
    ");
    createTable($branch, 'DASH_DIAR_CASES', "
        CASE_ID NUMBER NOT NULL, CASE_CODE VARCHAR2(40) NOT NULL, IMPORT_RUN_ID NUMBER NOT NULL,
        ENTITY_TYPE VARCHAR2(10) NOT NULL, SOURCE_CASE_ID NUMBER, PARENT_EVENT_CASE_ID NUMBER,
        RULE_VERSION_ID NUMBER, SITE_CODE VARCHAR2(20) NOT NULL, BRANCH_ID VARCHAR2(30),
        STORE_CODE VARCHAR2(40), STORE_NAME_AR VARCHAR2(250), STORE_NAME_EN VARCHAR2(250),
        CALENDAR_DAY DATE NOT NULL, EVENT_TYPE VARCHAR2(60), EVENT_FAMILY VARCHAR2(40),
        MEMBER_COUNT NUMBER DEFAULT 1 NOT NULL, SUPPRESSED_MEMBER_COUNT NUMBER DEFAULT 0 NOT NULL,
        OUTLIER_COUNT NUMBER DEFAULT 0 NOT NULL, ITEM_CODE VARCHAR2(60),
        ITEM_NAME_AR VARCHAR2(500), ITEM_NAME_EN VARCHAR2(500), RAW_UNIT_CODE VARCHAR2(40),
        RISK_SCORE NUMBER NOT NULL, SEVERITY_CODE VARCHAR2(20) NOT NULL,
        EVIDENCE_STRENGTH VARCHAR2(30), REPRESENTATIVE_SIGNALS VARCHAR2(4000),
        MEMBER_DISPOSITION VARCHAR2(50), REVIEW_REASON VARCHAR2(2000), REVIEW_ACTION VARCHAR2(500),
        STATUS_CODE VARCHAR2(30) DEFAULT 'NEW' NOT NULL, INVESTIGATION_OUTCOME VARCHAR2(40),
        RESOLUTION_SUMMARY VARCHAR2(4000), ESCALATION_LEVEL VARCHAR2(20), DUE_AT DATE,
        CLOSED_BY VARCHAR2(128), CLOSED_AT DATE, CREATED_AT DATE DEFAULT SYSDATE NOT NULL,
        UPDATED_BY VARCHAR2(128) NOT NULL, UPDATED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DIAR_CASES PRIMARY KEY (CASE_ID),
        CONSTRAINT UX_DIAR_CASE_CODE UNIQUE (CASE_CODE),
        CONSTRAINT FK_DIAR_CASE_IMPORT FOREIGN KEY (IMPORT_RUN_ID) REFERENCES DASH_DIAR_IMPORT_RUNS(IMPORT_RUN_ID),
        CONSTRAINT FK_DIAR_CASE_SOURCE FOREIGN KEY (SOURCE_CASE_ID) REFERENCES DASH_DIAR_SOURCE_CASES(SOURCE_CASE_ID),
        CONSTRAINT FK_DIAR_CASE_PARENT FOREIGN KEY (PARENT_EVENT_CASE_ID) REFERENCES DASH_DIAR_CASES(CASE_ID),
        CONSTRAINT FK_DIAR_CASE_RULEVER FOREIGN KEY (RULE_VERSION_ID) REFERENCES DASH_DIAR_RULE_VERSIONS(VERSION_ID),
        CONSTRAINT CK_DIAR_CASE_ENTITY CHECK (ENTITY_TYPE IN ('CASE','EVENT')),
        CONSTRAINT CK_DIAR_CASE_SEV CHECK (SEVERITY_CODE IN ('INFO','LOW','MEDIUM','HIGH','CRITICAL')),
        CONSTRAINT CK_DIAR_CASE_STATUS CHECK (STATUS_CODE IN ('NEW','UNDER_REVIEW','EVIDENCE_REQUESTED','EXPLAINED_ACTIVITY','CONFIRMED_ANOMALY','FALSE_POSITIVE','ESCALATED','CLOSED')),
        CONSTRAINT CK_DIAR_CASE_OUTCOME CHECK (INVESTIGATION_OUTCOME IS NULL OR INVESTIGATION_OUTCOME IN ('AUTHORIZED_ACTIVITY','OPERATIONAL_ERROR','DOCUMENTATION_GAP','SYSTEM_DATA_ISSUE','POLICY_VIOLATION','POTENTIAL_MISUSE','INSUFFICIENT_EVIDENCE')),
        CONSTRAINT CK_DIAR_CASE_ESC CHECK (ESCALATION_LEVEL IS NULL OR ESCALATION_LEVEL IN ('LOW','MEDIUM','HIGH','CRITICAL'))
    ");
    createTable($branch, 'DASH_DIAR_CASE_ASSIGNMENTS', "
        ASSIGNMENT_ID NUMBER NOT NULL, CASE_ID NUMBER NOT NULL, ASSIGNED_TO VARCHAR2(128) NOT NULL,
        ASSIGNED_BY VARCHAR2(128) NOT NULL, ASSIGNED_AT DATE DEFAULT SYSDATE NOT NULL, ENDED_AT DATE,
        STATUS_CODE VARCHAR2(20) DEFAULT 'ACTIVE' NOT NULL, ASSIGNMENT_REASON VARCHAR2(1000),
        CONSTRAINT PK_DIAR_CASE_ASSIGN PRIMARY KEY (ASSIGNMENT_ID),
        CONSTRAINT FK_DIAR_ASSIGN_CASE FOREIGN KEY (CASE_ID) REFERENCES DASH_DIAR_CASES(CASE_ID),
        CONSTRAINT CK_DIAR_ASSIGN_STATUS CHECK (STATUS_CODE IN ('ACTIVE','ENDED'))
    ");
    createTable($branch, 'DASH_DIAR_CASE_HISTORY', "
        HISTORY_ID NUMBER NOT NULL, CASE_ID NUMBER NOT NULL, ACTION_CODE VARCHAR2(60) NOT NULL,
        OLD_STATUS_CODE VARCHAR2(30), NEW_STATUS_CODE VARCHAR2(30), OLD_OUTCOME VARCHAR2(40),
        NEW_OUTCOME VARCHAR2(40), PERFORMED_BY VARCHAR2(128) NOT NULL,
        PERFORMED_AT DATE DEFAULT SYSDATE NOT NULL, NOTE_TEXT VARCHAR2(4000), DETAIL_JSON VARCHAR2(4000),
        CONSTRAINT PK_DIAR_CASE_HISTORY PRIMARY KEY (HISTORY_ID),
        CONSTRAINT FK_DIAR_HIST_CASE FOREIGN KEY (CASE_ID) REFERENCES DASH_DIAR_CASES(CASE_ID)
    ");
    createTable($branch, 'DASH_DIAR_CASE_NOTES', "
        NOTE_ID NUMBER NOT NULL, CASE_ID NUMBER NOT NULL, NOTE_TYPE VARCHAR2(30) NOT NULL,
        NOTE_TEXT VARCHAR2(4000) NOT NULL, IS_PRIVATE NUMBER(1) DEFAULT 0 NOT NULL,
        CREATED_BY VARCHAR2(128) NOT NULL, CREATED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DIAR_CASE_NOTES PRIMARY KEY (NOTE_ID),
        CONSTRAINT FK_DIAR_NOTE_CASE FOREIGN KEY (CASE_ID) REFERENCES DASH_DIAR_CASES(CASE_ID),
        CONSTRAINT CK_DIAR_NOTE_TYPE CHECK (NOTE_TYPE IN ('REVIEW','EVIDENCE_REQUEST','ACTION','MANAGEMENT','GENERAL')),
        CONSTRAINT CK_DIAR_NOTE_PRIVATE CHECK (IS_PRIVATE IN (0,1))
    ");
    createTable($branch, 'DASH_DIAR_CASE_EVIDENCE', "
        EVIDENCE_ID NUMBER NOT NULL, CASE_ID NUMBER NOT NULL, EVIDENCE_TYPE VARCHAR2(40) NOT NULL,
        REFERENCE_NO VARCHAR2(200), DESCRIPTION_TEXT VARCHAR2(2000) NOT NULL,
        FILE_NAME VARCHAR2(260), STORAGE_PATH VARCHAR2(1000), FILE_SHA256 VARCHAR2(64),
        ADDED_BY VARCHAR2(128) NOT NULL, ADDED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DIAR_CASE_EVID PRIMARY KEY (EVIDENCE_ID),
        CONSTRAINT FK_DIAR_EVID_CASE FOREIGN KEY (CASE_ID) REFERENCES DASH_DIAR_CASES(CASE_ID),
        CONSTRAINT CK_DIAR_EVID_TYPE CHECK (EVIDENCE_TYPE IN ('TRANSACTION_REPORT','APPROVAL','INVENTORY_SHEET','USER_STATEMENT','SYSTEM_LOG','POLICY_DOCUMENT','OTHER'))
    ");
    foreach (['DASH_DIAR_IMPORT_SEQ','DASH_DIAR_SOURCE_SEQ','DASH_DIAR_CASE_SEQ','DASH_DIAR_ASSIGN_SEQ','DASH_DIAR_HISTORY_SEQ','DASH_DIAR_NOTE_SEQ','DASH_DIAR_EVID_SEQ'] as $sequence) {
        createSequence($branch, $sequence);
    }
    $indexes = [
        ['IX_DIAR_SRC_IMPORT','CREATE INDEX IX_DIAR_SRC_IMPORT ON DASH_DIAR_SOURCE_CASES (IMPORT_RUN_ID, SITE_CODE)'],
        ['IX_DIAR_SRC_EVENT','CREATE INDEX IX_DIAR_SRC_EVENT ON DASH_DIAR_SOURCE_CASES (EVENT_CODE, MEMBER_DISPOSITION)'],
        ['IX_DIAR_SRC_STORE_DAY','CREATE INDEX IX_DIAR_SRC_STORE_DAY ON DASH_DIAR_SOURCE_CASES (SITE_CODE, STORE_CODE, CALENDAR_DAY)'],
        ['IX_DIAR_CASE_QUEUE','CREATE INDEX IX_DIAR_CASE_QUEUE ON DASH_DIAR_CASES (STATUS_CODE, SEVERITY_CODE, UPDATED_AT)'],
        ['IX_DIAR_CASE_SCOPE','CREATE INDEX IX_DIAR_CASE_SCOPE ON DASH_DIAR_CASES (SITE_CODE, STORE_CODE, CALENDAR_DAY)'],
        ['IX_DIAR_CASE_PARENT','CREATE INDEX IX_DIAR_CASE_PARENT ON DASH_DIAR_CASES (PARENT_EVENT_CASE_ID)'],
        ['IX_DIAR_ASSIGN_ACTIVE','CREATE INDEX IX_DIAR_ASSIGN_ACTIVE ON DASH_DIAR_CASE_ASSIGNMENTS (CASE_ID, STATUS_CODE)'],
        ['UX_DIAR_ASSIGN_ONE_ACTIVE',"CREATE UNIQUE INDEX UX_DIAR_ASSIGN_ONE_ACTIVE ON DASH_DIAR_CASE_ASSIGNMENTS (CASE WHEN STATUS_CODE='ACTIVE' THEN CASE_ID ELSE NULL END)"],
        ['IX_DIAR_HIST_CASE','CREATE INDEX IX_DIAR_HIST_CASE ON DASH_DIAR_CASE_HISTORY (CASE_ID, PERFORMED_AT)'],
        ['IX_DIAR_NOTE_CASE','CREATE INDEX IX_DIAR_NOTE_CASE ON DASH_DIAR_CASE_NOTES (CASE_ID, CREATED_AT)'],
        ['IX_DIAR_EVID_CASE','CREATE INDEX IX_DIAR_EVID_CASE ON DASH_DIAR_CASE_EVIDENCE (CASE_ID, ADDED_AT)'],
    ];
    foreach ($indexes as $index) {
        createIndex($branch, $index[0], $index[1]);
    }
}

try {
    $identity = Database::fetchOne($branch, "SELECT LOWER(SYS_CONTEXT('USERENV','DB_NAME')) db_name, UPPER(SYS_CONTEXT('USERENV','CURRENT_SCHEMA')) current_schema FROM dual");
    if (strtolower((string)($identity['db_name'] ?? '')) !== 'oranjc') {
        throw new RuntimeException('CONTROL_DATABASE_MUST_BE_ORANJC');
    }
    if (strtoupper((string)($identity['current_schema'] ?? '')) !== 'WATANY') {
        throw new RuntimeException('CONTROL_SCHEMA_MUST_BE_WATANY');
    }
    $foundation = Database::fetchOne($branch, "SELECT COUNT(*) object_count FROM USER_TABLES WHERE TABLE_NAME IN ('DASH_DIAR_RULESETS','DASH_DIAR_RULE_VERSIONS','DASH_DIAR_RULE_PARAMS','DASH_BRANCH_REGISTRY')");
    if ((int)($foundation['object_count'] ?? 0) !== 4) {
        throw new RuntimeException('DIAR4A_FOUNDATION_REQUIRED');
    }
    $activeRule = Database::fetchOne($branch, "SELECT VERSION_ID version_id FROM (SELECT VERSION_ID FROM DASH_DIAR_RULE_VERSIONS WHERE STATUS_CODE='ACTIVE' ORDER BY ACTIVATED_AT DESC, VERSION_ID DESC) WHERE ROWNUM=1");
    if (!$activeRule) {
        throw new RuntimeException('ACTIVE_DIAR_RULE_VERSION_REQUIRED');
    }
    $ruleVersionId = (int)$activeRule['version_id'];
    $result['gates']['CONTROL_IDENTITY'] = 'PASS';
    $result['gates']['DIAR4A_FOUNDATION'] = 'PASS';

    installSchema($branch);
    $result['gates']['SCHEMA_INSTALL'] = 'PASS';

    $manifest = readJsonFile($seedRoot . DIRECTORY_SEPARATOR . 'manifest.json');
    $summary = readJsonFile($seedRoot . DIRECTORY_SEPARATOR . 'run-summary.json');
    if (($summary['status'] ?? '') !== 'PASS' || ($summary['engine_revision'] ?? '') !== 'DIAR3-R2') {
        throw new RuntimeException('SOURCE_DIAR3R2_PASS_REQUIRED');
    }
    if (($summary['oracle_operational_writes'] ?? '') !== 'NONE') {
        throw new RuntimeException('SOURCE_ORACLE_WRITE_CONTRACT_FAILED');
    }
    $sourceRunCode = (string)($summary['job_id'] ?? '');
    if ($sourceRunCode === '') {
        throw new RuntimeException('SOURCE_RUN_CODE_MISSING');
    }
    $result['source_run_code'] = $sourceRunCode;

    $requiredHashes = [
        'cross_branch_final_review_queue.csv' => 'd7a33ada04c4b98886c03e0955571c7ba32ab37ae247159000d568b369c01a08',
        'NJCH/event_member_details.csv' => '94f9a40f79ab39e59b62b90f00386da417a17818911f60c6b22c808e600b2b46',
        'MNH/event_member_details.csv' => '4439adabc4ac1ee20b12e42b19f7cf65ecd08840593d268ec93131a9f062ced1',
    ];
    foreach ($requiredHashes as $relative => $expected) {
        $path = $seedRoot . DIRECTORY_SEPARATOR . str_replace('/', DIRECTORY_SEPARATOR, $relative);
        if (!is_file($path) || !hash_equals($expected, hash_file('sha256', $path))) {
            throw new RuntimeException('SOURCE_HASH_MISMATCH: ' . $relative);
        }
        if (($manifest['files'][$relative] ?? '') !== $expected) {
            throw new RuntimeException('SOURCE_MANIFEST_HASH_MISMATCH: ' . $relative);
        }
    }
    $combinedSourceHash = hash('sha256', implode('|', array_values($requiredHashes)));
    $result['gates']['SOURCE_HASH_VERIFIED'] = 'PASS';

    $existingRun = Database::fetchOne($branch, "SELECT IMPORT_RUN_ID import_run_id, STATUS_CODE status_code FROM DASH_DIAR_IMPORT_RUNS WHERE SOURCE_RUN_CODE=:source_run_code", ['source_run_code' => $sourceRunCode]);
    if ($existingRun && (string)$existingRun['status_code'] === 'PASS') {
        $importRunId = (int)$existingRun['import_run_id'];
        $result['idempotent'] = true;
    } else {
        if ($existingRun) {
            cleanupImport($branch, (int)$existingRun['import_run_id']);
        }
        $runRow = Database::fetchOne($branch, 'SELECT DASH_DIAR_IMPORT_SEQ.NEXTVAL id_value FROM dual');
        $importRunId = (int)($runRow['id_value'] ?? 0);
        Database::execute($branch, "INSERT INTO DASH_DIAR_IMPORT_RUNS (
            IMPORT_RUN_ID,SOURCE_RUN_CODE,SOURCE_ENGINE_REV,SOURCE_SHA256,SOURCE_FILE_NAME,
            STATUS_CODE,STARTED_AT,IMPORTED_BY
        ) VALUES (
            :import_run_id,:source_run_code,'DIAR3-R2',:source_sha256,
            'cross_branch_final_review_queue.csv','RUNNING',SYSDATE,'DIAR4B_IMPORT'
        )", [
            'import_run_id' => $importRunId,
            'source_run_code' => $sourceRunCode,
            'source_sha256' => $combinedSourceHash,
        ]);

        $sourceRows = array_merge(
            readCsv($seedRoot . DIRECTORY_SEPARATOR . 'NJCH' . DIRECTORY_SEPARATOR . 'event_member_details.csv'),
            readCsv($seedRoot . DIRECTORY_SEPARATOR . 'MNH' . DIRECTORY_SEPARATOR . 'event_member_details.csv')
        );
        if (count($sourceRows) !== 3421) {
            throw new RuntimeException('SOURCE_CASE_COUNT_EXPECTED_3421_ACTUAL_' . count($sourceRows));
        }
        $sourceStatements = [];
        foreach ($sourceRows as $row) {
            $sourceStatements[] = [
                'sql' => "INSERT INTO DASH_DIAR_SOURCE_CASES (
                    SOURCE_CASE_ID,IMPORT_RUN_ID,SOURCE_CASE_CODE,SOURCE_ROW_SHA256,SITE_CODE,
                    BRANCH_ID,STORE_CODE,STORE_NAME_AR,STORE_NAME_EN,ITEM_CODE,ITEM_NAME_AR,ITEM_NAME_EN,
                    RAW_UNIT_CODE,RAW_UNIT_NAME_AR,RAW_UNIT_NAME_EN,CALENDAR_DAY,FIRST_TRANS_AT,LAST_TRANS_AT,
                    MOVEMENT_LINES,TRANSACTION_COUNT,DISTINCT_USERS,INBOUND_RAW_QTY,OUTBOUND_RAW_QTY,
                    NET_RAW_QTY,RAW_VALUE_TOTAL,RETURN_IN_QTY,RETURN_OUT_QTY,TRANSFER_OUT_QTY,
                    RECON_ADD_QTY,RECON_SUB_QTY,INVENTORY_EVENTS,BASELINE_STATUS,RISK_SCORE,
                    SEVERITY_CODE,EVIDENCE_STRENGTH,SIGNALS,REVIEW_ACTION,EVENT_CODE,EVENT_TYPE,
                    EVENT_FAMILY,EVENT_QUALIFIED,EVENT_MEMBER_COUNT,MEMBER_DISPOSITION,
                    CORRELATION_REASON,CREATED_AT
                ) VALUES (
                    DASH_DIAR_SOURCE_SEQ.NEXTVAL,:import_run_id,:case_code,:row_sha,:site_code,
                    :branch_id,:store_code,:store_name_ar,:store_name_en,:item_code,:item_name_ar,:item_name_en,
                    :unit_code,:unit_name_ar,:unit_name_en,TO_DATE(:calendar_day,'YYYY-MM-DD'),
                    CASE WHEN :first_at IS NULL THEN NULL ELSE TO_DATE(:first_at,'YYYY-MM-DD HH24:MI:SS') END,
                    CASE WHEN :last_at IS NULL THEN NULL ELSE TO_DATE(:last_at,'YYYY-MM-DD HH24:MI:SS') END,
                    :movement_lines,:transaction_count,:distinct_users,:inbound_qty,:outbound_qty,
                    :net_qty,:raw_value,:return_in,:return_out,:transfer_out,:recon_add,:recon_sub,
                    :inventory_events,:baseline_status,:risk_score,:severity,:evidence_strength,
                    :signals,:review_action,:event_code,:event_type,:event_family,:event_qualified,
                    :event_member_count,:member_disposition,:correlation_reason,SYSDATE
                )",
                'params' => [
                    'import_run_id' => $importRunId,
                    'case_code' => textValue($row,'CASE_ID',40),
                    'row_sha' => sourceRowHash($row),
                    'site_code' => textValue($row,'SITE_CODE',20),
                    'branch_id' => textValue($row,'BRANCH_ID',30),
                    'store_code' => textValue($row,'STORE_CODE',40),
                    'store_name_ar' => textValue($row,'STORE_NAME_AR',250),
                    'store_name_en' => textValue($row,'STORE_NAME_EN',250),
                    'item_code' => textValue($row,'ITEM_CODE',60),
                    'item_name_ar' => textValue($row,'ITEM_NAME_AR',500),
                    'item_name_en' => textValue($row,'ITEM_NAME_EN',500),
                    'unit_code' => textValue($row,'RAW_UNIT_CODE',40),
                    'unit_name_ar' => textValue($row,'RAW_UNIT_NAME_AR',200),
                    'unit_name_en' => textValue($row,'RAW_UNIT_NAME_EN',200),
                    'calendar_day' => textValue($row,'CALENDAR_DAY',10),
                    'first_at' => textValue($row,'FIRST_TRANS_DATETIME',19),
                    'last_at' => textValue($row,'LAST_TRANS_DATETIME',19),
                    'movement_lines' => numberValue($row,'MOVEMENT_LINES'),
                    'transaction_count' => numberValue($row,'TRANSACTION_COUNT'),
                    'distinct_users' => numberValue($row,'DISTINCT_USERS'),
                    'inbound_qty' => numberValue($row,'INBOUND_RAW_QTY'),
                    'outbound_qty' => numberValue($row,'DERIVED_OUTBOUND_RAW_QTY'),
                    'net_qty' => numberValue($row,'NET_RAW_QTY'),
                    'raw_value' => numberValue($row,'RAW_VALUE_TOTAL'),
                    'return_in' => numberValue($row,'RETURN_IN_QTY'),
                    'return_out' => numberValue($row,'RETURN_OUT_QTY'),
                    'transfer_out' => numberValue($row,'TRANSFER_OUT_QTY'),
                    'recon_add' => numberValue($row,'RECON_ADD_QTY'),
                    'recon_sub' => numberValue($row,'RECON_SUB_QTY'),
                    'inventory_events' => numberValue($row,'INVENTORY_EVENTS'),
                    'baseline_status' => textValue($row,'BASELINE_STATUS_USED',30),
                    'risk_score' => numberValue($row,'RISK_SCORE') ?: '0',
                    'severity' => textValue($row,'SEVERITY',20) ?: 'INFO',
                    'evidence_strength' => textValue($row,'EVIDENCE_STRENGTH',30),
                    'signals' => textValue($row,'SIGNALS',4000),
                    'review_action' => textValue($row,'REVIEW_ACTION',200),
                    'event_code' => textValue($row,'EVENT_ID',40),
                    'event_type' => textValue($row,'EVENT_TYPE',60),
                    'event_family' => textValue($row,'EVENT_FAMILY',40),
                    'event_qualified' => textValue($row,'EVENT_QUALIFIED',3),
                    'event_member_count' => numberValue($row,'EVENT_MEMBER_COUNT'),
                    'member_disposition' => textValue($row,'MEMBER_DISPOSITION',50),
                    'correlation_reason' => textValue($row,'CORRELATION_REASON',2000),
                ],
            ];
        }
        executeChunks($branch, $sourceStatements, 75);

        $sourceIdRows = Database::fetchAll($branch, "SELECT SOURCE_CASE_CODE source_case_code, SOURCE_CASE_ID source_case_id FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:import_run_id", ['import_run_id' => $importRunId]);
        $sourceIdMap = [];
        foreach ($sourceIdRows as $row) {
            $sourceIdMap[(string)$row['source_case_code']] = (int)$row['source_case_id'];
        }

        $queueRows = readCsv($seedRoot . DIRECTORY_SEPARATOR . 'cross_branch_final_review_queue.csv');
        if (count($queueRows) !== 2913) {
            throw new RuntimeException('QUEUE_ENTITY_COUNT_EXPECTED_2913_ACTUAL_' . count($queueRows));
        }
        usort($queueRows, function (array $a, array $b): int {
            $ta = (string)($a['REVIEW_ENTITY_TYPE'] ?? '');
            $tb = (string)($b['REVIEW_ENTITY_TYPE'] ?? '');
            if ($ta === $tb) {
                return strcmp((string)$a['REVIEW_ID'], (string)$b['REVIEW_ID']);
            }
            return $ta === 'EVENT' ? -1 : 1;
        });

        $eventRows = array_values(array_filter($queueRows, function (array $row): bool {
            return (string)($row['REVIEW_ENTITY_TYPE'] ?? '') === 'EVENT';
        }));
        $caseRows = array_values(array_filter($queueRows, function (array $row): bool {
            return (string)($row['REVIEW_ENTITY_TYPE'] ?? '') === 'CASE';
        }));

        $buildQueueStatement = function (array $row, ?int $parentEventCaseId) use ($importRunId, $ruleVersionId, $sourceIdMap): array {
            $entityType = textValue($row,'REVIEW_ENTITY_TYPE',10) ?: 'CASE';
            $originalCase = textValue($row,'ORIGINAL_CASE_ID',40);
            $sourceId = $originalCase !== null && isset($sourceIdMap[$originalCase]) ? $sourceIdMap[$originalCase] : null;
            $riskScore = $entityType === 'EVENT'
                ? (numberValue($row,'EVENT_RISK_SCORE') ?: numberValue($row,'MAX_ORIGINAL_RISK_SCORE'))
                : numberValue($row,'ORIGINAL_RISK_SCORE');
            $severity = $entityType === 'EVENT'
                ? (textValue($row,'EVENT_SEVERITY',20) ?: 'MEDIUM')
                : (textValue($row,'ORIGINAL_SEVERITY',20) ?: 'MEDIUM');
            return [
                'sql' => "INSERT INTO DASH_DIAR_CASES (
                    CASE_ID,CASE_CODE,IMPORT_RUN_ID,ENTITY_TYPE,SOURCE_CASE_ID,PARENT_EVENT_CASE_ID,
                    RULE_VERSION_ID,SITE_CODE,BRANCH_ID,STORE_CODE,STORE_NAME_AR,STORE_NAME_EN,
                    CALENDAR_DAY,EVENT_TYPE,EVENT_FAMILY,MEMBER_COUNT,SUPPRESSED_MEMBER_COUNT,
                    OUTLIER_COUNT,ITEM_CODE,ITEM_NAME_AR,ITEM_NAME_EN,RAW_UNIT_CODE,RISK_SCORE,
                    SEVERITY_CODE,EVIDENCE_STRENGTH,REPRESENTATIVE_SIGNALS,MEMBER_DISPOSITION,
                    REVIEW_REASON,REVIEW_ACTION,STATUS_CODE,CREATED_AT,UPDATED_BY,UPDATED_AT
                ) VALUES (
                    DASH_DIAR_CASE_SEQ.NEXTVAL,:case_code,:import_run_id,:entity_type,:source_case_id,
                    :parent_event_case_id,:rule_version_id,:site_code,:branch_id,:store_code,
                    :store_name_ar,:store_name_en,TO_DATE(:calendar_day,'YYYY-MM-DD'),:event_type,
                    :event_family,:member_count,:suppressed_count,:outlier_count,:item_code,
                    :item_name_ar,:item_name_en,:unit_code,:risk_score,:severity,:evidence_strength,
                    :signals,:member_disposition,:review_reason,:review_action,'NEW',SYSDATE,
                    'DIAR4B_IMPORT',SYSDATE
                )",
                'params' => [
                    'case_code' => textValue($row,'REVIEW_ID',40),
                    'import_run_id' => $importRunId,
                    'entity_type' => $entityType,
                    'source_case_id' => $sourceId,
                    'parent_event_case_id' => $parentEventCaseId,
                    'rule_version_id' => $ruleVersionId,
                    'site_code' => textValue($row,'SITE_CODE',20),
                    'branch_id' => textValue($row,'BRANCH_ID',30),
                    'store_code' => textValue($row,'STORE_CODE',40),
                    'store_name_ar' => textValue($row,'STORE_NAME_AR',250),
                    'store_name_en' => textValue($row,'STORE_NAME_EN',250),
                    'calendar_day' => textValue($row,'CALENDAR_DAY',10),
                    'event_type' => textValue($row,'EVENT_TYPE',60),
                    'event_family' => textValue($row,'EVENT_FAMILY',40),
                    'member_count' => numberValue($row,'MEMBER_COUNT') ?: '1',
                    'suppressed_count' => numberValue($row,'SUPPRESSED_MEMBER_COUNT') ?: '0',
                    'outlier_count' => numberValue($row,'OUTLIER_COUNT') ?: '0',
                    'item_code' => textValue($row,'ITEM_CODE',60),
                    'item_name_ar' => textValue($row,'ITEM_NAME_AR',500),
                    'item_name_en' => textValue($row,'ITEM_NAME_EN',500),
                    'unit_code' => textValue($row,'RAW_UNIT_CODE',40),
                    'risk_score' => $riskScore ?: '0',
                    'severity' => $severity,
                    'evidence_strength' => $sourceId && isset($sourceIdMap[$originalCase]) ? 'MODERATE' : null,
                    'signals' => textValue($row,'REPRESENTATIVE_SIGNALS',4000),
                    'member_disposition' => textValue($row,'MEMBER_DISPOSITION',50),
                    'review_reason' => textValue($row,'REVIEW_REASON',2000),
                    'review_action' => textValue($row,'REVIEW_ACTION',500),
                ],
            ];
        };

        $eventStatements = [];
        foreach ($eventRows as $row) {
            $eventStatements[] = $buildQueueStatement($row, null);
        }
        executeChunks($branch, $eventStatements, 50);

        $eventDbRows = Database::fetchAll($branch, "SELECT CASE_CODE case_code, CASE_ID case_id FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:import_run_id AND ENTITY_TYPE='EVENT'", ['import_run_id' => $importRunId]);
        $eventMap = [];
        foreach ($eventDbRows as $row) {
            $eventMap[(string)$row['case_code']] = (int)$row['case_id'];
        }

        $sourceEventMap = [];
        foreach ($sourceRows as $sourceRow) {
            $sourceEventMap[(string)($sourceRow['CASE_ID'] ?? '')] = (string)($sourceRow['EVENT_ID'] ?? '');
        }
        $caseStatements = [];
        foreach ($caseRows as $row) {
            $originalCase = (string)($row['ORIGINAL_CASE_ID'] ?? '');
            $eventCode = $sourceEventMap[$originalCase] ?? '';
            $parentId = isset($eventMap[$eventCode]) ? $eventMap[$eventCode] : null;
            $caseStatements[] = $buildQueueStatement($row, $parentId);
        }
        executeChunks($branch, $caseStatements, 75);

        Database::execute($branch, "INSERT INTO DASH_DIAR_CASE_HISTORY (
            HISTORY_ID,CASE_ID,ACTION_CODE,OLD_STATUS_CODE,NEW_STATUS_CODE,
            PERFORMED_BY,PERFORMED_AT,NOTE_TEXT
        )
        SELECT DASH_DIAR_HISTORY_SEQ.NEXTVAL,CASE_ID,'IMPORTED',NULL,'NEW',
               'DIAR4B_IMPORT',SYSDATE,'Imported from validated DIAR-3 R2 review queue.'
        FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:import_run_id", ['import_run_id' => $importRunId]);

        $sourceStats = Database::fetchOne($branch, "SELECT COUNT(*) source_count,
            SUM(CASE WHEN EVENT_QUALIFIED='YES' THEN 1 ELSE 0 END) event_member_count,
            SUM(CASE WHEN MEMBER_DISPOSITION='SUPPRESSED_BATCH_MEMBER' THEN 1 ELSE 0 END) suppressed_count
            FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:import_run_id", ['import_run_id' => $importRunId]);
        $queueStats = Database::fetchOne($branch, "SELECT COUNT(*) queue_count,
            SUM(CASE WHEN ENTITY_TYPE='EVENT' THEN 1 ELSE 0 END) event_count
            FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:import_run_id", ['import_run_id' => $importRunId]);
        Database::execute($branch, "UPDATE DASH_DIAR_IMPORT_RUNS SET
            QUEUE_ENTITY_COUNT=:queue_count,SOURCE_CASE_COUNT=:source_count,
            EVENT_COUNT=:event_count,EVENT_MEMBER_COUNT=:event_member_count,
            SUPPRESSED_COUNT=:suppressed_count,STATUS_CODE='PASS',
            COMPLETED_AT=SYSDATE,ERROR_MESSAGE=NULL
            WHERE IMPORT_RUN_ID=:import_run_id", [
            'queue_count' => (int)($queueStats['queue_count'] ?? 0),
            'source_count' => (int)($sourceStats['source_count'] ?? 0),
            'event_count' => (int)($queueStats['event_count'] ?? 0),
            'event_member_count' => (int)($sourceStats['event_member_count'] ?? 0),
            'suppressed_count' => (int)($sourceStats['suppressed_count'] ?? 0),
            'import_run_id' => $importRunId,
        ]);
    }

    $counts = Database::fetchOne($branch, "SELECT
        (SELECT COUNT(*) FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:import_run_id) source_cases,
        (SELECT COUNT(*) FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:import_run_id) queue_entities,
        (SELECT COUNT(*) FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:import_run_id AND ENTITY_TYPE='EVENT') events,
        (SELECT COUNT(*) FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:import_run_id AND EVENT_QUALIFIED='YES') event_members,
        (SELECT COUNT(*) FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:import_run_id AND MEMBER_DISPOSITION='SUPPRESSED_BATCH_MEMBER') suppressed_members,
        (SELECT COUNT(*) FROM DASH_DIAR_CASE_HISTORY h JOIN DASH_DIAR_CASES c ON c.CASE_ID=h.CASE_ID WHERE c.IMPORT_RUN_ID=:import_run_id AND h.ACTION_CODE='IMPORTED') imported_history
        FROM dual", ['import_run_id' => $importRunId]);

    $result['counts'] = $counts ?: [];
    $expected = [
        'source_cases' => 3421,
        'queue_entities' => 2913,
        'events' => 10,
        'event_members' => 535,
        'suppressed_members' => 518,
        'imported_history' => 2913,
    ];
    foreach ($expected as $key => $value) {
        if ((int)($counts[$key] ?? -1) !== $value) {
            throw new RuntimeException('COUNT_GATE_FAILED_' . strtoupper($key) . '_EXPECTED_' . $value . '_ACTUAL_' . (int)($counts[$key] ?? -1));
        }
    }
    $dupes = Database::fetchOne($branch, "SELECT
        (SELECT COUNT(*)-COUNT(DISTINCT SOURCE_CASE_CODE) FROM DASH_DIAR_SOURCE_CASES WHERE IMPORT_RUN_ID=:import_run_id) source_duplicates,
        (SELECT COUNT(*)-COUNT(DISTINCT CASE_CODE) FROM DASH_DIAR_CASES WHERE IMPORT_RUN_ID=:import_run_id) queue_duplicates
        FROM dual", ['import_run_id' => $importRunId]);
    if ((int)($dupes['source_duplicates'] ?? -1) !== 0 || (int)($dupes['queue_duplicates'] ?? -1) !== 0) {
        throw new RuntimeException('UNIQUE_IDENTIFIER_GATE_FAILED');
    }
    $result['gates']['ALL_SOURCE_CASES_IMPORTED'] = 'PASS';
    $result['gates']['FINAL_QUEUE_IMPORTED'] = 'PASS';
    $result['gates']['EVENT_MEMBERS_PRESERVED'] = 'PASS';
    $result['gates']['SUPPRESSED_NOT_REQUEUED'] = 'PASS';
    $result['gates']['IDENTIFIERS_UNIQUE'] = 'PASS';
    $result['gates']['INITIAL_HISTORY_COMPLETE'] = 'PASS';
    $result['gates']['NO_AUTOMATIC_FRAUD_CONCLUSION'] = 'PASS';
    $result['gates']['ORACLE_OPERATIONAL_WRITES_NONE'] = 'PASS';
    $result['status'] = 'PASS';
    echo json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), PHP_EOL;
    exit(0);
} catch (\Throwable $e) {
    $result['error'] = $e->getMessage();
    error_log('[DIAR4BApply] ' . $e->getMessage());
    echo json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), PHP_EOL;
    exit(1);
}
