#!/usr/bin/env python3
from __future__ import annotations

import argparse
import json
from pathlib import Path

from openpyxl import Workbook
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation


NAVY = "16324F"
ORANGE = "F59E0B"
YELLOW = "FFF3BF"
TEAL = "0F766E"
PURPLE = "7C3AED"
LIGHT_ORANGE = "FFF7E6"
LIGHT_TEAL = "EAF7F5"
LIGHT_PURPLE = "F3EEFF"
LIGHT_BLUE = "EDF5FC"
LIGHT_GRAY = "F3F4F6"
WHITE = "FFFFFF"
GREEN = "E9F7EF"
RED = "FDECEC"
THIN = Side(style="thin", color="D1D5DB")


def font(size=11, bold=False, color="1F2937"):
    return Font(name="微软雅黑", size=size, bold=bold, color=color)


def fill(color):
    return PatternFill("solid", fgColor=color)


def title(ws, text, columns):
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=columns)
    c = ws.cell(1, 1, text)
    c.font = font(18, True, WHITE)
    c.fill = fill(NAVY)
    c.alignment = Alignment(vertical="center")
    ws.row_dimensions[1].height = 32


def header(ws, row, labels, color=NAVY):
    for col, label in enumerate(labels, 1):
        c = ws.cell(row, col, label)
        c.font = font(10, True, WHITE)
        c.fill = fill(color)
        c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        c.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)


def style_grid(ws, start_row, end_row, start_col, end_col):
    for row in ws.iter_rows(min_row=start_row, max_row=end_row, min_col=start_col, max_col=end_col):
        for c in row:
            c.font = font(10)
            c.alignment = Alignment(vertical="top", wrap_text=True)
            c.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)


def compact_modules(item):
    return "；".join(f"{m['name']}：{'、'.join(m['points'])}" for m in item["core_modules"])


def load_items(path):
    items = [json.loads(p.read_text(encoding="utf-8")) for p in path.glob("*.json")]
    return sorted(items, key=lambda item: item["sequence"])


def add_detail_sheet(wb, title_name, spu, items, source_rows, accent, pale):
    ws = wb.create_sheet(title_name)
    title(ws, f"{spu}｜SKU详情卡", 8)
    ws.sheet_view.showGridLines = False
    cursor = 3
    for item in items:
        row = source_rows[item["sku"]]
        ws.merge_cells(start_row=cursor, start_column=1, end_row=cursor, end_column=8)
        c = ws.cell(cursor, 1, f"='02-SKU内容总表'!A{row}&\"｜\"&'02-SKU内容总表'!C{row}&\"｜\"&'02-SKU内容总表'!D{row}")
        c.font = font(14, True, WHITE)
        c.fill = fill(accent)
        c.alignment = Alignment(vertical="center")
        ws.row_dimensions[cursor].height = 26
        fields = [
            ("购买人", "E"), ("一句话产品", "F"), ("唯一卖点", "G"), ("适用客户", "H"),
            ("标准组合", "I"), ("核心模块", "J"), ("使用主流程", "K"), ("客户价值", "L"),
            ("产品边界", "M"), ("部署方式", "O"), ("入口形态", "P"), ("统一选配", "Q"),
            ("代表项目", "N"), ("证据状态", "R"),
        ]
        for idx, (label, col) in enumerate(fields, 1):
            r = cursor + idx
            ws.cell(r, 1, label)
            ws.cell(r, 2, f"='02-SKU内容总表'!{col}{row}")
            ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=8)
            ws.cell(r, 1).fill = fill(pale)
            ws.cell(r, 1).font = font(10, True, accent)
            ws.cell(r, 2).font = font(10)
            for cc in range(1, 9):
                cell = ws.cell(r, cc)
                cell.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
                cell.alignment = Alignment(vertical="top", wrap_text=True)
            ws.row_dimensions[r].height = 34 if label not in {"核心模块", "产品边界", "证据状态"} else 48
        cursor += len(fields) + 2
    ws.column_dimensions["A"].width = 16
    for col in range(2, 9):
        ws.column_dimensions[get_column_letter(col)].width = 18
    ws.freeze_panes = "A3"


