<?php
/**
 * Database Optimizer Service
 *
 * Provides comprehensive database maintenance for WooCommerce and WordPress
 * including cleanup of sessions, orders, revisions, transients, and more.
 *
 * @package Mamba\Modules\DB\Services
 * @since   1.0.0
 */

namespace Mamba\Modules\DB\Services;

use Mamba\Support\Logger;
use Mamba\Support\SavingsTracker;

/**
 * Class DatabaseOptimizer
 *
 * Core database optimizer that defines and executes cleanup tasks for
 * WooCommerce sessions, orders, stock, product data, and WordPress content.
 * Supports both legacy and HPOS (High-Performance Order Storage) tables.
 *
 * @since 1.0.0
 */
final class DatabaseOptimizer {
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    
    private array $tasks = [
        // 1) Action Scheduler history
        'as_prune' => [
            'label' => 'Purge old background jobs',
            'desc' => 'Delete Action Scheduler history older than the age you choose.',
            'default_interval' => 'weekly',
            'fields' => [
                'retention_days' => [30, 60, 90, 180],
                'statuses' => ['complete', 'failed'],
            ],
            'count_cb' => 'countAsPrune',
            'run_cb' => 'runAsPrune',
        ],
        // 2) Expired Woo sessions
        'sessions_expired' => [
            'label' => 'Clear expired sessions',
            'desc' => 'Delete expired rows from the WooCommerce sessions table.',
            'default_interval' => 'daily',
            'fields' => [],
            'count_cb' => 'countSessionsExpired',
            'run_cb' => 'runSessionsExpired',
        ],
        // 3) Stale reserved stock
        'reserved_stock' => [
            'label' => 'Clear stale reserved stock',
            'desc' => 'Delete stock reservations older than ~2× your "Hold stock (minutes)".',
            'default_interval' => 'daily',
            'fields' => [],
            'count_cb' => 'countReservedStock',
            'run_cb' => 'runReservedStock',
        ],
        // 4) Order items + itemmeta orphans (HPOS-aware parent check)
        'order_items_orphans' => [
            'label' => 'Clean orphaned order items & meta',
            'desc' => 'Remove order items with no parent order and their orphaned meta.',
            'default_interval' => 'weekly',
            'fields' => [],
            'count_cb' => 'countOrderItemsOrphans',
            'run_cb' => 'runOrderItemsOrphans',
        ],
        // 5) Order lookup orphans (HPOS tables)
        'order_lookup_orphans' => [
            'label' => 'Clean order lookup orphans',
            'desc' => 'Remove HPOS lookup rows with no matching order.',
            'default_interval' => 'weekly',
            'fields' => [],
            'count_cb' => 'countOrderLookupOrphans',
            'run_cb' => 'runOrderLookupOrphans',
        ],
        // 6) System order notes rotation (keeps customer notes)
        'system_notes_rotate' => [
            'label' => 'Remove old system order notes',
            'desc' => 'Delete system-generated order notes older than the age you choose (keeps customer notes).',
            'default_interval' => 'weekly',
            'fields' => [
                'months' => [3, 6, 12],
            ],
            'count_cb' => 'countSystemNotesRotate',
            'run_cb' => 'runSystemNotesRotate',
        ],
        // 7) Download log rotation
        'download_log_rotate' => [
            'label' => 'Prune old download logs',
            'desc' => 'Delete WooCommerce download-log entries older than the age you choose.',
            'default_interval' => 'weekly',
            'fields' => [
                'retention_days' => [30, 60, 90, 180, 365],
            ],
            'count_cb' => 'countDownloadLogRotate',
            'run_cb' => 'runDownloadLogRotate',
        ],
        // 8) Unused attribute terms + orphaned relationships + orphan terms
        'attr_terms_prune' => [
            'label' => 'Remove unused product attribute terms',
            'desc' => 'Delete unused attribute taxonomies (pa_*) & orphaned term relationships, then clean truly orphaned terms.',
            'default_interval' => 'weekly',
            'fields' => [],
            'count_cb' => 'countAttrTermsPrune',
            'run_cb' => 'runAttrTermsPrune',
        ],
        // 9) Order meta orphans (HPOS migration fallout)
        'order_meta_orphans' => [
            'label' => 'Clean orphaned order metadata',
            'desc' => 'Remove postmeta for deleted orders (legacy → HPOS migration cleanup).',
            'default_interval' => 'weekly',
            'fields' => [],
            'count_cb' => 'countOrderMetaOrphans',
            'run_cb' => 'runOrderMetaOrphans',
        ],
        // 10) Product meta orphans
        'product_meta_orphans' => [
            'label' => 'Clean orphaned product metadata',
            'desc' => 'Remove postmeta for deleted products and variations.',
            'default_interval' => 'weekly',
            'fields' => [],
            'count_cb' => 'countProductMetaOrphans',
            'run_cb' => 'runProductMetaOrphans',
        ],
        // 11) Customer sessions validation
        'customer_sessions_orphans' => [
            'label' => 'Clean sessions for deleted users',
            'desc' => 'Remove sessions where user_id no longer exists.',
            'default_interval' => 'weekly',
            'fields' => [],
            'count_cb' => 'countCustomerSessionsOrphans',
            'run_cb' => 'runCustomerSessionsOrphans',
        ],
        // 12) Draft checkout orders cleanup (WooCommerce)
        'draft_orders_cleanup' => [
            'label' => 'Delete draft checkout orders',
            'desc' => 'Remove abandoned checkout-draft orders older than the age you choose.',
            'default_interval' => 'daily',
            'fields' => [
                'retention_hours' => [1, 6, 12, 24, 48, 72],
            ],
            'count_cb' => 'countDraftOrders',
            'run_cb' => 'runDraftOrdersCleanup',
        ],
        // 13) Trashed posts cleanup
        'trashed_posts' => [
            'label' => 'Delete trashed posts',
            'desc' => 'Permanently delete posts, pages, and products in trash older than the age you choose.',
            'default_interval' => 'weekly',
            'fields' => [
                'retention_days' => [7, 14, 30, 60, 90],
            ],
            'count_cb' => 'countTrashedPosts',
            'run_cb' => 'runTrashedPostsCleanup',
        ],
        // 14) Post revisions cleanup
        'post_revisions' => [
            'label' => 'Delete old post revisions',
            'desc' => 'Remove post revisions older than the age you choose (keeps latest revision per post).',
            'default_interval' => 'weekly',
            'fields' => [
                'retention_days' => [30, 60, 90, 180, 365],
            ],
            'count_cb' => 'countPostRevisions',
            'run_cb' => 'runPostRevisionsCleanup',
        ],
        // 15) Auto-drafts cleanup (Premium)
        'auto_drafts' => [
            'label' => 'Delete auto-draft posts',
            'desc' => 'Remove auto-draft posts older than the age you choose.',
            'default_interval' => 'daily',
            'fields' => [
                'retention_days' => [1, 7, 14, 30],
            ],
            'count_cb' => 'countAutoDrafts',
            'run_cb' => 'runAutoDraftsCleanup',
            'premium' => true,
        ],
        // 16) Expired transients cleanup
        'expired_transients' => [
            'label' => 'Delete expired transients',
            'desc' => 'Remove expired transients from the options table to reduce database bloat.',
            'default_interval' => 'daily',
            'fields' => [],
            'count_cb' => 'countExpiredTransients',
            'run_cb' => 'runExpiredTransientsCleanup',
        ],
        // 17) Spam comments cleanup
        'spam_comments' => [
            'label' => 'Delete spam comments',
            'desc' => 'Remove spam comments and reviews older than the age you choose.',
            'default_interval' => 'weekly',
            'fields' => [
                'retention_days' => [7, 14, 30, 60],
            ],
            'count_cb' => 'countSpamComments',
            'run_cb' => 'runSpamCommentsCleanup',
        ],
        // 18) Trashed comments cleanup
        'trashed_comments' => [
            'label' => 'Delete trashed comments',
            'desc' => 'Permanently delete comments and reviews in trash older than the age you choose.',
            'default_interval' => 'weekly',
            'fields' => [
                'retention_days' => [7, 14, 30, 60],
            ],
            'count_cb' => 'countTrashedComments',
            'run_cb' => 'runTrashedCommentsCleanup',
        ],
    ];

