<?php

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

class AIEO_Session_Schema_Migrator {

    
    private static $instance = null;

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

    
    private function __construct() {
        
        add_action('admin_menu', array($this, 'add_admin_menu'), 70);
        add_action('admin_init', array($this, 'register_settings'));
        add_action('wp_ajax_aieo_run_migration', array($this, 'ajax_run_migration'));
        add_action('wp_ajax_aieo_check_migration_status', array($this, 'ajax_check_status'));
    }

    
    private function get_database_version() {
        global $wpdb;

        
        $version = $wpdb->get_var("SELECT VERSION()");
        $is_mariadb = stripos($version, 'mariadb') !== false;

        
        preg_match('/(\d+\.\d+\.\d+)/', $version, $matches);
        $version_number = isset($matches[1]) ? $matches[1] : '0.0.0';

        return array(
            'full_version' => $version,
            'is_mariadb' => $is_mariadb,
            'version_number' => $version_number
        );
    }

    
    private function column_exists($table, $column) {
        global $wpdb;

        
        $result = $wpdb->get_results("SHOW COLUMNS FROM `{$table}` LIKE '{$column}'");
        return !empty($result);
    }

    
    private function index_exists($table, $index_name) {
        global $wpdb;

        
        $result = $wpdb->get_results("SHOW INDEX FROM `{$table}` WHERE Key_name = '{$index_name}'");
        return !empty($result);
    }

    
    public function add_admin_menu() {
        add_submenu_page(
            'ai-eshop-optimizer',                                    
            __('Session Schema Migration', 'ai-eshop-optimizer'),    
            __('🔧 Migrator', 'ai-eshop-optimizer'),                 
            'manage_options',                                        
            'aieo-session-migration',                                
            array($this, 'render_migration_page')                   
        );
    }

    
    public function register_settings() {
        register_setting(
            'aieo_migration',
            'aieo_migration_status',
            array(
                'type' => 'array',
                'sanitize_callback' => function($value) {
                    if (!is_array($value)) {
                        return array();
                    }
                    return array_map('sanitize_text_field', $value);
                },
            )
        );
    }

    
    public function check_migration_status() {
        global $wpdb;

        $status = array(
            'sessions_table_exists' => false,
            'statics_table_exists' => false,
            'has_product_id_column' => false,
            'has_scroll_depth_column' => false,
            'has_attention_analytics_columns' => false,
            'has_statics_summary_columns' => false,
            'embeddings_table_exists' => false,
            'has_embeddings_failure_columns' => false,
            'total_events' => 0,
            'events_with_product_id' => 0,
            'events_needing_backfill' => 0,
            'migration_needed' => false,
            'migration_recommended' => false,
        );

        
        $sessions_table = $wpdb->prefix . 'aieo_sessions';
        
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$sessions_table}'");
        $status['sessions_table_exists'] = ($table_exists === $sessions_table);

