"""Scenario Planner — port of Streamlit page_supply_scenarios.

Pure-Python algorithms (no DB calls — caller passes in resolved data
maps). Mirrors app.py:7060-7229 verbatim so the React + Streamlit
versions stay numerically identical.

Public entry point: `compute_scenario()`. Caller pre-loads stock /
forecast / incoming / costs / lead times / VP-buyer demand, then this
module classifies each PO, runs the sanity check, and returns the
per-PO frame + projection series + KPI summary.

Excel export builder lives at `build_supplier_xlsx()`.
"""
from __future__ import annotations

import io
from dataclasses import dataclass, field
from typing import Optional

from sqlalchemy import text
from sqlalchemy.orm import Session


# -------------------------------------------------------------------
# Week arithmetic (naive 52-week year — matches app.py)
# -------------------------------------------------------------------

def week_step(y: int, w: int, n: int = 1) -> tuple[int, int]:
    """Step (year, week) forward by n weeks. Wraps at 52."""
    w_new = w + n
    while w_new > 52:
        w_new -= 52
        y += 1
    while w_new < 1:
        w_new += 52
        y -= 1
    return (y, w_new)


def week_series(start_y: int, start_w: int, n_weeks: int) -> list[tuple[int, int]]:
    out: list[tuple[int, int]] = []
    y, w = start_y, start_w
    for _ in range(n_weeks):
        out.append((y, w))
        y, w = week_step(y, w, 1)
    return out


# -------------------------------------------------------------------
# Cover walk (fractional weeks)
# -------------------------------------------------------------------

def real_weeks_cover(stock_now: float, demand_series: list[float]) -> float:
    """Walk-forward weeks of cover, with trailing-average extrapolation.

    A SKU with zero demand returns the horizon + 999 (effectively
    infinite — that's what trips the cancel threshold).

    Thin wrapper over the canonical engine (backend.services.coverage). Here
    incoming POs are already folded into stock_now / the series by the caller,
    so no separate incoming stream is passed.
    """
    from backend.services.coverage import weeks_of_cover
    return weeks_of_cover(stock_now, demand_series)


# -------------------------------------------------------------------
# 3-state classifier
# -------------------------------------------------------------------

def classify(
    real_cover_now: float,
    real_post_delivery_cover: float,    # informational, doesn't drive logic
    cancel_threshold: Optional[float],
    postpone_trigger: float = 4.0,
) -> str:
    if cancel_threshold is not None and real_cover_now >= cancel_threshold:
        return "CANCEL"
    if real_cover_now >= postpone_trigger:
        return "POSTPONE"
    return "PRODUCE"


# -------------------------------------------------------------------
# Sanity check — un-postpone latest PO when stacking causes stockout
# -------------------------------------------------------------------

def sanity_check(
    per_po: list[dict],          # mutated copies: each dict has sku/po_year/po_week/action_raw
    all_pos: list[dict],         # full incoming universe (sku, year, week, qty)
    fc_lookup: dict[tuple[str, int], float],
    wh_map: dict[str, float],
    walk_weeks: list[tuple[int, int]],
    postpone_delay: int,
) -> tuple[list[str], list[tuple[str, int, int]]]:
    """Returns (adjusted_actions list, flipped PO keys)."""
    n = len(per_po)
    adjusted = [r["action_raw"] for r in per_po]
    flips: list[tuple[str, int, int]] = []

    # Group rows by SKU
    by_sku: dict[str, list[int]] = {}
    for i, r in enumerate(per_po):
        by_sku.setdefault(r["sku"], []).append(i)

    pos_by_sku: dict[str, list[dict]] = {}
    for p in all_pos:
        pos_by_sku.setdefault(p["sku"], []).append(p)

    for sku, idxs in by_sku.items():
        actions = {
            (sku, int(per_po[i]["po_year"]), int(per_po[i]["po_week"])): adjusted[i]
            for i in idxs
        }
        if sum(1 for a in actions.values() if a == "POSTPONE") < 2:
            continue
        while True:
            # Rebuild inflow for this SKU under current 'actions'
            inflow: dict[int, float] = {}
            for p in pos_by_sku.get(sku, []):
                py, pw, q = int(p["year"]), int(p["week"]), float(p["qty"])
                key = (sku, py, pw)
                act = actions.get(key)
                if act == "CANCEL":
                    continue
                if act == "POSTPONE":
                    ny, nw = week_step(py, pw, postpone_delay)
                    inflow[ny * 100 + nw] = inflow.get(ny * 100 + nw, 0.0) + q
                else:
                    inflow[py * 100 + pw] = inflow.get(py * 100 + pw, 0.0) + q
            # Walk WH stock
            s = float(wh_map.get(sku, 0))
            violation = False
            for (y, w) in walk_weeks:
                k = y * 100 + w
                d = fc_lookup.get((sku, k), 0.0)
                s = max(0.0, s + inflow.get(k, 0) - d)
                if d > 0 and s < d:
                    violation = True
                    break
            if not violation:
                break
            # Flip the latest-week active POSTPONE → PRODUCE
            active_postpones = sorted(
                [
                    i for i in idxs
                    if actions.get((sku, int(per_po[i]["po_year"]),
                                    int(per_po[i]["po_week"]))) == "POSTPONE"
                ],
                key=lambda i: (per_po[i]["po_year"], per_po[i]["po_week"]),
                reverse=True,
            )
            if not active_postpones:
                break
            flip_i = active_postpones[0]
            key = (sku, int(per_po[flip_i]["po_year"]), int(per_po[flip_i]["po_week"]))
            actions[key] = "PRODUCE"
            adjusted[flip_i] = "PRODUCE"
            flips.append(key)

    return adjusted, flips


