import re
from pathlib import Path

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


ROOT = Path(__file__).resolve().parents[1]
SOURCE = ROOT / "deliverables" / "cafa-meal-flow-alternative-exception-inventory.md"
OUTPUT = ROOT / "deliverables" / "央美项目分支异常UseCase清单_20260904.xlsx"
BLUE = "1F4E78"
LIGHT_BLUE = "D9EAF7"
LIGHT_ORANGE = "FCE4D6"
LIGHT_RED = "F4CCCC"
LIGHT_GREEN = "E2F0D9"
WHITE = "FFFFFF"
THIN = Side(style="thin", color="B7B7B7")


def section(text, title):
    match = re.search(rf"^## {re.escape(title)}\s*$([\s\S]*?)(?=^## |\Z)", text, re.M)
    if not match:
        raise ValueError(f"missing section: {title}")
    return match.group(1)


def table(text, title):
    lines = [line.strip() for line in section(text, title).splitlines() if line.strip().startswith("|")]
    if len(lines) < 3:
        raise ValueError(f"missing table: {title}")
    rows = [[cell.strip().replace("`", "") for cell in line.strip("|").split("|")] for line in lines]
    return [rows[0]] + rows[2:]


def numbered(text, title):
    rows = []
    for line in section(text, title).splitlines():
        match = re.match(r"^(\d+)\.\s+(.*)$", line.strip())
        if match:
            rows.append([int(match.group(1)), match.group(2)])
    return [["序号", "测试场景"]] + rows


def add_sheet(wb, name, rows):
    ws = wb.create_sheet(name)
    for row in rows:
        ws.append(row)
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions
    ws.sheet_view.showGridLines = False
    for cell in ws[1]:
        cell.fill = PatternFill("solid", fgColor=BLUE)
        cell.font = Font(name="Microsoft YaHei", size=11, bold=True, color=WHITE)
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    for row in ws.iter_rows():
        for cell in row:
            cell.border = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
            if cell.row > 1:
                cell.font = Font(name="Microsoft YaHei", size=10)
                cell.alignment = Alignment(vertical="top", wrap_text=True)
                value = str(cell.value or "")
                if value.startswith("A-"):
                    cell.fill = PatternFill("solid", fgColor=LIGHT_ORANGE)
                elif value.startswith("E-"):
                    cell.fill = PatternFill("solid", fgColor=LIGHT_RED)
                elif value.startswith("UC-"):
                    cell.fill = PatternFill("solid", fgColor=LIGHT_GREEN)
    ws.row_dimensions[1].height = 30
    for col in range(1, ws.max_column + 1):
        longest = max(len(str(ws.cell(row, col).value or "")) for row in range(1, min(ws.max_row, 80) + 1))
        ws.column_dimensions[get_column_letter(col)].width = min(max(longest * 1.35, 12), 60)
    return ws


text = SOURCE.read_text(encoding="utf-8")
wb = Workbook()
wb.remove(wb.active)
intro = [
    ["字段", "内容"],
    ["当前事实", "当前没有卡、码、脸能力；卡码脸/NFC仅为采购目标，本轮不作为可执行分支。"],
    ["事实源", "央美项目交付差异点.xlsx；SHA256 9f646323a04dfe1253314440b277baccafce318fdf5f33aa7863fc7f4854ad2e"],
    ["附件结构", "1个Sheet、4个标的、11条差异"],
    ["当前范围", "人员同步、餐盘绑定、称重、余额阈值、扣费、解绑、离线补传、对账和设备运维"],
    ["DoR", "BLOCKED：当前人员/账户识别入口及多项P0规则未冻结"],
    ["清单结构", "Use Case地图、主流程、分支流程、异常恢复、业务规则、页面接口数据、首轮测试、待确认项"],
]
add_sheet(wb, "00-说明", intro)
add_sheet(wb, "01-UseCase地图", table(text, "2. 系统 Use Case Map"))
add_sheet(wb, "02-主流程", table(text, "3. 主流程基线"))
add_sheet(wb, "03-分支流程", table(text, "4. 分支流程"))
add_sheet(wb, "04-异常恢复", table(text, "5. 异常与恢复流程"))
add_sheet(wb, "05-业务规则", table(text, "6. 业务规则"))
add_sheet(wb, "06-页面接口数据", table(text, "7. 页面、接口、数据与可观测性"))
add_sheet(wb, "07-首轮测试", numbered(text, "8. 第一轮必须执行的 15 条测试"))
add_sheet(wb, "08-待确认项", table(text, "9. P0 待确认项"))
wb.save(OUTPUT)

check = load_workbook(OUTPUT, data_only=False)
if check.sheetnames != ["00-说明", "01-UseCase地图", "02-主流程", "03-分支流程", "04-异常恢复", "05-业务规则", "06-页面接口数据", "07-首轮测试", "08-待确认项"]:
    raise SystemExit("unexpected sheet structure")
print(OUTPUT)