CREATE TABLE `live_friday_sessions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(180) NOT NULL,
  `description` TEXT NULL,
  `starts_at` DATETIME NOT NULL,
  `youtube_url` VARCHAR(2048) NULL,
  `twitch_url` VARCHAR(2048) NULL,
  `replay_url` VARCHAR(2048) NULL,
  `status` ENUM('draft','published') NOT NULL DEFAULT 'draft',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `live_friday_session_start_unique` (`starts_at`),
  KEY `live_friday_session_public_index` (`status`,`starts_at`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `live_friday_reminders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_id` BIGINT UNSIGNED NOT NULL,
  `email` VARCHAR(190) NOT NULL,
  `token_hash` CHAR(64) NULL,
  `token_expires_at` DATETIME NULL,
  `confirmed_at` DATETIME NULL,
  `sent_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `live_friday_reminder_unique` (`session_id`,`email`),
  UNIQUE KEY `live_friday_reminder_token_unique` (`token_hash`),
  KEY `live_friday_reminder_send_index` (`session_id`,`confirmed_at`,`sent_at`),
  CONSTRAINT `live_friday_reminder_session_fk` FOREIGN KEY (`session_id`) REFERENCES `live_friday_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `live_friday_questions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `session_id` BIGINT UNSIGNED NOT NULL,
  `display_name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(190) NULL,
  `message` TEXT NOT NULL,
  `may_read_on_air` TINYINT(1) NOT NULL DEFAULT 0,
  `status` ENUM('new','selected','answered','archived') NOT NULL DEFAULT 'new',
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `live_friday_question_admin_index` (`status`,`created_at`),
  KEY `live_friday_question_session_index` (`session_id`,`created_at`),
  CONSTRAINT `live_friday_question_session_fk` FOREIGN KEY (`session_id`) REFERENCES `live_friday_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
