<?php

/**
 * Database Optimization Module
 *
 * Cleans up database tables and analyzes autoloaded options
 *
 * @package ProRank\SEO\Modules\Performance
 * @since   1.0.0
 */

declare(strict_types=1);

namespace ProRank\SEO\Modules\Performance;

use ProRank\SEO\Core\FilesystemHelper;
use ProRank\SEO\Modules\BaseModule;

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


/**
 * DatabaseOptimizationModule class
 */


class DatabaseOptimizationModule extends BaseModule {

    /**
     * Required tier for this module
     */
    protected string $feature_tier = 'free';
    
    /**
     * Database tables to clean
     *
     * @var array
     */
    private const CLEANUP_TABLES = [
        'posts' => [
            'revisions' => "post_type = 'revision'",
            'auto_drafts' => "post_status = 'auto-draft'",
            'trash' => "post_status = 'trash'",
        ],
        'postmeta' => [
            'orphaned' => "post_id NOT IN (SELECT ID FROM {prefix}posts)",
            'duplicates' => "meta_id NOT IN (SELECT * FROM (SELECT MIN(meta_id) FROM {prefix}postmeta GROUP BY post_id, meta_key) AS temp)",
        ],
        'comments' => [
            'spam' => "comment_approved = 'spam'",
            'trash' => "comment_approved = 'trash'",
            'pending' => "comment_approved = '0'",
            'pingbacks' => "comment_type = 'pingback'",
            'trackbacks' => "comment_type = 'trackback'",
        ],
        'commentmeta' => [
            'orphaned' => "comment_id NOT IN (SELECT comment_ID FROM {prefix}comments)",
        ],
        'termmeta' => [
            'orphaned' => "term_id NOT IN (SELECT term_id FROM {prefix}terms)",
        ],
        'usermeta' => [
            'orphaned' => "user_id NOT IN (SELECT ID FROM {prefix}users)",
        ],
        'term_relationships' => [
            'orphaned' => "object_id NOT IN (SELECT ID FROM {prefix}posts)",
        ],
        'options' => [
            'transients' => "option_name LIKE '_transient_%' OR option_name LIKE '_site_transient_%'",
            'expired_transients' => "option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP()",
        ],
    ];

    /**
     * Map cleanup setting keys to executable cleanup types.
     *
     * @var array<string,string>
     */
    private const CLEANUP_ITEM_MAP = [
        'revisions' => 'revisions',
        'drafts' => 'auto_drafts',
        'trash' => 'trash',
        'spam_comments' => 'spam',
        'unapproved_comments' => 'pending',
        'transients' => 'transients',
        'expired_transients' => 'expired_transients',
        'orphaned_meta' => 'orphaned',
        'action_scheduler' => 'action_scheduler',
        'wpmailsmtp_logs' => 'wpmailsmtp_logs',
    ];

    /**
     * Default retention for Action Scheduler history cleanup.
     */
    private const ACTION_SCHEDULER_RETENTION_DAYS = 7;

    /**
     * Default retention for WP Mail SMTP logs.
     */
    private const WPMAILSMTP_LOG_RETENTION_DAYS = 30;

    /**
     * Only legacy disk-backed engines are safe candidates for conversion.
     *
     * @var string[]
     */
    private const SAFE_INNODB_CONVERSION_ENGINES = [
        'MyISAM',
        'Aria',
    ];

    /**
     * Minimum free space that should surface an Optimize action on its own.
     *
     * Small single-digit MB overhead on large InnoDB tables is usually not
     * worth surfacing as a primary action.
     */
    private const OPTIMIZE_OVERHEAD_MIN_BYTES = 8388608; // 8 MB

    /**
     * Relative free-space ratio that also makes Optimize worth surfacing.
     */
    private const OPTIMIZE_OVERHEAD_RATIO = 0.05; // 5%
    
    /**
     * Constructor
     */
    public function __construct() {
        $this->slug = 'database_optimization';
        $this->name = 'Database Optimization';
        $this->description = 'Clean up database and optimize autoloaded data';
// Manual cleanup is free, auto-cleanup is pro
        $this->parent_slug = 'performance';
    }
    
    /**
     * Initialize module hooks
     *
     * @return void
     */
    public function init_hooks(): void {
        // Check if module should run
        if (!$this->should_run()) {
            return;
        }

        // Always register the cleanup callback; scheduling is controlled by settings.
        add_action('prorank_database_cleanup', [$this, 'run_scheduled_cleanup']);
        add_action('init', [$this, 'schedule_cleanup']);

        // Autoload guard runs daily to keep autoload bloat under control
        add_action('init', [$this, 'schedule_autoload_guard']);
        add_action('prorank_autoload_guard', [$this, 'run_autoload_guard']);
        
        // AJAX handlers
        add_action('wp_ajax_prorank_analyze_database', [$this, 'handle_analyze_database']);
        add_action('wp_ajax_prorank_cleanup_database', [$this, 'handle_cleanup_database']);
        add_action('wp_ajax_prorank_analyze_autoload', [$this, 'handle_analyze_autoload']);
        
        // WP-CLI commands
        if (defined('WP_CLI') && WP_CLI) {
            \WP_CLI::add_command('prorank db-optimize', [$this, 'cli_optimize']);
        }
    }
    
    /**
     * Check if module should run
     *
     * @return bool
     */
    private function should_run(): bool {
        return $this->is_enabled();
    }
    
    /**
     * Schedule cleanup task
     *
     * @return void
     */
    public function schedule_cleanup(): void {
        $next = wp_next_scheduled('prorank_database_cleanup');

        if (!$this->is_auto_cleanup_enabled()) {
            if ($next) {
                wp_unschedule_event($next, 'prorank_database_cleanup');
            }
            return;
        }

        $frequency = $this->get_cleanup_frequency();
        $schedules = wp_get_schedules();
        if (!isset($schedules[$frequency])) {
            $frequency = 'weekly';
        }

        if (!$next) {
            wp_schedule_event(time() + MINUTE_IN_SECONDS, $frequency, 'prorank_database_cleanup');
            return;
        }

        if (function_exists('wp_get_scheduled_event')) {
            $event = wp_get_scheduled_event('prorank_database_cleanup');
            if ($event && isset($event->schedule) && $event->schedule !== $frequency) {
                wp_unschedule_event($next, 'prorank_database_cleanup');
                wp_schedule_event(time() + MINUTE_IN_SECONDS, $frequency, 'prorank_database_cleanup');
            }
        }
    }
    
    /**
     * Run scheduled cleanup
     *
     * @return void
     */
    public function run_scheduled_cleanup(): void {
        $types = $this->get_auto_cleanup_types();
        
        if (empty($types)) {
            return;
        }
        
        foreach ($types as $type) {
            $this->cleanup_database_type($type);
        }
        
        // Log cleanup
        update_option('prorank_last_db_cleanup', current_time('mysql'), false);
    }

    /**
     * Determine whether automatic cleanup is enabled.
     *
     * Supports both legacy and current settings keys.
     *
     * @return bool
     */
    private function is_auto_cleanup_enabled(): bool {
        $enabled = $this->get_setting('auto_cleanup_enabled', null);
        if ($enabled === null) {
            $enabled = $this->get_setting('auto_cleanup', false);
        }

        return (bool) $enabled;
    }

    /**
     * Resolve cleanup frequency from settings (legacy + current keys).
     *
     * @return string
     */
    private function get_cleanup_frequency(): string {
        $frequency = (string) $this->get_setting('cleanup_schedule', '');
        if ($frequency === '') {
            $frequency = (string) $this->get_setting('cleanup_frequency', 'weekly');
        }

        return $frequency !== '' ? $frequency : 'weekly';
    }

    /**
     * Resolve cleanup types from settings (legacy + current keys).
     *
     * @return array<int,string>
     */
    private function get_auto_cleanup_types(): array {
        $legacy_types = $this->get_setting('auto_cleanup_types', null);
        if (is_array($legacy_types) && !empty($legacy_types)) {
            return array_values(array_unique(array_filter(array_map('strval', $legacy_types))));
        }

        $cleanup_items = $this->get_setting('cleanup_items', []);
        return $this->get_cleanup_types_from_items(is_array($cleanup_items) ? $cleanup_items : []);
    }

    /**
     * Resolve cleanup types from an item-selection array.
     *
     * @param array<string,mixed> $cleanup_items Cleanup item flags from settings/UI.
     * @return array<int,string>
     */
    public function get_cleanup_types_from_items(array $cleanup_items): array {
        if (empty($cleanup_items)) {
            return [];
        }

        $types = [];
        foreach (self::CLEANUP_ITEM_MAP as $setting_key => $cleanup_type) {
            if (!empty($cleanup_items[$setting_key])) {
                $types[] = $cleanup_type;
            }
        }

        return array_values(array_unique($types));
    }

    /**
     * Whether the current engine is a safe candidate for InnoDB conversion.
     *
     * @param string $engine Storage engine name.
     * @return bool
     */
    public function can_convert_to_innodb_engine(string $engine): bool {
        return in_array($engine, self::SAFE_INNODB_CONVERSION_ENGINES, true);
    }

    /**
     * Explain the recommended InnoDB conversion behavior for a table engine.
     *
     * @param string $engine Storage engine name.
     * @return string
     */
    public function get_innodb_conversion_note(string $engine): string {
        if ($engine === '' || strtolower($engine) === 'innodb') {
            return __('Already using the recommended InnoDB engine.', 'prorank-seo');
        }

        if ($this->can_convert_to_innodb_engine($engine)) {
            return __('Safe to convert this legacy disk-based engine to InnoDB.', 'prorank-seo');
        }

        if (strcasecmp($engine, 'MEMORY') === 0) {
            return __('Keep MEMORY tables on their original engine unless the plugin vendor documents otherwise.', 'prorank-seo');
        }

        return __('Keep this engine unless the plugin vendor explicitly recommends an InnoDB conversion.', 'prorank-seo');
    }

    /**
     * Get the option autoload values WordPress currently treats as enabled.
     *
     * @return string[]
     */
    private function get_autoload_enabled_values(): array {
        if (function_exists('wp_autoload_values_to_autoload')) {
            $values = wp_autoload_values_to_autoload();
            if (is_array($values) && !empty($values)) {
                return array_values(array_unique(array_map('strval', $values)));
            }
        }

        return ['yes', 'on', 'auto-on', 'auto'];
    }

    /**
     * Build a prepared SQL fragment for enabled autoload values.
     *
     * @param string $column Column name.
     * @return array{0:string,1:array}
     */
    private function get_autoload_enabled_sql(string $column = 'autoload'): array {
        $values = $this->get_autoload_enabled_values();
        $placeholders = implode(', ', array_fill(0, count($values), '%s'));

        return [
            "{$column} IN ({$placeholders})",
            $values,
        ];
    }

