<?php
/**
 * Name: Database Cleaner
 * Description: Comprehensive database cleaning and optimization tool. Remove revisions, drafts, spam, orphaned data, transients, and more. Browse tables, options, and metadata with detailed size analysis.
 * Category: Optimization
 * Icon: -
 * UseCases: [
 *   {
 *     "title": "Deep Database Cleanup",
 *     "description": "Remove thousands of post revisions, auto-drafts, spam comments, and orphaned metadata to dramatically reduce database size."
 *   },
 *   {
 *     "title": "Optimize Performance",
 *     "description": "Clean expired transients, orphaned data, and optimize tables to improve query performance and site speed."
 *   },
 *   {
 *     "title": "Database Analysis",
 *     "description": "Browse and analyze database tables, options, and metadata to identify bloat and understand what's using space."
 *   },
 *   {
 *     "title": "Scheduled Maintenance",
 *     "description": "Set up automatic daily or weekly cleanup to keep your database lean and fast without manual intervention."
 *   }
 * ]
 * Added: 2025-12-19
 * Tags: database, db, cleanup, optimization, revisions, transients, spam, orphaned, metadata, performance
 * IsBeta: true
 * WhereToFind: {
 *   "location": "Switchboard Menu",
 *   "url": "admin.php?page=switchboard-database-cleaner",
 *   "instructions": "Access the full Database Cleaner dashboard from Switchboard > Database Cleaner in the admin menu.",
 *   "newTab": false
 * }
 */

// Prevent direct access
if (!defined('ABSPATH')) exit;

class SwitchboardDatabaseCleaner {

    /**
     * Option key for storing settings
     */
    private $option_key = 'switchboard_database-cleaner_settings';

    /**
     * Cleanup item definitions organized by category
     */
    private $cleanup_items = [];

    /**
     * Constructor - register hooks and initialize
     */
    public function __construct() {
        $this->define_cleanup_items();

        // Admin menu
        add_action('admin_menu', [$this, 'register_admin_page'], 99);

        // Enqueue assets
        add_action('admin_enqueue_scripts', [$this, 'enqueue_assets']);

        // AJAX handlers
        add_action('wp_ajax_switchboard_dbcleaner_counts', [$this, 'ajax_get_counts']);
        add_action('wp_ajax_switchboard_dbcleaner_clean', [$this, 'ajax_clean_items']);
        add_action('wp_ajax_switchboard_dbcleaner_preview', [$this, 'ajax_get_preview']);
        add_action('wp_ajax_switchboard_dbcleaner_db_size', [$this, 'ajax_get_db_size']);
        add_action('wp_ajax_switchboard_dbcleaner_tables', [$this, 'ajax_get_tables']);
        add_action('wp_ajax_switchboard_dbcleaner_optimize_table', [$this, 'ajax_optimize_table']);
        add_action('wp_ajax_switchboard_dbcleaner_options', [$this, 'ajax_get_options']);
        add_action('wp_ajax_switchboard_dbcleaner_delete_option', [$this, 'ajax_delete_option']);
        add_action('wp_ajax_switchboard_dbcleaner_metadata', [$this, 'ajax_get_metadata']);
        add_action('wp_ajax_switchboard_dbcleaner_delete_metadata', [$this, 'ajax_delete_metadata']);
        add_action('wp_ajax_switchboard_dbcleaner_logs', [$this, 'ajax_get_logs']);
        add_action('wp_ajax_switchboard_dbcleaner_save_settings', [$this, 'ajax_save_settings']);

        // Cron for scheduled cleanup
        add_action('switchboard_database_cleaner_cron', [$this, 'run_scheduled_cleanup']);

        // Register with data registry
        $this->register_with_data_registry();
    }

