#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""生成②核心核酸扩增原料货号 × 应用场景速查表：可编辑HTML + Excel"""
import json, os

BASE = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/刘子研上手包'

# ============ 核心产品线数据（32条） ============
# 字段：品类、产品线、代表货号、形态、应用场景(初稿)、典型客户(留空=横线待填)
LINES = [
# ---- qPCR 预混液（DNA 探针法）----
{"cat":"qPCR预混液","name":"FastAmpli Premix-UNG Ⅳ","item":"M2181-4 / FM2181-4 / M0181-4","form":"2×/5×/10× 预混液","scene":"DNA 探针法定量 PCR 主力，带 UNG 防污染。用于乙肝、结核、HPV、呼吸道等病原 DNA 检测试剂盒的通用 qPCR 体系。","cust":""},
{"cat":"qPCR预混液","name":"Superstart Premix plus-UNG","item":"M2071 / M0071","form":"2×/10× 预混液","scene":"通用型 qPCR 预混液，性价比之选，适合大批量常规 DNA 定量。","cust":""},
{"cat":"qPCR预混液","name":"Robustart Premix-UNG","item":"M2161 / M4211-3 / M416-3","form":"2×/2.35×/4× 预混液","scene":"抗抑制能力强，适合粗制样本（血液直扩、免提取）的 DNA 定量。","cust":""},
{"cat":"qPCR预混液","name":"Superstart Direct Premix ARMS-UNG","item":"MD2031","form":"2× 预混液","scene":"ARMS 等位基因特异性扩增，用于 SNP/点突变检测（如肿瘤靶向用药基因分型）。","cust":""},
{"cat":"qPCR预混液","name":"Fast Direct Premix-UNG","item":"MD2071","form":"2× 预混液","scene":"直接扩增（免提取），血液/组织/拭子直接上样，适合快检场景。","cust":""},
{"cat":"qPCR预混液","name":"SensiDirect Premix-UNG","item":"MD2091","form":"2× 预混液","scene":"高灵敏度直接扩增，低拷贝样本也能稳定检出。","cust":""},
{"cat":"qPCR预混液","name":"SensiStart Premix-UNG","item":"M5261","form":"5× 预混液","scene":"高灵敏度热启动 qPCR，样本珍贵、需低 LOD 的场景。","cust":""},
{"cat":"qPCR预混液","name":"Hyperstart Tth Premix Buffer","item":"2FM101-1-2","form":"8× 缓冲液","scene":"Tth 酶体系，耐高温、兼有逆转录活性，DNA/RNA 都能扩增，特殊场景定制。","cust":""},
# ---- qRT-PCR 预混液（一步法）----
{"cat":"qRT-PCR预混液","name":"Neoscript RT Premix","item":"M5274 / M2274 / M8254 / FM513","form":"2×/5×/8× 预混液","scene":"一步法 qRT-PCR 主力（逆转录+定量一管完成），带 UNG 防污染。RNA 病毒检测（新冠、流感、合胞等）试剂盒首选。","cust":""},
{"cat":"qRT-PCR预混液","name":"Neoscript Fast RT Premix-UNG","item":"M5244 / M2244","form":"2×/5× 预混液","scene":"快速一步法 qRT-PCR，缩短扩增时间，适合高通量、快检。","cust":""},
{"cat":"qRT-PCR预混液","name":"SensiDirect RT Premix-UNG","item":"MD5104 / MD2104 / FMD5104","form":"1.25×/2×/5×/10× 预混液","scene":"高灵敏度一步法 qRT-PCR + 直接扩增，低拷贝 RNA 样本、免提取场景。","cust":""},
{"cat":"qRT-PCR预混液","name":"Fast Direct RT Premix-UNG","item":"FMD5084","form":"5× 预混液","scene":"快速直接一步法 qRT-PCR，免提取 + 快速扩增，POCT/快检一体机常用。","cust":""},
{"cat":"qRT-PCR预混液","name":"FastAmpli RT Premix-UNG","item":"FM5144","form":"5× 预混液","scene":"FastAmpli 系列的一步法 RT 版，DNA/RNA 通吃的高性能体系。","cust":""},
# ---- Taq 聚合酶 ----
{"cat":"Taq聚合酶","name":"Robustart Taq QS Ⅱ","item":"E20 / FE20","form":"酶（热启动）","scene":"热启动 Taq 聚合酶主力，耐杂质、特异高。客户自配 qPCR/普通 PCR 体系的首选酶。","cust":""},
{"cat":"Taq聚合酶","name":"Robustart Taq QS Ⅲ","item":"FE40","form":"酶（热启动）","scene":"三代升级，耐抑制更强，复杂样本（血、粪便、环境）直扩。","cust":""},
{"cat":"Taq聚合酶","name":"Robustart Taq","item":"FE16","form":"酶","scene":"普通 Taq 酶，常规 PCR 扩增。","cust":""},
{"cat":"Taq聚合酶","name":"Superstart Taq plus","item":"E07 / FE07","form":"酶","scene":"通用型 Taq，性价比高，常规检测。","cust":""},
{"cat":"Taq聚合酶","name":"HS Taq","item":"E03","form":"酶（热启动）","scene":"热启动 Taq，降低非特异扩增。","cust":""},
{"cat":"Taq聚合酶","name":"Polluxstart Taq QS Ⅲ","item":"E41","form":"酶（热启动）","scene":"Polluxstart 系列热启动酶。","cust":""},
{"cat":"Taq聚合酶","name":"Hyperstart Tth","item":"2FM101-1-1","form":"酶","scene":"Tth 耐高温聚合酶，兼具逆转录活性，DNA/RNA 一酶通吃，特殊体系定制。","cust":""},
{"cat":"Taq聚合酶","name":"Hyperstart Taq/UNG 酶","item":"2FM091-1","form":"酶（预混 UNG）","scene":"Taq + UNG 预混酶，客户自配体系时直接加防污染。","cust":""},
{"cat":"Taq聚合酶","name":"Hyperstart Tth plus/UNG 酶","item":"2FM101-2-1","form":"酶（预混 UNG）","scene":"Tth + UNG 预混，DNA/RNA 双靶标 + 防污染。","cust":""},
# ---- 逆转录酶 ----
{"cat":"逆转录酶","name":"Neoscript RTase","item":"E13 / FE13","form":"酶","scene":"逆转录酶主力，两步法 qRT-PCR 或客户自配 RT 体系。耐高温、逆转录效率高。","cust":""},
{"cat":"逆转录酶","name":"Neoscript RTase HS","item":"E29 / FE29 / FE43","form":"酶（热启动）","scene":"热启动逆转录酶，降低非特异，适合低拷贝 RNA。","cust":""},
{"cat":"逆转录酶","name":"Neoscript RTase/UNG Mix","item":"M5134","form":"酶预混","scene":"逆转录酶 + UNG 预混，自配一步法体系用。","cust":""},
{"cat":"逆转录酶","name":"Superstart Direct Part RTase","item":"FMDR02","form":"酶","scene":"直接逆转录酶，配合免提取直接扩增。","cust":""},
{"cat":"逆转录酶","name":"SensiDircet RTase/UNG Mix","item":"FMD5104","form":"酶预混","scene":"高灵敏逆转录酶 + UNG 预混，低拷贝 RNA 一步法自配。","cust":""},
# ---- LAMP 等温扩增 ----
{"cat":"LAMP等温扩增","name":"Lamp Premix Plus","item":"PFHW1007","form":"预混液","scene":"LAMP 等温扩增预混液，Bst2.0/3.0 热启动，60-65℃ 恒温快检。POCT、现场快检、动物疫病检测。","cust":""},
{"cat":"LAMP等温扩增","name":"Bst 2.0 HS","item":"FE103","form":"酶","scene":"Bst DNA 聚合酶（链置换活性），LAMP/等温扩增的核心酶。","cust":""},
# ---- 动物诊断原料 ----
{"cat":"动物诊断原料","name":"DNA类动物诊断原料 mix","item":"AHM2031 / AHM202","form":"预混液","scene":"兽用 DNA 检测（非洲猪瘟等动物疫病 PCR）。","cust":""},
{"cat":"动物诊断原料","name":"RNA类动物诊断原料 mix","item":"AHRM5014","form":"预混液","scene":"兽用 RNA 检测（禽流感、口蹄疫等一步法 qRT-PCR）。","cust":""},
# ---- 辅助酶 ----
{"cat":"辅助酶","name":"UNG","item":"E01","form":"酶","scene":"尿嘧啶 DNA 糖基化酶，防污染体系核心，可单独购买搭配自配体系。","cust":""},
{"cat":"辅助酶","name":"RNase Inhibitor","item":"AS05 / FAS05","form":"酶","scene":"RNA 酶抑制剂，保护 RNA 不被降解，RT 实验必加。","cust":""},
{"cat":"辅助酶","name":"EXase DNA polymerase","item":"E36","form":"酶（高保真）","scene":"高保真 DNA 聚合酶（3'→5' 校对），测序、克隆、SNP 分析用。","cust":""},
]

