#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""套装归集后的货号销售额帕累托（只归集父级无成交的纯套装，避免重复计数）"""
import json
import os
from collections import defaultdict

import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
BOM = json.load(open(W + '/数据/product_bom_map.json', encoding='utf-8'))
PO, CO = BOM['parents_of'], BOM['children_of']
OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '货号销售额帕累托_套装归集_2026.xlsx')

child2parent = {}
for child, arr in PO.items():
    ps = {x.get('ps') for x in arr if x.get('ps')}
    if len(ps) == 1:
        child2parent[child] = ps.pop()

P = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261020.xlsx'
wb = openpyxl.load_workbook(P, read_only=True, data_only=True)
ws = wb.active
rows = ws.iter_rows(values_only=True)
H = {h: i for i, h in enumerate([str(x).strip() for x in next(rows)])}

sku = defaultdict(lambda: {'amt': 0.0, 'orders': set(), 'cust': set(), 'cust_orders': defaultdict(set), 'dept': defaultdict(float), 'names': set()})
total = 0.0
blank = 0.0
for r in rows:
    if str(r[H['单据类型']] or '') != '标准销售订单' or str(r[H['单据状态']] or '') != '已审核':
        continue
    if str(r[H['是否赠品']] or '') == '是':
        continue
    cust = str(r[H['客户']] or '')
    if any(k in cust for k in ('宝锐', '宝泰', '横琴')):
        continue
    code = str(r[H['货号#']] or '').strip()
    try:
        a = float(r[H['价税合计']] or 0)
    except Exception:
        a = 0.0
    total += a
    if not code:
        blank += a
        continue
    x = sku[code]
    x['amt'] += a
    x['orders'].add(str(r[H['单据编号']] or ''))
    x['cust'].add(cust)
    x['cust_orders'][cust].add(str(r[H['单据编号']] or ''))
    x['dept'][str(r[H['备货部门']] or '未填')] += a
    x['names'].add(str(r[H['物料名称']] or '').strip())

# 纯套装判定
kids = defaultdict(list)
for c, p in child2parent.items():
    if c in sku:
        kids[p].append(c)
pure = {p: v for p, v in kids.items() if p not in sku}
mixed = {p: v for p, v in kids.items() if p in sku}

# 归集
units = defaultdict(lambda: {'amt': 0.0, 'kind': '单品', 'parent': '', 'parts': [], 'names': set(),
                            'cust_orders': defaultdict(set), 'orders': set()})
for code, v in sku.items():
    p = child2parent.get(code)
    if p and p in pure:
        u = units[p]
        u['amt'] += v['amt']
        u['kind'] = '套装'
        u['parent'] = p
        u['parts'].append((code, round(v['amt'], 2)))
        u['names'].add(p)
        u['orders'] |= v['orders']
        for _c, _os in v['cust_orders'].items():
            u['cust_orders'][_c] |= _os
    else:
        u = units[code]
        u['amt'] += v['amt']
        u['names'] |= v['names']
        u['orders'] |= v['orders']
        for _c, _os in v['cust_orders'].items():
            u['cust_orders'][_c] |= _os
for p, v in pure.items():
    if p in units:
        units[p]['names'] = {p}

ranked = sorted(units.items(), key=lambda kv: -kv[1]['amt'])
cum = 0.0
rows80 = []
for code, v in ranked:
    if v['amt'] <= 0:
        continue
    cum += v['amt']
    _custs = len(v['cust_orders'])
    _rep = sum(1 for _os in v['cust_orders'].values() if len(_os) >= 2)
    _avg = v['amt'] / len(v['orders']) if v['orders'] else 0
    rows80.append([code, v['kind'], round(v['amt'], 2), round(v['amt'] / total * 100, 3),
                   round(cum / total * 100, 3), len(v['orders']), _custs, _rep,
                   round(_rep / _custs * 100, 1) if _custs else 0, round(_avg, 2),
                   len(v['parts']) if v['kind'] == '套装' else 0,
                   '、'.join(p[0] for p in v['parts']) if v['parts'] else ''])
    if cum / total * 100 >= 80:
        break

# 名称：套装取 BOM 里的父级品名
KITNAME = {}
for p, arr in CO.items():
    if arr:
        KITNAME[p] = (arr[0].get('pn') or '') + ' ' + (arr[0].get('pp') or '')

print('总额 %.2f｜归集后单元 %d｜前80%% %d 个（覆盖 %.2f%%）｜纯套装 %d｜混合套装 %d' % (
    total, len(units), len(rows80), rows80[-1][4], len(pure), len(mixed)))

