openclaw-voc-skill/scripts/build_glass_report.py

from __future__ import annotations

import argparse
import html
import math
import re
from collections import Counter, defaultdict
from datetime import datetime
from pathlib import Path
from typing import Dict, Iterable, List, Tuple

from openpyxl import load_workbook


TOTAL_WORD_RE = re.compile(r"(总计|合计|小计|汇总|平均)")
LM_CODE_RE = re.compile(r"^(LM-[0-9A-Za-z]+(?:[+\-][0-9A-Za-z]+)*)")
PAREN_RE = re.compile(r"[（(][^）)]*[）)]")
TRAILING_NOTE_RE = re.compile(r"[-–—](左开|右开|见附图|开孔见图).*$")
TRAILING_THICKNESS_RE = re.compile(r"\d+(?:\+\d+|\-\d+)?(?:\s*[mM]{1,2})$")
LEADING_THICKNESS_RE = re.compile(r"^\s*(?:\d+(?:\+\d+|\-\d+)+|\d+)(?:\s*[mM]{1,2})[-–—]?\s*")
WHITESPACE_RE = re.compile(r"\s+")


PALETTE = [
    "#2563eb",
    "#14b8a6",
    "#f59e0b",
    "#ef4444",
    "#8b5cf6",
    "#06b6d4",
    "#10b981",
    "#f97316",
    "#64748b",
    "#db2777",
]


def to_float(value) -> float:
    if value in (None, ""):
        return 0.0
    if isinstance(value, (int, float)):
        return float(value)
    try:
        return float(str(value).replace(",", "").strip())
    except Exception:
        return 0.0


def clean_text(value: str) -> str:
    text = str(value).strip()
    text = text.replace("\u3000", "")
    text = text.replace("双蓉", "双绒")
    text = WHITESPACE_RE.sub("", text)
    return text


def extract_code(name: str) -> str | None:
    match = LM_CODE_RE.match(name)
    if not match:
        return None
    return match.group(1)


def normalize_group_key(raw_name: str) -> str:
    name = clean_text(raw_name)

    code = extract_code(name)
    if code:
        return code

    name = PAREN_RE.sub("", name)
    name = LEADING_THICKNESS_RE.sub("", name)
    name = TRAILING_NOTE_RE.sub("", name)
    name = TRAILING_THICKNESS_RE.sub("", name)
    name = name.strip("-_，,;；")
    name = WHITESPACE_RE.sub("", name)
    return name or clean_text(raw_name)


def format_qty(value: float) -> str:
    return f"{int(round(value)):,.0f}"


def format_area(value: float) -> str:
    return f"{value:,.2f}"


def format_money(value: float) -> str:
    return f"¥{value:,.2f}"


def truncate_text(text: str, limit: int = 22) -> str:
    if len(text) <= limit:
        return text
    return text[: max(0, limit - 1)] + "…"


def count_decimals(value: float) -> int:
    if abs(value) >= 1000:
        return 0
    if abs(value) >= 100:
        return 1
    return 2


def percent(value: float, total: float) -> str:
    if total <= 0:
        return "0.0%"
    return f"{value / total * 100:.1f}%"


def label_tags(raw_name: str, sheet_name: str) -> List[str]:
    name = clean_text(raw_name)
    tags: List[str] = []
    if "夹丝" in name or sheet_name == "夹丝玻璃":
        tags.append("夹丝")
    if "透明" in name or sheet_name == "透明玻璃":
        tags.append("透明")
    if "烤漆" in name or sheet_name == "烤漆玻璃":
        tags.append("烤漆")

    art_keywords = [
        "长虹",
        "油砂",
        "玉砂",
        "热弯",
        "平四边",
        "蜂窝",
        "斜格",
        "小灯芯",
        "竹子图",
        "打印",
    ]
    if sheet_name == "艺术玻璃" or any(k in name for k in art_keywords):
        tags.append("艺术玻璃")

    # Keep tag order stable and unique.
    seen = set()
    ordered: List[str] = []
    for tag in tags:
        if tag not in seen:
            seen.add(tag)
            ordered.append(tag)
    return ordered


