#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
ERP 订单完整明细索引 —— 服务「订单管理」Tab 的订单详情（品名/货号/规格/数量/单价/金额）

产出：数据/orders_detail_2026.json
  结构：{updated_at, source, count, orders: {<单据编号>: {no, customer, sales, date, type, status,
         contract_no, total, line_count, lines:[{sku, code, name, spec, unit, qty, price, tax, amount,
         gift, batch, ptype, group}]}}}
用法：/usr/bin/python3 脚本/gen_orders_detail.py
"""
import json
import os
import sys
import io
from datetime import datetime

HERE = os.path.dirname(os.path.abspath(__file__))
ROOT = os.path.dirname(HERE)
sys.path.insert(0, HERE)


def f(v):
    """安全转 float"""
    if v is None:
        return 0.0
    s = str(v).strip().replace(',', '')
    if s in ('', '-', 'None', 'nan', 'NaN', '合计'):
        return 0.0
    try:
        return float(s)
    except ValueError:
        return 0.0


def s(v):
    if v is None:
        return ''
    t = str(v).strip()
    return '' if t in ('None', 'nan', 'NaN') else t


def main():
    import erp_sales_core as E
    import pandas as pd

    fp = E.find_latest_erp()
    if not fp:
        print('❌ 未找到 ERP 文件')
        return 1
    df = pd.read_excel(fp, dtype=str)
    print('源文件: %s ｜ %d 行' % (os.path.basename(fp), len(df)))

    orders = {}
    skipped = 0
    for _, r in df.iterrows():
        d = s(r.get('合同日期'))
        if not d.startswith('2026'):
            skipped += 1
            continue
        no = s(r.get('单据编号'))
        if not no:
            skipped += 1
            continue
        o = orders.get(no)
        if o is None:
            o = orders[no] = {
                'no': no,
                'customer': s(r.get('客户')),
                'sales': s(r.get('销售员')),
                'date': d.split(' ')[0] if ' ' in d else d,
                'type': s(r.get('单据类型')),
                'status': s(r.get('单据状态')),
                'contract_no': s(r.get('合同编号#')),
                'total': 0.0,
                'recipient': s(r.get('收货人姓名')),
                'phone': s(r.get('收货人联系电话#')),
                'address': s(r.get('收货方地址')) or s(r.get('收货地址')),
                'trackingNo': s(r.get('物流单号#')) or s(r.get('物流单号')),
                'recipientCompany': s(r.get('收货方')),
                'shipDate': s(r.get('发货日期')),
                'lines': [],
            }
        # 同一订单的收货/物流字段可能只在后续行出现，逐行补空
        for _k, _col in [('recipient', '收货人姓名'), ('phone', '收货人联系电话#'),
                         ('address', '收货方地址'), ('trackingNo', '物流单号#'),
                         ('recipientCompany', '收货方'), ('shipDate', '发货日期')]:
            if not o.get(_k) and s(r.get(_col)):
                o[_k] = s(r.get(_col))
        qty = f(r.get('销售数量'))
        price = f(r.get('含税单价'))
        amt = f(r.get('价税合计'))
        sku = s(r.get('货号#')) or s(r.get('物料编码'))
        o['lines'].append({
            'sku': sku,
            'code': s(r.get('物料编码')),
            'name': s(r.get('物料名称')),
            'spec': s(r.get('客户物料规格型号#')),
            'unit': s(r.get('销售单位')),
            'qty': round(qty, 4),
            'price': round(price, 6),
            'tax': round(f(r.get('税率%')), 2),
            'amount': round(amt, 2),
            'gift': s(r.get('是否赠品')) == '是',
            'batch': s(r.get('出货批号#')),
            'ptype': s(r.get('产品类型#')),
            'pgroup': s(r.get('销售分组（二级）')),
        })
        o['total'] = round(o['total'] + amt, 2)

    for o in orders.values():
        o['line_count'] = len(o['lines'])
        o['is_test'] = o['type'] == '测试销售订单'
        o['is_forecast'] = o['type'] == '预测订单' or o['no'].startswith('FO')
        o['has_gift'] = any(x['gift'] for x in o['lines'])
        o['gift_amount'] = round(sum(x['amount'] for x in o['lines'] if x['gift']), 2)

    out = {
        'updated_at': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        'source': os.path.basename(fp),
        'count': len(orders),
        'line_count': sum(o['line_count'] for o in orders.values()),
        'orders': orders,
    }
    dst = os.path.join(ROOT, '数据', 'orders_detail_2026.json')
    with io.open(dst, 'w', encoding='utf-8') as fh:
        json.dump(out, fh, ensure_ascii=False, separators=(',', ':'))

    size = os.path.getsize(dst) / 1e6
    multi = sum(1 for o in orders.values() if o['line_count'] > 1)
    print('单据 %d ｜ 明细行 %d ｜ 一单多货号 %d 单 (%.0f%%) ｜ 跳过 %d 行'
          % (len(orders), out['line_count'], multi, 100.0 * multi / len(orders), skipped))
    print('测试单 %d ｜ 预测单 %d ｜ 含赠品单 %d'
          % (sum(1 for o in orders.values() if o['is_test']),
             sum(1 for o in orders.values() if o['is_forecast']),
             sum(1 for o in orders.values() if o['has_gift'])))
    print('✅ %s（%.2f MB）' % (dst, size))

    k = 'ZHBR260921035'
    if k in orders:
        o = orders[k]
        print()
        print('样例 %s ｜ %s ｜ %s ｜ %s ｜ 合计 %s 元 ｜ %d 行'
              % (o['no'], o['customer'][:20], o['date'], o['type'], o['total'], o['line_count']))
        for x in o['lines'][:4]:
            print('   · %-22s %-40s %s%s @%s 税率%s%% = %s%s'
                  % (x['sku'], x['name'][:40], x['qty'], x['unit'], x['price'], x['tax'], x['amount'],
                     ' [赠品]' if x['gift'] else ''))
    return 0


if __name__ == '__main__':
    sys.exit(main())