--
-- This script creates the Airlock IAM default schema for MSSQL.
--
-- Please ensure that the database supports UTF-8 character encoding.
-- This should be the case for any MSSQL database.

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');

CREATE TABLE medusa_user
(
    -- General authentication information fields
    username                      NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    auth_method                   NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    next_auth_method              NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    auth_migration_date           DATETIME2,
    locked                        INT DEFAULT 0                                      NOT NULL,
    lock_reason                   NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    lock_date                     DATETIME2,
    valid                         INT DEFAULT 1                                      NOT NULL,
    not_valid_before              DATETIME2,
    not_valid_after               DATETIME2,

    -- Statistic data
    failed_logins                 INT,
    failed_logins_before          INT,
    failed_token_counts           NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    total_logins                  INT,
    lat_succ_login                DATETIME2,
    lat_succ_login2               DATETIME2,
    lat_login_attempt             DATETIME2,
    first_login                   DATETIME2,
    unlock_attempts               INT,
    lat_unlock_attempt            DATETIME2,

    -- self-registration data
    self_registered               INT DEFAULT 0                                      NOT NULL,
    self_registration_date        DATETIME2,
    inactivity_reminder_sent_date DATETIME2,
    channel_verification_resends  INT,

    -- Role settings
    roles                         NVARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CS_AS,

    -- User context data
    salutation                    NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    givenname                     NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    surname                       NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    street                        NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    streetNumber                  NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    address2                      NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    zipcode                       NVARCHAR(12) COLLATE SQL_Latin1_General_CP1_CS_AS,
    town                          NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    state                         NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    country                       NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    company                       NVARCHAR(75) COLLATE SQL_Latin1_General_CP1_CS_AS,
    email                         NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    new_email                     NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    language                      NCHAR(2) COLLATE SQL_Latin1_General_CP1_CS_AS,
    birthdate                     DATE,
    disclaimer_tag                NVARCHAR(1024) COLLATE SQL_Latin1_General_CP1_CS_AS,
    remember_me_secret            NVARCHAR(1024) COLLATE SQL_Latin1_General_CP1_CS_AS,
    remember_me_gen_date          DATETIME2,
    realm                         NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,

    -- Technical database fields
    rowVersionId                  INT DEFAULT 0                                      NOT NULL,
    rowInsertDate                 DATETIME2,
    rowInsertUser                 NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    rowUpdateDate                 DATETIME2,
    rowUpdateUser                 NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    deleted                       INT DEFAULT 0                                      NOT NULL,

    -- Password related fields
    pwd_hash                      NVARCHAR(4000) COLLATE SQL_Latin1_General_CP1_CS_AS,
    pwd_chg_enf                   INT DEFAULT 0                                      NOT NULL,
    pwd_lat_del                   DATETIME2,
    pwd_lat_gen                   DATETIME2,
    pwd_lat_chg                   DATETIME2,
    pwd_next_chg                  DATETIME2,
    pwd_order_new                 INT DEFAULT 0                                      NOT NULL,
    pwd_order_user                NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    pwd_order_date                DATETIME2,
    pwd_failed_resets             INT DEFAULT 0                                      NOT NULL,

    -- MTAN (SMS) related fields (including IAK)
    mtan_number                   NVARCHAR(25) COLLATE SQL_Latin1_General_CP1_CS_AS,
    mtan_del_date                 DATETIME2,
    mtan_ass_date                 DATETIME2,
    mtan_order_new                INT DEFAULT 0                                      NOT NULL,
    mtan_order_user               NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    mtan_order_date               DATETIME2,
    mtan_iak                      NVARCHAR(4000) COLLATE SQL_Latin1_General_CP1_CS_AS,

    -- Certificate related fields (including IAK and self registration)
    cert_subject_cn               NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    cert_x509_data                NVARCHAR(4000) COLLATE SQL_Latin1_General_CP1_CS_AS,
    cert_serial                   NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    cert_del_date                 DATETIME2,
    cert_ass_date                 DATETIME2,
    cert_order_new                INT DEFAULT 0                                      NOT NULL,
    cert_order_user               NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    cert_order_date               DATETIME2,
    cert_iak                      NVARCHAR(4000) COLLATE SQL_Latin1_General_CP1_CS_AS,

    -- OATH OTP related fields
    oathotp_data                  NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    oathotp_serial                NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    oathotp_del_date              DATETIME2,
    oathotp_gen_date              DATETIME2,
    oathotp_order_new             INT DEFAULT 0                                      NOT NULL,
    oathotp_order_user            NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    oathotp_order_date            DATETIME2,

    -- SecurID related fields
    securid_user                  NVARCHAR(25) COLLATE SQL_Latin1_General_CP1_CS_AS,
    securid_serial                NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    securid_pin                   NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,

    -- Matrix cards (TAN lists) related fields
    matrix_current_list           VARBINARY(MAX),
    matrix_next_list              VARBINARY(MAX),
    matrix_del_date               DATETIME2,
    matrix_gen_date               DATETIME2,
    matrix_order_new              INT DEFAULT 0                                      NOT NULL,
    matrix_order_user             NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    matrix_order_date             DATETIME2,
    matrix_chal_open_since        DATETIME2,
    matrix_open_chals             INT DEFAULT 0                                      NOT NULL,

    -- Only one login possible
    last_gsid_date                DATETIME2,
    last_gsid_value               NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,

    -- Secret questions features per user
    secret_questions_enabled      INT DEFAULT 0                                      NOT NULL,

    CONSTRAINT pk_medusa_user PRIMARY KEY (username)
);

