from __future__ import annotations

import csv
import shutil
from copy import copy
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.image import Image as XLImage
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo


ROOT = Path("/Users/jack/code/010-cpt/008-zhct/zhctprompt")
TEMPLATE = Path("/Users/jack/同步空间/cpt/05_经营管理与会议/005_日常管理/周例会（事业部层面）/2026年/周报模板.xlsx")
SOURCE_WEEKLY = Path("/Users/jack/Desktop/周报0605.xlsx")
PASTED_TEXT = Path("/Users/jack/.codex/attachments/80c3dc35-b38a-4c42-a99f-57c8af26b9b5/pasted-text.txt")
OUT_DIR = ROOT / "work/weekly-reports/2026-W23/rich-excel-weekly-report/outputs"
OUTPUT = OUT_DIR / "数字技术中心产品开发部周报_丰富版_20260601-20260607.xlsx"
SOURCE_INDEX = OUT_DIR / "source-index.csv"

COLORS = {
    "navy": "17324D",
    "blue": "2563EB",
    "light_blue": "DBEAFE",
    "green": "16A34A",
    "light_green": "DCFCE7",
    "amber": "F59E0B",
    "light_amber": "FEF3C7",
    "red": "DC2626",
    "light_red": "FEE2E2",
    "gray": "F8FAFC",
    "line": "CBD5E1",
    "text": "0F172A",
    "white": "FFFFFF",
}


def norm(v: str) -> str:
    return v.replace("\n", "\n").strip()


def clear_sheet(ws):
    for merged in list(ws.merged_cells.ranges):
        ws.unmerge_cells(str(merged))
    for row in ws.iter_rows():
        for cell in row:
            cell.value = None
            cell.hyperlink = None
            cell.comment = None
    ws.auto_filter.ref = None


def style_title(ws, title: str, subtitle: str, last_col: int):
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=last_col)
    c = ws.cell(1, 1, title)
    c.font = Font(name="PingFang SC", size=18, bold=True, color=COLORS["white"])
    c.fill = PatternFill("solid", fgColor=COLORS["navy"])
    c.alignment = Alignment(horizontal="center", vertical="center")
    ws.row_dimensions[1].height = 34
    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=last_col)
    c = ws.cell(2, 1, subtitle)
    c.font = Font(name="PingFang SC", size=10, color="334155")
    c.fill = PatternFill("solid", fgColor="E2E8F0")
    c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=True)
    ws.row_dimensions[2].height = 28


def set_header(row):
    for cell in row:
        cell.font = Font(name="PingFang SC", size=10, bold=True, color=COLORS["white"])
        cell.fill = PatternFill("solid", fgColor=COLORS["navy"])
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = Border(
            left=Side(style="thin", color=COLORS["line"]),
            right=Side(style="thin", color=COLORS["line"]),
            top=Side(style="thin", color=COLORS["line"]),
            bottom=Side(style="thin", color=COLORS["line"]),
        )


def write_table(ws, start_row: int, start_col: int, headers: list[str], rows: list[list], table_name: str | None = None):
    for i, h in enumerate(headers, start_col):
        ws.cell(start_row, i, h)
    set_header(ws[start_row][start_col - 1 : start_col - 1 + len(headers)])
    thin = Side(style="thin", color=COLORS["line"])
    for r_idx, row in enumerate(rows, start_row + 1):
        for c_idx, value in enumerate(row, start_col):
            cell = ws.cell(r_idx, c_idx, value)
            cell.font = Font(name="PingFang SC", size=10, color=COLORS["text"])
            cell.alignment = Alignment(vertical="top", wrap_text=True)
            cell.border = Border(left=thin, right=thin, top=thin, bottom=thin)
            if r_idx % 2 == 0:
                cell.fill = PatternFill("solid", fgColor=COLORS["gray"])
    end_row = start_row + len(rows)
    end_col = start_col + len(headers) - 1
    ws.auto_filter.ref = f"{get_column_letter(start_col)}{start_row}:{get_column_letter(end_col)}{end_row}"
    if table_name and rows:
        ref = f"{get_column_letter(start_col)}{start_row}:{get_column_letter(end_col)}{end_row}"
        tab = Table(displayName=table_name, ref=ref)
        tab.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showRowStripes=True, showColumnStripes=False)
        ws.add_table(tab)
    return end_row, end_col


def fit_columns(ws, widths: dict[int, int]):
    for idx, width in widths.items():
        ws.column_dimensions[get_column_letter(idx)].width = width


def freeze(ws, cell="A4"):
    ws.freeze_panes = cell


def add_note(ws, row: int, col: int, text: str, width_cols: int):
    ws.merge_cells(start_row=row, start_column=col, end_row=row, end_column=col + width_cols - 1)
    c = ws.cell(row, col, text)
    c.font = Font(name="PingFang SC", size=10, color="334155")
    c.fill = PatternFill("solid", fgColor="F1F5F9")
    c.alignment = Alignment(wrap_text=True, vertical="top")
    ws.row_dimensions[row].height = 44


