/*
 Migrates the Airlock IAM DB schema from version 8.1 to version 8.2.
 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_consent
(
    id                      VARCHAR2(36)  NOT NULL,
    user_id                 VARCHAR2(100) NOT NULL,
    authorization_server_id VARCHAR2(50)  NOT NULL,
    client_id               VARCHAR2(50)  NOT NULL,
    scope_name              VARCHAR2(50)  NOT NULL,
    status                  VARCHAR2(20)  NOT NULL,
    updated_at              NUMBER(19)    NOT NULL,
    tenant_id               VARCHAR2(50)  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);
