#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""竞品大表 · 价格基准分析（在 competitor_table_to_md.py 之后跑）

把「竞品到手价」按单位归一到 元/T 或 元/ml，输出可比的价差；
并检测重复行 / 我方价格缺口 / 价格异常。
铁律：单位不明确的一律不猜，进「不可比」清单。
"""
import os, sys, re, json, io
import openpyxl
from collections import defaultdict

XL = os.path.expanduser(
    '~/.hermes/profiles/zone-b/cache/documents/'
    'doc_122874ef1080_生命科学产品线-竞品大表-V1.0 2020609.xlsx')
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
from competitor_table_to_md import load, norm_vendor


def parse2(txt):
    """返回 dict(yT=, yml=, basis=, grade=, raw=)  —— 含 T→ml 体积换算。"""
    if not txt or not txt.strip():
        return dict(yT=None, yml=None, basis='', grade='空', raw='')
    t = txt.strip()
    if t.startswith('暂无') and len(t) <= 6:
        return dict(yT=None, yml=None, basis='', grade='暂无', raw=t)
    grade = '非实际' if t.startswith('非实际') else ('实际' if t.startswith('实际') else '其他')
    body = re.sub(r'^(实际价格|非实际价格|实际|非实际)\s*[:：]?\s*', '', t)
    yT = yml = None; basis = ''
    # A) X元一支（Yml/支）
    m = re.search(r'([\d,.]+)\s*元\s*[一1]\s*[支瓶管]\s*[（(]\s*([\d.]+)\s*m[lL]', body)
    if m:
        try:
            yml = float(m.group(1).replace(',', '')) / float(m.group(2)); basis = '元/支÷体积'
        except ZeroDivisionError: pass
    # B) Yml/X元
    if yml is None:
        m = re.search(r'([\d.]+)\s*m[lL]\s*/\s*([\d,.]+)\s*元', body)
        if m:
            try:
                yml = float(m.group(2).replace(',', '')) / float(m.group(1)); basis = '元/ml直读'
            except ZeroDivisionError: pass
    # C) XT(⏎Y μL)/Z元  —— 有每反应体积才能折算 ml
    if yT is None and yml is None:
        m = re.search(r'([\d,.]+)\s*(?:T|rxns?|rxn|反应)\s*[（(]\s*([\d.]+)\s*[μu]?[lL]\s*[）)]'
                      r'[^/]{0,12}/\s*([\d,.]+)\s*元', body)
        if m:
            try:
                n = float(m.group(1).replace(',', '')); vol = float(m.group(2))
                price = float(m.group(3).replace(',', ''))
                yT = price / n
                yml = price / (n * vol / 1000.0)
                basis = '元/T + 按%gμL/反应折ml' % vol
            except ZeroDivisionError: pass
    # D) XT/Z元（无体积）
    if yT is None and yml is None:
        m = re.search(r'([\d,.]+)\s*(?:T|rxns?|rxn|反应)\s*[^/]{0,20}/\s*([\d,.]+)\s*元', body)
        if m:
            try:
                yT = float(m.group(2).replace(',', '')) / float(m.group(1).replace(',', '')); basis = '元/T直读'
            except ZeroDivisionError: pass
    return dict(yT=yT, yml=yml, basis=basis, grade=grade, raw=t)


def main():
    sheets = load(XL)
    rows = []
    for sh, rs, _ in sheets:
        for rno, v in rs:
            rows.append(dict(sheet=sh, row=rno, step=v[0], op=v[1], region=v[2],
                             sku=v[3], prod=v[4], vendor_raw=v[5], price=v[6],
                             adv=v[7], fb=v[8], strat=v[9], rmk=v[10]))
    from competitor_table_to_md import split_vendors
    for r in rows:
        vs = split_vendors(r['vendor_raw'])
        r['vendor'] = vs[0] if vs else ''
        r.update(parse2(r['price']))

    print('  ════ ① 可归一化结果 ════')
    nt = sum(1 for r in rows if r['yT'])
    nm = sum(1 for r in rows if r['yml'])
    print('  元/T 可算: %d ｜ 元/ml 可算: %d ｜ 两者皆无: %d ／ 共 %d 条'
          % (nt, nm, sum(1 for r in rows if not r['yT'] and not r['yml']), len(rows)))

    print('\n  ════ ② 按「宝锐货号」看竞品价格带（同单位才比）════')
    by = defaultdict(list)
    for r in rows:
        if r['sku'].strip():
            by[(r['sheet'], r['sku'].strip(), r['prod'].strip())].append(r)
    multi = [(k, v) for k, v in by.items() if len([x for x in v if x['yT']]) >= 2]
    multi.sort(key=lambda kv: -len(kv[1]))
    for (sh, sku, prod), v in multi[:12]:
        pts = [(x['vendor'], x['yT'], x['grade']) for x in v if x['yT']]
        pts.sort(key=lambda p: p[1])
        lo, hi = pts[0], pts[-1]
        spread = hi[1] / lo[1] if lo[1] else 0
        print('  ── %s ｜ %s ｜ %s  （%d 家可比）' % (sh, sku, prod[:26], len(pts)))
        print('       最低 %-16s %.2f 元/T (%s)' % (lo[0], lo[1], lo[2]))
        print('       最高 %-16s %.2f 元/T (%s)   价差 %.1f×' % (hi[0], hi[1], hi[2], spread))

    print('\n  ════ ③ 重复行检测（同 Sheet 内字段全同）════')
    seen = {}
    dup = 0
    for r in rows:
        key = (r['sheet'], r['step'], r['op'], r['region'], r['sku'], r['vendor_raw'],
               r['price'], r['adv'], r['adv'])
        if key in seen:
            dup += 1
            print('  • 第%d行 与 第%d行 完全重复 ｜ %s ｜ %s' % (r['row'], seen[key], r['sku'], r['vendor_raw'][:44]))
        else:
            seen[key] = r['row']
    print('  重复行: %d' % dup)

    print('\n  ════ ④ 厂家梯队（按本表出现频次）════')
    INTERNATIONAL = ('赛默飞(Thermo)', 'Takara', 'Toyobo', 'QIAGEN', 'Promega', 'Zymo', 'ABM', '默克(Merck)')
    cnt = defaultdict(int)
    for r in rows:
        if r['vendor']: cnt[r['vendor']] += 1
    intl = {k: v for k, v in cnt.items() if k in INTERNATIONAL}
    dom = {k: v for k, v in cnt.items() if k not in INTERNATIONAL}
    print('  国际巨头 %d 家 / %d 条: %s' % (len(intl), sum(intl.values()),
          '、'.join('%s(%d)' % (k, v) for k, v in sorted(intl.items(), key=lambda x: -x[1]))))
    print('  国内厂商 %d 家 / %d 条: %s' % (len(dom), sum(dom.values()),
          '、'.join('%s(%d)' % (k, v) for k, v in sorted(dom.items(), key=lambda x: -x[1])[:12])))

    print('\n  ════ ⑤ 我方定价缺口 ════')
    has_our_price = [r for r in rows if re.search(r'宝锐|我们.*价', r['price'] or '')]
    print('  本表只登记「竞品到手价」，**无一条宝锐自己的报价** → 无法直接算「我们贵/便宜多少」')
    print('  需要补：宝锐对应货号的现行报价（或标准产品定价方案）才能做双向对比')

    print('\n  ════ ⑥ 价格异常（同单位跨度 ≥ 5×）════')
    anomalies = 0
    for (sh, sku, prod), v in by.items():
        pts = sorted([(x['vendor'], x['yT'], x['row']) for x in v if x['yT']], key=lambda p: p[1])
        if len(pts) >= 2 and pts[0][1] and pts[-1][1] / pts[0][1] >= 5:
            anomalies += 1
            print('  • %s %s ｜ %.2f(%s) ↔ %.2f(%s)  第%d/%d行'
                  % (sku, prod[:20], pts[0][1], pts[0][0], pts[-1][1], pts[-1][0], pts[0][2], pts[-1][2]))
    print('  跨度≥5× 的组合: %d' % anomalies)
    io.open('/tmp/comp_rows.json', 'w', encoding='utf-8').write(
        json.dumps(rows, ensure_ascii=False, indent=1))
    print('\n  （逐行明细已存 /tmp/comp_rows.json）')


if __name__ == '__main__':
    main()