#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026 Q3 同比分析 —— 2026 YTD(1-9) vs 2025 同期 vs 2025 全年 vs 2024 全年

口径（对齐 脚本/erp_sales_core.py）：
    单据类型 == 标准销售订单
    是否赠品 != 是
    剔除宝锐系（客户名含 宝锐|宝泰|横琴）
    金额 = 价税合计（含税）
    ⚠️「单据状态 == 已审核」仅 2026 件有该列；脚本会打印 2026 件里非已审核的占比，
       若可忽略则跨年比较成立（历史件本身即为已审核导出）。

部门归属：各年用「该年自己的名册」
    2026 → 数据/pricing/_personnel.json
    2025 → 2025全年订单.xlsx!销售部门区域对应
    2024 → 2024全年订单.xlsx!Sheet2

用法：env -u PYTHONPATH /usr/bin/python3 脚本/q3_yoy_compare.py
产出：2026年3季度汇报/_汇总/erp_yoy.json  + 控制台报表
"""
import os, re, json, collections

import pandas as pd

BASE = os.path.expanduser('~/Desktop/Hermes输出-工作类')
HIST = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/2024和2025历史数据')
MONTHLY = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据')
OUTDIR = os.path.join(BASE, '2026年3季度汇报', '_汇总')

BAORUI = '宝锐|宝泰|横琴'
DEPT_KEYS = ('大客户', '生命科学', '销售拓展')


def to_ym(v):
    s = str(v)
    m = re.match(r'^(\d{4})[-/](\d{1,2})', s.replace(' ', ''))
    if m:
        return '%s-%02d' % (m.group(1), int(m.group(2)))
    m = re.match(r'^(\d{4})(\d{2})$', s.strip())
    if m:
        return '%s-%s' % (m.group(1), m.group(2))
    return ''


def norm_dept(d):
    """把各年名册里的部门写法归一到三部门。

    ⚠️ 判定顺序必须与官方脚本 脚本/erp_sales_core.load_mappings() 完全一致：
       依次 if '大客户' → if '生命科学' → if '销售拓展'，**不得加特例**。
       实测教训：曾为「诊断原料销售部（下辖销售拓展部、大客户部、生命科学销售部）」
       这条组织架构行加 '下辖' 特例 → 把刘新元名下 132.5万 从大客户部挖走，
       与工作台 erp_amount.json 的部门口径对不上。官方脚本就是按「大客户」优先归的。
    唯一新增：2025 名册把生命科学写成「科研-浙江 / 科研-广东」，
       2024 写成「生命科学销售部」→ 需把「科研」也归到生命科学部，否则该部门凭空为 0。
    """
    d = str(d or '')
    if '大客户' in d:
        return '大客户部'
    if '生命科学' in d or '科研' in d:
        return '生命科学部'
    if '销售拓展' in d:
        return '销售拓展部'
    return d.strip() or None


def latest_2026():
    fs = [f for f in os.listdir(MONTHLY) if f.startswith('销售订单_20260101-') and f.endswith('.xlsx')]
    return os.path.join(MONTHLY, sorted(fs)[-1])


def rost_2026():
    p = os.path.join(BASE, '数据', 'pricing', '_personnel.json')
    d = json.load(open(p, encoding='utf-8'))['personnel']
    return {x['name']: norm_dept(x.get('dept')) for x in d}


def rost_2025():
    p = os.path.join(HIST, '2025全年订单.xlsx')
    df = pd.read_excel(p, sheet_name='销售部门区域对应', dtype=str)
    df.columns = [str(c).strip() for c in df.columns]
    return {str(r['销售员']).strip(): norm_dept(r.get('部门')) for _, r in df.iterrows() if pd.notna(r.get('销售员'))}


def rost_2024():
    p = os.path.join(HIST, '2024全年订单.xlsx')
    df = pd.read_excel(p, sheet_name='Sheet2', dtype=str)
    df.columns = [str(c).strip() for c in df.columns]
    return {str(r['销售员']).strip(): norm_dept(r.get('部门')) for _, r in df.iterrows() if pd.notna(r.get('销售员'))}


def load(fp, sheet, roster, tag):
    """读一份 ERP 件 → DataFrame(ym, customer, sales, dept, amt)"""
    df = pd.read_excel(fp, sheet_name=sheet, dtype=str)
    df.columns = [str(c).strip() for c in df.columns]
    amt_col = '价税合计' if '价税合计' in df.columns else ('金额' if '金额' in df.columns else None)
    if amt_col is None:
        raise SystemExit('%s 无金额列' % tag)
    df['_amt'] = pd.to_numeric(df[amt_col], errors='coerce').fillna(0)
    if amt_col == '金额' and '税率%' in df.columns:
        df['_amt'] = df['_amt'] * (1 + pd.to_numeric(df['税率%'], errors='coerce').fillna(0) / 100)

    ym_src = '合同月份' if '合同月份' in df.columns and df['合同月份'].notna().any() else '合同日期'
    df['_ym'] = df[ym_src].apply(to_ym)

    n0 = len(df)
    df = df[df['单据类型'] == '标准销售订单']
    n1 = len(df)
    df = df[df['是否赠品'] != '是']
    n2 = len(df)
    df = df[~df['客户'].astype(str).str.contains(BAORUI, na=False)]
    n3 = len(df)

    unapproved = 0
    if '单据状态' in df.columns:
        unapproved = int((df['单据状态'] != '已审核').sum())

    df['_cust'] = df['客户'].astype(str).str.strip()
    df['_sp'] = df['销售员'].astype(str).str.strip() if '销售员' in df.columns else ''
    df['_dept'] = df['_sp'].map(roster)
    df['_year'] = df['_ym'].str[:4]

    print('  [%s] 总 %d → 标准订单 %d → 非赠品 %d → 剔宝锐系 %d ｜ 未映射部门 %d ｜ 未审核 %d'
          % (tag, n0, n1, n2, n3, int(df['_dept'].isna().sum()), unapproved))
    return df[df['_ym'] != ''][['_ym', '_year', '_cust', '_sp', '_dept', '_amt']].copy()


def summ(df, ym_from=None, ym_to=None):
    d = df
    if ym_from:
        d = d[d['_ym'] >= ym_from]
    if ym_to:
        d = d[d['_ym'] <= ym_to]
    total = d['_amt'].sum()
    by_month = d.groupby('_ym')['_amt'].sum().to_dict()
    by_dept = d.groupby('_dept')['_amt'].sum().to_dict()
    by_cust = d.groupby('_cust')['_amt'].sum().sort_values(ascending=False)
    by_sp = d.groupby('_sp')['_amt'].sum().sort_values(ascending=False)
    return dict(total=total, by_month=by_month, by_dept=by_dept,
                top_cust=by_cust.head(25).to_dict(), by_sp=by_sp.to_dict(),
                rows=len(d))


def main():
    os.makedirs(OUTDIR, exist_ok=True)
    print('════ 载入 ════')
    f26 = latest_2026()
    print('  2026 件: %s' % os.path.basename(f26))
    d26 = load(f26, 0, rost_2026(), '2026')
    d25 = load(os.path.join(HIST, '2025全年订单.xlsx'), 'Sheet1', rost_2025(), '2025全年')
    d24 = load(os.path.join(HIST, '2024全年订单.xlsx'), 'Sheet1', rost_2024(), '2024全年')

    out = {}
    print()
    print('════ ① 总盘同比（万元）════')
    y26 = summ(d26, '2026-01', '2026-09')
    y25 = summ(d25, '2025-01', '2025-09')
    f25 = summ(d25)
    f24 = summ(d24)
    rows = [
        ('2024 全年', f24['total']),
        ('2025 全年', f25['total']),
        ('2025 同期 1-9月', y25['total']),
        ('2026 1-9月（YTD）', y26['total']),
    ]
    print('  %-22s %12s %12s' % ('口径', '金额(万元)', '同比'))
    for label, v in rows:
        yoy = ''
        if label.startswith('2026'):
            yoy = '%+.1f%%' % ((v - y25['total']) / y25['total'] * 100) if y25['total'] else 'n/a'
        print('  %-22s %12.1f %12s' % (label, v / 1e4, yoy))

    print()
    print('════ ② 按月同比（万元）════')
    print('  %-9s %12s %12s %10s' % ('月份', '2025', '2026', '同比'))
    for m in range(1, 10):
        k25, k26 = '2025-%02d' % m, '2026-%02d' % m
        a, b = f25['by_month'].get(k25, 0), y26['by_month'].get(k26, 0)
        pct = ('%+.1f%%' % ((b - a) / a * 100)) if a else 'n/a'
        print('  %-9s %12.1f %12.1f %10s' % ('%d月' % m, a / 1e4, b / 1e4, pct))
    print('  %-9s %12.1f %12.1f' % ('1-9合计', y25['total'] / 1e4, y26['total'] / 1e4))

    print()
    print('════ ③ 三部门同比（万元）════')
    print('  %-10s %12s %12s %12s %10s' % ('部门', '2025全年', '2025同期1-9', '2026 1-9', '同比'))
    for dep in ('销售拓展部', '大客户部', '生命科学部'):
        a, b, c = f25['by_dept'].get(dep, 0), y25['by_dept'].get(dep, 0), y26['by_dept'].get(dep, 0)
        pct = ('%+.1f%%' % ((c - b) / b * 100)) if b else 'n/a'
        print('  %-10s %12.1f %12.1f %12.1f %10s' % (dep, a / 1e4, b / 1e4, c / 1e4, pct))

    print()
    print('════ ④ TOP15 客户同比（万元）════')
    print('  %-40s %10s %10s %10s' % ('客户', '2025同期', '2026 1-9', '同比'))
    for cust, v in list(y26['top_cust'].items())[:15]:
        a = y25['top_cust'].get(cust, 0)
        pct = ('%+.1f%%' % ((v - a) / a * 100)) if a else '新增'
        print('  %-40s %10.1f %10.1f %10s' % (cust[:38], a / 1e4, v / 1e4, pct))

    out = {
        'generated': pd.Timestamp.now().strftime('%Y-%m-%d %H:%M'),
        'source_2026': os.path.basename(f26),
        'source_2025': '2025全年订单.xlsx[Sheet1]',
        'source_2024': '2024全年订单.xlsx[Sheet1]',
        'caliber': '标准销售订单 + 非赠品 + 剔宝锐系；金额=价税合计',
        'y2026_ytd': y26, 'y2025_ytd': y25, 'y2025_full': f25, 'y2024_full': f24,
    }
    p = os.path.join(OUTDIR, 'erp_yoy.json')
    json.dump(out, open(p, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
    print('\n  ✅ 已写 %s' % p)


if __name__ == '__main__':
    main()