#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""货号主号映射（多组分套装归集）基础设置 —— 生成 + 体检

规则（用户 2026-10-16 基础设置更新）：
  -1 酶mix ｜ -2 逆转录酶/Buffer ｜ -3 离子/dNTP ｜ -4 其他拆分 ｜（可能 -5/-6）
  两组分→主号 -DB ｜ 三组分→-TP ｜ 四组分→-4P ｜ 五组分→-FP ｜ 六组分→-6P

产出：
  数据/货号主号映射.json        母体 → {组分清单, 主号后缀（实测）, 推定后缀, 冲突标记}
  数据/货号主号映射_待确认.json 与规则不一致的清单（供拍板）
"""
import io
import json
import os
import re
import warnings
from collections import defaultdict

warnings.filterwarnings('ignore')
import openpyxl  # noqa: E402

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = [('2025', '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/2024和2025历史数据/2025全年订单.xlsx'),
       ('2026', '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261015.xlsx')]
SPLIT = re.compile(r'-(\d)$')
SUF = {'DB': 2, 'TP': 3, '4P': 4, 'FP': 5, '6P': 6}
SUF_OF = {2: 'DB', 3: 'TP', 4: '4P', 5: 'FP', 6: '6P'}

sku = {}          # 货号 → {name, amt, n, years}
for tag, path in SRC:
    wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
    ws = wb['Sheet1'] if tag == '2025' else wb[wb.sheetnames[0]]
    hdr = None
    for r in ws.iter_rows(min_row=1, values_only=True):
        if hdr is None:
            hdr = [str(x or '').strip() for x in r]
            ix = {k: hdr.index(k) for k in hdr if k in ('货号#', '物料名称', '价税合计', '是否赠品')}
            continue
        s = str((r[ix['货号#']] if ix.get('货号#') is not None and ix['货号#'] < len(r) else '') or '').strip()
        if not s:
            continue
        d = sku.setdefault(s, {'name': '', 'amt': 0.0, 'n': 0, 'y': set()})
        d['n'] += 1
        d['y'].add(tag)
        try:
            d['amt'] += float(r[ix['价税合计']] or 0)
        except Exception:
            pass
        nm = str((r[ix['物料名称']] if ix.get('物料名称') is not None and ix['物料名称'] < len(r) else '') or '').strip()
        if nm:
            d['name'] = nm
    wb.close()
print('█ 货号 %d 个' % len(sku))

# 母体归集：主号（带 -DB/-TP/…）与组分（带 -n）都回溯到母体
def mother(k):
    if re.search(r'-(DB|TP|4P|FP|6P)$', k):
        return re.sub(r'-(DB|TP|4P|FP|6P)$', '', k)
    if SPLIT.search(k):
        return SPLIT.sub('', k)
    return None

fam = defaultdict(lambda: {'comp': {}, 'main': {}, 'sku_self': None})
for k, v in sku.items():
    m = mother(k)
    if m is None:
        continue
    if re.search(r'-(DB|TP|4P|FP|6P)$', k):
        fam[m]['main'][k] = v
    else:
        fam[m]['comp'][k] = v
    if re.search(r'-(DB|TP|4P|FP|6P)$', k) is None and SPLIT.search(k) is None:
        fam[m]['sku_self'] = v

out, todo = {}, {'后缀与组分个数不符': [], '组分与主号并存': [], '组分序号 > 6': [], '三级嵌套': [], '自称主号但无组分': []}
for m, v in sorted(fam.items(), key=lambda kv: -sum(x['amt'] for x in kv[1]['comp'].values())):
    comps = sorted(v['comp'], key=lambda k: (len(k), k))
    # 组分序号（取最后一段数字）
    lics = sorted({int(SPLIT.search(c).group(1)) for c in comps if SPLIT.search(c)})
    cnt = len([l for l in lics if l <= 6])
    main = sorted(v['main'])
    sufs = sorted({re.search(r'-(DB|TP|4P|FP|6P)$', k).group(1) for k in main})
    guess = SUF_OF.get(cnt, '')
    _src = v['comp'] or v['main']
    rec = {'mother': m, 'name': (next(iter(_src.values()))['name'] if _src else ''),
           'comp_cnt': cnt, 'comps': [{'sku': c, 'name': v['comp'][c]['name'], 'amt': round(v['comp'][c]['amt']),
                                       'years': sorted(v['comp'][c]['y'])} for c in comps],
           'mains': [{'sku': k, 'suffix': re.search(r'-(DB|TP|4P|FP|6P)$', k).group(1), 'name': v['main'][k]['name'],
                      'amt': round(v['main'][k]['amt']), 'years': sorted(v['main'][k]['y'])} for k in main],
           'guess_suffix': guess}
    if main and sufs and guess and guess not in sufs:
        todo['后缀与组分个数不符'].append({'mother': m, 'comp_cnt': cnt, 'guess': guess, 'actual': sufs,
                                         'comps': comps, 'mains': main})
    if comps and main:
        todo['组分与主号并存'].append({'mother': m, 'comps': comps, 'mains': main})
    if any(l > 6 for l in lics):
        todo['组分序号 > 6'].append({'mother': m, 'lics': lics, 'comps': comps})
    if any(re.search(r'-(DB|TP|4P|FP|6P)-\d$', c) for c in comps):
        todo['三级嵌套'].append({'mother': m, 'comps': [c for c in comps if re.search(r'-(DB|TP|4P|FP|6P)-\d$', c)]})
    if main and not comps:
        todo['自称主号但无组分'].append({'mother': m, 'mains': main})
    out[m] = rec

json.dump({'meta': {'rule': {'-1': '酶mix', '-2': '逆转录酶/Buffer', '-3': '离子/dNTP', '-4': '其他',
                             'suffix': {'DB': 2, 'TP': 3, '4P': 4, 'FP': 5, '6P': 6}},
                    'source': '用户刘新元 2026-10-16 基础设置更新', 'status': '生效中（3 项待确认）',
                    'generated': '2026-10-16'},
           'families': out}, io.open(W + '/数据/货号主号映射.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
json.dump(todo, io.open(W + '/数据/货号主号映射_待确认.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1)

print('█ 母体（多组分套装）%d 个' % len(out))
print('█ 组分号 %d ｜ 主号 %d' % (sum(len(x['comps']) for x in out.values()), sum(len(x['mains']) for x in out.values())))
for k, v in todo.items():
    print('   ⚠ %-22s %d 例' % (k, len(v)))
print()
print('█ 【待确认1】主号后缀 ≠ 组分个数（前 12 例）')
for x in todo['后缀与组分个数不符'][:12]:
    print('   %-22s 组分 %d 个 → 规则应为 -%s，实测主号 -%s ｜ %s' % (x['mother'], x['comp_cnt'], x['guess'],
          '/'.join(x['actual']), '、'.join(x['comps'])[:44]))
print()
print('█ 【待确认2】组分与主号并存（前 8 例，含金额）')
for x in todo['组分与主号并存'][:8]:
    print('   %-22s 组分 %s（%s 元）｜ 主号 %s（%s 元）' % (x['mother'], '、'.join(x['comps']),
          format(sum(fam[x['mother']]['comp'][c]['amt'] for c in x['comps']), ',.0f'), '、'.join(x['mains']),
          format(sum(fam[x['mother']]['main'][c]['amt'] for c in x['mains']), ',.0f')))
print()
print('█ 【待确认3】组分序号 > 6（全部 %d 例）' % len(todo['组分序号 > 6']))
for x in todo['组分序号 > 6'][:10]:
    print('   %-22s 序号 %s ｜ %s' % (x['mother'], x['lics'], '、'.join(x['comps'])[:50]))
print()
print('█ 【待确认4】三级嵌套（主号下再拆组分）%d 例' % len(todo['三级嵌套']))
for x in todo['三级嵌套'][:6]:
    print('   %-22s %s' % (x['mother'], '、'.join(x['comps'])[:60]))
print()
print('✅ 数据/货号主号映射.json（%d 母体）｜ 数据/货号主号映射_待确认.json'
      % len(out))