# -*- coding: utf-8 -*-
# 宝锐产品主数据(分子检测类) -> 数据/product_master.json
# 用法: python3 脚本/parse_product_catalog.py [xlsx]
import json, os, sys, glob
from datetime import datetime
import pandas as pd

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
OUT = os.path.join(ROOT, '数据', 'product_master.json')

def find_xlsx():
    if len(sys.argv) > 1:
        return sys.argv[1]
    cand = glob.glob(os.path.expanduser('~/.hermes/cache/documents/*分子检测类试剂产品清单*.xlsx'))
    return max(cand, key=os.path.getmtime)

def tx(v):
    s = str(v).strip()
    return '' if s in ('nan', 'NaT', 'None', '-', '/', '---') else s

def main():
    src = find_xlsx()
    s1 = pd.read_excel(src, sheet_name=0, header=0)
    s2 = pd.read_excel(src, sheet_name=1, header=0)
    s3 = pd.read_excel(src, sheet_name=2, header=0)
    s4 = pd.read_excel(src, sheet_name=3, header=1)

    # 货号 -> 销售分组(品类); 有效期
    cat, life = {}, {}
    for _, r in s1.iterrows():
        c = tx(r.get('产品货号'))
        if not c:
            continue
        g = tx(r.get('销售分组'))
        if g and '勿选' not in g and c not in cat:
            cat[c] = g
        mo = r.get('有效期/月')
        try:
            m = int(float(str(mo).replace('月', '').replace('M', '').strip()))
            if 1 <= m <= 60:
                life.setdefault(c, m)
        except Exception:
            pass

    skus = {}

    def get(code):
        if code not in skus:
            skus[code] = {'sku': code, 'name': '', 'pcode': '', 'spec': '', 'unit': '',
                          'line': 'da', 'cat': cat.get(code, ''), 'life_months': life.get(code, ''),
                          'produced': '', 'source': [], 'components': []}
        return skus[code]

    # --- S2 分子试剂产品部(权威产品+组分) ---
    f2 = s2['产品货号'].ffill()
    for idx, r in s2.iterrows():
        c = tx(r.get('产品货号')) or tx(f2.loc[idx])
        if not c:
            continue
        e = get(c)
        if '产品部' not in e['source']:
            e['source'].append('产品部')
        for k, col in (('name', '产品名称'), ('pcode', '产品编码'), ('spec', '产品规格型号'),
                       ('unit', '单位'), ('produced', '是否已经转产')):
            v = tx(r.get(col))
            if v and not e[k]:
                e[k] = v
        comp = tx(r.get('组分货号')) or tx(r.get('组分编码'))
        cname = tx(r.get('组分名称'))
        if comp and cname and comp != c and not any(x['code'] == comp for x in e['components']):
            e['components'].append({'code': comp, 'name': cname,
                                    'spec': tx(r.get('组分规格型号')),
                                    'vol': tx(r.get('装量')), 'unit2': tx(r.get('单位'))})

    # --- S1 生产部(全量产品: 单组份/多组分/成品/科研/预冻干) ---
    f1 = s1['产品货号'].ffill()
    for idx, r in s1.iterrows():
        c = tx(r.get('产品货号')) or tx(f1.loc[idx])
        if not c:
            continue
        e = get(c)
        if '生产部' not in e['source']:
            e['source'].append('生产部')
        grp = tx(r.get('BOM分组'))
        if grp and not e.get('bom_group'):
            e['bom_group'] = grp
        for k, col in (('name', '产品名称'), ('spec', '产品规格型号'), ('unit', '产品单位')):
            v = tx(r.get(col))
            if v and not e[k]:
                e[k] = v
        g = tx(r.get('销售分组'))
        if g and '勿选' not in g and not e['cat']:
            e['cat'] = g
        mo = r.get('有效期/月')
        try:
            m = int(float(str(mo).replace('月', '').replace('M', '').strip()))
            if 1 <= m <= 60 and not e['life_months']:
                e['life_months'] = m
        except Exception:
            pass
        ci = tx(r.get('组分货号'))
        cn = tx(r.get('组分名称'))
        if ci and cn and ci != c and not any(x['code'] == ci for x in e['components']):
            e['components'].append({'code': ci, 'name': cn,
                                    'spec': tx(r.get('组分规格型号')),
                                    'vol': tx(r.get('子项用量')), 'unit2': tx(r.get('组分单位'))})

    # --- S3 科研产品线 ---
    for _, r in s3.iterrows():
        c = tx(r.get('产品货号'))
        if not c:
            continue
        e = get(c)
        e['line'] = 'ls'
        if '科研' not in e['source']:
            e['source'].append('科研')
        for k, col in (('name', '产品名称'), ('pcode', '产品编码'), ('spec', '产品规格型号'),
                       ('unit', '单位'), ('produced', '是否已经转产')):
            v = tx(r.get(col))
            if v and not e[k]:
                e[k] = v
        ci = tx(r.get('组分货号')) or tx(r.get('组分编码'))
        cn = tx(r.get('组分名称'))
        if ci and cn and ci != c and not any(x['code'] == ci for x in e['components']):
            e['components'].append({'code': ci, 'name': cn,
                                    'spec': tx(r.get('组分规格型号')),
                                    'vol': tx(r.get('装量')), 'unit2': tx(r.get('单位'))})

    rows = [e for e in skus.values() if e['name']]
    rows.sort(key=lambda x: (x['line'], x['sku']))
    name2sku = {}
    for e in rows:
        name2sku.setdefault(e['name'], []).append(e['sku'])

    changes = []
    for _, r in s4.iterrows():
        if tx(r.get('类型')):
            changes.append({'type': tx(r.get('类型')), 'sku': tx(r.get('产品货号')),
                            'name': tx(r.get('产品名称')), 'spec': tx(r.get('产品规格型号')),
                            'date': str(r.get('修改日期'))[:10], 'what': tx(r.get('修改内容'))})

    pack = {'generated': datetime.now().strftime('%Y-%m-%d %H:%M'), 'source': os.path.basename(src),
            'total_skus': len(rows),
            'by_line': {'da': sum(1 for e in rows if e['line'] == 'da'),
                        'ls': sum(1 for e in rows if e['line'] == 'ls')},
            'with_components': sum(1 for e in rows if e['components']),
            'with_cat': sum(1 for e in rows if e['cat']),
            'with_life': sum(1 for e in rows if e['life_months']),
            'by_bom_group': {},
            'skus': rows, 'name_index': name2sku, 'changes': changes}
    bc = {}
    for e in rows:
        bc[e.get('bom_group', '未标注')] = bc.get(e.get('bom_group', '未标注'), 0) + 1
    pack['by_bom_group'] = bc
    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    with open(OUT, 'w', encoding='utf-8') as f:
        json.dump(pack, f, ensure_ascii=False, indent=1)
    print('源文件:', pack['source'])
    print('SKU 总数:', pack['total_skus'], pack['by_line'])
    print('有组分(BOM):', pack['with_components'], '| 有品类:', pack['with_cat'], '| 有有效期:', pack['with_life'])
    print('BOM分组:', pack['by_bom_group'])
    print('变更记录:', len(changes))
    print('样例:')
    for e in rows[:6]:
        print(' ', e['sku'], '|', e['name'], '|', e['spec'], '|', e['cat'], '| 组分', len(e['components']))
    print('输出:', OUT, os.path.getsize(OUT), 'bytes')

main()