<?php
/**
 * Name: Database Optimizer
 * Description: Clean and optimize WordPress database tables by removing post revisions, spam, trashed items, expired transients, and unused data to improve performance and speed
 * Category: Optimization
 * Icon: -
 * UseCases: [
 *   {
 *     "title": "Clean Database Bloat",
 *     "description": "Remove 5000+ post revisions, auto-drafts, and spam comments to shrink oversized database from 2GB to 500MB."
 *   },
 *   {
 *     "title": "Optimize Table Performance",
 *     "description": "Run OPTIMIZE TABLE on all WordPress tables to defragment and improve query speed on long-running sites."
 *   },
 *   {
 *     "title": "Pre-Migration Cleanup",
 *     "description": "Clean trash, transients, and spam before migrating to new host to reduce export file size and transfer time."
 *   }
 * ]
 * Added: 2025-08-18
 * Beta: true
 * Tags: database, db, optimization, cleanup, performance, mysql
 * WhereToFind: {
 *   "location": "Switchboard Settings",
 *   "url": "",
 *   "instructions": "Click 'Run Optimizer' button in module settings to clean revisions, auto-drafts, trash, spam comments, and optimize database tables.",
 *   "newTab": false
 * }
 */

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

class SwitchboardDatabaseOptimizer {
    
    public function __construct() {
        add_action('wp_ajax_switchboard_optimize_database', [$this, 'ajax_optimize_database']);
    }
    
    public function register_data_locations() {
        return [
            'options' => [
                'switchboard_database-optimizer_settings'
            ],
            'option_patterns' => [],
            'tables' => [],
            'post_meta' => [],
            'user_meta' => [],
            'term_meta' => [],
            'transients' => []
        ];
    }
    
