#!/usr/bin/env python3
"""Append the Coze/Running format compatibility result to the Excel report."""

from __future__ import annotations

import csv
from pathlib import Path

from openpyxl import load_workbook
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter


ROOT = Path(__file__).resolve().parents[1]
REPORTS = ROOT / "reports"
CONVERTED = ROOT / "coze" / "converted"
EXCEL_REPORT = REPORTS / "coze-migration-test-results.xlsx"
NODE_MAPPING = CONVERTED / "node-mapping.csv"
EDGE_MAPPING = CONVERTED / "edge-mapping.csv"
PACKAGE = CONVERTED / "Workflow-running_ai_assistant_5_compat-draft-0001.zip"
REPORT = CONVERTED / "format-compatibility-report.md"
COZE_SAMPLE = ROOT / "coze" / "sample-export" / "Workflow-dish_recognition_more_simple-draft-5725.zip"
COZE_WORKFLOW_URL = "https://www.coze.cn/work_flow?workflow_id=7657510397302816794&space_id=7366210605697007616"
RUN_SUCCESS_SCREENSHOT = "evidence/screenshots/coze-import-run-success-20260701.png"
PAYWALL_SCREENSHOT = "evidence/screenshots/coze-import-paywall-20260701.png"
BUSINESS_PENDING_NOTE = (
    "兼容包已能导入并执行包装节点，但输出只是 NOT_DIFY_BUSINESS_OUTPUT 占位信号；"
    "尚未重绑 Coze 原生 LLM、知识库、HTTP、条件、变量和代码节点，不能算业务语义一比一测试通过。"
)
COMPAT_OUTPUT = (
    "NOT_DIFY_BUSINESS_OUTPUT: this Coze workflow is only an import skeleton. "
    "Rebind runtime nodes from node-mapping.csv before business testing."
)


def count_rows(path: Path) -> int:
    with path.open("r", encoding="utf-8-sig", newline="") as handle:
        return sum(1 for _ in csv.DictReader(handle))


def style_sheet(ws) -> None:
    fill = PatternFill("solid", fgColor="1F4E78")
    font = Font(color="FFFFFF", bold=True)
    for cell in ws[1]:
        cell.fill = fill
        cell.font = font
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions
    for row in ws.iter_rows(min_row=2):
        for cell in row:
            cell.alignment = Alignment(vertical="top", wrap_text=True)
    for col in range(1, ws.max_column + 1):
        letter = get_column_letter(col)
        width = 12
        for cell in ws[letter]:
            width = min(90, max(width, len(str(cell.value or "")[:90]) + 2))
        ws.column_dimensions[letter].width = width


def upsert_summary_rows(wb) -> None:
    ws = wb["Summary"]
    updates = {
        "Coze平台导入/执行结果": "PENDING_REBIND",
        "Coze阻塞说明": BUSINESS_PENDING_NOTE,
        "Coze兼容包导入结果": "PASS",
        "Coze兼容包装试运行": "PASS_COMPAT_WRAPPER_ONLY",
        "Coze业务语义测试结果": "PENDING_REBIND",
        "Coze业务语义测试说明": BUSINESS_PENDING_NOTE,
        "证据截图": RUN_SUCCESS_SCREENSHOT,
        "Coze工作流URL": COZE_WORKFLOW_URL,
        "Coze导入工作流名称": "running_ai_assistant_5_compat",
        "Coze占位输出信号": COMPAT_OUTPUT,
        "本地动态单元测试总数": 1969,
        "迁移契约测试": 1831,
        "格式兼容单元测试": 138,
    }
    existing = {}
    for row in range(1, ws.max_row + 1):
        key = ws.cell(row=row, column=1).value
        if key:
            existing[str(key)] = row
    for key, value in updates.items():
        if key in existing:
            ws.cell(row=existing[key], column=2, value=value)
        else:
            ws.append([key, value])
    style_sheet(ws)


