-- add posts column in user table
ALTER TABLE wcf1_user ADD wbbPosts INT(10) NOT NULL DEFAULT 0;
ALTER TABLE wcf1_user ADD INDEX wbbPosts (wbbPosts);

ALTER TABLE wcf1_user ADD wbbBestAnswers INT(10) NOT NULL DEFAULT 0;

-- add thread id column in article content table
ALTER TABLE wcf1_article_content ADD articleThreadID INT(10);

DROP TABLE IF EXISTS wbb1_board;
CREATE TABLE wbb1_board (
	-- general
	boardID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	parentID INT(10),
	position SMALLINT(5) NOT NULL DEFAULT 0,
	boardType TINYINT(1) NOT NULL DEFAULT 0,
	title VARCHAR(255) NOT NULL DEFAULT '',
	description MEDIUMTEXT,
	descriptionUseHtml TINYINT(1) NOT NULL DEFAULT 0,
	externalURL VARCHAR(255) NOT NULL DEFAULT '',
	time INT(10) NOT NULL DEFAULT 0,
	metaDescription VARCHAR(255) NOT NULL DEFAULT '',

	-- properties
	countUserPosts TINYINT(1) NOT NULL DEFAULT 1,
	daysPrune SMALLINT(5) NOT NULL DEFAULT 0,
	enableMarkingAsDone TINYINT(1) NOT NULL DEFAULT 0,
	ignorable TINYINT(1) NOT NULL DEFAULT 1,
	isClosed TINYINT(1) NOT NULL DEFAULT 0,
	isInvisible TINYINT(1) NOT NULL DEFAULT 0,
	isPrivate TINYINT(1) NOT NULL DEFAULT 0,
	postSortOrder VARCHAR(4) NOT NULL DEFAULT '',
	postsPerPage SMALLINT(5) NOT NULL DEFAULT 0,
	searchable TINYINT(1) NOT NULL DEFAULT 1,
	searchableForSimilarThreads TINYINT(1) NOT NULL DEFAULT 1,
	showSubBoards TINYINT(1) NOT NULL DEFAULT 1,
	sortField VARCHAR(20) NOT NULL DEFAULT '',
	sortOrder VARCHAR(4) NOT NULL DEFAULT '',
	styleID INT(10),
	threadsPerPage SMALLINT(5) NOT NULL DEFAULT 0,
	enableBestAnswer TINYINT(1) NOT NULL DEFAULT 0,
	formID INT(10),

	-- statistics
	clicks INT(10) NOT NULL DEFAULT 0,
	posts INT(10) NOT NULL DEFAULT 0,
	threads INT(10) NOT NULL DEFAULT 0,

	-- board icon
	iconData TEXT NULL,

	UNIQUE KEY (parentID, boardID)
);

DROP TABLE IF EXISTS wbb1_board_last_post;
CREATE TABLE wbb1_board_last_post (
	boardID INT(10) NOT NULL,
	languageID INT(10),
	threadID INT(10) NOT NULL,
	UNIQUE KEY (boardID, languageID)
);

DROP TABLE IF EXISTS wbb1_board_user_status;
CREATE TABLE wbb1_board_user_status (
	userID INT(10) NOT NULL,
	boardID INT(10) NOT NULL,
	status VARCHAR(255), -- 'ignored', 'watched', 'lurked'
	PRIMARY KEY (userID, boardID),
	KEY userID_status (userID, status)
);