    public function getTasks(): array {
        return $this->tasks;
    }

    public function getTaskIds(): array {
        return array_keys($this->tasks);
    }

    public function getTask(string $taskId): ?array {
        return $this->tasks[$taskId] ?? null;
    }

        public function calculateCounts(): array {
        global $wpdb;
        $counts = [];
        $settings = get_option('mamba_db_optimizer_settings', []);

        foreach ($this->tasks as $taskId => $task) {
            $taskSettings = $settings[$taskId] ?? [];
            $args = $taskSettings['args'] ?? [];

            // Set defaults for UI
            if (isset($task['fields']['retention_days']) && empty($args['retention_days'])) {
                $args['retention_days'] = $task['fields']['retention_days'][2] ?? 90;
            }
            if (isset($task['fields']['months']) && empty($args['months'])) {
                $args['months'] = 6;
            }
            if (isset($task['fields']['statuses']) && empty($args['statuses'])) {
                $args['statuses'] = $task['fields']['statuses'];
            }

            $countMethod = $task['count_cb'] ?? '';
            if (method_exists($this, $countMethod)) {
                try {
                    $counts[$taskId] = (int) $this->$countMethod($wpdb, $args);
                } catch (\Exception $e) {
                    // Log error but don't break the UI
                    if (defined('WP_DEBUG') && WP_DEBUG) {
                        error_log("Mamba DB Optimizer: Error calculating count for task {$taskId}: " . $e->getMessage());
                    }
                    $counts[$taskId] = 0;
                }
            } else {
                $counts[$taskId] = 0;
            }
        }

        return $counts;
    }

