-- migration_pgvector.sql — S569: pgvector su Supabase per semantic memory
--
-- Esegui UNA VOLTA nel SQL Editor di Supabase (Project → SQL Editor → New Query).
-- Riavvia il backend dopo l'esecuzione.
--
-- Prerequisito: la tabella semantic_memory deve già esistere.
-- Se non esiste, creala prima:
--   CREATE TABLE IF NOT EXISTS public.semantic_memory (
--     id       TEXT PRIMARY KEY,
--     content  TEXT NOT NULL,
--     metadata JSONB DEFAULT '{}'
--   );


-- ── 1. Estensione pgvector ────────────────────────────────────────────────────
-- Disponibile su tutti i piani Supabase (PostgreSQL ≥ 14.x).
CREATE EXTENSION IF NOT EXISTS vector;


-- ── 2. Colonna embedding ──────────────────────────────────────────────────────
-- BAAI/bge-small-en-v1.5 produce vettori a 384 dimensioni.
ALTER TABLE public.semantic_memory
  ADD COLUMN IF NOT EXISTS embedding vector(384);


-- ── 3. Funzione RPC per similarity search ────────────────────────────────────
-- Chiamata da semantic.py: self._sb.rpc('match_semantic_memory', {...}).execute()
-- match_threshold 0.2 = soglia coseno minima; 1.0 = identico, 0.0 = ortogonale.
CREATE OR REPLACE FUNCTION match_semantic_memory(
  query_embedding vector(384),
  match_count     int   DEFAULT 5,
  match_threshold float DEFAULT 0.2
)
RETURNS TABLE (
  id         text,
  content    text,
  metadata   jsonb,
  similarity float
)
LANGUAGE sql STABLE
AS $$
  SELECT
    id,
    content,
    metadata,
    (1 - (embedding <=> query_embedding))::float AS similarity
  FROM public.semantic_memory
  WHERE embedding IS NOT NULL
    AND (1 - (embedding <=> query_embedding)) > match_threshold
  ORDER BY embedding <=> query_embedding
  LIMIT match_count;
$$;


-- ── 4. Indice IVFFlat per ricerca ANN veloce (opzionale) ─────────────────────
-- Utile oltre ~1000 righe. Se la tabella è vuota o piccola, salta per ora.
-- Decommentare quando il numero di documenti supera 1000:
--
-- CREATE INDEX IF NOT EXISTS semantic_memory_embedding_idx
--   ON public.semantic_memory
--   USING ivfflat (embedding vector_cosine_ops)
--   WITH (lists = 100);
--
-- Per tabelle molto grandi (>100K righe) preferire HNSW:
-- CREATE INDEX IF NOT EXISTS semantic_memory_embedding_hnsw_idx
--   ON public.semantic_memory
--   USING hnsw (embedding vector_cosine_ops)
--   WITH (m = 16, ef_construction = 64);


-- ── Verifica ──────────────────────────────────────────────────────────────────
-- Esegui queste query per confermare che tutto sia a posto:
--
-- SELECT column_name, data_type
--   FROM information_schema.columns
--  WHERE table_name = 'semantic_memory';
--
-- SELECT proname FROM pg_proc WHERE proname = 'match_semantic_memory';