#!/usr/bin/env python3
"""Generate business-friendly HTML and Excel deliverables.

Inputs are the processed CSV/JSON artifacts created by analyze_bid_data.py.
Outputs are a standalone visual HTML report and a multi-sheet .xlsx workbook.
The workbook is written as OOXML directly rather than through openpyxl.
"""

from __future__ import annotations

import argparse
import csv
import html
import json
import math
import shutil
import zipfile
from pathlib import Path
from typing import Any


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


SHEETS = [
    ("导读", None),
    ("原始清洗数据", "cleaned_awards.csv"),
    ("月度趋势", "monthly_summary.csv"),
    ("省份分析", "province_summary.csv"),
    ("城市分析", "city_summary.csv"),
    ("客户Top20", "client_top20.csv"),
    ("供应商次数Top20", "supplier_top20.csv"),
    ("供应商金额Top20", "supplier_amount_top20.csv"),
    ("需求类型", "demand_summary.csv"),
    ("客户类型", "client_type_summary.csv"),
    ("商机Top30", "opportunity_top30.csv"),
    ("风险红旗", "risk_flags.csv"),
]


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


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


def parse_float(value: Any) -> float | None:
    if value is None:
        return None
    text = str(value).strip().replace(",", "")
    if not text:
        return None
    try:
        return float(text)
    except ValueError:
        return None


def yuan(value: Any) -> str:
    number = parse_float(value)
    if number is None:
        return "未披露"
    if abs(number) >= 100000000:
        return f"{number / 100000000:.2f}亿元"
    if abs(number) >= 10000:
        return f"{number / 10000:.2f}万元"
    return f"{number:,.0f}元"


def pct(value: float) -> str:
    return f"{value * 100:.1f}%"


def sanitize_visible(text: Any) -> str:
    return str(text if text is not None else "").replace("—", "-").replace("–", "-")


def xml_escape(text: Any) -> str:
    text = sanitize_visible(text)
    return (
        text.replace("&", "&")
        .replace("<", "&lt;")
        .replace(">", "&gt;")
        .replace('"', "&quot;")
    )


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


def cell_xml(ref: str, value: Any, style: int = 0, formula: str | None = None) -> str:
    if formula is not None:
        return f'<c r="{ref}" s="{style}"><f>{xml_escape(formula)}</f><v></v></c>'
    if value is None or value == "":
        return f'<c r="{ref}" s="{style}"/>'
    if isinstance(value, bool):
        return f'<c r="{ref}" t="b" s="{style}"><v>{1 if value else 0}</v></c>'
    number = parse_float(value)
    if number is not None and str(value).strip() not in {"", "--"}:
        return f'<c r="{ref}" s="{style}"><v>{number}</v></c>'
    return f'<c r="{ref}" t="inlineStr" s="{style}"><is><t>{xml_escape(value)}</t></is></c>'


def row_xml(row_num: int, values: list[Any], styles: list[int] | None = None, formulas: dict[int, str] | None = None) -> str:
    styles = styles or [0] * len(values)
    formulas = formulas or {}
    cells = []
    for idx, value in enumerate(values, start=1):
        ref = f"{col_letter(idx)}{row_num}"
        cells.append(cell_xml(ref, value, styles[idx - 1] if idx - 1 < len(styles) else 0, formulas.get(idx)))
    return f'<row r="{row_num}">' + "".join(cells) + "</row>"


def style_for_column(header: str) -> int:
    if "金额" in header or header in {"最大金额", "平均金额", "中位数金额", "P90金额"}:
        return 10
    if "项目数" in header or "记录号" in header or "排名" in header or "合同工期" in header:
        return 10
    if "分" in header and header not in {"省份"}:
        return 10
    return 0


