CREATE TABLE IF NOT EXISTS editorial_months (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  month_key CHAR(7) NOT NULL UNIQUE,
  title VARCHAR(180) NOT NULL,
  strategy_note TEXT NULL,
  status ENUM('planning','active','closed') NOT NULL DEFAULT 'planning',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS editorial_topics (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  editorial_month_id BIGINT UNSIGNED NOT NULL,
  source_kind ENUM('vera','manual','mandatory') NOT NULL,
  source_key VARCHAR(140) NULL,
  title VARCHAR(220) NOT NULL,
  angle TEXT NULL,
  project_slug VARCHAR(80) NOT NULL,
  objective VARCHAR(100) NOT NULL DEFAULT 'transmission',
  preferred_channel VARCHAR(40) NOT NULL DEFAULT 'site',
  channels_json JSON NOT NULL,
  rationale TEXT NULL,
  product_label VARCHAR(180) NULL,
  mandatory TINYINT(1) NOT NULL DEFAULT 0,
  priority_score TINYINT UNSIGNED NOT NULL DEFAULT 50,
  status ENUM('proposed','selected','planned','drafted','done','ignored') NOT NULL DEFAULT 'proposed',
  due_date DATE NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  UNIQUE KEY month_source_unique(editorial_month_id,source_kind,source_key),
  INDEX(status,priority_score),
  INDEX(project_slug,due_date),
  FOREIGN KEY(editorial_month_id) REFERENCES editorial_months(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
