#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""刘子研 2026 Q3（7–9月）按【集团客户】维度统计【每个货号】的采购金额合计。

数据源：用户提供的《刘子研三季度数据-截止至9.30.xls》sheet「2026年销售数据」
集团归属（hybrid 口径，按优先级）：
  ① 表内「集团客户#」列（用户表自带）
  ② 数据/diag_mappings.json 的 cust_to_group（官方客户清单口径）
  无集团归属 → 保留原客户名（不猜测合并）
门禁：归集前后 Σ金额 必须相等（铁律2）；输出必须标覆盖率（铁律3）
输出：销售分析/刘子研_2026Q3_集团x货号_采购金额.xlsx（长表 + 矩阵 + 明细）
用法：python3 脚本/统计_Q3_集团x货号金额.py
"""
import os, json, re
import pandas as pd

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = os.path.expanduser('~/.hermes/profiles/zone-b/cache/documents/doc_6f16ee249d8e_刘子研三季度数据-截止至9.30(1).xls')
OUT = os.path.join(ROOT, '销售分析', '刘子研_2026Q3_集团x货号_采购金额.xlsx')
ASSOC = '宝锐|宝泰|横琴'


def main():
    df = pd.read_excel(SRC, sheet_name='2026年销售数据', header=0, engine='xlrd')
    df.columns = [str(c).strip() for c in df.columns]
    print('源表: %d 行 × %d 列' % (len(df), df.shape[1]))

    df['_d'] = pd.to_datetime(df['合同日期'], errors='coerce')
    df['_m'] = df['_d'].dt.strftime('%Y-%m')
    df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0)

    keep = df['_m'].isin(['2026-07', '2026-08', '2026-09'])
    keep = keep & df['单据类型'].astype(str).str.contains('标准销售订单', na=False)
    keep = keep & df['单据状态'].astype(str).str.contains('已审核', na=False)
    keep = keep & ~df['是否赠品'].astype(str).str.strip().isin(['是', 'Y', 'True', 'true'])
    keep = keep & ~df['客户'].astype(str).str.contains(ASSOC, na=False, regex=True)
    keep = keep & ~df['业务关闭'] if '业务关闭' in df.columns else keep
    d = df[keep].copy()
    print('筛选后（2026-07~09 · 标准销售订单 · 已审核 · 非赠品 · 排除关联方）: %d 行 ｜ 金额 %.2f' % (len(d), d['_amt'].sum()))
    if d.empty:
        print('  ⚠️ 无数据，检查月份字段口径'); return

    # ---- 集团归属：hybrid ----
    col = None
    for c in ('集团客户#', '集团客户'):
        if c in d.columns: col = c; break
    own = d[col].astype(str).str.strip() if col else pd.Series([''] * len(d), index=d.index)
    own = own.where(~own.isin(['nan', 'None', '']), '')
    try:
        c2g = json.load(open(os.path.join(ROOT, '数据', 'diag_mappings.json'), encoding='utf-8'))['cust_to_group']
        gm = {k.strip(): v.strip() for k, v in c2g.items() if k and v and k.strip() != v.strip()}
        grpset = set(gm.values())
    except Exception as e:
        print('  ⚠️ diag_mappings 读取失败：%s' % str(e)[:60]); gm, grpset = {}, set()
    mapv = d['客户'].astype(str).str.strip().map(gm).fillna('')
    d['_grp'] = own.where(own != '', mapv)
    d['_grp_src'] = ['表内集团列' if o else ('映射表' if m else '') for o, m in zip(own, mapv)]
    g = d['_grp'].fillna('').astype(str).str.strip().replace({'nan': '', 'None': '', 'NaN': ''})
    d['_grp'] = g.where(g != '', d['客户'].astype(str).str.strip())

    amt_before = d['_amt'].sum()
    amt_after = d.groupby('_grp')['_amt'].sum().sum()
    assert abs(amt_before - amt_after) < 0.05, '守恒门禁失败: %.2f vs %.2f' % (amt_before, amt_after)

    isgrp = d['_grp'].isin(grpset) | (d['_grp_src'] == '表内集团列')
    print('\n══ 集团归集覆盖率 ══')
    print('  已归集集团数: %d ｜ 归集覆盖金额: %.2f / %.2f = %.1f%%' % (
        d.loc[isgrp, '_grp'].nunique(), d.loc[isgrp, '_amt'].sum(), amt_before,
        100 * d.loc[isgrp, '_amt'].sum() / amt_before if amt_before else 0))
    print('  未归集（按客户名独立统计）: %d 个客户 / %d 行' % (d.loc[~isgrp, '客户'].nunique(), (~isgrp).sum()))
    print('  守恒门禁: Σ归集前 %.2f == Σ归集后 %.2f ✅' % (amt_before, amt_after))

    hk = '货号#' if '货号#' in d.columns else '货号'
    long = (d.assign(货号=d[hk].astype(str))
             .groupby(['_grp', '货号'], dropna=False)
             .agg(金额=('_amt', 'sum'), 数量=('销售数量', 'sum'), 单数=('单据编号', 'nunique'))
             .reset_index().rename(columns={'_grp': '集团客户'}))
    long = long.sort_values(['集团客户', '金额'], ascending=[True, False])
    pivot = long.pivot_table(index='集团客户', columns='货号', values='金额', aggfunc='sum', fill_value=0)
    pivot['合计'] = pivot.sum(axis=1)
    pivot = pivot.sort_values('合计', ascending=False)

    print('\n══ 集团 × 货号（金额合计，Top 集团）══')
    print('  集团数 %d ｜ 货号数 %d ｜ 组合 %d ｜ 总额 %.2f' % (long['集团客户'].nunique(), long['货号'].nunique(), len(long), long['金额'].sum()))
    for g, sub in long.groupby('集团客户'):
        pass
    top = pivot.head(10)
    for g, row in top.iterrows():
        cols = [(c, row[c]) for c in pivot.columns[:-1] if row[c] > 0]
        top3 = sorted(cols, key=lambda x: -x[1])[:3]
        print('   %-22s 合计 %10.2f ｜ 货号 %2d 个 ｜ Top: %s' % (
            str(g)[:22], row['合计'], len(cols),
            ' / '.join('%s %.0f' % (c, v) for c, v in top3)))

    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    with pd.ExcelWriter(OUT) as w:
        long.to_excel(w, sheet_name='长表_集团x货号', index=False)
        pivot.reset_index().to_excel(w, sheet_name='矩阵_集团x货号', index=False)
        dd = d[['_d', '单据编号', '客户', '_grp', '_grp_src', hk, '物料名称', '销售数量',
                '含税单价', '_amt', '出货批号#', '销售员']].rename(columns={'_d': '合同日期', '_grp': '集团客户', '_grp_src': '集团来源', '_amt': '价税合计'})
        dd.sort_values(['集团客户', '合同日期']).to_excel(w, sheet_name='明细', index=False)
        pd.DataFrame([{'指标': '期间', '值': '2026-07-01 ~ 2026-09-30（按合同日期）'},
                      {'指标': '归集前金额', '值': round(amt_before, 2)},
                      {'指标': '归集后金额', '值': round(amt_after, 2)},
                      {'指标': '已归集集团数', '值': int(d.loc[isgrp, '_grp'].nunique())},
                      {'指标': '归集覆盖金额占比', '值': '%.1f%%' % (100 * d.loc[isgrp, '_amt'].sum() / amt_before)},
                      {'指标': '口径', '值': '标准销售订单·已审核·非赠品·排除关联方(宝锐|宝泰|横琴)'}]).to_excel(w, sheet_name='口径', index=False)
    print('\n→ %s' % OUT)


if __name__ == '__main__':
    main()