# -*- coding: utf-8 -*-
"""佰奥莱博近三年数据层：全部数字从 ERP 导出于真实 JSON 现算，输出 bai_data.json"""
import json, os
import pandas as pd

W = os.path.expanduser('~/Desktop/Hermes输出-工作类')
D = os.path.join(W, '数据')
H = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/2024和2025历史数据')
CUST = '西安佰奥莱博生物科技有限公司'
GPAT = '佰奥莱博|基灵|基水|瑞佰'

def load(f):
    df = pd.read_excel(f, sheet_name='Sheet1')
    df.columns = [str(c).strip() for c in df.columns]
    df['价税合计'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0)
    if '合同日期' in df.columns:
        df['合同日期'] = pd.to_datetime(df['合同日期'], errors='coerce')
    else:
        df['合同日期'] = pd.to_datetime(df['合同月份'].astype(str) + '-01', errors='coerce')
    df['ym'] = df['合同日期'].dt.strftime('%Y-%m')
    return df

d24 = load(os.path.join(H, '2024全年订单.xlsx'))
d25 = load(os.path.join(H, '2025全年订单.xlsx'))
d26 = load(os.path.join(W, '销售分析/销售订单_20260101-20260928.xlsx'))

def cust_year(df, year):
    d = df[df['客户'].astype(str).str.contains('西安佰奥莱博', na=False)].copy()
    orders = int(d['单据编号'].nunique())
    amt = round(float(d['价税合计'].sum()), 2)
    monthly = {k: round(float(v), 2) for k, v in d.groupby('ym')['价税合计'].sum().items()}
    sku = d.groupby('货号#')['价税合计'].sum().sort_values(ascending=False)
    names = d.groupby('货号#')['物料名称'].first().to_dict()
    gift = int(d['是否赠品'].astype(str).str.contains('是').sum()) if '是否赠品' in d.columns else 0
    fmd = float(d[d['货号#'].astype(str).str.startswith('FMD')]['价税合计'].sum())
    return {
        'year': year, 'amount': amt, 'orders': orders, 'lines': int(len(d)),
        'avg_order': round(amt / orders, 2) if orders else 0,
        'gift_lines': gift, 'monthly': monthly,
        'fmd_amount': round(fmd, 2), 'fmd_share': round(fmd / amt * 100, 1) if amt else 0,
        'top_sku': [{'sku': str(k), 'name': str(names.get(k, '')),
                     'amt': round(float(v), 2)} for k, v in sku.head(6).items()],
        'first': str(d['合同日期'].min())[:10], 'last': str(d['合同日期'].max())[:10],
    }

y = {str(k): cust_year(v, k) for k, v in [(2024, d24), (2025, d25), (2026, d26)]}

def ytd(df, year):
    d = df[df['客户'].astype(str).str.contains('西安佰奥莱博', na=False)]
    d9 = d[d['合同日期'].dt.month <= 9]
    return {'amount': round(float(d9['价税合计'].sum()), 2), 'orders': int(d9['单据编号'].nunique())}

y['ytd_2025'] = ytd(d25, 2025)
y['ytd_2026'] = ytd(d26, 2026)

y['yoy_2025'] = round((y['2025']['amount'] / y['2024']['amount'] - 1) * 100, 1)
y['yoy_2026'] = round((y['ytd_2026']['amount'] / y['ytd_2025']['amount'] - 1) * 100, 1)

# 集团口径
grp = {}
for nm, df in [('2024', d24), ('2025', d25), ('2026', d26)]:
    sub = df[df['客户'].astype(str).str.contains(GPAT, na=False)]
    if nm == '2026':
        pass
    grp[nm] = {'amount': round(float(sub['价税合计'].sum()), 2), 'orders': int(sub['单据编号'].nunique()),
               'by_cust': {k: round(float(v), 2) for k, v in sub.groupby('客户')['价税合计'].sum().items()}}
