#!/usr/bin/env python3
"""Build the user-facing Excel workbook from CSV truth sources via OOXML."""

from __future__ import annotations

import argparse
import csv
import shutil
import subprocess
import tempfile
from dataclasses import dataclass
from pathlib import Path
from xml.etree import ElementTree as ET
from xml.sax.saxutils import escape


MAIN_NS = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
DOC_REL_NS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
PKG_REL_NS = "http://schemas.openxmlformats.org/package/2006/relationships"
CONTENT_NS = "http://schemas.openxmlformats.org/package/2006/content-types"
ET.register_namespace("", MAIN_NS)
ET.register_namespace("r", DOC_REL_NS)

ROOT = Path(__file__).resolve().parents[1]
SKILL_DIR = Path("/Users/jack/.codex/skills/minimax-xlsx")
TEMPLATE = SKILL_DIR / "templates/minimal_xlsx"


@dataclass(frozen=True)
class Formula:
    text: str
    style: int


@dataclass
class SheetSpec:
    name: str
    title: str
    subtitle: str
    headers: list[str]
    rows: list[list[str | Formula]]
    widths: list[float]


class StringTable:
    def __init__(self) -> None:
        self.values: list[str] = []
        self.index: dict[str, int] = {}
        self.references = 0

    def add(self, value: object) -> int:
        text = "" if value is None else str(value)
        self.references += 1
        if text not in self.index:
            self.index[text] = len(self.values)
            self.values.append(text)
        return self.index[text]


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


def col_letter(number: int) -> str:
    result = ""
    while number:
        number, remainder = divmod(number - 1, 26)
        result = chr(65 + remainder) + result
    return result