CREATE TABLE medusa_admin
(
    -- General authentication information fields
    username                     NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    locked                       INT DEFAULT 0                                      NOT NULL,
    lock_reason                  NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    lock_date                    DATETIME2,
    not_valid_before             DATETIME2,
    not_valid_after              DATETIME2,

    -- Statistic data
    failed_logins                INT,
    failed_logins_before         INT,
    failed_first_factor_attempts INT,
    failed_stepup_attempts       INT,
    total_logins                 INT,
    lat_succ_login               DATETIME2,
    lat_succ_login2              DATETIME2,
    lat_login_attempt            DATETIME2,
    first_login                  DATETIME2,

    -- Role settings
    roles                        NVARCHAR(1000) COLLATE SQL_Latin1_General_CP1_CS_AS,

    -- User context data
    givenname                    NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    surname                      NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    email                        NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    realm                        NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,

    -- Technical database fields
    rowVersionId                 INT DEFAULT 0                                      NOT NULL,
    rowInsertDate                DATETIME2,
    rowInsertUser                NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    rowUpdateDate                DATETIME2,
    rowUpdateUser                NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    deleted                      INT DEFAULT 0                                      NOT NULL,

    -- Password related fields
    pwd_hash                     NVARCHAR(4000) COLLATE SQL_Latin1_General_CP1_CS_AS,
    pwd_chg_enf                  INT DEFAULT 0                                      NOT NULL,
    pwd_lat_del                  DATETIME2,
    pwd_lat_gen                  DATETIME2,
    pwd_lat_chg                  DATETIME2,
    pwd_next_chg                 DATETIME2,
    pwd_order_new                INT DEFAULT 0                                      NOT NULL,

    CONSTRAINT pk_medusa_admin PRIMARY KEY (username)
);

CREATE TABLE medusa_maint_msg
(
    name             NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    system_available INT DEFAULT 1                                      NOT NULL,
    active           INT DEFAULT 1                                      NOT NULL,
    valid_from       DATETIME2                                          NOT NULL,
    valid_to         DATETIME2                                          NOT NULL,
    location         NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    -- Technical database fields
    rowVersionId     INT DEFAULT 0                                      NOT NULL,
    rowInsertDate    DATETIME2,
    rowInsertUser    NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    rowUpdateDate    DATETIME2,
    rowUpdateUser    NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    deleted          INT DEFAULT 0                                      NOT NULL,

    CONSTRAINT pk_medusa_maint_msg PRIMARY KEY (name)
);

