-- Requires: AOCore Trino plugin (Obj E); model catalog (Obj C) seeded with the referenced model
--           The model must support JSON-mode output (model_card.supports_json_mode = true)
-- Function:  LLM_EXTRACT(model VARCHAR, text VARCHAR, schema VARCHAR) → VARCHAR
-- Docs:      docs/developer/ai-sql-functions.md#llm_extract

-- ─────────────────────────────────────────────────────────────────────────
-- Example 1: Extract contact info from unstructured form submissions
-- ─────────────────────────────────────────────────────────────────────────
SELECT id,
       LLM_EXTRACT(
           'claude-sonnet-extract',
           description,
           '{"type":"object","properties":{"name":{"type":"string"},"age":{"type":"integer"},"email":{"type":"string"}}}'
       ) AS extracted_json
FROM contact_form_submissions
WHERE submitted_at > now() - INTERVAL '1' HOUR;
-- Expected output schema:
--   id             (preserves source column type)
--   extracted_json VARCHAR  — JSON string like '{"name":"Alice","age":30,"email":"alice@example.com"}'
--                             or NULL on per-item failure (budget, guardrail, malformed schema, etc.)

-- Performance note: the BatchQueue accumulates up to 64 rows (or 100ms) before
-- flushing. LLM_EXTRACT is a chat-completions call with JSON-mode enforced;
-- provider latency: 200ms–2s typical. For >10K rows, materialize the input
-- subset first (see ai-sql-aware-query-patterns.sql Pattern 3).


-- ─────────────────────────────────────────────────────────────────────────
-- Example 2: Parse the JSON output in downstream SQL
-- ─────────────────────────────────────────────────────────────────────────
WITH extracted AS (
    SELECT id,
           LLM_EXTRACT(
               'claude-sonnet-extract',
               description,
               '{"type":"object","properties":{"name":{"type":"string"},"age":{"type":"integer"},"email":{"type":"string"}}}'
           ) AS raw_json
    FROM contact_form_submissions
    WHERE submitted_at > now() - INTERVAL '1' HOUR
)
SELECT
    id,
    json_extract_scalar(raw_json, '$.name')             AS name,
    TRY_CAST(json_extract_scalar(raw_json, '$.age') AS INTEGER) AS age,
    json_extract_scalar(raw_json, '$.email')            AS email
FROM extracted
WHERE raw_json IS NOT NULL;
-- TRY_CAST guards against non-integer values in the extracted 'age' field.


-- ─────────────────────────────────────────────────────────────────────────
-- Example 3: Extract structured fields from log messages
-- ─────────────────────────────────────────────────────────────────────────
WITH log_schema AS (
    -- Define the extraction schema once to avoid repetition
    SELECT '{"type":"object","properties":{"severity":{"type":"string"},"error_code":{"type":"string"},"service":{"type":"string"},"message":{"type":"string"}}}' AS schema_def
),
extracted_logs AS (
    SELECT
        l.id,
        l.raw_message,
        LLM_EXTRACT('claude-sonnet-extract', l.raw_message, s.schema_def) AS extracted
    FROM application_logs l, log_schema s
    WHERE l.created_at > now() - INTERVAL '30' MINUTE
      AND l.level IN ('ERROR', 'WARN')
)
SELECT
    id,
    json_extract_scalar(extracted, '$.severity')   AS severity,
    json_extract_scalar(extracted, '$.error_code') AS error_code,
    json_extract_scalar(extracted, '$.service')    AS service,
    json_extract_scalar(extracted, '$.message')    AS message
FROM extracted_logs
WHERE extracted IS NOT NULL;


-- ─────────────────────────────────────────────────────────────────────────
-- Notes on the schema argument
-- ─────────────────────────────────────────────────────────────────────────
-- The schema argument must be valid JSON. A malformed schema (invalid JSON)
-- returns SQL NULL immediately (pre-dispatch, no gateway call is made).
-- The schema is a JSON document; a JSON Schema object is the recommended
-- format. The Go dispatch bundles the schema into the prompt template's
-- {{schema}} placeholder before the upstream call.


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