from __future__ import annotations

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

import numpy as np
import pandas as pd
from pandas.errors import PerformanceWarning
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_CSV = Path("/Users/zhangling/Documents/2026年事业部工作/2026年销管/【1】招投标信息-乙方宝/7月线索导入模板-按上月CRM模板-剔除无中标单位-法人电话补充.csv")
OUT_DIR = REPO_ROOT / "docs" / "final-results" / "20260823-yifangbao-july-leads-analysis"
OUT_XLSX = OUT_DIR / "20260823-yifangbao-july-leads-analysis.xlsx"
OUT_HTML = OUT_DIR / "index.html"
DESKTOP_DIR = Path("/Users/zhangling/Desktop/Codex/乙方宝-中标项目分析/20260823-yifangbao-july-leads-analysis")

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

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


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)
    if not text:
        return False
    digits = re.sub(r"\D", "", text)
    return len(digits) >= 7 and "****" not in text and "..." not in text


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, re.I):
        return "硬件/设备"
    if re.search(r"平台|系统|软件|数字化|智慧校园|智慧院区|信息化|弱电智能化", text):
        return "系统/平台"
    if re.search(r"智慧食堂|食堂", text):
        return "智慧食堂综合"
    if re.search(r"网上超市|综合零售|合同履约验收|货物买卖合同", text):
        return "零售/合同验收"
    return "其他"


def relevance(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 "边缘/需复核"
    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 += 32
        elif amount >= 5_000_000:
            score += 26
        elif amount >= 1_000_000:
            score += 20
        elif amount >= 300_000:
            score += 12
        else:
            score += 5
    if row.get("中标方电话有效"):
        score += 10
    if row.get("天眼查电话有效"):
        score += 8
    if row.get("招标方电话有效"):
        score += 6
    if normalize_text(row.get("天眼查法定代表人")):
        score += 4
    if row.get("客户类型") in {"银行/金融", "学校/教育", "医疗卫生", "政府/事业"}:
        score += 10
    elif row.get("客户类型") == "企业/国企":
        score += 6
    if row.get("相关性") == "核心相关":
        score += 10
    elif row.get("相关性") == "相邻机会":
        score += 5
    elif row.get("相关性") == "低相关/需复核":
        score -= 6
    score += min(max(company_counts.get(normalize_text(row.get("公司名称")), 1) - 1, 0) * 2, 10)
    date = row.get("日期")
    if pd.notna(date):
        if date >= pd.Timestamp("2026-08-01"):
            score += 8
        elif date >= pd.Timestamp("2026-07-24"):
            score += 6
        else:
            score += 3
    if score >= 70:
        grade = "A-优先跟进"
    elif score >= 52:
        grade = "B-重点培育"
    elif score >= 35:
        grade = "C-常规触达"
    else:
        grade = "D-复核/补全"
    return int(score), grade


def lead_action(row: pd.Series) -> str:
    if row.get("相关性") in {"边缘/需复核", "低相关/需复核"}:
        return "先复核项目是否属于智慧食堂/数科目标场景，再决定是否导入跟进"
    if row.get("优先级", "").startswith("A"):
        return "24小时内跟进，核实采购范围、项目阶段和可切入产品包"
    if row.get("优先级", "").startswith("B"):
        return "3个工作日内触达，优先确认决策链和后续扩展机会"
    if pd.isna(row.get("金额数值")):
        return "先补金额口径，再进入常规触达"
    if not row.get("任一电话有效"):
        return "先补可靠电话，再进入常规触达"
    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_source() -> pd.DataFrame:
    warnings.simplefilter("ignore", PerformanceWarning)
    df = pd.read_csv(SOURCE_CSV, dtype=str, encoding="utf-8-sig")
    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-%d")
    df["月份"] = df["日期"].dt.strftime("%Y-%m")
    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.apply(client_type, axis=1)
    df["需求类型"] = df.apply(demand_type, axis=1)
    df["相关性"] = df.apply(relevance, 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()),
            核心相关数=("相关性", lambda x: (x == "核心相关").sum()),
        )
        .reset_index()
        .sort_values(["线索数", "金额合计"], ascending=False)
    )
    out["电话可用率"] = np.where(out["线索数"] > 0, out["电话可用数"] / out["线索数"], 0)
    out["核心相关率"] = np.where(out["线索数"] > 0, out["核心相关数"] / out["线索数"], 0)
    return out.head(top) if top else out


