<?php

/**
 * Other cleanup functionality for Wuclean plugin.
 *
 * @package Wuclean
 */
if ( !defined( 'ABSPATH' ) ) {
    exit;
}
if ( !defined( 'PT_WUCLEAN_OTHER_ORPHANED_DATA_KEY' ) ) {
    define( 'PT_WUCLEAN_OTHER_ORPHANED_DATA_KEY', 'pt_wuclean_other_orphaned_data' );
}
if ( !defined( 'PT_WUCLEAN_OTHER_ORPHANED_DATA_OPTION' ) ) {
    define( 'PT_WUCLEAN_OTHER_ORPHANED_DATA_OPTION', 'pt_wuclean_other_orphaned_data_cache' );
}
/**
 * Persist Other module orphaned data for 24 hours (transient + option fallback).
 *
 * @param array $data Orphaned data array to store.
 * @return void
 */
function pt_wuclean_other_store_orphaned_data(  $data  ) {
    if ( !is_array( $data ) ) {
        $data = array();
    }
    set_transient( PT_WUCLEAN_OTHER_ORPHANED_DATA_KEY, $data, DAY_IN_SECONDS );
    update_option( PT_WUCLEAN_OTHER_ORPHANED_DATA_OPTION, array(
        'data'      => $data,
        'stored_at' => time(),
        'expires'   => time() + DAY_IN_SECONDS,
    ), false );
}

/**
 * Remove stored Other module orphaned data.
 *
 * @return void
 */
function pt_wuclean_other_delete_orphaned_data() {
    delete_transient( PT_WUCLEAN_OTHER_ORPHANED_DATA_KEY );
    delete_option( PT_WUCLEAN_OTHER_ORPHANED_DATA_OPTION );
}

/**
 * Retrieve Other module orphaned data with legacy fallbacks.
 *
 * @return array
 */
function pt_wuclean_other_get_orphaned_data() {
    $data = get_transient( PT_WUCLEAN_OTHER_ORPHANED_DATA_KEY );
    if ( false !== $data && is_array( $data ) ) {
        return $data;
    }
    $option_value = get_option( PT_WUCLEAN_OTHER_ORPHANED_DATA_OPTION, array() );
    if ( is_array( $option_value ) && isset( $option_value['data'] ) ) {
        $expires = ( isset( $option_value['expires'] ) ? intval( $option_value['expires'] ) : 0 );
        if ( 0 === $expires || $expires > time() ) {
            $cached_data = ( is_array( $option_value['data'] ) ? $option_value['data'] : array() );
            set_transient( PT_WUCLEAN_OTHER_ORPHANED_DATA_KEY, $cached_data, DAY_IN_SECONDS );
            return $cached_data;
        }
    }
    return array();
}

/**
 * Other page with Settings tab.
 */
function pt_wuclean_render_other_page() {
    // Defense-in-depth: the admin menu is capability-gated, but verify again before reading request input.
    if ( !current_user_can( pt_wuclean_get_required_capability() ) ) {
        wp_die( esc_html__( 'You do not have sufficient permissions to access this page.', 'wuclean-database-cleaner-for-woocommerce' ) );
    }
    // Tab query arg: settings is the default; other tabs require a valid tab nonce (see nav links).
    $current_tab = 'settings';
    $valid_tabs = array('settings', 'orphaned_data');
    if ( isset( $_GET['tab'] ) ) {
        $tab_value = sanitize_text_field( wp_unslash( $_GET['tab'] ) );
        if ( !in_array( $tab_value, $valid_tabs, true ) ) {
            $current_tab = 'settings';
        } elseif ( 'settings' === $tab_value ) {
            $current_tab = 'settings';
        } elseif ( isset( $_GET['_wpnonce'] ) && wp_verify_nonce( sanitize_text_field( wp_unslash( $_GET['_wpnonce'] ) ), 'wuclean-other-tab' ) ) {
            $current_tab = $tab_value;
        }
    }
    echo '<div class="wrap">';
    echo '<h1>🔧 ' . esc_html__( 'Other', 'wuclean-database-cleaner-for-woocommerce' ) . '</h1>';
    // Tab navigation.
    echo '<nav class="nav-tab-wrapper">';
    echo '<a href="' . esc_url( add_query_arg( array(
        'page'     => 'wuclean-other',
        'tab'      => 'settings',
        '_wpnonce' => wp_create_nonce( 'wuclean-other-tab' ),
    ), admin_url( 'admin.php' ) ) ) . '" class="nav-tab' . (( 'settings' === $current_tab ? ' nav-tab-active' : '' )) . '">' . esc_html__( 'Settings', 'wuclean-database-cleaner-for-woocommerce' ) . '</a>';
    echo '<a href="' . esc_url( add_query_arg( array(
        'page'     => 'wuclean-other',
        'tab'      => 'orphaned_data',
        '_wpnonce' => wp_create_nonce( 'wuclean-other-tab' ),
    ), admin_url( 'admin.php' ) ) ) . '" class="nav-tab' . (( 'orphaned_data' === $current_tab ? ' nav-tab-active' : '' )) . '">' . esc_html__( 'Orphaned Data', 'wuclean-database-cleaner-for-woocommerce' ) . '</a>';
    echo '</nav>';
    // Standard inner wrapper for all tabs (max width + side padding).
    echo '<div class="pt-wuclean-page-inner" style="max-width: 1600px; box-sizing: border-box;">';
    if ( 'settings' === $current_tab ) {
        pt_wuclean_render_other_settings_tab();
    } elseif ( 'orphaned_data' === $current_tab ) {
        pt_wuclean_render_other_orphaned_data_tab();
    }
    echo '</div>';
    // Close inner wrapper.
    echo '</div>';
}

/**
 * Other Settings Tab.
 */
