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

CREATE TABLE managers (
	service_number INT NOT NULL,
	management_level INT NOT NULL,
	division_id INT NOT NULL,
	PRIMARY KEY (service_number, management_level, division_id),
	FOREIGN KEY fk_service_number (service_number) REFERENCES users (service_number),
	FOREIGN KEY fk_division (division_id) REFERENCES divisions (id)
) ENGINE=InnoDB;

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

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

CREATE TABLE requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	uid CHAR(8) NOT NULL UNIQUE,
	request_type_id INT NOT NULL,
	service_number INT,
	name VARCHAR(255) NULL,
	cost_centre INT NOT NULL,
	section_location VARCHAR(255) NULL,
	contact_number VARCHAR(16),
	delivery_location VARCHAR(255),
	division_id INT NULL,
	approver_service_number INT DEFAULT NULL,
	date_submitted DATETIME NOT NULL,
	status_id INT DEFAULT 1,
	last_altered_date DATETIME DEFAULT NULL,
	last_altered_by INT DEFAULT NULL,
	FOREIGN KEY fk_request_type (request_type_id) REFERENCES request_types (id),
	FOREIGN KEY fk_division (division_id) REFERENCES divisions (id),
	FOREIGN KEY fk_approver (approver_service_number) REFERENCES users (service_number),
	FOREIGN KEY fk_status (status_id) REFERENCES request_statuses (id),
	FOREIGN KEY fk_last_alterer (last_altered_by) REFERENCES users (service_number)
) ENGINE=InnoDB;

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

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

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

CREATE TABLE workgroup_supervisors (
	service_number INT NOT NULL,
	cost_centre SMALLINT NOT NULL,
	cost_centre_sub CHAR(1) DEFAULT NULL,
	depot_id INT NOT NULL,
	workgroup_id INT NOT NULL,
	division_id INT NOT NULL,
	region_id INT NOT NULL,
	PRIMARY KEY (service_number, cost_centre, depot_id, workgroup_id, division_id, region_id),
	FOREIGN KEY fk_user (service_number) REFERENCES users (service_number),
	FOREIGN KEY fk_depot (depot_id) REFERENCES depots (id),
	FOREIGN KEY fk_workgroups (workgroup_id) REFERENCES workgroups (id),
	FOREIGN KEY fk_divisions (division_id) REFERENCES divisions (id),
	FOREIGN KEY fk_regions (region_id) REFERENCES regions (id)
) ENGINE=InnoDB;

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

CREATE TABLE request_histories (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	history_type_id INT NOT NULL,
	service_number INT NOT NULL,
	date_when TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY request (request_id) REFERENCES requests (id),
	FOREIGN KEY history_type (history_type_id) REFERENCES request_history_types (id),
	FOREIGN KEY service_number (service_number) REFERENCES users (service_number)
) ENGINE=InnoDB;

CREATE TABLE request_history_comment (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	history_id INT NOT NULL,
	comment TEXT,
	FOREIGN KEY history (history_id) REFERENCES request_histories (id)
) ENGINE=InnoDB;

CREATE TABLE request_history_status_change (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	history_id INT NOT NULL,
	status_before_id INT NOT NULL,
	status_after_id INT NOT NULL,
	FOREIGN KEY history (history_id) REFERENCES request_histories (id),
	FOREIGN KEY status_before (status_before_id) REFERENCES request_statuses (id),
	FOREIGN KEY status_after (status_after_id) REFERENCES request_statuses (id)
) ENGINE=InnoDB;