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

CREATE TABLE device_usage
(
    user_id           VARCHAR(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
    device_type       VARCHAR(20) COLLATE utf8mb4_0900_bin    NOT NULL,
    device_id         VARCHAR(50) COLLATE utf8mb4_0900_bin    NOT NULL,
    total_usages      INTEGER                                 NOT NULL,
    first_usage_date  BIGINT                                  NOT NULL,
    latest_usage_date BIGINT                                  NOT NULL,
    tenant_id         VARCHAR(50) COLLATE utf8mb4_0900_bin    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) COLLATE utf8mb4_0900_bin NOT NULL,
    expires_at BIGINT                                NOT NULL,
    tenant_id  VARCHAR(50) COLLATE utf8mb4_0900_bin  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 COLUMN session_id VARCHAR(256) COLLATE utf8mb4_0900_bin NOT NULL;
ALTER TABLE user_trail_log
    MODIFY COLUMN request_id VARCHAR(256) COLLATE utf8mb4_0900_bin NOT NULL;
