from __future__ import annotations

import html
import math
import shutil
from collections import Counter, defaultdict
from datetime import datetime
from pathlib import Path
from typing import Any, Iterable

from openpyxl import load_workbook
from openpyxl.chart import BarChart, DoughnutChart, LineChart, PieChart, Reference
from openpyxl.chart.label import DataLabelList
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter


REPO_ROOT = Path(__file__).resolve().parents[1]
SOURCE_XLSX = Path("/Users/zhangling/Desktop/周报/2026-h1-yifangbao-bid-analysis-workbook.xlsx")
OUT_DIR = REPO_ROOT / "docs" / "final-results" / "20260614-yifangbao-bid-analysis-charts"
OUT_XLSX = OUT_DIR / "2026-h1-yifangbao-bid-analysis-charts.xlsx"
OUT_HTML = OUT_DIR / "index.html"

PRIMARY = "1F4E5F"
SECONDARY = "D56A3A"
SURFACE = "F4F8F8"
HEADER = "DDEDEC"
GRID = "D8E2E2"
TEXT = "18343A"
MUTED = "607275"
WARN = "C24B4B"


def safe_number(value: Any) -> float:
    if isinstance(value, (int, float)) and not isinstance(value, bool):
        if math.isfinite(float(value)):
            return float(value)
    return 0.0


def to_month(value: Any) -> str | None:
    if value in (None, ""):
        return None
    if isinstance(value, datetime):
        return value.strftime("%Y-%m")
    text = str(value).strip()
    if len(text) >= 7 and text[:4].isdigit():
        return text[:7].replace("/", "-")
    return None


def short_label(value: Any, limit: int = 14) -> str:
    text = "" if value is None else str(value).strip()
    return text if len(text) <= limit else text[: limit - 1] + "…"


def sheet_rows(ws) -> list[dict[str, Any]]:
    headers = [cell.value for cell in ws[1]]
    rows = []
    for values in ws.iter_rows(min_row=2, values_only=True):
        row = {headers[i]: values[i] if i < len(values) else None for i in range(len(headers))}
        if any(v not in (None, "") for v in row.values()):
            rows.append(row)
    return rows


def remove_generated_sheets(wb) -> None:
    for name in list(wb.sheetnames):
        if name.startswith("图表-") or name == "图表总览":
            del wb[name]


def setup_sheet(ws, title: str, subtitle: str) -> None:
    ws.sheet_view.showGridLines = False
    ws.freeze_panes = "A5"
    ws.merge_cells("A1:H1")
    ws["A1"] = title
    ws["A1"].font = Font(name="Arial", bold=True, size=18, color=TEXT)
    ws["A1"].fill = PatternFill("solid", fgColor=HEADER)
    ws["A1"].alignment = Alignment(vertical="center")
    ws.row_dimensions[1].height = 30
    ws.merge_cells("A2:H2")
    ws["A2"] = subtitle
    ws["A2"].font = Font(name="Arial", size=10, color=MUTED)
    ws["A2"].alignment = Alignment(wrap_text=True, vertical="top")
    ws.row_dimensions[2].height = 34
    for col in range(1, 12):
        ws.column_dimensions[get_column_letter(col)].width = 14
    ws.column_dimensions["A"].width = 24
    ws.column_dimensions["B"].width = 16
    ws.column_dimensions["C"].width = 16
    ws.column_dimensions["D"].width = 16
    ws.column_dimensions["E"].width = 16
    ws.column_dimensions["F"].width = 18
    ws.column_dimensions["G"].width = 18
    ws.column_dimensions["H"].width = 18


