"""
ABC PO cancellation analysis.

Rules locked with Lovro:
  - PO window: CW22-CW27 2026 only (skip CW19-CW21 = locked/in-transit)
  - Stock floor: project stock without the PO must stay >= 2 x avg forward
    weekly demand at every week from the PO's delivery week through end of
    horizon. Safety stock parameter ignored on purpose (aggressive cut).
  - Stock view: WH + stores (HR/AT/SLO) combined for the "company stock" picture.
  - Today: CW20 2026 (2026-05-14).

Output:
  - _abc_cancel_candidates.csv  (per-PO row with safe-to-cancel flag + reason)
  - _abc_cancel_by_sku.csv      (per-SKU rollup including cumulative cancel)
  - _abc_cancel_summary.txt     (top-line numbers for the prompt)
"""

import pandas as pd
import numpy as np

CURRENT_YEAR = 2026
CURRENT_WEEK = 20
PO_WINDOW = [(2026, w) for w in range(22, 28)]   # CW22..CW27 inclusive
HORIZON_END = (2026, 40)                          # walk well past CW27
FLOOR_WEEKS = 2                                   # >= 2w forward demand
FWD_AVG_WEEKS = 8                                 # how many weeks to avg for forward demand

# ---------- load ----------
inc = pd.read_csv("data/incoming_supply.csv")
sup = pd.read_csv("data/supply_master.csv")
stock_wh = pd.read_csv("data/stock.csv")
stores = []
for f in ["stock_stores.csv", "stock_stores_at.csv", "stock_stores_slo.csv"]:
    s = pd.read_csv(f"data/{f}")
    s.columns = [c.lower() for c in s.columns]
    stores.append(s[["sku", "on_hand"]])
stores_df = pd.concat(stores, ignore_index=True).groupby("sku", as_index=False)["on_hand"].sum()
fc = pd.read_csv("data/forecast_for_supply.csv")
costs = pd.read_csv("data/sku_costs.csv")
plan = pd.read_csv("data/sku_plan_list.csv")
plan.columns = [c.lower() for c in plan.columns]

# normalise
cost_map = dict(zip(costs["sku"], costs["cost_price"]))
plan = plan.rename(columns={"cat": "category", "oznaka": "tier"})
plan_min = plan[["sku", "category", "tier"]].copy()

# combined opening stock (WH + stores)
opening = pd.concat([stock_wh[["sku", "on_hand"]], stores_df], ignore_index=True)
opening = opening.groupby("sku", as_index=False)["on_hand"].sum()
on_hand_map = dict(zip(opening["sku"], opening["on_hand"]))

# ABC supplier mask
sup["is_abc"] = sup["supplier"].fillna("").str.contains("ABC NUTRITIONAL", case=False)
abc_skus = set(sup[sup["is_abc"]]["sku"])

# ABC incoming
inc_m = inc.merge(sup[["sku", "supplier", "is_abc"]], on="sku", how="left")
inc_m["is_abc"] = inc_m["is_abc"].fillna(False).astype(bool)
inc_m["yw"] = inc_m["year"] * 100 + inc_m["week"]
abc_inc = inc_m[inc_m["is_abc"]].copy()

# all incoming (for accurate roll-forward including non-cancellable POs)
all_inc = inc.copy()

# ---------- helper: walk stock forward ----------
def yw(y, w):
    return y * 100 + w

def week_iter(start_y, start_w, end_y, end_w):
    y, w = start_y, start_w
    while (y, w) <= (end_y, end_w):
        yield (y, w)
        w += 1
        if w > 52:
            w = 1
            y += 1

fc["yw"] = fc["year"] * 100 + fc["week"]
fc_lookup = {(r.sku, r.yw): r.demand for r in fc.itertuples()}

all_inc["yw"] = all_inc["year"] * 100 + all_inc["week"]
inc_lookup_full = {}
for r in all_inc.itertuples():
    inc_lookup_full[(r.sku, r.yw)] = inc_lookup_full.get((r.sku, r.yw), 0) + r.qty

def project_stock(sku, cancelled_pos):
    """Roll opening stock forward from current week to HORIZON_END.
    cancelled_pos = set of (sku, yw) tuples whose qty is removed.
    Returns dict {yw: closing_stock_after_that_week}.
    """
    s = float(on_hand_map.get(sku, 0))
    out = {}
    for (yy, ww) in week_iter(CURRENT_YEAR, CURRENT_WEEK, *HORIZON_END):
        key = (sku, yw(yy, ww))
        d = float(fc_lookup.get(key, 0))
        inc_q = float(inc_lookup_full.get(key, 0))
        if key in cancelled_pos:
            inc_q -= float(inc_lookup_full.get(key, 0))  # net to zero for that week's cancelled PO
        s = max(0.0, s + inc_q - d)
        out[yw(yy, ww)] = s
    return out

