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

-- Create table (idempotent) with isActive flag
CREATE TABLE IF NOT EXISTS documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT,            -- corresponds to Document.pageContent
  metadata JSONB,          -- corresponds to Document.metadata
  embedding VECTOR(1536),  -- 1536 works for OpenAI embeddings, change if needed
  "isActive" BOOLEAN NOT NULL DEFAULT TRUE
);

-- Backfill column if table already existed without it
ALTER TABLE documents
  ADD COLUMN IF NOT EXISTS "isActive" BOOLEAN NOT NULL DEFAULT TRUE;

-- Useful indexes (optional but recommended)
-- Cosine distance index for pgvector (because we use <=>)
CREATE INDEX IF NOT EXISTS documents_embedding_ivfflat_cosine_active_idx
  ON documents USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100)
  WHERE "isActive" IS TRUE AND embedding IS NOT NULL;

-- GIN index for metadata filters
CREATE INDEX IF NOT EXISTS documents_metadata_gin_idx
  ON documents USING gin (metadata);

-- (Re)create search function: active rows only, null-safe, and requires non-null embeddings
CREATE OR REPLACE FUNCTION match_documents (
  query_embedding VECTOR(1536),
  match_count INT DEFAULT NULL,
  filter JSONB DEFAULT '{}'::jsonb
) RETURNS TABLE (
  id BIGINT,
  content TEXT,
  metadata JSONB,
  similarity FLOAT
)
LANGUAGE plpgsql
AS $$
#variable_conflict use_column
BEGIN
  RETURN QUERY
  SELECT
    d.id,
    d.content,
    d.metadata,
    1 - (d.embedding <=> query_embedding) AS similarity
  FROM documents AS d
  WHERE d.embedding IS NOT NULL
    AND COALESCE(d."isActive", FALSE) = TRUE
    AND COALESCE(d.metadata, '{}'::jsonb) @> COALESCE(filter, '{}'::jsonb)
  ORDER BY d.embedding <=> query_embedding
  LIMIT COALESCE(match_count, 10);  -- default to 10 results
END;
$$;