"""
Polleo — SCM Director Action Plan
==================================

Builds the management-facing follow-up to polleo_stock_analysis_*.xlsx:
  * Cost of inaction (capital + shrinkage + opportunity, NOT warehouse rent)
  * 3 execution scenarios (A Conservative / B Aggressive / C Hybrid)
  * Side-by-side comparison
  * Implementation roadmap + KPI targets + root-cause narrative

Inputs:
  * polleo_stock_analysis_<today>.xlsx  → Raw Data sheet (per-SKU metrics)
Output:
  * polleo_scm_action_plan_<today>.xlsx (8 sheets, charts on selected sheets)

Key assumptions (all surfaced in the workbook):
  * Warehouse rent €18,750.69/mo is FIXED — excluded from "holding cost
    savings" because clearing stock does not reduce the invoice. It is
    mentioned only as a growth-constraint argument.
  * DSO = DPO = 60 days → CCC = DIO.
  * WACC 8%/yr on tied-up capital.
  * Shrinkage rates per perishability class (annual):
      HIGH perishable (proteini, BIO, RTD, sportska prehrana) — 15%
      CLOTHING (ODJEĆA I OBUĆA)                               — 5%
      LOW perishable (everything else)                        — 3%
  * Clothing fashion-depreciation: 4% per month compounded
    (used only for the depreciation-curve table on Sheet 1).
"""
from __future__ import annotations

import sys
from datetime import datetime, date
from pathlib import Path

import pandas as pd
import numpy as np

if hasattr(sys.stdout, "reconfigure"):
    sys.stdout.reconfigure(encoding="utf-8")

ROOT     = Path(__file__).resolve().parent
TODAY    = date.today()
INPUT_X  = ROOT / f"polleo_stock_analysis_{TODAY:%Y%m%d}.xlsx"
OUTPUT_X = ROOT / f"polleo_scm_action_plan_{TODAY:%Y%m%d}.xlsx"

# --- assumptions ----------------------------------------------------------
WACC               = 0.08
WAREHOUSE_RENT_MO  = 18_750.69          # fixed, NOT used in savings math
DSO                = 60
DPO                = 60                 # CCC = DIO
CLOTHING_DEPREC_MO = 0.04               # monthly fashion-obsolescence

SHRINKAGE = {
    "HIGH":     0.15,
    "CLOTHING": 0.05,
    "LOW":      0.03,
}

HIGH_CATS = {"PROTEINI", "BIO I SUPERFOODS", "RTD & SNACKS",
             "SPORTSKA PREHRANA"}
CLOTHING_CATS = {"ODJEĆA I OBUĆA"}

HEALTH_ORDER = ["STAR", "HEALTHY", "SLOW", "DEAD"]
HEALTH_FILL = {
    "STAR":    "C6EFCE",
    "HEALTHY": "DDEBF7",
    "SLOW":    "FFE699",
    "DEAD":    "F8CBAD",
}


def classify_perishability(category: str) -> str:
    if category in HIGH_CATS:
        return "HIGH"
    if category in CLOTHING_CATS:
        return "CLOTHING"
    return "LOW"


# ---------------------------------------------------------------------------
# Load data
# ---------------------------------------------------------------------------
def load_raw_data() -> pd.DataFrame:
    if not INPUT_X.exists():
        print(f"ERROR: input not found: {INPUT_X}", file=sys.stderr)
        sys.exit(1)
    df = pd.read_excel(INPUT_X, sheet_name="Raw Data", header=2)
    df["perishability"] = df["category"].apply(classify_perishability)
    df["shrinkage_rate"] = df["perishability"].map(SHRINKAGE)
    return df


