CREATE TABLE users (
	user_id INT(8) NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(30) NOT NULL,
    user_password VARCHAR(255) NOT NULL,
    user_level ENUM('user', 'moderator', 'administrator') NOT NULL DEFAULT 'user',
    user_date_registered DATETIME NOT NULL,
    UNIQUE INDEX user_name_unique (user_name),
	PRIMARY KEY (user_id)
);

CREATE TABLE categories (
	category_id INT(8) NOT NULL AUTO_INCREMENT,
    category_name VARCHAR(255) NOT NULL,
    category_description VARCHAR(255) NOT NULL,
    category_thread_count INT(8) NOT NULL DEFAULT 0,
    category_post_count INT(8) NOT NULL DEFAULT 0,
    UNIQUE INDEX category_name_unique (category_name),
    PRIMARY KEY (category_id)
);

CREATE TABLE threads (
	thread_id INT(8) NOT NULL AUTO_INCREMENT,
	thread_subject VARCHAR(255) NOT NULL,
	thread_date_created DATETIME NOT NULL,
	thread_last_post_date DATETIME NOT NULL,
	thread_category INT(8) NOT NULL,
	thread_author INT(8) NOT NULL,
	PRIMARY KEY (thread_id)
);

CREATE TABLE posts (
	post_id INT(8) NOT NULL AUTO_INCREMENT,
	post_content TEXT NOT NULL,
	post_date_created DATETIME NOT NULL,
	post_date_edited DATETIME,
	post_thread INT(8) NOT NULL,
	post_author INT(8) NOT NULL,
	PRIMARY KEY (post_id)
);

ALTER TABLE threads ADD FOREIGN KEY(thread_category) REFERENCES categories(category_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE threads ADD FOREIGN KEY(thread_author) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE posts ADD FOREIGN KEY(post_thread) REFERENCES threads(thread_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE posts ADD FOREIGN KEY(post_author) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE;