#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
客户360°档案卡数据聚合器
输入（数据/目录）：
  pricing/_index.json + pricing/<客户>.json  → 基础信息/集团/负责人/主销产品
  cust_yearly.json                           → 2024/2025/2026 年度采购额
  mcp_activities.json                        → 活动记录（拜访/线上）
  opps_by_customer.json + opportunity_sandbox.json → 商机
  mcp_complaints.json                        → 客诉
  shipment_status.json                       → H2订单+物流
输出：数据/customer_360.json（单文件，前端一次加载，客户端搜索）
"""
import json, os, time, datetime, collections

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
DATA = os.path.join(ROOT, '数据')
PRICING = os.path.join(DATA, 'pricing')


def load(fn, default=None):
    p = os.path.join(DATA, fn)
    if not os.path.exists(p):
        return default
    return json.load(open(p, encoding='utf-8'))


def norm(n):
    return (n or '').strip()


import re
REGION_SFX = re.compile(r'-(浙江|广东|江苏|北京|上海|华北|华南|华中|华东|华西|山东|四川|福建|河南|河北|湖南|湖北|安徽|重庆|天津|陕西)$')


def strip_sfx(n):
    return REGION_SFX.sub('', norm(n))


def norm_name(v):
    """字段值可能为 str 或 MCP 关系型 {key,label}/{name_cn} 形态，统一取名称。"""
    if isinstance(v, dict):
        for k in ('label', 'name_cn', 'name', 'value', 'text'):
            if norm(v.get(k)):
                return norm(v.get(k))
        return ''
    if isinstance(v, (list, tuple)):
        return norm_name(v[0]) if v else ''
    return norm(v)


# ── 权威销售归属 ─────────────────────────────────────────────
# 各客户 detail 文件里的 salesperson 多为陈旧值（含已离职人员），归属统一按下表。
# 优先级（前者命中即用，后者只补空缺、绝不覆盖）：
#   0) pricing/_auth_customers_*.json ——《诊断原料&生命科学客户清单》官方归属清单（权威）
#   1) pricing/_key_customers.json    —— 重点客户表（业务已确认，但仅 444 条）
#   2) 月报库 report_store.json       —— 覆盖长尾
_AUTH_SALES = {}
_AUTH_SRC = {}     # 归属来源：auth_list / key_customers / report_store / detail
_AUTH_LINE = {}    # 业务线：da / ls（官方清单独有）
_AUTH_GROUP = {}   # 集团简称（官方清单独有，182 个集团）
# 官方清单里销售员列填的是区域占位（如"上海"）的客户 → 明确"未指派"，
# 后续数据源不得再用区域名顶替成归属。
_AUTH_UNASSIGNED = set()
# 集团级归属：重点客户表的「集团条目」（如"圣湘集团"）owner。
# 集团卡片的销售员默认由成员并集派生；集团作为整体有明确负责人时，以本表为准。
_KC_GROUP_OWNER = {}


def load_auth_sales():
    """加载权威归属表：客户名 → 销售员。同时登记去后缀别名。

    0) pricing/_auth_customers_<日期>.json —— 官方《客户清单》导出，
       按 (业务线, 销售员) 记录，含集团简称；文件名日期最新者生效。
    """
    global _AUTH_SALES, _AUTH_SRC, _AUTH_LINE, _AUTH_GROUP, _AUTH_UNASSIGNED, _KC_GROUP_OWNER
    m, src, ln, gp = {}, {}, {}, {}
    gown = {}
    unassigned = set()

    # ── 0) 官方客户清单（最高权威）─────────────────────
    try:
        import glob
        cands = sorted(glob.glob(os.path.join(PRICING, '_auth_customers_*.json')))
        if cands:
            au = json.load(open(cands[-1], encoding='utf-8'))
            n0 = 0
            for nm, v in (au.get('customers') or {}).items():
                nm2, sp = norm(nm), norm(v.get('sp'))
                if not nm2 or not sp:
                    continue
                m[nm2] = sp
                src[nm2] = 'auth_list'
                ln[nm2] = v.get('line') or ''
                if norm(v.get('group')):
                    gp[nm2] = norm(v.get('group'))
                n0 += 1
                a = strip_sfx(nm2)
                if a and a not in m:
                    m[a] = sp
                    src[a] = 'auth_list'
                    ln[a] = v.get('line') or ''
                    if norm(v.get('group')):
                        gp[a] = norm(v.get('group'))
            meta = au.get('_meta', {})
            for nm in (au.get('unassigned') or {}):
                unassigned.add(norm(nm))
                unassigned.add(strip_sfx(norm(nm)))
            print(f"  官方客户清单({os.path.basename(cands[-1])} "
                  f"数据日{meta.get('data_date','')}) 归属: {n0} 条"
                  f"{'，未指派(区域占位) ' + str(len(unassigned)//2) + ' 条' if unassigned else ''}")
    except Exception as ex:
        print("  官方客户清单归属跳过:", ex)

    # ── 1) 重点客户表（只补空缺，不覆盖官方清单）──────────
    kc = load('pricing/_key_customers.json', []) or []
    if isinstance(kc, dict):
        kc = list(kc.values())
    n1 = 0
    for e in kc:
        nm, sp = norm(e.get('name')), norm(e.get('salesperson'))
        # 集团条目（"XX集团"）= 集团级归属，与成员并集分开记录
        if nm and sp and nm.endswith('集团'):
            gown[nm] = sp
        if not nm or not sp or nm in m or nm in unassigned:
            continue
        m[nm] = sp
        src[nm] = 'key_customers'
        n1 += 1
        a = strip_sfx(nm)
        if a and a not in m:
            m[a] = sp
            src[a] = 'key_customers'
    print(f"  重点客户表补归属: {n1} 条")
    try:
        rs = load('月报库/report_store.json', {})
        cust = rs.get('customers', {}) if isinstance(rs, dict) else {}
        added = 0
        for nm, v in cust.items():
            if not isinstance(v, dict):
                continue
            sp, n2 = norm(v.get('salesperson')), norm(nm)
            if sp and n2 and n2 not in m and n2 not in unassigned:
                m[n2] = sp
                src[n2] = 'report_store'
                a = strip_sfx(n2)
                if a and a not in m:
                    m[a] = sp
                    src[a] = 'report_store'
                added += 1
        print(f"  月报库补归属: {added} 条")
    except Exception as ex:
        print("  月报库归属回填跳过:", ex)
    _AUTH_SALES = m
    _AUTH_SRC = src
    _AUTH_LINE = ln
    _AUTH_GROUP = gp
    _AUTH_UNASSIGNED = unassigned
    _KC_GROUP_OWNER = gown
    return m


def auth_sales(name):
    """取权威归属；查不到返回空串。"""
    return _AUTH_SALES.get(name) or _AUTH_SALES.get(strip_sfx(name)) or ''


def auth_line(name):
    """取业务线 da / ls（仅官方客户清单提供，其余源为空）。"""
    return _AUTH_LINE.get(name) or _AUTH_LINE.get(strip_sfx(name)) or ''


def auth_group(name):
    """取集团简称（官方客户清单的显式分组，覆盖 182 个集团）。"""
    return _AUTH_GROUP.get(name) or _AUTH_GROUP.get(strip_sfx(name)) or ''


def auth_unassigned(name):
    """官方清单里销售员列填的是区域占位（如"上海"）→ 明确未指派到人。"""
    return name in _AUTH_UNASSIGNED or strip_sfx(name) in _AUTH_UNASSIGNED


def group_owner(gname):
    """集团级负责人（重点客户表的集团条目）；无显式指派返回空串。"""
    return _KC_GROUP_OWNER.get(gname) or ''


def auth_source(name):
    """归属来源：auth_list（官方客户清单）/ key_customers（重点表）/ report_store（月报库）/ ''。"""
    return _AUTH_SRC.get(name) or _AUTH_SRC.get(strip_sfx(name)) or ''


def dedupe(seq, key_fn):
    seen = set()
    out = []
    for item in seq:
        k = key_fn(item)
        if k not in seen:
            seen.add(k)
            out.append(item)
    return out


def sum_field(items, field):
    return round(sum(float(x.get(field) or 0) for x in items))


def build_customer(entry, yearly, act_by_cust, opp_sandbox_by_cust, opps_bc,
                   comp_by_cust, orders_by_cust, h2_spend, levels):
    name = norm(entry.get('name'))
    if not name or name.lower() == 'test':
        return None
    fn = entry.get('file') or name
    lv = levels.get(name) or levels.get(strip_sfx(name)) or {}
    mcp_level, mcp_tier = lv.get('level', ''), lv.get('tier', '')
    detail = {}
    pj = os.path.join(PRICING, fn + '.json')
    if os.path.exists(pj):
        try:
            detail = json.load(open(pj, encoding='utf-8'))
        except Exception:
            detail = {}

    # 主销产品 top5（排除赠品）
    prods = [p for p in (detail.get('products') or []) if not p.get('is_gift')]
    prods.sort(key=lambda p: -(p.get('total_amt') or 0))
    top_products = [{'sku': p.get('sku'), 'name': (p.get('name') or '')[:30],
                     'amt': round(p.get('total_amt') or 0)} for p in prods[:5]]

    y = yearly.get(name) or {}
    y24, y25, y26 = y.get('2024', 0) or 0, y.get('2025', 0) or 0, y.get('2026', 0) or 0

    acts_c = sorted(act_by_cust.get(name, []), key=lambda a: a['date'], reverse=True)[:8]
    opps_live = (opps_bc.get(name) or [])[:10]
    opps_live = [{'name': norm(o.get('name')), 'status': norm(o.get('status')),
                  'creator': norm(o.get('creator')), 'url': o.get('url') or ''} for o in opps_live]
    comps = sorted(comp_by_cust.get(name, []), key=lambda c: c['date'], reverse=True)[:10]

    ords = sorted(orders_by_cust.get(name, []), key=lambda o: o.get('createDate') or '', reverse=True)[:8]
    ords_out = [{
        'id': o.get('orderId'), 'date': o.get('createDate'),
        'product': (o.get('product') or '')[:30],
        'amt': round(float(o.get('taxTotal') or o.get('amount') or 0) or 0),
        'ship': ((o.get('shipment') or {}).get('statusLabel')) or ('无物流' if not o.get('trackingNo') else '未知'),
    } for o in ords]

    risks = []
    if y26 == 0 and (y24 > 0 or y25 > 0):
        risks.append('今年无采购')
    elif y25 > 0 and 0 < y26 < y25 * 0.25:
        risks.append('采购大幅下滑')
    open_comps = [c for c in comps if not any(k in c['status'] for k in ('完成', '关闭', '解决', '结束'))]
    if open_comps:
        risks.append(f"客诉进行中×{len(open_comps)}")

    return {
        'name': name,
        'short': norm(detail.get('short_name')) or name[:12],
        'group': auth_group(name) or norm(detail.get('group')) or norm(entry.get('group_name')),
        'sales': (auth_sales(name)
                  or ('' if auth_unassigned(name) else norm(detail.get('salesperson')))),
        'sales_source': ('unassigned' if (auth_unassigned(name) and not auth_sales(name))
                         else (auth_source(name)
                               or ('detail' if norm(detail.get('salesperson')) else ''))),
        'sales_line': auth_line(name),
        'level': mcp_level,
        'tier': mcp_tier,
        'total': round(entry.get('total') or 0),
        'orders': entry.get('orders') or 0,
        'contacts': entry.get('contacts') or 0,
        'latest_date': entry.get('latest_date') or '',
        'y2024': round(y24), 'y2025': round(y25), 'y2026': round(y26),
        'h2_2026': round(h2_spend.get(name, 0)),
        'top_products': top_products,
        'activities': acts_c,
        'opps': opps_live,
        'opp_sandbox': opp_sandbox_by_cust.get(name, [])[:8],
        'complaints': comps,
        'recent_orders': ords_out,
        'risks': risks,
    }


def build_group(members):
    # 集团级聚合
    total = round(sum(c['total'] for c in members))
    y24 = round(sum(c['y2024'] for c in members))
    y25 = round(sum(c['y2025'] for c in members))
    y26 = round(sum(c['y2026'] for c in members))
    h2 = round(sum(c['h2_2026'] for c in members))
    orders = sum(c['orders'] for c in members)
    contacts = sum(c['contacts'] for c in members)

    # 主销产品聚合（按 sku+名称）
    prod_map = {}
    for c in members:
        for p in c.get('top_products') or []:
            key = (norm(p.get('sku')) or norm(p.get('name')), p.get('name'))
            prod_map.setdefault(key, {'sku': p.get('sku'), 'name': p.get('name'), 'amt': 0})
            prod_map[key]['amt'] += p.get('amt') or 0
    top_products = sorted(prod_map.values(), key=lambda p: -p['amt'])[:5]

    # 商机/客诉/订单/活动去重合并
    opps = dedupe([o for c in members for o in c.get('opps') or []], lambda o: o.get('url') or o.get('name'))
    opp_sandbox = dedupe([o for c in members for o in c.get('opp_sandbox') or []],
                         lambda o: (o.get('name'), o.get('sales'), o.get('type')))
    complaints = dedupe([cp for c in members for cp in c.get('complaints') or []], lambda cp: cp.get('id'))
    recent_orders = dedupe([o for c in members for o in c.get('recent_orders') or []], lambda o: o.get('id'))
    activities = dedupe([a for c in members for a in c.get('activities') or []],
                        lambda a: (a.get('name'), a.get('date'), a.get('mode')))
    activities.sort(key=lambda a: a['date'], reverse=True)

    latest_date = max((c['latest_date'] for c in members if c.get('latest_date')), default='')
    # 集团级显式负责人优先；无则取成员归属并集
    _gown = group_owner(norm(members[0].get('group')))
    sales_list = ([_gown] if _gown else
                  sorted({c['sales'] for c in members if c.get('sales')}))
    members_sorted = sorted(members, key=lambda c: -c['total'])

    # 集团级别风险
    risks = []
    if y26 == 0 and (y24 > 0 or y25 > 0):
        risks.append('今年无采购')
    elif y25 > 0 and 0 < y26 < y25 * 0.25:
        risks.append('采购大幅下滑')
    open_comps = [c for c in complaints if not any(k in c['status'] for k in ('完成', '关闭', '解决'))]
    if open_comps:
        risks.append(f"客诉进行中×{len(open_comps)}")

    # 集团等级：只要有一家大客户即大客户
    level = ''
    if any(c.get('level') == '大客户' for c in members):
        level = '大客户'
    elif any(c.get('level') == '重点客户' for c in members):
        level = '重点客户'

    return {
        'name': norm(members[0].get('group')),
        'members': [c['name'] for c in members_sorted],
        'member_totals': [{'name': c['name'], 'total': c['total']} for c in members_sorted],
        'count': len(members),
        'sales': '、'.join(sales_list) if sales_list else '',
        'level': level,
        'total': total,
        'orders': orders,
        'contacts': contacts,
        'latest_date': latest_date,
        'y2024': y24, 'y2025': y25, 'y2026': y26,
        'h2_2026': h2,
        'top_products': top_products,
        'activities': activities[:8],
        'opps': opps[:10],
        'opp_sandbox': opp_sandbox[:8],
        'complaints': complaints[:10],
        'recent_orders': sorted(recent_orders, key=lambda o: o.get('date') or '', reverse=True)[:8],
        'risks': risks,
    }


def main():
    t0 = time.time()
    idx = load('pricing/_index.json', [])
    yearly = load('cust_yearly.json', {})
    acts = load('daily_activities_mcp.json', {}).get('items', [])
    if not acts:  # 兼容旧数据源
        acts = load('mcp_activities.json', {}).get('items', [])
    opps_bc = load('opps_by_customer.json', {})
    sandbox = load('opportunity_sandbox.json', {}).get('opportunities', [])
    complaints = load('mcp_complaints.json', {}).get('items', [])
    ship = load('shipment_status.json', {}).get('orders', [])
    levels = load('customer_levels.json', {})
    _auth = load_auth_sales()
    print(f"  权威归属表: {len(set(_auth.values()))} 位销售 / {len(_auth)} 条名称映射")

    # ── 按客户预分组 ─────────────────────────────
    act_by_cust = collections.defaultdict(list)
    for a in acts:
        c = norm_name(a.get('field_5f20fc'))
        if not c:
            continue
        act_by_cust[c].append({
            'date': (a.get('start_time') or '')[:10],
            'name': norm(a.get('name')),
            'mode': norm_name(a.get('field_76654e'))[:12],
            'snippet': norm(a.get('field_b99055')).replace('\n', ' ')[:100],
            'owner': ((a.get('owner') or {}).get('name_cn') if isinstance(a.get('owner'), dict) else '') or '',
        })
    # 去重：同一客户同一名称+日期只留一条
    for c in act_by_cust:
        act_by_cust[c] = dedupe(act_by_cust[c], lambda a: (a['name'], a['date']))

    opp_sandbox_by_cust = collections.defaultdict(list)
    for o in sandbox:
        opp_sandbox_by_cust[norm(o.get('customer'))].append({
            'name': norm(o.get('name')), 'target': o.get('target') or 0,
            'sales': norm(o.get('sales')), 'type': norm(o.get('type')),
        })

    comp_by_cust = collections.defaultdict(list)
    for c in complaints:
        # 状态：新格式用 status/node，旧格式用 work_item_status[0].label
        st = norm(c.get('status')) or norm(c.get('node'))
        if not st:
            ws = c.get('work_item_status') or []
            if ws and isinstance(ws[0], dict):
                st = ws[0].get('label', '')
        # 日期：新格式 created，旧格式 start_time
        cdate = (norm(c.get('created')) or norm(c.get('start_time')))[:10]
        cid = c.get('id') or c.get('work_item_id')
        # 客户：新格式直接有 customer；旧格式靠 field_ac5caf 标签
        cust = norm(c.get('customer'))
        if cust:
            labels = [{'label': cust}]
        else:
            labels = c.get('field_ac5caf') or []
            if isinstance(labels, dict):
                labels = [labels]
        for item in labels:
            if isinstance(item, dict) and item.get('label'):
                comp_by_cust[norm(item['label'])].append({
                    'name': norm(c.get('name')), 'status': st,
                    'date': cdate,
                    'id': cid,
                })
    for c in comp_by_cust:
        comp_by_cust[c] = dedupe(comp_by_cust[c], lambda cp: cp['id'])

    orders_by_cust = collections.defaultdict(list)
    h2_spend = collections.defaultdict(float)
    for o in ship:
        for key in {norm(o.get('mcpCustomer')), norm(o.get('customer'))} - {''}:
            orders_by_cust[key].append(o)
        cust = norm(o.get('mcpCustomer')) or norm(o.get('customer'))
        try:
            h2_spend[cust] += float(o.get('taxTotal') or o.get('amount') or 0)
        except Exception:
            pass
    for c in orders_by_cust:
        orders_by_cust[c] = dedupe(orders_by_cust[c], lambda o: o.get('orderId'))

    # ── 逐客户聚合 ─────────────────────────────
    customers = []
    for entry in idx:
        c = build_customer(entry, yearly, act_by_cust, opp_sandbox_by_cust,
                           opps_bc, comp_by_cust, orders_by_cust, h2_spend, levels)
        if c:
            customers.append(c)

    customers.sort(key=lambda c: -c['total'])

    # ── 集团聚合 ─────────────────────────────
    group_map = collections.defaultdict(list)
    for c in customers:
        g = c.get('group') or ''
        if g:
            group_map[g].append(c)
    groups = [build_group(members) for members in group_map.values() if len(members) >= 2]
    groups.sort(key=lambda g: -g['total'])

    # ── 补充：MCP打了级别但无ERP记录的新客户（占位卡）──
    existing = {c['name'] for c in customers} | {strip_sfx(c['name']) for c in customers}
    stubs = 0
    for lname, lv in levels.items():
        if lname in existing or strip_sfx(lname) in existing:
            continue
        nm = norm(lname)
        customers.append({
            'name': nm, 'short': nm[:12], 'group': auth_group(nm), 'sales': auth_sales(nm),
            'sales_source': auth_source(nm), 'sales_line': auth_line(nm),
            'level': lv.get('level', ''), 'tier': lv.get('tier', ''),
            'total': 0, 'orders': 0, 'contacts': 0, 'latest_date': '',
            'y2024': 0, 'y2025': 0, 'y2026': 0, 'h2_2026': 0,
            'top_products': [], 'activities': [], 'opps': [], 'opp_sandbox': [],
            'complaints': [], 'recent_orders': [], 'risks': ['ERP无采购记录(新客户)'],
        })
        stubs += 1
    if stubs:
        print(f"  +{stubs} 个MCP标签客户无ERP记录，已补占位卡")

    out = {
        'updated_at': time.strftime('%Y-%m-%d %H:%M:%S'),
        'count': len(customers),
        'group_count': len(groups),
        'customers': customers,
        'groups': groups,
    }
    outp = os.path.join(DATA, 'customer_360.json')
    json.dump(out, open(outp, 'w', encoding='utf-8'), ensure_ascii=False)
    size = os.path.getsize(outp) // 1024
    print(f"✅ {len(customers)} 客户 / {len(groups)} 集团 → customer_360.json ({size}KB), 耗时 {time.time()-t0:.1f}s")
    stats = {
        '有活动': sum(1 for c in customers if c['activities']),
        '有商机': sum(1 for c in customers if c['opps'] or c['opp_sandbox']),
        '有客诉': sum(1 for c in customers if c['complaints']),
        '有H2订单': sum(1 for c in customers if c['recent_orders']),
        '有风险标记': sum(1 for c in customers if c['risks']),
    }
    print("覆盖率:", stats)


if __name__ == '__main__':
    main()