-- Requires: AOCore Trino plugin (Obj E); model catalog (Obj C) seeded with the referenced model
-- Function:  LLM_GENERATE(model VARCHAR, prompt VARCHAR) → VARCHAR
--            LLM_GENERATE(model VARCHAR, prompt VARCHAR, params MAP<VARCHAR,VARCHAR>) → VARCHAR
-- Docs:      docs/developer/ai-sql-functions.md#llm_generate

-- ─────────────────────────────────────────────────────────────────────────
-- Example 1: Basic per-row text generation
-- ─────────────────────────────────────────────────────────────────────────
SELECT id,
       LLM_GENERATE('claude-sonnet-4-7', 'Summarize in one sentence: ' || content) AS summary
FROM articles
WHERE published_at > now() - INTERVAL '7' DAY;
-- Expected output schema:
--   id      (preserves source column type)
--   summary VARCHAR  — generated text, or NULL on per-item failure

-- Performance note: the BatchQueue accumulates up to 64 rows (or 100ms) before
-- flushing to POST /v1/batch/udf. For tables larger than ~10K rows, materialize
-- the filtered subset to a temp table first (see ai-sql-aware-query-patterns.sql
-- Pattern 3 — materialized-table best practice).


-- ─────────────────────────────────────────────────────────────────────────
-- Example 2: With params (temperature, max_tokens)
-- ─────────────────────────────────────────────────────────────────────────
SELECT id,
       LLM_GENERATE(
           'claude-sonnet-4-7',
           'Translate to French: ' || content,
           MAP(ARRAY['temperature', 'max_tokens'], ARRAY['0.3', '500'])
       ) AS translation
FROM articles
WHERE language = 'en'
  AND published_at > now() - INTERVAL '1' DAY;
-- Expected output schema:
--   id          (preserves source column type)
--   translation VARCHAR


-- ─────────────────────────────────────────────────────────────────────────
-- Example 3: NULL-safe pattern — materialize then filter
-- ─────────────────────────────────────────────────────────────────────────
-- Calling LLM_GENERATE in both SELECT and WHERE evaluates the UDF twice per row.
-- Materialize into a CTE so the function runs once:
WITH generated AS (
    SELECT id, content,
           LLM_GENERATE('claude-sonnet-4-7', 'Write a 10-word headline: ' || content) AS headline
    FROM articles
    WHERE published_at > now() - INTERVAL '3' DAY
)
SELECT id, content, headline
FROM generated
WHERE headline IS NOT NULL;
-- The WHERE clause filters rows where the UDF returned NULL (per-item failures:
-- budget_exceeded, rate_limited, guardrail_blocked, eval_gate_blocked, etc.).
-- See the WARNING log on the Trino worker for the specific error code.


-- ─────────────────────────────────────────────────────────────────────────
-- Audit trail
-- ─────────────────────────────────────────────────────────────────────────
-- Each LLM_GENERATE call emits:
--   proxy.chat       — operational record (ProxyService)
--   udf.eval_gate.pass — CM-3 evidence if the model has an eval gate configured
--
-- To look up audit entries for a specific Trino query:
--   SELECT action, updated_values
--   FROM audit_logs
--   WHERE action LIKE 'trino.query.%'
--     AND object_id LIKE 'trino-query/<query_id>/%';