# -*- coding: utf-8 -*-
"""科研秋季促销复盘数据层（B 口径）
- B 口径：浙江 2026-08-20~09-30；广东 2026-09-01~10-16；按订单销售员所属区域套窗口
- 主表：2026 年 ERP 标准销售订单 + 已审核 + 非赠品 + 剔除宝锐/宝泰/横琴
- 签约主体：B 口径 8 名销售员（不含未纳入原促销名单的陈彩红，附录单列）
- 课题组字段：ERP「商机(飞书)#」，空值回落到客户名/（未挂课题组）
- 金额：ERP 价税合计（含税），按行汇总；订单去重后汇总
"""
import json
import re
from pathlib import Path

import pandas as pd

ROOT = Path('/Users/liuxinyuan/Desktop/Hermes输出-工作类')
SRC = Path('/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261103.xlsx')
OUT = ROOT / '营销活动' / '科研促销复盘_20261103' / '数据'
OUT.mkdir(parents=True, exist_ok=True)

REGION = {'王诗香': '广东', '黄建华': '广东', '韩远怀': '广东', '刘斌': '广东',
          '叶信福': '浙江', '袁殿果': '浙江', '侯海瑜': '浙江', '吴云': '浙江'}
WINDOW = {'广东': ('2026-09-01', '2026-10-16'), '浙江': ('2026-08-20', '2026-09-30')}
DEALER_GROUP_MARK = '经销商'
TIERS = [(100000, '10W'), (50000, '5W'), (20000, '2W'),
         (10000, '1W'), (5000, '5K'), (2000, '2K'), (1000, '1K')]
GIFT = {'1K': (50, 100), '2K': (100, 200), '5K': (250, 500),
        '1W': (500, 1000), '2W': (1000, 2000), '5W': (2500, 5000), '10W': (5000, 10000)}


def is_dealer(customer):
    c = str(customer)
    if re.search(r'大学|学院|医院|研究院|研究所|中心|疾病预防|疾控|海关|检验检疫|附属', c):
        return False
    return bool(re.search(r'有限公司|有限责任公司', c))


def primary_group(df):
    """一单多行时取金额最大的非空课题组；全空返回空串。"""
    sub = df[df['商机(飞书)#'].fillna('').str.strip() != '']
    if sub.empty:
        return ''
    g = sub.groupby(sub['商机(飞书)#'].fillna('').str.strip())['amt'].sum().sort_values(ascending=False)
    return str(g.index[0])


def tier_of(amount):
    for threshold, label in TIERS:
        if amount >= threshold:
            return label
    return '未达标'


def build_orders(df):
    rows = []
    for oid, z in df.groupby('单据编号'):
        customer = str(z['客户'].iloc[0])
        dealer = is_dealer(customer)
        group = primary_group(z)
        if not group:
            if dealer:
                group = DEALER_GROUP_MARK if customer == '杭州智科芯生物科技有限公司' else customer
            else:
                group = '（未挂课题组）'
        rows.append({
            'order': oid,
            'date': z['date'].min().strftime('%Y-%m-%d'),
            'month': z['date'].min().strftime('%Y-%m'),
            'region': z['region'].iloc[0],
            'sales': z['销售员'].iloc[0],
            'customer': customer,
            'dealer': bool(dealer),
            'group': group,
            'group_key': customer + '||' + group,
            'amount': round(float(z['amt'].sum()), 2),
            'lines': int(len(z)),
        })
    return pd.DataFrame(rows)


