app.py

import os
import math
from functools import lru_cache

import numpy as np
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
import streamlit as st
from sklearn.cluster import KMeans
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler
from sklearn.tree import DecisionTreeClassifier, plot_tree
import matplotlib.pyplot as plt

st.set_page_config(
    page_title="freshie - Perishable Retail Optimization",
    page_icon="🐱",
    layout="wide",
    initial_sidebar_state="expanded",
)

DATA_CANDIDATES = [
    os.environ.get("DATA_PATH", ""),
    "perishable_goods_management.csv",
    "/app/perishable_goods_management.csv",
    "/data/perishable_goods_management.csv",
    "/mnt/data/perishable_goods_management.csv",
]

CATEGORY_COLORS = {
    "Produce": "#2E8B57",
    "Dairy": "#1E90FF",
    "Meat": "#B22222",
    "Seafood": "#20B2AA",
    "Bakery": "#D2691E",
    "Ready_to_Eat": "#8A2BE2",
}

FOCUS_CATEGORY = "Bakery"

REGION_COORDS = {
    "West": (34.05, -118.24),
    "Northeast": (40.71, -74.00),
    "Southeast": (33.75, -84.39),
    "Midwest": (41.88, -87.63),
    "Southwest": (32.78, -96.80),
}


COLUMN_GROUPS = {
    "Identity & network": [
        "record_id", "product_id", "product_name", "category", "store_id", "region", "supplier_id"
    ],
    "Time & expiry": [
        "transaction_date", "expiration_date", "shelf_life_days", "day_of_week", "month", "days_until_expiry"
    ],
    "Storage & handling": [
        "storage_temp", "temp_deviation", "temp_abuse_events", "handling_score", "packaging_score", "spoilage_risk"
    ],
    "Demand & inventory": [
        "initial_quantity", "daily_demand", "units_sold", "leftover_units", "stockout_flag", "lost_sales_units", "sell_through_pct"
    ],
    "Pricing & promotions": [
        "base_price", "cost_price", "selling_price", "discount_pct", "markdown_applied", "is_promoted"
    ],
    "Waste & profitability": [
        "units_wasted", "waste_pct", "profit", "profit_margin_pct"
    ]
}





def inject_css():
    st.markdown(
        """
        <style>
        .stApp {
            background:
                linear-gradient(rgba(255,250,245,0.92), rgba(247,251,255,0.93)),
                url("https://images.unsplash.com/photo-1542838132-92c53300491e?auto=format&fit=crop&w=1600&q=80");
            background-size: cover;
            background-position: center;
            background-attachment: fixed;
        }
        [data-testid="stDataFrame"] {
            background: rgba(255,255,255,0.80);
            border-radius: 14px;
            padding: 4px;
        }
        .section-card {
            background: rgba(255,255,255,0.82);
            border: 1px solid rgba(31,45,61,0.07);
            border-radius: 16px;
            padding: 12px 14px;
            box-shadow: 0 8px 18px rgba(0,0,0,0.04);
        }
        .main-title {
            display:flex;
            align-items:center;
            gap:14px;
            margin-bottom:6px;
        }
        .logo-badge {
            font-size: 2.2rem;
            line-height: 1;
            background: linear-gradient(135deg, #ffe6cc 0%, #ffd7eb 100%);
            border-radius: 18px;
            padding: 10px 14px;
            box-shadow: 0 8px 22px rgba(0,0,0,0.06);
        }
        .brand-title {
            font-size: 2.6rem;
            font-weight: 800;
            color: #1f2d3d;
            letter-spacing: -0.02em;
        }
        .brand-sub {
            color: #5e6b78;
            margin-top: -2px;
            margin-bottom: 12px;
        }
        div[data-testid="stMetric"] {
            background: rgba(255,255,255,0.82);
            border: 1px solid rgba(31,45,61,0.07);
            border-radius: 16px;
            padding: 12px 14px;
            box-shadow: 0 8px 18px rgba(0,0,0,0.04);
        }
        .mini-note {
            background:#fff7ed;
            border-left:4px solid #fb923c;
            padding:10px 12px;
            border-radius:10px;
            margin: 8px 0 14px 0;
            color:#7c4a03;
        }
        </style>
        """,
        unsafe_allow_html=True,
    )


def category_icon(category: str) -> str:
    mapping = {
        "Bakery": "🥐",
        "Dairy": "🥛",
        "Meat": "🥩",
        "Seafood": "🐟",
        "Produce": "🥬",
        "Ready_to_Eat": "🍱",
        "Beverages": "🧃",
        "Frozen_Meals": "🧊",
        "Pharmaceuticals": "💊",
        "Deli": "🧺",
    }
    return mapping.get(str(category), "📦")


def with_product_elements(frame: pd.DataFrame, product_col: str = "product_name", category_col: str = "category") -> pd.DataFrame:
    out = frame.copy()
    if category_col in out.columns:
        out["category_tag"] = out[category_col].apply(lambda x: f"{category_icon(x)} {x}")
    if product_col in out.columns and category_col in out.columns:
        out["product_item"] = out.apply(lambda r: f"{category_icon(r[category_col])} {r[product_col]}", axis=1)
    return out


def region_anchor(region: str):
    return REGION_COORDS.get(region, (39.0, -96.0))


def attach_store_locations(df: pd.DataFrame) -> pd.DataFrame:
    stores = sorted(df["store_id"].dropna().unique())
    rows = []
    for store in stores:
        sub = df[df["store_id"] == store]
        region = str(sub["region"].mode().iloc[0]) if not sub.empty else "West"
        base_lat, base_lon = region_anchor(region)
        seed = abs(hash(store)) % 10000
        rng = np.random.default_rng(seed)
        rows.append(
            {
                "store_id": store,
                "store_lat": base_lat + rng.uniform(-0.55, 0.55),
                "store_lon": base_lon + rng.uniform(-0.75, 0.75),
            }
        )
    loc = pd.DataFrame(rows)
    return df.merge(loc, on="store_id", how="left")


def find_data_path() -> str:
    for path in DATA_CANDIDATES:
        if path and os.path.exists(path):
            return path
    raise FileNotFoundError(
        "perishable_goods_management.csv not found. Put it next to app.py or set DATA_PATH."
    )