def q(value: str) -> str:
    return escape(value, {'"': """})


def append_font(fonts: ET.Element, *, name: str = "Microsoft YaHei", size: str = "10.5", bold: bool = False, color: str = "00000000", underline: bool = False) -> int:
    index = len(list(fonts))
    font = ET.SubElement(fonts, f"{{{MAIN_NS}}}font")
    if bold:
        ET.SubElement(font, f"{{{MAIN_NS}}}b")
    if underline:
        ET.SubElement(font, f"{{{MAIN_NS}}}u")
    ET.SubElement(font, f"{{{MAIN_NS}}}sz", {"val": size})
    ET.SubElement(font, f"{{{MAIN_NS}}}name", {"val": name})
    ET.SubElement(font, f"{{{MAIN_NS}}}color", {"rgb": color})
    return index


def append_fill(fills: ET.Element, color: str) -> int:
    index = len(list(fills))
    fill = ET.SubElement(fills, f"{{{MAIN_NS}}}fill")
    pattern = ET.SubElement(fill, f"{{{MAIN_NS}}}patternFill", {"patternType": "solid"})
    ET.SubElement(pattern, f"{{{MAIN_NS}}}fgColor", {"rgb": color})
    ET.SubElement(pattern, f"{{{MAIN_NS}}}bgColor", {"indexed": "64"})
    return index


def append_border(borders: ET.Element) -> int:
    index = len(list(borders))
    border = ET.SubElement(borders, f"{{{MAIN_NS}}}border")
    for side in ("left", "right", "top", "bottom"):
        node = ET.SubElement(border, f"{{{MAIN_NS}}}{side}", {"style": "thin"})
        ET.SubElement(node, f"{{{MAIN_NS}}}color", {"rgb": "00D9E2F3"})
    ET.SubElement(border, f"{{{MAIN_NS}}}diagonal")
    return index


def append_xf(cell_xfs: ET.Element, *, font_id: int, fill_id: int, border_id: int, align: str = "left", vertical: str = "top", wrap: bool = True, num_fmt_id: int = 0) -> int:
    index = len(list(cell_xfs))
    attrs = {
        "numFmtId": str(num_fmt_id), "fontId": str(font_id), "fillId": str(fill_id),
        "borderId": str(border_id), "xfId": "0", "applyFont": "1", "applyFill": "1",
        "applyBorder": "1", "applyAlignment": "1",
    }
    if num_fmt_id:
        attrs["applyNumberFormat"] = "1"
    xf = ET.SubElement(cell_xfs, f"{{{MAIN_NS}}}xf", attrs)
    align_attrs = {"horizontal": align, "vertical": vertical}
    if wrap:
        align_attrs["wrapText"] = "1"
    ET.SubElement(xf, f"{{{MAIN_NS}}}alignment", align_attrs)
    return index


def add_styles(workdir: Path) -> dict[str, int]:
    path = workdir / "xl/styles.xml"
    tree = ET.parse(path)
    root = tree.getroot()
    fonts = root.find(f"{{{MAIN_NS}}}fonts")
    fills = root.find(f"{{{MAIN_NS}}}fills")
    borders = root.find(f"{{{MAIN_NS}}}borders")
    cell_xfs = root.find(f"{{{MAIN_NS}}}cellXfs")
    assert fonts is not None and fills is not None and borders is not None and cell_xfs is not None

    body_font = append_font(fonts)
    title_font = append_font(fonts, size="16", bold=True, color="00FFFFFF")
    section_font = append_font(fonts, size="11", bold=True, color="00FFFFFF")
    header_font = append_font(fonts, size="10.5", bold=True, color="00FFFFFF")
    strong_font = append_font(fonts, bold=True, color="001F4E78")
    green_font = append_font(fonts, bold=True, color="00375E26")
    amber_font = append_font(fonts, bold=True, color="009C6500")
    red_font = append_font(fonts, bold=True, color="009C0006")
    gray_font = append_font(fonts, color="00666666")

    dark_blue = append_fill(fills, "0017365D")
    medium_blue = append_fill(fills, "002F75B5")
    orange = append_fill(fills, "00ED7D31")
    light_blue = append_fill(fills, "00D9EAF7")
    green = append_fill(fills, "00E2F0D9")
    amber = append_fill(fills, "00FFF2CC")
    red = append_fill(fills, "00FCE4D6")
    gray = append_fill(fills, "00E7E6E6")
    white = append_fill(fills, "00FFFFFF")
    border = append_border(borders)

    styles = {
        "title": append_xf(cell_xfs, font_id=title_font, fill_id=dark_blue, border_id=border, vertical="center"),
        "section": append_xf(cell_xfs, font_id=section_font, fill_id=medium_blue, border_id=border, vertical="center"),
        "header": append_xf(cell_xfs, font_id=header_font, fill_id=orange, border_id=border, align="center", vertical="center"),
        "body": append_xf(cell_xfs, font_id=body_font, fill_id=white, border_id=border),
        "body_center": append_xf(cell_xfs, font_id=body_font, fill_id=white, border_id=border, align="center", vertical="center"),
        "strong": append_xf(cell_xfs, font_id=strong_font, fill_id=light_blue, border_id=border),
        "has": append_xf(cell_xfs, font_id=green_font, fill_id=green, border_id=border, align="center", vertical="center"),
        "partial": append_xf(cell_xfs, font_id=amber_font, fill_id=amber, border_id=border, align="center", vertical="center"),
        "none": append_xf(cell_xfs, font_id=red_font, fill_id=red, border_id=border, align="center", vertical="center"),
        "pending": append_xf(cell_xfs, font_id=gray_font, fill_id=gray, border_id=border, align="center", vertical="center"),
        "na": append_xf(cell_xfs, font_id=gray_font, fill_id=white, border_id=border, align="center", vertical="center"),
        "formula_int": append_xf(cell_xfs, font_id=body_font, fill_id=white, border_id=border, align="center", vertical="center", num_fmt_id=167),
        "formula_pct": append_xf(cell_xfs, font_id=body_font, fill_id=white, border_id=border, align="center", vertical="center", num_fmt_id=165),
        "note": append_xf(cell_xfs, font_id=amber_font, fill_id=amber, border_id=border),
    }

    for node in (fonts, fills, borders, cell_xfs):
        node.attrib["count"] = str(len(list(node)))
    tree.write(path, encoding="UTF-8", xml_declaration=True)
    return styles


def style_for(value: str | Formula, column: int, styles: dict[str, int]) -> int:
    if isinstance(value, Formula):
        return value.style
    if value == "有":
        return styles["has"]
    if value == "部分具备":
        return styles["partial"]
    if value == "暂无":
        return styles["none"]
    if value == "待核实":
        return styles["pending"]
    if value == "不适用":
        return styles["na"]
    return styles["strong"] if column in (1, 2) else styles["body"]


def worksheet_xml(spec: SheetSpec, strings: StringTable, styles: dict[str, int]) -> str:
    last_col = col_letter(len(spec.headers))
    last_row = len(spec.rows) + 3
    chunks = [
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
        f'<worksheet xmlns="{MAIN_NS}" xmlns:r="{DOC_REL_NS}">',
        f'<dimension ref="A1:{last_col}{last_row}"/>',
        '<sheetViews><sheetView tabSelected="0" workbookViewId="0"><pane ySplit="3" topLeftCell="A4" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>',
        '<sheetFormatPr defaultRowHeight="28"/>',
        '<cols>',
    ]
    for index, width in enumerate(spec.widths, start=1):
        chunks.append(f'<col min="{index}" max="{index}" width="{width}" customWidth="1"/>')
    chunks.extend(['</cols>', '<sheetData>'])

    title_index = strings.add(spec.title)
    chunks.append(f'<row r="1" ht="30" customHeight="1"><c r="A1" t="s" s="{styles["title"]}"><v>{title_index}</v></c></row>')
    subtitle_index = strings.add(spec.subtitle)
    chunks.append(f'<row r="2" ht="36" customHeight="1"><c r="A2" t="s" s="{styles["section"]}"><v>{subtitle_index}</v></c></row>')
    chunks.append('<row r="3" ht="42" customHeight="1">')
    for col, header in enumerate(spec.headers, start=1):
        idx = strings.add(header)
        chunks.append(f'<c r="{col_letter(col)}3" t="s" s="{styles["header"]}"><v>{idx}</v></c>')
    chunks.append('</row>')

    for row_number, row in enumerate(spec.rows, start=4):
        chunks.append(f'<row r="{row_number}" ht="42" customHeight="1">')
        for col, value in enumerate(row, start=1):
            ref = f"{col_letter(col)}{row_number}"
            style = style_for(value, col, styles)
            if isinstance(value, Formula):
                chunks.append(f'<c r="{ref}" s="{value.style}"><f>{escape(value.text)}</f><v></v></c>')
            else:
                idx = strings.add(value)
                chunks.append(f'<c r="{ref}" t="s" s="{style}"><v>{idx}</v></c>')
        chunks.append('</row>')
    chunks.extend([
        '</sheetData>',
        f'<autoFilter ref="A3:{last_col}{last_row}"/>',
        f'<mergeCells count="2"><mergeCell ref="A1:{last_col}1"/><mergeCell ref="A2:{last_col}2"/></mergeCells>',
        '<pageMargins left="0.25" right="0.25" top="0.5" bottom="0.5" header="0.2" footer="0.2"/>',
        '<pageSetup orientation="landscape" fitToWidth="1" fitToHeight="0"/>',
        '</worksheet>',
    ])
    return "".join(chunks)


def summary_spec(catalog_rows: int, matrix_headers: list[str], matrix_rows: int, styles: dict[str, int]) -> SheetSpec:
    domains = ["前厅", "后厨", "食安", "营养健康", "经营管理", "平台与集成", "交付运维", "智能硬件"]
    competitors = matrix_headers[7:-1]
    rows: list[list[str | Formula]] = []
    rows.append(["口径说明", "功能点先定义，再填厂商；空白不等于没有，默认状态为待核实。", "", "", "", "", ""])
    rows.append(["状态词", "有", "部分具备", "暂无", "待核实", "不适用", ""])
    rows.append(["证据等级", "A=合同/中标/验收/真实系统", "B=内部表格/项目材料", "C=公开资料", "D=结构推导", "", ""])
    rows.append(["注意", "原表为2025-11/12基线；厂商状态需按当前版本逐项复核。", "", "", "", "", ""])
    rows.append(["", "", "", "", "", "", ""])
    rows.append(["业务域", "功能点总数", "P0", "P1", "说明", "", ""])
    for domain in domains:
        rows.append([
            domain,
            Formula(f'COUNTIF(\'功能明细\'!B4:B{catalog_rows + 3},"{domain}")', styles["formula_int"]),
            Formula(f'COUNTIFS(\'功能明细\'!B4:B{catalog_rows + 3},"{domain}",\'功能明细\'!N4:N{catalog_rows + 3},"P0")', styles["formula_int"]),
            Formula(f'COUNTIFS(\'功能明细\'!B4:B{catalog_rows + 3},"{domain}",\'功能明细\'!N4:N{catalog_rows + 3},"P1")', styles["formula_int"]),
            "P0/P1是标准版建议优先级，不代表当前已完成。", "", "",
        ])
    rows.append(["合计", Formula(f"COUNTA('功能明细'!A4:A{catalog_rows + 3})", styles["formula_int"]), "", "", "", "", ""])
    rows.append(["", "", "", "", "", "", ""])
    rows.append(["厂商", "有", "部分具备", "暂无", "待核实", "已确认覆盖", "非待核实率"])
    matrix_last = matrix_rows + 3
    for row_index, competitor in enumerate(competitors, start=len(rows) + 4):
        column = col_letter(matrix_headers.index(competitor) + 1)
        rows.append([
            competitor,
            Formula(f'COUNTIF(\'竞品矩阵\'!{column}4:{column}{matrix_last},"有")', styles["formula_int"]),
            Formula(f'COUNTIF(\'竞品矩阵\'!{column}4:{column}{matrix_last},"部分具备")', styles["formula_int"]),
            Formula(f'COUNTIF(\'竞品矩阵\'!{column}4:{column}{matrix_last},"暂无")', styles["formula_int"]),
            Formula(f'COUNTIF(\'竞品矩阵\'!{column}4:{column}{matrix_last},"待核实")', styles["formula_int"]),
            Formula(f"B{row_index}+C{row_index}", styles["formula_int"]),
            Formula(f"IFERROR((B{row_index}+C{row_index}+D{row_index})/(B{row_index}+C{row_index}+D{row_index}+E{row_index}),0)", styles["formula_pct"]),
        ])
    return SheetSpec(
        name="说明与统计",
        title="智慧营养健康餐厅竞品产品功能明细分析",
        subtitle="981项能力候选｜前厅、后厨、食安、营养健康、经营、平台、交付、硬件统一口径｜状态必须有证据",
        headers=["项目", "值1", "值2", "值3", "值4", "值5", "值6"],
        rows=rows,
        widths=[20, 24, 18, 18, 36, 18, 18],
    )


def dict_rows(rows: list[dict[str, str]], headers: list[str]) -> list[list[str]]:
    return [[row.get(header, "") for header in headers] for row in rows]


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("output", type=Path)
    args = parser.parse_args()
    args.output.parent.mkdir(parents=True, exist_ok=True)

    catalog_headers, catalog = read_csv(ROOT / "capability-catalog.csv")
    matrix_headers, matrix = read_csv(ROOT / "competitor-matrix.csv")
    evidence_headers, evidence = read_csv(ROOT / "evidence-ledger.csv")
    backlog_headers, backlog = read_csv(ROOT / "validation-backlog.csv")

    with tempfile.TemporaryDirectory(prefix="cpt-competitor-xlsx-") as temporary:
        workdir = Path(temporary) / "xlsx"
        shutil.copytree(TEMPLATE, workdir)
        styles = add_styles(workdir)

        specs: list[SheetSpec] = [summary_spec(len(catalog), matrix_headers, len(matrix), styles)]
        specs.append(SheetSpec("功能明细", "智慧营养健康餐厅产品功能明细库", "每项含角色、触点、输入、规则、输出与验收观察点；可作为PRD、竞品补证和研发优先级底表。", catalog_headers, dict_rows(catalog, catalog_headers), [14, 12, 20, 22, 24, 42, 26, 28, 34, 40, 34, 46, 28, 14, 16, 20, 12, 28, 34]))
        specs.append(SheetSpec("竞品矩阵", "康比特与11家友商统一功能矩阵", "仅使用有/部分具备/暂无/待核实/不适用；原表空白不等于暂无。", matrix_headers, dict_rows(matrix, matrix_headers), [14, 12, 20, 22, 25, 14, 16] + [16] * (len(matrix_headers) - 8) + [28]))

        domain_headers = ["capability_id", "业务域", "一级模块", "二级模块", "功能点", "功能定义", "主要用户角色", "使用端/触点", "核心业务规则", "验收观察点", "客户价值", "标准版优先级", "能力性质", "候选来源"]
        domain_groups = [
            ("前厅", ["前厅"]), ("后厨", ["后厨"]), ("食安", ["食安"]), ("营养健康", ["营养健康"]),
            ("经营管理", ["经营管理"]), ("平台集成", ["平台与集成"]), ("交付运维", ["交付运维"]), ("智能硬件", ["智能硬件"]),
        ]
        for sheet_name, domains in domain_groups:
            subset = [row for row in catalog if row["业务域"] in domains]
            specs.append(SheetSpec(sheet_name, f"{sheet_name}产品功能明细", f"共{len(subset)}项；按一级模块、二级模块和功能点筛选。", domain_headers, dict_rows(subset, domain_headers), [14, 12, 20, 22, 26, 42, 26, 28, 40, 46, 28, 14, 16, 20]))

        specs.append(SheetSpec("证据台账", "竞品功能状态证据台账", "B级原表状态与C级公开汇编线索分开保留；stale/unknown必须继续复核。", evidence_headers, dict_rows(evidence, evidence_headers), [14, 14, 12, 28, 18, 14, 28, 12, 12, 40, 12, 14, 14, 52]))
        specs.append(SheetSpec("待补证清单", "11家友商逐功能补证清单", "每条待核实都给出建议证据和通过条件；先补P0，再补P1。", backlog_headers, dict_rows(backlog, backlog_headers), [14, 12, 20, 22, 26, 20, 14, 42, 52, 14]))

        strings = StringTable()
        worksheets = workdir / "xl/worksheets"
        for existing in worksheets.glob("sheet*.xml"):
            existing.unlink()
        for index, spec in enumerate(specs, start=1):
            (worksheets / f"sheet{index}.xml").write_text(worksheet_xml(spec, strings, styles), encoding="utf-8")

        shared = [
            '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>',
            f'<sst xmlns="{MAIN_NS}" count="{strings.references}" uniqueCount="{len(strings.values)}">',
        ]
        for value in strings.values:
            preserve = value.startswith(" ") or value.endswith(" ") or "\n" in value
            attr = ' xml:space="preserve"' if preserve else ""
            shared.append(f"<si><t{attr}>{escape(value)}</t></si>")
        shared.append("</sst>")
        (workdir / "xl/sharedStrings.xml").write_text("".join(shared), encoding="utf-8")

        workbook_path = workdir / "xl/workbook.xml"
        workbook_tree = ET.parse(workbook_path)
        workbook_root = workbook_tree.getroot()
        sheets_node = workbook_root.find(f"{{{MAIN_NS}}}sheets")
        assert sheets_node is not None
        for child in list(sheets_node):
            sheets_node.remove(child)
        for index, spec in enumerate(specs, start=1):
            rel_id = "rId1" if index == 1 else f"rId{index + 2}"
            ET.SubElement(sheets_node, f"{{{MAIN_NS}}}sheet", {"name": spec.name, "sheetId": str(index), f"{{{DOC_REL_NS}}}id": rel_id})
        workbook_tree.write(workbook_path, encoding="UTF-8", xml_declaration=True)

        rels_path = workdir / "xl/_rels/workbook.xml.rels"
        rels_tree = ET.parse(rels_path)
        rels_root = rels_tree.getroot()
        for node in list(rels_root):
            if node.attrib.get("Type", "").endswith("/worksheet"):
                rels_root.remove(node)
        for index in range(1, len(specs) + 1):
            rel_id = "rId1" if index == 1 else f"rId{index + 2}"
            ET.SubElement(rels_root, f"{{{PKG_REL_NS}}}Relationship", {
                "Id": rel_id,
                "Type": "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet",
                "Target": f"worksheets/sheet{index}.xml",
            })
        rels_tree.write(rels_path, encoding="UTF-8", xml_declaration=True)

        content_path = workdir / "[Content_Types].xml"
        content_tree = ET.parse(content_path)
        content_root = content_tree.getroot()
        for node in list(content_root):
            if node.attrib.get("PartName", "").startswith("/xl/worksheets/"):
                content_root.remove(node)
        for index in range(1, len(specs) + 1):
            ET.SubElement(content_root, f"{{{CONTENT_NS}}}Override", {
                "PartName": f"/xl/worksheets/sheet{index}.xml",
                "ContentType": "application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml",
            })
        content_tree.write(content_path, encoding="UTF-8", xml_declaration=True)

        subprocess.run([
            "python3", str(SKILL_DIR / "scripts/xlsx_pack.py"), str(workdir), str(args.output)
        ], check=True)
        print(f"output={args.output}")
        print(f"sheets={len(specs)} catalog_rows={len(catalog)} matrix_rows={len(matrix)} evidence_rows={len(evidence)} backlog_rows={len(backlog)}")


if __name__ == "__main__":
    main()