def style_range(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.border = Border(left=thin, right=thin, top=thin, bottom=thin)
            cell.alignment = Alignment(vertical="center", wrap_text=True)
            cell.font = Font(name="Arial", size=10, color=TEXT)
            if cell.row == min_row:
                cell.fill = PatternFill("solid", fgColor=HEADER)
                cell.font = Font(name="Arial", bold=True, size=10, color=TEXT)
            elif cell.row % 2 == 0:
                cell.fill = PatternFill("solid", fgColor="FBFDFD")


def write_table(
    ws,
    start_row: int,
    start_col: int,
    headers: list[str],
    rows: Iterable[Iterable[Any]],
    number_formats: dict[int, str] | None = None,
) -> tuple[int, int, int, int]:
    row_list = [list(row) for row in rows]
    for c, header in enumerate(headers, start_col):
        ws.cell(start_row, c, header)
    for r, row in enumerate(row_list, start_row + 1):
        for c, value in enumerate(row, start_col):
            ws.cell(r, c, value)
    end_row = start_row + len(row_list)
    end_col = start_col + len(headers) - 1
    style_range(ws, start_row, end_row, start_col, end_col)
    if number_formats:
        for offset, fmt in number_formats.items():
            col = start_col + offset
            for row in range(start_row + 1, end_row + 1):
                ws.cell(row, col).number_format = fmt
    return start_row, end_row, start_col, end_col


def add_note(ws, cell: str, text: str) -> None:
    ws[cell] = text
    ws[cell].font = Font(name="Arial", size=9, color=MUTED, italic=True)
    ws[cell].alignment = Alignment(wrap_text=True, vertical="top")


def add_bar_chart(
    ws,
    title: str,
    table_range: tuple[int, int, int, int],
    anchor: str,
    value_col_offset: int = 1,
    horizontal: bool = True,
    width: float = 16,
    height: float = 8,
    num_fmt: str | None = None,
) -> BarChart:
    min_row, max_row, min_col, _ = table_range
    chart = BarChart()
    chart.type = "bar" if horizontal else "col"
    chart.style = 10
    chart.title = title
    chart.height = height
    chart.width = width
    chart.y_axis.title = None
    chart.x_axis.title = None
    if num_fmt:
        chart.x_axis.numFmt = num_fmt
        chart.y_axis.numFmt = num_fmt if not horizontal else None
    data = Reference(ws, min_col=min_col + value_col_offset, min_row=min_row, max_row=max_row)
    cats = Reference(ws, min_col=min_col, min_row=min_row + 1, max_row=max_row)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.legend = None
    ws.add_chart(chart, anchor)
    return chart


def add_multi_bar_chart(
    ws,
    title: str,
    table_range: tuple[int, int, int, int],
    anchor: str,
    min_series_offset: int,
    max_series_offset: int,
    horizontal: bool = False,
    stacked: bool = False,
    width: float = 16,
    height: float = 8,
) -> BarChart:
    min_row, max_row, min_col, _ = table_range
    chart = BarChart()
    chart.type = "bar" if horizontal else "col"
    chart.style = 10
    chart.title = title
    chart.height = height
    chart.width = width
    chart.grouping = "stacked" if stacked else "clustered"
    if stacked:
        chart.overlap = 100
    data = Reference(
        ws,
        min_col=min_col + min_series_offset,
        max_col=min_col + max_series_offset,
        min_row=min_row,
        max_row=max_row,
    )
    cats = Reference(ws, min_col=min_col, min_row=min_row + 1, max_row=max_row)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    ws.add_chart(chart, anchor)
    return chart


def add_line_chart(
    ws,
    title: str,
    table_range: tuple[int, int, int, int],
    anchor: str,
    min_series_offset: int,
    max_series_offset: int,
    width: float = 16,
    height: float = 8,
) -> LineChart:
    min_row, max_row, min_col, _ = table_range
    chart = LineChart()
    chart.style = 13
    chart.title = title
    chart.height = height
    chart.width = width
    data = Reference(
        ws,
        min_col=min_col + min_series_offset,
        max_col=min_col + max_series_offset,
        min_row=min_row,
        max_row=max_row,
    )
    cats = Reference(ws, min_col=min_col, min_row=min_row + 1, max_row=max_row)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.y_axis.majorGridlines = None
    ws.add_chart(chart, anchor)
    return chart


def add_pie_chart(
    ws,
    title: str,
    table_range: tuple[int, int, int, int],
    anchor: str,
    doughnut: bool = False,
    width: float = 10,
    height: float = 8,
) -> PieChart:
    min_row, max_row, min_col, _ = table_range
    chart = DoughnutChart() if doughnut else PieChart()
    chart.title = title
    chart.height = height
    chart.width = width
    data = Reference(ws, min_col=min_col + 1, min_row=min_row, max_row=max_row)
    cats = Reference(ws, min_col=min_col, min_row=min_row + 1, max_row=max_row)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.dataLabels = DataLabelList()
    chart.dataLabels.showPercent = True
    ws.add_chart(chart, anchor)
    return chart


def summarize_raw(raw_rows: list[dict[str, Any]]) -> dict[str, list[tuple[Any, ...]]]:
    month = defaultdict(lambda: [0, 0.0])
    province = defaultdict(lambda: [0, 0.0])
    demand = Counter()
    customer = Counter()
    stage = Counter()
    for row in raw_rows:
        if not row.get("项目名称"):
            continue
        amount = safe_number(row.get("金额数值") or row.get("中标金额（元）"))
        m = to_month(row.get("月份") or row.get("信息发布时间") or row.get("发布时间"))
        if m:
            month[m][0] += 1
            month[m][1] += amount / 10000
        p = row.get("发布省份") or "未填"
        province[p][0] += 1
        province[p][1] += amount / 10000
        demand[row.get("需求类型") or "未识别"] += 1
        customer[row.get("客户类型") or "未识别"] += 1
        stage[row.get("中标阶段") or "未识别"] += 1
    month_rows = [(k, v[0], round(v[1], 1)) for k, v in sorted(month.items())]
    province_rows = [(k, v[0], round(v[1], 1)) for k, v in sorted(province.items(), key=lambda item: item[1][0], reverse=True)[:12]]
    demand_rows = demand.most_common(8)
    customer_rows = customer.most_common(10)
    stage_rows = stage.most_common(8)
    return {
        "month": month_rows,
        "province": province_rows,
        "demand": demand_rows,
        "customer": customer_rows,
        "stage": stage_rows,
    }


def table_from_source(
    ws_source,
    label_header: str,
    rows_limit: int,
    value_headers: list[str],
    amount_to_wan: bool = False,
    sort_by_header: str | None = None,
) -> tuple[list[str], list[list[Any]]]:
    headers = [cell.value for cell in ws_source[1]]
    indexes = {h: i for i, h in enumerate(headers)}
    rows = []
    for values in ws_source.iter_rows(min_row=2, values_only=True):
        if not values or values[indexes[label_header]] in (None, ""):
            continue
        row = [short_label(values[indexes[label_header]])]
        for h in value_headers:
            value = values[indexes[h]]
            is_amount_value = "金额" in h and "项目数" not in h and "吸引力" not in h
            if amount_to_wan and is_amount_value:
                value = safe_number(value) / 10000
            row.append(value)
        rows.append(row)
    if sort_by_header:
        sort_offset = value_headers.index(sort_by_header) + 1
        rows.sort(key=lambda r: safe_number(r[sort_offset]), reverse=True)
    return [label_header] + [
        h + ("(万元)" if amount_to_wan and "金额" in h and "项目数" not in h and "吸引力" not in h else "")
        for h in value_headers
    ], rows[:rows_limit]


def create_overview(wb, chart_plan: list[tuple[str, str, str, str]]) -> None:
    ws = wb.create_sheet("图表总览", 0)
    setup_sheet(ws, "2026年1-6月乙方宝智慧食堂中标分析 - 图表总览", "每个原始/分析 sheet 均生成对应图表页；原数据 sheet 保留在工作簿后方，可用于复核。")
    rows = []
    for source, chart_sheet, chart_type, usage in chart_plan:
        rows.append([source, chart_sheet, chart_type, usage])
    write_table(ws, 4, 1, ["原 sheet", "图表页", "图表形式", "汇报用途"], rows)
    ws.column_dimensions["A"].width = 18
    ws.column_dimensions["B"].width = 24
    ws.column_dimensions["C"].width = 30
    ws.column_dimensions["D"].width = 58
    add_note(ws, "A30", f"生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M')}；源文件：{SOURCE_XLSX}")


def build_charted_workbook() -> list[tuple[str, str, str, str]]:
    OUT_DIR.mkdir(parents=True, exist_ok=True)
    temp_xlsx = OUT_DIR / "_working.xlsx"
    shutil.copy2(SOURCE_XLSX, temp_xlsx)
    wb = load_workbook(temp_xlsx)
    value_wb = load_workbook(SOURCE_XLSX, data_only=True)
    remove_generated_sheets(wb)

    try:
        wb.calculation.fullCalcOnLoad = True
        wb.calculation.forceFullCalc = True
    except AttributeError:
        pass

    chart_plan: list[tuple[str, str, str, str]] = []

    # 导读
    ws = wb.create_sheet("图表-导读", 0)
    setup_sheet(ws, "导读：核心指标可视化", "把导读页中的关键规模、金额和风险指标转换成汇报时可直接引用的 KPI 与覆盖率图。")
    guide = value_wb["导读"]
    metrics = {guide.cell(r, 1).value: guide.cell(r, 2).value for r in range(6, 13)}
    cards = [
        ("记录数", metrics.get("记录数"), "条"),
        ("披露金额记录数", metrics.get("披露金额记录数"), "条"),
        ("披露金额合计", round(safe_number(metrics.get("披露金额合计")) / 100000000, 2), "亿元"),
        ("金额中位数", round(safe_number(metrics.get("金额中位数")) / 10000, 1), "万元"),
        ("最大金额", round(safe_number(metrics.get("最大金额")) / 100000000, 2), "亿元"),
        ("红色风险项", metrics.get("红色风险项"), "项"),
    ]
    write_table(ws, 4, 1, ["指标", "数值", "单位"], cards, {1: "#,##0.00"})
    disclosed = safe_number(metrics.get("披露金额记录数"))
    total = safe_number(metrics.get("记录数"))
    coverage_rows = [("已披露金额", disclosed), ("未披露金额", max(total - disclosed, 0))]
    coverage_range = write_table(ws, 4, 5, ["披露状态", "项目数"], coverage_rows, {1: "#,##0"})
    add_pie_chart(ws, "金额披露覆盖率", coverage_range, "E9", doughnut=True, width=10, height=7)
    chart_plan.append(("导读", "图表-导读", "KPI 表 + 环形图", "先讲总盘子、金额披露覆盖率和红色风险项数量。"))

    # 原始清洗数据
    raw_rows = sheet_rows(value_wb["原始清洗数据"])
    raw_summary = summarize_raw(raw_rows)
    ws = wb.create_sheet("图表-原始清洗数据", 1)
    setup_sheet(ws, "原始清洗数据：月度、地区与类型概览", "从原始清洗数据重新聚合，帮助在不翻明细的情况下说明样本结构。金额单位为万元。")
    month_range = write_table(ws, 4, 1, ["月份", "项目数", "金额合计(万元)"], raw_summary["month"], {1: "#,##0", 2: "#,##0.0"})
    add_line_chart(ws, "月度项目数与金额", month_range, "E4", 1, 2, width=15, height=7)
    province_range = write_table(ws, 15, 1, ["省份", "项目数", "金额合计(万元)"], raw_summary["province"], {1: "#,##0", 2: "#,##0.0"})
    add_bar_chart(ws, "Top省份项目数", province_range, "E15", 1, horizontal=True, width=15, height=8)
    demand_range = write_table(ws, 31, 1, ["需求类型", "项目数"], raw_summary["demand"], {1: "#,##0"})
    add_pie_chart(ws, "需求类型占比", demand_range, "E31", doughnut=False, width=11, height=8)
    chart_plan.append(("原始清洗数据", "图表-原始清洗数据", "折线图 + 横向柱图 + 饼图", "说明样本按月、地区、需求类型的结构。"))

    # 月度趋势
    ws = wb.create_sheet("图表-月度趋势", 2)
    setup_sheet(ws, "月度趋势：数量与金额节奏", "数量与金额分开呈现，避免 5 月大额项目掩盖项目数变化。金额单位为万元。")
    headers, rows = table_from_source(value_wb["月度趋势"], "月份", 12, ["项目数", "披露金额项目数", "金额合计"], amount_to_wan=True)
    table_range = write_table(ws, 4, 1, headers, rows, {1: "#,##0", 2: "#,##0", 3: "#,##0.0"})
    add_line_chart(ws, "项目数与披露金额项目数", table_range, "F4", 1, 2, width=15, height=7)
    add_bar_chart(ws, "金额合计(万元)", table_range, "F18", 3, horizontal=False, width=15, height=7, num_fmt="#,##0")
    chart_plan.append(("月度趋势", "图表-月度趋势", "折线图 + 柱图", "讲清月度项目活跃度和金额峰值。"))

    # 省份分析
    ws = wb.create_sheet("图表-省份分析", 3)
    setup_sheet(ws, "省份分析：区域集中度", "按项目数和金额两条线分别排序，便于区分高频地区与大额地区。金额单位为万元。")
    headers, rows = table_from_source(value_wb["省份分析"], "发布省份", 15, ["项目数", "金额合计"], amount_to_wan=True, sort_by_header="项目数")
    count_range = write_table(ws, 4, 1, headers, rows, {1: "#,##0", 2: "#,##0.0"})
    add_bar_chart(ws, "Top15省份项目数", count_range, "E4", 1, horizontal=True, width=15, height=9)
    headers, rows = table_from_source(value_wb["省份分析"], "发布省份", 15, ["金额合计", "项目数"], amount_to_wan=True, sort_by_header="金额合计")
    amount_range = write_table(ws, 23, 1, headers, rows, {1: "#,##0.0", 2: "#,##0"})
    add_bar_chart(ws, "Top15省份金额合计(万元)", amount_range, "E23", 1, horizontal=True, width=15, height=9)
    chart_plan.append(("省份分析", "图表-省份分析", "双横向排行图", "比较高频省份和高金额省份。"))

    # 城市分析
    ws = wb.create_sheet("图表-城市分析", 4)
    setup_sheet(ws, "城市分析：重点城市排行", "城市数量较多，使用 Top15 横向排行减少长标签拥挤。金额单位为万元。")
    headers, rows = table_from_source(value_wb["城市分析"], "发布市级", 15, ["项目数", "金额合计"], amount_to_wan=True, sort_by_header="项目数")
    count_range = write_table(ws, 4, 1, headers, rows, {1: "#,##0", 2: "#,##0.0"})
    add_bar_chart(ws, "Top15城市项目数", count_range, "E4", 1, horizontal=True, width=15, height=9)
    headers, rows = table_from_source(value_wb["城市分析"], "发布市级", 15, ["金额合计", "项目数"], amount_to_wan=True, sort_by_header="金额合计")
    amount_range = write_table(ws, 23, 1, headers, rows, {1: "#,##0.0", 2: "#,##0"})
    add_bar_chart(ws, "Top15城市金额合计(万元)", amount_range, "E23", 1, horizontal=True, width=15, height=9)
    chart_plan.append(("城市分析", "图表-城市分析", "双横向排行图", "定位城市层面的重点市场。"))

    # 客户Top20
    ws = wb.create_sheet("图表-客户Top20", 5)
    setup_sheet(ws, "客户Top20：高频客户与金额贡献", "客户名称较长，图表标签采用简称，完整名称仍保留在原 sheet。金额单位为万元。")
    headers, rows = table_from_source(value_wb["客户Top20"], "招标单位", 15, ["项目数", "金额合计"], amount_to_wan=True, sort_by_header="项目数")
    count_range = write_table(ws, 4, 1, headers, rows, {1: "#,##0", 2: "#,##0.0"})
    add_bar_chart(ws, "客户Top15项目数", count_range, "E4", 1, horizontal=True, width=15, height=9)
    headers, rows = table_from_source(value_wb["客户Top20"], "招标单位", 10, ["金额合计", "项目数"], amount_to_wan=True, sort_by_header="金额合计")
    amount_range = write_table(ws, 23, 1, headers, rows, {1: "#,##0.0", 2: "#,##0"})
    add_bar_chart(ws, "客户Top10金额合计(万元)", amount_range, "E23", 1, horizontal=True, width=15, height=8)
    chart_plan.append(("客户Top20", "图表-客户Top20", "横向排行图", "展示重点客户的频次和规模贡献。"))

    # 供应商次数Top20
    ws = wb.create_sheet("图表-供应商次数Top20", 6)
    setup_sheet(ws, "供应商次数Top20：中标频次竞争格局", "用次数排行识别高频供应商，用金额排行补充规模信息。金额单位为万元。")
    headers, rows = table_from_source(value_wb["供应商次数Top20"], "中标单位", 15, ["项目数", "金额合计"], amount_to_wan=True, sort_by_header="项目数")
    count_range = write_table(ws, 4, 1, headers, rows, {1: "#,##0", 2: "#,##0.0"})
    add_bar_chart(ws, "供应商Top15中标次数", count_range, "E4", 1, horizontal=True, width=15, height=9)
    headers, rows = table_from_source(value_wb["供应商次数Top20"], "中标单位", 10, ["金额合计", "项目数"], amount_to_wan=True, sort_by_header="金额合计")
    amount_range = write_table(ws, 23, 1, headers, rows, {1: "#,##0.0", 2: "#,##0"})
    add_bar_chart(ws, "高频供应商金额合计(万元)", amount_range, "E23", 1, horizontal=True, width=15, height=8)
    chart_plan.append(("供应商次数Top20", "图表-供应商次数Top20", "横向排行图", "说明竞争者出现频次和高频供应商金额。"))

    # 供应商金额Top20
    ws = wb.create_sheet("图表-供应商金额Top20", 7)
    setup_sheet(ws, "供应商金额Top20：金额集中度", "大额项目对金额排名影响明显，图表以金额为主、项目数作为旁表字段。金额单位为万元。")
    headers, rows = table_from_source(value_wb["供应商金额Top20"], "中标单位", 12, ["金额合计", "项目数"], amount_to_wan=True, sort_by_header="金额合计")
    amount_range = write_table(ws, 4, 1, headers, rows, {1: "#,##0.0", 2: "#,##0"})
    add_bar_chart(ws, "供应商Top12金额合计(万元)", amount_range, "E4", 1, horizontal=True, width=15, height=10)
    add_bar_chart(ws, "供应商Top12项目数", amount_range, "E25", 2, horizontal=True, width=15, height=8)
    chart_plan.append(("供应商金额Top20", "图表-供应商金额Top20", "金额排行图 + 项目数排行图", "解释金额集中在哪些供应商及其样本频次。"))

    # 需求类型
    ws = wb.create_sheet("图表-需求类型", 8)
    setup_sheet(ws, "需求类型：需求结构与金额", "项目数说明市场热度，金额说明预算规模；两者分开看更清楚。金额单位为万元。")
    headers, rows = table_from_source(value_wb["需求类型"], "需求类型", 10, ["项目数", "金额合计"], amount_to_wan=True, sort_by_header="项目数")
    table_range = write_table(ws, 4, 1, headers, rows, {1: "#,##0", 2: "#,##0.0"})
    add_bar_chart(ws, "需求类型项目数", table_range, "E4", 1, horizontal=True, width=15, height=8)
    add_bar_chart(ws, "需求类型金额合计(万元)", table_range, "E20", 2, horizontal=True, width=15, height=8)
    chart_plan.append(("需求类型", "图表-需求类型", "双横向柱图", "对比各类需求的热度和金额规模。"))

    # 客户类型
    ws = wb.create_sheet("图表-客户类型", 9)
    setup_sheet(ws, "客户类型：客群结构", "用柱图看项目数，用环形图看金额结构，方便讲客群重点。金额单位为万元。")
    headers, rows = table_from_source(value_wb["客户类型"], "客户类型", 10, ["项目数", "金额合计"], amount_to_wan=True, sort_by_header="项目数")
    table_range = write_table(ws, 4, 1, headers, rows, {1: "#,##0", 2: "#,##0.0"})
    add_bar_chart(ws, "客户类型项目数", table_range, "E4", 1, horizontal=True, width=15, height=8)
    pie_rows = [[r[0], r[2]] for r in rows]
    pie_range = write_table(ws, 20, 1, ["客户类型", "金额合计(万元)"], pie_rows, {1: "#,##0.0"})
    add_pie_chart(ws, "客户类型金额占比", pie_range, "E20", doughnut=True, width=11, height=8)
    chart_plan.append(("客户类型", "图表-客户类型", "横向柱图 + 环形图", "说明银行、学校、政府等客群的项目与金额结构。"))

    # 商机Top30
    ws = wb.create_sheet("图表-商机Top30", 10)
    setup_sheet(ws, "商机Top30：评分结构", "Top 商机既看总分，也看评分构成，方便解释为什么这些项目优先。")
    headers, rows = table_from_source(value_wb["商机Top30"], "项目名称", 10, ["总分", "市场热度", "金额吸引力", "客户价值", "竞争可进入性", "可行动性"], sort_by_header="总分")
    table_range = write_table(ws, 4, 1, headers, rows, {1: "0.0", 2: "0.0", 3: "0.0", 4: "0.0", 5: "0.0", 6: "0.0"})
    add_bar_chart(ws, "Top10商机总分", table_range, "I4", 1, horizontal=True, width=15, height=9)
    component_range = (table_range[0], table_range[1], table_range[2], table_range[3])
    add_multi_bar_chart(ws, "Top10商机评分构成", component_range, "I23", 2, 6, horizontal=True, stacked=True, width=15, height=9)
    chart_plan.append(("商机Top30", "图表-商机Top30", "总分排行 + 堆积条形图", "解释高优先级商机的评分来源。"))

    # 风险红旗
    risk_rows = sheet_rows(value_wb["风险红旗"])
    level_counts = Counter(row.get("风险等级") or "未识别" for row in risk_rows)
    rule_counts = Counter(row.get("触发规则") or "未识别" for row in risk_rows)
    top_amounts = sorted(
        [
            (short_label(row.get("项目名称"), 16), safe_number(row.get("金额")) / 10000, row.get("风险等级"))
            for row in risk_rows
            if safe_number(row.get("金额")) > 0
        ],
        key=lambda x: x[1],
        reverse=True,
    )[:10]
    ws = wb.create_sheet("图表-风险红旗", 11)
    setup_sheet(ws, "风险红旗：复核工作量与重点", "风险图表用于安排复核优先级；金额单位为万元。")
    level_range = write_table(ws, 4, 1, ["风险等级", "条目数"], level_counts.most_common(), {1: "#,##0"})
    add_pie_chart(ws, "风险等级占比", level_range, "E4", doughnut=True, width=10, height=7)
    rule_range = write_table(ws, 14, 1, ["触发规则", "条目数"], rule_counts.most_common(12), {1: "#,##0"})
    add_bar_chart(ws, "Top触发规则", rule_range, "E14", 1, horizontal=True, width=15, height=9)
    amount_range = write_table(ws, 31, 1, ["项目", "金额(万元)", "风险等级"], top_amounts, {1: "#,##0.0"})
    add_bar_chart(ws, "风险项金额Top10(万元)", amount_range, "E31", 1, horizontal=True, width=15, height=8)
    chart_plan.append(("风险红旗", "图表-风险红旗", "环形图 + 规则排行 + 金额排行", "安排数据复核的优先级。"))

    # 大额项目
    ws = wb.create_sheet("图表-大额项目", 12)
    setup_sheet(ws, "大额项目：亿元级项目复核", "仅展示超过 1 亿元项目，图表用于突出其对总金额的影响。金额单位为亿元。")
    large = []
    for row in sheet_rows(value_wb["大额项目"]):
        large.append([short_label(row.get("项目名称"), 18), safe_number(row.get("中标金额（元）")) / 100000000, row.get("中标单位"), row.get("审核要点")])
    large_range = write_table(ws, 4, 1, ["项目", "金额(亿元)", "中标单位", "审核要点"], large, {1: "#,##0.00"})
    ws.column_dimensions["C"].width = 28
    ws.column_dimensions["D"].width = 64
    add_bar_chart(ws, "大额项目金额(亿元)", large_range, "F4", 1, horizontal=True, width=13, height=7)
    chart_plan.append(("大额项目", "图表-大额项目", "横向金额柱图 + 审核要点表", "专门说明亿元项目和需要复核的口径风险。"))

    create_overview(wb, chart_plan)

    # Move overview first and keep chart pages before data pages.
    wb.active = 0
    for ws in wb.worksheets:
        if ws.title.startswith("图表"):
            ws.sheet_properties.tabColor = PRIMARY
        else:
            ws.sheet_properties.tabColor = "C9D3D3"

    wb.save(OUT_XLSX)
    temp_xlsx.unlink(missing_ok=True)
    return chart_plan


def build_html(chart_plan: list[tuple[str, str, str, str]]) -> None:
    rows = "\n".join(
        f"<tr><td>{html.escape(source)}</td><td>{html.escape(chart_sheet)}</td><td>{html.escape(chart_type)}</td><td>{html.escape(usage)}</td></tr>"
        for source, chart_sheet, chart_type, usage in chart_plan
    )
    html_text = f"""<!doctype html>
<html lang="zh-CN">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <title>乙方宝智慧食堂中标分析图表版</title>
  <style>
    :root {{
      color-scheme: light;
      --ink: #18343a;
      --muted: #607275;
      --line: #d8e2e2;
      --panel: #f4f8f8;
      --accent: #1f4e5f;
      --accent2: #d56a3a;
      --white: #ffffff;
    }}
    * {{ box-sizing: border-box; }}
    body {{
      margin: 0;
      font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "PingFang SC", "Microsoft YaHei", Arial, sans-serif;
      background: #fbfdfd;
      color: var(--ink);
      line-height: 1.55;
    }}
    main {{ max-width: 1120px; margin: 0 auto; padding: 44px 24px 64px; }}
    header {{
      border-top: 6px solid var(--accent);
      padding: 28px 0 22px;
      display: grid;
      gap: 14px;
    }}
    h1 {{ margin: 0; font-size: clamp(28px, 4vw, 44px); line-height: 1.12; letter-spacing: 0; }}
    p {{ margin: 0; color: var(--muted); max-width: 820px; }}
    .actions {{ display: flex; gap: 12px; flex-wrap: wrap; margin-top: 8px; }}
    a.button {{
      display: inline-flex;
      align-items: center;
      justify-content: center;
      min-height: 42px;
      padding: 0 18px;
      border: 1px solid var(--accent);
      background: var(--accent);
      color: white;
      text-decoration: none;
      font-weight: 700;
      border-radius: 6px;
    }}
    section {{ margin-top: 30px; }}
    .summary {{
      display: grid;
      grid-template-columns: repeat(3, minmax(0, 1fr));
      gap: 12px;
    }}
    .metric {{
      background: var(--panel);
      border: 1px solid var(--line);
      border-radius: 8px;
      padding: 16px;
    }}
    .metric strong {{ display: block; font-size: 22px; color: var(--accent); }}
    .metric span {{ color: var(--muted); font-size: 13px; }}
    table {{
      width: 100%;
      border-collapse: collapse;
      background: var(--white);
      border: 1px solid var(--line);
      table-layout: fixed;
    }}
    th, td {{ padding: 12px 14px; border-bottom: 1px solid var(--line); vertical-align: top; text-align: left; }}
    th {{ background: var(--panel); color: var(--accent); font-size: 13px; }}
    td {{ font-size: 14px; }}
    tr:last-child td {{ border-bottom: 0; }}
    td:nth-child(1) {{ width: 18%; font-weight: 700; }}
    td:nth-child(2) {{ width: 22%; }}
    td:nth-child(3) {{ width: 26%; color: var(--accent2); font-weight: 700; }}
    footer {{ margin-top: 28px; color: var(--muted); font-size: 13px; }}
    @media (max-width: 760px) {{
      main {{ padding: 28px 16px 44px; }}
      .summary {{ grid-template-columns: 1fr; }}
      table {{ table-layout: auto; }}
      th, td {{ padding: 10px; }}
    }}
  </style>
</head>
<body>
  <main>
    <header>
      <h1>乙方宝智慧食堂中标分析图表版</h1>
      <p>本结果包基于 2026 年 1-6 月分析工作簿生成，保留原始数据 sheet，并新增每个 sheet 对应的图表页，便于汇报时按趋势、地区、客户、供应商、需求与风险逐页展示。</p>
      <div class="actions">
        <a class="button" href="./{OUT_XLSX.name}">打开图表版 Excel</a>
      </div>
    </header>
    <section class="summary" aria-label="结果摘要">
      <div class="metric"><strong>{len(chart_plan)}</strong><span>个原 sheet 已生成图表页</span></div>
      <div class="metric"><strong>原生 Excel</strong><span>图表可继续编辑和复制到汇报材料</span></div>
      <div class="metric"><strong>保留明细</strong><span>原始清洗数据和分析报表未覆盖</span></div>
    </section>
    <section>
      <table>
        <thead><tr><th>原 sheet</th><th>图表页</th><th>图表形式</th><th>汇报用途</th></tr></thead>
        <tbody>{rows}</tbody>
      </table>
    </section>
    <footer>生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M')}；输出目录：{OUT_DIR}</footer>
  </main>
</body>
</html>
"""
    OUT_HTML.write_text(html_text, encoding="utf-8")


def main() -> None:
    chart_plan = build_charted_workbook()
    build_html(chart_plan)
    print(f"wrote {OUT_XLSX}")
    print(f"wrote {OUT_HTML}")
    print(f"chart pages: {len(chart_plan)}")


if __name__ == "__main__":
    main()