#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""系列级聚类（v2）：按产品系列归档资料、列缺口任务

口径（2026-10-26 用户拍板）：
1. 系列编码 = 代表货号 + 产品名双列
2. 组分货号（-1 酶Mix / -2 Buffer / -3 dNTP / -4 MgCl2 / -5 -6）并入系列，标「组分」，不派验证任务
3. 资料与缺口按系列级（一套），配方号只作属性
4. DG（可冻干）与 NA（低核酸背景）合并入系列，但需单独列专项性能参数
5. 特殊货号（P 冻干球 / 2FM 定制 / A3 / AHM / AHRM）单列待标记
"""
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输出-工作类'
DOC = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents'
F1 = os.path.join(DOC, 'doc_1fb1044c5ded_2026-10-24-附件1（销售额占比80%的货号分析）.xlsx')
F2 = os.path.join(DOC, 'doc_a1cb1a53ca50_2026-10-24-附件2（已有数据思维导图清单）.xlsx')
OUT = os.path.join(ROOT, '销售分析', '产品系列资料归档与缺口清单_2026-10-26.xlsx')
SKU_MAP = '/tmp/sku_map.json'
SPECIAL = os.path.join(ROOT, '数据', 'special_sku_map.json')

CORE = ['冻融稳定性', '加速稳定性', '效期/长期/实时稳定性', '运输/物流稳定性']
FORM_TOKENS = ['NA', 'DG', 'DB', 'TP', 'FP', '5P', 'O20', 'O25', 'S50', 'S70', 'P01', 'P02', 'P03']
COMP_RE = re.compile(r'-\d$')

DG_PARAMS = ('冻干前后活性回收率、冻干粉复溶时间与澄清度、冻干保护剂兼容性、冻干态加速稳定性（25/37℃）、'
             '冻干球含水量与外观、常温运输耐受（25℃ 7/14 天）')
NA_PARAMS = ('宿主核酸残留（E.coli/CHO/酵母 qPCR）、DNase/RNase 残留、阴性对照 Ct 与非特异扩增、'
             '低拷贝检出限（≤10 copies）、批间背景一致性')


def strip_p(sku):
    """P 开头的冻干球货号 → 基础货号（PFHW1007-P01-104101-O25-104102 → FHW1007）"""
    s = re.sub(r'\s+', '', sku).upper()
    if not re.match(r'^P[A-Z]', s):
        return s, False
    s = re.sub(r'^P(?=[A-Z])', '', s)
    s = re.split(r'-(?:P\d|R\d|O\d|S\d|V\d|T\d|SCS|PF-|FPO)', s)[0]
    return s, True


def base_sku(sku):
    s = re.sub(r'\s+', '', sku).upper()
    for t in FORM_TOKENS:
        s = re.sub(r'-(%s)(?=-|$)' % t, '', s)
    s = re.sub(r'-{2,}', '-', s).strip('-')
    m = re.match(r'^(.*?[A-Z]\d*)-\d{2}$', s)
    if m and not re.search(r'\d{6}$', m.group(1)):
        s = m.group(1)
    return s


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 kind(name):
    n = name or ''
    if re.search(r'Premix|Mix|预混', n, re.I):
        return '预混液'
    if re.search(r'Taq|RTase|逆转录|UNG|Bst|酶|Enzyme|Polymerase|Tth', n, re.I):
        return '单酶'
    return '其他'


def xfold(name):
    m = re.search(r'(\d+(?:\.\d+)?)\s*[×xX]', name or '')
    return m.group(1) + '×' if m else ''


def load_f1():
    ws = openpyxl.load_workbook(F1, data_only=True)['销售额占比75%的货号']
    out = {}
    for r in range(2, ws.max_row + 1):
        sku = str(ws.cell(r, 2).value or '').strip()
        if sku:
            out[re.sub(r'\s+', '', sku)] = {'amt': float(ws.cell(r, 3).value or 0), 'rank': ws.cell(r, 1).value}
    return out


def load_f2():
    ws = openpyxl.load_workbook(F2, data_only=True)['覆盖矩阵']
    out = {}
    for r in range(3, ws.max_row + 1):
        sku = str(ws.cell(r, 2).value or '').strip()
        if sku:
            out[re.sub(r'\s+', '', sku)] = {
                'docs': int(ws.cell(r, 4).value or 0),
                'core': {CORE[i]: str(ws.cell(r, 5 + i).value or '').strip() for i in range(4)},
            }
    return out


def docs_of(v, f2):
    if v in f2:
        return f2[v]['docs'], f2[v]['core']
    for k, val in f2.items():
        if v.startswith(k) or k.startswith(v):
            return val['docs'], val['core']
    return 0, {}


def pick_code(codes, amts, names):
    def score(c):
        s = 0
        nm = names.get(c, '')
        if not nm.strip().startswith('2×'):
            s += 1
        if c.upper().startswith('P'):
            s += 3
        if re.match(r'^F', c.upper()):
            s += 2
        if re.search(r'\d{6}', c):
            s += 1
        return (s, len(c), -amts.get(c, 0))
    return sorted(codes, key=score)[0]


def series_label(rep):
    """M2181-4 → M2181；MD2104 → MD2104；M2181-4-095203 → M2181"""
    s = rep
    s = re.sub(r'-\d{6}.*$', '', s)
    s = re.sub(r'-\d$', '', s)
    return s


def main():
    f1, f2 = load_f1(), load_f2()
    sm = json.load(open(SKU_MAP, encoding='utf-8'))
    names, amts = sm['name'], sm['amt']

    universe = list(f1) + [s for s in f2 if s not in f1] + [s for s, a in amts.items() if a >= 200000 and s not in f1 and s not in f2]
    universe = [re.sub(r'\s+', '', x) for x in universe]
    CUSTOM = re.compile(r'^(2FM|A3$|AHM|AHRM)')
    universe = [x for x in universe if not CUSTOM.match(x)]
    # 剔除脏货号：含小写字母、超长、无字母的编码（多为组分/录入错误）
    universe = [x for x in universe if re.match(r'^[A-Z0-9][A-Z0-9\-]*$', x) and len(x) <= 26]

    rows = []
    for sku in universe:
        comp = bool(COMP_RE.search(sku))
        core_sku, is_p = strip_p(sku)
        b = base_sku(COMP_RE.sub('', core_sku)) if comp else base_sku(core_sku)
        nm = names.get(sku, '')
        if not nm:
            for k, v in names.items():
                if v and (k.startswith(b) or b.startswith(k)):
                    nm = v
                    break
        rows.append({
            '系列': family(nm),
            '主货号': b, '变体货号': sku, '物料名称': nm,
            '类型': kind(nm), 'X数': xfold(nm),
            '组分': '组分' if comp else '成品',
            'DG': 'Y' if (is_p or re.search(r'\(DG\)|（DG）', names.get(sku, ''))) else '',
            'NA': 'Y' if re.search(r'\(NA\)|（NA）', names.get(sku, '')) or re.search(r'-NA(-|$)', sku.upper()) else '',
            '2026销售额': round(amts.get(sku, 0) or 0, 2),
            '附件1排名': f1.get(sku, {}).get('rank', ''),
        })

    # 系列级汇总
    series = defaultdict(list)
    for r in rows:
        series[r['系列']].append(r)

    srows = []
    for fam, rs in series.items():
        finished = [r for r in rs if r['组分'] == '成品']
        comps = [r for r in rs if r['组分'] == '组分']
        amt = sum(r['2026销售额'] for r in rs)
        codes = sorted({r['主货号'] for r in rs})
        rep0 = pick_code(codes, amts, names)
        docs = sum(docs_of(r['变体货号'], f2)[0] for r in rs)
        core_have = set()
        for r in rs:
            for k, val in (docs_of(r['变体货号'], f2)[1] or {}).items():
                if val and val not in ('0', ''):
                    core_have.add(k)
        missing = [c for c in CORE if c not in core_have]
        base_pool = finished if finished else comps
        kd = Counter(r['类型'] for r in base_pool).most_common(1)[0][0] if base_pool else '其他'
        if kd == '其他' and comps:
            kd = '组分（' + '、'.join(sorted({c['类型'] for c in comps})) + '）'
        dg = any(r['DG'] for r in rs)
        na = any(r['NA'] for r in rs)
        srows.append({
            '代表货号': series_label(rep0), '基准货号': rep0, '产品系列': fam, '类型': kd,
            '主货号数': len(codes), '成品变体数': len(finished), '组分数': len(comps),
            'X数': '、'.join(sorted({r['X数'] for r in finished if r['X数']})) or '—',
            'DG': 'Y' if dg else '', 'NA': 'Y' if na else '',
            '2026销售额': round(amt, 2), '资料份数': docs,
            '资料状态': '未收录' if docs == 0 else ('齐全' if not missing else '部分'),
            '缺失核心项': '、'.join(missing) or '无',
            'DG专项参数': DG_PARAMS if dg else '',
            'NA专项参数': NA_PARAMS if na else '',
            '主货号清单': '、'.join(codes),
        })

    srows.sort(key=lambda r: -r['2026销售额'])
    used = {}
    for r in srows:
        lab = r['代表货号']
        if lab in used:
            used[lab] += 1
            r['代表货号'] = '%s-%d' % (lab, used[lab])
        else:
            used[lab] = 1

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

    ws = wb.active
    ws.title = '系列总表'
    h = ['代表货号', '基准货号', '产品系列', '类型', '主货号数', '成品变体数', '组分数', 'X数', 'DG', 'NA',
         '2026销售额', '资料份数', '资料状态', '缺失核心项', 'DG专项参数', 'NA专项参数', '主货号清单']
    ws.append(h)
    for r in srows:
        ws.append([r[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([14, 20, 34, 9, 9, 10, 8, 14, 5, 5, 13, 9, 9, 34, 60, 60, 70], 1):
        ws.column_dimensions[get_column_letter(i)].width = w
    ws.freeze_panes = 'B2'

    ws2 = wb.create_sheet('主货号与变体明细')
    h2 = ['代表货号', '产品系列', '主货号', '变体货号', '物料名称', '类型', 'X数', '组分', 'DG', 'NA', '2026销售额', '附件1排名']
    ws2.append(h2)
    rep_of = {r['产品系列']: r['代表货号'] for r in srows}
    for r in sorted(rows, key=lambda x: (-x['2026销售额'], x['主货号'])):
        ws2.append([rep_of.get(r['系列'], ''), r['系列'], r['主货号'], r['变体货号'], r['物料名称'], r['类型'],
                    r['X数'], r['组分'], r['DG'], r['NA'], r['2026销售额'], r['附件1排名']])
    for c in ws2[1]:
        c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True)
    for i, w in enumerate([14, 32, 22, 30, 46, 8, 8, 7, 5, 5, 13, 9], 1):
        ws2.column_dimensions[get_column_letter(i)].width = w
    ws2.freeze_panes = 'C2'

    ws3 = wb.create_sheet('系列缺口任务清单')
    h3 = ['任务ID', '代表货号', '产品系列', '类型', '成品变体数', '2026销售额(万)', '资料份数', '缺失核心项',
          'DG/NA专项', '优先级', '建议动作', '责任部门', '预计工时(人日)', '计划开始', '计划完成']
    ws3.append(h3)
    n = 0
    for r in srows:
        if r['缺失核心项'] == '无':
            continue
        n += 1
        sales = r['2026销售额']
        pri = 'P0' if sales >= 1000000 else ('P1' if sales >= 300000 else 'P2')
        items = r['缺失核心项'].split('、')
        special = []
        if r['DG']:
            special.append('DG可冻干参数')
        if r['NA']:
            special.append('NA低背景参数')
        action = ('先建系列档案，再补：' if r['资料份数'] == 0 else '补充：') + r['缺失核心项']
        if special:
            action += '；另需' + '、'.join(special)
        days = 2 * len(items) + (2 if r['资料份数'] == 0 else 0) + 2 * len(special)
        ws3.append([f'T{n:03d}', r['代表货号'], r['产品系列'], r['类型'], r['成品变体数'], round(sales / 1e4, 1),
                    r['资料份数'], r['缺失核心项'], '、'.join(special) or '—', pri, action, '产品部/研发', days, '', ''])
    for c in ws3[1]:
        c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True)
    for i, w in enumerate([8, 16, 32, 9, 10, 13, 9, 30, 20, 7, 52, 12, 13, 11, 11], 1):
        ws3.column_dimensions[get_column_letter(i)].width = w
    ws3.freeze_panes = 'A2'

    # 特殊货号待标记
    sp = json.load(open(SPECIAL, encoding='utf-8'))
    ws4 = wb.create_sheet('特殊货号待标记')
    ws4.append(['类别', '原货号', '去P后基础货号', '物料名称', '客户', '2026销售额', '订单数', '建议归属系列', '你的标记'])
    for sku, v in sorted(sp.items(), key=lambda x: -x[1]['amt']):
        if sku.startswith(('AHM', 'AHRM')):
            cat = 'AHM/AHRM 动物诊断'
        elif sku.startswith('2FM'):
            cat = '2FM 丽珠定制'
        elif sku == 'A3':
            cat = 'A3 欧易单细胞（单酶·逆转录酶）'
        elif re.match(r'^P[A-Z]', sku):
            cat = 'P 冻干球'
        else:
            cat = '其他'
        b = re.sub(r'-?(P\d+|R\d+|O\d+|S\d+|V\d+|T\d+|SCS\w*|PF-O25).*$', '', re.sub(r'^P(?=[A-Z])', '', sku))
        ws4.append([cat, sku, b, v['name'], '、'.join(v['cust'][:3]), v['amt'], v['orders'], '', ''])
    for c in ws4[1]:
        c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True)
    for i, w in enumerate([18, 36, 26, 52, 34, 13, 8, 18, 16], 1):
        ws4.column_dimensions[get_column_letter(i)].width = w
    ws4.freeze_panes = 'A2'

    ws5 = wb.create_sheet('特殊货号汇总')
    ws5.append(['类别', '基础货号', '物料名称示例', '变体数', '客户数', '2026销售额', '主要客户'])
    grp = defaultdict(list)
    for sku, v in sp.items():
        if sku.startswith(('AHM', 'AHRM')):
            cat, b = 'AHM/AHRM 动物诊断', sku
        elif sku.startswith('2FM'):
            cat, b = '2FM 丽珠定制', re.sub(r'-\d+-\d+$', '', sku)
        elif sku == 'A3':
            cat, b = 'A3 欧易单细胞（单酶·逆转录酶）', 'A3'
        else:
            cat = 'P 冻干球'
            b = re.sub(r'-?(P\d+|R\d+|O\d+|S\d+|V\d+|T\d+|SCS\w*|PF-O25).*$', '', re.sub(r'^P(?=[A-Z])', '', sku))
        grp[(cat, b)].append(v)
    for (cat, b), vs in sorted(grp.items(), key=lambda x: -sum(v['amt'] for v in x[1])):
        cust = Counter()
        for v in vs:
            for c in v['cust']:
                cust[c] += 1
        ws5.append([cat, b, vs[0]['name'][:60], len(vs), len(cust),
                    round(sum(v['amt'] for v in vs), 2), '、'.join([c for c, _ in cust.most_common(3)])])
    for c in ws5[1]:
        c.font = H; c.fill = F; c.alignment = Alignment(horizontal='center', wrap_text=True)
    for i, w in enumerate([24, 26, 46, 8, 8, 13, 44], 1):
        ws5.column_dimensions[get_column_letter(i)].width = w

    ws6 = wb.create_sheet('口径说明')
    for line in [
        ['系列定义', '按物料名称的产品系列归并（去掉 X 数、括号内的 DG/NA/配方号等修饰）；例：M2181-4、M2181-4-NA、M2181-4-095203、FM2181-4 同属 FastAmpli Premix-UNG Ⅳ'],
        ['代表货号', '同系列内优先取无 F 前缀、无 6 位配方号、长度最短、销售额最高的货号'],
        ['组分', '-1 酶Mix、-2 Buffer、-3 dNTP、-4 MgCl2、-5/-6 等，并入系列但标「组分」，不单独派验证任务'],
        ['资料颗粒度', '系列级一套资料，配方号只作属性；附件2 的 56 个货号资料按前缀归到系列'],
        ['DG/NA', 'DG=可冻干形态，NA=低核酸背景形态，合并入系列，但资料需单独列专项性能参数（见系列总表两列）'],
        ['缺失判定', '冻融/加速/效期/运输 四项核心资料，系列内任一变体有即视为具备'],
        ['优先级', 'P0：系列 2026 销售额 ≥100 万；P1：≥30 万；P2：其余'],
        ['待确认', '责任部门、工时与时间节点待你确认后出甘特图'],
    ]:
        ws6.append(line)
    ws6.column_dimensions['A'].width = 14
    ws6.column_dimensions['B'].width = 120

    wb.save(OUT)
    print('系列 %d ｜主货号 %d ｜变体 %d ｜任务 %d ｜特殊货号 %d' % (
        len(srows), len({r['主货号'] for r in rows}), len(rows), n, len(sp)))
    print('任务工时合计 %.0f 人日 ｜ P0 %d' % (sum(float(x[12] or 0) for x in ws3.iter_rows(min_row=2, values_only=True)),
                                        sum(1 for x in ws3.iter_rows(min_row=2, values_only=True) if x[9] == 'P0')))
    print('\nTop10 系列:')
    for r in srows[:10]:
        print('  %-10s %-36s %-4s 主货号%2d 成品%2d 组分%2d %7.1f万 资料%3d %s' % (
            r['代表货号'], r['产品系列'][:36], r['类型'], r['主货号数'], r['成品变体数'], r['组分数'],
            r['2026销售额'] / 1e4, r['资料份数'], r['资料状态']))
    print('✅', OUT)


if __name__ == '__main__':
    main()