Py.Cafe

"""
KPI Superstore Dashboard - Dash app replicating the screen in KPI.png.
Data from data/orders.csv (pandas only). Generate with: python export_orders_from_hyper.py
"""

from pathlib import Path
import pandas as pd
from dash import Dash, html, dcc, Input, Output, callback
import plotly.graph_objects as go

# ---------------------------------------------------------------------------
# Data loading (once at startup) - pandas only
# ---------------------------------------------------------------------------
ORDERS_CSV = Path("orders.csv")

def load_orders_df():
    if not ORDERS_CSV.exists():
        raise FileNotFoundError(
            f"Data not found: {ORDERS_CSV}. Run export_orders_from_hyper.py once to create it."
        )
    df = pd.read_csv(ORDERS_CSV, parse_dates=["Order Date"])
    return df.dropna(subset=["Order Date"])

DF = load_orders_df()

# ---------------------------------------------------------------------------
# Date period helpers
# ---------------------------------------------------------------------------
def get_period_bounds(date_view, ref_date):
    """Return (current_start, current_end), (prior_start, prior_end) as pandas Timestamps."""
    ref = pd.Timestamp(ref_date)
    if date_view == "YTD":
        cur_start = ref.replace(month=1, day=1)
        cur_end = ref
        prior_end = cur_start - pd.Timedelta(days=1)
        prior_start = prior_end.replace(month=1, day=1)
    elif date_view == "QTD":
        q = (ref.month - 1) // 3 + 1
        cur_start = ref.replace(month=1 + (q - 1) * 3, day=1)
        cur_end = ref
        prior_end = cur_start - pd.Timedelta(days=1)
        prior_start = prior_end.replace(month=1 + (q - 1) * 3, day=1)
    else:  # MTD
        cur_start = ref.replace(day=1)
        cur_end = ref
        prior_end = cur_start - pd.Timedelta(days=1)
        prior_start = prior_end.replace(day=1)
    return (cur_start, cur_end), (prior_start, prior_end)

def filter_df(df, region, category, sub_category):
    out = df
    if region and region != "All":
        out = out[out["Region"] == region]
    if category and category != "All":
        out = out[out["Category"] == category]
    if sub_category and sub_category != "All":
        out = out[out["Sub-Category"] == sub_category]
    return out

def compute_kpis(df, date_view):
    if df.empty:
        return None
    ref_date = df["Order Date"].max()
    (cur_start, cur_end), (prior_start, prior_end) = get_period_bounds(date_view, ref_date)
    cur = df[(df["Order Date"] >= cur_start) & (df["Order Date"] <= cur_end)]
    prior = df[(df["Order Date"] >= prior_start) & (df["Order Date"] <= prior_end)]
    if cur.empty:
        return None
    sales_cur = cur["Sales"].sum()
    sales_prior = prior["Sales"].sum()
    orders_cur = cur["Order ID"].nunique() if "Order ID" in cur.columns else len(cur)
    orders_prior = prior["Order ID"].nunique() if "Order ID" in prior.columns else len(prior)
    profit_cur = cur["Profit"].sum()
    profit_prior = prior["Profit"].sum()
    qty_cur = cur["Quantity"].sum()
    qty_prior = prior["Quantity"].sum()

    def pct(now, prev):
        if prev == 0:
            return 0.0
        return (now - prev) / prev * 100

    return {
        "ref_date": ref_date,
        "sales_cur": sales_cur, "sales_prior": sales_prior, "sales_pct": pct(sales_cur, sales_prior),
        "orders_cur": orders_cur, "orders_prior": orders_prior, "orders_pct": pct(orders_cur, orders_prior),
        "profit_cur": profit_cur, "profit_prior": profit_prior, "profit_pct": pct(profit_cur, profit_prior),
        "qty_cur": qty_cur, "qty_prior": qty_prior, "qty_pct": pct(qty_cur, qty_prior),
        "cur_start": cur_start, "cur_end": cur_end,
        "cur_df": cur, "prior_df": prior,
    }

