import traceback
from pathlib import Path
from typing import List, Dict, Any
from langchain_core.tools import tool
from core.db_connection import db_session
from core.logger_config import get_logger

from .sql_safety import (
    MAX_SQL_TOOL_ROWS,
    mssql_wrap_top_subquery,
    normalize_llm_sql,
    validate_llm_sql,
)

logger = get_logger(__name__)


@tool
def execute_sql_query(query: str) -> List[Dict[str, Any]]:
    """
    Executes a read-only SQL query against the MS SQL Server database.
    Only a single SELECT (or WITH ... SELECT) is allowed.
    Returns: List of dictionaries (rows), at most MAX_SQL_TOOL_ROWS from the server.
    """
    logger.info("llm query (truncated for logs): %s", (query or "")[:300])
    try:
        qn = normalize_llm_sql(query)
        err = validate_llm_sql(qn)
        if err:
            logger.error("SQL tool rejected query: %s", err)
            return [{"error": err}]

        wrapped = mssql_wrap_top_subquery(qn, MAX_SQL_TOOL_ROWS)
        rows = db_session.execute(wrapped)
        if len(rows) > MAX_SQL_TOOL_ROWS:
            rows = rows[:MAX_SQL_TOOL_ROWS]

        logger.info("was successful True (%s rows)", len(rows))
        return rows
        
    except Exception as e:
        _q = query or ""
        _q_preview = (_q[:500] + "…") if len(_q) > 500 else _q
        _tb = traceback.extract_tb(e.__traceback__)
        _loc = _tb[-1] if _tb else None
        _raised_where = (
            f"{Path(_loc.filename).name}:{_loc.lineno}" if _loc else "unknown"
        )
        logger.exception(
            "SQL tool execution failed (raised at %s; handler %s; query for logs): %s",
            _raised_where,
            Path(__file__).name,
            _q_preview,
        )

        # Debug: uncomment the block below and comment out the generic ``return`` that follows.
        # That returns query + reason + innermost traceback file:line to the tool consumer.
        # Do not enable in production — exposes schema/DB details to the model or user.
        # return [{
        #     "error": (
        #         f"Query execution failed (raised at {_raised_where}; handler {Path(__file__).name}). "
        #         f"Query: {query!r}. Reason: {e!s}"
        #     )
        # }]

        return [{"error": "Query execution failed."}]