import streamlit as st
import pandas as pd
import openai
import requests

# Streamlit App
def main():
    st.title("Invoice Interest Calculator and Conversation")

    # Prompt user for OpenAI API key
    api_key = st.text_input("Enter your OpenAI API key:")

    if api_key:
        # Download BOE rates
        download_boe_rates()

        # Allow user to upload Excel sheet
        uploaded_file = st.file_uploader("Upload Excel file", type=["xlsx", "xls"])

        if uploaded_file is not None:
            df = pd.read_excel(uploaded_file)

            # Display uploaded data
            st.write("Uploaded Data:")
            st.write(df)

            # Analyze Excel sheet
            due_dates, payment_dates, amounts = analyze_excel(df)

            # Allow user to specify late interest rate
            late_interest_rate = st.number_input("Enter Late Interest Rate (%):", min_value=0.0, max_value=100.0, step=0.1)

            # Calculate late interest if due dates and payment dates are available
            if due_dates and payment_dates:
                # Create DataFrame with extracted due dates, payment dates, and placeholder amount
                df_calculate = pd.DataFrame({
                    'due_date': due_dates,
                    'payment_date': payment_dates,
                    'amount': amounts
                })

                # Calculate late interest
                df_with_interest = calculate_late_interest(df_calculate, late_interest_rate)

                # Display calculated late interest
                total_late_interest = df_with_interest['late_interest'].sum()
                st.write("Calculated Late Interest:")
                st.write(total_late_interest)

            # Generate conversation prompt
            prompt = "I have analyzed the provided Excel sheet. "
            if due_dates:
                prompt += f"The due dates in the sheet are: {', '.join(str(date) for date in due_dates)}. "
            if payment_dates:
                prompt += f"The payment dates in the sheet are: {', '.join(str(date) for date in payment_dates)}. "
            if amounts:
                prompt += f"The amounts in the sheet are: {', '.join(str(amount) for amount in amounts)}. "
            prompt += "Based on this information, what would you like to discuss?"

            # Allow user to engage in conversation
            user_input = st.text_input("Start a conversation:")
            if st.button("Send"):
                openai.api_key = api_key  # Set user-provided OpenAI API key
                
                completion = openai.ChatCompletion.create(
                    model="gpt-4-turbo",
                    messages=[
                        {"role": "system", "content": prompt},
                        {"role": "user", "content": user_input}
                    ],
                    max_tokens=1800 
                )
                response = completion.choices[0].message['content']
                st.write("AI's Response:")
                st.write(response)
    else:
        st.warning("Please enter your OpenAI API key.")

# Function to calculate late interest
def calculate_late_interest(data, late_interest_rate):
    # Calculate late days and late interest
    data['late_days'] = (data['payment_date'] - data['due_date']).dt.days.clip(lower=0)
    data['late_interest'] = data['late_days'] * data['amount'] * (late_interest_rate / 100)
    return data

# Function to analyze Excel sheet and extract relevant information
def analyze_excel(df):
    # Extract due dates and payment dates
    due_dates = df.iloc[:, 0].dropna().tolist()
    payment_dates = df.iloc[:, 1].dropna().tolist()
    amounts = []

    # Extract and clean amounts from third column
    for amount in df.iloc[:, 2]:
        if isinstance(amount, str):
            amount = amount.replace('"', '').replace(',', '')
        amounts.append(float(amount))

    return due_dates, payment_dates, amounts

# Function to download Bank of England rates
def download_boe_rates():
    try:
        headers = {
            'accept-language': 'en-US,en;q=0.9',
            'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36'
        }
        url = 'https://www.bankofengland.co.uk/boeapps/database/Bank-Rate.asp'
        response = requests.get(url, headers=headers)
        if response.status_code == 200:
            df = pd.read_html(response.text)[0]
            df.to_csv('boe_rates.csv', index=False)
            st.success("Bank of England rates downloaded successfully.")
        else:
            st.error("Failed to retrieve data from the Bank of England website.")
    except requests.RequestException as e:
        st.error(f"Failed to download rates: {e}")

if __name__ == "__main__":
    main()