from http.client import HTTPException
import sys
import os
from datetime import datetime
from typing import List
from fastapi import UploadFile
import pandas as pd
from io import BytesIO
import re
import httpx
from typing import List, Dict, Any, Optional
from dotenv import load_dotenv  # 👈 import dotenv
from tabulate import tabulate
# Cargar variables desde el archivo .env
load_dotenv()



# Agrega la ruta absoluta al sys.path
sys.path.append('/var/www/html/config')

from cnxpdo import get_connection



def getListaTablas():
    try:
        conexionBD = get_connection()
        if conexionBD is None:
            return {
                "success": 0,
                "message": "Error de conexión"
            }

        cursor = conexionBD.cursor(dictionary=True)
        query = "SELECT * FROM listado_tablas_prospectos WHERE estado_contacto  = 'Prospectos'"
        cursor.execute(query)
        userData = cursor.fetchall()

        cursor.close()
        conexionBD.close()

        return {
            "success": 1,
            "message": "tablas obtenidas correctamente",
            "data": userData
        }
    except Exception as e:
        return {
            "success": 0,
            "message": f"Error: {str(e)}"
        }

def traer_tablas_permitidas():
    try:
        conexionBD = get_connection()
        if conexionBD is None:
            return {
                "success": 0,
                "message": "Error de conexión"
            }

        cursor = conexionBD.cursor(dictionary=True)
        query = "SELECT * FROM listado_tablas_prospectos"
        cursor.execute(query)
        tablas = cursor.fetchall()
        nombre_tablas = []
        if len(tablas) > 0:
            nombre_tablas = [item['nombre_tabla'] for item in tablas]

        cursor.close()
        conexionBD.close()

        return nombre_tablas
    except Exception as e:
        return {
            "success": 0,
            "message": f"Error: {str(e)}"
        }

def datos_tabla_seleccionada(data):
    try:
        conexionBD = get_connection()
        if conexionBD is None:
            return {
                "success": 0,
                "message": "Error de conexión"
            }
        
        tabla = data.get("tablaSeleccionada")
        # page = data.get("page", 1)
        # page_size = data.get("pageSize", 5)
        # offset = (page - 1) * page_size
        
        # Lista blanca de tablas permitidas
        tablas_validas = traer_tablas_permitidas()
        if len(tablas_validas) == 0 :
            return {
                "success": False,
                "message": "No se encontraron tablas permitidas"
            }

        if tabla not in tablas_validas:
            return {
                "success": False,
                "message": "Tabla no permitida"
            }

        cursor = conexionBD.cursor(dictionary=True)

        # Consulta con el filtro WHERE t.estado_etapa = 1
        query = f"""
            SELECT 
                t.*, e.estado_etapa AS estado_etapa_desc
            FROM 
                {tabla} t
            LEFT JOIN 
                estados_etapas e ON t.estado_etapa = e.id
            WHERE 
                t.estado_etapa IN (1, 5)
            ORDER BY 
                t.id DESC
        """

        # cursor.execute(query, (page_size, offset))
        cursor.execute(query)
        registros = cursor.fetchall()

        # Consulta de conteo con la misma condición WHERE t.estado_etapa = 1
        # count_query = f"""
        #     SELECT COUNT(*) AS total
        #     FROM {tabla} t
        #     WHERE t.estado_etapa = 1
        # """
        # cursor.execute(count_query)
        # total = cursor.fetchone()['total']
        # total_pages = (total // page_size) + (1 if total % page_size != 0 else 0)
        
        # Reemplazamos el ID por la descripción del estado_etapa
        for registro in registros:
            registro['estado_etapa'] = registro.get('estado_etapa_desc', None)  # Usamos estado_etapa_desc

        return {
            "success": True,
            "message": "Datos obtenidos correctamente",
            "data": registros,
            # "totalRecords": total,
            # "totalPages": total_pages,
            # "currentPage": page
        }
        
    except Exception as e:
        return {
            "success": False,
            "message": f"Error: {str(e)}"
        }
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conexionBD' in locals() and conexionBD:
            conexionBD.close()
            
