#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""大客户部 · 货号下降待判定清单（客户 × 货号 粒度）

新口径：下降原因三选一（人工判定）
  ① 丢单        客户需求量未变，流向其他供应商  → 需备注「丢给谁」
  ② 需求减少    没丢单，客户需求量下降          → 备注可选（原因）
  ③ 换号        没丢单，客户改用我方其他货号     → 需备注「换成什么货号」

脚本只做三件事：算下降、算自动线索（同客户其他货号增长 / 同母体主号变化）、给「建议」（供人工参考，不代替判定）
输出：数据/大客户部_货号下降_待判定.json
"""
import io
import json
import os
import re
import warnings
from collections import defaultdict

warnings.filterwarnings('ignore')
import openpyxl  # noqa: E402

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = [('2025', '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2025全年订单.xlsx'),
       ('2026', __import__('sys').path.insert(0, '/Users/liuxinyuan/Desktop/Hermes输出-工作类/脚本') or __import__('erp_sales_core').find_latest_erp())]
M25 = {'2025-%02d' % i for i in range(1, 10)}
M26 = {'2026-%02d' % i for i in range(1, 10)}
DK_SP = {'刘子研', '赵云浩', '张立娅', '黄明月', '刘新元', '任鑫', '杜建'}
BAORUI = ('宝锐', '宝泰', '横琴')

rows = []
for tag, path in SRC:
    wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
    ws = wb['Sheet1'] if tag == '2025' else wb[wb.sheetnames[0]]
    hdr = None
    for r in ws.iter_rows(min_row=1, values_only=True):
        if hdr is None:
            hdr = [str(x or '').strip() for x in r]
            ix = {k: hdr.index(k) for k in hdr if k in
                  ('货号#', '物料名称', '价税合计', '销售数量', '客户', '销售员', '是否赠品', '单据类型', '单据状态', '合同月份', '合同日期')}
            continue
        g = {k: (r[v] if v < len(r) else None) for k, v in ix.items()}
        if '标准' not in str(g.get('单据类型') or ''):
            continue
        if str(g.get('是否赠品') or '').strip() == '是':
            continue
        if '单据状态' in g and str(g.get('单据状态') or '').strip() not in ('已审核', ''):
            continue
        c = str(g.get('客户') or '').strip()
        if not c or any(k in c for k in BAORUI):
            continue
        mon = str(g.get('合同月份') or g.get('合同日期') or '')[:7]
        if tag == '2025' and mon not in M25:
            continue
        if tag == '2026' and mon not in M26:
            continue
        s = str(g.get('货号#') or '').strip()
        if not s:
            continue
        try:
            amt = float(g.get('价税合计') or 0)
        except Exception:
            amt = 0.0
        try:
            qty = float(g.get('销售数量') or 0)
        except Exception:
            qty = 0.0
        rows.append({'y': tag, 'cust': c, 'sp': str(g.get('销售员') or '').strip(), 'sku': s,
                     'name': str(g.get('物料名称') or '').strip(), 'amt': amt, 'qty': qty, 'mon': mon})
print('█ 有效明细 %d 行' % len(rows))

# 大客户部客户 = 权威口径清单（数据/大客户部_客户清单.json）
#   归属链路：apply_sales_owner.CUSTOMER_OWNER_OVERRIDE > customer_sp_map.json
#             人员归并 黄明月/张立娅/刘新元 → 刘子研；部门取 目标基础数据_2026.json
_scope = json.load(io.open(W + '/数据/大客户部_客户清单.json', encoding='utf-8'))
dk_cust = {x['cust'] for x in _scope['customers']}
sp_of = {x['cust']: x.get('owner', '') for x in _scope['customers']}
print('█ 大客户部客户 %d 家（权威口径，非按 ERP 销售员圈）' % len(dk_cust))
rows = [r for r in rows if r['cust'] in dk_cust]
assert rows, '大客户部客户无有效明细'
print('█ 圈定后有效明细 %d 行 ｜ 客户 %d 家' % (len(rows), len({r['cust'] for r in rows})))

# 客户 × 货号 聚合
ag = defaultdict(lambda: {'a25': 0.0, 'a26': 0.0, 'q25': 0.0, 'q26': 0.0, 'n25': 0, 'n26': 0, 'last': '', 'name': ''})
for r in rows:
    k = (r['cust'], r['sku'])
    d = ag[k]
    if r['y'] == '2025':
        d['a25'] += r['amt']; d['q25'] += r['qty']; d['n25'] += 1
    else:
        d['a26'] += r['amt']; d['q26'] += r['qty']; d['n26'] += 1; d['last'] = max(d['last'], r['mon'])
    if r['name']:
        d['name'] = r['name']

# 同客户货号变化（做「可能换成什么」的线索）
by_cust = defaultdict(dict)
for (c, s), d in ag.items():
    by_cust[c][s] = d

# 货号主号映射（判断是否同母体/同主号）
MAP = {}
mp = W + '/数据/货号主号映射.json'
if os.path.exists(mp):
    fams = json.load(io.open(mp, encoding='utf-8'))['families']
    for m, v in fams.items():
        members = [x['sku'] for x in v['comps']] + [x['sku'] for x in v['mains']]
        for x in members:
            MAP[x] = m

def mother(s):
    if s in MAP:
        return MAP[s]
    return re.sub(r'-(DB|TP|4P|FP|6P)$', '', re.sub(r'-\d$', '', s))

MIN25 = 10000.0      # 2025 同期 ≥1 万才纳入判定
DROP = 0.30          # 下降 ≥30% 才算「下降待判定」
declines = []
for (c, s), d in ag.items():
    a25, a26 = d['a25'], d['a26']
    if a25 < MIN25:
        continue
    if a26 >= a25 * (1 - DROP):
        continue
    pct = (a26 - a25) / a25 * 100
    # 线索 1：同客户其他货号 2026 相对 2025 的增量（可能换号去向）
    ups = []
    for s2, d2 in by_cust[c].items():
        if s2 == s:
            continue
        delta = d2['a26'] - d2['a25']
        if delta > 0:
            ups.append({'sku': s2, 'name': d2['name'][:38], 'delta': round(delta), 'same_mother': mother(s2) == mother(s)})
    ups.sort(key=lambda x: (-x['same_mother'], -x['delta']))
    # 线索 2：同母体整体是否也降（若整体降 → 更像需求减少/丢单；若整体升而本号降 → 更像换号）
    fam = mother(s)
    fam25 = sum(v['a25'] for (cc, ss), v in ag.items() if cc == c and mother(ss) == fam)
    fam26 = sum(v['a26'] for (cc, ss), v in ag.items() if cc == c and mother(ss) == fam)
    fam_pct = (fam26 - fam25) / fam25 * 100 if fam25 else 0
    # 线索 3：客户整体（大客户部口径）变化
    c25 = sum(v['a25'] for (cc, ss), v in ag.items() if cc == c)
    c26 = sum(v['a26'] for (cc, ss), v in ag.items() if cc == c)
    cust_pct = (c26 - c25) / c25 * 100 if c25 else 0
    # 建议（仅供参考）
    if fam26 > fam25 * 1.2 and fam_pct > 20:
        sug, why = '③ 可能换号', '同母体合计上升 %.0f%%，本货号下降 → 疑似换用同族其他货号' % fam_pct
    elif cust_pct < -20 and fam_pct < -20:
        sug, why = '② 可能需求减少', '客户整体 -%.0f%%、同母体 -%.0f%% → 更像客户需求收缩' % (abs(cust_pct), abs(fam_pct))
    else:
        sug, why = '① 需重点核实是否丢单', '客户整体 %.0f%%、同母体 %.0f%% → 需求侧不足以解释，可能流向他人' % (cust_pct, fam_pct)
    declines.append({
        'id': '%s|%s' % (c, s), 'cust': c, 'sp': sp_of.get(c, ''), 'sku': s, 'name': d['name'][:60],
        'mother': fam, 'a25': round(a25), 'a26': round(a26), 'pct': round(pct, 1),
        'q25': round(d['q25'], 1), 'q26': round(d['q26'], 1), 'n25': d['n25'], 'n26': d['n26'],
        'last': d['last'] or '—', 'fam25': round(fam25), 'fam26': round(fam26), 'fam_pct': round(fam_pct, 1),
        'cust25': round(c25), 'cust26': round(c26), 'cust_pct': round(cust_pct, 1),
        'hint_ups': ups[:3], 'suggest': sug, 'suggest_why': why,
        'reason': '', 'note_who': '', 'note_sku': '', 'status': '待判定',
    })
declines.sort(key=lambda x: x['a25'] - x['a26'], reverse=True)
print('█ 下降待判定（客户×货号）%d 条 ｜ 涉及 %d 客户 ｜ 下降金额合计 %.1f 万'
      % (len(declines), len({d['cust'] for d in declines}), sum(d['a25'] - d['a26'] for d in declines) / 1e4))

# 新机会：2026 新增 & 上量
news, grows = [], []
for (c, s), d in ag.items():
    if d['a26'] < 30000:
        continue
    if d['a25'] < 1:
        news.append({'cust': c, 'sp': sp_of.get(c, ''), 'sku': s, 'name': d['name'][:60], 'a26': round(d['a26']),
                     'first': '', 'last': d['last']})
    elif d['a26'] >= d['a25'] * 2:
        grows.append({'cust': c, 'sp': sp_of.get(c, ''), 'sku': s, 'name': d['name'][:60], 'a25': round(d['a25']),
                      'a26': round(d['a26']), 'pct': round((d['a26'] - d['a25']) / d['a25'] * 100, 0), 'last': d['last']})
news.sort(key=lambda x: -x['a26'])
grows.sort(key=lambda x: -(x['a26'] - x['a25']))
print('█ 新增（客户×货号 ≥3 万）%d 条 ｜ 上量（≥2×）%d 条' % (len(news), len(grows)))

out = {'meta': {'gen': __import__('datetime').date.today().isoformat(),
                'erp_2026': os.path.basename(SRC[1][1]), 'caliber': '标准销售订单+非赠品+已审核；2025 1-9月 vs 2026 1-9月（同期可比）',
                'scope': '大客户部客户=数据/大客户部_客户清单.json（归属 override+人员归并+部门聚合，2026-10-17 修正）',
                'scope_source': '三部门逐月_2026.json customers 表 dept=大客户部（52 家）',
                'dk_cust': len(dk_cust), 'min25': MIN25, 'drop_threshold': DROP,
                'reasons': {'1': '丢单（需求未变，流向其他供应商）', '2': '没丢单，客户需求量减少',
                            '3': '没丢单，客户改用我方其他货号'}},
       'declines': declines, 'news': news, 'grows': grows}
p = W + '/数据/大客户部_货号下降_待判定.json'
json.dump(out, io.open(p, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
print('✅ %s（%.0f KB）' % (p, os.path.getsize(p) / 1024))
print('\nTop 8 下降（按金额）：')
for d in declines[:8]:
    print('  %-22s %-24s %-30s %8.1f→%8.1f 万 %+6.1f%% ｜ 建议：%s' %
          (d['cust'][:20], d['sku'], d['name'][:26], d['a25'] / 1e4, d['a26'] / 1e4, d['pct'], d['suggest']))