-- Apply after 202609240009. One programme and capacity for every existing event/group.
CREATE TABLE event_programmes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL,
 programme_key VARCHAR(140) NOT NULL,
 title VARCHAR(200) NOT NULL,
 capacity INT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_programme_unit_key(unit_id,programme_key),
 UNIQUE KEY uq_programme_id_unit(id,unit_id),
 CONSTRAINT fk_programme_unit FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE meetings ADD COLUMN programme_id BIGINT UNSIGNED NULL AFTER unit_id;
INSERT INTO event_programmes(unit_id,programme_key,title,capacity)
SELECT m.unit_id,
 CASE WHEN m.linked_event_group IS NOT NULL AND m.linked_event_group<>''
      THEN CONCAT('group:',m.linked_event_group) ELSE CONCAT('event:',m.id) END,
 MIN(m.title),
 COALESCE(MIN(CASE WHEN m.archived=0 THEN m.dining_capacity END),MIN(m.dining_capacity))
FROM meetings m
GROUP BY m.unit_id, CASE WHEN m.linked_event_group IS NOT NULL AND m.linked_event_group<>''
      THEN CONCAT('group:',m.linked_event_group) ELSE CONCAT('event:',m.id) END;
UPDATE meetings m JOIN event_programmes p ON p.unit_id=m.unit_id AND p.programme_key=
 CASE WHEN m.linked_event_group IS NOT NULL AND m.linked_event_group<>''
      THEN CONCAT('group:',m.linked_event_group) ELSE CONCAT('event:',m.id) END
SET m.programme_id=p.id,m.dining_capacity=p.capacity;
ALTER TABLE meetings
 ADD KEY idx_meeting_programme(programme_id,unit_id),
 ADD CONSTRAINT fk_meeting_programme_unit FOREIGN KEY(programme_id,unit_id) REFERENCES event_programmes(id,unit_id);