<?php

if (!defined('ABSPATH')) {
    exit;
}

class AI_eShop_Chat_Database_Manager {

    
    private static $instance = null;

    
    private $embeddings_table;

    
    private $chat_history_table;

    
    private $content_gaps_table;

    
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    
    private function __construct() {
        global $wpdb;
        $this->embeddings_table = $wpdb->prefix . 'aieo_chat_embeddings';
        $this->chat_history_table = $wpdb->prefix . 'aieo_chat_history';
        $this->content_gaps_table = $wpdb->prefix . 'aieo_chat_content_gaps';

        
        
        
        
        
        add_filter('aieo_chat_retention_days', array($this, 'filter_retention_days'));

        
        add_action('aieo_chat_cleanup_cron', array($this, 'cleanup_old_chats'));
        if (!wp_next_scheduled('aieo_chat_cleanup_cron')) {
            wp_schedule_event(time(), 'daily', 'aieo_chat_cleanup_cron');
        }
    }

    
    public static function ensure_chat_history_columns() {
        static $done = false;
        if ($done) { return; }
        $done = true;
        global $wpdb;
        $table = $wpdb->prefix . 'aieo_chat_history';
        if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)) !== $table) {
            return; 
        }
        $wpdb->suppress_errors();
        $has = $wpdb->get_var($wpdb->prepare(
            "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = 'user_rating'",
            DB_NAME, $table
        ));
        if (empty($has)) {
            
            $wpdb->query("ALTER TABLE {$table} ADD COLUMN user_rating TINYINT UNSIGNED DEFAULT NULL COMMENT 'User feedback rating 0-100, only stored if changed from default 50'");
            $idx = $wpdb->get_var($wpdb->prepare(
                "SELECT INDEX_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND INDEX_NAME = 'user_rating'",
                DB_NAME, $table
            ));
            if (empty($idx)) {
                
                $wpdb->query("ALTER TABLE {$table} ADD KEY user_rating (user_rating)");
            }
            if (defined('WP_DEBUG') && WP_DEBUG) { error_log('AIEO: added missing user_rating column to chat history table'); }
        }
        
        
        
        $token_columns = array(
            'tokens_in'   => 'tokens_in INT UNSIGNED DEFAULT NULL',
            'tokens_out'  => 'tokens_out INT UNSIGNED DEFAULT NULL',
            'stop_reason' => 'stop_reason VARCHAR(32) DEFAULT NULL',
        );
        foreach ($token_columns as $column => $definition) {
            $has = $wpdb->get_var($wpdb->prepare(
                "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s AND COLUMN_NAME = %s",
                DB_NAME, $table, $column
            ));
            if (empty($has)) {
                
                $wpdb->query("ALTER TABLE {$table} ADD COLUMN {$definition}");
                if (defined('WP_DEBUG') && WP_DEBUG) { error_log('AIEO: added missing ' . $column . ' column to chat history table'); }
            }
        }
        $wpdb->show_errors();
    }

    
    public function create_tables() {
        global $wpdb;
        $charset_collate = aieo_get_charset_collate();

        
        $embeddings_exists = $wpdb->get_var("SHOW TABLES LIKE '{$this->embeddings_table}'") === $this->embeddings_table;

        if (!$embeddings_exists) {
            
            
            $embeddings_sql = "CREATE TABLE {$this->embeddings_table} (
                id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                post_id bigint(20) UNSIGNED NOT NULL,
                post_type varchar(50) NOT NULL DEFAULT 'product',
                engine_type varchar(50) NOT NULL DEFAULT 'voyage-2',
                deduplicated tinyint(1) NOT NULL DEFAULT 0,
                embedding LONGTEXT NOT NULL,
                content_hash varchar(32) NOT NULL,
                failure_count int(11) DEFAULT 0,
                last_failure_reason varchar(500) DEFAULT NULL,
                last_failure_date datetime DEFAULT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY post_id_type_engine_dedup (post_id, post_type, engine_type, deduplicated),
                KEY post_type (post_type),
                KEY engine_type (engine_type),
                KEY deduplicated (deduplicated),
                KEY created_at (created_at),
                KEY lookup_idx (post_id, engine_type, deduplicated),
                KEY stats_idx (engine_type, deduplicated, created_at),
                KEY failure_idx (failure_count, last_failure_date)
            ) $charset_collate;";

            
            $wpdb->query($embeddings_sql);
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO: Created embeddings table with full schema (fresh install)'); }
        } else {
            
            $this->add_performance_indexes();
            $this->add_failure_tracking_columns(true);
        }

        
        $history_exists = $wpdb->get_var("SHOW TABLES LIKE '{$this->chat_history_table}'") === $this->chat_history_table;

        if (!$history_exists) {
            
            $chat_history_sql = "CREATE TABLE {$this->chat_history_table} (
                id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                conversation_id varchar(64) NOT NULL,
                session_id varchar(64) NOT NULL,
                user_id bigint(20) UNSIGNED DEFAULT NULL,
                user_message TEXT NOT NULL,
                assistant_message TEXT NOT NULL,
                model_used varchar(100) DEFAULT 'claude-3-5-sonnet-20241022',
                embedder_used varchar(50) DEFAULT NULL,
                products_found TEXT DEFAULT NULL,
                potential_suggestions_found TEXT DEFAULT NULL,
                user_rating TINYINT UNSIGNED DEFAULT NULL COMMENT 'User feedback rating 0-100, only stored if changed from default 50',
                tokens_in INT UNSIGNED DEFAULT NULL,
                tokens_out INT UNSIGNED DEFAULT NULL,
                stop_reason VARCHAR(32) DEFAULT NULL,
                created_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY conversation_id (conversation_id),
                KEY session_id (session_id),
                KEY user_id (user_id),
                KEY created_at (created_at),
                KEY user_rating (user_rating)
            ) $charset_collate;";

            
            $wpdb->query($chat_history_sql);
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO: Created chat history table (fresh install)'); }
        }

        
        
        
        $gaps_exists = $wpdb->get_var("SHOW TABLES LIKE '{$this->content_gaps_table}'") === $this->content_gaps_table;
        if (!$gaps_exists) {
            $gaps_sql = "CREATE TABLE {$this->content_gaps_table} (
                id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
                gap_key varchar(191) NOT NULL,
                sample_question TEXT NOT NULL,
                occurrences int(11) NOT NULL DEFAULT 0,
                zero_product_turns int(11) NOT NULL DEFAULT 0,
                unhappy_turns int(11) NOT NULL DEFAULT 0,
                avg_rating float DEFAULT NULL,
                demand_weight float NOT NULL DEFAULT 0,
                status varchar(20) NOT NULL DEFAULT 'open',
                mapped_object_type varchar(20) DEFAULT NULL,
                mapped_object_id bigint(20) UNSIGNED DEFAULT NULL,
                first_seen datetime DEFAULT NULL,
                last_seen datetime DEFAULT NULL,
                updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY gap_key (gap_key),
                KEY demand_weight (demand_weight),
                KEY status (status),
                KEY last_seen (last_seen)
            ) $charset_collate;";

            
            $wpdb->query($gaps_sql);
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO: Created content-gaps signal table (fresh install)'); }
        }
    }

    
    private function add_performance_indexes() {
        global $wpdb;

        
        static $indexes_checked = false;
        if ($indexes_checked) {
            return; 
        }

        
        
        
        
        $wpdb->suppress_errors();

        $lookup_exists = $wpdb->query(
            "SELECT 1 FROM {$this->embeddings_table} USE INDEX (lookup_idx) LIMIT 0"
        );

        if ($lookup_exists === false) {
            
            $wpdb->query("ALTER TABLE {$this->embeddings_table} ADD INDEX lookup_idx (post_id, engine_type, deduplicated)");
        }

        
        $stats_exists = $wpdb->query(
            "SELECT 1 FROM {$this->embeddings_table} USE INDEX (stats_idx) LIMIT 0"
        );

        if ($stats_exists === false) {
            
            $wpdb->query("ALTER TABLE {$this->embeddings_table} ADD INDEX stats_idx (engine_type, deduplicated, created_at)");
        }

        
        $wpdb->show_errors();

        
        $indexes_checked = true;
    }

    
    private function add_failure_tracking_columns($force_check = false) {
        global $wpdb;

        
        
        static $columns_checked = false;
        if ($columns_checked && !$force_check) {
            return;
        }

        
        $wpdb->suppress_errors();
        $column_exists = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_SCHEMA = %s
                AND TABLE_NAME = %s
                AND COLUMN_NAME = 'failure_count'",
                DB_NAME,
                $this->embeddings_table
            )
        );

        if (empty($column_exists)) {
            
            
            $wpdb->query("ALTER TABLE {$this->embeddings_table}
                ADD COLUMN failure_count int(11) DEFAULT 0");
            $wpdb->query("ALTER TABLE {$this->embeddings_table}
                ADD COLUMN last_failure_reason varchar(500) DEFAULT NULL");
            $wpdb->query("ALTER TABLE {$this->embeddings_table}
                ADD COLUMN last_failure_date datetime DEFAULT NULL");

            
            $index_exists = $wpdb->query(
                "SELECT 1 FROM {$this->embeddings_table} USE INDEX (failure_idx) LIMIT 0"
            );
            if ($index_exists === false) {
                $wpdb->query("ALTER TABLE {$this->embeddings_table}
                    ADD INDEX failure_idx (failure_count, last_failure_date)");
            }

            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO: Added failure tracking columns to embeddings table'); }
        }

        $wpdb->show_errors();
        $columns_checked = true;
    }

    
    public function store_embedding($post_id, $embedding, $content_hash, $post_type = 'product', $engine_type = null, $deduplicated = 0) {
        global $wpdb;

        
        $failure_reason = null;

        if (empty($embedding) || !is_array($embedding)) {
            $failure_reason = "Empty or invalid embedding data";
        } elseif (true) {
            
            $expected_dims = array(
                'text-embedding-ada-002' => 1536,
                'text-embedding-3-small' => 1536,
                'text-embedding-3-large' => 3072,
                'voyage-2' => 1024,
                'voyage-3' => 1024,
                'voyage-3.5' => 1024,
                'voyage-3.5-lite' => 1024,
                'voyage-large-2' => 1536,
            );

            $embedding_count = count($embedding);
            $expected = isset($expected_dims[$engine_type]) ? $expected_dims[$engine_type] : null;

            if ($expected !== null && $embedding_count !== $expected) {
                $failure_reason = "Wrong dimensions: expected $expected, got $embedding_count";
            } else {
                
                $non_zero_count = 0;
                foreach ($embedding as $value) {
                    if (abs($value) > 0.0001) {
                        $non_zero_count++;
                        if ($non_zero_count > 10) break; 
                    }
                }

                if ($non_zero_count < 10) {
                    $failure_reason = "Nearly all-zero embedding (API failure)";
                } else {
                    
                    if ($engine_type === null) {
                        $engine_type = get_option('aieo_chat_embedding_model', 'voyage-2');
                    }

                    
                    $compressed = $this->compress_embedding($embedding);

                    
                    if (strlen($compressed) < 100) {
                        $failure_reason = "Compressed size too small: " . strlen($compressed) . " bytes";
                    }
                }
            }
        }

        
        if ($failure_reason !== null) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log("AIEO: Rejected embedding for post_id=$post_id, type=$post_type, engine=$engine_type - Reason: $failure_reason"); }
            $this->log_embedding_failure($post_id, $post_type, $engine_type, $deduplicated, $failure_reason);
            return false;
        }

        
        if ($engine_type === null) {
            $engine_type = get_option('aieo_chat_embedding_model', 'voyage-2');
        }

        
        
        $cache_suspended = false;
        if (function_exists('wp_suspend_cache_addition')) {
            $cache_suspended = true;
            wp_suspend_cache_addition(true);
        }

        
        
        
        
        $result = $wpdb->query($wpdb->prepare(
            "INSERT INTO {$this->embeddings_table}
            (post_id, post_type, engine_type, deduplicated, embedding, content_hash, failure_count, last_failure_reason, last_failure_date, created_at, updated_at)
            VALUES (%d, %s, %s, %d, %s, %s, 0, NULL, NULL, %s, %s)
            ON DUPLICATE KEY UPDATE
                embedding = VALUES(embedding),
                content_hash = VALUES(content_hash),
                failure_count = 0,
                last_failure_reason = NULL,
                last_failure_date = NULL,
                updated_at = VALUES(updated_at)",
            $post_id,
            $post_type,
            $engine_type,
            $deduplicated,
            $compressed,
            $content_hash,
            current_time('mysql', true),
            current_time('mysql', true)
        ));

        
        if ($cache_suspended) {
            wp_suspend_cache_addition(false);
        }

        
        
        
        
        

        return $result !== false;
    }

    
    private function log_embedding_failure($post_id, $post_type, $engine_type, $deduplicated, $failure_reason) {
        global $wpdb;

        
        $wpdb->query($wpdb->prepare(
            "INSERT INTO {$this->embeddings_table}
            (post_id, post_type, engine_type, deduplicated, embedding, content_hash, failure_count, last_failure_reason, last_failure_date, created_at, updated_at)
            VALUES (%d, %s, %s, %d, '', '', 1, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                failure_count = failure_count + 1,
                last_failure_reason = VALUES(last_failure_reason),
                last_failure_date = VALUES(last_failure_date),
                updated_at = VALUES(updated_at)",
            $post_id,
            $post_type,
            $engine_type,
            $deduplicated,
            $failure_reason,
            current_time('mysql', true),
            current_time('mysql', true),
            current_time('mysql', true)
        ));
    }

    
    public function get_failed_embeddings($min_failures = 1) {
        global $wpdb;

        return $wpdb->get_results($wpdb->prepare(
            "SELECT post_id, post_type, engine_type, deduplicated, failure_count, last_failure_reason, last_failure_date
            FROM {$this->embeddings_table}
            WHERE failure_count >= %d
            ORDER BY failure_count DESC, last_failure_date DESC
            LIMIT 1000",
            $min_failures
        ));
    }

    
    public function get_failure_statistics($post_type, $engine_type, $deduplicated = 0) {
        global $wpdb;

        $stats = $wpdb->get_row($wpdb->prepare(
            "SELECT
                COUNT(*) as total_failed,
                SUM(failure_count) as total_attempts,
                MAX(failure_count) as max_failures,
                MIN(last_failure_date) as first_failure,
                MAX(last_failure_date) as latest_failure
            FROM {$this->embeddings_table}
            WHERE post_type = %s
            AND engine_type = %s
            AND deduplicated = %d
            AND failure_count > 0",
            $post_type,
            $engine_type,
            $deduplicated
        ), ARRAY_A);

        
        $reasons = $wpdb->get_results($wpdb->prepare(
            "SELECT last_failure_reason, COUNT(*) as count
            FROM {$this->embeddings_table}
            WHERE post_type = %s
            AND engine_type = %s
            AND deduplicated = %d
            AND failure_count > 0
            GROUP BY last_failure_reason
            ORDER BY count DESC
            LIMIT 10",
            $post_type,
            $engine_type,
            $deduplicated
        ), ARRAY_A);

        return array(
            'total_failed' => (int) $stats['total_failed'],
            'total_attempts' => (int) $stats['total_attempts'],
            'max_failures' => (int) $stats['max_failures'],
            'first_failure' => $stats['first_failure'],
            'latest_failure' => $stats['latest_failure'],
            'failure_reasons' => $reasons
        );
    }

    
    private function compress_embedding($embedding) {
        
        $packed = '';
        foreach ($embedding as $value) {
            
            $packed .= pack('f', $value);
        }

        
        $compressed = gzcompress($packed, 9);

        
        return base64_encode($compressed);
    }

    
    public function decompress_embedding($compressed) {
        
        $decoded = base64_decode($compressed);

        
        $decompressed = gzuncompress($decoded);

        
        $unpacked = unpack('f*', $decompressed);

        return array_values($unpacked);
    }

    
    public function get_embedding($post_id) {
        global $wpdb;

        
        
        
        
        $row = $wpdb->get_row($wpdb->prepare(
            "SELECT embedding, content_hash FROM {$this->embeddings_table} WHERE post_id = %d AND post_type != 'user'",
            $post_id
        ));

        if (!$row) {
            return null;
        }

        return array(
            'embedding' => $this->decompress_embedding($row->embedding),
            'content_hash' => $row->content_hash
        );
    }

    
    public function get_embeddings_for_search($post_types = array('product'), $post_ids = null, $engine_type = null) {
        global $wpdb;

        
        if (empty($post_types) || !is_array($post_types)) {
            $post_types = array('product');
        }

        
        if ($engine_type === null) {
            $engine_type = get_option('aieo_chat_embedding_model', 'voyage-2');
        }

        $placeholders = implode(',', array_fill(0, count($post_types), '%s'));

        if ($post_ids && is_array($post_ids)) {
            $id_placeholders = implode(',', array_fill(0, count($post_ids), '%d'));
            $sql = $wpdb->prepare(
                "SELECT post_id, embedding FROM {$this->embeddings_table}
                WHERE post_type IN ($placeholders)
                AND post_id IN ($id_placeholders)
                AND (engine_type = %s OR engine_type IS NULL)",
                array_merge($post_types, $post_ids, array($engine_type))
            );
        } else {
            $sql = $wpdb->prepare(
                "SELECT post_id, embedding FROM {$this->embeddings_table}
                WHERE post_type IN ($placeholders)
                AND (engine_type = %s OR engine_type IS NULL)",
                array_merge($post_types, array($engine_type))
            );
        }

        
        $rows = $wpdb->get_results($sql);

        $embeddings = array();
        foreach ($rows as $row) {
            $embeddings[$row->post_id] = $this->decompress_embedding($row->embedding);
        }

        return $embeddings;
    }

    
    public function delete_embedding($post_id) {
        global $wpdb;

        
        
        $result = $wpdb->query($wpdb->prepare(
            "DELETE FROM {$this->embeddings_table} WHERE post_id = %d AND post_type != 'user'",
            $post_id
        ));

        return $result !== false;
    }

    
    public function delete_embeddings_by_type($post_type) {
        global $wpdb;

        return $wpdb->delete(
            $this->embeddings_table,
            array('post_type' => $post_type),
            array('%s')
        );
    }

    
    public function clear_all_embeddings() {
        global $wpdb;
        return $wpdb->query("TRUNCATE TABLE {$this->embeddings_table}") !== false;
    }

    
    public function get_stats() {
        global $wpdb;

        $stats = array(
            'total' => 0,
            'by_type' => array(),
            'recent_24h' => 0
        );

        
        $stats['total'] = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$this->embeddings_table}");

        
        
        $type_counts = $wpdb->get_results(
            "SELECT post_type, COUNT(*) as count FROM {$this->embeddings_table} GROUP BY post_type"
        );
        foreach ($type_counts as $row) {
            $stats['by_type'][$row->post_type] = (int) $row->count;
        }

        
        $stats['recent_24h'] = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->embeddings_table} WHERE created_at > %s",
            gmdate('Y-m-d H:i:s', strtotime('-24 hours'))
        ));

        return $stats;
    }

    
    public function save_chat_message($conversation_id, $user_message, $assistant_message, $model = 'claude-3-5-sonnet-20241022', $products = null, $embedder = null, $potential_suggestions = null, $tokens_in = null, $tokens_out = null, $stop_reason = null) {
        global $wpdb;

        
        
        if (get_option('aieo_chat_history_enabled', '1') !== '1') {
            return false;
        }

        
        
        self::ensure_chat_history_columns();

        $session_id = $this->get_session_id();
        $user_id = get_current_user_id();

        
        $data = array(
            'conversation_id' => $conversation_id,
            'session_id' => $session_id,
            'user_message' => $user_message,
            'assistant_message' => $assistant_message,
            'model_used' => $model,
            'embedder_used' => $embedder,
            'products_found' => $products ? json_encode($products, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
            'potential_suggestions_found' => $potential_suggestions ? json_encode($potential_suggestions, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES) : null,
            'created_at' => current_time('mysql', true)
        );

        $format = array('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');

        
        if ($user_id > 0) {
            $data['user_id'] = $user_id;
            $format[] = '%d';
        }

        
        
        
        if ($tokens_in !== null) {
            $data['tokens_in'] = (int) $tokens_in;
            $format[] = '%d';
        }
        if ($tokens_out !== null) {
            $data['tokens_out'] = (int) $tokens_out;
            $format[] = '%d';
        }
        if ($stop_reason !== null && $stop_reason !== '') {
            $data['stop_reason'] = substr((string) $stop_reason, 0, 32);
            $format[] = '%s';
        }

        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Saving chat history to: ' . $this->chat_history_table); }
        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO History enabled option: ' . get_option('aieo_chat_history_enabled', '1')); }

        
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$this->chat_history_table}'") === $this->chat_history_table;
        if (!$table_exists) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Chat history table does NOT exist! Creating it now...'); }
            $this->create_tables();
        }

        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Data being saved: ' . print_r($data, true)); }

        $result = $wpdb->insert(
            $this->chat_history_table,
            $data,
            $format
        );

        if ($result === false) {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Chat history save FAILED. Error: ' . $wpdb->last_error); }
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Last query: ' . $wpdb->last_query); }
            return false;
        } else {
            if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) { error_log('AIEO Chat history saved successfully. Insert ID: ' . $wpdb->insert_id); }
            return $wpdb->insert_id;
        }
    }

    
    private function get_session_id() {
        
        $session_tracking_enabled = get_option('aieo_session_tracking_enabled', '1') === '1';

        
        if ($session_tracking_enabled && isset($_COOKIE['aieo_session_id'])) {
            return sanitize_text_field(wp_unslash( $_COOKIE['aieo_session_id'] ));
        }

        
        if (session_id() === '') {
            session_start();
        }
        if (!isset($_SESSION['aieo_chat_session_id'])) {
            $_SESSION['aieo_chat_session_id'] = wp_generate_uuid4();
        }
        return sanitize_text_field($_SESSION['aieo_chat_session_id']);
    }

    
    public function get_chat_stats() {
        global $wpdb;

        $stats = array(
            'total_conversations' => 0,
            'total_messages' => 0,
            'avg_per_conversation' => 0,
            'last_30_days' => array(
                'conversations' => 0,
                'messages' => 0
            )
        );

        
        $stats['total_conversations'] = (int) $wpdb->get_var(
            "SELECT COUNT(DISTINCT conversation_id) FROM {$this->chat_history_table}"
        );

        
        $stats['total_messages'] = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$this->chat_history_table}"
        );

        
        if ($stats['total_conversations'] > 0) {
            $stats['avg_per_conversation'] = round($stats['total_messages'] / $stats['total_conversations'], 1);
        }

        
        $date_30_days_ago = gmdate('Y-m-d H:i:s', strtotime('-30 days'));

        $stats['last_30_days']['conversations'] = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(DISTINCT conversation_id) FROM {$this->chat_history_table} WHERE created_at > %s",
            $date_30_days_ago
        ));

        $stats['last_30_days']['messages'] = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$this->chat_history_table} WHERE created_at > %s",
            $date_30_days_ago
        ));

        return $stats;
    }

    
    public function get_conversation_messages($conversation_id, $limit = 6) {
        global $wpdb;
        $conversation_id = (string) $conversation_id;
        if ('' === $conversation_id) {
            return array();
        }
        
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT user_message, assistant_message, created_at
               FROM {$this->chat_history_table}
              WHERE conversation_id = %s
              ORDER BY id DESC
              LIMIT %d",
            $conversation_id,
            max(1, (int) $limit)
        ), ARRAY_A);
        if (!is_array($rows)) {
            return array();
        }
        return array_reverse($rows); 
    }

    
    public function get_recent_conversations($limit = 10) {
        global $wpdb;

        
        $conversations = $wpdb->get_results($wpdb->prepare(
            "SELECT
                conversation_id,
                user_id,
                COUNT(*) as message_count,
                MIN(created_at) as started_at,
                MAX(created_at) as last_message_at
            FROM {$this->chat_history_table}
            GROUP BY conversation_id, user_id
            ORDER BY last_message_at DESC
            LIMIT %d",
            $limit
        ));

        return $conversations;
    }

    
    
    public function filter_retention_days($days) {
        $opt = (int) get_option('aieo_chat_retention_days', 0);
        return $opt > 0 ? max(1, $opt) : $days;
    }

    public function cleanup_old_chats() {
        global $wpdb;

        $retention_days = apply_filters('aieo_chat_retention_days', 30);
        $cutoff_date = gmdate('Y-m-d H:i:s', strtotime("-{$retention_days} days"));

        $wpdb->query($wpdb->prepare(
            "DELETE FROM {$this->chat_history_table} WHERE created_at < %s",
            $cutoff_date
        ));
    }

    
    public function get_posts_without_embeddings($post_type = 'product', $limit = 100, $engine_type = null, $deduplicated = 0, $exclude = array()) {
        global $wpdb;

        
        
        $exclude     = array_values(array_filter(array_map('intval', (array) $exclude)));
        $exclude_sql = empty($exclude) ? '' : ' AND p.ID NOT IN (' . implode(',', $exclude) . ')';

        
        
        
        
        
        
        
        
        
        
        
        
        
        
        if ($post_type === 'product') {
            $vitals_helper = AI_eShop_Chat_Product_Vitals_Helper::get_instance();

            if ($vitals_helper->is_vitals_table_available()) {
                $prioritize_sales = get_option('aieo_chat_prioritize_by_sales', '0') === '1';

                $all_product_ids = $vitals_helper->get_sellable_unit_ids(array(
                    'prioritize_sales' => $prioritize_sales,
                    'in_stock_only'    => true,
                ));

                
                $total_units_in_vitals = $vitals_helper->count_sellable_units(array(
                    'in_stock_only' => true,
                ));
                $vitals_has_most_products = !empty($all_product_ids) && $total_units_in_vitals > 0;

                if ($vitals_has_most_products) {
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    if ($engine_type !== null) {
                        $embedded_ids = $wpdb->get_col($wpdb->prepare(
                            "SELECT post_id FROM {$this->embeddings_table}
                             WHERE engine_type = %s AND deduplicated = %d",
                            $engine_type,
                            $deduplicated
                        ));
                    } else {
                        
                        $embedded_ids = $wpdb->get_col(
                            "SELECT post_id FROM {$this->embeddings_table}"
                        );
                    }

                    $embedded_lookup = array_fill_keys(array_map('intval', $embedded_ids), true);

                    $post_ids_without_embeddings = array();
                    foreach ($all_product_ids as $product_id) {
                        if (isset($embedded_lookup[(int) $product_id])) {
                            continue; 
                        }
                        $post_ids_without_embeddings[] = $product_id;
                        if (count($post_ids_without_embeddings) >= $limit) {
                            break;
                        }
                    }

                    return $post_ids_without_embeddings;
                }
            }
        }

        
        
        
        
        
        
        $sellable_predicate = '';
        $type_clause = 'p.post_type = %s';
        $type_args = array($post_type);

        if ($post_type === 'product') {
            $type_clause = "p.post_type IN ('product', 'product_variation')";
            $type_args   = array();
            
            $sellable_predicate = "AND (
                p.post_type = 'product_variation'
                OR (p.post_type = 'product' AND NOT EXISTS (
                    SELECT 1 FROM {$wpdb->posts} c
                    WHERE c.post_parent = p.ID
                      AND c.post_type = 'product_variation'
                      AND c.post_status = 'publish'
                ))
            )";
        }

        if ($engine_type !== null) {
            
            $sql = $wpdb->prepare(
                "SELECT DISTINCT p.ID
                 FROM {$wpdb->posts} p
                 LEFT JOIN {$this->embeddings_table} e
                     ON p.ID = e.post_id
                     AND e.engine_type = %s
                     AND e.deduplicated = %d
                 WHERE {$type_clause}
                 AND p.post_status = 'publish'
                 {$sellable_predicate}
                 {$exclude_sql}
                 AND e.id IS NULL
                 LIMIT %d",
                array_merge(array($engine_type, $deduplicated), $type_args, array($limit))
            );
        } else {
            
            $sql = $wpdb->prepare(
                "SELECT DISTINCT p.ID
                 FROM {$wpdb->posts} p
                 LEFT JOIN {$this->embeddings_table} e ON p.ID = e.post_id
                 WHERE {$type_clause}
                 AND p.post_status = 'publish'
                 {$sellable_predicate}
                 {$exclude_sql}
                 AND e.id IS NULL
                 LIMIT %d",
                array_merge($type_args, array($limit))
            );
        }

        
        $results = $wpdb->get_col($sql);

        return array_map('intval', $results);
    }

    
    public function count_posts_without_embeddings($post_type = 'product', $engine_type = null, $deduplicated = 0, $exclude = array()) {
        global $wpdb;

        
        $exclude     = array_values(array_filter(array_map('intval', (array) $exclude)));
        $exclude_sql = empty($exclude) ? '' : ' AND p.ID NOT IN (' . implode(',', $exclude) . ')';

        
        
        
        if ($post_type === 'product') {
            $vitals_helper = AI_eShop_Chat_Product_Vitals_Helper::get_instance();

            if ($vitals_helper->is_vitals_table_available()) {
                $vitals_table = $wpdb->prefix . 'aieo_core_product_vitals';

                
                
                
                
                if ($engine_type !== null) {
                    
                    return (int) $wpdb->get_var($wpdb->prepare(
                        "SELECT COUNT(DISTINCT v.product_id)
                         FROM {$vitals_table} v
                         INNER JOIN {$wpdb->posts} p ON v.product_id = p.ID
                         LEFT JOIN (
                             SELECT DISTINCT parent_id
                             FROM {$vitals_table}
                             WHERE product_id <> parent_id
                         ) vp ON vp.parent_id = v.product_id
                         LEFT JOIN {$this->embeddings_table} e
                             ON e.post_id = v.product_id
                             AND e.engine_type = %s
                             AND e.deduplicated = %d
                         WHERE p.post_status = 'publish'
                           AND p.post_type IN ('product', 'product_variation')
                           AND v.stock > 0
                           AND (v.product_id <> v.parent_id OR vp.parent_id IS NULL)
                           AND e.id IS NULL",
                        $engine_type,
                        $deduplicated
                    ));
                }

                
                
                return (int) $wpdb->get_var(
                    "SELECT COUNT(DISTINCT v.product_id)
                     FROM {$vitals_table} v
                     INNER JOIN {$wpdb->posts} p ON v.product_id = p.ID
                     LEFT JOIN (
                         SELECT DISTINCT parent_id
                         FROM {$vitals_table}
                         WHERE product_id <> parent_id
                     ) vp ON vp.parent_id = v.product_id
                     LEFT JOIN {$this->embeddings_table} e ON e.post_id = v.product_id
                     WHERE p.post_status = 'publish'
                       AND p.post_type IN ('product', 'product_variation')
                       AND v.stock > 0
                       AND (v.product_id <> v.parent_id OR vp.parent_id IS NULL)
                       AND e.id IS NULL"
                );
            }
        }

        
        if ($engine_type !== null) {
            $sql = $wpdb->prepare(
                "SELECT COUNT(p.ID)
                 FROM {$wpdb->posts} p
                 LEFT JOIN {$this->embeddings_table} e
                     ON p.ID = e.post_id
                     AND e.post_type = %s
                     AND e.engine_type = %s
                     AND e.deduplicated = %d
                 WHERE p.post_type = %s
                 AND p.post_status = 'publish'
                 {$exclude_sql}
                 AND e.id IS NULL",
                $post_type,
                $engine_type,
                $deduplicated,
                $post_type
            );
        } else {
            $sql = $wpdb->prepare(
                "SELECT COUNT(p.ID)
                 FROM {$wpdb->posts} p
                 LEFT JOIN {$this->embeddings_table} e ON p.ID = e.post_id AND e.post_type = %s
                 WHERE p.post_type = %s
                 AND p.post_status = 'publish'
                 {$exclude_sql}
                 AND e.id IS NULL",
                $post_type,
                $post_type
            );
        }

        
        return (int) $wpdb->get_var($sql);
    }

    
    public function get_terms_without_embeddings($taxonomy, $limit = 100, $engine_type = null, $deduplicated = 0) {
        global $wpdb;

        $post_type_key = 'taxonomy:' . $taxonomy;

        if ($engine_type !== null) {
            $sql = $wpdb->prepare(
                "SELECT DISTINCT t.term_id
                FROM {$wpdb->terms} t
                INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
                LEFT JOIN {$this->embeddings_table} e
                    ON t.term_id = e.post_id
                    AND e.post_type = %s
                    AND e.engine_type = %s
                    AND e.deduplicated = %d
                WHERE tt.taxonomy = %s
                AND tt.count > 0
                AND e.id IS NULL
                LIMIT %d",
                $post_type_key,
                $engine_type,
                $deduplicated,
                $taxonomy,
                $limit
            );
        } else {
            $sql = $wpdb->prepare(
                "SELECT DISTINCT t.term_id
                FROM {$wpdb->terms} t
                INNER JOIN {$wpdb->term_taxonomy} tt ON t.term_id = tt.term_id
                LEFT JOIN {$this->embeddings_table} e ON t.term_id = e.post_id AND e.post_type = %s
                WHERE tt.taxonomy = %s
                AND tt.count > 0
                AND e.id IS NULL
                LIMIT %d",
                $post_type_key,
                $taxonomy,
                $limit
            );
        }

        
        $results = $wpdb->get_col($sql);

        return array_map('intval', $results);
    }
}