CREATE TABLE IF NOT EXISTS editorial_themes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  theme_key VARCHAR(100) NOT NULL UNIQUE,
  title VARCHAR(180) NOT NULL,
  summary TEXT NOT NULL,
  signal_count INT UNSIGNED NOT NULL DEFAULT 0,
  momentum_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
  projects_json JSON NOT NULL,
  evidence_json JSON NOT NULL,
  refreshed_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX(momentum_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS editorial_content_drafts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  editorial_theme_id BIGINT UNSIGNED NOT NULL,
  project_slug VARCHAR(80) NOT NULL,
  format VARCHAR(40) NOT NULL,
  title VARCHAR(220) NOT NULL,
  draft_text MEDIUMTEXT NOT NULL,
  sources_json JSON NOT NULL,
  status ENUM('prepared','reviewed','rejected') NOT NULL DEFAULT 'prepared',
  created_at DATETIME NOT NULL,
  reviewed_at DATETIME NULL,
  INDEX(project_slug,status),
  FOREIGN KEY(editorial_theme_id) REFERENCES editorial_themes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
