#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""大客户部客户清单（权威口径）—— 修复部门映射偏差

归属优先级（与工作台数据管道完全一致）：
  1) 脚本/apply_sales_owner.py 的 CUSTOMER_OWNER_OVERRIDE（用户 2026-10-06 拍板，15 条）
  2) 数据/customer_sp_map.json（最近月份最常见销售员）
  3) 人员归并：黄明月 / 张立娅 / 刘新元 → 刘子研（大客户1岗）
  部门：人员 → 数据/目标基础数据_2026.json（大客户部 = 刘子研 / 赵云浩）

输出：数据/大客户部_客户清单.json
"""
import io
import json
import os
import sys
import warnings
from collections import defaultdict

warnings.filterwarnings('ignore')
import openpyxl  # noqa: E402

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
D = W + '/数据/'
BASE = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库'
sys.path.insert(0, W + '/脚本')

from apply_sales_owner import CUSTOMER_OWNER_OVERRIDE  # noqa: E402

MERGE = {'黄明月': '刘子研', '张立娅': '刘子研', '刘新元': '刘子研'}
SRC = [('2025', BASE + '/2024和2025历史数据/2025全年订单.xlsx', 'Sheet1'),
       ('2026', __import__('sys').path.insert(0, '/Users/liuxinyuan/Desktop/Hermes输出-工作类/脚本') or __import__('erp_sales_core').find_latest_erp(), None)]
M25 = {'2025-%02d' % i for i in range(1, 10)}
M26 = {'2026-%02d' % i for i in range(1, 10)}
BAORUI = ('宝锐', '宝泰', '横琴')


def load():
    rows = []
    for tag, path, sheet in SRC:
        wb = openpyxl.load_workbook(path, data_only=True, read_only=True)
        ws = wb[sheet] if sheet else wb[wb.sheetnames[0]]
        hdr, ix = None, {}
        for r in ws.iter_rows(min_row=1, values_only=True):
            if hdr is None:
                hdr = [str(x or '').strip() for x in r]
                ix = {k: hdr.index(k) for k in hdr if k in
                      ('货号#', '物料名称', '价税合计', '销售数量', '客户', '销售员', '是否赠品',
                       '单据类型', '单据状态', '合同月份', '合同日期')}
                continue
            g = {k: (r[v] if v < len(r) else None) for k, v in ix.items()}
            if '标准' not in str(g.get('单据类型') or ''):
                continue
            if str(g.get('是否赠品') or '').strip() == '是':
                continue
            if '单据状态' in g and str(g.get('单据状态') or '').strip() not in ('已审核', ''):
                continue
            c = str(g.get('客户') or '').strip()
            if not c or any(k in c for k in BAORUI):
                continue
            mon = str(g.get('合同月份') or g.get('合同日期') or '')[:7]
            if (tag == '2025' and mon not in M25) or (tag == '2026' and mon not in M26):
                continue
            try:
                amt = float(g.get('价税合计') or 0)
            except Exception:
                amt = 0.0
            rows.append({'y': tag, 'cust': c, 'sp': str(g.get('销售员') or '').strip(), 'amt': amt})
    return rows


rows = load()
print('█ ERP 有效明细 %d 行 ｜ 客户 %d 家' % (len(rows), len({r['cust'] for r in rows})))

# 2026 主表：部门权威判定（已含 override + merge）
scope = json.load(io.open(D + '三部门逐月_2026.json', encoding='utf-8'))['customers']
# 客户部门：按「2026 年金额最大的部门」定；2026 全为 0 时回退 2025 年金额最大的部门
#   —— 表里同一客户会按部门拆成多行（2025 金额挂当年部门、2026 挂现部门），不能取任意一行
_A26, _A25 = defaultdict(lambda: defaultdict(float)), defaultdict(lambda: defaultdict(float))
for c in scope:
    _A26[c['cust']][c['dept']] += c.get('amt') or 0.0
    _A25[c['cust']][c['dept']] += c.get('amt_2025') or 0.0


def dept_pick(cust):
    d = _A26.get(cust) or {}
    if sum(d.values()) > 0:
        return max(d.items(), key=lambda kv: kv[1])[0], '2026'
    d = _A25.get(cust) or {}
    if d:
        return max(d.items(), key=lambda kv: kv[1])[0], '2025'
    return '', ''


dept_of_cust, dept_basis = {}, {}
for c in scope:
    nm = c['cust']
    if nm in dept_of_cust:
        continue
    dp, basis = dept_pick(nm)
    dept_of_cust[nm], dept_basis[nm] = dp, basis

# 客户 → 销售员（override > sp_map > merge）
spm = json.load(io.open(D + 'customer_sp_map.json', encoding='utf-8'))['map']
def owner(c):
    return MERGE.get(CUSTOMER_OWNER_OVERRIDE.get(c) or spm.get(c) or '', CUSTOMER_OWNER_OVERRIDE.get(c) or spm.get(c) or '')

# 人员 → 部门（权威源）
t = json.load(io.open(D + '目标基础数据_2026.json', encoding='utf-8'))
ppl_dept = {p['name']: p['dept'] for p in t['people']}
DK_PEOPLE = {'刘子研', '赵云浩'}

# 全部客户：2025 或 2026 出现
allc = sorted({r['cust'] for r in rows})
recs = []
for c in allc:
    o = owner(c)
    dept = dept_of_cust.get(c) or ppl_dept.get(o, '')
    a25 = sum(r['amt'] for r in rows if r['y'] == '2025' and r['cust'] == c)
    a26 = sum(r['amt'] for r in rows if r['y'] == '2026' and r['cust'] == c)
    recs.append({'cust': c, 'owner': o, 'dept': dept, 'dept_basis': dept_basis.get(c, ''),
                 'a25': round(a25, 2), 'a26': round(a26, 2), 'in_table': bool(dept_of_cust.get(c))})

dk = [x for x in recs if x['dept'] == '大客户部']
dk_2025only = [x for x in dk if not x['in_table']]
print('\n█ 大客户部客户合计 %d 家' % len(dk))
print('   其中 2026 有流水（表内）: %d 家 ｜ 仅 2025 有流水（靠归属链路补入）: %d 家' % (len([x for x in dk if x['in_table']]), len(dk_2025only)))
for x in sorted(dk_2025only, key=lambda y: -y['a25']):
    print('      补入 %-34s 归属 %-5s ｜ 2025 %7.1f → 2026 %7.1f 万' % (x['cust'][:32], x['owner'], x['a25'] / 1e4, x['a26'] / 1e4))
print('   2025 合计 %.1f 万 ｜ 2026 合计 %.1f 万' % (sum(x['a25'] for x in dk) / 1e4, sum(x['a26'] for x in dk) / 1e4))

# 旧清单反查
old = json.load(io.open(D + '大客户部_货号下降_待判定.json', encoding='utf-8'))
oldc = {x['cust'] for k in ('declines', 'news', 'grows') for x in old[k]}
print('\n█ 旧清单 %d 家 → 权威判定' % len(oldc))
bad = []
for c in sorted(oldc):
    hit = [x for x in recs if x['cust'] == c]
    if hit:
        x = hit[0]
        if x['dept'] != '大客户部':
            bad.append((c, x['owner'], x['dept'], x['a25'], x['a26']))
print('   确认不属于大客户部 %d 家：' % len(bad))
for c, o, d, a25, a26 in sorted(bad, key=lambda z: -(z[3] + z[4])):
    print('      %-34s 归属 %-5s %-6s ｜ 2025 %7.1f → 2026 %7.1f 万' % (c[:32], o or '(无)', d, a25 / 1e4, a26 / 1e4))

json.dump({'generated': '2026-10-17',
           'caliber': '归属=apply_sales_owner.CUSTOMER_OWNER_OVERRIDE > customer_sp_map.json；人员归并 黄明月/张立娅/刘新元→刘子研；部门=目标基础数据_2026.json',
           'dk_people': sorted(DK_PEOPLE),
           'customers': dk,
           'excluded_from_old_list': [{'cust': c, 'owner': o, 'dept': d, 'a25': a25, 'a26': a26} for c, o, d, a25, a26 in bad]},
          io.open(D + '大客户部_客户清单.json', 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
print('\n✅ 写入 数据/大客户部_客户清单.json（%d 家）' % len(dk))