#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
ERP 特殊要求：把每条要求回连到它真正的单据（修 单据编号 / 日期 / 出现次数）
============================================================================
背景（bug）：extract_erp_remarks.py 生成条目时，把「单据编号/日期/销售员/出现次数」
按**该批次第 1 条候选**统一填给了整批 —— 一批 20 条里只有第 1 条的元数据是对的。
模型输出里没有单据号，所以正确的做法是：用条目的「备注原文依据」反查候选库
（客户+备注文本 去重后的 5,806 组），回填真实单据号、日期，并按**单据编号去重**统计出现次数。

用法：python3 repair_erp_links.py [--apply]
"""
import os
import re
import sys
import json
import glob
from collections import defaultdict

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
LIB = os.path.join(ROOT, '数据', 'customer_special_requirements.json')
REVIEW = os.path.join(ROOT, '数据', 'sr_review.json')


def _s(v):
    if v is None:
        return ''
    t = str(v).strip()
    return '' if t.lower() in ('nan', 'none', 'null', 'nat') else t


def norm(s):
    return re.sub(r'[\s，。；;、：:（）()【】\[\]！!？?\-—_/]+', '', _s(s))


def load_groups():
    """候选组：(客户, 备注文本) → 单据号集合 + 每组抽一条代表单据的日期"""
    import pandas as pd
    import warnings
    warnings.filterwarnings('ignore')
    paths = sorted(glob.glob(os.path.join(ROOT, '数据', '**', '销售订单导出*.xlsx'), recursive=True))
    seen_row = set()
    groups = defaultdict(set)
    meta = {}
    for p in paths:
        try:
            d = pd.read_excel(p)
        except Exception:
            continue
        col = lambda k: next((c for c in d.columns if k in str(c)), None)
        c_cust, c_doc = col('销售订单关联客户'), col('单据编号')
        c_i, c_c, c_sku = col('备注(内部)'), col('备注(客户)'), col('货号#')
        if not (c_cust and (c_i or c_c)):
            continue
        for _, r in d.iterrows():
            cust = _s(r.get(c_cust))
            if not cust or '「' in cust:
                continue
            txt = '\n'.join(x for x in (_s(r.get(c_i)), _s(r.get(c_c))) if x)
            if len(txt) < 4:
                continue
            doc = _s(r.get(c_doc))
            key = (doc, _s(r.get(c_sku)), norm(txt))
            if key in seen_row:
                continue
            seen_row.add(key)
            gk = (cust, norm(txt))
            groups[gk].add(doc)
            if gk not in meta:
                m = re.search(r'ZHBR(\d{2})(\d{2})(\d{2})', doc)
                meta[gk] = '20%s-%s-%s' % m.groups() if m else ''
    return groups, meta


def build_index(groups):
    """10 字滑窗倒排索引：原文 → 候选组（用于把条目的备注原文反查回候选组）"""
    idx = {}
    for gk, t in ((k, k[1]) for k in groups):
        if len(t) < 10:
            idx.setdefault(t, []).append(gk)
            continue
        for j in range(0, len(t) - 9, 3):
            idx.setdefault(t[j:j + 10], []).append(gk)
    return idx


def find_group(idx, cust, evidence):
    """按原文反查候选组：取命中窗口最多、且客户名互相包含的那个"""
    t = norm(evidence)
    if len(t) < 6:
        return None
    score = defaultdict(int)
    if len(t) < 10:
        for gk in idx.get(t, []):
            score[gk] += 3
    for j in range(0, max(1, len(t) - 9), 3):
        for gk in idx.get(t[j:j + 10], []):
            score[gk] += 1
    if not score:
        return None
    c = norm(cust)
    best, bs, best_ok = None, 0, False
    for gk, s in score.items():
        gc = norm(gk[0])
        ok = bool(c and gc and (c in gc or gc in c))
        if ok:
            s += 2                        # 客户名互相包含（ERP 全称 vs 库里简称）
        if s > bs or (s == bs and ok and not best_ok):
            bs, best, best_ok = s, gk, ok
    # 短原文（<10 字）歧义大，必须客户名对得上；长原文允许靠文本本身命中
    if len(t) < 10:
        return best if (best_ok and bs >= 2) else None
    return best if (best_ok or bs >= 5) else None


def main(apply):
    groups, meta = load_groups()
    idx = build_index(groups)
    print('  候选组 %d ｜ 索引窗口 %d ｜ 文档总数(去重后) %d' % (len(groups), len(idx), len({d for v in groups.values() for d in v})))
    stat = {'linked': 0, 'hits_gt1': 0, 'miss': 0}
    for path, is_lib in ((LIB, True), (REVIEW, False)):
        d = json.load(open(path, encoding='utf-8'))
        rows = d.get('items') if is_lib else d.get('rows')
        for it in rows:
            cust = it.get('customer') or ''
            ev = _s(it.get('note') if is_lib else it.get('evidence'))
            ev = re.sub(r'^(备注原文|依据原文)[：:]\s*', '', ev)
            ev = re.split(r'（该要求在', ev)[0].replace('📎', '')
            gk = find_group(idx, cust, ev)
            if not gk:
                # 回连不上（模型改写过原文）→ 原来的单据号是「批次第 1 条」的，必错，清掉不留假号
                stat['miss'] += 1
                if is_lib:
                    it['source'] = 'ERP订单 单据号待核（按备注原文核对）'
                    it.pop('hits', None)
                else:
                    it['activity_id'] = ''
                continue
            docs = sorted(d for d in groups[gk] if d)
            real = [d for d in docs if not d.upper().startswith('FO')]   # 优先真实销售订单，预测单(FO)只做兜底
            doc = (real or docs)[0] if (real or docs) else ''
            date = meta.get(gk) or ''
            if is_lib:
                it['source'] = 'ERP订单 %s（%s）' % (doc or '无单据号', date or '日期未知')
                note = re.sub(r'（该要求在 \d+ 张订单中出现）', '', it.get('note') or '')
                it['hits'] = len(docs) or 1
                it['note'] = note + (('（该要求在 %d 张订单中出现）' % len(docs)) if len(docs) > 1 else '')
            else:
                it['activity_id'] = doc
                it['activity_date'] = date or it.get('activity_date') or ''
                it['hits'] = len(docs) or 1
            stat['linked'] += 1
            if len(docs) > 1:
                stat['hits_gt1'] += 1
        if is_lib:
            d['version'] = int(d.get('version') or 1) + 1
        if apply:
            json.dump(d, open(path, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
    print('  ✅ 回连成功 %d 条 ｜ 其中重复出现(>1张订单) %d 条 ｜ 未能回连 %d 条' % (stat['linked'], stat['hits_gt1'], stat['miss']))
    if not apply:
        print('  （预览模式，未写入；加 --apply 生效）')


if __name__ == '__main__':
    main('--apply' in sys.argv[1:])