from __future__ import annotations

import html
import math
import re
from datetime import datetime
from pathlib import Path
from typing import Any

import numpy as np
import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, DoughnutChart, LineChart, Reference
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo


REPO_ROOT = Path(__file__).resolve().parents[1]
SOURCE_XLSX = Path("/Users/zhangling/Documents/2026年事业部工作/2026年销管/【1】招投标信息-乙方宝/6月线索导入模板-0631-0715.xlsx")
OUT_DIR = REPO_ROOT / "docs" / "final-results" / "20260719-yifangbao-leads-analysis"
OUT_XLSX = OUT_DIR / "20260719-yifangbao-leads-analysis.xlsx"
OUT_HTML = OUT_DIR / "index.html"

PRIMARY = "1F4E5F"
ACCENT = "C96A3D"
INK = "18343A"
MUTED = "607275"
SURFACE = "F4F8F8"
HEADER = "DCEBEA"
LINE = "D8E2E2"
WARN = "B54747"
GOOD = "276749"

MISSING_TOKENS = {"", "nan", "none", "null", "暂未公布", "未公布", "暂无", "无", "不详", "-"}


def normalize_text(value: Any) -> str:
    if value is None or (isinstance(value, float) and math.isnan(value)):
        return ""
    text = str(value).strip()
    return "" if text.lower() in MISSING_TOKENS else text


def phone_valid(value: Any) -> bool:
    text = normalize_text(value)
    digits = re.sub(r"\D", "", text)
    return len(digits) >= 7


def clean_province(value: Any) -> str:
    text = normalize_text(value)
    if not text:
        return "未填"
    return (
        text.replace("省", "")
        .replace("市", "")
        .replace("自治区", "")
        .replace("壮族", "")
        .replace("回族", "")
        .replace("维吾尔", "")
        .replace("特别行政区", "")
    )


def clean_city(value: Any) -> str:
    text = normalize_text(value)
    if not text:
        return "未填"
    return text.replace("市", "").replace("自治州", "").replace("地区", "")


def client_type(row: pd.Series) -> str:
    text = f"{normalize_text(row.get('招标单位'))} {normalize_text(row.get('项目名称'))}"
    if re.search(r"银行|建行|农行|中行|工行|邮储|信用社|农商行|金融", text):
        return "银行/金融"
    if re.search(r"学校|大学|学院|教育局|幼儿园|中学|小学|职教|校区|师范", text):
        return "学校/教育"
    if re.search(r"医院|卫生|医学|疾控|疗养", text):
        return "医疗卫生"
    if re.search(r"政府|机关|事业|公安|法院|检察|局|中心|委员会|管委会", text):
        return "政府/事业"
    if re.search(r"电信|移动|联通|烟草|能源|集团|公司|园区|产业|地铁|物业", text):
        return "企业/国企"
    return "其他/未识别"


def demand_type(row: pd.Series) -> str:
    text = f"{normalize_text(row.get('项目名称'))} {normalize_text(row.get('招标单位'))}"
    if re.search(r"维保|维护|运维|单一来源|续费|服务期", text):
        return "运维/续费"
    if re.search(r"餐饮服务|经营服务|运营服务|食材|劳务|食堂餐饮|供应商选型", text):
        return "餐饮运营"
    if re.search(r"设备|机具|终端|刷脸|IC卡|监控|厨房|采购安装|配套", text):
        return "硬件/设备"
    if re.search(r"平台|系统|软件|数字化|智慧校园|智慧院区|信息化", text):
        return "系统/平台"
    if re.search(r"智慧食堂|食堂", text):
        return "智慧食堂综合"
    return "其他"


def amount_tier(amount: float | None) -> str:
    if pd.isna(amount):
        return "未披露"
    if amount >= 10_000_000:
        return "1000万以上"
    if amount >= 5_000_000:
        return "500-1000万"
    if amount >= 1_000_000:
        return "100-500万"
    if amount >= 300_000:
        return "30-100万"
    return "30万以下"


