import streamlit as st
import pandas as pd
import matplotlib.pyplot as plt
import plotly.express as px



df = pd.read_csv(r'FY2021_merged_file.csv', dtype={"Fiscal Week": "string", 
                                            "Fiscal Year": "category", 
                                            "Chain Code": "category", 
                                            "Store": "category", 
                                            "Address": "string", 
                                            "Postal Code": "float", 
                                            "City": "category", 
                                            "State": "category", 
                                            "Container Code": "category", 
                                            "Sales Item Category": "category", 
                                            "units sold":"float", 
                                            "SalePrice":"float", 
                                            "sales $":"float"})

df["Postal Code"] = df["Postal Code"].convert_dtypes()
df["units sold"] = df["units sold"].convert_dtypes()

# Extract fiscal year and week from the 'Fiscal Week' column for sorting
df['Fiscal Year'] = df['Fiscal Week'].apply(lambda x: int(x.split(' ')[1]))  # Extract year as an integer
df['Week Number'] = df['Fiscal Week'].apply(lambda x: int(x.split('Week ')[1]))  # Extract week as an integer

# Sort the DataFrame by fiscal year and week number
df = df.sort_values(by=['Fiscal Year', 'Week Number'])

# Reformat 'Fiscal Week' for display (e.g., 'FY21W51')
df['Fiscal Week Short'] = df.apply(lambda x: f"FY{x['Fiscal Year']%100}W{x['Week Number']}", axis=1)

# Ensure the short fiscal week column is treated as a categorical variable and sorted by the order of appearance
df['Fiscal Week Short'] = pd.Categorical(df['Fiscal Week Short'], categories=df['Fiscal Week Short'].unique(), ordered=True)


# df['Fiscal Week'] = df['Fiscal Week'].apply(lambda x: x.replace('FY 20', 'FY').replace('Week ', 'W'))

# Sort by 'Fiscal Week'
# df = df.sort_values(by='Fiscal Week')

st.title('Sales Data Dashboard')


state = st.selectbox('Select State', df['State'].unique())
feature = st.selectbox('Select Feature for Grouping', ['Chain Code', 'Sales Item Category', 'Fiscal Week'])

# Filter the dataframe based on selections
filtered_df = df[df['State'] == state]

# Plot based on user's selection
if feature == 'Sales Item Category':
    st.subheader(f'Sales Data for {state} - Grouped by Sales Item Category')
    group_data = filtered_df.groupby(['Fiscal Week Short', 'Sales Item Category'])['units sold'].sum().reset_index()
    fig = px.bar(group_data, x='Fiscal Week Short', y='units sold', color='Sales Item Category',
                 title=f'Units Sold over Fiscal Week in {state} by Sales Item Category',
                 labels={'Units Sold': 'Units Sold'})

elif feature == 'Chain Code':
    st.subheader(f'Sales Data for {state} - Grouped by Chain Code')
    group_data = filtered_df.groupby(['Fiscal Week Short', 'Chain Code'])['units sold'].sum().reset_index()
    fig = px.bar(group_data, x='Fiscal Week Short', y='units sold', color='Chain Code',
                 title=f'Units Sold over Fiscal Week in {state} by Chain Code',
                 labels={'Units Sold': 'Units Sold'})

print(df.head(5))
# Display the interactive plot
st.plotly_chart(fig)