@st.cache_data(show_spinner=False)
def load_data() -> pd.DataFrame:
    path = find_data_path()
    df = pd.read_csv(path)

    df["transaction_date"] = pd.to_datetime(df["transaction_date"], errors="coerce")
    df["expiration_date"] = pd.to_datetime(df["expiration_date"], errors="coerce")

    df["sell_through_pct"] = np.where(
        df["initial_quantity"] > 0, df["units_sold"] / df["initial_quantity"], 0
    )
    df["stock_demand_ratio"] = np.where(
        df["daily_demand"] > 0, df["initial_quantity"] / df["daily_demand"], np.nan
    )
    df["gross_margin"] = df["selling_price"] - df["cost_price"]
    df["leftover_units"] = (df["initial_quantity"] - df["units_sold"]).clip(lower=0)
    df["stockout_flag"] = (df["daily_demand"] > df["initial_quantity"]).astype(int)
    df["lost_sales_units"] = (df["daily_demand"] - df["units_sold"]).clip(lower=0)
    if "waste_pct" in df.columns and df["waste_pct"].max() > 1:
        df["waste_pct"] = df["waste_pct"] / 100
    df["waste_pct"] = df["waste_pct"].clip(lower=0, upper=1)
    df["value_score"] = (
        (1 - df["waste_pct"].clip(0, 1)) * 0.35
        + df["profit_margin_pct"].clip(lower=0) / 100 * 0.25
        + (1 - df["days_until_expiry"].clip(upper=14) / 14) * 0.15
        + df["discount_pct"].clip(0, 0.5) * 0.25
    )
    df["expiry_bucket"] = pd.cut(
        df["days_until_expiry"],
        bins=[-1, 1, 3, 7, 30, 10_000],
        labels=["<=1d", "2-3d", "4-7d", "8-30d", ">30d"],
    )
    df["high_waste_flag"] = (df["waste_pct"] >= df["waste_pct"].quantile(0.75)).astype(int)
    df["waste_high"] = (df["waste_pct"] > df["waste_pct"].median()).astype(int)
    df["profit_high"] = (df["profit"] > df["profit"].median()).astype(int)
    df["promo_effective"] = ((df["is_promoted"] == 1) & (df["sell_through_pct"] > df["sell_through_pct"].median())).astype(int)
    return df


@st.cache_data(show_spinner=False)
def fit_segments(df: pd.DataFrame) -> pd.DataFrame:
    work = df[[
        "daily_demand",
        "initial_quantity",
        "waste_pct",
        "shelf_life_days",
        "stock_demand_ratio",
        "sell_through_pct",
    ]].replace([np.inf, -np.inf], np.nan).dropna().copy()

    sample_size = min(len(work), 20000)
    work = work.sample(sample_size, random_state=42)
    scaler = StandardScaler()
    X = scaler.fit_transform(work)
    km = KMeans(n_clusters=4, random_state=42, n_init=10)
    work["cluster"] = km.fit_predict(X)
    return work


@st.cache_resource(show_spinner=False)
def fit_risk_model(df: pd.DataFrame):
    features = [
        "daily_demand",
        "initial_quantity",
        "shelf_life_days",
        "days_until_expiry",
        "temp_deviation",
        "temp_abuse_events",
        "handling_score",
        "packaging_score",
        "spoilage_risk",
        "discount_pct",
        "markdown_applied",
        "is_weekend",
        "supplier_score",
    ]
    X = df[features]
    y = df["high_waste_flag"]
    X_train, X_test, y_train, y_test = train_test_split(
        X, y, test_size=0.2, random_state=42, stratify=y
    )
    model = RandomForestClassifier(
        n_estimators=120, random_state=42, n_jobs=-1, max_depth=10
    )
    model.fit(X_train, y_train)
    importances = pd.Series(model.feature_importances_, index=features).sort_values(ascending=False)
    return model, importances


@lru_cache(maxsize=1)
def cluster_name_map():
    return {
        0: "Stable performers",
        1: "Overstocked slow movers",
        2: "Short-life high risk",
        3: "High demand fast movers",
    }


def apply_filters(df: pd.DataFrame):
    st.sidebar.header("Filters")

    if "filter_regions" not in st.session_state:
        st.session_state["filter_regions"] = []
    if "filter_stores" not in st.session_state:
        st.session_state["filter_stores"] = []

    all_regions = sorted(df["region"].dropna().unique())
    all_stores = sorted(df["store_id"].dropna().unique())

    # If the user selected stores directly, infer the matching region(s).
    if st.session_state["filter_stores"] and not st.session_state["filter_regions"]:
        inferred_regions = sorted(
            df.loc[df["store_id"].isin(st.session_state["filter_stores"]), "region"]
            .dropna()
            .unique()
        )
        st.session_state["filter_regions"] = inferred_regions

    # Region selection drives store options.
    regions = st.sidebar.multiselect(
        "Region",
        all_regions,
        key="filter_regions",
    )

    available_stores = sorted(
        df.loc[df["region"].isin(regions), "store_id"].dropna().unique()
    ) if regions else all_stores

    # Keep only stores that still belong to the selected region(s).
    st.session_state["filter_stores"] = [
        s for s in st.session_state["filter_stores"] if s in available_stores
    ]

    stores = st.sidebar.multiselect(
        "Store",
        available_stores,
        key="filter_stores",
    )

    # If stores are selected, make region selection follow them exactly.
    if stores:
        inferred_regions = sorted(
            df.loc[df["store_id"].isin(stores), "region"].dropna().unique()
        )
        if inferred_regions != regions:
            st.session_state["filter_regions"] = inferred_regions
            regions = inferred_regions

    categories = st.sidebar.multiselect("Category", sorted(df["category"].dropna().unique()), default=[])
    expiry_range = st.sidebar.slider("Days until expiry", 0, int(df["days_until_expiry"].max()), (0, 30))
    weekend_choice = st.sidebar.selectbox("Day type", ["All", "Weekday", "Weekend"])

    filtered = df.copy()
    if regions:
        filtered = filtered[filtered["region"].isin(regions)]
    if stores:
        filtered = filtered[filtered["store_id"].isin(stores)]
    if categories:
        filtered = filtered[filtered["category"].isin(categories)]
    filtered = filtered[
        (filtered["days_until_expiry"] >= expiry_range[0])
        & (filtered["days_until_expiry"] <= expiry_range[1])
    ]
    if weekend_choice == "Weekday":
        filtered = filtered[filtered["is_weekend"] == 0]
    elif weekend_choice == "Weekend":
        filtered = filtered[filtered["is_weekend"] == 1]
    return filtered


def metric_row(df: pd.DataFrame):
    c1, c2, c3, c4, c5 = st.columns(5)
    c1.metric("Waste %", f"{df['waste_pct'].mean():.1%}")
    c2.metric("Profit", f"€{df['profit'].mean():.2f}")
    c3.metric("Sell-through", f"{df['sell_through_pct'].mean():.1%}")
    c4.metric("Units wasted", f"{df['units_wasted'].mean():.1f}")
    c5.metric("Markdown rate", f"{df['markdown_applied'].mean():.1%}")


def manager_dashboard(df: pd.DataFrame):
    st.subheader("Manager Mode")
    metric_row(df)

    a, b = st.columns([1.2, 1])
    with a:
        trend = df.groupby(df["transaction_date"].dt.to_period("M").astype(str))[["waste_pct", "profit"]].mean().reset_index()
        fig = go.Figure()
        fig.add_trace(go.Scatter(x=trend["transaction_date"], y=trend["waste_pct"], name="Waste %", mode="lines+markers"))
        fig.add_trace(go.Scatter(x=trend["transaction_date"], y=trend["profit"], name="Profit", mode="lines+markers", yaxis="y2"))
        fig.update_layout(
            title="Monthly Waste and Profit Trend",
            yaxis=dict(title="Waste %"),
            yaxis2=dict(title="Profit", overlaying="y", side="right"),
            legend=dict(orientation="h"),
            margin=dict(l=10, r=10, t=40, b=10),
        )
        st.plotly_chart(fig, use_container_width=True)
    with b:
        top_risk = (
            df.groupby("category")[["waste_pct", "profit", "stock_demand_ratio"]]
            .mean()
            .sort_values("waste_pct", ascending=False)
            .head(8)
            .reset_index()
        )
        fig = px.bar(top_risk, x="waste_pct", y="category", orientation="h", title="High Waste Categories")
        st.plotly_chart(fig, use_container_width=True)

    c1, c2 = st.columns(2)
    with c1:
        store_risk = (
            df.groupby("store_id")[["waste_pct", "profit", "temp_deviation"]]
            .mean()
            .sort_values(["waste_pct", "temp_deviation"], ascending=[False, False])
            .head(15)
            .reset_index()
        )
        st.dataframe(store_risk, use_container_width=True, hide_index=True)
    with c2:
        expiry = df.groupby("expiry_bucket")[["waste_pct", "profit", "discount_pct"]].mean().reset_index()
        fig = px.line(expiry, x="expiry_bucket", y=["waste_pct", "profit", "discount_pct"], markers=True, title="Expiry Stage Performance")
        st.plotly_chart(fig, use_container_width=True)



