/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE TABLE IF NOT EXISTS `forum_boards` (
    `bid` INT(10) NOT NULL AUTO_INCREMENT,
    `cid` INT(10) NOT NULL,
    `name` VARCHAR(50) DEFAULT NULL,
    `icon` VARCHAR(75) DEFAULT NULL,
    `order` TINYINT(3) DEFAULT NULL,
    PRIMARY KEY (`bid`),
    KEY `FK_forum_boards_forum_categories` (`cid`),
    CONSTRAINT `FK_forum_boards_forum_categories` FOREIGN KEY (`cid`) REFERENCES `forum_categories` (`cid`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `forum_categories` (
    `cid` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `forum_permissions` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `subject` VARCHAR(50) NOT NULL,
    `rid` INT(10) DEFAULT NULL,
    `type` VARCHAR(50) NOT NULL,
    `cid` INT(10) DEFAULT NULL,
    `bid` INT(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `FK_forum_permissions_roles` (`rid`),
    KEY `FK_forum_permissions_forum_categories` (`cid`),
    KEY `FK_forum_permissions_forum_boards` (`bid`),
    CONSTRAINT `FK_forum_permissions_forum_boards` FOREIGN KEY (`bid`) REFERENCES `forum_boards` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_forum_permissions_forum_categories` FOREIGN KEY (`cid`) REFERENCES `forum_categories` (`cid`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_forum_permissions_roles` FOREIGN KEY (`rid`) REFERENCES `roles` (`rid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `forum_posts` (
    `pid` INT(10) NOT NULL AUTO_INCREMENT,
    `tid` INT(10) NOT NULL,
    `reply_to_pid` INT(10) DEFAULT NULL,
	`user_id` INT(10) UNSIGNED NOT NULL,
    `content` MEDIUMTEXT NOT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_edit` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`pid`),
    KEY `FK_forum_posts_forum_threads` (`tid`),
    KEY `FK_forum_posts_forum_posts` (`reply_to_pid`),
    KEY `FK_forum_posts_users` (`user_id`),
    CONSTRAINT `FK_forum_posts_forum_posts` FOREIGN KEY (`reply_to_pid`) REFERENCES `forum_posts` (`pid`) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT `FK_forum_posts_forum_threads` FOREIGN KEY (`tid`) REFERENCES `forum_threads` (`tid`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_forum_posts_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 `forum_reactions` (
    `rid` INT(10) NOT NULL AUTO_INCREMENT,
    `pid` INT(10) NOT NULL,
	`user_id` INT(10) UNSIGNED NOT NULL,
    `rname` VARCHAR(50) NOT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`rid`),
    KEY `FK_forum_reactions_forum_posts` (`pid`),
    KEY `FK_forum_reactions_users` (`user_id`),
    CONSTRAINT `FK_forum_reactions_forum_posts` FOREIGN KEY (`pid`) REFERENCES `forum_posts` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_forum_reactions_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 `forum_shouts` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`user_id` INT(10) UNSIGNED NOT NULL,
	`content` VARCHAR(200) NOT NULL,
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	INDEX `FK_forum_shouts_users` (`user_id`),
	CONSTRAINT `FK_forum_shouts_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `forum_threads` (
    `tid` INT(10) NOT NULL AUTO_INCREMENT,
    `bid` INT(10) NOT NULL,
	`user_id` INT(10) UNSIGNED NOT NULL,
    `topic` VARCHAR(50) NOT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_posted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`last_post_user_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `locked` TINYINT(1) DEFAULT NULL,
    `pinned` TINYINT(1) DEFAULT NULL,
    PRIMARY KEY (`tid`),
    KEY `FK_forum_threads_forum_boards` (`bid`),
    KEY `FK_forum_threads_users` (`user_id`),
    KEY `FK_forum_threads_users_2` (`last_post_user_id`),
    CONSTRAINT `FK_forum_threads_forum_boards` FOREIGN KEY (`bid`) REFERENCES `forum_boards` (`bid`) ON UPDATE CASCADE,
    CONSTRAINT `FK_forum_threads_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_forum_threads_users_2` FOREIGN KEY (`last_post_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `forum_threads_read` (
    `tid` INT(10) NOT NULL,
	`user_id` INT(10) UNSIGNED NOT NULL,
    `timestamp` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE INDEX `user_id, tid` (`user_id`, `tid`),
    KEY `FK_forum_threads_read_forum_threads` (`tid`),
    CONSTRAINT `FK_forum_threads_read_forum_threads` FOREIGN KEY (`tid`) REFERENCES `forum_threads` (`tid`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FK_forum_threads_read_users` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;