# -*- coding: utf-8 -*-
"""Build ATP four-way comparison matrix XLSX (internal)."""
import json, os
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

BASE = os.path.expanduser('~/Desktop/Hermes输出-工作类')
D = json.load(open(os.path.join(BASE, '数据/atp_fourway_comparison_20261106.json'), encoding='utf-8'))
OUT = os.path.join(BASE, '产品与市场/调研报告/ATP荧光检测四方差_横向比较矩阵_20261106_V01.xlsx')

NAVY = '0F2B52'; BLUE = '1A56DB'; LIGHT = 'EEF4FF'; GREY = 'F4F7FB'; WHITE = 'FFFFFF'
thin = Side(style='thin', color='D9E2EF')
border = Border(left=thin, right=thin, top=thin, bottom=thin)

wb = openpyxl.Workbook()
ws = wb.active
ws.title = '总览'
ws.sheet_view.showGridLines = False

def title(ws, text, sub=None):
    ws.merge_cells('A1:F1')
    c = ws['A1']; c.value = text
    c.font = Font(name='宋体', size=15, bold=True, color=WHITE)
    c.fill = PatternFill('solid', fgColor=NAVY)
    c.alignment = Alignment(horizontal='left', vertical='center')
    ws.row_dimensions[1].height = 34
    if sub:
        ws.merge_cells('A2:F2')
        c = ws['A2']; c.value = sub
        c.font = Font(name='宋体', size=9, color='5D6B80')
        c.fill = PatternFill('solid', fgColor=GREY)
        c.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
        ws.row_dimensions[2].height = 30

def header(ws, row, cols):
    for j, (name, width) in enumerate(cols, 1):
        c = ws.cell(row=row, column=j, value=name)
        c.font = Font(name='宋体', size=9, bold=True, color=WHITE)
        c.fill = PatternFill('solid', fgColor=NAVY)
        c.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
        c.border = border
        ws.column_dimensions[get_column_letter(j)].width = width
    ws.row_dimensions[row].height = 30

def body(ws, row, vals, alt=False, bold_first=False):
    for j, v in enumerate(vals, 1):
        c = ws.cell(row=row, column=j, value=v)
        c.font = Font(name='宋体', size=9, bold=(bold_first and j == 1))
        c.fill = PatternFill('solid', fgColor=(GREY if alt else WHITE))
        c.alignment = Alignment(horizontal='left', vertical='top', wrap_text=True)
        c.border = border
    ws.row_dimensions[row].height = 44

title(ws, 'ATP 荧光检测四方差：横向比较矩阵', '易瑞 / 天隆 / 宝锐 / 克拉玛尔 ｜ 内部研究稿 V01 ｜ 2026-11-06 ｜ 含客户数据，禁止外发')
header(ws, 4, [('维度', 14), ('宝锐', 30), ('克拉玛尔', 30), ('天隆', 36), ('易瑞', 30), ('证据强度', 14)])
rows = [
    ('角色', '荧光素酶原料供应商', '生化试剂目录供应商', 'ATP 仪器 + Quickswab 拭子厂家', '食品快检上市公司；天隆 Biolum 海外代理', '—'),
    ('可比较产品', 'E800-020714；1 mg/mL；甘油液体/冻干粉', '13081130021；冻干粉；SDS-PAGE ≥98%', 'Biolum / Plus / Pro；Quickswab A010–A036', 'ATP 检测试剂（餐具/环境洁净度）；仪器未公开', '—'),
    ('检测限 LOD', '0.1 pmol ATP/管（3σ，96 孔板）', '未披露', '营销 1×10⁻¹⁶；正文 1×10⁻¹⁵；AOAC 0.9 fmol', '未披露', '● AOAC / ◐ 宣称'),
    ('精密度', '未披露 CV', '未披露', '100 fmol：5.65%/10.24%；1 fmol：21.52%/75.18%', '未披露', '● AOAC'),
    ('线性/量程', '0.1–200 pmol；R²≥0.999', '未披露', '0–999,999 RLU；Pro R²≥0.99', '未披露', '◐ 宣称'),
    ('稳定性', '4℃ 90%、25℃ 77%、37℃ 74%（7 天）；客户 73%', '无公开；易瑞体系 37℃ 7 天 40%', '拭子/试剂稳定性未公开', '要求 7 天下降≤15%、11 天≤25%', '◐ 单次/客户'),
    ('仪器', '无；96 孔板/手持式', '无', 'Biolum 280g/3.5"/10s；Pro 420g/5"/10s/云端', '未公开；天隆海外代理', '● 公开'),
    ('检测体系', 'D-Luciferin + ATP/Mg²⁺；可定制 buffer', '仅酶粉；客户自配', '一体化液态稳定拭子；试剂开放、建议配套拭子', '客户自有 buffer；16 µL/test；200 nmol 标液', '◐ 客户'),
    ('认证/质量', 'ATP 系统认证未披露；COA 待补', '未披露 COA/批间', 'AOAC 022401；NMPA/FDA/CE 宣称', '上市公司；食品快检体系', '● / ○'),
    ('价格（元/mg）', '16.0 / 14.4 / 12.96（100mg/500mg/1g）', '2,708（1mg）/1,084.6（5mg）/16.96（100mg 内部）', '整机 1.8–3.0 万元/台；拭子未公开', '易瑞首单 100 元/mg（小批量项目价）', '◐ 价差大'),
    ('数据完整度', '2/5', '2/5', '4/5', '1/5', '—'),
]
for i, r in enumerate(rows, 5):
    body(ws, i, r, alt=(i % 2 == 0), bold_first=True)

