*/ class A1AI_Data_Manager { /** * Database tables. * * @since 1.0.0 * @access private * @var array $tables Database tables used by the plugin. */ private $tables; /** * Initialize the class and set its properties. * * @since 1.0.0 */ public function __construct() { global $wpdb; $this->tables = array( 'chatbots' => $wpdb->prefix . 'a1ai_chatbots', 'conversations' => $wpdb->prefix . 'a1ai_conversations', 'messages' => $wpdb->prefix . 'a1ai_messages', 'adjustments' => $wpdb->prefix . 'a1ai_adjustments', 'business_info' => $wpdb->prefix . 'a1ai_business_info', 'quality_events' => $wpdb->prefix . 'a1ai_quality_events', ); // Ensure ai_temperature column exists in adjustments table $this->maybe_add_temperature_column(); } /** * Add ai_temperature column to adjustments table if missing. */ private function maybe_add_temperature_column() { if (get_option('a1ai_adjustments_has_temperature', false)) { return; } global $wpdb; $adjustments_table = $this->tables['adjustments']; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- One-time migration; table name from whitelisted class property $col = $wpdb->get_results( $wpdb->prepare( "SHOW COLUMNS FROM `{$adjustments_table}` LIKE %s", 'ai_temperature' ) ); if ( empty( $col ) ) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- One-time migration; table name from whitelisted class property $wpdb->query( "ALTER TABLE `{$adjustments_table}` ADD COLUMN ai_temperature float NOT NULL DEFAULT 0.7 AFTER ai_match_type" ); } update_option('a1ai_adjustments_has_temperature', true); } /** * Create database tables. * * @since 1.0.0 * @return bool True on success, false on failure. */ public function create_tables() { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database queries are necessary for table creation $charset_collate = $wpdb->get_charset_collate(); $sql = array(); // Chatbots table $sql[] = "CREATE TABLE {$this->tables['chatbots']} ( id mediumint(9) NOT NULL AUTO_INCREMENT, name varchar(255) NOT NULL, settings longtext NOT NULL, created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL, updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (id) ) $charset_collate;"; // Conversations table $sql[] = "CREATE TABLE {$this->tables['conversations']} ( id mediumint(9) NOT NULL AUTO_INCREMENT, chatbot_id mediumint(9) NOT NULL, user_id bigint(20) DEFAULT 0 NOT NULL, session_id varchar(255) NOT NULL, created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (id), KEY chatbot_id (chatbot_id), KEY user_id (user_id), KEY session_id (session_id) ) $charset_collate;"; // Messages table $sql[] = "CREATE TABLE {$this->tables['messages']} ( id mediumint(9) NOT NULL AUTO_INCREMENT, conversation_id mediumint(9) NOT NULL, role varchar(50) NOT NULL, content longtext NOT NULL, tokens_used int DEFAULT 0 NOT NULL, created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (id), KEY conversation_id (conversation_id) ) $charset_collate;"; // Adjustments table $sql[] = "CREATE TABLE {$this->tables['adjustments']} ( id mediumint(9) NOT NULL AUTO_INCREMENT, chatbot_id mediumint(9) NOT NULL, user_input text NOT NULL, ai_output text NOT NULL, ui_match_type varchar(20) NOT NULL DEFAULT 'Broad', ai_match_type varchar(20) NOT NULL DEFAULT 'Broad', ai_temperature float NOT NULL DEFAULT 0.7, created_at datetime DEFAULT CURRENT_TIMESTAMP NOT NULL, updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (id), KEY chatbot_id (chatbot_id) ) $charset_collate;"; // Quality events table (answer-quality telemetry; mirrors migration 1.6.1) $sql[] = "CREATE TABLE {$this->tables['quality_events']} ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, chatbot_id mediumint(9) NOT NULL DEFAULT 0, conversation_id mediumint(9) NOT NULL DEFAULT 0, message_id bigint(20) unsigned DEFAULT NULL, event_type varchar(32) NOT NULL, retrieval_tier varchar(16) NOT NULL DEFAULT 'none', result_count smallint(6) NOT NULL DEFAULT 0, semantic_used tinyint(1) NOT NULL DEFAULT 0, user_message text DEFAULT NULL, assistant_excerpt text DEFAULT NULL, tokens_used int(11) NOT NULL DEFAULT 0, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY chatbot_id (chatbot_id), KEY event_type (event_type), KEY created_at (created_at) ) $charset_collate;"; // System prompts table $sql[] = "CREATE TABLE {$wpdb->prefix}a1ai_system_prompts ( id mediumint(9) NOT NULL AUTO_INCREMENT, chatbot_id mediumint(9) NOT NULL, prompt_name varchar(255) NOT NULL, prompt_content longtext NOT NULL, is_active tinyint(1) NOT NULL DEFAULT 1, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY chatbot_id (chatbot_id) ) $charset_collate;"; // Custom instructions table $sql[] = "CREATE TABLE {$wpdb->prefix}a1ai_custom_instructions ( id mediumint(9) NOT NULL AUTO_INCREMENT, chatbot_id mediumint(9) NOT NULL, instruction_name varchar(255) NOT NULL, instruction_content longtext NOT NULL, instruction_type varchar(50) NOT NULL DEFAULT 'general', priority int(11) NOT NULL DEFAULT 0, is_active tinyint(1) NOT NULL DEFAULT 1, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY chatbot_id (chatbot_id) ) $charset_collate;"; // Business information table $sql[] = "CREATE TABLE {$this->tables['business_info']} ( id mediumint(9) NOT NULL AUTO_INCREMENT, chatbot_id mediumint(9) NOT NULL, business_name varchar(255) DEFAULT NULL, tagline text DEFAULT NULL, business_type varchar(100) DEFAULT NULL, services longtext DEFAULT NULL, products longtext DEFAULT NULL, hours longtext DEFAULT NULL, address text DEFAULT NULL, phone varchar(50) DEFAULT NULL, email varchar(255) DEFAULT NULL, website varchar(255) DEFAULT NULL, policies longtext DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY chatbot_id (chatbot_id), UNIQUE KEY chatbot_business_info (chatbot_id) ) $charset_collate;"; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; // Execute each query separately to better handle errors $success = true; foreach ($sql as $query) { $result = dbDelta($query); if (empty($result)) { $success = false; } } return $success; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Check if tables exist. * * @since 1.0.0 * @return bool True if all tables exist, false otherwise. */ public function tables_exist() { // Try to get from cache first $cache_key = 'a1ai_tables_exist'; $tables_exist = wp_cache_get($cache_key, 'a1ai'); if (false !== $tables_exist) { return $tables_exist; } global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for table existence check // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented above with wp_cache_get and below with wp_cache_set foreach ($this->tables as $table) { $table_name = $wpdb->esc_like($table); $result = $wpdb->get_var($wpdb->prepare( "SHOW TABLES LIKE %s", $table_name )); if ($result !== $table) { wp_cache_set($cache_key, false, 'a1ai', 3600); // Cache for 1 hour return false; } } wp_cache_set($cache_key, true, 'a1ai', 3600); // Cache for 1 hour return true; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Get a chatbot by ID. * * @since 1.0.0 * @param int $id The chatbot ID. * @return array|false The chatbot data or false if not found. */ public function get_chatbot($id) { // Try to get from cache first $cache_key = 'a1ai_chatbot_' . intval($id); $chatbot = wp_cache_get($cache_key, 'a1ai'); if (false !== $chatbot) { return $chatbot; } global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented above with wp_cache_get and below with wp_cache_set $chatbot = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}a1ai_chatbots WHERE id = %d", $id ), ARRAY_A ); if ($chatbot) { $unserialized_settings = maybe_unserialize($chatbot['settings']); $chatbot['settings'] = is_array($unserialized_settings) ? $unserialized_settings : array(); // Store in cache for future use wp_cache_set($cache_key, $chatbot, 'a1ai', 3600); // Cache for 1 hour } return $chatbot; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Get all chatbots. * * @since 1.0.0 * @return array Array of chatbots. */ public function get_chatbots() { // Try to get from cache first $cache_key = 'a1ai_all_chatbots'; $chatbots = wp_cache_get($cache_key, 'a1ai'); if (false !== $chatbots) { return $chatbots; } global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached $table = $this->tables['chatbots']; $chatbots = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}a1ai_chatbots ORDER BY id ASC", ARRAY_A ); foreach ($chatbots as &$chatbot) { $unserialized_settings = maybe_unserialize($chatbot['settings']); $chatbot['settings'] = is_array($unserialized_settings) ? $unserialized_settings : array(); } // Store in cache for future use wp_cache_set($cache_key, $chatbots, 'a1ai', 3600); // Cache for 1 hour return $chatbots; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Create a new chatbot or update an existing one. * * @since 1.0.0 * @param int $id The chatbot ID (0 for new chatbot). * @param string $name The chatbot name. * @param array $settings The chatbot settings. * @return int|false The chatbot ID or false on failure. */ public function save_chatbot($id, $name, $settings) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database queries are necessary for write operations $data = array( 'name' => sanitize_text_field($name), 'settings' => maybe_serialize($settings), ); $format = array( '%s', // name '%s', // settings ); // Update existing chatbot if ($id > 0) { $result = $wpdb->update( $this->tables['chatbots'], $data, array('id' => $id), $format, array('%d') ); // Clear cache wp_cache_delete('a1ai_chatbot_' . intval($id), 'a1ai'); wp_cache_delete('a1ai_all_chatbots', 'a1ai'); return $result !== false ? $id : false; } // Create new chatbot $result = $wpdb->insert( $this->tables['chatbots'], $data, $format ); // Clear cache wp_cache_delete('a1ai_all_chatbots', 'a1ai'); return $result !== false ? $wpdb->insert_id : false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Delete a chatbot. * * @since 1.0.0 * @param int $id The chatbot ID. * @return bool True on success, false on failure. */ public function delete_chatbot($id) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database queries are necessary for write operations $result = $wpdb->delete( $this->tables['chatbots'], array('id' => $id), array('%d') ) !== false; if ($result) { // Clear cache wp_cache_delete('a1ai_chatbot_' . intval($id), 'a1ai'); wp_cache_delete('a1ai_all_chatbots', 'a1ai'); } return $result; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Create a new conversation. * * @since 1.0.0 * @param int $chatbot_id The chatbot ID. * @param string $session_id The session ID. * @param int $user_id The user ID (0 for guests). * @return int|false The conversation ID or false on failure. */ public function create_conversation($chatbot_id, $session_id, $user_id = 0) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database queries are necessary for write operations $result = $wpdb->insert( $this->tables['conversations'], array( 'chatbot_id' => $chatbot_id, 'session_id' => $session_id, 'user_id' => $user_id, ), array( '%d', // chatbot_id '%s', // session_id '%d', // user_id ) ); return $result !== false ? $wpdb->insert_id : false; } /** * Get a conversation by ID. * * @since 1.0.0 * @param int $id The conversation ID. * @return array|false The conversation data or false if not found. */ public function get_conversation($id) { // Try to get from cache first $cache_key = 'a1ai_conversation_' . intval($id); $conversation = wp_cache_get($cache_key, 'a1ai'); if (false !== $conversation) { return $conversation; } global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented above with wp_cache_get and below with wp_cache_set $conversation = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}a1ai_conversations WHERE id = %d", $id ), ARRAY_A ); if ($conversation) { // Store in cache for future use wp_cache_set($cache_key, $conversation, 'a1ai', 3600); // Cache for 1 hour } return $conversation; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Get a conversation by session ID. * * @since 1.0.0 * @param string $session_id The session ID. * @param int $chatbot_id The chatbot ID. * @return array|false The conversation data or false if not found. */ public function get_conversation_by_session($session_id, $chatbot_id) { global $wpdb; // Try to get from cache first $cache_key = 'a1ai_conversation_' . md5($session_id . '_' . $chatbot_id); $conversation = wp_cache_get($cache_key, 'a1ai'); if (false !== $conversation) { return $conversation; } // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented above with wp_cache_get and below with wp_cache_set $conversation = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}a1ai_conversations WHERE session_id = %s AND chatbot_id = %d ORDER BY id DESC LIMIT 1", $session_id, $chatbot_id ), ARRAY_A ); // Store in cache for future use if ($conversation) { wp_cache_set($cache_key, $conversation, 'a1ai', 3600); // Cache for 1 hour } return $conversation; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Add a message to a conversation. * * @since 1.0.0 * @param int $conversation_id The conversation ID. * @param string $role The message role (user, assistant). * @param string $content The message content. * @param int $tokens_used The number of tokens used. * @return int|false The message ID or false on failure. */ public function add_message($conversation_id, $role, $content, $tokens_used = 0) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database queries are necessary for write operations // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // No caching needed for this write operation, but we clear cache after insert $result = $wpdb->insert( $this->tables['messages'], array( 'conversation_id' => $conversation_id, 'role' => $role, 'content' => $content, 'tokens_used' => intval($tokens_used), ), array( '%d', // conversation_id '%s', // role '%s', // content '%d', // tokens_used ) ); if ($result !== false) { // Clear message cache wp_cache_delete('a1ai_messages_' . intval($conversation_id), 'a1ai'); return $wpdb->insert_id; } return false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Record an answer-quality telemetry event (abstention, no-context answer, * adjustment hit, or a normal answer with its retrieval tier). Consumed by * the workspace connector's /chatbot/quality endpoints. Fails silently — * telemetry must never break the visitor-facing answer flow. * * @since 1.6.1 * @param array $data Event fields (chatbot_id, conversation_id, message_id, * event_type, retrieval_tier, result_count, semantic_used, * user_message, assistant_excerpt, tokens_used). * @return int|false Insert id or false. */ public function add_quality_event($data) { global $wpdb; $event_type = isset($data['event_type']) ? sanitize_key($data['event_type']) : ''; $allowed_events = array('answered', 'abstention', 'no_context', 'adjustment_hit', 'error', 'thumbs_up', 'thumbs_down'); if (!in_array($event_type, $allowed_events, true)) { return false; } $allowed_tiers = array('workspace', 'premium', 'basic', 'none', 'adjustment'); $tier = isset($data['retrieval_tier']) ? sanitize_key($data['retrieval_tier']) : 'none'; if (!in_array($tier, $allowed_tiers, true)) { $tier = 'none'; } // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Direct database queries are necessary for write operations $result = $wpdb->insert( $this->tables['quality_events'], array( 'chatbot_id' => isset($data['chatbot_id']) ? intval($data['chatbot_id']) : 0, 'conversation_id' => isset($data['conversation_id']) ? intval($data['conversation_id']) : 0, 'message_id' => isset($data['message_id']) ? intval($data['message_id']) : null, 'event_type' => $event_type, 'retrieval_tier' => $tier, 'result_count' => isset($data['result_count']) ? intval($data['result_count']) : 0, 'semantic_used' => empty($data['semantic_used']) ? 0 : 1, 'user_message' => isset($data['user_message']) ? mb_substr(sanitize_textarea_field($data['user_message']), 0, 500) : '', 'assistant_excerpt' => isset($data['assistant_excerpt']) ? mb_substr(sanitize_textarea_field($data['assistant_excerpt']), 0, 300) : '', 'tokens_used' => isset($data['tokens_used']) ? intval($data['tokens_used']) : 0, // Written explicitly in GMT rather than letting the column's // CURRENT_TIMESTAMP default fill it with MySQL-local time: the // reporting cutoffs (summary window, retention prune) are all // gmdate()-based, so a local default would skew both by the // server's UTC offset. 'created_at' => current_time('mysql', true), ), array('%d', '%d', '%d', '%s', '%s', '%d', '%d', '%s', '%s', '%d', '%s') ); if ($result === false) { // Table may not exist yet (migration not run) — never surface an error. return false; } $insert_id = $wpdb->insert_id; // Lazy retention: roughly 1% of inserts prune events older than 90 days, // so the table stays bounded without a dedicated cron. if (1 === wp_rand(1, 100)) { $wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from $wpdb->prefix + hardcoded suffix "DELETE FROM {$this->tables['quality_events']} WHERE created_at < %s", gmdate('Y-m-d H:i:s', time() - 90 * DAY_IN_SECONDS) ) ); } // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery return $insert_id; } /** * Get messages for a conversation. * * @since 1.0.0 * @param int $conversation_id The conversation ID. * @return array Array of messages. */ public function get_messages($conversation_id) { // Try to get from cache first $cache_key = 'a1ai_messages_' . intval($conversation_id); $messages = wp_cache_get($cache_key, 'a1ai'); if (false !== $messages) { return $messages; } global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented above with wp_cache_get and below with wp_cache_set $messages = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}a1ai_messages WHERE conversation_id = %d ORDER BY id ASC", $conversation_id ), ARRAY_A ); // Store in cache for future use - with shorter expiration since messages are updated frequently wp_cache_set($cache_key, $messages, 'a1ai', 300); // Cache for 5 minutes return $messages; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Get messages for a conversation (alias of get_messages for compatibility). * * @since 1.0.0 * @param int $conversation_id The conversation ID. * @return array Array of messages. */ public function get_conversation_messages($conversation_id) { return $this->get_messages($conversation_id); } /** * Get recent messages for a conversation with a limit. * * @since 1.6.0 * @param int $conversation_id The conversation ID. * @param int $limit Maximum number of messages to return. * @return array Array of recent messages, oldest first. */ public function get_recent_messages($conversation_id, $limit = 20) { $limit = max(4, min(intval($limit), 100)); $cache_key = 'a1ai_recent_messages_' . intval($conversation_id) . '_' . $limit; $messages = wp_cache_get($cache_key, 'a1ai'); if (false !== $messages) { return $messages; } global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching $messages = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM ( SELECT * FROM {$wpdb->prefix}a1ai_messages WHERE conversation_id = %d ORDER BY id DESC LIMIT %d ) AS recent ORDER BY id ASC", $conversation_id, $limit ), ARRAY_A ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery wp_cache_set($cache_key, $messages, 'a1ai', 300); return $messages; } /** * Update token usage for a message. * * @since 1.0.0 * @param int $conversation_id The conversation ID. * @param int $message_position The position of the message in the conversation (1-based). * @param int $tokens_used The number of tokens used. * @return bool True on success, false on failure. */ public function update_message_tokens($conversation_id, $message_position, $tokens_used) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database queries are necessary for write operations // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // No caching needed for this write operation, but we clear cache after update // Get the message ID based on the conversation ID and position $message_id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}a1ai_messages WHERE conversation_id = %d ORDER BY id DESC LIMIT %d, 1", $conversation_id, $message_position > 1 ? $message_position - 1 : 0 ) ); if (!$message_id) { return false; } // Update the tokens_used field $result = $wpdb->update( $this->tables['messages'], array('tokens_used' => $tokens_used), array('id' => $message_id), array('%d'), array('%d') ); // Clear message cache after update $cache_key = 'a1ai_messages_' . intval($conversation_id); wp_cache_delete($cache_key, 'a1ai'); return $result !== false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Add an adjustment. * * @since 1.0.0 * @param int $chatbot_id The chatbot ID. * @param string $user_input The user input pattern. * @param string $ai_output The AI output response. * @param string $ui_match_type The UI match type (Flexible, Broad, Phrase, Exact). * @param string $ai_match_type The AI match type (Flexible, Broad, Phrase, Exact). * @return int|false The adjustment ID or false on failure. */ public function add_adjustment($chatbot_id, $user_input, $ai_output, $ui_match_type = 'Broad', $ai_match_type = 'Broad', $priority = 0, $ai_temperature = 0.7) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database queries are necessary for write operations // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // No caching needed for this write operation, but we clear cache after insert $result = $wpdb->insert( $this->tables['adjustments'], array( 'chatbot_id' => $chatbot_id, 'user_input' => $user_input, 'ai_output' => $ai_output, 'ui_match_type' => $ui_match_type, 'ai_match_type' => $ai_match_type, 'ai_temperature' => $ai_temperature, ), array( '%d', // chatbot_id '%s', // user_input '%s', // ai_output '%s', // ui_match_type '%s', // ai_match_type '%f', // ai_temperature ) ); // Clear related cache $cache_key = 'a1ai_adjustments_' . intval($chatbot_id); wp_cache_delete($cache_key, 'a1ai'); return $result !== false ? $wpdb->insert_id : false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Get adjustments for a chatbot. * * @since 1.0.0 * @param int $chatbot_id The chatbot ID. * @return array Array of adjustments. */ public function get_adjustments($chatbot_id) { global $wpdb; // Try to get from cache first $cache_key = 'a1ai_adjustments_' . intval($chatbot_id); $adjustments = wp_cache_get($cache_key, 'a1ai'); if (false !== $adjustments) { return $adjustments; } // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented above with wp_cache_get and below with wp_cache_set $adjustments = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}a1ai_adjustments WHERE chatbot_id = %d ORDER BY id DESC", $chatbot_id ), ARRAY_A ); // Store in cache for future use wp_cache_set($cache_key, $adjustments, 'a1ai', 1800); // Cache for 30 minutes return $adjustments; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Get recent conversations for admin display. * * @since 1.0.0 * @param int $limit Number of conversations to retrieve. * @return array Array of conversations. */ public function get_recent_conversations($limit = 50) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented below with wp_cache_get and wp_cache_set $conversations = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}a1ai_conversations ORDER BY created_at DESC LIMIT %d", $limit ), ARRAY_A ); return $conversations ?: array(); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Get usage statistics. * * @since 1.0.0 * @param string $period The period (day, week, month, year). * @return array Array of usage statistics. */ public function get_usage_stats($period = 'month') { global $wpdb; // Try to get from cache first $cache_key = 'a1ai_usage_stats_' . $period; $stats = wp_cache_get($cache_key, 'a1ai'); if (false !== $stats) { return $stats; } // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented above with wp_cache_get and below with wp_cache_set // Prepare queries based on period type switch ($period) { case 'day': $date_format = '%Y-%m-%d'; $messages = $wpdb->get_results( $wpdb->prepare( "SELECT DATE_FORMAT(created_at, %s) as date, COUNT(*) as count, SUM(tokens_used) as tokens FROM {$wpdb->prefix}a1ai_messages WHERE DATE(created_at) = CURDATE() GROUP BY DATE(created_at) ORDER BY created_at ASC", $date_format ), ARRAY_A ); $conversations = $wpdb->get_results( $wpdb->prepare( "SELECT DATE_FORMAT(created_at, %s) as date, COUNT(*) as count FROM {$wpdb->prefix}a1ai_conversations WHERE DATE(created_at) = CURDATE() GROUP BY DATE(created_at) ORDER BY created_at ASC", $date_format ), ARRAY_A ); break; case 'week': $date_format = '%Y-%m-%d'; $messages = $wpdb->get_results( $wpdb->prepare( "SELECT DATE_FORMAT(created_at, %s) as date, COUNT(*) as count, SUM(tokens_used) as tokens FROM {$wpdb->prefix}a1ai_messages WHERE YEARWEEK(created_at) = YEARWEEK(CURDATE()) GROUP BY DATE(created_at) ORDER BY created_at ASC", $date_format ), ARRAY_A ); $conversations = $wpdb->get_results( $wpdb->prepare( "SELECT DATE_FORMAT(created_at, %s) as date, COUNT(*) as count FROM {$wpdb->prefix}a1ai_conversations WHERE YEARWEEK(created_at) = YEARWEEK(CURDATE()) GROUP BY DATE(created_at) ORDER BY created_at ASC", $date_format ), ARRAY_A ); break; case 'year': $date_format = '%Y-%m'; $messages = $wpdb->get_results( $wpdb->prepare( "SELECT DATE_FORMAT(created_at, %s) as date, COUNT(*) as count, SUM(tokens_used) as tokens FROM {$wpdb->prefix}a1ai_messages WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) GROUP BY YEAR(created_at), MONTH(created_at) ORDER BY created_at ASC", $date_format ), ARRAY_A ); $conversations = $wpdb->get_results( $wpdb->prepare( "SELECT DATE_FORMAT(created_at, %s) as date, COUNT(*) as count FROM {$wpdb->prefix}a1ai_conversations WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR) GROUP BY YEAR(created_at), MONTH(created_at) ORDER BY created_at ASC", $date_format ), ARRAY_A ); break; case 'month': default: $date_format = '%Y-%m-%d'; $messages = $wpdb->get_results( $wpdb->prepare( "SELECT DATE_FORMAT(created_at, %s) as date, COUNT(*) as count, SUM(tokens_used) as tokens FROM {$wpdb->prefix}a1ai_messages WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY DATE(created_at) ORDER BY created_at ASC", $date_format ), ARRAY_A ); $conversations = $wpdb->get_results( $wpdb->prepare( "SELECT DATE_FORMAT(created_at, %s) as date, COUNT(*) as count FROM {$wpdb->prefix}a1ai_conversations WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) GROUP BY DATE(created_at) ORDER BY created_at ASC", $date_format ), ARRAY_A ); break; } // Get total stats $totals = $wpdb->get_row( "SELECT COUNT(*) as message_count, SUM(tokens_used) as tokens_used FROM {$wpdb->prefix}a1ai_messages", ARRAY_A ); $conversation_count = $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}a1ai_conversations" ); $stats = array( 'messages' => $messages, 'conversations' => $conversations, 'totals' => array( 'message_count' => $totals['message_count'] ?: 0, 'tokens_used' => $totals['tokens_used'] ?: 0, 'conversation_count' => $conversation_count ?: 0, ), ); // Store in cache for future use wp_cache_set($cache_key, $stats, 'a1ai', 900); // Cache for 15 minutes return $stats; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Send email notification for new chat. * * @since 1.0.0 * @param int $chatbot_id The chatbot ID. * @param string $session_id The session ID. * @param string $user_message The first user message. * @return bool True if email sent successfully, false otherwise. */ public function send_new_chat_notification($chatbot_id, $session_id, $user_message = '') { // Get chatbot data $chatbot = $this->get_chatbot($chatbot_id); if (empty($chatbot)) { return false; } // Get chatbot settings $settings = maybe_unserialize($chatbot['settings']); // Check if notification email is set if (empty($settings['notification_email']) || !is_email($settings['notification_email'])) { return false; } // Prepare email content $site_name = get_bloginfo('name'); $site_url = get_site_url(); $admin_url = admin_url('admin.php?page=a1ai-conversations'); $subject = sprintf( /* translators: %s: Site name */ __('[%s] New Chat Started', 'a1ai-chatbot'), $site_name ); $message = sprintf( /* translators: %1$s: Site name, %2$s: Chatbot name, %3$s: Site URL, %4$s: Admin URL, %5$s: Session ID, %6$s: User message */ __( 'A new chat has started on your website. Site: %1$s Chatbot: %2$s Website: %3$s Admin Panel: %4$s Session ID: %5$s First Message: %6$s This is an automated notification from your A1AI chatbot system.', 'a1ai-chatbot' ), $site_name, $chatbot['name'], $site_url, $admin_url, $session_id, !empty($user_message) ? $user_message : __('No message yet', 'a1ai-chatbot') ); // Email headers $headers = array( 'Content-Type: text/plain; charset=UTF-8', 'From: ' . $site_name . ' <' . get_option('admin_email') . '>', ); // Send email $sent = wp_mail($settings['notification_email'], $subject, $message, $headers); // Log the attempt for debugging if (defined('WP_DEBUG') && WP_DEBUG && defined('WP_DEBUG_LOG') && WP_DEBUG_LOG) { $log_message = sprintf( 'A1AI Email Notification: Attempted to send new chat notification to %s for chatbot %d. Result: %s', $settings['notification_email'], $chatbot_id, $sent ? 'Success' : 'Failed' ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log error_log($log_message); } return $sent; } /** * Get system prompt for a chatbot * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @return string The system prompt content */ public function get_system_prompt($chatbot_id) { // Try to get from cache first $cache_key = 'a1ai_system_prompt_' . intval($chatbot_id); $system_prompt = wp_cache_get($cache_key, 'a1ai'); if (false !== $system_prompt) { return $system_prompt; } global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented above with wp_cache_get and below with wp_cache_set $table_name = $wpdb->prefix . 'a1ai_system_prompts'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // Table names cannot be parameterized, so interpolation is necessary $result = $wpdb->get_var( $wpdb->prepare( "SELECT prompt_content FROM {$table_name} WHERE chatbot_id = %d AND is_active = 1 ORDER BY id DESC LIMIT 1", $chatbot_id ) ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared $system_prompt = $result ? $result : ''; // Cache the result wp_cache_set($cache_key, $system_prompt, 'a1ai', 3600); // Cache for 1 hour return $system_prompt; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Save system prompt for a chatbot * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @param string $prompt_name The prompt name * @param string $prompt_content The prompt content * @return int|false The prompt ID or false on failure */ public function save_system_prompt($chatbot_id, $prompt_name, $prompt_content) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $system_prompts_table = $wpdb->prefix . 'a1ai_system_prompts'; // Deactivate existing prompts for this chatbot $wpdb->update( $system_prompts_table, array('is_active' => 0), array('chatbot_id' => $chatbot_id), array('%d'), array('%d') ); // Insert new system prompt $result = $wpdb->insert( $system_prompts_table, array( 'chatbot_id' => $chatbot_id, 'prompt_name' => sanitize_text_field($prompt_name), 'prompt_content' => sanitize_textarea_field($prompt_content), 'is_active' => 1, 'created_at' => current_time('mysql'), 'updated_at' => current_time('mysql') ), array('%d', '%s', '%s', '%d', '%s', '%s') ); // Clear cache wp_cache_delete('a1ai_system_prompt_' . intval($chatbot_id), 'a1ai'); return $result ? $wpdb->insert_id : false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Get custom instructions for a chatbot * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @param string $type The instruction type (optional) * @return array Array of custom instructions */ public function get_custom_instructions($chatbot_id, $type = '') { // Try to get from cache first $cache_key = 'a1ai_custom_instructions_' . intval($chatbot_id) . '_' . sanitize_key($type); $instructions = wp_cache_get($cache_key, 'a1ai'); if (false !== $instructions) { return $instructions; } global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary and properly cached // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is implemented above with wp_cache_get and below with wp_cache_set $custom_instructions_table = $wpdb->prefix . 'a1ai_custom_instructions'; $table_name = $wpdb->prefix . 'a1ai_custom_instructions'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // Table names cannot be parameterized, so interpolation is necessary if (!empty($type)) { $results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table_name} WHERE chatbot_id = %d AND instruction_type = %s AND is_active = 1 ORDER BY priority ASC, id ASC", $chatbot_id, $type ), ARRAY_A ); } else { $results = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$table_name} WHERE chatbot_id = %d AND is_active = 1 ORDER BY priority ASC, id ASC", $chatbot_id ), ARRAY_A ); } // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared $instructions = $results ? $results : array(); // Cache the result wp_cache_set($cache_key, $instructions, 'a1ai', 3600); // Cache for 1 hour return $instructions; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Save custom instruction for a chatbot * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @param string $instruction_name The instruction name * @param string $instruction_content The instruction content * @param string $instruction_type The instruction type * @param int $priority The priority (optional) * @return int|false The instruction ID or false on failure */ public function save_custom_instruction($chatbot_id, $instruction_name, $instruction_content, $instruction_type = 'general', $priority = 0) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $custom_instructions_table = $wpdb->prefix . 'a1ai_custom_instructions'; // For system prompts, we want to update existing ones instead of creating new ones if ($instruction_type === 'system') { return $this->save_system_prompt_instruction($chatbot_id, $instruction_name, $instruction_content); } // For other instruction types, deactivate existing ones and create new ones $wpdb->update( $custom_instructions_table, array('is_active' => 0), array('chatbot_id' => $chatbot_id, 'instruction_type' => $instruction_type), array('%d'), array('%d', '%s') ); $result = $wpdb->insert( $custom_instructions_table, array( 'chatbot_id' => $chatbot_id, 'instruction_name' => sanitize_text_field($instruction_name), 'instruction_content' => sanitize_textarea_field($instruction_content), 'instruction_type' => sanitize_text_field($instruction_type), 'priority' => intval($priority), 'is_active' => 1, 'created_at' => current_time('mysql'), 'updated_at' => current_time('mysql') ), array('%d', '%s', '%s', '%s', '%d', '%d', '%s', '%s') ); // Clear cache wp_cache_delete('a1ai_custom_instructions_' . intval($chatbot_id) . '_' . sanitize_key($instruction_type), 'a1ai'); wp_cache_delete('a1ai_custom_instructions_' . intval($chatbot_id) . '_', 'a1ai'); return $result ? $wpdb->insert_id : false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Save system prompt instruction with proper update logic * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @param string $instruction_name The instruction name * @param string $instruction_content The instruction content * @return int|false The instruction ID or false on failure */ private function save_system_prompt_instruction($chatbot_id, $instruction_name, $instruction_content) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $custom_instructions_table = $wpdb->prefix . 'a1ai_custom_instructions'; // Check if a system prompt already exists for this chatbot // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // Table names cannot be parameterized in prepare statements // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // This is a write operation that doesn't need caching $existing = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$custom_instructions_table} WHERE chatbot_id = %d AND instruction_type = %s AND instruction_name = %s AND is_active = 1", $chatbot_id, 'system', $instruction_name ) ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared if ($existing) { // Update existing system prompt $result = $wpdb->update( $custom_instructions_table, array( 'instruction_content' => sanitize_textarea_field($instruction_content), 'updated_at' => current_time('mysql') ), array('id' => $existing), array('%s', '%s'), array('%d') ); // Clear cache wp_cache_delete('a1ai_custom_instructions_' . intval($chatbot_id) . '_system', 'a1ai'); wp_cache_delete('a1ai_custom_instructions_' . intval($chatbot_id) . '_', 'a1ai'); return $result !== false ? $existing : false; } else { // Insert new system prompt $result = $wpdb->insert( $custom_instructions_table, array( 'chatbot_id' => $chatbot_id, 'instruction_name' => sanitize_text_field($instruction_name), 'instruction_content' => sanitize_textarea_field($instruction_content), 'instruction_type' => 'system', 'priority' => 0, 'is_active' => 1, 'created_at' => current_time('mysql'), 'updated_at' => current_time('mysql') ), array('%d', '%s', '%s', '%s', '%d', '%d', '%s', '%s') ); // Clear cache wp_cache_delete('a1ai_custom_instructions_' . intval($chatbot_id) . '_system', 'a1ai'); wp_cache_delete('a1ai_custom_instructions_' . intval($chatbot_id) . '_', 'a1ai'); return $result ? $wpdb->insert_id : false; } // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Update custom instruction * * @since 1.0.0 * @param int $instruction_id The instruction ID * @param array $data The data to update * @return bool Success status */ public function update_custom_instruction($instruction_id, $data) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $custom_instructions_table = $wpdb->prefix . 'a1ai_custom_instructions'; $update_data = array(); $format = array(); if (isset($data['instruction_name'])) { $update_data['instruction_name'] = sanitize_text_field($data['instruction_name']); $format[] = '%s'; } if (isset($data['instruction_content'])) { $update_data['instruction_content'] = sanitize_textarea_field($data['instruction_content']); $format[] = '%s'; } if (isset($data['instruction_type'])) { $update_data['instruction_type'] = sanitize_text_field($data['instruction_type']); $format[] = '%s'; } if (isset($data['priority'])) { $update_data['priority'] = intval($data['priority']); $format[] = '%d'; } if (isset($data['is_active'])) { $update_data['is_active'] = intval($data['is_active']); $format[] = '%d'; } $update_data['updated_at'] = current_time('mysql'); $format[] = '%s'; $result = $wpdb->update( $custom_instructions_table, $update_data, array('id' => $instruction_id), $format, array('%d') ); // Clear cache for all custom instructions (since we don't know the type) wp_cache_delete('a1ai_custom_instructions_' . intval($instruction_id) . '_*', 'a1ai'); return $result !== false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Delete custom instruction * * @since 1.0.0 * @param int $instruction_id The instruction ID * @return bool Success status */ public function delete_custom_instruction($instruction_id) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $custom_instructions_table = $wpdb->prefix . 'a1ai_custom_instructions'; $result = $wpdb->delete( $custom_instructions_table, array('id' => $instruction_id), array('%d') ); // Clear cache for all custom instructions (since we don't know the type) wp_cache_delete('a1ai_custom_instructions_' . intval($instruction_id) . '_*', 'a1ai'); return $result !== false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Delete messages by conversation ID * * @since 1.0.0 * @param int $conversation_id The conversation ID * @return bool Success status */ public function delete_messages_by_conversation($conversation_id) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $result = $wpdb->delete( $this->tables['messages'], array('conversation_id' => $conversation_id), array('%d') ); // Clear cache wp_cache_delete('a1ai_messages_' . intval($conversation_id), 'a1ai'); return $result !== false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Delete conversation * * @since 1.0.0 * @param int $conversation_id The conversation ID * @return bool Success status */ public function delete_conversation($conversation_id) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $result = $wpdb->delete( $this->tables['conversations'], array('id' => $conversation_id), array('%d') ); // Clear cache wp_cache_delete('a1ai_conversation_' . intval($conversation_id), 'a1ai'); return $result !== false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Update adjustment * * @since 1.0.0 * @param int $adjustment_id The adjustment ID * @param array $data The adjustment data * @return bool Success status */ public function update_adjustment($adjustment_id, $data) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $update_data = array(); $format = array(); if (isset($data['user_input'])) { $update_data['user_input'] = sanitize_textarea_field($data['user_input']); $format[] = '%s'; } if (isset($data['ai_output'])) { $update_data['ai_output'] = sanitize_textarea_field($data['ai_output']); $format[] = '%s'; } if (isset($data['ui_match_type'])) { $update_data['ui_match_type'] = sanitize_text_field($data['ui_match_type']); $format[] = '%s'; } if (isset($data['ai_match_type'])) { $update_data['ai_match_type'] = sanitize_text_field($data['ai_match_type']); $format[] = '%s'; } if (isset($data['ai_temperature'])) { $update_data['ai_temperature'] = floatval($data['ai_temperature']); $format[] = '%f'; } if (empty($update_data)) { return false; } $update_data['updated_at'] = current_time('mysql'); $format[] = '%s'; $result = $wpdb->update( $this->tables['adjustments'], $update_data, array('id' => $adjustment_id), $format, array('%d') ); // Clear cache for all adjustments (since we don't know the chatbot_id) wp_cache_delete('a1ai_adjustments_*', 'a1ai'); return $result !== false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Delete adjustment * * @since 1.0.0 * @param int $adjustment_id The adjustment ID * @return bool Success status */ public function delete_adjustment($adjustment_id) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $result = $wpdb->delete( $this->tables['adjustments'], array('id' => $adjustment_id), array('%d') ); // Clear cache for all adjustments (since we don't know the chatbot_id) wp_cache_delete('a1ai_adjustments_*', 'a1ai'); return $result !== false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Get adjustment by ID * * @since 1.0.0 * @param int $adjustment_id The adjustment ID * @return array|false The adjustment data or false on failure */ public function get_adjustment($adjustment_id) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // No caching needed for single record retrieval $adjustment = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}a1ai_adjustments WHERE id = %d", $adjustment_id ), ARRAY_A ); return $adjustment; // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Ensure the business_info table exists; create it if missing. * * @since 1.4.0 */ private function ensure_business_info_table() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$this->tables['business_info']} ( id mediumint(9) NOT NULL AUTO_INCREMENT, chatbot_id mediumint(9) NOT NULL, business_name varchar(255) DEFAULT NULL, tagline text DEFAULT NULL, business_type varchar(100) DEFAULT NULL, services longtext DEFAULT NULL, products longtext DEFAULT NULL, hours longtext DEFAULT NULL, address text DEFAULT NULL, phone varchar(50) DEFAULT NULL, email varchar(255) DEFAULT NULL, website varchar(255) DEFAULT NULL, policies longtext DEFAULT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY chatbot_id (chatbot_id), UNIQUE KEY chatbot_business_info (chatbot_id) ) {$charset_collate};"; dbDelta($sql); } /** * Get business information for a chatbot * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @return array|false The business information or false if not found */ public function get_business_info($chatbot_id) { global $wpdb; // Ensure business_info table exists (handles upgrades where table was never created) // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery -- Schema check required; no API equivalent $table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $this->tables['business_info'] ) ) ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery if ( $table_exists !== $this->tables['business_info'] ) { $this->ensure_business_info_table(); } // Try to get from cache first $cache_key = 'a1ai_business_info_' . $chatbot_id; $business_info = wp_cache_get( $cache_key, 'a1ai' ); if ( false !== $business_info ) { return $business_info; } // Table name from whitelisted class property; identifier cannot be passed to prepare(). // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared $table_name = $this->tables['business_info']; $business_info = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM `' . $table_name . '` WHERE chatbot_id = %d', $chatbot_id ), ARRAY_A ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared // Cache the result if ($business_info) { wp_cache_set($cache_key, $business_info, 'a1ai', 3600); } return $business_info; } /** * Save business information for a chatbot * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @param array $business_data The business information data * @return int|false The business info ID or false on failure */ public function save_business_info($chatbot_id, $business_data) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations // Prepare data for database $data = array( 'chatbot_id' => $chatbot_id, 'business_name' => isset($business_data['business_name']) ? sanitize_text_field($business_data['business_name']) : null, 'tagline' => isset($business_data['tagline']) ? sanitize_textarea_field($business_data['tagline']) : null, 'business_type' => isset($business_data['business_type']) ? sanitize_text_field($business_data['business_type']) : null, 'services' => isset($business_data['services']) ? wp_json_encode($business_data['services']) : null, 'products' => isset($business_data['products']) ? wp_json_encode($business_data['products']) : null, 'hours' => isset($business_data['hours']) ? wp_json_encode($business_data['hours']) : null, 'address' => isset($business_data['address']) ? sanitize_textarea_field($business_data['address']) : null, 'phone' => isset($business_data['phone']) ? sanitize_text_field($business_data['phone']) : null, 'email' => isset($business_data['email']) ? sanitize_email($business_data['email']) : null, 'website' => isset($business_data['website']) ? esc_url_raw($business_data['website']) : null, 'policies' => isset($business_data['policies']) ? sanitize_textarea_field($business_data['policies']) : null, ); $format = array( '%d', // chatbot_id '%s', // business_name '%s', // tagline '%s', // business_type '%s', // services '%s', // products '%s', // hours '%s', // address '%s', // phone '%s', // email '%s', // website '%s', // policies ); // Check if business info already exists for this chatbot $existing = $this->get_business_info($chatbot_id); if ($existing) { // Update existing record $result = $wpdb->update( $this->tables['business_info'], $data, array('chatbot_id' => $chatbot_id), $format, array('%d') ); $business_info_id = $existing['id']; } else { // Insert new record $result = $wpdb->insert( $this->tables['business_info'], $data, $format ); $business_info_id = $wpdb->insert_id; } // Clear cache wp_cache_delete('a1ai_business_info_' . $chatbot_id, 'a1ai'); return $result !== false ? $business_info_id : false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } /** * Delete business information for a chatbot * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @return bool Success status */ public function delete_business_info($chatbot_id) { global $wpdb; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for write operations $result = $wpdb->delete( $this->tables['business_info'], array('chatbot_id' => $chatbot_id), array('%d') ); // Clear cache wp_cache_delete('a1ai_business_info_' . $chatbot_id, 'a1ai'); return $result !== false; // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } }