table = $wpdb->prefix . 'aisentic_conversations'; } /** * Create a new conversation * * @param int $user_id User ID. * @param string $channel Channel (admin or public). * @param string $title Optional title. * @return Conversation|null */ public function create( int $user_id, string $channel = 'admin', $title = null ): ?Conversation { global $wpdb; $uuid = Uuid::uuid4()->toString(); $now = current_time( 'mysql' ); $result = $wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $this->table, [ 'uuid' => $uuid, 'user_id' => $user_id, 'channel' => $channel, 'title' => $title, 'created_at' => $now, 'updated_at' => $now, ], [ '%s', '%d', '%s', '%s', '%s', '%s' ] ); if ( false === $result ) { return null; } return $this->findByUuid( $uuid ); } /** * Get the object cache key for a conversation UUID. * * @param string $uuid Conversation UUID. * @return string */ private function get_cache_key( string $uuid ): string { return 'conv_' . md5( $uuid ); } /** * Invalidate the object cache entry for a conversation UUID. * * @param string $uuid Conversation UUID. * @return void */ private function invalidate_cache( string $uuid ): void { wp_cache_delete( $this->get_cache_key( $uuid ), 'aisentic' ); } /** * Find conversation by UUID * * @param string $uuid Conversation UUID. * @return Conversation|null */ public function findByUuid( string $uuid ): ?Conversation { global $wpdb; $cache_key = $this->get_cache_key( $uuid ); $cached = wp_cache_get( $cache_key, 'aisentic' ); if ( false !== $cached ) { return ! empty( $cached ) ? new Conversation( $cached ) : null; } $row = $wpdb->get_row( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery $wpdb->prepare( "SELECT * FROM {$this->table} WHERE uuid = %s", $uuid ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared ARRAY_A ); wp_cache_set( $cache_key, $row ? $row : [], 'aisentic', 300 ); return $row ? new Conversation( $row ) : null; } /** * Update conversation * * @param string $uuid Conversation UUID. * @param array $data Data to update. * @return bool */ public function update( string $uuid, array $data ): bool { global $wpdb; $data['updated_at'] = current_time( 'mysql' ); $this->invalidate_cache( $uuid ); $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $this->table, $data, [ 'uuid' => $uuid ], null, [ '%s' ] ); return false !== $result; } /** * Delete conversation * * @param string $uuid Conversation UUID. * @return bool */ public function delete( string $uuid ): bool { global $wpdb; $this->invalidate_cache( $uuid ); $result = $wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $this->table, [ 'uuid' => $uuid ], [ '%s' ] ); return false !== $result; } /** * List conversations with pagination * * @param int $page Page number. * @param int $per_page Items per page. * @param int|null $user_id Filter by user ID. * @param string $channel Filter by channel. * @param string $search_text Search text for title filtering. * @param array $date_range Date range filter ['start' => 'Y-m-d H:i:s', 'end' => 'Y-m-d H:i:s']. * @return array */ public function list( int $page = 1, int $per_page = 20, ?int $user_id = null, string $channel = '', string $search_text = '', array $date_range = [] ): array { global $wpdb; $offset = ( $page - 1 ) * $per_page; $where = []; $values = []; if ( ! empty( $channel ) ) { $where[] = 'channel = %s'; $values[] = $channel; } if ( null !== $user_id ) { $where[] = 'user_id = %d'; $values[] = $user_id; } // Add search text filter if ( ! empty( $search_text ) ) { $where[] = 'title LIKE %s'; $values[] = '%' . $wpdb->esc_like( $search_text ) . '%'; } // Add date range filter for updated_at if ( ! empty( $date_range['start'] ) ) { $where[] = 'updated_at >= %s'; $values[] = $date_range['start']; } if ( ! empty( $date_range['end'] ) ) { $where[] = 'updated_at <= %s'; $values[] = $date_range['end']; } $where_clause = ! empty( $where ) ? implode( ' AND ', $where ) : '1=1'; // Get total count. // Table name is safe - $wpdb->prefix is controlled by WordPress core $count_query = "SELECT COUNT(*) FROM {$wpdb->prefix}aisentic_conversations WHERE {$where_clause}"; // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is trusted if ( ! empty( $values ) ) { $total = $wpdb->get_var( $wpdb->prepare( $count_query, ...$values ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter } else { $total = $wpdb->get_var( $count_query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- table name from trusted $wpdb->prefix; $where_clause is fixed placeholder literals. } // Get items. $items_query = "SELECT * FROM {$wpdb->prefix}aisentic_conversations WHERE {$where_clause} ORDER BY updated_at DESC LIMIT %d OFFSET %d"; // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $wpdb->prefix is trusted if ( ! empty( $values ) ) { $items = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $items_query uses trusted $wpdb->prefix + fixed placeholder literals, then goes through $wpdb->prepare(). $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter $items_query, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared array_merge( $values, [ $per_page, $offset ] ) ), ARRAY_A ); } else { $items = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $items_query uses trusted $wpdb->prefix + fixed placeholder literals, then goes through $wpdb->prepare(). $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter $items_query, // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared $per_page, $offset ), ARRAY_A ); } $conversations = array_map( function ( $item ) { return new Conversation( $item ); }, $items ); return [ 'items' => $conversations, 'total' => (int) $total, 'page' => $page, 'per_page' => $per_page, ]; } /** * Touch conversation (update updated_at timestamp) * * @param string $uuid Conversation UUID. * @return bool */ public function touch( string $uuid ): bool { global $wpdb; $this->invalidate_cache( $uuid ); $result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching $this->table, [ 'updated_at' => current_time( 'mysql' ) ], [ 'uuid' => $uuid ], [ '%s' ], [ '%s' ] ); return false !== $result; } }