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

CREATE TABLE fjs_figfleet_statuses (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) UNIQUE,
	acronym VARCHAR(8) UNIQUE
) ENGINE=InnoDB;

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

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

CREATE TABLE cost_centres (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	number VARCHAR(16) UNIQUE
) ENGINE=InnoDB;

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

CREATE TABLE fjs_figfleet_classes (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	class VARCHAR(255) UNIQUE,
	acronym VARCHAR(16) UNIQUE
) ENGINE=InnoDB;

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

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

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

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

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

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

CREATE TABLE fjs_assets (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	fleet_number INT NOT NULL UNIQUE,
	make_id INT,
	model_id INT,
	rego_number VARCHAR(128),
	vin VARCHAR(32),
	figfleet_status_id INT NOT NULL,
	figfleet_class_id INT,
	fgo_classification_id INT,
	vehicle_type_id INT,
	asset_type_id INT NOT NULL,
	type_level1 VARCHAR(64),
	type_level2 VARCHAR(64),
	attached INT,
	cost_centre_id INT,
	location_id INT,
	contact_name VARCHAR(255),
	purchase_date DATE,
	build_year year,
	in_service_date DATE,
	last_service_date DATE,
	next_service_date DATE,
	next_service_type_id INT,
	odometer_reading_date DATE,
	odometer_reading INT,
	last_service_garage_id INT,
	service_sheet VARCHAR(255),
	disposal_date DATE,
	last_electrical_test_date DATE DEFAULT NULL,
	last_ndt_date DATE DEFAULT NULL,
	figfleet_comments TEXT,
	scheduler_notes TEXT,
	pending_recall BIT DEFAULT 0,
	INDEX idx_attached (attached),
	INDEX idx_next_service_date (next_service_date),
	INDEX idx_in_service_date (in_service_date),
	INDEX idx_location (location_id),
	INDEX idx_figfleet_status (figfleet_status_id),
	FOREIGN KEY fk_make (make_id) REFERENCES fjs_asset_makes (id),
	FOREIGN KEY fk_model (model_id) REFERENCES fjs_asset_models (id),
	FOREIGN KEY fk_asset_type (asset_type_id) REFERENCES fjs_asset_types (id),
	FOREIGN KEY fk_figfleet_status (figfleet_status_id) REFERENCES fjs_figfleet_statuses (id),
	FOREIGN KEY fk_last_service_garage (last_service_garage_id) REFERENCES fjs_garages (id),
	FOREIGN KEY fk_next_service_type (next_service_type_id) REFERENCES fjs_service_types (id),
	FOREIGN KEY fk_figfleet_class (figfleet_class_id) REFERENCES fjs_figfleet_classes (id),
	FOREIGN KEY fk_location (location_id) REFERENCES fjs_locations (id),
	FOREIGN KEY fk_vehicle_type (vehicle_type_id) REFERENCES fjs_vehicle_types (id),
	FOREIGN KEY fk_cost_centre (cost_centre_id) REFERENCES cost_centres (id),
	FOREIGN KEY fk_fgo_classification (fgo_classification_id) REFERENCES fjs_fgo_classifications (id)
) ENGINE=InnoDB;

CREATE TABLE fjs_work_orders (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	asset_id INT NOT NULL,
	garage_id INT,
	booking_date DATE,
	est_completion_date DATE,
	contact_name VARCHAR(255),
	contact_phone VARCHAR(10),
	supervisor_name VARCHAR(255),
	short_service_description VARCHAR(255) NOT NULL,
	reported_defect BIT NOT NULL,
	detailed_service_description TEXT NOT NULL,
	days_off_road DECIMAL(5,3),
	defect_avoidable BIT DEFAULT NULL,
	mechanic_assigned VARCHAR(255) DEFAULT NULL,
	actual_start_date DATE DEFAULT NULL,
	actual_completion_date DATE DEFAULT NULL,
	garage_notes TEXT,
	work_order_status_id INT NOT NULL,
	date_created DATETIME NOT NULL,
	date_updated DATETIME DEFAULT NULL,
	creator_id INT,
	FOREIGN KEY fk_work_order_status (work_order_status_id) REFERENCES fjs_work_order_statuses (id),
	FOREIGN KEY fk_asset (asset_id) REFERENCES fjs_assets (id),
	FOREIGN KEY fk_garage (garage_id) REFERENCES fjs_garages (id)
) ENGINE=InnoDB;

CREATE TABLE fjs_fgo_classification_map (
	type_level1 VARCHAR(64) UNIQUE,
	classification_id INT NOT NULL,
	FOREIGN KEY fk_classification (classification_id) REFERENCES fjs_fgo_classifications (id)
) ENGINE=InnoDB;

CREATE TABLE fjs_work_order_day_entries (
	work_order_id INT NOT NULL,
	entry_date DATE NOT NULL,
	off_road_reason_id INT NOT NULL,
	PRIMARY KEY (work_order_id, entry_date),
	INDEX idx_work_order_id (work_order_id),
	INDEX idx_entry_date (entry_date),
	INDEX idx_off_road_reason_id (off_road_reason_id),
	FOREIGN KEY fk_off_road_reason (off_road_reason_id) REFERENCES fjs_off_road_reasons (id)
) ENGINE=InnoDB;

CREATE TABLE user_defaults (
	service_number INT NOT NULL,
	garage_id INT DEFAULT NULL,
	FOREIGN KEY fk_service_number (service_number) REFERENCES users (service_number) ON DELETE CASCADE,
	FOREIGN KEY fk_garage (garage_id) REFERENCES fjs_garages (id) ON DELETE SET NULL
) ENGINE=InnoDB;