-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Event log — source of truth for all memory
CREATE TABLE IF NOT EXISTS events (
  id BIGSERIAL PRIMARY KEY,
  project VARCHAR NOT NULL,
  query_id VARCHAR NOT NULL,
  run_id VARCHAR NOT NULL,
  turn INT NOT NULL,
  event_type VARCHAR NOT NULL,  -- "ingest", "gate_update", "gate_exit", "synthesis"
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  data JSONB NOT NULL,
  UNIQUE(project, query_id, run_id, turn)
);

CREATE INDEX idx_events_project_query ON events(project, query_id);
CREATE INDEX idx_events_run ON events(run_id);
CREATE INDEX idx_events_type ON events(event_type);

-- L0: Evidence chunks (raw, with source reference)
CREATE TABLE IF NOT EXISTS chunks_l0 (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project VARCHAR NOT NULL,
  query_id VARCHAR NOT NULL,
  source VARCHAR NOT NULL,  -- "pi", "claude", "transcript"
  content TEXT NOT NULL,
  tokens INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_chunks_l0_project_query ON chunks_l0(project, query_id);

-- L1: Per-query memories (one per standing query, up to 1024 tokens)
CREATE TABLE IF NOT EXISTS memories_l1 (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project VARCHAR NOT NULL,
  query_id VARCHAR NOT NULL,
  content TEXT NOT NULL,
  tokens INT NOT NULL,
  embedding vector(768),  -- nomic-embed-text-v2-moe
  chunks_seen INT NOT NULL,
  chunks_used INT NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  run_id VARCHAR NOT NULL,
  UNIQUE(project, query_id)
);

CREATE INDEX idx_memories_l1_project ON memories_l1(project);
CREATE INDEX idx_memories_l1_embedding ON memories_l1 USING ivfflat (embedding vector_cosine_ops);

-- L1 -> L0 provenance (which evidence chunks produced this memory)
CREATE TABLE IF NOT EXISTS l1_l0_edges (
  l1_id UUID REFERENCES memories_l1(id) ON DELETE CASCADE,
  l0_id UUID REFERENCES chunks_l0(id) ON DELETE CASCADE,
  PRIMARY KEY (l1_id, l0_id)
);

-- L2: Project synthesis (one per project, up to 1024 tokens)
CREATE TABLE IF NOT EXISTS memories_l2 (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project VARCHAR NOT NULL UNIQUE,
  content TEXT NOT NULL,
  tokens INT NOT NULL,
  embedding vector(768),
  l1_count INT NOT NULL,  -- how many L1 memories were used
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  run_id VARCHAR NOT NULL
);

CREATE INDEX idx_memories_l2_project ON memories_l2(project);
CREATE INDEX idx_memories_l2_embedding ON memories_l2 USING ivfflat (embedding vector_cosine_ops);

-- L2 -> L1 provenance (which L1 memories produced this synthesis)
CREATE TABLE IF NOT EXISTS l2_l1_edges (
  l2_id UUID REFERENCES memories_l2(id) ON DELETE CASCADE,
  l1_id UUID REFERENCES memories_l1(id) ON DELETE CASCADE,
  PRIMARY KEY (l2_id, l1_id)
);

-- Reference corpus (not gated, used in queries)
CREATE TABLE IF NOT EXISTS reference_corpus (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project VARCHAR NOT NULL,
  name VARCHAR NOT NULL,  -- doc name or skill name
  content TEXT NOT NULL,
  embedding vector(768),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(project, name)
);

CREATE INDEX idx_corpus_project ON reference_corpus(project);
CREATE INDEX idx_corpus_embedding ON reference_corpus USING ivfflat (embedding vector_cosine_ops);

-- Ingest jobs (async queue)
CREATE TABLE IF NOT EXISTS ingest_jobs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project VARCHAR NOT NULL,
  ingest_id VARCHAR NOT NULL UNIQUE,
  status VARCHAR NOT NULL DEFAULT 'pending',  -- pending, processing, done, failed
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  error TEXT
);

CREATE INDEX idx_ingest_jobs_project ON ingest_jobs(project);
CREATE INDEX idx_ingest_jobs_status ON ingest_jobs(status);

-- Skills extracted from memories
CREATE TABLE IF NOT EXISTS skills (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project VARCHAR NOT NULL,
  name VARCHAR NOT NULL,
  description TEXT NOT NULL,
  when_to_use TEXT,
  examples TEXT,
  l1_source UUID NOT NULL REFERENCES memories_l1(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(project, name)
);

CREATE INDEX idx_skills_project ON skills(project);