#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""PubMed mRNA 论文 → 单位/PI 聚合 + Excel/HTML
输入：数据/mrna_pubmed_raw.json
输出：数据/mrna_pubmed_pi.json、Excel sheet、HTML
"""
import io
import json
import re
from collections import defaultdict

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

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
RAW = W + '/数据/mrna_pubmed_raw.json'
OUT_J = W + '/数据/mrna_pubmed_pi.json'
OUT_X = W + '/销售分析/科研经费_单位级清单_广东浙江_2026.xlsx'
OUT_H = W + '/销售分析/mRNA活跃PI_PubMed反推_2024-2025.html'

PROV = {
    '广东': ['guangdong', 'guangzhou', 'shenzhen', 'zhuhai', 'dongguan', 'foshan', 'zhongshan', 'shantou',
             'zhanjiang', 'huizhou', 'jiangmen', 'zhaoqing', '广东', '广州', '深圳', '珠海', '东莞', '佛山', '汕头'],
    '浙江': ['zhejiang', 'hangzhou', 'ningbo', 'wenzhou', 'shaoxing', 'jiaxing', 'jinhua', 'taizhou',
             'huzhou', 'lishui', 'quzhou', 'zhoushan', '浙江', '杭州', '宁波', '温州', '绍兴', '嘉兴', '金华', '台州'],
}
INST_KW = re.compile(r'(universit|hospital|institut|academy|college|laborator|centre|center|school of medicine|medical)', re.I)


def inst_of(aff):
    parts = [p.strip() for p in re.split(r'[,，;；]', aff or '') if p.strip()]
    best, best_score = '', 0
    for p in parts:
        pl = p.lower()
        sc = 0
        if 'university' in pl:
            sc = 4
        elif 'hospital' in pl:
            sc = 4
        elif 'institute' in pl or 'academy' in pl:
            sc = 3
        elif 'college' in pl or 'school of medicine' in pl:
            sc = 2
        elif 'laborator' in pl or 'center' in pl or 'centre' in pl:
            sc = 1
        if sc >= best_score and sc > 0 and len(p) > 6:
            best, best_score = p, sc
    if best:
        b = re.sub(r'\s+', ' ', best).replace('Yat-Sen', 'Yat-sen').strip(' .')
        return b[:90]
    return parts[0][:90] if parts else ''


def prov_of(text):
    t = (text or '').lower()
    for p, kws in PROV.items():
        if any(k.lower() in t for k in kws):
            return p
    return ''


def nsfc_ids(grants):
    out = []
    for g in grants or []:
        gid = (g.get('id') or '').strip()
        ag = (g.get('agency') or '').lower()
        if re.fullmatch(r'\d{8}', gid) and gid[0] in '345678':
            out.append(gid)
    return sorted(set(out))


recs = json.load(io.open(RAW, encoding='utf-8'))
for r in recs:
    r['prov2'] = r['prov'] or prov_of(r['last_aff']) or prov_of(r.get('all_aff', ''))
    r['inst'] = inst_of(r['last_aff'])
    r['nsfc'] = nsfc_ids(r['grants'])

cn = [r for r in recs if r['china']]
gd = [r for r in cn if r['prov2'] == '广东']
zj = [r for r in cn if r['prov2'] == '浙江']
print('中国 %d ｜广东 %d ｜浙江 %d ｜带国自然号 %d' % (len(cn), len(gd), len(zj), sum(1 for r in cn if r['nsfc'])))


def agg(rs):
    g = defaultdict(lambda: {'n': 0, 'pmids': [], 'nsfc': set(), 'titles': [], 'years': set()})
    for r in rs:
        k = (r['inst'], r['last_ln'] + ' ' + r['last_fn'] if r['last_fn'] else r['last_ln'])
        d = g[k]
        d['n'] += 1; d['pmids'].append(r['pmid']); d['nsfc'].update(r['nsfc'])
        d['years'].add(r['year'])
        if len(d['titles']) < 2:
            d['titles'].append(r['title'])
    out = []
    for (inst, pi), d in g.items():
        out.append({'inst': inst, 'pi': pi.strip(), 'n': d['n'], 'nsfc': sorted(d['nsfc']),
                    'titles': d['titles'], 'years': sorted(d['years']), 'pmids': d['pmids'][:3]})
    out.sort(key=lambda x: (-x['n'], -len(x['nsfc'])))
    return out


pi_gd, pi_zj = agg(gd), agg(zj)
# 单位汇总
def unit_agg(rs):
    u = defaultdict(lambda: {'n': 0, 'pis': set(), 'nsfc': set()})
    for r in rs:
        d = u[r['inst']]
        d['n'] += 1; d['pis'].add(r['last_ln'] + ' ' + r['last_fn']); d['nsfc'].update(r['nsfc'])
    return sorted([{'inst': k, 'n': v['n'], 'pis': len(v['pis']), 'nsfc': len(v['nsfc'])} for k, v in u.items()],
                  key=lambda x: -x['n'])


u_gd, u_zj = unit_agg(gd), unit_agg(zj)
io.open(OUT_J, 'w', encoding='utf-8').write(json.dumps({
    'meta': {'query': 'mRNA/LNP/circRNA/IVT + China[ad] + 2024:2025', 'total': len(recs), 'china': len(cn),
             'gd': len(gd), 'zj': len(zj), 'note': '通讯作者取末位作者；单位为末位作者单位；国自然号取 8 位数字'},
    'gd': {'n': len(gd), 'units': u_gd, 'pis': pi_gd},
    'zj': {'n': len(zj), 'units': u_zj, 'pis': pi_zj},
}, ensure_ascii=False, indent=1))

# ── Excel ──
wb = load_workbook(OUT_X)
for s in ('mRNA_PubMed_PI',):
    if s in wb.sheetnames:
        del wb[s]
ws = wb.create_sheet('mRNA_PubMed_PI')
ws.append(['省', '单位', '通讯作者(PI)', '论文数', '国自然批准号', '年份', '代表论文', 'PMID'])
for prov, pis in (('广东', pi_gd), ('浙江', pi_zj)):
    for p in pis:
        ws.append([prov, p['inst'], p['pi'], p['n'], '/'.join(p['nsfc']), '/'.join(p['years']),
                   p['titles'][0] if p['titles'] else '', '/'.join(p['pmids'])])
HEAD = PatternFill('solid', fgColor='1F4E79'); HF = Font(color='FFFFFF', bold=True, size=10)
for c in range(1, ws.max_column + 1):
    cell = ws.cell(row=1, column=c); cell.fill = HEAD; cell.font = HF
    cell.alignment = Alignment(horizontal='center', wrap_text=True)
for i, w in enumerate([6, 40, 18, 7, 26, 10, 70, 18], start=1):
    ws.column_dimensions[get_column_letter(i)].width = w
ws.freeze_panes = 'A2'; ws.auto_filter.ref = ws.dimensions
wb.save(OUT_X)
print('✅ Excel sheet mRNA_PubMed_PI')

# ── HTML ──
def tbl(pis, n=20):
    rows = []
    for p in pis[:n]:
        rows.append('<tr><td>%s</td><td>%s</td><td class="n">%d</td><td>%s</td><td class="pj">%s</td></tr>' % (
            p['inst'], p['pi'], p['n'], '/'.join(p['nsfc']) or '—', (p['titles'][0] if p['titles'] else '')[:70]))
    return '\n'.join(rows)


def utbl(us, n=12):
    return '\n'.join('<tr><td>%s</td><td class="n">%d</td><td class="n">%d</td><td class="n">%d</td></tr>' %
                     (u['inst'], u['n'], u['pis'], u['nsfc']) for u in us[:n])


CSS = """*{box-sizing:border-box}body{margin:0;background:#f4f6fa;font-family:-apple-system,"PingFang SC",sans-serif;color:#1b2430;font-size:13px;line-height:1.6}
.wrap{max-width:1200px;margin:0 auto;padding:0 18px 40px}h1{font-size:21px;margin:12px 0 4px}.meta{color:#5b6675;font-size:12.2px;margin-bottom:12px}
.card{background:#fff;border:1px solid #e3e8f1;border-radius:10px;padding:14px 16px;margin-bottom:14px}
.card h2{font-size:15px;margin:0 0 10px;padding-left:9px;border-left:3px solid #1F4E79}
table{width:100%;border-collapse:collapse;font-size:11.7px}th{background:#1F4E79;color:#fff;padding:7px 6px;text-align:left}th.n{text-align:right}
td{padding:6px 6px;border-bottom:1px solid #eef1f6;vertical-align:top}td.n{text-align:right;font-variant-numeric:tabular-nums;white-space:nowrap}
td.pj{color:#6b7684;font-size:11.1px}.kpis{display:grid;grid-template-columns:repeat(4,1fr);gap:9px;margin:10px 0 14px}
.kpi{background:#fff;border:1px solid #e3e8f1;border-top:3px solid #1F4E79;border-radius:9px;padding:9px 11px}
.kl{font-size:11px;color:#6b7684}.kv{font-size:19px;font-weight:800;color:#1F4E79}.ks{font-size:10.5px;color:#8a94a3}
.box{background:#fffaf0;border:1px solid #f3e2bf;border-radius:8px;padding:10px 12px}.fn{color:#6b7684;font-size:11.3px;margin-top:7px}"""

html = """<!DOCTYPE html><html lang="zh-CN"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>mRNA 活跃 PI：PubMed 反推（2024–2025，中国机构）</title><style>__CSS__</style></head><body><div class="wrap">
<h1>mRNA 活跃 PI：PubMed 反推名单（广东 / 浙江）</h1>
<div class="meta">不是立项名单，是<b>已经在发论文的人</b>。检索式：mRNA vaccine / mRNA therapeutic / mRNA delivery / lipid nanoparticle / LNP / circRNA / self-amplifying RNA / in vitro transcription + China[ad] + 2024–2025。通讯作者取末位作者，单位为末位作者单位。截至 2026-10-17。</div>
<div class="kpis">
 <div class="kpi"><div class="kl">中国机构论文</div><div class="kv">__CN__</div><div class="ks">命中总 __TOT__ 篇</div></div>
 <div class="kpi"><div class="kl">广东</div><div class="kv">__GD__ 篇</div><div class="ks">__GDU__ 家单位</div></div>
 <div class="kpi"><div class="kl">浙江</div><div class="kv">__ZJ__ 篇</div><div class="ks">__ZJU__ 家单位</div></div>
 <div class="kpi"><div class="kl">带国自然批准号</div><div class="kv">__GR__ 篇</div><div class="ks">可回填官方立项库</div></div>
</div>
<div class="card"><h2>广东：单位 Top 12</h2>
<table><thead><tr><th>单位</th><th class="n">论文数</th><th class="n">PI 数</th><th class="n">国自然号数</th></tr></thead><tbody>__UGD__</tbody></table></div>
<div class="card"><h2>广东：活跃 PI Top 20</h2>
<table><thead><tr><th>单位</th><th>通讯作者</th><th class="n">论文数</th><th>国自然批准号</th><th>代表论文</th></tr></thead><tbody>__PGD__</tbody></table></div>
<div class="card"><h2>浙江：单位 Top 12</h2>
<table><thead><tr><th>单位</th><th class="n">论文数</th><th class="n">PI 数</th><th class="n">国自然号数</th></tr></thead><tbody>__UZJ__</tbody></table></div>
<div class="card"><h2>浙江：活跃 PI Top 20</h2>
<table><thead><tr><th>单位</th><th>通讯作者</th><th class="n">论文数</th><th>国自然批准号</th><th>代表论文</th></tr></thead><tbody>__PZJ__</tbody></table></div>
<div class="card"><h2>怎么用 / 局限</h2>
<div class="box"><ul style="margin:2px 0;padding-left:18px">
<li><b>用法</b>：这份名单是“现在就在做 mRNA 的实验室”，比立项名单更接近真实采购；国自然批准号可直接与官方导出对撞，验证是否为 2026 立项。</li>
<li><b>局限 1</b>：末位作者≈通讯作者，但不绝对；个别论文通讯作者非末位。</li>
<li><b>局限 2</b>：只有发过论文的 PI，新立项还没发文的人不在名单里。</li>
<li><b>局限 3</b>：单位取末位作者单位，合作论文可能归错单位。</li>
<li><b>局限 4</b>：只覆盖 2024–2025 英文论文，中文期刊与会议不在内。</li>
</ul></div></div>
__FOOTER__
</div></body></html>"""

html = (html.replace('__CSS__', CSS).replace('__CN__', f'{len(cn):,}').replace('__TOT__', f'{len(recs):,}')
        .replace('__GD__', str(len(gd))).replace('__GDU__', str(len(u_gd)))
        .replace('__ZJ__', str(len(zj))).replace('__ZJU__', str(len(u_zj)))
        .replace('__GR__', str(sum(1 for r in cn if r['nsfc'])))
        .replace('__UGD__', utbl(u_gd)).replace('__PGD__', tbl(pi_gd))
        .replace('__UZJ__', utbl(u_zj)).replace('__PZJ__', tbl(pi_zj)))
io.open(OUT_H, 'w', encoding='utf-8').write(html)
print('✅ HTML %.0f KB' % (len(html.encode()) / 1024))
print('广东 Top5:', [(p['inst'][:20], p['pi'], p['n']) for p in pi_gd[:5]])
print('浙江 Top5:', [(p['inst'][:20], p['pi'], p['n']) for p in pi_zj[:5]])