<?php

declare(strict_types=1);

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

use App\Core\Database;

$branch = 'njch';
$result = [
    'schema' => 'DIAR4A_CENTRAL_CONTROL_APPLY_V2',
    'captured_at' => date(DATE_ATOM),
    'branch' => 'NJCH',
    'target_database' => 'ORANJC',
    'target_schema' => 'WATANY',
    'status' => 'FAIL',
    'checks' => [],
    'oracle_writes' => 'CONTROL_TABLES_ONLY',
];

function runDdl(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 . ')');
    runDdl($branch, "BEGIN EXECUTE IMMEDIATE '" . $safe . "'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -955 THEN RAISE; END IF; END;");
}

function createSequence(string $branch, string $name): void
{
    runDdl($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 $sql): void
{
    $safe = str_replace("'", "''", $sql);
    runDdl($branch, "BEGIN EXECUTE IMMEDIATE '" . $safe . "'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -955 THEN RAISE; END IF; END;");
}

function nextId(string $branch, string $sequence): int
{
    $row = Database::fetchOne($branch, 'SELECT ' . $sequence . '.NEXTVAL id_value FROM dual');
    return (int)($row['id_value'] ?? 0);
}

function seedSetting(string $branch, string $module, string $code, string $nameEn, string $nameAr, string $type, $value): void
{
    $id = nextId($branch, 'DASH_SHARED_SET_SEQ');
    $num = $type === 'NUMBER' ? (string)$value : null;
    $text = $type === 'TEXT' ? (string)$value : null;
    $bool = $type === 'BOOLEAN' ? (int)$value : null;
    Database::execute($branch, "MERGE INTO DASH_SHARED_SETTINGS t
        USING (SELECT :module_code module_code, :setting_code setting_code FROM dual) s
        ON (t.MODULE_CODE=s.module_code AND t.SETTING_CODE=s.setting_code)
        WHEN MATCHED THEN UPDATE SET NAME_EN=:name_en, NAME_AR=:name_ar,
            VALUE_TYPE=:value_type, NUM_VALUE=:num_value, TEXT_VALUE=:text_value,
            BOOL_VALUE=:bool_value, IS_ACTIVE=1, UPDATED_BY='DIAR4A_R1', UPDATED_AT=SYSDATE
        WHEN NOT MATCHED THEN INSERT (
            SETTING_ID,MODULE_CODE,SETTING_CODE,NAME_EN,NAME_AR,VALUE_TYPE,
            NUM_VALUE,TEXT_VALUE,BOOL_VALUE,IS_SECRET,IS_ACTIVE,UPDATED_BY,UPDATED_AT
        ) VALUES (
            :setting_id,:module_code,:setting_code,:name_en,:name_ar,:value_type,
            :num_value,:text_value,:bool_value,0,1,'DIAR4A_R1',SYSDATE
        )", [
        'setting_id'=>$id,'module_code'=>$module,'setting_code'=>$code,
        'name_en'=>$nameEn,'name_ar'=>$nameAr,'value_type'=>$type,
        'num_value'=>$num,'text_value'=>$text,'bool_value'=>$bool,
    ]);
}