DROP TABLE IF EXISTS wbb1_post;
CREATE TABLE wbb1_post (
	postID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	threadID INT(10) NOT NULL,
	userID INT(10),
	username VARCHAR(255) NOT NULL DEFAULT '',
	subject VARCHAR(255) NOT NULL DEFAULT '',
	message MEDIUMTEXT NOT NULL,
	time INT(10) NOT NULL DEFAULT 0,

	isDeleted TINYINT(1) NOT NULL DEFAULT 0,
	isDisabled TINYINT(1) NOT NULL DEFAULT 0,
	isClosed TINYINT(1) NOT NULL DEFAULT 0,
	isOfficial TINYINT(1) NOT NULL DEFAULT 0,
	editorID INT(10),
	editor VARCHAR(255) NOT NULL DEFAULT '',
	lastEditTime INT(10) NOT NULL DEFAULT 0,
	editCount MEDIUMINT(7) NOT NULL DEFAULT 0,
	editReason TEXT,
	lastVersionTime INT(10) NOT NULL DEFAULT 0,
	attachments SMALLINT(5) NOT NULL DEFAULT 0,
	pollID INT(10),
	enableHtml TINYINT(1) NOT NULL DEFAULT 0,
	ipAddress VARCHAR(39) NOT NULL DEFAULT '',
	cumulativeLikes MEDIUMINT(7) NOT NULL DEFAULT 0,
	deleteTime INT(10) NOT NULL DEFAULT 0,
	enableTime INT(10) NOT NULL DEFAULT 0,
	hasEmbeddedObjects TINYINT(1) NOT NULL DEFAULT 0,
	KEY (threadID, userID),
	KEY threadID_2 (threadID, isDeleted, isDisabled, time),
	-- Slightly different name, has already been created in some very large sites before.
	KEY thread_3 (threadID, isDisabled, userID, time),
	KEY userToPost (userID, isDeleted, isDisabled, threadID),
	KEY (isDeleted),
	KEY (isDisabled),
	KEY (ipAddress),
	KEY (time),
	KEY (enableTime),
	KEY isOfficial (threadID, isOfficial)
);

DROP TABLE IF EXISTS wbb1_rss_feed;
CREATE TABLE wbb1_rss_feed (
	feedID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255) NOT NULL DEFAULT '',
	url TEXT,
	isDisabled TINYINT(1) NOT NULL DEFAULT 0,
	lastRun INT(10) NOT NULL DEFAULT 0,
	cycleTime MEDIUMINT(5) NOT NULL DEFAULT 1800,
	maxResults SMALLINT(5) NOT NULL DEFAULT 0,
	searchKeywords TEXT,
	boardID INT(10),
	userID INT(10),
	languageID INT(10),
	closeThread TINYINT(1) NOT NULL DEFAULT 0,
	disableThread TINYINT(1) NOT NULL DEFAULT 0,
	threadTags TEXT,
	useCategoriesAsTags TINYINT(1) NOT NULL DEFAULT 0,
	errorMessage TEXT
);

DROP TABLE IF EXISTS wbb1_rss_feed_log;
CREATE TABLE wbb1_rss_feed_log (
	feedID INT(10) NOT NULL,
	hash VARCHAR(40) NOT NULL DEFAULT '',
	threadID INT(10),

	UNIQUE KEY (feedID, hash)
);

DROP TABLE IF EXISTS wbb1_thread;
CREATE TABLE wbb1_thread (
	threadID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	boardID INT(10) NOT NULL,
	languageID INT(10),
	topic VARCHAR(255) NOT NULL DEFAULT '',
	firstPostID INT(10),
	time INT(10) NOT NULL DEFAULT 0,
	userID INT(10),
	username VARCHAR(255) NOT NULL DEFAULT '',
	lastPostID INT(10),
	lastPostTime INT(10) NOT NULL DEFAULT 0,
	lastPosterID INT(10),
	lastPoster VARCHAR(255) NOT NULL DEFAULT '',
	replies INT(10) NOT NULL DEFAULT 0,
	views INT(10) NOT NULL DEFAULT 0,
	attachments INT(10) NOT NULL DEFAULT 0,
	polls INT(10) NOT NULL DEFAULT 0,
	isAnnouncement TINYINT(1) NOT NULL DEFAULT 0,
	isSticky TINYINT(1) NOT NULL DEFAULT 0,
	isDisabled TINYINT(1) NOT NULL DEFAULT 0,
	isClosed TINYINT(1) NOT NULL DEFAULT 0,
	isDeleted TINYINT(1) NOT NULL DEFAULT 0,
	movedThreadID INT(10),
	movedTime INT(10) NOT NULL DEFAULT 0,
	isDone TINYINT(1) NOT NULL DEFAULT 0,
	cumulativeLikes MEDIUMINT(7) NOT NULL DEFAULT 0,
	hasLabels TINYINT(1) NOT NULL DEFAULT 0,
	deleteTime INT(10) NOT NULL DEFAULT 0,
	bestAnswerPostID INT(10),

	KEY (lastPostTime),
	KEY (boardID, isAnnouncement, isSticky, lastPostTime, isDeleted, isDisabled),
	KEY (boardID, isDeleted, isDisabled, movedThreadID),
	KEY (isDeleted),
	KEY (isDisabled),
	KEY (movedTime),
	KEY privateLastPost (boardID, isDeleted, isDisabled, movedThreadID, userID, lastPostTime, languageID)
);