function pt_wuclean_render_other_settings_tab() {
    if ( !current_user_can( pt_wuclean_get_required_capability() ) ) {
        wp_die( esc_html__( 'You do not have sufficient permissions to access this page.', 'wuclean-database-cleaner-for-woocommerce' ) );
    }
    // Handle form submission.
    if ( isset( $_POST['pt_wuclean_other_settings_nonce'] ) && wp_verify_nonce( sanitize_text_field( wp_unslash( $_POST['pt_wuclean_other_settings_nonce'] ) ), 'pt_wuclean_save_other_settings' ) ) {
        $settings = get_option( 'pt_wuclean_settings', array() );
        // Comments.
        $settings['enable_trash_spam_reviews'] = ( isset( $_POST['enable_trash_spam_reviews'] ) ? 1 : 0 );
        $settings['trash_spam_reviews_days'] = max( 1, min( 365, absint( wp_unslash( $_POST['trash_spam_reviews_days'] ?? 30 ) ) ) );
        $settings['enable_trashed_reviews_cleanup'] = ( isset( $_POST['enable_trashed_reviews_cleanup'] ) ? 1 : 0 );
        $settings['enable_trash_spam_comments'] = ( isset( $_POST['enable_trash_spam_comments'] ) ? 1 : 0 );
        $settings['trash_spam_comments_days'] = max( 1, min( 365, absint( wp_unslash( $_POST['trash_spam_comments_days'] ?? 30 ) ) ) );
        $settings['enable_trashed_comments_cleanup'] = ( isset( $_POST['enable_trashed_comments_cleanup'] ) ? 1 : 0 );
        // Posts & Pages.
        $settings['enable_post_revisions_cleanup'] = ( isset( $_POST['enable_post_revisions_cleanup'] ) ? 1 : 0 );
        $settings['post_revisions_keep_count'] = max( 1, min( 10, absint( wp_unslash( $_POST['post_revisions_keep_count'] ?? 3 ) ) ) );
        $settings['post_revisions_max_age_days'] = max( 30, min( 3650, absint( wp_unslash( $_POST['post_revisions_max_age_days'] ?? 365 ) ) ) );
        $settings['enable_orphaned_revisions_cleanup'] = ( isset( $_POST['enable_orphaned_revisions_cleanup'] ) ? 1 : 0 );
        $settings['enable_auto_drafts_cleanup'] = ( isset( $_POST['enable_auto_drafts_cleanup'] ) ? 1 : 0 );
        $settings['auto_drafts_days'] = max( 1, min( 365, absint( wp_unslash( $_POST['auto_drafts_days'] ?? 7 ) ) ) );
        $settings['enable_trashed_posts_cleanup'] = ( isset( $_POST['enable_trashed_posts_cleanup'] ) ? 1 : 0 );
        $settings['trashed_posts_days'] = max( 1, min( 365, absint( wp_unslash( $_POST['trashed_posts_days'] ?? 30 ) ) ) );
        // Database Optimization.
        $settings['enable_expired_transients_cleanup'] = ( isset( $_POST['enable_expired_transients_cleanup'] ) ? 1 : 0 );
        $settings['enable_orphaned_postmeta_cleanup'] = ( isset( $_POST['enable_orphaned_postmeta_cleanup'] ) ? 1 : 0 );
        $settings['enable_orphaned_commentmeta_cleanup'] = ( isset( $_POST['enable_orphaned_commentmeta_cleanup'] ) ? 1 : 0 );
        $settings['enable_orphaned_usermeta_cleanup'] = ( isset( $_POST['enable_orphaned_usermeta_cleanup'] ) ? 1 : 0 );
        $settings['enable_orphaned_term_relationships_cleanup'] = ( isset( $_POST['enable_orphaned_term_relationships_cleanup'] ) ? 1 : 0 );
        update_option( 'pt_wuclean_settings', $settings, false );
        echo '<div class="notice notice-success"><p>' . esc_html__( 'Other cleanup settings saved successfully!', 'wuclean-database-cleaner-for-woocommerce' ) . '</p></div>';
    }
    $settings = get_option( 'pt_wuclean_settings', array() );
    // Comments.
    $enable_trash_spam_reviews = $settings['enable_trash_spam_reviews'] ?? 0;
    $trash_spam_reviews_days = $settings['trash_spam_reviews_days'] ?? 30;
    $enable_trashed_reviews_cleanup = $settings['enable_trashed_reviews_cleanup'] ?? 0;
    $enable_trash_spam_comments = $settings['enable_trash_spam_comments'] ?? 0;
    $trash_spam_comments_days = $settings['trash_spam_comments_days'] ?? 30;
    $enable_trashed_comments_cleanup = $settings['enable_trashed_comments_cleanup'] ?? 0;
    // Posts & Pages.
    $enable_post_revisions_cleanup = $settings['enable_post_revisions_cleanup'] ?? 0;
    $post_revisions_keep_count = $settings['post_revisions_keep_count'] ?? 3;
    $post_revisions_max_age_days = $settings['post_revisions_max_age_days'] ?? 365;
    $enable_orphaned_revisions_cleanup = $settings['enable_orphaned_revisions_cleanup'] ?? 0;
    $enable_auto_drafts_cleanup = $settings['enable_auto_drafts_cleanup'] ?? 0;
    $auto_drafts_days = $settings['auto_drafts_days'] ?? 7;
    $enable_trashed_posts_cleanup = $settings['enable_trashed_posts_cleanup'] ?? 0;
    $trashed_posts_days = $settings['trashed_posts_days'] ?? 30;
    // Database Optimization.
    $enable_expired_transients_cleanup = $settings['enable_expired_transients_cleanup'] ?? 0;
    $enable_orphaned_postmeta_cleanup = $settings['enable_orphaned_postmeta_cleanup'] ?? 0;
    $enable_orphaned_commentmeta_cleanup = $settings['enable_orphaned_commentmeta_cleanup'] ?? 0;
    $enable_orphaned_usermeta_cleanup = $settings['enable_orphaned_usermeta_cleanup'] ?? 0;
    $enable_orphaned_term_relationships_cleanup = $settings['enable_orphaned_term_relationships_cleanup'] ?? 0;
    echo '<div style="max-width: 1600px; margin-top: 2em;">';
    echo '<h2>⚙️ ' . esc_html__( 'Other Cleanup Settings', 'wuclean-database-cleaner-for-woocommerce' ) . '</h2>';
    // Info box.
    echo '<div style="max-width: 1600px; background: #e7f3ff; border: 1px solid #2196f3; border-radius: 4px; padding: 15px; margin: 20px 0;">';
    if ( pt_wuclean_fs()->is_not_paying() ) {
        echo '<p style="margin: 0;"><strong>ℹ️ ' . esc_html__( 'About Other Cleanups:', 'wuclean-database-cleaner-for-woocommerce' ) . '</strong> ' . esc_html__( 'This section handles cleanup of comments, post revisions, auto-drafts, orphaned database records, and expired transients, which helps reduce database size and improve site performance. Best practice is to only use this if you make regular backups. Automate daily cleanup with Wuclean Pro👑', 'wuclean-database-cleaner-for-woocommerce' ) . '</p>';
    }
    echo '</div>';
    echo '<form method="post" class="wuclean">';
    wp_nonce_field( 'pt_wuclean_save_other_settings', 'pt_wuclean_other_settings_nonce' );
    // Settings card.
    echo '<div style="background: #fff; border: 1px solid #ccd0d4; border-radius: 4px; padding: 20px; margin: 20px 0; max-width: 1600px;">';
    echo '<h3 style="margin: 0 0 8px 0;">📋 ' . esc_html__( 'Shared Scan Settings (Manual + Scheduled)', 'wuclean-database-cleaner-for-woocommerce' ) . '</h3>';
    echo '<table class="form-table"><tbody>';
    // Section Header: Comments.
    echo '<tr><td colspan="2"><h3 style="margin: 0 0 10px 0; padding-top: 0;">' . esc_html__( 'Comments', 'wuclean-database-cleaner-for-woocommerce' ) . '</h3></td></tr>';
    // Trash Spam Product Reviews.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Trash spam product reviews', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Move spam product reviews to trash after the specified number of days. Gives you time to review before permanent deletion.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_trash_spam_reviews" value="1"' . checked( $enable_trash_spam_reviews, 1, false ) . '> ';
    echo '<input type="number" name="trash_spam_reviews_days" min="1" max="365" value="' . esc_attr( $trash_spam_reviews_days ) . '" style="width: 70px;"> ' . esc_html__( 'days old', 'wuclean-database-cleaner-for-woocommerce' );
    echo '</td></tr>';
    // Delete Trashed Product Reviews.
    echo '<tr><th scope="row" style="width: 300px; padding-left: 20px;">' . esc_html__( '└─ Delete trashed product reviews', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Permanently delete product reviews that have been moved to trash.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_trashed_reviews_cleanup" value="1"' . checked( $enable_trashed_reviews_cleanup, 1, false ) . '>';
    echo '</td></tr>';
    // Trash Spam Comments.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Trash spam comments', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Move spam comments (excluding product reviews) to trash after the specified number of days.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_trash_spam_comments" value="1"' . checked( $enable_trash_spam_comments, 1, false ) . '> ';
    echo '<input type="number" name="trash_spam_comments_days" min="1" max="365" value="' . esc_attr( $trash_spam_comments_days ) . '" style="width: 70px;"> ' . esc_html__( 'days old', 'wuclean-database-cleaner-for-woocommerce' );
    echo '</td></tr>';
    // Delete Trashed Comments.
    echo '<tr><th scope="row" style="width: 300px; padding-left: 20px;">' . esc_html__( '└─ Delete trashed comments', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Permanently delete regular blog comments that have been moved to trash.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_trashed_comments_cleanup" value="1"' . checked( $enable_trashed_comments_cleanup, 1, false ) . '>';
    echo '</td></tr>';
    // Section Header: Posts & Pages.
    echo '<tr><td colspan="2"><h3 style="margin: 20px 0 10px 0; padding-top: 20px;">' . esc_html__( 'Posts & Pages', 'wuclean-database-cleaner-for-woocommerce' ) . '</h3></td></tr>';
    // Delete Post Revisions.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Delete post revisions', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Delete old post revisions while keeping the most recent ones. Helps reduce database bloat significantly.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_post_revisions_cleanup" value="1"' . checked( $enable_post_revisions_cleanup, 1, false ) . '> ';
    echo esc_html__( 'Keep last', 'wuclean-database-cleaner-for-woocommerce' ) . ' ';
    echo '<input type="number" name="post_revisions_keep_count" min="1" max="10" value="' . esc_attr( $post_revisions_keep_count ) . '" style="width: 60px;"> ';
    echo esc_html__( 'revisions per post, delete older than', 'wuclean-database-cleaner-for-woocommerce' ) . ' ';
    echo '<input type="number" name="post_revisions_max_age_days" min="30" max="3650" value="' . esc_attr( $post_revisions_max_age_days ) . '" style="width: 70px;"> ';
    echo esc_html__( 'days', 'wuclean-database-cleaner-for-woocommerce' );
    echo '</td></tr>';
    // Delete Orphaned Revisions.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Delete orphaned revisions', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Delete revisions whose parent post or page has been deleted. These orphaned revisions waste database space.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_orphaned_revisions_cleanup" value="1"' . checked( $enable_orphaned_revisions_cleanup ?? 0, 1, false ) . '>';
    echo '</td></tr>';
    // Delete Auto-Drafts.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Delete auto-drafts', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Delete auto-saved drafts older than the specified number of days.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_auto_drafts_cleanup" value="1"' . checked( $enable_auto_drafts_cleanup, 1, false ) . '> ';
    echo '<input type="number" name="auto_drafts_days" min="1" max="365" value="' . esc_attr( $auto_drafts_days ) . '" style="width: 70px;"> ' . esc_html__( 'days old', 'wuclean-database-cleaner-for-woocommerce' );
    echo '</td></tr>';
    // Delete Trashed Posts/Pages.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Delete trashed posts/pages', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Permanently delete posts and pages that have been in trash for the specified number of days.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_trashed_posts_cleanup" value="1"' . checked( $enable_trashed_posts_cleanup, 1, false ) . '> ';
    echo '<input type="number" name="trashed_posts_days" min="1" max="365" value="' . esc_attr( $trashed_posts_days ) . '" style="width: 70px;"> ' . esc_html__( 'days old', 'wuclean-database-cleaner-for-woocommerce' );
    echo '</td></tr>';
    // Section Header: Database Optimization.
    echo '<tr><td colspan="2"><h3 style="margin: 20px 0 10px 0; padding-top: 20px;">' . esc_html__( 'Database Optimization', 'wuclean-database-cleaner-for-woocommerce' ) . '</h3></td></tr>';
    // Delete Expired Transients.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Delete expired transients', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Delete expired transients and timeouts from the options table.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_expired_transients_cleanup" value="1"' . checked( $enable_expired_transients_cleanup, 1, false ) . '>';
    echo '</td></tr>';
    // Detect Orphaned Post Meta.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Detect orphaned post meta', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Detects orphaned postmeta for regular WordPress posts/pages (blog posts, custom post types). Excludes all WooCommerce/product/order meta which are handled separately. Results will be shown on the Orphaned Data tab for manual review and deletion.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_orphaned_postmeta_cleanup" value="1"' . checked( $enable_orphaned_postmeta_cleanup, 1, false ) . '>';
    echo '</td></tr>';
    // Detect Orphaned Comment Meta.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Detect orphaned comment meta', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Detects metadata for comments that no longer exist. Results will be shown on the Orphaned Data tab for manual review and deletion.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_orphaned_commentmeta_cleanup" value="1"' . checked( $enable_orphaned_commentmeta_cleanup, 1, false ) . '>';
    echo '</td></tr>';
    // Detect Orphaned User Meta.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Detect orphaned user meta', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Detects orphaned usermeta for non-customer users (subscribers, authors, etc.). Excludes all WooCommerce customer meta which are handled by Customer orphaned data detection. Results will be shown on the Orphaned Data tab for manual review and deletion.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_orphaned_usermeta_cleanup" value="1"' . checked( $enable_orphaned_usermeta_cleanup, 1, false ) . '>';
    echo '</td></tr>';
    // Detect Orphaned Term Relationships.
    echo '<tr><th scope="row" style="width: 300px;">' . esc_html__( 'Detect orphaned term relationships', 'wuclean-database-cleaner-for-woocommerce' ) . ' <span class="pt-wuclean-tooltip">?<span class="pt-wuclean-tooltip-text">' . esc_html__( 'Detects orphaned term relationships for regular WordPress posts/pages (blog categories, tags). Excludes all product taxonomies which are handled separately. Results will be shown on the Orphaned Data tab for manual review and deletion.', 'wuclean-database-cleaner-for-woocommerce' ) . '</span></span></th><td>';
    echo '<input type="checkbox" name="enable_orphaned_term_relationships_cleanup" value="1"' . checked( $enable_orphaned_term_relationships_cleanup, 1, false ) . '>';
    echo '</td></tr>';
    echo '</tbody></table>';
    echo '<p style="margin-bottom: 0;"><input type="submit" class="button button-primary" value="' . esc_attr__( 'Save Settings', 'wuclean-database-cleaner-for-woocommerce' ) . '"></p>';
    echo '</div>';
    // Close settings card.
    echo '</form>';
    // Manual Cleanup Section.
    echo '<div style="background: #fff; border: 1px solid #ccd0d4; border-radius: 4px; padding: 20px; margin: 20px 0; max-width: 1600px;">';
    echo '<h3 style="margin-top: 0;">🔍 ' . esc_html__( 'Manual Cleanup', 'wuclean-database-cleaner-for-woocommerce' ) . '</h3>';
    echo '<p>' . esc_html__( 'Run the cleanup immediately based on your settings saved above. No email notification will be sent.', 'wuclean-database-cleaner-for-woocommerce' ) . '</p>';
    echo '<button type="button" id="pt-run-other-cleanup" class="button button-primary" style="background: #2196f3; border-color: #2196f3;">▶️ ' . esc_html__( 'Run Cleanup Now', 'wuclean-database-cleaner-for-woocommerce' ) . '</button>';
    echo '<div id="pt-other-cleanup-result" style="display:none; margin-top:15px;"></div>';
    echo '</div>';
    // Close manual cleanup card.
    // Free version: progressive disclosure for automation benefits (Settings tab only).
    $pro_benefits = array(__( 'Schedule automatic cleanup on a daily basis', 'wuclean-database-cleaner-for-woocommerce' ), __( 'Receive email notifications of cleanup actions, detected orphaned data and counts', 'wuclean-database-cleaner-for-woocommerce' ), __( 'Delete selected orphan data easily', 'wuclean-database-cleaner-for-woocommerce' ));
    pt_wuclean_render_pro_automation_callout( $pro_benefits, 'other-settings' );
    echo '</div>';
}

/**
 * Render Orphaned Data Tab (Other module).
 */
function pt_wuclean_render_other_orphaned_data_tab() {
    if ( !current_user_can( pt_wuclean_get_required_capability() ) ) {
        wp_die( esc_html__( 'You do not have sufficient permissions to access this page.', 'wuclean-database-cleaner-for-woocommerce' ) );
    }
    $tab_nonce = wp_create_nonce( 'wuclean-other-tab' );
    // Validate tab nonce before trusting any GET-based filters for this view.
    $tab_nonce_valid = isset( $_GET['_wpnonce'] ) && wp_verify_nonce( sanitize_text_field( wp_unslash( $_GET['_wpnonce'] ) ), 'wuclean-other-tab' );
    // Get cached orphaned data.
    $orphaned_data = pt_wuclean_other_get_orphaned_data();
    echo '<div style="max-width: 1600px; margin-top: 2em;">';
    echo '<h2>🗑️ ' . esc_html__( 'Orphaned Data', 'wuclean-database-cleaner-for-woocommerce' ) . '</h2>';
    // Info box.
    echo '<div style="background: #e7f3ff; border: 1px solid #2196f3; border-radius: 4px; padding: 15px; margin: 20px 0;">';
    echo '<p style="margin: 0;"><strong>ℹ️ ' . esc_html__( 'About Orphaned Data:', 'wuclean-database-cleaner-for-woocommerce' ) . '</strong> ';
    if ( pt_wuclean_fs()->is_not_paying() ) {
        echo esc_html__( 'This tab shows orphaned database records from regular WordPress content (posts, comments, users, terms). Use your favorite database manager to clean selected items or consider upgrading to Wuclean Pro👑', 'wuclean-database-cleaner-for-woocommerce' );
    }
    echo '</p></div>';
    // Empty state.
    if ( empty( $orphaned_data ) ) {
        echo '<div style="background: #fff; border: 1px solid #ccd0d4; border-radius: 4px; padding: 20px; margin: 20px 0; text-align: center;">';
        echo '<p style="margin: 0; color: #646970;">' . esc_html__( 'No orphaned data found.', 'wuclean-database-cleaner-for-woocommerce' ) . '</p>';
        echo '<p style="margin: 10px 0 0 0; color: #646970; font-size: 13px;">' . esc_html__( 'Enable orphaned data detection in Settings to scan for orphaned database records.', 'wuclean-database-cleaner-for-woocommerce' ) . '</p>';
        echo '</div>';
        echo '</div>';
        return;
    }
    // Flatten all orphaned data into single array with type info.
    $all_items = array();
    foreach ( $orphaned_data as $type => $items ) {
        if ( is_array( $items ) ) {
            $all_items = array_merge( $all_items, $items );
        }
    }
    // Search handling.
    $search_query = '';
    if ( $tab_nonce_valid && isset( $_GET['orphaned_search'] ) ) {
        $search_query = sanitize_text_field( wp_unslash( $_GET['orphaned_search'] ) );
    }
    $filtered_items = $all_items;
    if ( !empty( $search_query ) ) {
        $filtered_items = array_filter( $all_items, function ( $item ) use($search_query) {
            $searchable = implode( ' ', array(
                $item['type'] ?? '',
                $item['source'] ?? '',
                $item['meta_key'] ?? '',
                $item['details'] ?? ''
            ) );
            return stripos( $searchable, $search_query ) !== false;
        } );
    }
    // Search form.
    echo '<form method="get" action="' . esc_url( admin_url( 'admin.php' ) ) . '" style="margin-bottom: 20px;">';
    echo '<input type="hidden" name="page" value="wuclean-other">';
    echo '<input type="hidden" name="tab" value="orphaned_data">';
    echo '<input type="hidden" name="_wpnonce" value="' . esc_attr( $tab_nonce ) . '">';
    echo '<input type="text" name="orphaned_search" value="' . esc_attr( $search_query ) . '" placeholder="' . esc_attr__( 'Search by type, source, meta key...', 'wuclean-database-cleaner-for-woocommerce' ) . '" style="width: 400px; padding: 5px; height: 30px; box-sizing: border-box;">';
    echo '<input type="submit" value="' . esc_attr__( 'Search', 'wuclean-database-cleaner-for-woocommerce' ) . '" class="button">';
    if ( !empty( $search_query ) ) {
        echo '<a href="' . esc_url( add_query_arg( array(
            'page'     => 'wuclean-other',
            'tab'      => 'orphaned_data',
            '_wpnonce' => $tab_nonce,
        ), admin_url( 'admin.php' ) ) ) . '" class="button">' . esc_html__( 'Clear Search', 'wuclean-database-cleaner-for-woocommerce' ) . '</a>';
    }
    echo '</form>';
    if ( empty( $filtered_items ) ) {
        echo '<div style="background: #fff; border: 1px solid #ccd0d4; border-radius: 4px; padding: 20px; margin: 20px 0; text-align: center;">';
        if ( !empty( $search_query ) ) {
            echo '<p style="margin: 0; color: #646970;">' . esc_html__( 'No items match your search criteria.', 'wuclean-database-cleaner-for-woocommerce' ) . '</p>';
            echo '<br><a href="' . esc_url( add_query_arg( array(
                'page'     => 'wuclean-other',
                'tab'      => 'orphaned_data',
                '_wpnonce' => $tab_nonce,
            ), admin_url( 'admin.php' ) ) ) . '" class="button">' . esc_html__( 'Clear Search', 'wuclean-database-cleaner-for-woocommerce' ) . '</a>';
        } else {
            echo '<p style="margin: 0; color: #646970;">' . esc_html__( 'No orphaned data found.', 'wuclean-database-cleaner-for-woocommerce' ) . '</p>';
        }
        echo '</div>';
        echo '</div>';
        return;
    }
    // Pagination.
    $per_page = 25;
    $current_page = 1;
    if ( $tab_nonce_valid && isset( $_GET['paged'] ) ) {
        $current_page = max( 1, intval( $_GET['paged'] ) );
    }
    $total_items = count( $filtered_items );
    $total_pages = ceil( $total_items / $per_page );
    $offset = ($current_page - 1) * $per_page;
    $paginated_items = array_slice( $filtered_items, $offset, $per_page );
    // Pagination info.
    echo '<div style="margin-bottom: 10px;">';
    $start = $offset + 1;
    $end = min( $offset + $per_page, $total_items );
    if ( !empty( $search_query ) ) {
        /* translators: %1$d: start number, %2$d: end number, %3$d: total count. */
        $showing_filtered = __( 'Showing %1$d-%2$d of %3$d items (filtered by search)', 'wuclean-database-cleaner-for-woocommerce' );
        echo '<span>' . esc_html( sprintf(
            $showing_filtered,
            $start,
            $end,
            $total_items
        ) ) . '</span>';
    } else {
        /* translators: %1$d: start number, %2$d: end number, %3$d: total count. */
        $showing_all = __( 'Showing %1$d-%2$d of %3$d items', 'wuclean-database-cleaner-for-woocommerce' );
        echo '<span>' . esc_html( sprintf(
            $showing_all,
            $start,
            $end,
            $total_items
        ) ) . '</span>';
    }
    echo '</div>';
    // Bulk action form.
    echo '<form method="post">';
    wp_nonce_field( 'pt_wuclean_orphaned_data_action', 'pt_wuclean_orphaned_data_nonce' );
    // Bulk action buttons.
    echo '<div style="margin-bottom: 15px; display: flex; gap: 10px; align-items: center; flex-wrap: wrap;">';
    echo '</div>';
    // Table.
    echo '<div style="background: #fff; border: 1px solid #ccd0d4; border-radius: 4px; padding: 20px; margin: 20px 0;">';
    echo '<table class="wp-list-table widefat fixed striped">';
    echo '<thead><tr>';
    echo '<th style="width: 200px;">' . esc_html__( 'Type', 'wuclean-database-cleaner-for-woocommerce' ) . '</th>';
    echo '<th style="width: 140px;">' . esc_html__( 'Source', 'wuclean-database-cleaner-for-woocommerce' ) . '</th>';
    echo '<th style="width: 200px;">' . esc_html__( 'Meta Key', 'wuclean-database-cleaner-for-woocommerce' ) . '</th>';
    echo '<th>' . esc_html__( 'Details', 'wuclean-database-cleaner-for-woocommerce' ) . '</th>';
    echo '</tr></thead><tbody>';
    foreach ( $paginated_items as $item ) {
        if ( !is_array( $item ) ) {
            continue;
            // Safety: skip malformed entries.
        }
        $id = ( isset( $item['id'] ) ? $item['id'] : '' );
        $type = ( isset( $item['type'] ) ? $item['type'] : '' );
        $source = ( isset( $item['source'] ) ? $item['source'] : '' );
        $meta_key = ( isset( $item['meta_key'] ) ? $item['meta_key'] : (( isset( $item['taxonomy'] ) ? $item['taxonomy'] : 'N/A' )) );
        $details = ( isset( $item['details'] ) ? $item['details'] : '' );
        echo '<tr>';
        echo '<td>' . esc_html( $type ) . '</td>';
        echo '<td>' . esc_html( $source ) . '</td>';
        echo '<td><code>' . esc_html( $meta_key ) . '</code></td>';
        echo '<td>' . esc_html( $details ) . '</td>';
        echo '</tr>';
    }
    echo '</tbody></table>';
    echo '</div>';
    echo '</form>';
    // Pagination.
    $preserve_params = array(
        'page'     => 'wuclean-other',
        'tab'      => 'orphaned_data',
        '_wpnonce' => wp_create_nonce( 'wuclean-other-tab' ),
    );
    if ( !empty( $search_query ) ) {
        $preserve_params['orphaned_search'] = $search_query;
    }
    pt_wuclean_render_pagination( array(
        'current_page'    => $current_page,
        'total_pages'     => $total_pages,
        'base_url'        => admin_url( 'admin.php' ),
        'page_param'      => 'paged',
        'preserve_params' => $preserve_params,
    ) );
    echo '</div>';
}

/**
 * Detect orphaned post meta (for manual review)
 * Returns array of orphaned items with details
 *
 * Smart filtering approach:
 * - Includes ALL non-product WordPress orphaned meta
 * - Includes LONE product-like orphaned meta (single meta per post_id)
 * - Excludes product orphans with 2+ meta (handled by products.php intelligent scoring)
 * - This creates a safety net for edge cases that fall below products.php threshold
 */
function pt_wuclean_detect_orphaned_postmeta() {
    global $wpdb;
    $limit = 500;
    // Detect up to 500 items.
    // Step 1: Get ALL orphaned postmeta (no exclusions yet).
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $all_orphaned = $wpdb->get_results( $wpdb->prepare( "SELECT pm.meta_id, pm.post_id, pm.meta_key, pm.meta_value\n         FROM {$wpdb->postmeta} pm\n         LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id\n         WHERE p.ID IS NULL\n         LIMIT %d", $limit ) );
    // Step 2: Group by post_id.
    $orphaned_by_post = array();
    foreach ( $all_orphaned as $meta ) {
        if ( !isset( $orphaned_by_post[$meta->post_id] ) ) {
            $orphaned_by_post[$meta->post_id] = array();
        }
        $orphaned_by_post[$meta->post_id][] = $meta;
    }
    // Step 3: Define product-like patterns (matches products.php detection logic).
    // Use exact keys for strong indicators.
    $strong_product_keys = array(
        '_price',
        '_regular_price',
        '_sale_price',
        '_stock',
        '_stock_status',
        '_manage_stock',
        '_backorders',
        '_sku',
        '_weight',
        '_length',
        '_width',
        '_height',
        '_downloadable',
        '_virtual',
        '_tax_status',
        '_featured',
        '_catalog_visibility',
        '_sold_individually',
        '_purchase_note',
        '_upsell_ids',
        '_crosssell_ids',
        '_product_attributes',
        '_product_image_gallery',
        '_product_type',
        '_product_visibility',
        '_product_version',
        // WooCommerce version tracking (set on ~99% of products).
        'total_sales',
    );
    // Use prefixes for pattern matching.
    $product_prefixes = array('_product_', '_wc_', '_order_');
    // Step 4: Filter and build results.
    $results = array();
    foreach ( $orphaned_by_post as $post_id => $meta_entries ) {
        $product_meta_count = 0;
        // Count how many product-like meta this post has.
        foreach ( $meta_entries as $meta ) {
            $is_product_like = false;
            // Check exact match with strong indicators.
            if ( in_array( $meta->meta_key, $strong_product_keys, true ) ) {
                $is_product_like = true;
            } else {
                // Check prefix match.
                foreach ( $product_prefixes as $prefix ) {
                    if ( strpos( $meta->meta_key, $prefix ) === 0 ) {
                        $is_product_like = true;
                        break;
                    }
                }
            }
            if ( $is_product_like ) {
                ++$product_meta_count;
            }
        }
        // Include this post's orphaned meta if:
        // - It has NO product-like meta (regular WordPress orphan), OR
        // - It has exactly 1 product-like meta (lone orphan below products.php threshold).
        // Skip if 2+ product meta (products.php intelligent scoring will handle it).
        if ( $product_meta_count <= 1 ) {
            foreach ( $meta_entries as $meta ) {
                $value_preview = ( strlen( $meta->meta_value ) > 50 ? substr( $meta->meta_value, 0, 50 ) . '...' : $meta->meta_value );
                // Add note for lone product orphans.
                $type_label = 'Orphaned Post Meta';
                if ( 1 === $product_meta_count ) {
                    // Check if this specific meta is the product-like one.
                    $is_this_product_like = in_array( $meta->meta_key, $strong_product_keys, true );
                    if ( !$is_this_product_like ) {
                        foreach ( $product_prefixes as $prefix ) {
                            if ( strpos( $meta->meta_key, $prefix ) === 0 ) {
                                $is_this_product_like = true;
                                break;
                            }
                        }
                    }
                    if ( $is_this_product_like ) {
                        $type_label = 'Orphaned Post Meta (Lone Product Meta)';
                    }
                }
                $results[] = array(
                    'id'         => 'postmeta:' . $meta->meta_id,
                    'type'       => $type_label,
                    'source'     => 'Post ID: ' . $meta->post_id,
                    'meta_key'   => $meta->meta_key,
                    'meta_value' => $value_preview,
                    'details'    => sprintf( 'Meta key: %s | Post ID: %d (deleted)', $meta->meta_key, $meta->post_id ),
                );
            }
        }
        // Skip posts with 2+ product-like meta (products.php territory).
    }
    return $results;
}

/**
 * Detect orphaned comment meta (for manual review)
 */
function pt_wuclean_detect_orphaned_commentmeta() {
    global $wpdb;
    $limit = 500;
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $orphaned_items = $wpdb->get_results( $wpdb->prepare( "SELECT cm.meta_id, cm.comment_id, cm.meta_key, cm.meta_value\n         FROM {$wpdb->commentmeta} cm\n         LEFT JOIN {$wpdb->comments} c ON c.comment_ID = cm.comment_id\n         WHERE c.comment_ID IS NULL\n         LIMIT %d", $limit ) );
    $results = array();
    foreach ( $orphaned_items as $item ) {
        $value_preview = ( strlen( $item->meta_value ) > 50 ? substr( $item->meta_value, 0, 50 ) . '...' : $item->meta_value );
        $results[] = array(
            'id'         => 'commentmeta:' . $item->meta_id,
            'type'       => 'Orphaned Comment Meta',
            'source'     => 'Comment ID: ' . $item->comment_id,
            'meta_key'   => $item->meta_key,
            'meta_value' => $value_preview,
            'details'    => sprintf( 'Meta key: %s | Comment ID: %d (deleted)', $item->meta_key, $item->comment_id ),
        );
    }
    return $results;
}

/**
 * Detect orphaned user meta (for manual review)
 * Excludes WooCommerce customer meta
 */
function pt_wuclean_detect_orphaned_usermeta() {
    global $wpdb;
    $limit = 500;
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $orphaned_items = $wpdb->get_results( $wpdb->prepare(
        "SELECT um.umeta_id, um.user_id, um.meta_key, um.meta_value\n         FROM {$wpdb->usermeta} um\n         LEFT JOIN {$wpdb->users} u ON u.ID = um.user_id\n         WHERE u.ID IS NULL\n         AND um.meta_key NOT LIKE %s\n         AND um.meta_key NOT LIKE %s\n         AND um.meta_key NOT LIKE %s\n         AND um.meta_key NOT LIKE %s\n         AND um.meta_key NOT LIKE %s\n         AND um.meta_key NOT LIKE %s\n         AND um.meta_key NOT LIKE %s\n         LIMIT %d",
        '_billing_%',
        '_shipping_%',
        'paying_customer',
        'last_update',
        '_money_spent',
        '_order_count',
        '_woocommerce_%',
        $limit
    ) );
    $results = array();
    foreach ( $orphaned_items as $item ) {
        $value_preview = ( strlen( $item->meta_value ) > 50 ? substr( $item->meta_value, 0, 50 ) . '...' : $item->meta_value );
        $results[] = array(
            'id'         => 'usermeta:' . $item->umeta_id,
            'type'       => 'Orphaned User Meta',
            'source'     => 'User ID: ' . $item->user_id,
            'meta_key'   => $item->meta_key,
            'meta_value' => $value_preview,
            'details'    => sprintf( 'Meta key: %s | User ID: %d (deleted)', $item->meta_key, $item->user_id ),
        );
    }
    return $results;
}

/**
 * Detect orphaned term relationships (for manual review)
 */
function pt_wuclean_detect_orphaned_term_relationships() {
    global $wpdb;
    $limit = 500;
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $orphaned_items = $wpdb->get_results( $wpdb->prepare(
        "SELECT tr.object_id, tr.term_taxonomy_id, tt.term_id, tt.taxonomy\n         FROM {$wpdb->term_relationships} tr\n         LEFT JOIN {$wpdb->posts} p ON p.ID = tr.object_id\n         LEFT JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id\n         WHERE p.ID IS NULL\n         AND tt.taxonomy NOT LIKE %s\n         AND tt.taxonomy NOT LIKE %s\n         LIMIT %d",
        'product%',
        'pa_%',
        $limit
    ) );
    $results = array();
    foreach ( $orphaned_items as $item ) {
        $results[] = array(
            'id'       => 'term_rel:' . $item->object_id . ':' . $item->term_taxonomy_id,
            'type'     => 'Orphaned Term Relationship',
            'source'   => 'Post ID: ' . $item->object_id,
            'taxonomy' => $item->taxonomy,
            'term_id'  => $item->term_id,
            'details'  => sprintf(
                'Taxonomy: %s | Post ID: %d (deleted) | Term ID: %d',
                $item->taxonomy,
                $item->object_id,
                $item->term_id
            ),
        );
    }
    return $results;
}

/**
 * Clean up trashed product reviews
 */
function pt_wuclean_cleanup_trashed_reviews() {
    global $wpdb;
    // Delete trashed product reviews in batches.
    $batch_size = 100;
    $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->comments}\n         WHERE comment_type = 'review'\n         AND comment_approved = 'trash'\n         LIMIT %d", $batch_size ) );
    return $deleted;
}

/**
 * Clean up trashed regular comments
 */
function pt_wuclean_cleanup_trashed_comments() {
    global $wpdb;
    // Delete trashed comments (not reviews) in batches.
    $batch_size = 100;
    $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->comments} \n         WHERE comment_type = 'comment' \n         AND comment_approved = 'trash'\n         LIMIT %d", $batch_size ) );
    return $deleted;
}

