#!/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')
OUT = os.path.join(ROOT, '销售分析/主销货号_产品系列帕累托_20261030_V01.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)

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)

# 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')
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, '113 个货号/套装的系列归集明细', '每一行对应原帕累托的一行；原销售额合计 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])

# 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%截断/套装归集影响'),
]
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

# 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%帕累托再做一次系列级帕累托”，不能把系列销售额当成全年全量销售额'),
    ('待确认', '档案未收录 10 个系列、特殊货号 4 个分组；确认后再决定并档或保持单列'),
    ('生成日期', '2026-10-30'),
]
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))