# -*- coding: utf-8 -*-
"""A1 货号别名映射层 —— 构建 + 回归

用法:
  /usr/bin/python3 脚本/product_alias.py                 # 构建别名层 + 用 2026 订单回归
  /usr/bin/python3 脚本/product_alias.py --years 2024 2025 2026

产出:
  数据/product_aliases.json         别名层（规则/别名表/族表/名称表/统计/缺口）
  数据/product_alias_regression.json 回归报告（分年 / 分规则 / 未解析清单）
  数据/product_alias_gaps.csv       缺口清单（交产品部，按金额降序）

三级解析口径:
  L1 精确: 可定位到唯一主 sku（规格/组分/效期均可查）
  L2 族级: 可定位到产品族/候选（批次或定制规格待目录补全）
  L3 缺口: 目录外（外购件/耗材仪器/缺失产品线），有归因、需补数据
"""
import json, os, re, sys, glob, csv, collections
from datetime import datetime
import pandas as pd

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
D = os.path.join(ROOT, '数据')
ORD_DIR = os.path.normpath(os.path.join(ROOT, '..', 'hermas输入-工作台数据库'))
ORDER_SRC = {
    2026: os.path.join(ORD_DIR, '月度销售额数据', '销售订单_20260101-20260909.xlsx'),
    2025: os.path.join(ORD_DIR, '2024和2025历史数据', '2025全年订单.xlsx'),
    2024: os.path.join(ORD_DIR, '2024和2025历史数据', '2024全年订单.xlsx'),
}
CUST_SUFFIX = {'DB', 'TP', 'NA', '5P', '4P', '3P', '2P', 'P', 'S70', 'S25', 'S20', 'S50', 'TL', 'SLQ',
               'O25', 'O50', 'O20', 'O100', 'M01', 'M02', 'M03', 'P01', 'P02', 'R01', 'R02',
               'A', 'B', 'C', 'D', 'TS', 'DG', 'G1', 'G2', 'FP', 'T1', 'T4', 'T6', 'BR1', 'DW1'}
BATCH_RE = re.compile(r'^\d{6}$')
CODE_RE = re.compile(r'^\d+(\.\d+)+$')          # ERP 物料编码形态 2.05.01.0518
CONSUM_RE = re.compile(r'(样本管|混匀|吸头|离心管|管-|板|包装|试剂瓶|冻存|磁套|枪头|铝箔|封膜)')


def tx(v):
    s = str(v).strip()
    return '' if s in ('nan', 'None', 'NaT', '') else s


def num(v):
    try:
        return float(v)
    except Exception:
        return 0.0


def norm_name(n):
    """名称归一：去括号内容、去空格标点、小写"""
    n = re.sub(r'[（(][^）)]*[）)]', ' ', str(n or ''))
    n = re.sub(r'[\s\.。、,，×*™®\-_/]', '', n)
    return n.lower()


def name_overlap(order_name, master_name):
    """订单物料名 vs 主数据产品名 字符重合度（用于标记待复核映射）"""
    a = re.sub(r'[^0-9a-z\u4e00-\u9fff]', '', str(order_name or '').lower())
    b = re.sub(r'[^0-9a-z\u4e00-\u9fff]', '', str(master_name or '').lower())
    if not a or not b:
        return 0.0
    mset = set(re.sub(r'[0-9]{4,}', '', b) or b)
    return sum(1 for ch in mset if ch in a) / max(1, len(mset))


def find_catalog():
    for p in [os.path.join(ROOT, '知识库-copilot', '诊断原料', '产品资料',
                           '产品_分子检测类试剂产品清单_20260829.xlsx')]:
        if os.path.exists(p):
            return p
    cand = glob.glob(os.path.expanduser('~/.hermes/cache/documents/*分子检测类试剂产品清单*.xlsx'))
    return max(cand, key=os.path.getmtime) if cand else ''


