/* General Tablet Schema */

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

CREATE TABLE tablet_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	request_type_id INT NOT NULL,
	approver_service_number INT NOT NULL,
	approver_is_acting TINYINT,
	FOREIGN KEY fk_request (request_id) REFERENCES requests (id),
	FOREIGN KEY fk_request_type (request_type_id) REFERENCES tablet_request_types (id),
	FOREIGN KEY fk_approver (approver_service_number) REFERENCES users (service_number)
) ENGINE=InnoDB;

CREATE TABLE tablet_scheduling (
	tablet_request_id INT NOT NULL,
	day_of_week TINYINT NOT NULL,
	PRIMARY KEY (tablet_request_id, day_of_week),
	FOREIGN KEY fk_tablet_request (tablet_request_id) REFERENCES tablet_requests (id)
) ENGINE=InnoDB;


/* Schema for tablet device requests */

CREATE TABLE tablet_tablet_replacement_reports (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	service_number INT NOT NULL,
	employee_name VARCHAR(255),
	employee_position VARCHAR(255),
	report TEXT,
	security_paperwork_submitted TINYINT DEFAULT 0
) ENGINE=InnoDB;

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

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

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

CREATE TABLE tablet_tablet_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	tablet_request_id INT NOT NULL,
	request_type_id INT NOT NULL,
	replacement_reason_id INT DEFAULT NULL,
	police_event_number VARCHAR(32),
	mount_was_damaged TINYINT DEFAULT 0,
	vehicle_fleet_number INT DEFAULT NULL,
	vehicle_rego_number VARCHAR(16),
	damage_fault_details TEXT,
	replacement_report_id INT DEFAULT NULL,
	FOREIGN KEY fk_tablet_request (tablet_request_id) REFERENCES tablet_requests (id),
	FOREIGN KEY fk_request_type (request_type_id) REFERENCES tablet_tablet_request_types (id),
	FOREIGN KEY fk_replacement_reason (replacement_reason_id) REFERENCES tablet_tablet_replacement_reasons (id),
	FOREIGN KEY fk_replacement_report (replacement_report_id) REFERENCES tablet_tablet_replacement_reports (id)
) ENGINE=InnoDB;

CREATE TABLE tablet_tablet_request_additional_items (
	tablet_tablet_request_id INT NOT NULL,
	item_id INT NOT NULL,
	FOREIGN KEY fk_tablet_tablet_request (tablet_tablet_request_id) REFERENCES tablet_tablet_requests (id),
	FOREIGN KEY fk_item (item_id) REFERENCES tablet_tablet_additional_items (id)
) ENGINE=InnoDB;

/* Schema for Vehicle Mount device requests */

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

CREATE TABLE tablet_mount_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	tablet_request_id INT NOT NULL,
	request_reason_id INT NOT NULL,
	old_vehicle_fleet_number INT DEFAULT NULL,
	old_vehicle_rego_number VARCHAR(16),
	new_vehicle_fleet_number INT DEFAULT NULL,
	new_vehicle_rego_number VARCHAR(16),
	details TEXT,
	FOREIGN KEY fk_tablet_request (tablet_request_id) REFERENCES tablet_requests (id),
	FOREIGN KEY fk_request_reason (request_reason_id) REFERENCES tablet_mount_request_reasons (id)
) ENGINE=InnoDB;

/* Schema for Accessories requests */

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

CREATE TABLE tablet_accessory_tablet_accessories (
	tablet_request_id INT NOT NULL,
	tablet_number INT NOT NULL,
	accessory_id INT NOT NULL,
	PRIMARY KEY (tablet_request_id, tablet_number, accessory_id),
	FOREIGN KEY fk_tablet_request (tablet_request_id) REFERENCES tablet_requests (id),
	FOREIGN KEY fk_accessory (accessory_id) REFERENCES tablet_accessory_accessories (id)
) ENGINE=InnoDB;

/* Schema for Services requests */

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

CREATE TABLE tablet_services_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	tablet_request_id INT NOT NULL,
	service_type_id INT NOT NULL,
	details TEXT,
	FOREIGN KEY fk_tablet_request (tablet_request_id) REFERENCES tablet_requests (id),
	FOREIGN KEY fk_tablet_request (tablet_request_id) REFERENCES tablet_requests (id),
	FOREIGN KEY fk_service_type (service_type_id) REFERENCES tablet_services_service_types (id)
) ENGINE=InnoDB;
