fill_template.py

import json
import re
import shutil
import sys
import unicodedata
import zipfile
from datetime import datetime, date
from pathlib import Path
from xml.sax.saxutils import escape as xml_escape

DEFAULT_TEMPLATE = "/scripts/EXPEDIENTE_FINANCIERO_FINAL.xlsx"

SHEET_PORTADA = "xl/worksheets/sheet1.xml"   # 01_Portada
SHEET_EC      = "xl/worksheets/sheet3.xml"   # 03_EstadosCuenta
SHEET_RAW     = "xl/worksheets/sheet8.xml"   # 08_EC_Raw
TABLE_EC      = "xl/tables/table1.xml"       # Tabla_EC

MESES_ES = ['ene', 'feb', 'mar', 'abr', 'may', 'jun',
            'jul', 'ago', 'sep', 'oct', 'nov', 'dic']

# Comisiones por SPEI / transferencia. NO se descartan: solo se MARCAN (bandera
# en la columna N de 08_EC_Raw) para la comparativa de # de movimientos.
# OJO: 'CGO' y 'CARGO' NO son marcas de comision, son la palabra que muchos bancos
# usan para el movimiento mismo. Con ellas dentro, HSBC marcaba 107 de 208
# movimientos como 'comision por SPEI' (son transferencias reales: 'CGO SPEI FACT
# 2440' de $174,000, 'CGO SPEI FACT DTEFACT1466' de $1,832,928) y SANTANDER 12 mas
# ('CARGO TRANSFERENCIA ENLACE' de $150,000). Una comision real dice COMISION/COM/
# CUOTA de forma explicita. La bandera es informativa (columna N), no mueve dinero.
COMISION_RE = re.compile(
    r'(COMIS\w*|\bCOM\b\.?|CUOTA)[\s\S]{0,50}(SPEI|TRANSFER\w*|\bTRANSF\b|\bTEF\b)'
    r'|(SPEI|TRANSFER\w*|\bTRANSF\b|\bTEF\b)[\s\S]{0,50}(COMIS\w*|\bCOM\b\.?|CUOTA)',
    re.IGNORECASE)

# Filas de saldos en 03_EstadosCuenta (V3.8.3: se insertaron 4 renglones en la
# zona de conteos, por eso bajaron respecto a V3.8.2 que las tenia en 19 y 28).
ROW_SALDO_INI = 23
ROW_SALDO_FIN = 32
ROW_SALDO_PROM = 33   # SALDO PROMEDIO (J33 ya es =AVERAGE(D33:I33) en la plantilla)

# Seccion "VERIFICACION CONTRA TOTALES DEL BANCO" (V3.8.5, fila base 196)
ROW_VERIF = 196       # B: titulo; B+1: banco(s); B+3 Depositos; B+4 Retiros;
                      # B+5 saldo ini; B+6 saldo fin. Reportado en col D.

# Columna de la bandera de comision en 08_EC_Raw (fuera de Tabla_EC, que es A:M)
COL_COMISION = 'N'

# Capacidad del desglose por persona: el array dinamico B33 (y su gemelo de
# egresos) se derrama hacia abajo hasta toparse con la fila TOTAL. En la
# plantilla V3.8.1 hay 74 filas libres (33..106). Si hay mas ordenantes que eso
# Excel marca #DESBORDAMIENTO!, asi que se limita el numero de nombres DISTINTOS:
# se conservan los mayores por monto y el resto se agrupa en "OTROS".
DESGLOSE_CAPACIDAD = 74

EPOCH = date(1899, 12, 30)


# ---------------------------------------------------------------- utilidades

def parse_fecha(val):
    if not val:
        return None
    s = str(val).strip()[:10]
    try:
        return datetime.strptime(s, '%Y-%m-%d').date()
    except Exception:
        return None


def parse_num(val):
    if val is None or val == '':
        return 0.0
    if isinstance(val, (int, float)):
        return float(val)
    s = str(val).replace('$', '').replace(',', '').strip()
    neg = s.startswith('(') and s.endswith(')')
    s = s.strip('()')
    try:
        n = float(s)
    except (TypeError, ValueError):
        return 0.0
    return -n if neg else n


def serial(d):
    """Fecha -> numero de serie Excel."""
    return (d - EPOCH).days


def num_str(x):
    """Numero -> string sin notacion cientifica, max 2 decimales utiles."""
    r = round(float(x), 2)
    if r == int(r):
        return str(int(r))
    return ('%.2f' % r)


def col_idx(letra):
    n = 0
    for ch in letra:
        n = n * 26 + (ord(ch) - 64)
    return n


def quitar_acentos(s):
    return ''.join(c for c in unicodedata.normalize('NFD', s)
                   if unicodedata.category(c) != 'Mn')


# ------------------------------------------------- edicion XML de worksheets

def desharear_formulas(xml):
    """Convierte TODAS las formulas compartidas (t="shared") en explicitas.

    Excel guarda grupos de formulas donde solo la celda "maestra" tiene el
    texto y las demas son referencias (si="N"). Si la cirugia sobreescribe
    una celda del grupo, el rango compartido queda inconsistente y algunas
    versiones de Excel "reparan" el archivo borrando formulas en cascada.
    Expandirlas todas elimina el riesgo por completo.
    """
    if '<f t="shared"' not in xml:
        return xml
    try:
        from openpyxl.formula.translate import Translator
    except ImportError:
        raise RuntimeError('openpyxl es requerido para expandir formulas compartidas')
    from xml.sax.saxutils import unescape as xml_unescape

    # 1) recolectar maestras (tienen ref= y cuerpo): si -> (celda_origen, formula_cruda)
    #    el cuerpo de <f> nunca contiene '<' crudo (va escapado), por eso [^<]*
    masters = {}
    for m in re.finditer(
            r'<c r="([A-Z]+\d+)"[^>]*><f t="shared"(?=[^>]*ref=")[^>]*?si="(\d+)"[^>]*>([^<]*)</f>',
            xml):
        masters[m.group(2)] = (m.group(1), xml_unescape(m.group(3)))

    # 2) reescribir cada celda con f compartida
    def reescribir(cm):
        celda = cm.group(0)
        ref = cm.group(1)
        fm = re.search(r'<f t="shared"[^>]*?si="(\d+)"[^>]*?(?:/>|>([^<]*)</f>)', celda)
        if not fm:
            return celda
        si = fm.group(1)
        cuerpo = fm.group(2)
        if cuerpo:                                  # maestra: quitar atributos shared
            nueva_f = '<f>%s</f>' % cuerpo
        else:                                       # miembro: traducir desde la maestra
            if si not in masters:
                return celda                        # huerfana: no inventar nada
            origen, formula = masters[si]
            traducida = Translator('=' + formula, origin=origen).translate_formula(ref)
            nueva_f = '<f>%s</f>' % xml_escape(traducida[1:])
        return celda[:fm.start()] + nueva_f + celda[fm.end():]

    return re.sub(r'<c r="([A-Z]+\d+)"[^>]*?(?:/>|>.*?</c>)', reescribir, xml, flags=re.S)


def build_cell(ref, s=None, num=None, formula=None, text=None):
    sa = ' s="%s"' % s if s else ''
    if text is not None:
        return ('<c r="%s"%s t="inlineStr"><is><t xml:space="preserve">%s</t></is></c>'
                % (ref, sa, xml_escape(str(text))))
    if formula is not None:
        return '<c r="%s"%s><f>%s</f></c>' % (ref, sa, xml_escape(formula))
    if num is not None:
        return '<c r="%s"%s><v>%s</v></c>' % (ref, sa, num if isinstance(num, str) else num_str(num))
    return '<c r="%s"%s/>' % (ref, sa)


def get_style(xml, ref):
    m = re.search(r'<c r="%s"[^>]*?s="(\d+)"' % ref, xml)
    return m.group(1) if m else None


def set_row_cells(xml, row, updates):
    """updates: dict {'D': cell_xml_completo, ...}. La fila DEBE existir."""
    pat = re.compile(r'<row([^>]*\br="%d"[^>]*?)(/>|>(.*?)</row>)' % row, re.S)
    m = pat.search(xml)
    if not m:
        raise ValueError('Fila %d no existe en la hoja' % row)
    attrs = m.group(1)
    body = m.group(3) or ''
    # celdas existentes -> dict por letra de columna
    cells = {}
    for cm in re.finditer(r'<c r="([A-Z]+)%d"[^>]*?(?:/>|>.*?</c>)' % row, body, re.S):
        cells[cm.group(1)] = cm.group(0)
    cells.update(updates)
    nuevo_body = ''.join(cells[k] for k in sorted(cells.keys(), key=col_idx))
    nuevo = '<row%s>%s</row>' % (attrs, nuevo_body)
    return xml[:m.start()] + nuevo + xml[m.end():]