def load_records(xlsx_path: Path):
    wb = load_workbook(xlsx_path, read_only=True, data_only=True)
    records = []
    excluded = []

    for ws in wb.worksheets:
        for row_idx, row in enumerate(ws.iter_rows(values_only=True), start=1):
            if row_idx == 1:
                continue

            name = row[0] if len(row) > 0 else None
            qty = to_float(row[1] if len(row) > 1 else None)
            area = to_float(row[2] if len(row) > 2 else None)
            amount = to_float(row[3] if len(row) > 3 else None)

            if not name or not isinstance(name, str) or not name.strip():
                if any(v not in (None, "") for v in (row[1] if len(row) > 1 else None, row[2] if len(row) > 2 else None, row[3] if len(row) > 3 else None)):
                    excluded.append(
                        {
                            "sheet": ws.title,
                            "row": row_idx,
                            "name": "",
                            "reason": "blank_name",
                        }
                    )
                continue

            clean_name = clean_text(name)
            if TOTAL_WORD_RE.search(clean_name):
                excluded.append(
                    {
                        "sheet": ws.title,
                        "row": row_idx,
                        "name": clean_name,
                        "reason": "summary_row",
                    }
                )
                continue

            if qty == 0 and area == 0 and amount == 0:
                excluded.append(
                    {
                        "sheet": ws.title,
                        "row": row_idx,
                        "name": clean_name,
                        "reason": "zero_row",
                    }
                )
                continue

            records.append(
                {
                    "sheet": ws.title,
                    "row": row_idx,
                    "raw_name": clean_name,
                    "group_key": normalize_group_key(clean_name),
                    "qty": qty,
                    "area": area,
                    "amount": amount,
                    "tags": label_tags(clean_name, ws.title),
                }
            )

    return records, excluded


def build_sheet_stats(records: List[Dict]):
    stats: Dict[str, Dict] = {}
    for rec in records:
        bucket = stats.setdefault(
            rec["sheet"],
            {
                "sheet": rec["sheet"],
                "rows": 0,
                "qty": 0.0,
                "area": 0.0,
                "amount": 0.0,
            },
        )
        bucket["rows"] += 1
        bucket["qty"] += rec["qty"]
        bucket["area"] += rec["area"]
        bucket["amount"] += rec["amount"]
    return stats


def build_group_stats(records: List[Dict]):
    stats: Dict[str, Dict] = {}
    for rec in records:
        key = rec["group_key"]
        bucket = stats.setdefault(
            key,
            {
                "group_key": key,
                "qty": 0.0,
                "area": 0.0,
                "amount": 0.0,
                "rows": 0,
                "raw_names": Counter(),
                "sheets": Counter(),
                "tags": Counter(),
            },
        )
        bucket["qty"] += rec["qty"]
        bucket["area"] += rec["area"]
        bucket["amount"] += rec["amount"]
        bucket["rows"] += 1
        bucket["raw_names"][rec["raw_name"]] += 1
        bucket["sheets"][rec["sheet"]] += 1
        bucket["tags"].update(rec["tags"])

    for bucket in stats.values():
        raw_counts = bucket["raw_names"]
        bucket["variant_count"] = len(raw_counts)
        bucket["variants"] = raw_counts.most_common()
        bucket["primary_name"] = bucket["group_key"]
        bucket["dominant_sheet"] = bucket["sheets"].most_common(1)[0][0] if bucket["sheets"] else ""
    return stats


def build_label_stats(records: List[Dict], group_stats: Dict[str, Dict]):
    labels = ["夹丝", "透明", "烤漆", "艺术玻璃"]
    out: Dict[str, Dict] = {}
    for label in labels:
        out[label] = {
            "label": label,
            "rows": 0,
            "qty": 0.0,
            "area": 0.0,
            "amount": 0.0,
            "groups": Counter(),
            "group_metrics": defaultdict(lambda: {"qty": 0.0, "area": 0.0, "amount": 0.0}),
            "raw_names": Counter(),
        }

    for rec in records:
        group_key = rec["group_key"]
        for label in rec["tags"]:
            bucket = out[label]
            bucket["rows"] += 1
            bucket["qty"] += rec["qty"]
            bucket["area"] += rec["area"]
            bucket["amount"] += rec["amount"]
            bucket["groups"][group_key] += rec["amount"]
            bucket["group_metrics"][group_key]["qty"] += rec["qty"]
            bucket["group_metrics"][group_key]["area"] += rec["area"]
            bucket["group_metrics"][group_key]["amount"] += rec["amount"]
            bucket["raw_names"][rec["raw_name"]] += 1

    for label, bucket in out.items():
        ranked = bucket["groups"].most_common()
        bucket["top_groups"] = [
            {
                "name": key,
                "amount": amt,
                "qty": bucket["group_metrics"][key]["qty"],
                "area": bucket["group_metrics"][key]["area"],
                "variants": group_stats[key]["variant_count"] if key in group_stats else 0,
            }
            for key, amt in ranked[:6]
        ]
    return out


def find_focus_group(group_stats: Dict[str, Dict], target: str) -> Dict:
    return group_stats.get(target, {
        "group_key": target,
        "qty": 0.0,
        "area": 0.0,
        "amount": 0.0,
        "rows": 0,
        "variant_count": 0,
        "variants": [],
        "raw_names": Counter(),
        "sheets": Counter(),
        "dominant_sheet": "",
    })


def escape(text) -> str:
    return html.escape(str(text), quote=True)


