<?php
/**
 * Database Manager for BoostBot.
 *
 * Handles all database table creation and management.
 *
 * @package BoostBot
 * @since   1.0.0
 */

// Prevent direct access
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_DB_Manager
 *
 * Manages database operations including table creation,
 * upgrades, and removal.
 *
 * @since 1.0.0
 */
class BoostBot_DB_Manager {

    /**
     * WordPress database object.
     *
     * @var wpdb
     */
    private $wpdb;

    /**
     * Database charset collation.
     *
     * @var string
     */
    private $charset_collate;

    /**
     * Table prefix for BoostBot tables.
     *
     * @var string
     */
    private $table_prefix;

    /**
     * Array of table names.
     *
     * @var array
     */
    private $tables = array();

    /**
     * Constructor.
     */
    public function __construct() {
        global $wpdb;

        $this->wpdb            = $wpdb;
        $this->charset_collate = $wpdb->get_charset_collate();
        $this->table_prefix    = $wpdb->prefix . 'boostbot_';

        $this->define_tables();
    }

    /**
     * Define all table names.
     *
     * @return void
     */
    private function define_tables() {
        $this->tables = array(
            'leads'              => self::sanitize_identifier( $this->table_prefix . 'leads' ),
            'conversations'      => self::sanitize_identifier( $this->table_prefix . 'conversations' ),
            'knowledge_index'    => self::sanitize_identifier( $this->table_prefix . 'knowledge_index' ),
            'knowledge_chunks'   => self::sanitize_identifier( $this->table_prefix . 'knowledge_chunks' ),
            'knowledge_raptor_nodes' => self::sanitize_identifier( $this->table_prefix . 'knowledge_raptor_nodes' ),
            'index_queue'        => self::sanitize_identifier( $this->table_prefix . 'index_queue' ),
            'site_profile'       => self::sanitize_identifier( $this->table_prefix . 'site_profile' ),
            'widgets'            => self::sanitize_identifier( $this->table_prefix . 'widgets' ),
            'form_fields'        => self::sanitize_identifier( $this->table_prefix . 'form_fields' ),
            'chatbot_widgets'    => self::sanitize_identifier( $this->table_prefix . 'chatbot_widgets' ),
            'analytics'          => self::sanitize_identifier( $this->table_prefix . 'analytics' ),
            'cache'              => self::sanitize_identifier( $this->table_prefix . 'cache' ),
            'bots'               => self::sanitize_identifier( $this->table_prefix . 'bots' ),
            'personalities'      => self::sanitize_identifier( $this->table_prefix . 'personalities' ),
            'personality_traits' => self::sanitize_identifier( $this->table_prefix . 'personality_traits' ),
            'tones'              => self::sanitize_identifier( $this->table_prefix . 'tones' ),
            'contact_lists'      => self::sanitize_identifier( $this->table_prefix . 'contact_lists' ),
            'contacts'           => self::sanitize_identifier( $this->table_prefix . 'contacts' ),
            'galleries'          => self::sanitize_identifier( $this->table_prefix . 'galleries' ),
            'gallery_images'     => self::sanitize_identifier( $this->table_prefix . 'gallery_images' ),
            'appearance_presets' => self::sanitize_identifier( $this->table_prefix . 'appearance_presets' ),
            'opening_screen_presets' => self::sanitize_identifier( $this->table_prefix . 'opening_screen_presets' ),
            'meetings'           => self::sanitize_identifier( $this->table_prefix . 'meetings' ),
            'widget_usage'       => self::sanitize_identifier( $this->table_prefix . 'widget_usage' ),
            'bundles'            => self::sanitize_identifier( $this->table_prefix . 'bundles' ),
            'woocommerce_presets' => self::sanitize_identifier( $this->table_prefix . 'woocommerce_presets' ),
            'woocommerce_coupons' => self::sanitize_identifier( $this->table_prefix . 'woocommerce_coupons' ),
        );
    }

    /**
     * Get a table name by key.
     *
     * @param string $table_key The table key.
     * @return string|null The full table name or null if not found.
     */
    public function get_table( $table_key ) {
        return isset( $this->tables[ $table_key ] ) ? $this->tables[ $table_key ] : null;
    }

    /**
     * Get all table names.
     *
     * @return array
     */
    public function get_all_tables() {
        return $this->tables;
    }

    /**
     * Create all plugin database tables.
     *
     * @return void
     */
    public function create_tables() {
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $this->create_leads_table();
        $this->create_conversations_table();
        $this->create_knowledge_index_table();
        $this->create_knowledge_chunks_table();
        $this->create_knowledge_raptor_nodes_table();
        $this->create_index_queue_table();
        $this->create_site_profile_table();
        $this->create_widgets_table();
        $this->create_form_fields_table();
        $this->create_chatbot_widgets_table();
        $this->create_analytics_table();
        $this->create_cache_table();
        $this->create_bots_table();
        $this->create_personalities_table();
        $this->create_personality_traits_table();
        $this->create_tones_table();
        $this->create_contact_lists_table();
        $this->create_contacts_table();
        $this->create_galleries_table();
        $this->create_gallery_images_table();
        $this->create_appearance_presets_table();
        $this->create_opening_screen_presets_table();
        $this->create_meetings_table();
        $this->create_widget_usage_table();
        $this->create_bundles_table();
        // WooCommerce tables (presets + coupons) are created by the
        // class-migration-create-woocommerce-tables migration, which
        // runs immediately after create_tables() in the activation flow.

        // Create default personality and traits if needed
        $this->maybe_create_default_personality();
        $this->maybe_create_default_traits();
        
        // Create default tones if needed
        $this->maybe_create_default_tones();
        
        // Create default appearance presets if needed
        $this->maybe_create_default_appearance_presets();
        
        // Create default opening screen presets if needed
        $this->maybe_create_default_opening_screen_presets();

        // Create default bot only after defaults it depends on are seeded
        // (personality + appearance/opening presets).
        $this->maybe_create_default_bot();

        // Log table creation
        if ( defined( 'WP_DEBUG' ) && WP_DEBUG && class_exists( 'BoostBot_Logger' ) ) {
            BoostBot_Logger::info( 'Database tables created/updated', array(
                'tables' => array_keys( $this->tables ),
            ) );
        }
    }

