SET foreign_key_checks = 0;

DROP TABLE IF EXISTS `service`;
CREATE TABLE `service` (
    `id` INT NOT NULL AUTO_INCREMENT,
     /* 255 utf-8 codepoints is max for InnoDB keys */
    `name` VARCHAR(255) NOT NULL,
    `key_params` VARCHAR(128) NOT NULL,
    `uri` VARCHAR(1024) NOT NULL,
    `uri_digest` VARCHAR(1024) NOT NULL,
    `check_uri` BOOLEAN NOT NULL,
    `execute_sync` BOOLEAN NOT NULL,
    `reuse_cert` BOOLEAN NOT NULL DEFAULT FALSE,
    `reused_cert_expiration_margin` BIGINT NOT NULL DEFAULT 86400,
    `store_to_system` BOOLEAN NOT NULL DEFAULT FALSE,
    `allow_devid_self_service` BOOLEAN NOT NULL DEFAULT FALSE,
    `max_slots_devid_self_service` INT NOT NULL DEFAULT 10,
    `devid_self_service_can_fetch_key` BOOLEAN NOT NULL DEFAULT FALSE,
    `install_smime_cert_to_ldap` BOOLEAN NOT NULL DEFAULT FALSE,
    `use_tpm_vsc` INT NOT NULL DEFAULT 0,
    `tpm_endorsement_cas` LONGTEXT NULL,
    `apply_smime_settings` BOOLEAN NOT NULL DEFAULT FALSE,
    `install_alt_security_identities` BOOLEAN NOT NULL DEFAULT FALSE,
    `apply_address_books` BOOLEAN NOT NULL DEFAULT FALSE,
    `hwsig_formula` VARCHAR(1024) NOT NULL,
    `split_domain` BOOLEAN NOT NULL,
    `cert_signer` INT NOT NULL DEFAULT 0,
    `require_email_in_san` BOOLEAN NOT NULL DEFAULT FALSE,
    `cn_to_san_link` TINYINT NOT NULL DEFAULT 0,
    `match_ou_of_csr_and_imported_cert` BOOLEAN NOT NULL DEFAULT TRUE,
    `allow_archiving_seats` BOOLEAN NOT NULL DEFAULT TRUE,
    `agent_auto_renew_seat_certs` BOOLEAN NOT NULL DEFAULT TRUE,
    `server_auto_renew_seat_certs` BOOLEAN NOT NULL DEFAULT FALSE,
    `use_seat_name_as_initial_default_for_cert_cn` BOOLEAN NOT NULL DEFAULT TRUE,
    `archive_seats_with_expired_cert` BOOLEAN NOT NULL DEFAULT FALSE,
    `allow_enrolling_archived_seats` BOOLEAN NOT NULL DEFAULT FALSE,
    `enable_web_agent` BOOLEAN NOT NULL DEFAULT FALSE,
    `keytalk_agent_keep_alive_interval` INT NULL DEFAULT 300,
    `comment` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`name`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_meta`;
CREATE TABLE `service_meta` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `created_utc` BIGINT NULL,
    `creator_info` VARCHAR(256) NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `ssl_plus_settings`;
CREATE TABLE `ssl_plus_settings` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `customer_id` VARCHAR(255) NOT NULL,
    `authentication_username` VARCHAR(256) NOT NULL,
    `authentication_password` VARCHAR(256) NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`customer_id`,`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `ssl_plus_order`;
CREATE TABLE `ssl_plus_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(255) NOT NULL, /* Order ID given to KeyTalk */
    `reserved_seat_name` VARCHAR(256) NOT NULL,
    `ssl_plus_settings_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`order_id`,`ssl_plus_settings_id`),
    FOREIGN KEY (`ssl_plus_settings_id`)
        REFERENCES `ssl_plus_settings`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `ssl_plus_placed_dv_certificate_order`;
CREATE TABLE `ssl_plus_placed_dv_certificate_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `placed_order_id` VARCHAR(255) NOT NULL, /* Internal SSL Plus Order ID, differs from ssl_plus_order.order_id */
    `acme_certificate_id` VARCHAR(64) NOT NULL,
    `order_id` INT NOT NULL,
    `seat_id` INT NOT NULL,
    `user_hwsig` VARCHAR(256) NOT NULL,
    `csr` MEDIUMTEXT NULL,
    `status_changed_utc` BIGINT NOT NULL,
    `is_issued` BOOLEAN NOT NULL DEFAULT FALSE,
    `issued_cert_fingerprint` VARCHAR(64) NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`placed_order_id`),
    FOREIGN KEY (`order_id`)
            REFERENCES `ssl_plus_order`(`id`)
            ON DELETE cascade
            ON UPDATE cascade,
    FOREIGN KEY (`seat_id`)
            REFERENCES `devid_user`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `keytalk_agent_cert_scraping_settings`;
CREATE TABLE `keytalk_agent_cert_scraping_settings` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `schedule` INT NOT NULL,
    `cert_purposes` VARCHAR(1024) NOT NULL,
    `cert_store_types` VARCHAR(1024) NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `role` INT NOT NULL,
    `email` VARCHAR(256) NOT NULL DEFAULT '',
    `mobile` VARCHAR(256) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `account_password`;
CREATE TABLE `account_password` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `login_name` VARCHAR(255) NOT NULL,
    `salt` MEDIUMTEXT NOT NULL,
    `password` MEDIUMTEXT NOT NULL,
    `account_id` INT NOT NULL,
    `hash_algo` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`login_name`),
    UNIQUE KEY (`account_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `account_password_policy`;
