CREATE TABLE blog_post (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(100),
	nice_name VARCHAR(100),
	body TEXT,
	date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	num_replies INT UNSIGNED NOT NULL DEFAULT 0,
	
	PRIMARY KEY(id),
	FULLTEXT(body),
	INDEX(nice_name)
);

CREATE TABLE blog_tag (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(15) NOT NULL,
	nice_name VARCHAR(15) NOT NULL,
	num_posts INT UNSIGNED NOT NULL DEFAULT 1,
	
	PRIMARY KEY(id),
	UNIQUE(name),
	UNIQUE INDEX(nice_name)
);

CREATE TABLE blog_post_tag (
	post_id INT UNSIGNED NOT NULL,
	tag_id INT UNSIGNED NOT NULL,
	
	PRIMARY KEY(post_id, tag_id),
	FOREIGN KEY(post_id) REFERENCES blog_post(id),
	FOREIGN KEY(tag_id) REFERENCES blog_tag(id)
);

CREATE TABLE blog_comment (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	post_id INT NOT NULL,
	parent_id INT NOT NULL DEFAULT 0,
	body TEXT,
	date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	is_published TINYINT(1) DEFAULT 0,
	profile_id INT UNSIGNED NOT NULL,
	
	PRIMARY KEY(id),
	FOREIGN KEY(post_id) REFERENCES blog_post(id) ON DELETE CASCADE,
	FOREIGN KEY(parent_id) REFERENCES blog_comment(id),
	FOREIGN KEY(profile_id) REFERENCES blog_profile(id),
	INDEX(is_published)
);

CREATE TABLE blog_thread (
	post_id INT UNSIGNED NOT NULL,
	comment_id INT UNSIGNED NOT NULL,
	left_index INT UNSIGNED NOT NULL,
	right_index INT UNSIGNED NOT NULL,
	
	PRIMARY KEY(post_id, comment_id),
	FOREIGN KEY(post_id) REFERENCES blog_post(id) ON DELETE CASCADE,
	FOREIGN KEY(comment_id) REFERENCES blog_comment(id) ON DELETE RESTRICT,
	UNIQUE(post_id, comment_id, left_index),
	UNIQUE(post_id, comment_id, right_index),
	INDEX(left_index),
	INDEX(right_index)
);

CREATE TABLE blog_profile (
	id INT UNSIGNED NOT NULL AUTO_INCREMENT,
	name VARCHAR(20),
	email VARCHAR(30),
	
	PRIMARY KEY(id),
	INDEX(email)
);