#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""生命科学部：需求 → 客户（单位 + 课题组）

数据源（仅这两类）
  1) 近 6 个月飞书活动：activities_feishu.json(7–9 月正文) + daily_activities_mcp.json(7–10 月正文)
     + activities_q2q3_2026.json(4–7 月，仅活动类型、无正文)
  2) 生命科学部 Q3 汇报 8 份（pptx，含竞品价格表里的课题组名）

客户范围：官方清单「生命科学销售部」114 家（2026-09-30 版）

输出：数据/2027战略_生命科学_需求客户.json
"""
import io
import json
import re
import zipfile
import glob
import os
from collections import defaultdict

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
D = W + '/数据/'
B = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2026年3季度汇报/生命科学销售部/'
LO, HI = '2026-04-17', '2026-10-17'

CATS = [
    ('逆转录 / qPCR', ['逆转录', 'RT-qPCR', 'RTQ', 'rtq', 'qPCR', 'qpcr', '荧光定量', '预混', '探针', 'MIX', 'Mix']),
    ('克隆 / 同源重组 / 连接', ['连接酶', 'T4', '同源重组', '克隆', '载体', '感受态', '质粒构建', '重组酶', 'Gibson']),
    ('高保真酶 / PCR', ['高保真', 'PCR', '扩增', 'Phusion', 'Pfu', 'Taq']),
    ('核酸提取 / 质粒提取', ['提取', '柱提', '磁珠', '质粒小提', '胶回收', '核酸纯化', 'DNA提取', 'RNA提取']),
    ('蛋白 / WB', ['蛋白', 'WB', 'Western', 'marker', 'Marker', '抗体', '跑胶', 'PVDF', '纯化']),
    ('mRNA / 建库 / NGS', ['mRNA', '建库', 'NGS', '测序', '文库', '单细胞', '三代']),
    ('价格 / 促销', ['价格', '报价', '折扣', '买赠', '买一送', '促销', '开学季', '活动价', '积分', '京东卡', '优惠']),
    ('技术支持 / 实验服务', ['技术支持', '带教', '培训', '代做', '方案', '实验设计', '答疑', '售后']),
    ('货期 / 现货', ['现货', '货期', '交期', '断货', '库存', '发货']),
]

official = json.load(io.open(D + '客户清单_官方_20260930.json', encoding='utf-8'))['customers']
LS = {x['cust'] for x in official if x['dept'] == '生命科学部'}
OWNER = {x['cust']: x['sp'] for x in official}
LS_NAMES = sorted(LS, key=len, reverse=True)
print('█ 官方生命科学客户 %d 家' % len(LS))


def match(raw):
    c = str(raw or '').strip()
    c = re.sub(r'[-－](广东|浙江|上海|北京|江苏|华南|华北|华中|华西)$', '', c)
    for n in LS_NAMES:
        if n in c or (len(c) >= 5 and c in n):
            return n
    return ''


acts = []
def push(date, cust, text, src, has_text=True):
    d = str(date or '')[:10]
    if not (LO <= d <= HI):
        return
    c = match(cust)
    if not c:
        return
    t = re.sub(r'<[^>]+>|!\[.*?\)|\s+', ' ', str(text or '')).strip()
    acts.append({'d': d, 'c': c, 't': t, 'src': src, 'full': has_text and len(t) > 6})

fs = json.load(io.open(D + 'activities_feishu.json', encoding='utf-8'))['items']
for x in fs:
    if str(x.get('dept_raw')) in ('广东', '浙江'):
        push(x.get('date'), x.get('customer'), x.get('text'), 'FS')
mp = json.load(io.open(D + 'daily_activities_mcp.json', encoding='utf-8'))['items']
for x in mp:
    v = x.get('field_5ed7ab')
    if isinstance(v, str):
        m = re.search(r"'label':\s*'([^']+)'", v); v = m.group(1) if m else v
    elif isinstance(v, dict):
        v = v.get('label', '')
    if v in ('广东', '浙江'):
        cu = x.get('field_5f20fc')
        if isinstance(cu, str):
            m = re.search(r"'label':\s*'([^']+)'", cu); cu = m.group(1) if m else cu
        elif isinstance(cu, dict):
            cu = cu.get('label', '')
        push(x.get('start_time'), cu, x.get('field_b99055'), 'MCP')
q = json.load(io.open(D + 'activities_q2q3_2026.json', encoding='utf-8'))['activities']
for x in q:
    if str(x.get('dept_name')) in ('生命科学-浙江', '生命科学-广东'):
        pass  # 4–6 月无正文，跳过证据（只做覆盖说明）

seen, uniq = set(), []
for a in acts:
    k = (a['d'], a['c'], a['t'][:60])
    if k in seen:
        continue
    seen.add(k); uniq.append(a)
acts = uniq
print('█ 生命科学活动（有正文）%d 条 ｜ 客户 %d 家' % (len(acts), len({a['c'] for a in acts})))

# Q3 汇报：文本 + 课题组
deck = ''
for f in sorted(glob.glob(B + '*.pptx')):
    z = zipfile.ZipFile(f)
    for n in sorted([n for n in z.namelist() if re.match(r'ppt/slides/slide\d+\.xml$', n)],
                    key=lambda s: int(re.search(r'\d+', s).group())):
        x = z.read(n).decode('utf-8', 'ignore')
        x = re.sub(r'</a:p>', '\n', x)
        deck += re.sub(r'<[^>]+>', '', x) + '\n'
print('█ Q3 生命科学汇报文本 %d 字' % len(re.findall(r'[\u4e00-\u9fff]', deck)))
# 课题组名（…课题组 / …组）
groups = set(re.findall(r'([\u4e00-\u9fff]{2,4})\s*(?:课题组|研究组|组)\b', deck))

res = []
for cat, kws in CATS:
    hits = defaultdict(lambda: {'n': 0, 'samples': []})
    for a in acts:
        t = a['t']
        if any(k in t for k in kws):
            h = hits[a['c']]
            h['n'] += 1
            if len(h['samples']) < 2 and len(t) > 10:
                h['samples'].append({'d': a['d'], 't': t[:110], 'src': a['src']})
    # 汇报命中（客户名 + 关键词）
    for c in LS:
        if c in deck:
            for m in re.finditer(re.escape(c), deck):
                seg = deck[m.end():m.end() + 200]
                if any(k in seg for k in kws):
                    hits[c]['n'] += 1
                    hits[c]['samples'].append({'d': '2026-Q3', 't': '（Q3 汇报命中）', 'src': 'Q3'})
                    break
    ranked = sorted(hits.items(), key=lambda kv: -kv[1]['n'])
    res.append({'cat': cat, 'total': sum(v['n'] for _, v in ranked),
                'customers': [{'cust': c, 'owner': OWNER.get(c, ''), 'hits': h['n'], 'samples': h['samples'][:2]}
                              for c, h in ranked[:6]]})

json.dump({'generated': '2026-10-17', 'window': [LO, HI], 'activities': len(acts),
           'ls_customers': len(LS), 'groups_in_deck': sorted(groups), 'cats': res},
          io.open(D + '2027战略_生命科学_需求客户.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1)

for r in res:
    print('\n▸ %s（命中 %d）' % (r['cat'], r['total']))
    for c in r['customers'][:5]:
        print('   %-28s %-5s %2d ｜%s' % (c['cust'][:26], c['owner'], c['hits'],
                                          (c['samples'][0]['t'][:70] if c['samples'] else '')))
print('\n✅ 数据/2027战略_生命科学_需求客户.json')