"""Current SPU identity from the September 21 snapshot, with sourced metadata."""

import hashlib
import json
from collections import defaultdict

import pandas as pd

from audit_private import WorkbookReader, sheet_records
from release_config import ROOT, SNAPSHOT, SNAPSHOT_TIME, EVIDENCE, prepare

FLAG = "是否占用坑位（0-否，1-是）"
CATEGORY = "硬百/厨房用具/饮水用具"


def load_current_listing(sales):
    prepare()
    old = json.loads((ROOT / "research/private/P01_上架清单9.18.json").read_text(encoding="utf-8"))
    old = [r for r in old if r.get("后台三级分类") == "饮水用具"]
    by_sku = {r["sku_code"]: r for r in old}
    by_spu = defaultdict(list)
    for row in old:
        by_spu[row["spu_code"]].append(row)
    book = WorkbookReader(SNAPSHOT)
    try:
        sheets = {s["name"]: s for s in book.sheets}
        source, _ = sheet_records(book, sheets["9.21data"])
        master, headers = sheet_records(book, sheets["Sheet2"])
    finally:
        book.close()
    current = [r for r in source if r.get("配置类目") == CATEGORY]
    master_by_sku = defaultdict(list)
    for row in master:
        master_by_sku[str(row.get(headers["C"], ""))].append(row)
    fact_brands = sales.groupby("sku")["brand"].agg(lambda values: sorted(set(values))).to_dict()
    management_brands = set(sales.brand)
    output = []
    for row in current:
        sku, spu = str(row["sku编码"]), str(row["spu编码"])
        prior_rows = by_spu.get(spu, [])
        prior = by_sku.get(sku) or (prior_rows[0] if prior_rows else {})
        brands = fact_brands.get(sku) or sorted({
            r["管理品牌名称"] for r in prior_rows if r.get("管理品牌名称")
        })
        brand_basis = "P02管理品牌" if sku in fact_brands else "P01同SPU管理品牌"
        if not brands:
            brands = sorted({str(r.get("所属品牌", "")) for r in master_by_sku[sku]})
            assert len(brands) == 1 and brands[0] in management_brands, (spu, brands)
            brand_basis = "P06所属品牌与P02管理品牌名称精确一致"
        assert len(brands) == 1, (spu, brands)
        classification = str(row["商品后台分类"])
        assert classification.startswith(CATEGORY + "/")
        specs = []
        for item in master_by_sku[sku]:
            specs.append({
                "sku": sku, "channel": item.get("所属频道"),
                "saleStatus": item.get("可售状态"), "listingStatus": item.get("上下架状态"),
                "price": item.get("前台售卖价"), "shipping": item.get("商品运费"),
                "snapshotMargin": item.get("毛利率（含运费）"), "row": item["_row"],
            })
        for channel in str(row.get("商品上架频道", "")).split(","):
            output.append({
                "sku_code": sku, "spu_code": spu, "商品名称": row["商品名称"],
                "管理品牌名称": brands[0], "管理品牌依据": brand_basis,
                "后台一级分类": "硬百", "后台二级分类": "厨房用具",
                "后台三级分类": "饮水用具", "后台四级分类": classification[len(CATEGORY) + 1:],
                "上架频道": channel.strip(), "规格": row.get("规格名称", ""),
                "首次上架时间": prior.get("首次上架时间"),
                "可用库存数量": None, "运营标签": row.get("运营标签", ""),
                "占坑标记": row.get(FLAG), "快照新增SPU": spu not in by_spu,
                "快照时间": row["数据写入时间"], "来源行": row["_row"],
                "当前规格状态": specs,
            })
    old_spus, new_spus = set(by_spu), {str(r["spu编码"]) for r in current}
    old_skus, new_skus = set(by_sku), {str(r["sku编码"]) for r in current}
    occupying = {str(r["spu编码"]) for r in current if r[FLAG] == "是"}
    summary = {
        "source": "P06", "file": SNAPSHOT.name, "time": SNAPSHOT_TIME,
        "rows": len(current), "skus": len(new_skus), "spus": len(new_spus),
        "occupiedSpus": len(occupying), "fixedPlanCapacity": 122,
        "addedSpus": sorted(new_spus - old_spus), "removedSpus": sorted(old_spus - new_spus),
        "addedSkus": sorted(new_skus - old_skus), "removedSkus": sorted(old_skus - new_skus),
        "sha256": hashlib.sha256(SNAPSHOT.read_bytes()).hexdigest(),
        "identityRule": "系统SPU取9.21data的spu编码；SKU精确关联Sheet2的当前可售状态与价格。",
        "capacityRule": "饮水用具固定122个规划位，每个入选SPU或待关联主档的品款组配置1个规划位，空位按候选排序补齐。",
    }
    assert {r["数据写入时间"] for r in current} == {SNAPSHOT_TIME}
    (EVIDENCE / "在售快照.json").write_text(
        json.dumps({"summary": summary, "rows": current, "normalized": output},
                   ensure_ascii=False, indent=2), encoding="utf-8")
    return pd.DataFrame(output), summary