"""13-week isporučivost forecast — projects per-SKU weekly closing
stock and aggregates per tier (Gold / Silver / Bronze) to answer
management's question: "when will we hit target deliverability?"

Inputs:
  • data/Isporučivost GOLD, SILVER, BRONZE (5).xlsx
    - SKU list, OZNAKA (tier), Stanje zalihe, AVG prodaja (monthly),
      DATUM DOLASKA, Količina (for SKUs not in Coverage)
  • data/Coverage_W*.xlsx  (latest)
    - Per-SKU weekly Stock / Demand / Incoming / Closing for the
      14-week horizon out of the POKRIVENOST sheet

Logic:
  Per SKU per week W in horizon:
    closing[W] = prev_closing + incoming[W] - demand[W]
    deliverable[W] = closing[W] > monthly_avg / 3

  Per tier per week:
    %_deliverable = count(deliverable) / count(tier_skus) * 100

Targets from email: Gold 97% · Silver 94% · Bronze 83%.

Output: docs/Isporucivost_Target_Forecast.xlsx with 5 sheets:
  1. Summary       (hit week per tier)
  2. Weekly        (tier x week % matrix + flags)
  3. Per-SKU       (every SKU x week: closing stock + deliverable flag)
  4. Bottlenecks   (SKUs never reaching deliverable in horizon)
  5. Risk SKUs     (currently deliverable but drop in horizon)

Run: py analyze_isporucivost_forecast.py
"""
from __future__ import annotations

from collections import defaultdict
from datetime import datetime
from pathlib import Path

import openpyxl
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

import argparse
parser = argparse.ArgumentParser()
parser.add_argument("--no-vp", action="store_true",
                     help="Exclude wholesale (VP) on-top demand from the projection")
args, _ = parser.parse_known_args()
EXCLUDE_VP = args.no_vp

ROOT = Path(__file__).parent
DATA = ROOT / "data"
OUT_SUFFIX = "_no_VP" if EXCLUDE_VP else ""
OUT = ROOT / "docs" / f"Isporucivost_Target_Forecast{OUT_SUFFIX}.xlsx"
OUT.parent.mkdir(parents=True, exist_ok=True)

# Find latest Coverage workbook
COVERAGE_FILE = max(
    DATA.glob("Coverage_W*.xlsx"),
    key=lambda p: p.stat().st_mtime,
)
ISP_FILE = next(DATA.glob("Isporučivost GOLD, SILVER, BRONZE*.xlsx"))

# Targets per tier (from management email)
TARGETS = {"01 GOLD": 0.97, "02 SILVER": 0.94, "03 BRONZE": 0.83}

# Map normalized tier label to display
TIER_DISPLAY = {"01 GOLD": "GOLD", "02 SILVER": "SILVER", "03 BRONZE": "BRONZE"}


def normalize_tier(value) -> str:
    """Normalize OZNAKA cell to canonical tier key."""
    if value is None:
        return ""
    s = str(value).strip().upper()
    if "GOLD" in s: return "01 GOLD"
    if "SILVER" in s: return "02 SILVER"
    if "BRONZE" in s: return "03 BRONZE"
    return ""


# ============================================================
# 1. Load isporučivost Excel — SKU master + their incoming
# ============================================================
print(f"Loading {ISP_FILE.name}...")
import warnings
warnings.filterwarnings("ignore", category=UserWarning)
wb_isp = openpyxl.load_workbook(ISP_FILE, data_only=True)
ws_isp = wb_isp["ISPORUČIVOST VP&B2C"]

skus_master = {}    # sku -> meta
for r in range(2, ws_isp.max_row + 1):
    sku = ws_isp.cell(r, 1).value
    if not sku:
        continue
    sku = str(sku).strip()
    tier_raw = ws_isp.cell(r, 7).value
    tier = normalize_tier(tier_raw)
    if not tier:
        continue
    name = ws_isp.cell(r, 2).value or ""
    grupacija = ws_isp.cell(r, 3).value or ""
    stock = ws_isp.cell(r, 8).value
    avg_monthly = ws_isp.cell(r, 10).value
    eta = ws_isp.cell(r, 11).value    # DATUM DOLASKA
    eta_qty = ws_isp.cell(r, 12).value  # Količina
    try:
        stock_v = float(stock or 0)
    except (TypeError, ValueError):
        stock_v = 0.0
    try:
        avg_v = float(avg_monthly or 0)
    except (TypeError, ValueError):
        avg_v = 0.0
    try:
        eta_qty_v = float(eta_qty or 0)
    except (TypeError, ValueError):
        eta_qty_v = 0.0
    # ETA may be a datetime or a string ("imat ćemo idući tjedan info")
    eta_yw = None
    if isinstance(eta, datetime):
        iso = eta.isocalendar()
        eta_yw = int(iso[0]) * 100 + int(iso[1])
    skus_master[sku] = {
        "tier": tier,
        "name": str(name)[:60],
        "grupacija": str(grupacija),
        "current_stock": stock_v,
        "monthly_avg": avg_v,
        "eta_yw": eta_yw,
        "eta_qty": eta_qty_v,
    }
