#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026年10月逾期应收（科研）重点清单。

源：2026年10月逾期应收回款计划-总表（回款进度） -科研.xlsx
    使用「附表_逾期&未逾期（汇总）」；逾期判据 = 逾期天数可转数字且逾期金额>0。
课题组映射：数据/ls_groups.json 的 groups[].customers。
    同一往来单位对应多个课题组时，不硬拆金额，标“多课题组（N个）”；
    仅当唯一映射时才写课题组名。
"""
import os
import re
import json
import math
from collections import defaultdict, Counter
from datetime import datetime

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.properties import PageSetupProperties

ROOT = "/Users/liuxinyuan/Desktop/Hermes输出-工作类"
SRC = "/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/doc_190cba4be820_2026年10月逾期应收回款计划-总表（回款进度） -科研.xlsx"
GROUPS = os.path.join(ROOT, "数据", "ls_groups.json")
OUT = os.path.join(ROOT, "经营分析", "2026年10月逾期科研客户重点清单_20261031_V01.xlsx")

NAVY = "1F3864"
BLUE = "2F5597"
LIGHT = "D9E2F3"
PALE = "EEF3FA"
GREY = "F2F2F2"
WHITE = "FFFFFF"
RED = "C00000"
GREEN = "006100"
ORANGE = "FCE4D6"
YELLOW = "FFF2CC"
THIN = Side(style="thin", color="BFBFBF")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)
CENTER = Alignment(horizontal="center", vertical="center", wrap_text=True)
LEFT = Alignment(horizontal="left", vertical="center", wrap_text=True)
RIGHT = Alignment(horizontal="right", vertical="center")
NUM = '#,##0.00;[Red]-#,##0.00'
PCT = '0.0%;[Red]-0.0%'


def sf(v):
    try:
        if v is None or str(v).strip() in ("", "-", "—", "/"):
            return 0.0
        return float(str(v).replace(",", ""))
    except Exception:
        return 0.0


def parse():
    wb = openpyxl.load_workbook(SRC, data_only=True)
    ws = wb["附表_逾期&未逾期（汇总）"]
    rows = []
    for r in range(4, ws.max_row + 1):
        cust = ws.cell(r, 3).value
        if not cust:
            continue
        try:
            days = int(float(ws.cell(r, 9).value))
        except Exception:
            continue
        amt = sf(ws.cell(r, 10).value)
        if amt <= 0:
            continue
        progress = sf(ws.cell(r, 13).value)
        rows.append({
            "dept": ws.cell(r, 1).value or "",
            "sales": ws.cell(r, 2).value or "",
            "customer": str(cust).strip(),
            "doc": ws.cell(r, 4).value or "",
            "contract": ws.cell(r, 5).value or "",
            "biz_date": ws.cell(r, 6).value,
            "due_date": ws.cell(r, 7).value,
            "ar": sf(ws.cell(r, 8).value),
            "days": days,
            "overdue": amt,
            "progress": progress,
            "net": amt - progress,
            "group": "",
            "group_count": 0,
            "group_note": "",
        })
    # group mapping
    gd = json.load(open(GROUPS, encoding="utf-8"))
    cm = defaultdict(list)
    for g in gd.get("groups", []):
        for c in g.get("customers", []) or []:
            cm[str(c).strip()].append(g)
    for x in rows:
        gs = cm.get(x["customer"], [])
        x["group_count"] = len(gs)
        if len(gs) == 1:
            x["group"] = gs[0]["name"]
            x["group_note"] = gs[0]["name"]
        elif len(gs) > 1:
            x["group_note"] = f"多课题组（{len(gs)}个）"
        else:
            x["group_note"] = "未映射"
    return rows, cm


def aging_bucket(d):
    if d <= 30: return "0-30天"
    if d <= 60: return "31-60天"
    if d <= 90: return "61-90天"
    if d <= 180: return "91-180天"
    if d <= 365: return "181-365天"
    return "365天以上"


def agg_customers(rows):
    a = {}
    for x in rows:
        c = x["customer"]
        if c not in a:
            a[c] = {"customer": c, "overdue": 0, "progress": 0, "net": 0, "n": 0,
                    "max_days": 0, "over180": 0, "over365": 0, "sales": set(),
                    "dept": set(), "docs": [], "group_note": x["group_note"],
                    "group_count": x["group_count"], "group": x["group"]}
        y = a[c]
        y["overdue"] += x["overdue"]; y["progress"] += x["progress"]; y["net"] += x["net"]
        y["n"] += 1; y["max_days"] = max(y["max_days"], x["days"])
        if x["days"] > 180: y["over180"] += x["overdue"]
        if x["days"] > 365: y["over365"] += x["overdue"]
        y["sales"].add(x["sales"]); y["dept"].add(x["dept"])
        y["docs"].append(x)
    out = []
    for y in a.values():
        y["sales"] = "、".join(sorted(str(s) for s in y["sales"]))
        y["dept"] = "、".join(sorted(str(s) for s in y["dept"]))
        y["over180_pct"] = y["over180"] / y["overdue"] if y["overdue"] else 0
        y["over365_pct"] = y["over365"] / y["overdue"] if y["overdue"] else 0
        # risk class
        if y["overdue"] >= 15000 and y["over180_pct"] >= 0.60:
            y["risk"] = "A类：金额大+账龄长"
        elif y["max_days"] > 180:
            y["risk"] = "B类：金额小但有长账龄"
        else:
            y["risk"] = "C类：新逾期/短期"
        out.append(y)
    return sorted(out, key=lambda z: -z["overdue"])


def style_title(ws, text, ncols, sub=None):
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ncols)
    c = ws.cell(1, 1, text)
    c.font = Font(name="微软雅黑", bold=True, size=14, color=WHITE)
    c.fill = PatternFill("solid", fgColor=NAVY)
    c.alignment = Alignment(horizontal="left", vertical="center")
    ws.row_dimensions[1].height = 28
    if sub:
        ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=ncols)
        c2 = ws.cell(2, 1, sub)
        c2.font = Font(name="微软雅黑", size=9, color="404040")
        c2.fill = PatternFill("solid", fgColor=PALE)
        c2.alignment = LEFT
        ws.row_dimensions[2].height = 34


def header(ws, row, headers):
    for j, h in enumerate(headers, 1):
        c = ws.cell(row, j, h)
        c.font = Font(name="微软雅黑", bold=True, size=9, color=WHITE)
        c.fill = PatternFill("solid", fgColor=BLUE)
        c.alignment = CENTER
        c.border = BORDER
    ws.row_dimensions[row].height = 34


def cell(ws, r, c, v, fmt=None, align=None, bold=False, fill=None, color="000000"):
    x = ws.cell(r, c, v)
    x.font = Font(name="微软雅黑", size=9, bold=bold, color=color)
    x.border = BORDER
    x.alignment = align or LEFT
    if fmt: x.number_format = fmt
    if fill: x.fill = PatternFill("solid", fgColor=fill)


def widths(ws, arr):
    for i, w in enumerate(arr, 1):
        ws.column_dimensions[get_column_letter(i)].width = w


def autoheight(ws):
    for r in range(1, ws.max_row + 1):
        if r in (1, 2): continue
        lines = 1
        for c in ws[r]:
            if isinstance(c.value, str) and c.value:
                w = ws.column_dimensions[c.column_letter].width or 10
                lines = max(lines, math.ceil(len(c.value) / max(6, w / 2)))
        ws.row_dimensions[r].height = min(120, max(18, 14 * lines + 4))


def build():
    rows, cm = parse()
    cust = agg_customers(rows)
    total = sum(x["overdue"] for x in rows)
    total_prog = sum(x["progress"] for x in rows)
    total_net = total - total_prog
    top5 = sum(x["overdue"] for x in cust[:5])
    top10 = sum(x["overdue"] for x in cust[:10])
    aging = defaultdict(float)
    for x in rows: aging[aging_bucket(x["days"])] += x["overdue"]
    sp = defaultdict(lambda: [0.0, 0])
    for x in rows:
        sp[x["sales"]][0] += x["overdue"]; sp[x["sales"]][1] += 1
    unique_groups = [x for x in cust if x["group_count"] == 1]

    wb = openpyxl.Workbook(); wb.remove(wb.active)

    # 00 结论
    ws = wb.create_sheet("00_结论与口径")
    widths(ws, [4, 24, 106])
    style_title(ws, "2026年10月逾期应收（科研）｜重点清单", 3,
                "截止：2026-09-30｜金额单位：元｜来源：2026年10月逾期应收回款计划-总表（回款进度） -科研.xlsx")
    r = 4
    ws.cell(r, 2, "一、先看结论").font = Font(name="微软雅黑", bold=True, size=11, color=NAVY)
    r += 1
    conclusions = [
        ("总量", f"逾期 {total:,.2f} 元，共 {len(rows)} 笔、{len(cust)} 个往来单位；10月已回款 {total_prog:,.2f} 元（{total_prog/total*100:.1f}%），净未回款 {total_net:,.2f} 元。"),
        ("集中度", f"前5个单位 {top5:,.2f} 元，占 {top5/total*100:.1f}%；前10个单位 {top10:,.2f} 元，占 {top10/total*100:.1f}%。重点只需盯5—10个单位。"),
        ("账龄", f"180天以上 {aging['181-365天']+aging['365天以上']:,.2f} 元，占 {((aging['181-365天']+aging['365天以上'])/total*100):.1f}%；其中365天以上 {aging['365天以上']:,.2f} 元，占 {aging['365天以上']/total*100:.1f}%。"),
        ("销售集中", "吴云 225,011.48 元（88.4%，84笔）；韩远怀 29,661.70 元（11.6%，38笔）。重点在浙江区域。"),
        ("课题组口径", "附件只有“往来单位”，没有“课题组/联系人/合同对应课题组”字段。浙江大学对应196个课题组、浙江中医药55个、浙江农林73个、温州医科55个、华南农业53个——同一单位多组，无法把每笔逾期精确拆到PI组。本表按往来单位提炼；仅对唯一映射的7个单位标注课题组名，其余标“多课题组（N个）”，不硬拆。"),
        ("动作优先级", "A类5个单位（金额≥1.5万且180天以上占比≥60%）：浙江大学、浙江中医药大学、温州大学、浙江农林大学、温州医科大学；合计198,157.68元，占77.8%。先按合同号回CRM商机/联系人拆到课题组，再谈催收。"),
    ]
    for k, v in conclusions:
        cell(ws, r, 2, k, bold=True, fill=PALE)
        cell(ws, r, 3, v)
        r += 1

    r += 1
    ws.cell(r, 2, "二、风险分层").font = Font(name="微软雅黑", bold=True, size=11, color=NAVY)
    r += 1
    header(ws, r, ["", "层级", "标准与金额"])
    r += 1
    for label, cond in [
        ("A类", "金额≥1.5万 且 180天以上占比≥60%"),
        ("B类", "金额<1.5万 但最长逾期>180天"),
        ("C类", "最长逾期≤180天（新逾期/短期）"),
    ]:
        xs = [x for x in cust if x["risk"].startswith(label[0])]
        amt = sum(x["overdue"] for x in xs)
        cell(ws, r, 2, label, bold=True, fill=ORANGE if label == "A类" else (YELLOW if label == "B类" else GREY))
        cell(ws, r, 3, f"{cond}；{len(xs)}个单位，{amt:,.2f}元，占{amt/total*100:.1f}%")
        r += 1

    r += 1
    ws.cell(r, 2, "三、口径说明").font = Font(name="微软雅黑", bold=True, size=11, color=NAVY)
    r += 1
    notes = [
        ("逾期判据", "「附表_逾期&未逾期（汇总）」中逾期天数可转数字且逾期金额>0；未逾期行不计。"),
        ("金额", "逾期金额=J列；10月已回款=M列；净未回款=J-M。金额为元，未做含税/非含税调整。"),
        ("账龄", "按逾期天数分档：0-30、31-60、61-90、91-180、181-365、365+；与源表账龄列可能有1天边界差，以逾期天数为准。"),
        ("课题组映射", "用数据/ls_groups.json 的 groups[].customers 精确匹配往来单位；一个单位对应多个课题组时不拆分金额。"),
        ("未映射", "华中农业大学、珠海市聚鑫隆科技有限公司在 ls_groups.json 无匹配；不强行归类。"),
        ("用途", "内部催收优先级清单；不对外发送，不改变ERP/财务账。"),
    ]
    for k, v in notes:
        cell(ws, r, 2, k, bold=True, fill=GREY)
        cell(ws, r, 3, v)
        r += 1

    # 01 重点清单
    ws = wb.create_sheet("01_重点清单")
    widths(ws, [4, 26, 12, 12, 7, 9, 5, 22])
    style_title(ws, "重点逾期科研客户 TOP15（按逾期金额）", 8,
                "净未回款=逾期-10月已回款；课题组列只写唯一映射，多课题组不硬拆；销售员见03明细")
    header(ws, 3, ["排名", "往来单位", "逾期金额", "净未回款", "最长逾期天数", ">180天占比", "风险", "课题组映射"])
    r = 4
    for i, x in enumerate(cust[:15], 1):
        fill = ORANGE if x["risk"].startswith("A") else (YELLOW if x["risk"].startswith("B") else None)
        cell(ws, r, 1, i, align=CENTER)
        cell(ws, r, 2, x["customer"], bold=x["overdue"] >= 15000)
        cell(ws, r, 3, x["overdue"], NUM, RIGHT, bold=x["overdue"] >= 15000, fill=fill)
        cell(ws, r, 4, x["net"], NUM, RIGHT, bold=x["net"] >= 15000)
        cell(ws, r, 5, x["max_days"], NUM, CENTER, color=RED if x["max_days"] > 365 else "000000")
        cell(ws, r, 6, x["over180_pct"], PCT, CENTER, color=RED if x["over180_pct"] >= 0.6 else "000000")
        cell(ws, r, 7, x["risk"].split("：")[0], align=CENTER, fill=fill)
        cell(ws, r, 8, x["group_note"], fill=GREY if "多课题组" in x["group_note"] else None)
        r += 1
    # total
    cell(ws, r, 2, "TOP15合计", bold=True, fill=LIGHT)
    cell(ws, r, 3, sum(x["overdue"] for x in cust[:15]), NUM, RIGHT, bold=True, fill=LIGHT)
    cell(ws, r, 4, sum(x["net"] for x in cust[:15]), NUM, RIGHT, bold=True, fill=LIGHT)
    for c in [1, 5, 6, 7, 8]:
        cell(ws, r, c, "", fill=LIGHT)

    # 02 账龄与集中度
    ws = wb.create_sheet("02_账龄与集中度")
    widths(ws, [24, 18, 14, 40])
    style_title(ws, "逾期账龄与集中度", 4, "金额单位：元")
    header(ws, 3, ["账龄档", "逾期金额", "占总额", "说明"])
    r = 4
    for k in ["0-30天", "31-60天", "61-90天", "91-180天", "181-365天", "365天以上"]:
        v = aging[k]
        cell(ws, r, 1, k, bold=k in ("181-365天", "365天以上"))
        cell(ws, r, 2, v, NUM, RIGHT)
        cell(ws, r, 3, v / total if total else 0, PCT, CENTER)
        cell(ws, r, 4, "重点风险" if k in ("181-365天", "365天以上") else "")
        r += 1
    cell(ws, r, 1, "合计", bold=True, fill=LIGHT)
    cell(ws, r, 2, total, NUM, RIGHT, bold=True, fill=LIGHT)
    cell(ws, r, 3, 1, PCT, CENTER, bold=True, fill=LIGHT)
    r += 2
    ws.cell(r, 1, "集中度").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header(ws, r, ["口径", "金额", "占总额", "备注"])
    r += 1
    for label, amt, note in [("前5个单位", top5, "重点中的重点"), ("前10个单位", top10, "覆盖近九成"), ("其余22个单位", total - top10, "长尾，单笔小额")]:
        cell(ws, r, 1, label)
        cell(ws, r, 2, amt, NUM, RIGHT)
        cell(ws, r, 3, amt / total if total else 0, PCT, CENTER)
        cell(ws, r, 4, note)
        r += 1
    r += 2
    ws.cell(r, 1, "按销售员").font = Font(name="微软雅黑", bold=True, size=10, color=NAVY)
    r += 1
    header(ws, r, ["销售员", "逾期金额", "占总额", "笔数"])
    r += 1
    for k, (v, n) in sorted(sp.items(), key=lambda kv: -kv[1][0]):
        cell(ws, r, 1, k)
        cell(ws, r, 2, v, NUM, RIGHT)
        cell(ws, r, 3, v / total if total else 0, PCT, CENTER)
        cell(ws, r, 4, n, NUM, CENTER)
        r += 1

    # 03 全部明细
    ws = wb.create_sheet("03_全部明细")
    widths(ws, [5, 21, 10, 28, 20, 20, 21, 12, 10, 12, 12, 12, 26])
    style_title(ws, "全部逾期明细（122笔）", 13,
                "按往来单位+逾期金额排序；课题组列只写唯一映射；多课题组不硬拆")
    header(ws, 3, ["序号", "往来单位", "销售员", "单据编号", "合同编号", "到期日", "课题组映射", "逾期天数", "账龄", "逾期金额", "10月已回款", "净未回款", "备注"])
    r = 4
    idx = 0
    for x in sorted(rows, key=lambda z: (-z["overdue"], z["days"])):
        idx += 1
        cell(ws, r, 1, idx, align=CENTER)
        cell(ws, r, 2, x["customer"])
        cell(ws, r, 3, x["sales"], align=CENTER)
        cell(ws, r, 4, x["doc"])
        cell(ws, r, 5, x["contract"])
        cell(ws, r, 6, x["due_date"].strftime("%Y-%m-%d") if isinstance(x["due_date"], datetime) else str(x["due_date"] or ""), align=CENTER)
        cell(ws, r, 7, x["group_note"], fill=GREY if "多课题组" in x["group_note"] else None)
        cell(ws, r, 8, x["days"], NUM, CENTER, color=RED if x["days"] > 365 else "000000")
        cell(ws, r, 9, aging_bucket(x["days"]), align=CENTER)
        cell(ws, r, 10, x["overdue"], NUM, RIGHT)
        cell(ws, r, 11, x["progress"], NUM, RIGHT)
        cell(ws, r, 12, x["net"], NUM, RIGHT)
        cell(ws, r, 13, "已回款" if x["progress"] >= x["overdue"] - 0.01 else "")
        r += 1

    # 04 唯一映射
    ws = wb.create_sheet("04_可唯一映射课题组")
    widths(ws, [24, 24, 16, 14, 12, 12, 40])
    style_title(ws, "可唯一映射到课题组的单位", 7,
                "仅这些单位在 ls_groups.json 中只对应1个课题组；金额小，但可精确到PI组")
    header(ws, 3, ["往来单位", "课题组", "销售员", "逾期金额", "笔数", "最长天数", "说明"])
    r = 4
    for x in sorted(unique_groups, key=lambda z: -z["overdue"]):
        cell(ws, r, 1, x["customer"])
        cell(ws, r, 2, x["group"], bold=True)
        cell(ws, r, 3, x["sales"], align=CENTER)
        cell(ws, r, 4, x["overdue"], NUM, RIGHT)
        cell(ws, r, 5, x["n"], NUM, CENTER)
        cell(ws, r, 6, x["max_days"], NUM, CENTER)
        cell(ws, r, 7, "可精确到课题组" if x["max_days"] <= 180 else "可精确到课题组，但账龄已>180天")
        r += 1

    # auto height / print
    for ws in wb.worksheets:
        autoheight(ws)
        ws.sheet_view.zoomScale = 85
        ws.page_setup.orientation = "landscape"
        ws.page_setup.fitToWidth = 1
        ws.page_setup.fitToHeight = 0
        ws.sheet_properties.pageSetUpPr = PageSetupProperties(fitToPage=True)

    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    wb.save(OUT)
    print("SAVED", OUT)
    print("total", total, "progress", total_prog, "net", total_net, "rows", len(rows), "customers", len(cust))
    print("top5", top5, top5/total, "top10", top10, top10/total)
    print("aging", dict(aging))
    print("unique groups", [(x["customer"], x["group"], x["overdue"]) for x in unique_groups])


if __name__ == "__main__":
    build()