CREATE TABLE `account_password_policy` (
    `min_total` INT NOT NULL,
    `min_upper` INT NOT NULL,
    `min_lower` INT NOT NULL,
    `min_digits` INT NOT NULL,
    `min_special` INT NOT NULL
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `account_client_cert_match_settings`;
CREATE TABLE `account_client_cert_match_settings` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `cert_field_name` VARCHAR(255) NOT NULL,
    `cert_field_value` VARCHAR(1024) NOT NULL,
    `account_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`cert_field_name`, `account_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `operator_assigned_service`;
CREATE TABLE `operator_assigned_service` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `operator_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `operator_service_id` (`operator_id`, `service_id`),
    FOREIGN KEY (`operator_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `operator_assigned_service_group`;
CREATE TABLE `operator_assigned_service_group` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `operator_id` INT NOT NULL,
    `group_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `operator_group_id` (`operator_id`, `group_id`),
    FOREIGN KEY (`operator_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`group_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `power_operator_assigned_service`;
CREATE TABLE `power_operator_assigned_service` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `power_operator_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `power_operator_service_id` (`power_operator_id`, `service_id`),
    FOREIGN KEY (`power_operator_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `power_operator_assigned_service_group`;
CREATE TABLE `power_operator_assigned_service_group` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `power_operator_id` INT NOT NULL,
    `group_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `power_operator_group_id` (`power_operator_id`, `group_id`),
    FOREIGN KEY (`power_operator_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`group_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `operator_certkey_download_ticket`;
CREATE TABLE `operator_certkey_download_ticket` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `operator_id` INT NOT NULL,
    `devid_user_id` INT NOT NULL,
    `ticket_status` INT NOT NULL,
    `ticket_expires_utc` BIGINT NULL,
    `approvers_last_notified_utc` BIGINT NULL,
    `approvers_notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`operator_id`, `devid_user_id`),
    FOREIGN KEY (`operator_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`devid_user_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `operator_certkey_modification_ticket`;
CREATE TABLE `operator_certkey_modification_ticket` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `operator_id` INT NOT NULL,
    `devid_user_id` INT NOT NULL,
    `ticket_status` INT NOT NULL,
    `ticket_expires_utc` BIGINT NULL,
    `approvers_last_notified_utc` BIGINT NULL,
    `approvers_notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`operator_id`, `devid_user_id`),
    FOREIGN KEY (`operator_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`devid_user_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `keytalk_agent_cert_request_ticket`;
CREATE TABLE `keytalk_agent_cert_request_ticket` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `devid_user_id` INT NOT NULL,
    `ticket_status` INT NOT NULL,
    `ticket_expires_utc` BIGINT NULL,
    `approvers_last_notified_utc` BIGINT NULL,
    `approvers_notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`devid_user_id`),
    FOREIGN KEY (`devid_user_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `manager_assigned_service`;
CREATE TABLE `manager_assigned_service` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `manager_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `manager_service_id` (`manager_id`, `service_id`),
    FOREIGN KEY (`manager_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `manager_assigned_service_group`;
CREATE TABLE `manager_assigned_service_group` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `manager_id` INT NOT NULL,
    `group_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `manager_group_id` (`manager_id`, `group_id`),
    FOREIGN KEY (`manager_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`group_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `ssl_discovery_manager_assigned_service`;
CREATE TABLE `ssl_discovery_manager_assigned_service` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `manager_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `manager_service_id` (`manager_id`, `service_id`),
    FOREIGN KEY (`manager_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `ssl_discovery_manager_assigned_service_group`;
CREATE TABLE `ssl_discovery_manager_assigned_service_group` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `manager_id` INT NOT NULL,
    `group_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `manager_group_id` (`manager_id`, `group_id`),
    FOREIGN KEY (`manager_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`group_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `certkey_owner_assigned_service`;
CREATE TABLE `certkey_owner_assigned_service` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `certkey_owner_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`certkey_owner_id`, `service_id`),
    FOREIGN KEY (`certkey_owner_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `certkey_owner_assigned_service_group`;
CREATE TABLE `certkey_owner_assigned_service_group` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `certkey_owner_id` INT NOT NULL,
    `group_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`certkey_owner_id`, `group_id`),
    FOREIGN KEY (`certkey_owner_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`group_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `certkey_owner_name`;
CREATE TABLE `certkey_owner_name` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `certkey_owner_id` INT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`name`),
    UNIQUE KEY (`certkey_owner_id`),
    FOREIGN KEY (`certkey_owner_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `certs_report_field_name`;
CREATE TABLE `certs_report_field_name` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `keyname_field_name` VARCHAR(256) NOT NULL,
    `significance_value_field_name` VARCHAR(256) NOT NULL,
    `expiry_utc_field_name` VARCHAR(256) NOT NULL,
    `warning_threshold_field_name` VARCHAR(256) NOT NULL,
    `key_function_type_and_length_field_name` VARCHAR(256) NOT NULL,
    `key_generation_and_storage_location_field_name` VARCHAR(256) NOT NULL,
    `purpose_field_name` VARCHAR(256) NOT NULL,
    `workflow_reference_field_name` VARCHAR(256) NOT NULL,
    `exportable_field_name` VARCHAR(256) NOT NULL,
    `owner_field_name` VARCHAR(256) NOT NULL,
    `actual_certkey_info_field_name` VARCHAR(256) NOT NULL,
    `actual_cert_status_field_name` VARCHAR(256) NOT NULL,
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `public_trusted_ssl_provisioning`;
CREATE TABLE `public_trusted_ssl_provisioning` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `devid_username` VARCHAR(255) NOT NULL, /* 255 utf-8 codepoints is max for InnoDB keys */
    `last_enrollment_error` VARCHAR(1024) NOT NULL DEFAULT "",
    `last_installation_error` VARCHAR(1024) NOT NULL DEFAULT "",
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`, `devid_username`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `keytalk_certgen_settings`;
CREATE TABLE `keytalk_certgen_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `signing_ca_index` INT NOT NULL,
    `auto_select_csr_signer` BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_gcc_certgen_settings`;
CREATE TABLE `globalsign_gcc_certgen_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `profile_type` INT NOT NULL,
    `product` INT NOT NULL,
    `username` VARCHAR(128) NOT NULL,
    `password` VARCHAR(128) NOT NULL,
    `profile_id` VARCHAR(128) NOT NULL,
    `domain_id` VARCHAR(128) NOT NULL,
    `contact_first_name` VARCHAR(128) NOT NULL,
    `contact_last_name` VARCHAR(128) NOT NULL,
    `contact_phone` VARCHAR(128) NOT NULL,
    `contact_email` VARCHAR(128) NOT NULL,
    `coupon` VARCHAR(128) NOT NULL DEFAULT "",
    `campaign` VARCHAR(128) NOT NULL DEFAULT "",
    `cert_validity_duration` BIGINT NOT NULL,
    `is_private_intranetssl` BOOLEAN NOT NULL DEFAULT FALSE,
    `sid` VARCHAR(128) NOT NULL DEFAULT "",
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_atlas_account`;
CREATE TABLE `globalsign_atlas_account`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `api_key` VARCHAR(256) NOT NULL,
    `api_secret` VARCHAR(256) NOT NULL,
    `api_signing_certkey` MEDIUMTEXT,
    `admin_email` VARCHAR(128) NOT NULL,
    UNIQUE KEY (`name`),
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_atlas_domain_claim`;
CREATE TABLE `globalsign_atlas_domain_claim`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `claim_id` VARCHAR(255) NOT NULL,
    `token` VARCHAR(1024) NOT NULL DEFAULT "",
    `domain_hint` VARCHAR(1024) NOT NULL DEFAULT "", /* domain is handy for troubleshooting */
    `expiring_claim_last_notified_utc` BIGINT NULL,
    `expiring_token_last_notified_utc` BIGINT NULL,
    `expired_claim_last_notified_utc` BIGINT NULL,
    `expired_token_last_notified_utc` BIGINT NULL,
    `expiring_claim_notifying_started_utc` BIGINT NULL,
    `expiring_token_notifying_started_utc` BIGINT NULL,
    `expired_claim_notifying_started_utc` BIGINT NULL,
    `expired_token_notifying_started_utc` BIGINT NULL,
    `globalsign_atlas_account_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`claim_id`),
    FOREIGN KEY (`globalsign_atlas_account_id`)
        REFERENCES `globalsign_atlas_account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_atlas_certgen_settings`;
CREATE TABLE `globalsign_atlas_certgen_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `globalsign_atlas_account_id` INT NOT NULL,
    `product` INT NOT NULL,
    `cert_validity_duration` BIGINT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`globalsign_atlas_account_id`)
        REFERENCES `globalsign_atlas_account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `digicert_pki_platform_certgen_settings`;
CREATE TABLE `digicert_pki_platform_certgen_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `product` INT NOT NULL,
    `profile_type` INT NOT NULL,
    `profile_id` VARCHAR(128) NOT NULL,
    `api_key` VARCHAR(256) NOT NULL,
    `cert_validity_months` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `digicert_central_certgen_settings`;
CREATE TABLE `digicert_central_certgen_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `account_region` INT NOT NULL,
    `product` INT NOT NULL,
    `api_key` VARCHAR(256) NOT NULL,
    `organization_id` INT NULL,
    `cert_validity_months` INT NOT NULL,
    /* if EV approver id is known then approver contact info is optional, otherwise it is required */
    `ev_approver_id` INT NULL,
    `ev_approver_first_name` VARCHAR(128) NULL,
    `ev_approver_last_name` VARCHAR(128) NULL,
    `ev_approver_job_title` VARCHAR(128) NULL,
    `ev_approver_phone` VARCHAR(64) NULL,
    `ev_approver_email` VARCHAR(128) NULL,
    `dv_approver_mail_fetch_proto` INT NULL,
    `dv_approver_azure_client_id` VARCHAR(256) NULL,
    `dv_approver_azure_client_secret` VARCHAR(256) NULL,
    `dv_approver_azure_tenant_id` VARCHAR(256) NULL,
    `dv_approver_email` VARCHAR(256) NULL,
    `dv_approver_generic_imap_host` VARCHAR(256) NULL,
    `dv_approver_generic_imap_port` INT NULL,
    `dv_approver_generic_imap_password` VARCHAR(256) NULL,
    `division_id` INT NULL,
    `primary_smime_cert_purpose` INT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `digicert_central_expiring_domain_notification`;
CREATE TABLE `digicert_central_expiring_domain_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `domain` VARCHAR(256) NOT NULL,
    `validation_type` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_account_domain_validation_type` (`account_id`,`domain`,`validation_type`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `digicert_central_expired_domain_notification`;
CREATE TABLE `digicert_central_expired_domain_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `domain` VARCHAR(256) NOT NULL,
    `validation_type` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_account_domain_validation_type` (`account_id`,`domain`,`validation_type`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_gcc_expiring_domain_notification`;
CREATE TABLE `globalsign_gcc_expiring_domain_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `domain` VARCHAR(256) NOT NULL,
    `product` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_account_domain_product` (`account_id`,`domain`,`product`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_gcc_expired_domain_notification`;
CREATE TABLE `globalsign_gcc_expired_domain_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `domain` VARCHAR(256) NOT NULL,
    `product` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_account_domain_product` (`account_id`,`domain`,`product`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `external_devid_users_smime_cert_enrollment_settings`;
CREATE TABLE `external_devid_users_smime_cert_enrollment_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `requestor_service_id` INT NOT NULL,
    `recipient_service_id` INT NOT NULL,
    `force_sms` BOOLEAN NOT NULL DEFAULT FALSE,
    `requestor_success_email_notification_templ_subj` VARCHAR(256) NOT NULL,
    `requestor_success_email_notification_templ_body` MEDIUMTEXT NOT NULL,
    `requestor_success_email_notification_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `requestor_cert_picked_up_email_notification_templ_subj` VARCHAR(256) NOT NULL,
    `requestor_cert_picked_up_email_notification_templ_body` MEDIUMTEXT NOT NULL,
    `requestor_cert_picked_up_email_notification_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `requestor_cert_invite_pending_email_notification_templ_subj` VARCHAR(256) NOT NULL,
    `requestor_cert_invite_pending_email_notification_templ_body` MEDIUMTEXT NOT NULL,
    `requestor_cert_invite_pending_email_notification_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `requestor_cert_invite_expired_email_notification_templ_subj` VARCHAR(256) NOT NULL,
    `requestor_cert_invite_expired_email_notification_templ_body` MEDIUMTEXT NOT NULL,
    `requestor_cert_invite_expired_email_notification_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `requestor_cert_invite_declined_email_notification_templ_subj` VARCHAR(256) NOT NULL,
    `requestor_cert_invite_declined_email_notification_templ_body` MEDIUMTEXT NOT NULL,
    `requestor_cert_invite_declined_email_notification_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `requestor_error_email_notification_templ_subj` VARCHAR(256) NOT NULL,
    `requestor_error_email_notification_templ_body` MEDIUMTEXT NOT NULL,
    `requestor_error_email_notification_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `requestor_cert_order_submitted_templ_subj` VARCHAR(256) NOT NULL,
    `requestor_cert_order_submitted_templ_body` MEDIUMTEXT NOT NULL,
    `requestor_cert_order_submitted_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `recipient_cert_order_submitted_templ_subj` VARCHAR(256) NOT NULL,
    `recipient_cert_order_submitted_templ_body` MEDIUMTEXT NOT NULL,
    `recipient_cert_order_submitted_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `recipient_success_email_notification_templ_subj` VARCHAR(256) NOT NULL,
    `recipient_success_email_notification_templ_body` MEDIUMTEXT NOT NULL,
    `recipient_success_email_notification_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `recipient_cert_ready_for_pickup_email_notification_templ_subj` VARCHAR(256) NOT NULL,
    `recipient_cert_ready_for_pickup_email_notification_templ_body` MEDIUMTEXT NOT NULL,
    `recipient_cert_ready_for_pickup_email_notification_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    `recipient_cert_invite_pending_email_notification_templ_subj` VARCHAR(256) NOT NULL,
    `recipient_cert_invite_pending_email_notification_templ_body` MEDIUMTEXT NOT NULL,
    `recipient_cert_invite_pending_email_notification_templ_bodyfmt` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`requestor_service_id`),
    FOREIGN KEY (`requestor_service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`recipient_service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `devid_service_settings`;
CREATE TABLE `devid_service_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `learn_mode` SMALLINT NOT NULL,
    `def_user_slot_learn_modes` JSON NULL,
    `user_enrollment_start_utc` BIGINT NULL,/* Not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `user_enrollment_end_utc` BIGINT NULL,/* Not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `slots_timer_when_enrollment_expires` BIGINT NULL,
    `new_user_learn_once_slots_timer` BIGINT NULL,
    `existing_user_learn_once_slots_timer` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_required_cred_type`;
CREATE TABLE `service_required_cred_type` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `cred_type` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`,`cred_type`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_default_cert_field`;
CREATE TABLE `service_default_cert_field` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `cert_c` CHAR(2) NOT NULL,
    `cert_st` VARCHAR(128) NOT NULL,
    `cert_l` VARCHAR(128) NOT NULL,
    `cert_o` VARCHAR(128) NOT NULL,
    `cert_ous` VARCHAR(1024) NOT NULL,
    `cert_e` VARCHAR(128) NOT NULL,
    `cert_ttl` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_cert_extension`;
CREATE TABLE `service_cert_extension` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `cert_extension_name` VARCHAR(128) NOT NULL,
    `cert_extension_value` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`, `cert_extension_name`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `enabled_rest_api`;
CREATE TABLE `enabled_rest_api` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `api_type` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`,`api_type`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `messages_and_workflow`;
CREATE TABLE `messages_and_workflow` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `notify_on_successful_cert_issuance` BOOLEAN NOT NULL DEFAULT FALSE,
    `notify_on_failed_cert_issuance` BOOLEAN NOT NULL DEFAULT FALSE,
    `notify_on_cert_expiry` BOOLEAN NOT NULL DEFAULT FALSE,
    `notify_on_successful_crl_publish` BOOLEAN NOT NULL DEFAULT FALSE,
    `notify_on_failed_crl_publish` BOOLEAN NOT NULL DEFAULT FALSE,
    `notify_on_seat_smime_cert_expiry` BOOLEAN NOT NULL DEFAULT FALSE,
    `certkeymeta_warning_threshold_days` INT NOT NULL DEFAULT 7,
    `seat_quota` INT NULL,
    `seat_quota_expiry_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `mod_mysql`;
CREATE TABLE `mod_mysql` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `user_lockout_allowed` BOOLEAN NOT NULL DEFAULT TRUE,
    `zero_hwsig_allowed` BOOLEAN NOT NULL DEFAULT FALSE,
    `verify_hwsig` BOOLEAN NOT NULL DEFAULT FALSE,
    `db_host` VARCHAR(256) NOT NULL DEFAULT 'mysql.keytalkdemo.com',
    `db_port` INT NOT NULL DEFAULT 3306,
    `db_name` VARCHAR(256) NOT NULL DEFAULT 'keytalk_users',
    `db_user`VARCHAR(256) NOT NULL DEFAULT  'keytalk-admin',
    `db_password`VARCHAR(256) NOT NULL DEFAULT 'secret',
    `db_verification_cas` MEDIUMTEXT NULL,
    `db_login_cert_key` MEDIUMTEXT NULL,
    `credential_hash_type` INT NOT NULL DEFAULT 2,
    `db_auth_type` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_mysql_user_table_mapping`;
CREATE TABLE `mod_mysql_user_table_mapping` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `mod_mysql_id` INT NOT NULL,
    `mapping_name` VARCHAR(256) NOT NULL,
    `mapping_value` VARCHAR(256) NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`mod_mysql_id`)
        REFERENCES `mod_mysql`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_mysql_cert_attr_mapping`;
CREATE TABLE `mod_mysql_cert_attr_mapping` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `mod_mysql_id` INT NOT NULL,
    `attr_name` VARCHAR(128) NOT NULL,
    `attr_value` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`mod_mysql_id`)
        REFERENCES `mod_mysql`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_internal`;
CREATE TABLE `mod_internal` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `user_lockout_allowed` BOOLEAN NOT NULL DEFAULT TRUE,
    `zero_hwsig_allowed` BOOLEAN NOT NULL DEFAULT FALSE,
    `auto_register_new_users` BOOLEAN NOT NULL DEFAULT TRUE,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_internal_user`;
CREATE TABLE `mod_internal_user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mod_internal_id` INT NOT NULL,
  /* 255 utf-8 codepoints is max for InnoDB keys */
  `USERID` VARCHAR(255) NOT NULL,
  `PASSWD` VARCHAR(256) NOT NULL,
  `PIN` VARCHAR(256) NOT NULL,
  `PASSWD_SALT` VARCHAR(256) NOT NULL,
  `PIN_SALT` VARCHAR(256) NOT NULL,
  `password_expiry_utc` BIGINT NULL,
  PRIMARY KEY (`id`),
  UNIQUE (`USERID`,`mod_internal_id`),
  FOREIGN KEY (`mod_internal_id`)
      REFERENCES `mod_internal`(`id`)
      ON DELETE cascade
      ON UPDATE cascade )
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_internal_user_cert_attr`;
CREATE TABLE `mod_internal_user_cert_attr` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `mod_internal_user_id` INT NOT NULL,
    `attr_name` VARCHAR(128) NOT NULL,
    `attr_value` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`mod_internal_user_id`, `attr_name`),
    FOREIGN KEY (`mod_internal_user_id`)
        REFERENCES `mod_internal_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_internal_post_overwrite_rule`;
CREATE TABLE `mod_internal_post_overwrite_rule` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mod_internal_id` INT NOT NULL,
  `rule` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`mod_internal_id`),
  FOREIGN KEY (`mod_internal_id`)
      REFERENCES `mod_internal`(`id`)
      ON DELETE cascade
      ON UPDATE cascade )
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_ldap`;
CREATE TABLE `mod_ldap` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `user_lockout_allowed` BOOLEAN NOT NULL DEFAULT TRUE,
    `zero_hwsig_allowed` BOOLEAN NOT NULL DEFAULT FALSE,
    `cert_attr_mappings_filter` VARCHAR(256) NOT NULL DEFAULT '(sAMAccountName=$(userid))',
    `user_lookup_mode` INT NOT NULL DEFAULT 0,
    `user_custom_filter` VARCHAR(256) NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_ldap_kerberos_settings`;
CREATE TABLE `mod_ldap_kerberos_settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mod_ldap_id` INT NOT NULL,
  `realm` VARCHAR(256) NOT NULL,
  `kdc` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`mod_ldap_id`),
  FOREIGN KEY (`mod_ldap_id`)
      REFERENCES `mod_ldap`(`id`)
      ON DELETE cascade
      ON UPDATE cascade )
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_ldap_userid_overwrite_settings`;
CREATE TABLE `mod_ldap_userid_overwrite_settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mod_ldap_id` INT NOT NULL,
  `source_suffix` VARCHAR(256) NOT NULL,
  `target_suffix` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`mod_ldap_id`),
  FOREIGN KEY (`mod_ldap_id`)
      REFERENCES `mod_ldap`(`id`)
      ON DELETE cascade
      ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_ldap_match_setting`;
