SET storage_engine=InnoDB;

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

CREATE TABLE change_reasons (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(32) NOT NULL UNIQUE,
	name VARCHAR(255) NOT NULL,
	category_id TINYINT NOT NULL,
	FOREIGN KEY idx_category (category_id) REFERENCES change_reason_categories (id)
);

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

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

CREATE TABLE branches (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL
);

CREATE TABLE sections (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL
);

CREATE TABLE org_units (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL
);

CREATE TABLE statuses (
  id TINYINT NOT NULL AUTO_INCREMENT,
  code VARCHAR(32) NOT NULL,
  name VARCHAR(255) DEFAULT NULL,
  requires_reason TINYINT(1) DEFAULT 0,
  PRIMARY KEY (id),
  UNIQUE KEY code (code)
);

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

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

CREATE TABLE employee_group_pairings (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	group_id INT NOT NULL,
	subgroup_id INT NOT NULL,
	FOREIGN KEY idx_group (group_id) REFERENCES employee_groups (id),
	FOREIGN KEY idx_subgroup (subgroup_id) REFERENCES employee_subgroups(id)
);

CREATE TABLE cost_centres (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(255) NOT NULL
);

CREATE TABLE rdo_types (
	id INT NOT NULL PRIMARY KEY,
	name VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE requests (

	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	applicant_service_number INT NOT NULL,
	applicant_name VARCHAR(255) NOT NULL,
	employee_service_number INT NOT NULL,
	employee_name VARCHAR(255) NOT NULL,
	approver_service_number INT NOT NULL,
	status_id TINYINT NOT NULL DEFAULT 1,
	last_status_changer INT DEFAULT NULL,
	status_reason VARCHAR(255) DEFAULT NULL,
	date_submitted TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

	current_position_id VARCHAR(32) NOT NULL,
	current_position_title VARCHAR(64) NOT NULL,

	change_reason_id TINYINT NOT NULL,
	incumbent_service_number INT DEFAULT NULL,
	incumbent_name VARCHAR(255),
	new_position_id VARCHAR(32),
	new_position_title VARCHAR(64),
	position_location VARCHAR(255),
	grade VARCHAR(255),
	cost_centre INT DEFAULT NULL,
	pay_point VARCHAR(255) DEFAULT NULL,
	division_id INT DEFAULT NULL,
	branch_id INT DEFAULT NULL,
	section_id INT DEFAULT NULL,
	org_unit_id INT DEFAULT NULL,
	payslip_email_addr VARCHAR(255)DEFAULT NULL,
	work_schedule_rule_id INT DEFAULT NULL,
	tt_headquarters VARCHAR(255),
	employee_group_id INT,
	employee_subgroup_id INT,
	start_date DATE DEFAULT NULL,
	end_date DATE,
	trp TINYINT,
	work_phone VARCHAR(16),
	work_mobile VARCHAR(16),
	rdo_type_id INT DEFAULT NULL,
	rdo_day_of_week TINYINT DEFAULT NULL COMMENT 'Monday through Sunday (1-7)',
	is_shift_work TINYINT(1) DEFAULT NULL,
	is_field_based TINYINT(1) DEFAULT NULL,
	comments VARCHAR(255) DEFAULT NULL,

	FOREIGN KEY idx_status (status_id) REFERENCES statuses (id),
	FOREIGN KEY idx_change_reason (change_reason_id) REFERENCES change_reasons (id),
	FOREIGN KEY idx_approver (approver_service_number) REFERENCES users (service_number),
	FOREIGN KEY idx_last_changer (last_status_changer) REFERENCES users (service_number),
	FOREIGN KEY idx_cost_centre (cost_centre) REFERENCES cost_centres (id),
	FOREIGN KEY idx_division (division_id) REFERENCES divisions (id),
	FOREIGN KEY idx_section (section_id) REFERENCES sections (id),
	FOREIGN KEY idx_org_unit (org_unit_id) REFERENCES org_units (id),
	FOREIGN KEY idx_branch (branch_id) REFERENCES branches (id),
	FOREIGN KEY idx_employee_group (employee_group_id) REFERENCES employee_groups (id),
	FOREIGN KEY idx_employee_subgroup (employee_subgroup_id) REFERENCES employee_subgroups (id),
	FOREIGN KEY idx_rdo_type (rdo_type_id) REFERENCES rdo_types (id),
	FOREIGN KEY idx_work_schedule_rule (work_schedule_rule_id) REFERENCES work_schedule_rules (id)

);

CREATE TABLE request_work_hours (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	day_of_week TINYINT NOT NULL COMMENT 'Monday through Sunday (1-7)',
	start_time TIME NOT NULL,
	end_time TIME NOT NULL,
	FOREIGN KEY idx_request (request_id) REFERENCES requests (id),
	UNIQUE KEY uk_request_day (request_id, day_of_week)
);
