CREATE TABLE IF NOT EXISTS echo_hooks (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  hook_text VARCHAR(500) NOT NULL,
  category_slug VARCHAR(50) NOT NULL,
  tone ENUM('frisson','courant_air','poltergeist') NOT NULL,
  intention VARCHAR(160) NOT NULL DEFAULT '',
  active TINYINT(1) NOT NULL DEFAULT 1,
  usage_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  UNIQUE KEY echo_hook_unique(hook_text),
  INDEX echo_hook_filter(active,category_slug,tone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS echo_drafts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  echo_idea_id BIGINT UNSIGNED NOT NULL,
  echo_hook_id BIGINT UNSIGNED NULL,
  format_slug VARCHAR(40) NOT NULL,
  hook_text VARCHAR(500) NOT NULL,
  draft_text MEDIUMTEXT NOT NULL,
  video_script MEDIUMTEXT NULL,
  visual_brief TEXT NULL,
  vera_checks_json JSON NOT NULL,
  share_score SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('prepared','reviewed','scheduled','published','discarded') NOT NULL DEFAULT 'prepared',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY(echo_idea_id) REFERENCES echo_ideas(id) ON DELETE CASCADE,
  FOREIGN KEY(echo_hook_id) REFERENCES echo_hooks(id) ON DELETE SET NULL,
  INDEX echo_draft_status(status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
