"""Equipment promo analysis batch 2 — build equipment_promo_analysis_data.xlsx with 5 sheets."""
from __future__ import annotations

import sys
from pathlib import Path

import pandas as pd
import numpy as np

DATA = Path(__file__).resolve().parent
ROOT = DATA.parent
OUT = DATA / "equipment_promo_analysis_data.xlsx"
SKU_LIST = DATA / "_sku_batch2.txt"

# --- load batch2 SKU list ---
batch2 = [s.strip() for s in SKU_LIST.read_text().splitlines() if s.strip()]
print(f"Batch 2 SKUs: {len(batch2)}")

# --- assign group label per SKU based on prefix/name ---
def assign_group(sku: str, name: str) -> str:
    name_low = (name or "").lower()
    if sku.startswith("LRQ"):
        if "purevis" in name_low:
            return "LARQ PureVis"
        if "filter" in name_low:
            return "LARQ s filterom"
        if any(t in name_low for t in ["twist", "flip", "swig"]):
            return "LARQ Twist/Flip/Swig"
        return "LARQ dodaci"
    if sku.startswith("GMS"):
        return "Kinezioloska traka"
    if sku == "THG03001":
        return "Theragun PRO PLUS"
    if sku in ("THG03048", "THG03003", "THG23904"):
        return "Theragun Prime/G5/G6"
    if sku in ("THG03002", "THG23903", "THG23902"):
        return "Theragun Elite"
    if sku in ("THG03004", "THG03005"):
        return "Theragun Relief"
    if sku == "THG03049":
        return "Theragun Sense"
    if sku in ("THG03050", "THG23905", "THG23913"):
        return "Theragun Mini/Mini Plus"
    if sku in ("THG23901", "THG23914"):
        return "Theragun ostalo"
    if sku.startswith("THG"):
        return "Theragun ostalo"
    if sku.startswith("LMT"):
        return "LUMI Ice Bath"
    if sku == "TNT25825":
        return "Masazni pistolj mini"
    if sku.startswith("TNT257"):
        return "Traka za trcanje"
    if sku.startswith("AQP"):
        return "Aquaphor City boca"
    if sku.startswith("SJW"):
        for tag in ["Extro", "Intro", "Nitro", "Ortho", "Marvel", "Lip"]:
            if tag.lower() in name_low:
                return f"SafeJawz {tag}"
        return "SafeJawz ostalo"
    if sku in ("ATC06637", "ATC06638"):
        return "Trening prsluk"
    if sku in ("ATC06516", "ATC06517", "ATC06518", "ATC06519", "ATC06520", "ATC06521"):
        return "Girja gumirana"
    if sku.startswith("ATC065"):
        return "Bucica gumirana"
    if sku.startswith("FIT"):
        return "Tatami strunjaca"
    return "Other"

# --- name resolution from multiple sources ---
print("Loading SKU names from sales_detailed and SifrarnikArtikala...")
sd_names = (
    pd.read_csv(DATA / "sales_detailed.csv", usecols=["sku", "naziv"], low_memory=False)
    .dropna()
    .drop_duplicates("sku")
    .set_index("sku")["naziv"]
)
try:
    sifra = pd.read_excel(DATA / "SifrarnikArtikala.xlsx", usecols=["Šifra", "Naziv artikla/usluge"])
    sifra.columns = ["sku", "naziv"]
    sifra = sifra.dropna(subset=["sku"]).drop_duplicates("sku").set_index("sku")["naziv"]
except Exception as e:
    print("Sifrarnik load failed:", e)
    sifra = pd.Series(dtype=str)

def name_of(sku: str) -> str:
    if sku in sd_names.index:
        return str(sd_names.loc[sku])
    if sku in sifra.index:
        return str(sifra.loc[sku])
    return ""

sku_name = {s: name_of(s) for s in batch2}
sku_group = {s: assign_group(s, sku_name[s]) for s in batch2}
print(f"SKUs with names: {sum(1 for v in sku_name.values() if v)}/{len(batch2)}")

