#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
下单页数据构建（三合一：货号 · 历史成交价 · 特殊要求）

产出（均在 数据/ 下）：
  1. order_sku_index.json        货号索引：名称/类别/包装规格选项/单位/PF标记/价格分类/成交统计
  2. customer_price_history.json 客户×货号 历史成交价（**按单位族归并**，避免 ml 与 T 混价）
  3. customer_order_context.json 客户下单上下文：归属/分级/账期台账(归一化天数)/特殊要求/合同编号

关键口径（均为实测后确定，勿随意改）：
  - 价格必须按「单位族」归并：同一客户同一货号可能既有 15.0元/ml 又有 0.2元/T(50μl)，混算均价无意义
  - 账期需从 4 类提取源合并（customer_special_requirements + _erp_sr_extracted + _sr_extracted_s2/s3/s6/s7）
    并**必须含付款类关键词**才认定，否则「3个工作日备货后发货」会被误判成账期
  - 账期归一化为天数比较：预付款=0，N个工作日=N，1个月=30，2个月=60，其他=999
  - 2026 快照为累积文件，只能取最新一份；2025 全年订单有多张分月表会重叠 → 去重
"""
import pandas as pd, json, io, os, re, glob, collections
from datetime import datetime

WORK = os.path.expanduser('~/Desktop/Hermes输出-工作类')
IN = os.path.expanduser('~/Desktop/hermas输入-工作台数据库')
DATA = os.path.join(WORK, '数据')

NORM = {
    '客户': 'customer', '销售员': 'sales', '货号#': 'sku', '物料名称': 'name', '物料编码': 'matcode',
    '销售单位': 'unit', '销售数量': 'qty', '含税单价': 'price', '价税合计': 'amt',
    '合同日期': 'date', '合同月份': 'month', '单据编号': 'order', '单据类型': 'otype',
    '合同编号#': 'contract', '是否赠品': 'gift', '产品大类#': 'cat', '产品类型#': 'ptype',
    '客户物料规格型号#': 'cust_spec', '销售分组（二级）': 'grp',
}
SKU_RE = re.compile(r'^[A-Za-z0-9][A-Za-z0-9\-\.]{1,44}$')


# ─────────────── ERP ───────────────
def norm_cols(df):
    ren = {c: NORM[str(c).strip()] for c in df.columns if str(c).strip() in NORM}
    return df.rename(columns=ren)


def find_line_sheets(path):
    out = []
    try:
        xl = pd.ExcelFile(path)
    except Exception as e:
        print('   ⚠️ 打开失败 %s: %s' % (os.path.basename(path), e))
        return out
    for sn in xl.sheet_names:
        try:
            raw = pd.read_excel(path, sheet_name=sn, header=None, nrows=8)
        except Exception:
            continue
        for i in range(len(raw)):
            vals = [str(x) for x in raw.iloc[i].tolist()]
            if any('货号' in v for v in vals) and any(v.strip() == '客户' for v in vals):
                out.append((sn, i))
                break
    return out


def pick_erp_files():
    def end_of(f):
        m = re.search(r'-(\d{8})\.xlsx$', f)
        return m.group(1) if m else ''

    snap = [f for f in glob.glob(os.path.join(IN, '月度销售额数据', '销售订单_20260101-*.xlsx')) if end_of(f)]
    files = [max(snap, key=end_of)] if snap else []
    files += sorted(glob.glob(os.path.join(IN, '2024和2025历史数据', '*全年订单.xlsx')))
    return files


def load_erp():
    files = pick_erp_files()
    frames = []
    for f in files:
        for sn, hdr in find_line_sheets(f):
            try:
                df = pd.read_excel(f, sheet_name=sn, header=hdr)
            except Exception:
                continue
            df = norm_cols(df)
            if 'sku' not in df.columns or 'customer' not in df.columns:
                continue
            df['_src'] = '%s[%s]' % (os.path.basename(f), sn)
            frames.append(df)
            print('   ✓ %-44s 行%6d 列%d' % ('%s[%s]' % (os.path.basename(f), sn), len(df), len(df.columns)))
    if not frames:
        raise SystemExit('未找到 ERP 行级数据')
    big = pd.concat(frames, ignore_index=True)
    for c in ['customer', 'sku', 'name', 'unit', 'order', 'contract', 'cat', 'ptype', 'cust_spec',
              'sales', 'otype', 'gift', 'grp', 'matcode']:
        big[c] = big[c].astype(str).str.strip().replace({'nan': '', 'None': ''}) if c in big.columns else ''
    for c in ['qty', 'price', 'amt']:
        big[c] = pd.to_numeric(big[c], errors='coerce') if c in big.columns else 0

    def to_month(r):
        for k in ('date', 'month'):
            v = r.get(k)
            if isinstance(v, str) and re.match(r'^\d{4}-\d{2}', v):
                return v[:7]
            if isinstance(v, (pd.Timestamp, datetime)):
                return v.strftime('%Y-%m')
        return ''

    big['ym'] = big.apply(to_month, axis=1)
    big = big[big['sku'].str.match(SKU_RE, na=False)]
    before = len(big)
    big = big.drop_duplicates(subset=['order', 'sku', 'qty', 'price', 'unit', 'ym'], keep='first')
    print('   去重 %d → %d 行' % (before, len(big)))
    return big


# ─────────────── 货号分类与包装规格 ───────────────
def classify_sku(name, bom_group, units, max_ml=0):
    """按用户口径分三类 → 决定标准包装规格
    T 族（T(50μl)/T(25μl)）→ 预混液类；ml 族再按 BOM 组分 / 历史单笔量级区分单酶 vs 单组分"""
    n = name or ''
    fams = set('T' if str(u).startswith('T(') else ('ml' if str(u) in ('ml', 'μl', 'ul') else str(u)) for u in units)
    if re.search(r'premix|mastermix|mix\b|预混|混合液', n, re.I) or 'T' in fams:
        return 'premix', ['100T', '1000T', '10000T']
    if bom_group == '单组份' or (max_ml and max_ml >= 10):
        return 'component', ['1ml', '5ml', '10ml', '40ml', '100ml']
    return 'enzyme', ['0.1ml', '1ml', '5ml']


SPEC_FAMILY = {}   # 规格 → 单位族
for _t in ['100T', '1000T', '10000T']:
    SPEC_FAMILY[_t] = 'T'
for _v in ['0.1ml', '1ml', '5ml', '10ml', '40ml', '100ml']:
    SPEC_FAMILY[_v] = 'ml'


def unit_family(u):
    u = (u or '').strip()
    if u.startswith('T('):
        return 'T'
    if u in ('ml', 'μl', 'ul', 'L'):
        return 'ml'
    return u or '未知'


# ─────────────── 账期解析 ───────────────
TERM_CUE = re.compile(r'账期|预付|款到|月结|见票|款期|付款|先款|结款|货款')
WORKDAY = re.compile(r'(\d{1,3})\s*个?\s*工作日')
CN_MONTH = [('三个月', 90), ('3个月', 90), ('两个月', 60), ('2个月', 60), ('一个月', 30), ('1个月', 30)]


def parse_terms(text):
    """→ [(档位文案, 折算天数)]；非付款语境一律不认定，且排除「三个月内生产的效期」这类误命中"""
    t = text or ''
    if not TERM_CUE.search(t):
        return []
    out = []
    if re.search(r'预付款|预付|先款|款到|先款后货|现款', t):
        out.append(('预付款', 0))
    for m in WORKDAY.finditer(t):
        tail = t[m.end():m.end() + 8]
        if re.search(r'备货|发货|出库|生产|到货|入库|内发', tail):
            continue                                  # 「3个工作日备货后发货」是发货时效，不是账期
        out.append(('%s个工作日' % m.group(1), int(m.group(1))))
    for k, lbl, d in (('三个月', '3个月', 90), ('3个月', '3个月', 90), ('两个月', '2个月', 60), ('2个月', '2个月', 60),
                      ('一个月', '1个月', 30), ('1个月', '1个月', 30)):
        for m in re.finditer(re.escape(k), t):
            tail = t[m.end():m.end() + 8]
            if re.search(r'内生产|效期|保质|有效|内用完|内使用', tail):
                continue                              # 「三个月内生产的效期」是效期要求，不是账期
            out.append((lbl, d))
    if '月结' in t:
        out.append(('1个月（月结）', 30))
    return out


TERM_DAYS = {'预付款': 0, '7个工作日': 7, '1个月': 30, '2个月': 60, '其他': 999}


def terms_days(label):
    if label in TERM_DAYS:
        return float(TERM_DAYS[label])
    m = re.match(r'(\d+)个工作日', label or '')
    if m:
        return float(m.group(1))
    return 999.0


# ─────────────── 主流程 ───────────────
def main():
    print('═══ ① 读取 ERP 行级订单 ═══')
    big = load_erp()
    latest_ym = max([m for m in big['ym'] if m] or ['2026-09'])
    print('   合计 %d 行 ｜ 货号 %d ｜ 客户 %d ｜ 最新月份 %s' %
          (len(big), big['sku'].nunique(), big['customer'].nunique(), latest_ym))

    # ② 货号索引
    print('\n═══ ② 货号索引 ═══')
    pm = json.load(io.open(os.path.join(DATA, 'product_master.json'), encoding='utf-8'))
    _raw = pm.get('skus', [])
    pmskus = _raw if isinstance(_raw, dict) else {x['sku']: x for x in _raw if isinstance(x, dict) and x.get('sku')}
    print('   主数据 %d 条' % len(pmskus))
    name_map, unit_map, grp_map, maxml_map = {}, collections.defaultdict(set), {}, {}
    for s, g in big.groupby('sku'):
        nm = g['name'].value_counts()
        if len(nm):
            name_map[s] = nm.index[0]
        for u in g['unit'].unique():
            if u and u != 'nan':
                unit_map[s].add(u)
        gg = g['grp'].value_counts()
        if len(gg) and gg.index[0]:
            grp_map[s] = gg.index[0]
        gm = g[(g['unit'] == 'ml') & (g['qty'] > 0)]['qty']
        if len(gm):
            maxml_map[s] = float(gm.max())
    skus, cls_count = {}, collections.Counter()
    for s in sorted(set(big['sku']) | set(x for x in (pmskus.keys()))):
        if not SKU_RE.match(s):
            continue
        p = pmskus.get(s) or {}
        nm = name_map.get(s) or p.get('name') or ''
        units = set(unit_map.get(s, set())) | ({str(p['unit'])} if p.get('unit') else set())
        cls, opts = classify_sku(nm, p.get('bom_group') or '', units, maxml_map.get(s, 0))
        cls_count[cls] += 1
        g = big[big['sku'] == s]
        skus[s] = {
            'sku': s, 'name': nm, 'cat': p.get('cat') or (g['cat'].iloc[0] if len(g) and g['cat'].iloc[0] else ''),
            'bom': p.get('bom_group') or '', 'line': p.get('line') or '', 'life': p.get('life_months'),
            'unit': '/'.join(sorted(u for u in units if u and u != 'nan')[:3]),
            'spec_class': cls, 'spec_options': opts, 'spec': p.get('spec') or '',
            'grp': grp_map.get(s, ''), 'is_pf': s.upper().startswith('PF'),
            'orders': int(g['order'].nunique()) if len(g) else 0,
            'customers': int(g['customer'].nunique()) if len(g) else 0,
        }
    print('   货号 %d ｜ 分类 %s ｜ PF %d' %
          (len(skus), dict(cls_count), sum(1 for v in skus.values() if v['is_pf'])))
    io.open(os.path.join(DATA, 'order_sku_index.json'), 'w', encoding='utf-8').write(json.dumps(
        {'generated': datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 'latest_month': latest_ym,
         'count': len(skus), 'skus': skus}, ensure_ascii=False))

    # ③ 客户×货号 历史成交价（按单位族）
    print('\n═══ ③ 历史成交价（单位族归并）═══')
    px = big[(big['price'] > 0) & (big['gift'] != '是') & (big['customer'] != '')].copy()
    px['fam'] = px['unit'].map(unit_family)
    y, m = int(latest_ym[:4]), int(latest_ym[5:7])
    cut = '%04d-%02d' % (y - 1, m)
    hist, pair_n = {}, 0
    def _mc_of(v):
        v = str(v or '').strip()
        return '' if v in ('', 'nan', 'None') else v

    for (cust, sku), g in px.groupby(['customer', 'sku']):
        # 物料编码：ERP 的「物料编码」与「货号#」不是一一对应（改版/多包装会有多个），
        # 所以既对每笔成交记当笔编码（mc），也按客户×货号收集全部编码（mcs，新→旧）。
        gs_ = g.sort_values(['ym', 'order'])
        mcs = []
        if 'matcode' in gs_.columns:
            for _m in list(gs_['matcode'])[::-1]:
                _m = _mc_of(_m)
                if _m and _m not in mcs:
                    mcs.append(_m)
        mcs = mcs[:6]
        fams = {}
        for fam, gf in g.groupby('fam'):
            gf = gf.sort_values(['ym', 'order'])
            last = gf.iloc[-1]
            r12 = gf[gf['ym'] >= cut]
            if len(r12):
                q = r12['qty'].fillna(0)
                w = round(float((r12['price'] * q).sum() / q.sum()), 6) if q.sum() else round(float(r12['price'].mean()), 6)
            else:
                w = None
            fams[fam] = {
                'last': {'p': round(float(last.price), 6), 'u': last.unit, 'd': last.ym, 'no': last.order,
                         'q': float(last.qty) if pd.notna(last.qty) else 0, 'mc': _mc_of(last.matcode)},
                'avg12m': w, 'cnt': int(len(gf)),
                'deals': [{'p': round(float(r.price), 6), 'u': r.unit, 'q': float(r.qty) if pd.notna(r.qty) else 0,
                           'd': r.ym, 'no': r.order, 'mc': _mc_of(r.matcode)} for r in gf.itertuples()],
            }
        if fams:
            hist.setdefault(cust, {})[sku] = {'fams': fams, 'units': sorted(set(g['unit']))[:4], 'mcs': mcs}
            pair_n += 1
    print('   客户 %d ｜ 客户×货号 %d ｜ 单单位 %d ｜ 多单位族 %d' %
          (len(hist), pair_n,
           sum(1 for c in hist.values() for v in c.values() if len(v['fams']) == 1),
           sum(1 for c in hist.values() for v in c.values() if len(v['fams']) > 1)))
    io.open(os.path.join(DATA, 'customer_price_history.json'), 'w', encoding='utf-8').write(json.dumps(
        {'generated': datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 'latest_month': latest_ym,
         'count': len(hist), 'hist': hist}, ensure_ascii=False))

    # ④ 客户下单上下文
    print('\n═══ ④ 客户下单上下文（含账期台账）═══')
    auth = json.load(io.open(os.path.join(DATA, 'pricing', '_auth_customers_20260831.json'), encoding='utf-8'))['customers']
    mc = json.load(io.open(os.path.join(DATA, 'mcp_customers.json'), encoding='utf-8'))
    grade = {}
    for c in (mc['customers'] if isinstance(mc, dict) else mc):
        gl = c.get('field_17186c')
        grade[c['name']] = gl.get('label') if isinstance(gl, dict) else (gl or '')

    # 账期证据：合并 5 个源
    ev = collections.defaultdict(list)
    sp = json.load(io.open(os.path.join(DATA, 'customer_special_requirements.json'), encoding='utf-8'))
    for it in sp['items']:
        c, t = it.get('customer'), it.get('content') or ''
        if c and t:
            ev[c].append({'cat': it.get('category'), 't': t[:220], 'src': '特殊要求库'})
    for f in ['_erp_sr_extracted.json', '_erp_sr_extracted_s6.json', '_erp_sr_extracted_s7.json',
              '_sr_extracted_s2.json', '_sr_extracted_s3.json']:
        p = os.path.join(DATA, f)
        if not os.path.exists(p):
            continue
        d = json.load(io.open(p, encoding='utf-8'))
        if isinstance(d, dict):                       # 这些文件都是 {total/by_doc/by_activity, items:[...]}
            arr = d.get('items') or d.get('entries') or d.get('records') or []
        else:
            arr = d
        for x in arr:
            if not isinstance(x, dict):
                continue
            c, t = x.get('客户'), x.get('内容') or x.get('依据') or ''
            if c and t:
                ev[c].append({'cat': x.get('类别'), 't': t[:220], 'src': f.replace('.json', '')})
    # 同一客户同一句话可能来自多个源 → 去重（保留最先出现的源）
    for c in list(ev):
        seen, uniq = set(), []
        for x in ev[c]:
            key = re.sub(r'\s+', '', x['t'])
            if key in seen:
                continue
            seen.add(key)
            uniq.append(x)
        ev[c] = uniq
    sr_by_cust = collections.defaultdict(lambda: {'by_sku': collections.defaultdict(list), 'common': []})
    terms = collections.defaultdict(list)
    for c, arr in ev.items():
        for x in arr:
            for lbl, d in parse_terms(x['t']):
                terms[c].append({'label': lbl, 'days': d, 'raw': x['t'][:150], 'src': x['src']})
    for it in sp['items']:
        c, t = it.get('customer'), (it.get('content') or '').strip()
        if not c or not t:
            continue
        e = sr_by_cust[c]
        for s in [x for x in (it.get('skus') or []) if x]:
            e['by_sku'][s].append(t[:200])
        if not (it.get('skus') or []):
            e['common'].append({'cat': it.get('category'), 't': t[:200]})
    cno = big[big['contract'] != ''].groupby('customer')['contract'].apply(lambda s: list(s.value_counts().index[:3]))
    ctx = {}
    for cust, info in auth.items():
        s = sr_by_cust.get(cust) or {'by_sku': {}, 'common': []}
        tl = terms.get(cust, [])
        # 「仅限本单/此次/一次性」的账期是单次特批，不能当作长期授权。
        # 但只对**比基线更宽松**的档位生效：预付款(0天)是最严档位，任何情况下都必须保留，
        # 否则会被放松成销售员默认档位（方向性错误）。标记词也要收紧，避免「或该订单不打款」这类条件从句误伤。
        ONEOFF = re.compile(r'仅限本单|只限本单|本单有效|本单专用|本单适用|本单享受|此次订单|本次订单|仅此一单|一次性')
        standing = [x for x in tl if x['days'] == 0 or not ONEOFF.search(x['raw'])]
        best = max(standing, key=lambda x: x['days']) if standing else None
        # 有付款语境但**没**解析出明确账期的原话 → 作为补充依据给审批人
        parsed_raw = set(re.sub(r'\s+', '', x['raw']) for x in tl)
        pay_notes = [{'t': x['t'][:180], 'src': x['src']} for x in (ev.get(cust) or [])
                     if TERM_CUE.search(x['t']) and re.sub(r'\s+', '', x['t'])[:150] not in parsed_raw]
        ctx[cust] = {
            'sp': info.get('sp', ''), 'line': info.get('line', ''), 'dept': info.get('dept', ''),
            'group': info.get('group', ''), 'grade': grade.get(cust, ''),
            'terms': [dict(x) for x in tl][:6],
            'terms_granted': (best['label'] if best else ''),
            'terms_days': (best['days'] if best else None),
            'pay_notes': pay_notes[:3],
            'sr_common': s['common'][:6],
            'sr_by_sku': {k: v[:3] for k, v in list(s['by_sku'].items())},
            'contracts': list(cno.get(cust, [])),
        }
    print('   客户 %d ｜ 有账期台账 %d ｜ 有按货号特殊要求 %d' %
          (len(ctx), sum(1 for v in ctx.values() if v['terms']), sum(1 for v in ctx.values() if v['sr_by_sku'])))
    io.open(os.path.join(DATA, 'customer_order_context.json'), 'w', encoding='utf-8').write(json.dumps(
        {'generated': datetime.now().strftime('%Y-%m-%d %H:%M:%S'), 'count': len(ctx), 'ctx': ctx}, ensure_ascii=False))

    print('\n═══ 产物 ═══')
    for f in ['order_sku_index.json', 'customer_price_history.json', 'customer_order_context.json']:
        print('   %-32s %7.2f MB' % (f, os.path.getsize(os.path.join(DATA, f)) / 1048576.0))


if __name__ == '__main__':
    main()