"""Combined isporučivost forecast for management — runs BOTH scenarios
(full demand vs no-VP) in one pass and produces a single comparison Excel
plus a draft email.

Logic improvements over the standalone analyze script:
  • ETA (DATUM DOLASKA + Količina) from Isporučivost Excel is now applied
    ONLY to SKUs that have ZERO PO entries in Coverage workbook + the
    incoming_supply.csv across the entire 13-week horizon. Prevents
    double-counting for SKUs whose PO is already tracked.
  • Both scenarios in ONE Excel:
        Sheet 1  Summary comparison (with VP vs without VP)
        Sheet 2  Weekly trajectory (both scenarios side-by-side)
        Sheet 3  Bottleneck SKUs (consensus across both)
        Sheet 4  Risk SKUs
  • Draft email saved as docx for forwarding to management.

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

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

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

warnings.filterwarnings("ignore", category=UserWarning)

ROOT = Path(__file__).parent
DATA = ROOT / "data"
OUT = ROOT / "docs" / "Isporucivost_Uprava_Report.xlsx"
EMAIL_OUT = ROOT / "docs" / "Email_Uprava_Isporucivost.docx"
OUT.parent.mkdir(parents=True, exist_ok=True)

# ============================================================
# Config
# ============================================================
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 = {"01 GOLD": 0.97, "02 SILVER": 0.94, "03 BRONZE": 0.83}
TIER_DISPLAY = {"01 GOLD": "GOLD", "02 SILVER": "SILVER", "03 BRONZE": "BRONZE"}


def normalize_tier(value) -> str:
    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 ""


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


# ============================================================
# 1. Load Isporučivost master (SKU list + tier + monthly_avg + ETA)
# ============================================================
print(f"Loading {ISP_FILE.name}...")
wb_isp = openpyxl.load_workbook(ISP_FILE, data_only=True)
ws_isp = wb_isp["ISPORUČIVOST VP&B2C"]

skus_master = {}
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 = normalize_tier(ws_isp.cell(r, 7).value)
    if not tier: continue
    eta = ws_isp.cell(r, 11).value
    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(ws_isp.cell(r, 2).value or "")[:60],
        "grupacija": str(ws_isp.cell(r, 3).value or ""),
        "current_stock": _to_float(ws_isp.cell(r, 8).value),
        "monthly_avg": _to_float(ws_isp.cell(r, 10).value),
        "eta_yw": eta_yw,
        "eta_qty": _to_float(ws_isp.cell(r, 12).value),
    }
wb_isp.close()
print(f"  {len(skus_master)} SKUs")

# ============================================================
# 2. Load Coverage POKRIVENOST (weekly Stock/Demand/Incoming/Closing)
# ============================================================
print(f"Loading {COVERAGE_FILE.name}...")
wb_cov = openpyxl.load_workbook(COVERAGE_FILE, data_only=True, read_only=True)
ws_cov = wb_cov["POKRIVENOST"]
all_rows = list(ws_cov.iter_rows(values_only=True))
wb_cov.close()

# Week columns (header row idx 2)
week_cols = {}
if len(all_rows) >= 3:
    for c, v in enumerate(all_rows[2]):
        if isinstance(v, str) and v.lower().startswith("week"):
            try:
                wk = int(v.split()[-1])
                week_cols[wk] = c
            except ValueError:
                pass

iso_now = datetime.now().isocalendar()
current_year = int(iso_now[0])
current_week = int(iso_now[1])
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:
    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:]

# New Coverage layout: col 9 = label, col 8 = tier
SKU_COL, TIER_COL, LABEL_COL = 5, 8, 9
BLOCK = 5
cov_data = {}
for start_idx in range(3, len(all_rows), BLOCK):
    if start_idx + 3 >= len(all_rows): break
    row_stock = all_rows[start_idx]
    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()
    row_demand = all_rows[start_idx + 1]
    row_incoming = all_rows[start_idx + 2]
    row_closing = all_rows[start_idx + 3]
    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, horizon: CW{horizon_weeks[0]}..CW{horizon_weeks[-1]}")

# ============================================================
# 3. Load incoming_supply.csv (fresh PO data)
# ============================================================
incoming_by_sku_yw = defaultdict(lambda: defaultdict(float))
inc_csv = DATA / "incoming_supply.csv"
if inc_csv.exists():
    df = pd.read_csv(inc_csv)
    df.columns = [c.strip().lower() for c in df.columns]
    if {"sku", "year", "week", "qty"} <= set(df.columns):
        for _, r in 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"  Incoming PO entries: {sum(len(v) for v in incoming_by_sku_yw.values())} for "
      f"{len(incoming_by_sku_yw)} SKUs")

# ============================================================
# 4. Identify which SKUs have NO PO in coverage at all — use Isporučivost ETA only for those
# ============================================================
horizon_yws = {current_year * 100 + w for w in horizon_weeks}
no_po_skus = set()
for sku, meta in skus_master.items():
    cov = cov_data.get(sku, {})
    total_cov_incoming = sum(w.get("incoming", 0) for w in cov.values())
    total_csv_incoming = sum(q for yw, q in incoming_by_sku_yw.get(sku, {}).items()
                              if yw in horizon_yws)
    if total_cov_incoming == 0 and total_csv_incoming == 0:
        no_po_skus.add(sku)
print(f"  SKUs without any PO in horizon: {len(no_po_skus)} "
      f"(ETA from Isporučivost applied only to these)")

# ============================================================
# 5. Load vp_input.csv (wholesale on-top — to be optionally subtracted)
# ============================================================
vp_ontop_by_sku_yw = defaultdict(lambda: defaultdict(float))
vp_csv = DATA / "vp_input.csv"
if vp_csv.exists():
    df = pd.read_csv(vp_csv)
    sku_col_name = df.columns[0]
    cw_cols = [c for c in df.columns if isinstance(c, str) and c.upper().startswith("CW")]
    for _, r in df.iterrows():
        s = str(r[sku_col_name]).strip()
        for cw_col in cw_cols:
            try:
                q = float(r[cw_col] or 0)
            except (TypeError, ValueError):
                q = 0
            if q <= 0: continue
            w = int(cw_col[2:])
            yw = current_year * 100 + w
            vp_ontop_by_sku_yw[s][yw] += q
print(f"  VP on-top across {len(vp_ontop_by_sku_yw)} SKUs")


# ============================================================
# 6. Per-scenario analysis
# ============================================================
def compute_scenario(exclude_vp: bool) -> dict:
    """Run full analysis for one demand scenario."""
    delivery = {}      # sku -> {week: 0/1}
    projection = {}    # sku -> {week: closing_stock}

    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
        # ETA only if SKU has no PO anywhere
        eta_eligible = sku in no_po_skus
        eta_yw = meta["eta_yw"] if eta_eligible else None
        eta_qty = meta["eta_qty"] if eta_eligible else 0
        sku_csv_incoming = incoming_by_sku_yw.get(sku, {})
        vp_map = vp_ontop_by_sku_yw.get(sku, {})

        # Build per-week stock + delivery
        sku_proj = {}
        sku_delivery = {}
        prev_closing = None

        for i, w in enumerate(horizon_weeks):
            yw_cur = current_year * 100 + w
            wd = cov.get(w, {}) if cov else {}

            # Start-of-week stock
            if i == 0:
                start_stock = wd.get("stock", 0) if cov else 0
                if start_stock <= 0:
                    start_stock = float(meta["current_stock"])
            else:
                start_stock = prev_closing or 0

            # Demand for this week
            demand = wd.get("demand", 0) or weekly_dem_fallback
            if exclude_vp:
                vp_sub = vp_map.get(yw_cur, 0)
                demand = max(0, demand - vp_sub)

            # Incoming (max of workbook + CSV — avoid double-count)
            inc_cov = wd.get("incoming", 0) or 0
            inc_csv = sku_csv_incoming.get(yw_cur, 0)
            incoming = max(inc_cov, inc_csv)

            # ETA pulse if eligible (SKU has no PO anywhere)
            if eta_yw is not None and eta_qty > 0 and eta_yw <= yw_cur:
                incoming += eta_qty
                eta_qty = 0   # consume

            # Deliverable: available stock + incoming >= this week's demand
            available = start_stock + incoming
            sku_delivery[w] = 1 if (demand <= 0 or available >= demand) else 0

            # Closing stock for next week
            closing = max(0.0, available - demand)
            sku_proj[w] = closing
            prev_closing = closing

        projection[sku] = sku_proj
        delivery[sku] = sku_delivery

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

    tier_pct = defaultdict(dict)
    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_pct[tier][w] = deliv / total

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

    # Bottlenecks
    bottlenecks = []
    risks = []
    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": meta["tier"],
                "grupacija": meta["grupacija"],
                "current_stock": meta["current_stock"],
                "monthly_avg": meta["monthly_avg"],
                "eta_yw": meta["eta_yw"], "eta_qty": meta["eta_qty"],
                "is_no_po": sku in no_po_skus,
            })
        elif 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": meta["tier"],
                "grupacija": meta["grupacija"],
                "current_stock": meta["current_stock"],
                "monthly_avg": meta["monthly_avg"],
                "first_drop_week": first_drop,
                "weeks_to_drop": horizon_weeks.index(first_drop),
            })

    current_pct = {tier: tier_pct[tier][horizon_weeks[0]] for tier in tier_skus}
    return {
        "delivery": delivery,
        "projection": projection,
        "tier_pct": tier_pct,
        "hit_week": hit_week,
        "current_pct": current_pct,
        "bottlenecks": bottlenecks,
        "risks": risks,
        "tier_skus": tier_skus,
    }


print()
print("Computing FULL demand scenario...")
res_full = compute_scenario(exclude_vp=False)
print("Computing NO-VP scenario...")
res_no_vp = compute_scenario(exclude_vp=True)

print()
print("Quick comparison — CW20:")
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    full_pct = res_full["current_pct"].get(tier, 0)
    nv_pct = res_no_vp["current_pct"].get(tier, 0)
    target = TARGETS[tier]
    print(f"  {TIER_DISPLAY[tier]:7s}  full {full_pct*100:5.1f}%  "
          f"no-VP {nv_pct*100:5.1f}%  target {target*100:.0f}%")


# ============================================================
# 7. Write combined Excel
# ============================================================
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")
GROUP_FILL = PatternFill("solid", fgColor="EAF1F8")

wb = openpyxl.Workbook()

# --- Sheet 1: Summary ---
ws1 = wb.active
ws1.title = "Summary"

# Title
ws1.merge_cells("A1:I1")
title = ws1.cell(1, 1, f"Isporučivost Target Forecast — "
                       f"{len(horizon_weeks)}-tjedna projekcija (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, "Usporedba: FULL demand (B2B + B2C) vs SAMO RETAIL+WEB (bez VP on-top). "
                "Deliverable formula: (stock_start + incoming) ≥ demand za taj tjedan."
       ).font = Font(italic=True, color="666666")
ws1.merge_cells("A2:I2")

# Headers
hdrs = ["Tier", "Total SKUs",
        "Current % (FULL)", "Hit week (FULL)",
        "Current % (NO-VP)", "Hit week (NO-VP)",
        "Target", "Δ vs FULL", "Status"]
for c, h in enumerate(hdrs, start=1):
    cc = ws1.cell(4, c, h)
    cc.font = Font(bold=True, color="FFFFFF")
    cc.fill = HEADER_FILL
    cc.alignment = Alignment(horizontal="center", wrap_text=True)
    cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
ws1.row_dimensions[4].height = 36

row = 5
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    tot = len(res_full["tier_skus"].get(tier, []))
    full_pct = res_full["current_pct"].get(tier, 0)
    nv_pct = res_no_vp["current_pct"].get(tier, 0)
    target = TARGETS[tier]
    h_full = res_full["hit_week"].get(tier)
    h_nv = res_no_vp["hit_week"].get(tier)

    if full_pct >= target:
        status, fill = "✅ Već iznad targeta", GREEN_FILL
    elif h_full is not None:
        status, fill = f"✅ Hit CW{h_full}", GREEN_FILL
    elif h_nv is not None:
        status, fill = f"⚠️ Hit samo bez VP (CW{h_nv})", YELLOW_FILL
    else:
        status, fill = "❌ Ne dosegnuto", RED_FILL

    delta = (nv_pct - full_pct) * 100
    values = [
        TIER_DISPLAY[tier], tot,
        f"{full_pct*100:.1f}%", (f"CW{h_full}" if h_full else "—"),
        f"{nv_pct*100:.1f}%", (f"CW{h_nv}" if h_nv else "—"),
        f"{target*100:.0f}%",
        f"+{delta:.1f}pp" if delta > 0 else f"{delta:.1f}pp",
        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, 9) else "center"))
        if c == 1:
            cc.font = Font(bold=True, size=11)
    row += 1

# Commentary block
row += 2
ws1.cell(row, 1, "Komentar:").font = Font(bold=True, color="0C2340")
row += 1
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    full_pct = res_full["current_pct"].get(tier, 0)
    nv_pct = res_no_vp["current_pct"].get(tier, 0)
    target = TARGETS[tier]
    name = TIER_DISPLAY[tier]
    if full_pct >= target:
        msg = f"{name}: iznad targeta već u CW{horizon_weeks[0]} (full {full_pct*100:.1f}%, target {target*100:.0f}%)."
    elif res_full["hit_week"].get(tier) is not None:
        h = res_full["hit_week"][tier]
        idx = horizon_weeks.index(h)
        msg = (f"{name}: full-demand scenario doseže {target*100:.0f}% u CW{h} "
               f"({idx} tjedana od danas).")
    else:
        max_full = max(res_full["tier_pct"][tier].values())
        max_nv = max(res_no_vp["tier_pct"][tier].values())
        msg = (f"{name}: u 13-tjednom horizontu maksimum FULL {max_full*100:.1f}% "
               f"/ bez VP {max_nv*100:.1f}%, target {target*100:.0f}% nije dosegnuto u oba "
               "scenarija. Intervencija na bottleneck SKU-ovima.")
    ws1.cell(row, 1, msg).font = Font(size=11)
    ws1.merge_cells(start_row=row, start_column=1, end_row=row, end_column=9)
    row += 1

# Column widths
widths = [14, 11, 16, 14, 16, 14, 9, 13, 30]
for i, w in enumerate(widths, start=1):
    ws1.column_dimensions[get_column_letter(i)].width = w


# --- Sheet 2: Weekly trajectory (both scenarios stacked) ---
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 — FULL demand vs BEZ VP on-top")
title2.font = Font(bold=True, size=12, color="FFFFFF")
title2.fill = HEADER_FILL
title2.alignment = Alignment(horizontal="center")

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

row = 4
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    target = TARGETS[tier]
    # Group header
    ws2.cell(row, 1, f"━ {TIER_DISPLAY[tier]} ━").font = Font(bold=True, color="0C2340")
    ws2.cell(row, 1).fill = GROUP_FILL
    ws2.merge_cells(start_row=row, start_column=1, end_row=row, end_column=len(hdr))
    row += 1
    for label, scen in [("FULL", res_full), ("NO VP", res_no_vp)]:
        ws2.cell(row, 1, f"  {label}").font = Font(bold=True)
        ws2.cell(row, 2, f"{target*100:.0f}%").alignment = Alignment(horizontal="center")
        for j, w in enumerate(horizon_weeks):
            pct = scen["tier_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: Bottlenecks (consensus — appears in either scenario) ---
ws3 = wb.create_sheet("Bottleneck SKUs")
ws3.cell(1, 1, "SKU-ovi koji NIKAD ne dosegnu deliverable u 13-tj horizontu "
                "(prioritet za PO — uprava donosi odluku)"
        ).font = Font(bold=True, size=12, color="FFFFFF")
ws3.cell(1, 1).fill = HEADER_FILL
ws3.merge_cells("A1:K1")

# Merge bottlenecks from both
bot_skus_full = {b["sku"]: b for b in res_full["bottlenecks"]}
bot_skus_nv = {b["sku"]: b for b in res_no_vp["bottlenecks"]}
all_bot_skus = set(bot_skus_full) | set(bot_skus_nv)

hdrs3 = ["SKU", "Name", "Tier", "Grupacija", "Current stock", "Monthly avg",
         "ETA datum", "ETA količina", "Bottleneck FULL", "Bottleneck NO-VP", "Razlog"]
for c, h in enumerate(hdrs3, start=1):
    cc = ws3.cell(3, c, h)
    cc.font = Font(bold=True, color="FFFFFF")
    cc.fill = HEADER_FILL
    cc.alignment = Alignment(horizontal="center", wrap_text=True)
ws3.row_dimensions[3].height = 32

sort_order = {"01 GOLD": 0, "02 SILVER": 1, "03 BRONZE": 2}
sorted_bot = sorted(all_bot_skus,
                     key=lambda s: (sort_order.get(skus_master[s]["tier"], 9),
                                      -skus_master[s]["monthly_avg"]))

row = 4
for sku in sorted_bot:
    meta = skus_master[sku]
    in_full = sku in bot_skus_full
    in_nv = sku in bot_skus_nv
    is_no_po = sku in no_po_skus
    # Razlog
    if meta["current_stock"] == 0 and meta["eta_qty"] == 0:
        razlog = "Stock 0, nema ETA — zatražiti PO odmah"
    elif meta["current_stock"] == 0 and meta["eta_qty"] > 0:
        razlog = "Stock 0, ETA postoji ali nedovoljan ili predaleko"
    elif is_no_po:
        razlog = "Nema PO u horizon-u, stock se troši ispod demanda"
    else:
        razlog = "PO postoji ali ne stiže prije nego što stock padne"
    eta_str = f"CW{meta['eta_yw'] % 100}" if meta["eta_yw"] else "—"
    values = [
        sku, meta["name"], TIER_DISPLAY[meta["tier"]], meta["grupacija"],
        int(meta["current_stock"]), int(meta["monthly_avg"]),
        eta_str, int(meta["eta_qty"]),
        "❌" if in_full else "—", "❌" if in_nv else "—",
        razlog,
    ]
    for c, v in enumerate(values, start=1):
        cc = ws3.cell(row, c, v)
        cc.fill = RED_FILL
        cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
    row += 1

widths3 = [12, 45, 10, 17, 13, 12, 11, 12, 14, 14, 50]
for i, w in enumerate(widths3, start=1):
    ws3.column_dimensions[get_column_letter(i)].width = w


# --- Sheet 4: Risk SKUs (deliverable now but drop in horizon — FULL scenario) ---
ws4 = wb.create_sheet("Risk SKUs")
ws4.cell(1, 1, "SKU-ovi koji su trenutno deliverable, ali padaju ispod u horizontu "
                "(early warning — naručivati prije nego što padnu)"
        ).font = Font(bold=True, size=12, color="FFFFFF")
ws4.cell(1, 1).fill = HEADER_FILL
ws4.merge_cells("A1:I1")

hdrs4 = ["SKU", "Name", "Tier", "Current stock", "Monthly avg",
          "First drop week", "Weeks until drop", "ETA datum", "ETA količina"]
for c, h in enumerate(hdrs4, start=1):
    cc = ws4.cell(3, c, h)
    cc.font = Font(bold=True, color="FFFFFF")
    cc.fill = HEADER_FILL
    cc.alignment = Alignment(horizontal="center", wrap_text=True)

sorted_risks = sorted(res_full["risks"],
                       key=lambda r: (r["weeks_to_drop"], sort_order.get(r["tier"], 9)))
row = 4
for rk in sorted_risks:
    meta = skus_master[rk["sku"]]
    eta_str = f"CW{meta['eta_yw'] % 100}" if meta["eta_yw"] else "—"
    values = [
        rk["sku"], meta["name"], TIER_DISPLAY[meta["tier"]],
        int(meta["current_stock"]), int(meta["monthly_avg"]),
        f"CW{rk['first_drop_week']}", rk["weeks_to_drop"],
        eta_str, int(meta["eta_qty"]),
    ]
    for c, v in enumerate(values, start=1):
        cc = ws4.cell(row, c, v)
        cc.fill = YELLOW_FILL
        cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
    row += 1

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


# Save Excel
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(f"\nGenerated Excel: {written}  ({written.stat().st_size // 1024} KB)")


# ============================================================
# 8. Draft email (.docx)
# ============================================================
try:
    from docx import Document
    from docx.shared import Pt, RGBColor

    doc = Document()
    style = doc.styles["Normal"]
    style.font.name = "Calibri"
    style.font.size = Pt(11)

    title_p = doc.add_paragraph()
    tr = title_p.add_run("Isporučivost — stanje i projekcija do CW" + str(horizon_weeks[-1]))
    tr.bold = True
    tr.font.size = Pt(14)
    tr.font.color.rgb = RGBColor(0x0C, 0x23, 0x40)

    doc.add_paragraph("Poštovani,")
    doc.add_paragraph(
        f"Pripremio sam projekciju isporučivosti po GOLD / SILVER / BRONZE tieru za "
        f"sljedećih {len(horizon_weeks)} tjedana (CW{horizon_weeks[0]} → CW{horizon_weeks[-1]}). "
        "Analizu sam radio u dva scenarija — s punim demandom (B2B + B2C) i bez VP "
        "on-top potražnje (samo retail + web). Detalji u priloženoj Excel datoteci "
        f"({OUT.name})."
    )

    # Trenutno stanje
    doc.add_paragraph("Trenutno stanje (CW" + str(horizon_weeks[0]) + "):").runs[0].bold = True
    for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
        full_pct = res_full["current_pct"].get(tier, 0)
        nv_pct = res_no_vp["current_pct"].get(tier, 0)
        target = TARGETS[tier]
        status = "✅" if full_pct >= target else "❌"
        doc.add_paragraph(
            f"{status}  {TIER_DISPLAY[tier]}:  "
            f"FULL {full_pct*100:.1f}%  ·  NO-VP {nv_pct*100:.1f}%  ·  target {target*100:.0f}%",
            style="List Bullet",
        )

    # Glavni nalazi
    doc.add_paragraph("Glavni nalazi:").runs[0].bold = True

    not_hit_tiers = [TIER_DISPLAY[t] for t in TARGETS
                      if res_full["current_pct"].get(t, 0) < TARGETS[t]
                      and res_full["hit_week"].get(t) is None]
    if not_hit_tiers:
        doc.add_paragraph(
            f"U 13-tjednom horizontu **{', '.join(not_hit_tiers)}** ne dosežu zadane targete. "
            "Razlog: stock za većinu SKU-ova depletira jer nema dovoljno scheduled PO-ova.",
            style="List Bullet",
        )

    n_bot = len(set(b["sku"] for b in res_full["bottlenecks"]) |
                set(b["sku"] for b in res_no_vp["bottlenecks"]))
    doc.add_paragraph(
        f"**{n_bot} SKU-ova** ne dosežu deliverable status u cijelom horizontu — "
        "to su artikli koji bez novog PO-a fizički ne mogu zadovoljiti tjednu potražnju. "
        "Detaljnu listu s razlogom (stock 0 / ETA postoji ali nedovoljna / PO nema) "
        "vidite u sheet-u 'Bottleneck SKUs'.",
        style="List Bullet",
    )

    n_risk = len(res_full["risks"])
    doc.add_paragraph(
        f"**{n_risk} SKU-ova** su trenutno deliverable, ALI padaju ispod thresholda u "
        "horizontu — early warning lista za proaktivno naručivanje (sheet 'Risk SKUs').",
        style="List Bullet",
    )

    full_avg_drop = (res_full["current_pct"].get("02 SILVER", 0) -
                      res_full["tier_pct"]["02 SILVER"].get(horizon_weeks[-1], 0)) * 100
    doc.add_paragraph(
        f"Trend: SILVER tier pada s {res_full['current_pct'].get('02 SILVER', 0)*100:.1f}% "
        f"na {res_full['tier_pct']['02 SILVER'].get(horizon_weeks[-1], 0)*100:.1f}% do "
        f"CW{horizon_weeks[-1]} (−{full_avg_drop:.0f}pp). Slično i Bronze. Bez intervencije "
        "ćemo izgubiti i Silver/Bronze targete koje trenutno zadovoljavamo.",
        style="List Bullet",
    )

    # Preporuka
    doc.add_paragraph("Preporuka:").runs[0].bold = True
    doc.add_paragraph(
        "Hitno donijeti odluku o PO-u za bottleneck SKU-ove (lista u Excel-u). "
        "Procjenjujemo da bi pokrivanje 11-12 ključnih GOLD SKU-ova bilo dovoljno "
        "da GOLD tier dosegne 97% target u sljedećih 4-6 tjedana.",
        style="List Bullet",
    )
    doc.add_paragraph(
        "Paralelno provjeriti datume i količine na ETA-ovima u Isporučivost listi — "
        "za fitness/borilačku/gadget kategoriju gdje nema PO-a u glavnom Coverage sustavu.",
        style="List Bullet",
    )

    # Methodology note
    doc.add_paragraph()
    note = doc.add_paragraph()
    nr = note.add_run("Metodologija: ")
    nr.bold = True
    note.add_run(
        "isporučivost = (stock_na_početku_tjedna + incoming_taj_tjedan) ≥ "
        "demand_taj_tjedan. Podaci: Coverage_W"
        + str(current_week) + ".xlsx + incoming_supply.csv. ETA iz tablice "
        "Isporučivosti se primjenjuje samo na SKU-ove BEZ PO-a u Coverage-u."
    )
    note.runs[0].font.size = Pt(9)
    note.runs[1].font.size = Pt(9)
    note.runs[1].font.color.rgb = RGBColor(0x66, 0x66, 0x66)

    doc.add_paragraph()
    doc.add_paragraph("Srdačno,")
    doc.add_paragraph("Lovro")

    # Safe save email
    def _safe_save_docx(d, path):
        try:
            d.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
            d.save(alt)
            return alt

    em = _safe_save_docx(doc, EMAIL_OUT)
    print(f"Generated email draft: {em}  ({em.stat().st_size // 1024} KB)")

except ImportError:
    print("\n⚠️ python-docx not available — skipping email draft generation.")
    print("   Install with: py -m pip install python-docx --break-system-packages")

print("\nDone.")