def actualizar_voluntario_db(id_voluntario, data):
    conexionBD = get_connection()
    cursor = None  # Inicializar el cursor aquí para evitar el error de "variable no asociada"
    
    if conexionBD is None:
        return {"success": False, "message": "Error de conexión a la base de datos"}

    try:
        # Obtener el nombre de la tabla
        nombre_tabla = data.get("nombre_tabla", "")
        # Depuración
        print(f"Datos de llegada: {data}")
        print(f"Nombre de la tabla: {nombre_tabla}")

        # Verificar si 'nombre_tabla' está vacío o no es válido
        if nombre_tabla not in [
            "inscripciones_voluntarios", 
            "union_voluntarios_antiguos", 
            "base_datos_10k", 
            "registros_crm_antiguo",
            "prospectos_manual"
        ]:
            return {"success": False, "message": "Tabla no válida especificada en la solicitud"}

        # Desestructurar los datos comunes para ambas tablas
        tipo_documento = data.get("tipo_documento", None) 
        documento = data.get("documento", None) 
        nombres = data.get("nombres", None)  
        apellidos = data.get("apellidos", None) 
        correos = data.get("correos", None) 
        telefono = data.get("telefono", None) 
        pais = data.get("pais", None) 
        departamento = data.get("departamento", None) 
        ciudad = data.get("ciudad", None) 
        direccion = data.get("direccion", None) 
        genero = data.get("genero", None) 
        recomendado = data.get("recomendado", None) or 'No' 
        nombre_recomendador = data.get("nombre_recomendador", None) or 'No recomendado' 
        grupo_wp = data.get("grupo_wp", None) 
        grupo_wp1 = data.get("grupo_wp1", None) 
        como_ayudar = data.get("como_ayudar", None) 
        pasion = data.get("pasion", None) 
        nombres_completos = data.get("nombres_completos", None) 
        intereses = data.get("intereses", None) 
        autorizacion = data.get("autorizacion", None) 
        barrio = data.get("barrio", None) 
        fecha_cumpleanos = data.get("fecha_cumpleanos", None) 
        localidad = data.get("localidad", None) 
        tipo_sangre = data.get("tipo_sangre", None) 
        fecha_registro = data.get("fecha_registro", None) 
        


        # Crear el cursor solo si el nombre_tabla es válido
        cursor = conexionBD.cursor(dictionary=True)

        query = None  # Inicializar la variable query
        params = []    # Lista de parámetros

        if nombre_tabla == "inscripciones_voluntarios":
            query = """
                UPDATE inscripciones_voluntarios
                SET tipo_documento = %s, documento = %s, nombres = %s, apellidos = %s, correos = %s, 
                    telefono = %s, pais = %s, departamento = %s, ciudad = %s, direccion = %s, 
                    genero = %s, recomendado = %s, nombre_recomendador = %s, grupo_wp = %s, 
                    grupo_wp1 = %s, como_ayudar = %s, pasion = %s
                WHERE id = %s
            """
            params = [tipo_documento, documento, nombres, apellidos, correos, telefono,
                      pais, departamento, ciudad, direccion, genero, recomendado, 
                      nombre_recomendador, grupo_wp, grupo_wp1, como_ayudar, pasion, id_voluntario]

            
        elif nombre_tabla == "base_datos_10k":
            query = """
              UPDATE base_datos_10k
              SET fecha_registro = %s, nombres_completos = %s, documento = %s, correos = %s, 
              telefono = %s, departamento = %s, ciudad = %s, intereses = %s, autorizacion = %s
              WHERE id = %s
          """
            params = [fecha_registro, nombres_completos, documento, correos,
            telefono, departamento, ciudad, intereses, autorizacion, id_voluntario]
        
        elif nombre_tabla == "registros_crm_antiguo":
            query = """
                UPDATE registros_crm_antiguo
                SET nombres = %s, apellidos = %s, correos = %s, telefono = %s,
                    tipo_documento = %s, documento = %s, departamento = %s, ciudad = %s, localidad = %s,
                    barrio = %s, direccion = %s, fecha_cumpleanos = %s, genero = %s, tipo_sangre = %s
                WHERE id = %s
            """
        
            params = [nombres, apellidos, correos, telefono,
                tipo_documento, documento, departamento, ciudad, localidad,
                barrio, direccion, fecha_cumpleanos, genero, tipo_sangre, id_voluntario]

        elif nombre_tabla == "prospectos_manual":
            # Obtener fecha_registro actual para preservarla
            cursor.execute("SELECT fecha_registro FROM prospectos_manual WHERE id = %s", (id_voluntario,))
            result = cursor.fetchone()
            fecha_registro_actual = result['fecha_registro'] if result else None
            
            query = """
                UPDATE prospectos_manual
                SET nombres = %s, apellidos = %s, tipo_documento = %s, documento = %s, correos = %s, 
                    telefono = %s, pais = %s, departamento = %s, ciudad = %s, localidad = %s, 
                    barrio = %s, direccion = %s, fecha_cumpleanos = %s, genero = %s, tipo_sangre = %s, 
                    recomendado = %s, nombre_recomendador = %s, grupo_wp = %s, grupo_wp1 = %s, 
                    como_ayudar = %s, pasion = %s, intereses = %s, autorizacion = %s, fecha_registro = %s
                WHERE id = %s
            """
            params = [nombres, apellidos, tipo_documento, documento, correos, telefono, 
                    pais, departamento, ciudad, localidad, barrio, direccion, fecha_cumpleanos, 
                    genero, tipo_sangre, recomendado, nombre_recomendador, grupo_wp, grupo_wp1, 
                    como_ayudar, pasion, intereses, autorizacion, fecha_registro_actual, id_voluntario]


        # Si 'query' sigue siendo None, significa que 'nombre_tabla' no es válido
        if query is None:
            return {"success": False, "message": "Tabla no válida especificada en la solicitud"}

        

        # Ejecutar la consulta
        cursor.execute(query, params)

        # Confirmar los cambios en la base de datos
        conexionBD.commit()

        # Query para obtener los datos actualizados
        query_select = f"SELECT * FROM {nombre_tabla} WHERE id = %s"
        cursor.execute(query_select, (id_voluntario,))
        voluntario_actualizado = cursor.fetchone()

        cursor.close()
        conexionBD.close()

        return {"success": True, "message": "Voluntario actualizado", "data": voluntario_actualizado}
    
    except Exception as e:
        return {"success": False, "message": f"Error al actualizar los datos: {str(e)}"}
    
    finally:
        if cursor:
            cursor.close()
        if conexionBD:
            conexionBD.close()

            

