#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""竞品大表 → Markdown 转写 + 量化分析

输入：生命科学产品线-竞品大表-V1.0.xlsx（2 Sheet，11 字段，一行一条竞品对标）
输出：① Markdown 转写（保留全部原文，合并单元格补全）
      ② 量化分析（厂家频次/价格情报质量/数据缺口）—— 全部现算，不估算

用法：python3 competitor_table_to_md.py [xlsx] [--md-out 路径]
"""
import sys, os, re, io, json, datetime
import openpyxl

DEFAULT_XLSX = os.path.expanduser(
    '~/.hermes/profiles/zone-b/cache/documents/'
    'doc_122874ef1080_生命科学产品线-竞品大表-V1.0 2020609.xlsx')
OUT_MD = os.path.expanduser(
    '~/Desktop/Hermes输出-工作类/资料/生命科学产品线-竞品大表-V1.0.md')

COLS = ['关键步骤', '具体操作', '区域', '货号', '产品名称',
        '对标竞品', '竞品到手价', '竞品核心优势/宣传点',
        '客户反馈（竞品）', '竞争策略', '备注']

# ── 厂家归一化表（把同一家的各种写法收敛成一个检索键）──
VENDOR_MAP = [
    ('诺唯赞', '诺唯赞'), ('vazyme', '诺唯赞'),
    ('翌圣', '翌圣'), ('yeasen', '翌圣'), ('翊圣', '翌圣'),
    ('艾科瑞', '艾科瑞'), ('accurate', '艾科瑞'),
    ('全式金', '全式金'), ('transgen', '全式金'),
    ('爱博泰克', '爱博泰克'), ('abclonal', '爱博泰克'),
    ('康润', '康润(GenStar)'), ('genstar', '康润(GenStar)'),
    ('赛默飞', '赛默飞(Thermo)'), ('thermo', '赛默飞(Thermo)'), ('invitrogen', '赛默飞(Thermo)'),
    ('碧云天', '碧云天'), ('beyotime', '碧云天'),
    ('新赛美', '新赛美'), ('雅酶', '雅酶'), ('epizyme', '雅酶'),
    ('塞维尔', '塞维尔'), ('servicebio', '塞维尔'),
    ('康为', '康为(世纪)'), ('cwbio', '康为(世纪)'),
    ('白鲨', '白鲨(Biosharp)'), ('biosharp', '白鲨(Biosharp)'),
    ('天根', '天根(TIANGEN)'), ('tiangen', '天根(TIANGEN)'),
    ('takara', 'Takara'), ('toyobo', 'Toyobo'),
    ('qiagen', 'QIAGEN'), ('凯杰', 'QIAGEN'),
    ('promega', 'Promega'), ('zymo', 'Zymo'), ('abm', 'ABM'),
    ('默克', '默克(Merck)'), ('merck', '默克(Merck)'),
    ('近岸', '近岸蛋白'), ('novoprotein', '近岸蛋白'),
    ('亚科因', '亚科因(Abbkine)'), ('abbkine', '亚科因(Abbkine)'),
    ('吐露港', '吐露港'), ('索莱宝', '索莱宝'), ('弗德', '弗德'),
    ('金沙', '金沙生物'), ('积福', '积福生物'), ('美基', '美基生物'),
    ('酷莱博', '酷莱博'), ('易思德', '易思德'), ('ag', '艾科瑞'),
]


KNOWN = {c for _, c in VENDOR_MAP}


def norm_vendor(s):
    """从「厂家-货号（品名）」里抽厂家并归一化。抽出什么算什么，抽不出返回原前缀。

    ⚠️ 陷阱：一个单元格里可能列同一厂家的多个货号（如碧云天「P1005-xxx ⏎ P1045-yyy」），
    按换行拆分后第二行会被当成新厂家「P1045」。所以**只有能归一化到 KNOWN 的片段才算竞品**，
    否则退回整格判定（见 analyze 的 split_vendors）。
    """
    if not s or not s.strip():
        return ''
    head = re.split(r'[-—]', s.strip())[0]
    head = head.split('（')[0].split('(')[0].strip()
    low = s.lower()
    for key, canon in VENDOR_MAP:
        if key in low or key in head:
            return canon
    return head or s.strip()[:12]


def split_vendors(cell):
    """把一个单元格拆成竞品厂家列表；拆不出已知厂家的片段一律丢弃（防假厂家）。"""
    if not cell or not cell.strip():
        return []
    whole = norm_vendor(cell)
    if whole in KNOWN:
        # 整格只认已知厂家时，仍需展开同一单元格内的多家（如「诺唯赞-Q711 ⏎ 诺唯赞-Q312」→ 1 家）
        out = []
        for frag in re.split(r'[\n⏎]+', cell):
            if frag.strip():
                c = norm_vendor(frag)
                if c in KNOWN:
                    out.append(c)
        return out or [whole]
    out = []
    for frag in re.split(r'[\n⏎]+', cell):
        if frag.strip():
            c = norm_vendor(frag)
            if c in KNOWN:
                out.append(c)
    return out


# ── 价格解析：只认能确定单位的，模糊的一律不猜 ──
def parse_price(txt):
    """返回 (每ml单价 or None, 每T单价 or None, 情报等级, 说明)

    情报等级：实际 / 非实际 / 暂无 / 空
    铁律：单位不明确的**不硬算**，返回 None 并注明原因，绝不估算。
    """
    if not txt or not txt.strip():
        return None, None, '空', ''
    t = txt.strip()
    if '暂无' in t and len(t) <= 6:
        return None, None, '暂无', ''
    grade = '非实际' if t.startswith('非实际') else ('实际' if t.startswith('实际') else '其他')
    body = re.sub(r'^(实际价格|非实际价格|实际|非实际)\s*[:：]?\s*', '', t)
    ml = t_ = None
    # 形态1：X元一支（Yml/支）
    m = re.search(r'([\d,.]+)\s*元\s*[一1]\s*[支瓶管]\s*[（(]\s*([\d.]+)\s*m[lL]\s*/\s*[支瓶管]', body)
    if m:
        try: ml = float(m.group(1).replace(',', '')) / float(m.group(2))
        except ZeroDivisionError: pass
    # 形态2：a) 1ml/10元   b) 100ml/248元
    if ml is None:
        m = re.search(r'([\d.]+)\s*m[lL]\s*/\s*([\d,.]+)\s*元', body)
        if m:
            try: ml = float(m.group(2).replace(',', '')) / float(m.group(1))
            except ZeroDivisionError: pass
    # 形态3：XT[或 X rxns]/Y元 → 元/T（只在单位是 T/rxn 时）
    if ml is None:
        m = re.search(r'([\d,.]+)\s*(?:T|rxns?|rxn|反应)\b[^/]{0,24}/\s*([\d,.]+)\s*元', body)
        if m:
            try: t_ = float(m.group(2).replace(',', '')) / float(m.group(1).replace(',', ''))
            except ZeroDivisionError: pass
    note = '' if (ml or t_) else '单位不明确，未归一'
    return ml, t_, grade, note


def load(path):
    wb = openpyxl.load_workbook(path, data_only=True)
    out = []
    for ws in wb.worksheets:
        # 先把合并区补全（合并区只有左上角有值）
        grid = [[ws.cell(row=r, column=c).value for c in range(1, 12)]
                for r in range(1, ws.max_row + 1)]
        for rng in ws.merged_cells.ranges:
            v = ws.cell(row=rng.min_row, column=rng.min_col).value
            for r in range(rng.min_row, rng.max_row + 1):
                for c in range(rng.min_col, rng.max_col + 1):
                    grid[r - 1][c - 1] = v
        rows = []
        for i, g in enumerate(grid[1:], start=2):     # 跳过表头
            vals = [('' if v is None else str(v).strip()) for v in g]
            if any(vals):
                rows.append((i, vals))
        out.append((ws.title, rows, grid[0]))
    return out


def md_cell(s):
    return (s or '').replace('|', '\\|').replace('\n', '<br>').strip()


def write_md(sheets, path):
    L = []
    L.append('# 生命科学产品线 · 竞品大表 V1.0')
    L.append('')
    L.append('> **来源**：`生命科学产品线-竞品大表-V1.0 2020609.xlsx`（V1.0，2026-06-09 版）')
    L.append('> **转写**：原文逐字保留（含错别字、未填写的空单元格），仅做表格化排版；合并单元格已补全。')
    L.append('> **生成**：%s ｜ 生成器 `脚本/competitor_table_to_md.py`' %
             datetime.datetime.now().strftime('%Y-%m-%d %H:%M'))
    L.append('')
    L.append('## 字段口径（原表表头全文，未删改）')
    L.append('')
    for i, c in enumerate(COLS):
        raw = ''
        for sh, _, hdr in sheets:
            if i < len(hdr) and hdr[i]:
                raw = str(hdr[i]).strip()
                break
        L.append('%d. **%s**：%s' % (i + 1, c, raw.replace('\n', '  ')))
    L.append('')
    for sh, rows, _ in sheets:
        L.append('## %s（%d 条）' % (sh, len(rows)))
        L.append('')
        L.append('| ' + ' | '.join(COLS) + ' |')
        L.append('|' + '---|' * len(COLS))
        for rno, vals in rows:
            L.append('| ' + ' | '.join(md_cell(v) for v in vals) + ' |')
        L.append('')
    io.open(path, 'w', encoding='utf-8').write('\n'.join(L))
    return '\n'.join(L)


def analyze(sheets):
    res = {'sheets': {}, 'vendors': {}, 'steps': {}, 'verdict_rows': 0,
           'price_norm': [], 'issues': []}
    all_rows = []
    for sh, rows, _ in sheets:
        res['sheets'][sh] = len(rows)
        all_rows += [(sh, r, v) for r, v in rows]
    res['rows_total'] = len(all_rows)

    for sh, rno, v in all_rows:
        step, vname, sku, disp, price, adv, fb, strat, rmk = (
            v[0], v[5], v[3], v[4], v[6], v[7], v[8], v[9], v[10])
        if step:
            res['steps'][step] = res['steps'].get(step, 0) + 1
        for c in split_vendors(vname):
            res['vendors'][c] = res['vendors'].get(c, 0) + 1
        ml, t_, grade, note = parse_price(price)
        res['price_norm'].append(dict(sheet=sh, row=rno, sku=sku, disp=disp,
                                      vendor=vname.strip()[:60], price=price,
                                      grade=grade, yml=ml, yT=t_, note=note))
        if fb.strip():
            res['verdict_rows'] += 1
        # 数据缺口
        if not disp.strip():
            res['issues'].append((sh, rno, '缺宝锐货号'))
        if not sku.strip():
            res['issues'].append((sh, rno, '缺宝锐产品名称'))
        if not vname.strip():
            res['issues'].append((sh, rno, '缺对标竞品'))
        if '货号异常' in adv or '货号有问题' in adv or '货号异' in adv:
            res['issues'].append((sh, rno, '竞品货号存疑'))
        if re.search(r'^[?？]', (disp or '').strip()) or (disp or '').strip() in ('?', '？'):
            res['issues'].append((sh, rno, '宝锐货号/名称待补(?)'))
    return res


if __name__ == '__main__':
    path = sys.argv[1] if len(sys.argv) > 1 and not sys.argv[1].startswith('--') else DEFAULT_XLSX
    out = OUT_MD
    if '--md-out' in sys.argv:
        out = sys.argv[sys.argv.index('--md-out') + 1]
    sheets = load(path)
    txt = write_md(sheets, out)
    print('  ✅ Markdown 已写出: %s  (%d 行 / %d 字符)' % (out, txt.count('\n') + 1, len(txt)))
    a = analyze(sheets)
    print('\n  ════ Sheet 分布 ════')
    for k, n in a['sheets'].items():
        print('  • %-10s %d 条' % (k, n))
    print('  合计 %d 条竞品对标记录' % a['rows_total'])
    print('\n  ════ 关键步骤分布 ════')
    for k, n in sorted(a['steps'].items(), key=lambda x: -x[1]):
        print('  • %-26s %d' % (k, n))
    print('\n  ════ 竞品厂家频次（TOP15）════')
    for k, n in sorted(a['vendors'].items(), key=lambda x: -x[1])[:15]:
        print('  • %-18s %d' % (k, n))
    print('  厂家去重后共 %d 家' % len(a['vendors']))
    g = {}
    for p in a['price_norm']:
        g[p['grade']] = g.get(p['grade'], 0) + 1
    print('\n  ════ 价格情报质量 ════')
    for k in ('实际', '非实际', '其他', '暂无', '空'):
        if k in g:
            print('  • %-5s %3d 条 (%.1f%%)' % (k, g[k], g[k] * 100.0 / a['rows_total']))
    nm = sum(1 for p in a['price_norm'] if p['yml'] or p['yT'])
    print('  可归一化出单价: %d/%d (%.1f%%)  不可归一: %d'
          % (nm, a['rows_total'], nm * 100.0 / a['rows_total'], a['rows_total'] - nm))
    print('\n  ════ 数据缺口 ════')
    ig = {}
    for sh, r, what in a['issues']:
        ig[what] = ig.get(what, 0) + 1
    for k, n in sorted(ig.items(), key=lambda x: -x[1]):
        print('  • %-22s %d 处' % (k, n))
    print('  • 有客户真实反馈的行: %d/%d (%.1f%%)'
          % (a['verdict_rows'], a['rows_total'], a['verdict_rows'] * 100.0 / a['rows_total']))
    io.open('/tmp/comp_analysis.json', 'w', encoding='utf-8').write(
        json.dumps(a, ensure_ascii=False, indent=1))
    print('\n  （明细已存 /tmp/comp_analysis.json）')