#!/usr/bin/env python3
"""KPI 刷新管线：从飞书导出Excel → kpi_progress.json
用法：python3 refresh_kpi.py [xlsx_path]
默认路径：~/Desktop/Hermes输出-工作类/数据/销售订单导出-销售管理.xlsx
"""
import json, os, sys, openpyxl
from collections import defaultdict
from datetime import datetime

DATA_DIR = os.path.expanduser('~/Desktop/Hermes输出-工作类/数据')
DEFAULT_XLSX = os.path.expanduser('~/Desktop/Hermes输出-工作类/数据/销售订单导出-销售管理.xlsx')

def refresh(xlsx_path=None):
    path = xlsx_path or DEFAULT_XLSX
    if not os.path.exists(path):
        print(f'❌ 文件不存在: {path}')
        print(f'   请从飞书项目导出销售订单Excel到此路径')
        sys.exit(1)
    
    wb = openpyxl.load_workbook(path, data_only=True)
    ws = wb.active
    
    # 定位列
    xdr_idx = amt_idx = bn_idx = None
    for row in ws.iter_rows(min_row=1, max_row=5, values_only=True):
        for i, c in enumerate(row):
            s = str(c).strip() if c else ''
            if s == '下单人': xdr_idx = i
            if '价税合计' in s: amt_idx = i
            if '单据编号' in s: bn_idx = i
        if xdr_idx is not None: break
    
    if xdr_idx is None:
        print('❌ 未找到"下单人"列')
        sys.exit(1)
    
    # 按7月汇总
    july_by_sp = defaultdict(float)
    errors = 0
    total_rows = 0
    
    for row in ws.iter_rows(min_row=2, values_only=True):
        if not row: continue
        total_rows += 1
        xdr = str(row[xdr_idx]).strip() if len(row) > xdr_idx and row[xdr_idx] else ''
        bn = str(row[bn_idx]).strip() if len(row) > bn_idx and row[bn_idx] else ''
        
        try:
            amt = float(row[amt_idx]) if len(row) > amt_idx and row[amt_idx] else 0
        except (ValueError, TypeError):
            errors += 1
            continue
        
        if not xdr or amt == 0: continue
        if bn.startswith('ZHBR2607'):
            july_by_sp[xdr] += amt
    
    july_total = sum(july_by_sp.values())
    
    # 写入
    kp_path = os.path.join(DATA_DIR, 'kpi_progress.json')
    with open(kp_path) as f:
        kp = json.load(f)
    
    kp['updated_at'] = datetime.now().strftime('%Y-%m-%dT%H:%M:%S')
    kp['total'] = july_total
    kp['by_salesperson'] = dict(july_by_sp)
    kp['order_count'] = sum(1 for _ in july_by_sp.values())
    
    with open(kp_path, 'w') as f:
        json.dump(kp, f, ensure_ascii=False, indent=2)
    
    print(f'✅ kpi_progress.json 已刷新')
    print(f'   {total_rows}行 → {len(july_by_sp)}人 → {july_total/1e4:.1f}万')
    for name, amt in sorted(july_by_sp.items(), key=lambda x: x[1], reverse=True)[:5]:
        print(f'   {name}: {amt/1e4:.1f}万')

if __name__ == '__main__':
    refresh(sys.argv[1] if len(sys.argv) > 1 else None)