"""S&OP monthly review service.

Builds the single payload that the S&OP Meeting page (monthly view) renders:
  1. Plan snapshot for the target month (from monthly_plan_snapshots).
  2. Last-month review — actual vs plan, top offenders + top contributors.
  3. Monthly FA — backtest aggregated to the month, per-SKU reason tagging.
  4. Lost sales — reuses Finance lost-sales report, scoped to the month.
  5. Current-month progress — actual MTD + remaining-week forecast = projection.

The "month" in the request is what the meeting is reviewing. Today (CW22 of
May 2026) the natural read is:
  - month=5 → "the month we just had" (still partial — projection lights up)
  - month=4 → fully closed prior month (projection section is suppressed)
  - month=6 → forward plan only (no actuals)
"""
from __future__ import annotations

from datetime import date, timedelta
from typing import Optional

import pandas as pd
from sqlalchemy import text
from sqlalchemy.orm import Session

from backend.services import finance_service as fin
from backend.services.time_utils import (
    days_in_calendar_month,
    iso_week_first_monday,
)


def _today_iso() -> tuple[int, int]:
    iso = date.today().isocalendar()
    return int(iso[0]), int(iso[1])


def _month_iso_weeks(year: int, month: int) -> list[tuple[int, int, float]]:
    """All (iso_year, iso_week, day_fraction) tuples that overlap with the
    given calendar month — same shape monthly_plan_service uses."""
    weeks: dict[tuple[int, int], int] = {}
    last_day = days_in_calendar_month(year, month)
    d = date(year, month, 1)
    for off in range(last_day):
        cur = d + timedelta(days=off)
        iso = cur.isocalendar()
        weeks[(int(iso[0]), int(iso[1]))] = weeks.get((int(iso[0]), int(iso[1])), 0) + 1
    return [(y, w, n / 7.0) for (y, w), n in sorted(weeks.items())]


def _classify_fa_reason(
    *, on_top_total: float, forecast: float, actual: float,
    had_stockout: bool,
) -> str:
    """One-of: KAM_over, KAM_under, Stockout, Model_drift, In_tolerance."""
    if actual <= 0:
        return "In_tolerance"
    abs_err_pct = abs(forecast - actual) / actual
    if abs_err_pct <= 0.30:
        return "In_tolerance"
    if had_stockout and actual < 0.7 * forecast:
        return "Stockout"
    if on_top_total > 0:
        # KAM commitments dominated the forecast; tag direction
        if forecast > 1.3 * actual:
            return "KAM_over"
        if actual > 1.3 * forecast:
            return "KAM_under"
    return "Model_drift"