def build_tables(df: pd.DataFrame) -> dict[str, pd.DataFrame]:
    summary = pd.DataFrame(
        [
            ["线索总数", 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["中标方电话有效"].sum()), "条"],
            ["天眼查电话有效", int(df["天眼查电话有效"].sum()), "条"],
            ["疑似重复线索", int((df["疑似重复组大小"] > 1).sum()), "条"],
        ],
        columns=["指标", "数值", "单位"],
    )
    daily = (
        df.groupby("发布日期", dropna=False)
        .agg(
            线索数=("项目名称", "count"),
            金额合计=("金额数值", "sum"),
            A级线索数=("优先级", lambda x: (x == "A-优先跟进").sum()),
            核心相关数=("相关性", lambda x: (x == "核心相关").sum()),
        )
        .reset_index()
        .sort_values("发布日期")
    )
    tables = {
        "summary": summary,
        "daily": daily,
        "owner": grouped(df, "销售线索所有人"),
        "province": grouped(df, "省份简称"),
        "city": grouped(df, "城市简称", 30),
        "client": grouped(df, "客户类型"),
        "demand": grouped(df, "需求类型"),
        "relevance": grouped(df, "相关性"),
        "tiers": grouped(df, "金额层级"),
        "priority": grouped(df, "优先级"),
    }
    tables["company"] = (
        df.groupby("公司名称", dropna=False)
        .agg(
            线索数=("项目名称", "count"),
            金额合计=("金额数值", "sum"),
            最高金额=("金额数值", "max"),
            涉及省份=("省份简称", lambda x: "、".join(sorted(set(x.dropna()))[:8])),
            负责人=("销售线索所有人", lambda x: "、".join(sorted(set(x.dropna()))[:8])),
            电话可用数=("任一电话有效", "sum"),
            核心相关数=("相关性", lambda x: (x == "核心相关").sum()),
        )
        .reset_index()
        .sort_values(["线索数", "金额合计"], ascending=False)
        .head(40)
    )
    top_cols = [
        "优先级",
        "线索评分",
        "相关性",
        "公司名称",
        "项目名称",
        "发布日期",
        "省",
        "市",
        "金额数值",
        "客户类型",
        "需求类型",
        "销售线索所有人",
        "招标单位",
        "中标单位名称",
        "中标单位联系人电话",
        "天眼查电话",
        "建议动作",
        "官网查看地址",
    ]
    tables["top_leads"] = df.sort_values(["线索评分", "金额数值"], ascending=False)[top_cols].head(50)
    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)])
    tables["quality"] = pd.DataFrame(quality_rows, columns=["字段", "有效条数", "缺失/无效条数", "有效率"])
    tables["duplicates"] = df[df["疑似重复组大小"] > 1].sort_values(["疑似重复键", "日期"])[
        ["疑似重复键", "疑似重复组大小", "公司名称", "项目名称", "发布日期", "省", "市", "金额数值", "招标单位", "中标单位名称", "官网查看地址"]
    ]
    detail_cols = [
        "优先级",
        "线索评分",
        "相关性",
        "建议动作",
        "客户类型",
        "需求类型",
        "金额层级",
        "任一电话有效",
        "同公司线索数",
        "公司名称",
        "销售线索所有人",
        "项目名称",
        "发布日期",
        "省",
        "市",
        "金额数值",
        "招标单位",
        "招标单位联系人",
        "招标单位联系人电话",
        "中标单位名称",
        "中标单位联系人",
        "中标单位联系人电话",
        "天眼查法定代表人",
        "天眼查电话",
        "官网查看地址",
    ]
    tables["detail"] = df[detail_cols].sort_values(["优先级", "线索评分"], ascending=[True, False])
    return tables


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}"
    table = Table(displayName=name, ref=ref)
    table.tableStyleInfo = TableStyleInfo(name="TableStyleMedium2", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False)
    ws.add_table(table)


def style_sheet(ws) -> None:
    ws.sheet_view.showGridLines = False
    ws.freeze_panes = "A5"
    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 = 30
    thin = Side(style="thin", color=LINE)
    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.value not in (None, ""):
                cell.border = Border(bottom=thin)
            if cell.row == 4:
                cell.fill = PatternFill("solid", fgColor=HEADER)
                cell.font = Font(name="Arial", bold=True, size=10, color=INK)
    widths = {
        "A": 18,
        "B": 14,
        "C": 16,
        "D": 24,
        "E": 36,
        "F": 16,
        "G": 16,
        "H": 16,
        "I": 16,
        "J": 18,
        "K": 20,
        "L": 18,
        "M": 30,
        "N": 28,
        "O": 20,
        "P": 20,
        "Q": 42,
        "R": 52,
    }
    for col, width in widths.items():
        ws.column_dimensions[col].width = width


