#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""各部门「在架SKU」卖最差排名(按白班/晚班拆分)。

⚠️ 口径要点(5月踩坑):
- 菜品库导出**无「售卖状态」字段**(导出时是"全部状态",在售/下架混在一起无法区分)。
- 因此用「当月有售(≥1件)」作为"在架"的代理 —— 下架的季节菜(披萨/牛排/汉堡线)自动排除。
- 代价: 会漏掉极少数"在架但整月真没人点"的款。要100%精确, 需用户重新导出菜品库勾选「售卖状态=售卖中」。

用法: python3 menu_onsale_ranking.py <月度账务目录>
输出: 每店每部门, 在架SKU按销量升序(白班/晚班分列), 标注濒死(≤3件)。
"""
import openpyxl, warnings, glob, sys
from collections import defaultdict
warnings.filterwarnings('ignore')
BASE=(sys.argv[1] if len(sys.argv)>1 else ".").rstrip("/")
def fh(ws,k):
    for i,r in enumerate(ws.iter_rows(values_only=True),1):
        if r and any(c==k for c in r if c is not None): return i,[str(c).strip() if c else '' for c in r]
def g(p): f=glob.glob(f"{BASE}/{p}"); return f[0]

# 各部门一级分类归属(与 build_analysis 一致)
DEPTMAP={
 "西湖":{"厨房":["小吃","主食","零食"],"咖啡":["咖啡","甜品","茶饮Tea","茶饮tea"]},
 "滨江":{"厨房":["肉肉肉","小吃","主食","零食"],"咖啡":["咖啡","甜品点心","茶饮Tea","茶饮tea"]},
}
def dept_of(p,n,store):
    p=(p or "").strip();pl=p.lower()
    for d,cats in DEPTMAP[store].items():
        if p in cats or (d=="厨房" and pl.startswith("brunch")): return d
    if store=="西湖":
        if p=="软饮": return "咖啡" if False else "调酒"
        if p.startswith("精酿"): return "精酿"
        if p in ["鸡尾酒","纯饮","纯饮酒"]: return "精酿" if any(k in str(n) for k in ["酒头","畅饮"]) else "调酒"
    else:
        if p=="无咖无醇": return "咖啡"
        if p.startswith("精酿") or p in ["瓶罐精酿","瓶装精酿"]: return "精酿"
        if p in ["鸡尾酒","纯饮酒","纯饮"]: return "精酿" if any(k in str(n) for k in ["酒头","畅饮"]) else "调酒"
    return None

DEPTS=["厨房","咖啡","精酿","调酒"]
for store in ["西湖","滨江"]:
    mf=g(f"大梦_可能实验室_{store}店_菜品库_*.xlsx"); wb=openpyxl.load_workbook(mf,data_only=True); ws=wb["菜品"]
    hr,hdr=fh(ws,"菜品编码（SPUID）"); cn=hdr.index("菜品名称"); cc=hdr.index("基础分类"); cpx=hdr.index("售卖价")
    menu={}
    for i,r in enumerate(ws.iter_rows(values_only=True),1):
        if i<=hr: continue
        if r[cn] and r[cc]:
            nm=str(r[cn]).strip()
            if nm not in menu:
                try: px=float(r[cpx]) if r[cpx] not in (None,"") else None
                except: px=None
                menu[nm]=(str(r[cc]).split("/")[0],px)
    gf=g(f"大梦可能实验室（{store}店）_全渠道订单明细_*.xlsx"); wb=openpyxl.load_workbook(gf,data_only=True); ws=wb.active
    hr,hdr=fh(ws,"营业日期"); co=hdr.index("订单号"); cs=hdr.index("餐段")
    o2s={str(r[co]):(str(r[cs]) if r[cs] else None) for i,r in enumerate(ws.iter_rows(values_only=True),1) if i>hr and r[co]}
    of=g(f"大梦_可能实验室_{store}店__店内订单明细*.xlsx"); wb=openpyxl.load_workbook(of,data_only=True); ws=wb["菜品明细"]
    hr,hdr=fh(ws,"订单编号"); cio=hdr.index("订单编号"); cin=hdr.index("菜品名称"); ciq=hdr.index("销售数量"); cir=hdr.index("菜品收入（元）")
    sales=defaultdict(lambda:defaultdict(lambda:[0.0,0.0]))
    for i,r in enumerate(ws.iter_rows(values_only=True),1):
        if i<=hr: continue
        nm=str(r[cin]).strip() if r[cin] else None
        if nm and nm!="--" and nm in menu:
            seg=o2s.get(str(r[cio]))
            if seg in ("白班","晚班"): sales[nm][seg][0]+=float(r[ciq] or 0); sales[nm][seg][1]+=float(r[cir] or 0)
    print("="*66); print(f"{store}店 在架(当月有售)出品 卖最差排名"); print("="*66)
    for dept in DEPTS:
        items=[nm for nm in sales if dept_of(menu[nm][0],nm,store)==dept]
        rows=[]
        for nm in items:
            wq,wr=sales[nm]["白班"]; nq,nr=sales[nm]["晚班"]; tq=wq+nq; tr=wr+nr
            rows.append((tr,tq,nm,wq,nq))
        rows.sort(key=lambda x:(x[0],x[1]))
        dying=sum(1 for r in rows if r[1]<=3)
        print(f"\n--- {dept}: 在架{len(rows)}款, 濒死(≤3件){dying}款, 卖最差Top8 ---")
        for tr,tq,nm,wq,nq in rows[:8]:
            print(f"   合计¥{tr:>6.0f}/{tq:>3.0f}件 (白{wq:.0f}/晚{nq:.0f})  {nm[:30]}")