def replace_coze_import_evidence(wb) -> None:
    if "Coze Import Evidence" in wb.sheetnames:
        del wb["Coze Import Evidence"]
    ws = wb.create_sheet("Coze Import Evidence", 2)
    ws.append(["step", "status", "observed", "evidence"])
    rows = [
        (
            "历史权限验证",
            "HISTORICAL_BLOCKED",
            "早期点击 Coze 资源库-工作流-导入后出现导入/导出升级页，未出现文件选择器。",
            PAYWALL_SCREENSHOT,
        ),
        (
            "上传原 Running/Dify 包",
            "FORMAT_INCOMPATIBLE",
            "原包缺少 Coze 必需的 MANIFEST.yml 与 workflow/*.yaml，不能作为 Coze 原生工作流导入。",
            "/Users/jack/Downloads/dify-running-ai-assistant-coze-upload-candidate.zip",
        ),
        (
            "上传 Coze 兼容包",
            "PASS",
            "Coze 页面通知显示“全部导入完成”，画布创建 running_ai_assistant_5_compat。",
            str(PACKAGE),
        ),
        (
            "兼容包装试运行",
            "PASS_COMPAT_WRAPPER_ONLY",
            "输入 5 公里配速训练问题和 user_id 后，页面显示“运行完成 2s / 0 Tokens”，但输出只是兼容包装提示文本。",
            RUN_SUCCESS_SCREENSHOT,
        ),
        (
            "试运行输出变量",
            "PASS_COMPAT_WRAPPER_ONLY",
            f"输出 {COMPAT_OUTPUT}，只能证明导入骨架的包装节点执行链路成立。",
            COZE_WORKFLOW_URL,
        ),
        (
            "业务语义一比一运行",
            "PENDING_REBIND",
            BUSINESS_PENDING_NOTE,
            str(REPORT),
        ),
    ]
    for row in rows:
        ws.append(row)
    style_sheet(ws)


def main() -> None:
    wb = load_workbook(EXCEL_REPORT)
    upsert_summary_rows(wb)
    replace_coze_import_evidence(wb)
    if "Format Compatibility" in wb.sheetnames:
        del wb["Format Compatibility"]
    ws = wb.create_sheet("Format Compatibility", 1)
    ws.append(["item", "status", "observed_result", "evidence"])
    rows = [
        (
            "Coze 原生样本包",
            "PASS",
            "包含根目录、MANIFEST.yml、workflow/*.yaml。",
            str(COZE_SAMPLE),
        ),
        (
            "Running/Dify 原候选包",
            "FORMAT_INCOMPATIBLE",
            "只有 dify-export-no-secret.yml、workflow-draft.json、workflow-normalized.json，缺少 Coze 清单和 workflow DSL。",
            "/Users/jack/Downloads/dify-running-ai-assistant-coze-upload-candidate.zip",
        ),
        (
            "转换后的 Coze 兼容包",
            "PASS",
            "已生成 Coze 原生 Zip 结构，并已在 Coze 平台导入成功；业务运行节点需按映射文件重绑。",
            str(PACKAGE),
        ),
        (
            "Coze 平台导入",
            "PASS",
            "导入后创建 running_ai_assistant_5_compat，页面通知显示“全部导入完成”。",
            COZE_WORKFLOW_URL,
        ),
        (
            "Coze 兼容包装试运行",
            "PASS_COMPAT_WRAPPER_ONLY",
            "页面显示“运行完成 2s / 0 Tokens”，输出兼容包装提示文本；这不是业务语义测试通过。",
            RUN_SUCCESS_SCREENSHOT,
        ),
        (
            "Coze 业务语义测试",
            "PENDING_REBIND",
            BUSINESS_PENDING_NOTE,
            str(REPORT),
        ),
        (
            "节点映射",
            "PASS",
            f"{count_rows(NODE_MAPPING)} 个 Dify 节点已映射到 Coze 兼容节点。",
            str(NODE_MAPPING),
        ),
        (
            "连线映射",
            "PASS",
            f"{count_rows(EDGE_MAPPING)} 条 Dify 连线已映射，分支 handle 保留在 CSV 中。",
            str(EDGE_MAPPING),
        ),
        (
            "人工重绑清单",
            "PASS",
            "模型、知识库、HTTP 环境变量、会话变量、条件分支、Python 代码均已列为重绑项。",
            str(REPORT),
        ),
    ]
    for row in rows:
        ws.append(row)
    style_sheet(ws)
    wb.save(EXCEL_REPORT)
    print(EXCEL_REPORT)


if __name__ == "__main__":
    main()