#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""战略突破方向提取：单细胞/组学/测序 + mRNA
输出：数据/战略突破_项目PI清单.json、Excel 两张 sheet、HTML 目标名单
"""
import csv
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输出-工作类'
BASE = W + '/销售分析/基金源文件'
OUT_J = W + '/数据/战略突破_项目PI清单.json'
OUT_X = W + '/销售分析/科研经费_单位级清单_广东浙江_2026.xlsx'
OUT_H = W + '/销售分析/战略突破_单细胞组学与mRNA_目标名单_2026.html'

ZJ_AMT = [('重大', 100), ('杰青', 80), ('重点', 30), ('探索', 10), ('青基', 10), ('青稞', 10), ('地区', 10), ('延续', 80)]


def zj_amt(cat):
    for k, v in ZJ_AMT:
        if k in cat:
            return v
    return 10


rows = []
for r in csv.DictReader(io.open(BASE + '/parsed/浙江_2026省基金_明细.csv', encoding='utf-8-sig')):
    if r['no'].isdigit() and int(r['no']) <= 2667:
        rows.append({'prov': '浙江', 'unit': r['unit'], 'pi': r['pi'].strip() or '未填', 'name': r['name'],
                     'amt': zj_amt(r['cat']), 'cat': r['cat']})
for r in csv.DictReader(io.open(BASE + '/parsed/广东_2026省基金_明细.csv', encoding='utf-8-sig')):
    rows.append({'prov': '广东', 'unit': r['unit'], 'pi': (r['pi'] or '').strip() or '未填', 'name': r['name'],
                 'amt': int(r['amt']), 'cat': r['cat']})

SC = ['单细胞', 'scRNA', '空间转录组', '空间组学', '细胞图谱', '细胞异质性']
OMICS = ['组学', '基因组', '转录组', '蛋白组', '代谢组', '宏基因组', '测序', '多组学', '表观组', '微生物组', '全基因组', '重测序']
MRNA = ['mRNA', '信使RNA', '环状RNA', 'circRNA', '核酸疫苗', '脂质纳米', 'LNP', '体外转录', 'IVT', '加帽', '核苷酸修饰', '递送系统']


def hit(title, kws):
    t = (title or '').lower()
    return [k for k in kws if k.lower() in t]


def pick(kws):
    out = []
    for r in rows:
        h = hit(r['name'], kws)
        if h:
            out.append({**r, 'hit': '/'.join(sorted(set(h)))})
    return out


sc = pick(SC); om = pick(OMICS); mr = pick(MRNA)
# 单细胞并入组学（去重）
seen = {(r['prov'], r['unit'], r['pi'], r['name']) for r in sc}
om = [r for r in om if (r['prov'], r['unit'], r['pi'], r['name']) not in seen] + sc
print('单细胞 %d 项 / %.0f 万' % (len(sc), sum(r['amt'] for r in sc)))
print('组学测序 %d 项 / %.0f 万' % (len(om), sum(r['amt'] for r in om)))
print('mRNA %d 项 / %.0f 万' % (len(mr), sum(r['amt'] for r in mr)))


def agg(rs):
    by_u = defaultdict(lambda: {'n': 0, 'amt': 0, 'pis': set()})
    by_p = {}
    for r in rs:
        u = by_u[(r['prov'], r['unit'])]
        u['n'] += 1; u['amt'] += r['amt']; u['pis'].add(r['pi'])
        k = (r['prov'], r['unit'], r['pi'])
        if k not in by_p:
            by_p[k] = {'prov': r['prov'], 'unit': r['unit'], 'pi': r['pi'], 'n': 0, 'amt': 0,
                       'proj': [], 'hit': set(), 'cat': r['cat']}
        p = by_p[k]; p['n'] += 1; p['amt'] += r['amt']; p['hit'].add(r['hit'])
        if len(p['proj']) < 2:
            p['proj'].append(r['name'])
    units = sorted([{'prov': k[0], 'unit': k[1], **v, 'pis': len(v['pis'])} for k, v in by_u.items()], key=lambda x: -x['amt'])
    pis = sorted(by_p.values(), key=lambda x: -x['amt'])
    return units, pis


u_om, p_om = agg(om)
u_mr, p_mr = agg(mr)
for _p in p_om + p_mr:
    _p['hit'] = sorted(_p['hit'])
    _p['proj'] = [_x for _x in _p['proj']]

io.open(OUT_J, 'w', encoding='utf-8').write(json.dumps({
    'meta': {'sc': len(sc), 'omics': len(om), 'mrna': len(mr),
             'note': '关键词命中；组学含单细胞；同一项目可能同时命中组学与 mRNA'},
    'omics': {'n': len(om), 'amt': sum(r['amt'] for r in om), 'units': u_om, 'pis': p_om},
    'mrna': {'n': len(mr), 'amt': sum(r['amt'] for r in mr), 'units': u_mr, 'pis': p_mr},
}, ensure_ascii=False, indent=1))

# ── Excel ──
wb = load_workbook(OUT_X)
for s in ('战略_单细胞组学', '战略_mRNA'):
    if s in wb.sheetnames:
        del wb[s]
HEAD = PatternFill('solid', fgColor='1F4E79'); HF = Font(color='FFFFFF', bold=True, size=10)


def sheet(name, units, pis):
    ws = wb.create_sheet(name)
    ws.append(['层级', '省', '单位', '负责人', '项数', '经费(万元)', '项目类别', '命中关键词', '代表项目'])
    for u in units[:40]:
        ws.append(['单位', u['prov'], u['unit'], '（%d 位负责人）' % u['pis'], u['n'], u['amt'], '', '', ''])
    for p in pis:
        ws.append(['课题组', p['prov'], p['unit'], p['pi'], p['n'], p['amt'], p['cat'], '/'.join(sorted(p['hit'])), '；'.join(p['proj'])])
    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([8, 6, 30, 14, 8, 12, 14, 20, 70], start=1):
        ws.column_dimensions[get_column_letter(i)].width = w
    ws.freeze_panes = 'A2'


sheet('战略_单细胞组学', u_om, p_om)
sheet('战略_mRNA', u_mr, p_mr)
wb.save(OUT_X)
print('✅ Excel 两张战略 sheet')


def trs(units, pis, n=25):
    out = []
    for u in units[:12]:
        out.append('<tr class="u"><td>%s</td><td>%s</td><td>%d 位负责人</td><td class="n">%d</td><td class="n">%s</td><td>—</td><td>—</td></tr>' % (
            u['prov'], u['unit'], u['pis'], u['n'], f"{u['amt']:,}"))
    for p in pis[:n]:
        out.append('<tr><td>%s</td><td>%s</td><td>%s</td><td class="n">%d</td><td class="n">%s</td><td>%s</td><td class="pj">%s</td></tr>' % (
            p['prov'], p['unit'], p['pi'], p['n'], f"{p['amt']:,}", p['cat'] if len(p['proj']) == 1 else '/'.join(sorted(p['hit'])), p['proj'][0][:46]))
    return '\n'.join(out)


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:1240px;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}
.card h2 span{font-size:10.6px;color:#8a94a3;font-weight:600;margin-left:6px}
table{width:100%;border-collapse:collapse;font-size:11.6px}
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:11px}tr.u td{background:#f2f7fd;font-weight:600}
.kpis{display:grid;grid-template-columns:repeat(3,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:#f7f9fd;border:1px solid #dfe6f2;border-radius:8px;padding:10px 12px}
.box.warn{background:#fffaf0;border-color:#f3e2bf}.grid2{display:grid;grid-template-columns:1fr 1fr;gap:10px}
.fn{color:#6b7684;font-size:11.3px;margin-top:7px}ul{margin:4px 0;padding-left:18px}"""

