-- Defines the MySQL database tables
--
-- $Id: mysql-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 phpMyAdmin if you have it installed):
--     mysqladmin -p create conferencedb
--     mysql -p conferencedb < mysql-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 = [REDACTED]
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 = [REDACTED]
CREATE TABLE conference (
	id INT AUTO_INCREMENT 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 LONGTEXT NOT NULL DEFAULT '',
	overview LONGTEXT NOT NULL DEFAULT '',
	program LONGTEXT 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 LONGTEXT 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 LONGTEXT 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 LONGTEXT NOT NULL DEFAULT '',
	review_reject LONGTEXT NOT NULL DEFAULT '',
	invited_papers VARCHAR(5) NOT NULL DEFAULT '',
	invited_email LONGTEXT 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 LONGTEXT 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 INT AUTO_INCREMENT 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 LONGTEXT 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 LONGTEXT NOT NULL DEFAULT '',
	paper LONGTEXT 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 LONGTEXT 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,
	fulltext(title),
	fulltext(paper),
	fulltext(first_name, surname, email, affiliation, add_first_names, add_surnames, add_emails, add_affiliations, bio),
	fulltext(abstract),
	fulltext(discipline),
	fulltext(topic),
	fulltext(approach),
	fulltext(coverage),
	fulltext(discipline, topic, sponsor, approach, format, language, relation, coverage)
);

-- use the following line instead if you are using a MySQL version earlier than 3.23.23 (disables fulltext indexes)
-- CREATE TABLE papers (
-- 	cf INT NOT NULL,
-- 	id INT AUTO_INCREMENT 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 LONGTEXT 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 LONGTEXT NOT NULL DEFAULT '',
-- 	paper LONGTEXT 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 LONGTEXT 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
-- );


-- 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 INT AUTO_INCREMENT 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,
	comments LONGTEXT NOT NULL DEFAULT '',
	recommendation LONGTEXT 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 INT AUTO_INCREMENT 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 LONGTEXT 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 INT AUTO_INCREMENT 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 INT AUTO_INCREMENT PRIMARY KEY,
	building VARCHAR(90) NOT NULL DEFAULT '',
	building_short VARCHAR(20) NOT NULL DEFAULT ''
);

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

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

CREATE TABLE scheduler_themes (
	cf INT NOT NULL,
	theme_id INT AUTO_INCREMENT 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 INT AUTO_INCREMENT PRIMARY KEY,
	type VARCHAR(90) NOT NULL DEFAULT '',
	type_short VARCHAR(20) NOT NULL DEFAULT '',
	num_presenters INT NOT NULL DEFAULT 0,
	chair_required TINYINT NOT NULL DEFAULT 0,
	discussant_required TINYINT NOT NULL DEFAULT 0
);

CREATE TABLE scheduler_presentations (
	cf INT NOT NULL,
	present_id INT AUTO_INCREMENT 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 INT AUTO_INCREMENT 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 INT AUTO_INCREMENT 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 ''
);
