#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""为指定货号生成 性能优势/应用场景/应用客户 明细 Excel"""
import json, os, glob
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

BASE = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SKUS = ["2FM101-1-2", "2FMD021-000711-TP", "2FMD021-000711-TP-2",
        "BR1A401-10", "BR1A401-11", "BR1A401-12", "BR1A401-13",
        "BR1A501-02", "BR1A501-03"]

# 1) 货号清单基础字段
catalog = {}
with open(f'{BASE}/刘子研上手包/数据/核酸扩增原料货号清单.json', encoding='utf-8') as f:
    for it in json.load(f):
        if it.get('货号') in SKUS:
            catalog[it['货号']] = it

# 2) 扫描 pricing 客户
cust_map = {s: [] for s in SKUS}
for fp in glob.glob(f'{BASE}/数据/pricing/*.json'):
    name = os.path.basename(fp)
    if name.startswith('_'):
        continue
    try:
        with open(fp, encoding='utf-8') as f:
            data = json.load(f)
    except Exception:
        continue
    for p in data.get('products', []):
        sku = p.get('sku')
        if sku in cust_map:
            cust_map[sku].append({
                '客户': data.get('customer', ''),
                '数量': p.get('total_qty', ''),
                '金额': p.get('total_amt', ''),
                '单价': p.get('latest_price', ''),
                '赠样': '是' if p.get('is_gift') else '否',
                '年份': p.get('years', ''),
            })

# 3) 性能优势 / 应用场景（人工整理）
perf = {
 "2FM101-1-2": "Tth 酶体系配套 8×缓冲液：Tth 聚合酶耐高温、兼具逆转录活性（有 Mn2+ 时），DNA/RNA 双靶标一酶通吃；本缓冲液含 dUTP 支持 UNG 防污染、Mn2+ free 配方，客户可按需加 Mn2+ 切换 DNA/RNA 扩增。",
 "2FMD021-000711-TP": "HBV 一步法冻干直扩预混液（Probe qPCR + UNG 防污染）：直扩免提取、支持冻干剂型，DG 定制级，为乙肝核酸检测试剂盒定制体系。",
 "2FMD021-000711-TP-2": "配套 10×缓冲液（Mg2+ free）：供客户自调 Mg2+ 浓度做体系优化，配合 000711 直扩体系使用。",
 "BR1A401-10": "HandyAmp 快速 PCR 预混液（Dye plus）：延伸速度 1sec/kb（≥1kb/10s），30min 完成 DNA 扩增，错误率≤2×10⁻⁵；对标 Takara SpeedSTAR HS（~10sec/kb）性能不输、价格 1/3~1/5，且支持可冻干（Takara 不具备）；含染料可直接电泳。",
 "BR1A401-11": "HandyAmp 快速 PCR 预混液（Dye plus）：延伸速度 1sec/kb（≥1kb/10s），30min 完成 DNA 扩增，错误率≤2×10⁻⁵；对标 Takara SpeedSTAR HS（~10sec/kb）性能不输、价格 1/3~1/5，且支持可冻干；含染料可直接电泳。",
 "BR1A401-12": "HandyAmp 快速 PCR 预混液（Dye plus）：延伸速度 1sec/kb（≥1kb/10s），30min 完成 DNA 扩增，错误率≤2×10⁻⁵；对标 Takara SpeedSTAR HS（~10sec/kb）性能不输、价格 1/3~1/5，且支持可冻干；含染料可直接电泳。",
 "BR1A401-13": "HandyAmp 快速 PCR 预混液（Dye plus）：延伸速度 1sec/kb（≥1kb/10s），30min 完成 DNA 扩增，错误率≤2×10⁻⁵；对标 Takara SpeedSTAR HS（~10sec/kb）性能不输、价格 1/3~1/5，且支持可冻干；含染料可直接电泳。",
 "BR1A501-02": "Robustart SYBR 染料法 qPCR 预混液（Universal ROX）：热启动 Taq 抗抑制、特异高；Universal ROX 校正，适配高/低 ROX 全系主流 qPCR 仪，科研定量检测通用。",
 "BR1A501-03": "Robustart SYBR 染料法 qPCR 预混液（Universal ROX）：热启动 Taq 抗抑制、特异高；Universal ROX 校正，适配高/低 ROX 全系主流 qPCR 仪，科研定量检测通用。",
}
scene = {
 "2FM101-1-2": "DNA/RNA 双靶标一步法检测；耐高温特殊体系定制；需 UNG 防污染的试剂盒体系（诊断原料级）。",
 "2FMD021-000711-TP": "乙肝病毒（HBV）核酸检测试剂盒——一步法探针 qPCR，直扩+可冻干。",
 "2FMD021-000711-TP-2": "配合 000711 直扩体系的缓冲液定制（Mg2+ 自调）。",
 "BR1A401-10": "快速 PCR / 基因扩增；分子克隆、菌落 PCR、快速检测；科研与诊断快速扩增场景。",
 "BR1A401-11": "快速 PCR / 基因扩增；分子克隆、菌落 PCR、快速检测；科研与诊断快速扩增场景。",
 "BR1A401-12": "快速 PCR / 基因扩增；分子克隆、菌落 PCR、快速检测；科研与诊断快速扩增场景。",
 "BR1A401-13": "快速 PCR / 基因扩增；分子克隆、菌落 PCR、快速检测；科研与诊断快速扩增场景。",
 "BR1A501-02": "基因表达定量 qPCR（SYBR 染料法）；科研定量检测（高校/科研院所）。",
 "BR1A501-03": "基因表达定量 qPCR（SYBR 染料法）；科研定量检测（高校/科研院所）。",
}