def avg_fwd_demand(sku, from_y, from_w, n_weeks=FWD_AVG_WEEKS):
    weeks = []
    y, w = from_y, from_w
    for _ in range(n_weeks):
        weeks.append(fc_lookup.get((sku, yw(y, w)), 0))
        w += 1
        if w > 52:
            w = 1
            y += 1
    return float(np.mean(weeks)) if weeks else 0.0

# ---------- per-PO evaluation ----------
window_yw = {yw(y, w) for (y, w) in PO_WINDOW}
abc_window = abc_inc[abc_inc["yw"].isin(window_yw)].copy()

rows = []
for _, po in abc_window.iterrows():
    sku = po["sku"]
    po_yw = int(po["yw"])
    po_y = po_yw // 100
    po_w = po_yw % 100
    qty = float(po["qty"])

    fwd_avg = avg_fwd_demand(sku, po_y, po_w)
    floor = FLOOR_WEEKS * fwd_avg

    # baseline projection (all POs intact)
    base = project_stock(sku, set())
    # projection with this single PO cancelled
    cut = project_stock(sku, {(sku, po_yw)})

    # min projected stock from PO delivery week onward
    weeks_from_po = [w for w in cut.keys() if w >= po_yw]
    min_after = min(cut[w] for w in weeks_from_po) if weeks_from_po else 0.0
    base_min_after = min(base[w] for w in weeks_from_po) if weeks_from_po else 0.0

    safe = min_after >= floor and fwd_avg >= 0  # fwd_avg=0 means no demand → trivially safe

    # special case: if fwd_avg == 0 (dead SKU) it's automatically a safe cancel
    no_demand = fwd_avg == 0

    cost = cost_map.get(sku, 0.0)
    eur = qty * cost

    rows.append({
        "sku": sku,
        "po_year": po_y,
        "po_week": po_w,
        "po_label": f"CW{po_w}",
        "qty": int(qty),
        "cost_price": round(cost, 2),
        "eur_unlock": round(eur, 0),
        "opening_now_combined": int(on_hand_map.get(sku, 0)),
        "fwd_avg_weekly_demand": round(fwd_avg, 1),
        "floor_units_2w": round(floor, 0),
        "base_min_stock_from_delivery": int(round(base_min_after)),
        "cut_min_stock_from_delivery": int(round(min_after)),
        "safe_to_cancel": bool(safe),
        "no_demand": bool(no_demand),
    })

per_po = pd.DataFrame(rows)

# attach tier/category
per_po = per_po.merge(plan_min, on="sku", how="left")

# ---------- per-SKU cumulative cancel (if SKU has multiple POs) ----------
sku_rows = []
for sku, g in per_po.groupby("sku"):
    g_sorted = g.sort_values(["po_year", "po_week"])
    cumulative_cancel = set()
    cum_safe_qty = 0
    cum_safe_eur = 0.0
    cum_po_count = 0
    for _, po in g_sorted.iterrows():
        po_yw_v = po["po_year"] * 100 + po["po_week"]
        trial = cumulative_cancel | {(sku, po_yw_v)}
        proj = project_stock(sku, trial)
        weeks_from_po = [w for w in proj.keys() if w >= po_yw_v]
        min_after = min(proj[w] for w in weeks_from_po) if weeks_from_po else 0.0
        fwd = avg_fwd_demand(sku, po["po_year"], po["po_week"])
        if (fwd == 0) or (min_after >= FLOOR_WEEKS * fwd):
            cumulative_cancel = trial
            cum_safe_qty += int(po["qty"])
            cum_safe_eur += float(po["eur_unlock"])
            cum_po_count += 1

    base_proj = project_stock(sku, set())
    full_cancel = project_stock(sku, {(sku, p["po_year"] * 100 + p["po_week"]) for _, p in g_sorted.iterrows()})
    sku_rows.append({
        "sku": sku,
        "tier": g["tier"].iloc[0],
        "category": g["category"].iloc[0],
        "opening_now_combined": int(on_hand_map.get(sku, 0)),
        "fwd_avg_weekly_demand": round(avg_fwd_demand(sku, 2026, 22), 1),
        "total_po_count": int(len(g)),
        "total_po_qty": int(g["qty"].sum()),
        "total_po_eur": round(float(g["eur_unlock"].sum()), 0),
        "single_safe_po_count": int(g["safe_to_cancel"].sum()),
        "single_safe_qty": int(g[g["safe_to_cancel"]]["qty"].sum()),
        "single_safe_eur": round(float(g[g["safe_to_cancel"]]["eur_unlock"].sum()), 0),
        "cumulative_safe_po_count": cum_po_count,
        "cumulative_safe_qty": cum_safe_qty,
        "cumulative_safe_eur": round(cum_safe_eur, 0),
    })