        public function executeTask(string $taskId): int {
        global $wpdb;

        $task = $this->getTask($taskId);
        if (!$task) {
            return 0;
        }

        // Feature Gating: Premium DB Tasks
        // FREE tasks (6): as_prune, post_revisions, expired_transients, spam_comments, trashed_comments, trashed_posts
        // PREMIUM tasks (12): All WooCommerce-specific and advanced cleanup tasks
        $premium_tasks = [
            'sessions_expired',           // Clear expired sessions
            'customer_sessions_orphans',  // Clean sessions for deleted users
            'reserved_stock',             // Clear stale reserved stock
            'order_lookup_orphans',       // Clean order lookup orphans
            'order_meta_orphans',         // Clean orphaned order metadata
            'download_log_rotate',        // Prune old download logs
            'order_items_orphans',        // Clean orphaned order items
            'system_notes_rotate',        // Remove old system order notes
            'attr_terms_prune',           // Remove unused attribute terms
            'product_meta_orphans',       // Clean orphaned product metadata
            'draft_orders_cleanup',       // Delete draft checkout orders
            'auto_drafts'                 // Delete auto-draft posts
        ];
        if (in_array($taskId, $premium_tasks, true)) {
            if (!function_exists('mamba_fs') || !mamba_fs()->can_use_premium_code__premium_only()) {
                return 0;
            }
        }

        $settings = get_option('mamba_db_optimizer_settings', []);
        $taskSettings = $settings[$taskId] ?? [];
        $args = $taskSettings['args'] ?? [];

        // Set defaults
        if (isset($task['fields']['retention_days']) && empty($args['retention_days'])) {
            $args['retention_days'] = $task['fields']['retention_days'][2] ?? 90;
        }
        if (isset($task['fields']['months']) && empty($args['months'])) {
            $args['months'] = 6;
        }
        if (isset($task['fields']['statuses']) && empty($args['statuses'])) {
            $args['statuses'] = $task['fields']['statuses'];
        }

        $runMethod = $task['run_cb'] ?? '';
        if (!method_exists($this, $runMethod)) {
            return 0;
        }

        try {
            $affected = (int) $this->$runMethod($wpdb, $args);

            // Update last run info
            $settings[$taskId]['last'] = time();
            $settings[$taskId]['last_rows'] = $affected;
            update_option('mamba_db_optimizer_settings', $settings);
            
            // Log the task completion
            Logger::info("DB task completed: {$taskId}", [
                'task' => $taskId,
                'rows_affected' => $affected,
                'args' => $args
            ]);
            
            // Track DB cleanup savings
            if ($affected > 0) {
                SavingsTracker::trackDbCleanup($affected);
            }
            
            // Trigger logging action
            do_action('mamba_db_task_completed', $taskId, $affected);

            return $affected;
        } catch (\Exception $e) {
            // Log error
            Logger::error("DB task failed: {$taskId}", [
                'task' => $taskId,
                'error' => $e->getMessage()
            ]);
            do_action('mamba_cache_error', "DB task {$taskId} failed: " . $e->getMessage(), ['task' => $taskId]);
            return 0;
        }
    }

