/*
 Migrates the Airlock IAM DB schema from version 8.0 to version 8.1.
 Written for MSSQL.
*/

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

-- Required if Airlock Microgateway 4.x is used in front of Airlock IAM
ALTER TABLE user_trail_log
    ALTER COLUMN session_id NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL;
ALTER TABLE user_trail_log
    ALTER COLUMN request_id NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL;