def forecast_region_demand(cat_df: pd.DataFrame, region: str) -> pd.DataFrame:
    d = cat_df[cat_df["region"] == region].copy()
    if d.empty:
        return pd.DataFrame()
    ts = d.groupby("transaction_date")["daily_demand"].mean().reset_index().sort_values("transaction_date")
    if len(ts) < 14:
        return pd.DataFrame()
    recent = ts.tail(56).copy()
    weekday_avg = recent.groupby(recent["transaction_date"].dt.dayofweek)["daily_demand"].mean().to_dict()
    last_date = ts["transaction_date"].max()
    future_dates = pd.date_range(last_date + pd.Timedelta(days=1), periods=14, freq="D")
    future = pd.DataFrame({
        "transaction_date": future_dates,
        "daily_demand": [weekday_avg.get(d.dayofweek, ts["daily_demand"].tail(14).mean()) for d in future_dates],
        "series": "Forecast"
    })
    hist = ts.tail(60).copy()
    hist["series"] = "Actual"
    return pd.concat([hist, future], ignore_index=True)



def build_weekpart_inventory_views(cat_df: pd.DataFrame):
    tmp = cat_df.copy()
    tmp["week_part"] = np.where(tmp["is_weekend"] == 1, "Weekend", "Weekday")

    store_view = (
        tmp.groupby(["store_id", "region", "week_part"])
        .agg(
            avg_inventory=("initial_quantity", "mean"),
            avg_remaining=("leftover_units", "mean"),
            avg_demand=("daily_demand", "mean"),
            avg_units_sold=("units_sold", "mean"),
            sell_through=("sell_through_pct", "mean"),
            stockout_rate=("stockout_flag", "mean"),
            unmet_demand=("lost_sales_units", "mean"),
            waste_pct=("waste_pct", "mean"),
            units_wasted=("units_wasted", "mean"),
            avg_profit=("profit", "mean"),
            avg_margin=("profit_margin_pct", "mean"),
            markdown_rate=("markdown_applied", "mean"),
            promo_rate=("is_promoted", "mean"),
            avg_days_until_expiry=("days_until_expiry", "mean"),
            temp_dev=("temp_deviation", "mean"),
            spoilage_risk=("spoilage_risk", "mean"),
        )
        .reset_index()
    )

    region_view = (
        tmp.groupby(["region", "week_part"])
        .agg(
            avg_inventory=("initial_quantity", "mean"),
            avg_remaining=("leftover_units", "mean"),
            avg_demand=("daily_demand", "mean"),
            avg_units_sold=("units_sold", "mean"),
            sell_through=("sell_through_pct", "mean"),
            stockout_rate=("stockout_flag", "mean"),
            unmet_demand=("lost_sales_units", "mean"),
            waste_pct=("waste_pct", "mean"),
            units_wasted=("units_wasted", "mean"),
            avg_profit=("profit", "mean"),
            avg_margin=("profit_margin_pct", "mean"),
            markdown_rate=("markdown_applied", "mean"),
            promo_rate=("is_promoted", "mean"),
            avg_days_until_expiry=("days_until_expiry", "mean"),
            temp_dev=("temp_deviation", "mean"),
            spoilage_risk=("spoilage_risk", "mean"),
        )
        .reset_index()
    )

    return store_view, region_view

