#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""ERP 实际主货号清单（多组分套装）→ xlsx 交付

主号定义：尾缀 -DB / -TP / -4P / -FP / -6P（用户在用的实际主货号，非推定）
附：口径冲突标记（后缀≠组分个数 / 与组分并存 / 非组分尾号）
"""
import io
import re
import warnings
from collections import defaultdict

warnings.filterwarnings('ignore')
import openpyxl  # noqa: E402
from openpyxl.styles import Alignment, Font, PatternFill  # 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')]
SUF = re.compile(r'-(DB|TP|4P|FP|6P)$')
SPLIT = re.compile(r'-(\d)$')
SUF_OF = {2: 'DB', 3: 'TP', 4: '4P', 5: 'FP', 6: '6P'}

sku = {}
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
        i = ix.get('货号#')
        s = str((r[i] if i is not None and i < len(r) else '') or '').strip()
        if not s:
            continue
        d = sku.setdefault(s, {'name': '', 'y': {}, 'n': 0, 'paid': 0.0})
        d['n'] += 1
        try:
            amt = float(r[ix['价税合计']] or 0)
        except Exception:
            amt = 0.0
        d['y'][tag] = d['y'].get(tag, 0.0) + amt
        gift = str((r[ix['是否赠品']] if ix.get('是否赠品') is not None and ix['是否赠品'] < len(r) else '') or '').strip()
        if gift != '是':
            d['paid'] += amt
        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 个（2025+2026 全部单据）' % len(sku))

# 主号
MAINS = [k for k in sku if SUF.search(k)]
# 母体 → 组分（含三级嵌套按最外层母体归）
fam_comp = defaultdict(list)
for k in sku:
    if SUF.search(k):
        continue
    if SPLIT.search(k):
        fam_comp[SPLIT.sub('', k)].append(k)

rows = []
for k in sorted(MAINS, key=lambda x: -(sku[x]['y'].get('2026', 0) + sku[x]['y'].get('2025', 0))):
    v = sku[k]
    m = SUF.sub('', k)
    comps = sorted(fam_comp.get(m, []))
    cnt = len({int(SPLIT.search(c).group(1)) for c in comps if SPLIT.search(c) and int(SPLIT.search(c).group(1)) <= 6})
    guess = SUF_OF.get(cnt, '')
    suf = SUF.search(k).group(1)
    rows.append({'sku': k, 'suffix': suf, 'name': v['name'], 'comp_cnt': cnt, 'guess': guess,
                 'match': ('✅' if (guess and guess == suf) else ('❌' if guess else '—')),
                 'comps': '、'.join(comps), 'n_comp_skus': len(comps),
                 'comp_amt': sum(sku[c]['paid'] for c in comps),
                 'a25': v['y'].get('2025', 0.0), 'a26': v['y'].get('2026', 0.0), 'n': v['n'],
                 'years': '/'.join(sorted(v['y']))})
tot_amt = sum(r['a25'] + r['a26'] for r in rows)
conf = [r for r in rows if r['match'] == '❌']
both = [r for r in rows if r['comp_amt'] > 0 and (r['a25'] + r['a26']) > 0]
print('█ 主号 %d 个 ｜ 合计金额（含赠品）%s 元' % (len(rows), format(tot_amt, ',.0f')))
print('█ 与规则冲突 %d 个 ｜ 主号与组分同时有钱（双计风险）%d 个' % (len(conf), len(both)))

wb = openpyxl.Workbook()
H1 = PatternFill('solid', fgColor='1A5276')
HF = Font(color='FFFFFF', bold=True, size=10)
def head(ws, cols, widths):
    ws.append(cols)
    for i, c in enumerate(ws[1], 1):
        c.fill = H1; c.font = HF; c.alignment = Alignment(horizontal='center', vertical='center')
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[openpyxl.utils.get_column_letter(i)].width = w
    ws.freeze_panes = 'A2'

ws = wb.active; ws.title = 'ERP主货号清单'
head(ws, ['主货号', '尾缀', '品名', 'ERP组分数', '推导后缀', '一致?', '关联组分号',
          '2025金额', '2026金额', '单据笔数', '出现年', '组分侧金额(仅参考)'],
     [30, 7, 40, 9, 9, 7, 46, 14, 14, 9, 10, 16])
for r in rows:
    ws.append([r['sku'], r['suffix'], r['name'], r['comp_cnt'], r['guess'], r['match'], r['comps'],
               round(r['a25']), round(r['a26']), r['n'], r['years'], round(r['comp_amt'])])
    if r['match'] == '❌':
        for c in ws[ws.max_row]:
            c.fill = PatternFill('solid', fgColor='FDE7E7')
ws.append(['合计', '', '%d 个主号' % len(rows), '', '', '', '', round(sum(r['a25'] for r in rows)),
           round(sum(r['a26'] for r in rows)), sum(r['n'] for r in rows), '', ''])
ws[ws.max_row][0].font = Font(bold=True)

ws2 = wb.create_sheet('口径冲突清单')
head(ws2, ['类型', '母体/主号', '说明'], [22, 32, 78])
for r in conf:
    ws2.append(['① 后缀≠组分数', r['sku'], '实测组分 %d 个 → 规则应 -%s，ERP 实际 -%s ｜ 组分：%s' % (r['comp_cnt'], r['guess'], r['suffix'], r['comps'])])
for r in both:
    ws2.append(['② 主号与组分并存', r['sku'], '主号 %s 元 ｜ 组分合计 %s 元（%s）→ 相加会双计' % (format(r['a25'] + r['a26'], ',.0f'), format(r['comp_amt'], ',.0f'), r['comps'][:60])])
ws2.append(['③ 三级嵌套', '（401 例）', '形如 M2181-4-020303-DB-1/-2、FMD0104-074901-6P-1/-2/-3：主号下再拆组分，归到主号为止'])
ws2.append(['④ 非组分尾号', 'BTE-8 / E20-8 / AS412-9 / CM-8 / FE33-043801-8', '机型/规格尾号，非组分位次，不得并入母体 BTE/E20'])
ws2.append(['④ 超 6 组分', 'MD5104-014409', '实测 -1…-7（7 个组分），规则无 7 组分后缀，待产品部定'])

ws3 = wb.create_sheet('规则说明')
for line in [['多组分套装归集规则（刘新元 2026-10-16 基础设置）'], [],
             ['组分位次', '-1 酶mix ｜ -2 逆转录酶或Buffer ｜ -3 离子或dNTP ｜ -4 其他拆分 ｜ 可能 -5/-6'],
             ['主号后缀', '2组分→-DB ｜ 3组分→-TP ｜ 4组分→-4P ｜ 5组分→-FP ｜ 6组分→-6P'],
             ['本清单', 'ERP 实际在售主货号（尾缀 -DB/-TP/-4P/-FP/-6P），非推定'],
             ['金额口径', '价税合计，含赠品（赠品行金额通常为 0）；2025 为全年，2026 截至 2026-10-15'],
             ['数据源', '2025全年订单.xlsx Sheet1 ｜ 销售订单_20260101-20261015.xlsx']]:
    ws3.append(line)
ws3['A1'].font = Font(bold=True, size=12)
ws3.column_dimensions['A'].width = 16
ws3.column_dimensions['B'].width = 96

out = W + '/销售分析/宝锐ERP主货号清单（多组分套装）_2026-10-16.xlsx'
wb.save(out)
import os
print('✅ %s（%.0f KB）｜ 3 Sheet：主号清单 %d 行 ｜ 冲突 %d 行' % (out, os.path.getsize(out) / 1024, len(rows), len(conf) + len(both) + 3))