def score_row(row: pd.Series, company_counts: dict[str, int]) -> tuple[int, str]:
    amount = row.get("金额数值")
    score = 0
    if pd.notna(amount):
        if amount >= 10_000_000:
            score += 30
        elif amount >= 5_000_000:
            score += 25
        elif amount >= 1_000_000:
            score += 20
        elif amount >= 300_000:
            score += 12
        else:
            score += 5
    if row.get("招标方电话有效"):
        score += 8
    if row.get("中标方电话有效"):
        score += 7
    if row.get("天眼查电话有效"):
        score += 6
    if normalize_text(row.get("招标单位联系人")):
        score += 3
    if normalize_text(row.get("中标单位联系人")):
        score += 3
    if row.get("客户类型") in {"银行/金融", "学校/教育", "医疗卫生", "政府/事业"}:
        score += 10
    elif row.get("客户类型") == "企业/国企":
        score += 6
    score += min(max(company_counts.get(normalize_text(row.get("公司名称")), 1) - 1, 0) * 2, 8)
    date = row.get("日期")
    if pd.notna(date):
        if date >= pd.Timestamp("2026-07-10"):
            score += 8
        elif date >= pd.Timestamp("2026-07-01"):
            score += 6
        else:
            score += 3
    if row.get("需求类型") in {"系统/平台", "硬件/设备", "智慧食堂综合", "餐饮运营"}:
        score += 5
    if score >= 60:
        grade = "A-优先跟进"
    elif score >= 45:
        grade = "B-重点培育"
    elif score >= 30:
        grade = "C-常规触达"
    else:
        grade = "D-补全后再判断"
    return score, grade


def lead_action(row: pd.Series) -> str:
    missing = []
    if pd.isna(row.get("金额数值")):
        missing.append("补金额")
    if not row.get("招标方电话有效"):
        missing.append("补招标方电话")
    if not (row.get("中标方电话有效") or row.get("天眼查电话有效")):
        missing.append("补中标方电话")
    if row.get("优先级", "").startswith("A"):
        return "24小时内跟进，核实采购范围、落地场景和后续复购/扩展机会"
    if row.get("优先级", "").startswith("B"):
        return "3个工作日内触达，优先补齐关键联系人和预算口径"
    if missing:
        return "先" + "、".join(missing[:2]) + "，再进入常规触达"
    return "进入常规触达池，按区域/行业节奏维护"


def money(value: float | int | None, unit: str = "元") -> str:
    if value is None or pd.isna(value):
        return "-"
    value = float(value)
    if unit == "亿元":
        return f"{value / 100000000:.2f}亿元"
    if unit == "万元":
        return f"{value / 10000:.1f}万元"
    return f"{value:,.0f}"


def pct(part: float, total: float) -> str:
    if not total:
        return "0.0%"
    return f"{part / total * 100:.1f}%"


def load_and_enrich() -> pd.DataFrame:
    df = pd.read_excel(SOURCE_XLSX, sheet_name="天眼查补全", dtype=str)
    for col in df.columns:
        df[col] = df[col].map(normalize_text)
    df["金额数值"] = pd.to_numeric(df["中标金额"].str.replace(",", "", regex=False), errors="coerce")
    df["日期"] = pd.to_datetime(df["信息发布时间"], errors="coerce")
    df["月份"] = df["日期"].dt.strftime("%Y-%m")
    df["发布日期"] = df["日期"].dt.strftime("%Y-%m-%d")
    df["省份简称"] = df["省"].map(clean_province)
    df["城市简称"] = df["市"].map(clean_city)
    df["招标方电话有效"] = df["招标单位联系人电话"].map(phone_valid)
    df["中标方电话有效"] = df["中标单位联系人电话"].map(phone_valid)
    df["天眼查电话有效"] = df["天眼查电话"].map(phone_valid)
    df["任一电话有效"] = df[["招标方电话有效", "中标方电话有效", "天眼查电话有效"]].any(axis=1)
    df["招标方联系人有效"] = df["招标单位联系人"].map(lambda v: bool(normalize_text(v)))
    df["中标方联系人有效"] = df["中标单位联系人"].map(lambda v: bool(normalize_text(v)))
    df["客户类型"] = df.apply(client_type, axis=1)
    df["需求类型"] = df.apply(demand_type, axis=1)
    df["金额层级"] = df["金额数值"].map(amount_tier)
    company_counts = df["公司名称"].value_counts().to_dict()
    scores = df.apply(lambda row: score_row(row, company_counts), axis=1)
    df["线索评分"] = [item[0] for item in scores]
    df["优先级"] = [item[1] for item in scores]
    df["建议动作"] = df.apply(lead_action, axis=1)
    df["同公司线索数"] = df["公司名称"].map(company_counts).fillna(1).astype(int)
    df["疑似重复键"] = (
        df["项目名称"].str.replace(r"\s+", "", regex=True).str[:28]
        + "|"
        + df["招标单位"].fillna("")
        + "|"
        + df["中标单位名称"].fillna("")
    )
    dup_counts = df["疑似重复键"].value_counts()
    df["疑似重复组大小"] = df["疑似重复键"].map(dup_counts).astype(int)
    return df


