#!/usr/bin/env python3
"""
fill_template.py
================
Recibe JSON por stdin con cliente + transacciones, abre la plantilla
EXPEDIENTE_FINANCIERO_FINAL.xlsx, llena la pestana 08_EC_Raw, y guarda
el resultado en la ruta de salida que se pasa por argv[1].

Uso:
    echo '<json>' | python3 fill_template.py /tmp/salida.xlsx [template_path]

JSON esperado:
    {
      "cliente": "ANDRES_PEREZ_BANUELOS",
      "transacciones": [
        {
          "Fecha": "2025-10-16",   # YYYY-MM-DD
          "Concepto": "...",
          "Tipo": "DEPOSITO" | "RETIRO",
          "Banco": "BBVA",
          "Categoria": "Ingresos Operativos" | "" ,
          "Deposito": 1000.0,
          "Retiro": 0,
          "Monto": 1000.0
        },
        ...
      ]
    }

Salida (stdout):
    {"output": "<path>", "rows_written": N, "cliente": "..."}
"""

import json
import sys
import shutil
from datetime import datetime
from pathlib import Path

try:
    from openpyxl import load_workbook
except ImportError:
    print(json.dumps({"error": "openpyxl no instalado. Ejecuta: pip install openpyxl"}))
    sys.exit(1)


DEFAULT_TEMPLATE = "/home/node/python_scripts/EXPEDIENTE_FINANCIERO_FINAL.xlsx"
SHEET_NAME = "08_EC_Raw"
PORTADA_SHEET = "01_Portada"
TABLE_NAME = "Tabla_EC"
HEADER_ROW = 1
DATA_START_ROW = 2

# Celdas editables en 01_Portada (segun la inspeccion del template)
PORTADA_CELDAS = {
    'C9':  'nombre',         # Nombre / Razon Social
    'C10': 'rfc',            # RFC
    'C15': 'fecha_analisis', # Fecha de Analisis (hoy)
}

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


def parse_fecha(val):
    """Convierte string YYYY-MM-DD a datetime, o devuelve None."""
    if not val:
        return None
    if isinstance(val, datetime):
        return val
    s = str(val).strip()[:10]
    try:
        return datetime.strptime(s, '%Y-%m-%d')
    except Exception:
        return None


