#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把 20261029 全量 ERP 的 2026 部分合并进 2026 主档，并保留新增物流字段。
C+B 口径：只合并 2026；2024/2025 历史不进入 2026 主档。
订单级覆盖：新文件出现的单据，用新文件全部行替换旧行；旧文件独有单据保留。
"""
import json
import os
import shutil
import sys
from datetime import datetime

import pandas as pd

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
DIR = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据')
NEW = os.path.join(DIR, '_增量归档', '销售订单_20240101-20261029(补充收货人、物流单号、收货地址信息）.xlsx')
OUT = os.path.join(DIR, '销售订单_20260101-20261029.xlsx')
sys.path.insert(0, os.path.join(ROOT, '脚本'))
import erp_sales_core as E


def clean(df):
    df = df.copy()
    df = df[df['单据编号'].notna()]
    df = df[df['单据编号'].astype(str).str.strip().ne('')]
    df = df[df['单据编号'].astype(str).str.strip().ne('合计')]
    for c in df.columns:
        df[c] = df[c].astype(str).str.strip().replace({'nan': '', 'None': ''})
    return df


def main():
    old_fp = E.find_latest_erp()
    assert old_fp and old_fp != OUT, '当前最新 ERP 异常: %s' % old_fp
    print('旧主档:', os.path.basename(old_fp))
    print('新全量:', os.path.basename(NEW))
    old = clean(pd.read_excel(old_fp, dtype=str))
    new = clean(pd.read_excel(NEW, dtype=str))
    new['_d'] = new['合同日期'].str[:10]
    new26 = new[new['_d'].str.match(r'^2026-')].drop(columns=['_d']).copy()
    print('旧 2026 行:', len(old), '订单:', old['单据编号'].nunique())
    print('新 2026 行:', len(new26), '订单:', new26['单据编号'].nunique())

    old_by = {k: g for k, g in old.groupby('单据编号', sort=False)}
    new_by = {k: g for k, g in new26.groupby('单据编号', sort=False)}
    old_ids, new_ids = set(old_by), set(new_by)
    replaced = old_ids & new_ids
    old_only = old_ids - new_ids
    new_only = new_ids - old_ids
    print('重叠订单:', len(replaced), '旧独有:', len(old_only), '新增订单:', len(new_only))

    cols = list(old.columns) + [c for c in new26.columns if c not in old.columns]
    frames = []
    # 旧独有订单原样保留
    for oid in old_by:
        if oid not in new_by:
            frames.append(old_by[oid].reindex(columns=cols))
    # 新文件订单全部采用新行
    for oid in new_by:
        frames.append(new_by[oid].reindex(columns=cols))
    merged = pd.concat(frames, ignore_index=True)
    # 按合同日期/单据编号排序，便于人工核对
    merged['_sort'] = merged['合同日期'].str[:10]
    merged = merged.sort_values(['_sort', '单据编号'], kind='stable').drop(columns=['_sort'])

    # 备份旧输出（若已存在）
    if os.path.exists(OUT):
        bak = OUT + '.bak_' + datetime.now().strftime('%Y%m%d_%H%M%S')
        shutil.copy2(OUT, bak)
        print('旧输出备份:', os.path.basename(bak))
    merged.to_excel(OUT, index=False)
    os.utime(OUT, None)
    print('✅ 写入', OUT, '行', len(merged), '大小 %.2f MB' % (os.path.getsize(OUT) / 1024 / 1024))

    mt, mp, md, src = E.aggregate(OUT)
    report = {
        'generated_at': datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        'old_file': os.path.basename(old_fp), 'new_file': os.path.basename(NEW),
        'output': os.path.basename(OUT),
        'old_rows': len(old), 'new_2026_rows': len(new26), 'merged_rows': len(merged),
        'old_orders': len(old_ids), 'new_2026_orders': len(new_ids),
        'replaced_orders': len(replaced), 'old_only_orders': len(old_only), 'new_only_orders': len(new_only),
        'monthly_total': {k: round(v, 2) for k, v in sorted(mt.items()) if k.startswith('2026-')},
        'year_total': round(sum(v for k, v in mt.items() if k.startswith('2026')), 2),
    }
    rp = os.path.join(ROOT, '数据', 'erp_merge_20261029_report.json')
    with open(rp, 'w', encoding='utf-8') as f:
        json.dump(report, f, ensure_ascii=False, indent=2)
    print('月份:', report['monthly_total'])
    print('2026 全年:', report['year_total'])
    print('报告:', rp)
    print('find_latest_erp:', os.path.basename(E.find_latest_erp()))


if __name__ == '__main__':
    main()