/*
Phase 10.6.2.1 — Executive Action Review Production Audit Trail
Oracle 30-character identifier compatible version.

سبب هذا الإصلاح:
بعض إصدارات Oracle القديمة لا تسمح بأسماء Constraints / Indexes / Sequences أطول من 30 حرفًا.
الجداول نفسها بقيت بنفس الأسماء المستخدمة في كود PHP:
  DASH_EXEC_ACTION_REVIEW
  DASH_EXEC_ACTION_REVIEW_AUDIT
لكن تم تقصير أسماء الـ Constraints / Indexes / Sequence.

Run once in the dashboard Oracle schema, normally WATANY.
Optional .env:
  EXEC_ACTION_REVIEW_SCHEMA=WATANY
  EXEC_ACTION_REVIEW_STORAGE=oracle
*/

-- Main latest-state table: one row per hospital + operational day + action key.
CREATE TABLE DASH_EXEC_ACTION_REVIEW (
    HOSPITAL_CODE       VARCHAR2(30)   NOT NULL,
    OPERATIONAL_DATE    DATE           NOT NULL,
    ACTION_KEY          VARCHAR2(150)  NOT NULL,
    STATUS              VARCHAR2(20)   DEFAULT 'open' NOT NULL,
    REVIEW_NOTES        VARCHAR2(2000),
    UPDATED_BY          VARCHAR2(150),
    UPDATED_AT          DATE           DEFAULT SYSDATE,
    CREATED_AT          DATE           DEFAULT SYSDATE,
    ACTION_TITLE        VARCHAR2(500),
    ACTION_SEVERITY     VARCHAR2(30),
    ACTION_CATEGORY     VARCHAR2(80),
    ACTION_METRIC       VARCHAR2(120),
    ACTION_SNAPSHOT     VARCHAR2(4000),
    REVIEW_COUNT        NUMBER         DEFAULT 0,
    CONSTRAINT PK_DASH_ACT_REV PRIMARY KEY (HOSPITAL_CODE, OPERATIONAL_DATE, ACTION_KEY),
    CONSTRAINT CK_DASH_ACT_REV_ST CHECK (STATUS IN ('open','reviewed','deferred'))
);

CREATE INDEX IX_DASH_ACT_REV_01
    ON DASH_EXEC_ACTION_REVIEW (HOSPITAL_CODE, STATUS, OPERATIONAL_DATE);

-- Audit sequence and immutable history table.
-- Short name because DASH_EXEC_ACTION_REVIEW_AUDIT_S is 31 chars on old Oracle.
CREATE SEQUENCE DASH_EXEC_ACT_REV_AUD_S
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

CREATE TABLE DASH_EXEC_ACTION_REVIEW_AUDIT (
    AUDIT_ID            NUMBER         NOT NULL,
    HOSPITAL_CODE       VARCHAR2(30)   NOT NULL,
    OPERATIONAL_DATE    DATE           NOT NULL,
    ACTION_KEY          VARCHAR2(150)  NOT NULL,
    OLD_STATUS          VARCHAR2(20),
    NEW_STATUS          VARCHAR2(20)   NOT NULL,
    OLD_NOTE            VARCHAR2(2000),
    NEW_NOTE            VARCHAR2(2000),
    CHANGED_BY          VARCHAR2(150),
    CHANGED_AT          DATE           DEFAULT SYSDATE,
    ACTION_SNAPSHOT     VARCHAR2(4000),
    CONSTRAINT PK_DASH_ACT_REV_AUD PRIMARY KEY (AUDIT_ID),
    CONSTRAINT CK_DASH_ACT_AUD_ST CHECK (NEW_STATUS IN ('open','reviewed','deferred'))
);

CREATE INDEX IX_DASH_ACT_AUD_01
    ON DASH_EXEC_ACTION_REVIEW_AUDIT (HOSPITAL_CODE, OPERATIONAL_DATE, ACTION_KEY);

CREATE INDEX IX_DASH_ACT_AUD_02
    ON DASH_EXEC_ACTION_REVIEW_AUDIT (HOSPITAL_CODE, CHANGED_AT);

-- Optional verification:
SELECT table_name FROM user_tables
 WHERE table_name IN ('DASH_EXEC_ACTION_REVIEW','DASH_EXEC_ACTION_REVIEW_AUDIT');

SELECT sequence_name FROM user_sequences
 WHERE sequence_name = 'DASH_EXEC_ACT_REV_AUD_S';