def build_sop_monthly_review(db: Session, year: int, month: int) -> dict:
    month_key = year * 100 + month
    label = f"{year}-{month:02d}"
    cur_y, cur_w = _today_iso()
    cur_yw = cur_y * 100 + cur_w

    iso_weeks = _month_iso_weeks(year, month)
    iso_yw_set = {(y, w) for (y, w, _) in iso_weeks}
    month_start = date(year, month, 1)
    month_end   = date(year, month, days_in_calendar_month(year, month))
    is_partial = month_start <= date.today() <= month_end

    # ── 1) Locked plan snapshot for the target month ──────────────────────
    plan_row = db.execute(text("""
        SELECT total_revenue_eur, total_ruc_eur, total_qty_ws, total_qty_b2c,
               total_ruc_ws, total_ruc_b2c,
               n_skus, locked_at, label, per_sku_data
        FROM monthly_plan_snapshots WHERE month_key = :mk
    """), {"mk": month_key}).mappings().first()

    plan = {
        "exists":             plan_row is not None,
        "label":              (plan_row or {}).get("label"),
        "revenue_eur":        float((plan_row or {}).get("total_revenue_eur") or 0),
        "ruc_eur":            float((plan_row or {}).get("total_ruc_eur") or 0),
        "qty_ws":             float((plan_row or {}).get("total_qty_ws") or 0),
        "qty_b2c":            float((plan_row or {}).get("total_qty_b2c") or 0),
        # Authoritative channel split — written by monthly_plan_service after
        # all calibrations (retail target etc.) are applied. Preferred over
        # deriving from per_sku_plan ratios, which lag any post-build
        # channel adjustments.
        "ruc_ws_stored":      float((plan_row or {}).get("total_ruc_ws") or 0),
        "ruc_b2c_stored":     float((plan_row or {}).get("total_ruc_b2c") or 0),
        "n_skus":             int((plan_row or {}).get("n_skus") or 0),
        "locked_at":          str((plan_row or {}).get("locked_at") or ""),
    }
    per_sku_plan: dict[str, dict] = {}
    if plan_row and plan_row.get("per_sku_data"):
        for entry in plan_row["per_sku_data"]:
            sku = entry.get("sku")
            if sku:
                per_sku_plan[sku] = entry

    # Channel + RUC breakdown — sum the per-SKU snapshot fields. The snapshot
    # was built day-fraction so totals match the headline plan numbers.
    plan_revenue_ws  = sum(float(p.get("ws_revenue_eur_df")  or 0) for p in per_sku_plan.values())
    plan_revenue_b2c = sum(float(p.get("b2c_revenue_eur_df") or 0) for p in per_sku_plan.values())
    plan_ruc_ws      = sum(float(p.get("ws_ruc_eur_df")      or 0) for p in per_sku_plan.values())
    plan_ruc_b2c     = sum(float(p.get("b2c_ruc_eur_df")     or 0) for p in per_sku_plan.values())
    plan["channel_breakdown"] = {
        "revenue_ws":  round(plan_revenue_ws, 0),
        "revenue_b2c": round(plan_revenue_b2c, 0),
        "ruc_ws":      round(plan_ruc_ws, 0),
        "ruc_b2c":     round(plan_ruc_b2c, 0),
        "qty_ws":      round(plan["qty_ws"], 0),
        "qty_b2c":     round(plan["qty_b2c"], 0),
    }

    # ── 2) Actuals for the month (sum across overlapping ISO weeks)
    #      Revenue uses NET tax_base / quantity per channel × price; RUC from
    #      erp_transactions.ruc_eur.
    iso_yw_list = [y * 100 + w for (y, w, _) in iso_weeks]
    # Per-SKU actuals — STRICT calendar month, nets returns. Matches the
    # channel breakdown above so headline + per-channel reconcile to the
    # same source.
    actuals = pd.read_sql(text("""
        SELECT p.sku, p.name,
               SUM(et.quantity)::float                AS qty,
               SUM(COALESCE(NULLIF(et.tax_base,0),
                            et.total_value * 0.80))::float AS revenue_eur,
               SUM(et.ruc_eur)::float                 AS ruc_eur
        FROM erp_transactions et
        JOIN dim_products p ON p.id = et.product_id
        WHERE et.transaction_date BETWEEN DATE :mstart AND DATE :mend
        GROUP BY p.sku, p.name
    """), db.bind, params={
        "mstart": month_start.isoformat(),
        "mend":   month_end.isoformat(),
    })
    actuals = actuals.set_index("sku")

    actual_revenue = float(actuals["revenue_eur"].sum() or 0)
    actual_ruc     = float(actuals["ruc_eur"].sum() or 0)
    actual_qty     = float(actuals["qty"].sum() or 0)

    # ── 3) Top offenders / contributors — variance per SKU vs plan in RUC
    #      Scope: Gold / Silver / Bronze planned SKUs only (per user spec —
    #      unplanned / N/A SKUs noise out the headline).
    #      gap_ruc_eur = actual_ruc − plan_ruc
    #      Positive gap = SKU helped (more RUC than planned)
    #      Negative gap = SKU hurt
    offenders_rows: list[dict] = []
    contributors_rows: list[dict] = []
    if plan["exists"] and per_sku_plan:
        # SKU → tier lookup (sku_planning is the canonical source — same one
        # the snapshot used at lock time).
        tier_map = {
            r["sku"]: (r["tier"] or "").strip()
            for r in db.execute(text("""
                SELECT p.sku, sp.tier
                FROM sku_planning sp
                JOIN dim_products p ON p.id = sp.product_id
            """)).mappings()
        }
        GSB = {"01 GOLD", "02 SILVER", "03 BRONZE"}
        for sku, p in per_sku_plan.items():
            tier = tier_map.get(sku, "")
            if tier not in GSB:
                continue   # drop N/A SKUs
            plan_qty_total = float((p.get("qty_ws_df") or 0) + (p.get("qty_b2c_df") or 0))
            plan_ruc = float((p.get("ws_ruc_eur_df") or 0) + (p.get("b2c_ruc_eur_df") or 0))
            a = actuals.loc[sku].to_dict() if sku in actuals.index else {"qty": 0, "revenue_eur": 0, "ruc_eur": 0, "name": p.get("name")}
            actual_q   = float(a.get("qty") or 0)
            actual_ruc_v = float(a.get("ruc_eur") or 0)
            gap_ruc = actual_ruc_v - plan_ruc
            row = {
                "sku":              sku,
                "name":             a.get("name"),
                "tier":             tier,
                "plan_qty":         round(plan_qty_total, 1),
                "actual_qty":       round(actual_q, 1),
                "plan_ruc_eur":     round(plan_ruc, 0),
                "actual_ruc_eur":   round(actual_ruc_v, 0),
                "gap_ruc_eur":      round(gap_ruc, 0),
            }
            if gap_ruc < 0:
                offenders_rows.append(row)
            else:
                contributors_rows.append(row)
        offenders_rows.sort(key=lambda r: r["gap_ruc_eur"])
        contributors_rows.sort(key=lambda r: -r["gap_ruc_eur"])

    last_month_review = {
        "actual_revenue_eur":  round(actual_revenue, 0),
        "actual_ruc_eur":      round(actual_ruc, 0),
        "actual_qty":          round(actual_qty, 0),
        "var_revenue_eur":     round(actual_revenue - plan["revenue_eur"], 0) if plan["exists"] else None,
        "var_ruc_eur":         round(actual_ruc     - plan["ruc_eur"],     0) if plan["exists"] else None,
        "var_revenue_pct":     round((actual_revenue / plan["revenue_eur"] - 1) * 100, 1) if plan["exists"] and plan["revenue_eur"] > 0 else None,
        "var_ruc_pct":         round((actual_ruc     / plan["ruc_eur"]     - 1) * 100, 1) if plan["exists"] and plan["ruc_eur"]     > 0 else None,
        "top_offenders":       offenders_rows[:10],
        "top_contributors":    contributors_rows[:10],
    }

    # ── 4) Monthly FA — aggregate backtest to the month
    fa = pd.read_sql(text("""
        SELECT p.sku, p.name,
               COALESCE(sp.tier,'') AS tier,
               SUM(br.forecast)::float AS forecast,
               SUM(br.actual)::float   AS actual
        FROM backtest_results br
        JOIN dim_products p ON p.id = br.product_id
        LEFT JOIN sku_planning sp ON sp.product_id = p.id
        WHERE br.year * 100 + br.week = ANY(:yws)
          AND br.actual > 0
        GROUP BY p.sku, p.name, sp.tier
    """), db.bind, params={"yws": iso_yw_list})

    # On-top totals for the same month per SKU (for KAM_over/under reasoning)
    ot = pd.read_sql(text("""
        SELECT p.sku,
               SUM(CASE WHEN channel = 'wholesale'                 THEN quantity ELSE 0 END)::float AS ot_ws,
               SUM(CASE WHEN channel IN ('retail','food retail')   THEN quantity ELSE 0 END)::float AS ot_mp
        FROM on_top_inputs oti
        JOIN dim_products p ON p.id = oti.product_id
        WHERE oti.year_week = ANY(:yws)
        GROUP BY p.sku
    """), db.bind, params={"yws": iso_yw_list}).set_index("sku")

    # Stock proxy — current WH stock < 1.5w × avg demand AND actual << forecast
    stk = pd.read_sql(text("""
        SELECT p.sku, SUM(CASE WHEN ds.is_warehouse THEN esc.stock_qty ELSE 0 END)::float AS wh_now
        FROM erp_stock_current esc
        JOIN dim_stores ds ON ds.id = esc.store_id
        JOIN dim_products p ON p.id = esc.product_id
        GROUP BY p.sku
    """), db.bind).set_index("sku")

    fa["ot_total"] = fa["sku"].map(lambda s: float((ot.loc[s, "ot_ws"] if s in ot.index else 0) + (ot.loc[s, "ot_mp"] if s in ot.index else 0)))
    fa["wh_now"]   = fa["sku"].map(lambda s: float(stk.loc[s, "wh_now"]) if s in stk.index else 0.0)
    fa["weeks_in_month"] = len(iso_weeks)
    fa["avg_wk_forecast"] = fa["forecast"] / fa["weeks_in_month"].replace(0, 1)
    fa["had_stockout_proxy"] = (
        (fa["wh_now"] < 1.5 * fa["avg_wk_forecast"]) &
        (fa["actual"] < 0.7 * fa["forecast"])
    )
    fa["abs_err"] = (fa["forecast"] - fa["actual"]).abs()
    fa["fa_pct"]  = (fa["forecast"] / fa["actual"].where(fa["actual"] > 0, 1)) * 100
    fa["reason"]  = fa.apply(
        lambda r: _classify_fa_reason(
            on_top_total=float(r["ot_total"]),
            forecast=float(r["forecast"]),
            actual=float(r["actual"]),
            had_stockout=bool(r["had_stockout_proxy"]),
        ), axis=1,
    )

    monthly_fa_offenders = fa.sort_values("abs_err", ascending=False).head(15)
    monthly_fa = {
        "n_skus":    int(len(fa)),
        "n_weeks":   len(iso_weeks),
        "fa_signed_pct": round(float(fa["forecast"].sum() / max(fa["actual"].sum(), 1) * 100), 1),
        "by_reason": fa.groupby("reason")["abs_err"].agg(["count", "sum"]).reset_index().rename(
            columns={"count": "n_skus", "sum": "abs_err_units"}
        ).to_dict("records"),
        "top_offenders": [
            {
                "sku": r["sku"], "name": r["name"], "tier": r["tier"],
                "forecast": round(float(r["forecast"]), 0),
                "actual":   round(float(r["actual"]),   0),
                "abs_err":  round(float(r["abs_err"]),  0),
                "fa_pct":   round(float(r["fa_pct"]),   1),
                "reason":   r["reason"],
            }
            for _, r in monthly_fa_offenders.iterrows()
        ],
    }

    # ── 5) Lost sales — uses Finance forward-looking report when month is
    #      current (the only case where it's meaningful). For past months we
    #      surface a note: real backward attribution needs the stock_history
    #      table which only started snapshotting from this week forward.
    if is_partial:
        try:
            ctx = fin.FinanceContext(db)
            ls = fin.report_lost_sales(ctx)
            df_ls = ls.get("df")
            if df_ls is not None and not df_ls.empty:
                df_ls = df_ls.copy()
                # Filter to Gold/Silver/Bronze tiered SKUs only
                gsb_mask = df_ls["tier"].fillna("").str.upper().isin(
                    {"01 GOLD", "02 SILVER", "03 BRONZE"}
                )
                df_ls = df_ls[gsb_mask]
                # Compute lost RUC per row = lost_units × (sell_price − cost)
                df_ls["lost_ruc_eur"] = (
                    df_ls["lost_demand_units"]
                    * (df_ls["selling_price"] - df_ls["cost_price"])
                ).round(2)
                at_risk = df_ls[df_ls["stockout_week"] != "SAFE"].sort_values(
                    "lost_ruc_eur", ascending=False,
                ).head(10)
                lost_sales = {
                    "total_units":       round(float(at_risk["lost_demand_units"].sum()), 0),
                    "total_revenue_eur": round(float(at_risk["lost_sales_revenue_eur"].sum()), 0),
                    "total_ruc_eur":     round(float(at_risk["lost_ruc_eur"].sum()), 0),
                    "n_events":          int(len(at_risk)),
                    "top_events": [
                        {
                            "sku":              r["sku"],
                            "name":             r.get("name"),
                            "tier":             r.get("tier"),
                            "year":             year,
                            "week":             None,
                            "lost_units":       float(r["lost_demand_units"]),
                            "lost_revenue_eur": float(r["lost_sales_revenue_eur"]),
                            "lost_ruc_eur":     float(r["lost_ruc_eur"]),
                        }
                        for _, r in at_risk.iterrows()
                    ],
                    "note": "Forward-looking stockout risk (Finance Lost Sales source) — "
                            "Gold/Silver/Bronze SKUs only. Backward attribution will be "
                            "available from next month now that stock_history is "
                            "collecting weekly snapshots.",
                }
            else:
                lost_sales = {
                    "total_units": 0, "total_revenue_eur": 0, "total_ruc_eur": 0,
                    "n_events": 0, "top_events": [],
                }
        except Exception as exc:  # noqa: BLE001
            lost_sales = {
                "error": f"{type(exc).__name__}: {exc}",
                "n_events": 0, "total_revenue_eur": 0, "total_ruc_eur": 0,
                "top_events": [],
            }
    else:
        lost_sales = {
            "n_events": 0,
            "total_units": 0, "total_revenue_eur": 0, "total_ruc_eur": 0,
            "top_events": [],
            "note": "Backward stockout attribution requires the stock_history table. "
                    "Snapshots started this week — full month attribution will be "
                    "available for the next closed month onward.",
        }

    # ── 6) Current-month progress (only when month is the running calendar month)
    progress = None
    if is_partial:
        # Split iso_weeks into "already complete" (week_end <= today) and remaining
        today = date.today()
        completed_yws: list[tuple[int, int]] = []
        remaining_yws: list[tuple[int, int]] = []
        for (y, w, _) in iso_weeks:
            try:
                mon = iso_week_first_monday(y, w)
                sun = mon + timedelta(days=6)
            except Exception:
                continue
            if sun <= today:
                completed_yws.append((y, w))
            else:
                remaining_yws.append((y, w))

        # Actual revenue for completed weeks of the target month so far —
        # STRICT calendar-month window (matches the Channel breakdown
        # below). Earlier this used `ISO_week IN (completed_yws)` which
        # included the late-April days of CW18 (Apr 27-30) when the user
        # was viewing May, inflating May's MTD by ~€500K. Returns are
        # NETTED in (no qty>0 filter), same as the finance reports
        # managers reconcile against.
        actual_to_date_rev = 0.0
        actual_to_date_ruc = 0.0
        if completed_yws:
            # Window = intersection of [month_start, month_end] with the
            # union of completed ISO weeks. Latest completed Sunday is
            # what bounds the MTD upper edge.
            from datetime import date as _date
            sundays = []
            for (y, w) in completed_yws:
                try:
                    mon = iso_week_first_monday(y, w)
                    sundays.append(mon + timedelta(days=6))
                except Exception:
                    pass
            if sundays:
                upper = min(month_end, max(sundays))
                lower = month_start
                row = db.execute(text("""
                    SELECT
                      COALESCE(SUM(COALESCE(NULLIF(et.tax_base,0),
                                             et.total_value * 0.80)), 0)::float AS rev,
                      COALESCE(SUM(et.ruc_eur), 0)::float                          AS ruc
                    FROM erp_transactions et
                    WHERE et.transaction_date BETWEEN DATE :lower AND DATE :upper
                """), {"lower": lower.isoformat(),
                       "upper": upper.isoformat()}).mappings().first() or {}
                actual_to_date_rev = float(row.get("rev") or 0)
                actual_to_date_ruc = float(row.get("ruc") or 0)

        # Remaining-week forecast revenue — split into WS + B2C and value
        # each at the channel-appropriate plan price (VPC for wholesale,
        # catalog-NET for B2C). The old approach multiplied the whole
        # qty by avg_sell_price which is a blended trailing realization
        # — that overstates revenue for KAM-commit-heavy upcoming weeks
        # where WS quantity dominates.
        proj_remaining_rev = 0.0
        if remaining_yws:
            yws_int = [y*100 + w for (y, w) in remaining_yws]
            f = pd.read_sql(text("""
                WITH latest_fc AS (
                    SELECT DISTINCT ON (product_id, year, week)
                        product_id, year, week,
                        (COALESCE(baseline,0) * COALESCE(planner_factor,1))::float
                          AS baseline_qty,
                        COALESCE(on_top_wholesale, 0)::float AS ot_ws,
                        COALESCE(on_top_retail,    0)::float AS ot_mp
                    FROM forecasts
                    WHERE (year * 100 + week) = ANY(:yws)
                    ORDER BY product_id, year, week, run_id DESC
                )
                SELECT p.sku, f.baseline_qty, f.ot_ws, f.ot_mp,
                       COALESCE(sp.ws_share_26w, 0.5)::float AS ws_share,
                       COALESCE(sp.vpc,             0)::float AS vpc,
                       COALESCE(ep.normal_retail_ppp, 0)::float * 0.80 AS retail_net,
                       COALESCE(ep.avg_sell_price,    0)::float AS avg_price
                FROM latest_fc f
                JOIN dim_products p ON p.id = f.product_id
                LEFT JOIN sku_planning sp ON sp.product_id = f.product_id
                LEFT JOIN erp_prices ep   ON ep.product_id = f.product_id
            """), db.bind, params={"yws": yws_int})
            # Plan price lookups from snapshot (preferred — frozen at lock)
            snap_ws  = {sku: float(p.get("plan_price_ws") or 0) for sku, p in per_sku_plan.items()}
            snap_b2c = {sku: float(p.get("plan_price_b2c") or 0) for sku, p in per_sku_plan.items()}
            def _plan_price_ws(row):
                v = snap_ws.get(row["sku"], 0.0)
                if v > 0: return v
                if row["vpc"] > 0: return row["vpc"]
                return row["avg_price"]
            def _plan_price_b2c(row):
                v = snap_b2c.get(row["sku"], 0.0)
                if v > 0: return v
                if row["retail_net"] > 0: return row["retail_net"]
                return row["avg_price"]
            f["p_ws"]  = f.apply(_plan_price_ws,  axis=1)
            f["p_b2c"] = f.apply(_plan_price_b2c, axis=1)
            f["qty_ws"]  = f["baseline_qty"] * f["ws_share"]       + f["ot_ws"]
            f["qty_b2c"] = f["baseline_qty"] * (1 - f["ws_share"]) + f["ot_mp"]
            f["revenue"] = f["qty_ws"] * f["p_ws"] + f["qty_b2c"] * f["p_b2c"]
            proj_remaining_rev = float(f["revenue"].sum())

        # Remaining-week RUC projection — qty × ruc_rate per channel
        proj_remaining_ruc = 0.0
        if remaining_yws:
            yws_int = [y*100 + w for (y, w) in remaining_yws]
            ruc_df = pd.read_sql(text("""
                WITH latest_fc AS (
                    SELECT DISTINCT ON (product_id, year, week)
                        product_id, year, week,
                        (COALESCE(baseline,0) * COALESCE(planner_factor,1))::float AS baseline_qty,
                        COALESCE(on_top_wholesale, 0)::float AS ot_ws,
                        COALESCE(on_top_retail,    0)::float AS ot_mp
                    FROM forecasts
                    WHERE (year * 100 + week) = ANY(:yws)
                    ORDER BY product_id, year, week, run_id DESC
                ),
                ruc_rates AS (
                    SELECT product_id,
                           NULLIF(SUM(CASE WHEN cm.channel = 'wholesale' THEN et.quantity ELSE 0 END),0)::float AS qty_ws,
                           SUM(CASE WHEN cm.channel = 'wholesale' THEN et.ruc_eur ELSE 0 END)::float                AS ruc_ws,
                           NULLIF(SUM(CASE WHEN cm.channel IN ('retail','webshop') THEN et.quantity ELSE 0 END),0)::float AS qty_b2c,
                           SUM(CASE WHEN cm.channel IN ('retail','webshop') THEN et.ruc_eur ELSE 0 END)::float    AS ruc_b2c
                    FROM erp_transactions et
                    JOIN lookup_channel_map cm ON cm.id = et.channel_map_id
                    WHERE et.transaction_date >= (CURRENT_DATE - INTERVAL '8 weeks')
                    GROUP BY product_id
                )
                SELECT p.sku, f.baseline_qty, f.ot_ws, f.ot_mp,
                       COALESCE(sp.ws_share_26w, 0.5)::float                  AS ws_share,
                       COALESCE(ruc.ruc_ws  / ruc.qty_ws,  0)::float           AS ruc_rate_ws,
                       COALESCE(ruc.ruc_b2c / ruc.qty_b2c, 0)::float          AS ruc_rate_b2c
                FROM latest_fc f
                JOIN dim_products p     ON p.id = f.product_id
                LEFT JOIN sku_planning sp ON sp.product_id = f.product_id
                LEFT JOIN ruc_rates ruc  ON ruc.product_id = f.product_id
            """), db.bind, params={"yws": yws_int})
            ruc_df["qty_ws_total"]  = ruc_df["baseline_qty"] * ruc_df["ws_share"]       + ruc_df["ot_ws"]
            ruc_df["qty_b2c_total"] = ruc_df["baseline_qty"] * (1 - ruc_df["ws_share"]) + ruc_df["ot_mp"]
            ruc_df["ruc_total"] = (
                ruc_df["qty_ws_total"]  * ruc_df["ruc_rate_ws"]
                + ruc_df["qty_b2c_total"] * ruc_df["ruc_rate_b2c"]
            )
            proj_remaining_ruc = float(ruc_df["ruc_total"].sum())

        proj_total = actual_to_date_rev + proj_remaining_rev
        proj_total_ruc = actual_to_date_ruc + proj_remaining_ruc
        progress = {
            "weeks_complete":           [f"CW{w:02d}" for (_, w) in completed_yws],
            "weeks_remaining":          [f"CW{w:02d}" for (_, w) in remaining_yws],
            "actual_to_date_revenue_eur": round(actual_to_date_rev, 0),
            "actual_to_date_ruc_eur":     round(actual_to_date_ruc, 0),
            "proj_remaining_revenue_eur": round(proj_remaining_rev, 0),
            "proj_remaining_ruc_eur":     round(proj_remaining_ruc, 0),
            "proj_total_revenue_eur":     round(proj_total, 0),
            "proj_total_ruc_eur":         round(proj_total_ruc, 0),
            "var_to_plan_pct":            (
                round((proj_total / plan["revenue_eur"] - 1) * 100, 1)
                if plan["exists"] and plan["revenue_eur"] > 0 else None
            ),
            "var_ruc_to_plan_pct":        (
                round((proj_total_ruc / plan["ruc_eur"] - 1) * 100, 1)
                if plan["exists"] and plan["ruc_eur"] > 0 else None
            ),
            "on_track":                   bool(plan["exists"] and plan["revenue_eur"] > 0 and proj_total >= plan["revenue_eur"] * 0.95),
        }

    # ── 7) Plan drivers (only meaningful when the month still has future
    #      weeks — i.e. partial or fully future). Two views:
    #         a) Top buyers by KAM commit value (qty × plan_price_ws)
    #         b) Top promo parents by committed qty + plan revenue impact
    #      Both pull from on_top_inputs + promo_policy_items so they reflect
    #      what's actually scheduled in the system right now.
    plan_drivers = None
    if not (date.today() > month_end):  # partial or future
        # Per-SKU plan_price + plan_ruc rate lookups (from snapshot)
        sku_to_price_ws  = {sku: float(p.get("plan_price_ws") or 0)
                              for sku, p in per_sku_plan.items()}
        sku_to_price_b2c = {sku: float(p.get("plan_price_b2c") or 0)
                              for sku, p in per_sku_plan.items()}
        sku_to_ruc_ws    = {sku: float(p.get("ws_ruc_rate") or 0)
                              for sku, p in per_sku_plan.items()}
        sku_to_ruc_b2c   = {sku: float(p.get("retail_ruc_rate") or 0)
                              for sku, p in per_sku_plan.items()}
        sku_to_name = {sku: p.get("name") for sku, p in per_sku_plan.items()}

        # By buyer — wholesale commits only (buyer field is only set on WS rows)
        ot_buyer = pd.read_sql(text("""
            SELECT
              COALESCE(NULLIF(oti.buyer, ''), '(unassigned)') AS buyer,
              p.sku,
              SUM(oti.quantity)::float AS qty
            FROM on_top_inputs oti
            JOIN dim_products p ON p.id = oti.product_id
            WHERE oti.channel = 'wholesale'
              AND oti.year_week = ANY(:yws)
            GROUP BY buyer, p.sku
        """), db.bind, params={"yws": iso_yw_list})
        ot_buyer["plan_price"]  = ot_buyer["sku"].map(sku_to_price_ws).fillna(0)
        ot_buyer["ruc_rate"]    = ot_buyer["sku"].map(sku_to_ruc_ws).fillna(0)
        ot_buyer["revenue_eur"] = ot_buyer["qty"] * ot_buyer["plan_price"]
        ot_buyer["ruc_eur"]     = ot_buyer["qty"] * ot_buyer["ruc_rate"]
        by_buyer = (ot_buyer.groupby("buyer")
                              .agg(n_skus=("sku", "nunique"),
                                   qty=("qty", "sum"),
                                   revenue_eur=("revenue_eur", "sum"),
                                   ruc_eur=("ruc_eur", "sum"))
                              .reset_index()
                              .sort_values("revenue_eur", ascending=False)
                              .head(10))

        # By promo parent — sum committed qty for each parent_opis. Promo
        # policies (rabat) fire at the till only, so we count only the
        # B2C (retail + food retail) on-top commits, not wholesale. Value
        # at the B2C plan price + B2C RUC rate accordingly.
        promo_drivers = pd.read_sql(text("""
            SELECT ppi.description AS parent,
                   CASE WHEN cmp.section IS NOT NULL THEN cmp.section
                        WHEN ppi.description ILIKE '%contest%' THEN 'CONTEST'
                        ELSE 'PROMO' END AS section,
                   p.sku,
                   ppi.rabat_pct,
                   COALESCE(SUM(ot_b2c.q), 0) AS committed_qty
            FROM promo_policies pp
            JOIN promo_policy_items ppi ON ppi.policy_id = pp.id
            JOIN dim_products p          ON p.id = ppi.product_id
            LEFT JOIN contest_monthly_plan cmp
              ON cmp.month_key = :mk
             AND cmp.policy_name = pp.policy_name
             AND cmp.parent_opis = ppi.description
            LEFT JOIN (
                SELECT product_id, quantity AS q FROM on_top_inputs
                WHERE channel IN ('retail','food retail')
                  AND year_week = ANY(:yws)
            ) ot_b2c ON ot_b2c.product_id = ppi.product_id
            WHERE (pp.valid_from, pp.valid_to) OVERLAPS
                  (DATE :mstart, DATE :mend)
            GROUP BY ppi.description, cmp.section, p.sku, ppi.rabat_pct
        """), db.bind, params={
            "mk": month_key, "yws": iso_yw_list,
            "mstart": month_start.isoformat(), "mend": month_end.isoformat(),
        })
        # B2C-only valuation (promo is a till-side discount → uses B2C price).
        promo_drivers["plan_price_b2c"] = promo_drivers["sku"].map(sku_to_price_b2c).fillna(0)
        promo_drivers["ruc_b2c"]        = promo_drivers["sku"].map(sku_to_ruc_b2c).fillna(0)
        promo_drivers["revenue_eur"] = (
            promo_drivers["committed_qty"] * promo_drivers["plan_price_b2c"]
        )
        promo_drivers["ruc_eur"] = (
            promo_drivers["committed_qty"] * promo_drivers["ruc_b2c"]
        )
        by_promo = (promo_drivers.groupby(["parent", "section"])
                                   .agg(n_skus=("sku", "nunique"),
                                        committed_qty=("committed_qty", "sum"),
                                        revenue_eur=("revenue_eur", "sum"),
                                        ruc_eur=("ruc_eur", "sum"),
                                        max_rabat_pct=("rabat_pct", "max"))
                                   .reset_index()
                                   .sort_values("revenue_eur", ascending=False)
                                   .head(15))

        plan_drivers = {
            "top_buyers": [
                {"buyer": r["buyer"], "n_skus": int(r["n_skus"]),
                 "qty":   round(float(r["qty"]), 0),
                 "revenue_eur": round(float(r["revenue_eur"]), 0),
                 "ruc_eur":     round(float(r["ruc_eur"]), 0)}
                for _, r in by_buyer.iterrows()
            ],
            "top_promos": [
                {"parent": r["parent"], "section": r["section"],
                 "n_skus": int(r["n_skus"]),
                 "committed_qty": round(float(r["committed_qty"]), 0),
                 "revenue_eur":   round(float(r["revenue_eur"]), 0),
                 "ruc_eur":       round(float(r["ruc_eur"]), 0),
                 "max_rabat_pct": float(r["max_rabat_pct"]) if pd.notna(r["max_rabat_pct"]) else None}
                for _, r in by_promo.iterrows()
            ],
        }

    # ── 8) Three-channel breakdown (wholesale / retail / webshop) ──────
    # Actuals: STRICT CALENDAR MONTH (transaction_date BETWEEN month_start
    # AND month_end). Earlier this used the same ISO-week scope as plan
    # day-fraction logic, but that included late-April dates as part of
    # May (CW18 = Apr 27 - May 3) and inflated the May actuals by ~5 days
    # of April. Matching strict calendar lets the page agree with the
    # finance-side actuals reports managers use.
    # Channel actuals — strict calendar month, NETS RETURNS (don't filter
    # qty>0; negative-qty rows reduce the total like the finance report
    # does). LEFT JOIN on channel map so unmapped rows fall into 'other'
    # and don't disappear (caused €15K silent gap vs managers' table).
    chan_actual = pd.read_sql(text("""
        SELECT COALESCE(cm.channel, 'other') AS channel,
               SUM(et.quantity)::float       AS qty,
               SUM(COALESCE(NULLIF(et.tax_base,0),
                            et.total_value * 0.80))::float AS revenue_eur,
               SUM(et.ruc_eur)::float        AS ruc_eur
        FROM erp_transactions et
        LEFT JOIN lookup_channel_map cm ON cm.id = et.channel_map_id
        WHERE et.transaction_date BETWEEN DATE :mstart AND DATE :mend
        GROUP BY COALESCE(cm.channel, 'other')
    """), db.bind, params={
        "mstart": month_start.isoformat(),
        "mend":   month_end.isoformat(),
    })
    chan_actual_map = {
        r["channel"]: {"qty":  float(r["qty"]  or 0),
                       "revenue_eur": float(r["revenue_eur"] or 0),
                       "ruc_eur":     float(r["ruc_eur"] or 0)}
        for _, r in chan_actual.iterrows()
    }

    # B2C retail-vs-webshop split — trailing-13w ratio from v_sales_weekly_full
    b2c_split_row = db.execute(text("""
        WITH last13 AS (
            SELECT year, week FROM v_sales_weekly_full
            GROUP BY year, week ORDER BY year DESC, week DESC LIMIT 13
        )
        SELECT SUM(qty_retail)::float                AS retail_qty,
               SUM(qty_webshop)::float               AS webshop_qty,
               (SUM(qty_retail) + SUM(qty_webshop))::float AS b2c_qty
        FROM v_sales_weekly_full v
        JOIN last13 lw USING (year, week)
    """)).mappings().first() or {}
    b2c_total = float(b2c_split_row.get("b2c_qty") or 0)
    retail_share  = float(b2c_split_row.get("retail_qty")  or 0) / b2c_total if b2c_total > 0 else 0.85
    webshop_share = 1.0 - retail_share

    # Plan side — split the aligned headline (plan["revenue_eur"] and
    # plan["ruc_eur"]) by the WS/B2C proportions implied by per_sku_data.
    # This way WS+B2C(retail+webshop) sums to the headline.
    sku_rev_ws  = sum(float(p.get("ws_revenue_eur_df")  or 0) for p in per_sku_plan.values())
    sku_rev_b2c = sum(float(p.get("b2c_revenue_eur_df") or 0) for p in per_sku_plan.values())
    sku_ruc_ws  = sum(float(p.get("ws_ruc_eur_df")      or 0) for p in per_sku_plan.values())
    sku_ruc_b2c = sum(float(p.get("b2c_ruc_eur_df")     or 0) for p in per_sku_plan.values())
    rev_ws_share = (sku_rev_ws / (sku_rev_ws + sku_rev_b2c)) if (sku_rev_ws + sku_rev_b2c) > 0 else 0.5
    ruc_ws_share = (sku_ruc_ws / (sku_ruc_ws + sku_ruc_b2c)) if (sku_ruc_ws + sku_ruc_b2c) > 0 else 0.5
    plan_revenue_ws  = float(plan.get("revenue_eur") or 0) * rev_ws_share
    plan_revenue_b2c = float(plan.get("revenue_eur") or 0) * (1 - rev_ws_share)
    # Prefer the stored channel split (set by build_monthly_snapshot AFTER
    # all calibrations — retail-target pin, fictive add-back, etc). The
    # per_sku-derived ratio lags these post-build adjustments, so for any
    # snapshot built with calibration the stored values are the truth.
    stored_ws_ruc  = float(plan.get("ruc_ws_stored") or 0)
    stored_b2c_ruc = float(plan.get("ruc_b2c_stored") or 0)
    if stored_ws_ruc > 0 and stored_b2c_ruc > 0:
        plan_ruc_ws  = stored_ws_ruc
        plan_ruc_b2c = stored_b2c_ruc
    else:
        plan_ruc_ws  = float(plan.get("ruc_eur") or 0) * ruc_ws_share
        plan_ruc_b2c = float(plan.get("ruc_eur") or 0) * (1 - ruc_ws_share)
    plan_qty_ws      = float(plan.get("qty_ws", 0))
    plan_qty_b2c     = float(plan.get("qty_b2c", 0))

    def _chan_row(name: str,
                  plan_qty: float, plan_rev: float, plan_ruc: float,
                  actual_qty: float, actual_rev: float, actual_ruc: float,
                  mgmt_ruc: float = 0.0, mgmt_est_ruc: float = 0.0) -> dict:
        return {
            "channel":            name,
            "plan_qty":           round(plan_qty, 0),
            "plan_revenue_eur":   round(plan_rev, 0),
            "plan_ruc_eur":       round(plan_ruc, 0),
            "actual_qty":         round(actual_qty, 0),
            "actual_revenue_eur": round(actual_rev, 0),
            "actual_ruc_eur":     round(actual_ruc, 0),
            "mgmt_plan_ruc_eur":  round(mgmt_ruc, 0),
            "mgmt_estimation_ruc_eur": round(mgmt_est_ruc, 0),
        }

    actual_ws_dict      = chan_actual_map.get("wholesale", {"qty": 0, "revenue_eur": 0, "ruc_eur": 0})
    actual_retail_dict  = chan_actual_map.get("retail",    {"qty": 0, "revenue_eur": 0, "ruc_eur": 0})
    actual_webshop_dict = chan_actual_map.get("webshop",   {"qty": 0, "revenue_eur": 0, "ruc_eur": 0})

    actual_other_dict   = chan_actual_map.get("other",     {"qty": 0, "revenue_eur": 0, "ruc_eur": 0})

    # ── Mgmt plan lookup — channel × region aggregated from
    # management_plan_lines (loaded from leadership spreadsheet).
    # Mapping: 'Retail domestic' + 'Retail international' → Retail row.
    #          'Online' → Webshop row. 'Wholesale' (Export already
    #          folded in at load time) → Wholesale row.
    mgmt = pd.read_sql(text("""
        SELECT channel, SUM(COALESCE(ruc_plan, 0))::float           AS ruc_plan,
                        SUM(COALESCE(ruc_estimation_month, 0))::float AS ruc_est
        FROM management_plan_lines WHERE month_key = :mk
        GROUP BY channel
    """), db.bind, params={"mk": month_key})
    mgmt_map: dict[str, dict] = {}
    for _, r in mgmt.iterrows():
        mgmt_map[r["channel"]] = {"ruc_plan": float(r["ruc_plan"]),
                                   "ruc_est":  float(r["ruc_est"])}
    mgmt_retail_ruc = (mgmt_map.get("Retail domestic", {}).get("ruc_plan", 0)
                       + mgmt_map.get("Retail international", {}).get("ruc_plan", 0))
    mgmt_retail_est = (mgmt_map.get("Retail domestic", {}).get("ruc_est", 0)
                       + mgmt_map.get("Retail international", {}).get("ruc_est", 0))
    mgmt_webshop_ruc = mgmt_map.get("Online", {}).get("ruc_plan", 0)
    mgmt_webshop_est = mgmt_map.get("Online", {}).get("ruc_est", 0)
    mgmt_ws_ruc      = mgmt_map.get("Wholesale", {}).get("ruc_plan", 0)
    mgmt_ws_est      = mgmt_map.get("Wholesale", {}).get("ruc_est", 0)

    channel_breakdown = [
        _chan_row("Wholesale",
                  plan_qty_ws, plan_revenue_ws, plan_ruc_ws,
                  actual_ws_dict["qty"], actual_ws_dict["revenue_eur"], actual_ws_dict["ruc_eur"],
                  mgmt_ws_ruc, mgmt_ws_est),
        _chan_row("Retail",
                  plan_qty_b2c * retail_share,
                  plan_revenue_b2c * retail_share,
                  plan_ruc_b2c * retail_share,
                  actual_retail_dict["qty"], actual_retail_dict["revenue_eur"], actual_retail_dict["ruc_eur"],
                  mgmt_retail_ruc, mgmt_retail_est),
        _chan_row("Webshop",
                  plan_qty_b2c * webshop_share,
                  plan_revenue_b2c * webshop_share,
                  plan_ruc_b2c * webshop_share,
                  actual_webshop_dict["qty"], actual_webshop_dict["revenue_eur"], actual_webshop_dict["ruc_eur"],
                  mgmt_webshop_ruc, mgmt_webshop_est),
    ]
    # Add "Other" row only when there's unmapped activity worth showing.
    # These are doc types not registered in lookup_channel_map (typically
    # internal transfers / quotation docs) — usually a small residual.
    if actual_other_dict["revenue_eur"] > 0 or actual_other_dict["qty"] > 0:
        channel_breakdown.append(_chan_row(
            "Other (unmapped)",
            0, 0, 0,  # no plan equivalent — these aren't planned channels
            actual_other_dict["qty"], actual_other_dict["revenue_eur"], actual_other_dict["ruc_eur"],
        ))

    return {
        "month_key":         month_key,
        "label":             label,
        "is_partial":        is_partial,
        "is_past":           date.today() > month_end,
        "is_future":         date.today() < month_start,
        "weeks":             [f"CW{w:02d}" for (_, w, _) in iso_weeks],
        "plan":              plan,
        "last_month_review": last_month_review,
        "monthly_fa":        monthly_fa,
        "lost_sales":        lost_sales,
        "progress":          progress,
        "plan_drivers":      plan_drivers,
        "channel_breakdown": channel_breakdown,
        "generated_at":      date.today().isoformat(),
    }