def parse_num(val):
    if val is None or val == '':
        return 0
    try:
        return float(val)
    except (TypeError, ValueError):
        return 0


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

    # Copia template a output_path
    src = Path(template_path)
    if not src.exists():
        raise FileNotFoundError(f"Template no existe: {template_path}")
    shutil.copy(str(src), output_path)

    wb = load_workbook(output_path)

    # ===== 01_Portada: llenar datos del cliente =====
    if PORTADA_SHEET in wb.sheetnames:
        wp = wb[PORTADA_SHEET]
        # Solo escribimos si tenemos dato (no sobreescribir con vacio)
        if cliente_raw:
            wp['C9'] = cliente_raw.upper()
        if rfc:
            wp['C10'] = str(rfc).upper().strip()
        # Fecha de analisis = hoy
        wp['C15'] = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0)
        wp['C15'].number_format = 'yyyy-mm-dd'

    # ===== 08_EC_Raw: llenar con TERCIOS POR ESTADO DE CUENTA =====
    # En lugar de meter todas las transacciones detalladas, se agrupan
    # por (Banco, MesAno) para identificar cada EC, y se dividen sus
    # transacciones en 3 bloques cronologicos iguales. Por cada bloque
    # se escribe UNA fila con: rango fechas + sumas + neto.
    if SHEET_NAME not in wb.sheetnames:
        raise ValueError(f"La pestana '{SHEET_NAME}' no existe en el template")
    ws = wb[SHEET_NAME]

    # Limpiar todas las filas de datos previas
    last_row = ws.max_row
    if last_row >= DATA_START_ROW:
        ws.delete_rows(DATA_START_ROW, last_row - DATA_START_ROW + 1)

    # Headers ampliados:
    # L=Trimestre, M=Rango Fechas, N=Moneda, O=Monto Original, P=Tipo de Cambio
    headers_extra = {
        12: 'Trimestre',
        13: 'Rango Fechas',
        14: 'Moneda',
        15: 'Monto Original',
        16: 'Tipo de Cambio',
    }
    for col, name in headers_extra.items():
        if str(ws.cell(row=HEADER_ROW, column=col).value or '').strip().lower() != name.lower():
            ws.cell(row=HEADER_ROW, column=col, value=name)

    def trimestre_de(fecha_dt):
        if not fecha_dt:
            return ''
        return f"Q{((fecha_dt.month - 1) // 3) + 1}"

    # ===== Calcular tercios por EC (Banco, MesAno, Moneda) =====
    # Importante: agrupar por moneda. Deposito/Retiro ya vienen en MXN (convertidos
    # via Banxico TC). MontoOriginal mantiene el monto en la moneda nativa.
    grupos = {}
    for t in transacciones:
        fecha_dt = parse_fecha(t.get('Fecha'))
        if not fecha_dt:
            continue
        banco = str(t.get('Banco', '') or 'N/A').strip()
        moneda = str(t.get('Moneda', '') or 'MXN').strip().upper()
        if moneda not in ('MXN', 'USD', 'EUR'):
            moneda = 'MXN'
        mesanio = f"{fecha_dt.year}-{fecha_dt.month:02d}"
        key = (banco, mesanio, moneda)
        if key not in grupos:
            grupos[key] = []
        grupos[key].append({
            'fecha_dt': fecha_dt,
            'deposito': parse_num(t.get('Deposito')),
            'retiro': parse_num(t.get('Retiro')),
            'monto_original': parse_num(t.get('MontoOriginal') or t.get('Monto')),
            'tipo_cambio': parse_num(t.get('TipoCambio')) or 1.0,
        })

    # Construir lista de filas TERCIO (3 por EC+moneda)
    filas_tercio = []
    # Ordenar por periodo cronologicamente, luego banco, luego moneda
    for key in sorted(grupos.keys(), key=lambda k: (k[1], k[0], k[2])):
        banco, mesanio, moneda = key
        txs = sorted(grupos[key], key=lambda x: x['fecha_dt'])
        n = len(txs)
        if n == 0:
            continue

        b1_end = n // 3
        b2_end = (2 * n) // 3
        bloques = [
            ('1', txs[0:b1_end] if b1_end > 0 else txs[0:1]),
            ('2', txs[b1_end:b2_end] if b2_end > b1_end else txs[b1_end:b1_end+1]),
            ('3', txs[b2_end:n] if n > b2_end else txs[-1:])
        ]
        # Caso de pocas tx: si n<3 solo emite filas con datos disponibles
        if n < 3:
            bloques = [(str(i+1), [txs[i]] if i < n else []) for i in range(3)]

        for nombre_bloque, lista in bloques:
            if not lista:
                continue
            fechas = [x['fecha_dt'] for x in lista]
            fecha_min = min(fechas)
            fecha_max = max(fechas)
            sum_dep_mxn = sum(x['deposito'] for x in lista)
            sum_ret_mxn = sum(x['retiro'] for x in lista)
            neto_mxn = sum_dep_mxn - sum_ret_mxn
            # Suma en moneda original (sumando deposito + retiro como abs / pero
            # mejor: sumar monto original tal cual ya viene con signo o como abs)
            sum_original = sum(x['monto_original'] for x in lista)
            # TC: si la moneda es MXN, TC=1.0; si USD/EUR, usar el TC de la primera tx
            # (todas las del bloque deberian tener el mismo TC del dia de proceso)
            tc_usado = lista[0]['tipo_cambio'] if moneda != 'MXN' else 1.0

            fecha_medio = lista[len(lista)//2]['fecha_dt']

            filas_tercio.append({
                'Fecha': fecha_min,
                'Concepto': f"Tercio {nombre_bloque} de {banco} {mesanio} {moneda} ({len(lista)} tx)",
                'Tipo': 'TERCIO',
                'Banco': banco,
                'Categoria': '',
                'Deposito': round(sum_dep_mxn, 2),
                'Retiro': round(sum_ret_mxn, 2),
                'Monto': round(neto_mxn, 2),
                'Mes': MESES_ES[fecha_medio.month - 1],
                'Anio': fecha_medio.year,
                'MesAnio': f"{fecha_medio.month:02d} {fecha_medio.year}",
                'Trimestre': trimestre_de(fecha_medio),
                'RangoFechas': f"{fecha_min.strftime('%Y-%m-%d')} al {fecha_max.strftime('%Y-%m-%d')}",
                'Moneda': moneda,
                'MontoOriginal': round(sum_original, 2),
                'TipoCambio': round(tc_usado, 4),
                'NumTx': len(lista)
            })

    # Escribir las filas tercio en la pestana
    # F-H = montos en MXN (convertidos); O = monto en moneda original; P = TC
    for i, f in enumerate(filas_tercio, start=DATA_START_ROW):
        ws.cell(row=i, column=1, value=f['Fecha'])
        ws.cell(row=i, column=1).number_format = 'yyyy-mm-dd'
        ws.cell(row=i, column=2, value=f['Concepto'])
        ws.cell(row=i, column=3, value=f['Tipo'])
        ws.cell(row=i, column=4, value=f['Banco'])
        ws.cell(row=i, column=5, value=f['Categoria'])
        # Deposito/Retiro/Monto siempre en MXN (formato pesos)
        fmt_mxn_pos = '"$"#,##0.00'
        fmt_mxn_neg = '"$"#,##0.00;[Red]("$"#,##0.00)'
        ws.cell(row=i, column=6, value=f['Deposito'])
        ws.cell(row=i, column=6).number_format = fmt_mxn_pos
        ws.cell(row=i, column=7, value=f['Retiro'])
        ws.cell(row=i, column=7).number_format = fmt_mxn_pos
        ws.cell(row=i, column=8, value=f['Monto'])
        ws.cell(row=i, column=8).number_format = fmt_mxn_neg
        ws.cell(row=i, column=9, value=f['Mes'])
        ws.cell(row=i, column=10, value=f['Anio'])
        ws.cell(row=i, column=11, value=f['MesAnio'])
        ws.cell(row=i, column=12, value=f['Trimestre'])
        ws.cell(row=i, column=13, value=f['RangoFechas'])
        moneda = f.get('Moneda', 'MXN')
        ws.cell(row=i, column=14, value=moneda)
        # Monto original en su moneda nativa (USD/EUR/MXN)
        ws.cell(row=i, column=15, value=f['MontoOriginal'])
        if moneda == 'USD':
            ws.cell(row=i, column=15).number_format = '"US$"#,##0.00'
        elif moneda == 'EUR':
            ws.cell(row=i, column=15).number_format = '"€"#,##0.00'
        else:
            ws.cell(row=i, column=15).number_format = '"$"#,##0.00'
        # Tipo de cambio
        ws.cell(row=i, column=16, value=f['TipoCambio'])
        ws.cell(row=i, column=16).number_format = '#,##0.0000'

    # Actualizar referencia de Tabla_EC para incluir hasta col P (TipoCambio)
    total_tercios = len(filas_tercio)
    last_row_new = HEADER_ROW + total_tercios if total_tercios > 0 else HEADER_ROW + 1
    new_ref = f"A1:P{last_row_new}"
    if TABLE_NAME in ws.tables:
        ws.tables[TABLE_NAME].ref = new_ref

    wb.save(output_path)
    return {
        "output": output_path,
        "rows_written": total_tercios,
        "tercios_generados": total_tercios,
        "ec_detectados": len(grupos),
        "transacciones_consolidadas": len(transacciones),
        "cliente": cliente,
        "portada_filled": {
            "nombre": cliente_raw if cliente_raw else None,
            "rfc": rfc if rfc else None,
            "fecha_analisis": datetime.now().strftime('%Y-%m-%d')
        }
    }


def _UNUSED_llenar_resumen_trimestral(wb, transacciones):
    """Crea/limpia pestana '10_Resumen_Trimestral' y la llena con totales por Trimestre+Ano."""
    sheet_name = '10_Resumen_Trimestral'
    if sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        # Limpiar todo
        if ws.max_row > 0:
            ws.delete_rows(1, ws.max_row)
    else:
        ws = wb.create_sheet(sheet_name)

    # Agrupar por (Ano, Trimestre)
    grupos = {}
    for t in transacciones:
        fecha_dt = parse_fecha(t.get('Fecha'))
        if not fecha_dt:
            continue
        anio = fecha_dt.year
        q = ((fecha_dt.month - 1) // 3) + 1
        key = (anio, q)
        if key not in grupos:
            grupos[key] = {
                'count': 0, 'sum_dep': 0.0, 'sum_ret': 0.0,
                'fecha_min': fecha_dt, 'fecha_max': fecha_dt
            }
        g = grupos[key]
        g['count'] += 1
        g['sum_dep'] += parse_num(t.get('Deposito'))
        g['sum_ret'] += parse_num(t.get('Retiro'))
        if fecha_dt < g['fecha_min']: g['fecha_min'] = fecha_dt
        if fecha_dt > g['fecha_max']: g['fecha_max'] = fecha_dt

    # Escribir headers
    headers = ['Año', 'Trimestre', 'Periodo', 'Fecha Desde', 'Fecha Hasta',
               '# Transacciones', 'Σ Depósitos', 'Σ Retiros', 'Saldo Neto']
    for c, h in enumerate(headers, start=1):
        ws.cell(row=1, column=c, value=h)
    _bold_header(ws, 1, len(headers))

    # Escribir filas ordenadas cronologicamente
    row = 2
    for key in sorted(grupos.keys()):
        anio, q = key
        g = grupos[key]
        neto = g['sum_dep'] - g['sum_ret']
        ws.cell(row=row, column=1, value=anio)
        ws.cell(row=row, column=2, value=f'Q{q}')
        ws.cell(row=row, column=3, value=f'Q{q} {anio}')
        ws.cell(row=row, column=4, value=g['fecha_min'])
        ws.cell(row=row, column=4).number_format = 'yyyy-mm-dd'
        ws.cell(row=row, column=5, value=g['fecha_max'])
        ws.cell(row=row, column=5).number_format = 'yyyy-mm-dd'
        ws.cell(row=row, column=6, value=g['count'])
        ws.cell(row=row, column=7, value=round(g['sum_dep'], 2))
        ws.cell(row=row, column=7).number_format = '"$"#,##0.00'
        ws.cell(row=row, column=8, value=round(g['sum_ret'], 2))
        ws.cell(row=row, column=8).number_format = '"$"#,##0.00'
        ws.cell(row=row, column=9, value=round(neto, 2))
        ws.cell(row=row, column=9).number_format = '"$"#,##0.00;[Red]("$"#,##0.00)'
        row += 1

    # Auto-ajustar anchos basicos
    for col_idx, w in enumerate([8, 10, 12, 14, 14, 16, 16, 16, 16], start=1):
        from openpyxl.utils import get_column_letter
        ws.column_dimensions[get_column_letter(col_idx)].width = w

    return len(grupos)


def llenar_tercios(wb, transacciones):
    """Crea/limpia pestana '11_Tercios' con 3 bloques por cada estado de cuenta (banco+periodo).

    Identifica un EC por (Banco, MesAno). Dentro de cada EC ordena por fecha,
    divide en 3 bloques cronologicos iguales por cantidad, y calcula totales.
    """
    sheet_name = '11_Tercios'
    if sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        if ws.max_row > 0:
            ws.delete_rows(1, ws.max_row)
    else:
        ws = wb.create_sheet(sheet_name)

    # Agrupar transacciones por (Banco, MesAno=YYYY-MM dominante)
    grupos = {}
    for t in transacciones:
        fecha_dt = parse_fecha(t.get('Fecha'))
        if not fecha_dt:
            continue
        banco = str(t.get('Banco', '') or 'N/A').strip()
        # MesAno como string YYYY-MM
        mesanio = f"{fecha_dt.year}-{fecha_dt.month:02d}"
        key = (banco, mesanio)
        if key not in grupos:
            grupos[key] = []
        grupos[key].append({
            'fecha_dt': fecha_dt,
            'deposito': parse_num(t.get('Deposito')),
            'retiro': parse_num(t.get('Retiro'))
        })

    # Headers
    headers = ['EC', 'Banco', 'Periodo (Año-Mes)', 'Bloque',
               'Fecha Desde', 'Fecha Hasta', '# Transacciones',
               'Σ Depósitos', 'Σ Retiros', 'Saldo Neto']
    for c, h in enumerate(headers, start=1):
        ws.cell(row=1, column=c, value=h)
    _bold_header(ws, 1, len(headers))

    row = 2
    total_grupos = 0
    # Ordenar grupos por (banco, periodo) cronologicamente
    for key in sorted(grupos.keys(), key=lambda k: (k[1], k[0])):
        banco, mesanio = key
        txs = sorted(grupos[key], key=lambda x: x['fecha_dt'])
        n = len(txs)
        if n == 0:
            continue

        # Dividir en 3 bloques cronologicos lo mas iguales posible
        b1_end = n // 3
        b2_end = (2 * n) // 3
        bloques = [
            ('1', txs[0:b1_end]),
            ('2', txs[b1_end:b2_end]),
            ('3', txs[b2_end:n])
        ]

        ec_label = f"{banco} {mesanio}"
        for nombre_bloque, lista in bloques:
            if not lista:
                # bloque vacio (caso muy raro con muy pocas tx)
                continue
            fechas = [x['fecha_dt'] for x in lista]
            sum_dep = sum(x['deposito'] for x in lista)
            sum_ret = sum(x['retiro'] for x in lista)
            neto = sum_dep - sum_ret
            ws.cell(row=row, column=1, value=ec_label)
            ws.cell(row=row, column=2, value=banco)
            ws.cell(row=row, column=3, value=mesanio)
            ws.cell(row=row, column=4, value=f"Tercio {nombre_bloque}")
            ws.cell(row=row, column=5, value=min(fechas))
            ws.cell(row=row, column=5).number_format = 'yyyy-mm-dd'
            ws.cell(row=row, column=6, value=max(fechas))
            ws.cell(row=row, column=6).number_format = 'yyyy-mm-dd'
            ws.cell(row=row, column=7, value=len(lista))
            ws.cell(row=row, column=8, value=round(sum_dep, 2))
            ws.cell(row=row, column=8).number_format = '"$"#,##0.00'
            ws.cell(row=row, column=9, value=round(sum_ret, 2))
            ws.cell(row=row, column=9).number_format = '"$"#,##0.00'
            ws.cell(row=row, column=10, value=round(neto, 2))
            ws.cell(row=row, column=10).number_format = '"$"#,##0.00;[Red]("$"#,##0.00)'
            row += 1
        total_grupos += 1

    # Anchos
    from openpyxl.utils import get_column_letter
    for col_idx, w in enumerate([22, 14, 14, 12, 14, 14, 16, 16, 16, 16], start=1):
        ws.column_dimensions[get_column_letter(col_idx)].width = w

    return total_grupos * 3


def main():
    if len(sys.argv) < 2:
        print(json.dumps({"error": "Uso: fill_template.py <output_path> [template_path]"}))
        sys.exit(1)

    output_path = sys.argv[1]
    template_path = sys.argv[2] if len(sys.argv) > 2 else DEFAULT_TEMPLATE

    try:
        data = json.load(sys.stdin)
    except Exception as e:
        print(json.dumps({"error": f"JSON invalido por stdin: {e}"}))
        sys.exit(1)

    try:
        result = 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()