# ---------------- 索引构建 ----------------
def build_indexes():
    M = json.load(open(os.path.join(D, 'product_master.json')))
    skus = M['skus']
    idx = {'skus': skus, 'ok': {e['sku'] for e in skus}}

    # 1) 源表三套编码 → 产品货号（别名池；区分「产品级编码」与「组分编码」）
    alias, akind = {}, {}
    cat = find_catalog()
    idx['catalog'] = os.path.basename(cat) if cat else ''
    if cat:
        sheets = []
        for si in range(3):
            sheets.append(pd.read_excel(cat, sheet_name=si, header=0, dtype=str))
        for df in sheets:
            f = df['产品货号'].ffill() if '产品货号' in df.columns else None
            for i, r in df.iterrows():
                self_code = tx(r.get('产品货号')) or (tx(f.loc[i]) if f is not None else '')
                if not self_code:
                    continue
                for c in ('产品对内货号', '产品生产货号', '产品编码'):
                    v = tx(r.get(c))
                    if v and v != self_code and v not in alias:
                        alias[v], akind[v] = self_code, '产品级'
                for c in ('组分货号', '组分对内货号', '组分生产货号', '组分编码', '组分旧编码'):
                    v = tx(r.get(c))
                    if v and v != self_code and v not in alias:
                        alias[v], akind[v] = self_code, '组分'
    idx['alias'], idx['alias_kind'] = alias, akind

    # 2) 族表（去 6 位批号段）+ 首段粗族 + 名称表
    fam, coarse, names = collections.defaultdict(set), collections.defaultdict(set), collections.defaultdict(set)
    for e in skus:
        sku = e['sku']
        segs = sku.split('-')
        keep = [s for s in segs if not BATCH_RE.match(s)]
        if len(keep) < len(segs):
            fam['-'.join(keep)].add(sku)
        coarse[segs[0]].add(sku)
        nk = norm_name(e.get('name'))
        if len(nk) >= 8:
            names[nk].add(sku)
    idx['family'] = {k: sorted(v) for k, v in fam.items()}
    idx['coarse'] = {k: sorted(v) for k, v in coarse.items()}
    idx['names'] = {k: sorted(v) for k, v in names.items()}
    idx['name_keys'] = sorted(names.keys(), key=len, reverse=True)
    return M, idx


def strip_variants(code):
    """迭代剥离尾部 批号段(纯数字) / 定制段"""
    out, cur, steps = [], code.split('-'), []
    while len(cur) > 1:
        t = cur[-1]
        if t.isdigit():
            r = '批号段'
        elif t.upper() in CUST_SUFFIX:
            r = '定制段'
        else:
            break
        cur = cur[:-1]
        steps.append(r)
        out.append(('-'.join(cur), '+'.join(steps)))
    return out


def subseq_hit(code, ok_codes):
    """主货号各段在订单码中按序出现（处理 组合编码 PFHW1007-P01-104101-O25-104102）"""
    segs = code.split('-')
    best = None
    for c in ok_codes:
        ms = c.split('-')
        if len(ms) < 3:
            continue
        i = 0
        for s in segs:
            if i < len(ms) and s == ms[i]:
                i += 1
        if i == len(ms) and (best is None or len(c) > len(best)):
            best = c
    return best


def make_resolver(idx):
    ok, alias, fam, coarse = idx['ok'], idx['alias'], idx['family'], idx['coarse']
    akind = idx['alias_kind']
    names, nkeys = idx['names'], idx['name_keys']

    def al_rule(c):
        return '源表别名(组分归一)' if akind.get(c) == '组分' else '源表别名'

    def resolve(code, nm=''):
        """返回 (level, rule, target)"""
        if code in ok:
            return 'L1', '主货号精确', code
        if code in alias and alias[code] in ok:
            return 'L1', al_rule(code), alias[code]
        for v, st in strip_variants(code):
            if v in ok:
                return 'L1', '后缀剥离:' + st, v
            if v in alias and alias[v] in ok:
                return 'L1', '后缀剥离+别名:' + st, alias[v]
        sq = subseq_hit(code, ok)
        if sq:
            return 'L1', '段子序列', sq
        nk = norm_name(nm)
        if len(nk) >= 8:
            if nk in names:
                s = names[nk]
                return ('L1', '名称精确', s[0]) if len(s) == 1 else ('L2', f'名称族({len(s)})', s[0])
            for k in nkeys:
                if k.startswith(nk) or nk.startswith(k):
                    s = names[k]
                    return ('L1', '名称前缀精确', s[0]) if len(s) == 1 else ('L2', f'名称前缀族({len(s)})', s[0])
        for v, st in strip_variants(code):
            if v in fam:
                return 'L2', '细族:' + st, fam[v][0]
        root = code.split('-')[0]
        if root in coarse:
            return 'L2', f'首段族({len(coarse[root])})', coarse[root][0]
        keep = [s for s in code.split('-') if not BATCH_RE.match(s)]
        if len(keep) < len(code.split('-')) and '-'.join(keep) in fam:
            return 'L2', '直接去批号段', fam['-'.join(keep)][0]
        return 'L3', '', ''
    return resolve


