#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
生成 三部门逐月_2026.json —— 三部门月×部门实际/目标/2025同期 + 客户 + 销售员聚合。
数据全部从 ERP 真实订单现算，单位统一为万元（保留2位小数）。
运行：env -u PYTHONPATH -u VIRTUAL_ENV /usr/bin/python3 gen_dept_monthly_json.py
"""
import os, sys, json, shutil, datetime
import pandas as pd

sys.path.insert(0, '/Users/liuxinyuan/Desktop/Hermes输出-工作类/脚本')
import erp_sales_core as core

BASE = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
DATA = os.path.join(BASE, '数据')
OUT = os.path.join(DATA, '三部门逐月_2026.json')
SRC_2025 = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2025全年订单.xlsx'
TARGET_PREF = os.path.join(DATA, '月度目标_2026统一.json')
TARGET_FALLBACK = os.path.join(DATA, 'year_targets_2026.json')

DEPTS = ['大客户部', '销售拓展部', '生命科学部']
MONTHS_ALL = [f'2026-{m:02d}' for m in range(1, 13)]   # 全年 12 个月：目标口径（权威源含 10-12 月）
MONTHS_ACT = [f'2026-{m:02d}' for m in range(1, 10)]   # 实际/2025 同期：仅 1-9 月（10-12 月无 ERP 数据，保持空白，绝不写 0）
MONTHS = MONTHS_ALL  # 兼容旧引用
W = 1e4  # 元 -> 万元


def norm_dept(d):
    """部门归一：判定顺序 大客户 → 生命科学 → 销售拓展"""
    if not d:
        return None
    d = str(d)
    if '大客户' in d:
        return '大客户部'
    if '生命科学' in d or '科研' in d:
        return '生命科学部'
    if '销售拓展' in d or '拓展' in d:
        return '销售拓展部'
    return None


# ---------------------------------------------------------------- 2026 现算
def extract_2026():
    """按 aggregate 同口径提取 2026 1-9 的 月×部门、月×部门×客户、月×部门×销售员。"""
    fp = core.find_latest_erp()
    df = pd.read_excel(fp, dtype=str)
    # 金额口径 = 价税合计（含税）
    df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0)
    # 销售员
    if '销售员' in df.columns:
        df['_sp'] = df['销售员'].fillna('').astype(str)
    else:
        om = core.load_order_sp_map(); sm = core.load_sp_map()
        df['_oid'] = df['单据编号'].astype(str).str.strip()
        df['_sp'] = df['_oid'].map(om).fillna('')
        miss = df['_sp'] == ''
        df.loc[miss, '_sp'] = df.loc[miss, '客户'].astype(str).str.strip().map(sm).fillna('')
    pt_col = '客户采购类型#' if '客户采购类型#' in df.columns else ('产品类型#' if '产品类型#' in df.columns else None)
    df['_date'] = df['合同日期'].apply(core.ser2date)
    df['_month'] = df['_date'].str[:7]

    std = df[(df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核') & (df['是否赠品'] != '是')]
    std = std[~std['客户'].astype(str).str.contains(core.BAORUI_PATTERN, na=False)].copy()
    std['_amt'] = std['_amt'].astype(float)

    person_dept, lzy = core.load_mappings()
    md, mdc, mds = {}, {}, {}          # dept / dept-cust / dept-sp
    sp_custs = {}                      # (m,dept,sp) -> set(cust)
    for _, r in std.iterrows():
        m = str(r['_month'])
        if m not in MONTHS_ACT:      # 只看 1-9 月（实际口径）
            continue
        sp = str(r['_sp']).strip()
        cust = str(r['客户']).strip()
        amt = float(r['_amt'])
        pt = str(r[pt_col]) if pt_col else ''
        kpi_person, dept = core.map_person(sp, cust, person_dept, lzy, pt)
        if dept is None:
            continue
        eff_sp = kpi_person or sp
        md.setdefault(m, {}); md[m][dept] = md[m].get(dept, 0) + amt
        mdc.setdefault(m, {}).setdefault(dept, {})
        mdc[m][dept][cust] = mdc[m][dept].get(cust, 0) + amt
        mds.setdefault(m, {}).setdefault(dept, {})
        mds[m][dept][eff_sp] = mds[m][dept].get(eff_sp, 0) + amt
        sp_custs.setdefault((m, dept, eff_sp), set()).add(cust)
    return fp, md, mdc, mds, sp_custs


# ---------------------------------------------------------------- 2025 同期
def extract_2025():
    """2025 1-9 同口径。该文件无「单据状态」列，采用 标准销售订单 + 非赠品 + 价税合计，
    部门经「销售部门区域对应」表归一（科研-* → 生命科学部）。锚点 2749.27 万可复现。"""
    if not os.path.exists(SRC_2025):
        return None
    df = pd.read_excel(SRC_2025, sheet_name='Sheet1', dtype=str)
    df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0)
    df['_month'] = df['合同月份'].astype(str).str[:7]
    m = pd.read_excel(SRC_2025, sheet_name='销售部门区域对应', dtype=str)
    sp2dept = {str(r['销售员']).strip(): str(r['部门']).strip()
               for _, r in m.iterrows() if pd.notna(r['销售员'])}
    person_dept, lzy = core.load_mappings()

    std = df[(df['单据类型'] == '标准销售订单') & (df['是否赠品'] != '是')].copy()
    std = std[~std['客户'].astype(str).str.contains(core.BAORUI_PATTERN, na=False)]
    std['_amt'] = std['_amt'].astype(float)

    md, mdc = {}, {}
    for _, r in std.iterrows():
        m5 = str(r['_month'])
        if not ('2025-01' <= m5 <= '2025-09'):
            continue
        sp = str(r['销售员']).strip()
        cust = str(r['客户']).strip()
        amt = float(r['_amt'])
        dept = norm_dept(sp2dept.get(sp))
        if dept is None:
            _, dept = core.map_person(sp, cust, person_dept, lzy, str(r.get('产品类型#', '')))
        if dept is None:
            continue
        md.setdefault(m5, {}); md[m5][dept] = md[m5].get(dept, 0) + amt
        mdc.setdefault(m5, {}).setdefault(dept, {})
        mdc[m5][dept][cust] = mdc[m5][dept].get(cust, 0) + amt
    return md, mdc


# ---------------------------------------------------------------- 目标
def load_targets():
    """读权威目标源，返回 12 个月（1-12）的部门月度目标（元）。"""
    if os.path.exists(TARGET_PREF):
        with open(TARGET_PREF, encoding='utf-8') as f:
            t = json.load(f)
        depts = {}
        for d in DEPTS:
            months = list(t['depts'][d]['months'])
            assert len(months) == 12, f'{d} 目标月份数 != 12'
            depts[d] = {'annual': t['depts'][d]['annual'], 'months': months}
        mt = t['monthly_total']
        total_months = [mt[str(i)] for i in range(1, 13)]
        return os.path.basename(TARGET_PREF), depts, total_months, t['annual_total']
    # 回退
    with open(TARGET_FALLBACK, encoding='utf-8') as f:
        t = json.load(f)
    depts = {}
    for d in DEPTS:
        months = list(t['depts'][d]['months'])
        assert len(months) == 12, f'{d} 目标月份数 != 12'
        depts[d] = {'annual': t['depts'][d]['annual'], 'months': months}
    total_months = list(t['monthly'])
    return os.path.basename(TARGET_FALLBACK), depts, total_months, t['annual']


def r2(x):
    return round(float(x) / W, 2)


def main():
    fp26, md26, mdc26, mds26, sp_custs = extract_2026()
    src25, mdc25 = (None, None)
    res25 = extract_2025()
    md25 = mdc25 = None
    if res25:
        md25, mdc25 = res25
        src25 = os.path.basename(SRC_2025)
    tgt_name, tdepts, tmonths_total, tannual_total = load_targets()

    months_all = MONTHS_ALL      # 12 个月（目标）
    months_act = MONTHS_ACT      # 9 个月（实际/2025 同期）
    months25key = {f'2026-{m:02d}': f'2025-{m:02d}' for m in range(1, 10)}

    depts_out = {}
    for d in DEPTS:
        # 目标：12 个月全给
        tm = {months_all[i]: r2(tdepts[d]['months'][i]) for i in range(12)}
        # 实际 / 2025 同期：仅 1-9 月（10-12 月不写键 → 前端显示空白，绝不写 0）
        am = {months_act[i]: r2(md26.get(months_act[i], {}).get(d, 0)) for i in range(9)}
        a25 = {}
        for i in range(9):
            k26, k25 = months_act[i], months25key[months_act[i]]
            if md25 is None:
                a25[k26] = None
            else:
                a25[k26] = r2(md25.get(k25, {}).get(d, 0))
        depts_out[d] = {'target_annual': tdepts[d]['annual'],
                        'target_monthly': tm, 'actual_monthly': am, 'actual_2025': a25}

    total_out = {
        'target_annual': tannual_total,
        # 合计 = 三部门之和（保证 部门合计 == total，避免独立四舍五入产生 0.01 差）
        'target_monthly': {m: round(sum(depts_out[d]['target_monthly'][m] for d in DEPTS), 2) for m in months_all},
        'actual_monthly': {m: round(sum(depts_out[d]['actual_monthly'][m] for d in DEPTS), 2) for m in months_act},
    }
    if md25 is None:
        total_out['actual_2025'] = {m: None for m in months_act}
    else:
        total_out['actual_2025'] = {
            m: round(sum(depts_out[d]['actual_2025'][m] for d in DEPTS), 2) for m in months_act}

    # ── Q4（2026-10 ~ 12）目标与年度缺口 ───────────────────────────────────────
    # 口径：部门月度目标（target_monthly，2位小数）先按 1 位小数取整再加总，
    #       与 kpi_scheme.json meta.h2_targets 的展示口径一致（200.7/340.9/134.5…）。
    # annual_shortfall_at_full_q4 = 年度目标 − 1-9月实际 − Q4目标
    #   （即使 Q4 完全达标，全年仍差这么多；本年度实际仅到 9 月，此为 Q4 补缺口的最低要求。）
    _r1 = lambda x: round(float(x), 1)
    q4_months = ['2026-10', '2026-11', '2026-12']
    q4_by_dept = {d: _r1(sum(_r1(depts_out[d]['target_monthly'][m]) for m in q4_months)) for d in DEPTS}
    q4_target = _r1(sum(q4_by_dept.values()))
    ytd_actual_1dp = _r1(sum(total_out['actual_monthly'].values()))
    shortfall = _r1(tannual_total - ytd_actual_1dp - q4_target)
    q4 = {
        'months': q4_months,
        'target': q4_target,
        'target_by_dept': q4_by_dept,
        'annual_shortfall_at_full_q4': shortfall,
    }

    # customers / salespersons（2026 1-9，aml>0，金额降序）
    customers = []
    for m in months_act:
        for d in DEPTS:
            for cust, amt in mdc26.get(m, {}).get(d, {}).items():
                v = r2(amt)
                if v <= 0:
                    continue
                k25 = months25key[m]
                a25 = r2(mdc25.get(k25, {}).get(d, {}).get(cust, 0)) if mdc25 else 0.0
                customers.append({'month': m, 'dept': d, 'cust': cust,
                                  'amt': v, 'amt_2025': a25})
    # [FIX 2026-10-05] 补 2025-only 客户（2026 同期为 0）。
    # 原实现只遍历 2026，导致「纯 2025 有、2026 无」的客户根本不进数组 →
    # 驱动层无法算出「流失客户」（恒为 0），且瀑布图基线被低估成 1,662.4 万
    # （正确应为 2,749.3 万）。补入后 Σamt_2025 覆盖全量 2025，瀑布图可勾稽。
    _seen26 = {(c['month'], c['dept'], c['cust']) for c in customers}
    _added25 = 0
    for m in months_act:
        k25 = months25key[m]
        for d in DEPTS:
            for cust, amt in (mdc25 or {}).get(k25, {}).get(d, {}).items():
                v = r2(amt)
                if v <= 0 or (m, d, cust) in _seen26:
                    continue
                customers.append({'month': m, 'dept': d, 'cust': cust,
                                  'amt': 0.0, 'amt_2025': v, 'only2025': True})
                _added25 += 1
    print(f'   [FIX] 补入 2025-only 客户 {_added25} 条（用于流失口径）')
    customers.sort(key=lambda x: (x['month'], x['dept'], -x['amt']))

    salespersons = []
    for m in months_act:
        for d in DEPTS:
            for sp, amt in mds26.get(m, {}).get(d, {}).items():
                v = r2(amt)
                if v <= 0:
                    continue
                salespersons.append({'month': m, 'dept': d, 'sp': sp,
                                     'amt': v, 'custs': len(sp_custs.get((m, d, sp), set()))})
    salespersons.sort(key=lambda x: (x['month'], x['dept'], -x['amt']))

    note = (
        '口径：ERP 标准销售订单 + 单据状态=已审核 + 非赠品(是否赠品≠是) + 剔除宝锐/宝泰/横琴自体系客户；'
        '金额=价税合计（含税，元→万元）。部门归一判定顺序：大客户→生命科学→销售拓展。'
        f'2026 源=ERP现用件 {os.path.basename(fp26)}。'
        f'2025 同期源={src25}（该历史文件无「单据状态」字段，故沿用 标准销售订单+非赠品 口径，'
        '2025部门由「销售部门区域对应」表归一，科研-* 计入生命科学部；2025 年 1-9 月合计 2749.27 万，与已知锚点一致）。'
        f'目标源={tgt_name}（全年 12 个月：1-6月采用H1官方月度目标，7-12月按部门节奏缩放至年度目标余额；年度 大客户2650/拓展3400/生命科学900=合计6950 万。10-12月目标为 2026-10-06 由本脚本接入）。'
        'actual_2025 键沿用 2026 月标签（同比对齐）；'
        'target_monthly 含 12 个月（1-12）；actual_monthly / actual_2025 仅 1-9 月（10-12 月无 ERP 数据 → 不写键，保持空白，绝不写 0）；'
        'q4 = 10-12 月目标与年度缺口。'
        'customers/salespersons 为 2026 1-9 月，按金额降序，仅保留金额>0（四舍五入到万元后仍 >0）。'
    )

    out = {
        'meta': {
            'generated': datetime.datetime.now().astimezone().isoformat(timespec='seconds'),
            'source_erp': os.path.basename(fp26),
            'source_target': tgt_name,
            'note': note,
            'q4_caliber': ('Q4 目标 = 部门 10-12 月月度目标（target_monthly，2位小数）先按 1 位小数取整再加总，'
                           '与 kpi_scheme.json meta.h2_targets 展示口径一致；'
                           'annual_shortfall_at_full_q4 = 年度目标(6950) − 1-9月实际 − Q4目标。'),
        },
        'months': months_all,
        'depts': depts_out,
        'total': total_out,
        'q4': q4,
        'customers': customers,
        'salespersons': salespersons,
    }

    if os.path.exists(OUT):
        ts = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
        shutil.copy2(OUT, OUT + f'.bak_{ts}')
        print('备份原文件 ->', OUT + f'.bak_{ts}')
    with open(OUT, 'w', encoding='utf-8') as f:
        json.dump(out, f, ensure_ascii=False, indent=2)
    print('已写出:', OUT)

    # ------------------------------------------------ 自检门禁
    print('\n================ 自检门禁 ================')
    a_tot = {m: sum(x for x in [depts_out[d]['actual_monthly'][m] for d in DEPTS]) for m in months_act}
    ytd = sum(a_tot.values())
    print('\n月份     大客户部   销售拓展部  生命科学部 |  实际合计   该月目标   达成率')
    for m in months_act:
        row = [depts_out[d]['actual_monthly'][m] for d in DEPTS]
        tgt = total_out['target_monthly'][m]
        rate = a_tot[m] / tgt * 100 if tgt else 0
        print(f'{m}  {row[0]:9.2f} {row[1]:10.2f} {row[2]:9.2f} | {a_tot[m]:9.2f} {tgt:9.2f}  {rate:6.2f}%')
    ytd_tgt = sum(total_out['target_monthly'][m] for m in months_act)
    print(f'YTD合计  {sum(depts_out["大客户部"]["actual_monthly"].values()):9.2f} '
          f'{sum(depts_out["销售拓展部"]["actual_monthly"].values()):10.2f} '
          f'{sum(depts_out["生命科学部"]["actual_monthly"].values()):9.2f} | '
          f'{ytd:9.2f} {ytd_tgt:9.2f}  {ytd/ytd_tgt*100:6.2f}%')

    # ① 2026 1-9 合计锚点
    g1 = abs(ytd - 3443.0) <= 10
    print(f'\n① 2026 1-9 合计 = {ytd:.2f} 万（锚点 3443.0 ±10）-> {"PASS" if g1 else "FAIL"}')
    # ② 三部门 YTD 之和 == total YTD
    s_dept = sum(sum(depts_out[d]['actual_monthly'].values()) for d in DEPTS)
    g2 = abs(s_dept - ytd) < 0.01
    print(f'② 三部门YTD之和 = {s_dept:.2f} vs total {ytd:.2f} -> {"PASS" if g2 else "FAIL"}')
    # ③ 每月三部门之和 == 该月 total
    g3 = all(abs(a_tot[m] - total_out['actual_monthly'][m]) < 0.01 for m in months_act)
    print(f'③ 每月三部门之和==total该月 -> {"PASS" if g3 else "FAIL"}')
    # ④ 达成率
    rates = {d: sum(depts_out[d]['actual_monthly'].values()) / depts_out[d]['target_annual'] * 100 for d in DEPTS}
    tot_rate = ytd / total_out['target_annual'] * 100
    exp = {'大客户部': 41.77, '销售拓展部': 67.53, '生命科学部': 4.43}
    okd = all(abs(rates[d] - exp[d]) <= 1 for d in DEPTS) and abs(tot_rate - 49.54) <= 1
    print('④ 达成率(实际/年度目标): ' + '  '.join(f'{d}={rates[d]:.2f}%' for d in DEPTS) + f'  合计={tot_rate:.2f}%')
    print(f'   期望 大客户41.77/拓展67.53/生命科学4.43/合计49.54 -> {"PASS" if okd else "FAIL"}')
    # ⑤ 目标 12 个月且全年合计 == 6950；10-12 月实际必须无 0
    g5 = (len(out['months']) == 12
          and len(total_out['target_monthly']) == 12
          and all(len(depts_out[d]['target_monthly']) == 12 for d in DEPTS)
          and abs(sum(total_out['target_monthly'].values()) - 6950) <= 0.5)
    no_zero_1012 = all(
        (m not in depts_out[d]['actual_monthly']) or depts_out[d]['actual_monthly'][m] is None
        for d in DEPTS for m in q4_months)
    g5 = g5 and no_zero_1012 and (len(depts_out['大客户部']['actual_monthly']) == 9)
    print(f'⑤ 目标月份={len(out["months"])} 全年目标合计={sum(total_out["target_monthly"].values()):.2f} 万(锚 6950±0.5) '
          f'/ 10-12月实际无 0 -> {"PASS" if g5 else "FAIL"}')
    # ⑥ Q4 目标与年度缺口
    g6 = (abs(q4['target'] - 2052.9) < 0.05 and abs(q4['annual_shortfall_at_full_q4'] - 1454.3) < 0.05)
    print(f'⑥ Q4目标={q4["target"]} Q4分部门={q4["target_by_dept"]} 年度缺口={q4["annual_shortfall_at_full_q4"]} -> {"PASS" if g6 else "FAIL"}')
    print(f'\ncustomers={len(customers)}  salespersons={len(salespersons)}')
    if md25:
        y25 = sum(sum(md25.get(months25key[m], {}).values()) for m in months_act)
        print(f'2025 1-9 同期合计 = {y25/W:.2f} 万（锚点 2749.3 ±10）-> {"PASS" if abs(y25/W-2749.3)<=10 else "FAIL"}')
    print('\nALL GATES:', 'PASS' if (g1 and g2 and g3 and okd and g5 and g6) else 'CHECK')


if __name__ == '__main__':
    main()