def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--manifests", required=True, type=Path)
    parser.add_argument("--output", required=True, type=Path)
    args = parser.parse_args()
    items = load_items(args.manifests)
    wb = Workbook()
    wb.calculation.calcMode = "auto"
    wb.calculation.fullCalcOnLoad = True
    wb.calculation.forceFullCalc = True
    ws = wb.active
    ws.title = "00-使用说明"
    title(ws, "智慧食堂 SKU 内容总表｜横向对比＋详情卡", 9)
    ws.sheet_view.showGridLines = False
    notes = [
        (3, "当前已确认SKU数", "='02-SKU内容总表'!T2"),
        (4, "最终确认SKU数", len(items)),
        (5, "状态", '=IF(B3=B4,"18个SKU范围已确认定版","SKU数量待复核")'),
        (7, "主编辑入口", "02-SKU内容总表：黄色单元格可继续修改，横向对比和详情卡自动引用。"),
        (8, "横向比较", "01-横向对比：一行一个SKU，用于会议、筛选、选型和版本评审。"),
        (9, "详情查看", "03—05详情卡：按SPU展开每个SKU完整内容。"),
        (10, "证据原则", "项目案例只作证据；标准能力、选配、接口、部署与非标必须分开。"),
        (11, "数量结论", "原17个SKU基础上新增哈尔滨专业SKU，当前共18个。"),
        (12, "版本结论", "公开SKU统一使用SPUxx-SKUxx编码，新增SPU01-SKU12营养健康智厨专业版。"),
    ]
    for r, label, value in notes:
        ws.cell(r, 1, label).font = font(11, True, NAVY)
        ws.cell(r, 1).fill = fill(LIGHT_BLUE)
        ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=9)
        ws.cell(r, 2, value).font = font(11)
        ws.cell(r, 2).alignment = Alignment(wrap_text=True, vertical="top")
        for c in range(1, 10):
            ws.cell(r, c).border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
        ws.row_dimensions[r].height = 30
    ws.column_dimensions["A"].width = 18
    for c in range(2, 10): ws.column_dimensions[get_column_letter(c)].width = 16

    ws = wb.create_sheet("01-横向对比")
    labels = ["序号", "SPU", "SKU编码", "SKU名称", "购买人", "唯一卖点", "标准组合", "使用主流程", "部署方式", "入口形态", "客户价值", "产品边界", "统一选配", "证据状态", "版本意见"]
    title(ws, "SKU横向对比｜一行一个SKU", len(labels))
    ws.cell(2, 1, "来源：02-SKU内容总表；当前18个SKU，公开编码统一为SPUxx-SKUxx。黄色列填写评审意见。")
    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(labels))
    ws.cell(2, 1).font = font(10, False, "4B5563")
    header(ws, 4, labels)
    for idx, _ in enumerate(items, 5):
        source = idx
        source_row = idx
        cols = ["A", "B", "C", "D", "E", "G", "I", "K", "O", "P", "L", "M", "Q", "R"]
        for col_idx, source_col in enumerate(cols, 1):
            ws.cell(idx, col_idx, f"='02-SKU内容总表'!{source_col}{source_row}")
        ws.cell(idx, 15, "")
        ws.cell(idx, 15).fill = fill(YELLOW)
    style_grid(ws, 5, 4 + len(items), 1, len(labels))
    ws.freeze_panes = "A5"
    ws.auto_filter.ref = f"A4:O{4+len(items)}"
    widths = [9, 12, 12, 22, 24, 32, 32, 44, 22, 25, 28, 38, 30, 34, 24]
    for i, width in enumerate(widths, 1): ws.column_dimensions[get_column_letter(i)].width = width
    for r in range(5, 5 + len(items)): ws.row_dimensions[r].height = 66

    ws = wb.create_sheet("02-SKU内容总表")
    labels = ["序号", "SPU", "SKU编码", "SKU名称", "购买人", "一句话产品", "唯一卖点", "适用客户", "标准组合", "核心模块", "使用主流程", "客户价值", "产品边界", "代表项目", "部署方式", "入口形态", "统一选配", "证据状态", "修改意见"]
    title(ws, "SKU内容总表｜唯一主要编辑入口", len(labels))
    ws.cell(2, 1, "已证实SKU数")
    ws.cell(2, 2, f"=COUNTA(C5:C{4+len(items)})")
    ws.cell(2, 4, "黄色字段可修改；灰色编码建议稳定；绿色项目仅作内部证据，不进入销售脑图。")
    ws.merge_cells(start_row=2, start_column=4, end_row=2, end_column=len(labels))
    header(ws, 4, labels)
    source_rows = {}
    for row, item in enumerate(items, 5):
        source_rows[item["sku"]] = row
        vals = [
            item["sequence"], item["spu"], item["sku"], item["name"], item["buyer"], item["one_liner"], item["primary_buying_point"],
            "；".join(item["suitable_for"]), "；".join(item["standard_combo"]), compact_modules(item),
            " → ".join(item["main_flow"]), "；".join(item["customer_values"]), "；".join(item["boundaries"]),
            item["representative_projects"], item["deployment"], item["entry_points"], item["options"], item["evidence_status"], "",
        ]
        for col, value in enumerate(vals, 1):
            ws.cell(row, col, value)
            if col in {1, 3}: ws.cell(row, col).fill = fill(LIGHT_GRAY)
            elif col == 14: ws.cell(row, col).fill = fill(GREEN)
            elif col not in {2, 18}: ws.cell(row, col).fill = fill(YELLOW)
            if col == 18 and ("待" in value or "冲突" in value): ws.cell(row, col).fill = fill(RED)
    style_grid(ws, 5, 4 + len(items), 1, len(labels))
    ws.freeze_panes = "A5"
    ws.auto_filter.ref = f"A4:S{4+len(items)}"
    widths = [9, 12, 12, 22, 25, 34, 34, 28, 30, 64, 48, 30, 42, 28, 25, 28, 28, 42, 26]
    for i, width in enumerate(widths, 1): ws.column_dimensions[get_column_letter(i)].width = width
    for r in range(5, 5 + len(items)): ws.row_dimensions[r].height = 82
    ws["T2"] = f"=COUNTA(C5:C{4+len(items)})"
    ws.column_dimensions["T"].hidden = True

    by_spu = {name: [x for x in items if x["spu"] == name] for name in ["营养结算", "智慧食安", "智慧进销存"]}
    add_detail_sheet(wb, "03-营养结算详情", "营养结算", by_spu["营养结算"], source_rows, ORANGE, LIGHT_ORANGE)
    add_detail_sheet(wb, "04-智慧食安详情", "智慧食安", by_spu["智慧食安"], source_rows, TEAL, LIGHT_TEAL)
    add_detail_sheet(wb, "05-智慧进销存详情", "智慧进销存", by_spu["智慧进销存"], source_rows, PURPLE, LIGHT_PURPLE)

    ws = wb.create_sheet("06-范围确认与证据边界")
    title(ws, "范围确认、证据边界与后续产品门禁", 7)
    labels = ["序号", "类型", "对象", "确认结论/证据缺口", "状态", "确认来源", "后续标准"]
    header(ws, 3, labels, TEAL)
    issues = [
        ["01", "范围", "SKU总数", "原17个SKU加哈尔滨专业SKU，当前共18个", "已确认", "Jack 2026-09-02新增专业版", "公开编号为SPU01/02/03内分组SKU"],
        ["02", "版本", "智慧食安", "SPU02-SKU01至SKU03按本表纳入最终范围", "已确认", "Jack 2026-09-02：就这些", "对外名称与编码保持本表一致"],
        ["03", "证据", "SPU01-SKU11", "集中保障供餐缺合同级软件与验收证据", "待补证", "项目线索与内部清单", "完成软件版本、真机、离线一致性和验收证据"],
        ["04", "证据", "SPU01-SKU06", "免绑盘全链路与高峰能力待真机验证", "待补证", "项目与硬件梳理", "指定型号全流程、异常与压力测试通过"],
        ["05", "商务", "全部SKU", "价格、设备数量、接口、等保和高可用仍按项目冻结", "持续门禁", "产品材料证据规则", "配置报价表与合同范围逐项确认"],
    ]
    for r, row in enumerate(issues, 4):
        for c, value in enumerate(row, 1): ws.cell(r, c, value)
    style_grid(ws, 4, 3 + len(issues), 1, 7)
    widths = [8, 12, 18, 42, 34, 22, 44]
    for i, width in enumerate(widths, 1): ws.column_dimensions[get_column_letter(i)].width = width
    for r in range(4, 4 + len(issues)): ws.row_dimensions[r].height = 56

    ws = wb.create_sheet("07-修改记录")
    title(ws, "SKU修改记录", 8)
    header(ws, 3, ["版本", "日期", "修改人", "Sheet", "SKU编码", "修改内容", "评审结论", "后续动作"])
    ws.cell(4, 1, "v2.3-final")
    ws.cell(4, 2, "2026-09-02")
    ws.cell(4, 3, "Jack确认，Codex整理")
    ws.cell(4, 4, "全表")
    ws.cell(4, 5, "18个全部SKU")
    ws.cell(4, 6, "公开编码统一为SPUxx-SKUxx；新增SPU01-SKU12营养健康智厨专业版")
    ws.cell(4, 7, "已确认定版")
    ws.cell(4, 8, "专业版接口贯通和项目验收继续补证")
    style_grid(ws, 4, 4, 1, 8)
    for i, width in enumerate([14, 14, 24, 18, 20, 56, 18, 42], 1): ws.column_dimensions[get_column_letter(i)].width = width

    ws = wb.create_sheet("99-字典")
    ws.sheet_state = "hidden"
    ws.append(["SPU", "证据等级", "评审状态"])
    for row in [["营养结算", "A", "待确认"], ["智慧食安", "B", "评审中"], ["智慧进销存", "C", "已冻结"], ["", "D", "暂停"]]: ws.append(row)

    for ws in wb.worksheets:
        ws.sheet_properties.pageSetUpPr.fitToPage = True
        ws.page_setup.fitToWidth = 1
        ws.page_setup.fitToHeight = 0
        ws.sheet_properties.outlinePr.summaryBelow = True
    wb.active = wb.sheetnames.index("01-横向对比")
    args.output.parent.mkdir(parents=True, exist_ok=True)
    wb.save(args.output)
    print(args.output)


if __name__ == "__main__":
    main()