html = """<!DOCTYPE html><html lang="zh-CN"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
<title>战略突破方向：单细胞/组学/测序 与 mRNA</title><style>__CSS__</style></head><body><div class="wrap">
<h1>战略突破方向：单细胞 / 组学 / 测序 与 mRNA</h1>
<div class="meta">范围：广东 + 浙江 2026 省自然科学基金立项清单（5,838 项逐项）。按项目名称关键词命中，颗粒度到<b>单位 + 负责人</b>。截至 2026-10-17。</div>
<div class="kpis">
 <div class="kpi"><div class="kl">单细胞 / 组学 / 测序</div><div class="kv">__ON__ 项 · __OA__ 万</div><div class="ks">涉及 __OUN__ 家单位</div></div>
 <div class="kpi"><div class="kl">mRNA 相关</div><div class="kv">__MN__ 项 · __MA__ 万</div><div class="ks">涉及 __MUN__ 家单位</div></div>
 <div class="kpi"><div class="kl">两方向合计</div><div class="kv">__TA__ 万</div><div class="ks">占省基金总盘 __TP__%</div></div>
</div>

<div class="card"><h2>一、为什么这两块是“战略”而不是“不可及” <span>现金盘小，但决定 2027 产品线</span></h2>
<div class="grid2">
<div class="box"><h4 style="margin:0 0 4px">单细胞 / 组学 / 测序</h4><ul>
<li><b>宝锐现在能供</b>：核酸提取（柱式/磁珠）、逆转录、文库构建用酶（Tn5、连接酶）、dNTP。</li>
<li><b>缺</b>：单细胞建库试剂盒、空间组学探针、测序接头/纯化磁珠。</li>
<li><b>打法</b>：不做建库整体方案，做“建库前处理 + 酶”的性价比替代，绑客户已有平台。</li></ul></div>
<div class="box"><h4 style="margin:0 0 4px">mRNA</h4><ul>
<li><b>宝锐现在能供</b>：T7 体外转录酶、加帽酶、DNase I、修饰核苷酸、纯化柱/磁珠。</li>
<li><b>缺</b>：LNP 递送配方、GMP 级原料、质控方法包。</li>
<li><b>打法</b>：从科研级 IVT 原料切入，先做“能重复出结果”的整套小试包。</li></ul></div>
</div>
<div class="fn">这两块当期经费只占省基金 __TP__%，但客户是同一批 PI；等盘子大了再进，供应链已被锁死。</div></div>

<div class="card"><h2>二、单细胞 / 组学 / 测序：单位与 PI 名单 <span>__ON__ 项 / __OA__ 万</span></h2>
<table><thead><tr><th>省</th><th>单位</th><th>负责人</th><th class="n">项数</th><th class="n">经费(万)</th><th>关键词</th><th>代表项目</th></tr></thead>
<tbody>__T_OM__</tbody></table>
<div class="fn">前 12 行为单位汇总（按经费），其后为 PI 级 Top 25。全量见 Excel「战略_单细胞组学」。</div></div>

<div class="card"><h2>三、mRNA：单位与 PI 名单 <span>__MN__ 项 / __MA__ 万</span></h2>
<table><thead><tr><th>省</th><th>单位</th><th>负责人</th><th class="n">项数</th><th class="n">经费(万)</th><th>关键词</th><th>代表项目</th></tr></thead>
<tbody>__T_MR__</tbody></table>
<div class="fn">全量见 Excel「战略_mRNA」。命中词含 mRNA/环状RNA/核酸疫苗/LNP/体外转录/加帽。</div></div>

<div class="card"><h2>四、这份名单怎么用</h2><div class="grid2">
<div class="box"><h4 style="margin:0 0 4px">动作</h4><ul>
<li>把 PI 名单按省分给对应销售，作为 2027 Q1 拜访清单（不是撒网，是定点）。</li>
<li>每个 PI 只问三个问题：现在用谁家的建库/IVT 原料、卡在哪个环节、能不能给我们一次小试。</li>
<li>单细胞方向优先找“有平台、缺耗材预算”的单位；mRNA 方向优先找“刚立项、还没定供应商”的新 PI。</li></ul></div>
<div class="box warn"><h4 style="margin:0 0 4px">注意</h4><ul>
<li>关键词命中会漏掉不写“组学/mRNA”但实际做相关实验的项目，名单是下限不是上限。</li>
<li>经费是项目总额，不是耗材预算；单细胞项目的试剂占比通常高于普通项目。</li>
<li>mRNA 在省基金里项目数少，真正的量在国自然与横向课题，需另抓。</li></ul></div>
</div></div>
__FOOTER__
</div></body></html>"""

html = (html.replace('__CSS__', CSS)
        .replace('__ON__', str(len(om))).replace('__OA__', f"{sum(r['amt'] for r in om):,}")
        .replace('__OUN__', str(len(u_om))).replace('__MN__', str(len(mr)))
        .replace('__MA__', f"{sum(r['amt'] for r in mr):,}").replace('__MUN__', str(len(u_mr)))
        .replace('__TA__', f"{sum(r['amt'] for r in om) + sum(r['amt'] for r in mr):,}")
        .replace('__TP__', '%.1f' % ((sum(r['amt'] for r in om) + sum(r['amt'] for r in mr)) / 92020 * 100))
        .replace('__T_OM__', trs(u_om, p_om)).replace('__T_MR__', trs(u_mr, p_mr)))
io.open(OUT_H, 'w', encoding='utf-8').write(html)
print('✅ HTML %.0f KB' % (len(html.encode()) / 1024))