__( 'Missing hot sellers', 'ai-eshop-optimizer' ), 'description' => sprintf( __( 'SKUs selling well in the PHYSICAL stores that are NOT live in the eshop yet — auto-imported draft products still missing image/description/categories/brand, or products absent online entirely. Ranked by physical net turnover so you publish the highest-impact ones first. Each item gives physical units + net %s turnover over the most recent valid data period, the product post_status, which attributes are missing, needs_creation (no Woo product), and the product_id for the edit link. Reads wp_aieo_dmm_catalog_gap_candidates.', 'ai-eshop-optimizer' ), $sym ), 'category' => \AIEO\Claude\Bootstrap::CATEGORY, 'input_schema' => array( 'default' => array(), 'type' => 'object', 'properties' => array( 'needs_creation_only' => array( 'type' => 'boolean', 'default' => false, 'description' => 'Only SKUs with NO Woo product at all (must be created from scratch), excluding the draft-completion cases.', ), 'limit' => array( 'type' => 'integer', 'minimum' => 1, 'maximum' => 200, 'default' => 50, ), ), ), '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' ); } public static function execute( $input ) { global $wpdb; $input = is_array( $input ) ? $input : array(); $nc = ! empty( $input['needs_creation_only'] ); $limit = isset( $input['limit'] ) ? max( 1, min( 200, (int) $input['limit'] ) ) : 50; $gap = $wpdb->prefix . 'aieo_dmm_catalog_gap_candidates'; if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $gap ) ) !== $gap ) { return new \WP_Error( 'aieo_gap_missing', __( 'Run the "Stock recovery" job under Catalog Sync first.', 'ai-eshop-optimizer' ) ); } $sum = $wpdb->get_row( "SELECT COUNT(*) total, SUM(needs_creation=1) needs_creation, ROUND(SUM(phys_turnover),2) turnover_at_stake FROM `{$gap}` WHERE status = 'pending'", ARRAY_A ); $where = "status = 'pending'" . ( $nc ? ' AND needs_creation = 1' : '' ); $rows = $wpdb->get_results( "SELECT product_id, sku, name, post_status, phys_units, phys_sale_days, last_phys_sale_date, phys_turnover, missing_image, missing_desc, missing_cats, missing_brand, needs_creation FROM `{$gap}` WHERE {$where} ORDER BY priority_score DESC, phys_units_recent DESC LIMIT {$limit}", ARRAY_A ); return array( 'summary' => array( 'missing_hot_sellers' => (int) ( $sum['total'] ?? 0 ), 'need_creation' => (int) ( $sum['needs_creation'] ?? 0 ), 'net_turnover_at_stake' => (float) ( $sum['turnover_at_stake'] ?? 0 ), ), 'note' => __( 'Ranked by physical net turnover (ex-VAT) over the most recent valid ERP data period. needs_creation=1 = no Woo product; otherwise it is a draft/incomplete product (see post_status + missing_* flags) to complete and publish.', 'ai-eshop-optimizer' ), 'count' => count( (array) $rows ), 'products' => array_values( (array) $rows ), ); } }