table = $wpdb->prefix . 'aisentic_messages'; } /** * Create a new message * * @param string $conversationUuid Conversation UUID. * @param string $role Message role. * @param string $content Message content. * @param array|null $metadata Optional metadata. * @return Message|null */ public function create( string $conversation_uuid, string $role, string $content, ?array $metadata = null ): ?Message { global $wpdb; $result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $this->table, [ 'conversation_uuid' => $conversation_uuid, 'role' => $role, 'content' => $content, 'metadata' => $metadata ? wp_json_encode( $metadata ) : null, 'created_at' => current_time( 'mysql' ), ], [ '%s', '%s', '%s', '%s', '%s' ] ); if ( false === $result ) { return null; } $this->invalidate_conversation_messages_cache( $conversation_uuid ); $id = $wpdb->insert_id; return $this->findById( $id ); } /** * Get the object cache key for a message ID. * * @param int $id Message ID. * @return string */ private function get_message_cache_key( int $id ): string { return 'msg_' . $id; } /** * Get the current cache version for a conversation's message list. * * @param string $conversation_uuid Conversation UUID. * @return int */ private function get_messages_cache_version( string $conversation_uuid ): int { $ver = wp_cache_get( 'conv_msgs_ver_' . md5( $conversation_uuid ), 'aisentic' ); return false === $ver ? 0 : (int) $ver; } /** * Invalidate all cached message lists for a conversation. * * @param string $conversation_uuid Conversation UUID. * @return void */ private function invalidate_conversation_messages_cache( string $conversation_uuid ): void { $ver = $this->get_messages_cache_version( $conversation_uuid ); wp_cache_set( 'conv_msgs_ver_' . md5( $conversation_uuid ), $ver + 1, 'aisentic', 300 ); } /** * Find message by ID * * @param int $id Message ID. * @return Message|null */ public function findById( int $id ): ?Message { global $wpdb; $cache_key = $this->get_message_cache_key( $id ); $cached = wp_cache_get( $cache_key, 'aisentic' ); if ( false !== $cached ) { return ! empty( $cached ) ? new Message( $cached ) : null; } $row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $wpdb->prepare( "SELECT * FROM {$this->table} WHERE id = %d", $id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared ARRAY_A ); wp_cache_set( $cache_key, $row ? $row : [], 'aisentic', 300 ); return $row ? new Message( $row ) : null; } /** * Find the placeholder message holding a given pending-approval id. * * Used on the expiry path, where the approval transient (which normally * carries the message id) is already gone and the card still has to be * marked so a page reload does not resurrect an actionable approval. * * The id lives inside the JSON metadata blob, so this is a LIKE scan rather * than an indexed lookup — acceptable because it only runs when a user acts * on an approval that has already expired. * * @param string $pending_id Workflow id stored in metadata.pending_approval.pending_id. * @return Message|null */ public function find_by_pending_approval_id( string $pending_id ): ?Message { global $wpdb; if ( '' === $pending_id ) { return null; } $needle = '%' . $wpdb->esc_like( '"pending_id":"' . $pending_id . '"' ) . '%'; $row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->prepare( "SELECT * FROM {$this->table} WHERE metadata LIKE %s ORDER BY id DESC LIMIT 1", $needle ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared ARRAY_A ); return $row ? new Message( $row ) : null; } /** * Get messages for a conversation * * @param string $conversation_uuid Conversation UUID. * @param int $limit Maximum number of messages. * @return Message[] */ public function getByConversation( string $conversation_uuid, int $limit = 100 ): array { global $wpdb; $ver = $this->get_messages_cache_version( $conversation_uuid ); $cache_key = 'conv_msgs_' . md5( $conversation_uuid ) . '_' . $limit . '_v' . $ver; $cached = wp_cache_get( $cache_key, 'aisentic' ); if ( false !== $cached ) { return $cached; } $rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $wpdb->prepare( "SELECT * FROM {$this->table} WHERE conversation_uuid = %s ORDER BY created_at ASC, id ASC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $conversation_uuid, $limit ), ARRAY_A ); $messages = array_map( function ( $row ) { return new Message( $row ); }, $rows ); wp_cache_set( $cache_key, $messages, 'aisentic', 300 ); return $messages; } /** * Get the newest messages for a conversation, oldest first. * * The limit in getByConversation() counts from the start of the thread, * so on a long conversation it keeps the first messages, not the last. * * @param string $conversation_uuid Conversation UUID. * @param int $limit Maximum number of messages. * @return Message[] */ public function get_recent_by_conversation( string $conversation_uuid, int $limit ): array { global $wpdb; $ver = $this->get_messages_cache_version( $conversation_uuid ); $cache_key = 'conv_recent_msgs_' . md5( $conversation_uuid ) . '_' . $limit . '_v' . $ver; $cached = wp_cache_get( $cache_key, 'aisentic' ); if ( false !== $cached ) { return $cached; } $rows = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $wpdb->prepare( "SELECT * FROM {$this->table} WHERE conversation_uuid = %s ORDER BY created_at DESC, id DESC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $conversation_uuid, $limit ), ARRAY_A ); $messages = array_map( function ( $row ) { return new Message( $row ); }, array_reverse( (array) $rows ) ); wp_cache_set( $cache_key, $messages, 'aisentic', 300 ); return $messages; } /** * Get the most recent message for a conversation. * * @param string $conversation_uuid Conversation UUID. * @return Message|null */ public function get_last_message( string $conversation_uuid ): ?Message { global $wpdb; $row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->prepare( "SELECT * FROM {$this->table} WHERE conversation_uuid = %s ORDER BY created_at DESC, id DESC LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared $conversation_uuid ), ARRAY_A ); return $row ? new Message( $row ) : null; } /** * Update a message's content and metadata. * * @param int $id Message ID. * @param string $conversation_uuid Conversation UUID (for cache invalidation). * @param string $content New message content. * @param array|null $metadata New metadata, or null to leave it unchanged. * @return bool */ public function update( int $id, string $conversation_uuid, string $content, ?array $metadata = null ): bool { global $wpdb; $data = [ 'content' => $content ]; $fmt = [ '%s' ]; if ( null !== $metadata ) { $data['metadata'] = $metadata ? wp_json_encode( $metadata ) : null; $fmt[] = '%s'; } $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $this->table, $data, [ 'id' => $id ], $fmt, [ '%d' ] ); if ( false === $result ) { return false; } wp_cache_delete( $this->get_message_cache_key( $id ), 'aisentic' ); $this->invalidate_conversation_messages_cache( $conversation_uuid ); return true; } /** * Delete messages for a conversation * * @param string $conversation_uuid Conversation UUID. * @return bool */ public function deleteByConversation( string $conversation_uuid ): bool { global $wpdb; $this->invalidate_conversation_messages_cache( $conversation_uuid ); $result = $wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $this->table, [ 'conversation_uuid' => $conversation_uuid ], [ '%s' ] ); return false !== $result; } /** * Delete messages from a specific index onwards * * @param string $conversation_uuid Conversation UUID. * @param int $from_index Starting index (0-based). * @return bool */ public function deleteFromIndex( string $conversation_uuid, int $from_index ): bool { global $wpdb; // Get all messages for the conversation ordered by created_at with increased limit $messages = $this->getByConversation( $conversation_uuid, 1000 ); // If index is out of bounds, return false if ( $from_index >= count( $messages ) ) { return false; } // Get IDs of messages to delete (from index onwards) $ids_to_delete = []; $msg_count = count( $messages ); for ( $i = $from_index; $i < $msg_count; $i++ ) { $ids_to_delete[] = $messages[ $i ]->id; } if ( empty( $ids_to_delete ) ) { return false; } // Delete messages by IDs // Table name is safe - $wpdb->prefix is controlled by WordPress core $table = $wpdb->prefix . 'aisentic_messages'; // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is trusted $placeholders = implode( ',', array_fill( 0, count( $ids_to_delete ), '%d' ) ); $query = $wpdb->prepare( "DELETE FROM `{$table}` WHERE id IN ($placeholders)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders is a generated list of %d markers; ids bound via prepare(). ...$ids_to_delete ); $result = $wpdb->query( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $query is built via $wpdb->prepare() above. if ( false !== $result ) { $this->invalidate_conversation_messages_cache( $conversation_uuid ); } return false !== $result; } /** * Get message count for a conversation * * @param string $conversation_uuid Conversation UUID. * @return int */ public function countByConversation( string $conversation_uuid ): int { global $wpdb; return (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter $wpdb->prepare( "SELECT COUNT(*) FROM {$this->table} WHERE conversation_uuid = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $this->table uses $wpdb->prefix $conversation_uuid ) ); } /** * Update metadata for a message * * @param int $id Message ID. * @param array $metadata New metadata array. * @return bool */ public function update_metadata( int $id, array $metadata ): bool { global $wpdb; $conversation_uuid = $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $wpdb->prepare( "SELECT conversation_uuid FROM {$this->table} WHERE id = %d", $id ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared ); $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $this->table, [ 'metadata' => wp_json_encode( $metadata ) ], [ 'id' => $id ], [ '%s' ], [ '%d' ] ); if ( false !== $result ) { wp_cache_delete( $this->get_message_cache_key( $id ), 'aisentic' ); if ( is_string( $conversation_uuid ) && '' !== $conversation_uuid ) { $this->invalidate_conversation_messages_cache( $conversation_uuid ); } } return false !== $result; } /** * Rate agent message * * @param int $message_id Message ID. * @param int $is_satisfied Satisfaction level (-1, 0, 1). * @return bool */ public function rateAgentMessage( $message_id, $is_satisfied ) { global $wpdb; wp_cache_delete( $this->get_message_cache_key( (int) $message_id ), 'aisentic' ); $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $this->table, [ 'is_satisfied' => $is_satisfied, ], [ 'id' => $message_id ], [ '%d' ] ); if ( false === $result ) { return false; } return true; } }