import sqlite3
import pandas as pd
import gradio as gr

from init_db import ensure_database
from nl_to_sql import parse_question_to_sql, format_answer

DB_PATH = "demo_store.db"
ensure_database(DB_PATH)


def execute_read_only_sql(sql: str):
    sql_clean = sql.strip().lower()
    banned = ["insert ", "update ", "delete ", "drop ", "alter ", "create ", "replace ", "truncate ", "attach ", "pragma ", "vacuum "]
    if not sql_clean.startswith("select"):
        raise ValueError("Only read-only SELECT queries are allowed in this demo.")
    if any(word in sql_clean for word in banned):
        raise ValueError("Unsafe SQL detected. This demo only allows SELECT queries.")

    conn = sqlite3.connect(DB_PATH)
    try:
        df = pd.read_sql_query(sql, conn)
    finally:
        conn.close()
    return df


SYSTEM_TEXT = """
You can ask questions like:
- Show all customers
- Show orders from March 2026
- What is the total revenue by country?
- Top 5 products by revenue
- Average order value by customer
- How many orders are delayed?
""".strip()


def answer_question(message, history):
    sql, notes = parse_question_to_sql(message)
    try:
        df = execute_read_only_sql(sql)
        answer = format_answer(message, sql, df, notes)
    except Exception as e:
        answer = f"""I could not run that query.

Reason: {e}

SQL candidate:
```sql
{sql}
```"""
    return answer


with gr.Blocks(title="NL → SQL Demo") as demo:
    gr.Markdown("# Natural language → SQL chatbot")
    gr.Markdown(
        "Ask a business question in plain English. The app converts it into a SQLite query and executes it against a demo store database."
    )
    gr.Markdown("""## Demo schema
- `customers(id, name, country, segment)`
- `products(id, name, category, price)`
- `orders(id, customer_id, order_date, status, shipping_days)`
- `order_items(id, order_id, product_id, quantity, unit_price)`""")
    
    chatbot = gr.ChatInterface(
    fn=answer_question,
    examples=[
        "Show all customers",
        "What is the total revenue by country?",
        "Top 5 products by revenue",
        "Show orders from March 2026",
        "Average order value by customer",
        "How many orders are delayed?",
        "Show revenue by month",
        "List products in the Electronics category",
    ],
    )
    gr.Markdown("""### Notes
- This starter uses simple rule-based parsing for reliability and zero external dependencies.
- It is deliberately **read-only**: only `SELECT` statements are executed.
- You can later swap `parse_question_to_sql()` for an LLM-based implementation while keeping the same UI.""")

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