/**
 * Trash spam product reviews (move to trash, not delete).
 *
 * @param int $days Age in days after which to trash spam reviews. Default 30.
 * @return int|false Number of rows affected or false on failure.
 */
function pt_wuclean_trash_spam_reviews(  $days = 30  ) {
    global $wpdb;
    // Move spam product reviews to trash after X days.
    $batch_size = 100;
    $cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-' . $days . ' days' ) );
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $trashed = $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->comments}\n         SET comment_approved = 'trash'\n         WHERE comment_approved = 'spam'\n         AND comment_type = 'review'\n         AND comment_date < %s\n         LIMIT %d", $cutoff, $batch_size ) );
    return $trashed;
}

/**
 * Trash spam comments (excluding reviews, move to trash, not delete).
 *
 * @param int $days Age in days after which to trash spam comments. Default 30.
 * @return int|false Number of rows affected or false on failure.
 */
function pt_wuclean_trash_spam_comments(  $days = 30  ) {
    global $wpdb;
    // Move spam comments (NOT reviews) to trash after X days.
    $batch_size = 100;
    $cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-' . $days . ' days' ) );
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $trashed = $wpdb->query( $wpdb->prepare( "UPDATE {$wpdb->comments} \n         SET comment_approved = 'trash'\n         WHERE comment_approved = 'spam'\n         AND comment_type != 'review'\n         AND comment_date < %s\n         LIMIT %d", $cutoff, $batch_size ) );
    return $trashed;
}

