-- Requires: AOCore Trino plugin (Obj E); model catalog (Obj C) seeded with the referenced model
-- Function:  RERANK(model VARCHAR, query VARCHAR, candidates ARRAY<VARCHAR>) → ARRAY<ROW(doc_id VARCHAR, score DOUBLE)>
-- Docs:      docs/developer/ai-sql-functions.md#rerank

-- ─────────────────────────────────────────────────────────────────────────
-- Example 1: Rerank a fixed candidate list
-- ─────────────────────────────────────────────────────────────────────────
SELECT r.doc_id, r.score
FROM UNNEST(
    RERANK(
        'cohere-rerank-3',
        'machine learning tutorials for beginners',
        ARRAY['ML 101 guide', 'Deep learning intro', 'Linear algebra basics', 'Python for data science']
    )
) AS r(doc_id, score)
ORDER BY r.score DESC;
-- Expected output schema:
--   doc_id VARCHAR — the candidate text string (as passed in ARRAY; identifies by content not index)
--   score  DOUBLE  — relevance score (higher = more relevant)

-- Performance note: the entire ARRAY<VARCHAR> is dispatched as one batch item.
-- Rerank models are typically lower-latency than chat models (50–200ms typical).


-- ─────────────────────────────────────────────────────────────────────────
-- Example 2: Rerank top-K documents from a table
-- ─────────────────────────────────────────────────────────────────────────
-- Gather candidate documents, then re-rank with a query string.
WITH candidates AS (
    SELECT id, title, content
    FROM knowledge_articles
    WHERE category = 'tutorial'
    ORDER BY views DESC
    LIMIT 20
),
reranked AS (
    SELECT r.doc_id, r.score
    FROM UNNEST(
        RERANK(
            'cohere-rerank-3',
            'machine learning tutorials',
            ARRAY(SELECT title FROM candidates)
        )
    ) AS r(doc_id, score)
)
SELECT c.id, c.title, rr.score
FROM candidates c
JOIN reranked rr ON c.title = rr.doc_id
ORDER BY rr.score DESC
LIMIT 5;
-- NOTE: doc_id is the candidate text (title in this case), so the JOIN is on
-- title = doc_id. If titles are not unique, use SEMANTIC_SEARCH + RERANK
-- hybrid retrieval instead (see ai-sql-aware-query-patterns.sql Pattern 4).


-- ─────────────────────────────────────────────────────────────────────────
-- Example 3: Rerank search results in a pipeline
-- ─────────────────────────────────────────────────────────────────────────
-- Use RERANK after a keyword or BM25 search to improve result quality:
WITH bm25_results AS (
    SELECT id, title, snippet,
           rank() OVER (ORDER BY fts_score DESC) AS bm25_rank
    FROM search_results
    WHERE query_id = 'q-12345'
    ORDER BY fts_score DESC
    LIMIT 25
),
reranked AS (
    SELECT r.doc_id, r.score AS rerank_score
    FROM UNNEST(
        RERANK(
            'cohere-rerank-3',
            'how to configure rate limiting',
            ARRAY(SELECT snippet FROM bm25_results)
        )
    ) AS r(doc_id, score)
)
SELECT b.id, b.title, b.bm25_rank, rr.rerank_score
FROM bm25_results b
JOIN reranked rr ON b.snippet = rr.doc_id
ORDER BY rr.rerank_score DESC
LIMIT 10;


-- ─────────────────────────────────────────────────────────────────────────
-- Error handling
-- ─────────────────────────────────────────────────────────────────────────
-- Per-item failures return NULL for the entire ARRAY<ROW> result for that row.
-- RERANK has no per-item AIBOM chain — it is a single-step operation.
-- Check Trino worker WARNING logs for error codes (budget_exceeded, rate_limited,
-- guardrail_blocked, model_not_found, etc.).


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