#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""候选扫描：找出「客户归属人」与「实际下单人(2026 1-9 ERP)」不一致的客户。
只读，不写任何数据。输出按差额(客户2026总额)降序。
"""
import os, sys, json
sys.path.insert(0, '/Users/liuxinyuan/Desktop/Hermes输出-工作类/脚本')
import pandas as pd
import erp_sales_core as core
import sp_merge as _spm

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
MONTHS_ACT = [f'2026-{m:02d}' for m in range(1, 10)]

fp = core.find_latest_erp()
df = pd.read_excel(fp, dtype=str)
df['_amt'] = pd.to_numeric(df['价税合计'], errors='coerce').fillna(0)
df['_sp'] = df['销售员'].fillna('').astype(str)
pt_col = '客户采购类型#' if '客户采购类型#' in df.columns else ('产品类型#' if '产品类型#' in df.columns else None)
df['_date'] = df['合同日期'].apply(core.ser2date)
df['_month'] = df['_date'].str[:7]

std = df[(df['单据类型'] == '标准销售订单') & (df['单据状态'] == '已审核') & (df['是否赠品'] != '是')]
std = std[~std['客户'].astype(str).str.contains(core.BAORUI_PATTERN, na=False)].copy()
std['_amt'] = std['_amt'].astype(float)
person_dept, lzy = core.load_mappings()

cust_sp = {}          # cust -> {eff_sp: amt}
cust_dept = {}        # cust -> {dept: amt}
for _, r in std.iterrows():
    m = str(r['_month'])
    if m not in MONTHS_ACT:
        continue
    sp = str(r['_sp']).strip()
    cust = str(r['客户']).strip()
    amt = float(r['_amt'])
    pt = str(r[pt_col]) if pt_col else ''
    kpi_person, dept = core.map_person(sp, cust, person_dept, lzy, pt)
    eff_sp = _spm.merge_sp(kpi_person or sp)
    cust_sp.setdefault(cust, {}).setdefault(eff_sp, 0.0)
    cust_sp[cust][eff_sp] += amt
    if dept:
        cust_dept.setdefault(cust, {}).setdefault(dept, 0.0)
        cust_dept[cust][dept] += amt

spm = (json.load(open(f'{W}/数据/customer_sp_map.json', encoding='utf-8')) or {}).get('map') or {}

rows = []
for cust, d in cust_sp.items():
    tot = sum(d.values())
    dominant = max(d.items(), key=lambda kv: kv[1])[0]
    raw_owner = (spm.get(cust) or '').strip() or None
    owner = _spm.merge_sp(raw_owner) if raw_owner else None
    if owner != dominant:
        rows.append({
            'cust': cust,
            'owner': owner or '(无归属)',
            'owner_raw': raw_owner,
            'dominant': dominant,
            'amt_total': round(tot / 1e4, 2),
            'sp_breakdown': {k: round(v / 1e4, 2) for k, v in sorted(d.items(), key=lambda kv: -kv[1])},
            'depts': {k: round(v / 1e4, 2) for k, v in sorted(cust_dept.get(cust, {}).items(), key=lambda kv: -kv[1])},
        })

rows.sort(key=lambda x: -x['amt_total'])
print(f"不一致客户数: {len(rows)}  (ERP={os.path.basename(fp)})")
print("=" * 100)
for i, r in enumerate(rows[:25], 1):
    print(f"{i:2d}. {r['cust']}")
    print(f"    归属人={r['owner']}(raw={r['owner_raw']})  实际主下单人={r['dominant']}  差额={r['amt_total']}万")
    print(f"    下单人拆解={r['sp_breakdown']}  部门={r['depts']}")
json.dump(rows, open(f'{W}/.q3scratch/candidates_salesowner.json', 'w', encoding='utf-8'),
          ensure_ascii=False, indent=1)
print("\nsaved -> .q3scratch/candidates_salesowner.json")