#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""提取「7货号 × 5客户」测试台账数据 → 数据/test_ledger.json
数据源：
  1) 数据/销售订单导出-销售管理.xlsx  —— 订单/测试/赠品/采购明细（含备注）
  2) 数据/daily_activities_mcp.json    —— 活动记录（客户×货号）
  3) 数据/_archive/test_feedback_full.json + test_feedback*.json —— 书面测试反馈
"""
import os, json, re
from collections import defaultdict, Counter
import pandas as pd

ROOT = os.path.expanduser('~/Desktop/Hermes输出-工作类')
OUT = os.path.join(ROOT, '数据/test_ledger.json')

SKUS = ['M2274-073702-1', 'M2274-073702', 'M2274-088901-DB', 'M2274-090803',
        'MD2104-092401-DB', 'MD2104-003006-DB-2', 'MD2104-003006-DB',
        'MD2104-003001-1', 'MD2104-003001', 'M2331']
TARGETS = {'天隆': '苏州天隆生物科技', '新产业': '深圳新产业生物', '丽珠': '珠海丽珠试剂',
           '海尔施': '宁波海尔施基因', '佰奥莱博': '西安佰奥莱博'}
# 相似主体（需用户确认）
ALIAS = {'佰奥长生': '佰奥长生（深圳）'}
SKU_RE = {s: re.compile(re.escape(s) + r'(?!\d)') for s in SKUS}
# 短码/尾码（活动描述里常用，如「838和003006冻干球」「073702」）
TAIL = {}
for _s in SKUS:
    _m = re.search(r'(\d{6})', _s)
    if _m:
        TAIL[_s] = re.compile(r'(?<!\d)' + _m.group(1) + r'(?!\d)')
TAIL['M2331'] = re.compile(r'(?<![A-Z0-9])M2331(?!\d)')

def sku_hit(text):
    if not text:
        return []
    out = []
    for s in SKUS:
        if SKU_RE[s].search(text) or (s in TAIL and TAIL[s].search(text)):
            out.append(s)
    return out

def cust_short(name):
    n = str(name)
    for k, v in TARGETS.items():
        if k in n:
            return k
    for k in ALIAS:
        if k in n:
            return k
    return ''

def parse_date(no):
    # 单据编号形如 ZHBR250831012 / BR2508310001 / FO20260407003
    m = re.search(r'(?:ZHBR|BR|FO|XS|SO)(\d{6})', str(no))
    if m:
        s = m.group(1)
        if s[:2] in ('25', '26'):
            return '20%s-%s-%s' % (s[:2], s[2:4], s[4:6])
    m = re.search(r'(20\d{2})(\d{2})(\d{2})', str(no))
    if m:
        y, mo, d = m.groups()
        if '01' <= mo <= '12' and '01' <= d <= '31':
            return '%s-%s-%s' % (y, mo, d)
    return ''

# ── 1) 订单行 ──────────────────────────────────────────────
df = pd.read_excel(os.path.join(ROOT, '数据/销售订单导出-销售管理.xlsx'))
df.columns = [str(c).strip() for c in df.columns]
orders = []
for _, r in df.iterrows():
    sku = str(r.get('货号#') or '').strip()
    if not sku:
        continue
    base = next((s for s in SKUS if sku == s), None)
    if base is None:
        continue
    cust = str(r.get('销售订单关联客户') or '').strip()
    if str(cust).startswith('「'):
        continue  # 分组汇总行
    note_i = str(r.get('备注(内部)#') or '').strip()
    note_c = str(r.get('备注(客户)#') or '').strip()
    note = ' ｜ '.join(x for x in (note_i, note_c) if x and x != 'nan')
    orders.append({
        'sku': sku,
        'base': base,
        'name': str(r.get('物料名称') or '').strip()[:60],
        'spec': str(r.get('规格型号') or '').strip()[:40],
        'customer': cust,
        'cshort': cust_short(cust),
        'date': parse_date(r.get('单据编号')),
        'no': str(r.get('单据编号') or '').strip(),
        'type': (str(r.get('订单类型#') or '').strip().replace('nan', '') or '—'),
        'qty': (lambda v: (float(v) if str(v).replace('.', '', 1).replace('-', '', 1).isdigit() else 0))(r.get('销售数量')),
        'unit': str(r.get('销售单位') or '').strip(),
        'gift': str(r.get('是否赠品') or '').strip(),
        'batch': str(r.get('出货批号#') or '').strip().replace('nan', ''),
        'amt': (lambda v: (float(v) if str(v).replace('.', '', 1).replace('-', '', 1).isdigit() else 0))(r.get('价税合计(本位币)')),
        'note': note[:300],
        'is_test': '测试' in str(r.get('订单类型#') or ''),
        'is_buy': '采购' in str(r.get('订单类型#') or ''),
    })
orders = [o for o in orders if o['date'] or o['no']]
orders.sort(key=lambda x: (x['date'] or '0000', x['sku']))
print('订单行: %d（其中目标客户 %d）' % (len(orders), sum(1 for o in orders if o['cshort'])))

# 数据质量标记
bad_dates = [o for o in orders if o['date'] and (o['date'] > '2026-09-30' or o['date'] < '2019-01-01')]
print('  日期异常行: %d %s' % (len(bad_dates), [o['no'] + '→' + str(o['date']) for o in bad_dates[:5]]))

# ── 2) 活动记录 ────────────────────────────────────────────
acts = json.load(open(os.path.join(ROOT, '数据/daily_activities_mcp.json'), encoding='utf-8'))
items = acts if isinstance(acts, list) else (acts.get('items') or [])
activities = []
for r in items:
    desc = str(r.get('field_b99055') or '')
    cl = r.get('field_5f20fc') or {}
    cname = str(cl.get('label') or '') if isinstance(cl, dict) else str(cl)
    owner = r.get('owner') or {}
    hit = sku_hit(desc) + [x for x in sku_hit(cname) if x not in sku_hit(desc)]
    cs = cust_short(cname)
    if not (hit or cs):
        continue
    activities.append({
        'date': str(r.get('start_time') or '')[:10],
        'customer': cname,
        'cshort': cs or cust_short(desc),
        'owner': str(owner.get('name_cn') or '') if isinstance(owner, dict) else '',
        'desc': re.sub(r'\s+', ' ', desc)[:400],
        'skus': hit,
        'name': str(r.get('name') or ''),
    })
activities.sort(key=lambda x: x['date'])
print('活动: %d（客户相关）｜ 其中命中目标货号 %d' % (len(activities), sum(1 for a in activities if a['skus'])))

# ── 3) 书面反馈 ────────────────────────────────────────────
FB = ['数据/_archive/test_feedback_full.json', '数据/test_feedback.json',
      '数据/test_feedback_202608.json', '数据/todo_202608.json']
feedback, seen = [], set()
for rel in FB:
    p = os.path.join(ROOT, rel)
    if not os.path.exists(p):
        continue
    try:
        d = json.load(open(p, encoding='utf-8'))
    except Exception:
        continue
    rows = d if isinstance(d, list) else (d.get('raw') or d.get('items') or d.get('records') or [])
    if not isinstance(rows, list):
        continue
    for r in rows:
        if not isinstance(r, dict):
            continue
        blob = ' '.join(str(v) for v in r.values())
        sk = next((s for s in SKUS if re.search(re.escape(s) + r'(?!\d)', blob)), None)
        if not sk:
            continue
        txt = next((str(r[k]) for k in ('description', '反馈结果(销售)', 'content', 'detail', 'feedback') if r.get(k)), '')
        cust = next((str(r[k]) for k in ('customer', '客户', '活动关联客户') if r.get(k)), '')
        date = str(next((r[k] for k in ('date', '反馈时间', '创建时间') if r.get(k)), ''))[:10]
        sales = str(next((r[k] for k in ('salesperson', '销售员', '创建者') if r.get(k)), ''))
        key = (sk, date, txt[:80])
        if key in seen:
            continue
        seen.add(key)
        feedback.append({'sku': sk, 'date': date, 'customer': cust, 'cshort': cust_short(cust),
                         'sales': sales, 'sentiment': str(r.get('sentiment') or ''), 'text': re.sub(r'\s+', ' ', txt)[:500]})
feedback.sort(key=lambda x: (x['date'] or '', x['sku']))
print('书面反馈: %d 条（目标客户 %d 条）' % (len(feedback), sum(1 for f in feedback if f['cshort'])))

# ── 4) 汇总 ────────────────────────────────────────────────
summary = {}
for s in SKUS:
    o = [x for x in orders if x['base'] == s]
    tgt = [x for x in o if x['cshort'] in TARGETS]
    summary[s] = {
        'orders': len(o), 'target_orders': len(tgt),
        'test': sum(1 for x in o if x['is_test']), 'buy': sum(1 for x in o if x['is_buy']),
        'customers': len(set(x['customer'] for x in o)),
        'target_customers': sorted(set(x['cshort'] for x in tgt)),
        'first': min([x['date'] for x in o if x['date']] or ['']), 'last': max([x['date'] for x in o if x['date']] or ['']),
        'name': next((x['name'] for x in o if x['name']), ''),
        'spec': next((x['spec'] for x in o if x['spec']), ''),
        'fb': sum(1 for f in feedback if f['sku'] == s),
        'fb_target': sum(1 for f in feedback if f['sku'] == s and f['cshort']),
        'act_hit': sum(1 for a in activities if s in a['skus']),
    }
cust_summary = {}
for k in list(TARGETS) + list(ALIAS):
    o = [x for x in orders if x['cshort'] == k]
    cust_summary[k] = {
        'full': next((x['customer'] for x in o), ''),
        'orders': len(o), 'skus': sorted(set(x['base'] for x in o)),
        'test': sum(1 for x in o if x['is_test']), 'buy': sum(1 for x in o if x['is_buy']),
        'first': min([x['date'] for x in o if x['date']] or ['']),
        'last': max([x['date'] for x in o if x['date']] or ['']),
        'fb': sum(1 for f in feedback if f['cshort'] == k),
        'acts': sum(1 for a in activities if a['cshort'] == k),
        'act_hit': sum(1 for a in activities if a['cshort'] == k and a['skus']),
    }
pack = {
    'meta': {
        'title': '测试台账 · 7 货号 × 5 客户',
        'skus': SKUS, 'targets': TARGETS, 'alias': ALIAS,
        'order_rows': len(orders), 'activity_rows': len(activities), 'feedback_rows': len(feedback),
        'span': [min([o['date'] for o in orders if o['date']] or ['']), max([o['date'] for o in orders if o['date']] or [''])],
        'note': '订单/测试/赠品/采购数据源=ERP销售订单导出-销售管理；书面反馈数据源=飞书活动反馈（最早2026-05-27）',
    },
    'summary': summary, 'cust_summary': cust_summary,
    'orders': orders, 'activities': activities, 'feedback': feedback,
}
json.dump(pack, open(OUT, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
print('\n✅ 写出 %s  %.1f KB' % (OUT, os.path.getsize(OUT) / 1024))
print('\n=== 货号汇总 ===')
for s in SKUS:
    x = summary[s]
    print('  %-20s 订单%3d(目标客户%2d) 测试%3d 采购%2d 客户%3d 反馈%2d(目标客户%d) 活动%2d  %s~%s'
          % (s, x['orders'], x['target_orders'], x['test'], x['buy'], x['customers'], x['fb'], x['fb_target'], x['act_hit'], x['first'], x['last']))
print('\n=== 客户汇总 ===')
for k, v in cust_summary.items():
    print('  %-8s 订单%3d 测试%3d 采购%2d 货号%d 反馈%2d 活动%3d(命中货号%d)  %s~%s'
          % (k, v['orders'], v['test'], v['buy'], len(v['skus']), v['fb'], v['acts'], v['act_hit'], v['first'], v['last']))