/*
 Migrates the Airlock IAM DB schema from version 8.0 to version 8.1.
 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 device_usage
(
    user_id           VARCHAR2(100) NOT NULL,
    device_type       VARCHAR2(20)  NOT NULL,
    device_id         VARCHAR2(50)  NOT NULL,
    total_usages      INTEGER       NOT NULL,
    first_usage_date  NUMBER(19)    NOT NULL,
    latest_usage_date NUMBER(19)    NOT NULL,
    tenant_id         VARCHAR2(50)  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  VARCHAR(100) NOT NULL,
    expires_at NUMBER(19)   NOT NULL,
    tenant_id  VARCHAR2(50) 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);

-- Required if Airlock Microgateway 4.x is used in front of Airlock IAM
ALTER TABLE user_trail_log
    MODIFY session_id VARCHAR2(256);
ALTER TABLE user_trail_log
    MODIFY request_id VARCHAR2(256);