def createNotes(data):
    try:
        conexionBD = get_connection()
        if conexionBD is None:
            return {
                "success": 0,
                "message": "Error de conexión"
            }

        id_empleado = data.get("id_empleado")
        id_contacto_origen = data.get("id_contacto_origen")
        id_contacto_union = data.get("id_contacto_union")
        origen_datos = data.get("origen_datos")
        id_estado_etapa = data.get("id_estado_etapa")
        nota = data.get("nota")
        
        if id_estado_etapa in [None, "", "undefined", "null"]:
            id_estado_etapa = None

        if any(x is None for x in [id_empleado, id_contacto_origen, id_contacto_union, origen_datos, nota]):
            return {
                "success": 0,
                "message": "Faltan datos requeridos"
            }

        cursor = conexionBD.cursor()
        query = """
            INSERT INTO notas_contacto (fecha_hora, id_empleado, id_contacto_origen, id_contacto_union, origen_datos, id_estado_etapa, nota)
            VALUES (NOW(), %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(query, (id_empleado, id_contacto_origen, id_contacto_union, origen_datos, id_estado_etapa, nota))
        conexionBD.commit()

        return {
            "success": 1,
            "message": "Nota creada correctamente"
        }

    except Exception as e:
        return {
            "success": 0,
            "message": f"Error: {str(e)}"
        }
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conexionBD' in locals() and conexionBD:
            conexionBD.close()


def getNotesContact(data):
    try:
        conexionBD = get_connection()
        if conexionBD is None:
            return {
                "success": 0,
                "message": "Error de conexión"
            }

        id_contacto_origen = data.get("id_contacto_origen")
        if not id_contacto_origen:
            return {
                "success": 0,
                "message": "Falta el ID del contacto"
            }

        cursor = conexionBD.cursor(dictionary=True)
        query = """
            SELECT nc.*, 
                   CONCAT(e.nombres, ' ', e.apellidos) AS nombre_empleado, 
                   ee.estado_etapa AS estado_etapa
            FROM notas_contacto nc
            JOIN empleados e ON nc.id_empleado = e.id
            LEFT JOIN estados_etapas ee ON nc.id_estado_etapa = ee.id
            WHERE nc.id_contacto_origen = %s
            ORDER BY nc.fecha_hora DESC
        """
        cursor.execute(query, (id_contacto_origen,))
        notas = cursor.fetchall()

        return {
            "success": 1,
            "message": "Notas obtenidas correctamente",
            "data": notas
        }

    except Exception as e:
        return {
            "success": 0,
            "message": f"Error: {str(e)}"
        }
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conexionBD' in locals() and conexionBD:
            conexionBD.close()



def verificacion_persona_contactada(data: dict):
    try:
        # Extraer los valores del diccionario
        documento = data.get("documento")
        correos = data.get("correos")
        telefono = data.get("telefono")
        idOrigenDato = data.get("idOrigenDato", None)
        nombreTabla = data.get("nombreTabla", "")
        
        # Conexión a la base de datos
        conexionBD = get_connection()  # Asumiendo que get_connection() está implementado correctamente
        if conexionBD is None:
            return {"success": False, "message": "Error de conexión a la base de datos", "data": {}}

        cursor = conexionBD.cursor(dictionary=True)

        # Crear la consulta SQL para buscar registros
        # query = """
        #     SELECT id, documento, correos, telefono
        #     FROM union_contactos_personas
        #     WHERE documento = %s OR correos = %s OR telefono = %s
        # """
        
        query = """
            SELECT id
            FROM union_contactos_personas
            WHERE id_origen_datos = %s
                AND origen_datos = %s
        """
        
        cursor.execute(query, (idOrigenDato, nombreTabla))
        resultado = cursor.fetchone()

        # Si se encuentra un registro
        if resultado:
            return {
                "success": True,
                "message": "Registro encontrado en tabla de unión",
                "data": {
                    "campo_encontrado": resultado["id"],
                    "exist": True
                }
            }
        else:
            # Si no se encuentra un registro
            return {
                "success": True,
                "message": "Registro no encontrado en la tabla unión",
                "data": {
                    "campo_encontrado": None,
                    "exist": False
                }
            }

    except Error as e:
        # Si hay un error en la consulta
        return {"success": False, "message": f"Error en la consulta: {str(e)}", "data": {}}
    finally:
        # Cerrar el cursor y la conexión
        if cursor:
            cursor.close()
        if conexionBD:
            conexionBD.close()



def actualizar_estado_etapa_verificado(data: dict):
    try:
        # Extraemos los valores del diccionario
        origen_datos = data.get("origen_datos")
        estado_etapa = data.get("estado_etapa")
        id_registro = data.get("id")
        
        # Lista blanca de tablas válidas
        tablas_validas = [
            "inscripciones_voluntarios", 
            "registros_crm_antiguo", 
            "base_datos_10k",
            "prospectos_manual"
        ]  # Añadir otras tablas permitidas aquí
        
        # Verificar que la tabla indicada en 'origen_datos' sea válida
        if origen_datos not in tablas_validas:
            return {"success": False, "message": "La tabla especificada no es válida", "data": {}}
        
        # Conexión a la base de datos
        conexionBD = get_connection()  # Asumimos que esta función está correctamente implementada
        if conexionBD is None:
            return {"success": False, "message": "Error de conexión a la base de datos", "data": {}}

        cursor = conexionBD.cursor()

        # Consulta SQL para actualizar el estado de la etapa en la tabla indicada
        update_query = f"""
            UPDATE {origen_datos} 
            SET estado_etapa = %s
            WHERE id = %s
        """
        
        cursor.execute(update_query, (estado_etapa, id_registro))
        
        # Si se actualizó algún registro, commit y retorno de éxito
        if cursor.rowcount > 0:
            conexionBD.commit()
            return {"success": True, "message": "Estado de la etapa del contacto actualizado a primer contacto", "data": {"id": id_registro}}
        else:
            return {"success": False, "message": "No se encontró el registro con el id proporcionado", "data": {}}

    except Error as e:
        # Manejo de errores en la consulta
        return {"success": False, "message": f"Error en la actualización: {str(e)}", "data": {}}
    finally:
        # Cerrar cursor y conexión
        if cursor:
            cursor.close()
        if conexionBD:
            conexionBD.close()


