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

CREATE TABLE remember_me_token
(
    pk          BIGINT                                             NOT NULL IDENTITY (1, 1),
    id          NVARCHAR(36) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    iam_user_id NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    token_hash  VARBINARY(64)                                      NOT NULL,
    created_at  BIGINT                                             NOT NULL,
    renewed_at  BIGINT,
    tenant_id   NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS  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 IDENTITY (1, 1),
    remember_me_token_pk    BIGINT                                            NOT NULL,
    request_type            TINYINT                                           NOT NULL,
    request_ip              NVARCHAR(45) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    header_user_agent       NVARCHAR(4000) COLLATE Latin1_General_CI_AI,
    geolocation_continent   NVARCHAR(2) COLLATE Latin1_General_CI_AI,
    geolocation_country     NVARCHAR(2) COLLATE Latin1_General_CI_AI,
    geolocation_subdivision NVARCHAR(3) COLLATE Latin1_General_CI_AI,
    geolocation_city        NVARCHAR(256) COLLATE Latin1_General_CI_AI,
    geolocation_zip         NVARCHAR(32) COLLATE Latin1_General_CI_AI,
    geolocation_timezone    NVARCHAR(128) COLLATE Latin1_General_CI_AI,
    geolocation_latitude    NVARCHAR(16) COLLATE SQL_Latin1_General_CP1_CS_AS,
    geolocation_longitude   NVARCHAR(16) COLLATE SQL_Latin1_General_CP1_CS_AS,

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