-- Requires: AOCore Trino plugin (Obj E); model catalog (Obj C) seeded with the referenced model
-- Function:  EMBED(model VARCHAR, text VARCHAR) → ARRAY<DOUBLE>
-- Docs:      docs/developer/ai-sql-functions.md#embed

-- ─────────────────────────────────────────────────────────────────────────
-- Example 1: Generate embeddings for a document backfill
-- ─────────────────────────────────────────────────────────────────────────
SELECT id,
       EMBED('text-embedding-3-small', title || ' ' || content) AS embedding
FROM documents
WHERE indexed_at IS NULL;
-- Expected output schema:
--   id        (preserves source column type)
--   embedding ARRAY<DOUBLE>  — 1536 dims for text-embedding-3-small; NULL on failure

-- Performance note: EMBED calls are batched (up to 64 per flush; 100ms timeout).
-- Embedding models are lower-latency than chat models (50–200ms typical vs
-- 200ms–2s). For large backfills (>10K rows) materialize the unindexed set to a
-- temp table first:
--   CREATE TABLE unindexed_docs AS SELECT id, title, content FROM documents WHERE indexed_at IS NULL;
--   SELECT id, EMBED('text-embedding-3-small', title || ' ' || content) AS embedding FROM unindexed_docs;


-- ─────────────────────────────────────────────────────────────────────────
-- Example 2: Embed a query string for downstream similarity use
-- ─────────────────────────────────────────────────────────────────────────
-- NOTE: For production semantic search, use SEMANTIC_SEARCH instead of
-- computing EMBED + cosine similarity manually. This pattern is shown only
-- for completeness / custom distance metrics.
WITH query_embed AS (
    SELECT EMBED('text-embedding-3-small', 'machine learning fundamentals') AS qvec
)
SELECT d.id, d.title, d.embedding
FROM documents d, query_embed q
WHERE d.embedding IS NOT NULL
  AND q.qvec IS NOT NULL
ORDER BY cosine_distance(d.embedding, q.qvec)   -- hypothetical custom distance function
LIMIT 10;
-- hypothetical cosine_distance — may not exist in your Trino deployment.
-- Use SEMANTIC_SEARCH('my_vector_index', 'machine learning fundamentals', 10) instead.


-- ─────────────────────────────────────────────────────────────────────────
-- Example 3: Write embeddings back to an index table
-- ─────────────────────────────────────────────────────────────────────────
-- After embedding, insert into a Lance-backed vector table (provisioned by Obj D):
INSERT INTO kb_vectors (id, content, embedding)
SELECT id, content,
       EMBED('text-embedding-3-small', content) AS embedding
FROM knowledge_articles
WHERE EMBED('text-embedding-3-small', content) IS NOT NULL;
-- Note: the double EMBED call here evaluates twice. Prefer to materialize:
--   WITH embedded AS (SELECT id, content, EMBED('text-embedding-3-small', content) AS emb FROM ...)
--   INSERT INTO kb_vectors SELECT id, content, emb FROM embedded WHERE emb IS NOT NULL;


-- ─────────────────────────────────────────────────────────────────────────
-- Audit trail
-- ─────────────────────────────────────────────────────────────────────────
-- Each EMBED call emits:
--   proxy.embedding    — operational record (ProxyService)
--   udf.eval_gate.pass — CM-3 evidence if the model has an eval gate configured