# -*- coding: utf-8 -*-
"""
L6 作业规范验证案例对照表 Excel 生成脚本
用途：生成 CPT-V Excel 文件，含使用说明、案例基本信息模板、6 节点对照表（示例+空白模板）、汇总评价、阶段性汇总分析
运营团队可直接复制空白模板填写真实案例。
"""

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

# ============ 品牌色 ============
COLOR_PRIMARY = "004046"      # 深墨绿（主色，表头）
COLOR_SECONDARY = "D4AF78"    # 浅沙金（辅色，异常/强制）
COLOR_BG = "F2F0EB"           # 米白浅灰（背景，示例区）
COLOR_WHITE = "FFFFFF"
COLOR_LIGHT_GOLD = "F5E6D3"   # 浅金（示例表头）
COLOR_TEMPLATE = "FFFFFF"     # 模板区
COLOR_NOTE = "FFF8E7"         # 说明区

# ============ 样式 ============
def font(size=10, bold=False, color="000000"):
    return Font(name="微软雅黑", size=size, bold=bold, color=color)

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

thin = Side(border_style="thin", color="999999")
medium = Side(border_style="medium", color=COLOR_PRIMARY)
border_all = Border(left=thin, right=thin, top=thin, bottom=thin)
border_header = Border(left=medium, right=medium, top=medium, bottom=medium)

align_wrap = Alignment(horizontal="left", vertical="top", wrap_text=True)
align_center = Alignment(horizontal="center", vertical="center", wrap_text=True)

# ============ 数据：6 节点规范定义 ============
NODES = [
    {
        "id": "①", "name": "师傅出发准备",
        "items": [
            ("订单核对", "核对品类/型号/地址/时段"),
            ("工具检查", "按品类工具清单检查工具包"),
            ("着装规范", "穿着工服佩戴工牌"),
            ("路线确认", "确认导航路线与预计到达时间"),
        ],
        "collect": "出发打卡（GPS+时间戳）、工具清单照片",
        "exception": "工具缺失→返回补齐或协调换师傅",
    },
    {
        "id": "②", "name": "联系客户确认到场",
        "items": [
            ("提前联系", "出发前 30min 联系客户"),
            ("信息告知", "告知预计到达时间与师傅信息"),
            ("位置确认", "确认楼宇入口/电梯/停车位置"),
            ("区域提醒", "提醒客户清空安装区域"),
        ],
        "collect": "联系记录（WhatsApp 截图/电话录音）",
        "exception": "客户未应答→3 次联系后升级 OL；客户改期→PAUSE 重新安排",
    },
    {
        "id": "③", "name": "入户规范",
        "items": [
            ("**穿鞋套**", "**穿鞋套（强制）**"),
            ("地面保护", "铺设地面保护垫"),
            ("区域确认", "确认安装区域已清空"),
            ("产品核对", "核对产品外包装完好"),
            ("入户拍照", "拍照记录入户前现场状态"),
        ],
        "collect": "入户前现场照片、穿鞋套照片、地面保护照片、产品外包装照片",
        "exception": "客户拒穿鞋套→沟通引导；现场未清空→协助清空或等待",
    },
    {
        "id": "④", "name": "安装作业",
        "items": [
            ("SOP 执行", "按品类 SOP 执行安装"),
            ("**成品保护**", "已安装家具/地面遮盖"),
            ("**拆旧清理**", "拆旧物品集中放置"),
            ("**噪音管控**", "避免午休/夜间施工"),
            ("问题记录", "遇到问题即时拍照记录"),
        ],
        "collect": "安装步骤视频（分段）、施工过程照片、遇到问题照片、实际工时记录",
        "exception": "现场增项→增项报价确认；超能力→PAUSE 换师傅",
    },
    {
        "id": "⑤", "name": "完工清场",
        "items": [
            ("**垃圾带走**", "包装材料/拆旧物品带走"),
            ("**现场复位**", "家具归位/地面清洁"),
            ("工具清点", "工具清点无误"),
            ("完工拍照", "拍照记录完工后现场状态"),
        ],
        "collect": "完工照片（多角度）、清场后全景照片、垃圾带离照片",
        "exception": "垃圾遗漏→返回清理",
    },
    {
        "id": "⑥", "name": "交付说明",
        "items": [
            ("使用讲解", "讲解使用注意事项与维护方法"),
            ("验收引导", "引导客户逐项验收"),
            ("结果确认", "确认验收结果（通过/整改）"),
            ("签字确认", "请客户签字确认"),
            ("售后提醒", "提醒售后回访与复购入口"),
        ],
        "collect": "客户签字照片、验收视频、交付说明记录",
        "exception": "验收不通过→BACK 整改；客户投诉→ESC 客诉处理",
    },
]