CREATE TABLE medusa_maint_msg_tnsl
(
    message_ref   NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    language      NVARCHAR(2) COLLATE SQL_Latin1_General_CP1_CS_AS    NOT NULL,
    message       NVARCHAR(4000) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    -- Technical database fields
    rowVersionId  INT DEFAULT 0                                       NOT NULL,
    rowInsertDate DATETIME2,
    rowInsertUser NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    rowUpdateDate DATETIME2,
    rowUpdateUser NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    deleted       INT DEFAULT 0                                       NOT NULL,

    CONSTRAINT pk_medusa_maint_msg_tnsl PRIMARY KEY (message_ref, language)
);

CREATE TABLE token
(
    token_id                BIGINT            NOT NULL IDENTITY (1, 1),
    type                    NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    serial_id               NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    active                  TINYINT DEFAULT 0 NOT NULL,
    activation_date         BIGINT,
    obsoletes_token_id      BIGINT,
    validity_range_lower    BIGINT,
    validity_range_upper    BIGINT,
    generation_date         BIGINT,
    first_usage_date        BIGINT,
    latest_usage_date       BIGINT,
    total_usages            INT     DEFAULT 0 NOT NULL,
    token_data              NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    activates_token_id      BIGINT,
    tracking_id             NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_1  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_2  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_3  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_4  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_5  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_6  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_7  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_8  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_9  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_10 NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_11 NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    generic_data_element_12 NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,

    CONSTRAINT pk_token_id PRIMARY KEY (token_id)
);
-- FOREIGN KEY indexes
CREATE INDEX obsoletes_token_id_idx
    ON token (obsoletes_token_id);
ALTER TABLE token
    ADD CONSTRAINT fk_obsoletes_token_id FOREIGN KEY (obsoletes_token_id) REFERENCES token (token_id);
CREATE INDEX activates_token_id_index
    ON token (activates_token_id);
ALTER TABLE token
    ADD CONSTRAINT fk_activates_token_id FOREIGN KEY (activates_token_id) REFERENCES token (token_id);
-- Additional indexes
CREATE INDEX token_type_index
    ON token (type);
CREATE INDEX token_serial_id_index
    ON token (serial_id);
-- Indexes on generic_data_element_X are not possible due to their size.

CREATE TABLE token_assignment
(
    ta_token_id               BIGINT                                             NOT NULL,
    ta_user                   NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    ta_assignment_date        BIGINT                                             NOT NULL,
    ta_assignment_user        NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    ta_order_new              TINYINT DEFAULT 0                                  NOT NULL,
    ta_order_new_date         BIGINT,
    ta_order_new_user         NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    ta_order_options          NVARCHAR(250) COLLATE SQL_Latin1_General_CP1_CS_AS,
    ta_additional_information NVARCHAR(250) COLLATE SQL_Latin1_General_CP1_CS_AS,
    ta_comment                NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,

    CONSTRAINT pk_ta_token_id_user PRIMARY KEY (ta_token_id, ta_user)
);
-- FOREIGN KEY indexes
ALTER TABLE token_assignment
    ADD CONSTRAINT fk_ta_token_id FOREIGN KEY (ta_token_id) REFERENCES token (token_id) ON DELETE CASCADE;
-- Additional indexes
CREATE INDEX ta_index
    ON token_assignment (ta_user, ta_token_id);

CREATE TABLE login_history
(
    id                     INT                                                NOT NULL IDENTITY (1, 1),
    username               NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    login_timestamp        DATETIME2                                          NOT NULL,
    ip                     NVARCHAR(45) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    header_user_agent      NVARCHAR(512) COLLATE SQL_Latin1_General_CP1_CS_AS,
    header_accept_encoding NVARCHAR(512) COLLATE SQL_Latin1_General_CP1_CS_AS,
    header_accept_language NVARCHAR(512) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geoloc_present         INT                                                NOT NULL,
    geoloc_continent       NCHAR(2) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geoloc_country         NCHAR(2) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geoloc_subdivision     NCHAR(3) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geoloc_city            NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geoloc_zip             NVARCHAR(32) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geoloc_timezone        NVARCHAR(128) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geoloc_latitude        NVARCHAR(16) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geoloc_longitude       NVARCHAR(16) COLLATE SQL_Latin1_General_CP1_CS_AS,

    CONSTRAINT pk_login_history PRIMARY KEY (id)
);
-- Additional indexes
CREATE INDEX login_history_idx
    ON login_history (username);

