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

import csv
import json
import math
import subprocess
from collections import Counter
from datetime import datetime
from pathlib import Path

from openpyxl import Workbook, load_workbook
from openpyxl.chart import BarChart, DoughnutChart, Reference
from openpyxl.comments import Comment
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")
THREAD_ARCHIVE_ROOT = Path("/Users/jack/code/099-github/notebooklm-mcp/output/codex-thread-archives")
WORK = ROOT / "work/2026-09-21-evidence-backed-data-analysis"
OUTPUT = ROOT / "outputs/2026-09-21-evidence-backed-data-analysis"
XLSX = OUTPUT / "康比特数科证据驱动数据分析_20260921.xlsx"
SNAPSHOT = WORK / "evidence-snapshot.json"
SOURCE_AUDIT = WORK / "source-audit.csv"
DECISIONS = WORK / "decision-matrix.csv"
ANALYSIS_MD = WORK / "evidence-backed-data-analysis.md"

FONT = "Heiti SC"
NAVY = "17365D"
BLUE = "1F4E79"
TEAL = "0F6B6D"
GREEN = "2E7D32"
AMBER = "C98300"
RED = "B42318"
PALE_BLUE = "DCE6F1"
PALE_GREEN = "E2F0D9"
PALE_AMBER = "FFF2CC"
PALE_RED = "FCE4D6"
LIGHT = "F4F6F8"
GRID = "D9E1E8"
TEXT = "243447"


def read_tsv(path: Path) -> list[dict[str, str]]:
    with path.open(encoding="utf-8-sig", newline="") as fh:
        return list(csv.DictReader(fh, delimiter="\t"))


def read_csv(path: Path) -> list[dict[str, str]]:
    with path.open(encoding="utf-8-sig", newline="") as fh:
        return list(csv.DictReader(fh))


def read_json(path: Path):
    return json.loads(path.read_text(encoding="utf-8"))


def git_value(*args: str) -> str:
    result = subprocess.run(
        ["git", *args], cwd=ROOT, text=True, capture_output=True, check=True
    )
    return result.stdout.strip()


def pct(n: float, d: float) -> float | None:
    return n / d if d else None


def evidence_level_fill(level: str) -> str:
    return {"A": PALE_GREEN, "B": PALE_BLUE, "C": PALE_AMBER, "D": PALE_RED}.get(level, LIGHT)


def normalize_time(value: str) -> str:
    return (value or "").replace("T", " ").replace("Z", " +00:00")


def latest_global_thread_index() -> Path:
    candidates: list[tuple[int, str, Path]] = []
    snapshots = THREAD_ARCHIVE_ROOT / "snapshots"
    for path in snapshots.glob("*/*/index.json"):
        try:
            data = read_json(path)
        except (OSError, json.JSONDecodeError):
            continue
        if not data.get("workspace_filter") and int(data.get("thread_count") or 0) > 0:
            candidates.append((int(data.get("thread_count") or 0), str(data.get("generated_at") or ""), path))
    if candidates:
        return max(candidates, key=lambda item: (item[0], item[1]))[2]
    return THREAD_ARCHIVE_ROOT / "latest/index.json"


def add_source_comment(cell, source: str, date: str = ""):
    text = f"Source: {source}"
    if date:
        text += f"; Data date: {date}"
    cell.comment = Comment(text, "Codex")


def set_widths(ws, widths: dict[str, float]) -> None:
    for col, width in widths.items():
        ws.column_dimensions[col].width = width


def base_sheet(ws, freeze: str = "A2", fit_height: int = 1) -> None:
    ws.sheet_view.showGridLines = False
    ws.freeze_panes = freeze
    ws.sheet_properties.pageSetUpPr.fitToPage = True
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = fit_height
    ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
    ws.page_setup.paperSize = ws.PAPERSIZE_A4
    ws.page_margins.left = 0.25
    ws.page_margins.right = 0.25
    ws.page_margins.top = 0.35
    ws.page_margins.bottom = 0.35


def style_header(row) -> None:
    for cell in row:
        cell.fill = PatternFill("solid", fgColor=BLUE)
        cell.font = Font(name=FONT, size=10, bold=True, color="FFFFFF")
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = Border(bottom=Side(style="thin", color=GRID))


def style_body(ws, min_row: int, max_row: int, min_col: int, max_col: int) -> None:
    thin = Side(style="thin", color=GRID)
    for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
        for cell in row:
            cell.font = Font(name=FONT, size=10, color=TEXT)
            cell.alignment = Alignment(vertical="top", wrap_text=True)
            cell.border = Border(bottom=thin)


def add_table(ws, name: str, start_row: int, end_row: int, end_col: int) -> None:
    if end_row <= start_row:
        return
    ref = f"A{start_row}:{get_column_letter(end_col)}{end_row}"
    table = Table(displayName=name, ref=ref)
    table.tableStyleInfo = TableStyleInfo(
        name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False,
        showRowStripes=True, showColumnStripes=False,
    )
    ws.add_table(table)


def add_title(ws, title: str, subtitle: str, end_col: int) -> None:
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=end_col)
    c = ws.cell(1, 1, title)
    c.fill = PatternFill("solid", fgColor=NAVY)
    c.font = Font(name=FONT, size=18, bold=True, color="FFFFFF")
    c.alignment = Alignment(vertical="center")
    ws.row_dimensions[1].height = 32
    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=end_col)
    c = ws.cell(2, 1, subtitle)
    c.font = Font(name=FONT, size=10, color="5B6573")
    c.alignment = Alignment(vertical="center", wrap_text=True)
    ws.row_dimensions[2].height = 28


def write_csv(path: Path, header: list[str], rows: list[list]) -> None:
    path.parent.mkdir(parents=True, exist_ok=True)
    with path.open("w", encoding="utf-8-sig", newline="") as fh:
        writer = csv.writer(fh)
        writer.writerow(header)
        writer.writerows(rows)