def fill_status_colors(ws, status_col: int, start_row: int, end_row: int):
    for r in range(start_row, end_row + 1):
        v = str(ws.cell(r, status_col).value or "")
        fill = None
        if any(k in v for k in ["已完成", "已生成", "可用", "推进中"]):
            fill = COLORS["light_green"]
        if any(k in v for k in ["待确认", "待补", "进行中", "需补证"]):
            fill = COLORS["light_amber"]
        if any(k in v for k in ["阻塞", "异常", "风险"]):
            fill = COLORS["light_red"]
        if fill:
            ws.cell(r, status_col).fill = PatternFill("solid", fgColor=fill)


project_rows = [
    [1, "团餐/企事业", "交付-06 开发实现", "江苏国信/国庆项目（项目标准名称待确认）", "6月25日试用；7月1日正式上线", "待确认", "海琴/海军/倩倩/待确认", "方案/需求确认中", "待确认", "开发推进", "6/25试用", "7/1正式上线后触发", "上线后触发", "推进 H5/PC 原型、报餐/订餐/接待权限/人脸批量导入等业务规则", "完成模块状态表、客户确认清单、上线风险清单", "名称混用、定制需求与标准版差异大；需锁定范围和客户确认", "国信样板项目，可沉淀企业/国企标准包", "周报0605.xlsx C4/C6/C10；会议纪要"],
    [2, "团餐/食安", "交付-05 云效拆解", "食安教委端 PC 版本", "6月第2周进入开发衔接", "待确认", "待确认", "不涉及", "待确认", "PRD拆分/开发排期", "待排期", "未触发", "未触发", "方向基本确认；现有 PRD 拆为八个板块并补齐云效任务", "输出八板块任务拆解、效果图/前端衔接清单", "PRD细分、云效任务和开发估时需补齐", "学校/教委监管标准包", "周报0605.xlsx C5/C9；专项周报"],
    [3, "团餐/硬件", "交付-03 PRD/原型", "德沃晨检设备/后厨硬件能力", "下周完成门禁联动效果确认", "待确认", "待确认", "不涉及", "设备到位", "联调评估", "未触发", "未触发", "未触发", "设备已到、接口文档已提供，需确认与门禁联动效果", "形成硬件能力矩阵和标准版/教委版差异表", "供应商效果、设备型号、接口字段和验收证据不足", "硬件白名单/食安证据链", "周报0605.xlsx C5/C9；专项周报"],
    [4, "团餐/交付", "交付-09 试用上线", "江西206项目", "本周/下周继续从机迁移和上线问题闭环", "待确认", "待确认", "已签/待核", "已完成/待核", "上线后问题处理", "已上线/运行中", "待确认", "上线后触发", "跟进从机迁移、设备配对、压力测试和数据权限检查", "输出问题清单、迁移验证记录和回归结论", "现场网络/设备配对/数据权限影响稳定性", "从机迁移与压力测试 SOP", "周报0605.xlsx C8/C9；W23团队周汇总"],
    [5, "团餐/交付", "交付-01 项目启动", "莱迪森部署", "数据库名确认后导入并验表", "待确认", "待确认", "待确认", "已准备", "SQL文件已到VM目标目录", "未触发", "未触发", "未触发", "SQL 文件已部署到 VM 目标目录", "确认数据库名、导入 SQL、验表、继续 PHP/Nginx/Supervisor", "客户 VM 敏感配置不可入库；数据库状态待最终确认", "客户本地部署恢复 SOP", "HEARTBEAT；team-weekly-summary.md"],
    [6, "团餐/项目支持", "售后-04 回归验证", "金斯瑞项目", "持续跟进南京餐厅部署和食安/夜宵/设备支持", "待确认", "待确认", "已签/待核", "已完成/待核", "开发/部署支持", "运行/待上线", "待确认", "上线后触发", "跟进食安南京餐厅部署、夜宵逻辑、AI识别/取餐柜等问题", "输出现场问题清单、回归记录、标准能力沉淀建议", "需求分散，需避免项目支持长期吞噬标准化资源", "取餐柜/夜宵/AI识别结算场景沉淀", "周报0605.xlsx C8/C10；W23周汇总"],
    [7, "团餐/售后", "售后-02 问题定位", "山西焦煤余额显示问题", "下周提交问题单和修复计划", "待确认", "待确认", "已签/待核", "不涉及", "问题定位", "待修复上线", "回归后触发", "不涉及", "现金余额显示反向，需要提交邮件任务协调修复", "问题单、责任人、修复计划、回归结果", "余额/现金显示涉及账务口径，需谨慎验证", "消费机/余额显示问题库", "TXT重点事项；会议纪要抽取"],
    [8, "团餐/售后", "售后-03 修复排期", "AI 人脸识别摄像头调起异常", "下周形成修复方案和验证记录", "待确认", "待确认", "待确认", "不涉及", "定位中", "待修复", "回归后触发", "不涉及", "摄像头调用异常影响河北等项目落地", "问题说明、影响项目、修复方案、验证记录", "设备调用、浏览器权限、硬件/SDK差异需拆清", "AI人脸设备排障 SOP", "TXT重点事项；会议纪要抽取"],
    [9, "数科/项目", "运维/售前", "广东反兴奋剂/上海训练基地/网信办等数科项目", "6月继续按项目节点推进", "待确认", "王朔/待确认", "已签/待核", "不涉及", "运维/报价/项目支持", "运行中", "持续服务", "按节点", "政务云迁移报价、训练基地联调、网信办后厨管家等并行推进", "形成项目状态与费用/报价/上线支持清单", "跨项目事项较多，需看板化避免遗漏", "数科项目 SOP 阶段划分", "周报0605.xlsx C5/C6；HEARTBEAT"],
]

