SET storage_engine=InnoDB;

CREATE TABLE regions (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE rostered_shift_categories (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	code VARCHAR(16) NOT NULL UNIQUE
);

CREATE TABLE rostered_shifts (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	category_id TINYINT NOT NULL,
	start_time TIME NOT NULL,
	end_time TIME NOT NULL,
	FOREIGN KEY idx_category_id (category_id) REFERENCES rostered_shift_categories (id)
);

CREATE TABLE asset_groups (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(8) NOT NULL UNIQUE,
	name VARCHAR(255) NOT NULL
);

CREATE TABLE job_types (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(16) NOT NULL UNIQUE,
	name VARCHAR(255) NOT NULL,
	requires_details TINYINT(1) DEFAULT 0
);

CREATE TABLE entry_statuses (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(16) NOT NULL UNIQUE,
	name VARCHAR(255) NOT NULL
);

CREATE TABLE requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	serial_number INT NOT NULL,
	service_number INT NOT NULL,
	full_name VARCHAR(255) NOT NULL,
	sheet_date DATE NOT NULL,
	section VARCHAR(255) NOT NULL,
	cost_centre INT NOT NULL,
	rostered_shift_id TINYINT NOT NULL,
	manager_service_number INT NOT NULL,
	date_submitted DATETIME NOT NULL,
	last_altered_by INT DEFAULT NULL,
	UNIQUE KEY (service_number, sheet_date, rostered_shift_id),
	FOREIGN KEY idx_rostered_shift (rostered_shift_id) REFERENCES rostered_shifts (id),
	FOREIGN KEY idx_manager_service_number (manager_service_number) REFERENCES users (service_number),
	FOREIGN KEY idx_last_altered_by (last_altered_by) REFERENCES users (service_number)
);

CREATE TABLE job_entries (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	serial_number INT DEFAULT NULL,
	region_id TINYINT NOT NULL,
	asset_group_id TINYINT NOT NULL,
	job_type_id TINYINT DEFAULT NULL,
	job_type_details VARCHAR(255) DEFAULT NULL,
	job_number BIGINT UNSIGNED DEFAULT NULL,
	start_time TIME NOT NULL,
	end_time TIME NOT NULL,
	trainee_service_number INT DEFAULT NULL,
	trainee_full_name VARCHAR(255) DEFAULT NULL,
	comments TEXT,
	status_id TINYINT NOT NULL DEFAULT 1,
	overtime_minutes_before INT DEFAULT 0,
	overtime_minutes_after INT DEFAULT 0,
	UNIQUE KEY (request_id, serial_number),
	FOREIGN KEY idx_request (request_id) REFERENCES requests (id),
	FOREIGN KEY idx_region (region_id) REFERENCES regions (id),
	FOREIGN KEY idx_asset_group (asset_group_id) REFERENCES asset_groups (id),
	FOREIGN KEY idx_job_type (job_type_id) REFERENCES job_types (id),
	FOREIGN KEY idx_status (status_id) REFERENCES entry_statuses (id)
);

CREATE TABLE trainees (
	service_number INT NOT NULL PRIMARY KEY,
	first_name VARCHAR(255),
	last_name VARCHAR(255)
);

CREATE TABLE user_defaults (
	service_number INT NOT NULL,
	region_id TINYINT DEFAULT NULL,
	FOREIGN KEY idx_service_number (service_number) REFERENCES users (service_number) ON DELETE CASCADE,
	FOREIGN KEY idx_region (region_id) REFERENCES regions (id) ON DELETE CASCADE
);

CREATE TABLE asset_mappings (
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	region_id TINYINT NOT NULL,
	asset_group_id TINYINT NOT NULL,
	job_type_id TINYINT DEFAULT NULL,
	code VARCHAR(64) NOT NULL,
	FOREIGN KEY idx_region (region_id) REFERENCES regions (id) ON DELETE CASCADE,
	FOREIGN KEY idx_asset_group (asset_group_id) REFERENCES asset_groups (id) ON DELETE CASCADE,
	FOREIGN KEY idx_job_type (job_type_id) REFERENCES job_types (id) ON DELETE CASCADE
);
