CREATE TABLE user
(
	user_id
		INTEGER NOT NULL
		PRIMARY KEY
		AUTO_INCREMENT,

	user_name
		VARCHAR(255) NOT NULL,
	user_crypt
		VARCHAR(255) NOT NULL,

	user_email
		VARCHAR(255) NOT NULL,
	user_desc
		TEXT NOT NULL,

	user_isadmin
		BOOL NOT NULL,

	INDEX (user_name)
);

CREATE TABLE patch
(
	patch_id
		INTEGER NOT NULL
		PRIMARY KEY
		AUTO_INCREMENT,

	patch_uid
		VARCHAR(32) NOT NULL,

	patch_status
		ENUM('O', 'D', 'R', 'A') NOT NULL,

	patch_origtext
		MEDIUMTEXT NOT NULL,

	patch_text
		MEDIUMTEXT NOT NULL,

	patch_teaser
		TEXT NOT NULL,

	patch_votecheckok
		INTEGER NOT NULL,

	queue_id INTEGER,
	user_id_owner INTEGER NOT NULL,
	user_id_approver INTEGER NOT NULL,

	INDEX (patch_uid),
	INDEX (patch_status),
	FULLTEXT (patch_text)
);

CREATE TABLE patch_file
(
	patch_id INTEGER NOT NULL,

	patch_file
		VARCHAR(255) NOT NULL,

	INDEX (patch_id),
	INDEX (patch_file)
);

-- CREATE TABLE patch_watcher
-- (
-- 	patch_id INTEGER NOT NULL,
-- 	user_id_watcher INTEGER NOT NULL,
-- 
-- 	INDEX (patch_id),
-- 	INDEX (user_id_watcher)
-- );

CREATE TABLE queue
(
	queue_id
		INTEGER NOT NULL
		PRIMARY KEY
		AUTO_INCREMENT,

	queue_title
		VARCHAR(255) NOT NULL,
	queue_text
		TEXT NOT NULL,

	queue_discarded
		BOOL NOT NULL,

	user_id_owner INTEGER NOT NULL
);

CREATE TABLE journal
(
	journal_id
		INTEGER NOT NULL
		PRIMARY KEY
		AUTO_INCREMENT,

	journal_title
		VARCHAR(255) NOT NULL,
	journal_text
		TEXT NOT NULL,

	journal_discarded
		BOOL NOT NULL,

	user_id_owner INTEGER NOT NULL
);

CREATE TABLE journal_patch (
	journal_id INTEGER NOT NULL,
	patch_id INTEGER NOT NULL,

	INDEX (journal_id),
	INDEX (patch_id)
);

CREATE TABLE msg
(
	msg_id
		INTEGER NOT NULL
		PRIMARY KEY
		AUTO_INCREMENT,

	msg_title
		VARCHAR(255) NOT NULL,
	msg_text
		TEXT NOT NULL,

	user_id INTEGER NOT NULL
);

CREATE TABLE msg_patch (
	msg_id INTEGER NOT NULL,
	patch_id INTEGER NOT NULL,

	INDEX (msg_id),
	INDEX (patch_id)
);

CREATE TABLE msg_queue (
	msg_id INTEGER NOT NULL,
	queue_id INTEGER NOT NULL,

	INDEX (msg_id),
	INDEX (queue_id)
);

CREATE TABLE msg_journal (
	msg_id INTEGER NOT NULL,
	journal_id INTEGER NOT NULL,

	INDEX (msg_id),
	INDEX (journal_id)
);

CREATE TABLE votepattern
(
	votepattern_id
		INTEGER NOT NULL
		PRIMARY KEY
		AUTO_INCREMENT,

	votepattern_title
		VARCHAR(255) NOT NULL,

	votepattern_minvotes
		INTEGER NOT NULL
);

CREATE TABLE votepattern_regex
(
	votepattern_id INTEGER NOT NULL,

	votepattern_regex
		VARCHAR(255) NOT NULL,

	INDEX (votepattern_id)
);

CREATE TABLE votepattern_user
(
	votepattern_id INTEGER NOT NULL,
	user_id INTEGER NOT NULL,

	INDEX (votepattern_id),
	INDEX (user_id)
);

CREATE TABLE vote
(
	vote_id
		INTEGER NOT NULL
		PRIMARY KEY
		AUTO_INCREMENT,

	vote_type
		ENUM('+', '-') NOT NULL,
	vote_details
		VARCHAR(255) NOT NULL,

	user_id INTEGER NOT NULL,
	patch_id INTEGER NOT NULL,

	INDEX (patch_id)
);