product_rows = [
    [1, "产品标准化", "PC端产品 PRD", "细化教育端/PC端 PRD，提交营销并与开发对齐估时，目标 6 月内完成上线拆解", "教委端/学校端/企业端", "6月30日前", "待确认", "PRD、云效任务、开发估时表、上线计划", "需按周拆分 6-8 月路线图"],
    [2, "产品标准化", "接待权限标准定义", "调研用友竞品接待权限逻辑，定义接待负责人、接待部门、外部人员、费用归属和订餐边界", "江苏国信/企业接待餐", "6月第2周", "倩倩/待确认", "《接待权限标准定义文档》", "非标能力，具备标准产品复用价值"],
    [3, "产品标准化", "人脸 PC 批量导入", "跟进人脸从 PC 端批量导入实现方案，判断沉淀为标准产品路径", "国信/人员管理/刷脸消费", "6月第2-3周", "待确认", "功能说明、PRD 或标准化建议", "需明确照片、权限、数据安全边界"],
    [4, "产品标准化", "食安教委监管标准包", "教委端 PC 版本按八板块拆解，补齐云效任务并与前端开发衔接", "学校/教委", "6月第2周", "待确认", "八板块拆解表、效果图、开发任务", "需补真实学校/教委验证材料"],
    [5, "产品差异化", "AI 营养价值链", "把智慧餐厅从普通结算系统升级为 AI 营养：健康画像、餐前推荐、餐中反馈、餐后报告", "企业/学校/运动队", "6月持续", "待确认", "demo 脚本、报告样张、FAQ、非医疗边界", "响应白总价值营销主线"],
    [6, "产品差异化", "三大系统控标能力", "将 AI 营养中枢映射到前厅营养结算、食安监管、进销存三大载体", "投标/售前/标准包", "6月持续", "待确认", "控标项、证书/软著证据夹、销售一页纸", "专利/软著/国产化证书需补真件"],
    [7, "AI赋能", "Playwright UI 自动化测试", "从本地跑通推进到协作项目可复用，补中文报告、失败分析、截图/视频、定时跑测", "开发/测试/交付", "下周", "待确认", "测试用例、HTML报告、视频、字幕、截图、失败分析", "需要选真实低风险项目接入"],
    [8, "AI赋能", "AI 协作 Agent", "产品资料/会议纪要训练 Agent，支持需求理解、云效提需、自动化测试、构建发布", "产品开发部协作", "7天试点", "Jack/待确认", "资料目录、训练清单、接入项目、生成结果", "高风险发布/支付/设备需人工门禁"],
    [9, "其他", "市场调研与竞品能力评估", "结合招投标/竞品/用友接待权限/多模态插件等调研，形成可进入产品和销售话术的结论", "产品/销售/市场中心", "每周持续", "待确认", "市场调研表、竞品对比、场景化材料", "需区分 C 级参考和 A/B 级证据"],
    [10, "其他", "专利/软著与知识产权", "推进 A01/A06/A08/A11/A14 五件专利补证；软著按版本升级和设备方向整理材料", "智慧营养/食安/AI硬件", "6月持续", "待确认", "补证包、代理人沟通清单、资质证据夹", "未授权前不得写成已授权专利"],
]

standardization_rows = [
    ["江苏国信/国庆", "接待权限/接待部门/接待餐", "企业接待餐、部门费用归属", "是", "企业/国企智慧食堂接待权限模块", "定义文档、竞品调研、PRD、云效任务", "待定义", "倩倩/待确认", "6月第2周"],
    ["江苏国信/国庆", "人脸从 PC 端批量导入", "员工批量建档、刷脸消费/取餐", "是", "人员管理标准能力", "功能说明、字段规则、异常处理、权限边界", "待方案", "待确认", "6月第2-3周"],
    ["江苏国信/国庆", "错分就餐/核销扣款", "堂食/外卖/接待餐核销", "可能", "企业样板项目能力包", "业务规则、原型、测试用例", "待客户确认", "待确认", "6月25日前"],
    ["教委端", "八板块 PC 端 PRD", "学校/教委食安监管", "是", "学校/教委监管标准包", "PRD、效果图、云效任务、验收标准", "待拆解", "待确认", "6月第2周"],
    ["德沃晨检/后厨硬件", "晨检设备联动门禁", "后厨人员上岗准入", "是", "食安硬件证据链", "设备型号、接口、联动视频、SOP", "设备到位，待验证", "待确认", "下周"],
    ["金斯瑞/标准版", "夜宵逻辑页面与规则", "运动队/企业夜宵营养管理", "可能", "AI营养/消费规则扩展", "页面清单、业务规则、开发任务", "需求增加", "待确认", "待排期"],
    ["山西焦煤", "余额显示异常处理", "消费机余额/现金显示", "是", "设备余额显示回归用例", "问题单、修复记录、回归截图", "待处理", "待确认", "下周"],
    ["河北等项目", "AI人脸识别摄像头调用异常", "人脸识别/摄像头调起", "是", "设备调用排障 SOP", "影响项目、修复方案、验证记录", "待定位", "待确认", "下周"],
    ["产品资料", "场景化标准文档和讲解视频", "销售演示/交付培训", "是", "标准资料库", "场景图、视频、微盘归档、Q&A", "持续完善", "倩倩/待确认", "持续"],
    ["机场餐卡", "系统需求分析 SOP", "客户 Excel 需求 -> 系统证据 -> 方案判断", "是", "system-grounded-requirement-analysis skill", "Before/After、HTML评审、需求分级", "已沉淀", "Jack/Codex", "已完成"],
]