def agregar_prospecto_nuevo(data: dict):
    conexionBD = None
    cursor = None
    try:
        # Extracción de datos del diccionario "data"
        nombreTabla = data.get("nombreTabla", "")
        nombres = data.get("nombres", "")
        apellidos = data.get("apellidos", "")
        documento = data.get("documento", "")
        correos = data.get("correos", "")
        telefono = data.get("telefono", "")
        tipo_documento = data.get("tipo_documento", None)
        departamento = data.get("departamento", None)
        ciudad = data.get("ciudad", None)
        estado_etapa = data.get("estado_etapa", None)
        genero = data.get("genero", None)
        fecha_registro = data.get("fecha_registro", None)
        intereses = data.get("intereses", None)
        autorizacion = data.get("autorizacion", None)
        pais = data.get("pais", None)
        direccion = data.get("direccion", None)
        recomendado = data.get("recomendado", None)
        nombre_recomendador = data.get("nombre_recomendador", None)
        grupo_wp = data.get("grupo_wp", None)
        grupo_wp1 = data.get("grupo_wp1", None)
        como_ayudar = data.get("como_ayudar", None)
        pasion = data.get("pasion", None)
        localidad = data.get("localidad", None)
        barrio = data.get("barrio", None)
        fecha_cumpleanos = data.get("fecha_cumpleanos", None)
        tipo_sangre = data.get("tipo_sangre", None)
        
        # Validar que la tabla sea válida
        tablas_validas = [
            "inscripciones_voluntarios", 
            "registros_crm_antiguo", 
            "base_datos_10k",
            "prospectos_manual"
        ]
        if nombreTabla not in tablas_validas:
            return {"success": False, "message": "Tabla no válida", "data": {}}
        
        # Conexión a la base de datos
        conexionBD = get_connection()  # Asumimos que esta función está correctamente implementada
        if conexionBD is None:
            return {"success": False, "message": "Error de conexión a la base de datos", "data": {}}
        
        cursor = conexionBD.cursor(dictionary=True)
        
        
        # Verificación de duplicados (documento, correos, telefono)
        campo_duplicado = None
        
        # Comprobamos los tres campos por separado
        if documento:
            query_verificar_documento = """
                SELECT id FROM {tabla} WHERE documento = %s
            """.format(tabla=nombreTabla)
            cursor.execute(query_verificar_documento, (documento,))
            if cursor.fetchone():
                campo_duplicado = "documento"
        
        if not campo_duplicado and correos:
            query_verificar_correos = """
                SELECT id FROM {tabla} WHERE correos = %s
            """.format(tabla=nombreTabla)
            cursor.execute(query_verificar_correos, (correos,))
            if cursor.fetchone():
                campo_duplicado = "correos"
        
        if not campo_duplicado and telefono:
            query_verificar_telefono = """
                SELECT id FROM {tabla} WHERE telefono = %s
            """.format(tabla=nombreTabla)
            cursor.execute(query_verificar_telefono, (telefono,))
            if cursor.fetchone():
                campo_duplicado = "telefono"
        
        if campo_duplicado:
            return {"success": False, "message": f"Ya existe un registro con el {campo_duplicado} proporcionado", "data": {}}
        
        # Mapeo de los datos a insertar por tabla
        if nombreTabla == "inscripciones_voluntarios":
            query_insert = """
                INSERT INTO inscripciones_voluntarios (
                    tipo_documento, documento, nombres, apellidos, correos, telefono, 
                    pais, departamento, ciudad, direccion, genero, recomendado, 
                    nombre_recomendador, grupo_wp, grupo_wp1, como_ayudar, pasion, 
                    fecha_registro, estado_etapa, fecha_estado
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """
            valores = (
                tipo_documento, documento, nombres, apellidos, correos, telefono,
                pais, departamento, ciudad, direccion,
                genero, recomendado, nombre_recomendador,
                grupo_wp, grupo_wp1, como_ayudar,
                pasion, fecha_registro, estado_etapa
            )

        elif nombreTabla == "base_datos_10k":
            nombres_completos = f"{nombres} {apellidos}"
            query_insert = """
                INSERT INTO base_datos_10k (
                    fecha_registro, nombres_completos, documento, correos, telefono, 
                    departamento, ciudad, intereses, autorizacion, estado_etapa, fecha_estado
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """
            valores = (
                fecha_registro, nombres_completos, documento, correos, telefono, 
                departamento, ciudad, intereses, autorizacion,
                estado_etapa
            )

        elif nombreTabla == "registros_crm_antiguo":
            query_insert = """
                INSERT INTO registros_crm_antiguo (
                    nombres, apellidos, correos, telefono, tipo_documento, documento,
                    departamento, ciudad, localidad, barrio, direccion, fecha_cumpleanos,
                    genero, tipo_sangre, estado_etapa, fecha_estado
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """
            valores = (
                nombres, apellidos, correos, telefono, tipo_documento, documento,
                departamento, ciudad, localidad, barrio,
                direccion, fecha_cumpleanos, genero,
                tipo_sangre, estado_etapa
            )

        # Agregar el nuevo elif para la tabla "prospectos_manual"
        elif nombreTabla == "prospectos_manual":
            query_insert = """
                INSERT INTO prospectos_manual (
                    nombres, apellidos, tipo_documento, documento, correos, telefono, 
                    pais, departamento, ciudad, localidad, barrio, direccion, fecha_cumpleanos, 
                    genero, tipo_sangre, recomendado, nombre_recomendador, grupo_wp, grupo_wp1, 
                    como_ayudar, pasion, intereses, autorizacion, fecha_registro, estado_etapa, fecha_estado
                ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, NOW())
            """
            valores = (
                nombres, apellidos, tipo_documento, documento, correos, telefono, 
                pais, departamento, ciudad, localidad, barrio, direccion, fecha_cumpleanos, 
                genero, tipo_sangre, recomendado, nombre_recomendador, grupo_wp, grupo_wp1, 
                como_ayudar, pasion, intereses, autorizacion, fecha_registro, estado_etapa
            )

        # Ejecutar la consulta de inserción
        cursor.execute(query_insert, valores)
        conexionBD.commit()

        # Obtener el ID del registro insertado
        id_insertado = cursor.lastrowid

        # Cerrar la conexión y el cursor
        cursor.close()
        conexionBD.close()

        # Retornar el éxito con el ID del prospecto insertado
        return {
            "success": True,
            "message": "Prospecto agregado exitosamente",
            "data": {"id": id_insertado}
        }

    except Error as e:
        # Registra la consulta SQL y valores que causaron el error
        print(f"Error de MySQL: {str(e)}")
        if cursor and hasattr(cursor, 'statement'):
            print(f"Última consulta ejecutada: {cursor.statement}")
        return {"success": False, "message": f"Error en la inserción: {str(e)}", "data": {}}
    
    except Exception as e:
        print(f"Error general: {str(e)}")
        return {"success": False, "message": f"Error inesperado: {str(e)}", "data": {}}
    
    finally:
        # Cerrar cursor y conexión
        if cursor:
            cursor.close()
        if conexionBD:
            conexionBD.close()
            

