import gradio as gr
import os
import openai

client = openai.OpenAI(api_key=os.environ.get("OPENAI_API_KEY"))

BASE_SYSTEM_PROMPT = "You are an SQL expert. Based on the selected dialect, return a correct SQL query for the user's request."

def query_openai(message, chat_history, dialect):
    system_prompt = f"{BASE_SYSTEM_PROMPT} Use the {dialect} dialect."

    messages = [{"role": "system", "content": system_prompt}]
    for user, bot in chat_history:
        messages.append({"role": "user", "content": user})
        messages.append({"role": "assistant", "content": bot})
    messages.append({"role": "user", "content": message})

    response = client.chat.completions.create(
        model="gpt-3.5-turbo",
        messages=messages,
        temperature=0.5
    )

    return response.choices[0].message.content

def respond(message, chat_history, dialect):
    try:
        bot_reply = query_openai(message, chat_history, dialect)
    except Exception as e:
        bot_reply = f"❌ Error: {str(e)}"
    chat_history.append((message, bot_reply))
    return "", chat_history

with gr.Blocks() as demo:
    gr.Markdown("## 🧠 SQL Query Generator Bot (OpenAI GPT-Powered)")

    dialect = gr.Dropdown(["MySQL", "PostgreSQL", "SQLite"], value="MySQL", label="Select SQL Dialect")
    chatbot = gr.Chatbot()
    msg = gr.Textbox(label="Describe your SQL query in plain English")
    clear = gr.Button("Clear Chat")
    state = gr.State([])

    msg.submit(respond, [msg, state, dialect], [msg, chatbot])
    clear.click(lambda: ([], []), None, [chatbot, state])

demo.launch()