# Streamlit app code
import streamlit as st
import requests
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

API_URL = "https://14d0-34-27-134-153.ngrok-free.app/query"  # Replace with your ngrok public URL

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//)**")
    
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("Submit"):
    if question:
        response = requests.post(API_URL, json={"question": question})
        
        if response.status_code == 200:
            data = response.json()
            st.write("Generated SQL Query:")
            st.code(data["sql_query"], language="sql")
            
            st.write("Query Results:")
            result_df = pd.read_json(data["result"], orient='records')
            st.dataframe(result_df)
            
            # Visualize the data
            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: {response.json().get('error')}")
    else:
        st.warning("Please enter a question.")