    /**
     * Render settings panel for Database Optimizer
     * 
     * Displays interactive database optimization tools with:
     * - Styled checkbox options for selecting optimization tasks:
     *   * Delete post revisions
     *   * Delete auto-drafts
     *   * Empty trash
     *   * Delete spam comments
     *   * Delete expired transients
     *   * Optimize database tables
     * - Action button with inline JavaScript for AJAX execution
     * - Real-time feedback display with success/error states
     * - Modern Shadcn-style UI with hover effects and transitions
     * 
     * This module provides tools only (no settings are saved). Each operation is
     * executed via AJAX when the user clicks "Optimize Database" button. The inline
     * JavaScript collects selected options, shows confirmation dialog, makes fetch()
     * call to AJAX handler, and displays results.
     * 
     * All operations are destructive and cannot be undone, so confirmation is required.
     * 
     * @param array $settings Current module settings (unused - tool-only module)
     * @return void Outputs HTML directly with extensive inline CSS and JavaScript
     */
    public function render_settings($settings = []) {
        ?>
        <input type="hidden" name="module" value="database-optimizer">

        <div class="sb-sp-group" style="margin-bottom: 24px;">
            <h3 class="sb-sp-heading"><?php esc_html_e('Database Optimization', 'wpswitchboard'); ?></h3>
            <p class="sb-sp-description"><?php esc_html_e('Clean and optimize your WordPress database for better performance.', 'wpswitchboard'); ?></p>
        </div>

        <div class="sb-sp-card sb-sp-p-4" style="margin-bottom: 24px;">
            <div class="sb-module-option-item sb-module-toggle-item" style="margin-bottom: 16px;">
                <div class="sb-module-option-content">
                    <div class="sb-module-toggle-header">
                        <strong class="sb-module-option-title"><?php esc_html_e('Delete Post Revisions', 'wpswitchboard'); ?></strong>
                        <label class="sb-module-toggle">
                            <input type="checkbox" id="opt_revisions" checked>
                            <span class="sb-module-toggle-slider"></span>
                        </label>
                    </div>
                    <p class="sb-module-option-text"><?php esc_html_e('Remove all stored post revisions to reduce database size', 'wpswitchboard'); ?></p>
                </div>
            </div>

            <div class="sb-module-option-item sb-module-toggle-item" style="margin-bottom: 16px;">
                <div class="sb-module-option-content">
                    <div class="sb-module-toggle-header">
                        <strong class="sb-module-option-title"><?php esc_html_e('Delete Auto-Drafts', 'wpswitchboard'); ?></strong>
                        <label class="sb-module-toggle">
                            <input type="checkbox" id="opt_autodrafts" checked>
                            <span class="sb-module-toggle-slider"></span>
                        </label>
                    </div>
                    <p class="sb-module-option-text"><?php esc_html_e('Clean up automatically saved drafts that are no longer needed', 'wpswitchboard'); ?></p>
                </div>
            </div>

            <div class="sb-module-option-item sb-module-toggle-item" style="margin-bottom: 16px;">
                <div class="sb-module-option-content">
                    <div class="sb-module-toggle-header">
                        <strong class="sb-module-option-title"><?php esc_html_e('Empty Trash', 'wpswitchboard'); ?></strong>
                        <label class="sb-module-toggle">
                            <input type="checkbox" id="opt_trash" checked>
                            <span class="sb-module-toggle-slider"></span>
                        </label>
                    </div>
                    <p class="sb-module-option-text"><?php esc_html_e('Permanently delete all posts and pages in trash', 'wpswitchboard'); ?></p>
                </div>
            </div>

            <div class="sb-module-option-item sb-module-toggle-item" style="margin-bottom: 16px;">
                <div class="sb-module-option-content">
                    <div class="sb-module-toggle-header">
                        <strong class="sb-module-option-title"><?php esc_html_e('Delete Spam Comments', 'wpswitchboard'); ?></strong>
                        <label class="sb-module-toggle">
                            <input type="checkbox" id="opt_spam" checked>
                            <span class="sb-module-toggle-slider"></span>
                        </label>
                    </div>
                    <p class="sb-module-option-text"><?php esc_html_e('Remove all comments marked as spam from your database', 'wpswitchboard'); ?></p>
                </div>
            </div>

            <div class="sb-module-option-item sb-module-toggle-item" style="margin-bottom: 16px;">
                <div class="sb-module-option-content">
                    <div class="sb-module-toggle-header">
                        <strong class="sb-module-option-title"><?php esc_html_e('Delete Expired Transients', 'wpswitchboard'); ?></strong>
                        <label class="sb-module-toggle">
                            <input type="checkbox" id="opt_transients" checked>
                            <span class="sb-module-toggle-slider"></span>
                        </label>
                    </div>
                    <p class="sb-module-option-text"><?php esc_html_e('Clean up expired cached data and temporary options', 'wpswitchboard'); ?></p>
                </div>
            </div>

            <div class="sb-module-option-item sb-module-toggle-item" style="margin-bottom: 0;">
                <div class="sb-module-option-content">
                    <div class="sb-module-toggle-header">
                        <strong class="sb-module-option-title"><?php esc_html_e('Optimize Database Tables', 'wpswitchboard'); ?></strong>
                        <label class="sb-module-toggle">
                            <input type="checkbox" id="opt_optimize_tables">
                            <span class="sb-module-toggle-slider"></span>
                        </label>
                    </div>
                    <p class="sb-module-option-text"><?php esc_html_e('Defragment and optimize all database tables for better performance', 'wpswitchboard'); ?></p>
                </div>
            </div>
        </div>

        <div class="sb-sp-button-group" style="justify-content: flex-end; margin-bottom: 16px;">
            <button type="button" class="button button-primary button-small" id="db-optimizer-btn" data-i18n="<?php echo esc_attr(json_encode([
                'optimizing' => esc_html__('Optimizing...', 'wpswitchboard'),
                'optimizeDb' => esc_html__('Optimize Database', 'wpswitchboard'),
                'selectOne' => esc_html__('Please select at least one optimization option.', 'wpswitchboard'),
                'confirmOptimize' => esc_html__('Are you sure you want to optimize the database? This action cannot be undone.', 'wpswitchboard'),
                'errorOccurred' => esc_html__('An error occurred during optimization.', 'wpswitchboard'),
                'failedConnect' => esc_html__('Failed to connect to server. Please try again.', 'wpswitchboard')
            ])); ?>" onclick="
                const btn = document.getElementById('db-optimizer-btn');
                const feedback = document.getElementById('db-optimizer-feedback');
                const i18n = JSON.parse(btn.dataset.i18n);

                const options = [];
                if(document.getElementById('opt_revisions').checked) options.push('revisions');
                if(document.getElementById('opt_autodrafts').checked) options.push('autodrafts');
                if(document.getElementById('opt_trash').checked) options.push('trash');
                if(document.getElementById('opt_spam').checked) options.push('spam');
                if(document.getElementById('opt_transients').checked) options.push('transients');
                if(document.getElementById('opt_optimize_tables').checked) options.push('optimize_tables');

                if(options.length === 0) {
                    feedback.className = 'error';
                    feedback.textContent = i18n.selectOne;
                    feedback.style.display = 'block';
                    return;
                }

                if(!confirm(i18n.confirmOptimize)) return;

                // Disable button and show loading
                btn.disabled = true;
                btn.textContent = i18n.optimizing;
                feedback.style.display = 'none';

                fetch(ajaxurl, {
                    method: 'POST',
                    headers: {'Content-Type': 'application/x-www-form-urlencoded'},
                    body: 'action=switchboard_optimize_database&nonce=' + '<?php echo wp_create_nonce('switchboard_optimize_db'); ?>' + '&options=' + JSON.stringify(options)
                }).then(r => r.json()).then(d => {
                    if(d.success) {
                        feedback.className = 'success';
                        feedback.textContent = d.data.message;
                    } else {
                        feedback.className = 'error';
                        feedback.textContent = d.data.message || i18n.errorOccurred;
                    }
                    feedback.style.display = 'block';
                    btn.disabled = false;
                    btn.textContent = i18n.optimizeDb;
                }).catch(err => {
                    feedback.className = 'error';
                    feedback.textContent = i18n.failedConnect;
                    feedback.style.display = 'block';
                    btn.disabled = false;
                    btn.textContent = i18n.optimizeDb;
                });
            "><?php esc_html_e('Optimize Database', 'wpswitchboard'); ?></button>
        </div>

        <div id="db-optimizer-feedback" style="display: none; margin: 16px 0; padding: 12px 16px; border-radius: 6px; font-size: 13px; line-height: 1.6; white-space: pre-line;"></div>

        <div class="sb-sp-info-box sb-sp-info-box-info">
            <h4 class="sb-sp-heading-sm"><?php esc_html_e('Note', 'wpswitchboard'); ?></h4>
            <p class="sb-sp-description" style="margin: 0;"><?php esc_html_e('This module provides tools only. No automatic settings are saved.', 'wpswitchboard'); ?></p>
        </div>

        <style>
        #db-optimizer-feedback.success {
            background-color: #f0fdf4;
            border: 1px solid #86efac;
            color: #166534;
        }
        #db-optimizer-feedback.error {
            background-color: #fef2f2;
            border: 1px solid #fca5a5;
            color: #991b1b;
        }
        </style>
        <?php
    }
    
    public function ajax_optimize_database() {
        check_ajax_referer('switchboard_optimize_db', 'nonce');

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

        if (!isset($_POST['options'])) {
            wp_send_json_error(['message' => esc_html__('No options provided', 'wpswitchboard')]);
            return;
        }

        global $wpdb;
        $options = json_decode(stripslashes($_POST['options']), true);

        if (!is_array($options)) {
            wp_send_json_error(['message' => esc_html__('Invalid options format', 'wpswitchboard')]);
            return;
        }

        $results = [];
        $total_deleted = 0;

        foreach ($options as $option) {
            if (!is_string($option)) {
                continue;
            }

            switch ($option) {
                case 'revisions':
                    // First count
                    $before = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $wpdb->posts WHERE post_type = %s", 'revision'));
                    // Then delete
                    $count = $wpdb->query(
                        $wpdb->prepare("DELETE FROM $wpdb->posts WHERE post_type = %s", 'revision')
                    );
                    $results[] = sprintf(esc_html__('Deleted %d post revision%s', 'wpswitchboard'), $count, $count != 1 ? 's' : '');
                    $total_deleted += $count;
                    break;

                case 'autodrafts':
                    $count = $wpdb->query(
                        $wpdb->prepare("DELETE FROM $wpdb->posts WHERE post_status = %s", 'auto-draft')
                    );
                    $results[] = sprintf(esc_html__('Deleted %d auto-draft%s', 'wpswitchboard'), $count, $count != 1 ? 's' : '');
                    $total_deleted += $count;
                    break;

                case 'trash':
                    $count = $wpdb->query(
                        $wpdb->prepare("DELETE FROM $wpdb->posts WHERE post_status = %s", 'trash')
                    );
                    $results[] = sprintf(esc_html__('Emptied trash (%d item%s)', 'wpswitchboard'), $count, $count != 1 ? 's' : '');
                    $total_deleted += $count;
                    break;

                case 'spam':
                    $count = $wpdb->query(
                        $wpdb->prepare("DELETE FROM $wpdb->comments WHERE comment_approved = %s", 'spam')
                    );
                    $results[] = sprintf(esc_html__('Deleted %d spam comment%s', 'wpswitchboard'), $count, $count != 1 ? 's' : '');
                    $total_deleted += $count;
                    break;

                case 'transients':
                    $count = $wpdb->query(
                        $wpdb->prepare(
                            "DELETE FROM $wpdb->options WHERE option_name LIKE %s",
                            $wpdb->esc_like('_transient_') . '%'
                        )
                    );
                    $results[] = sprintf(esc_html__('Deleted %d transient%s', 'wpswitchboard'), $count, $count != 1 ? 's' : '');
                    $total_deleted += $count;
                    break;

                case 'optimize_tables':
                    $tables = $wpdb->get_results('SHOW TABLES', ARRAY_N);
                    $table_count = 0;
                    if (is_array($tables)) {
                        foreach ($tables as $table) {
                            if (isset($table[0])) {
                                // Validate table name against WordPress tables
                                $table_name = $table[0];
                                if (preg_match('/^[a-zA-Z0-9_]+$/', $table_name)) {
                                    $wpdb->query("OPTIMIZE TABLE `{$table_name}`");
                                    $table_count++;
                                }
                            }
                        }
                    }
                    $results[] = sprintf(esc_html__('Optimized %d database table%s', 'wpswitchboard'), $table_count, $table_count != 1 ? 's' : '');
                    break;
            }
        }

        // Add summary
        if ($total_deleted > 0) {
            array_unshift($results, sprintf(esc_html__('Done: Database optimization complete! Removed %d total item%s.', 'wpswitchboard'), $total_deleted, $total_deleted != 1 ? 's' : '') . "\n");
        } else {
            array_unshift($results, esc_html__('Done: Database optimization complete! No items needed cleaning.', 'wpswitchboard') . "\n");
        }

        wp_send_json_success(['message' => implode("\n", $results)]);
    }
}

$switchboard_instance = new SwitchboardDatabaseOptimizer();
Switchboard_Data_Registry::get_instance()->auto_register($switchboard_instance, __FILE__);