    /**
     * Schedule daily autoload guard scan
     *
     * @return void
     */
    public function schedule_autoload_guard(): void {
        if (!wp_next_scheduled('prorank_autoload_guard')) {
            $frequency = apply_filters('prorank_autoload_guard_frequency', 'daily');
            wp_schedule_event(time() + HOUR_IN_SECONDS, $frequency, 'prorank_autoload_guard');
        }
    }

    /**
     * Run autoload guard to disable oversized autoloaded options
     *
     * @return void
     */
    public function run_autoload_guard(): void {
        $threshold_kb = (int) apply_filters('prorank_autoload_guard_threshold_kb', 256);
        $oversized = $this->find_oversized_autoload_options($threshold_kb);
        $disabled = [];

        foreach ($oversized as $option) {
            if ($this->is_core_option($option['name'])) {
                continue;
            }

            $updated = $this->update_option_autoload($option['name'], 'no');
            if ($updated) {
                $disabled[] = $option;
            }
        }

        $report = [
            'last_run' => current_time('mysql'),
            'threshold_kb' => $threshold_kb,
            'found' => count($oversized),
            'disabled_count' => count($disabled),
            'disabled' => array_map(function($item) {
                return [
                    'name' => $item['name'],
                    'size_kb' => $item['size_kb'],
                ];
            }, array_slice($disabled, 0, 25)),
        ];

        update_option('prorank_autoload_guard_report', $report, false);
    }

