zms_server.sql

-- MySQL Script generated by MySQL Workbench
-- Mon Jul  8 11:11:29 2024
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema zms_server
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema zms_server
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `zms_server` DEFAULT CHARACTER SET utf8 ;
USE `zms_server` ;

-- -----------------------------------------------------
-- Table `zms_server`.`domain`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`domain` (
  `domain_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(512) NOT NULL,
  `description` VARCHAR(4096) NOT NULL DEFAULT '',
  `org` VARCHAR(1024) NOT NULL DEFAULT '',
  `uuid` VARCHAR(128) NOT NULL DEFAULT '',
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `audit_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `ypm_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `account` VARCHAR(128) NOT NULL DEFAULT '',
  `modified` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `created` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `application_id` VARCHAR(128) NOT NULL DEFAULT '',
  `cert_dns_domain` VARCHAR(256) NOT NULL DEFAULT '',
  `member_expiry_days` INT NOT NULL DEFAULT 0,
  `token_expiry_mins` INT NOT NULL DEFAULT 0,
  `service_cert_expiry_mins` INT NOT NULL DEFAULT 0,
  `role_cert_expiry_mins` INT NOT NULL DEFAULT 0,
  `sign_algorithm` VARCHAR(64) NOT NULL DEFAULT '',
  `service_expiry_days` INT NOT NULL DEFAULT 0,
  `user_authority_filter` VARCHAR(512) NOT NULL DEFAULT '',
  `group_expiry_days` INT NOT NULL DEFAULT 0,
  `azure_subscription` VARCHAR(128) NOT NULL DEFAULT '',
  `business_service` VARCHAR(256) NOT NULL DEFAULT '',
  `member_purge_expiry_days` INT NOT NULL DEFAULT 0,
  `gcp_project` VARCHAR(128) NOT NULL DEFAULT '',
  `gcp_project_number` VARCHAR(64) NOT NULL DEFAULT '',
  `product_id` VARCHAR(128) NOT NULL DEFAULT '',
  `feature_flags` INT NOT NULL DEFAULT 0,
  `environment` VARCHAR(32) NOT NULL DEFAULT '',
  `resource_owner` VARCHAR(256) NOT NULL DEFAULT '',
  `azure_tenant` VARCHAR(128) NOT NULL DEFAULT '',
  `azure_client` VARCHAR(128) NOT NULL DEFAULT '',
  `x509_cert_signer_keyid` VARCHAR(64) NOT NULL DEFAULT '',
  `ssh_cert_signer_keyid` VARCHAR(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`domain_id`),
  UNIQUE INDEX `uq_name` (`name` ASC),
  INDEX `idx_modified` (`modified` ASC),
  INDEX `idx_account` (`account` ASC),
  INDEX `idx_ypmid` (`ypm_id` ASC),
  INDEX `idx_appid` (`application_id` ASC),
  INDEX `idx_azure` (`azure_subscription` ASC),
  INDEX `idx_business_service` (`business_service` ASC),
  INDEX `idx_gcp` (`gcp_project` ASC),
  INDEX `idx_product_id` (`product_id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`role`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`role` (
  `role_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `domain_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(512) NOT NULL,
  `trust` VARCHAR(512) NOT NULL DEFAULT '',
  `modified` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `created` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `audit_enabled` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Adding audit_enabled column to store role meta attribute specifying whether role has audit_enabled',
  `self_serve` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Adding self_serve flag so that any user can request role membership for the given role and it will go for domain admin’s approval.',
  `member_expiry_days` INT NOT NULL DEFAULT 0,
  `token_expiry_mins` INT NOT NULL DEFAULT 0,
  `cert_expiry_mins` INT NOT NULL DEFAULT 0,
  `sign_algorithm` VARCHAR(64) NOT NULL DEFAULT '',
  `service_expiry_days` INT NOT NULL DEFAULT 0,
  `member_review_days` INT NOT NULL DEFAULT 0,
  `service_review_days` INT NOT NULL DEFAULT 0,
  `group_review_days` INT NOT NULL DEFAULT 0,
  `review_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `notify_roles` VARCHAR(512) NOT NULL DEFAULT '',
  `last_reviewed_time` DATETIME(3) NULL,
  `user_authority_filter` VARCHAR(512) NOT NULL DEFAULT '',
  `user_authority_expiration` VARCHAR(64) NOT NULL DEFAULT '',
  `group_expiry_days` INT NOT NULL DEFAULT 0,
  `description` VARCHAR(4096) NOT NULL DEFAULT '',
  `delete_protection` TINYINT(1) NOT NULL DEFAULT 0,
  `max_members` INT NOT NULL DEFAULT 0,
  `self_renew` TINYINT(1) NOT NULL DEFAULT 0,
  `self_renew_mins` INT NOT NULL DEFAULT 0,
  `resource_owner` VARCHAR(256) NOT NULL DEFAULT '',
  `principal_domain_filter` VARCHAR(1024) NOT NULL DEFAULT '',
  PRIMARY KEY (`role_id`),
  UNIQUE INDEX `uq_domain_role` (`domain_id` ASC, `name` ASC),
  CONSTRAINT `fk_role_domain`
    FOREIGN KEY (`domain_id`)
    REFERENCES `zms_server`.`domain` (`domain_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`principal`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`principal` (
  `principal_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(512) NOT NULL,
  `system_suspended` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Flag to hold Principal state coming from Authority ',
  PRIMARY KEY (`principal_id`),
  UNIQUE INDEX `uq_name` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`policy`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`policy` (
  `policy_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `domain_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(512) NOT NULL,
  `modified` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `created` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `version` VARCHAR(32) NOT NULL DEFAULT '0',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `description` VARCHAR(4096) NOT NULL DEFAULT '',
  `resource_owner` VARCHAR(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`policy_id`),
  INDEX `fk_policy_domain_idx` (`domain_id` ASC),
  UNIQUE INDEX `uq_domain_policy` (`name` ASC, `domain_id` ASC, `version` ASC),
  CONSTRAINT `fk_policy_domain`
    FOREIGN KEY (`domain_id`)
    REFERENCES `zms_server`.`domain` (`domain_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`assertion`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`assertion` (
  `assertion_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `policy_id` INT UNSIGNED NOT NULL,
  `role` VARCHAR(512) NOT NULL,
  `resource` VARCHAR(512) NOT NULL,
  `action` VARCHAR(128) NOT NULL,
  `effect` VARCHAR(16) NOT NULL DEFAULT 'ALLOW',
  PRIMARY KEY (`assertion_id`),
  INDEX `fk_assertion_policy_idx` (`policy_id` ASC),
  INDEX `idx_role` (`role` ASC),
  INDEX `idx_resource` (`resource` ASC),
  INDEX `idx_action` (`action` ASC),
  INDEX `idx_effect` (`effect` ASC),
  CONSTRAINT `fk_policy_assertion_policy`
    FOREIGN KEY (`policy_id`)
    REFERENCES `zms_server`.`policy` (`policy_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`service`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`service` (
  `service_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `domain_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(512) NOT NULL,
  `provider_endpoint` VARCHAR(512) NOT NULL DEFAULT '',
  `executable` VARCHAR(256) NOT NULL DEFAULT '',
  `svc_user` VARCHAR(256) NOT NULL DEFAULT '',
  `svc_group` VARCHAR(256) NOT NULL DEFAULT '',
  `modified` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `created` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `description` VARCHAR(4096) NOT NULL DEFAULT '',
  `resource_owner` VARCHAR(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`service_id`),
  INDEX `fk_service_domain_idx` (`domain_id` ASC),
  UNIQUE INDEX `uq_domain_service` (`name` ASC, `domain_id` ASC),
  CONSTRAINT `fk_service_domain`
    FOREIGN KEY (`domain_id`)
    REFERENCES `zms_server`.`domain` (`domain_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`public_key`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`public_key` (
  `service_id` INT UNSIGNED NOT NULL,
  `key_id` VARCHAR(128) NOT NULL,
  `key_value` TEXT(65535) NOT NULL,
  PRIMARY KEY (`service_id`, `key_id`),
  CONSTRAINT `fk_service_public_key_service`
    FOREIGN KEY (`service_id`)
    REFERENCES `zms_server`.`service` (`service_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`host`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`host` (
  `host_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(512) NOT NULL,
  PRIMARY KEY (`host_id`),
  UNIQUE INDEX `uq_name` (`name` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`service_host`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`service_host` (
  `service_id` INT UNSIGNED NOT NULL,
  `host_id` INT UNSIGNED NOT NULL,
  INDEX `idx_host` (`host_id` ASC, `service_id` ASC),
  PRIMARY KEY (`service_id`, `host_id`),
  CONSTRAINT `fk_service_host_host`
    FOREIGN KEY (`host_id`)
    REFERENCES `zms_server`.`host` (`host_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_service_host_service`
    FOREIGN KEY (`service_id`)
    REFERENCES `zms_server`.`service` (`service_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`entity`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`entity` (
  `name` VARCHAR(256) NOT NULL,
  `domain_id` INT UNSIGNED NOT NULL,
  `value` TEXT(65536) NOT NULL,
  `modified` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `created` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`name`, `domain_id`),
  CONSTRAINT `fk_entity_domain`
    FOREIGN KEY (`domain_id`)
    REFERENCES `zms_server`.`domain` (`domain_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`role_member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`role_member` (
  `role_id` INT UNSIGNED NOT NULL,
  `principal_id` INT UNSIGNED NOT NULL,
  `expiration` DATETIME(3) NULL,
  `review_reminder` DATETIME(3) NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `audit_ref` VARCHAR(512) NULL COMMENT 'Audit reference mandatory for membership changes of audit enabled / self serve roles',
  `last_notified_time` DATETIME(3) NULL,
  `server` VARCHAR(512) NULL,
  `req_principal` VARCHAR(512) NOT NULL DEFAULT '',
  `review_last_notified_time` DATETIME(3) NULL,
  `review_server` VARCHAR(512) NULL,
  `system_disabled` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`role_id`, `principal_id`),
  INDEX `idx_principal` (`principal_id` ASC, `role_id` ASC),
  CONSTRAINT `fk_role_member_role`
    FOREIGN KEY (`role_id`)
    REFERENCES `zms_server`.`role` (`role_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_role_member_principal`
    FOREIGN KEY (`principal_id`)
    REFERENCES `zms_server`.`principal` (`principal_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`domain_template`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`domain_template` (
  `domain_id` INT UNSIGNED NOT NULL,
  `template` VARCHAR(64) NOT NULL,
  `current_version` INT NOT NULL DEFAULT 0,
  UNIQUE INDEX `uq_domain_template` (`template` ASC, `domain_id` ASC),
  PRIMARY KEY (`domain_id`, `template`),
  CONSTRAINT `fk_domain_template_domain`
    FOREIGN KEY (`domain_id`)
    REFERENCES `zms_server`.`domain` (`domain_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`role_audit_log`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`role_audit_log` (
  `audit_log_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` INT UNSIGNED NOT NULL,
  `admin` VARCHAR(512) NOT NULL,
  `member` VARCHAR(512) NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action` VARCHAR(32) NOT NULL,
  `audit_ref` VARCHAR(512) NOT NULL,
  INDEX `fk_role_audit_log_role_id_idx` (`role_id` ASC),
  PRIMARY KEY (`audit_log_id`),
  CONSTRAINT `fk_role_audit_log_role`
    FOREIGN KEY (`role_id`)
    REFERENCES `zms_server`.`role` (`role_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`quota`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`quota` (
  `domain_id` INT UNSIGNED NOT NULL,
  `subdomain` INT UNSIGNED NOT NULL,
  `role` INT UNSIGNED NOT NULL,
  `role_member` INT UNSIGNED NOT NULL,
  `policy` INT UNSIGNED NOT NULL,
  `assertion` INT UNSIGNED NOT NULL,
  `entity` INT UNSIGNED NOT NULL,
  `service` INT UNSIGNED NOT NULL,
  `service_host` INT UNSIGNED NOT NULL,
  `public_key` INT UNSIGNED NOT NULL,
  `modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `principal_group` INT UNSIGNED NOT NULL,
  `principal_group_member` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`domain_id`),
  CONSTRAINT `fk_quota_domain1`
    FOREIGN KEY (`domain_id`)
    REFERENCES `zms_server`.`domain` (`domain_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`pending_role_member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`pending_role_member` (
  `role_id` INT UNSIGNED NOT NULL,
  `principal_id` INT UNSIGNED NOT NULL,
  `expiration` DATETIME(3) NULL,
  `review_reminder` DATETIME(3) NULL,
  `audit_ref` VARCHAR(512) NULL COMMENT 'Audit reference mandatory for membership changes of audit enabled / self serve roles',
  `req_time` DATETIME(3) NULL DEFAULT CURRENT_TIMESTAMP(3),
  `last_notified_time` DATETIME(3) NULL DEFAULT CURRENT_TIMESTAMP(3),
  `server` VARCHAR(255) NULL COMMENT 'server responsible for updating the last_notified_time',
  `req_principal` VARCHAR(512) NOT NULL DEFAULT '',
  `pending_state` VARCHAR(12) NOT NULL DEFAULT 'ADD' COMMENT 'add state for pending request',
  PRIMARY KEY (`role_id`, `principal_id`),
  INDEX `idx_principal` (`principal_id` ASC, `role_id` ASC),
  CONSTRAINT `fk_pending_role_member_role`
    FOREIGN KEY (`role_id`)
    REFERENCES `zms_server`.`role` (`role_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pending_role_member_principal`
    FOREIGN KEY (`principal_id`)
    REFERENCES `zms_server`.`principal` (`principal_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`principal_group`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`principal_group` (
  `group_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `domain_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(512) NOT NULL,
  `modified` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `created` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `audit_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `self_serve` TINYINT(1) NOT NULL DEFAULT 0,
  `review_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `notify_roles` VARCHAR(512) NOT NULL DEFAULT '',
  `last_reviewed_time` DATETIME(3) NULL,
  `user_authority_filter` VARCHAR(512) NOT NULL DEFAULT '',
  `user_authority_expiration` VARCHAR(64) NOT NULL DEFAULT '',
  `member_expiry_days` INT NOT NULL DEFAULT 0,
  `service_expiry_days` INT NOT NULL DEFAULT 0,
  `delete_protection` TINYINT(1) NOT NULL DEFAULT 0,
  `max_members` INT NOT NULL DEFAULT 0,
  `self_renew` TINYINT(1) NOT NULL DEFAULT 0,
  `self_renew_mins` INT NOT NULL DEFAULT 0,
  `resource_owner` VARCHAR(256) NOT NULL DEFAULT '',
  `principal_domain_filter` VARCHAR(1024) NOT NULL DEFAULT '',
  PRIMARY KEY (`group_id`),
  UNIQUE INDEX `uq_domain_group` (`domain_id` ASC, `name` ASC),
  CONSTRAINT `fk_group_domain`
    FOREIGN KEY (`domain_id`)
    REFERENCES `zms_server`.`domain` (`domain_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`principal_group_member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`principal_group_member` (
  `group_id` INT UNSIGNED NOT NULL,
  `principal_id` INT UNSIGNED NOT NULL,
  `expiration` DATETIME(3) NULL,
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `system_disabled` INT NOT NULL DEFAULT 0,
  `audit_ref` VARCHAR(512) NULL,
  `last_notified_time` DATETIME(3) NULL,
  `server` VARCHAR(512) NULL,
  `req_principal` VARCHAR(512) NOT NULL DEFAULT '',
  INDEX `idx_principal` (`principal_id` ASC, `group_id` ASC),
  PRIMARY KEY (`group_id`, `principal_id`),
  CONSTRAINT `fk_group_member_group`
    FOREIGN KEY (`group_id`)
    REFERENCES `zms_server`.`principal_group` (`group_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_group_member_principal`
    FOREIGN KEY (`principal_id`)
    REFERENCES `zms_server`.`principal` (`principal_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`pending_principal_group_member`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`pending_principal_group_member` (
  `group_id` INT UNSIGNED NOT NULL,
  `principal_id` INT UNSIGNED NOT NULL,
  `expiration` DATETIME(3) NULL,
  `audit_ref` VARCHAR(512) NULL,
  `req_time` DATETIME(3) NULL DEFAULT CURRENT_TIMESTAMP(3),
  `last_notified_time` DATETIME(3) NULL DEFAULT CURRENT_TIMESTAMP(3),
  `server` VARCHAR(255) NULL,
  `req_principal` VARCHAR(512) NOT NULL DEFAULT '',
  `pending_state` VARCHAR(12) NOT NULL DEFAULT 'ADD' COMMENT 'add state for pending request',
  PRIMARY KEY (`group_id`, `principal_id`),
  INDEX `idx_principal` (`principal_id` ASC, `group_id` ASC),
  CONSTRAINT `fk_pending_group_member_group`
    FOREIGN KEY (`group_id`)
    REFERENCES `zms_server`.`principal_group` (`group_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pending_group_member_principal`
    FOREIGN KEY (`principal_id`)
    REFERENCES `zms_server`.`principal` (`principal_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`principal_group_audit_log`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`principal_group_audit_log` (
  `audit_log_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id` INT UNSIGNED NOT NULL,
  `admin` VARCHAR(512) NOT NULL,
  `member` VARCHAR(512) NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `action` VARCHAR(32) NOT NULL,
  `audit_ref` VARCHAR(512) NOT NULL,
  PRIMARY KEY (`audit_log_id`),
  INDEX `fk_group_audit_log_group_id_idx` (`group_id` ASC),
  CONSTRAINT `fk_group_audit_log_group`
    FOREIGN KEY (`group_id`)
    REFERENCES `zms_server`.`principal_group` (`group_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`role_tags`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`role_tags` (
  `role_id` INT UNSIGNED NOT NULL,
  `key` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL,
  `value` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`role_id`, `key`, `value`),
  INDEX `tag_key_val_sec_idx` (`key` ASC, `value` ASC),
  CONSTRAINT `fk_role_tag_role`
    FOREIGN KEY (`role_id`)
    REFERENCES `zms_server`.`role` (`role_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`domain_tags`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`domain_tags` (
  `domain_id` INT UNSIGNED NOT NULL,
  `key` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL,
  `value` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`key`, `value`, `domain_id`),
  CONSTRAINT `fk_domain_tag_domain`
    FOREIGN KEY (`domain_id`)
    REFERENCES `zms_server`.`domain` (`domain_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`assertion_condition`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`assertion_condition` (
  `assertion_id` INT UNSIGNED NOT NULL,
  `condition_id` INT NOT NULL,
  `key` VARCHAR(64) NOT NULL,
  `operator` VARCHAR(16) NOT NULL,
  `value` VARCHAR(2048) NOT NULL,
  PRIMARY KEY (`assertion_id`, `condition_id`, `key`),
  INDEX `fk_condition_assertion_idx` (`assertion_id` ASC),
  CONSTRAINT `assertion_id`
    FOREIGN KEY (`assertion_id`)
    REFERENCES `zms_server`.`assertion` (`assertion_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`group_tags`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`group_tags` (
  `group_id` INT UNSIGNED NOT NULL,
  `key` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL,
  `value` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`group_id`, `key`, `value`),
  INDEX `tag_key_val_sec_idx` (`key` ASC, `value` ASC),
  CONSTRAINT `fk_group_tag_group`
    FOREIGN KEY (`group_id`)
    REFERENCES `zms_server`.`principal_group` (`group_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`service_domain_dependency`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`service_domain_dependency` (
  `domain` VARCHAR(512) NOT NULL,
  `service` VARCHAR(1024) NOT NULL,
  PRIMARY KEY (`domain`(128), `service`(128)),
  INDEX `idx_service` (`service`(256) ASC),
  INDEX `idx_domain` (`domain`(256) ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`policy_tags`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`policy_tags` (
  `policy_id` INT UNSIGNED NOT NULL,
  `key` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL,
  `value` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`policy_id`, `key`, `value`),
  INDEX `tag_key_val_sec_idx` (`key` ASC, `value` ASC),
  CONSTRAINT `fk_policy_tag_policy`
    FOREIGN KEY (`policy_id`)
    REFERENCES `zms_server`.`policy` (`policy_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`service_tags`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`service_tags` (
  `service_id` INT UNSIGNED NOT NULL,
  `key` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NOT NULL,
  `value` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`service_id`, `key`, `value`),
  INDEX `tag_key_val_sec_idx` (`key` ASC, `value` ASC),
  CONSTRAINT `fk_service_tag_service`
    FOREIGN KEY (`service_id`)
    REFERENCES `zms_server`.`service` (`service_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `zms_server`.`domain_contacts`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `zms_server`.`domain_contacts` (
  `domain_id` INT UNSIGNED NOT NULL,
  `type` VARCHAR(64) NOT NULL,
  `name` VARCHAR(512) NOT NULL,
  PRIMARY KEY (`domain_id`, `type`),
  UNIQUE INDEX `uq_domain_contact` (`domain_id` ASC, `type` ASC),
  INDEX `idx_contact_name` (`name` ASC),
  CONSTRAINT `fk_domain_contacts_domain`
    FOREIGN KEY (`domain_id`)
    REFERENCES `zms_server`.`domain` (`domain_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;