"""Alternative isporučivost forecast — uses the SAME formula as the
Isporučivost Excel (column I: `=IF(stock > monthly_avg / 3, 1, 0)`)
applied to PROJECTED OPENING STOCK from the Coverage POKRIVENOST sheet
for each of the next 13 weeks.

Differences from build_isporucivost_uprava_report.py:
  • Deliverable formula = opening_stock_W > monthly_avg / 3
    (matches Excel snapshot logic, projected weekly)
  • opening_stock_W = Coverage POKRIVENOST `Stock` row for week W
    (recomputed for NO-VP scenario using reduced demand)
  • Output structure mirrors the previous report: Summary + Weekly
    trajectory + Bottlenecks + Risk + draft email.

Run: py build_isporucivost_opening_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_OpeningStock_Report.xlsx"
EMAIL_OUT = ROOT / "docs" / "Email_Uprava_Isporucivost_OpeningStock.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


# ============================================================
# Load 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")


# ============================================================
# Load Coverage POKRIVENOST — opening stock + demand + incoming per week
# ============================================================
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]}")


# ============================================================
# Load incoming_supply.csv — for SKUs NOT in POKRIVENOST
# ============================================================
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


# ============================================================
# Load vp_input.csv for NO-VP scenario
# ============================================================
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")


# ============================================================
# Per-scenario analysis using OPENING STOCK formula
# ============================================================
def compute_scenario(exclude_vp: bool) -> dict:
    """For each SKU x week, compute opening_stock and check
    deliverable = opening_stock > monthly_avg / 3."""
    delivery = {}        # sku -> {week: 0/1}
    opening_stock = {}   # sku -> {week: opening_stock value}

    for sku, meta in skus_master.items():
        cov = cov_data.get(sku)
        vp_map = vp_ontop_by_sku_yw.get(sku, {})
        threshold = meta["monthly_avg"] / 3.0 if meta["monthly_avg"] else 0
        # No threshold = treat as deliverable (idle SKU)
        is_idle = meta["monthly_avg"] <= 0

        sku_open = {}
        sku_delivery = {}

        # For FULL scenario:
        #   • SKU in POKRIVENOST → use Coverage's Stock row directly
        #   • SKU not in POKRIVENOST → simulate weekly:
        #       prev_open + weekly_incoming - weekly_demand
        #     using Isporučivost current_stock + monthly_avg/4.33 +
        #     ETA + incoming_supply.csv
        if not exclude_vp:
            if cov:
                # SKU in POKRIVENOST → trust workbook Stock row
                for w in horizon_weeks:
                    open_s = cov.get(w, {}).get("stock", 0)
                    if open_s <= 0 and w != horizon_weeks[0]:
                        prev_w_idx = horizon_weeks.index(w) - 1
                        prev_w = horizon_weeks[prev_w_idx]
                        open_s = sku_open.get(prev_w, 0)
                    sku_open[w] = open_s
                    sku_delivery[w] = 1 if (is_idle or open_s > threshold) else 0
            else:
                # SKU not in POKRIVENOST → synthetic projection
                weekly_demand = (meta["monthly_avg"] / 4.33) if meta["monthly_avg"] else 0
                csv_inc = incoming_by_sku_yw.get(sku, {})
                eta_yw_local = meta["eta_yw"]
                eta_qty_local = meta["eta_qty"]
                prev_open = float(meta["current_stock"])
                for i, w in enumerate(horizon_weeks):
                    yw_cur = current_year * 100 + w
                    if i == 0:
                        open_s = prev_open
                    else:
                        # apply previous week's depletion + incoming
                        prev_yw = current_year * 100 + horizon_weeks[i - 1]
                        prev_incoming = csv_inc.get(prev_yw, 0)
                        # ETA pulse on its own week (one-shot)
                        if (eta_yw_local is not None and eta_qty_local > 0
                                and eta_yw_local == prev_yw):
                            prev_incoming += eta_qty_local
                            eta_qty_local = 0
                        open_s = max(0.0, prev_open + prev_incoming - weekly_demand)
                    sku_open[w] = open_s
                    sku_delivery[w] = 1 if (is_idle or open_s > threshold) else 0
                    prev_open = open_s
        else:
            # NO-VP: recompute opening stocks
            csv_inc = incoming_by_sku_yw.get(sku, {})
            eta_yw_local = meta["eta_yw"]
            eta_qty_local = meta["eta_qty"]
            prev_open = None
            for i, w in enumerate(horizon_weeks):
                if i == 0:
                    if cov:
                        prev_open = cov.get(w, {}).get("stock", 0) or float(meta["current_stock"])
                    else:
                        prev_open = float(meta["current_stock"])
                    sku_open[w] = prev_open
                else:
                    prev_w = horizon_weeks[i - 1]
                    prev_w_data = cov.get(prev_w, {}) if cov else {}
                    if cov:
                        prev_demand = prev_w_data.get("demand", 0) or 0
                        prev_incoming = prev_w_data.get("incoming", 0) or 0
                    else:
                        # Synthetic fallback for non-Coverage SKUs
                        prev_demand = (meta["monthly_avg"] / 4.33) if meta["monthly_avg"] else 0
                        prev_incoming = csv_inc.get(current_year * 100 + prev_w, 0)
                        if (eta_yw_local is not None and eta_qty_local > 0
                                and eta_yw_local == current_year * 100 + prev_w):
                            prev_incoming += eta_qty_local
                            eta_qty_local = 0
                    prev_yw = current_year * 100 + prev_w
                    vp_sub = vp_map.get(prev_yw, 0)
                    eff_demand = max(0, prev_demand - vp_sub)
                    open_s = max(0.0, prev_open + prev_incoming - eff_demand)
                    sku_open[w] = open_s
                    prev_open = open_s
                sku_delivery[w] = 1 if (is_idle or sku_open[w] > threshold) else 0

        delivery[sku] = sku_delivery
        opening_stock[sku] = sku_open

    # Aggregate per tier
    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():
        if not skus: continue
        for w in horizon_weeks:
            deliv = sum(delivery[s][w] for s in skus)
            tier_pct[tier][w] = deliv / len(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

    current_pct = {tier: tier_pct[tier][horizon_weeks[0]] for tier in tier_skus}

    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"],
                "threshold": meta["monthly_avg"] / 3.0 if meta["monthly_avg"] else 0,
                "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),
            })

    return {
        "delivery": delivery, "opening_stock": opening_stock,
        "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 (opening stock formula)...")
res_full = compute_scenario(exclude_vp=False)
print("Computing NO-VP scenario...")
res_no_vp = compute_scenario(exclude_vp=True)

print()
print("Quick comparison — CW" + str(horizon_weeks[0]) + " (formula: opening_stock > monthly_avg/3):")
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    fp = res_full["current_pct"].get(tier, 0)
    nv = res_no_vp["current_pct"].get(tier, 0)
    target = TARGETS[tier]
    print(f"  {TIER_DISPLAY[tier]:7s}  FULL {fp*100:5.1f}%  NO-VP {nv*100:5.1f}%  target {target*100:.0f}%")


# ============================================================
# Write Excel (mirrors previous report structure)
# ============================================================
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()
ws1 = wb.active
ws1.title = "Summary"

ws1.merge_cells("A1:I1")
title = ws1.cell(1, 1, f"Isporučivost — OPENING STOCK formula (kao Excel kolona I)  ·  "
                       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 > monthly_avg / 3  "
                "(stock_at_start[W] iz Coverage POKRIVENOST)."
       ).font = Font(italic=True, color="666666")
ws1.merge_cells("A2:I2")

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, []))
    fp = res_full["current_pct"].get(tier, 0)
    nv = 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 fp >= 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 - fp) * 100
    values = [
        TIER_DISPLAY[tier], tot,
        f"{fp*100:.1f}%", (f"CW{h_full}" if h_full else "—"),
        f"{nv*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

row += 2
ws1.cell(row, 1, "Komentar:").font = Font(bold=True, color="0C2340")
row += 1
for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
    fp = res_full["current_pct"].get(tier, 0)
    nv = res_no_vp["current_pct"].get(tier, 0)
    target = TARGETS[tier]
    name = TIER_DISPLAY[tier]
    if fp >= target:
        msg = f"{name}: iznad targeta već u CW{horizon_weeks[0]} ({fp*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}: doseže {target*100:.0f}% u CW{h} ({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}: max FULL {max_full*100:.1f}% / bez VP {max_nv*100:.1f}%, "
               f"target {target*100:.0f}% nije dosegnuto.")
    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

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
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 — Opening stock formula, FULL vs NO-VP")
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, 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
ws3 = wb.create_sheet("Bottleneck SKUs")
ws3.cell(1, 1, "SKU-ovi koji NIKAD ne dosegnu deliverable (opening stock ≤ monthly_avg/3 svaki tjedan)"
        ).font = Font(bold=True, size=12, color="FFFFFF")
ws3.cell(1, 1).fill = HEADER_FILL
ws3.merge_cells("A1:I1")

hdrs3 = ["SKU", "Name", "Tier", "Grupacija",
         "Current stock", "Monthly avg", "Threshold (avg/3)",
         "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

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 = sorted(set(bot_skus_full) | set(bot_skus_nv),
                  key=lambda s: ({"01 GOLD": 0, "02 SILVER": 1, "03 BRONZE": 2}.get(
                      skus_master[s]["tier"], 9),
                      -skus_master[s]["monthly_avg"]))
row = 4
for sku in all_bot:
    meta = skus_master[sku]
    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 "—"
    values = [
        sku, meta["name"], TIER_DISPLAY[meta["tier"]], meta["grupacija"],
        int(meta["current_stock"]), int(meta["monthly_avg"]),
        int(threshold), eta_str, int(meta["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

widths3 = [12, 45, 10, 17, 13, 12, 14, 11, 12]
for i, w in enumerate(widths3, 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 su deliverable u CW" + str(horizon_weeks[0])
                + ", ali padaju ispod thresholda u horizontu"
        ).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",
          "Threshold", "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)

sorted_risks = sorted(res_full["risks"],
                       key=lambda r: (r["weeks_to_drop"],
                                        {"01 GOLD": 0, "02 SILVER": 1, "03 BRONZE": 2}.get(r["tier"], 9)))
row = 4
for rk in sorted_risks:
    meta = skus_master[rk["sku"]]
    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 "—"
    values = [
        rk["sku"], meta["name"], TIER_DISPLAY[meta["tier"]],
        int(meta["current_stock"]), int(meta["monthly_avg"]),
        int(threshold),
        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

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


# ============================================================
# Sheet 5 — GOLD / SILVER missing per week (operational pick-list)
# ============================================================
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 i SILVER SKU-ovi koji nisu isporučivi (opening_stock ≤ avg/3) "
                    "— po tjednu  ·  FULL scenarij  ·  SAMO POKRIVENOST")
title5.font = Font(bold=True, size=12, color="FFFFFF")
title5.fill = HEADER_FILL
title5.alignment = Alignment(horizontal="center")

# Header
hdrs5 = ["SKU", "Name", "Tier", "Monthly avg", "Threshold"] + [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

# Filter: GOLD + SILVER SKUs that fail in at least one horizon week
# AND are in POKRIVENOST (Coverage-tracked only — user request).
target_tiers = {"01 GOLD", "02 SILVER"}
missing_skus = []
for sku, meta in skus_master.items():
    if meta["tier"] not in target_tiers:
        continue
    if sku not in cov_data:
        continue    # skip SKUs without POKRIVENOST entry (synthetic projection)
    weekly = res_full["delivery"][sku]
    n_missing = sum(1 for w in horizon_weeks if weekly[w] == 0)
    if n_missing == 0:
        continue
    missing_skus.append({
        "sku": sku, "name": meta["name"], "tier": meta["tier"],
        "monthly_avg": meta["monthly_avg"],
        "threshold": meta["monthly_avg"] / 3.0 if meta["monthly_avg"] else 0,
        "n_missing": n_missing,
    })

# Sort: Gold first, then by number of missing weeks (most missing first)
sort_order = {"01 GOLD": 0, "02 SILVER": 1}
missing_skus.sort(key=lambda s: (sort_order[s["tier"]], -s["n_missing"]))

row = 4
for s in missing_skus:
    sku = s["sku"]
    sku_open_map = res_full["opening_stock"][sku]
    sku_deliv = res_full["delivery"][sku]
    fixed = [
        sku, s["name"], TIER_DISPLAY[s["tier"]],
        int(s["monthly_avg"]), int(s["threshold"]),
    ]
    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"))
    # Weekly cells: opening stock value, red if missing, green if OK
    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

# Add a footer row 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):
    gold_miss = sum(1 for s in missing_skus
                     if s["tier"] == "01 GOLD"
                     and res_full["delivery"][s["sku"]][w] == 0)
    silver_miss = sum(1 for s in missing_skus
                       if s["tier"] == "02 SILVER"
                       and res_full["delivery"][s["sku"]][w] == 0)
    cc = ws5.cell(row, 5 + 1 + j, f"G:{gold_miss} S:{silver_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)

# Column widths
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 = 11
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: 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)")


# ============================================================
# 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)

    title_p = doc.add_paragraph()
    tr = title_p.add_run("Isporučivost — projekcija po OPENING STOCK formuli (kao Excel kolona I)")
    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"Priložena je projekcija isporučivosti za sljedećih {len(horizon_weeks)} "
        f"tjedana (CW{horizon_weeks[0]} → CW{horizon_weeks[-1]}). Logika je "
        "ista kao formula u Excelu (kolona I): SKU je isporučiv ako je opening "
        "stock tog tjedna veći od mjesečnog avg / 3. Stock svakog tjedna "
        "uzimam iz Coverage tablice (Stock row). Detalji u priloženoj datoteci "
        f"({OUT.name})."
    )

    doc.add_paragraph("Trenutno stanje (CW" + str(horizon_weeks[0]) + "):").runs[0].bold = True
    for tier in ["01 GOLD", "02 SILVER", "03 BRONZE"]:
        fp = res_full["current_pct"].get(tier, 0)
        nv = res_no_vp["current_pct"].get(tier, 0)
        target = TARGETS[tier]
        status = "✅" if fp >= target else "❌"
        doc.add_paragraph(
            f"{status}  {TIER_DISPLAY[tier]}:  FULL {fp*100:.1f}%  ·  "
            f"NO-VP {nv*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 res_full["current_pct"].get(t, 0) < TARGETS[t]
                and res_full["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. "
            "Opening stock projektiranjem kroz Coverage padne ispod monthly_avg/3 "
            "za sve veći broj SKU-ova kako tjedni napreduju.",
            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** trajno ispod thresholda u cijelom horizontu — "
        "prioritet za PO odluku (sheet 'Bottleneck SKUs').",
        style="List Bullet",
    )

    n_risk = len(res_full["risks"])
    doc.add_paragraph(
        f"**{n_risk} SKU-ova** trenutno OK ali padaju u horizontu — "
        "early warning lista (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 > monthly_avg / 3.  "
        "Opening stock = `Stock` row iz Coverage POKRIVENOST za svaki tjedan. "
        "Za NO-VP scenarij opening stock se re-projicira oduzimajući VP on-top "
        "iz tjedne potražnje. Izvori: Coverage_W"
        + str(current_week) + ".xlsx + Isporučivost xlsx + vp_input.csv."
    )
    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"Generated email draft: {em}  ({em.stat().st_size // 1024} KB)")

except ImportError:
    print("\n⚠️ python-docx not available — email skipped.")

print("\nDone.")