extendify / app / Agent / Controllers / ChatHistoryController.php

ChatHistoryController.php in Extendify 3.1.5, at app/Agent/Controllers/ChatHistoryController.php

220 lines 6.7 KB

Raw Download Zip

1	<?php
2
3	/**
4	* Store info about messages/events from chat
5	*/
6
7	namespace Extendify\Agent\Controllers;
8
9	defined('ABSPATH') \|\| die('No direct access.');
10
11	use Extendify\Shared\Services\Sanitizer;
12
13	/**
14	* The controller
15	*/
16
17	class ChatHistoryController
18	{
19	/**
20	* Initialize the controller and set up the database table.
21	*
22	* @return \WP_REST_Response
23	*/
24	public static function init()
25	{
26	self::setupChatHistoryTable();
27	}
28
29	/**
30	* Get the last 250 messages
31	*
32	* @return array
33	*/
34	public static function getChatHistory($user_id = null)
35	{
36	global $wpdb;
37	$table = $wpdb->prefix . 'extendify_agent_events';
38	$user_id = $user_id ?: get_current_user_id();
39
40	$results = $wpdb->get_results(
41	$wpdb->prepare(
42	"SELECT * FROM $table WHERE user_id = %d ORDER BY id DESC LIMIT 250",
43	$user_id
44	),
45	ARRAY_A
46	);
47	return array_map(function ($item) {
48	return [
49	'id' => $item['event_id'],
50	'type' => $item['type'],
51	'details' => json_decode($item['details'], true),
52	];
53	}, $results);
54	}
55
56	/**
57	* Delete the user's chat history, if the table exists.
58	*
59	* @param int\|null $user_id The user ID, defaults to the current user.
60	* @return void
61	*/
62	public static function clear($user_id = null)
63	{
64	global $wpdb;
65	$table = $wpdb->prefix . 'extendify_agent_events';
66	$user_id = $user_id ?: get_current_user_id();
67
68	if (!$wpdb->get_var("SHOW TABLES LIKE '$table'")) {
69	return;
70	}
71
72	$wpdb->query(
73	$wpdb->prepare("DELETE FROM $table WHERE user_id = %d", $user_id)
74	);
75	}
76
77	/**
78	* Return the data
79	*
80	* @return \WP_REST_Response
81	*/
82	public static function get()
83	{
84	$messages = self::getChatHistory();
85	$state = ['state' => ['messages' => Sanitizer::sanitizeArray($messages)]];
86	return new \WP_REST_Response($state);
87	}
88
89	/**
90	* Persist the data
91	*
92	* @param \WP_REST_Request $request - The request.
93	* @return \WP_REST_Response
94	*/
95	public static function store($request)
96	{
97	global $wpdb;
98	$table = $wpdb->prefix . 'extendify_agent_events';
99	$user_id = get_current_user_id();
100
101	$state = $request->get_param('state');
102	$parsed = is_string($state) ? json_decode($state, true) : $state;
103	$messages = $parsed['state']['messages'] ?? [];
104
105	// Find the latest event and only add new messages since then.
106	/* TODO: Maybe we may need a check like:
107	"if this message is assistant and so is the last, delete the last one" to avoid duplicates on retries."
108	*/
109	$latest = $wpdb->get_var(
110	$wpdb->prepare(
111	"SELECT event_id FROM $table WHERE user_id = %d ORDER BY created_at DESC LIMIT 1",
112	$user_id
113	)
114	);
115	$startIndex = 0;
116	if ($latest) {
117	foreach ($messages as $i => $msg) {
118	// Tool results land on the newest row after insert; skipping it loses them.
119	if ($msg['id'] === $latest) {
120	$startIndex = $i;
121	break;
122	}
123	}
124	}
125
126	$toInsert = array_slice($messages, $startIndex);
127	foreach ($toInsert as $msg) {
128	self::upsertEvent($msg, $user_id);
129	}
130
131	return self::get();
132	}
133
134	/**
135	* Upsert an event into the database.
136	*
137	* @param array $msg The message data.
138	* @param int $user_id The user ID.
139	* @return void
140	*/
141	private static function upsertEvent($msg, $user_id)
142	{
143	global $wpdb;
144	$table = $wpdb->prefix . 'extendify_agent_events';
145	$created_at = current_time('mysql');
146	$event_id = Sanitizer::sanitizeText($msg['id']);
147	$type = Sanitizer::sanitizeText($msg['type']);
148	$details = wp_json_encode(Sanitizer::sanitizeArray($msg['details']));
149
150	$sql = $wpdb->prepare(
151	"INSERT INTO $table (created_at, event_id, type, details, user_id)
152	VALUES (%s, %s, %s, %s, %d)
153	ON DUPLICATE KEY UPDATE
154	created_at = VALUES(created_at),
155	type = VALUES(type),
156	details = VALUES(details)",
157	$created_at,
158	$event_id,
159	$type,
160	$details,
161	$user_id
162	);
163	$wpdb->query($sql);
164	}
165
166	/**
167	* Ensures the custom table exists and is up to date for MySQL.
168	* Creates the table if missing, adds missing columns, and ensures an index on user_id.
169	*
170	* @return void
171	*/
172	private static function setupChatHistoryTable()
173	{
174	global $wpdb;
175	$table = $wpdb->prefix . 'extendify_agent_events';
176	$columns = [
177	'id' => "BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY",
178	'created_at' => "DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP",
179	'event_id' => "VARCHAR(64) NOT NULL",
180	'type' => "VARCHAR(64) NOT NULL",
181	'details' => "LONGTEXT",
182	'user_id' => "BIGINT UNSIGNED NOT NULL"
183	];
184
185	// Check if the table exists and create it if not
186	$exists = $wpdb->get_var("SHOW TABLES LIKE '$table'");
187	if (!$exists) {
188	$cols = [];
189	foreach ($columns as $name => $type) {
190	$cols[] = "$name $type";
191	}
192	$indexes = "INDEX(user_id), UNIQUE INDEX unique_event_id (event_id, user_id)";
193	$sql = "CREATE TABLE $table (" . implode(',', $cols) . ", $indexes)";
194	$sql .= " " . $wpdb->get_charset_collate() . ";";
195	$wpdb->query($sql);
196	return;
197	}
198
199	// Check existing columns and add missing ones
200	$existingCols = array_column($wpdb->get_results("SHOW COLUMNS FROM $table", ARRAY_A), 'Field');
201	foreach ($columns as $name => $type) {
202	if (!in_array($name, $existingCols, true)) {
203	$wpdb->query("ALTER TABLE $table ADD COLUMN $name $type");
204	}
205	}
206
207	// Unique index on (event_id, user_id)
208	$uniqueIndex = $wpdb->get_results("SHOW INDEX FROM $table WHERE Key_name = 'unique_event_id'", ARRAY_A);
209	if (empty($uniqueIndex)) {
210	$wpdb->query("CREATE UNIQUE INDEX unique_event_id ON $table(event_id, user_id)");
211	}
212
213	// Regular index on user_id
214	$userIndex = $wpdb->get_results("SHOW INDEX FROM $table WHERE Key_name = 'user_id'", ARRAY_A);
215	if (empty($userIndex)) {
216	$wpdb->query("CREATE INDEX user_id ON $table(user_id)");
217	}
218	}
219	}
220