# -------------------------------------------------------------------
# Company-wide cash projection (€ stock walk)
# -------------------------------------------------------------------

def company_eur_projection(
    all_pos: list[dict],
    scenario_actions: dict[tuple[str, int, int], str],
    cost_map: dict[str, float],
    fc_lookup: dict[tuple[str, int], float],
    outflow_skus: set[str],
    walk_weeks: list[tuple[int, int]],
    start_stock: dict[str, float],
    postpone_delay: int,
    postpone_targets: Optional[dict[tuple[str, int, int], tuple[int, int]]] = None,
    avg_map: Optional[dict[str, float]] = None,
) -> dict[tuple[int, int], float]:
    """Per-SKU walk-forward of stock value.

    For every SKU and every week:
        closing[sku] = max(0, opening[sku] - demand[sku] + inflow[sku])
    Then total_value[week] = Σ closing[sku] × cost[sku].

    The per-SKU floor is what aligns this with
    SupplyService.get_stock_projection_data — a SKU that stocks out
    contributes 0 to value from that week on AND stops absorbing
    further demand. The previous aggregate version over-subtracted
    demand for stocked-out SKUs, causing a growing gap vs Stock
    Projection of up to ~€700k by week 13.

    avg_map: 4w trailing avg used as the demand fallback when
    fc_lookup has no entry for (sku, year_week).
    """
    targets = postpone_targets or {}
    avg_lookup = avg_map or {}

    # Build per-SKU per-week inflow (units), applying scenario actions
    inflow_by_sku_yw: dict[str, dict[int, float]] = {}
    for p in all_pos:
        sku = p["sku"]
        y, w, q = int(p["year"]), int(p["week"]), float(p["qty"])
        key = (sku, y, w)
        if key in scenario_actions:
            act = scenario_actions[key]
            if act == "CANCEL":
                continue
            if act == "POSTPONE":
                ny, nw = targets.get(key) or week_step(y, w, postpone_delay)
                bucket = inflow_by_sku_yw.setdefault(sku, {})
                bucket[ny * 100 + nw] = bucket.get(ny * 100 + nw, 0.0) + q
                continue
        bucket = inflow_by_sku_yw.setdefault(sku, {})
        bucket[y * 100 + w] = bucket.get(y * 100 + w, 0.0) + q

    # Per-SKU stock walk
    stock: dict[str, float] = {s: float(start_stock.get(s, 0.0)) for s in outflow_skus}
    out: dict[tuple[int, int], float] = {}
    for i, (y, w) in enumerate(walk_weeks):
        if i == 0:
            out[(y, w)] = sum(stock[s] * cost_map.get(s, 0.0) for s in outflow_skus)
            continue
        key = y * 100 + w
        for sku in outflow_skus:
            sku_inflow = inflow_by_sku_yw.get(sku, {}).get(key, 0.0)
            d = fc_lookup.get((sku, key))
            if d is None:
                d = avg_lookup.get(sku, 0.0)
            stock[sku] = max(0.0, stock[sku] + sku_inflow - float(d or 0.0))
        out[(y, w)] = sum(stock[s] * cost_map.get(s, 0.0) for s in outflow_skus)
    return out


