from smolagents import tool, CodeAgent, InferenceClientModel
import os
import dotenv
from sqlalchemy import create_engine, text
import gradio as gr

dotenv.load_dotenv()

DB_PATH = "real_estate.db"
MODEL_ID = "Qwen/Qwen3-235B-A22B-Instruct-2507"
HF_TOKEN = os.environ.get("HF_TOKEN", None)
if HF_TOKEN is None:
    raise RuntimeError("HF_TOKEN missing. Add it in Space Settings → Repository secrets.")

engine = create_engine(f"sqlite:///{DB_PATH}", echo=True, future=True)

@tool
def sql_engine(query: str) -> str:
    """
    Executes a SQL query on the 'real_estate.db' SQLite database and returns the results as a string. The database contains a table named 'real_estate' with the following schema:
        Columns:
    - id: TEXT — Unique identifier of the listing.
    - ref: TEXT — Reference number of the property listing.
    - bua: TEXT — Built-up area of the property in square feet.
    - title: TEXT — Title or headline of the property listing.
    - no_of_leads: BIGINT — Number of customer leads or inquiries for the property.
    - location: TEXT — Full address or general location of the property.
    - sublocation: TEXT — Sub-location or neighborhood within the main location.
    - building: TEXT — Name of the building in which the property is located.
    - unit: TEXT — Unit number or identifier within the building.
    - price: FLOAT — Price of the property in AED (United Arab Emirates Dirham).
    - category: TEXT — Category or type of property (e.g., Apartment, Villa, Townhouse, Duplex, Penthouse, Hotel Apartment, Land Residential, Loft Apartment, Residential Building, Bungalow, Full Floor Residential, Bulk Units).
    - score: BIGINT — Quality or ranking score of the property (0–100 scale).
    - beds: FLOAT — Number of bedrooms in the property.
    - baths: FLOAT — Number of bathrooms in the property.
    - property_for: TEXT — Type of contract: either "Rental" or "Sales".
    - views: TEXT — List of view features (comma-separated, e.g., "Sea View, City View").
    - features: TEXT — Comma-separated list of property features (e.g., "Balcony, Pool, Parking").
    - furnishing: TEXT — Furnishing status (e.g., Partly Furnished, Unfurnished, Furnished, Fitted, Not Fitted, Shell And Core).
    - assigned_agent_name: TEXT — Name of the agent currently assigned to the property.
    - marketed_agent_name: TEXT — Name of the agent marketing the property.
    - portals: TEXT — List of online portals where the property is listed (comma-separated).

    Use LIKE for partial matches on fields like location, sublocation, building, features, and portals, assigned_agent_name, marketed_agent_name, views, features.
    Args:
        query (str): The SQL query to be executed.

    """
    output = ""

    with engine.connect() as connection:
        result = connection.execute(text(query))
        rows = result.fetchall()
        if not rows:
            return "No results found."
        for row in rows:
            output += str(row) + "\n"
    return output.strip()

def make_agent():

    agent = CodeAgent(model= InferenceClientModel(model_id=MODEL_ID, api_key=HF_TOKEN),
                    tools=[sql_engine],
                    description="A helpful agent that can answer questions about real estate properties in Dubai based on a database of listings.",
                    )

    return agent

agent = make_agent()

def run_agent(user_request: str) -> str:
    """
    Takes a natural language request, lets the agent decide the SQL via the tool,
    and returns a friendly response.
    """
    if not user_request or not user_request.strip():
        return "Please enter a request."
    try:
        return agent.run(user_request.strip())
    except Exception as e:
        return f"Agent error: {e}"

# ---- Gradio UI ----
with gr.Blocks(title="Real Estate Text-to-SQL") as demo:
    gr.Markdown(
        """
        # 🏡 Nazem's personal Driven Text-to-SQL Agent
        Ask natural-language questions to the CRM's listings database.

        **Examples**
        - "Write me a message to send my client who is asking me to propose 3 properties to show him. He is looking for flat with 2 to 3 bedrooms a marina view and a swimming pool between 1 and 5 million AED."
        - "Show 5 apartments in Dubai Marina with ≥3 beds, ≥2 baths, price 1M–2M AED. Include id, title, location, price, beds, baths, features."
        - "Top 10 cheapest villas in Palm Jumeirah with at least 4 bedrooms."
        - "
        """
    )
    with gr.Row():
        inp = gr.Textbox(label="Your request", lines=5, placeholder="Ask for properties, filters, columns, limits…")
    btn = gr.Button("Run")
    out = gr.Markdown(label="Response")

    btn.click(run_agent, inputs=inp, outputs=out)

if __name__ == "__main__":
    demo.launch()