#!/usr/bin/env python3
"""Validate the three-SPU, nine-SKU strategy deliverables."""

from __future__ import annotations

import argparse
import json
from pathlib import Path

from openpyxl import load_workbook


def main():
    parser=argparse.ArgumentParser(); parser.add_argument("--root",type=Path,required=True); args=parser.parse_args(); root=args.root
    model=json.loads((root/"02-内部证据/strategy-model.json").read_text(encoding="utf-8"))
    wf=load_workbook(root/"01-最终交付/智慧食堂三大SPU九SKU年度产品与销售战略.xlsx",data_only=False)
    wv=load_workbook(root/"01-最终交付/智慧食堂三大SPU九SKU年度产品与销售战略.xlsx",data_only=True)
    expected=["00-战略总览","01-三SPU布局","02-九SKU产品地图","03-SPAN-PDC","04-7-2-1资源配置","05-年度销售任务","06-销售漏斗","07-五支战斗编组","08-季度战役","09-跨SPU扩单","10-KPI与缺口"]
    if wf.sheetnames!=expected: raise SystemExit(f"unexpected sheets: {wf.sheetnames}")
    formulas=sum(1 for ws in wf for row in ws.iter_rows() for cell in row if isinstance(cell.value,str) and cell.value.startswith("="))
    if formulas!=34: raise SystemExit(f"formula count mismatch: {formulas}")
    if [wv["01-三SPU布局"].cell(r,4).value for r in range(5,8)]!=[720,300,180]: raise SystemExit("SPU target mismatch")
    if wv["05-年度销售任务"]["H14"].value!=1200 or wv["08-季度战役"]["B9"].value!=1200: raise SystemExit("annual target cache mismatch")
    if wv["08-季度战役"]["C9"].value!=24: raise SystemExit("project-win cache mismatch")
    if len(model["spus"])!=3 or len(model["skus"])!=9: raise SystemExit("model count mismatch")
    if sum(x["target_wan"] for x in model["skus"])!=1200 or sum(x["units"] for x in model["skus"])!=33: raise SystemExit("sales model mismatch")
    html=root/"01-最终交付/smart-canteen-3-spu-9-sku-annual-strategy.html"
    if not html.exists() or html.stat().st_size<5000: raise SystemExit("HTML missing or too small")
    print(json.dumps({"status":"PASS","sheets":11,"spus":3,"skus":9,"formulas":34,"target_wan":1200,"spu_split":[720,300,180],"projects":24,"sku_order_lines":33},ensure_ascii=False))


if __name__=="__main__": main()