import streamlit as st
import requests
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

API_URL = "https://7eba-34-127-95-74.ngrok-free.app/query"  

st.set_page_config(
    page_title="SQL Agent with Streamlit", 
    page_icon=":bar_chart:",  
    layout="wide" 
)

with st.sidebar:
    st.write("## About Me")
    st.write("**Mahmoud Hassanen**") 
    st.write("**[LinkedIn Profile](https://www.linkedin.com/in/mahmoudhassanen99/)**")

# Main content
st.title("SQL Agent with Streamlit")
st.header("Analyze Sales Data with Natural Language Queries")

# Input for the question
question = st.text_input("Enter your question:")

if st.button("Generate SQL"):
    if question:
        # API to generate SQL
        response = requests.post(API_URL, json={"question": question})
        
        if response.status_code == 200:
            data = response.json()
            generated_sql = data["sql_query"]
            st.session_state.generated_sql = generated_sql  # Store the generated SQL in session state
            st.write("### Generated SQL Query:")
            st.code(generated_sql, language="sql")
        else:
            st.error(f"API Error: Status Code {response.status_code}")
    else:
        st.warning("Please enter a question.")

# Allow the user to modify the SQL query
if "generated_sql" in st.session_state:
    modified_sql = st.text_area("Modify the SQL query (if needed):", st.session_state.generated_sql, height=200)
    
    if st.button("Execute Modified Query"):
        try:
            # Execute the modified SQL query
            response = requests.post(API_URL, json={"sql_query": modified_sql})  # Send the modified SQL to the API
            if response.status_code == 200:
                data = response.json()
                result_df = pd.read_json(data["result"], orient='records')
                st.write("### Query Results:")
                st.dataframe(result_df)
                
                # Visualize the data (if applicable)
                if 'region' in result_df.columns and 'total_sales' in result_df.columns:
                    st.write("### Total Sales by Region")
                    fig, ax = plt.subplots()
                    sns.barplot(x='region', y='total_sales', data=result_df, ax=ax)
                    st.pyplot(fig)
            else:
                st.error(f"Error executing SQL: {response.json().get('error')}")
        except Exception as e:
            st.error(f"Error executing SQL: {e}")