migrations/phase_1_3_manual.sql

-- Phase 1.3 manual database repair for existing PostgreSQL databases.
-- Safe to run more than once. Mirrors startup guards in app.core.database.

ALTER TABLE IF EXISTS previous_papers
  ADD COLUMN IF NOT EXISTS board TEXT,
  ADD COLUMN IF NOT EXISTS class_level TEXT,
  ADD COLUMN IF NOT EXISTS source_url TEXT,
  ADD COLUMN IF NOT EXISTS source_domain TEXT,
  ADD COLUMN IF NOT EXISTS source_title TEXT,
  ADD COLUMN IF NOT EXISTS retrieved_at TIMESTAMP,
  ADD COLUMN IF NOT EXISTS file_hash TEXT,
  ADD COLUMN IF NOT EXISTS verification_status TEXT NOT NULL DEFAULT 'verified',
  ADD COLUMN IF NOT EXISTS confidence_score FLOAT,
  ADD COLUMN IF NOT EXISTS official_source BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS notes TEXT;

CREATE INDEX IF NOT EXISTS ix_previous_papers_file_hash
  ON previous_papers (file_hash);

CREATE INDEX IF NOT EXISTS ix_previous_papers_verification_status
  ON previous_papers (verification_status);

ALTER TABLE IF EXISTS video_render_jobs
  ADD COLUMN IF NOT EXISTS source_document_id TEXT,
  ADD COLUMN IF NOT EXISTS evidence_label TEXT,
  ADD COLUMN IF NOT EXISTS target_duration_seconds FLOAT,
  ADD COLUMN IF NOT EXISTS audio_duration_seconds FLOAT,
  ADD COLUMN IF NOT EXISTS render_duration_seconds FLOAT,
  ADD COLUMN IF NOT EXISTS scene_audio_statuses_json JSON;