sub25 = d25[d25['客户'].astype(str).str.contains(GPAT, na=False)]
grp['ytd_2025'] = {'amount': round(float(sub25[sub25['合同日期'].dt.month <= 9]['价税合计'].sum()), 2)}
grp['yoy_2026'] = round((grp['2026']['amount'] / grp['ytd_2025']['amount'] - 1) * 100, 1)

# 应收
ar = json.load(open(os.path.join(D, '2026年10月应收账龄_大客户部.json')))
rows = ar.get('detail', [])
bills = [r for r in rows if '西安佰奥莱博' in str(r.get('cust', ''))]
ar_total = round(sum(float(b['amt']) for b in bills), 2)
od_total = round(sum(float(b['od_amt'] or 0) for b in bills), 2)
main = [r for r in json.load(open(os.path.join(D, '2026年10月应收客户账龄_主力客户.json'))).get('customers', []) if '西安佰奥莱博' in str(r.get('cust', ''))]

# 客户360（客诉、商机、活动、联系人）
c360 = json.load(open(os.path.join(D, 'customer_360.json')))
c = [x for x in c360['customers'] if x['name'] == CUST][0]
contacts = json.load(open(os.path.join(D, 'customer_contacts.json'))).get('contacts', {}).get(CUST, [])
srs_all = json.load(open(os.path.join(D, 'customer_special_requirements.json'))).get('items', [])
srs = [x for x in srs_all if CUST in str(x.get('customer', '')) and x.get('status') == 'active']

# 2026年10月订单（orders_detail_2026.json，dict 结构）
det = json.load(open(os.path.join(D, 'orders_detail_2026.json')))['orders']
oct_orders = []
for oid, o in det.items():
    if CUST in json.dumps(o, ensure_ascii=False):
        oct_orders.append({'id': oid, 'date': o.get('date'), 'amt': o.get('amt'),
                           'contract': o.get('contract'), 'ship': o.get('ship')})
oct_orders = [x for x in oct_orders if str(x.get('date', '')) >= '2026-10-01']
oct_amt = round(sum(float(x['amt'] or 0) for x in oct_orders), 2)

data = {
    'customer': CUST, 'sales': c.get('sales'), 'dept': '诊断原料大客户销售部',
    'level': c.get('level'), 'tier': c.get('tier'), 'group': c.get('group'),
    'yearly': y, 'group': grp,
    'top_products_360': c.get('top_products', []),
    'opps': c.get('opp_sandbox', []), 'complaints': c.get('complaints', []),
    'activities': c.get('activities', [])[:5],
    'ar': {'bills': bills, 'total': ar_total, 'overdue': od_total,
           'ratio': round(od_total / ar_total * 100, 1) if ar_total else 0,
           'main': main},
    'contacts': sorted(set(contacts)), 'n_contacts': len(set(contacts)),
    'n_sr': len(srs),
    'sr_by_cat': {k: sum(1 for x in srs if x.get('category') == k) for k in sorted(set(x.get('category') for x in srs))},
    'oct_orders': oct_orders, 'oct_amt': oct_amt,
    'amount_incl_oct': round(y['2026']['amount'] + oct_amt, 2),
    'total3y': round(sum(y[k]['amount'] for k in ['2024', '2025', '2026']), 2),
    'total3y_orders': sum(y[k]['orders'] for k in ['2024', '2025', '2026']),
}
json.dump(data, open(os.path.join(D, '_bai_3y_data.json'), 'w'), ensure_ascii=False, indent=1)
print(json.dumps({k: v for k, v in data.items() if k not in ('ar',)}, ensure_ascii=False, indent=1)[:3500])
print('AR total', ar_total, 'overdue', od_total, 'bills', len(bills))
for b in bills:
    print('  ', b.get('bill'), b.get('due'), b.get('amt'), b.get('od_days'), b.get('bucket'))
print('SR count', len(srs), 'contacts', len(set(contacts)))