/*
 Migrates the Airlock IAM DB schema from version 8.2 to version 8.3.
 Written for MSSQL.
 */

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

ALTER TABLE oauth2_session
    ADD claims NVARCHAR(MAX) COLLATE Latin1_General_CI_AI;
