wpdb = $wpdb; $this->jobs_table = $wpdb->prefix . 'aipt_automation_jobs'; $this->job_products_table = $wpdb->prefix . 'aipt_job_products'; } /** * Create a new job * * @param AutomationJob $job Job to create * @return int|false Job ID on success, false on failure * @throws \Exception On validation or database errors */ public function createJob(AutomationJob $job) { $validation_errors = $job->validate(); if (!empty($validation_errors)) { throw new \Exception('Job validation failed: ' . wp_json_encode($validation_errors)); } $job->created_at = current_time('mysql'); $job->updated_at = current_time('mysql'); $job->status = 'preparing'; $job->progress = 0; $data = $job->toArray(); unset($data['id']); $result = $this->wpdb->insert( $this->jobs_table, $data, [ '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s', '%s' ] ); if ($result === false) { throw new \Exception('Failed to create job: ' . esc_html($this->wpdb->last_error)); } $job_id = $this->wpdb->insert_id; try { \AIPT\Core\Services\UsageTracker::record_milestone( \AIPT\Core\Services\UsageTracker::MILESTONE_FIRST_AUTOMATION ); } catch (\Throwable $t) { } $this->buildPoolAndSetStatus((int) $job_id, $job); return $job_id; } /** * Rebuild a job's product pool from its saved filter after an edit * changed that filter: the pool built from the old filter is dropped and * built again exactly like a new job's, product count and status * included. Only for a job that has not started (the caller checks). * * @param AutomationJob $job Saved job. * @return void */ public function rebuildPool(AutomationJob $job): void { $this->resetJobPool((int) $job->id); $this->buildPoolAndSetStatus((int) $job->id, $job); } /** * Build a job's product pool (a few chunks now, the rest on background * ticks) and move the job to 'ready', 'preparing' or 'completed' to * match, or pause it when its saved filter cannot be honored. * * @param int $job_id Job ID. * @param AutomationJob $job Job whose filter and settings drive the pool. * @return void */ private function buildPoolAndSetStatus(int $job_id, AutomationJob $job): void { try { $pool_builder = new \AIPT\Api\AutomationJobs\PoolBuilder(); $filter = is_array($job->product_filter) ? $job->product_filter : []; $pool_options = AutomationJob::getPoolBuildOptions( (string) $job->tool_type, is_array($job->settings) ? $job->settings : [] ); $pool_result = $pool_builder->buildPool( $job_id, $filter, 2000, 5, $pool_options['skip_processed'], $pool_options['targeted_types'] ); if (!empty($pool_result['blocked'])) { $this->wpdb->update( $this->jobs_table, [ 'status' => 'paused', 'error_message' => (string) $pool_result['blocked']['message'], 'total_products' => 0, 'updated_at' => current_time('mysql'), ], ['id' => $job_id], ['%s', '%s', '%d', '%s'], ['%d'] ); return; } $target_total = isset($pool_result['total']) ? (int) $pool_result['total'] : 0; $current_total = (int) $this->wpdb->get_var( $this->wpdb->prepare("SELECT COUNT(*) FROM {$this->job_products_table} WHERE job_id = %d", $job_id) ); $update_data = [ 'total_products' => $target_total, 'updated_at' => current_time('mysql') ]; $update_formats = ['%d','%s']; if ($target_total === 0) { $update_data['status'] = 'completed'; $update_data['progress'] = 100; $update_data['completed_at'] = current_time('mysql'); $update_formats = array_merge($update_formats, ['%s','%d','%s']); } elseif ($current_total >= $target_total) { $update_data['status'] = 'ready'; $update_formats[] = '%s'; } else { $update_data['status'] = 'preparing'; $update_formats[] = '%s'; } $this->wpdb->update( $this->jobs_table, $update_data, ['id' => $job_id], $update_formats, ['%d'] ); } catch (\Exception $e) { } } /** * Get a job by ID * * @param int $job_id Job ID * @return AutomationJob|null Job object or null if not found */ public function getJob($job_id) { $result = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->jobs_table} WHERE id = %d", $job_id ), ARRAY_A ); if (!$result) { return null; } return new AutomationJob($result); } /** * Update a job * * @param AutomationJob $job Job to update * @return bool Success status * @throws \Exception On validation or database errors */ public function updateJob(AutomationJob $job) { if (empty($job->id)) { throw new \Exception('Job ID is required for update'); } $validation_errors = $job->validate(); if (!empty($validation_errors)) { throw new \Exception('Job validation failed: ' . wp_json_encode($validation_errors)); } $job->updated_at = current_time('mysql'); $data = $job->toArray(); $job_id = $data['id']; unset($data['id'], $data['created_at']); $result = $this->wpdb->update( $this->jobs_table, $data, ['id' => $job_id], [ '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%s' ], ['%d'] ); if ($result === false) { throw new \Exception('Failed to update job: ' . esc_html($this->wpdb->last_error)); } return true; } /** * Delete a job and all related data * * Deletes job record and all associated data from: * - aipt_draft_content (generated content awaiting review) * - aipt_job_logs (execution logs) * - aipt_job_products (product queue) * - aipt_automation_jobs (job record itself) * * @param int $job_id Job ID to delete * @return bool Success status * @throws \Exception On database errors */ public function deleteJob($job_id) { $job_id = absint($job_id); if ($job_id <= 0) { return false; } $this->wpdb->query('START TRANSACTION'); try { $draft_content_table = $this->wpdb->prefix . 'aipt_draft_content'; $this->wpdb->delete( $draft_content_table, ['job_id' => $job_id], ['%d'] ); $job_logs_table = $this->wpdb->prefix . 'aipt_job_logs'; $this->wpdb->delete( $job_logs_table, ['job_id' => $job_id], ['%d'] ); $this->wpdb->delete( $this->job_products_table, ['job_id' => $job_id], ['%d'] ); $result = $this->wpdb->delete( $this->jobs_table, ['id' => $job_id], ['%d'] ); if ($result === false) { throw new \Exception('Failed to delete job: ' . esc_html($this->wpdb->last_error)); } if ($this->wpdb->query('COMMIT') === false) { throw new \RuntimeException('COMMIT failed: ' . $this->wpdb->last_error); } return $result > 0; } catch (\Throwable $e) { $rollback = $this->wpdb->query('ROLLBACK'); if ($rollback === false) { } throw new \Exception('Failed to delete job', 0, $e); } } /** * Get jobs with filters * * @param array $filters Filters (status, tool_type, created_by, etc.) * @param int $limit Limit * @param int $offset Offset * @param string $order_by Order by field * @param string $order Order direction (ASC/DESC) * @return array Jobs */ public function getJobs($filters = [], $limit = 20, $offset = 0, $order_by = 'created_at', $order = 'DESC') { $where_conditions = []; $where_values = []; if (!empty($filters['status'])) { if (is_array($filters['status'])) { $placeholders = implode(',', array_fill(0, count($filters['status']), '%s')); $where_conditions[] = "status IN ({$placeholders})"; $where_values = array_merge($where_values, $filters['status']); } else { $where_conditions[] = 'status = %s'; $where_values[] = $filters['status']; } } if (!empty($filters['tool_type'])) { $where_conditions[] = 'tool_type = %s'; $where_values[] = $filters['tool_type']; } if (!empty($filters['created_by'])) { $where_conditions[] = 'created_by = %d'; $where_values[] = $filters['created_by']; } $query = "SELECT * FROM {$this->jobs_table}"; if (!empty($where_conditions)) { $query .= ' WHERE ' . implode(' AND ', $where_conditions); } $allowed_order_by = ['id', 'name', 'status', 'tool_type', 'progress', 'created_at', 'updated_at', 'next_run']; if (!in_array($order_by, $allowed_order_by)) { $order_by = 'created_at'; } $order = strtoupper($order) === 'ASC' ? 'ASC' : 'DESC'; $query .= " ORDER BY {$order_by} {$order}"; $query .= " LIMIT %d OFFSET %d"; $where_values[] = $limit; $where_values[] = $offset; if (!empty($where_values)) { $results = $this->wpdb->get_results( $this->wpdb->prepare($query, $where_values), ARRAY_A ); } else { $results = $this->wpdb->get_results($query, ARRAY_A); } $jobs = []; foreach ($results as $result) { $jobs[] = new AutomationJob($result); } return $jobs; } /** * Whether AutomationConstants::MAX_CONCURRENT_JOBS jobs are already * status='running'. * * Shared by every path that can start a job running (the scheduler * poller, Trigger Now, Resume) so they all honor the same system-wide * cap through one mechanism, instead of each re-deriving and comparing * the running count itself. * * @return bool */ public function isAtConcurrentJobCap(): bool { return $this->getJobCount(['status' => 'running']) >= AutomationConstants::MAX_CONCURRENT_JOBS; } /** * Set $job_id running only while fewer than * AutomationConstants::MAX_CONCURRENT_JOBS jobs run. * * The running count and the status write happen under one MySQL * advisory lock, so two jobs starting at the same moment cannot both * take the last free slot. Both places a job starts running go through * here: the executor's tick start and Resume. A database that refuses * the lock query falls back to the same check without the lock rather * than never starting a job. * * @param int $job_id Job ID. * @return string One of the START_* constants. */ public function startJobWithinConcurrentCap(int $job_id): string { $lock_name = $this->startLockName(); $locked = $this->wpdb->get_var($this->wpdb->prepare('SELECT GET_LOCK(%s, %d)', $lock_name, self::START_LOCK_WAIT_SECONDS)); if (null !== $locked && '1' !== (string) $locked) { return self::START_BUSY; } try { if ($this->isAtConcurrentJobCap()) { return self::START_CAP_FULL; } return $this->updateJobStatus($job_id, 'running') ? self::START_ADMITTED : self::START_WRITE_FAILED; } finally { if ('1' === (string) $locked) { $this->wpdb->get_var($this->wpdb->prepare('SELECT RELEASE_LOCK(%s)', $lock_name)); } } } /** * Advisory lock name for job starts on this site's jobs table, within * MySQL's 64-character limit. * * @return string */ private function startLockName(): string { return 'aipt_job_start_' . sha1((defined('DB_NAME') ? (string) \DB_NAME : '') . ':' . $this->jobs_table); } /** * Get total job count with filters * * @param array $filters Filters * @return int Total count */ public function getJobCount($filters = []) { $where_conditions = []; $where_values = []; if (!empty($filters['status'])) { if (is_array($filters['status'])) { $placeholders = implode(',', array_fill(0, count($filters['status']), '%s')); $where_conditions[] = "status IN ({$placeholders})"; $where_values = array_merge($where_values, $filters['status']); } else { $where_conditions[] = 'status = %s'; $where_values[] = $filters['status']; } } if (!empty($filters['tool_type'])) { $where_conditions[] = 'tool_type = %s'; $where_values[] = $filters['tool_type']; } if (!empty($filters['created_by'])) { $where_conditions[] = 'created_by = %d'; $where_values[] = $filters['created_by']; } $query = "SELECT COUNT(*) FROM {$this->jobs_table}"; if (!empty($where_conditions)) { $query .= ' WHERE ' . implode(' AND ', $where_conditions); } if (!empty($where_values)) { return (int) $this->wpdb->get_var($this->wpdb->prepare($query, $where_values)); } else { return (int) $this->wpdb->get_var($query); } } /** * Update job status * * @param int $job_id Job ID * @param string $status New status * @param string|null $error_message Optional error message * @return bool Success status */ public function updateJobStatus($job_id, $status, $error_message = null) { if ('failed' === $status && !$this->canTransitionToFailed((int) $job_id)) { return false; } $data = [ 'status' => $status, 'updated_at' => current_time('mysql') ]; $formats = ['%s', '%s']; if ($status === 'running') { $data['started_at'] = current_time('mysql'); $formats[] = '%s'; } elseif (in_array($status, ['completed', 'failed'])) { $data['completed_at'] = current_time('mysql'); $formats[] = '%s'; } if ($error_message !== null) { $data['error_message'] = $error_message; $formats[] = '%s'; } elseif (in_array($status, ['running', 'ready', 'scheduled', 'preparing', 'completed'], true)) { $data['error_message'] = null; $formats[] = '%s'; } $result = $this->wpdb->update( $this->jobs_table, $data, ['id' => $job_id], $formats, ['%d'] ); return $result !== false; } /** * Whether $job_id may transition to 'failed' right now. * * Refused when the job is already settled: 'completed', the literal * 'cancelled' status, or 'failed' already carrying * self::CANCELLED_BY_USER_ERROR_MESSAGE (how a user cancellation is * actually stored; see that constant's docblock). A job not found at * all is left to the normal update (which will simply affect 0 rows) * rather than refused for an unrelated reason. * * @param int $job_id Job ID. * @return bool */ private function canTransitionToFailed($job_id) { $current = $this->wpdb->get_row($this->wpdb->prepare( "SELECT status, error_message FROM {$this->jobs_table} WHERE id = %d", $job_id )); if (!$current) { return true; } if (in_array($current->status, ['completed', 'cancelled'], true)) { return false; } if ('failed' === $current->status && self::CANCELLED_BY_USER_ERROR_MESSAGE === $current->error_message) { return false; } return true; } /** * Pause a job whose paid behavior cannot be honored, storing why. * * @param int $job_id Job ID. * @param string $message User-visible reason. * @param bool $reset_pool Drop a partially built pool so a resume rebuilds it. * @return bool Success status */ public function pauseForEntitlement(int $job_id, string $message, bool $reset_pool = false): bool { if ($reset_pool) { $this->resetJobPool($job_id); } return $this->updateJobStatus($job_id, 'paused', $message); } /** * Remove a job's product pool and progress counters so the pool is built * again (a new recurring cycle, or a pool build that was blocked). The * pool builder's keyset cursor starts after the highest pooled product, * so a rebuild needs an empty pool. * * @param int $job_id Job ID. * @return bool Success status */ public function resetJobPool(int $job_id): bool { $deleted = $this->wpdb->delete($this->job_products_table, ['job_id' => $job_id], ['%d']); $updated = $this->wpdb->update( $this->jobs_table, [ 'total_products' => 0, 'processed_products' => 0, 'failed_products' => 0, 'progress' => 0, 'updated_at' => current_time('mysql'), ], ['id' => $job_id], ['%d', '%d', '%d', '%d', '%s'], ['%d'] ); return false !== $deleted && false !== $updated; } /** * Overwrite a job's settings column directly (and bump updated_at) * without re-running AutomationJob::validate() against the whole job. * * Used by the Premium recurring-cycle re-pin, which computes a new * settings.blueprint from the job's OWN already-valid settings — full * revalidation of the unrelated schedule/status/filter fields the * model's validate() also checks would be pure overhead on every cycle * start, for an array this method's own caller already derived. * * @param int $job_id Job ID. * @param array $settings New settings value. * @return bool Success status. */ public function updateJobSettings(int $job_id, array $settings): bool { $updated = $this->wpdb->update( $this->jobs_table, [ 'settings' => wp_json_encode($settings), 'updated_at' => current_time('mysql'), ], ['id' => $job_id], ['%s', '%s'], ['%d'] ); return false !== $updated; } /** * Set a job's next_run directly (and bump updated_at). * * @param int $job_id Job ID. * @param string $next_run UTC 'Y-m-d H:i:s' time, the format next_run is stored in. * @return bool Success status. */ public function setJobNextRun(int $job_id, string $next_run): bool { $updated = $this->wpdb->update( $this->jobs_table, [ 'next_run' => $next_run, 'updated_at' => current_time('mysql'), ], ['id' => $job_id], ['%s', '%s'], ['%d'] ); return false !== $updated; } /** * One-time upgrade step for recurring jobs that were mid-cycle before cycles re-armed. * * Before recurring jobs were re-armed, starting a cycle never moved * next_run forward, so a job that was preparing, running or paused at the * upgrade still stores the start of the cycle it is working through: a * next_run not later than started_at. Kept, that past next_run would make * the re-arm start a full new cycle as soon as the run completes, and a * resume drop the rows still to do. Clearing it lets a paused job resume * into its remaining rows and the re-arm calculate the real next cycle. * A due cycle that has not begun (next_run later than started_at) is kept. * * next_run is stored in UTC and started_at in site time, so both are * compared in UTC. Each row is cleared only if its next_run is unchanged. * * @return int Number of jobs whose stored run was cleared. */ public function clearLegacyInProgressCycleRuns(): int { $rows = $this->wpdb->get_results( "SELECT id, next_run, started_at, schedule_config FROM {$this->jobs_table} WHERE status IN ('preparing', 'running', 'paused') AND next_run IS NOT NULL AND started_at IS NOT NULL", ARRAY_A ); $cleared = 0; foreach ((array) $rows as $row) { $row = (array) $row; $schedule = json_decode((string) ($row['schedule_config'] ?? ''), true); if (!\AIPT\Api\AutomationJobs\Services\JobEntitlementGuard::isRecurring($schedule)) { continue; } $next_ts = strtotime((string) $row['next_run'] . ' UTC'); $started_ts = strtotime(get_gmt_from_date((string) $row['started_at']) . ' UTC'); if (false === $next_ts || false === $started_ts || $next_ts > $started_ts) { continue; } $updated = $this->wpdb->update( $this->jobs_table, ['next_run' => null], ['id' => (int) $row['id'], 'next_run' => (string) $row['next_run']], ['%s'], ['%d', '%s'] ); if (false !== $updated && (int) $updated > 0) { $cleared++; } } return $cleared; } /** * Number of pool rows that are still pending or processing. * * @param int $job_id Job ID. * @return int */ public function countOpenJobProducts(int $job_id): int { return (int) $this->wpdb->get_var( $this->wpdb->prepare( "SELECT COUNT(*) FROM {$this->job_products_table} WHERE job_id = %d AND status IN ('pending', 'processing')", $job_id ) ); } /** * Update job progress * * @param int $job_id Job ID * @param int $processed_products Number of processed products * @param int $failed_products Number of failed products * @return bool Success status */ public function updateJobProgress($job_id, $processed_products, $failed_products = 0) { $job = $this->getJob($job_id); if (!$job) { return false; } $progress = $job->total_products > 0 ? round(($processed_products / $job->total_products) * 100) : 0; $result = $this->wpdb->update( $this->jobs_table, [ 'progress' => min(100, max(0, $progress)), 'processed_products' => $processed_products, 'failed_products' => $failed_products, 'updated_at' => current_time('mysql'), 'last_progress_logged_at' => current_time('mysql') ], ['id' => $job_id], ['%d', '%d', '%d', '%s', '%s'], ['%d'] ); return $result !== false; } /** * Create job products entries * * @param int $job_id Job ID * @param array $product_filter Product filter configuration * @return bool Success status */ private function createJobProducts($job_id, $product_filter, bool $skipProcessed = false, array $targetedTypes = []) { global $wpdb; try { $product_ids = $this->getFilteredProductIds($product_filter, $skipProcessed, $targetedTypes); if (empty($product_ids)) { return true; } $batch_size = 100; $batches = array_chunk($product_ids, $batch_size); foreach ($batches as $batch) { $values = []; $placeholders = []; foreach ($batch as $product_id) { $values[] = $job_id; $values[] = $product_id; $values[] = 'pending'; $values[] = current_time('mysql'); $placeholders[] = '(%d, %d, %s, %s)'; } $query = "INSERT INTO {$this->job_products_table} (job_id, product_id, status, created_at) VALUES " . implode(', ', $placeholders); $result = $wpdb->query($wpdb->prepare($query, $values)); if ($result === false) { throw new \Exception('Failed to create job products: ' . $wpdb->last_error); } } return true; } catch (\Exception $e) { return false; } } /** * Get filtered product IDs based on filter configuration * * @param array $product_filter Product filter configuration * @return array Product IDs */ private function getFilteredProductIds($product_filter, bool $skipProcessed = false, array $targetedTypes = []) { global $wpdb; $query = " SELECT DISTINCT p.ID FROM {$wpdb->posts} p WHERE p.post_type = 'product' AND p.post_status = 'publish' "; $query_params = []; if ( PremiumClassGuard::exists( '\\AIPT\\Premium\\AutomationJobs\\Services\\ProductFilterAugmenter' ) ) { \AIPT\Premium\AutomationJobs\Services\ProductFilterAugmenter::addManagerFilters( $query, $query_params, $product_filter ); } if (!empty($product_filter['priceRange']['min']) || !empty($product_filter['priceRange']['max'])) { $price_conditions = []; if (!empty($product_filter['priceRange']['min'])) { $price_conditions[] = "CAST(pm.meta_value AS DECIMAL(10,2)) >= %f"; $query_params[] = (float) $product_filter['priceRange']['min']; } if (!empty($product_filter['priceRange']['max'])) { $price_conditions[] = "CAST(pm.meta_value AS DECIMAL(10,2)) <= %f"; $query_params[] = (float) $product_filter['priceRange']['max']; } if (!empty($price_conditions)) { $query .= " AND p.ID IN ( SELECT post_id FROM {$wpdb->postmeta} pm WHERE pm.meta_key = '_price' AND " . implode(' AND ', $price_conditions) . " )"; } } if (!empty($product_filter['includeIds'])) { $include_ids = array_filter(array_map('intval', explode(',', $product_filter['includeIds']))); if (!empty($include_ids)) { $include_placeholders = implode(',', array_fill(0, count($include_ids), '%d')); $query .= " AND p.ID IN ({$include_placeholders})"; $query_params = array_merge($query_params, $include_ids); } else { $query .= ' AND 1=0'; } } if (!empty($product_filter['excludeIds'])) { $exclude_ids = array_map('intval', explode(',', $product_filter['excludeIds'])); if (!empty($exclude_ids)) { $exclude_placeholders = implode(',', array_fill(0, count($exclude_ids), '%d')); $query .= " AND p.ID NOT IN ({$exclude_placeholders})"; $query_params = array_merge($query_params, $exclude_ids); } } if ($skipProcessed) { $skip_condition = $this->buildAiProcessedSkipPredicate('p', $targetedTypes); if ('' !== $skip_condition) { $query .= " AND {$skip_condition}"; } } $query .= " ORDER BY p.ID ASC LIMIT 1000"; if (!empty($query_params)) { $results = $wpdb->get_col($wpdb->prepare($query, $query_params)); } else { $results = $wpdb->get_col($query); } return array_map('intval', $results); } 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) . ' )'; } /** * Get failed product IDs by job ID * * @param int $jobId Job ID * @return array Array of failed product IDs */ public function getFailedProductIdsByJobId(int $jobId): array { $table = $this->wpdb->prefix . 'aipt_job_products'; $result = $this->wpdb->get_col( $this->wpdb->prepare( "SELECT product_id FROM {$table} WHERE job_id = %d AND status = 'failed' ORDER BY product_id ASC", $jobId ) ); return $result; } }