-- User-driven category system: organizers create categories by label,
-- system reuses existing ones, orphans get garbage-collected.

ALTER TABLE `stage_categories`
  ADD COLUMN `created_by` char(36) DEFAULT NULL;

ALTER TABLE `stage_categories`
  DROP COLUMN `group_name`;

-- Case-insensitive unique on utf8mb4_0900_ai_ci collation
ALTER TABLE `stage_categories`
  ADD UNIQUE KEY `uq_label` (`label`(100));

-- Registration-to-category link table (was in init.sql but missing from prod MySQL)
CREATE TABLE IF NOT EXISTS `registration_category_assignments` (
  `registration_id` char(36) NOT NULL,
  `category_id` char(36) NOT NULL,
  PRIMARY KEY (`registration_id`, `category_id`),
  CONSTRAINT `fk_rca_registration` FOREIGN KEY (`registration_id`) REFERENCES `registrations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rca_category` FOREIGN KEY (`category_id`) REFERENCES `stage_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;