ai_rows = [
    ["Codex/ChatGPT 产品资料生成", "需求理解、文档输出、场景图和云效任务整理效率低", "周报、会议纪要、项目资料、系统截图", "国信原型调整、场景化材料、周报图文版", "靠人工整理，周期长且不易追溯", "可快速生成初稿并保留证据路径", "已接入产品资料和周报", "固定输入模板和人审门禁", "Jack/产品负责人"],
    ["Playwright UI 自动化测试", "回归测试、报告、视频和失败定位难标准化", "自然语言用例、页面、测试账号、业务流程", "中文 HTML 报告、截图、视频、失败原因分析", "本地跑通但协作复用不足", "目标进入真实项目协作，可每日定时跑测", "待选低风险项目", "完成中文报告样例并接入协作项目", "待确认"],
    ["AI 协作 Agent", "会议纪要到需求、云效、测试、构建发布链路断裂", "产品资料、项目资料、会议纪要、云效任务", "需求理解、任务提交、测试/构建/发布建议", "人工转写和任务拆解多", "目标减少人工干预并提升任务质量", "处于7天试点设计", "先跑白总 AI营养价值链和奇庚总样板运营", "Jack/待确认"],
    ["AI 运动营养师", "AI 对话身份、历史、游客迁移、三餐推荐等基础能力需稳定", "用户画像、bot_id、conversation_id、历史消息、饮食数据", "三餐推荐、会话稳定、历史列表、游客迁移方案", "清缓存丢历史、用户身份不稳", "后端持久化身份和会话元数据", "待拆云效任务", "拆解任务并对齐 V1/V2 版本计划", "待确认"],
    ["AI 用户手册/操作视频", "交付培训材料制作慢，不易复用", "业务流程、截图、旁白脚本、测试记录", "手册、Word/PDF、操作视频素材", "人工截图和写稿", "可从测试/流程自动生成材料", "已有项目 skill 和视频 harness", "选择 1 个功能形成手册+视频样例", "待确认"],
    ["市场/竞品调研 AI 化", "市场调研与竞品分析不能停留在资料摘抄", "招投标、竞品页面、行业报告、用友接待权限逻辑", "场景化结论、产品差距、销售话术、补证清单", "人工分散检索", "每周沉淀为场景/标准包/投标参数", "已有智慧食堂周包和招投标分析", "建立场景化市场调研表", "市场/产品"],
]

planning_rows = [
    ["PC端产品 PRD", "提炼会议要求和现有 PRD 缺口", "拆八板块/企业端关键链路", "提交云效并估时", "开发/测试联动", "上线节点复核", "进行中", "源表和会议纪要已抽取", "PRD+云效任务+估时表", "待确认", "需补责任人和开发估时"],
    ["国信/国庆样板项目", "原型与业务规则跟进", "客户确认清单", "试用问题清单", "上线风险清单", "7/1正式上线复盘", "高优先", "H5/PC链路和定制需求已识别", "模块状态表", "海琴/海军/待确认", "名称和范围待确认"],
    ["接待权限标准化", "定义问题和竞品对象", "调研用友逻辑", "输出标准定义", "PRD/云效", "标准产品评审", "待启动", "源表已有定义雏形", "标准定义文档", "倩倩/待确认", "权限边界需谨慎"],
    ["食安教委端", "方向确认", "八板块拆解", "前端开发衔接", "联调/演示", "标准包评审", "进行中", "方向基本确认", "八板块任务表", "待确认", "缺真实验证材料"],
    ["硬件能力矩阵", "德沃/后厨硬件梳理", "门禁联动验证", "前厅硬件资料扩展", "设备白名单", "销售/交付包", "进行中", "设备到位，接口文档已提供", "硬件能力矩阵", "待确认", "供应商验证不足"],
    ["Playwright 自动化测试", "框架方向明确", "中文报告样例", "接入协作项目", "定时跑测/视频", "沉淀测试 SOP", "进行中", "迭代方向明确", "HTML报告样例", "待确认", "需真实项目验证"],
    ["AI运动营养师", "迭代方向抽取", "身份/会话/历史任务拆解", "三餐推荐/游客迁移", "联调验证", "报告/推荐闭环", "待拆解", "六项优化方向明确", "云效任务包", "待确认", "健康数据授权边界"],
]