# Sheet 2: LOD + precision
ws2 = wb.create_sheet('检测限与精密度'); ws2.sheet_view.showGridLines = False
title(ws2, '检测限与精密度：先把单位换算清楚', '天隆营销 1×10⁻¹⁶ / 正文 1×10⁻¹⁵ / AOAC 0.9 fmol；宝锐 0.1 pmol')
header(ws2, 4, [('口径', 34), ('mol', 12), ('pmol', 12), ('fmol', 12), ('宝锐 0.1 pmol 的倍数', 18), ('说明', 40)])
for i, x in enumerate(D['lod'], 5):
    ratio = 100.0 / x['fmol']
    body(ws2, i, (x['label'], x['mol'], x['pmol'], x['fmol'], f'{ratio:,.1f}×', x['note']), alt=(i % 2 == 0))
ws2.cell(row=10, column=1, value='AOAC 022401 纯分析物精密度（n=10）').font = Font(name='宋体', size=10, bold=True, color=NAVY)
header(ws2, 11, [('ATP（fmol/assay）', 20), ('开发者 RSD', 18), ('独立实验室 RSD', 20), ('判读', 50)])
for i, lv in enumerate(D['precision']['levels_fmol'], 12):
    dev = D['precision']['developer_rsd'][i-12]; ind = D['precision']['independent_rsd'][i-12]
    note = '低端，检测到但定量不稳' if lv <= 5 else ('0.1 pmol；可定量区间' if lv == 100 else '')
    body(ws2, i, (lv, f'{dev:.2f}%', f'{ind:.2f}%', note), alt=(i % 2 == 0))

# Sheet 3: instrument & system
ws3 = wb.create_sheet('仪器与体系'); ws3.sheet_view.showGridLines = False
title(ws3, '仪器与检测体系：天隆是唯一有整机的一家', '公开参数与 AOAC 验证；易瑞仪器型号未公开')
header(ws3, 4, [('项目', 18), ('Biolum', 42), ('Biolum Pro', 42), ('说明', 26)])
instrument = [
    ('重量', '280 g（含电池）', '420 g', '公开参数'),
    ('显示', '3.5 寸彩屏', '5 寸高清触摸屏', '公开参数'),
    ('检测时间', '10 秒/样本', '10 秒/样本', '公开参数'),
    ('读数范围', '0–999,999 RLU', '0–999,999 RLU', '公开参数'),
    ('检测限/灵敏度', '参数表 1×10⁻¹⁶；正文 1×10⁻¹⁵；AOAC 0.9 fmol', '1×10⁻¹⁶ mol 标准品稳定检出', 'AOAC 为准'),
    ('精密度', 'AOAC 100 fmol：5.65%/10.24%', '1×10⁻¹³ mol CV<6%', '公开/AOAC'),
    ('数据/通讯', '999 条；USB/蓝牙', '10,000 条；USB/云端', '公开参数'),
    ('认证', 'AOAC 022401；NMPA/FDA/CE 宣称', 'AOAC/CE 宣称', '证书有效期需核'),
]
for i, r in enumerate(instrument, 5):
    body(ws3, i, r, alt=(i % 2 == 0), bold_first=True)