per_sku = pd.DataFrame(sku_rows).sort_values("cumulative_safe_eur", ascending=False)

# ---------- write outputs ----------
per_po_out = per_po.sort_values(["safe_to_cancel", "eur_unlock"], ascending=[False, False])
per_po_out.to_csv("_abc_cancel_candidates.csv", index=False)
per_sku.to_csv("_abc_cancel_by_sku.csv", index=False)

# ---------- summary ----------
total_abc_eur = float((abc_window["qty"] * abc_window["sku"].map(cost_map).fillna(0)).sum())
safe_eur_single = float(per_po[per_po["safe_to_cancel"]]["eur_unlock"].sum())
safe_eur_cum = float(per_sku["cumulative_safe_eur"].sum())
total_qty = int(abc_window["qty"].sum())
safe_qty_single = int(per_po[per_po["safe_to_cancel"]]["qty"].sum())
safe_qty_cum = int(per_sku["cumulative_safe_qty"].sum())

by_tier = per_sku.groupby("tier").agg(
    skus=("sku", "count"),
    cum_qty=("cumulative_safe_qty", "sum"),
    cum_eur=("cumulative_safe_eur", "sum"),
).sort_values("cum_eur", ascending=False)

by_week = per_po.groupby(["po_label", "safe_to_cancel"]).agg(
    skus=("sku", "nunique"),
    qty=("qty", "sum"),
    eur=("eur_unlock", "sum"),
).round(0)

# dead-demand SKUs (no forward sales) — these are the easiest cancels
no_dem = per_po[per_po["no_demand"]]
top_eur_safe = per_po[per_po["safe_to_cancel"]].sort_values("eur_unlock", ascending=False).head(20)

summary_lines = []
summary_lines.append("=" * 72)
summary_lines.append("ABC PO CANCELLATION ANALYSIS - SUMMARY")
summary_lines.append(f"As of CW{CURRENT_WEEK}/{CURRENT_YEAR}. Window: CW22-CW27 2026.")
summary_lines.append(f"Floor: stock without PO >= {FLOOR_WEEKS}w forward demand.")
summary_lines.append(f"Stock view: WH + stores HR/AT/SLO combined.")
summary_lines.append("=" * 72)
summary_lines.append("")
summary_lines.append(f"ABC PO universe in window: {len(abc_window)} PO rows, "
                     f"{abc_window['sku'].nunique()} SKUs, "
                     f"{total_qty:,} units, EUR {total_abc_eur:,.0f}")
summary_lines.append("")
summary_lines.append(f"SAFE to cancel (each PO evaluated standalone):")
summary_lines.append(f"  POs: {int(per_po['safe_to_cancel'].sum())} of {len(abc_window)}")
summary_lines.append(f"  Qty: {safe_qty_single:,} units")
summary_lines.append(f"  EUR unlock: {safe_eur_single:,.0f}")
summary_lines.append("")
summary_lines.append(f"SAFE to cancel (cumulative per SKU, greedy earliest-first):")
summary_lines.append(f"  Qty: {safe_qty_cum:,} units")
summary_lines.append(f"  EUR unlock: {safe_eur_cum:,.0f}")
summary_lines.append("")
summary_lines.append(f"Dead-demand SKUs (no forward forecast): {no_dem['sku'].nunique()} SKUs, "
                     f"{int(no_dem['qty'].sum()):,} units, EUR {float(no_dem['eur_unlock'].sum()):,.0f}")
summary_lines.append("")
summary_lines.append("By PO delivery week:")
summary_lines.append(by_week.to_string())
summary_lines.append("")
summary_lines.append("By tier (cumulative safe cancel):")
summary_lines.append(by_tier.to_string())
summary_lines.append("")
summary_lines.append("Top 20 safe-cancel POs by EUR:")
cols = ["sku", "tier", "category", "po_label", "qty", "cost_price", "eur_unlock",
        "opening_now_combined", "fwd_avg_weekly_demand", "cut_min_stock_from_delivery"]
summary_lines.append(top_eur_safe[cols].to_string(index=False))

txt = "\n".join(summary_lines)
with open("_abc_cancel_summary.txt", "w", encoding="utf-8") as f:
    f.write(txt)

print(txt)