-- Defines the PostgreSQL database tables
--
-- $Id: pgsql-tables.sql,v 1.5 2003/09/13 22:50:20 kevin Exp $
--
-- It's recommended you use the install script to create the tables,
-- but you can do it manually with the following commands (or use phpPgAdmin if you have it installed):
--     createdb conferencedb
--     psql conferencedb < pgsql-tables.sql
--
-- if you install manually, make sure the table names in include/db.inc.php
-- match the ones created with this script


-- create the table that will store configuration variables and multiple conference info (if applicable) and create entry with default values (with admin username/password = admin/admin)
CREATE TABLE conference_global (
	multi INT NOT NULL DEFAULT 0,
	baseurl VARCHAR(255) NOT NULL DEFAULT '',
	basedir VARCHAR(255) NOT NULL DEFAULT '',
	tmpdir VARCHAR(255) NOT NULL DEFAULT '',
	repositoryid VARCHAR(60) NOT NULL DEFAULT '',
	defaultfonts VARCHAR(255) NOT NULL DEFAULT '',
	admin_login VARCHAR(32) NOT NULL DEFAULT '',
	admin_password VARCHAR(32) NOT NULL DEFAULT '',
	name VARCHAR(255) NOT NULL DEFAULT '',
	sponsor VARCHAR(255) NOT NULL DEFAULT '',
	sponsor_url VARCHAR(255) NOT NULL DEFAULT '',
	institution VARCHAR(255) NOT NULL DEFAULT '',
	department VARCHAR(255) NOT NULL DEFAULT '',
	address VARCHAR(255) NOT NULL DEFAULT '',
	city VARCHAR(60) NOT NULL DEFAULT '',
	province VARCHAR(60) NOT NULL DEFAULT '',
	country VARCHAR(60) NOT NULL DEFAULT '',
	postal_code VARCHAR(12) NOT NULL DEFAULT '',
	contact_email VARCHAR(60) NOT NULL DEFAULT '',
	intro TEXT NOT NULL DEFAULT '',
	theme VARCHAR(32) NOT NULL DEFAULT '',
	title_image VARCHAR(12) NOT NULL DEFAULT '',
	title_text VARCHAR(255) NOT NULL DEFAULT '',
	logo_image VARCHAR(12) NOT NULL DEFAULT ''
);

