
CREATE TABLE org_purposes (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	purpose VARCHAR(255),
	ord INT UNIQUE
) ENGINE=InnoDB;


CREATE TABLE grant_uses (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	grant_use VARCHAR(255),
	ord INT UNIQUE
) ENGINE=InnoDB;

CREATE TABLE volunteer_roles (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	role VARCHAR(255),
	ord INT UNIQUE
) ENGINE=InnodB;

CREATE TABLE justifications (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	justification VARCHAR(512) NOT NULL,
	ord INT UNIQUE
) ENGINE=InnoDB;

CREATE TABLE submissions (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	serial_id INT NOT NULL,
	load_id INT DEFAULT NULL,
	service_number INT NOT NULL,
	which_application TINYINT NOT NULL,
	is_inkind BIT DEFAULT 0,
	is_movember BIT DEFAULT 0,
	movember_id INT DEFAULT NULL,
	movember_team_name VARCHAR(255) DEFAULT NULL,
	applicant_name VARCHAR(64) NOT NULL,
	applicant_division INT NOT NULL,
	applicant_work_addr VARCHAR(255) NOT NULL,
	applicant_postcode int NOT NULL,
	applicant_suburb VARCHAR(64) NOT NULL,
	applicant_phone VARCHAR(16) NOT NULL,
	applicant_email VARCHAR(255) NOT NULL,
	org_name VARCHAR(128) NOT NULL,
	position_in_org VARCHAR(64) NOT NULL,
	org_contact_name VARCHAR(64) NOT NULL,
	org_contact_phone VARCHAR(16) NOT NULL,
	org_contact_email VARCHAR(255) NOT NULL,
	org_addr_line1 VARCHAR(128) NOT NULL,
	org_addr_line2 VARCHAR(128) NOT NULL,
	org_suburb VARCHAR(64) NOT NULL,
	org_postcode INT NOT NULL,
	org_state INT NOT NULL,
	org_purpose INT NOT NULL,
	org_purpose_other VARCHAR(64) DEFAULT NULL,
	org_description TEXT,
	grant_use INT NULL,
	grant_use_other VARCHAR(64) DEFAULT NULL,
	volunteer_role INT NOT NULL,
	volunteer_role_other VARCHAR(64) DEFAULT NULL,
	volun_reg_num VARCHAR(16) DEFAULT NULL,
	contactable TINYINT NOT NULL,
	recc_id INT NOT NULL,
	recc_io_number INT NOT NULL,
	submission_date DATETIME NOT NULL,
	status tinyint DEFAULT NULL,
	endorsed_by INT DEFAULT NULL,
	amount DECIMAL(8, 2) DEFAULT NULL,
	justification INT DEFAULT NULL,
	decision_date DATETIME NULL DEFAULT NULL,
	decision_user_id INT DEFAULT NULL,
	gl_account INT NULL,
	is_paid BIT DEFAULT NULL,
	sms_updates BIT NOT NULL DEFAULT b'0'
) ENGINE=InnoDB;

ALTER TABLE submissions ADD INDEX idx_recc_id (recc_id);
ALTER TABLE submissions ADD INDEX idx_service_number (service_number);
ALTER TABLE submissions ADD INDEX idx_isinkind (is_inkind);
ALTER TABLE submissions ADD INDEX idx_is_paid (is_paid);
ALTER TABLE submissions ADD INDEX idx_submission_date (submission_date);

ALTER TABLE submissions ADD FOREIGN KEY fk_division (applicant_division) REFERENCES divisions (id);
ALTER TABLE submissions ADD FOREIGN KEY fk_org_purpose (org_purpose) REFERENCES org_purposes (id);
ALTER TABLE submissions ADD FOREIGN KEY fk_grant_use (grant_use) REFERENCES grant_uses (id);
ALTER TABLE submissions ADD FOREIGN KEY fk_volunteer_role (volunteer_role) REFERENCES volunteer_roles (id);
ALTER TABLE submissions ADD FOREIGN KEY fk_recc_id (recc_id) REFERENCES reccs (id);
ALTER TABLE submissions ADD FOREIGN KEY fk_justification (justification) REFERENCES justifications (id);
ALTER TABLE submissions ADD FOREIGN KEY fk_decision_user (decision_user_id) REFERENCES users (id);

CREATE TABLE submissions_inkind_uses (
	submission_id INT PRIMARY KEY,
	interior_lighting TINYINT DEFAULT 0,
	fans TINYINT DEFAULT 0,
	floodlighting TINYINT DEFAULT 0,
	powerpoints TINYINT DEFAULT 0,
	switchboards TINYINT DEFAULT 0
) ENGINE=InnoDB;

ALTER TABLE submissions_inkind_uses ADD FOREIGN KEY fk_submission_id (submission_id) REFERENCES submissions (id) ON DELETE CASCADE;

/************************************************************
Note:  The year column in budgets is meant to be the
fiscal year, not the actual year.
For example, year = 2010, month=1 would be Jan 2011, not Jan
2010, since the 2010/2011 fisal year.  Jan 2010 would actually
be year=2009, month=1
*************************************************************/

CREATE TABLE budgets (
	recc_id INT NOT NULL,
	year SMALLINT NULL,
	month TINYINT NULL,
	amount DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;

CREATE UNIQUE INDEX idx_budget_types ON budgets (recc_id, year, month);

CREATE INDEX idx_when ON budgets (year, month);

ALTER TABLE budgets ADD FOREIGN KEY fk_recc_id (recc_id) REFERENCES reccs (id);