market_rows = [
    ["白总价值营销", "AI营养，让你吃得对", "健康画像、餐前推荐、餐中反馈、餐后报告", "把智慧餐厅从普通系统升级为健康团餐服务入口", "10分钟 demo + FAQ + 非医疗声明", "B/D内部会议证据，需补真实客户反馈"],
    ["奇庚总经营执行", "样板、渠道、标准包、30天运营", "重点项目作战、学校/企事业标准包、样板运营", "证明能签约、验收、回款、复制", "6月重点项目作战表 + 样板运营 SOP", "收入/回款/验收需财务和合同证据"],
    ["企业/国企场景", "国信接待餐、员工就餐、部门费用归属", "接待权限、接待部门、H5报餐/订餐、人脸批量导入", "形成企业/国企智慧食堂标准包", "国信样板项目复盘和标准定义文档", "项目名称和范围待确认"],
    ["学校/教委场景", "食安监管、膳食经费、家长阳光公示", "教委端 PC、学校端、食安驾驶舱、晨检设备", "形成学校/教委监管标准包", "八板块 PRD + 食安硬件证据链", "需补学校试点和政策/专家证据"],
    ["市场/竞品调研", "用友接待权限、招投标参数、智慧食堂行业周包", "竞品逻辑、招标评分、行业趋势", "把调研转成产品差距、控标参数和销售话术", "竞品对比表 + 销售一页纸", "聚合源和竞品材料多为 C/D 级"],
    ["存量客户增长/QBR", "客户成功、运营月报、续费增购", "机场餐卡、金斯瑞、206、网信办等项目证据", "从交付支持转向存量经营", "客户 QBR 样张 + 30天陪跑 SOP", "需客户授权和真实运营数据"],
]

patent_rows = [
    ["A01", "基于餐饮结算事件触发的膳食结构分析与即时营养建议推送方法及系统", 95, 68, "P0预备", "支付成功页、营养摘要、授权脱敏、推送日志", "康比特运动营养能力进入高频用餐场景的即时反馈入口", "支付到建议 trace、授权留痕、推送状态"],
    ["A06", "基于识别区域校准与在线学习的 AI 菜品识别结算方法及系统", 92, 82, "P0优先", "设备型号、识别区域、准确率、在线学习、支付联动", "AI识别结算台和真实摄入数据入口", "正式测试指标和部署证据"],
    ["A08", "基于食堂消费数据与个人体征数据融合的营养日报及阶段报告生成方法及系统", 96, 70, "P0预备", "报告样张、设备 trace、授权、非医疗声明", "持续营养服务和续费能力的核心专利基础", "体征设备同步 trace、报告日志、缺失降级"],
    ["A11", "基于 AI 视觉时序融合的后厨违规行为识别与闭环预警方法及系统", 82, 80, "P0优先", "AI事件、整改工单、人工复核、算法指标", "后厨 AI 食安事件入口，区别于普通明厨亮灶", "真实事件 trace、误报复核统计"],
    ["A14", "集中用餐单位食品安全多维风险评分、分层预警与驾驶舱闭环管理方法及系统", 88, 74, "P0优先但先补证", "评分权重、阈值版本、脱敏事件、驾驶舱截图", "食安风险中枢和监管驾驶舱", "风险分权重和客户场景验证"],
]

risk_rows = [
    ["国信/国庆项目名称和范围未统一", "国信样板项目", "影响试用/上线范围、客户确认和周报口径", "项目名称混用、定制与标准版边界待确认", "产品负责人/项目负责人", "6月第2周", "待确认"],
    ["6月25日试用和7月1日上线节点压力", "国信/国庆项目", "影响客户试用、正式上线和样板复盘", "模块状态、风险清单、客户确认不足", "项目负责人/研发负责人", "6月25日前", "待确认"],
    ["PC端 PRD 和云效任务未完全拆解", "教委端/企业端产品", "影响研发估时、排期和验收", "缺按周拆解和可验收产物", "产品负责人", "6月30日前", "待确认"],
    ["AI 自动化测试未进入真实协作项目", "AI赋能/测试", "AI价值难以被管理层看见", "中文报告、失败分析、视频、定时跑测未形成样例", "研发/测试负责人", "下周", "待确认"],
    ["山西焦煤余额显示涉及账务口径", "山西焦煤", "错误修复可能影响现金/补贴显示和客户信任", "需确认现金余额、补贴余额和显示逻辑", "研发/产品/交付", "下周", "待确认"],
    ["摄像头调用异常影响项目落地", "AI人脸识别相关项目", "影响河北等项目的人脸能力交付", "问题原因、设备差异、浏览器/SDK权限未拆清", "研发负责人", "下周", "待确认"],
    ["专利和软著不能写成已授权事实", "专利/资质", "对外宣传和控标材料存在合规风险", "A01/A08等仍需补证，技术交底书不是授权专利", "产品/法务/专利代理人", "持续", "Jack/待确认"],
    ["市场调研证据等级不足", "市场/竞品/招投标", "可能把聚合源或竞品推导写成事实", "官方原文、附件、评分细则待补", "市场/产品", "每周", "待确认"],
]

