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

-- Comment out 'SET sqlblanklines on' if you're using a SQL client other than Oracle SQL*Plus
SET sqlblanklines on

ALTER SESSION SET NLS_LENGTH_SEMANTICS='CHAR';

CREATE TABLE oauth2_par_request
(
    request_uri_reference   VARCHAR2(50) NOT NULL,
    authorization_server_id VARCHAR2(50) NOT NULL,
    client_id               VARCHAR2(50) NOT NULL,
    query_string            CLOB        NOT NULL,
    valid_until             NUMBER(19)  NOT NULL,
    tenant_id               VARCHAR2(50) 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        VARCHAR2(100) NOT NULL,
    expires_at NUMBER(19)    NOT NULL,
    tenant_id  VARCHAR2(50)  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 CLOB;

-- Fix wrong VARCHAR definition
ALTER TABLE accepted_sso_tickets
    MODIFY ticket_id VARCHAR2(100);
