wpdb = $wpdb; $this->job_products_table = $wpdb->prefix . 'aipt_job_products'; $this->jobs_table = $wpdb->prefix . 'aipt_automation_jobs'; } /** * Build pool for a job using INSERT...SELECT with keyset pagination. * * - Uses keyset pagination (WHERE ID > last_id) for O(1) performance vs OFFSET's O(n) * - Ensures no duplicates via UNIQUE(job_id, product_id) using INSERT IGNORE * * @param int $job_id Job ID * @param array $filter Product filter configuration (same shape as AutomationJobManager::getFilteredProductIds filters) * @param int $chunk_size Number of products per INSERT chunk * @param int $max_chunks Optional safety cap on chunks per call (prevents long requests) * @return array{inserted:int,total:int,blocked?:array} 'blocked' is the * JobEntitlementGuard denial when a saved paid filter cannot be * honored; nothing is inserted and the caller pauses the job. */ public function buildPool($job_id, array $filter, $chunk_size = 2000, $max_chunks = 20, bool $skipProcessed = false, array $targetedTypes = []) { $job_id = (int) $job_id; $chunk_size = max(100, (int) $chunk_size); $max_chunks = max(1, (int) $max_chunks); $blocked = JobEntitlementGuard::filterBlock($filter); if (null !== $blocked) { return ['inserted' => 0, 'total' => 0, 'blocked' => $blocked]; } $base_select_sql = $this->buildFilteredProductsSelect($filter, $skipProcessed, $targetedTypes); if (!$base_select_sql) { return ['inserted' => 0, 'total' => 0]; } $total = $skipProcessed ? 0 : $this->getCachedTotal($job_id); if ($total === 0) { $count_sql = "SELECT COUNT(1) FROM (" . $base_select_sql . ") as src"; $total = (int) $this->wpdb->get_var($count_sql); } $last_id = $this->getPoolCursor($job_id); $inserted = 0; $chunks_done = 0; while ($chunks_done < $max_chunks) { $sql = $this->wpdb->prepare( "INSERT IGNORE INTO {$this->job_products_table} (job_id, product_id, status, created_at) SELECT %d, src.ID, 'pending', %s FROM (" . $base_select_sql . ") as src WHERE src.ID > %d ORDER BY src.ID ASC LIMIT %d", $job_id, current_time('mysql'), $last_id, $chunk_size ); $res = $this->wpdb->query($sql); if ($res === false) { break; } $chunk_inserted = (int) $res; $inserted += $chunk_inserted; $chunks_done++; if ($chunk_inserted === 0) { break; } $last_id = $this->getPoolCursor($job_id); if ($last_id === 0) { break; } } return ['inserted' => $inserted, 'total' => $total]; } /** * Return the current keyset cursor: MAX(product_id) already in the pool for this job. * * Called at the START of buildPool() (B-02 fix) and after each successful chunk insert * to advance the cursor for the next iteration. Returns 0 when the pool is empty. * * @param int $job_id * @return int */ private function getPoolCursor($job_id) { $cursor = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT MAX(product_id) FROM {$this->job_products_table} WHERE job_id = %d", $job_id ) ); return (int) $cursor; } /** * Read total_products from the job record (P-04 cache). * * Returns 0 when not yet stored, which signals buildPool() to run the COUNT query and * let AutomationJobManager persist the result. On subsequent ticks the stored value * is returned directly, eliminating the expensive COUNT(*) subquery. * * @param int $job_id * @return int */ private function getCachedTotal($job_id) { $total = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT total_products FROM {$this->jobs_table} WHERE id = %d", $job_id ) ); return (int) $total; } /** * Build the SELECT SQL for product IDs based on filter * Returns a SQL string that selects a single column "ID" of product IDs. * * Premium filter augmentation (categories, tags, stock status) is applied in * buildPool() via the 'aipt_automation_pool_filter_query' filter hook (A-06). * * @param array $filter * @return string|null */ private function buildFilteredProductsSelect(array $filter, bool $skipProcessed = false, array $targetedTypes = []) { global $wpdb; $conditions = []; $joins = []; $conditions[] = "p.post_type = 'product'"; $conditions[] = $this->wpdb->prepare('p.post_status = %s', JobEntitlementGuard::productStatus($filter)); if ( PremiumClassGuard::exists( '\\AIPT\\Premium\\AutomationJobs\\Services\\ProductFilterAugmenter' ) ) { \AIPT\Premium\AutomationJobs\Services\ProductFilterAugmenter::addPoolBuilderFilters( $joins, $conditions, $filter ); } elseif ( self::hasPaidRestriction( $filter ) ) { $conditions[] = '1=0'; } if (!empty($filter['priceRange']['min']) || !empty($filter['priceRange']['max'])) { $joins[] = "INNER JOIN {$wpdb->postmeta} pm_price ON pm_price.post_id = p.ID"; $conditions[] = "pm_price.meta_key = '_price'"; if (!empty($filter['priceRange']['min'])) { $conditions[] = $this->wpdb->prepare("CAST(pm_price.meta_value AS DECIMAL(10,2)) >= %f", (float) $filter['priceRange']['min']); } if (!empty($filter['priceRange']['max'])) { $conditions[] = $this->wpdb->prepare("CAST(pm_price.meta_value AS DECIMAL(10,2)) <= %f", (float) $filter['priceRange']['max']); } } if (ProductIdListFilter::wasRequested($filter['includeIds'] ?? null)) { $include_ids = ProductIdListFilter::parse($filter['includeIds']); if (!empty($include_ids)) { $conditions[] = "p.ID IN (" . implode(',', $include_ids) . ")"; } else { $conditions[] = '1=0'; } } if (ProductIdListFilter::wasRequested($filter['excludeIds'] ?? null)) { $exclude_ids = ProductIdListFilter::parse($filter['excludeIds']); if (!empty($exclude_ids)) { $conditions[] = "p.ID NOT IN (" . implode(',', $exclude_ids) . ")"; } else { $conditions[] = '1=0'; } } $description_filter_conditions = $this->getDescriptionFilterConditions($filter); if (!empty($description_filter_conditions)) { $conditions[] = '(' . implode(' OR ', $description_filter_conditions) . ')'; } if ($skipProcessed) { $skip_condition = $this->buildAiProcessedSkipPredicate('p', $targetedTypes); if ('' !== $skip_condition) { $conditions[] = $skip_condition; } } $sql = "SELECT DISTINCT p.ID FROM {$wpdb->posts} p"; if (!empty($joins)) { $sql .= ' ' . implode(' ', $joins); } if (!empty($conditions)) { $sql .= ' WHERE ' . implode(' AND ', $conditions); } return $sql; } /** * Whether a filter carries a paid category, tag, stock or product status * restriction. * * Mirrors ProductFilterAugmenter's own presence checks so both sides agree * on what "restricted" means. * * @param array $filter Product filter. * @return bool */ private static function hasPaidRestriction(array $filter): bool { return (!empty($filter['categories']) && is_array($filter['categories'])) || (!empty($filter['tags']) && is_array($filter['tags'])) || (!empty($filter['stockStatus']) && 'all' !== $filter['stockStatus']) || JobEntitlementGuard::DEFAULT_PRODUCT_STATUS !== JobEntitlementGuard::productStatus($filter); } private function getDescriptionFilterConditions(array $filter): array { $raw_filters = []; if (isset($filter['descriptionFilters'])) { $raw_filters = is_array($filter['descriptionFilters']) ? $filter['descriptionFilters'] : explode(',', (string) $filter['descriptionFilters']); } elseif (isset($filter['descriptionFilter'])) { $raw_filters = is_array($filter['descriptionFilter']) ? $filter['descriptionFilter'] : explode(',', (string) $filter['descriptionFilter']); } $conditions = []; foreach ($raw_filters as $raw_filter) { $condition = ProductFilterBuilder::getContentFilterWherePart(sanitize_text_field((string) $raw_filter), 'p'); if ($condition !== '') { $conditions[] = '(' . $condition . ')'; } } return $conditions; } private function buildAiProcessedSkipPredicate($product_alias, array $targetedTypes) { if (empty($targetedTypes)) { return $this->wpdb->prepare( "NOT EXISTS (SELECT 1 FROM {$this->wpdb->postmeta} aipt_ai_processed_pm WHERE aipt_ai_processed_pm.post_id = {$product_alias}.ID AND aipt_ai_processed_pm.meta_key = %s)", \AIPT\Core\ContentMarking\MetaKeys::ROLLUP ); } $conditions = []; foreach ($targetedTypes as $targeted_type) { $meta_key = \AIPT\Core\ContentMarking\MetaKeys::keyForType((string) $targeted_type); if (null === $meta_key) { continue; } $conditions[] = $this->wpdb->prepare( "NOT EXISTS (SELECT 1 FROM {$this->wpdb->postmeta} aipt_ai_processed_pm WHERE aipt_ai_processed_pm.post_id = {$product_alias}.ID AND aipt_ai_processed_pm.meta_key = %s)", $meta_key ); } if (empty($conditions)) { return ''; } return '( ' . implode(' OR ', $conditions) . ' )'; } }