#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把货号级销售额帕累托按产品系列归档归集，并重算系列级帕累托。
只读源文件；输出 xlsx，不改原文件。
"""
import os, re, json
from collections import defaultdict, Counter
from datetime import datetime
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
P = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/doc_4df817673b24_货号销售额帕累托_套装归集_2026.xlsx'
F = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/doc_7f8bac21a9de_产品系列资料归档与缺口清单_2026-10-26.xlsx'
V = os.path.join(ROOT, '销售分析/产品系列资料归档与缺口清单_v3_2026-10-26.xlsx')
E = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261027.xlsx'
OUT = os.path.join(ROOT, '销售分析/主销货号_产品系列帕累托_20261030_V02.xlsx')

def norm(v):
    if v is None: return ''
    return str(v).replace('\xa0', ' ').strip().upper()

def num(v):
    try:
        if pd.isna(v): return 0.0
        return float(str(v).replace(',', '').replace('，', ''))
    except Exception:
        return 0.0

# ---------- load ----------
x = pd.read_excel(P, sheet_name='套装归集_前80%', header=0)
fm = pd.read_excel(F, sheet_name='主货号与变体明细', header=0)
fs = pd.read_excel(F, sheet_name='特殊货号待标记', header=0)
ft = pd.read_excel(F, sheet_name='系列总表', header=0)
vm = pd.read_excel(V, sheet_name='变体明细', header=0)
erp = pd.read_excel(E, usecols=['单据类型','单据状态','是否赠品','客户','物料名称','价税合计'])
for c in ['单据类型','单据状态','是否赠品','客户','物料名称']:
    erp[c] = erp[c].astype(str).str.strip()
erp['金额'] = pd.to_numeric(erp['价税合计'], errors='coerce').fillna(0)
erp_std = erp[(erp['单据类型'].str.contains('标准销售订单')) & (erp['单据状态'].str.contains('已审核')) & (erp['是否赠品'] != '是') & (~erp['客户'].str.contains('宝锐|宝泰|横琴', na=False))]
erp_sd = erp_std[erp_std['物料名称'].str.contains('SensiDirect RT Premix-UNG', case=False, na=False)]
erp_sd_full = float(erp_sd['金额'].sum())
erp_sd_rows = len(erp_sd)

for df in (x, fm, fs, vm):
    for c in df.columns:
        if '货号' in c or '套装' in c:
            df[c] = df[c].map(norm)

x['金额'] = x['销售额(元)'].map(num)
# dedupe / keep all Pareto rows (113)
x = x[x['货号/套装'].astype(str).str.len() > 0].copy()

fmap = {}
for _, r in fm.iterrows():
    for c in ['变体货号', '主货号', '代表货号']:
        if r[c] and r[c] != 'NAN':
            fmap.setdefault(r[c], r['产品系列'])

smap = {}
for _, r in fs.iterrows():
    for c in ['原货号', '去P后基础货号']:
        if r[c] and r[c] != 'NAN':
            smap.setdefault(r[c], r['类别'])

vmap = {}
for _, r in vm.iterrows():
    if r['货号'] and r['货号'] != 'NAN':
        vmap.setdefault(r['货号'], r['系列'])

# v3 series -> final series via code overlap
bridge = defaultdict(Counter)
for code, vs in vmap.items():
    if code in fmap:
        bridge[vs][fmap[code]] += 1
v2f = {s: c.most_common(1)[0][0] for s, c in bridge.items() if c}

def candidates(code):
    out = [code]
    s = code
    for pat in [r'-P\d+$', r'-O\d+$', r'-S\d+$', r'-DB$', r'-FP$', r'-TP$',
                r'-5P$', r'-6P$', r'-NA$', r'-DG$', r'-\d{6}$', r'-\d$']:
        s2 = re.sub(pat, '', s)
        if s2 != s:
            out.append(s2); s = s2
    for pre in ['P', 'F']:
        if s.startswith(pre) and len(s) > 2 and s[1].isalpha():
            out.append(s[1:])
    return list(dict.fromkeys(out))

def classify(code):
    """return group, category, source, confidence, note"""
    if code in fmap:
        return fmap[code], '标准系列', '产品系列归档·主货号与变体明细', '高', ''
    if code in smap:
        return smap[code], '特殊货号', '产品系列归档·特殊货号待标记', '高', '按特殊货号类别归集'
    if re.match(r'^2FM', code):
        return '2FM 丽珠定制', '特殊货号·客户定制', '2FM 编码规则', '高', '客户定制，不并入标准系列'
    if code == 'A3':
        return 'A3 欧易单细胞（单酶·逆转录酶）', '特殊货号·客户定制', 'A3 编码规则', '高', '客户定制，不并入标准系列'
    if re.match(r'^(AHM|AHRM)', code):
        return 'AHM/AHRM 动物诊断', '特殊货号·动物诊断', 'AHM/AHRM 编码规则', '高', '动物诊断，不并入标准系列'
    # exact v3
    if code in vmap:
        vs = vmap[code]
        if vs in v2f:
            return v2f[vs], '标准系列', f'v3变体明细→{vs}→{v2f[vs]}', '中', 'v3 映射回 final 系列'
        return f'档案未收录系列｜{vs}', '档案未收录', f'v3变体明细→{vs}', '低', 'final 归档未收录该系列，需产品部确认'
    # prefix stripping
    for c in candidates(code):
        if c in fmap:
            return fmap[c], '标准系列', f'变体前缀匹配（{c}）', '中', ''
        if c in vmap:
            vs = vmap[c]
            if vs in v2f:
                return v2f[vs], '标准系列', f'v3变体明细→{vs}→{v2f[vs]}（前缀{c}）', '中', ''
            return f'档案未收录系列｜{vs}', '档案未收录', f'v3变体明细→{vs}（前缀{c}）', '低', 'final 归档未收录该系列，需产品部确认'
    return '未归系列', '未归', '未匹配', '低', '需产品部确认'

rows = []
for _, r in x.iterrows():
    code = r['货号/套装']
    group, cat, src, conf, note = classify(code)
    rows.append({
        '原排名': int(r['排名']) if pd.notna(r['排名']) else '',
        '货号/套装': code,
        '品名': '' if pd.isna(r['品名']) else str(r['品名']).strip(),
        '原类型': r['类型'],
        '原销售额': round(r['金额'], 2),
        '产品系列/分组': group,
        '归集类别': cat,
        '归集依据': src,
        '置信度': conf,
        '组分数': int(num(r.get('组分数'))) if pd.notna(r.get('组分数')) else 0,
        '并入的子货号': '' if pd.isna(r.get('并入的子货号')) else str(r.get('并入的子货号')),
        '备注': note,
        '拆分/修正': '',
    })
d = pd.DataFrame(rows)
d['原排名_sort'] = pd.to_numeric(d['原排名'], errors='coerce').fillna(9999)

# ---------- 专项修正（V02） ----------
v01_sd = float(d.loc[d['产品系列/分组'] == 'SensiDirect RT Premix-UNG', '原销售额'].sum())
correction_log = []

# 1) M5244-ZJ4-TP 是共享货号：ERP 同货号同时用于 SensiDirect 与 Neoscript
m = d[d['货号/套装'] == 'M5244-ZJ4-TP']
if len(m):
    row = m.iloc[0].to_dict()
    d = d.drop(m.index)
    for amt, grp, src, note in [
        (9900.0, 'SensiDirect RT Premix-UNG', 'ERP物料名称拆分：5×SensiDirect RT Premix-UNG (003006)', '共享货号，仅9,900元为SensiDirect'),
        (120000.0, 'Neoscript Fast RT Premix-UNG', 'ERP物料名称拆分：5×Neoscript Fast RT Premix-UNG (ZJ4)', '共享货号，120,000元归Neoscript'),
    ]:
        r = dict(row)
        r['原销售额'] = amt
        r['产品系列/分组'] = grp
        r['归集依据'] = src
        r['置信度'] = '高'
        r['备注'] = note
        r['拆分/修正'] = f'M5244-ZJ4-TP → {grp} {amt:,.0f}'
        d = pd.concat([d, pd.DataFrame([r])], ignore_index=True)
    correction_log.append({
        '项目': 'M5244-ZJ4-TP 共享货号拆分',
        '原值/处理': '原帕累托1行 129,900 元，全部误归 SensiDirect',
        '修正后': 'SensiDirect 9,900 + Neoscript 120,000',
        '依据': 'ERP 2026标准订单：5×SensiDirect (003006) 9,900；5×Neoscript (ZJ4) 120,000',
        '影响': 'SensiDirect -120,000；Neoscript +120,000；总额不变',
    })

# 2) 12.5×SensiDirect RTase/UNG Mix 是组分/酶Mix，不是 RT Premix-UNG 成品
m = d['货号/套装'] == 'FMD5104-000814-1'
if m.any():
    d.loc[m, '产品系列/分组'] = 'SensiDirect RTase/UNG Mix（组分）'
    d.loc[m, '归集类别'] = '组分/非成品'
    d.loc[m, '归集依据'] = 'ERP物料名称判定：12.5×SensiDirect RTase/UNG Mix'
    d.loc[m, '备注'] = '不是 RT Premix-UNG 成品，从标准系列剔除'
    d.loc[m, '拆分/修正'] = 'FMD5104-000814-1 → 组分/非成品'
    correction_log.append({
        '项目': 'FMD5104-000814-1 剔除',
        '原值/处理': '原归 SensiDirect RT Premix-UNG，70,000 元',
        '修正后': 'SensiDirect RTase/UNG Mix（组分）/ 组分非成品',
        '依据': 'ERP物料名称为12.5×SensiDirect RTase/UNG Mix (with dNTPs)，不是RT Premix-UNG',
        '影响': 'SensiDirect -70,000；总额不变',
    })

# 3) 6P 是套装父级，ERP 无父号成交；组件合计才 67,000
m = d['货号/套装'] == 'FMD0104-074901-6P'
if m.any():
    d.loc[m, '产品系列/分组'] = 'SensiDirect RT Premix-UNG 套装（6P）'
    d.loc[m, '归集类别'] = '套装/非成品'
    d.loc[m, '归集依据'] = 'ERP无父号成交；组件-1~-4合计67,000'
    d.loc[m, '备注'] = '套装父级单列，不与RT Premix成品混算'
    d.loc[m, '拆分/修正'] = 'FMD0104-074901-6P → 套装/非成品'
    correction_log.append({
        '项目': 'FMD0104-074901-6P 套装父级单列',
        '原值/处理': '原归 SensiDirect RT Premix-UNG，67,000 元',
        '修正后': 'SensiDirect RT Premix-UNG 套装（6P）/ 套装非成品',
        '依据': 'ERP无父号成交；-1~-4组件合计67,000',
        '影响': 'SensiDirect -67,000；总额不变',
    })

correction_log.append({
    '项目': '归档遗漏的4个成品变体保留',
    '原值/处理': 'V01已计入，但final系列归档的14个正数变体未列',
    '修正后': '保留在SensiDirect标准系列',
    '依据': 'MD2104-003006-DB 128,940 / MD5104-000830-DB 100,800 / MD6104-020322 78,115 / MD5104-145103-DB 74,000；ERP物料名均为SensiDirect RT Premix-UNG',
    '影响': '合计381,855；V02仍高于归档值',
})

d['原排名_sort'] = pd.to_numeric(d['原排名'], errors='coerce').fillna(9999)

# add zero-sales final series so the standard universe is complete
series_meta = {}
for _, r in ft.iterrows():
    name = str(r['产品系列']).strip()
    series_meta[name] = {
        '代表货号': str(r['代表货号']).strip(),
        '基准货号': str(r['基准货号']).strip(),
        '类型': str(r['类型']).strip(),
        '归档2026销售额': num(r['2026销售额']),
    }
existing_std = set(d.loc[d['归集类别'] == '标准系列', '产品系列/分组'])
for name, meta in series_meta.items():
    if name not in existing_std:
        d.loc[len(d)] = {
            '原排名': '', '货号/套装': '', '品名': '', '原类型': '',
            '原销售额': 0.0, '产品系列/分组': name, '归集类别': '标准系列',
            '归集依据': '系列归档（帕累托无成交）', '置信度': '高',
            '组分数': 0, '并入的子货号': '', '备注': '该系列在原帕累托前80%中无成交', '拆分/修正': ''
        }

# ---------- aggregate ----------
d['产品系列/分组'] = d['产品系列/分组'].astype(str)
agg = d.groupby(['产品系列/分组', '归集类别'], as_index=False).agg(
    销售额=('原销售额', 'sum'),
    货号数=('货号/套装', lambda s: sum(1 for v in s if v)),
    主要货号=('货号/套装', lambda s: '、'.join(list(s[s != ''])[:3]))
)
# top 3 by amount per group
top3 = (d[d['货号/套装'] != ''].sort_values(['产品系列/分组', '原销售额'], ascending=[True, False])
        .groupby('产品系列/分组').head(3)
        .groupby('产品系列/分组')
        .apply(lambda g: '、'.join(f"{r['货号/套装']}({r['原销售额']/10000:.1f}万)" for _, r in g.iterrows())))
agg['主要货号'] = agg['产品系列/分组'].map(top3).fillna('')
agg = agg.sort_values('销售额', ascending=False).reset_index(drop=True)
total = float(d['原销售额'].sum())
agg['排名'] = range(1, len(agg) + 1)
agg['占比%'] = agg['销售额'] / total * 100 if total else 0
agg['累计%'] = agg['占比%'].cumsum()

# full Pareto layer
agg['帕累托分层'] = ''
for i, r in agg.iterrows():
    if r['累计%'] <= 80.0001 or i == 0:
        agg.at[i, '帕累托分层'] = '核心（≤80%）'
    elif r['累计%'] <= 95.0001:
        agg.at[i, '帕累托分层'] = '中间（80–95%）'
    else:
        agg.at[i, '帕累托分层'] = '长尾（>95%）'
agg['是否前80%'] = agg['帕累托分层'].eq('核心（≤80%）').map({True: '是', False: '否'})

# standard-only
std = agg[agg['归集类别'] == '标准系列'].copy().reset_index(drop=True)
std['排名'] = range(1, len(std) + 1)
std_total = float(std['销售额'].sum())
std['占比%'] = std['销售额'] / std_total * 100 if std_total else 0
std['累计%'] = std['占比%'].cumsum()
std['帕累托分层'] = ''
for i, r in std.iterrows():
    if r['累计%'] <= 80.0001 or i == 0:
        std.at[i, '帕累托分层'] = '核心（≤80%）'
    elif r['累计%'] <= 95.0001:
        std.at[i, '帕累托分层'] = '中间（80–95%）'
    else:
        std.at[i, '帕累托分层'] = '长尾（>95%）'
std['是否前80%'] = std['帕累托分层'].eq('核心（≤80%）').map({True: '是', False: '否'})
std['代表货号'] = std['产品系列/分组'].map(lambda s: series_meta.get(s, {}).get('代表货号', ''))
std['类型'] = std['产品系列/分组'].map(lambda s: series_meta.get(s, {}).get('类型', ''))
std['归档2026销售额'] = std['产品系列/分组'].map(lambda s: series_meta.get(s, {}).get('归档2026销售额', 0))
std['映射与归档差异'] = std['销售额'] - std['归档2026销售额']

# special / unrecorded / unassigned
other = agg[agg['归集类别'] != '标准系列'].copy().reset_index(drop=True)
other['占比%'] = other['销售额'] / total * 100 if total else 0
other['序号'] = range(1, len(other) + 1)
other['全口径排名'] = other['排名']

# ---------- workbook ----------
os.makedirs(os.path.dirname(OUT), exist_ok=True)
wb = Workbook()
NAVY = '1F4E79'; BLUE = '2E75B6'; LIGHT = 'DDEBF7'; PALE = 'F2F7FB'
ORANGE = 'FCE4D6'; GRAY = 'E7E6E6'; GREEN = 'E2EFDA'; WHITE = 'FFFFFF'
thin = Side(style='thin', color='D9E2F3')
border = Border(left=thin, right=thin, top=thin, bottom=thin)
base_font = Font(name='Arial', size=10)
head_font = Font(name='Arial', size=10, bold=True, color=WHITE)

def title(ws, text, sub=''):
    ws.merge_cells('A1:L1')
    c = ws['A1']; c.value = text
    c.font = Font(name='Arial', size=15, bold=True, color=NAVY)
    c.alignment = Alignment(vertical='center')
    ws.row_dimensions[1].height = 28
    if sub:
        ws.merge_cells('A2:L2')
        c2 = ws['A2']; c2.value = sub
        c2.font = Font(name='Arial', size=9, color='666666')
        c2.alignment = Alignment(vertical='center')
        ws.row_dimensions[2].height = 20

def write_table(ws, df, start_row=3, widths=None, special_fill=False):
    df = df.reset_index(drop=True)
    cols = list(df.columns)
    for j, col in enumerate(cols, 1):
        c = ws.cell(row=start_row, column=j, value=col)
        c.fill = PatternFill('solid', fgColor=BLUE)
        c.font = head_font
        c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        c.border = border
    for i, row in df.iterrows():
        r = start_row + 1 + i
        for j, col in enumerate(cols, 1):
            v = row[col]
            if isinstance(v, float) and pd.isna(v): v = ''
            c = ws.cell(row=r, column=j, value=v)
            c.font = base_font
            c.border = border
            c.alignment = Alignment(vertical='center', wrap_text=(col in ['主要货号', '并入库子货号', '备注', '归集依据']))
            if isinstance(v, (int, float)):
                c.number_format = '#,##0.00' if col in ['销售额', '原销售额', '归档2026销售额', '映射与归档差异'] else ('0.00' if col in ['占比%', '累计%'] else '0')
    ws.freeze_panes = f'A{start_row+1}'
    ws.auto_filter.ref = f'A{start_row}:{get_column_letter(len(cols))}{start_row+len(df)}'
    ws.sheet_view.showGridLines = False
    if widths:
        for j, w in enumerate(widths, 1):
            ws.column_dimensions[get_column_letter(j)].width = w

# 1 full Pareto
ws = wb.active; ws.title = '系列帕累托_全口径'
title(ws, '主销货号 · 产品系列销售额帕累托', f'基于《货号销售额帕累托_套装归集_2026》前80%的113个货号/套装；按《产品系列资料归档与缺口清单_2026-10-26》归集；生成日期 2026-10-30；V02 已修正共享货号/组分/套装')
full_cols = ['排名', '产品系列/分组', '归集类别', '销售额', '占比%', '累计%', '帕累托分层', '是否前80%', '货号数', '主要货号']
write_table(ws, agg[full_cols], widths=[6, 30, 20, 14, 10, 10, 16, 10, 8, 52])
for i, row in agg.iterrows():
    r = 4 + i
    fill = ORANGE if row['归集类别'] != '标准系列' else (LIGHT if row['是否前80%'] == '是' else None)
    if fill:
        for j in range(1, len(full_cols) + 1):
            ws.cell(row=r, column=j).fill = PatternFill('solid', fgColor=fill)

# 2 standard
ws = wb.create_sheet('标准系列帕累托')
title(ws, '标准系列销售额帕累托', f'仅 35 个标准系列；标准系列口径合计 {std_total:,.2f} 元；不含特殊货号/档案未收录/未归')
std_cols = ['排名', '产品系列/分组', '代表货号', '类型', '销售额', '占比%', '累计%', '帕累托分层', '是否前80%', '货号数', '主要货号', '归档2026销售额', '映射与归档差异']
write_table(ws, std[std_cols].rename(columns={'产品系列/分组': '产品系列'}), widths=[6, 30, 16, 10, 14, 10, 10, 16, 10, 8, 46, 16, 16])
for i, row in std.iterrows():
    r = 4 + i
    if row['是否前80%'] == '是':
        for j in range(1, len(std_cols) + 1):
            ws.cell(row=r, column=j).fill = PatternFill('solid', fgColor=LIGHT)

# 3 other
ws = wb.create_sheet('特殊与未收录')
title(ws, '特殊货号 / 档案未收录系列', '不并入标准系列排名：特殊货号按归档类别单列；档案未收录系列仅按 v3 变体明细识别，需产品部确认')
o_cols = ['序号', '产品系列/分组', '归集类别', '销售额', '占比%', '货号数', '主要货号', '全口径排名']
write_table(ws, other[o_cols], widths=[6, 34, 22, 14, 10, 8, 60, 10])

# 4 detail
ws = wb.create_sheet('归集明细')
title(ws, '货号/套装的系列归集明细（V02）', '原帕累托113行；M5244-ZJ4-TP按物料名称拆为2行，原销售额合计仍为29,427,701.20元')
d_cols = ['原排名', '货号/套装', '品名', '原类型', '原销售额', '产品系列/分组', '归集类别', '归集依据', '置信度', '组分数', '并入的子货号', '备注', '拆分/修正']
detail_out = d[d['货号/套装'] != ''].copy()
write_table(ws, detail_out[d_cols].assign(_sort=detail_out['原排名_sort']).sort_values('_sort').drop(columns='_sort'), widths=[7, 28, 38, 9, 14, 30, 20, 38, 8, 7, 40, 36, 42])

# 5 pending
ws = wb.create_sheet('待确认清单')
title(ws, '需要确认的归集项', '优先看：档案未收录系列；特殊货号是否按客户/形态单列')
pend = d[d['归集类别'].isin(['档案未收录', '特殊货号', '特殊货号·客户定制', '特殊货号·动物诊断', '组分/非成品', '套装/非成品', '未归'])].copy()
pend = pend[['货号/套装', '原销售额', '产品系列/分组', '归集类别', '归集依据', '置信度', '备注']].sort_values(['归集类别', '产品系列/分组', '原销售额'], ascending=[True, True, False]).reset_index(drop=True)
write_table(ws, pend, widths=[28, 14, 34, 22, 42, 8, 42])

# 6 checks
ws = wb.create_sheet('校验')
title(ws, '口径校验与对账', '用于判断“系列帕累托”是否可勾稽，不改变源数据')
checks = [
    ('源帕累托行数', len(x), ''),
    ('源帕累托销售额合计', round(total, 2), '元'),
    ('归集后销售额合计', round(float(d['原销售额'].sum()), 2), '元'),
    ('差额', round(float(d['原销售额'].sum()) - total, 2), '元；应为 0'),
    ('系列/分组数量', len(agg), '标准系列 + 特殊 + 档案未收录 + 非成品/未归'),
    ('标准系列数量', len(std), '来自 final 系列总表'),
    ('档案未收录系列数', int((agg['归集类别'] == '档案未收录').sum()), '需产品部确认'),
    ('特殊货号/分组数', int((agg['归集类别'].str.startswith('特殊货号')).sum()), '客户定制/动物诊断/特殊形态'),
    ('原帕累托说明中的全年总额', 36736635.53, '元；原文件口径说明'),
    ('本表占全年总额', round(total / 36736635.53 * 100, 2), '%；本表只基于原帕累托前80%'),
    ('标准系列归档总额', round(float(std['归档2026销售额'].sum()), 2), '元；final 系列总表'),
    ('标准系列映射总额', round(std_total, 2), '元；本表归集'),
    ('标准系列映射-归档差异', round(std_total - float(std['归档2026销售额'].sum()), 2), '元；受前80%截断/套装归集影响'),
    ('归集明细行数', len(d[d['货号/套装'] != '']), 'M5244-ZJ4-TP拆为2行，原113行变114行'),
    ('SensiDirect V01', 4152361.00, '元；V01归集值'),
    ('SensiDirect V02', round(float(d.loc[d['产品系列/分组'] == 'SensiDirect RT Premix-UNG', '原销售额'].sum()), 2), '元；修正后'),
    ('SensiDirect 修正额', round(float(d.loc[d['产品系列/分组'] == 'SensiDirect RT Premix-UNG', '原销售额'].sum()) - 4152361.00, 2), '元；-120,000 -70,000 -67,000'),
    ('SensiDirect 归档值', round(float(series_meta.get('SensiDirect RT Premix-UNG', {}).get('归档2026销售额', 0)), 2), '元；final系列总表'),
    ('SensiDirect ERP全量按名', round(erp_sd_full, 2), '元；ERP标准订单物料名含SensiDirect RT Premix-UNG'),
]
ws['A3'] = '项目'; ws['B3'] = '数值'; ws['C3'] = '说明'
for j in range(1, 4):
    c = ws.cell(row=3, column=j); c.fill = PatternFill('solid', fgColor=BLUE); c.font = head_font; c.border = border
for i, (a, b, c) in enumerate(checks, 4):
    ws.cell(row=i, column=1, value=a).border = border
    cell = ws.cell(row=i, column=2, value=b); cell.border = border
    if isinstance(b, (int, float)): cell.number_format = '#,##0.00'
    ws.cell(row=i, column=3, value=c).border = border
ws.column_dimensions['A'].width = 32; ws.column_dimensions['B'].width = 16; ws.column_dimensions['C'].width = 62
ws.sheet_view.showGridLines = False

# 6b correction log
ws = wb.create_sheet('修正记录')
title(ws, 'V02 修正记录', '针对 SensiDirect RT Premix-UNG 的共享货号、组分、套装父级修正；总额不变')
write_table(ws, pd.DataFrame(correction_log), widths=[30, 56, 45, 60, 52])

# 6c SensiDirect specific
ws = wb.create_sheet('SensiDirect专项核对')
title(ws, 'SensiDirect RT Premix-UNG 专项核对', '同一个系列在四个口径下的金额差异；用于回答“4,152,361 是否可信”')
sd_v2 = float(d.loc[d['产品系列/分组'] == 'SensiDirect RT Premix-UNG', '原销售额'].sum())
archive_sd = float(series_meta.get('SensiDirect RT Premix-UNG', {}).get('归档2026销售额', 0))
sd_rows = [
    ('V01 系列帕累托', 4152361.00, '原V01归集：19行之和；含共享货号/组分/套装父级问题'),
    ('V02 系列帕累托', round(sd_v2, 2), '修正后：M5244拆分、剔除12.5×Mix、6P套装单列'),
    ('V01→V02 差异', round(sd_v2 - 4152361.00, 2), '应为 -257,000（-120,000 -70,000 -67,000）'),
    ('产品系列归档·系列总表', round(archive_sd, 2), 'final系列总表原值；仅14个正数成品变体，且M5244共享货号未拆'),
    ('归档值按M5244修正', round(archive_sd - 120000.0, 2), '仅扣M5244中的Neoscript 120,000；未补归档遗漏变体'),
    ('归档遗漏的4个成品变体', 381855.00, 'MD2104-003006-DB / MD5104-000830-DB / MD6104-020322 / MD5104-145103-DB，V02保留'),
    ('ERP全量按物料名', round(erp_sd_full, 2), '2026标准订单、非赠品、非内部；物料名含SensiDirect RT Premix-UNG'),
    ('ERP全量行数', erp_sd_rows, '行数'),
    ('ERP全量 vs V02', round(erp_sd_full - sd_v2, 2), '差额主要来自V02只覆盖原帕累托前80%，以及Pareto与ERP金额的小差异'),
    ('ERP全量 vs 归档', round(erp_sd_full - archive_sd, 2), '归档只覆盖部分成品变体，不能当全量'),
]
write_table(ws, pd.DataFrame(sd_rows, columns=['口径','金额/行数','说明']), widths=[32, 18, 100])

# 7 method
ws = wb.create_sheet('口径说明')
title(ws, '口径说明', '数据来源、归集规则、特殊处理、未解决项')
notes = [
    ('数据源 1', '货号销售额帕累托_套装归集_2026.xlsx → 套装归集_前80%'),
    ('数据源 2', '产品系列资料归档与缺口清单_2026-10-26.xlsx → 主货号与变体明细 / 特殊货号待标记 / 系列总表'),
    ('辅助校验', '产品系列资料归档与缺口清单_v3_2026-10-26.xlsx → 变体明细（仅用于 final 未覆盖货号的前缀/系列候选，不改变 final 系列名）'),
    ('帕累托基数', '原文件前80%的 113 个货号/套装，合计 29,427,701.20 元；不是全年 36,736,635.53 元全量'),
    ('重算方法', '按产品系列/分组汇总销售额 → 降序 → 计算占比与累计占比 → 以累计≤80%为核心层'),
    ('标准系列', 'final 系列总表的 35 个系列；同一系列的成品、组分、DG/NA、不同 X 数合并'),
    ('特殊货号', 'A3、2FM、AHM/AHRM、P 冻干球按归档的特殊货号类别单列，不强行并入标准系列'),
    ('档案未收录', 'final 系列总表未收录、但 v3 变体明细可识别的系列，单列「档案未收录系列」，需产品部确认是否并档'),
    ('套装', '沿用原帕累托的套装归集口径：纯套装父级无成交时并入父级；不重复拆分子货号'),
    ('订单/客户数', '原帕累托的订单数、客户数不可跨货号直接相加，本表不输出系列级订单数/客户数，避免重复计数'),
    ('重要限制', '本表是“对原前80%帕累托再做一次系列级帕累托”，不能把系列销售额当成全年全量销售额'),
    ('V02修正1', 'M5244-ZJ4-TP为共享货号：ERP同货号同时用于SensiDirect(9,900)和Neoscript(120,000)，V02按物料名称拆为两行'),
    ('V02修正2', 'FMD5104-000814-1为12.5×SensiDirect RTase/UNG Mix，不是RT Premix-UNG成品，V02剔除到组分/非成品'),
    ('V02修正3', 'FMD0104-074901-6P为套装父级，ERP无父号成交；组件-1~-4合计67,000，V02单列套装/非成品'),
    ('待确认', '档案未收录 10 个系列、特殊货号 4 个分组、非成品2个分组；确认后再决定并档或保持单列'),
    ('生成日期', '2026-10-30（V02）'),
]
ws['A3'] = '项目'; ws['B3'] = '说明'
for j in range(1, 3):
    c = ws.cell(row=3, column=j); c.fill = PatternFill('solid', fgColor=BLUE); c.font = head_font; c.border = border
for i, (a, b) in enumerate(notes, 4):
    ws.cell(row=i, column=1, value=a).border = border
    c = ws.cell(row=i, column=2, value=b); c.border = border
    c.alignment = Alignment(wrap_text=True, vertical='top')
ws.column_dimensions['A'].width = 20; ws.column_dimensions['B'].width = 105
ws.sheet_view.showGridLines = False

wb.save(OUT)
print('saved', OUT)
print('rows', len(d), 'groups', len(agg), 'std', len(std), 'total', total)
print(agg[['排名','产品系列/分组','归集类别','销售额','占比%','累计%','是否前80%']].head(20).to_string(index=False))
print('\nstandard top:')
print(std[['排名','产品系列/分组','销售额','占比%','累计%','是否前80%']].head(15).to_string(index=False))
print('\npending groups:', other[['产品系列/分组','归集类别','销售额']].to_string(index=False))