# ============ 示例案例（模拟真实·宜家 PAX 衣柜组装，含墙体不平异常） ============
EXAMPLE_BASIC = [
    ("案例编号", "CV-202608-01"),
    ("服务日期", "2026-08-05"),
    ("品类", "家具"),
    ("细分类目", "宜家 PAX 衣柜组装（PAX 2米高×3米宽，白色，型号 102.518.57）"),
    ("楼宇类型", "屋苑（ESTT）"),
    ("区域", "九龙·黄大仙区（脱敏：仅保留区域与楼宇类型）"),
    ("师傅等级", "AUTH（认证师傅）"),
    ("师傅姓名", "陈师傅（化名）·3 年家具安装经验"),
    ("客户称呼", "张先生（化名）"),
    ("预估工时", "4.0 小时"),
    ("实际工时", "4.5 小时（+0.5h，因墙体不平处理）"),
    ("案例提供人", "成文"),
    ("案例类型", "典型场景案例（含异常）"),
    ("客户满意度", "4.8 / 5.0"),
]

# 每节点的示例行：(规范项, 真实操作, 是否符合, 差异说明/优化建议)
EXAMPLE_NODES = {
    "①": [
        ("订单核对", "13:00 在师傅 App 核对订单：PAX 衣柜型号 102.518.57、上门时段 14:00-18:00、地址黄大仙区某屋苑", "是", "—"),
        ("工具检查", "按家具类工具清单检查：电钻、水平仪、螺丝刀套装、内六角、卷尺、铅笔、垫片备件包（多厚度）", "是", "—"),
        ("着装规范", "穿着织布鸟工服，佩戴工牌", "是", "—"),
        ("路线确认", "App 导航确认走龙翔道，预计 13:50 到达", "是", "—"),
        ("采集物", "App 自动记录出发打卡（GPS+13:00 时间戳）；拍摄工具清单照片", "已采集", "—"),
    ],
    "②": [
        ("提前联系", "13:30 通过 WhatsApp 联系客户确认到场", "是", "—"),
        ("信息告知", "告知 14:00 到达，发送师傅电子名片（含资质证书与历史评分 4.9）", "是", "—"),
        ("位置确认", "客户告知入口在 B 座，电梯可容纳 2 米长货物（L2 已提前确认）", "是", "—"),
        ("区域提醒", "提醒客户清空卧室安装区域，移除床品与易碎品", "是", "—"),
        ("采集物", "WhatsApp 沟通截图保存至跟踪表", "已采集", "—"),
        ("异常", "无异常", "无异常", "—"),
    ],
    "③": [
        ("穿鞋套", "师傅主动穿鞋套入户，客户备注体验良好", "是", "—"),
        ("地面保护", "在卧室地面铺设保护垫，覆盖安装区域与走动路径", "是", "—"),
        ("区域确认", "客户已清空区域，床品已移除", "是", "—"),
        ("产品核对", "核对宜家包装 5 件，外包装完好，条码扫描无误", "是", "—"),
        ("入户拍照", "拍摄入户前卧室全景（含安装位置墙面与地面状态）", "是", "—"),
        ("采集物", "入户前现场照片、穿鞋套照片、地面保护照片、产品外包装照片全部采集", "已采集", "—"),
        ("异常", "无异常", "无异常", "—"),
    ],
    "④": [
        ("SOP 执行",
         "按 PAX 衣柜组装 SOP 执行：①14:10-14:50 组装框架；②14:50-15:10 竖立固定；③15:10 发现异常（见下）；④15:30-16:30 垫片调整后继续组装；⑤16:30-17:00 安装门板与配件",
         "是", "—"),
        ("成品保护", "对已安装框架用防尘布遮盖，地面持续保护", "是", "—"),
        ("拆旧清理", "无拆旧物品（新装）", "是", "—"),
        ("噪音管控", "14:00-18:00 施工，避开午休与夜间，无噪音问题", "是", "—"),
        ("问题记录",
         "【异常发现·墙体不平】15:10 安装到第 3 节柜体时，发现柜体与墙体顶部出现缝隙，实测顶部缝隙 8mm（带卷尺拍照）。\n"
         "【处理链路】①拍照记录墙体缝隙（带卷尺刻度）；②暂停施工，向客户说明情况并解释需加装垫片调整；"
         "③现场发起增项报价 HKD 50（垫片材料 HKD 20 + 额外工时 0.5h × HKD 60）；④客户口头确认后 App 电子签字；"
         "⑤使用多厚度垫片逐步调整，重新固定柜体，复测垂直度达标；⑥拍摄增项前后对比照片。",
         "是",
         "【优化建议】\n1. CPT-D 增项报价流程对小型增项（≤HKD 100）偏重，建议增加「小型增项快速确认」通道，减少师傅现场等待与客户决策摩擦。\n"
         "2. L2 需求确认阶段建议增加「墙体平整度」采集项（客户用卷尺拍照门框/墙角），提前预警。\n"
         "3. 家具类 SOP 应补充「墙体不平处理垫片」标准操作分支。"),
        ("采集物",
         "3 段安装步骤视频（框架组装/竖立固定/门板安装）+ 施工过程照片 12 张 + 墙体缝隙照片（带卷尺）+ 增项前后对比照片 + 实际工时 4.5h 自动记录",
         "已采集", "—"),
        ("异常", "有异常：墙体不平·小型增项 HKD 50（垫片+额外工时 0.5h），客户 App 电子签字确认", "有异常",
         "增项费用：HKD 50｜增项内容：垫片调整+额外工时｜确认方式：App 电子签字｜处理结果：柜体垂直度达标"),
    ],
    "⑤": [
        ("垃圾带走", "师傅带走全部纸箱、泡沫与包装材料，分类投放至屋苑回收点", "是", "—"),
        ("现场复位", "扫地拖地，家具归位，恢复卧室原布局", "是", "—"),
        ("工具清点", "清点无误，垫片备件包剩余量记录", "是", "—"),
        ("完工拍照", "拍摄完工衣柜多角度照片（正面/侧面/门细节/垫片处理部位）", "是", "—"),
        ("采集物", "完工照片、清场后全景照片、垃圾带离照片全部采集", "已采集", "—"),
        ("异常", "无异常", "无异常", "—"),
    ],
    "⑥": [
        ("使用讲解", "讲解 PAX 衣柜门铰调节方法、承重注意事项（每层≤15kg）、垫片维护说明", "是", "—"),
        ("验收引导", "引导客户逐项验收：门开关顺滑度、抽屉滑轨、柜体垂直度、垫片部位稳固性", "是", "—"),
        ("结果确认", "客户确认通过，无整改项", "是", "—"),
        ("签字确认", "客户在 App 电子签字（含增项确认与验收确认）", "是", "—"),
        ("售后提醒", "提醒 7 天后回访、复购优惠码 WEAVELY50、推荐入口", "是", "—"),
        ("采集物", "客户签字照片、验收视频（逐项检查）、交付说明记录", "已采集", "—"),
        ("异常", "无异常", "无异常", "—"),
    ],
}

