#!/usr/bin/env python3
"""KOCR v4 — Excel 模板填充与合并"""

import os
from datetime import datetime
from collections import defaultdict


def fill_template(voucher_data, template_path, output_path, verbose=False):
    """填充金蝶 K3 导入模板
    
    Args:
        voucher_data: K3VoucherParser.parse() 的输出
        template_path: 模板 .xls 文件路径
        output_path: 输出 .xls 文件路径
        verbose: 是否输出详细信息
    """
    import xlrd
    import xlwt
    
    wb = xlrd.open_workbook(template_path, formatting_info=True)
    schema_sheet = wb.sheet_by_index(0)
    page_sheet = wb.sheet_by_index(1)
    
    out_wb = xlwt.Workbook(encoding='utf-8')
    
    # Sheet 1: t_Schema
    out_ws = out_wb.add_sheet('t_Schema', cell_overwrite_ok=True)
    for r in range(schema_sheet.nrows):
        for c in range(schema_sheet.ncols):
            val = schema_sheet.cell_value(r, c)
            if isinstance(val, float) and val == int(val):
                val = int(val)
            out_ws.write(r, c, val)
    
    # Sheet 2: Page1
    out_page = out_wb.add_sheet('Page1', cell_overwrite_ok=True)
    
    headers = []
    for c in range(page_sheet.ncols):
        val = page_sheet.cell_value(0, c)
        out_page.write(0, c, val)
        headers.append(val)
    
    def col_idx(predicate, default=999):
        for i, h in enumerate(headers):
            if predicate in str(h):
                return i
        return default
    
    # 列索引
    col_date = col_idx('凭证日期')
    col_year = col_idx('会计年度')
    col_period = col_idx('会计期间')
    col_vword = col_idx('凭证字')
    col_vno = col_idx('凭证号')
    col_code = col_idx('科目代码')
    col_name = col_idx('科目名称')
    col_currency = col_idx('币别')
    col_cur_name = col_idx('币别名称')
    col_debit = col_idx('借方')
    col_credit = col_idx('贷方')
    col_desc = col_idx('摘要')
    col_preparer = col_idx('制单')
    col_checker = col_idx('审核')
    col_approve = col_idx('核准')
    col_cashier = col_idx('出纳')
    col_handler = col_idx('经办')
    col_settle_type = col_idx('结算方式')
    col_attachments = col_idx('附件数')
    col_entry_id = col_idx('分录序号')
    col_amount_for = col_idx('原币金额')
    col_biz_date = col_idx('业务日期')
    col_measure_unit = col_idx('数量单位')
    col_unit_price = col_idx('单价')
    col_exchange_rate = col_idx('汇率')
    
    date = voucher_data['date'] or datetime.now()
    
    row = 1
    entry_id = 0
    
    for ei, entry in enumerate(voucher_data['entries']):
        debit = entry.get('debit', 0) or 0
        credit = entry.get('credit', 0) or 0
        amount = max(debit, credit)
        
        out_page.write(row, col_date, f'{date.year}-{date.month}-{date.day}')
        out_page.write(row, col_year, float(voucher_data['year'] or date.year))
        out_page.write(row, col_period, float(voucher_data['period'] or date.month))
        out_page.write(row, col_vword, voucher_data['voucher_word'] or '记')
        out_page.write(row, col_vno, float(voucher_data['voucher_no'] or 0))
        out_page.write(row, col_biz_date, f'{date.year}-{date.month}-{date.day}')
        
        out_page.write(row, col_code, entry['account_code'])
        out_page.write(row, col_name, entry['account_name'])
        out_page.write(row, col_currency, 'RMB')
        out_page.write(row, col_cur_name, '人民币')
        
        out_page.write(row, col_amount_for, float(amount))
        out_page.write(row, col_debit, float(debit))
        out_page.write(row, col_credit, float(credit))
        
        out_page.write(row, col_desc, entry.get('description', ''))
        out_page.write(row, col_preparer, voucher_data['preparer'] or 'Manager')
        out_page.write(row, col_checker, 'NONE')
        out_page.write(row, col_approve, 'NONE')
        out_page.write(row, col_cashier, 'NONE')
        out_page.write(row, col_handler, voucher_data['handler'] or '')
        out_page.write(row, col_settle_type, '*')
        out_page.write(row, col_attachments, float(0))
        out_page.write(row, col_measure_unit, '*')
        out_page.write(row, col_unit_price, float(0))
        out_page.write(row, col_entry_id, float(entry_id))
        out_page.write(row, col_exchange_rate, float(1))
        
        row += 1
        entry_id += 1
    
    os.makedirs(os.path.dirname(output_path), exist_ok=True)
    out_wb.save(output_path)
    
    if verbose:
        total_entries = len(voucher_data['entries'])
        print(f"\n✅ Excel 输出: {output_path}")
        print(f"   凭证: {voucher_data['voucher_word']}-{voucher_data['voucher_no']}")
        print(f"   日期: {date.strftime('%Y-%m-%d')}")
        print(f"   分录: {total_entries} 行")
        print(f"   合计: 借 {sum(e.get('debit',0) or 0 for e in voucher_data['entries']):.2f} 贷 {sum(e.get('credit',0) or 0 for e in voucher_data['entries']):.2f}")