def manager_category_intelligence(df: pd.DataFrame):
    st.subheader("Category Intelligence")
    visible_categories = sorted(df["category"].dropna().unique())
    if not visible_categories:
        st.warning("No category remains after filtering.")
        return

    cat_df = df.copy()
    focus = visible_categories[0] if len(visible_categories) == 1 else ", ".join(visible_categories[:3]) + (" ..." if len(visible_categories) > 3 else "")

    st.markdown(
        f"Filtered category scope: **{focus}**. This page compares regional operations, inventory, profitability, demand, stockout and waste trade-offs for the current sidebar-filtered view."
    )

    c1, c2, c3, c4, c5, c6 = st.columns(6)
    c1.metric("Avg demand", f"{cat_df['daily_demand'].mean():.1f}")
    c2.metric("Avg stock", f"{cat_df['initial_quantity'].mean():.1f}")
    c3.metric("Avg remaining", f"{cat_df['leftover_units'].mean():.1f}")
    c4.metric("Sell-through", f"{cat_df['sell_through_pct'].mean():.1%}")
    c5.metric("Stockout rate", f"{cat_df['stockout_flag'].mean():.1%}")
    c6.metric("Waste rate", f"{cat_df['waste_pct'].mean():.1%}")

    with st.expander("42-column feature map grouped into business themes"):
        for group, cols in COLUMN_GROUPS.items():
            st.markdown(f"**{group}**")
            st.code(", ".join(cols), language=None)

    region_summary = (
        cat_df.groupby("region")
        .agg(
            avg_demand=("daily_demand", "mean"),
            avg_stock=("initial_quantity", "mean"),
            avg_remaining=("leftover_units", "mean"),
            avg_units_sold=("units_sold", "mean"),
            sell_through=("sell_through_pct", "mean"),
            avg_profit=("profit", "mean"),
            avg_margin=("profit_margin_pct", "mean"),
            waste_pct=("waste_pct", "mean"),
            units_wasted=("units_wasted", "mean"),
            markdown_rate=("markdown_applied", "mean"),
            promo_rate=("is_promoted", "mean"),
            temp_dev=("temp_deviation", "mean"),
            shelf_life=("shelf_life_days", "mean"),
            days_until_expiry=("days_until_expiry", "mean"),
            stockout_rate=("stockout_flag", "mean"),
            lost_sales=("lost_sales_units", "mean"),
            spoilage_risk=("spoilage_risk", "mean"),
        )
        .reset_index()
    )

    store_summary = (
        cat_df.groupby(["store_id", "region"])
        .agg(
            avg_inventory=("initial_quantity", "mean"),
            avg_remaining=("leftover_units", "mean"),
            avg_demand=("daily_demand", "mean"),
            avg_units_sold=("units_sold", "mean"),
            sell_through=("sell_through_pct", "mean"),
            avg_profit=("profit", "mean"),
            avg_margin=("profit_margin_pct", "mean"),
            waste_pct=("waste_pct", "mean"),
            units_wasted=("units_wasted", "mean"),
            stockout_rate=("stockout_flag", "mean"),
            lost_sales=("lost_sales_units", "mean"),
            markdown_rate=("markdown_applied", "mean"),
            promo_rate=("is_promoted", "mean"),
            avg_expiry_days=("days_until_expiry", "mean"),
            temp_dev=("temp_deviation", "mean"),
        )
        .reset_index()
    )
    region_summary["profit_size"] = region_summary["avg_profit"].clip(lower=0) + 1

    a, b = st.columns([1.2, 1])
    with a:
        melt = region_summary.melt(
            id_vars="region",
            value_vars=["avg_demand", "avg_stock", "avg_profit"],
            var_name="metric",
            value_name="value",
        )
        fig = px.bar(
            melt,
            x="region",
            y="value",
            color="metric",
            barmode="group",
            title=f"{focus}: regional operations, inventory and profit comparison",
        )
        st.plotly_chart(fig, use_container_width=True)
    with b:
        fig = px.scatter(
            region_summary,
            x="stockout_rate",
            y="waste_pct",
            size="profit_size",
            color="region",
            hover_data=[
                "avg_demand", "avg_stock", "avg_remaining", "lost_sales", "days_until_expiry", "spoilage_risk"
            ],
            title=f"{focus}: stockout vs waste trade-off by region",
        )
        st.plotly_chart(fig, use_container_width=True)

    c1, c2 = st.columns([1, 1.2])
    with c1:
        st.markdown("### Regional KPI table")
        st.dataframe(
            region_summary.sort_values("avg_profit", ascending=False),
            use_container_width=True,
            hide_index=True,
        )
    with c2:
        region_choice = st.selectbox("Forecast region", sorted(cat_df["region"].dropna().unique()))
        forecast_df = forecast_region_demand(cat_df, region_choice)
        if not forecast_df.empty:
            fig = px.line(
                forecast_df,
                x="transaction_date",
                y="daily_demand",
                color="series",
                title=f"{focus}: 60-day actual + 14-day demand forecast for {region_choice}",
            )
            st.plotly_chart(fig, use_container_width=True)

    st.markdown("### Same-store / same-region weekday vs weekend inventory analysis")
    st.markdown('<div class="mini-note">This section follows the current sidebar filters. Region, store, and category stay aligned with the left panel.</div>', unsafe_allow_html=True)
    st.markdown('<div class="mini-note">Store-level comparison now follows the current sidebar filter. If the filter includes one store, you get a pure same-store comparison. If it includes multiple stores, you get the filtered store portfolio comparison.</div>', unsafe_allow_html=True)
    store_weekpart, region_weekpart = build_weekpart_inventory_views(cat_df)

    d1, d2 = st.columns([1.2, 1])
    with d1:
        region_week_melt = region_weekpart.melt(
            id_vars=["region", "week_part"],
            value_vars=[
                "avg_inventory", "avg_demand", "avg_remaining",
                "avg_profit", "stockout_rate", "waste_pct"
            ],
            var_name="metric",
            value_name="value",
        )
        fig = px.bar(
            region_week_melt,
            x="region",
            y="value",
            color="week_part",
            facet_row="metric",
            barmode="group",
            title=f"{focus}: same-region weekday vs weekend comparison",
            height=1100,
        )
        fig.update_yaxes(matches=None)
        st.plotly_chart(fig, use_container_width=True)
    with d2:
        filtered_store_focus = (
            store_weekpart.groupby("week_part")
            .agg(
                avg_inventory=("avg_inventory", "mean"),
                avg_demand=("avg_demand", "mean"),
                avg_remaining=("avg_remaining", "mean"),
                unmet_demand=("unmet_demand", "mean"),
                stockout_rate=("stockout_rate", "mean"),
                waste_pct=("waste_pct", "mean"),
                avg_profit=("avg_profit", "mean"),
            )
            .reset_index()
        )
        if not filtered_store_focus.empty:
            fig = px.bar(
                filtered_store_focus.melt(
                    id_vars=["week_part"],
                    value_vars=[
                        "avg_inventory", "avg_demand", "avg_remaining",
                        "unmet_demand", "stockout_rate", "waste_pct", "avg_profit"
                    ],
                    var_name="metric",
                    value_name="value",
                ),
                x="metric",
                y="value",
                color="week_part",
                barmode="group",
                title="Filtered stores: weekday vs weekend comparison",
            )
            st.plotly_chart(fig, use_container_width=True)

    e1, e2 = st.columns([1.05, 1.15])
    with e1:
        region_week_pivot = region_weekpart.pivot(
            index="region",
            columns="week_part",
            values=[
                "avg_inventory", "avg_remaining", "avg_demand", "avg_units_sold",
                "sell_through", "stockout_rate", "unmet_demand", "waste_pct",
                "units_wasted", "avg_profit", "avg_margin", "markdown_rate",
                "promo_rate", "avg_days_until_expiry", "temp_dev", "spoilage_risk"
            ],
        )
        region_week_pivot.columns = [f"{a}_{b}" for a, b in region_week_pivot.columns]
        region_week_pivot = region_week_pivot.reset_index()

        delta_metrics = [
            "avg_inventory", "avg_remaining", "avg_demand", "avg_units_sold",
            "sell_through", "stockout_rate", "unmet_demand", "waste_pct",
            "units_wasted", "avg_profit", "avg_margin", "markdown_rate",
            "promo_rate", "avg_days_until_expiry", "temp_dev", "spoilage_risk"
        ]
        for metric in delta_metrics:
            wd = f"{metric}_Weekday"
            we = f"{metric}_Weekend"
            if wd in region_week_pivot.columns and we in region_week_pivot.columns:
                region_week_pivot[f"{metric}_weekend_minus_weekday"] = region_week_pivot[we] - region_week_pivot[wd]

        st.markdown("### Region weekday/weekend delta table")
        st.dataframe(region_week_pivot, use_container_width=True, hide_index=True)
    with e2:
        filtered_store_table = store_weekpart.copy()
        if not filtered_store_table.empty:
            filtered_store_table = filtered_store_table.rename(columns={"store_id": "store"})
            st.markdown("### Filter-aligned store weekday/weekend indicators")
            st.dataframe(filtered_store_table, use_container_width=True, hide_index=True)

    weekday_view = region_weekpart[region_weekpart["week_part"] == "Weekday"].set_index("region")
    weekend_view = region_weekpart[region_weekpart["week_part"] == "Weekend"].set_index("region")
    common_regions = sorted(set(weekday_view.index).intersection(set(weekend_view.index)))

    if common_regions:
        for region in common_regions:
            wd = weekday_view.loc[region]
            we = weekend_view.loc[region]
            pieces = []
            demand_delta = we["avg_demand"] - wd["avg_demand"]
            inventory_delta = we["avg_inventory"] - wd["avg_inventory"]
            remaining_delta = we["avg_remaining"] - wd["avg_remaining"]
            stockout_delta = we["stockout_rate"] - wd["stockout_rate"]
            waste_delta = we["waste_pct"] - wd["waste_pct"]
            profit_delta = we["avg_profit"] - wd["avg_profit"]
            markdown_delta = we["markdown_rate"] - wd["markdown_rate"]
            promo_delta = we["promo_rate"] - wd["promo_rate"]

            if demand_delta > 0:
                pieces.append(f"weekend demand is higher by {demand_delta:.1f}")
            else:
                pieces.append(f"weekday demand is higher by {abs(demand_delta):.1f}")

            if inventory_delta > 0:
                pieces.append(f"weekend inventory is higher by {inventory_delta:.1f}")
            else:
                pieces.append(f"weekday inventory is higher by {abs(inventory_delta):.1f}")

            if remaining_delta > 0:
                pieces.append(f"weekend leftover stock rises by {remaining_delta:.1f}")
            elif remaining_delta < 0:
                pieces.append(f"weekday leftover stock rises by {abs(remaining_delta):.1f}")

            if stockout_delta > 0.01:
                pieces.append(f"weekend stockout risk is worse by {stockout_delta:.1%}")
            elif stockout_delta < -0.01:
                pieces.append(f"weekday stockout risk is worse by {abs(stockout_delta):.1%}")

            if waste_delta > 0.01:
                pieces.append(f"weekend waste is higher by {waste_delta:.1%}")
            elif waste_delta < -0.01:
                pieces.append(f"weekday waste is higher by {abs(waste_delta):.1%}")

            pieces.append(f"profit shifts by €{profit_delta:.2f} from weekday to weekend")
            pieces.append(f"markdown changes by {markdown_delta:.1%} on weekends")
            pieces.append(f"promotion rate changes by {promo_delta:.1%} on weekends")

            st.markdown(f"- **{region}**: " + "; ".join(pieces) + ".")

    st.markdown("### Regional recommendations")
    mean_stockout = region_summary["stockout_rate"].mean()
    mean_waste = region_summary["waste_pct"].mean()
    mean_margin = region_summary["avg_margin"].mean()
    mean_temp = region_summary["temp_dev"].mean()
    mean_expiry = region_summary["days_until_expiry"].mean()

    for _, r in region_summary.iterrows():
        advice = []
        if r["stockout_rate"] > mean_stockout:
            advice.append("raise replenishment and morning safety stock")
        if r["waste_pct"] > mean_waste:
            advice.append("start markdown earlier")
        if r["avg_margin"] < mean_margin:
            advice.append("use bundles instead of deeper discounts")
        if r["temp_dev"] > mean_temp:
            advice.append("tighten storage handling")
        if r["days_until_expiry"] < mean_expiry:
            advice.append("prioritize fresher inbound allocation")
        if not advice:
            advice.append("maintain and scale current playbook")
        st.markdown(f"- **{r['region']}**: " + "; ".join(advice) + ".")

    st.markdown("### Marketing design simulator")
    m1, m2, m3, m4 = st.columns(4)
    promo_region = m1.selectbox("Target region", sorted(cat_df["region"].dropna().unique()), key="cat_region")
    promo_type = m2.selectbox("Promo type", ["Early markdown", "Breakfast bundle", "Happy-hour discount", "Loyalty coupon"])
    discount = m3.slider("Discount %", 0, 40, 15, key="cat_discount")
    duration = m4.slider("Duration (days)", 1, 10, 4, key="cat_duration")

    base = cat_df[cat_df["region"] == promo_region].copy()
    base_sales = base["units_sold"].mean()
    base_waste = base["waste_pct"].mean()
    base_profit = base["profit"].mean()
    promo_factor = {"Early markdown": 0.12, "Breakfast bundle": 0.16, "Happy-hour discount": 0.10, "Loyalty coupon": 0.08}[promo_type]
    sales_lift = promo_factor + discount / 180 + min(duration / 60, 0.10)
    waste_drop = min(0.42, promo_factor + discount / 200)
    margin_drag = discount / 160 * (0.75 if promo_type == "Breakfast bundle" else 1.0)
    est_sales = base_sales * (1 + sales_lift)
    est_waste = max(base_waste * (1 - waste_drop), 0)
    est_profit = base_profit * (1 + sales_lift - margin_drag)

    x1, x2, x3 = st.columns(3)
    x1.metric("Estimated avg units sold", f"{est_sales:.2f}", delta=f"+{(est_sales-base_sales):.2f}")
    x2.metric("Estimated waste", f"{est_waste:.1%}", delta=f"-{(base_waste-est_waste):.1%}")
    x3.metric("Estimated avg profit", f"€{est_profit:.2f}", delta=f"€{(est_profit-base_profit):.2f}")

