/*
 Migrates the Airlock IAM DB schema from version 7.7 to version 8.0.
 Written for Oracle DB.

 IMPORTANT: Please set the correct timezone below in which IAM is running in the FUNCTION timestampToNanos below!
*/

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

-- Prepare function for timestamp migration; remember to update the timezone to the correct time zone used by IAM
CREATE OR REPLACE FUNCTION timestampToNanos(ts TIMESTAMP) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC
AS
    timezone varchar2(40) := 'Europe/Zurich';
BEGIN
    IF ts IS NULL THEN
        return NULL;
    ELSE
        return (EXTRACT(DAY FROM ((FROM_TZ(ts, timezone) - TIMESTAMP '1970-01-01 00:00:00 UTC') * 24 * 60)) * 60 +
                EXTRACT(SECOND FROM FROM_TZ(ts, timezone))
                   ) * 1000000000;
    END IF;
END timestampToNanos;
/

-- Rename columns to not use reserved SQL keywords
ALTER TABLE oauth2_session_attribute
    RENAME COLUMN value TO content;

-- Drop allowedIps
ALTER TABLE medusa_user
    DROP COLUMN allowedIPs;

-- Drop secovid related columns
-- IMPORTANT: Remove this if relying on secovid_data column
ALTER TABLE medusa_user
    DROP COLUMN secovid_data;
-- IMPORTANT: Remove this if relying on secovid_serial column
ALTER TABLE medusa_user
    DROP COLUMN secovid_serial;

-- Drop failed_stepup_attempts
ALTER TABLE medusa_user
    DROP COLUMN failed_stepup_attempts;

-- Drop user_consent table
DROP TABLE user_consent CASCADE CONSTRAINTS;

-- Drop medusa_pwd_selfservice_token table
DROP TABLE medusa_pwd_selfservice_token CASCADE CONSTRAINTS;

CREATE TABLE user_trail_log
(
    pk                    NUMBER(19) GENERATED ALWAYS AS IDENTITY,
    created_at            NUMBER(19)     NOT NULL,
    log_level             VARCHAR2(8)    NOT NULL,
    session_id            VARCHAR2(32)   NOT NULL,
    request_id            VARCHAR2(32)   NOT NULL,
    configuration_context VARCHAR2(50)   NOT NULL,
    client_ip             VARCHAR2(45)   NOT NULL,
    target_id             VARCHAR2(100)  NOT NULL,
    authentee_id          VARCHAR2(100)  NOT NULL,
    authentee_provided_id VARCHAR2(100)  NOT NULL,
    message               VARCHAR2(4000) NOT NULL,
    tenant_id             VARCHAR2(50)   NOT NULL,

    CONSTRAINT pk_user_trail_log PRIMARY KEY (pk)
);

-- Additional indexes
CREATE INDEX ix_user_trail_log_target_id ON user_trail_log (target_id, created_at);
CREATE INDEX ix_user_trail_log_created_at ON user_trail_log (created_at);

-- Enable cascade on delete for token table
ALTER TABLE token_assignment
    DROP CONSTRAINT fk_ta_token_id;
ALTER TABLE token_assignment
    ADD CONSTRAINT fk_ta_token_id FOREIGN KEY (ta_token_id) REFERENCES token (token_id) ON DELETE CASCADE;

-- Delete all obsolete client-centric OAuth 2.0 tokens
DELETE
FROM token
WHERE type = 'OAUTH2'
  AND generic_data_element_12 IS NULL;

-- Drop statistics_raw_data table if it exists
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE statistics_raw_data';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;
/

-- Enable the usage of Vasco Cronto enhanced query.
UPDATE token
SET generic_data_element_3 = 'ASSIGNED'
WHERE type = 'VASCO'
  AND generic_data_element_2 = 'MASTER_ACTIVATION'
  AND generic_data_element_3 IS NULL
  AND EXISTS(SELECT ta_token_id FROM token_assignment WHERE token.token_id = token_assignment.ta_token_id);
UPDATE token
SET generic_data_element_3 = 'UNUSED'
WHERE type = 'VASCO'
  AND generic_data_element_2 = 'MASTER_ACTIVATION'
  AND generic_data_element_3 IS NULL;


-- Migrate all timestamps on token to NUMBER(19)
ALTER TABLE token
    ADD (
        activation_date_new NUMBER(19),
        validity_range_lower_new NUMBER(19),
        validity_range_upper_new NUMBER(19),
        generation_date_new NUMBER(19),
        first_usage_date_new NUMBER(19),
        latest_usage_date_new NUMBER(19)
        );

UPDATE token
SET activation_date_new      = timestampToNanos(activation_date),
    validity_range_lower_new = timestampToNanos(validity_range_lower),
    validity_range_upper_new = timestampToNanos(validity_range_upper),
    generation_date_new      = timestampToNanos(generation_date),
    first_usage_date_new     = timestampToNanos(first_usage_date),
    latest_usage_date_new    = timestampToNanos(latest_usage_date)
WHERE 1 = 1;

ALTER TABLE token
    DROP (activation_date, validity_range_lower, validity_range_upper, generation_date, first_usage_date,
          latest_usage_date);
ALTER TABLE token RENAME COLUMN activation_date_new TO activation_date;
ALTER TABLE token RENAME COLUMN validity_range_lower_new TO validity_range_lower;
ALTER TABLE token RENAME COLUMN validity_range_upper_new TO validity_range_upper;
ALTER TABLE token RENAME COLUMN generation_date_new TO generation_date;
ALTER TABLE token RENAME COLUMN first_usage_date_new TO first_usage_date;
ALTER TABLE token RENAME COLUMN latest_usage_date_new TO latest_usage_date;


-- Migrate all timestamps on token_assignment to NUMBER(19)
ALTER TABLE token_assignment
    ADD (
        ta_assignment_date_new NUMBER(19),
        ta_order_new_date_new NUMBER(19));
UPDATE token_assignment
SET ta_assignment_date_new = timestampToNanos(ta_assignment_date),
    ta_order_new_date_new  = timestampToNanos(ta_order_new_date)
WHERE 1 = 1;
ALTER TABLE token_assignment
    MODIFY (ta_assignment_date_new NOT NULL);
ALTER TABLE token_assignment
    DROP (ta_assignment_date, ta_order_new_date);
ALTER TABLE token_assignment RENAME COLUMN ta_assignment_date_new TO ta_assignment_date;
ALTER TABLE token_assignment RENAME COLUMN ta_order_new_date_new TO ta_order_new_date;

DROP FUNCTION timestampToNanos;
