#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""ERP 货号维度分析：帕累托前 80% + 备货部门 + 近 3 个月新增采购 TOP10"""
import datetime as dt
import json
import os
from collections import defaultdict

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

BASE = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库'
ERP = os.path.join(BASE, '月度销售额数据/销售订单_20260101-20261020.xlsx')
ERP25 = os.path.join(BASE, '2024和2025历史数据/2025全年订单.xlsx')
OUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '货号销售额帕累托_前80%_2026.xlsx')
WIN_START = dt.date(2026, 7, 21)   # 近 3 个月（截至 2026-10-20）

EXCLUDE_CUST = ('宝锐', '宝泰', '横琴')


def d(v):
    if isinstance(v, dt.datetime):
        return v.date()
    if isinstance(v, dt.date):
        return v
    try:
        return dt.datetime.strptime(str(v)[:10], '%Y-%m-%d').date()
    except Exception:
        return None


def load(path):
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb.active
    rows = ws.iter_rows(values_only=True)
    head = [str(x).strip() if x is not None else '' for x in next(rows)]
    return {h: i for i, h in enumerate(head)}, rows


# ── ① 货号 × 销售额 ───────────────────────────────────────────
H, rows = load(ERP)
sku = defaultdict(lambda: {'amt': 0.0, 'rows': 0, 'orders': set(), 'cust': set(), 'cust_orders': defaultdict(set),
                           'dept': defaultdict(float), 'first': None, 'last': None, 'names': set()})
total = 0.0
blank_amt = 0.0
kept = 0
for r in rows:
    typ = str(r[H['单据类型']] or '')
    st = str(r[H['单据状态']] or '')
    gift = str(r[H['是否赠品']] or '')
    cust = str(r[H['客户']] or '')
    if typ != '标准销售订单' or st != '已审核' or gift == '是':
        continue
    if any(k in cust for k in EXCLUDE_CUST):
        continue
    try:
        amt = float(r[H['价税合计']] or 0)
    except Exception:
        amt = 0.0
    total += amt
    kept += 1
    code = str(r[H['货号#']] or '').strip()
    if not code or code.lower() == 'none':
        blank_amt += amt
        continue
    x = sku[code]
    x['amt'] += amt
    x['rows'] += 1
    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 '未填')] += amt
    x['names'].add(str(r[H['物料名称']] or '').strip())
    dd = d(r[H['合同日期']])
    if dd:
        x['first'] = dd if x['first'] is None or dd < x['first'] else x['first']
        x['last'] = dd if x['last'] is None or dd > x['last'] else x['last']

ranked = sorted(sku.items(), key=lambda kv: -kv[1]['amt'])
cum = 0.0
rows80 = []
for code, v in ranked:
    if v['amt'] <= 0:
        continue
    cum += v['amt']
    share = v['amt'] / total * 100
    cumshare = cum / total * 100
    top_dept = max(v['dept'].items(), key=lambda kv: kv[1])
    dept_share = top_dept[1] / v['amt'] * 100 if v['amt'] else 0
    rep = sum(1 for _c, _os in v['cust_orders'].items() if len(_os) >= 2)
    avg = v['amt'] / len(v['orders']) if v['orders'] else 0
    rows80.append([code, sorted(v['names'])[0][:60] if v['names'] else '', round(v['amt'], 2), round(share, 3),
                   round(cumshare, 3), len(v['orders']), len(v['cust']), rep,
                   round(rep / len(v['cust']) * 100, 1) if v['cust'] else 0, round(avg, 2),
                   top_dept[0], round(dept_share, 1), len(v['dept']), str(v['first'] or ''), str(v['last'] or '')])
    if cumshare >= 80:
        break

# ── ③ 备货部门汇总（全部货号）────────────────────────────────
dept_sum = defaultdict(lambda: {'amt': 0.0, 'skus': set()})
for code, v in sku.items():
    for dp, a in v['dept'].items():
        dept_sum[dp]['amt'] += a
        dept_sum[dp]['skus'].add(code)
dept_rows = [[k, round(v['amt'], 2), round(v['amt'] / total * 100, 2), len(v['skus'])]
             for k, v in sorted(dept_sum.items(), key=lambda kv: -kv[1]['amt'])]

# ── ④ 近 3 个月新增采购货号 ───────────────────────────────────
H25, rows25 = load(ERP25)
sk25 = set()
for r in rows25:
    c = str(r[H25.get('货号#', -1)] or '').strip() if '货号#' in H25 else ''
    if c:
        sk25.add(c)

new_pool = []
for code, v in sku.items():
    if v['amt'] <= 0 or not v['last'] or not v['first']:
        continue
    if v['last'] < WIN_START:
        continue
    if v['first'] < WIN_START:      # 窗口前 2026 已有采购 → 不是新增
        continue
    if code in sk25:                # 2025 就买过 → 不是新增
        continue
    win_amt = 0.0
    new_pool.append([code, sorted(v['names'])[0][:60] if v['names'] else '', str(v['first']),
                     round(v['amt'], 2), len(v['orders']), len(v['cust']),
                     max(v['dept'].items(), key=lambda kv: kv[1])[0]])

# 重新按窗口内金额算（需二次扫描，保证 TOP10 是近 3 个月金额）
win_amt = defaultdict(float)
H2, rows2 = load(ERP)
for r in rows2:
    if str(r[H2['单据类型']] or '') != '标准销售订单' or str(r[H2['单据状态']] or '') != '已审核':
        continue
    if str(r[H2['是否赠品']] or '') == '是':
        continue
    cust = str(r[H2['客户']] or '')
    if any(k in cust for k in EXCLUDE_CUST):
        continue
    dd = d(r[H2['合同日期']])
    if not dd or dd < WIN_START:
        continue
    code = str(r[H2['货号#']] or '').strip()
    try:
        amt = float(r[H2['价税合计']] or 0)
    except Exception:
        amt = 0.0
    win_amt[code] += amt

