CREATE TABLE si_inspections (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	pmis INT NOT NULL,
	username VARCHAR(255),
	location_id INT NOT NULL REFERENCES si_locations (id),
	when_date DATE,
	when_time TIME
) ENGINE=InnoDB;

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

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

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

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

CREATE TABLE si_inspection_faults (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	inspection_id INT NOT NULL REFERENCES inspections (id),
	maintenance_category_id INT NOT NULL REFERENCES si_maintenance_categories (id),
	fault_type_id INT NOT NULL REFERENCES si_fault_types (id),
	component_affected_id INT NOT NULL REFERENCES si_inspection_components (id),
	priority TINYINT NOT NULL,
	inspector_comment TEXT,
	action_status INT DEFAULT NULL,
	which_building VARCHAR(64),
	which_floor VARCHAR(64),
	which_room VARCHAR(64)
) ENGINE=InnoDB;