def sheet_xml_from_table(name: str, headers: list[str], rows: list[dict[str, str]]) -> str:
    widths = []
    for i, header in enumerate(headers, start=1):
        if header in {"项目名称", "行动建议", "复核原因", "官网查看地址"}:
            width = 48
        elif header in {"招标单位", "中标单位"}:
            width = 34
        else:
            width = min(max(len(header) + 8, 12), 24)
        widths.append(f'<col min="{i}" max="{i}" width="{width}" customWidth="1"/>')

    data_rows = [row_xml(1, headers, [4] * len(headers))]
    for row_idx, row in enumerate(rows, start=2):
        values = [sanitize_visible(row.get(header, "")) for header in headers]
        styles = [style_for_column(header) for header in headers]
        data_rows.append(row_xml(row_idx, values, styles))

    end_col = col_letter(len(headers))
    end_row = max(len(rows) + 1, 1)
    return f"""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
  xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <dimension ref="A1:{end_col}{end_row}"/>
  <sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>
  <sheetFormatPr defaultRowHeight="18"/>
  <cols>{''.join(widths)}</cols>
  <sheetData>{''.join(data_rows)}</sheetData>
  <autoFilter ref="A1:{end_col}{end_row}"/>
  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>
"""


def intro_sheet_xml(raw_rows: int, risk_rows: int) -> str:
    last_raw = raw_rows + 1
    last_risk = risk_rows + 1
    rows = [
        row_xml(1, ["2026年1-6月乙方宝智慧食堂中标分析工作簿"], [4]),
        row_xml(3, ["使用说明", "本工作簿面向业务复核使用。导读页为公式摘要，其余 sheet 为原始清洗数据和分析报表。"], [4, 0]),
        row_xml(5, ["核心指标", "数值", "口径"], [4, 4, 4]),
        row_xml(6, ["记录数", "", "原始清洗数据项目名称非空行数"], [0, 10, 0], {2: f"COUNTA('原始清洗数据'!B2:B{last_raw})"}),
        row_xml(7, ["披露金额记录数", "", "金额数值可计算记录"], [0, 10, 0], {2: f"COUNT('原始清洗数据'!T2:T{last_raw})"}),
        row_xml(8, ["披露金额合计", "", "单位为元"], [0, 10, 0], {2: f"SUM('原始清洗数据'!T2:T{last_raw})"}),
        row_xml(9, ["金额中位数", "", "单位为元"], [0, 10, 0], {2: f"MEDIAN('原始清洗数据'!T2:T{last_raw})"}),
        row_xml(10, ["金额P90近似", "", "按披露金额 Top 10% 位置近似"], [0, 10, 0], {2: f"LARGE('原始清洗数据'!T2:T{last_raw},ROUNDUP(COUNT('原始清洗数据'!T2:T{last_raw})*0.1,0))"}),
        row_xml(11, ["最大金额", "", "单位为元"], [0, 10, 0], {2: f"MAX('原始清洗数据'!T2:T{last_raw})"}),
        row_xml(12, ["红色风险项", "", "风险红旗表中等级为红的条目"], [0, 10, 0], {2: f'COUNTIF(\'风险红旗\'!B2:B{last_risk},"红")'}),
        row_xml(14, ["Sheet", "内容"], [4, 4]),
    ]
    for idx, (sheet_name, file_name) in enumerate(SHEETS[1:], start=15):
        rows.append(row_xml(idx, [sheet_name, "原始清洗数据" if file_name == "cleaned_awards.csv" else "分析报表"], [0, 0]))
    end_row = len(rows)

    return f"""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
  xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <dimension ref="A1:C{end_row}"/>
  <sheetViews><sheetView tabSelected="1" workbookViewId="0"/></sheetViews>
  <sheetFormatPr defaultRowHeight="18"/>
  <cols>
    <col min="1" max="1" width="24" customWidth="1"/>
    <col min="2" max="2" width="22" customWidth="1"/>
    <col min="3" max="3" width="44" customWidth="1"/>
  </cols>
  <sheetData>{''.join(rows)}</sheetData>
  <pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>
"""