    private function tableExists(string $name): bool {
        global $wpdb;
        try {
            $pattern = $wpdb->esc_like($name); // escape '_' and '%'
            $found = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $pattern));
            return $found === $name;
        } catch (\Exception $e) {
            return false;
        }
    }

    private function reservedStockDateColumn(): ?string {
        global $wpdb;
        try {
            $p = $wpdb->prefix;
            if (!$this->tableExists("{$p}wc_reserved_stock")) return null;
            $cols = $wpdb->get_col("SHOW COLUMNS FROM {$p}wc_reserved_stock", 0);
            if (!is_array($cols)) return null;
            // Preference order: most common first
            foreach (['date_created','created','timestamp','expires'] as $c) {
                if (in_array($c, $cols, true)) return $c;
            }
            return null;
        } catch (\Exception $e) {
            return null;
        }
    }

    private function sessionsUserIdColumn(): ?string {
        global $wpdb;
        try {
            $p = $wpdb->prefix;
            if (!$this->tableExists("{$p}woocommerce_sessions")) return null;
            $cols = $wpdb->get_col("SHOW COLUMNS FROM {$p}woocommerce_sessions", 0);
            if (!is_array($cols)) return null;
            // Check for common user ID column names
            foreach (['user_id','customer_id','user_key'] as $c) {
                if (in_array($c, $cols, true)) return $c;
            }
            return null;
        } catch (\Exception $e) {
            return null;
        }
    }

    private function hposEnabled(): bool {
        if (!class_exists('\Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController')) {
            return false;
        }
        try {
            $ctl = \Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController::class;
            $container = wc_get_container();
            if (!$container) return false;
            $obj = $container->get($ctl);
            return method_exists($obj, 'custom_orders_table_usage_is_enabled') && $obj->custom_orders_table_usage_is_enabled();
        } catch (\Throwable $e) {
            return false;
        }
    }

    // 1) Action Scheduler (UTC-safe)
    private function countAsPrune($wpdb, array $args): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}actionscheduler_actions")) {
            return 0;
        }
        $days = max(1, intval($args['retention_days'] ?? 90));
        $statuses = array_map('sanitize_text_field', (array)($args['statuses'] ?? ['complete', 'failed']));
        $in = implode("','", array_map('esc_sql', $statuses));
        return intval($wpdb->get_var("
            SELECT COUNT(*) FROM {$p}actionscheduler_actions
            WHERE status IN ('{$in}')
              AND scheduled_date_gmt < (UTC_TIMESTAMP() - INTERVAL {$days} DAY)
        "));
    }

    private function runAsPrune($wpdb, array $args): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}actionscheduler_actions")) {
            return 0;
        }
        $days = max(1, intval($args['retention_days'] ?? 90));
        $statuses = array_map('sanitize_text_field', (array)($args['statuses'] ?? ['complete', 'failed']));
        $in = implode("','", array_map('esc_sql', $statuses));
        
        $logs = $wpdb->query("
            DELETE l FROM {$p}actionscheduler_logs l
            JOIN {$p}actionscheduler_actions a ON a.action_id = l.action_id
            WHERE a.status IN ('{$in}')
              AND a.scheduled_date_gmt < (UTC_TIMESTAMP() - INTERVAL {$days} DAY)
        ");
        if ($logs === false) $logs = 0;
        
        $acts = $wpdb->query("
            DELETE FROM {$p}actionscheduler_actions
            WHERE status IN ('{$in}')
              AND scheduled_date_gmt < (UTC_TIMESTAMP() - INTERVAL {$days} DAY)
        ");
        if ($acts === false) $acts = 0;
        
        return $logs + $acts;
    }

    // 2) Sessions
    private function countSessionsExpired($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}woocommerce_sessions")) {
            return 0;
        }
        return intval($wpdb->get_var("SELECT COUNT(*) FROM {$p}woocommerce_sessions WHERE session_expiry < UNIX_TIMESTAMP()"));
    }

    private function runSessionsExpired($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}woocommerce_sessions")) {
            return 0;
        }
        $rows = $wpdb->query("DELETE FROM {$p}woocommerce_sessions WHERE session_expiry < UNIX_TIMESTAMP()");
        return ($rows === false) ? 0 : intval($rows);
    }

    // 3) Reserved stock
    private function countReservedStock($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}wc_reserved_stock")) {
            return 0;
        }
        $col = $this->reservedStockDateColumn();
        if (!$col) return 0;
        $minutes = max((int) get_option('woocommerce_hold_stock_minutes', 60) * 2, 10);
        return intval($wpdb->get_var( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            $wpdb->prepare("SELECT COUNT(*) FROM {$p}wc_reserved_stock WHERE {$col} < (NOW() - INTERVAL %d MINUTE)", $minutes) // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
        ));
    }

    private function runReservedStock($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}wc_reserved_stock")) {
            return 0;
        }
        $col = $this->reservedStockDateColumn();
        if (!$col) return 0;
        $minutes = max((int) get_option('woocommerce_hold_stock_minutes', 60) * 2, 10);
        $rows = $wpdb->query( // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
            $wpdb->prepare("DELETE FROM {$p}wc_reserved_stock WHERE {$col} < (NOW() - INTERVAL %d MINUTE)", $minutes) // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter
        );
        return ($rows === false) ? 0 : intval($rows);
    }

    // 4) Order items & meta orphans
    private function countOrderItemsOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}woocommerce_order_items")) {
            return 0;
        }

        $a = intval($wpdb->get_var("
            SELECT COUNT(*) FROM {$p}woocommerce_order_itemmeta m
            LEFT JOIN {$p}woocommerce_order_items i ON i.order_item_id = m.order_item_id
            WHERE i.order_item_id IS NULL
        "));

        if ($this->hposEnabled() && $this->tableExists("{$p}wc_orders")) {
            $b = intval($wpdb->get_var("
                SELECT COUNT(*) FROM {$p}woocommerce_order_items i
                LEFT JOIN {$p}wc_orders o ON o.id = i.order_id
                WHERE o.id IS NULL
            "));
        } else {
            $b = intval($wpdb->get_var("
                SELECT COUNT(*) FROM {$p}woocommerce_order_items i
                LEFT JOIN {$p}posts p ON p.ID = i.order_id
                WHERE p.ID IS NULL
            "));
        }
        return $a + $b;
    }

    private function runOrderItemsOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}woocommerce_order_items")) {
            return 0;
        }

        $m = $wpdb->query("
            DELETE m FROM {$p}woocommerce_order_itemmeta m
            LEFT JOIN {$p}woocommerce_order_items i ON i.order_item_id = m.order_item_id
            WHERE i.order_item_id IS NULL
        ");
        if ($m === false) $m = 0;

        if ($this->hposEnabled() && $this->tableExists("{$p}wc_orders")) {
            $i = $wpdb->query("
                DELETE i FROM {$p}woocommerce_order_items i
                LEFT JOIN {$p}wc_orders o ON o.id = i.order_id
                WHERE o.id IS NULL
            ");
        } else {
            $i = $wpdb->query("
                DELETE i FROM {$p}woocommerce_order_items i
                LEFT JOIN {$p}posts p ON p.ID = i.order_id
                WHERE p.ID IS NULL
            ");
        }
        if ($i === false) $i = 0;
        return $m + $i;
    }

    // 5) HPOS lookups
    private function countOrderLookupOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        $t = 0;
        if (!$this->tableExists("{$p}wc_orders")) {
            return 0;
        }
        if ($this->tableExists("{$p}wc_order_stats")) {
            $t += intval($wpdb->get_var("SELECT COUNT(*) FROM {$p}wc_order_stats s LEFT JOIN {$p}wc_orders o ON o.id = s.order_id WHERE o.id IS NULL"));
        }
        if ($this->tableExists("{$p}wc_order_product_lookup")) {
            $t += intval($wpdb->get_var("SELECT COUNT(*) FROM {$p}wc_order_product_lookup s LEFT JOIN {$p}wc_orders o ON o.id = s.order_id WHERE o.id IS NULL"));
        }
        if ($this->tableExists("{$p}wc_order_addresses")) {
            $t += intval($wpdb->get_var("SELECT COUNT(*) FROM {$p}wc_order_addresses s LEFT JOIN {$p}wc_orders o ON o.id = s.order_id WHERE o.id IS NULL"));
        }
        return $t;
    }

    private function runOrderLookupOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        $t = 0;
        if (!$this->tableExists("{$p}wc_orders")) {
            return 0;
        }
        if ($this->tableExists("{$p}wc_order_stats")) {
            $x = $wpdb->query("DELETE s FROM {$p}wc_order_stats s LEFT JOIN {$p}wc_orders o ON o.id = s.order_id WHERE o.id IS NULL");
            $t += ($x === false ? 0 : $x);
        }
        if ($this->tableExists("{$p}wc_order_product_lookup")) {
            $x = $wpdb->query("DELETE s FROM {$p}wc_order_product_lookup s LEFT JOIN {$p}wc_orders o ON o.id = s.order_id WHERE o.id IS NULL");
            $t += ($x === false ? 0 : $x);
        }
        if ($this->tableExists("{$p}wc_order_addresses")) {
            $x = $wpdb->query("DELETE s FROM {$p}wc_order_addresses s LEFT JOIN {$p}wc_orders o ON o.id = s.order_id WHERE o.id IS NULL");
            $t += ($x === false ? 0 : $x);
        }
        return $t;
    }

    // 6) System order notes (keeps customer notes)
    private function countSystemNotesRotate($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        $months = max(1, intval($args['months'] ?? 6));
        return intval($wpdb->get_var($wpdb->prepare("
            SELECT COUNT(*) FROM {$p}comments c
            LEFT JOIN {$p}commentmeta cm ON cm.comment_id = c.comment_ID AND cm.meta_key='is_customer_note'
            WHERE c.comment_type='order_note'
              AND COALESCE(cm.meta_value,'0')='0'
              AND c.comment_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d MONTH)
        ", $months)));
    }

    private function runSystemNotesRotate($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        $months = max(1, intval($args['months'] ?? 6));
        $rows = $wpdb->query($wpdb->prepare("
            DELETE c FROM {$p}comments c
            LEFT JOIN {$p}commentmeta cm ON cm.comment_id = c.comment_ID AND cm.meta_key='is_customer_note'
            WHERE c.comment_type='order_note'
              AND COALESCE(cm.meta_value,'0')='0'
              AND c.comment_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d MONTH)
        ", $months));
        if ($rows === false) $rows = 0;
        $wpdb->query("DELETE FROM {$p}commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM {$p}comments)");
        return $rows;
    }

    // 7) Download logs
    private function countDownloadLogRotate($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}wc_download_log")) {
            return 0;
        }
        $days = max(1, intval($args['retention_days'] ?? 180));
        return intval($wpdb->get_var("SELECT COUNT(*) FROM {$p}wc_download_log WHERE timestamp < (NOW() - INTERVAL {$days} DAY)"));
    }

    private function runDownloadLogRotate($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}wc_download_log")) {
            return 0;
        }
        $days = max(1, intval($args['retention_days'] ?? 180));
        $rows = $wpdb->query("DELETE FROM {$p}wc_download_log WHERE timestamp < (NOW() - INTERVAL {$days} DAY)");
        return ($rows === false) ? 0 : intval($rows);
    }

    // 8) Attribute terms + orphaned relationships + orphan terms
    private function countAttrTermsPrune($wpdb, array $args = []): int {
        $p = $wpdb->prefix;

        // Unused attribute taxonomies (pa_*) = no products relate to them
        $unused_attr_tt = intval($wpdb->get_var("
            SELECT COUNT(*) FROM {$p}term_taxonomy tt
            LEFT JOIN {$p}term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
            WHERE tt.taxonomy LIKE 'pa_%'
              AND tr.object_id IS NULL
        "));

        // Orphaned term_relationships (object deleted)
        $orphan_tr = intval($wpdb->get_var("
            SELECT COUNT(*) FROM {$p}term_relationships tr
            LEFT JOIN {$p}posts p ON p.ID = tr.object_id
            WHERE p.ID IS NULL
        "));

        // Truly orphaned terms (no taxonomy rows at all)
        $orphan_terms = intval($wpdb->get_var("
            SELECT COUNT(*) FROM {$p}terms t
            LEFT JOIN {$p}term_taxonomy tt ON tt.term_id = t.term_id
            WHERE tt.term_id IS NULL
        "));

        return $unused_attr_tt + $orphan_tr + $orphan_terms;
    }

    private function runAttrTermsPrune($wpdb, array $args = []): int {
        $p = $wpdb->prefix;

        // Delete unused attribute taxonomies (pa_*)
        $a = $wpdb->query("
            DELETE tt FROM {$p}term_taxonomy tt
            LEFT JOIN {$p}term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
            WHERE tt.taxonomy LIKE 'pa_%'
              AND tr.object_id IS NULL
        ");
        if ($a === false) $a = 0;

        // Delete orphaned term_relationships
        $b = $wpdb->query("
            DELETE tr FROM {$p}term_relationships tr
            LEFT JOIN {$p}posts p ON p.ID = tr.object_id
            WHERE p.ID IS NULL
        ");
        if ($b === false) $b = 0;

        // Delete truly orphaned terms (no taxonomy left)
        $c = $wpdb->query("
            DELETE t FROM {$p}terms t
            LEFT JOIN {$p}term_taxonomy tt ON tt.term_id = t.term_id
            WHERE tt.term_id IS NULL
        ");
        if ($c === false) $c = 0;

        return $a + $b + $c;
    }

    // 9) Order meta orphans (legacy → missing posts that were orders)
    private function countOrderMetaOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;

        if ($this->hposEnabled() && $this->tableExists("{$p}wc_orders")) {
            $aggressive = apply_filters('mamba_db_hpos_aggressive_meta_cleanup', true);
            if ($aggressive) {
                // HPOS mode: posts that exist as shop_order but not in wc_orders (stale legacy rows)
                return (int) $wpdb->get_var("
                    SELECT COUNT(*) FROM {$p}postmeta pm
                    INNER JOIN {$p}posts po ON po.ID = pm.post_id AND po.post_type = 'shop_order'
                    LEFT JOIN {$p}wc_orders o ON o.id = po.ID
                    WHERE o.id IS NULL
                ");
            }
        }

        // Legacy mode: orphan meta where the parent post is gone, and the id looks like an order (via presence of _order_key)
        return (int) $wpdb->get_var("
            SELECT COUNT(*) FROM {$p}postmeta pm
            LEFT JOIN {$p}posts po ON po.ID = pm.post_id
            WHERE po.ID IS NULL
              AND EXISTS (
                  SELECT 1 FROM {$p}postmeta pm2
                  WHERE pm2.post_id = pm.post_id AND pm2.meta_key = '_order_key'
              )
        ");
    }

    private function runOrderMetaOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;

        if ($this->hposEnabled() && $this->tableExists("{$p}wc_orders")) {
            $aggressive = apply_filters('mamba_db_hpos_aggressive_meta_cleanup', true);
            if ($aggressive) {
                // HPOS mode: delete meta for legacy shop_order posts that never made it into wc_orders
                $rows = $wpdb->query("
                    DELETE pm FROM {$p}postmeta pm
                    INNER JOIN {$p}posts po ON po.ID = pm.post_id AND po.post_type = 'shop_order'
                    LEFT JOIN {$p}wc_orders o ON o.id = po.ID
                    WHERE o.id IS NULL
                ");
                return ($rows === false) ? 0 : (int) $rows;
            }
        }

        // Legacy mode: delete meta for missing posts that look like orders
        $rows = $wpdb->query("
            DELETE pm FROM {$p}postmeta pm
            LEFT JOIN {$p}posts po ON po.ID = pm.post_id
            WHERE po.ID IS NULL
              AND EXISTS (
                  SELECT 1 FROM {$p}postmeta pm2
                  WHERE pm2.post_id = pm.post_id AND pm2.meta_key = '_order_key'
              )
        ");
        return ($rows === false) ? 0 : (int) $rows;
    }

    // 10) Product meta orphans (missing posts + productish keys)
    private function countProductMetaOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        // Heuristic: common Woo product meta keys
        $keys = ["_sku","_price","_regular_price","_sale_price","_product_attributes","_stock","_stock_status"];
        $in   = "'" . implode("','", array_map('esc_sql', $keys)) . "'";

        return (int) $wpdb->get_var("
            SELECT COUNT(*) FROM {$p}postmeta pm
            LEFT JOIN {$p}posts po ON po.ID = pm.post_id
            WHERE po.ID IS NULL
              AND pm.meta_key IN ($in)
        ");
    }

    private function runProductMetaOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        $keys = ["_sku","_price","_regular_price","_sale_price","_product_attributes","_stock","_stock_status"];
        
        // Build placeholders for prepare()
        $placeholders = implode(',', array_fill(0, count($keys), '%s'));
        
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->query($wpdb->prepare(
            "DELETE pm FROM {$p}postmeta pm
            LEFT JOIN {$p}posts po ON po.ID = pm.post_id
            WHERE po.ID IS NULL
              AND pm.meta_key IN ($placeholders)",
            $keys
        ));
        return ($rows === false) ? 0 : (int) $rows;
    }

    // 11) Customer sessions validation
    private function countCustomerSessionsOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}woocommerce_sessions")) {
            return 0;
        }
        
        // Column name comes from schema inspection - whitelist valid column names
        $userCol = $this->sessionsUserIdColumn();
        if (!$userCol || !in_array($userCol, ['user_id', 'customer_id', 'user_key'], true)) {
            return 0;
        }
        
        // Build query with whitelisted column name (safe identifier)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return intval($wpdb->get_var(
            "SELECT COUNT(*) FROM {$p}woocommerce_sessions s
            LEFT JOIN {$p}users u ON u.ID = s.`" . esc_sql($userCol) . "`
            WHERE s.`" . esc_sql($userCol) . "` > 0
              AND u.ID IS NULL"
        ));
    }

    private function runCustomerSessionsOrphans($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        if (!$this->tableExists("{$p}woocommerce_sessions")) {
            return 0;
        }
        
        // Column name comes from schema inspection - whitelist valid column names
        $userCol = $this->sessionsUserIdColumn();
        if (!$userCol || !in_array($userCol, ['user_id', 'customer_id', 'user_key'], true)) {
            return 0;
        }
        
        // Build query with whitelisted column name (safe identifier)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->query(
            "DELETE s FROM {$p}woocommerce_sessions s
            LEFT JOIN {$p}users u ON u.ID = s.`" . esc_sql($userCol) . "`
            WHERE s.`" . esc_sql($userCol) . "` > 0
              AND u.ID IS NULL"
        );
        
        return ($rows === false) ? 0 : intval($rows);
    }

    // 12) Draft checkout orders cleanup (WooCommerce)
    private function countDraftOrders($wpdb, array $args = []): int {
        $hours = max(1, intval($args['retention_hours'] ?? 24));
        
        // Check if HPOS is enabled
        if ($this->hposEnabled() && $this->tableExists("{$wpdb->prefix}wc_orders")) {
            return intval($wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->prefix}wc_orders 
                WHERE status = 'checkout-draft' 
                AND date_created_gmt < (UTC_TIMESTAMP() - INTERVAL %d HOUR)",
                $hours
            )));
        }
        
        // Legacy posts table
        return intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}posts 
            WHERE post_type = 'shop_order' 
            AND post_status = 'checkout-draft' 
            AND post_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d HOUR)",
            $hours
        )));
    }

    private function runDraftOrdersCleanup($wpdb, array $args = []): int {
        $hours = max(1, intval($args['retention_hours'] ?? 24));
        $deleted = 0;
        
        // Check if HPOS is enabled
        if ($this->hposEnabled() && $this->tableExists("{$wpdb->prefix}wc_orders")) {
            // Get draft order IDs first
            $orderIds = $wpdb->get_col($wpdb->prepare(
                "SELECT id FROM {$wpdb->prefix}wc_orders 
                WHERE status = 'checkout-draft' 
                AND date_created_gmt < (UTC_TIMESTAMP() - INTERVAL %d HOUR)
                LIMIT 1000",
                $hours
            ));
            
            if (!empty($orderIds)) {
                foreach ($orderIds as $orderId) {
                    // Use WooCommerce's delete function if available for proper cleanup
                    if (function_exists('wc_get_order')) {
                        $order = wc_get_order($orderId);
                        if ($order) {
                            $order->delete(true); // Force delete
                            $deleted++;
                        }
                    }
                }
            }
        } else {
            // Legacy posts table - use WooCommerce functions for proper cleanup
            $orderIds = $wpdb->get_col($wpdb->prepare(
                "SELECT ID FROM {$wpdb->prefix}posts 
                WHERE post_type = 'shop_order' 
                AND post_status = 'checkout-draft' 
                AND post_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d HOUR)
                LIMIT 1000",
                $hours
            ));
            
            if (!empty($orderIds)) {
                foreach ($orderIds as $orderId) {
                    if (function_exists('wc_get_order')) {
                        $order = wc_get_order($orderId);
                        if ($order) {
                            $order->delete(true); // Force delete
                            $deleted++;
                        }
                    }
                }
            }
        }
        
        return $deleted;
    }

    // 13) Trashed posts cleanup
    private function countTrashedPosts($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 30));
        return intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}posts 
            WHERE post_status = 'trash' 
            AND post_modified_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)",
            $days
        )));
    }

    private function runTrashedPostsCleanup($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 30));
        $p = $wpdb->prefix;
        
        // Get IDs of posts to delete
        $postIds = $wpdb->get_col($wpdb->prepare(
            "SELECT ID FROM {$p}posts 
            WHERE post_status = 'trash' 
            AND post_modified_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)
            LIMIT 1000",
            $days
        ));
        
        if (empty($postIds)) {
            return 0;
        }
        
        $deleted = 0;
        foreach ($postIds as $postId) {
            // Use WordPress function for proper cleanup (removes meta, terms, etc.)
            if (wp_delete_post($postId, true)) {
                $deleted++;
            }
        }
        
        return $deleted;
    }

    // 14) Post revisions cleanup
    private function countPostRevisions($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 90));
        return intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}posts 
            WHERE post_type = 'revision' 
            AND post_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)",
            $days
        )));
    }

    private function runPostRevisionsCleanup($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 90));
        $p = $wpdb->prefix;
        
        // Get revision IDs to delete (excluding the most recent revision per parent)
        $revisionIds = $wpdb->get_col($wpdb->prepare(
            "SELECT r.ID FROM {$p}posts r
            WHERE r.post_type = 'revision'
            AND r.post_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)
            AND r.ID NOT IN (
                SELECT MAX(r2.ID) FROM {$p}posts r2
                WHERE r2.post_type = 'revision'
                AND r2.post_parent = r.post_parent
                GROUP BY r2.post_parent
            )
            LIMIT 1000",
            $days
        ));
        
        if (empty($revisionIds)) {
            return 0;
        }
        
        $deleted = 0;
        foreach ($revisionIds as $revisionId) {
            if (wp_delete_post_revision($revisionId)) {
                $deleted++;
            }
        }
        
        return $deleted;
    }

    // 15) Auto-drafts cleanup
    private function countAutoDrafts($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 7));
        return intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}posts 
            WHERE post_status = 'auto-draft' 
            AND post_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)",
            $days
        )));
    }

    private function runAutoDraftsCleanup($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 7));
        $p = $wpdb->prefix;
        
        // Get auto-draft IDs
        $postIds = $wpdb->get_col($wpdb->prepare(
            "SELECT ID FROM {$p}posts 
            WHERE post_status = 'auto-draft' 
            AND post_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)
            LIMIT 1000",
            $days
        ));
        
        if (empty($postIds)) {
            return 0;
        }
        
        $deleted = 0;
        foreach ($postIds as $postId) {
            if (wp_delete_post($postId, true)) {
                $deleted++;
            }
        }
        
        return $deleted;
    }

    // 16) Expired transients cleanup
    private function countExpiredTransients($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        
        // Count expired transients (timeout has passed)
        return intval($wpdb->get_var(
            "SELECT COUNT(*) FROM {$p}options 
            WHERE option_name LIKE '_transient_timeout_%' 
            AND option_value < UNIX_TIMESTAMP()"
        ));
    }

    private function runExpiredTransientsCleanup($wpdb, array $args = []): int {
        $p = $wpdb->prefix;
        
        // Get expired transient names
        $expiredTimeouts = $wpdb->get_col(
            "SELECT option_name FROM {$p}options 
            WHERE option_name LIKE '_transient_timeout_%' 
            AND option_value < UNIX_TIMESTAMP()
            LIMIT 1000"
        );
        
        if (empty($expiredTimeouts)) {
            return 0;
        }
        
        $deleted = 0;
        foreach ($expiredTimeouts as $timeoutName) {
            // Extract transient name from timeout option name
            $transientName = str_replace('_transient_timeout_', '', $timeoutName);
            
            // Delete both the transient and its timeout
            if (delete_transient($transientName)) {
                $deleted++;
            }
        }
        
        // Also clean up site transients if multisite
        if (is_multisite()) {
            $expiredSiteTimeouts = $wpdb->get_col(
                "SELECT option_name FROM {$p}options 
                WHERE option_name LIKE '_site_transient_timeout_%' 
                AND option_value < UNIX_TIMESTAMP()
                LIMIT 500"
            );
            
            foreach ($expiredSiteTimeouts as $timeoutName) {
                $transientName = str_replace('_site_transient_timeout_', '', $timeoutName);
                if (delete_site_transient($transientName)) {
                    $deleted++;
                }
            }
        }
        
        return $deleted;
    }

    // 17) Spam comments cleanup
    private function countSpamComments($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 14));
        return intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}comments 
            WHERE comment_approved = 'spam' 
            AND comment_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)",
            $days
        )));
    }

    private function runSpamCommentsCleanup($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 14));
        $p = $wpdb->prefix;
        
        // Get spam comment IDs
        $commentIds = $wpdb->get_col($wpdb->prepare(
            "SELECT comment_ID FROM {$p}comments 
            WHERE comment_approved = 'spam' 
            AND comment_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)
            LIMIT 1000",
            $days
        ));
        
        if (empty($commentIds)) {
            return 0;
        }
        
        $deleted = 0;
        foreach ($commentIds as $commentId) {
            if (wp_delete_comment($commentId, true)) {
                $deleted++;
            }
        }
        
        return $deleted;
    }

    // 18) Trashed comments cleanup
    private function countTrashedComments($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 30));
        return intval($wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$wpdb->prefix}comments 
            WHERE comment_approved = 'trash' 
            AND comment_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)",
            $days
        )));
    }

    private function runTrashedCommentsCleanup($wpdb, array $args = []): int {
        $days = max(1, intval($args['retention_days'] ?? 30));
        $p = $wpdb->prefix;
        
        // Get trashed comment IDs
        $commentIds = $wpdb->get_col($wpdb->prepare(
            "SELECT comment_ID FROM {$p}comments 
            WHERE comment_approved = 'trash' 
            AND comment_date_gmt < (UTC_TIMESTAMP() - INTERVAL %d DAY)
            LIMIT 1000",
            $days
        ));
        
        if (empty($commentIds)) {
            return 0;
        }
        
        $deleted = 0;
        foreach ($commentIds as $commentId) {
            if (wp_delete_comment($commentId, true)) {
                $deleted++;
            }
        }
        
        return $deleted;
    }
}
