ALTER TABLE rooms
    ADD COLUMN event_id CHAR(36) NULL AFTER id;

UPDATE rooms r
JOIN event_days d ON d.id = r.day_id
SET r.event_id = d.event_id;

ALTER TABLE activities
    ADD COLUMN day_id CHAR(36) NULL AFTER id;

UPDATE activities a
JOIN rooms r ON r.id = a.room_id
SET a.day_id = r.day_id;

CREATE TEMPORARY TABLE room_merge_map AS
SELECT r.id AS old_id, grouped.canonical_id
FROM rooms r
JOIN (
    SELECT event_id, LOWER(TRIM(name)) AS normalized_name, MIN(id) AS canonical_id
    FROM rooms
    GROUP BY event_id, LOWER(TRIM(name))
) grouped ON grouped.event_id = r.event_id AND grouped.normalized_name = LOWER(TRIM(r.name));

UPDATE activities a
JOIN room_merge_map m ON m.old_id = a.room_id
SET a.room_id = m.canonical_id;

DELETE r
FROM rooms r
JOIN room_merge_map m ON m.old_id = r.id
WHERE r.id <> m.canonical_id;

DROP TEMPORARY TABLE room_merge_map;

ALTER TABLE rooms
    DROP FOREIGN KEY fk_rooms_day,
    DROP INDEX uq_room_name_day,
    DROP COLUMN day_id,
    MODIFY event_id CHAR(36) NOT NULL,
    ADD UNIQUE KEY uq_room_name_event (event_id, name),
    ADD KEY idx_rooms_event_order (event_id, sort_order),
    ADD CONSTRAINT fk_rooms_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE;

ALTER TABLE activities
    MODIFY day_id CHAR(36) NOT NULL,
    ADD KEY idx_activity_day_room_order (day_id, room_id, sort_order),
    ADD CONSTRAINT fk_activities_day FOREIGN KEY (day_id) REFERENCES event_days(id) ON DELETE CASCADE;