ALTER TABLE `event_registrations`
  ADD COLUMN `phone` VARCHAR(32) NULL AFTER `email`,
  ADD COLUMN `reminder_opt_in` TINYINT(1) NOT NULL DEFAULT 0 AFTER `phone`,
  ADD COLUMN `confirmed_at` VARCHAR(40) NULL AFTER `reminder_opt_in`,
  ADD KEY `registration_reminder_index` (`event_id`, `reminder_opt_in`, `status`);

UPDATE `event_registrations`
SET `confirmed_at` = COALESCE(`updated_at`, `created_at`)
WHERE `status` IN ('confirmed', 'waitlisted') AND `confirmed_at` IS NULL;

CREATE TABLE `registration_confirmation_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `registration_id` BIGINT UNSIGNED NOT NULL,
  `token_hash` CHAR(64) NOT NULL,
  `expires_at` VARCHAR(40) NOT NULL,
  `used_at` VARCHAR(40) NULL,
  `created_at` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `registration_confirmation_token_unique` (`token_hash`),
  KEY `registration_confirmation_registration_index` (`registration_id`, `expires_at`),
  CONSTRAINT `registration_confirmation_registration_fk`
    FOREIGN KEY (`registration_id`) REFERENCES `event_registrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
