CREATE TABLE IF NOT EXISTS echo_casting_candidates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  generation_no SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  hook_id BIGINT UNSIGNED NULL,
  title VARCHAR(255) NOT NULL,
  proposition_text TEXT NOT NULL,
  category_slug VARCHAR(50) NOT NULL,
  tone ENUM('frisson','courant_air','poltergeist') NOT NULL,
  format_slug VARCHAR(40) NOT NULL,
  emotion_slug VARCHAR(50) NOT NULL,
  source_theme VARCHAR(255) NOT NULL DEFAULT '',
  status ENUM('pending','liked','rewrite','rejected') NOT NULL DEFAULT 'pending',
  voted_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  UNIQUE KEY echo_casting_generation_hook(generation_no,hook_id),
  INDEX echo_casting_queue(generation_no,status,id),
  FOREIGN KEY(hook_id) REFERENCES echo_hooks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS echo_casting_generations (
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  generation_no SMALLINT UNSIGNED NOT NULL UNIQUE,
  profile_json JSON NOT NULL,
  candidate_count SMALLINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
