"""Módulo para procesar archivos Excel en el proyecto ResumenF29."""
import locale
import pandas as pd
from openpyxl.styles import numbers, Alignment, Font, Border, Side, PatternFill
from openpyxl.utils import get_column_letter
from openpyxl import load_workbook

# Módulo para manejar detalles de archivos Excel en el proyecto ResumenF29."""

def generar_hoja_detalle(excel_path):
    """ Diccionario de códigos con sus etiquetas correspondientes """
    codigos_etiquetas = {
        '020': "Exportaciones",
        '142': "Ventas Exentas",
        '538': "Total IVA DF",
        '000': "Total Ventas",
        '563': "Base Imponible",
        '001': "Diferencia 563",
        '062': "PPM Determinado",
        '066': "PPM Renta Presunta",
        '005': "IVA CF del Mes",
        '504': "Remanente CF",
        '537': "Total IVA CF",
        '003': "Compras Netas",
        '562': "Compras Exentas",
        '004': "Total Compras",
        '593': "Dev. IVA CF",
        '525': "IVA Act. Fijos",
        '002': "Neto Act.Fijos",
        '048': "Ret. I.Unico",
        '049': "Ret. 3% Rta 42 1",
        '155': "Ret. 3% Rta 42 2",
        '151': "Ret. 2da. Cat.",
        '547': "Total Determinado"
    }

    codigos_buscados = list(codigos_etiquetas.keys())
    datos_detalle = []

    with pd.ExcelFile(excel_path) as xls:
        for sheet_name in xls.sheet_names:
            if sheet_name == "Detalle":
                continue

            df = pd.read_excel(xls, sheet_name=sheet_name)
            valores = {"Periodo": sheet_name}

            for codigo in codigos_buscados:
                # Se busca el código como entero para coincidir con el formato de convierte_pdf
                valor = df.loc[df["Código"] == int(codigo), "Valor"].values
                valores[codigo] = valor[0] if len(valor) > 0 else None

            datos_detalle.append(valores)

    # Crear DataFrame con los datos
    df_detalle = pd.DataFrame(datos_detalle)

    # Ordenar por período
    df_detalle["Periodo"] = pd.to_datetime(df_detalle["Periodo"], format="%Y%m")
    df_detalle = df_detalle.sort_values(by="Periodo")

    # Configurar locale para español
    try:
        locale.setlocale(locale.LC_TIME, 'es_ES.UTF-8')
    except locale.Error:
        try:
            locale.setlocale(locale.LC_TIME, 'spanish')
        except locale.Error:
            print("Locale español no disponible, usando configuración por defecto")
            locale.setlocale(locale.LC_TIME, '')

    df_detalle["Periodo"] = df_detalle["Periodo"].dt.strftime("%B %Y")

    # Reordenar columnas
    columnas_ordenadas = ["Periodo"] + codigos_buscados
    df_detalle = df_detalle[columnas_ordenadas]

    # Exportar a Excel con formato especial
    with pd.ExcelWriter(excel_path, engine="openpyxl", mode="a") as writer:
        # Escribir datos comenzando desde la fila 3 (dejando espacio para encabezados)
        df_detalle.to_excel(writer, sheet_name="Detalle", index=False, startrow=1)

        workbook = writer.book
        worksheet = writer.sheets["Detalle"]

        # Escribir primera fila de encabezado (códigos)
        for col_num, codigo in enumerate(codigos_buscados, start=1):
            cell = worksheet.cell(row=1, column=col_num+1)  # +1 para saltar columna "Periodo"
            cell.value = codigo
            cell.alignment = Alignment(horizontal="center", vertical="center")

        # Escribir segunda fila de encabezado (etiquetas)
        for col_num, codigo in enumerate(codigos_buscados, start=1):
            cell = worksheet.cell(row=2, column=col_num+1)
            cell.value = codigos_etiquetas[codigo]
            cell.alignment = Alignment(horizontal="center", vertical="center")

        # Escribir título columna "Periodo"
        worksheet.cell(row=1, column=1).value = "Periodo"
        worksheet.cell(row=2, column=1).value = "(Mes Año)"

        # Formato numérico y alineación para datos
        for col in codigos_buscados:
            col_index = df_detalle.columns.get_loc(col) + 1
            col_letter = get_column_letter(col_index)

            for row in range(3, len(df_detalle) + 3):
                cell = worksheet.cell(row=row, column=col_index)
                if cell.value is not None:
                    try:
                        if isinstance(cell.value, str):
                            clean_value = cell.value.replace('.', '').replace(',', '.')
                        else:
                            clean_value = str(cell.value)

                        num_value = float(clean_value)
                        rounded_value = int(round(num_value))
                        cell.value = rounded_value
                        cell.number_format = '#,##0'
                        cell.alignment = Alignment(horizontal="right")

                    except (ValueError, AttributeError):
                        cell.number_format = numbers.FORMAT_TEXT

            worksheet.column_dimensions[col_letter].width = 14

            # AGREGAR FILA DE TOTALES
            last_data_row = len(df_detalle) + 2
            totals_row_idx = 15 # Fila de totales fija según estructura solicitada

            for col_num_border in range(1, len(df_detalle.columns) + 1):
                cell_border = worksheet.cell(row=totals_row_idx, column=col_num_border)
                cell_border.border = Border(top=Side(style='medium'))

            total_label_cell = worksheet.cell(row=totals_row_idx, column=1, value="TOTAL...")
            total_label_cell.font = Font(bold=True)

            for col_num_sum, codigo_sum in enumerate(codigos_buscados, start=1):
                col_letter_sum = get_column_letter(col_num_sum + 1)
                formula = f"=SUM({col_letter_sum}3:{col_letter_sum}{last_data_row})"
                cell_sum = worksheet.cell(row=totals_row_idx, column=col_num_sum + 1, value=formula)
                cell_sum.number_format = '#,##0'
                cell_sum.font = Font(bold=True)
                cell_sum.alignment = Alignment(horizontal="right")

        worksheet.column_dimensions['A'].width = 14
        worksheet.merge_cells('A1:A2')
        worksheet.freeze_panes = 'A3'
        workbook.move_sheet(worksheet, offset=-len(workbook.sheetnames)+1)


