-- DIAR-4B Investigation Case Management
-- Target: NJCH / ORANJC / WATANY
-- Operational MM* writes: NONE

SET SERVEROUTPUT ON
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK

DECLARE
  PROCEDURE create_table_if_missing(p_name VARCHAR2, p_ddl CLOB) IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM USER_TABLES WHERE TABLE_NAME = UPPER(p_name);
    IF v_count = 0 THEN EXECUTE IMMEDIATE p_ddl; END IF;
  END;
  PROCEDURE create_seq_if_missing(p_name VARCHAR2) IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM USER_SEQUENCES WHERE SEQUENCE_NAME = UPPER(p_name);
    IF v_count = 0 THEN
      EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || p_name || ' START WITH 1 INCREMENT BY 1 NOCACHE';
    END IF;
  END;
  PROCEDURE create_index_if_missing(p_name VARCHAR2, p_ddl VARCHAR2) IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM USER_INDEXES WHERE INDEX_NAME = UPPER(p_name);
    IF v_count = 0 THEN EXECUTE IMMEDIATE p_ddl; END IF;
  END;
BEGIN
  create_table_if_missing('DASH_DIAR_IMPORT_RUNS', q'[
    CREATE TABLE 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'))
    )
  ]');

  create_table_if_missing('DASH_DIAR_SOURCE_CASES', q'[
    CREATE TABLE 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'))
    )
  ]');

  create_table_if_missing('DASH_DIAR_CASES', q'[
    CREATE TABLE 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'))
    )
  ]');

  create_table_if_missing('DASH_DIAR_CASE_ASSIGNMENTS', q'[
    CREATE TABLE 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'))
    )
  ]');

  create_table_if_missing('DASH_DIAR_CASE_HISTORY', q'[
    CREATE TABLE 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)
    )
  ]');

  create_table_if_missing('DASH_DIAR_CASE_NOTES', q'[
    CREATE TABLE 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))
    )
  ]');

  create_table_if_missing('DASH_DIAR_CASE_EVIDENCE', q'[
    CREATE TABLE 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'))
    )
  ]');

  create_seq_if_missing('DASH_DIAR_IMPORT_SEQ');
  create_seq_if_missing('DASH_DIAR_SOURCE_SEQ');
  create_seq_if_missing('DASH_DIAR_CASE_SEQ');
  create_seq_if_missing('DASH_DIAR_ASSIGN_SEQ');
  create_seq_if_missing('DASH_DIAR_HISTORY_SEQ');
  create_seq_if_missing('DASH_DIAR_NOTE_SEQ');
  create_seq_if_missing('DASH_DIAR_EVID_SEQ');

  create_index_if_missing('IX_DIAR_SRC_IMPORT', 'CREATE INDEX IX_DIAR_SRC_IMPORT ON DASH_DIAR_SOURCE_CASES (IMPORT_RUN_ID, SITE_CODE)');
  create_index_if_missing('IX_DIAR_SRC_EVENT', 'CREATE INDEX IX_DIAR_SRC_EVENT ON DASH_DIAR_SOURCE_CASES (EVENT_CODE, MEMBER_DISPOSITION)');
  create_index_if_missing('IX_DIAR_SRC_STORE_DAY', 'CREATE INDEX IX_DIAR_SRC_STORE_DAY ON DASH_DIAR_SOURCE_CASES (SITE_CODE, STORE_CODE, CALENDAR_DAY)');
  create_index_if_missing('IX_DIAR_CASE_QUEUE', 'CREATE INDEX IX_DIAR_CASE_QUEUE ON DASH_DIAR_CASES (STATUS_CODE, SEVERITY_CODE, UPDATED_AT)');
  create_index_if_missing('IX_DIAR_CASE_SCOPE', 'CREATE INDEX IX_DIAR_CASE_SCOPE ON DASH_DIAR_CASES (SITE_CODE, STORE_CODE, CALENDAR_DAY)');
  create_index_if_missing('IX_DIAR_CASE_PARENT', 'CREATE INDEX IX_DIAR_CASE_PARENT ON DASH_DIAR_CASES (PARENT_EVENT_CASE_ID)');
  create_index_if_missing('IX_DIAR_ASSIGN_ACTIVE', 'CREATE INDEX IX_DIAR_ASSIGN_ACTIVE ON DASH_DIAR_CASE_ASSIGNMENTS (CASE_ID, STATUS_CODE)');
  create_index_if_missing('UX_DIAR_ASSIGN_ONE_ACTIVE', q'[CREATE UNIQUE INDEX UX_DIAR_ASSIGN_ONE_ACTIVE ON DASH_DIAR_CASE_ASSIGNMENTS (CASE WHEN STATUS_CODE='ACTIVE' THEN CASE_ID ELSE NULL END)]');
  create_index_if_missing('IX_DIAR_HIST_CASE', 'CREATE INDEX IX_DIAR_HIST_CASE ON DASH_DIAR_CASE_HISTORY (CASE_ID, PERFORMED_AT)');
  create_index_if_missing('IX_DIAR_NOTE_CASE', 'CREATE INDEX IX_DIAR_NOTE_CASE ON DASH_DIAR_CASE_NOTES (CASE_ID, CREATED_AT)');
  create_index_if_missing('IX_DIAR_EVID_CASE', 'CREATE INDEX IX_DIAR_EVID_CASE ON DASH_DIAR_CASE_EVIDENCE (CASE_ID, ADDED_AT)');
END;
/
COMMIT;