    /**
     * Find oversized autoloaded options
     *
     * @param int $threshold_kb Threshold in KB
     * @return array Oversized options
     */
    public function find_oversized_autoload_options(int $threshold_kb = 256): array {
        global $wpdb;

        $threshold_bytes = $threshold_kb * 1024;
        [$autoload_sql, $autoload_values] = $this->get_autoload_enabled_sql();
        $oversized = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT option_name, LENGTH(option_value) as size
                 FROM {$wpdb->options}
                 WHERE {$autoload_sql} AND LENGTH(option_value) >= %d
                 ORDER BY size DESC",
                ...array_merge($autoload_values, [$threshold_bytes])
            )
        );

        $options = [];
        foreach ($oversized as $row) {
            $options[] = [
                'name' => $row->option_name,
                'size_kb' => round($row->size / 1024, 2),
            ];
        }

        return $options;
    }

    /**
     * Get last autoload guard report
     *
     * @return array Report data
     */
    public function get_autoload_guard_report(): array {
        $report = get_option('prorank_autoload_guard_report', []);
        if (!is_array($report)) {
            $report = [];
        }

        $defaults = [
            'last_run' => null,
            'threshold_kb' => (int) apply_filters('prorank_autoload_guard_threshold_kb', 256),
            'found' => 0,
            'disabled_count' => 0,
            'disabled' => [],
        ];

        return wp_parse_args($report, $defaults);
    }
    
    /**
     * Analyze database for optimization opportunities
     *
     * @return array Analysis results
     */
    public function analyze_database(): array {
        global $wpdb;
        
        $analysis = [
            'total_size' => 0,
            'tables' => [],
            'cleanup_potential' => [],
            'recommendations' => [],
        ];
        
        // Get database size
        $db_name = DB_NAME;
        $size_query = $wpdb->get_results(
            "SELECT 
                table_name AS 'table',
                ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'size_mb'
             FROM information_schema.TABLES
             WHERE table_schema = '$db_name'
             ORDER BY (data_length + index_length) DESC"
        );
        
        foreach ($size_query as $table) {
            $analysis['tables'][$table->table] = (float) $table->size_mb;
            $analysis['total_size'] += (float) $table->size_mb;
        }
        
        // Analyze cleanup potential
        foreach (self::CLEANUP_TABLES as $table => $conditions) {
            $table_name = $wpdb->prefix . $table;
            
            foreach ($conditions as $type => $where) {
                $where = str_replace('{prefix}', $wpdb->prefix, $where);
                $count = $wpdb->get_var("SELECT COUNT(*) FROM $table_name WHERE $where");
                
                if ($count > 0) {
                    $analysis['cleanup_potential'][$type] = [
                        'count' => (int) $count,
                        'table' => $table,
                        'description' => $this->get_cleanup_description($type),
                    ];
                }
            }
        }
        
        // Add recommendations
        if ($analysis['cleanup_potential']) {
            $analysis['recommendations'][] = __('Database cleanup can free up space and improve performance', 'prorank-seo');
        }
        
        // Check for large tables
        foreach ($analysis['tables'] as $table => $size) {
            if ($size > 100) {
                $analysis['recommendations'][] = sprintf(
                    /* translators: %1$s: table name, %2$.2f: size in MB */
                    __('Table %1$s is large (%2$.2f MB), consider optimization', 'prorank-seo'),
                    $table,
                    $size
                );
            }
        }
        
        return $analysis;
    }
    
    /**
     * Analyze autoloaded options
     *
     * @return array Analysis results
     */
    public function analyze_autoload(): array {
        global $wpdb;
        
        $analysis = [
            'total_size' => 0,
            'total_count' => 0,
            'large_options' => [],
            'suspicious_options' => [],
            'recommendations' => [],
        ];
        
        // Get autoloaded options
        [$autoload_sql, $autoload_values] = $this->get_autoload_enabled_sql();
        $autoload_query = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT option_name, autoload, LENGTH(option_value) as size
                 FROM {$wpdb->options}
                 WHERE {$autoload_sql}
                 ORDER BY size DESC",
                ...$autoload_values
            )
        );
        
        foreach ($autoload_query as $option) {
            $size_kb = $option->size / 1024;
            $analysis['total_size'] += $size_kb;
            $analysis['total_count']++;
            
            // Flag large options (> 100KB)
            if ($size_kb > 100) {
                $analysis['large_options'][] = [
                    'name' => $option->option_name,
                    'size' => round($size_kb, 2),
                ];
            }
            
            // Check for suspicious patterns
            if ($this->is_suspicious_option($option->option_name)) {
                $analysis['suspicious_options'][] = [
                    'name' => $option->option_name,
                    'size' => round($size_kb, 2),
                    'reason' => $this->get_suspicious_reason($option->option_name),
                ];
            }
        }
        
        // Add recommendations
        if ($analysis['total_size'] > 1000) { // > 1MB
            $analysis['recommendations'][] = sprintf(
                /* translators: %.2f: size in KB */
                __('Total autoloaded data is %1$.2f KB, consider reducing', 'prorank-seo'),
                $analysis['total_size']
            );
        }
        
        if (count($analysis['large_options']) > 0) {
            $analysis['recommendations'][] = __('Large autoloaded options found, consider disabling autoload', 'prorank-seo');
        }
        
        if (count($analysis['suspicious_options']) > 0) {
            $analysis['recommendations'][] = __('Potentially unnecessary autoloaded options detected', 'prorank-seo');
        }

        // Autoload guard status
        $analysis['guard'] = $this->get_autoload_guard_report();
        
        return $analysis;
    }
    
    /**
     * Clean up database by type
     *
     * @param string $type Cleanup type
     * @return array Cleanup results
     */
    public function cleanup_database_type(string $type): array {
        global $wpdb;

        if ($type === 'action_scheduler') {
            return $this->cleanup_action_scheduler_history($this->get_action_scheduler_retention_days());
        }

        if ($type === 'wpmailsmtp_logs') {
            return $this->cleanup_wpmailsmtp_email_logs($this->get_wpmailsmtp_log_retention_days());
        }
        
        $results = [
            'success' => false,
            'partial_success' => false,
            'deleted' => 0,
            'error' => null,
            'table' => null,
            'tables' => [],
            'optimized' => false,
            'optimized_tables' => [],
        ];
        
        $targets = [];
        foreach (self::CLEANUP_TABLES as $table_name => $conditions) {
            if (isset($conditions[$type])) {
                $targets[] = [
                    'table' => $wpdb->prefix . $table_name,
                    'condition' => str_replace('{prefix}', $wpdb->prefix, $conditions[$type]),
                ];
            }
        }
        
        if (empty($targets)) {
            $results['error'] = __('Invalid cleanup type', 'prorank-seo');
            return $results;
        }

        $results['table'] = $targets[0]['table'];
        $errors = [];
        $successful_targets = 0;

        foreach ($targets as $target) {
            $table = $target['table'];
            $condition = $target['condition'];

            try {
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
                $deleted = $wpdb->query("DELETE FROM $table WHERE $condition");

                if ($deleted === false) {
                    $errors[] = $wpdb->last_error ?: sprintf(__('Cleanup failed for %s', 'prorank-seo'), $table);
                    $results['tables'][] = [
                        'table' => $table,
                        'deleted' => 0,
                        'optimized' => false,
                        'error' => $wpdb->last_error ?: null,
                    ];
                    continue;
                }

                $successful_targets++;
                $table_result = [
                    'table' => $table,
                    'deleted' => (int) $deleted,
                    'optimized' => false,
                    'error' => null,
                ];
                $results['deleted'] += (int) $deleted;

                if ($deleted > 0) {
                    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
                    $table_result['optimized'] = $wpdb->query("OPTIMIZE TABLE $table") !== false;
                    if ($table_result['optimized']) {
                        $results['optimized_tables'][] = $table;
                    }
                }

                $results['tables'][] = $table_result;
            } catch (\Exception $e) {
                $errors[] = $e->getMessage();
                $results['tables'][] = [
                    'table' => $table,
                    'deleted' => 0,
                    'optimized' => false,
                    'error' => $e->getMessage(),
                ];
            }
        }

        $results['optimized'] = !empty($results['optimized_tables']);
        $results['success'] = $successful_targets > 0 && empty($errors);
        $results['partial_success'] = $successful_targets > 0 && !empty($errors);

        if (!empty($errors)) {
            $results['error'] = implode(' | ', array_unique(array_filter($errors)));
        }

        if ($successful_targets > 0 && $results['error'] === null) {
            $results['success'] = true;
        }

        return $results;
    }

    /**
     * Clean completed/failed/canceled Action Scheduler history older than the configured retention window.
     *
     * @param int $retention_days Retention window in days.
     * @return array Cleanup results.
     */
    private function cleanup_action_scheduler_history(int $retention_days): array {
        global $wpdb;

        $actions_table = $wpdb->prefix . 'actionscheduler_actions';
        $logs_table = $wpdb->prefix . 'actionscheduler_logs';

        $results = [
            'success' => false,
            'partial_success' => false,
            'deleted' => 0,
            'error' => null,
            'table' => $actions_table,
            'tables' => [],
            'optimized' => false,
            'optimized_tables' => [],
        ];

        if (!$this->table_exists($actions_table) || !$this->table_exists($logs_table)) {
            $results['error'] = __('Action Scheduler tables were not found.', 'prorank-seo');
            return $results;
        }

        $cutoff = gmdate('Y-m-d H:i:s', time() - ($retention_days * DAY_IN_SECONDS));
        $statuses = ['complete', 'failed', 'canceled'];

        $logs_deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE logs
                 FROM `{$logs_table}` AS logs
                 INNER JOIN `{$actions_table}` AS actions ON actions.action_id = logs.action_id
                 WHERE actions.status IN (%s, %s, %s)
                   AND actions.scheduled_date_gmt <> %s
                   AND actions.scheduled_date_gmt < %s",
                $statuses[0],
                $statuses[1],
                $statuses[2],
                '0000-00-00 00:00:00',
                $cutoff
            )
        );

        $actions_deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM `{$actions_table}`
                 WHERE status IN (%s, %s, %s)
                   AND scheduled_date_gmt <> %s
                   AND scheduled_date_gmt < %s",
                $statuses[0],
                $statuses[1],
                $statuses[2],
                '0000-00-00 00:00:00',
                $cutoff
            )
        );

        $results['tables'][] = [
            'table' => $logs_table,
            'deleted' => $logs_deleted === false ? 0 : (int) $logs_deleted,
            'optimized' => false,
            'error' => $logs_deleted === false ? ($wpdb->last_error ?: __('Failed to delete Action Scheduler logs.', 'prorank-seo')) : null,
        ];
        $results['tables'][] = [
            'table' => $actions_table,
            'deleted' => $actions_deleted === false ? 0 : (int) $actions_deleted,
            'optimized' => false,
            'error' => $actions_deleted === false ? ($wpdb->last_error ?: __('Failed to delete Action Scheduler actions.', 'prorank-seo')) : null,
        ];

        $results['deleted'] = max(0, (int) ($logs_deleted === false ? 0 : $logs_deleted)) + max(0, (int) ($actions_deleted === false ? 0 : $actions_deleted));
        $has_errors = $logs_deleted === false || $actions_deleted === false;
        $has_success = ($logs_deleted !== false && (int) $logs_deleted >= 0) || ($actions_deleted !== false && (int) $actions_deleted >= 0);

        $results['success'] = $has_success && !$has_errors;
        $results['partial_success'] = $has_success && $has_errors;
        if ($has_errors) {
            $results['error'] = implode(' | ', array_filter(array_map(
                static function(array $table_result): ?string {
                    return $table_result['error'] ?? null;
                },
                $results['tables']
            )));
        }

        return $results;
    }

    /**
     * Clean WP Mail SMTP email logs older than the configured retention window.
     *
     * @param int $retention_days Retention window in days.
     * @return array Cleanup results.
     */
    private function cleanup_wpmailsmtp_email_logs(int $retention_days): array {
        global $wpdb;

        $table = $wpdb->prefix . 'wpmailsmtp_emails_log';
        $results = [
            'success' => false,
            'partial_success' => false,
            'deleted' => 0,
            'error' => null,
            'table' => $table,
            'tables' => [],
            'optimized' => false,
            'optimized_tables' => [],
        ];

        if (!$this->table_exists($table)) {
            $results['error'] = __('WP Mail SMTP email log table was not found.', 'prorank-seo');
            return $results;
        }

        $cutoff = gmdate('Y-m-d H:i:s', time() - ($retention_days * DAY_IN_SECONDS));
        $deleted = $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM `{$table}` WHERE date_sent < %s",
                $cutoff
            )
        );

        $results['tables'][] = [
            'table' => $table,
            'deleted' => $deleted === false ? 0 : (int) $deleted,
            'optimized' => false,
            'error' => $deleted === false ? ($wpdb->last_error ?: __('Failed to delete WP Mail SMTP email logs.', 'prorank-seo')) : null,
        ];
        $results['deleted'] = $deleted === false ? 0 : (int) $deleted;
        $results['success'] = $deleted !== false;
        if ($deleted === false) {
            $results['error'] = $wpdb->last_error ?: __('Failed to delete WP Mail SMTP email logs.', 'prorank-seo');
        }

        return $results;
    }
    
    /**
     * Get cleanup description
     *
     * @param string $type Cleanup type
     * @return string Description
     */
    private function get_cleanup_description(string $type): string {
        $descriptions = [
            'revisions' => __('Post revisions', 'prorank-seo'),
            'auto_drafts' => __('Auto-draft posts', 'prorank-seo'),
            'trash' => __('Trashed posts and comments', 'prorank-seo'),
            'spam' => __('Comments marked as spam', 'prorank-seo'),
            'pending' => __('Pending / unapproved comments', 'prorank-seo'),
            'pingbacks' => __('Pingbacks', 'prorank-seo'),
            'trackbacks' => __('Trackbacks', 'prorank-seo'),
            'orphaned' => __('Orphaned metadata and relationships', 'prorank-seo'),
            'duplicates' => __('Duplicate metadata', 'prorank-seo'),
            'transients' => __('All transients', 'prorank-seo'),
            'expired_transients' => __('Expired transients', 'prorank-seo'),
            'action_scheduler' => __('Action Scheduler history', 'prorank-seo'),
            'wpmailsmtp_logs' => __('WP Mail SMTP email logs', 'prorank-seo'),
        ];
        
        return $descriptions[$type] ?? $type;
    }

    /**
     * Check whether a table exists in the current database.
     *
     * @param string $table_name Table name.
     * @return bool
     */
    private function table_exists(string $table_name): bool {
        global $wpdb;

        $found = $wpdb->get_var(
            $wpdb->prepare(
                'SHOW TABLES LIKE %s',
                $table_name
            )
        );

        return $found === $table_name;
    }

    /**
     * Resolve the configured Action Scheduler retention window.
     *
     * @return int
     */
    private function get_action_scheduler_retention_days(): int {
        return $this->get_retention_days_setting('action_scheduler_retention_days', self::ACTION_SCHEDULER_RETENTION_DAYS);
    }

    /**
     * Resolve the configured WP Mail SMTP log retention window.
     *
     * @return int
     */
    private function get_wpmailsmtp_log_retention_days(): int {
        return $this->get_retention_days_setting('wpmailsmtp_log_retention_days', self::WPMAILSMTP_LOG_RETENTION_DAYS);
    }

    /**
     * Sanitize retention settings to a safe range.
     *
     * @param string $setting_key Setting key.
     * @param int    $default     Default retention window.
     * @return int
     */
    private function get_retention_days_setting(string $setting_key, int $default): int {
        $days = absint($this->get_setting($setting_key, $default));
        if ($days < 1) {
            $days = $default;
        }

        return min(365, $days);
    }
    
    /**
     * Check if option name is suspicious
     *
     * @param string $option_name Option name
     * @return bool
     */
    private function is_suspicious_option(string $option_name): bool {
        $patterns = [
            'session' => '/^_wp_session_/',
            'transient' => '/^_(site_)?transient(_timeout)?_/',
            'cache' => '/_cache$/',
            'temporary' => '/_temp_/',
        ];
        
        foreach ($patterns as $pattern) {
            if (preg_match($pattern, $option_name)) {
                return true;
            }
        }
        
        return false;
    }
    
    /**
     * Get reason why option is suspicious
     *
     * @param string $option_name Option name
     * @return string Reason
     */
    private function get_suspicious_reason(string $option_name): string {
        if (strpos($option_name, '_wp_session_') === 0) {
            return __('Session data should not be autoloaded', 'prorank-seo');
        }
        
        if (strpos($option_name, '_transient_') === 0 || strpos($option_name, '_site_transient_') === 0 || strpos($option_name, '_transient_timeout_') === 0 || strpos($option_name, '_site_transient_timeout_') === 0) {
            return __('Transients should not be autoloaded', 'prorank-seo');
        }
        
        if (substr($option_name, -6) === '_cache') {
            return __('Cache data typically should not be autoloaded', 'prorank-seo');
        }
        
        return __('Temporary data should not be autoloaded', 'prorank-seo');
    }
    
    /**
     * Handle analyze database AJAX request
     *
     * @return void
     */
    public function handle_analyze_database(): void {
        // Check nonce
        if (!check_ajax_referer('prorank_database_optimization', 'nonce', false)) {
            wp_send_json_error(__('Invalid nonce', 'prorank-seo'));
        }
        
        // Check capabilities
        if (!current_user_can('manage_options')) {
            wp_send_json_error(__('Insufficient permissions', 'prorank-seo'));
        }
        
        $analysis = $this->analyze_database();
        
        wp_send_json_success($analysis);
    }
    
    /**
     * Handle cleanup database AJAX request
     *
     * @return void
     */
    public function handle_cleanup_database(): void {
        // Check nonce
        if (!check_ajax_referer('prorank_database_optimization', 'nonce', false)) {
            wp_send_json_error(__('Invalid nonce', 'prorank-seo'));
        }
        
        // Check capabilities
        if (!current_user_can('manage_options')) {
            wp_send_json_error(__('Insufficient permissions', 'prorank-seo'));
        }
        
        $type = sanitize_text_field($_POST['type'] ?? '');
        if (empty($type)) {
            wp_send_json_error(__('Cleanup type required', 'prorank-seo'));
        }
        
        $results = $this->cleanup_database_type($type);
        
        if ($results['success']) {
            wp_send_json_success([
                'message' => sprintf(
                    /* translators: %d: number of items deleted */
                    __('Deleted %1$d items', 'prorank-seo'),
                    $results['deleted']
                ),
                'deleted' => $results['deleted'],
            ]);
        } else {
            wp_send_json_error($results['error'] ?? __('Cleanup failed', 'prorank-seo'));
        }
    }
    
    /**
     * Handle analyze autoload AJAX request
     *
     * @return void
     */
    public function handle_analyze_autoload(): void {
        // Check nonce
        if (!check_ajax_referer('prorank_database_optimization', 'nonce', false)) {
            wp_send_json_error(__('Invalid nonce', 'prorank-seo'));
        }
        
        // Check capabilities
        if (!current_user_can('manage_options')) {
            wp_send_json_error(__('Insufficient permissions', 'prorank-seo'));
        }
        
        $analysis = $this->analyze_autoload();
        
        wp_send_json_success($analysis);
    }
    
    /**
     * WP-CLI optimize command
     *
     * @param array $args Command arguments
     * @param array $assoc_args Associative arguments
     * @return void
     */
    public function cli_optimize(array $args, array $assoc_args): void {
        \WP_CLI::line('Analyzing database...');
        
        $analysis = $this->analyze_database();
        
        \WP_CLI::line(sprintf('Total database size: %.2f MB', $analysis['total_size']));
        \WP_CLI::line('');
        
        if (!empty($analysis['cleanup_potential'])) {
            \WP_CLI::line('Cleanup opportunities:');
            foreach ($analysis['cleanup_potential'] as $type => $data) {
                \WP_CLI::line(sprintf('- %s: %d items', $data['description'], $data['count']));
            }
            
            if (isset($assoc_args['cleanup']) && $assoc_args['cleanup']) {
                \WP_CLI::line('');
                \WP_CLI::line('Running cleanup...');
                
                foreach (array_keys($analysis['cleanup_potential']) as $type) {
                    $results = $this->cleanup_database_type($type);
                    if ($results['success']) {
                        \WP_CLI::success(sprintf('Cleaned %d %s', $results['deleted'], $type));
                    } else {
                        \WP_CLI::error($results['error'], false);
                    }
                }
            }
        } else {
            \WP_CLI::success('No cleanup needed!');
        }
    }
    
    /**
     * Get module statistics
     *
     * @return array Statistics
     */
    public function get_stats(): array {
        $stats = [
            'last_cleanup' => get_option('prorank_last_db_cleanup', null),
            'database_analysis' => $this->analyze_database(),
            'autoload_analysis' => $this->analyze_autoload(),
        ];
        
        return $stats;
    }
    
    /**
     * Get real-time database statistics
     *
     * @return array Database statistics
     */
    public function get_database_stats(): array {
        global $wpdb;
        
        $stats = [
            'posts' => [
                'revisions' => 0,
                'auto_drafts' => 0,
                'trash' => 0,
            ],
            'comments' => [
                'spam' => 0,
                'trash' => 0,
                'pending' => 0,
                'pingbacks' => 0,
                'trackbacks' => 0,
            ],
            'transients' => [
                'all' => 0,
                'expired' => 0,
            ],
            'orphaned' => [
                'postmeta' => 0,
                'commentmeta' => 0,
                'usermeta' => 0,
                'termmeta' => 0,
                'relationships' => 0,
            ],
            'database' => [
                'total_size' => 0,
                'tables_with_overhead' => 0,
                'total_overhead' => 0,
            ],
            'server' => [
                'php_version' => PHP_VERSION,
                'mysql_version' => $wpdb->db_version(),
                'memory_limit' => ini_get('memory_limit'),
                'max_execution_time' => ini_get('max_execution_time'),
                'upload_max_filesize' => ini_get('upload_max_filesize'),
                'post_max_size' => ini_get('post_max_size'),
            ],
        ];
        
        // Count post revisions
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'");
        $stats['posts']['revisions'] = $count !== null ? (int) $count : 0;
        
        // Count auto-drafts
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'");
        $stats['posts']['auto_drafts'] = $count !== null ? (int) $count : 0;
        
        // Count trashed posts
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'");
        $stats['posts']['trash'] = $count !== null ? (int) $count : 0;
        
        // Count spam comments
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'spam'");
        $stats['comments']['spam'] = $count !== null ? (int) $count : 0;
        
        // Count trashed comments
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'trash'");
        $stats['comments']['trash'] = $count !== null ? (int) $count : 0;
        
        // Count pending comments
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = '0'");
        $stats['comments']['pending'] = $count !== null ? (int) $count : 0;
        
        // Count pingbacks
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_type = 'pingback'");
        $stats['comments']['pingbacks'] = $count !== null ? (int) $count : 0;
        
        // Count trackbacks
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_type = 'trackback'");
        $stats['comments']['trackbacks'] = $count !== null ? (int) $count : 0;
        
        // Count orphaned postmeta
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE post_id NOT IN (SELECT ID FROM {$wpdb->posts})");
        $stats['orphaned']['postmeta'] = $count !== null ? (int) $count : 0;
        
        // Count orphaned commentmeta
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->commentmeta} WHERE comment_id NOT IN (SELECT comment_ID FROM {$wpdb->comments})");
        $stats['orphaned']['commentmeta'] = $count !== null ? (int) $count : 0;
        
        // Count orphaned usermeta
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->usermeta} WHERE user_id NOT IN (SELECT ID FROM {$wpdb->users})");
        $stats['orphaned']['usermeta'] = $count !== null ? (int) $count : 0;
        
        // Count orphaned termmeta
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->termmeta} WHERE term_id NOT IN (SELECT term_id FROM {$wpdb->terms})");
        $stats['orphaned']['termmeta'] = $count !== null ? (int) $count : 0;
        
        // Count orphaned relationships
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->term_relationships} WHERE object_id NOT IN (SELECT ID FROM {$wpdb->posts})");
        $stats['orphaned']['relationships'] = $count !== null ? (int) $count : 0;
        
        // Count all transients
        $count = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->options} 
             WHERE option_name LIKE '_transient_%' 
             OR option_name LIKE '_site_transient_%'"
        );
        $stats['transients']['all'] = $count !== null ? (int) $count : 0;
        
        // Count expired transients
        $count = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->options} 
             WHERE option_name LIKE '_transient_timeout_%' 
             AND option_value < UNIX_TIMESTAMP()"
        );
        $stats['transients']['expired'] = $count !== null ? (int) $count : 0;
        
        // Get database size and overhead
        $db_name = DB_NAME;
        $table_status = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT 
                    table_name,
                    data_length,
                    index_length,
                    data_free
                 FROM information_schema.TABLES
                 WHERE table_schema = %s",
                $db_name
            )
        );
        
        if ($table_status) {
            foreach ($table_status as $table) {
                // Some MySQL/MariaDB setups return INFORMATION_SCHEMA fields in uppercase keys.
                $data_length = (float) ($table->DATA_LENGTH ?? $table->data_length ?? 0);
                $index_length = (float) ($table->INDEX_LENGTH ?? $table->index_length ?? 0);
                $data_free   = (float) ($table->DATA_FREE ?? $table->data_free ?? 0);

                if ($data_length > 0 || $index_length > 0) {
                    $stats['database']['total_size'] += ($data_length + $index_length);
                }

                if ($data_free > 0) {
                    $stats['database']['tables_with_overhead']++;
                    $stats['database']['total_overhead'] += $data_free;
                }
            }
        }
        
        // Convert bytes to MB
        $stats['database']['total_size'] = round($stats['database']['total_size'] / 1024 / 1024, 2);
        $stats['database']['total_overhead'] = round($stats['database']['total_overhead'] / 1024 / 1024, 2);
        
        return $stats;
    }
    
    /**
     * Get table overhead information
     *
     * @return array Table overhead details
     */
    public function get_table_overhead(): array {
        global $wpdb;
        
        $overhead_tables = [];
        $db_name = DB_NAME;
        
        $tables = $wpdb->get_results(
            "SELECT 
                table_name,
                data_length,
                index_length,
                data_free,
                engine
             FROM information_schema.TABLES
             WHERE table_schema = '$db_name'
             AND data_free > 0
             ORDER BY data_free DESC"
        );
        
        foreach ($tables as $table) {
            $data_length = (float) ($table->DATA_LENGTH ?? $table->data_length ?? 0);
            $index_length = (float) ($table->INDEX_LENGTH ?? $table->index_length ?? 0);
            $data_free = (float) ($table->DATA_FREE ?? $table->data_free ?? 0);
            $table_name = (string) ($table->TABLE_NAME ?? $table->table_name ?? '');
            $engine = (string) ($table->ENGINE ?? $table->engine ?? '');

            $total_size = $data_length + $index_length;
            $overhead_percentage = $total_size > 0 ? round(($data_free / $total_size) * 100, 2) : 0;

            $overhead_tables[] = [
                'name' => $table_name,
                'engine' => $engine,
                'data_size' => round($data_length / 1024 / 1024, 2),
                'index_size' => round($index_length / 1024 / 1024, 2),
                'overhead' => round($data_free / 1024 / 1024, 2),
                'overhead_percentage' => $overhead_percentage,
                'total_size' => round($total_size / 1024 / 1024, 2),
            ];
        }
        
        return $overhead_tables;
    }
    
    /**
     * Get all database tables with detailed information
     *
     * @return array Tables information
     */
    public function get_all_tables_info(): array {
        global $wpdb;
        
        $tables = [];
        $db_name = DB_NAME;
        $wp_prefix = $wpdb->prefix;
        
        $all_tables = $wpdb->get_results(
            "SELECT 
                table_name,
                table_rows,
                data_length,
                index_length,
                data_free,
                engine,
                create_time,
                update_time,
                table_collation
             FROM information_schema.TABLES
             WHERE table_schema = '$db_name'
             ORDER BY table_name"
        );
        
        foreach ($all_tables as $table) {
            // Handle uppercase column names from MySQL information_schema
            $data_length = $table->DATA_LENGTH ?? $table->data_length ?? 0;
            $index_length = $table->INDEX_LENGTH ?? $table->index_length ?? 0;
            $table_name = $table->TABLE_NAME ?? $table->table_name ?? '';
            $table_rows = $table->TABLE_ROWS ?? $table->table_rows ?? 0;
            $data_free = $table->DATA_FREE ?? $table->data_free ?? 0;
            $engine = $table->ENGINE ?? $table->engine ?? 'Unknown';
            $create_time = $table->CREATE_TIME ?? $table->create_time ?? null;
            $update_time = $table->UPDATE_TIME ?? $table->update_time ?? null;
            $table_collation = $table->TABLE_COLLATION ?? $table->table_collation ?? '';
            
            $total_size = $data_length + $index_length;
            $is_wp_core = strpos($table_name, $wp_prefix) === 0;
            
            $tables[] = [
                'name' => $table_name,
                'rows' => (int) $table_rows,
                'data_size' => round($data_length / 1024 / 1024, 2),
                'index_size' => round($index_length / 1024 / 1024, 2),
                'total_size' => round($total_size / 1024 / 1024, 2),
                'overhead' => round($data_free / 1024 / 1024, 2),
                'engine' => $engine,
                'collation' => $table_collation,
                'created' => $create_time,
                'updated' => $update_time,
                'is_core' => $is_wp_core,
                'can_optimize' => $this->should_offer_optimize((int) $data_free, (int) $total_size),
                'can_repair' => in_array($engine, ['MyISAM', 'ARCHIVE']),
                'can_convert_to_innodb' => $this->can_convert_to_innodb_engine($engine),
                'conversion_note' => $this->get_innodb_conversion_note($engine),
            ];
        }
        
        return $tables;
    }

    /**
     * Whether the table has enough reclaimable overhead to justify Optimize.
     *
     * @param int $data_free  Reclaimable free space in bytes.
     * @param int $total_size Current table size in bytes.
     * @return bool
     */
    private function should_offer_optimize(int $data_free, int $total_size): bool {
        if ($data_free <= 0) {
            return false;
        }

        if ($data_free >= self::OPTIMIZE_OVERHEAD_MIN_BYTES) {
            return true;
        }

        return $total_size > 0 && ($data_free / $total_size) >= self::OPTIMIZE_OVERHEAD_RATIO;
    }
    
    /**
     * Repair database table
     *
     * @param string $table_name Table name
     * @return array Operation result
     */
    public function repair_table(string $table_name): array {
        global $wpdb;
        
        $result = ['success' => false, 'message' => ''];
        
        // Sanitize table name
        $table_name = sanitize_text_field($table_name);
        
        // Check if table exists
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.TABLES 
             WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_name
        ));
        
        if (!$exists) {
            $result['message'] = __('Table does not exist', 'prorank-seo');
            return $result;
        }
        
        // Repair table
        $repair_result = $wpdb->get_results("REPAIR TABLE `$table_name`");
        
        if ($repair_result) {
            $status = end($repair_result);
            if ($status->Msg_text === 'OK' || strpos($status->Msg_text, 'OK') !== false) {
                $result['success'] = true;
                $result['message'] = __('Table repaired successfully', 'prorank-seo');
            } else {
                $result['message'] = $status->Msg_text;
            }
        } else {
            $result['message'] = __('Failed to repair table', 'prorank-seo');
        }
        
        return $result;
    }
    
    /**
     * Optimize database table
     *
     * @param string $table_name Table name
     * @return array Operation result
     */
    public function optimize_table(string $table_name): array {
        global $wpdb;
        
        $result = ['success' => false, 'message' => ''];
        
        // Sanitize table name
        $table_name = sanitize_text_field($table_name);
        
        // Check if table exists
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM information_schema.TABLES 
             WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_name
        ));
        
        if (!$exists) {
            $result['message'] = __('Table does not exist', 'prorank-seo');
            return $result;
        }
        
        // Optimize table
        $optimize_result = $wpdb->get_results("OPTIMIZE TABLE `$table_name`");
        
        if ($optimize_result) {
            $status = end($optimize_result);
            if ($status->Msg_text === 'OK' || strpos($status->Msg_text, 'OK') !== false || strpos($status->Msg_text, 'optimized') !== false) {
                $result['success'] = true;
                $result['message'] = __('Table optimized successfully', 'prorank-seo');
                
                // Get new table info
                $table_info = $this->get_table_info($table_name);
                if ($table_info) {
                    $result['new_size'] = $table_info['total_size'];
                    $result['new_overhead'] = $table_info['overhead'];
                }
            } else {
                $result['message'] = $status->Msg_text;
            }
        } else {
            $result['message'] = __('Failed to optimize table', 'prorank-seo');
        }
        
        return $result;
    }
    
    /**
     * Convert table to InnoDB
     *
     * @param string $table_name Table name
     * @return array Operation result
     */
    public function convert_table_to_innodb(string $table_name): array {
        global $wpdb;
        
        $result = ['success' => false, 'message' => ''];
        
        // Sanitize table name
        $table_name = sanitize_text_field($table_name);
        
        // Get current engine
        $current_engine = $wpdb->get_var($wpdb->prepare(
            "SELECT ENGINE FROM information_schema.TABLES 
             WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_name
        ));
        
        if (!$current_engine) {
            $result['message'] = __('Table does not exist', 'prorank-seo');
            return $result;
        }
        
        if (strtolower($current_engine) === 'innodb') {
            $result['message'] = __('Table is already using InnoDB engine', 'prorank-seo');
            return $result;
        }

        if (!$this->can_convert_to_innodb_engine($current_engine)) {
            $result['message'] = $this->get_innodb_conversion_note($current_engine);
            return $result;
        }
        
        // Convert to InnoDB
        $convert = $wpdb->query("ALTER TABLE `$table_name` ENGINE=InnoDB");
        
        if ($convert !== false) {
            $result['success'] = true;
            $result['message'] = __('Table converted to InnoDB successfully', 'prorank-seo');
        } else {
            $result['message'] = __('Failed to convert table to InnoDB', 'prorank-seo');
        }
        
        return $result;
    }
    
    /**
     * Get single table info
     *
     * @param string $table_name Table name
     * @return array|null Table information
     */
    private function get_table_info(string $table_name): ?array {
        global $wpdb;
        
        $info = $wpdb->get_row($wpdb->prepare(
            "SELECT 
                table_name AS name,
                table_rows AS rows,
                ROUND(data_length / 1024 / 1024, 2) AS data_size,
                ROUND(index_length / 1024 / 1024, 2) AS index_size,
                ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_size,
                ROUND(data_free / 1024 / 1024, 2) AS overhead,
                engine,
                table_collation AS collation
            FROM information_schema.TABLES
            WHERE table_schema = %s AND table_name = %s",
            DB_NAME,
            $table_name
        ), ARRAY_A);
        
        return $info;
    }
    
    /**
     * Empty database table
     *
     * @param string $table_name Table name
     * @return array Operation result
     */
    public function empty_table(string $table_name): array {
        global $wpdb;
        
        $result = ['success' => false, 'message' => ''];
        
        // Sanitize table name
        $table_name = sanitize_text_field($table_name);
        
        // Prevent emptying core WordPress tables
        $protected_tables = [
            $wpdb->prefix . 'users',
            $wpdb->prefix . 'usermeta',
            $wpdb->prefix . 'posts',
            $wpdb->prefix . 'postmeta',
            $wpdb->prefix . 'terms',
            $wpdb->prefix . 'termmeta',
            $wpdb->prefix . 'term_taxonomy',
            $wpdb->prefix . 'term_relationships',
            $wpdb->prefix . 'options',
        ];
        
        if (in_array($table_name, $protected_tables)) {
            $result['message'] = __('Cannot empty core WordPress table', 'prorank-seo');
            return $result;
        }
        
        // Truncate table
        $truncate = $wpdb->query("TRUNCATE TABLE `$table_name`");
        
        if ($truncate !== false) {
            $result['success'] = true;
            $result['message'] = __('Table emptied successfully', 'prorank-seo');
        } else {
            $result['message'] = __('Failed to empty table', 'prorank-seo');
        }
        
        return $result;
    }
    
    /**
     * Get all options with detailed information
     *
     * @param array $args Query arguments
     * @return array Options information
     */
    public function get_all_options(array $args = []): array {
        global $wpdb;
        
        $defaults = [
            'search' => '',
            'autoload' => '',
            'limit' => 100,
            'offset' => 0,
            'orderby' => 'option_name',
            'order' => 'ASC',
        ];
        
        $args = wp_parse_args($args, $defaults);
        
        $where = "1=1";
        
        if (!empty($args['search'])) {
            $search = '%' . $wpdb->esc_like($args['search']) . '%';
            $where .= $wpdb->prepare(" AND option_name LIKE %s", $search);
        }
        
        if ($args['autoload'] !== '') {
            if (in_array($args['autoload'], ['yes', 'enabled', 'on', 'true'], true)) {
                [$autoload_sql, $autoload_values] = $this->get_autoload_enabled_sql();
                $where .= $wpdb->prepare(" AND {$autoload_sql}", ...$autoload_values);
            } elseif (in_array($args['autoload'], ['no', 'disabled', 'off', 'false'], true)) {
                [$autoload_sql, $autoload_values] = $this->get_autoload_enabled_sql();
                $where .= $wpdb->prepare(" AND NOT ({$autoload_sql})", ...$autoload_values);
            } else {
                $where .= $wpdb->prepare(" AND autoload = %s", $args['autoload']);
            }
        }
        
        $orderby = in_array($args['orderby'], ['option_name', 'autoload']) ? $args['orderby'] : 'option_name';
        $order = $args['order'] === 'DESC' ? 'DESC' : 'ASC';
        
        $options = $wpdb->get_results($wpdb->prepare(
            "SELECT option_id, option_name, option_value, autoload,
                    LENGTH(option_value) as size
             FROM {$wpdb->options}
             WHERE $where
             ORDER BY $orderby $order
             LIMIT %d OFFSET %d",
            $args['limit'],
            $args['offset']
        ));
        
        $total = $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->options} WHERE $where");
        
        $formatted_options = [];
        foreach ($options as $option) {
            $formatted_options[] = [
                'id' => $option->option_id,
                'name' => $option->option_name,
                'value' => substr($option->option_value, 0, 100), // Truncate for display
                'size' => round($option->size / 1024, 2), // KB
                'autoload' => $option->autoload,
                'is_autoloaded' => in_array((string) $option->autoload, $this->get_autoload_enabled_values(), true),
                'is_transient' => strpos($option->option_name, '_transient') !== false,
                'is_core' => $this->is_core_option($option->option_name),
            ];
        }
        
        return [
            'options' => $formatted_options,
            'total' => $total,
        ];
    }
    
    /**
     * Update option autoload status
     *
     * @param string $option_name Option name
     * @param string $autoload 'yes' or 'no'
     * @return bool Success
     */
    public function update_option_autoload(string $option_name, string $autoload): bool {
        global $wpdb;

        if ($option_name === '') {
            return false;
        }

        $autoload = in_array($autoload, ['yes', 'on', 'enabled', 'true'], true) ? 'on' : 'off';

        if (function_exists('wp_set_option_autoload_values')) {
            $results = wp_set_option_autoload_values([$option_name => $autoload]);
            return !empty($results[$option_name]);
        }

        $legacy_autoload = $autoload === 'on' ? 'yes' : 'no';
        $updated = $wpdb->update(
            $wpdb->options,
            ['autoload' => $legacy_autoload],
            ['option_name' => $option_name],
            ['%s'],
            ['%s']
        );

        if ($updated === false) {
            return false;
        }

        if ($legacy_autoload === 'yes') {
            wp_cache_delete($option_name, 'options');
            wp_cache_delete('alloptions', 'options');
        } else {
            $alloptions = wp_load_alloptions(true);
            if (isset($alloptions[$option_name])) {
                unset($alloptions[$option_name]);
                wp_cache_set('alloptions', $alloptions, 'options');
            }
        }

        return true;
    }
    
    /**
     * Get all cron jobs
     *
     * @return array Cron jobs information
     */
    public function get_all_cron_jobs(): array {
        $cron = _get_cron_array();
        $schedules = wp_get_schedules();
        $jobs = [];
        
        foreach ($cron as $timestamp => $hooks) {
            if ($timestamp === 'version') {
                continue;
            }
            
            foreach ($hooks as $hook => $events) {
                foreach ($events as $key => $event) {
                    $schedule = isset($event['schedule']) ? $event['schedule'] : false;
                    $is_orphaned = false === has_action($hook);
                    
                    $jobs[] = [
                        'hook' => $hook,
                        'timestamp' => $timestamp,
                        'next_run' => get_date_from_gmt(wp_date('Y-m-d H:i:s', $timestamp)),
                        'schedule' => $schedule,
                        'interval' => $schedule && isset($schedules[$schedule]) ? $schedules[$schedule]['interval'] : null,
                        'args' => $event['args'],
                        'is_orphaned' => $is_orphaned,
                        'can_delete' => $is_orphaned,
                    ];
                }
            }
        }
        
        usort($jobs, function($a, $b) {
            return $a['timestamp'] - $b['timestamp'];
        });
        
        return $jobs;
    }
    
    /**
     * Delete cron job
     *
     * @param string $hook Hook name
     * @param int $timestamp Timestamp
     * @param array $args Arguments
     * @return bool Success
     */
    public function delete_cron_job(string $hook, int $timestamp, array $args = []): bool {
        if ($hook === '' || $timestamp <= 0 || false !== has_action($hook)) {
            return false;
        }

        $removed = wp_unschedule_event($timestamp, $hook, $args);
        if ($removed) {
            return true;
        }

        if (function_exists('wp_clear_scheduled_hook')) {
            $cleared = wp_clear_scheduled_hook($hook, $args);
            return is_int($cleared) && $cleared > 0;
        }

        return false;
    }
    
    /**
     * Get orphaned items preview
     *
     * @param string $type Type of orphaned items
     * @return array Orphaned items details
     */
    public function get_orphaned_items_preview(string $type): array {
        global $wpdb;
        
        $items = [];
        
        switch ($type) {
            case 'postmeta':
                $items = $wpdb->get_results(
                    "SELECT pm.meta_id, pm.post_id, pm.meta_key, pm.meta_value
                     FROM {$wpdb->postmeta} pm
                     LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                     WHERE p.ID IS NULL
                     LIMIT 50"
                );
                break;
                
            case 'commentmeta':
                $items = $wpdb->get_results(
                    "SELECT cm.meta_id, cm.comment_id, cm.meta_key, cm.meta_value
                     FROM {$wpdb->commentmeta} cm
                     LEFT JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID
                     WHERE c.comment_ID IS NULL
                     LIMIT 50"
                );
                break;
                
            case 'usermeta':
                $items = $wpdb->get_results(
                    "SELECT um.umeta_id, um.user_id, um.meta_key, um.meta_value
                     FROM {$wpdb->usermeta} um
                     LEFT JOIN {$wpdb->users} u ON um.user_id = u.ID
                     WHERE u.ID IS NULL
                     LIMIT 50"
                );
                break;
                
            case 'termmeta':
                $items = $wpdb->get_results(
                    "SELECT tm.meta_id, tm.term_id, tm.meta_key, tm.meta_value
                     FROM {$wpdb->termmeta} tm
                     LEFT JOIN {$wpdb->terms} t ON tm.term_id = t.term_id
                     WHERE t.term_id IS NULL
                     LIMIT 50"
                );
                break;
                
            case 'relationships':
                $items = $wpdb->get_results(
                    "SELECT tr.object_id, tr.term_taxonomy_id
                     FROM {$wpdb->term_relationships} tr
                     LEFT JOIN {$wpdb->posts} p ON tr.object_id = p.ID
                     WHERE p.ID IS NULL
                     LIMIT 50"
                );
                break;
        }
        
        return $items;
    }
    
    /**
     * Clean up database with retention days
     *
     * @param string $type Cleanup type
     * @param int $retention_days Days to keep
     * @return array Cleanup results
     */
    public function cleanup_with_retention(string $type, int $retention_days): array {
        global $wpdb;
        
        $results = [
            'success' => false,
            'deleted' => 0,
            'error' => null,
        ];
        
        $date_limit = wp_date('Y-m-d H:i:s', strtotime("-{$retention_days} days"));
        
        // Find the cleanup condition
        $condition = null;
        $table = null;
        
        foreach (self::CLEANUP_TABLES as $table_name => $conditions) {
            if (isset($conditions[$type])) {
                $condition = $conditions[$type];
                $table = $wpdb->prefix . $table_name;
                break;
            }
        }
        
        if (!$condition || !$table) {
            $results['error'] = __('Invalid cleanup type', 'prorank-seo');
            return $results;
        }
        
        // Add date condition based on table
        $date_column = '';
        if ($table === $wpdb->posts) {
            $date_column = 'post_date';
        } elseif ($table === $wpdb->comments) {
            $date_column = 'comment_date';
        }
        
        if ($date_column) {
            $condition .= " AND $date_column < '$date_limit'";
        }
        
        // Replace prefix placeholder
        $condition = str_replace('{prefix}', $wpdb->prefix, $condition);
        
        // Perform cleanup
        try {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $deleted = $wpdb->query("DELETE FROM $table WHERE $condition");
            
            if ($deleted !== false) {
                $results['success'] = true;
                $results['deleted'] = $deleted;
                
                // Optimize table after cleanup
                if ($deleted > 0) {
                    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
                    $wpdb->query("OPTIMIZE TABLE $table");
                }
            }
        } catch (\Exception $e) {
            $results['error'] = $e->getMessage();
        }
        
        return $results;
    }
    
    /**
     * Get database overview report
     *
     * @return array Overview report
     */
    public function get_database_overview(): array {
        global $wpdb;
        
        $overview = [
            'total_size' => 0,
            'total_tables' => 0,
            'total_rows' => 0,
            'cleanup_potential' => [],
            'largest_tables' => [],
            'problem_areas' => [],
            'recommendations' => [],
        ];
        
        // Get database size and table count
        $db_name = DB_NAME;
        $tables = $wpdb->get_results(
            "SELECT 
                table_name,
                table_rows,
                data_length,
                index_length,
                data_free
             FROM information_schema.TABLES
             WHERE table_schema = '$db_name'
             ORDER BY (data_length + index_length) DESC"
        );
        
        foreach ($tables as $table) {
            $data_length = (float) ($table->DATA_LENGTH ?? $table->data_length ?? 0);
            $index_length = (float) ($table->INDEX_LENGTH ?? $table->index_length ?? 0);
            $table_rows = (int) ($table->TABLE_ROWS ?? $table->table_rows ?? 0);

            $overview['total_size'] += ($data_length + $index_length);
            $overview['total_tables']++;
            $overview['total_rows'] += $table_rows;
        }
        
        // Get top 5 largest tables
        $overview['largest_tables'] = array_slice(array_map(function($table) {
            $data_length = (float) ($table->DATA_LENGTH ?? $table->data_length ?? 0);
            $index_length = (float) ($table->INDEX_LENGTH ?? $table->index_length ?? 0);
            $table_name = (string) ($table->TABLE_NAME ?? $table->table_name ?? '');
            $table_rows = (int) ($table->TABLE_ROWS ?? $table->table_rows ?? 0);

            return [
                'name' => $table_name,
                'size' => round(($data_length + $index_length) / 1024 / 1024, 2),
                'rows' => $table_rows,
            ];
        }, $tables), 0, 5);
        
        // Get cleanup potential
        foreach (self::CLEANUP_TABLES as $table => $conditions) {
            $table_name = $wpdb->prefix . $table;
            
            foreach ($conditions as $type => $where) {
                $where = str_replace('{prefix}', $wpdb->prefix, $where);
                $count = $wpdb->get_var("SELECT COUNT(*) FROM $table_name WHERE $where");
                
                if ($count > 0) {
                    $overview['cleanup_potential'][] = [
                        'type' => $type,
                        'count' => $count,
                        'description' => $this->get_cleanup_description($type),
                    ];
                }
            }
        }
        
        // Identify problem areas
        if ($overview['total_size'] > 500 * 1024 * 1024) { // > 500MB
            $overview['problem_areas'][] = __('Database size is very large', 'prorank-seo');
        }
        
        [$autoload_sql, $autoload_values] = $this->get_autoload_enabled_sql();
        $autoload_size = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COALESCE(SUM(LENGTH(option_value)), 0)
                 FROM {$wpdb->options}
                 WHERE {$autoload_sql}",
                ...$autoload_values
            )
        );
        
        if ($autoload_size > 1024 * 1024) { // > 1MB
            $overview['problem_areas'][] = __('Autoloaded data is too large', 'prorank-seo');
        }
        
        // Generate recommendations
        if (!empty($overview['cleanup_potential'])) {
            $overview['recommendations'][] = __('Run database cleanup to remove unnecessary data', 'prorank-seo');
        }
        
        if ($autoload_size > 500 * 1024) {
            $overview['recommendations'][] = __('Review and optimize autoloaded options', 'prorank-seo');
        }
        
        // Convert size to MB
        $overview['total_size'] = round($overview['total_size'] / 1024 / 1024, 2);
        
        return $overview;
    }
    
    /**
     * Get secure backup directory
     *
     * @return string
     */
    private function get_backup_dir(): string {
        return trailingslashit(WP_CONTENT_DIR) . 'prorank-secure-db-backups';
    }

    /**
     * Ensure backup directory exists and is protected from public access
     *
     * @return string
     */
    private function ensure_backup_dir(): string {
        $backup_dir = $this->get_backup_dir();

        if (!file_exists($backup_dir)) {
            wp_mkdir_p($backup_dir);
        }

        // Block web access
        $htaccess = $backup_dir . '/.htaccess';
        if (!file_exists($htaccess)) {
            FilesystemHelper::put_contents($htaccess, "Require all denied\n");
        }

        // Prevent directory listing
        $index = $backup_dir . '/index.php';
        if (!file_exists($index)) {
            FilesystemHelper::put_contents($index, "<?php // Silence is golden\n");
        }

        return $backup_dir;
    }

    /**
     * Migrate legacy backups from uploads into the secure directory
     *
     * @return void
     */
    private function migrate_legacy_backups(): void {
        $upload_dir = wp_upload_dir();
        $legacy_dir = trailingslashit($upload_dir['basedir']) . 'prorank-db-backups';

        if (!is_dir($legacy_dir)) {
            return;
        }

        $secure_dir = $this->ensure_backup_dir();
        foreach (glob($legacy_dir . '/*.sql') ?: [] as $legacy_file) {
            $target = $secure_dir . '/' . basename($legacy_file);
            if (!file_exists($target)) {
                \ProRank\SEO\Core\FilesystemHelper::move($legacy_file, $target);
            }
        }
    }

    /**
     * Build the absolute path to a backup file in the secure directory
     *
     * @param string $filename Backup filename
     * @return string
     */
    private function get_backup_file_path(string $filename): string {
        $backup_dir = $this->ensure_backup_dir();
        return $backup_dir . '/' . basename($filename);
    }

    /**
     * Create database backup
     *
     * @param array $tables Optional specific tables to backup
     * @return array Backup result
     */
    public function create_backup(array $tables = []): array {
        global $wpdb;
        
        $result = [
            'success' => false,
            'file' => '',
            'size' => 0,
            'message' => '',
        ];
        
        // Ensure secure backup directory exists and migrate legacy files
        $this->migrate_legacy_backups();
        $backup_dir = $this->ensure_backup_dir();
        
        // Generate backup filename
        $filename = 'prorank-backup-' . wp_date('Y-m-d-H-i-s') . '-' . wp_generate_password(8, false) . '.sql';
        $filepath = $backup_dir . '/' . $filename;
        
        $tables = $this->normalize_backup_tables($tables);
        if (empty($tables)) {
            $result['message'] = __('No WordPress tables found to back up.', 'prorank-seo');
            return $result;
        }
        
        try {
            $handle = @fopen($filepath, 'wb');
            if (!$handle) {
                $result['message'] = __('Failed to create backup file', 'prorank-seo');
                return $result;
            }

            $header = "-- ProRank Database Backup\n";
            $header .= "-- Generated: " . wp_date('Y-m-d H:i:s') . "\n";
            $header .= "-- WordPress Version: " . get_bloginfo('version') . "\n";
            $header .= "-- Database: " . DB_NAME . "\n\n";
            $header .= "SET SQL_MODE = \"NO_AUTO_VALUE_ON_ZERO\";\n";
            $header .= "SET time_zone = \"+00:00\";\n\n";

            if (false === fwrite($handle, $header)) {
                fclose($handle);
                FilesystemHelper::delete_file($filepath);
                $result['message'] = __('Failed to write backup file header', 'prorank-seo');
                return $result;
            }
            
            foreach ($tables as $table) {
                // Get table structure
                $create_table = $wpdb->get_row("SHOW CREATE TABLE `$table`", ARRAY_A);
                if ($create_table && !empty($create_table['Create Table'])) {
                    if (false === fwrite($handle, "\n-- Table structure for table `$table`\nDROP TABLE IF EXISTS `$table`;\n" . $create_table['Create Table'] . ";\n\n")) {
                        throw new \RuntimeException(__('Failed to write backup table structure.', 'prorank-seo'));
                    }
                    
                    // Get table data
                    $rows = $wpdb->get_results("SELECT * FROM `$table`", ARRAY_A);
                    if ($rows) {
                        if (false === fwrite($handle, "-- Data for table `$table`\n")) {
                            throw new \RuntimeException(__('Failed to write backup table header.', 'prorank-seo'));
                        }
                        
                        foreach ($rows as $row) {
                            $values = array_map([$this, 'format_backup_value'], $row);
                            if (false === fwrite($handle, "INSERT INTO `$table` VALUES (" . implode(',', $values) . ");\n")) {
                                throw new \RuntimeException(__('Failed to write backup table data.', 'prorank-seo'));
                            }
                        }

                        if (false === fwrite($handle, "\n")) {
                            throw new \RuntimeException(__('Failed to finalize backup table data.', 'prorank-seo'));
                        }
                    }
                }
            }

            fclose($handle);
            
            // Save backup file
            if (file_exists($filepath)) {
                $result['success'] = true;
                $result['file'] = $filename;
                $result['size'] = (int) filesize($filepath);
                $hash = hash_file('sha256', $filepath);
                $result['message'] = sprintf(
/* translators: %1$: value, %2$: value */
                    __('Backup created successfully: %1$s (%2$.2f MB)', 'prorank-seo'),
                    $filename,
                    $result['size'] / 1024 / 1024
                );
                
                // Store backup info in database
                $backups = get_option('prorank_db_backups', []);
                $backups[] = [
                    'file' => $filename,
                    'date' => current_time('mysql'),
                    'size' => $result['size'],
                    'tables' => count($tables),
                    'hash' => $hash,
                    'storage' => 'secure',
                ];
                
                $backups = $this->prune_backup_inventory($backups);
                
                update_option('prorank_db_backups', $backups);
            } else {
                $result['message'] = __('Failed to create backup file', 'prorank-seo');
            }
        } catch (\Exception $e) {
            if (isset($handle) && is_resource($handle)) {
                fclose($handle);
            }
            FilesystemHelper::delete_file($filepath);
            $result['message'] = $e->getMessage();
        }
        
        return $result;
    }
    
    /**
     * Restore database from backup
     *
     * @param string $backup_file Backup filename
     * @return array Restore result
     */
    public function restore_backup(string $backup_file): array {
        global $wpdb;
        
        $result = [
            'success' => false,
            'message' => '',
        ];
        
        $this->migrate_legacy_backups();
        $backup_dir = $this->ensure_backup_dir();
        $filepath = $this->get_backup_file_path($backup_file);

        // If file was not migrated yet, attempt to migrate again
        if (!file_exists($filepath)) {
            $this->migrate_legacy_backups();
            $filepath = $this->get_backup_file_path($backup_file);
        }
        
        // Security check - ensure file is in backup directory
        if (!file_exists($filepath) || strpos(realpath($filepath), realpath($backup_dir)) !== 0) {
            $result['message'] = __('Invalid backup file', 'prorank-seo');
            return $result;
        }
        
        try {
            $handle = @fopen($filepath, 'rb');
            if (!$handle) {
                $result['message'] = __('Cannot read backup file', 'prorank-seo');
                return $result;
            }

            $query = '';
            while (($line = fgets($handle)) !== false) {
                $trimmed = trim($line);
                if ($trimmed === '' || strpos($trimmed, '-- ') === 0) {
                    continue;
                }

                $query .= $line;

                if (str_ends_with(rtrim($trimmed), ';')) {
                    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                    $wpdb->query($query);
                    $query = '';
                }
            }

            if (trim($query) !== '') {
                // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
                $wpdb->query($query);
            }

            fclose($handle);
            
            $result['success'] = true;
            $result['message'] = __('Database restored successfully', 'prorank-seo');
            
            // Clear all caches
            wp_cache_flush();
            
        } catch (\Exception $e) {
            if (isset($handle) && is_resource($handle)) {
                fclose($handle);
            }
            $result['message'] = sprintf(
/* translators: %1$: value */
                __('Restore failed: %1$s', 'prorank-seo'),
                $e->getMessage()
            );
        }
        
        return $result;
    }
    
    /**
     * Get list of available backups
     *
     * @return array List of backups
     */
    public function get_backups(): array {
        $this->migrate_legacy_backups();

        $backups = get_option('prorank_db_backups', []);
        $backup_dir = $this->ensure_backup_dir();
        
        // Scan directory for actual backup files
        $files_in_dir = [];
        if (is_dir($backup_dir)) {
            $files = scandir($backup_dir);
            foreach ($files as $file) {
                if (preg_match('/^(prorank-backup-|db-backup-).*\.sql$/', $file)) {
                    $filepath = $backup_dir . '/' . $file;
                    $files_in_dir[$file] = [
                        'file' => $file,
                        'date' => wp_date('Y-m-d H:i:s', filemtime($filepath)),
                        'size' => filesize($filepath),
                        'tables' => 0, // Unknown
                        'hash' => hash_file('sha256', $filepath),
                        'storage' => 'secure',
                    ];
                }
            }
        }
        
        // Merge stored backups with actual files
        $all_backups = [];
        $stored_files = [];
        
        foreach ($backups as $backup) {
            if (isset($files_in_dir[$backup['file']])) {
                $all_backups[] = array_merge($files_in_dir[$backup['file']], $backup);
                $stored_files[] = $backup['file'];
            }
        }
        
        foreach ($files_in_dir as $file => $info) {
            if (!in_array($file, $stored_files)) {
                $all_backups[] = $info;
            }
        }

        // Persist normalized backup list (most recent kept last for array_reverse)
        update_option('prorank_db_backups', $all_backups);
        
        return array_reverse(array_map(function($backup) {
            $backup['size_formatted'] = size_format($backup['size'], 2);
            $backup['created'] = $backup['date'] ?? '';
            $backup['storage'] = $backup['storage'] ?? 'secure';
            return $backup;
        }, $all_backups)); // Most recent first
    }


    /**
     * Delete backup file
     *
     * @param string $backup_file Backup filename
     * @return bool Success
     */
    public function delete_backup(string $backup_file): bool {
        $this->migrate_legacy_backups();
        $backup_dir = $this->ensure_backup_dir();
        $filepath = $this->get_backup_file_path($backup_file);
        
        // Security check
        $real_dir = realpath($backup_dir);
        $real_file = realpath($filepath);
        if (!$real_dir || !$real_file || strpos($real_file, $real_dir) !== 0) {
            return false;
        }
        
        if (file_exists($filepath) && \ProRank\SEO\Core\FilesystemHelper::delete_file($filepath)) {
            // Remove from stored backups
            $backups = get_option('prorank_db_backups', []);
            $backups = array_filter($backups, function($backup) use ($backup_file) {
                return $backup['file'] !== $backup_file;
            });
            update_option('prorank_db_backups', array_values($backups));
            
            return true;
        }
        
        return false;
    }

    /**
     * Normalize a backup table list to WordPress tables only.
     *
     * @param array $tables Requested tables.
     * @return string[]
     */
    private function normalize_backup_tables(array $tables = []): array {
        global $wpdb;

        $available_tables = $wpdb->get_col(
            $wpdb->prepare(
                "SELECT table_name
                 FROM information_schema.TABLES
                 WHERE table_schema = %s
                 AND table_name LIKE %s",
                DB_NAME,
                $wpdb->esc_like($wpdb->prefix) . '%'
            )
        );

        if (empty($tables)) {
            return array_values(array_map('strval', $available_tables));
        }

        $available_lookup = array_fill_keys(array_map('strval', $available_tables), true);
        $normalized = [];
        foreach ($tables as $table) {
            $table = sanitize_text_field((string) $table);
            if ($table !== '' && isset($available_lookup[$table])) {
                $normalized[] = $table;
            }
        }

        return array_values(array_unique($normalized));
    }

    /**
     * Convert a PHP value to a SQL literal for backup export.
     *
     * @param mixed $value Exported value.
     * @return string
     */
    private function format_backup_value($value): string {
        global $wpdb;

        if ($value === null) {
            return 'NULL';
        }

        return (string) $wpdb->prepare('%s', (string) $value);
    }

    /**
     * Retain only the 10 most recent backup files and metadata entries.
     *
     * @param array $backups Backup metadata.
     * @return array
     */
    private function prune_backup_inventory(array $backups): array {
        if (count($backups) <= 10) {
            return array_values($backups);
        }

        usort($backups, static function(array $a, array $b): int {
            $left = strtotime((string) ($a['date'] ?? '')) ?: 0;
            $right = strtotime((string) ($b['date'] ?? '')) ?: 0;
            return $left <=> $right;
        });

        $overflow = array_slice($backups, 0, max(count($backups) - 10, 0));
        foreach ($overflow as $backup) {
            if (!empty($backup['file'])) {
                FilesystemHelper::delete_file($this->get_backup_file_path((string) $backup['file']));
            }
        }

        return array_values(array_slice($backups, -10));
    }
    
    /**
     * Check if option is core WordPress option
     *
     * @param string $option_name Option name
     * @return bool
     */
    private function is_core_option(string $option_name): bool {
        $core_options = [
            'siteurl', 'home', 'blogname', 'blogdescription', 'users_can_register',
            'admin_email', 'start_of_week', 'use_balanceTags', 'use_smilies',
            'require_name_email', 'comments_notify', 'posts_per_rss', 'rss_use_excerpt',
            'mailserver_url', 'mailserver_login', 'mailserver_pass', 'mailserver_port',
            'default_category', 'default_comment_status', 'default_ping_status',
            'default_pingback_flag', 'posts_per_page', 'date_format', 'time_format',
            'links_updated_date_format', 'comment_moderation', 'moderation_notify',
            'permalink_structure', 'rewrite_rules', 'hack_file', 'blog_charset',
            'moderation_keys', 'active_plugins', 'category_base', 'ping_sites',
            'comment_max_links', 'gmt_offset', 'default_email_category', 'recently_edited',
            'template', 'stylesheet', 'comment_registration', 'html_type',
            'use_trackback', 'default_role', 'db_version', 'uploads_use_yearmonth_folders',
            'upload_path', 'blog_public', 'default_link_category', 'show_on_front',
            'tag_base', 'show_avatars', 'avatar_rating', 'upload_url_path',
            'thumbnail_size_h', 'thumbnail_size_w', 'thumbnail_crop', 'medium_size_h',
            'medium_size_w', 'avatar_default', 'large_size_h', 'large_size_w',
            'image_default_link_type', 'image_default_size', 'image_default_align',
            'close_comments_for_old_posts', 'close_comments_days_old', 'thread_comments',
            'thread_comments_depth', 'page_comments', 'comments_per_page',
            'default_comments_page', 'comment_order', 'sticky_posts', 'widget_categories',
            'widget_text', 'widget_rss', 'uninstall_plugins', 'timezone_string',
            'page_for_posts', 'page_on_front', 'default_post_format', 'link_manager_enabled',
            'finished_splitting_shared_terms', 'site_icon', 'medium_large_size_h',
            'medium_large_size_w', 'wp_page_for_privacy_policy', 'show_comments_cookies_opt_in',
            'admin_email_lifespan', 'disallowed_keys', 'comment_previously_approved',
            'auto_plugin_theme_update_emails', 'auto_update_core_dev', 'auto_update_core_minor',
            'auto_update_core_major', 'wp_force_deactivated_plugins', 'wp_user_roles',
            'widget_block', 'sidebars_widgets', 'cron', 'theme_mods_',
        ];
        
        foreach ($core_options as $core) {
            if ($option_name === $core || strpos($option_name, $core) === 0) {
                return true;
            }
        }
        
        return false;
    }

    // =========================================================================
    // DEEP DATABASE CLEANUP (WP-Optimize Level)
    // Direct SQL operations for maximum performance
    // =========================================================================

    /**
     * Run deep database cleanup with all advanced optimizations
     * Uses direct SQL for maximum performance
     *
     * @return array Cleanup results
     */
    public function run_deep_cleanup(): array {
        global $wpdb;

        $results = [
            'success' => true,
            'operations' => [],
            'total_deleted' => 0,
            'space_freed' => 0,
            'errors' => [],
        ];

        // 1. Orphaned Post Meta
        $results['operations']['orphaned_postmeta'] = $this->cleanup_orphaned_postmeta();

        // 2. Orphaned Comment Meta
        $results['operations']['orphaned_commentmeta'] = $this->cleanup_orphaned_commentmeta();

        // 3. Orphaned Term Meta
        $results['operations']['orphaned_termmeta'] = $this->cleanup_orphaned_termmeta();

        // 4. Orphaned User Meta
        $results['operations']['orphaned_usermeta'] = $this->cleanup_orphaned_usermeta();

        // 5. Expired Transients (with proper cleanup)
        $results['operations']['expired_transients'] = $this->cleanup_expired_transients_deep();

        // 6. Orphaned Term Relationships
        $results['operations']['orphaned_relationships'] = $this->cleanup_orphaned_term_relationships();

        // 7. Oembeds in postmeta
        $results['operations']['oembeds'] = $this->cleanup_oembed_cache();

        // 8. Optimize tables
        $results['operations']['optimize_tables'] = $this->optimize_core_tables();

        // Calculate totals
        foreach ($results['operations'] as $op) {
            if (isset($op['deleted'])) {
                $results['total_deleted'] += $op['deleted'];
            }
            if (isset($op['error'])) {
                $results['errors'][] = $op['error'];
            }
        }

        $results['success'] = empty($results['errors']);

        // Log the cleanup
        update_option('prorank_last_deep_cleanup', [
            'timestamp' => current_time('mysql'),
            'results' => $results,
        ], false);

        return $results;
    }

    /**
     * Cleanup orphaned post meta
     *
     * @return array
     */
    private function cleanup_orphaned_postmeta(): array {
        global $wpdb;

        try {
            $deleted = $wpdb->query(
                "DELETE pm FROM {$wpdb->postmeta} pm
                 LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                 WHERE p.ID IS NULL"
            );

            return [
                'success' => true,
                'deleted' => $deleted !== false ? $deleted : 0,
                'description' => __('Orphaned post meta entries', 'prorank-seo'),
            ];
        } catch (\Throwable $e) {
            return [
                'success' => false,
                'deleted' => 0,
                'error' => $e->getMessage(),
            ];
        }
    }

    /**
     * Cleanup orphaned comment meta
     *
     * @return array
     */
    private function cleanup_orphaned_commentmeta(): array {
        global $wpdb;

        try {
            $deleted = $wpdb->query(
                "DELETE cm FROM {$wpdb->commentmeta} cm
                 LEFT JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID
                 WHERE c.comment_ID IS NULL"
            );

            return [
                'success' => true,
                'deleted' => $deleted !== false ? $deleted : 0,
                'description' => __('Orphaned comment meta entries', 'prorank-seo'),
            ];
        } catch (\Throwable $e) {
            return [
                'success' => false,
                'deleted' => 0,
                'error' => $e->getMessage(),
            ];
        }
    }

    /**
     * Cleanup orphaned term meta
     *
     * @return array
     */
    private function cleanup_orphaned_termmeta(): array {
        global $wpdb;

        try {
            $deleted = $wpdb->query(
                "DELETE tm FROM {$wpdb->termmeta} tm
                 LEFT JOIN {$wpdb->terms} t ON tm.term_id = t.term_id
                 WHERE t.term_id IS NULL"
            );

            return [
                'success' => true,
                'deleted' => $deleted !== false ? $deleted : 0,
                'description' => __('Orphaned term meta entries', 'prorank-seo'),
            ];
        } catch (\Throwable $e) {
            return [
                'success' => false,
                'deleted' => 0,
                'error' => $e->getMessage(),
            ];
        }
    }

    /**
     * Cleanup orphaned user meta
     *
     * @return array
     */
    private function cleanup_orphaned_usermeta(): array {
        global $wpdb;

        try {
            $deleted = $wpdb->query(
                "DELETE um FROM {$wpdb->usermeta} um
                 LEFT JOIN {$wpdb->users} u ON um.user_id = u.ID
                 WHERE u.ID IS NULL"
            );

            return [
                'success' => true,
                'deleted' => $deleted !== false ? $deleted : 0,
                'description' => __('Orphaned user meta entries', 'prorank-seo'),
            ];
        } catch (\Throwable $e) {
            return [
                'success' => false,
                'deleted' => 0,
                'error' => $e->getMessage(),
            ];
        }
    }

    /**
     * Cleanup expired transients with proper matching
     * Removes both timeout and value entries
     *
     * @return array
     */
    private function cleanup_expired_transients_deep(): array {
        global $wpdb;

        try {
            // First, delete expired timeout entries
            $timeout_deleted = $wpdb->query(
                "DELETE FROM {$wpdb->options}
                 WHERE option_name LIKE '_transient_timeout_%'
                 AND option_value < UNIX_TIMESTAMP()"
            );

            // Then delete orphaned transient values (where timeout doesn't exist)
            $value_deleted = $wpdb->query(
                "DELETE a FROM {$wpdb->options} a
                 WHERE a.option_name LIKE '_transient_%'
                 AND a.option_name NOT LIKE '_transient_timeout_%'
                 AND NOT EXISTS (
                     SELECT 1 FROM (SELECT option_name FROM {$wpdb->options}
                           WHERE option_name LIKE '_transient_timeout_%') AS t
                     WHERE t.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12))
                 )"
            );

            return [
                'success' => true,
                'deleted' => ($timeout_deleted !== false ? $timeout_deleted : 0) + ($value_deleted !== false ? $value_deleted : 0),
                'description' => __('Expired transients', 'prorank-seo'),
            ];
        } catch (\Throwable $e) {
            return [
                'success' => false,
                'deleted' => 0,
                'error' => $e->getMessage(),
            ];
        }
    }

    /**
     * Cleanup orphaned term relationships
     *
     * @return array
     */
    private function cleanup_orphaned_term_relationships(): array {
        global $wpdb;

        try {
            $deleted = $wpdb->query(
                "DELETE tr FROM {$wpdb->term_relationships} tr
                 LEFT JOIN {$wpdb->posts} p ON tr.object_id = p.ID
                 WHERE p.ID IS NULL"
            );

            return [
                'success' => true,
                'deleted' => $deleted !== false ? $deleted : 0,
                'description' => __('Orphaned term relationships', 'prorank-seo'),
            ];
        } catch (\Throwable $e) {
            return [
                'success' => false,
                'deleted' => 0,
                'error' => $e->getMessage(),
            ];
        }
    }

    /**
     * Cleanup oEmbed cache from postmeta
     *
     * @return array
     */
    private function cleanup_oembed_cache(): array {
        global $wpdb;

        try {
            $deleted = $wpdb->query(
                "DELETE FROM {$wpdb->postmeta}
                 WHERE meta_key LIKE '_oembed_%'"
            );

            return [
                'success' => true,
                'deleted' => $deleted !== false ? $deleted : 0,
                'description' => __('oEmbed cache entries', 'prorank-seo'),
            ];
        } catch (\Throwable $e) {
            return [
                'success' => false,
                'deleted' => 0,
                'error' => $e->getMessage(),
            ];
        }
    }

    /**
     * Optimize core WordPress tables
     *
     * @return array
     */
    private function optimize_core_tables(): array {
        global $wpdb;

        $tables = [
            $wpdb->posts,
            $wpdb->postmeta,
            $wpdb->comments,
            $wpdb->commentmeta,
            $wpdb->terms,
            $wpdb->termmeta,
            $wpdb->term_taxonomy,
            $wpdb->term_relationships,
            $wpdb->options,
            $wpdb->usermeta,
        ];

        $optimized = 0;
        $errors = [];

        foreach ($tables as $table) {
            try {
                $result = $wpdb->query("OPTIMIZE TABLE $table");
                if ($result !== false) {
                    $optimized++;
                }
            } catch (\Throwable $e) {
                $errors[] = "{$table}: " . $e->getMessage();
            }
        }

        return [
            'success' => empty($errors),
            'optimized' => $optimized,
            'total_tables' => count($tables),
            'description' => __('Tables optimized', 'prorank-seo'),
            'errors' => $errors,
        ];
    }

    /**
     * Get estimated savings for deep cleanup
     *
     * @return array Estimated counts and sizes
     */
    public function estimate_deep_cleanup(): array {
        global $wpdb;

        $estimates = [];

        // Orphaned postmeta
        $estimates['orphaned_postmeta'] = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->postmeta} pm
             LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID
             WHERE p.ID IS NULL"
        );

        // Orphaned commentmeta
        $estimates['orphaned_commentmeta'] = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->commentmeta} cm
             LEFT JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID
             WHERE c.comment_ID IS NULL"
        );

        // Orphaned termmeta
        $estimates['orphaned_termmeta'] = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->termmeta} tm
             LEFT JOIN {$wpdb->terms} t ON tm.term_id = t.term_id
             WHERE t.term_id IS NULL"
        );

        // Expired transients
        $estimates['expired_transients'] = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->options}
             WHERE option_name LIKE '_transient_timeout_%'
             AND option_value < UNIX_TIMESTAMP()"
        );

        // oEmbed cache
        $estimates['oembed_cache'] = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->postmeta}
             WHERE meta_key LIKE '_oembed_%'"
        );

        // Total
        $estimates['total'] = array_sum($estimates);

        return $estimates;
    }
}
