
CREATE TABLE si_sites (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	address VARCHAR(255),
	suburb VARCHAR(64)
) ENGINE=InnoDB;

CREATE TABLE si_contract_types (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE si_audit_types (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(32)
) ENGINE=InnoDB;

CREATE TABLE si_contractors (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE si_employees (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	email_addr VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE si_query_types (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE si_query_categories (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE si_queries (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	description VARCHAR(255),
	query_type_id INT REFERENCES si_query_types (id),
	query_category_id INT REFERENCES si_query_categories (id)
) ENGINE=InnoDB;

CREATE TABLE si_inspections (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	inspection_id VARCHAR(64),
	site_id INT NOT NULL REFERENCES si_sites (id),
	contract_type_id INT NOT NULL REFERENCES si_contract_types (id),
	audit_type_id INT NOT NULL REFERENCES si_audit_types (id),
	contractor_id INT NOT NULL REFERENCES si_contractors (id),
	subcontractor_id INT NOT NULL REFERENCES si_contractors (id),
	when_date DATE,
	site_inspector_id INT NOT NULL REFERENCES si_employees (id),
	site_supervisor_id INT NOT NULL REFERENCES si_employees (id),
	contract_officer_id INT NOT NULL REFERENCES si_employees (id),
	is_active BIT NOT NULL,
	control_officer_recomm TEXT,
	remedial_action_plan TEXT,
	preventative_action_plan TEXT
) ENGINE=InnoDB;

CREATE TABLE si_inspection_queries (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	display_id VARCHAR(16),
	inspection_id INT NOT NULL REFERENCES si_inspections (id),
	query_id INT NOT NULL REFERENCES si_queries (id),
	value TINYINT,
	action_required BIT

) ENGINE=InnoDB;

CREATE TABLE si_inspection_query_add_info (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	inspection_id INT NOT NULL REFERENCES si_inspections (id),
	inspection_query_id INT REFERENCES si_inspection_queries (id),
	comment TEXT
) ENGINE=InnoDB;

CREATE TABLE si_positions (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	inspection_id INT NOT NULL REFERENCES si_inspections (id),
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE si_trainings (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE si_inspection_personnel (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	inspection_id INT NOT NULL REFERENCES si_insepections (id),
	name VARCHAR(128) NOT NULL,
	num INT,
	position_id INT NOT NULL REFERENCES si_positions (id),
	training_comments TEXT
) ENGINE=InnoDB;

CREATE TABLE si_position_training_req_types (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(32)
) ENGINE=InnoDB;

CREATE TABLE si_position_trainings (
	position_id INT NOT NULL REFERENCES si_positions (id),
	training_id INT NOT NULL REFERENCES si_trainings (id),
	position_training_req_type INT NOT NULL REFERENCES si_position_training_req_types (id),
	PRIMARY KEY (position_id, training_id)
) ENGINE=InnoDB;

CREATE TABLE si_inspection_personnel_trainings (
	personnel_id INT NOT NULL REFERENCES si_personnel (id),
	training_id INT NOT NULL REFERENCES si_trainings (id),
	has BIT,
	PRIMARY KEY (personnel_id, training_id)
) ENGINE=InnoDB;


/**********Old table design**********
CREATE TABLE si_inspection_query_add_info (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	inspection_id INT NOT NULL REFERENCES si_inspections (id),
	query_type_id INT NOT NULL REFERENCES si_query_types (id),
	value TEXT
) ENGINE=InnoDB;
*/