EXAMPLE_SUMMARY = [
    ("规范符合度", "6 节点共 30 项规范，符合 30 项，符合率 100%"),
    ("采集物完整度", "应采集 6 组，实际采集 6 组，完整率 100%"),
    ("异常情况", "发生 1 项异常（墙体不平·小型增项 HKD 50），处理方式：现场报价→客户 App 电子签字确认→垫片调整→复测达标"),
    ("客户满意度", "满意（评分：4.8 / 5.0）"),
    ("关键差异点",
     "1. 增项报价流程对小型增项偏重，师傅现场等待客户决策约 8min\n"
     "2. 师傅主动穿鞋套符合规范，客户反馈体验良好\n"
     "3. 墙体不平属于屋苑常见情况（顶部缝隙 8mm），L2 阶段未提前预警"),
    ("流程优化建议",
     "1. CPT-D 增项报价增加「小型增项（≤HKD 100）快速确认」通道\n"
     "2. L2 现场条件采集增加「墙体平整度」字段（客户卷尺拍照门框/墙角）\n"
     "3. 家具类 SOP 补充「墙体不平处理垫片」标准操作分支"),
    ("SOP 改进建议", "PAX 衣柜组装 SOP 增加「墙体不平」分支处理步骤：缝隙≤10mm 用垫片；>10mm 建议客户联系墙面修整"),
    ("工具清单调整", "家具类工具清单增加「垫片备件包（多厚度 1/2/3/5mm）」作为标配"),
]

# ============ 表头列定义 ============
COLUMNS = [
    ("规范项", 16),
    ("规范要求", 30),
    ("真实操作", 52),
    ("是否符合", 12),
    ("差异说明 / 优化建议", 38),
]