# Excel
wb2 = openpyxl.Workbook()
HF = PatternFill('solid', fgColor='1F4E79'); HFONT = Font(color='FFFFFF', bold=True, size=10)


def style(ws, headers, widths, data, money=(), pct=()):
    for i, h in enumerate(headers, 1):
        c = ws.cell(row=1, column=i, value=h); c.fill = HF; c.font = HFONT
        c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        ws.column_dimensions[get_column_letter(i)].width = widths[i - 1]
    for ri, row in enumerate(data, 2):
        for ci, val in enumerate(row, 1):
            c = ws.cell(row=ri, column=ci, value=val)
            if ci in money:
                c.number_format = '#,##0.00'
            if ci in pct:
                c.number_format = '0.00"%"'
    ws.freeze_panes = 'A2'


ws1 = wb2.active; ws1.title = '套装归集_前80%'
data1 = []
for i, r in enumerate(rows80, 1):
    data1.append([i, r[0], KITNAME.get(r[0], '')[:44], r[1], r[2], r[3], r[4], r[5], r[6], r[7], r[8], r[9], r[10], r[11]])
style(ws1, ['排名', '货号/套装', '品名', '类型', '销售额(元)', '占比%', '累计%', '订单数', '采购客户数', '复购客户数', '复购率%', '平均单次采购额(元)', '组分数', '并入的子货号'],
      [6, 20, 42, 8, 14, 9, 9, 8, 10, 10, 9, 15, 8, 40], data1, money=(5, 12), pct=(6, 7, 11))

ws2 = wb2.create_sheet('归集明细')
data2 = []
for p, v in sorted(pure.items(), key=lambda kv: -sum(sku[c]['amt'] for c in kv[1])):
    for c in v:
        data2.append([p, KITNAME.get(p, '')[:40], c, sorted(sku[c]['names'])[0][:40] if sku[c]['names'] else '',
                      round(sku[c]['amt'], 2)])
    data2.append([p, KITNAME.get(p, '')[:40], '小计', '', round(sum(sku[c]['amt'] for c in v), 2)])
style(ws2, ['套装', '套装品名', '子货号', '子货号品名', '销售额(元)'], [16, 40, 16, 46, 14], data2, money=(5,))

ws3 = wb2.create_sheet('未归集_父级也在售')
data3 = []
for p, v in sorted(mixed.items(), key=lambda kv: -sum(sku[c]['amt'] for c in kv[1]))[:60]:
    data3.append([p, KITNAME.get(p, '')[:36], len(v), round(sum(sku[c]['amt'] for c in v), 2),
                  round(sku[p]['amt'], 2)])
style(ws3, ['套装', '套装品名', '子项数', '子项合计(元)', '父号自身成交(元)'], [18, 40, 8, 16, 18], data3, money=(4, 5))

ws4 = wb2.create_sheet('口径说明')
notes = [
    ['项目', '说明'],
    ['归集规则', '仅归集「父级货号 2026 年无成交」的纯套装：把其子货号销售额合并到父级，作为一个统计单元'],
    ['为什么不归集全部', 'BOM 中 301 个套装含已成交子货号，其中 279 个的父级本身也在售；2026 年有 1,479 张订单同时含父级与其子项，机械归集会把同一笔金额算两次'],
    ['本次归集范围', '22 个纯套装、37 个子货号，合计 %.2f 元' % sum(sum(sku[c]['amt'] for c in v) for v in pure.values())],
    ['未归集部分', '279 个套装（423 个子货号、约 %.2f 万元）保持原样，父级与子项各自独立排名' % (sum(sum(sku[c]['amt'] for c in v) for v in mixed.values()) / 1e4)],
    ['总额', '%.2f 元（与单货号口径一致，归集不改变总额）' % total],
    ['空货号', '未填货号 %.2f 元，计入总额但不参与排名' % blank],
]
for i, row in enumerate(notes, 1):
    for j, v in enumerate(row, 1):
        c = ws4.cell(row=i, column=j, value=v)
        if i == 1:
            c.fill = HF; c.font = HFONT
ws4.column_dimensions['A'].width = 16
ws4.column_dimensions['B'].width = 100
wb2.save(OUT)
print('✅', OUT)
json.dump({'rows80': rows80, 'total': round(total, 2), 'pure': {k: [c for c in v] for k, v in pure.items()},
           'mixed_count': len(mixed)},
          open(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '数据', 'sku_pareto_kit.json'), 'w'), ensure_ascii=False, indent=1)