#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""大梦 by 可能实验室 — 月度营收分析引擎（按月参数化）

用法: python3 build_analysis.py <月度账务目录> [YYYY-MM]   # 默认 2026-05
生成 大梦可能实验室_{N}月营收分析_西湖店vs滨江店.xlsx（13 sheets）+ _analysis_summary.json，
并打印工资所需的部门/班次/部门×班次业绩三元组。

⚠️ 跑本脚本前先跑 make_menu_lib.py 生成当月菜品库，否则新上 SKU 会落到关键词兜底。

数据源（同目录）:
  POS 店内订单明细 (菜品明细 / 订单明细 / 优惠明细 sheets)
  全渠道订单明细 (含 餐段)
  菜品库 (SKU→基础分类)
  团购收益(美团大梦) + 新版收益(点评/可能实验室)
"""
import openpyxl, warnings, glob, sys, re
from collections import defaultdict
warnings.filterwarnings('ignore')

import sys as _sys, calendar as _cal
# 用法: python3 build_analysis.py <月度账务目录> [YYYY-MM]
BASE = (_sys.argv[1] if len(_sys.argv) > 1 else ".").rstrip("/")
MONTH = _sys.argv[2] if len(_sys.argv) > 2 else "2026-05"
_y,_m = int(MONTH[:4]), int(MONTH[5:7])
DAYS = _cal.monthrange(_y,_m)[1]

# ===================== 部门归口规则（菜品一级分类）=====================
def dept_of_category(primary, secondary, store):
    p = (primary or "").strip()
    pl = p.lower()
    s = (secondary or "").strip()
    if store == "西湖":
        if p in ["小吃","主食","零食"] or pl.startswith("brunch"):
            return "厨房"
        if p in ["咖啡","甜品"] or p in ["茶饮Tea","茶饮tea"]:
            return "咖啡"
        if p == "软饮":
            return "咖啡" if any(k in s for k in ["可尔必思","海盐荔枝"]) else "调酒"
        if p.startswith("精酿"):
            return "精酿"
        if p in ["鸡尾酒","纯饮","纯饮酒"]:
            return "调酒"
    else:  # 滨江
        if p in ["肉肉肉","小吃","主食","零食"] or pl.startswith("brunch"):
            return "厨房"
        if p in ["咖啡","甜品点心"] or p in ["茶饮Tea","茶饮tea"]:
            return "咖啡"
        if p == "无咖无醇":
            return "调酒" if "无醇鸡尾酒" in s else "咖啡"
        if p.startswith("精酿") or p in ["瓶罐精酿","瓶装精酿"]:
            return "精酿"
        if p in ["鸡尾酒","纯饮酒","纯饮"]:
            return "调酒"
    return None  # 其他/团购套餐/特惠套餐/加料 → 需 SKU 级处理或跳过

# 周边/服务（非部门，单列）
def is_peripheral(name):
    n=str(name)
    return any(k in n for k in ["扑克","点歌","雨伞","毛毯","游戏卡牌","桌游","充电","寄存"])
# POS 端团购套餐壳（收入计平台侧，POS 侧多为 0，跳过避免重复）
def is_teamgou_shell(name):
    return "美团团购" in str(name) or "团购套餐" in str(name) or "打卡套餐" in str(name)

# 「其他」类 SKU 按菜品名关键词推断部门（复刻 4 月 SKU重归类思路）
# 注意：酒类关键词优先（避免"果酒优格奶昔"等被咖啡词误捕）
def dept_by_name(name, store):
    n = str(name)
    # —— 精酿（啤酒/西打/果酒/气泡酒/品牌）优先 ——
    # 酒头/畅饮票=扎啤生啤(精酿)，与菜品库同类SKU一致
    if any(k in n for k in ["IPA","Lager","Stout","Ale","拉格","精酿","世涛","酸啤","古斯","Gose","西打","啤酒","札幌","健力士","三宝乐","制乐场","制乐厂","沙坡尾","气泡实验室","做梦去吧","滇麻","果酒","气泡酒","酒头","畅饮"]):
        return "精酿"
    # —— 调酒（烈酒/鸡尾酒/特调）——
    if any(k in n for k in ["特调","鸡尾酒","威士忌","金酒","朗姆","龙舌兰","伏特加","僵尸","Zombie","Negroni","内格罗尼","Margarita","玛格丽特","Old Fashion","古典","SHOT","纯饮","清酒","葡萄酒","红酒","白葡萄","金刚芭比","混四喜"]):
        return "调酒"
    # —— 咖啡/茶/无醇饮品 ——
    if any(k in n for k in ["美式","拿铁","咖啡","冷萃","澳白","Dirty","卡布","摩卡","瑰夏","耶加","曼特宁","葡萄成熟","优格","冰淇淋","奶昔","波旁","庄园"]):
        return "咖啡"
    if any(k in n for k in ["茶","龙井","乌龙","普洱","大梦冰茶","果茶"]):
        return "咖啡"
    # —— 厨房 ——
    if any(k in n for k in ["拼盘","小食","沙拉","Tacos","吐司","蛋","焗饭","意面","薯","鸡","牛肉","猪","披萨","brunch","早餐","三明治","汉堡","面包","可颂","煮蛋"]):
        return "厨房"
    return None

# ===================== 团购项目 → 部门 =====================
def teamgou_dept_split(name):
    """返回 {dept: ratio} 或 None(=代金券跳过)"""
    n = str(name)
    if "代金券" in n:
        return None  # 跳过，POS 已计入
    # 拆分套餐（先匹配，避免被"厨房/咖啡"单部门误判）
    if "单人轻食" in n or "温馨时光" in n:
        return {"厨房":0.6, "咖啡":0.4}
    if "营养满溢" in n or ("南瓜沙拉" in n and "Tacos" in n):
        return {"厨房":0.7, "咖啡":0.3}
    if "豪华烤肉拼盘" in n:
        return {"厨房":0.7, "调酒":0.3}
    if "香菜" in n and ("葡萄酒" in n or "Tacos" in n):
        return {"厨房":0.5, "调酒":0.5}
    # 单部门
    if any(k in n for k in ["咖啡任选","经典咖啡","美式","下午茶","白日梦"]):
        return {"咖啡":1.0}
    if "精酿" in n or "盲盒" in n:
        return {"精酿":1.0}
    if any(k in n for k in ["鸡尾酒","SHOT","小酌","HappyHour"]):
        # HappyHour 精酿 已被上面拦截；这里是鸡尾酒/SHOT
        if "精酿" in n: return {"精酿":1.0}
        return {"调酒":1.0}
    return {"未拆分":1.0}

# ===================== 读取工具 =====================
def find_header(ws, key):
    for i, r in enumerate(ws.iter_rows(values_only=True), start=1):
        if r and any(c == key for c in r if c is not None):
            return i, [str(c).strip() if c is not None else '' for c in r]
    return None, None

def g(path_glob):
    fs = glob.glob(f"{BASE}/{path_glob}")
    if not fs: sys.exit(f"缺少文件: {path_glob}")
    return fs[0]

STORES = {"西湖":"西湖", "滨江":"滨江"}

# ===================== 1) 菜品库: SKU/名称 → 分类 =====================
def load_menu(store):
    wb = openpyxl.load_workbook(g(f"大梦_可能实验室_{store}店_菜品库_*.xlsx"), data_only=True)
    ws = wb["菜品"]; hr,hdr = find_header(ws,"菜品编码（SPUID）")
    ci_name=hdr.index("菜品名称"); ci_cat=hdr.index("基础分类")
    name2cat={}
    for i,r in enumerate(ws.iter_rows(values_only=True),start=1):
        if i<=hr: continue
        if r[ci_name] and r[ci_cat]:
            parts=str(r[ci_cat]).split("/")
            name2cat[str(r[ci_name]).strip()]=(parts[0], parts[1] if len(parts)>1 else "")
    return name2cat

# ===================== 2) 全渠道: 订单号 → 餐段 =====================
def load_shift(store):
    wb=openpyxl.load_workbook(g(f"大梦可能实验室（{store}店）_全渠道订单明细_*.xlsx"),data_only=True)
    ws=wb.active; hr,hdr=find_header(ws,"营业日期")
    ci_o=hdr.index("订单号"); ci_s=hdr.index("餐段")
    ci_pay=hdr.index("顾客实付"); ci_amt=hdr.index("订单金额")
    o2shift={}; shift_rows=[]
    for i,r in enumerate(ws.iter_rows(values_only=True),start=1):
        if i<=hr: continue
        if r[ci_o]:
            sh=str(r[ci_s]) if r[ci_s] else None
            o2shift[str(r[ci_o])]=sh
            shift_rows.append((sh, float(r[ci_pay] or 0), float(r[ci_amt] or 0)))
    return o2shift, shift_rows

# ===================== 3) POS 订单明细 (顾客应付/服务费/优惠/每日) =====================
def load_orders(store):
    wb=openpyxl.load_workbook(g(f"大梦_可能实验室_{store}店__店内订单明细*.xlsx"),data_only=True)
    ws=wb["订单明细"]; hr,hdr=find_header(ws,"营业日期")
    idx={k:hdr.index(k) for k in ["营业日期","订单号","订单金额（元）","顾客应付（元）","订单优惠（元）","菜品收入（元）","服务费收入（元）"]}
    orders=[]
    for i,r in enumerate(ws.iter_rows(values_only=True),start=1):
        if i<=hr: continue
        no=r[idx["订单号"]]
        if not no or str(no).strip()=="--": continue          # 跳过幽灵汇总行
        if str(r[idx["营业日期"]]).strip()=="--": continue
        orders.append({
            "date":str(r[idx["营业日期"]]),
            "order":str(r[idx["订单号"]]),
            "amount":float(r[idx["订单金额（元）"]] or 0),
            "payable":float(r[idx["顾客应付（元）"]] or 0),
            "discount":float(r[idx["订单优惠（元）"]] or 0),
            "dish_rev":float(r[idx["菜品收入（元）"]] or 0),
            "service":float(r[idx["服务费收入（元）"]] or 0),
        })
    # 优惠明细(赠菜)
    ws2=wb["优惠明细"]; hr2,hdr2=find_header(ws2,"营业日期")
    gifts=[]
    if hr2:
        gi={k:(hdr2.index(k) if k in hdr2 else None) for k in ["营业日期","折扣优惠类型","折扣优惠名称","金额(¥)","折扣金额（元）","优惠金额（元）"]}
        amt_col = gi["金额(¥)"] or gi["折扣金额（元）"] or gi["优惠金额（元）"]
        for i,r in enumerate(ws2.iter_rows(values_only=True),start=1):
            if i<=hr2: continue
            typ=str(r[gi["折扣优惠类型"]]) if gi["折扣优惠类型"] is not None and r[gi["折扣优惠类型"]] else ""
            if "赠菜" in typ:
                gifts.append({"date":str(r[gi["营业日期"]]), "amt":float(r[amt_col] or 0) if amt_col is not None else 0})
    # 支付方式分布
    ws3=wb["支付明细"]; hr3,hdr3=find_header(ws3,"支付方式") if find_header(ws3,"支付方式")[0] else (None,None)
    pays=defaultdict(lambda:[0,0.0])
    if hr3:
        pi_way=hdr3.index("支付方式")
        pi_amt=None
        for cand in ["支付金额（元）","实收金额（元）","金额（元）","支付金额(¥)"]:
            if cand in hdr3: pi_amt=hdr3.index(cand); break
        for i,r in enumerate(ws3.iter_rows(values_only=True),start=1):
            if i<=hr3: continue
            if r[pi_way]:
                pays[str(r[pi_way])][0]+=1
                pays[str(r[pi_way])][1]+=float(r[pi_amt] or 0) if pi_amt is not None else 0
    return orders, gifts, dict(pays)

# ===================== 4) POS 菜品明细 → 部门/分类/班次 =====================
def load_dishes(store, name2cat, o2shift):
    wb=openpyxl.load_workbook(g(f"大梦_可能实验室_{store}店__店内订单明细*.xlsx"),data_only=True)
    ws=wb["菜品明细"]; hr,hdr=find_header(ws,"订单编号")
    ci_o=hdr.index("订单编号"); ci_name=hdr.index("菜品名称")
    ci_qty=hdr.index("销售数量"); ci_amt=hdr.index("金额合计（元）")
    ci_disc=hdr.index("菜品优惠（元）"); ci_rev=hdr.index("菜品收入（元）")
    dept_pos=defaultdict(float)        # dept -> 菜品收入
    cross=defaultdict(float)           # (dept,shift) -> 菜品收入
    cat1=defaultdict(lambda:[0,0.0,0.0,0.0])  # primary -> [qty, 原价, 优惠, 收入]
    cat2=defaultdict(lambda:[set(),0,0.0,0.0,0.0]) # basecat -> [skus,qty,原价,优惠,收入]
    other_skus=defaultdict(lambda:[0,0.0])  # name -> [qty, rev] (其他/未归类)
    unmatched=0
    for i,r in enumerate(ws.iter_rows(values_only=True),start=1):
        if i<=hr: continue
        name=str(r[ci_name]).strip() if r[ci_name] else None
        if not name: continue
        rev=float(r[ci_rev] or 0); amt=float(r[ci_amt] or 0)
        disc=float(r[ci_disc] or 0); qty=float(r[ci_qty] or 0)
        cat=name2cat.get(name)
        primary = cat[0] if cat else "（无菜品库）"
        secondary = cat[1] if cat else ""
        basecat = f"{primary}/{secondary}" if secondary else primary
        cat1[primary][0]+=qty; cat1[primary][1]+=amt; cat1[primary][2]+=disc; cat1[primary][3]+=rev
        cat2[basecat][0].add(name); cat2[basecat][1]+=qty; cat2[basecat][2]+=amt; cat2[basecat][3]+=disc; cat2[basecat][4]+=rev
        if is_teamgou_shell(name) or is_peripheral(name):
            other_skus[name][0]+=qty; other_skus[name][1]+=rev
            continue
        dept = dept_of_category(primary, secondary, store)
        if dept is None:
            dept = dept_by_name(name, store)
            if dept is None:
                other_skus[name][0]+=qty; other_skus[name][1]+=rev
                unmatched+=1
                continue
        dept_pos[dept]+=rev
        sh=o2shift.get(str(r[ci_o]))
        if sh in ("白班","晚班"):
            cross[(dept,sh)]+=rev
    return dept_pos, cross, cat1, cat2, other_skus, unmatched

# ===================== 5) 团购 → 部门 =====================
def load_teamgou():
    # 美团大梦
    rows=[]  # (store, brand, name, cnt, price, settle)
    wb=openpyxl.load_workbook(g("42323734_团购收益明细_*.xlsx"),data_only=True)
    ws=wb["收益明细表"]; data=list(ws.iter_rows(values_only=True))
    hdr=[str(c).strip() if c else '' for c in data[1]]
    ci_store=hdr.index("消费门店"); ci_pkg=hdr.index("套餐名")
    ci_total=hdr.index("总收入（元）")
    ci_settle=hdr.index("结算价(总收入-美团点评技术服务费-商家营销费用-消费后退-其他调整)（元）")
    ag=defaultdict(lambda:[0,0.0,0.0])
    for r in data[2:]:
        if not r[ci_pkg]: continue
        store="西湖" if "西湖" in str(r[ci_store]) else "滨江"
        k=(store,"大梦",str(r[ci_pkg]))
        ag[k][0]+=1; ag[k][1]+=float(r[ci_total] or 0); ag[k][2]+=float(r[ci_settle] or 0)
    # 点评可能实验室
    wb2=openpyxl.load_workbook(g("新版收益明细_*团购_*.xlsx"),data_only=True)
    ws2=wb2["收益明细"]; data2=list(ws2.iter_rows(values_only=True))
    hdr2=[str(c).strip() if c else '' for c in data2[0]]
    ci_store2=hdr2.index("美团门店名称"); ci_pkg2=hdr2.index("项目名称")
    ci_price2=hdr2.index("售价（美团售价）"); ci_merch2=hdr2.index("商家应得")
    for r in data2[1:]:
        if not r[ci_pkg2]: continue
        store="西湖" if "西湖" in str(r[ci_store2]) else "滨江"
        k=(store,"可能实验室",str(r[ci_pkg2]))
        ag[k][0]+=1; ag[k][1]+=float(r[ci_price2] or 0); ag[k][2]+=float(r[ci_merch2] or 0)
    # 拆部门（用结算/商家应得）
    dept_tg=defaultdict(lambda: defaultdict(float))  # store -> dept -> settle
    voucher=defaultdict(lambda:[0,0.0,0.0])  # store -> [cnt,price,settle]
    detail=[]
    for (store,brand,name),(c,p,s) in sorted(ag.items()):
        split=teamgou_dept_split(name)
        if split is None:
            voucher[store][0]+=c; voucher[store][1]+=p; voucher[store][2]+=s
            detail.append((brand,store,name,c,p,s,"⚠️代金券跳过","—"))
            continue
        rule="; ".join(f"{d} {int(r*100)}%" for d,r in split.items())
        for d,ratio in split.items():
            dept_tg[store][d]+=s*ratio
        detail.append((brand,store,name,c,p,s,rule,rule))
    return dept_tg, voucher, detail

# ===================== 主流程 =====================
def main():
    result={}
    menus={st:load_menu(st) for st in STORES}
    shifts={st:load_shift(st) for st in STORES}
    dept_pos={}; cross={}; cat1={}; cat2={}; others={}; unmatched={}
    orders={}; gifts={}; pays={}
    for st in STORES:
        o2shift=shifts[st][0]
        dept_pos[st],cross[st],cat1[st],cat2[st],others[st],unmatched[st]=load_dishes(st,menus[st],o2shift)
        orders[st],gifts[st],pays[st]=load_orders(st)
    # 每日营收
    daily={}
    for st in STORES:
        dd=defaultdict(lambda:[0,0.0,0.0,0.0])  # date->[orders,amount,payable,discount]
        for o in orders[st]:
            d=o["date"]
            dd[d][0]+=1; dd[d][1]+=o["amount"]; dd[d][2]+=o["payable"]; dd[d][3]+=o["discount"]
        gd=defaultdict(lambda:[0,0.0])
        for gft in gifts[st]:
            gd[gft["date"]][0]+=1; gd[gft["date"]][1]+=gft["amt"]
        daily[st]={d:(dd[d],gd.get(d,[0,0.0])) for d in sorted(dd)}
    dept_tg, voucher, tg_detail = load_teamgou()

    # ---- 部门收入合计 ----
    DEPTS=["厨房","咖啡","精酿","调酒"]
    print("="*72)
    print(f"  大梦 {_m} 月营收分析 — 部门收入（POS菜品 + 团购套餐结算）")
    print("="*72)
    print(f"  {'部门':<6}{'滨江POS':>11}{'滨江团购':>10}{'滨江合计':>11}{'西湖POS':>11}{'西湖团购':>10}{'西湖合计':>11}")
    dept_total={}
    for d in DEPTS:
        bp=dept_pos["滨江"].get(d,0); bt=dept_tg["滨江"].get(d,0); bc=bp+bt
        xp=dept_pos["西湖"].get(d,0); xt=dept_tg["西湖"].get(d,0); xc=xp+xt
        dept_total[d]={"滨江":bc,"西湖":xc}
        print(f"  {d:<6}{bp:>11.2f}{bt:>10.2f}{bc:>11.2f}{xp:>11.2f}{xt:>10.2f}{xc:>11.2f}")

    # ---- 班次营收 ----
    shift_sum=defaultdict(lambda:[0,0.0,0.0])  # (store,shift)->[orders, amount, payable]
    for st in STORES:
        for sh,pay,amt in shifts[st][1]:
            if sh in ("白班","晚班"):
                shift_sum[(st,sh)][0]+=1
                shift_sum[(st,sh)][1]+=amt
                shift_sum[(st,sh)][2]+=pay
    print("\n  班次营收（顾客实付）:")
    for st in STORES:
        for sh in ["白班","晚班"]:
            o,a,p=shift_sum[(st,sh)]
            print(f"    {st}店 {sh}: 订单{o:>5} 实付{p:>11.2f} 客单{p/o if o else 0:>7.2f}")

    # ---- 部门×班次（按部门合计比例校正）----
    print("\n  部门×班次（校正到部门合计）:")
    cross_scaled={}
    for st in STORES:
        for d in DEPTS:
            raw_sum=sum(cross[st].get((d,sh),0) for sh in ["白班","晚班"])
            target=dept_total[d][st]
            for sh in ["白班","晚班"]:
                raw=cross[st].get((d,sh),0)
                cross_scaled[(st,d,sh)]= round(raw/raw_sum*target) if raw_sum>0 else 0
    for st in STORES:
        line=f"    {st}: "+" ".join(f"{d}(白{cross_scaled[(st,d,'白班')]}/晚{cross_scaled[(st,d,'晚班')]})" for d in DEPTS)
        print(line)

    # ---- 12 员工 三元组 ----
    EMP=[
        ("蔡逸丰","西湖","精酿","晚班"),("何简","西湖","厨房","白班"),("宋群喜","西湖","咖啡","白班"),
        ("胡舒","西湖","调酒","晚班"),("郭思儒","西湖","咖啡","白班"),("秦天","西湖","厨房","晚班"),
        ("李想","滨江","调酒","晚班"),("王瑛胤","滨江","咖啡","白班"),("刘润祥","滨江","厨房","晚班"),
        ("朱秋风","滨江","精酿","晚班"),("叶磊","滨江","厨房","白班"),("尹志艳","滨江","厨房","中班"),
    ]
    BJ_KITCHEN={"刘润祥","叶磊","尹志艳"}
    print("\n"+"="*72)
    print("  12 员工 业绩三元组 (部门业绩 / 班次业绩 / 部门×班次业绩)")
    print("="*72)
    emp_out=[]
    for name,st,dept,sh in EMP:
        dr=dept_total.get(dept,{}).get(st,0)
        sr=shift_sum.get((st,sh),[0,0,0])[2] if sh in ("白班","晚班") else 0
        cr=cross_scaled.get((st,dept,sh),0)
        if name in BJ_KITCHEN: sr=0; cr=0
        if sh=="中班": sr=0; cr=0
        emp_out.append((name,st,dept,sh,dr,sr,cr))
        print(f"  {name:<6}{st}店 {dept}/{sh:<3} 部门{dr:>11.2f} 班次{sr:>11.2f} 部门×班次{cr:>9}")

    # 存盘供 workflow 校验 / 写表
    import json
    summary={
        "month":MONTH,
        "dept_pos":{st:dict(dept_pos[st]) for st in STORES},
        "dept_tg":{st:dict(dept_tg[st]) for st in STORES},
        "dept_total":dept_total,
        "shift_sum":{f"{st}|{sh}":shift_sum[(st,sh)] for st in STORES for sh in ["白班","晚班"]},
        "cross_scaled":{f"{st}|{d}|{sh}":cross_scaled[(st,d,sh)] for st in STORES for d in DEPTS for sh in ["白班","晚班"]},
        "voucher":{st:voucher[st] for st in STORES},
        "unmatched":unmatched,
        "others":{st:dict(others[st]) for st in STORES},
        "employees":emp_out,
        "orders_count":{st:len(orders[st]) for st in STORES},
        "pos_payable":{st:round(sum(o["payable"] for o in orders[st]),2) for st in STORES},
        "pos_dishrev":{st:round(sum(o["dish_rev"] for o in orders[st]),2) for st in STORES},
    }
    with open(f"{BASE}/_analysis_summary.json","w") as f:
        json.dump(summary,f,ensure_ascii=False,indent=2,default=str)
    print(f"\n  未归类菜品笔数: 西湖={unmatched['西湖']} 滨江={unmatched['滨江']}")
    print(f"  POS订单数: 西湖={len(orders['西湖'])} 滨江={len(orders['滨江'])}")

    write_xlsx(summary, dept_total, dept_pos, dept_tg, shift_sum, cross_scaled,
               cat1, cat2, daily, pays, voucher, tg_detail, DEPTS, EMP, BJ_KITCHEN)
    print(f"\n  汇总已存: {BASE}/_analysis_summary.json")
    print(f"  分析表已存: {BASE}/大梦可能实验室_{_m}月营收分析_西湖店vs滨江店.xlsx")
    return summary


def write_xlsx(s, dept_total, dept_pos, dept_tg, shift_sum, cross_scaled,
               cat1, cat2, daily, pays, voucher, tg_detail, DEPTS, EMP, BJ_KITCHEN):
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment
    wb=Workbook(); wb.remove(wb.active)
    H=Font(bold=True); TITLE=Font(bold=True,size=13)
    GREEN=PatternFill("solid",fgColor="E2EFDA"); BLUE=PatternFill("solid",fgColor="DDEBF7")
    HEADER=PatternFill("solid",fgColor="44546A"); HW=Font(bold=True,color="FFFFFF")
    def sheet(name): return wb.create_sheet(name)
    def hdr(ws,row,cols,fill=True):
        for j,c in enumerate(cols,1):
            cell=ws.cell(row=row,column=j,value=c)
            if fill: cell.fill=HEADER; cell.font=HW
    # —— 总览 ——
    ws=sheet("总览"); ws["A1"]=f"大梦·可能实验室 — {_y}年{_m}月营收总览（西湖店 vs 滨江店）"; ws["A1"].font=TITLE
    ws["A2"]=f"区间 {_y}/{_m:02d}/01–{_m:02d}/{DAYS} ｜ 数据源：POS店内订单明细 + 美团团购收益 + 点评/可能实验室收益"
    r=4; ws.cell(r,1,"指标").font=H; ws.cell(r,2,"滨江店").font=H; ws.cell(r,3,"西湖店").font=H; ws.cell(r,4,"两店合计").font=H
    bj_dep=sum(dept_total[d]["滨江"] for d in DEPTS); xh_dep=sum(dept_total[d]["西湖"] for d in DEPTS)
    rows=[
        ("4部门收入合计(POS+团购)", bj_dep, xh_dep),
        ("POS菜品收入", sum(dept_pos["滨江"].values()), sum(dept_pos["西湖"].values())),
        ("团购套餐结算", sum(dept_tg["滨江"].values()), sum(dept_tg["西湖"].values())),
        ("白班实付", shift_sum[("滨江","白班")][2], shift_sum[("西湖","白班")][2]),
        ("晚班实付", shift_sum[("滨江","晚班")][2], shift_sum[("西湖","晚班")][2]),
        ("POS订单数", s["orders_count"]["滨江"], s["orders_count"]["西湖"]),
    ]
    for i,(k,b,x) in enumerate(rows):
        rr=r+1+i; ws.cell(rr,1,k); ws.cell(rr,2,round(b,2)); ws.cell(rr,3,round(x,2)); ws.cell(rr,4,round(b+x,2))
    # —— 部门收入 ——
    ws=sheet("部门收入"); ws["A1"]="负责部门收入 — POS菜品收入 + 团购套餐结算（代金券不重算）"; ws["A1"].font=TITLE
    hdr(ws,3,["部门","滨江_POS","滨江_团购","滨江_合计","西湖_POS","西湖_团购","西湖_合计","两店合计"])
    for i,d in enumerate(DEPTS):
        rr=4+i; bp=dept_pos["滨江"].get(d,0); bt=dept_tg["滨江"].get(d,0); xp=dept_pos["西湖"].get(d,0); xt=dept_tg["西湖"].get(d,0)
        for j,v in enumerate([d,round(bp,2),round(bt,2),round(bp+bt,2),round(xp,2),round(xt,2),round(xp+xt,2),round(bp+bt+xp+xt,2)],1):
            ws.cell(rr,j,v)
    tot_r=4+len(DEPTS)
    ws.cell(tot_r,1,"合计").font=H
    for j,col in enumerate(["滨江_POS","滨江_团购","滨江_合计","西湖_POS","西湖_团购","西湖_合计","两店合计"],2):
        ws.cell(tot_r,j,round(sum(ws.cell(4+i,j).value for i in range(len(DEPTS))),2)).font=H
    # 占比块
    ws.cell(tot_r+2,1,"② 4部门占比").font=H
    hdr(ws,tot_r+3,["部门","滨江合计","西湖合计","两店合计","滨江占比","西湖占比"])
    for i,d in enumerate(sorted(DEPTS,key=lambda x:-(dept_total[x]['滨江']+dept_total[x]['西湖']))):
        rr=tot_r+4+i; bc=dept_total[d]["滨江"]; xc=dept_total[d]["西湖"]
        ws.cell(rr,1,d); ws.cell(rr,2,round(bc,2)); ws.cell(rr,3,round(xc,2)); ws.cell(rr,4,round(bc+xc,2))
        ws.cell(rr,5,round(bc/bj_dep,4)); ws.cell(rr,6,round(xc/xh_dep,4))
    # —— 团购→部门归口 ——
    ws=sheet("团购→部门归口"); ws["A1"]="团购平台项目 → 部门归口明细（结算/商家应得口径）"; ws["A1"].font=TITLE
    hdr(ws,3,["品牌","门店","项目名称","笔数","售价","结算/应得","归口规则"])
    for i,(brand,store,name,c,p,sv,rule,_) in enumerate(tg_detail):
        rr=4+i
        for j,v in enumerate([brand,store+"店",name,c,round(p,2),round(sv,2),rule],1): ws.cell(rr,j,v)
    base=4+len(tg_detail)+1
    ws.cell(base,1,"② 团购套餐→部门 汇总（剔除代金券）").font=H
    hdr(ws,base+1,["门店","部门","金额"])
    rr=base+2
    for st in ["滨江","西湖"]:
        for d in DEPTS:
            v=dept_tg[st].get(d,0)
            if v: ws.cell(rr,1,st+"店"); ws.cell(rr,2,d); ws.cell(rr,3,round(v,2)); rr+=1
    ws.cell(rr,1,"③ 代金券(跳过,未重算)").font=H; rr+=1
    hdr(ws,rr,["门店","笔数","售价","结算"]); rr+=1
    for st in ["滨江","西湖"]:
        v=voucher[st]; ws.cell(rr,1,st+"店"); ws.cell(rr,2,v[0]); ws.cell(rr,3,round(v[1],2)); ws.cell(rr,4,round(v[2],2)); rr+=1
    # —— 班次营收 ——
    ws=sheet("班次营收"); ws["A1"]="班次营收对比 — 白班 vs 晚班（顾客实付）"; ws["A1"].font=TITLE
    hdr(ws,3,["门店","餐段","订单数","订单金额(原价)","顾客实付","日均订单","日均实付","客单价(实付)"])
    rr=4
    for st in ["滨江","西湖"]:
        for sh in ["白班","晚班"]:
            o,a,p=shift_sum[(st,sh)]
            for j,v in enumerate([st+"店",sh,o,round(a,2),round(p,2),round(o/DAYS,1),round(p/DAYS,1),round(p/o if o else 0,2)],1): ws.cell(rr,j,v); rr+=0
            rr+=1
    # —— 部门×班次 ——
    ws=sheet("部门x班次"); ws["A1"]="部门 × 班次 营收（校正到部门合计）"; ws["A1"].font=TITLE
    hdr(ws,3,["门店","部门","白班","晚班","合计"])
    rr=4
    for st in ["滨江","西湖"]:
        for d in DEPTS:
            wv=cross_scaled[(st,d,"白班")]; nv=cross_scaled[(st,d,"晚班")]
            for j,v in enumerate([st+"店",d,wv,nv,wv+nv],1): ws.cell(rr,j,v)
            rr+=1
    # —— 品类营收(一级) ——
    ws=sheet("品类营收(一级)"); ws["A1"]="品类营收 — 一级分类 西湖 vs 滨江"; ws["A1"].font=TITLE
    hdr(ws,3,["一级分类","滨江_件数","滨江_收入","西湖_件数","西湖_收入"])
    allcat=sorted(set(cat1["滨江"])|set(cat1["西湖"]), key=lambda c:-(cat1['滨江'].get(c,[0,0,0,0])[3]+cat1['西湖'].get(c,[0,0,0,0])[3]))
    for i,c in enumerate(allcat):
        rr=4+i; b=cat1["滨江"].get(c,[0,0,0,0]); x=cat1["西湖"].get(c,[0,0,0,0])
        for j,v in enumerate([c,int(b[0]),round(b[3],2),int(x[0]),round(x[3],2)],1): ws.cell(rr,j,v)
    # —— 品类营收(二级) ——
    ws=sheet("品类营收(二级)"); ws["A1"]="品类营收 — 二级分类(基础分类) 各店Top"; ws["A1"].font=TITLE
    r0=3
    for st in ["滨江","西湖"]:
        ws.cell(r0,1,f"■ {st}店").font=H; r0+=1
        hdr(ws,r0,["基础分类","SKU数","销售件数","菜品收入"]); r0+=1
        top=sorted(cat2[st].items(), key=lambda x:-x[1][4])[:30]
        for c,v in top:
            ws.cell(r0,1,c); ws.cell(r0,2,len(v[0])); ws.cell(r0,3,int(v[1])); ws.cell(r0,4,round(v[4],2)); r0+=1
        r0+=1
    # —— 每日营收 ——
    for st in ["滨江","西湖"]:
        ws=sheet(f"{st}店_每日营收"); ws["A1"]=f"{st}店 — {_y}年{_m}月每日营收"; ws["A1"].font=TITLE
        hdr(ws,3,["日期","订单数","订单金额(原价)","顾客应付","订单优惠","赠菜笔数","赠菜金额"])
        rr=4
        for d,(dd,gd) in daily[st].items():
            for j,v in enumerate([d,dd[0],round(dd[1],2),round(dd[2],2),round(dd[3],2),gd[0],round(gd[1],2)],1): ws.cell(rr,j,v)
            rr+=1
    # —— 支付方式分布 ——
    ws=sheet("支付方式分布"); ws["A1"]="支付方式分布（POS端）"; ws["A1"].font=TITLE
    r0=3
    for st in ["滨江","西湖"]:
        ws.cell(r0,1,f"■ {st}店").font=H; r0+=1
        hdr(ws,r0,["支付方式","笔数","支付金额"]); r0+=1
        for way,(c,amt) in sorted(pays[st].items(), key=lambda x:-x[1][1]):
            ws.cell(r0,1,way); ws.cell(r0,2,c); ws.cell(r0,3,round(amt,2)); r0+=1
        r0+=1
    # —— 12员工业绩（工资交接）——
    ws=sheet("12员工业绩"); ws["A1"]="12 员工业绩三元组（→ 工资表V2 cols 19/20/21）"; ws["A1"].font=TITLE
    hdr(ws,3,["姓名","归属","部门","班次","部门业绩","班次业绩","部门×班次业绩"])
    for i,(name,st,dept,sh,dr,sr,cr) in enumerate(s["employees"]):
        rr=4+i
        for j,v in enumerate([name,st+"店",dept,sh,round(dr,2),round(sr,2),cr],1): ws.cell(rr,j,v)
        fill=GREEN if st=="西湖" else BLUE
        for j in range(1,8): ws.cell(rr,j).fill=fill
    # —— 深度分析 / 改进建议 占位（workflow 填充）——
    ws=sheet("深度分析"); ws["A1"]="深度分析 — 异常项与关键洞察（见正文）"; ws["A1"].font=TITLE
    ws=sheet("改进建议"); ws["A1"]=f"基于{_m}月数据的改进建议（见正文）"; ws["A1"].font=TITLE

    for ws in wb.worksheets:
        ws.column_dimensions["A"].width=26
        for col in "BCDEFGH": ws.column_dimensions[col].width=14
    wb.save(f"{BASE}/大梦可能实验室_{_m}月营收分析_西湖店vs滨江店.xlsx")

if __name__=="__main__":
    main()