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

INSERT INTO operating_regions VALUES
	(1, 'North'),
	(2, 'Central'),
	(3, 'South');

ALTER TABLE operating_areas ADD COLUMN op_region_id INT DEFAULT NULL AFTER id;
ALTER TABLE operating_areas ADD FOREIGN KEY idx_op_region_id (op_region_id) REFERENCES operating_regions (id);

INSERT INTO operating_areas (op_region_id, name) VALUES (1, 'Newcastle');

UPDATE operating_areas SET op_region_id = 1 WHERE name IN ('Newcastle', 'Central Coast');
UPDATE operating_areas SET op_region_id = 2 WHERE name IN ('Dee Why', 'North');
UPDATE operating_areas SET op_region_id = 3 WHERE name IN ('Bankstown', 'East', 'City', 'South');

ALTER TABLE operating_areas CHANGE COLUMN op_region_id op_region_id INT NOT NULL;


DELETE FROM capacities WHERE operating_area_id NOT IN (SELECT id FROM operating_areas);

ALTER TABLE capacities DROP FOREIGN KEY capacities_ibfk_1;

ALTER TABLE capacities ADD FOREIGN KEY (operating_area_id) REFERENCES operating_areas (id);