def grouped(df: pd.DataFrame, by: str, top: int | None = None) -> pd.DataFrame:
    out = (
        df.groupby(by, dropna=False)
        .agg(
            线索数=("项目名称", "count"),
            披露金额数=("金额数值", "count"),
            金额合计=("金额数值", "sum"),
            平均金额=("金额数值", "mean"),
            电话可用数=("任一电话有效", "sum"),
            A级线索数=("优先级", lambda x: (x == "A-优先跟进").sum()),
        )
        .reset_index()
        .sort_values(["线索数", "金额合计"], ascending=False)
    )
    out["电话可用率"] = np.where(out["线索数"] > 0, out["电话可用数"] / out["线索数"], 0)
    return out.head(top) if top else out


def make_tables(df: pd.DataFrame) -> dict[str, pd.DataFrame]:
    summary_rows = [
        ["线索总数", len(df), "条"],
        ["披露金额线索", int(df["金额数值"].notna().sum()), "条"],
        ["披露金额合计", float(df["金额数值"].sum()), "元"],
        ["披露金额中位数", float(df["金额数值"].median()), "元"],
        ["100万以上线索", int((df["金额数值"] >= 1_000_000).sum()), "条"],
        ["A级优先线索", int((df["优先级"] == "A-优先跟进").sum()), "条"],
        ["任一电话有效", int(df["任一电话有效"].sum()), "条"],
        ["疑似重复线索", int((df["疑似重复组大小"] > 1).sum()), "条"],
    ]
    summary = pd.DataFrame(summary_rows, columns=["指标", "数值", "单位"])
    daily = (
        df.groupby("发布日期", dropna=False)
        .agg(线索数=("项目名称", "count"), 金额合计=("金额数值", "sum"), A级线索数=("优先级", lambda x: (x == "A-优先跟进").sum()))
        .reset_index()
        .sort_values("发布日期")
    )
    owner = grouped(df, "销售线索所有人")
    province = grouped(df, "省份简称")
    city = grouped(df, "城市简称", 20)
    client = grouped(df, "客户类型")
    demand = grouped(df, "需求类型")
    tiers = grouped(df, "金额层级")
    priority = grouped(df, "优先级")
    company = (
        df.groupby("公司名称", dropna=False)
        .agg(
            线索数=("项目名称", "count"),
            金额合计=("金额数值", "sum"),
            最高金额=("金额数值", "max"),
            涉及省份=("省份简称", lambda x: "、".join(sorted(set(x.dropna()))[:6])),
            负责人=("销售线索所有人", lambda x: "、".join(sorted(set(x.dropna()))[:6])),
            电话可用数=("任一电话有效", "sum"),
        )
        .reset_index()
        .sort_values(["线索数", "金额合计"], ascending=False)
        .head(30)
    )
    top_leads_cols = [
        "优先级",
        "线索评分",
        "公司名称",
        "项目名称",
        "发布日期",
        "省",
        "市",
        "金额数值",
        "客户类型",
        "需求类型",
        "销售线索所有人",
        "招标单位",
        "中标单位名称",
        "任一电话有效",
        "建议动作",
        "官网查看地址",
    ]
    top_leads = df.sort_values(["线索评分", "金额数值"], ascending=False)[top_leads_cols].head(40)
    quality_rows = []
    for col in ["联系人姓名", "中标金额", "招标单位", "招标单位联系人", "招标单位联系人电话", "中标单位名称", "中标单位联系人", "中标单位联系人电话", "天眼查法定代表人", "天眼查电话"]:
        nonempty = int(df[col].map(lambda v: bool(normalize_text(v))).sum())
        quality_rows.append([col, nonempty, len(df) - nonempty, nonempty / len(df)])
    quality = pd.DataFrame(quality_rows, columns=["字段", "有效条数", "缺失/无效条数", "有效率"])
    duplicates = df[df["疑似重复组大小"] > 1].sort_values(["疑似重复键", "日期"])[
        ["疑似重复键", "疑似重复组大小", "公司名称", "项目名称", "发布日期", "省", "市", "金额数值", "招标单位", "中标单位名称", "官网查看地址"]
    ]
    detail_cols = [
        "优先级",
        "线索评分",
        "建议动作",
        "客户类型",
        "需求类型",
        "金额层级",
        "任一电话有效",
        "同公司线索数",
        "公司名称",
        "销售线索所有人",
        "项目名称",
        "发布日期",
        "省",
        "市",
        "金额数值",
        "招标单位",
        "招标单位联系人",
        "招标单位联系人电话",
        "中标单位名称",
        "中标单位联系人",
        "中标单位联系人电话",
        "天眼查法定代表人",
        "天眼查电话",
        "官网查看地址",
    ]
    return {
        "summary": summary,
        "daily": daily,
        "owner": owner,
        "province": province,
        "city": city,
        "client": client,
        "demand": demand,
        "tiers": tiers,
        "priority": priority,
        "company": company,
        "top_leads": top_leads,
        "quality": quality,
        "duplicates": duplicates,
        "detail": df[detail_cols].sort_values(["优先级", "线索评分"], ascending=[True, False]),
    }