# Sheet 4: Yirui case
ws4 = wb.create_sheet('易瑞案例'); ws4.sheet_view.showGridLines = False
title(ws4, '易瑞案例：稳定性 + 价格的胜出，不是 LOD 的胜出', '客户口径；含价格，禁止外发')
header(ws4, 4, [('日期', 16), ('事件/数据', 70), ('口径', 20)])
for i, x in enumerate(D['case']['timeline'], 5):
    body(ws4, i, (x['date'], x['event'], '内部客户记录'), alt=(i % 2 == 0), bold_first=True)
ws4.merge_cells('A10:C10')
ws4['A10'] = '结论：' + D['case']['win'] + '  ' + D['case']['not_yet']
ws4['A10'].font = Font(name='宋体', size=9, bold=True, color='B91C1C')
ws4['A10'].alignment = Alignment(wrap_text=True, vertical='top')
ws4.row_dimensions[10].height = 60

# Sheet 5: 90-day
ws5 = wb.create_sheet('90天行动'); ws5.sheet_view.showGridLines = False
title(ws5, '90 天行动清单：先拿数据，再谈商务', '主责唯一；验收口径写死')
header(ws5, 4, [('时间', 10), ('动作', 60), ('主责', 10), ('协同', 22), ('验收/输出', 28)])
actions = [
    ('D0–D14', '签三方 NDA；拿 Quickswab/仪器/标液；书面确认天隆 LOD 口径', '刘新元', '王新旺、彭洪富、李红东', 'NDA、测试方法、样品清单'),
    ('D15–D30', '同体系 ATP 标准曲线（0/0.9/1/5/25/100/500/1500 fmol；空白 n≥10）', '王新旺', '研发、质量', 'LOD/LOQ 报告'),
    ('D15–D45', '37℃ 7/11 天、25℃、4℃ 稳定性；客户 buffer/Quickswab 提取液', '研发', '质量、王新旺', '稳定性报告'),
    ('D31–D45', 'Quickswab 兼容性：回收率、基质、批间 CV；与 Klamar 平行', '王新旺', '天隆试剂研发', '回收率 80–120%；CV≤15%'),
    ('D46–D60', '配方优化：酶量、buffer、luciferin、液体/冻干', '研发', '王新旺、质量', 'LOD≤1 fmol；7 天下降≤15%'),
    ('D46–D60', '易瑞数据授权：三方 NDA 或匿名化；确认仪器/buffer', '陈溪子', '刘新元、易瑞', '书面授权/匿名案例'),
    ('D61–D75', '20/100 mg 小批量；批特异 COA、批间、出口资料', '生产/质量', '彭洪富', 'COA、批间 CV、样品'),
    ('D61–D90', '商务方案：双源、3–5 g/年参照、12.96–16 元/mg', '刘新元', '彭洪富、李红东', '试点订单/年度框架'),
    ('D90', '决策评审：继续 LOD 对标或转成本/稳定性第二来源', '刘新元', '全体', 'Go/No-Go 纪要'),
]
for i, r in enumerate(actions, 5):
    body(ws5, i, r, alt=(i % 2 == 0), bold_first=True)

# Sheet 6: sources
ws6 = wb.create_sheet('来源'); ws6.sheet_view.showGridLines = False
title(ws6, '来源清单', '公开资料 + 内部客户记录；报告内引用编号对应')
header(ws6, 4, [('编号', 8), ('来源', 34), ('内容', 60), ('链接/路径', 55)])
for i, s in enumerate(D['sources'], 5):
    body(ws6, i, (s['id'], s['name'], s['detail'], s['url']), alt=(i % 2 == 0), bold_first=True)
    ws6.row_dimensions[i].height = 30

# freeze panes and print settings
for sh in [ws, ws2, ws3, ws4, ws5, ws6]:
    sh.freeze_panes = 'A5'
    sh.sheet_properties.pageSetUpPr.fitToPage = True
    sh.page_setup.fitToWidth = 1
    sh.page_setup.fitToHeight = 0
    sh.page_setup.orientation = 'landscape'
    sh.page_margins.left = 0.3; sh.page_margins.right = 0.3
    sh.page_margins.top = 0.5; sh.page_margins.bottom = 0.5

os.makedirs(os.path.dirname(OUT), exist_ok=True)
wb.save(OUT)
print('WROTE', OUT, os.path.getsize(OUT))