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

-- Comment out 'SET sqlblanklines on' if you're using a SQL client other than Oracle SQL*Plus
SET sqlblanklines on

ALTER SESSION SET NLS_LENGTH_SEMANTICS='CHAR';

CREATE TABLE remember_me_token
(
    pk          NUMBER(19)    NOT NULL,
    id          VARCHAR2(36)  NOT NULL,
    iam_user_id VARCHAR2(100) NOT NULL,
    token_hash  RAW(64)       NOT NULL,
    created_at  NUMBER(19)    NOT NULL,
    renewed_at  NUMBER(19),
    tenant_id   VARCHAR2(50)  NOT NULL,

    CONSTRAINT pk_remember_me_token PRIMARY KEY (pk)
);
-- Sequence for pk
CREATE SEQUENCE remember_me_token_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

-- 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                      NUMBER(19)   NOT NULL,
    remember_me_token_pk    NUMBER(19)   NOT NULL,
    request_type            NUMBER(1)    NOT NULL,
    request_ip              VARCHAR2(45) NOT NULL,
    header_user_agent       VARCHAR2(4000),
    geolocation_continent   VARCHAR2(2),
    geolocation_country     VARCHAR2(2),
    geolocation_subdivision VARCHAR2(3),
    geolocation_city        VARCHAR2(256),
    geolocation_zip         VARCHAR2(32),
    geolocation_timezone    VARCHAR2(128),
    geolocation_latitude    VARCHAR2(16),
    geolocation_longitude   VARCHAR2(16),

    CONSTRAINT pk_remember_me_request_metadata PRIMARY KEY (pk)
);
-- Sequence for pk
CREATE SEQUENCE remember_me_request_metadata_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

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