def gap_cause(tp, nm, code):
    if CONSUM_RE.search(nm):
        return '耗材/包装物料（非目录品，走物料台账）'
    if tp in ('NGS', '提取试剂', '仪器设备', 'mRNA产品'):
        return f'缺失产品线：{tp}（A2 待补目录）'
    if CODE_RE.match(code):
        return 'ERP 物料编码未映射（需产品部确认对应货号）'
    if tp in ('其他', ''):
        return '目录外品（类型未标注，待产品部确认）'
    return '待产品部确认（疑似未入目录/新货号）'


def load_orders(year):
    p = ORDER_SRC[year]
    if not os.path.exists(p):
        return None
    xl = pd.ExcelFile(p)
    sn = 'Sheet1' if 'Sheet1' in xl.sheet_names else xl.sheet_names[0]
    df = xl.parse(sn, dtype=str)
    return df


def aggregate(df):
    """按货号聚合：金额/行数/名称/类型/物料编码集/货号集"""
    agg = collections.defaultdict(lambda: {'amt': 0.0, 'rows': 0, 'name': '', 'type': '',
                                           'mc': set(), 'hk': set()})
    for _, r in df.iterrows():
        hk = tx(r.get('货号#'))
        mc = tx(r.get('物料编码'))
        c = hk or mc
        if not c:
            continue
        e = agg[c]
        e['amt'] += num(r.get('价税合计'))
        e['rows'] += 1
        if not e['name']:
            e['name'] = tx(r.get('物料名称'))
        if not e['type']:
            e['type'] = tx(r.get('产品类型#'))
        if mc:
            e['mc'].add(mc)
        if hk:
            e['hk'].add(hk)
    return agg


