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

 IMPORTANT: Please set the correct timezone below in which IAM is running in the FUNCTION datetime2ToNanos below!
*/
CREATE FUNCTION dbo.datetime2ToNanos(@dt DATETIME2)
    RETURNS BIGINT
BEGIN
    DECLARE @time_zone VARCHAR(50) = 'Central European Standard Time';
    RETURN CAST(DATEDIFF(SECOND, '19700101 00:00:00', @dt AT TIME ZONE @time_zone) AS BIGINT) * 1000000000
        + DATEPART(NANOSECOND, @dt AT TIME ZONE @time_zone)
END;
GO


-- Rename columns to not use reserved SQL keywords
EXEC sp_rename 'oauth2_session_attribute.value', 'content', 'COLUMN';

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

-- Drop medusa_pwd_selfservice_token table
DROP TABLE medusa_pwd_selfservice_token;

CREATE TABLE user_trail_log
(
    pk                    BIGINT                                            NOT NULL IDENTITY (1, 1),
    created_at            BIGINT                                            NOT NULL,
    log_level             NVARCHAR(8) COLLATE SQL_Latin1_General_CP1_CS_AS  NOT NULL,
    session_id            NVARCHAR(32) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    request_id            NVARCHAR(32) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    configuration_context NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
    client_ip             NVARCHAR(45) COLLATE Latin1_General_CI_AI         NOT NULL,
    target_id             NVARCHAR(100) COLLATE Latin1_General_CI_AI        NOT NULL,
    authentee_id          NVARCHAR(100) COLLATE Latin1_General_CI_AI        NOT NULL,
    authentee_provided_id NVARCHAR(100) COLLATE Latin1_General_CI_AI        NOT NULL,
    message               NVARCHAR(4000) COLLATE Latin1_General_CI_AI       NOT NULL,
    tenant_id             NVARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS 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
IF EXISTS(SELECT *
          FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_NAME = 'statistics_raw_data'
            AND TABLE_SCHEMA = 'dbo')
    DROP TABLE statistics_raw_data;

-- 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,
        validity_range_lower_new BIGINT,
        validity_range_upper_new BIGINT,
        generation_date_new BIGINT,
        first_usage_date_new BIGINT,
        latest_usage_date_new BIGINT;
GO;
UPDATE token
SET activation_date_new      = dbo.datetime2ToNanos(activation_date),
    validity_range_lower_new = dbo.datetime2ToNanos(validity_range_lower),
    validity_range_upper_new = dbo.datetime2ToNanos(validity_range_upper),
    generation_date_new      = dbo.datetime2ToNanos(generation_date),
    first_usage_date_new     = dbo.datetime2ToNanos(first_usage_date),
    latest_usage_date_new    = dbo.datetime2ToNanos(latest_usage_date)
WHERE 1 = 1;
ALTER TABLE token
    DROP COLUMN activation_date, validity_range_lower, validity_range_upper, generation_date, first_usage_date, latest_usage_date;
EXEC sp_rename 'token.activation_date_new', 'activation_date', 'COLUMN';
EXEC sp_rename 'token.validity_range_lower_new', 'validity_range_lower', 'COLUMN';
EXEC sp_rename 'token.validity_range_upper_new', 'validity_range_upper', 'COLUMN';
EXEC sp_rename 'token.generation_date_new', 'generation_date', 'COLUMN';
EXEC sp_rename 'token.first_usage_date_new', 'first_usage_date', 'COLUMN';
EXEC sp_rename 'token.latest_usage_date_new', 'latest_usage_date', 'COLUMN';

-- Migrate all timestamps on token_assignment to BIGINT
ALTER TABLE token_assignment
    ADD
        ta_assignment_date_new BIGINT,
        ta_order_new_date_new BIGINT;
GO;
UPDATE token_assignment
SET ta_assignment_date_new = dbo.datetime2ToNanos(ta_assignment_date),
    ta_order_new_date_new  = dbo.datetime2ToNanos(ta_order_new_date)
WHERE 1 = 1;
ALTER TABLE token_assignment
    ALTER COLUMN ta_assignment_date_new BIGINT NOT NULL;
ALTER TABLE token_assignment
    DROP COLUMN ta_assignment_date, ta_order_new_date;
EXEC sp_rename 'token_assignment.ta_assignment_date_new', 'ta_assignment_date', 'COLUMN';
EXEC sp_rename 'token_assignment.ta_order_new_date_new', 'ta_order_new_date', 'COLUMN';

DROP FUNCTION dbo.datetime2ToNanos;
