
CREATE TABLE traveltime_transport_types (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL UNIQUE,
	acronym VARCHAR(16) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE traveltime_duty_types (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL UNIQUE,
	acronym VARCHAR(16) NOT NULL UNIQUE
);

CREATE TABLE traveltime_managers (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	user_id INT,
	cost_centre INT NOT NULL,
	FOREIGN KEY fk_users_id (user_id) REFERENCES users (id),
	INDEX idx_cost_centre (cost_centre)
) ENGINE=InnoDB;

CREATE TABLE traveltime_submissions (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	submission_date DATETIME NOT NULL,
	load_id INT DEFAULT NULL,
	service_number INT NOT NULL,
	employee_name VARCHAR(128) NOT NULL,
	employee_suburb VARCHAR(128) NOT NULL,
	weekend_sunday DATE NOT NULL,
	manager_id INT NOT NULL,
	section_cost_centre VARCHAR(255) NOT NULL,
	INDEX idx_submission_date (submission_date),
	INDEX idx_service_number (service_number),
	INDEX idx_employee_name (employee_name),
	FOREIGN KEY fk_submission (load_id) REFERENCES submissions (id),
	FOREIGN KEY fk_manager_id (manager_id) REFERENCES traveltime_managers (id)
) ENGINE=InnoDB;

CREATE TABLE traveltime_submission_timedetails (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	submission_id INT NOT NULL,
	travel_day DATE NOT NULL,
	start_time TIME NOT NULL,
	end_time TIME NOT NULL,
	from_suburb VARCHAR(128) NOT NULL,
	to_suburb VARCHAR(128) NOT NULL,
	transport_type INT NOT NULL,
	duty_type INT NOT NULL,
	paygrade INT DEFAULT NULL,
	paygrade_rate DECIMAL(8,2) DEFAULT NULL,
	acting_other_paygrade BIT NOT NULL,
	status tinyint DEFAULT NULL,
	status_date DATETIME DEFAULT NULL,
	status_changer INT DEFAULT NULL,
	INDEX idx_status (status),
	FOREIGN KEY fk_users_status_changer (status_changer) REFERENCES users (id),
	FOREIGN KEY fk_paygrade (paygrade) REFERENCES paygrades (id),
	FOREIGN KEY fk_duty_type (duty_type) REFERENCES traveltime_duty_types (id),
	FOREIGN KEY fk_transport_type (transport_type) REFERENCES traveltime_travel_types (id),
	FOREIGN KEY fk_submission (submission_id) REFERENCES traveltime_submissions (id)
) ENGINE=InnoDB;

CREATE TABLE traveltime_delegations (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	delegator INT NOT NULL,
	delegatee INT NOT NULL,
	date_from DATE,
	date_to DATE,
	active BIT DEFAULT 1,
	FOREIGN KEY fk_users_delegator (delegator) REFERENCES users (id),
	FOREIGN KEY fk_users_delegatee (delegatee) REFERENCES users (id),
	INDEX idx_date_from (date_from),
	INDEX idx_date_to (date_to)
);

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

CREATE TABLE traveltime_notification_history (
	notification_id INT NOT NULL,
	service_number INT NOT NULL,
	sent TIMESTAMP NOT NULL,
	FOREIGN KEY fk_notification (notification_id) REFERENCES traveltime_notifications (id),
	INDEX idx_service_number (service_number)
);

INSERT INTO traveltime_notifications (name) VALUES ('manager_documentation');