CREATE TABLE account_link
(
    link_id        INT                                                NOT NULL IDENTITY (1, 1),
    username       NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    provider_id    NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    account_sub    NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    account_info   NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS,
    established_at DATETIME2                                          NOT NULL,
    tenant_id      NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,

    CONSTRAINT pk_account_link PRIMARY KEY (link_id)
);
-- Additional indexes
CREATE INDEX account_link_idx_username
    ON account_link (username, tenant_id);
CREATE INDEX account_link_idx_usr_prov
    ON account_link (username, provider_id, tenant_id);
CREATE UNIQUE INDEX account_link_idx_unique
    ON account_link (account_sub, provider_id, tenant_id);

CREATE TABLE subscription
(
    pk           INT                                                NOT NULL IDENTITY (1, 1),
    id           NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    display_name 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_subscription PRIMARY KEY (pk)
);
-- Additional indexes
CREATE UNIQUE INDEX subscription_id_idx_unique
    ON subscription (id);

CREATE TABLE principal
(
    pk              INT                                                NOT NULL IDENTITY (1, 1),
    id              NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    display_name    NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    locked          INT DEFAULT 0                                      NOT NULL,
    lock_reason     NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    lock_date       DATETIME2,
    creation_date   DATETIME2                                          NOT NULL,
    tenant_id       NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    description     NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    label           NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    subscription_pk INT,

    CONSTRAINT pk_principal PRIMARY KEY (pk)
);
-- FOREIGN KEY indexes
CREATE INDEX subscription_idx
    ON principal (subscription_pk);
ALTER TABLE principal
    ADD CONSTRAINT fk_subscription_pk FOREIGN KEY (subscription_pk) REFERENCES subscription (pk);
-- Additional indexes
CREATE UNIQUE INDEX principal_id_idx_unique
    ON principal (id);
CREATE INDEX tenant_id_idx
    ON principal (tenant_id);

CREATE TABLE oauth2_attributes
(
    pk                         INT                                               NOT NULL IDENTITY (1, 1),
    principal_pk               INT                                               NOT NULL,
    authorization_server_id    NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    client_id                  NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    client_secret              NVARCHAR(450) COLLATE SQL_Latin1_General_CP1_CS_AS,
    client_id_issued_at        DATETIME2                                         NOT NULL,
    client_secret_expires_at   DATETIME2,
    redirect_uris              NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    token_endpoint_auth_method NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    grant_types                NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    response_types             NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    client_name                NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    client_uri                 NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    logo_uri                   NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    scopes                     NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    contacts                   NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS,
    tos_uri                    NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    policy_uri                 NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    jwks_uri                   NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,
    software_id                NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    software_version           NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS,
    cert_issuer                NVARCHAR(450) COLLATE SQL_Latin1_General_CP1_CS_AS,
    cert_subject               NVARCHAR(450) COLLATE SQL_Latin1_General_CP1_CS_AS,
    tenant_id                  NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,

    CONSTRAINT pk_oauth2_attributes PRIMARY KEY (pk)
);
-- FOREIGN KEY indexes
CREATE UNIQUE INDEX oa2_attrs_principal_idx_unique
    ON oauth2_attributes (principal_pk);
