#!/usr/bin/env python3
"""Validate the 12-SKU strategy Excel, HTML and model."""

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"))
    workbook = root / "01-最终交付/智慧食堂12个SKU年度产品与销售战略.xlsx"
    formula_wb = load_workbook(workbook, data_only=False)
    value_wb = load_workbook(workbook, data_only=True)
    expected = ["00-战略总览", "01-12SKU产品地图", "02-SPAN-PDC", "03-7-2-1资源配置", "04-年度销售任务", "05-销售漏斗", "06-季度战役", "07-战斗编组", "08-KPI与停止线", "09-证据与缺口"]
    if formula_wb.sheetnames != expected:
        raise SystemExit(f"unexpected sheets: {formula_wb.sheetnames}")
    formulas = sum(1 for ws in formula_wb for row in ws.iter_rows() for cell in row if isinstance(cell.value, str) and cell.value.startswith("="))
    if formulas != 40:
        raise SystemExit(f"expected 40 formulas, got {formulas}")
    if value_wb["04-年度销售任务"]["G17"].value != 800 or value_wb["06-季度战役"]["B9"].value != 800:
        raise SystemExit("cached annual target mismatch")
    if value_wb["06-季度战役"]["C9"].value != 22:
        raise SystemExit("cached win target mismatch")
    if len(model["skus"]) != 12 or sum(x["target_wan"] for x in model["skus"]) != 800:
        raise SystemExit("strategy model mismatch")
    splits = {
        "focus": sum(x["target_wan"] for x in model["skus"] if x["role"].startswith("聚焦")),
        "breakthrough": sum(x["target_wan"] for x in model["skus"] if x["role"].startswith("突破")),
        "layout": sum(x["target_wan"] for x in model["skus"] if x["role"].startswith("布局")),
    }
    if splits != {"focus": 560, "breakthrough": 160, "layout": 80}:
        raise SystemExit(f"7-2-1 split mismatch: {splits}")
    html_path = root / "01-最终交付/smart-canteen-12-sku-annual-strategy.html"
    if not html_path.exists() or html_path.stat().st_size < 5000:
        raise SystemExit("HTML output missing or too small")
    print(json.dumps({"status":"PASS","sheets":10,"skus":12,"formulas":40,"target_wan":800,"split_wan":splits}, ensure_ascii=False))


if __name__ == "__main__":
    main()