#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
绩效邮件生成器 —— 第 1 步：解析考核分数通报表 → 结构化 JSON

输入：4 个部门的《…考核分数通报（YYYY年M月）.xlsx》（一个文件 12 个月 sheet，每月一页）
输出：数据/绩效_<YYYY-MM>.json
      每人：{姓名, 部门, 总分, 系数, 排名, 分项:[{项目, 档位, 实际得分, 折算得分, 权重}], 待改善[], 表扬[]}

判档规则（用户 2026-10-10 拍板）：分项档位「低于T1」→ 待改善；「T3」→ 表扬。
总分取底部「得分」行的**缓存值**（他发的文件 Excel 已算好；输入库副本无缓存值，不可用）。
用法：python3 绩效邮件_parse.py 2026 9
"""
import sys, os, json, glob, re
import openpyxl

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
PERF_DIR = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/销售绩效')
CACHE_DIR = os.path.expanduser('~/.hermes/profiles/zone-b/cache/documents')
SKIP_NAMES = ('平均', '合计', '小计', '人均', '部门平均', '总计')
SUMMARY_LABELS = ('月度关键任务得分', '月度关键任务得分折算', '关键任务得分（100分）',
                  '关键任务得分（折算70%）', '总得分', '得分', '月度绩效考核系数',
                  '部门经理评分（满分30分）', '部门经理评分（满分15分）',
                  '大区经理评分（满分15分）', '大区经理评分（满分30分）')

def resolve_file(path):
    """优先用用户飞书发来的副本（有 Excel 算好的缓存值）；输入库副本常无缓存值。"""
    base = os.path.basename(path)
    cand = os.path.join(CACHE_DIR, 'doc_*' + base)
    hit = sorted(glob.glob(cand))
    if hit:
        try:
            wbv = openpyxl.load_workbook(hit[0], data_only=True)
            for sn in wbv.sheetnames:
                if '趋势' not in sn:
                    ws = wbv[sn]
                    for r in range(1, ws.max_row + 1):
                        if str(ws.cell(r, 1).value or '').strip() in ('得分', '总得分'):
                            for c in range(4, ws.max_column + 1):
                                if ws.cell(r, c).value not in (None, ''):
                                    return hit[0]
        except Exception:
            pass
    return path


def find_month_sheet(wb, month):
    for n in wb.sheetnames:
        if re.match(r'^\s*%d月' % month, n) and '趋势' not in n:
            return n
    return None

def parse_sheet(ws, wsv, dept):
    """返回 {姓名: {...}}"""
    # 1) 姓名行 = 出现「考核项目」的那一行
    hdr_row = None
    for r in range(1, min(8, ws.max_row + 1)):
        if str(ws.cell(r, 1).value or '').strip() == '考核项目':
            hdr_row = r
            break
    if hdr_row is None:
        return {}
    persons = {}
    for c in range(4, ws.max_column + 1):
        nm = ws.cell(hdr_row, c).value
        nm = str(nm).strip() if nm else ''
        if nm and nm not in SKIP_NAMES and nm not in ('无', 'nan'):
            persons[c] = nm

    # 2) 划块：col A 非空且不是汇总行 → 新考核项目
    blocks, cur = [], None
    for r in range(hdr_row + 1, ws.max_row + 1):
        a = str(ws.cell(r, 1).value or '').strip()
        c3 = str(ws.cell(r, 3).value or '').strip()
        if a and a not in SUMMARY_LABELS and not a.startswith('月度关键任务得分折算') \
                and not re.match(r'^[（(]', a) and len(a) > 4:
            cur = {'name': a, 'rows': []}
            blocks.append(cur)
        if cur is not None:
            cur['rows'].append(r)
    # 汇总行归到最后一个块会污染 → 从块里剔除汇总行区间
    sum_start = None
    for blk in blocks:
        for r in blk['rows']:
            a = str(ws.cell(r, 1).value or '').strip()
            if a in SUMMARY_LABELS or a.startswith('月度关键任务得分折算'):
                sum_start = r if sum_start is None else min(sum_start, r)
    if sum_start:
        for blk in blocks:
            blk['rows'] = [r for r in blk['rows'] if r < sum_start]

    out = {}
    for c, nm in persons.items():
        items = []
        for blk in blocks:
            level, weight, actual, weighted = None, None, None, None
            for r in blk['rows']:
                b = str(ws.cell(r, 2).value or '').strip()
                val = wsv.cell(r, c).value
                if (b.startswith('低于T1') or b.startswith('＜') or b.startswith('<')) and val not in (None, ''):
                    level = '低于T1'
                elif b.startswith('T1') and val not in (None, ''):
                    level = 'T1'
                elif b.startswith('T2') and val not in (None, ''):
                    level = 'T2'
                elif b.startswith('T3') and val not in (None, ''):
                    level = 'T3'
                if b.startswith('权重'):
                    weight = b
                    if val not in (None, ''):
                        weighted = val
                elif str(ws.cell(r, 3).value or '').strip() == '实际得分':
                    if val not in (None, ''):
                        actual = val
            if level or actual is not None or weighted is not None:
                items.append({'项目': blk['name'], '档位': level, '实际得分': actual,
                              '折算得分': weighted, '权重': weight})
        # 汇总行
        total = coef = None
        for r in range(hdr_row, ws.max_row + 1):
            a = str(ws.cell(r, 1).value or '').strip()
            if a in ('得分', '总得分'):
                total = wsv.cell(r, c).value
            elif a == '月度绩效考核系数':
                coef = wsv.cell(r, c).value
        bad = [i['项目'] for i in items if i['档位'] == '低于T1']
        good = [i['项目'] for i in items if i['档位'] == 'T3']
        out[nm] = {'姓名': nm, '部门': dept, '总分': total, '系数': coef,
                   '分项': items, '待改善': bad, '表扬': good}
    return out

def main():
    year, month = int(sys.argv[1]), int(sys.argv[2])
    files = sorted(glob.glob(os.path.join(PERF_DIR, '*%d年%d月）.xlsx' % (year, month))))
    if not files:
        print('未找到 %d年%d月 的通报文件' % (year, month)); return
    people = {}
    for f in files:
        base = os.path.basename(f)
        dept = base.split('-')[0] if '-' in base else base.split('销售工程师')[0]
        dept = re.sub(r'（.*$', '', dept).strip()
        f = resolve_file(f)
        wb = openpyxl.load_workbook(f, data_only=False)
        wbv = openpyxl.load_workbook(f, data_only=True)
        sn = find_month_sheet(wb, month)
        if not sn:
            print('  ⚠️ %s 无 %d月 sheet' % (os.path.basename(f)[:20], month)); continue
        got = parse_sheet(wb[sn], wbv[sn], dept)
        miss = [n for n, v in got.items() if v['总分'] is None]
        print('  %-14s sheet[%s] → %d 人%s' % (dept, sn.strip()[:22], len(got),
              ('  ⚠️ 无总分: %s' % ','.join(miss)) if miss else ''))
        people.update(got)
    # 排名（用户 2026-10-10 拍板：①分母不含两位区域经理 ②**分部门排**）
    RANK_EXCLUDE = ('韩远怀', '吴云')
    pools = {}
    for p in people.values():
        if isinstance(p['总分'], (int, float)) and p['姓名'] not in RANK_EXCLUDE:
            pools.setdefault(p['部门'], []).append(p)
    for dept, grp in pools.items():
        grp.sort(key=lambda x: -x['总分'])
        for i, p in enumerate(grp, 1):
            p['排名'] = i
            p['团队人数'] = len(grp)
            p['排名范围'] = dept
    n = max([len(g) for g in pools.values()] or [0])
    for p in people.values():
        p.setdefault('排名', None)
        p.setdefault('团队人数', None)
    out = os.path.join(ROOT, '数据', '绩效_%d-%02d.json' % (year, month))
    os.makedirs(os.path.dirname(out), exist_ok=True)
    with open(out, 'w', encoding='utf-8') as fh:
        json.dump({'期间': '%d年%d月' % (year, month), '团队人数': n,
                   '人员': list(people.values())}, fh, ensure_ascii=False, indent=1)
    print('\n  分部门排名：')
    _all = sorted([p for p in people.values() if isinstance(p.get('排名'), int)],
                  key=lambda x: (x.get('排名范围') or '', x['排名']))
    for p in _all:
        print('   %-8s %2d/%-3d %-6s %6.1f ｜待改善 %-2d ｜T3 %-2d' %
              (p.get('排名范围', ''), p['排名'], p['团队人数'], p['姓名'], p['总分'],
               len(p['待改善']), len(p['表扬'])))
    print('\n  → %s' % out)

if __name__ == '__main__':
    main()