INSERT INTO conference_global
	(multi, baseurl, basedir, tmpdir, repositoryid, defaultfonts, admin_login, admin_password, name)
	VALUES
	('0', 'http://www.pkp.ubc.ca/conference/', '/conference/', '/tmp/', 'ocs', 'Verdana,Arial,Geneva,Helvetica,sans-serif', 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Open Conference Systems');


-- create the table that will store the conference data and create initial entry (with admin username/password = admin/admin)
CREATE TABLE conference (
	id SERIAL PRIMARY KEY,
	admin_login VARCHAR(32) NOT NULL DEFAULT '',
	admin_password VARCHAR(32) NOT NULL DEFAULT '',
	name VARCHAR(255) NOT NULL DEFAULT '',
	directors TEXT NOT NULL DEFAULT '',
	director_titles TEXT NOT NULL DEFAULT '',
	director_emails TEXT NOT NULL DEFAULT '',
	contact_name VARCHAR(60) NOT NULL DEFAULT '',
	contact_title VARCHAR(60) NOT NULL DEFAULT '',
	contact_email VARCHAR(60) NOT NULL DEFAULT '',
	contact_address VARCHAR(255) NOT NULL DEFAULT '',
	contact_phone VARCHAR(20) NOT NULL DEFAULT '',
	contact_fax VARCHAR(20) NOT NULL DEFAULT '',
	contact_notify INT NOT NULL DEFAULT 0,
	support_name VARCHAR(60) NOT NULL DEFAULT '',
	support_email VARCHAR(60) NOT NULL DEFAULT '',
	sponsor VARCHAR(255) NOT NULL DEFAULT '',
	sponsor_url VARCHAR(255) NOT NULL DEFAULT '',
	institution VARCHAR(255) NOT NULL DEFAULT '',
	department VARCHAR(255) NOT NULL DEFAULT '',
	address VARCHAR(255) NOT NULL DEFAULT '',
	city VARCHAR(60) NOT NULL DEFAULT '',
	province VARCHAR(60) NOT NULL DEFAULT '',
	country VARCHAR(60) NOT NULL DEFAULT '',
	postal_code VARCHAR(12) NOT NULL DEFAULT '',
	contributors TEXT NOT NULL DEFAULT '',
	contributor_urls TEXT NOT NULL DEFAULT '',
	start_date DATE,
	end_date DATE,
	abstract_deadline DATE,
	paper_deadline DATE,
	accept_deadline DATE,
	deadline_close TEXT NOT NULL DEFAULT '',
	intro TEXT NOT NULL DEFAULT '',
	overview TEXT NOT NULL DEFAULT '',
	program TEXT NOT NULL DEFAULT '',
	call_for_papers VARCHAR(5) NOT NULL DEFAULT '',
	present_single VARCHAR(5) NOT NULL DEFAULT '',
	present_single_length INT NOT NULL DEFAULT 0,
	present_single_time INT NOT NULL DEFAULT 0,
	present_other VARCHAR(5) NOT NULL DEFAULT '',
	present_other_desc TEXT NOT NULL DEFAULT '',
	present_other_length VARCHAR(255) NOT NULL DEFAULT '',
	present_other_time VARCHAR(255) NOT NULL DEFAULT '',
	present_multiple VARCHAR(5) NOT NULL DEFAULT '',
	present_multiple_length INT NOT NULL DEFAULT 0,
	present_multiple_time INT NOT NULL DEFAULT 0,
	call_notice TEXT NOT NULL DEFAULT '',
	ref_distinguish VARCHAR(5) NOT NULL DEFAULT '',
	submission_type VARCHAR(24) NOT NULL DEFAULT '',
	peer_review VARCHAR(12) NOT NULL DEFAULT '',
	review_blind VARCHAR(12) NOT NULL DEFAULT '',
	review_deadline DATE,
	num_reviewers INT NOT NULL DEFAULT 0,
	review_assign VARCHAR(12) NOT NULL DEFAULT '',
	review_email TEXT NOT NULL DEFAULT '',
	review_com VARCHAR(255) NOT NULL DEFAULT '',
	review_rec VARCHAR(255) NOT NULL DEFAULT '',
	review_anon VARCHAR(5) NOT NULL DEFAULT '',
	review_accept TEXT NOT NULL DEFAULT '',
	review_reject TEXT NOT NULL DEFAULT '',
	invited_papers VARCHAR(5) NOT NULL DEFAULT '',
	invited_email TEXT NOT NULL DEFAULT '',
	allow_comments VARCHAR(5) NOT NULL DEFAULT '',
	discussion VARCHAR(5) NOT NULL DEFAULT '',
	discussion_url VARCHAR(255) NOT NULL DEFAULT '',
	discussion_email TEXT NOT NULL DEFAULT '',
	discipline_ex VARCHAR(255) NOT NULL DEFAULT '',
	coverage_geo_ex VARCHAR(255) NOT NULL DEFAULT '',
	coverage_hist_ex VARCHAR(255) NOT NULL DEFAULT '',
	coverage_sample_ex VARCHAR(255) NOT NULL DEFAULT '',
	type_ex VARCHAR(255) NOT NULL DEFAULT '',
	multilingual VARCHAR(5) NOT NULL DEFAULT '',
	language VARCHAR(30) NOT NULL DEFAULT '',
	include_bio VARCHAR(5) NOT NULL DEFAULT '',
	bio_length INT NOT NULL DEFAULT 0,
	paper_access VARCHAR(12) NOT NULL DEFAULT '',
	wordview VARCHAR(255) NOT NULL DEFAULT '',
	copyright_notice TEXT NOT NULL DEFAULT '',
	author_options TEXT NOT NULL DEFAULT '',
	rst VARCHAR(32) NOT NULL DEFAULT '',
	citation_style VARCHAR(5) NOT NULL DEFAULT '',
	related_links VARCHAR(5) NOT NULL DEFAULT '',
	schedule VARCHAR(5) NOT NULL DEFAULT '',
	registration VARCHAR(5) NOT NULL DEFAULT '',
	reg_limit VARCHAR(6) NOT NULL DEFAULT '',
	reg_close_date DATE,
	reg_details TEXT NOT NULL DEFAULT '',
	reg_levels TEXT NOT NULL DEFAULT '',
	reg_fees TEXT NOT NULL DEFAULT '',
	reg_late_date DATE,
	reg_levels_late TEXT NOT NULL DEFAULT '',
	reg_fees_late TEXT NOT NULL DEFAULT '',
	reg_invoice VARCHAR(5) NOT NULL DEFAULT '',
	reg_pay_options TEXT NOT NULL DEFAULT '',
	reg_options TEXT NOT NULL DEFAULT '',
	reg_accomm TEXT NOT NULL DEFAULT '',
	reg_travel TEXT NOT NULL DEFAULT '',
	reg_map VARCHAR(255) NOT NULL DEFAULT '',
	theme VARCHAR(32) NOT NULL DEFAULT '',
	title_image VARCHAR(12) NOT NULL DEFAULT '',
	title_text VARCHAR(255) NOT NULL DEFAULT '',
	logo_image VARCHAR(12) NOT NULL DEFAULT '',
	active INT NOT NULL DEFAULT 0
);

INSERT INTO conference
	(name, admin_login, admin_password, active)
	VALUES
	('Open Conference Systems', 'admin', '21232f297a57a5a743894a0e4a801fc3', '1');


-- create the table that will store papers
CREATE TABLE papers (
 	cf INT NOT NULL,
 	id SERIAL PRIMARY KEY,
 	first_name VARCHAR(60) NOT NULL DEFAULT '',
 	surname VARCHAR(60) NOT NULL DEFAULT '',
 	login VARCHAR(60) NOT NULL DEFAULT '',
 	password VARCHAR(60) NOT NULL DEFAULT '',
 	email VARCHAR(60) NOT NULL DEFAULT '',
 	affiliation VARCHAR(255) NOT NULL DEFAULT '',
 	url VARCHAR(255) NOT NULL DEFAULT '',
 	add_first_names TEXT NOT NULL DEFAULT '',
 	add_surnames TEXT NOT NULL DEFAULT '',
 	add_emails TEXT NOT NULL DEFAULT '',
 	add_affiliations TEXT NOT NULL DEFAULT '',
 	add_urls TEXT NOT NULL DEFAULT '',
 	present_format VARCHAR(255) NOT NULL DEFAULT '',
 	multiple_title VARCHAR(255) NOT NULL DEFAULT '',
 	multiple_org_name VARCHAR(255) NOT NULL DEFAULT '',
 	multiple_org_aff VARCHAR(60) NOT NULL DEFAULT '',
 	multiple_org_email VARCHAR(60) NOT NULL DEFAULT '',
 	multiple_overview TEXT NOT NULL DEFAULT '',
 	multiple_presenters TEXT NOT NULL DEFAULT '',
 	multiple_disc_name VARCHAR(255) NOT NULL DEFAULT '',
 	multiple_disc_aff VARCHAR(60) NOT NULL DEFAULT '',
 	multiple_disc_email VARCHAR(60) NOT NULL DEFAULT '',
 	title VARCHAR(255) NOT NULL DEFAULT '',
 	abstract TEXT NOT NULL DEFAULT '',
 	sponsor TEXT NOT NULL DEFAULT '',
 	discipline VARCHAR(255) NOT NULL DEFAULT '',
 	topic VARCHAR(255) NOT NULL DEFAULT '',
 	approach VARCHAR(255) NOT NULL DEFAULT '',
 	coverage VARCHAR(255) NOT NULL DEFAULT '',
 	format VARCHAR(255) NOT NULL DEFAULT '',
 	bio TEXT NOT NULL DEFAULT '',
 	paper TEXT NOT NULL DEFAULT '',
 	paper_date DATE,
 	language VARCHAR(30) NOT NULL DEFAULT '',
 	relation VARCHAR(255) NOT NULL DEFAULT '',
 	pdf VARCHAR(255) NOT NULL DEFAULT '',
 	appendix_names TEXT NOT NULL DEFAULT '',
 	appendix_dates TEXT NOT NULL DEFAULT '',
 	appendix TEXT NOT NULL DEFAULT '',
 	appendix_pdf TEXT NOT NULL DEFAULT '',
 	comment_email VARCHAR(5) NOT NULL DEFAULT '',
 	paper_email VARCHAR(5) NOT NULL DEFAULT '',
 	delete_paper VARCHAR(5) NOT NULL DEFAULT '',
 	restrict_access VARCHAR(5) NOT NULL DEFAULT '',
 	accepted VARCHAR(12) NOT NULL DEFAULT '',
 	view_reviews VARCHAR(5) NOT NULL DEFAULT '',
 	created DATETIME,
 	timestamp TIMESTAMP NOT NULL DEFAULT NOW()
);


-- create the table that will store invited speakers info
CREATE TABLE invited_speakers (
	cf INT NOT NULL,
	login VARCHAR(60) NOT NULL DEFAULT '',
	password VARCHAR(60) NOT NULL DEFAULT '',
	name VARCHAR(90) NOT NULL DEFAULT '',
	email VARCHAR(60) NOT NULL DEFAULT '',
	affiliation VARCHAR(255) NOT NULL DEFAULT '',
	PRIMARY KEY(cf, login)
);


-- create the table that will store registrants info
CREATE TABLE registrants (
	cf INT NOT NULL,
	id SERIAL PRIMARY KEY,
	name VARCHAR(90) NOT NULL DEFAULT '',
	affiliation VARCHAR(255) NOT NULL DEFAULT '',
	email VARCHAR(60) NOT NULL DEFAULT '',
	phone VARCHAR(20) NOT NULL DEFAULT '',
	fax VARCHAR(20) NOT NULL DEFAULT '',
	address VARCHAR(255) NOT NULL DEFAULT '',
	reg_level VARCHAR(60) NOT NULL DEFAULT '',
	reg_fee VARCHAR(20) NOT NULL DEFAULT '',
	has_paid VARCHAR(5) NOT NULL DEFAULT '',
	reg_email VARCHAR(5) NOT NULL DEFAULT '',
	special_requests TEXT NOT NULL DEFAULT '',
	date_registered DATETIME,
	date_paid DATETIME
);

-- create the table that will store reviewers info
CREATE TABLE reviewers (
	cf INT NOT NULL,
	login VARCHAR(60) NOT NULL DEFAULT '',
	password VARCHAR(60) NOT NULL DEFAULT '',
	name VARCHAR(90) NOT NULL DEFAULT '',
	email VARCHAR(60) NOT NULL DEFAULT '',
	PRIMARY KEY(cf, login)
);

-- create the table that will store reviewer proposals info
CREATE TABLE reviewer_proposals (
	cf INT NOT NULL,
	reviewer VARCHAR(60) NOT NULL DEFAULT '',
	paper INT NOT NULL,
	PRIMARY KEY(cf, reviewer, paper)
);

-- create the table that will store reviews
CREATE TABLE reviews (
	cf INT NOT NULL,
	paper INT NOT NULL,
	reviewer VARCHAR(60) NOT NULL DEFAULT '',
	timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
	comments TEXT NOT NULL DEFAULT '',
	recommendation TEXT NOT NULL DEFAULT '',
	reveal_name VARCHAR(5) NOT NULL DEFAULT '',
	PRIMARY KEY(cf, paper, reviewer)
);


-- create the table that will store paper comments
CREATE TABLE comments (
	cf INT NOT NULL,
	id SERIAL PRIMARY KEY,
	paper INT NOT NULL,
	date DATE,
	author VARCHAR(90) NOT NULL DEFAULT '',
	email VARCHAR(60) NOT NULL DEFAULT '',
	title VARCHAR(255) NOT NULL DEFAULT '',
	comments TEXT NOT NULL DEFAULT ''
);


-- create the table that will store related links and add a gratuitous link to PKP
CREATE TABLE links (
	cf INT NOT NULL,
	id SERIAL PRIMARY KEY,
	title VARCHAR(90) NOT NULL DEFAULT '',
	url VARCHAR(255) NOT NULL DEFAULT '',
	annotation TEXT NOT NULL DEFAULT ''
);

INSERT INTO links 
	(cf, title, url, annotation)
	VALUES
	('1', 'The Public Knowledge Project', 'http://www.pkp.ubc.ca', 'A federally funded research initiative that seeks to improve the scholarly and public quality of academic research through innovative online environments such as this one.');



-- create conference scheduler tables
CREATE TABLE scheduler_locations (
	cf INT NOT NULL,
	location_id SERIAL PRIMARY KEY,
	building VARCHAR(120) NOT NULL DEFAULT '',
	building_short VARCHAR(20) NOT NULL DEFAULT ''
);

CREATE TABLE scheduler_locations_rooms (
	cf INT NOT NULL,
	room_id SERIAL PRIMARY KEY,
	location_id INT NOT NULL,
	room VARCHAR(60) NOT NULL DEFAULT ''
);

CREATE TABLE scheduler_session_times (
	cf INT NOT NULL,
	time_id SERIAL PRIMARY KEY,
	date DATE NOT NULL,
	start_time TIME WITHOUT TIME ZONE NOT NULL,
	end_time TIME WITHOUT TIME ZONE NOT NULL
);

CREATE TABLE scheduler_themes (
	cf INT NOT NULL,
	theme_id SERIAL PRIMARY KEY,
	theme VARCHAR(90) NOT NULL DEFAULT '',
	theme_short VARCHAR(20) NOT NULL DEFAULT ''
);

CREATE TABLE scheduler_session_types (
	cf INT NOT NULL,
	type_id SERIAL PRIMARY KEY,
	type VARCHAR(90) NOT NULL DEFAULT '',
	type_short VARCHAR(20) NOT NULL DEFAULT '',
	num_presenters INT NOT NULL DEFAULT 0,
	chair_required SMALLINT NOT NULL DEFAULT 0,
	discussant_required SMALLINT NOT NULL DEFAULT 0
);

CREATE TABLE scheduler_presentations (
	cf INT NOT NULL,
	present_id SERIAL PRIMARY KEY,
	time INT NOT NULL DEFAULT 0,
	location INT NOT NULL DEFAULT 0,
	room INT NOT NULL DEFAULT 0,
	paper INT NOT NULL DEFAULT 0,
	type INT NOT NULL DEFAULT 0,
	theme INT NOT NULL DEFAULT 0
);

CREATE TABLE scheduler_chairs (
	cf INT NOT NULL,
	chair_id SERIAL PRIMARY KEY,
	time INT NOT NULL DEFAULT 0,
	location INT NOT NULL DEFAULT 0,
	room INT NOT NULL DEFAULT 0,
	chair VARCHAR(255) NOT NULL DEFAULT '',
	chair_affiliation VARCHAR(255) NOT NULL DEFAULT '',
	discussant VARCHAR(255) NOT NULL DEFAULT '',
	discussant_affiliation VARCHAR(255) NOT NULL DEFAULT ''
);

CREATE TABLE scheduler_functions (
	cf INT NOT NULL,
	function_id SERIAL PRIMARY KEY,
	time INT NOT NULL DEFAULT 0,
	location INT NOT NULL DEFAULT 0,
	room INT NOT NULL DEFAULT 0,
	event VARCHAR(255) NOT NULL DEFAULT '',
	description TEXT NOT NULL DEFAULT ''
);
