import gradio as gr
import pandas as pd
import duckdb
import requests
import re
import os
from io import BytesIO

# 🔐 Read Together API key from Hugging Face Secrets
TOGETHER_API_KEY = os.getenv("TOGETHER_API_KEY")

# 🧠 Generate SQL from Prompt
def generate_sql_from_prompt(prompt, df):
    schema = ", ".join([f"{col} ({str(dtype)})" for col, dtype in df.dtypes.items()])
    full_prompt = f"""
You are a SQL expert. Here is a table called 'df' with the following schema:
{schema}

User question: "{prompt}"

Write a valid SQL query using the 'df' table. Return only the SQL code.
"""

    url = "https://api.together.xyz/v1/chat/completions"
    headers = {
        "Authorization": f"Bearer {TOGETHER_API_KEY}",
        "Content-Type": "application/json"
    }
    payload = {
        "model": "mistralai/Mixtral-8x7B-Instruct-v0.1",
        "messages": [{"role": "user", "content": full_prompt}],
        "temperature": 0.2,
        "max_tokens": 200
    }

    try:
        response = requests.post(url, headers=headers, json=payload)
        response.raise_for_status()
        result = response.json()
        return result['choices'][0]['message']['content'].strip("```sql").strip("```").strip()
    except Exception as e:
        return f"Error in API call: {str(e)}"

# 🧽 Clean SQL for DuckDB
def clean_sql_for_duckdb(sql, df_columns):
    sql = sql.replace("`", '"')
    for col in df_columns:
        if " " in col and f'"{col}"' not in sql:
            pattern = r'\b' + re.escape(col) + r'\b'
            sql = re.sub(pattern, f'"{col}"', sql)
    return sql

# 🔄 Global dataframe holder
df_global = {"df": None}

# 📂 Upload handler
def upload_excel(file):
    if file is None:
        return "❌ No file uploaded."
    try:
        df = pd.read_excel(BytesIO(file.read()))
        df_global["df"] = df
        return f"✅ Uploaded file with shape: {df.shape}"
    except Exception as e:
        return f"❌ Failed to load file: {e}"

# 💬 Handle prompt
def handle_query(prompt):
    df = df_global.get("df")
    if df is None:
        return "❌ Please upload an Excel file first.", pd.DataFrame()

    try:
        sql = generate_sql_from_prompt(prompt, df)
        if sql.startswith("Error"):
            return sql, pd.DataFrame()

        cleaned_sql = clean_sql_for_duckdb(sql, df.columns)
        result_df = duckdb.query(cleaned_sql).to_df()
        return f"📜 Generated SQL:\n{sql}", result_df
    except Exception as e:
        return f"❌ Error: {e}", pd.DataFrame()

# 🎨 UI
with gr.Blocks() as demo:
    gr.Markdown("# 🤖 SQL Chatbot with Together API + DuckDB")
    file_input = gr.File(label="📂 Upload Excel File (.xlsx only)", file_types=[".xlsx"])
    upload_status = gr.Textbox(label="Status", interactive=False)

    with gr.Row():
        prompt_box = gr.Textbox(label="💬 Your Question", placeholder="e.g., Show me total sales by region")
        run_button = gr.Button("🚀 Generate SQL + Run")

    sql_output = gr.Textbox(label="📜 SQL Query")
    result_table = gr.Dataframe(label="📊 Query Results")

    file_input.change(upload_excel, inputs=file_input, outputs=upload_status)
    run_button.click(handle_query, inputs=prompt_box, outputs=[sql_output, result_table])

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