__( 'Get rating leaderboard', 'ai-eshop-optimizer' ), 'description' => __( 'Return the top-N highest-rated and bottom-N lowest-rated products (with vote counts) from `wp_aieo_rating_stats`. `min_votes` filters out single-vote noise.', 'ai-eshop-optimizer' ), 'category' => \AIEO\Claude\Bootstrap::CATEGORY, 'input_schema' => self::input_schema(), 'output_schema' => array( 'type' => 'object' ), 'execute_callback' => array( __CLASS__, 'execute' ), 'permission_callback' => array( __CLASS__, 'permission_check' ), 'meta' => array( 'annotations' => array( 'readonly' => true, 'idempotent' => true, ), 'show_in_rest' => true, ), ) ); } public static function permission_check( $input = null ) { unset( $input ); return current_user_can( 'manage_woocommerce' ); } private static function input_schema() { return array( 'default' => array(), 'type' => 'object', 'properties' => array( 'n' => array( 'type' => 'integer', 'minimum' => 1, 'maximum' => 50, 'default' => 10, ), 'min_votes' => array( 'type' => 'integer', 'minimum' => 1, 'maximum' => 100, 'default' => 3, ), ), ); } public static function execute( $input ) { global $wpdb; $input = is_array( $input ) ? $input : array(); $n = isset( $input['n'] ) ? max( 1, min( 50, (int) $input['n'] ) ) : 10; $min_votes = isset( $input['min_votes'] ) ? max( 1, (int) $input['min_votes'] ) : 3; $table = $wpdb->prefix . 'aieo_rating_stats'; $exists = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s", $table ) ); if ( ! $exists ) { return array( 'top' => array(), 'bottom' => array(), 'note' => __( 'wp_aieo_rating_stats not installed.', 'ai-eshop-optimizer' ) ); } $top = $wpdb->get_results( $wpdb->prepare( "SELECT product_id, count_total AS votes, ROUND(sum_total / GREATEST(count_total, 1), 2) AS avg_rating FROM {$table} WHERE object_type = 'product' AND count_total >= %d ORDER BY (sum_total / GREATEST(count_total, 1)) DESC, count_total DESC LIMIT %d", $min_votes, $n ), ARRAY_A ); $bottom = $wpdb->get_results( $wpdb->prepare( "SELECT product_id, count_total AS votes, ROUND(sum_total / GREATEST(count_total, 1), 2) AS avg_rating FROM {$table} WHERE object_type = 'product' AND count_total >= %d ORDER BY (sum_total / GREATEST(count_total, 1)) ASC, count_total DESC LIMIT %d", $min_votes, $n ), ARRAY_A ); $enrich = static function ( $rows ) { $out = array(); foreach ( (array) $rows as $r ) { $pid = (int) $r['product_id']; $out[] = array( 'product_id' => $pid, 'title' => get_the_title( $pid ), 'permalink' => get_permalink( $pid ), 'votes' => (int) $r['votes'], 'avg_rating' => (float) $r['avg_rating'], ); } return $out; }; return array( 'min_votes' => $min_votes, 'n' => $n, 'top' => $enrich( $top ), 'bottom' => $enrich( $bottom ), ); } }