app.py

import streamlit as st
from google import genai
from io import StringIO
from prompts import SYSTEM_INSTRUCTION, USER_PROMPT_TEMPLATE

st.set_page_config(page_title="SQL AI Assistant", layout="wide")

# 1. Setup Sidebar for Context
st.sidebar.title("🛠️ Database Context")
dialect = st.sidebar.selectbox("SQL Dialect", ["PostgreSQL", "MySQL", "SQLite", "BigQuery", "Snowflake"])
db_name = st.sidebar.text_input("Database Name", placeholder="e.g. production_db")
schema = st.sidebar.text_area("Table Schemas (DDL)", placeholder="CREATE TABLE users (id INT, name TEXT...)", height=300)

st.title("🤖 Gemini SQL Generator")
st.caption(f"Powered by Gemini 2.5 Flash")

# 2. Initialize Gemini Client
# In HF Spaces, go to Settings -> Secrets and add 'GEMINI_API_KEY'
api_key = st.secrets["GEMINI_API_KEY"]
client = genai.Client(api_key=api_key)

# 3. Chat Interface
if "messages" not in st.session_state:
    st.session_state.messages = []

for msg in st.session_state.messages:
    st.chat_message(msg["role"]).write(msg["content"])

if prompt := st.chat_input("Show me the top 10 users by signup date..."):
    st.session_state.messages.append({"role": "user", "content": prompt})
    st.chat_message("user").write(prompt)

    # Build the full system instructions with current sidebar context
    full_system_msg = SYSTEM_INSTRUCTION.format(
        dialect=dialect, 
        db_name=db_name, 
        schema=schema if schema else "No specific schema provided."
    )

    with st.spinner("Generating SQL..."):
        response = client.models.generate_content(
            model="gemini-2.5-flash",
            config={'system_instruction': full_system_msg},
            contents=USER_PROMPT_TEMPLATE.format(user_input=prompt)
        )
        
        sql_output = response.text
        st.session_state.messages.append({"role": "assistant", "content": sql_output})
        st.chat_message("assistant").code(sql_output, language="sql")