import pandas as pd
import plotly.express as px
import streamlit as st

# Page configuration
st.set_page_config(page_title="Sales Dashboard", page_icon=":bar_chart:", layout="wide")

# Function to read data from Excel file
@st.cache_data
def get_data_from_excel():
    df = pd.read_excel(
        io="supermarkt_sales.xlsx",
        engine="openpyxl",
        sheet_name="Sales",
        skiprows=3,
        usecols="B:R",
        nrows=1000,
    )
    # Add 'hour' column to the dataframe
    df["hour"] = pd.to_datetime(df["Time"], format="%H:%M:%S").dt.hour
    return df

df = get_data_from_excel()

# Sidebar for data filtering
st.sidebar.header("Please Filter Here:")
city = st.sidebar.multiselect(
    "Select the City:",
    options=df["City"].unique(),
    default=df["City"].unique()
)
customer_type = st.sidebar.multiselect(
    "Select the Customer Type:",
    options=df["Customer_type"].unique(),
    default=df["Customer_type"].unique(),
)
gender = st.sidebar.multiselect(
    "Select the Gender:",
    options=df["Gender"].unique(),
    default=df["Gender"].unique()
)
df_selection = df.query(
    "City == @city & Customer_type == @customer_type & Gender == @gender"
)

# Data verification
if df_selection.empty:
    st.warning("No data available based on the current filter settings!")
    st.stop()

# Main page
st.title(":bar_chart: Sales Dashboard")
st.markdown("##")

# Key Performance Indicators (KPIs)
total_sales = int(df_selection["Total"].sum())
average_rating = round(df_selection["Rating"].mean(), 1)
star_rating = ":star:" * int(round(average_rating, 0))
average_sale_by_transaction = round(df_selection["Total"].mean(), 2)

left_column, middle_column, right_column = st.columns(3)
with left_column:
    st.subheader("Total Sales:")
    st.subheader(f"US $ {total_sales:,}")
with middle_column:
    st.subheader("Average Rating:")
    st.subheader(f"{average_rating} {star_rating}")
with right_column:
    st.subheader("Average Sales Per Transaction:")
    st.subheader(f"US $ {average_sale_by_transaction}")

st.markdown("""---""")

# Sales by hour [Bar chart]
sales_by_hour = df_selection.groupby(by=["hour"])[["Total"]].sum()
fig_hourly_sales = px.bar(
    sales_by_hour,
    x=sales_by_hour.index,
    y="Total",
    title="<b>Sales by Hour</b>",
    color_discrete_sequence=px.colors.qualitative.Bold,
    template="plotly_white",
)
fig_hourly_sales.update_layout(
    xaxis=dict(tickmode="linear"),
    plot_bgcolor="rgba(0,0,0,0)",
    yaxis=(dict(showgrid=False)),
)

# Sales by product line [Pie chart]
sales_by_product_line = df_selection.groupby(by=["Product line"])[["Total"]].sum().sort_values(by="Total")
fig_product_sales_pie = px.pie(
    sales_by_product_line,
    names=sales_by_product_line.index,
    values="Total",
    title="<b>Sales by Product Line</b>",
    color_discrete_sequence=px.colors.qualitative.Set3,
    template="plotly_white",
)

# Sales by product line [Second bar chart]
fig_product_sales_bar = px.bar(
    sales_by_product_line,
    x=sales_by_product_line.index,
    y="Total",
    title="<b>Sales by Product Line</b>",
    color_discrete_sequence=px.colors.qualitative.Bold,
    template="plotly_white",
)
fig_product_sales_bar.update_layout(
    plot_bgcolor="rgba(0,0,0,0)",
    xaxis=(dict(showgrid=False))
)

# New chart: Sales by day [Line chart]
sales_by_day = df_selection.groupby(by=pd.Grouper(freq='D', key='Date'))[['Total']].sum()
fig_daily_sales = px.line(
    sales_by_day,
    x=sales_by_day.index,
    y="Total",
    title="<b>Sales by Day</b>",
    color_discrete_sequence=px.colors.qualitative.Light24,
    template="plotly_white",
)

# New chart: Price distribution [Histogram]
fig_price_distribution = px.histogram(
    df_selection,
    x="Unit price",
    title="<b>Price Distribution</b>",
    color_discrete_sequence=px.colors.qualitative.Bold,
    template="plotly_white",
)

# New chart: Relationship between price and quantity [Scatter plot]
fig_price_quantity_scatter = px.scatter(
    df_selection,
    x="Unit price",
    y="Quantity",
    title="<b>Price vs. Quantity</b>",
    color_discrete_sequence=px.colors.qualitative.Light24,
    template="plotly_white",
)

# Display the charts
left_column, middle_column, right_column = st.columns(3)
left_column.plotly_chart(fig_hourly_sales, use_container_width=True)
middle_column.plotly_chart(fig_product_sales_bar, use_container_width=True)
right_column.plotly_chart(fig_product_sales_pie, use_container_width=True)

st.markdown("""---""")

left_column, middle_column, right_column = st.columns(3)
left_column.plotly_chart(fig_daily_sales, use_container_width=True)
middle_column.plotly_chart(fig_price_distribution, use_container_width=True)
right_column.plotly_chart(fig_price_quantity_scatter, use_container_width=True)

# Hide Streamlit's style
hide_st_style = """
            <style>
            #MainMenu {visibility: hidden;}
            footer {visibility: hidden;}
            header {visibility: hidden;}
            </style>
            """
st.markdown(hide_st_style, unsafe_allow_html=True)