SET storage_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 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 request_statuses (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(32) NOT NULL UNIQUE,
	code VARCHAR(16) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	serial_number TINYINT NOT NULL,
	service_number INT NOT NULL,
	employee_name VARCHAR(32) NOT NULL,
	email_addr VARCHAR(128) NOT NULL,
	mobile_phone VARCHAR(16) NOT NULL,
	section VARCHAR(255) NOT NULL,
	division_id INT NOT NULL,
	branch VARCHAR(255) NOT NULL,
	approver_service_number INT NOT NULL,
	request_type_id INT NOT NULL,
	last_altered_by INT DEFAULT NULL,
	last_altered_date DATETIME DEFAULT NULL,
	endorser_service_number INT DEFAULT NULL,
	primary_leave_period_id INT,
	leave_comments TEXT,
	reason_declined TEXT,
	med_cert_witnessed TINYINT DEFAULT 0,
	FOREIGN KEY idx_division (division_id) REFERENCES divisions (id),
	FOREIGN KEY idx_last_altered_by (last_altered_by) REFERENCES users (service_number),
	FOREIGN KEY idx_request_type_id (request_type_id) REFERENCES request_types (id)
) ENGINE=InnoDB;

CREATE TABLE request_items (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	item_serial_number TINYINT NOT NULL,
	request_id INT NOT NULL,
	status_id INT NOT NULL DEFAULT 1,
	FOREIGN KEY idx_request_id (request_id) REFERENCES requests (id),
	FOREIGN KEY idx_status_id (status_id) REFERENCES request_statuses (id)
) ENGINE=InnoDB;

CREATE TABLE request_leave_types (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL UNIQUE,
	code VARCHAR(32) NOT NULL UNIQUE,
	parent_id INT DEFAULT NULL,
	FOREIGN KEY idx_parent_id (parent_id) REFERENCES request_leave_types (id)
) ENGINE=InnoDB;

CREATE TABLE request_leave_periods (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	item_id INT NOT NULL,
	leave_type_id INT NOT NULL,
	start_date DATE NOT NULL,
	end_date DATE NOT NULL,
	FOREIGN KEY idx_leave_type (leave_type_id) REFERENCES request_leave_types (id),
	FOREIGN KEY idx_item_id (item_id) REFERENCES request_items (id) ON DELETE CASCADE
) ENGINE=InnoDB;

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

CREATE TABLE request_leave_period_details (
	leave_period_id INT NOT NULL,
	start_time TIME DEFAULT NULL,
	end_time TIME DEFAULT NULL,
	duration_weeks TINYINT DEFAULT NULL,
	duration_days TINYINT DEFAULT NULL,
	duration_hours TINYINT DEFAULT NULL,
	duration_minutes TINYINT DEFAULT NULL,
	pay_type_id INT DEFAULT NULL,
	prepayment_requested TINYINT DEFAULT NULL,
	FOREIGN KEY idx_leave_period (leave_period_id) REFERENCES request_leave_periods (id) ON DELETE CASCADE,
	FOREIGN KEY idx_pay_type (pay_type_id) REFERENCES request_leave_period_pay_types (id)
) ENGINE=InnoDB;

ALTER TABLE requests ADD CONSTRAINT fk_primary_leave_period FOREIGN KEY idx_primary_leave_period (primary_leave_period_id) REFERENCES request_leave_periods (id);

CREATE TABLE request_rdos (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	item_id INT NOT NULL,
	rdo_date DATE NOT NULL,
	FOREIGN KEY idx_item_id (item_id) REFERENCES request_items (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE request_ulves (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	item_id INT NOT NULL,
	ulve_date DATE NOT NULL,
	FOREIGN KEY idx_item_id (item_id) REFERENCES request_items (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE `holidays` (
	`id` int NOT NULL AUTO_INCREMENT,
	`year` int DEFAULT NULL,
	`month` tinyint DEFAULT NULL,
	`day` tinyint DEFAULT NULL,
	`name` varchar(255) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `idx_date` (`year`,`month`,`day`),
	KEY `idx_year` (`year`),
	KEY `idx_month` (`month`),
	KEY `idx_day` (`day`)
) ENGINE=InnoDB;

CREATE TABLE request_rdo_replacements (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	item_id INT NOT NULL,
	actual_date DATE NULL DEFAULT NULL,
	date_taken DATE NOT NULL,
	FOREIGN KEY idx_item_id (item_id) REFERENCES request_items (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE request_accrued_rdos (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	item_id INT NOT NULL,
	rdo_date DATE NOT NULL,
	been_taken TINYINT DEFAULT 0,
	FOREIGN KEY idx_item_id (item_id) REFERENCES request_items (id) ON DELETE CASCADE
) ENGINE=InnoDB;