    /**
     * Define all cleanup items with their metadata
     */
    private function define_cleanup_items() {
        $this->cleanup_items = [
            'posts' => [
                'label' => __('Posts', 'wpswitchboard'),
                'icon' => '',
                'items' => [
                    'revisions' => [
                        'label' => __('Revisions', 'wpswitchboard'),
                        'description' => __('Post revisions stored for undo/history', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                    'auto_drafts' => [
                        'label' => __('Auto-Drafts', 'wpswitchboard'),
                        'description' => __('Automatically saved drafts', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                    'trashed_posts' => [
                        'label' => __('Trashed Posts', 'wpswitchboard'),
                        'description' => __('Posts and pages in trash', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                ],
            ],
            'comments' => [
                'label' => __('Comments', 'wpswitchboard'),
                'icon' => '',
                'items' => [
                    'spam_comments' => [
                        'label' => __('Spam Comments', 'wpswitchboard'),
                        'description' => __('Comments marked as spam', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                    'trashed_comments' => [
                        'label' => __('Trashed Comments', 'wpswitchboard'),
                        'description' => __('Comments in trash', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                    'unapproved_comments' => [
                        'label' => __('Unapproved Comments', 'wpswitchboard'),
                        'description' => __('Comments pending moderation', 'wpswitchboard'),
                        'default' => false,
                        'risk' => 'caution',
                    ],
                    'pingbacks' => [
                        'label' => __('Pingbacks & Trackbacks', 'wpswitchboard'),
                        'description' => __('Auto-generated pingback/trackback comments', 'wpswitchboard'),
                        'default' => false,
                        'risk' => 'caution',
                    ],
                ],
            ],
            'metadata' => [
                'label' => __('Metadata', 'wpswitchboard'),
                'icon' => '',
                'items' => [
                    'orphaned_postmeta' => [
                        'label' => __('Orphaned Post Meta', 'wpswitchboard'),
                        'description' => __('Post metadata with no parent post', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                    'duplicated_postmeta' => [
                        'label' => __('Duplicated Post Meta', 'wpswitchboard'),
                        'description' => __('Duplicate metadata entries', 'wpswitchboard'),
                        'default' => false,
                        'risk' => 'caution',
                    ],
                    'orphaned_commentmeta' => [
                        'label' => __('Orphaned Comment Meta', 'wpswitchboard'),
                        'description' => __('Comment metadata with no parent comment', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                    'orphaned_usermeta' => [
                        'label' => __('Orphaned User Meta', 'wpswitchboard'),
                        'description' => __('User metadata with no parent user', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                    'orphaned_termmeta' => [
                        'label' => __('Orphaned Term Meta', 'wpswitchboard'),
                        'description' => __('Term metadata with no parent term', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                    'oembed_cache' => [
                        'label' => __('oEmbed Caches', 'wpswitchboard'),
                        'description' => __('Cached embed data from external services', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                ],
            ],
            'transients' => [
                'label' => __('Transients', 'wpswitchboard'),
                'icon' => '',
                'items' => [
                    'expired_transients' => [
                        'label' => __('Expired Transients', 'wpswitchboard'),
                        'description' => __('Temporary data that has expired', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                    'all_transients' => [
                        'label' => __('All Transients', 'wpswitchboard'),
                        'description' => __('All temporary cached data (rebuilds on demand)', 'wpswitchboard'),
                        'default' => false,
                        'risk' => 'risky',
                    ],
                ],
            ],
            'terms' => [
                'label' => __('Terms', 'wpswitchboard'),
                'icon' => '',
                'items' => [
                    'unused_terms' => [
                        'label' => __('Unused Terms', 'wpswitchboard'),
                        'description' => __('Categories/tags with no posts', 'wpswitchboard'),
                        'default' => false,
                        'risk' => 'caution',
                    ],
                    'orphaned_term_relationships' => [
                        'label' => __('Orphaned Term Relationships', 'wpswitchboard'),
                        'description' => __('Term relationships with missing terms', 'wpswitchboard'),
                        'default' => true,
                        'risk' => 'safe',
                    ],
                ],
            ],
        ];
    }

    /**
     * Register admin page under Switchboard menu
     */
    public function register_admin_page() {
        add_submenu_page(
            'switchboard',
            __('Database Cleaner', 'wpswitchboard'),
            __('Database Cleaner', 'wpswitchboard'),
            'manage_options',
            'switchboard-database-cleaner',
            [$this, 'render_admin_page']
        );
    }

    /**
     * Enqueue admin assets
     */
    public function enqueue_assets($hook) {
        // Check if we're on the database-cleaner page (check both hook and GET parameter)
        $is_dbcleaner_page = (
            (isset($_GET['page']) && $_GET['page'] === 'switchboard-database-cleaner') ||
            strpos($hook, 'switchboard-database-cleaner') !== false
        );

        if (!$is_dbcleaner_page) {
            return;
        }

        // Enqueue Switchboard admin CSS for CSS variables
        if (defined('SWITCHBOARD_URL') && defined('SWITCHBOARD_VERSION')) {
            wp_enqueue_style(
                'switchboard-admin',
                SWITCHBOARD_URL . 'switchboard-admin.css',
                [],
                SWITCHBOARD_VERSION
            );
        }

        wp_enqueue_style(
            'switchboard-database-cleaner',
            SWITCHBOARD_URL . 'assets/database-cleaner/admin.css',
            ['switchboard-admin'],
            SWITCHBOARD_VERSION
        );

        wp_enqueue_script(
            'switchboard-database-cleaner',
            SWITCHBOARD_URL . 'assets/database-cleaner/admin.js',
            ['jquery'],
            SWITCHBOARD_VERSION,
            true
        );

        wp_localize_script('switchboard-database-cleaner', 'switchboardDbCleaner', [
            'ajaxurl' => admin_url('admin-ajax.php'),
            'nonce' => wp_create_nonce('switchboard_dbcleaner'),
            'items' => $this->cleanup_items,
            'settings' => $this->get_settings(),
            'i18n' => [
                'refresh' => __('Refresh', 'wpswitchboard'),
                'refreshing' => __('Refreshing...', 'wpswitchboard'),
                'cleaning' => __('Cleaning...', 'wpswitchboard'),
                'cleaned' => __('Cleaned %d items', 'wpswitchboard'),
                'noItems' => __('No items to clean', 'wpswitchboard'),
                'error' => __('An error occurred', 'wpswitchboard'),
                'confirmClean' => __('Are you sure you want to clean the selected items? This cannot be undone.', 'wpswitchboard'),
                'confirmCleanAll' => __('Are you sure you want to clean all safe items? This cannot be undone.', 'wpswitchboard'),
                'confirmDelete' => __('Are you sure you want to delete this item?', 'wpswitchboard'),
                'confirmDeleteMeta' => __('Are you sure you want to delete ALL instances of this meta key? This cannot be undone.', 'wpswitchboard'),
                'saved' => __('Settings saved', 'wpswitchboard'),
                'optimizing' => __('Optimizing...', 'wpswitchboard'),
                'optimized' => __('Table optimized', 'wpswitchboard'),
                'loading' => __('Loading...', 'wpswitchboard'),
                'items' => __('items', 'wpswitchboard'),
                'deleted' => __('Deleted', 'wpswitchboard'),
            ],
        ]);
    }

    /**
     * Render the admin page
     */
    public function render_admin_page() {
        include SWITCHBOARD_PATH . 'assets/database-cleaner/admin.php';
    }

    /**
     * Get module settings
     */
    public function get_settings() {
        $defaults = [
            'auto_clean' => 'disabled',
            'auto_clean_items' => ['revisions', 'spam_comments', 'expired_transients', 'orphaned_postmeta'],
            'batch_size' => 100,
            'size_history' => [],
            'cleanup_log' => [],
        ];

        return wp_parse_args(get_option($this->option_key, []), $defaults);
    }

    /**
     * Register with data registry for cleanup
     */
    private function register_with_data_registry() {
        if (class_exists('Switchboard_Data_Registry')) {
            $registry = Switchboard_Data_Registry::get_instance();
            $registry->auto_register($this, __FILE__);
        }
    }

    /**
     * Register data locations for cleanup system
     */
    public function register_data_locations() {
        return [
            'options' => [
                'switchboard_database-cleaner_settings',
            ],
            'option_patterns' => [],
            'tables' => [],
            'post_meta' => [],
            'user_meta' => [],
            'term_meta' => [],
            'transients' => [],
        ];
    }

    /**
     * Called when module is activated
     */
    public function activate() {
        $settings = $this->get_settings();

        if ($settings['auto_clean'] !== 'disabled' && !wp_next_scheduled('switchboard_database_cleaner_cron')) {
            wp_schedule_event(time(), $settings['auto_clean'], 'switchboard_database_cleaner_cron');
        }
    }

    /**
     * Called when module is deactivated
     */
    public function deactivate() {
        wp_clear_scheduled_hook('switchboard_database_cleaner_cron');
    }

    // =========================================================================
    // COUNT METHODS
    // =========================================================================

    /**
     * Get count for a specific item type
     */
    public function get_count($item_key) {
        global $wpdb;

        switch ($item_key) {
            case 'revisions':
                return (int) $wpdb->get_var(
                    $wpdb->prepare("SELECT COUNT(*) FROM $wpdb->posts WHERE post_type = %s", 'revision')
                );

            case 'auto_drafts':
                return (int) $wpdb->get_var(
                    $wpdb->prepare("SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = %s", 'auto-draft')
                );

            case 'trashed_posts':
                return (int) $wpdb->get_var(
                    $wpdb->prepare("SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = %s", 'trash')
                );

            case 'spam_comments':
                return (int) $wpdb->get_var(
                    $wpdb->prepare("SELECT COUNT(*) FROM $wpdb->comments WHERE comment_approved = %s", 'spam')
                );

            case 'trashed_comments':
                return (int) $wpdb->get_var(
                    $wpdb->prepare("SELECT COUNT(*) FROM $wpdb->comments WHERE comment_approved = %s", 'trash')
                );

            case 'unapproved_comments':
                return (int) $wpdb->get_var(
                    $wpdb->prepare("SELECT COUNT(*) FROM $wpdb->comments WHERE comment_approved = %s", '0')
                );

            case 'pingbacks':
                return (int) $wpdb->get_var(
                    "SELECT COUNT(*) FROM $wpdb->comments WHERE comment_type IN ('pingback', 'trackback')"
                );

            case 'orphaned_postmeta':
                return (int) $wpdb->get_var(
                    "SELECT COUNT(*) FROM $wpdb->postmeta pm
                     WHERE NOT EXISTS (SELECT 1 FROM $wpdb->posts p WHERE p.ID = pm.post_id)"
                );

            case 'duplicated_postmeta':
                return (int) $wpdb->get_var(
                    "SELECT COUNT(*) FROM $wpdb->postmeta pm1
                     WHERE EXISTS (
                         SELECT 1 FROM $wpdb->postmeta pm2
                         WHERE pm1.post_id = pm2.post_id
                         AND pm1.meta_key = pm2.meta_key
                         AND pm1.meta_value = pm2.meta_value
                         AND pm1.meta_id > pm2.meta_id
                     )"
                );

            case 'orphaned_commentmeta':
                return (int) $wpdb->get_var(
                    "SELECT COUNT(*) FROM $wpdb->commentmeta cm
                     WHERE NOT EXISTS (SELECT 1 FROM $wpdb->comments c WHERE c.comment_ID = cm.comment_id)"
                );

            case 'orphaned_usermeta':
                return (int) $wpdb->get_var(
                    "SELECT COUNT(*) FROM $wpdb->usermeta um
                     WHERE NOT EXISTS (SELECT 1 FROM $wpdb->users u WHERE u.ID = um.user_id)"
                );

            case 'orphaned_termmeta':
                return (int) $wpdb->get_var(
                    "SELECT COUNT(*) FROM $wpdb->termmeta tm
                     WHERE NOT EXISTS (SELECT 1 FROM $wpdb->terms t WHERE t.term_id = tm.term_id)"
                );

            case 'oembed_cache':
                return (int) $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT COUNT(*) FROM $wpdb->postmeta WHERE meta_key LIKE %s",
                        $wpdb->esc_like('_oembed_') . '%'
                    )
                );

            case 'expired_transients':
                $time = time();
                return (int) $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT COUNT(*) FROM $wpdb->options
                         WHERE option_name LIKE %s AND option_value < %d",
                        $wpdb->esc_like('_transient_timeout_') . '%',
                        $time
                    )
                );

            case 'all_transients':
                return (int) $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT COUNT(*) FROM $wpdb->options
                         WHERE option_name LIKE %s AND option_name NOT LIKE %s",
                        $wpdb->esc_like('_transient_') . '%',
                        $wpdb->esc_like('_transient_timeout_') . '%'
                    )
                );

            case 'unused_terms':
                // Count must match the same filter logic as clean_items()
                // Exclude: nav_menu, post_format, wp_* internal taxonomies, WooCommerce product attributes
                $default_category = (int) get_option('default_category', 0);
                return (int) $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT COUNT(*) FROM $wpdb->term_taxonomy tt
                         WHERE tt.count = 0
                         AND tt.taxonomy NOT IN ('nav_menu', 'post_format', 'wp_theme', 'wp_template', 'wp_template_part', 'wp_global_styles', 'wp_navigation', 'wp_pattern_category')
                         AND tt.taxonomy NOT LIKE %s
                         AND tt.term_id != %d",
                        'pa_%',
                        $default_category
                    )
                );

            case 'orphaned_term_relationships':
                return (int) $wpdb->get_var(
                    "SELECT COUNT(*) FROM $wpdb->term_relationships tr
                     WHERE NOT EXISTS (
                         SELECT 1 FROM $wpdb->term_taxonomy tt WHERE tt.term_taxonomy_id = tr.term_taxonomy_id
                     )"
                );

            default:
                return 0;
        }
    }

    /**
     * Estimate size for item type (rough calculation)
     */
    public function estimate_size($item_key, $count) {
        // Average row sizes in bytes (estimates)
        $avg_sizes = [
            'revisions' => 5000,           // posts table row
            'auto_drafts' => 2000,         // posts table row
            'trashed_posts' => 5000,       // posts table row
            'spam_comments' => 1000,       // comments table row
            'trashed_comments' => 1000,
            'unapproved_comments' => 1000,
            'pingbacks' => 500,
            'orphaned_postmeta' => 200,    // postmeta table row
            'duplicated_postmeta' => 200,
            'orphaned_commentmeta' => 150,
            'orphaned_usermeta' => 150,
            'orphaned_termmeta' => 100,
            'oembed_cache' => 500,
            'expired_transients' => 300,   // options table row
            'all_transients' => 300,
            'unused_terms' => 100,
            'orphaned_term_relationships' => 50,
        ];

        $avg_size = isset($avg_sizes[$item_key]) ? $avg_sizes[$item_key] : 100;
        return $count * $avg_size;
    }

    // =========================================================================
    // CLEAN METHODS
    // =========================================================================

    /**
     * Clean items of a specific type
     */
    public function clean_items($item_key, $limit = 100) {
        global $wpdb;

        $cleaned = 0;

        switch ($item_key) {
            case 'revisions':
                $cleaned = $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM $wpdb->posts WHERE post_type = %s LIMIT %d",
                        'revision',
                        $limit
                    )
                );
                break;

            case 'auto_drafts':
                $cleaned = $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM $wpdb->posts WHERE post_status = %s LIMIT %d",
                        'auto-draft',
                        $limit
                    )
                );
                break;

            case 'trashed_posts':
                $cleaned = $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM $wpdb->posts WHERE post_status = %s LIMIT %d",
                        'trash',
                        $limit
                    )
                );
                break;

            case 'spam_comments':
                $cleaned = $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM $wpdb->comments WHERE comment_approved = %s LIMIT %d",
                        'spam',
                        $limit
                    )
                );
                break;

            case 'trashed_comments':
                $cleaned = $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM $wpdb->comments WHERE comment_approved = %s LIMIT %d",
                        'trash',
                        $limit
                    )
                );
                break;

            case 'unapproved_comments':
                $cleaned = $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM $wpdb->comments WHERE comment_approved = %s LIMIT %d",
                        '0',
                        $limit
                    )
                );
                break;

            case 'pingbacks':
                $cleaned = $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM $wpdb->comments WHERE comment_type IN ('pingback', 'trackback') LIMIT %d",
                        $limit
                    )
                );
                break;

            case 'orphaned_postmeta':
                // Use subquery approach for better MySQL compatibility (LIMIT in multi-table DELETE not universally supported)
                $orphaned_ids = $wpdb->get_col(
                    $wpdb->prepare(
                        "SELECT pm.meta_id FROM $wpdb->postmeta pm
                         WHERE NOT EXISTS (SELECT 1 FROM $wpdb->posts p WHERE p.ID = pm.post_id)
                         LIMIT %d",
                        $limit
                    )
                );
                if (!empty($orphaned_ids)) {
                    // Sanitize IDs and build placeholders for prepared statement
                    $orphaned_ids = array_map('intval', $orphaned_ids);
                    $placeholders = implode(',', array_fill(0, count($orphaned_ids), '%d'));
                    $cleaned = $wpdb->query($wpdb->prepare(
                        "DELETE FROM $wpdb->postmeta WHERE meta_id IN ($placeholders)",
                        $orphaned_ids
                    ));
                } else {
                    $cleaned = 0;
                }
                break;

            case 'duplicated_postmeta':
                // Get IDs of duplicates to delete (keep the oldest)
                $duplicates = $wpdb->get_col(
                    $wpdb->prepare(
                        "SELECT pm1.meta_id FROM $wpdb->postmeta pm1
                         WHERE EXISTS (
                             SELECT 1 FROM $wpdb->postmeta pm2
                             WHERE pm1.post_id = pm2.post_id
                             AND pm1.meta_key = pm2.meta_key
                             AND pm1.meta_value = pm2.meta_value
                             AND pm1.meta_id > pm2.meta_id
                         )
                         LIMIT %d",
                        $limit
                    )
                );
                if (!empty($duplicates)) {
                    // Sanitize IDs and build placeholders for prepared statement
                    $duplicates = array_map('intval', $duplicates);
                    $placeholders = implode(',', array_fill(0, count($duplicates), '%d'));
                    $cleaned = $wpdb->query($wpdb->prepare(
                        "DELETE FROM $wpdb->postmeta WHERE meta_id IN ($placeholders)",
                        $duplicates
                    ));
                }
                break;

            case 'orphaned_commentmeta':
                // Use subquery approach for better MySQL compatibility
                $orphaned_ids = $wpdb->get_col(
                    $wpdb->prepare(
                        "SELECT cm.meta_id FROM $wpdb->commentmeta cm
                         WHERE NOT EXISTS (SELECT 1 FROM $wpdb->comments c WHERE c.comment_ID = cm.comment_id)
                         LIMIT %d",
                        $limit
                    )
                );
                if (!empty($orphaned_ids)) {
                    // Sanitize IDs and build placeholders for prepared statement
                    $orphaned_ids = array_map('intval', $orphaned_ids);
                    $placeholders = implode(',', array_fill(0, count($orphaned_ids), '%d'));
                    $cleaned = $wpdb->query($wpdb->prepare(
                        "DELETE FROM $wpdb->commentmeta WHERE meta_id IN ($placeholders)",
                        $orphaned_ids
                    ));
                } else {
                    $cleaned = 0;
                }
                break;

            case 'orphaned_usermeta':
                // Use subquery approach for better MySQL compatibility
                $orphaned_ids = $wpdb->get_col(
                    $wpdb->prepare(
                        "SELECT um.umeta_id FROM $wpdb->usermeta um
                         WHERE NOT EXISTS (SELECT 1 FROM $wpdb->users u WHERE u.ID = um.user_id)
                         LIMIT %d",
                        $limit
                    )
                );
                if (!empty($orphaned_ids)) {
                    // Sanitize IDs and build placeholders for prepared statement
                    $orphaned_ids = array_map('intval', $orphaned_ids);
                    $placeholders = implode(',', array_fill(0, count($orphaned_ids), '%d'));
                    $cleaned = $wpdb->query($wpdb->prepare(
                        "DELETE FROM $wpdb->usermeta WHERE umeta_id IN ($placeholders)",
                        $orphaned_ids
                    ));
                } else {
                    $cleaned = 0;
                }
                break;

            case 'orphaned_termmeta':
                // Use subquery approach for better MySQL compatibility
                $orphaned_ids = $wpdb->get_col(
                    $wpdb->prepare(
                        "SELECT tm.meta_id FROM $wpdb->termmeta tm
                         WHERE NOT EXISTS (SELECT 1 FROM $wpdb->terms t WHERE t.term_id = tm.term_id)
                         LIMIT %d",
                        $limit
                    )
                );
                if (!empty($orphaned_ids)) {
                    // Sanitize IDs and build placeholders for prepared statement
                    $orphaned_ids = array_map('intval', $orphaned_ids);
                    $placeholders = implode(',', array_fill(0, count($orphaned_ids), '%d'));
                    $cleaned = $wpdb->query($wpdb->prepare(
                        "DELETE FROM $wpdb->termmeta WHERE meta_id IN ($placeholders)",
                        $orphaned_ids
                    ));
                } else {
                    $cleaned = 0;
                }
                break;

            case 'oembed_cache':
                $cleaned = $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM $wpdb->postmeta WHERE meta_key LIKE %s LIMIT %d",
                        $wpdb->esc_like('_oembed_') . '%',
                        $limit
                    )
                );
                break;