CREATE TABLE `mod_ldap_match_setting` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mod_ldap_id` INT NOT NULL,
  `attr_name` VARCHAR(256) NOT NULL,
  `attr_value` VARCHAR(256) NOT NULL,
  `match_mode` VARCHAR(64) NOT NULL,
  `filter` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`mod_ldap_id`)
      REFERENCES `mod_ldap`(`id`)
      ON DELETE cascade
      ON UPDATE cascade )
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_ldap_server`;
CREATE TABLE `mod_ldap_server` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mod_ldap_id` INT NOT NULL,
  `server_index` INT NOT NULL, /* servers for the given module are tried in the order of their index */
  `url` VARCHAR(1024) NOT NULL DEFAULT 'ldap://ldap.keytalkdemo.com:389',
  `bind_dn` VARCHAR(256) NOT NULL DEFAULT '$(userid)@keytalk.com',
  `bind_password` VARCHAR(256) NOT NULL DEFAULT '$(password)',
  `base_dn` VARCHAR(256) NOT NULL DEFAULT 'ou=people,dc=keytalk,dc=com',
  `service_user` VARCHAR(256) NOT NULL DEFAULT '',
  `service_password` VARCHAR(256) NOT NULL DEFAULT '',
  `allow_empty_password` BOOLEAN NOT NULL DEFAULT FALSE,
  `verification_cas` MEDIUMTEXT NULL,
  `is_ad` BOOLEAN NOT NULL DEFAULT FALSE,
  `address_book_only` BOOLEAN NOT NULL DEFAULT FALSE,
  `address_book_dn_template` VARCHAR(256) NOT NULL DEFAULT '',
  `otp_auth` BOOLEAN NOT NULL DEFAULT FALSE,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`mod_ldap_id`,`server_index`),
  FOREIGN KEY (`mod_ldap_id`)
      REFERENCES `mod_ldap`(`id`)
      ON DELETE cascade
      ON UPDATE cascade )
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_ldap_cert_attr_mapping`;
CREATE TABLE `mod_ldap_cert_attr_mapping` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `mod_ldap_id` INT NOT NULL,
    `attr_name` VARCHAR(128) NOT NULL,
    `attr_value` MEDIUMTEXT NOT NULL,
    `fallback_value` VARCHAR(256) NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`mod_ldap_id`)
        REFERENCES `mod_ldap`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_ldap_post_overwrite_rule`;
CREATE TABLE `mod_ldap_post_overwrite_rule` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mod_ldap_id` INT NOT NULL,
  `rule` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`mod_ldap_id`),
  FOREIGN KEY (`mod_ldap_id`)
      REFERENCES `mod_ldap`(`id`)
      ON DELETE cascade
      ON UPDATE cascade )
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_azure`;
CREATE TABLE `mod_azure` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `user_lockout_allowed` BOOLEAN NOT NULL DEFAULT TRUE,
    `zero_hwsig_allowed` BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_azure_server`;