wb_isp.close()
print(f"  {len(skus_master)} SKUs with valid tier ({sum(1 for s in skus_master.values() if s['tier']=='01 GOLD')} Gold / "
      f"{sum(1 for s in skus_master.values() if s['tier']=='02 SILVER')} Silver / "
      f"{sum(1 for s in skus_master.values() if s['tier']=='03 BRONZE')} Bronze)")

# ============================================================
# 2. Load Coverage POKRIVENOST — per-SKU weekly data
# ============================================================
print(f"Loading {COVERAGE_FILE.name}...")
wb_cov = openpyxl.load_workbook(COVERAGE_FILE, data_only=True, read_only=True)
ws_cov = wb_cov["POKRIVENOST"]

# Read ALL rows into memory (read_only ws.cell() is O(N) per call — disaster
# for 2553×62 sheet. iter_rows() streams once.).
print("  Reading POKRIVENOST rows...")
all_rows = list(ws_cov.iter_rows(values_only=True))
wb_cov.close()
print(f"  Loaded {len(all_rows)} rows.")

# Find which columns are which weeks (header row index 2 = row 3, cols 9..)
week_cols = {}    # week_int -> col_idx (0-indexed)
if len(all_rows) >= 3:
    hdr = all_rows[2]
    for c, v in enumerate(hdr):
        if isinstance(v, str) and v.lower().startswith("week"):
            try:
                wk = int(v.split()[-1])
                week_cols[wk] = c
            except ValueError:
                pass

print(f"  Weekly columns: CW{min(week_cols)}..CW{max(week_cols)} ({len(week_cols)} weeks)")

# Determine projection horizon — current week onward, 13 weeks
iso_now = datetime.now().isocalendar()
current_week = int(iso_now[1])
current_year = int(iso_now[0])
horizon_weeks = []
for offset in range(0, 13):
    w = current_week + offset
    y = current_year
    if w > 52:
        w -= 52; y += 1
    if w in week_cols:
        horizon_weeks.append(w)
if not horizon_weeks:
    # Coverage workbook might be stale — fall back to all available weeks
    horizon_weeks = sorted([w for w in week_cols if w >= current_week])[:13]
    if not horizon_weeks:
        horizon_weeks = sorted(week_cols.keys())[-13:]
print(f"  Projection horizon: CW{horizon_weeks[0]}..CW{horizon_weeks[-1]} ({len(horizon_weeks)} weeks)")

# Walk POKRIVENOST 5-row blocks (Stock/Demand/Incoming/Closing/Coverage)
# starting at row index 3 (= excel row 4). all_rows are 0-indexed.
# Load vp_input.csv — VP (wholesale) on-top demand per SKU per CW.
# If --no-vp flag passed, we'll SUBTRACT this from the Coverage Demand
# so the projection reflects retail+web demand only.
vp_ontop_by_sku_yw = defaultdict(lambda: defaultdict(float))    # sku -> yw -> qty
if EXCLUDE_VP:
    vp_csv = DATA / "vp_input.csv"
    if vp_csv.exists():
        import pandas as _pd
        vp_df = _pd.read_csv(vp_csv)
        sku_col_name = vp_df.columns[0]
        cw_cols = [c for c in vp_df.columns
                   if isinstance(c, str) and c.upper().startswith("CW")]
        for _, r in vp_df.iterrows():
            s = str(r[sku_col_name]).strip()
            for cw_col in cw_cols:
                try:
                    qty = float(r[cw_col] or 0)
                except (TypeError, ValueError):
                    qty = 0
                if qty <= 0:
                    continue
                w = int(cw_col[2:])
                yw = current_year * 100 + w if False else None
                # Best-effort YW: assume current year unless week < current
                # week (then it's next year)
                from datetime import datetime as _dt
                yr_now = int(_dt.now().isocalendar()[0])
                yw = yr_now * 100 + w
                vp_ontop_by_sku_yw[s][yw] += qty
    print(f"  Loaded vp_input.csv: VP on-top across "
          f"{len(vp_ontop_by_sku_yw)} SKUs (will be SUBTRACTED).")
