<?php

global $wpdb, $bp;

$blog_ids = $wpdb->get_col( "SELECT DISTINCT blog_id FROM {$bp->blogs->table_name_blogmeta}" );

$has_dupes = [];
foreach ( $blog_ids as $blog_id ) {
	$blog_metas = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$bp->blogs->table_name_blogmeta} WHERE blog_id = %d", $blog_id ) );

	$blog_meta_ids = [];
	foreach ( $blog_metas as $blog_meta ) {
		$key   = $blog_meta->meta_key;
		$value = $blog_meta->meta_value;
		if ( ! isset( $blog_meta_ids[ $key ][ $value ] ) ) {
			$blog_meta_ids[ $key ][ $value ] = 0;
		}

		$blog_meta_ids[ $key ][ $value ]++;
	}

	foreach ( $blog_meta_ids as $the_key => $values ) {
		foreach ( $values as $value => $count ) {
			if ( $count > 1 ) {
				$has_dupes[ $blog_id ][] = $the_key;
				break;
			}
		}
	}
}

foreach ( $has_dupes as $blog_id => $dupe_keys ) {
	foreach ( $dupe_keys as $dupe_key ) {
		$dupe_ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$bp->blogs->table_name_blogmeta} WHERE blog_id = %d and meta_key = %s LIMIT 1, 1000", $blog_id, $dupe_key ) );

		$dupe_ids_sql = implode( ',', array_map( 'intval', $dupe_ids ) );

		$wpdb->query( "DELETE FROM {$bp->blogs->table_name_blogmeta} WHERE id in ({$dupe_ids_sql})" );
	}
}