import pandas as pd
from sqlalchemy import text
from app.db.db_connector import get_db_engine



from langchain_openai import ChatOpenAI
from config.settings import settings

def execute_sql_query(sql_query: str):
    """Run a SQL query and return results as list of dicts."""
    engine = get_db_engine()
    with engine.connect() as conn:
        result = conn.execute(text(sql_query))
        rows = [dict(row) for row in result.mappings()]
    return rows

def run_and_handle_sql_query(sql_query: str, user_question: str):
    """
    Executes the SQL query, and if no results, uses LLM to generate a friendly message.
    Returns either a list of dicts (rows) or {"chat_message": ...}.
    """
    try:
        rows = execute_sql_query(sql_query)
        if not rows:
            llm = ChatOpenAI(
                model="gpt-4o-mini", 
                temperature=0, 
                api_key=settings.OPENAI_API_KEY,
                request_timeout=None  # No timeout for API requests
            )
            no_result_prompt = (
                f"The following SQL query was generated for the user's question, but it returned no results. "
                f"User question: {user_question}\nSQL query: {sql_query}\n"
                "Please explain to the user in a friendly way that no matching records were found for their request."
            )
            no_result_response = llm.invoke([{"role": "user", "content": no_result_prompt}]).content.strip()
            return {"chat_message": no_result_response}
        return rows
    except Exception as e:
        return {"error": str(e), "sql_query": sql_query}

# def run_query(sql: str):
#     engine = get_db_engine()
#     with engine.connect() as conn:
#         result = conn.execute(text(sql))
#         df = pd.DataFrame(result.fetchall(), columns=result.keys())
#     return df