| 1 |
<?php |
| 2 |
|
| 3 |
namespace wpforo\classes; |
| 4 |
|
| 5 |
// Exit if accessed directly |
| 6 |
if ( ! defined( 'ABSPATH' ) ) exit; |
| 7 |
|
| 8 |
/** |
| 9 |
* Local Vector Storage for AI Embeddings |
| 10 |
* |
| 11 |
* Handles storing and searching embeddings in WordPress MySQL database |
| 12 |
* as an alternative to cloud-based vector storage (gVectors AI Services on AWS Cloud). |
| 13 |
* |
| 14 |
* Features: |
| 15 |
* - Binary packed vector storage (efficient BLOB) |
| 16 |
* - Pre-computed magnitudes for fast cosine similarity |
| 17 |
* - Similarity cache with TTL |
| 18 |
* - PHP-based cosine similarity calculation |
| 19 |
* |
| 20 |
* @since 3.0.0 |
| 21 |
*/ |
| 22 |
class VectorStorageLocal { |
| 23 |
|
| 24 |
/** |
| 25 |
* Default cache TTL in seconds (1 hour) |
| 26 |
*/ |
| 27 |
const CACHE_TTL = 3600; |
| 28 |
|
| 29 |
/** |
| 30 |
* Maximum similar items to cache per source |
| 31 |
*/ |
| 32 |
const MAX_CACHED_SIMILAR = 20; |
| 33 |
|
| 34 |
/** |
| 35 |
* Post count threshold for performance warning |
| 36 |
*/ |
| 37 |
const PERFORMANCE_THRESHOLD = 5000000; |
| 38 |
|
| 39 |
/** |
| 40 |
* Default vector dimensions |
| 41 |
*/ |
| 42 |
const DEFAULT_DIMENSIONS = 1024; |
| 43 |
|
| 44 |
/** |
| 45 |
* Constructor |
| 46 |
*/ |
| 47 |
public function __construct() { |
| 48 |
// Note: Cron registration moved to VectorStorageManager::register_cron_hooks() |
| 49 |
// This ensures the cleanup callback is available when the cron fires |
| 50 |
} |
| 51 |
|
| 52 |
/** |
| 53 |
* Store an embedding vector for a post |
| 54 |
* |
| 55 |
* @param int $topicid Topic ID |
| 56 |
* @param int $postid Post ID (chunk) |
| 57 |
* @param int $forumid Forum ID |
| 58 |
* @param int $userid User ID |
| 59 |
* @param array $vector Float array of embeddings |
| 60 |
* @param string $content_hash MD5 hash of content |
| 61 |
* @param string $content_preview Content preview (full chunk text) |
| 62 |
* @param string $model_name Model used for embedding |
| 63 |
* @return int|false Insert ID or false on failure |
| 64 |
*/ |
| 65 |
public function store_embedding( $topicid, $postid, $forumid, $userid, $vector, $content_hash, $content_preview = '', $model_name = 'amazon.titan-embed-text-v2', $content_type = 'forum' ) { |
| 66 |
global $wpdb; |
| 67 |
|
| 68 |
if ( empty( $vector ) || ! is_array( $vector ) ) { |
| 69 |
return false; |
| 70 |
} |
| 71 |
|
| 72 |
$dimensions = count( $vector ); |
| 73 |
$magnitude = $this->calculate_magnitude( $vector ); |
| 74 |
|
| 75 |
// Normalize vector for faster similarity computation |
| 76 |
$normalized_vector = $this->normalize_vector( $vector, $magnitude ); |
| 77 |
$binary_vector = $this->pack_vector( $normalized_vector ); |
| 78 |
|
| 79 |
// Check if embedding already exists |
| 80 |
$existing = $wpdb->get_var( $wpdb->prepare( |
| 81 |
"SELECT id FROM " . WPF()->tables->ai_embeddings . " WHERE postid = %d AND model_name = %s", |
| 82 |
$postid, |
| 83 |
$model_name |
| 84 |
) ); |
| 85 |
|
| 86 |
$data = [ |
| 87 |
'topicid' => $topicid, |
| 88 |
'postid' => $postid, |
| 89 |
'forumid' => $forumid, |
| 90 |
'userid' => $userid, |
| 91 |
'embedding_vector' => $binary_vector, |
| 92 |
'vector_dimensions' => $dimensions, |
| 93 |
'vector_magnitude' => 1.0, // Normalized vectors have magnitude 1 |
| 94 |
'model_name' => $model_name, |
| 95 |
'content_hash' => $content_hash, |
| 96 |
'content_preview' => $content_preview, |
| 97 |
'content_type' => $content_type, |
| 98 |
]; |
| 99 |
|
| 100 |
if ( $existing ) { |
| 101 |
// Update existing |
| 102 |
$result = $wpdb->update( |
| 103 |
WPF()->tables->ai_embeddings, |
| 104 |
$data, |
| 105 |
[ 'id' => $existing ], |
| 106 |
[ '%d', '%d', '%d', '%d', '%s', '%d', '%f', '%s', '%s', '%s', '%s' ], |
| 107 |
[ '%d' ] |
| 108 |
); |
| 109 |
|
| 110 |
// Invalidate cache for this post |
| 111 |
$this->invalidate_cache( 'post', $postid ); |
| 112 |
|
| 113 |
return $existing; |
| 114 |
} else { |
| 115 |
// Insert new |
| 116 |
$result = $wpdb->insert( |
| 117 |
WPF()->tables->ai_embeddings, |
| 118 |
$data, |
| 119 |
[ '%d', '%d', '%d', '%d', '%s', '%d', '%f', '%s', '%s', '%s', '%s' ] |
| 120 |
); |
| 121 |
|
| 122 |
return $result ? $wpdb->insert_id : false; |
| 123 |
} |
| 124 |
} |
| 125 |
|
| 126 |
/** |
| 127 |
* Get embedding for a post |
| 128 |
* |
| 129 |
* @param int $postid Post ID |
| 130 |
* @param string $model_name Model name |
| 131 |
* @return array|null Embedding data or null |
| 132 |
*/ |
| 133 |
public function get_embedding( $postid, $model_name = 'amazon.titan-embed-text-v2' ) { |
| 134 |
global $wpdb; |
| 135 |
|
| 136 |
$row = $wpdb->get_row( $wpdb->prepare( |
| 137 |
"SELECT * FROM " . WPF()->tables->ai_embeddings . " WHERE postid = %d AND model_name = %s", |
| 138 |
$postid, |
| 139 |
$model_name |
| 140 |
), ARRAY_A ); |
| 141 |
|
| 142 |
if ( ! $row ) { |
| 143 |
return null; |
| 144 |
} |
| 145 |
|
| 146 |
$row['vector'] = $this->unpack_vector( $row['embedding_vector'] ); |
| 147 |
unset( $row['embedding_vector'] ); |
| 148 |
|
| 149 |
return $row; |
| 150 |
} |
| 151 |
|
| 152 |
/** |
| 153 |
* Delete embedding for a post |
| 154 |
* |
| 155 |
* @param int $postid Post ID |
| 156 |
* @return bool Success |
| 157 |
*/ |
| 158 |
public function delete_embedding( $postid ) { |
| 159 |
global $wpdb; |
| 160 |
|
| 161 |
$result = $wpdb->delete( |
| 162 |
WPF()->tables->ai_embeddings, |
| 163 |
[ 'postid' => $postid ], |
| 164 |
[ '%d' ] |
| 165 |
); |
| 166 |
|
| 167 |
// Also delete from cache |
| 168 |
$this->invalidate_cache( 'post', $postid ); |
| 169 |
|
| 170 |
return $result !== false; |
| 171 |
} |
| 172 |
|
| 173 |
/** |
| 174 |
* Delete all embeddings for a topic |
| 175 |
* |
| 176 |
* @param int $topicid Topic ID |
| 177 |
* @return int Number of deleted rows |
| 178 |
*/ |
| 179 |
public function delete_topic_embeddings( $topicid ) { |
| 180 |
global $wpdb; |
| 181 |
|
| 182 |
// Get all postids first for cache invalidation |
| 183 |
$postids = $wpdb->get_col( $wpdb->prepare( |
| 184 |
"SELECT postid FROM " . WPF()->tables->ai_embeddings . " WHERE topicid = %d", |
| 185 |
$topicid |
| 186 |
) ); |
| 187 |
|
| 188 |
$result = $wpdb->delete( |
| 189 |
WPF()->tables->ai_embeddings, |
| 190 |
[ 'topicid' => $topicid ], |
| 191 |
[ '%d' ] |
| 192 |
); |
| 193 |
|
| 194 |
// Invalidate cache for all posts |
| 195 |
foreach ( $postids as $postid ) { |
| 196 |
$this->invalidate_cache( 'post', $postid ); |
| 197 |
} |
| 198 |
|
| 199 |
return $result; |
| 200 |
} |
| 201 |
|
| 202 |
/** |
| 203 |
* Delete WordPress CPT embeddings from local storage. |
| 204 |
* |
| 205 |
* @param array|null $post_types Post types to delete (e.g., ['docs', 'page']). Null = all non-forum. |
| 206 |
* @param array|null $post_ids Specific post IDs to delete. |
| 207 |
* @return int Number of deleted rows. |
| 208 |
*/ |
| 209 |
public function delete_wp_embeddings( $post_types = null, $post_ids = null ) { |
| 210 |
global $wpdb; |
| 211 |
|
| 212 |
// Guard: ensure tables object and ai_embeddings property exist |
| 213 |
if ( ! WPF()->tables || ! isset( WPF()->tables->ai_embeddings ) ) { |
| 214 |
return 0; |
| 215 |
} |
| 216 |
|
| 217 |
$table = WPF()->tables->ai_embeddings; |
| 218 |
|
| 219 |
if ( ! empty( $post_ids ) ) { |
| 220 |
// Delete specific post IDs |
| 221 |
$placeholders = implode( ', ', array_fill( 0, count( $post_ids ), '%d' ) ); |
| 222 |
$deleted = $wpdb->query( |
| 223 |
$wpdb->prepare( |
| 224 |
"DELETE FROM $table WHERE postid IN ($placeholders) AND content_type != 'forum'", |
| 225 |
...$post_ids |
| 226 |
) |
| 227 |
); |
| 228 |
} elseif ( ! empty( $post_types ) ) { |
| 229 |
// Delete by content_type |
| 230 |
$placeholders = implode( ', ', array_fill( 0, count( $post_types ), '%s' ) ); |
| 231 |
$deleted = $wpdb->query( |
| 232 |
$wpdb->prepare( |
| 233 |
"DELETE FROM $table WHERE content_type IN ($placeholders)", |
| 234 |
...$post_types |
| 235 |
) |
| 236 |
); |
| 237 |
} else { |
| 238 |
// Delete all non-forum embeddings |
| 239 |
$deleted = $wpdb->query( |
| 240 |
"DELETE FROM $table WHERE content_type != 'forum'" |
| 241 |
); |
| 242 |
} |
| 243 |
|
| 244 |
// Clear the embeddings cache |
| 245 |
$wpdb->query( "DELETE FROM " . WPF()->tables->ai_embeddings_cache ); |
| 246 |
|
| 247 |
return (int) $deleted; |
| 248 |
} |
| 249 |
|
| 250 |
/** |
| 251 |
* Semantic search using cosine similarity |
| 252 |
* |
| 253 |
* @param array $query_vector Query embedding vector |
| 254 |
* @param int $limit Maximum results |
| 255 |
* @param array $filters Optional filters: forumid, userid, etc. |
| 256 |
* @return array Search results with scores |
| 257 |
*/ |
| 258 |
public function semantic_search( $query_vector, $limit = 10, $filters = [] ) { |
| 259 |
global $wpdb; |
| 260 |
|
| 261 |
if ( empty( $query_vector ) ) { |
| 262 |
return []; |
| 263 |
} |
| 264 |
|
| 265 |
// Extract minimum score threshold from filters (0-1 scale, 0 = no filtering) |
| 266 |
$min_score = isset( $filters['min_score'] ) ? (float) $filters['min_score'] : 0; |
| 267 |
|
| 268 |
// Normalize query vector |
| 269 |
$query_magnitude = $this->calculate_magnitude( $query_vector ); |
| 270 |
$normalized_query = $this->normalize_vector( $query_vector, $query_magnitude ); |
| 271 |
|
| 272 |
// Build WHERE clause for filters |
| 273 |
$where = []; |
| 274 |
$values = []; |
| 275 |
|
| 276 |
if ( ! empty( $filters['forumid'] ) ) { |
| 277 |
$where[] = 'forumid = %d'; |
| 278 |
$values[] = (int) $filters['forumid']; |
| 279 |
} |
| 280 |
|
| 281 |
if ( ! empty( $filters['forumids'] ) && is_array( $filters['forumids'] ) ) { |
| 282 |
$placeholders = implode( ',', array_fill( 0, count( $filters['forumids'] ), '%d' ) ); |
| 283 |
$where[] = "forumid IN ($placeholders)"; |
| 284 |
$values = array_merge( $values, array_map( 'intval', $filters['forumids'] ) ); |
| 285 |
} |
| 286 |
|
| 287 |
if ( ! empty( $filters['userid'] ) ) { |
| 288 |
$where[] = 'userid = %d'; |
| 289 |
$values[] = (int) $filters['userid']; |
| 290 |
} |
| 291 |
|
| 292 |
if ( ! empty( $filters['exclude_topicids'] ) && is_array( $filters['exclude_topicids'] ) ) { |
| 293 |
$placeholders = implode( ',', array_fill( 0, count( $filters['exclude_topicids'] ), '%d' ) ); |
| 294 |
$where[] = "topicid NOT IN ($placeholders)"; |
| 295 |
$values = array_merge( $values, array_map( 'intval', $filters['exclude_topicids'] ) ); |
| 296 |
} |
| 297 |
|
| 298 |
$where_sql = ! empty( $where ) ? 'WHERE ' . implode( ' AND ', $where ) : ''; |
| 299 |
|
| 300 |
// Process embeddings in batches to avoid memory exhaustion on large datasets. |
| 301 |
// Each batch loads ~2000 rows (~9MB with 1024-dim vectors), keeping peak memory safe. |
| 302 |
$batch_size = 2000; |
| 303 |
$offset = 0; |
| 304 |
$top_results = []; |
| 305 |
|
| 306 |
$table = WPF()->tables->ai_embeddings; |
| 307 |
|
| 308 |
while ( true ) { |
| 309 |
$batch_query = "SELECT id, topicid, postid, forumid, userid, embedding_vector, vector_dimensions, content_preview, content_type |
| 310 |
FROM {$table} {$where_sql} |
| 311 |
ORDER BY id ASC LIMIT %d OFFSET %d"; |
| 312 |
$batch_values = array_merge( $values, [ $batch_size, $offset ] ); |
| 313 |
$batch_query = $wpdb->prepare( $batch_query, $batch_values ); |
| 314 |
|
| 315 |
$rows = $wpdb->get_results( $batch_query, ARRAY_A ); |
| 316 |
|
| 317 |
if ( empty( $rows ) ) { |
| 318 |
break; |
| 319 |
} |
| 320 |
|
| 321 |
// Calculate similarity for each embedding in this batch |
| 322 |
foreach ( $rows as $row ) { |
| 323 |
$stored_vector = $this->unpack_vector( $row['embedding_vector'] ); |
| 324 |
$similarity = $this->dot_product( $normalized_query, $stored_vector ); |
| 325 |
|
| 326 |
// Skip results below minimum score threshold |
| 327 |
if ( $min_score > 0 && $similarity < $min_score ) { |
| 328 |
continue; |
| 329 |
} |
| 330 |
|
| 331 |
$top_results[] = [ |
| 332 |
'id' => $row['id'], |
| 333 |
'topicid' => $row['topicid'], |
| 334 |
'postid' => $row['postid'], |
| 335 |
'forumid' => $row['forumid'], |
| 336 |
'userid' => $row['userid'], |
| 337 |
'similarity' => $similarity, |
| 338 |
'content_preview' => $row['content_preview'], |
| 339 |
'content_type' => $row['content_type'] ?? 'forum', |
| 340 |
]; |
| 341 |
} |
| 342 |
|
| 343 |
// Trim accumulated results to top N to bound memory growth |
| 344 |
if ( count( $top_results ) > $limit * 3 ) { |
| 345 |
usort( $top_results, function( $a, $b ) { |
| 346 |
return $b['similarity'] <=> $a['similarity']; |
| 347 |
} ); |
| 348 |
$top_results = array_slice( $top_results, 0, $limit ); |
| 349 |
} |
| 350 |
|
| 351 |
// If fewer rows than batch size, we've processed everything |
| 352 |
if ( count( $rows ) < $batch_size ) { |
| 353 |
break; |
| 354 |
} |
| 355 |
|
| 356 |
$offset += $batch_size; |
| 357 |
|
| 358 |
// Free batch memory before loading next batch |
| 359 |
unset( $rows ); |
| 360 |
} |
| 361 |
|
| 362 |
if ( empty( $top_results ) ) { |
| 363 |
return []; |
| 364 |
} |
| 365 |
|
| 366 |
// Final sort by similarity (descending) |
| 367 |
usort( $top_results, function( $a, $b ) { |
| 368 |
return $b['similarity'] <=> $a['similarity']; |
| 369 |
} ); |
| 370 |
|
| 371 |
// Return top N results |
| 372 |
return array_slice( $top_results, 0, $limit ); |
| 373 |
} |
| 374 |
|
| 375 |
/** |
| 376 |
* Find similar items with caching |
| 377 |
* |
| 378 |
* @param string $source_type 'topic' or 'post' |
| 379 |
* @param int $source_id Source item ID |
| 380 |
* @param int $limit Maximum results |
| 381 |
* @param bool $force_refresh Force cache refresh |
| 382 |
* @return array Similar items with scores |
| 383 |
*/ |
| 384 |
public function find_similar( $source_type, $source_id, $limit = 10, $force_refresh = false ) { |
| 385 |
// Check cache first |
| 386 |
if ( ! $force_refresh ) { |
| 387 |
$cached = $this->get_cached_similar( $source_type, $source_id, $limit ); |
| 388 |
if ( $cached !== null ) { |
| 389 |
return $cached; |
| 390 |
} |
| 391 |
} |
| 392 |
|
| 393 |
// Get source embedding |
| 394 |
if ( $source_type === 'topic' ) { |
| 395 |
// Get first post's embedding for topic |
| 396 |
global $wpdb; |
| 397 |
$first_postid = $wpdb->get_var( $wpdb->prepare( |
| 398 |
"SELECT postid FROM " . WPF()->tables->ai_embeddings . " WHERE topicid = %d ORDER BY postid ASC LIMIT 1", |
| 399 |
$source_id |
| 400 |
) ); |
| 401 |
if ( ! $first_postid ) { |
| 402 |
return []; |
| 403 |
} |
| 404 |
$embedding = $this->get_embedding( $first_postid ); |
| 405 |
} else { |
| 406 |
$embedding = $this->get_embedding( $source_id ); |
| 407 |
} |
| 408 |
|
| 409 |
if ( ! $embedding || empty( $embedding['vector'] ) ) { |
| 410 |
return []; |
| 411 |
} |
| 412 |
|
| 413 |
// Search for similar items |
| 414 |
$filters = [ |
| 415 |
'exclude_topicids' => [ $embedding['topicid'] ], // Exclude self |
| 416 |
]; |
| 417 |
|
| 418 |
$results = $this->semantic_search( $embedding['vector'], self::MAX_CACHED_SIMILAR, $filters ); |
| 419 |
|
| 420 |
// Group by topic (forum) or post (CPT) and take best match per group |
| 421 |
$by_group = []; |
| 422 |
foreach ( $results as $result ) { |
| 423 |
$content_type = $result['content_type'] ?? 'forum'; |
| 424 |
if ( $content_type !== 'forum' ) { |
| 425 |
// WordPress CPT: group by postid (topicid is 0 for all CPT) |
| 426 |
$group_key = 'wp_' . $result['postid']; |
| 427 |
} else { |
| 428 |
// Forum: group by topicid |
| 429 |
$group_key = 'topic_' . $result['topicid']; |
| 430 |
} |
| 431 |
if ( ! isset( $by_group[ $group_key ] ) || $result['similarity'] > $by_group[ $group_key ]['similarity'] ) { |
| 432 |
$by_group[ $group_key ] = $result; |
| 433 |
} |
| 434 |
} |
| 435 |
|
| 436 |
// Re-sort and limit |
| 437 |
$similar = array_values( $by_group ); |
| 438 |
usort( $similar, function( $a, $b ) { |
| 439 |
return $b['similarity'] <=> $a['similarity']; |
| 440 |
} ); |
| 441 |
$similar = array_slice( $similar, 0, self::MAX_CACHED_SIMILAR ); |
| 442 |
|
| 443 |
// Cache results |
| 444 |
$this->cache_similar( $source_type, $source_id, 'topic', $similar ); |
| 445 |
|
| 446 |
return array_slice( $similar, 0, $limit ); |
| 447 |
} |
| 448 |
|
| 449 |
/** |
| 450 |
* Get cached similar items |
| 451 |
* |
| 452 |
* @param string $source_type Source type |
| 453 |
* @param int $source_id Source ID |
| 454 |
* @param int $limit Maximum results |
| 455 |
* @return array|null Cached results or null if not cached/expired |
| 456 |
*/ |
| 457 |
private function get_cached_similar( $source_type, $source_id, $limit ) { |
| 458 |
global $wpdb; |
| 459 |
|
| 460 |
$now = current_time( 'mysql' ); |
| 461 |
|
| 462 |
$results = $wpdb->get_results( $wpdb->prepare( |
| 463 |
"SELECT similar_id, similarity_score |
| 464 |
FROM " . WPF()->tables->ai_embeddings_cache . " |
| 465 |
WHERE source_type = %s AND source_id = %d AND expires_at > %s |
| 466 |
ORDER BY rank_position ASC |
| 467 |
LIMIT %d", |
| 468 |
$source_type, |
| 469 |
$source_id, |
| 470 |
$now, |
| 471 |
$limit |
| 472 |
), ARRAY_A ); |
| 473 |
|
| 474 |
if ( empty( $results ) ) { |
| 475 |
return null; |
| 476 |
} |
| 477 |
|
| 478 |
// Enrich with topic data |
| 479 |
$enriched = []; |
| 480 |
foreach ( $results as $row ) { |
| 481 |
$enriched[] = [ |
| 482 |
'topicid' => (int) $row['similar_id'], |
| 483 |
'similarity' => (float) $row['similarity_score'], |
| 484 |
]; |
| 485 |
} |
| 486 |
|
| 487 |
return $enriched; |
| 488 |
} |
| 489 |
|
| 490 |
/** |
| 491 |
* Cache similar items |
| 492 |
* |
| 493 |
* @param string $source_type Source type |
| 494 |
* @param int $source_id Source ID |
| 495 |
* @param string $similar_type Similar item type |
| 496 |
* @param array $similar Similar items |
| 497 |
*/ |
| 498 |
private function cache_similar( $source_type, $source_id, $similar_type, $similar ) { |
| 499 |
global $wpdb; |
| 500 |
|
| 501 |
// Delete existing cache for this source |
| 502 |
$wpdb->delete( |
| 503 |
WPF()->tables->ai_embeddings_cache, |
| 504 |
[ |
| 505 |
'source_type' => $source_type, |
| 506 |
'source_id' => $source_id, |
| 507 |
], |
| 508 |
[ '%s', '%d' ] |
| 509 |
); |
| 510 |
|
| 511 |
// Insert new cache entries |
| 512 |
$expires_at = date( 'Y-m-d H:i:s', time() + self::CACHE_TTL ); |
| 513 |
|
| 514 |
foreach ( $similar as $rank => $item ) { |
| 515 |
$wpdb->insert( |
| 516 |
WPF()->tables->ai_embeddings_cache, |
| 517 |
[ |
| 518 |
'source_type' => $source_type, |
| 519 |
'source_id' => $source_id, |
| 520 |
'similar_type' => $similar_type, |
| 521 |
'similar_id' => $item['topicid'], |
| 522 |
'similarity_score' => $item['similarity'], |
| 523 |
'rank_position' => $rank + 1, |
| 524 |
'expires_at' => $expires_at, |
| 525 |
], |
| 526 |
[ '%s', '%d', '%s', '%d', '%f', '%d', '%s' ] |
| 527 |
); |
| 528 |
} |
| 529 |
} |
| 530 |
|
| 531 |
/** |
| 532 |
* Invalidate cache for an item |
| 533 |
* |
| 534 |
* @param string $type Item type |
| 535 |
* @param int $id Item ID |
| 536 |
*/ |
| 537 |
public function invalidate_cache( $type, $id ) { |
| 538 |
global $wpdb; |
| 539 |
|
| 540 |
// Delete where this item is the source |
| 541 |
$wpdb->delete( |
| 542 |
WPF()->tables->ai_embeddings_cache, |
| 543 |
[ |
| 544 |
'source_type' => $type, |
| 545 |
'source_id' => $id, |
| 546 |
], |
| 547 |
[ '%s', '%d' ] |
| 548 |
); |
| 549 |
|
| 550 |
// Delete where this item is in similar results |
| 551 |
$wpdb->delete( |
| 552 |
WPF()->tables->ai_embeddings_cache, |
| 553 |
[ |
| 554 |
'similar_type' => $type, |
| 555 |
'similar_id' => $id, |
| 556 |
], |
| 557 |
[ '%s', '%d' ] |
| 558 |
); |
| 559 |
} |
| 560 |
|
| 561 |
/** |
| 562 |
* Cleanup expired cache entries |
| 563 |
*/ |
| 564 |
public function cleanup_expired_cache() { |
| 565 |
global $wpdb; |
| 566 |
|
| 567 |
$now = current_time( 'mysql' ); |
| 568 |
|
| 569 |
$wpdb->query( $wpdb->prepare( |
| 570 |
"DELETE FROM " . WPF()->tables->ai_embeddings_cache . " WHERE expires_at < %s", |
| 571 |
$now |
| 572 |
) ); |
| 573 |
} |
| 574 |
|
| 575 |
/** |
| 576 |
* Get embedding statistics |
| 577 |
* |
| 578 |
* @return array Statistics |
| 579 |
*/ |
| 580 |
public function get_stats() { |
| 581 |
global $wpdb; |
| 582 |
|
| 583 |
$stats = [ |
| 584 |
'total_embeddings' => 0, |
| 585 |
'total_topics' => 0, |
| 586 |
'total_posts' => 0, |
| 587 |
'cache_entries' => 0, |
| 588 |
'storage_size_mb' => 0, |
| 589 |
'last_indexed_at' => null, |
| 590 |
]; |
| 591 |
|
| 592 |
// Combined query: total embeddings, unique topics, unique posts, last indexed |
| 593 |
// (4 queries → 1 on the same table) |
| 594 |
$combined = $wpdb->get_row( |
| 595 |
"SELECT COUNT(*) as total_embeddings, |
| 596 |
COUNT(DISTINCT topicid) as total_topics, |
| 597 |
COUNT(DISTINCT postid) as total_posts, |
| 598 |
MAX(updated_at) as last_indexed_at |
| 599 |
FROM " . WPF()->tables->ai_embeddings, |
| 600 |
ARRAY_A |
| 601 |
); |
| 602 |
if ( $combined ) { |
| 603 |
$stats['total_embeddings'] = (int) $combined['total_embeddings']; |
| 604 |
$stats['total_topics'] = (int) $combined['total_topics']; |
| 605 |
$stats['total_posts'] = (int) $combined['total_posts']; |
| 606 |
$stats['last_indexed_at'] = $combined['last_indexed_at']; |
| 607 |
} |
| 608 |
|
| 609 |
// Cache entries (separate table) |
| 610 |
$stats['cache_entries'] = (int) $wpdb->get_var( |
| 611 |
"SELECT COUNT(*) FROM " . WPF()->tables->ai_embeddings_cache |
| 612 |
); |
| 613 |
|
| 614 |
// Storage size (approximate) |
| 615 |
$table_name = WPF()->tables->ai_embeddings; |
| 616 |
$table_status = $wpdb->get_row( |
| 617 |
$wpdb->prepare( "SHOW TABLE STATUS WHERE Name = %s", $table_name ) |
| 618 |
); |
| 619 |
if ( $table_status ) { |
| 620 |
$stats['storage_size_mb'] = round( ( $table_status->Data_length + $table_status->Index_length ) / 1024 / 1024, 2 ); |
| 621 |
} |
| 622 |
|
| 623 |
return $stats; |
| 624 |
} |
| 625 |
|
| 626 |
/** |
| 627 |
* Get counts of indexed WordPress CPT content grouped by post type. |
| 628 |
* |
| 629 |
* Queries the ai_embeddings table for non-forum content and returns |
| 630 |
* counts keyed by wp_{content_type} (e.g., wp_docs, wp_page, wp_post). |
| 631 |
* This matches the format returned by the cloud API's /rag/wordpress/status. |
| 632 |
* |
| 633 |
* @return array<string, int> Post type => count (e.g., ['wp_docs' => 145, 'wp_page' => 20]) |
| 634 |
*/ |
| 635 |
public function get_wp_indexed_counts() { |
| 636 |
global $wpdb; |
| 637 |
|
| 638 |
// Guard: ensure tables object and ai_embeddings property exist |
| 639 |
if ( ! WPF()->tables || ! isset( WPF()->tables->ai_embeddings ) ) { |
| 640 |
return []; |
| 641 |
} |
| 642 |
|
| 643 |
$results = $wpdb->get_results( |
| 644 |
"SELECT content_type, COUNT(*) as cnt |
| 645 |
FROM " . WPF()->tables->ai_embeddings . " |
| 646 |
WHERE content_type != 'forum' |
| 647 |
GROUP BY content_type", |
| 648 |
ARRAY_A |
| 649 |
); |
| 650 |
|
| 651 |
$counts = []; |
| 652 |
if ( $results ) { |
| 653 |
foreach ( $results as $row ) { |
| 654 |
$counts[ 'wp_' . $row['content_type'] ] = (int) $row['cnt']; |
| 655 |
} |
| 656 |
} |
| 657 |
|
| 658 |
return $counts; |
| 659 |
} |
| 660 |
|
| 661 |
/** |
| 662 |
* Get all indexed WordPress post IDs (non-forum content) |
| 663 |
* |
| 664 |
* @return array Array of post IDs that have been indexed |
| 665 |
*/ |
| 666 |
public function get_wp_indexed_post_ids() { |
| 667 |
global $wpdb; |
| 668 |
|
| 669 |
if ( ! WPF()->tables || ! isset( WPF()->tables->ai_embeddings ) ) { |
| 670 |
return []; |
| 671 |
} |
| 672 |
|
| 673 |
$results = $wpdb->get_col( |
| 674 |
"SELECT DISTINCT postid FROM " . WPF()->tables->ai_embeddings . " WHERE content_type != 'forum'" |
| 675 |
); |
| 676 |
|
| 677 |
return array_map( 'intval', $results ); |
| 678 |
} |
| 679 |
|
| 680 |
/** |
| 681 |
* Check if local storage should be recommended based on post count |
| 682 |
* |
| 683 |
* @return array Recommendation with status and message |
| 684 |
*/ |
| 685 |
public function get_storage_recommendation() { |
| 686 |
$post_count = WPF()->post->get_count(); |
| 687 |
|
| 688 |
if ( $post_count < 10000 ) { |
| 689 |
return [ |
| 690 |
'status' => 'good', |
| 691 |
'message' => wpforo_phrase( 'Excellent choice for your forum size. Local storage will provide fast performance.', false ), |
| 692 |
'icon' => 'yes-alt', |
| 693 |
]; |
| 694 |
} elseif ( $post_count < self::PERFORMANCE_THRESHOLD ) { |
| 695 |
return [ |
| 696 |
'status' => 'good', |
| 697 |
'message' => wpforo_phrase( 'Good choice. Local storage with caching will provide acceptable performance.', false ), |
| 698 |
'icon' => 'yes', |
| 699 |
]; |
| 700 |
} elseif ( $post_count < 100000 ) { |
| 701 |
return [ |
| 702 |
'status' => 'warning', |
| 703 |
'message' => sprintf( |
| 704 |
wpforo_phrase( 'Your forum has %s posts. Local storage may have slower search performance. Consider using cloud storage for better results.', false ), |
| 705 |
number_format( $post_count ) |
| 706 |
), |
| 707 |
'icon' => 'warning', |
| 708 |
]; |
| 709 |
} else { |
| 710 |
return [ |
| 711 |
'status' => 'not_recommended', |
| 712 |
'message' => sprintf( |
| 713 |
wpforo_phrase( 'Your forum has %s posts. Cloud storage (gVectors) is recommended for optimal performance.', false ), |
| 714 |
number_format( $post_count ) |
| 715 |
), |
| 716 |
'icon' => 'dismiss', |
| 717 |
]; |
| 718 |
} |
| 719 |
} |
| 720 |
|
| 721 |
// ========================================================================= |
| 722 |
// Vector Math Utilities |
| 723 |
// ========================================================================= |
| 724 |
|
| 725 |
/** |
| 726 |
* Pack float array to binary |
| 727 |
* |
| 728 |
* @param array $vector Float array |
| 729 |
* @return string Binary packed data |
| 730 |
*/ |
| 731 |
private function pack_vector( $vector ) { |
| 732 |
return pack( 'f*', ...$vector ); |
| 733 |
} |
| 734 |
|
| 735 |
/** |
| 736 |
* Unpack binary to float array |
| 737 |
* |
| 738 |
* @param string $binary Binary data |
| 739 |
* @return array Float array |
| 740 |
*/ |
| 741 |
private function unpack_vector( $binary ) { |
| 742 |
$floats = unpack( 'f*', $binary ); |
| 743 |
return array_values( $floats ); |
| 744 |
} |
| 745 |
|
| 746 |
/** |
| 747 |
* Calculate vector magnitude |
| 748 |
* |
| 749 |
* @param array $vector Float array |
| 750 |
* @return float Magnitude |
| 751 |
*/ |
| 752 |
private function calculate_magnitude( $vector ) { |
| 753 |
$sum = 0; |
| 754 |
foreach ( $vector as $val ) { |
| 755 |
$sum += $val * $val; |
| 756 |
} |
| 757 |
return sqrt( $sum ); |
| 758 |
} |
| 759 |
|
| 760 |
/** |
| 761 |
* Normalize vector to unit length |
| 762 |
* |
| 763 |
* @param array $vector Float array |
| 764 |
* @param float $magnitude Pre-computed magnitude (optional) |
| 765 |
* @return array Normalized vector |
| 766 |
*/ |
| 767 |
private function normalize_vector( $vector, $magnitude = null ) { |
| 768 |
if ( $magnitude === null ) { |
| 769 |
$magnitude = $this->calculate_magnitude( $vector ); |
| 770 |
} |
| 771 |
|
| 772 |
if ( $magnitude == 0 ) { |
| 773 |
return $vector; |
| 774 |
} |
| 775 |
|
| 776 |
return array_map( function( $val ) use ( $magnitude ) { |
| 777 |
return $val / $magnitude; |
| 778 |
}, $vector ); |
| 779 |
} |
| 780 |
|
| 781 |
/** |
| 782 |
* Calculate dot product of two vectors |
| 783 |
* |
| 784 |
* @param array $a Vector A |
| 785 |
* @param array $b Vector B |
| 786 |
* @return float Dot product |
| 787 |
*/ |
| 788 |
private function dot_product( $a, $b ) { |
| 789 |
$sum = 0; |
| 790 |
$len = min( count( $a ), count( $b ) ); |
| 791 |
|
| 792 |
for ( $i = 0; $i < $len; $i++ ) { |
| 793 |
$sum += $a[ $i ] * $b[ $i ]; |
| 794 |
} |
| 795 |
|
| 796 |
return $sum; |
| 797 |
} |
| 798 |
|
| 799 |
/** |
| 800 |
* Calculate cosine similarity between two vectors |
| 801 |
* |
| 802 |
* @param array $a Vector A |
| 803 |
* @param array $b Vector B |
| 804 |
* @return float Similarity score (0-1) |
| 805 |
*/ |
| 806 |
public function cosine_similarity( $a, $b ) { |
| 807 |
$dot = $this->dot_product( $a, $b ); |
| 808 |
$mag_a = $this->calculate_magnitude( $a ); |
| 809 |
$mag_b = $this->calculate_magnitude( $b ); |
| 810 |
|
| 811 |
if ( $mag_a == 0 || $mag_b == 0 ) { |
| 812 |
return 0; |
| 813 |
} |
| 814 |
|
| 815 |
return $dot / ( $mag_a * $mag_b ); |
| 816 |
} |
| 817 |
} |
| 818 |
|