def make_color(index: int) -> str:
    return PALETTE[index % len(PALETTE)]


def make_horizontal_bar_svg(items: List[Dict], width: int = 1040, row_h: int = 34) -> str:
    if not items:
        return '<div class="empty">暂无可展示数据</div>'

    height = 36 + len(items) * row_h + 16
    left = 300
    right = 150
    top = 18
    chart_w = width - left - right
    max_value = max(item["value"] for item in items) or 1.0
    tick_values = [0, max_value * 0.25, max_value * 0.5, max_value * 0.75, max_value]

    def x_for(value: float) -> float:
        return left + chart_w * value / max_value

    grid_lines = []
    for tick in tick_values[1:]:
        x = x_for(tick)
        grid_lines.append(
            f'<line x1="{x:.1f}" y1="{top}" x2="{x:.1f}" y2="{height - 24}" stroke="#e5e7eb" stroke-dasharray="3 4" />'
        )
        grid_lines.append(
            f'<text x="{x:.1f}" y="{height - 6}" text-anchor="middle" font-size="11" fill="#64748b">{escape(format_money(tick)) if max_value > 1000 else escape(format_qty(tick))}</text>'
        )

    rows = []
    for idx, item in enumerate(items):
        y = top + idx * row_h
        value = item["value"]
        label = escape(truncate_text(item["label"], 28))
        bar_w = chart_w * value / max_value
        color = item.get("color") or make_color(idx)
        rows.append(
            f"""
            <g>
              <text x="12" y="{y + 20}" font-size="13" fill="#0f172a">{label}</text>
              <rect x="{left}" y="{y + 6}" width="{chart_w}" height="18" rx="9" fill="#eef2ff" />
              <rect x="{left}" y="{y + 6}" width="{max(3, bar_w):.1f}" height="18" rx="9" fill="{color}" />
              <text x="{width - 12}" y="{y + 20}" text-anchor="end" font-size="13" fill="#0f172a">{escape(item['value_text'])}</text>
            </g>
            """
        )

    return f"""
    <svg class="chart-svg" viewBox="0 0 {width} {height}" role="img" aria-label="bar chart">
      <rect x="0" y="0" width="{width}" height="{height}" rx="8" fill="white" />
      {' '.join(grid_lines)}
      {' '.join(rows)}
    </svg>
    """


def render_chip(text: str, tone: str = "neutral") -> str:
    return f'<span class="chip {tone}">{escape(text)}</span>'


def render_variant_list(variants: List[Tuple[str, int]], limit: int = 4) -> str:
    if not variants:
        return '<span class="muted">无</span>'
    parts = []
    for name, count in variants[:limit]:
        parts.append(render_chip(f"{name} ×{count}", "soft"))
    if len(variants) > limit:
        parts.append(render_chip(f"+{len(variants) - limit} 个", "neutral"))
    return "".join(parts)


def render_group_card(group: Dict, title: str) -> str:
    variants = group.get("variants", [])
    return f"""
    <div class="focus-card">
      <div class="focus-head">
        <div>
          <div class="focus-title">{escape(title)}</div>
          <div class="focus-sub">{escape(group['group_key'])}</div>
        </div>
        <div class="focus-badge">{group.get('variant_count', 0)} 个 SKU</div>
      </div>
      <div class="focus-metrics">
        <div><span>数量</span><strong>{format_qty(group['qty'])}</strong></div>
        <div><span>面积</span><strong>{format_area(group['area'])} ㎡</strong></div>
        <div><span>金额</span><strong>{format_money(group['amount'])}</strong></div>
        <div><span>来源</span><strong>{escape(group.get('dominant_sheet', ''))}</strong></div>
      </div>
      <div class="focus-variants">{render_variant_list(variants)}</div>
    </div>
    """


def render_table(rows: List[Dict], columns: List[Tuple[str, str]], table_class: str = "") -> str:
    head = "".join(f"<th>{escape(label)}</th>" for label, _ in columns)
    body_rows = []
    for row in rows:
        cells = []
        for _, key in columns:
            value = row.get(key, "")
            if isinstance(value, float):
                if key in {"qty"}:
                    value = format_qty(value)
                elif key in {"area"}:
                    value = f"{format_area(value)} ㎡"
                elif key in {"amount"}:
                    value = format_money(value)
            cells.append(f"<td>{escape(value)}</td>")
        body_rows.append("<tr>" + "".join(cells) + "</tr>")
    return f"""
    <div class="table-wrap {table_class}">
      <table>
        <thead><tr>{head}</tr></thead>
        <tbody>{''.join(body_rows)}</tbody>
      </table>
    </div>
    """