def set_number_formats(wb) -> None:
    for ws in wb.worksheets:
        headers = {cell.column: cell.value for cell in ws[4]} if ws.max_row >= 4 else {}
        for row in ws.iter_rows(min_row=5):
            for cell in row:
                header = headers.get(cell.column)
                if header and "金额" in str(header) and isinstance(cell.value, (int, float)):
                    cell.number_format = "#,##0"
                if header and "率" in str(header) 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, start: int, end: int, cat_col: int, val_col: int, anchor: str, horizontal: bool = True) -> None:
    if end <= 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=start, max_row=end)
    cats = Reference(ws, min_col=cat_col, min_row=start + 1, max_row=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, start: int, end: int, cat_col: int, val_col: int, anchor: str) -> None:
    if end <= 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=start, max_row=end)
    cats = Reference(ws, min_col=cat_col, min_row=start + 1, max_row=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, start: int, end: int, cat_col: int, val_col: int, anchor: str) -> None:
    if end <= start:
        return
    chart = DoughnutChart()
    chart.title = title
    chart.height = 7
    chart.width = 10
    data = Reference(ws, min_col=val_col, min_row=start, max_row=end)
    cats = Reference(ws, min_col=cat_col, min_row=start + 1, max_row=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:
        sheets = [
            ("summary", "分析总览"),
            ("daily", "每日趋势"),
            ("top_leads", "优先跟进线索"),
            ("owner", "负责人分析"),
            ("province", "省份分析"),
            ("city", "城市Top30"),
            ("client", "客户类型"),
            ("demand", "需求类型"),
            ("relevance", "相关性复核"),
            ("tiers", "金额层级"),
            ("priority", "优先级分布"),
            ("company", "公司重复线索"),
            ("quality", "数据质量"),
            ("duplicates", "疑似重复"),
            ("detail", "清洗明细"),
        ]
        for key, sheet_name in sheets:
            tables[key].to_excel(writer, sheet_name=sheet_name, index=False, startrow=3)
    wb = load_workbook(OUT_XLSX)
    titles = {
        "分析总览": "7月乙方宝 CRM 线索导入数据分析总览",
        "每日趋势": "发布日期趋势",
        "优先跟进线索": "优先跟进线索 Top50",
        "负责人分析": "销售线索所有人分析",
        "省份分析": "省份分布分析",
        "城市Top30": "城市 Top30 分析",
        "客户类型": "客户类型分析",
        "需求类型": "需求类型分析",
        "相关性复核": "智慧食堂相关性复核",
        "金额层级": "金额层级分析",
        "优先级分布": "线索优先级分布",
        "公司重复线索": "同公司重复出现线索",
        "数据质量": "字段完整性与导入质量",
        "疑似重复": "疑似重复项目清单",
        "清洗明细": "带分类和评分的清洗明细",
    }
    for idx, ws in enumerate(wb.worksheets, start=1):
        ws["A1"] = titles.get(ws.title, ws.title)
        style_sheet(ws)
        if ws.max_row >= 5 and ws.max_column >= 1:
            dummy = pd.DataFrame(columns=[ws.cell(4, c).value for c in range(1, ws.max_column + 1)], index=range(max(ws.max_row - 4, 0)))
            add_table(ws, f"T_{idx:02d}", 4, 1, dummy)
    set_number_formats(wb)
    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, 18), 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_doughnut(wb["优先级分布"], "优先级占比", 4, wb["优先级分布"].max_row, 1, 2, "I4")
    add_bar(wb["数据质量"], "字段有效率", 4, wb["数据质量"].max_row, 1, 4, "F4")
    wb.save(OUT_XLSX)


def report_info(df: pd.DataFrame, tables: dict[str, pd.DataFrame]) -> dict[str, Any]:
    total = len(df)
    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]
    demand_top = tables["demand"].iloc[0]
    relevance_core = int((df["相关性"] == "核心相关").sum())
    return {
        "total": total,
        "amount_count": int(df["金额数值"].notna().sum()),
        "amount_sum": amount_sum,
        "median_amount": float(df["金额数值"].median()),
        "top5_sum": top5_sum,
        "top5_share": top5_sum / amount_sum if amount_sum else 0,
        "a_count": int((df["优先级"] == "A-优先跟进").sum()),
        "b_count": int((df["优先级"] == "B-重点培育").sum()),
        "core_count": relevance_core,
        "core_rate": relevance_core / total if total else 0,
        "winner_phone_count": int(df["中标方电话有效"].sum()),
        "tyc_phone_count": int(df["天眼查电话有效"].sum()),
        "date_min": df["日期"].min().strftime("%Y-%m-%d"),
        "date_max": df["日期"].max().strftime("%Y-%m-%d"),
        "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["金额合计"]),
        "demand_top_name": demand_top["需求类型"],
        "demand_top_count": int(demand_top["线索数"]),
        "dup_count": int((df["疑似重复组大小"] > 1).sum()),
    }