async def archivo_a_dataframe(archivo: UploadFile, procesamiento_ligero: bool = False) -> pd.DataFrame:
    """
    Convierte un UploadFile (Excel o CSV) en un DataFrame de pandas.
    - Si procesamiento_ligero=True → sólo lee las primeras 15 filas (rápido) 
      y deja el puntero en 0 para poder volver a procesar el archivo completo después.
    - Si procesamiento_ligero=False → lee el archivo completo.
    """
    TIPOS_EXCEL = {
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        "application/vnd.ms-excel",
        "application/vnd.ms-excel.sheet.macroEnabled.12",
    }

    nombre_archivo = (archivo.filename or "").lower()
    tipo_contenido = archivo.content_type or ""

    if procesamiento_ligero:
        # leer solo las primeras 15 filas directo del stream
        try:
            archivo.file.seek(0)
        except Exception:
            pass
        if nombre_archivo.endswith((".xlsx", ".xls", ".xlsm", ".xlsb")) or tipo_contenido in TIPOS_EXCEL:
            df = pd.read_excel(archivo.file, sheet_name=0, dtype=str, nrows=15)
        else:
            df = pd.read_csv(archivo.file, dtype=str, sep=None, engine="python", nrows=15)

        # dejar puntero al inicio para poder usarlo después
        try:
            archivo.file.seek(0)
        except Exception:
            pass
        return df

    else:
        # leer archivo completo a memoria
        raw = await archivo.read()
        try:
            archivo.file.seek(0)
        except Exception:
            pass

        if nombre_archivo.endswith((".xlsx", ".xls", ".xlsm", ".xlsb")) or tipo_contenido in TIPOS_EXCEL:
            return pd.read_excel(BytesIO(raw), sheet_name=0, dtype=str)
        else:
            return pd.read_csv(BytesIO(raw), dtype=str, sep=None, engine="python")

async def procesamiento_principal_paso_1_validacion_formato(excel: UploadFile) -> Dict[str, Any]:
    df = await archivo_a_dataframe(excel, procesamiento_ligero=True)

    if df is None:
        return {"success": False, "message": "Error al leer el archivo", "data": {}}

    if not validar_headers_en_primera_fila(df):
        return {"success": False, "message": "Encabezados inválidos en el archivo"}
    else:
        return {"success": True, "message": "Encabezados válidos"}

async def procesamiento_principal_paso_2_obtener_columnas_excel(excel: UploadFile):
    df = await archivo_a_dataframe(excel, procesamiento_ligero=True)
    if df is None:
        return {"success": False, "message": "Error al leer el archivo", "data": {}}
    columnas = obtener_columnas_df(df)
    if len(columnas) == 0:
        return {"success": False, "message": "No se encontraron columnas en el archivo", "data": {"columnas": []}}
    return {"success": True, "message": "Columnas obtenidas", "data": {"columnas": columnas}}

async def procesamiento_principal_paso_3_obtener_columnas_obligatorias_base_de_datos():
    return obtener_columnas_obligatorias_prospectos_manual()


async def procesamiento_principal_paso_4_obtener_columnas_opcionales_base_de_datos():
    return obtener_columnas_opcionales_prospectos_manual()

async def procesamiento_principal_paso_5_validacion_openai(excel: UploadFile):
    df = await archivo_a_dataframe(excel, procesamiento_ligero=True)
    respuesta_openai = await validar_datos_con_openai(df, timeout_seconds=60)
    return respuesta_openai