def replace_sheet_data_rows(xml, filas_xml, keep_row1=True):
    """Reemplaza todo el contenido de <sheetData> dejando la fila 1 intacta."""
    m = re.search(r'<sheetData>(.*)</sheetData>', xml, re.S)
    if not m:
        raise ValueError('sheetData no encontrado')
    row1 = ''
    if keep_row1:
        r1 = re.search(r'<row[^>]*\br="1"[^>]*(?:/>|>.*?</row>)', m.group(1), re.S)
        row1 = r1.group(0) if r1 else ''
    nuevo = '<sheetData>%s%s</sheetData>' % (row1, ''.join(filas_xml))
    return xml[:m.start()] + nuevo + xml[m.end():]


# --------------------------------------- extraccion de nombre del ordenante

BANCOS_TOKENS = [
    'CITIBANAMEX', 'BANAMEX', 'BBVA', 'BANCOMER', 'SANTANDER', 'BANORTE',
    'SCOTIABANK', 'BANREGIO', 'AFIRME', 'INBURSA', 'MULTIVA', 'BANBAJIO',
    'BAJIO', 'AZTECA', 'BANCOPPEL', 'MIFEL', 'MONEX', 'ACTINVER', 'INTERCAM',
    'COMPARTAMOS', 'BANSI', 'HSBC', 'ALBO', 'KLAR', 'NVIO', 'MERCADO PAGO',
    'MERCADOPAGO', 'STP',
]

STOPWORDS = {
    'SPEI', 'RECIBIDO', 'RECIBIDOS', 'ENVIADO', 'ENVIADOS', 'TRANSFERENCIA',
    'TRANSFERENCIAS', 'TRANSF', 'TEF', 'INTERBANCARIA', 'INTERBANCARIO',
    'DEPOSITO', 'DEPOSITOS', 'ABONO', 'ABONOS', 'PAGO', 'PAGOS', 'CUENTA',
    'TERCERO', 'TERCEROS', 'REF', 'REFERENCIA', 'CLAVE', 'RASTREO', 'CVE',
    'FOLIO', 'CONCEPTO', 'ORDENANTE', 'BENEFICIARIO', 'BANCO', 'BCO', 'CTA',
    'CLABE', 'RFC', 'HORA', 'HR', 'BNET', 'BMOV', 'CEP', 'NULL', 'EFECTIVO',
    'PRACTIC', 'PRACTICAJA', 'CAJERO', 'SUCURSAL', 'VENTANILLA', 'TRASPASO',
    'MISMO', 'TITULAR', 'NOMINA', 'DISPERSION', 'WEB', 'BANCA', 'MOVIL',
    'APP', 'LINEA', 'POR', 'PARA', 'CON', 'SIN', 'DESDE',
    # ruido del disclaimer SPEI / Santander ("...DATO NO VERIFICADO POR ESTA
    # INSTITUCION...") y palabras de relleno que no son nombre
    'DATO', 'DATOS', 'VERIFICADO', 'VERIFICADA', 'INSTITUCION', 'ESTA', 'ESTE',
    'AUT', 'AUTORIZACION', 'PUNTO', 'VENTA', 'TERMINAL', 'AFIL', 'COBRO',
    'CARGO', 'CARGOS', 'IVA', 'COMISION', 'COMISIONES', 'MEMBRESIA',
    # disclaimer GAT de BBVA al pie ("...RENDIMIENTO QUE SE OBTENDRIA DESPUES DE
    # DESCONTAR LA INFLACION ESTIMADA... en adelante") -> nunca es nombre
    'OBTENDRIA', 'OBTENDRA', 'INFLACION', 'RENDIMIENTO', 'ESTIMADO', 'ESTIMADA',
    'ADELANTE', 'NOMINAL', 'DESCONTAR', 'IMPUESTOS', 'ANTES', 'DESPUES', 'GAT',
    'FACT', 'PRAC', 'FOLIO', 'CIUDAD', 'TARJETA', 'CREDITO', 'DEBITO',
    'AUTOMATICO', 'RECIBO', 'PREST', 'PRESTAMO', 'DISPOSICION',
}

PERMITIDAS_CORTAS = {'Y', 'DE', 'LA', 'EL', 'SA', 'CV', 'RL', 'SC', 'AC', 'S'}

# --- Movimientos SIN contraparte: se resumen por su NATURALEZA, no por nombre ---
# El usuario pide que todo lo que aluda a efectivo diga 'EFECTIVO' (antes caia en
# 'SIN IDENTIFICAR' porque EFECTIVO/DEPOSITO/CAJERO estan en STOPWORDS: en
# SANTANDER eran 63 movimientos, entre ellos 'DEPOSITO EN EFECTIVO' x36).
EFECTIVO_RE = re.compile(
    r'RETIRO\s+SIN\s+TARJETA|SIN\s+TARJETA|EN\s+EFECTIVO|DE\s+EFECTIVO|'
    r'\bEFECTIVO\b|DISPOSICION\s+(?:DE\s+)?EFECTIVO|RETIRO\s+CAJERO|'
    r'\bCAJERO\b|\bATM\b|\bPRACTICAJA\b|PRACTIC\b|\bVENTANILLA\b|'
    r'RETIRO\s+EN\s+SUCURSAL|DEP[O0]SITO\s+SUCURSAL', re.I)
# Otros movimientos que tampoco tienen contraparte (son del propio banco/SAT).
# El orden importa: se evalua de arriba hacia abajo.
NATURALEZA = [
    ('EFECTIVO',            EFECTIVO_RE),
    ('ISR',                 re.compile(r'\bI\s*S\s*R\b|IMPUESTO\s+SOBRE\s+LA\s+RENTA|RETENCION\s+ISR', re.I)),
    ('IVA',                 re.compile(r'\bI\s*V\s*A\b|\bVV\s+AA\b', re.I)),
    ('COMISIONES',          re.compile(r'COMISION|MEMBRESIA|ANUALIDAD|MANEJO\s+DE\s+CUENTA', re.I)),
    ('INTERESES',           re.compile(r'\bINTERES(?:ES)?\b|RENDIMIENTO(?:S)?\s+PAGADO', re.I)),
    ('TRASPASO PROPIO',     re.compile(r'MISMO\s+TITULAR|TRASPASO\s+ENTRE\s+CUENTAS|CUENTA\s+PROPIA', re.I)),
]
# OJO: aqui NO van 'PAGO DE SERVICIOS' ni 'DOMICILIACION'. Esos SI tienen
# contraparte (CFE, TELMEX, la aseguradora...) y el usuario quiere saber DE QUIEN
# fue el movimiento: colapsarlos en una etiqueta generica escondia 128 y 63
# movimientos de SANTANDER bajo un mismo nombre.


def _naturaleza(raw):
    """Etiqueta corta para movimientos sin contraparte. '' si no aplica."""
    for etiqueta, rx in NATURALEZA:
        if rx.search(raw):
            return etiqueta
    return ''


def _ultimo_recurso(raw):
    """Etiqueta para movimientos donde el banco NO da contraparte. Se aplica solo
    DESPUES de que fallaron todas las reglas de nombre, para que un nombre real
    siempre gane. Casos medidos en BBVA (60 de 686 caian en 'SIN IDENTIFICAR')."""
    # Pago de impuestos: el SAT es la contraparte, pero 'S.A.T.' se pierde al
    # quitar la puntuacion (queda 'S A T', tres tokens de 1 letra).
    if re.search(r'\bS\.A\.T\.?|\bSAT\b|SERVICIO\s+DE\s+ADMINISTRACION\s+TRIBUTARIA', raw):
        return 'SAT'
    # Pago a la tarjeta de credito del propio cliente: no hay tercero. (22 casos)
    if re.search(r'PAGO\s+TARJETA\s+DE\s+CREDITO|PAGO\s+A\s+TARJETA', raw):
        return 'TARJETA DE CREDITO'
    # Cobro automatico de un prestamo: el numero de recibo identifica el credito,
    # asi que se conserva (se repite mes con mes y agrupa solo).
    m = re.search(r'RECIBO\s+PREST\.?\s*(\d{6,})', raw)
    if m:
        return 'PRESTAMO ' + m.group(1)
    m = re.search(r'\bPREST\w*\s+(\d{6,})', raw)
    if m:
        return 'PRESTAMO ' + m.group(1)
    # BBVA 'PAGO CUENTA DE TERCERO BNET <ref> pago': el ordenante no escribio
    # nombre. La referencia BNET es del movimiento, no del tercero, asi que usarla
    # crearia un nombre distinto por operacion; se agrupan en una sola etiqueta.
    if re.search(r'CUENTA\s+DE\s+TERCERO', raw):
        return 'TERCERO SIN NOMBRE'
    # Si al menos se sabe el banco contraparte, decirlo es mejor que no decir nada.
    for b in BANCOS_TOKENS:
        if b in raw:
            return b
    return ''