# ---------------------------------------------------------------------------
# Compute aggregates
# ---------------------------------------------------------------------------
def compute_metrics(df: pd.DataFrame) -> dict:
    total_stock = float(df["stock_value"].sum())
    annual_margin_total = float(df["annual_margin_eur"].sum())

    # health-bucket value + margin
    bucket = (
        df.groupby("health")
          .agg(stock_value=("stock_value", "sum"),
               annual_margin=("annual_margin_eur", "sum"),
               n_skus=("sku", "count"))
          .reindex(HEALTH_ORDER)
          .fillna(0)
    )
    bucket["yield_eur_per_eur"] = bucket.apply(
        lambda r: r["annual_margin"] / r["stock_value"]
        if r["stock_value"] > 0 else 0,
        axis=1,
    )

    # Working vs trapped
    working = df[df["health"].isin(["STAR", "HEALTHY"])]
    trapped = df[df["health"].isin(["SLOW", "DEAD"])]
    working_val  = float(working["stock_value"].sum())
    trapped_val  = float(trapped["stock_value"].sum())
    working_marg = float(working["annual_margin_eur"].sum())
    trapped_marg = float(trapped["annual_margin_eur"].sum())
    working_yield = working_marg / working_val if working_val else 0
    trapped_yield = trapped_marg / trapped_val if trapped_val else 0
    overall_yield = annual_margin_total / total_stock if total_stock else 0
    yield_gap = working_yield - trapped_yield
    opportunity_cost_annual = trapped_val * yield_gap

    # Per (health, perishability) value table — drives bucket-level holding cost
    df["capital_cost_annual"]   = df["stock_value"] * WACC
    df["shrinkage_cost_annual"] = df["stock_value"] * df["shrinkage_rate"]
    df["variable_holding_annual"] = (
        df["capital_cost_annual"] + df["shrinkage_cost_annual"]
    )
    df["variable_holding_monthly"] = df["variable_holding_annual"] / 12

    bucket_costs = (
        df.groupby("health")
          .agg(stock_value=("stock_value", "sum"),
               capital_cost=("capital_cost_annual", "sum"),
               shrinkage_cost=("shrinkage_cost_annual", "sum"),
               total_variable_annual=("variable_holding_annual", "sum"),
               total_variable_monthly=("variable_holding_monthly", "sum"))
          .reindex(HEALTH_ORDER)
          .fillna(0)
    )

    # Per category top 5 by variable monthly holding cost
    cat_costs = (
        df.groupby("category")
          .agg(stock_value=("stock_value", "sum"),
               variable_monthly=("variable_holding_monthly", "sum"))
          .sort_values("variable_monthly", ascending=False)
    )
    top_cat_costs = cat_costs.head(5)

    # DEAD breakdown by perishability — used by scenario math
    dead = df[df["health"] == "DEAD"]
    dead_clothing   = float(dead.loc[dead["perishability"] == "CLOTHING", "stock_value"].sum())
    dead_perishable = float(dead.loc[dead["perishability"] == "HIGH",     "stock_value"].sum())
    dead_other      = float(dead.loc[dead["perishability"] == "LOW",      "stock_value"].sum())
    slow_value      = float(df.loc[df["health"] == "SLOW", "stock_value"].sum())

    # Clothing depreciation curve (cost-value erosion over time)
    clothing_total = float(
        df.loc[df["perishability"] == "CLOTHING", "stock_value"].sum()
    )
    clothing_dead_total = dead_clothing
    months = [0, 3, 6, 9, 12]
    depreciation_curve = pd.DataFrame({
        "months_from_now":     months,
        "clothing_total_eur":  [clothing_total * (1 - CLOTHING_DEPREC_MO) ** m for m in months],
        "clothing_dead_eur":   [clothing_dead_total * (1 - CLOTHING_DEPREC_MO) ** m for m in months],
    })

    return {
        "df":                  df,
        "total_stock":         total_stock,
        "annual_margin_total": annual_margin_total,
        "bucket":              bucket,
        "bucket_costs":        bucket_costs,
        "top_cat_costs":       top_cat_costs,
        "cat_costs":           cat_costs,
        "working_val":         working_val,
        "trapped_val":         trapped_val,
        "working_marg":        working_marg,
        "trapped_marg":        trapped_marg,
        "working_yield":       working_yield,
        "trapped_yield":       trapped_yield,
        "overall_yield":       overall_yield,
        "yield_gap":           yield_gap,
        "opportunity_annual":  opportunity_cost_annual,
        "dead_clothing":       dead_clothing,
        "dead_perishable":     dead_perishable,
        "dead_other":          dead_other,
        "slow_value":          slow_value,
        "depreciation_curve":  depreciation_curve,
        "clothing_total":      clothing_total,
    }


# ---------------------------------------------------------------------------
# Scenario math
# ---------------------------------------------------------------------------
def build_scenario_a(m: dict) -> dict:
    """Conservative — 6 months, low risk."""
    rec = {
        "clothing":     {"value": m["dead_clothing"],   "recovery": 0.25},
        "perishable":   {"value": m["dead_perishable"], "recovery": 0.40},
        "other":        {"value": m["dead_other"],      "recovery": 0.35},
    }
    revenue = sum(v["value"] * v["recovery"] for v in rec.values())
    writeoff = sum(v["value"] * (1 - v["recovery"]) for v in rec.values())
    slow_organic_reduction = 300_000.0
    stock_reduction = sum(v["value"] for v in rec.values()) + slow_organic_reduction
    new_stock = m["total_stock"] - stock_reduction
    # holding cost savings: WACC + avg shrinkage on the freed stock
    freed_capital = stock_reduction
    # Approximate shrinkage using a blended rate (8% — mid between HIGH/CLOTH/LOW)
    blended_shrink = 0.08
    annual_savings = freed_capital * (WACC + blended_shrink)
    monthly_savings = annual_savings / 12
    return {
        "name":             "A — Conservative",
        "subtitle":         "Kontrolirani outlet + stopiranje nabave",
        "horizon_months":   6,
        "rec_table":        rec,
        "revenue":          revenue,
        "writeoff":         writeoff,
        "tax_benefit":      0.0,
        "stock_reduction":  stock_reduction,
        "new_stock":        new_stock,
        "annual_savings":   annual_savings,
        "monthly_savings":  monthly_savings,
        "reinvest_capital": 0.0,
        "reinvest_margin":  0.0,
        "net_12m_pnl":      -writeoff + annual_savings,
        "dio_target":       50,
        "risk":             "LOW",
    }


def build_scenario_b(m: dict) -> dict:
    """Aggressive — 3 months, fastest cash recovery, larger write-off."""
    dead_total = m["dead_clothing"] + m["dead_perishable"] + m["dead_other"]
    dead_recovery = 0.20
    dead_revenue = dead_total * dead_recovery
    dead_writeoff = dead_total * (1 - dead_recovery)
    tax_benefit = dead_total * 0.30 * 0.18   # 30% donated, 18% tax rate

    slow_recovery = 0.60
    slow_target_reduction_value = m["slow_value"] * 0.50
    slow_revenue = slow_target_reduction_value * slow_recovery
    slow_writeoff = slow_target_reduction_value * (1 - slow_recovery)
    # Per prompt: "extra revenue above organic" implied; use the full revenue figure
    slow_extra_revenue = 350_000.0  # explicit prompt figure
    stock_reduction = dead_total + slow_target_reduction_value
    new_stock = m["total_stock"] - stock_reduction
    reinvest_capital = 500_000.0
    reinvest_margin = reinvest_capital * m["working_yield"]

    freed_capital = stock_reduction
    blended_shrink = 0.08
    annual_savings = freed_capital * (WACC + blended_shrink)
    monthly_savings = annual_savings / 12

    revenue_total = dead_revenue + slow_extra_revenue
    writeoff_total = dead_writeoff

    return {
        "name":             "B — Aggressive",
        "subtitle":         "Brza likvidacija + reinvestiranje",
        "horizon_months":   3,
        "revenue":          revenue_total,
        "writeoff":         writeoff_total,
        "tax_benefit":      tax_benefit,
        "stock_reduction":  stock_reduction,
        "new_stock":        new_stock,
        "annual_savings":   annual_savings,
        "monthly_savings":  monthly_savings,
        "reinvest_capital": reinvest_capital,
        "reinvest_margin":  reinvest_margin,
        "net_12m_pnl":      (-writeoff_total + annual_savings
                             + reinvest_margin + tax_benefit),
        "dio_target":       45,
        "risk":             "MEDIUM",
        "dead_revenue":     dead_revenue,
        "dead_writeoff":    dead_writeoff,
        "slow_extra_revenue": slow_extra_revenue,
    }