# 客诉备注
complaint = {
 "2FM101-1-2": "客诉：2026-03 丽珠 外包装袋贴错标签（同系列 2FM101-1-1 曾颜色变黄、分批次发货）。",
 "2FMD021-000711-TP": "",
 "2FMD021-000711-TP-2": "",
 "BR1A401-10": "客诉：2026-06 温州医科大 -10 试管破裂；浙农林 多条带杂带；华南农大陈瑞爱课题组客诉。",
 "BR1A401-11": "",
 "BR1A401-12": "",
 "BR1A401-13": "",
 "BR1A501-02": "",
 "BR1A501-03": "",
}

# 4) 生成 Excel
wb = Workbook()
ws = wb.active
ws.title = '货号明细'

headers = ['货号', '产品名', '品类', '单位', '性能优势', '应用项目/场景',
           '应用客户', '2026订单数', '2026客户数', '2026价税合计(元)', '含税单价主价(元)', '客诉/风险备注']
ws.append(headers)

thin = Side(style='thin', color='D0D0D0')
border = Border(left=thin, right=thin, top=thin, bottom=thin)
head_fill = PatternFill('solid', fgColor='1852B6')
head_font = Font(color='FFFFFF', bold=True, size=10)
body_font = Font(size=9)
wrap = Alignment(vertical='top', wrap_text=True)
center = Alignment(vertical='top', horizontal='center', wrap_text=True)

for c in ws[1]:
    c.fill = head_fill; c.font = head_font
    c.alignment = Alignment(vertical='center', horizontal='center', wrap_text=True)
    c.border = border

for sku in SKUS:
    it = catalog.get(sku, {})
    # 客户串
    custs = cust_map.get(sku, [])
    cust_lines = []
    for cu in custs:
        qty = cu['数量']; amt = cu['金额']
        seg = f"{cu['客户']}"
        if amt not in ('', 0, None) or qty not in ('', 0, None):
            seg += f"（{qty}{it.get('单位','包')}/{amt}元/赠样:{cu['赠样']}/{cu['年份']}）"
        else:
            seg += f"（赠样/{cu['年份']}）"
        cust_lines.append(seg)
    cust_str = '；'.join(cust_lines) if cust_lines else '暂无客户明细'
    price = it.get('含税单价主价')
    price_str = '' if price is None else price
    row = [sku, it.get('产品名', ''), it.get('品类', ''), it.get('单位', ''),
           perf.get(sku, ''), scene.get(sku, ''),
           cust_str, it.get('订单数', ''), it.get('客户数', ''),
           it.get('2026价税合计', ''), price_str, complaint.get(sku, '')]
    ws.append(row)
    r = ws.max_row
    for i, cell in enumerate(ws[r], 1):
        cell.border = border; cell.font = body_font
        if i in (4, 8, 9, 10, 11):
            cell.alignment = center
        else:
            cell.alignment = wrap

widths = [18, 40, 12, 9, 46, 34, 46, 9, 9, 13, 13, 26]
for i, w in enumerate(widths, 1):
    ws.column_dimensions[chr(64+i)].width = w
ws.freeze_panes = 'A2'

out = f'{BASE}/刘子研上手包/货号性能优势与应用明细.xlsx'
wb.save(out)
print('OK:', out)
for s in SKUS:
    print(f"  {s}: 客户{len(cust_map[s])}个 / {catalog.get(s,{}).get('订单数','?')}单")