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		datetime year to second,
	datemodified	datetime year to second,
	
	PRIMARY KEY (id)
);

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

DROP TABLE userauth;
CREATE TABLE userauth (
	id			char(19),
	username	char(12),
	authid	char(19),
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	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		datetime year to second,
	datemodified	datetime year to second,
	
	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		datetime year to second,
	datemodified	datetime year to second,
	
	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		datetime year to second,
	datemodified	datetime year to second,
	
	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		datetime year to day,
	dateend			datetime year to day,
	budgetdollars	money,
	clientfiscalyear char(10),
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	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),
	projectname		varchar(50),
	description		varchar(255),
	datebegin		datetime year to day,
	dateend			datetime year to day,
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	PRIMARY KEY (id)
);

DROP TABLE budgetproject;
CREATE TABLE budgetproject (
	id					char(19),
	budgetid			char(19),
	projectid		char(19),
	budgetdollars	money,
	billrate			money,
	payrate			money,
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	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		datetime year to day,
	datepaid			datetime year to day,
	status			char(10),
	comment			varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	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),
	rate			money,
	comment		varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	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	money,
	hourlyrate		money,
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	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),
	date			datetime year to day,
	hours			decimal(16),
	hourlyrate	money,
	description	varchar(255),
	denialreason	varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	PRIMARY KEY (id),
	FOREIGN KEY (projectid) REFERENCES project (id),
	FOREIGN KEY (personid) REFERENCES person (id)
);

DROP TABLE expense;
CREATE TABLE expense (
	id					char(19),
	projectid		char(19),
	personid			char(19),
	cost				money,
	description		varchar(255),
	denialreason	varchar(255),
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	PRIMARY KEY (id),
	FOREIGN KEY (projectid) REFERENCES project (id),
	FOREIGN KEY (personid) REFERENCES person (id)
);

DROP TABLE fundedactivity;
CREATE TABLE fundedactivity (
	id					char(19),
	activityid		char(19),	/* work or expense */
	activitytable	char(18),	/* 'work' or 'expense' */
	pbpid				char(19),	/* persbudgproj */
	cwid				char(19),	/* cashwithdrawals ELIMINATE!*/
	amount			money,
	/* bookkeeping */
	userid			char(12),
	dateentered		datetime year to second,
	datemodified	datetime year to second,
	
	PRIMARY KEY (id),
	FOREIGN KEY (pbpid) REFERENCES persbudgproj (id),
	FOREIGN KEY (cwid) REFERENCES cashwithdrawals (id)
);
	
DROP TABLE cashdeposits;
CREATE TABLE cashdeposits (
	id				char(19),
	payee			char(19),
	payor			char(19),
	checknumber	varchar(30),
	date			datetime year to day,
	amount		money,
	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			money,
	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			datetime year to day,
	amount		money,
	comments		varchar(255),
	
	PRIMARY KEY (id),
	FOREIGN KEY (payee) references person (id),
	FOREIGN KEY (payor) references company (id)
);