#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""集团维度 2025 vs 2026 同比增减归因（货号级驱动）。
数据源：doc_b68712feec68_刘子研三季度数据-截止至9.30(1).xls
  sheet「2026年销售数据」「2025年销售数据」

口径铁律：
  1) 集团归集只用「集团客户#」列（无值才回退客户名），绝不用客户名关键词匹配。
  2) 主口径（对比公平）= 2026-01-01~09-30 vs 2025-01-01~09-30（期末月日对齐）；
     附口径 = 2025 全年 vs 2026 前 9 月（口径不可比，仅供参考）。
  3) 标准销售订单 · 已审核 · 非赠品 · 排除关联方（宝锐/宝泰/横琴）；金额取「价税合计」；
     空货号行填「（未填货号）」不得丢。

三层守恒门禁（不过不写盘）：
  G1 总级：Σ各集团Δ == 全量Δ
  G2 集团内：每集团 Σ各货号Δ == 该集团Δ
  G3 归集前后 Σ金额 相等（每年度一条）
"""
import os
import sys

import pandas as pd

USR = os.path.expanduser(
    '~/.hermes/profiles/zone-b/cache/documents/doc_b68712feec68_刘子研三季度数据-截止至9.30(1).xls')
OUTDIR = os.path.expanduser('~/Desktop/Hermes输出-工作类/销售分析')
OUT_XLSX = os.path.join(OUTDIR, '集团维度_货号增减归因.xlsx')

SKU_EMPTY = '（未填货号）'


def load(sheet):
    d = pd.read_excel(USR, sheet_name=sheet, header=0, engine='xlrd')
    d.columns = [str(c).strip() for c in d.columns]
    hk = '货号#' if '货号#' in d.columns else '货号'
    sk = d[hk].fillna('').astype(str).str.strip().replace({'nan': ''})
    d['_sku'] = sk.where(sk != '', SKU_EMPTY)
    d['_d'] = pd.to_datetime(d['合同日期'], errors='coerce')
    d['_yr'] = d['_d'].dt.year
    d['_amt'] = pd.to_numeric(d['价税合计'], errors='coerce').fillna(0)
    d['_cust'] = d['客户'].astype(str).str.strip()
    gcol = '集团客户#' if '集团客户#' in d.columns else '集团客户'
    # ★ 空值先 fillna('') 再判空：避免 astype(str) 把 NaN 变成字符串 'nan' 而静默成组
    g = d[gcol].fillna('').astype(str).str.strip().replace({'nan': '', 'None': '', 'NaN': ''})
    d['_grp'] = g.where(g != '', d['_cust'])
    d['_iscorp'] = g != ''
    return d


def clean(d):
    k = d['单据类型'].astype(str).str.contains('标准销售订单', na=False)
    k &= d['单据状态'].astype(str).str.contains('已审核', na=False)
    k &= ~d['是否赠品'].astype(str).str.strip().isin(['是', 'Y', 'True', 'true'])
    k &= ~d['_cust'].str.contains('宝锐|宝泰|横琴', na=False, regex=True)
    return d[k].copy()


def period(d, y, m_end=9, full=False):
    k = d['_d'].dt.year == y
    if not full:
        k &= d['_d'] <= pd.Timestamp('%d-%02d-30' % (y, m_end)) + pd.Timedelta(days=0)
        k &= (d['_d'].dt.month <= m_end)
    return d[k]


def main():
    d26 = clean(load('2026年销售数据'))
    d25 = clean(load('2025年销售数据'))

    # ── 期间切片 ────────────────────────────────────────────
    d25p = period(d25, 2025, 9)      # 2025 1-9 月
    d26p = period(d26, 2026, 9)      # 2026 1-9 月（主口径）
    d25f = d25.copy()                # 2025 全年（附口径）

    T25, T26 = d25p['_amt'].sum(), d26p['_amt'].sum()
    print('主口径 2025-1~9月: %d 行 ¥%.2f' % (len(d25p), T25))
    print('主口径 2026-1~9月: %d 行 ¥%.2f' % (len(d26p), T26))
    print('附口径 2025 全年 : %d 行 ¥%.2f' % (len(d25f), d25f['_amt'].sum()))

    # ── 总级（主口径）────────────────────────────────────────
    g25 = d25p.groupby('_grp')['_amt'].sum()
    g26 = d26p.groupby('_grp')['_amt'].sum()
    ga = pd.concat([g25.rename('额25'), g26.rename('额26')], axis=1).fillna(0.0)
    ga['Δ'] = ga['额26'] - ga['额25']
    ga['Δ%'] = (ga['Δ'] / ga['额25'].replace(0, float('nan')) * 100).round(1)

    # ── 集团×货号（主口径）──────────────────────────────────
    sk25 = d25p.groupby(['_grp', '_sku'])['_amt'].sum().rename('额25')
    sk26 = d26p.groupby(['_grp', '_sku'])['_amt'].sum().rename('额26')
    sk = pd.concat([sk25, sk26], axis=1).fillna(0.0).reset_index()
    sk['Δ'] = sk['额26'] - sk['额25']

    # ══ 守恒门禁 ════════════════════════════════════════════
    G1 = abs(ga['Δ'].sum() - (T26 - T25))
    per = sk.groupby('_grp')['Δ'].sum()
    joined = ga['Δ'] - per.reindex(ga.index).fillna(0.0)
    G2 = float(joined.abs().max())
    raw25, raw26 = d25p['_amt'].sum(), d26p['_amt'].sum()
    grp25 = d25p.groupby('_grp')['_amt'].sum().sum()
    grp26 = d26p.groupby('_grp')['_amt'].sum().sum()
    G3a, G3b = abs(raw25 - grp25), abs(raw26 - grp26)

    print('\n══ 守恒门禁 ══')
    print('G1 总级 Σ各集团Δ=%.4f  全量Δ=%.4f  差=%.6f %s'
          % (ga['Δ'].sum(), T26 - T25, G1, '✅' if G1 < 0.05 else '❌'))
    print('G2 集团内 max|Σ货号Δ - 集团Δ|=%.6f %s' % (G2, '✅' if G2 < 0.05 else '❌'))
    print('G3 归集前后 2025: %.4f vs %.4f 差=%.6f %s' % (raw25, grp25, G3a, '✅' if G3a < 0.05 else '❌'))
    print('G3 归集前后 2026: %.4f vs %.4f 差=%.6f %s' % (raw26, grp26, G3b, '✅' if G3b < 0.05 else '❌'))
    assert G1 < 0.05 and G2 < 0.05 and G3a < 0.05 and G3b < 0.05, '守恒门禁未过，禁止写盘'

    # ── 总览：增量Top3/减量Top3 货号 ─────────────────────────
    def top_skus(g, n=3):
        sub = sk[sk['_grp'] == g]
        up = sub[sub['Δ'] > 0].nlargest(n, 'Δ')
        dn = sub[sub['Δ'] < 0].nsmallest(n, 'Δ')
        fu = '；'.join('%s %+.0f' % (r['_sku'], r['Δ']) for _, r in up.iterrows()) or '—'
        fd = '；'.join('%s %+.0f' % (r['_sku'], r['Δ']) for _, r in dn.iterrows()) or '—'
        return fu, fd

    nsku_by_grp = sk.groupby('_grp')['_sku'].nunique()
    rows = []
    for g in ga.sort_values('Δ', ascending=False).index:
        fu, fd = top_skus(g)
        r = ga.loc[g]
        rows.append({'集团': g, '2025同期': round(r['额25'], 2), '2026同期': round(r['额26'], 2),
                     'Δ': round(r['Δ'], 2), 'Δ%': r['Δ%'],
                     '增量Top3货号': fu, '减量Top3货号': fd,
                     '货号数': int(nsku_by_grp.get(g, 0))})
    overview = pd.DataFrame(rows).sort_values('Δ', ascending=False)

    # ── 长表 ────────────────────────────────────────────────
    lt = sk.copy()
    gd = ga['Δ']
    lt['占比(Δ/集团Δ)'] = lt.apply(
        lambda r: round(r['Δ'] / gd[r['_grp']] * 100, 1) if gd[r['_grp']] not in (0,) else None, axis=1)
    lt = lt.rename(columns={'_grp': '集团', '_sku': '货号', '额25': '25同期', '额26': '26同期'})
    lt = lt[['集团', '货号', '25同期', '26同期', 'Δ', '占比(Δ/集团Δ)']].round(2)
    lt = lt.sort_values(['集团', 'Δ'], ascending=[True, False])

    # ── 各集团增量/减量 Top ─────────────────────────────────
    inc = sk[sk['Δ'] > 0].copy()
    inc = inc.merge(ga['Δ'].rename('集团Δ'), left_on='_grp', right_index=True)
    inc['占集团增量比%'] = (inc['Δ'] / inc['集团Δ'] * 100).round(1)
    inc = inc.rename(columns={'_grp': '集团', '_sku': '货号', '额25': '25同期', '额26': '26同期'},
                     )[['集团', '货号', '25同期', '26同期', 'Δ', '占集团增量比%']]
    inc = inc.round(2).sort_values(['集团', 'Δ'], ascending=[True, False])

    dec = sk[sk['Δ'] < 0].copy()
    dec = dec.merge(ga['Δ'].rename('集团Δ'), left_on='_grp', right_index=True)
    dec['占集团减量比%'] = (dec['Δ'] / dec['集团Δ'] * 100).round(1)
    dec = dec.rename(columns={'_grp': '集团', '_sku': '货号', '额25': '25同期', '额26': '26同期'},
                     )[['集团', '货号', '25同期', '26同期', 'Δ', '占集团减量比%']]
    dec = dec.round(2).sort_values(['集团', 'Δ'], ascending=[True, True])

    # ── 附口径：2025 全年 vs 2026 前9月（不可比）─────────────
    f25 = d25f.groupby('_grp')['_amt'].sum()
    f26 = d26p.groupby('_grp')['_amt'].sum()
    fa = pd.concat([f25.rename('2025全年'), f26.rename('2026前9月')], axis=1).fillna(0.0)
    fa['Δ'] = fa['2026前9月'] - fa['2025全年']
    fa['Δ%'] = (fa['Δ'] / fa['2025全年'].replace(0, float('nan')) * 100).round(1)
    fa = fa.reset_index().rename(columns={'_grp': '集团'}).round(2)
    fa.insert(0, '口径说明', '⚠️口径不可比：12 个月 vs 9 个月，2026 Q4 无数据，仅供参考')
    fa = fa.sort_values('Δ', ascending=False)

    # ── 明细 ────────────────────────────────────────────────
    det = pd.concat([d25p.assign(年份=2025), d26p.assign(年份=2026)])
    det = det[['年份', '_d', '_grp', '_cust', '_sku', '物料名称', '销售数量', '价税合计', '销售员', '单据编号']]
    det = det.rename(columns={'_d': '合同日期', '_grp': '集团', '_cust': '客户', '_sku': '货号'})
    det = det.sort_values(['年份', '合同日期'])

    # ── 汇总统计（供报告/图）────────────────────────────────
    total_delta = float(T26 - T25)
    ents = len(ga)
    nsku = int(sk[sk['Δ'] != 0]['_sku'].nunique())
    inc_ents = ga[ga['Δ'] > 0].sort_values('Δ', ascending=False)
    dec_ents = ga[ga['Δ'] < 0].sort_values('Δ')
    print('\n集团(实体)数=%d  有Δ货号数=%d  总增减=%.2f' % (ents, nsku, total_delta))
    print('其中「集团客户#」真集团数(2026前9月) =', int(d26p.loc[d26p['_iscorp'], '_grp'].nunique()),
          ' 独立客户(回退客户名)数 =', int(d26p.loc[~d26p['_iscorp'], '_grp'].nunique()))
    print('\n══ 增量最大 5 个集团 ══')
    for g in inc_ents.head(5).index:
        fu, _ = top_skus(g, 1)
        print('  %-22s Δ%+.0f  (25 %.0f→26 %.0f)  第一驱动: %s' % (g, ga.loc[g, 'Δ'], ga.loc[g, '额25'], ga.loc[g, '额26'], fu))
    print('\n══ 减量最大 5 个集团 ══')
    for g in dec_ents.head(5).index:
        _, fd = top_skus(g, 1)
        print('  %-22s Δ%+.0f  (25 %.0f→26 %.0f)  第一拖累: %s' % (g, ga.loc[g, 'Δ'], ga.loc[g, '额25'], ga.loc[g, '额26'], fd))
    print('\n附口径 2025全年总额=%.2f  2026前9月总额=%.2f  差=%.2f' % (fa['2025全年'].sum(), fa['2026前9月'].sum(), fa['Δ'].sum()))

    # ── 写盘 ────────────────────────────────────────────────
    os.makedirs(OUTDIR, exist_ok=True)
    with pd.ExcelWriter(OUT_XLSX) as w:
        overview.to_excel(w, sheet_name='集团同比总览', index=False)
        lt.to_excel(w, sheet_name='集团x货号_Δ长表', index=False)
        inc.to_excel(w, sheet_name='各集团增量Top', index=False)
        dec.to_excel(w, sheet_name='各集团减量Top', index=False)
        fa.to_excel(w, sheet_name='附口径_全年vs前9月', index=False)
        det.to_excel(w, sheet_name='明细', index=False)
    print('\n✅ → %s' % OUT_XLSX)

    # 导出绘图数据
    import json
    plot = {
        'total_delta': total_delta,
        'entities': [{'g': str(g), 'd': round(float(ga.loc[g, 'Δ']), 2),
                      'a25': round(float(ga.loc[g, '额25']), 2), 'a26': round(float(ga.loc[g, '额26']), 2)}
                     for g in ga.sort_values('Δ', ascending=False).index],
        'top_inc': str(inc_ents.index[0]) if len(inc_ents) else None,
        'top_dec': str(dec_ents.index[0]) if len(dec_ents) else None,
        'sku_by_grp': {},
    }
    for g in [plot['top_inc'], plot['top_dec']]:
        if not g:
            continue
        sub = sk[sk['_grp'] == g].sort_values('Δ', ascending=False)
        plot['sku_by_grp'][g] = [{'sku': str(r['_sku']), 'd': round(float(r['Δ']), 2),
                                  'a25': round(float(r['额25']), 2), 'a26': round(float(r['额26']), 2)}
                                 for _, r in sub.iterrows()]
    with open(os.path.join(OUTDIR, '_group_yoy_plot.json'), 'w', encoding='utf-8') as f:
        json.dump(plot, f, ensure_ascii=False, indent=1)
    print('✅ → _group_yoy_plot.json (实体 %d 个)' % len(plot['entities']))
    return plot


if __name__ == '__main__':
    main()