$payload */ public function create( string $type, array $payload, ?string $idempotency_key = null ): int { global $wpdb; $table = TableNames::assert_plugin_table( TableNames::jobs() ); $site_id = TableNames::site_id(); $attachment = isset( $payload['attachment_id'] ) ? (int) $payload['attachment_id'] : 0; $requested = isset( $payload['requested_by'] ) ? (int) $payload['requested_by'] : get_current_user_id(); $operation = isset( $payload['operation_id'] ) && is_string( $payload['operation_id'] ) && wp_is_uuid( $payload['operation_id'] ) ? $payload['operation_id'] : null; $now = current_time( 'mysql' ); $idempotency_key = $idempotency_key ? hash( 'sha256', $idempotency_key ) : null; if ( $idempotency_key ) { $existing = $wpdb->get_row( $wpdb->prepare( 'SELECT id, status FROM %i WHERE site_id = %d AND idempotency_key = %s LIMIT 1', $table, $site_id, $idempotency_key ), ARRAY_A ); if ( $existing && in_array( (string) $existing['status'], array( 'pending', 'running', 'retrying' ), true ) ) { return (int) $existing['id']; } if ( $existing ) { $wpdb->update( $table, array( 'idempotency_key' => null ), array( 'id' => (int) $existing['id'] ) ); } } $wpdb->insert( $table, array( 'site_id' => $site_id, 'attachment_id' => $attachment > 0 ? $attachment : null, 'requested_by' => $requested > 0 ? $requested : null, 'operation_id' => $operation, 'type' => $type, 'status' => 'pending', 'payload' => wp_json_encode( $payload ), 'progress' => 0, 'attempts' => 0, 'created_at' => $now, 'available_at' => $now, 'idempotency_key' => $idempotency_key, ) ); if ( ! $wpdb->insert_id && $idempotency_key ) { return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE site_id = %d AND idempotency_key = %s LIMIT 1', $table, $site_id, $idempotency_key ) ); } return (int) $wpdb->insert_id; } /** * Return the newest unfinished bulk operation owned by a user. * * @return array|null */ public function active_operation( int $requested_by ): ?array { global $wpdb; if ( $requested_by <= 0 ) { return null; } $table = TableNames::assert_plugin_table( TableNames::jobs() ); $operation_id = $wpdb->get_var( $wpdb->prepare( "SELECT operation_id FROM %i WHERE site_id = %d AND requested_by = %d AND operation_id IS NOT NULL AND status IN ('pending', 'running', 'retrying') ORDER BY id DESC LIMIT 1", $table, TableNames::site_id(), $requested_by ) ); if ( ! is_string( $operation_id ) || ! wp_is_uuid( $operation_id ) ) { return null; } $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT id, attachment_id, type, status FROM %i WHERE site_id = %d AND requested_by = %d AND operation_id = %s ORDER BY id ASC', $table, TableNames::site_id(), $requested_by, $operation_id ), ARRAY_A ); if ( ! is_array( $rows ) || ! $rows ) { return null; } $terminal = array( 'completed', 'failed', 'cancelled' ); $job_ids = array(); $open_job_ids = array(); $attachment_ids = array(); $open_attachment_ids = array(); $failed = 0; foreach ( $rows as $row ) { $job_id = (int) $row['id']; $attachment_id = (int) ( $row['attachment_id'] ?? 0 ); $status = (string) $row['status']; $job_ids[] = $job_id; if ( $attachment_id > 0 ) { $attachment_ids[] = $attachment_id; } if ( ! in_array( $status, $terminal, true ) ) { $open_job_ids[] = $job_id; if ( $attachment_id > 0 ) { $open_attachment_ids[] = $attachment_id; } } if ( 'failed' === $status ) { ++$failed; } } $type = (string) ( $rows[0]['type'] ?? '' ); return array( 'operationId' => $operation_id, 'kind' => 'convert_asset' === $type ? 'convert' : 'analyze', 'total' => count( $rows ), 'current' => count( $rows ) - count( $open_job_ids ), 'failed' => $failed, 'jobIds' => $job_ids, 'openJobIds' => $open_job_ids, 'attachmentIds' => array_values( array_unique( $attachment_ids ) ), 'openAttachmentIds' => array_values( array_unique( $open_attachment_ids ) ), ); } /** True if an open job of this type already exists for the same attachment. */ public function has_open_for_attachment( string $type, int $attachment_id ): bool { global $wpdb; $table = TableNames::assert_plugin_table( TableNames::jobs() ); $found = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE site_id = %d AND type = %s AND status IN (\'pending\', \'running\', \'retrying\') AND attachment_id = %d LIMIT 1', $table, TableNames::site_id(), $type, $attachment_id ) ); return (int) $found > 0; } /** True if an open job of the requested type targets this attachment. */ public function has_open_type_for_attachment( string $type, int $attachment_id ): bool { global $wpdb; $table = TableNames::assert_plugin_table( TableNames::jobs() ); $found = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE site_id = %d AND type = %s AND status IN (\'pending\', \'running\', \'retrying\') AND attachment_id = %d LIMIT 1', $table, TableNames::site_id(), $type, $attachment_id ) ); return (int) $found > 0; } /** * True if any open analyze/generate job targets this attachment (payload may include extra keys). */ public function has_open_attachment_work( int $attachment_id ): bool { global $wpdb; $table = TableNames::assert_plugin_table( TableNames::jobs() ); $found = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE site_id = %d AND type IN (\'analyze_asset\', \'generate_metadata\') AND status IN (\'pending\', \'running\', \'retrying\') AND attachment_id = %d LIMIT 1', $table, TableNames::site_id(), $attachment_id ) ); return (int) $found > 0; } /** Open analyze/generate job id for attachment, or 0. */ public function open_attachment_job_id( int $attachment_id ): int { global $wpdb; $table = TableNames::assert_plugin_table( TableNames::jobs() ); $id = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE site_id = %d AND type IN (\'analyze_asset\', \'generate_metadata\') AND status IN (\'pending\', \'running\', \'retrying\') AND attachment_id = %d ORDER BY id DESC LIMIT 1', $table, TableNames::site_id(), $attachment_id ) ); return (int) $id; } /** Number of runnable jobs; deferred retries are counted when due. */ public function count_open( ?int $actor = null ): int { global $wpdb; return (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM %i WHERE site_id = %d AND " . $this->ready_sql() . $this->actor_sql( $actor ), TableNames::assert_plugin_table( TableNames::jobs() ), TableNames::site_id() ) ); } /** @return list Runnable job IDs for asynchronous dispatch. */ public function pending_ids( int $limit = 10, ?int $actor = null ): array { global $wpdb; $limit = max( 1, min( 50, $limit ) ); $rows = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE site_id = %d AND ' . $this->ready_sql() . $this->actor_sql( $actor ) . ' ORDER BY id ASC LIMIT %d', TableNames::assert_plugin_table( TableNames::jobs() ), TableNames::site_id(), $limit ) ) ?: array(); return array_values( array_map( 'intval', $rows ) ); } /** * Atomically claim one job (pending/retrying → running). * * @return array|null */ public function claim_one( int $id ): ?array { global $wpdb; $table = TableNames::assert_plugin_table( TableNames::jobs() ); $site_id = TableNames::site_id(); $now = current_time( 'mysql' ); $lease = wp_generate_uuid4(); $expires = wp_date( 'Y-m-d H:i:s', (int) current_time( 'timestamp' ) + ( 5 * MINUTE_IN_SECONDS ) ); $updated = $wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = \'running\', started_at = %s, heartbeat_at = %s, lease_token = %s, lease_expires_at = %s, attempts = attempts + 1 WHERE id = %d AND site_id = %d AND ' . $this->ready_sql(), $table, $now, $now, $lease, $expires, $id, $site_id ) ); return 1 === (int) $updated ? $this->find( $id ) : null; } /** * Atomically claim up to $limit open jobs (pending/retrying → running). * * @return list> */ public function claim_pending( int $limit = 5, ?int $actor = null ): array { global $wpdb; $table = TableNames::assert_plugin_table( TableNames::jobs() ); $site_id = TableNames::site_id(); $limit = max( 1, min( 50, $limit ) ); // Re-queue only expired leases. The started_at fallback covers pre-migration rows. $stale_ts = (int) current_time( 'timestamp' ) - ( 5 * MINUTE_IN_SECONDS ); $stale_before = wp_date( 'Y-m-d H:i:s', $stale_ts ); $now = current_time( 'mysql' ); $wpdb->query( $wpdb->prepare( 'UPDATE %i SET status = \'pending\', error = %s, finished_at = NULL, lease_token = NULL, lease_expires_at = NULL, heartbeat_at = NULL, available_at = %s WHERE site_id = %d AND status = \'running\' AND ((lease_expires_at IS NOT NULL AND lease_expires_at < %s) OR (lease_expires_at IS NULL AND started_at IS NOT NULL AND started_at < %s))' . $this->actor_sql( $actor ), $table, __( 'Recovered stale running job.', 'foldrocket' ), $now, $site_id, $now, $stale_before ) ); $ids = $wpdb->get_col( $wpdb->prepare( 'SELECT id FROM %i WHERE site_id = %d AND ' . $this->ready_sql() . $this->actor_sql( $actor ) . ' ORDER BY CASE WHEN type IN (\'generate_metadata\', \'analyze_asset\') THEN 0 ELSE 1 END, COALESCE(started_at, created_at) ASC, id ASC LIMIT %d', $table, $site_id, $limit ) ) ?: array(); $claimed = array(); foreach ( $ids as $id ) { if ( $actor !== null ) { $candidate = $this->find( (int) $id ); if ( ! $candidate || ! current_user_can( 'edit_post', (int) ( $candidate['payload']['attachment_id'] ?? 0 ) ) ) { continue; } } $job = $this->claim_one( (int) $id ); if ( $job ) { $claimed[] = $job; } } return $claimed; } /** * @return array{items: list>, total: int} */ public function list( int $page = 1, int $per_page = 20 ): array { global $wpdb; $table = TableNames::assert_plugin_table( TableNames::jobs() ); $site_id = TableNames::site_id(); $offset = max( 0, ( $page - 1 ) * $per_page ); $total = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE site_id = %d', $table, $site_id ) ); $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE site_id = %d ORDER BY id DESC LIMIT %d OFFSET %d', $table, $site_id, $per_page, $offset ), ARRAY_A ) ?: array(); return array( 'items' => array_map( array( $this, 'format' ), $rows ), 'total' => $total, ); } /** @return array|null */ public function find( int $id ): ?array { global $wpdb; $row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE id = %d AND site_id = %d', TableNames::assert_plugin_table( TableNames::jobs() ), $id, TableNames::site_id() ), ARRAY_A ); return $row ? $this->format( $row ) : null; } public function mark_running( int $id ): void { global $wpdb; $wpdb->update( TableNames::jobs(), array( 'status' => 'running', 'started_at' => current_time( 'mysql' ), 'attempts' => (int) $wpdb->get_var( $wpdb->prepare( 'SELECT attempts FROM %i WHERE id = %d', TableNames::assert_plugin_table( TableNames::jobs() ), $id ) ) + 1, ), array( 'id' => $id, 'site_id' => TableNames::site_id() ) ); } /** Save the next batch without creating thousands of child jobs. Cancellation wins. */ public function checkpoint( int $id, array $payload, int $progress, bool $done, string $lease_token ): bool { global $wpdb; if ( $lease_token === '' ) { return false; } $updated = $wpdb->update( TableNames::jobs(), array( 'attempts' => 0, 'payload' => wp_json_encode( $payload ), 'progress' => $progress, 'status' => $done ? 'completed' : 'pending', 'available_at' => $done ? null : current_time( 'mysql' ), 'started_at' => null, 'finished_at' => $done ? current_time( 'mysql' ) : null, 'lease_token' => null, 'lease_expires_at'=> null, 'heartbeat_at' => null, 'idempotency_key' => $done ? null : $this->idempotency_key( $id ), 'error' => null, ), array( 'id' => $id, 'site_id' => TableNames::site_id(), 'status' => 'running', 'lease_token' => $lease_token ) ); if ( false === $updated ) { return false; } if ( 1 === (int) $updated ) { return true; } return $this->owns_lease( $id, $lease_token ); } private function owns_lease( int $id, string $lease_token ): bool { global $wpdb; $found = $wpdb->get_var( $wpdb->prepare( 'SELECT lease_token FROM %i WHERE id = %d AND site_id = %d AND status = \'running\'', TableNames::assert_plugin_table( TableNames::jobs() ), $id, TableNames::site_id() ) ); return is_string( $found ) && hash_equals( $found, $lease_token ); } /** Extend an active lease while a chunked handler is making progress. */ public function heartbeat( int $id, string $lease_token ): bool { global $wpdb; if ( $lease_token === '' ) { return false; } $now = current_time( 'mysql' ); $updated = $wpdb->update( TableNames::jobs(), array( 'heartbeat_at' => $now, 'lease_expires_at' => wp_date( 'Y-m-d H:i:s', (int) current_time( 'timestamp' ) + ( 5 * MINUTE_IN_SECONDS ) ), ), array( 'id' => $id, 'site_id' => TableNames::site_id(), 'status' => 'running', 'lease_token' => $lease_token ) ); if ( false === $updated ) { return false; } return 1 === (int) $updated || $this->owns_lease( $id, $lease_token ); } /** * Persist progress for a long-running worker without releasing its lease. * * @param array|null $payload */ public function update_progress( int $id, string $lease_token, int $progress, ?array $payload = null ): bool { global $wpdb; if ( $lease_token === '' ) { return false; } $now = current_time( 'mysql' ); $data = array( 'progress' => max( 0, min( 99, $progress ) ), 'heartbeat_at' => $now, 'lease_expires_at' => wp_date( 'Y-m-d H:i:s', (int) current_time( 'timestamp' ) + ( 5 * MINUTE_IN_SECONDS ) ), ); if ( null !== $payload ) { $data['payload'] = wp_json_encode( $payload ); } $updated = $wpdb->update( TableNames::jobs(), $data, array( 'id' => $id, 'site_id' => TableNames::site_id(), 'status' => 'running', 'lease_token' => $lease_token ) ); if ( false === $updated ) { return false; } return 1 === (int) $updated || $this->owns_lease( $id, $lease_token ); } /** Release a claim that could not start because the same resource is busy. */ public function release_claim( int $id, string $lease_token, int $delay = 5 ): bool { global $wpdb; if ( $lease_token === '' ) { return false; } return 1 === $wpdb->update( TableNames::jobs(), array( 'status' => 'pending', 'available_at' => wp_date( 'Y-m-d H:i:s', (int) current_time( 'timestamp' ) + max( 1, $delay ) ), 'started_at' => null, 'lease_token' => null, 'lease_expires_at' => null, 'heartbeat_at' => null, 'attempts' => max( 0, (int) $this->attempts( $id ) - 1 ), ), array( 'id' => $id, 'site_id' => TableNames::site_id(), 'status' => 'running', 'lease_token' => $lease_token ) ); } private function attempts( int $id ): int { global $wpdb; return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT attempts FROM %i WHERE id = %d AND site_id = %d', TableNames::assert_plugin_table( TableNames::jobs() ), $id, TableNames::site_id() ) ); } private function idempotency_key( int $id ): ?string { global $wpdb; $value = $wpdb->get_var( $wpdb->prepare( 'SELECT idempotency_key FROM %i WHERE id = %d AND site_id = %d', TableNames::assert_plugin_table( TableNames::jobs() ), $id, TableNames::site_id() ) ); return is_string( $value ) && $value !== '' ? $value : null; } public function mark_completed( int $id, string $lease_token ): bool { global $wpdb; if ( $lease_token === '' ) { return false; } return 1 === $wpdb->update( TableNames::jobs(), array( 'status' => 'completed', 'progress' => 100, 'finished_at' => current_time( 'mysql' ), 'error' => null, 'lease_token' => null, 'lease_expires_at' => null, 'heartbeat_at' => null, 'idempotency_key' => null, ), array( 'id' => $id, 'site_id' => TableNames::site_id(), 'status' => 'running', 'lease_token' => $lease_token ) ); } public function mark_failed( int $id, string $lease_token, string $error, bool $retryable = true ): bool { global $wpdb; if ( $lease_token === '' ) { return false; } $job = $this->find( $id ); $delay = $retryable ? self::retry_delay( (int) ( $job['attempts'] ?? 1 ) ) : 0; return 1 === $wpdb->update( TableNames::jobs(), array( 'status' => $retryable ? 'retrying' : 'failed', 'error' => $error, 'finished_at' => current_time( 'mysql' ), 'available_at' => $retryable ? wp_date( 'Y-m-d H:i:s', (int) current_time( 'timestamp' ) + $delay ) : null, 'lease_token' => null, 'lease_expires_at' => null, 'heartbeat_at' => null, 'idempotency_key' => $retryable ? ( $job['idempotencyKey'] ?? null ) : null, ), array( 'id' => $id, 'site_id' => TableNames::site_id(), 'status' => 'running', 'lease_token' => $lease_token ) ); } public function cancel( int $id ): bool { $job = $this->find( $id ); if ( ! $job || ! in_array( $job['status'], array( 'pending', 'retrying', 'running' ), true ) ) { return false; } global $wpdb; $wpdb->update( TableNames::jobs(), array( 'status' => 'cancelled', 'finished_at' => current_time( 'mysql' ), 'lease_token' => null, 'lease_expires_at' => null, 'heartbeat_at' => null, 'idempotency_key' => null, ), array( 'id' => $id, 'site_id' => TableNames::site_id() ) ); return true; } public function reset_for_retry( int $id ): bool { $job = $this->find( $id ); if ( ! $job || ! in_array( $job['status'], array( 'failed', 'retrying', 'cancelled' ), true ) ) { return false; } global $wpdb; $wpdb->update( TableNames::jobs(), array( 'status' => 'pending', 'error' => null, 'finished_at' => null, 'started_at' => null, 'available_at'=> current_time( 'mysql' ), 'lease_token' => null, 'lease_expires_at' => null, 'heartbeat_at' => null, 'attempts' => 0, 'progress' => $job['progress'] ?? 0, ), array( 'id' => $id, 'site_id' => TableNames::site_id() ) ); return true; } /** Delay before the next automatic attempt. */ public static function retry_delay( int $attempts ): int { return match ( $attempts ) { 1 => 60, 2 => 300, 3 => 1200, default => 3600 }; } private function ready_sql(): string { global $wpdb; return $wpdb->prepare( "status IN ('pending', 'retrying') AND (available_at IS NULL OR available_at <= %s)", current_time( 'mysql' ) ); } /** Non-admin pumps may execute only AI work enqueued by the current user. */ private function actor_sql( ?int $actor ): string { global $wpdb; return $actor === null ? '' : $wpdb->prepare( " AND type IN ('analyze_asset','generate_metadata','convert_asset','ai_edit_image','apply_ai_edit','build_library_zip','optimize_video','restore_video','generate_video_poster') AND requested_by = %d", $actor ); } /** * @param array $row * @return array */ private function format( array $row ): array { $payload = json_decode( (string) ( $row['payload'] ?? '{}' ), true ); return array( 'id' => (int) $row['id'], 'operationId' => $row['operation_id'] ?? null, 'attachmentId' => isset( $row['attachment_id'] ) ? (int) $row['attachment_id'] : null, 'requestedBy' => isset( $row['requested_by'] ) ? (int) $row['requested_by'] : null, 'type' => (string) $row['type'], 'status' => (string) $row['status'], 'payload' => is_array( $payload ) ? $payload : array(), 'progress' => (float) $row['progress'], 'attempts' => (int) $row['attempts'], 'error' => $row['error'], 'createdAt' => (string) $row['created_at'], 'availableAt' => $row['available_at'] ?? null, 'startedAt' => $row['started_at'], 'finishedAt' => $row['finished_at'], 'leaseExpiresAt' => $row['lease_expires_at'] ?? null, 'leaseToken' => $row['lease_token'] ?? null, 'heartbeatAt' => $row['heartbeat_at'] ?? null, 'idempotencyKey' => $row['idempotency_key'] ?? null, ); } }