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)
        
        # Si page_size es 0 o muy grande, traer todos los datos
        traer_todos_los_datos = page_size == 0 or page_size > 10000

        # Lista blanca de tablas permitidas
        tablas_validas = traer_tablas_permitidas()
        if len(tablas_validas) == 0:
            return {
                "success": False,
                "message": "No se encontraron tablas permitidas",
                "data": [],
                "totalRecords": 0,
                "totalPages": 0,
                "currentPage": 0
            }

        if tabla not in tablas_validas:
            return {
                "success": False,
                "message": "Tabla no permitida",
                "data": [],
                "totalRecords": 0,
                "totalPages": 0,
                "currentPage": 0
            }

        cursor = conexionBD.cursor(dictionary=True)

        # Consulta base - TEMPORAL: sin filtro para debug
        base_query = f"""
            SELECT 
                t.*
            FROM 
                {tabla} t
            ORDER BY 
                t.id DESC
        """

        if traer_todos_los_datos:
            # Traer todos los datos sin paginación
            cursor.execute(base_query)
            registros = cursor.fetchall()
            total_records = len(registros)
            
            return {
                "success": True,
                "message": "Datos obtenidos correctamente",
                "data": registros,
                "totalRecords": total_records,
                "totalPages": 1,
                "currentPage": 1
            }
        else:
            # Paginación normal
            offset = (page - 1) * page_size
            paginated_query = base_query + " LIMIT %s OFFSET %s"
            
            cursor.execute(paginated_query, (page_size, offset))
            registros = cursor.fetchall()
            
            # Consulta de conteo
            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)}",
            "data": [],
            "totalRecords": 0,
            "totalPages": 0,
            "currentPage": 0
        }
    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)}"
        }