#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""增量 ERP 合并进全年文件（铁律：不退回要求重传，直接合并）
策略：旧全年文件保留增量起始日之前的行；增量文件提供起始日之后的全部行（含修订）。
产出：月度销售额数据/销售订单_20260101-YYYYMMDD.xlsx（全年命名，find_latest_erp 按 mtime 会选中它）
回归校验：1-8 月逐月金额必须与基线完全一致；9 月按新数据更新。
"""
import os
import sys
import json
import shutil
import datetime

import pandas as pd

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

import os as _os
NEW = _os.environ.get('ERP_INCREMENT') or ''
assert NEW and _os.path.exists(NEW), '请设置 ERP_INCREMENT 指向增量文件'
DIR = os.path.expanduser('~/Desktop/hermas输入-工作台数据库/月度销售额数据')
BASE = json.load(open(os.path.join(ROOT, '数据/_erp_baseline_snapshot.json'), encoding='utf-8'))

old_fp = core.find_latest_erp()
old = pd.read_excel(old_fp, dtype=str)
new = pd.read_excel(NEW, dtype=str)

# 列结构对齐：新件可能是旧件的超集（如多出备注列），按旧件 schema 对齐后再拼
if list(old.columns) != list(new.columns):
    extra = [c for c in new.columns if c not in old.columns]
    missing = [c for c in old.columns if c not in new.columns]
    print('⚠️ 列结构不一致 → 对齐到现用 schema')
    print('   旧 %d 列 / 新 %d 列 ｜ 新件多余(丢弃): %s ｜ 新件缺失: %s' % (
        len(old.columns), len(new.columns), extra, missing))
    assert not missing, '新件缺少旧件必需列，不能合并: %s' % missing
    new = new[list(old.columns)]

old['_d'] = old['合同日期'].apply(core.ser2date)
new['_d'] = new['合同日期'].apply(core.ser2date)

cut = new['_d'][new['_d'].str.match(r'^\d{4}-\d{2}-\d{2}$')].min()
end = new['_d'][new['_d'].str.match(r'^\d{4}-\d{2}-\d{2}$')].max()
print('增量区间: %s → %s ｜ 切分点: %s' % (cut, end, cut))

old_keep = old[old['_d'] < cut]
new_keep = new[(new['_d'] >= cut) & (new['_d'] <= end)].dropna(subset=['_d'])
old_keep = old_keep.drop(columns=['_d'])
new_keep = new_keep.drop(columns=['_d'])

print('旧文件保留: %d 行（丢弃 %d 行重叠区）' % (len(old_keep), len(old) - len(old_keep)))
print('增量采用  : %d 行（含合计行已剔除）' % len(new_keep))

merged = pd.concat([old_keep, new_keep], ignore_index=True)
out_name = '销售订单_20260101-%s.xlsx' % end.replace('-', '')
out_fp = os.path.join(DIR, out_name)
# iCloud/fileprovider 对大 xlsx 直接写会偶发 Errno 11：先写同目录临时文件，再原子替换
tmp_fp = os.path.join(DIR, '.%s.tmp-%s.xlsx' % (out_name, datetime.datetime.now().strftime('%Y%m%d%H%M%S')))
try:
    merged.to_excel(tmp_fp, index=False)
    os.replace(tmp_fp, out_fp)
finally:
    if os.path.exists(tmp_fp):
        os.remove(tmp_fp)
print('✅ 合并文件: %s ｜ %d 行 ｜ %.2f MB' % (out_name, len(merged), os.path.getsize(out_fp) / 1024 / 1024))

# 归档原始增量文件（放子目录，find_latest_erp 用 listdir 不递归，不会被误选）
arch = os.path.join(DIR, '_增量归档')
os.makedirs(arch, exist_ok=True)
shutil.copy2(NEW, os.path.join(arch, '销售订单_%s_%s.xlsx' % (cut.replace('-', ''), end.replace('-', ''))))
print('📦 原始增量已归档: _增量归档/')

# ── 回归校验 ───────────────────────────────────────────────
os.utime(out_fp, None)   # 确保 mtime 最新
picked = core.find_latest_erp()
print()
print('find_latest_erp 现在选中:', os.path.basename(picked), '✅' if picked == out_fp else '❌ 不是合并文件！')
assert picked == out_fp, '管道未选中合并文件，中止'

mt, mp, md, src = core.aggregate(out_fp)
W = 1e4
print()
print('--- 回归校验：逐月对比基线（万元）---')
print('   %-9s %10s %10s %10s %s' % ('月份', '基线', '更新后', '差值', '判定'))
ok = True
for m in sorted(set(list(BASE['monthly_total'].keys()) + list(mt.keys()))):
    b = BASE['monthly_total'].get(m, 0) / W
    n = mt.get(m, 0) / W
    diff = n - b
    if m <= '2026-08':
        verdict = '✅ 未变' if abs(diff) < 0.05 else '❌ 历史月被改动！'
        if abs(diff) >= 0.05:
            ok = False
    else:
        verdict = '🔄 更新（+%.1f 万）' % diff
    print('   %-9s %10.1f %10.1f %10.1f  %s' % (m, b, n, diff, verdict))

print()
print('部门 9 月变化:')
for d in sorted({x for m in md for x in md[m]}):
    b = BASE['monthly_dept'].get('2026-09', {}).get(d, 0) / W
    n = md.get('2026-09', {}).get(d, 0) / W
    print('   %-14s %8.1f → %8.1f 万  (%+.1f)' % (d, b, n, n - b))

print()
print('H1(1-6月) 基线 %.1f 万 → 现在 %.1f 万' % (
    sum(BASE['monthly_total'].get(m, 0) for m in BASE['monthly_total'] if m <= '2026-06') / W,
    sum(mt[m] for m in mt if m <= '2026-06') / W))
print('全年累计 基线 %.1f 万 → 现在 %.1f 万' % (sum(BASE['monthly_total'].values()) / W, sum(mt.values()) / W))
print()
print('回归结论:', '✅ 历史月零改动，仅有 9 月更新' if ok else '❌ 历史月被改动，需排查')
print('MERGE_OK=' + ('1' if ok else '0'))