            case 'expired_transients':
                $time = time();
                $expired = $wpdb->get_col(
                    $wpdb->prepare(
                        "SELECT option_name FROM $wpdb->options
                         WHERE option_name LIKE %s AND option_value < %d LIMIT %d",
                        $wpdb->esc_like('_transient_timeout_') . '%',
                        $time,
                        $limit
                    )
                );
                if (!empty($expired)) {
                    foreach ($expired as $transient) {
                        $key = str_replace('_transient_timeout_', '', $transient);
                        if (delete_transient($key)) {
                            $cleaned++;
                        }
                    }
                }
                break;

            case 'all_transients':
                $transients = $wpdb->get_col(
                    $wpdb->prepare(
                        "SELECT option_name FROM $wpdb->options
                         WHERE option_name LIKE %s AND option_name NOT LIKE %s LIMIT %d",
                        $wpdb->esc_like('_transient_') . '%',
                        $wpdb->esc_like('_transient_timeout_') . '%',
                        $limit
                    )
                );
                if (!empty($transients)) {
                    foreach ($transients as $transient) {
                        $key = str_replace('_transient_', '', $transient);
                        if (delete_transient($key)) {
                            $cleaned++;
                        }
                    }
                }
                break;

            case 'unused_terms':
                // Get term_id and taxonomy together, excluding protected taxonomies and default terms
                // Exclude: nav_menu (navigation), post_format (core), wp_* (internal)
                $default_category = (int) get_option('default_category', 0);

