#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""建客户级台账：应收账龄 × ERP 2026 营收（含部门/销售员），识别逾期与营收主力客户

输出：数据/2026年10月应收客户账龄_主力客户.json
"""
import io
import json
import warnings

warnings.filterwarnings('ignore')
import pandas as pd  # noqa: E402

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
ERP = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261015.xlsx'
B = ['账期内', '逾期1-30天', '逾期31-60天', '逾期61-365天', '逾期365天以上（一年以上）']

D1 = json.load(io.open(W + '/数据/2026年10月应收账龄_大客户部.json', encoding='utf-8'))
D2 = json.load(io.open(W + '/数据/2026年10月应收账龄_销售拓展部.json', encoding='utf-8'))
DEPT_SHORT = {'诊断原料大客户销售部': '大客户部', '诊断原料销售拓展部': '销售拓展部'}

# ── 客户级应收台账 ──
led = {}
for D in (D1, D2):
    for r in D['detail']:
        k = r['cust']
        o = led.setdefault(k, {
            'cust': k, 'dept': DEPT_SHORT.get(r['dept'], r['dept']), 'sp': r['sp'],
            'ar': 0.0, 'overdue': 0.0, 'buckets': {b: 0.0 for b in B}, 'n': 0, 'n_od': 0,
            'max_days': 0, 'max_bill': '', 'oct_pay': 0.0,
        })
        o['ar'] += r['amt']
        o['n'] += 1
        o['oct_pay'] += r.get('oct_pay', 0) or 0
        o['buckets'][r['bucket']] += r['amt']
        if r['overdue']:
            o['overdue'] += r['amt']
            o['n_od'] += 1
            if (r['od_days'] or 0) > o['max_days']:
                o['max_days'] = r['od_days'] or 0
                o['max_bill'] = r['bill']
for o in led.values():
    for k in ('ar', 'overdue', 'oct_pay'):
        o[k] = round(o[k], 2)

# ── ERP 2026 营收（剔除赠品）──
x = pd.read_excel(ERP)
x['是否赠品'] = x['是否赠品'].astype(str).str.strip()
x['价税合计'] = pd.to_numeric(x['价税合计'], errors='coerce').fillna(0)
paid = x[(x['是否赠品'] != '是') & (x['价税合计'] > 0)]
rev = paid.groupby(paid['客户'].astype(str).str.strip())['价税合计'].sum().to_dict()
rev_sp = paid.groupby([paid['客户'].astype(str).str.strip(), paid['销售员'].astype(str).str.strip()])['价税合计'].sum().to_dict()

for o in led.values():
    o['rev2026'] = round(float(rev.get(o['cust'], 0)), 2)
    o['matched'] = o['cust'] in rev
    # ERP 侧销售员（可能有多个）
    sps = sorted({sp for (c, sp), v in rev_sp.items() if c == o['cust'] and v > 0})
    o['erp_sp'] = sps
    o['exposure'] = round(o['overdue'] / o['rev2026'] * 100, 1) if o['rev2026'] > 0 else None

custs = sorted(led.values(), key=lambda o: -o['ar'])
tot_ar = round(sum(o['ar'] for o in custs), 2)
tot_ov = round(sum(o['overdue'] for o in custs), 2)
tot_rev = round(sum(o['rev2026'] for o in custs), 2)
print('█ 客户台账 %d 家 ｜ 应收合计 %s 元 ｜ 逾期 %s 元 ｜ ERP 2026 营收 %s 元'
      % (len(custs), format(tot_ar, ',.2f'), format(tot_ov, ',.2f'), format(tot_rev, ',.2f')))
print('   未匹配 ERP（本年零营收）: %d 家 ｜ 其中逾期>0 的 %d 家，逾期合计 %s 元'
      % (sum(1 for o in custs if not o['matched']),
         sum(1 for o in custs if not o['matched'] and o['overdue'] > 0),
         format(sum(o['overdue'] for o in custs if not o['matched']), ',.2f')))
print()
print('█ 逾期主力客户 Top12（全体）')
print('   %-30s %-8s %-8s %12s %6s %10s %6s' % ('客户', '部门', '销售员', '逾期(元)', '单数', '最长天数', '>60天'))
for o in sorted(custs, key=lambda o: -o['overdue'])[:12]:
    o60 = sum(o['buckets'][b] for b in B[3:])
    print('   %-30s %-8s %-8s %12s %6d %10d %6s' % (o['cust'][:30], o['dept'], o['sp'], format(o['overdue'], ',.0f'),
                                                    o['n_od'], o['max_days'], format(o60, ',.0f')))
print()
print('█ 营收主力客户 Top12（ERP 2026，剔除赠品）')
print('   %-30s %-8s %-8s %12s %12s %10s %8s' % ('客户', '部门', '销售员', '2026营收', '应收余额', '逾期', '逾期/营收'))
for o in sorted(custs, key=lambda o: -o['rev2026'])[:12]:
    print('   %-30s %-8s %-8s %12s %12s %10s %8s' % (o['cust'][:30], o['dept'], o['sp'],
          format(o['rev2026'], ',.0f'), format(o['ar'], ',.0f'), format(o['overdue'], ',.0f'),
          ('%.1f%%' % o['exposure']) if o['exposure'] is not None else '—'))
print()
print('█ 高风险：长期挂账且本年零营收')
for o in sorted([o for o in custs if not o['matched'] and o['overdue'] > 0], key=lambda o: -o['overdue']):
    print('   %-30s %-8s %-8s 逾期 %10s 元 ｜ 最长 %4d 天 ｜ 单数 %d' % (o['cust'][:30], o['dept'], o['sp'],
          format(o['overdue'], ',.0f'), o['max_days'], o['n_od']))

# 按销售员汇总营收
by_sp = {}
for o in custs:
    s = by_sp.setdefault(o['sp'], {'dept': o['dept'], 'rev': 0.0, 'ar': 0.0, 'overdue': 0.0, 'n': 0})
    s['rev'] += o['rev2026']
    s['ar'] += o['ar']
    s['overdue'] += o['overdue']
    s['n'] += 1
for k, v in by_sp.items():
    for kk in ('rev', 'ar', 'overdue'):
        v[kk] = round(v[kk], 2)
    v['exposure'] = round(v['overdue'] / v['rev'] * 100, 1) if v['rev'] > 0 else None
print()
print('█ 按销售员：营收 vs 逾期')
for sp, v in sorted(by_sp.items(), key=lambda kv: -kv[1]['rev']):
    print('   %-8s %-8s 营收 %12s ｜ 应收 %12s ｜ 逾期 %12s ｜ 逾期/营收 %s'
          % (sp, v['dept'], format(v['rev'], ',.0f'), format(v['ar'], ',.0f'), format(v['overdue'], ',.0f'),
             ('%.1f%%' % v['exposure']) if v['exposure'] is not None else '—'))

json.dump({'meta': {'generated': D1['meta']['generated'], 'period': '2026年10月',
                    'erp_source': '销售订单_20260101-20261015.xlsx（剔除赠品/测试）',
                    'tot_ar': tot_ar, 'tot_ov': tot_ov, 'tot_rev': tot_rev, 'n_cust': len(custs)},
           'customers': custs, 'by_sp': by_sp},
          io.open(W + '/数据/2026年10月应收客户账龄_主力客户.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
print()
print('✅ JSON: 数据/2026年10月应收客户账龄_主力客户.json')