-- Migration 005: structured facts extracted by cf-harvest
-- payload stores the type-specific JSON verbatim — no field flattening.
-- fact_hash deduplicates identical facts across re-ingests.

CREATE TABLE IF NOT EXISTS knowledge_facts (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    source_id   INTEGER NOT NULL REFERENCES knowledge_sources(id) ON DELETE CASCADE,
    fact_type   TEXT    NOT NULL
                        CHECK (fact_type IN (
                            'companion_planting', 'soil_amendment', 'propagation',
                            'pest_diagnosis', 'harvest_timing', 'instruction',
                            'medicinal', 'history_context'
                        )),
    subject     TEXT,
    payload     TEXT    NOT NULL,
    presenter   TEXT,
    confidence  REAL,
    fact_hash   TEXT    NOT NULL UNIQUE,
    created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_knowledge_facts_type      ON knowledge_facts (fact_type);
CREATE INDEX IF NOT EXISTS idx_knowledge_facts_subject   ON knowledge_facts (subject);
CREATE INDEX IF NOT EXISTS idx_knowledge_facts_source_id ON knowledge_facts (source_id);