CREATE TABLE `mod_azure_server` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `mod_azure_id` INT NOT NULL,
    `url` VARCHAR(256) NOT NULL,
    `tenant_id` VARCHAR(256) NOT NULL,
    `client_id` VARCHAR(256) NOT NULL,
    `client_secret` VARCHAR(256) NOT NULL,
    `tenant_domain` VARCHAR(256) NOT NULL,
    `ms_graph_url` VARCHAR(256) NOT NULL DEFAULT 'https://graph.microsoft.com/v1.0',
    PRIMARY KEY (`id`),
    FOREIGN KEY (`mod_azure_id`)
        REFERENCES `mod_azure`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_azure_post_overwrite_rule`;
CREATE TABLE `mod_azure_post_overwrite_rule` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mod_azure_id` INT NOT NULL,
  `rule` TEXT NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`mod_azure_id`),
  FOREIGN KEY (`mod_azure_id`)
      REFERENCES `mod_azure`(`id`)
      ON DELETE cascade
      ON UPDATE cascade )
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_azure_mfa_settings`;
CREATE TABLE `mod_azure_mfa_settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mod_azure_id` INT NOT NULL,
  `mfa_kind` INT NOT NULL,	-- 0: No MFA, 1: MFA using Popups, 2: MFA using Redirections
  `authority` VARCHAR(512) NOT NULL,
  `scopes` VARCHAR(512) NOT NULL,
  `redirect_uri` VARCHAR(1024) NOT NULL,
  `post_logout_uri` VARCHAR(1024) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`mod_azure_id`),
  FOREIGN KEY (`mod_azure_id`)
      REFERENCES `mod_azure`(`id`)
      ON DELETE cascade
      ON UPDATE cascade )
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mod_azure_cert_attr_mapping`;
CREATE TABLE `mod_azure_cert_attr_mapping` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `mod_azure_id` INT NOT NULL,
    `attr_name` VARCHAR(128) NOT NULL,
    `attr_value` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`mod_azure_id`)
        REFERENCES `mod_azure`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `devid_user`;
CREATE TABLE `devid_user` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `username` VARCHAR(255) NOT NULL, /* 255 utf-8 codepoints is max for InnoDB keys */
    `cn` VARCHAR(256) NOT NULL DEFAULT '',
    `gn` VARCHAR(256) NOT NULL DEFAULT '',
    `sn` VARCHAR(256) NOT NULL DEFAULT '',
    `san` VARCHAR(10240) NOT NULL DEFAULT '',
    `key_params` VARCHAR(128) NOT NULL,
    `learn_once_slots_close_utc` BIGINT NULL, /* Not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `emailed_cert_key_passwd` VARCHAR(256) NOT NULL DEFAULT '',
    `mobile_number` VARCHAR(256) NOT NULL DEFAULT '',
    `archived` BOOLEAN NOT NULL DEFAULT FALSE,
    `accept_keytalk_agent_scraped_certs` INT NOT NULL DEFAULT 1, /* Defaults to "once" */
    `last_keytalk_agent_contact_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`,`username`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX `devid_user_service_id_index` on `devid_user`(`service_id`);

DROP TABLE IF EXISTS `seat_last_enrollment_status`;
CREATE TABLE `seat_last_enrollment_status` (
    `seat_id` INT NOT NULL,
    `status` BOOLEAN NOT NULL,
    `last_modified_utc` BIGINT NULL,
    `enrollment_purpose` TEXT NOT NULL,
    `error` TEXT NOT NULL,
    UNIQUE KEY (`seat_id`),
    FOREIGN KEY (`seat_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `devid_slot`;
CREATE TABLE `devid_slot` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `learn_mode` SMALLINT NOT NULL,
    `hwsig` VARCHAR(256) NOT NULL,
    `last_hwsig_updated_utc` BIGINT NULL,/* Not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `last_authenticated_utc` BIGINT NULL, /* Not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `hwdescription` MEDIUMTEXT NULL,
    `comment` MEDIUMTEXT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX `devid_slot_user_id_index` on `devid_slot`(`user_id`);

DROP TABLE IF EXISTS `devid_user_certificate`;
CREATE TABLE `devid_user_certificate` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `user_hwsig` VARCHAR(256) NOT NULL,
    `certs` MEDIUMTEXT NOT NULL, /* certificate, possibly followed by issuer CAs */
    `privkey` MEDIUMTEXT NULL,
    `privkey_local_encryption_type` INT NULL,
    `privkey_local_encryption_bit` INT NULL,
    `csr` MEDIUMTEXT NULL, /* contains NULL for fw before v6.2.10 */
    `cn` VARCHAR(256) NULL, /*  use NULL to easily spot records for which we failed to extract CN during Db schema conversion of Db schema 3.2 -> 4.0 */
    `issued_utc` BIGINT NOT NULL,/* Not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `not_before_utc` BIGINT NOT NULL,/* redundant cert metadata for fast search; not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `not_after_utc` BIGINT NOT NULL,/* redundant cert metadata for fast search; not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `fingerprint` VARCHAR(64) NOT NULL,
    `tpm_attester_ca` MEDIUMTEXT NULL, /* only filled when attested/endorsed by a TPM */
    `revoked` BOOLEAN NOT NULL,
    `revocation_time_utc` BIGINT NULL, /* Use NULL because this should only be filled if revoked is TRUE */
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`)
            REFERENCES `devid_user`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX `devid_user_certificate_user_hwsig_index` on `devid_user_certificate`(`user_hwsig`);
CREATE INDEX `devid_user_certificate_fingerprint_index` on `devid_user_certificate`(`fingerprint`);

/* CSR of the seat. SHOULD NOT be confused with the CSR of seat's certificate (yet, it might turn into it later on) */
DROP TABLE IF EXISTS `devid_user_csr`;
CREATE TABLE `devid_user_csr` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `username` VARCHAR(255) NOT NULL,
    `cn` VARCHAR(256) NOT NULL, /* redundant CSR metadata for fast search */
    `csr_pubkey_fingerprint` VARCHAR(64) NOT NULL, /* redundant CSR metadata to facilitate fast search */
    `csr` MEDIUMTEXT NULL,
    `privkey` MEDIUMTEXT NULL,
    `privkey_local_encryption_type` INT NULL,
    `privkey_local_encryption_bit` INT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`,`username`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX `devid_user_csr_csr_pubkey_fingerprint_index` on `devid_user_csr`(`csr_pubkey_fingerprint`);

DROP TABLE IF EXISTS `devid_user_cvc_certificate`;
CREATE TABLE `devid_user_cvc_certificate` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `chr` VARCHAR(256) NOT NULL, /* redundant cert metadata for fast search */
    `car` VARCHAR(256) NOT NULL, /* redundant cert metadata for fast search */
    `cert` BLOB NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`user_id`)
            REFERENCES `devid_user`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE INDEX `devid_user_cvc_certificate_chr_index` on `devid_user_cvc_certificate`(`chr`);
CREATE INDEX `devid_user_cvc_certificate_car_index` on `devid_user_cvc_certificate`(`car`);

DROP TABLE IF EXISTS `seat_latest_roca_key`;
CREATE TABLE `seat_latest_roca_key` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `seat_id` INT NOT NULL,
    `csr` MEDIUMTEXT NULL,
    `detected_utc` BIGINT NOT NULL,
    `notified_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`seat_id`)
            REFERENCES `devid_user`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

/* redundant CSR metadata for quick search */
DROP TABLE IF EXISTS `devid_user_csr_san_email`;
CREATE TABLE `devid_user_csr_san_email` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `devid_user_csr_id` INT NOT NULL,
    `email` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`devid_user_csr_id`)
        REFERENCES `devid_user_csr`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `devid_user_otp`;
CREATE TABLE `devid_user_otp` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `devid_user_id` INT NOT NULL,
    `salt` MEDIUMTEXT NOT NULL,
    `otp` MEDIUMTEXT NOT NULL,/* sha256(salt + plain OTP) */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`devid_user_id`),
    FOREIGN KEY (`devid_user_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `devid_user_certkey_meta`;
CREATE TABLE `devid_user_certkey_meta` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `user_id` INT NOT NULL,
    `significance_value` INT NOT NULL,
    `expiry_utc` BIGINT NULL,/* not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `warning_threshold_days` INT NOT NULL,
    `key_function_type_and_length` VARCHAR(256) NOT NULL,
    `key_generation_and_storage_location` VARCHAR(256) NOT NULL,
    `purpose` VARCHAR(256) NOT NULL,
    `exportable` BOOLEAN NOT NULL,
    `owner_name` VARCHAR(256) NOT NULL,
    `normalized_owner_name` VARCHAR(256) NOT NULL, /* derived from the 'owner_name' column; used for fast searching */
    `workflow_reference` VARCHAR(256) NOT NULL DEFAULT '',
    `owner_notified_on_expiration_utc` BIGINT NULL,
    `notifying_owner_on_expiration_started_utc` BIGINT NULL,
    `owner_notified_on_revocation_utc` BIGINT NULL,
    `notifying_owner_on_revocation_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`user_id`),
    FOREIGN KEY (`user_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `certkey_meta_delegated_admin_notified_on_expiration`;
CREATE TABLE `certkey_meta_delegated_admin_notified_on_expiration` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `certkey_id` INT NOT NULL,
    `admin_id` INT NOT NULL,
    `notified_utc` BIGINT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`certkey_id`,`admin_id`),
    FOREIGN KEY (`certkey_id`)
        REFERENCES `devid_user_certkey_meta`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`admin_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `certkey_meta_notifying_delegated_admin_on_expiration`;
CREATE TABLE `certkey_meta_notifying_delegated_admin_on_expiration` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `certkey_id` INT NOT NULL,
    `admin_id` INT NOT NULL,
    `notifying_started_utc` BIGINT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`certkey_id`,`admin_id`),
    FOREIGN KEY (`certkey_id`)
        REFERENCES `devid_user_certkey_meta`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`admin_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_atlas_certificate_order`;
