PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    canonical_url TEXT NOT NULL UNIQUE,
    source TEXT NOT NULL DEFAULT 'hongcan',
    title TEXT,
    author TEXT,
    published_at TEXT,
    summary TEXT,
    content TEXT,
    content_hash TEXT,
    crawl_status TEXT NOT NULL DEFAULT 'pending',
    retry_count INTEGER NOT NULL DEFAULT 0,
    last_error TEXT,
    discovered_at TEXT NOT NULL,
    crawled_at TEXT,
    updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_articles_published_at
    ON articles(published_at);
CREATE INDEX IF NOT EXISTS idx_articles_crawl_status
    ON articles(crawl_status, retry_count);
CREATE INDEX IF NOT EXISTS idx_articles_content_hash
    ON articles(content_hash);

CREATE TABLE IF NOT EXISTS crawl_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    started_at TEXT NOT NULL,
    finished_at TEXT,
    status TEXT NOT NULL DEFAULT 'running',
    discovered_count INTEGER NOT NULL DEFAULT 0,
    inserted_count INTEGER NOT NULL DEFAULT 0,
    updated_count INTEGER NOT NULL DEFAULT 0,
    failed_count INTEGER NOT NULL DEFAULT 0,
    message TEXT
);

CREATE TABLE IF NOT EXISTS ai_analyses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id INTEGER NOT NULL,
    model TEXT NOT NULL,
    analysis_type TEXT NOT NULL DEFAULT 'editorial',
    result_json TEXT NOT NULL,
    content_hash TEXT,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(article_id, model, analysis_type),
    FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_ai_analyses_article
    ON ai_analyses(article_id, updated_at);

CREATE TABLE IF NOT EXISTS industry_signals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    signal_date TEXT NOT NULL,
    title TEXT NOT NULL,
    summary TEXT NOT NULL,
    trend TEXT NOT NULL DEFAULT 'emerging',
    confidence REAL NOT NULL DEFAULT 0.5,
    implications_json TEXT NOT NULL DEFAULT '[]',
    article_ids_json TEXT NOT NULL DEFAULT '[]',
    tags_json TEXT NOT NULL DEFAULT '[]',
    model TEXT NOT NULL,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE(signal_date, title)
);

CREATE INDEX IF NOT EXISTS idx_industry_signals_date
    ON industry_signals(signal_date DESC, confidence DESC);