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

def get_together_api_key():
    """
    Retrieves Together API key from Hugging Face Secrets (hosted) or fallback to local key (dev).
    """
    key = os.environ.get("TOGETHER_API_KEY")
    
    if key:
        print("✅ TOGETHER_API_KEY loaded from Hugging Face secret.")
        return key
    
    # For local dev fallback
    local_key = "your-local-api-key-here"  # 👈 REPLACE with your actual key
    if local_key:
        print("⚠️ Using local fallback API key.")
        return local_key
    
    raise RuntimeError("❌ TOGETHER_API_KEY is missing. Set it in Hugging Face Secrets or update the fallback.")


# ✅ READ API KEY from Hugging Face Secret
TOGETHER_API_KEY = get_together_api_key()
if not TOGETHER_API_KEY:
    raise RuntimeError("❌ TOGETHER_API_KEY not found. Set it in Hugging Face > Settings > Secrets.")

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
    }

    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()

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

def chatbot_interface(file, question):
    try:
        df = pd.read_excel(file)
        sql = generate_sql_from_prompt(question, df)
        cleaned_sql = clean_sql_for_duckdb(sql, df.columns)
        result = duckdb.query(cleaned_sql).to_df()
        return f"📜 SQL Query:\n```sql\n{sql}\n```", result
    except Exception as e:
        return f"❌ Error: {str(e)}", pd.DataFrame()

with gr.Blocks() as demo:
    gr.Markdown("## 📊 Excel SQL Chatbot with Together API")
    file_input = gr.File(label="📂 Upload Excel File (.xlsx)")
    question = gr.Textbox(label="🧠 Ask a question about your data")
    submit = gr.Button("🚀 Generate & Query")
    sql_output = gr.Markdown()
    result_table = gr.Dataframe()
    submit.click(fn=chatbot_interface, inputs=[file_input, question], outputs=[sql_output, result_table])

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