wpdb = $wpdb; $this->table = $wpdb->prefix . 'aipt_chatbot_jobs'; } /** * Create a pending chatbot job row (idempotent on * uniq_session_request) and return its job_uuid. * * Back-compat wrapper around {@see self::createOrFindPendingJob()} for * callers that only need the UUID. Prefer the array-returning variant * when the caller must distinguish a fresh insert from an idempotent * retry (e.g. to skip re-scheduling an already-queued Action Scheduler * action). * * @throws \InvalidArgumentException When session_id is not a v4 UUID, * client_request_id is empty or * exceeds the 64-char schema cap, or * payload is not array-shaped. */ public function createPendingJob( string $session_id, string $client_request_id, array $payload ): string { return $this->createOrFindPendingJob( $session_id, $client_request_id, $payload )['job_uuid']; } /** * Create-or-find variant of {@see self::createPendingJob()} that also * reports whether the row was freshly inserted by this call (true) or * already existed from a prior request that hit the same * (session_id, client_request_id) idempotency key (false). * * @return array{job_uuid:string, was_fresh:bool} * * @throws \InvalidArgumentException Same conditions as createPendingJob(). * @throws \RuntimeException When the INSERT itself fails. */ public function createOrFindPendingJob( string $session_id, string $client_request_id, array $payload ): array { if ( 1 !== preg_match( '/^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i', $session_id ) ) { throw new \InvalidArgumentException( 'createPendingJob: session_id is not a v4 UUID.' ); } $client_request_id = trim( $client_request_id ); if ( '' === $client_request_id ) { throw new \InvalidArgumentException( 'createPendingJob: client_request_id is empty.' ); } if ( strlen( $client_request_id ) > 64 ) { throw new \InvalidArgumentException( 'createPendingJob: client_request_id exceeds 64 chars.' ); } if ( 1 !== preg_match( '/^[A-Za-z0-9_\-]+$/', $client_request_id ) ) { throw new \InvalidArgumentException( 'createPendingJob: client_request_id charset (allowed: A-Z a-z 0-9 _ -).' ); } $job_uuid = \wp_generate_uuid4(); $status_token = \wp_generate_password( ChatbotConstants::STATUS_TOKEN_LENGTH, false ); $payload_json = \wp_json_encode( $payload ); if ( ! is_string( $payload_json ) || '' === $payload_json ) { $payload_json = '{}'; } $now = \current_time( 'mysql' ); $insert_sql = $this->wpdb->prepare( "INSERT INTO {$this->table} (job_uuid,session_id,client_request_id,status_token,status,payload,attempts,created_at) VALUES (%s,%s,%s,%s,%s,%s,%d,%s) ON DUPLICATE KEY UPDATE job_uuid = job_uuid", $job_uuid, $session_id, $client_request_id, $status_token, 'pending', $payload_json, 0, $now ); $result = $this->wpdb->query( $insert_sql ); if ( false === $result ) { throw new \RuntimeException( 'Failed to create chatbot job.' ); } if ( 1 === (int) $result ) { return array( 'job_uuid' => $job_uuid, 'was_fresh' => true, ); } $existing_uuid = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT job_uuid FROM {$this->table} WHERE session_id = %s AND client_request_id = %s LIMIT 1", $session_id, $client_request_id ) ); if ( ! is_string( $existing_uuid ) || '' === $existing_uuid ) { throw new \RuntimeException( sprintf( 'ChatbotJobRepository::createOrFindPendingJob — INSERT reported ON DUPLICATE KEY but no row matched (session_id=%s, client_request_id=%s). Concurrent delete or transient DB failure suspected.', \esc_html( $session_id ), \esc_html( $client_request_id ) ) ); } return array( 'job_uuid' => $existing_uuid, 'was_fresh' => false, ); } public function findByUuid( string $job_uuid ): ?array { $row = $this->wpdb->get_row( $this->wpdb->prepare( "SELECT * FROM {$this->table} WHERE job_uuid = %s LIMIT 1", $job_uuid ), ARRAY_A ); return is_array( $row ) ? $row : null; } public function findByUuidWithToken( string $job_uuid, string $status_token ): ?array { $row = $this->findByUuid( $job_uuid ); if ( null === $row || ! isset( $row['status_token'] ) ) { return null; } return hash_equals( (string) $row['status_token'], $status_token ) ? $row : null; } public function claimAtomic( string $job_uuid, int $claim_ttl_seconds ): ?string { $claim_ttl_seconds = max( 1, $claim_ttl_seconds ); $claim_token = \wp_generate_uuid4(); $claimed_at = \current_time( 'mysql' ); $expires_at = $this->formatTimestamp( \time() + $claim_ttl_seconds ); $sql = $this->wpdb->prepare( "UPDATE {$this->table} SET claim_token = %s, claimed_at = %s, expires_at = %s, status = 'processing', attempts = attempts + 1 WHERE job_uuid = %s AND claim_token IS NULL AND status = 'pending' LIMIT 1", $claim_token, $claimed_at, $expires_at, $job_uuid ); $updated = $this->queryWithDeadlockRetry( $sql ); return (int) $updated > 0 ? $claim_token : null; } public function releaseClaim( string $job_uuid, string $claim_token ): bool { $updated = $this->wpdb->update( $this->table, [ 'status' => 'pending', 'claim_token' => null, 'claimed_at' => null, 'expires_at' => null, ], [ 'job_uuid' => $job_uuid, 'claim_token' => $claim_token, 'status' => 'processing', ], [ '%s', '%s', '%s', '%s' ], [ '%s', '%s', '%s' ] ); return (int) $updated > 0; } public function markCompleted( string $job_uuid, string $claim_token, array $response_payload ): bool { $response_json = \wp_json_encode( $response_payload ); if ( ! is_string( $response_json ) || '' === $response_json ) { $response_json = '{}'; } $updated = $this->wpdb->update( $this->table, [ 'status' => 'completed', 'response' => $response_json, 'completed_at' => \current_time( 'mysql' ), 'claim_token' => null, 'claimed_at' => null, 'expires_at' => null, 'error_code' => null, 'error_detail' => null, ], [ 'job_uuid' => $job_uuid, 'claim_token' => $claim_token, 'status' => 'processing', ], [ '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' ], [ '%s', '%s', '%s' ] ); return (int) $updated > 0; } public function markFailed( string $job_uuid, string $claim_token, string $error_code, ?string $error_detail = null ): bool { $updated = $this->wpdb->update( $this->table, [ 'status' => 'failed', 'error_code' => \sanitize_key( $error_code ), 'error_detail' => $this->sanitizeErrorDetail( $error_detail ), 'claim_token' => null, 'claimed_at' => null, 'expires_at' => null, ], [ 'job_uuid' => $job_uuid, 'claim_token' => $claim_token, 'status' => 'processing', ], [ '%s', '%s', '%s', '%s', '%s', '%s' ], [ '%s', '%s', '%s' ] ); return (int) $updated > 0; } public function markRequiresUserRetry( string $job_uuid, string $claim_token, string $error_code, int $backoff_seconds ): bool { $next_attempt_at = $this->formatTimestamp( \time() + max( 0, $backoff_seconds ) ); $updated = $this->wpdb->update( $this->table, [ 'status' => 'requires_user_retry', 'error_code' => \sanitize_key( $error_code ), 'next_attempt_at' => $next_attempt_at, 'claim_token' => null, 'claimed_at' => null, 'expires_at' => null, ], [ 'job_uuid' => $job_uuid, 'claim_token' => $claim_token, 'status' => 'processing', ], [ '%s', '%s', '%s', '%s', '%s', '%s' ], [ '%s', '%s', '%s' ] ); return (int) $updated > 0; } /** * Reclaim stale claims (worker crashed / timed out past claim TTL). * * Returns the list of job UUIDs that were reclaimed so the caller can * reschedule a fresh Action Scheduler action for each. Without that * follow-up scheduling the original single-action was consumed when * the worker started and the row would sit in `pending` forever * because the queue is otherwise event-driven (no periodic drain). * * @param int $stale_grace_seconds Grace period after expires_at before reclaim. * @return string[] Reclaimed job UUIDs (caller schedules new AS actions). */ /** * Transition a job from `pending` → `failed` without a claim token. * * Used by EnqueueService when AsyncRunner::scheduleJob() fails right * after createPendingJob(). The standard markFailed() requires a * claim_token to prove ownership, but at this point the row was just * created and no worker has — or can — claim it (the AS action that * would let a worker claim wasn't actually scheduled). Without this * dedicated transition the row would sit in pending forever because * reclaimStaleClaims() only handles rows that DID acquire a claim * and then went stale. */ public function markScheduleFailed( string $job_uuid, string $error_code, ?string $error_detail = null ): bool { $updated = $this->wpdb->update( $this->table, [ 'status' => 'failed', 'error_code' => \sanitize_key( $error_code ), 'error_detail' => $this->sanitizeErrorDetail( $error_detail ), 'completed_at' => \current_time( 'mysql' ), ], [ 'job_uuid' => $job_uuid, 'status' => 'pending', 'claim_token' => null, ], [ '%s', '%s', '%s', '%s' ], [ '%s', '%s', '%s' ] ); return (int) $updated > 0; } /** * Requeue a row that failed to SCHEDULE (see markScheduleFailed) back to * `pending` so an idempotent retry of the same (session_id, * client_request_id) can attempt scheduling again. * * The WHERE is guarded by status='failed' AND the schedule-failure * error_code AND claim_token IS NULL, so it can NEVER resurrect a genuine * processing failure (different error_code) or a still-claimed row. */ public function requeueScheduleFailed( string $job_uuid, string $schedule_failed_code ): bool { $updated = $this->wpdb->query( $this->wpdb->prepare( "UPDATE {$this->table} SET status = 'pending', error_code = NULL, error_detail = NULL, completed_at = NULL WHERE job_uuid = %s AND status = 'failed' AND error_code = %s AND claim_token IS NULL", $job_uuid, \sanitize_key( $schedule_failed_code ) ) ); return (int) $updated > 0; } public function reclaimStaleClaims( int $stale_grace_seconds ): array { $threshold = $this->formatTimestamp( \time() - max( 0, $stale_grace_seconds ) ); $this->wpdb->query( $this->wpdb->prepare( "UPDATE {$this->table} SET status = 'failed', error_code = 'worker_max_attempts', claim_token = NULL, claimed_at = NULL, expires_at = NULL WHERE claim_token IS NOT NULL AND expires_at < %s AND attempts >= %d", $threshold, ChatbotConstants::MAX_CLAIM_ATTEMPTS ) ); $candidate_uuids = $this->wpdb->get_col( $this->wpdb->prepare( "SELECT job_uuid FROM {$this->table} WHERE claim_token IS NOT NULL AND expires_at < %s", $threshold ) ); if ( empty( $candidate_uuids ) ) { return []; } $updated = $this->wpdb->query( $this->wpdb->prepare( "UPDATE {$this->table} SET status = 'pending', claim_token = NULL, claimed_at = NULL, expires_at = NULL WHERE claim_token IS NOT NULL AND expires_at < %s", $threshold ) ); if ( false === $updated || (int) $updated <= 0 ) { return []; } $reclaimed = array_values( array_filter( array_map( 'strval', $candidate_uuids ) ) ); if ( ! empty( $reclaimed ) ) { ChatbotLogger::warn( 'stale_claims_reclaimed', [ 'count' => count( $reclaimed ) ] ); } return $reclaimed; } /** * Delete finished job rows older than the retention window. * * Job rows hold the visitor's message, the reply and optionally the * consented email, so terminal rows must not be kept indefinitely. * Pending and processing rows are never touched. * * @param int $days_old Retention in days (minimum 1). * @return int Number of rows deleted. */ public function purgeFinished( int $days_old ): int { $cutoff = \gmdate( 'Y-m-d H:i:s', (int) \current_time( 'timestamp' ) - ( max( 1, $days_old ) * DAY_IN_SECONDS ) ); $deleted = $this->wpdb->query( $this->wpdb->prepare( "DELETE FROM {$this->table} WHERE status IN ('completed','failed','requires_user_retry') AND created_at < %s", $cutoff ) ); return false === $deleted ? 0 : (int) $deleted; } private function queryWithDeadlockRetry( string $sql ) { $attempt = 0; do { $result = $this->wpdb->query( $sql ); if ( false !== $result ) { return $result; } $error = strtolower( (string) $this->wpdb->last_error ); if ( false === strpos( $error, 'deadlock' ) && false === strpos( $error, 'lock wait timeout' ) ) { return false; } usleep( (int) ( pow( 2, $attempt ) * 100000 ) ); $attempt++; } while ( $attempt < 3 ); return false; } private function sanitizeErrorDetail( ?string $error_detail ): ?string { if ( null === $error_detail || '' === $error_detail ) { return null; } $sanitized = ErrorHandler::sanitize_error_for_logging( $error_detail ); $sanitized = preg_replace( '/\b(?:SELECT|INSERT|UPDATE|DELETE)\b[^;]*?\b(?:FROM|INTO|SET|VALUES)\b[^;]*;?/i', '[SQL_REMOVED]', $sanitized ); $sanitized = \wp_strip_all_tags( $sanitized ); $sanitized = preg_replace( '/\s+/', ' ', $sanitized ); $sanitized = is_string( $sanitized ) ? trim( $sanitized ) : ''; return '' === $sanitized ? null : substr( $sanitized, 0, 2000 ); } private function formatTimestamp( int $timestamp ): string { return \gmdate( 'Y-m-d H:i:s', $timestamp ); } }