# -------------------------------------------------------------------
# Cumulative cover-after-arrival (multi-PO stacking aware)
# -------------------------------------------------------------------

def cover_after_arrival_map(
    per_po: list[dict],
    all_pos: list[dict],
    eff_actions: dict[tuple[str, int, int], str],
    override_targets: dict[tuple[str, int, int], tuple[int, int]],
    in_scope_keys: set[tuple[str, int, int]],
    cur_y: int,
    cur_w: int,
    cover_walk_horizon: int,
    horizon: int,
    fc_lookup: dict[tuple[str, int], float],
    cover_basis_for: dict[str, float],
    postpone_delay: int,
) -> list[Optional[float]]:
    """For each PO in per_po, return cumulative cover at moment-after-arrival.

    Walk stock forward from today, accumulating all inflows (in-scope POs
    with their effective actions + out-of-scope POs as-scheduled),
    subtracting demand. At the PO's arrival week, compute forward cover.
    """
    def _arrival(sku: str, py: int, pw: int) -> Optional[tuple[int, int]]:
        key = (sku, py, pw)
        act = eff_actions.get(key, "PRODUCE")
        if act == "CANCEL":
            return None
        if act == "POSTPONE":
            return override_targets.get(key) or week_step(py, pw, postpone_delay)
        return (py, pw)

    inflow_by_sku_yw: dict[str, dict[int, float]] = {}
    for r in per_po:
        sku = r["sku"]
        py, pw = int(r["po_year"]), int(r["po_week"])
        arr = _arrival(sku, py, pw)
        if arr is None:
            continue
        ay, aw = arr
        yw = ay * 100 + aw
        inflow_by_sku_yw.setdefault(sku, {})
        inflow_by_sku_yw[sku][yw] = inflow_by_sku_yw[sku].get(yw, 0.0) + float(r["qty"])

    for p in all_pos:
        okey = (p["sku"], int(p["year"]), int(p["week"]))
        if okey in in_scope_keys:
            continue
        yw = int(p["year"]) * 100 + int(p["week"])
        inflow_by_sku_yw.setdefault(p["sku"], {})
        inflow_by_sku_yw[p["sku"]][yw] = (
            inflow_by_sku_yw[p["sku"]].get(yw, 0.0) + float(p["qty"])
        )

    walk_yws = week_series(cur_y, cur_w, cover_walk_horizon)
    yw_index = {y * 100 + w: i for i, (y, w) in enumerate(walk_yws)}

    out: list[Optional[float]] = []
    for r in per_po:
        sku = r["sku"]
        py, pw = int(r["po_year"]), int(r["po_week"])
        arr = _arrival(sku, py, pw)
        if arr is None:
            out.append(None)
            continue
        ay, aw = arr
        arrival_yw = ay * 100 + aw
        if arrival_yw not in yw_index:
            out.append(None)
            continue
        sku_inflows = inflow_by_sku_yw.get(sku, {})
        s = float(cover_basis_for.get(sku, 0.0))
        stop_i = yw_index[arrival_yw]
        for i in range(stop_i + 1):
            yw_key = walk_yws[i][0] * 100 + walk_yws[i][1]
            s += sku_inflows.get(yw_key, 0.0)
            s -= fc_lookup.get((sku, yw_key), 0.0)
            if s < 0:
                s = 0.0
        fwd = [
            fc_lookup.get((sku, y * 100 + w), 0.0)
            for (y, w) in week_series(ay, aw, horizon)[1:]
        ]
        out.append(round(real_weeks_cover(s, fwd), 1))
    return out


# -------------------------------------------------------------------
# Data loader — pulls everything the algorithm needs from Postgres
# -------------------------------------------------------------------

