-- V5: Add truth scoring columns for the Truth Engine integration
-- Part of: https://gitea.ingwaz.work/Ingwaz/openbrain-mcp/issues/31
--
-- All columns are nullable — existing memories start unscored and
-- get populated by the background truth scoring worker.
--
-- Rollback (manual):
--   ALTER TABLE memories
--     DROP COLUMN IF EXISTS truth_value,
--     DROP COLUMN IF EXISTS truth_confidence,
--     DROP COLUMN IF EXISTS truth_category,
--     DROP COLUMN IF EXISTS truth_evaluated_at,
--     DROP COLUMN IF EXISTS ecan_sti,
--     DROP COLUMN IF EXISTS ecan_lti;
--   DROP INDEX IF EXISTS idx_memories_truth_category;
--   DROP INDEX IF EXISTS idx_memories_truth_unevaluated;

ALTER TABLE memories
  ADD COLUMN truth_value        REAL,
  ADD COLUMN truth_confidence    REAL,
  ADD COLUMN truth_category      TEXT,
  ADD COLUMN truth_evaluated_at  TIMESTAMPTZ,
  ADD COLUMN ecan_sti            REAL,
  ADD COLUMN ecan_lti            REAL;

-- Partial index: quickly find memories by truth category
CREATE INDEX idx_memories_truth_category
  ON memories (truth_category)
  WHERE truth_category IS NOT NULL;

-- Partial index: efficiently find unscored memories for the background worker
CREATE INDEX idx_memories_truth_unevaluated
  ON memories (created_at)
  WHERE truth_evaluated_at IS NULL;