# --- load sales_detailed (used for sheets 1, 2, partial 5) ---
print("Loading sales_detailed.csv...")
sd = pd.read_csv(
    DATA / "sales_detailed.csv",
    usecols=[
        "date", "year", "week", "dokument", "tip_dok", "partner", "naziv_partnera",
        "sku", "naziv", "kategorija_artikla", "kolicina",
        "nabavna_vrijednost_eur", "ruc_eur", "vrijednost_eur", "drzava",
    ],
    low_memory=False,
)
sd["date"] = pd.to_datetime(sd["date"], errors="coerce")
sd["kolicina"] = pd.to_numeric(sd["kolicina"], errors="coerce")
sd["vrijednost_eur"] = pd.to_numeric(sd["vrijednost_eur"], errors="coerce")
sd["nabavna_vrijednost_eur"] = pd.to_numeric(sd["nabavna_vrijednost_eur"], errors="coerce")
sd["ruc_eur"] = pd.to_numeric(sd["ruc_eur"], errors="coerce")
print(f"  rows: {len(sd):,}, date range: {sd['date'].min()} -> {sd['date'].max()}")

SD_FROM = sd["date"].min()
SD_TO = sd["date"].max()
WINDOW_NOTE = f"sales_detailed window: {SD_FROM.date()} -> {SD_TO.date()} ({(SD_TO - SD_FROM).days} days)"
print(WINDOW_NOTE)

# Exclude internal/inter-company doc types from "sales transactions"
INTERNAL_DOCS = {"IRC", "IZD", "SRM", "SMR", "OTS"}  # IRC = inter-company etc.
sd_sales = sd[~sd["tip_dok"].isin(INTERNAL_DOCS)].copy()

# ============================================================
# Sheet 1: MARGIN PER TRANSACTION
# ============================================================
print("\n--- Sheet 1: Margin per transaction ---")
b1 = sd_sales[sd_sales["sku"].isin(batch2)].copy()
# Per-unit metrics: divide line value/cost/ruc by quantity
b1 = b1[b1["kolicina"] > 0].copy()
b1["unit_price"] = b1["vrijednost_eur"] / b1["kolicina"]
b1["unit_cost"] = b1["nabavna_vrijednost_eur"] / b1["kolicina"]
b1["unit_margin"] = b1["ruc_eur"] / b1["kolicina"]

agg = b1.groupby("sku", as_index=False).agg(
    total_transactions=("dokument", "count"),
    total_units=("kolicina", "sum"),
    avg_unit_sell_price=("unit_price", "mean"),
    avg_unit_cost=("unit_cost", "mean"),
    avg_unit_margin=("unit_margin", "mean"),
    total_revenue=("vrijednost_eur", "sum"),
    total_cost=("nabavna_vrijednost_eur", "sum"),
    total_margin=("ruc_eur", "sum"),
)
agg["avg_margin_pct"] = np.where(
    agg["avg_unit_sell_price"] > 0,
    agg["avg_unit_margin"] / agg["avg_unit_sell_price"] * 100,
    np.nan,
)

# include zero-sales SKUs
all_skus = pd.DataFrame({"sku": batch2})
sheet1 = all_skus.merge(agg, on="sku", how="left")
sheet1["naziv"] = sheet1["sku"].map(sku_name)
sheet1["grupa"] = sheet1["sku"].map(sku_group)
sheet1 = sheet1[[
    "sku", "naziv", "grupa",
    "total_transactions", "total_units",
    "avg_unit_sell_price", "avg_unit_cost", "avg_unit_margin", "avg_margin_pct",
    "total_revenue", "total_cost", "total_margin",
]].sort_values("total_margin", ascending=False, na_position="last")
print(f"  rows: {len(sheet1)} (with sales: {sheet1['total_transactions'].notna().sum()})")

# ============================================================
# Sheet 2: BASKET ATTACH RATE
# ============================================================
print("\n--- Sheet 2: Basket attach rate ---")
# Documents that contain at least one batch2 SKU
b2_doc_ids = sd_sales.loc[sd_sales["sku"].isin(batch2), "dokument"].dropna().unique()
print(f"  documents containing batch2 SKU: {len(b2_doc_ids):,}")

basket = sd_sales[sd_sales["dokument"].isin(b2_doc_ids)].copy()
# For each (equipment SKU, doc), all OTHER skus in same doc
eq_in_doc = (
    basket[basket["sku"].isin(batch2)][["dokument", "sku"]]
    .drop_duplicates()
    .rename(columns={"sku": "equipment_sku"})
)
others = basket.rename(columns={
    "sku": "attached_sku",
    "naziv": "attached_naziv",
    "kategorija_artikla": "attached_kategorija",
})[
    ["dokument", "attached_sku", "attached_naziv", "attached_kategorija",
     "kolicina", "vrijednost_eur", "ruc_eur"]
]
joined = eq_in_doc.merge(others, on="dokument", how="left")
joined = joined[joined["equipment_sku"] != joined["attached_sku"]].copy()