def collect() -> dict:
    tasks = read_tsv(ROOT / "control/task-index/tasks.tsv")
    skill_rows = read_csv(ROOT / "standards-stack/agent-skills/CODEX_TEAM_SKILL_MANIFEST.csv")
    current_repos = read_tsv(ROOT / "control/current-status.tsv")
    repo_manifest = read_csv(ROOT / "control/zhctproject-repositories.csv")
    problem_stats = read_json(ROOT / "work/2026-09-18-workspace-problem-ranking/stats.json")
    problem_groups = read_json(ROOT / "work/2026-09-18-workspace-problem-ranking/group-map.json")
    scenes = read_json(ROOT / "work/weekly-reports/2026-W39/direction-template/output/projects-classified.json")
    financial = read_json(ROOT / "work/weekly-reports/2026-W39/direction-template/output/financial.json")
    thread_index = latest_global_thread_index()
    threads = read_json(thread_index)
    active_rows = read_csv(ROOT / "work_store/data-queries/active-user-breakdown-20260905/project-breakdown.csv")

    recent_tasks = [row for row in tasks if row.get("updated_at", "")[:10] >= "2026-08-23"]
    recent_threads = [row for row in threads["threads"] if row.get("updated_at", "")[:10] >= "2026-08-23"]
    scene_counts = Counter(row["scene"] for row in scenes)
    stage_counts = Counter(row["stage"] for row in scenes)
    status_counts = Counter(row["status"] for row in scenes)
    active_positive = [row for row in active_rows if float(row.get("累计订单活跃人次") or 0) > 0]
    active_order_projects = [row for row in active_rows if float(row.get("累计订单") or 0) > 0]

    baseline = {
        "date": "2026-07-20",
        "tasks": 307,
        "threads": 1497,
        "skills": 189,
        "pm_skills": 69,
        "wedrive_files": 59468,
        "wedrive_docs": 7207,
        "repo_inventory": 47,
        "registered_repos": 11,
        "source": "work/2026-07-20-connected-systems-full-data/system-snapshot.csv",
    }
    current = {
        "date": "2026-09-21",
        "tasks": len(tasks),
        "threads": int(threads["thread_count"]),
        "skills": len(skill_rows),
        "pm_skills": sum(row["skill_name"].startswith("pm-") for row in skill_rows),
        "wedrive_files": 61880,
        "wedrive_docs": 7825,
        "repo_inventory": len(current_repos),
        "registered_repos": len(repo_manifest),
        "source": "current files; WeDrive snapshot 2026-09-10",
    }

    evidence = {
        "generated_at": datetime.now().astimezone().isoformat(timespec="seconds"),
        "git_sha": git_value("rev-parse", "HEAD"),
        "thread_index": str(thread_index),
        "baseline": baseline,
        "current": current,
        "recent_30d": {
            "tasks": len(recent_tasks),
            "tasks_with_evidence": sum(bool(row.get("evidence_path", "").strip()) for row in recent_tasks),
            "threads": len(recent_threads),
            "thread_workspaces": len({row.get("workspace", "") for row in recent_threads}),
            "problem_record_days": sum(int(row["days"]) for row in problem_stats),
            "problem_groups": sum(int(row["groups"]) for row in problem_stats),
            "problem_tasks": sum(int(row["tasks"]) for row in problem_stats),
        },
        "scene": {
            "records": len(scenes),
            "mapped": len([row for row in scenes if row["scene"] != "待归类"]),
            "counts": dict(scene_counts),
            "stages": dict(stage_counts),
            "statuses": dict(status_counts),
            "top_two_share": pct(scene_counts["政企"] + scene_counts["学校"], len(scenes)),
        },
        "active_usage": {
            "projects": len(active_rows),
            "projects_with_orders": len(active_order_projects),
            "projects_with_positive_active": len(active_positive),
            "registered_accounts_snapshot": sum(int(float(row.get("登记账号快照") or 0)) for row in active_rows),
            "cumulative_order_active_visits": sum(int(float(row.get("累计订单活跃人次") or 0)) for row in active_rows),
            "cumulative_orders": sum(int(float(row.get("累计订单") or 0)) for row in active_rows),
            "meeting_public_cloud_users": 28000,
            "meeting_public_cloud_dau": 5000,
        },
        "problem_stats": problem_stats,
        "financial": financial,
    }
    SNAPSHOT.write_text(json.dumps(evidence, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")
    return {
        "evidence": evidence,
        "tasks": tasks,
        "recent_tasks": recent_tasks,
        "problem_groups": problem_groups,
        "scenes": scenes,
        "active_rows": active_rows,
    }


def make_workbook(data: dict) -> None:
    ev = data["evidence"]
    wb = Workbook()
    wb.remove(wb.active)

    # 1. 结论总览
    ws = wb.create_sheet("结论总览")
    base_sheet(ws, "A8")
    add_title(ws, "康比特数科证据驱动数据分析", "截至 2026-09-21｜历史基线 + 最近30天问题结构 + 六场景项目组合 + 经营/使用口径审计", 8)
    metrics = [
        ("任务索引", ev["current"]["tasks"], "条", "A", "control/task-index/tasks.tsv"),
        ("近30天任务", ev["recent_30d"]["tasks"], "条", "A", "updated_at >= 2026-08-23"),
        ("线程归档", ev["current"]["threads"], "条", "A", ev["thread_index"]),
        ("微盘文件", ev["current"]["wedrive_files"], "个", "A", "work_company_knowledge/reports/summary.md"),
        ("六场景记录", ev["scene"]["records"], "条", "B", "W39 projects-classified.json"),
        ("问题主题", ev["recent_30d"]["problem_groups"], "个", "B", "workspace-problem-ranking/group-map.json"),
    ]
    for idx, (name, value, unit, level, source) in enumerate(metrics):
        col = 1 + idx
        ws.cell(4, col, name)
        ws.cell(5, col, value)
        ws.cell(6, col, unit)
        ws.cell(4, col).fill = PatternFill("solid", fgColor=evidence_level_fill(level))
        ws.cell(4, col).font = Font(name=FONT, size=10, bold=True, color=TEXT)
        ws.cell(5, col).font = Font(name=FONT, size=18, bold=True, color=NAVY)
        ws.cell(6, col).font = Font(name=FONT, size=9, color="667085")
        for r in range(4, 7):
            ws.cell(r, col).alignment = Alignment(horizontal="center", vertical="center")
        add_source_comment(ws.cell(5, col), source)

    ws.cell(8, 1, "核心判断")
    ws.cell(8, 2, "证据")
    ws.cell(8, 5, "管理含义")
    ws.merge_cells("B8:D8")
    ws.merge_cells("E8:H8")
    style_header(ws[8])
    conclusions = [
        ("1. 执行留痕已经很强，但还不是经营闭环", f"任务 {ev['current']['tasks']} 条、证据路径覆盖 100%；线程 {ev['current']['threads']} 条。", "继续扩大任务/线程数量的边际价值下降；优先把商机、合同、交付、使用、回款串成一张事实表。"),
        ("2. 六场景不是六条等量业务线", f"34条记录中政企19、学校7，两者占 {ev['scene']['top_two_share']:.1%}；其余四场景各1条，另4条待归类。", "未来90天资源以政企+学校为主；医疗/社区做付费验证，部队/竞技体育按具名机会推进。"),
        ("3. 当前项目表更像交付/售后资产目录，不是销售漏斗", f"售后20条、交付11条、售前仅3条；售后占 {ev['scene']['stages']['售后阶段']/ev['scene']['records']:.1%}。", "不能用34条项目记录预测签约；需要独立CRM漏斗和机会阶段证据。"),
        ("4. 经营数字存在状态混算风险", "目标、预计、发货签收、签约、确收、回款、储备在会议材料中并存，且部分口径相互矛盾。", "所有金额必须带 amount_type、数据日期和来源；禁止跨状态相加。"),
        ("5. 用户与活跃数据目前不可用于规模结论", f"只读复算得到8451累计订单活跃人次，仅3/24项目非零；会议另报公有云累计2.8万人、日活5000余，定义/范围不同。", "先统一独立用户、日活、订单活跃人次和覆盖项目；对外停止使用“8000/2.8万用户”式混合表达。"),
        ("6. 营养健康方向值得继续，但尚未证明可持续变现", "营养健康问题类24个记录日，学校/政企功能持续推进；仍缺付费、使用留存、复测结果和续费证据。", "产品方向继续；商业结论延长验证，不把模块完成写成价值已证明。"),
    ]
    row = 9
    for title, evidence, meaning in conclusions:
        ws.cell(row, 1, title)
        ws.merge_cells(start_row=row, start_column=2, end_row=row, end_column=4)
        ws.cell(row, 2, evidence)
        ws.merge_cells(start_row=row, start_column=5, end_row=row, end_column=8)
        ws.cell(row, 5, meaning)
        ws.cell(row, 1).font = Font(name=FONT, size=10, bold=True, color=NAVY)
        for c in (1, 2, 5):
            ws.cell(row, c).alignment = Alignment(vertical="top", wrap_text=True)
        ws.row_dimensions[row].height = 52
        row += 1

    row += 1
    ws.cell(row, 1, "我认可的经营决策")
    ws.merge_cells(start_row=row, start_column=1, end_row=row, end_column=8)
    ws.cell(row, 1).fill = PatternFill("solid", fgColor=TEAL)
    ws.cell(row, 1).font = Font(name=FONT, size=12, bold=True, color="FFFFFF")
    row += 1
    decisions = [
        ("SHIP", "政企+学校作为未来90天主战场", "现有项目与问题证据最密集；先做标准包、增购和复制，不平均撒资源。"),
        ("EXTEND", "营养健康专业版的付费与留存验证", "先证明有人持续使用、客户愿付费、能续费，再扩大投入。"),
        ("SHIP", "六场景作为汇报和需求分类法", "用于统一语言和案例归档；不直接作为六条独立产品线或资源比例。"),
        ("STOP", "把1200万/3倍管道当作已确认经营目标", "当前缺真实CRM漏斗、SKU收入、成本、毛利、回款和产能验证。"),
        ("STOP", "用任务/线程/提交数证明人效或业务价值", "这些数字只能证明留痕和活动量，不能证明收入、质量、周期或客户结果。"),
        ("STOP", "对外使用未统一的活跃/用户规模数字", "先完成指标字典、覆盖率和跨日去重。"),
    ]
    for status, decision, reason in decisions:
        ws.cell(row, 1, status)
        ws.cell(row, 2, decision)
        ws.merge_cells(start_row=row, start_column=2, end_row=row, end_column=4)
        ws.cell(row, 5, reason)
        ws.merge_cells(start_row=row, start_column=5, end_row=row, end_column=8)
        color = {"SHIP": PALE_GREEN, "EXTEND": PALE_AMBER, "STOP": PALE_RED}[status]
        ws.cell(row, 1).fill = PatternFill("solid", fgColor=color)
        ws.cell(row, 1).font = Font(name=FONT, size=10, bold=True, color=TEXT)
        for c in (1, 2, 5):
            ws.cell(row, c).alignment = Alignment(vertical="top", wrap_text=True)
        ws.row_dimensions[row].height = 36
        row += 1
    set_widths(ws, {"A": 28, "B": 22, "C": 12, "D": 12, "E": 22, "F": 18, "G": 18, "H": 18})
    ws.print_area = f"A1:H{row - 1}"

    # 2. 经营口径
    ws = wb.create_sheet("经营口径")
    base_sheet(ws, "A4")
    add_title(ws, "经营数字口径审计", "原始会议数字逐项保留，不跨状态混算；百分比只在同一行且分子分母含义明确时计算", 10)
    headers = ["指标", "目标/分母", "已确认/分子", "预计", "进度", "预计完成率", "来源", "可否汇总", "证据", "审计说明"]
    for c, h in enumerate(headers, 1):
        ws.cell(4, c, h)
    style_header(ws[4])
    rows = [
        ["数科业务中心", "", 217, "", "", "", "用户手记", "否", "B", "217万与70%的金额性质/分母未明确，不反推目标"],
        ["健康团餐", 200, "", 50, "", "=IFERROR(D6/B6,\"\")", "手记+会议原文", "否", "B", "50为月底预计；另提54万清单，待商务核对"],
        ["商务月度", 640, 266, 398, "=IFERROR(C7/B7,\"\")", "=IFERROR(D7/B7,\"\")", "用户手记", "否", "B", "266标为确收；260.6为发货签收，不能混用"],
        ["商务发货签收", "", 260.6, "", "", "", "会议原文", "否", "B", "发货签收不是确收或回款"],
        ["8—12月冲刺", 2200, 764, "", "=IFERROR(C9/B9,\"\")", "", "会议原文", "否", "B", "764为中标/签约金额，不是已确认收入"],
        ["8—12月储备", "", 3000, "", "", "", "手记+会议原文", "否", "B", "商机储备不与签约额或收入相加"],
        ["公有云累计使用人数", "", 28000, "", "", "", "会议原文", "否", "C", "不包含政务云；去重、日期和覆盖范围待核"],
        ["公有云日活", "", 5000, "", "", "", "会议原文", "否", "C", "5000余人近似数；不能与8451累计订单活跃人次比较"],
    ]
    for r_idx, values in enumerate(rows, 5):
        for c, value in enumerate(values, 1):
            ws.cell(r_idx, c, value)
        ws.cell(r_idx, 9).fill = PatternFill("solid", fgColor=evidence_level_fill(str(values[8])))
    style_body(ws, 5, 12, 1, 10)
    for row in range(5, 13):
        for col in [2, 3, 4]:
            ws.cell(row, col).number_format = '#,##0.0;[Red](#,##0.0);-'
        for col in [5, 6]:
            ws.cell(row, col).number_format = '0.0%'
    add_table(ws, "FinancialAudit", 4, 12, 10)
    set_widths(ws, {"A": 23, "B": 14, "C": 15, "D": 14, "E": 13, "F": 15, "G": 20, "H": 12, "I": 10, "J": 48})
    ws.print_area = "A1:J12"

    # 3. 六场景组合
    ws = wb.create_sheet("六场景组合")
    base_sheet(ws, "A4")
    add_title(ws, "六场景项目组合", "34条项目记录，不等于34个独立客户/合同；场景用于分类，不能直接推算收入", 11)
    headers = ["场景", "记录数", "占比", "售前", "交付", "售后", "已完成", "进行中", "筹备中", "未启动", "判断"]
    for c, h in enumerate(headers, 1): ws.cell(4, c, h)
    style_header(ws[4])
    scene_order = ["政企", "学校", "医疗", "社区康养", "部队", "竞技体育", "待归类"]
    for idx, scene in enumerate(scene_order, 5):
        scene_rows = [r for r in data["scenes"] if r["scene"] == scene]
        ws.cell(idx, 1, scene)
        ws.cell(idx, 2, len(scene_rows))
        ws.cell(idx, 3, f"=B{idx}/SUM($B$5:$B$11)")
        for col, key in [(4, "售前阶段"), (5, "交付阶段"), (6, "售后阶段")]:
            ws.cell(idx, col, sum(r["stage"] == key for r in scene_rows))
        for col, key in [(7, "已完成"), (8, "进行中"), (9, "筹备中"), (10, "未启动")]:
            ws.cell(idx, col, sum(r["status"] == key for r in scene_rows))
        decision = {
            "政企": "主战场：优先标准包、存量增购和多基地复用",
            "学校": "主战场：先完成学校营养与四域边界验证",
            "医疗": "验证场：只围绕具名医院机会推进",
            "社区康养": "验证场：先证明付费、持续使用和复测闭环",
            "部队": "机会制：部署与安全前提满足后推进",
            "竞技体育": "证据资产：补真实案例与产品化边界",
            "待归类": "先补终端客户，不计场景业绩",
        }[scene]
        ws.cell(idx, 11, decision)
        ws.cell(idx, 3).number_format = "0.0%"
    style_body(ws, 5, 11, 1, 11)
    add_table(ws, "ScenePortfolio", 4, 11, 11)
    set_widths(ws, {"A": 16, "B": 10, "C": 10, "D": 9, "E": 9, "F": 9, "G": 10, "H": 10, "I": 10, "J": 10, "K": 42})
    chart = DoughnutChart()
    chart.title = "六场景记录分布"
    chart.add_data(Reference(ws, min_col=2, min_row=4, max_row=11), titles_from_data=True)
    chart.set_categories(Reference(ws, min_col=1, min_row=5, max_row=11))
    chart.height, chart.width = 7, 11
    ws.add_chart(chart, "A14")
    stage = BarChart()
    stage.type = "col"
    stage.title = "阶段结构"
    stage.y_axis.title = "记录数"
    stage.add_data(Reference(ws, min_col=4, max_col=6, min_row=4, max_row=11), titles_from_data=True)
    stage.set_categories(Reference(ws, min_col=1, min_row=5, max_row=11))
    stage.height, stage.width = 7, 13
    ws.add_chart(stage, "F14")
    ws.print_area = "A1:Q28"

    # 4. 问题投入
    ws = wb.create_sheet("问题投入")
    base_sheet(ws, "A4")
    add_title(ws, "最近30天持续投入问题结构", "主指标为问题记录日，不是人工工时；窗口 2026-08-20 至 2026-09-18", 8)
    headers = ["排名", "问题类别", "记录日", "记录日占比", "去重主题", "关联任务", "代表主题", "解释边界"]
    for c, h in enumerate(headers, 1): ws.cell(4, c, h)
    style_header(ws[4])
    for idx, item in enumerate(ev["problem_stats"], 5):
        ws.cell(idx, 1, item["rank"])
        ws.cell(idx, 2, item["name"])
        ws.cell(idx, 3, item["days"])
        ws.cell(idx, 4, f"=C{idx}/SUM($C$5:$C$16)")
        ws.cell(idx, 5, item["groups"])
        ws.cell(idx, 6, item["tasks"])
        ws.cell(idx, 7, item["examples"])
        ws.cell(idx, 8, "记录频度可看持续负担；不能推断真实工时或个人绩效")
        ws.cell(idx, 4).number_format = "0.0%"
    style_body(ws, 5, 16, 1, 8)
    add_table(ws, "ProblemStructure", 4, 16, 8)
    set_widths(ws, {"A": 9, "B": 28, "C": 11, "D": 13, "E": 12, "F": 12, "G": 36, "H": 44})
    chart = BarChart()
    chart.type = "bar"
    chart.style = 10
    chart.title = "问题记录日排名"
    chart.add_data(Reference(ws, min_col=3, min_row=4, max_row=16), titles_from_data=True)
    chart.set_categories(Reference(ws, min_col=2, min_row=5, max_row=16))
    chart.height, chart.width = 9, 16
    chart.legend = None
    ws.add_chart(chart, "A19")
    ws.print_area = "A1:H35"

    # 5. 能力增长
    ws = wb.create_sheet("能力增长")
    base_sheet(ws, "A4")
    add_title(ws, "控制面与知识资产变化", "2026-07-20 基线对比 2026-09-21；增长表示覆盖/留痕扩张，不等于收入或人效增长", 8)
    headers = ["指标", "7/20基线", "当前", "变化", "变化率", "证据", "可解释为", "不能解释为"]
    for c, h in enumerate(headers, 1): ws.cell(4, c, h)
    style_header(ws[4])
    growth = [
        ("任务索引", "tasks", "A", "项目控制覆盖扩大", "业务产出增长"),
        ("Codex线程", "threads", "A", "历史上下文覆盖扩大", "人效或收入增长"),
        ("全部Skills", "skills", "A", "可复用方法增加", "团队已稳定采用"),
        ("PM Skills", "pm_skills", "A", "PM方法路由增加", "产品成功率提高"),
        ("微盘文件", "wedrive_files", "A", "索引资料增加/范围变化", "新业务材料净增"),
        ("微盘文档", "wedrive_docs", "A", "可解析文档增加", "有效知识被使用"),
        ("010-cpt仓库", "repo_inventory", "A", "代码资产纳管扩大", "仓库健康或业务价值"),
        ("登记业务仓库", "registered_repos", "A", "zhct业务技术边界扩大", "交付复杂度已下降"),
    ]
    for idx, (name, key, level, can, cannot) in enumerate(growth, 5):
        ws.cell(idx, 1, name)
        ws.cell(idx, 2, ev["baseline"][key])
        ws.cell(idx, 3, ev["current"][key])
        ws.cell(idx, 4, f"=C{idx}-B{idx}")
        ws.cell(idx, 5, f"=IFERROR(D{idx}/B{idx},\"\")")
        ws.cell(idx, 6, level)
        ws.cell(idx, 7, can)
        ws.cell(idx, 8, cannot)
        ws.cell(idx, 5).number_format = "0.0%"
        ws.cell(idx, 6).fill = PatternFill("solid", fgColor=evidence_level_fill(level))
    style_body(ws, 5, 12, 1, 8)
    add_table(ws, "CapabilityGrowth", 4, 12, 8)
    set_widths(ws, {"A": 22, "B": 14, "C": 14, "D": 13, "E": 13, "F": 10, "G": 34, "H": 36})
    ws.print_area = "A1:H12"

    # 6. 数据可信度
    ws = wb.create_sheet("数据可信度")
    base_sheet(ws, "A4")
    add_title(ws, "关键数据可信度审计", "同名指标不代表同一口径；优先修复会直接影响经营决策的主键、范围、时间和状态", 9)
    headers = ["主题", "当前数字", "证据", "范围/定义", "主要缺口", "可否决策", "停止表达", "补齐动作", "Owner角色"]
    for c, h in enumerate(headers, 1): ws.cell(4, c, h)
    style_header(ws[4])
    trust_rows = [
        ["任务留痕", f"{ev['current']['tasks']}条；近30天{ev['recent_30d']['tasks']}条", "A", "任务索引；证据路径100%", "状态词不统一；任务不等于独立结果", "可用于追溯，不用于人效", "完成了多少业务价值", "统一结果类型、客户结果和验收状态", "PMO/项目管理"],
        ["项目场景", f"34条；政企19、学校7", "B", "W39营养餐厅相关记录", "非独立项目；4条终端客户不明", "可做场景地图", "六场景规模/收入", "补 customer_id/project_id/contract_id", "销售运营"],
        ["经营金额", "217/200/640/2200/3000等", "B/C", "会议手记与转写", "目标、预计、签约、确收、回款混合", "不可汇总预测", "整体完成率/缺口", "统一amount_type和财务回读", "财务+商务"],
        ["公有云用户", "累计2.8万；日活5000余", "C", "会议近似数；未含政务云", "日期、去重、覆盖项目、事件定义缺失", "不可对外规模结论", "2.8万真实用户/5000 DAU", "用户级匿名事实表与覆盖率", "产品数据"],
        ["订单活跃", f"{ev['active_usage']['cumulative_order_active_visits']}累计人次", "B", f"24项目快照，仅{ev['active_usage']['projects_with_positive_active']}项目非零", "跨日重复；项目覆盖不完整", "可解释来源，不可当用户", "8451个活跃用户", "跨日COUNT DISTINCT并标数据截止", "数据平台"],
        ["产品组合", "3 SPU / 9 SKU", "D", "内部战略评审稿", "缺SKU收入、成本、毛利、交付人天、续费", "可作销售分类", "已验证年度产品战略", "合同按SPU/SKU拆单并核成本", "产品+财务"],
        ["营养成效", "模块/方案持续推进", "B/D", "项目与研发证据", "缺同人基线、使用、复测、付费、续费", "只能继续验证", "已证明健康效果/第二增长曲线", "建立一人一周期研究数据", "产品+研究院"],
        ["问题投入", "185问题记录日/125主题", "B", "Git记录+任务归并", "不是人工工时；分类含判断", "可排治理优先级", "团队工时排名", "统一问题ID和实际投入分钟", "PMO/团队负责人"],
    ]
    for r_idx, values in enumerate(trust_rows, 5):
        for c, value in enumerate(values, 1): ws.cell(r_idx, c, value)
        ws.cell(r_idx, 3).fill = PatternFill("solid", fgColor=evidence_level_fill(str(values[2]).split("/")[0]))
    style_body(ws, 5, 12, 1, 9)
    add_table(ws, "TrustAudit", 4, 12, 9)
    set_widths(ws, {"A": 18, "B": 24, "C": 10, "D": 30, "E": 36, "F": 26, "G": 30, "H": 36, "I": 18})
    ws.print_area = "A1:I12"

    # 7. 决策矩阵
    ws = wb.create_sheet("决策矩阵")
    base_sheet(ws, "A4")
    add_title(ws, "经营假设决策矩阵", "SHIP / EXTEND / STOP 是证据门禁判断，不是随机对照实验的统计显著性结论", 9)
    headers = ["假设/动作", "建议", "信心", "主要证据", "最强反证", "下一证据", "Owner", "截止建议", "通过标准"]
    for c, h in enumerate(headers, 1): ws.cell(4, c, h)
    style_header(ws[4])
    decision_rows = [
        ["未来90天聚焦政企+学校", "SHIP", "中", "两场景占34条记录的76.5%；既有结算/交付/方案证据最多", "记录多可能来自历史包袱，不等于新增长", "各筛3个满足五项商机门槛的具名机会", "销售+产品", "14天", "至少2个正式报价/采购节点"],
        ["六场景作为统一分类法", "SHIP", "高", "W39模板已完成34条映射与软硬件拆分", "医疗/社区/部队/竞技体育样本各1条", "连续4周填报且4条待归类清零", "PMO", "30天", "项目主键完整率100%"],
        ["营养健康专业版扩大投入", "EXTEND", "中低", "营养功能24记录日；学校/政企/社区均有落地动作", "缺付费、留存、复测、续费和健康结果", "1个付费样板+30天使用/服务数据", "产品+研究院", "45天", "使用、交付成本和续费意愿同时过门"],
        ["3 SPU / 9 SKU用于销售组织", "EXTEND", "中", "结构能分清结算/食安/进销存三类预算", "缺合同拆分和真实经济性", "最近20份合同回标SPU/SKU/收入/成本", "产品+财务", "30天", "收入与贡献额可按SKU复算"],
        ["1200万+3倍管道作为承诺目标", "STOP", "高", "战略文件明确标为D级假设；缺当前CRM漏斗与交付产能", "会议称储备约3000万、签约764万，但状态不可混算", "逐机会核买方/预算/负责人/窗口/下一步", "销售+财务", "14天", "漏斗、赢率、回款和产能可回读"],
        ["用活跃数据证明用户规模", "STOP", "高", "8451是跨日累计订单活跃人次且仅3项目非零", "会议另报2.8万用户/5000 DAU，范围不同", "统一事件、身份、日期和项目覆盖", "数据平台", "21天", "独立用户与人次可并列复算"],
        ["继续扩大AI协作与Skill体系", "SHIP", "中", "任务证据覆盖100%；264 skills；历史上下文可恢复", "数量增长不等于周期、质量或业务收益", "抽样10个同类任务比较周期/返工/缺陷前置", "AI工程化", "30天", "至少一项效率或质量指标改善"],
    ]
    for r_idx, values in enumerate(decision_rows, 5):
        for c, value in enumerate(values, 1): ws.cell(r_idx, c, value)
        fill = {"SHIP": PALE_GREEN, "EXTEND": PALE_AMBER, "STOP": PALE_RED}[values[1]]
        ws.cell(r_idx, 2).fill = PatternFill("solid", fgColor=fill)
        ws.cell(r_idx, 2).font = Font(name=FONT, size=10, bold=True, color=TEXT)
    style_body(ws, 5, 11, 1, 9)
    add_table(ws, "DecisionMatrix", 4, 11, 9)
    set_widths(ws, {"A": 30, "B": 12, "C": 12, "D": 42, "E": 42, "F": 38, "G": 18, "H": 14, "I": 36})
    ws.print_area = "A1:I11"

    # 8. 指标字典
    ws = wb.create_sheet("指标字典")
    base_sheet(ws, "A4")
    add_title(ws, "建议最小经营事实表", "不先建大平台；先用一张可回读事实表串联客户、机会、合同、项目、SKU、交付、使用和回款", 8)
    headers = ["字段", "对象", "定义", "是否必填", "产生系统", "更新频率", "校验规则", "用途"]
    for c, h in enumerate(headers, 1): ws.cell(4, c, h)
    style_header(ws[4])
    dictionary = [
        ["customer_id", "客户", "终端付费客户唯一ID；渠道公司与终端客户分开", "是", "CRM/项目台账", "变更时", "名称去重且可追溯", "防止一客多名"],
        ["opportunity_id", "商机", "一次独立采购机会唯一ID", "是", "CRM", "每周", "五项商机门槛完整", "真实销售漏斗"],
        ["contract_id", "合同", "正式合同唯一ID；未签为空", "条件必填", "合同/财务", "签约时", "金额/税率/付款节点一致", "签约与回款"],
        ["project_id", "交付项目", "一个可验收交付范围唯一ID", "是", "项目台账", "立项时", "不以方案记录代替项目", "交付状态"],
        ["scene", "场景", "政企/学校/医疗/社区康养/部队/竞技体育", "是", "项目台账", "立项时", "终端客户不明则待归类", "场景组合"],
        ["spu / sku", "产品", "合同中实际销售的SPU和SKU订单线", "是", "报价/合同", "报价/变更", "与版本/BOM对应", "产品收入与复购"],
        ["amount_type", "金额", "目标/储备/报价/签约/发货/确收/回款", "是", "销售/财务", "每周", "不同类型不得相加", "经营口径"],
        ["amount / data_date", "金额", "同税口径金额和数据截止日", "是", "销售/财务", "每周", "来源单据可回读", "目标与实际"],
        ["direct_cost", "经济性", "硬件、第三方、实施、差旅、渠道、售后准备成本", "是", "财务/交付", "里程碑", "与合同同口径", "贡献额/现金暴露"],
        ["delivery_hours", "交付", "按项目和问题ID记录的实际投入小时", "是", "项目工时", "每周", "等待时间单列", "产能与标准化"],
        ["active_unique_users", "使用", "周期内按项目+匿名用户跨日去重", "条件必填", "数据平台", "每日", "与人次并列，不混用", "使用规模"],
        ["usage_event", "使用", "关键功能事件及成功/失败状态", "条件必填", "产品埋点", "实时/每日", "事件版本与功能版本一致", "留存/漏斗/实验"],
        ["accepted_at / collected_at", "结果", "验收与回款实际日期", "条件必填", "项目/财务", "发生时", "有单据/确认人", "闭环判断"],
        ["evidence_path", "证据", "支持该事实的系统记录或文件路径", "是", "控制项目", "每次变更", "可读取且时间一致", "审计与复盘"],
    ]
    for r_idx, values in enumerate(dictionary, 5):
        for c, value in enumerate(values, 1): ws.cell(r_idx, c, value)
    style_body(ws, 5, 18, 1, 8)
    add_table(ws, "MetricDictionary", 4, 18, 8)
    set_widths(ws, {"A": 25, "B": 15, "C": 46, "D": 13, "E": 22, "F": 13, "G": 38, "H": 25})
    ws.print_area = "A1:H18"

    # 9. 场景项目明细
    ws = wb.create_sheet("场景项目明细")
    base_sheet(ws, "A2", 0)
    scene_headers = ["name", "scene", "group", "type", "stage", "status", "owner", "software", "hardware", "gap", "evidence"]
    for c, h in enumerate(scene_headers, 1): ws.cell(1, c, h)
    style_header(ws[1])
    for r_idx, item in enumerate(data["scenes"], 2):
        for c, key in enumerate(scene_headers, 1): ws.cell(r_idx, c, item.get(key, ""))
    style_body(ws, 2, len(data["scenes"]) + 1, 1, len(scene_headers))
    add_table(ws, "SceneDetails", 1, len(data["scenes"]) + 1, len(scene_headers))
    set_widths(ws, {"A": 35, "B": 14, "C": 18, "D": 16, "E": 14, "F": 13, "G": 22, "H": 52, "I": 45, "J": 48, "K": 35})

    # 10. 问题主题明细
    ws = wb.create_sheet("问题主题明细")
    base_sheet(ws, "A2", 0)
    headers = ["group_id", "category", "name", "task_count", "record_days", "days", "task_ids"]
    for c, h in enumerate(headers, 1): ws.cell(1, c, h)
    style_header(ws[1])
    for r_idx, item in enumerate(data["problem_groups"], 2):
        values = [item["group_id"], item["category"], item["name"], len(item["task_ids"]), item["record_days"], ", ".join(item["days"]), ", ".join(item["task_ids"])]
        for c, value in enumerate(values, 1): ws.cell(r_idx, c, value)
    style_body(ws, 2, len(data["problem_groups"]) + 1, 1, len(headers))
    add_table(ws, "ProblemGroups", 1, len(data["problem_groups"]) + 1, len(headers))
    set_widths(ws, {"A": 12, "B": 12, "C": 38, "D": 12, "E": 12, "F": 38, "G": 95})

    # 11. 近30天任务
    ws = wb.create_sheet("近30天任务")
    base_sheet(ws, "A2", 0)
    task_headers = ["updated_at", "task_id", "title", "status", "evidence_path", "skills", "tags"]
    for c, h in enumerate(task_headers, 1): ws.cell(1, c, h)
    style_header(ws[1])
    for r_idx, item in enumerate(sorted(data["recent_tasks"], key=lambda x: x["updated_at"], reverse=True), 2):
        for c, key in enumerate(task_headers, 1): ws.cell(r_idx, c, item.get(key, ""))
    style_body(ws, 2, len(data["recent_tasks"]) + 1, 1, len(task_headers))
    add_table(ws, "RecentTasks", 1, len(data["recent_tasks"]) + 1, len(task_headers))
    set_widths(ws, {"A": 23, "B": 40, "C": 46, "D": 50, "E": 60, "F": 70, "G": 45})

    # 12. 来源审计
    ws = wb.create_sheet("来源审计")
    base_sheet(ws, "A2", 0)
    sources = [
        ["任务索引", "A", "2026-09-21", "control/task-index/tasks.tsv", "全量", "726条；近30天186条；证据路径100%", "状态词不统一，任务不等于独立业务结果"],
        ["Codex线程归档", "A", "2026-09-21", ev["thread_index"], "全量", f"{ev['current']['threads']}条；近30天{ev['recent_30d']['threads']}条", "线程数不等于人效；包含控制/恢复类任务"],
        ["微盘知识索引", "A", "2026-09-10", "work_company_knowledge/reports/summary.md", "元数据全量", "61880文件；7825文档", "不等于正文已读或资料有效"],
        ["30天问题排名", "B", "2026-09-18", "work/2026-09-18-workspace-problem-ranking/summary.md", "任务窗口", "200项→125主题→12类；185记录日", "分类/归并含分析判断；非工时"],
        ["W39六场景项目表", "B", "2026-09-21", "work/weekly-reports/2026-W39/direction-template/output/projects-classified.json", "筛选34条", "30条映射六场景；4条待归类", "记录不等于独立客户/合同"],
        ["W39经营口径", "B/C", "2026-09-21", "work/weekly-reports/2026-W39/direction-template/output/financial.json", "会议材料", "保留目标/预计/签约/确收等原口径", "未经CRM/财务总账回读"],
        ["活跃数据复算", "B", "2026-09-05", "work_store/data-queries/active-user-breakdown-20260905/analysis.md", "24项目快照", "8451累计订单活跃人次；3项目非零", "跨日重复、覆盖不全、非独立用户"],
        ["3SPU/9SKU战略", "D", "2026-09-02", "work/2026-09-02-smart-canteen-3-spu-9-sku-strategy/SMART_CANTEEN_3_SPU_9_SKU_STRATEGY.md", "内部假设", "产品结构与年度情景", "缺真实漏斗、收入、成本、毛利和产能"],
        ["7/20系统基线", "A", "2026-07-20", "work/2026-07-20-connected-systems-full-data/system-snapshot.csv", "当日快照", "任务/线程/微盘/Skill/仓库基线", "只用于控制面变化，不代表业务增长"],
    ]
    headers = ["来源", "证据等级", "数据日期", "路径", "覆盖范围", "可支持", "不能支持"]
    for c, h in enumerate(headers, 1): ws.cell(1, c, h)
    style_header(ws[1])
    for r_idx, values in enumerate(sources, 2):
        for c, value in enumerate(values, 1): ws.cell(r_idx, c, value)
        ws.cell(r_idx, 2).fill = PatternFill("solid", fgColor=evidence_level_fill(str(values[1]).split("/")[0]))
    style_body(ws, 2, len(sources) + 1, 1, 7)
    add_table(ws, "SourceAudit", 1, len(sources) + 1, 7)
    set_widths(ws, {"A": 25, "B": 12, "C": 15, "D": 72, "E": 24, "F": 42, "G": 50})

    # Workbook-level normalization.
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if cell.value is not None and cell.font.name != FONT:
                    cell.font = Font(
                        name=FONT,
                        size=cell.font.sz or 10,
                        bold=cell.font.bold,
                        italic=cell.font.italic,
                        color=cell.font.color,
                    )
        ws.auto_filter.ref = ws.auto_filter.ref

    wb.calculation.fullCalcOnLoad = True
    wb.calculation.forceFullCalc = True
    OUTPUT.mkdir(parents=True, exist_ok=True)
    wb.save(XLSX)


def write_outputs(data: dict) -> None:
    ev = data["evidence"]
    source_rows = [
        ["任务索引", "A", "2026-09-21", "control/task-index/tasks.tsv", "全量", "活动与追溯", "不等于人效/业务结果"],
        ["Codex线程归档", "A", "2026-09-21", ev["thread_index"], "全量", "上下文规模", "不等于产出"],
        ["微盘知识索引", "A", "2026-09-10", "work_company_knowledge/reports/summary.md", "元数据全量", "资料规模", "不等于正文质量"],
        ["30天问题排名", "B", "2026-09-18", "work/2026-09-18-workspace-problem-ranking/summary.md", "200项", "治理优先级", "不等于工时"],
        ["W39六场景项目表", "B", "2026-09-21", "work/weekly-reports/2026-W39/direction-template/output/projects-classified.json", "34条", "场景结构", "不等于独立客户/合同"],
        ["W39经营口径", "B/C", "2026-09-21", "work/weekly-reports/2026-W39/direction-template/output/financial.json", "会议材料", "口径审计", "不能跨状态汇总"],
        ["活跃数据复算", "B", "2026-09-05", "work_store/data-queries/active-user-breakdown-20260905/analysis.md", "24项目", "解释8451来源", "不等于独立用户"],
        ["3SPU/9SKU战略", "D", "2026-09-02", "work/2026-09-02-smart-canteen-3-spu-9-sku-strategy/SMART_CANTEEN_3_SPU_9_SKU_STRATEGY.md", "内部假设", "产品结构", "不是已验证经营目标"],
        ["7/20系统基线", "A", "2026-07-20", "work/2026-07-20-connected-systems-full-data/system-snapshot.csv", "快照", "控制面变化", "不等于业务增长"],
    ]
    write_csv(SOURCE_AUDIT, ["来源", "证据等级", "数据日期", "路径", "覆盖", "可支持", "不能支持"], source_rows)

    decision_rows = [
        ["政企+学校作为90天主战场", "SHIP", "中", "两场景占34条记录76.5%", "14天内各筛3个合格机会"],
        ["六场景作为分类法", "SHIP", "高", "W39已映射30/34条", "连续4周使用并清零待归类"],
        ["营养健康专业版扩大投入", "EXTEND", "中低", "功能投入高但缺付费/留存/复测", "1个付费样板+30天行为数据"],
        ["3SPU/9SKU销售组织", "EXTEND", "中", "结构清楚但缺合同经济性", "20份合同回标SKU/收入/成本"],
        ["1200万+3倍管道作为承诺", "STOP", "高", "D级假设且缺真实CRM", "逐机会补五项门槛"],
        ["用活跃数据证明用户规模", "STOP", "高", "8451为人次且覆盖3/24项目", "统一用户/事件/时间/覆盖"],
        ["继续扩大AI协作体系", "SHIP", "中", "追溯强但价值未量化", "抽样10任务比较周期/返工/缺陷"],
    ]
    write_csv(DECISIONS, ["假设", "建议", "信心", "依据", "下一证据"], decision_rows)

    md = f"""# 康比特数科证据驱动数据分析

- 数据截止：2026-09-21
- 历史基线：2026-07-20
- 主方法：证据金字塔 + 系统约束分析 + 经营漏斗审计
- Excel：`outputs/2026-09-21-evidence-backed-data-analysis/康比特数科证据驱动数据分析_20260921.xlsx`

## 一句话结论

康比特数科已经形成高强度的 AI 执行、任务留痕和知识资产化能力，但还没有形成同等强度的经营数据闭环；下一阶段最重要的不是继续增加任务、Skill、报告或场景数量，而是用同一组主键把客户、商机、合同、项目、SKU、交付成本、真实使用、验收和回款串起来。

## 我认可的六个判断

1. **执行留痕强，但不是经营闭环。** 当前任务索引 {ev['current']['tasks']} 条，近30天更新 {ev['recent_30d']['tasks']} 条且证据路径覆盖100%；线程归档 {ev['current']['threads']} 条。它证明可追溯性，不证明收入、人效或客户价值。
2. **六场景不应平均投入。** 34条项目记录中政企19条、学校7条，合计占 {ev['scene']['top_two_share']:.1%}；医疗、社区康养、部队、竞技体育各1条，另有4条待归类。未来90天主战场应是政企+学校。
3. **当前项目表不是销售漏斗。** 34条中售后20条、交付11条、售前仅3条。它更适合做交付资产和案例地图，不能直接用于签约预测。
4. **经营数字最大问题是状态混算。** 目标、预计、签约、发货签收、确收、回款和储备必须分别管理；当前材料不足以形成可信的事业部完成率或收入预测。
5. **用户规模还不能下结论。** 8451是跨日累计订单活跃人次，而且24个项目中只有3个项目该字段非零；会议材料中的公有云累计2.8万人和日活5000余人属于另一范围与口径，二者不可比较。
6. **营养健康值得继续，但只能判定为 EXTEND。** 近30天营养健康类有24个问题记录日，功能和方案都在持续推进；仍缺付费、留存、同人复测、续费和交付成本证据，不能写成差异化已变现或第二增长曲线已成立。

## SHIP / EXTEND / STOP

- **SHIP**：政企+学校作为未来90天主战场；六场景作为统一分类和汇报框架；AI协作体系继续内部使用。
- **EXTEND**：营养健康专业版付费与留存验证；3 SPU / 9 SKU作为销售组织结构，等待合同与经济性回标。
- **STOP**：把1200万元和3倍管道写成已确认承诺；用任务/线程/提交数证明人效；用8451、2.8万、5000等未统一口径数字对外证明用户规模；把34条项目记录当34个独立客户或合同。

## 未来30天最小闭环

1. 建一张最小经营事实表，至少包含 `customer_id / opportunity_id / contract_id / project_id / scene / SPU / SKU / amount_type / amount / data_date / direct_cost / delivery_hours / active_unique_users / accepted_at / collected_at / evidence_path`。
2. 用五项门槛清理商机：业务问题、付费主体和预算、关键负责人、采购窗口、客户确认的下一步日期。没有五项，不进入预测。
3. 从政企、学校各选3个具名机会；社区/医疗只保留1个付费验证；部队/竞技体育按具名机会推进，不预留平均资源。
4. 为营养专业版建立一个付费样板，至少跟踪30天使用、服务次数、同人复测、交付成本和续费意愿。
5. 修正用户指标：独立用户、人次、订单用户、登录DAU分别定义，并展示项目覆盖率、数据截止日和失败项目数。

## 证据边界

- 任务、Git和本地索引可证明记录存在；分类、管理判断和资源建议仍含分析判断。
- W39经营数字来自会议手记/转写，未经过CRM和财务总账统一回读。
- 3 SPU / 9 SKU和1200万元情景是D级内部假设，不是已验证事实。
- SHIP / EXTEND / STOP是经营假设门禁，不是随机对照实验统计结论；当前没有可运行 `/analyze-test` 的随机实验事件表。
"""
    ANALYSIS_MD.write_text(md, encoding="utf-8")


def verify() -> dict:
    evidence = read_json(SNAPSHOT)
    wb = load_workbook(XLSX, data_only=False)
    formula_errors = []
    for ws in wb.worksheets:
        for row in ws.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value in {"#REF!", "#DIV/0!", "#VALUE!", "#NAME?", "#N/A"}:
                    formula_errors.append(f"{ws.title}!{cell.coordinate}:{cell.value}")
    expected = ["结论总览", "经营口径", "六场景组合", "问题投入", "能力增长", "数据可信度", "决策矩阵", "指标字典", "场景项目明细", "问题主题明细", "近30天任务", "来源审计"]
    assert wb.sheetnames == expected, wb.sheetnames
    assert not formula_errors, formula_errors
    assert wb["场景项目明细"].max_row == 35
    assert wb["问题主题明细"].max_row == 126
    assert wb["近30天任务"].max_row == int(evidence["recent_30d"]["tasks"]) + 1
    result = {
        "xlsx": str(XLSX),
        "bytes": XLSX.stat().st_size,
        "sheets": len(wb.sheetnames),
        "sheet_names": wb.sheetnames,
        "formula_literal_errors": formula_errors,
        "scene_detail_rows": wb["场景项目明细"].max_row - 1,
        "problem_group_rows": wb["问题主题明细"].max_row - 1,
        "recent_task_rows": wb["近30天任务"].max_row - 1,
    }
    (WORK / "validation.json").write_text(json.dumps(result, ensure_ascii=False, indent=2) + "\n", encoding="utf-8")
    return result


def main() -> None:
    WORK.mkdir(parents=True, exist_ok=True)
    OUTPUT.mkdir(parents=True, exist_ok=True)
    data = collect()
    write_outputs(data)
    make_workbook(data)
    result = verify()
    print(json.dumps(result, ensure_ascii=False, indent=2))


if __name__ == "__main__":
    main()