def add_table(ws, name: str, start_row: int, start_col: int, df: pd.DataFrame) -> None:
    if df.empty:
        return
    end_row = start_row + len(df)
    end_col = start_col + len(df.columns) - 1
    ref = f"{get_column_letter(start_col)}{start_row}:{get_column_letter(end_col)}{end_row}"
    tab = Table(displayName=name, ref=ref)
    style = TableStyleInfo(name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False)
    tab.tableStyleInfo = style
    ws.add_table(tab)


def style_sheet(ws) -> None:
    ws.sheet_view.showGridLines = False
    for row in ws.iter_rows():
        for cell in row:
            cell.font = Font(name="Arial", size=10, color=INK)
            cell.alignment = Alignment(vertical="center", wrap_text=True)
            if cell.row == 1:
                cell.font = Font(name="Arial", bold=True, size=16, color=INK)
                cell.fill = PatternFill("solid", fgColor=HEADER)
    thin = Side(style="thin", color=LINE)
    for row in ws.iter_rows():
        for cell in row:
            if cell.value not in (None, ""):
                cell.border = Border(bottom=thin)
    widths = {
        "A": 18,
        "B": 14,
        "C": 22,
        "D": 22,
        "E": 18,
        "F": 18,
        "G": 16,
        "H": 16,
        "I": 24,
        "J": 16,
        "K": 36,
        "L": 16,
        "M": 18,
        "N": 18,
        "O": 36,
        "P": 52,
    }
    for col, width in widths.items():
        ws.column_dimensions[col].width = width
    ws.freeze_panes = "A3"


def write_df(ws, df: pd.DataFrame, start_row: int = 3, start_col: int = 1, table_name: str | None = None) -> None:
    for c, col in enumerate(df.columns, start_col):
        ws.cell(start_row, c, col)
    for r, (_, row) in enumerate(df.iterrows(), start_row + 1):
        for c, value in enumerate(row.tolist(), start_col):
            if pd.isna(value):
                value = None
            if isinstance(value, (np.integer,)):
                value = int(value)
            elif isinstance(value, (np.floating,)):
                value = float(value)
            ws.cell(r, c, value)
    if table_name:
        add_table(ws, table_name, start_row, start_col, df)


def set_number_formats(wb) -> None:
    for idx, ws in enumerate(wb.worksheets, start=1):
        for row in ws.iter_rows():
            for cell in row:
                header = ws.cell(3, cell.column).value if ws.max_row >= 3 else None
                if header and "金额" in str(header) and isinstance(cell.value, (int, float)):
                    cell.number_format = "#,##0"
                if header and ("率" in str(header) or str(header).endswith("占比")) and isinstance(cell.value, (int, float)):
                    cell.number_format = "0.0%"
                if header and "评分" in str(header) and isinstance(cell.value, (int, float)):
                    cell.number_format = "0"


def add_bar(ws, title: str, data_start: int, data_end: int, cat_col: int, val_col: int, anchor: str, horizontal: bool = True) -> None:
    if data_end <= data_start:
        return
    chart = BarChart()
    chart.type = "bar" if horizontal else "col"
    chart.style = 10
    chart.title = title
    chart.height = 7
    chart.width = 13
    data = Reference(ws, min_col=val_col, min_row=data_start, max_row=data_end)
    cats = Reference(ws, min_col=cat_col, min_row=data_start + 1, max_row=data_end)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.legend = None
    ws.add_chart(chart, anchor)


