app/tools/sql_whisperer/whisperer.py

"""Natural language to SQL engine."""
from app.core.ai import call_ai_json

_SYSTEM = """You are an expert SQL engineer and database architect.
Convert natural language questions into precise, optimized SQL queries.
Always produce correct, runnable SQL. Explain your query so a junior dev can learn.
Return ONLY valid JSON — no markdown fences, no preamble.
CRITICAL: All JSON string values must be properly escaped. Use \\n for newlines inside strings."""

_PROMPT_TMPL = """Convert this natural language question into SQL.

QUESTION: {question}

DATABASE SCHEMA:
{schema}

DIALECT: {dialect}

Return JSON with EXACTLY these keys:
{{
  "sql": "<the complete SQL query, formatted with proper indentation>",
  "explanation": "<step-by-step explanation of what the query does and why>",
  "warnings": ["<any potential issues, e.g. missing indexes, large scans>"],
  "alternatives": [
    {{"label": "<alternative approach name>", "sql": "<alternative SQL>", "trade_off": "<when to use this instead>"}}
  ],
  "sample_result_shape": "<description of what the result set looks like, e.g. 'Returns rows with columns: user_id, name, total_orders'>"
}}

If the question is ambiguous, make the most reasonable assumption and note it in warnings.
If no schema is provided, generate SQL for a generic table structure that matches the question."""


def whisper(question: str, schema: str, dialect: str = "PostgreSQL") -> dict:
    prompt = _PROMPT_TMPL.format(
        question=question[:2000],
        schema=schema[:4000] if schema else "(no schema provided — infer reasonable table structure)",
        dialect=dialect
    )
    try:
        result = call_ai_json([{"role": "user", "content": prompt}], system=_SYSTEM)
        return result if isinstance(result, dict) else {}
    except Exception:
        return {}