def apply_header_row(ws, row, start_col=1, color=COLOR_PRIMARY, font_color=COLOR_WHITE):
    """应用表头样式"""
    for c in range(start_col, start_col + len(COLUMNS)):
        cell = ws.cell(row=row, column=c)
        cell.font = font(10, bold=True, color=font_color)
        cell.fill = fill(color)
        cell.alignment = align_center
        cell.border = border_header


def apply_data_row(ws, row, start_col=1, bg=None, bold_first=False):
    """应用数据行样式"""
    for c in range(start_col, start_col + len(COLUMNS)):
        cell = ws.cell(row=row, column=c)
        cell.font = font(10, bold=(bold_first and c == start_col))
        if bg:
            cell.fill = fill(bg)
        cell.alignment = align_wrap
        cell.border = border_all


def set_column_widths(ws, start_col=1):
    for i, (_, w) in enumerate(COLUMNS):
        ws.column_dimensions[get_column_letter(start_col + i)].width = w


def add_section_title(ws, row, text, color=COLOR_SECONDARY):
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=len(COLUMNS))
    cell = ws.cell(row=row, column=1, value=text)
    cell.font = font(11, bold=True, color=COLOR_PRIMARY)
    cell.fill = fill(color)
    cell.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    ws.row_dimensions[row].height = 22