DROP TABLE IF EXISTS wbb1_thread_announcement;
CREATE TABLE wbb1_thread_announcement (
	boardID INT(10) NOT NULL,
	threadID INT(10) NOT NULL,
	PRIMARY KEY (boardID, threadID)
);

DROP TABLE IF EXISTS wbb1_thread_form;
CREATE TABLE wbb1_thread_form (
	formID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title VARCHAR(255) NOT NULL DEFAULT ''
);

DROP TABLE IF EXISTS wbb1_thread_form_option;
CREATE TABLE wbb1_thread_form_option (
	optionID INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	optionTitle VARCHAR(255) NOT NULL DEFAULT '',
	optionDescription TEXT,
	optionType VARCHAR(255) NOT NULL DEFAULT '',
	defaultValue MEDIUMTEXT,
	validationPattern TEXT,
	selectOptions MEDIUMTEXT,
	required TINYINT(1) NOT NULL DEFAULT 0,
	showOrder INT(10) NOT NULL DEFAULT 0,
	isDisabled TINYINT(1) NOT NULL DEFAULT 0,
	formID INT(10),
	tmpHash VARCHAR(40) DEFAULT NULL,
	timeCreated INT(10)
);

DROP TABLE IF EXISTS wbb1_thread_form_option_value;
CREATE TABLE wbb1_thread_form_option_value (
	postID INT(10) NOT NULL,
	optionID INT(10) NOT NULL,
	optionValue MEDIUMTEXT NOT NULL,
	UNIQUE KEY (postID, optionID)
);

DROP TABLE IF EXISTS wbb1_thread_similar;
CREATE TABLE wbb1_thread_similar (
	threadID INT(10) NOT NULL,
	similarThreadID INT(10) NOT NULL,
	UNIQUE KEY (threadID, similarThreadID)
);

DROP TABLE IF EXISTS wbb1_thread_user_status;
CREATE TABLE wbb1_thread_user_status (
	userID INT(10) NOT NULL,
	threadID INT(10) NOT NULL,
	status VARCHAR(255), -- 'ignored', 'watched'
	PRIMARY KEY (userID, threadID),
	KEY userID_status (userID, status)
);

ALTER TABLE wbb1_board ADD FOREIGN KEY (styleID) REFERENCES wcf1_style (styleID) ON DELETE SET NULL;
ALTER TABLE wbb1_board ADD FOREIGN KEY (parentID) REFERENCES wbb1_board (boardID) ON DELETE SET NULL;
ALTER TABLE wbb1_board ADD FOREIGN KEY (formID) REFERENCES wbb1_thread_form (formID) ON DELETE SET NULL;

ALTER TABLE wbb1_board_last_post ADD FOREIGN KEY (boardID) REFERENCES wbb1_board (boardID) ON DELETE CASCADE;
ALTER TABLE wbb1_board_last_post ADD FOREIGN KEY (languageID) REFERENCES wcf1_language (languageID) ON DELETE CASCADE;
ALTER TABLE wbb1_board_last_post ADD FOREIGN KEY (threadID) REFERENCES wbb1_thread (threadID) ON DELETE CASCADE;

ALTER TABLE wbb1_board_user_status ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE CASCADE;
ALTER TABLE wbb1_board_user_status ADD FOREIGN KEY (boardID) REFERENCES wbb1_board (boardID) ON DELETE CASCADE;

