import streamlit as st
import pandas as pd
from langchain.agents import create_csv_agent
from langchain.llms import OpenAI

# Set Streamlit page configuration
st.set_page_config(page_title='CSV Processing', 
                   page_icon=":memo:", 
                   layout='wide', 
                   initial_sidebar_state='collapsed')

# Set CSS properties for HTML components
st.markdown("""
<style>
body {
    color: #fff;
    background-color: #4f8bf9;
}
h1, h2 {
    color: #ffdd00;
}
</style>
    """, unsafe_allow_html=True)

hide_style='''
    <style>
    #MainMenu {visibility:hidden;}
    footer {visibility:hidden;}
    .css-hi6a2p {padding-top: 0rem;}
    head {visibility:hidden;}
    </style>
'''
st.markdown("""
<h1 style='text-align: center; color: #ffdd00;'>XLS Office Documents Analysis with ChatGPT4 NLP Model</h1>
    """, unsafe_allow_html=True)
#st.title('XLS Office Documents Analysis with ChatGPT4 NLP Model')

def load_data(file):
    df = pd.read_excel(file, engine='openpyxl')
    df.to_csv('data.csv', index=False)  # Convert XLS to CSV
    return 'data.csv'

def initialize_agent(file, openai_api_key):
    agent = create_csv_agent(OpenAI(temperature=0, openai_api_key=openai_api_key), file, verbose=True)
    return agent

uploaded_file = st.file_uploader("Upload XLS", type=['xlsx'])
st.markdown(hide_style, unsafe_allow_html=True)

openai_api_key = st.sidebar.text_input('OpenAI API Key', type="password")

# Pre-defined question examples
question_examples = [
    "how many rows are there?",
    "how many people are female?",
    "how many people have stayed more than 3 years in the city?",
    "how many people have stayed more than 3 years in the city and are female?",
    "Are there more males or females?",
    "What are the column names?",
    "What is the average age?",
    "Which country appears the most and how many times does it appear?",
    "What is the ratio of males to females?"
    # Add more examples as needed
]

# Dropdown select box for question examples
selected_example = st.selectbox('Choose a question example:', question_examples)

# Pre-populate the question field with the selected example
question = st.text_input('Enter your question:', value=selected_example)

if not openai_api_key or not openai_api_key.startswith('sk-'):
    st.warning('Please enter your OpenAI API key!', icon='⚠️')
else:
    if uploaded_file is not None:
        # Create a progress bar
        progress_bar = st.progress(0)
        progress_bar.progress(25)  # Start the progress at 25%

        csv_file = load_data(uploaded_file)  # Now the uploaded file is an XLS file
        progress_bar.progress(50)  # Update the progress to 50%
        
        agent = initialize_agent(csv_file, openai_api_key)
        progress_bar.progress(100)  # Complete the progress bar
        
        if question:
            response = agent.run(question)
            #st.markdown(f'**Response:** {response}')
            st.markdown(f'<div style="color: red; font-size: 24px; text-align: center;">**{response}**</div>',unsafe_allow_html=True)