ALTER TABLE oauth2_attributes
    ADD CONSTRAINT fk_oa_principal_pk FOREIGN KEY (principal_pk) REFERENCES principal (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX oa2_attrs_as_cid_idx_unique
    ON oauth2_attributes (authorization_server_id, client_id, tenant_id);

CREATE TABLE oauth2_attribute_translation
(
    oauth2_attributes_pk INT NOT NULL,
    attribute_name       NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    locale               NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS,
    text                 NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS
);
-- FOREIGN KEY indexes
CREATE INDEX oauth2_attributes_pk_idx
    ON oauth2_attribute_translation (oauth2_attributes_pk);
ALTER TABLE oauth2_attribute_translation
    ADD CONSTRAINT fk_oauth2_attributes_pk FOREIGN KEY (oauth2_attributes_pk) REFERENCES oauth2_attributes (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX oauth2_translation_idx_unique
    ON oauth2_attribute_translation (oauth2_attributes_pk, attribute_name, locale);

CREATE TABLE oauth2_session
(
    pk     BIGINT                                            NOT NULL IDENTITY (1, 1),
    id     NVARCHAR(40) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    claims NVARCHAR(MAX) COLLATE Latin1_General_CI_AI,

    CONSTRAINT pk_oauth2_session PRIMARY KEY (pk)
);
-- Additional indexes
CREATE UNIQUE INDEX ux_oauth2_session_id
    ON oauth2_session (id);

CREATE TABLE oauth2_session_attribute
(
    oauth2_session_pk BIGINT                                            NOT NULL,
    name              NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    content           NVARCHAR(200) COLLATE Latin1_General_CI_AI        NOT NULL
);
-- FOREIGN KEY indexes
CREATE INDEX ix_osa_oauth2_session_pk
    ON oauth2_session_attribute (oauth2_session_pk);
ALTER TABLE oauth2_session_attribute
    ADD CONSTRAINT fk_oauth2_session_pk FOREIGN KEY (oauth2_session_pk) REFERENCES oauth2_session (pk) ON DELETE CASCADE;

CREATE TABLE client_cert_credential
(
    id           NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    principal_pk INT                                                NOT NULL,
    serial       NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    issuer       NVARCHAR(450) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    subject      NVARCHAR(450) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    not_before   DATETIME2                                          NOT NULL,
    not_after    DATETIME2                                          NOT NULL,
    certificate  NVARCHAR(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    fingerprint  NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    tenant_id    NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL
);
-- FOREIGN KEY indexes
CREATE INDEX principal_pk_idx
    ON client_cert_credential (principal_pk);
ALTER TABLE client_cert_credential
    ADD CONSTRAINT fk_ccc_principal_pk FOREIGN KEY (principal_pk) REFERENCES principal (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX ccc_id_idx_unique
    ON client_cert_credential (id);
CREATE UNIQUE INDEX fingerprint_idx_unique
    ON client_cert_credential (fingerprint, tenant_id);
CREATE INDEX ccc_subject_idx
    ON client_cert_credential (subject);

CREATE TABLE airlock_2fa_user
(
    pk                  INT                                                NOT NULL IDENTITY (1, 1),
    iam_user_id         NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    airlock_2fa_user_id NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    tenant_id           NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,

    CONSTRAINT pk_airlock_2fa_user PRIMARY KEY (pk)
);
-- Additional indexes
CREATE UNIQUE INDEX a2fa_iam_user_id_idx_unique
    ON airlock_2fa_user (iam_user_id, tenant_id);
CREATE UNIQUE INDEX a2fa_user_id_idx_unique
    ON airlock_2fa_user (airlock_2fa_user_id);

CREATE TABLE airlock_2fa_activation_letter
(
    pk                  INT                                                NOT NULL IDENTITY (1, 1),
    id                  NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    activation_code     NVARCHAR(750) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    airlock_2fa_user_pk INT                                                NOT NULL,
    creator             NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,

    CONSTRAINT pk_2fa_activation_letter PRIMARY KEY (pk)
);
-- FOREIGN KEY indexes
CREATE INDEX fk_activation_letter_user_pk
    ON airlock_2fa_activation_letter (airlock_2fa_user_pk);
ALTER TABLE airlock_2fa_activation_letter
    ADD CONSTRAINT fk_activation_letter_user_pk FOREIGN KEY (airlock_2fa_user_pk) REFERENCES airlock_2fa_user (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX a2fa_letter_id_idx_unique
    ON airlock_2fa_activation_letter (id);

CREATE TABLE airlock_2fa_activation_order
(
    id                  NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    airlock_2fa_user_pk INT                                                NOT NULL,
    creator             NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    ordered_at          BIGINT                                             NOT NULL
);
-- FOREIGN KEY indexes
CREATE INDEX ix_a2fa_activ_order_user_pk
    ON airlock_2fa_activation_order (airlock_2fa_user_pk);
ALTER TABLE airlock_2fa_activation_order
    ADD CONSTRAINT fk_a2fa_activ_order_user_pk FOREIGN KEY (airlock_2fa_user_pk) REFERENCES airlock_2fa_user (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX ux_a2fa_activ_order_id
    ON airlock_2fa_activation_order (id);

CREATE TABLE api_key
(
    id            NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    principal_pk  INT                                                NOT NULL,
    fingerprint   NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    secret        NVARCHAR(450) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    description   NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS,
    valid_from    DATETIME2,
    valid_to      DATETIME2,
    locked        INT DEFAULT 0                                      NOT NULL,
    creation_date DATETIME2                                          NOT NULL,
    tenant_id     NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL
);
-- FOREIGN KEY indexes
CREATE INDEX fk_api_key_principal_pk
    ON api_key (principal_pk);
ALTER TABLE api_key
    ADD CONSTRAINT fk_api_key_principal_pk FOREIGN KEY (principal_pk) REFERENCES principal (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX api_key_id_idx_unique
    ON api_key (id);
CREATE UNIQUE INDEX api_key_fp_idx_unique
    ON api_key (fingerprint, tenant_id);

CREATE TABLE plan_usage
(
    id              NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    subscription_pk INT                                                NOT NULL,
    name            NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    rate_limit      INT
);
-- FOREIGN KEY indexes
CREATE INDEX fk_plan_usage_subscription_pk
    ON plan_usage (subscription_pk);
ALTER TABLE plan_usage
    ADD CONSTRAINT fk_plan_usage_subscription_pk FOREIGN KEY (subscription_pk) REFERENCES subscription (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX plan_usage_subscr_idx_unique
    ON plan_usage (name, subscription_pk);
CREATE UNIQUE INDEX plan_usage_id_idx_unique
    ON plan_usage (id);

CREATE TABLE last_selection
(
    last_selection_id INT                                                NOT NULL IDENTITY (1, 1),
    username          NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    option_id         NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    selected_at       DATETIME2                                          NOT NULL,
    step_id           NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    tenant_id         NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,

    CONSTRAINT pk_last_selection PRIMARY KEY (last_selection_id)
);
-- Additional indexes
CREATE UNIQUE INDEX ux_last_selection
    ON last_selection (username, tenant_id, step_id);

CREATE TABLE fido_user
(
    pk               BIGINT                                             NOT NULL IDENTITY (1, 1),
    iam_user_id      NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    user_handle      NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    relying_party_id NVARCHAR(255) COLLATE Latin1_General_CI_AI         NOT NULL,
    tenant_id        NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,

    CONSTRAINT pk_fido_user PRIMARY KEY (pk)
);
-- Additional indexes
CREATE UNIQUE INDEX ux_fido_user_handle
    ON fido_user (user_handle);
CREATE UNIQUE INDEX ux_fido_iam_user_rpid
    ON fido_user (iam_user_id, relying_party_id, tenant_id);

CREATE TABLE fido_credential
(
    id                            NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    fido_user_pk                  BIGINT                                            NOT NULL,
    name                          NVARCHAR(100) COLLATE Latin1_General_CI_AI        NOT NULL,
    display_name                  NVARCHAR(100) COLLATE Latin1_General_CI_AI        NOT NULL,
    public_key_credential_id      VARBINARY(MAX)                                    NOT NULL,
    public_key_credential_id_hash VARBINARY(64)                                     NOT NULL,
    public_key                    VARBINARY(1024)                                   NOT NULL,
    signature_counter             BIGINT DEFAULT 0                                  NOT NULL,
    resident_requested            TINYINT                                           NOT NULL,
    attestation_object            VARBINARY(MAX)                                    NOT NULL,
    registered_at                 BIGINT                                            NOT NULL,
    first_used_at                 BIGINT,
    last_used_at                  BIGINT,
    total_usages                  INT    DEFAULT 0                                  NOT NULL,
    locked                        TINYINT                                           NOT NULL,
    transports                    NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS
);
-- FOREIGN KEY indexes
CREATE INDEX ix_fido_credential_user_pk
    ON fido_credential (fido_user_pk);
ALTER TABLE fido_credential
    ADD CONSTRAINT fk_fido_credential_user_pk FOREIGN KEY (fido_user_pk) REFERENCES fido_user (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX ux_fido_cred_id
    ON fido_credential (id);
CREATE UNIQUE INDEX ux_fido_cred_id_hash
    ON fido_credential (public_key_credential_id_hash);

CREATE TABLE remember_me_token
(
    pk          BIGINT                                             NOT NULL IDENTITY (1, 1),
    id          NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    iam_user_id NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    token_hash  VARBINARY(64)                                      NOT NULL,
    created_at  BIGINT                                             NOT NULL,
    renewed_at  BIGINT,
    tenant_id   NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,

    CONSTRAINT pk_remember_me_token PRIMARY KEY (pk)
);
-- Additional indexes
CREATE INDEX ix_remember_me_iam_user_id on remember_me_token (iam_user_id);
CREATE INDEX ix_remember_me_token_created_at on remember_me_token (created_at, tenant_id);
CREATE INDEX ix_remember_me_token_renewed_at on remember_me_token (renewed_at, tenant_id);
CREATE UNIQUE INDEX ux_remember_me_id ON remember_me_token (id, tenant_id);
CREATE UNIQUE INDEX ux_remember_me_token_hash on remember_me_token (token_hash, tenant_id);

CREATE TABLE remember_me_request_metadata
(
    pk                      BIGINT                                            NOT NULL IDENTITY (1, 1),
    remember_me_token_pk    BIGINT                                            NOT NULL,
    request_type            TINYINT                                           NOT NULL,
    request_ip              NVARCHAR(45) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    header_user_agent       NVARCHAR(4000) COLLATE Latin1_General_CI_AI,
    geolocation_continent   NVARCHAR(2) COLLATE Latin1_General_CI_AI,
    geolocation_country     NVARCHAR(2) COLLATE Latin1_General_CI_AI,
    geolocation_subdivision NVARCHAR(3) COLLATE Latin1_General_CI_AI,
    geolocation_city        NVARCHAR(256) COLLATE Latin1_General_CI_AI,
    geolocation_zip         NVARCHAR(32) COLLATE Latin1_General_CI_AI,
    geolocation_timezone    NVARCHAR(128) COLLATE Latin1_General_CI_AI,
    geolocation_latitude    NVARCHAR(16) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geolocation_longitude   NVARCHAR(16) COLLATE SQL_Latin1_General_CP1_CS_AS,

    CONSTRAINT pk_remember_me_request_metadata PRIMARY KEY (pk)
);
-- FOREIGN KEY indexes
ALTER TABLE remember_me_request_metadata
    ADD CONSTRAINT fk_remember_me_token_pk FOREIGN KEY (remember_me_token_pk) REFERENCES remember_me_token (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX ux_remember_me_token_request_type ON remember_me_request_metadata (remember_me_token_pk, request_type);

CREATE TABLE flow_continuation_token
(
    pk                    BIGINT                                            NOT NULL IDENTITY (1, 1),
    token_hash            NVARCHAR(86) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    user_id               NVARCHAR(100) COLLATE Latin1_General_CI_AI        NOT NULL,
    target_flow_id        NVARCHAR(30) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    created_at            BIGINT                                            NOT NULL,
    valid_until           BIGINT                                            NOT NULL,
    configuration_context NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    forward_url           NVARCHAR(200) COLLATE Latin1_General_CI_AI,
    tenant_id             NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,

    CONSTRAINT pk_flow_continuation_token PRIMARY KEY (pk)
);
-- Additional indexes
CREATE UNIQUE INDEX ux_flow_continuation_token ON flow_continuation_token (token_hash);

CREATE TABLE user_trail_log
(
    pk                    BIGINT                                             NOT NULL IDENTITY (1, 1),
    created_at            BIGINT                                             NOT NULL,
    log_level             NVARCHAR(8) COLLATE SQL_Latin1_General_CP1_CS_AS   NOT NULL,
    session_id            NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    request_id            NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    configuration_context NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    client_ip             NVARCHAR(45) COLLATE Latin1_General_CI_AI          NOT NULL,
    target_id             NVARCHAR(100) COLLATE Latin1_General_CI_AI         NOT NULL,
    authentee_id          NVARCHAR(100) COLLATE Latin1_General_CI_AI         NOT NULL,
    authentee_provided_id NVARCHAR(100) COLLATE Latin1_General_CI_AI         NOT NULL,
    message               NVARCHAR(4000) COLLATE Latin1_General_CI_AI        NOT NULL,
    tenant_id             NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,

    CONSTRAINT pk_user_trail_log PRIMARY KEY (pk)
);
-- Additional indexes
CREATE INDEX ix_user_trail_log_target_id ON user_trail_log (target_id, created_at);
CREATE INDEX ix_user_trail_log_created_at ON user_trail_log (created_at);

CREATE TABLE device_usage
(
    user_id           NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    device_type       NVARCHAR(20) COLLATE Latin1_General_CI_AI          NOT NULL,
    device_id         NVARCHAR(50) COLLATE Latin1_General_CI_AI          NOT NULL,
    total_usages      INT                                                NOT NULL,
    first_usage_date  BIGINT                                             NOT NULL,
    latest_usage_date BIGINT                                             NOT NULL,
    tenant_id         NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL
);

-- Additional indexes
CREATE UNIQUE INDEX ux_device_usage ON device_usage (device_id, user_id, device_type, tenant_id);

CREATE TABLE accepted_sso_tickets
(
    ticket_id  NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    expires_at BIGINT                                             NOT NULL,
    tenant_id  NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL
);
-- Additional indexes
CREATE UNIQUE INDEX ux_accepted_sso_tickets ON accepted_sso_tickets (ticket_id, tenant_id);
CREATE INDEX ix_accepted_sso_tickets_expires_at ON accepted_sso_tickets (expires_at);

CREATE TABLE oauth2_consent
(
    id                      NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    user_id                 NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    authorization_server_id NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    client_id               NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    scope_name              NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    status                  NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    updated_at              BIGINT                                             NOT NULL,
    tenant_id               NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL
);
-- Additional indexes
CREATE UNIQUE INDEX ux_oauth2_consent_id ON oauth2_consent (id);
CREATE INDEX ix_oauth2_consent_user_as_client ON oauth2_consent (user_id, authorization_server_id, client_id);

CREATE TABLE oauth2_par_request
(
    request_uri_reference   NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    authorization_server_id NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    client_id               NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    query_string            NVARCHAR(MAX) COLLATE Latin1_General_CI_AI        NOT NULL,
    valid_until             BIGINT                                            NOT NULL,
    tenant_id               NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
);
-- Additional indexes
CREATE UNIQUE INDEX ux_oauth2_par_request_uri ON oauth2_par_request (request_uri_reference);
CREATE INDEX ix_oauth2_par_request_valid_until ON oauth2_par_request (valid_until ASC);

CREATE TABLE oauth2_accepted_client_assertions
(
    jti        NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    expires_at BIGINT                                             NOT NULL,
    tenant_id  NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL
);
-- Additional indexes
CREATE UNIQUE INDEX ux_oauth2_accepted_client_assertions ON oauth2_accepted_client_assertions (jti);
CREATE INDEX ix_oauth2_accepted_client_assertions_expires_at ON oauth2_accepted_client_assertions (expires_at ASC);

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)
);
-- Additional indexes
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);
