ALTER TABLE request_types CHANGE COLUMN code code VARCHAR(32) UNIQUE NOT NULL;

INSERT INTO request_types (id, name, code) VALUES
	(7, 'Service Deactivation', 'service_deactivation');

CREATE TABLE service_deactivation_devices (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(255) NOT NULL,
	code VARCHAR(32) NOT NULL UNIQUE,
	active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE service_deactivation_requests (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	request_id INT NOT NULL,
	device_type_id INT NOT NULL,
	phone_number VARCHAR(16) NOT NULL,
	approver_service_number INT NOT NULL,
	FOREIGN KEY idx_request (request_id) REFERENCES requests (id),
	FOREIGN KEY idx_device (device_type_id) REFERENCES service_deactivation_devices (id),
	FOREIGN KEY idx_approver (approver_service_number) REFERENCES users (service_number)
) ENGINE=InnoDB;

INSERT INTO service_deactivation_devices (id, name, code) VALUES
	(1, 'Mobile Phone', 'mobile'),
	(2, 'iPad', 'ipad'),
	(3, 'Broadband Card', 'broadband_card');