        if (!$status['sessions_table_exists']) {
            $status['migration_needed'] = false;
            return $status;
        }

        
        $statics_table = $wpdb->prefix . 'aieo_sessions_statics';
        
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$statics_table}'");
        $status['statics_table_exists'] = ($table_exists === $statics_table);

        
        
        $column = $wpdb->get_results("SHOW COLUMNS FROM {$sessions_table} LIKE 'product_id'");
        $status['has_product_id_column'] = !empty($column);

        
        
        $column = $wpdb->get_results("SHOW COLUMNS FROM {$sessions_table} LIKE 'scroll_depth'");
        $status['has_scroll_depth_column'] = !empty($column);

        
        $attention_columns = array('event_sequence', 'is_first_event', 'time_on_page', 'visit_number', 'order_id', 'checkout_step');
        $has_all_attention_columns = true;
        foreach ($attention_columns as $col) {
            
            $column = $wpdb->get_results("SHOW COLUMNS FROM {$sessions_table} LIKE '{$col}'");
            if (empty($column)) {
                $has_all_attention_columns = false;
                break;
            }
        }
        $status['has_attention_analytics_columns'] = $has_all_attention_columns;

        
        if ($status['statics_table_exists']) {
            
            $column = $wpdb->get_results("SHOW COLUMNS FROM {$statics_table} LIKE 'total_events'");
            $status['has_statics_summary_columns'] = !empty($column);
        }

        
        
        $status['total_events'] = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$sessions_table}");

        if ($status['has_product_id_column']) {
            
            $status['events_with_product_id'] = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$sessions_table} WHERE product_id IS NOT NULL"
            );

            
            
            $status['events_needing_backfill'] = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$sessions_table}
                 WHERE event_type IN ('product_view', 'add_to_cart', 'remove_from_cart')
                 AND product_id IS NULL
                 AND event_data IS NOT NULL"
            );
        }

        
        $embeddings_table = $wpdb->prefix . 'aieo_chat_embeddings';
        
        $table_exists = $wpdb->get_var("SHOW TABLES LIKE '{$embeddings_table}'");
        $status['embeddings_table_exists'] = ($table_exists === $embeddings_table);

        if ($status['embeddings_table_exists']) {
            
            $has_failure_count = $this->column_exists($embeddings_table, 'failure_count');
            $has_failure_reason = $this->column_exists($embeddings_table, 'last_failure_reason');
            $has_failure_date = $this->column_exists($embeddings_table, 'last_failure_date');
            $status['has_embeddings_failure_columns'] = $has_failure_count && $has_failure_reason && $has_failure_date;
        }

        
        $chat_history_table = $wpdb->prefix . 'aieo_chat_history';
        
        $chat_history_exists = $wpdb->get_var("SHOW TABLES LIKE '{$chat_history_table}'");
        $status['chat_history_table_exists'] = ($chat_history_exists === $chat_history_table);

        if ($status['chat_history_table_exists']) {
            
            $status['has_user_rating_column'] = $this->column_exists($chat_history_table, 'user_rating');
        } else {
            $status['has_user_rating_column'] = true; 
        }

        
        $status['migration_needed'] = !$status['has_product_id_column']
                                    || !$status['has_scroll_depth_column']
                                    || !$status['has_attention_analytics_columns']
                                    || ($status['embeddings_table_exists'] && !$status['has_embeddings_failure_columns'])
                                    || ($status['chat_history_table_exists'] && !$status['has_user_rating_column']);
        $status['migration_recommended'] = $status['events_needing_backfill'] > 0 || !$status['has_statics_summary_columns'];

        return $status;
    }

    
    public function add_columns() {
        global $wpdb;
        $sessions_table = $wpdb->prefix . 'aieo_sessions';
        $statics_table = $wpdb->prefix . 'aieo_sessions_statics';

        $results = array(
            'success' => true,
            'messages' => array(),
            'errors' => array()
        );

        
        $db_info = $this->get_database_version();
        $results['messages'][] = sprintf(
            'Database: %s %s',
            $db_info['is_mariadb'] ? 'MariaDB' : 'MySQL',
            $db_info['version_number']
        );

        
        $wpdb->show_errors();
        $wpdb->suppress_errors(false);

        
        $columns_to_add = array(
            'product_id' => array(
                'definition' => "product_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'Extracted from event_data'",
                'position' => 'AFTER event_type'
            ),
            'product_name' => array(
                'definition' => "product_name VARCHAR(512) DEFAULT NULL",
                'position' => 'AFTER product_id'
            ),
            'category_id' => array(
                'definition' => "category_id BIGINT(20) UNSIGNED DEFAULT NULL",
                'position' => 'AFTER product_name'
            ),
            'category_name' => array(
                'definition' => "category_name VARCHAR(512) DEFAULT NULL COMMENT 'Decoded category name'",
                'position' => 'AFTER category_id'
            ),
            'scroll_depth' => array(
                'definition' => "scroll_depth TINYINT(3) UNSIGNED DEFAULT NULL COMMENT '0-100%'",
                'position' => 'AFTER category_name'
            ),
            'click_count' => array(
                'definition' => "click_count SMALLINT(5) UNSIGNED DEFAULT NULL",
                'position' => 'AFTER scroll_depth'
            ),
            
            'event_sequence' => array(
                'definition' => "event_sequence INT(10) UNSIGNED DEFAULT NULL COMMENT 'Position in session (1st, 2nd, 3rd event)'",
                'position' => 'AFTER click_count'
            ),
            'is_first_event' => array(
                'definition' => "is_first_event TINYINT(1) DEFAULT 0 COMMENT '1 if landing page/product'",
                'position' => 'AFTER event_sequence'
            ),
            'is_last_event' => array(
                'definition' => "is_last_event TINYINT(1) DEFAULT 0 COMMENT '1 if exit page/product'",
                'position' => 'AFTER is_first_event'
            ),
            'is_only_event' => array(
                'definition' => "is_only_event TINYINT(1) DEFAULT 0 COMMENT '1 if bounce (first AND last)'",
                'position' => 'AFTER is_last_event'
            ),
            'time_on_page' => array(
                'definition' => "time_on_page INT(10) UNSIGNED DEFAULT NULL COMMENT 'Seconds spent on this page/product'",
                'position' => 'AFTER is_only_event'
            ),
            'visit_number' => array(
                'definition' => "visit_number SMALLINT(5) UNSIGNED DEFAULT NULL COMMENT 'Nth visit to this product in session (hesitation)'",
                'position' => 'AFTER time_on_page'
            ),
            'order_id' => array(
                'definition' => "order_id BIGINT(20) UNSIGNED DEFAULT NULL COMMENT 'WooCommerce order ID if session led to purchase'",
                'position' => 'AFTER visit_number'
            ),
            'checkout_step' => array(
                'definition' => "checkout_step VARCHAR(50) DEFAULT NULL COMMENT 'cart, billing, shipping, payment, order_received'",
                'position' => 'AFTER order_id'
            )
        );

        
        foreach ($columns_to_add as $column_name => $column_info) {
            if ($this->column_exists($sessions_table, $column_name)) {
                $results['messages'][] = "Column '{$column_name}' already exists (skipped)";
                continue;
            }

            $sql = "ALTER TABLE `{$sessions_table}` ADD COLUMN {$column_info['definition']} {$column_info['position']}";
            
            $result = $wpdb->query($sql);

            if ($result === false) {
                $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                $results['errors'][] = "Failed to add column '{$column_name}': {$error_msg}";
                $results['success'] = false;
            } else {
                $results['messages'][] = "✓ Column '{$column_name}' added successfully";
            }
        }

        
        
        
        
        
        
        $indexes_to_add = array(
            'idx_product_event' => "(product_id, event_type, timestamp)",
            'idx_category_id' => "(category_id)",
            'idx_session_product' => "(session_id, product_id, event_type)",
            
            'idx_sequence' => "(session_id, event_sequence)",
            'idx_first_page' => "(product_id, is_first_event)",
            'idx_last_page' => "(product_id, is_last_event)",
            'idx_bounce' => "(product_id, is_only_event)",
            'idx_order_id' => "(order_id)",
            'idx_checkout_step' => "(checkout_step)",
            'idx_hesitation' => "(product_id, visit_number)"
        );

        
        foreach ($indexes_to_add as $index_name => $index_columns) {
            if ($this->index_exists($sessions_table, $index_name)) {
                $results['messages'][] = "Index '{$index_name}' already exists (skipped)";
                continue;
            }

            $sql = "ALTER TABLE `{$sessions_table}` ADD INDEX `{$index_name}` {$index_columns}";
            
            $result = $wpdb->query($sql);

            if ($result === false) {
                $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                
                if (strpos($error_msg, 'Duplicate key') === false && strpos($error_msg, 'duplicate') === false) {
                    $results['errors'][] = "Failed to add index '{$index_name}': {$error_msg}";
                } else {
                    $results['messages'][] = "Index '{$index_name}' already exists (duplicate key, skipped)";
                }
            } else {
                $results['messages'][] = "✓ Index '{$index_name}' added successfully";
            }
        }

        
        $archive_table = $wpdb->prefix . 'aieo_sessions_archive';
        
        if ($wpdb->get_var("SHOW TABLES LIKE '{$archive_table}'") == $archive_table) {
            foreach ($columns_to_add as $column_name => $column_info) {
                if ($this->column_exists($archive_table, $column_name)) {
                    $results['messages'][] = "Archive column '{$column_name}' already exists (skipped)";
                    continue;
                }

                $sql = "ALTER TABLE `{$archive_table}` ADD COLUMN {$column_info['definition']} {$column_info['position']}";
                
                $result = $wpdb->query($sql);

                if ($result === false) {
                    $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                    $results['errors'][] = "Failed to add archive column '{$column_name}': {$error_msg}";
                } else {
                    $results['messages'][] = "✓ Archive column '{$column_name}' added successfully";
                }
            }

            foreach ($indexes_to_add as $index_name => $index_columns) {
                if ($this->index_exists($archive_table, $index_name)) {
                    $results['messages'][] = "Archive index '{$index_name}' already exists (skipped)";
                    continue;
                }

                $sql = "ALTER TABLE `{$archive_table}` ADD INDEX `{$index_name}` {$index_columns}";
                
                $result = $wpdb->query($sql);

                if ($result === false) {
                    $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                    if (strpos($error_msg, 'Duplicate key') === false && strpos($error_msg, 'duplicate') === false) {
                        $results['errors'][] = "Failed to add archive index '{$index_name}': {$error_msg}";
                    } else {
                        $results['messages'][] = "Archive index '{$index_name}' already exists (duplicate key, skipped)";
                    }
                } else {
                    $results['messages'][] = "✓ Archive index '{$index_name}' added successfully";
                }
            }
        } else {
            $results['messages'][] = "Archive table does not exist yet (will be created with correct schema)";
        }

        
        $statics_columns = array(
            'last_timestamp' => array(
                'definition' => "last_timestamp BIGINT(20) UNSIGNED DEFAULT NULL",
                'position' => 'AFTER first_timestamp'
            ),
            'total_events' => array(
                'definition' => "total_events INT(10) UNSIGNED DEFAULT 0",
                'position' => 'AFTER last_timestamp'
            ),
            'total_product_views' => array(
                'definition' => "total_product_views INT(10) UNSIGNED DEFAULT 0",
                'position' => 'AFTER total_events'
            ),
            'total_add_to_cart' => array(
                'definition' => "total_add_to_cart INT(10) UNSIGNED DEFAULT 0",
                'position' => 'AFTER total_product_views'
            ),
            'total_scroll_events' => array(
                'definition' => "total_scroll_events INT(10) UNSIGNED DEFAULT 0",
                'position' => 'AFTER total_add_to_cart'
            ),
            'total_click_events' => array(
                'definition' => "total_click_events INT(10) UNSIGNED DEFAULT 0",
                'position' => 'AFTER total_scroll_events'
            ),
            'session_duration_seconds' => array(
                'definition' => "session_duration_seconds INT(10) UNSIGNED DEFAULT 0",
                'position' => 'AFTER total_click_events'
            ),
            'updated_at' => array(
                'definition' => "updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
                'position' => 'AFTER created_at'
            )
        );

        
        if ($this->check_migration_status()['statics_table_exists']) {
            foreach ($statics_columns as $column_name => $column_info) {
                if ($this->column_exists($statics_table, $column_name)) {
                    $results['messages'][] = "Statics column '{$column_name}' already exists (skipped)";
                    continue;
                }

                $sql = "ALTER TABLE `{$statics_table}` ADD COLUMN {$column_info['definition']} {$column_info['position']}";
                
                $result = $wpdb->query($sql);

                if ($result === false) {
                    $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                    $results['errors'][] = "Failed to add statics column '{$column_name}': {$error_msg}";
                } else {
                    $results['messages'][] = "✓ Statics column '{$column_name}' added successfully";
                }
            }

            
            if (!$this->index_exists($statics_table, 'idx_updated_at')) {
                $sql = "ALTER TABLE `{$statics_table}` ADD INDEX `idx_updated_at` (updated_at)";
                
                $wpdb->query($sql);
                $results['messages'][] = "✓ Statics index 'idx_updated_at' added successfully";
            }
        }

        
        $embeddings_table = $wpdb->prefix . 'aieo_chat_embeddings';
        
        if ($wpdb->get_var("SHOW TABLES LIKE '{$embeddings_table}'") == $embeddings_table) {
            $results['messages'][] = "Checking chat embeddings table...";

            $embeddings_columns = array(
                'failure_count' => array(
                    'definition' => "failure_count INT(11) DEFAULT 0 COMMENT 'Number of failed embedding generation attempts'",
                    'position' => 'AFTER content_hash'
                ),
                'last_failure_reason' => array(
                    'definition' => "last_failure_reason VARCHAR(500) DEFAULT NULL COMMENT 'Reason for last failure'",
                    'position' => 'AFTER failure_count'
                ),
                'last_failure_date' => array(
                    'definition' => "last_failure_date DATETIME DEFAULT NULL COMMENT 'Timestamp of last failure'",
                    'position' => 'AFTER last_failure_reason'
                )
            );

            foreach ($embeddings_columns as $column_name => $column_info) {
                if ($this->column_exists($embeddings_table, $column_name)) {
                    $results['messages'][] = "Embeddings column '{$column_name}' already exists (skipped)";
                    continue;
                }

                $sql = "ALTER TABLE `{$embeddings_table}` ADD COLUMN {$column_info['definition']} {$column_info['position']}";
                
                $result = $wpdb->query($sql);

                if ($result === false) {
                    $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                    $results['errors'][] = "Failed to add embeddings column '{$column_name}': {$error_msg}";
                } else {
                    $results['messages'][] = "✓ Embeddings column '{$column_name}' added successfully";
                }
            }

            
            if (!$this->index_exists($embeddings_table, 'failure_idx')) {
                $sql = "ALTER TABLE `{$embeddings_table}` ADD INDEX `failure_idx` (failure_count, last_failure_date)";
                
                $result = $wpdb->query($sql);

                if ($result === false) {
                    $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                    if (strpos($error_msg, 'Duplicate key') === false && strpos($error_msg, 'duplicate') === false) {
                        $results['errors'][] = "Failed to add embeddings index 'failure_idx': {$error_msg}";
                    } else {
                        $results['messages'][] = "Embeddings index 'failure_idx' already exists (duplicate key, skipped)";
                    }
                } else {
                    $results['messages'][] = "✓ Embeddings index 'failure_idx' added successfully";
                }
            } else {
                $results['messages'][] = "Embeddings index 'failure_idx' already exists (skipped)";
            }
        } else {
            $results['messages'][] = "Chat embeddings table does not exist yet (will be created with correct schema)";
        }

        
        $chat_history_table = $wpdb->prefix . 'aieo_chat_history';
        
        if ($wpdb->get_var("SHOW TABLES LIKE '{$chat_history_table}'") == $chat_history_table) {
            $results['messages'][] = "Checking chat history table...";

            
            if (!$this->column_exists($chat_history_table, 'user_rating')) {
                $sql = "ALTER TABLE `{$chat_history_table}` ADD COLUMN user_rating TINYINT UNSIGNED DEFAULT NULL COMMENT 'User feedback rating 0-100, only stored if changed from default 50' AFTER potential_suggestions_found";
                
                $result = $wpdb->query($sql);

                if ($result === false) {
                    $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                    $results['errors'][] = "Failed to add chat history column 'user_rating': {$error_msg}";
                } else {
                    $results['messages'][] = "✓ Chat history column 'user_rating' added successfully";
                }

                
                if (!$this->index_exists($chat_history_table, 'user_rating')) {
                    $sql = "ALTER TABLE `{$chat_history_table}` ADD INDEX `user_rating` (user_rating)";
                    
                    $result = $wpdb->query($sql);

                    if ($result === false) {
                        $error_msg = $wpdb->last_error ? $wpdb->last_error : 'Unknown database error';
                        if (strpos($error_msg, 'Duplicate key') === false && strpos($error_msg, 'duplicate') === false) {
                            $results['errors'][] = "Failed to add chat history index 'user_rating': {$error_msg}";
                        }
                    } else {
                        $results['messages'][] = "✓ Chat history index 'user_rating' added successfully";
                    }
                }
            } else {
                $results['messages'][] = "Chat history column 'user_rating' already exists (skipped)";
            }
        } else {
            $results['messages'][] = "Chat history table does not exist yet (will be created with correct schema)";
        }

        return $results;
    }

    
    public function backfill_data($batch_size = 10000, $offset = 0) {
        global $wpdb;
        $sessions_table = $wpdb->prefix . 'aieo_sessions';

        
        $wpdb->show_errors();
        $wpdb->suppress_errors(false);

        
        
        $result = $wpdb->query($wpdb->prepare(
            "UPDATE {$sessions_table}
             SET product_id = CAST(JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.product_id')) AS UNSIGNED),
                 product_name = JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.product_name'))
             WHERE id IN (
                 SELECT id FROM (
                     SELECT id FROM {$sessions_table}
                     WHERE event_type IN ('product_view', 'add_to_cart', 'remove_from_cart', 'add_to_wishlist')
                     AND product_id IS NULL
                     AND event_data IS NOT NULL
                     AND JSON_EXTRACT(event_data, '$.product_id') IS NOT NULL
                     LIMIT %d
                 ) AS batch
             )",
            $batch_size
        ));

        if ($result === false) {
            return array(
                'success' => false,
                'error' => $wpdb->last_error ? $wpdb->last_error : 'Failed to backfill product data',
                'rows_updated' => 0
            );
        }

        $product_rows = $wpdb->rows_affected;

        
        $wpdb->query($wpdb->prepare(
            "UPDATE {$sessions_table}
             SET category_id = CAST(JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.category_id')) AS UNSIGNED),
                 category_name = JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.category_name'))
             WHERE id IN (
                 SELECT id FROM (
                     SELECT id FROM {$sessions_table}
                     WHERE event_type = 'category_view'
                     AND category_id IS NULL
                     AND event_data IS NOT NULL
                     AND JSON_EXTRACT(event_data, '$.category_id') IS NOT NULL
                     LIMIT %d
                 ) AS batch
             )",
            $batch_size
        ));

        $category_rows = $wpdb->rows_affected;

        
        $result = $wpdb->query($wpdb->prepare(
            "UPDATE {$sessions_table}
             SET scroll_depth = CAST(JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.scroll_depth')) AS UNSIGNED)
             WHERE id IN (
                 SELECT id FROM (
                     SELECT id FROM {$sessions_table}
                     WHERE event_type = 'scroll_depth'
                     AND scroll_depth IS NULL
                     AND event_data IS NOT NULL
                     AND JSON_EXTRACT(event_data, '$.scroll_depth') IS NOT NULL
                     LIMIT %d
                 ) AS batch
             )",
            $batch_size
        ));

        $scroll_rows = $wpdb->rows_affected;

        
        $wpdb->query($wpdb->prepare(
            "UPDATE {$sessions_table}
             SET click_count = CAST(JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.click_count')) AS UNSIGNED)
             WHERE id IN (
                 SELECT id FROM (
                     SELECT id FROM {$sessions_table}
                     WHERE event_type = 'click'
                     AND click_count IS NULL
                     AND event_data IS NOT NULL
                     AND JSON_EXTRACT(event_data, '$.click_count') IS NOT NULL
                     LIMIT %d
                 ) AS batch
             )",
            $batch_size
        ));

        $click_rows = $wpdb->rows_affected;

        
        
        $remaining = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$sessions_table}
             WHERE event_data IS NOT NULL
             AND (
                 (event_type IN ('product_view', 'add_to_cart', 'remove_from_cart', 'add_to_wishlist')
                  AND product_id IS NULL
                  AND JSON_EXTRACT(event_data, '$.product_id') IS NOT NULL)
                 OR
                 (event_type = 'category_view'
                  AND category_id IS NULL
                  AND JSON_EXTRACT(event_data, '$.category_id') IS NOT NULL)
                 OR
                 (event_type = 'scroll_depth'
                  AND scroll_depth IS NULL
                  AND JSON_EXTRACT(event_data, '$.scroll_depth') IS NOT NULL)
                 OR
                 (event_type = 'click'
                  AND click_count IS NULL
                  AND JSON_EXTRACT(event_data, '$.click_count') IS NOT NULL)
             )"
        );

        return array(
            'success' => true,
            'rows_updated' => $product_rows + $category_rows + $scroll_rows + $click_rows,
            'product_rows' => $product_rows,
            'category_rows' => $category_rows,
            'scroll_rows' => $scroll_rows,
            'click_rows' => $click_rows,
            'remaining' => $remaining,
            'complete' => $remaining == 0
        );
    }

    
    public function update_statics_summary() {
        global $wpdb;
        $sessions_table = $wpdb->prefix . 'aieo_sessions';
        $statics_table = $wpdb->prefix . 'aieo_sessions_statics';

        $wpdb->query(
            "UPDATE {$statics_table} ss
             JOIN (
                 SELECT
                     session_id,
                     MAX(timestamp) AS last_timestamp,
                     COUNT(*) AS total_events,
                     SUM(CASE WHEN event_type = 'product_view' THEN 1 ELSE 0 END) AS total_product_views,
                     SUM(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS total_add_to_cart,
                     SUM(CASE WHEN event_type = 'scroll_depth' THEN 1 ELSE 0 END) AS total_scroll_events,
                     SUM(CASE WHEN event_type = 'click' THEN 1 ELSE 0 END) AS total_click_events,
                     CAST((MAX(timestamp) - MIN(timestamp)) / 1000 AS UNSIGNED) AS session_duration_seconds
                 FROM {$sessions_table}
                 GROUP BY session_id
             ) s ON s.session_id = ss.session_id
             SET
                 ss.last_timestamp = s.last_timestamp,
                 ss.total_events = s.total_events,
                 ss.total_product_views = s.total_product_views,
                 ss.total_add_to_cart = s.total_add_to_cart,
                 ss.total_scroll_events = s.total_scroll_events,
                 ss.total_click_events = s.total_click_events,
                 ss.session_duration_seconds = s.session_duration_seconds,
                 ss.updated_at = UTC_TIMESTAMP()"
        );

        $rows_updated = $wpdb->rows_affected;

        return array(
            'success' => true,
            'rows_updated' => $rows_updated,
            
            'message' => sprintf(__('Updated %d session summaries', 'ai-eshop-optimizer'), $rows_updated)
        );
    }

    
    public function ajax_run_migration() {
        check_ajax_referer('aieo_migration_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        $step = sanitize_text_field(wp_unslash( $_POST['step'] )); 
        $offset = isset($_POST['offset']) ? (int) $_POST['offset'] : 0;

        switch ($step) {
            case 'add_columns':
                $result = $this->add_columns();
                wp_send_json_success($result);
                break;

            case 'backfill_data':
                $result = $this->backfill_data(10000, $offset);
                wp_send_json_success($result);
                break;

            case 'update_summary':
                $result = $this->update_statics_summary();
                wp_send_json_success($result);
                break;

            default:
                wp_send_json_error(array('message' => 'Unknown step'));
        }
    }

    
    public function ajax_check_status() {
        check_ajax_referer('aieo_migration_nonce', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(array('message' => 'Unauthorized'));
        }

        $status = $this->check_migration_status();
        wp_send_json_success($status);
    }

    
    public function render_migration_page() {
        $status = $this->check_migration_status();
        $nonce = wp_create_nonce('aieo_migration_nonce');

        include plugin_dir_path(__FILE__) . '../assets/pages/session-migration-page.php';
    }
}

AIEO_Session_Schema_Migrator::get_instance();