CREATE TABLE `globalsign_atlas_certificate_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `devid_user_certificate_id` INT NOT NULL,
    `order_id` VARCHAR(64) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`devid_user_certificate_id`),
    UNIQUE KEY (`order_id`),
    FOREIGN KEY (`devid_user_certificate_id`)
            REFERENCES `devid_user_certificate`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_atlas_expiring_signing_api_key_notification`;
CREATE TABLE `globalsign_atlas_expiring_signing_api_key_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `globalsign_atlas_account_id` INT NOT NULL,
    `cert_fingerprint` VARCHAR(64) NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`globalsign_atlas_account_id`)
        REFERENCES `globalsign_atlas_account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_atlas_expired_signing_api_key_notification`;
CREATE TABLE `globalsign_atlas_expired_signing_api_key_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `globalsign_atlas_account_id` INT NOT NULL,
    `cert_fingerprint` VARCHAR(64) NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`globalsign_atlas_account_id`)
        REFERENCES `globalsign_atlas_account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `external_devid_user_smime_certificate`;
CREATE TABLE `external_devid_user_smime_certificate` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `devid_user_certificate_id` INT NOT NULL,
    `download_id` VARCHAR(128) NOT NULL,
    `download_password` VARCHAR(128) NOT NULL,
    `times_downloaded` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`devid_user_certificate_id`),
    UNIQUE KEY (`download_id`),
    FOREIGN KEY (`devid_user_certificate_id`)
            REFERENCES `devid_user_certificate`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_gcc_certificate_order`;
CREATE TABLE `globalsign_gcc_certificate_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(64) NOT NULL, /* not unique since reissued certificates might belong to the same order */
    `product` INT NULL, /* use NULL to easily spot records for which we failed to extract product type during conversion of Db schema 3.2 -> 4.0 */
    `fingerprint` VARCHAR(64) NOT NULL,
    `service_id` INT NOT NULL,
    `renewed_from_order_id` VARCHAR(64) NULL, /* id of the order we are renewed from */
    `renewal_order_id` VARCHAR(64) NULL, /* id of the order renewed from us */
    `reissue_order_id` VARCHAR(64) NULL, /* id of the order reissued from us */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`fingerprint`),
    FOREIGN KEY (`service_id`)
            REFERENCES `service`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_gcc_placed_ps1_certificate_order`;
CREATE TABLE `globalsign_gcc_placed_ps1_certificate_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(64) NOT NULL,
    `product` INT NOT NULL,
    `status` INT NOT NULL,
    `user_email` VARCHAR(255) NOT NULL,
    `user_mobile` VARCHAR(255) NOT NULL,
    `user_service_id` INT NOT NULL,
    `pickup_password` VARCHAR(256) NOT NULL,
    `requestor_email` VARCHAR(256) NOT NULL,
    `requestor_service_id` INT NOT NULL,
    `requestor_pending_order_last_notified_utc` BIGINT NULL,
    `recipient_pending_order_last_notified_utc` BIGINT NULL,
    `requestor_pending_order_notifying_started_utc` BIGINT NULL,
    `recipient_pending_order_notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`order_id`),
    FOREIGN KEY (`user_service_id`)
            REFERENCES `service`(`id`)
            ON DELETE cascade
            ON UPDATE cascade,
    FOREIGN KEY (`requestor_service_id`)
             REFERENCES `service`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_gcc_placed_ssl_certificate_order`;
CREATE TABLE `globalsign_gcc_placed_ssl_certificate_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(64) NOT NULL,
    `product` INT NOT NULL,
    `devid_user_id` INT NOT NULL,
    `user_hwsig` VARCHAR(256) NOT NULL,
    `status` INT NOT NULL,
    `approver_email` VARCHAR(255) NOT NULL,
    `csr` MEDIUMTEXT NULL,
    `privkey` MEDIUMTEXT NULL,
    `privkey_local_encryption_type` INT NULL,
    `privkey_local_encryption_bit` INT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`order_id`,`product`),
    FOREIGN KEY (`devid_user_id`)
            REFERENCES `devid_user`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `globalsign_gcc_placed_mssl_certificate_reissuance_order`;
CREATE TABLE `globalsign_gcc_placed_mssl_certificate_reissuance_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(64) NOT NULL,
    `product` INT NOT NULL,
    `devid_user_id` INT NOT NULL,
    `user_hwsig` VARCHAR(256) NOT NULL,
    `status` INT NOT NULL,
    `csr` MEDIUMTEXT NULL,
    `privkey` MEDIUMTEXT NULL,
    `privkey_local_encryption_type` INT NULL,
    `privkey_local_encryption_bit` INT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`order_id`),
    FOREIGN KEY (`devid_user_id`)
            REFERENCES `devid_user`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `digicert_pki_platform_order`;
CREATE TABLE `digicert_pki_platform_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(128) NOT NULL, /* normally cert s/n */
    `seat_id` VARCHAR(512) NOT NULL,
    `product` INT NOT NULL,
    `profile_type` INT NOT NULL,
    `profile_id` VARCHAR(128) NOT NULL,
    `requested_cert_validity_months` INT NOT NULL,
    `requested_cn` VARCHAR(64) NOT NULL, /* CN of the requested cert might differ from the CN of the resulted cert */
    `requested_san` VARCHAR(10240) NOT NULL, /* SAN of the requested cert might differ from the SAN of the resulted cert */
    `cert_fingerprint` VARCHAR(64) NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`order_id`),
    UNIQUE KEY (`cert_fingerprint`),
    FOREIGN KEY (`service_id`)
            REFERENCES `service`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `digicert_central_order`;
CREATE TABLE `digicert_central_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `order_id` INT NOT NULL, /* not unique because re-issued cert orders keep the same order IDs */
    `product` INT NOT NULL,
    `organization_id` INT NULL,
    `requested_cert_validity_months` INT NOT NULL,
    `requested_cn` VARCHAR(64) NOT NULL, /* CN of the requested cert might differ from the CN of the resulted cert */
    `requested_san` VARCHAR(10240) NOT NULL, /* SAN of the requested cert might differ from the SAN of the resulted cert */
    `cert_fingerprint` VARCHAR(64) NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`cert_fingerprint`),
    FOREIGN KEY (`service_id`)
            REFERENCES `service`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `digicert_central_placed_dv_certificate_order`;
CREATE TABLE `digicert_central_placed_dv_certificate_order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `order_id` INT NOT NULL, /* DigiCert-specific order ID */
    `certificate_id` INT NOT NULL, /* DigiCert-specific certificate ID */
    `acme_certificate_id` VARCHAR(64) NOT NULL,
    `keytalk_order_id` VARCHAR(64) NULL, /* Order ID assigned by KeyTalk  */
    `product` INT NOT NULL,
    `seat_id` INT NOT NULL,
    `user_hwsig` VARCHAR(256) NOT NULL,
    `status` VARCHAR(32) NOT NULL,
    `status_changed_utc` BIGINT NOT NULL,
    `approver_email` VARCHAR(255) NOT NULL,
    `csr` MEDIUMTEXT NULL,
    `privkey` MEDIUMTEXT NULL,
    `privkey_local_encryption_type` INT NULL,
    `privkey_local_encryption_bit` INT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`order_id`,`product`),
    UNIQUE KEY (`keytalk_order_id`),
    FOREIGN KEY (`seat_id`)
            REFERENCES `devid_user`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `digicert_central_placed_dv_certificate_order_domain`;
CREATE TABLE `digicert_central_placed_dv_certificate_order_domain` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `order_record_id` INT NOT NULL,
    `domain_name` VARCHAR(255) NOT NULL,
    `approved` BOOLEAN NOT NULL,
    `approved_utc` BIGINT NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`order_record_id`)
            REFERENCES `digicert_central_placed_dv_certificate_order`(`id`)
            ON DELETE cascade
            ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `address_book`;
CREATE TABLE `address_book` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `service_id` INT NOT NULL,
   `ldap_svr_url` VARCHAR(1024) NOT NULL,
   `search_base` VARCHAR(256) NOT NULL,
   `verification_cas` MEDIUMTEXT NULL,
   PRIMARY KEY (`id`),
   FOREIGN KEY (`service_id`)
       REFERENCES `service`(`id`)
       ON DELETE cascade
       ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_cdp_settings`;
CREATE TABLE `service_cdp_settings` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `service_id` INT NOT NULL,
   `file_protocol` INT NOT NULL,
   `hostname` VARCHAR(256) NOT NULL,
   `host_port` INT NULL,
   `cred_username` VARCHAR(256) NOT NULL,
   `remote_path` VARCHAR(256) NOT NULL,
   `remote_filename` VARCHAR(256) NOT NULL,
   `crl_validity_minutes` INT NOT NULL DEFAULT 10080, /* 1 week */
   PRIMARY KEY (`id`),
   UNIQUE KEY (`service_id`),
   FOREIGN KEY (`service_id`)
       REFERENCES `service`(`id`)
       ON DELETE cascade
       ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `private_ca_cdp_settings`;
CREATE TABLE `private_ca_cdp_settings` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `cert_type` INT NOT NULL,
   `extra_cert_id` INT NOT NULL, /* not NULL-able to allow for a unique compound key (`cert_type`,`extra_cert_id`)  */
   `file_protocol` INT NOT NULL,
   `hostname` VARCHAR(256) NOT NULL,
   `host_port` INT NULL,
   `cred_username` VARCHAR(256) NOT NULL,
   `remote_path` VARCHAR(256) NOT NULL,
   `remote_filename` VARCHAR(256) NOT NULL,
   `crl_validity_minutes` INT NOT NULL DEFAULT 10080, /* 1 week */
   PRIMARY KEY (`id`),
   UNIQUE KEY (`cert_type`,`extra_cert_id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `private_ca_revoked_certificate`;