def _resumen_crudo(raw):
    """Ultimo recurso: resume el concepto en algo LEGIBLE en vez de devolver
    'SIN IDENTIFICAR'. Quita bancos, digitos y puntuacion, tira las stopwords y
    se queda con las palabras con sustancia. Solo devuelve '' si de verdad no
    quedo nada (concepto vacio o puros numeros)."""
    s = raw
    for b in BANCOS_TOKENS:
        s = s.replace(b, ' ')
    s = re.sub(r'[^A-ZÑ&\s]', ' ', s)
    toks, vistos = [], set()
    for t in s.split():
        if len(t) < 3 or t in STOPWORDS or t in vistos:
            continue
        vistos.add(t)
        toks.append(t)
    return ' '.join(toks[:6])[:60].strip()


def _con_sustancia(r):
    return any(len(t) >= 3 and t not in PERMITIDAS_CORTAS for t in r)


def _primer_nombre(text):
    """Toma la PRIMERA corrida de palabras-nombre del texto (se detiene en el
    primer token invalido: stopword, conector suelto o token con digito). Sirve
    para 'DEL CLIENTE <nombre> CLAVE...' o 'BNET 123 <nombre> ...'."""
    s = quitar_acentos(str(text or '').upper())
    for b in BANCOS_TOKENS:
        s = s.replace(b, ' ')
    s = re.sub(r'[^A-ZÑ&\s]', ' ', s)          # quita digitos y puntuacion
    run = []
    for tok in s.split():
        valido = (len(tok) >= 3 and tok not in STOPWORDS) or tok in PERMITIDAS_CORTAS
        if valido:
            run.append(tok)
        elif run:
            break
    while run and run[0] in PERMITIDAS_CORTAS:
        run.pop(0)
    while run and run[-1] in PERMITIDAS_CORTAS:
        run.pop()
    if run and _con_sustancia(run):
        return ' '.join(run[:6])[:60].strip()
    return ''


def extraer_nombre(concepto, tipo=''):
    """Nombre de la persona/empresa contraparte del movimiento.

    Orden de prioridad:
      1) Patron explicito de contraparte SPEI (Santander/Banamex):
         'DEL CLIENTE X' (deposito), 'AL CLIENTE X' / 'A FAVOR DE X' (retiro).
      2) BBVA: texto libre del ordenante tras 'BNET <digitos>'.
      3) Heuristica: ultima corrida de palabras-nombre del concepto.
    """
    raw = quitar_acentos(str(concepto or '').upper())

    # 0) Movimientos SIN contraparte (efectivo, comisiones, IVA/ISR, intereses,
    #    servicios): se resumen por su NATURALEZA. Va PRIMERO porque un
    #    'RETIRO SIN TARJETA' o un 'DEPOSITO EN EFECTIVO' no tienen de quien
    #    sacar un nombre y antes terminaban en 'SIN IDENTIFICAR'.
    nat = _naturaleza(raw)
    if nat:
        return nat

    # 1) contraparte explicita por NOMBRE (Santander/Banorte SPEI: 'DEL CLIENTE X',
    #    'AL CLIENTE X', 'A FAVOR DE X').
    m = re.search(r'\b(?:DEL CLIENTE|AL CLIENTE|A FAVOR DE)\s+(.*)$', raw)
    if m:
        cand = _primer_nombre(m.group(1))
        if cand:
            return cand

    # 2) Banorte orden de pago SPEI: 'BENEF:<nombre> (DATO NO VERIF...)'.
    m = re.search(r'\bBENEF:?\s*(.+?)(?:\(DATO|CVE\s+RAST|\bRFC\b|$)', raw)
    if m:
        cand = _primer_nombre(m.group(1))
        if cand:
            return cand

    # 3) Banorte traspaso a/desde tercero: el banco identifica al tercero por su
    #    NUMERO DE CUENTA ('A LA CUENTA: <cuenta>' / 'DE LA CUENTA: <cuenta>'); se
    #    agrupa por esa cuenta (NO por el folio generico 'TERCEROS 0000000001').
    m = re.search(r'\b(?:A LA CUENTA|DE LA CUENTA)\s*:?\s*(\d{6,})', raw)
    if m:
        return 'CUENTA ' + m.group(1)

    # 4) BBVA 'CUENTA DE TERCERO <cuenta> <texto>': nombre si lo trae (2+ palabras),
    #    si solo es un concepto ('pago de mercancia') se agrupa por el num. de cuenta.
    m_acc = re.search(r'\bTERCERO\s+(\d{6,})', raw)
    if m_acc:
        m2 = re.search(r'\bTERCERO\s+\d{6,}\s+(.*?)(?:\s+REF\b|$)', raw)
        cand = _primer_nombre(m2.group(1)) if m2 else ''
        if cand and len(cand.split()) >= 2:
            return cand
        return 'CUENTA ' + m_acc.group(1)

    # 5) BBVA 'BNET <ref> <texto libre>' (lo que escribio quien ordena/recibe)
    m = re.search(r'\bBNET\s+\d+\s+(.*)$', raw)
    if m:
        cand = _primer_nombre(m.group(1))
        if cand:
            return cand

    # 6) SPEI generico: el banco imprime primero las REFERENCIAS (clave de rastreo,
    #    CLABE, folio: bloques largos de digitos) y AL FINAL el nombre de la
    #    contraparte. Se toma el texto que sigue al ULTIMO bloque de 10+ digitos.
    #    Ej: 'SPEI RECIBIDOMULTIVA BANC 0000121PAGO HONORARIOS LOMAS
    #    00132180000009445645 26021595130297 ARUAL MEDICINA DE REANIMACION'
    #    -> 'ARUAL MEDICINA DE REANIMACION'.
    if 'SPEI' in raw or 'TRANSFERENCIA' in raw:
        cola = re.split(r'\d{10,}', raw)[-1]
        cand = _primer_nombre(cola)
        if cand and len(cand.split()) >= 2:
            return cand

    # 3) heuristica general: ultima corrida con 2+ palabras (si no, la mas larga)
    s = raw
    for b in BANCOS_TOKENS:
        s = s.replace(b, ' ')
    s = re.sub(r'[^A-Z0-9Ñ&\s]', ' ', s)
    tokens = [t for t in s.split() if not any(ch.isdigit() for ch in t)]
    runs, run = [], []
    for tok in tokens:
        valido = (len(tok) >= 3 and tok not in STOPWORDS) or tok in PERMITIDAS_CORTAS
        if valido:
            run.append(tok)
        else:
            if run:
                runs.append(run)
            run = []
    if run:
        runs.append(run)
    runs = [r for r in runs if _con_sustancia(r)]
    if not runs:
        # Antes se devolvia 'SIN IDENTIFICAR' aunque el concepto TUVIERA texto util
        # (el Sheet lo mostraba bien y la maqueta no). Ahora solo se rinde si de
        # verdad no quedo nada legible.
        return _resumen_crudo(raw) or _ultimo_recurso(raw) or 'SIN IDENTIFICAR'
    candidata = None
    for r in reversed(runs):
        if len(r) >= 2:
            candidata = r
            break
    if candidata is None:
        candidata = max(runs, key=len)
    candidata = candidata[-6:]
    return (' '.join(candidata)[:60].strip()
            or _resumen_crudo(raw) or _ultimo_recurso(raw) or 'SIN IDENTIFICAR')


# ---------------------------------------------------------------- principal