next_rows = [
    ["输出 PC 端 PRD 拆解和云效任务表", "产品标准化", "PRD、云效任务、开发估时表", "6月30日前完成上线目标拆解，任务可按负责人估时", "产品负责人待确认", "研发负责人", "2026-06-30", "估时和范围待确认"],
    ["国信/国庆项目试用前问题清单闭环", "项目交付闭环", "模块状态表、客户确认清单、上线风险清单", "6月25日前能支撑试用，7月1日前有正式上线风险控制表", "项目负责人待确认", "研发/交付", "2026-06-25", "项目名称/范围待确认"],
    ["完成接待权限标准定义文档", "产品标准化", "接待权限定义文档", "明确接待负责人、接待部门、费用归属、外部人员和订餐权限边界", "倩倩/待确认", "产品/研发", "2026-06-13", "竞品逻辑和标准版边界待确认"],
    ["人脸 PC 批量导入标准化建议", "产品标准化", "功能说明或 PRD", "说明是否进入标准产品、字段规则、权限和异常处理", "待确认", "研发", "2026-06-20", "数据安全与照片权限"],
    ["Playwright 中文报告样例", "AI赋能提效", "HTML测试报告、截图、视频、失败分析", "至少覆盖 1 个低风险真实项目流程，可复跑", "待确认", "研发/测试", "2026-06-13", "测试环境和账号准备"],
    ["AI运动营养师迭代任务拆解", "AI赋能/产品差异化", "三餐推荐、会话稳定、游客迁移等任务包", "进入任务管理并明确版本计划", "待确认", "ai_api/ai_app负责人", "2026-06-20", "健康数据授权和非医疗边界"],
    ["食安/教委/硬件能力矩阵", "产品标准化/市场调研", "硬件矩阵、标准版/教委版差异表、接入方案", "德沃晨检、后厨硬件、体脂秤等有状态和下一步验证", "待确认", "硬件/交付/产品", "2026-06-20", "供应商验证和证据不足"],
    ["专利 A01/A06/A08/A11/A14 补证清单推进", "商业价值/IP资产", "补证任务表、代理人沟通清单", "每件专利列出缺证、责任人、补证路径", "Jack/产品负责人", "专利代理人", "2026-06-30", "法律意见和检索结果待代理人确认"],
    ["形成场景化市场调研表", "业界能力/市场调研", "学校/企业/政府/医院/社区场景表", "每个场景至少一条市场信号、客户痛点、产品动作和证据缺口", "市场/产品", "销售/解决方案", "2026-06-13", "聚合信息需官方源核验"],
]

source_rows = [
    ["S01", "周报0605.xlsx", str(SOURCE_WEEKLY), "原始本周周报、当前周 C 列、图片素材", "B"],
    ["S02", "周报模板.xlsx", str(TEMPLATE), "事业部层面项目/产品工作汇报模板", "B"],
    ["S03", "已粘贴的文本.txt", str(PASTED_TEXT), "周报填写要求、字段、禁止写法和重点事项", "B"],
    ["S04", "产品标准化与AI赋能专项周报", str(ROOT / "work/weekly-reports/2026-W23/product-standardization-ai-weekly/product-standardization-ai-weekly-report.md"), "本周主线、关键判断、图文证据", "B/D"],
    ["S05", "W23团队周汇总", str(ROOT / "work/weekly-reports/outputs/2026-W23/team-weekly-summary.md"), "项目全局亮点、专利、企业微盘、机场餐卡 SOP", "B/D"],
    ["S06", "白总/奇庚总领导思路执行包", str(ROOT / "work/2026-06-02-ai-nutrition-smart-canteen-parallel-execution-system/summary.md"), "价值营销、样板运营、标准包、7天试点", "B/D"],
    ["S07", "专利申请挖掘材料包", str(ROOT / "work/2026-06-03-patent-application-mining-package/summary.md"), "A01/A06/A08/A11/A14 5件专利优先级和补证", "B/D"],
    ["S08", "产品差异性专利控标项", str(ROOT / "work/2026-05-30-product-differentiation-patent-control-items/patent-capability-map.csv"), "15份技术交底书到三大系统控标能力映射", "B/D"],
    ["S09", "HEARTBEAT.md", str(ROOT / "HEARTBEAT.md"), "全局本周恢复入口、领导思路、项目亮点", "B"],
]


