#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""系列归集异常排查：货号前缀法 vs 物料名称法

背景：M5244-ZJ4-TP 的 ERP 物料名称被写成「SensiDirect RT Premix-UNG」，按名称归集会误入 MD2104 系列。
本脚本以**货号编码规则**为准（X 档位数字可剥离），列出与名称法冲突的货号供人工核对。
"""
import json
import os
import re
from collections import Counter, defaultdict

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

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = os.path.join(ROOT, '销售分析', '产品系列资料归档与缺口清单_2026-10-26.xlsx')
OUT = os.path.join(ROOT, '销售分析', '系列归集异常核对_2026-10-26.xlsx')


def family(name):
    n = re.sub(r'^\s*\d+(?:\.\d+)?\s*[×xX]\s*', '', name or '')
    n = re.sub(r'\s*[\(（].*$', '', n).strip().replace('Ⅱ', 'II').replace('Ⅳ', 'IV').replace('Ⅲ', 'III')
    return re.sub(r'\s+', ' ', n) or ''


def code_key(sku):
    """货号 → 系列键：剥离 P/F 前缀、X 档位、配方号、组分与形态后缀"""
    s = re.sub(r'\s+', '', sku).upper()
    s = re.sub(r'-\d$', '', s)                        # 组分 -1..-6（必须带连字符）
    for t in ['NA', 'DG', 'DB', 'TP', 'FP', '5P', 'O20', 'O25', 'S50', 'S70', 'P01', 'P02', 'P03']:
        s = re.sub(r'-%s(?=-|$)' % t, '', s)
    s = re.sub(r'-{2,}', '-', s).strip('-')
    if re.match(r'^P[A-Z]', s):                        # PFM2181 → FM2181
        s = s[1:]
    m = re.match(r'^([A-Z]+)(\d+)(.*)$', s)
    if not m:
        return s, '', ''
    L, D, rest = m.group(1), m.group(2), m.group(3)
    if L.startswith('F') and len(L) > 1:               # FM → M, FMD → MD, FHW → HW
        L = L[1:]
    x = ''
    if L in ('M', 'MD') and len(D) >= 4:               # M2181 → X2 + 181；MD5104 → X5 + 104
        x, D = D[:-3], D[-3:]
    return L + D, x, rest


def main():
    wb = openpyxl.load_workbook(SRC)
    rows = list(wb['主货号与变体明细'].iter_rows(min_row=2, values_only=True))
    # 列：代表货号 0, 产品系列 1, 主货号 2, 变体货号 3, 物料名称 4, 类型 5, X数 6, 组分 7, DG 8, NA 9, 销售额 10, 排名 11
    recs, seen = [], set()
    for r in rows:
        if r[3] in seen:
            continue
        seen.add(r[3])
        key, x, _ = code_key(r[3])
        recs.append({'cur': r[0], 'cur_fam': r[1], 'sku': r[3], 'name': r[4] or '',
                     'type': r[5], 'comp': r[7], 'dg': r[8], 'na': r[9],
                     'amt': r[10] or 0, 'key': key, 'x': x, 'fam': family(r[4] or '')})

    # 每个货号键下：名称法的主系列（众数）
    by_key = defaultdict(list)
    for r in recs:
        by_key[r['key']].append(r)

    anomalies = []
    for key, rs in by_key.items():
        fams = Counter(r['fam'] for r in rs if r['fam'])
        main_fam = fams.most_common(1)[0][0] if fams else ''
        for r in rs:
            if r['fam'] and main_fam and r['fam'] != main_fam:
                anomalies.append({
                    '货号': r['sku'], '物料名称': r['name'],
                    '当前归类(名称法)': r['cur'], '当前系列名': r['cur_fam'],
                    '建议系列(货号法)': key, '货号键下主名称': main_fam,
                    '本货号名称': r['fam'], '差异类型': '名称与同键其它货号不一致',
                    '2026销售额': r['amt'], '组分': r['comp'],
                })
        # 名称法内部也不一致时，记录
        if len(fams) > 1:
            for r in rs:
                if r['fam'] == main_fam and fams[r['fam']] == 1 and len(fams) > 1:
                    pass

    # M5244 系列统计（货号键 M244）
    m5244 = [r for r in recs if r['key'] == 'M244']
    m5244.sort(key=lambda r: -r['amt'])

    wb2 = openpyxl.Workbook()
    H = Font(color='FFFFFF', bold=True)
    F = PatternFill('solid', fgColor='1F4E79')

    ws = wb2.active
    ws.title = '异常清单'
    h = ['货号', '物料名称', '当前归类(名称法)', '当前系列名', '建议系列(货号法)', '货号键下主名称', '本货号名称', '差异类型', '2026销售额']
    ws.append(h)
    for a in sorted(anomalies, key=lambda x: -x['2026销售额']):
        ws.append([a[k] for k in h])
    for c in ws[1]:
        c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True)
    for i, w in enumerate([26, 52, 14, 34, 14, 34, 34, 26, 13], 1):
        ws.column_dimensions[get_column_letter(i)].width = w
    ws.freeze_panes = 'A2'

    ws2 = wb2.create_sheet('M5244系列')
    ws2.append(['货号', '物料名称', 'X档', '组分', 'DG', 'NA', '2026销售额', '当前归类(名称法)'])
    for r in m5244:
        ws2.append([r['sku'], r['name'], r['x'], r['comp'], r['dg'], r['na'], r['amt'], r['cur']])
    ws2.append([])
    ws2.append(['合计', '', '', '', '', '', sum(r['amt'] for r in m5244), ''])
    for c in ws2[1]:
        c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True)
    for i, w in enumerate([26, 56, 7, 7, 6, 6, 13, 14], 1):
        ws2.column_dimensions[get_column_letter(i)].width = w

    ws3 = wb2.create_sheet('全部对照')
    ws3.append(['货号', '物料名称', '名称法系列', '货号法键', 'X档', '组分', '2026销售额', '是否一致'])
    for r in sorted(recs, key=lambda x: (x['key'], -x['amt'])):
        ws3.append([r['sku'], r['name'], r['cur'], r['key'], r['x'], r['comp'], r['amt'],
                    '一致' if r['cur'] == r['key'] else '待核'])
    for c in ws3[1]:
        c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True)
    for i, w in enumerate([26, 52, 14, 14, 7, 7, 13, 8], 1):
        ws3.column_dimensions[get_column_letter(i)].width = w
    ws3.freeze_panes = 'A2'

    wb2.save(OUT)
    print('异常 %d 条 ｜ M5244 系列 %d 个货号 / %.1f 万' % (len(anomalies), len(m5244), sum(r['amt'] for r in m5244) / 1e4))
    for a in sorted(anomalies, key=lambda x: -x['2026销售额'])[:20]:
        print('  %-26s %-40s 现:%s → 建议:%s' % (a['货号'], a['物料名称'][:40], a['当前归类(名称法)'], a['建议系列(货号法)']))
    print('\nM5244 系列明细:')
    for r in m5244:
        print('  %-24s %-46s %8.0f  %s' % (r['sku'], r['name'][:46], r['amt'], r['cur']))
    print('✅', OUT)


if __name__ == '__main__':
    main()