CREATE TABLE `private_ca_revoked_certificate` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `certificate` MEDIUMTEXT NOT NULL,
   `fingerprint` VARCHAR(64) NOT NULL, /*redundant cert metadata for quick search*/
   `revocation_utc` BIGINT NOT NULL,
   `cdp_last_publish_utc` BIGINT NOT NULL,
   `cdp_next_publish_utc` BIGINT NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY (`fingerprint`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `private_ca_revoked_certificate_type`;
CREATE TABLE `private_ca_revoked_certificate_type` (
   `id` INT NOT NULL AUTO_INCREMENT,
   `cert_type` INT NOT NULL,
   `extra_cert_id` INT NOT NULL, /* not NULL-able to allow for a unique compound key (`cert_type`,`extra_cert_id`, `revoked_certificate_id`) */
   `revoked_certificate_id` INT NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY (`cert_type`,`extra_cert_id`, `revoked_certificate_id`),
   FOREIGN KEY (`revoked_certificate_id`)
       REFERENCES `private_ca_revoked_certificate`(`id`)
       ON DELETE cascade
       ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `private_issuer_ca`;
CREATE TABLE `private_issuer_ca` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `cert` MEDIUMTEXT NULL,
    `fingerprint` VARCHAR(64) NOT NULL,/* redundant cert metadata for quick search */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`fingerprint`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `crl`;
CREATE TABLE `crl` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `data` LONGTEXT NOT NULL,
    `certs` LONGTEXT NOT NULL, /*list of revoked certificates containing the entire certs, not just serial and revocation date*/
    `cert_issuer_ca` MEDIUMTEXT NOT NULL,
    `cert_issuer_ca_fingerprint` VARCHAR(64) NOT NULL,/* redundant issuer CA metadata for quick search */
    `fingerprint` VARCHAR(64) NULL,/* redundant CRL metadata for quick search, NULL when CRL is not signed */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`cert_issuer_ca_fingerprint`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `mobile_iron_settings`;
CREATE TABLE `mobile_iron_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `url` VARCHAR(256) NOT NULL DEFAULT '',
    `admin_username` VARCHAR(256) NOT NULL DEFAULT '',
    `admin_password` VARCHAR(256) NOT NULL DEFAULT '',
    `ce_id` INT NOT NULL DEFAULT 0,
    `fetch_upn` BOOLEAN NOT NULL DEFAULT FALSE,
    `fetch_upn_binding` VARCHAR(256) NOT NULL DEFAULT '',
    `pfx_key_encryption_algo` INT NOT NULL DEFAULT 0,/* use legacy encryption by default to minimize the amount of support calls */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `intune_settings`;
CREATE TABLE `intune_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `purpose` INT NOT NULL,
    `pcc_key_service_provider` VARCHAR(256) NOT NULL DEFAULT '',
    `pcc_key_name` VARCHAR(256) NOT NULL DEFAULT '',
    `pcc_pub_key` MEDIUMTEXT NOT NULL,
    `azure_ad_url` VARCHAR(256) NOT NULL DEFAULT '',
    `tenant_id` VARCHAR(256) NOT NULL DEFAULT '',
    `client_id` VARCHAR(256) NOT NULL DEFAULT '',
    `client_secret` VARCHAR(256) NOT NULL DEFAULT '',
    `fetch_upn` BOOLEAN NOT NULL DEFAULT FALSE,
    `fetch_upn_binding` VARCHAR(256) NOT NULL DEFAULT '',
    `pfx_key_encryption_algo` INT NOT NULL DEFAULT 0, /* use legacy encryption by default to minimize the amount of support calls */
    `encryption_padding` INT NOT NULL DEFAULT 2,/* OEAP-SHA256 */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `workspaceone_settings`;
CREATE TABLE `workspaceone_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `url` VARCHAR(256) NOT NULL DEFAULT '',
    `api_token` VARCHAR(256) NOT NULL DEFAULT '',
    `auth_method` INT NOT NULL DEFAULT 0,
    `client_id` VARCHAR(256) NOT NULL DEFAULT '',
    `client_secret` VARCHAR(256) NOT NULL DEFAULT '',
    `auth_svr_scope` INT NOT NULL,
    `custom_auth_svr_url` VARCHAR(256) NOT NULL DEFAULT '',
    `auth_cert` MEDIUMTEXT NULL,
    `auth_key` MEDIUMTEXT NULL,
    `auth_key_local_encryption_type` INT NULL,
    `auth_key_local_encryption_bit` INT NULL,
    `workaround_dh_too_small_error` BOOLEAN NOT NULL DEFAULT FALSE,
    `pfx_key_encryption_algo` INT NOT NULL DEFAULT 0,/* use legacy encryption by default to minimize the amount of support calls */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `disclaimer_settings`;
CREATE TABLE `disclaimer_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `check_disclaimer_interval` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `default_disclaimer`;
CREATE TABLE `default_disclaimer` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `signature_name` VARCHAR(256) NOT NULL,
    `zip_content` LONGBLOB NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `disclaimer`;
CREATE TABLE `disclaimer` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `signature_name` VARCHAR(256) NOT NULL,
    `zip_content` LONGBLOB NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `disclaimer_domain`;
CREATE TABLE `disclaimer_domain` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `disclaimer_id` INT NOT NULL,
    `domain` VARCHAR(256) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`domain`, `disclaimer_id`),
    FOREIGN KEY (`disclaimer_id`)
        REFERENCES `disclaimer`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `user_lockout`;
CREATE TABLE `user_lockout` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_name` VARCHAR(255) NOT NULL,/* 255 utf-8 codepoints is max for InnoDB keys */
    `userid` VARCHAR(255) NOT NULL,
    `is_temp_lock` BOOLEAN NOT NULL,
    `temp_lock_count` INT NOT NULL DEFAULT 0,
    `temp_lock_expiration_utc` BIGINT NULL, /* Not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_name`, `userid`))
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `account_failed_password_login`;
CREATE TABLE `account_failed_password_login` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(255) NOT NULL,
    `ip` VARCHAR(255) NULL,
    `utc` BIGINT NOT NULL,
    PRIMARY KEY (`id`))
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `keytalk_agent_msg_templ_on_unattended_popup`;
CREATE TABLE `keytalk_agent_msg_templ_on_unattended_popup` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `msg` VARCHAR(1024) NOT NULL,
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `keytalk_agent_msg_on_successful_auth`;
CREATE TABLE `keytalk_agent_msg_on_successful_auth` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `last_modified_utc` BIGINT NULL, /* Not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    `msg` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `seat_certificate_transactions`;
CREATE TABLE `seat_certificate_transactions` (
    `id` INT NOT NULL AUTO_INCREMENT,
    /* 255 utf-8 codepoints is max for InnoDB keys */
    `service_name` VARCHAR(255) NOT NULL,
    `license_serial` VARCHAR(255) NOT NULL,
    `license_function` VARCHAR(255) NOT NULL,
    `number_of_transactions` BIGINT NOT NULL,
    `last_modified_utc` BIGINT NULL,/* Not DATETIME because mysqlpp driver can't correctly handle UTC enforcing local time instead */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_name`, `license_serial`, `license_function`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `license`;
CREATE TABLE `license` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `active` BOOLEAN NOT NULL,
    `text` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `invalid_license_sysadmin_notification`;
CREATE TABLE `invalid_license_sysadmin_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `license_id` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`license_id`),
    FOREIGN KEY (`license_id`)
        REFERENCES `license`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `invalid_license_non_sysadmin_notification`;
CREATE TABLE `invalid_license_non_sysadmin_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `license_id` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`account_id`, `license_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`license_id`)
        REFERENCES `license`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_seat_quota_reached_notification`;
CREATE TABLE `service_seat_quota_reached_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`account_id`, `service_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_seat_quota_approaching_notification`;
CREATE TABLE `service_seat_quota_approaching_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`account_id`, `service_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_seat_quota_expired_notification`;
CREATE TABLE `service_seat_quota_expired_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    `seat_quota_expiry_utc` BIGINT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`account_id`, `service_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_seat_quota_expiring_notification`;
CREATE TABLE `service_seat_quota_expiring_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    `seat_quota_expiry_utc` BIGINT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`account_id`, `service_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `tenant_seat_quota_reached_notification`;
CREATE TABLE `tenant_seat_quota_reached_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `tenant_id` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`account_id`, `tenant_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`tenant_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `tenant_seat_quota_approaching_notification`;
CREATE TABLE `tenant_seat_quota_approaching_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `tenant_id` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`account_id`, `tenant_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`tenant_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `tenant_seat_quota_expired_notification`;
CREATE TABLE `tenant_seat_quota_expired_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `tenant_id` INT NOT NULL,
    `seat_quota_expiry_utc` BIGINT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`account_id`, `tenant_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`tenant_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `tenant_seat_quota_expiring_notification`;
CREATE TABLE `tenant_seat_quota_expiring_notification` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `account_id` INT NOT NULL,
    `tenant_id` INT NOT NULL,
    `seat_quota_expiry_utc` BIGINT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`account_id`, `tenant_id`),
    FOREIGN KEY (`account_id`)
        REFERENCES `account`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`tenant_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `smime_cert_holder_notification_on_cert_expiration`;