/**
 * Clean up post revisions.
 *
 * @param int $keep_count   Number of revisions to keep per post. Default 3.
 * @param int $max_age_days Max age in days; revisions older are eligible. Default 365.
 * @return int Total number of revisions deleted.
 */
function pt_wuclean_cleanup_post_revisions(  $keep_count = 3, $max_age_days = 365  ) {
    global $wpdb;
    $total_deleted = 0;
    $batch_size = 50;
    // Process 50 posts at a time.
    $max_age_cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-' . $max_age_days . ' days' ) );
    // Get posts that have revisions (in batches).
    // Only include revisions where the parent post/page still exists.
    $posts_with_revisions = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT r.post_parent \n         FROM {$wpdb->posts} r\n         INNER JOIN {$wpdb->posts} p ON p.ID = r.post_parent\n         WHERE r.post_type = 'revision' \n         AND r.post_parent > 0\n         AND p.post_type IN ('post', 'page')\n         ORDER BY r.post_parent ASC\n         LIMIT %d", $batch_size ) );
    foreach ( $posts_with_revisions as $post_id ) {
        // Get all revisions for this post, ordered by date (newest first).
        $revisions = $wpdb->get_results( $wpdb->prepare( "SELECT ID, post_modified \n             FROM {$wpdb->posts} \n             WHERE post_type = 'revision' \n             AND post_parent = %d \n             ORDER BY post_modified DESC", $post_id ) );
        // Track how many revisions we've kept (that aren't too old).
        $kept_count = 0;
        foreach ( $revisions as $revision ) {
            // First check: Delete if older than max age (regardless of count).
            if ( $revision->post_modified < $max_age_cutoff ) {
                $wpdb->delete( $wpdb->posts, array(
                    'ID' => $revision->ID,
                ) );
                ++$total_deleted;
                continue;
                // Skip to next revision, don't count this one.
            }
            // Revision is not too old, check if we should keep it.
            ++$kept_count;
            // Second check: Delete if we've already kept enough revisions.
            if ( $kept_count > $keep_count ) {
                $wpdb->delete( $wpdb->posts, array(
                    'ID' => $revision->ID,
                ) );
                ++$total_deleted;
            }
            // Otherwise, keep this revision (it's within keep_count and not too old).
        }
    }
    return $total_deleted;
}

