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

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