else:
    print(f"  VP on-top: INCLUDED in demand projection.")

# Load incoming_supply.csv DIRECTLY — POKRIVENOST's incoming column
# can be stale (depends on when CM last refreshed the Coverage workbook).
# The CSV is the live source of truth from incoming PO uploads.
import pandas as _pd
inc_csv = DATA / "incoming_supply.csv"
incoming_by_sku_yw = defaultdict(lambda: defaultdict(float))  # sku -> yw -> qty
if inc_csv.exists():
    inc_df = _pd.read_csv(inc_csv)
    inc_df.columns = [c.strip().lower() for c in inc_df.columns]
    if {"sku", "year", "week", "qty"} <= set(inc_df.columns):
        for _, r in inc_df.iterrows():
            try:
                s = str(r["sku"]).strip()
                yw = int(r["year"]) * 100 + int(r["week"])
                q = float(r["qty"] or 0)
                if q > 0:
                    incoming_by_sku_yw[s][yw] += q
            except (TypeError, ValueError):
                continue
print(f"  Loaded incoming_supply.csv: {sum(len(v) for v in incoming_by_sku_yw.values())} "
      f"PO entries across {len(incoming_by_sku_yw)} SKUs.")

cov_data = {}    # sku -> {week: {stock, demand, incoming, closing}}
BLOCK = 5

def _to_float(v):
    try:
        return float(v or 0)
    except (TypeError, ValueError):
        return 0.0


# NEW Coverage layout (May 2026):
#   col 0..7: meta (vpkolicina, supplier, lead time, grupacija, sku, name, komentar)
#   col 8:    Oznaka (tier: 01 GOLD / 02 SILVER / 03 BRONZE)  ← NEW column
#   col 9:    row label (Stock / Demand / Incoming / Closing / Coverage)
#   col 10..: weekly values
SKU_COL = 5
TIER_COL = 8
LABEL_COL = 9

for start_idx in range(3, len(all_rows), BLOCK):
    if start_idx + 3 >= len(all_rows):
        break
    row_stock   = all_rows[start_idx]
    row_demand  = all_rows[start_idx + 1]
    row_incoming = all_rows[start_idx + 2]
    row_closing = all_rows[start_idx + 3]

    if len(row_stock) <= LABEL_COL:
        continue
    sku = row_stock[SKU_COL]
    label = row_stock[LABEL_COL]
    if not sku or label != "Stock":
        continue
    sku = str(sku).strip()

    weekly = {}
    for w, col in week_cols.items():
        if col >= len(row_stock):
            continue
        weekly[w] = {
            "stock":    _to_float(row_stock[col]),
            "demand":   _to_float(row_demand[col]) if col < len(row_demand) else 0,
            "incoming": _to_float(row_incoming[col]) if col < len(row_incoming) else 0,
            "closing":  _to_float(row_closing[col]) if col < len(row_closing) else 0,
        }
    cov_data[sku] = weekly

print(f"  {len(cov_data)} SKUs in POKRIVENOST")

