#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""从最新 ERP 订单导出构建「订单编号 → 客户/销售员/货号」索引，供活动录入页订单下拉使用。
输出：数据/orders_2026_index.json
  { "updated_at": "...", "source": "文件名",
    "orders": [ {"no","customer","sales","date","types":["标准销售订单"],
                 "skus":[{"sku","name","qty","gift"}], "amount"} ] }
用法：/usr/bin/python3 脚本/gen_orders_index.py [源xlsx]
"""
import sys, os, json, re, collections, datetime
import pandas as pd

ROOT = os.path.expanduser('~/Desktop/Hermes输出-工作类')
SRC_DIR = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据')
OUT = os.path.join(ROOT, '数据/orders_2026_index.json')


def pick_source():
    if len(sys.argv) > 1:
        return sys.argv[1]
    cands = [f for f in os.listdir(SRC_DIR) if f.endswith('.xlsx') and '销售订单' in f]
    if not cands:
        raise SystemExit('未找到销售订单导出 xlsx')
    # 取覆盖区间最长的（文件名形如 销售订单_20260101-20260921.xlsx）
    def span(f):
        m = re.findall(r'20\d{6}', f)
        return (len(m), max(m) if m else '')
    return os.path.join(SRC_DIR, sorted(cands, key=span)[-1])


def main():
    src = pick_source()
    df = pd.read_excel(src, sheet_name=0)
    need = ['单据编号', '客户', '销售员', '合同日期', '单据类型', '货号#', '物料名称', '销售数量', '是否赠品']
    for c in need:
        if c not in df.columns:
            raise SystemExit('缺列: %s（实际列: %s）' % (c, list(df.columns)))

    df = df[df['单据编号'].notna()].copy()
    df['单据编号'] = df['单据编号'].astype(str).str.strip()
    df = df[df['单据编号'] != '']

    # 合同日期 → YYYY-MM-DD
    def d2s(v):
        try:
            return pd.to_datetime(v).strftime('%Y-%m-%d')
        except Exception:
            return ''
    df['_date'] = df['合同日期'].map(d2s)

    orders = {}
    for no, g in df.groupby('单据编号', sort=False):
        first = g.iloc[0]
        skus, seen = [], set()
        for _, r in g.iterrows():
            sku = str(r['货号#']).strip() if pd.notna(r['货号#']) else ''
            if not sku or sku.lower() == 'nan':
                continue
            key = (sku, str(r['物料名称'])[:60])
            if key in seen:
                continue
            seen.add(key)
            qty = r['销售数量']
            skus.append({
                'sku': sku,
                'name': re.sub(r'\s+', ' ', str(r['物料名称']))[:60] if pd.notna(r['物料名称']) else '',
                'qty': (round(float(qty), 2) if pd.notna(qty) else 0),
                'gift': (str(r['是否赠品']).strip() == '是'),
            })
        types = sorted(set(str(x).strip() for x in g['单据类型'].dropna()))
        dates = sorted([x for x in g['_date'] if x])
        orders[no] = {
            'no': no,
            'customer': str(first['客户']).strip() if pd.notna(first['客户']) else '',
            'sales': str(first['销售员']).strip() if pd.notna(first['销售员']) else '',
            'date': dates[-1] if dates else '',
            'types': types,
            'is_test': any('测试' in t for t in types),
            'skus': skus,
        }

    out = {
        'updated_at': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        'source': os.path.basename(src),
        'count': len(orders),
        'orders': sorted(orders.values(), key=lambda o: o['date'], reverse=True),
    }
    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    with open(OUT, 'w', encoding='utf-8') as f:
        json.dump(out, f, ensure_ascii=False, separators=(',', ':'))

    size = os.path.getsize(OUT) / 1024.0
    print('  源文件 : %s' % os.path.basename(src))
    print('  订单数 : %d' % len(orders))
    print('  货号行 : %d' % sum(len(o['skus']) for o in orders.values()))
    print('  含货号订单 : %d（空货号订单 %d）' % (sum(1 for o in orders.values() if o['skus']), sum(1 for o in orders.values() if not o['skus'])))
    print('  测试单 : %d ｜ 2026 日期区间: %s ~ %s' % (
        sum(1 for o in orders.values() if o['is_test']),
        min((o['date'] for o in orders.values() if o['date']), default='-'),
        max((o['date'] for o in orders.values() if o['date']), default='-')))
    print('  输出 : %s  (%.1f KB)' % (OUT, size))


if __name__ == '__main__':
    main()