def add_line(ws, title: str, data_start: int, data_end: int, cat_col: int, val_col: int, anchor: str) -> None:
    if data_end <= data_start:
        return
    chart = LineChart()
    chart.style = 13
    chart.title = title
    chart.height = 7
    chart.width = 14
    data = Reference(ws, min_col=val_col, min_row=data_start, max_row=data_end)
    cats = Reference(ws, min_col=cat_col, min_row=data_start + 1, max_row=data_end)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    chart.legend = None
    ws.add_chart(chart, anchor)


def add_doughnut(ws, title: str, data_start: int, data_end: int, cat_col: int, val_col: int, anchor: str) -> None:
    if data_end <= data_start:
        return
    chart = DoughnutChart()
    chart.title = title
    chart.height = 7
    chart.width = 10
    data = Reference(ws, min_col=val_col, min_row=data_start, max_row=data_end)
    cats = Reference(ws, min_col=cat_col, min_row=data_start + 1, max_row=data_end)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)
    ws.add_chart(chart, anchor)


def build_excel(tables: dict[str, pd.DataFrame]) -> None:
    OUT_DIR.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(OUT_XLSX, engine="openpyxl") as writer:
        tables["summary"].to_excel(writer, sheet_name="分析总览", index=False, startrow=2)
        tables["daily"].to_excel(writer, sheet_name="每日趋势", index=False, startrow=2)
        tables["top_leads"].to_excel(writer, sheet_name="优先跟进线索", index=False, startrow=2)
        tables["owner"].to_excel(writer, sheet_name="负责人分析", index=False, startrow=2)
        tables["province"].to_excel(writer, sheet_name="省份分析", index=False, startrow=2)
        tables["city"].to_excel(writer, sheet_name="城市Top20", index=False, startrow=2)
        tables["client"].to_excel(writer, sheet_name="客户类型", index=False, startrow=2)
        tables["demand"].to_excel(writer, sheet_name="需求类型", index=False, startrow=2)
        tables["tiers"].to_excel(writer, sheet_name="金额层级", index=False, startrow=2)
        tables["priority"].to_excel(writer, sheet_name="优先级分布", index=False, startrow=2)
        tables["company"].to_excel(writer, sheet_name="公司重复线索", index=False, startrow=2)
        tables["quality"].to_excel(writer, sheet_name="数据质量", index=False, startrow=2)
        tables["duplicates"].to_excel(writer, sheet_name="疑似重复", index=False, startrow=2)
        tables["detail"].to_excel(writer, sheet_name="清洗明细", index=False, startrow=2)

    wb = load_workbook(OUT_XLSX)
    titles = {
        "分析总览": "线索数据分析总览",
        "每日趋势": "每日线索与金额趋势",
        "优先跟进线索": "优先跟进线索 Top40",
        "负责人分析": "销售线索所有人分析",
        "省份分析": "省份分布分析",
        "城市Top20": "城市 Top20 分析",
        "客户类型": "客户类型分析",
        "需求类型": "需求类型分析",
        "金额层级": "金额层级分析",
        "优先级分布": "线索优先级分布",
        "公司重复线索": "同公司重复出现线索",
        "数据质量": "字段完整性与导入质量",
        "疑似重复": "疑似重复项目清单",
        "清洗明细": "带分类和评分的清洗明细",
    }
    for idx, ws in enumerate(wb.worksheets, start=1):
        ws.insert_rows(1)
        ws["A1"] = titles.get(ws.title, ws.title)
        ws["A1"].font = Font(name="Arial", bold=True, size=17, color=INK)
        ws["A1"].fill = PatternFill("solid", fgColor=HEADER)
        ws["A1"].alignment = Alignment(vertical="center")
        ws.row_dimensions[1].height = 28
        style_sheet(ws)
        max_col = ws.max_column
        if ws.max_row >= 4 and max_col >= 1:
            add_table(ws, f"T_{idx:02d}", 4, 1, pd.DataFrame(columns=[ws.cell(4, c).value for c in range(1, max_col + 1)], index=range(max(ws.max_row - 4, 0))))
    set_number_formats(wb)
    # Charts after tables/styles.
    add_line(wb["每日趋势"], "每日线索数", 4, wb["每日趋势"].max_row, 1, 2, "F4")
    add_bar(wb["每日趋势"], "每日披露金额", 4, wb["每日趋势"].max_row, 1, 3, "F20", horizontal=False)
    add_bar(wb["负责人分析"], "负责人线索数", 4, min(wb["负责人分析"].max_row, 14), 1, 2, "I4")
    add_bar(wb["省份分析"], "省份线索数", 4, min(wb["省份分析"].max_row, 16), 1, 2, "I4")
    add_bar(wb["客户类型"], "客户类型线索数", 4, wb["客户类型"].max_row, 1, 2, "I4")
    add_bar(wb["需求类型"], "需求类型线索数", 4, wb["需求类型"].max_row, 1, 2, "I4")
    add_doughnut(wb["金额层级"], "金额层级占比", 4, wb["金额层级"].max_row, 1, 2, "I4")
    add_doughnut(wb["优先级分布"], "优先级占比", 4, wb["优先级分布"].max_row, 1, 2, "I4")
    add_bar(wb["数据质量"], "字段有效率", 4, wb["数据质量"].max_row, 1, 4, "F4")
    wb.save(OUT_XLSX)


