DROP TABLE domain;
CREATE TABLE domain (			/* For lookup values */
	id				char(19),
	filter		varchar(40),
	value			varchar(50),
	description		varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id)
);

DROP TABLE users;
CREATE TABLE users (
	username		char(12),
	password		char(12),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (username)
);

DROP TABLE userauth;
CREATE TABLE userauth (
	id			char(19),
	username	char(12),
	authid	char(19),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (username) REFERENCES users (username),
	FOREIGN KEY (authid) REFERENCES domain (id)
);

DROP TABLE person;
CREATE TABLE person (
	id				char(19),
	firstname	varchar(25),
	lastname		varchar(25),
	mailaddress	varchar(255),
	phone			varchar(20),
	fax			varchar(20),
	cell			varchar(20),
	pager			varchar(20),
	email1		varchar(50),
	email2		varchar(50),
	taxid			char(11),
	username		char(12),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id)
);

DROP TABLE company;
CREATE TABLE company (
	id				char(19),
	companyname	varchar(50),
	abbrev		char(6),
	division		varchar(50),
	mailaddress	varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id)
);

DROP TABLE companyperson;
CREATE TABLE companyperson (
	id				char(19),
	companyid	char(19),
	personid		char(19),
	roleid		char(19),	/* standard role */
	customrole	varchar(100),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (companyid) REFERENCES company (id),
	FOREIGN KEY (personid) REFERENCES person (id),
	FOREIGN KEY (roleid) REFERENCES domain (id)
);

DROP TABLE budget;
CREATE TABLE budget (
	id					char(19),
	contractno		varchar(19),
	clientcompanyid	char(19),
	contractedcoid	char(19),
	description		varchar(255),
	datebegin		date,
	dateend			date,
	budgetdollars	decimal(12,2),
	clientfiscalyear char(10),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (clientcompanyid) REFERENCES company (id),
	FOREIGN KEY (contractedcoid) REFERENCES company (id)
);

DROP TABLE project;
CREATE TABLE project (
	id					char(19),
	parentid			char(19),
	parenttable		char(18),
	projectname		varchar(50),
	description		varchar(255),
	datebegin		date,
	dateend			date,
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id)
);

DROP TABLE budgetproject;
CREATE TABLE budgetproject (
	id					char(19),
	budgetid			char(19),
	projectid		char(19),
	budgetdollars	decimal(12,2),
	billrate			decimal(12,2),
	payrate			decimal(12,2),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (budgetid) REFERENCES budget (id),
	FOREIGN KEY (projectid) REFERENCES project (id)
);

DROP TABLE accountsrecv;
CREATE TABLE accountsrecv (
	id					char(19),
	invoicenumber	char(19),
	budgetid			char(19),
	datebilled		date,
	datepaid			date,
	status			char(10),
	comment			varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (budgetid) REFERENCES budget (id)
);

DROP TABLE invoicelineitem;
CREATE TABLE invoicelineitem (
	id				char(19),
	invoiceid	char(19),
	projectid	char(19),
	dates			varchar(30),
	hours			decimal(16,2),
	rate			decimal(12,2),
	comment			varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (invoiceid) REFERENCES accountsrecv (id),
	FOREIGN KEY (projectid)	REFERENCES project(id)
);

DROP TABLE persbudgproj;
CREATE TABLE persbudgproj (
	id				char(19),
	budgprojid	char(19),
	personid		char(19),
	budgetdollars	decimal(12,2),
	hourlyrate		decimal(12,2),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (budgprojid) REFERENCES budgetproject (id),
	FOREIGN KEY (personid) REFERENCES person (id)
);

DROP TABLE work;
CREATE TABLE work (
	id					char(19),
	projectid		char(19),
	personid			char(19),
	companyid		char(19),	/* contracted company */
	date				date,
	hours				decimal(16,2),
	hourlyrate		decimal(12,2),
	description		text,
	denialreason	varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (projectid) REFERENCES project (id),
	FOREIGN KEY (personid) REFERENCES person (id),
	FOREIGN KEY (companyid) REFERENCES company (id)
);

DROP TABLE expense;
CREATE TABLE expense (
	id				char(19),
	projectid	char(19),
	personid		char(19),
	cost			decimal(12,2),
	description	varchar(255),
	denialreason	varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (projectid) REFERENCES project (id),
	FOREIGN KEY (personid) REFERENCES person (id)
);

DROP TABLE cashdeposits;
CREATE TABLE cashdeposits (
	id				char(19),
	payee			char(19),
	payor			char(19),
	checknumber	varchar(30),
	date			date,
	amount		decimal(12,2),
	comments		varchar(255),
	
	PRIMARY KEY (id),
	FOREIGN KEY (payee) references company (id),
	FOREIGN KEY (payor)	references company (id)
);

DROP TABLE cashdeplineitem;
CREATE TABLE cashdeplineitem (
	id					char(19),
	cashdepositid	char(19),
	amount			decimal(12,2),
	invoiceid		char(19),
	comments			varchar(255),
	
	PRIMARY KEY (id),
	FOREIGN KEY (cashdepositid) references cashdeposits (id),
	FOREIGN KEY (invoiceid) references accountsrecv (id)
);

DROP TABLE cashwithdrawals;
CREATE TABLE cashwithdrawals (
	id				char(19),
	payee			char(19),
	payor			char(19),
	checknumber	varchar(30),
	date			date,
	amount		decimal(12,2),
	comments		varchar(255),
	
	PRIMARY KEY (id),
	FOREIGN KEY (payee) references person (id),
	FOREIGN KEY (payor) references company (id)
);
	
DROP TABLE bug;
CREATE TABLE bug (
	id					char(19),
	projectid		char(19),
	bugno				decimal(12,2),
	dupbugid			char(19),
	description		varchar(255),
	statusid			char(19),	/* domain, filter='bugstatus' */
	assignedtoid	char(19),	/* companyperson */
	priority			integer,
	duedate			date,
	
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (projectid) references project (id),
	FOREIGN KEY (dupbugid) references bug (id),
	FOREIGN KEY (statusid) references domain (id),
	FOREIGN KEY (assignedtoid) references companyperson (id)
);
	
DROP TABLE buglog;
CREATE TABLE buglog (
	id				char(19),
	bugid			char(19),
	contents		text,
	
	/* bookkeeping */
	userid			char(12),
	dateentered		timestamp,
	datemodified	timestamp,
	
	PRIMARY KEY (id),
	FOREIGN KEY (bugid) references bug (id)
);