# ============================================================
# 3. Per-SKU projection over horizon
# ============================================================
def project_sku(sku: str, meta: dict, cov: dict | None) -> dict:
    """Return {week: closing_stock} for the SKU across horizon_weeks.

    New Coverage workbook has Closing column POPULATED (real numbers)
    so we can use those directly. ETA pulses from Isporučivost Excel
    are added on top for SKUs whose PO arrived after Coverage refresh.

      • If sku in POKRIVENOST: closing_W = workbook[W].closing (+ ETA bump if applicable)
      • Else (fitness/borilačka/gadgets not in POKRIVENOST):
          synthetic = start_stock - cumulative_weekly_demand + cumulative_incoming
    """
    eta_yw = meta.get("eta_yw")
    eta_qty = meta.get("eta_qty", 0)
    sku_incoming_map = incoming_by_sku_yw.get(sku, {})

    out = {}

    if cov:
        # Use workbook's Closing column directly when including all demand.
        # When --no-vp, recompute closing using (demand - vp_ontop) so the
        # projected stock reflects retail+web demand only.
        eta_bump_applied = False
        eta_bump_qty = 0.0
        eta_bump_from_week = None

        if EXCLUDE_VP:
            # Recompute closing per week from scratch using (demand − VP)
            vp_map = vp_ontop_by_sku_yw.get(sku, {})
            prev_closing = None
            for w in horizon_weeks:
                wd = cov.get(w, {})
                yw_cur = current_year * 100 + w
                if prev_closing is None:
                    prev_closing = wd.get("stock", 0) or float(meta.get("current_stock", 0) or 0)
                demand_full = wd.get("demand", 0) or 0
                vp_sub = vp_map.get(yw_cur, 0)
                demand_eff = max(0, demand_full - vp_sub)
                incoming = wd.get("incoming", 0) or 0
                incoming = max(incoming, sku_incoming_map.get(yw_cur, 0))
                # ETA bump
                if (eta_yw is not None and eta_qty > 0
                        and eta_yw <= yw_cur and not eta_bump_applied):
                    if incoming < eta_qty * 0.5:
                        incoming += eta_qty
                    eta_bump_applied = True
                closing = max(0.0, prev_closing + incoming - demand_eff)
                out[w] = closing
                prev_closing = closing
            return out

        # Default (full demand): use workbook closing + ETA bump
        for w in horizon_weeks:
            wd = cov.get(w, {})
            closing = wd.get("closing", 0)

            yw_cur = current_year * 100 + w
            if (eta_yw is not None and eta_qty > 0
                    and eta_yw <= yw_cur and not eta_bump_applied):
                workbook_inc = wd.get("incoming", 0) or 0
                csv_inc = sku_incoming_map.get(yw_cur, 0)
                if workbook_inc < eta_qty * 0.5 and csv_inc < eta_qty * 0.5:
                    eta_bump_qty = eta_qty
                    eta_bump_from_week = w
                eta_bump_applied = True

            if eta_bump_from_week is not None and w >= eta_bump_from_week:
                closing += eta_bump_qty

            out[w] = closing
    else:
        # Synthetic projection (SKU not in POKRIVENOST)
        weekly_dem = (meta["monthly_avg"] / 4.33) if meta["monthly_avg"] else 0
        stock = float(meta.get("current_stock", 0) or 0)
        for w in horizon_weeks:
            yw_cur = current_year * 100 + w
            if eta_yw is not None and eta_qty > 0 and eta_yw <= yw_cur:
                stock += eta_qty
                eta_qty = 0
            csv_inc = sku_incoming_map.get(yw_cur, 0)
            stock += csv_inc
            stock = max(0.0, stock - weekly_dem)
            out[w] = stock
    return out


projection = {}     # sku -> {week: closing}
for sku, meta in skus_master.items():
    projection[sku] = project_sku(sku, meta, cov_data.get(sku))


# ============================================================
# 4. Deliverable flag per SKU per week
# ============================================================
def is_deliverable(closing: float, monthly_avg: float) -> bool:
    if monthly_avg <= 0:
        # Zero-sales SKU — trivially "deliverable" by formula (any stock OK)
        return True
    return closing > (monthly_avg / 3.0)


# Deliverable check: do we have ENOUGH STOCK TO FULFILL THIS WEEK'S
# DEMAND from available supply?
#
#   deliverable_W = (stock_at_start_W + incoming_W) >= demand_W
#
# Week 0 stock = workbook.Stock for current week.
# Subsequent weeks: start stock = closing of prev week (from projection).
# Falls back to monthly_avg/4.33 for missing demand and ETA pulses for
# fitness/borilačka/gadgets without POKRIVENOST entry.
delivery = {}
for sku, meta in skus_master.items():
    cov = cov_data.get(sku)
    weekly_dem_fallback = (meta["monthly_avg"] / 4.33) if meta["monthly_avg"] else 0
    sku_incoming_map = incoming_by_sku_yw.get(sku, {})
    weekly = {}
    for i, w in enumerate(horizon_weeks):
        # Start-of-week stock
        if i == 0:
            if cov:
                start_stock = cov.get(w, {}).get("stock", 0)
                if start_stock <= 0:
                    start_stock = float(meta.get("current_stock", 0) or 0)
            else:
                start_stock = float(meta.get("current_stock", 0) or 0)
        else:
            start_stock = projection[sku].get(horizon_weeks[i - 1], 0)
        # Demand & incoming for this week
        wd = cov.get(w, {}) if cov else {}
        demand = wd.get("demand", 0) or weekly_dem_fallback
        yw_cur = current_year * 100 + w
        # Optionally subtract VP on-top
        if EXCLUDE_VP:
            vp_subtract = vp_ontop_by_sku_yw.get(sku, {}).get(yw_cur, 0)
            demand = max(0, demand - vp_subtract)
        incoming = (wd.get("incoming", 0) or 0)
        csv_inc = sku_incoming_map.get(yw_cur, 0)
        incoming = max(incoming, csv_inc)    # use the larger (avoid double-count)
        # ETA from Isporučivost Excel (one-shot, propagated to projection
        # already; but here check if it arrives this week)
        if meta.get("eta_yw") == yw_cur and meta.get("eta_qty", 0) > 0:
            incoming += meta["eta_qty"]
        # Deliverable: can the week's demand be met from available stock?
        available = start_stock + incoming
        weekly[w] = 1 if (demand <= 0 or available >= demand) else 0
    delivery[sku] = weekly