def write_workbook(processed_dir: Path, output: Path) -> None:
    work_dir = Path("/tmp/bid-analysis-xlsx-work")
    if work_dir.exists():
        shutil.rmtree(work_dir)
    shutil.copytree(XLSX_TEMPLATE, work_dir)

    tables: dict[str, tuple[list[str], list[dict[str, str]]]] = {}
    for sheet_name, file_name in SHEETS:
        if file_name:
            tables[sheet_name] = read_csv_with_header(processed_dir / file_name)

    raw_rows = len(tables["原始清洗数据"][1])
    risk_rows = len(tables["风险红旗"][1])

    workbook_sheets = []
    relationships = [
        '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>',
        '<Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>',
        '<Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" Target="sharedStrings.xml"/>',
    ]
    overrides = [
        '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>',
        '<Default Extension="xml" ContentType="application/xml"/>',
        '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>',
        '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>',
        '<Override PartName="/xl/sharedStrings.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"/>',
    ]

    worksheets_dir = work_dir / "xl/worksheets"
    worksheets_dir.mkdir(parents=True, exist_ok=True)
    for idx, (sheet_name, file_name) in enumerate(SHEETS, start=1):
        rid = "rId1" if idx == 1 else f"rId{idx + 2}"
        workbook_sheets.append(f'<sheet name="{xml_escape(sheet_name)}" sheetId="{idx}" r:id="{rid}"/>')
        if idx > 1:
            relationships.append(
                f'<Relationship Id="{rid}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet{idx}.xml"/>'
            )
        overrides.append(
            f'<Override PartName="/xl/worksheets/sheet{idx}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>'
        )
        if idx == 1:
            xml = intro_sheet_xml(raw_rows, risk_rows)
        else:
            headers, rows = tables[sheet_name]
            xml = sheet_xml_from_table(sheet_name, headers, rows)
        (worksheets_dir / f"sheet{idx}.xml").write_text(xml, encoding="utf-8")

    (work_dir / "xl/workbook.xml").write_text(
        f"""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
  xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
  <fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
  <workbookPr defaultThemeVersion="166925"/>
  <bookViews><workbookView xWindow="0" yWindow="0" windowWidth="20140" windowHeight="10960"/></bookViews>
  <sheets>{''.join(workbook_sheets)}</sheets>
  <calcPr calcId="191029" fullCalcOnLoad="1"/>
</workbook>
""",
        encoding="utf-8",
    )
    (work_dir / "xl/_rels/workbook.xml.rels").write_text(
        f"""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
  {''.join(relationships)}
</Relationships>
""",
        encoding="utf-8",
    )
    (work_dir / "[Content_Types].xml").write_text(
        f"""<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">
  {''.join(overrides)}
</Types>
""",
        encoding="utf-8",
    )
    (work_dir / "xl/sharedStrings.xml").write_text(
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?><sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="0" uniqueCount="0"/>',
        encoding="utf-8",
    )
    (work_dir / "xl/styles.xml").write_text(simple_styles_xml(), encoding="utf-8")

    output.parent.mkdir(parents=True, exist_ok=True)
    if output.exists():
        output.unlink()
    with zipfile.ZipFile(output, "w", zipfile.ZIP_DEFLATED) as zf:
        for path in sorted(work_dir.rglob("*")):
            if path.is_file():
                zf.write(path, path.relative_to(work_dir).as_posix())


def simple_styles_xml() -> str:
    """Return a conservative styles.xml compatible with Excel and openpyxl.

    The style indexes intentionally preserve the template's common slots:
    0 default, 4 header, 10 integer/number, so existing sheet generation code
    can stay readable.
    """
    return """<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="3">
    <font><sz val="11"/><name val="Calibri"/></font>
    <font><b/><sz val="11"/><name val="Calibri"/><color rgb="00000000"/></font>
    <font><sz val="11"/><name val="Calibri"/><color rgb="00000000"/></font>
  </fonts>
  <fills count="3">
    <fill><patternFill patternType="none"/></fill>
    <fill><patternFill patternType="gray125"/></fill>
    <fill><patternFill patternType="solid"><fgColor rgb="00EDE9DD"/><bgColor indexed="64"/></patternFill></fill>
  </fills>
  <borders count="1">
    <border><left/><right/><top/><bottom/><diagonal/></border>
  </borders>
  <cellStyleXfs count="1">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
  </cellStyleXfs>
  <cellXfs count="13">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0" applyFont="1"/>
    <xf numFmtId="0" fontId="2" fillId="0" borderId="0" xfId="0" applyFont="1"/>
    <xf numFmtId="0" fontId="1" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1"/>
    <xf numFmtId="3" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="3" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="10" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="10" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="3" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="3" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="1" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>
    <xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0" applyFill="1"/>
  </cellXfs>
  <cellStyles count="1">
    <cellStyle name="Normal" xfId="0" builtinId="0"/>
  </cellStyles>
</styleSheet>
"""