CREATE TABLE `smime_cert_holder_notification_on_cert_expiration` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `devid_user_certificate_id` INT NOT NULL,
    `last_notified_utc` BIGINT NULL,
    `notifying_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`devid_user_certificate_id`),
    FOREIGN KEY (`devid_user_certificate_id`)
        REFERENCES `devid_user_certificate`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `configuration`;
CREATE TABLE `configuration` (
    /* 255 utf-8 codepoints is max for InnoDB keys */
    `name` VARCHAR(255),
    `val` MEDIUMTEXT NOT NULL,
    PRIMARY KEY (`name`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `smtp_configuration`;
CREATE TABLE `smtp_configuration` (
    `id` INT NOT NULL,
    `server_address` VARCHAR(256),
    `security` INT NOT NULL DEFAULT 0,
    `auth_username` VARCHAR(256),
    `auth_password` VARCHAR(256),
    `sender_email` VARCHAR(256),
    `smime_signing_certkey` MEDIUMTEXT,
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `sms_account_configuration`;
CREATE TABLE `sms_account_configuration` (
    `id` INT NOT NULL,
    `account_sid` VARCHAR(256),
    `auth_token` VARCHAR(256),
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `rccd`;
CREATE TABLE `rccd` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `data` MEDIUMBLOB NOT NULL,
    `download_uid` VARCHAR(255) NOT NULL,
    `content_verson` INT NOT NULL, /* redundant metadata */
    PRIMARY KEY (`id`),
    UNIQUE KEY (`download_uid`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `rccd_service`;
CREATE TABLE `rccd_service` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `rccd_id` INT NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`rccd_id`, `service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`rccd_id`)
        REFERENCES `rccd`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `intune_scep_configuration`;
