-- Requires: AOCore Trino plugin (Obj E); model catalog (Obj C) seeded with the referenced model
--           AND a prompt_template asset associated with the model containing a {{labels}} placeholder
-- Function:  LLM_CLASSIFY(model VARCHAR, text VARCHAR, labels ARRAY<VARCHAR>) → VARCHAR
-- Docs:      docs/developer/ai-sql-functions.md#llm_classify

-- ─────────────────────────────────────────────────────────────────────────
-- Example 1: Sentiment classification
-- ─────────────────────────────────────────────────────────────────────────
SELECT id, content,
       LLM_CLASSIFY('claude-haiku-classify', content, ARRAY['positive', 'negative', 'neutral']) AS sentiment
FROM reviews
WHERE reviewed_at > now() - INTERVAL '24' HOUR;
-- Expected output schema:
--   id        (preserves source column type)
--   content   (preserves source column type)
--   sentiment VARCHAR  — one of 'positive', 'negative', 'neutral', or NULL on failure

-- Performance note: the BatchQueue accumulates up to 64 rows (or 100ms) before
-- flushing. The classify call resolves a prompt_template catalog asset (60s TTL
-- cache) and substitutes {{labels}} before the upstream provider call.
-- Provider latency: 200ms–2s typical. For >10K rows, use the materialized-table
-- pattern (see ai-sql-aware-query-patterns.sql Pattern 3).


-- ─────────────────────────────────────────────────────────────────────────
-- Example 2: Filter on classification result (NULL-safe CTE pattern)
-- ─────────────────────────────────────────────────────────────────────────
-- ANTI-PATTERN: calling LLM_CLASSIFY in both SELECT and WHERE evaluates twice:
--   SELECT id FROM tickets WHERE LLM_CLASSIFY('m', content, ARRAY['urgent','low']) = 'urgent';
--
-- RECOMMENDED: materialize into a CTE so the function runs once per row:
WITH classified AS (
    SELECT id, content,
           LLM_CLASSIFY('claude-haiku-classify', content, ARRAY['urgent', 'non-urgent']) AS priority
    FROM support_tickets
    WHERE created_at > now() - INTERVAL '1' DAY
)
SELECT id, content
FROM classified
WHERE priority = 'urgent';
-- Rows where priority IS NULL (per-item failures) are automatically excluded
-- by the WHERE priority = 'urgent' predicate.


-- ─────────────────────────────────────────────────────────────────────────
-- Example 3: Multi-label classification with NULL filtering
-- ─────────────────────────────────────────────────────────────────────────
WITH categorized AS (
    SELECT
        id,
        title,
        body,
        LLM_CLASSIFY(
            'claude-haiku-classify',
            title || ': ' || body,
            ARRAY['bug', 'feature_request', 'question', 'documentation', 'other']
        ) AS category
    FROM github_issues
    WHERE created_at > now() - INTERVAL '7' DAY
      AND state = 'open'
)
SELECT id, title, body, category
FROM categorized
WHERE category IS NOT NULL
ORDER BY category, id;
-- Expected output schema:
--   id, title, body (preserve source types)
--   category VARCHAR — one of the 5 labels, or excluded if NULL


-- ─────────────────────────────────────────────────────────────────────────
-- Audit trail
-- ─────────────────────────────────────────────────────────────────────────
-- Each LLM_CLASSIFY 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 Trino query:
--   SELECT action, updated_values FROM audit_logs
--   WHERE action LIKE 'trino.query.%'
--     AND object_id LIKE 'trino-query/<query_id>/%';