def insight_text(df: pd.DataFrame, tables: dict[str, pd.DataFrame]) -> dict[str, Any]:
    total = len(df)
    amount_count = int(df["金额数值"].notna().sum())
    amount_sum = float(df["金额数值"].sum())
    top5_sum = float(df.nlargest(5, "金额数值")["金额数值"].sum())
    owner_top = tables["owner"].iloc[0]
    province_top = tables["province"].iloc[0]
    amount_province_top = tables["province"].sort_values("金额合计", ascending=False).iloc[0]
    client_top = tables["client"].iloc[0]
    demand_top = tables["demand"].iloc[0]
    a_count = int((df["优先级"] == "A-优先跟进").sum())
    b_count = int((df["优先级"] == "B-重点培育").sum())
    phone_count = int(df["任一电话有效"].sum())
    date_min = df["日期"].min().strftime("%Y-%m-%d")
    date_max = df["日期"].max().strftime("%Y-%m-%d")
    return {
        "total": total,
        "amount_count": amount_count,
        "amount_sum": amount_sum,
        "median_amount": float(df["金额数值"].median()),
        "top5_sum": top5_sum,
        "top5_share": top5_sum / amount_sum if amount_sum else 0,
        "owner_top_name": owner_top["销售线索所有人"],
        "owner_top_count": int(owner_top["线索数"]),
        "owner_top_share": int(owner_top["线索数"]) / total,
        "province_top_name": province_top["省份简称"],
        "province_top_count": int(province_top["线索数"]),
        "amount_province_top_name": amount_province_top["省份简称"],
        "amount_province_top_sum": float(amount_province_top["金额合计"]),
        "client_top_name": client_top["客户类型"],
        "client_top_count": int(client_top["线索数"]),
        "demand_top_name": demand_top["需求类型"],
        "demand_top_count": int(demand_top["线索数"]),
        "a_count": a_count,
        "b_count": b_count,
        "phone_count": phone_count,
        "phone_rate": phone_count / total,
        "date_min": date_min,
        "date_max": date_max,
        "duplicate_count": int((df["疑似重复组大小"] > 1).sum()),
    }


def render_rank_rows(df: pd.DataFrame, label_col: str, value_col: str, limit: int = 8, value_fmt: str = "count") -> str:
    rows = []
    view = df.head(limit).copy()
    max_value = max(float(view[value_col].max() or 1), 1)
    for _, row in view.iterrows():
        value = float(row[value_col] or 0)
        width = max(4, value / max_value * 100)
        display = money(value, "万元") if value_fmt == "money" else f"{int(value)}"
        rows.append(
            f"<tr><td>{html.escape(str(row[label_col]))}</td><td><span class='bar'><i style='width:{width:.1f}%'></i></span></td><td>{display}</td></tr>"
        )
    return "\n".join(rows)


