CREATE TABLE file_types (
	id TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	code VARCHAR(32) UNIQUE NOT NULL,
	name VARCHAR(64) NOT NULL
) ENGINE=InnoDB;

INSERT INTO file_types (id, code, name) VALUES
	(1, 'resignation_letter', 'Resignation Letter'),
	(2, 'letter_of_authority', 'Letter of Authority'),
	(3, 'med_cert', 'Medical Certificate'),
	(4, 'workers_compensation', 'Workers Compensation');

CREATE TABLE request_files (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	file_type_id TINYINT NOT NULL,
	filename VARCHAR(255) NOT NULL,
	filepath VARCHAR(255) NOT NULL,
	FOREIGN KEY idx_file_type (file_type_id) REFERENCES file_types (id),
	FOREIGN KEY idx_request (request_id) REFERENCES requests (id)
) ENGINE=InnoDB;