def generate_summary(df: pd.DataFrame) -> str:
    waste = df["waste_pct"].mean()
    profit = df["profit"].mean()
    stockout = (df["daily_demand"] > df["initial_quantity"]).mean()
    worst_region = df.groupby("region")["waste_pct"].mean().idxmax()
    best_region = df.groupby("region")["profit"].mean().idxmax()
    return f"""
- Average waste rate is **{waste:.1%}**, indicating {'high inefficiency' if waste > 0.2 else 'acceptable performance'}.
- Average profit is **EUR {profit:.2f}**, with strongest performance in **{best_region}**.
- Stockout rate is **{stockout:.1%}**, suggesting {'understocking risk' if stockout > 0.2 else 'balanced supply'}.

Key issue:
- Highest waste occurs in **{worst_region}**.

Recommended actions:
- Advance markdown timing for short-life products.
- Rebalance inventory using demand signals.
- Use bundles instead of deeper discounts where possible.
"""


def generate_slide_insights(df: pd.DataFrame):
    insights = []
    if df["waste_pct"].mean() > 0.2:
        insights.append("High waste is driven by short shelf-life items and delayed markdown timing.")
    if (df["daily_demand"] > df["initial_quantity"]).mean() > 0.2:
        insights.append("Frequent stockouts indicate under-forecasting of demand in key regions.")
    if df["discount_pct"].mean() > 0.25:
        insights.append("Over-reliance on discounting is reducing margin quality.")
    if df["temp_deviation"].mean() > 2:
        insights.append("Temperature deviation is materially contributing to spoilage risk.")
    if not insights:
        insights.append("Current performance is stable, with room to optimize promotion quality and inventory precision.")
    return insights


