Unused Media Files Report

add_action( 'admin_menu', function () {
    add_media_page( 'Unused Files', 'Unused Files', 'upload_files', 'wpide-unused-media', function () {
        global $wpdb;

        $paged    = isset( $_GET['paged'] ) ? max( 1, absint( $_GET['paged'] ) ) : 1;
        $per_page = 40;

        // Unattached files that are not a featured image anywhere.
        $candidates = $wpdb->get_results( $wpdb->prepare(
            "SELECT p.ID, p.post_title, p.post_date FROM {$wpdb->posts} p
            WHERE p.post_type = 'attachment' AND p.post_parent = 0
            AND NOT EXISTS ( SELECT 1 FROM {$wpdb->postmeta} m WHERE m.meta_key = '_thumbnail_id' AND m.meta_value = p.ID )
            ORDER BY p.ID DESC LIMIT %d OFFSET %d",
            $per_page,
            ( $paged - 1 ) * $per_page
        ) );

        $unused = array();
        foreach ( $candidates as $file ) {
            $name = preg_replace( '/(-scaled|-rotated)?\.[a-z0-9]+$/i', '', wp_basename( (string) get_attached_file( $file->ID ) ) );
            $used = $name && $wpdb->get_var( $wpdb->prepare(
                "SELECT ID FROM {$wpdb->posts} WHERE post_type NOT IN ('revision','attachment') AND post_status <> 'trash' AND post_content LIKE %s LIMIT 1",
                '%' . $wpdb->esc_like( $name ) . '%'
            ) );
            if ( ! $used && (int) get_option( 'site_icon' ) !== (int) $file->ID && (int) get_theme_mod( 'custom_logo' ) !== (int) $file->ID ) {
                $unused[] = $file;
            }
        }

        echo '<div class="wrap"><h1>Unused Files</h1><p>Files below are not attached, not a featured image, not the logo or site icon, and not mentioned in any content.</p>';
        if ( ! $unused ) {
            echo '<p>No unused files on this page.</p>';
        } else {
            echo '<table class="widefat striped"><thead><tr><th style="width:70px">File</th><th>Title</th><th>Uploaded</th></tr></thead><tbody>';
            foreach ( $unused as $file ) {
                printf(
                    '<tr><td>%s</td><td><a href="%s">%s</a></td><td>%s</td></tr>',
                    wp_get_attachment_image( $file->ID, array( 60, 60 ), true ),
                    esc_url( get_edit_post_link( $file->ID ) ),
                    esc_html( $file->post_title ),
                    esc_html( mysql2date( get_option( 'date_format' ), $file->post_date ) )
                );
            }
            echo '</tbody></table>';
        }
        if ( count( $candidates ) === $per_page ) {
            printf( '<p><a class="button" href="%s">Next page</a></p>', esc_url( add_query_arg( 'paged', $paged + 1 ) ) );
        }
        echo '</div>';
    } );
} );