async def procesamiento_principal_paso_6_procesar_excel_y_guardar_en_base_de_datos(
    excel: UploadFile, 
    mapeo_columnas: Dict[str, str]
) -> Dict[str, Any]:
    print("=== Paso 6: Inicio ===")
    print(f"📌 Mapeo Columnas recibido: {mapeo_columnas}")

    df = await archivo_a_dataframe(excel)
    if df is None:
        print("❌ Error: no se pudo leer el Excel")
        return {"success": False, "message": "Error al leer el archivo", "data": {}}
    
    try:
        print(f"✅ DataFrame leído con {len(df)} filas y {len(df.columns)} columnas")
        print(f"📋 Columnas originales del Excel: {list(df.columns)}")

        # Validar que las columnas del mapeo existan en el Excel
        faltantes = [col for col in mapeo_columnas.keys() if col not in df.columns]
        if faltantes:
            print(f"⚠️ Columnas faltantes: {faltantes}")
            return {
                "success": False,
                "message": f"Columnas no encontradas en Excel: {faltantes}",
                "data": {"faltantes": faltantes, "columns_excel": list(df.columns)},
            }

        print("👉 Aplicando mapeo_de_columnas...")
        df_mapeado = mapeo_de_columnas(df.copy(), mapeo_columnas)
        print(f"✅ df_mapeado listo. Columnas: {list(df_mapeado.columns)}")

        print("👉 Aplicando incluir_solo_columnas_necesarias...")
        df_con_columnas_necesarias = incluir_solo_columnas_necesarias(df_mapeado.copy())
        print(f"✅ df_con_columnas_necesarias listo. Columnas: {list(df_con_columnas_necesarias.columns)}")

        print("👉 Aplicando normalizar_columnas_obligatorias...")
        df_columnas_obligatorias = normalizar_columnas_obligatorias(df_con_columnas_necesarias.copy())
        print(f"✅ df_columnas_obligatorias listo. Columnas: {list(df_columnas_obligatorias.columns)}")

        print("👉 Aplicando normalizar_columnas_opcionales...")
        df_columnas_opcionales = normalizar_columnas_opcionales(df_columnas_obligatorias.copy())
        print(f"✅ df_columnas_opcionales listo. Columnas: {list(df_columnas_opcionales.columns)}")

        print("👉 Aplicando categorizacion_prospectos...")
        df_categorizado = categorizacion_prospectos(df_columnas_opcionales.copy())
        print(f"✅ df_categorizado listo. Columnas: {list(df_categorizado.columns)}")

        print("=== Paso 6: Finalizado correctamente ===")

        # 🔹 Debug: devolver preview JSON
        return {
            "success": True,
            "data": {
                "dataframe_original": df.head(2).fillna("").to_dict(orient="records"),
                "dataframe_mapeado": df_mapeado.head(2).fillna("").to_dict(orient="records"),
                "dataframe_con_columnas_necesarias": df_con_columnas_necesarias.head(2).fillna("").to_dict(orient="records"),
                "dataframe_columnas_obligatorias": df_columnas_obligatorias.head(2).fillna("").to_dict(orient="records"),
                "dataframe_columnas_opcionales": df_columnas_opcionales.head(2).fillna("").to_dict(orient="records"),
                "dataframe_categorizado": df_categorizado.head(2).fillna("").to_dict(orient="records"),
            }
        }

    except Exception as e:
        print("❌ ERROR en Paso 6:")
        import traceback
        traceback.print_exc()
        raise HTTPException(status_code=400, detail=str(e))
def validar_headers_en_primera_fila(df: pd.DataFrame) -> bool:
    """
    Valida que en la primera fila (header=0) existan encabezados reales.
    Retorna True si hay al menos un header válido distinto de 'Unnamed', False si no.
    """
    columnas = [str(c).strip().lower() for c in df.columns]
    headers_validos = [c for c in columnas if c and not c.startswith("unnamed")]
    return len(headers_validos) > 0


def obtener_columnas_obligatorias_prospectos_manual():
        conexionBD = get_connection()  # Asumimos que esta función está correctamente implementada
        if conexionBD is None:
            return {"success": False, "message": "Error de conexión a la base de datos", "data": {}}
        
        cursor = conexionBD.cursor(dictionary=True)
        cursor.execute("SELECT columna FROM columnas_obligatorias where es_obligatoria = 1 AND tabla = 'prospectos_manual'")
        columnas_obligatorias = cursor.fetchall()
        return {"success": True, "message": "Columnas obligatorias obtenidas", "data": [c["columna"] for c in columnas_obligatorias if c["columna"] != "id"]}

def obtener_todas_las_columnas_prospectos_manual():
        conexionBD = get_connection()  # Asumimos que esta función está correctamente implementada
        if conexionBD is None:
            return {"success": False, "message": "Error de conexión a la base de datos", "data": {}}

        cursor = conexionBD.cursor(dictionary=True)
        cursor.execute("SELECT columna FROM columnas_obligatorias WHERE tabla = 'prospectos_manual'")
        columnas= cursor.fetchall()
        return {"success": True, "message": "Todas las columnas obtenidas", "data": [c["columna"] for c in columnas if c["columna"] != "id"]}
def obtener_columnas_opcionales_prospectos_manual():
        conexionBD = get_connection()  # Asumimos que esta función está correctamente implementada
        if conexionBD is None:
            return {"success": False, "message": "Error de conexión a la base de datos", "data": {}}
        
        cursor = conexionBD.cursor(dictionary=True)
        cursor.execute("SELECT columna FROM columnas_obligatorias where es_obligatoria = 0 AND tabla = 'prospectos_manual'")
        columnas_opcionales = cursor.fetchall()
        return {"success": True, "message": "Columnas opcionales obtenidas", "data": [c["columna"] for c in columnas_opcionales if c["columna"] != "id"]}




def obtener_columnas_df(df: pd.DataFrame) -> List[str]:
    return [str(c).strip() for c in df.columns.tolist()]



def obtener_primeras_10_filas_df(df: pd.DataFrame) -> list[dict]:
    """
    Retorna una lista de hasta 10 diccionarios con las primeras filas del DataFrame,
    limpiando NaN/None a vacío.
    """
    # Tomar primeras 10 filas
    df_muestra = df.head(10).copy()
    
    # Limpiar NaN/None -> vacío
    df_muestra = df_muestra.fillna("")

    # Convertir a lista de diccionarios
    lista_diccionarios = df_muestra.to_dict(orient="records")

    return lista_diccionarios

