CREATE TABLE notification (			// table of who should be notified for actions on a job (not implemented)
	fkjob_id		int,		// the job id number (relates to job_id in job table)
	fkpersonnel_id		text,		// the personnel id number (relates to personnel_id in personnel table)
	constraint notification_pkey primary key ( fkjob_id, fkpersonnel_id )
  );


CREATE TABLE client (				// list of all clients with open or closed jobs
	client_id		int,		// client id (generated externally from &timesheet;)
	client_name		text not null,	// the client's full name as it should appear on reports
	default_bill_rate	money,		// the default billing rate for the client
	default_approved	int,		// if automatically approved, 0 = no, 1 = yes
	constraint client_pkey primary key ( client_id )
  );


CREATE TABLE personnel (			// list of all personnel, employees and supervisors who use timesheet
	personnel_id		text,		// the logon id for the user
	first_name		text,		// user's first name
	middle_name		text,		// user's middle initial
	last_name		text,		// user's last name
	email			text,		// e-mail address
	super_user		text,		// if they have supervisor status or not, 0 = no, 1 = yes
	constraint personnel_pkey primary key ( personnel_id )
  );


CREATE TABLE hours (				// list of all logged, approved and deleted hours in &timesheet;
	hours_id		int,		// id of the hours being logged, generated internally by &timesheet;
	fkjob_id		int not null,	// the job's id as relates to the job in the job table
	fkclient_id		int not null,	// the client's id as relates to the client in the client table
	fkpersonnel_id		text not null,	// the user's id as it relates to the personnel_id in the personnel table
	date_entered		date,		// date the user logged the hours, generated by &timesheet; as the time the entry is made
	intime			text,		// date the user attributes the hours worked 
	time_in			text,		// time the hours entry started
	time_out		text,		// time the hours entry stopped
	total_hours		real not null,	// total hours for a particular entry
	billable		int,		// should this be billable to the client, 0 = no, 1 = yes
	del			text,		// is this a deleted hours entry, 0 = no, 1 = yes
	hours_description	text,		// the description of the hours worked
	comment			text,		// comments about the hours worked
	category		text,		// category for this work (from set list)
	parking			real,		// parking fees accrued for this hours entry
	expense_amount		money,		// 
	downloaded		text,		// downloaded because approved, 0 = no, 1 = yes
	fkapprover_id		text,		// the id of the supervisor who approved the hours
	approval_date		date,		// the date the hours were approved
	constraint hours_pkey primary key ( hours_id )
  );


CREATE TABLE job (				// list of all possible jobs
	job_id			int,		// id of the job, generated internally by &timesheet;
	fkclient_id		int not null,	// the client's id as relates to the client table
	estimated_hours		real,		// estimated number of hours for the completion of this job
	current_hours_estimate	real,		// 
	current_estimate_date	date,		// the date the estimate of hours was made on the job
	open_date		date,		// date the job was opened
	close_date		date,		// date the job was closed
	open			int,		// job is open or not, 0 = no, 1 = yes
	bill_rate		money,		// the billing rate for this job
	job_description		text,		// the description of the job
	default_approved	int,		// any hours logged to the job are automatically approved
	fksupervisor_id		text,		// the id of the supervisor responsible for this job, as relates to the id in the personnel table
	fknotify_id		text,		// list of user id's, as relate to the personnel_ids in the personnel table, to notify of the job's creation
	flag_hrs		int,		// number of hours logged to this job for flagging a warning to the supervisor
	category		text,		// category for this job (from set list)
	downloaded		text,		// if the job has been downloaded or not, 0 = no, 1 = yes
	constraint job_pkey primary key ( job_id )
  );


CREATE TABLE category (				// list of possible categories of work for hours and jobs
	category_id	text,			// the id for the category
	creator_id	text,			// the id for who created the category
	constraint category_pkey primary key ( category_id )
  );