CREATE TABLE job_types (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE job_categories (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE statuses (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE duration_classes (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255),
	hours DECIMAL(3, 1)
) ENGINE=InnoDB;

CREATE TABLE zones (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;


CREATE TABLE areas (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	region_id INT NOT NULL,
	name VARCHAR(255),
	KEY region (region_id)
) ENGINE=InnoDB;

CREATE TABLE regions (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE officers (
	service_number INT NOT NULL PRIMARY KEY,
	name VARCHAR(255),
	phone VARCHAR(16)
) ENGINE=InnoDB;

CREATE TABLE requests (
	daros_number INT NOT NULL PRIMARY KEY,
	date_issued DATETIME,
	description VARCHAR(255),
	job_type_id INT NOT NULL,
	job_category_id INT NOT NULL,
	status_id INT NOT NULL,
	duration_class_id INT NOT NULL,
	sheet_number VARCHAR(16),
	diag_ref VARCHAR(8),
	zone_id INT NOT NULL,
	operating_area_id INT NOT NULL,
	officer_service_number INT NOT NULL,
	FOREIGN KEY fk_job_type (job_type_id) REFERENCES job_types (id),
	FOREIGN KEY fk_job_category (job_category_id) REFERENCES job_categories (id),
	FOREIGN KEY fk_status (status_id) REFERENCES statuses (id),
	FOREIGN KEY fk_duration_class (duration_class_id) REFERENCES duration_classes (id),
	FOREIGN KEY fk_zone (zone_id) REFERENCES zones (id),
	FOREIGN KEY fk_operating_area (operating_area_id) REFERENCES areas (id),
	FOREIGN KEY fk_officer (officer_service_number) REFERENCES officers (service_number)
) ENGINE=InnoDB;