-- Requires: AOCore Trino plugin (Obj E); model catalog (Obj C) seeded with the referenced model
--           Lance vector index registered in catalog (Obj D); vector_index.index_ready = true
--           AOCORE_LANCE_RETRIEVAL_ENABLED=true on the AOCore gateway
-- Function:  SEMANTIC_SEARCH(vector_table VARCHAR, query_text VARCHAR, top_k INTEGER) → ARRAY<ROW(doc_id VARCHAR, score DOUBLE)>
-- Docs:      docs/developer/ai-sql-functions.md#semantic_search

-- NOTE: The embedding model is resolved SERVER-SIDE from the vector index's
-- default_embedding_model. You do NOT specify the model in the SQL call — this
-- ensures the query vector and the index vectors always use the same model.

-- ─────────────────────────────────────────────────────────────────────────
-- Example 1: Basic semantic search (top-10 results)
-- ─────────────────────────────────────────────────────────────────────────
SELECT s.doc_id, s.score
FROM UNNEST(SEMANTIC_SEARCH('kb_vectors', 'what is the AOCore lakehouse?', 10)) AS s(doc_id, score)
ORDER BY s.score DESC;
-- Expected output schema:
--   doc_id VARCHAR — the catalog-stored document id from the Lance index
--   score  DOUBLE  — ANN similarity score (higher = more similar)


-- ─────────────────────────────────────────────────────────────────────────
-- Example 2: JOIN with document table to retrieve full content (CROSS JOIN UNNEST)
-- ─────────────────────────────────────────────────────────────────────────
SELECT d.id, d.title, d.content, s.score
FROM documents d
CROSS JOIN UNNEST(SEMANTIC_SEARCH('kb_vectors', 'how does eval gate work?', 5)) AS s(doc_id, score)
WHERE d.id = s.doc_id
ORDER BY s.score DESC;
-- Expected output schema:
--   id, title, content (from documents table)
--   score DOUBLE

-- Performance note: each SEMANTIC_SEARCH call performs two steps server-side:
--   1. embed the query_text (ProxyService.ExecuteEmbedding; 50–200ms typical)
--   2. Lance ANN search in-process (D-02 shim; <10ms warm index)
-- Total expected latency per call: 60–250ms steady-state (warm cache, warm index).
-- For batch workloads applying SEMANTIC_SEARCH per row, materialize the query
-- column to a temp table first (see ai-sql-aware-query-patterns.sql Pattern 3).


-- ─────────────────────────────────────────────────────────────────────────
-- Example 3: Semantic search with additional filters
-- ─────────────────────────────────────────────────────────────────────────
-- The SEMANTIC_SEARCH result is the ANN search result; apply relational filters
-- via the JOIN predicate.
SELECT d.id, d.title, d.category, d.created_at, s.score
FROM documents d
CROSS JOIN UNNEST(SEMANTIC_SEARCH('kb_vectors', 'authentication and API keys', 10)) AS s(doc_id, score)
WHERE d.id = s.doc_id
  AND d.category = 'security'
  AND d.created_at > now() - INTERVAL '90' DAY
ORDER BY s.score DESC
LIMIT 5;


-- ─────────────────────────────────────────────────────────────────────────
-- Example 4: Multiple SEMANTIC_SEARCH calls in one query
-- ─────────────────────────────────────────────────────────────────────────
-- Each SEMANTIC_SEARCH call is a separate batch item; each emits its own
-- AIBOM chain. Two independent searches in one SELECT:
WITH support_results AS (
    SELECT s.doc_id, s.score, 'support' AS source
    FROM UNNEST(SEMANTIC_SEARCH('support_vectors', 'rate limit errors', 5)) AS s(doc_id, score)
),
kb_results AS (
    SELECT s.doc_id, s.score, 'kb' AS source
    FROM UNNEST(SEMANTIC_SEARCH('kb_vectors', 'rate limit errors', 5)) AS s(doc_id, score)
)
SELECT doc_id, score, source
FROM support_results
UNION ALL
SELECT doc_id, score, source
FROM kb_results
ORDER BY score DESC;


-- ─────────────────────────────────────────────────────────────────────────
-- Audit trail
-- ─────────────────────────────────────────────────────────────────────────
-- Each SEMANTIC_SEARCH call emits 4 audit chain entries:
--
--   proxy.embedding            — operational (ProxyService, embed step)
--   udf.embed.query            — AIBOM entry 1 (embed provenance)
--   udf.lance.ann_search       — AIBOM entry 2 (ANN search provenance)
--   udf.semantic_search.aibom  — AIBOM tie entry (Obj F join key)
--
-- The 3 AIBOM entries are emitted atomically in one Postgres transaction.
-- To look up AIBOM entries for a specific Trino query:
--
--   SELECT action, updated_values
--   FROM audit_logs
--   WHERE updated_values->>'trino_query_id' = '<query_id>'
--   ORDER BY sequence_number ASC;
--
-- Returns: proxy.embedding, udf.embed.query, udf.lance.ann_search,
--          udf.semantic_search.aibom (in sequence_number order).
--
-- Wire format reference: docs/dev/udf-aibom-chain-wire-format.md (schema_version=1)