async def validar_datos_con_openai(
    df: pd.DataFrame,
    openai_api_key: Optional[str] = None,
    model: str = "gpt-4o-mini",
    timeout_seconds: int = 30
) -> Dict[str, Any]:
    """
    Recibe un DataFrame, extrae columnas y primeras 10 filas como string,
    envía a OpenAI y retorna:
      {
        "ai_validation": "exitosamenteexitoso" | "fracasadamentefracasado",
        "valid": bool
      }
    """
    columnas = obtener_columnas_obligatorias_prospectos_manual()
    filas_texto = str(obtener_primeras_10_filas_df(df))
    prompt = f"""
Eres un validador de coherencia de datos tabulares. 
Tu tarea es revisar si las columnas especificadas tienen sentido lógico y son consistentes.

COLUMNAS A VALIDAR:
{columnas}

MUESTRA DE DATOS (Es una lista de diccionarios que mapea la tabla):
{filas_texto}

Criterios de validación:
- Documento: puede ser cédula, tarjeta de identidad, pasaporte, NIT u otro identificador. 
  Debe parecer razonable (dígitos o combinación alfanumérica típica de documentos), 
  no palabras incoherentes como "ABCXYZ" o "123hola".
- Nombres: deben contener letras, no solo números ni símbolos raros.
- En general: se considera coherente si al menos el 60% de los campos de las columnas especificadas tienen sentido. 
  No es necesario que todos estén correctos, basta con mayoría razonable.
- Los datos nulos no son considerados incoherentes.

Reglas de salida:
1. Si las filas cumplen con al menos un 50% de coherencia en las columnas especificadas → responde ÚNICAMENTE: exitosamenteexitoso
2. Si alguna fila cae por debajo del 50% de coherencia en las columnas especificadas → responde ÚNICAMENTE: fracasadamentefracasado

No des explicaciones, no repitas las filas. Responde SOLO con la palabra clave fracasadamentefracasado o exitosamenteexitoso.
"""


    api_key = (openai_api_key or os.getenv("OPENAI_API_KEY") or "").strip().strip('"').strip("'")
    print(f"Usando API Key: {'sí' if api_key else 'no'}")
    print(f"Api Key: {api_key}")
    if not api_key:
        return {"ai_validation": "fracasadamentefracasado", "valid": False}

    url = "https://api.openai.com/v1/chat/completions"
    headers = {"Authorization": f"Bearer {api_key}", "Content-Type": "application/json"}
    payload = {
        "model": model,
        "messages": [
            {"role": "system", "content": "Eres un verificador de coherencia de datos tabulares."},
            {"role": "user", "content": prompt}
        ],
        "temperature": 0,
    }
    print(f"prompt: {prompt}")
    ai_text = ""
    try:
        async with httpx.AsyncClient(timeout=timeout_seconds) as client:
            resp = await client.post(url, headers=headers, json=payload)
            resp.raise_for_status()
            data = resp.json()
            print(f"data: {data}")
            ai_text = (
                data.get("choices", [{}])[0]
                    .get("message", {})
                    .get("content", "")
            ) or "fracasadamentefracasado"
            print(ai_text)
    except Exception as e:
        print(f"Error en la llamada a OpenAI: {e}")
        ai_text = "fracasadamentefracasado"

    ai_text = ai_text.strip().lower()

    return {
        "ai_validation": ai_text,
        "success": ai_text == "exitosamenteexitoso",
    }




def mapeo_de_columnas(df: pd.DataFrame, mapeo: Dict[str, str]) -> pd.DataFrame:
    """
    Mapea los nombres de las columnas del DataFrame a nombres estandarizados.
    """
    df = df.rename(columns=mapeo)
    return df

def incluir_solo_columnas_necesarias(df: pd.DataFrame) -> pd.DataFrame:
    """
    Ajusta el DataFrame para que tenga exactamente las columnas de la tabla prospectos_manual:
    - Agrega las que falten (rellenadas con None).
    - Elimina las que no corresponden.
    - Devuelve el DataFrame con las columnas en el orden correcto.
    """
    columnas_tabla = obtener_todas_las_columnas_prospectos_manual().get("data", [])
    print(f"Columnas tabla: {columnas_tabla}")
    # Crear columnas faltantes con None
    for col in columnas_tabla:
        if col not in df.columns:
            df[col] = None
    # Dejar solo las columnas válidas y en el orden correcto
    df = df[columnas_tabla].copy()
    return df

def normalizar_columnas_obligatorias(df: pd.DataFrame) -> pd.DataFrame:
    """
    Normaliza las columnas obligatorias del DataFrame según reglas:
    - nombres, apellidos: minúsculas, sin espacios extra.
    - documento: solo dígitos.
    - correos: debe contener '@'.
    - telefono: solo dígitos, espacios y '+'.
    
    Diferencia entre:
      - "no ingresado": cuando está vacío o nulo.
      - "mal digitado": cuando hay datos pero no cumplen reglas.
    Todo se retorna en minúscula.
    """

    def limpiar_texto(valor: str) -> str:
        if not valor or pd.isna(valor) or str(valor).strip() == "":
            return "no ingresado"
        return str(valor).strip().lower()

    def limpiar_documento(valor: str) -> str:
        if not valor or pd.isna(valor) or str(valor).strip() == "":
            return "no ingresado"
        solo_numeros = re.sub(r"\D", "", str(valor))
        return solo_numeros if solo_numeros else "mal digitado"

    def limpiar_correo(valor: str) -> str:
        if not valor or pd.isna(valor) or str(valor).strip() == "":
            return "no ingresado"
        correo = str(valor).strip().lower()
        return correo if "@" in correo else "mal digitado"

    def limpiar_telefono(valor: str) -> str:
        if not valor or pd.isna(valor) or str(valor).strip() == "":
            return "no ingresado"
        telefono = re.sub(r"[^0-9 +]", "", str(valor))
        return telefono.strip().lower() if telefono.strip() else "mal digitado"

    # Normalización por columna
    if "nombres" in df.columns:
        df["nombres"] = df["nombres"].apply(limpiar_texto)
    if "apellidos" in df.columns:
        df["apellidos"] = df["apellidos"].apply(limpiar_texto)
    if "documento" in df.columns:
        df["documento"] = df["documento"].apply(limpiar_documento)
    if "correos" in df.columns:
        df["correos"] = df["correos"].apply(limpiar_correo)
    if "telefono" in df.columns:
        df["telefono"] = df["telefono"].apply(limpiar_telefono)

    return df