def write_node_sheet(ws, node, example_rows):
    """生成单个节点 sheet：示例 + 空白模板"""
    set_column_widths(ws)
    # 标题
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(COLUMNS))
    t = ws.cell(row=1, column=1, value=f"节点 {node['id']} {node['name']}")
    t.font = font(13, bold=True, color=COLOR_WHITE)
    t.fill = fill(COLOR_PRIMARY)
    t.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    ws.row_dimensions[1].height = 28

    # 节点说明
    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(COLUMNS))
    info = ws.cell(row=2, column=1,
                   value=f"采集物要求：{node['collect']}    ｜    异常处理：{node['exception']}")
    info.font = font(9, color="555555")
    info.fill = fill(COLOR_NOTE)
    info.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True, indent=1)
    ws.row_dimensions[2].height = 30

    row = 3
    # 示例区标题
    add_section_title(ws, row, "【示例案例·参考】CV-202608-01 宜家 PAX 衣柜组装（模拟·仅供格式参考）")
    row += 1
    # 示例表头
    for i, (name, _) in enumerate(COLUMNS):
        ws.cell(row=row, column=i + 1, value=name)
    apply_header_row(ws, row, color=COLOR_SECONDARY, font_color=COLOR_PRIMARY)
    ws.row_dimensions[row].height = 20
    row += 1
    # 示例数据
    for item in example_rows:
        name, op, fit, diff = item
        ws.cell(row=row, column=1, value=name)
        # 规范要求列：从 NODES 找
        req = ""
        for n, r in node["items"]:
            if n.replace("**", "") == name.replace("**", ""):
                req = r
                break
        if name == "采集物":
            req = node["collect"]
        elif name == "异常":
            req = node["exception"]
        ws.cell(row=row, column=2, value=req)
        ws.cell(row=row, column=3, value=op)
        ws.cell(row=row, column=4, value=fit)
        ws.cell(row=row, column=5, value=diff)
        is_bold = name.startswith("**") or name in ("穿鞋套", "垃圾带走", "现场复位", "成品保护", "拆旧清理", "噪音管控")
        apply_data_row(ws, row, bg=COLOR_BG, bold_first=True)
        # 异常行高亮
        if name == "异常" and fit == "有异常":
            for c in range(1, 6):
                ws.cell(row=row, column=c).fill = fill(COLOR_LIGHT_GOLD)
        # 自动行高（根据内容长度估算）
        max_len = max(len(str(op)), len(str(diff)))
        ws.row_dimensions[row].height = max(18, min(120, 15 + max_len // 4 * 2))
        row += 1

    row += 1
    # 空白模板区标题
    add_section_title(ws, row, "【空白模板·复制填写】请复制下方行使用，下拉框已预设")
    row += 1
    # 模板表头
    for i, (name, _) in enumerate(COLUMNS):
        ws.cell(row=row, column=i + 1, value=name)
    apply_header_row(ws, row)
    ws.row_dimensions[row].height = 20
    header_row = row
    row += 1
    # 模板空白行（含规范项与规范要求预填，其他空白）
    template_start = row
    # 规范项行
    for name, req in node["items"]:
        ws.cell(row=row, column=1, value=name.replace("**", ""))
        ws.cell(row=row, column=2, value=req.replace("**", ""))
        ws.cell(row=row, column=3, value="")
        ws.cell(row=row, column=4, value="")
        ws.cell(row=row, column=5, value="")
        apply_data_row(ws, row, bold_first=True)
        ws.row_dimensions[row].height = 28
        row += 1
    # 采集物行
    ws.cell(row=row, column=1, value="采集物")
    ws.cell(row=row, column=2, value=node["collect"])
    apply_data_row(ws, row, bold_first=True)
    ws.row_dimensions[row].height = 28
    row += 1
    # 异常行
    ws.cell(row=row, column=1, value="异常")
    ws.cell(row=row, column=2, value=node["exception"])
    apply_data_row(ws, row, bold_first=True)
    ws.row_dimensions[row].height = 28
    row += 1
    template_end = row - 1

    # 下拉验证：是否符合列（第4列）
    dv_fit = DataValidation(type="list", formula1='"是,否"', allow_blank=True)
    dv_fit.add(f"D{template_start}:D{template_end}")
    ws.add_data_validation(dv_fit)
    # 采集物行单独设置下拉
    dv_col = DataValidation(type="list", formula1='"已采集,未采集"', allow_blank=True)
    # 采集物行号
    collect_row = template_start + len(node["items"])
    dv_col.add(f"D{collect_row}:D{collect_row}")
    ws.add_data_validation(dv_col)
    # 异常行下拉
    dv_exc = DataValidation(type="list", formula1='"无异常,有异常"', allow_blank=True)
    exc_row = collect_row + 1
    dv_exc.add(f"D{exc_row}:D{exc_row}")
    ws.add_data_validation(dv_exc)

    # 冻结窗格（冻结表头）
    ws.freeze_panes = f"A{header_row + 1}"


# ============ Sheet: 使用说明 ============
def build_intro_sheet(ws):
    set_column_widths(ws)
    ws.column_dimensions["A"].width = 20
    ws.column_dimensions["B"].width = 80
    ws.merge_cells("A1:B1")
    t = ws.cell(row=1, column=1, value="L6 作业规范验证案例对照表（CPT-V）")
    t.font = font(16, bold=True, color=COLOR_WHITE)
    t.fill = fill(COLOR_PRIMARY)
    t.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    ws.row_dimensions[1].height = 36

    rows = [
        ("文档编号", "DOC-P41-V / CPT-V"),
        ("版本", "V1.0"),
        ("创建日期", "2026-08-05"),
        ("维护人", "OL（成文）/ DT"),
        ("关联文档", "CPT-D §2.1.1 L6 上门安装作业规范子流程；CPT-F 现场工作登记表"),
        ("", ""),
        ("一、目的", "验证 CPT-D §2.1.1 L6 上门安装作业规范子流程的可操作性与完备性，通过真实案例与规范逐项对照，发现差异与遗漏，为下一轮流程和功能设计优化提供依据。"),
        ("二、背景", "基于 2026-08-05 核心运营团队内部讨论决议事项 2：整理真实案例，对照 CPT-D 客户项目生命周期流程图，用于下一轮流程和功能设计优化。"),
        ("三、填写要求", "填写人：OL（成文）牵头，师傅/运营协助提供原始信息\n案例来源：平台已发生的真实服务订单（脱敏后填写）\n填写周期：建议每月汇总 3-5 个典型案例"),
        ("四、脱敏规则", "客户姓名→化名（如「张先生」）；地址→仅保留区域与楼宇类型；电话/身份证等隐私字段不记录。"),
        ("五、PDPO 合规", "涉及客户隐私信息须脱敏处理，遵守香港 PDPO 隐私法规。多媒体采集物中敏感信息（人脸/门牌/个人物品）须脱敏。"),
        ("六、Sheet 索引", "1. 使用说明（本页）\n2. 案例基本信息（模板+示例）\n3-8. 节点①~⑥ 对照表（每页上方示例、下方空白模板，下拉框已预设）\n9. 案例汇总评价（模板+示例）\n10. 阶段性汇总分析框架"),
        ("七、使用方法", "①查看「案例基本信息」Sheet 的示例区了解填写格式；②在每个节点 Sheet 的「空白模板」区域复制行并填写；③「是否符合」列已预设下拉框（是/否、已采集/未采集、无异常/有异常）；④填写完成后在「案例汇总评价」Sheet 汇总；⑤每积累 5-10 个案例后在「阶段性汇总分析」Sheet 统计。"),
        ("八、示例案例说明", "本文件示例案例 CV-202608-01 为模拟真实场景（宜家 PAX 衣柜组装，含墙体不平异常），仅供格式参考，不代表真实业务。真实案例由 OL（成文）团队填写。"),
    ]
    r = 3
    for label, content in rows:
        if label == "" and content == "":
            r += 1
            continue
        ws.cell(row=r, column=1, value=label).font = font(10, bold=True, color=COLOR_PRIMARY)
        ws.cell(row=r, column=1).fill = fill(COLOR_NOTE)
        ws.cell(row=r, column=1).alignment = Alignment(horizontal="left", vertical="top", wrap_text=True, indent=1)
        ws.cell(row=r, column=1).border = border_all
        c = ws.cell(row=r, column=2, value=content)
        c.font = font(10)
        c.alignment = align_wrap
        c.border = border_all
        # 行高
        lines = content.count("\n") + 1
        ws.row_dimensions[r].height = max(20, 15 * lines + 5)
        r += 1
    ws.freeze_panes = "A3"


# ============ Sheet: 案例基本信息 ============
def build_basic_sheet(ws):
    ws.column_dimensions["A"].width = 20
    ws.column_dimensions["B"].width = 50
    ws.column_dimensions["C"].width = 4
    ws.column_dimensions["D"].width = 20
    ws.column_dimensions["E"].width = 50

    ws.merge_cells("A1:E1")
    t = ws.cell(row=1, column=1, value="案例基本信息")
    t.font = font(14, bold=True, color=COLOR_WHITE)
    t.fill = fill(COLOR_PRIMARY)
    t.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    ws.row_dimensions[1].height = 30

    # 双列：左示例，右模板
    ws.cell(row=2, column=1, value="【示例案例·参考】CV-202608-01").font = font(11, bold=True, color=COLOR_PRIMARY)
    ws.cell(row=2, column=1).fill = fill(COLOR_SECONDARY)
    ws.cell(row=2, column=4, value="【空白模板·复制填写】").font = font(11, bold=True, color=COLOR_PRIMARY)
    ws.cell(row=2, column=4).fill = fill(COLOR_SECONDARY)
    ws.row_dimensions[2].height = 22

    # 表头
    for col, (label, _) in enumerate([("字段", ""), ("内容", "")], start=1):
        if col <= 2:
            ws.cell(row=3, column=col, value=label)
    for col, (label, _) in enumerate([("字段", ""), ("内容", "")], start=4):
        ws.cell(row=3, column=col, value=label)
    for c in [1, 2, 4, 5]:
        cell = ws.cell(row=3, column=c)
        cell.font = font(10, bold=True, color=COLOR_WHITE)
        cell.fill = fill(COLOR_PRIMARY)
        cell.alignment = align_center
        cell.border = border_header
    ws.row_dimensions[3].height = 20

    # 模板字段（与示例一致）
    template_fields = [f[0] for f in EXAMPLE_BASIC]

    r = 4
    for i, (label, value) in enumerate(EXAMPLE_BASIC):
        # 示例列
        ws.cell(row=r, column=1, value=label).font = font(10, bold=True)
        ws.cell(row=r, column=1).fill = fill(COLOR_BG)
        ws.cell(row=r, column=1).alignment = align_wrap
        ws.cell(row=r, column=1).border = border_all
        ws.cell(row=r, column=2, value=value).font = font(10)
        ws.cell(row=r, column=2).fill = fill(COLOR_BG)
        ws.cell(row=r, column=2).alignment = align_wrap
        ws.cell(row=r, column=2).border = border_all
        # 模板列
        ws.cell(row=r, column=4, value=label).font = font(10, bold=True)
        ws.cell(row=r, column=4).alignment = align_wrap
        ws.cell(row=r, column=4).border = border_all
        ws.cell(row=r, column=5, value="").font = font(10)
        ws.cell(row=r, column=5).alignment = align_wrap
        ws.cell(row=r, column=5).border = border_all
        ws.row_dimensions[r].height = 24
        r += 1

    # 案例类型下拉
    type_row = 4 + [f[0] for f in EXAMPLE_BASIC].index("案例类型")
    dv = DataValidation(type="list", formula1='"顺利案例,异常案例,典型场景案例"', allow_blank=True)
    dv.add(f"E{type_row}")
    ws.add_data_validation(dv)

    ws.freeze_panes = "A4"


# ============ Sheet: 案例汇总评价 ============
def build_summary_sheet(ws):
    ws.column_dimensions["A"].width = 22
    ws.column_dimensions["B"].width = 55
    ws.column_dimensions["C"].width = 4
    ws.column_dimensions["D"].width = 22
    ws.column_dimensions["E"].width = 55

    ws.merge_cells("A1:E1")
    t = ws.cell(row=1, column=1, value="案例汇总评价")
    t.font = font(14, bold=True, color=COLOR_WHITE)
    t.fill = fill(COLOR_PRIMARY)
    t.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    ws.row_dimensions[1].height = 30

    ws.cell(row=2, column=1, value="【示例案例·参考】CV-202608-01").font = font(11, bold=True, color=COLOR_PRIMARY)
    ws.cell(row=2, column=1).fill = fill(COLOR_SECONDARY)
    ws.cell(row=2, column=4, value="【空白模板·复制填写】").font = font(11, bold=True, color=COLOR_PRIMARY)
    ws.cell(row=2, column=4).fill = fill(COLOR_SECONDARY)
    ws.row_dimensions[2].height = 22

    for c, label in [(1, "评价维度"), (2, "评价"), (4, "评价维度"), (5, "评价")]:
        cell = ws.cell(row=3, column=c, value=label)
        cell.font = font(10, bold=True, color=COLOR_WHITE)
        cell.fill = fill(COLOR_PRIMARY)
        cell.alignment = align_center
        cell.border = border_header
    ws.row_dimensions[3].height = 20

    r = 4
    for label, value in EXAMPLE_SUMMARY:
        ws.cell(row=r, column=1, value=label).font = font(10, bold=True)
        ws.cell(row=r, column=1).fill = fill(COLOR_BG)
        ws.cell(row=r, column=1).alignment = align_wrap
        ws.cell(row=r, column=1).border = border_all
        ws.cell(row=r, column=2, value=value).font = font(10)
        ws.cell(row=r, column=2).fill = fill(COLOR_BG)
        ws.cell(row=r, column=2).alignment = align_wrap
        ws.cell(row=r, column=2).border = border_all
        ws.cell(row=r, column=4, value=label).font = font(10, bold=True)
        ws.cell(row=r, column=4).alignment = align_wrap
        ws.cell(row=r, column=4).border = border_all
        ws.cell(row=r, column=5, value="").font = font(10)
        ws.cell(row=r, column=5).alignment = align_wrap
        ws.cell(row=r, column=5).border = border_all
        lines = value.count("\n") + 1
        ws.row_dimensions[r].height = max(24, 15 * lines + 8)
        r += 1

    # 满意度下拉
    sat_row = 4 + [s[0] for s in EXAMPLE_SUMMARY].index("客户满意度")
    dv = DataValidation(type="list", formula1='"满意,一般,不满意"', allow_blank=True)
    dv.add(f"E{sat_row}")
    ws.add_data_validation(dv)

    ws.freeze_panes = "A4"


# ============ Sheet: 阶段性汇总分析 ============
def build_stage_sheet(ws):
    ws.column_dimensions["A"].width = 18
    for col in "BCDEFG":
        ws.column_dimensions[col].width = 16

    ws.merge_cells("A1:G1")
    t = ws.cell(row=1, column=1, value="阶段性汇总分析（每 5-10 个案例后填写）")
    t.font = font(14, bold=True, color=COLOR_WHITE)
    t.fill = fill(COLOR_PRIMARY)
    t.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    ws.row_dimensions[1].height = 30

    # 5.1 案例分布
    r = 3
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
    cell = ws.cell(row=r, column=1, value="5.1 案例分布")
    cell.font = font(11, bold=True, color=COLOR_PRIMARY)
    cell.fill = fill(COLOR_SECONDARY)
    cell.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    r += 1
    dist = [
        ("品类覆盖", "家具 ___ 例 / 灯具 ___ 例 / 卫浴 ___ 例 / 厨房 ___ 例 / 家电 ___ 例 / 智能 ___ 例 / 门窗 ___ 例 / 软装 ___ 例"),
        ("楼宇覆盖", "唐楼 ___ 例 / 洋楼 ___ 例 / 屋苑 ___ 例 / 公屋 ___ 例 / 居屋 ___ 例 / 村屋 ___ 例 / 豪宅 ___ 例"),
        ("案例类型", "顺利 ___ 例 / 异常 ___ 例 / 典型 ___ 例"),
        ("师傅等级", "GOLD ___ 例 / AUTH ___ 例 / NORM ___ 例"),
    ]
    for label, val in dist:
        ws.cell(row=r, column=1, value=label).font = font(10, bold=True)
        ws.cell(row=r, column=1).fill = fill(COLOR_NOTE)
        ws.cell(row=r, column=1).border = border_all
        ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=7)
        ws.cell(row=r, column=2, value=val).font = font(10)
        ws.cell(row=r, column=2).alignment = align_wrap
        ws.cell(row=r, column=2).border = border_all
        ws.row_dimensions[r].height = 24
        r += 1

    # 5.2 规范符合度统计
    r += 1
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
    cell = ws.cell(row=r, column=1, value="5.2 规范符合度统计")
    cell.font = font(11, bold=True, color=COLOR_PRIMARY)
    cell.fill = fill(COLOR_SECONDARY)
    cell.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    r += 1
    headers = ["节点", "规范项数", "平均符合率", "主要差异点"]
    for i, h in enumerate(headers):
        ws.cell(row=r, column=i + 1, value=h)
        ws.cell(row=r, column=i + 1).font = font(10, bold=True, color=COLOR_WHITE)
        ws.cell(row=r, column=i + 1).fill = fill(COLOR_PRIMARY)
        ws.cell(row=r, column=i + 1).alignment = align_center
        ws.cell(row=r, column=i + 1).border = border_header
    ws.merge_cells(start_row=r, start_column=4, end_row=r, end_column=7)
    r += 1
    node_stats = [
        ("① 师傅出发准备", 5, "___%"),
        ("② 联系客户确认到场", 6, "___%"),
        ("③ 入户规范", 7, "___%"),
        ("④ 安装作业", 7, "___%"),
        ("⑤ 完工清场", 6, "___%"),
        ("⑥ 交付说明", 6, "___%"),
        ("合计", 37, "___%"),
    ]
    for name, cnt, rate in node_stats:
        ws.cell(row=r, column=1, value=name).font = font(10, bold=(name == "合计"))
        ws.cell(row=r, column=2, value=cnt).font = font(10)
        ws.cell(row=r, column=3, value=rate).font = font(10)
        ws.merge_cells(start_row=r, start_column=4, end_row=r, end_column=7)
        ws.cell(row=r, column=4, value="").font = font(10)
        for c in range(1, 8):
            ws.cell(row=r, column=c).border = border_all
            ws.cell(row=r, column=c).alignment = align_wrap
        ws.row_dimensions[r].height = 22
        r += 1

    # 5.5 流程优化建议汇总
    r += 1
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=7)
    cell = ws.cell(row=r, column=1, value="5.5 流程优化建议汇总")
    cell.font = font(11, bold=True, color=COLOR_PRIMARY)
    cell.fill = fill(COLOR_SECONDARY)
    cell.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    r += 1
    headers = ["序号", "优化建议", "涉及文档", "负责人", "状态"]
    for i, h in enumerate(headers):
        ws.cell(row=r, column=i + 1, value=h)
        ws.cell(row=r, column=i + 1).font = font(10, bold=True, color=COLOR_WHITE)
        ws.cell(row=r, column=i + 1).fill = fill(COLOR_PRIMARY)
        ws.cell(row=r, column=i + 1).alignment = align_center
        ws.cell(row=r, column=i + 1).border = border_header
    ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=4)
    r += 1
    for i in range(1, 6):
        ws.cell(row=r, column=1, value=i).font = font(10)
        ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=4)
        ws.cell(row=r, column=5, value="CPT-D / CPT-F / SCL").font = font(10)
        ws.cell(row=r, column=6, value="").font = font(10)
        ws.cell(row=r, column=7, value="□待处理 □已采纳 □已实施").font = font(10)
        for c in range(1, 8):
            ws.cell(row=r, column=c).border = border_all
            ws.cell(row=r, column=c).alignment = align_wrap
        ws.row_dimensions[r].height = 24
        r += 1

    ws.freeze_panes = "A3"


# ============ 主函数 ============
def main():
    wb = Workbook()

    # Sheet 1: 使用说明
    ws_intro = wb.active
    ws_intro.title = "使用说明"
    build_intro_sheet(ws_intro)

    # Sheet 2: 案例基本信息
    ws_basic = wb.create_sheet("案例基本信息")
    build_basic_sheet(ws_basic)

    # Sheet 3-8: 节点①~⑥
    for node in NODES:
        title = f"节点{node['id']}{node['name']}"
        # sheet 名长度限制 31
        ws = wb.create_sheet(title[:31])
        write_node_sheet(ws, node, EXAMPLE_NODES[node["id"]])

    # Sheet 9: 案例汇总评价
    ws_sum = wb.create_sheet("案例汇总评价")
    build_summary_sheet(ws_sum)

    # Sheet 10: 阶段性汇总分析
    ws_stage = wb.create_sheet("阶段性汇总分析")
    build_stage_sheet(ws_stage)

    out = r"d:\AC\TF\hk2026\docs\客户项目跟踪服务\L6作业规范验证案例对照表.xlsx"
    wb.save(out)
    print(f"OK: {out}")
    print(f"Sheets: {wb.sheetnames}")


if __name__ == "__main__":
    main()