ALTER TABLE capacities CHANGE COLUMN operating_area_id operating_area_id INT NULL;

ALTER TABLE capacities ADD COLUMN op_region_id INT AFTER id;

INSERT INTO capacities (op_region_id, capacity_date, normal_hours, ot_hours)
	SELECT
		op_area.op_region_id, capacity_date, SUM(normal_hours), SUM(ot_hours)
	FROM
		capacities c
		JOIN operating_areas op_area
			ON op_area.id = c.operating_area_id
	GROUP BY op_area.op_region_id, capacity_date;

DELETE FROM capacities WHERE op_region_id IS NULL;
ALTER TABLE capacities CHANGE COLUMN op_region_id op_region_id INT NOT NULL;

ALTER TABLE capacities DROP FOREIGN KEY capacities_ibfk_1;
ALTER TABLE capacities DROP COLUMN operating_area_id;

ALTER TABLE capacities ADD FOREIGN KEY idx_op_region (op_region_id) REFERENCES operating_regions (id);