function seedParam(string $branch, int $versionId, array $p): void
{
    $id = nextId($branch, 'DASH_DIAR_PARAM_SEQ');
    Database::execute($branch, "MERGE INTO DASH_DIAR_RULE_PARAMS t
        USING (SELECT :version_id version_id, :param_code param_code FROM dual) s
        ON (t.VERSION_ID=s.version_id AND t.PARAM_CODE=s.param_code)
        WHEN MATCHED THEN UPDATE SET
            GROUP_CODE=:group_code, NAME_EN=:name_en, NAME_AR=:name_ar,
            DESCRIPTION_EN=:description_en, DESCRIPTION_AR=:description_ar,
            VALUE_TYPE=:value_type, NUM_VALUE=:num_value, TEXT_VALUE=:text_value,
            BOOL_VALUE=:bool_value, MIN_NUM=:min_num, MAX_NUM=:max_num,
            UNIT_CODE=:unit_code, SORT_ORDER=:sort_order, IS_EDITABLE=1
        WHEN NOT MATCHED THEN INSERT (
            PARAM_ID,VERSION_ID,GROUP_CODE,PARAM_CODE,NAME_EN,NAME_AR,
            DESCRIPTION_EN,DESCRIPTION_AR,VALUE_TYPE,NUM_VALUE,TEXT_VALUE,
            BOOL_VALUE,MIN_NUM,MAX_NUM,UNIT_CODE,SORT_ORDER,IS_EDITABLE
        ) VALUES (
            :param_id,:version_id,:group_code,:param_code,:name_en,:name_ar,
            :description_en,:description_ar,:value_type,:num_value,:text_value,
            :bool_value,:min_num,:max_num,:unit_code,:sort_order,1
        )", [
        'param_id'=>$id,'version_id'=>$versionId,'group_code'=>$p[0],
        'param_code'=>$p[1],'name_en'=>$p[2],'name_ar'=>$p[3],
        'description_en'=>$p[4],'description_ar'=>$p[5],
        'value_type'=>$p[6],
        'num_value'=>$p[6]==='NUMBER'?(string)$p[7]:null,
        'text_value'=>$p[6]==='TEXT'?(string)$p[7]:null,
        'bool_value'=>$p[6]==='BOOLEAN'?(int)$p[7]:null,
        'min_num'=>$p[8]===null?null:(string)$p[8],
        'max_num'=>$p[9]===null?null:(string)$p[9],
        'unit_code'=>$p[10], 'sort_order'=>$p[11],
    ]);
}

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');
    }
    $existing = Database::fetchOne($branch, "SELECT COUNT(*) object_count
        FROM USER_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW') AND OBJECT_NAME IN (
            'SECURITY_USERS','DASH_ACL_ROLES','DASH_ACL_USER_ROLES',
            'DASH_ACL_ROLE_OVERRIDES','DASH_ACL_USER_BRANCHES','DASH_ACL_AUDIT'
        )");
    if ((int)($existing['object_count'] ?? 0) !== 6) {
        throw new RuntimeException('CENTRAL_USERS_AND_ACL_SCHEMA_REQUIRED');
    }

    $p142Columns = Database::fetchOne($branch, "SELECT COUNT(*) column_count
        FROM USER_TAB_COLUMNS
        WHERE TABLE_NAME='DASH_ACL_ROLES'
          AND COLUMN_NAME IN ('ROLE_DESCRIPTION_EN','ROLE_DESCRIPTION_AR')");
    if ((int)($p142Columns['column_count'] ?? 0) !== 2) {
        throw new RuntimeException('P14_2_ACL_COLUMNS_REQUIRED');
    }
    $p142Sequence = Database::fetchOne($branch, "SELECT COUNT(*) object_count
        FROM USER_SEQUENCES WHERE SEQUENCE_NAME='DASH_ACL_AUDIT_SEQ'");
    if ((int)($p142Sequence['object_count'] ?? 0) !== 1) {
        throw new RuntimeException('P14_2_ACL_AUDIT_SEQUENCE_REQUIRED');
    }
    $p142Index = Database::fetchOne($branch, "SELECT COUNT(*) object_count
        FROM USER_INDEXES WHERE INDEX_NAME='UX_DASH_ACL_USER_ONE_ROLE'");
    if ((int)($p142Index['object_count'] ?? 0) !== 1) {
        throw new RuntimeException('P14_2_ONE_ROLE_INDEX_REQUIRED');
    }
    $overrideColumns = Database::fetchOne($branch, "SELECT COUNT(*) column_count
        FROM USER_TAB_COLUMNS
        WHERE TABLE_NAME='DASH_ACL_ROLE_OVERRIDES'
          AND COLUMN_NAME IN ('ROLE_CODE','PERMISSION_CODE','EFFECT','UPDATED_BY','UPDATED_AT')");
    if ((int)($overrideColumns['column_count'] ?? 0) !== 5) {
        throw new RuntimeException('P14_2_ROLE_OVERRIDE_COLUMNS_REQUIRED');
    }
    $ddlPrivileges = Database::fetchOne($branch, "SELECT
        SUM(CASE WHEN PRIVILEGE='CREATE TABLE' THEN 1 ELSE 0 END) create_table_count,
        SUM(CASE WHEN PRIVILEGE='CREATE SEQUENCE' THEN 1 ELSE 0 END) create_sequence_count
        FROM SESSION_PRIVS");
    if ((int)($ddlPrivileges['create_table_count'] ?? 0) < 1
        || (int)($ddlPrivileges['create_sequence_count'] ?? 0) < 1) {
        throw new RuntimeException('CREATE_TABLE_AND_SEQUENCE_PRIVILEGES_REQUIRED');
    }

    $result['checks']['p14_2_acl_contract'] = 'PASS';
    $result['checks']['ddl_privileges'] = 'PASS';

    createTable($branch, 'DASH_BRANCH_REGISTRY', "
        HOSPITAL_CODE VARCHAR2(20) NOT NULL, NAME_EN VARCHAR2(160) NOT NULL,
        NAME_AR VARCHAR2(160), SOURCE_DB_NAME VARCHAR2(60) NOT NULL,
        SOURCE_SCHEMA VARCHAR2(60) NOT NULL, IS_ENABLED NUMBER(1) DEFAULT 1 NOT NULL,
        IS_READ_ONLY NUMBER(1) DEFAULT 1 NOT NULL, CONTROL_DATA_FLAG NUMBER(1) DEFAULT 0 NOT NULL,
        STATUS_CODE VARCHAR2(30) NOT NULL, SORT_ORDER NUMBER(6) DEFAULT 0 NOT NULL,
        UPDATED_BY VARCHAR2(128), UPDATED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DASH_BRANCH_REG PRIMARY KEY (HOSPITAL_CODE),
        CONSTRAINT CK_DASH_BRANCH_BOOL CHECK (IS_ENABLED IN (0,1) AND IS_READ_ONLY IN (0,1) AND CONTROL_DATA_FLAG IN (0,1))
    ");
    createTable($branch, 'DASH_SHARED_SETTINGS', "
        SETTING_ID NUMBER NOT NULL, MODULE_CODE VARCHAR2(40) NOT NULL,
        SETTING_CODE VARCHAR2(100) NOT NULL, NAME_EN VARCHAR2(180) NOT NULL,
        NAME_AR VARCHAR2(180), VALUE_TYPE VARCHAR2(20) NOT NULL,
        NUM_VALUE NUMBER, TEXT_VALUE VARCHAR2(2000), BOOL_VALUE NUMBER(1), JSON_VALUE CLOB,
        IS_SECRET NUMBER(1) DEFAULT 0 NOT NULL, IS_ACTIVE NUMBER(1) DEFAULT 1 NOT NULL,
        UPDATED_BY VARCHAR2(128), UPDATED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DASH_SHARED_SETTINGS PRIMARY KEY (SETTING_ID),
        CONSTRAINT UX_DASH_SHARED_SET UNIQUE (MODULE_CODE, SETTING_CODE),
        CONSTRAINT CK_DASH_SHARED_TYPE CHECK (VALUE_TYPE IN ('NUMBER','TEXT','BOOLEAN','JSON')),
        CONSTRAINT CK_DASH_SHARED_BOOL CHECK (IS_SECRET IN (0,1) AND IS_ACTIVE IN (0,1) AND (BOOL_VALUE IS NULL OR BOOL_VALUE IN (0,1)))
    ");
    createTable($branch, 'DASH_SHARED_SET_AUDIT', "
        AUDIT_ID NUMBER NOT NULL, MODULE_CODE VARCHAR2(40) NOT NULL,
        SETTING_CODE VARCHAR2(100) NOT NULL, OLD_VALUE VARCHAR2(4000),
        NEW_VALUE VARCHAR2(4000), CHANGED_BY VARCHAR2(128) NOT NULL,
        CHANGED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DASH_SHARED_AUDIT PRIMARY KEY (AUDIT_ID)
    ");
    createTable($branch, 'DASH_DIAR_RULESETS', "
        RULESET_ID NUMBER NOT NULL, RULESET_CODE VARCHAR2(50) NOT NULL,
        NAME_EN VARCHAR2(180) NOT NULL, NAME_AR VARCHAR2(180),
        DESCRIPTION_EN VARCHAR2(1000), DESCRIPTION_AR VARCHAR2(1000),
        IS_ACTIVE NUMBER(1) DEFAULT 1 NOT NULL, CREATED_BY VARCHAR2(128) NOT NULL,
        CREATED_AT DATE DEFAULT SYSDATE NOT NULL, UPDATED_BY VARCHAR2(128),
        UPDATED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DASH_DIAR_RULESETS PRIMARY KEY (RULESET_ID),
        CONSTRAINT UX_DASH_DIAR_RULESET UNIQUE (RULESET_CODE),
        CONSTRAINT CK_DASH_DIAR_RS_BOOL CHECK (IS_ACTIVE IN (0,1))
    ");
    createTable($branch, 'DASH_DIAR_RULE_VERSIONS', "
        VERSION_ID NUMBER NOT NULL, RULESET_ID NUMBER NOT NULL, VERSION_NO NUMBER(10) NOT NULL,
        STATUS_CODE VARCHAR2(20) NOT NULL, PARENT_VERSION_ID NUMBER, CONFIG_HASH VARCHAR2(64),
        CHANGE_REASON VARCHAR2(1000) NOT NULL, CALIBRATION_STATUS VARCHAR2(40) NOT NULL,
        CREATED_BY VARCHAR2(128) NOT NULL, CREATED_AT DATE DEFAULT SYSDATE NOT NULL,
        APPROVED_BY VARCHAR2(128), APPROVED_AT DATE, ACTIVATED_BY VARCHAR2(128),
        ACTIVATED_AT DATE, RETIRED_AT DATE,
        CONSTRAINT PK_DASH_DIAR_RVERS PRIMARY KEY (VERSION_ID),
        CONSTRAINT FK_DIAR_RV_RULESET FOREIGN KEY (RULESET_ID) REFERENCES DASH_DIAR_RULESETS(RULESET_ID),
        CONSTRAINT FK_DIAR_RV_PARENT FOREIGN KEY (PARENT_VERSION_ID) REFERENCES DASH_DIAR_RULE_VERSIONS(VERSION_ID),
        CONSTRAINT UX_DIAR_RV_NO UNIQUE (RULESET_ID, VERSION_NO),
        CONSTRAINT CK_DIAR_RV_STATUS CHECK (STATUS_CODE IN ('DRAFT','APPROVED','ACTIVE','RETIRED'))
    ");
    createIndex($branch, "CREATE UNIQUE INDEX UX_DIAR_ONE_ACTIVE ON DASH_DIAR_RULE_VERSIONS (CASE WHEN STATUS_CODE = 'ACTIVE' THEN RULESET_ID ELSE NULL END)");
    createTable($branch, 'DASH_DIAR_RULE_PARAMS', "
        PARAM_ID NUMBER NOT NULL, VERSION_ID NUMBER NOT NULL, GROUP_CODE VARCHAR2(50) NOT NULL,
        PARAM_CODE VARCHAR2(100) NOT NULL, NAME_EN VARCHAR2(180) NOT NULL, NAME_AR VARCHAR2(180),
        DESCRIPTION_EN VARCHAR2(1000), DESCRIPTION_AR VARCHAR2(1000), VALUE_TYPE VARCHAR2(20) NOT NULL,
        NUM_VALUE NUMBER, TEXT_VALUE VARCHAR2(2000), BOOL_VALUE NUMBER(1), MIN_NUM NUMBER,
        MAX_NUM NUMBER, UNIT_CODE VARCHAR2(30), SORT_ORDER NUMBER(6) DEFAULT 0 NOT NULL,
        IS_EDITABLE NUMBER(1) DEFAULT 1 NOT NULL,
        CONSTRAINT PK_DASH_DIAR_PARAMS PRIMARY KEY (PARAM_ID),
        CONSTRAINT FK_DIAR_PARAM_VERSION FOREIGN KEY (VERSION_ID) REFERENCES DASH_DIAR_RULE_VERSIONS(VERSION_ID),
        CONSTRAINT UX_DIAR_PARAM_CODE UNIQUE (VERSION_ID, PARAM_CODE),
        CONSTRAINT CK_DIAR_PARAM_TYPE CHECK (VALUE_TYPE IN ('NUMBER','TEXT','BOOLEAN')),
        CONSTRAINT CK_DIAR_PARAM_BOOL CHECK (IS_EDITABLE IN (0,1) AND (BOOL_VALUE IS NULL OR BOOL_VALUE IN (0,1)))
    ");
    createIndex($branch, 'CREATE INDEX IX_DIAR_PARAM_GROUP ON DASH_DIAR_RULE_PARAMS (VERSION_ID, GROUP_CODE, SORT_ORDER)');
    createTable($branch, 'DASH_DIAR_RULE_SCOPES', "
        SCOPE_ID NUMBER NOT NULL, VERSION_ID NUMBER NOT NULL, SCOPE_TYPE VARCHAR2(30) NOT NULL,
        SCOPE_CODE VARCHAR2(100) NOT NULL, EFFECT_CODE VARCHAR2(10) NOT NULL,
        IS_ACTIVE NUMBER(1) DEFAULT 1 NOT NULL, NOTES VARCHAR2(1000),
        CONSTRAINT PK_DASH_DIAR_SCOPES PRIMARY KEY (SCOPE_ID),
        CONSTRAINT FK_DIAR_SCOPE_VERSION FOREIGN KEY (VERSION_ID) REFERENCES DASH_DIAR_RULE_VERSIONS(VERSION_ID),
        CONSTRAINT UX_DIAR_SCOPE_CODE UNIQUE (VERSION_ID, SCOPE_TYPE, SCOPE_CODE),
        CONSTRAINT CK_DIAR_SCOPE_EFFECT CHECK (EFFECT_CODE IN ('INCLUDE','EXCLUDE') AND IS_ACTIVE IN (0,1))
    ");
    createTable($branch, 'DASH_DIAR_DRY_RUNS', "
        DRY_RUN_ID NUMBER NOT NULL, VERSION_ID NUMBER NOT NULL, BASE_VERSION_ID NUMBER,
        STATUS_CODE VARCHAR2(20) NOT NULL, SOURCE_START_DATE DATE, SOURCE_END_DATE DATE,
        REQUESTED_BY VARCHAR2(128) NOT NULL, REQUESTED_AT DATE DEFAULT SYSDATE NOT NULL,
        STARTED_AT DATE, COMPLETED_AT DATE, BEFORE_CASES NUMBER, AFTER_CASES NUMBER,
        DELTA_CASES NUMBER, RESULT_HASH VARCHAR2(64), ERROR_MESSAGE VARCHAR2(2000),
        CONSTRAINT PK_DASH_DIAR_DRY_RUNS PRIMARY KEY (DRY_RUN_ID),
        CONSTRAINT FK_DIAR_DRY_VERSION FOREIGN KEY (VERSION_ID) REFERENCES DASH_DIAR_RULE_VERSIONS(VERSION_ID),
        CONSTRAINT FK_DIAR_DRY_BASE FOREIGN KEY (BASE_VERSION_ID) REFERENCES DASH_DIAR_RULE_VERSIONS(VERSION_ID),
        CONSTRAINT CK_DIAR_DRY_STATUS CHECK (STATUS_CODE IN ('PENDING','RUNNING','PASS','FAIL','CANCELLED'))
    ");
    createIndex($branch, 'CREATE INDEX IX_DIAR_DRY_VERSION ON DASH_DIAR_DRY_RUNS (VERSION_ID, REQUESTED_AT)');
    createTable($branch, 'DASH_DIAR_DRY_RESULTS', "
        RESULT_ID NUMBER NOT NULL, DRY_RUN_ID NUMBER NOT NULL, DIMENSION_TYPE VARCHAR2(30) NOT NULL,
        DIMENSION_CODE VARCHAR2(100) NOT NULL, METRIC_CODE VARCHAR2(60) NOT NULL,
        BEFORE_VALUE NUMBER, AFTER_VALUE NUMBER, DELTA_VALUE NUMBER,
        CREATED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DASH_DIAR_DRY_RES PRIMARY KEY (RESULT_ID),
        CONSTRAINT FK_DIAR_DRES_RUN FOREIGN KEY (DRY_RUN_ID) REFERENCES DASH_DIAR_DRY_RUNS(DRY_RUN_ID),
        CONSTRAINT UX_DIAR_DRES_METRIC UNIQUE (DRY_RUN_ID, DIMENSION_TYPE, DIMENSION_CODE, METRIC_CODE)
    ");
    createTable($branch, 'DASH_DIAR_AUDIT', "
        AUDIT_ID NUMBER NOT NULL, ACTOR_USERNAME VARCHAR2(128) NOT NULL,
        ACTION_CODE VARCHAR2(80) NOT NULL, TARGET_TYPE VARCHAR2(40) NOT NULL,
        TARGET_KEY VARCHAR2(180) NOT NULL, OLD_VALUE VARCHAR2(4000), NEW_VALUE VARCHAR2(4000),
        CREATED_AT DATE DEFAULT SYSDATE NOT NULL,
        CONSTRAINT PK_DASH_DIAR_AUDIT PRIMARY KEY (AUDIT_ID)
    ");
    createIndex($branch, 'CREATE INDEX IX_DIAR_AUDIT_TARGET ON DASH_DIAR_AUDIT (TARGET_TYPE, TARGET_KEY, CREATED_AT)');

    foreach (['DASH_SHARED_SET_SEQ','DASH_SHARED_AUD_SEQ','DASH_DIAR_RULESET_SEQ','DASH_DIAR_RVER_SEQ','DASH_DIAR_PARAM_SEQ','DASH_DIAR_SCOPE_SEQ','DASH_DIAR_DRY_SEQ','DASH_DIAR_DRES_SEQ','DASH_DIAR_AUDIT_SEQ'] as $sequence) {
        createSequence($branch, $sequence);
    }

    $branches = [
        ['NJCH','New Jeddah Clinic Hospital','مستشفى جدة الوطني الجديد','ORANJC','WATANY',1,1,1,'READY',10],
        ['MNH','Madina National Hospital','مستشفى المدينة الوطني','ORAMNH','WATANY',1,1,0,'READY',20],
        ['JCKH','Jeddah Clinic Hospital','مستشفى جدة كلينك','ORAJCK','WATANY',0,1,0,'DEFERRED',30],
    ];
    foreach ($branches as $b) {
        Database::execute($branch, "MERGE INTO DASH_BRANCH_REGISTRY t
            USING (SELECT :hospital_code hospital_code FROM dual) s
            ON (t.HOSPITAL_CODE=s.hospital_code)
            WHEN MATCHED THEN UPDATE SET NAME_EN=:name_en,NAME_AR=:name_ar,
                SOURCE_DB_NAME=:db_name,SOURCE_SCHEMA=:source_schema,
                IS_ENABLED=:is_enabled,IS_READ_ONLY=:is_read_only,
                CONTROL_DATA_FLAG=:control_flag,STATUS_CODE=:status_code,
                SORT_ORDER=:sort_order,UPDATED_BY='DIAR4A_R1',UPDATED_AT=SYSDATE
            WHEN NOT MATCHED THEN INSERT (
                HOSPITAL_CODE,NAME_EN,NAME_AR,SOURCE_DB_NAME,SOURCE_SCHEMA,
                IS_ENABLED,IS_READ_ONLY,CONTROL_DATA_FLAG,STATUS_CODE,SORT_ORDER,
                UPDATED_BY,UPDATED_AT
            ) VALUES (
                :hospital_code,:name_en,:name_ar,:db_name,:source_schema,
                :is_enabled,:is_read_only,:control_flag,:status_code,:sort_order,
                'DIAR4A_R1',SYSDATE
            )", [
            'hospital_code'=>$b[0],'name_en'=>$b[1],'name_ar'=>$b[2],
            'db_name'=>$b[3],'source_schema'=>$b[4],'is_enabled'=>$b[5],
            'is_read_only'=>$b[6],'control_flag'=>$b[7],
            'status_code'=>$b[8],'sort_order'=>$b[9],
        ]);
    }
    seedSetting($branch,'CORE','CONTROL_DATABASE','Control database','قاعدة التحكم','TEXT','NJCH/ORANJC');
    seedSetting($branch,'CORE','CONTROL_SCHEMA','Control schema','مخطط التحكم','TEXT','WATANY');
    seedSetting($branch,'CORE','SOURCE_WRITE_BOUNDARY','Source write boundary','حدود الكتابة على المصادر','TEXT','OPERATIONAL_TABLES_READ_ONLY');
    seedSetting($branch,'DIAR','CALIBRATION_STATUS','Calibration status','حالة المعايرة','TEXT','PENDING_OPERATIONAL_FEEDBACK');
    seedSetting($branch,'DIAR','DEFAULT_ANALYSIS_DAYS','Default analysis days','أيام التحليل الافتراضية','NUMBER',180);
    seedSetting($branch,'DIAR','MAX_EXPORT_ROWS','Maximum export rows','الحد الأقصى لصفوف التصدير','NUMBER',50000);

    $ruleset = Database::fetchOne($branch, "SELECT RULESET_ID ruleset_id FROM DASH_DIAR_RULESETS WHERE RULESET_CODE='DIAR_CORE'");
    if (!$ruleset) {
        $rulesetId = nextId($branch,'DASH_DIAR_RULESET_SEQ');
        Database::execute($branch, "INSERT INTO DASH_DIAR_RULESETS (
            RULESET_ID,RULESET_CODE,NAME_EN,NAME_AR,DESCRIPTION_EN,DESCRIPTION_AR,
            IS_ACTIVE,CREATED_BY,CREATED_AT,UPDATED_BY,UPDATED_AT
        ) VALUES (
            :id,'DIAR_CORE','Drug Inventory Anomaly and Risk Rules',
            'قواعد شذوذ ومخاطر مخزون الأدوية',
            'Central explainable rules for drug inventory review and event correlation.',
            'قواعد مركزية قابلة للتفسير لمراجعة مخزون الأدوية وربط الأحداث.',
            1,'DIAR4A_R1',SYSDATE,'DIAR4A_R1',SYSDATE
        )", ['id'=>$rulesetId]);
    } else { $rulesetId=(int)$ruleset['ruleset_id']; }
    $active = Database::fetchOne($branch, "SELECT VERSION_ID version_id FROM DASH_DIAR_RULE_VERSIONS WHERE RULESET_ID=:ruleset_id AND STATUS_CODE='ACTIVE'", ['ruleset_id'=>$rulesetId]);
    if (!$active) {
        $versionId=nextId($branch,'DASH_DIAR_RVER_SEQ');
        Database::execute($branch, "INSERT INTO DASH_DIAR_RULE_VERSIONS (
            VERSION_ID,RULESET_ID,VERSION_NO,STATUS_CODE,PARENT_VERSION_ID,
            CONFIG_HASH,CHANGE_REASON,CALIBRATION_STATUS,CREATED_BY,CREATED_AT,
            ACTIVATED_BY,ACTIVATED_AT
        ) VALUES (
            :version_id,:ruleset_id,1,'ACTIVE',NULL,NULL,
            'Initial controlled rules imported from DIAR-3 R2 validated configuration',
            'PENDING_OPERATIONAL_FEEDBACK','DIAR4A_R1',SYSDATE,'DIAR4A_R1',SYSDATE
        )", ['version_id'=>$versionId,'ruleset_id'=>$rulesetId]);
    } else { $versionId=(int)$active['version_id']; }

    $params = [
        ['SEVERITY','REVIEW_THRESHOLD','Review threshold','حد المراجعة','Cases at or above this score enter the review queue.','الحالات عند هذا الحد أو أعلى تدخل قائمة المراجعة.','NUMBER',35,20,70,'POINTS',10],
        ['SEVERITY','HIGH_THRESHOLD','High threshold','حد الخطورة العالية','High-priority review threshold.','حد المراجعة مرتفعة الأولوية.','NUMBER',55,35,90,'POINTS',20],
        ['SEVERITY','CRITICAL_THRESHOLD','Critical threshold','حد الخطورة الحرجة','Immediate review threshold.','حد المراجعة الفورية.','NUMBER',70,50,100,'POINTS',30],
        ['SEVERITY','EXPANDED_THRESHOLD','Expanded threshold','حد المراقبة الموسع','Sensitivity and monitoring threshold.','حد الحساسية والمراقبة الموسعة.','NUMBER',25,10,50,'POINTS',40],
        ['BASELINE','READY_SPIKE_MAD_MULT','READY spike MAD multiplier','معامل MAD للارتفاع في READY','Median plus multiplier times scaled MAD.','الوسيط مضافًا إليه معامل MAD المعدل.','NUMBER',4,1,10,'MULTIPLIER',10],
        ['BASELINE','READY_EXTREME_MAD_MULT','READY extreme MAD multiplier','معامل MAD للحالة القصوى READY','Extreme quantity multiplier.','معامل الكمية القصوى.','NUMBER',6,2,15,'MULTIPLIER',20],
        ['BASELINE','LIMITED_EXTREME_MULT','LIMITED extreme multiplier','معامل LIMITED للحالة القصوى','Multiplier against non-zero percentile.','معامل مقابل النسبة المئوية غير الصفرية.','NUMBER',1.25,1,5,'MULTIPLIER',30],
        ['BASELINE','SPARSE_SILENCE_DAYS','SPARSE silence days','أيام الانقطاع SPARSE','Minimum inactive days before after-silence signal.','الحد الأدنى لأيام الانقطاع قبل الإشارة.','NUMBER',30,7,180,'DAYS',40],
        ['BASELINE','NO_ACTIVITY_SILENCE_DAYS','No-activity silence days','أيام الانقطاع NO_ACTIVITY','Minimum gap for new outbound activity signal.','الحد الأدنى لفجوة نشاط الصادر الجديد.','NUMBER',60,14,365,'DAYS',50],
        ['SIGNAL_WEIGHT','W_OUT_EXTREME','Outbound extreme weight','وزن الصادر الأقصى','READY extreme outbound contribution.','مساهمة الصادر الأقصى لفئة READY.','NUMBER',38,0,100,'POINTS',10],
        ['SIGNAL_WEIGHT','W_OUT_SPIKE','Outbound spike weight','وزن ارتفاع الصادر','READY outbound spike contribution.','مساهمة ارتفاع الصادر لفئة READY.','NUMBER',26,0,100,'POINTS',20],
        ['SIGNAL_WEIGHT','W_OUT_EXTREME_LIMITED','LIMITED extreme weight','وزن الصادر الأقصى LIMITED','LIMITED extreme outbound contribution.','مساهمة الصادر الأقصى LIMITED.','NUMBER',32,0,100,'POINTS',30],
        ['SIGNAL_WEIGHT','W_OUT_SPIKE_LIMITED','LIMITED spike weight','وزن ارتفاع الصادر LIMITED','LIMITED outbound spike contribution.','مساهمة ارتفاع الصادر LIMITED.','NUMBER',22,0,100,'POINTS',40],
        ['SIGNAL_WEIGHT','W_OUT_AFTER_SILENCE','After-silence weight','وزن الحركة بعد الانقطاع','SPARSE activity after silence.','حركة SPARSE بعد الانقطاع.','NUMBER',24,0,100,'POINTS',50],
        ['SIGNAL_WEIGHT','W_OUT_SPARSE_EXTREME','SPARSE extreme weight','وزن الصادر الأقصى SPARSE','SPARSE extreme quantity contribution.','مساهمة الكمية القصوى SPARSE.','NUMBER',18,0,100,'POINTS',60],
        ['SIGNAL_WEIGHT','W_NEW_OUT_ACTIVITY','New activity weight','وزن النشاط الجديد','Outbound activity after long no-activity gap.','نشاط صادر بعد انقطاع طويل.','NUMBER',18,0,100,'POINTS',70],
        ['SIGNAL_WEIGHT','W_RECON_NORMAL','Reconciliation weight','وزن التسوية','Standard reconciliation contribution.','مساهمة التسوية العادية.','NUMBER',20,0,100,'POINTS',80],
        ['SIGNAL_WEIGHT','W_RECON_EXTREME','Extreme reconciliation weight','وزن التسوية القصوى','Extreme reconciliation contribution.','مساهمة التسوية القصوى.','NUMBER',28,0,100,'POINTS',90],
        ['SIGNAL_WEIGHT','W_RETURN_OUT','Return-out weight','وزن المرتجع الصادر','Standard return-out contribution.','مساهمة المرتجع الصادر.','NUMBER',18,0,100,'POINTS',100],
        ['SIGNAL_WEIGHT','W_RETURN_OUT_SPIKE','Return-out spike weight','وزن ارتفاع المرتجع الصادر','High return-out contribution.','مساهمة ارتفاع المرتجع الصادر.','NUMBER',25,0,100,'POINTS',110],
        ['SIGNAL_WEIGHT','W_RETURN_IN','Return-in weight','وزن المرتجع الداخل','Standard return-in contribution.','مساهمة المرتجع الداخل.','NUMBER',10,0,100,'POINTS',120],
        ['SIGNAL_WEIGHT','W_RETURN_IN_SPIKE','Return-in spike weight','وزن ارتفاع المرتجع الداخل','High return-in contribution.','مساهمة ارتفاع المرتجع الداخل.','NUMBER',17,0,100,'POINTS',130],
        ['SIGNAL_WEIGHT','W_TRANSFER_SPIKE','Transfer spike weight','وزن ارتفاع التحويل','Unusual transfer contribution.','مساهمة التحويل غير المعتاد.','NUMBER',14,0,100,'POINTS',140],
        ['SIGNAL_WEIGHT','W_AFTER_HOURS','After-hours weight','وزن خارج ساعات العمل','Movement concentration outside daytime hours.','تركيز الحركة خارج ساعات النهار.','NUMBER',7,0,100,'POINTS',150],
        ['SIGNAL_WEIGHT','W_SINGLE_USER','Single-user weight','وزن المستخدم الواحد','High volume concentrated under one user.','حجم مرتفع مركز لدى مستخدم واحد.','NUMBER',9,0,100,'POINTS',160],
        ['SIGNAL_WEIGHT','W_USER_TRACE_GAP','User trace gap weight','وزن فجوة توثيق المستخدم','Missing user trace contribution.','مساهمة نقص توثيق المستخدم.','NUMBER',12,0,100,'POINTS',170],
        ['SIGNAL_WEIGHT','W_ROUND_TRIP','Same-day round-trip weight','وزن الدخول والخروج المتقارب','Inbound and outbound quantities close on same day.','تقارب الدخول والخروج في اليوم نفسه.','NUMBER',9,0,100,'POINTS',180],
        ['SIGNAL_WEIGHT','W_INV_NET_CHANGE','Inventory net-change weight','وزن تغير صافي الجرد','Inventory event with non-zero net quantity.','جرد مع تغير غير صفري في الصافي.','NUMBER',7,0,100,'POINTS',190],
        ['SIGNAL_WEIGHT','W_BONUS_TWO_SIGNALS','Two-signal bonus','مكافأة إشارتين','Bonus for two independent indicator families.','مكافأة اجتماع عائلتين مستقلتين من المؤشرات.','NUMBER',3,0,30,'POINTS',200],
        ['SIGNAL_WEIGHT','W_BONUS_THREE_SIGNALS','Three-signal bonus','مكافأة ثلاث إشارات','Bonus for three or more independent indicator families.','مكافأة اجتماع ثلاث عائلات أو أكثر من المؤشرات.','NUMBER',6,0,30,'POINTS',210],
        ['EVENT_CORRELATION','EVENT_MIN_INV_RECON','Inventory and reconciliation members','أعضاء حدث الجرد والتسوية','Minimum members for a mass inventory and reconciliation event.','الحد الأدنى لأعضاء حدث الجرد والتسوية الجماعي.','NUMBER',8,2,500,'MEMBERS',10],
        ['EVENT_CORRELATION','EVENT_MIN_RECON','Reconciliation members','أعضاء حدث التسوية','Minimum members for a mass reconciliation event.','الحد الأدنى لأعضاء حدث التسوية الجماعي.','NUMBER',10,2,500,'MEMBERS',20],
        ['EVENT_CORRELATION','EVENT_MIN_INV_ONLY','Inventory-only members','أعضاء حدث الجرد فقط','Minimum members for an inventory-only event.','الحد الأدنى لأعضاء حدث الجرد فقط.','NUMBER',8,2,500,'MEMBERS',30],
        ['EVENT_CORRELATION','EVENT_OUTLIER_QTY_RATIO','Batch outlier quantity ratio','نسبة كمية الشاذ داخل الحدث','Independent non-adjustment outbound ratio required to retain an outlier.','نسبة الصادر المستقل المطلوبة للاحتفاظ بالشاذ.','NUMBER',0.25,0,1,'RATIO',40],
        ['EVENT_CORRELATION','EVENT_OUTLIER_MIN_SCORE','Batch outlier context score','نقاط سياق الشاذ داخل الحدث','Minimum independent context score to retain a batch outlier.','الحد الأدنى لنقاط السياق المستقل للاحتفاظ بالشاذ.','NUMBER',10,0,100,'POINTS',50],
        ['EVENT_CORRELATION','EVENT_SUPPRESSION_ENABLED','Event suppression enabled','تفعيل تخفيض تكرار الأحداث','Preserve evidence while removing duplicate work-queue members.','حفظ الأدلة مع إزالة التكرار من قائمة العمل.','BOOLEAN',1,null,null,'ON_OFF',60],
        ['OPERATIONS','DEFAULT_ANALYSIS_DAYS','Default analysis period','فترة التحليل الافتراضية','Default number of historical days.','عدد الأيام التاريخية الافتراضي.','NUMBER',180,30,730,'DAYS',10],
        ['OPERATIONS','MAX_EXPORT_ROWS','Maximum export rows','الحد الأقصى لصفوف التصدير','Maximum rows in one operational export.','أقصى عدد صفوف في ملف تصدير واحد.','NUMBER',50000,1000,1000000,'ROWS',20],
    ];
    foreach ($params as $p) { seedParam($branch,$versionId,$p); }

    foreach ([['NJCH','INCLUDE'],['MNH','INCLUDE'],['JCKH','EXCLUDE']] as $s) {
        $scopeId=nextId($branch,'DASH_DIAR_SCOPE_SEQ');
        Database::execute($branch, "MERGE INTO DASH_DIAR_RULE_SCOPES t
            USING (SELECT :version_id version_id,'BRANCH' scope_type,:scope_code scope_code FROM dual) s
            ON (t.VERSION_ID=s.version_id AND t.SCOPE_TYPE=s.scope_type AND t.SCOPE_CODE=s.scope_code)
            WHEN MATCHED THEN UPDATE SET EFFECT_CODE=:effect_code,IS_ACTIVE=1,
                NOTES='Initial DIAR-4A controlled scope'
            WHEN NOT MATCHED THEN INSERT (
                SCOPE_ID,VERSION_ID,SCOPE_TYPE,SCOPE_CODE,EFFECT_CODE,IS_ACTIVE,NOTES
            ) VALUES (
                :scope_id,:version_id,'BRANCH',:scope_code,:effect_code,1,
                'Initial DIAR-4A controlled scope'
            )", ['scope_id'=>$scopeId,'version_id'=>$versionId,'scope_code'=>$s[0],'effect_code'=>$s[1]]);
    }

    $configRows = Database::fetchAll($branch, "SELECT PARAM_CODE param_code,VALUE_TYPE value_type,
        NUM_VALUE num_value,TEXT_VALUE text_value,BOOL_VALUE bool_value
        FROM DASH_DIAR_RULE_PARAMS WHERE VERSION_ID=:version_id ORDER BY PARAM_CODE", ['version_id'=>$versionId]);
    $canonical=[];
    foreach ($configRows as $row) {
        $canonical[(string)$row['param_code']] = $row['value_type']==='NUMBER' ? (float)$row['num_value'] : ($row['value_type']==='BOOLEAN' ? (int)$row['bool_value'] : (string)$row['text_value']);
    }
    $scopes=Database::fetchAll($branch,"SELECT SCOPE_CODE scope_code,EFFECT_CODE effect_code FROM DASH_DIAR_RULE_SCOPES WHERE VERSION_ID=:version_id ORDER BY SCOPE_CODE",['version_id'=>$versionId]);
    foreach($scopes as $scope){$canonical['SCOPE_'.(string)$scope['scope_code']] = (string)$scope['effect_code'];}
    ksort($canonical);
    $hash=hash('sha256',json_encode($canonical,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));
    Database::execute($branch,"UPDATE DASH_DIAR_RULE_VERSIONS SET CONFIG_HASH=:config_hash WHERE VERSION_ID=:version_id",['config_hash'=>$hash,'version_id'=>$versionId]);

    Database::execute($branch, "MERGE INTO DASH_ACL_ROLE_OVERRIDES t
        USING (SELECT ROLE_CODE role_code,'diar.rules.view' permission_code FROM DASH_ACL_ROLES WHERE ROLE_CODE='GENERAL_MANAGER') s
        ON (t.ROLE_CODE=s.role_code AND t.PERMISSION_CODE=s.permission_code)
        WHEN NOT MATCHED THEN INSERT (
            ROLE_CODE,PERMISSION_CODE,EFFECT,UPDATED_BY,UPDATED_AT
        ) VALUES ('GENERAL_MANAGER','diar.rules.view','DENY','DIAR4A_R1',SYSDATE)");

    $result['checks']['identity'] = 'PASS';
    $result['checks']['central_acl_reused'] = 'PASS';
    $result['checks']['control_tables_created'] = 'PASS';
    $result['checks']['initial_rules_seeded'] = 'PASS';
    $result['checks']['operational_table_writes'] = 'NONE';
    $result['status'] = 'PASS';
    $result['active_version_id'] = $versionId;
    $result['config_hash'] = $hash;
} catch (Throwable $error) {
    $result['error'] = preg_replace('/\s+/', ' ', (string)$error->getMessage());
}

echo json_encode($result, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES), PHP_EOL;
exit($result['status'] === 'PASS' ? 0 : 1);
