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

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

    CONSTRAINT pk_remember_me_token PRIMARY KEY (pk)
);
-- 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) NOT NULL,
    header_user_agent       VARCHAR(4000),
    geolocation_continent   VARCHAR(2),
    geolocation_country     VARCHAR(2),
    geolocation_subdivision VARCHAR(3),
    geolocation_city        VARCHAR(256),
    geolocation_zip         VARCHAR(32),
    geolocation_timezone    VARCHAR(128),
    geolocation_latitude    VARCHAR(16),
    geolocation_longitude   VARCHAR(16),

    CONSTRAINT pk_remember_me_request_metadata PRIMARY KEY (pk)
);
-- 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);