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

CREATE TABLE ar_managers (
	service_number INT NOT NULL,
	management_level INT NOT NULL,
	division_id INT NOT NULL,
	PRIMARY KEY (service_number, management_level, division_id),
	FOREIGN KEY fk_service_number (service_number) REFERENCES users (service_number),
	FOREIGN KEY fk_division (division_id) REFERENCES ar_divisions (id)
) ENGINE=InnoDB;

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

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

CREATE TABLE ar_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_type_id INT NOT NULL,
	service_number INT NOT NULL,
	name VARCHAR(255) NOT NULL,
	cost_centre INT NOT NULL,
	section_location VARCHAR(255) NOT NULL,
	contact_number VARCHAR(16),
	delivery_location VARCHAR(255),
	division_id INT NOT NULL,
	approver_service_number INT NOT NULL,
	request_status_id INT NOT NULL,
	FOREIGN KEY fk_request_type (request_type_id) REFERENCES ar_request_types (id),
	FOREIGN KEY fk_division (division_id) REFERENCES ar_divisions (id),
	FOREIGN KEY fk_approver (approver_service_number) REFERENCES users (service_number),
	FOREIGN KEY fk_status (request_status_id) REFERENCES ar_request_statuses (id)
) ENGINE=InnoDB;

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

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

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

CREATE TABLE ar_mobile_data_plans (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	data_gb VARCHAR(255) NOT NULL UNIQUE,
	extra_cost DECIMAL(5, 2) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE ar_mobile_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	mobile_request_type_id INT NOT NULL,
	employee_group VARCHAR(255) NOT NULL,
	phone_id INT NOT NULL,
	data_plan_id INT DEFAULT NULL,
	FOREIGN KEY fk_request (request_id) REFERENCES ar_requests (id),
	FOREIGN KEY fk_mobile_req_type (mobile_request_type_id) REFERENCES ar_mobile_request_types (id),
	FOREIGN KEY fk_phone (phone_id) REFERENCES ar_mobile_phones (id),
	FOREIGN KEY fk_data_plan (data_plan_id) REFERENCES ar_mobile_data_plans (id)
) ENGINE=InnoDB;
	
CREATE TABLE ar_mobile_request_services (
	mobile_request_id INT NOT NULL,
	mobile_service_id INT NOT NULL,
	PRIMARY KEY (mobile_request_id, mobile_service_id),
	FOREIGN KEY fk_mobile_request (mobile_request_id) REFERENCES ar_mobile_requests (id),
	FOREIGN KEY fk_mobile_services (mobile_service_id) REFERENCES ar_mobile_services (id)
) ENGINE=InnoDB;

/*

CREATE TABLE ar_gps_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL
	...
) ENGINE=InnoDB;

CREATE TABLE ar_laptop_broadband_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL
	...
) ENGINE=InnoDB;

CREATE TABLE ar_tablet_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL
	...
) ENGINE=InnoDB;


CREATE TABLE ar_tablet_accessory_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL
	...
) ENGINE=InnoDB;

*/