/*
 Migrates the Airlock IAM DB schema from version 7.5 to version 7.6.
 Written for MySQL.
 */

ALTER TABLE airlock_2fa_activation_order
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- --------------------

CREATE TABLE remember_me_token
(
    pk          BIGINT                                  NOT NULL AUTO_INCREMENT,
    id          VARCHAR(36) COLLATE utf8mb4_0900_bin    NOT NULL,
    iam_user_id VARCHAR(100) COLLATE utf8mb4_0900_ai_ci NOT NULL,
    token_hash  VARBINARY(64)                           NOT NULL,
    created_at  BIGINT                                  NOT NULL,
    renewed_at  BIGINT,
    tenant_id   VARCHAR(50) COLLATE utf8mb4_0900_bin    NOT NULL,

    CONSTRAINT pk_remember_me_token PRIMARY KEY (pk)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
-- Additional indexes
CREATE INDEX ix_remember_me_iam_user_id on remember_me_token (iam_user_id);
CREATE INDEX ix_remember_me_token_created_at on remember_me_token (created_at, tenant_id);
CREATE INDEX ix_remember_me_token_renewed_at on remember_me_token (renewed_at, tenant_id);
CREATE UNIQUE INDEX ux_remember_me_id ON remember_me_token (id, tenant_id);
CREATE UNIQUE INDEX ux_remember_me_token_hash on remember_me_token (token_hash, tenant_id);

CREATE TABLE remember_me_request_metadata
(
    pk                      BIGINT                               NOT NULL AUTO_INCREMENT,
    remember_me_token_pk    BIGINT                               NOT NULL,
    request_type            TINYINT                              NOT NULL,
    request_ip              VARCHAR(45) COLLATE utf8mb4_0900_bin NOT NULL,
    header_user_agent       VARCHAR(4000) COLLATE utf8mb4_0900_ai_ci,
    geolocation_continent   VARCHAR(2) COLLATE utf8mb4_0900_ai_ci,
    geolocation_country     VARCHAR(2) COLLATE utf8mb4_0900_ai_ci,
    geolocation_subdivision VARCHAR(3) COLLATE utf8mb4_0900_ai_ci,
    geolocation_city        VARCHAR(256) COLLATE utf8mb4_0900_ai_ci,
    geolocation_zip         VARCHAR(32) COLLATE utf8mb4_0900_ai_ci,
    geolocation_timezone    VARCHAR(128) COLLATE utf8mb4_0900_ai_ci,
    geolocation_latitude    VARCHAR(16) COLLATE utf8mb4_0900_bin,
    geolocation_longitude   VARCHAR(16) COLLATE utf8mb4_0900_bin,

    CONSTRAINT pk_remember_me_request_metadata PRIMARY KEY (pk)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
-- FOREIGN KEY indexes
ALTER TABLE remember_me_request_metadata
    ADD CONSTRAINT fk_remember_me_token_pk FOREIGN KEY (remember_me_token_pk) REFERENCES remember_me_token (pk) ON DELETE CASCADE;
-- Additional indexes
CREATE UNIQUE INDEX ux_remember_me_token_request_type ON remember_me_request_metadata (remember_me_token_pk, request_type);