def build_workbook():
    OUT_DIR.mkdir(parents=True, exist_ok=True)
    work_copy = OUT_DIR / "template-working-copy.xlsx"
    shutil.copy2(TEMPLATE, work_copy)
    wb = load_workbook(work_copy)

    # Keep the template sheet names but rebuild their contents.
    for sheet_name in ["项目", "产品"]:
        if sheet_name in wb.sheetnames:
            clear_sheet(wb[sheet_name])

    summary = wb.create_sheet("00_管理摘要", 0)
    project = wb["项目"]
    product = wb["产品"]
    planning = wb.create_sheet("产品规划")
    standard = wb.create_sheet("标准化台账")
    ai = wb.create_sheet("AI赋能")
    market = wb.create_sheet("市场调研与场景")
    patent = wb.create_sheet("专利与资质")
    risk = wb.create_sheet("风险与决策")
    next_plan = wb.create_sheet("下周计划")
    visuals = wb.create_sheet("图文证据")
    sources = wb.create_sheet("来源索引")

    # Summary
    style_title(summary, "数字技术中心-产品开发部周报（丰富版）", "覆盖周期：2026-06-01 至 2026-06-07｜口径：按主线、阶段、产物、风险、沉淀价值组织，兼顾白总价值营销与奇庚总经营执行", 10)
    add_note(summary, 3, 1, "一句话总结：本周围绕产品标准化、项目交付闭环、AI赋能提效、市场调研与知识产权沉淀推进；核心是把国信样板、教委/食安标准包、硬件矩阵、AI自动化测试和AI营养价值链收束为可验收、可复用、可支撑经营的资产。", 10)
    kpi_rows = [
        ["主线", "本周信号", "经营含义", "证据等级"],
        ["产品标准化", "PC端PRD、教委端八板块、接待权限、人脸批量导入", "把项目需求变成标准能力和可报价模块", "B/D"],
        ["项目交付闭环", "国信6/25试用、7/1上线；206/莱迪森/金斯瑞等并行", "用SOP阶段降低并发交付风险", "B"],
        ["AI赋能提效", "Playwright中文报告、AI协作Agent、AI手册视频、Codex资料生成", "从工具使用转为流程资产", "B/D"],
        ["市场调研/场景化", "用友接待权限、招投标参数、学校/企业场景", "把市场信号转为产品动作和销售话术", "C/D"],
        ["商业价值/IP", "A01/A06/A08/A11/A14 五件专利优先补证", "形成AI营养、AI视觉结算、食安监管控标护城河", "B/D"],
    ]
    write_table(summary, 5, 1, kpi_rows[0], kpi_rows[1:], "SummaryMainlines")
    summary["A13"] = "本周重点不要写成“持续推进”：所有事项都必须落到产物、负责人、DDL、风险和沉淀价值。"
    summary["A13"].font = Font(name="PingFang SC", bold=True, color=COLORS["red"])
    summary.merge_cells("A13:J13")
    summary["A13"].fill = PatternFill("solid", fgColor=COLORS["light_red"])
    chart_data = [
        ["分类", "事项数"],
        ["重点项目", len(project_rows)],
        ["产品事项", len(product_rows)],
        ["标准化台账", len(standardization_rows)],
        ["AI赋能", len(ai_rows)],
        ["风险决策", len(risk_rows)],
        ["下周计划", len(next_rows)],
    ]
    write_table(summary, 15, 1, chart_data[0], chart_data[1:], "SummaryCounts")
    chart = BarChart()
    chart.title = "本周管理对象数量"
    chart.y_axis.title = "数量"
    chart.x_axis.title = "分类"
    data = Reference(summary, min_col=2, min_row=15, max_row=15 + len(chart_data) - 1)
    cats = Reference(summary, min_col=1, min_row=16, max_row=15 + len(chart_data) - 1)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.height = 7
    chart.width = 15
    summary.add_chart(chart, "E15")
    fit_columns(summary, {1: 22, 2: 42, 3: 44, 4: 14, 5: 18, 6: 18, 7: 18, 8: 18, 9: 18, 10: 18})
    freeze(summary, "A5")

    # Project sheet
    style_title(project, "一、项目工作", "围绕合同-采购-交付-上线-验收-回款六节点推进；新增 SOP 阶段、沉淀价值和证据链接，避免按人名流水账。", 18)
    project_headers = ["序号", "项目类型", "项目阶段", "项目名称", "关键时间", "项目金额", "负责人", "合同", "采购", "交付", "上线", "验收", "回款", "本周进展", "本周计划/后续动作", "问题与偏差", "备注", "链接"]
    end_row, _ = write_table(project, 4, 1, project_headers, project_rows, "ProjectWeekly")
    fill_status_colors(project, 3, 5, end_row)
    fit_columns(project, {1: 6, 2: 15, 3: 18, 4: 26, 5: 22, 6: 12, 7: 18, 8: 14, 9: 14, 10: 16, 11: 16, 12: 16, 13: 16, 14: 44, 15: 44, 16: 38, 17: 34, 18: 36})
    freeze(project, "A5")

    # Product sheet
    style_title(product, "二、产品工作", "按产品差异化、产品标准化、AI赋能、市场调研/IP资产归纳，明确时间节点、责任人、输出物和经营价值。", 9)
    product_headers = ["序号", "一级分类", "二级分类", "工作内容", "对应项目/场景", "时间节点", "负责人", "本周输出/目标", "备注"]
    end_row, _ = write_table(product, 4, 1, product_headers, product_rows, "ProductWeekly")
    fill_status_colors(product, 2, 5, end_row)
    fit_columns(product, {1: 6, 2: 16, 3: 22, 4: 52, 5: 22, 6: 18, 7: 16, 8: 40, 9: 36})
    freeze(product, "A5")

    # Other sheets
    sheet_specs = [
        (planning, "产品规划落地表", "必须按周拆解，不允许只写“6月完成”。", ["产品/模块", "6月第1周", "6月第2周", "6月第3周", "6月第4周", "7月第1周", "当前状态", "本周产物", "下周产物", "负责人", "风险"], planning_rows, "PlanningTable", {1: 22, 2: 28, 3: 28, 4: 28, 5: 28, 6: 28, 7: 14, 8: 30, 9: 30, 10: 16, 11: 30}),
        (standard, "标准化沉淀台账", "凡是项目中出现的非标能力，都要判断是否进入标准产品。", ["来源项目", "非标需求/功能", "业务场景", "是否可复用", "标准产品建议", "需要沉淀的资料", "当前状态", "负责人", "DDL"], standardization_rows, "StandardizationTable", {1: 20, 2: 26, 3: 28, 4: 12, 5: 34, 6: 40, 7: 16, 8: 16, 9: 16}),
        (ai, "AI赋能周报", "AI 赋能必须写清输入、输出、Before/After、是否接入项目和下周计划。", ["AI场景", "解决的问题", "输入资料", "输出结果", "Before", "After", "是否接入项目", "下周计划", "负责人"], ai_rows, "AIEnablementTable", {1: 24, 2: 34, 3: 32, 4: 36, 5: 30, 6: 34, 7: 20, 8: 36, 9: 16}),
        (market, "市场调研与场景化表达", "响应白总价值营销、奇庚总经营执行：市场调研必须转化为产品动作、销售话术和证据缺口。", ["主线/场景", "市场/领导信号", "产品化表达", "业务含义", "下周动作", "证据边界/缺口"], market_rows, "MarketScenarioTable", {1: 20, 2: 30, 3: 36, 4: 36, 5: 34, 6: 34}),
        (patent, "专利与资质沉淀", "专利材料只作为申报前筛查和产品差异性证据，未授权前不得写成已授权专利。", ["编号", "建议发明名称", "战略核心度", "当前准备度", "当前层级", "补证重点", "产品/经营价值", "主要缺口"], patent_rows, "PatentTable", {1: 8, 2: 56, 3: 14, 4: 14, 5: 18, 6: 36, 7: 38, 8: 34}),
        (risk, "风险与决策请求", "风险必须写影响、阻塞点、需要谁决策和最晚解决时间。", ["风险事项", "影响项目", "影响结果", "当前阻塞点", "需要谁决策/协调", "最晚解决时间", "负责人"], risk_rows, "RiskTable", {1: 32, 2: 24, 3: 38, 4: 38, 5: 24, 6: 16, 7: 16}),
        (next_plan, "下周计划", "下周计划必须是可验收任务，不写“继续推进”。", ["任务", "所属主线", "交付物", "验收标准", "负责人", "协作人", "截止时间", "风险"], next_rows, "NextPlanTable", {1: 34, 2: 20, 3: 34, 4: 48, 5: 18, 6: 18, 7: 16, 8: 32}),
        (sources, "来源索引", "所有结论需能追溯到来源；D级判断不得写成已完成事实。", ["source_id", "source_name", "path", "used_for", "evidence_level"], source_rows, "SourceIndexTable", {1: 10, 2: 30, 3: 86, 4: 48, 5: 14}),
    ]
    for ws, title, subtitle, headers, rows, table_name, widths in sheet_specs:
        style_title(ws, title, subtitle, len(headers))
        end_row, _ = write_table(ws, 4, 1, headers, rows, table_name)
        fit_columns(ws, widths)
        freeze(ws, "A5")
        for r in range(5, end_row + 1):
            ws.row_dimensions[r].height = 58

    # Add visual evidence sheet.
    style_title(visuals, "图文证据", "引用 W23 专项周报已生成的经营看板和源材料截图，方便领导快速扫读。", 6)
    visual_items = [
        ("本周经营结论", ROOT / "work/weekly-reports/2026-W23/product-standardization-ai-weekly/charts/01-executive-summary.png"),
        ("重点项目/能力推进看板", ROOT / "work/weekly-reports/2026-W23/product-standardization-ai-weekly/charts/02-project-board.png"),
        ("AI 赋能闭环", ROOT / "work/weekly-reports/2026-W23/product-standardization-ai-weekly/charts/03-ai-enablement-loop.png"),
        ("风险与控制动作", ROOT / "work/weekly-reports/2026-W23/product-standardization-ai-weekly/charts/04-risk-control.png"),
        ("下周推进路线图", ROOT / "work/weekly-reports/2026-W23/product-standardization-ai-weekly/charts/05-next-week-roadmap.png"),
    ]
    row = 4
    for title, img_path in visual_items:
        visuals.cell(row, 1, title)
        visuals.cell(row, 1).font = Font(name="PingFang SC", size=12, bold=True, color=COLORS["navy"])
        visuals.cell(row + 1, 1, str(img_path))
        visuals.cell(row + 1, 1).font = Font(name="PingFang SC", size=9, color="64748B")
        visuals.merge_cells(start_row=row, start_column=1, end_row=row, end_column=6)
        visuals.merge_cells(start_row=row + 1, start_column=1, end_row=row + 1, end_column=6)
        if img_path.exists():
            img = XLImage(str(img_path))
            img.width = 900
            img.height = 500
            visuals.add_image(img, f"A{row + 2}")
            visuals.row_dimensions[row + 2].height = 380
        row += 25
    fit_columns(visuals, {1: 24, 2: 24, 3: 24, 4: 24, 5: 24, 6: 24})

    # Global formatting.
    for ws in wb.worksheets:
        ws.sheet_view.showGridLines = False
        for row_cells in ws.iter_rows():
            for cell in row_cells:
                if cell.value is not None:
                    cell.alignment = copy(cell.alignment)
                    cell.alignment = Alignment(
                        horizontal=cell.alignment.horizontal or "left",
                        vertical=cell.alignment.vertical or "top",
                        wrap_text=True,
                    )

    wb.save(OUTPUT)

    with SOURCE_INDEX.open("w", newline="", encoding="utf-8-sig") as f:
        writer = csv.writer(f)
        writer.writerow(["source_id", "source_name", "path", "used_for", "evidence_level"])
        writer.writerows(source_rows)

    if work_copy.exists():
        work_copy.unlink()


if __name__ == "__main__":
    build_workbook()
    print(OUTPUT)