def fill(template_path, output_path, data):
    cliente = data.get('cliente', 'CLIENTE_SIN_NOMBRE')
    cliente_raw = data.get('cliente_raw', cliente)
    rfc = str(data.get('rfc', '') or '').strip()
    transacciones = data.get('transacciones', [])
    saldos_in = data.get('saldos', []) or []
    if not isinstance(transacciones, list):
        raise ValueError("'transacciones' debe ser una lista")

    src = Path(template_path)
    if not src.exists():
        raise FileNotFoundError('Template no existe: %s' % template_path)
    shutil.copy(str(src), output_path)

    # ===== 1) Normalizar transacciones (SIN FILTRAR: se conservan todas) =====
    # Cada movimiento del estado de cuenta se conserva tal cual. Solo se MARCA
    # si es una comision por SPEI/transferencia (bandera 'es_comision') para la
    # comparativa de # de movimientos; la marca NO elimina nada.
    txs, comisiones = [], 0
    for t in transacciones:
        f = parse_fecha(t.get('Fecha'))
        if not f:
            continue
        dep = parse_num(t.get('Deposito'))
        ret = parse_num(t.get('Retiro'))
        movimiento = max(abs(dep), abs(ret))
        concepto = str(t.get('Concepto', '') or '').strip()
        es_com = bool(COMISION_RE.search(concepto))
        if es_com:
            comisiones += 1
        txs.append({
            'fecha': f,
            'concepto': concepto,
            'tipo': str(t.get('Tipo', '') or '').strip(),
            'banco': str(t.get('Banco', '') or 'N/A').strip(),
            'categoria': str(t.get('Categoria', '') or '').strip(),
            'deposito': round(abs(dep), 2),
            'retiro': round(abs(ret), 2),
            'monto': round(parse_num(t.get('Monto')) or (dep - ret) or movimiento, 2),
            'moneda': str(t.get('Moneda', '') or 'MXN').strip().upper() or 'MXN',
            'monto_original': round(parse_num(t.get('MontoOriginal')) or movimiento, 2),
            'es_comision': es_com,
        })
    txs.sort(key=lambda x: x['fecha'])

    # nombre del ordenante/beneficiario limpio, una sola vez por transaccion
    for t in txs:
        t['nombre'] = extraer_nombre(t['concepto'])

    # ===== Mapas de nombres con TOPE de capacidad (evita #DESBORDAMIENTO!) =====
    # Se construye un mapa nombre->nombre_a_mostrar para depositos y otro para
    # retiros: si hay mas nombres distintos que la capacidad del desglose, se
    # conservan los mayores por monto y el resto cae en una bolsa "OTROS".
    def _mapa_nombres(clave_monto, etiqueta_otros):
        tot = {}
        for t in txs:
            if t[clave_monto] > 0:
                tot[t['nombre']] = tot.get(t['nombre'], 0.0) + t[clave_monto]

        # --- Fusion de variantes del MISMO nombre -------------------------------
        # El PDF corta la razon social en distinto punto segun cuanto texto la
        # precede, asi que el mismo cliente aparece como 'EXPORTACIONES TEXTIL' y
        # 'EXPORTACIONES TEXTILES MEXICANAS'. Se fusionan solo cuando la clave
        # canonica de uno es PREFIJO de la del otro y mide >= 12 caracteres: es
        # conservador a proposito (fusionar de mas atribuiria dinero a la
        # contraparte equivocada, que es peor que dejar dos filas separadas).
        canon = {n: re.sub(r'[^A-Z0-9]', '', quitar_acentos(n).upper()) for n in tot}
        rep = {}
        for n in sorted(tot, key=lambda x: (-len(canon[x]), x)):     # del mas largo al mas corto
            cn = canon[n]
            destino = n
            if len(cn) >= 12:
                for m in rep:
                    if canon[m].startswith(cn) or cn.startswith(canon[m]):
                        destino = rep[m]
                        break
            rep[n] = destino
        fusionado = {}
        for n, dest in rep.items():
            fusionado[dest] = fusionado.get(dest, 0.0) + tot[n]

        if len(fusionado) <= DESGLOSE_CAPACIDAD:
            return {n: rep[n] for n in tot}
        principales = set(sorted(fusionado, key=lambda n: -fusionado[n])[:DESGLOSE_CAPACIDAD - 1])
        return {n: (rep[n] if rep[n] in principales else etiqueta_otros) for n in tot}

    mapa_dep = _mapa_nombres('deposito', 'OTROS DEPOSITANTES')
    mapa_ret = _mapa_nombres('retiro', 'OTROS BENEFICIARIOS')

    # ===== 2) Saldos por (banco, mes): el ULTIMO registro gana =====
    saldos = {}
    for srow in saldos_in:
        mes = str(srow.get('mes', '') or '').strip()
        if not re.match(r'^\d{4}-\d{2}$', mes):
            continue
        banco = str(srow.get('banco', '') or 'N/A').strip()
        si = srow.get('saldo_inicial')
        sf = srow.get('saldo_final')
        sp = srow.get('saldo_promedio')
        dr = srow.get('dep_reportado')
        rr = srow.get('ret_reportado')
        si = None if si in (None, '') else parse_num(si)
        sf = None if sf in (None, '') else parse_num(sf)
        sp = None if sp in (None, '') else parse_num(sp)
        dr = None if dr in (None, '') else parse_num(dr)
        rr = None if rr in (None, '') else parse_num(rr)
        fuente = str(srow.get('fuente', '') or '').strip()
        saldos[(banco, mes)] = (si, sf, sp, dr, rr, fuente)

    # ===== Saldos por MES CALENDARIO via SALDO CORRIDO =====
    # El banco reporta el saldo por CORTE (p.ej. BBVA corta el dia 22, NO por mes
    # calendario). Pegar el saldo del corte a un mes calendario descuadra: el saldo
    # es del periodo 23->22 pero los ingresos/egresos se suman por mes calendario.
    # Por eso derivamos el saldo de cada mes calendario del SALDO CORRIDO: ancla =
    # saldo inicial del banco del estado MAS ANTIGUO, y se acumula (deposito-retiro)
    # en orden de fecha. Asi DESCUADRE = 0 por construccion, y el primer saldo
    # inicial y el ultimo saldo final amarran con lo que reporta el banco.
    # acc = [si, sf, has_si, has_sf, sp, has_sp]
    _periodos = sorted(saldos.keys(), key=lambda k: k[1])
    bank_si = next((saldos[k][0] for k in _periodos if saldos[k][0] is not None), None)
    bank_sf = next((saldos[k][1] for k in reversed(_periodos) if saldos[k][1] is not None), None)
    sp_por_mes = {}
    for (banco, mes), tup in saldos.items():
        if len(tup) > 2 and tup[2] is not None:
            sp_por_mes[mes] = sp_por_mes.get(mes, 0.0) + tup[2]

    # El saldo corrido asume UNA cuenta. Con varios bancos/cuentas mezclados no
    # aplica (el saldo no es un solo hilo) -> se cae al saldo del banco por mes.
    _un_solo_banco = len({k[0] for k in saldos}) <= 1
    saldo_mes = {}
    if bank_si is not None and txs and _un_solo_banco:
        running = bank_si
        for t in txs:                       # txs ya esta ordenado por fecha
            mk = t['fecha'].strftime('%Y-%m')
            if mk not in saldo_mes:
                saldo_mes[mk] = [round(running, 2), round(running, 2), True, True, 0.0, False]
            running = round(running + t['deposito'] - t['retiro'], 2)
            saldo_mes[mk][1] = running
        for mk in saldo_mes:                # saldo promedio del banco (informativo)
            if mk in sp_por_mes:
                saldo_mes[mk][4] = sp_por_mes[mk]
                saldo_mes[mk][5] = True
    else:
        # Fallback (sin ancla de saldo): saldos del banco mapeados por mes tal cual.
        for (banco, mes), tup in saldos.items():
            si, sf, sp = tup[0], tup[1], tup[2]
            acc = saldo_mes.setdefault(mes, [0.0, 0.0, False, False, 0.0, False])
            if si is not None:
                acc[0] += si
                acc[2] = True
            if sf is not None:
                acc[1] += sf
                acc[3] = True
            if sp is not None:
                acc[4] += sp
                acc[5] = True

    # ===== 3) Ventana de ultimos 6 meses =====
    meses_data = {t['fecha'].strftime('%Y-%m') for t in txs} | set(saldo_mes.keys())
    if meses_data:
        ultimo = max(meses_data)
        uy, um = int(ultimo[:4]), int(ultimo[5:7])
        ventana = []
        y, mth = uy, um
        for _ in range(6):
            ventana.append('%04d-%02d' % (y, mth))
            mth -= 1
            if mth == 0:
                mth, y = 12, y - 1
        ventana.reverse()              # [mas antiguo .. mas reciente]
    else:
        ventana = []

    # ===== 4) Desglose de ingresos/egresos por persona =====
    # En V3.8.1 lo calcula la PLANTILLA sola con formulas dinamicas que agrupan
    # por el nombre que escribimos en la columna Concepto de 08_EC_Raw. Aqui solo
    # contamos cuantos nombres distintos quedaran (tras el tope) para el reporte.
    ordenantes = set(mapa_dep.values())
    beneficiarios = set(mapa_ret.values())

    # ===== 5) Abrir el xlsx como zip y editar XML =====
    zin = zipfile.ZipFile(output_path, 'r')
    contenido = {i.filename: zin.read(i.filename) for i in zin.infolist()}
    infos = {i.filename: i for i in zin.infolist()}
    zin.close()

    # ---- 5a) 08_EC_Raw: TODAS las transacciones (sin filtrar) ----
    raw = contenido[SHEET_RAW].decode('utf-8')
    # estilos de referencia tomados de la propia plantilla
    s_fecha = get_style(raw, 'A2') or get_style(raw, 'A3')
    s_texto = get_style(raw, 'B2')
    s_dep = get_style(raw, 'F2')
    s_mon = get_style(raw, 'G2')

    filas_xml = []
    for i, t in enumerate(txs, start=2):
        # Concepto que se escribe en la columna B = nombre limpio del
        # ordenante (depositos) o beneficiario (retiros), ya con tope de
        # capacidad aplicado. Asi los DOS desgloses de la plantilla (ingresos
        # por FILTER de Deposito>0, egresos por FILTER de Retiro>0) agrupan por
        # persona sin desbordarse.
        if t['deposito'] > 0:
            concepto_out = mapa_dep.get(t['nombre'], t['nombre'])
        else:
            concepto_out = mapa_ret.get(t['nombre'], t['nombre'])
        celdas = [
            build_cell('A%d' % i, s=s_fecha, num=str(serial(t['fecha']))),
            build_cell('B%d' % i, s=s_texto, text=concepto_out[:250]),
            build_cell('C%d' % i, text=t['tipo']),
            build_cell('D%d' % i, text=t['banco']),
            build_cell('E%d' % i, text=t['categoria']),
            build_cell('F%d' % i, s=s_dep, num=t['deposito']),
            build_cell('G%d' % i, s=s_mon, num=t['retiro']),
            build_cell('H%d' % i, s=s_mon, num=t['monto']),
            build_cell('I%d' % i, text=MESES_ES[t['fecha'].month - 1]),
            build_cell('J%d' % i, num=str(t['fecha'].year)),
            build_cell('K%d' % i, text='%02d %d' % (t['fecha'].month, t['fecha'].year)),
            build_cell('L%d' % i, text=t['moneda']),
            build_cell('M%d' % i, num=t['monto_original']),
            # N = bandera de comision (1 si es comision SPEI/transferencia) para
            # la comparativa de # de movimientos. Fuera de Tabla_EC (A:M).
            build_cell('N%d' % i, num='1') if t['es_comision'] else '',
        ]
        filas_xml.append('<row r="%d">%s</row>' % (i, ''.join(celdas)))

    raw = replace_sheet_data_rows(raw, filas_xml, keep_row1=True)
    last_row = max(2, 1 + len(txs))
    raw = re.sub(r'<dimension ref="[^"]*"/>',
                 '<dimension ref="A1:N%d"/>' % last_row, raw)
    contenido[SHEET_RAW] = raw.encode('utf-8')

    # ---- 5b) Tabla_EC: redimensionar al numero real de filas ----
    tbl = contenido[TABLE_EC].decode('utf-8')
    tbl = re.sub(r'(<table [^>]*?ref=")[^"]*(")', r'\g<1>A1:M%d\g<2>' % last_row, tbl)
    tbl = re.sub(r'(<autoFilter [^>]*?ref=")[^"]*(")', r'\g<1>A1:M%d\g<2>' % last_row, tbl)
    contenido[TABLE_EC] = tbl.encode('utf-8')

    # ---- 5c) 03_EstadosCuenta ----
    ec = contenido[SHEET_EC].decode('utf-8')
    # expandir formulas compartidas ANTES de sobreescribir celdas (evita
    # que Excel "repare" el archivo por rangos compartidos inconsistentes)
    ec = desharear_formulas(ec)

    # limites de escaneo de las formulas sobre 08_EC_Raw. La plantilla usa
    # rangos del tipo '08_EC_Raw'!$F$2:$F$1350 (y $1259 en el FILTER, $560 en
    # versiones viejas). Solo se amplian ESOS limites si hay mas transacciones;
    # NO se tocan referencias a celdas como $J$104 (fila TOTAL del desglose).
    # Patron seguro: ":$<col>$<bound>" -> fin de un rango de una columna.
    def _ampliar_bound(m):
        col, b = m.group(1), int(m.group(2))
        nuevo = max(b, last_row)
        return ':$%s$%d' % (col, nuevo)
    ec = re.sub(r':\$([A-Z]{1,3})\$(\d{3,6})\b', _ampliar_bound, ec)

    if ventana:
        # D16 = primer mes de la ventana (E16:I16 son EDATE y se actualizan solos)
        y0, m0 = int(ventana[0][:4]), int(ventana[0][5:7])
        d16 = date(y0, m0, 1)
        ec = set_row_cells(ec, 16, {
            'D': build_cell('D16', s=get_style(ec, 'D16'), num=str(serial(d16))),
        })

        # SALDO INICIAL (fila ROW_SALDO_INI) / SALDO FINAL (ROW_SALDO_FIN):
        # valores REALES del banco. Se escriben TODAS las columnas D..I: con dato
        # real si existe, o vacias si ese mes no tiene estado de cuenta (asi no
        # queda el numero viejo de la plantilla ni la cascada). J = suma fila.
        ri, rf = ROW_SALDO_INI, ROW_SALDO_FIN
        upd19, upd28 = {}, {}
        for off, mes in enumerate(ventana):
            colL = chr(ord('D') + off)
            acc = saldo_mes.get(mes)
            s19 = get_style(ec, '%s%d' % (colL, ri)) or get_style(ec, '%s%d' % (colL, rf))
            s28 = get_style(ec, '%s%d' % (colL, rf))
            if acc and acc[2]:
                upd19[colL] = build_cell('%s%d' % (colL, ri), s=s19, num=round(acc[0], 2))
            else:
                upd19[colL] = build_cell('%s%d' % (colL, ri), s=s19)          # vacia
            if acc and acc[3]:
                upd28[colL] = build_cell('%s%d' % (colL, rf), s=s28, num=round(acc[1], 2))
            else:
                upd28[colL] = build_cell('%s%d' % (colL, rf), s=s28)          # vacia
        upd19['J'] = build_cell('J%d' % ri, s=get_style(ec, 'J%d' % ri) or get_style(ec, 'J%d' % rf),
                                formula='SUM(D%d:I%d)' % (ri, ri))
        upd28['J'] = build_cell('J%d' % rf, s=get_style(ec, 'J%d' % rf),
                                formula='SUM(D%d:I%d)' % (rf, rf))
        ec = set_row_cells(ec, ri, upd19)
        ec = set_row_cells(ec, rf, upd28)

        # SALDO PROMEDIO (fila ROW_SALDO_PROM): saldo promedio REAL de cada mes
        # (suma de cuentas del mes). J33 se deja como esta en la plantilla
        # (=AVERAGE(D33:I33)). Meses sin dato quedan vacios.
        rp = ROW_SALDO_PROM
        upd33 = {}
        for off, mes in enumerate(ventana):
            colL = chr(ord('D') + off)
            acc = saldo_mes.get(mes)
            sp_sty = get_style(ec, '%s%d' % (colL, rp)) or get_style(ec, '%s%d' % (colL, rf))
            if acc and acc[5]:
                upd33[colL] = build_cell('%s%d' % (colL, rp), s=sp_sty, num=round(acc[4], 2))
            else:
                upd33[colL] = build_cell('%s%d' % (colL, rp), s=sp_sty)        # vacia
        ec = set_row_cells(ec, rp, upd33)

    # (Desglose por persona: lo genera la plantilla con su formula dinamica B33;
    #  no se escribe nada en las filas 33+.)

    # ---- VERIFICACION CONTRA TOTALES DEL BANCO (fila ROW_VERIF, V3.8.5) ----
    # Escribe los totales REALES que reporto el banco (depositos/retiros/saldos,
    # deterministas, de totales_banco.py). La plantilla calcula 'Extraido' con
    # SUM(Tabla_EC[...]) y la 'Diferencia'. Si difiere de 0 -> extraccion incompleta.
    rv = ROW_VERIF
    dep_rep_tot = sum(t[3] for t in saldos.values() if len(t) > 3 and t[3] is not None)
    ret_rep_tot = sum(t[4] for t in saldos.values() if len(t) > 4 and t[4] is not None)
    hay_rep = any((len(t) > 3 and t[3] is not None) or (len(t) > 4 and t[4] is not None)
                  for t in saldos.values())
    bancos = sorted({b for (b, m) in saldos.keys() if b and b not in ('N/A', '')})
    # Saldo inicial/final que REPORTA el banco (independiente del saldo corrido):
    # el del estado mas antiguo y el del mas reciente. Sirve de referencia y para
    # cotejar contra los totales del banco.
    si_per = bank_si
    sf_per = bank_sf
    # Fuente de extraccion por mes (determinista=exacto / gemini=respaldo). Se
    # anexa a la linea de banco para que de un vistazo se vea si algun mes cayo a
    # Gemini (y conviene reprocesar tras desplegar el extractor).
    from collections import Counter as _Counter
    _fu = _Counter(t[5] for t in saldos.values() if len(t) > 5 and t[5])
    _fu_txt = ('  |  Fuente: ' + ', '.join('%s (%d)' % (k, v) for k, v in sorted(_fu.items()))) if _fu else ''
    # Comisiones/Intereses marcados Tipo='COMISION' (Banorte: van APARTE de EGRESOS;
    # la plantilla ya los excluye de EGRESOS y los resta en DESCUADRE). Se anota su
    # total y conteo en la linea de banco como referencia (en una celda que existe).
    comi_tot = round(sum(t['retiro'] for t in txs if t['tipo'] == 'COMISION'), 2)
    comi_n = sum(1 for t in txs if t['tipo'] == 'COMISION')
    _comi_txt = ('  |  Comisiones/Intereses (aparte de egresos): $%s (%d mov)'
                 % ('{:,.2f}'.format(comi_tot), comi_n)) if comi_tot > 0 else ''
    # Devoluciones (Banorte, Tipo='DEVOLUCION', monto NEGATIVO): no cuentan como
    # ingreso ni egreso (netean). Se anota su total (positivo) y conteo.
    dev_tot = round(-sum((t['retiro'] or 0) + (t['deposito'] or 0)
                         for t in txs if t['tipo'] == 'DEVOLUCION'), 2)
    dev_n = sum(1 for t in txs if t['tipo'] == 'DEVOLUCION')
    if dev_n:
        _comi_txt += ('  |  Devoluciones (no cuentan): $%s (%d mov)'
                      % ('{:,.2f}'.format(dev_tot), dev_n))
    # Meses FUERA de la ventana de 6 columnas. Los movimientos si estan en
    # 08_EC_Raw, pero 03_EstadosCuenta solo muestra 6 meses: con bancos que cortan
    # a mitad de mes (BBVA corta el dia 22) SEIS estados producen SIETE meses
    # calendario, y el mas viejo queda fuera. Sin este aviso se lee como
    # "faltan movimientos" cuando en realidad es la ventana.
    _fuera = {}
    for t in txs:
        mk = t['fecha'].strftime('%Y-%m')
        if ventana and mk not in ventana:
            acc = _fuera.setdefault(mk, [0, 0.0, 0.0])
            acc[0] += 1
            acc[1] += t['deposito']
            acc[2] += t['retiro']
    meses_fuera = {k: {'movimientos': v[0], 'depositos': round(v[1], 2),
                       'retiros': round(v[2], 2)} for k, v in sorted(_fuera.items())}
    if meses_fuera:
        _nf = sum(v['movimientos'] for v in meses_fuera.values())
        _df = sum(v['depositos'] for v in meses_fuera.values())
        _rf = sum(v['retiros'] for v in meses_fuera.values())
        _comi_txt += ('  |  FUERA DE LA VENTANA DE 6 MESES (%s): %d mov, '
                      'depositos $%s, retiros $%s (si estan en 08_EC_Raw)'
                      % (', '.join(meses_fuera), _nf,
                         '{:,.2f}'.format(_df), '{:,.2f}'.format(_rf)))
    if bancos:
        ec = set_row_cells(ec, rv + 1, {
            'C': build_cell('C%d' % (rv + 1), s=get_style(ec, 'C%d' % (rv + 1)),
                            text=', '.join(bancos) + _fu_txt + _comi_txt)})
    if hay_rep:
        ec = set_row_cells(ec, rv + 3, {
            'D': build_cell('D%d' % (rv + 3), s=get_style(ec, 'D%d' % (rv + 3)), num=round(dep_rep_tot, 2))})
        ec = set_row_cells(ec, rv + 4, {
            'D': build_cell('D%d' % (rv + 4), s=get_style(ec, 'D%d' % (rv + 4)), num=round(ret_rep_tot, 2))})
    if si_per is not None:
        ec = set_row_cells(ec, rv + 5, {
            'D': build_cell('D%d' % (rv + 5), s=get_style(ec, 'D%d' % (rv + 5)), num=round(si_per, 2))})
    if sf_per is not None:
        ec = set_row_cells(ec, rv + 6, {
            'D': build_cell('D%d' % (rv + 6), s=get_style(ec, 'D%d' % (rv + 6)), num=round(sf_per, 2))})

    contenido[SHEET_EC] = ec.encode('utf-8')

    # ---- 5d) 01_Portada (V3.8.1: C9 nombre, C11 RFC, C17 fecha de analisis) ----
    por = contenido[SHEET_PORTADA].decode('utf-8')
    por = desharear_formulas(por)
    if cliente_raw:
        por = set_row_cells(por, 9, {
            'C': build_cell('C9', s=get_style(por, 'C9'), text=str(cliente_raw).upper())})
    if rfc:
        por = set_row_cells(por, 11, {
            'C': build_cell('C11', s=get_style(por, 'C11'), text=rfc.upper())})
    por = set_row_cells(por, 17, {
        'C': build_cell('C17', s=get_style(por, 'C17'), num=str(serial(date.today())))})
    contenido[SHEET_PORTADA] = por.encode('utf-8')

    # ---- 5e) recálculo completo al abrir + eliminar calcChain obsoleto ----
    wbx = contenido['xl/workbook.xml'].decode('utf-8')
    if '<calcPr' in wbx:
        wbx = re.sub(r'<calcPr ', '<calcPr fullCalcOnLoad="1" ', wbx, count=1)
    else:
        wbx = wbx.replace('</workbook>', '<calcPr fullCalcOnLoad="1"/></workbook>')
    contenido['xl/workbook.xml'] = wbx.encode('utf-8')

    if 'xl/calcChain.xml' in contenido:
        del contenido['xl/calcChain.xml']
        rels = contenido['xl/_rels/workbook.xml.rels'].decode('utf-8')
        rels = re.sub(r'<Relationship [^>]*calcChain[^>]*/>', '', rels)
        contenido['xl/_rels/workbook.xml.rels'] = rels.encode('utf-8')
        ct = contenido['[Content_Types].xml'].decode('utf-8')
        ct = re.sub(r'<Override [^>]*calcChain[^>]*/>', '', ct)
        contenido['[Content_Types].xml'] = ct.encode('utf-8')

    # ---- 5f) reempaquetar ----
    with zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED) as zout:
        for nombre, datos in contenido.items():
            zi = infos.get(nombre)
            if zi is not None:
                zi2 = zipfile.ZipInfo(nombre, date_time=zi.date_time)
                zi2.compress_type = zipfile.ZIP_DEFLATED
                zi2.external_attr = zi.external_attr
                zout.writestr(zi2, datos)
            else:
                zout.writestr(nombre, datos)

    return {
        'output': output_path,
        'rows_written': len(txs),
        'comisiones_marcadas': comisiones,
        'ventana_meses': ventana,
        'meses_fuera_ventana': meses_fuera,   # en 08_EC_Raw pero no en 03_EstadosCuenta
        'meses_con_saldo': sorted(saldo_mes.keys()),
        'ordenantes_detectados': len(ordenantes),
        'beneficiarios_detectados': len(beneficiarios),
        'cliente': cliente,
        'portada_filled': {
            'nombre': cliente_raw or None,
            'rfc': rfc or None,
            'fecha_analisis': date.today().strftime('%Y-%m-%d'),
        },
    }