                $terms = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT tt.term_id, tt.taxonomy FROM $wpdb->term_taxonomy tt
                         WHERE tt.count = 0
                         AND tt.taxonomy NOT IN ('nav_menu', 'post_format', 'wp_theme', 'wp_template', 'wp_template_part', 'wp_global_styles', 'wp_navigation', 'wp_pattern_category')
                         AND tt.taxonomy NOT LIKE %s
                         AND tt.term_id != %d
                         LIMIT %d",
                        'pa_%', // WooCommerce product attributes
                        $default_category,
                        $limit
                    )
                );
                if (!empty($terms)) {
                    foreach ($terms as $term_data) {
                        // Double-check term is safe to delete
                        $term = get_term($term_data->term_id, $term_data->taxonomy);
                        if (is_wp_error($term) || !$term) {
                            continue;
                        }
                        // Skip if term has a different count now (race condition)
                        if ($term->count > 0) {
                            continue;
                        }
                        $result = wp_delete_term($term_data->term_id, $term_data->taxonomy);
                        if ($result && !is_wp_error($result)) {
                            $cleaned++;
                        }
                    }
                }
                break;

            case 'orphaned_term_relationships':
                // Use subquery approach for better MySQL compatibility
                // term_relationships has composite primary key (object_id, term_taxonomy_id)
                $orphaned = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT tr.object_id, tr.term_taxonomy_id FROM $wpdb->term_relationships tr
                         WHERE NOT EXISTS (
                             SELECT 1 FROM $wpdb->term_taxonomy tt WHERE tt.term_taxonomy_id = tr.term_taxonomy_id
                         )
                         LIMIT %d",
                        $limit
                    )
                );
                if (!empty($orphaned)) {
                    foreach ($orphaned as $row) {
                        $wpdb->delete(
                            $wpdb->term_relationships,
                            ['object_id' => $row->object_id, 'term_taxonomy_id' => $row->term_taxonomy_id],
                            ['%d', '%d']
                        );
                        $cleaned++;
                    }
                } else {
                    $cleaned = 0;
                }
                break;
        }

        // Log the cleanup
        $this->log_cleanup($item_key, $cleaned);

        return $cleaned !== false ? (int) $cleaned : 0;
    }

    /**
     * Log cleanup action
     */
    private function log_cleanup($item_key, $count) {
        $settings = $this->get_settings();
        $log = $settings['cleanup_log'];

        // Add new entry
        $log[] = [
            'item' => $item_key,
            'count' => $count,
            'time' => current_time('mysql'),
            'timestamp' => time(),
        ];

        // Keep only last 100 entries
        $log = array_slice($log, -100);

        $settings['cleanup_log'] = $log;
        update_option($this->option_key, $settings);
    }

    // =========================================================================
    // DATABASE INFO METHODS
    // =========================================================================

    /**
     * Get total database size
     */
    public function get_database_size() {
        global $wpdb;

        $result = $wpdb->get_row(
            "SELECT
                SUM(data_length + index_length) as total_size,
                SUM(data_length) as data_size,
                SUM(index_length) as index_size
             FROM information_schema.TABLES
             WHERE table_schema = DATABASE()"
        );

        return [
            'total' => $result ? (int) $result->total_size : 0,
            'data' => $result ? (int) $result->data_size : 0,
            'index' => $result ? (int) $result->index_size : 0,
        ];
    }

    /**
     * Get all database tables with sizes
     */
    public function get_tables() {
        global $wpdb;

        // Get database name from wpdb
        $db_name = $wpdb->dbname;

        $tables = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT
                    TABLE_NAME as name,
                    TABLE_ROWS as `rows`,
                    DATA_LENGTH as data_size,
                    INDEX_LENGTH as index_size,
                    (DATA_LENGTH + INDEX_LENGTH) as total_size,
                    ENGINE as engine,
                    TABLE_COLLATION as collation
                 FROM information_schema.TABLES
                 WHERE TABLE_SCHEMA = %s
                 ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC",
                $db_name
            )
        );

        if (empty($tables)) {
            $tables = [];
        }

        $total_size = 0;
        foreach ($tables as $table) {
            $total_size += (int) $table->total_size;
        }

        // Add percentage
        foreach ($tables as $table) {
            $table->percentage = $total_size > 0 ? round(((int) $table->total_size / $total_size) * 100, 2) : 0;
        }

        return $tables;
    }

    /**
     * Optimize a database table
     */
    public function optimize_table($table_name) {
        global $wpdb;

        // Validate table name
        if (!preg_match('/^[a-zA-Z0-9_]+$/', $table_name)) {
            return false;
        }

        // Check table exists
        $exists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM information_schema.TABLES
                 WHERE table_schema = DATABASE() AND table_name = %s",
                $table_name
            )
        );

        if (!$exists) {
            return false;
        }

        $result = $wpdb->query("OPTIMIZE TABLE `{$table_name}`");
        return $result !== false;
    }

    /**
     * Get wp_options data (excluding transients)
     */
    public function get_options($page = 1, $per_page = 50, $search = '', $autoload = 'all') {
        global $wpdb;

        $offset = ($page - 1) * $per_page;

        $where = ["option_name NOT LIKE %s"];
        $params = [$wpdb->esc_like('_transient_') . '%'];

        if (!empty($search)) {
            $where[] = "option_name LIKE %s";
            $params[] = '%' . $wpdb->esc_like($search) . '%';
        }

        // Handle autoload filter - WP 6.6+ uses 'on'/'off' instead of 'yes'/'no'
        if ($autoload === 'yes') {
            $where[] = "(autoload = 'yes' OR autoload = 'on')";
        } elseif ($autoload === 'no') {
            $where[] = "(autoload = 'no' OR autoload = 'off' OR autoload = '')";
        }

        $where_clause = implode(' AND ', $where);

        // Get total count
        $total = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM $wpdb->options WHERE $where_clause",
                ...$params
            )
        );

        // Get options with normalized autoload value
        $options = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT option_id, option_name, LENGTH(option_value) as size,
                        CASE WHEN autoload IN ('yes', 'on') THEN 'yes' ELSE 'no' END as autoload
                 FROM $wpdb->options
                 WHERE $where_clause
                 ORDER BY size DESC
                 LIMIT %d OFFSET %d",
                ...[...$params, $per_page, $offset]
            )
        );

        return [
            'options' => $options,
            'total' => (int) $total,
            'pages' => ceil($total / $per_page),
        ];
    }

    /**
     * Delete an option
     */
    public function delete_option_by_name($option_name) {
        // Protect WordPress core options
        $protected = [
            '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_w', 'thumbnail_size_h', 'thumbnail_crop', 'medium_size_w',
            'medium_size_h', 'avatar_default', 'large_size_w', 'large_size_h',
            '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_w', 'medium_large_size_h',
            '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_attachment_pages_enabled',
            // Additional critical options
            'cron', 'sidebars_widgets', 'widget_block', 'widget_nav_menu',
            'widget_search', 'widget_recent-posts', 'widget_recent-comments',
            'widget_archives', 'widget_meta', 'widget_calendar', 'widget_pages',
            'widget_tag_cloud', 'widget_custom_html', 'widget_media_audio',
            'widget_media_image', 'widget_media_gallery', 'widget_media_video',
            'nonce_key', 'nonce_salt', 'auth_key', 'auth_salt',
            'secure_auth_key', 'secure_auth_salt', 'logged_in_key', 'logged_in_salt',
            'current_theme', 'stylesheet_root', 'template_root',
        ];

        // Also protect options matching these patterns
        $protected_patterns = [
            '/^theme_mods_/',     // Theme customizations
            '/^nav_menu_/',       // Navigation menus
            '/^widget_/',         // All widgets
            '/^user_roles$/',     // User roles
            '/^_site_transient/', // Site transients (critical)
        ];

        foreach ($protected_patterns as $pattern) {
            if (preg_match($pattern, $option_name)) {
                return false;
            }
        }

        if (in_array($option_name, $protected, true)) {
            return false;
        }

        return delete_option($option_name);
    }

    /**
     * Get postmeta data for browsing
     */
    public function get_metadata($page = 1, $per_page = 50, $search = '', $sort = 'size') {
        global $wpdb;

        $offset = ($page - 1) * $per_page;
        $order_by = $sort === 'size' ? 'size DESC' : 'meta_key ASC';

        $where = '1=1';
        $params = [];

        if (!empty($search)) {
            $where = "meta_key LIKE %s";
            $params[] = '%' . $wpdb->esc_like($search) . '%';
        }

        // Get aggregated meta keys with counts and sizes
        if (empty($params)) {
            $meta = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT meta_key, COUNT(*) as count, SUM(LENGTH(meta_value)) as size
                     FROM $wpdb->postmeta
                     WHERE $where
                     GROUP BY meta_key
                     ORDER BY $order_by
                     LIMIT %d OFFSET %d",
                    $per_page,
                    $offset
                )
            );
            $total = $wpdb->get_var("SELECT COUNT(DISTINCT meta_key) FROM $wpdb->postmeta WHERE $where");
        } else {
            $meta = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT meta_key, COUNT(*) as count, SUM(LENGTH(meta_value)) as size
                     FROM $wpdb->postmeta
                     WHERE $where
                     GROUP BY meta_key
                     ORDER BY $order_by
                     LIMIT %d OFFSET %d",
                    ...[...$params, $per_page, $offset]
                )
            );
            $total = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(DISTINCT meta_key) FROM $wpdb->postmeta WHERE $where",
                    ...$params
                )
            );
        }

        return [
            'metadata' => $meta,
            'total' => (int) $total,
            'pages' => ceil($total / $per_page),
        ];
    }

    /**
     * Run scheduled cleanup
     */
    public function run_scheduled_cleanup() {
        $settings = $this->get_settings();
        $items_to_clean = $settings['auto_clean_items'];

        foreach ($items_to_clean as $item_key) {
            // Clean all items of this type (in batches)
            $batch_size = $settings['batch_size'];
            $total_cleaned = 0;

            do {
                $cleaned = $this->clean_items($item_key, $batch_size);
                $total_cleaned += $cleaned;
            } while ($cleaned >= $batch_size && $total_cleaned < 10000); // Safety limit
        }

        // Update size history
        $this->update_size_history();
    }

    /**
     * Update database size history
     */
    private function update_size_history() {
        $settings = $this->get_settings();
        $history = $settings['size_history'];

        $today = date('Y-m-d');
        $size = $this->get_database_size();

        // Update or add today's entry
        $found = false;
        foreach ($history as $key => $entry) {
            if ($entry['date'] === $today) {
                $history[$key]['size'] = $size['total'];
                $found = true;
                break;
            }
        }

        if (!$found) {
            $history[] = [
                'date' => $today,
                'size' => $size['total'],
            ];
        }

        // Keep last 60 days
        $history = array_slice($history, -60);

        $settings['size_history'] = $history;
        update_option($this->option_key, $settings);
    }

    // =========================================================================
    // AJAX HANDLERS
    // =========================================================================

    /**
     * AJAX: Get counts for all items
     */
    public function ajax_get_counts() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        $counts = [];
        foreach ($this->cleanup_items as $category_key => $category) {
            foreach ($category['items'] as $item_key => $item) {
                $count = $this->get_count($item_key);
                $counts[$item_key] = [
                    'count' => $count,
                    'size' => $this->estimate_size($item_key, $count),
                ];
            }
        }

        // Get database size
        $db_size = $this->get_database_size();

        wp_send_json_success([
            'counts' => $counts,
            'db_size' => $db_size,
        ]);
    }

    /**
     * AJAX: Clean selected items
     */
    public function ajax_clean_items() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        $items = isset($_POST['items']) ? (array) $_POST['items'] : [];
        $settings = $this->get_settings();
        $batch_size = $settings['batch_size'];

        $results = [];
        $total_cleaned = 0;

        foreach ($items as $item_key) {
            $item_key = sanitize_key($item_key);

            // Verify item exists
            $valid = false;
            foreach ($this->cleanup_items as $category) {
                if (isset($category['items'][$item_key])) {
                    $valid = true;
                    break;
                }
            }

            if (!$valid) {
                continue;
            }

            // Clean all items of this type
            $item_cleaned = 0;
            do {
                $cleaned = $this->clean_items($item_key, $batch_size);
                $item_cleaned += $cleaned;
            } while ($cleaned >= $batch_size && $item_cleaned < 10000);

            $results[$item_key] = $item_cleaned;
            $total_cleaned += $item_cleaned;
        }

        // Update size history
        $this->update_size_history();

        wp_send_json_success([
            'results' => $results,
            'total' => $total_cleaned,
        ]);
    }

    /**
     * AJAX: Get preview of items to clean
     */
    public function ajax_get_preview() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        global $wpdb;

        $item_key = isset($_POST['item']) ? sanitize_key($_POST['item']) : '';
        $limit = 10;

        $preview = [];

        switch ($item_key) {
            case 'revisions':
                $preview = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT ID, post_title, post_date FROM $wpdb->posts
                         WHERE post_type = %s ORDER BY post_date DESC LIMIT %d",
                        'revision',
                        $limit
                    )
                );
                break;

            case 'spam_comments':
                $preview = $wpdb->get_results(
                    $wpdb->prepare(
                        "SELECT comment_ID, comment_author, comment_date, LEFT(comment_content, 100) as excerpt
                         FROM $wpdb->comments WHERE comment_approved = %s
                         ORDER BY comment_date DESC LIMIT %d",
                        'spam',
                        $limit
                    )
                );
                break;

            // Add more preview types as needed
        }

        wp_send_json_success(['preview' => $preview]);
    }

    /**
     * AJAX: Get database size
     */
    public function ajax_get_db_size() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        wp_send_json_success($this->get_database_size());
    }

    /**
     * AJAX: Get database tables
     */
    public function ajax_get_tables() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        wp_send_json_success(['tables' => $this->get_tables()]);
    }

    /**
     * AJAX: Optimize a table
     */
    public function ajax_optimize_table() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        $table = isset($_POST['table']) ? sanitize_text_field($_POST['table']) : '';

        if (empty($table)) {
            wp_send_json_error(['message' => __('Invalid table name', 'wpswitchboard')]);
        }

        $result = $this->optimize_table($table);

        if ($result) {
            wp_send_json_success(['message' => __('Table optimized', 'wpswitchboard')]);
        } else {
            wp_send_json_error(['message' => __('Failed to optimize table', 'wpswitchboard')]);
        }
    }

    /**
     * AJAX: Get options
     */
    public function ajax_get_options() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        $page = isset($_POST['page']) ? (int) $_POST['page'] : 1;
        $search = isset($_POST['search']) ? sanitize_text_field($_POST['search']) : '';
        $autoload = isset($_POST['autoload']) ? sanitize_text_field($_POST['autoload']) : 'all';

        wp_send_json_success($this->get_options($page, 50, $search, $autoload));
    }

    /**
     * AJAX: Delete an option
     */
    public function ajax_delete_option() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        $option_name = isset($_POST['option_name']) ? sanitize_text_field($_POST['option_name']) : '';

        if (empty($option_name)) {
            wp_send_json_error(['message' => __('Invalid option name', 'wpswitchboard')]);
        }

        $result = $this->delete_option_by_name($option_name);

        if ($result) {
            wp_send_json_success(['message' => __('Option deleted', 'wpswitchboard')]);
        } else {
            wp_send_json_error(['message' => __('Cannot delete this option (protected)', 'wpswitchboard')]);
        }
    }

    /**
     * AJAX: Get metadata
     */
    public function ajax_get_metadata() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        $page = isset($_POST['page']) ? (int) $_POST['page'] : 1;
        $search = isset($_POST['search']) ? sanitize_text_field($_POST['search']) : '';
        $sort = isset($_POST['sort']) ? sanitize_text_field($_POST['sort']) : 'size';

        wp_send_json_success($this->get_metadata($page, 50, $search, $sort));
    }

    /**
     * AJAX: Delete metadata by key
     */
    public function ajax_delete_metadata() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        $meta_key = isset($_POST['meta_key']) ? sanitize_text_field($_POST['meta_key']) : '';

        if (empty($meta_key)) {
            wp_send_json_error(['message' => __('Invalid meta key', 'wpswitchboard')]);
        }

        // Protect WordPress core meta keys and common plugin meta keys
        $protected_prefixes = [
            '_wp_',           // WordPress core
            '_edit_',         // Edit locks
            '_menu_',         // Menu items
            '_thumbnail',     // Featured images
            '_oembed_',       // Embed cache
            '_encloseme',     // Podcast enclosures
            '_pingme',        // Pingbacks
            '_elementor_',    // Elementor page builder
            '_fl_builder_',   // Beaver Builder
            '_et_',           // Divi
            '_yoast_',        // Yoast SEO
            '_rank_math_',    // Rank Math SEO
            '_wc_',           // WooCommerce
            '_product_',      // WooCommerce products
            '_customer_',     // WooCommerce customers
        ];
        foreach ($protected_prefixes as $prefix) {
            if (strpos($meta_key, $prefix) === 0) {
                wp_send_json_error(['message' => __('Cannot delete this meta key (protected)', 'wpswitchboard')]);
            }
        }

        global $wpdb;
        $deleted = $wpdb->delete($wpdb->postmeta, ['meta_key' => $meta_key]);

        if ($deleted !== false) {
            $this->log_cleanup('metadata_' . $meta_key, $deleted);
            wp_send_json_success([
                'message' => sprintf(__('Deleted %d entries', 'wpswitchboard'), $deleted),
                'count' => $deleted
            ]);
        } else {
            wp_send_json_error(['message' => __('Failed to delete metadata', 'wpswitchboard')]);
        }
    }

    /**
     * AJAX: Get cleanup logs
     */
    public function ajax_get_logs() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        $settings = $this->get_settings();

        wp_send_json_success([
            'logs' => array_reverse($settings['cleanup_log']),
            'size_history' => $settings['size_history'],
        ]);
    }

    /**
     * AJAX: Save settings
     */
    public function ajax_save_settings() {
        check_ajax_referer('switchboard_dbcleaner', 'nonce');

        if (!current_user_can('manage_options')) {
            wp_send_json_error(['message' => __('Unauthorized', 'wpswitchboard')]);
        }

        $settings = $this->get_settings();

        // Update auto clean
        $auto_clean = isset($_POST['auto_clean']) ? sanitize_key($_POST['auto_clean']) : 'disabled';
        if (in_array($auto_clean, ['disabled', 'daily', 'weekly'], true)) {
            $settings['auto_clean'] = $auto_clean;
        }

        // Update auto clean items - validate against allowed cleanup items
        if (isset($_POST['auto_clean_items']) && is_array($_POST['auto_clean_items'])) {
            $sanitized_items = array_map('sanitize_key', $_POST['auto_clean_items']);
            // Build list of valid item keys from cleanup_items
            $valid_keys = [];
            foreach ($this->cleanup_items as $category) {
                $valid_keys = array_merge($valid_keys, array_keys($category['items']));
            }
            // Only keep valid items
            $settings['auto_clean_items'] = array_values(array_intersect($sanitized_items, $valid_keys));
        }

        // Update batch size
        if (isset($_POST['batch_size'])) {
            $batch_size = (int) $_POST['batch_size'];
            if ($batch_size >= 10 && $batch_size <= 1000) {
                $settings['batch_size'] = $batch_size;
            }
        }

        update_option($this->option_key, $settings);

        // Update cron schedule
        wp_clear_scheduled_hook('switchboard_database_cleaner_cron');
        if ($settings['auto_clean'] !== 'disabled') {
            wp_schedule_event(time(), $settings['auto_clean'], 'switchboard_database_cleaner_cron');
        }

        wp_send_json_success(['message' => __('Settings saved', 'wpswitchboard')]);
    }
}

// Initialize
$switchboard_instance = new SwitchboardDatabaseCleaner();
Switchboard_Data_Registry::get_instance()->auto_register($switchboard_instance, __FILE__);