new_rows = []
for row in new_pool:
    row[3] = round(win_amt.get(row[0], 0.0), 2)
    new_rows.append(row)
new_rows.sort(key=lambda x: -x[3])
new_rows = [r for r in new_rows if r[3] > 0][:10]

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


def style(ws, headers, widths, rows_data, money_cols=(), pct_cols=()):
    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(rows_data, 2):
        for ci, val in enumerate(row, 1):
            c = ws.cell(row=ri, column=ci, value=val)
            if ci in money_cols:
                c.number_format = '#,##0.00'
            if ci in pct_cols:
                c.number_format = '0.0"%"'
    ws.freeze_panes = 'A2'


ws1 = wb.active; ws1.title = '前80%货号'
style(ws1, ['排名', '货号', '品名', '销售额(元)', '占比%', '累计占比%', '订单数', '采购客户数', '复购客户数', '复购率%', '平均单次采购额(元)', '主备货部门', '该部门占比%', '备货部门数', '首次', '最近'],
      [6, 22, 46, 14, 9, 10, 8, 10, 10, 9, 15, 16, 12, 11, 11, 11],
      [[i + 1] + r for i, r in enumerate(rows80)], money_cols=(4,), pct_cols=(5, 6, 10))

ws2 = wb.create_sheet('备货部门汇总')
style(ws2, ['备货部门', '销售额(元)', '占比%', '涉及货号数'], [20, 15, 10, 12],
      dept_rows, money_cols=(2,), pct_cols=(3,))

ws3 = wb.create_sheet('近3月新增TOP10')
style(ws3, ['排名', '货号', '品名', '近3月销售额(元)', '首次采购', '订单数', '客户数', '主备货部门'],
      [6, 22, 52, 16, 12, 8, 8, 16],
      [[i + 1] + r for i, r in enumerate(new_rows)], money_cols=(4,))

ws4 = wb.create_sheet('口径说明')
notes = [
    ['项目', '说明'],
    ['数据源', '销售订单_20260101-20261020.xlsx（2026-10-20 入库，26,301 行）'],
    ['统计口径', '标准销售订单 + 已审核 + 非赠品 + 剔除宝锐/宝泰/横琴自体系客户；金额=价税合计'],
    ['货号口径', '取「货号#」列；未填货号的行单独计入总额但不参与排名'],
    ['前80%', '按货号销售额降序累加，累计占比首次 ≥80% 即止（含跨过 80% 的那一条）'],
    ['备货部门', '同一货号出现在多个备货部门时，取金额最大的部门为主部门，并给出部门数与占比'],
    ['近3个月', '2026-07-21 ~ 2026-10-20（截至最新 ERP 日期往前推 3 个月）'],
    ['新增采购', '该货号在 2026-07-21 之前（2026 年内）无采购，且 2025 全年订单中也未出现'],
    ['验证', '总额 %.2f 元｜有效行 %d｜货号总数 %d｜未填货号金额 %.2f 元' % (total, kept, len(sku), blank_amt)],
]
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 = 14
ws4.column_dimensions['B'].width = 92


# ── 补充：近 3 个月采购额 TOP10（不区分是否新增）───────────────
win_rows = []
for code, a in sorted(win_amt.items(), key=lambda kv: -kv[1])[:10]:
    v = sku.get(code)
    if not v:
        continue
    win_rows.append([code, sorted(v['names'])[0][:60] if v['names'] else '', round(a, 2),
                     len(v['orders']), len(v['cust']), max(v['dept'].items(), key=lambda kv: kv[1])[0]])

ws3b = wb.create_sheet('近3月采购额TOP10')
style(ws3b, ['排名', '货号', '品名', '近3月销售额(元)', '订单数', '客户数', '主备货部门'],
      [6, 22, 52, 16, 8, 8, 16], [[i + 1] + r for i, r in enumerate(win_rows)], money_cols=(4,))


# ── 备货部门（仅前 80% 货号）─────────────────────────────────
keep80 = {r[0] for r in rows80}
dept80 = defaultdict(float)
tot80 = 0.0
for code, v in sku.items():
    if code not in keep80:
        continue
    for dp, a in v['dept'].items():
        dept80[dp] += a
    tot80 += v['amt']
dept80_rows = [[k, round(v, 2), round(v / tot80 * 100, 2)] for k, v in sorted(dept80.items(), key=lambda kv: -kv[1])]
ws2b = wb.create_sheet('备货部门_前80%')
style(ws2b, ['备货部门', '销售额(元)', '占前80%货号%'], [20, 15, 14], dept80_rows, money_cols=(2,), pct_cols=(3,))

wb.save(OUT)
print('总额 %.2f 元｜货号 %d 个｜前80%%含 %d 个货号（覆盖 %.2f%%）｜未填货号金额 %.2f' % (
    total, len(sku), len(rows80), rows80[-1][4], blank_amt))
print('备货部门 Top3:', dept_rows[:3])
print('近3月新增 TOP10 数:', len(new_rows))
for r in new_rows[:5]:
    print('  ', r[0], r[1][:30], r[3])
print('✅', OUT)
json.dump({'total': round(total, 2), 'sku_count': len(sku), 'rows80': rows80,
           'depts': dept_rows, 'new10': new_rows},
          open(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '数据', 'sku_pareto_2026.json'), 'w'),
          ensure_ascii=False, indent=1)