"""Transaction-window diagnostics and traceable, fixed-capacity assortment."""

import csv
import re
from collections import Counter, defaultdict

import numpy as np
import pandas as pd

from analyze_private import ROOT, DATA, aggregate, grouped, records, load_json, FLOORS
from release_config import OUTPUT, prepare

MONTHS = [str(m) for m in pd.period_range("2025-09", "2026-08", freq="M")]
CAPACITY = 122
CAT_RULES = {
    "保温饮水杯": "以真空保温饮水为主功能；通勤、儿童、运动、咖啡饮用、钛材与容量作为属性。",
    "保温壶": "以储水、分享及旅行大容量保温为主功能；桌面与户外作为场景属性。",
    "随行饮水杯": "常温随行饮水杯与运动水壶；玻璃、塑料及儿童作为属性。",
    "日用饮水杯": "通用桌面杯、马克杯、口杯；材质、花色和组合件数作为属性。",
    "冲泡茶器": "泡茶杯、茶水分离杯、茶壶、盖碗及茶具组合；套装按主功能归类。",
    "咖啡杯碟": "专用咖啡杯碟；真空保温随行咖啡杯进入保温饮水杯。",
    "手动咖啡器具": "手冲、摩卡、法压、冷萃与手摇磨豆器具及对应组合。",
    "酒杯酒具": "葡萄酒、啤酒、白酒杯与分酒器；套装作为属性。",
    "冷水分享壶": "家用常温分享壶及以分享壶为主件的杯壶组合。",
    "滤水壶": "过滤饮用水的壶体及壶芯组合，兼容型号作为属性。",
    "滤芯": "独立销售过滤耗材，按适配型号和包装数量管理。",
}


def ratio(numerator, denominator):
    return float(numerator / denominator) if denominator else None


def change(before, after):
    output = {}
    for metric in ("amount", "quantity", "gross", "unit_value"):
        x, y = before.get(metric), after.get(metric)
        output[metric + "Delta"] = y - x if x is not None and y is not None else None
        output[metric + "Rate"] = ratio(y - x, x) if x is not None and y is not None else None
    output["marginPp"] = ((after["margin"] - before["margin"]) * 100
                          if before["margin"] is not None and after["margin"] is not None else None)
    return output


def half_compare(frame):
    first = aggregate(frame[frame.month.isin(MONTHS[:6])])
    second = aggregate(frame[frame.month.isin(MONTHS[6:])])
    return {"first": first, "second": second, **change(first, second)}


def add_mom(rows):
    previous = None
    for row in sorted(rows, key=lambda r: r["month"]):
        row["mom"] = change(previous, row) if previous else {
            k: None for k in ("amountDelta", "amountRate", "quantityDelta", "quantityRate",
                             "grossDelta", "grossRate", "unit_valueDelta", "unit_valueRate", "marginPp")
        }
        previous = row


def lifecycle(frame):
    by_month = grouped(frame, "month").set_index("month")
    active = [month for month in MONTHS if month in by_month.index
              and (by_month.loc[month, "amount"] > 0 or by_month.loc[month, "quantity"] > 0)]
    longest = current = 0
    for month in MONTHS:
        current = current + 1 if month in active else 0
        longest = max(longest, current)
    trailing = 0
    for month in reversed(MONTHS):
        if month not in active:
            break
        trailing += 1
    recent3 = aggregate(frame[frame.month.isin(MONTHS[-3:])])
    recent6 = aggregate(frame[frame.month.isin(MONTHS[-6:])])
    first = active[0] if active else None
    last = active[-1] if active else None
    if not active:
        stage = "零交易观察"
    elif last < MONTHS[-3]:
        stage = "交易休眠"
    elif first >= MONTHS[-3]:
        stage = "近期首销"
    elif len(active) >= 9 and trailing >= 3:
        stage = "持续动销"
    elif len(active) >= 6:
        stage = "稳定动销"
    else:
        stage = "间歇动销"
    return {
        "firstSale": first, "lastSale": last, "activeMonths": len(active),
        "coverage": len(active) / 12, "longestStreak": longest, "trailingStreak": trailing,
        "spanMonths": MONTHS.index(last) - MONTHS.index(first) + 1 if active else 0,
        "months": active, "stage": stage, "recent3": recent3, "recent6": recent6,
        "monthly": [{"month": month, **{key: float(by_month.loc[month, key])
                    if month in by_month.index else 0 for key in ("amount", "quantity", "gross")}}
                    for month in MONTHS],
    }