def actualizar_calculos_detalle(excel_path):
    """
    Lee la hoja "Detalle" del archivo Excel, recalcula los valores para cada período y
    actualiza las celdas correspondientes.
    
    Mejora v1.2.6:
    - Se incluye el código 504 (Remanente) en la limpieza numérica.
    - Si 504 está vacío o no es numérico, se trata como 0.
    - '003' (Compras Netas) = (537 - 504) / 0.19
    """
    codigos_etiquetas = {
        '020': "Exportaciones", '142': "Ventas Exentas", '538': "Total IVA DF",
        '000': "Total Ventas", '563': "Base Imponible", '001': "Diferencia 563",
        '062': "PPM Determinado", '066': "PPM Renta Presunta", '005': "IVA CF del Mes", 
        '504': "Remanente CF",
        '537': "Total IVA CF", '003': "Compras Netas", '562': "Compras Exentas",
        '004': "Total Compras", '593': "Dev. IVA CF", '525': "IVA Act. Fijos",
        '002': "Neto Act.Fijos", '048': "Ret. I.Unico", '049': "Ret. 3% Rta 42 1",
        '155': "Ret. 3% Rta 42 2", '151': "Ret. 2da. Cat.", '547': "Total Determinado"
    }

    codigos_buscados = list(codigos_etiquetas.keys())
    columnas = ["Periodo"] + codigos_buscados

    # Leer datos saltando encabezados
    df = pd.read_excel(excel_path, sheet_name="Detalle", skiprows=2, header=None, names=columnas)

    # Definir columnas necesarias para cálculos
    cols_to_clean = ['020', '142', '538', '563', '537', '504', '562', '525']
    
    for col in cols_to_clean:
        if col not in df.columns:
            df[col] = 0
        # TRATAMIENTO: 'coerce' convierte vacíos en NaN, y fillna(0) los hace 0 numérico
        df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)

    # Realizar Cálculos
    df['000'] = (df['020'] + df['142'] + (df['538'] / 0.19)).round().astype(int)
    df['001'] = (df['000'] - df['563']).round().astype(int)
    df['002'] = (df['525'] / 0.19).round().astype(int)
    df['005'] = (df['537']  - df['504']).round().astype(int)
    
    #'005': "IVA CF del Mes",
    
    # NUEVA FÓRMULA SOLICITADA (TRATANDO EL REMANENTE 504 COMO 0 SI ESTÁ VACÍO)
    df['003'] = ((df['537'] - df['504']) / 0.19).round().astype(int)
    
    df['004'] = (df['003'] + df['562']).round().astype(int)

    # Abrir Excel para aplicar cambios y formato
    wb = load_workbook(excel_path)
    ws = wb["Detalle"]

    # Mapeo de columnas
    header_row = [cell.value for cell in ws[1]]
    codigo_to_col = {str(valor): idx + 1 for idx, valor in enumerate(header_row) if valor is not None}
    yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

    # Actualizar Datos en Amarillo
    start_row = 3
    for i, (_, row_data) in enumerate(df.iterrows()):
        excel_row = start_row + i
        for codigo in ['000', '001', '002', '003', '004', '005']:
            if codigo in codigo_to_col:
                col_index = codigo_to_col[codigo]
                cell = ws.cell(row=excel_row, column=col_index, value=int(row_data[codigo]))
                cell.number_format = '#,##0'
                cell.alignment = Alignment(horizontal="right")
                cell.fill = yellow_fill

    # Actualizar Fila de Totales (Fila 15)
    totals_row = 15
    if ws.cell(row=totals_row, column=1).value == "TOTAL...":
        for codigo in ['000', '001', '002', '003', '004', '005']:
            if codigo in codigo_to_col:
                col_index = codigo_to_col[codigo]
                total_sum = int(df[codigo].sum())
                cell = ws.cell(row=totals_row, column=col_index, value=total_sum)
                cell.number_format = '#,##0'
                cell.alignment = Alignment(horizontal="right")
                cell.fill = yellow_fill

    # Agregar Nota al pie
    comment_row = totals_row + 2
    comment_text = ("Las columnas '000', '001', '002', '003', '004', '005' en amarillo no están en el F29, "
                    "sino que son calculadas para un mayor análisis.")
    ws.merge_cells(start_row=comment_row, start_column=1, end_row=comment_row, end_column=len(columnas))
    comment_cell = ws.cell(row=comment_row, column=1, value=comment_text)
    comment_cell.alignment = Alignment(horizontal="center")

    wb.save(excel_path)
    print("Cálculos v1.2.6 (003 con Remanente 504) actualizados en Excel.")