def bar_chart(items: list[tuple[str, float]], *, title: str, unit: str = "") -> str:
    width, height = 860, 300
    left, right, top, bottom = 110, 34, 34, 42
    max_value = max([v for _, v in items] or [1])
    bar_h = (height - top - bottom) / max(len(items), 1)
    rows = [f'<text x="{left}" y="22" class="chart-title">{html.escape(title)}</text>']
    for idx, (label, value) in enumerate(items):
        y = top + idx * bar_h + 5
        w = (width - left - right) * value / max_value if max_value else 0
        rows.append(f'<text x="0" y="{y + 15:.1f}" class="axis-label">{html.escape(label[:12])}</text>')
        rows.append(f'<rect x="{left}" y="{y:.1f}" width="{w:.1f}" height="{max(bar_h - 10, 8):.1f}" rx="6" class="bar"/>')
        rows.append(f'<text x="{left + w + 8:.1f}" y="{y + 15:.1f}" class="value-label">{value:,.0f}{unit}</text>')
    return f'<svg class="chart" viewBox="0 0 {width} {height}" role="img" aria-label="{html.escape(title)}">{"".join(rows)}</svg>'


def line_chart(items: list[tuple[str, float]], *, title: str) -> str:
    width, height = 860, 300
    left, right, top, bottom = 54, 28, 38, 48
    values = [v for _, v in items]
    max_value = max(values or [1])
    min_value = min(values or [0])
    span = max(max_value - min_value, 1)
    points = []
    for idx, (_, value) in enumerate(items):
        x = left + idx * ((width - left - right) / max(len(items) - 1, 1))
        y = top + (max_value - value) / span * (height - top - bottom)
        points.append((x, y))
    poly = " ".join(f"{x:.1f},{y:.1f}" for x, y in points)
    labels = []
    for (label, value), (x, y) in zip(items, points):
        labels.append(f'<circle cx="{x:.1f}" cy="{y:.1f}" r="5" class="dot"/>')
        labels.append(f'<text x="{x:.1f}" y="{height - 20}" text-anchor="middle" class="axis-label">{html.escape(label[5:])}</text>')
        labels.append(f'<text x="{x:.1f}" y="{y - 10:.1f}" text-anchor="middle" class="value-label">{yuan(value)}</text>')
    return f'<svg class="chart" viewBox="0 0 {width} {height}" role="img" aria-label="{html.escape(title)}"><text x="{left}" y="24" class="chart-title">{html.escape(title)}</text><polyline points="{poly}" class="line"/>{ "".join(labels) }</svg>'


def table_html(headers: list[str], rows: list[list[Any]], limit: int = 8) -> str:
    body = []
    for row in rows[:limit]:
        body.append("<tr>" + "".join(f"<td>{html.escape(sanitize_visible(cell))}</td>" for cell in row) + "</tr>")
    return (
        "<table><thead><tr>"
        + "".join(f"<th>{html.escape(header)}</th>" for header in headers)
        + "</tr></thead><tbody>"
        + "".join(body)
        + "</tbody></table>"
    )