def render_group_table(group_stats: Dict[str, Dict], limit: int = 15) -> str:
    rows = sorted(group_stats.values(), key=lambda x: x["amount"], reverse=True)[:limit]
    table_rows = []
    for idx, g in enumerate(rows, start=1):
        variant_preview = "；".join(name for name, _ in g["variants"][:3])
        if len(g["variants"]) > 3:
            variant_preview += f" 等 {len(g['variants'])} 个SKU"
        table_rows.append(
            {
                "rank": idx,
                "group": g["group_key"],
                "variants": variant_preview,
                "variant_count": g["variant_count"],
                "rows": g["rows"],
                "qty": g["qty"],
                "area": g["area"],
                "amount": g["amount"],
                "source": g["dominant_sheet"],
            }
        )
    columns = [
        ("排名", "rank"),
        ("产品级名称", "group"),
        ("原始SKU", "variants"),
        ("SKU数", "variant_count"),
        ("明细行", "rows"),
        ("数量", "qty"),
        ("面积", "area"),
        ("金额", "amount"),
        ("主来源", "source"),
    ]
    return render_table(table_rows, columns)


def render_sheet_table(sheet_stats: Dict[str, Dict]) -> str:
    rows = sorted(sheet_stats.values(), key=lambda x: x["amount"], reverse=True)
    table_rows = []
    for idx, s in enumerate(rows, start=1):
        table_rows.append(
            {
                "rank": idx,
                "sheet": s["sheet"],
                "rows": s["rows"],
                "qty": s["qty"],
                "area": s["area"],
                "amount": s["amount"],
            }
        )
    columns = [
        ("排名", "rank"),
        ("来源表", "sheet"),
        ("有效行", "rows"),
        ("数量", "qty"),
        ("面积", "area"),
        ("金额", "amount"),
    ]
    return render_table(table_rows, columns)


def render_label_cards(label_stats: Dict[str, Dict], total_amount: float) -> str:
    cards = []
    for idx, label in enumerate(["夹丝", "透明", "烤漆", "艺术玻璃"]):
        bucket = label_stats[label]
        items = bucket["top_groups"]
        bars = []
        top_amt = items[0]["amount"] if items else 0
        for item_idx, item in enumerate(items[:5]):
            bars.append(
                f"""
                <div class="mini-row">
                  <span class="mini-name">{escape(truncate_text(item['name'], 16))}</span>
                  <span class="mini-bar"><i style="width:{max(6, item['amount'] / (top_amt or 1) * 100):.1f}%; background:{make_color(item_idx + idx)}"></i></span>
                  <span class="mini-value">{format_money(item['amount'])}</span>
                </div>
                """
            )
        cards.append(
            f"""
            <div class="label-card">
              <div class="label-top">
                <div>
                  <div class="label-name">{escape(label)}</div>
                  <div class="label-sub">{bucket['rows']} 行 · {format_qty(bucket['qty'])} 件 · {format_money(bucket['amount'])}</div>
                </div>
                <div class="label-badge">{percent(bucket['amount'], total_amount)}</div>
              </div>
              <div class="label-bars">{''.join(bars) if bars else '<div class="empty">暂无命中</div>'}</div>
            </div>
            """
        )
    return "\n".join(cards)


def make_summary_insight(records: List[Dict], group_stats: Dict[str, Dict]) -> str:
    top_groups = sorted(group_stats.values(), key=lambda x: x["amount"], reverse=True)[:3]
    pieces = []
    for g in top_groups:
        pieces.append(f"{g['group_key']}（{format_money(g['amount'])}）")
    return "；".join(pieces)


