Product Data Health Report

add_action( 'admin_menu', function () {
    add_submenu_page( 'edit.php?post_type=product', 'Product data health', 'Data health', 'edit_products', 'wpide-product-health', function () {
        global $wpdb;

        $uncategorized = (int) get_option( 'default_product_cat' );
        $checks        = array(
            'No featured image'     => "SELECT p.ID FROM {$wpdb->posts} p LEFT JOIN {$wpdb->postmeta} m ON m.post_id = p.ID AND m.meta_key = '_thumbnail_id' WHERE p.post_type = 'product' AND p.post_status = 'publish' AND ( m.meta_value IS NULL OR m.meta_value = '' OR m.meta_value = '0' ) LIMIT 100",
            'No SKU'                => "SELECT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->wc_product_meta_lookup} l ON l.product_id = p.ID WHERE p.post_type = 'product' AND p.post_status = 'publish' AND ( l.sku IS NULL OR l.sku = '' ) LIMIT 100",
            'No price'              => "SELECT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->wc_product_meta_lookup} l ON l.product_id = p.ID WHERE p.post_type = 'product' AND p.post_status = 'publish' AND l.max_price IS NULL LIMIT 100",
            'No short description'  => "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'product' AND post_status = 'publish' AND post_excerpt = '' LIMIT 100",
            'Only in Uncategorized' => $uncategorized ? $wpdb->prepare( "SELECT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->term_relationships} r ON r.object_id = p.ID INNER JOIN {$wpdb->term_taxonomy} t ON t.term_taxonomy_id = r.term_taxonomy_id AND t.taxonomy = 'product_cat' WHERE p.post_type = 'product' AND p.post_status = 'publish' GROUP BY p.ID HAVING COUNT(*) = 1 AND MAX(t.term_id) = %d LIMIT 100", $uncategorized ) : '',
        );

        echo '<div class="wrap"><h1>' . esc_html( 'Product data health' ) . '</h1>';

        foreach ( $checks as $label => $sql ) {
            $ids = $sql ? array_map( 'intval', $wpdb->get_col( $sql ) ) : array(); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Static queries.

            printf( '<h2>%1$s <span class="count">(%2$s)</span></h2>', esc_html( $label ), esc_html( count( $ids ) >= 100 ? '100+' : (string) count( $ids ) ) );

            if ( ! $ids ) {
                echo '<p>' . esc_html( 'All good.' ) . '</p>';
                continue;
            }

            _prime_post_caches( $ids, false, false );
            echo '<ul style="columns:2;max-width:900px">';

            foreach ( $ids as $id ) {
                printf( '<li><a href="%1$s">%2$s</a></li>', esc_url( get_edit_post_link( $id ) ), esc_html( get_the_title( $id ) ) );
            }

            echo '</ul>';
        }

        echo '</div>';
    } );
} );