@dataclass
class ScenarioData:
    wh_map: dict[str, float]            # sku → WH on-hand
    stores_map: dict[str, float]        # sku → stores on-hand
    cost_map: dict[str, float]          # sku → cost_price (€/unit)
    lt_map: dict[str, float]            # sku → lead_time_weeks
    fc_lookup: dict[tuple[str, int], float]  # (sku, year*100+week) → forecast qty
    # avg_map: 4-week trailing avg of v_sales_weekly_full.qty_total per SKU.
    # Used as a demand fallback for SKUs that have stock but no entry in
    # the forecasts table (long-tail / unplanned items). Without this the
    # cash projection treats their stock value as frozen forever, which
    # diverged from the per-SKU Stock Projection chart by ~€370k.
    avg_map: dict[str, float]
    incoming: list[dict]                # all incoming_supply rows enriched
    all_skus: set[str]
    vp_buyer_demand: dict[str, dict[tuple[str, int], float]]  # buyer → {(sku, week_no): qty}
    all_vp_buyers: list[str]
    suppliers: list[str]
    cur_year: int
    cur_week: int


def load_scenario_data(db: Session) -> ScenarioData:
    """Pull stock / forecast / incoming / costs / lead times / VP buyer demand
    from Postgres in a single shot. Returns a ScenarioData record."""
    cur_row = db.execute(
        text("SELECT EXTRACT(ISOYEAR FROM now())::int AS y, EXTRACT(WEEK FROM now())::int AS w")
    ).mappings().first()
    cur_year, cur_week = int(cur_row["y"]), int(cur_row["w"])

    # WH stock — `dim_stores.is_warehouse` is canonical. `unit_code = '01'` was
    # historically used but SI/AT also have stores called '01' that are NOT
    # warehouses; only the boolean is correct cross-country.
    wh_rows = db.execute(text("""
        SELECT p.sku, SUM(esc.stock_qty)::float AS qty
        FROM erp_stock_current esc
        JOIN dim_products p ON p.id = esc.product_id
        JOIN dim_stores  ds ON ds.id = esc.store_id
        WHERE ds.is_warehouse
        GROUP BY p.sku
    """)).mappings().all()
    wh_map = {r["sku"]: float(r["qty"] or 0) for r in wh_rows}

    # Stores stock — every non-warehouse location.
    stores_rows = db.execute(text("""
        SELECT p.sku, SUM(esc.stock_qty)::float AS qty
        FROM erp_stock_current esc
        JOIN dim_products p ON p.id = esc.product_id
        JOIN dim_stores  ds ON ds.id = esc.store_id
        WHERE NOT ds.is_warehouse
        GROUP BY p.sku
    """)).mappings().all()
    stores_map = {r["sku"]: float(r["qty"] or 0) for r in stores_rows}

    # Costs
    cost_rows = db.execute(text("""
        SELECT p.sku, ec.cost_price::float AS c
        FROM erp_costs ec
        JOIN dim_products p ON p.id = ec.product_id
        WHERE ec.cost_price IS NOT NULL
    """)).mappings().all()
    cost_map = {r["sku"]: float(r["c"] or 0) for r in cost_rows}

    # Lead times (one row per product — most recent)
    lt_rows = db.execute(text("""
        SELECT p.sku, sm.lead_time_weeks::float AS lt
        FROM supply_master sm
        JOIN dim_products p ON p.id = sm.product_id
        WHERE sm.lead_time_weeks IS NOT NULL
    """)).mappings().all()
    lt_map = {r["sku"]: float(r["lt"] or 0) for r in lt_rows}

    # Forecast (latest run)
    fc_rows = db.execute(text("""
        SELECT p.sku, f.year, f.week, COALESCE(f.total, 0)::float AS demand
        FROM forecasts f
        JOIN dim_products p ON p.id = f.product_id
        WHERE f.run_id = (SELECT MAX(run_id) FROM forecasts)
    """)).mappings().all()
    fc_lookup: dict[tuple[str, int], float] = {}
    for r in fc_rows:
        fc_lookup[(r["sku"], int(r["year"]) * 100 + int(r["week"]))] = float(r["demand"] or 0)

    # 4-week trailing avg sell-through per SKU (fallback for unplanned SKUs).
    # Same shape as SupplyRepository.get_avg_weekly_demand so the per-SKU Stock
    # Projection chart and this aggregate walk converge on the same outflow.
    avg_rows = db.execute(text("""
        WITH max_yw AS (
            SELECT MAX(year * 100 + week) AS m FROM v_sales_weekly_full
        )
        SELECT p.sku,
               (SUM(v.qty_total)::float / 4)::float AS avg_qty
        FROM v_sales_weekly_full v
        JOIN dim_products p ON p.id = v.product_id
        CROSS JOIN max_yw
        WHERE v.year * 100 + v.week BETWEEN
              ((max_yw.m / 100) * 100 + GREATEST((max_yw.m % 100) - 3, 1))
              AND max_yw.m
        GROUP BY p.sku
    """)).mappings().all()
    avg_map = {r["sku"]: float(r["avg_qty"] or 0) for r in avg_rows}

    # Incoming POs enriched with supplier / tier / category / name
    inc_rows = db.execute(text("""
        SELECT
            p.sku,
            COALESCE(p.name, '')              AS name,
            COALESCE(sp.tier, '')             AS tier,
            COALESCE(c.name, '')              AS category,
            COALESCE(ds.name, '(unknown)')    AS supplier,
            ins.year::int                     AS year,
            ins.week::int                     AS week,
            ins.quantity::float               AS qty
        FROM incoming_supply ins
        JOIN dim_products p        ON p.id = ins.product_id
        LEFT JOIN sku_planning sp   ON sp.product_id = p.id
        LEFT JOIN dim_categories c  ON c.id = p.category_id
        LEFT JOIN supply_master sm  ON sm.product_id = p.id
        LEFT JOIN dim_suppliers ds  ON ds.id = sm.supplier_id
        WHERE ins.year IS NOT NULL AND ins.week IS NOT NULL
        ORDER BY p.sku, ins.year, ins.week
    """)).mappings().all()
    incoming = [dict(r) for r in inc_rows]

    all_skus = set(wh_map.keys()) | set(stores_map.keys()) | {r["sku"] for r in fc_rows}

    # VP buyer detail — from on_top_inputs joined to users to get the buyer name
    vp_rows = db.execute(text("""
        SELECT
            LOWER(TRIM(ot.buyer))             AS buyer,
            p.sku                              AS sku,
            (ot.year_week % 100)               AS week,
            SUM(ot.quantity)::float            AS qty
        FROM on_top_inputs ot
        JOIN dim_products p ON p.id = ot.product_id
        WHERE ot.channel = 'wholesale' AND ot.buyer IS NOT NULL
        GROUP BY LOWER(TRIM(ot.buyer)), p.sku, ot.year_week % 100
    """)).mappings().all()
    vp_buyer_demand: dict[str, dict[tuple[str, int], float]] = {}
    seen_buyers: set[str] = set()
    for r in vp_rows:
        b = r["buyer"]
        if not b:
            continue
        # Pretty-case display: keep first ERP/user-table version
        seen_buyers.add(b)
        vp_buyer_demand.setdefault(b, {})
        vp_buyer_demand[b][(r["sku"], int(r["week"]))] = (
            vp_buyer_demand[b].get((r["sku"], int(r["week"])), 0.0) + float(r["qty"] or 0)
        )
    all_vp_buyers = sorted(seen_buyers)

    suppliers = sorted({r["supplier"] for r in incoming if r["supplier"]})

    return ScenarioData(
        wh_map=wh_map, stores_map=stores_map, cost_map=cost_map, lt_map=lt_map,
        fc_lookup=fc_lookup, avg_map=avg_map,
        incoming=incoming, all_skus=all_skus,
        vp_buyer_demand=vp_buyer_demand, all_vp_buyers=all_vp_buyers,
        suppliers=suppliers, cur_year=cur_year, cur_week=cur_week,
    )