# ============================================================
# 5. Per-tier per-week aggregation
# ============================================================
tier_skus = defaultdict(list)
for sku, meta in skus_master.items():
    tier_skus[meta["tier"]].append(sku)

tier_weekly_pct = defaultdict(dict)    # tier -> {week: pct}
for tier, skus in tier_skus.items():
    total = len(skus)
    if total == 0:
        continue
    for w in horizon_weeks:
        deliv = sum(delivery[s][w] for s in skus)
        tier_weekly_pct[tier][w] = deliv / total

# Current % (first horizon week)
current_pct = {tier: tier_weekly_pct[tier][horizon_weeks[0]] for tier in tier_skus}

# Hit week per tier
hit_week = {}
for tier in tier_skus:
    target = TARGETS[tier]
    hit_week[tier] = None
    for w in horizon_weeks:
        if tier_weekly_pct[tier][w] >= target:
            hit_week[tier] = w
            break

# ============================================================
# 6. Bottlenecks (never deliverable) + Risk SKUs (deliverable now,
# becomes non-deliverable in horizon)
# ============================================================
bottlenecks = []    # never deliverable in horizon
risks = []          # deliverable in W0 but non-deliverable in some W
for sku, meta in skus_master.items():
    weekly = delivery[sku]
    days_deliv = sum(weekly.values())
    if days_deliv == 0:
        bottlenecks.append({
            "sku": sku, "name": meta["name"], "tier": TIER_DISPLAY[meta["tier"]],
            "current_stock": meta["current_stock"],
            "monthly_avg": meta["monthly_avg"],
            "threshold": meta["monthly_avg"] / 3.0 if meta["monthly_avg"] else 0,
            "eta": ("CW" + str(meta["eta_yw"] % 100) if meta["eta_yw"] else "—"),
            "eta_qty": meta["eta_qty"],
        })
        continue
    # If currently deliverable but becomes non-deliverable at any future point
    if weekly[horizon_weeks[0]] == 1 and any(weekly[w] == 0 for w in horizon_weeks[1:]):
        first_drop = next(w for w in horizon_weeks[1:] if weekly[w] == 0)
        risks.append({
            "sku": sku, "name": meta["name"], "tier": TIER_DISPLAY[meta["tier"]],
            "current_stock": meta["current_stock"],
            "monthly_avg": meta["monthly_avg"],
            "first_drop_week": first_drop,
            "weeks_to_drop": horizon_weeks.index(first_drop),
            "eta": ("CW" + str(meta["eta_yw"] % 100) if meta["eta_yw"] else "—"),
            "eta_qty": meta["eta_qty"],
        })


# ============================================================
# 7. Write Excel report
# ============================================================
THIN = Side(border_style="thin", color="CCCCCC")
HEADER_FILL = PatternFill("solid", fgColor="0C2340")
GREEN_FILL = PatternFill("solid", fgColor="D4F4DD")
RED_FILL = PatternFill("solid", fgColor="FCD9D7")
YELLOW_FILL = PatternFill("solid", fgColor="FFF4D4")
ALT_FILL = PatternFill("solid", fgColor="F5F7FA")
WIN_FONT = Font(bold=True, color="10B981")
NEG_FONT = Font(bold=True, color="DC2626")

wb = openpyxl.Workbook()

# ---- Sheet 1: Summary ----
ws1 = wb.active
ws1.title = "Summary"
ws1.merge_cells("A1:F1")
title = ws1.cell(1, 1, f"Isporučivost Target Forecast — {len(horizon_weeks)}-tjedna projekcija "
                       f"(CW{horizon_weeks[0]} → CW{horizon_weeks[-1]})")
