*/ class A1AI_Migration { /** * The data manager instance * * @since 1.0.0 * @access private * @var A1AI_Data_Manager $data_manager The data manager instance */ private $data_manager; /** * Initialize the migration class * * @since 1.0.0 * @param A1AI_Data_Manager $data_manager The data manager instance */ public function __construct($data_manager) { $this->data_manager = $data_manager; } /** * Run all pending migrations * * @since 1.0.0 * @return array Migration results */ public function run_migrations() { $results = array(); $current_version = get_option('a1ai_db_version', '1.0.0'); // Migration 1.1.0: Add system prompts and custom instructions tables if (version_compare($current_version, '1.1.0', '<')) { $results['1.1.0'] = $this->migrate_to_1_1_0(); } // Migration 1.2.0: Move system prompts to custom instructions table if (version_compare($current_version, '1.2.0', '<')) { $results['1.2.0'] = $this->migrate_to_1_2_0(); } // Migration 1.3.0: Update adjustments table structure if (version_compare($current_version, '1.3.0', '<')) { $results['1.3.0'] = $this->migrate_to_1_3_0(); } // Migration 1.4.0: Add business_info table for sites that don't have it if (version_compare($current_version, '1.4.0', '<')) { $results['1.4.0'] = $this->migrate_to_1_4_0(); } // Migration 1.6.1: Add quality_events table for answer-quality telemetry. // NOTE: gated at 1.6.1 (not 1.6.0) because some installs have a1ai_db_version // stamped to 1.6.0 by the plugin-version stamping in run_database_upgrade(). if (version_compare($current_version, '1.6.1', '<')) { $results['1.6.1'] = $this->migrate_to_1_6_1(); } return $results; } /** * Migration to version 1.1.0: Add dedicated tables for system prompts and custom instructions * * @since 1.0.0 * @return array Migration result */ private function migrate_to_1_1_0() { global $wpdb; // Load the upgrade API require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $results = array( 'success' => true, 'messages' => array(), 'errors' => array() ); try { // Create system prompts table $system_prompts_table = $wpdb->prefix . 'a1ai_system_prompts'; $sql_system_prompts = "CREATE TABLE IF NOT EXISTS $system_prompts_table ( 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) ) " . $wpdb->get_charset_collate(); $result = dbDelta($sql_system_prompts); if (!empty($result)) { $results['messages'][] = 'System prompts table created/updated: ' . implode(', ', $result); } // Create custom instructions table $custom_instructions_table = $wpdb->prefix . 'a1ai_custom_instructions'; $sql_custom_instructions = "CREATE TABLE IF NOT EXISTS $custom_instructions_table ( 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) ) " . $wpdb->get_charset_collate(); $result = dbDelta($sql_custom_instructions); if (!empty($result)) { $results['messages'][] = 'Custom instructions table created/updated: ' . implode(', ', $result); } // Migrate existing data from JSON settings to new tables $migration_result = $this->migrate_existing_data(); if ($migration_result['success']) { $results['messages'] = array_merge($results['messages'], $migration_result['messages']); } else { $results['errors'] = array_merge($results['errors'], $migration_result['errors']); } // Update database version update_option('a1ai_db_version', '1.1.0'); $results['messages'][] = 'Database version updated to 1.1.0'; } catch (Exception $e) { $results['success'] = false; $results['errors'][] = 'Migration failed: ' . $e->getMessage(); } return $results; } /** * Migrate existing system prompts and custom instructions from JSON settings * * @since 1.0.0 * @return array Migration result */ private function migrate_existing_data() { global $wpdb; $results = array( 'success' => true, 'messages' => array(), 'errors' => array() ); try { // Get all chatbots $table_name = $wpdb->prefix . 'a1ai_chatbots'; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is not needed for migration operations // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // This is a simple table read with no user input, so no prepare needed $chatbots = $wpdb->get_results("SELECT id, settings FROM {$table_name}", ARRAY_A); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery $migrated_count = 0; foreach ($chatbots as $chatbot) { $settings = maybe_unserialize($chatbot['settings']); $chatbot_id = $chatbot['id']; $migrated = false; // Migrate system prompt if (!empty($settings['system_prompt'])) { $system_prompt_result = $this->migrate_system_prompt($chatbot_id, $settings['system_prompt']); if ($system_prompt_result) { $migrated = true; $results['messages'][] = "Migrated system prompt for chatbot ID $chatbot_id"; } } // Migrate custom instructions if (!empty($settings['custom_instructions'])) { $custom_instructions_result = $this->migrate_custom_instructions($chatbot_id, $settings['custom_instructions']); if ($custom_instructions_result) { $migrated = true; $results['messages'][] = "Migrated custom instructions for chatbot ID $chatbot_id"; } } if ($migrated) { $migrated_count++; } } $results['messages'][] = "Successfully migrated data for $migrated_count chatbots"; } catch (Exception $e) { $results['success'] = false; $results['errors'][] = 'Data migration failed: ' . $e->getMessage(); } return $results; } /** * Migrate system prompt for a specific chatbot * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @param string $prompt_content The system prompt content * @return bool Success status */ private function migrate_system_prompt($chatbot_id, $prompt_content) { global $wpdb; $system_prompts_table = $wpdb->prefix . 'a1ai_system_prompts'; // Check if system prompt already exists for this chatbot // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is not needed for migration operations $table_name = $wpdb->prefix . 'a1ai_system_prompts'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // Table names cannot be parameterized, so interpolation is necessary $existing = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table_name} WHERE chatbot_id = %d AND is_active = 1", $chatbot_id ) ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery if ($existing) { // Update existing system prompt // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration write operations // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is not needed for migration write operations $result = $wpdb->update( $system_prompts_table, array( 'prompt_content' => sanitize_textarea_field($prompt_content), 'updated_at' => current_time('mysql') ), array('id' => $existing), array('%s', '%s'), array('%d') ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } else { // Insert new system prompt // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration write operations // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is not needed for migration write operations $result = $wpdb->insert( $system_prompts_table, array( 'chatbot_id' => $chatbot_id, 'prompt_name' => 'Default System Prompt', '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') ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } return $result !== false; } /** * Migrate custom instructions for a specific chatbot * * @since 1.0.0 * @param int $chatbot_id The chatbot ID * @param string $instructions_content The custom instructions content * @return bool Success status */ private function migrate_custom_instructions($chatbot_id, $instructions_content) { global $wpdb; $custom_instructions_table = $wpdb->prefix . 'a1ai_custom_instructions'; // Check if custom instructions already exist for this chatbot // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is not needed for migration operations $table_name = $wpdb->prefix . 'a1ai_custom_instructions'; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // Table names cannot be parameterized, so interpolation is necessary $existing = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table_name} WHERE chatbot_id = %d AND instruction_type = 'general' AND is_active = 1", $chatbot_id ) ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery if ($existing) { // Update existing custom instructions // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration write operations // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is not needed for migration write operations $result = $wpdb->update( $custom_instructions_table, array( 'instruction_content' => sanitize_textarea_field($instructions_content), 'updated_at' => current_time('mysql') ), array('id' => $existing), array('%s', '%s'), array('%d') ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } else { // Insert new custom instructions // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration write operations // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is not needed for migration write operations $result = $wpdb->insert( $custom_instructions_table, array( 'chatbot_id' => $chatbot_id, 'instruction_name' => 'General Instructions', 'instruction_content' => sanitize_textarea_field($instructions_content), 'instruction_type' => 'general', 'priority' => 0, 'is_active' => 1, 'created_at' => current_time('mysql'), 'updated_at' => current_time('mysql') ), array('%d', '%s', '%s', '%s', '%d', '%d', '%s', '%s') ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery } return $result !== false; } /** * Rollback migration to version 1.1.0 * * @since 1.0.0 * @return array Rollback result */ public function rollback_1_1_0() { global $wpdb; $results = array( 'success' => true, 'messages' => array(), 'errors' => array() ); try { // Drop the new tables $system_prompts_table = $wpdb->prefix . 'a1ai_system_prompts'; $custom_instructions_table = $wpdb->prefix . 'a1ai_custom_instructions'; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for schema changes // phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange // Schema changes are necessary for rollback operations // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is not needed for schema changes // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // These are system tables with no user input, so no prepare needed $wpdb->query("DROP TABLE IF EXISTS {$system_prompts_table}"); $wpdb->query("DROP TABLE IF EXISTS {$custom_instructions_table}"); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery $results['messages'][] = 'System prompts and custom instructions tables dropped'; // Revert database version update_option('a1ai_db_version', '1.0.0'); $results['messages'][] = 'Database version reverted to 1.0.0'; } catch (Exception $e) { $results['success'] = false; $results['errors'][] = 'Rollback failed: ' . $e->getMessage(); } return $results; } /** * Migration to version 1.2.0: Move system prompts to custom instructions table * * @since 1.0.0 * @return array Migration result */ private function migrate_to_1_2_0() { global $wpdb; // Load the upgrade API require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $results = array( 'success' => true, 'messages' => array(), 'errors' => array() ); try { // Get all active system prompts from the system_prompts table $system_prompts_table = $wpdb->prefix . 'a1ai_system_prompts'; $custom_instructions_table = $wpdb->prefix . 'a1ai_custom_instructions'; // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration read operations // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Caching is not needed for migration read operations // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // Table names cannot be parameterized in prepare statements $system_prompts = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$system_prompts_table} WHERE is_active = %d", 1 ), ARRAY_A ); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery if (!empty($system_prompts)) { foreach ($system_prompts as $prompt) { // First, deactivate any existing system prompts for this chatbot to avoid duplicates // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration write operations $wpdb->update( $custom_instructions_table, array('is_active' => 0), array( 'chatbot_id' => $prompt['chatbot_id'], 'instruction_type' => 'system', 'instruction_name' => 'System Prompt' ), array('%d'), array('%d', '%s', '%s') ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery // Check if a system prompt already exists for this chatbot in custom instructions // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // Table names cannot be parameterized in prepare statements // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Direct database query is necessary for migration operations $existing = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$custom_instructions_table} WHERE chatbot_id = %d AND instruction_type = %s AND instruction_name = %s", $prompt['chatbot_id'], 'system', 'System Prompt' ) ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared if ($existing) { // Update existing system prompt in custom instructions // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration write operations $wpdb->update( $custom_instructions_table, array( 'instruction_content' => sanitize_textarea_field($prompt['prompt_content']), 'is_active' => 1, 'updated_at' => current_time('mysql') ), array('id' => $existing), array('%s', '%d', '%s'), array('%d') ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery $results['messages'][] = "Updated system prompt for chatbot ID {$prompt['chatbot_id']}"; } else { // Insert new system prompt into custom instructions // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // Direct database query is necessary for migration write operations $wpdb->insert( $custom_instructions_table, array( 'chatbot_id' => $prompt['chatbot_id'], 'instruction_name' => 'System Prompt', 'instruction_content' => sanitize_textarea_field($prompt['prompt_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') ); // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery $results['messages'][] = "Migrated system prompt for chatbot ID {$prompt['chatbot_id']}"; } } // Clear cache for all chatbots // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Direct database query is necessary for migration operations $chatbots = $wpdb->get_results("SELECT id FROM {$wpdb->prefix}a1ai_chatbots", ARRAY_A); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery foreach ($chatbots as $chatbot) { wp_cache_delete('a1ai_custom_instructions_' . intval($chatbot['id']) . '_system', 'a1ai'); wp_cache_delete('a1ai_custom_instructions_' . intval($chatbot['id']) . '_', 'a1ai'); } $results['messages'][] = 'System prompts migration completed successfully'; } else { $results['messages'][] = 'No system prompts found to migrate'; } // Update database version update_option('a1ai_db_version', '1.2.0'); $results['messages'][] = 'Database version updated to 1.2.0'; } catch (Exception $e) { $results['success'] = false; $results['errors'][] = 'Migration failed: ' . $e->getMessage(); } return $results; } /** * Migration to version 1.3.0: Update adjustments table structure * * @since 1.0.0 * @return array Migration result */ private function migrate_to_1_3_0() { global $wpdb; // Load the upgrade API require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $results = array( 'success' => true, 'messages' => array(), 'errors' => array() ); try { $adjustments_table = $wpdb->prefix . 'a1ai_adjustments'; // Check if the table exists // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Direct database query is necessary for migration operations $table_exists = $wpdb->get_var($wpdb->prepare( "SHOW TABLES LIKE %s", $adjustments_table )); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery if ($table_exists) { // Check if we need to migrate from old structure // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Table names cannot be parameterized in DESCRIBE statements // Direct database query is necessary for migration operations $columns = $wpdb->get_results("DESCRIBE " . $adjustments_table, ARRAY_A); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared $column_names = array_column($columns, 'Field'); // If old structure exists (has session_id, user_message, ai_message) if (in_array('session_id', $column_names) && in_array('user_message', $column_names) && in_array('ai_message', $column_names)) { // Create backup of old data // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // Table names cannot be parameterized in SELECT statements // Direct database query is necessary for migration operations $old_data = $wpdb->get_results("SELECT * FROM " . $adjustments_table, ARRAY_A); // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // Drop the old table // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange // Table names cannot be parameterized in DROP statements // Direct database query is necessary for migration operations // Schema changes are necessary for database upgrades $wpdb->query("DROP TABLE " . $adjustments_table); // phpcs:enable WordPress.DB.DirectDatabaseQuery.SchemaChange // phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared $results['messages'][] = 'Dropped old adjustments table structure'; } } // Create new adjustments table structure $sql_adjustments = "CREATE TABLE IF NOT EXISTS {$adjustments_table} ( 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', 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) ) " . $wpdb->get_charset_collate(); $result = dbDelta($sql_adjustments); if (!empty($result)) { $results['messages'][] = 'Adjustments table structure updated: ' . implode(', ', $result); } // Update database version update_option('a1ai_db_version', '1.3.0'); $results['messages'][] = 'Database version updated to 1.3.0'; } catch (Exception $e) { $results['success'] = false; $results['errors'][] = 'Migration failed: ' . $e->getMessage(); } return $results; } /** * Migration to version 1.4.0: Add business_info table if missing * * @since 1.4.0 * @return array Migration result */ private function migrate_to_1_4_0() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $results = array( 'success' => true, 'messages' => array(), 'errors' => array(), ); try { $business_info_table = $wpdb->prefix . 'a1ai_business_info'; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$business_info_table} ( 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); $results['messages'][] = 'Business info table created or verified.'; update_option('a1ai_db_version', '1.4.0'); } catch (Exception $e) { $results['success'] = false; $results['errors'][] = 'Migration failed: ' . $e->getMessage(); } return $results; } /** * Migration to version 1.6.1: Add quality_events table for answer-quality * telemetry (abstentions, no-context answers, retrieval-tier tracking). * Read by the workspace connector's /chatbot/quality REST endpoints. * * @since 1.6.1 * @return array Migration result */ private function migrate_to_1_6_1() { global $wpdb; require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $results = array( 'success' => true, 'messages' => array(), 'errors' => array(), ); try { $quality_table = $wpdb->prefix . 'a1ai_quality_events'; $charset_collate = $wpdb->get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS {$quality_table} ( 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};"; dbDelta($sql); $results['messages'][] = 'Quality events table created or verified.'; update_option('a1ai_db_version', '1.6.1'); } catch (Exception $e) { $results['success'] = false; $results['errors'][] = 'Migration failed: ' . $e->getMessage(); } return $results; } }