ALTER TABLE wbb1_post ADD FOREIGN KEY (threadID) REFERENCES wbb1_thread (threadID) ON DELETE CASCADE;
ALTER TABLE wbb1_post ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE SET NULL;
ALTER TABLE wbb1_post ADD FOREIGN KEY (editorID) REFERENCES wcf1_user (userID) ON DELETE SET NULL;
ALTER TABLE wbb1_post ADD FOREIGN KEY (pollID) REFERENCES wcf1_poll (pollID) ON DELETE SET NULL;

ALTER TABLE wbb1_rss_feed ADD FOREIGN KEY (boardID) REFERENCES wbb1_board (boardID) ON DELETE SET NULL;
ALTER TABLE wbb1_rss_feed ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE SET NULL;
ALTER TABLE wbb1_rss_feed ADD FOREIGN KEY (languageID) REFERENCES wcf1_language (languageID) ON DELETE SET NULL;

ALTER TABLE wbb1_rss_feed_log ADD FOREIGN KEY (feedID) REFERENCES wbb1_rss_feed (feedID) ON DELETE CASCADE;
ALTER TABLE wbb1_rss_feed_log ADD FOREIGN KEY (threadID) REFERENCES wbb1_thread (threadID) ON DELETE SET NULL;

ALTER TABLE wbb1_thread ADD FOREIGN KEY (boardID) REFERENCES wbb1_board (boardID) ON DELETE CASCADE;
ALTER TABLE wbb1_thread ADD FOREIGN KEY (languageID) REFERENCES wcf1_language (languageID) ON DELETE SET NULL;
ALTER TABLE wbb1_thread ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE SET NULL;
ALTER TABLE wbb1_thread ADD FOREIGN KEY (lastPosterID) REFERENCES wcf1_user (userID) ON DELETE SET NULL;
ALTER TABLE wbb1_thread ADD FOREIGN KEY (movedThreadID) REFERENCES wbb1_thread (threadID) ON DELETE CASCADE;
ALTER TABLE wbb1_thread ADD FOREIGN KEY (bestAnswerPostID) REFERENCES wbb1_post (postID) ON DELETE SET NULL;

ALTER TABLE wbb1_thread_form_option ADD FOREIGN KEY (formID) REFERENCES wbb1_thread_form (formID) ON DELETE CASCADE;

ALTER TABLE wbb1_thread_form_option_value ADD FOREIGN KEY (postID) REFERENCES wbb1_post (postID) ON DELETE CASCADE;
ALTER TABLE wbb1_thread_form_option_value ADD FOREIGN KEY (optionID) REFERENCES wbb1_thread_form_option (optionID) ON DELETE CASCADE;

ALTER TABLE wbb1_thread_announcement ADD FOREIGN KEY (boardID) REFERENCES wbb1_board (boardID) ON DELETE CASCADE;
ALTER TABLE wbb1_thread_announcement ADD FOREIGN KEY (threadID) REFERENCES wbb1_thread (threadID) ON DELETE CASCADE;

ALTER TABLE wbb1_thread_similar ADD FOREIGN KEY (threadID) REFERENCES wbb1_thread (threadID) ON DELETE CASCADE;
ALTER TABLE wbb1_thread_similar ADD FOREIGN KEY (similarThreadID) REFERENCES wbb1_thread (threadID) ON DELETE CASCADE;

ALTER TABLE wbb1_thread_user_status ADD FOREIGN KEY (userID) REFERENCES wcf1_user (userID) ON DELETE CASCADE;
ALTER TABLE wbb1_thread_user_status ADD FOREIGN KEY (threadID) REFERENCES wbb1_thread (threadID) ON DELETE CASCADE;

ALTER TABLE wcf1_article_content ADD FOREIGN KEY (articleThreadID) REFERENCES wbb1_thread (threadID) ON DELETE SET NULL;

-- default values
INSERT INTO	wbb1_board
		(boardID, boardType, title, description, time)
VALUES		(1, 1, 'Main Category', '', UNIX_TIMESTAMP());
INSERT INTO	wbb1_board
		(boardID, parentID, title, description, time)
VALUES		(2, 1, 'Default Forum', 'This is the description of the default forum.', UNIX_TIMESTAMP());
