#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
差旅费报销汇总表 · 自动归集（流程脚本）
============================================
输入：汇总表 xlsx（你只要填好「出差日期 + 出差地」）
输出：① 已填写的汇总表 xlsx（只补空格，不覆盖你手填的数；差异单独列出）
      ② 汇总表 PDF（供打印，插到报销包最前）
      ③ 按制度粘贴次序排列的差旅报销打印包 PDF

用法：
  python3 脚本/travel_summary.py --book <汇总表.xlsx> [--batch 8月W4]
                                [--mode fill|verify|report] [--no-pack] [--travelers 2]
"""
import argparse
import datetime
import glob
import json
import os
import re
import subprocess
import sys

sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
import invoice_core as core          # noqa: E402

BASE = core.BASE
OUT_XLSX = os.path.join(BASE, '工具')
OUT_PACK = os.path.join(BASE, '打印包')
CHROME = '/Applications/Google Chrome.app/Contents/MacOS/Google Chrome'

# ── 归集映射：费用类别(+票种) → 汇总表列 ──────────────────────────────
COL = {'飞机': 'D', '火车/高铁': 'E', '巴士': 'F', '轮船': 'G', '旅途其他': 'H',
       '市内交通': 'I', '住宿': 'J', '招待': 'L', '补助': 'M', '其他费用': 'N'}
COL_NAME = {'D': '旅途交通-飞机', 'E': '旅途交通-火车/高铁', 'F': '旅途交通-巴士',
            'G': '旅途交通-轮船', 'H': '旅途交通-其他', 'I': '市内交通费',
            'J': '住宿费', 'L': '业务招待费', 'M': '出差补助', 'N': '其他费用'}

# 打印包次序（制度：面单→汇总表→发票→行程单/住宿清单→小票→抵扣联）
PACK_ORDER = ['飞机', '火车/高铁', '巴士', '轮船', '旅途其他', '市内交通', '住宿', '招待', '其他费用']


def classify(rec, rules, clue=None):
    """把一张发票归到汇总表的一列。返回 (key, 说明)。文本线索优先，票种名兜底。"""
    text = (clue or {}).get('text') or ''
    cat = str((rec.get('分类') or {}).get('费用类别') or '')
    blob = str(rec.get('销方名称') or '') + str(rec.get('来源文件') or '') + \
        str(rec.get('发票类型') or '') + str(rec.get('票种') or '') + ' ' + text[:400]

    # 顺序即优先级：先判专属票面特征，再退到字段/文件名
    if re.search(r'宿费|住宿费|房费|酒店|宾馆|旅馆|民宿', blob):
        return '住宿', '住宿/宿费'
    if re.search(r'铁路电子客票|中国铁路|12306|站\s*[A-Z]?\d{1,4}|虹桥站|东站|南站|北站', blob) or '铁路' in cat:
        return '火车/高铁', '铁路电子客票'
    if re.search(r'航空运输电子客票|航班号|承运人|客票级别|电子发票（航空', blob) or re.search(r'航空', blob):
        return '飞机', '航空客票行程单'
    if re.search(r'租赁|租车|一嗨|神州|结算单|车辆.*租赁', blob):
        return '旅途其他', '租车费'
    # 停车费/通行费/ETC 归市内交通（与汇总表实际填法一致）；加油/充电才是私车公用→其他费用
    if re.search(r'停车费|通行费|ETC|高速公路|过路费', blob):
        return '市内交通', '停车/通行费'
    if re.search(r'加油|油品|汽油|柴油|充电', blob) or '私车公用' in cat:
        return '其他费用', '私车公用/油费'
    if re.search(r'滴滴|出行|出租|网约车|高德|T3|曹操|客运|打车', blob) or '市内交通' in cat or '目的地交通' in cat:
        return '市内交通', '市内/目的地交通'
    if re.search(r'汽车票|巴士|长途', blob):
        return '巴士', '汽车/巴士票'
    if '招待' in cat or re.search(r'餐饮|餐厅|外卖|食品', blob):
        return '招待', '业务招待'
    return '其他费用', '未明确 → 其他费用（请核对）'


def read_book(path):
    """读汇总表：出差日期 / 出差地 / 出差天数 / 年份"""
    import openpyxl
    wb = openpyxl.load_workbook(path)
    ws = wb['差旅费报销汇总表'] if '差旅费报销汇总表' in wb.sheetnames else wb.worksheets[0]
    year, fill_date, days = datetime.date.today().year, None, None
    for row in ws.iter_rows(min_row=1, max_row=6):
        for c in row:
            if isinstance(c.value, str) and '填报日期' in c.value:
                p = ws.cell(row=c.row, column=c.column + 1).value
                if isinstance(p, datetime.datetime):
                    fill_date = p.date()
            if isinstance(c.value, str) and '出差天数' in c.value:
                d = ws.cell(row=c.row, column=c.column + 1).value
                if isinstance(d, (int, float)):
                    days = int(d)
    if fill_date:
        year = fill_date.year
    rows = []
    for r in range(5, ws.max_row + 1):
        b = ws.cell(row=r, column=2).value      # 出差日期
        cp = ws.cell(row=r, column=3).value     # 出差地
        if b in (None, '') and cp in (None, ''):
            continue
        d = None
        if isinstance(b, datetime.datetime):
            d = b.date()
        elif isinstance(b, (int, float)):        # Excel 序列号
            d = (datetime.date(1899, 12, 30) + datetime.timedelta(days=int(b)))
        elif isinstance(b, str):
            m = re.search(r'(\d{1,2})\s*月\s*(\d{1,2})', b)
            if m:
                d = datetime.date(year, int(m.group(1)), int(m.group(2)))
        rows.append({'row': r, 'date': d, 'place': str(cp or '').strip(), 'col': {}})
    return wb, ws, rows, year, days, fill_date


CLUES = os.path.join(BASE, '数据', '_票面线索.json')


def load_clues():
    try:
        return json.load(open(CLUES, encoding='utf-8'))
    except Exception:
        return {}


def assign_key(rec, key):
    """归行日期：机票看行程日、铁路看乘车日、租车看取车日，其余看开票日期

    ★ 2026-10-25 修复：原来一律用开票日，导致同一天开票的去程+回程机票被并到同一行
      （D8=3300，重复计入 1600）。机票必须按票面/备注里的行程日归行。
    """
    cl = CLUES_CACHE.get(rec['id']) or {}
    open_date = str(rec.get('开票日期') or '')[:10]
    if key == '火车/高铁' and cl.get('travel'):
        return cl['travel']
    if key == '旅途其他':                     # 租车：取车日优先，其次还车日
        return cl.get('pickup') or cl.get('travel') or cl.get('return') or open_date
    if key == '飞机':
        blob = ' '.join(str(rec.get(k) or '') for k in ('备注', '项目名称', '销方名称'))
        blob += ' ' + ' '.join(str(v) for v in (rec.get('分类') or {}).values() if isinstance(v, str))
        m = (re.search(r'(?<!\d)(\d{1,2})\s*[/\-月]\s*(\d{1,2})(?!\d)', blob)
             or re.search(r'(\d{1,2})\s*月\s*(\d{1,2})\s*日', blob))
        if m and open_date:
            try:
                y = int(open_date[:4])
                cand = datetime.date(y, int(m.group(1)), int(m.group(2)))
                # 行程日与开票日相差超过 45 天视为误抓，回退开票日
                if abs((cand - datetime.date.fromisoformat(open_date)).days) <= 45:
                    return cand.isoformat()
            except Exception:
                pass
    return open_date


CLUES_CACHE = {}


def load_invoices():
    led = core.load_ledger()
    return [r for r in led['发票'] if r.get('类型') != '附件'], led


def main(argv):
    ap = argparse.ArgumentParser()
    ap.add_argument('--book', required=True)
    ap.add_argument('--batch', default='')
    ap.add_argument('--mode', default='fill', choices=['fill', 'verify', 'report'])
    ap.add_argument('--travelers', type=int, default=1, help='同行人数（补助与合住参考，不自动乘）')
    ap.add_argument('--no-pack', action='store_true')
    a = ap.parse_args(argv)

    rules = core.load_rules()
    wb, ws, rows, year, days, fill_date = read_book(a.book)
    rows = [r for r in rows if r['date']]
    if not rows:
        print('⚠️ 汇总表里没有可识别的「出差日期」行')
        return 1
    d0, d1 = min(r['date'] for r in rows), max(r['date'] for r in rows)
    lo, hi = d0, d1 + datetime.timedelta(days=2)      # 发票常滞后开，留 2 天窗口
    print('═══ 汇总表 ═══')
    print('  出差期间：%s ~ %s ｜ 天数：%s ｜ 合计 %d 行' % (d0, d1, days or '未填', len(rows)))
    for r in rows:
        print('     第%d行  %s  %s' % (r['row'], r['date'].strftime('%m-%d'), r['place']))

    invs, led = load_invoices()
    CLUES_CACHE.update(load_clues())
    # 租车发票 → 关联结算单的还车时间（按订单号匹配）
    orders = {}
    for r in led['发票']:
        m = re.search(r'订单\s*(\d{6,})', str(r.get('来源文件') or ''))
        if m and CLUES_CACHE.get(r['id'], {}).get('return'):
            orders[m.group(1)] = CLUES_CACHE[r['id']]['return']
    for r in invs:
        m = re.search(r'订单\s*(\d{6,})', str(r.get('来源文件') or ''))
        if m and m.group(1) in orders:
            CLUES_CACHE.setdefault(r['id'], {})['return'] = orders[m.group(1)]

    cand, outside = [], []
    for rec in invs:
        key0, _ = classify(rec, rules, CLUES_CACHE.get(rec['id']))
        ad = assign_key(rec, key0)                      # 归行日期（铁路看乘车日/租车看还车日）
        try:
            dd = datetime.date.fromisoformat(ad[:10])
        except Exception:
            dd = None
        if dd and lo <= dd <= hi:
            cand.append((rec, dd))
        elif dd and d0 - datetime.timedelta(days=5) <= dd <= d1 + datetime.timedelta(days=10):
            outside.append((rec, dd))

    print('\n═══ 归集（%d 张落在窗口 %s~%s）═══' % (len(cand), lo, hi))
    for rec, dd in sorted(cand, key=lambda x: x[1]):
        key, why = classify(rec, rules, CLUES_CACHE.get(rec['id']))
        # 行归属：住宿按「酒店城市」匹配出差地；其余按归行日期就近
        row = None
        if key == '住宿':
            seller = str(rec.get('销方名称') or '')
            for r in rows:
                for city in re.findall(r'[\u4e00-\u9fa5]{2,3}', r['place']):
                    if city and city in seller and city not in ('珠海',):
                        row = r
                        break
                if row:
                    break
        if row is None:
            row = min(rows, key=lambda r: abs((r['date'] - dd).days))
        amt = float(rec.get('价税合计') or 0)
        row['col'].setdefault(key, []).append((rec['id'], amt, why))
        print('   %s %s %9.2f  %-10s → 第%d行(%s) %s 列 ｜ %s'
              % (rec['id'][-4:], dd.strftime('%m-%d'), amt, (rec.get('分类') or {}).get('费用类别', ''),
                 row['row'], row['date'].strftime('%m-%d'), COL[key], why))

    print('\n═══ 汇总 ═══')
    hdr = '  行  日期    出差地                  ' + '  '.join('%-9s' % COL_NAME[c] for c in
                                                          ['D', 'I', 'J', 'L', 'M', 'N'])
    print(hdr)
    total = 0.0
    plan = []
    for r in rows:
        vals = {}
        for key, items in r['col'].items():
            vals[COL[key]] = round(sum(x[1] for x in items), 2)
        # 出差补助：制度 80 元/天（不满一天减半）
        sub = float((rules.get('标准与限额') or {}).get('出差补助', {}).get('每天', 80) or 80)
        vals.setdefault('M', sub)
        line_total = sum(v for k, v in vals.items() if k in 'IJLMN') + vals.get('D', 0) + \
            vals.get('E', 0) + vals.get('F', 0) + vals.get('G', 0) + vals.get('H', 0)
        total += line_total
        plan.append({'row': r, 'vals': vals, 'total': round(line_total, 2)})
        print('  %2d  %s  %-20s %s' % (r['row'], r['date'].strftime('%m-%d'), r['place'][:20],
                                       '  '.join('%s=%s' % (k, vals.get(k, '')) for k in ['D', 'I', 'J', 'L', 'M', 'N'])))
    print('  合计 %.2f 元' % total)

    # 与表内已有值对比
    print('\n═══ 与表内已有值对比（只报告差异，不覆盖）═══')
    diff = 0
    for p in plan:
        for k, v in p['vals'].items():
            cell = ws['%s%d' % (k, p['row']['row'])]
            cur = cell.value
            if cur in (None, ''):
                continue
            num = None
            if isinstance(cur, (int, float)):
                num = float(cur)
            elif isinstance(cur, str) and cur.startswith('='):
                try:
                    expr = cur[1:].replace('*', '*').replace('+', '+')
                    if re.fullmatch(r'[\d\.\+\*\(\)\s]+', expr):
                        num = float(eval(expr))
                except Exception:
                    num = None
            if num is not None and abs(num - v) > 0.01:
                print('   ⚠️ %s%d 表内 %s ｜ 发票归集 %.2f' % (k, p['row']['row'], cur, v))
                diff += 1
    if not diff:
        print('   ✅ 无差异（表内已填值与我归集的一致）')

    if a.mode == 'report':
        print('\n（report 模式：不写文件）')
        return 0

    # ── 写 xlsx：最小改动（格式零变化、不加工作表、不动同住人/备注）──
    import xlsx_fill_min as MX
    edits = {}
    # ★ 跨行防重复：同一金额若已在同列其他行计入，跳过并在报告里点名
    def _col_values(col, lo=5, hi=15):
        out = {}
        for rr in range(lo, hi + 1):
            vv = ws['%s%d' % (col, rr)].value
            if isinstance(vv, (int, float)):
                out[rr] = float(vv)
        return out
    for p in plan:
        for k, v in p['vals'].items():
            ref = '%s%d' % (k, p['row']['row'])
            cell = ws[ref]
            if cell.value not in (None, ''):
                continue
            dup = [rr for rr, vv in _col_values(k).items() if abs(vv - v) <= 0.01]
            if dup:
                print('   ↷ 跳过 %s（%.2f 已在第 %s 行计入，避免重复）' % (ref, v, ','.join(map(str, dup))))
                continue
            edits[ref] = v
    stem = os.path.splitext(os.path.basename(a.book))[0]
    out_xlsx = os.path.join(OUT_XLSX, stem + '.xlsx')
    filled, skipped = MX.fill(a.book, out_xlsx, '差旅费报销汇总表', edits)
    print('\n✅ 汇总表已【最小改动】写出：%s' % out_xlsx)
    print('   填入 %d 格：%s%s' % (len(filled), ' '.join(filled),
                                 (' ｜ 跳过：%s' % skipped) if skipped else ''))
    same, diffent = MX.verify(a.book, out_xlsx, '差旅费报销汇总表')
    print('   其余 %d 个内部部件逐字节一致 ｜ 差异条目：%s' % (same, diffent or '无'))
    print('   同住人/备注/合并单元格/绘图 均未触碰')

    # 归集明细 → 独立文件（不写进汇总表工作簿）
    csv_path = os.path.join(core.BASE, '数据', '差旅归集明细_%s.csv' % (a.batch or stem))
    with open(csv_path, 'w', encoding='utf-8-sig') as f:
        f.write('发票号,开票日期,销方,金额,归入行,归入列,依据\n')
        for p in plan:
            for key, items in p['row']['col'].items():
                for iid, amt, why in items:
                    rec = next((x for x in invs if x['id'] == iid), None)
                    f.write('%s,%s,%s,%.2f,第%d行 %s,%s,%s\n' % (
                        iid, (rec or {}).get('开票日期'), ((rec or {}).get('销方名称') or '').replace(',', ' '),
                        amt, p['row']['row'], p['row']['date'].strftime('%m-%d'),
                        COL_NAME.get(COL[key]), why))
    print('   📄 归集明细（独立文件）：%s' % csv_path)
    if outside:
        print('   窗口外的临近票（未归集，请确认是否属本趟）：')
        for rec, dd in sorted(outside, key=lambda x: x[1]):
            print('      %s %s %9.2f ｜ %s' % (rec['id'][-4:], dd, float(rec.get('价税合计') or 0),
                                               (rec.get('来源文件') or '')[:40]))

    if a.no_pack:
        return 0

    # ── 打印包：按制度次序的票据（不含汇总表）──
    _build_pack(plan, cand, stem, out_xlsx, rules, led)
    return 0


def _hotel_companions(plan, led):
    """把「结账单（入住确认）」按入住日期关联到当日住宿发票 → 打印包内紧挨着排"""
    atts = [r for r in led['发票'] if r.get('类型') == '附件']
    pairs = {}          # 发票 id → [结账单记录]
    used = set()
    for p in plan:
        invs_here = [x for key, items in p['row']['col'].items() if key in ('住宿',)
                     for x in items]
        if not invs_here:
            continue
        day = p['row']['date'].isoformat()
        books = [a for a in atts if (CLUES_CACHE.get(a['id']) or {}).get('checkin') == day
                 and a['id'] not in used]
        books.sort(key=lambda a: str(a.get('来源文件')))
        for i, (iid, _amt, _why) in enumerate(invs_here):
            if i < len(books):
                pairs.setdefault(iid, []).append(books[i])
                used.add(books[i]['id'])
    return pairs


def _build_pack(plan, cand, batch, xlsx_path, rules, led=None):
    import fitz
    doc = fitz.open()
    hotel = _hotel_companions(plan, led or {})
    if hotel:
        print('   住宿发票已带上入住确认（结账单）：%d 张' % len(hotel))

    # 发票按制度次序：旅途交通 → 市内交通 → 住宿 → 招待 → 其他费用
    order_idx = {k: i for i, k in enumerate(PACK_ORDER)}
    items = []
    for rec, dd in cand:
        key, _ = classify(rec, rules, CLUES_CACHE.get(rec['id']))
        items.append((order_idx.get(key, 99), dd, rec, key))
    items.sort(key=lambda x: (x[0], x[1]))
    added = att_added = 0
    for _, dd, rec, key in items:
        fp = _find(rec)
        if not fp:
            print('   ⚠️ 原始文件缺失，跳过：%s' % (rec.get('来源文件') or rec['id']))
            continue
        try:
            src = fitz.open(fp)
            doc.insert_pdf(src)
            src.close()
            added += 1
        except Exception as e:
            print('   ⚠️ 无法并入：%s（%s）' % (rec.get('来源文件'), e))
            continue
        # 紧挨着插入当日住宿的入住确认（结账单）
        for comp in hotel.get(rec['id'], []):
            cfp = _find(comp)
            if not cfp:
                continue
            try:
                c = fitz.open(cfp)
                doc.insert_pdf(c)
                c.close()
                att_added += 1
            except Exception:
                pass

    out = os.path.join(OUT_PACK, '%s.pdf' % batch)      # 规则1：按差旅文件名称命名
    doc.save(out, garbage=3, deflate=True)
    n = len(doc)
    doc.close()
    print('✅ 打印包：%s' % out)
    print('   %d 页 = 发票 %d 张 + 入住确认 %d 份（不含汇总表，汇总表见同目录 Excel）'
          % (n, added, att_added))
    print('   次序：旅途交通 → 市内交通 → 住宿(发票+入住确认紧邻) → 招待 → 其他费用')
    return out


def _find(rec):
    name = rec.get('来源文件') or ''
    for base in (core.DONE, core.INBOX):
        p = os.path.join(base, name)
        if os.path.exists(p):
            return p
        hit = glob.glob(os.path.join(base, '*%s*' % os.path.splitext(name)[0][:24]))
        if hit:
            return hit[0]
    return None


def _book_html(plan, batch):
    """把汇总结果渲染成一张 A4 横版表（供打印，样式贴近制度模板）
    注意：CSS 里含 % ，不能用 % 格式化 —— 统一用 __令牌__ 替换。"""
    th = ''.join('<th>%s</th>' % v for v in
                 ['序号', '出差日期', '出差地', '飞机', '火车/高铁', '巴士', '轮船', '其他',
                  '市内交通费', '住宿费', '同住人', '业务招待费', '出差补助', '其他费用', '小计'])
    trs = []
    tot = 0.0
    for i, p in enumerate(plan, 1):
        v = p['vals']
        sub = p['total']
        tot += sub
        trs.append('<tr><td>%d</td><td>%s</td><td>%s</td>%s<td>%.2f</td></tr>' % (
            i, p['row']['date'].strftime('%-m月%-d日'), p['row']['place'],
            ''.join('<td>%s</td>' % (('%.2f' % v[k]) if v.get(k) else '') for k in
                    ['D', 'E', 'F', 'G', 'H', 'I', 'J', '', 'L', 'M', 'N']), sub))
    tpl = '''<!doctype html><html><head><meta charset="utf-8"><style>
@page{size:A4 landscape;margin:10mm}
body{font-family:"PingFang SC","Songti SC",sans-serif;font-size:10px;color:#000}
h1{font-size:16px;text-align:center;margin:0 0 6px}
.meta{display:flex;justify-content:space-between;font-size:11px;margin-bottom:6px}
table{width:100%;border-collapse:collapse}
th,td{border:1px solid #333;padding:3px 4px;text-align:center}
th{background:#eef3fa;font-weight:600}
tr:nth-child(even) td{background:#fafcff}
tfoot td{font-weight:700;background:#f0f4fa}
</style></head><body>
<h1>差旅费报销汇总表</h1>
<div class="meta"><span>部门：诊断原料销售部</span><span>报销人：刘新元</span>
<span>批次：__BATCH__</span><span>出差天数：__DAYS__</span><span>填报日期：__FILL__</span></div>
<table><thead><tr>__TH__</tr></thead><tbody>__TRS__</tbody>
<tfoot><tr><td colspan="3">合计</td><td colspan="11">__TOT__ 元</td></tr></tfoot></table>
</body></html>'''
    return (tpl.replace('__TH__', th).replace('__TRS__', ''.join(trs))
               .replace('__BATCH__', str(batch)).replace('__DAYS__', str(len(plan)))
               .replace('__FILL__', datetime.date.today().strftime('%Y-%m-%d'))
               .replace('__TOT__', '%.2f' % tot))


if __name__ == '__main__':
    sys.exit(main(sys.argv[1:]))