# Summary per equipment SKU
summary = joined.groupby("equipment_sku", as_index=False).agg(
    total_equipment_orders=("dokument", "nunique"),
    total_attached_lines=("attached_sku", "count"),
    total_attached_units=("kolicina", "sum"),
    total_attached_revenue=("vrijednost_eur", "sum"),
    total_attached_margin=("ruc_eur", "sum"),
)
summary["avg_items_per_basket"] = (
    summary["total_attached_lines"] / summary["total_equipment_orders"]
).round(2)
summary["avg_attached_margin_per_order"] = (
    summary["total_attached_margin"] / summary["total_equipment_orders"]
).round(2)
summary["avg_attached_revenue_per_order"] = (
    summary["total_attached_revenue"] / summary["total_equipment_orders"]
).round(2)

# all batch2 — including those with no attach data
basket_summary = all_skus.merge(
    summary.rename(columns={"equipment_sku": "sku"}), on="sku", how="left"
)
basket_summary["naziv"] = basket_summary["sku"].map(sku_name)
basket_summary["grupa"] = basket_summary["sku"].map(sku_group)
basket_summary = basket_summary[[
    "sku", "naziv", "grupa",
    "total_equipment_orders", "total_attached_lines", "total_attached_units",
    "avg_items_per_basket",
    "total_attached_revenue", "avg_attached_revenue_per_order",
    "total_attached_margin", "avg_attached_margin_per_order",
]].sort_values("avg_attached_margin_per_order", ascending=False, na_position="last")

# Top attached items per equipment SKU (frequency table)
top_attached = (
    joined.groupby(
        ["equipment_sku", "attached_sku", "attached_naziv", "attached_kategorija"],
        as_index=False,
        dropna=False,
    )
    .agg(
        frequency=("dokument", "nunique"),
        units=("kolicina", "sum"),
        revenue=("vrijednost_eur", "sum"),
        margin=("ruc_eur", "sum"),
    )
    .sort_values(["equipment_sku", "frequency"], ascending=[True, False])
)
# Keep top 10 per equipment SKU
top_attached = top_attached.groupby("equipment_sku", as_index=False).head(10)
top_attached["equipment_grupa"] = top_attached["equipment_sku"].map(sku_group)
top_attached["equipment_naziv"] = top_attached["equipment_sku"].map(sku_name)
top_attached = top_attached[[
    "equipment_sku", "equipment_naziv", "equipment_grupa",
    "attached_sku", "attached_naziv", "attached_kategorija",
    "frequency", "units", "revenue", "margin",
]]

print(f"  summary rows: {len(basket_summary)}")
print(f"  top_attached rows: {len(top_attached)}")

# ============================================================
# Sheet 3: PULL-FORWARD
# ============================================================
print("\n--- Sheet 3: Pull-forward ---")
sc = pd.read_csv(DATA / "sales_clean.csv", low_memory=False)
sc = sc[sc["sku"].isin(batch2)].copy()
sc["year"] = sc["year"].astype(int)
sc["week"] = sc["week"].astype(int)
sc["yw"] = sc["year"] * 100 + sc["week"]
# Build a continuous week index per SKU
sc["units"] = sc["qty_total"].fillna(0)
sc["ruc"] = sc["ruc_total"].fillna(0)

pc = pd.read_csv(DATA / "erp_promo_calendar.csv")
pc = pc[pc["sku"].isin(batch2)].copy()
pc["year"] = pc["year"].astype(int)
pc["week"] = pc["week"].astype(int)
pc["yw"] = pc["year"] * 100 + pc["week"]