def render_rank_rows(df: pd.DataFrame, label_col: str, value_col: str, limit: int = 8) -> str:
    view = df.head(limit).copy()
    max_value = max(float(view[value_col].max() or 1), 1)
    rows = []
    for _, row in view.iterrows():
        value = float(row[value_col] or 0)
        width = max(4, value / max_value * 100)
        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>{int(value)}</td></tr>"
        )
    return "\n".join(rows)


def build_html(df: pd.DataFrame, tables: dict[str, pd.DataFrame]) -> None:
    info = report_info(df, tables)
    top_rows = []
    for _, row in tables["top_leads"].head(12).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['客户类型']))} / {html.escape(str(row['需求类型']))}</small></td>"
            f"<td>{money(row['金额数值'], '万元')}</td>"
            f"<td>{html.escape(str(row['建议动作']))}</td>"
            "</tr>"
        )
    quality_rows = []
    for _, row in tables["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>7月乙方宝 CRM 线索导入数据分析</title>
  <style>
    :root {{
      --bg: #f8fbfb;
      --ink: #18343a;
      --muted: #607275;
      --line: #d8e2e2;
      --panel: #ffffff;
      --soft: #eef6f5;
      --accent: #1f4e5f;
      --hot: #c96a3d;
      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.18fr) minmax(260px, .82fr);
      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;
    }}
    .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;
    }}
    .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); }}
    .warning {{ border: 1px solid #efd8c9; background: #fff8f3; color: #83451f; padding: 14px 16px; border-radius: 8px; margin-top: 18px; }}
    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>7月乙方宝 CRM 线索导入数据分析</h1>
        <p>基于 CRM 导入模板 CSV，对线索规模、金额集中度、负责人负载、区域/客群结构、智慧食堂相关性、联系方式补全和跟进优先级做业务复盘。</p>
        <div class="actions"><a class="button" href="./{OUT_XLSX.name}">打开分析 Excel</a></div>
      </div>
      <aside class="meta">
        <b>分析口径</b><br>
        数据共 {info['total']} 条，信息发布时间覆盖 {info['date_min']} 至 {info['date_max']}。文件名为 7 月线索，但实际包含 8 月上旬数据，建议汇报时按“7/15-8/12 批次”表述。
      </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>{info['winner_phone_count']}</strong><span>中标方电话有效条数；天眼查电话有效 {info['tyc_phone_count']} 条</span></div>
    </section>

    <section>
      <h2>核心发现</h2>
      <div class="insights">
        <div class="insight"><b>金额规模可观但高度集中</b><p>披露金额合计 {money(info['amount_sum'], '亿元')}，Top5 金额线索合计 {money(info['top5_sum'], '亿元')}，占披露金额 {info['top5_share']*100:.1f}%。大额项目需要先复核是否为智慧食堂单项、餐饮运营或弱电总包。</p></div>
        <div class="insight"><b>联系字段补全效果明显</b><p>中标单位联系人电话有效 {info['winner_phone_count']} 条，占 {info['winner_phone_count']/info['total']*100:.1f}%；天眼查电话有效 {info['tyc_phone_count']} 条，占 {info['tyc_phone_count']/info['total']*100:.1f}%。导入 CRM 后具备直接触达基础。</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>核心相关线索 {info['core_count']} 条，占 {info['core_rate']*100:.1f}%；其余包含智慧校园、弱电智能化、网上超市合同验收等相邻或边缘项目，导入前建议做一次口径确认。</p></div>
      </div>
      <div class="warning">口径提醒：发布日期最高峰并不等于线索生成峰值，CSV 中“信息发布时间”是公告发布时间；若要评估销售团队当日处理效率，需要另取 CRM 创建时间或导入时间。</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['demand'], '需求类型', '线索数')}</tbody></table>
        <table><thead><tr><th>相关性</th><th>线索数</th><th>数量</th></tr></thead><tbody>{render_rank_rows(tables['relevance'], '相关性', '线索数')}</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 copy_to_desktop() -> bool:
    try:
        DESKTOP_DIR.mkdir(parents=True, exist_ok=True)
        shutil.copy2(OUT_HTML, DESKTOP_DIR / "index.html")
        shutil.copy2(OUT_XLSX, DESKTOP_DIR / OUT_XLSX.name)
        return True
    except PermissionError as exc:
        print(f"desktop copy skipped: {exc}")
        return False


def main() -> None:
    df = load_source()
    tables = build_tables(df)
    build_excel(tables)
    build_html(df, tables)
    copied = copy_to_desktop()
    print(f"wrote {OUT_XLSX}")
    print(f"wrote {OUT_HTML}")
    if copied:
        print(f"copied {DESKTOP_DIR}")
    print(f"rows={len(df)} amount_sum={df['金额数值'].sum():.2f} a_leads={(df['优先级'] == 'A-优先跟进').sum()} winner_phone={df['中标方电话有效'].sum()}")


if __name__ == "__main__":
    main()