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

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

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

CREATE TABLE mobile_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 mobile_carriers (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL UNIQUE,
	code VARCHAR(16) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE 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 mobile_accessories (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	device_id INT DEFAULT NULL,
	name VARCHAR(255),
	FOREIGN KEY fk_device (device_id) REFERENCES mobile_devices (id)
) ENGINE=InnoDB;

CREATE TABLE mobile_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	device_type_id INT NOT NULL,
	request_type_id INT DEFAULT NULL,
	data_plan_id INT DEFAULT NULL,
	current_number varchar(16) DEFAULT NULL,
	current_carrier_id INT DEFAULT NULL,
	need_nextg_card TINYINT DEFAULT 0,
	approver_service_number INT NOT NULL,
	FOREIGN KEY fk_request (request_id) REFERENCES requests (id),
	FOREIGN KEY fk_device (device_type_id) REFERENCES mobile_devices (id),
	FOREIGN KEY fk_request_type (request_type_id) REFERENCES mobile_request_types (id),
	FOREIGN KEY fk_data_plan (data_plan_id) REFERENCES mobile_data_plans (id),
	FOREIGN KEY fk_carrier (current_carrier_id) REFERENCES mobile_carriers (id),
	FOREIGN KEY fk_approver (approver_service_number) REFERENCES users (service_number)
) ENGINE=InnoDB;

CREATE TABLE mobile_request_services (
	mobile_request_id INT NOT NULL,
	service_id INT NOT NULL,
	PRIMARY KEY (mobile_request_id, service_id),
	FOREIGN KEY fk_mobile_request (mobile_request_id) REFERENCES mobile_requests (id),
	FOREIGN KEY fk_mobile_service (service_id) REFERENCES mobile_services (id)
) ENGINE=InnoDB;

CREATE TABLE mobile_request_accessories (
	mobile_request_id INT NOT NULL,
	accessory_id INT NOT NULL,
	PRIMARY KEY (mobile_request_id, accessory_id),
	FOREIGN KEY fk_mobile_request (mobile_request_id) REFERENCES mobile_requests (id),
	FOREIGN KEY fk_mobile_accessory (accessory_id) REFERENCES mobile_accessories (id)
) ENGINE=InnoDB;