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

 IMPORTANT: Please set the correct timezone below in which IAM is running.
 If this fails, time zone information must be installed before continuing or conversion will always result in NULL!
 See https://mariadb.com/kb/en/mysql_tzinfo_to_sql/ for details on how to install time zone information
 */


-- Rename columns to not use reserved SQL keywords
ALTER TABLE oauth2_session_attribute
    CHANGE COLUMN value content VARCHAR(200) NOT NULL;

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

-- Drop medusa_pwd_selfservice_token table
DROP TABLE medusa_pwd_selfservice_token;

CREATE TABLE user_trail_log
(
    pk                    BIGINT                                         NOT NULL AUTO_INCREMENT,
    created_at            BIGINT                                         NOT NULL,
    log_level             VARCHAR(8) COLLATE utf8mb4_nopad_bin           NOT NULL,
    session_id            VARCHAR(32) COLLATE utf8mb4_nopad_bin          NOT NULL,
    request_id            VARCHAR(32) COLLATE utf8mb4_nopad_bin          NOT NULL,
    configuration_context VARCHAR(50) COLLATE utf8mb4_nopad_bin          NOT NULL,
    client_ip             VARCHAR(45) COLLATE utf8mb4_general_nopad_ci   NOT NULL,
    target_id             VARCHAR(100) COLLATE utf8mb4_general_nopad_ci  NOT NULL,
    authentee_id          VARCHAR(100) COLLATE utf8mb4_general_nopad_ci  NOT NULL,
    authentee_provided_id VARCHAR(100) COLLATE utf8mb4_general_nopad_ci  NOT NULL,
    message               VARCHAR(4000) COLLATE utf8mb4_general_nopad_ci NOT NULL,
    tenant_id             VARCHAR(50) COLLATE utf8mb4_nopad_bin          NOT NULL,

    CONSTRAINT pk_user_trail_log PRIMARY KEY (pk)
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_520_nopad_ci;
-- 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 COLUMN activation_date_new      BIGINT AFTER activation_date,
    ADD COLUMN validity_range_lower_new BIGINT AFTER validity_range_lower,
    ADD COLUMN validity_range_upper_new BIGINT AFTER validity_range_upper,
    ADD COLUMN generation_date_new      BIGINT AFTER generation_date,
    ADD COLUMN first_usage_date_new     BIGINT AFTER first_usage_date,
    ADD COLUMN latest_usage_date_new    BIGINT AFTER latest_usage_date;
UPDATE token
SET activation_date_new      = UNIX_TIMESTAMP(activation_date) * 1000000000,
    validity_range_lower_new = UNIX_TIMESTAMP(validity_range_lower) * 1000000000,
    validity_range_upper_new = UNIX_TIMESTAMP(validity_range_upper) * 1000000000,
    generation_date_new      = UNIX_TIMESTAMP(generation_date) * 1000000000,
    first_usage_date_new     = UNIX_TIMESTAMP(first_usage_date) * 1000000000,
    latest_usage_date_new    = UNIX_TIMESTAMP(latest_usage_date) * 1000000000
WHERE 1 = 1;
ALTER TABLE token
    DROP COLUMN activation_date,
    DROP COLUMN validity_range_lower,
    DROP COLUMN validity_range_upper,
    DROP COLUMN generation_date,
    DROP COLUMN first_usage_date,
    DROP COLUMN latest_usage_date;
ALTER TABLE token
    CHANGE COLUMN activation_date_new activation_date BIGINT,
    CHANGE COLUMN validity_range_lower_new validity_range_lower BIGINT,
    CHANGE COLUMN validity_range_upper_new validity_range_upper BIGINT,
    CHANGE COLUMN generation_date_new generation_date BIGINT,
    CHANGE COLUMN first_usage_date_new first_usage_date BIGINT,
    CHANGE COLUMN latest_usage_date_new latest_usage_date BIGINT;


-- Migrate all timestamps on token_assignment to BIGINT
ALTER TABLE token_assignment
    ADD COLUMN ta_assignment_date_new BIGINT AFTER ta_assignment_date,
    ADD COLUMN ta_order_new_date_new  BIGINT AFTER ta_order_new_date;
UPDATE token_assignment
SET ta_assignment_date_new = UNIX_TIMESTAMP(ta_assignment_date) * 1000000000,
    ta_order_new_date_new  = UNIX_TIMESTAMP(ta_order_new_date) * 1000000000
WHERE 1 = 1;
ALTER TABLE token_assignment
    DROP COLUMN ta_assignment_date,
    DROP COLUMN ta_order_new_date;
ALTER TABLE token_assignment
    CHANGE COLUMN ta_assignment_date_new ta_assignment_date BIGINT NOT NULL,
    CHANGE COLUMN ta_order_new_date_new ta_order_new_date BIGINT;