def build_html(df: pd.DataFrame, tables: dict[str, pd.DataFrame]) -> None:
    info = insight_text(df, tables)
    top_leads = tables["top_leads"].head(12)
    top_rows = []
    for _, row in top_leads.iterrows():
        top_rows.append(
            "<tr>"
            f"<td>{html.escape(str(row['优先级']))}<br><small>{int(row['线索评分'])}分</small></td>"
            f"<td>{html.escape(str(row['公司名称']))}<br><small>{html.escape(str(row['销售线索所有人']))}</small></td>"
            f"<td>{html.escape(str(row['项目名称']))}</td>"
            f"<td>{html.escape(str(row['客户类型']))}<br><small>{html.escape(str(row['需求类型']))}</small></td>"
            f"<td>{money(row['金额数值'], '万元')}</td>"
            f"<td>{html.escape(str(row['建议动作']))}</td>"
            "</tr>"
        )
    quality = tables["quality"].copy()
    quality_rows = []
    for _, row in quality.iterrows():
        rate = float(row["有效率"])
        quality_rows.append(
            f"<tr><td>{html.escape(str(row['字段']))}</td><td>{int(row['有效条数'])}</td><td>{int(row['缺失/无效条数'])}</td><td>{rate*100:.1f}%</td></tr>"
        )

    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 {{
      --bg: #f8fbfb;
      --ink: #18343a;
      --muted: #607275;
      --line: #d8e2e2;
      --panel: #ffffff;
      --soft: #eef6f5;
      --accent: #1f4e5f;
      --hot: #c96a3d;
      --good: #276749;
      color-scheme: light;
    }}
    * {{ box-sizing: border-box; }}
    body {{
      margin: 0;
      background: var(--bg);
      color: var(--ink);
      font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", "PingFang SC", "Microsoft YaHei", Arial, sans-serif;
      line-height: 1.58;
    }}
    main {{ max-width: 1180px; margin: 0 auto; padding: 42px 24px 64px; }}
    header {{
      display: grid;
      grid-template-columns: minmax(0, 1.2fr) minmax(260px, .8fr);
      gap: 28px;
      align-items: end;
      padding: 26px 0 30px;
      border-top: 7px solid var(--accent);
      border-bottom: 1px solid var(--line);
    }}
    h1 {{ margin: 0; font-size: clamp(30px, 4vw, 50px); line-height: 1.08; letter-spacing: 0; }}
    h2 {{ margin: 34px 0 14px; font-size: 22px; }}
    p {{ margin: 0; color: var(--muted); max-width: 780px; }}
    .meta {{
      border: 1px solid var(--line);
      background: var(--panel);
      border-radius: 8px;
      padding: 18px;
      color: var(--muted);
      font-size: 14px;
    }}
    .metrics {{
      display: grid;
      grid-template-columns: repeat(4, minmax(0, 1fr));
      gap: 12px;
      margin-top: 22px;
    }}
    .metric {{
      background: var(--panel);
      border: 1px solid var(--line);
      border-radius: 8px;
      padding: 16px;
      min-height: 112px;
    }}
    .metric strong {{ display: block; font-size: 26px; color: var(--accent); line-height: 1.15; }}
    .metric span {{ display: block; margin-top: 8px; color: var(--muted); font-size: 13px; }}
    .insights {{
      display: grid;
      grid-template-columns: repeat(2, minmax(0, 1fr));
      gap: 14px;
    }}
    .insight {{
      border-left: 4px solid var(--accent);
      background: var(--panel);
      border-radius: 8px;
      padding: 16px 18px;
      box-shadow: 0 14px 40px rgba(24,52,58,.06);
    }}
    .insight b {{ display: block; margin-bottom: 6px; }}
    .grid2 {{
      display: grid;
      grid-template-columns: repeat(2, minmax(0, 1fr));
      gap: 16px;
    }}
    table {{ width: 100%; border-collapse: collapse; background: var(--panel); border: 1px solid var(--line); table-layout: fixed; }}
    th, td {{ padding: 11px 12px; border-bottom: 1px solid var(--line); text-align: left; vertical-align: top; font-size: 14px; }}
    th {{ background: var(--soft); color: var(--accent); font-size: 13px; }}
    tr:last-child td {{ border-bottom: 0; }}
    small {{ color: var(--muted); }}
    .bar {{ display: block; height: 10px; background: #edf3f3; border-radius: 999px; overflow: hidden; }}
    .bar i {{ display: block; height: 100%; background: var(--accent); }}
    .actions {{ display: flex; gap: 12px; margin-top: 16px; flex-wrap: wrap; }}
    a.button {{
      display: inline-flex;
      min-height: 42px;
      align-items: center;
      justify-content: center;
      padding: 0 18px;
      border-radius: 6px;
      background: var(--accent);
      color: #fff;
      text-decoration: none;
      font-weight: 700;
    }}
    footer {{ margin-top: 30px; color: var(--muted); font-size: 13px; }}
    @media (max-width: 880px) {{
      main {{ padding: 28px 16px 46px; }}
      header, .metrics, .insights, .grid2 {{ grid-template-columns: 1fr; }}
      table {{ table-layout: auto; }}
    }}
  </style>
</head>
<body>
  <main>
    <header>
      <div>
        <h1>乙方宝线索导入数据分析</h1>
        <p>基于 <strong>{info['date_min']} 至 {info['date_max']}</strong> 的 121 条线索，围绕线索规模、金额集中度、负责人负载、区域/客群结构、跟进优先级和导入质量做销售管理分析。</p>
        <div class="actions"><a class="button" href="./{OUT_XLSX.name}">打开分析 Excel</a></div>
      </div>
      <aside class="meta">
        <b>分析口径</b><br>
        金额按“中标金额”可数值化记录统计；电话有效按至少 7 位数字判断；优先级为销售跟进辅助评分，建议结合实际客户关系复核。
      </aside>
    </header>

    <section class="metrics">
      <div class="metric"><strong>{info['total']}</strong><span>线索总数</span></div>
      <div class="metric"><strong>{money(info['amount_sum'], '亿元')}</strong><span>披露金额合计，覆盖 {info['amount_count']} 条</span></div>
      <div class="metric"><strong>{info['a_count']} 条</strong><span>A级优先线索，另有 {info['b_count']} 条 B 级重点培育</span></div>
      <div class="metric"><strong>{pct(info['phone_count'], info['total'])}</strong><span>任一电话有效率，{info['phone_count']} 条可直接触达</span></div>
    </section>

    <section>
      <h2>核心发现</h2>
      <div class="insights">
        <div class="insight"><b>金额高度集中</b><p>Top5 金额线索合计 {money(info['top5_sum'], '亿元')}，占披露金额 {info['top5_share']*100:.1f}%。大额线索应单独复核项目范围，避免餐饮运营、智能化总包与智慧食堂单项混在一个口径里。</p></div>
        <div class="insight"><b>负责人负载不均</b><p>{html.escape(str(info['owner_top_name']))} 名下 {info['owner_top_count']} 条线索，占 {info['owner_top_share']*100:.1f}%；建议对 A/B 级线索设置统一 SLA，避免高量负责人响应延迟。</p></div>
        <div class="insight"><b>区域有两个观察维度</b><p>线索数量最高是 {html.escape(str(info['province_top_name']))}，共 {info['province_top_count']} 条；金额最高集中在 {html.escape(str(info['amount_province_top_name']))}，合计 {money(info['amount_province_top_sum'], '亿元')}。</p></div>
        <div class="insight"><b>数据补全仍是转化前置动作</b><p>当前任一电话有效率为 {info['phone_rate']*100:.1f}%，疑似重复线索 {info['duplicate_count']} 条；导入 CRM 前建议先处理缺电话、缺金额和重复项目。</p></div>
      </div>
    </section>

    <section>
      <h2>排行概览</h2>
      <div class="grid2">
        <table><thead><tr><th>负责人</th><th>线索数</th><th>数量</th></tr></thead><tbody>{render_rank_rows(tables['owner'], '销售线索所有人', '线索数')}</tbody></table>
        <table><thead><tr><th>省份</th><th>线索数</th><th>数量</th></tr></thead><tbody>{render_rank_rows(tables['province'], '省份简称', '线索数')}</tbody></table>
        <table><thead><tr><th>客户类型</th><th>线索数</th><th>数量</th></tr></thead><tbody>{render_rank_rows(tables['client'], '客户类型', '线索数')}</tbody></table>
        <table><thead><tr><th>需求类型</th><th>线索数</th><th>数量</th></tr></thead><tbody>{render_rank_rows(tables['demand'], '需求类型', '线索数')}</tbody></table>
      </div>
    </section>

    <section>
      <h2>优先跟进清单</h2>
      <table>
        <thead><tr><th>优先级</th><th>公司/负责人</th><th>项目</th><th>类型</th><th>金额</th><th>建议动作</th></tr></thead>
        <tbody>{''.join(top_rows)}</tbody>
      </table>
    </section>

    <section>
      <h2>字段完整性</h2>
      <table>
        <thead><tr><th>字段</th><th>有效条数</th><th>缺失/无效</th><th>有效率</th></tr></thead>
        <tbody>{''.join(quality_rows)}</tbody>
      </table>
    </section>

    <footer>输出目录：{OUT_DIR}；生成时间：{datetime.now().strftime('%Y-%m-%d %H:%M')}。</footer>
  </main>
</body>
</html>
"""
    OUT_HTML.write_text(html_text, encoding="utf-8")


def main() -> None:
    df = load_and_enrich()
    tables = make_tables(df)
    build_excel(tables)
    build_html(df, tables)
    print(f"wrote {OUT_XLSX}")
    print(f"wrote {OUT_HTML}")
    print(f"rows={len(df)} amount_sum={df['金额数值'].sum():.2f} a_leads={(df['优先级'] == 'A-优先跟进').sum()}")


if __name__ == "__main__":
    main()