<?php
/**
 * Database operations for BotFend Anti-Bot Firewall - SMART AGGREGATION VERSION
 *
 * @package BotFend\Database
 * @since 4.0.0
 * @version 4.8.3 - Added missing indexes and atomic operation timeout
 */

namespace BotFend\Database;

use BotFend\Core\Config;
use Exception;

defined('ABSPATH') || exit;

class Database {
    
    const BLOCKLIST_TABLE = 'botfend_blocklist';
    const LOG_TABLE = 'botfend_logs';
    const LOG_AGGREGATES_TABLE = 'botfend_log_aggregates';
    const UNBLOCK_REQUESTS_TABLE = 'botfend_unblock_requests';
    const TOR_NODES_TABLE = 'botfend_tor_nodes';
    
    const CACHE_GROUP = 'botfend_defender';
    
    const CACHE_TTL_SHORT = 30;     
    const CACHE_TTL_MEDIUM = 300;   
    const CACHE_TTL_LONG = 3600;    
    const CACHE_TTL_DAY = 86400;    
    
    const CACHE_PREFIX_BLOCKED = 'blocked_';
    const CACHE_PREFIX_LOGS = 'logs_';
    const CACHE_PREFIX_STATS = 'stats_';
    const CACHE_PREFIX_TOR = 'tor_';
    const CACHE_PREFIX_AGGREGATES = 'aggregates_';
    
    const MAX_DETAILED_LOGS = 300;           
    const AGGREGATE_AFTER_HOURS = 24;        
    const KEEP_AGGREGATES_DAYS = 90;         
    const LOG_SAMPLING_RATE = 0.1;           
    const CRITICAL_SCORE_THRESHOLD = 7;      
    const CRITICAL_ACTIONS = ['blocked', 'challenged', 'tor_blocked', 'iphub_block', 'abuseipdb_block', 'zero_day_block'];
    
    /**
     * Database version for schema migrations
     *
     * @since 4.8.3
     */
    const DB_VERSION = '4.8.3';
    
