import streamlit as st
import pandas as pd
import openpyxl
# Load data from Excel file
@st.cache_data
def load_data(file_path):
    return pd.read_excel(file_path)

# Path to the Excel file
file_path = "BullSale.xlsx"

# Columns to display
columns_to_display = [
    "Animal ID", "Name", "Sex", "Birth Date",  # Static columns
    "ProS", "ProS%", "HerdBuilder", "HerdBuilder %", "GridMaster", "GridMaster %",
    "CED", "CED Acc", "CED %", "BW", "BW Acc", "BW %", "WW", "WW Acc", "WW %", "YW",
    "YW Acc", "YW %", "ADG", "ADG Acc", "ADG %", "DMI", "DMI Acc", "DMI %", "Milk",
    "Milk Acc", "Milk %", "ME", "ME Acc", "ME %", "HPG", "HPG Acc", "HPG %", "CEM",
    "CEM Acc", "CEM %", "Stay", "Stay Acc", "Stay %", "Marb", "Marb Acc", "Marb %",
    "YG", "YG Acc", "YG %", "CW", "CW Acc", "CW %", "RE", "RE Acc", "RE %", "BF",
    "BF Acc", "BF %", "Actual BW", "WW Date", "Actual WW", "Sire ID", "Sire Reg #",
]

try:
    # Load data
    data = load_data(file_path)

    # Ensure Type column exists
    if "Type" not in data.columns:
        st.error("The 'Type' column is missing. Please check your file.")
    else:
        st.title("Bull Sale Inventory Dashboard")

        # Get unique bull types
        types = data["Type"].unique()

        # Sidebar for selecting bull types
        st.sidebar.header("Filter by Bull Type")
        selected_types = st.sidebar.multiselect(
            "Select the type(s) of bulls you're interested in:",
            options=types,
            default=types,  # Select all types by default
        )

        if selected_types:
            for selected_type in selected_types:
                # Filter data by each selected type
                filtered_data = data[data["Type"] == selected_type]

                # Display the editable data table for each selected type using st.dataframe
                st.subheader(f"{selected_type} Inventory")
                st.dataframe(
                    filtered_data[columns_to_display],
                    use_container_width=True
                )

                # Download the edited data for this type
                @st.cache_data
                def convert_df_to_csv(df):
                    return df.to_csv(index=False).encode("utf-8")

                csv = convert_df_to_csv(filtered_data)
                st.download_button(
                    label=f"Download {selected_type} Data as CSV",
                    data=csv,
                    file_name=f"{selected_type}_data.csv",
                    mime="text/csv",
                )
        else:
            st.info("Please select at least one bull type to view the data.")

except FileNotFoundError:
    st.error(f"The file at {file_path} was not found. Please check the path and try again.")
except Exception as e:
    st.error(f"An error occurred: {e}")