#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""用最新 ERP 全年主档 + 历史 LS 底表重建工作台「课题组」Tab 数据。

数据源：
  历史底表 销售分析/源数据/生命科学销售订单_20261009_含作废终止标记.xlsx（保留 2024-12-27 ~ 2025-12-31）
  最新主档 erp_sales_core.find_latest_erp()（2026 全年，自动跟随 ERP 上传）
> 原脚本硬编码 2026-10-08 快照，导致 ERP 更新到 10-27 后课题组数据仍停在 10-08。

用户 2026-10-08 拍板：
  Q1=B   以本表【标准口径】作为 LS 销售额来源
  Q2     离职者业绩按区域规则归并（浙→吴云）
  Q3     王慧洁/刘静文/李仁钰（杭州·离职）→ 吴云；徐中平（国际业务部）→ 不统计
  Q4     本轮只改课题组 Tab

产出：
  数据/ls_groups.json             课题组档案 + 采购实据（前台「课题组」Tab 读它）
  数据/ls_ketizu_purchase.json    课题组采购台账（674 个）
  数据/order_ketizu_map.json      单据编号→课题组（1.5% → 75.4%）
  数据/_ls_intake_report.json     入库审计（归并/剔除/异常，可溯源）
"""
import io
import json
import os
import sys
from collections import Counter, defaultdict

import pandas as pd

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
os.chdir(ROOT)
SPECIAL = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/销售分析/源数据/生命科学销售订单_20261009_含作废终止标记.xlsx'

def _load_latest_erp():
    """优先取最新全年 ERP 主档；避免误取增量/局部导出。"""
    import importlib.util
    cp = os.path.join(ROOT, '脚本', 'erp_sales_core.py')
    spec = importlib.util.spec_from_file_location('erp_core_ls', cp)
    core = importlib.util.module_from_spec(spec)
    spec.loader.exec_module(core)
    fp = core.find_latest_erp()
    if not fp:
        raise SystemExit('未找到 ERP 销售订单文件')
    return fp

LATEST = _load_latest_erp()

# ── 口径与归并（用户拍板）────────────────────────────────────────────
CALIBER = '标准销售订单 & 已审核 & 非赠品 & 客户不含(宝锐|宝泰|横琴) & 未作废 & 非业务终止'
EXCLUDE_SALES = {'徐中平'}                       # 国际业务部，不统计
OWNER_MERGE = {                                   # 离职 → 区域经理
    '王慧洁': '吴云', '刘静文': '吴云', '李仁钰': '吴云',
    '李岚风': '吴云', '赵以诺': '吴云', '曾琪': '吴云',
}
SALES_REGION = {
    '吴云': '浙江', '叶信福': '浙江', '侯海瑜': '浙江', '袁殿果': '浙江',
    '韩远怀': '广东', '王诗香': '广东', '刘斌': '广东', '陈彩红': '广东',
    '黄建华': '广东',      # 依据：MCP 商机视图 _rz-opbvg 中其名下 8 个课题组 region 全为「广东」
}
for _k, _v in OWNER_MERGE.items():
    SALES_REGION[_k] = SALES_REGION.get(_v, '')

# ── 读数据 ────────────────────────────────────────────────────────
hist = pd.read_excel(SPECIAL, sheet_name='Sheet1', dtype=str)
latest = pd.read_excel(LATEST, sheet_name=0, dtype=str)
for _df in (hist, latest):
    for c in _df.columns:
        _df[c] = _df[c].astype(str).str.strip().replace({'nan': '', 'None': '', 'NaT': ''})
    _df['_d'] = pd.to_datetime(_df['合同日期'], errors='coerce', format='mixed')
# 历史文件只保留 2025 及更早；2026 一律以最新 ERP 主档为准，避免重叠/重复
LS_GROUPS = {g for g in set(hist['销售分组（二级）']) if g.startswith('生命科学') or g == '生物医药-mRNA原料'}  # 仅生命科学/科研 mRNA 范围
hist = hist[hist['_d'] < pd.Timestamp('2026-01-01')].copy()
latest = latest[(latest['_d'] >= pd.Timestamp('2026-01-01')) & (latest['销售分组（二级）'].isin(LS_GROUPS))].copy()
df = pd.concat([hist, latest], ignore_index=True, sort=False)
for c in ('作废状态', '业务终止'):
    if c not in df.columns:
        df[c] = ''
for c in df.columns:
    if c != '_d':
        df[c] = df[c].fillna('').astype(str).str.strip().replace({'nan': '', 'None': '', 'NaT': ''})
raw_rows = len(df)
df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0.0)
df['_d'] = pd.to_datetime(df['合同日期'], errors='coerce', format='mixed')

excluded_rows = df['销售员'].isin(EXCLUDE_SALES)
n_excluded, amt_excluded = int(excluded_rows.sum()), float(df.loc[excluded_rows, '_amt'].sum())
df = df.loc[~excluded_rows].copy()

df['_std'] = ((df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核')
              & (df['是否赠品'] != '是')
              & (~df['客户'].str.contains('宝锐|宝泰|横琴', na=False)) & (~df['作废状态'].isin(['已作废'])) & (~df['业务终止'].isin(['业务终止'])))
df['_orig_sales'] = df['销售员']                          # 保留原销售员
df['_sales'] = df['销售员'].map(lambda x: OWNER_MERGE.get(x, x))   # 归并后

ketizu_list = json.load(io.open('数据/ketizu_list.json', encoding='utf-8'))['ketizu']
K_SET = set(ketizu_list)
old_ls = json.load(io.open('数据/ls_groups_mcp.json', encoding='utf-8'))   # 冻结的 MCP 底表（勿读产出物，保证可重复运行）

# ── 课题组归属清洗 ────────────────────────────────────────────────
df['_kz'] = df['商机(飞书)#'].where(df['商机(飞书)#'].isin(K_SET), '')
bad_kz = sorted(set(df.loc[(df['商机(飞书)#'] != '') & (df['_kz'] == ''), '商机(飞书)#']))
bad_kz_rows = int(((df['商机(飞书)#'] != '') & (df['_kz'] == '')).sum())

amt_all = float(df.loc[df['_std'], '_amt'].sum())          # 含非课题组值行
kzd = df.loc[df['_kz'] != '']
amt_kz = float(kzd.loc[kzd['_std'], '_amt'].sum())         # 仅课题组口径
period = [str(df['_d'].min().date()), str(df['_d'].max().date())]
AS_OF = df['_d'].max()
P25_END = AS_OF.replace(year=AS_OF.year - 1)   # 同比窗口与最新数据日对齐（如 2025-10-27）

# ── 课题组维度聚合 ────────────────────────────────────────────────
rows = []
for name, g in kzd.groupby('_kz'):
    st = g.loc[g['_std']]
    skus = g.loc[g['货号#'] != '', '货号#'].value_counts()
    top = []
    if len(st):
        sa = st.groupby('货号#')['_amt'].sum().sort_values(ascending=False)
        top = [{'sku': k, 'amt': round(float(v), 2)} for k, v in list(sa.items())[:5] if k]
    sales_c = g['_sales'].value_counts()
    rows.append({
        'name': name,
        'sales': sales_c.index[0] if len(sales_c) else '',
        'sales_all': sorted(set(g['_sales'])),
        'owner_merged_from': sorted(set(g.loc[g['_orig_sales'] != g['_sales'], '_orig_sales'])),
        'region': SALES_REGION.get(sales_c.index[0] if len(sales_c) else '', ''),
        'amt': round(float(st['_amt'].sum()), 2),
        'orders': int(g['单据编号'].nunique()), 'orders_paid': int(st['单据编号'].nunique()),
        'rows': int(len(g)), 'test_rows': int((g['单据类型'] == '测试销售订单').sum()),
        'gift_rows': int((g['是否赠品'] == '是').sum()),
        'skus': int(skus.shape[0]),
        'customers': sorted(set(g.loc[g['客户'] != '', '客户'])),
        'first_deal': str(st['_d'].min().date()) if len(st) and pd.notna(st['_d'].min()) else '',
        'last_deal': str(st['_d'].max().date()) if len(st) and pd.notna(st['_d'].max()) else '',
        'first_any': str(g['_d'].min().date()) if pd.notna(g['_d'].min()) else '',
        'last_any': str(g['_d'].max().date()) if pd.notna(g['_d'].max()) else '',
        'amt_p25': round(float(st.loc[(st['_d'] >= pd.Timestamp('2025-01-01')) & (st['_d'] <= P25_END), '_amt'].sum()), 2),
        'amt_p26': round(float(st.loc[(st['_d'] >= pd.Timestamp('2026-01-01')) & (st['_d'] <= AS_OF), '_amt'].sum()), 2),
        'top_skus': top,
    })
ket = {r['name']: r for r in rows}
paids = [r for r in rows if r['amt'] > 0]

# ── 合并进 ls_groups（在谈州课题组 ∪ 有订单课题组）────────────────
def kid(n):
    v = ketizu_list.get(n)
    if isinstance(v, dict):
        for k in ('story_id', 'id', 'work_item_id', 'storyId'):
            if v.get(k):
                return str(v[k])
    return ''

groups, src_cnt = [], Counter()
for g in old_ls['groups']:
    n = g['name']
    m = {'name': n, 'status': g.get('status', ''), 'node': g.get('node', ''),
         'sales': g.get('sales', ''), 'region': g.get('region', ''), 'id': g.get('id', ''), 'src': 'mcp'}
    if n in ket:
        k = ket[n]
        m['src'] = 'both'
        for f in ('amt', 'orders', 'orders_paid', 'rows', 'test_rows', 'gift_rows', 'skus',
                  'customers', 'first_deal', 'last_deal', 'first_any', 'last_any', 'amt_p25', 'amt_p26',
                  'top_skus', 'owner_merged_from', 'sales_all'):
            m[f] = k[f]
        if k['sales']:
            m['sales'], m['region'] = k['sales'], k['region'] or m['region']
    groups.append(m)
    src_cnt[m['src']] += 1
have = {g['name'] for g in groups}
for n, k in ket.items():
    if n in have:
        continue
    groups.append({'name': n, 'status': '', 'node': '', 'sales': k['sales'], 'region': k['region'],
                   'id': kid(n), 'src': 'order',
                   **{f: k[f] for f in ('amt', 'orders', 'orders_paid', 'rows', 'test_rows', 'gift_rows',
                                        'skus', 'customers', 'first_deal', 'last_deal', 'first_any',
                                        'last_any', 'amt_p25', 'amt_p26', 'top_skus', 'owner_merged_from',
                                        'sales_all')}})
    src_cnt['order'] += 1
groups.sort(key=lambda x: (-x.get('amt', 0), -x.get('orders', 0), x['name']))

by_sales_paid, by_region_paid = defaultdict(float), defaultdict(float)
for r in paids:
    by_sales_paid[r['sales'] or '(未归属)'] += r['amt']
    by_region_paid[r['region'] or '(未标注)'] += r['amt']

# ── 看板 KPI（口径与前台说明一一对应，构建期算，勿在前端二次算）─────────
_kzg = kzd.groupby('_kz')
_paid_ord = kzd.loc[kzd['_std']].groupby('_kz')['单据编号'].nunique().reindex(_kzg.size().index).fillna(0).astype(int)
_all_ord = _kzg['单据编号'].nunique()
_kamt = kzd.loc[kzd['_std']].groupby('_kz')['_amt'].sum().reindex(_paid_ord.index).fillna(0.0)
_klast = _kzg['_d'].max()
_sleep = (_klast < AS_OF - pd.Timedelta(days=180))
_n_paid = int((_kamt > 0).sum())


def _amt_between(a, b):
    m = df['_std'] & (df['_d'] >= pd.Timestamp(a)) & (df['_d'] <= pd.Timestamp(b)) & (df['_kz'] != '')
    return float(df.loc[m, '_amt'].sum())


_p25, _p26 = _amt_between('2025-01-01', P25_END.strftime('%Y-%m-%d')), _amt_between('2026-01-01', AS_OF.strftime('%Y-%m-%d'))
_sleep_top = [{'name': n, 'amt': round(float(_kamt[n]), 2), 'orders': int(_all_ord[n]),
               'last': str(_klast[n].date()), 'sales': ket[n]['sales'], 'region': ket[n]['region'],
               'days': int((AS_OF - _klast[n]).days)}
              for n in _kamt[(_kamt > 0) & _sleep.reindex(_kamt.index, fill_value=False)].sort_values(ascending=False).index[:20]]

KPI = {
    'as_of': str(AS_OF.date()),
    'gift_ratio': round(100.0 * float((df['是否赠品'] == '是').mean()), 1),
    'test_ratio': round(100.0 * float((df['单据类型'] == '测试销售订单').mean()), 1),
    'conv': {'paid': _n_paid, 'all': len(rows), 'rate': round(100.0 * _n_paid / len(rows), 2)},
    'sleep': {'days': 180, 'n': int(_sleep.sum()), 'paid': int((_sleep & (_kamt > 0)).sum()),
              'amt': round(float(_kamt[_sleep & (_kamt > 0)].sum()), 2),
              'active90': int((_klast >= AS_OF - pd.Timedelta(days=90)).sum())},
    'amt': {'total': round(amt_kz, 2), 'yoy_p25': round(_p25, 2), 'yoy_p26': round(_p26, 2),
            'yoy_growth': round(100.0 * (_p26 - _p25) / _p25, 2) if _p25 else None,
            'yoy_period': '01-01 ~ ' + AS_OF.strftime('%m-%d'), 'ref_full2025': round(_amt_between('2025-01-01', '2025-12-31'), 2)},
    'repurchase': {'n': int((_paid_ord >= 2).sum()), 'once': int((_paid_ord == 1).sum()),
                   'never': int((_paid_ord == 0).sum())},
    'per_group': {'amt': round(amt_kz / _n_paid, 2) if _n_paid else None, 'denom': _n_paid,
                  'median': round(float(_kamt[_kamt > 0].median()), 2),
                  'above_avg': int((_kamt > _kamt[_kamt > 0].mean()).sum())},
    'sleep_top': _sleep_top,
}

new_ls = {
    'data_source': 'MCP 商机视图 _rz-opbvg（在谈课题组状态）+ 最新 ERP 全年主档·商机(飞书)#（实际采购）',
    'updated': str(AS_OF.date()),
    'order_src': os.path.basename(LATEST),
    'order_period': period[0] + ' ~ ' + period[1],   # 展示用字符串（前端直接渲染）
    'history_src': os.path.basename(SPECIAL),
    'period_range': period,
    'amt_caliber': CALIBER + '（用户 2026-10-08 拍板 Q1=B）',
    'total': old_ls.get('total', 0), 'sampled': len(groups),
    'amount_total': round(amt_kz, 2), 'amount_total_all': round(amt_all, 2),
    'paids': len(paids),
    'skus_total': int(kzd.loc[kzd['_std'] & (kzd['货号#'] != ''), '货号#'].nunique()),
    'customers_total': int(kzd.loc[kzd['_std'], '客户'].nunique()),
    'by_status': old_ls.get('by_status', {}), 'by_region': old_ls.get('by_region', {}),
    'by_sales': old_ls.get('by_sales', {}),
    'by_sales_paid': {k: round(v, 2) for k, v in sorted(by_sales_paid.items(), key=lambda x: -x[1])},
    'by_region_paid': {k: round(v, 2) for k, v in sorted(by_region_paid.items(), key=lambda x: -x[1])},
    'src_counts': dict(src_cnt),
    'kpi': KPI,
    'merge_note': {
        'owner_merge': OWNER_MERGE, 'excluded_sales': sorted(EXCLUDE_SALES),
        'non_ketizu_values': bad_kz,
        'note': '徐中平（国际业务部）已剔除；离职者按区域归并至吴云；台账金额=标准口径',
    },
    'groups': groups,
}

ledger = {
    'generated': str(AS_OF.date()), 'range': period, 'src': os.path.basename(LATEST), 'history_src': os.path.basename(SPECIAL), 'caliber': CALIBER,
    'count': len(rows), 'with_amount': len(paids),
    'amount_total': round(amt_kz, 2), 'amount_total_all': round(amt_all, 2),
    'amount_excluded_other_dept': round(amt_excluded, 2),
    'ketizu': sorted(rows, key=lambda x: -x['amt']),
}

omap = {}
for no, g in df.groupby('单据编号'):
    ks = sorted({x for x in g['_kz'] if x})
    if len(ks) > 1:                       # 一张单挂多个课题组：取首个并记录
        pass
    omap[no] = ks[0] if ks else ''
n_with = sum(1 for v in omap.values() if v)
new_map = {
    'generated': str(AS_OF.date()), 'range': period,
    'src': '最新 ERP 全年主档 商机(飞书)#（含课题组）', 'caliber': CALIBER,
    'orders': len(omap), 'with_ketizu': n_with,
    'coverage': round(100.0 * n_with / max(1, len(omap)), 2), 'map': omap,
    '_note': ('本文件由 脚本/build_ls_from_orders.py 生成（最新 ERP 全年主档 · 商机(飞书)#，覆盖 %.2f%%）。' % (100.0 * n_with / max(1, len(omap))) + ' '
              '已取代 脚本/pull_order_ketizu.py（飞书 field_2e119c，覆盖率仅 1.5%）—— 重跑旧脚本会把它覆盖回 1.5%，'
              '除非飞书侧字段补齐，否则请勿重跑。'),
}

report = {
    'generated': str(AS_OF.date()), 'src': os.path.basename(LATEST), 'history_src': os.path.basename(SPECIAL), 'rows_raw': raw_rows,
    'rows_used': int(len(df)), 'period': period,
    'amount_all': round(amt_all, 2), 'amount_ketizu': round(amt_kz, 2),
    'amount_non_ketizu_rows': round(amt_all - amt_kz, 2),
    'excluded': {'sales': sorted(EXCLUDE_SALES), 'rows': n_excluded, 'amount': round(amt_excluded, 2)},
    'owner_merge': OWNER_MERGE,
    'non_ketizu_values': bad_kz, 'non_ketizu_rows': bad_kz_rows,
    'ketizu_matched': len(K_SET & set(kzd['_kz'].unique())), 'ketizu_in_list': len(K_SET),
    'ketizu_with_orders': len(rows), 'ketizu_with_amount': len(paids),
    'unknown_sales': sorted(set(df['_sales']) - set(SALES_REGION) - {'徐中平'}),
}

for fn, obj in [('数据/ls_groups.json', new_ls), ('数据/ls_ketizu_purchase.json', ledger),
                ('数据/order_ketizu_map.json', new_map), ('数据/_ls_intake_report.json', report)]:
    with io.open(fn, 'w', encoding='utf-8') as f:
        json.dump(obj, f, ensure_ascii=False, indent=1)
    print('写出 %-34s %8.1f KB' % (fn, os.path.getsize(fn) / 1024.0))

print('\n── 汇总 ─────────────────────────────────')
print('区间 %s ~ %s ｜ 原始 %d 行 → 使用 %d 行' % (period[0], period[1], raw_rows, len(df)))
print('金额：全表标准口径 %.2f 元 ｜ 课题组口径 %.2f 元 ｜ 非课题组值行 %.2f 元'
      % (amt_all, amt_kz, amt_all - amt_kz))
print('剔除 徐中平：%d 行 / %.2f 元' % (n_excluded, amt_excluded))
print('课题组：清单 %d ｜ 有订单 %d ｜ 有金额 %d' % (len(K_SET), len(rows), len(paids)))
print('ls_groups：%d 条（%s）｜ 台账 %d' % (len(groups), dict(src_cnt), len(rows)))
print('order_ketizu_map：%d 单 ｜ 有课题组 %d ｜ 覆盖 %.2f%%（原 1.5%%）'
      % (len(omap), n_with, new_map['coverage']))
print('归并后成交额：', json.dumps(new_ls['by_sales_paid'], ensure_ascii=False))
print('非课题组脏值 %d 个：%s' % (len(bad_kz), bad_kz[:6]))
print('名册外销售员：', report['unknown_sales'])