/**
 * Clean up orphaned revisions (revisions whose parent post/page no longer exists)
 */
function pt_wuclean_cleanup_orphaned_revisions() {
    global $wpdb;
    $batch_size = 100;
    // Find revisions where the parent post no longer exists.
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $orphaned_revision_ids = $wpdb->get_col( $wpdb->prepare( "SELECT r.ID \n         FROM {$wpdb->posts} r\n         LEFT JOIN {$wpdb->posts} p ON p.ID = r.post_parent\n         WHERE r.post_type = 'revision' \n         AND r.post_parent > 0\n         AND p.ID IS NULL\n         LIMIT %d", $batch_size ) );
    $deleted = 0;
    if ( !empty( $orphaned_revision_ids ) ) {
        $placeholders = implode( ', ', array_fill( 0, count( $orphaned_revision_ids ), '%d' ) );
        // PCP-compliant because prepare() is *directly* inside query().
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders from absint IDs.
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $placeholders is %d,%d; $wpdb->posts; variadic $orphaned_revision_ids.
        $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->posts} WHERE ID IN ({$placeholders})", ...$orphaned_revision_ids ) );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
    }
    return $deleted;
}

/**
 * Clean up auto-drafts.
 *
 * @param int $days Age in days after which to delete auto-drafts. Default 7.
 * @return int|false Number of rows affected or false on failure.
 */