def build_transfer_suggestions(view_df: pd.DataFrame, full_df: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame]:
    if "store_lat" not in view_df.columns or "store_lon" not in view_df.columns:
        view_df = attach_store_locations(view_df.copy())
    if "store_lat" not in full_df.columns or "store_lon" not in full_df.columns:
        full_df = attach_store_locations(full_df.copy())

    store_summary = (
        view_df.groupby(["store_id", "region"])
        .agg(
            total_inventory=("initial_quantity", "sum"),
            units_sold=("units_sold", "sum"),
            remaining_inventory=("leftover_units", "sum"),
            total_demand=("daily_demand", "sum"),
            unmet_demand=("lost_sales_units", "sum"),
        )
        .reset_index()
    )
    store_summary["inventory_gap"] = store_summary["remaining_inventory"] - store_summary["unmet_demand"]

    receiving_need = (
        view_df.groupby(["store_id", "region", "category", "store_lat", "store_lon"])
        .agg(
            remaining_inventory=("leftover_units", "sum"),
            demand=("daily_demand", "sum"),
            unmet_demand=("lost_sales_units", "sum"),
            receiver_days_until_expiry=("days_until_expiry", "mean"),
        )
        .reset_index()
    )
    receiving_need = receiving_need[receiving_need["unmet_demand"] > 0].copy()
    if receiving_need.empty:
        return store_summary, pd.DataFrame(columns=[
            "store_id", "region", "category", "remaining_inventory", "demand", "unmet_demand",
            "recommended_transfer_qty", "same_region_options", "cross_region_options", "best_route"
        ])

    donor_pool = (
        full_df.groupby(["store_id", "region", "category", "store_lat", "store_lon"])
        .agg(
            donor_remaining=("leftover_units", "sum"),
            donor_demand=("daily_demand", "sum"),
            donor_days_until_expiry=("days_until_expiry", "mean"),
        )
        .reset_index()
    )
    donor_pool["surplus_qty"] = donor_pool["donor_remaining"] - donor_pool["donor_demand"]
    donor_pool = donor_pool[donor_pool["surplus_qty"] > 0].copy()

    def distance_km(lat1, lon1, lat2, lon2):
        # simple equirectangular approximation, good enough for prioritization
        x = (math.radians(lon2) - math.radians(lon1)) * math.cos((math.radians(lat1) + math.radians(lat2)) / 2)
        y = math.radians(lat2) - math.radians(lat1)
        return 6371 * math.sqrt(x * x + y * y)

    rows = []
    for _, r in receiving_need.iterrows():
        donors = donor_pool[
            (donor_pool["category"] == r["category"]) &
            (donor_pool["store_id"] != r["store_id"]) &
            (donor_pool["surplus_qty"] > 0)
        ].copy()

        if donors.empty:
            row = r.to_dict()
            row["recommended_transfer_qty"] = 0
            row["same_region_options"] = "No same-region donor"
            row["cross_region_options"] = "No cross-region donor"
            row["best_route"] = "No feasible transfer"
            rows.append(row)
            continue

        donors["priority_rank"] = (donors["region"] != r["region"]).astype(int)  # same region first
        donors["distance_km"] = donors.apply(
            lambda d: distance_km(r["store_lat"], r["store_lon"], d["store_lat"], d["store_lon"]),
            axis=1
        )
        # Prefer donors with more remaining shelf life after distance/region
        donors = donors.sort_values(
            ["priority_rank", "distance_km", "donor_days_until_expiry", "surplus_qty"],
            ascending=[True, True, False, False]
        )

        same_region = donors[donors["priority_rank"] == 0].head(3)
        cross_region = donors[donors["priority_rank"] == 1].head(3)

        best = donors.iloc[0]
        transfer_qty = int(min(r["unmet_demand"], max(best["surplus_qty"], 0)))

        def donor_label(d):
            tier = "same-region" if d["priority_rank"] == 0 else "cross-region"
            return f"{d['store_id']} ({tier}, {d['distance_km']:.0f} km, expiry {d['donor_days_until_expiry']:.1f}d, surplus {int(d['surplus_qty'])})"

        same_region_text = "; ".join(donor_label(d) for _, d in same_region.iterrows()) if not same_region.empty else "No same-region donor"
        cross_region_text = "; ".join(donor_label(d) for _, d in cross_region.iterrows()) if not cross_region.empty else "No cross-region donor"
        best_route = donor_label(best)

        row = r.to_dict()
        row["recommended_transfer_qty"] = transfer_qty
        row["same_region_options"] = same_region_text
        row["cross_region_options"] = cross_region_text
        row["best_route"] = best_route
        rows.append(row)

    transfer_df = pd.DataFrame(rows)
    return store_summary, transfer_df



def train_decision_tree(df: pd.DataFrame):
    features = ["daily_demand", "initial_quantity", "days_until_expiry", "temp_deviation", "discount_pct"]
    X = df[features]
    y = df["high_waste_flag"]
    model = DecisionTreeClassifier(max_depth=4, random_state=42)
    model.fit(X, y)
    return model, features


def manager_summary(df: pd.DataFrame):
    st.subheader("Executive Summary")
    st.markdown(generate_summary(df))
    st.markdown("### Slide-ready insights")
    for ins in generate_slide_insights(df):
        st.success(ins)


def manager_diagnose(df: pd.DataFrame):
    st.subheader("Diagnose")
    st.markdown("Use the custom thresholds below to define what counts as **high waste** and **high profit** for the current filtered data.")
    w1, w2 = st.columns(2)
    with w1:
        waste_threshold = st.number_input(
            "High waste threshold (waste_pct)",
            min_value=0.0,
            value=float(df["waste_pct"].median()),
            step=0.01,
            format="%.3f",
            help="Rows with waste_pct above this value are classified as High Waste.",
        )
    with w2:
        profit_threshold = st.number_input(
            "High profit threshold (profit)",
            value=float(df["profit"].median()),
            step=1.0,
            format="%.2f",
            help="Rows with profit above this value are classified as High Profit.",
        )

    diag = df.copy()
    diag["waste_high_custom"] = (diag["waste_pct"] > waste_threshold).astype(int)
    diag["profit_high_custom"] = (diag["profit"] > profit_threshold).astype(int)

    st.info(
        f"Current rule: High Waste = waste_pct > {waste_threshold:.3f}; High Profit = profit > {profit_threshold:.2f}. "
        f"Promotion effectiveness remains defined as promoted items whose sell-through is above the filtered median."
    )

    c1, c2, c3 = st.columns(3)
    c1.metric("High waste share", f"{diag['waste_high_custom'].mean():.1%}")
    c2.metric("High profit share", f"{diag['profit_high_custom'].mean():.1%}")
    c3.metric("Effective promo share", f"{diag['promo_effective'].mean():.1%}")

    tree_df = diag.copy()
    tree_df["high_waste_flag"] = tree_df["waste_high_custom"]
    model, features = train_decision_tree(tree_df)
    fig, ax = plt.subplots(figsize=(12, 6))
    plot_tree(model, feature_names=features, class_names=["Low Waste", "High Waste"], filled=True, ax=ax)
    st.pyplot(fig)
    plt.close(fig)

    importance_df = pd.DataFrame({"feature": features, "importance": model.feature_importances_}).sort_values("importance", ascending=False)
    fig2 = px.bar(importance_df, x="importance", y="feature", orientation="h", title="Decision Tree Split Importance")
    st.plotly_chart(fig2, use_container_width=True)

    st.markdown("### Classification views")
    c4, c5 = st.columns(2)
    with c4:
        waste_by_region = diag.groupby("region")[["waste_high_custom", "profit_high_custom"]].mean().reset_index()
        melt = waste_by_region.melt(id_vars="region", var_name="label", value_name="rate")
        fig3 = px.bar(melt, x="region", y="rate", color="label", barmode="group", title="High Waste vs High Profit by Region")
        st.plotly_chart(fig3, use_container_width=True)
    with c5:
        promo_by_cat = diag.groupby("category")["promo_effective"].mean().sort_values(ascending=False).reset_index()
        fig4 = px.bar(promo_by_cat, x="promo_effective", y="category", orientation="h", title="Promotion Effectiveness by Category")
        st.plotly_chart(fig4, use_container_width=True)


