/*
 Migrates the Airlock IAM DB schema from version 8.2 to version 8.3.
 Written for MySQL.
 */

CREATE TABLE oauth2_par_request
(
    request_uri_reference   VARCHAR(50) COLLATE utf8mb4_0900_bin NOT NULL,
    authorization_server_id VARCHAR(50) COLLATE utf8mb4_0900_bin NOT NULL,
    client_id               VARCHAR(50) COLLATE utf8mb4_0900_bin NOT NULL,
    query_string            TEXT COLLATE utf8mb4_0900_ai_ci      NOT NULL,
    valid_until             BIGINT                               NOT NULL,
    tenant_id               VARCHAR(50) COLLATE utf8mb4_0900_bin NOT NULL
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
-- Additional indexes
CREATE UNIQUE INDEX ux_oauth2_par_request_uri ON oauth2_par_request (request_uri_reference);
CREATE INDEX ix_oauth2_par_request_valid_until ON oauth2_par_request (valid_until ASC);

CREATE TABLE oauth2_accepted_client_assertions
(
    jti        VARCHAR(100) COLLATE utf8mb4_0900_bin NOT NULL,
    expires_at BIGINT                                NOT NULL,
    tenant_id  VARCHAR(50) COLLATE utf8mb4_0900_bin  NOT NULL
) CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;
-- Additional indexes
CREATE UNIQUE INDEX ux_oauth2_accepted_client_assertions ON oauth2_accepted_client_assertions (jti);
CREATE INDEX ix_oauth2_accepted_client_assertions_expires_at ON oauth2_accepted_client_assertions (expires_at ASC);

ALTER TABLE oauth2_session
    ADD COLUMN claims TEXT COLLATE utf8mb4_0900_ai_ci;

-- Fix collation on oauth2_session
ALTER TABLE oauth2_session
    MODIFY id VARCHAR(40) COLLATE utf8mb4_0900_bin NOT NULL;
ALTER TABLE device_usage
    DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
ALTER TABLE accepted_sso_tickets
    DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