def build_scenario_c(m: dict) -> dict:
    """Hybrid (recommended) — 4 months execution + structural reform."""
    # Perishable: 30% urgent (rok<3mj), 70% controlled (rok>3mj)
    perish_urgent_value     = m["dead_perishable"] * 0.30
    perish_urgent_revenue   = perish_urgent_value * 0.15
    perish_urgent_writeoff  = perish_urgent_value * 0.85

    perish_ctrl_value       = m["dead_perishable"] * 0.70
    perish_ctrl_revenue     = perish_ctrl_value * 0.40
    perish_ctrl_writeoff    = perish_ctrl_value * 0.60

    clothing_value          = m["dead_clothing"]
    clothing_recovery       = 0.18
    clothing_revenue        = clothing_value * clothing_recovery
    clothing_writeoff       = clothing_value * (1 - clothing_recovery)
    clothing_tax_benefit    = clothing_writeoff * 0.50 * 0.18   # ~half donated

    other_value             = m["dead_other"]
    other_recovery          = 0.30
    other_revenue           = other_value * other_recovery
    other_writeoff          = other_value * (1 - other_recovery)

    slow_organic_reduction  = 400_000.0
    revenue_total           = (perish_urgent_revenue + perish_ctrl_revenue
                                + clothing_revenue + other_revenue)
    writeoff_total          = (perish_urgent_writeoff + perish_ctrl_writeoff
                                + clothing_writeoff + other_writeoff)
    tax_benefit             = clothing_tax_benefit

    stock_reduction         = (m["dead_clothing"] + m["dead_perishable"]
                                + m["dead_other"] + slow_organic_reduction)
    new_stock               = m["total_stock"] - stock_reduction

    reinvest_capital        = 400_000.0
    # Sligthly discounted vs Scenario B (more conservative)
    reinvest_margin         = reinvest_capital * m["working_yield"] * 0.85

    freed_capital           = stock_reduction
    blended_shrink          = 0.08
    annual_savings          = freed_capital * (WACC + blended_shrink)
    monthly_savings         = annual_savings / 12

    return {
        "name":             "C — Hybrid (RECOMMENDED)",
        "subtitle":         "Prioritetna likvidacija + strukturna reforma",
        "horizon_months":   4,
        "revenue":          revenue_total,
        "writeoff":         writeoff_total,
        "tax_benefit":      tax_benefit,
        "stock_reduction":  stock_reduction,
        "new_stock":        new_stock,
        "annual_savings":   annual_savings,
        "monthly_savings":  monthly_savings,
        "reinvest_capital": reinvest_capital,
        "reinvest_margin":  reinvest_margin,
        "net_12m_pnl":      (-writeoff_total + annual_savings
                              + reinvest_margin + tax_benefit),
        "dio_target":       55,
        "risk":             "LOW-MEDIUM",
        "components": {
            "perish_urgent":  (perish_urgent_value, perish_urgent_revenue, perish_urgent_writeoff),
            "perish_ctrl":    (perish_ctrl_value, perish_ctrl_revenue, perish_ctrl_writeoff),
            "clothing":       (clothing_value, clothing_revenue, clothing_writeoff),
            "other":          (other_value, other_revenue, other_writeoff),
            "slow_organic":   (slow_organic_reduction, 0, 0),
        },
    }


# ---------------------------------------------------------------------------
# Excel
# ---------------------------------------------------------------------------
def fmt_eur(x):
    try:
        return f"€{x:,.0f}"
    except Exception:
        return str(x)