def manager_inventory(df: pd.DataFrame, full_df: pd.DataFrame):
    st.subheader("Inventory & Replenishment")
    store_summary, transfer_df = build_transfer_suggestions(df, full_df)

    overstock = df.copy()
    overstock["recommended_order_qty"] = (
        1.2 * overstock["daily_demand"] * (1 + overstock["demand_variability"])
        - overstock["leftover_units"]
    )
    overstock.loc[overstock["shelf_life_days"] <= 7, "recommended_order_qty"] *= 0.7
    overstock.loc[overstock["spoilage_risk"] >= overstock["spoilage_risk"].quantile(0.75), "recommended_order_qty"] *= 0.8
    overstock["recommended_order_qty"] = overstock["recommended_order_qty"].clip(lower=0).round()

    c1, c2 = st.columns([1.3, 1])
    with c1:
        category_summary = overstock.groupby("category")[["initial_quantity", "recommended_order_qty", "waste_pct", "profit"]].mean().reset_index()
        category_summary["order_reduction_pct"] = 1 - category_summary["recommended_order_qty"] / category_summary["initial_quantity"]
        fig = px.bar(
            category_summary.sort_values("order_reduction_pct", ascending=False),
            x="order_reduction_pct",
            y="category",
            orientation="h",
            title="Recommended Order Reduction by Category",
        )
        st.plotly_chart(fig, use_container_width=True)
    with c2:
        st.markdown("**Action shortlist**")
        shortlist = overstock.sort_values(["waste_pct", "stock_demand_ratio"], ascending=[False, False])[[
            "store_id", "product_name", "category", "initial_quantity", "daily_demand",
            "days_until_expiry", "waste_pct", "recommended_order_qty"
        ]].head(20)
        shortlist = with_product_elements(shortlist)
        st.dataframe(
            shortlist[["store_id", "product_item", "category_tag", "initial_quantity", "daily_demand", "days_until_expiry", "waste_pct", "recommended_order_qty"]],
            use_container_width=True,
            hide_index=True,
        )

    st.markdown("### What-if Simulator")
    col1, col2, col3 = st.columns(3)
    selected_category = col1.selectbox("Category for simulation", sorted(df["category"].unique()))
    order_cut = col2.slider("Reduce order quantity by %", 0, 40, 10)
    markdown_shift = col3.slider("Advance markdown trigger by days", 0, 5, 2)

    sim = df[df["category"] == selected_category].copy()
    current_waste = sim["waste_pct"].mean()
    current_profit = sim["profit"].mean()

    waste_reduction = 0.35 * (order_cut / 100) + 0.015 * markdown_shift
    sim_waste = max(current_waste * (1 - waste_reduction), 0)
    sim_profit = current_profit * (1 + 0.08 * (order_cut / 100) + 0.03 * markdown_shift)

    s1, s2, s3 = st.columns(3)
    s1.metric("Current waste", f"{current_waste:.1%}")
    s2.metric("Simulated waste", f"{sim_waste:.1%}", delta=f"-{(current_waste-sim_waste):.1%}")
    s3.metric("Simulated avg profit", f"€{sim_profit:.2f}", delta=f"€{(sim_profit-current_profit):.2f}")

    st.markdown("### Store inventory balance")
    st.dataframe(store_summary.sort_values(["unmet_demand", "remaining_inventory"], ascending=[False, False]), use_container_width=True, hide_index=True)

    st.markdown("### Transfer suggestions for stores where demand exceeds available inventory")
    st.caption("Routing logic: same-region donors are prioritized first, cross-region donors are used as second-best options, and donors are ranked by transport distance then remaining shelf life.")
    if transfer_df.empty:
        st.success("No filtered store currently shows unmet demand that needs transfer support.")
    else:
        show_cols = [
            "store_id", "region", "category", "remaining_inventory", "demand", "unmet_demand",
            "recommended_transfer_qty", "best_route", "same_region_options", "cross_region_options"
        ]
        transfer_show = transfer_df.sort_values(["unmet_demand", "recommended_transfer_qty"], ascending=[False, False])[show_cols].copy()
        transfer_show = with_product_elements(transfer_show, product_col="category", category_col="category")
        st.dataframe(
            transfer_show,
            use_container_width=True,
            hide_index=True,
        )


def manager_promotions(df: pd.DataFrame):
    st.subheader("Promotion Designer")
    left, right = st.columns([1, 1.2])
    with left:
        promo_category = st.selectbox("Promotion category", sorted(df["category"].unique()), key="promo_cat")
        expiry_target = st.selectbox("Target expiry bucket", ["<=1d", "2-3d", "4-7d", "8-30d", ">30d"])
        discount = st.slider("Discount %", 0, 50, 18)
        bundle = st.checkbox("Bundle with complementary items", value=True)
        weekend_only = st.checkbox("Weekend campaign only", value=False)

        sub = df[(df["category"] == promo_category) & (df["expiry_bucket"].astype(str) == expiry_target)].copy()
        if weekend_only:
            sub = sub[sub["is_weekend"] == 1]

        demand_lift = 0.08 + discount / 200
        if bundle:
            demand_lift += 0.06

        est_sales_uplift = sub["units_sold"].mean() * demand_lift if len(sub) else 0
        est_waste_drop = sub["waste_pct"].mean() * min(0.35, demand_lift) if len(sub) else 0
        est_profit = sub["profit"].mean() * (1 + demand_lift - discount / 150) if len(sub) else 0

        st.metric("Estimated sales uplift", f"{est_sales_uplift:.2f} units")
        st.metric("Estimated waste reduction", f"{est_waste_drop:.1%}")
        st.metric("Estimated avg profit", f"€{est_profit:.2f}")

    with right:
        promo_base = df.groupby(["expiry_bucket"])[["discount_pct", "waste_pct", "profit"]].mean().reset_index()
        fig = px.bar(promo_base, x="expiry_bucket", y=["discount_pct", "waste_pct"], barmode="group", title="Current Discount vs Waste by Expiry")
        st.plotly_chart(fig, use_container_width=True)

        st.markdown("**Recommended promotion copy**")
        st.info(
            f"Run a {discount}% {promo_category} campaign for {expiry_target} items"
            + (" on weekends" if weekend_only else "")
            + (" with bundle offers" if bundle else " as single-item markdown")
            + ". Position the offer at high-traffic display zones and highlight value + freshness."
        )


def manager_risk(df: pd.DataFrame):
    st.subheader("Risk & Store Operations")
    _, importances = fit_risk_model(df)
    c1, c2 = st.columns([1.1, 1])
    with c1:
        fig = px.bar(importances.head(10).sort_values(), orientation="h", title="Top Drivers of High Waste Risk")
        st.plotly_chart(fig, use_container_width=True)
    with c2:
        heat = df.groupby(["region", "category"])["temp_deviation"].mean().reset_index()
        fig = px.density_heatmap(heat, x="category", y="region", z="temp_deviation", title="Temperature Deviation Heatmap")
        st.plotly_chart(fig, use_container_width=True)

    alerts = (
        df.groupby("store_id")[["temp_deviation", "temp_abuse_events", "waste_pct", "profit"]]
        .mean()
        .assign(alert_score=lambda x: 0.35 * x["temp_deviation"] + 0.25 * x["temp_abuse_events"] + 0.4 * x["waste_pct"] * 10)
        .sort_values("alert_score", ascending=False)
        .head(15)
        .reset_index()
    )
    st.markdown("### Automated store alerts")
    st.dataframe(alerts, use_container_width=True, hide_index=True)


