# represents a single user
CREATE TABLE user (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,	
	alias VARCHAR(20) NOT NULL,
	password CHAR(32) NOT NULL,
	email VARCHAR(50) NOT NULL,
	is_deleted TINYINT(1) NOT NULL DEFAULT 0,
	
	PRIMARY KEY(id),
	INDEX(password),
	INDEX(is_deleted)
);

# session
CREATE TABLE session (
	id CHAR(32) NOT NULL,
	is_using_cookie TINYINT(1) NOT NULL DEFAULT 1,
	date_expire TIMESTAMP,
	
	PRIMARY KEY(id)
);

# link users to session, ie: logged in users
CREATE TABLE user_session (
	user_id INT UNSIGNED NOT NULL,
	session_id CHAR(32) NOT NULL,
	
	PRIMARY KEY(user_id, session_id),
	FOREIGN KEY(user_id) REFERENCES user(id) ON DELETE CASCADE,
	FOREIGN KEY(session_id) REFERENCES session(id) ON DELETE CASCADE,
	UNIQUE INDEX(user_id),
	UNIQUE INDEX(session_id)
);

# a message, this represents topics, replies, and private messages. messages
# can only be created by one user; however, in the example of private messages,
# they can be linked to many users, giving those users 'viewing' priviledge
# for those messages.
CREATE TABLE message (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	user_id INT UNSIGNED NOT NULL,
	subject VARCHAR(150),
	
	date_created TIMESTAMP,
	date_updated TIMESTAMP,
	
	PRIMARY KEY(id),
	FOREIGN KEY(user_id) REFERENCES user(id) ON DELETE RESTRICT,
	FULLTEXT(subject),
	INDEX(user_id),
	INDEX(date_created)
);

# message body, separated from message so that reads don't get message text
# and so most writes/updates only affect this table
CREATE TABLE message_body (
	message_id INT UNSIGNED NOT NULL,
	body TEXT,
	
	PRIMARY KEY(message_id),
	FOREIGN KEY(message_id) REFERENCES message(id) ON DELETE CASCADE,
	FULLTEXT(body)
);

# represents categories / forums, a lot like tags
CREATE TABLE category (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	
	title VARCHAR(150) DEFAULT '',
	description TEXT,
	num_threads INT NOT NULL DEFAULT 0, # derivable
	num_strings INT NOT NULL DEFAULT 0,
	left_index INT UNSIGNED NOT NULL,
	right_index INT UNSIGNED NOT NULL,
	is_deleted TINYINT(1) NOT NULL DEFAULT 0,
	
	PRIMARY KEY(id),
	UNIQUE INDEX(left_index),
	UNIQUE INDEX(right_index),
	INDEX(is_deleted)
);

# a string is a message inside a thread, the unique keys give this enough
# information to maintain an nested set hierarchy
CREATE TABLE string (
	category_id INT UNSIGNED NOT NULL,
	message_id INT UNSIGNED NOT NULL,
	left_index INT UNSIGNED NOT NULL,
	right_index INT UNSIGNED NOT NULL,
	is_deleted TINYINT(1) NOT NULL DEFAULT 0,
	
	PRIMARY KEY(category_id, message_id),
	FOREIGN KEY(category_id) REFERENCES category(id) ON DELETE RESTRICT,
	FOREIGN KEY(message_id) REFERENCES message(id) ON DELETE RESTRICT,
	UNIQUE(category_id, message_id, left_index),
	UNIQUE(category_id, message_id, right_index),
	INDEX(is_deleted),
	INDEX(left_index),
	INDEX(right_index)
);

# represents uploaded / attached files
CREATE TABLE file (
	id CHAR(32) NOT NULL, # file hash
	size INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	date_created TIMESTAMP,
	
	PRIMARY KEY(id)
);

# links (private messages / topics / replies) and their file attachments
CREATE TABLE message_file (
	file_id CHAR(32) NOT NULL,
	message_id INT UNSIGNED NOT NULL,
	
	PRIMARY KEY(file_id, message_id),
	FOREIGN KEY(file_id) REFERENCES file(id) ON DELETE CASCADE,
	FOREIGN KEY(message_id) REFERENCES message(id) ON DELETE CASCADE
);

# this gives multiple users access to a message, this isn't as important of a
# feature.
CREATE TABLE private_message (
	user_id INT UNSIGNED NOT NULL,
	message_id INT UNSIGNED NOT NULL,
	is_blind TINYINT(1) DEFAULT 0, # in the sense of blind carbon copy
	
	PRIMARY KEY(user_id, message_id),
	FOREIGN KEY(user_id) REFERENCES user(id) ON DELETE RESTRICT,
	FOREIGN KEY(message_id) REFERENCES message(id) ON DELETE RESTRICT
);

# emoticons available for use when posting, not super important, but makes use
# of this existing file table
CREATE TABLE emoticon (
	code CHAR(5) NOT NULL,
	file_id CHAR(32) NOT NULL,
	
	PRIMARY KEY(code),
	FOREIGN KEY(file_id) REFERENCES file(id) ON DELETE RESTRICT
);

# messages that have absolutely been seen by a user. the way unseen messages
# are tracked is by checking if a semi recent message (within several days)
# is not in the results of viewed messages. Viewed message tuples are pruned
# in terms of that offset of several days
CREATE TABLE viewed_message (
	user_id INT UNSIGNED NOT NULL,
	message_id INT UNSIGNED NOT NULL,
	date_viewed TIMESTAMP,
	
	PRIMARY KEY(user_id, message_id),
	FOREIGN KEY(user_id) REFERENCES user(id) ON DELETE CASCADE,
	FOREIGN KEY(message_id) REFERENCES message(id) ON DELETE CASCADE
);

# view for threads, this gives more meaning to strings and hides the details
# of mptt from the developer

# this view gets all thread info exlcuding message body text
CREATE VIEW thread_title_list AS SELECT m.*, u.alias AS user_alias, s.category_id, COALESCE(vm.date_viewed, 0) AS date_read  
FROM message m LEFT JOIN viewed_message vm ON m.id=vm.message_id, string s, category c, user u 
WHERE m.id=s.message_id AND s.category_id=c.id AND (s.is_deleted+c.is_deleted+u.is_deleted)=0 AND u.id=m.user_id 
GROUP BY s.category_id ORDER BY s.left_index ASC;

# this view gets all thread info
CREATE VIEW thread_content_list AS SELECT m.*, mb.body, u.alias AS user_alias, s.category_id 
FROM message m, message_body mb, string s, category c, user u 
WHERE m.id=mb.message_id AND m.id=s.message_id AND s.category_id=c.id AND (s.is_deleted+c.is_deleted+u.is_deleted)=0 AND u.id=m.user_id 
GROUP BY s.category_id ORDER BY s.left_index ASC;

# view for categories that hides the mptt details from the developer
CREATE VIEW category_list AS SELECT * FROM category ORDER BY left_index ASC;