def write_excel(m: dict, sA: dict, sB: dict, sC: dict, out: Path):
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
    from openpyxl.utils import get_column_letter
    from openpyxl.formatting.rule import CellIsRule
    from openpyxl.chart import BarChart, LineChart, Reference

    wb = Workbook()

    H1 = Font(bold=True, size=14, color="FFFFFF")
    H2 = Font(bold=True, size=11, color="FFFFFF")
    H3 = Font(bold=True, size=11)
    FILL_DARK = PatternFill("solid", fgColor="1F4E79")
    FILL_MID  = PatternFill("solid", fgColor="2E75B6")
    FILL_GRN  = PatternFill("solid", fgColor="C6EFCE")
    FILL_AMB  = PatternFill("solid", fgColor="FFE699")
    FILL_RED  = PatternFill("solid", fgColor="F8CBAD")
    FILL_REC  = PatternFill("solid", fgColor="E2EFDA")

    def title(ws, txt, row=1, span=8):
        c = ws.cell(row=row, column=1, value=txt)
        c.font = H1; c.fill = FILL_DARK
        ws.merge_cells(start_row=row, start_column=1,
                       end_row=row, end_column=span)
        c.alignment = Alignment(horizontal="left", vertical="center")
        ws.row_dimensions[row].height = 22

    def subtitle(ws, txt, row, span=8):
        c = ws.cell(row=row, column=1, value=txt)
        c.font = Font(italic=True, color="595959", size=10)
        ws.merge_cells(start_row=row, start_column=1,
                       end_row=row, end_column=span)

    def autosize(ws, max_w=52):
        for col in ws.columns:
            try:
                letter = get_column_letter(col[0].column)
            except AttributeError:
                continue
            length = 12
            for cell in col:
                if cell.value is None:
                    continue
                length = max(length, min(max_w, len(str(cell.value)) + 2))
            ws.column_dimensions[letter].width = length

    def headers(ws, row, labels, fill=FILL_MID):
        for i, lab in enumerate(labels, 1):
            c = ws.cell(row=row, column=i, value=lab)
            c.font = H2; c.fill = fill

    # =====================================================================
    # SHEET 1: Cost of Inaction
    # =====================================================================
    ws = wb.active
    ws.title = "Cost of Inaction"
    title(ws, "COST OF INACTION — što košta čekati", span=8)
    subtitle(ws,
             f"Generated {datetime.now():%Y-%m-%d %H:%M}    "
             f"WACC {WACC:.0%}, DSO=DPO={DSO}d (CCC=DIO), "
             f"warehouse rent FIXED €{WAREHOUSE_RENT_MO:,.0f}/mo — excluded from savings",
             row=2)

    row = 4
    ws.cell(row=row, column=1, value="HEADLINE — Opportunity Cost").font = H3
    row += 1
    ws.cell(row=row, column=1, value="Trapped stock value").font = Font(bold=True)
    ws.cell(row=row, column=3, value=fmt_eur(m["trapped_val"]))
    row += 1
    ws.cell(row=row, column=1, value="WORKING yield (€margin/€stock/yr)").font = Font(bold=True)
    ws.cell(row=row, column=3, value=f"{m['working_yield']:.2f}")
    row += 1
    ws.cell(row=row, column=1, value="TRAPPED yield (€margin/€stock/yr)").font = Font(bold=True)
    ws.cell(row=row, column=3, value=f"{m['trapped_yield']:.2f}")
    row += 1
    ws.cell(row=row, column=1, value="Yield gap").font = Font(bold=True)
    ws.cell(row=row, column=3, value=f"{m['yield_gap']:.2f}")
    row += 1
    ws.cell(row=row, column=1,
            value="OPPORTUNITY COST — propuštena marža/god").font = Font(bold=True, color="C00000")
    c = ws.cell(row=row, column=3, value=fmt_eur(m["opportunity_annual"]))
    c.font = Font(bold=True, color="C00000")
    row += 1
    ws.cell(row=row, column=1,
            value="≈ Mjesečno propušteno").font = Font(bold=True, color="C00000")
    c = ws.cell(row=row, column=3, value=fmt_eur(m["opportunity_annual"]/12))
    c.font = Font(bold=True, color="C00000")
    row += 2

    # Variable holding cost per bucket
    ws.cell(row=row, column=1,
            value="Variable holding cost po bucketu (WACC + shrinkage, NE uključuje fiksni najam)"
            ).font = H3
    row += 1
    headers(ws, row, ["Bucket", "Stock value (€)",
                      "Capital cost (8%, €/yr)",
                      "Shrinkage (€/yr)",
                      "Total variable (€/yr)",
                      "Total variable (€/mj)"])
    row += 1
    start = row
    for h in HEALTH_ORDER:
        r = m["bucket_costs"].loc[h]
        ws.cell(row=row, column=1, value=h).fill = PatternFill("solid", fgColor=HEALTH_FILL[h])
        ws.cell(row=row, column=2, value=float(r["stock_value"])).number_format = "#,##0"
        ws.cell(row=row, column=3, value=float(r["capital_cost"])).number_format = "#,##0"
        ws.cell(row=row, column=4, value=float(r["shrinkage_cost"])).number_format = "#,##0"
        ws.cell(row=row, column=5, value=float(r["total_variable_annual"])).number_format = "#,##0"
        ws.cell(row=row, column=6, value=float(r["total_variable_monthly"])).number_format = "#,##0"
        row += 1
    # Trapped (SLOW+DEAD) total
    trapped_holding_annual = float(
        m["bucket_costs"].loc[["SLOW", "DEAD"], "total_variable_annual"].sum()
    )
    trapped_holding_monthly = trapped_holding_annual / 12
    ws.cell(row=row, column=1, value="TRAPPED (SLOW+DEAD)").font = Font(bold=True)
    ws.cell(row=row, column=2,
            value=float(m["trapped_val"])).number_format = "#,##0"
    ws.cell(row=row, column=5, value=trapped_holding_annual).number_format = "#,##0"
    ws.cell(row=row, column=6, value=trapped_holding_monthly).number_format = "#,##0"
    for c_idx in range(1, 7):
        ws.cell(row=row, column=c_idx).fill = FILL_AMB
    row += 2

    # Top 5 categories by monthly variable holding cost
    ws.cell(row=row, column=1,
            value="Top 5 kategorija po varijabilnom mjesečnom trošku").font = H3
    row += 1
    headers(ws, row, ["Category", "Stock value (€)", "Variable cost (€/mj)"])
    row += 1
    for cat, r in m["top_cat_costs"].iterrows():
        ws.cell(row=row, column=1, value=cat)
        ws.cell(row=row, column=2, value=float(r["stock_value"])).number_format = "#,##0"
        ws.cell(row=row, column=3, value=float(r["variable_monthly"])).number_format = "#,##0"
        row += 1
    row += 1

    # Bottom line
    ws.cell(row=row, column=1,
            value="LINIJA NA DNU — koliko košta svaki mjesec bez akcije"
            ).font = H3
    row += 1
    ws.cell(row=row, column=1, value="Variable holding cost na trapped stock").font = Font(bold=True)
    ws.cell(row=row, column=3, value=fmt_eur(trapped_holding_monthly) + " /mj")
    row += 1
    ws.cell(row=row, column=1,
            value="Propuštena marža (opportunity)").font = Font(bold=True)
    ws.cell(row=row, column=3, value=fmt_eur(m["opportunity_annual"]/12) + " /mj")
    row += 1
    ws.cell(row=row, column=1,
            value="UKUPNO mjesečno BEZ AKCIJE").font = Font(bold=True, color="C00000")
    total_mo_cost = trapped_holding_monthly + m["opportunity_annual"]/12
    c = ws.cell(row=row, column=3, value=fmt_eur(total_mo_cost) + " /mj")
    c.font = Font(bold=True, color="C00000")
    row += 2

    # Warehouse note (separate)
    ws.cell(row=row, column=1, value="Warehouse — kontekstualna napomena").font = H3
    row += 1
    notes = [
        f"Skladišni najam €{WAREHOUSE_RENT_MO:,.2f}/mj je FIKSNI (AIPK-TRGOVINA, "
        "1,200 paletnih mjesta) — čišćenje stocka NE smanjuje fakturu.",
        "Argument se NE temelji na warehouse troškovima. Jedini warehouse-vezani "
        "argument: ako je skladište blizu kapaciteta, novi proizvod ili sezonska "
        "narudžba zahtijeva da PRVO oslobodimo prostor.",
        "Trenutna popunjenost nije poznata (logistika nema WMS) — preporuka: "
        "WMS implementacija u sklopu strukturne reforme (Scenario C).",
    ]
    for n in notes:
        ws.cell(row=row, column=1, value="• " + n)
        ws.merge_cells(start_row=row, start_column=1,
                       end_row=row, end_column=8)
        row += 1
    row += 1

    # Clothing depreciation curve
    ws.cell(row=row, column=1,
            value="Odjeća — krivulja pada vrijednosti (4%/mj fashion obsolescence)"
            ).font = H3
    row += 1
    headers(ws, row, ["Months from now",
                      "All clothing value (€)",
                      "DEAD clothing value (€)"])
    row += 1
    curve_start = row
    for _, r in m["depreciation_curve"].iterrows():
        ws.cell(row=row, column=1, value=int(r["months_from_now"]))
        ws.cell(row=row, column=2, value=float(r["clothing_total_eur"])).number_format = "#,##0"
        ws.cell(row=row, column=3, value=float(r["clothing_dead_eur"])).number_format = "#,##0"
        row += 1
    curve_end = row - 1

    chart_curve = LineChart()
    chart_curve.title = "Clothing value erosion (4%/mo)"
    chart_curve.y_axis.title = "EUR at cost"
    chart_curve.x_axis.title = "Months from now"
    data_ref = Reference(ws, min_col=2, min_row=curve_start - 1,
                         max_row=curve_end, max_col=3)
    cats_ref = Reference(ws, min_col=1, min_row=curve_start, max_row=curve_end)
    chart_curve.add_data(data_ref, titles_from_data=True)
    chart_curve.set_categories(cats_ref)
    chart_curve.height = 9; chart_curve.width = 16
    ws.add_chart(chart_curve, f"E{curve_start - 1}")

    autosize(ws)

    # =====================================================================
    # SHEET 2-4: Scenarios
    # =====================================================================
    def write_scenario(ws, sc, m, bullet_actions, color_top=FILL_MID):
        title(ws, f"{sc['name']} — {sc['subtitle']}", span=6)
        subtitle(ws,
                 f"Trajanje: {sc['horizon_months']} mjeseci    "
                 f"Risk: {sc['risk']}    "
                 f"Target DIO: {sc['dio_target']}d (CCC = DIO since DSO=DPO=60d)",
                 row=2)
        row = 4
        ws.cell(row=row, column=1, value="Akcije").font = H3
        row += 1
        for i, b in enumerate(bullet_actions, 1):
            ws.cell(row=row, column=1, value=f"{i}. {b}")
            ws.merge_cells(start_row=row, start_column=1,
                           end_row=row, end_column=6)
            row += 1
        row += 1
        ws.cell(row=row, column=1, value="Financijski summary").font = H3
        row += 1
        items = [
            ("Revenue od likvidacije",          sc["revenue"]),
            ("Write-off (P&L hit)",            -sc["writeoff"]),
            ("Tax benefit (donacije)",          sc.get("tax_benefit", 0)),
            ("Annual holding cost savings (WACC+shrink)", sc["annual_savings"]),
            ("Reinvestment capital",            sc.get("reinvest_capital", 0)),
            ("Reinvestment margin (yr)",        sc.get("reinvest_margin", 0)),
            ("Stock reduction (period)",        sc["stock_reduction"]),
            ("New stock value (after)",         sc["new_stock"]),
            ("New DIO estimate",                f"~{sc['dio_target']} days"),
        ]
        for label, val in items:
            ws.cell(row=row, column=1, value=label).font = Font(bold=True)
            if isinstance(val, str):
                ws.cell(row=row, column=3, value=val)
            else:
                ws.cell(row=row, column=3, value=float(val)).number_format = "#,##0"
                if val and val < 0:
                    ws.cell(row=row, column=3).font = Font(color="C00000")
            row += 1
        # Bottom line
        ws.cell(row=row, column=1, value="NET 12-MONTH P&L IMPACT").font = Font(bold=True, size=12)
        c = ws.cell(row=row, column=3, value=float(sc["net_12m_pnl"]))
        c.number_format = "#,##0"
        c.font = Font(bold=True, size=12,
                       color=("006100" if sc["net_12m_pnl"] >= 0 else "C00000"))
        autosize(ws)

    # -- Scenario A
    wsA = wb.create_sheet("Scenario A — Conservative")
    write_scenario(
        wsA, sA, m,
        [
            f"DEAD odjeća (€{m['dead_clothing']:,.0f}): outlet -50%, 25% recovery → "
            f"€{m['dead_clothing']*0.25:,.0f} revenue, €{m['dead_clothing']*0.75:,.0f} write-off",
            f"DEAD perishable (€{m['dead_perishable']:,.0f}): rok-check + flash -30%, 40% recovery → "
            f"€{m['dead_perishable']*0.40:,.0f} revenue, €{m['dead_perishable']*0.60:,.0f} write-off",
            f"DEAD ostalo (€{m['dead_other']:,.0f}): bundle/loyalty/B2B outlet, 35% recovery → "
            f"€{m['dead_other']*0.35:,.0f} revenue, €{m['dead_other']*0.65:,.0f} write-off",
            f"SLOW stock (€{m['slow_value']:,.0f}): moratorij na nabavu + -10% top 50, "
            "organic reduction ~€300K",
            "Nabavna disciplina: hard stop ako WOS > 16; MOQ review za top dobavljače",
        ],
    )

    # -- Scenario B
    wsB = wb.create_sheet("Scenario B — Aggressive")
    write_scenario(
        wsB, sB, m,
        [
            f"SVE DEAD (€{m['dead_clothing']+m['dead_perishable']+m['dead_other']:,.0f}) "
            "u 90 dana: -30%/-50%/-70%/donacija (20% recovery)",
            f"SLOW (€{m['slow_value']:,.0f}): -20% odmah / -30% za WOS>30, "
            "cilj -50% u 3 mj (60% recovery + €350K extra revenue)",
            "Gold reklasifikacija: 50 Gold SLOW/DEAD SKU-ova → Bronze / phase-out",
            f"Reinvestiranje €500K oslobođenog kapitala u STAR underst.: "
            f"očekivano +€{500_000 * m['working_yield']:,.0f}/yr extra margin",
        ],
    )

    # -- Scenario C (recommended)
    wsC = wb.create_sheet("Scenario C — Hybrid (REC)")
    sC_actions = [
        "Faza 1 — TRIAGE (W1-2): fizička provjera rokova, razvrstavanje u "
        "kutije a/b/c po preostalom roku",
        f"Faza 2 — ODJEĆA BLITZ (W1-4): B2B outlet + donacije, target recovery "
        f"~18% od €{m['dead_clothing']:,.0f} + porezna olakšica",
        "Faza 3 — NABAVNA REFORMA (W2-8): auto-block PO if WOS>20, MOQ alert, "
        "tier review trigger, weekly unplanned-stock report",
        f"Faza 4 — REINVESTIRANJE (W4-16): €{sC['reinvest_capital']:,.0f} u STAR "
        f"underst. SKU-ove, potential +€{sC['reinvest_margin']:,.0f}/yr extra margin",
        "Faza 5 — KPI MONITORING: weekly dashboard (DIO, %trapped, dead value, "
        "STAR ratio, PO compliance)",
    ]
    write_scenario(wsC, sC, m, sC_actions)
    # mark recommended sheet with green tab color
    wsC.sheet_properties.tabColor = "70AD47"

    # =====================================================================
    # SHEET 5: Scenario Comparison
    # =====================================================================
    ws = wb.create_sheet("Scenario Comparison")
    title(ws, "Scenario Comparison — side by side", span=6)
    subtitle(ws,
             "Warehouse rent NOT in savings (fixed). "
             "Variable saving = freed capital × (WACC 8% + ~8% shrinkage).",
             row=2)

    headers_row = 4
    cols = ["Metric", "Status Quo", sA["name"], sB["name"], sC["name"]]
    for i, c in enumerate(cols, 1):
        cell = ws.cell(row=headers_row, column=i, value=c)
        cell.font = H2; cell.fill = FILL_MID
    # mark recommended col
    ws.cell(row=headers_row, column=5).fill = PatternFill("solid", fgColor="70AD47")

    rows = [
        ("Timeline",                          "—",
         f"{sA['horizon_months']} months",
         f"{sB['horizon_months']} months",
         f"{sC['horizon_months']} months"),
        ("Revenue from liquidation",          0,
         sA["revenue"], sB["revenue"], sC["revenue"]),
        ("Write-off (P&L hit)",               0,
         -sA["writeoff"], -sB["writeoff"], -sC["writeoff"]),
        ("Tax benefit (donacije)",            0,
         sA.get("tax_benefit", 0), sB.get("tax_benefit", 0), sC.get("tax_benefit", 0)),
        ("Stock after execution (€)",         m["total_stock"],
         sA["new_stock"], sB["new_stock"], sC["new_stock"]),
        ("DIO after (days)",                  101,
         sA["dio_target"], sB["dio_target"], sC["dio_target"]),
        ("CCC after (= DIO, since DSO=DPO)",  101,
         sA["dio_target"], sB["dio_target"], sC["dio_target"]),
        ("Variable cost saving (€/yr)",       0,
         sA["annual_savings"], sB["annual_savings"], sC["annual_savings"]),
        ("Variable cost saving (€/mj)",       0,
         sA["monthly_savings"], sB["monthly_savings"], sC["monthly_savings"]),
        ("Warehouse saving",                  0, 0, 0, 0),
        ("Reinvestment capital (€)",          0,
         sA.get("reinvest_capital", 0),
         sB.get("reinvest_capital", 0),
         sC.get("reinvest_capital", 0)),
        ("Reinvestment margin (€/yr)",        0,
         sA.get("reinvest_margin", 0),
         sB.get("reinvest_margin", 0),
         sC.get("reinvest_margin", 0)),
        ("Opportunity cost recovered (€/yr)",
         -m["opportunity_annual"],
         -m["opportunity_annual"] * 0.5,    # partial
         -m["opportunity_annual"] * 0.85,
         -m["opportunity_annual"] * 0.75),
        ("NET 12-month P&L impact (€)",
         -m["opportunity_annual"] - 0,      # status quo: only opportunity cost
         sA["net_12m_pnl"], sB["net_12m_pnl"], sC["net_12m_pnl"]),
        ("Risk level",                        "HIGH (expiry)",
         sA["risk"], sB["risk"], sC["risk"]),
        ("Process change",                    "NONE",
         "Partial", "Minimal", "Full"),
    ]
    row = headers_row + 1
    pnl_chart_row = None
    for label, *vals in rows:
        ws.cell(row=row, column=1, value=label).font = Font(bold=True)
        for j, v in enumerate(vals, 2):
            if isinstance(v, str):
                ws.cell(row=row, column=j, value=v)
            else:
                cell = ws.cell(row=row, column=j, value=float(v))
                cell.number_format = "#,##0"
                if v < 0:
                    cell.font = Font(color="C00000")
        if label.startswith("NET 12-month"):
            pnl_chart_row = row
        # tint recommended column
        ws.cell(row=row, column=5).fill = FILL_REC
        row += 1

    # Add bar chart for Net 12-month P&L
    if pnl_chart_row is not None:
        chart = BarChart()
        chart.type = "col"
        chart.title = "Net 12-month P&L impact (€)"
        chart.y_axis.title = "EUR"
        data_ref = Reference(ws, min_col=2, min_row=pnl_chart_row,
                             max_col=5,  max_row=pnl_chart_row)
        cats_ref = Reference(ws, min_col=2, min_row=headers_row,
                             max_col=5,  max_row=headers_row)
        chart.add_data(data_ref, titles_from_data=False)
        chart.set_categories(cats_ref)
        chart.height = 9; chart.width = 16
        ws.add_chart(chart, f"G{headers_row}")

    autosize(ws)

    # =====================================================================
    # SHEET 6: Implementation Roadmap (Scenario C)
    # =====================================================================
    ws = wb.create_sheet("Implementation Roadmap")
    title(ws, "Implementation Roadmap — Scenario C (recommended)", span=5)
    subtitle(ws, "Owner + KPI per action, sortirano kronološki", row=2)
    headers_row = 4
    cols = ["Week", "Action", "Owner", "KPI", "€€€ Impact"]
    for i, c in enumerate(cols, 1):
        cell = ws.cell(row=headers_row, column=i, value=c)
        cell.font = H2; cell.fill = FILL_MID

    roadmap = [
        ("W1",   "Physical expiry audit — all perishable DEAD", "Warehouse manager", "# SKUs audited", "—"),
        ("W1-2", "Negotiate B2B outlet deal for clothing", "Commercial director", "Deal signed Y/N", "—"),
        ("W2",   "Launch flash sale perishable urgent", "Marketing + E-com", "Units sold/day", "+€20K"),
        ("W2",   "Implement PO auto-block for WOS>20", "Dev team (Polleo Demand)", "Feature live Y/N", "Prevention"),
        ("W3-4", "Clothing bulk sale / donation", "Operations", "Pallets cleared", "+€51K + tax benefit"),
        ("W4",   "Gold tier reclassification", "Demand planner", "50 SKUs reclassified", "Focus"),
        ("W4-8", "SLOW stock -10/-20% campaign", "Marketing", "WOS reduction trend", "+€100K"),
        ("W4-8", "MOQ renegotiation with top 5 suppliers", "Procurement", "New MOQs signed", "Prevention"),
        ("W8",   "First reinvestment round — STAR understocked", "Demand planner + procurement", "POs placed",
         f"Future +€{sC['reinvest_margin']:,.0f}"),
        ("W8-16", "Monitor weekly KPIs", "SCM director", "DIO, % trapped", "Tracking"),
        ("W12",  "Mid-execution review", "Board", "Scenario on track Y/N", "Decision point"),
        ("W16",  "Final review + Q4 plan", "Board", "DIO target hit", "Close-out"),
    ]
    for i, r in enumerate(roadmap, start=headers_row + 1):
        for j, v in enumerate(r, 1):
            ws.cell(row=i, column=j, value=v)
    ws.freeze_panes = ws.cell(row=headers_row + 1, column=1)
    autosize(ws)

    # =====================================================================
    # SHEET 7: KPI Dashboard Targets
    # =====================================================================
    ws = wb.create_sheet("KPI Targets")
    title(ws, "KPI Dashboard Targets — Q3, Q4, World-Class", span=5)
    subtitle(ws,
             "Current = baseline as of analysis date. "
             "World-class = benchmarks for sports-nutrition retail / D2C",
             row=2)
    headers_row = 4
    for i, c in enumerate(
        ["KPI", "Current", "Q3 Target", "Q4 Target", "World-Class"], 1
    ):
        cell = ws.cell(row=headers_row, column=i, value=c)
        cell.font = H2; cell.fill = FILL_MID

    # use actual values for "Current" where known
    star_pct = m["bucket"].loc["STAR", "stock_value"] / m["total_stock"]
    gold_working = (
        m["df"][(m["df"]["tier_display"] == "Gold")
                 & m["df"]["health"].isin(["STAR", "HEALTHY"])]["stock_value"].sum()
        / max(m["df"][m["df"]["tier_display"] == "Gold"]["stock_value"].sum(), 1)
    )
    n_unplanned = int((m["df"]["tier_display"] == "Unplanned").sum())
    inv_turns = 365 / 101  # ~3.6x
    pct_trapped = m["trapped_val"] / m["total_stock"]

    kpis = [
        ("DIO (days)",                       "101",   "60",     "50",     "35-45"),
        ("CCC (days; = DIO since DSO=DPO)",  "101",   "60",     "50",     "30-40"),
        ("% Trapped stock",                  f"{pct_trapped:.0%}", "35%", "20%", "<15%"),
        ("Dead stock value (€)",             f"€{m['bucket'].loc['DEAD','stock_value']:,.0f}",
                                             "€300K", "€150K",  "<€100K"),
        ("STAR % of total value",            f"{star_pct:.1%}", "35%",  "45%",   ">50%"),
        ("Gold tier 'working' rate",         f"{gold_working:.0%}", "70%", "85%",  ">90%"),
        ("Inventory turns/year",             f"{inv_turns:.1f}x", "6x",  "8x",    "10-12x"),
        ("Unplanned SKUs with stock",        f"{n_unplanned:,}", "1,500", "500",  "<200"),
        ("PO compliance (no order if WOS>16)", "N/A",  "90%",   "98%",    "100%"),
        ("Avg WOS coverage on planned SKUs", "Mixed", "4-8w",  "4-8w",   "4-8w"),
    ]
    for i, r in enumerate(kpis, start=headers_row + 1):
        for j, v in enumerate(r, 1):
            ws.cell(row=i, column=j, value=v)
    autosize(ws)

    # =====================================================================
    # SHEET 8: Root Cause Analysis (prose)
    # =====================================================================
    ws = wb.create_sheet("Root Cause Analysis")
    title(ws, "Zašto smo ovdje — root cause analiza", span=4)
    subtitle(ws,
             "Strukturni problemi koji su doveli do trenutne situacije. "
             "Bez popravka uzroka, simptom se vraća.",
             row=2)

    sections = [
        ("1. Nema automatske veze između stock levela i nabave",
         "Nabava naručuje po osjećaju ili po MOQ-u, bez real-time vidjenja "
         "WOS-a. Rezultat: stock raste neprimijećen dok ne postane problem. "
         "Rješenje: PO auto-block za SKU s WOS > 20 (Scenario C, Faza 3a)."),
        ("2. Gold/Silver klasifikacija se ne revidira",
         "SKU jednom klasificiran kao Gold ostaje Gold zauvijek, čak i kad "
         "prestane prodavati. Trenutno ~43 Gold SKU-ova ima WOS > 20. "
         "Rješenje: automatski tier-review trigger (Scenario C, Faza 3c)."),
        ("3. Unplanned SKU-ovi nemaju vlasnika",
         f"{int((m['df']['tier_display']=='Unplanned').sum()):,} SKU-ova "
         f"(€{m['df'][m['df']['tier_display']=='Unplanned']['stock_value'].sum():,.0f}) "
         "nema tier, nema planera, nema review ciklus. Roba je naručena, "
         "prodaja je pala, nitko nije odgovoran za reakciju. "
         "Rješenje: weekly unplanned-stock report (Scenario C, Faza 3d)."),
        ("4. MOQ kod nekih dobavljača tjera na overstock",
         "Ako je MOQ 13+ tjedana potražnje, svaka narudžba automatski "
         "stvara overstock. Treba renegocirati ili naći alternativne "
         "dobavljače. Rješenje: MOQ alert flag (Scenario C, Faza 3b)."),
        ("5. Odjeća — strateška odluka koja nije prošla",
         f"€{m['clothing_total']:,.0f} u odjeći, od čega "
         f"€{m['dead_clothing']:,.0f} dead. Kategorija očito ne rezonira "
         "s customer bazom (sports-nutrition audience). Treba donijeti "
         "odluku: exit kategoriju ili radikalno smanjiti asortiman "
         "na <50 dokazanih SKU-ova."),
        ("6. Nema expiry-date tracking-a u sustavu",
         "Za retailera koji prodaje perishable goods, ovo je osnovni "
         "hygiene — a mi nemamo rok trajanja po SKU-u/batchu u bazi. "
         "Svaki protein, bar, RTD bi trebao imati expiry visibility. "
         "Rješenje: dodati expiry_date kolonu u erp_stock_history "
         "i prikazivati u demand planning app-u."),
    ]
    row = 4
    for header, body in sections:
        ws.cell(row=row, column=1, value=header).font = H3
        row += 1
        cell = ws.cell(row=row, column=1, value=body)
        cell.alignment = Alignment(wrap_text=True, vertical="top")
        ws.merge_cells(start_row=row, start_column=1,
                       end_row=row, end_column=4)
        ws.row_dimensions[row].height = 60
        row += 2
    ws.column_dimensions["A"].width = 30
    ws.column_dimensions["B"].width = 30
    ws.column_dimensions["C"].width = 30
    ws.column_dimensions["D"].width = 30

    wb.save(out)


