/*
Phase 10.6.6 - Executive Review Governance, Ownership & Follow-up Management
Oracle 30-character identifier compatible schema.

Run ONCE in the dashboard Oracle schema (normally WATANY), then verify with:
  public/executive-action-governance-storage-check.php?hospital=njch

Optional .env overrides:
  EXEC_ACTION_GOVERNANCE_STORAGE=oracle
  EXEC_ACTION_GOVERNANCE_SCHEMA=WATANY
  EXEC_ACTION_GOVERNANCE_TABLE=DASH_EXEC_ACTION_GOV
  EXEC_ACTION_GOVERNANCE_AUDIT_TABLE=DASH_EXEC_ACTION_GOV_AUDIT
  EXEC_ACTION_GOVERNANCE_AUDIT_SEQUENCE=DASH_EXEC_ACT_GOV_AUD_S
*/

-- Current accountability state: one durable row per hospital + action key.
CREATE TABLE DASH_EXEC_ACTION_GOV (
    HOSPITAL_CODE          VARCHAR2(30)   NOT NULL,
    ACTION_KEY             VARCHAR2(150)  NOT NULL,
    FIRST_OPERATIONAL_DATE DATE           NOT NULL,
    LAST_OPERATIONAL_DATE  DATE           NOT NULL,
    OWNER_NAME             VARCHAR2(150),
    ASSIGNED_TO            VARCHAR2(150),
    DUE_DATE               DATE,
    FOLLOW_UP_DATE         DATE,
    ESCALATION_LEVEL       VARCHAR2(20)   DEFAULT 'none' NOT NULL,
    RESOLUTION_STATUS      VARCHAR2(20)   DEFAULT 'active' NOT NULL,
    RESOLUTION_NOTE        VARCHAR2(2000),
    RESOLVED_AT            DATE,
    RESOLVED_BY            VARCHAR2(150),
    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),
    UPDATE_COUNT           NUMBER         DEFAULT 0,
    CONSTRAINT PK_DASH_ACT_GOV PRIMARY KEY (HOSPITAL_CODE, ACTION_KEY),
    CONSTRAINT CK_DASH_ACT_GOV_ESC CHECK (ESCALATION_LEVEL IN ('none','watch','high','critical')),
    CONSTRAINT CK_DASH_ACT_GOV_RES CHECK (RESOLUTION_STATUS IN ('active','resolved'))
);

CREATE INDEX IX_DASH_ACT_GOV_01
    ON DASH_EXEC_ACTION_GOV (HOSPITAL_CODE, RESOLUTION_STATUS, DUE_DATE);

CREATE INDEX IX_DASH_ACT_GOV_02
    ON DASH_EXEC_ACTION_GOV (HOSPITAL_CODE, OWNER_NAME, ASSIGNED_TO);

-- Immutable audit sequence and history.
CREATE SEQUENCE DASH_EXEC_ACT_GOV_AUD_S
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

CREATE TABLE DASH_EXEC_ACTION_GOV_AUDIT (
    AUDIT_ID              NUMBER         NOT NULL,
    HOSPITAL_CODE         VARCHAR2(30)   NOT NULL,
    OPERATIONAL_DATE      DATE           NOT NULL,
    ACTION_KEY            VARCHAR2(150)  NOT NULL,
    OLD_OWNER_NAME        VARCHAR2(150),
    NEW_OWNER_NAME        VARCHAR2(150),
    OLD_ASSIGNED_TO       VARCHAR2(150),
    NEW_ASSIGNED_TO       VARCHAR2(150),
    OLD_DUE_DATE          DATE,
    NEW_DUE_DATE          DATE,
    OLD_FOLLOW_UP_DATE    DATE,
    NEW_FOLLOW_UP_DATE    DATE,
    OLD_ESC_LEVEL         VARCHAR2(20),
    NEW_ESC_LEVEL         VARCHAR2(20)   NOT NULL,
    OLD_RES_STATUS        VARCHAR2(20),
    NEW_RES_STATUS        VARCHAR2(20)   NOT NULL,
    OLD_RES_NOTE          VARCHAR2(2000),
    NEW_RES_NOTE          VARCHAR2(2000),
    CHANGED_BY            VARCHAR2(150),
    CHANGED_AT            DATE           DEFAULT SYSDATE,
    ACTION_SNAPSHOT       VARCHAR2(4000),
    CONSTRAINT PK_DASH_ACT_GOV_AUD PRIMARY KEY (AUDIT_ID),
    CONSTRAINT CK_DASH_ACT_GOV_AUD_ES CHECK (NEW_ESC_LEVEL IN ('none','watch','high','critical')),
    CONSTRAINT CK_DASH_ACT_GOV_AUD_RS CHECK (NEW_RES_STATUS IN ('active','resolved'))
);

CREATE INDEX IX_DASH_ACT_GAUD_01
    ON DASH_EXEC_ACTION_GOV_AUDIT (HOSPITAL_CODE, ACTION_KEY, CHANGED_AT);

CREATE INDEX IX_DASH_ACT_GAUD_02
    ON DASH_EXEC_ACTION_GOV_AUDIT (HOSPITAL_CODE, OPERATIONAL_DATE, CHANGED_AT);

-- Verification.
SELECT table_name
  FROM user_tables
 WHERE table_name IN ('DASH_EXEC_ACTION_GOV','DASH_EXEC_ACTION_GOV_AUDIT');

SELECT sequence_name
  FROM user_sequences
 WHERE sequence_name = 'DASH_EXEC_ACT_GOV_AUD_S';