CREATE TABLE `intune_scep_configuration` (
    `id` INT NOT NULL,
    `service_id` INT NOT NULL,
    `recipient_cert` MEDIUMTEXT NULL,
    `recipient_key` MEDIUMTEXT NULL,
    `recipient_key_local_encryption_type` INT NULL,
    `recipient_key_local_encryption_bit` INT NULL,
    `signing_cert` MEDIUMTEXT NULL,
    `signing_key` MEDIUMTEXT NULL,
    `signing_key_local_encryption_type` INT NULL,
    `signing_key_local_encryption_bit` INT NULL,
    `issuer_certs` MEDIUMTEXT NULL,
    `ra_service_name` VARCHAR(256) NOT NULL DEFAULT '',
    `ra_cn_prefix` VARCHAR(256) NOT NULL DEFAULT '',
    `scep_ra_cert_expiration_margin` INT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `intune_scep_ra_cert_renewal`;
CREATE TABLE `intune_scep_ra_cert_renewal` (
    `id` INT NOT NULL,
    `renewal_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `generic_scep_configuration`;
CREATE TABLE `generic_scep_configuration` (
    `id` INT NOT NULL,
    `service_id` INT NOT NULL,
    `recipient_cert` MEDIUMTEXT NULL,
    `recipient_key` MEDIUMTEXT NULL,
    `recipient_key_local_encryption_type` INT NULL,
    `recipient_key_local_encryption_bit` INT NULL,
    `signing_cert` MEDIUMTEXT NULL,
    `signing_key` MEDIUMTEXT NULL,
    `signing_key_local_encryption_type` INT NULL,
    `signing_key_local_encryption_bit` INT NULL,
    `issuer_certs` MEDIUMTEXT NULL,
    `ra_service_name` VARCHAR(256) NOT NULL DEFAULT '',
    `ra_cn_prefix` VARCHAR(256) NOT NULL DEFAULT '',
    `scep_ra_cert_expiration_margin` INT NULL,
    `challenge` VARCHAR(256) NULL,
    `p7_payload_encryption_algo` INT NOT NULL DEFAULT 0, # AES-256-CBC by default
    `is_static_challenge_password` BOOLEAN DEFAULT FALSE,
    `static_challenge_password` VARCHAR(256) NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `generic_scep_ra_cert_renewal`;
CREATE TABLE `generic_scep_ra_cert_renewal` (
    `id` INT NOT NULL,
    `renewal_started_utc` BIGINT NULL,
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `acme_configuration`;
CREATE TABLE `acme_configuration` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `email_new_seat_cert_to_holder`;
CREATE TABLE `email_new_seat_cert_to_holder` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `auto_gen_key_passwd` BOOLEAN NOT NULL,
    `certkey_fmt` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `scheduled_settings_backup_config`;
CREATE TABLE `scheduled_settings_backup_config` (
    `id` INT NOT NULL,
    `period_days`  INT NOT NULL,
    `offset_hours`  INT NOT NULL,
    `ssh_hostname` VARCHAR(256) NOT NULL,
    `ssh_username` VARCHAR(256) NOT NULL,
    `remote_filepath` VARCHAR(256) NOT NULL,
    `ssh_host_port` INT NOT NULL,
    `settings_creation_flags` INT NOT NULL,
    `aes_encryption_key` VARCHAR(256) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `scheduled_settings_backup`;
CREATE TABLE `scheduled_settings_backup` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `server_uuid` VARCHAR(256) NOT NULL,
    `start_utc` BIGINT NOT NULL,
    `in_progress` BOOLEAN NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`server_uuid`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

/* BE CEREFUL WHEN ALTERING THIS TABLE or it might cause automatic fw updates to stop working (updaterd daemon) */
DROP TABLE IF EXISTS `available_firmware_info`;
CREATE TABLE `available_firmware_info` (
    `version` VARCHAR(255),
    `min_compatible_version` VARCHAR(255),
    `first_found_utc` BIGINT,
    UNIQUE KEY (`version`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

/* BE CEREFUL WHEN ALTERING THIS TABLE or it might cause automatic fw updates to stop working (updaterd daemon) */
DROP TABLE IF EXISTS `firmware_availability_last_checked`;
CREATE TABLE `firmware_availability_last_checked` (
    `utc` BIGINT
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

/* BE CEREFUL WHEN ALTERING THIS TABLE or it might cause automatic fw updates to stop working (updaterd daemon) */
DROP TABLE IF EXISTS `ongoing_fw_installation`;
CREATE TABLE `ongoing_fw_installation` (
    `started_utc` BIGINT,
    UNIQUE KEY (`started_utc`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

/* BE CEREFUL WHEN ALTERING THIS TABLE or it might cause automatic fw updates to stop working (updaterd daemon) */
DROP TABLE IF EXISTS `firmware_settings`;
CREATE TABLE `firmware_settings` (
    `automatically_install_new_fw` BOOLEAN NOT NULL DEFAULT TRUE
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
INSERT INTO firmware_settings (`automatically_install_new_fw`) VALUES (TRUE);

DROP TABLE IF EXISTS `admin_notification_email_templ`;
CREATE TABLE `admin_notification_email_templ` (
    `notification_type` INT NOT NULL,
    `subj` VARCHAR(256) NOT NULL,
    `body` MEDIUMTEXT NOT NULL,
    `bodyfmt` INT NOT NULL DEFAULT 0,
    UNIQUE KEY (`notification_type`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `ongoing_seat_cert_renewal`;
CREATE TABLE `ongoing_seat_cert_renewal` (
    `seat_id` INT NOT NULL,
    `started_utc` BIGINT,
    UNIQUE KEY (`seat_id`),
    FOREIGN KEY (`seat_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `service_group`;
CREATE TABLE `service_group` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `seat_quota` INT NULL,
    `seat_quota_expiry_utc` BIGINT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`name`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `service_group_cross`;
CREATE TABLE `service_group_cross` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `group_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`, `group_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`group_id`)
        REFERENCES `service_group`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `certkey_modification_approvers_for_managers`;
CREATE TABLE `certkey_modification_approvers_for_managers` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `role` INT NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`role`, `service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `certkey_modification_approvers_for_operators`;
CREATE TABLE `certkey_modification_approvers_for_operators` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `role` INT NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`role`, `service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `cert_request_approvers_for_keytalk_agents`;
CREATE TABLE `cert_request_approvers_for_keytalk_agents` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `role` INT NOT NULL,
    `service_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`role`, `service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;


DROP TABLE IF EXISTS `scep_ca_certgen_settings`;
CREATE TABLE `scep_ca_certgen_settings`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `scep_svr_url` VARCHAR(1024) NOT NULL,
    `challenge_password` VARCHAR(256) NULL,
    `ssl_verification_ca_cert` MEDIUMTEXT NULL,
    `otp_enabled` BOOLEAN NULL,
    `otp_svr_url` VARCHAR(1024) NULL,
    `otp_svr_user` VARCHAR(256) NULL,
    `otp_svr_password` VARCHAR(256) NULL,
    `otp_svr_ca_chain` MEDIUMTEXT NULL,
    `bypass_system_proxy` BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `scep_ca_certgen_otp`;
CREATE TABLE `scep_ca_certgen_otp`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `settings_id` INT NOT NULL,
    `start_utc` BIGINT NOT NULL,
    `otp` VARCHAR(256) NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`settings_id`)
        REFERENCES `scep_ca_certgen_settings`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `csr_match_rules`;
CREATE TABLE `csr_match_rules`(
    `id` INT NOT NULL AUTO_INCREMENT,
    `service_id` INT NOT NULL,
    `subject_rule` INT NOT NULL,
    `san_rule` INT NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

-- KeyTalk AD tables

DROP TABLE IF EXISTS `ad_service_data`;
CREATE TABLE `ad_service_data` (
    `service_id` INT NOT NULL,
    `kind` INT NOT NULL DEFAULT 0,
    `status` INT NOT NULL DEFAULT 0,
    `time_utc` BIGINT NULL,
    `priv_key` MEDIUMTEXT NULL,
    `head_data` MEDIUMTEXT NULL,
    `body_data` LONGTEXT NULL,
    PRIMARY KEY (`service_id`, `kind`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `ad_seat_data`;
CREATE TABLE `ad_seat_data` (
    `service_id` INT NOT NULL,
    `seat_id` INT NOT NULL,
    `kind` INT NOT NULL DEFAULT 0,
    `status` INT NOT NULL DEFAULT 0,
    `time_utc` BIGINT NULL,
    `priv_key` MEDIUMTEXT NULL,
    `head_data` MEDIUMTEXT NULL,
    `body_data` LONGTEXT NULL,
    PRIMARY KEY (`service_id`, `seat_id`, `kind`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`seat_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `ad_transaction`;
CREATE TABLE `ad_transaction` (
    `service_id` INT NOT NULL,
    `transaction_id` VARCHAR(32) NOT NULL,
    `kind` INT NOT NULL DEFAULT 0,
    `status` INT NOT NULL DEFAULT 0,
    `stage` INT NOT NULL DEFAULT 0,
    `request_task_id` INT NOT NULL DEFAULT 0,
    `apply_task_id` INT NOT NULL DEFAULT 0,
    `begin_time` BIGINT NULL,
    `end_time` BIGINT NULL,
    `head_data` TEXT NULL,
    `body_data` MEDIUMTEXT NULL,
    `tag` VARCHAR(256) NULL,
    PRIMARY KEY (`service_id`, `transaction_id`, `kind`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `smb_link`;
CREATE TABLE `smb_link` (
    `smb_seat_id` INT NOT NULL,
    `member_seat_id` INT NOT NULL,
    `update_time_utc` BIGINT NULL,
    PRIMARY KEY (`smb_seat_id`, `member_seat_id`),
    FOREIGN KEY (`smb_seat_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade,
    FOREIGN KEY (`member_seat_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `smb_info`;
CREATE TABLE `smb_info` (
    `seat_id` INT NOT NULL,
    `smb_upn` VARCHAR(256) NULL,
    `update_time_utc` BIGINT NULL,
    PRIMARY KEY (`seat_id`),
    FOREIGN KEY (`seat_id`)
        REFERENCES `devid_user`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `intune_certs_publish_request`;
CREATE TABLE `intune_certs_publish_request` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `created_utc` BIGINT NULL,
    `service_id` INT NOT NULL,
    `cert_attr_lookup` INT NOT NULL,
    `ad_attr_type` INT NULL,
    `ad_attr_extension_name` VARCHAR(256) NULL,
    `ad_attr_name` VARCHAR(256) NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`service_id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `intune_cert_publish_request`;
CREATE TABLE `intune_cert_publish_request` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `request_id` VARCHAR(255) NOT NULL,
    `intune_certs_publish_request_id` INT NOT NULL,
    `status` INT NOT NULL,
    `last_updated_utc` BIGINT NULL,
    `seat_name` VARCHAR(255) NULL,
    `certs` MEDIUMTEXT NULL,
    `privkey` MEDIUMTEXT NULL,
    `privkey_local_encryption_type` INT NULL,
    `privkey_local_encryption_bit` INT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`request_id`),
    FOREIGN KEY (`intune_certs_publish_request_id`)
        REFERENCES `intune_certs_publish_request`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `db_encryption_report`;
CREATE TABLE `db_encryption_report` (
    `mac_address` VARCHAR(32) NOT NULL,
    `ip_address` VARCHAR(64) NULL,
    `encryption_type` INT NOT NULL,
    `ciphertext` VARCHAR(1024) NULL,
    `last_updated_utc` BIGINT NOT NULL,
    PRIMARY KEY (`mac_address`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;


/* Populate Db with a minimal viable set of data */
/* NOTE: keep in sync with the server constants defined in rsvr/common.h and resept/common.h ! */

SET @auditor_password='change!';
SET @auditor_password_salt='95b6cb9e3ff040bb9736cbb34be51d40';
SET @auditor_password_hash='a7fbc046b795b4cd02b96c6b498f8ea0e0481e612ab3075b1fbf2dac02b89dadc2553b0231887845983ba221e308ed27bba40bf64cb39aef2d221ea161d6b2f0';
INSERT INTO `account` (`id`, `role`) VALUES
(1, 2)
;
INSERT INTO `account_password` (`login_name`,`salt`,`password`,`account_id`,`hash_algo`) VALUES
 ('auditor', @auditor_password_salt, @auditor_password_hash, 1, 1)
 ;


INSERT INTO `configuration`
VALUES
('db_schema_version', '186.0'),
('license_remaining_transactions_threshold', '1000'),
('auto_restart_fail_stopped_services', '1')
;


SET foreign_key_checks = 1;

-- Helper table for proper sorting of learn modes
DROP TABLE IF EXISTS `learn_mode_prio`;

CREATE TABLE `learn_mode_prio` (
  `id` SMALLINT NOT NULL,
  `prio` INT NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

LOCK TABLES `learn_mode_prio` WRITE;
INSERT INTO `learn_mode_prio` VALUES (0, 1), (1, 2), (2, 0), (3, 3);
UNLOCK TABLES;

DROP TABLE IF EXISTS `meta_data`;
CREATE TABLE `meta_data` (
    `kind` INT NOT NULL DEFAULT 0,
    `id` VARCHAR(256) NOT NULL,
    `status` INT NOT NULL DEFAULT 0,
    `time_utc` BIGINT NULL,
    `critical_data` LONGTEXT NULL,
    `plain_data` LONGTEXT NULL,
    PRIMARY KEY (`kind`, `id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `template_meta_data`;
CREATE TABLE `template_meta_data` (
    `kind` INT NOT NULL DEFAULT 0,
    `service_id` INT NOT NULL,
    `id` VARCHAR(256) NOT NULL,
    `status` INT NOT NULL DEFAULT 0,
    `time_utc` BIGINT NULL,
    `critical_data` LONGTEXT NULL,
    `plain_data` LONGTEXT NULL,
    PRIMARY KEY (`kind`, `service_id`, `id`),
    FOREIGN KEY (`service_id`)
        REFERENCES `service`(`id`)
        ON DELETE cascade
        ON UPDATE cascade
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create PQC Certificate table and indices

DROP TABLE IF EXISTS `pqc_certificate`;
CREATE TABLE `pqc_certificate` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `creation_utc` BIGINT NOT NULL DEFAULT 0,
    `not_before_utc` BIGINT NOT NULL DEFAULT 0,
    `not_after_utc` BIGINT NOT NULL DEFAULT 0,
    `cert_kind` INT NOT NULL DEFAULT 0,
    `pqc_mode` INT NOT NULL DEFAULT 0,
    `main_algo` INT NOT NULL DEFAULT 0,
    `alt_algo` INT NOT NULL DEFAULT 0,
    `main_key_kind` INT NOT NULL DEFAULT 0,
    `alt_key_kind` INT NOT NULL DEFAULT 0,
    `serial_number` VARCHAR(256) NULL,
    `fingerprint` VARCHAR(512) NULL,
    `subject_cn` VARCHAR(256) NULL,
    `issuer_fingerprint` VARCHAR(512) NULL,
    `issuer_cn` VARCHAR(256) NULL,
    `san` VARCHAR(8192) NULL,
    `key_pem` MEDIUMTEXT NULL,
    `cert_pem` MEDIUMTEXT NULL,
    PRIMARY KEY (`id`)
)
DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Indexes for numeric/time lookups and sorting
CREATE INDEX `idx_not_before` ON `pqc_certificate`(not_before_utc);
CREATE INDEX `idx_not_after` ON `pqc_certificate`(not_after_utc);
CREATE INDEX `idx_not_before_after` ON `pqc_certificate`(not_before_utc, not_after_utc);
CREATE INDEX `idx_cert_kind` ON `pqc_certificate`(cert_kind);
CREATE INDEX `idx_pqc_mode` ON `pqc_certificate`(pqc_mode);
CREATE INDEX `idx_main_algo` ON `pqc_certificate`(main_algo);
CREATE INDEX `idx_alt_algo` ON `pqc_certificate`(alt_algo);
CREATE INDEX `idx_cert_kind_not_after` ON `pqc_certificate`(cert_kind, not_after_utc);
CREATE INDEX `idx_pqc_mode_not_after` ON `pqc_certificate`(pqc_mode, not_after_utc);
CREATE INDEX `idx_main_algo_not_after` ON `pqc_certificate`(main_algo, not_after_utc);

-- Prefix indexes for text searching/sorting
CREATE INDEX `idx_serial_number` ON `pqc_certificate`(serial_number(255));
CREATE INDEX `idx_fingerprint` ON `pqc_certificate`(fingerprint(255));
CREATE INDEX `idx_subject_cn` ON `pqc_certificate`(subject_cn(255));
CREATE INDEX `idx_issuer_fingerprint` ON `pqc_certificate`(issuer_fingerprint(255));
CREATE INDEX `idx_issuer_cn` ON `pqc_certificate`(issuer_cn(255));
CREATE INDEX `idx_san` ON `pqc_certificate`(san(255));
CREATE INDEX `idx_subject_cn_san` ON `pqc_certificate`(subject_cn(255), san(255));
CREATE INDEX `idx_subject_issuer_cn` ON `pqc_certificate`(subject_cn(255), issuer_cn(255));
CREATE INDEX `idx_subject_issuer_cn_san` ON `pqc_certificate`(subject_cn(255), issuer_cn(255), san(255));
