#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""补齐工作台订单：以 ERP 2026 全量为底座，补 MCP 未覆盖的订单（如 7-8 月断层）

产物：数据/erp_order_supplement.json
字段对齐 shipment_status.json 的订单结构，供前端订单列表直接渲染。
"""
import json
import os
import sys
import time
from collections import defaultdict

import openpyxl

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
sys.path.insert(0, os.path.join(ROOT, '脚本'))
import erp_sales_core as core  # noqa: E402

DATA = os.path.join(ROOT, '数据')
OUT = os.path.join(DATA, 'erp_order_supplement.json')


def main():
    pd, lzy = core.load_mappings()
    p = core.find_latest_erp()
    wb = openpyxl.load_workbook(p, data_only=True, read_only=True)
    ws = wb.worksheets[0]
    it = ws.iter_rows(values_only=True)
    H = [str(x or '').strip() for x in next(it)]
    ix = {h: j for j, h in enumerate(H)}

    existing = set()
    for f in (os.path.join(DATA, 'h1_archive', 'h1_all.json'),
              os.path.join(DATA, 'shipment_status.json')):
        if os.path.exists(f):
            d = json.load(open(f, encoding='utf-8'))
            for o in (d.get('orders') or []):
                existing.add(str(o.get('orderId')))

    agg = {}
    for r in it:
        if str(r[ix['单据状态']]) != '已审核':
            continue
        oid = str(r[ix['单据编号']] or '').strip()
        if not oid or oid in existing:
            continue
        cust = str(r[ix['客户']] or '')
        if any(k in cust for k in ('宝锐', '宝泰', '横琴')):
            continue
        qty = float(r[ix['销售数量']] or 0)
        amt = float(r[ix['价税合计']] or 0)
        d = str(r[ix['合同日期']] or '')[:10]
        e = agg.setdefault(oid, {'orderId': oid, 'customer': cust,
                                 'dept': '', 'createDate': d,
                                 'contractDate': d, 'trackingNo': '', 'amount': 0.0,
                                 'taxTotal': 0.0, 'amountSource': 'ERP', 'creator': '',
                                 'creatorEmail': '', 'recipient': '', 'phone': '', 'address': '',
                                 'contractNo': str(r[ix['合同编号#']] or ''), 'orderType': str(r[ix['单据类型']] or ''),
                                 'mcpShipDate': str(r[ix['发货日期']] or '')[:10], 'shipped': False,
                                 'shipment': {'status': 'no_tracking', 'statusLabel': '无运单号',
                                              'routes': [], 'routeCount': 0, 'lastRoute': None},
                                 'source': 'erp_supplement', 'lines': 0, 'skus': []})
        e['amount'] += amt
        e['taxTotal'] += amt
        e['lines'] += 1
        sku = str(r[ix['货号#']] or '').strip()
        if sku and sku not in e['skus']:
            e['skus'].append(sku)
        if float(r[ix['累计出库数量']] or 0) > 0:
            e['shipped'] = True
        pt = str(r[ix['产品类型#']] or '') if '产品类型#' in ix else ''
        person, dept2 = core.map_person(str(r[ix['销售员']] or ''), cust, pd, lzy, pt)
        if person:
            e['creator'] = person
        if dept2:
            e['dept'] = dept2

    orders = sorted(agg.values(), key=lambda x: x['createDate'])
    json.dump({'generated_at': time.strftime('%Y-%m-%d %H:%M:%S'), 'total': len(orders),
               'source_erp': os.path.basename(p), 'orders': orders},
              open(OUT, 'w', encoding='utf-8'), ensure_ascii=False)
    print('补充订单 %d 单 ｜ 金额 %.0f' % (len(orders), sum(o['taxTotal'] for o in orders)))
    by = defaultdict(int)
    for o in orders:
        by[o['createDate'][:7]] += 1
    print('按月', dict(sorted(by.items())))


if __name__ == '__main__':
    main()