function pt_wuclean_cleanup_auto_drafts(  $days = 7  ) {
    global $wpdb;
    // Delete auto-drafts older than X days in batches.
    $batch_size = 100;
    $cutoff = gmdate( 'Y-m-d H:i:s', strtotime( '-' . $days . ' days' ) );
    $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->posts}\n         WHERE post_status = 'auto-draft'\n         AND post_modified < %s\n         LIMIT %d", $cutoff, $batch_size ) );
    return $deleted;
}

/**
 * Clean up trashed posts and pages.
 *
 * @param int $days Age in days after which to permanently delete trashed posts/pages. Default 30.
 * @return int Number of posts/pages deleted.
 */
function pt_wuclean_cleanup_trashed_posts(  $days = 30  ) {
    global $wpdb;
    $batch_size = 100;
    $cutoff_time = time() - $days * 24 * 3600;
    // Find trashed posts/pages using _wp_trash_meta_time for reliable trash date.
    // WordPress automatically sets this meta when ANY content is trashed.
    // Using post_modified is unreliable as it can be updated by plugins/auto-save.
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $trashed_post_ids = $wpdb->get_col( $wpdb->prepare( "SELECT p.ID \n         FROM {$wpdb->posts} p\n         INNER JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id\n         WHERE p.post_status = 'trash'\n         AND p.post_type IN ('post', 'page')\n         AND pm.meta_key = '_wp_trash_meta_time'\n         AND pm.meta_value <= %d\n         LIMIT %d", $cutoff_time, $batch_size ) );
    $deleted = 0;
    if ( !empty( $trashed_post_ids ) ) {
        $placeholders = implode( ', ', array_fill( 0, count( $trashed_post_ids ), '%d' ) );
        // PCP-compliant because prepare() is *directly* inside query().
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders from absint IDs.
        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- $placeholders is %d,%d; $wpdb->posts; variadic $trashed_post_ids.
        $deleted = $wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->posts} WHERE ID IN ({$placeholders})", ...$trashed_post_ids ) );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
    }
    return $deleted;
}

