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

# Set page configuration
st.set_page_config(
    page_title="SQL Agent with Streamlit",  # Page title
    page_icon=":bar_chart:",  # Favicon emoji
    layout="wide"  # Page layout option
)

# Add a header and page name
st.title("SQL Agent with Streamlit")
st.header("Analyze Sales Data with Natural Language Queries")

# Add a sidebar with your name and LinkedIn profile
with st.sidebar:
    st.write("## About Me")
    st.write("**Name:** Your Name")  # Replace with your name
    st.write("**LinkedIn:** [Your LinkedIn Profile](https://www.linkedin.com/in/your-profile/)")  # Replace with your LinkedIn URL

# API URL (replace with your ngrok URL)
API_URL = "https://c6d9-34-27-134-153.ngrok-free.app/query"  # Replace with your ngrok public URL

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

if st.button("Generate SQL"):
    if question:
        # Call the 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
            result = execute_sql(modified_sql)  # Use your existing execute_sql function
            st.write("### Query Results:")
            st.dataframe(result)
            
            # Visualize the data (if applicable)
            if 'region' in result.columns and 'total_sales' in result.columns:
                st.write("### Total Sales by Region")
                fig, ax = plt.subplots()
                sns.barplot(x='region', y='total_sales', data=result, ax=ax)
                st.pyplot(fig)
        except Exception as e:
            st.error(f"Error executing SQL: {e}")

# Function to execute SQL and return results
def execute_sql(sql_query):
    # Create a SQLAlchemy connection string
    connection_string = f"mssql+pyodbc://{username}:{password}@{server}/{database}?driver={driver.replace(' ', '+')}"
    engine = create_engine(connection_string)
    
    # Execute the query and fetch results
    df = pd.read_sql(sql_query, engine)
    return df