def main():
    x = pd.read_excel(SRC, dtype=str)
    x['date'] = pd.to_datetime(x['合同日期'], errors='coerce')
    x['amt'] = pd.to_numeric(x['价税合计'], errors='coerce').fillna(0)
    x['qty'] = pd.to_numeric(x['销售数量'], errors='coerce').fillna(0)
    x['region'] = x['销售员'].map(REGION)

    base = x[
        (x['单据类型'] == '标准销售订单')
        & (x['单据状态'] == '已审核')
        & (x['是否赠品'] == '否')
        & (~x['客户'].fillna('').str.contains('宝锐|宝泰|横琴', regex=True))
        & (x['销售员'].isin(REGION))
    ].copy()

    # B 口径订单
    b_lines = pd.concat([
        base[(base['region'] == r) & (base['date'] >= d0) & (base['date'] <= d1)]
        for r, (d0, d1) in WINDOW.items()
    ])
    b = build_orders(b_lines)

    # 促销前基线：浙江 4/1-8/19；广东 4/1-8/31（用于新老课题组判定）
    pre_lines = pd.concat([
        base[(base['region'] == '浙江') & (base['date'] >= '2026-04-01') & (base['date'] < '2026-08-20')],
        base[(base['region'] == '广东') & (base['date'] >= '2026-04-01') & (base['date'] < '2026-09-01')],
    ])
    pre = build_orders(pre_lines)
    pre_keys = set(pre['group_key'])
    b['new_group'] = ~b['group_key'].isin(pre_keys)

    # 4-9 月基线（按用户口径；含促销日，属保守基线）
    baseline_lines = base[(base['date'] >= '2026-04-01') & (base['date'] <= '2026-09-30')].copy()
    monthly = baseline_lines.assign(month=baseline_lines['date'].dt.strftime('%Y-%m')).groupby('month').agg(
        amount=('amt', 'sum'), orders=('单据编号', 'nunique')
    ).reset_index()
    monthly_region = baseline_lines.assign(month=baseline_lines['date'].dt.strftime('%Y-%m')).groupby(
        ['month', 'region']).agg(amount=('amt', 'sum'), orders=('单据编号', 'nunique')
    ).reset_index()
    baseline_region = baseline_lines.groupby('region').agg(
        amount=('amt', 'sum'), orders=('单据编号', 'nunique'), customers=('客户', 'nunique')
    ).reset_index()
    baseline_sales = baseline_lines.groupby(['region', '销售员']).agg(
        amount=('amt', 'sum'), orders=('单据编号', 'nunique'), customers=('客户', 'nunique')
    ).reset_index().rename(columns={'销售员': 'sales'})
    baseline_sales = baseline_sales.rename(columns={'amount': 'amount_base', 'orders': 'orders_base', 'customers': 'customers_base'})

    # 订单层汇总
    sales = b.groupby(['region', 'sales']).agg(
        amount=('amount', 'sum'), orders=('order', 'nunique'), customers=('customer', 'nunique'),
        groups=('group_key', 'nunique'), new_groups=('new_group', 'sum')
    ).reset_index()
    sales = sales.merge(baseline_sales, on=['region', 'sales'], how='left')
    sales['baseline_monthly'] = sales['amount_base'] / 6
    sales['window_days'] = sales['region'].map({'广东': 46, '浙江': 42})
    sales['promo_monthly'] = sales['amount'] / sales['window_days'] * 30
    sales['growth'] = sales['promo_monthly'] / sales['baseline_monthly'] - 1
    sales['share'] = sales['amount'] / b['amount'].sum()

    region = b.groupby('region').agg(
        amount=('amount', 'sum'), orders=('order', 'nunique'), customers=('customer', 'nunique'),
        groups=('group_key', 'nunique'), new_groups=('new_group', 'sum')
    ).reset_index()
    region = region.merge(baseline_region, on='region', how='left', suffixes=('', '_base'))
    region['baseline_monthly'] = region['amount_base'] / 6
    region['window_days'] = region['region'].map({'广东': 46, '浙江': 42})
    region['promo_monthly'] = region['amount'] / region['window_days'] * 30
    region['growth'] = region['promo_monthly'] / region['baseline_monthly'] - 1
    region['share'] = region['amount'] / b['amount'].sum()

    # 课题组表（客户+课题组为唯一键；同名课题组跨客户分开）
    group = b.groupby('group_key').agg(
        customer=('customer', 'first'), group=('group', 'first'),
        region=('region', lambda s: '/'.join(sorted(set(s)))),
        sales=('sales', lambda s: '/'.join(sorted(set(s)))),
        amount=('amount', 'sum'), orders=('order', 'nunique'),
        new_group=('new_group', 'max')
    ).reset_index()
    group['tier'] = group['amount'].map(tier_of)
    group['gift_low'] = group['tier'].map(lambda t: GIFT.get(t, (0, 0))[0])
    group['gift_high'] = group['tier'].map(lambda t: GIFT.get(t, (0, 0))[1])
    group = group.sort_values(['amount', 'customer'], ascending=[False, True]).reset_index(drop=True)

    # 按区域/销售员的新课题组数（按唯一客户+课题组键，不按订单重复计）
    gr = group.assign(region=group['region'].str.split('/')).explode('region')
    region_new = gr.groupby('region')['new_group'].sum().to_dict()
    region['new_groups'] = region['region'].map(region_new).fillna(0).astype(int)
    gs = group.assign(sales=group['sales'].str.split('/')).explode('sales')
    sales_new = gs.groupby('sales')['new_group'].sum().to_dict()
    sales['new_groups'] = sales['sales'].map(sales_new).fillna(0).astype(int)

    # 客户表
    customer = b.groupby('customer').agg(
        region=('region', lambda s: '/'.join(sorted(set(s)))),
        sales=('sales', lambda s: '/'.join(sorted(set(s)))),
        amount=('amount', 'sum'), orders=('order', 'nunique'), groups=('group_key', 'nunique'),
        dealer=('dealer', 'max'), new_groups=('new_group', 'sum')
    ).reset_index()
    customer['tier'] = customer['amount'].map(tier_of)
    customer['gift_low'] = customer['tier'].map(lambda t: GIFT.get(t, (0, 0))[0])
    customer['gift_high'] = customer['tier'].map(lambda t: GIFT.get(t, (0, 0))[1])
    customer = customer.sort_values('amount', ascending=False).reset_index(drop=True)

    # 经销商
    dealer = b[b['dealer']].copy()
    dealer['dealer_type'] = dealer.apply(
        lambda r: '代课题组采购' if (r['group'] not in (r['customer'], DEALER_GROUP_MARK, '（未挂课题组）')
                                  and '课题组' in r['group']) else '自购/未注明课题组', axis=1
    )
    dealer_rows = dealer[['date', 'order', 'region', 'sales', 'customer', 'group', 'amount', 'dealer_type']].copy()
    dealer_rows['note'] = dealer_rows.apply(
        lambda r: ('经销商代' + r['group'] + '采购；销售备注终端课题组：' + r['group'])
        if r['dealer_type'] == '代课题组采购' else '经销商自购/未注明终端课题组', axis=1
    )
    dealer_summary = dealer.groupby('dealer_type').agg(
        amount=('amount', 'sum'), orders=('order', 'nunique'), customers=('customer', 'nunique')
    ).reset_index()

    # 产品结构
    product = b_lines.groupby(['货号#', '物料名称']).agg(
        amount=('amt', 'sum'), orders=('单据编号', 'nunique'), customers=('客户', 'nunique'), qty=('qty', 'sum')
    ).reset_index().sort_values('amount', ascending=False)
    product['share'] = product['amount'] / b['amount'].sum()
    category = b_lines.groupby('销售分组（二级）').agg(
        amount=('amt', 'sum'), orders=('单据编号', 'nunique')
    ).reset_index().sort_values('amount', ascending=False)
    category['share'] = category['amount'] / b['amount'].sum()

    # 促销期月度
    promo_month = b.assign(month=b['date'].str[:7]).groupby(['month', 'region']).agg(
        amount=('amount', 'sum'), orders=('order', 'nunique')
    ).reset_index()

    # 未纳入原 B 统计名单的销售员（陈彩红）：只做附录差异说明
    chen_lines = x[
        (x['销售员'] == '陈彩红')
        & (x['单据类型'] == '标准销售订单')
        & (x['单据状态'] == '已审核')
        & (x['是否赠品'] == '否')
        & (~x['客户'].fillna('').str.contains('宝锐|宝泰|横琴', regex=True))
        & (x['date'] >= '2026-09-01') & (x['date'] <= '2026-10-16')
    ].copy()
    chen_lines['region'] = '广东'
    chen = build_orders(chen_lines)
    chen_summary = {
        'amount': round(float(chen['amount'].sum()), 2) if len(chen) else 0.0,
        'orders': int(chen['order'].nunique()) if len(chen) else 0,
        'customers': int(chen['customer'].nunique()) if len(chen) else 0,
        'groups': int(chen['group_key'].nunique()) if len(chen) else 0,
    }

    # 更干净的促销前基线（剔除促销日）：浙江 4/1-8/19（141 天），广东 4/1-8/31（153 天）
    z_amt = float(pre_lines[pre_lines['region'] == '浙江']['amt'].sum())
    g_amt = float(pre_lines[pre_lines['region'] == '广东']['amt'].sum())
    pre_monthly = z_amt / (141 / 30) + g_amt / (153 / 30)
    pre_summary = {
        'total': round(float(pre_lines['amt'].sum()), 2),
        'zhejiang': round(z_amt, 2),
        'guangdong': round(g_amt, 2),
        'monthly_equivalent': round(pre_monthly, 2),
        'growth': round(float(region['promo_monthly'].sum()) / pre_monthly - 1, 4),
    }

    total = float(b['amount'].sum())
    summary = {
        'total': round(total, 2),
        'orders': int(b['order'].nunique()),
        'customers': int(b['customer'].nunique()),
        'groups': int(b['group_key'].nunique()),
        'new_groups': int(group['new_group'].sum()),
        'new_group_orders': int(b[b['new_group']]['order'].nunique()),
        'new_group_amount': round(float(b[b['new_group']]['amount'].sum()), 2),
        'old_group_amount': round(float(b[~b['new_group']]['amount'].sum()), 2),
        'dealer_amount': round(float(b[b['dealer']]['amount'].sum()), 2),
        'dealer_orders': int(b[b['dealer']]['order'].nunique()),
        'dealer_customers': int(b[b['dealer']]['customer'].nunique()),
        'dealer_for_group_amount': round(float(dealer[dealer['dealer_type'] == '代课题组采购']['amount'].sum()), 2),
        'dealer_for_group_orders': int(dealer[dealer['dealer_type'] == '代课题组采购']['order'].nunique()),
        'dealer_self_amount': round(float(dealer[dealer['dealer_type'] == '自购/未注明课题组']['amount'].sum()), 2),
        'dealer_self_orders': int(dealer[dealer['dealer_type'] == '自购/未注明课题组']['order'].nunique()),
        'baseline_total': round(float(baseline_lines['amt'].sum()), 2),
        'baseline_orders': int(baseline_lines['单据编号'].nunique()),
        'baseline_monthly': round(float(baseline_lines['amt'].sum()) / 6, 2),
        'promo_monthly_equivalent': round(float(region['promo_monthly'].sum()), 2),
        'growth_vs_baseline_monthly': round(float(region['promo_monthly'].sum()) / (float(baseline_lines['amt'].sum()) / 6) - 1, 4),
        'avg_order': round(total / b['order'].nunique(), 2),
        'avg_group': round(total / b['group_key'].nunique(), 2),
        'reach_groups': int((group['tier'] != '未达标').sum()),
        'gift_low': int(group['gift_low'].sum()),
        'gift_high': int(group['gift_high'].sum()),
        'reach_customers': int((customer['tier'] != '未达标').sum()),
        'customer_gift_low': int(customer['gift_low'].sum()),
        'customer_gift_high': int(customer['gift_high'].sum()),
    }

    # 断言：合计勾稽
    assert abs(total - 110887.50) < 0.01, total
    assert abs(total - region['amount'].sum()) < 0.01
    assert abs(total - sales['amount'].sum()) < 0.01
    assert abs(total - group['amount'].sum()) < 0.01
    assert abs(total - customer['amount'].sum()) < 0.01
    assert abs(total - b[b['dealer']]['amount'].sum() - b[~b['dealer']]['amount'].sum()) < 0.01
    assert abs(total - b[b['new_group']]['amount'].sum() - b[~b['new_group']]['amount'].sum()) < 0.01
    assert abs(summary['baseline_total'] - monthly['amount'].sum()) < 0.01

    data = {
        'meta': {
            'period': 'B：浙江 2026-08-20~09-30；广东 2026-09-01~10-16',
            'source': str(SRC),
            'filters': '标准销售订单 + 已审核 + 非赠品 + 剔除宝锐/宝泰/横琴；含税价税合计',
            'baseline': '促销前 4-9 月月度销售额（完整 4-9 月，含促销日；保守口径）',
            'new_group': '促销前（浙江 4/1-8/19、广东 4/1-8/31）无同客户同课题组订单',
            'generated': '2026-11-03',
        },
        'summary': summary,
        'appendix_chen': chen_summary,
        'appendix_pre_baseline': pre_summary,
        'orders': b.sort_values('date').to_dict('records'),
        'monthly_baseline': monthly.to_dict('records'),
        'monthly_baseline_region': monthly_region.to_dict('records'),
        'promo_month': promo_month.to_dict('records'),
        'region': region.to_dict('records'),
        'sales': sales.to_dict('records'),
        'groups': group.to_dict('records'),
        'customers': customer.to_dict('records'),
        'dealer_rows': dealer_rows.sort_values('date').to_dict('records'),
        'dealer_summary': dealer_summary.to_dict('records'),
        'products': product.head(30).to_dict('records'),
        'categories': category.to_dict('records'),
    }
    out = OUT / 'promo_review_2026.json'
    out.write_text(json.dumps(data, ensure_ascii=False, indent=1), encoding='utf-8')
    print('WROTE', out)
    print(json.dumps(summary, ensure_ascii=False, indent=1))
    print('\nREGION\n', region.to_string(index=False))
    print('\nSALES\n', sales.to_string(index=False))
    print('\nGROUPS tier\n', group['tier'].value_counts().to_string())
    print('\nDEALER\n', dealer_summary.to_string(index=False))


if __name__ == '__main__':
    main()