from cnxpdo import get_connection

def get_lista_tablas_tipo_contacto(tipo_contacto:str):
    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  = %s"

        cursor.execute(query,(tipo_contacto,))
        list_tables = cursor.fetchall()


        cursor.close()
        conexionBD.close()
        return {
            "success": 1,
            "message": "tablas obtenidas correctamente",
            "data": list_tables
        }
    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"
            }
        
        tipo_contacto = data.get("tipo_contacto")
        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 
        if tipo_contacto == 'Prospectos':
            query = f"""
                SELECT 
                    t.*
                FROM 
                    {tabla} t
                WHERE t.estado_etapa = 1
                ORDER BY 
                    t.id DESC
                LIMIT %s OFFSET %s
            """
        else:
            query = f"""
                SELECT 
                    t.*
                FROM 
                    {tabla} t
                ORDER BY 
                    t.id DESC
                LIMIT %s OFFSET %s
            """

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

        # Consulta de conteo con la misma condición WHERE t.estado_etapa = 1
        if tipo_contacto == 'Prospectos':
            count_query = f"""
            SELECT COUNT(*) AS total
            FROM {tabla} t
            WHERE t.estado_etapa = 1
            """
        else:
            count_query = f"""
                SELECT COUNT(*) AS total
                FROM {tabla} t
            """

        cursor.execute(count_query)
        total = cursor.fetchone()['total']
        total_pages = (total // page_size) + (1 if total % page_size != 0 else 0)


        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 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 filtrado_dinamico_busqueda_campos(data:dict):
    try:
        conexionBD = get_connection()
        if conexionBD is None:
            return {
                "success": 0,
                "message": "Error de conexión"
            }
        
        tipo_contacto = data.get("tipo_contacto")
        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"
            }
        
        if not data.get("campos") or not data.get("campos") :
             return {
                "success": False,
                "message": "No se encontraron campos para realizar la busqueda"
            }

        cursor = conexionBD.cursor(dictionary=True)

        where_dinamico = creacion_where_dinamico(data.get("campos"))
        # Consulta con el filtro 
        if tipo_contacto == 'Prospectos':
            where_dinamico += " AND t.estado_etapa = 1"
            query = f"""
                SELECT 
                    t.*
                FROM
                    {tabla} t
                {where_dinamico}
                ORDER BY 
                    t.id DESC
                LIMIT %s OFFSET %s
            """
        else:
            query = f"""
                SELECT 
                    t.*
                FROM 
                    {tabla} t
                {where_dinamico}
                ORDER BY 
                    t.id DESC
                LIMIT %s OFFSET %s
            """

        print("query1",query) 
        cursor.execute(query, (page_size, offset))
        registros = cursor.fetchall()

        where_dinamico = ""
        where_dinamico = creacion_where_dinamico(data.get("campos"))

        # Consulta de conteo con la misma condición WHERE t.estado_etapa = 1
        if tipo_contacto == 'Prospectos':
            where_dinamico += " AND t.estado_etapa = 1"

            count_query = f"""
            SELECT COUNT(*) AS total
            FROM {tabla} t
            {where_dinamico}
            """
        else:
            count_query = f"""
                SELECT COUNT(*) AS total
                FROM {tabla} t
                {where_dinamico}
            """
        print("query1",count_query) 
        cursor.execute(count_query)
        total = cursor.fetchone()['total']
        total_pages = (total // page_size) + (1 if total % page_size != 0 else 0)
        

        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 creacion_where_dinamico(campos):
        # Construccion dinamica del where
        where_dinamico = ""
        condiciones = []

        for campo, valor in  campos.items():
            if valor is not None:
                condiciones.append(f"{campo} LIKE '%{valor}%'")

        if condiciones:
            where_dinamico += " WHERE t." + " AND t.".join(condiciones)

        return where_dinamico
    

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

        cursor = conexionBD.cursor(dictionary=True)
        query = "SELECT * FROM estados_etapas"
        cursor.execute(query)
        estados_etapas = cursor.fetchall()
      
        cursor.close()
        conexionBD.close()

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