/**
 * Clean up expired transients and timeouts.
 */
function pt_wuclean_cleanup_expired_transients() {
    global $wpdb;
    $batch_size = 1000;
    $max_batches = 5;
    $total_transients = 0;
    $total_timeouts = 0;
    for ($i = 0; $i < $max_batches; $i++) {
        // Delete expired transient timeouts first.
        $deleted_timeouts = $wpdb->query( $wpdb->prepare(
            "DELETE FROM {$wpdb->options} \n         WHERE option_name LIKE %s \n         AND option_value < %d\n         LIMIT %d",
            '_transient_timeout_%',
            time(),
            $batch_size
        ) );
        // Delete corresponding transients (orphaned ones).
        // Use subquery workaround for MySQL limitation.
        $deleted_transients = $wpdb->query( $wpdb->prepare(
            "DELETE FROM {$wpdb->options} \n         WHERE option_name LIKE %s \n         AND option_name NOT LIKE %s\n         AND option_name NOT IN (\n             SELECT transient_name FROM (\n                 SELECT REPLACE(option_name, '_transient_timeout_', '_transient_') AS transient_name\n                 FROM {$wpdb->options} \n                 WHERE option_name LIKE %s\n             ) AS temp_table\n         )\n         LIMIT %d",
            '_transient_%',
            '_transient_timeout_%',
            '_transient_timeout_%',
            $batch_size
        ) );
        $total_timeouts += (int) $deleted_timeouts;
        $total_transients += (int) $deleted_transients;
        // If neither query hit the batch limit, there is nothing more to delete.
        if ( $deleted_timeouts < $batch_size && $deleted_transients < $batch_size ) {
            break;
        }
    }
    return array(
        'transients' => $total_transients,
        'timeouts'   => $total_timeouts,
    );
}

/**
 * AJAX handler for manual other cleanup
 */