def _hojas_por_nombre(contenido):
    """{nombre de hoja -> 'xl/worksheets/sheetN.xml'} resuelto por los rels.

    NO se puede asumir que la hoja N-esima del libro sea sheetN.xml: el orden de
    las pestanas y el numero de archivo son independientes (en la plantilla de
    microempresa '00_VisitaFisica' es sheet7.xml y '07_EC_Raw' es sheet8.xml).
    """
    wbx = contenido['xl/workbook.xml'].decode('utf-8')
    relsx = contenido['xl/_rels/workbook.xml.rels'].decode('utf-8')
    rels = {}
    for m in re.finditer(r'<Relationship\b[^>]*>', relsx):
        tag = m.group(0)
        rid = re.search(r'Id="([^"]+)"', tag)
        tgt = re.search(r'Target="([^"]+)"', tag)
        if rid and tgt:
            rels[rid.group(1)] = tgt.group(1).lstrip('/')
    hojas = {}
    for m in re.finditer(r'<sheet\b[^>]*>', wbx):
        tag = m.group(0)
        nom = re.search(r'name="([^"]+)"', tag)
        rid = re.search(r'r:id="([^"]+)"', tag)
        if nom and rid and rid.group(1) in rels:
            destino = rels[rid.group(1)]
            if not destino.startswith('xl/'):
                destino = 'xl/' + destino
            hojas[unescape_xml(nom.group(1))] = destino
    return hojas