def consumer_deals(df: pd.DataFrame):
    st.subheader("Consumer Mode")
    c1, c2, c3 = st.columns(3)
    max_budget = c1.slider("Budget (€)", 5, 60, 20)
    preferred_category = c2.selectbox("Preferred category", ["All"] + sorted(df["category"].unique()))
    max_expiry = c3.slider("Maximum days until expiry", 1, 14, 5)

    deals = df[df["days_until_expiry"] <= max_expiry].copy()
    if preferred_category != "All":
        deals = deals[deals["category"] == preferred_category]
    deals = deals.assign(
        savings=lambda x: x["base_price"] - x["selling_price"],
        deal_score=lambda x: x["discount_pct"] * 0.5 + x["value_score"] * 0.35 + (x["profit_margin_pct"].clip(lower=0) / 100) * 0.15,
    ).sort_values(["deal_score", "savings"], ascending=False)

    display = deals[[
        "product_name", "category", "store_id", "days_until_expiry",
        "base_price", "selling_price", "discount_pct", "savings"
    ]].head(25)
    st.dataframe(display, use_container_width=True, hide_index=True)

    fig = px.scatter(
        deals.head(500), x="selling_price", y="discount_pct", color="category",
        hover_data=["product_name", "store_id", "days_until_expiry"],
        title="Discounted Items Map"
    )
    st.plotly_chart(fig, use_container_width=True)

    affordable = deals[deals["selling_price"] <= max_budget].head(10)
    if not affordable.empty:
        st.markdown("### Best picks for your budget")
        for _, row in affordable.iterrows():
            st.success(
                f"Now €{row['selling_price']:.2f} (save €{row['base_price'] - row['selling_price']:.2f}) · expires in {int(row['days_until_expiry'])} day(s)"
            )
            st.markdown(
                f"""
                🛒 **{row['product_name']}**  
                📦 Category: {row['category']}  
                🏪 Store: {row['store_id']}  
                💸 Discount: {row['discount_pct']*100:.0f}%  
                ⏳ Expiry: {row['days_until_expiry']} days
                """
            )


def build_bundle(df: pd.DataFrame, budget: float, people: int, theme: str):
    work = df.copy()
    work = work[work["days_until_expiry"] <= 7].copy()
    work["score"] = work["value_score"] + work["discount_pct"]

    theme_map = {
        "Quick dinner": ["Ready_to_Eat", "Produce", "Bakery", "Dairy"],
        "Healthy protein": ["Meat", "Seafood", "Dairy", "Produce"],
        "Family breakfast": ["Bakery", "Dairy", "Beverages", "Produce"],
        "Budget saver": list(work["category"].unique()),
    }
    cats = theme_map.get(theme, list(work["category"].unique()))
    work = work[work["category"].isin(cats)].sort_values(["score", "selling_price"], ascending=[False, True])

    chosen = []
    remaining = budget
    target_items = min(max(people + 1, 3), 6)
    used_categories = set()

    for _, row in work.iterrows():
        if row["selling_price"] <= remaining:
            if theme != "Budget saver" and row["category"] in used_categories:
                continue
            chosen.append(row)
            remaining -= row["selling_price"]
            used_categories.add(row["category"])
            if len(chosen) >= target_items:
                break

    if not chosen:
        return pd.DataFrame(), 0.0, 0.0
    bundle = pd.DataFrame(chosen)
    total = bundle["selling_price"].sum()
    saved = (bundle["base_price"] - bundle["selling_price"]).sum()
    return bundle, total, saved


def consumer_bundles(df: pd.DataFrame):
    st.subheader("Bundle Builder")
    c1, c2, c3 = st.columns(3)
    budget = c1.slider("Bundle budget (€)", 8, 80, 25)
    people = c2.slider("People", 1, 6, 2)
    theme = c3.selectbox("Bundle theme", ["Quick dinner", "Healthy protein", "Family breakfast", "Budget saver"])

    bundle, total, saved = build_bundle(df, budget, people, theme)
    if bundle.empty:
        st.warning("No bundle found for the current filters.")
        return

    k1, k2, k3 = st.columns(3)
    k1.metric("Bundle total", f"€{total:.2f}")
    k2.metric("You save", f"€{saved:.2f}")
    k3.metric("Items", f"{len(bundle)}")

    st.dataframe(bundle[[
        "product_name", "category", "store_id", "selling_price", "base_price", "discount_pct", "days_until_expiry"
    ]], use_container_width=True, hide_index=True)

    st.info(
        "Suggested marketing use: turn these bundles into one-click promotions for end customers or pre-designed campaign packs for store managers."
    )


def consumer_personal(df: pd.DataFrame):
    st.subheader("Personalized Promotions")
    favorite = st.selectbox("Favorite category", sorted(df["category"].unique()))
    price_cap = st.slider("Max item price (€)", 1, 30, 10)
    not_too_close = st.checkbox("Hide items expiring within 1 day", value=False)

    recs = df[df["category"] == favorite].copy()
    recs = recs[recs["selling_price"] <= price_cap]
    if not_too_close:
        recs = recs[recs["days_until_expiry"] > 1]
    recs = recs.assign(score=lambda x: x["discount_pct"] * 0.55 + x["value_score"] * 0.45).sort_values("score", ascending=False).head(12)

    cols = st.columns(3)
    for i, (_, row) in enumerate(recs.iterrows()):
        with cols[i % 3]:
            st.markdown(f"### {row['product_name']}")
            st.write(f"{row['category']} · {row['store_id']}")
            st.write(f"Now **€{row['selling_price']:.2f}** | Save **€{(row['base_price'] - row['selling_price']):.2f}**")
            st.write(f"Expires in {int(row['days_until_expiry'])} day(s)")
            st.button("Add to shortlist", key=f"short_{i}")


def main():
    inject_css()
    st.markdown(
        """
        <div class="main-title">
            <div class="logo-badge">🐱🐟</div>
            <div>
                <div class="brand-title">freshie</div>
                <div class="brand-sub">Perishable retail optimization for managers and consumers</div>
            </div>
        </div>
        """,
        unsafe_allow_html=True,
    )

    try:
        df = load_data()
    except Exception as e:
        st.error(str(e))
        st.stop()

    filtered = apply_filters(df)
    if filtered.empty:
        st.warning("No data left after filtering.")
        st.stop()

    role = st.radio("Choose your mode", ["Manager", "Consumer"], horizontal=True)

    if role == "Manager":
        tabs = st.tabs([
            "Overview",
            "Executive Summary",
            "Category Intelligence",
            "Inventory & Replenishment",
            "Promotion Designer",
            "Diagnose",
        ])
        with tabs[0]:
            manager_dashboard(filtered)
        with tabs[1]:
            manager_summary(filtered)
        with tabs[2]:
            manager_category_intelligence(filtered)
        with tabs[3]:
            manager_inventory(filtered, df)
        with tabs[4]:
            manager_promotions(filtered)
        with tabs[5]:
            manager_diagnose(filtered)
    else:
        tabs = st.tabs([
            "Deal Finder",
            "Bundle Builder",
            "Personalized Promotions",
        ])
        with tabs[0]:
            consumer_deals(filtered)
        with tabs[1]:
            consumer_bundles(filtered)
        with tabs[2]:
            consumer_personal(filtered)

    with st.expander("About this app"):
        st.markdown(
            """
            - **Manager mode** turns data into inventory, markdown, and operational decisions.
            - **Consumer mode** surfaces discounted products, smart bundles, and personalized promotions.
            - Built for deployment on Hugging Face Docker Spaces with Streamlit.
            """
        )


if __name__ == "__main__":
    main()