def generate_report(
    xlsx_path: Path,
    output_path: Path,
    records: List[Dict],
    excluded: List[Dict],
    sheet_stats: Dict[str, Dict],
    group_stats: Dict[str, Dict],
    label_stats: Dict[str, Dict],
):
    total_qty = sum(r["qty"] for r in records)
    total_area = sum(r["area"] for r in records)
    total_amount = sum(r["amount"] for r in records)
    total_rows = len(records)
    total_groups = len(group_stats)
    total_sheets = len(sheet_stats)
    excluded_rows = len(excluded)
    summary_insight = make_summary_insight(records, group_stats)

    focus_lm012 = find_focus_group(group_stats, "LM-012")
    focus_super_white = find_focus_group(group_stats, "超白钢化")

    product_chart_items = [
        {
            "label": g["group_key"],
            "value": g["amount"],
            "value_text": format_money(g["amount"]),
            "color": make_color(i),
        }
        for i, g in enumerate(sorted(group_stats.values(), key=lambda x: x["amount"], reverse=True)[:10])
    ]
    sheet_chart_items = [
        {
            "label": s["sheet"],
            "value": s["amount"],
            "value_text": format_money(s["amount"]),
            "color": make_color(i),
        }
        for i, s in enumerate(sorted(sheet_stats.values(), key=lambda x: x["amount"], reverse=True))
    ]
    label_chart_items = [
        {
            "label": label,
            "value": label_stats[label]["amount"],
            "value_text": format_money(label_stats[label]["amount"]),
            "color": make_color(i),
        }
        for i, label in enumerate(["夹丝", "透明", "烤漆", "艺术玻璃"])
    ]

    excluded_examples = excluded[:8]
    excluded_html = "".join(
        f"<li><b>{escape(item['sheet'])}</b> 第 {item['row']} 行：{escape(item['name'] or '空白')}（{escape(item['reason'])}）</li>"
        for item in excluded_examples
    )

    report_html = 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 {{
      --bg: #f5f7fb;
      --panel: #ffffff;
      --line: #dbe2ea;
      --text: #0f172a;
      --muted: #64748b;
      --soft: #eef2ff;
      --accent: #2563eb;
      --accent-2: #14b8a6;
      --accent-3: #f59e0b;
      --accent-4: #ef4444;
      --shadow: 0 12px 30px rgba(15, 23, 42, 0.08);
    }}
    * {{ box-sizing: border-box; }}
    body {{
      margin: 0;
      background: var(--bg);
      color: var(--text);
      font-family: "PingFang SC", "Microsoft YaHei", "Noto Sans SC", system-ui, -apple-system, BlinkMacSystemFont, sans-serif;
      line-height: 1.5;
    }}
    .wrap {{
      max-width: 1480px;
      margin: 0 auto;
      padding: 24px;
    }}
    .hero {{
      background: linear-gradient(135deg, #0f172a 0%, #1e293b 52%, #0f766e 100%);
      color: white;
      border-radius: 8px;
      padding: 28px;
      box-shadow: var(--shadow);
    }}
    .hero-top {{
      display: grid;
      grid-template-columns: 1.5fr 1fr;
      gap: 18px;
      align-items: start;
    }}
    .title {{
      font-size: 32px;
      font-weight: 800;
      letter-spacing: 0;
      margin: 0 0 8px 0;
    }}
    .subtitle {{
      color: rgba(255,255,255,0.82);
      margin: 0 0 16px 0;
      max-width: 980px;
      font-size: 14px;
    }}
    .chips {{
      display: flex;
      flex-wrap: wrap;
      gap: 8px;
    }}
    .chip {{
      display: inline-flex;
      align-items: center;
      gap: 6px;
      border-radius: 999px;
      border: 1px solid transparent;
      padding: 6px 10px;
      font-size: 12px;
      font-weight: 600;
      white-space: nowrap;
    }}
    .chip.soft {{ background: rgba(255,255,255,0.12); color: white; border-color: rgba(255,255,255,0.16); }}
    .chip.neutral {{ background: rgba(255,255,255,0.08); color: rgba(255,255,255,0.86); border-color: rgba(255,255,255,0.12); }}
    .hero-metrics {{
      display: grid;
      grid-template-columns: repeat(2, minmax(0, 1fr));
      gap: 10px;
    }}
    .hero-metric {{
      background: rgba(255,255,255,0.08);
      border: 1px solid rgba(255,255,255,0.12);
      border-radius: 8px;
      padding: 14px 14px 12px;
    }}
    .hero-metric span {{
      display: block;
      color: rgba(255,255,255,0.72);
      font-size: 12px;
      margin-bottom: 6px;
    }}
    .hero-metric strong {{
      font-size: 24px;
      line-height: 1.1;
    }}
    .section {{
      margin-top: 18px;
      background: var(--panel);
      border: 1px solid #e5e7eb;
      border-radius: 8px;
      box-shadow: var(--shadow);
      overflow: hidden;
    }}
    .section-head {{
      padding: 18px 20px 10px;
      border-bottom: 1px solid #edf2f7;
    }}
    .section-title {{
      font-size: 18px;
      font-weight: 800;
      margin: 0 0 4px 0;
    }}
    .section-desc {{
      margin: 0;
      color: var(--muted);
      font-size: 13px;
    }}
    .section-body {{
      padding: 18px 20px 20px;
    }}
    .kpis {{
      display: grid;
      grid-template-columns: repeat(6, minmax(0, 1fr));
      gap: 12px;
    }}
    .kpi {{
      background: #fff;
      border: 1px solid #e5e7eb;
      border-radius: 8px;
      padding: 14px;
      box-shadow: 0 2px 8px rgba(15,23,42,0.03);
      min-height: 92px;
    }}
    .kpi span {{
      color: var(--muted);
      font-size: 12px;
      display: block;
      margin-bottom: 8px;
    }}
    .kpi strong {{
      font-size: 22px;
      display: block;
      margin-bottom: 4px;
    }}
    .kpi em {{
      font-style: normal;
      color: var(--muted);
      font-size: 12px;
    }}
    .grid-2 {{
      display: grid;
      grid-template-columns: 1.35fr 0.95fr;
      gap: 16px;
      align-items: start;
    }}
    .panel {{
      border: 1px solid #e5e7eb;
      border-radius: 8px;
      background: #fff;
      padding: 16px;
    }}
    .panel-head {{
      display: flex;
      align-items: baseline;
      justify-content: space-between;
      gap: 10px;
      margin-bottom: 12px;
    }}
    .panel-head h3 {{
      margin: 0;
      font-size: 15px;
    }}
    .panel-head p {{
      margin: 0;
      color: var(--muted);
      font-size: 12px;
    }}
    .focus-grid {{
      display: grid;
      grid-template-columns: repeat(2, minmax(0, 1fr));
      gap: 14px;
    }}
    .focus-card {{
      border: 1px solid #e5e7eb;
      border-radius: 8px;
      background: linear-gradient(180deg, #ffffff, #f8fafc);
      padding: 14px;
    }}
    .focus-head {{
      display: flex;
      justify-content: space-between;
      gap: 10px;
      align-items: start;
      margin-bottom: 12px;
    }}
    .focus-title {{
      font-size: 20px;
      font-weight: 800;
      margin-bottom: 4px;
    }}
    .focus-sub {{
      color: var(--muted);
      font-size: 12px;
      word-break: break-all;
    }}
    .focus-badge {{
      background: #ecfeff;
      color: #0f766e;
      border: 1px solid #99f6e4;
      border-radius: 999px;
      padding: 4px 10px;
      font-size: 12px;
      font-weight: 700;
      white-space: nowrap;
    }}
    .focus-metrics {{
      display: grid;
      grid-template-columns: repeat(2, minmax(0, 1fr));
      gap: 10px;
      margin-bottom: 12px;
    }}
    .focus-metrics div {{
      background: white;
      border: 1px solid #eef2f7;
      border-radius: 8px;
      padding: 10px;
    }}
    .focus-metrics span {{
      display: block;
      color: var(--muted);
      font-size: 12px;
      margin-bottom: 4px;
    }}
    .focus-metrics strong {{
      font-size: 16px;
    }}
    .focus-variants {{
      display: flex;
      flex-wrap: wrap;
      gap: 8px;
    }}
    .soft {{
      background: #f1f5f9;
      color: #0f172a;
      border-color: #e2e8f0;
    }}
    .label-grid {{
      display: grid;
      grid-template-columns: repeat(4, minmax(0, 1fr));
      gap: 14px;
    }}
    .label-card {{
      border: 1px solid #e5e7eb;
      border-radius: 8px;
      background: #fff;
      padding: 14px;
      min-height: 240px;
    }}
    .label-top {{
      display: flex;
      justify-content: space-between;
      gap: 12px;
      align-items: start;
      margin-bottom: 12px;
    }}
    .label-name {{
      font-size: 18px;
      font-weight: 800;
      margin-bottom: 4px;
    }}
    .label-sub {{
      color: var(--muted);
      font-size: 12px;
    }}
    .label-badge {{
      background: #eff6ff;
      border: 1px solid #bfdbfe;
      color: #1d4ed8;
      border-radius: 999px;
      padding: 4px 10px;
      font-size: 12px;
      font-weight: 700;
      white-space: nowrap;
    }}
    .mini-row {{
      display: grid;
      grid-template-columns: 92px 1fr 98px;
      gap: 10px;
      align-items: center;
      margin-bottom: 10px;
    }}
    .mini-name {{
      font-size: 12px;
      color: #334155;
      overflow: hidden;
      text-overflow: ellipsis;
      white-space: nowrap;
    }}
    .mini-bar {{
      display: block;
      height: 8px;
      background: #eef2f7;
      border-radius: 999px;
      overflow: hidden;
    }}
    .mini-bar i {{
      display: block;
      height: 100%;
      border-radius: 999px;
    }}
    .mini-value {{
      font-size: 12px;
      text-align: right;
      color: #0f172a;
      font-weight: 600;
    }}
    .table-wrap {{
      overflow-x: auto;
    }}
    table {{
      width: 100%;
      border-collapse: collapse;
      min-width: 1080px;
    }}
    th, td {{
      border-bottom: 1px solid #edf2f7;
      padding: 11px 10px;
      text-align: left;
      vertical-align: top;
      font-size: 13px;
    }}
    th {{
      position: sticky;
      top: 0;
      background: #f8fafc;
      color: #0f172a;
      font-weight: 800;
      z-index: 1;
    }}
    tbody tr:nth-child(even) td {{
      background: #fbfdff;
    }}
    .chart-svg {{
      width: 100%;
      height: auto;
      display: block;
    }}
    .insight {{
      display: grid;
      grid-template-columns: 1.2fr 0.8fr;
      gap: 16px;
      align-items: start;
    }}
    .note {{
      background: #fffbeb;
      border: 1px solid #fde68a;
      color: #92400e;
      border-radius: 8px;
      padding: 14px 16px;
      font-size: 13px;
    }}
    .note ul {{
      margin: 8px 0 0 18px;
      padding: 0;
    }}
    .note li {{
      margin-bottom: 6px;
    }}
    .empty {{
      color: var(--muted);
      font-size: 13px;
      padding: 12px 0;
    }}
    .muted {{
      color: var(--muted);
    }}
    .footer {{
      margin: 18px 0 8px;
      color: var(--muted);
      font-size: 12px;
      text-align: center;
    }}
    @media (max-width: 1200px) {{
      .hero-top, .grid-2, .insight {{
        grid-template-columns: 1fr;
      }}
      .kpis, .label-grid, .focus-grid {{
        grid-template-columns: repeat(2, minmax(0, 1fr));
      }}
    }}
    @media (max-width: 760px) {{
      .wrap {{ padding: 14px; }}
      .hero {{ padding: 20px; }}
      .title {{ font-size: 24px; }}
      .kpis, .label-grid, .focus-grid, .hero-metrics {{
        grid-template-columns: 1fr;
      }}
      table {{ min-width: 920px; }}
      .mini-row {{
        grid-template-columns: 72px 1fr 86px;
      }}
    }}
  </style>
</head>
<body>
  <div class="wrap">
    <section class="hero">
      <div class="hero-top">
        <div>
          <h1 class="title">玻璃明细数据报表</h1>
          <p class="subtitle">
            以“料单编号聚合 + 厚度 SKU 归并 + 标签并行统计”为主线，整合来源表中的有效明细。
            已优先合并以 <b>LM-</b> 开头的同编号产品，并把 <b>超白钢化</b> 这类厚度 SKU 统一到产品级别。
          </p>
          <div class="chips">
            {render_chip(f"文件：{xlsx_path.name}", "soft")}
            {render_chip(f"有效明细 {total_rows} 行", "soft")}
            {render_chip(f"产品级 {total_groups} 组", "soft")}
            {render_chip(f"已剔除 {excluded_rows} 条汇总/空白行", "soft")}
          </div>
        </div>
        <div class="hero-metrics">
          <div class="hero-metric"><span>总数量</span><strong>{format_qty(total_qty)}</strong></div>
          <div class="hero-metric"><span>总面积</span><strong>{format_area(total_area)} ㎡</strong></div>
          <div class="hero-metric"><span>总金额</span><strong>{format_money(total_amount)}</strong></div>
          <div class="hero-metric"><span>来源表</span><strong>{total_sheets}</strong></div>
        </div>
      </div>
    </section>

    <section class="section">
      <div class="section-head">
        <h2 class="section-title">统计概览</h2>
        <p class="section-desc">先看整体规模，再往下看产品级聚合、标签维度和来源表分布。</p>
      </div>
      <div class="section-body">
        <div class="kpis">
          <div class="kpi"><span>有效记录</span><strong>{total_rows}</strong><em>去掉总计/空白行后</em></div>
          <div class="kpi"><span>产品级分组</span><strong>{total_groups}</strong><em>同编号、同家族归并</em></div>
          <div class="kpi"><span>原始SKU数</span><strong>{sum(g['variant_count'] for g in group_stats.values())}</strong><em>去重后的原始名称数</em></div>
          <div class="kpi"><span>夹丝命中</span><strong>{label_stats['夹丝']['rows']}</strong><em>可与其他标签重叠</em></div>
          <div class="kpi"><span>透明命中</span><strong>{label_stats['透明']['rows']}</strong><em>可与其他标签重叠</em></div>
          <div class="kpi"><span>烤漆/艺术</span><strong>{label_stats['烤漆']['rows']} / {label_stats['艺术玻璃']['rows']}</strong><em>双维并行统计</em></div>
        </div>
      </div>
    </section>

    <section class="section">
      <div class="section-head">
        <h2 class="section-title">规则聚合重点</h2>
        <p class="section-desc">以下两组是这次报表里最关键的归并逻辑，和你的统计规则一一对应。</p>
      </div>
      <div class="section-body">
        <div class="focus-grid">
          {render_group_card(focus_lm012, "LM 编号聚合")}
          {render_group_card(focus_super_white, "超白钢化家族")}
        </div>
      </div>
    </section>

    <section class="section">
      <div class="section-head">
        <h2 class="section-title">产品级别 Top 10</h2>
        <p class="section-desc">按金额排序后的产品级统计，已把同编号和同家族 SKU 聚合到一条产品线。</p>
      </div>
      <div class="section-body">
        <div class="grid-2">
          <div class="panel">
            <div class="panel-head">
              <div>
                <h3>产品金额排行</h3>
                <p>看哪个产品级组合贡献了最多销售额。</p>
              </div>
            </div>
            {make_horizontal_bar_svg(product_chart_items)}
          </div>
          <div class="panel">
            <div class="panel-head">
              <div>
                <h3>来源表金额排行</h3>
                <p>不同 sheet 的金额贡献和有效行数分布。</p>
              </div>
            </div>
            {make_horizontal_bar_svg(sheet_chart_items)}
          </div>
        </div>
      </div>
    </section>

    <section class="section">
      <div class="section-head">
        <h2 class="section-title">标签维度</h2>
        <p class="section-desc">四个维度按名称关键词和专类 sheet 共同归类，标签之间允许重叠，因此合计不会等于总量。</p>
      </div>
      <div class="section-body">
        <div class="panel" style="margin-bottom:16px;">
          <div class="panel-head">
            <div>
              <h3>四大标签金额对比</h3>
              <p>从多个来源里单独看夹丝、透明、烤漆、艺术玻璃。</p>
            </div>
          </div>
          {make_horizontal_bar_svg(label_chart_items)}
        </div>
        <div class="label-grid">
          {render_label_cards(label_stats, total_amount)}
        </div>
      </div>
    </section>

    <section class="section">
      <div class="section-head">
        <h2 class="section-title">产品级明细表</h2>
        <p class="section-desc">按产品级统计前 15 条，便于回看每个聚合组对应了哪些原始 SKU。</p>
      </div>
      <div class="section-body">
        {render_group_table(group_stats, 15)}
      </div>
    </section>

    <section class="section">
      <div class="section-head">
        <h2 class="section-title">来源表明细</h2>
        <p class="section-desc">用于看各 sheet 的有效行、数量、面积和金额总览。</p>
      </div>
      <div class="section-body">
        {render_sheet_table(sheet_stats)}
      </div>
    </section>

    <section class="section">
      <div class="section-head">
        <h2 class="section-title">数据质量说明</h2>
        <p class="section-desc">已自动剔除每个 sheet 的汇总行、空白行和 0 值行，避免把总计重复计入图表。</p>
      </div>
      <div class="section-body">
        <div class="insight">
          <div class="note">
            <b>已排除的典型行</b>
            <ul>
              {excluded_html}
            </ul>
            <div style="margin-top:10px;">
              这类行主要是 <b>总计/合计</b>、空白行或 0 值占位行。它们不会进入图表和总表。
            </div>
          </div>
          <div class="panel">
            <div class="panel-head">
              <div>
                <h3>自动摘要</h3>
                <p>顶部最强的几条产品级记录。</p>
              </div>
            </div>
            <div style="font-size:14px;color:#0f172a;">
              {escape(summary_insight)}
            </div>
            <div style="margin-top:14px;color:var(--muted);font-size:13px;">
              这份报表的重点，是把编码型料单和厚度型 SKU 先做产品级归并，再对四个标签分别统计。
              这样既能看总盘，也能回溯每个 SKU 的原始来源。
            </div>
          </div>
        </div>
      </div>
    </section>

    <div class="footer">
      生成时间：{datetime.now().strftime("%Y-%m-%d %H:%M:%S")} · 数据来源：{escape(str(xlsx_path))}
    </div>
  </div>
</body>
</html>
"""

    output_path.write_text(report_html, encoding="utf-8")


def main():
    parser = argparse.ArgumentParser(description="Build glass detail HTML report")
    parser.add_argument(
        "--input",
        default=r"D:\edge下载文件\玻璃明细.xlsx",
        help="Excel file path",
    )
    parser.add_argument(
        "--output",
        default=None,
        help="Output HTML path",
    )
    args = parser.parse_args()

    xlsx_path = Path(args.input)
    if not xlsx_path.exists():
        raise FileNotFoundError(xlsx_path)

    out_path = Path(args.output) if args.output else Path("outputs") / "glass_detail_report_20260525" / "glass_detail_report.html"
    out_path.parent.mkdir(parents=True, exist_ok=True)

    records, excluded = load_records(xlsx_path)
    sheet_stats = build_sheet_stats(records)
    group_stats = build_group_stats(records)
    label_stats = build_label_stats(records, group_stats)

    generate_report(
        xlsx_path=xlsx_path,
        output_path=out_path,
        records=records,
        excluded=excluded,
        sheet_stats=sheet_stats,
        group_stats=group_stats,
        label_stats=label_stats,
    )

    print(
        {
            "input": str(xlsx_path),
            "output": str(out_path),
            "records": len(records),
            "excluded": len(excluded),
            "groups": len(group_stats),
            "sheets": len(sheet_stats),
        }
    )


if __name__ == "__main__":
    main()