#!/usr/bin/env python3
"""Validate the 18-SKU quotation pack and front-of-house selection matrix."""

from __future__ import annotations

import argparse
import json
import re
from pathlib import Path
from zipfile import ZipFile

from openpyxl import load_workbook


ERRORS = {"#REF!", "#DIV/0!", "#VALUE!", "#N/A", "#NAME?"}


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--root", type=Path, required=True)
    args = parser.parse_args()
    root = args.root
    files = sorted((root / "01-18个SKU独立报价单").glob("*.xlsx"))
    if len(files) != 18:
        raise SystemExit(f"expected 18 quote files, got {len(files)}")
    audit = json.loads((root / "03-内部过程-可忽略/build-audit.json").read_text(encoding="utf-8"))
    audit_map = {x["sku"]: x for x in audit["items"]}
    formula_count = 0
    for path in files:
        match = re.match(r"(SPU\d{2}-SKU\d{2})-", path.name)
        if not match:
            raise SystemExit(f"bad filename: {path.name}")
        code = match.group(1)
        formula_wb = load_workbook(path, data_only=False)
        value_wb = load_workbook(path, data_only=True)
        expected = ["01-客户报价样板", "02-产品配置清单", "03-内部测算_严禁外发", "04-报价思路与边界"]
        if formula_wb.sheetnames != expected:
            raise SystemExit(f"bad sheets: {path.name} {formula_wb.sheetnames}")
        formula_count += sum(
            1
            for ws in formula_wb.worksheets
            for row in ws.iter_rows()
            for cell in row
            if isinstance(cell.value, str) and cell.value.startswith("=")
        )
        for ws in value_wb.worksheets:
            for row in ws.iter_rows():
                for cell in row:
                    value = cell.value
                    if isinstance(value, str) and value in ERRORS:
                        raise SystemExit(f"formula error: {path.name} {ws.title}!{cell.coordinate} {value}")
                    if isinstance(value, str) and any(x in value for x in ("NC-", "FS-", "IM-")):
                        raise SystemExit(f"legacy SKU code: {path.name} {ws.title}!{cell.coordinate}")
        values = [cell.value for row in value_wb["01-客户报价样板"].iter_rows() for cell in row]
        if audit_map[code]["priced_subtotal"] not in values:
            raise SystemExit(f"missing cached subtotal: {path.name}")
    overview = load_workbook(root / "02-总览与报价思路/智慧食堂18个SKU配置报价总览.xlsx", data_only=True)
    for row in range(6, 24):
        code = overview["01-SKU报价总览"].cell(row, 3).value
        if overview["01-SKU报价总览"].cell(row, 6).value != audit_map[code]["priced_subtotal"]:
            raise SystemExit(f"overview subtotal mismatch: {code}")
    matrix = load_workbook(root / "02-总览与报价思路/智慧食堂前厅12个SKU_PC功能模块对比与选配指引.xlsx")
    if matrix["01-PC功能模块对比"].max_row != 45:
        raise SystemExit("PC function matrix must contain 40 feature rows")
    if matrix["02-SKU硬件对应"].max_row != 17:
        raise SystemExit("hardware matrix must contain 12 SKU rows")
    if matrix["03-销售升级话术"].max_row != 45:
        raise SystemExit("sales talk matrix must contain 40 rows")
    with ZipFile(root / "02-总览与报价思路/智慧食堂18个SKU独立报价单.zip") as archive:
        names = archive.namelist()
        if len(names) != 18 or any("__MACOSX" in name or ".DS_Store" in name for name in names):
            raise SystemExit("invalid quotation ZIP")
    print(json.dumps({"status": "PASS", "quote_files": 18, "formula_cells": formula_count, "pc_function_rows": 40, "hardware_rows": 12, "sales_talk_rows": 40}, ensure_ascii=False))


if __name__ == "__main__":
    main()