def make_mini_chart(kpi_name, cur_series, prior_series, cur_mean, fmt=",.0f"):
    """cur_series, prior_series: index = period (e.g. month), value = aggregate."""
    fig = go.Figure()
    x = list(range(len(prior_series)))
    fig.add_trace(go.Bar(x=x, y=prior_series.values, name="Previous Year", marker_color="lightgray", width=0.4))
    fig.add_trace(go.Bar(x=x, y=cur_series.values, name="Current YTD", marker_color="darkgray", width=0.5))
    fig.add_trace(go.Scatter(x=x, y=cur_series.values, mode="lines", name="Current YTD line", line=dict(color="black", width=2)))
    if cur_mean is not None and not pd.isna(cur_mean):
        fig.add_hline(y=cur_mean, line_dash="dash", line_color="gray")
    fig.update_layout(
        margin=dict(l=30, r=30, t=20, b=30),
        showlegend=False,
        xaxis=dict(tickvals=[0, 10, 20, 30, 40, 50] if len(x) > 50 else list(range(0, len(x), max(1, len(x) // 6))), title=None),
        yaxis=dict(title=None),
        paper_bgcolor="rgba(0,0,0,0)",
        plot_bgcolor="rgba(0,0,0,0)",
        height=180,
    )
    fig.update_xaxes(showgrid=False)
    fig.update_yaxes(showgrid=True, gridcolor="lightgray")
    return fig

def build_chart_series(cur_df, prior_df, date_view, value_col, agg="sum"):
    """Group by period (month for YTD/QTD, day for MTD) and aggregate."""
    if cur_df.empty:
        return pd.Series(dtype=float), pd.Series(dtype=float), None
    if date_view == "MTD":
        cur_df = cur_df.copy()
        cur_df["_period"] = cur_df["Order Date"].dt.day
        prior_df = prior_df.copy()
        prior_df["_period"] = prior_df["Order Date"].dt.day
    else:
        cur_df = cur_df.copy()
        cur_df["_period"] = cur_df["Order Date"].dt.to_period("M").astype(str)
        prior_df = prior_df.copy()
        prior_df["_period"] = prior_df["Order Date"].dt.to_period("M").astype(str)
    if agg == "sum":
        cur_s = cur_df.groupby("_period", sort=True)[value_col].sum()
        prior_s = prior_df.groupby("_period", sort=True)[value_col].sum()
    else:
        cur_s = cur_df.groupby("_period", sort=True)[value_col].nunique()
        prior_s = prior_df.groupby("_period", sort=True)[value_col].nunique()
    # Align to same index (union of periods)
    all_periods = sorted(set(cur_s.index) | set(prior_s.index))
    cur_s = cur_s.reindex(all_periods, fill_value=0)
    prior_s = prior_s.reindex(all_periods, fill_value=0)
    cur_mean = cur_s.mean() if len(cur_s) else None
    return cur_s, prior_s, cur_mean

# ---------------------------------------------------------------------------
# App and layout
# ---------------------------------------------------------------------------
app = Dash(__name__)
app.title = "KPI Superstore Dashboard"

# Dropdown options from data
REGIONS = ["All"] + sorted(DF["Region"].dropna().unique().tolist())
CATEGORIES = ["All"] + sorted(DF["Category"].dropna().unique().tolist())
SUB_CATEGORIES = ["All"] + sorted(DF["Sub-Category"].dropna().unique().tolist())
LAST_UPDATED = DF["Order Date"].max().strftime("%d/%m/%Y") if not DF.empty else ""

app.layout = html.Div([
    html.Div([
        html.H1("KPI SUPERSTORE DASHBOARD", style={"margin": 0, "fontSize": "1.5rem", "fontWeight": "bold", "color": "#333"}),
        html.Div([
            html.Span(style={"display": "inline-block", "width": 8, "height": 8, "borderRadius": "50%", "backgroundColor": "#2ecc71", "marginRight": 6, "verticalAlign": "middle"}),
            html.Span(f"Last Updated: {LAST_UPDATED}", style={"fontSize": "0.85rem", "color": "#888"}),
        ], style={"display": "flex", "alignItems": "center"}),
    ], style={"display": "flex", "justifyContent": "space-between", "alignItems": "center", "padding": "12px 20px", "borderBottom": "1px solid #eee", "backgroundColor": "#fafafa"}),

    html.Div([
        html.Div([
            html.Div([
                html.Div([html.Span("SALES", style={"fontWeight": "bold", "color": "#333"}), html.Hr(style={"margin": "4px 0", "border": "none", "borderTop": "3px solid #c0392b", "width": 40})]),
                html.Div(id="sales-value", style={"fontSize": "1.8rem", "fontWeight": "bold"}),
                html.Div(id="sales-subtitle", style={"fontSize": "0.75rem", "color": "#888"}),
                html.Div(id="sales-pct", style={"marginTop": 4}),
                dcc.Graph(id="sales-chart", config={"displayModeBar": False}, style={"height": 180}),
            ], className="kpi-card", style={"padding": 16, "backgroundColor": "white", "borderRadius": 8, "boxShadow": "0 1px 3px rgba(0,0,0,0.1)"}),
            html.Div([
                html.Div([html.Span("ORDERS", style={"fontWeight": "bold", "color": "#333"}), html.Hr(style={"margin": "4px 0", "border": "none", "borderTop": "3px solid #16a085", "width": 40})]),
                html.Div(id="orders-value", style={"fontSize": "1.8rem", "fontWeight": "bold"}),
                html.Div(id="orders-subtitle", style={"fontSize": "0.75rem", "color": "#888"}),
                html.Div(id="orders-pct", style={"marginTop": 4}),
                dcc.Graph(id="orders-chart", config={"displayModeBar": False}, style={"height": 180}),
            ], className="kpi-card", style={"padding": 16, "backgroundColor": "white", "borderRadius": 8, "boxShadow": "0 1px 3px rgba(0,0,0,0.1)"}),
            html.Div([
                html.Div([html.Span("PROFIT", style={"fontWeight": "bold", "color": "#333"}), html.Hr(style={"margin": "4px 0", "border": "none", "borderTop": "3px solid #c0392b", "width": 40})]),
                html.Div(id="profit-value", style={"fontSize": "1.8rem", "fontWeight": "bold"}),
                html.Div(id="profit-subtitle", style={"fontSize": "0.75rem", "color": "#888"}),
                html.Div(id="profit-pct", style={"marginTop": 4}),
                dcc.Graph(id="profit-chart", config={"displayModeBar": False}, style={"height": 180}),
            ], className="kpi-card", style={"padding": 16, "backgroundColor": "white", "borderRadius": 8, "boxShadow": "0 1px 3px rgba(0,0,0,0.1)"}),
            html.Div([
                html.Div([html.Span("QUANTITY", style={"fontWeight": "bold", "color": "#333"}), html.Hr(style={"margin": "4px 0", "border": "none", "borderTop": "3px solid #16a085", "width": 40})]),
                html.Div(id="quantity-value", style={"fontSize": "1.8rem", "fontWeight": "bold"}),
                html.Div(id="quantity-subtitle", style={"fontSize": "0.75rem", "color": "#888"}),
                html.Div(id="quantity-pct", style={"marginTop": 4}),
                dcc.Graph(id="quantity-chart", config={"displayModeBar": False}, style={"height": 180}),
            ], className="kpi-card", style={"padding": 16, "backgroundColor": "white", "borderRadius": 8, "boxShadow": "0 1px 3px rgba(0,0,0,0.1)"}),
        ], style={"display": "grid", "gridTemplateColumns": "1fr 1fr", "gap": 16, "flex": 1}),

        html.Div([
            html.H3("Filters", style={"fontSize": "1rem", "marginBottom": 12, "color": "#333"}),
            html.Label("Select Date View", style={"fontSize": "0.8rem", "color": "#666"}),
            dcc.RadioItems(id="date-view", options=[{"label": "YTD", "value": "YTD"}, {"label": "QTD", "value": "QTD"}, {"label": "MTD", "value": "MTD"}], value="YTD", inline=True, style={"marginBottom": 16}),
            html.Label("Select Region", style={"fontSize": "0.8rem", "color": "#666"}),
            dcc.Dropdown(id="region", options=[{"label": x, "value": x} for x in REGIONS], value="All", clearable=False, style={"marginBottom": 12}),
            html.Label("Select Category", style={"fontSize": "0.8rem", "color": "#666"}),
            dcc.Dropdown(id="category", options=[{"label": x, "value": x} for x in CATEGORIES], value="All", clearable=False, style={"marginBottom": 12}),
            html.Label("Select Sub-Category", style={"fontSize": "0.8rem", "color": "#666"}),
            dcc.Dropdown(id="sub-category", options=[{"label": x, "value": x} for x in SUB_CATEGORIES], value="All", clearable=False, style={"marginBottom": 20}),
            html.H3("Legend", style={"fontSize": "1rem", "marginBottom": 8, "color": "#333"}),
            html.Div([
                html.Div("Current YTD: dark bars + line", style={"fontSize": "0.8rem", "color": "#555"}),
                html.Div("Previous Year: light grey bars", style={"fontSize": "0.8rem", "color": "#555"}),
            ], style={"marginBottom": 16}),
            html.Div("Designed by: Fuad Ahmed | @Fuad9510", style={"fontSize": "0.75rem", "color": "#999"}),
        ], style={"width": 280, "padding": 20, "backgroundColor": "#f9f9f9", "borderRadius": 8, "borderLeft": "1px solid #eee"}),
    ], style={"display": "flex", "gap": 24, "padding": 20, "minHeight": "calc(100vh - 60px)", "backgroundColor": "#f0f0f0"}),
], style={"fontFamily": "sans-serif"})

# ---------------------------------------------------------------------------
# Callbacks
# ---------------------------------------------------------------------------
def fmt_currency(x):
    if abs(x) >= 1e6:
        return f"${x/1e6:.1f}M"
    if abs(x) >= 1e3:
        return f"${x/1e3:.0f}K"
    return f"${x:,.0f}"

def pct_badge(pct, is_red_metric):
    if pct >= 0:
        color = "#16a085"
    else:
        color = "#c0392b"
    return html.Span(f"{pct:+.0f}% vs prior year", style={"backgroundColor": color, "color": "white", "padding": "2px 8px", "borderRadius": 4, "fontSize": "0.8rem"})

@callback(
    [Output("sales-value", "children"), Output("sales-subtitle", "children"), Output("sales-pct", "children"), Output("sales-chart", "figure"),
     Output("orders-value", "children"), Output("orders-subtitle", "children"), Output("orders-pct", "children"), Output("orders-chart", "figure"),
     Output("profit-value", "children"), Output("profit-subtitle", "children"), Output("profit-pct", "children"), Output("profit-chart", "figure"),
     Output("quantity-value", "children"), Output("quantity-subtitle", "children"), Output("quantity-pct", "children"), Output("quantity-chart", "figure")],
    [Input("date-view", "value"), Input("region", "value"), Input("category", "value"), Input("sub-category", "value")],
)
def update_kpis(date_view, region, category, sub_category):
    period_label = {"YTD": "current YTD", "QTD": "current QTD", "MTD": "current MTD"}.get(date_view, "current YTD")
    filtered = filter_df(DF, region, category, sub_category)
    k = compute_kpis(filtered, date_view)
    if k is None:
        empty_fig = go.Figure().update_layout(margin=dict(l=30, r=30, t=20, b=30), height=180, paper_bgcolor="rgba(0,0,0,0)", plot_bgcolor="rgba(0,0,0,0)")
        empty = ("—", period_label, html.Span("—"), empty_fig)
        return empty * 4

    cur_sales, prior_sales, _ = build_chart_series(k["cur_df"], k["prior_df"], date_view, "Sales")
    cur_orders, prior_orders, _ = build_chart_series(k["cur_df"], k["prior_df"], date_view, "Order ID", agg="nunique")
    cur_profit, prior_profit, _ = build_chart_series(k["cur_df"], k["prior_df"], date_view, "Profit")
    cur_qty, prior_qty, _ = build_chart_series(k["cur_df"], k["prior_df"], date_view, "Quantity")

    sales_fig = make_mini_chart("Sales", cur_sales, prior_sales, cur_sales.mean() if len(cur_sales) else None)
    orders_fig = make_mini_chart("Orders", cur_orders, prior_orders, cur_orders.mean() if len(cur_orders) else None)
    profit_fig = make_mini_chart("Profit", cur_profit, prior_profit, cur_profit.mean() if len(cur_profit) else None)
    quantity_fig = make_mini_chart("Quantity", cur_qty, prior_qty, cur_qty.mean() if len(cur_qty) else None)

    return (
        fmt_currency(k["sales_cur"]), period_label, pct_badge(k["sales_pct"], True), sales_fig,
        f"{k['orders_cur']:,.0f}", period_label, pct_badge(k["orders_pct"], False), orders_fig,
        fmt_currency(k["profit_cur"]), period_label, pct_badge(k["profit_pct"], True), profit_fig,
        f"{k['qty_cur']:,.0f}", period_label, pct_badge(k["qty_pct"], False), quantity_fig,
    )

if __name__ == "__main__":
    app.run(debug=True, port=8050)