title.font = Font(bold=True, size=14, color="FFFFFF")
title.fill = HEADER_FILL
title.alignment = Alignment(horizontal="center", vertical="center")
ws1.row_dimensions[1].height = 28

ws1.cell(2, 1, "Source: Isporučivost xlsx + Coverage POKRIVENOST. "
                "Deliverable formula: closing_stock > monthly_avg / 3."
       ).font = Font(italic=True, color="666666")
ws1.merge_cells("A2:F2")

# Header
headers = ["Tier", "Total SKUs", "Current %", "Target %", "Hit week", "Status"]
for c, h in enumerate(headers, start=1):
    cc = ws1.cell(4, c, h)
    cc.font = Font(bold=True, color="FFFFFF")
    cc.fill = HEADER_FILL
    cc.alignment = Alignment(horizontal="center", vertical="center")
    cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)

row = 5
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    tot = len(tier_skus.get(tier, []))
    cur = current_pct.get(tier, 0)
    target = TARGETS[tier]
    hit = hit_week.get(tier)
    if cur >= target:
        status = "✅ Već iznad targeta"
        fill = GREEN_FILL
    elif hit is not None:
        status = f"✅ Hit CW{hit}"
        fill = GREEN_FILL
    else:
        status = "❌ Ne dosegnuto u horizontu"
        fill = RED_FILL
    values = [
        TIER_DISPLAY[tier], tot,
        f"{cur*100:.1f}%", f"{target*100:.0f}%",
        f"CW{hit}" if hit else "—",
        status,
    ]
    for c, v in enumerate(values, start=1):
        cc = ws1.cell(row, c, v)
        cc.fill = fill
        cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
        cc.alignment = Alignment(horizontal=("left" if c in (1, 6) else "center"))
        if c == 1:
            cc.font = Font(bold=True, size=11)
    row += 1

for i, w in enumerate([14, 12, 13, 10, 11, 30], start=1):
    ws1.column_dimensions[get_column_letter(i)].width = w

# Add brief commentary block
row += 2
ws1.cell(row, 1, "Pregled:").font = Font(bold=True, color="0C2340")
row += 1
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    cur = current_pct.get(tier, 0)
    target = TARGETS[tier]
    delta = cur - target
    if delta >= 0:
        msg = (f"{TIER_DISPLAY[tier]}: već iznad targeta za "
                f"{delta*100:+.1f}pp.")
    elif hit_week.get(tier):
        msg = (f"{TIER_DISPLAY[tier]}: doseciće {target*100:.0f}% "
                f"u CW{hit_week[tier]} "
                f"({horizon_weeks.index(hit_week[tier])} tjedana od danas).")
    else:
        ge = max(tier_weekly_pct[tier].values())
        msg = (f"{TIER_DISPLAY[tier]}: u 13-tjednom horizontu maksimum "
                f"je {ge*100:.1f}%, target {target*100:.0f}% nije dosegnuto. "
                "Treba intervencija na bottleneck SKU-ovima.")
    ws1.cell(row, 1, msg)
    ws1.merge_cells(start_row=row, start_column=1, end_row=row, end_column=6)
    row += 1

# ---- Sheet 2: Weekly trajectory ----
ws2 = wb.create_sheet("Weekly trajectory")
ws2.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(horizon_weeks) + 2)
title2 = ws2.cell(1, 1, "13-tjedna projekcija isporučivosti per tier")
title2.font = Font(bold=True, size=12, color="FFFFFF")
title2.fill = HEADER_FILL
title2.alignment = Alignment(horizontal="center")

headers2 = ["Tier", "Target"] + [f"CW{w}" for w in horizon_weeks]
for c, h in enumerate(headers2, start=1):
    cc = ws2.cell(3, c, h)
    cc.font = Font(bold=True, color="FFFFFF")
    cc.fill = HEADER_FILL
    cc.alignment = Alignment(horizontal="center")
    cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)

row = 4
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    target = TARGETS[tier]
    ws2.cell(row, 1, TIER_DISPLAY[tier]).font = Font(bold=True)
    ws2.cell(row, 2, f"{target*100:.0f}%").alignment = Alignment(horizontal="center")
    for j, w in enumerate(horizon_weeks):
        pct = tier_weekly_pct[tier].get(w, 0)
        cc = ws2.cell(row, 3 + j, f"{pct*100:.1f}%")
        cc.alignment = Alignment(horizontal="center")
        cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
        if pct >= target:
            cc.fill = GREEN_FILL
        elif pct >= target - 0.05:
            cc.fill = YELLOW_FILL
        else:
            cc.fill = RED_FILL
    row += 1

