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

 IMPORTANT: Please set the correct timezone below in which IAM is running!
 */
SET TIME ZONE 'Europe/Zurich';


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

-- Drop allowedIps
ALTER TABLE medusa_user
    DROP COLUMN ALLOWEDIPS;

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

-- Drop failed_stepup_attempts
ALTER TABLE medusa_user
    DROP COLUMN FAILED_STEPUP_ATTEMPTS;

-- Drop user_consent table
DROP TABLE user_consent CASCADE;

-- Drop medusa_pwd_selfservice_token table
DROP TABLE medusa_pwd_selfservice_token CASCADE;

CREATE TABLE user_trail_log
(
    pk                    BIGINT        NOT NULL AUTO_INCREMENT,
    created_at            BIGINT        NOT NULL,
    log_level             VARCHAR(8)    NOT NULL,
    session_id            VARCHAR(32)   NOT NULL,
    request_id            VARCHAR(32)   NOT NULL,
    configuration_context VARCHAR(50)   NOT NULL,
    client_ip             VARCHAR(45)   NOT NULL,
    target_id             VARCHAR(100)  NOT NULL,
    authentee_id          VARCHAR(100)  NOT NULL,
    authentee_provided_id VARCHAR(100)  NOT NULL,
    message               VARCHAR(4000) NOT NULL,
    tenant_id             VARCHAR(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

-- 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 BIGINT
ALTER TABLE token
    ADD activation_date_new BIGINT AFTER activation_date;
ALTER TABLE token
    ADD validity_range_lower_new BIGINT AFTER validity_range_lower;
ALTER TABLE token
    ADD validity_range_upper_new BIGINT AFTER validity_range_upper;
ALTER TABLE token
    ADD generation_date_new BIGINT AFTER generation_date;
ALTER TABLE token
    ADD first_usage_date_new BIGINT AFTER first_usage_date;
ALTER TABLE token
    ADD latest_usage_date_new BIGINT AFTER latest_usage_date;

UPDATE token
SET activation_date_new      = EXTRACT(EPOCH FROM CAST(activation_date AS TIMESTAMP WITH TIME ZONE)) * 1000000000,
    validity_range_lower_new = EXTRACT(EPOCH FROM CAST(validity_range_lower AS TIMESTAMP WITH TIME ZONE)) * 1000000000,
    validity_range_upper_new = EXTRACT(EPOCH FROM CAST(validity_range_upper AS TIMESTAMP WITH TIME ZONE)) * 1000000000,
    generation_date_new      = EXTRACT(EPOCH FROM CAST(generation_date AS TIMESTAMP WITH TIME ZONE)) * 1000000000,
    first_usage_date_new     = EXTRACT(EPOCH FROM CAST(first_usage_date AS TIMESTAMP WITH TIME ZONE)) * 1000000000,
    latest_usage_date_new    = EXTRACT(EPOCH FROM CAST(latest_usage_date AS TIMESTAMP WITH TIME ZONE)) * 1000000000
WHERE 1 = 1;

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


-- Migrate all timestamps on token_assignment to BIGINT
ALTER TABLE token_assignment
    ADD ta_assignment_date_new BIGINT AFTER ta_assignment_date;
ALTER TABLE token_assignment
    ADD ta_order_new_date_new BIGINT AFTER ta_order_new_date;

UPDATE token_assignment
SET ta_assignment_date_new = EXTRACT(EPOCH FROM CAST(ta_assignment_date AS TIMESTAMP WITH TIME ZONE)) * 1000000000,
    ta_order_new_date_new  = EXTRACT(EPOCH FROM CAST(ta_order_new_date AS TIMESTAMP WITH TIME ZONE)) * 1000000000
WHERE 1 = 1;

ALTER TABLE token_assignment
    ALTER COLUMN ta_assignment_date_new SET NOT NULL;
ALTER TABLE token_assignment
    DROP COLUMN (ta_assignment_date, ta_order_new_date);
ALTER TABLE token_assignment
    ALTER COLUMN ta_assignment_date_new RENAME TO ta_assignment_date;
ALTER TABLE token_assignment
    ALTER COLUMN ta_order_new_date_new RENAME TO ta_order_new_date;
