__( 'Restock forecast', 'ai-eshop-optimizer' ), 'description' => __( 'Which products need restocking. Returns items already out of stock (with the estimated lost-sales cost) plus items the self-tuning predictor projects will run out within `within_days`, ranked by urgency. Each item includes the chosen daily sales rate, days until run-out, projected zero-stock date, a suggested reorder quantity, and the model\'s reliability_score / prediction_confidence (0–1) and uses_parent_signal flag. Choose grain="variation" for per-SKU restocking (default) or grain="parent" for whole-product planning. Reads wp_aieo_sales_predictor, rebuilt by the Product/Orders analytics "Sales / out-of-stock predictor" job.', '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( 'grain' => array( 'type' => 'string', 'enum' => array( 'variation', 'parent' ), 'default' => 'variation', 'description' => 'variation = per-SKU (default); parent = whole product (stock summed across its variations).', ), 'within_days' => array( 'type' => 'integer', 'minimum' => 0, 'maximum' => 365, 'default' => 30, 'description' => 'Include in-stock items projected to run out within this many days. 0 = only items already out of stock.', ), 'min_confidence' => array( 'type' => 'number', 'minimum' => 0, 'maximum' => 1, 'default' => 0, 'description' => 'Drop items whose prediction_confidence is below this (0–1). Leave 0 to see everything and judge per row.', ), 'limit' => array( 'type' => 'integer', 'minimum' => 1, 'maximum' => 200, 'default' => 50, ), ), ); } public static function execute( $input ) { global $wpdb; $input = is_array( $input ) ? $input : array(); $grain = ( isset( $input['grain'] ) && 'parent' === $input['grain'] ) ? 'parent' : 'variation'; $within_days = isset( $input['within_days'] ) ? max( 0, min( 365, (int) $input['within_days'] ) ) : 30; $min_confidence = isset( $input['min_confidence'] ) ? max( 0, min( 1, (float) $input['min_confidence'] ) ) : 0.0; $limit = isset( $input['limit'] ) ? max( 1, min( 200, (int) $input['limit'] ) ) : 50; $sp = $wpdb->prefix . 'aieo_sales_predictor'; $v = $wpdb->prefix . 'aieo_core_product_vitals'; if ( (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $sp ) ) !== $sp ) { return new \WP_Error( 'aieo_predictor_missing', __( 'The sales predictor table does not exist yet. Run the "Sales / out-of-stock predictor" job under Tools → Product/Orders analytics.', 'ai-eshop-optimizer' ) ); } $anchor = $wpdb->get_var( $wpdb->prepare( "SELECT MAX(prediction_date) FROM `{$sp}` WHERE grain = %s", $grain ) ); if ( empty( $anchor ) ) { return array( 'grain' => $grain, 'message' => __( 'No predictor rows for this grain yet — run the predictor job first.', 'ai-eshop-optimizer' ), 'products' => array(), ); } $summary = $wpdb->get_row( $wpdb->prepare( "SELECT SUM(current_stock < 1) AS out_of_stock_now, SUM(current_stock >= 1 AND chosen_zero_stock_date IS NOT NULL AND chosen_zero_stock_date <= %s + INTERVAL %d DAY) AS projected_runout, ROUND(SUM(CASE WHEN current_stock < 1 AND chosen_estimator_value IS NOT NULL THEN chosen_estimator_value * out_of_stock_days * price ELSE 0 END), 2) AS est_oos_cost FROM `{$sp}` WHERE grain = %s AND prediction_date = %s", $anchor, $within_days, $grain, $anchor ), ARRAY_A ); $rows = $wpdb->get_results( $wpdb->prepare( "SELECT sp.product_id, sp.parent_product_id, v.product_name, v.brand, v.sku, sp.current_stock, sp.out_of_stock_days, sp.chosen_items_per_day, sp.chosen_days_until_runout, sp.chosen_zero_stock_date, sp.reorder_quantity, sp.reliability_score, sp.prediction_confidence, sp.uses_parent_signal, ROUND(CASE WHEN sp.current_stock < 1 AND sp.chosen_estimator_value IS NOT NULL THEN sp.chosen_estimator_value * sp.out_of_stock_days * sp.price ELSE NULL END, 2) AS estimated_oos_cost FROM `{$sp}` sp LEFT JOIN `{$v}` v ON v.product_id = sp.product_id WHERE sp.grain = %s AND sp.prediction_date = %s AND COALESCE(sp.prediction_confidence, 0) >= %f AND ( sp.current_stock < 1 OR ( sp.chosen_zero_stock_date IS NOT NULL AND sp.chosen_zero_stock_date <= %s + INTERVAL %d DAY ) ) ORDER BY (sp.current_stock < 1) DESC, sp.chosen_zero_stock_date ASC, sp.chosen_items_per_day DESC LIMIT %d", $grain, $anchor, $min_confidence, $anchor, $within_days, $limit ), ARRAY_A ); return array( 'grain' => $grain, 'anchor_date' => $anchor, 'within_days' => $within_days, 'summary' => array( 'out_of_stock_now' => isset( $summary['out_of_stock_now'] ) ? (int) $summary['out_of_stock_now'] : 0, 'projected_runout_in_window' => isset( $summary['projected_runout'] ) ? (int) $summary['projected_runout'] : 0, 'estimated_out_of_stock_cost' => isset( $summary['est_oos_cost'] ) ? (float) $summary['est_oos_cost'] : 0.0, ), 'note' => __( 'reliability_score & prediction_confidence are 0–1; uses_parent_signal=1 means a thin variation\'s rate was blended from its parent (treat as indicative). anchor_date may trail today if the store has been quiet.', 'ai-eshop-optimizer' ), 'count' => count( (array) $rows ), 'products' => array_values( (array) $rows ), ); } }