def normalized_name(value):
    return re.sub(r"\s+", "", value).replace("（", "(").replace("）", ")").lower()


def classify(name, original):
    # Electrical components have precedence over use and material keywords.
    if re.search(r"成新|瑕疵|二手|样品", name):
        return "特卖品", "转特卖管理"
    if re.search(r"电陶炉|电热|电动|奶泡机|智能|温显|显温|数显|电水|煮茶器|加热底座", name):
        return "电器及电子功能品", "电子功能边界转出"
    if re.search(r"焖烧|闷烧|饭盒|保温饭|焖罐", name):
        return "餐食容器", "转餐食容器类"
    if "磨豆机" in name and not re.search(r"手摇|手动|泰摩|栗子", name):
        return "动力结构核验", "动力结构核验"
    if "滤芯" in name or "滤水" in name or "净水" in name or "壶六芯" in name:
        has_jug = bool(re.search(r"\d壶|一壶|壶\d芯|滤水壶.*\d+(?:\.\d+)?[Ll升]|"
                                 r"净水壶.*\d+(?:\.\d+)?[Ll升]|滤水壶.*系列|壶\s*\+", name))
        return ("滤水壶" if has_jug or "滤芯" not in name else "滤芯"), "功能归类"
    if re.search(r"红酒|白酒|葡萄酒|鸡尾酒|香槟杯|啤酒|分酒|酒具|酒杯", name):
        return "酒杯酒具", "功能归类"
    if re.search(r"摩卡|手冲|法压|冷萃|磨豆|滤杯|咖啡壶|泰摩栗子", name) and "保温" not in name:
        return "手动咖啡器具", "功能归类"
    if "儿童" in name and "保温" in name:
        return "保温饮水杯", "功能归类"
    if original == "保温壶":
        return "保温壶", "功能归类"
    if original == "保温杯" or re.search(r"保温|保冷|双饮随行咖啡杯|钛享咖啡杯|双饮咖啡杯", name):
        return "保温饮水杯", "功能归类"
    if re.search(r"茶水分离|泡茶|茶具|茶壶|茶漏|茶仓|盖碗|快客杯|品茗", name):
        return "冲泡茶器", "功能归类"
    if original in ("茶具", "茶具套装"):
        return "冲泡茶器", "功能归类"
    if re.search(r"冷水壶|一壶两杯|凉水壶", name) or original == "杯壶套装":
        return "冷水分享壶", "功能归类"
    if re.search(r"马克杯|口杯|搪瓷|盖杯", name) or original == "陶瓷杯/马克杯":
        return "日用饮水杯", "功能归类"
    if original == "咖啡杯":
        return "咖啡杯碟", "功能归类"
    if original == "咖啡壶":
        return "手动咖啡器具", "功能归类"
    if re.search(r"随手|随行|运动|吸管|吨吨|吨杯|喷雾|塑料|拎拎|太空杯|便携|弹跳|弹盖", name):
        return "随行饮水杯", "功能归类"
    return "日用饮水杯", "功能归类"


def decomposition(frame, key):
    first = grouped(frame[frame.month.isin(MONTHS[:6])], key).set_index(key)
    second = grouped(frame[frame.month.isin(MONTHS[6:])], key).set_index(key)
    output = []
    for value in sorted(set(first.index) | set(second.index)):
        a = {m: float(first.loc[value, m]) if value in first.index else 0
             for m in ("amount", "quantity", "gross")}
        b = {m: float(second.loc[value, m]) if value in second.index else 0
             for m in ("amount", "quantity", "gross")}
        for item in (a, b):
            item["margin"] = ratio(item["gross"], item["amount"])
            item["unit_value"] = ratio(item["amount"], item["quantity"])
        output.append({key: value, "first": a, "second": b, **change(a, b)})
    return sorted(output, key=lambda row: row["amountDelta"])