# ============ 生成 HTML ============
def esc(s):
    return (s.replace('&','&amp;').replace('<','&lt;').replace('>','&gt;')
             .replace('"','&quot;').replace("'",'&#39;'))

data_json = json.dumps(LINES, ensure_ascii=False)
CATS = ['qPCR预混液','qRT-PCR预混液','Taq聚合酶','逆转录酶','LAMP等温扩增','动物诊断原料','辅助酶']

html = '''<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>核心核酸扩增原料货号 × 应用场景速查表 · 宝锐生物</title>
<style>
:root{--primary:#1a73e8;--deep:#1852B6;--bg:#f5f7fa;--card:#fff;--muted:#5f6368;--line:#e5e7eb;--green:#0d904f;--orange:#b06000;}
*{box-sizing:border-box;margin:0;padding:0;}
body{font-family:-apple-system,"PingFang SC","Microsoft YaHei",sans-serif;background:var(--bg);color:#202124;}
header{background:linear-gradient(135deg,#1852B6,#1a73e8);color:#fff;padding:22px 28px;}
header h1{font-size:22px;font-weight:700;}
header .sub{font-size:13px;opacity:.9;margin-top:6px;}
header .brand{font-size:12px;opacity:.75;margin-top:4px;letter-spacing:1px;}
.toolbar{position:sticky;top:0;background:#fff;border-bottom:1px solid var(--line);padding:12px 28px;display:flex;gap:12px;align-items:center;flex-wrap:wrap;z-index:10;}
.toolbar input{flex:1;min-width:200px;padding:9px 14px;border:1px solid var(--line);border-radius:8px;font-size:14px;outline:none;}
.toolbar input:focus{border-color:var(--primary);}
.toolbar button{padding:9px 16px;border:1px solid var(--line);border-radius:8px;background:#fff;font-size:13px;cursor:pointer;color:#202124;}
.toolbar button.primary{background:var(--primary);color:#fff;border-color:var(--primary);}
.hint{font-size:12.5px;color:var(--muted);padding:10px 28px;background:#fff7e8;border-bottom:1px solid #ffe3b3;}
.wrap{padding:20px 28px 40px;}
table{width:100%;border-collapse:collapse;background:#fff;border-radius:12px;overflow:hidden;box-shadow:0 2px 10px rgba(0,0,0,.06);}
th{background:var(--deep);color:#fff;font-size:13px;font-weight:600;padding:12px 12px;text-align:left;white-space:nowrap;}
td{padding:11px 12px;font-size:13px;border-bottom:1px solid var(--line);vertical-align:top;line-height:1.55;}
tr:hover td{background:#f8fbff;}
.cat{color:var(--primary);font-weight:600;white-space:nowrap;font-size:12px;}
.name{font-weight:700;font-size:13.5px;min-width:160px;}
.item{color:var(--green);font-weight:600;font-family:Menlo,Consolas,monospace;font-size:12px;white-space:nowrap;}
.form{color:var(--muted);font-size:12px;white-space:nowrap;}
.scene{min-width:260px;}
.cust{min-width:120px;color:var(--orange);}
[contenteditable]{outline:none;border-radius:4px;transition:background .15s;}
[contenteditable]:hover{background:#e8f0fe;}
[contenteditable]:focus{background:#fff3cd;}
.cust[contenteditable]:empty::before{content:"________（待填写）";color:#c0c0c0;}
.toast{position:fixed;bottom:24px;right:24px;background:#202124;color:#fff;padding:12px 20px;border-radius:10px;font-size:14px;opacity:0;transition:opacity .3s;pointer-events:none;}
.toast.show{opacity:1;}
@media print{header,.toolbar,.hint{display:none;}body{background:#fff;}tr{break-inside:avoid;}}
</style>
</head>
<body>
<header>
  <h1>核心核酸扩增原料 · 货号 × 应用场景速查表</h1>
  <div class="sub">分子诊断原料核心产品线速查 · 覆盖 qPCR / qRT-PCR / 酶 / 等温扩增</div>
  <div class="brand">BIORI BIOTECH · 刘子研新手上手包</div>
</header>
<div class="toolbar">
  <input id="search" placeholder="🔍 搜索货号 / 产品线 / 应用场景关键词…" oninput="render()">
  <select id="catSel" onchange="render()" style="padding:9px 12px;border:1px solid var(--line);border-radius:8px;font-size:13px;">
    <option value="">全部品类</option>
  </select>
  <button onclick="exportJson()">⬇ 导出 JSON</button>
  <button onclick="resetEdit()">⟲ 重置编辑</button>
  <button class="primary" onclick="window.print()">🖨 打印 / 存 PDF</button>
</div>
<div class="hint">💡 <b>「应用场景」已写初稿</b>，点击可直接改；<b>「典型客户」列留空待你填写</b>（点进单元格直接输入，改动自动保存本地）。横线处是我不确定、需要你补充的部分。</div>
<div class="wrap"><table id="tbl">
<thead><tr><th>品类</th><th>产品线</th><th>代表货号</th><th>形态</th><th>应用场景（可编辑）</th><th>典型客户 / 案例（待填）</th></tr></thead>
<tbody></tbody>
</table></div>
<div class="toast" id="toast"></div>
<script>
var LINES = __DATA__;
var CATS = __CATS__;
var edited = JSON.parse(localStorage.getItem('br_sku_edit') || '{}');
LINES = LINES.map(function(d,i){
  if(edited[i]){ ['scene','cust'].forEach(function(k){ if(edited[i][k]!==undefined) d[k]=edited[i][k]; }); }
  return d;
});
function esc(s){return String(s).replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;');}
function render(){
  var q = (document.getElementById('search').value||'').trim().toLowerCase();
  var c = document.getElementById('catSel').value;
  var sel = document.getElementById('catSel');
  if(sel.options.length===1){ CATS.forEach(function(x){ var o=document.createElement('option'); o.value=x; o.textContent=x; sel.appendChild(o); }); }
  var html='';
  LINES.forEach(function(d,i){
    if(c && d.cat!==c) return;
    if(q && (d.name+d.item+d.scene+d.cat).toLowerCase().indexOf(q)===-1) return;
    html += '<tr data-i="'+i+'">'
      + '<td class="cat">'+esc(d.cat)+'</td>'
      + '<td class="name">'+esc(d.name)+'</td>'
      + '<td class="item">'+esc(d.item)+'</td>'
      + '<td class="form">'+esc(d.form)+'</td>'
      + '<td class="scene" contenteditable="true" data-k="scene" oninput="markEdit(this)">'+esc(d.scene)+'</td>'
      + '<td class="cust" contenteditable="true" data-k="cust" oninput="markEdit(this)">'+esc(d.cust||'')+'</td>'
      + '</tr>';
  });
  document.querySelector('#tbl tbody').innerHTML = html || '<tr><td colspan="6" style="text-align:center;color:#9e9e9e;padding:40px;">没有匹配的货号</td></tr>';
}
var saveTimer;
function markEdit(el){
  var tr = el.closest('tr');
  var i = tr.getAttribute('data-i');
  var k = el.getAttribute('data-k');
  var v = el.innerText;
  var e = JSON.parse(localStorage.getItem('br_sku_edit')||'{}');
  e[i]=e[i]||{}; e[i][k]=v;
  localStorage.setItem('br_sku_edit', JSON.stringify(e));
  clearTimeout(saveTimer); saveTimer=setTimeout(function(){showToast('已自动保存 ✓');},800);
}
function exportJson(){
  var out = LINES.map(function(d){return {cat:d.cat,name:d.name,item:d.item,form:d.form,scene:d.scene,cust:d.cust};});
  var blob=new Blob([JSON.stringify(out,null,2)],{type:'application/json'});
  var a=document.createElement('a'); a.href=URL.createObjectURL(blob); a.download='货号应用场景_编辑版.json'; a.click();
  showToast('已导出 JSON');
}
function resetEdit(){ localStorage.removeItem('br_sku_edit'); location.reload(); }
function showToast(m){ var t=document.getElementById('toast'); t.textContent=m; t.classList.add('show'); setTimeout(function(){t.classList.remove('show');},1500); }
render();
</script>
</body>
</html>'''
html = html.replace('__DATA__', data_json).replace('__CATS__', json.dumps(CATS, ensure_ascii=False))
html_path = os.path.join(BASE, '02_货号应用场景速查表.html')
open(html_path, 'w', encoding='utf-8').write(html)
print('HTML 生成:', html_path, f'({len(html)/1024:.0f} KB)')

# ============ 生成 Excel ============
import pandas as pd
df = pd.DataFrame(LINES)
df = df[['cat','name','item','form','scene','cust']]
df.columns = ['品类','产品线','代表货号','形态','应用场景','典型客户（待填）']
excel_path = os.path.join(BASE, '02_货号应用场景速查表.xlsx')
with pd.ExcelWriter(excel_path, engine='openpyxl') as w:
    df.to_excel(w, sheet_name='核心货号', index=False)
    # 调整列宽
    ws = w.sheets['核心货号']
    widths = {'A':12,'B':26,'C':28,'D':14,'E':52,'F':22}
    for col, wd in widths.items():
        ws.column_dimensions[col].width = wd
print('Excel 生成:', excel_path)