SET storage_engine=InnoDB;

CREATE TABLE requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	employee_service_number INT NOT NULL,
	employee_name VARCHAR(255) NOT NULL,
	employee_branch VARCHAR(255) NOT NULL,
	employee_section VARCHAR(255) NOT NULL,
	cost_centre INT NOT NULL,
	manager_service_number INT,
	work_description VARCHAR(255) NOT NULL,
	alternatives VARCHAR(255) NOT NULL,
	risks VARCHAR(255) NOT NULL,
	comments VARCHAR(255) DEFAULT NULL
);

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

CREATE TABLE accom_details (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	status_id INT DEFAULT 1,
	staff_service_number INT NOT NULL,
	staff_name VARCHAR(255) NOT NULL,
	staff_position VARCHAR(255) NOT NULL,
	check_in_date DATE NOT NULL,
	num_nights INT NOT NULL,
	estimated_cost INT NOT NULL,
	FOREIGN KEY idx_request (request_id) REFERENCES requests (id),
	FOREIGN KEY idx_status (status_id) REFERENCES statuses (id)
);

CREATE TABLE accom_detail_status_history (
	service_number INT NOT NULL,
	accom_detail_id INT NOT NULL,
	previous_status_id INT NOT NULL,
	new_status_id INT NOT NULL,
	event_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY idx_service_number (service_number) REFERENCES users (service_number),
	FOREIGN KEY idx_accom_detail (accom_detail_id) REFERENCES accom_details (id),
	FOREIGN KEY idx_prev_status (previous_status_id) REFERENCES statuses (id),
	FOREIGN KEY idx_new_status (new_status_id) REFERENCES statuses (id)
);