# Identify contiguous promo periods per (sku, promo_types)
pc = pc.sort_values(["sku", "promo_types", "yw"])
periods_rows = []
for (sku, ptype), g in pc.groupby(["sku", "promo_types"]):
    yws = g["yw"].tolist()
    if not yws:
        continue
    start = prev = yws[0]
    for cur in yws[1:]:
        # adjacency check using yw arithmetic (handles year boundary roughly)
        if cur == prev + 1 or (cur == (prev // 100 + 1) * 100 + 1 and prev % 100 in (52, 53)):
            prev = cur
            continue
        periods_rows.append({"sku": sku, "promo_types": ptype, "start_yw": start, "end_yw": prev})
        start = prev = cur
    periods_rows.append({"sku": sku, "promo_types": ptype, "start_yw": start, "end_yw": prev})

periods = pd.DataFrame(periods_rows)
print(f"  promo periods (sku x ptype): {len(periods)}")

# Helper: enumerate yws from a center yw plus/minus N weeks (1-based weeks 1..52)
def yw_offset(yw: int, delta: int) -> int:
    year, week = divmod(yw, 100)
    week += delta
    while week < 1:
        year -= 1
        week += 52
    while week > 52:
        year += 1
        week -= 52
    return year * 100 + week

def yws_range(start_yw: int, end_yw: int) -> list[int]:
    out = []
    cur = start_yw
    safety = 0
    while cur <= end_yw and safety < 200:
        out.append(cur)
        cur = yw_offset(cur, 1)
        safety += 1
    return out

WINDOW = 8  # weeks pre/post
sc_idx = sc.set_index(["sku", "yw"])[["units", "ruc"]]

pull_rows = []
for r in periods.itertuples(index=False):
    pre_start = yw_offset(r.start_yw, -WINDOW)
    pre_end = yw_offset(r.start_yw, -1)
    post_start = yw_offset(r.end_yw, 1)
    post_end = yw_offset(r.end_yw, WINDOW)

    def collect(start_yw, end_yw, label):
        weeks = yws_range(start_yw, end_yw)
        units_sum = ruc_sum = 0.0
        n_weeks_data = 0
        for yw in weeks:
            try:
                row = sc_idx.loc[(r.sku, yw)]
                u = float(row["units"])
                rg = float(row["ruc"])
                units_sum += u
                ruc_sum += rg
                n_weeks_data += 1
            except KeyError:
                pass
        return {
            "sku": r.sku,
            "promo_types": r.promo_types,
            "start_yw": r.start_yw,
            "end_yw": r.end_yw,
            "period": label,
            "weeks_span": len(weeks),
            "weeks_with_data": n_weeks_data,
            "total_units": units_sum,
            "avg_units_per_week": units_sum / len(weeks) if weeks else 0,
            "total_ruc": ruc_sum,
            "avg_ruc_per_week": ruc_sum / len(weeks) if weeks else 0,
        }

    pull_rows.append(collect(pre_start, pre_end, "pre_promo"))
    pull_rows.append(collect(r.start_yw, r.end_yw, "during_promo"))
    pull_rows.append(collect(post_start, post_end, "post_promo"))

pull = pd.DataFrame(pull_rows)
pull["grupa"] = pull["sku"].map(sku_group)
pull["naziv"] = pull["sku"].map(sku_name)
# Flag whether RUC is trustworthy for this promo period.
# sales_clean.csv has ruc_total populated only from 2026 W1 onwards.
pull["ruc_reliable"] = (pull["start_yw"] // 100) >= 2026
pull = pull[[
    "sku", "naziv", "grupa", "promo_types", "start_yw", "end_yw", "period",
    "weeks_span", "weeks_with_data", "total_units", "avg_units_per_week",
    "total_ruc", "avg_ruc_per_week", "ruc_reliable",
]].sort_values(["sku", "start_yw", "period"])

# Pivoted summary: per (sku, promo period) pre/during/post side-by-side
pivot_base = pull.copy()
pivot_base["ruc_reliable"] = pivot_base["ruc_reliable"].astype(int)
pivot = pivot_base.pivot_table(
    index=["sku", "naziv", "grupa", "promo_types", "start_yw", "end_yw", "ruc_reliable"],
    columns="period",
    values=["avg_units_per_week", "avg_ruc_per_week"],
    aggfunc="first",
).reset_index()
pivot.columns = [
    "_".join([c for c in col if c]).strip("_") if isinstance(col, tuple) else col
    for col in pivot.columns
]
# Compute pull-forward ratio: post / pre (lower means cannibalization of future sales)
if "avg_units_per_week_pre_promo" in pivot.columns and "avg_units_per_week_post_promo" in pivot.columns:
    pivot["post_vs_pre_units_ratio"] = (
        pivot["avg_units_per_week_post_promo"]
        / pivot["avg_units_per_week_pre_promo"].replace(0, np.nan)
    ).round(3)
    pivot["uplift_during_vs_pre"] = (
        pivot["avg_units_per_week_during_promo"]
        / pivot["avg_units_per_week_pre_promo"].replace(0, np.nan)
    ).round(3)

print(f"  pull rows: {len(pull)}, pivot rows: {len(pivot)}")

# ============================================================
# Sheet 4: STOCK COVERAGE
# ============================================================
print("\n--- Sheet 4: Stock coverage ---")
stock = pd.read_csv(DATA / "stock.csv")
# Also include warehouse stocks per country if available
extra_stock = {}
for fname, col in [
    ("stock_stores.csv", "stores_hr"),
    ("stock_stores_at.csv", "stores_at"),
    ("stock_stores_slo.csv", "stores_slo"),
]:
    p = DATA / fname
    if p.exists():
        try:
            df_x = pd.read_csv(p)
            # heuristic: first col is sku, sum remaining numeric cols
            df_x = df_x.copy()
            num_cols = df_x.select_dtypes(include=[np.number]).columns.tolist()
            if num_cols:
                df_x[col] = df_x[num_cols].sum(axis=1)
                extra_stock[col] = df_x[["sku", col]]
        except Exception as e:
            print(f"  warn: {fname}: {e}")

# Last 12 weeks of sales — use max year/week from sales_clean
max_year = sc["year"].max()
max_week = sc[sc["year"] == max_year]["week"].max()
# build list of last 12 yws
last12 = []
cur_yw = int(max_year) * 100 + int(max_week)
for _ in range(12):
    last12.append(cur_yw)
    cur_yw = yw_offset(cur_yw, -1)

sc_last = sc[sc["yw"].isin(last12)].copy()
avg_weekly = sc_last.groupby("sku", as_index=False).agg(
    weeks_with_sales=("qty_total", lambda s: (s > 0).sum()),
    total_units_12w=("qty_total", "sum"),
)
avg_weekly["avg_weekly_sales_12w"] = (avg_weekly["total_units_12w"] / 12).round(2)

cov = pd.DataFrame({"sku": batch2})
cov = cov.merge(stock, on="sku", how="left")
cov = cov.merge(avg_weekly, on="sku", how="left")
for col, df_x in extra_stock.items():
    cov = cov.merge(df_x, on="sku", how="left")

cov["weeks_of_cover"] = np.where(
    cov["avg_weekly_sales_12w"] > 0,
    (cov["on_hand"].fillna(0) / cov["avg_weekly_sales_12w"]).round(1),
    np.nan,
)
cov["naziv"] = cov["sku"].map(sku_name)
cov["grupa"] = cov["sku"].map(sku_group)

front_cols = ["sku", "naziv", "grupa", "on_hand"]
extra_cols = [c for c in cov.columns if c.startswith("stores_")]
metric_cols = ["weeks_with_sales", "total_units_12w", "avg_weekly_sales_12w", "weeks_of_cover"]
cov = cov[front_cols + extra_cols + metric_cols].sort_values(
    "weeks_of_cover", ascending=False, na_position="last"
)
print(f"  rows: {len(cov)}")

# ============================================================
# Sheet 5: CUSTOMER ACQUISITION (proxy)
# ============================================================
print("\n--- Sheet 5: Customer acquisition (proxy) ---")
# B2B proxy: partner from sales_detailed.csv
# A "new partner" = first time that partner appears in the file overall
# A "returning partner" = had any prior dokument before this one
b2b_docs = sd[sd["sku"].isin(batch2) & ~sd["tip_dok"].isin(INTERNAL_DOCS)].copy()
b2b_docs = b2b_docs[b2b_docs["partner"].notna()].copy()

# global first appearance of each partner in the file
partner_first = (
    sd.dropna(subset=["partner", "date"]).groupby("partner")["date"].min().rename("first_seen")
)
b2b_docs = b2b_docs.merge(partner_first, left_on="partner", right_index=True, how="left")
b2b_docs["customer_type"] = np.where(
    b2b_docs["date"] == b2b_docs["first_seen"], "new", "returning"
)

# Group by (sku, type) — count distinct partners
b2b_summary = (
    b2b_docs.groupby(["sku", "customer_type"])["partner"]
    .nunique()
    .reset_index(name="partners")
)
total_per_sku = b2b_summary.groupby("sku")["partners"].sum().rename("total_partners")
b2b_summary = b2b_summary.merge(total_per_sku, on="sku")
b2b_summary["pct"] = (b2b_summary["partners"] / b2b_summary["total_partners"] * 100).round(1)
b2b_summary["naziv"] = b2b_summary["sku"].map(sku_name)
b2b_summary["grupa"] = b2b_summary["sku"].map(sku_group)
b2b_summary = b2b_summary[[
    "sku", "naziv", "grupa", "customer_type", "partners", "total_partners", "pct"
]].sort_values(["sku", "customer_type"])

# Webshop coupon proxy: count distinct order_ids (no customer_id available)
wb_proxy = pd.DataFrame()
try:
    wb = pd.read_csv(DATA / "webshop_coupon_orders.csv", low_memory=False)
    if not wb.empty and "sku" in wb.columns and "order_id" in wb.columns:
        wb_b2 = wb[wb["sku"].isin(batch2)].copy()
        # Treat each unique order_id with that SKU as one event.
        # We have no customer_id; we use order_id as the unit.
        wb_proxy = (
            wb_b2.groupby("sku")
            .agg(
                coupon_orders=("order_id", "nunique"),
                total_qty=("quantity", "sum") if "quantity" in wb_b2.columns else ("order_id", "count"),
            )
            .reset_index()
        )
        wb_proxy["naziv"] = wb_proxy["sku"].map(sku_name)
        wb_proxy["grupa"] = wb_proxy["sku"].map(sku_group)
        wb_proxy = wb_proxy[["sku", "naziv", "grupa", "coupon_orders", "total_qty"]]
except Exception as e:
    print("  webshop_coupon_orders error:", e)

print(f"  b2b rows: {len(b2b_summary)}, webshop proxy rows: {len(wb_proxy)}")

# ============================================================
# README sheet
# ============================================================
readme = pd.DataFrame({
    "Field": [
        "Generated",
        "Batch",
        "SKUs in scope",
        "sales_detailed window",
        "sales_clean window",
        "Note: customer_id",
        "Note: tip_dok filter",
        "Note: pull-forward window",
        "Note: RUC reliability",
        "Note: equipment categorization",
        "Source files",
    ],
    "Value": [
        pd.Timestamp.now().strftime("%Y-%m-%d %H:%M"),
        "Batch 2 (equipment / durables)",
        str(len(batch2)),
        f"{SD_FROM.date()} -> {SD_TO.date()} (~{(SD_TO - SD_FROM).days} days, NOT 12 months)",
        f"{int(sc['year'].min())} W{int(sc[sc['year']==sc['year'].min()]['week'].min())} -> {int(sc['year'].max())} W{int(sc[sc['year']==sc['year'].max()]['week'].max())}",
        "No customer_id field exists in any source. B2B sheet uses 'partner' as proxy; webshop sheet uses order_id (no customer linkage available).",
        f"Excluded internal/inter-company doc types from transactions: {sorted(INTERNAL_DOCS)}",
        f"+/- {WINDOW} weeks around each contiguous promo period from erp_promo_calendar.csv",
        "ruc_total in sales_clean.csv is zero for all of 2025 (data limitation). Pull-forward RUC numbers are only reliable for promos that start in 2026+. See ruc_reliable column.",
        "Each SKU is mapped to a group via prefix + name pattern matching (see equipment grupa column).",
        "sales_detailed.csv, sales_clean.csv, erp_promo_calendar.csv, stock.csv, stock_stores*.csv, webshop_coupon_orders.csv, SifrarnikArtikala.xlsx",
    ],
})

# ============================================================
# WRITE EXCEL
# ============================================================
print(f"\nWriting {OUT}...")
with pd.ExcelWriter(OUT, engine="openpyxl") as xl:
    readme.to_excel(xl, sheet_name="README", index=False)
    sheet1.to_excel(xl, sheet_name="1_Margin_per_transaction", index=False)
    basket_summary.to_excel(xl, sheet_name="2a_Basket_summary", index=False)
    top_attached.to_excel(xl, sheet_name="2b_Top_attached_items", index=False)
    pull.to_excel(xl, sheet_name="3a_Pull_forward_long", index=False)
    pivot.to_excel(xl, sheet_name="3b_Pull_forward_pivot", index=False)
    cov.to_excel(xl, sheet_name="4_Stock_coverage", index=False)
    b2b_summary.to_excel(xl, sheet_name="5a_B2B_partner_proxy", index=False)
    if not wb_proxy.empty:
        wb_proxy.to_excel(xl, sheet_name="5b_Webshop_coupon_proxy", index=False)

print("Done.")
print(f"Output: {OUT}")