# ---------------------------------------------------------------------------
# Console
# ---------------------------------------------------------------------------
def print_summary(m, sA, sB, sC):
    print("\n=== POLLEO SCM ACTION PLAN ===\n")
    print(f"Input:  {INPUT_X.name}")
    print(f"Output: {OUTPUT_X.name}\n")
    print("--- Cost of Inaction ---")
    print(f"  Trapped stock:        €{m['trapped_val']:,.0f}")
    print(f"  Working yield:        {m['working_yield']:.2f} €/€/yr")
    print(f"  Trapped yield:        {m['trapped_yield']:.2f} €/€/yr")
    print(f"  Yield gap:            {m['yield_gap']:.2f}")
    print(f"  Opportunity €/yr:     €{m['opportunity_annual']:,.0f}")
    print(f"  Opportunity €/mj:     €{m['opportunity_annual']/12:,.0f}")
    print()
    for sc in [sA, sB, sC]:
        print(f"--- {sc['name']} ({sc['horizon_months']} mj) ---")
        print(f"  Revenue:         €{sc['revenue']:,.0f}")
        print(f"  Write-off:       €{sc['writeoff']:,.0f}")
        print(f"  Tax benefit:     €{sc.get('tax_benefit',0):,.0f}")
        print(f"  Annual savings:  €{sc['annual_savings']:,.0f}")
        print(f"  Stock after:     €{sc['new_stock']:,.0f} (-{(m['total_stock']-sc['new_stock'])/m['total_stock']:.0%})")
        print(f"  DIO target:      {sc['dio_target']}d")
        print(f"  Reinvest margin: €{sc.get('reinvest_margin',0):,.0f}")
        print(f"  NET 12m P&L:     €{sc['net_12m_pnl']:,.0f}")
        print()


def main():
    print("Loading raw data…")
    df = load_raw_data()
    print(f"  {len(df):,} SKUs loaded from {INPUT_X.name}")
    print("Computing metrics…")
    m = compute_metrics(df)
    print("Building scenarios…")
    sA = build_scenario_a(m)
    sB = build_scenario_b(m)
    sC = build_scenario_c(m)
    print_summary(m, sA, sB, sC)
    print(f"Writing {OUTPUT_X.name} …")
    write_excel(m, sA, sB, sC, OUTPUT_X)
    print(f"Excel saved: {OUTPUT_X}")


if __name__ == "__main__":
    main()