-- Migration 007: kb_ingest_job gains AI-classification staging.
--
-- Task Master #20 (v0.8.0). The two-phase ingest pipeline introduced in
-- this release runs:
--    queued → processing(stage=classifying)
--           → awaiting_review                  (user reviews AI suggestions)
--           → processing(stage=embedding)      (after user commits)
--           → done | failed
--
-- New columns:
--   • status gains 'awaiting_review' value
--   • processing_stage:  'classifying' | 'embedding' | 'upserting' (audit)
--   • ai_suggestions:    JSONB written by the classifier; consumed by
--                        the review screen as default form values
--   • batch_id:          uuid string assigned by the upload route to all
--                        files in one drag-drop drop. Lets the review
--                        screen group N files even after a tab refresh.
--
-- Idempotent — wrapped in a single transaction.
--
-- Run as postgres superuser:
--   docker exec -i <pg-container> psql -U postgres -d insurance_kb \
--     -f /tmp/007_ingest_classifier.sql

BEGIN;

ALTER TABLE kb_ingest_job DROP CONSTRAINT IF EXISTS kb_ingest_job_status_check;
ALTER TABLE kb_ingest_job
  ADD CONSTRAINT kb_ingest_job_status_check
  CHECK (status IN ('queued','processing','awaiting_review','done','failed'));

ALTER TABLE kb_ingest_job
  ADD COLUMN IF NOT EXISTS processing_stage TEXT,
  ADD COLUMN IF NOT EXISTS ai_suggestions   JSONB,
  ADD COLUMN IF NOT EXISTS batch_id         TEXT;

CREATE INDEX IF NOT EXISTS kb_ingest_job_batch_idx ON kb_ingest_job (batch_id);
-- Partial index keeps it tiny: production should have very few rows in
-- 'awaiting_review' at any moment (it's a transient state).
CREATE INDEX IF NOT EXISTS kb_ingest_job_awaiting_idx
  ON kb_ingest_job (created_at DESC) WHERE status = 'awaiting_review';

COMMIT;