def unescape_xml(s):
    from xml.sax.saxutils import unescape
    return unescape(s, {'&quot;': '"', '&apos;': "'"})


# Columnas de 07_EC_Raw en la plantilla de microempresa. Ojo: Categoria(E),
# Deposito(F) y Retiro(G) son COLUMNAS CALCULADAS de Tabla_EC (derivan de Tipo y
# Monto), y Mes(I)/Anio(J)/MesAnio(K)/Saldo(M) tambien son formulas. Excel solo
# las autorrellena al editar de forma interactiva, asi que al escribir el XML hay
# que emitir la formula en CADA fila o esas columnas quedan vacias y toda la
# maqueta lee ceros.
MICRO_RAW_HOJA = '07_EC_Raw'
MICRO_EC_HOJA = '04_EstadosCuenta'
MICRO_PORTADA_HOJA = '01_Portada'
MICRO_F = {                       # formulas por fila de 07_EC_Raw
    'E': 'IF(Tabla_EC[[#This Row],[Tipo]]="Depósito","Ingreso","Egreso")',
    'F': 'IF(Tabla_EC[[#This Row],[Categoria]]="Ingreso",Tabla_EC[[#This Row],[Monto]],0)',
    'G': 'IF(Tabla_EC[[#This Row],[Categoria]]="Egreso",Tabla_EC[[#This Row],[Monto]],0)',
    'I': 'IF(Tabla_EC[[#This Row],[Fecha]]="","",TEXT(Tabla_EC[[#This Row],[Fecha]], "mmm"))',
    'J': 'IF(Tabla_EC[[#This Row],[Fecha]]="","",YEAR(Tabla_EC[[#This Row],[Fecha]]))',
    'K': ('IF(Tabla_EC[[#This Row],[Fecha]]="","",TEXT(Tabla_EC[[#This Row],[Fecha]], "mm")'
          ' & " " & YEAR(Tabla_EC[[#This Row],[Fecha]]))'),
}
# Filas de 04_EstadosCuenta que se escriben (el resto son formulas SUMIFS/COUNTIFS
# que leen 07_EC_Raw y se recalculan solas).
MICRO_ROW_MES = 7          # C7 = primer mes de la ventana (D7..H7 son EDATE)
MICRO_ROW_SALDO_INI = 8    # C8 = saldo inicial (D8..H8 = saldo final anterior)
MICRO_ROW_SALDO_PROM = 12  # C12:H12 = saldo promedio del mes (vacias en la plantilla)
MICRO_COLS = ['C', 'D', 'E', 'F', 'G', 'H']


