-- news/news_enricher/create_table.sql
-- Dedupe log for the News Wire enricher. Parallel to outage_news_log:
-- one row per (source_topic_id), so the bot can't reply twice to the
-- same thin post even if it dies mid-run.
--
-- Lives in the dedicated `news` database (NOT `outages`). Run as:
--   USE news;  -- or pass DB name on CLI
--   then the CREATE TABLE below
-- Migrated out of `outages` DB on 2026-05-15.

CREATE TABLE IF NOT EXISTS news_enricher_log (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    source_topic_id INT UNSIGNED NOT NULL,           -- yabbse_topics.id_topic of the thin post
    source_msg_id   INT UNSIGNED NOT NULL,           -- yabbse_messages.id_msg of the thin post
    source_member   INT UNSIGNED NULL,               -- id_member who made the thin post
    source_url      VARCHAR(512) NULL,               -- URL extracted from the thin post
    reply_topic_id  INT UNSIGNED NULL,               -- topic the reply went into (same as source_topic_id)
    reply_msg_id    INT UNSIGNED NULL,               -- yabbse_messages.id_msg of our reply
    posted_at       DATETIME     NOT NULL,
    status          ENUM('posted','error','dry_run','skipped_fetch') NOT NULL DEFAULT 'posted',
    payload_json    LONGTEXT     NULL,               -- OG metadata + rendered body for forensics
    error_msg       TEXT         NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_source_topic (source_topic_id),
    KEY idx_posted (posted_at),
    KEY idx_source_member (source_member)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SHOW CREATE TABLE news_enricher_log\G