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

CREATE TABLE verifiable_credential
(
    credential_id VARCHAR(50) COLLATE utf8mb4_nopad_bin         NOT NULL,
    user_id       VARCHAR(100) COLLATE utf8mb4_general_nopad_ci NOT NULL,
    tenant_id     VARCHAR(50) COLLATE utf8mb4_nopad_bin         NOT NULL,

    CONSTRAINT pk_verifiable_credential PRIMARY KEY (credential_id)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_520_nopad_ci;
CREATE INDEX ix_verifiable_credential_user_id ON verifiable_credential (user_id);


CREATE TABLE event_outbox
(
    event_id    VARCHAR(36) COLLATE utf8mb4_nopad_bin  NOT NULL,
    event_type  VARCHAR(255) COLLATE utf8mb4_nopad_bin NOT NULL,
    payload     TEXT COLLATE utf8mb4_general_nopad_ci  NOT NULL,
    created_at  BIGINT                                 NOT NULL,
    tie_breaker INTEGER                                NOT NULL,
    tenant_id   VARCHAR(50) COLLATE utf8mb4_nopad_bin  NOT NULL,

    CONSTRAINT pk_event_outbox PRIMARY KEY (event_id)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_520_nopad_ci;

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 VARCHAR(10) COLLATE utf8mb4_nopad_bin NOT NULL
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_520_nopad_ci;
INSERT INTO schema_metadata (version)
VALUES ('8.6');
