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

CREATE TABLE verifiable_credential
(
    credential_id VARCHAR(50)  NOT NULL,
    user_id       VARCHAR(100) NOT NULL,
    tenant_id     VARCHAR(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    VARCHAR(36)  NOT NULL,
    event_type  VARCHAR(255) NOT NULL,
    payload     TEXT         NOT NULL,
    created_at  BIGINT       NOT NULL,
    tie_breaker INT          NOT NULL,
    tenant_id   VARCHAR(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      SMALLINT DEFAULT 1 PRIMARY KEY CHECK (pk = 1),
    version VARCHAR(10) NOT NULL
);
INSERT INTO schema_metadata (version)
VALUES ('8.6');