def normalizar_columnas_opcionales(df: pd.DataFrame) -> pd.DataFrame:
    """
    Normaliza las columnas opcionales del DataFrame:
    - Convierte a minúsculas y elimina espacios extra.
    - Si está vacío o solo contiene espacios → "no digitado".
    """
    columnas_opcionales = obtener_columnas_opcionales_prospectos_manual().get("data", [])

    def limpiar_opcional(valor: str) -> str:
        if not valor or pd.isna(valor) or str(valor).strip() == "":
            return "no digitado"
        return str(valor).strip().lower()

    for col in columnas_opcionales:
        if col in df.columns:
            df[col] = df[col].apply(limpiar_opcional)

    return df

def categorizacion_prospectos(df: pd.DataFrame) -> pd.DataFrame:
    """
    Agrega una columna 'categoria_prospecto' según la disponibilidad de contacto:
      - 'total'     → correo y teléfono válidos
      - 'parcial'   → al menos uno válido
      - 'descartado'→ ninguno válido
    Se consideran inválidos los valores 'no ingresado' o 'mal digitado'.
    """

    def es_valido(valor: str) -> bool:
        if not valor or pd.isna(valor):
            return False
        valor = str(valor).strip().lower()
        return valor not in ["no ingresado", "mal digitado"]

    categorias = []
    for _, fila in df.iterrows():
        correo_ok = es_valido(fila.get("correos", None))
        telefono_ok = es_valido(fila.get("telefono", None))

        if correo_ok and telefono_ok:
            categorias.append("total")
        elif correo_ok or telefono_ok:
            categorias.append("parcial")
        else:
            categorias.append("descartado")

    df = df.copy()
    df["categoria_prospecto"] = categorias
    return df


def insercion_masiva_dataframe(df, nombre_tabla, tamano_lote=5000):
    """
    Inserta un DataFrame en MySQL por lotes (chunks).
    Devuelve la cantidad de filas insertadas.
    """
    if df.empty:
        return 0

    # Preparar nombres de columnas y placeholders (%s)
    columnas = list(df.columns)
    columnas_sql = ", ".join(f"`{c}`" for c in columnas)
    placeholders = ", ".join(["%s"] * len(columnas))
    insert_sql = f"INSERT INTO `{nombre_tabla}` ({columnas_sql}) VALUES ({placeholders})"

    # Convertir el DataFrame en lista de tuplas
    datos = [tuple(fila) for fila in df.itertuples(index=False, name=None)]

    conn = get_connection()
    cur = conn.cursor()
    insertados = 0

    try:
        for i in range(0, len(datos), tamano_lote):
            lote = datos[i:i+tamano_lote]
            cur.executemany(insert_sql, lote)
            insertados += len(lote)

        conn.commit()
        return insertados
    except Exception:
        conn.rollback()
        raise
    finally:
        cur.close()
        conn.close()

def insertar_prospectos(df):
    """
    Inserta los prospectos en sus tablas correspondientes:
    - total/parcial → prospectos_manual
    - descartado → descartados_contactabilidad
    Devuelve un resumen con cuántos se insertaron en cada lugar.
    """
    resumen = {
        "success":False,
        "prospectos_manual_total": 0,
        "prospectos_manual_parcial": 0,
        "descartados_contactabilidad": 0
    }

    # --- Prospectos TOTAL ---
    df_total = df[df["categoria_prospecto"] == "total"].copy()
    if not df_total.empty:
        df_total["validacion_contactabilidad"] = 2
        columnas_prospectos = [
            "nombres","apellidos","tipo_documento","documento","correos","telefono","pais",
            "departamento","ciudad","localidad","barrio","direccion","fecha_cumpleanos",
            "genero","tipo_sangre","recomendado","nombre_recomendador","grupo_wp","grupo_wp1",
            "como_ayudar","pasion","intereses","autorizacion","fecha_registro",
            "estado_etapa","fecha_estado","validacion_contactabilidad"
        ]
        resumen["prospectos_manual_total"] = insercion_masiva_dataframe(df_total[columnas_prospectos], "prospectos_manual")

    # --- Prospectos PARCIAL ---
    df_parcial = df[df["categoria_prospecto"] == "parcial"].copy()
    if not df_parcial.empty:
        df_parcial["validacion_contactabilidad"] = 1
        columnas_prospectos = [
            "nombres","apellidos","tipo_documento","documento","correos","telefono","pais",
            "departamento","ciudad","localidad","barrio","direccion","fecha_cumpleanos",
            "genero","tipo_sangre","recomendado","nombre_recomendador","grupo_wp","grupo_wp1",
            "como_ayudar","pasion","intereses","autorizacion","fecha_registro",
            "estado_etapa","fecha_estado","validacion_contactabilidad"
        ]
        resumen["prospectos_manual_parcial"] = insercion_masiva_dataframe(df_parcial[columnas_prospectos], "prospectos_manual")

    # --- Prospectos DESCARTADOS ---
    df_desc = df[df["categoria_prospecto"] == "descartado"].copy()
    if not df_desc.empty:
        columnas_descartados = [
            "nombres","apellidos","tipo_documento","documento","correos","telefono","pais",
            "departamento","ciudad","localidad","barrio","direccion","fecha_cumpleanos",
            "genero","tipo_sangre","recomendado","nombre_recomendador","grupo_wp","grupo_wp1",
            "como_ayudar","pasion","intereses","autorizacion","fecha_registro",
            "estado_etapa","fecha_estado"
        ]
        resumen["descartados_contactabilidad"] = insercion_masiva_dataframe(df_desc[columnas_descartados], "descartados_contactabilidad")
    resumen["success"] = True
    return resumen