def fill_microempresa(template_path, output_path, data):
    """Llena EXPEDIENTE_FINANCIERO_V1.5 MICROEMPRESA.xlsx.

    A diferencia de la maqueta V3 anterior, esta plantilla SI tiene hoja de datos
    crudos ('07_EC_Raw', con Tabla_EC A1:M) y su hoja 04_EstadosCuenta calcula
    todo con SUMIFS/COUNTIFS sobre ella. Asi que el llenado es el mismo concepto
    que la maqueta estandar: se vuelcan TODOS los movimientos y la maqueta se
    calcula sola. Solo se escriben ademas el primer mes de la ventana, el saldo
    inicial, el saldo promedio por mes y los datos del solicitante.
    """
    cliente = data.get('cliente', 'CLIENTE_SIN_NOMBRE')
    cliente_raw = data.get('cliente_raw', cliente)
    rfc = str(data.get('rfc', '') or '').strip()
    transacciones = data.get('transacciones', []) or []
    saldos_in = data.get('saldos', []) or []
    if not isinstance(transacciones, list):
        raise ValueError("'transacciones' debe ser una lista")

    src = Path(template_path)
    if not src.exists():
        raise FileNotFoundError('Template no existe: %s' % template_path)
    shutil.copy(str(src), output_path)

    # ===== 1) Normalizar (sin filtrar, igual que la maqueta estandar) =====
    txs = []
    for t in transacciones:
        f = parse_fecha(t.get('Fecha'))
        if not f:
            continue
        dep = abs(parse_num(t.get('Deposito')))
        ret = abs(parse_num(t.get('Retiro')))
        concepto = str(t.get('Concepto', '') or '').strip()
        tipo = str(t.get('Tipo', '') or '').strip()
        txs.append({
            'fecha': f,
            'nombre': extraer_nombre(concepto),
            # La columna calculada Categoria compara Tipo="Depósito". Se normaliza
            # para que el acento y la forma coincidan siempre; los demas tipos
            # (COMISION, DEVOLUCION, SIN_IMPORTE) caen en "Egreso", que es correcto.
            'tipo': 'Depósito' if (dep > 0 and ret == 0) else (tipo or 'Retiro'),
            'banco': str(t.get('Banco', '') or 'N/A').strip(),
            'monto': round(dep if dep > 0 else ret, 2),
            'dep': round(dep, 2),
            'ret': round(ret, 2),
            'moneda': (str(t.get('Moneda', '') or 'MXN').strip().upper() or 'MXN'),
        })
    txs.sort(key=lambda x: x['fecha'])

    # tope de nombres distintos, igual que en la maqueta estandar
    def _mapa(clave, etiqueta):
        tot = {}
        for t in txs:
            if t[clave] > 0:
                tot[t['nombre']] = tot.get(t['nombre'], 0.0) + t[clave]
        canon = {n: re.sub(r'[^A-Z0-9]', '', quitar_acentos(n).upper()) for n in tot}
        rep = {}
        for n in sorted(tot, key=lambda x: (-len(canon[x]), x)):
            destino = n
            if len(canon[n]) >= 12:
                for m in rep:
                    if canon[m].startswith(canon[n]) or canon[n].startswith(canon[m]):
                        destino = rep[m]
                        break
            rep[n] = destino
        fus = {}
        for n, dst in rep.items():
            fus[dst] = fus.get(dst, 0.0) + tot[n]
        if len(fus) <= DESGLOSE_CAPACIDAD:
            return {n: rep[n] for n in tot}
        top = set(sorted(fus, key=lambda n: -fus[n])[:DESGLOSE_CAPACIDAD - 1])
        return {n: (rep[n] if rep[n] in top else etiqueta) for n in tot}

    mapa_dep = _mapa('dep', 'OTROS DEPOSITANTES')
    mapa_ret = _mapa('ret', 'OTROS BENEFICIARIOS')

    # ===== 2) Ventana de 6 meses y saldo inicial ancla =====
    meses = sorted({t['fecha'].strftime('%Y-%m') for t in txs})
    ventana = meses[-6:]
    fuera = meses[:-6]
    sal_por_mes = {}
    prom_por_mes = {}
    for s in saldos_in:
        m = str(s.get('mes', ''))
        if not re.match(r'^\d{4}-\d{2}$', m):
            continue
        if s.get('saldo_inicial') not in (None, ''):
            sal_por_mes[m] = parse_num(s.get('saldo_inicial'))
        if s.get('saldo_promedio') not in (None, ''):
            prom_por_mes[m] = parse_num(s.get('saldo_promedio'))
    ancla = None
    for m in ventana:
        if m in sal_por_mes:
            ancla = sal_por_mes[m]
            break
    # saldo promedio por mes: el del banco si lo reporta, si no el punto medio
    # del saldo corrido de ese mes.
    por_mes = {m: [0.0, 0.0] for m in ventana}
    for t in txs:
        m = t['fecha'].strftime('%Y-%m')
        if m in por_mes:
            por_mes[m][0] += t['dep']
            por_mes[m][1] += t['ret']
    corrido = ancla if ancla is not None else 0.0
    prom_calc = {}
    for m in ventana:
        ini = corrido
        fin = round(ini + por_mes[m][0] - por_mes[m][1], 2)
        prom_calc[m] = round((ini + fin) / 2, 2)
        corrido = fin

    # ===== 3) Abrir el xlsx y resolver hojas POR NOMBRE =====
    zin = zipfile.ZipFile(output_path, 'r')
    contenido = {i.filename: zin.read(i.filename) for i in zin.infolist()}
    infos = {i.filename: i for i in zin.infolist()}
    zin.close()
    hojas = _hojas_por_nombre(contenido)
    for req in (MICRO_RAW_HOJA, MICRO_EC_HOJA, MICRO_PORTADA_HOJA):
        if req not in hojas:
            raise ValueError('La plantilla no tiene la hoja %r (hojas: %s)'
                             % (req, sorted(hojas)))

    # ===== 4) 07_EC_Raw: TODOS los movimientos =====
    raw = contenido[hojas[MICRO_RAW_HOJA]].decode('utf-8')
    est = {c: get_style(raw, '%s2' % c) for c in 'ABCDEFGHIJKLM'}
    filas_xml = []
    for i, t in enumerate(txs, start=2):
        nombre = (mapa_dep if t['dep'] > 0 else mapa_ret).get(t['nombre'], t['nombre'])
        celdas = [
            build_cell('A%d' % i, s=est['A'], num=str(serial(t['fecha']))),
            build_cell('B%d' % i, s=est['B'], text=nombre[:250]),
            build_cell('C%d' % i, s=est['C'], text=t['tipo']),
            build_cell('D%d' % i, s=est['D'], text=t['banco']),
            build_cell('E%d' % i, s=est['E'], formula=MICRO_F['E']),
            build_cell('F%d' % i, s=est['F'], formula=MICRO_F['F']),
            build_cell('G%d' % i, s=est['G'], formula=MICRO_F['G']),
            build_cell('H%d' % i, s=est['H'], num=t['monto']),
            build_cell('I%d' % i, s=est['I'], formula=MICRO_F['I']),
            build_cell('J%d' % i, s=est['J'], formula=MICRO_F['J']),
            build_cell('K%d' % i, s=est['K'], formula=MICRO_F['K']),
            build_cell('L%d' % i, s=est['L'], text=t['moneda']),
            # Saldo corrido: la primera fila arranca del saldo inicial del banco.
            build_cell('M%d' % i, s=est['M'],
                       formula=('%s+Tabla_EC[[#This Row],[Deposito]]'
                                '-Tabla_EC[[#This Row],[Retiro]]'
                                % (num_str(ancla) if (i == 2 and ancla is not None)
                                   else ('M%d' % (i - 1)) if i > 2 else '0'))),
        ]
        filas_xml.append('<row r="%d">%s</row>' % (i, ''.join(celdas)))
    raw = replace_sheet_data_rows(raw, filas_xml, keep_row1=True)
    last_row = max(2, 1 + len(txs))
    raw = re.sub(r'<dimension ref="[^"]*"/>', '<dimension ref="A1:M%d"/>' % last_row, raw)
    contenido[hojas[MICRO_RAW_HOJA]] = raw.encode('utf-8')

    # Tabla_EC al numero real de filas
    tbl = contenido[TABLE_EC].decode('utf-8')
    tbl = re.sub(r'(<table [^>]*?ref=")[^"]*(")', r'\g<1>A1:M%d\g<2>' % last_row, tbl)
    tbl = re.sub(r'(<autoFilter [^>]*?ref=")[^"]*(")', r'\g<1>A1:M%d\g<2>' % last_row, tbl)
    contenido[TABLE_EC] = tbl.encode('utf-8')

    # ===== 5) 04_EstadosCuenta =====
    ec = desharear_formulas(contenido[hojas[MICRO_EC_HOJA]].decode('utf-8'))
    # DEFECTO DE LA PLANTILLA: los SUMIFS de Depositos/Retiros/Efectivo cierran el
    # mes con '"<=" & EDATE(C$7,1)', o sea INCLUYEN el dia 1 del mes siguiente, asi
    # que todo movimiento fechado el dia 1 se suma en DOS meses. Los COUNTIFS de
    # al lado (# Depositos / # Retiros) ya usan '"<"', que es lo correcto.
    # Medido con 6 estados de BANAMEX: +$517,344.11 en depositos y +$192,332.71 en
    # retiros contra lo que reporta el banco. Se corrige aqui para que la maqueta
    # cuadre; conviene arreglarlo tambien en la plantilla de origen.
    n_fix = ec.count('"&lt;=" &amp; EDATE(')
    ec = ec.replace('"&lt;=" &amp; EDATE(', '"&lt;" &amp; EDATE(')
    # los SUMIFS/COUNTIFS escanean 07_EC_Raw hasta $2167: se amplia si hay mas filas
    def _ampliar(m):
        return ':$%s$%d' % (m.group(1), max(int(m.group(2)), last_row))
    ec = re.sub(r':\$([A-Z]{1,3})\$(\d{3,6})\b', _ampliar, ec)
    if ventana:
        y0, m0 = int(ventana[0][:4]), int(ventana[0][5:7])
        ec = set_row_cells(ec, MICRO_ROW_MES, {
            'C': build_cell('C%d' % MICRO_ROW_MES,
                            s=get_style(ec, 'C%d' % MICRO_ROW_MES),
                            num=str(serial(date(y0, m0, 1))))})
        if ancla is not None:
            ec = set_row_cells(ec, MICRO_ROW_SALDO_INI, {
                'C': build_cell('C%d' % MICRO_ROW_SALDO_INI,
                                s=get_style(ec, 'C%d' % MICRO_ROW_SALDO_INI),
                                num=round(ancla, 2))})
        for i, m in enumerate(ventana):
            col = MICRO_COLS[i]
            ec = set_row_cells(ec, MICRO_ROW_SALDO_PROM, {
                col: build_cell('%s%d' % (col, MICRO_ROW_SALDO_PROM),
                                s=get_style(ec, '%s%d' % (col, MICRO_ROW_SALDO_PROM)),
                                num=prom_por_mes.get(m, prom_calc[m]))})
        for i in range(len(ventana), 6):     # meses sin estado -> vacio
            col = MICRO_COLS[i]
            ec = set_row_cells(ec, MICRO_ROW_SALDO_PROM, {
                col: build_cell('%s%d' % (col, MICRO_ROW_SALDO_PROM),
                                s=get_style(ec, '%s%d' % (col, MICRO_ROW_SALDO_PROM)))})
    contenido[hojas[MICRO_EC_HOJA]] = ec.encode('utf-8')

    # ===== 6) 01_Portada: C9 nombre, C10 RFC, C14 fecha de analisis =====
    por = desharear_formulas(contenido[hojas[MICRO_PORTADA_HOJA]].decode('utf-8'))
    por = set_row_cells(por, 9, {'C': build_cell('C9', s=get_style(por, 'C9'),
                                                 text=str(cliente_raw).upper())})
    # El RFC se escribe SIEMPRE (aunque venga vacio) para no dejar el de ejemplo.
    por = set_row_cells(por, 10, {'C': build_cell('C10', s=get_style(por, 'C10'),
                                                  text=rfc.upper() if rfc else '')})
    por = set_row_cells(por, 14, {'C': build_cell('C14', s=get_style(por, 'C14'),
                                                  num=str(serial(date.today())))})
    contenido[hojas[MICRO_PORTADA_HOJA]] = por.encode('utf-8')

    # ===== 7) recalculo al abrir =====
    contenido.pop('xl/calcChain.xml', None)
    wbx = contenido['xl/workbook.xml'].decode('utf-8')
    if '<calcPr' in wbx:
        wbx = re.sub(r'<calcPr[^>]*/>', '<calcPr calcId="191029" fullCalcOnLoad="1"/>', wbx)
    else:
        wbx = wbx.replace('</workbook>',
                          '<calcPr calcId="191029" fullCalcOnLoad="1"/></workbook>')
    contenido['xl/workbook.xml'] = wbx.encode('utf-8')
    ct = contenido['[Content_Types].xml'].decode('utf-8')
    ct = re.sub(r'<Override[^>]*calcChain[^>]*/>', '', ct)
    contenido['[Content_Types].xml'] = ct.encode('utf-8')

    zout = zipfile.ZipFile(output_path, 'w', zipfile.ZIP_DEFLATED)
    for nombre, blob in contenido.items():
        if nombre == 'xl/calcChain.xml':
            continue
        zi = infos.get(nombre)
        zout.writestr(zi if zi is not None else nombre, blob)
    zout.close()

    return {
        'output': output_path,
        'plantilla': 'microempresa',
        'rows_written': len(txs),
        'ventana_meses': ventana,
        'meses_fuera_ventana': fuera,
        'saldo_inicial_ancla': ancla,
        'ordenantes_detectados': len(set(mapa_dep.values())),
        'beneficiarios_detectados': len(set(mapa_ret.values())),
        'sumifs_corregidos': n_fix,   # '<=' -> '<' en el cierre de mes (ver nota)
        'cliente': cliente,
    }


def main():
    if len(sys.argv) < 2:
        print(json.dumps({"error": "Uso: fill_template.py <output_path> [template_path] [--microempresa]"}))
        sys.exit(1)
    argv = [a for a in sys.argv[1:] if a != '--microempresa']
    micro = '--microempresa' in sys.argv
    output_path = argv[0]
    template_path = argv[1] if len(argv) > 1 else DEFAULT_TEMPLATE
    try:
        data = json.load(sys.stdin)
    except Exception as e:
        print(json.dumps({"error": "JSON invalido por stdin: %s" % e}))
        sys.exit(1)
    try:
        result = (fill_microempresa if micro else fill)(template_path, output_path, data)
        print(json.dumps(result, ensure_ascii=False))
    except Exception as e:
        import traceback
        print(json.dumps({"error": str(e), "trace": traceback.format_exc()}))
        sys.exit(1)


if __name__ == '__main__':
    main()