def merge_output(results, template_path, output_dir, review=False):
    """合并多张凭证为一个排序好的 Excel
    
    Args:
        results: [(fname, voucher_data, output_path, quality), ...]
        template_path: 模板路径
        output_dir: 输出目录
        review: 是否生成校验报告
    
    Returns:
        merge_path: 合并后的 Excel 路径
    """
    import xlrd
    import xlwt
    
    # 按 (日期, 凭证号) 分组
    groups = defaultdict(list)
    for item in results:
        v = item[1]
        key = (
            v.get('date').strftime('%Y-%m-%d') if v.get('date') else '0000',
            v.get('voucher_no') or 0
        )
        groups[key].append(item)
    
    sorted_keys = sorted(groups.keys())
    src_wb = xlrd.open_workbook(template_path, formatting_info=True)
    schema_sheet = src_wb.sheet_by_index(0)
    
    all_entries = []
    merged_report = []
    
    for key in sorted_keys:
        group = groups[key]
        
        if len(group) == 1:
            # 单页
            fname, voucher, out_path, quality = group[0]
            wb = xlrd.open_workbook(out_path)
            ws = wb.sheet_by_name('Page1')
            for r in range(1, ws.nrows):
                row_data = [ws.cell_value(r, c) for c in range(ws.ncols)]
                all_entries.append(row_data)
            merged_report.append(([fname], fname, voucher, None))
        else:
            # 多页合并
            page_names = [g[0] for g in group]
            voucher = group[0][1]
            
            total_dr = 0
            total_cr = 0
            pages_entries = []
            
            for fname, v, out_path, quality in group:
                wb = xlrd.open_workbook(out_path)
                ws = wb.sheet_by_name('Page1')
                page_rows = []
                for r in range(1, ws.nrows):
                    row_data = [ws.cell_value(r, c) for c in range(ws.ncols)]
                    page_rows.append(row_data)
                    dr_val = float(row_data[10] or 0) if isinstance(row_data[10], (int, float)) else 0.0
                    cr_val = float(row_data[11] or 0) if isinstance(row_data[11], (int, float)) else 0.0
                    total_dr += dr_val
                    total_cr += cr_val
                pages_entries.append((fname, page_rows))
            
            merged_ok = abs(total_dr - total_cr) < 0.01
            
            for fname, page_rows in pages_entries:
                for row_data in page_rows:
                    all_entries.append(row_data)
            
            pages_str = ' + '.join(page_names)
            merged_report.append((page_names, pages_str, voucher, merged_ok))
    
    # 写入合并文件
    
    # ---- 科目名称归一化：同一科目代码取最高频名称 ---- #
    from collections import Counter
    name_counter = Counter()
    for row_data in all_entries:
        code = str(row_data[5]) if len(row_data) > 5 else ''
        name = str(row_data[6]) if len(row_data) > 6 else ''
        if name and len(name) >= 2:
            name_counter[(code, name)] += 1
    code_best = {}
    for (code, name), cnt in name_counter.items():
        if code not in code_best or cnt > code_best[code][1]:
            code_best[code] = (name, cnt)
    fixed_count = 0
    for row_data in all_entries:
        code = str(row_data[5]) if len(row_data) > 5 else ''
        if code in code_best:
            best_name = code_best[code][0]
            if len(row_data) > 6 and str(row_data[6]) != best_name:
                row_data[6] = best_name
                fixed_count += 1
    if fixed_count > 0:
        print(f'   🔄 科目名称归一化：修正 {fixed_count} 处不一致')
    
    # ---- 标准映射覆盖：用户提供的正确对照表 ---- #
    STANDARD_NAMES = {
        '1002.01.01': '淇县支行1710020809200182975',
        '1002.02.01': '漓江支行4105016228400000750',
        '1012.01': 'POS刷卡未到账',
        '2203.01': '物业管理费',
        '2241.01.01': '装修押金',
        '6051.02': '办证工本收入',
        '6051.08': '转售水电费收入',
        '6401.02.02': '办公费',
        '6401.51.09': '日常维修费',
    }
    override_count = 0
    for row_data in all_entries:
        code = str(row_data[5]) if len(row_data) > 5 else ''
        if code in STANDARD_NAMES and len(row_data) > 6:
            correct_name = STANDARD_NAMES[code]
            if str(row_data[6]) != correct_name:
                row_data[6] = correct_name
                override_count += 1
    if override_count > 0:
        print(f'   📋 标准映射覆盖：修正 {override_count} 处')
    
    out_wb = xlwt.Workbook(encoding='utf-8')
    out_ws = out_wb.add_sheet('t_Schema', cell_overwrite_ok=True)
    for r in range(schema_sheet.nrows):
        for c in range(schema_sheet.ncols):
            val = schema_sheet.cell_value(r, c)
            if isinstance(val, float) and val == int(val):
                val = int(val)
            out_ws.write(r, c, val)
    
    out_page = out_wb.add_sheet('Page1', cell_overwrite_ok=True)
    for c in range(len(all_entries[0]) if all_entries else 0):
        out_page.write(0, c, src_wb.sheet_by_index(1).cell_value(0, c))
    for r, row_data in enumerate(all_entries):
        for c, val in enumerate(row_data):
            out_page.write(r + 1, c, val)
    
    merge_path = os.path.join(output_dir, '合并凭证_按顺序.xls')
    out_wb.save(merge_path)
    
    return merge_path