def main():
    years = [2026]
    if '--years' in sys.argv:
        i = sys.argv.index('--years')
        years = [int(x) for x in sys.argv[i + 1:] if x.isdigit()]
    M, idx = build_indexes()
    resolve = make_resolver(idx)
    sku_name = {s['sku']: s['name'] for s in idx['skus']}
    print('主数据 SKU:', len(idx['ok']), '| 源表:', idx['catalog'])
    print('别名池:', len(idx['alias']), '| 细族:', len(idx['family']), '| 首段族:', len(idx['coarse']), '| 名称键:', len(idx['names']))

    report = {'generated': datetime.now().strftime('%Y-%m-%d %H:%M'),
              'master': {'skus': len(idx['ok']), 'alias': len(idx['alias']),
                         'family': len(idx['family']), 'names': len(idx['names'])},
              'years': {}}
    all_gap, all_review = [], []
    aggs = {}
    for y in years:
        df = load_orders(y)
        if df is None:
            print(f'[{y}] 订单文件缺失，跳过'); continue
        aggs[y] = aggregate(df)
    # ---- pass 1: 逐年解析，并用已精确解析的「名称 → sku」建跨年名称表 ----
    res0, name_map = {}, collections.defaultdict(set)
    for y, agg in aggs.items():
        res0[y] = {}
        for code, e in agg.items():
            lv, rule, tgt = resolve(code, e['name'])
            res0[y][code] = (lv, rule, tgt)
            nk = norm_name(e['name'])
            if lv == 'L1' and len(nk) >= 8:
                name_map[nk].add(tgt)
    name_map = {k: sorted(v)[0] for k, v in name_map.items() if len(v) == 1}
    print('跨年名称表:', len(name_map), '条')

    for y, agg in aggs.items():
        co = collections.defaultdict(set)   # 物料编码 -> 货号#
        for c, e in agg.items():
            if len(e['hk']) == 1 and e['mc']:
                for mc in e['mc']:
                    co[mc] |= e['hk']
        amt, cnt, rule_amt = collections.Counter(), collections.Counter(), collections.Counter()
        gaps, review = [], []
        for code, e in agg.items():
            lv, rule, tgt = res0[y][code]
            if lv == 'L3' and code in co:                 # 订单内部共现：物料编码→货号#
                hk = sorted(co[code])
                if len(hk) == 1:
                    lv, rule, tgt = resolve(hk[0], e['name'])
                    if lv != 'L3':
                        rule = '订单内共现(物料编码→货号):' + rule
            if lv == 'L3':                                # 跨年同名称归一
                nk = norm_name(e['name'])
                if len(nk) >= 8 and nk in name_map:
                    lv, rule, tgt = 'L1', '跨年同名称', name_map[nk]
            amt[lv] += e['amt']; cnt[lv] += 1
            rule_amt[f'{lv}|{rule.split("(")[0]}'] += e['amt']
            # 复核清单：非精确规则的 L1 映射，若订单物料名与目标产品名重合 <45% → 待人工确认
            if lv == 'L1' and rule.split(':')[0] not in ('主货号精确',):
                rk = rule.split('(')[0].split(':')[0]
                if rk not in ('主货号精确',) and tgt in sku_name:
                    ov = name_overlap(e['name'], sku_name[tgt])
                    amt['REVIEW'] += e['amt']
                    if ov < 0.45:
                        review.append({'code': code, 'amt': round(e['amt'], 2), 'rows': e['rows'],
                                       'name': e['name'], 'target': tgt, 'target_name': sku_name[tgt],
                                       'rule': rule, 'overlap': round(ov, 2)})
            if lv == 'L3':
                gaps.append({'code': code, 'amt': round(e['amt'], 2), 'rows': e['rows'],
                             'name': e['name'], 'type': e['type'],
                             'cause': gap_cause(e['type'], e['name'], code)})

        tot = sum(e['amt'] for e in agg.values())
        gaps.sort(key=lambda x: -x['amt'])
        review.sort(key=lambda x: -x['amt'])
        hi = amt['L1'] - sum(r['amt'] for r in review)
        report['years'][y] = {
            'total_amt': round(tot, 2), 'codes': len(agg),
            'L1': {'amt': round(amt['L1'], 2), 'pct': round(amt['L1'] / tot * 100, 1), 'codes': cnt['L1']},
            'L1_high': {'amt': round(hi, 2), 'pct': round(hi / tot * 100, 1)},
            'review': {'amt': round(sum(r['amt'] for r in review), 2),
                       'pct': round(sum(r['amt'] for r in review) / tot * 100, 1),
                       'codes': len(review)},
            'L2': {'amt': round(amt['L2'], 2), 'pct': round(amt['L2'] / tot * 100, 1), 'codes': cnt['L2']},
            'L3': {'amt': round(amt['L3'], 2), 'pct': round(amt['L3'] / tot * 100, 1), 'codes': cnt['L3']},
            'cover': round((amt['L1'] + amt['L2']) / tot * 100, 1),
            'top_rules': [{'rule': k, 'amt': round(v, 2)} for k, v in rule_amt.most_common(14)],
            'gap_top': gaps[:60], 'review_top': review[:60],
        }
        print(f'\n[{y}] 金额 {tot/1e4:.1f} 万 / {len(agg)} 个货号码')
        print(f'  L1 精确 {amt["L1"]/1e4:>8.2f}万 {amt["L1"]/tot*100:>5.1f}% ({cnt["L1"]})  其中高置信 {hi/1e4:.2f}万 {hi/tot*100:.1f}%')
        print(f'  复核清单 {sum(r["amt"] for r in review)/1e4:>7.2f}万 {sum(r["amt"] for r in review)/tot*100:>5.1f}% ({len(review)} 码)')
        print(f'  L2 族级 {amt["L2"]/1e4:>8.2f}万 {amt["L2"]/tot*100:>5.1f}% ({cnt["L2"]})')
        print(f'  L3 缺口 {amt["L3"]/1e4:>8.2f}万 {amt["L3"]/tot*100:>5.1f}% ({cnt["L3"]}) -> 覆盖率 {(amt["L1"]+amt["L2"])/tot*100:.1f}%')
        print('  规则 TOP:')
        for k, v in rule_amt.most_common(8):
            print(f'    {k:<34} {v/1e4:>8.2f}万')
        all_gap += [{**g, 'year': y} for g in gaps]
        all_review += [{**g, 'year': y} for g in review]

    # 别名层落盘（只保留命中主货号的高置信映射）
    by_code = {}
    for code, tgt in idx['alias'].items():
        if tgt in idx['ok']:
            by_code[code] = {'sku': tgt, 'level': 'L1',
                             'rule': '源表别名(组分归一)' if idx['alias_kind'].get(code) == '组分' else '源表别名'}
    for x in idx['ok']:
        by_code[x] = {'sku': x, 'level': 'L1', 'rule': '主货号'}
    pack = {'generated': report['generated'], 'source': {'master': 'product_master.json',
            'catalog': idx['catalog'], 'orders': {y: os.path.basename(p) for y, p in ORDER_SRC.items()}},
            'levels': {'L1': '精确到主 sku', 'L2': '族级（候选）', 'L3': '目录外（有归因）'},
            'rules_desc': {
                '主货号精确': '订单货号 = 主数据产品货号',
                '源表别名/源表别名(组分归一)': '源表 产品对内货号/生产货号/产品编码 → 产品货号；组分编码/组分对内/生产/旧编码 → 父产品',
                '后缀剥离': '迭代剥离尾部 批号段(≥4位数字) / 定制段(DB/NA/5P/S70/O25…)',
                '段子序列': '主货号各段在订单码中按序出现（组合编码）',
                '名称精确/前缀': '物料名称归一后匹配主数据产品名称',
                '订单内共现': '同物料编码在订单内出现过的唯一货号',
                '跨年同名称': '该名称在 2024/2025/2026 任一年已精确解析 → 回填',
                '细族/首段族': '去 6 位批号段后的族键 / 货号首段'},
            'stats': report['years'], 'by_code': by_code, 'families': idx['family'],
            'coarse': idx['coarse'], 'name_index': idx['names'],
            'top_rules': report['years'].get(years[0], {}).get('top_rules', []),
            'gaps': all_gap[:400], 'review': sorted(all_review, key=lambda x: -x['amt'])[:300]}
    os.makedirs(D, exist_ok=True)
    json.dump(pack, open(os.path.join(D, 'product_aliases.json'), 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
    json.dump(report, open(os.path.join(D, 'product_alias_regression.json'), 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
    with open(os.path.join(D, 'product_alias_gaps.csv'), 'w', newline='', encoding='utf-8-sig') as f:
        w = csv.DictWriter(f, fieldnames=['year', 'code', 'amt', 'rows', 'name', 'type', 'cause'])
        w.writeheader()
        w.writerows(sorted(all_gap, key=lambda x: -x['amt']))
    with open(os.path.join(D, 'product_alias_review.csv'), 'w', newline='', encoding='utf-8-sig') as f:
        w = csv.DictWriter(f, fieldnames=['year', 'code', 'amt', 'rows', 'name', 'target', 'target_name', 'rule', 'overlap'])
        w.writeheader()
        w.writerows(sorted(all_review, key=lambda x: -x['amt']))
    print('\n产出:')
    for p in ('product_aliases.json', 'product_alias_regression.json', 'product_alias_gaps.csv', 'product_alias_review.csv'):
        fp = os.path.join(D, p)
        print(f'  {fp}  {os.path.getsize(fp):,} bytes')


if __name__ == '__main__':
    main()