opticore/includes/class-database-optimizer.php

<?php
/**
 * Database Optimizer Class
 * 
 * Handles database optimization tasks
 */

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

class OptiCore_Database_Optimizer {
    
    private static $instance = null;
    
    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    private function __construct() {
    }
    
    public function init() {
        // Schedule automatic optimization
        add_action('opticore_database_optimization', array($this, 'run_optimization'));
    }
    
    /**
     * Run database optimization
     */
    public function run_optimization() {
        global $wpdb;
        
        // Clean up post revisions (keep last 3)
        $this->cleanup_post_revisions(3);
        
        // Clean up auto-drafts
        $this->cleanup_auto_drafts();
        
        // Clean up trashed posts
        $this->cleanup_trash();
        
        // Clean up orphaned metadata
        $this->cleanup_orphaned_metadata();
        
        // Clean up transients
        $this->cleanup_transients();
        
        // Optimize database tables
        $this->optimize_tables();
        
        // Log optimization
        update_option('opticore_last_optimization', current_time('mysql'));
    }
    
    /**
     * Clean up post revisions
     */
    private function cleanup_post_revisions($keep = 3) {
        global $wpdb;
        
        $revisions = $wpdb->get_results(
            "SELECT r1.ID, r1.post_parent 
            FROM {$wpdb->posts} r1 
            WHERE r1.post_type = 'revision' 
            AND r1.post_parent IN (
                SELECT DISTINCT post_parent 
                FROM {$wpdb->posts} 
                WHERE post_type = 'revision'
            )"
        );
        
        $revision_groups = array();
        foreach ($revisions as $revision) {
            if (!isset($revision_groups[$revision->post_parent])) {
                $revision_groups[$revision->post_parent] = array();
            }
            $revision_groups[$revision->post_parent][] = $revision->ID;
        }
        
        foreach ($revision_groups as $parent_id => $revision_ids) {
            if (count($revision_ids) > $keep) {
                rsort($revision_ids);
                $to_delete = array_slice($revision_ids, $keep);
                
                foreach ($to_delete as $revision_id) {
                    wp_delete_post_revision($revision_id);
                }
            }
        }
    }
    
    /**
     * Clean up auto-drafts
     */
    private function cleanup_auto_drafts() {
        global $wpdb;
        
        $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$wpdb->posts} 
                WHERE post_status = 'auto-draft' 
                AND post_modified < %s",
                date('Y-m-d H:i:s', strtotime('-30 days'))
            )
        );
    }
    
    /**
     * Clean up trash
     */
    private function cleanup_trash() {
        global $wpdb;
        
        $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$wpdb->posts} 
                WHERE post_status = 'trash' 
                AND post_modified < %s",
                date('Y-m-d H:i:s', strtotime('-30 days'))
            )
        );
    }
    
    /**
     * Clean up orphaned metadata
     */
    private function cleanup_orphaned_metadata() {
        global $wpdb;
        
        // Clean orphaned post meta
        $wpdb->query(
            "DELETE pm FROM {$wpdb->postmeta} pm 
            LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id 
            WHERE p.ID IS NULL"
        );
        
        // Clean orphaned comment meta
        $wpdb->query(
            "DELETE cm FROM {$wpdb->commentmeta} cm 
            LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id 
            WHERE c.comment_ID IS NULL"
        );
        
        // Clean orphaned term relationships
        $wpdb->query(
            "DELETE tr FROM {$wpdb->term_relationships} tr 
            LEFT JOIN {$wpdb->posts} p ON p.ID = tr.object_id 
            WHERE p.ID IS NULL"
        );
    }
    
    /**
     * Clean up expired transients
     */
    private function cleanup_transients() {
        global $wpdb;
        
        $time = time();
        
        $wpdb->query(
            $wpdb->prepare(
                "DELETE FROM {$wpdb->options} 
                WHERE option_name LIKE '\_transient\_timeout\_%' 
                AND option_value < %d",
                $time
            )
        );
        
        // Clean up orphaned transient options
        $wpdb->query(
            "DELETE FROM {$wpdb->options} 
            WHERE option_name LIKE '\_transient\_%' 
            AND option_name NOT LIKE '\_transient\_timeout\_%' 
            AND option_name NOT IN (
                SELECT REPLACE(option_name, '_timeout', '') 
                FROM {$wpdb->options} 
                WHERE option_name LIKE '\_transient\_timeout\_%'
            )"
        );
    }
    
    /**
     * Optimize database tables
     */
    private function optimize_tables() {
        global $wpdb;
        
        $tables = $wpdb->get_results('SHOW TABLES', ARRAY_N);
        
        foreach ($tables as $table) {
            $table_name = $table[0];
            $wpdb->query("OPTIMIZE TABLE `{$table_name}`");
        }
    }
    
    /**
     * Get database statistics
     */
    public function get_database_stats() {
        global $wpdb;
        
        $stats = array();
        
        // Count revisions
        $stats['revisions'] = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'"
        );
        
        // Count auto-drafts
        $stats['auto_drafts'] = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
        );
        
        // Count trash
        $stats['trash'] = $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'"
        );
        
        // Database size
        $tables = $wpdb->get_results('SHOW TABLE STATUS');
        $size = 0;
        foreach ($tables as $table) {
            $size += $table->Data_length + $table->Index_length;
        }
        $stats['database_size'] = $size;
        
        // Last optimization
        $stats['last_optimization'] = get_option('opticore_last_optimization', __('Never', 'opticore'));
        
        return $stats;
    }
}