    /**
     * Create the leads table.
     *
     * @return void
     */
    private function create_leads_table() {
        $table_name = $this->tables['leads'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            conversation_id varchar(100) NOT NULL,
            visitor_id varchar(100) NOT NULL,
            name varchar(255) DEFAULT NULL,
            email varchar(255) DEFAULT NULL,
            phone varchar(50) DEFAULT NULL,
            company varchar(255) DEFAULT NULL,
            lead_score int(11) DEFAULT 0,
            lead_status varchar(20) DEFAULT 'new',
            source_page varchar(500) DEFAULT NULL,
            referrer varchar(500) DEFAULT NULL,
            utm_data longtext DEFAULT NULL,
            form_data longtext DEFAULT NULL,
            conversation_summary text DEFAULT NULL,
            metadata longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_visitor_id (visitor_id),
            KEY idx_email (email),
            KEY idx_lead_status (lead_status),
            KEY idx_created_at (created_at),
            KEY idx_conversation_id (conversation_id)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the conversations table.
     *
     * @return void
     */
    private function create_conversations_table() {
        $table_name = $this->tables['conversations'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            conversation_id varchar(100) NOT NULL,
            visitor_id varchar(100) NOT NULL,
            session_data longtext DEFAULT NULL,
            context_data longtext DEFAULT NULL,
            total_messages int(11) DEFAULT 0,
            bot_messages int(11) DEFAULT 0,
            user_messages int(11) DEFAULT 0,
            widgets_used longtext DEFAULT NULL,
            conversion_achieved tinyint(1) DEFAULT 0,
            satisfaction_score int(11) DEFAULT NULL,
            started_at datetime DEFAULT CURRENT_TIMESTAMP,
            last_message_at datetime DEFAULT NULL,
            ended_at datetime DEFAULT NULL,
            duration_seconds int(11) DEFAULT NULL,
            ai_summary text DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY idx_conversation_id (conversation_id),
            KEY idx_visitor_id (visitor_id),
            KEY idx_started_at (started_at),
            KEY idx_conversion (conversion_achieved)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the knowledge index table.
     *
     * @return void
     */
    private function create_knowledge_index_table() {
        $table_name = $this->tables['knowledge_index'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            post_id bigint(20) unsigned DEFAULT NULL,
            post_type varchar(50) DEFAULT 'page',
            content_type varchar(50) NOT NULL,
            content_id bigint(20) unsigned DEFAULT NULL,
            title varchar(500) DEFAULT NULL,
            summary text DEFAULT NULL,
            full_content longtext DEFAULT NULL,
            technical_specs longtext DEFAULT NULL,
            price_info longtext DEFAULT NULL,
            keywords longtext DEFAULT NULL,
            embedding_vector longtext DEFAULT NULL,
            url varchar(500) DEFAULT NULL,
            language varchar(10) DEFAULT 'he',
            relevance_score float DEFAULT 0,
            indexed_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_post_id (post_id),
            KEY idx_post_type (post_type),
            KEY idx_content_type (content_type),
            KEY idx_content_id (content_id),
            KEY idx_updated_at (updated_at),
            KEY idx_relevance_score (relevance_score)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the knowledge chunks table (semantic chunk storage).
     *
     * Stores chunk-level text and embedding vectors for semantic retrieval on large sites.
     *
     * @since 1.7.0
     * @return void
     */
    private function create_knowledge_chunks_table() {
        $table_name = $this->tables['knowledge_chunks'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            knowledge_id bigint(20) unsigned NOT NULL,
            content_type varchar(50) NOT NULL,
            content_id bigint(20) unsigned DEFAULT NULL,
            chunk_index int(11) NOT NULL DEFAULT 0,
            chunk_text longtext DEFAULT NULL,
            embedding_vector longtext DEFAULT NULL,
            embedding_provider varchar(20) DEFAULT NULL,
            embedding_model varchar(100) DEFAULT NULL,
            embedding_dimension int(11) DEFAULT NULL,
            char_count int(11) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_knowledge_id (knowledge_id),
            KEY idx_content (content_type, content_id),
            KEY idx_updated_at (updated_at)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the knowledge RAPTOR nodes table (hierarchical summary tree).
     *
     * Stores summary nodes produced during indexing to enable tree-organized retrieval.
     *
     * @since 1.12.0
     * @return void
     */
    private function create_knowledge_raptor_nodes_table() {
        $table_name = $this->tables['knowledge_raptor_nodes'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            knowledge_id bigint(20) unsigned NOT NULL,
            content_type varchar(50) NOT NULL,
            content_id bigint(20) unsigned DEFAULT NULL,
            node_level int(11) NOT NULL DEFAULT 0,
            node_index int(11) NOT NULL DEFAULT 0,
            parent_node_id bigint(20) unsigned DEFAULT NULL,
            title varchar(500) DEFAULT NULL,
            url varchar(500) DEFAULT NULL,
            summary_text longtext DEFAULT NULL,
            child_node_ids longtext DEFAULT NULL,
            source_chunk_ids longtext DEFAULT NULL,
            meta_json longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_knowledge_id (knowledge_id),
            KEY idx_content (content_type, content_id),
            KEY idx_level (node_level),
            KEY idx_parent (parent_node_id),
            KEY idx_updated_at (updated_at)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the indexing queue table (resilient indexing).
     *
     * Stores a persistent queue of content items to index, with retry metadata.
     *
     * @since 1.13.0
     * @return void
     */
    private function create_index_queue_table() {
        $table_name = $this->tables['index_queue'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            content_id bigint(20) unsigned NOT NULL,
            content_type varchar(20) NOT NULL,
            priority tinyint(3) unsigned NOT NULL DEFAULT 5,
            status enum('pending','processing','completed','failed','skipped') NOT NULL DEFAULT 'pending',
            extraction_level tinyint(3) unsigned NOT NULL DEFAULT 5,
            attempts tinyint(3) unsigned NOT NULL DEFAULT 0,
            max_attempts tinyint(3) unsigned NOT NULL DEFAULT 3,
            content_size int(10) unsigned NOT NULL DEFAULT 0,
            last_error longtext DEFAULT NULL,
            memory_peak int(10) unsigned DEFAULT NULL,
            processing_time float DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            started_at datetime DEFAULT NULL,
            completed_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY unique_content (content_id, content_type),
            KEY idx_status_priority (status, priority),
            KEY idx_processing (status, started_at)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the site profile table.
     *
     * @return void
     */
    private function create_site_profile_table() {
        $table_name = $this->tables['site_profile'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id int(11) unsigned NOT NULL AUTO_INCREMENT,
            site_url varchar(500) NOT NULL,
            business_type varchar(100) DEFAULT NULL,
            business_description text DEFAULT NULL,
            main_topics longtext DEFAULT NULL,
            tone_of_voice varchar(50) DEFAULT 'professional',
            conversion_goals longtext DEFAULT NULL,
            indexed_content_types longtext DEFAULT NULL,
            total_posts_indexed int(11) DEFAULT 0,
            total_pages_indexed int(11) DEFAULT 0,
            total_products_indexed int(11) DEFAULT 0,
            last_full_index datetime DEFAULT NULL,
            configuration longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_site_url (site_url(191))
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the widgets table.
     *
     * @return void
     */
    private function create_widgets_table() {
        $table_name = $this->tables['widgets'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            name varchar(255) NOT NULL,
            type varchar(50) NOT NULL,
            description text DEFAULT NULL,
            config longtext DEFAULT NULL,
            is_active tinyint(1) DEFAULT 1,
            usage_count int(11) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_type (type),
            KEY idx_is_active (is_active)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the form_fields table.
     *
     * @return void
     */
    private function create_form_fields_table() {
        $table_name = $this->tables['form_fields'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            widget_id bigint(20) unsigned NOT NULL,
            field_type varchar(50) NOT NULL,
            label varchar(255) NOT NULL,
            placeholder varchar(255) DEFAULT NULL,
            name varchar(100) NOT NULL,
            is_required tinyint(1) DEFAULT 0,
            validation_rules longtext DEFAULT NULL,
            options longtext DEFAULT NULL,
            sort_order int(11) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_widget_id (widget_id),
            KEY idx_sort_order (sort_order)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the chatbot_widgets junction table.
     *
     * @return void
     */
    private function create_chatbot_widgets_table() {
        $table_name = $this->tables['chatbot_widgets'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            chatbot_id bigint(20) unsigned NOT NULL,
            widget_id bigint(20) unsigned NOT NULL,
            is_enabled tinyint(1) DEFAULT 1,
            custom_config longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_chatbot_widget (chatbot_id, widget_id),
            KEY idx_chatbot_id (chatbot_id),
            KEY idx_widget_id (widget_id)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the analytics table.
     *
     * @return void
     */
    private function create_analytics_table() {
        $table_name = $this->tables['analytics'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            metric_type varchar(100) NOT NULL,
            metric_value float DEFAULT 0,
            context longtext DEFAULT NULL,
            timestamp datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_metric_type (metric_type),
            KEY idx_timestamp (timestamp)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the cache table.
     *
     * @return void
     */
    private function create_cache_table() {
        $table_name = $this->tables['cache'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            cache_key varchar(255) NOT NULL,
            cache_type varchar(50) DEFAULT 'general',
            cache_data longtext DEFAULT NULL,
            hits int(11) DEFAULT 0,
            expires_at datetime DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_cache_key (cache_key),
            KEY idx_cache_type (cache_type),
            KEY idx_expires_at (expires_at)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the bots table.
     *
     * @return void
     */
    private function create_bots_table() {
        $table_name = $this->tables['bots'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            bot_name varchar(255) NOT NULL,
            bot_slug varchar(100) NOT NULL,
            language varchar(10) NOT NULL DEFAULT 'he',
            description text DEFAULT NULL,
            is_active tinyint(1) DEFAULT 1,
            test_mode tinyint(1) DEFAULT 0,
            is_default tinyint(1) DEFAULT 0,
            welcome_message text DEFAULT NULL,
            show_welcome_message tinyint(1) DEFAULT 1,
            bubble_icon varchar(255) DEFAULT NULL,
            primary_color varchar(20) DEFAULT NULL,
            appearance_preset_id bigint(20) unsigned DEFAULT NULL,
            opening_screen_preset_id bigint(20) unsigned DEFAULT NULL,
            opening_screen_overrides longtext DEFAULT NULL,
            position varchar(20) DEFAULT 'bottom-right',
            bot_output_char_limit int(11) DEFAULT NULL,
            brand_card_mode varchar(20) DEFAULT 'global',
            brand_card_override longtext DEFAULT NULL,
            personality_id bigint(20) unsigned DEFAULT NULL,
            system_prompt text DEFAULT NULL,
            tone varchar(50) DEFAULT NULL,
            proactive_engagement tinyint(1) DEFAULT 0,
            proactive_delay_seconds int(11) DEFAULT 10,
            personality_overrides longtext DEFAULT NULL,
            personality_traits longtext DEFAULT NULL,
            custom_instructions text DEFAULT NULL,
            rules text DEFAULT NULL,
            forbidden_topics text DEFAULT NULL,
            display_conditions longtext DEFAULT NULL,
            priority int(11) DEFAULT 0,
            knowledge_files longtext DEFAULT NULL,
            attachment_files longtext DEFAULT NULL,
            enabled_coupons longtext DEFAULT NULL,
            contact_list_id bigint(20) unsigned DEFAULT NULL,
            enabled_widgets longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            created_by bigint(20) unsigned DEFAULT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY idx_bot_slug (bot_slug),
            KEY idx_is_active (is_active),
            KEY idx_is_default (is_default),
            KEY idx_priority (priority)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the personalities table.
     *
     * @return void
     */
    private function create_personalities_table() {
        $table_name = $this->tables['personalities'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            name varchar(255) NOT NULL,
            slug varchar(100) NOT NULL,
            description text DEFAULT NULL,
            system_prompt text NOT NULL,
            traits longtext DEFAULT NULL,
            tone varchar(50) DEFAULT 'friendly',
            proactive_engagement tinyint(1) DEFAULT 1,
            proactive_delay_seconds int(11) DEFAULT 10,
            custom_instructions text DEFAULT NULL,
            is_default tinyint(1) DEFAULT 0,
            is_system tinyint(1) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_slug (slug),
            KEY idx_is_default (is_default)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the personality traits table.
     *
     * @return void
     */
    private function create_personality_traits_table() {
        $table_name = $this->tables['personality_traits'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            name varchar(100) NOT NULL,
            slug varchar(50) NOT NULL,
            description varchar(255) DEFAULT NULL,
            prompt_addition text DEFAULT NULL,
            is_active tinyint(1) DEFAULT 1,
            PRIMARY KEY (id),
            UNIQUE KEY idx_slug (slug)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the tones table.
     *
     * @return void
     */
    private function create_tones_table() {
        $table_name = $this->tables['tones'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            name varchar(100) NOT NULL,
            slug varchar(50) NOT NULL,
            emoji varchar(20) DEFAULT NULL,
            description varchar(255) DEFAULT NULL,
            system_prompt text DEFAULT NULL,
            is_system tinyint(1) DEFAULT 0,
            is_active tinyint(1) DEFAULT 1,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_slug (slug),
            KEY idx_is_active (is_active)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the contact lists table.
     *
     * @return void
     */
    private function create_contact_lists_table() {
        $table_name = $this->tables['contact_lists'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            description text DEFAULT NULL,
            is_primary tinyint(1) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_is_primary (is_primary)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the contacts table.
     *
     * @return void
     */
    private function create_contacts_table() {
        $table_name = $this->tables['contacts'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            list_id bigint(20) unsigned NOT NULL,
            name varchar(255) NOT NULL,
            title varchar(255) DEFAULT NULL,
            department varchar(255) DEFAULT NULL,
            phone varchar(50) DEFAULT NULL,
            email varchar(255) DEFAULT NULL,
            whatsapp varchar(50) DEFAULT NULL,
            description text DEFAULT NULL,
            availability text DEFAULT NULL,
            meeting_availability longtext DEFAULT NULL,
            specialties longtext DEFAULT NULL,
            sort_order int(11) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_list_id (list_id),
            KEY idx_sort_order (sort_order)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the galleries table.
     *
     * @return void
     */
    private function create_galleries_table() {
        $table_name = $this->tables['galleries'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            name varchar(255) NOT NULL,
            description text DEFAULT NULL,
            is_active tinyint(1) DEFAULT 1,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_is_active (is_active)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the gallery images table.
     *
     * @return void
     */
    private function create_gallery_images_table() {
        $table_name = $this->tables['gallery_images'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            gallery_id bigint(20) unsigned NOT NULL,
            attachment_id bigint(20) unsigned DEFAULT NULL,
            url varchar(500) NOT NULL,
            title varchar(255) DEFAULT NULL,
            description text DEFAULT NULL,
            link_url varchar(500) DEFAULT NULL,
            sort_order int(11) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_gallery_id (gallery_id),
            KEY idx_sort_order (sort_order)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the appearance presets table.
     *
     * @return void
     */
    private function create_appearance_presets_table() {
        $table_name = $this->tables['appearance_presets'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            name varchar(255) NOT NULL,
            slug varchar(100) NOT NULL,
            description text DEFAULT NULL,
            is_default tinyint(1) DEFAULT 0,
            is_system tinyint(1) DEFAULT 0,
            primary_color varchar(20) DEFAULT '#3BC2D0',
            secondary_color varchar(20) DEFAULT '#E32774',
            background_color varchar(20) DEFAULT '#FFFFFF',
            header_background varchar(20) DEFAULT '#3BC2D0',
            header_text_color varchar(20) DEFAULT '#FFFFFF',
            user_message_bg varchar(20) DEFAULT '#3BC2D0',
            user_message_text varchar(20) DEFAULT '#FFFFFF',
            bot_message_bg varchar(20) DEFAULT '#F3F4F6',
            bot_message_text varchar(20) DEFAULT '#1F2937',
            input_bg varchar(20) DEFAULT '#FFFFFF',
            input_text varchar(20) DEFAULT '#1F2937',
            input_border varchar(20) DEFAULT '#E5E7EB',
            button_bg varchar(20) DEFAULT '#3BC2D0',
            button_text varchar(20) DEFAULT '#FFFFFF',
            button_hover_bg varchar(20) DEFAULT '#2BA8B5',
            link_color varchar(20) DEFAULT '#3BC2D0',
            font_family varchar(255) DEFAULT 'inherit',
            font_size_base varchar(20) DEFAULT '14px',
            font_size_header varchar(20) DEFAULT '18px',
            font_weight_normal varchar(20) DEFAULT '400',
            font_weight_bold varchar(20) DEFAULT '600',
            line_height varchar(20) DEFAULT '1.5',
            letter_spacing varchar(20) DEFAULT 'normal',
            bubble_size varchar(20) DEFAULT '60px',
            bubble_icon varchar(50) DEFAULT 'robot',
            bubble_custom_icon varchar(500) DEFAULT NULL,
            bubble_animation varchar(50) DEFAULT 'none',
            window_width varchar(20) DEFAULT '380px',
            window_height varchar(20) DEFAULT '600px',
            window_border_radius varchar(20) DEFAULT '16px',
            window_shadow varchar(100) DEFAULT '0 10px 40px rgba(0,0,0,0.15)',
            custom_css text DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_slug (slug),
            KEY idx_is_default (is_default)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the opening screen presets table.
     *
     * @return void
     */
    private function create_opening_screen_presets_table() {
        $table_name = $this->tables['opening_screen_presets'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            name varchar(255) NOT NULL,
            slug varchar(100) NOT NULL,
            description text DEFAULT NULL,
            is_default tinyint(1) DEFAULT 0,
            is_system tinyint(1) DEFAULT 0,
            mode varchar(50) NOT NULL DEFAULT 'questions_ai',
            welcome_text varchar(500) DEFAULT NULL,
            show_welcome tinyint(1) DEFAULT 1,
            questions longtext DEFAULT NULL,
            ai_instructions text DEFAULT NULL,
            qa_pairs longtext DEFAULT NULL,
            contact_list_id bigint(20) unsigned DEFAULT NULL,
            show_departments tinyint(1) DEFAULT 1,
            show_contact_cards tinyint(1) DEFAULT 1,
            custom_prompt text DEFAULT NULL,
            attached_files longtext DEFAULT NULL,
            opening_buttons longtext DEFAULT NULL,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_slug (slug),
            KEY idx_is_default (is_default),
            KEY idx_mode (mode)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }


    /**
     * Create default bot if none exists.
     *
     * @return void
     */
    private function maybe_create_default_bot() {
        $table_name = $this->tables['bots'];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $count = $this->wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        if ( (int) $count === 0 ) {
            // Get existing settings for migration
            $settings = get_option( 'boostbot_settings', array() );
            $appearance_id = null;
            $opening_id = null;
            $personality_id = null;

            // Resolve default appearance preset to link on first insert.
            $appearance_table = $this->tables['appearance_presets'];
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $appearance_id = $this->wpdb->get_var( "SELECT id FROM {$appearance_table} WHERE is_default = 1 LIMIT 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

            // Resolve default opening screen preset to link on first insert.
            $opening_table = $this->tables['opening_screen_presets'];
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $opening_id = $this->wpdb->get_var( "SELECT id FROM {$opening_table} WHERE is_default = 1 LIMIT 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

            // Resolve default personality to link on first insert.
            $personalities_table = $this->tables['personalities'];
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $personality_id = $this->wpdb->get_var( "SELECT id FROM {$personalities_table} WHERE is_default = 1 LIMIT 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $this->wpdb->insert(
                $table_name,
                array(
                    'bot_name'        => __( 'נציג שירות', 'boostbot' ),
                    'bot_slug'        => 'default',
                    'description'     => __( 'הצ\'אטבוט הראשי של האתר שלך', 'boostbot' ),
                    'is_active'       => 1,
                    'test_mode'       => 1,
                    'is_default'      => 1,
                    'welcome_message' => isset( $settings['welcome_message'] ) ? $settings['welcome_message'] : __( 'שלום! 👋 איך אוכל לעזור לך היום?', 'boostbot' ),
                    'primary_color'   => isset( $settings['primary_color'] ) ? $settings['primary_color'] : '#3BC2D0',
                    'position'        => 'bottom-right',
                    'appearance_preset_id' => ! empty( $appearance_id ) ? (int) $appearance_id : null,
                    'opening_screen_preset_id' => ! empty( $opening_id ) ? (int) $opening_id : null,
                    'personality_id'  => ! empty( $personality_id ) ? (int) $personality_id : null,
                    'proactive_delay_seconds' => 10,
                    'created_at'      => current_time( 'mysql' ),
                    'created_by'      => get_current_user_id(),
                ),
                array( '%s', '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%s', '%d' )
            );

            // Link default presets to the new bot
            $bot_id = $this->wpdb->insert_id;
            if ( $bot_id ) {
                // Link default appearance preset
                if ( $appearance_id ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $this->wpdb->update( $table_name, array( 'appearance_preset_id' => (int) $appearance_id ), array( 'id' => $bot_id ), array( '%d' ), array( '%d' ) );
                }

                // Link default opening screen preset (FAQ)
                if ( $opening_id ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $this->wpdb->update( $table_name, array( 'opening_screen_preset_id' => (int) $opening_id ), array( 'id' => $bot_id ), array( '%d' ), array( '%d' ) );
                }

                // Link default personality
                if ( $personality_id ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $this->wpdb->update( $table_name, array( 'personality_id' => (int) $personality_id ), array( 'id' => $bot_id ), array( '%d' ), array( '%d' ) );
                }
            }

            if ( class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::info( 'Default bot created with linked presets', array( 'bot_id' => $bot_id ) );
            }
        }
    }

    /**
     * Create default personality if none exists.
     *
     * @return void
     */
    private function maybe_create_default_personality() {
        $table_name = $this->tables['personalities'];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $count = $this->wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        if ( (int) $count === 0 ) {
            $default_prompt = __(
                'אתה נציג שירות לקוחות ידידותי ומקצועי.

הנחיות התנהגות:
- תמיד שאל שאלות כדי להבין טוב יותר את צרכי הלקוח
- הצע מוצרים ושירותים רלוונטיים בצורה טבעית (לא דוחפת)
- התייחס לכל שאלה ברצינות ותן מענה מקיף
- אם אתה לא בטוח במשהו - הודה בכך והצע לחבר לנציג אנושי
- שמור על טון חם ומזמין
- סכם את הנקודות החשובות בסוף כל תשובה ארוכה

סגנון תקשורת:
- השתמש בשפה ידידותית ונגישה
- הימנע משפה טכנית מדי אלא אם הלקוח מבקש
- השתמש באימוג\'ים במידה לתקשורת יותר חמה
- היה תמציתי אך מקיף',
                'boostbot'
            );

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $this->wpdb->insert(
                $table_name,
                array(
                    'name'                    => __( 'אישיות ברירת מחדל', 'boostbot' ),
                    'slug'                    => 'default',
                    'description'             => __( 'אישיות ידידותית, תקשורתית ושיווקית', 'boostbot' ),
                    'system_prompt'           => $default_prompt,
                    'tone'                    => 'friendly',
                    'proactive_engagement'    => 1,
                    'proactive_delay_seconds' => 10,
                    'is_default'              => 1,
                    'is_system'               => 1,
                    'created_at'              => current_time( 'mysql' ),
                ),
                array( '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%s' )
            );

            if ( class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::info( 'Default personality created' );
            }
        }
    }

    /**
     * Create default personality traits if none exist.
     *
     * @return void
     */
    private function maybe_create_default_traits() {
        $table_name = $this->tables['personality_traits'];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $count = $this->wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        if ( (int) $count === 0 ) {
            $traits = array(
                array(
                    'name'            => __( 'נחמד', 'boostbot' ),
                    'slug'            => 'friendly',
                    'description'     => __( 'שומר על טון חם וידידותי', 'boostbot' ),
                    'prompt_addition' => __( 'תמיד שמור על טון חם, נחמד ומזמין.', 'boostbot' ),
                ),
                array(
                    'name'            => __( 'שיווקי', 'boostbot' ),
                    'slug'            => 'sales',
                    'description'     => __( 'מציע מוצרים ושירותים', 'boostbot' ),
                    'prompt_addition' => __( 'הצע מוצרים ושירותים רלוונטיים בצורה טבעית.', 'boostbot' ),
                ),
                array(
                    'name'            => __( 'מקצועי', 'boostbot' ),
                    'slug'            => 'professional',
                    'description'     => __( 'שומר על רמה גבוהה של מקצועיות', 'boostbot' ),
                    'prompt_addition' => __( 'שמור על טון מקצועי ורשמי.', 'boostbot' ),
                ),
                array(
                    'name'            => __( 'מצחיק', 'boostbot' ),
                    'slug'            => 'humorous',
                    'description'     => __( 'משלב הומור קל', 'boostbot' ),
                    'prompt_addition' => __( 'שלב הומור קל ובדיחות במידה.', 'boostbot' ),
                ),
                array(
                    'name'            => __( 'מלומד', 'boostbot' ),
                    'slug'            => 'knowledgeable',
                    'description'     => __( 'מדגיש מומחיות וידע', 'boostbot' ),
                    'prompt_addition' => __( 'הדגם ידע עמוק ומומחיות בתחום.', 'boostbot' ),
                ),
                array(
                    'name'            => __( 'אמפתי', 'boostbot' ),
                    'slug'            => 'empathetic',
                    'description'     => __( 'מראה הבנה ואמפתיה', 'boostbot' ),
                    'prompt_addition' => __( 'הראה אמפתיה והבנה לצרכי הלקוח.', 'boostbot' ),
                ),
                array(
                    'name'            => __( 'יעיל', 'boostbot' ),
                    'slug'            => 'efficient',
                    'description'     => __( 'תשובות תמציתיות וישירות', 'boostbot' ),
                    'prompt_addition' => __( 'תן תשובות תמציתיות וישירות, ללא מילים מיותרות.', 'boostbot' ),
                ),
                array(
                    'name'            => __( 'דוחף', 'boostbot' ),
                    'slug'            => 'pushy',
                    'description'     => __( 'מעודד לפעולה מיידית', 'boostbot' ),
                    'prompt_addition' => __( 'עודד את הלקוח לבצע פעולה עכשיו, הדגש מבצעים מוגבלים.', 'boostbot' ),
                ),
            );

            foreach ( $traits as $trait ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $this->wpdb->insert(
                    $table_name,
                    array(
                        'name'            => $trait['name'],
                        'slug'            => $trait['slug'],
                        'description'     => $trait['description'],
                        'prompt_addition' => $trait['prompt_addition'],
                        'is_active'       => 1,
                    ),
                    array( '%s', '%s', '%s', '%s', '%d' )
                );
            }

            if ( class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::info( 'Default personality traits created', array( 'count' => count( $traits ) ) );
            }
        }
    }

    /**
     * Create default tones if none exist.
     *
     * @return void
     */
    private function maybe_create_default_tones() {
        $table_name = $this->tables['tones'];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $count = $this->wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        if ( (int) $count === 0 && class_exists( 'BoostBot_Tone_Model' ) ) {
            $tones = BoostBot_Tone_Model::get_default_tones();

            foreach ( $tones as $tone ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $this->wpdb->insert(
                    $table_name,
                    array(
                        'name'          => $tone['name'],
                        'slug'          => $tone['slug'],
                        'emoji'         => $tone['emoji'],
                        'description'   => $tone['description'],
                        'system_prompt' => $tone['system_prompt'],
                        'is_system'     => $tone['is_system'],
                        'is_active'     => $tone['is_active'],
                        'created_at'    => current_time( 'mysql' ),
                    ),
                    array( '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s' )
                );
            }

            if ( class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::info( 'Default tones created', array( 'count' => count( $tones ) ) );
            }
        }
    }

    /**
     * Create default appearance presets if none exist.
     *
     * @return void
     */
    private function maybe_create_default_appearance_presets() {
        $table_name = $this->tables['appearance_presets'];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $count = $this->wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        if ( (int) $count === 0 ) {
            // Try to load from seed file first (preset 18 - the polished default)
            $seed_file = BOOSTBOT_PLUGIN_DIR . 'includes/database/seeds/default-appearance-preset.json';
            $seed_loaded = false;

            if ( file_exists( $seed_file ) ) {
                // phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
                $seed_json = file_get_contents( $seed_file );
                $seed_data = json_decode( $seed_json, true );

                if ( is_array( $seed_data ) && ! empty( $seed_data['name'] ) ) {
                    // Prepare data for insertion
                    $insert_data = array(
                        'name'              => $seed_data['name'],
                        'slug'              => sanitize_title( $seed_data['name'] ),
                        'description'       => isset( $seed_data['description'] ) ? $seed_data['description'] : '',
                        'is_default'        => 1,
                        'is_system'         => 1,
                        'primary_color'     => isset( $seed_data['primary_color'] ) ? $seed_data['primary_color'] : '#3BC2D0',
                        'secondary_color'   => isset( $seed_data['secondary_color'] ) ? $seed_data['secondary_color'] : '#E32774',
                        'background_color'  => isset( $seed_data['background_color'] ) ? $seed_data['background_color'] : '#FFFFFF',
                        'header_background' => isset( $seed_data['header_background'] ) ? $seed_data['header_background'] : '#3BC2D0',
                        'header_text_color' => isset( $seed_data['header_text_color'] ) ? $seed_data['header_text_color'] : '#FFFFFF',
                        'user_message_bg'   => isset( $seed_data['user_message_bg'] ) ? $seed_data['user_message_bg'] : '#3BC2D0',
                        'user_message_text' => isset( $seed_data['user_message_text'] ) ? $seed_data['user_message_text'] : '#FFFFFF',
                        'bot_message_bg'    => isset( $seed_data['bot_message_bg'] ) ? $seed_data['bot_message_bg'] : '#F3F4F6',
                        'bot_message_text'  => isset( $seed_data['bot_message_text'] ) ? $seed_data['bot_message_text'] : '#1F2937',
                        'input_bg'          => isset( $seed_data['input_bg'] ) ? $seed_data['input_bg'] : '#FFFFFF',
                        'input_text'        => isset( $seed_data['input_text'] ) ? $seed_data['input_text'] : '#1F2937',
                        'input_border'      => isset( $seed_data['input_border'] ) ? $seed_data['input_border'] : '#E5E7EB',
                        'button_bg'         => isset( $seed_data['button_bg'] ) ? $seed_data['button_bg'] : '#3BC2D0',
                        'button_text'       => isset( $seed_data['button_text'] ) ? $seed_data['button_text'] : '#FFFFFF',
                        'button_hover_bg'   => isset( $seed_data['button_hover_bg'] ) ? $seed_data['button_hover_bg'] : '#2BA8B5',
                        'link_color'        => isset( $seed_data['link_color'] ) ? $seed_data['link_color'] : '#3BC2D0',
                        'font_family'       => isset( $seed_data['font_family'] ) ? $seed_data['font_family'] : 'inherit',
                        'font_size_base'    => isset( $seed_data['font_size_base'] ) ? $seed_data['font_size_base'] : '14px',
                        'font_size_header'  => isset( $seed_data['font_size_header'] ) ? $seed_data['font_size_header'] : '18px',
                        'font_weight_normal'=> isset( $seed_data['font_weight_normal'] ) ? $seed_data['font_weight_normal'] : '400',
                        'font_weight_bold'  => isset( $seed_data['font_weight_bold'] ) ? $seed_data['font_weight_bold'] : '600',
                        'line_height'       => isset( $seed_data['line_height'] ) ? $seed_data['line_height'] : '1.5',
                        'letter_spacing'    => isset( $seed_data['letter_spacing'] ) ? $seed_data['letter_spacing'] : 'normal',
                        'bubble_size'       => isset( $seed_data['bubble_size'] ) ? $seed_data['bubble_size'] : '60px',
                        'bubble_icon'       => isset( $seed_data['bubble_icon'] ) ? $seed_data['bubble_icon'] : 'robot',
                        'bubble_animation'  => isset( $seed_data['bubble_animation'] ) ? $seed_data['bubble_animation'] : 'none',
                        'window_width'      => isset( $seed_data['window_width'] ) ? $seed_data['window_width'] : '380px',
                        'window_height'     => isset( $seed_data['window_height'] ) ? $seed_data['window_height'] : '600px',
                        'window_border_radius' => isset( $seed_data['window_border_radius'] ) ? $seed_data['window_border_radius'] : '16px',
                        'window_shadow'     => isset( $seed_data['window_shadow'] ) ? $seed_data['window_shadow'] : '0 10px 40px rgba(0,0,0,0.15)',
                        'custom_css'        => isset( $seed_data['custom_css'] ) ? $seed_data['custom_css'] : '',
                        'created_at'        => current_time( 'mysql' ),
                    );

                    // Add Week 21 JSON fields if present
                    if ( isset( $seed_data['global_values'] ) && is_array( $seed_data['global_values'] ) ) {
                        $insert_data['global_values'] = wp_json_encode( $seed_data['global_values'] );
                    }
                    if ( isset( $seed_data['element_settings'] ) && is_array( $seed_data['element_settings'] ) ) {
                        $insert_data['element_settings'] = wp_json_encode( $seed_data['element_settings'] );
                    }
                    if ( isset( $seed_data['widget_settings'] ) && is_array( $seed_data['widget_settings'] ) ) {
                        $insert_data['widget_settings'] = wp_json_encode( $seed_data['widget_settings'] );
                    }

                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $result = $this->wpdb->insert( $table_name, $insert_data );

                    if ( $result ) {
                        $seed_loaded = true;
                        if ( class_exists( 'BoostBot_Logger' ) ) {
                            BoostBot_Logger::info( 'Default appearance preset loaded from seed file', array( 'name' => $seed_data['name'] ) );
                        }
                    }
                }
            }

            // Fallback to hardcoded preset if seed file failed
            if ( ! $seed_loaded ) {
                $presets = array(
                    array(
                        'name'              => __( 'ברירת מחדל', 'boostbot' ),
                        'slug'              => 'default',
                        'description'       => __( 'עיצוב ברירת המחדל של BoostBot', 'boostbot' ),
                        'is_default'        => 1,
                        'is_system'         => 1,
                        'primary_color'     => '#3BC2D0',
                        'secondary_color'   => '#E32774',
                        'header_background' => '#3BC2D0',
                        'user_message_bg'   => '#3BC2D0',
                    ),
                );

                foreach ( $presets as $preset ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $this->wpdb->insert(
                        $table_name,
                        array_merge(
                            $preset,
                            array( 'created_at' => current_time( 'mysql' ) )
                        )
                    );
                }

                if ( class_exists( 'BoostBot_Logger' ) ) {
                    BoostBot_Logger::info( 'Default appearance presets created (fallback)', array( 'count' => count( $presets ) ) );
                }
            }
        }
    }

    /**
     * Create default opening screen presets if none exist.
     *
     * @return void
     */
    private function maybe_create_default_opening_screen_presets() {
        $table_name = $this->tables['opening_screen_presets'];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $count = $this->wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        if ( (int) $count === 0 ) {
            $presets = array(
                array(
                    'name'          => __( 'שאלות נפוצות', 'boostbot' ),
                    'slug'          => 'faq-questions',
                    'description'   => __( 'מציג שאלות נפוצות שה-AI עונה עליהן', 'boostbot' ),
                    'is_default'    => 1,
                    'is_system'     => 1,
                    'mode'          => 'questions_ai',
                    'welcome_text'  => __( 'שלום! 👋 במה אוכל לעזור?', 'boostbot' ),
                    'show_welcome'  => 1,
                    'questions'     => wp_json_encode( array(
                        array( 'text' => __( 'מה שעות הפעילות?', 'boostbot' ), 'icon' => '🕐' ),
                        array( 'text' => __( 'איך יוצרים קשר?', 'boostbot' ), 'icon' => '📞' ),
                        array( 'text' => __( 'מה המחירים?', 'boostbot' ), 'icon' => '💰' ),
                    ) ),
                ),
                array(
                    'name'          => __( 'הצעת עזרה', 'boostbot' ),
                    'slug'          => 'offer-help',
                    'description'   => __( 'הבוט מציע עזרה אוטומטית בפתיחה', 'boostbot' ),
                    'is_default'    => 0,
                    'is_system'     => 1,
                    'mode'          => 'questions_ai',
                    'welcome_text'  => __( 'היי! 👋', 'boostbot' ),
                    'show_welcome'  => 1,
                ),
                array(
                    'name'          => __( 'ללא הודעת פתיחה', 'boostbot' ),
                    'slug'          => 'no-opening',
                    'description'   => __( 'אין הודעה אוטומטית - ממתין ללקוח', 'boostbot' ),
                    'is_default'    => 0,
                    'is_system'     => 1,
                    'mode'          => 'questions_ai',
                    'welcome_text'  => '',
                    'show_welcome'  => 0,
                    'questions'     => wp_json_encode( array() ),
                ),
            );

            foreach ( $presets as $preset ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $this->wpdb->insert(
                    $table_name,
                    array_merge(
                        $preset,
                        array( 'created_at' => current_time( 'mysql' ) )
                    )
                );
            }

            if ( class_exists( 'BoostBot_Logger' ) ) {
                BoostBot_Logger::info( 'Default opening screen presets created', array( 'count' => count( $presets ) ) );
            }
        }
    }

    /**
     * Seed default entities for any language.
     *
     * Creates a default bot, personality, tones, traits, widgets, opening
     * screen preset, and appearance preset for the given language.
     * Idempotent — skips each entity type if records already exist for that language.
     *
     * System prompts are always in English (LLM instructions).
     * UI-facing labels use a per-language translations map; unknown languages
     * fall back to English.
     *
     * @param string $language Two-letter language code (e.g. 'he', 'en').
     * @return void
     */
    /**
     * Ensure at least one row with is_default=1 exists for the given language.
     * If none exists, promote the first row (lowest id) for that language.
     */
    private function ensure_default_exists( string $table, string $language ): void {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $has_default = (int) $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$table} WHERE language = %s AND is_default = 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $language
            )
        );
        if ( $has_default > 0 ) {
            return;
        }
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table identifier sanitized via sanitize_identifier(); values bind via prepare().
        $first_id = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT id FROM {$table} WHERE language = %s ORDER BY id ASC LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $language
            )
        );
        if ( $first_id ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $this->wpdb->update( $table, array( 'is_default' => 1 ), array( 'id' => (int) $first_id ), array( '%d' ), array( '%d' ) );
        }
    }

    public function seed_language_defaults( string $language ): void {
        $labels = self::get_seed_labels( $language );
        $prompts = self::get_seed_prompts();
        $now = current_time( 'mysql' );

        // ── Bot ────────────────────────────────────────────────────
        $bot_table = $this->tables['bots'];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $existing_bot = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$bot_table} WHERE language = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $language
            )
        );
        if ( (int) $existing_bot === 0 ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $this->wpdb->insert( $bot_table, array(
                'bot_name'             => $labels['bot_name'],
                'bot_slug'             => 'default-' . $language,
                'language'             => $language,
                'description'          => $labels['bot_description'],
                'is_active'            => 1,
                'is_default'           => 1,
                'welcome_message'      => $labels['welcome_message'],
                'show_welcome_message' => 1,
                'position'             => 'bottom-right',
                'priority'             => 0,
                'created_at'           => $now,
                'updated_at'           => $now,
            ) );
        } else {
            $this->ensure_default_exists( $bot_table, $language );
            $system_slug = 'default-' . $language;
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $system_bot = $this->wpdb->get_row(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                $this->wpdb->prepare(
                    "SELECT id FROM {$bot_table} WHERE language = %s AND bot_slug = %s LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    $language,
                    $system_slug
                )
            );
            if ( $system_bot ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $this->wpdb->update(
                    $bot_table,
                    array( 'bot_name' => $labels['bot_name'], 'description' => $labels['bot_description'] ),
                    array( 'id' => (int) $system_bot->id ),
                    array( '%s', '%s' ),
                    array( '%d' )
                );
            } else {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $legacy = (int) $this->wpdb->get_var(
                    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                    $this->wpdb->prepare(
                        "SELECT COUNT(*) FROM {$bot_table} WHERE language = %s AND bot_slug = 'default'", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                        $language
                    )
                );
                if ( ! $legacy ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $this->wpdb->update( $bot_table, array( 'is_default' => 0 ), array( 'language' => $language, 'is_default' => 1 ) );
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $this->wpdb->insert( $bot_table, array(
                        'bot_name'             => $labels['bot_name'],
                        'bot_slug'             => $system_slug,
                        'language'             => $language,
                        'description'          => $labels['bot_description'],
                        'is_active'            => 1,
                        'is_default'           => 1,
                        'welcome_message'      => $labels['welcome_message'],
                        'show_welcome_message' => 1,
                        'position'             => 'bottom-right',
                        'priority'             => 0,
                        'created_at'           => $now,
                        'updated_at'           => $now,
                    ) );
                }
            }
        }

        // ── Personality ────────────────────────────────────────────
        $pers_table = $this->tables['personalities'];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $existing_pers = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$pers_table} WHERE language = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $language
            )
        );
        if ( (int) $existing_pers === 0 ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $this->wpdb->insert( $pers_table, array(
                'name'          => $labels['personality_name'],
                'slug'          => 'default-' . $language,
                'language'      => $language,
                'description'   => $labels['personality_description'],
                'system_prompt' => $prompts['personality'],
                'tone'          => 'friendly-' . $language,
                'is_default'    => 1,
                'is_system'     => 1,
                'created_at'    => $now,
            ) );
        } else {
            $this->ensure_default_exists( $pers_table, $language );
            $system_slug = 'default-' . $language;
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $system_pers = $this->wpdb->get_row(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                $this->wpdb->prepare(
                    "SELECT id FROM {$pers_table} WHERE language = %s AND slug = %s AND is_system = 1 LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    $language,
                    $system_slug
                )
            );
            if ( $system_pers ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $this->wpdb->update(
                    $pers_table,
                    array( 'name' => $labels['personality_name'], 'description' => $labels['personality_description'], 'system_prompt' => $prompts['personality'] ),
                    array( 'id' => (int) $system_pers->id ),
                    array( '%s', '%s', '%s' ),
                    array( '%d' )
                );
            } else {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $legacy = (int) $this->wpdb->get_var(
                    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                    $this->wpdb->prepare(
                        "SELECT COUNT(*) FROM {$pers_table} WHERE language = %s AND (slug = 'default' OR slug = 'default-personality') AND is_system = 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                        $language
                    )
                );
                if ( ! $legacy ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $this->wpdb->update( $pers_table, array( 'is_default' => 0 ), array( 'language' => $language, 'is_default' => 1 ) );
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $this->wpdb->insert( $pers_table, array(
                        'name'          => $labels['personality_name'],
                        'slug'          => $system_slug,
                        'language'      => $language,
                        'description'   => $labels['personality_description'],
                        'system_prompt' => $prompts['personality'],
                        'tone'          => 'friendly-' . $language,
                        'is_default'    => 1,
                        'is_system'     => 1,
                        'created_at'    => $now,
                    ) );
                }
            }
        }

        // ── Opening screen preset ──────────────────────────────────
        $osp_table = $this->tables['opening_screen_presets'];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $existing_osp = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$osp_table} WHERE language = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $language
            )
        );
        if ( (int) $existing_osp === 0 ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $this->wpdb->insert( $osp_table, array(
                'name'         => $labels['osp_name'],
                'slug'         => 'faq-questions-' . $language,
                'language'     => $language,
                'description'  => $labels['osp_description'],
                'is_default'   => 1,
                'is_system'    => 1,
                'mode'         => 'questions_ai',
                'welcome_text' => $labels['osp_welcome'],
                'show_welcome' => 1,
                'questions'    => wp_json_encode( $labels['osp_questions'] ),
                'created_at'   => $now,
            ) );
        } else {
            $this->ensure_default_exists( $osp_table, $language );
        }

        // ── Tones ──────────────────────────────────────────────────
        $tone_table = $this->tables['tones'];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $existing_tones = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$tone_table} WHERE language = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $language
            )
        );
        if ( (int) $existing_tones === 0 ) {
            $tone_keys = array( 'friendly', 'professional', 'casual', 'formal', 'humorous', 'sales', 'supportive', 'expert' );
            foreach ( $tone_keys as $key ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $this->wpdb->insert( $tone_table, array(
                    'name'          => $labels['tones'][ $key ]['name'],
                    'slug'          => $key . '-' . $language,
                    'emoji'         => $labels['tones'][ $key ]['emoji'],
                    'language'      => $language,
                    'description'   => $labels['tones'][ $key ]['description'],
                    'system_prompt' => $prompts['tones'][ $key ],
                    'is_system'     => 1,
                    'is_active'     => 1,
                    'created_at'    => $now,
                ), array( '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s' ) );
            }
        }

        // ── Personality traits ─────────────────────────────────────
        $trait_table = $this->tables['personality_traits'];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $existing_traits = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$trait_table} WHERE language = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $language
            )
        );
        if ( (int) $existing_traits === 0 ) {
            $trait_keys = array( 'friendly', 'sales', 'professional', 'humorous', 'knowledgeable', 'empathetic', 'efficient', 'pushy' );
            foreach ( $trait_keys as $key ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $this->wpdb->insert( $trait_table, array(
                    'name'            => $labels['traits'][ $key ]['name'],
                    'slug'            => $key . '-' . $language,
                    'language'        => $language,
                    'description'     => $labels['traits'][ $key ]['description'],
                    'prompt_addition' => $prompts['traits'][ $key ],
                    'is_active'       => 1,
                ), array( '%s', '%s', '%s', '%s', '%s', '%d' ) );
            }
        }

        // ── Widgets ────────────────────────────────────────────────
        $widget_table = $this->tables['widgets'];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $existing_widgets = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$widget_table} WHERE language = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $language
            )
        );
        if ( (int) $existing_widgets === 0 ) {
            foreach ( $labels['widgets'] as $widget ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $this->wpdb->insert( $widget_table, array(
                    'name'        => $widget['name'],
                    'type'        => $widget['type'],
                    'language'    => $language,
                    'description' => $widget['description'],
                    'config'      => is_string( $widget['config'] ) ? $widget['config'] : wp_json_encode( $widget['config'] ),
                    'is_active'   => 1,
                    'created_at'  => $now,
                    'updated_at'  => $now,
                ) );
            }
        }

        // ── Appearance preset ──────────────────────────────────────
        $ap_table = $this->tables['appearance_presets'];
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $existing_ap = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM {$ap_table} WHERE language = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $language
            )
        );
        if ( (int) $existing_ap === 0 ) {
            $seed_path = BOOSTBOT_PLUGIN_DIR . 'includes/database/seeds/default-appearance-preset.json';
            $insert_data = array(
                'name'        => $labels['appearance_name'],
                'slug'        => 'default-' . $language,
                'language'    => $language,
                'description' => $labels['appearance_description'],
                'is_default'  => 1,
                'is_system'   => 1,
                'created_at'  => $now,
            );

            if ( file_exists( $seed_path ) ) {
                // phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
                $raw = file_get_contents( $seed_path );
                $seed_data = json_decode( $raw, true );
                if ( is_array( $seed_data ) ) {
                    $copy_fields = array(
                        'primary_color', 'secondary_color', 'background_color',
                        'header_background', 'header_text_color',
                        'user_message_bg', 'user_message_text',
                        'bot_message_bg', 'bot_message_text',
                        'input_bg', 'input_text', 'input_border',
                        'button_bg', 'button_text', 'button_hover_bg', 'link_color',
                        'font_family', 'font_size_base', 'font_size_header',
                        'font_weight_normal', 'font_weight_bold',
                        'line_height', 'letter_spacing',
                        'bubble_size', 'bubble_icon', 'bubble_custom_icon', 'bubble_animation',
                        'window_width', 'window_height', 'window_border_radius', 'window_shadow',
                        'custom_css',
                    );
                    foreach ( $copy_fields as $field ) {
                        if ( isset( $seed_data[ $field ] ) ) {
                            $insert_data[ $field ] = $seed_data[ $field ];
                        }
                    }
                    if ( 'he' !== $language && isset( $seed_data['element_settings'] ) && is_array( $seed_data['element_settings'] ) ) {
                        $he_to_lang = array(
                            'הקלד הודעה...' => 'Type a message...',
                            'מקוון'         => 'Online',
                        );
                        if ( isset( $seed_data['element_settings']['inputArea']['placeholder'] ) ) {
                            $seed_data['element_settings']['inputArea']['placeholder'] = $he_to_lang[ $seed_data['element_settings']['inputArea']['placeholder'] ] ?? 'Type a message...';
                        }
                        if ( isset( $seed_data['element_settings']['header']['subtitle'] ) ) {
                            $seed_data['element_settings']['header']['subtitle'] = $he_to_lang[ $seed_data['element_settings']['header']['subtitle'] ] ?? 'Online';
                        }
                    }
                    foreach ( array( 'global_values', 'element_settings', 'widget_settings' ) as $json_field ) {
                        if ( isset( $seed_data[ $json_field ] ) && is_array( $seed_data[ $json_field ] ) ) {
                            $insert_data[ $json_field ] = wp_json_encode( $seed_data[ $json_field ] );
                        }
                    }
                }
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $this->wpdb->insert( $ap_table, $insert_data );
        } else {
            $this->ensure_default_exists( $ap_table, $language );
        }

        // ── WooCommerce Preset (only if WooCommerce is active) ─────
        if ( class_exists( 'WooCommerce' ) && isset( $this->tables['woocommerce_presets'] ) ) {
            $woo_table = $this->tables['woocommerce_presets'];
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $existing_woo = $this->wpdb->get_var(
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                $this->wpdb->prepare(
                    "SELECT COUNT(*) FROM {$woo_table} WHERE language = %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                    $language
                )
            );
            if ( (int) $existing_woo === 0 ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                $this->wpdb->insert( $woo_table, array(
                    'name'                  => $labels['woo_preset_name'] ?? 'Default Settings',
                    'slug'                  => 'default-' . $language,
                    'language'              => $language,
                    'is_default'            => 1,
                    'is_system'             => 1,
                    'is_active'             => 1,
                    'product_mode'          => 'all',
                    'bundle_enabled'        => 1,
                    'bundle_min_products'   => 2,
                    'bundle_max_products'   => 5,
                    'bundle_discount_type'  => 'percent',
                    'bundle_discount_value' => 10,
                    'include_on_sale'       => 1,
                    'include_variable'      => 1,
                    'show_savings'          => 1,
                    'show_original_prices'  => 1,
                    'round_prices'          => 1,
                    'round_to'              => 0,
                    'created_at'            => $now,
                    'updated_at'            => $now,
                ) );
            }
        }

        if ( class_exists( 'BoostBot_Logger' ) ) {
            BoostBot_Logger::info( "Seeded language defaults for '{$language}'" );
        }
    }

    /**
     * Get UI-facing labels for seed data in the given language.
     *
     * System prompts are NOT included here — they live in get_seed_prompts()
     * because they're always in English (instructions to the LLM).
     *
     * @param string $language Language code.
     * @return array Label map.
     */
    private static function get_seed_labels( string $language ): array {
        $translations = array(
            'he' => array(
                'bot_name'                => 'נציג שירות',
                'bot_description'         => 'הצ\'אטבוט הראשי של האתר שלך',
                'welcome_message'         => 'שלום! 👋 איך אוכל לעזור לך היום?',
                'personality_name'        => 'אישיות ברירת מחדל',
                'personality_description' => 'אישיות ידידותית, תקשורתית ושיווקית',
                'osp_name'                => 'שאלות נפוצות',
                'osp_description'         => 'מציג שאלות נפוצות שה-AI עונה עליהן',
                'osp_welcome'             => 'שלום! 👋 במה אוכל לעזור?',
                'osp_questions'           => array(
                    array( 'text' => 'מה שעות הפעילות?', 'icon' => '🕐' ),
                    array( 'text' => 'איך יוצרים קשר?', 'icon' => '📞' ),
                    array( 'text' => 'מה המחירים?', 'icon' => '💰' ),
                ),
                'appearance_name'         => 'ברירת מחדל',
                'appearance_description'  => 'עיצוב ברירת המחדל של BoostBot',
                'woo_preset_name'         => 'הגדרות ברירת מחדל',
                'tones' => array(
                    'friendly'     => array( 'name' => 'ידידותי',  'emoji' => '😊', 'description' => 'טון חם ומזמין' ),
                    'professional' => array( 'name' => 'מקצועי',   'emoji' => '💼', 'description' => 'טון עסקי ומקצועי' ),
                    'casual'       => array( 'name' => 'נינוח',    'emoji' => '🎉', 'description' => 'טון קליל ונינוח' ),
                    'formal'       => array( 'name' => 'רשמי',     'emoji' => '📋', 'description' => 'טון רשמי ומכובד' ),
                    'humorous'     => array( 'name' => 'הומוריסטי','emoji' => '😄', 'description' => 'טון שמח עם הומור' ),
                    'sales'        => array( 'name' => 'שיווקי',   'emoji' => '🛒', 'description' => 'טון שיווקי ומכירתי' ),
                    'supportive'   => array( 'name' => 'תומך',     'emoji' => '🤝', 'description' => 'טון תומך ואמפתי' ),
                    'expert'       => array( 'name' => 'מומחה',    'emoji' => '🎓', 'description' => 'טון סמכותי ומומחה' ),
                ),
                'traits' => array(
                    'friendly'      => array( 'name' => 'נחמד',   'description' => 'שומר על טון חם וידידותי' ),
                    'sales'         => array( 'name' => 'שיווקי', 'description' => 'מציע מוצרים ושירותים' ),
                    'professional'  => array( 'name' => 'מקצועי', 'description' => 'שומר על רמה גבוהה של מקצועיות' ),
                    'humorous'      => array( 'name' => 'מצחיק',  'description' => 'משלב הומור קל' ),
                    'knowledgeable' => array( 'name' => 'מלומד',  'description' => 'מדגיש מומחיות וידע' ),
                    'empathetic'    => array( 'name' => 'אמפתי',  'description' => 'מראה הבנה ואמפתיה' ),
                    'efficient'     => array( 'name' => 'יעיל',   'description' => 'תשובות תמציתיות וישירות' ),
                    'pushy'         => array( 'name' => 'דוחף',   'description' => 'מעודד לפעולה מיידית' ),
                ),
                'widgets' => array(
                    array( 'name' => 'טופס מהיר',      'type' => 'dynamic_form', 'description' => 'כשהלקוח מעוניין להשאיר פרטים ליצירת קשר או שיחזרו אליו',
                        'config' => wp_json_encode( array( 'fields' => array(
                            array( 'name' => 'name', 'label' => 'שם', 'type' => 'text', 'required' => true, 'placeholder' => 'השם שלך' ),
                            array( 'name' => 'phone', 'label' => 'טלפון', 'type' => 'tel', 'required' => true, 'placeholder' => 'מספר טלפון' ),
                        ), 'submit_text' => 'שלח', 'success_message' => 'תודה! נחזור אליך בהקדם', 'creates_lead' => true ) ) ),
                    array( 'name' => 'טופס יצירת קשר', 'type' => 'dynamic_form', 'description' => 'כשהלקוח מעוניין לשלוח הודעה מפורטת',
                        'config' => wp_json_encode( array( 'fields' => array(
                            array( 'name' => 'name', 'label' => 'שם מלא', 'type' => 'text', 'required' => true, 'placeholder' => 'שם מלא' ),
                            array( 'name' => 'phone', 'label' => 'טלפון', 'type' => 'tel', 'required' => true, 'placeholder' => 'מספר טלפון' ),
                            array( 'name' => 'email', 'label' => 'אימייל', 'type' => 'email', 'required' => false, 'placeholder' => 'כתובת אימייל' ),
                            array( 'name' => 'message', 'label' => 'הודעה', 'type' => 'textarea', 'required' => false, 'placeholder' => 'ההודעה שלך' ),
                        ), 'submit_text' => 'שלח הודעה', 'success_message' => 'תודה על פנייתך! נחזור אליך בהקדם', 'creates_lead' => true ) ) ),
                    array( 'name' => 'וואטסאפ',       'type' => 'whatsapp',     'description' => 'כשהלקוח מעוניין ליצור קשר בוואטסאפ',
                        'config' => wp_json_encode( array( 'phone_number' => '', 'message_template' => 'היי, פניתי דרך הבוט באתר' ) ) ),
                    array( 'name' => 'יומן',           'type' => 'calendar',     'description' => 'כשהלקוח מעוניין לקבוע פגישה או שיחה',
                        'config' => wp_json_encode( array( 'title' => 'קביעת פגישה', 'meeting_types' => array(
                            array( 'name' => 'פגישה פרונטלית', 'duration' => 60, 'icon' => '🏢' ),
                            array( 'name' => 'שיחת טלפון', 'duration' => 30, 'icon' => '📞' ),
                            array( 'name' => 'שיחת וידאו', 'duration' => 45, 'icon' => '💻' ),
                        ) ) ) ),
                    array( 'name' => 'גלריה',         'type' => 'gallery',      'description' => 'כשהלקוח מעוניין לראות גלריית תמונות',
                        'config' => wp_json_encode( array( 'images' => array() ) ) ),
                    array( 'name' => 'סרטון YouTube',  'type' => 'youtube',      'description' => 'כשהלקוח מעוניין לצפות בסרטון',
                        'config' => wp_json_encode( array( 'video_url' => '' ) ) ),
                    array( 'name' => 'כרטיס קשר',     'type' => 'contact_card', 'description' => 'כשהלקוח מעוניין לראות פרטי קשר',
                        'config' => wp_json_encode( array( 'name' => '', 'title' => '', 'phone' => '', 'email' => '' ) ) ),
                ),
            ),
            'en' => array(
                'bot_name'                => 'Customer Service',
                'bot_description'         => 'The main chatbot for your website',
                'welcome_message'         => 'Hello! 👋 How can I help you today?',
                'personality_name'        => 'Default Personality',
                'personality_description' => 'A friendly and professional personality',
                'osp_name'                => 'FAQ Questions',
                'osp_description'         => 'Shows frequently asked questions answered by AI',
                'osp_welcome'             => 'Hello! 👋 How can I help?',
                'osp_questions'           => array(
                    array( 'text' => 'What are your hours?', 'icon' => '🕐' ),
                    array( 'text' => 'How can I contact you?', 'icon' => '📞' ),
                    array( 'text' => 'What are the prices?', 'icon' => '💰' ),
                ),
                'appearance_name'         => 'Default',
                'appearance_description'  => 'BoostBot\'s default design',
                'woo_preset_name'         => 'Default Settings',
                'tones' => array(
                    'friendly'     => array( 'name' => 'Friendly',     'emoji' => '😊', 'description' => 'Warm and welcoming tone' ),
                    'professional' => array( 'name' => 'Professional', 'emoji' => '💼', 'description' => 'Business-like and professional tone' ),
                    'casual'       => array( 'name' => 'Casual',       'emoji' => '🎉', 'description' => 'Light and relaxed tone' ),
                    'formal'       => array( 'name' => 'Formal',       'emoji' => '📋', 'description' => 'Formal and respectful tone' ),
                    'humorous'     => array( 'name' => 'Humorous',     'emoji' => '😄', 'description' => 'Happy tone with humor' ),
                    'sales'        => array( 'name' => 'Sales',        'emoji' => '🛒', 'description' => 'Marketing and sales-oriented tone' ),
                    'supportive'   => array( 'name' => 'Supportive',   'emoji' => '🤝', 'description' => 'Supportive and empathetic tone' ),
                    'expert'       => array( 'name' => 'Expert',       'emoji' => '🎓', 'description' => 'Expert-level authoritative tone' ),
                ),
                'traits' => array(
                    'friendly'      => array( 'name' => 'Friendly',      'description' => 'Maintains a warm and friendly tone' ),
                    'sales'         => array( 'name' => 'Sales',         'description' => 'Suggests products and services' ),
                    'professional'  => array( 'name' => 'Professional',  'description' => 'Maintains a high level of professionalism' ),
                    'humorous'      => array( 'name' => 'Humorous',      'description' => 'Incorporates light humor' ),
                    'knowledgeable' => array( 'name' => 'Knowledgeable', 'description' => 'Emphasizes expertise and knowledge' ),
                    'empathetic'    => array( 'name' => 'Empathetic',    'description' => 'Shows understanding and empathy' ),
                    'efficient'     => array( 'name' => 'Efficient',     'description' => 'Concise and direct responses' ),
                    'pushy'         => array( 'name' => 'Pushy',         'description' => 'Encourages immediate action' ),
                ),
                'widgets' => array(
                    array( 'name' => 'Quick Form',         'type' => 'dynamic_form', 'description' => 'When the customer wants to leave details or wants a callback - show this form',
                        'config' => wp_json_encode( array( 'fields' => array(
                            array( 'name' => 'name', 'label' => 'Name', 'type' => 'text', 'required' => true, 'placeholder' => 'Your name' ),
                            array( 'name' => 'phone', 'label' => 'Phone', 'type' => 'tel', 'required' => true, 'placeholder' => 'Phone number' ),
                        ), 'submit_text' => 'Send', 'success_message' => 'Thank you! We\'ll get back to you soon', 'creates_lead' => true ) ) ),
                    array( 'name' => 'Full Contact Form',  'type' => 'dynamic_form', 'description' => 'When the customer wants to send a detailed message - show this form',
                        'config' => wp_json_encode( array( 'fields' => array(
                            array( 'name' => 'name', 'label' => 'Full Name', 'type' => 'text', 'required' => true, 'placeholder' => 'Full name' ),
                            array( 'name' => 'phone', 'label' => 'Phone', 'type' => 'tel', 'required' => true, 'placeholder' => 'Phone number' ),
                            array( 'name' => 'email', 'label' => 'Email', 'type' => 'email', 'required' => false, 'placeholder' => 'Email address' ),
                            array( 'name' => 'message', 'label' => 'Message', 'type' => 'textarea', 'required' => false, 'placeholder' => 'Your message' ),
                        ), 'submit_text' => 'Send Message', 'success_message' => 'Thank you for your inquiry! We\'ll get back to you soon', 'creates_lead' => true ) ) ),
                    array( 'name' => 'WhatsApp',           'type' => 'whatsapp',     'description' => 'When the customer wants to contact via WhatsApp - show this button',
                        'config' => wp_json_encode( array( 'phone_number' => '', 'message_template' => 'Hi, I contacted through the website bot' ) ) ),
                    array( 'name' => 'Calendar',           'type' => 'calendar',     'description' => 'When the customer wants to schedule a meeting or call - show this calendar',
                        'config' => wp_json_encode( array( 'title' => 'Schedule Meeting', 'meeting_types' => array(
                            array( 'name' => 'In-person Meeting', 'duration' => 60, 'icon' => '🏢' ),
                            array( 'name' => 'Phone Call', 'duration' => 30, 'icon' => '📞' ),
                            array( 'name' => 'Video Call', 'duration' => 45, 'icon' => '💻' ),
                        ) ) ) ),
                    array( 'name' => 'Gallery',            'type' => 'gallery',      'description' => 'When the customer wants to see work or examples - show this gallery',
                        'config' => wp_json_encode( array( 'images' => array() ) ) ),
                    array( 'name' => 'YouTube Video',      'type' => 'youtube',      'description' => 'When the customer wants to watch a video - show this video',
                        'config' => wp_json_encode( array( 'video_url' => '' ) ) ),
                    array( 'name' => 'Contact Card',       'type' => 'contact_card', 'description' => 'When the customer wants contact details - show this card',
                        'config' => wp_json_encode( array( 'name' => '', 'title' => '', 'phone' => '', 'email' => '' ) ) ),
                ),
            ),
        );

        return $translations[ $language ] ?? $translations['en'];
    }

    /**
     * Get system prompts used in seed data.
     *
     * These are instructions to the LLM and remain in English regardless of
     * the bot's configured language.
     *
     * @return array Prompt map.
     */
    private static function get_seed_prompts(): array {
        return array(
            'personality' => "You are a friendly and professional customer service representative.\n"
                . "Answer clearly and helpfully. If you don't know, say so honestly.\n"
                . "Guide the user gently toward conversion when appropriate.",
            'tones' => array(
                'friendly'     => "You are a friendly and warm customer service representative.\n\nBehavior guidelines:\n- Always maintain a warm and welcoming tone\n- Use accessible and easy-to-understand language\n- Show empathy and understanding of customer needs\n- Ask questions to better understand needs\n- Proactively offer help\n\nCommunication style:\n- Use emojis sparingly for a warmer feel\n- Address the customer directly\n- End messages with an offer for further help",
                'professional' => "You are a professional and experienced advisor.\n\nBehavior guidelines:\n- Maintain a professional and serious tone\n- Demonstrate deep knowledge in the field\n- Provide comprehensive and detailed responses\n- Explain professional concepts clearly\n- Be precise with data and details\n\nCommunication style:\n- Use professional but clear language\n- Avoid slang and informal language\n- Build structured and organized responses\n- Treat every question with maximum seriousness",
                'casual'       => "You are a helpful friend who loves to help.\n\nBehavior guidelines:\n- Speak naturally and casually\n- Use everyday language\n- Make the conversation pleasant and light\n- Don't be too formal\n- Show personality and positive energy\n\nCommunication style:\n- Use emojis freely\n- Light slang is okay\n- Answer briefly and directly\n- Create a pleasant and relaxed atmosphere",
                'formal'       => "You are an official representative of the organization.\n\nBehavior guidelines:\n- Maintain high formality and courtesy\n- Address respectfully\n- Be precise and meticulous\n- Avoid humor or casualness\n- Conduct yourself in an orderly and professional manner\n\nCommunication style:\n- Use formal and clear language\n- Avoid emojis and informal markers\n- Build well-structured responses\n- Maintain maximum courtesy and politeness",
                'humorous'     => "You are a cheerful helper who loves to laugh.\n\nBehavior guidelines:\n- Incorporate light humor and jokes in moderation\n- Make the conversation enjoyable\n- Don't lose relevance because of humor\n- Know when to be serious when needed\n- Use positive and pleasant humor\n\nCommunication style:\n- Use funny emojis\n- Wordplay is welcome\n- Maintain a positive and optimistic tone\n- Don't overdo it - humor complements the information",
                'sales'        => "You are a professional and trustworthy sales advisor.\n\nBehavior guidelines:\n- Highlight the benefits of products/services\n- Identify customer needs and match solutions\n- Emphasize added value and uniqueness\n- Gently encourage action\n- Be trustworthy and not too pushy\n\nCommunication style:\n- Use persuasive but credible language\n- Emphasize benefits for the customer\n- Offer alternatives and comparisons\n- End with a clear call to action",
                'supportive'   => "You are a dedicated and empathetic support representative.\n\nBehavior guidelines:\n- Show full understanding of customer frustration\n- Listen carefully to the problem\n- Offer practical solutions\n- Close accompaniment until the issue is resolved\n- Ensure the customer is satisfied at the end\n\nCommunication style:\n- Use empathetic statements\n- Never blame the customer\n- Explain the solution step by step\n- Offer additional help if needed",
                'expert'       => "You are a leading expert in your field.\n\nBehavior guidelines:\n- Display deep and professional knowledge\n- Explain technical concepts clearly\n- Give recommendations based on experience\n- Share professional insights and tips\n- Be authoritative yet accessible\n\nCommunication style:\n- Use professional terminology with explanations\n- Present examples and case studies\n- Build deep and comprehensive responses\n- Reference additional sources when relevant",
            ),
            'traits' => array(
                'friendly'      => 'Always maintain a warm, friendly, and welcoming tone.',
                'sales'         => 'Naturally suggest relevant products and services.',
                'professional'  => 'Maintain a professional and formal tone.',
                'humorous'      => 'Incorporate light humor and jokes in moderation.',
                'knowledgeable' => 'Demonstrate deep knowledge and expertise in the field.',
                'empathetic'    => 'Show empathy and understanding of customer needs.',
                'efficient'     => 'Give concise and direct answers without unnecessary words.',
                'pushy'         => 'Encourage the customer to take action now, emphasize limited-time offers.',
            ),
        );
    }

    /**
     * Drop all plugin tables.
     *
     * Use with caution - only for uninstall.
     *
     * @return void
     */
    public function drop_tables() {
        foreach ( $this->tables as $table_name ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
            $this->wpdb->query( $this->wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table_name ) );
        }
    }

    /**
     * Check if a table exists.
     *
     * @param string $table_key The table key.
     * @return bool
     */
    public function table_exists( $table_key ) {
        if ( ! isset( $this->tables[ $table_key ] ) ) {
            return false;
        }

        $table_name = $this->tables[ $table_key ];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $this->wpdb->get_var(
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            $this->wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name )
        );

        return $result === $table_name;
    }

    /**
     * Check if all tables exist.
     *
     * @return bool
     */
    public function all_tables_exist() {
        foreach ( array_keys( $this->tables ) as $table_key ) {
            if ( ! $this->table_exists( $table_key ) ) {
                return false;
            }
        }
        return true;
    }

    /**
     * Get table status (for debugging).
     *
     * @return array
     */
    public function get_table_status() {
        $status = array();

        foreach ( $this->tables as $key => $table_name ) {
            $exists = $this->table_exists( $key );
            $count  = 0;

            if ( $exists ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
                $count = (int) $this->wpdb->get_var( "SELECT COUNT(*) FROM {$table_name}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            }

            $status[ $key ] = array(
                'table_name' => $table_name,
                'exists'     => $exists,
                'row_count'  => $count,
            );
        }

        return $status;
    }

    /**
     * Create the meetings table.
     *
     * @return void
     */
    private function create_meetings_table() {
        $table_name = $this->tables['meetings'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            conversation_id varchar(100) DEFAULT NULL,
            chatbot_id bigint(20) unsigned DEFAULT NULL,
            widget_id bigint(20) unsigned DEFAULT NULL,
            customer_name varchar(255) DEFAULT NULL,
            customer_email varchar(255) DEFAULT NULL,
            customer_phone varchar(50) DEFAULT NULL,
            title varchar(255) NOT NULL,
            description text DEFAULT NULL,
            meeting_date date NOT NULL,
            start_time time NOT NULL,
            end_time time NOT NULL,
            timezone varchar(50) DEFAULT 'Asia/Jerusalem',
            google_event_id varchar(255) DEFAULT NULL,
            google_meet_link varchar(500) DEFAULT NULL,
            google_calendar_link varchar(500) DEFAULT NULL,
            attendee_contacts longtext DEFAULT NULL,
            assigned_contact_id bigint(20) unsigned DEFAULT NULL,
            custom_fields longtext DEFAULT NULL,
            ai_summary text DEFAULT NULL,
            status enum('scheduled','confirmed','cancelled','completed','no_show') DEFAULT 'scheduled',
            reminder_sent tinyint(1) DEFAULT 0,
            confirmation_sent tinyint(1) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_conversation_id (conversation_id),
            KEY idx_chatbot_id (chatbot_id),
            KEY idx_meeting_date (meeting_date),
            KEY idx_status (status),
            KEY idx_google_event_id (google_event_id),
            KEY idx_assigned_contact_id (assigned_contact_id)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the widget_usage table.
     * Tracks widget usage per bot.
     *
     * @return void
     */
    private function create_widget_usage_table() {
        $table_name = $this->tables['widget_usage'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            language varchar(10) NOT NULL DEFAULT 'he',
            widget_id bigint(20) unsigned NOT NULL,
            bot_id bigint(20) unsigned NOT NULL,
            usage_count int(11) DEFAULT 0,
            last_used_at datetime DEFAULT CURRENT_TIMESTAMP,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_widget_bot (widget_id, bot_id),
            KEY idx_widget_id (widget_id),
            KEY idx_bot_id (bot_id)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }

    /**
     * Create the bundles table.
     * Stores AI-generated product bundles.
     *
     * @since 1.0.0
     * @return void
     */
    private function create_bundles_table() {
        $table_name = $this->tables['bundles'];

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $sql = "CREATE TABLE {$table_name} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            conversation_id varchar(100) DEFAULT NULL,
            chatbot_id bigint(20) unsigned DEFAULT NULL,
            widget_id bigint(20) unsigned DEFAULT NULL,
            customer_criteria longtext DEFAULT NULL,
            products longtext NOT NULL,
            total_price decimal(10,2) NOT NULL,
            original_price decimal(10,2) DEFAULT NULL,
            discount_amount decimal(10,2) DEFAULT 0,
            discount_percent decimal(5,2) DEFAULT 0,
            status varchar(20) DEFAULT 'suggested',
            language varchar(10) NOT NULL DEFAULT 'he',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            converted_at datetime DEFAULT NULL,
            PRIMARY KEY (id),
            KEY idx_conversation_id (conversation_id),
            KEY idx_chatbot_id (chatbot_id),
            KEY idx_status (status),
            KEY idx_language (language),
            KEY idx_created_at (created_at)
        ) {$this->charset_collate};"; // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        dbDelta( $sql );
    }


    /**
     * Restrict a SQL identifier to [A-Za-z0-9_] characters.
     *
     * DDL cannot use $wpdb->prepare() for identifiers, so any identifier
     * that ends up interpolated into a query first passes through this
     * allow-list.
     *
     * @param string $identifier Raw identifier.
     * @return string Identifier with disallowed characters removed.
     */
    private static function sanitize_identifier( $identifier ) {
        return preg_replace( '/[^A-Za-z0-9_]/', '', (string) $identifier );
    }
}

