#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
解析「考核分数通报」xlsx（官方已算分）-> 数据/kpi_actual.json
用途：把官方真实达成值/实际得分/折算得分/上级评分/总分回填到 KPI 积分卡看板（真实数据模式）。
口径：低于T1=0分、达T1=60分、达T2=100分、达T3=120分（离散，不插值）；折算得分=实际得分×权重。
"""
import json, os, re
import pandas as pd

DOCS = "/Users/liuxinyuan/.hermes/cache/documents"
OUT = "/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据/kpi_actual.json"

FILES = {
    "dk":     "doc_060b3420fd8e_大客户销售部-销售工程师考核分数通报（2026年8月）.xlsx",
    "tz":     "doc_da923c61a28b_销售拓展部销售工程师考核分数通报（2026年8月）.xlsx",
    "ls_eng": "doc_a720352df2ad_生命科学销售部-各区域销售工程师考核分数通报（2026年8月）.xlsx",
    "ls_mgr": "doc_f6e8e910ff5f_生命科学销售部-区域销售经理考核分数通报（2026年8月）.xlsx",
}
TIER_ROWS = {"低于T1": "below", "T1（门槛目标）": "T1", "T2（达标目标）": "T2", "T3（挑战目标）": "T3"}
SUP_PAT = re.compile(r"(大区经理|部门经理|区域经理|销售总监|总经理)评分")
TOTAL_KEYS = ["总得分", "得分", "月度绩效考核系数", "关键任务得分", "月度关键任务得分"]


def s(v):
    if v is None:
        return ""
    try:
        if pd.isna(v):
            return ""
    except Exception:
        pass
    return str(v).strip()


def num(v):
    t = s(v)
    if not t:
        return None
    try:
        return float(t)
    except Exception:
        return None


def parse_sheet(df):
    """返回 {items:[...], superiors:[...], totals:{...}, agg:{...}}"""
    # 表头行：找 col2 为 '分值'/'数值' 的行
    hdr = None
    for i in range(min(8, len(df))):
        if s(df.iat[i, 2]) in ("分值", "数值"):
            hdr = i
            break
    if hdr is None:
        return None
    people = {}
    for c in range(3, df.shape[1]):
        nm = s(df.iat[hdr, c])
        if nm and nm not in ("平均", "备注") and not nm.startswith("Unnamed"):
            people[c] = nm
    if not people:
        return None

    title = s(df.iat[0, 0])
    note = s(df.iat[1, 0]) if df.shape[0] > 1 else ""
    rule_text = ""
    for i in range(0, min(6, len(df))):
        for c in range(0, min(4, df.shape[1])):
            t = s(df.iat[i, c])
            if "得120分" in t or "得100分" in t:
                rule_text = t
                break
        if rule_text:
            break

    items, superiors, totals = [], [], {}
    cur = None
    for r in range(hdr + 1, len(df)):
        c0, c1, c2 = s(df.iat[r, 0]), s(df.iat[r, 1]), s(df.iat[r, 2])
        vals = {c: df.iat[r, c] for c in people}

        if c0.startswith("大区经理评分") or c0.startswith("部门经理评分") or \
           c0.startswith("区域经理评分") or c0.startswith("销售总监评分"):
            sup = {"name": c0.replace("\\n", " ").strip(), "full": num(c2), "scores": {}}
            for c in people:
                v = num(vals[c])
                if v is not None:
                    sup["scores"][people[c]] = v
            superiors.append(sup)
            continue
        if c0.startswith("关键任务得分") or c0.startswith("月度关键任务得分") or \
           c0.startswith("总得分") or c0.startswith("得分") or c0.startswith("月度绩效考核系数"):
            k = c0.replace("\\n", " ").strip()
            d = totals.setdefault(k, {})
            for c in people:
                v = num(vals[c])
                if v is not None:
                    d[people[c]] = v
            continue

        # 其余：c0 非空 => 新考核项（c2 可能是「低于T1」门槛）
        if c0:
            cur = {"name": re.sub(r"\s+", " ", c0.replace("\\n", " ")).strip(),
                   "tiers": {}, "score": {}, "disc": {}, "rawvals": {}, "weight": None}
            items.append(cur)
            if c2 and c2 not in ("分值", "数值"):
                cur["tiers"]["below"] = c2
            for c in people:
                v = num(vals[c])
                if v is not None:
                    cur["rawvals"][people[c]] = v
            continue

        if cur is None:
            continue

        if c1 in TIER_ROWS:
            cur["tiers"][TIER_ROWS[c1]] = c2
            for c in people:
                v = num(vals[c])
                if v is not None:
                    cur["rawvals"][people[c]] = v
            continue

        if c2 == "实际得分":
            for c in people:
                v = num(vals[c])
                if v is not None:
                    cur["score"][people[c]] = v
            continue

        if c2.startswith("权重") or c1.startswith("权重"):
            src = c2 if c2.startswith("权重") else c1
            cur["weight"] = num(src.replace("权重", "").replace("%", ""))
            if cur["weight"] is not None:
                cur["weight"] = cur["weight"] / 100.0
            if c1 == "折算得分" or c2 == "折算得分":
                for c in people:
                    v = num(vals[c])
                    if v is not None:
                        cur["disc"][people[c]] = v
            continue

        # 汇总行
        for key in ("关键任务得分（100分）", "关键任务得分（折算70%）", "月度关键任务得分",
                    "关键任务得分", "总得分", "得分", "月度绩效考核系数", "大区经理评分", "部门经理评分",
                    "区域经理评分", "销售总监评分"):
            if c0.startswith(key):
                k = key
                if k in ("关键任务得分", "得分"):
                    k = "总得分" if "得分（100" not in c0 else k
                if "关键任务得分" in c0:
                    k = c0.replace("\\n", " ").strip()
                if k.startswith("大区经理评分") or k.startswith("部门经理评分") or \
                   k.startswith("区域经理评分") or k.startswith("销售总监评分"):
                    sup = {"name": k, "full": num(c2), "scores": {}}
                    for c in people:
                        v = num(vals[c])
                        if v is not None:
                            sup["scores"][people[c]] = v
                    superiors.append(sup)
                elif TOTAL_KEYS:
                    d = totals.setdefault(k, {})
                    for c in people:
                        v = num(vals[c])
                        if v is not None:
                            d[people[c]] = v
                break

    return {"title": title, "note": note, "rule_text": rule_text, "people": list(people.values()),
            "items": items, "superiors": superiors, "totals": totals}


def main():
    out = {}
    for key, fn in FILES.items():
        p = os.path.join(DOCS, fn)
        xl = pd.ExcelFile(p)
        months = {}
        for sh in xl.sheet_names:
            m = re.match(r"\s*(\d{1,2})月", sh)
            if not m:
                continue
            mo = int(m.group(1))
            if mo < 1 or mo > 12:
                continue
            if "趋势" in sh or "Sheet" in sh:
                continue
            df = xl.parse(sh, header=None)
            r = parse_sheet(df)
            if r and r["items"]:
                ym = f"2025-{mo:02d}" if (key == "ls_eng" and mo == 12) else f"2026-{mo:02d}"
                months[ym] = r
        out[key] = {"file": fn, "months": months}
        print(f"{key:7s} {len(months)} 个月: {sorted(months)}")

    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    with open(OUT, "w", encoding="utf-8") as f:
        json.dump(out, f, ensure_ascii=False, indent=1)
    print("WROTE", OUT, os.path.getsize(OUT))


if __name__ == "__main__":
    main()