wpfunnels / includes / core / AI / ConversationStore.php

ConversationStore.php in WPFunnels – Funnel Builder for WooCommerce with Checkout & One Click Upsell 3.13.1, at includes/core/AI/ConversationStore.php

345 lines 9.8 KB

Raw Download Zip

1	<?php
2	/**
3	* ConversationStore — persistence for AI copilot conversations and messages.
4	*
5	* Every read/write path verifies ownership: conversations are strictly per-user.
6	*
7	* @package WPFunnels\AI
8	* @since 3.13.0
9	*/
10
11	namespace WPFunnels\AI;
12
13	defined( 'ABSPATH' ) \|\| exit;
14
15	/**
16	* Class ConversationStore
17	*/
18	class ConversationStore {
19
20	/**
21	* Conversations table suffix.
22	*/
23	public const CONVERSATIONS_TABLE = 'wpfnl_ai_conversations';
24
25	/**
26	* Messages table suffix.
27	*/
28	public const MESSAGES_TABLE = 'wpfnl_ai_messages';
29
30	/**
31	* Flag option set once the tables exist.
32	*/
33	private const READY_OPTION = '_wpfnl_ai_tables_ready';
34
35	/**
36	* Idempotently create the AI tables if missing. Covers the window before the
37	* migration runs and self-heals multisite subsites.
38	*
39	* @return void
40	*/
41	public static function ensureTables() {
42	if ( 'yes' === get_option( self::READY_OPTION ) ) {
43	return;
44	}
45
46	global $wpdb;
47
48	$conversations = $wpdb->prefix . self::CONVERSATIONS_TABLE;
49	$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $conversations ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
50
51	if ( $exists !== $conversations ) {
52	self::createTables();
53	}
54
55	update_option( self::READY_OPTION, 'yes', false );
56	}
57
58	/**
59	* Create both tables via dbDelta.
60	*
61	* @return void
62	*/
63	public static function createTables() {
64	global $wpdb;
65
66	require_once ABSPATH . 'wp-admin/includes/upgrade.php';
67
68	$wpdb->hide_errors();
69	$charset = $wpdb->get_charset_collate();
70	$conversations = $wpdb->prefix . self::CONVERSATIONS_TABLE;
71	$messages = $wpdb->prefix . self::MESSAGES_TABLE;
72
73	dbDelta(
74	"CREATE TABLE IF NOT EXISTS {$conversations} (
75	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
76	user_id bigint(20) unsigned NOT NULL DEFAULT 0,
77	title varchar(255) NOT NULL DEFAULT '',
78	provider varchar(50) NOT NULL DEFAULT '',
79	context_type varchar(50) NOT NULL DEFAULT '',
80	context_id bigint(20) unsigned DEFAULT NULL,
81	status varchar(30) NOT NULL DEFAULT 'idle',
82	pending longtext DEFAULT NULL,
83	created_at datetime DEFAULT NULL,
84	updated_at datetime DEFAULT NULL,
85	PRIMARY KEY (id),
86	KEY user_id (user_id),
87	KEY context (context_type, context_id)
88	) {$charset}"
89	);
90
91	dbDelta(
92	"CREATE TABLE IF NOT EXISTS {$messages} (
93	id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
94	conversation_id bigint(20) unsigned NOT NULL DEFAULT 0,
95	role varchar(20) NOT NULL DEFAULT 'user',
96	content longtext DEFAULT NULL,
97	meta longtext DEFAULT NULL,
98	created_at datetime DEFAULT NULL,
99	PRIMARY KEY (id),
100	KEY conversation_id (conversation_id)
101	) {$charset}"
102	);
103	}
104
105	/**
106	* Drop both tables. Used by uninstall only.
107	*
108	* @return void
109	*/
110	public static function dropTables() {
111	global $wpdb;
112
113	$conversations = $wpdb->prefix . self::CONVERSATIONS_TABLE;
114	$messages = $wpdb->prefix . self::MESSAGES_TABLE;
115
116	$wpdb->query( "DROP TABLE IF EXISTS {$messages}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
117	$wpdb->query( "DROP TABLE IF EXISTS {$conversations}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
118
119	delete_option( self::READY_OPTION );
120	}
121
122	/**
123	* Start a conversation.
124	*
125	* @param int $user_id Owner.
126	* @param string $provider Provider slug at creation time.
127	* @param string $context_type funnel\|step\|analytics\|dashboard.
128	* @param int $context_id Related object id, 0 for none.
129	* @param string $title Conversation title.
130	* @return int Conversation id.
131	*/
132	public static function createConversation( $user_id, $provider, $context_type, $context_id, $title ) {
133	self::ensureTables();
134
135	global $wpdb;
136
137	$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
138	$wpdb->prefix . self::CONVERSATIONS_TABLE,
139	[
140	'user_id' => (int) $user_id,
141	'title' => mb_substr( (string) $title, 0, 250 ),
142	'provider' => (string) $provider,
143	'context_type' => (string) $context_type,
144	'context_id' => $context_id ? (int) $context_id : null,
145	'status' => 'idle',
146	'created_at' => current_time( 'mysql' ),
147	'updated_at' => current_time( 'mysql' ),
148	]
149	);
150
151	$id = (int) $wpdb->insert_id;
152	if ( $id > 0 ) {
153	do_action( 'wpfunnels_ai_chat_started', $id, (int) $user_id, (string) $context_type, (int) $context_id );
154	}
155
156	return $id;
157	}
158
159	/**
160	* Fetch a conversation row with `pending` decoded.
161	*
162	* @param int $conversation_id Conversation id.
163	* @return array\|null
164	*/
165	public static function getConversation( $conversation_id ) {
166	global $wpdb;
167
168	$table = $wpdb->prefix . self::CONVERSATIONS_TABLE;
169	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
170	$row = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", (int) $conversation_id ), ARRAY_A );
171
172	if ( ! $row ) {
173	return null;
174	}
175
176	$row['pending'] = ! empty( $row['pending'] ) ? json_decode( $row['pending'], true ) : null;
177	return $row;
178	}
179
180	/**
181	* Fetch a conversation only when it belongs to the given user.
182	*
183	* @param int $conversation_id Conversation id.
184	* @param int $user_id Expected owner.
185	* @return array\|null
186	*/
187	public static function getOwnedConversation( $conversation_id, $user_id ) {
188	$row = self::getConversation( $conversation_id );
189	if ( ! $row \|\| (int) $row['user_id'] !== (int) $user_id ) {
190	return null;
191	}
192	return $row;
193	}
194
195	/**
196	* List a user's conversations, newest first.
197	*
198	* @param int $user_id Owner.
199	* @param string $context_type Optional context filter.
200	* @param int $context_id Optional context id filter.
201	* @param int $limit Page size.
202	* @param int $offset Offset.
203	* @return array
204	*/
205	public static function listConversations( $user_id, $context_type = '', $context_id = 0, $limit = 50, $offset = 0 ) {
206	self::ensureTables();
207
208	global $wpdb;
209
210	$table = $wpdb->prefix . self::CONVERSATIONS_TABLE;
211	$where = 'user_id = %d';
212	$args = [ (int) $user_id ];
213
214	if ( '' !== $context_type ) {
215	$where .= ' AND context_type = %s';
216	$args[] = $context_type;
217	}
218	if ( (int) $context_id > 0 ) {
219	$where .= ' AND context_id = %d';
220	$args[] = (int) $context_id;
221	}
222
223	$args[] = max( 1, (int) $limit );
224	$args[] = max( 0, (int) $offset );
225
226	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
227	$rows = $wpdb->get_results(
228	$wpdb->prepare(
229	"SELECT id, title, provider, context_type, context_id, status, created_at, updated_at
230	FROM {$table} WHERE {$where} ORDER BY id DESC LIMIT %d OFFSET %d",
231	$args
232	),
233	ARRAY_A
234	);
235
236	return is_array( $rows ) ? $rows : [];
237	}
238
239	/**
240	* Patch conversation fields. `pending` is JSON-encoded automatically.
241	*
242	* @param int $conversation_id Conversation id.
243	* @param array $fields Columns to update.
244	* @return void
245	*/
246	public static function updateConversation( $conversation_id, $fields ) {
247	global $wpdb;
248
249	if ( array_key_exists( 'pending', $fields ) && null !== $fields['pending'] ) {
250	$fields['pending'] = wp_json_encode( $fields['pending'] );
251	}
252	$fields['updated_at'] = current_time( 'mysql' );
253
254	$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
255	$wpdb->prefix . self::CONVERSATIONS_TABLE,
256	$fields,
257	[ 'id' => (int) $conversation_id ]
258	);
259	}
260
261	/**
262	* Delete a conversation and its messages (no FK cascade defined).
263	*
264	* @param int $conversation_id Conversation id.
265	* @return void
266	*/
267	public static function deleteConversation( $conversation_id ) {
268	global $wpdb;
269
270	$wpdb->delete( $wpdb->prefix . self::MESSAGES_TABLE, [ 'conversation_id' => (int) $conversation_id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
271	$wpdb->delete( $wpdb->prefix . self::CONVERSATIONS_TABLE, [ 'id' => (int) $conversation_id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery
272	}
273
274	/**
275	* Append a message.
276	*
277	* @param int $conversation_id Conversation id.
278	* @param string $role user\|assistant\|tool.
279	* @param array $content Normalized content.
280	* @param array $meta Provider, raw payload, usage.
281	* @return int Message id.
282	*/
283	public static function appendMessage( $conversation_id, $role, $content, $meta = [] ) {
284	global $wpdb;
285
286	$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery
287	$wpdb->prefix . self::MESSAGES_TABLE,
288	[
289	'conversation_id' => (int) $conversation_id,
290	'role' => (string) $role,
291	'content' => wp_json_encode( $content ),
292	'meta' => ! empty( $meta ) ? wp_json_encode( $meta ) : null,
293	'created_at' => current_time( 'mysql' ),
294	]
295	);
296
297	return (int) $wpdb->insert_id;
298	}
299
300	/**
301	* All messages of a conversation in order. Content/meta stay JSON strings;
302	* provider adapters decode lazily.
303	*
304	* @param int $conversation_id Conversation id.
305	* @return array
306	*/
307	public static function getMessages( $conversation_id ) {
308	global $wpdb;
309
310	$table = $wpdb->prefix . self::MESSAGES_TABLE;
311	// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery
312	$rows = $wpdb->get_results(
313	$wpdb->prepare(
314	"SELECT id, role, content, meta, created_at FROM {$table} WHERE conversation_id = %d ORDER BY id ASC",
315	(int) $conversation_id
316	),
317	ARRAY_A
318	);
319
320	return is_array( $rows ) ? $rows : [];
321	}
322
323	/**
324	* Number of assistant messages since the most recent user message — the
325	* loop-iteration counter for the current turn.
326	*
327	* @param array $messages Message rows in order.
328	* @return int
329	*/
330	public static function assistantStepsThisTurn( $messages ) {
331	$steps = 0;
332
333	for ( $i = count( $messages ) - 1; $i >= 0; $i-- ) {
334	if ( 'user' === $messages[ $i ]['role'] ) {
335	break;
336	}
337	if ( 'assistant' === $messages[ $i ]['role'] ) {
338	$steps++;
339	}
340	}
341
342	return $steps;
343	}
344	}
345