CREATE TABLE user_privilege (
	userid					varchar not null,
	is_administrator		boolean not null,
	PRIMARY KEY (userid)
);

CREATE TABLE session (
	id						varchar not null,
	userid     				varchar not null,
	login_time				timestamp not null default now(),
	PRIMARY KEY (id)
);

CREATE TABLE commitfest_status (
	id						integer not null,
	name					varchar not null,
	PRIMARY KEY (id)
);
INSERT INTO commitfest_status VALUES (1, 'Future');		 -- Not ready yet.
INSERT INTO commitfest_status VALUES (2, 'Open');		 -- Submit here.
INSERT INTO commitfest_status VALUES (3, 'In Progress'); -- Review here.
INSERT INTO commitfest_status VALUES (4, 'Closed');		 -- All done.

CREATE TABLE commitfest (
	id						serial,
	name					varchar not null,
	commitfest_status_id	integer not null references commitfest_status (id),
	PRIMARY KEY (id)
);

CREATE TABLE commitfest_topic (
	id						serial,
	commitfest_id			integer not null references commitfest (id),
	name					varchar not null,
	PRIMARY KEY (id)
);
ALTER TABLE commitfest_topic ADD COLUMN sortorder INTEGER NOT NULL DEFAULT 50;

CREATE TABLE patch_status (
	id						integer not null,
	name					varchar not null,
	PRIMARY KEY (id)
);
INSERT INTO patch_status VALUES (1, 'Needs Review');
INSERT INTO patch_status VALUES (2, 'Waiting on Author');
INSERT INTO patch_status VALUES (3, 'Ready for Committer');
INSERT INTO patch_status VALUES (4, 'Committed');
INSERT INTO patch_status VALUES (5, 'Returned with Feedback');
INSERT INTO patch_status VALUES (6, 'Rejected');

CREATE TABLE patch (
	id						serial,
	commitfest_topic_id     integer not null references commitfest_topic (id),
	name					varchar not null,
	patch_status_id			integer not null references patch_status (id),
	author   				varchar not null,
	reviewers				varchar not null,
	date_closed				date,
	creation_time			timestamp with time zone not null default now(),
	PRIMARY KEY (id)
);
ALTER TABLE patch
	ADD COLUMN last_updater varchar,
	ADD COLUMN last_updated_time timestamp with time zone not null
		default now();
ALTER TABLE patch
	ADD COLUMN committer varchar not null default '';

CREATE TABLE patch_comment_type (
	id						integer not null,
	name					varchar not null,
	PRIMARY KEY (id)
);
INSERT INTO patch_comment_type VALUES (1, 'Comment');
INSERT INTO patch_comment_type VALUES (2, 'Patch');
INSERT INTO patch_comment_type VALUES (3, 'Review');

CREATE TABLE patch_comment (
	id						serial,
	patch_id				integer not null references patch (id),
	patch_comment_type_id	integer not null
		references patch_comment_type (id),
	message_id				varchar,
	content					varchar,
	creator					varchar not null,
	creation_time			timestamp with time zone not null default now(),
	PRIMARY KEY (id)
);
ALTER TABLE patch_comment
	ADD COLUMN last_updater varchar,
	ADD COLUMN last_updated_time timestamp with time zone not null
		default now();
