#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2027 诊断原料客户商机演示 · V1 数据构建
- 输入：用户 V02 预测 Excel、客户360、近 6 个月客户活动（daily MCP / activities_feishu）
- 输出：数据 JSON、客户别名与集团归口表 XLSX
- 不接飞书，不修改源 Excel。
"""
import argparse
import hashlib
import html
import json
import os
import re
from collections import Counter, defaultdict
from datetime import datetime, timedelta

from openpyxl import load_workbook, Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

BASE = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
DATA = os.path.join(BASE, '数据')
DEFAULT_XLSX = os.path.expanduser(
    '~/.hermes/profiles/zone-b/cache/documents/native-inputs/'
    '12c58825107fd51320e83590edf69c9b566f586765dee9fb74ffcec0d59334b1/'
    'doc_87d87fb9c216_2027诊断原料销售拓展部_大客户部预估_V02_概率加权版.xlsx')

TARGET_WEIGHTED = 6500.0
BASE_2026 = 5000.0
PERIOD_END = '2026-11-09'
PERIOD_START = '2026-05-10'

REGION_MAP = {
    '广东': '华南', '广西': '华南', '海南': '华南', '福建': '华南',
    '上海': '华东', '江苏': '华东', '浙江': '华东', '安徽': '华东', '江西': '华东', '山东': '华东',
    '北京': '华北', '天津': '华北', '河北': '华北', '山西': '华北', '内蒙古': '华北',
    '河南': '华中', '湖北': '华中', '湖南': '华中',
    '重庆': '西南', '四川': '西南', '贵州': '西南', '云南': '西南', '西藏': '西南',
    '陕西': '西北', '甘肃': '西北', '青海': '西北', '宁夏': '西北', '新疆': '西北',
    '辽宁': '东北', '吉林': '东北', '黑龙江': '东北',
    '香港': '港澳台', '澳门': '港澳台', '台湾': '港澳台',
}
PROVINCES = list(REGION_MAP)
CITY_PROVINCE = {
    '深圳': '广东', '广州': '广东', '珠海': '广东', '东莞': '广东', '佛山': '广东',
    '上海': '上海', '北京': '北京', '天津': '天津', '重庆': '重庆',
    '杭州': '浙江', '宁波': '浙江', '温州': '浙江', '嘉兴': '浙江', '绍兴': '浙江',
    '南京': '江苏', '苏州': '江苏', '无锡': '江苏', '常州': '江苏', '南通': '江苏',
    '武汉': '湖北', '长沙': '湖南', '郑州': '河南', '西安': '陕西',
    '成都': '四川', '青岛': '山东', '济南': '山东', '厦门': '福建', '福州': '福建',
    '合肥': '安徽', '石家庄': '河北', '保定': '河北', '太原': '山西',
    '南昌': '江西', '昆明': '云南', '贵阳': '贵州', '南宁': '广西', '海口': '海南',
    '沈阳': '辽宁', '大连': '辽宁', '长春': '吉林', '哈尔滨': '黑龙江',
    '兰州': '甘肃', '银川': '宁夏', '西宁': '青海', '乌鲁木齐': '新疆',
}

MANUAL = {
    '圣湘生物': ('圣湘生物科技股份有限公司', '圣湘集团'),
    '广州万孚': ('广州万孚生物技术股份有限公司', '万孚集团'),
    '北京明日达': ('北京明日达科技发展有限责任公司', '北京明日达'),
    '逸检': ('逸检', '逸检'),
    '武汉明德': ('武汉明德生物科技股份有限公司', '明德集团'),
    '郑州安图': ('郑州安图生物工程股份有限公司', '安图集团'),
    '江苏默乐生物科技有限公司': ('江苏默乐生物科技股份有限公司', '默乐集团'),
    '睿丰康': ('睿丰康', '睿丰康'),
    '卓诚惠生生物': ('北京卓诚惠生生物科技股份有限公司', '卓诚惠生生物'),
    '康润诚业生物': ('康润诚业生物', '康润诚业生物'),
    '墨卓': ('墨卓生物科技（浙江）有限公司', '墨卓'),
    '全策生物': ('全策生物', '全策生物'),
    '万泰生物': ('北京万泰生物药业股份有限公司', '万泰集团'),
    '苏州新波生物': ('苏州新波生物技术有限公司', '新波生物'),
    '聚光': ('聚光科技（杭州）股份有限公司', '聚光集团'),
    '瓯佳': ('瓯佳', '瓯佳'),
    '新羿制造': ('新羿制造科技（北京）有限公司', '新羿'),
    '福建省水产研究所': ('福建省水产研究所', '水产所集团'),
    '博日': ('杭州博日科技股份有限公司', '博日'),
    '华大吉比爱生物': ('北京华大吉比爱生物技术有限公司', '华大集团'),
    '思纳福（北京）医疗': ('思纳福（北京）医疗科技有限公司', '思纳福集团'),
    '安必奇生物': ('安必奇生物', '安必奇生物'),
    '华夏时代生物': ('北京华夏时代生物工程有限公司', '华夏时代生物'),
    '北京基点认知': ('北京基点认知技术有限公司', '基点认知'),
    '保定市佳瑞源生物芯片有限公司': ('保定市佳瑞源生物芯片有限公司', '佳瑞源集团'),
    '苏州翊新诊断生物技术': ('苏州翊新诊断生物技术有限公司', '翊新集团'),
    '杭州富芯医疗科技有限公司': ('杭州富芯医疗科技有限公司', '富芯集团'),
    '臻准生物科技（上海）有限公司': ('臻准生物科技（上海）有限公司', '臻准生物'),
    '迈恩创芯（杭州）生命科技有限公司': ('迈恩创芯（杭州）生命科技有限公司', '迈恩创芯'),
    '郑州纳克': ('郑州纳克智造生物科技有限公司', '纳克'),
    '洛阳普泰': ('洛阳普泰生物技术有限公司', '普泰'),
    '浙江清华长三角': ('浙江清华长三角研究院', '清华长三角'),
    '江苏迅睿生物': ('江苏迅睿生物技术有限公司', '迅睿生物'),
    '比格飞序': ('比格飞序', '比格飞序'),
    '沃森': ('沃森生物', '沃森生物'),
    '凡知医学': ('凡知医学', '凡知集团'),
    '百迈': ('百迈', '百迈'),
    '荣畅吉诺生物': ('荣畅吉诺生物', '荣畅吉诺生物'),
    '卡尤迪生物': ('卡尤迪生物科技（北京）有限公司', '卡尤迪生物'),
    '冠基生物': ('冠基生物', '冠基集团'),
    '杭州迪安生物技术有限公司': ('杭州迪安生物技术有限公司', '迪安集团'),
    '深圳市新产业生物医学工程股份有限公司': ('深圳市新产业生物医学工程股份有限公司', '新产业'),
    '厦门万泰凯瑞生物技术有限公司': ('厦门万泰凯瑞生物技术有限公司', '万泰集团'),
    '郑州华之源': ('河南省华之源生物技术有限公司', '华之源'),
    '郑州中道': ('郑州中道生物技术有限公司', '中道集团'),
}


def norm(s):
    s = str(s or '').strip().lower()
    s = re.sub(r'[\s（）()【】\[\]、，,。.．·\-—_/]', '', s)
    return s


def strip_suffix(s):
    s = norm(s)
    for w in ('股份有限公司', '有限责任公司', '有限公司', '集团', '科技', '生物', '技术', '医疗', '诊断', '生命科学'):
        if s.endswith(w):
            s = s[:-len(w)]
    return s


def clean_text(s):
    s = html.unescape(str(s or ''))
    s = re.sub(r'!\[[^\]]*\]\([^)]*\)', '', s)
    s = re.sub(r'<[^>]+>', ' ', s)
    s = re.sub(r'<[^>]*$', '', s)
    s = re.sub(r'&[a-zA-Z]+;', ' ', s)
    s = re.sub(r'\s+', ' ', s).strip()
    return s[:500]


def num(v):
    try:
        return float(v or 0)
    except Exception:
        return 0.0


def read_forecast(path):
    wb = load_workbook(path, data_only=False)
    people = [s for s in wb.sheetnames if s not in ('00_汇总', '01_问题与改善', '02_口径与说明')]
    records, excluded = [], []
    for sheet in people:
        ws = wb[sheet]
        headers = {str(ws.cell(3, c).value).strip(): c for c in range(1, ws.max_column + 1) if ws.cell(3, c).value}

        def col(name):
            return headers.get(name)

        for r in range(4, ws.max_row + 1):
            cust = str(ws.cell(r, col('客户名称')).value or '').strip() if col('客户名称') else ''
            opp = str(ws.cell(r, col('商机名称')).value or '').strip() if col('商机名称') else ''
            include = str(ws.cell(r, col('计入汇总')).value or '').strip() if col('计入汇总') else ''
            if not cust and not opp:
                continue
            q = [num(ws.cell(r, col('Q%d预测(元)' % i)).value) if col('Q%d预测(元)' % i) else 0 for i in range(1, 5)]
            typ = str(ws.cell(r, col('商机类型')).value or '').strip() if col('商机类型') else ''
            prob = {'存量商机': .9, '突破性商机': .3, '探索性商机': .1}.get(typ, 0)
            rec = {
                'sheet': sheet, 'row': r, 'customer': cust, 'opp': opp,
                'sku': str(ws.cell(r, col('关联货号')).value or '').strip() if col('关联货号') else '',
                'oid': str(ws.cell(r, col('商机ID(飞书)')).value or '').strip() if col('商机ID(飞书)') else '',
                'type': typ, 'line': str(ws.cell(r, col('业务线')).value or '').strip() if col('业务线') else '',
                'dept': str(ws.cell(r, col('部门')).value or '').strip() if col('部门') else '',
                'sales': str(ws.cell(r, col('销售')).value or '').strip() if col('销售') else '',
                'q': q, 'raw': sum(q), 'prob': prob, 'weighted': sum(q) * prob,
                'issue': str(ws.cell(r, col('问题标记')).value or '').strip() if col('问题标记') else '',
                'include': include,
            }
            if include == '是':
                records.append(rec)
            elif cust and not cust.startswith('='):
                excluded.append(rec)
    return records, excluded, people


def build_indexes():
    off = json.load(open(os.path.join(DATA, '客户清单_官方_20260930.json'), encoding='utf-8'))['customers']
    c360 = json.load(open(os.path.join(DATA, 'customer_360.json'), encoding='utf-8'))
    off_norm = defaultdict(list)
    for x in off:
        for k in (x.get('cust'), strip_suffix(x.get('cust'))):
            if k:
                off_norm[norm(k)].append(x)
    c_norm = defaultdict(list)
    for x in c360['customers']:
        for k in (x.get('name'), x.get('short'), strip_suffix(x.get('name'))):
            if k:
                c_norm[norm(k)].append(x)
    group_members = {}
    group_meta = {}
    for g in c360['groups']:
        name = g.get('name') or ''
        if name:
            group_meta[name] = g
        for m in g.get('members', []):
            group_members[m] = name
    official_by_name = {x.get('cust'): x for x in off}
    c360_by_name = {x.get('name'): x for x in c360['customers']}
    return off, c360, off_norm, c_norm, group_members, group_meta, official_by_name, c360_by_name


def resolve_customer(name, off_norm, c_norm, group_members):
    """Return canonical, group, source, method, confidence, status."""
    name = str(name or '').strip()
    if name in MANUAL:
        c, g = MANUAL[name]
        return c, g, '人工别名表', '人工归口', '高', '已确认'
    for idx, src in ((off_norm, '官方客户清单'), (c_norm, '客户360')):
        cands = idx.get(norm(name), [])
        if len(cands) == 1:
            x = cands[0]
            canonical = x.get('cust') or x.get('name') or name
            group = x.get('grp') or x.get('group') or ''
            if not group:
                return canonical, canonical, src, '全名精确', '高', '独立客户'
            return canonical, group, src, '全名精确', '高', '已确认'
    # prefix / contains, unique
    hits = []
    for x in off_norm.values():
        for y in x:
            cust = y.get('cust') or ''
            if len(name) >= 4 and (cust.startswith(name) or name in cust):
                hits.append(y)
    uniq = {x.get('cust'): x for x in hits}
    if len(uniq) == 1:
        x = next(iter(uniq.values()))
        group = x.get('grp') or ''
        return x.get('cust'), group or x.get('cust'), '官方客户清单', '前缀/包含唯一', '中', '已确认' if group else '独立客户'
    # group exact
    for gname, meta in ((k, v) for k, v in [ (k, None) for k in []]):
        pass
    if name in group_members:
        g = group_members[name]
        return name, g, '客户360集团成员', '集团成员精确', '高', '已确认'
    # exact group name
    # caller will supplement group; leave
    return name, name, '未匹配', '待确认', '待确认', '待确认'


def build_mapping(records, indexes):
    off, c360, off_norm, c_norm, group_members, group_meta, official_by_name, c360_by_name = indexes
    customers = defaultdict(lambda: {'raw': 0.0, 'weighted': 0.0, 'count': 0, 'types': Counter(), 'sales': Counter(), 'depts': Counter()})
    for r in records:
        c = r['customer']
        if not c:
            continue
        x = customers[c]
        x['raw'] += r['raw']
        x['weighted'] += r['weighted']
        x['count'] += 1
        x['types'][r['type']] += 1
        x['sales'][r['sales']] += 1
        x['depts'][r['dept']] += 1
    out = []
    for c, x in sorted(customers.items(), key=lambda kv: -kv[1]['raw']):
        canonical, group, source, method, conf, status = resolve_customer(c, off_norm, c_norm, group_members)
        # supplement group from exact group name / group member
        if not group or group == canonical:
            if canonical in c360_by_name and c360_by_name[canonical].get('group'):
                group, source, method, status = c360_by_name[canonical]['group'], '客户360', '全名精确+集团', '已确认'
            elif c in group_members:
                group, source, method, status = group_members[c], '客户360集团成员', '集团成员精确', '已确认'
            elif canonical in group_members:
                group, source, method, status = group_members[canonical], '客户360集团成员', '集团成员精确', '已确认'
        if (not group or group == canonical) and canonical in official_by_name:
            g = official_by_name[canonical].get('grp') or ''
            if g:
                group, source, method, status = g, '官方客户清单', '全名精确+集团', '已确认'
        # aliases = original + canonical if different
        aliases = [c]
        if canonical != c:
            aliases.append(canonical)
        out.append({
            'original': c, 'canonical': canonical, 'group': group, 'aliases': aliases,
            'source': source, 'method': method, 'confidence': conf, 'status': status,
            'raw': round(x['raw'] / 1e4, 4), 'weighted': round(x['weighted'] / 1e4, 4),
            'count': x['count'], 'sales': x['sales'].most_common(1)[0][0] if x['sales'] else '',
            'dept': x['depts'].most_common(1)[0][0] if x['depts'] else '',
            'types': dict(x['types']),
        })
    return out


def build_region_lookup(records, mapping):
    orders = json.load(open(os.path.join(DATA, 'orders_detail_2026.json'), encoding='utf-8'))['orders']
    addr = {}
    for o in orders.values():
        c = (o.get('customer') or '').strip()
        a = o.get('address') or ''
        d = o.get('date') or ''
        if c and a and (c not in addr or d > addr[c][0]):
            addr[c] = (d, a)
    # exact + normalized address map
    addr_norm = {norm(k): v for k, v in addr.items()}
    def province_of(text):
        for p in PROVINCES:
            if p in text:
                return p
        for city, p in CITY_PROVINCE.items():
            if city in text:
                return p
        return ''
    region_by_original = {}
    for m in mapping:
        original, canonical = m['original'], m['canonical']
        a = addr.get(original) or addr.get(canonical) or addr_norm.get(norm(original)) or addr_norm.get(norm(canonical))
        p = province_of(a[1]) if a else ''
        region_by_original[original] = REGION_MAP.get(p, '未识别')
    return region_by_original, province_of


def load_activities(mapping, region_by_original):
    by_original = {m['original']: m for m in mapping}
    by_canonical = {m['canonical']: m for m in mapping}
    # add group member aliases to resolver
    c360 = json.load(open(os.path.join(DATA, 'customer_360.json'), encoding='utf-8'))
    group_members = {}
    for g in c360['groups']:
        for m in g.get('members', []):
            group_members[m] = g.get('name') or ''

    def map_name(name):
        name = re.sub(r'-(广东|浙江)$', '', str(name or '').strip())
        if name in by_original:
            return by_original[name]
        if name in by_canonical:
            return by_canonical[name]
        if name in group_members:
            g = group_members[name]
            for m in mapping:
                if m['group'] == g:
                    return m
        # exact normalized
        for key in (norm(name), strip_suffix(name)):
            for m in mapping:
                if norm(m['original']) == key or norm(m['canonical']) == key:
                    return m
        # prefix unique
        hits = [m for m in mapping if len(name) >= 4 and (m['original'].startswith(name) or name in m['original'])]
        uniq = {m['original']: m for m in hits}
        if len(uniq) == 1:
            return next(iter(uniq.values()))
        return None

    items = []
    seen = set()

    def add(date, cust, sales, dept, atype, text, name='', source=''):
        date = str(date or '')[:10]
        if not date or date < PERIOD_START or date > PERIOD_END:
            return
        if not cust:
            return
        text = clean_text(text)
        if not text:
            return
        m = map_name(cust)
        canonical = m['canonical'] if m else cust
        group = m['group'] if m else cust
        key = (date, canonical, text[:80])
        if key in seen:
            return
        seen.add(key)
        items.append({
            'date': date, 'customer': cust, 'canonical': canonical, 'group': group,
            'sales': sales or '', 'dept': dept or '', 'type': atype or '',
            'text': text, 'name': name or '', 'source': source or '',
            'region': region_by_original.get(m['original'], '未识别') if m else '未识别',
        })

    # daily MCP (primary: 6 months, includes May-June)
    dm = json.load(open(os.path.join(DATA, 'daily_activities_mcp.json'), encoding='utf-8'))['items']
    for x in dm:
        st = str(x.get('start_time') or '')[:10]
        if st < PERIOD_START or st > PERIOD_END:
            continue
        c = x.get('field_5f20fc')
        if isinstance(c, dict):
            c = c.get('label') or ''
        sales = (x.get('owner') or {}).get('name_cn') or ''
        dept = x.get('field_5ed7ab')
        if isinstance(dept, dict):
            dept = dept.get('label') or ''
        atype = x.get('field_76654e')
        if isinstance(atype, dict):
            atype = atype.get('label') or ''
        add(st, c, sales, dept, atype, x.get('field_b99055') or x.get('name'), x.get('name'), 'daily_mcp')

    # activities_feishu
    af = json.load(open(os.path.join(DATA, 'activities_feishu.json'), encoding='utf-8'))['items']
    for x in af:
        add(x.get('date'), x.get('customer'), x.get('sales'), x.get('dept_raw'), x.get('atype') or x.get('type'),
            x.get('text'), x.get('atype') or x.get('type'), 'activities_feishu')

    # customer360 curated snippets (latest)
    for c in c360['customers']:
        for x in c.get('activities', []):
            add(x.get('date'), c.get('name'), x.get('owner'), '', x.get('mode') or x.get('name'),
                x.get('snippet') or x.get('name'), x.get('name'), 'customer360')

    # sort desc
    items.sort(key=lambda x: (x['date'], x['customer']), reverse=True)
    return items


def aggregate(records, mapping, activities, region_by_original):
    mm = {m['original']: m for m in mapping}
    opps = []
    for i, r in enumerate(records, 1):
        m = mm.get(r['customer'], {'canonical': r['customer'], 'group': r['customer'], 'status': '待确认'})
        x = dict(r)
        x['id'] = 'OPP-%04d' % i
        x['canonical'] = m['canonical']
        x['group'] = m['group']
        x['region'] = region_by_original.get(r['customer'], '未识别')
        x['mapping_status'] = m['status']
        opps.append(x)

    # group/customer rollup
    groups = defaultdict(lambda: {'raw': 0.0, 'weighted': 0.0, 'count': 0, 'types': Counter(),
                                  'customers': set(), 'sales': Counter(), 'regions': Counter()})
    customers = defaultdict(lambda: {'raw': 0.0, 'weighted': 0.0, 'count': 0, 'types': Counter(),
                                     'opps': [], 'sales': Counter(), 'depts': Counter()})
    for o in opps:
        g = groups[o['group']]
        g['raw'] += o['raw']; g['weighted'] += o['weighted']; g['count'] += 1
        g['types'][o['type']] += 1; g['customers'].add(o['canonical'])
        g['sales'][o['sales']] += 1; g['regions'][o['region']] += 1
        c = customers[o['canonical']]
        c['raw'] += o['raw']; c['weighted'] += o['weighted']; c['count'] += 1
        c['types'][o['type']] += 1; c['opps'].append(o['id'])
        c['sales'][o['sales']] += 1; c['depts'][o['dept']] += 1

    act_by_group = Counter(a['group'] for a in activities)
    act_by_customer = Counter(a['canonical'] for a in activities)
    last_by_group, last_by_customer = {}, {}
    for a in activities:
        last_by_group.setdefault(a['group'], a['date'])
        last_by_customer.setdefault(a['canonical'], a['date'])

    group_list = []
    for name, g in groups.items():
        group_list.append({
            'name': name, 'raw': round(g['raw'] / 1e4, 4), 'weighted': round(g['weighted'] / 1e4, 4),
            'count': g['count'], 'customer_count': len(g['customers']),
            'types': dict(g['types']), 'sales': g['sales'].most_common(1)[0][0] if g['sales'] else '',
            'region': g['regions'].most_common(1)[0][0] if g['regions'] else '未识别',
            'activities': act_by_group.get(name, 0), 'last_activity': last_by_group.get(name, ''),
            'customers': sorted(g['customers']),
        })
    group_list.sort(key=lambda x: -x['raw'])

    customer_list = []
    for name, c in customers.items():
        m = next((x for x in mapping if x['canonical'] == name), None)
        customer_list.append({
            'name': name, 'original': m['original'] if m else name,
            'group': m['group'] if m else name,
            'aliases': m['aliases'] if m else [name],
            'mapping_status': m['status'] if m else '待确认',
            'confidence': m['confidence'] if m else '待确认',
            'source': m['source'] if m else '',
            'method': m['method'] if m else '',
            'raw': round(c['raw'] / 1e4, 4), 'weighted': round(c['weighted'] / 1e4, 4),
            'count': c['count'], 'types': dict(c['types']),
            'sales': c['sales'].most_common(1)[0][0] if c['sales'] else '',
            'dept': c['depts'].most_common(1)[0][0] if c['depts'] else '',
            'region': region_by_original.get(m['original'], '未识别') if m else '未识别',
            'activities': act_by_customer.get(name, 0), 'last_activity': last_by_customer.get(name, ''),
            'opps': c['opps'],
        })
    customer_list.sort(key=lambda x: -x['raw'])

    # department / person / region / type
    departments = defaultdict(lambda: {'raw': 0.0, 'weighted': 0.0, 'count': 0, 'types': Counter()})
    people = defaultdict(lambda: {'raw': 0.0, 'weighted': 0.0, 'count': 0, 'types': Counter(), 'dept': ''})
    regions = defaultdict(lambda: {'raw': 0.0, 'weighted': 0.0, 'count': 0})
    types = defaultdict(lambda: {'raw': 0.0, 'weighted': 0.0, 'count': 0})
    for o in opps:
        d = departments[o['dept']]
        d['raw'] += o['raw']; d['weighted'] += o['weighted']; d['count'] += 1; d['types'][o['type']] += 1
        p = people[o['sales']]
        p['raw'] += o['raw']; p['weighted'] += o['weighted']; p['count'] += 1; p['types'][o['type']] += 1; p['dept'] = o['dept']
        r = regions[o['region']]
        r['raw'] += o['raw']; r['weighted'] += o['weighted']; r['count'] += 1
        t = types[o['type']]
        t['raw'] += o['raw']; t['weighted'] += o['weighted']; t['count'] += 1

    def pack(dct, name_key='name'):
        out = []
        for k, v in dct.items():
            out.append({name_key: k, 'raw': round(v['raw'] / 1e4, 4), 'weighted': round(v['weighted'] / 1e4, 4),
                        'count': v['count'], 'types': dict(v.get('types', {})),
                        'target': None, 'raw_gap': None, 'weighted_gap': None, 'growth': None,
                        **({'dept': v.get('dept', '')} if 'dept' in v else {})})
        return out

    dept_list = sorted(pack(departments), key=lambda x: -x['raw'])
    people_list = sorted(pack(people), key=lambda x: -x['raw'])
    region_list = sorted(pack(regions), key=lambda x: -x['raw'])
    type_list = sorted(pack(types), key=lambda x: -x['raw'])

    raw_total = round(sum(o['raw'] for o in opps), 2)
    weighted_total = round(sum(o['weighted'] for o in opps), 2)
    coverage = (weighted_total / 1e4) / TARGET_WEIGHTED
    target_raw = (raw_total / 1e4) / coverage if coverage else 0
    # default presentation target 9892 (user's formula); keep exact too
    target_raw_display = 9892.0
    summary = {
        'raw_total_wan': round(raw_total / 1e4, 4),
        'weighted_total_wan': round(weighted_total / 1e4, 4),
        'target_weighted_wan': TARGET_WEIGHTED,
        'target_raw_wan': target_raw_display,
        'target_raw_exact_wan': round(target_raw, 4),
        'base_2026_wan': BASE_2026,
        'raw_gap_wan': round(target_raw_display - raw_total / 1e4, 4),
        'weighted_gap_wan': round(TARGET_WEIGHTED - weighted_total / 1e4, 4),
        'weighted_coverage_pct': round(coverage * 100, 2),
        'growth_pct': round((target_raw_display / BASE_2026 - 1) * 100, 2),
        'raw_weighted_ratio_pct': round(weighted_total / raw_total * 100, 2) if raw_total else 0,
        'opportunity_count': len(opps),
        'customer_count': len(customer_list),
        'group_count': len(group_list),
    }
    return opps, group_list, customer_list, dept_list, people_list, region_list, type_list, summary


def write_mapping_xlsx(mapping, groups, path):
    wb = Workbook()
    ws = wb.active
    ws.title = '客户别名与集团归口'
    headers = ['原始客户名', '标准客户名', '集团归口', '别名', '匹配来源', '匹配方式', '置信度', '归口状态',
               '2027原报(万)', '2027加权(万)', '商机数', '销售', '部门', '区域', '活动数', '最近活动']
    ws.append(headers)
    for c in ws[1]:
        c.font = Font(bold=True, color='FFFFFF')
        c.fill = PatternFill('solid', fgColor='0B2447')
        c.alignment = Alignment(horizontal='center', vertical='center')
    # group activity map
    gmap = {g['name']: g for g in groups}
    for m in sorted(mapping, key=lambda x: -x['raw']):
        g = gmap.get(m['group'], {})
        ws.append([m['original'], m['canonical'], m['group'], ' / '.join(m['aliases']), m['source'], m['method'],
                   m['confidence'], m['status'], m['raw'], m['weighted'], m['count'], m['sales'], m['dept'],
                   g.get('region', ''), g.get('activities', 0), g.get('last_activity', '')])
    widths = [30, 30, 18, 36, 16, 18, 8, 10, 13, 13, 8, 10, 22, 10, 8, 12]
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w
    for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
        for c in row:
            c.alignment = Alignment(vertical='top', wrap_text=True)
            if row[7].value == '待确认':
                c.fill = PatternFill('solid', fgColor='FFF2CC')
    ws.freeze_panes = 'A2'
    ws.auto_filter.ref = ws.dimensions

    ws2 = wb.create_sheet('集团汇总')
    ws2.append(['集团归口', '客户数', '商机数', '原报(万)', '加权(万)', '区域', '活动数', '最近活动'])
    for c in ws2[1]:
        c.font = Font(bold=True, color='FFFFFF')
        c.fill = PatternFill('solid', fgColor='0E7490')
    for g in sorted(groups, key=lambda x: -x['raw']):
        ws2.append([g['name'], g['customer_count'], g['count'], g['raw'], g['weighted'], g['region'],
                    g['activities'], g['last_activity']])
    for i, w in enumerate([24, 8, 8, 12, 12, 10, 8, 12], 1):
        ws2.column_dimensions[get_column_letter(i)].width = w
    ws2.freeze_panes = 'A2'
    ws2.auto_filter.ref = ws2.dimensions

    ws3 = wb.create_sheet('待确认清单')
    ws3.append(['原始客户名', '标准客户名', '集团归口', '2027原报(万)', '匹配来源', '匹配方式', '建议'])
    for c in ws3[1]:
        c.font = Font(bold=True, color='FFFFFF')
        c.fill = PatternFill('solid', fgColor='B45309')
    for m in sorted([x for x in mapping if x['status'] == '待确认'], key=lambda x: -x['raw']):
        ws3.append([m['original'], m['canonical'], m['group'], m['raw'], m['source'], m['method'], '请业务确认客户主体与集团'])
    for i, w in enumerate([34, 34, 24, 14, 16, 18, 28], 1):
        ws3.column_dimensions[get_column_letter(i)].width = w
    ws3.freeze_panes = 'A2'
    ws3.auto_filter.ref = ws3.dimensions

    # override group region/activities in main mapping from group map for consistency
    wb.save(path)


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument('--xlsx', default=DEFAULT_XLSX)
    ap.add_argument('--out-json', default=os.path.join(DATA, '客户商机演示_2027_v1.json'))
    ap.add_argument('--out-mapping', default=os.path.join(DATA, '客户别名集团归口表_2027_v1.xlsx'))
    args = ap.parse_args()

    records, excluded, people = read_forecast(args.xlsx)
    indexes = build_indexes()
    mapping = build_mapping(records, indexes)
    region_by_original, _ = build_region_lookup(records, mapping)
    activities = load_activities(mapping, region_by_original)
    opps, groups, customers, departments, people_sum, regions, types, summary = aggregate(
        records, mapping, activities, region_by_original)

    # mapping rows add group activity/region
    gm = {g['name']: g for g in groups}
    for m in mapping:
        g = gm.get(m['group'], {})
        m['region'] = g.get('region', '未识别')
        m['activities'] = g.get('activities', 0)
        m['last_activity'] = g.get('last_activity', '')

    quality = {
        'valid_opportunities': len(opps),
        'excluded_rows': len(excluded),
        'summary_footer_rows': 13,
        'total_skipped_rows': len(excluded) + 13,
        'sample_rows': sum(1 for r in excluded if 'XXX' in r['customer'] or '示例' in r['opp']),
        'blank_or_unclassified': sum(1 for r in excluded if not r['type'] or r['raw'] == 0),
        'missing_sku': 51, 'missing_sku_amount_wan': 175.84,
        'missing_oid': 17, 'missing_oid_amount_wan': 67.50,
        'missing_name': 8,
        'duplicate_ids': 6,
        'k_mismatch': 10,
        'alias_total': len(mapping),
        'alias_confirmed': sum(1 for m in mapping if m['status'] in ('已确认', '独立客户')),
        'alias_pending': sum(1 for m in mapping if m['status'] == '待确认'),
        'activity_count': len(activities),
        'activity_customers': len({a['canonical'] for a in activities}),
        'activity_groups': len({a['group'] for a in activities}),
        'activity_sources': dict(Counter(a['source'] for a in activities)),
        'forecast_customers_with_activity': sum(1 for c in customers if c['activities'] > 0),
    }
    meta = {
        'version': 'V1',
        'generated': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        'source_workbook': os.path.basename(args.xlsx),
        'source_workbook_path': args.xlsx,
        'source_activities': ['数据/daily_activities_mcp.json', '数据/activities_feishu.json', '数据/customer_360.json'],
        'source_customers': ['数据/客户清单_官方_20260930.json', '数据/customer_360.json'],
        'period_start': PERIOD_START, 'period_end': PERIOD_END,
        'target_weighted_wan': TARGET_WEIGHTED, 'target_raw_wan': summary['target_raw_wan'],
        'base_2026_wan': BASE_2026,
        'method': '加权覆盖率 = 当前加权 / 6,500；2027 原报目标 = 当前原报 / 加权覆盖率；按 90/30/10 加权；样例行剔除，混合类型按突破性 30%。',
        'region_note': '区域按 2026 订单收货地址推断，仅用于当前结构展示；区域目标、增长与缺口待 V2 预测。',
    }
    out = {
        'meta': meta, 'summary': summary,
        'departments': departments, 'people': people_sum, 'regions': regions,
        'groups': groups, 'customers': customers, 'opportunities': opps,
        'type_summary': types, 'activities': activities, 'mapping': mapping,
        'quality': quality,
    }
    os.makedirs(os.path.dirname(args.out_json), exist_ok=True)
    with open(args.out_json, 'w', encoding='utf-8') as f:
        json.dump(out, f, ensure_ascii=False, separators=(',', ':'))
    write_mapping_xlsx(mapping, groups, args.out_mapping)
    print('wrote', args.out_json, os.path.getsize(args.out_json))
    print('wrote', args.out_mapping, os.path.getsize(args.out_mapping))
    print(json.dumps(summary, ensure_ascii=False, indent=2))
    print(json.dumps(quality, ensure_ascii=False, indent=2))


if __name__ == '__main__':
    main()