function pt_wuclean_ajax_run_other_cleanup() {
    // Check nonce.
    if ( !isset( $_POST['nonce'] ) || !wp_verify_nonce( sanitize_text_field( wp_unslash( $_POST['nonce'] ) ), 'pt_wuclean_other_cleanup' ) ) {
        wp_send_json_error( __( 'Invalid nonce', 'wuclean-database-cleaner-for-woocommerce' ) );
        return;
    }
    // Check permissions.
    if ( !current_user_can( pt_wuclean_get_required_capability() ) ) {
        wp_send_json_error( __( 'Insufficient permissions', 'wuclean-database-cleaner-for-woocommerce' ) );
        return;
    }
    // Get settings.
    $settings = get_option( 'pt_wuclean_settings', array() );
    $cleanup_results = array();
    // Comments Cleanup.
    if ( !empty( $settings['enable_trash_spam_reviews'] ) ) {
        $spam_reviews_days = intval( $settings['trash_spam_reviews_days'] ?? 30 );
        $cleanup_results['trash_spam_reviews'] = pt_wuclean_trash_spam_reviews( $spam_reviews_days );
    }
    if ( !empty( $settings['enable_trashed_reviews_cleanup'] ) ) {
        $cleanup_results['trashed_reviews'] = pt_wuclean_cleanup_trashed_reviews();
    }
    if ( !empty( $settings['enable_trash_spam_comments'] ) ) {
        $spam_comments_days = intval( $settings['trash_spam_comments_days'] ?? 30 );
        $cleanup_results['trash_spam_comments'] = pt_wuclean_trash_spam_comments( $spam_comments_days );
    }
    if ( !empty( $settings['enable_trashed_comments_cleanup'] ) ) {
        $cleanup_results['trashed_comments'] = pt_wuclean_cleanup_trashed_comments();
    }
    // Posts & Pages Cleanup.
    if ( !empty( $settings['enable_post_revisions_cleanup'] ) ) {
        $keep_count = intval( $settings['post_revisions_keep_count'] ?? 3 );
        $max_age = intval( $settings['post_revisions_max_age_days'] ?? 365 );
        $cleanup_results['post_revisions'] = pt_wuclean_cleanup_post_revisions( $keep_count, $max_age );
    }
    if ( !empty( $settings['enable_orphaned_revisions_cleanup'] ) ) {
        $cleanup_results['orphaned_revisions'] = pt_wuclean_cleanup_orphaned_revisions();
    }
    if ( !empty( $settings['enable_auto_drafts_cleanup'] ) ) {
        $auto_drafts_days = intval( $settings['auto_drafts_days'] ?? 7 );
        $cleanup_results['auto_drafts'] = pt_wuclean_cleanup_auto_drafts( $auto_drafts_days );
    }
    if ( !empty( $settings['enable_trashed_posts_cleanup'] ) ) {
        $trashed_posts_days = intval( $settings['trashed_posts_days'] ?? 30 );
        $cleanup_results['trashed_posts'] = pt_wuclean_cleanup_trashed_posts( $trashed_posts_days );
    }
    // Database Optimization.
    if ( !empty( $settings['enable_expired_transients_cleanup'] ) ) {
        $transients_result = pt_wuclean_cleanup_expired_transients();
        if ( is_array( $transients_result ) ) {
            $cleanup_results['transients'] = $transients_result['transients'] ?? 0;
            $cleanup_results['timeouts'] = $transients_result['timeouts'] ?? 0;
        }
    }
    // Orphaned Data Detection (stores results for manual review).
    $orphaned_data_found = array();
    if ( !empty( $settings['enable_orphaned_postmeta_cleanup'] ) ) {
        $orphaned_data_found['postmeta'] = pt_wuclean_detect_orphaned_postmeta();
    }
    if ( !empty( $settings['enable_orphaned_commentmeta_cleanup'] ) ) {
        $orphaned_data_found['commentmeta'] = pt_wuclean_detect_orphaned_commentmeta();
    }
    if ( !empty( $settings['enable_orphaned_usermeta_cleanup'] ) ) {
        $orphaned_data_found['usermeta'] = pt_wuclean_detect_orphaned_usermeta();
    }
    if ( !empty( $settings['enable_orphaned_term_relationships_cleanup'] ) ) {
        $orphaned_data_found['term_relationships'] = pt_wuclean_detect_orphaned_term_relationships();
    }
    // Store orphaned data results.
    if ( !empty( $orphaned_data_found ) && array_filter( $orphaned_data_found ) ) {
        pt_wuclean_other_store_orphaned_data( $orphaned_data_found );
        // Count total items for display.
        $total_orphaned = 0;
        foreach ( $orphaned_data_found as $type => $items ) {
            $total_orphaned += ( is_array( $items ) ? count( $items ) : 0 );
        }
        $cleanup_results['orphaned_data_detected'] = $total_orphaned;
    } else {
        pt_wuclean_other_delete_orphaned_data();
    }
    // Build summary message.
    $message = '<div class="notice notice-success" style="padding: 10px; background: #fff; border-left: 4px solid #46b450;">';
    $message .= '<strong>' . __( 'Cleanup completed successfully!', 'wuclean-database-cleaner-for-woocommerce' ) . '</strong><br><br>';
    // Check if anything was deleted.
    $has_deletions = false;
    foreach ( $cleanup_results as $count ) {
        if ( $count > 0 ) {
            $has_deletions = true;
            break;
        }
    }
    if ( $has_deletions ) {
        $message .= __( 'Cleaned:', 'wuclean-database-cleaner-for-woocommerce' ) . '<br>';
        // Comments.
        if ( !empty( $cleanup_results['trash_spam_reviews'] ) ) {
            // translators: %d: number of spam reviews moved to trash.
            $message .= '• ' . sprintf( __( '%d spam product reviews moved to trash', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['trash_spam_reviews'] ) . '<br>';
        }
        if ( !empty( $cleanup_results['trashed_reviews'] ) ) {
            // translators: %d: number of trashed reviews deleted.
            $message .= '• ' . sprintf( __( '%d trashed product reviews deleted', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['trashed_reviews'] ) . '<br>';
        }
        if ( !empty( $cleanup_results['trash_spam_comments'] ) ) {
            // translators: %d: number of spam comments moved to trash.
            $message .= '• ' . sprintf( __( '%d spam comments moved to trash', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['trash_spam_comments'] ) . '<br>';
        }
        if ( !empty( $cleanup_results['trashed_comments'] ) ) {
            // translators: %d: number of trashed comments deleted.
            $message .= '• ' . sprintf( __( '%d trashed comments deleted', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['trashed_comments'] ) . '<br>';
        }
        // Posts & Pages.
        if ( !empty( $cleanup_results['post_revisions'] ) ) {
            // translators: %d: number of post revisions.
            $message .= '• ' . sprintf( __( '%d post revisions', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['post_revisions'] ) . '<br>';
        }
        if ( !empty( $cleanup_results['orphaned_revisions'] ) ) {
            // translators: %d: number of orphaned revisions.
            $message .= '• ' . sprintf( __( '%d orphaned revisions', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['orphaned_revisions'] ) . '<br>';
        }
        if ( !empty( $cleanup_results['auto_drafts'] ) ) {
            // translators: %d: number of auto-drafts.
            $message .= '• ' . sprintf( __( '%d auto-drafts', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['auto_drafts'] ) . '<br>';
        }
        if ( !empty( $cleanup_results['trashed_posts'] ) ) {
            // translators: %d: number of trashed posts/pages.
            $message .= '• ' . sprintf( __( '%d trashed posts/pages', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['trashed_posts'] ) . '<br>';
        }
        // Database Optimization.
        if ( !empty( $cleanup_results['transients'] ) ) {
            // translators: %d: number of expired transients.
            $message .= '• ' . sprintf( __( '%d expired transients', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['transients'] ) . '<br>';
        }
        if ( !empty( $cleanup_results['timeouts'] ) ) {
            // translators: %d: number of transient timeouts.
            $message .= '• ' . sprintf( __( '%d transient timeouts', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['timeouts'] ) . '<br>';
        }
    }
    // Orphaned Data Detection Section.
    if ( !empty( $cleanup_results['orphaned_data_detected'] ) ) {
        $message .= '<br><strong>' . __( '⚠️ Orphaned Data Detected:', 'wuclean-database-cleaner-for-woocommerce' ) . '</strong><br>';
        // translators: %d: number of orphaned data entries found.
        $message .= sprintf( __( '%d orphaned database entries have been detected.', 'wuclean-database-cleaner-for-woocommerce' ), $cleanup_results['orphaned_data_detected'] ) . '<br>';
        $message .= __( 'Please review them in the Orphaned Data tab before deletion.', 'wuclean-database-cleaner-for-woocommerce' ) . '<br>';
    }
    if ( !$has_deletions ) {
        $message .= __( 'No items were deleted.', 'wuclean-database-cleaner-for-woocommerce' ) . '<br>';
    }
    $message .= '</div>';
    wp_send_json_success( array(
        'message' => wp_kses_post( $message ),
    ) );
}

// Register AJAX action.
add_action( 'wp_ajax_pt_wuclean_run_other_cleanup', 'pt_wuclean_ajax_run_other_cleanup' );
/**
 * Enqueue scripts for other cleanup page.
 *
 * @param string $hook Current admin page hook.
 * @return void
 */
function pt_wuclean_enqueue_other_cleanup_scripts(  $hook  ) {
    // Support both historical and current menu hook names so the inline script
    // is loaded regardless of whether the parent slug is "wuclean" (older free
    // versions) or "wuclean-database-cleaner-for-woocommerce" (current builds).
    if ( 'wuclean-database-cleaner-for-woocommerce_page_wuclean-other' !== $hook && 'wuclean_page_wuclean-other' !== $hook ) {
        return;
    }
    wp_enqueue_script(
        'pt-wuclean-common',
        plugin_dir_url( __FILE__ ) . 'assets/js/common.js',
        array('jquery'),
        '1.0.0',
        true
    );
    wp_add_inline_script( 'pt-wuclean-common', "\n        jQuery(document).ready(function(\$) {\n            \$('#pt-run-other-cleanup').on('click', function() {\n                var button = \$(this);\n                var resultDiv = \$('#pt-other-cleanup-result');\n                var btnRunning = '" . esc_js( __( 'Running...', 'wuclean-database-cleaner-for-woocommerce' ) ) . "';\n                var btnReady = '" . esc_js( __( 'Run Cleanup Now', 'wuclean-database-cleaner-for-woocommerce' ) ) . "';\n                var errGeneric = '" . esc_js( __( 'An error occurred. Please try again.', 'wuclean-database-cleaner-for-woocommerce' ) ) . "';\n\n                button.prop('disabled', true).text(btnRunning);\n                resultDiv.hide();\n\n                \$.ajax({\n                    url: ajaxurl,\n                    type: 'POST',\n                    data: {\n                        action: 'pt_wuclean_run_other_cleanup',\n                        nonce: '" . wp_create_nonce( 'pt_wuclean_other_cleanup' ) . "'\n                    },\n                    success: function(response) {\n                        if (response && response.success && response.data && response.data.message) {\n                            if (typeof ptWucleanSetKsesTrustedNoticeHtml === 'function') {\n                                ptWucleanSetKsesTrustedNoticeHtml(resultDiv, response.data.message);\n                            } else {\n                                resultDiv.empty().html(response.data.message).show();\n                            }\n                        } else if (response && !response.success) {\n                            var err = (typeof ptWucleanCoerceAjaxMessage === 'function')\n                                ? ptWucleanCoerceAjaxMessage(response.data, '')\n                                : '';\n                            if (err) {\n                                resultDiv.empty().append(\$('<div>', { class: 'notice notice-error' }).append(\$('<p>').text(err)));\n                                resultDiv.show();\n                            } else {\n                                resultDiv.empty().hide();\n                            }\n                        } else {\n                            resultDiv.empty().hide();\n                        }\n                        button.prop('disabled', false).text(btnReady);\n                    },\n                    error: function() {\n                        resultDiv.empty().append(\$('<div>', { class: 'notice notice-error' }).append(\$('<p>').text(errGeneric)));\n                        resultDiv.show();\n                        button.prop('disabled', false).text(btnReady);\n                    }\n                });\n            });\n        });\n    " );
}

add_action( 'admin_enqueue_scripts', 'pt_wuclean_enqueue_other_cleanup_scripts' );