#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""大客户部 货号级 2025 vs 2026：丢单识别 + 新机会/上量转化线索 + 活动/商机取证

口径：标准销售订单 + 非赠品（金额=价税合计，含税）；2025 取 1-9 月同期，2026 取 1-9 月（另给 1-10 月）
输出：数据/大客户部_货号丢单与新机会.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输出-工作类'
H = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2025全年订单.xlsx'
CUR = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261015.xlsx'
KP = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库'
M9 = ['2025-0%d' % i for i in range(1, 10)]
M9b = ['2026-0%d' % i for i in range(1, 10)]
DK_SP = {'刘子研', '赵云浩', '张立娅', '黄明月', '刘新元', '任鑫', '杜建'}   # 大客户部（含代管/离职）
BAORUI = ('宝锐', '宝泰', '横琴')


def sf(v):
    try:
        s = str(v).strip()
        if s in ('', '-', 'None', 'nan'):
            return None
        return float(s.replace(',', ''))
    except Exception:
        return None


def read_hist():
    wb = openpyxl.load_workbook(H, data_only=True, read_only=True)
    ws = wb['Sheet1']
    hdr = None
    rows = []
    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}
            continue
        g = {k: (r[v] if v < len(r) else None) for k, v in ix.items()}
        rows.append(g)
    wb.close()
    return rows


def read_cur():
    wb = openpyxl.load_workbook(CUR, data_only=True, read_only=True)
    ws = wb[wb.sheetnames[0]]
    hdr = None
    rows = []
    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}
            continue
        rows.append({k: (r[v] if v < len(r) else None) for k, v in ix.items()})
    wb.close()
    return rows


def sku_of(g):
    for k in ('货号#', '货号', '物料编码'):
        if g.get(k):
            return str(g[k]).strip()
    return ''


def name_of(g):
    return str(g.get('物料名称') or g.get('品名') or '').strip()


def keep(g, gift_ok=False):
    if '标准' not in str(g.get('单据类型') or ''):
        return False
    if not gift_ok and str(g.get('是否赠品') or '').strip() == '是':
        return False
    if '单据状态' in g and str(g.get('单据状态') or '').strip() not in ('已审核', ''):
        return False
    c = str(g.get('客户') or '')
    if any(k in c for k in BAORUI):
        return False
    return True


H25 = read_hist()
CU = read_cur()
print('█ 数据源：2025 明细 %d 行 ｜ 2026 明细 %d 行' % (len(H25), len(CU)))

# 大客户部客户集合（2026 归属优先，回退 2025 销售员）
dk_cust = set()
for g in CU:
    if keep(g, True) and str(g.get('销售员') or '').strip() in DK_SP:
        dk_cust.add(str(g.get('客户') or '').strip())
for g in H25:
    if keep(g, True) and str(g.get('销售员') or '').strip() in DK_SP:
        dk_cust.add(str(g.get('客户') or '').strip())
print('█ 大客户部客户 %d 家（2025∪2026 出现过的客户）' % len(dk_cust))

F25, F26 = defaultdict(lambda: {'amt': 0.0, 'qty': 0.0, 'n': 0}), defaultdict(lambda: {'amt': 0.0, 'qty': 0.0, 'n': 0})
INFO = {}
CUST = defaultdict(lambda: defaultdict(float))
LAST_M = {}
for g in H25:
    m = str(g.get('合同月份') or '')[:7]
    if m not in M9 or not keep(g) or str(g.get('客户') or '').strip() not in dk_cust:
        continue
    s = sku_of(g)
    if not s:
        continue
    a = sf(g.get('价税合计')) or 0.0
    F25[s]['amt'] += a
    F25[s]['qty'] += sf(g.get('销售数量')) or 0
    F25[s]['n'] += 1
    INFO[s] = name_of(g)
    CUST[s][str(g.get('客户'))] += a
for g in CU:
    d = str(g.get('合同日期') or '')[:7]
    if d not in M9b or not keep(g) or str(g.get('客户') or '').strip() not in dk_cust:
        continue
    s = sku_of(g)
    if not s:
        continue
    a = sf(g.get('价税合计')) or 0.0
    F26[s]['amt'] += a
    F26[s]['qty'] += sf(g.get('销售数量')) or 0
    F26[s]['n'] += 1
    INFO.setdefault(s, name_of(g))
    CUST[s][str(g.get('客户'))] += a
    if s not in LAST_M or d > LAST_M[s]:
        LAST_M[s] = d

# ── 丢单：2025 有 ≥3 万，2026 = 0 或 跌幅 ≥70% ──
lost, shrink = [], []
for s, v in F25.items():
    if v['amt'] < 30000:
        continue
    v26 = F26.get(s, {'amt': 0.0, 'qty': 0.0, 'n': 0})
    if v26['amt'] < 1:
        lost.append((s, INFO.get(s, ''), v['amt'], v['qty'], 0.0, sorted(CUST[s].items(), key=lambda x: -x[1])[:2]))
    elif v26['amt'] < v['amt'] * 0.3:
        shrink.append((s, INFO.get(s, ''), v['amt'], v['qty'], v26['amt'], (v26['amt'] - v['amt']) / v['amt'] * 100,
                       LAST_M.get(s, ''), sorted(CUST[s].items(), key=lambda x: -x[1])[:2]))
lost.sort(key=lambda x: -x[2])
shrink.sort(key=lambda x: x[4] - x[2])