ws2.column_dimensions["A"].width = 12
ws2.column_dimensions["B"].width = 9
for i in range(3, 3 + len(horizon_weeks)):
    ws2.column_dimensions[get_column_letter(i)].width = 8
ws2.freeze_panes = "C4"

# ---- Sheet 3: Per-SKU projection ----
ws3 = wb.create_sheet("Per-SKU projection")
headers3 = ["SKU", "Name", "Tier", "Grupacija",
              "Current stock", "Monthly avg", "Threshold (m/3)",
              "ETA", "ETA qty"] + [f"CW{w}" for w in horizon_weeks] + ["Deliverable weeks"]
for c, h in enumerate(headers3, start=1):
    cc = ws3.cell(1, c, h)
    cc.font = Font(bold=True, color="FFFFFF")
    cc.fill = HEADER_FILL
    cc.alignment = Alignment(horizontal="center", wrap_text=True)
ws3.row_dimensions[1].height = 30

# Sort: tier (GOLD first) → name
sort_order = {"01 GOLD": 0, "02 SILVER": 1, "03 BRONZE": 2}
sku_sorted = sorted(skus_master.items(),
                     key=lambda kv: (sort_order.get(kv[1]["tier"], 9), kv[1]["name"]))

row = 2
for sku, meta in sku_sorted:
    threshold = meta["monthly_avg"] / 3.0 if meta["monthly_avg"] else 0
    eta_str = f"CW{meta['eta_yw'] % 100}" if meta["eta_yw"] else "—"
    fixed = [
        sku, meta["name"], TIER_DISPLAY[meta["tier"]], meta["grupacija"],
        int(meta["current_stock"]), int(meta["monthly_avg"]),
        round(threshold, 1),
        eta_str, int(meta["eta_qty"]),
    ]
    for c, v in enumerate(fixed, start=1):
        ws3.cell(row, c, v)
    deliv_count = 0
    for j, w in enumerate(horizon_weeks):
        closing = projection[sku].get(w, 0)
        is_d = delivery[sku][w]
        deliv_count += is_d
        cc = ws3.cell(row, len(fixed) + 1 + j, int(closing))
        cc.fill = GREEN_FILL if is_d else RED_FILL
        cc.alignment = Alignment(horizontal="right")
    ws3.cell(row, len(headers3), f"{deliv_count}/{len(horizon_weeks)}")
    row += 1

ws3.column_dimensions["A"].width = 12
ws3.column_dimensions["B"].width = 45
ws3.column_dimensions["C"].width = 10
ws3.column_dimensions["D"].width = 18
for i in range(5, 10):
    ws3.column_dimensions[get_column_letter(i)].width = 13
for i in range(10, 10 + len(horizon_weeks)):
    ws3.column_dimensions[get_column_letter(i)].width = 9
ws3.column_dimensions[get_column_letter(len(headers3))].width = 16
ws3.freeze_panes = "E2"

# ---- Sheet 4: Bottlenecks ----
ws4 = wb.create_sheet("Bottlenecks")
ws4.cell(1, 1, "SKU-ovi koji NIKAD nisu deliverable u horizontu (treba intervenirati)"
        ).font = Font(bold=True, size=12, color="FFFFFF")
ws4.cell(1, 1).fill = HEADER_FILL
ws4.merge_cells(start_row=1, start_column=1, end_row=1, end_column=9)

headers4 = ["SKU", "Name", "Tier", "Current stock", "Monthly avg", "Threshold", "ETA", "ETA qty", "Razlog"]
for c, h in enumerate(headers4, start=1):
    cc = ws4.cell(3, c, h)
    cc.font = Font(bold=True, color="FFFFFF")
    cc.fill = HEADER_FILL
    cc.alignment = Alignment(horizontal="center")
