#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""补全 customer_attribution.json 中被截断的客户名单（声明数 > 数组实际数）。

来源：ERP 2025 全年 + 2026 1-9 月，按「销售员」字段取该人名下实际成交客户，
去重后追加到已有名单，直到达到该条声明的 customer_count（不足则有多少补多少）。
"""
import json
import os
import shutil
from datetime import datetime

import pandas as pd

P = os.path.expanduser('~/.hermes/profiles/baorui-sales/customer_attribution.json')
BASE = os.path.expanduser('~/Desktop/hermas输入-工作台数据库')
F25 = os.path.join(BASE, '2024和2025历史数据/2025全年订单.xlsx')
F26 = os.path.join(BASE, '月度销售额数据/销售订单_20260101-20260930.xlsx')


def erp_customers():
    out = {}
    for path, tag in ((F25, '2025'), (F26, '2026')):
        d = pd.ExcelFile(path).parse('Sheet1')
        d.columns = [str(c).strip() for c in d.columns]
        d['_sp'] = d['销售员'].astype(str).str.strip()
        d['_c'] = d['客户'].astype(str).str.strip()
        for sp, g in d.groupby('_sp'):
            cur = out.setdefault(sp, [])
            for c in g['_c'].unique():
                if c and c != 'nan' and c not in cur:
                    cur.append(c)
    return out


def main():
    A = json.load(open(P, encoding='utf-8'))
    sp = A['salespeople']
    erp = erp_customers()
    shutil.copy2(P, P + '.bak_before_backfill_' + datetime.now().strftime('%Y%m%d_%H%M%S'))
    fixed = []
    for name, v in sp.items():
        if not v:
            continue
        declared = v.get('customer_count', 0)
        have = list(v.get('customers', []))
        if declared <= len(have):
            continue
        pool = [c for c in erp.get(name, []) if c not in have]
        add = []
        for c in pool:
            if len(have) + len(add) >= declared:
                break
            add.append(c)
        if add:
            v['customers'] = have + add
            v.setdefault('_来源补全', '')  # 占位
            v['_来源补全'] = ('名单原被截断在 %d 条；已按 ERP（2025 全年 + 2026 1-9 月）'
                          '「销售员=%s」的实际成交客户补 %d 条，现 %d 条（声明 %d）'
                          % (len(have), name, len(add), len(v['customers']), declared))
            fixed.append((name, len(have), len(v['customers']), declared))
    A['total_customers'] = sum(len((v or {}).get('customers', [])) for v in sp.values())
    A['total_customers_declared'] = sum((v or {}).get('customer_count', 0) for v in sp.values())
    A['total_customers_unique'] = len({c for v in sp.values() for c in (v or {}).get('customers', [])})
    A.setdefault('_更新记录', []).append({
        '时间': datetime.now().strftime('%Y-%m-%d %H:%M'),
        '动作': '补全被截断名单：%s' % '、'.join(f[0] for f in fixed),
        '原因': '生成时名单被截断在 50 条 → 超出部分客户会被误判「不在归属范围」',
    })
    json.dump(A, open(P, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
    os.chmod(P, 0o600)
    print('✅ 补全 %d 人：' % len(fixed))
    for n, b, a, dcl in fixed:
        print('   %-8s %3d → %3d 条（声明 %d）%s' % (n, b, a, dcl, '  ← 仍不足（ERP 里没有更多）' if a < dcl else ''))
    print()
    A2 = json.load(open(P, encoding='utf-8'))
    sp2 = A2['salespeople']
    still = [(k, v.get('customer_count'), len(v.get('customers', [])))
             for k, v in sp2.items() if v and v.get('customer_count', 0) > len(v.get('customers', []))]
    print('仍不完整的条目：', still if still else '无 ✅')
    print('归属表：%d 人 ｜ 名单条数之和 %d ｜ 去重 %d' % (
        len(sp2), A2['total_customers'], A2['total_customers_unique']))


if __name__ == '__main__':
    main()