
CREATE TABLE IF NOT EXISTS `bans` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `scope` VARCHAR(50) NULL DEFAULT 'server',
    `server` int(11) DEFAULT NULL,
    `offender_steamid` bigint(20) NOT NULL,
    `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires` timestamp NULL DEFAULT NULL,
    `reason` varchar(500) DEFAULT NULL,
    `admin_steamid` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_bans_servers` (`server`),
    CONSTRAINT `FK_bans_servers` FOREIGN KEY (`server`) 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(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(20) DEFAULT NULL,
    `description` varchar(1000) DEFAULT NULL,
    `icon` varchar(25) DEFAULT NULL,
    `order` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `loading_screens` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) DEFAULT NULL,
    `background` varchar(350) DEFAULT NULL,
    `profile_cover` varchar(350) DEFAULT NULL,
    `about_us` varchar(500) DEFAULT NULL,
    `rules` varchar(500) DEFAULT NULL,
    `db_host` varchar(30) DEFAULT NULL,
    `db_port` varchar(10) DEFAULT NULL,
    `db_username` varchar(20) DEFAULT NULL,
    `db_password` varchar(50) DEFAULT NULL,
    `db_darkrp_database` varchar(20) DEFAULT NULL,
    `db_pointshop_database` varchar(20) DEFAULT NULL,
    `db_pointshop2_database` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `navbar_links` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL,
    `icon` varchar(25) DEFAULT NULL,
    `url` varchar(300) 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,
    `steamid` bigint(20) DEFAULT 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`),
    KEY `FK_notifications_users` (`steamid`),
    CONSTRAINT `FK_notifications_users` FOREIGN KEY (`steamid`) REFERENCES `users` (`steamid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `roles` (
    `rid` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    `ingame_equivalent` varchar(50) NOT NULL,
    `color` varchar(6) NULL DEFAULT NULL,
    `icon` varchar(25) 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(11) 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_sync_excluded_servers` (
    `rid` int(11) NOT NULL,
    `server` int(11) NOT NULL,
    KEY `FK_role_sync_excluded_servers_roles` (`rid`),
    KEY `FK_role_sync_excluded_servers_servers` (`server`),
    CONSTRAINT `FK_role_sync_excluded_servers_roles` FOREIGN KEY (`rid`) REFERENCES `roles` (`rid`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_role_sync_excluded_servers_servers` FOREIGN KEY (`server`) REFERENCES `servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `servers` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `token` VARCHAR(50) NULL DEFAULT NULL,
    `name` VARCHAR(30) NOT NULL,
    `game` VARCHAR(10) NOT NULL,
    `address` VARCHAR(30) 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,
    `image` VARCHAR(350) NULL DEFAULT NULL,
    `rules` VARCHAR(3000) NULL DEFAULT NULL,
    `order` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `settings` (
    `setting` varchar(50) NOT NULL,
    `category` varchar(50) NOT NULL,
    `value` varchar(15000) DEFAULT NULL,
    PRIMARY KEY (`setting`),
    UNIQUE KEY `setting_category` (`setting`,`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_credits` (
    `steamid` bigint(20) NOT NULL,
    `credits` mediumint(9) DEFAULT '0',
    PRIMARY KEY (`steamid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_failed_payment_logs` (
    `id` INT(11) 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(11) NOT NULL AUTO_INCREMENT,
    `server` int(11) NOT 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,
    `rid` int(11) DEFAULT NULL,
    `darkrpmoney` int(11) DEFAULT NULL,
    `pspoints` int(11) DEFAULT NULL,
    `ps2points` int(11) DEFAULT NULL,
    `ps2premiumpoints` int(11) DEFAULT NULL,
    `perma_weapons` VARCHAR(1000) NULL DEFAULT NULL,
    `lua` varchar(5000) DEFAULT NULL,
    `expiry_lua` varchar(5000) DEFAULT NULL,
    `commands` varchar(500) DEFAULT NULL,
    `expiry_commands` varchar(500) DEFAULT NULL,
    `image` varchar(350) DEFAULT NULL,
    `description` TEXT NULL DEFAULT NULL,
    `short_description` VARCHAR(1000) NULL DEFAULT NULL,
    `order` INT(10) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_donation_packages_servers` (`server`),
    KEY `FK_store_packages_roles` (`rid`),
    CONSTRAINT `FK_donation_packages_servers` FOREIGN KEY (`server`) REFERENCES `servers` (`id`) ON UPDATE CASCADE,
    CONSTRAINT `FK_store_packages_roles` FOREIGN KEY (`rid`) REFERENCES `roles` (`rid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `store_package_purchases` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `package` int(11) DEFAULT NULL,
    `payment_id` int(11) DEFAULT NULL,
    `package_name` VARCHAR(250) DEFAULT NULL,
	`package_cost_credits` MEDIUMINT(9) DEFAULT NULL,
    `steamid` bigint(20) NOT NULL,
    `redeemed` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    `expired` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
    `purchase_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expiry_timestamp` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_donation_purchases_donation_packages` (`package`),
    KEY `FK_store_package_purchases_store_payments` (`payment_id`),
    CONSTRAINT `FK_donation_purchases_donation_packages` FOREIGN KEY (`package`) 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(11) NOT NULL AUTO_INCREMENT,
    `processor` VARCHAR(50) NOT NULL,
    `processor_id` VARCHAR(100) NOT NULL,
    `steamid` BIGINT(20) NOT NULL,
    `currency` VARCHAR(3) NOT NULL,
    `total` INT(11) 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 `team` (
    `steamid` bigint(50) NOT NULL,
    `nameoverride` varchar(50) DEFAULT NULL,
    `server` int(11) DEFAULT NULL,
    `image` varchar(350) DEFAULT NULL,
    `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `order` INT UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`steamid`),
    KEY `FK_team_servers` (`server`),
    CONSTRAINT `FK_team_servers` FOREIGN KEY (`server`) REFERENCES `servers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `translations` (
    `id` INT(11) 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` (
    `steamid` bigint(20) NOT NULL,
    `name` varchar(50) DEFAULT NULL,
    `discordid` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_online` timestamp NULL DEFAULT NULL,
    `last_played` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`steamid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_revoked_roles` (
    `steamid` bigint(20) NOT NULL,
    `rid` int(11) NOT NULL,
    UNIQUE KEY `steamid_rid` (`steamid`,`rid`),
    KEY `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 (`steamid`) REFERENCES `users` (`steamid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `user_roles` (
    `steamid` bigint(20) NOT NULL,
    `rid` int(11) NOT NULL,
    UNIQUE KEY `steamid_rid` (`steamid`,`rid`),
    KEY `FK__roles` (`rid`),
    CONSTRAINT `FK__roles` FOREIGN KEY (`rid`) REFERENCES `roles` (`rid`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK__users` FOREIGN KEY (`steamid`) REFERENCES `users` (`steamid`) ON DELETE 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;

