#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""科研（生命科学销售部）销售金额排名 v2：2026-08-20 ~ 2026-09-30，按人 + 按区域。

v2 改动（v1 漏人）：
  名册 = 人员清单(19人) ∪ 数据/dept_overrides.json 里部门=生命科学销售部的人（陈彩红，用户确认）
  覆盖断言改为「在任一权威名册都没有的人」才报，不再把外部门的人误报成未映射。

口径（取自管道 脚本/erp_sales_core.py）：
  标准销售订单 & 已审核 & 非赠品 & 客户不含 宝锐|宝泰|横琴；金额=价税合计；区间按合同日期
区域归并：区域名含「浙江」→浙江大区；含「广州」或「广东」→广州大区
"""
import io
import json
import os
import sys
from collections import defaultdict

import pandas as pd

BASE = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库'
ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
ORD = BASE + '/月度销售额数据/销售订单_20260101-20260930.xlsx'
ORG = BASE + '/销售组织架构/统一工作台运行及授权.xlsx'
OVR = ROOT + '/数据/dept_overrides.json'
DEPT = '生命科学销售部'
A, B = '2026-08-20', '2026-09-30'

ok = True
def chk(label, cond, detail=''):
    global ok
    print('  %s %-42s %s' % ('✅' if cond else '❌', label, detail))
    if not cond:
        ok = False

def big(r):
    r = str(r)
    if '浙江' in r:
        return '浙江大区'
    if '广州' in r or '广东' in r:
        return '广州大区'
    return r or '未标区域'

# ── 名册：人员清单 + 补充映射 ──
org = pd.read_excel(ORG, sheet_name='人员清单')
org['_n'] = org['销售员'].astype(str).str.strip()
dept = org[org['部门'] == DEPT][['_n', '区域', '角色', '是否在职']].rename(columns={'_n': '销售员'}).copy()
dept['来源'] = '人员清单'

ov = json.load(io.open(OVR, encoding='utf-8'))
extra = []
for name, v in ov.items():
    if name.startswith('_') or not isinstance(v, dict):
        continue
    if (v.get('dept') or '').strip() == DEPT:
        extra.append({'销售员': name, '区域': v.get('region') or '', '角色': '销售工程师',
                      '是否在职': '在职', '来源': 'dept_overrides（' + str(v.get('from', ''))[:26] + '）'})
if extra:
    dept = pd.concat([dept, pd.DataFrame(extra)], ignore_index=True)
dept['大区'] = dept['区域'].map(big)
print('=== 名册 ===')
print('  人员清单 %s 命中 %d 人 ｜ 补充映射 +%d 人' % (DEPT, len(dept) - len(extra), len(extra)))
for e in extra:
    print('    + %s（%s → %s）' % (e['销售员'], e['区域'], big(e['区域'])))

# ── 订单 ──
df = pd.read_excel(ORD)
df['_d'] = pd.to_datetime(df['合同日期'], errors='coerce').dt.strftime('%Y-%m-%d')
df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0.0)
std = df[(df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核') & (df['是否赠品'] != '是')]
std = std[~std['客户'].astype(str).str.contains('宝锐|宝泰|横琴', na=False)]
std = std.copy()
std['_sp'] = std['销售员'].astype(str).str.strip()
win = std[(std['_d'] >= A) & (std['_d'] <= B)].copy()
print('\n=== 区间 ===')
print('  %s ~ %s ｜ 全公司有效行 %d ｜ 合计 %.4f 万' % (A, B, len(win), win['_amt'].sum() / 1e4))

# ── 逐人 ──
names = set(dept['销售员'])
sub = win[win['_sp'].isin(names)].copy()
by = defaultdict(lambda: {'amt': 0.0, 'custs': set(), 'docs': set(), 'rows': 0})
for _, r in sub.iterrows():
    d = by[r['_sp']]
    d['amt'] += float(r['_amt'])
    d['custs'].add(str(r['客户']).strip())
    d['docs'].add(str(r['单据编号']))
    d['rows'] += 1

rows = []
for _, p in dept.iterrows():
    n = p['销售员']
    v = by.get(n, {'amt': 0.0, 'custs': set(), 'docs': set(), 'rows': 0})
    rows.append({'销售员': n, '大区': p['大区'], '区域': p['区域'], '角色': p['角色'],
                 '是否在职': p['是否在职'], '来源': p['来源'],
                 '金额': round(v['amt'] / 1e4, 4), '客户数': len(v['custs']),
                 '订单数': len(v['docs']), '行数': v['rows'],
                 '单均': round(v['amt'] / 1e4 / len(v['docs']), 3) if v['docs'] else 0.0})
rows.sort(key=lambda r: (-r['金额'], r['销售员']))
total = round(sum(r['金额'] for r in rows), 4)
for i, r in enumerate(rows, 1):
    r['名次'] = i if r['金额'] > 0 else 0
    r['占比'] = round(r['金额'] * 100.0 / total, 2) if total else 0.0

# ── 按大区 ──
reg = defaultdict(lambda: {'amt': 0.0, 'custs': set(), 'docs': set(), 'people': 0, 'active': 0})
for r in rows:
    g = reg[r['大区']]
    g['amt'] += r['金额']
    g['people'] += 1
    if r['金额'] > 0:
        g['active'] += 1
for _, r in sub.iterrows():
    g = reg[dept.set_index('销售员').loc[r['_sp'], '大区']]
    g['custs'].add(str(r['客户']).strip())
    g['docs'].add(str(r['单据编号']))
regions = sorted([{'大区': k, '金额': round(v['amt'], 4), '客户数': len(v['custs']),
                   '订单数': len(v['docs']), '人数': v['people'], '出单人数': v['active'],
                   '占比': round(v['amt'] * 100.0 / total, 2) if total else 0.0}
                  for k, v in reg.items()], key=lambda r: -r['金额'])

# ── 校验 ──
print('\n=== 校验 ===')
chk('区间端点 = 2026-09-30（导出件已含）', df['_d'].max() == B, df['_d'].max())
allroster = set(org['_n'])
appear = set(win['_sp'])
truly_miss = sorted(appear - allroster - set(ov.keys()))
for n in truly_miss:
    s = win[win['_sp'] == n]
    print('     ⚠️ 未在任何名册: %s ｜ %.4f 万 ｜ %d 行' % (n, s['_amt'].sum() / 1e4, len(s)))
chk('区间内的人都在名册或补充映射内', not truly_miss, str(truly_miss))
chk('勾稽：Σ逐人 == 明细加总', abs(sum(r['金额'] for r in rows) - sub['_amt'].sum() / 1e4) < 1e-3,
    '%.4f vs %.4f' % (sum(r['金额'] for r in rows), sub['_amt'].sum() / 1e4))
chk('勾稽：Σ各大区 == 部门合计', abs(sum(r['金额'] for r in regions) - total) < 1e-3,
    '%.4f vs %.4f' % (sum(r['金额'] for r in regions), total))
chk('出单人数 == 金额>0 的人数', sum(1 for r in rows if r['金额'] > 0) == sum(x['出单人数'] for x in regions))

# ── 未计入但持本线权限者 ──
line = org[org['可下单产品线'].astype(str).str.contains('生命科学', na=False)]
outs = []
for _, p in line[line['部门'] != DEPT].iterrows():
    a = win[win['_sp'] == p['_n']]['_amt'].sum() / 1e4
    if a > 0:
        outs.append({'销售员': p['_n'], '部门': p['部门'], '区域': p['区域'], '金额': round(a, 4)})
# 名册外但有单的大额人（如刘子研）单独交代
rosterless = []
for n in sorted(appear - allroster):
    a = win[win['_sp'] == n]['_amt'].sum() / 1e4
    if a > 0:
        rosterless.append({'销售员': n, '金额': round(a, 4),
                           '月份': {k: int(v) for k, v in win[win['_sp'] == n]['_d'].str[:7].value_counts().sort_index().items()},
                           '客户样例': '、'.join(sorted({str(c) for c in win[win['_sp'] == n]['客户']})[:3])[:80]})

print('\n=== 按人排名（%s ｜ %s ~ %s）===' % (DEPT, A, B))
print('%-4s %-6s %-7s %-9s %-5s %10s %7s %5s %5s %8s' % ('名次', '销售员', '大区', '区域', '在职', '金额(万)', '占比%', '客户', '订单', '单均(万)'))
for r in rows:
    print('%-4s %-6s %-7s %-9s %-5s %10.4f %7.2f %5d %5d %8.3f'
          % (r['名次'] or '—', r['销售员'], r['大区'], r['区域'], r['是否在职'],
             r['金额'], r['占比'], r['客户数'], r['订单数'], r['单均']))
print('%-4s %-6s %-7s %-9s %-5s %10.4f %7.2f' % ('', '合计', '', '', '', total, 100.0))

print('\n=== 按区域（大区）===')
for r in regions:
    print('  %-7s %9.4f 万 ｜ %5.2f%% ｜ %2d 人（出单 %d）｜ 客户 %d ｜ 订单 %d'
          % (r['大区'], r['金额'], r['占比'], r['人数'], r['出单人数'], r['客户数'], r['订单数']))

print('\n=== 交代项 ===')
print('  ① 未计入本部门但持生命科学线权限者：')
for o in outs:
    print('     %-6s %-8s %-8s %8.4f 万' % (o['销售员'], o['部门'], o['区域'], o['金额']))
if not outs:
    print('     （无）')
print('  ② 区间内有单但不在「人员清单」的人（名册缺口）：')
for o in rosterless:
    print('     %-6s %9.4f 万 ｜ %s ｜ %s' % (o['销售员'], o['金额'], o['月份'], o['客户样例']))
if not rosterless:
    print('     （无）')

payload = {'dept': DEPT, 'start': A, 'end': B, 'generated': pd.Timestamp.now().strftime('%Y-%m-%d %H:%M'),
           'src': os.path.basename(ORD), 'roster': '统一工作台运行及授权.xlsx / 人员清单 + 数据/dept_overrides.json',
           'caliber': '单据类型=标准销售订单 & 单据状态=已审核 & 是否赠品≠是 & 客户不含(宝锐|宝泰|横琴)；金额=价税合计；区间按合同日期',
           'total': total, 'people': rows, 'regions': regions,
           'outsiders': outs, 'rosterless': rosterless}
os.makedirs(os.path.dirname(ROOT + '/销售分析/x'), exist_ok=True)
io.open(ROOT + '/销售分析/科研销售_按人与区域_20260820-20260930.json', 'w', encoding='utf-8').write(
    json.dumps(payload, ensure_ascii=False, indent=1))
print('\nJSON 已写')
sys.exit(0 if ok else 1)