# -------------------------------------------------------------------
# Main orchestration
# -------------------------------------------------------------------

HORIZON = 13


@dataclass
class ScenarioParams:
    suppliers: list[str]
    window_start_cw: int
    window_end_cw: int
    stock_basis: str                       # "wh_only" or "wh_plus_stores"
    postpone_trigger: float
    postpone_delay: int
    cancel_on: bool
    cancel_threshold: float
    target_eur: float
    excluded_buyers: list[str] = field(default_factory=list)
    # Optional per-PO overrides: keyed by "sku|po_year|po_week" → {action, new_cw?}
    overrides: dict[str, dict] = field(default_factory=dict)


def _po_key_str(sku: str, py: int, pw: int) -> str:
    return f"{sku}|{py}|{pw}"


def _resolve_target(po_y: int, po_w: int, new_cw: int, postpone_delay: int) -> tuple[int, int]:
    new_cw = int(new_cw)
    if new_cw == po_w:
        return week_step(po_y, po_w, postpone_delay)
    if new_cw < po_w:
        return (po_y + 1, new_cw)
    return (po_y, new_cw)


def compute_scenario(data: ScenarioData, params: ScenarioParams) -> dict:
    """Return the full scenario response (per-PO rows, KPIs, projection)."""
    cy, cw = data.cur_year, data.cur_week

    # Apply VP-buyer dropout to the demand walk
    adj_fc = dict(data.fc_lookup)
    excluded_norm = {str(b).strip().lower() for b in params.excluded_buyers}
    if excluded_norm:
        excluded_per_skuwk: dict[tuple[str, int], float] = {}
        for b in excluded_norm:
            for (sk, wk), q in data.vp_buyer_demand.get(b, {}).items():
                excluded_per_skuwk[(sk, wk)] = excluded_per_skuwk.get((sk, wk), 0.0) + q
        for k in list(adj_fc.keys()):
            k_sku, k_yw = k
            k_wk = k_yw % 100
            excl = excluded_per_skuwk.get((k_sku, k_wk), 0.0)
            if excl > 0:
                adj_fc[k] = max(0.0, adj_fc[k] - excl)

    # Cover basis (per-SKU stock for cover walks)
    def cover_basis(sku: str) -> float:
        if params.stock_basis == "wh_only":
            return float(data.wh_map.get(sku, 0))
        return float(data.wh_map.get(sku, 0)) + float(data.stores_map.get(sku, 0))
    cover_basis_for = {sku: cover_basis(sku) for sku in data.all_skus}

    # Resolve PO scope
    window_yws = {cy * 100 + w for w in range(int(params.window_start_cw), int(params.window_end_cw) + 1)}
    selected_suppliers = set(params.suppliers)
    in_scope = [
        p for p in data.incoming
        if p["supplier"] in selected_suppliers and (int(p["year"]) * 100 + int(p["week"])) in window_yws
    ]

    walk_weeks = week_series(cy, cw, HORIZON)
    proj_weeks = week_series(cy, cw, 14)
    # Anchor the cash walk to the END of the previous week so that the
    # first row in proj_weeks (= current week) shows the value AFTER
    # this week's inflow/outflow has been applied. Without this offset,
    # Scenario Planner's first chart point sits at the anchor (no
    # movement yet) while Stock Projection's first point is the closing
    # value of the same week — causing a ~€350k cosmetic mismatch.
    py, pw = (cy, cw - 1) if cw > 1 else (cy - 1, 52)
    proj_walk = week_series(py, pw, HORIZON + 6)
    cancel_threshold_eff: Optional[float] = params.cancel_threshold if params.cancel_on else None

    # Classify each in-scope PO
    per_po: list[dict] = []
    for p in in_scope:
        sku = p["sku"]
        py, pw = int(p["year"]), int(p["week"])
        qty = float(p["qty"])
        cost = float(data.cost_map.get(sku, 0.0))
        # Missing lead time → None (MISSING_LT), consistent with supply/finance;
        # do NOT fabricate an 8-week guess. lt only affects the displayed
        # lead_time_wk / cover_vs_lt, not the walk-forward cover math.
        _lt_raw = data.lt_map.get(sku)
        lt = float(_lt_raw) if _lt_raw is not None else None

        series_now = [adj_fc.get((sku, y * 100 + w), 0.0) for (y, w) in week_series(cy, cw, HORIZON)]
        cover_now = real_weeks_cover(cover_basis(sku), series_now)

        series_post = [adj_fc.get((sku, y * 100 + w), 0.0) for (y, w) in week_series(py, pw, HORIZON)]
        cover_post = real_weeks_cover(cover_basis(sku) + qty, series_post)

        action_raw = classify(cover_now, cover_post, cancel_threshold_eff, params.postpone_trigger)
        ny, nw = week_step(py, pw, params.postpone_delay)
        per_po.append({
            "sku": sku,
            "name": p["name"],
            "tier": p["tier"],
            "category": p["category"],
            "supplier": p["supplier"],
            "po_year": py, "po_week": pw,
            "po_label": f"CW{pw}",
            "qty": int(qty),
            "cost_price": round(cost, 2),
            "eur_value": round(qty * cost),
            "lead_time_wk": round(lt, 1) if lt is not None else None,
            "cover_now_wk": round(cover_now, 1),
            "cover_vs_lt": round(cover_now - lt, 1) if lt is not None else None,
            "cover_post_delivery_wk": round(cover_post, 1),
            "action_raw": action_raw,
            "auto_new_week": f"CW{nw}",
        })

    # Sanity check
    adjusted, flips = sanity_check(
        per_po=per_po,
        all_pos=data.incoming,
        fc_lookup=adj_fc,
        wh_map=data.wh_map,
        walk_weeks=walk_weeks,
        postpone_delay=params.postpone_delay,
    )
    for i, r in enumerate(per_po):
        r["auto_action"] = adjusted[i]

    # Resolve effective actions (auto vs override)
    in_scope_keys = {(r["sku"], int(r["po_year"]), int(r["po_week"])) for r in per_po}
    eff_actions: dict[tuple[str, int, int], str] = {}
    override_targets: dict[tuple[str, int, int], tuple[int, int]] = {}
    for r in per_po:
        sku, py, pw = r["sku"], int(r["po_year"]), int(r["po_week"])
        key_str = _po_key_str(sku, py, pw)
        ov = params.overrides.get(key_str)
        if not ov or ov.get("action", "AUTO") == "AUTO":
            eff_actions[(sku, py, pw)] = r["auto_action"]
            continue
        act = str(ov["action"]).upper()
        eff_actions[(sku, py, pw)] = act
        if act == "POSTPONE":
            new_cw = ov.get("new_cw")
            if new_cw is None:
                override_targets[(sku, py, pw)] = week_step(py, pw, params.postpone_delay)
            else:
                override_targets[(sku, py, pw)] = _resolve_target(py, pw, int(new_cw), params.postpone_delay)

    # Cover after arrival (multi-PO stacking aware)
    cover_after = cover_after_arrival_map(
        per_po=per_po, all_pos=data.incoming,
        eff_actions=eff_actions, override_targets=override_targets,
        in_scope_keys=in_scope_keys,
        cur_y=cy, cur_w=cw,
        cover_walk_horizon=HORIZON + 10, horizon=HORIZON,
        fc_lookup=adj_fc, cover_basis_for=cover_basis_for,
        postpone_delay=params.postpone_delay,
    )

    # Stamp effective action + new_delivery_week + cover_after_arrival onto rows
    for i, r in enumerate(per_po):
        key = (r["sku"], int(r["po_year"]), int(r["po_week"]))
        act = eff_actions[key]
        if key in override_targets:
            _, nw = override_targets[key]
            new_wk_label = f"CW{nw}"
        elif act == "CANCEL":
            new_wk_label = ""
        elif act == "POSTPONE":
            new_wk_label = r["auto_new_week"]
        else:
            new_wk_label = r["po_label"]
        r["action"] = act
        r["new_delivery_week"] = new_wk_label
        r["is_override"] = _po_key_str(r["sku"], int(r["po_year"]), int(r["po_week"])) in params.overrides \
                           and params.overrides[_po_key_str(r["sku"], int(r["po_year"]), int(r["po_week"]))].get("action", "AUTO") != "AUTO"
        r["cover_after_arrival_wk"] = cover_after[i]

    # Cash projection — baseline (no actions) vs scenario (with actions).
    # Per-SKU walk for parity with Stock Projection chart (see
    # company_eur_projection docstring).
    start_stock: dict[str, float] = {
        s: float(data.wh_map.get(s, 0.0) + data.stores_map.get(s, 0.0))
        for s in (set(data.wh_map) | set(data.stores_map))
    }
    scen_actions = {(r["sku"], int(r["po_year"]), int(r["po_week"])): r["action"] for r in per_po}
    baseline_proj = company_eur_projection(
        all_pos=data.incoming, scenario_actions={}, cost_map=data.cost_map,
        fc_lookup=adj_fc, outflow_skus=data.all_skus, walk_weeks=proj_walk,
        start_stock=start_stock, postpone_delay=params.postpone_delay,
        avg_map=data.avg_map,
    )
    scenario_proj = company_eur_projection(
        all_pos=data.incoming, scenario_actions=scen_actions, cost_map=data.cost_map,
        fc_lookup=adj_fc, outflow_skus=data.all_skus, walk_weeks=proj_walk,
        start_stock=start_stock, postpone_delay=params.postpone_delay,
        postpone_targets=override_targets,
        avg_map=data.avg_map,
    )

    # KPIs
    proj_keys = proj_weeks
    base_peak_yw = max(proj_keys, key=lambda k: baseline_proj.get(k, 0)) if proj_keys else (cy, cw)
    base_peak = baseline_proj.get(base_peak_yw, 0.0)
    scen_peak_yw = max(proj_keys, key=lambda k: scenario_proj.get(k, 0)) if proj_keys else (cy, cw)
    scen_peak = scenario_proj.get(scen_peak_yw, 0.0)
    cancel_eur = sum(r["eur_value"] for r in per_po if r["action"] == "CANCEL")
    postpone_eur = sum(r["eur_value"] for r in per_po if r["action"] == "POSTPONE")

    # Supplier-plan overlay (read-only): per-week €NV from the buying team's
    # template workbook, painted on the chart from CW29/2026 onward where
    # per-SKU ABC POs in incoming_supply tail off. Doesn't enter the
    # scenario math — just paints the planned arrivals on the same axes.
    from backend.services.supplier_plan_template import planned_incoming_eur
    chart = []
    for (y, w) in proj_keys:
        chart.append({
            "cw_label": f"CW{w}",
            "year": y, "week": w,
            "baseline_eur": baseline_proj.get((y, w), 0.0),
            "scenario_eur": scenario_proj.get((y, w), 0.0),
            "planned_incoming_eur": planned_incoming_eur(y, w),
        })

    excluded_demand_eur = 0.0
    if excluded_norm:
        proj_yw_set = {y * 100 + w for (y, w) in proj_walk}
        for b in excluded_norm:
            for (sk, wk_n), q in data.vp_buyer_demand.get(b, {}).items():
                for (k_sku, k_yw) in data.fc_lookup:
                    if k_sku == sk and (k_yw % 100) == wk_n and k_yw in proj_yw_set:
                        excluded_demand_eur += q * data.cost_map.get(sk, 0.0)
                        break

    return {
        "meta": {
            "current_year": cy,
            "current_week": cw,
            "suppliers": data.suppliers,
            "vp_buyers": data.all_vp_buyers,
            "horizon": HORIZON,
        },
        "per_po": per_po,
        "chart": chart,
        "kpi": {
            "baseline_peak_eur": round(base_peak),
            "baseline_peak_cw": f"CW{base_peak_yw[1]}",
            "scenario_peak_eur": round(scen_peak),
            "scenario_peak_cw": f"CW{scen_peak_yw[1]}",
            "delta_eur": round(scen_peak - base_peak),
            "cancel_eur": round(cancel_eur),
            "postpone_eur": round(postpone_eur),
            "total_unlocked_eur": round(cancel_eur + postpone_eur),
            "under_target": scen_peak < params.target_eur,
            "target_eur": round(params.target_eur),
            "pos_affected": sum(1 for r in per_po if r["action"] in ("CANCEL", "POSTPONE")),
            "n_cancel": sum(1 for r in per_po if r["action"] == "CANCEL"),
            "n_postpone": sum(1 for r in per_po if r["action"] == "POSTPONE"),
            "n_produce": sum(1 for r in per_po if r["action"] == "PRODUCE"),
        },
        "flips": [{"sku": s, "po_year": y, "po_week": w} for (s, y, w) in flips],
        "excluded_demand_eur": round(excluded_demand_eur),
    }


# -------------------------------------------------------------------
# Excel export (supplier-ready CANCEL / POSTPONE lists)
# -------------------------------------------------------------------

def build_supplier_xlsx(per_po: list[dict], action_label: str) -> Optional[bytes]:
    sub = [r for r in per_po if r["action"] == action_label]
    if not sub:
        return None
    sub_sorted = sorted(sub, key=lambda r: (r["supplier"], r["tier"], -r["eur_value"]))

    import openpyxl
    from openpyxl import Workbook
    wb = Workbook()
    ws = wb.active
    ws.title = action_label
    headers = ["sku", "name", "tier", "supplier", "original_delivery_week",
               "qty", "cost_price", "eur_value", "action",
               "new_delivery_week_or_blank", "override", "our_notes"]
    ws.append(headers)
    for r in sub_sorted:
        new_wk = r["new_delivery_week"] if r["action"] == "POSTPONE" else ""
        override = "manual" if r.get("is_override") else "auto"
        ws.append([
            r["sku"], r.get("name", ""), r.get("tier", ""), r["supplier"],
            r["po_label"], r["qty"], r["cost_price"], r["eur_value"],
            r["action"], new_wk, override, "",
        ])
    buf = io.BytesIO()
    wb.save(buf)
    return buf.getvalue()