def generate_html(processed_dir: Path, output: Path, workbook_name: str) -> None:
    summary = json.loads((processed_dir / "analysis_summary.json").read_text(encoding="utf-8"))
    monthly = read_csv(processed_dir / "monthly_summary.csv")
    province = read_csv(processed_dir / "province_summary.csv")
    demand = read_csv(processed_dir / "demand_summary.csv")
    clients = read_csv(processed_dir / "client_top20.csv")
    suppliers_amount = read_csv(processed_dir / "supplier_amount_top20.csv")
    opps = read_csv(processed_dir / "opportunity_top30.csv")
    risks = read_csv(processed_dir / "risk_flags.csv")

    top_month_amount = [(r["月份"], parse_float(r["金额合计"]) or 0) for r in monthly]
    top_province_count = [(r["发布省份"], parse_float(r["项目数"]) or 0) for r in province[:10]]
    demand_amount = [(r["需求类型"], (parse_float(r["金额合计"]) or 0) / 10000) for r in demand]
    risk_counter: dict[str, int] = {}
    for row in risks:
        risk_counter[row["触发规则"]] = risk_counter.get(row["触发规则"], 0) + 1
    top_risks = sorted(risk_counter.items(), key=lambda item: item[1], reverse=True)[:8]

    max_amount = parse_float(summary["max_amount"]) or 0
    total = parse_float(summary["amount_total"]) or 1
    max_share = max_amount / total if total else 0

    opp_rows = [
        [idx + 1, row["总分"], row["项目名称"], f"{row['省份']}/{row['城市']}", yuan(row["金额"]), row["招标单位"]]
        for idx, row in enumerate(opps[:10])
    ]
    client_rows = [
        [row["招标单位"], row["项目数"], yuan(row["金额合计"]), yuan(row["中位数金额"])]
        for row in clients[:10]
    ]
    supplier_rows = [
        [row["中标单位"], row["项目数"], yuan(row["金额合计"]), yuan(row["中位数金额"])]
        for row in suppliers_amount[:10]
    ]

    css = """
:root{--bg:#f4f2ed;--ink:#17201c;--muted:#66706a;--line:#d8d2c6;--panel:#fffdf8;--accent:#0f766e;--accent2:#b45309;--soft:#e4f2ee;--danger:#b91c1c}
*{box-sizing:border-box}body{margin:0;background:var(--bg);color:var(--ink);font-family:ui-sans-serif,system-ui,-apple-system,BlinkMacSystemFont,"Segoe UI",sans-serif;line-height:1.58}a{color:inherit}.wrap{max-width:1180px;margin:0 auto;padding:0 24px}.hero{min-height:88vh;display:grid;grid-template-columns:1.05fr .95fr;gap:42px;align-items:center;padding:64px 24px 42px}.kicker{font-size:13px;letter-spacing:.08em;color:var(--accent);font-weight:700}.hero h1{font-size:clamp(42px,6vw,76px);line-height:1.02;margin:18px 0 22px;letter-spacing:0}.hero p{max-width:680px;color:var(--muted);font-size:18px}.hero-panel{background:var(--panel);border:1px solid var(--line);border-radius:18px;padding:22px;box-shadow:0 24px 80px rgba(47,41,31,.12)}.metric-grid{display:grid;grid-template-columns:repeat(2,1fr);gap:12px}.metric{background:#f8f6ef;border:1px solid var(--line);border-radius:14px;padding:16px}.metric b{font-size:28px;display:block}.metric span{color:var(--muted);font-size:13px}.cta{display:flex;gap:12px;flex-wrap:wrap;margin-top:28px}.btn{display:inline-flex;align-items:center;justify-content:center;border-radius:999px;padding:11px 18px;text-decoration:none;font-weight:700;border:1px solid var(--ink)}.btn.primary{background:var(--ink);color:#fff}.btn.secondary{background:transparent}.section{padding:62px 0}.section h2{font-size:34px;line-height:1.16;margin:0 0 12px}.section p.lead{color:var(--muted);max-width:760px;margin:0 0 28px}.grid-3{display:grid;grid-template-columns:repeat(3,1fr);gap:16px}.card{background:var(--panel);border:1px solid var(--line);border-radius:18px;padding:20px}.card h3{margin:0 0 10px;font-size:18px}.big-number{font-size:36px;font-weight:800}.chart-wrap{background:var(--panel);border:1px solid var(--line);border-radius:18px;padding:18px;margin:16px 0;overflow:auto}.chart{width:100%;min-width:760px}.bar{fill:var(--accent)}.line{fill:none;stroke:var(--accent);stroke-width:4;stroke-linecap:round;stroke-linejoin:round}.dot{fill:var(--panel);stroke:var(--accent);stroke-width:3}.chart-title{font-weight:800;font-size:16px;fill:var(--ink)}.axis-label{font-size:12px;fill:var(--muted)}.value-label{font-size:12px;fill:var(--ink);font-weight:700}.split{display:grid;grid-template-columns:.9fr 1.1fr;gap:18px;align-items:start}.risk-list{display:grid;grid-template-columns:repeat(4,1fr);gap:12px}.risk-pill{border-radius:14px;border:1px solid #efcaca;background:#fff7f4;padding:14px}.risk-pill b{display:block;color:var(--danger);font-size:24px}table{width:100%;border-collapse:separate;border-spacing:0;background:var(--panel);border:1px solid var(--line);border-radius:16px;overflow:hidden}th,td{text-align:left;padding:12px 14px;border-bottom:1px solid var(--line);vertical-align:top;font-size:14px}th{background:#ece8dc;font-size:13px}tr:last-child td{border-bottom:0}.note{color:var(--muted);font-size:13px}.footer{padding:36px 0 54px;color:var(--muted);border-top:1px solid var(--line)}@media(max-width:820px){.hero,.split{grid-template-columns:1fr}.grid-3,.risk-list{grid-template-columns:1fr}.wrap{padding:0 16px}.hero{padding:42px 16px}.hero-panel{padding:16px}.metric-grid{grid-template-columns:1fr}.section{padding:42px 0}}
"""
    html_doc = f"""<!doctype html>
<html lang="zh-CN">
<head>
  <meta charset="utf-8"/>
  <meta name="viewport" content="width=device-width, initial-scale=1"/>
  <title>2026年1-6月乙方宝智慧食堂中标分析报告</title>
  <style>{css}</style>
</head>
<body>
  <main>
    <section class="hero wrap">
      <div>
        <div class="kicker">乙方宝中标通知分析</div>
        <h1>智慧食堂市场，先看机会，再看风险。</h1>
        <p>基于 345 条中标通知，报告把市场趋势、客户画像、供应商格局、商机优先级和风险红旗整理成可复核的业务交付物。</p>
        <div class="cta">
          <a class="btn primary" href="../deliverables/{html.escape(workbook_name)}">下载 Excel 工作簿</a>
          <a class="btn secondary" href="#opportunities">查看 Top 商机</a>
        </div>
      </div>
      <div class="hero-panel">
        <div class="metric-grid">
          <div class="metric"><b>{summary['row_count']}</b><span>中标通知记录</span></div>
          <div class="metric"><b>{summary['amount_count']}</b><span>披露金额记录</span></div>
          <div class="metric"><b>{yuan(summary['amount_total'])}</b><span>披露金额合计</span></div>
          <div class="metric"><b>{yuan(summary['amount_median'])}</b><span>金额中位数</span></div>
          <div class="metric"><b>{yuan(summary['amount_p90'])}</b><span>P90 金额</span></div>
          <div class="metric"><b>{pct(max_share)}</b><span>最大单项占比</span></div>
        </div>
      </div>
    </section>

    <section class="section wrap">
      <h2>一眼看懂市场波动</h2>
      <p class="lead">5 月金额异常抬升，核心原因是北京两条 13.92 亿元的食材劳务总包项目。正式经营判断需要同时看中位数和 P90。</p>
      <div class="chart-wrap">{line_chart(top_month_amount, title="月度披露金额趋势")}</div>
      <div class="grid-3">
        <div class="card"><h3>市场规模</h3><div class="big-number">{yuan(summary['amount_total'])}</div><p class="note">金额合计被超大项目明显拉高。</p></div>
        <div class="card"><h3>典型预算</h3><div class="big-number">{yuan(summary['amount_median'])}</div><p class="note">更接近日常智慧食堂项目预算。</p></div>
        <div class="card"><h3>高价值门槛</h3><div class="big-number">{yuan(summary['amount_p90'])}</div><p class="note">超过该值的项目应单独复盘。</p></div>
      </div>
    </section>

    <section class="section wrap">
      <h2>区域热度和需求类型</h2>
      <p class="lead">浙江、山东、新疆、辽宁项目频次靠前。需求上，软件系统、硬件终端、综合建设和餐饮运营并存，不能只按软件项目看预算。</p>
      <div class="split">
        <div class="chart-wrap">{bar_chart(top_province_count, title="省份项目数 Top 10", unit="条")}</div>
        <div class="chart-wrap">{bar_chart(demand_amount, title="需求类型金额合计", unit="万元")}</div>
      </div>
    </section>

    <section class="section wrap">
      <h2>客户和供应商格局</h2>
      <p class="lead">银行、学校、政府/事业单位是重点跟进对象。供应商按次数和按金额会得出不同名单，超大项目需要单列。</p>
      <div class="split">
        <div>{table_html(["重点客户", "项目数", "金额合计", "中位数"], client_rows, 8)}</div>
        <div>{table_html(["金额供应商", "项目数", "金额合计", "中位数"], supplier_rows, 8)}</div>
      </div>
    </section>

    <section id="opportunities" class="section wrap">
      <h2>商机 Top 10 摘要</h2>
      <p class="lead">完整 Top 30 已写入 Excel。HTML 只展示最优先的 10 条，便于业务快速扫读。</p>
      {table_html(["排名", "总分", "项目名称", "区域", "金额", "招标单位"], opp_rows, 10)}
    </section>

    <section class="section wrap">
      <h2>风险红旗需要复核</h2>
      <p class="lead">红旗只代表需要复核，不代表违规。当前主要问题是金额缺失、项目编号缺失、供应商频繁中标和异常大额。</p>
      <div class="risk-list">
        {''.join(f'<div class="risk-pill"><b>{count}</b><span>{html.escape(name)}</span></div>' for name, count in top_risks[:8])}
      </div>
    </section>

    <section class="section wrap">
      <h2>下周行动建议</h2>
      <div class="grid-3">
        <div class="card"><h3>销售核验</h3><p>先补齐 Top 30 商机的客户关键人、预算来源和后续采购计划。</p></div>
        <div class="card"><h3>售前复盘</h3><p>复盘高分项目中标方案，拆出硬件、软件、运营服务的报价边界。</p></div>
        <div class="card"><h3>数据治理</h3><p>建立单位别名表和异常剔除口径，每月更新双口径仪表盘。</p></div>
      </div>
    </section>
  </main>
  <footer class="footer wrap">生成自项目数据分析脚本。Excel 包含原始清洗数据和全部分析报表。</footer>
</body>
</html>
"""
    output.parent.mkdir(parents=True, exist_ok=True)
    output.write_text(sanitize_visible(html_doc), encoding="utf-8")


def main() -> int:
    parser = argparse.ArgumentParser(description="Generate HTML and Excel business deliverables.")
    parser.add_argument("--processed-dir", type=Path, default=WORKSPACE / "data/processed/20260612-yifangbao")
    parser.add_argument("--html", type=Path, default=WORKSPACE / "docs/reports/2026-h1-yifangbao-business-report.html")
    parser.add_argument("--xlsx", type=Path, default=WORKSPACE / "docs/deliverables/2026-h1-yifangbao-bid-analysis-workbook.xlsx")
    args = parser.parse_args()

    workbook_name = args.xlsx.name
    generate_html(args.processed_dir, args.html, workbook_name)
    write_workbook(args.processed_dir, args.xlsx)
    print(json.dumps({"html": str(args.html), "xlsx": str(args.xlsx)}, ensure_ascii=False, indent=2))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())