def build_candidates(sales, listing, current):
    current_by_id = {row["spu"]: row for row in current}
    historical_master = [
        r for r in load_json(DATA / "P01_上架清单9.18.json")
        if r.get("后台三级分类") == "饮水用具"
    ]
    old_by_spu = {r["spu_code"]: r for r in historical_master}
    exact_sku = {r["sku_code"]: r["spu_code"] for r in historical_master}
    exact_sku.update(listing.drop_duplicates("sku_code").set_index("sku_code")["spu_code"].to_dict())
    known_brands = {spu: row["管理品牌名称"] for spu, row in old_by_spu.items()}
    known_brands.update({spu: row["brand"] for spu, row in current_by_id.items()})
    by_name = defaultdict(set)
    for row in current:
        by_name[(row["brand"], normalized_name(row["name"]))].add(row["spu"])
    assignments = {}
    name_sets = defaultdict(list)
    for sku, frame in sales.groupby("sku"):
        first = frame.sort_values("month").iloc[-1]
        key = (first.brand, normalized_name(first["name"]))
        if re.search(r"成新|瑕疵|二手|样品", first["name"]) and sku not in exact_sku:
            assignments[sku] = ("H:" + sku, "特卖SKU单独分组")
        elif sku in exact_sku:
            assignments[sku] = ("SPU:" + exact_sku[sku], "快照SKU映射")
        elif known_brands.get(sku[:-2]) == first.brand:
            assignments[sku] = ("SPU:" + sku[:-2], "已知SPU编码前缀及管理品牌关联")
        elif len(by_name[key]) == 1:
            assignments[sku] = ("SPU:" + next(iter(by_name[key])), "同管理品牌及完整名称关联")
        else:
            name_sets[(first.brand, sku[:-2])].append(sku)
    for skus in name_sets.values():
        for sku in skus:
            assignments[sku] = ("H:" + min(skus), "同管理品牌及SKU编码前缀的规划品款组")
    sales["planId"] = sales.sku.map(lambda sku: assignments[sku][0])
    candidates = []
    electrical_models = set()
    for name in set(sales["name"]):
        if re.search(r"电陶炉|电热|电动|奶泡机|加热底座", name):
            electrical_models.update(re.findall(r"[A-Z]{2,4}-[A-Z0-9]+", name))
    all_ids = set(sales.planId) | {"SPU:" + row["spu"] for row in current}
    for candidate_id in sorted(all_ids):
        frame = sales[sales.planId.eq(candidate_id)]
        spu = candidate_id[4:] if candidate_id.startswith("SPU:") else None
        existing = current_by_id.get(spu)
        historical = old_by_spu.get(spu)
        fact = frame.sort_values("month").iloc[-1] if len(frame) else None
        name = existing["name"] if existing else historical["商品名称"] if historical else fact["name"]
        brand = existing["brand"] if existing else historical["管理品牌名称"] if historical else fact.brand
        original = existing["category"] if existing else historical["后台四级分类"] if historical else fact.category4
        category, boundary = classify(name, original)
        evidence_skus = sorted(set(frame.sku))
        skus = sorted(existing["skus"] if existing else evidence_skus)
        row = {
            "id": candidate_id, "spu": spu, "skus": skus, "evidenceSkus": evidence_skus,
            "name": name, "brand": brand, "category": original, "finalCategory": category,
            "boundary": boundary, "current": bool(existing),
            "identity": "系统SPU" if existing else "历史系统SPU" if spu else "历史品款组",
            "mappingBasis": "P06当前主档；P01历史主档与P02管理品牌关联"
            if existing else "P01历史系统主档" if spu else "管理品牌＋SKU编码前缀（规划分组）",
            "listedChannels": existing["listedChannels"] if existing else [],
            "firstListed": existing["firstListed"] if existing else None,
            "newlyObserved": existing.get("newlyObserved", False) if existing else False,
            "listedSkus": existing["skus"] if existing else [],
            "operatingTags": existing.get("operatingTags", []) if existing else [],
            "currentVariants": existing.get("currentVariants", []) if existing else [],
            "managementBrandBasis": existing.get("managementBrandBasis", []) if existing else ["P02管理品牌"],
            "availability": "9.21快照在售" if existing else "历史商品恢复核验",
            "annual": aggregate(frame), "life": lifecycle(frame), "half": half_compare(frame),
            "listedSkuAnnual": aggregate(frame[frame.sku.isin(existing["skus"])]) if existing else None,
            "channels": records(grouped(frame, "channel")),
            "nameVariants": sorted(set(frame["name"])),
        }
        if existing:
            variants = listing[listing["spu_code"].eq(spu)].drop_duplicates("sku_code")
            mixed = any(any(model in str(spec) for model in electrical_models) for spec in variants["规格"])
            if mixed:
                manual = variants[variants["规格"].astype(str).str.contains("手冲", regex=False)]
                row["skus"] = sorted(set(manual["sku_code"]))
                row["name"] = f"{brand} 手冲咖啡套装 " + " / ".join(manual["规格"].astype(str))
                row["scopeNote"] = "同SPU执行规格为手冲黑色DSC-TZ070A；依据P06规格及P02型号匹配。"
                assert row["skus"] and row["annual"]["amount"] == 0
        row["annualPerActiveMonth"] = ratio(row["annual"]["amount"], row["life"]["activeMonths"])
        candidates.append(row)
    return candidates


