# -*- coding: utf-8 -*-
"""解析三部门月度关键任务协议书 -> 结构化 JSON（规则源）"""
import json, re, os
import pandas as pd

DOCS = "/Users/liuxinyuan/.hermes/cache/documents"
FILES = {
    "科研_销售工程师": os.path.join(DOCS, "doc_9d241d80c089_🌟生命科学销售部-销售工程师月度关键任务协议书-2026年8月.xlsx"),
    "大客户部":        os.path.join(DOCS, "doc_7be3f850b9a1_⭐️大客户-月度关键任务协议书-2026-9月.xlsx"),
    "拓展部":          os.path.join(DOCS, "doc_6d59243c11b9_⭐️拓展部-月度关键任务责任书-2026-9月.xlsx"),
    "科研_区域经理":    os.path.join(DOCS, "doc_08b870b1ffe3_⭐️生命科学销售部-区域销售经理月度关键任务协议书-2026-8月.xlsx"),
}

def cell(v):
    if v is None: return ""
    s = str(v)
    if s.lower() == "nan": return ""
    return re.sub(r"\s+", " ", s).strip()

def parse_kpi_sheet(path, sheet):
    df = pd.read_excel(path, sheet_name=sheet, header=None).fillna("")
    hdr = None
    for i in range(min(12, len(df))):
        row = [cell(x) for x in df.iloc[i]]
        if any("关键任务项" in x for x in row):
            hdr = i; break
    if hdr is None: return None
    # 列定位
    hr = [cell(x) for x in df.iloc[hdr]]
    def find(*keys):
        for j, v in enumerate(hr):
            if any(k in v for k in keys): return j
        return None
    c_no   = find("序号") or 0
    c_item = find("关键任务项")
    c_desc = find("任务项描述")
    c_w    = find("权重")
    c_act  = find("实际达成")
    c_src  = find("数据来源")
    c_own  = find("数据统计责任人")
    # T1/T2/T3 在下一行
    sub = [cell(x) for x in df.iloc[hdr + 1]] if hdr + 1 < len(df) else []
    c_t1 = c_t2 = c_t3 = None
    for j, v in enumerate(sub):
        if "T1" in v: c_t1 = j
        if "T2" in v: c_t2 = j
        if "T3" in v: c_t3 = j
    if c_t1 is None:  # 有些版本同行
        for j, v in enumerate(hr):
            if "T1" in v: c_t1 = j
            if "T2" in v: c_t2 = j
            if "T3" in v: c_t3 = j
    items = []
    for i in range(hdr + 2, len(df)):
        r = [cell(x) for x in df.iloc[i]]
        if not r or len(r) <= c_item: break
        no, item = r[c_no], r[c_item]
        if "关键任务权重合计" in r[c_no] or "关键任务权重合计" in (item or ""): break
        if "上级评分" in "".join(r[:3]): break
        if not item: continue
        def g(c):
            return r[c] if (c is not None and c < len(r)) else ""
        w = g(c_w)
        try: w = float(w)
        except Exception: w = None
        items.append({
            "no": no, "item": item, "desc": g(c_desc), "weight": w,
            "T1": g(c_t1), "T2": g(c_t2), "T3": g(c_t3),
            "actual": g(c_act), "source": g(c_src), "owner": g(c_own),
        })
    # 上级评分区
    superiors = []
    for i in range(hdr, len(df)):
        r = [cell(x) for x in df.iloc[i]]
        joined = " | ".join(x for x in r if x)
        if "满分" in joined and ("分" in joined):
            label = r[0] if r and r[0] else (r[1] if len(r) > 1 else "")
            m = re.search(r"满分(\d+)分", joined)
            if m and "合计" not in label:
                owners = [x for x in r if x in ("直接上级", "部门负责人", "区域负责人", "总经理", "销售总监", "区域销售经理")]
                superiors.append({"role": label, "full": int(m.group(1)),
                                  "grader": owners[0] if owners else "",
                                  "rule": joined[:400]})
    # 权重合计
    wsum = None
    for i in range(hdr, len(df)):
        r = [cell(x) for x in df.iloc[i]]
        if any("关键任务权重合计" in x for x in r):
            for x in r:
                try:
                    wsum = float(x); break
                except Exception: pass
            break
    return {"sheet": sheet, "items": items, "superiors": superiors, "weight_sum": wsum}

def parse_assign_sheet(path, sheet):
    df = pd.read_excel(path, sheet_name=sheet, header=None).fillna("")
    rows = []
    for i in range(len(df)):
        r = [cell(x) for x in df.iloc[i]]
        if not any(r): continue
        rows.append(r[:9])
    return {"sheet": sheet, "rows": rows}

out = {}
for tag, path in FILES.items():
    xl = pd.ExcelFile(path)
    out[tag] = {"file": os.path.basename(path), "sheets": {}}
    for s in xl.sheet_names:
        is_assign = "指派任务" in s
        try:
            if is_assign:
                out[tag]["sheets"][s] = parse_assign_sheet(path, s)
            else:
                p = parse_kpi_sheet(path, s)
                if p: out[tag]["sheets"][s] = p
        except Exception as e:
            out[tag]["sheets"][s] = {"error": str(e)}

# 客户分配
try:
    dk = pd.read_excel(FILES["大客户部"], sheet_name="2人客户数量", header=None).fillna("")
    out["_客户分配_2人客户数量"] = [[cell(x) for x in dk.iloc[i]] for i in range(len(dk))]
except Exception as e:
    out["_客户分配_2人客户数量"] = str(e)

os.makedirs("/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据", exist_ok=True)
dst = "/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据/kpi_scheme_raw.json"
json.dump(out, open(dst, "w"), ensure_ascii=False, indent=1)
print("WROTE", dst, os.path.getsize(dst))

# 摘要打印
for tag in FILES:
    print("\n" + "=" * 90)
    print("###", tag, "|", out[tag]["file"])
    for s, v in out[tag]["sheets"].items():
        if "error" in v: print("  !!", s, v["error"]); continue
        if "rows" in v:
            print(f"  [{s}] 指派任务 {len(v['rows'])}行")
            n = 0
            for r in v["rows"]:
                if r and r[0] in ("1", "2", "3", "4", "5"):
                    print("      ", " || ".join(x[:40] for x in r if x)[:220]); n += 1
                    if n >= 6: break
        else:
            print(f"  [{s}] 权重合计={v['weight_sum']} 项数={len(v['items'])} 上级={[(x['role'],x['full'],x['grader']) for x in v['superiors']]}")
            for it in v["items"]:
                print(f"      {it['no']}. {it['item'][:20]:22s} w={it['weight']} T1={it['T1'][:18]} T2={it['T2'][:18]} T3={it['T3'][:18]} <- {it['source']}")