app.py

import os
import gradio as gr
from sqlalchemy import text
from smolagents import tool, CodeAgent, HfApiModel
import spaces

# Import the persistent database
from database import engine, receipts

@tool
def sql_engine(query: str) -> str:
    """
    Executes an SQL query on the 'receipts' table and returns formatted results.

    Args:
        query: The SQL query to execute.

    Returns:
        Query result as a formatted string.
    """
    try:
        with engine.connect() as con:
            rows = con.execute(text(query)).fetchall()

        if not rows:
            return "No results found."

        # If query returns a single value (e.g., AVG, SUM, COUNT), return as a string
        if len(rows) == 1 and len(rows[0]) == 1:
            return str(rows[0][0])  # Convert numerical result to string

        # Convert query results into a clean, readable format
        return "\n".join([", ".join(map(str, row)) for row in rows])

    except Exception as e:
        return f"Error: {str(e)}"

def query_sql(user_query: str) -> str:
    """
    Converts natural language input to an SQL query using CodeAgent
    and returns the execution results.

    Args:
        user_query: The user's request in natural language.

    Returns:
        The query result from the database as a formatted string.
    """
    # Provide the AI with the correct schema and strict instructions
    schema_info = (
        "The database has a table named 'receipts' with the following schema:\n"
        "- receipt_id (INTEGER, primary key)\n"
        "- customer_name (VARCHAR(16))\n"
        "- price (FLOAT)\n"
        "- tip (FLOAT)\n"
        "Generate a valid SQL SELECT query using ONLY these column names.\n"
        "DO NOT explain your reasoning, and DO NOT return anything other than the SQL query itself."
    )

    # Generate SQL query using the provided schema
    generated_sql = agent.run(f"{schema_info} Convert this request into SQL: {user_query}")

    # Log the generated SQL for debugging
    print(f"Generated SQL: {generated_sql}")

    # Ensure we only execute valid SELECT queries
    if not generated_sql.strip().lower().startswith(("select", "show", "pragma")):
        return "Error: Only SELECT queries are allowed."

    # Execute the SQL query and return the result
    result = sql_engine(generated_sql)

    # Log the SQL query result
    print(f"SQL Query Result: {result}")

    # Ensure proper formatting based on content
    try:
        float_result = float(result)
        return f"{float_result:.2f}"  # Format numbers to 2 decimal places
    except ValueError:
        return result  # Return text results directly without modification

def handle_query(user_input: str) -> str:
    """
    Calls query_sql, captures the output, and directly returns it to the UI.

    Args:
        user_input: The user's natural language question.

    Returns:
        The SQL query result as a plain string to be displayed in the UI.
    """
    return query_sql(user_input)  # Directly return the processed result

# Initialize CodeAgent to generate SQL queries from natural language
agent = CodeAgent(
    tools=[sql_engine],  # Ensure sql_engine is properly registered
    model=HfApiModel(model_id="Qwen/Qwen2.5-Coder-32B-Instruct"),
)

# Define Gradio interface using handle_query instead of query_sql
demo = gr.Interface(
    fn=handle_query,  # Call handle_query to return the final SQL output
    inputs=gr.Textbox(label="Enter your query in plain English"),
    outputs=gr.Textbox(label="Query Result"),
    title="Natural Language to SQL Executor",
    description="Enter a plain English request, and the AI will generate an SQL query and return the results.",
    flagging_mode="never",
)

if __name__ == "__main__":
    demo.launch(server_name="0.0.0.0", server_port=7860, share=True)