<?php
/**
 * Fired during plugin activation.
 *
 * @package Inqyra
 */

// If this file is called directly, abort.
if (!defined('WPINC')) {
    die;
}

/**
 * Plugin activator class.
 */
class Inqyra_Activator {

    /**
     * Database schema version.
     */
    const DB_VERSION = '1.8.0';

    /**
     * Runs during plugin activation.
     */
    public static function activate() {
        self::create_tables();
        self::create_default_agent();
        self::schedule_cron_events();
        self::set_default_options();

        // Store the database version
        update_option('inqyra_db_version', self::DB_VERSION);

        // Flag that we need to redirect to the setup wizard.
        set_transient('inqyra_activation_redirect', true, 60);

        // Clear rewrite rules
        flush_rewrite_rules();
    }

    /**
     * Create database tables.
     */
    private static function create_tables() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        // Agents table
        $table_agents = $wpdb->prefix . 'inqyra_agents';
        $sql_agents = "CREATE TABLE $table_agents (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            system_prompt text,
            language varchar(5) DEFAULT 'nl',
            provider enum('claude','openai','gemini','mistral','deepseek') DEFAULT 'claude',
            model varchar(100),
            max_tokens int(11) DEFAULT 4096,
            daily_token_limit int(11) DEFAULT 1000000,
            cost_limit_usd decimal(10,4) DEFAULT 10.00,
            hourly_request_limit int(11) DEFAULT 500,
            archive_after_days int(11) DEFAULT 30,
            delete_archived_after_days int(11) DEFAULT NULL,
            primary_color varchar(7) DEFAULT '#0d6efd',
            text_color varchar(7) DEFAULT '#ffffff',
            widget_position enum('bottom-right','bottom-left','top-right','top-left') DEFAULT 'bottom-right',
            greeting_message varchar(500),
            button_text varchar(100),
            button_text_color varchar(7) DEFAULT '#ffffff',
            header_title varchar(100) DEFAULT 'Chat',
            header_subtitle varchar(200) DEFAULT 'Powered by AI',
            powered_by_text varchar(100) DEFAULT 'Powered by Inqyra',
            header_icon_id bigint(20) unsigned DEFAULT 0,
            show_icon_in_messages tinyint(1) DEFAULT 0,
            send_button_icon_id bigint(20) unsigned DEFAULT 0,
            send_button_text varchar(100) DEFAULT '',
            crawl_parts longtext,
            crawl_post_types longtext,
            document_search_mode enum('linked','direct','hybrid') DEFAULT 'linked',
            document_score_weight decimal(3,2) DEFAULT 0.70,
            synonym_expansion_enabled tinyint(1) DEFAULT 1,
            synonym_expansion_strategy enum('append','replace','both') DEFAULT 'append',
            query_rewrite_enabled tinyint(1) DEFAULT 1,
            query_rewrite_provider enum('claude','openai','gemini','mistral','deepseek') DEFAULT 'claude',
            query_rewrite_model varchar(100) DEFAULT 'claude-3-haiku-20240307',
            auto_recrawl_enabled tinyint(1) DEFAULT 1,
            widget_enabled tinyint(1) DEFAULT 1,
            widget_display_mode enum('all','include','exclude') DEFAULT 'all',
            widget_pages longtext,
            widget_mobile_enabled tinyint(1) DEFAULT 1,
            conversation_persist tinyint(1) DEFAULT 0,
            input_placeholder varchar(255) DEFAULT '',
            hide_input_placeholder tinyint(1) DEFAULT 0,
            active tinyint(1) DEFAULT 1,
            created_at timestamp DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_active (active)
        ) $charset_collate;";

        dbDelta($sql_agents);

        // Knowledge chunks table
        $table_chunks = $wpdb->prefix . 'inqyra_knowledge_chunks';
        $sql_chunks = "CREATE TABLE $table_chunks (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            agent_id bigint(20) unsigned NOT NULL,
            source_type enum('post','page','cpt','document','manual') NOT NULL,
            source_id bigint(20) unsigned,
            source_url varchar(500),
            url_path varchar(500),
            title varchar(500),
            content_type enum('website','document') DEFAULT 'website',
            chunk_index int(11) DEFAULT 0,
            content text NOT NULL,
            content_hash varchar(32),
            embedding longblob,
            embedding_model varchar(100),
            needs_embedding tinyint(1) DEFAULT 1,
            created_at timestamp DEFAULT CURRENT_TIMESTAMP,
            updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY unique_chunk (agent_id, source_type, source_id, chunk_index),
            KEY idx_agent_type (agent_id, content_type),
            KEY idx_needs_embedding (needs_embedding),
            KEY idx_agent_needs_embedding (agent_id, needs_embedding),
            KEY idx_agent_embedding (agent_id, embedding(100)),
            FULLTEXT KEY ft_content (content),
            FULLTEXT KEY ft_url_path (url_path),
            FULLTEXT KEY ft_title (title)
        ) $charset_collate;";

        dbDelta($sql_chunks);

        // Chunk links table (for contextual retrieval)
        $table_links = $wpdb->prefix . 'inqyra_chunk_links';
        $sql_links = "CREATE TABLE $table_links (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            website_chunk_id bigint(20) unsigned NOT NULL,
            document_chunk_id bigint(20) unsigned NOT NULL,
            document_url varchar(2048),
            link_text varchar(500),
            created_at timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY unique_link (website_chunk_id, document_chunk_id),
            KEY idx_website_chunk (website_chunk_id),
            KEY idx_document_chunk (document_chunk_id),
            KEY idx_document_url (document_url(255))
        ) $charset_collate;";

        dbDelta($sql_links);

        // Premium tables — only create when running premium version
        if ( function_exists( 'inqyra_fs' ) && inqyra_fs()->is__premium_only() ) {
            // Documents table
            $table_documents = $wpdb->prefix . 'inqyra_documents';
            $sql_documents = "CREATE TABLE $table_documents (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                agent_id bigint(20) unsigned NOT NULL,
                filename varchar(255) NOT NULL,
                original_filename varchar(255) NOT NULL,
                file_path varchar(500) NOT NULL,
                file_type enum('pdf','docx','doc') NOT NULL,
                file_size bigint(20) unsigned,
                status enum('pending','processing','completed','failed') DEFAULT 'pending',
                error_message text,
                chunk_count int(11) DEFAULT 0,
                uploaded_by bigint(20) unsigned,
                created_at timestamp DEFAULT CURRENT_TIMESTAMP,
                updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY idx_agent_status (agent_id, status)
            ) $charset_collate;";

            dbDelta($sql_documents);

            // Synonyms table
            // Note: term is varchar(191) to stay within 767 byte key limit for utf8mb4
            $table_synonyms = $wpdb->prefix . 'inqyra_synonyms';
            $sql_synonyms = "CREATE TABLE $table_synonyms (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                agent_id bigint(20) unsigned NOT NULL,
                term varchar(191) NOT NULL,
                synonyms text NOT NULL,
                bidirectional tinyint(1) DEFAULT 0,
                case_sensitive tinyint(1) DEFAULT 0,
                created_at timestamp DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY unique_term (agent_id, term),
                KEY idx_agent (agent_id)
            ) $charset_collate;";

            dbDelta($sql_synonyms);

            // Webhooks table
            $table_webhooks = $wpdb->prefix . 'inqyra_webhooks';
            $sql_webhooks = "CREATE TABLE $table_webhooks (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                agent_id bigint(20) unsigned NOT NULL,
                name varchar(255) NOT NULL,
                url varchar(2048) NOT NULL,
                secret varchar(64) NOT NULL,
                events longtext NOT NULL,
                headers longtext,
                enabled tinyint(1) DEFAULT 1,
                created_at timestamp DEFAULT CURRENT_TIMESTAMP,
                updated_at timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY idx_agent_enabled (agent_id, enabled)
            ) $charset_collate;";

            dbDelta($sql_webhooks);

            // Webhook logs table
            $table_webhook_logs = $wpdb->prefix . 'inqyra_webhook_logs';
            $sql_webhook_logs = "CREATE TABLE $table_webhook_logs (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                webhook_id bigint(20) unsigned NOT NULL,
                event_type varchar(50) NOT NULL,
                payload longtext,
                response_status int(11),
                response_body text,
                success tinyint(1) DEFAULT 0,
                retry_count int(11) DEFAULT 0,
                error_message text,
                sent_at timestamp DEFAULT CURRENT_TIMESTAMP,
                completed_at timestamp NULL,
                PRIMARY KEY (id),
                KEY idx_webhook (webhook_id),
                KEY idx_sent_at (sent_at)
            ) $charset_collate;";

            dbDelta($sql_webhook_logs);
        }

        // Conversations table
        $table_conversations = $wpdb->prefix . 'inqyra_conversations';
        $sql_conversations = "CREATE TABLE $table_conversations (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            agent_id bigint(20) unsigned NOT NULL,
            session_id varchar(64) NOT NULL,
            visitor_ip varchar(45),
            user_agent varchar(500),
            referrer_url varchar(500),
            started_at timestamp DEFAULT CURRENT_TIMESTAMP,
            last_message_at timestamp NULL,
            message_count int(11) DEFAULT 0,
            total_tokens int(11) DEFAULT 0,
            total_cost_usd decimal(10,6) DEFAULT 0,
            archived tinyint(1) DEFAULT 0,
            lead_captured tinyint(1) DEFAULT 0,
            ended_webhook_sent tinyint(1) DEFAULT 0,
            PRIMARY KEY (id),
            UNIQUE KEY unique_session (session_id),
            KEY idx_agent_started (agent_id, started_at),
            KEY idx_archived (archived)
        ) $charset_collate;";

        dbDelta($sql_conversations);

        // Messages table
        $table_messages = $wpdb->prefix . 'inqyra_messages';
        $sql_messages = "CREATE TABLE $table_messages (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            conversation_id bigint(20) unsigned NOT NULL,
            role enum('user','assistant','system') NOT NULL,
            content text NOT NULL,
            tokens_used int(11) DEFAULT 0,
            cost_usd decimal(10,6) DEFAULT 0,
            context_chunks longtext,
            created_at timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_conversation (conversation_id),
            KEY idx_created (created_at),
            KEY idx_role_created (role, created_at),
            KEY idx_conv_created (conversation_id, created_at)
        ) $charset_collate;";

        dbDelta($sql_messages);

        // Crawl runs table
        $table_crawl_runs = $wpdb->prefix . 'inqyra_crawl_runs';
        $sql_crawl_runs = "CREATE TABLE $table_crawl_runs (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            agent_id bigint(20) unsigned NOT NULL,
            status enum('pending','running','completed','failed') DEFAULT 'pending',
            total_posts int(11) DEFAULT 0,
            processed_posts int(11) DEFAULT 0,
            chunks_created int(11) DEFAULT 0,
            documents_processed int(11) DEFAULT 0,
            error_message text,
            started_at timestamp NULL,
            completed_at timestamp NULL,
            created_at timestamp DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_agent_status (agent_id, status)
        ) $charset_collate;";

        dbDelta($sql_crawl_runs);
    }

    /**
     * Create a default agent if none exists.
     */
    private static function create_default_agent() {
        global $wpdb;

        $table = $wpdb->prefix . 'inqyra_agents';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix.
        $count = $wpdb->get_var($wpdb->prepare('SELECT COUNT(*) FROM %i', $table));

        if ($count == 0) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Schema migration requires direct query.
            $wpdb->insert(
                $table,
                [
                    'name' => __('Default Agent', 'inqyra'),
                    'system_prompt' => class_exists( 'Inqyra_Default_Prompts' ) ? Inqyra_Default_Prompts::get( 'en' ) : 'You are a helpful assistant for this website.',
                    'language' => 'nl',
                    'provider' => 'claude',
                    'model' => 'claude-sonnet-4-20250514',
                    'greeting_message' => __('Hello! How can I help you today?', 'inqyra'),
                    'button_text' => '💬',
                    'crawl_parts' => json_encode(['p', 'h1', 'h2', 'h3', 'li']),
                    'crawl_post_types' => json_encode(['post', 'page']),
                    'widget_enabled' => 1,
                    'widget_display_mode' => 'all',
                    'widget_pages' => '[]',
                    'widget_mobile_enabled' => 1,
                    'active' => 1,
                ]
            );
        }
    }

    /**
     * Schedule cron events.
     */
    private static function schedule_cron_events() {
        // Register custom cron interval
        add_filter('cron_schedules', function($schedules) {
            $schedules['every_minute'] = [
                'interval' => 60,
                'display' => __('Every Minute', 'inqyra')
            ];
            $schedules['every_fifteen_minutes'] = [
                'interval' => 900,
                'display' => __('Every 15 Minutes', 'inqyra')
            ];
            return $schedules;
        });

        // Schedule embedding processing
        if (!wp_next_scheduled('inqyra_process_embeddings')) {
            wp_schedule_event(time(), 'every_minute', 'inqyra_process_embeddings');
        }

        // Schedule conversation archiving (daily)
        if (!wp_next_scheduled('inqyra_archive_conversations')) {
            wp_schedule_event(time(), 'daily', 'inqyra_archive_conversations');
        }

        // Premium cron events
        if ( function_exists( 'inqyra_fs' ) && inqyra_fs()->is__premium_only() ) {
            // Schedule conversation ended webhook check (every 15 minutes)
            if (!wp_next_scheduled('inqyra_check_ended_conversations')) {
                wp_schedule_event(time(), 'every_fifteen_minutes', 'inqyra_check_ended_conversations');
            }

            // Schedule webhook log cleanup (daily)
            if (!wp_next_scheduled('inqyra_clean_webhook_logs')) {
                wp_schedule_event(time(), 'daily', 'inqyra_clean_webhook_logs');
            }
        }
    }

    /**
     * Set default plugin options.
     */
    private static function set_default_options() {
        // Only set if not already set
        if (get_option('inqyra_settings') === false) {
            update_option('inqyra_settings', [
                'api_keys' => [
                    'claude' => '',
                    'openai' => '',
                    'gemini' => '',
                    'mistral' => '',
                ],
                'embedding_batch_size' => 5,
                'debug_mode' => false,
            ]);
        }
    }

    /**
     * Check and upgrade database if needed.
     * This runs on admin_init to catch version changes.
     */
    public static function maybe_upgrade() {
        $current_version = get_option('inqyra_db_version', '1.0.0');

        if (version_compare($current_version, self::DB_VERSION, '<')) {
            self::upgrade_database($current_version);
            update_option('inqyra_db_version', self::DB_VERSION);
        }
    }

    /**
     * Run database upgrades.
     *
     * @param string $from_version The version upgrading from.
     */
    private static function upgrade_database(string $from_version) {
        global $wpdb;

        // Upgrade to 1.1.0: Add performance indexes
        if (version_compare($from_version, '1.1.0', '<')) {
            // Add indexes to messages table
            $table_messages = $wpdb->prefix . 'inqyra_messages';

            // Validate table names (security: ensure only alphanumeric and underscores)
            if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_messages)) {
                return;
            }

            // Check if index exists before adding - use prepare() for the Key_name value
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix.
            $indexes = $wpdb->get_results($wpdb->prepare(
                'SHOW INDEX FROM %i WHERE Key_name = %s',
                $table_messages,
                'idx_role_created'
            ));
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            if (empty($indexes)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD INDEX idx_role_created (role, created_at)', $table_messages));
            }

            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix.
            $indexes = $wpdb->get_results($wpdb->prepare(
                'SHOW INDEX FROM %i WHERE Key_name = %s',
                $table_messages,
                'idx_conv_created'
            ));
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            if (empty($indexes)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD INDEX idx_conv_created (conversation_id, created_at)', $table_messages));
            }

            // Add indexes to knowledge_chunks table
            $table_chunks = $wpdb->prefix . 'inqyra_knowledge_chunks';

            if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_chunks)) {
                return;
            }

            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix.
            $indexes = $wpdb->get_results($wpdb->prepare(
                'SHOW INDEX FROM %i WHERE Key_name = %s',
                $table_chunks,
                'idx_agent_needs_embedding'
            ));
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            if (empty($indexes)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD INDEX idx_agent_needs_embedding (agent_id, needs_embedding)', $table_chunks));
            }
        }

        // Upgrade to 1.2.0: Add query rewrite columns
        if (version_compare($from_version, '1.2.0', '<')) {
            $table_agents = $wpdb->prefix . 'inqyra_agents';

            if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_agents)) {
                return;
            }

            // Add query_rewrite_enabled column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'query_rewrite_enabled'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `query_rewrite_enabled` tinyint(1) DEFAULT 1 AFTER `synonym_expansion_strategy`', $table_agents));
            }

            // Add query_rewrite_provider column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'query_rewrite_provider'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN `query_rewrite_provider` enum('claude','openai','gemini','mistral','deepseek') DEFAULT 'claude' AFTER `query_rewrite_enabled`", $table_agents));
            }

            // Add query_rewrite_model column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'query_rewrite_model'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN `query_rewrite_model` varchar(100) DEFAULT 'claude-3-haiku-20240307' AFTER `query_rewrite_provider`", $table_agents));
            }
        }

        // Upgrade to 1.3.0: Add title column to knowledge_chunks
        if (version_compare($from_version, '1.3.0', '<')) {
            $table_chunks = $wpdb->prefix . 'inqyra_knowledge_chunks';

            if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_chunks)) {
                return;
            }

            // Add title column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_chunks,
                'title'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `title` varchar(500) AFTER `url_path`', $table_chunks));
            }

            // Add fulltext index on title
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix.
            $indexes = $wpdb->get_results($wpdb->prepare(
                'SHOW INDEX FROM %i WHERE Key_name = %s',
                $table_chunks,
                'ft_title'
            ));
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            if (empty($indexes)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD FULLTEXT KEY ft_title (title)', $table_chunks));
            }
        }

        // Upgrade to 1.4.0: Add document_url column to chunk_links
        if (version_compare($from_version, '1.4.0', '<')) {
            $table_links = $wpdb->prefix . 'inqyra_chunk_links';

            if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_links)) {
                return;
            }

            // Add document_url column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_links,
                'document_url'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `document_url` varchar(2048) AFTER `document_chunk_id`', $table_links));
            }

            // Add index on document_url
            // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table name from $wpdb->prefix.
            $indexes = $wpdb->get_results($wpdb->prepare(
                'SHOW INDEX FROM %i WHERE Key_name = %s',
                $table_links,
                'idx_document_url'
            ));
            // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            if (empty($indexes)) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD INDEX idx_document_url (document_url(255))', $table_links));
            }
        }

        // Upgrade to 1.5.0: Add widget visibility columns
        if (version_compare($from_version, '1.5.0', '<')) {
            $table_agents = $wpdb->prefix . 'inqyra_agents';

            if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_agents)) {
                return;
            }

            // Add widget_enabled column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'widget_enabled'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `widget_enabled` tinyint(1) DEFAULT 1 AFTER `query_rewrite_model`', $table_agents));
            }

            // Add widget_display_mode column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'widget_display_mode'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare("ALTER TABLE %i ADD COLUMN `widget_display_mode` enum('all','include','exclude') DEFAULT 'all' AFTER `widget_enabled`", $table_agents));
            }

            // Add widget_pages column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'widget_pages'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `widget_pages` longtext AFTER `widget_display_mode`', $table_agents));
            }

            // Add widget_mobile_enabled column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'widget_mobile_enabled'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `widget_mobile_enabled` tinyint(1) DEFAULT 1 AFTER `widget_pages`', $table_agents));
            }

            // Add auto_recrawl_enabled column
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'auto_recrawl_enabled'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `auto_recrawl_enabled` tinyint(1) DEFAULT 0 AFTER `query_rewrite_model`', $table_agents));
            }
        }

        // Upgrade to 1.6.0: Add deepseek to provider ENUM columns
        if (version_compare($from_version, '1.6.0', '<')) {
            $table_agents = $wpdb->prefix . 'inqyra_agents';

            if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_agents)) {
                return;
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
            $wpdb->query($wpdb->prepare("ALTER TABLE %i MODIFY COLUMN `provider` enum('claude','openai','gemini','mistral','deepseek') DEFAULT 'claude'", $table_agents));
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
            $wpdb->query($wpdb->prepare("ALTER TABLE %i MODIFY COLUMN `query_rewrite_provider` enum('claude','openai','gemini','mistral','deepseek') DEFAULT 'claude'", $table_agents));
        }

        // Upgrade to 1.7.0: Add conversation_persist column
        if (version_compare($from_version, '1.7.0', '<')) {
            $table_agents = $wpdb->prefix . 'inqyra_agents';

            if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_agents)) {
                return;
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'conversation_persist'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `conversation_persist` tinyint(1) DEFAULT 0 AFTER `widget_mobile_enabled`', $table_agents));
            }
        }

        // Upgrade to 1.8.0: Add input placeholder columns
        if (version_compare($from_version, '1.8.0', '<')) {
            $table_agents = $wpdb->prefix . 'inqyra_agents';

            if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_agents)) {
                return;
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema migration requires direct query.
            $column_exists = $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME,
                $table_agents,
                'input_placeholder'
            ));
            if (!$column_exists) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `input_placeholder` varchar(255) DEFAULT %s AFTER `conversation_persist`', $table_agents, ''));
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration requires direct query. Table name from $wpdb->prefix.
                $wpdb->query($wpdb->prepare('ALTER TABLE %i ADD COLUMN `hide_input_placeholder` tinyint(1) DEFAULT 0 AFTER `input_placeholder`', $table_agents));
            }
        }
    }
}
