<?php
/**
 * Cloudflare Sync Engine for BotFend Anti-Bot Firewall
 *
 * Handles synchronization of blocked IPs between BotFend and Cloudflare's
 * firewall rules using batched IP groups with transactional integrity,
 * race condition prevention, and comprehensive error handling.
 *
 * @package BotFend\Cloudflare
 * @since 4.9.0
 */

namespace BotFend\Cloudflare;

use BotFend\Core\Config;

defined('ABSPATH') || exit;

/**
 * Cloudflare Sync Engine class.
 *
 * Production-ready implementation with:
 * - Transactional integrity with rollback
 * - Race condition prevention via row locking
 * - Cloudflare rule limit enforcement
 * - Expression length validation
 * - Retry logic with exponential backoff
 * - Comprehensive logging and hooks
 *
 * @since 4.9.0
 */
class CloudflareSync {

    /**
     * Default maximum IPs per firewall rule.
     * Filterable via 'botfend_cf_max_ips_per_rule'.
     */
    private const DEFAULT_MAX_IPS_PER_RULE = 50;

    /**
     * Maximum expression length (Cloudflare limit is ~4096 chars).
     * Using safety margin for overhead.
     */
    private const MAX_EXPRESSION_LENGTH = 3500;

    /**
     * Maximum retry attempts for API calls.
     */
    private const MAX_RETRY_ATTEMPTS = 3;

    /**
     * Base delay for retry backoff (microseconds).
     */
    private const RETRY_BASE_DELAY = 100000;

    /**
     * Cloudflare API instance.
     *
     * @var CloudflareAPI
     */
    private $api;

    /**
     * Config instance.
     *
     * @var Config
     */
    private $config;

    /**
     * WordPress database instance.
     *
     * @var \wpdb
     */
    private $wpdb;

    /**
     * Sync tracking table name.
     *
     * @var string
     */
    private $table_name;

    /**
     * Rules table name.
     *
     * @var string
     */
    private $rules_table_name;

    /**
     * In-memory cache for rule IPs.
     *
     * @var array
     */
    private $rule_cache = [];

    /**
     * Flag to track transaction state.
     *
     * @var bool
     */
    private $in_transaction = false;

    /**
     * Constructor.
     *
     * @param CloudflareAPI $api    Cloudflare API wrapper instance.
     * @param Config        $config Config instance.
     */
    public function __construct(CloudflareAPI $api, Config $config) {
        global $wpdb;
        
        $this->api              = $api;
        $this->config           = $config;
        $this->wpdb             = $wpdb;
        $this->table_name       = $wpdb->prefix . 'botfend_cf_sync';
        $this->rules_table_name = $wpdb->prefix . 'botfend_cf_rules';
        
        $this->maybe_create_tables();
        
        // Register cleanup hooks
        add_action('botfend_cf_clean_expired', [$this, 'clean_expired_blocks']);
    }

    /**
     * Get maximum IPs per rule (filterable).
     *
     * @return int
     */
    private function get_max_ips_per_rule(): int {
        return (int) apply_filters(
            'botfend_cf_max_ips_per_rule',
            self::DEFAULT_MAX_IPS_PER_RULE
        );
    }

    /**
     * Get maximum rules allowed (filterable).
     *
     * @return int
     */
    private function get_max_rules(): int {
        return (int) apply_filters(
            'botfend_cf_max_rules',
            $this->config->get_int('cf_max_rules', 5)
        );
    }

    /**
     * Create the sync tracking tables if they don't exist.
     *
     * @return void
     */
    private function maybe_create_tables(): void {
        $charset_collate = $this->wpdb->get_charset_collate();

        // Table 1: IP to Rule mapping
        $sql_sync = "CREATE TABLE `{$this->table_name}` (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            ip varchar(45) NOT NULL,
            rule_id varchar(64) NOT NULL COMMENT 'Cloudflare firewall rule ID',
            score smallint(5) unsigned NOT NULL DEFAULT '0',
            action varchar(20) NOT NULL DEFAULT 'block',
            block_type varchar(50) DEFAULT NULL,
            block_reason text DEFAULT NULL,
            expires_at datetime DEFAULT NULL,
            synced_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY idx_ip (ip),
            KEY idx_rule_id (rule_id),
            KEY idx_expires (expires_at),
            KEY idx_synced_at (synced_at)
        ) {$charset_collate};";