def rank_key(row):
    return (-row["annual"]["amount"], -(row["annual"]["margin"] or 0),
            -row["annual"]["gross"], row["id"])


def enrich(result, sales, kitchen, listing):
    add_mom(result["monthly"])
    for channel in sorted(sales.channel.unique()):
        add_mom([row for row in result["monthlyByChannel"] if row["channel"] == channel])
    result["half"] = half_compare(sales)
    result["halfByChannel"] = [{"channel": channel, **half_compare(frame)}
                               for channel, frame in sales.groupby("channel")]
    result["categoryChanges"] = decomposition(sales, "category4")
    result["categoryChangesByChannel"] = [
        {"channel": channel, **row} for channel, frame in sales.groupby("channel")
        for row in decomposition(frame, "category4")
    ]
    result["brandChanges"] = decomposition(sales, "brand")
    result["brandChannels"] = records(grouped(sales, ["brand", "channel"]))
    result["categoryMonthly"] = records(grouped(sales, ["category4", "month"]))
    h = result["half"]
    qa, qb = h["first"]["quantity"], h["second"]["quantity"]
    pa, pb = h["first"]["unit_value"], h["second"]["unit_value"]
    result["bridge"] = {
        "volumeEffect": (qb - qa) * (pa + pb) / 2,
        "unitValueEffect": (pb - pa) * (qa + qb) / 2,
        "amountEffectOnGross": (h["second"]["amount"] - h["first"]["amount"])
        * (h["first"]["margin"] + h["second"]["margin"]) / 2,
        "marginEffectOnGross": (h["second"]["margin"] - h["first"]["margin"])
        * (h["first"]["amount"] + h["second"]["amount"]) / 2,
        "method": "对称分解；单位兑换额同时包含价格和商品结构；前后半年为年度内两个六个月窗口。",
    }
    products = []
    for sku, frame in sales.groupby("sku"):
        last = frame.sort_values("month").iloc[-1]
        products.append({"sku": sku, "name": last["name"], "brand": last.brand,
                         "category": last.category4, "annual": aggregate(frame),
                         "life": lifecycle(frame), "half": half_compare(frame)})
    result["skuLifecycle"] = sorted(products, key=lambda r: -r["annual"]["amount"])
    result["productChanges"] = sorted(products, key=lambda r: r["half"]["amountDelta"])
    candidates = build_candidates(sales, listing, result["spus"])
    # Slots are a recommendation, with explicit protection for recently listed trials
    # and one item per represented use. Historical restoration follows the same window.
    eligible = [r for r in candidates if r["finalCategory"] in CAT_RULES
                and (r["current"] or r["annual"]["amount"] > 0)]
    protected = [r for r in eligible if r["current"] and
                 (r["newlyObserved"] or r["firstListed"] and r["firstListed"] >= "2026-07-01")]
    selected_ids = {r["id"] for r in protected}
    for category in CAT_RULES:
        group = sorted([r for r in eligible if r["finalCategory"] == category], key=rank_key)
        if group and not any(r["finalCategory"] == category for r in protected):
            protected.append(group[0])
            selected_ids.add(group[0]["id"])
    ranked = sorted([r for r in eligible if r["id"] not in selected_ids], key=rank_key)
    selected = sorted(protected + ranked[:CAPACITY - len(protected)], key=rank_key)
    assert len(selected) == CAPACITY
    selected_ids = {r["id"] for r in selected}
    for index, row in enumerate(selected, 1):
        row["slot"] = index
        row["action"] = ("新增在售观察" if row["newlyObserved"]
                         else "新品试销" if row["current"] and row["firstListed"]
                         and row["firstListed"] >= "2026-07-01"
                         else "保留" if row["current"] else "恢复引入")
        selection_reason = ("9.21新增在售商品，配置一个完整月观察" if row["newlyObserved"]
                            else "近期上架试销保护" if row["action"] == "新品试销"
                            else "功能类基础覆盖" if row in protected else "年度金额优先排序")
        margin_text = f"{row['annual']['margin']:.2%}" if row["annual"]["margin"] is not None else "观察期"
        row["reason"] = (
            f"年度兑换额{row['annual']['amount']:.2f}元；"
            f"含运费毛利率{margin_text}；"
            f"毛利额{row['annual']['gross']:.2f}元；"
            f"动销{row['life']['activeMonths']}个月；最近交易{row['life']['lastSale'] or '观察期'}；"
            f"展示排序第{index}位；{selection_reason}"
        )
        if row.get("scopeNote"):
            row["reason"] += "；" + row["scopeNote"]
        if not row["current"] and row["life"]["stage"] == "交易休眠":
            row["reason"] += "；历史高贡献恢复试销，首个完整月核对兑换承接"
        row["suggestedChannels"] = [r["channel"] for r in row["channels"] if r["amount"] > 0]
    current_candidates = [r for r in candidates if r["current"]]
    removals = sorted([r for r in current_candidates if r["id"] not in selected_ids],
                      key=lambda r: r["annual"]["amount"])
    additions = [r for r in selected if not r["current"]]
    assert len(current_candidates) - len(removals) + len(additions) == CAPACITY
    replacements = []
    for index, added in enumerate(additions):
        removed = removals[index] if index < len(removals) else None
        if removed:
            removed["action"] = "边界转出" if removed["finalCategory"] not in CAT_RULES else "尾部替换"
            removed["reason"] = (f"{removed['boundary']}；年度兑换额{removed['annual']['amount']:.2f}元；"
                                 f"替换为{added['brand']} {added['name']}")
        replacements.append({
            "outId": removed["id"] if removed else None,
            "outSpu": removed["spu"] if removed else None,
            "outName": removed["name"] if removed else "空位补齐",
            "inId": added["id"], "inSkus": added["skus"], "inName": added["name"],
            "inSpu": added["spu"], "inIdentity": added["identity"],
            "outCategory": removed["finalCategory"] if removed else None, "inCategory": added["finalCategory"],
            "outAmount": removed["annual"]["amount"] if removed else 0, "inAmount": added["annual"]["amount"],
            "amountDifference": added["annual"]["amount"] - (removed["annual"]["amount"] if removed else 0),
            "grossDifference": added["annual"]["gross"] - (removed["annual"]["gross"] if removed else 0),
            "action": removed["action"] if removed else "空位补齐",
        })
    for r in current_candidates:
        if r["id"] in selected_ids and r["action"] not in ("新品试销", "新增在售观察"):
            r["action"] = "重点保留" if r["slot"] <= 30 else "保留"
            if r["life"]["stage"] == "交易休眠":
                r["action"] = "保留并激活动销"
    result["spus"] = sorted(current_candidates, key=rank_key)
    result["finalAssortment"] = selected
    result["replacements"] = replacements
    result["candidatePool"] = sorted(candidates, key=rank_key)
    allocations = []
    for category, rule in CAT_RULES.items():
        before = [r for r in current_candidates if r["finalCategory"] == category]
        after = [r for r in selected if r["finalCategory"] == category]
        annual = sales[sales.planId.isin({r["id"] for r in candidates if r["finalCategory"] == category})]
        fact = aggregate(annual)
        allocations.append({
            "category": category, "rule": rule, "currentSlots": len(before),
            "recommendedSlots": len(after), "delta": len(after) - len(before),
            "amount": fact["amount"], "gross": fact["gross"], "margin": fact["margin"],
            "amountShare": ratio(fact["amount"], result["overall"]["amount"]),
            "currentMappedAmount": sum(r["annual"]["amount"] for r in before),
            "currentPerSpu": ratio(sum(r["annual"]["amount"] for r in before), len(before)),
            "selectedAmount": sum(r["annual"]["amount"] for r in after),
            "examples": [r["name"] for r in after[:3]],
        })
    boundary = [r for r in current_candidates if r["finalCategory"] not in CAT_RULES]
    if boundary:
        allocations.append({
            "category": "边界转出", "rule": "电子功能品、餐食容器与动力结构核验商品按对应功能管理。",
            "currentSlots": len(boundary), "recommendedSlots": 0, "delta": -len(boundary),
            "amount": sum(r["annual"]["amount"] for r in boundary), "gross": sum(r["annual"]["gross"] for r in boundary),
            "margin": None, "amountShare": None, "currentMappedAmount": sum(r["annual"]["amount"] for r in boundary),
            "currentPerSpu": ratio(sum(r["annual"]["amount"] for r in boundary), len(boundary)),
            "selectedAmount": 0, "examples": [r["name"] for r in boundary],
        })
    result["allocations"] = allocations
    current_ids = {r["id"] for r in current_candidates}
    before = aggregate(sales[sales.planId.isin(current_ids)])
    after = aggregate(sales[sales.planId.isin(selected_ids)])
    result["assortmentComparison"] = {"current": before, "selected": after, **change(before, after)}
    scenario_rows = []
    for channel, frame in kitchen.groupby("channel"):
        dr = sales[sales.channel.eq(channel)]
        out = dr[dr.planId.isin({r["id"] for r in removals})]
        inc = dr[dr.planId.isin({r["id"] for r in additions})]
        # Historical entrants already occur in kitchen actuals. Compare two reconstructed baskets.
        fixed = {m: float(frame[m].sum() - inc[m].sum()) for m in ("amount", "quantity", "gross")}
        proposed = {m: fixed[m] - float(out[m].sum()) + float(inc[m].sum()) for m in fixed}
        for values in (fixed, proposed):
            values["margin"] = ratio(values["gross"], values["amount"])
        scenario_rows.append({"channel": channel, "floor": FLOORS.get(channel),
                              "currentBasket": fixed, "proposedBasket": proposed,
                              "marginHeadroom": proposed["gross"] - FLOORS.get(channel, 0) * proposed["amount"]})
    result["basketMargins"] = scenario_rows
    assert all(row["marginHeadroom"] >= 0 for row in scenario_rows), "Annual kitchen channel margin constraint"
    result["plan"] = {
        "capacity": CAPACITY, "kept": sum(r["current"] for r in selected),
        "historicalGroups": len(additions), "replacements": len(removals),
        "vacancyFills": max(0, CAPACITY - len(current_candidates)),
        "currentCount": len(current_candidates),
        "unresolvedGroups": sum(r["spu"] is None for r in additions),
        "boundaryOut": len(boundary),
        "knownSystemSpus": sum(r["spu"] is not None for r in selected),
        "capacityRule": result["snapshot"]["capacityRule"],
        "method": "饮水用具固定122个规划位。推荐保留2026年7月起首次上架商品试销，9.21新增在售商品观察一个完整月，各有成交功能类至少1款；其余按全年兑换金额、含运费毛利率、毛利额依次降序选满。分类配额由入选商品汇总，空位按候选排序补齐。",
        "identityRule": "当前在售SPU与执行SKU以P06的9.21快照为准；P01保留历史系统主档身份。历史品款组按管理品牌＋SKU编码前缀形成规划单元，每组配置1位，执行时关联系统主档。推荐SKU、当前在售SKU和历史成交SKU分别列示。",
        "skuRule": "SPU年度表现汇总关联历史成交规格；在售规格贡献单列。当前保留商品执行P06在售规格，历史恢复商品执行清单所列历史SKU并核验可售状态。",
        "evidenceRule": "年度金额排序构成历史证据下的推荐货盘；组合回放差额属于历史覆盖差额；规划期收益按月实测。",
        "lifecycleRule": "2025-09至2026-08交易观察窗；月兑换额或销量大于0计为动销月；首销和末销表示窗内首末交易；月间空档按交易空档记录。",
        "stageRule": "按顺序判定：动销0个月为零交易观察；末次交易早于2026-06为交易休眠；首次交易在2026-06及以后为近期首销；累计动销至少9个月且期末连续至少3个月为持续动销；累计至少6个月为稳定动销；其余为间歇动销。",
        "profitRule": "利润分析采用兑换毛利额（含C端运费）；返利、补贴与售后成本按本轮口径省略。",
        "marginRule": "厨房用具×频道×2025-09至2026-08；本表为订单创建月口径经营监测，年度结算以结算账期确认。",
        "halfRule": "2025-09至2026-02与2026-03至2026-08比较，包含季节和经营结构差异；原因定位为交易结果的可加总贡献分解。",
    }
    result["lifecycleSummary"] = dict(Counter(r["life"]["stage"] for r in current_candidates))


