/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

CREATE TABLE IF NOT EXISTS `audit_logs` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`auditable_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`auditable_type` VARCHAR(100) NULL DEFAULT NULL,
	`auditable_user_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`event` VARCHAR(7) NULL DEFAULT NULL,
	`attributes_original` TEXT NULL DEFAULT NULL,
	`attributes` TEXT NULL DEFAULT NULL,
	`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	INDEX `FK_audit_logs_users` (`user_id`),
	INDEX `FK_audit_logs_users_auditable` (`auditable_user_id`),
	CONSTRAINT `FK_audit_logs_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE SET NULL,
	CONSTRAINT `FK_audit_logs_users_auditable` FOREIGN KEY (`auditable_user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `bans` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `scope` VARCHAR(50) NULL DEFAULT 'server',
    `server_id` INT(10) NULL DEFAULT NULL,
    `server_group_id` INT UNSIGNED DEFAULT NULL,
    `offender_user_id` INT(10) UNSIGNED NOT NULL,
    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires` TIMESTAMP NULL DEFAULT NULL,
    `reason` VARCHAR(500) DEFAULT NULL,
    `admin_user_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_bans_servers` (`server_id`),
    KEY `FK_bans_server_groups` (`server_group_id`),
    CONSTRAINT `FK_bans_server_groups` FOREIGN KEY (`server_group_id`) REFERENCES `server_groups` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `FK_bans_servers` FOREIGN KEY (`server_id`) REFERENCES `servers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `features` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(20) DEFAULT NULL,
    `description` VARCHAR(1000) DEFAULT NULL,
    `icon` VARCHAR(75) DEFAULT NULL,
    `order` INT(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `navbar_links` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL,
    `icon` VARCHAR(75) DEFAULT NULL,
    `url` VARCHAR(300) NULL DEFAULT NULL,
    `nested_links` VARCHAR(3000) NULL DEFAULT NULL,
    `admin_only` TINYINT(4) NULL DEFAULT NULL,
    `order` INT(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `notifications` (
    `nid` BIGINT(20) unsigned NOT NULL AUTO_INCREMENT,
	`user_id` INT(10) UNSIGNED NOT NULL,
    `type` VARCHAR(50) DEFAULT NULL,
    `json` VARCHAR(1000) DEFAULT NULL,
    `read` TINYINT(4) DEFAULT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`nid`),
    INDEX `FK_notifications_users` (`user_id`),
    CONSTRAINT `FK_notifications_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `roles` (
    `rid` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `ingame_equivalent` VARCHAR(50) NOT NULL,
    `color` VARCHAR(6) NULL DEFAULT NULL,
    `icon` VARCHAR(75) NULL DEFAULT NULL,
    `order` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`rid`),
    UNIQUE INDEX `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `role_permissions` (
    `name` VARCHAR(50) NOT NULL,
    `subject` VARCHAR(50) NOT NULL,
    `rid` INT(10) DEFAULT NULL,
    `type` VARCHAR(50) NOT NULL,
    KEY `FK_role_permissions_roles` (`rid`),
    CONSTRAINT `FK_role_permissions_roles` FOREIGN KEY (`rid`) REFERENCES `roles` (`rid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `role_excluded_server_groups` (
    `role_id` INT(10) NOT NULL,
    `server_group_id` INT(10) UNSIGNED NOT NULL,
    UNIQUE INDEX `role_id_server_group_id` (`role_id`, `server_group_id`),
    INDEX `FK_role_excluded_server_groups_server_groups` (`server_group_id`),
    CONSTRAINT `FK_role_excluded_server_groups_roles` FOREIGN KEY (`role_id`) REFERENCES `roles` (`rid`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `FK_role_excluded_server_groups_server_groups` FOREIGN KEY (`server_group_id`) REFERENCES `server_groups` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `servers` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `token` VARCHAR(50) NULL DEFAULT NULL,
    `name` VARCHAR(30) NOT NULL,
    `game` VARCHAR(10) NOT NULL,
    `address` VARCHAR(255) NULL DEFAULT NULL,
    `port` MEDIUMINT(9) NULL DEFAULT NULL,
    `query_port` MEDIUMINT(9) NULL DEFAULT NULL,
    `rcon_port` MEDIUMINT(9) NULL DEFAULT NULL,
    `rcon_password` VARCHAR(50) NULL DEFAULT NULL,
    `ban_scope` ENUM('server', 'any', 'none') NOT NULL DEFAULT 'server',
	`ban_log` TINYINT(1) NULL DEFAULT '1',
    `default_ban_server_group_id` INT UNSIGNED DEFAULT NULL,
	`role_sync` ENUM('false', 'true', 'receive', 'send') NOT NULL DEFAULT 'true',
	`role_sync_create` TINYINT(1) NULL DEFAULT '1',
	`token_restrict_address` TINYINT NULL DEFAULT NULL,
	`poll_interval` SMALLINT(5) NULL DEFAULT '120',
    `image` VARCHAR(350) NULL DEFAULT NULL,
    `rules` VARCHAR(3000) NULL DEFAULT NULL,
	`visible` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
	`featured` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
	`metadata` JSON NULL DEFAULT NULL,
    `order` INT(10) UNSIGNED NULL DEFAULT NULL,
    `token_last_used_at` TIMESTAMP NULL DEFAULT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `FK_servers_server_groups` (`default_ban_server_group_id`),
    CONSTRAINT `FK_servers_server_groups` FOREIGN KEY (`default_ban_server_group_id`) REFERENCES `server_groups` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `server_groups` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `order` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
	UNIQUE INDEX `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `server_group_servers` (
	`server_group_id` INT(10) UNSIGNED NOT NULL,
	`server_id` INT(10) NOT NULL,
	UNIQUE INDEX `server_group_id_server_id` (`server_group_id`, `server_id`),
	INDEX `FK_server_group_servers_servers` (`server_id`),
	CONSTRAINT `FK_server_group_servers_servers` FOREIGN KEY (`server_id`) REFERENCES `servers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `FK_server_group_servers_server_groups` FOREIGN KEY (`server_group_id`) REFERENCES `server_groups` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `settings` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting` VARCHAR(50) NOT NULL,
    `category` VARCHAR(50) NOT NULL,
    `value` VARCHAR(15000) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `setting_category` (`setting`,`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_credits` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(10) UNSIGNED NOT NULL,
    `balance` MEDIUMINT(7) UNSIGNED NOT NULL DEFAULT '0',
    `actor_user_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `metadata` JSON NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `FK_store_credits_users` (`user_id`),
    INDEX `FK_store_credits_users_actor` (`actor_user_id`),
    CONSTRAINT `FK_store_credits_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `FK_store_credits_users_actor` FOREIGN KEY (`actor_user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_failed_payment_logs` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `processor` VARCHAR(50) NOT NULL,
    `processor_id` VARCHAR(100) NULL DEFAULT NULL,
    `message` VARCHAR(500) NOT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_packages` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `server_id` INT(10) NOT NULL,
	`category_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `name` VARCHAR(250) DEFAULT NULL,
    `cost_credits` MEDIUMINT(9) NULL DEFAULT NULL,
    `cost_currency` MEDIUMINT(9) NULL DEFAULT NULL,
    `valid_for` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
    `purchase_limit` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
    `image` VARCHAR(350) DEFAULT NULL,
    `description` TEXT NULL DEFAULT NULL,
    `short_description` VARCHAR(1000) NULL DEFAULT NULL,
	`visible` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
    `order` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_store_packages_servers` (`server_id`),
	KEY `FK_store_packages_store_package_categories` (`category_id`),
    CONSTRAINT `FK_store_packages_servers` FOREIGN KEY (`server_id`) REFERENCES `servers` (`id`) ON UPDATE CASCADE,
	CONSTRAINT `FK_store_packages_store_package_categories` FOREIGN KEY (`category_id`) REFERENCES `store_package_categories` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_package_actions` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `store_package_id` INT(10) UNSIGNED NOT NULL,
    `type` VARCHAR(255) NOT NULL,
    `payload` VARCHAR(10240) NULL DEFAULT NULL,
    `phase` ENUM('REDEEMABLE','ACTIVE','EXPIRING') NOT NULL DEFAULT 'REDEEMABLE',
    `user_presence_required` TINYINT(1) NULL DEFAULT '1',
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `FK_store_package_actions_store_packages` (`store_package_id`),
    CONSTRAINT `FK_store_package_actions_store_packages` FOREIGN KEY (`store_package_id`) REFERENCES `store_packages` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `store_package_action_executions` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`store_package_action_id` INT(10) UNSIGNED NOT NULL,
	`store_package_purchase_id` INT(10) UNSIGNED NOT NULL,
	`status` ENUM('QUEUED','PENDING','SUCCEEDED','FAILED') NOT NULL DEFAULT 'PENDING',
	`error` TEXT NULL DEFAULT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	INDEX `FK_store_package_action_executions_store_package_actions` (`store_package_action_id`),
	INDEX `FK_store_package_action_executions_store_package_purchases` (`store_package_purchase_id`),
	CONSTRAINT `FK_store_package_action_executions_store_package_actions` FOREIGN KEY (`store_package_action_id`) REFERENCES `store_package_actions` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
	CONSTRAINT `FK_store_package_action_executions_store_package_purchases` FOREIGN KEY (`store_package_purchase_id`) REFERENCES `store_package_purchases` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_package_categories` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `server_id` INT(10) NOT NULL,
    `name` VARCHAR(50) NULL DEFAULT NULL,
    `order` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_store_package_categories_servers` (`server_id`),
    CONSTRAINT `FK_store_package_categories_servers` FOREIGN KEY (`server_id`) REFERENCES `servers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_package_purchases` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `package_id` INT(10) UNSIGNED DEFAULT NULL,
    `payment_id` INT(10) DEFAULT NULL,
	`store_credit_id` INT(10) UNSIGNED NULL DEFAULT NULL,
	`user_id` INT(10) UNSIGNED NOT NULL,
    `metadata` JSON NULL DEFAULT NULL,
    `purchased_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `FK_store_package_purchases_store_packages` (`package_id`),
    INDEX `FK_store_package_purchases_store_payments` (`payment_id`),
    CONSTRAINT `FK_store_package_purchases_store_credits` FOREIGN KEY (`store_credit_id`) REFERENCES `store_credits` (`id`) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT `FK_store_package_purchases_store_packages` FOREIGN KEY (`package_id`) REFERENCES `store_packages` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `FK_store_package_purchases_store_payments` FOREIGN KEY (`payment_id`) REFERENCES `store_payments` (`id`) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_payments` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `processor` VARCHAR(50) NOT NULL,
    `processor_id` VARCHAR(100) NOT NULL,
	`user_id` INT(10) UNSIGNED NOT NULL,
    `currency` VARCHAR(3) NOT NULL,
    `total` INT(10) NOT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_sales` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	`description` VARCHAR(150) NULL DEFAULT NULL,
	`color` VARCHAR(6) NULL DEFAULT NULL,
	`base_discount` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
	`starts_at` TIMESTAMP NULL DEFAULT NULL,
	`ends_at` TIMESTAMP NULL DEFAULT NULL,
	`order` INT(10) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_sale_discountables` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`store_sale_id` INT(10) UNSIGNED NOT NULL,
	`store_sale_discountable_id` INT(10) NOT NULL,
	`store_sale_discountable_type` VARCHAR(100) NOT NULL,
	`discount` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	INDEX `FK_store_sale_discountables_store_sales` (`store_sale_id`),
	CONSTRAINT `FK_store_sale_discountables_store_sales` FOREIGN KEY (`store_sale_id`) REFERENCES `store_sales` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `team` (
	`user_id` INT(10) UNSIGNED NOT NULL,
    `nameoverride` VARCHAR(50) DEFAULT NULL,
    `server` INT(10) DEFAULT NULL,
    `image` VARCHAR(350) DEFAULT NULL,
    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `order` INT UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`user_id`),
    KEY `FK_team_servers` (`server`),
    CONSTRAINT `FK_team_servers` FOREIGN KEY (`server`) REFERENCES `servers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `FK_team_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `translations` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `locale` VARCHAR(6) NOT NULL,
    `category` VARCHAR(30) NOT NULL,
    `key` VARCHAR(50) NOT NULL,
    `value` VARCHAR(250) NOT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `locale_category_key` (`locale`, `category`, `key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT NULL,
	`avatar` VARCHAR(500) NULL DEFAULT NULL,
    `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_online` TIMESTAMP NULL DEFAULT NULL,
    `last_played` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_external_accounts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `source_id` VARCHAR(255) NOT NULL,
    `external_account_id` VARCHAR(255) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `source_id_user_id` (`source_id`, `user_id`),
    UNIQUE INDEX `external_account_id` (`external_account_id`),
    CONSTRAINT `FK_user_external_accounts_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_revoked_roles` (
	`user_id` INT(10) UNSIGNED NOT NULL,
    `rid` INT(10) NOT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE INDEX `user_id_rid` (`user_id`, `rid`),
    INDEX `FK_user_revoked_roles_roles` (`rid`),
    CONSTRAINT `FK_user_revoked_roles_roles` FOREIGN KEY (`rid`) REFERENCES `roles` (`rid`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_user_revoked_roles_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_roles` (
	`user_id` INT(10) UNSIGNED NOT NULL,
    `rid` INT(10) NOT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	UNIQUE INDEX `user_id_rid` (`user_id`, `rid`),
    INDEX `FK__roles` (`rid`),
    CONSTRAINT `FK_user_roles_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `FK__roles` FOREIGN KEY (`rid`) REFERENCES `roles` (`rid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `webhooks` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	`model` VARCHAR(50) NULL DEFAULT NULL,
	`event` VARCHAR(10) NULL DEFAULT NULL,
	`condition` VARCHAR(300) NULL DEFAULT NULL,
	`url` VARCHAR(1000) NULL DEFAULT NULL,
	`headers` VARCHAR(1000) NULL DEFAULT NULL,
	`body` VARCHAR(10000) NULL DEFAULT NULL,
	`enabled` TINYINT(1) NULL DEFAULT NULL,
	`order` INT(10) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `web_push_subscriptions` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user_id` INT(10) UNSIGNED NOT NULL,
    `session_id` VARCHAR(255) NULL DEFAULT NULL,
	`endpoint` VARCHAR(500) NOT NULL,
	`auth` VARCHAR(24) NOT NULL,
	`p256dh` VARCHAR(88) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE INDEX `session_id` (`session_id`),
	UNIQUE INDEX `endpoint` (`endpoint`),
	INDEX `FK_web_push_subscriptions_users` (`user_id`),
	CONSTRAINT `FK_web_push_subscriptions_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;