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

# Import database
from database import engine, receipts

# Load Hugging Face API token
load_dotenv(override=True)
hf_token = os.getenv("HF_TOKEN")

if not hf_token:
    raise ValueError("HF_TOKEN environment variable is not set.")

@tool
def sql_engine(query: str) -> str:
    """
    Executes an SQL query on the 'receipts' table and returns results.
    
    Table Schema:
        - receipt_id: INTEGER
        - customer_name: VARCHAR(16)
        - price: FLOAT
        - tip: FLOAT

    Args:
        query: The SQL query to execute.
    
    Returns:
        Query result as a string.
    """
    output = ""
    try:
        with engine.connect() as con:
            rows = con.execute(text(query))
            for row in rows:
                output += "\n" + str(row)
    except Exception as e:
        output = f"Error: {str(e)}"
    return output.strip()

# 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", token=hf_token),
)

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.
    """
    # Generate SQL from natural language
    generated_sql = agent.run(f"Convert this request into SQL: {user_query}")

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

# Define Gradio interface
demo = gr.Interface(
    fn=query_sql,
    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)