def merge_voucher_data(results):
    """按(日期,凭证号)合并凭证数据，返回合并后的凭证列表
    
    多页凭证（同一凭证号的多张照片）合并 entries，单页不变。
    合并后的数据用于统一的借贷平衡 + 大小写金额校验。
    
    Args:
        results: [(fname, voucher_data, out_path, quality), ...]
    
    Returns:
        [voucher_dict, ...]  每个 voucher_dict 包含合并后的 entries
    """
    from collections import defaultdict
    
    groups = defaultdict(list)
    for item in results:
        v = item[1]
        key = (
            v.get('date').strftime('%Y-%m-%d') if v.get('date') else '0000',
            v.get('voucher_no') or 0
        )
        groups[key].append(item)
    
    merged_list = []
    for key in sorted(groups.keys()):
        group = groups[key]
        page_names = [g[0] for g in group]
        base = group[0][1]  # 第一页的元数据（日期、凭证号等）
        
        # 合并所有页的 entries
        all_entries = []
        for fname, v, out_path, quality in group:
            all_entries.extend(v.get('entries', []))
        
        # 重新编号
        for i, e in enumerate(all_entries):
            e['id'] = i + 1
        
        merged_list.append({
            'date': base.get('date'),
            'voucher_word': base.get('voucher_word', '记'),
            'voucher_no': base.get('voucher_no'),
            'entries': all_entries,
            'total_str': base.get('total_str', ''),
            'total_amount': base.get('total_amount', 0),
            'pages': page_names,
            'is_multi_page': len(group) > 1,
            'page_count': len(group),
        })
    
    return merged_list