"""Isporučivost po Coverage stock-presence — pojednostavljena analiza.

Formula:
    deliverable[W] = opening_stock[W] > 0

Tj. "imamo li uopće taj artikl u opening stocku tog tjedna?" — bez
threshold-a (monthly_avg/3 i sl.). Čisto prisutnost stocka.

Filter:
    • SKU mora biti u Isporučivost listi (GOLD/SILVER/BRONZE)
    • SKU mora imati BAREM jedan non-zero (stock, demand, incoming) u
      horizont-u — inače se izbacuje kao "ne-relevantan"

Incoming dodatak:
    • Coverage POKRIVENOST Incoming row se koristi primarno
    • Za SKU-ove BEZ incoming-a u Coverage-u, primijeni ETA iz
      Isporučivost Excel-a (kolona K datum + L količina) ako postoji —
      bump-aj opening stock od ETA tjedna nadalje

Run: py build_isporucivost_coverage_stock_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_CoverageStock_Report.xlsx"
EMAIL_OUT = ROOT / "docs" / "Email_Uprava_Isporucivost_CoverageStock.docx"
OUT.parent.mkdir(parents=True, exist_ok=True)

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. Isporučivost master
# ============================================================
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 in scope")


# ============================================================
# 2. Coverage POKRIVENOST
# ============================================================
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_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:]

SKU_COL, LABEL_COL = 5, 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. Build opening_stock per SKU per week + apply ETA fallback
# ============================================================
def build_opening_stocks() -> dict:
    """Return {sku: {week: opening_stock}} for SKUs in scope.

    Primary: Coverage POKRIVENOST Stock row.
    Fallback: if SKU has no incoming in Coverage at all, AND has ETA in
    Isporučivost xlsx, bump opening stock from ETA week onwards by
    ETA qty.

    SKU is SKIPPED if all (stock, demand, incoming) are zero in horizon.
    """
    out = {}
    skipped_no_data = []
    for sku, meta in skus_master.items():
        cov = cov_data.get(sku)
        if not cov:
            # No POKRIVENOST entry — synthesize using current_stock + ETA + monthly_avg/4.33
            wd = (meta["monthly_avg"] / 4.33) if meta["monthly_avg"] else 0
            stock_init = float(meta["current_stock"])
            eta_qty_local = meta["eta_qty"]
            eta_yw_local = meta["eta_yw"]
            sku_open = {}
            prev = stock_init
            applied_eta = False
            any_nonzero = stock_init > 0 or wd > 0 or eta_qty_local > 0
            if not any_nonzero:
                skipped_no_data.append(sku)
                continue
            for i, w in enumerate(horizon_weeks):
                yw = current_year * 100 + w
                if i == 0:
                    sku_open[w] = prev
                else:
                    # apply ETA pulse before subtracting demand
                    if (not applied_eta and eta_yw_local
                            and eta_yw_local <= yw and eta_qty_local > 0):
                        prev = prev + eta_qty_local
                        applied_eta = True
                    prev = max(0.0, prev - wd)
                    sku_open[w] = prev
            out[sku] = sku_open
            continue

        # SKU has POKRIVENOST entry — collect raw values + filter
        horizon_totals = {"stock": 0, "demand": 0, "incoming": 0}
        for w in horizon_weeks:
            wd = cov.get(w, {})
            horizon_totals["stock"] += wd.get("stock", 0) or 0
            horizon_totals["demand"] += wd.get("demand", 0) or 0
            horizon_totals["incoming"] += wd.get("incoming", 0) or 0
        if (horizon_totals["stock"] == 0 and
                horizon_totals["demand"] == 0 and
                horizon_totals["incoming"] == 0):
            skipped_no_data.append(sku)
            continue

        # Default: opening_stock = Coverage Stock row per week
        sku_open = {}
        for w in horizon_weeks:
            sku_open[w] = cov.get(w, {}).get("stock", 0) or 0

        # ETA fallback — only if Coverage incoming is empty for this SKU
        if horizon_totals["incoming"] == 0 and meta["eta_yw"] and meta["eta_qty"] > 0:
            for w in horizon_weeks:
                yw = current_year * 100 + w
                if meta["eta_yw"] <= yw:
                    sku_open[w] = sku_open[w] + meta["eta_qty"]

        out[sku] = sku_open

    return out, skipped_no_data


opening_stocks, skipped_skus = build_opening_stocks()
print(f"  In-scope SKUs (with data): {len(opening_stocks)}")
print(f"  Skipped (no stock/demand/incoming): {len(skipped_skus)}")


# ============================================================
# 4. Deliverable + aggregation
# ============================================================
delivery = {sku: {w: 1 if v > 0 else 0 for w, v in os_w.items()}
            for sku, os_w in opening_stocks.items()}

# Per-tier aggregation
tier_skus = defaultdict(list)
for sku in opening_stocks:
    tier_skus[skus_master[sku]["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

current_pct = {tier: tier_pct[tier][horizon_weeks[0]] for tier in tier_skus}
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 + Risks
sort_order = {"01 GOLD": 0, "02 SILVER": 1, "03 BRONZE": 2}
bottlenecks = []
risks = []
for sku in opening_stocks:
    meta = skus_master[sku]
    weekly = delivery[sku]
    deliv_count = sum(weekly.values())
    if deliv_count == 0:
        bottlenecks.append({
            "sku": sku, "name": meta["name"], "tier": meta["tier"],
            "grupacija": meta["grupacija"],
            "monthly_avg": meta["monthly_avg"],
            "current_stock": meta["current_stock"],
            "eta_yw": meta["eta_yw"], "eta_qty": meta["eta_qty"],
        })
    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"],
            "current_stock": meta["current_stock"],
            "monthly_avg": meta["monthly_avg"],
            "first_drop_week": first_drop,
            "weeks_to_drop": horizon_weeks.index(first_drop),
            "eta_yw": meta["eta_yw"],
        })

print()
print(f"Quick result (CW{horizon_weeks[0]} → CW{horizon_weeks[-1]}):")
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    cur = current_pct.get(tier, 0)
    target = TARGETS[tier]
    n = len(tier_skus.get(tier, []))
    print(f"  {TIER_DISPLAY[tier]:7s}  ({n} SKUs)  current {cur*100:5.1f}%  target {target*100:.0f}%")


# ============================================================
# 5. Excel output
# ============================================================
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"
ws1.merge_cells("A1:F1")
title = ws1.cell(1, 1, "Isporučivost — Coverage stock presence  ·  "
                       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, "Formula: deliverable = opening_stock_tjedna > 0. "
                "Filter: SKU bez stock/demand/incoming u horizontu se izbacuje. "
                f"In-scope: {len(opening_stocks)} SKU-ova ({len(skipped_skus)} skipped)."
       ).font = Font(italic=True, color="666666")
ws1.merge_cells("A2:F2")

hdrs = ["Tier", "Total SKUs", "Current %", "Target", "Hit week", "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)

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]
    h = hit_week.get(tier)
    if cur >= target:
        status, fill = "✅ Već iznad targeta", GREEN_FILL
    elif h is not None:
        status, fill = f"✅ Hit CW{h}", GREEN_FILL
    else:
        status, fill = "❌ Ne dosegnuto u horizontu", RED_FILL
    values = [TIER_DISPLAY[tier], tot,
              f"{cur*100:.1f}%", f"{target*100:.0f}%",
              (f"CW{h}" if h 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, 11, 11, 9, 10, 32], start=1):
    ws1.column_dimensions[get_column_letter(i)].width = w


# --- 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-tj projekcija isporučivosti (opening_stock > 0)")
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]
    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_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 ---
ws3 = wb.create_sheet("Bottleneck SKUs")
ws3.cell(1, 1, "SKU-ovi koji NIKAD nemaju stock u horizontu (opening_stock = 0 svaki tjedan)"
        ).font = Font(bold=True, size=12, color="FFFFFF")
ws3.cell(1, 1).fill = HEADER_FILL
ws3.merge_cells("A1:H1")

hdrs3 = ["SKU", "Name", "Tier", "Grupacija", "Current stock",
         "Monthly avg", "ETA datum", "ETA količina"]
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

bottlenecks.sort(key=lambda b: (sort_order.get(b["tier"], 9), -b["monthly_avg"]))
row = 4
for b in bottlenecks:
    eta_str = f"CW{b['eta_yw'] % 100}" if b["eta_yw"] else "—"
    values = [b["sku"], b["name"], TIER_DISPLAY[b["tier"]], b["grupacija"],
              int(b["current_stock"]), int(b["monthly_avg"]),
              eta_str, int(b["eta_qty"])]
    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

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


# --- Sheet 4: Risk SKUs ---
ws4 = wb.create_sheet("Risk SKUs")
ws4.cell(1, 1, "SKU-ovi koji imaju stock danas, padaju na 0 u horizontu (early warning)"
        ).font = Font(bold=True, size=12, color="FFFFFF")
ws4.cell(1, 1).fill = HEADER_FILL
ws4.merge_cells("A1:H1")

hdrs4 = ["SKU", "Name", "Tier", "Current stock", "Monthly avg",
          "First drop week", "Weeks until drop", "ETA"]
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)

risks.sort(key=lambda r: (r["weeks_to_drop"], sort_order.get(r["tier"], 9)))
row = 4
for rk in risks:
    eta_str = f"CW{rk['eta_yw'] % 100}" if rk["eta_yw"] else "—"
    values = [rk["sku"], rk["name"], TIER_DISPLAY[rk["tier"]],
              int(rk["current_stock"]), int(rk["monthly_avg"]),
              f"CW{rk['first_drop_week']}", rk["weeks_to_drop"], eta_str]
    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

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


# --- Sheet 5: Missing GOLD-SILVER per week (Coverage-only) ---
ws5 = wb.create_sheet("Missing GOLD-SILVER per week")
ws5.merge_cells(start_row=1, start_column=1, end_row=1,
                  end_column=5 + len(horizon_weeks))
title5 = ws5.cell(1, 1,
    "GOLD/SILVER SKU-ovi koji nemaju opening stock (= 0) "
    f"u barem jednom tjednu · CW{horizon_weeks[0]} → CW{horizon_weeks[-1]}")
title5.font = Font(bold=True, size=12, color="FFFFFF")
title5.fill = HEADER_FILL
title5.alignment = Alignment(horizontal="center")

hdrs5 = ["SKU", "Name", "Tier", "Monthly avg", "ETA"] + [f"CW{w}" for w in horizon_weeks]
for c, h in enumerate(hdrs5, start=1):
    cc = ws5.cell(3, 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)
ws5.row_dimensions[3].height = 30

target_tiers = {"01 GOLD", "02 SILVER"}
missing_rows = []
for sku, sku_open in opening_stocks.items():
    meta = skus_master[sku]
    if meta["tier"] not in target_tiers: continue
    weekly = delivery[sku]
    n_missing = sum(1 for w in horizon_weeks if weekly[w] == 0)
    if n_missing == 0: continue
    missing_rows.append({
        "sku": sku, "name": meta["name"], "tier": meta["tier"],
        "monthly_avg": meta["monthly_avg"],
        "eta_yw": meta["eta_yw"], "n_missing": n_missing,
    })

missing_rows.sort(key=lambda s: (sort_order[s["tier"]], -s["n_missing"]))

row = 4
for s in missing_rows:
    sku = s["sku"]
    sku_open_map = opening_stocks[sku]
    sku_deliv = delivery[sku]
    eta_str = f"CW{s['eta_yw'] % 100}" if s["eta_yw"] else "—"
    fixed = [sku, s["name"], TIER_DISPLAY[s["tier"]],
              int(s["monthly_avg"]), eta_str]
    for c, v in enumerate(fixed, start=1):
        cc = ws5.cell(row, c, v)
        cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
        if c == 3:
            cc.font = Font(bold=True,
                             color=("BFA130" if s["tier"] == "01 GOLD" else "808080"))
    for j, w in enumerate(horizon_weeks):
        os_val = int(sku_open_map.get(w, 0))
        is_ok = sku_deliv[w] == 1
        cc = ws5.cell(row, 5 + 1 + j, os_val)
        cc.alignment = Alignment(horizontal="right")
        cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
        cc.fill = GREEN_FILL if is_ok else RED_FILL
        if not is_ok:
            cc.font = Font(bold=True, color="9B1B1B")
    row += 1

# Footer with per-week counts
row += 1
ws5.cell(row, 1, "Broj missing po tjednu:").font = Font(bold=True)
for j, w in enumerate(horizon_weeks):
    g_miss = sum(1 for s in missing_rows
                  if s["tier"] == "01 GOLD" and delivery[s["sku"]][w] == 0)
    s_miss = sum(1 for s in missing_rows
                  if s["tier"] == "02 SILVER" and delivery[s["sku"]][w] == 0)
    cc = ws5.cell(row, 5 + 1 + j, f"G:{g_miss} S:{s_miss}")
    cc.font = Font(bold=True, size=9, color="0C2340")
    cc.alignment = Alignment(horizontal="center")
    cc.fill = GROUP_FILL
    cc.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)

ws5.column_dimensions["A"].width = 12
ws5.column_dimensions["B"].width = 42
ws5.column_dimensions["C"].width = 10
ws5.column_dimensions["D"].width = 11
ws5.column_dimensions["E"].width = 9
for i in range(6, 6 + len(horizon_weeks)):
    ws5.column_dimensions[get_column_letter(i)].width = 9
ws5.freeze_panes = "F4"


def _safe_save(wb, 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: {written}  ({written.stat().st_size // 1024} KB)")

# ============================================================
# Email draft
# ============================================================
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)
    tp = doc.add_paragraph()
    tr = tp.add_run("Isporučivost — Coverage stock presence  ·  "
                     f"CW{horizon_weeks[0]} → CW{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 jednostavnijoj formuli — za "
        f"svaki SKU u svakom od sljedećih {len(horizon_weeks)} tjedana "
        f"provjeravam imamo li ga u opening stocku ({len(opening_stocks)} "
        f"SKU-ova u scope-u). SKU-ovi bez stocka/demanda/incoming-a u "
        f"horizontu su izbačeni iz analize ({len(skipped_skus)} kom). "
        f"Detalji u priloženoj datoteci ({OUT.name})."
    )

    doc.add_paragraph(f"Trenutno stanje (CW{horizon_weeks[0]}):").runs[0].bold = True
    for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
        cur = current_pct.get(tier, 0)
        target = TARGETS[tier]
        status = "✅" if cur >= target else "❌"
        doc.add_paragraph(
            f"{status}  {TIER_DISPLAY[tier]}:  {cur*100:.1f}%  (target {target*100:.0f}%)",
            style="List Bullet",
        )

    doc.add_paragraph("Glavni nalazi:").runs[0].bold = True
    not_hit = [TIER_DISPLAY[t] for t in TARGETS
                if current_pct.get(t, 0) < TARGETS[t] and hit_week.get(t) is None]
    if not_hit:
        doc.add_paragraph(
            f"U 13-tjednom horizontu **{', '.join(not_hit)}** ne dosežu zadane "
            "targete. Bez intervencije stock se troši, opening stock pada na 0 "
            "za sve više SKU-ova.", style="List Bullet")
    doc.add_paragraph(
        f"**{len(bottlenecks)} SKU-ova** nemaju stock u nijednom tjednu "
        "horizonta — top prioritet za PO (sheet 'Bottleneck SKUs').",
        style="List Bullet")
    doc.add_paragraph(
        f"**{len(risks)} SKU-ova** imaju stock danas ali padaju u horizontu "
        "— early warning (sheet 'Risk SKUs').",
        style="List Bullet")

    doc.add_paragraph()
    note = doc.add_paragraph()
    nr = note.add_run("Metodologija: ")
    nr.bold = True
    note.add_run(
        "deliverable = opening_stock_tjedna > 0 (Coverage POKRIVENOST Stock row). "
        "Za SKU-ove bez incoming-a u Coverage-u primjenjuje se ETA iz "
        "Isporučivost xlsx (kolona K + L). SKU-ovi bez stocka/demanda/"
        "incoming-a u horizontu se izbacuju (nisu aktivni u tracking-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")

    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"Email draft: {em}  ({em.stat().st_size // 1024} KB)")
except ImportError:
    print("⚠️ python-docx not available — email skipped.")

print("\nDone.")