-- Priority Forge — pgvector memory-hub adapter schema bootstrap.
--
-- Run once against the Postgres instance you point PF at. The adapter's
-- health() call also reports the missing pieces if you skip this.
--
--   PGPASSWORD=... psql -h <host> -U <user> -d <db> -f setup-pgvector.sql

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS team_memory_events (
  event_id      UUID PRIMARY KEY,
  team_id       TEXT NOT NULL,
  agent_id      TEXT NOT NULL,
  workspace_id  TEXT NOT NULL,
  event_type    TEXT NOT NULL,
  ts            TIMESTAMPTZ NOT NULL,
  payload       JSONB NOT NULL,
  summary       TEXT NOT NULL,
  metadata      JSONB NOT NULL DEFAULT '{}'::jsonb,
  embedding     VECTOR(384),                 -- nullable so adapter can backfill
  inserted_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_team_memory_events_team_ts
  ON team_memory_events (team_id, ts DESC);

CREATE INDEX IF NOT EXISTS idx_team_memory_events_agent
  ON team_memory_events (team_id, agent_id);

CREATE INDEX IF NOT EXISTS idx_team_memory_events_type
  ON team_memory_events (team_id, event_type);

-- ivfflat needs ANALYZE-derived stats; build after some rows exist, or
-- leave un-indexed initially (sequential scan is fine up to ~10K rows).
-- CREATE INDEX IF NOT EXISTS idx_team_memory_events_embedding
--   ON team_memory_events USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
