"""Analyze SQL errors locally, produce safe abstract feedback for Claude.

CRITICAL for stricter security: Raw error messages NEVER leave the local server.
Only sanitized, abstract descriptions are sent back to Claude for retry.
"""

from __future__ import annotations

from local_llm.ollama_client import OllamaClient


class ErrorAnalyzer:
    """Analyzes SQL errors locally and produces safe abstract feedback."""

    def __init__(self, ollama: OllamaClient) -> None:
        self._ollama = ollama

    async def analyze(self, sql: str, error_message: str) -> str:
        """Analyze a SQL error and produce a sanitized description.

        Args:
            sql: The SQL query that failed
            error_message: The raw error message from PostgreSQL

        Returns:
            A sanitized description safe to send to Claude.
            Contains NO actual values, table contents, or connection details.
        """
        prompt = f"""A SQL query failed with an error. Analyze the error and provide a brief,
abstract description of what went wrong. Your description must NOT contain:
- Any actual data values
- Connection strings or IP addresses
- Specific error codes
- Raw database error messages

SQL that failed:
{sql}

Error message:
{error_message}

Provide ONLY a brief, abstract description like:
- "The query references a column that doesn't exist in the target table"
- "The JOIN condition uses incompatible types"
- "The GROUP BY clause is missing a required column"
- "The table name is incorrect or doesn't exist"
- "A date function received an invalid format"
- "The aggregate function is used incorrectly"

Your sanitized description:"""

        response = await self._ollama.generate(
            prompt=prompt,
            system=(
                "You are a SQL error analyst. Provide ONLY abstract, sanitized error descriptions. "
                "Never include actual values, error codes, or raw messages."
            ),
            temperature=0.1,
            max_tokens=200,
        )

        sanitized = response.strip()

        # Extra safety: if the LLM leaked the raw error, fall back to generic message
        if any(
            fragment in sanitized.lower()
            for fragment in ["error:", "psycopg", "sqlalchemy", "traceback", "connection"]
        ):
            sanitized = "The SQL query contains a structural error that needs correction."

        return sanitized

    @staticmethod
    def quick_analyze(error_message: str) -> str:
        """Fast rule-based analysis without LLM — for common errors.

        Falls back to this if Ollama is unavailable.
        """
        error_lower = error_message.lower()

        if "column" in error_lower and "does not exist" in error_lower:
            return "The query references a column that doesn't exist in the target table."
        if "relation" in error_lower and "does not exist" in error_lower:
            return "The query references a table that doesn't exist."
        if "syntax error" in error_lower:
            return "The SQL query has a syntax error."
        if "type mismatch" in error_lower or "cannot cast" in error_lower:
            return "The query has a data type mismatch in a comparison or function."
        if "group by" in error_lower:
            return "The GROUP BY clause is missing a required column or is incorrectly structured."
        if "ambiguous" in error_lower:
            return "A column reference is ambiguous — it exists in multiple tables."
        if "division by zero" in error_lower:
            return "The query attempts a division by zero."
        if "permission" in error_lower or "denied" in error_lower:
            return "The query doesn't have permission to access the requested resource."
        if "timeout" in error_lower:
            return "The query took too long to execute. Consider simplifying it."

        return "The SQL query encountered an error and needs to be revised."