row = 4
bottlenecks.sort(key=lambda b: (sort_order.get(b["tier"], 9), -b["monthly_avg"]))
for b in bottlenecks:
    # Razlog inference
    if b["current_stock"] == 0 and b["eta_qty"] == 0:
        razlog = "Stock 0 i nema ETA — zatražiti PO"
    elif b["current_stock"] == 0 and b["eta_qty"] > 0:
        razlog = "Stock 0, ETA postoji ali kvantitet nedovoljan"
    elif b["monthly_avg"] > 0 and b["current_stock"] < b["threshold"]:
        razlog = f"Stock {int(b['current_stock'])} ispod thresholda {int(b['threshold'])}, demand previsok"
    else:
        razlog = "Demand model promijenio uvjete u horizontu"
    fixed = [
        b["sku"], b["name"], b["tier"],
        int(b["current_stock"]), int(b["monthly_avg"]),
        int(b["threshold"]),
        b["eta"], int(b["eta_qty"]),
        razlog,
    ]
    for c, v in enumerate(fixed, start=1):
        cc = ws4.cell(row, c, v)
        cc.fill = RED_FILL
        cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
    row += 1

ws4.column_dimensions["A"].width = 12
ws4.column_dimensions["B"].width = 45
ws4.column_dimensions["C"].width = 10
for i in range(4, 9):
    ws4.column_dimensions[get_column_letter(i)].width = 13
ws4.column_dimensions["I"].width = 50

# ---- Sheet 5: Risk SKUs ----
ws5 = wb.create_sheet("Risk SKUs")
ws5.cell(1, 1, "SKU-ovi trenutno deliverable, ALI padaju ispod thresholda u horizontu"
        ).font = Font(bold=True, size=12, color="FFFFFF")
ws5.cell(1, 1).fill = HEADER_FILL
ws5.merge_cells(start_row=1, start_column=1, end_row=1, end_column=8)

headers5 = ["SKU", "Name", "Tier", "Current stock", "Monthly avg", "First drop week", "Weeks until drop", "ETA"]
for c, h in enumerate(headers5, start=1):
    cc = ws5.cell(3, c, h)
    cc.font = Font(bold=True, color="FFFFFF")
    cc.fill = HEADER_FILL
    cc.alignment = Alignment(horizontal="center")
row = 4
risks.sort(key=lambda r: (r["weeks_to_drop"], sort_order.get(r["tier"], 9)))
for rk in risks:
    fixed = [
        rk["sku"], rk["name"], rk["tier"],
        int(rk["current_stock"]), int(rk["monthly_avg"]),
        f"CW{rk['first_drop_week']}", rk["weeks_to_drop"],
        rk["eta"],
    ]
    for c, v in enumerate(fixed, start=1):
        cc = ws5.cell(row, c, v)
        cc.fill = YELLOW_FILL
        cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
    row += 1

ws5.column_dimensions["A"].width = 12
ws5.column_dimensions["B"].width = 45
ws5.column_dimensions["C"].width = 10
for i in range(4, 9):
    ws5.column_dimensions[get_column_letter(i)].width = 13


# Safe save
def _safe_save(wb, path: Path) -> Path:
    try:
        wb.save(path)
        return path
    except PermissionError:
        i = 1
        while True:
            alt = path.with_name(f"{path.stem}_v{i}{path.suffix}")
            if not alt.exists():
                break
            i += 1
        wb.save(alt)
        return alt


written = _safe_save(wb, OUT)
print()
print(f"Generated: {written}  ({written.stat().st_size // 1024} KB)")
print()
print("CURRENT % — trenutno stanje:")
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    cur = current_pct.get(tier, 0)
    target = TARGETS[tier]
    print(f"  {TIER_DISPLAY[tier]:7s}: {cur*100:5.1f}%  (target {target*100:.0f}%)")
print()
print("HIT WEEK (kada ćemo doseći target):")
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    h = hit_week.get(tier)
    cur = current_pct.get(tier, 0)
    target = TARGETS[tier]
    if cur >= target:
        print(f"  {TIER_DISPLAY[tier]:7s}: ✅ već iznad targeta")
    elif h is not None:
        idx = horizon_weeks.index(h)
        print(f"  {TIER_DISPLAY[tier]:7s}: ✅ CW{h} ({idx} tjedana od danas)")
    else:
        ge = max(tier_weekly_pct[tier].values())
        print(f"  {TIER_DISPLAY[tier]:7s}: ❌ nije dosegnuto u 13 tjedana (max {ge*100:.1f}%)")
print()
print(f"Bottlenecks: {len(bottlenecks)} SKU-ova nikad ne dosegne deliverable")
print(f"Risk SKUs:   {len(risks)} SKU-ova padaju ispod thresholda u horizontu")