# ── 新机会 / 上量：2026 新增 或 增幅 ≥100% ──
newsku, grow = [], []
for s, v in F26.items():
    if v['amt'] < 30000:
        continue
    v25 = F25.get(s, {'amt': 0.0, 'qty': 0.0})
    if v25['amt'] < 1:
        newsku.append((s, INFO.get(s, ''), 0.0, v['amt'], v['qty'], LAST_M.get(s, ''), sorted(CUST[s].items(), key=lambda x: -x[1])[:2]))
    elif v['amt'] >= v25['amt'] * 2:
        grow.append((s, INFO.get(s, ''), v25['amt'], v['amt'], (v['amt'] - v25['amt']) / v25['amt'] * 100, LAST_M.get(s, ''),
                     sorted(CUST[s].items(), key=lambda x: -x[1])[:2]))
newsku.sort(key=lambda x: -x[3])
grow.sort(key=lambda x: -x[3])

print()
print('█ ① 丢单（2025 1-9月 ≥3 万，2026 同期 0）共 %d 个货号' % len(lost))
print('   %-18s %-30s %12s  %s' % ('货号', '品名', '2025金额', '涉及客户'))
for s, n, a, q, _, cs in lost[:25]:
    print('   %-18s %-30s %12s %s' % (s, n[:28], format(a, ',.0f'), '、'.join(c for c, _ in cs)))
print()
print('█ ② 大幅萎缩（跌幅 ≥70%%）共 %d 个货号' % len(shrink))
print('   %-18s %-26s %11s %11s %8s %s' % ('货号', '品名', '2025', '2026', '同比', '最后成交月'))
for s, n, a25, q, a26, p, lm, cs in shrink[:20]:
    print('   %-18s %-26s %11s %11s %7.0f%% %s ｜ %s' % (s, n[:24], format(a25, ',.0f'), format(a26, ',.0f'), p, lm, '、'.join(c for c, _ in cs)))

print()
print('█ ③ 新机会（2026 新增 ≥3 万）共 %d 个货号' % len(newsku))
print('   %-18s %-30s %12s %8s %s' % ('货号', '品名', '2026金额', '首单月', '客户'))
for s, n, _, a, q, lm, cs in newsku[:25]:
    print('   %-18s %-30s %12s %8s %s' % (s, n[:28], format(a, ',.0f'), lm, '、'.join(c for c, _ in cs)))
print()
print('█ ④ 上量（2026 ≥2× 于 2025）共 %d 个货号' % len(grow))
for s, n, a25, a26, p, lm, cs in grow[:20]:
    print('   %-18s %-26s %11s → %11s  %+7.0f%% ｜ %s' % (s, n[:24], format(a25, ',.0f'), format(a26, ',.0f'), p, '、'.join(c for c, _ in cs)))

# ── ⑤ 活动/商机取证（对 丢单/新机会 的货号扫活动正文与商机名）──
tgt = {s: '丢单' for s, *_ in lost}
for s, *_ in shrink:
    tgt.setdefault(s, '萎缩')
for s, *_ in newsku:
    tgt.setdefault(s, '新增')
for s, *_ in grow:
    tgt.setdefault(s, '上量')
hits = defaultdict(list)
KEY = re.compile(r'注册|申报|转产|上量|量产|试产|临床|拿证|获批|丢单|终止|切换|替换|竞品|降本|停用|冻结')
for src in ('数据/daily_activities_mcp.json', '数据/my_activities.json', '数据/2026Q3汇报_汇报数据层.json',
            '数据/opps_by_customer.json', '数据/customer_360.json'):
    p = os.path.join(W, src)
    if not os.path.exists(p):
        continue
    try:
        raw = io.open(p, encoding='utf-8').read()
    except Exception:
        continue
    for s in tgt:
        if s and s in raw:
            for m in list(re.finditer(re.escape(s), raw))[:2]:
                seg = re.sub(r'<[^>]+>', ' ', raw[max(0, m.start() - 260):m.start() + 260])
                seg = re.sub(r'\s+', ' ', seg)
                if KEY.search(seg):
                    hits[s].append((os.path.basename(src), seg[:300]))
print()
print('█ ⑤ 证据命中（含注册/申报/上量/竞品等关键词）%d 个货号' % len(hits))
for s, hs in list(hits.items())[:14]:
    print('   ▸%s（%s）' % (s, tgt.get(s, '')))
    for f, seg in hs[:2]:
        print('      [%s] %s' % (f, seg[:240]))

json.dump({'meta': {'gen': '2026-10-16', 'caliber': '标准销售订单+非赠品；2025 1-9月 vs 2026 1-9月', 'dk_cust': len(dk_cust)},
           'lost': [{'sku': s, 'name': n, 'amt25': a, 'custs': [c for c, _ in cs]} for s, n, a, q, _, cs in lost],
           'shrink': [{'sku': s, 'name': n, 'amt25': a, 'amt26': b, 'pct': p, 'last': lm, 'custs': [c for c, _ in cs]} for s, n, a, q, b, p, lm, cs in shrink],
           'new': [{'sku': s, 'name': n, 'amt26': a, 'first': lm, 'custs': [c for c, _ in cs]} for s, n, _, a, q, lm, cs in newsku],
           'grow': [{'sku': s, 'name': n, 'amt25': a, 'amt26': b, 'pct': p, 'last': lm, 'custs': [c for c, _ in cs]} for s, n, a, b, p, lm, cs in grow],
           'evidence': {k: [{'src': f, 'seg': g} for f, g in v] for k, v in hits.items()}},
          io.open(W + '/数据/大客户部_货号丢单与新机会.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
print()
print('✅ JSON: 数据/大客户部_货号丢单与新机会.json')