/*
 Migrates the Airlock IAM DB schema from version 8.5 to version 8.6.
 Written for Oracle DB.
 */

-- Comment out 'SET sqlblanklines on' if you're using a SQL client other than Oracle SQL*Plus
SET sqlblanklines on

ALTER SESSION SET NLS_LENGTH_SEMANTICS='CHAR';


CREATE TABLE verifiable_credential
(
    credential_id VARCHAR2(50)  NOT NULL,
    user_id       VARCHAR2(100) NOT NULL,
    tenant_id     VARCHAR2(50)  NOT NULL,

    CONSTRAINT pk_verifiable_credential PRIMARY KEY (credential_id)
);
CREATE INDEX ix_verifiable_credential_user_id ON verifiable_credential (user_id);


CREATE TABLE event_outbox
(
    event_id    VARCHAR2(36)  NOT NULL,
    event_type  VARCHAR2(255) NOT NULL,
    payload     CLOB          NOT NULL,
    created_at  NUMBER(19)    NOT NULL,
    tie_breaker NUMBER(10)    NOT NULL,
    tenant_id   VARCHAR2(50)  NOT NULL,

    CONSTRAINT pk_event_outbox PRIMARY KEY (event_id)
);
CREATE INDEX idx_event_outbox ON event_outbox (tenant_id, created_at, tie_breaker);


CREATE TABLE schema_metadata
(
    pk      NUMBER(1) DEFAULT 1 PRIMARY KEY CHECK (pk = 1),
    version VARCHAR2(10) NOT NULL
);
INSERT INTO schema_metadata (version)
VALUES ('8.6');
