SET storage_engine=InnoDB;

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

CREATE TABLE requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	notifier_service_number INT NOT NULL,
	notifier_name VARCHAR(255) NOT NULL,
	employee_service_number INT NOT NULL,
	employee_name VARCHAR(255) NOT NULL,
	employee_division VARCHAR(255),
	manager_service_number INT NOT NULL,
	manager_name VARCHAR(255) NOT NULL,
	manager_division VARCHAR(255),
	manager_email_addr VARCHAR(255) DEFAULT NULL,
	manager_mobile_phone VARCHAR(16) DEFAULT NULL,
	last_employment_date DATE NOT NULL,
	endofemployment_reason_id TINYINT NOT NULL,
	comments VARCHAR(255) NOT NULL,
	date_submitted TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY idx_endofemployment_reason (endofemployment_reason_id) REFERENCES endofemployment_reasons (id)
);

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

CREATE TABLE request_uploads (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	upload_type_id TINYINT NOT NULL,
	filename VARCHAR(255) NOT NULL,
	filepath VARCHAR(255) NOT NULL,
	FOREIGN KEY idx_request (request_id) REFERENCES requests (id),
	FOREIGN KEY idx_upload_type (upload_type_id) REFERENCES upload_types (id)
);