    private $config;
    private $wpdb;
    
    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->config = Config::get_instance();
    }
    
    private function get_table_name($table) {
        $allowed_tables = [
            self::BLOCKLIST_TABLE,
            self::LOG_TABLE,
            self::LOG_AGGREGATES_TABLE,
            self::UNBLOCK_REQUESTS_TABLE,
            self::TOR_NODES_TABLE,
            'botfend_system_logs',
            'botfend_dedup_queue',
            'botfend_fingerprints',
        ];
        
        if (!in_array($table, $allowed_tables, true)) {
            error_log('BotFend: Invalid table name requested: ' . sanitize_text_field($table));
            return false;
        }
        
        return $this->wpdb->prefix . $table;
    }
    
    public function create_tables() {
        $charset_collate = $this->wpdb->get_charset_collate();
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        
        $blocklist_table = $this->get_table_name(self::BLOCKLIST_TABLE);
        if ($blocklist_table) {
            $sql = "CREATE TABLE IF NOT EXISTS `{$blocklist_table}` (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                ip varchar(45) NOT NULL,
                type varchar(50) NOT NULL,
                reason text NOT NULL,
                score smallint(5) unsigned NOT NULL DEFAULT '0',
                country varchar(10) DEFAULT NULL,
                expires datetime DEFAULT NULL,
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                is_perpetual_offender tinyint(1) unsigned DEFAULT '0',
                PRIMARY KEY  (id),
                UNIQUE KEY idx_ip_expires (ip, expires),
                KEY idx_ip (ip),
                KEY idx_type (type),
                KEY idx_expires (expires),
                KEY idx_score (score),
                KEY idx_country (country),
                KEY idx_created_at (created_at)
            ) {$charset_collate};";
            dbDelta($sql);
        }
        
        $logs_table = $this->get_table_name(self::LOG_TABLE);
        if ($logs_table) {
            $sql = "CREATE TABLE IF NOT EXISTS `{$logs_table}` (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                ip varchar(45) NOT NULL,
                user_agent varchar(500) DEFAULT NULL,
                request_uri varchar(500) DEFAULT NULL,
                http_method varchar(10) DEFAULT NULL,
                score smallint(5) unsigned NOT NULL DEFAULT '0',
                factors text DEFAULT NULL,
                reason text DEFAULT NULL,
                action varchar(50) NOT NULL,
                country varchar(10) DEFAULT NULL,
                is_google_crawler tinyint(1) unsigned DEFAULT '0',
                duplicate_count int(11) unsigned DEFAULT '1',
                is_aggregated tinyint(1) unsigned DEFAULT '0',
                aggregated_at datetime DEFAULT NULL,
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_ip (ip),
                KEY idx_score (score),
                KEY idx_action (action),
                KEY idx_created_at (created_at),
                KEY idx_ip_action (ip, action),
                KEY idx_score_action (score, action),
                KEY idx_ip_created (ip, created_at),
                KEY idx_action_created (action, created_at),
                KEY idx_country (country),
                KEY idx_is_google (is_google_crawler),
                KEY idx_request_uri (request_uri(100)),
                KEY idx_duplicate_count (duplicate_count),
                KEY idx_updated_at (updated_at),
                KEY idx_is_aggregated (is_aggregated),
                KEY idx_aggregated_at (aggregated_at),
                INDEX idx_for_aggregation (is_aggregated, created_at, action, score),
                INDEX idx_ip_action_created_recent (ip, action, created_at),
                UNIQUE KEY idx_ip_action_created (ip, action, created_at)
            ) {$charset_collate};";
            dbDelta($sql);
        }
        
        $aggregates_table = $this->get_table_name(self::LOG_AGGREGATES_TABLE);
        if ($aggregates_table) {
            $sql = "CREATE TABLE IF NOT EXISTS `{$aggregates_table}` (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                period_type varchar(10) NOT NULL,
                period_start datetime NOT NULL,
                period_end datetime NOT NULL,
                total_requests int(11) unsigned NOT NULL DEFAULT '0',
                blocked_requests int(11) unsigned NOT NULL DEFAULT '0',
                challenged_requests int(11) unsigned NOT NULL DEFAULT '0',
                allowed_requests int(11) unsigned NOT NULL DEFAULT '0',
                average_score decimal(5,2) NOT NULL DEFAULT '0.00',
                unique_ips int(11) unsigned NOT NULL DEFAULT '0',
                top_countries text DEFAULT NULL,
                top_actions text DEFAULT NULL,
                threat_distribution text DEFAULT NULL,
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                UNIQUE KEY idx_period_unique (period_type, period_start),
                KEY idx_period_start (period_start),
                KEY idx_period_end (period_end),
                KEY idx_period_type (period_type),
                KEY idx_created_at (created_at)
            ) {$charset_collate};";
            dbDelta($sql);
        }
        
        $unblock_requests_table = $this->get_table_name(self::UNBLOCK_REQUESTS_TABLE);
        if ($unblock_requests_table) {
            $sql = "CREATE TABLE IF NOT EXISTS `{$unblock_requests_table}` (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                ip varchar(45) NOT NULL,
                email varchar(255) NOT NULL,
                reason text DEFAULT NULL,
                status varchar(20) NOT NULL DEFAULT 'pending',
                admin_notes text DEFAULT NULL,
                admin_action_by varchar(100) DEFAULT NULL,
                request_whitelist tinyint(1) unsigned DEFAULT '0',
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_ip (ip),
                KEY idx_status (status),
                KEY idx_created_at (created_at),
                KEY idx_status_created (status, created_at),
                KEY idx_email (email(100)),
                KEY idx_updated_at (updated_at)
            ) {$charset_collate};";
            dbDelta($sql);
        }
        
        $tor_nodes_table = $this->get_table_name(self::TOR_NODES_TABLE);
        if ($tor_nodes_table) {
            $sql = "CREATE TABLE IF NOT EXISTS `{$tor_nodes_table}` (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                ip varchar(45) NOT NULL,
                fingerprint varchar(40) DEFAULT NULL,
                nickname varchar(50) DEFAULT NULL,
                published datetime DEFAULT NULL,
                last_seen datetime DEFAULT NULL,
                exit_address varchar(45) DEFAULT NULL,
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                UNIQUE KEY idx_ip (ip),
                KEY idx_fingerprint (fingerprint),
                KEY idx_exit_address (exit_address),
                KEY idx_last_seen (last_seen),
                KEY idx_published (published),
                KEY idx_updated_at (updated_at)
            ) {$charset_collate};";
            dbDelta($sql);
        }
        
        $dedup_queue_table = $this->get_table_name('botfend_dedup_queue');
        if ($dedup_queue_table) {
            $sql = "CREATE TABLE IF NOT EXISTS `{$dedup_queue_table}` (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                ip varchar(45) NOT NULL,
                score smallint(5) unsigned NOT NULL DEFAULT '0',
                action varchar(50) NOT NULL,
                factors text DEFAULT NULL,
                user_agent varchar(500) DEFAULT NULL,
                country varchar(10) DEFAULT NULL,
                is_google_crawler tinyint(1) unsigned DEFAULT '0',
                request_count int(11) unsigned DEFAULT '1',
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                processed_at datetime DEFAULT NULL,
                PRIMARY KEY  (id),
                KEY idx_ip_action (ip, action),
                KEY idx_created_at (created_at),
                KEY idx_processed (processed_at)
            ) {$charset_collate};";
            dbDelta($sql);
        }
    
        $system_logs_table = $this->get_table_name('botfend_system_logs');
        if ($system_logs_table) {
            $sql = "CREATE TABLE IF NOT EXISTS `{$system_logs_table}` (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                level varchar(20) NOT NULL,
                message text NOT NULL,
                context longtext DEFAULT NULL,
                source varchar(100) DEFAULT NULL,
                ip varchar(45) DEFAULT NULL,
                user_agent varchar(500) DEFAULT NULL,
                created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY  (id),
                KEY idx_level (level),
                KEY idx_source (source),
                KEY idx_created_at (created_at),
                KEY idx_ip (ip)
            ) {$charset_collate};";
            dbDelta($sql);
        }
    }
    
    /**
     * Check and run database schema upgrades
     *
     * @since 4.8.3
     * @return bool True if upgrade was performed
     */
    public function maybe_upgrade_database() {
        $installed_version = get_option('botfend_db_version', '0');
        
        if (version_compare($installed_version, self::DB_VERSION, '<')) {
            $this->upgrade_schema();
            return true;
        }
        
        return false;
    }
    
    /**
     * Upgrade database schema for existing installations
     *
     * @since 4.8.3
     * @return bool True if upgrades were applied
     */
    public function upgrade_schema() {
        $current_version = get_option('botfend_db_version', '4.8.2');
        
        if (version_compare($current_version, '4.8.3', '<')) {
            $this->add_missing_indexes();
            update_option('botfend_db_version', '4.8.3');
            return true;
        }
        
        return false;
    }
    
    /**
     * Add missing indexes to existing tables
     *
     * @since 4.8.3
     * @return void
     */
    private function add_missing_indexes() {
        $logs_table = $this->get_table_name(self::LOG_TABLE);
        if (!$logs_table) {
            return;
        }
        
        $indexes_to_add = [
            'idx_for_aggregation' => "ALTER TABLE `{$logs_table}` ADD INDEX idx_for_aggregation (is_aggregated, created_at, action, score)",
            'idx_ip_action_created_recent' => "ALTER TABLE `{$logs_table}` ADD INDEX idx_ip_action_created_recent (ip, action, created_at)"
        ];
        
        foreach ($indexes_to_add as $index_name => $sql) {
            $check_index = $this->wpdb->get_results($this->wpdb->prepare(
                "SHOW INDEX FROM `{$logs_table}` WHERE Key_name = %s",
                $index_name
            ));
            
            if (empty($check_index)) {
                $result = $this->wpdb->query($sql);
                if ($result === false) {
                    error_log(sprintf(
                        'BotFend: Failed to add index %s. Error: %s',
                        $index_name,
                        $this->wpdb->last_error ?: 'unknown'
                    ));
                }
            }
        }
        
        $this->clear_all_caches();
    }
    
    public function drop_tables() {
        $core_tables = [
            self::BLOCKLIST_TABLE,
            self::LOG_TABLE,
            self::LOG_AGGREGATES_TABLE,
            self::UNBLOCK_REQUESTS_TABLE,
            self::TOR_NODES_TABLE,
            'botfend_system_logs',
            'botfend_dedup_queue',
            'botfend_fingerprints',
        ];
        
        foreach ($core_tables as $table_name) {
            $table = $this->get_table_name($table_name);
            if ($table) {
                $this->wpdb->query('DROP TABLE IF EXISTS `' . esc_sql($table) . '`');
            }
        }
        
        $this->clear_all_caches();
    }
    
    public function set_default_options() {
        $definitions = $this->config->get_definitions();
        
        foreach ($definitions as $option => $definition) {
            if (false === get_option($option)) {
                $default = $definition['default'];
                
                if (null === $default && $option === 'botfend_session_secure_cookie') {
                    $default = is_ssl();
                }
                
                add_option($option, $default);
            }
        }
    }
    
    public function add_to_whitelist($ip) {
        if (!filter_var($ip, FILTER_VALIDATE_IP)) {
            return false;
        }
        
        $current_list = (string) get_option('botfend_whitelist_ips', '');
        
        $lines = [];
        if (!empty(trim($current_list))) {
            $lines = explode("\n", $current_list);
            $lines = array_map('trim', $lines);
            $lines = array_filter($lines, function($line) {
                return !empty($line) && strpos($line, '#') !== 0;
            });
            $lines = array_values($lines);
        }
        
        if (in_array($ip, $lines, true)) {
            return true;
        }
        
        $lines[] = $ip;
        $lines = array_slice(array_values(array_unique($lines)), 0, 10000);
        
        return update_option('botfend_whitelist_ips', implode("\n", $lines), false);
    }

    public function bulk_delete_logs_by_ips($ips) {
        if (empty($ips) || !is_array($ips)) {
            return 0;
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return 0;
        }
        
        $sanitized_ips = array_map('sanitize_text_field', $ips);
        $sanitized_ips = array_filter($sanitized_ips, function($ip) {
            return filter_var($ip, FILTER_VALIDATE_IP);
        });
        
        if (empty($sanitized_ips)) {
            return 0;
        }
        
        $placeholders = implode(',', array_fill(0, count($sanitized_ips), '%s'));
        
        $query = $this->wpdb->prepare(
            "DELETE FROM `{$table}` WHERE ip IN ({$placeholders})",
            $sanitized_ips
        );
        
        $result = $this->wpdb->query($query);
        
        if ($result === false) {
            error_log('BotFend: Database error deleting logs by IPs. Error: ' . $this->wpdb->last_error);
            return 0;
        }
        
        $this->clear_all_caches();
        return (int) $result;
    }
    
    // ============================================
    // SMART AGGREGATION METHODS
    // ============================================
    
    public function log_request($ip, $score, $action, $factors, $user_agent = '', $is_google_crawler = false, $country = null, $throttle_seconds = null) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        if (!$this->config->get_bool('enable_smart_aggregation', true)) {
            return $this->log_request_direct($ip, $score, $action, $factors, $user_agent, $is_google_crawler, $country, $throttle_seconds);
        }
        
        $critical_score = $this->config->get_int('critical_score_threshold', self::CRITICAL_SCORE_THRESHOLD);
        $sampling_rate = $this->config->get_float('log_sampling_rate', self::LOG_SAMPLING_RATE);
        
        $should_log = $this->should_log_request($score, $action, $critical_score, $sampling_rate);
        
        if ($should_log) {
            $logged = $this->log_request_with_dedup($ip, $score, $action, $factors, $user_agent, $is_google_crawler, $country, $throttle_seconds);
            
            if ($logged) {
                $max_logs = $this->config->get_int('max_detailed_logs', self::MAX_DETAILED_LOGS);
                $this->enforce_detailed_log_limit($max_logs);
                
                $aggregate_hours = $this->config->get_int('aggregate_after_hours', self::AGGREGATE_AFTER_HOURS);
                $this->check_and_aggregate_old_logs($aggregate_hours);
                
                $this->update_realtime_counters($ip, $score, $action, $country);
            }
            
            return $logged;
        } else {
            $this->update_realtime_counters($ip, $score, $action, $country);
            return true;
        }
    }
    
    private function should_log_request($score, $action, $critical_score, $sampling_rate) {
        if ($score >= $critical_score) {
            return true;
        }
        
        if (in_array($action, self::CRITICAL_ACTIONS, true)) {
            return true;
        }
        
        $sampling_rate = max(0, min(1, $sampling_rate));
        $random = wp_rand(0, 1000) / 1000;
        
        return $random <= $sampling_rate;
    }
    
    private function log_request_with_dedup($ip, $score, $action, $factors, $user_agent = '', $is_google_crawler = false, $country = null, $throttle_seconds = null) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        if (!$this->config->get_bool('enable_centralized_logging', true)) {
            return $this->log_request_legacy($ip, $score, $action, $factors, $user_agent, $is_google_crawler, $country);
        }
        
        if ($throttle_seconds === null) {
            $throttle_seconds = $this->config->get_int('log_throttle_seconds', 10);
        }
        
        $dedup_window = $this->config->get_int('log_deduplication_window', 300);
        
        if ($throttle_seconds > 0) {
            $existing_log_id = $this->find_duplicate_log($ip, $action, $score, $dedup_window);
            
            if ($existing_log_id) {
                return $this->update_log_entry($existing_log_id, $score, $factors, $throttle_seconds);
            }
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return false;
        }
        
        $factors_str = $this->prepare_factors_string($factors);
        
        $request_uri = isset($_SERVER['REQUEST_URI']) ? 
            substr(sanitize_text_field(wp_unslash($_SERVER['REQUEST_URI'])), 0, 500) : '';
        
        $http_method = isset($_SERVER['REQUEST_METHOD']) ? 
            substr(sanitize_text_field(wp_unslash($_SERVER['REQUEST_METHOD'])), 0, 10) : '';
        
        $this->wpdb->query('START TRANSACTION');
        
        try {
            $result = $this->wpdb->insert(
                $table,
                [
                    'ip' => sanitize_text_field($ip),
                    'user_agent' => substr(sanitize_text_field($user_agent), 0, 500),
                    'request_uri' => $request_uri,
                    'http_method' => $http_method,
                    'score' => absint($score),
                    'factors' => $factors_str,
                    'reason' => $factors_str,
                    'action' => sanitize_text_field($action),
                    'country' => sanitize_text_field($country),
                    'is_google_crawler' => $is_google_crawler ? 1 : 0,
                    'duplicate_count' => 1,
                    'is_aggregated' => 0,
                    'aggregated_at' => null,
                    'created_at' => current_time('mysql', true),
                    'updated_at' => current_time('mysql', true),
                ],
                ['%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%s', '%s', '%s']
            );
            
            if ($result === false) {
                $this->wpdb->query('ROLLBACK');
                error_log(sprintf(
                    'BotFend: Database error inserting log for IP: %s. Error: %s',
                    $ip,
                    $this->wpdb->last_error ?: 'unknown'
                ));
                return false;
            }
            
            $this->wpdb->query('COMMIT');
            
            $this->invalidate_ip_logs_cache($ip);
            $this->invalidate_stats_cache();
            
            if ($throttle_seconds > 0) {
                $throttle_key = 'botfend_log_throttle_' . md5($ip . '_' . $action);
                set_transient($throttle_key, time(), $throttle_seconds);
            }
            
            return true;
        } catch (Exception $e) {
            $this->wpdb->query('ROLLBACK');
            
            if ($this->wpdb->last_error && strpos($this->wpdb->last_error, 'Deadlock') !== false) {
                usleep(100000);
                return $this->log_request_with_dedup($ip, $score, $action, $factors, $user_agent, $is_google_crawler, $country, $throttle_seconds);
            }
            
            error_log(sprintf(
                'BotFend: Exception inserting log for IP: %s. Message: %s. DB Error: %s',
                $ip,
                $e->getMessage(),
                $this->wpdb->last_error ?: 'none'
            ));
            return false;
        }
    }
    
    private function find_duplicate_log($ip, $action, $score, $window_seconds = 300) {
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return false;
        }
        
        $query = $this->wpdb->prepare(
            "SELECT id, duplicate_count FROM `{$table}` 
            WHERE ip = %s 
            AND action = %s 
            AND is_aggregated = 0
            AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d SECOND)
            ORDER BY created_at DESC 
            LIMIT 1",
            sanitize_text_field($ip),
            sanitize_text_field($action),
            absint($window_seconds)
        );
        
        $existing = $this->wpdb->get_row($query);
        
        return $existing ? (int) $existing->id : false;
    }
    
    private function update_log_entry($log_id, $score, $factors, $throttle_seconds = 10) {
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return false;
        }
        
        $existing_log = $this->wpdb->get_row($this->wpdb->prepare(
            "SELECT score, factors, duplicate_count, ip, action FROM `{$table}` WHERE id = %d",
            absint($log_id)
        ));
        
        if (!$existing_log) {
            return false;
        }
        
        $merged_factors = $this->merge_factors($existing_log->factors, $factors);
        $new_score = max($existing_log->score, absint($score));
        
        $result = $this->wpdb->update(
            $table,
            [
                'score' => $new_score,
                'factors' => $merged_factors,
                'reason' => $merged_factors,
                'duplicate_count' => $existing_log->duplicate_count + 1,
                'updated_at' => current_time('mysql', true),
            ],
            ['id' => $log_id],
            ['%d', '%s', '%s', '%d', '%s'],
            ['%d']
        );
        
        if ($result === false) {
            error_log(sprintf(
                'BotFend: Database error updating log ID: %d. Error: %s',
                $log_id,
                $this->wpdb->last_error ?: 'unknown'
            ));
            return false;
        }
        
        $this->invalidate_ip_logs_cache($existing_log->ip);
        
        if ($throttle_seconds > 0) {
            $throttle_key = 'botfend_log_throttle_' . md5($existing_log->ip . '_' . $existing_log->action);
            set_transient($throttle_key, time(), $throttle_seconds);
        }
        
        return true;
    }
    
    private function log_request_direct($ip, $score, $action, $factors, $user_agent = '', $is_google_crawler = false, $country = null, $throttle_seconds = null) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        if (!$this->config->get_bool('enable_centralized_logging', true)) {
            return $this->log_request_legacy($ip, $score, $action, $factors, $user_agent, $is_google_crawler, $country);
        }
        
        if ($throttle_seconds === null) {
            $throttle_seconds = $this->config->get_int('log_throttle_seconds', 10);
        }
        
        $dedup_window = $this->config->get_int('log_deduplication_window', 300);
        
        if ($throttle_seconds > 0) {
            $existing_log_id = $this->find_duplicate_log($ip, $action, $score, $dedup_window);
            
            if ($existing_log_id) {
                return $this->update_log_entry($existing_log_id, $score, $factors, $throttle_seconds);
            }
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return false;
        }
        
        $factors_str = $this->prepare_factors_string($factors);
        
        $request_uri = isset($_SERVER['REQUEST_URI']) ? 
            substr(sanitize_text_field(wp_unslash($_SERVER['REQUEST_URI'])), 0, 500) : '';
        
        $http_method = isset($_SERVER['REQUEST_METHOD']) ? 
            substr(sanitize_text_field(wp_unslash($_SERVER['REQUEST_METHOD'])), 0, 10) : '';
        
        $result = $this->wpdb->insert(
            $table,
            [
                'ip' => sanitize_text_field($ip),
                'user_agent' => substr(sanitize_text_field($user_agent), 0, 500),
                'request_uri' => $request_uri,
                'http_method' => $http_method,
                'score' => absint($score),
                'factors' => $factors_str,
                'reason' => $factors_str,
                'action' => sanitize_text_field($action),
                'country' => sanitize_text_field($country),
                'is_google_crawler' => $is_google_crawler ? 1 : 0,
                'duplicate_count' => 1,
                'is_aggregated' => 0,
                'created_at' => current_time('mysql', true),
            ],
            ['%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%s']
        );
        
        if ($result !== false) {
            $this->invalidate_ip_logs_cache($ip);
            $this->invalidate_stats_cache();
        } else {
            error_log(sprintf(
                'BotFend: Database error inserting direct log for IP: %s. Error: %s',
                $ip,
                $this->wpdb->last_error ?: 'unknown'
            ));
        }
        
        return $result !== false;
    }
    
    private function log_request_legacy($ip, $score, $action, $factors, $user_agent = '', $is_google_crawler = false, $country = null) {
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return false;
        }
        
        $request_uri = isset($_SERVER['REQUEST_URI']) ? 
            substr(sanitize_text_field(wp_unslash($_SERVER['REQUEST_URI'])), 0, 500) : '';
        
        $http_method = isset($_SERVER['REQUEST_METHOD']) ? 
            substr(sanitize_text_field(wp_unslash($_SERVER['REQUEST_METHOD'])), 0, 10) : '';
        
        $factors_str = $this->prepare_factors_string($factors);
        
        $result = $this->wpdb->insert(
            $table,
            [
                'ip' => sanitize_text_field($ip),
                'user_agent' => substr(sanitize_text_field($user_agent), 0, 500),
                'request_uri' => $request_uri,
                'http_method' => $http_method,
                'score' => absint($score),
                'factors' => $factors_str,
                'reason' => $factors_str,
                'action' => sanitize_text_field($action),
                'country' => sanitize_text_field($country),
                'is_google_crawler' => $is_google_crawler ? 1 : 0,
                'created_at' => current_time('mysql', true),
            ],
            ['%s', '%s', '%s', '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%s']
        );
        
        if ($result !== false) {
            $this->invalidate_ip_logs_cache($ip);
            $this->invalidate_stats_cache();
        } else {
            error_log(sprintf(
                'BotFend: Database error inserting legacy log for IP: %s. Error: %s',
                $ip,
                $this->wpdb->last_error ?: 'unknown'
            ));
        }
        
        return $result !== false;
    }
    
    private function enforce_detailed_log_limit($max_logs) {
        if ($max_logs <= 0) {
            return;
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return;
        }
        
        $count = (int) $this->wpdb->get_var(
            "SELECT COUNT(*) FROM `{$table}` WHERE is_aggregated = 0"
        );
        
        if ($count > $max_logs) {
            $to_aggregate = $count - $max_logs;
            
            $batch_size = 1000;
            $offset = 0;
            $max_iterations = ceil($to_aggregate / $batch_size);
            $iteration = 0;
            
            do {
                $old_logs_batch = $this->wpdb->get_results($this->wpdb->prepare(
                    "SELECT id, ip, score, action, country, created_at 
                     FROM `{$table}` 
                     WHERE is_aggregated = 0 
                     ORDER BY created_at ASC 
                     LIMIT %d OFFSET %d",
                    $batch_size,
                    $offset
                ));
                
                if (!empty($old_logs_batch)) {
                    $this->aggregate_logs($old_logs_batch, 'limit_enforcement');
                    
                    $ids = array_map(function($log) {
                        return absint($log->id);
                    }, $old_logs_batch);
                    
                    $id_list = implode(',', $ids);
                    $this->wpdb->query(
                        "UPDATE `{$table}` SET is_aggregated = 1, aggregated_at = UTC_TIMESTAMP() 
                         WHERE id IN ({$id_list})"
                    );
                }
                
                $offset += $batch_size;
                $iteration++;
                
                if ($iteration >= $max_iterations) {
                    break;
                }
                
                usleep(50000);
                
            } while (count($old_logs_batch) === $batch_size);
        }
    }
    
    private function check_and_aggregate_old_logs($aggregate_hours) {
        if ($aggregate_hours <= 0) {
            return;
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return;
        }
        
        $batch_size = 1000;
        $offset = 0;
        $max_iterations = 100;
        $iteration = 0;
        
        do {
            $old_logs_batch = $this->wpdb->get_results($this->wpdb->prepare(
                "SELECT id, ip, score, action, country, created_at 
                 FROM `{$table}` 
                 WHERE is_aggregated = 0 
                 AND created_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d HOUR)
                 ORDER BY created_at ASC 
                 LIMIT %d OFFSET %d",
                $aggregate_hours,
                $batch_size,
                $offset
            ));
            
            if (!empty($old_logs_batch)) {
                $this->aggregate_logs($old_logs_batch, 'time_based');
                
                $ids = array_map(function($log) {
                    return absint($log->id);
                }, $old_logs_batch);
                
                $id_list = implode(',', $ids);
                $this->wpdb->query(
                    "UPDATE `{$table}` SET is_aggregated = 1, aggregated_at = UTC_TIMESTAMP() 
                     WHERE id IN ({$id_list})"
                );
            }
            
            $offset += $batch_size;
            $iteration++;
            
            if ($iteration >= $max_iterations) {
                break;
            }
            
            usleep(50000);
            
        } while (count($old_logs_batch) === $batch_size);
    }
    
    private function aggregate_logs($logs, $aggregation_reason = 'auto') {
        if (empty($logs)) {
            return;
        }
        
        $hourly_data = [];
        foreach ($logs as $log) {
            $hour_key = gmdate('Y-m-d H:00:00', strtotime($log->created_at));
            
            if (!isset($hourly_data[$hour_key])) {
                $hourly_data[$hour_key] = [
                    'total' => 0,
                    'scores' => [],
                    'actions' => [],
                    'countries' => [],
                    'ips' => [],
                    'blocked' => 0,
                    'challenged' => 0,
                    'allowed' => 0,
                ];
            }
            
            $data = &$hourly_data[$hour_key];
            $data['total']++;
            $data['scores'][] = absint($log->score);
            $data['actions'][$log->action] = ($data['actions'][$log->action] ?? 0) + 1;
            
            if ($log->country) {
                $data['countries'][$log->country] = ($data['countries'][$log->country] ?? 0) + 1;
            }
            
            $data['ips'][$log->ip] = true;
            
            if (in_array($log->action, ['blocked', 'iphub_block', 'abuseipdb_block', 'tor_blocked', 'zero_day_block'])) {
                $data['blocked']++;
            } elseif (in_array($log->action, ['challenged', 'js_challenge', 'country_challenge', 'tor_challenge'])) {
                $data['challenged']++;
            } elseif (in_array($log->action, ['allowed', 'sampled', 'whitelist_allowed', 'verified_crawler'])) {
                $data['allowed']++;
            }
        }
        
        $aggregates_table = $this->get_table_name(self::LOG_AGGREGATES_TABLE);
        if (!$aggregates_table) {
            return;
        }
        
        $this->wpdb->query('START TRANSACTION');
        
        try {
            foreach ($hourly_data as $hour_start => $data) {
                $hour_end = gmdate('Y-m-d H:59:59', strtotime($hour_start));
                
                $avg_score = !empty($data['scores']) ? array_sum($data['scores']) / count($data['scores']) : 0;
                
                arsort($data['countries']);
                arsort($data['actions']);
                
                $threat_dist = [
                    'low' => count(array_filter($data['scores'], function($s) { return $s < 4; })),
                    'medium' => count(array_filter($data['scores'], function($s) { return $s >= 4 && $s < 7; })),
                    'high' => count(array_filter($data['scores'], function($s) { return $s >= 7; })),
                ];
                
                $this->save_aggregate(
                    'hourly',
                    $hour_start,
                    $hour_end,
                    $data['total'],
                    $data['blocked'],
                    $data['challenged'],
                    $data['allowed'],
                    $avg_score,
                    count($data['ips']),
                    array_slice($data['countries'], 0, 5, true),
                    array_slice($data['actions'], 0, 5, true),
                    $threat_dist
                );
            }
            
            $this->wpdb->query('COMMIT');
        } catch (Exception $e) {
            $this->wpdb->query('ROLLBACK');
            
            if ($this->wpdb->last_error && strpos($this->wpdb->last_error, 'Deadlock') !== false) {
                usleep(100000);
                $this->aggregate_logs($logs, $aggregation_reason);
                return;
            }
            
            error_log(sprintf(
                'BotFend: Exception aggregating logs. Message: %s. DB Error: %s',
                $e->getMessage(),
                $this->wpdb->last_error ?: 'none'
            ));
            return;
        }
        
        $this->create_daily_aggregates();
    }
    
    private function save_aggregate($period_type, $period_start, $period_end, $total_requests, $blocked_requests, $challenged_requests, $allowed_requests, $avg_score, $unique_ips, $top_countries, $top_actions, $threat_distribution) {
        $table = $this->get_table_name(self::LOG_AGGREGATES_TABLE);
        if (!$table) {
            return;
        }
        
        $data = [
            'period_type' => sanitize_text_field($period_type),
            'period_start' => sanitize_text_field($period_start),
            'period_end' => sanitize_text_field($period_end),
            'total_requests' => absint($total_requests),
            'blocked_requests' => absint($blocked_requests),
            'challenged_requests' => absint($challenged_requests),
            'allowed_requests' => absint($allowed_requests),
            'average_score' => round(floatval($avg_score), 2),
            'unique_ips' => absint($unique_ips),
            'top_countries' => wp_json_encode($top_countries),
            'top_actions' => wp_json_encode($top_actions),
            'threat_distribution' => wp_json_encode($threat_distribution),
        ];
        
        $existing = $this->wpdb->get_row($this->wpdb->prepare(
            "SELECT id FROM `{$table}` 
             WHERE period_type = %s 
             AND period_start = %s",
            $data['period_type'],
            $data['period_start']
        ));
        
        if ($existing) {
            $result = $this->wpdb->update(
                $table,
                $data,
                ['id' => $existing->id],
                ['%s', '%s', '%s', '%d', '%d', '%d', '%d', '%f', '%d', '%s', '%s', '%s'],
                ['%d']
            );
            
            if ($result === false) {
                error_log(sprintf(
                    'BotFend: Database error updating aggregate. Error: %s',
                    $this->wpdb->last_error ?: 'unknown'
                ));
            }
        } else {
            $result = $this->wpdb->insert(
                $table,
                $data,
                ['%s', '%s', '%s', '%d', '%d', '%d', '%d', '%f', '%d', '%s', '%s', '%s']
            );
            
            if ($result === false) {
                error_log(sprintf(
                    'BotFend: Database error inserting aggregate. Error: %s',
                    $this->wpdb->last_error ?: 'unknown'
                ));
            }
        }
        
        $this->invalidate_aggregates_cache();
    }
    
    private function create_daily_aggregates() {
        $aggregates_table = $this->get_table_name(self::LOG_AGGREGATES_TABLE);
        if (!$aggregates_table) {
            return;
        }
        
        $yesterday = gmdate('Y-m-d', strtotime('-1 day'));
        $hourly_aggregates = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT * FROM `{$aggregates_table}` 
             WHERE period_type = 'hourly' 
             AND DATE(period_start) = %s",
            $yesterday
        ));
        
        if (empty($hourly_aggregates)) {
            return;
        }
        
        $daily_data = [
            'total_requests' => 0,
            'blocked_requests' => 0,
            'challenged_requests' => 0,
            'allowed_requests' => 0,
            'scores' => [],
            'countries' => [],
            'actions' => [],
            'ips' => [],
        ];
        
        foreach ($hourly_aggregates as $hourly) {
            $daily_data['total_requests'] += absint($hourly->total_requests);
            $daily_data['blocked_requests'] += absint($hourly->blocked_requests);
            $daily_data['challenged_requests'] += absint($hourly->challenged_requests);
            $daily_data['allowed_requests'] += absint($hourly->allowed_requests);
            
            $hourly_scores = json_decode($hourly->threat_distribution, true);
            if ($hourly_scores) {
                $hour_total = array_sum(array_values($hourly_scores));
                if ($hour_total > 0) {
                    $estimated_avg = (
                        ($hourly_scores['low'] ?? 0) * 2 +
                        ($hourly_scores['medium'] ?? 0) * 5.5 +
                        ($hourly_scores['high'] ?? 0) * 8.5
                    ) / $hour_total;
                    $daily_data['scores'][] = $estimated_avg * $hour_total;
                }
            }
            
            $hourly_countries = json_decode($hourly->top_countries, true);
            if ($hourly_countries) {
                foreach ($hourly_countries as $country => $count) {
                    $daily_data['countries'][$country] = ($daily_data['countries'][$country] ?? 0) + $count;
                }
            }
            
            $hourly_actions = json_decode($hourly->top_actions, true);
            if ($hourly_actions) {
                foreach ($hourly_actions as $action => $count) {
                    $daily_data['actions'][$action] = ($daily_data['actions'][$action] ?? 0) + $count;
                }
            }
            
            $daily_data['ips'][] = absint($hourly->unique_ips);
        }
        
        $avg_score = !empty($daily_data['scores']) ? 
            array_sum($daily_data['scores']) / $daily_data['total_requests'] : 0;
        
        $unique_ips_estimate = !empty($daily_data['ips']) ? 
            (int) round(array_sum($daily_data['ips']) / count($daily_data['ips'])) : 0;
        
        arsort($daily_data['countries']);
        arsort($daily_data['actions']);
        
        $threat_dist = [
            'low' => (int) round($daily_data['total_requests'] * 0.4),
            'medium' => (int) round($daily_data['total_requests'] * 0.4),
            'high' => (int) round($daily_data['total_requests'] * 0.2),
        ];
        
        $this->wpdb->query('START TRANSACTION');
        
        try {
            $this->save_aggregate(
                'daily',
                $yesterday . ' 00:00:00',
                $yesterday . ' 23:59:59',
                $daily_data['total_requests'],
                $daily_data['blocked_requests'],
                $daily_data['challenged_requests'],
                $daily_data['allowed_requests'],
                $avg_score,
                $unique_ips_estimate,
                array_slice($daily_data['countries'], 0, 5, true),
                array_slice($daily_data['actions'], 0, 5, true),
                $threat_dist
            );
            
            $this->wpdb->query('COMMIT');
        } catch (Exception $e) {
            $this->wpdb->query('ROLLBACK');
            
            if ($this->wpdb->last_error && strpos($this->wpdb->last_error, 'Deadlock') !== false) {
                usleep(100000);
                $this->create_daily_aggregates();
                return;
            }
            
            error_log(sprintf(
                'BotFend: Exception creating daily aggregate. Message: %s. DB Error: %s',
                $e->getMessage(),
                $this->wpdb->last_error ?: 'none'
            ));
        }
    }
    
    private function update_realtime_counters($ip, $score, $action, $country) {
        $cache_key = 'botfend_' . self::CACHE_PREFIX_STATS . 'realtime_' . gmdate('Y-m-d-H');
        
        $stats = get_transient($cache_key);
        if (!$stats) {
            $stats = [
                'total' => 0,
                'scores' => [],
                'actions' => [],
                'countries' => [],
                'ips' => [],
                'hour' => gmdate('Y-m-d H:00:00'),
            ];
        }
        
        $stats['total']++;
        $stats['scores'][] = $score;
        $stats['actions'][$action] = ($stats['actions'][$action] ?? 0) + 1;
        
        if ($country) {
            $stats['countries'][$country] = ($stats['countries'][$country] ?? 0) + 1;
        }
        
        $stats['ips'][$ip] = true;
        
        set_transient($cache_key, $stats, 2 * HOUR_IN_SECONDS);
    }
    
    public function get_aggregated_stats($period_type = 'daily', $days = 7) {
        $cache_key = self::CACHE_PREFIX_AGGREGATES . $period_type . '_' . $days;
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($period_type, $days) {
            $table = $this->get_table_name(self::LOG_AGGREGATES_TABLE);
            if (!$table) {
                return [];
            }
            
            $results = $this->wpdb->get_results($this->wpdb->prepare(
                "SELECT * FROM `{$table}` 
                 WHERE period_type = %s 
                 AND period_start >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)                 
                 ORDER BY period_start DESC",
                sanitize_text_field($period_type),
                absint($days)
            )) ?: [];
            
            foreach ($results as &$result) {
                $result->top_countries = json_decode($result->top_countries, true) ?: [];
                $result->top_actions = json_decode($result->top_actions, true) ?: [];
                $result->threat_distribution = json_decode($result->threat_distribution, true) ?: [];
                $result->average_score = floatval($result->average_score);
            }
            
            return $results;
        }, self::CACHE_TTL_MEDIUM);
    }
    
    public function get_realtime_stats() {
        $cache_key = 'botfend_' . self::CACHE_PREFIX_STATS . 'realtime_' . gmdate('Y-m-d-H');
        $stats = get_transient($cache_key);
        
        if (!$stats) {
            return [
                'total' => 0,
                'average_score' => 0,
                'top_actions' => [],
                'top_countries' => [],
                'unique_ips' => 0,
            ];
        }
        
        $avg_score = !empty($stats['scores']) ? 
            round(array_sum($stats['scores']) / count($stats['scores']), 2) : 0;
        
        arsort($stats['actions']);
        arsort($stats['countries']);
        
        return [
            'total' => $stats['total'],
            'average_score' => $avg_score,
            'top_actions' => array_slice($stats['actions'], 0, 5, true),
            'top_countries' => array_slice($stats['countries'], 0, 5, true),
            'unique_ips' => count($stats['ips']),
            'hour' => $stats['hour'],
        ];
    }
    
    // ============================================
    // EXISTING DATABASE METHODS
    // ============================================
    
    public function get_logs_deduplicated($filters = [], $limit = 100, $hours = 24) {
        if ($this->config->get_bool('enable_smart_aggregation', true)) {
            if (!isset($filters['include_aggregated'])) {
                $filters['is_aggregated'] = 0;
            }
        }
        
        $cache_key = self::CACHE_PREFIX_LOGS . 'dedup_' . $this->generate_cache_key($filters) . '_' . $limit . '_' . $hours;
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($filters, $limit, $hours) {
            $table = $this->get_table_name(self::LOG_TABLE);
            if (!$table) {
                return [];
            }
            
            $where = ['1=1'];
            $params = [];
            
            if ($hours > 0) {
                $where[] = 'created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d HOUR)';
                $params[] = absint($hours);
            }
            
            if (!empty($filters['ip'])) {
                $where[] = 'ip LIKE %s';
                $params[] = '%' . $this->wpdb->esc_like(sanitize_text_field($filters['ip'])) . '%';
            }
            
            if (!empty($filters['action'])) {
                if (is_array($filters['action'])) {
                    $placeholders = implode(',', array_fill(0, count($filters['action']), '%s'));
                    $where[] = "action IN ({$placeholders})";
                    $params = array_merge($params, array_map('sanitize_text_field', $filters['action']));
                } else {
                    $where[] = 'action = %s';
                    $params[] = sanitize_text_field($filters['action']);
                }
            }
            
            if (!empty($filters['min_score'])) {
                $where[] = 'score >= %d';
                $params[] = absint($filters['min_score']);
            }
            
            if (!empty($filters['country'])) {
                $where[] = 'country = %s';
                $params[] = sanitize_text_field($filters['country']);
            }
            
            if (!empty($filters['date_from'])) {
                $where[] = 'created_at >= %s';
                $params[] = sanitize_text_field($filters['date_from']) . ' 00:00:00';
            }
            
            if (!empty($filters['date_to'])) {
                $where[] = 'created_at <= %s';
                $params[] = sanitize_text_field($filters['date_to']) . ' 23:59:59';
            }
            
            if (isset($filters['is_aggregated'])) {
                $where[] = 'is_aggregated = %d';
                $params[] = absint($filters['is_aggregated']);
            }
            
            $where_clause = implode(' AND ', $where);
            
            $query = $this->wpdb->prepare(
                "SELECT * FROM `{$table}` WHERE {$where_clause} ORDER BY created_at DESC LIMIT %d",
                array_merge($params, [absint($limit)])
            );
            
            $results = $this->wpdb->get_results($query) ?: [];
            
            foreach ($results as $result) {
                $result->ip = sanitize_text_field($result->ip);
                $result->user_agent = sanitize_text_field($result->user_agent);
                $result->request_uri = sanitize_text_field($result->request_uri);
                $result->http_method = sanitize_text_field($result->http_method);
                $result->action = sanitize_text_field($result->action);
                $result->country = sanitize_text_field($result->country);
                $result->score = absint($result->score);
                $result->is_google_crawler = (bool) $result->is_google_crawler;
                $result->is_aggregated = (bool) $result->is_aggregated;
                $result->duplicate_count = isset($result->duplicate_count) ? absint($result->duplicate_count) : 1;
            }
            
            return $results;
        }, self::CACHE_TTL_MEDIUM);
    }
    
    public function get_logs($filters = [], $limit = 100) {
        return $this->get_logs_deduplicated($filters, $limit, 24);
    }
    
    public function get_logs_count($filters = []) {
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return 0;
        }
        
        $where = ['1=1'];
        $params = [];
        
        if (!empty($filters['ip'])) {
            $where[] = 'ip LIKE %s';
            $params[] = '%' . $this->wpdb->esc_like(sanitize_text_field($filters['ip'])) . '%';
        }
        
        if (!empty($filters['action'])) {
            if (is_array($filters['action'])) {
                $placeholders = implode(',', array_fill(0, count($filters['action']), '%s'));
                $where[] = "action IN ({$placeholders})";
                $params = array_merge($params, array_map('sanitize_text_field', $filters['action']));
            } else {
                $where[] = 'action = %s';
                $params[] = sanitize_text_field($filters['action']);
            }
        }
        
        if (!empty($filters['min_score'])) {
            $where[] = 'score >= %d';
            $params[] = absint($filters['min_score']);
        }
        
        if (!empty($filters['country'])) {
            $where[] = 'country = %s';
            $params[] = sanitize_text_field($filters['country']);
        }
        
        if (!empty($filters['date_from'])) {
            $where[] = 'created_at >= %s';
            $params[] = sanitize_text_field($filters['date_from']) . ' 00:00:00';
        }
        
        if (!empty($filters['date_to'])) {
            $where[] = 'created_at <= %s';
            $params[] = sanitize_text_field($filters['date_to']) . ' 23:59:59';
        }
        
        if (isset($filters['is_aggregated'])) {
            $where[] = 'is_aggregated = %d';
            $params[] = absint($filters['is_aggregated']);
        }
        
        if (empty($filters['date_from']) && empty($filters['date_to']) && empty($filters['hours'])) {
            $where[] = 'created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)';
        }
        
        $where_clause = implode(' AND ', $where);
        
        if (!empty($params)) {
            $query = $this->wpdb->prepare(
                "SELECT COUNT(*) FROM `{$table}` WHERE {$where_clause}",
                $params
            );
        } else {
            $query = "SELECT COUNT(*) FROM `{$table}` WHERE {$where_clause}";
        }
        
        $result = $this->wpdb->get_var($query);
        
        if ($result === null && $this->wpdb->last_error) {
            error_log('BotFend: Database error getting logs count. Error: ' . $this->wpdb->last_error);
            return 0;
        }
        
        return (int) $result;
    }
    
    public function cleanup_duplicate_logs() {
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return 0;
        }
        
        $batch_size = 1000;
        $offset = 0;
        $deleted = 0;
        $max_iterations = 100;
        $iteration = 0;
        
        do {
            $duplicates_batch = $this->wpdb->get_results($this->wpdb->prepare(
                "SELECT 
                    ip, 
                    action, 
                    DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H:%%i') as minute_group,
                    MIN(id) as keep_id,
                    GROUP_CONCAT(id) as all_ids,
                    COUNT(*) as total_count
                FROM `{$table}`
                WHERE is_aggregated = 0
                AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)
                GROUP BY ip, action, minute_group
                HAVING COUNT(*) > 1
                LIMIT %d OFFSET %d",
                $batch_size,
                $offset
            ));
            
            foreach ($duplicates_batch as $dup) {
                $all_ids = explode(',', $dup->all_ids);
                $keep_id = $dup->keep_id;
                $delete_ids = array_diff($all_ids, [$keep_id]);
                
                if (!empty($delete_ids)) {
                    $this->wpdb->update(
                        $table,
                        ['duplicate_count' => $dup->total_count],
                        ['id' => $keep_id],
                        ['%d'],
                        ['%d']
                    );
                    
                    $id_list = implode(',', array_map('absint', $delete_ids));
                    $delete_result = $this->wpdb->query("DELETE FROM `{$table}` WHERE id IN ($id_list)");
                    
                    if ($delete_result !== false) {
                        $deleted += count($delete_ids);
                    }
                }
            }
            
            $offset += $batch_size;
            $iteration++;
            
            if ($iteration >= $max_iterations) {
                break;
            }
            
            usleep(50000);
            
        } while (count($duplicates_batch) === $batch_size);
        
        if ($deleted > 0) {
            $this->clear_all_caches();
        }
        
        return $deleted;
    }
    
    private function prepare_factors_string($factors) {
        if (is_array($factors)) {
            $factors = array_filter(array_unique($factors));
            return implode(', ', array_map('sanitize_text_field', $factors));
        } elseif (is_string($factors)) {
            return sanitize_text_field($factors);
        } else {
            return '';
        }
    }
    
    private function merge_factors($existing, $new) {
        if (empty($existing)) {
            return $this->prepare_factors_string($new);
        }
        
        if (empty($new)) {
            return $existing;
        }
        
        $existing_array = [];
        if (is_string($existing)) {
            $existing_array = array_filter(array_map('trim', explode(',', $existing)));
        } elseif (is_array($existing)) {
            $existing_array = $existing;
        }
        
        $new_array = [];
        if (is_string($new)) {
            $new_array = array_filter(array_map('trim', explode(',', $new)));
        } elseif (is_array($new)) {
            $new_array = $new;
        }
        
        $merged = array_merge($existing_array, $new_array);
        $merged = array_filter(array_unique($merged));
        
        return implode(', ', $merged);
    }
    
    public function is_ip_blocked($ip) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        $cache_key = self::CACHE_PREFIX_BLOCKED . 'ip_' . md5($ip);
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($ip) {
            $table = $this->get_table_name(self::BLOCKLIST_TABLE);
            if (!$table) {
                return false;
            }
            
            if ($this->is_ip_permanently_blocked($ip)) {
                return true;
            }
            
            $query = $this->wpdb->prepare(
                "SELECT 1 FROM `{$table}` WHERE ip = %s AND (expires IS NULL OR expires > UTC_TIMESTAMP()) LIMIT 1",
                $ip
            );
            
            $result = $this->wpdb->get_var($query);
            return !empty($result);
        }, self::CACHE_TTL_MEDIUM);
    }
    
    /**
     * Bulk check if multiple IPs are blocked
     *
     * @since 4.8.2
     * @param array $ips Array of IP addresses to check
     * @return array Associative array of IP => blocked status
     */
    public function are_ips_blocked(array $ips): array {
        if (empty($ips)) {
            return [];
        }
        
        $results = [];
        $uncached_ips = [];
        
        foreach ($ips as $ip) {
            if (!$this->validate_ip_for_query($ip)) {
                $results[$ip] = false;
                continue;
            }
            
            $cache_key = self::CACHE_PREFIX_BLOCKED . 'ip_' . md5($ip);
            $cached = wp_cache_get($cache_key, self::CACHE_GROUP);
            
            if (false !== $cached) {
                $results[$ip] = $cached;
            } else {
                $uncached_ips[] = $ip;
            }
        }
        
        if (empty($uncached_ips)) {
            return $results;
        }
        
        $table = $this->get_table_name(self::BLOCKLIST_TABLE);
        if (!$table) {
            foreach ($uncached_ips as $ip) {
                $results[$ip] = false;
            }
            return $results;
        }
        
        $placeholders = implode(',', array_fill(0, count($uncached_ips), '%s'));
        
        $query = $this->wpdb->prepare(
            "SELECT ip FROM `{$table}` 
             WHERE ip IN ({$placeholders}) 
             AND (expires IS NULL OR expires > UTC_TIMESTAMP())",
            $uncached_ips
        );
        
        $blocked_ips = $this->wpdb->get_col($query);
        $blocked_ips = array_flip($blocked_ips);
        
        foreach ($uncached_ips as $ip) {
            $is_blocked = isset($blocked_ips[$ip]);
            $cache_key = self::CACHE_PREFIX_BLOCKED . 'ip_' . md5($ip);
            
            wp_cache_set($cache_key, $is_blocked, self::CACHE_GROUP, self::CACHE_TTL_MEDIUM);
            $results[$ip] = $is_blocked;
        }
        
        return $results;
    }
    
    /**
     * Get recent logs for multiple IPs
     *
     * @since 4.8.2
     * @param array $ips Array of IP addresses
     * @param int $limit Maximum number of logs to return
     * @return array Log entries
     */
    public function get_logs_for_ips(array $ips, $limit = 10): array {
        if (empty($ips)) {
            return [];
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return [];
        }
        
        $placeholders = implode(',', array_fill(0, count($ips), '%s'));
        
        $query = $this->wpdb->prepare(
            "SELECT * FROM `{$table}` 
             WHERE ip IN ({$placeholders})
             AND is_aggregated = 0
             ORDER BY created_at DESC 
             LIMIT %d",
            array_merge($ips, [$limit])
        );
        
        return $this->wpdb->get_results($query) ?: [];
    }
    
    public function is_ip_permanently_blocked($ip) {
        $permanent_blocklist = $this->config->get_string('permanent_blocklist', '');
        if (empty($permanent_blocklist)) {
            return false;
        }
        
        $cache_key = self::CACHE_PREFIX_BLOCKED . 'permanent_' . md5($ip);
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($ip, $permanent_blocklist) {
            $blocked_ips = array_filter(array_map('trim', explode("\n", $permanent_blocklist)));
            
            foreach ($blocked_ips as $blocked_ip) {
                if (strpos($blocked_ip, '#') === 0) {
                    continue;
                }
                
                if (strpos($blocked_ip, '/') !== false) {
                    if ($this->ip_in_cidr($ip, $blocked_ip)) {
                        return true;
                    }
                } elseif ($ip === $blocked_ip) {
                    return true;
                }
            }
            
            return false;
        }, self::CACHE_TTL_LONG);
    }
    
    private function ip_in_cidr($ip, $cidr) {
        if (empty($ip) || empty($cidr)) {
            return false;
        }
        
        if (strpos($ip, ':') !== false) {
            return $this->ipv6_in_cidr($ip, $cidr);
        }
        
        if (strpos($cidr, '/') === false) {
            return $ip === $cidr;
        }
        
        list($subnet, $bits) = explode('/', $cidr);
        
        if (!filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4) || 
            !filter_var($subnet, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
            return false;
        }
        
        $ip_long = ip2long($ip);
        $subnet_long = ip2long($subnet);
        
        if ($ip_long === false || $subnet_long === false) {
            return false;
        }
        
        $bits = absint($bits);
        if ($bits < 0 || $bits > 32) {
            return false;
        }
        
        $mask = -1 << (32 - $bits);
        $subnet_long &= $mask;
        return ($ip_long & $mask) == $subnet_long;
    }
    
    private function ipv6_in_cidr($ip, $cidr) {
        if (empty($ip) || empty($cidr)) {
            return false;
        }
        
        if (!function_exists('inet_pton')) {
            return false;
        }
        
        if (strpos($cidr, '/') === false) {
            return $ip === $cidr;
        }
        
        list($subnet, $bits) = explode('/', $cidr);
        
        $ip_bin = @inet_pton($ip);
        $subnet_bin = @inet_pton($subnet);
        
        if ($ip_bin === false || $subnet_bin === false) {
            return false;
        }
        
        $bits = absint($bits);
        if ($bits < 0 || $bits > 128) {
            return false;
        }
        
        $bytes_to_check = (int) ($bits / 8);
        $bits_remaining = $bits % 8;
        
        for ($i = 0; $i < $bytes_to_check; $i++) {
            if ($ip_bin[$i] !== $subnet_bin[$i]) {
                return false;
            }
        }
        
        if ($bits_remaining > 0) {
            $mask_byte = ~((1 << (8 - $bits_remaining)) - 1) & 0xFF;
            if ((ord($ip_bin[$bytes_to_check]) & $mask_byte) !== (ord($subnet_bin[$bytes_to_check]) & $mask_byte)) {
                return false;
            }
        }
        
        return true;
    }
    
    public function calculate_smart_duration($ip, $threat_score, $reason = '') {
        $base_duration = $this->config->get_int('block_duration', 24);
        
        if ($threat_score >= 9) {
            $duration = $base_duration * 3;
        } elseif ($threat_score >= 7) {
            $duration = $base_duration * 2;
        } elseif ($threat_score >= 5) {
            $duration = $base_duration;
        } else {
            $duration = $base_duration / 2;
        }
        
        $repeat_multiplier = $this->get_repeat_offense_multiplier($ip);
        $duration *= $repeat_multiplier;
        
        $type_multiplier = $this->get_attack_type_multiplier($reason);
        $duration *= $type_multiplier;
        
        if (strpos(strtolower($reason), 'crawler impersonation') !== false) {
            $duration *= 1.5;
        }
        
        $duration = min($duration, 720);
        $duration = max($duration, 1);
        
        return (int) round($duration);
    }
    
    private function get_repeat_offense_multiplier($ip) {
        $cache_key = self::CACHE_PREFIX_BLOCKED . 'repeat_' . md5($ip);
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($ip) {
            $table = $this->get_table_name(self::BLOCKLIST_TABLE);
            if (!$table) {
                return 1.0;
            }
            
            $count = (int) $this->wpdb->get_var($this->wpdb->prepare(
                "SELECT COUNT(*) FROM `{$table}` 
                WHERE ip = %s 
                AND (expires IS NULL OR expires > UTC_TIMESTAMP())
                AND created_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)",
                $ip
            ));
            
            if ($count >= 3) {
                return 3.0;
            } elseif ($count >= 2) {
                return 2.0;
            }
            
            return 1.0;
        }, self::CACHE_TTL_MEDIUM);
    }
    
    private function get_attack_type_multiplier($reason) {
        $multipliers = [
            'honeypot' => 1.5,
            '404_attack' => 2.0,
            'session_hijack' => 3.0,
            'brute_force' => 2.5,
            'scraping' => 1.2,
            'spam' => 1.1,
            'waf' => 2.0,
            'sql_injection' => 2.5,
            'xss' => 2.3,
            'file_inclusion' => 2.8,
            'crawler_impersonation' => 1.5,
            'permanent_block' => 0,
        ];
        
        $reason = strtolower(sanitize_text_field($reason));
        foreach ($multipliers as $pattern => $multiplier) {
            if (strpos($reason, $pattern) !== false) {
                return $multiplier;
            }
        }
        
        return 1.0;
    }
    
    public function is_rate_limited($ip) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return false;
        }
        
        $limit = $this->config->get_int('rate_limit', 100);
        $window = 60;
        
        $cache_key = 'botfend_rate_limit_sliding_' . md5($ip);
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($table, $ip, $limit, $window) {
            $window_start = gmdate('Y-m-d H:i:s', time() - $window);
            
            $query = $this->wpdb->prepare(
                "SELECT COUNT(*) as request_count 
                 FROM `{$table}` 
                 WHERE ip = %s 
                 AND is_aggregated = 0
                 AND created_at > %s",
                $ip,
                $window_start
            );
            
            $count = (int) $this->wpdb->get_var($query);
            return $count >= $limit;
        }, 5);
    }
    
    public function is_404_abuser($ip) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return false;
        }
        
        $max_404s = $this->config->get_int('max_404s', 10);
        
        if ($max_404s <= 0) {
            return false;
        }

        $cache_key = 'botfend_404_abuser_' . md5($ip);
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($table, $ip, $max_404s) {
            $query = $this->wpdb->prepare(
                "SELECT COUNT(*) as abuse_count 
                 FROM `{$table}` 
                 WHERE ip = %s 
                 AND action = '404_abuse' 
                 AND is_aggregated = 0
                 AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 MINUTE)
                 LIMIT %d",
                $ip,
                $max_404s + 1
            );
            
            $count = (int) $this->wpdb->get_var($query);
            return $count > $max_404s;
        }, self::CACHE_TTL_SHORT);
    }
    
    public function is_tor_exit_node($ip) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        $cache_key = self::CACHE_PREFIX_TOR . 'node_' . md5($ip);
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($ip) {
            $table = $this->get_table_name(self::TOR_NODES_TABLE);
            if (!$table) {
                return false;
            }
            
            $query = $this->wpdb->prepare(
                "SELECT 1 FROM `{$table}` WHERE ip = %s OR exit_address = %s LIMIT 1",
                $ip,
                $ip
            );
            
            $result = $this->wpdb->get_var($query);
            return !empty($result);
        }, self::CACHE_TTL_LONG);
    }
    
    public function get_blocklist($filters = [], $limit = 100) {
        $cache_key = self::CACHE_PREFIX_BLOCKED . 'list_' . $this->generate_cache_key($filters) . '_' . $limit;
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($filters, $limit) {
            $table = $this->get_table_name(self::BLOCKLIST_TABLE);
            if (!$table) {
                return [];
            }
            
            $where = ['(expires IS NULL OR expires > UTC_TIMESTAMP())'];
            $params = [];
            
            if (!empty($filters['ip'])) {
                $where[] = 'ip LIKE %s';
                $params[] = '%' . $this->wpdb->esc_like(sanitize_text_field($filters['ip'])) . '%';
            }
            
            if (!empty($filters['type'])) {
                $where[] = 'type = %s';
                $params[] = sanitize_text_field($filters['type']);
            }
            
            if (!empty($filters['country'])) {
                $where[] = 'country = %s';
                $params[] = sanitize_text_field($filters['country']);
            }
            
            if (!empty($filters['reason'])) {
                $where[] = 'reason LIKE %s';
                $params[] = '%' . $this->wpdb->esc_like(sanitize_text_field($filters['reason'])) . '%';
            }
            
            $where_clause = implode(' AND ', $where);
            
            $results = $this->wpdb->get_results($this->wpdb->prepare(
                "SELECT * FROM `{$table}` WHERE {$where_clause} ORDER BY created_at DESC LIMIT %d",
                array_merge($params, [absint($limit)])
            )) ?: [];
            
            foreach ($results as $result) {
                $result->ip = sanitize_text_field($result->ip);
                $result->type = sanitize_text_field($result->type);
                $result->reason = sanitize_textarea_field($result->reason);
                $result->country = sanitize_text_field($result->country);
                $result->score = absint($result->score);
            }
            
            return $results;
        }, self::CACHE_TTL_SHORT);
    }
    
    public function get_distinct_actions() {
        $cache_key = self::CACHE_PREFIX_LOGS . 'distinct_actions';
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() {
            $table = $this->get_table_name(self::LOG_TABLE);
            if (!$table) {
                return [];
            }
            
            $actions = $this->wpdb->get_col("SELECT DISTINCT action FROM `{$table}` ORDER BY action ASC") ?: [];
            return array_map('sanitize_text_field', $actions);
        }, self::CACHE_TTL_LONG);
    }
    
    /**
     * Get blocklist statistics
     *
     * @since 4.8.2
     * @return array Blocklist statistics
     */
    public function get_blocklist_stats(): array {
        $cache_key = self::CACHE_PREFIX_STATS . 'blocklist';
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() {
            $table = $this->get_table_name(self::BLOCKLIST_TABLE);
            if (!$table) {
                return [
                    'total_blocks' => 0,
                    'permanent_blocks' => 0,
                    'active_temporary' => 0,
                    'manual_blocks' => 0,
                    'auto_blocks' => 0,
                    'countries_blocked' => 0,
                ];
            }
            
            $stats = $this->wpdb->get_row("
                SELECT 
                    COUNT(*) as total_blocks,
                    COUNT(CASE WHEN expires IS NULL THEN 1 END) as permanent_blocks,
                    COUNT(CASE WHEN expires > UTC_TIMESTAMP() THEN 1 END) as active_temporary,
                    COUNT(CASE WHEN type = 'manual' OR type = 'permanent_manual' THEN 1 END) as manual_blocks,
                    COUNT(CASE WHEN type = 'auto_block' OR type = 'iphub_block' OR type = 'abuseipdb_block' THEN 1 END) as auto_blocks,
                    COUNT(DISTINCT country) as countries_blocked
                FROM `{$table}`
                WHERE expires IS NULL OR expires > UTC_TIMESTAMP()
            ");
            
            return [
                'total_blocks' => (int) ($stats->total_blocks ?? 0),
                'permanent_blocks' => (int) ($stats->permanent_blocks ?? 0),
                'active_temporary' => (int) ($stats->active_temporary ?? 0),
                'manual_blocks' => (int) ($stats->manual_blocks ?? 0),
                'auto_blocks' => (int) ($stats->auto_blocks ?? 0),
                'countries_blocked' => (int) ($stats->countries_blocked ?? 0),
            ];
        }, self::CACHE_TTL_SHORT);
    }
    
    public function block_ip($ip, $type, $reason, $score, $duration_hours, $country = null) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        if ($this->is_ip_permanently_blocked($ip)) {
            $type = 'permanent_block';
            $duration_hours = 0;
        }
        
        $table = $this->get_table_name(self::BLOCKLIST_TABLE);
        if (!$table) {
            return false;
        }
        
        if ($this->config->get_bool('enable_smart_duration', true) && $type !== 'permanent_block') {
            $final_duration = $this->calculate_smart_duration($ip, $score, $reason);
        } else {
            $final_duration = absint($duration_hours);
        }
        
        $expires = $final_duration > 0 ? 
            gmdate('Y-m-d H:i:s', time() + ($final_duration * 3600)) : 
            null;
        
        $existing = $this->wpdb->get_row($this->wpdb->prepare(
            "SELECT id, expires FROM `{$table}` WHERE ip = %s AND (expires IS NULL OR expires > UTC_TIMESTAMP())",
            $ip
        ));
        
        if ($existing) {
            $result = $this->wpdb->update(
                $table,
                [
                    'type' => sanitize_text_field($type),
                    'reason' => sanitize_textarea_field($reason),
                    'score' => absint($score),
                    'country' => sanitize_text_field($country),
                    'expires' => $expires,
                    'created_at' => current_time('mysql', true)
                ],
                ['id' => $existing->id],
                ['%s', '%s', '%s', '%d', '%s', '%s', '%s'],
                ['%d']
            );
        } else {
            $result = $this->wpdb->insert(
                $table,
                [
                    'ip' => sanitize_text_field($ip),
                    'type' => sanitize_text_field($type),
                    'reason' => sanitize_textarea_field($reason),
                    'score' => absint($score),
                    'country' => sanitize_text_field($country),
                    'expires' => $expires,
                    'created_at' => current_time('mysql', true)
                ],
                ['%s', '%s', '%s', '%d', '%s', '%s', '%s']
            );
        }
        
        if ($result === false) {
            error_log(sprintf(
                'BotFend: Database error blocking IP: %s. Error: %s',
                $ip,
                $this->wpdb->last_error ?: 'unknown'
            ));
            return false;
        }
        
        $this->clear_ip_caches($ip);
        $this->clear_all_caches();
        $this->invalidate_stats_cache();
        
        do_action('botfend_ip_blocked', $ip, $type, $score, $reason);
        
        return true;
    }

    public function unblock_ip($ip) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        $table = $this->get_table_name(self::BLOCKLIST_TABLE);
        if (!$table) {
            return false;
        }
        
        $result = $this->wpdb->delete($table, ['ip' => sanitize_text_field($ip)], ['%s']);
        
        if ($result === false) {
            error_log(sprintf(
                'BotFend: Database error unblocking IP: %s. Error: %s',
                $ip,
                $this->wpdb->last_error ?: 'unknown'
            ));
            return false;
        }
        
        $this->clear_ip_caches($ip);
        $this->clear_all_caches();
        $this->invalidate_stats_cache();
        
        do_action('botfend_ip_unblocked', $ip);
        
        return true;
    }
    
    // ============================================
    // UNBLOCK REQUEST METHODS
    // ============================================
    
    public function submit_unblock_request($ip, $email, $reason, $request_whitelist = false) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        if (!is_email($email)) {
            return false;
        }
        
        $table = $this->get_table_name(self::UNBLOCK_REQUESTS_TABLE);
        if (!$table) {
            return false;
        }
        
        $existing = (int) $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM `{$table}` WHERE ip = %s AND status = 'pending'",
            $ip
        ));
        
        if ($existing > 0) {
            return false;
        }
        
        if ($request_whitelist) {
            $reason .= "\n\n[Whitelist requested: Yes]";
        }
        
        $result = $this->wpdb->insert(
            $table,
            [
                'ip' => sanitize_text_field($ip),
                'email' => sanitize_email($email),
                'reason' => sanitize_textarea_field($reason),
                'status' => 'pending',
                'request_whitelist' => $request_whitelist ? 1 : 0,
                'created_at' => current_time('mysql', true),
                'updated_at' => current_time('mysql', true),
            ],
            ['%s', '%s', '%s', '%s', '%d', '%s', '%s']
        );
        
        if ($result === false) {
            error_log(sprintf(
                'BotFend: Database error submitting unblock request for IP: %s. Error: %s',
                $ip,
                $this->wpdb->last_error ?: 'unknown'
            ));
            return false;
        }
        
        $this->invalidate_stats_cache();
        
        return true;
    }
    
    public function get_unblock_requests($filters = [], $limit = 50) {
        $cache_key = 'botfend_unblock_requests_' . $this->generate_cache_key($filters) . '_' . $limit;
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($filters, $limit) {
            $table = $this->get_table_name(self::UNBLOCK_REQUESTS_TABLE);
            if (!$table) {
                return [];
            }
            
            $where = ['1=1'];
            $params = [];
            
            if (!empty($filters['ip'])) {
                $where[] = 'ip LIKE %s';
                $params[] = '%' . $this->wpdb->esc_like(sanitize_text_field($filters['ip'])) . '%';
            }
            
            if (!empty($filters['email'])) {
                $where[] = 'email LIKE %s';
                $params[] = '%' . $this->wpdb->esc_like(sanitize_text_field($filters['email'])) . '%';
            }
            
            if (!empty($filters['status'])) {
                $where[] = 'status = %s';
                $params[] = sanitize_text_field($filters['status']);
            }
            
            if (!empty($filters['date_from'])) {
                $where[] = 'created_at >= %s';
                $params[] = sanitize_text_field($filters['date_from']) . ' 00:00:00';
            }
            
            if (!empty($filters['date_to'])) {
                $where[] = 'created_at <= %s';
                $params[] = sanitize_text_field($filters['date_to']) . ' 23:59:59';
            }
            
            $where_clause = implode(' AND ', $where);
            
            $results = $this->wpdb->get_results($this->wpdb->prepare(
                "SELECT * FROM `{$table}` WHERE {$where_clause} ORDER BY created_at DESC LIMIT %d",
                array_merge($params, [absint($limit)])
            )) ?: [];
            
            foreach ($results as $result) {
                $result->ip = sanitize_text_field($result->ip);
                $result->email = sanitize_email($result->email);
                $result->reason = sanitize_textarea_field($result->reason);
                $result->status = sanitize_text_field($result->status);
                $result->admin_notes = sanitize_textarea_field($result->admin_notes);
                $result->admin_action_by = sanitize_text_field($result->admin_action_by);
                $result->request_whitelist = (bool) ($result->request_whitelist ?? false);
            }
            
            return $results;
        }, self::CACHE_TTL_SHORT);
    }
    
    public function update_unblock_request($id, $data) {
        $table = $this->get_table_name(self::UNBLOCK_REQUESTS_TABLE);
        if (!$table) {
            return false;
        }
        
        $sanitized_data = [];
        foreach ($data as $key => $value) {
            if ($key === 'status') {
                $sanitized_data[$key] = sanitize_text_field($value);
            } elseif ($key === 'admin_notes') {
                $sanitized_data[$key] = sanitize_textarea_field($value);
            } elseif ($key === 'admin_action_by') {
                $sanitized_data[$key] = sanitize_text_field($value);
            } else {
                $sanitized_data[$key] = sanitize_text_field($value);
            }
        }
        
        $sanitized_data['updated_at'] = current_time('mysql', true);
        if (isset($sanitized_data['status']) && $sanitized_data['status'] !== 'pending') {
            $current_user = wp_get_current_user();
            $sanitized_data['admin_action_by'] = sanitize_text_field($current_user->user_login);
        }
        
        $result = $this->wpdb->update(
            $table,
            $sanitized_data,
            ['id' => absint($id)],
            array_fill(0, count($sanitized_data), '%s'),
            ['%d']
        );
        
        if ($result === false) {
            error_log(sprintf(
                'BotFend: Database error updating unblock request ID: %d. Error: %s',
                $id,
                $this->wpdb->last_error ?: 'unknown'
            ));
            return false;
        }
        
        if (isset($sanitized_data['status']) && $sanitized_data['status'] === 'approved') {
            $request = $this->get_unblock_request($id);
            if ($request) {
                $this->unblock_ip($request->ip);
            }
        }
        
        $this->invalidate_stats_cache();
        
        return true;
    }
    
    public function get_unblock_request($id) {
        $cache_key = 'botfend_unblock_request_' . $id;
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($id) {
            $table = $this->get_table_name(self::UNBLOCK_REQUESTS_TABLE);
            if (!$table) {
                return null;
            }
            
            $result = $this->wpdb->get_row($this->wpdb->prepare(
                "SELECT * FROM `{$table}` WHERE id = %d",
                absint($id)
            ));
            
            if ($result) {
                $result->ip = sanitize_text_field($result->ip);
                $result->email = sanitize_email($result->email);
                $result->reason = sanitize_textarea_field($result->reason);
                $result->status = sanitize_text_field($result->status);
                $result->admin_notes = sanitize_textarea_field($result->admin_notes);
                $result->admin_action_by = sanitize_text_field($result->admin_action_by);
            }
            
            return $result;
        }, self::CACHE_TTL_MEDIUM);
    }
    
    public function delete_unblock_request($id) {
        $table = $this->get_table_name(self::UNBLOCK_REQUESTS_TABLE);
        if (!$table) {
            return false;
        }
        
        $result = $this->wpdb->delete($table, ['id' => absint($id)], ['%d']) !== false;
        
        if ($result === false) {
            error_log(sprintf(
                'BotFend: Database error deleting unblock request ID: %d. Error: %s',
                $id,
                $this->wpdb->last_error ?: 'unknown'
            ));
            return false;
        }
        
        $this->invalidate_stats_cache();
        
        return true;
    }
    
    public function count_pending_unblock_requests() {
        $cache_key = 'botfend_pending_unblock_count';
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() {
            $table = $this->get_table_name(self::UNBLOCK_REQUESTS_TABLE);
            if (!$table) {
                return 0;
            }
            
            return (int) $this->wpdb->get_var("SELECT COUNT(*) FROM `{$table}` WHERE status = 'pending'");
        }, self::CACHE_TTL_SHORT);
    }
    
    // ============================================
    // HELPER METHODS
    // ============================================
    
    public function get_request_count($ip, $seconds = 60) {
        if (empty($ip) || !$this->validate_ip_for_query($ip)) {
            return 0;
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return 0;
        }
        
        $query = $this->wpdb->prepare(
            "SELECT COUNT(*) FROM `{$table}` 
             WHERE ip = %s 
             AND is_aggregated = 0
             AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d SECOND)",
            sanitize_text_field($ip),
            absint($seconds)
        );
        
        return (int) $this->wpdb->get_var($query);
    }
    
    public function get_recent_request_count($minutes = 60) {
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return 0;
        }
        
        $query = $this->wpdb->prepare(
            "SELECT COUNT(*) as count 
             FROM `{$table}` 
             WHERE is_aggregated = 0
             AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d MINUTE)",
            absint($minutes)
        );
        
        return (int) $this->wpdb->get_var($query);
    }
    
    public function get_country_historical_ratio($country) {
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return 0;
        }
        
        $total_requests = (int) $this->wpdb->get_var(
            "SELECT COUNT(*) FROM `{$table}` WHERE created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)"
        );
        
        if ($total_requests == 0) {
            return 0;
        }
        
        $country_requests = (int) $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM `{$table}` 
             WHERE country = %s 
             AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)",
            sanitize_text_field($country)
        ));
        
        return $country_requests / max($total_requests, 1);
    }
    
    public function count_user_agent_changes($ip, $seconds = 300) {
        if (empty($ip) || !$this->validate_ip_for_query($ip)) {
            return 0;
        }
        
        $table = $this->get_table_name(self::LOG_TABLE);
        if (!$table) {
            return 0;
        }
        
        $query = $this->wpdb->prepare(
            "SELECT COUNT(DISTINCT user_agent) 
             FROM `{$table}` 
             WHERE ip = %s 
             AND is_aggregated = 0
             AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d SECOND)
             AND user_agent IS NOT NULL 
             AND user_agent != ''",
            sanitize_text_field($ip),
            absint($seconds)
        );
        
        return (int) $this->wpdb->get_var($query);
    }
    
    public function get_dashboard_stats() {
        $cache_key = self::CACHE_PREFIX_STATS . 'dashboard';
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() {
            $logs_table = $this->get_table_name(self::LOG_TABLE);
            $blocklist_table = $this->get_table_name(self::BLOCKLIST_TABLE);
            $unblock_requests_table = $this->get_table_name(self::UNBLOCK_REQUESTS_TABLE);
            $tor_nodes_table = $this->get_table_name(self::TOR_NODES_TABLE);
            $aggregates_table = $this->get_table_name(self::LOG_AGGREGATES_TABLE);
            
            if (!$logs_table || !$blocklist_table || !$unblock_requests_table || !$tor_nodes_table || !$aggregates_table) {
                return $this->get_default_stats();
            }
            
            $total_requests_24h = (int) $this->wpdb->get_var(
                "SELECT COUNT(*) FROM `{$logs_table}` WHERE is_aggregated = 0 AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)"
            );
            
            $blocked_requests_24h = (int) $this->wpdb->get_var(
                "SELECT COUNT(*) FROM `{$logs_table}` WHERE action = 'blocked' AND is_aggregated = 0 AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)"
            );
            
            $aggregated_stats = $this->wpdb->get_row(
                "SELECT 
                    SUM(total_requests) as total_requests_7d,
                    SUM(blocked_requests) as blocked_requests_7d,
                    SUM(challenged_requests) as challenged_requests_7d,
                    AVG(average_score) as avg_score_7d
                FROM `{$aggregates_table}` 
                WHERE period_type IN ('hourly', 'daily') 
                AND period_start >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL 7 DAY)"
            );
            
            $active_blocks = (int) $this->wpdb->get_var(
                "SELECT COUNT(*) FROM `{$blocklist_table}` WHERE expires IS NULL OR expires > UTC_TIMESTAMP()"
            );
            
            $pending_unblock_requests = (int) $this->wpdb->get_var(
                "SELECT COUNT(*) FROM `{$unblock_requests_table}` WHERE status = 'pending'"
            );
            
            $tor_nodes_count = (int) $this->wpdb->get_var("SELECT COUNT(*) FROM `{$tor_nodes_table}`");
            
            $top_offenders = $this->wpdb->get_results(
                "SELECT ip, COUNT(*) as count FROM `{$logs_table}` WHERE score >= 6 AND is_aggregated = 0 AND created_at > DATE_SUB(UTC_TIMESTAMP(), INTERVAL 7 DAY) GROUP BY ip ORDER BY count DESC LIMIT 10"
            ) ?: [];
            
            $permanent_blocklist = $this->config->get_string('permanent_blocklist', '');
            $permanent_blocks_count = 0;
            if (!empty($permanent_blocklist)) {
                $blocks = array_filter(array_map('trim', explode("\n", $permanent_blocklist)));
                foreach ($blocks as $block) {
                    if (strpos($block, '#') !== 0 && !empty(trim($block))) {
                        $permanent_blocks_count++;
                    }
                }
            }
            
            $smart_agg_enabled = $this->config->get_bool('enable_smart_aggregation', true);
            $detailed_logs_count = (int) $this->wpdb->get_var("SELECT COUNT(*) FROM `{$logs_table}` WHERE is_aggregated = 0");
            $aggregated_logs_count = (int) $this->wpdb->get_var("SELECT COUNT(*) FROM `{$logs_table}` WHERE is_aggregated = 1");
            $aggregates_count = (int) $this->wpdb->get_var("SELECT COUNT(*) FROM `{$aggregates_table}`");
            
            return [
                'total_requests_24h' => $total_requests_24h,
                'blocked_requests_24h' => $blocked_requests_24h,
                'total_requests_7d' => (int) ($aggregated_stats->total_requests_7d ?? 0) + $total_requests_24h,
                'blocked_requests_7d' => (int) ($aggregated_stats->blocked_requests_7d ?? 0) + $blocked_requests_24h,
                'challenged_requests_7d' => (int) ($aggregated_stats->challenged_requests_7d ?? 0),
                'avg_score_7d' => round((float) ($aggregated_stats->avg_score_7d ?? 0), 2),
                'active_blocks' => $active_blocks,
                'permanent_blocks' => $permanent_blocks_count,
                'pending_unblock_requests' => $pending_unblock_requests,
                'tor_nodes_count' => $tor_nodes_count,
                'top_offenders' => $top_offenders,
                'smart_aggregation_enabled' => $smart_agg_enabled,
                'detailed_logs_count' => $detailed_logs_count,
                'aggregated_logs_count' => $aggregated_logs_count,
                'aggregates_count' => $aggregates_count,
                'max_detailed_logs' => $this->config->get_int('max_detailed_logs', self::MAX_DETAILED_LOGS),
                'log_sampling_rate' => $this->config->get_float('log_sampling_rate', self::LOG_SAMPLING_RATE) * 100,
            ];
        }, self::CACHE_TTL_SHORT);
    }
    
    private function get_default_stats() {
        return [
            'total_requests_24h' => 0,
            'blocked_requests_24h' => 0,
            'total_requests_7d' => 0,
            'blocked_requests_7d' => 0,
            'challenged_requests_7d' => 0,
            'avg_score_7d' => 0,
            'active_blocks' => 0,
            'permanent_blocks' => 0,
            'pending_unblock_requests' => 0,
            'tor_nodes_count' => 0,
            'top_offenders' => [],
            'smart_aggregation_enabled' => $this->config->get_bool('enable_smart_aggregation', true),
            'detailed_logs_count' => 0,
            'aggregated_logs_count' => 0,
            'aggregates_count' => 0,
            'max_detailed_logs' => self::MAX_DETAILED_LOGS,
            'log_sampling_rate' => self::LOG_SAMPLING_RATE * 100,
        ];
    }
    
    public function get_last_request_uri($ip, $before_time = null) {
        if (!$this->validate_ip_for_query($ip)) {
            return null;
        }
        
        $cache_key = self::CACHE_PREFIX_LOGS . 'last_uri_' . md5($ip . '_' . $before_time);
        
        return $this->get_cached_result($cache_key, self::CACHE_GROUP, function() use ($ip, $before_time) {
            $table = $this->get_table_name(self::LOG_TABLE);
            if (!$table) {
                return null;
            }
            
            $where_clause = "ip = %s AND is_aggregated = 0";
            $params = [sanitize_text_field($ip)];
            
            if ($before_time) {
                $where_clause .= " AND created_at <= DATE_ADD(%s, INTERVAL 5 MINUTE)";
                $params[] = sanitize_text_field($before_time);
            }
            
            $query = $this->wpdb->prepare(
                "SELECT request_uri FROM `{$table}` 
                 WHERE {$where_clause} 
                 AND request_uri IS NOT NULL 
                 AND request_uri != ''
                 ORDER BY created_at DESC 
                 LIMIT 1",
                $params
            );
            
            $result = $this->wpdb->get_var($query);
            return $result ? sanitize_text_field($result) : null;
        }, self::CACHE_TTL_MEDIUM);
    }
 
    /**
     * Check API rate limit with atomic operations and timeout handling
     *
     * @since 4.8.3
     * @param string $endpoint API endpoint identifier
     * @param string $ip Client IP address
     * @param int $max_requests Maximum allowed requests
     * @param int $time_window Time window in seconds
     * @param int $timeout Maximum seconds to wait for atomic operation
     * @return bool True if rate limited, false if allowed
     */
    public function is_rate_limited_api($endpoint, $ip, $max_requests = 10, $time_window = 60, $timeout = 5) {
        if (!$this->validate_ip_for_query($ip)) {
            return false;
        }
        
        $cache_key = 'botfend_api_rate_' . md5($endpoint . '_' . $ip);
        
        if (wp_cache_add($cache_key, 1, self::CACHE_GROUP, $time_window)) {
            $attempts = 1;
        } else {
            $attempts = false;
            $start_time = microtime(true);
            
            while (false === $attempts && (microtime(true) - $start_time) < $timeout) {
                $attempts = wp_cache_incr($cache_key, 1, self::CACHE_GROUP);
                if (false === $attempts) {
                    usleep(10000);
                }
            }
            
            if (false === $attempts) {
                $attempts = absint(get_transient($cache_key) ?: 0);
                if ($attempts >= $max_requests) {
                    return true;
                }
                $attempts++;
                set_transient($cache_key, $attempts, $time_window);
                return false;
            }
        }
        
        return $attempts > $max_requests;
    }
    
    public function perform_maintenance() {
        $logs_table = $this->get_table_name(self::LOG_TABLE);
        $aggregates_table = $this->get_table_name(self::LOG_AGGREGATES_TABLE);
        $blocklist_table = $this->get_table_name(self::BLOCKLIST_TABLE);
        $unblock_requests_table = $this->get_table_name(self::UNBLOCK_REQUESTS_TABLE);
        $tor_nodes_table = $this->get_table_name(self::TOR_NODES_TABLE);
        
        if (!$logs_table || !$aggregates_table || !$blocklist_table || !$unblock_requests_table || !$tor_nodes_table) {
            return 0;
        }
        
        $chunk_size = 1000;
        $deleted = 0;
        $max_iterations = 100;
        
        $keep_aggregates_days = $this->config->get_int('keep_aggregates_days', self::KEEP_AGGREGATES_DAYS);
        if ($keep_aggregates_days > 0) {
            $iteration = 0;
            do {
                $result = $this->wpdb->query($this->wpdb->prepare(
                    "DELETE FROM `{$logs_table}` 
                     WHERE is_aggregated = 1 
                     AND aggregated_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY) 
                     LIMIT %d",
                    $keep_aggregates_days,
                    $chunk_size
                ));
                
                if ($result === false) {
                    error_log(sprintf(
                        'BotFend: Database error deleting old aggregated logs. Error: %s',
                        $this->wpdb->last_error ?: 'unknown'
                    ));
                    break;
                }
                
                $deleted += (int) $result;
                
                if ($result > 0) {
                    usleep(100000);
                }
                
                $iteration++;
                if ($iteration >= $max_iterations) {
                    break;
                }
            } while ($result > 0);
        }
        
        if ($keep_aggregates_days > 0) {
            $result = $this->wpdb->query($this->wpdb->prepare(
                "DELETE FROM `{$aggregates_table}` 
                 WHERE period_start < DATE_SUB(UTC_TIMESTAMP(), INTERVAL %d DAY)",
                $keep_aggregates_days
            ));
            
            if ($result === false) {
                error_log(sprintf(
                    'BotFend: Database error deleting old aggregates. Error: %s',
                    $this->wpdb->last_error ?: 'unknown'
                ));
            }
        }
        
        $result = $this->wpdb->query("DELETE FROM `{$blocklist_table}` WHERE expires IS NOT NULL AND expires < UTC_TIMESTAMP() AND type != 'permanent_block'");
        if ($result === false) {
            error_log(sprintf(
                'BotFend: Database error deleting expired blocks. Error: %s',
                $this->wpdb->last_error ?: 'unknown'
            ));
        }
        
        $result = $this->wpdb->query("DELETE FROM `{$unblock_requests_table}` WHERE created_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 30 DAY)");
        if ($result === false) {
            error_log(sprintf(
                'BotFend: Database error deleting old unblock requests. Error: %s',
                $this->wpdb->last_error ?: 'unknown'
            ));
        }
        
        $result = $this->wpdb->query("DELETE FROM `{$tor_nodes_table}` WHERE last_seen < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 90 DAY)");
        if ($result === false) {
            error_log(sprintf(
                'BotFend: Database error deleting old TOR nodes. Error: %s',
                $this->wpdb->last_error ?: 'unknown'
            ));
        }
        
        $duplicates_deleted = $this->cleanup_duplicate_logs();
        $deleted += $duplicates_deleted;
        
        $aggregate_hours = $this->config->get_int('aggregate_after_hours', self::AGGREGATE_AFTER_HOURS);
        $this->check_and_aggregate_old_logs($aggregate_hours);
        
        $this->create_daily_aggregates();
        $this->clear_all_caches();
        
        return $deleted;
    }
    
    public function import_tor_exit_nodes($url = 'https://check.torproject.org/torbulkexitlist') {
        if (!$this->config->get_bool('enable_external_tor_check', false)) {
            return false;
        }
        
        $response = wp_safe_remote_get($url, [
            'timeout' => 30,
            'user-agent' => 'BotFend Anti-Bot Firewall/' . BOTFEND_VERSION,
            'sslverify' => true,
        ]);
        
        if (is_wp_error($response)) {
            error_log('BotFend: Error importing TOR exit nodes: ' . $response->get_error_message());
            return false;
        }
        
        $status_code = wp_remote_retrieve_response_code($response);
        if ($status_code !== 200) {
            error_log('BotFend: TOR exit list import failed with status: ' . $status_code);
            return false;
        }
        
        $body = wp_remote_retrieve_body($response);
        $ips = array_filter(array_map('trim', explode("\n", $body)), function($ip) {
            return filter_var($ip, FILTER_VALIDATE_IP);
        });
        
        $table = $this->get_table_name(self::TOR_NODES_TABLE);
        if (!$table) {
            return 0;
        }
        
        $imported = 0;
        foreach ($ips as $ip) {
            $ip = sanitize_text_field($ip);
            $existing = $this->wpdb->get_var($this->wpdb->prepare(
                "SELECT id FROM `{$table}` WHERE ip = %s",
                $ip
            ));
            
            if ($existing) {
                $result = $this->wpdb->update(
                    $table,
                    ['last_seen' => current_time('mysql', true)],
                    ['ip' => $ip],
                    ['%s'],
                    ['%s']
                );
            } else {
                $result = $this->wpdb->insert(
                    $table,
                    [
                        'ip' => $ip,
                        'exit_address' => $ip,
                        'last_seen' => current_time('mysql', true),
                        'created_at' => current_time('mysql', true),
                    ],
                    ['%s', '%s', '%s', '%s']
                );
            }
            
            if ($result !== false) {
                $imported++;
            }
        }
        
        update_option('botfend_tor_last_update', time());
        $this->invalidate_tor_cache();
        
        return $imported;
    }
    
    public function get_tor_exit_nodes($limit = null, $offset = 0): array {
        $table = $this->get_table_name(self::TOR_NODES_TABLE);
        if (!$table) {
            return [];
        }
        
        $query = "SELECT ip, fingerprint, nickname, published, last_seen, exit_address 
                  FROM `{$table}` 
                  ORDER BY last_seen DESC";
        
        if ($limit !== null) {
            $query .= $this->wpdb->prepare(" LIMIT %d OFFSET %d", absint($limit), absint($offset));
        }
        
        $results = $this->wpdb->get_results($query) ?: [];
        
        foreach ($results as $result) {
            $result->ip = sanitize_text_field($result->ip);
            $result->fingerprint = sanitize_text_field($result->fingerprint);
            $result->nickname = sanitize_text_field($result->nickname);
            $result->exit_address = sanitize_text_field($result->exit_address);
        }
        
        return $results;
    }
    
    public function get_tor_exit_nodes_count(): int {
        $table = $this->get_table_name(self::TOR_NODES_TABLE);
        if (!$table) {
            return 0;
        }
        
        $count = $this->wpdb->get_var("SELECT COUNT(*) FROM `{$table}`");
        
        return absint($count);
    }

    public function import_tor_exit_nodes_batch(array $ips): int {
        $table = $this->get_table_name(self::TOR_NODES_TABLE);
        if (!$table || empty($ips)) {
            return 0;
        }
        
        $imported = 0;
        $now = current_time('mysql', true);
        
        $this->wpdb->query('START TRANSACTION');
        
        try {
            foreach ($ips as $ip) {
                $ip = sanitize_text_field($ip);
                
                $exists = $this->wpdb->get_var($this->wpdb->prepare(
                    "SELECT id FROM `{$table}` WHERE ip = %s",
                    $ip
                ));
                
                if ($exists) {
                    $result = $this->wpdb->update(
                        $table,
                        ['last_seen' => $now, 'updated_at' => $now],
                        ['ip' => $ip],
                        ['%s', '%s'],
                        ['%s']
                    );
                } else {
                    $result = $this->wpdb->insert(
                        $table,
                        [
                            'ip' => $ip,
                            'exit_address' => $ip,
                            'last_seen' => $now,
                            'created_at' => $now,
                            'updated_at' => $now,
                        ],
                        ['%s', '%s', '%s', '%s', '%s']
                    );
                }
                
                if ($result !== false) {
                    $imported++;
                }
            }
            
            $this->wpdb->query('COMMIT');
        } catch (Exception $e) {
            $this->wpdb->query('ROLLBACK');
            
            if (defined('WP_DEBUG') && WP_DEBUG) {
                error_log('BotFend TOR: Batch import failed - ' . $e->getMessage());
            }
            
            return 0;
        }
        
        $this->invalidate_tor_cache();
        
        return $imported;
    }

    public function invalidate_tor_cache(): void {
        wp_cache_delete('tor_nodes_count', 'botfend_tor');
        wp_cache_delete('tor_nodes_all_0', 'botfend_tor');
        wp_cache_delete(self::CACHE_PREFIX_TOR . 'statistics', self::CACHE_GROUP);
        wp_cache_delete(self::CACHE_PREFIX_TOR . 'all_1000', self::CACHE_GROUP);
    }
    
    public function clear_logs() {
        if (!current_user_can('manage_options')) {
            return false;
        }
        
        $logs_table = $this->get_table_name(self::LOG_TABLE);
        $aggregates_table = $this->get_table_name(self::LOG_AGGREGATES_TABLE);
        
        if (!$logs_table || !$aggregates_table) {
            return false;
        }
        
        $result_logs = $this->wpdb->query("DELETE FROM `{$logs_table}`");
        if ($result_logs === false) {
            error_log(sprintf(
                'BotFend: Database error deleting logs table. Error: %s',
                $this->wpdb->last_error ?: 'unknown'
            ));
            return false;
        }
        
        $result_aggregates = $this->wpdb->query("DELETE FROM `{$aggregates_table}`");
        if ($result_aggregates === false) {
            error_log(sprintf(
                'BotFend: Database error deleting aggregates table. Error: %s',
                $this->wpdb->last_error ?: 'unknown'
            ));
            return false;
        }
        
        $this->clear_all_caches();
        
        return true;
    }
    
    public function clear_aggregated_data() {
        if (!current_user_can('manage_options')) {
            return false;
        }
        
        $aggregates_table = $this->get_table_name(self::LOG_AGGREGATES_TABLE);
        if (!$aggregates_table) {
            return false;
        }
        
        $result = $this->wpdb->query("DELETE FROM `{$aggregates_table}`");
        
        if ($result !== false) {
            $this->invalidate_aggregates_cache();
            return true;
        } else {
            error_log(sprintf(
                'BotFend: Database error deleting aggregates table. Error: %s',
                $this->wpdb->last_error ?: 'unknown'
            ));
            return false;
        }
    }
    
    public function auto_unblock_eligible_ips() {
        $table = $this->get_table_name(self::BLOCKLIST_TABLE);
        if (!$table) {
            return 0;
        }
        
        $expired = $this->wpdb->get_results(
            "SELECT id, ip FROM `{$table}` 
             WHERE expires IS NOT NULL 
             AND expires < UTC_TIMESTAMP() 
             AND type != 'permanent_block'"
        );
        
        $unblocked = 0;
        foreach ($expired as $block) {
            if ($this->unblock_ip($block->ip)) {
                $unblocked++;
            }
        }
        
        update_option('botfend_last_auto_unblock', time());
        
        return $unblocked;
    }
    
    // ============================================
    // CACHE METHODS
    // ============================================
    
    private function get_cached_result($key, $group = self::CACHE_GROUP, $callback = null, $ttl = 60) {
        $result = wp_cache_get($key, $group);
        
        if ($result !== false) {
            return $result;
        }
        
        if (is_callable($callback)) {
            $result = call_user_func($callback);
            
            if ($result !== false) {
                wp_cache_set($key, $result, $group, $ttl);
            }
        }
        
        return $result;
    }
    
    private function generate_cache_key($filters) {
        if (empty($filters)) {
            return 'empty';
        }
        
        ksort($filters);
        return md5((string) wp_json_encode($filters));
    }
    
    public function clear_ip_caches($ip) {
        if (!$this->validate_ip_for_query($ip)) {
            return;
        }
        
        $ip_hash = md5($ip);
        
        $cache_keys = [
            self::CACHE_PREFIX_BLOCKED . 'ip_' . $ip_hash,
            self::CACHE_PREFIX_BLOCKED . 'permanent_' . $ip_hash,
            self::CACHE_PREFIX_BLOCKED . 'repeat_' . $ip_hash,
            'botfend_rate_limit_' . $ip_hash,
            'botfend_rate_limit_sliding_' . $ip_hash,
            'botfend_404_abuser_' . $ip_hash,
            self::CACHE_PREFIX_LOGS . 'history_' . $ip_hash,
            self::CACHE_PREFIX_TOR . 'node_' . $ip_hash,
        ];
        
        foreach ($cache_keys as $key) {
            wp_cache_delete($key, self::CACHE_GROUP);
        }
        
        $patterns = [
            'botfend_log_throttle_' . $ip_hash,
        ];
        
        foreach ($patterns as $pattern) {
            delete_transient($pattern);
        }
    }
    
    private function invalidate_ip_logs_cache($ip) {
        $ip_hash = md5($ip);
        wp_cache_delete(self::CACHE_PREFIX_LOGS . 'list_ip_' . $ip_hash, self::CACHE_GROUP);
    }
    
    private function invalidate_aggregates_cache() {
        wp_cache_delete(self::CACHE_PREFIX_AGGREGATES . 'daily_7', self::CACHE_GROUP);
        wp_cache_delete(self::CACHE_PREFIX_AGGREGATES . 'hourly_1', self::CACHE_GROUP);
        wp_cache_delete(self::CACHE_PREFIX_STATS . 'dashboard', self::CACHE_GROUP);
    }
    
    private function invalidate_stats_cache() {
        wp_cache_delete(self::CACHE_PREFIX_STATS . 'dashboard', self::CACHE_GROUP);
        $this->invalidate_aggregates_cache();
    }
    
    public function clear_all_caches() {
        if (function_exists('wp_cache_flush_group')) {
            wp_cache_flush_group(self::CACHE_GROUP);
        }
    }
    
    private function validate_ip_for_query($ip) {
        if (empty($ip)) {
            return false;
        }
        
        $ip = trim(sanitize_text_field($ip));
        
        if (strlen($ip) > 45) {
            return false;
        }
        
        if (strpos($ip, '/') !== false) {
            return $this->is_valid_cidr($ip);
        }
        
        return filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4 | FILTER_FLAG_IPV6) !== false;
    }
    
    private function is_valid_cidr($cidr) {
        if (strpos($cidr, '/') === false) {
            return false;
        }
        
        $parts = explode('/', $cidr);
        if (count($parts) !== 2) {
            return false;
        }
        
        $ip = $parts[0];
        $mask = (int) $parts[1];
        
        if (!filter_var($ip, FILTER_VALIDATE_IP)) {
            return false;
        }
        
        if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
            return $mask >= 0 && $mask <= 32;
        }
        
        if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV6)) {
            return $mask >= 0 && $mask <= 128;
        }
        
        return false;
    }
    
    public function table_exists($table_name) {
        $full_table_name = $this->get_table_name($table_name);
        if (!$full_table_name) {
            return false;
        }
        
        $result = $this->wpdb->get_var($this->wpdb->prepare(
            "SHOW TABLES LIKE %s",
            $full_table_name
        ));
        
        return $result === $full_table_name;
    }
}