        // Table 2: Rule registry
        $sql_rules = "CREATE TABLE `{$this->rules_table_name}` (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            rule_id varchar(64) NOT NULL COMMENT 'Cloudflare firewall rule ID',
            rule_name varchar(255) NOT NULL,
            action varchar(20) NOT NULL DEFAULT 'block',
            ip_count int(10) unsigned NOT NULL DEFAULT '0',
            min_score int(3) NOT NULL DEFAULT '0',
            max_score int(3) NOT NULL DEFAULT '0',
            created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_modified datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            is_active tinyint(1) NOT NULL DEFAULT '1',
            PRIMARY KEY (id),
            UNIQUE KEY idx_rule_id (rule_id),
            KEY idx_action (action),
            KEY idx_is_active (is_active),
            KEY idx_created_at (created_at)
        ) {$charset_collate};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql_sync);
        dbDelta($sql_rules);
    }

    /**
     * Add an IP to Cloudflare blocklist.
     *
     * @param string      $ip      IP address.
     * @param string      $type    Block type.
     * @param int         $score   Threat score.
     * @param string      $reason  Block reason.
     * @param string|null $expires Expiration date (UTC).
     * @return bool True on success.
     */
    public function add_ip_to_blocklist(string $ip, string $type, int $score, string $reason, ?string $expires = null): bool {
        // Validate IP
        if (!$this->api->validate_ip($ip)) {
            $this->log_message('error', 'invalid_ip', $ip, 'Invalid IP format');
            return false;
        }

        // Check if already synced
        if ($this->is_ip_synced($ip)) {
            $this->log_message('debug', 'already_synced', $ip, 'IP already in sync');
            return true;
        }

        /**
         * Fires before adding an IP to Cloudflare blocklist.
         *
         * @since 4.9.0
         * @param string $ip     IP address.
         * @param string $type   Block type.
         * @param int    $score  Threat score.
         */
        do_action('botfend_cf_before_add_ip', $ip, $type, $score);

        $action = $this->get_action_for_score($score, $type);

        // Find or create rule with transaction lock
        $rule = $this->find_or_create_rule_with_lock($action, $score);

        if (!$rule) {
            $this->log_message('error', 'rule_unavailable', $ip, 'Could not find or create rule');
            
            /**
             * Fires when rule creation fails.
             *
             * @since 4.9.0
             */
            do_action('botfend_cf_rule_creation_failed', $ip, $action);
            
            return false;
        }
        
        // Add IP to rule
        $result = $this->add_ip_to_rule_transactional($ip, $rule, $score, $type, $reason, $expires, $action);

        if ($result) {
            /**
             * Fires after IP successfully synced.
             *
             * @since 4.9.0
             */
            do_action('botfend_cf_ip_synced', $ip, $rule['rule_id'], $action);
        } else {
            /**
             * Fires when IP sync fails.
             *
             * @since 4.9.0
             */
            do_action('botfend_cf_sync_failed', $ip, 'add_ip_failed');
        }

        return $result;
    }

    /**
     * Find or create rule with row-level locking to prevent race conditions.
     *
     * @param string $action Cloudflare action.
     * @param int    $score  Threat score.
     * @return array|null Rule data or null.
     */
    private function find_or_create_rule_with_lock(string $action, int $score): ?array {
        $max_ips = $this->get_max_ips_per_rule();

        // Start transaction
        $this->start_transaction();

        try {
            // Lock and find rule with capacity
            $rule = $this->wpdb->get_row(
                $this->wpdb->prepare(
                    "SELECT * FROM `{$this->rules_table_name}` 
                    WHERE action = %s 
                    AND ip_count < %d 
                    AND is_active = 1
                    ORDER BY ip_count ASC 
                    LIMIT 1 FOR UPDATE",
                    $action,
                    $max_ips
                ),
                ARRAY_A
            );
            
            if ($rule) {
                $this->commit_transaction();
                return $rule;
            }

            // No rule with capacity, check if we can create new one
            if (!$this->can_create_new_rule()) {
                $this->log_message('warning', 'rule_limit', 'N/A', 
                    sprintf('Rule limit reached (%d), attempting consolidation', $this->get_max_rules()));
                
                $this->attempt_rule_consolidation($action);
                
                // Try again after consolidation
                $rule = $this->wpdb->get_row(
                    $this->wpdb->prepare(
                        "SELECT * FROM `{$this->rules_table_name}` 
                        WHERE action = %s 
                        AND ip_count < %d 
                        AND is_active = 1
                        ORDER BY ip_count ASC 
                        LIMIT 1 FOR UPDATE",
                        $action,
                        $max_ips
                    ),
                    ARRAY_A
                );

                if ($rule) {
                    $this->commit_transaction();
                    return $rule;
                }

                $this->rollback_transaction();
                return null;
            }

            // Create new rule
            $rule = $this->create_new_rule_internal($action, $score);

            if ($rule) {
                $this->commit_transaction();
                return $rule;
            }

            $this->rollback_transaction();
            return null;

        } catch (\Exception $e) {
            $this->rollback_transaction();
            $this->log_message('error', 'find_or_create_failed', 'N/A', $e->getMessage());
            return null;
        }
    }

    /**
     * Check if we can create a new rule within limits.
     *
     * @return bool
     */
    private function can_create_new_rule(): bool {
        $current_count = (int) $this->wpdb->get_var(
            "SELECT COUNT(*) FROM `{$this->rules_table_name}` WHERE is_active = 1"
        );
        
        return $current_count < $this->get_max_rules();
    }

    /**
     * Attempt to consolidate rules to free up space.
     *
     * @param string $action Action to consolidate.
     * @return void
     */
    private function attempt_rule_consolidation(string $action): void {
        $max_ips = $this->get_max_ips_per_rule();
        
        // Find rules with low IP counts that could be merged
        $sparse_rules = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT rule_id, ip_count FROM `{$this->rules_table_name}` 
                WHERE action = %s AND is_active = 1 AND ip_count < %d
                ORDER BY ip_count ASC",
                $action,
                ceil($max_ips / 2)
            ),
            ARRAY_A
        );

        if (count($sparse_rules) < 2) {
            return; // Not enough rules to consolidate
        }

        $total_ips = array_sum(array_column($sparse_rules, 'ip_count'));
        
        if ($total_ips <= $max_ips) {
            // Can merge all sparse rules into one
            $this->log_message('info', 'consolidation', 'N/A', 
                sprintf('Consolidating %d sparse rules', count($sparse_rules)));
            
            // Implementation would merge rules here
            // For now, log the opportunity
            do_action('botfend_cf_consolidation_opportunity', $sparse_rules, $action);
        }
    }

    /**
     * Create a new Cloudflare rule (internal, assumes transaction).
     *
     * @param string $action Cloudflare action.
     * @param int    $score  Threat score.
     * @return array|null Rule data.
     */
    private function create_new_rule_internal(string $action, int $score): ?array {
        $rule_name = sprintf(
            'BotFend - %s - %s',
            ucfirst($action),
            substr(wp_generate_uuid4(), 0, 8)
        );

        // Create empty rule in Cloudflare
        $result = $this->api_call_with_retry(function () use ($rule_name, $action) {
            return $this->api->create_empty_firewall_rule($rule_name, $action);
        });

        if (is_wp_error($result) || !isset($result['result']['id'])) {
            $error_msg = is_wp_error($result) ? $result->get_error_message() : 'Unknown error';
            $this->log_message('error', 'rule_creation_failed', 'N/A', $error_msg);
            return null;
        }

        $rule_id = $result['result']['id'];

        // Store rule locally
        $inserted = $this->wpdb->insert(
            $this->rules_table_name,
            [
                'rule_id'   => $rule_id,
                'rule_name' => $rule_name,
                'action'    => $action,
                'ip_count'  => 0,
                'min_score' => max(0, $score - 2),
                'max_score' => min(10, $score + 2),
                'is_active' => 1,
            ],
            ['%s', '%s', '%s', '%d', '%d', '%d', '%d']
        );

        if (!$inserted) {
            // Rollback Cloudflare rule
            $this->api->delete_firewall_rule($rule_id);
            $this->log_message('error', 'db_insert_failed', $rule_id, 'Failed to store rule in DB');
            return null;
        }

        $this->log_message('info', 'rule_created', $rule_id, 
            sprintf('Created new rule for action: %s', $action));

        return [
            'rule_id'   => $rule_id,
            'rule_name' => $rule_name,
            'action'    => $action,
            'ip_count'  => 0,
        ];
    }

    /**
     * Add IP to rule with full transactional integrity.
     *
     * @param string      $ip      IP address.
     * @param array       $rule    Rule data.
     * @param int         $score   Threat score.
     * @param string      $type    Block type.
     * @param string      $reason  Block reason.
     * @param string|null $expires Expiration.
     * @param string      $action  Cloudflare action.
     * @return bool True on success.
     */
    private function add_ip_to_rule_transactional(string $ip, array $rule, int $score, string $type, string $reason, ?string $expires, string $action): bool {
        $this->start_transaction();

        try {
            // Get existing IPs with lock
            $existing_ips = $this->get_rule_ips_locked($rule['rule_id']);

            // Check if IP already in rule
            if (in_array($ip, $existing_ips, true)) {
                $this->store_sync_record($ip, $rule['rule_id'], $score, $action, $type, $reason, $expires);
                $this->commit_transaction();
                return true;
            }

            // Build new IP list
            $new_ips = array_merge($existing_ips, [$ip]);

            // Build expression (CORRECT format for Cloudflare Free tier)
            $expression = $this->build_ip_expression($new_ips);
            
            if ($expression === false) {
                // Expression too long, need new rule
                $this->rollback_transaction();
                
                $this->log_message('warning', 'expression_limit', $ip, 
                    'Expression would exceed limit, creating new rule');
                
                // Mark current rule as full
                $this->wpdb->update(
                    $this->rules_table_name,
                    ['ip_count' => $this->get_max_ips_per_rule()],
                    ['rule_id' => $rule['rule_id']],
                    ['%d'],
                    ['%s']
                );
                
                // Retry with new rule
                $new_rule = $this->find_or_create_rule_with_lock($action, $score);
                
                if ($new_rule) {
                    return $this->add_ip_to_rule_transactional($ip, $new_rule, $score, $type, $reason, $expires, $action);
                }
                
                return false;
            }

            // Update Cloudflare rule
            $result = $this->api_call_with_retry(function () use ($rule, $expression) {
                return $this->api->update_firewall_rule_expression($rule['rule_id'], $expression);
            });

            if (is_wp_error($result)) {
                throw new \Exception($result->get_error_message());
            }

            // Store sync record
            $this->store_sync_record($ip, $rule['rule_id'], $score, $action, $type, $reason, $expires);

            // Update rule IP count
            $updated = $this->wpdb->update(
                $this->rules_table_name,
                ['ip_count' => count($new_ips)],
                ['rule_id' => $rule['rule_id']],
                ['%d'],
                ['%s']
            );

            if ($updated === false) {
                throw new \Exception('Failed to update rule IP count');
            }

            $this->commit_transaction();
            
            // Clear cache
            $this->clear_rule_cache($rule['rule_id']);
            
            $this->log_message('debug', 'ip_added', $ip, 
                sprintf('Added to rule %s (now has %d IPs)', $rule['rule_id'], count($new_ips)));

            return true;

        } catch (\Exception $e) {
            $this->rollback_transaction();
            $this->log_message('error', 'add_ip_failed', $ip, $e->getMessage());
            
            // Attempt Cloudflare rollback if we got past expression update
            if (isset($expression) && isset($existing_ips)) {
                $this->log_message('warning', 'rollback', $ip, 'Attempting Cloudflare rollback');
                $rollback_expr = $this->build_ip_expression($existing_ips);
                if ($rollback_expr) {
                    $this->api->update_firewall_rule_expression($rule['rule_id'], $rollback_expr);
                }
            }

            return false;
        }
    }

    /**
     * Get all IPs in a rule with row lock.
     *
     * @param string $rule_id Rule ID.
     * @return array Array of IPs.
     */
    private function get_rule_ips_locked(string $rule_id): array {
        $results = $this->wpdb->get_col(
            $this->wpdb->prepare(
                "SELECT ip FROM `{$this->table_name}` 
                WHERE rule_id = %s 
                ORDER BY ip FOR UPDATE",
                $rule_id
            )
        );
        
        return $results ?: [];
    }

    /**
     * Get all IPs in a rule (cached).
     *
     * @param string $rule_id Rule ID.
     * @return array Array of IPs.
     */
    private function get_rule_ips(string $rule_id): array {
        if (isset($this->rule_cache[$rule_id])) {
            return $this->rule_cache[$rule_id];
        }

        $results = $this->wpdb->get_col(
            $this->wpdb->prepare(
                "SELECT ip FROM `{$this->table_name}` WHERE rule_id = %s",
                $rule_id
            )
        );
        
        $this->rule_cache[$rule_id] = $results ?: [];
        return $this->rule_cache[$rule_id];
    }

    /**
     * Clear rule cache.
     *
     * @param string $rule_id Rule ID.
     * @return void
     */
    private function clear_rule_cache(string $rule_id): void {
        unset($this->rule_cache[$rule_id]);
    }

    /**
     * Build Cloudflare expression for multiple IPs (Free tier compatible).
     * 
     * Free tier requires: (ip.src eq 1.2.3.4) or (ip.src eq 5.6.7.8)
     * NO outer parentheses!
     *
     * @param array $ips Array of IP addresses.
     * @return string|false Cloudflare expression or false if too long.
     */
    private function build_ip_expression(array $ips): string|false {
        if (empty($ips)) {
            return '(ip.src eq 0.0.0.0)';
        }

        $expressions = [];
        
        foreach ($ips as $ip) {
            if ($this->api->validate_ip($ip)) {
                // CORRECT: Use 'eq' operator, each IP gets its own parentheses
                $expressions[] = sprintf('(ip.src eq %s)', $ip);
            } else {
                $this->log_message('warning', 'invalid_ip_skipped', $ip, 'Skipping invalid IP in expression');
            }
        }

        if (empty($expressions)) {
            return '(ip.src eq 0.0.0.0)';
        }

        // CORRECT: NO outer parentheses - just join with ' or '
        $expression = implode(' or ', $expressions);

        // Validate length (Cloudflare limit ~4096 chars)
        if (strlen($expression) > self::MAX_EXPRESSION_LENGTH) {
            $this->log_message('warning', 'expression_too_long', 'N/A', 
                sprintf('Expression length %d exceeds limit %d', strlen($expression), self::MAX_EXPRESSION_LENGTH));
            return false;
        }

        return $expression;
    }

    /**
     * Remove an IP from Cloudflare blocklist.
     *
     * @param string $ip IP address.
     * @return bool True on success.
     */
    public function remove_ip_from_blocklist(string $ip): bool {
        $record = $this->get_sync_record($ip);

        if (!$record) {
            $this->log_message('debug', 'remove_not_found', $ip, 'IP not in sync records');
            return false;
        }

        /**
         * Fires before removing IP from blocklist.
         *
         * @since 4.9.0
         */
        do_action('botfend_cf_before_remove_ip', $ip, $record->rule_id);

        $success = $this->remove_ip_from_rule_transactional($ip, $record->rule_id);

        if ($success) {
            $this->delete_sync_record($ip);
            
            /**
             * Fires after IP removed.
             *
             * @since 4.9.0
             */
            do_action('botfend_cf_ip_removed', $ip, $record->rule_id);
        }

        return $success;
    }

    /**
     * Remove IP from rule with transactional integrity.
     *
     * @param string $ip      IP address.
     * @param string $rule_id Rule ID.
     * @return bool True on success.
     */
    private function remove_ip_from_rule_transactional(string $ip, string $rule_id): bool {
        $this->start_transaction();

        try {
            $current_ips = $this->get_rule_ips_locked($rule_id);
            $new_ips = array_values(array_diff($current_ips, [$ip]));

            if (empty($new_ips)) {
                // Rule empty, delete it
                $this->commit_transaction();
                return $this->delete_rule($rule_id);
            }

            // Build new expression
            $expression = $this->build_ip_expression($new_ips);
            
            if ($expression === false) {
                throw new \Exception('Failed to build expression');
            }

            // Update Cloudflare
            $result = $this->api_call_with_retry(function () use ($rule_id, $expression) {
                return $this->api->update_firewall_rule_expression($rule_id, $expression);
            });

            if (is_wp_error($result)) {
                throw new \Exception($result->get_error_message());
            }

            // Update IP count
            $updated = $this->wpdb->update(
                $this->rules_table_name,
                ['ip_count' => count($new_ips)],
                ['rule_id' => $rule_id],
                ['%d'],
                ['%s']
            );

            if ($updated === false) {
                throw new \Exception('Failed to update IP count');
            }
            
            $this->commit_transaction();
            $this->clear_rule_cache($rule_id);
            
            return true;

        } catch (\Exception $e) {
            $this->rollback_transaction();
            $this->log_message('error', 'remove_ip_failed', $ip, $e->getMessage());
            return false;
        }
    }

    /**
     * Delete an entire rule.
     *
     * @param string $rule_id Rule ID.
     * @return bool True on success.
     */
    private function delete_rule(string $rule_id): bool {
        $this->start_transaction();

        try {
            // Delete from Cloudflare
            $result = $this->api_call_with_retry(function () use ($rule_id) {
                return $this->api->delete_firewall_rule($rule_id);
            });

            if (is_wp_error($result)) {
                throw new \Exception($result->get_error_message());
            }

            // Delete local records
            $this->wpdb->delete($this->table_name, ['rule_id' => $rule_id], ['%s']);
            $this->wpdb->delete($this->rules_table_name, ['rule_id' => $rule_id], ['%s']);

            $this->commit_transaction();
            $this->clear_rule_cache($rule_id);
            
            $this->log_message('info', 'rule_deleted', $rule_id, 'Rule deleted successfully');
            
            return true;

        } catch (\Exception $e) {
            $this->rollback_transaction();
            $this->log_message('error', 'rule_delete_failed', $rule_id, $e->getMessage());
            return false;
        }
    }

    /**
     * Manually delete a rule (admin UI).
     *
     * @param string $rule_id Rule ID.
     * @return array Result.
     */
    public function manually_delete_rule(string $rule_id): array {
        if (!current_user_can('manage_options')) {
            return [
                'success' => false, 
                'message' => __('Insufficient permissions.', 'botfend-anti-bot-firewall')
            ];
        }

        $success = $this->delete_rule($rule_id);

        return [
            'success' => $success,
            'message' => $success 
                ? __('Rule deleted successfully.', 'botfend-anti-bot-firewall')
                : __('Failed to delete rule.', 'botfend-anti-bot-firewall')
        ];
    }

    /**
     * Manually remove an IP (admin UI).
     *
     * @param string $ip IP address.
     * @return array Result.
     */
    public function manually_remove_ip(string $ip): array {
        if (!current_user_can('manage_options')) {
            return [
                'success' => false, 
                'message' => __('Insufficient permissions.', 'botfend-anti-bot-firewall')
            ];
        }

        $success = $this->remove_ip_from_blocklist($ip);

        return [
            'success' => $success,
            'message' => $success 
                ? __('IP removed successfully.', 'botfend-anti-bot-firewall')
                : __('IP not found or removal failed.', 'botfend-anti-bot-firewall')
        ];
    }

    /**
     * Sync all eligible blocked IPs to Cloudflare.
     *
     * @param int $limit  Batch limit.
     * @param int $offset Offset.
     * @return array Sync results.
     */
    public function sync_all_blocked_ips(int $limit = 100, int $offset = 0): array {
        $min_score = $this->config->get_int('cf_min_block_score', 5);
        $blocklist_table = $this->wpdb->prefix . 'botfend_blocklist';

        $results = $this->wpdb->get_results(
            $this->wpdb->prepare(
                "SELECT b.ip, b.type, b.score, b.reason, b.expires 
                FROM `{$blocklist_table}` b
                LEFT JOIN `{$this->table_name}` s ON b.ip = s.ip
                WHERE (b.expires IS NULL OR b.expires > UTC_TIMESTAMP())
                AND b.score >= %d
                AND s.ip IS NULL
                ORDER BY b.score DESC
                LIMIT %d OFFSET %d",
                absint($min_score),
                absint($limit),
                absint($offset)
            )
        );

        if (empty($results)) {
            return ['total' => 0, 'synced' => 0, 'failed' => 0];
        }

        $synced = 0;
        $failed = 0;
        $errors = [];

        foreach ($results as $block) {
            $ip      = sanitize_text_field($block->ip);
            $type    = sanitize_text_field($block->type);
            $score   = absint($block->score);
            $reason  = sanitize_textarea_field($block->reason);
            $expires = $block->expires ?? null;

            if ($this->add_ip_to_blocklist($ip, $type, $score, $reason, $expires)) {
                $synced++;
            } else {
                $failed++;
                $errors[] = $ip;
            }

            // Rate limiting with jitter
            usleep(self::RETRY_BASE_DELAY + rand(0, 50000));
        }
        
        // Update last sync timestamp (UTC)
        update_option('botfend_cf_last_sync', $this->get_utc_datetime(), false);

        /**
         * Fires after batch sync completes.
         *
         * @since 4.9.0
         */
        do_action('botfend_cf_batch_sync_complete', $synced, $failed, $errors);

        return [
            'total'  => count($results),
            'synced' => $synced,
            'failed' => $failed,
            'errors' => $errors,
        ];
    }

    /**
     * Clean up expired IP blocks.
     *
     * @return int Number removed.
     */
    public function clean_expired_blocks(): int {
        $expired_ips = $this->wpdb->get_col(
            "SELECT ip FROM `{$this->table_name}` 
            WHERE expires_at IS NOT NULL 
            AND expires_at <= UTC_TIMESTAMP()"
        );

        if (empty($expired_ips)) {
            return 0;
        }

        $removed = 0;
        
        foreach ($expired_ips as $ip) {
            if ($this->remove_ip_from_blocklist($ip)) {
                $removed++;
            }
        }

        $this->log_message('info', 'cleanup', 'N/A', 
            sprintf('Cleaned %d expired IP blocks', $removed));

        return $removed;
    }

    /**
     * Get sync status.
     *
     * @return array Sync status.
     */
    public function get_sync_status(): array {
        $total_synced = (int) $this->wpdb->get_var("SELECT COUNT(*) FROM `{$this->table_name}`");
        $total_rules = (int) $this->wpdb->get_var("SELECT COUNT(*) FROM `{$this->rules_table_name}` WHERE is_active = 1");

        $min_score = $this->config->get_int('cf_min_block_score', 5);
        $blocklist_table = $this->wpdb->prefix . 'botfend_blocklist';

        $pending = (int) $this->wpdb->get_var(
            $this->wpdb->prepare(
                "SELECT COUNT(*) FROM `{$blocklist_table}` b
                LEFT JOIN `{$this->table_name}` s ON b.ip = s.ip
                WHERE (b.expires IS NULL OR b.expires > UTC_TIMESTAMP())
                AND b.score >= %d
                AND s.ip IS NULL",
                absint($min_score)
            )
        );

        $last_sync = get_option('botfend_cf_last_sync', '');
        
        $rule_stats = $this->wpdb->get_results(
            "SELECT action, COUNT(*) as rule_count, SUM(ip_count) as total_ips 
            FROM `{$this->rules_table_name}` 
            WHERE is_active = 1 
            GROUP BY action"
        );

        $max_rules = $this->get_max_rules();

        return [
            'total_synced'     => $total_synced,
            'total_rules'      => $total_rules,
            'max_rules'        => $max_rules,
            'rules_remaining'  => max(0, $max_rules - $total_rules),
            'pending_sync'     => $pending,
            'last_sync'        => !empty($last_sync) ? $last_sync : __('Never', 'botfend-anti-bot-firewall'),
            'rule_stats'       => $rule_stats,
            'max_ips_per_rule' => $this->get_max_ips_per_rule(),
            'health'           => $this->verify_cloudflare_sync(),
        ];
    }

    /**
     * Get all rules with their IPs for admin UI.
     *
     * @return array Rules with IPs.
     */
    public function get_all_rules_with_ips(): array {
        $rules = $this->wpdb->get_results(
            "SELECT * FROM `{$this->rules_table_name}` 
            WHERE is_active = 1 
            ORDER BY created_at DESC",
            ARRAY_A
        );

        foreach ($rules as &$rule) {
            $ips = $this->wpdb->get_results(
                $this->wpdb->prepare(
                    "SELECT ip, score, block_type, block_reason, expires_at 
                    FROM `{$this->table_name}` 
                    WHERE rule_id = %s 
                    ORDER BY synced_at DESC",
                    $rule['rule_id']
                )
            );
            $rule['ips'] = $ips;
            $rule['capacity'] = $this->get_max_ips_per_rule();
            $rule['utilization'] = round(($rule['ip_count'] / $this->get_max_ips_per_rule()) * 100, 1);
        }

        return $rules;
    }

    /**
     * Verify Cloudflare sync integrity.
     *
     * @return array Health check results.
     */
    public function verify_cloudflare_sync(): array {
        $issues = [];
        $healthy = true;

        // Check for orphaned IP records
        $orphaned = $this->wpdb->get_col(
            "SELECT ip FROM `{$this->table_name}` 
            WHERE rule_id NOT IN (SELECT rule_id FROM `{$this->rules_table_name}` WHERE is_active = 1)"
        );

        if (!empty($orphaned)) {
            $issues[] = sprintf(
                /* translators: %d: number of orphaned records */
                __('Found %d orphaned IP records without active rules.', 'botfend-anti-bot-firewall'),
                count($orphaned)
            );
            $healthy = false;
        }
        
        // Check rule IP counts match
        $count_mismatches = $this->wpdb->get_results(
            "SELECT r.rule_id, r.ip_count as stored_count, COUNT(s.ip) as actual_count
            FROM `{$this->rules_table_name}` r
            LEFT JOIN `{$this->table_name}` s ON r.rule_id = s.rule_id
            WHERE r.is_active = 1
            GROUP BY r.rule_id
            HAVING stored_count != actual_count"
        );

        if (!empty($count_mismatches)) {
            $issues[] = sprintf(
                /* translators: %d: number of mismatched rules */
                __('Found %d rules with IP count mismatches.', 'botfend-anti-bot-firewall'),
                count($count_mismatches)
            );
            $healthy = false;
        }

        // Check for expired records still in sync
        $stale_expired = (int) $this->wpdb->get_var(
            "SELECT COUNT(*) FROM `{$this->table_name}` 
            WHERE expires_at IS NOT NULL AND expires_at <= UTC_TIMESTAMP()"
        );

        if ($stale_expired > 0) {
            $issues[] = sprintf(
                /* translators: %d: number of stale records */
                __('Found %d expired records still in sync.', 'botfend-anti-bot-firewall'),
                $stale_expired
            );
            $healthy = false;
        }

        return [
            'healthy' => $healthy,
            'issues'  => $issues,
        ];
    }

    /**
     * Fix sync integrity issues.
     *
     * @return array Results.
     */
    public function fix_sync_integrity(): array {
        $fixed = 0;
        $errors = [];

        // Fix IP counts
        $this->wpdb->query(
            "UPDATE `{$this->rules_table_name}` r
            SET ip_count = (
                SELECT COUNT(*) FROM `{$this->table_name}` s 
                WHERE s.rule_id = r.rule_id
            )
            WHERE is_active = 1"
        );
        $fixed++;

        // Remove orphaned records
        $orphaned = $this->wpdb->query(
            "DELETE FROM `{$this->table_name}` 
            WHERE rule_id NOT IN (
                SELECT rule_id FROM `{$this->rules_table_name}` WHERE is_active = 1
            )"
        );
        
        if ($orphaned !== false) {
            $fixed += $orphaned;
        }

        return [
            'success' => true,
            'fixed'   => $fixed,
            'errors'  => $errors,
        ];
    }

    /**
     * Get action for score (now uses universal action).
     *
     * @param int    $score Threat score.
     * @param string $type  Block type.
     * @return string Cloudflare action.
     */
    private function get_action_for_score(int $score, string $type): string {
        // Use universal action from config instead of complex logic
        return $this->config->get_string('cf_universal_action', 'block');
    }

    /**
     * Check if IP is already synced.
     *
     * @param string $ip IP address.
     * @return bool
     */
    private function is_ip_synced(string $ip): bool {
        $count = (int) $this->wpdb->get_var(
            $this->wpdb->prepare("SELECT COUNT(*) FROM `{$this->table_name}` WHERE ip = %s", $ip)
        );
        return $count > 0;
    }

    /**
     * Store sync record.
     *
     * @param string      $ip      IP address.
     * @param string      $rule_id Rule ID.
     * @param int         $score   Threat score.
     * @param string      $action  Cloudflare action.
     * @param string      $type    Block type.
     * @param string      $reason  Block reason.
     * @param string|null $expires Expiration.
     * @return void
     */
    private function store_sync_record(string $ip, string $rule_id, int $score, string $action, string $type, string $reason, ?string $expires): void {
        $this->wpdb->replace(
            $this->table_name,
            [
                'ip'           => sanitize_text_field($ip),
                'rule_id'      => sanitize_text_field($rule_id),
                'score'        => absint($score),
                'action'       => sanitize_text_field($action),
                'block_type'   => sanitize_text_field($type),
                'block_reason' => substr(sanitize_textarea_field($reason), 0, 500),
                'expires_at'   => $expires,
                'synced_at'    => $this->get_utc_datetime(),
            ],
            ['%s', '%s', '%d', '%s', '%s', '%s', '%s', '%s']
        );
    }

    /**
     * Get sync record.
     *
     * @param string $ip IP address.
     * @return object|null
     */
    private function get_sync_record(string $ip): ?object {
        return $this->wpdb->get_row(
            $this->wpdb->prepare("SELECT * FROM `{$this->table_name}` WHERE ip = %s", $ip)
        );
    }

    /**
     * Delete sync record.
     *
     * @param string $ip IP address.
     * @return void
     */
    private function delete_sync_record(string $ip): void {
        $this->wpdb->delete($this->table_name, ['ip' => $ip], ['%s']);
    }

    /**
     * Get current UTC datetime.
     *
     * @return string
     */
    private function get_utc_datetime(): string {
        return gmdate('Y-m-d H:i:s');
    }

    /**
     * API call with retry logic and exponential backoff.
     *
     * @param callable $callback    API call callback.
     * @param int      $max_retries Maximum retry attempts.
     * @return mixed Result or WP_Error.
     */
    private function api_call_with_retry(callable $callback, int $max_retries = self::MAX_RETRY_ATTEMPTS): mixed {
        $attempt = 0;
        
        while ($attempt < $max_retries) {
            $result = $callback();
            
            if (!is_wp_error($result)) {
                return $result;
            }
            
            $attempt++;
            
            if ($attempt >= $max_retries) {
                return $result;
            }
            
            // Check if retryable
            if (!$this->is_retryable_error($result)) {
                return $result;
            }
            
            // Exponential backoff with jitter
            $delay = self::RETRY_BASE_DELAY * pow(2, $attempt - 1);
            $delay += rand(0, 50000); // Jitter
            
            $this->log_message('debug', 'retry', 'N/A', 
                sprintf('Retry attempt %d/%d after %dμs', $attempt, $max_retries, $delay));
            
            usleep($delay);
        }
        
        return $result;
    }

    /**
     * Check if error is retryable.
     *
     * @param \WP_Error $error Error object.
     * @return bool
     */
    private function is_retryable_error(\WP_Error $error): bool {
        $retryable_codes = [
            'timeout',
            'http_request_failed',
            'rate_limited',
            'service_unavailable',
            'gateway_timeout',
        ];
        
        $error_code = $error->get_error_code();
        
        return in_array($error_code, $retryable_codes, true);
    }

    /**
     * Start database transaction.
     *
     * @return void
     */
    private function start_transaction(): void {
        if (!$this->in_transaction) {
            $this->wpdb->query('START TRANSACTION');
            $this->in_transaction = true;
        }
    }

    /**
     * Commit database transaction.
     *
     * @return void
     */
    private function commit_transaction(): void {
        if ($this->in_transaction) {
            $this->wpdb->query('COMMIT');
            $this->in_transaction = false;
        }
    }

    /**
     * Rollback database transaction.
     *
     * @return void
     */
    private function rollback_transaction(): void {
        if ($this->in_transaction) {
            $this->wpdb->query('ROLLBACK');
            $this->in_transaction = false;
        }
    }

    /**
     * Log message with level.
     *
     * @param string $level   Log level (error, warning, info, debug).
     * @param string $context Context identifier.
     * @param string $ip      IP address or 'N/A'.
     * @param string $message Message.
     * @return void
     */
    private function log_message(string $level, string $context, string $ip, string $message): void {
        $log_enabled = defined('WP_DEBUG') && WP_DEBUG;
        $debug_enabled = defined('BOTFEND_DEBUG') && BOTFEND_DEBUG;
        
        // Always log errors
        if ($level === 'error') {
            $log_enabled = true;
        }
        
        // Debug requires explicit debug flag
        if ($level === 'debug' && !$debug_enabled) {
            return;
        }
        
        if (!$log_enabled) {
            return;
        }
        
        $log = sprintf(
            '[BotFend CF] [%s] [%s] IP: %s - %s',
            strtoupper($level),
            $context,
            $ip,
            $message
        );
        
        error_log($log);
        
        /**
         * Fires when a log message is recorded.
         *
         * @since 4.9.0
         */
        do_action('botfend_cf_log', $level, $context, $ip, $message);
    }
}