-- Search-box.ai
-- Author: Simon-Pierre Boucher
-- Contact: contact@spboucher.ai
-- File: packages/db/migrations/001_init.sql
-- Description: Initial database schema for research sessions, events and research state.

CREATE TABLE IF NOT EXISTS research_sessions (
  id            text PRIMARY KEY,
  question      text NOT NULL,
  status        text NOT NULL DEFAULT 'pending',
  answer        text,
  error         text,
  objectives    jsonb NOT NULL DEFAULT '[]',
  budgets       jsonb NOT NULL DEFAULT '{}',
  meta          jsonb NOT NULL DEFAULT '{}',
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS research_events (
  seq           bigserial PRIMARY KEY,
  id            text NOT NULL UNIQUE,
  session_id    text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,
  type          text NOT NULL,
  payload       jsonb NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS research_events_session_seq ON research_events (session_id, seq);

CREATE TABLE IF NOT EXISTS sources (
  id             text PRIMARY KEY,
  session_id     text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,
  url            text NOT NULL,
  title          text,
  domain         text NOT NULL,
  status         text NOT NULL DEFAULT 'found',
  citation_index integer,
  content        text,
  metadata       jsonb NOT NULL DEFAULT '{}',
  created_at     timestamptz NOT NULL DEFAULT now(),
  UNIQUE (session_id, url)
);

CREATE TABLE IF NOT EXISTS evidence (
  id            text PRIMARY KEY,
  session_id    text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,
  source_id     text NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
  claim_id      text,
  quote         text NOT NULL,
  note          text,
  stance        text NOT NULL DEFAULT 'context',
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS evidence_session ON evidence (session_id);

CREATE TABLE IF NOT EXISTS claims (
  id            text PRIMARY KEY,
  session_id    text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,
  text          text NOT NULL,
  status        text NOT NULL DEFAULT 'exploring',
  confidence    real NOT NULL DEFAULT 0.5,
  public_reason text,
  created_at    timestamptz NOT NULL DEFAULT now(),
  updated_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS claims_session ON claims (session_id);

CREATE TABLE IF NOT EXISTS contradictions (
  id            text PRIMARY KEY,
  session_id    text NOT NULL REFERENCES research_sessions(id) ON DELETE CASCADE,
  claim_id      text NOT NULL REFERENCES claims(id) ON DELETE CASCADE,
  description   text NOT NULL,
  evidence_ids  jsonb NOT NULL DEFAULT '[]',
  resolution    text,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS contradictions_session ON contradictions (session_id);