def export_tables(result):
    prepare()
    def export(filename, headers, rows):
        with (OUTPUT / filename).open("w", encoding="utf-8-sig", newline="") as handle:
            writer = csv.writer(handle)
            writer.writerow(headers)
            writer.writerows(rows)
        return {"file": filename, "headers": headers, "rows": rows}

    exports = {}
    headers = ["坑位序号", "品款标识", "系统SPU", "身份口径", "推荐执行SKU", "管理品牌", "商品名称",
               "原四级分类", "最终四级分类", "当前上架频道", "历史成交频道", "年度兑换额", "销量",
               "含运费毛利额", "含运费毛利率", "首次交易月", "最近交易月", "动销月数",
               "12月覆盖率", "最长连续月数", "期末连续月数", "近3月兑换额", "近6月兑换额",
               "生命周期", "动作", "依据", "当前在售SKU", "历史成交SKU", "当前状态", "运营标签",
               "当前在售规格年度兑换额", "当前在售规格年度毛利额", "管理品牌依据"]
    def assortment_rows(items):
        return [[r.get("slot"), r["id"], r["spu"], r["identity"], " / ".join(r["skus"]), r["brand"], r["name"],
             r["category"], r["finalCategory"], " / ".join(r["listedChannels"]),
             " / ".join(x["channel"] for x in r["channels"]), r["annual"]["amount"], r["annual"]["quantity"],
             r["annual"]["gross"], r["annual"]["margin"], r["life"]["firstSale"], r["life"]["lastSale"],
             r["life"]["activeMonths"], r["life"]["coverage"], r["life"]["longestStreak"], r["life"]["trailingStreak"],
             r["life"]["recent3"]["amount"], r["life"]["recent6"]["amount"], r["life"]["stage"], r["action"], r["reason"],
             " / ".join(r["listedSkus"]), " / ".join(r["evidenceSkus"]), r["availability"],
             " / ".join(r["operatingTags"]), (r["listedSkuAnnual"] or {}).get("amount"),
             (r["listedSkuAnnual"] or {}).get("gross"), " / ".join(r["managementBrandBasis"])]
            for r in items]
    exports["assortment"] = export("饮水用具122坑位最终货盘.csv", headers, assortment_rows(result["finalAssortment"]))
    exports["current"] = export("饮水用具现架122SPU决策.csv", headers, assortment_rows(result["spus"]))
    exports["allocation"] = export(
        "饮水用具四级分类配额.csv",
        ["最终四级分类", "功能边界", "现架归类SPU", "建议坑位", "调整量", "年度分类兑换额",
         "年度分类毛利额", "年度分类毛利率", "当前货盘年度金额", "当前SPU平均年度金额", "入选历史金额", "具体商品"],
        [[r[k] for k in ("category", "rule", "currentSlots", "recommendedSlots", "delta", "amount",
                          "gross", "margin", "currentMappedAmount", "currentPerSpu", "selectedAmount")]
         + [" / ".join(r["examples"])] for r in result["allocations"]])
    exports["lifecycle"] = export(
        "饮水用具商品生命周期.csv",
        ["SKU", "管理品牌", "商品", "原四级分类", "年度兑换额", "含运费毛利额", "含运费毛利率",
         "首次交易月", "最近交易月", "动销月数", "覆盖率", "最长连续月数", "期末连续月数",
         "近3月兑换额", "近6月兑换额", "生命周期"] + MONTHS,
        [[r["sku"], r["brand"], r["name"], r["category"], r["annual"]["amount"], r["annual"]["gross"],
          r["annual"]["margin"], r["life"]["firstSale"], r["life"]["lastSale"], r["life"]["activeMonths"],
          r["life"]["coverage"], r["life"]["longestStreak"], r["life"]["trailingStreak"],
          r["life"]["recent3"]["amount"], r["life"]["recent6"]["amount"], r["life"]["stage"]]
         + [m["amount"] for m in r["life"]["monthly"]] for r in result["skuLifecycle"]])
    exports["replacement"] = export(
        "饮水用具逐项替换清单.csv",
        ["调出系统SPU", "调出商品", "调入品款标识", "调入SKU", "调入商品", "调出分类", "调入分类",
         "调出年度金额", "调入年度金额", "历史金额差", "历史毛利额差", "动作", "调入系统SPU", "调入身份口径"],
        [[r["outSpu"], r["outName"], r["inId"], " / ".join(r["inSkus"]), r["inName"],
          r["outCategory"], r["inCategory"], r["outAmount"], r["inAmount"], r["amountDifference"],
          r["grossDifference"], r["action"], r["inSpu"], r["inIdentity"]] for r in result["replacements"]])
    exports["variants"] = export(
        "饮水用具当前在售规格.csv",
        ["系统SPU", "SKU", "管理品牌", "商品", "规格", "运营标签", "占坑标记", "在售频道",
         "可售状态", "上下架状态", "快照售价", "快照运费", "快照含运费毛利率", "明细来源行", "状态来源行"],
        [[r["spu"], v["sku"], r["brand"], r["name"], v["spec"], v["tag"], v["occupancy"],
          s.get("channel"), s.get("saleStatus"), s.get("listingStatus"), s.get("price"),
          s.get("shipping"), s.get("snapshotMargin"), v["sourceRow"], s.get("row")]
         for r in result["spus"] for v in r["currentVariants"]
         for s in (v["states"] or [{}])])
    exports["monthly"] = export(
        "饮水用具月度经营.csv",
        ["月份", "兑换额", "销量", "含运费毛利额", "毛利率", "金额环比", "销量环比", "毛利额环比", "毛利率环比百分点"],
        [[r["month"], r["amount"], r["quantity"], r["gross"], r["margin"],
          r["mom"]["amountRate"], r["mom"]["quantityRate"], r["mom"]["grossRate"], r["mom"]["marginPp"]]
         for r in result["monthly"]])
    result["exports"] = exports