<?php
/**
 * Database Optimizer Module
 *
 * Optimize and clean your WordPress database for better performance.
 *
 * @package BlaminhorEssentials
 */

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

/**
 * Database Optimizer module class
 *
 * Note: This module intentionally uses direct database queries because:
 * 1. It needs to perform low-level database operations (SHOW TABLE STATUS, OPTIMIZE TABLE)
 * 2. It must query system tables and metadata not accessible via WordPress APIs
 * 3. Caching would be counterproductive as we need real-time database state
 * 4. The operations are admin-only and require manage_options capability
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
 */
class Blaminhor_Essentials_Module_Db_Optimizer extends Blaminhor_Essentials_Module {

	/**
	 * Module ID
	 *
	 * @var string
	 */
	protected $id = 'db-optimizer';

	/**
	 * Module name
	 *
	 * @var string
	 */
	protected $name = 'Database Optimizer';

	/**
	 * Backup directory path
	 *
	 * @var string
	 */
	private $backup_dir;

	/**
	 * Initialize module
	 */
	protected function init() {
		$this->name        = __( 'Database Optimizer', 'blaminhor-essentials' );
		$this->description = __( 'Optimize and clean your WordPress database for better performance.', 'blaminhor-essentials' );
		$this->backup_dir  = is_multisite()
			? WP_CONTENT_DIR . '/blaminhor-essentials/backups/' . get_current_blog_id()
			: WP_CONTENT_DIR . '/blaminhor-essentials/backups';
	}

	/**
	 * Get default settings
	 *
	 * @return array
	 */
	protected function get_default_settings() {
		return array(
			'auto_cleanup'               => false,
			'cleanup_revisions'          => true,
			'cleanup_auto_drafts'        => true,
			'cleanup_trashed_posts'      => true,
			'cleanup_spam_comments'      => true,
			'cleanup_trash_comments'     => true,
			'cleanup_transients'         => true,
			'cleanup_orphan_meta'        => true,
			'cleanup_duplicated_postmeta'=> true,
			'cleanup_oembed_postmeta'    => true,
			'cleanup_duplicated_commentmeta' => true,
			'cleanup_pending_comments'   => false,
			'cleanup_orphan_usermeta'    => true,
			'cleanup_duplicated_usermeta'=> true,
			'cleanup_dead_cron'          => true,
			'revisions_to_keep'          => 5,
			'schedule'                   => 'weekly',
		);
	}

	/**
	 * Register hooks
	 */
	protected function register_hooks() {
		add_action( 'admin_init', array( $this, 'handle_settings_submission' ) );
		add_action( 'admin_enqueue_scripts', array( $this, 'enqueue_assets' ) );

		// AJAX handlers.
		add_action( 'wp_ajax_ap_db_get_stats', array( $this, 'ajax_get_stats' ) );
		add_action( 'wp_ajax_ap_db_optimize', array( $this, 'ajax_optimize' ) );
		add_action( 'wp_ajax_ap_db_optimize_tables', array( $this, 'ajax_optimize_tables' ) );
		add_action( 'wp_ajax_ap_dboptimizer_create_backup', array( $this, 'ajax_create_backup' ) );

		// Scheduled cleanup.
		if ( $this->get_setting( 'auto_cleanup', false ) ) {
			add_action( 'ap_db_scheduled_cleanup', array( $this, 'run_scheduled_cleanup' ) );
			$this->schedule_cleanup();
		}
	}

	/**
	 * Enqueue module assets
	 *
	 * @param string $hook Admin page hook.
	 */
	public function enqueue_assets( $hook ) {
		if ( strpos( $hook, 'blaminhor-essentials-db-optimizer' ) === false ) {
			return;
		}

		$config = array(
			'nonce'   => wp_create_nonce( 'blaminhor_essentials_admin' ),
			'ajaxurl' => admin_url( 'admin-ajax.php' ),
			'strings' => array(
				'creatingBackup'       => __( 'Creating backup...', 'blaminhor-essentials' ),
				'backupCreated'        => __( 'Backup created:', 'blaminhor-essentials' ),
				'errorCreatingBackup'  => __( 'Error creating backup.', 'blaminhor-essentials' ),
				'createBackupNow'      => __( 'Create Backup Now', 'blaminhor-essentials' ),
				'errorActivatingModule'=> __( 'Error activating module.', 'blaminhor-essentials' ),
				/* translators: %s: number of items (singular) */
				'itemSingular'         => __( '%s item', 'blaminhor-essentials' ),
				/* translators: %s: number of items (plural) */
				'itemPlural'           => __( '%s items', 'blaminhor-essentials' ),
				'confirmClean'         => __( 'Are you sure you want to clean these items? This action cannot be undone.', 'blaminhor-essentials' ),
				'cleaning'             => __( 'Cleaning...', 'blaminhor-essentials' ),
				'cleanupCompleted'     => __( 'Cleanup completed!', 'blaminhor-essentials' ),
				'itemsDeleted'         => __( 'items deleted.', 'blaminhor-essentials' ),
				'error'                => __( 'Error:', 'blaminhor-essentials' ),
				'clean'                => __( 'Clean', 'blaminhor-essentials' ),
				'cleanAll'             => __( 'Clean All', 'blaminhor-essentials' ),
				'optimizing'           => __( 'Optimizing...', 'blaminhor-essentials' ),
				'tablesOptimized'      => __( 'Tables optimized successfully!', 'blaminhor-essentials' ),
			),
		);

		wp_add_inline_script(
			'blaminhor-essentials-modules',
			'jQuery(document).ready(function($) { BlaminhorEssentialsDbOptimizer.init(' . wp_json_encode( $config ) . '); });'
		);
	}

	/**
	 * Schedule automatic cleanup
	 */
	private function schedule_cleanup() {
		if ( ! wp_next_scheduled( 'ap_db_scheduled_cleanup' ) ) {
			$schedule = $this->get_setting( 'schedule', 'weekly' );
			wp_schedule_event( time(), $schedule, 'ap_db_scheduled_cleanup' );
		}
	}

	/**
	 * Run scheduled cleanup
	 */
	public function run_scheduled_cleanup() {
		$this->cleanup_revisions();
		$this->cleanup_auto_drafts();
		$this->cleanup_trashed_posts();
		$this->cleanup_spam_comments();
		$this->cleanup_trashed_comments();
		$this->cleanup_transients();
		$this->cleanup_orphan_postmeta();
		$this->cleanup_orphan_commentmeta();
		$this->cleanup_orphan_termmeta();

		if ( $this->get_setting( 'cleanup_duplicated_postmeta', true ) ) {
			$this->cleanup_duplicated_postmeta();
		}
		if ( $this->get_setting( 'cleanup_oembed_postmeta', true ) ) {
			$this->cleanup_oembed_postmeta();
		}
		if ( $this->get_setting( 'cleanup_duplicated_commentmeta', true ) ) {
			$this->cleanup_duplicated_commentmeta();
		}
		// Note: pending_comments excluded from scheduled cleanup by default.
		if ( $this->get_setting( 'cleanup_pending_comments', false ) ) {
			$this->cleanup_pending_comments();
		}
		if ( $this->get_setting( 'cleanup_orphan_usermeta', true ) ) {
			$this->cleanup_orphan_usermeta();
		}
		if ( $this->get_setting( 'cleanup_duplicated_usermeta', true ) ) {
			$this->cleanup_duplicated_usermeta();
		}
		if ( $this->get_setting( 'cleanup_dead_cron', true ) ) {
			$this->cleanup_dead_cron();
		}
	}

	/**
	 * Get database statistics
	 *
	 * @return array
	 */
	public function get_stats() {
		global $wpdb;

		$stats = array();

		// Post revisions.
		$revisions_to_keep = absint( $this->get_setting( 'revisions_to_keep', 5 ) );
		if ( $revisions_to_keep > 0 ) {
			// Get revisions to delete (all except the last N per parent post).
			$stats['revisions'] = (int) $this->count_revisions_to_delete( $revisions_to_keep );
		} else {
			$stats['revisions'] = (int) $wpdb->get_var(
				"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'"
			);
		}

		// Auto-drafts.
		$stats['auto_drafts'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
		);

		// Trashed posts.
		$stats['trashed_posts'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'"
		);

		// Spam comments.
		$stats['spam_comments'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'spam'"
		);

		// Trashed comments.
		$stats['trashed_comments'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
		);

		// Expired transients.
		$stats['transients'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP()"
		);

		// Orphaned postmeta.
		$stats['orphan_postmeta'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID WHERE p.ID IS NULL"
		);

		// Orphaned commentmeta.
		$stats['orphan_commentmeta'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID WHERE c.comment_ID IS NULL"
		);

		// Orphaned termmeta.
		$stats['orphan_termmeta'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->termmeta} tm LEFT JOIN {$wpdb->terms} t ON tm.term_id = t.term_id WHERE t.term_id IS NULL"
		);

		// Orphaned term relationships.
		$stats['orphan_relationships'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->term_relationships} tr LEFT JOIN {$wpdb->posts} p ON tr.object_id = p.ID WHERE p.ID IS NULL"
		);

		// Duplicated postmeta.
		$stats['duplicated_postmeta'] = (int) $wpdb->get_var(
			"SELECT IFNULL(SUM(cnt - 1), 0) FROM (SELECT COUNT(*) AS cnt FROM {$wpdb->postmeta} GROUP BY post_id, meta_key HAVING cnt > 1) AS dupes"
		);

		// oEmbed postmeta cache.
		$stats['oembed_postmeta'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE meta_key LIKE '_oembed_%'"
		);

		// Duplicated commentmeta.
		$stats['duplicated_commentmeta'] = (int) $wpdb->get_var(
			"SELECT IFNULL(SUM(cnt - 1), 0) FROM (SELECT COUNT(*) AS cnt FROM {$wpdb->commentmeta} GROUP BY comment_id, meta_key HAVING cnt > 1) AS dupes"
		);

		// Pending comments (awaiting moderation).
		$stats['pending_comments'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = '0'"
		);

		// Orphaned usermeta.
		$stats['orphan_usermeta'] = (int) $wpdb->get_var(
			"SELECT COUNT(*) FROM {$wpdb->usermeta} um LEFT JOIN {$wpdb->users} u ON um.user_id = u.ID WHERE u.ID IS NULL"
		);

		// Duplicated usermeta.
		$stats['duplicated_usermeta'] = (int) $wpdb->get_var(
			"SELECT IFNULL(SUM(cnt - 1), 0) FROM (SELECT COUNT(*) AS cnt FROM {$wpdb->usermeta} GROUP BY user_id, meta_key HAVING cnt > 1) AS dupes"
		);

		// Dead cron events (past due by more than 24 hours).
		$cron_array = _get_cron_array();
		$dead_cron  = 0;
		if ( is_array( $cron_array ) ) {
			$threshold = time() - DAY_IN_SECONDS;
			foreach ( $cron_array as $timestamp => $hooks ) {
				if ( $timestamp < $threshold ) {
					foreach ( $hooks as $hook => $events ) {
						$dead_cron += count( $events );
					}
				}
			}
		}
		$stats['dead_cron'] = $dead_cron;

		// Database tables info.
		$stats['tables'] = array();
		$tables = $wpdb->get_results( $wpdb->prepare( 'SHOW TABLE STATUS LIKE %s', $wpdb->esc_like( $wpdb->prefix ) . '%' ) );
		$total_size = 0;
		$total_overhead = 0;
		foreach ( $tables as $table ) {
			$size = $table->Data_length + $table->Index_length;

			// For InnoDB tables, Data_free is not real overhead - it's pre-allocated space.
			// Only count overhead for MyISAM tables or if InnoDB fragmentation is significant.
			$engine = strtolower( $table->Engine ?? '' );
			if ( 'myisam' === $engine ) {
				// MyISAM: Data_free is real overhead.
				$overhead = $table->Data_free;
			} elseif ( 'innodb' === $engine ) {
				// InnoDB: Data_free is pre-allocated space managed by the engine.
				// OPTIMIZE TABLE cannot reclaim it. Do not report as overhead.
				$overhead = 0;
			} else {
				$overhead = $table->Data_free;
			}

			$total_size += $size;
			$total_overhead += $overhead;
			$stats['tables'][] = array(
				'name'     => $table->Name,
				'rows'     => $table->Rows,
				'size'     => $size,
				'overhead' => $overhead,
				'engine'   => $table->Engine ?? 'Unknown',
			);
		}
		$stats['total_size'] = $total_size;
		$stats['total_overhead'] = $total_overhead;

		// Total items to clean.
		$stats['total_items'] = $stats['revisions'] + $stats['auto_drafts'] + $stats['trashed_posts'] +
			$stats['spam_comments'] + $stats['trashed_comments'] + $stats['transients'] +
			$stats['orphan_postmeta'] + $stats['orphan_commentmeta'] + $stats['orphan_termmeta'] +
			$stats['orphan_relationships'] + $stats['duplicated_postmeta'] + $stats['oembed_postmeta'] +
			$stats['duplicated_commentmeta'] + $stats['pending_comments'] + $stats['orphan_usermeta'] +
			$stats['duplicated_usermeta'] + $stats['dead_cron'];

		return $stats;
	}

	/**
	 * Count revisions to delete (keeping last N per parent post)
	 *
	 * @param int $revisions_to_keep Number of revisions to keep per post.
	 * @return int Number of revisions to delete.
	 */
	private function count_revisions_to_delete( $revisions_to_keep ) {
		global $wpdb;

		// Get all parent posts that have revisions.
		$parents = $wpdb->get_col(
			"SELECT DISTINCT post_parent FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_parent > 0"
		);

		if ( empty( $parents ) ) {
			return 0;
		}

		$total_to_delete = 0;

		foreach ( $parents as $parent_id ) {
			// Count total revisions for this parent.
			$total_revisions = (int) $wpdb->get_var(
				$wpdb->prepare(
					"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_parent = %d",
					$parent_id
				)
			);

			// If more than we want to keep, count the excess.
			if ( $total_revisions > $revisions_to_keep ) {
				$total_to_delete += ( $total_revisions - $revisions_to_keep );
			}
		}

		return $total_to_delete;
	}

	/**
	 * Get revision IDs to delete (keeping last N per parent post)
	 *
	 * @param int $revisions_to_keep Number of revisions to keep per post.
	 * @return array Array of revision IDs to delete.
	 */
	private function get_revisions_to_delete( $revisions_to_keep ) {
		global $wpdb;

		// Get all parent posts that have revisions.
		$parents = $wpdb->get_col(
			"SELECT DISTINCT post_parent FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_parent > 0"
		);

		if ( empty( $parents ) ) {
			return array();
		}

		$revisions_to_delete = array();

		foreach ( $parents as $parent_id ) {
			// Get all revisions for this parent, ordered by date DESC.
			$revisions = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT ID FROM {$wpdb->posts} WHERE post_type = 'revision' AND post_parent = %d ORDER BY post_date DESC",
					$parent_id
				)
			);

			// Skip the first N (the ones to keep) and add the rest to delete list.
			if ( count( $revisions ) > $revisions_to_keep ) {
				$to_delete = array_slice( $revisions, $revisions_to_keep );
				$revisions_to_delete = array_merge( $revisions_to_delete, $to_delete );
			}
		}

		return $revisions_to_delete;
	}

	/**
	 * Cleanup post revisions
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_revisions() {
		global $wpdb;

		$revisions_to_keep = absint( $this->get_setting( 'revisions_to_keep', 5 ) );

		if ( $revisions_to_keep > 0 ) {
			// Keep the last X revisions per post.
			$revisions = $this->get_revisions_to_delete( $revisions_to_keep );
		} else {
			$revisions = $wpdb->get_col(
				"SELECT ID FROM {$wpdb->posts} WHERE post_type = 'revision'"
			);
		}

		$count = 0;
		foreach ( $revisions as $revision_id ) {
			wp_delete_post_revision( $revision_id );
			$count++;
		}

		return $count;
	}

	/**
	 * Cleanup auto-drafts
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_auto_drafts() {
		global $wpdb;

		$auto_drafts = $wpdb->get_col(
			"SELECT ID FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
		);

		$count = 0;
		foreach ( $auto_drafts as $post_id ) {
			wp_delete_post( $post_id, true );
			$count++;
		}

		return $count;
	}

	/**
	 * Cleanup trashed posts
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_trashed_posts() {
		global $wpdb;

		$trashed = $wpdb->get_col(
			"SELECT ID FROM {$wpdb->posts} WHERE post_status = 'trash'"
		);

		$count = 0;
		foreach ( $trashed as $post_id ) {
			wp_delete_post( $post_id, true );
			$count++;
		}

		return $count;
	}

	/**
	 * Cleanup spam comments
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_spam_comments() {
		global $wpdb;

		$spam = $wpdb->get_col(
			"SELECT comment_ID FROM {$wpdb->comments} WHERE comment_approved = 'spam'"
		);

		$count = 0;
		foreach ( $spam as $comment_id ) {
			wp_delete_comment( $comment_id, true );
			$count++;
		}

		return $count;
	}

	/**
	 * Cleanup trashed comments
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_trashed_comments() {
		global $wpdb;

		$trashed = $wpdb->get_col(
			"SELECT comment_ID FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
		);

		$count = 0;
		foreach ( $trashed as $comment_id ) {
			wp_delete_comment( $comment_id, true );
			$count++;
		}

		return $count;
	}

	/**
	 * Cleanup expired transients
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_transients() {
		global $wpdb;

		// Get expired transient names.
		$expired = $wpdb->get_col(
			"SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP()"
		);

		$count = 0;
		foreach ( $expired as $timeout_option ) {
			// Extract the transient name.
			$transient_name = str_replace( '_transient_timeout_', '', $timeout_option );

			// Delete the transient value.
			$wpdb->delete(
				$wpdb->options,
				array( 'option_name' => '_transient_' . $transient_name )
			);

			// Delete the timeout entry.
			$wpdb->delete(
				$wpdb->options,
				array( 'option_name' => $timeout_option )
			);

			$count++;
		}

		// Also clean up orphaned transient timeouts (timeout without value).
		$wpdb->query(
			"DELETE FROM {$wpdb->options} WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP()"
		);

		return $count;
	}

	/**
	 * Cleanup orphaned postmeta
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_orphan_postmeta() {
		global $wpdb;

		return $wpdb->query(
			"DELETE pm FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID WHERE p.ID IS NULL"
		);
	}

	/**
	 * Cleanup orphaned commentmeta
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_orphan_commentmeta() {
		global $wpdb;

		return $wpdb->query(
			"DELETE cm FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID WHERE c.comment_ID IS NULL"
		);
	}

	/**
	 * Cleanup orphaned termmeta
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_orphan_termmeta() {
		global $wpdb;

		return $wpdb->query(
			"DELETE tm FROM {$wpdb->termmeta} tm LEFT JOIN {$wpdb->terms} t ON tm.term_id = t.term_id WHERE t.term_id IS NULL"
		);
	}

	/**
	 * Cleanup orphaned term relationships
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_orphan_relationships() {
		global $wpdb;

		return $wpdb->query(
			"DELETE tr FROM {$wpdb->term_relationships} tr LEFT JOIN {$wpdb->posts} p ON tr.object_id = p.ID WHERE p.ID IS NULL"
		);
	}

	/**
	 * Cleanup duplicated postmeta (keep latest entry per post_id + meta_key)
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_duplicated_postmeta() {
		global $wpdb;

		return (int) $wpdb->query(
			"DELETE pm FROM {$wpdb->postmeta} pm INNER JOIN (
				SELECT post_id, meta_key, MAX(meta_id) AS keep_id
				FROM {$wpdb->postmeta}
				GROUP BY post_id, meta_key
				HAVING COUNT(*) > 1
			) dupes ON pm.post_id = dupes.post_id AND pm.meta_key = dupes.meta_key AND pm.meta_id < dupes.keep_id"
		);
	}

	/**
	 * Cleanup oEmbed postmeta cache
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_oembed_postmeta() {
		global $wpdb;

		return (int) $wpdb->query(
			"DELETE FROM {$wpdb->postmeta} WHERE meta_key LIKE '_oembed_%'"
		);
	}

	/**
	 * Cleanup duplicated commentmeta (keep latest entry per comment_id + meta_key)
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_duplicated_commentmeta() {
		global $wpdb;

		return (int) $wpdb->query(
			"DELETE cm FROM {$wpdb->commentmeta} cm INNER JOIN (
				SELECT comment_id, meta_key, MAX(meta_id) AS keep_id
				FROM {$wpdb->commentmeta}
				GROUP BY comment_id, meta_key
				HAVING COUNT(*) > 1
			) dupes ON cm.comment_id = dupes.comment_id AND cm.meta_key = dupes.meta_key AND cm.meta_id < dupes.keep_id"
		);
	}

	/**
	 * Cleanup pending comments (awaiting moderation)
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_pending_comments() {
		global $wpdb;

		$pending = $wpdb->get_col(
			"SELECT comment_ID FROM {$wpdb->comments} WHERE comment_approved = '0'"
		);

		$count = 0;
		foreach ( $pending as $comment_id ) {
			wp_delete_comment( $comment_id, true );
			$count++;
		}

		return $count;
	}

	/**
	 * Cleanup orphaned usermeta
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_orphan_usermeta() {
		global $wpdb;

		return (int) $wpdb->query(
			"DELETE um FROM {$wpdb->usermeta} um LEFT JOIN {$wpdb->users} u ON um.user_id = u.ID WHERE u.ID IS NULL"
		);
	}

	/**
	 * Cleanup duplicated usermeta (keep latest entry per user_id + meta_key)
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_duplicated_usermeta() {
		global $wpdb;

		return (int) $wpdb->query(
			"DELETE um FROM {$wpdb->usermeta} um INNER JOIN (
				SELECT user_id, meta_key, MAX(umeta_id) AS keep_id
				FROM {$wpdb->usermeta}
				GROUP BY user_id, meta_key
				HAVING COUNT(*) > 1
			) dupes ON um.user_id = dupes.user_id AND um.meta_key = dupes.meta_key AND um.umeta_id < dupes.keep_id"
		);
	}

	/**
	 * Cleanup dead cron events (past due by more than 24 hours)
	 *
	 * @return int Number of deleted items.
	 */
	public function cleanup_dead_cron() {
		$cron_array = _get_cron_array();
		if ( ! is_array( $cron_array ) ) {
			return 0;
		}

		$threshold = time() - DAY_IN_SECONDS;
		$count     = 0;

		foreach ( $cron_array as $timestamp => $hooks ) {
			if ( $timestamp < $threshold ) {
				foreach ( $hooks as $hook => $events ) {
					$count += count( $events );
				}
				unset( $cron_array[ $timestamp ] );
			}
		}

		if ( $count > 0 ) {
			_set_cron_array( $cron_array );
		}

		return $count;
	}

	/**
	 * Optimize database tables
	 *
	 * @return array Results.
	 */
	public function optimize_tables() {
		global $wpdb;

		// Get all tables with our prefix.
		$tables = $wpdb->get_col(
			$wpdb->prepare(
				'SHOW TABLES LIKE %s',
				$wpdb->esc_like( $wpdb->prefix ) . '%'
			)
		);
		$results = array();

		foreach ( $tables as $table ) {
			// Validate table name: must start with our prefix and contain only safe characters.
			if ( strpos( $table, $wpdb->prefix ) !== 0 || preg_match( '/[^a-zA-Z0-9_]/', $table ) ) {
				continue;
			}
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for table optimization
			$result = $wpdb->get_row(
				$wpdb->prepare( 'OPTIMIZE TABLE %i', $table )
			);
			$results[] = array(
				'table'  => $table,
				'status' => $result->Msg_type ?? 'unknown',
				'message' => $result->Msg_text ?? '',
			);
		}

		return $results;
	}

	/**
	 * AJAX: Get database stats
	 */
	public function ajax_get_stats() {
		check_ajax_referer( 'blaminhor_essentials_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( 'Unauthorized' );
		}

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

	/**
	 * AJAX: Run optimization
	 */
	public function ajax_optimize() {
		check_ajax_referer( 'blaminhor_essentials_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( 'Unauthorized' );
		}

		$type = isset( $_POST['type'] ) ? sanitize_key( $_POST['type'] ) : '';

		$results = array();

		switch ( $type ) {
			case 'revisions':
				$results['deleted'] = $this->cleanup_revisions();
				break;
			case 'auto_drafts':
				$results['deleted'] = $this->cleanup_auto_drafts();
				break;
			case 'trashed_posts':
				$results['deleted'] = $this->cleanup_trashed_posts();
				break;
			case 'spam_comments':
				$results['deleted'] = $this->cleanup_spam_comments();
				break;
			case 'trashed_comments':
				$results['deleted'] = $this->cleanup_trashed_comments();
				break;
			case 'transients':
				$results['deleted'] = $this->cleanup_transients();
				break;
			case 'orphan_postmeta':
				$results['deleted'] = $this->cleanup_orphan_postmeta();
				break;
			case 'orphan_commentmeta':
				$results['deleted'] = $this->cleanup_orphan_commentmeta();
				break;
			case 'orphan_termmeta':
				$results['deleted'] = $this->cleanup_orphan_termmeta();
				break;
			case 'orphan_relationships':
				$results['deleted'] = $this->cleanup_orphan_relationships();
				break;
			case 'duplicated_postmeta':
				$results['deleted'] = $this->cleanup_duplicated_postmeta();
				break;
			case 'oembed_postmeta':
				$results['deleted'] = $this->cleanup_oembed_postmeta();
				break;
			case 'duplicated_commentmeta':
				$results['deleted'] = $this->cleanup_duplicated_commentmeta();
				break;
			case 'pending_comments':
				$results['deleted'] = $this->cleanup_pending_comments();
				break;
			case 'orphan_usermeta':
				$results['deleted'] = $this->cleanup_orphan_usermeta();
				break;
			case 'duplicated_usermeta':
				$results['deleted'] = $this->cleanup_duplicated_usermeta();
				break;
			case 'dead_cron':
				$results['deleted'] = $this->cleanup_dead_cron();
				break;
			case 'all':
				$results['revisions'] = $this->cleanup_revisions();
				$results['auto_drafts'] = $this->cleanup_auto_drafts();
				$results['trashed_posts'] = $this->cleanup_trashed_posts();
				$results['spam_comments'] = $this->cleanup_spam_comments();
				$results['trashed_comments'] = $this->cleanup_trashed_comments();
				$results['transients'] = $this->cleanup_transients();
				$results['orphan_postmeta'] = $this->cleanup_orphan_postmeta();
				$results['orphan_commentmeta'] = $this->cleanup_orphan_commentmeta();
				$results['orphan_termmeta'] = $this->cleanup_orphan_termmeta();
				$results['orphan_relationships'] = $this->cleanup_orphan_relationships();
				$results['duplicated_postmeta'] = $this->cleanup_duplicated_postmeta();
				$results['oembed_postmeta'] = $this->cleanup_oembed_postmeta();
				$results['duplicated_commentmeta'] = $this->cleanup_duplicated_commentmeta();
				// Note: pending_comments excluded from "Clean All" as they may be legitimate.
				$results['orphan_usermeta'] = $this->cleanup_orphan_usermeta();
				$results['duplicated_usermeta'] = $this->cleanup_duplicated_usermeta();
				$results['dead_cron'] = $this->cleanup_dead_cron();
				$results['total'] = array_sum( $results );
				break;
			default:
				wp_send_json_error( 'Invalid type' );
		}

		wp_send_json_success( $results );
	}

	/**
	 * AJAX: Optimize tables
	 */
	public function ajax_optimize_tables() {
		check_ajax_referer( 'blaminhor_essentials_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( 'Unauthorized' );
		}

		$results = $this->optimize_tables();
		wp_send_json_success( $results );
	}

	/**
	 * Check if backup module is active
	 *
	 * @return bool
	 */
	private function is_backup_module_active() {
		$active_modules = get_option( 'blaminhor_essentials_active_modules', array() );
		return in_array( 'backup', $active_modules, true );
	}

	/**
	 * Ensure backup directory exists and is protected
	 */
	private function ensure_backup_directory() {
		if ( ! file_exists( $this->backup_dir ) ) {
			wp_mkdir_p( $this->backup_dir );
		}

		// Create .htaccess to protect backups.
		$htaccess = $this->backup_dir . '/.htaccess';
		if ( ! file_exists( $htaccess ) ) {
			file_put_contents( $htaccess, "Order deny,allow\nDeny from all" ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
		}

		// Create index.php for additional protection.
		$index = $this->backup_dir . '/index.php';
		if ( ! file_exists( $index ) ) {
			file_put_contents( $index, '<?php // Silence is golden.' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
		}
	}

	/**
	 * Create a SQL database backup
	 *
	 * @return array|WP_Error
	 */
	private function create_database_backup() {
		global $wpdb;

		if ( ! class_exists( 'ZipArchive' ) ) {
			return new WP_Error( 'no_zip', __( 'ZipArchive extension is not available.', 'blaminhor-essentials' ) );
		}

		$this->ensure_backup_directory();

		$site_name = sanitize_title( get_bloginfo( 'name' ) );
		$site_name = substr( $site_name, 0, 30 ) ?: 'wordpress';
		$timestamp = gmdate( 'Y-m-d-His' );
		$sql_temp  = $this->backup_dir . '/temp-db-optimizer-' . $timestamp . '.sql';
		$filename  = $site_name . '-' . $timestamp . '-db-optimizer.zip';
		$filepath  = $this->backup_dir . '/' . $filename;

		$tables = $wpdb->get_results( 'SHOW TABLES', ARRAY_N ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery

		if ( empty( $tables ) ) {
			return new WP_Error( 'no_tables', __( 'No database tables found.', 'blaminhor-essentials' ) );
		}

		// Start with SET NAMES to ensure proper encoding.
		$sql  = "SET NAMES utf8mb4;\n";
		$sql .= "SET CHARACTER SET utf8mb4;\n\n";
		$sql .= "-- Blaminhor Essentials Database Backup (Before DB Optimization)\n";
		$sql .= '-- Site: ' . html_entity_decode( get_bloginfo( 'name' ), ENT_QUOTES, 'UTF-8' ) . "\n";
		$sql .= '-- URL: ' . home_url() . "\n";
		$sql .= '-- Generated: ' . gmdate( 'Y-m-d H:i:s' ) . "\n\n";
		$sql .= "SET FOREIGN_KEY_CHECKS = 0;\n\n";

		foreach ( $tables as $table ) {
			$table_name = $table[0];

			// Only backup tables with our prefix.
			if ( strpos( $table_name, $wpdb->prefix ) !== 0 ) {
				continue;
			}

			// Validate table name contains only safe characters.
			if ( preg_match( '/[^a-zA-Z0-9_]/', $table_name ) ) {
				continue;
			}

			// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange
			$create = $wpdb->get_row(
				$wpdb->prepare( 'SHOW CREATE TABLE %i', $table_name ),
				ARRAY_N
			);
			// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange
			if ( $create ) {
				$sql .= $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table_name ) . ";\n";
				$sql .= $create[1] . ";\n\n";
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Required for db export
			$rows = $wpdb->get_results(
				$wpdb->prepare( 'SELECT * FROM %i', $table_name ),
				ARRAY_A
			);

			if ( ! empty( $rows ) ) {
				$columns         = array_keys( $rows[0] );
				$columns_escaped = array_map(
					function ( $col ) {
						return '`' . esc_sql( $col ) . '`';
					},
					$columns
				);

				// Prepare table identifier once for this table.
				$table_identifier = $wpdb->prepare( '%i', $table_name );

				foreach ( array_chunk( $rows, 100 ) as $chunk ) {
					$sql .= 'INSERT INTO ' . $table_identifier . ' (' . implode( ', ', $columns_escaped ) . ") VALUES\n";

					$values = array();
					foreach ( $chunk as $row ) {
						$row_values = array_map(
							function ( $value ) use ( $wpdb ) {
								if ( is_null( $value ) ) {
									return 'NULL';
								}
								return "'" . $wpdb->_real_escape( $value ) . "'";
							},
							array_values( $row )
						);
						$values[] = '(' . implode( ', ', $row_values ) . ')';
					}

					$sql .= implode( ",\n", $values ) . ";\n\n";
				}
			}
		}

		$sql .= "SET FOREIGN_KEY_CHECKS = 1;\n";

		// Write SQL to temp file.
		$written = file_put_contents( $sql_temp, $sql ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
		if ( false === $written ) {
			return new WP_Error( 'write_failed', __( 'Failed to write backup file.', 'blaminhor-essentials' ) );
		}

		// Wrap in a ZIP archive (compatible with the Backup module format).
		$zip = new ZipArchive();
		if ( $zip->open( $filepath, ZipArchive::CREATE | ZipArchive::OVERWRITE ) !== true ) {
			wp_delete_file( $sql_temp );
			return new WP_Error( 'zip_failed', __( 'Failed to create backup archive.', 'blaminhor-essentials' ) );
		}
		$zip->addFile( $sql_temp, 'database.sql' );
		$zip->close();
		wp_delete_file( $sql_temp );

		return array(
			'filename' => $filename,
			'path'     => $filepath,
			'size'     => filesize( $filepath ),
		);
	}

	/**
	 * AJAX: Create database backup
	 */
	public function ajax_create_backup() {
		check_ajax_referer( 'blaminhor_essentials_admin', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( __( 'Unauthorized', 'blaminhor-essentials' ) );
		}

		$result = $this->create_database_backup();

		if ( is_wp_error( $result ) ) {
			wp_send_json_error( $result->get_error_message() );
		}

		wp_send_json_success(
			array(
				'message'  => __( 'Backup created successfully.', 'blaminhor-essentials' ),
				'filename' => $result['filename'],
				'size'     => size_format( $result['size'] ),
			)
		);
	}

	/**
	 * Render backup notice
	 */
	private function render_backup_notice() {
		$backup_active = $this->is_backup_module_active();
		$backup_url    = admin_url( 'admin.php?page=blaminhor-essentials-backup' );
		$modules_url   = admin_url( 'admin.php?page=blaminhor-essentials' );
		?>
		<div class="ap-db-backup-notice">
			<div class="notice-icon">
				<span class="dashicons dashicons-shield"></span>
			</div>
			<div class="notice-content">
				<h4><?php esc_html_e( 'Important: Back up your database first', 'blaminhor-essentials' ); ?></h4>
				<p>
					<?php esc_html_e( 'Before cleaning your database, we strongly recommend creating a backup. This ensures you can restore your data if something goes wrong.', 'blaminhor-essentials' ); ?>
				</p>
				<div class="notice-actions">
					<button type="button" class="button button-primary" id="ap-db-create-backup-btn">
						<span class="dashicons dashicons-cloud-saved ap-mt-xs"></span>
						<?php esc_html_e( 'Create Backup Now', 'blaminhor-essentials' ); ?>
					</button>
					<?php if ( $backup_active ) : ?>
						<a href="<?php echo esc_url( $backup_url ); ?>" class="button">
							<span class="dashicons dashicons-external ap-mt-xs"></span>
							<?php esc_html_e( 'Go to Backup Module', 'blaminhor-essentials' ); ?>
						</a>
					<?php else : ?>
						<a href="<?php echo esc_url( $modules_url ); ?>" class="button">
							<span class="dashicons dashicons-admin-plugins ap-mt-xs"></span>
							<?php esc_html_e( 'Activate Backup Module', 'blaminhor-essentials' ); ?>
						</a>
					<?php endif; ?>
					<button type="button" class="button" id="ap-db-dismiss-notice">
						<?php esc_html_e( 'I have a backup', 'blaminhor-essentials' ); ?>
					</button>
				</div>
				<div id="ap-db-backup-result"></div>
			</div>
		</div>
		<?php
	}

	/**
	 * Format file size
	 *
	 * @param int $bytes Bytes.
	 * @return string
	 */
	private function format_size( $bytes ) {
		if ( $bytes >= 1073741824 ) {
			return number_format( $bytes / 1073741824, 2 ) . ' GB';
		} elseif ( $bytes >= 1048576 ) {
			return number_format( $bytes / 1048576, 2 ) . ' MB';
		} elseif ( $bytes >= 1024 ) {
			return number_format( $bytes / 1024, 2 ) . ' KB';
		}
		return $bytes . ' B';
	}

	/**
	 * Process settings
	 *
	 * @param array $data Form data.
	 */
	protected function process_settings( $data ) {
		$settings = array(
			'auto_cleanup'               => isset( $data['auto_cleanup'] ),
			'cleanup_revisions'          => isset( $data['cleanup_revisions'] ),
			'cleanup_auto_drafts'        => isset( $data['cleanup_auto_drafts'] ),
			'cleanup_trashed_posts'      => isset( $data['cleanup_trashed_posts'] ),
			'cleanup_spam_comments'      => isset( $data['cleanup_spam_comments'] ),
			'cleanup_trash_comments'     => isset( $data['cleanup_trash_comments'] ),
			'cleanup_transients'         => isset( $data['cleanup_transients'] ),
			'cleanup_orphan_meta'        => isset( $data['cleanup_orphan_meta'] ),
			'cleanup_duplicated_postmeta'=> isset( $data['cleanup_duplicated_postmeta'] ),
			'cleanup_oembed_postmeta'    => isset( $data['cleanup_oembed_postmeta'] ),
			'cleanup_duplicated_commentmeta' => isset( $data['cleanup_duplicated_commentmeta'] ),
			'cleanup_pending_comments'   => isset( $data['cleanup_pending_comments'] ),
			'cleanup_orphan_usermeta'    => isset( $data['cleanup_orphan_usermeta'] ),
			'cleanup_duplicated_usermeta'=> isset( $data['cleanup_duplicated_usermeta'] ),
			'cleanup_dead_cron'          => isset( $data['cleanup_dead_cron'] ),
			'revisions_to_keep'          => isset( $data['revisions_to_keep'] ) ? absint( $data['revisions_to_keep'] ) : 5,
			'schedule'                   => isset( $data['schedule'] ) ? sanitize_key( $data['schedule'] ) : 'weekly',
		);

		$this->save_settings( $settings );

		// Reschedule if needed.
		wp_clear_scheduled_hook( 'ap_db_scheduled_cleanup' );
		if ( $settings['auto_cleanup'] ) {
			wp_schedule_event( time(), $settings['schedule'], 'ap_db_scheduled_cleanup' );
		}

		add_action( 'admin_notices', function () {
			blaminhor_essentials_admin_notice( __( 'Settings saved.', 'blaminhor-essentials' ), 'success' );
		} );
	}

	/**
	 * Render settings page
	 */
	public function render_settings_page() {
		$stats = $this->get_stats();
		?>
		<div class="wrap blaminhor-essentials-wrap">
			<div class="blaminhor-essentials-header">
				<h1><?php esc_html_e( 'Database Optimizer', 'blaminhor-essentials' ); ?></h1>
				<p class="blaminhor-essentials-tagline"><?php esc_html_e( 'Optimize and clean your WordPress database for better performance.', 'blaminhor-essentials' ); ?></p>
			</div>

			<div class="blaminhor-essentials-tabs-wrapper">
				<div class="blaminhor-essentials-tabs">
					<button type="button" class="blaminhor-essentials-tab active" data-tab="overview"><?php esc_html_e( 'Overview', 'blaminhor-essentials' ); ?></button>
					<button type="button" class="blaminhor-essentials-tab" data-tab="cleanup"><?php esc_html_e( 'Cleanup', 'blaminhor-essentials' ); ?></button>
					<button type="button" class="blaminhor-essentials-tab" data-tab="tables"><?php esc_html_e( 'Tables', 'blaminhor-essentials' ); ?></button>
					<button type="button" class="blaminhor-essentials-tab" data-tab="settings"><?php esc_html_e( 'Settings', 'blaminhor-essentials' ); ?></button>
				</div>

			<!-- Overview Tab -->
			<div class="blaminhor-essentials-tab-content active" data-tab-content="overview">
				<div class="ap-db-overview">
					<?php if ( $stats['total_items'] > 0 ) : ?>
						<?php $this->render_backup_notice(); ?>
					<?php endif; ?>
					<div class="ap-db-summary-cards">
						<div class="ap-db-card ap-db-card-primary">
							<div class="ap-db-card-icon">
								<span class="dashicons dashicons-database"></span>
							</div>
							<div class="ap-db-card-content">
								<div class="ap-db-card-value"><?php echo esc_html( $this->format_size( $stats['total_size'] ) ); ?></div>
								<div class="ap-db-card-label"><?php esc_html_e( 'Database Size', 'blaminhor-essentials' ); ?></div>
							</div>
						</div>
						<div class="ap-db-card ap-db-card-warning">
							<div class="ap-db-card-icon">
								<span class="dashicons dashicons-warning"></span>
							</div>
							<div class="ap-db-card-content">
								<div class="ap-db-card-value"><?php echo esc_html( $this->format_size( $stats['total_overhead'] ) ); ?></div>
								<div class="ap-db-card-label"><?php esc_html_e( 'Overhead', 'blaminhor-essentials' ); ?></div>
							</div>
						</div>
						<div class="ap-db-card ap-db-card-info">
							<div class="ap-db-card-icon">
								<span class="dashicons dashicons-trash"></span>
							</div>
							<div class="ap-db-card-content">
								<div class="ap-db-card-value" id="ap-db-total-items"><?php echo esc_html( number_format_i18n( $stats['total_items'] ) ); ?></div>
								<div class="ap-db-card-label"><?php esc_html_e( 'Items to Clean', 'blaminhor-essentials' ); ?></div>
							</div>
						</div>
						<div class="ap-db-card ap-db-card-success">
							<div class="ap-db-card-icon">
								<span class="dashicons dashicons-list-view"></span>
							</div>
							<div class="ap-db-card-content">
								<div class="ap-db-card-value"><?php echo esc_html( count( $stats['tables'] ) ); ?></div>
								<div class="ap-db-card-label"><?php esc_html_e( 'Tables', 'blaminhor-essentials' ); ?></div>
							</div>
						</div>
					</div>

					<div class="ap-db-quick-actions">
						<h3><?php esc_html_e( 'Quick Actions', 'blaminhor-essentials' ); ?></h3>
						<div class="ap-db-action-buttons">
							<button type="button" class="button button-primary button-hero ap-btn-icon" id="ap-db-clean-all" <?php disabled( $stats['total_items'] === 0 ); ?>>
								<span class="dashicons dashicons-yes-alt"></span>
								<?php esc_html_e( 'Clean All', 'blaminhor-essentials' ); ?>
							</button>
							<button type="button" class="button button-secondary button-hero ap-btn-icon" id="ap-db-optimize-tables">
								<span class="dashicons dashicons-performance"></span>
								<?php esc_html_e( 'Optimize Tables', 'blaminhor-essentials' ); ?>
							</button>
							<button type="button" class="button button-secondary button-hero ap-btn-icon" id="ap-db-refresh-stats">
								<span class="dashicons dashicons-update"></span>
								<?php esc_html_e( 'Refresh Stats', 'blaminhor-essentials' ); ?>
							</button>
						</div>
					</div>
				</div>
			</div>

			<!-- Cleanup Tab -->
			<div class="blaminhor-essentials-tab-content" data-tab-content="cleanup">
				<?php $this->render_backup_notice(); ?>
				<div class="ap-db-cleanup-grid">
					<?php
					$cleanup_items = array(
						'revisions' => array(
							'icon'  => 'backup',
							'label' => __( 'Post Revisions', 'blaminhor-essentials' ),
							'desc'  => __( 'Old versions of your posts saved automatically.', 'blaminhor-essentials' ),
							'count' => $stats['revisions'],
						),
						'auto_drafts' => array(
							'icon'  => 'edit',
							'label' => __( 'Auto-Drafts', 'blaminhor-essentials' ),
							'desc'  => __( 'Automatically saved drafts that are no longer needed.', 'blaminhor-essentials' ),
							'count' => $stats['auto_drafts'],
						),
						'trashed_posts' => array(
							'icon'  => 'trash',
							'label' => __( 'Trashed Posts', 'blaminhor-essentials' ),
							'desc'  => __( 'Posts, pages, and other content in the trash.', 'blaminhor-essentials' ),
							'count' => $stats['trashed_posts'],
						),
						'spam_comments' => array(
							'icon'  => 'shield',
							'label' => __( 'Spam Comments', 'blaminhor-essentials' ),
							'desc'  => __( 'Comments marked as spam.', 'blaminhor-essentials' ),
							'count' => $stats['spam_comments'],
						),
						'trashed_comments' => array(
							'icon'  => 'trash',
							'label' => __( 'Trashed Comments', 'blaminhor-essentials' ),
							'desc'  => __( 'Comments in the trash.', 'blaminhor-essentials' ),
							'count' => $stats['trashed_comments'],
						),
						'transients' => array(
							'icon'  => 'clock',
							'label' => __( 'Expired Transients', 'blaminhor-essentials' ),
							'desc'  => __( 'Temporary cached data that has expired.', 'blaminhor-essentials' ),
							'count' => $stats['transients'],
						),
						'orphan_postmeta' => array(
							'icon'  => 'database',
							'label' => __( 'Orphaned Post Meta', 'blaminhor-essentials' ),
							'desc'  => __( 'Post metadata without associated posts.', 'blaminhor-essentials' ),
							'count' => $stats['orphan_postmeta'],
						),
						'orphan_commentmeta' => array(
							'icon'  => 'database',
							'label' => __( 'Orphaned Comment Meta', 'blaminhor-essentials' ),
							'desc'  => __( 'Comment metadata without associated comments.', 'blaminhor-essentials' ),
							'count' => $stats['orphan_commentmeta'],
						),
						'orphan_termmeta' => array(
							'icon'  => 'database',
							'label' => __( 'Orphaned Term Meta', 'blaminhor-essentials' ),
							'desc'  => __( 'Term metadata without associated terms.', 'blaminhor-essentials' ),
							'count' => $stats['orphan_termmeta'],
						),
						'orphan_relationships' => array(
							'icon'  => 'networking',
							'label' => __( 'Orphaned Relationships', 'blaminhor-essentials' ),
							'desc'  => __( 'Term relationships without associated posts.', 'blaminhor-essentials' ),
							'count' => $stats['orphan_relationships'],
						),
						'duplicated_postmeta' => array(
							'icon'  => 'admin-page',
							'label' => __( 'Duplicated Post Meta', 'blaminhor-essentials' ),
							'desc'  => __( 'Duplicate metadata entries for the same post and key.', 'blaminhor-essentials' ),
							'count' => $stats['duplicated_postmeta'],
						),
						'oembed_postmeta' => array(
							'icon'  => 'embed-video',
							'label' => __( 'oEmbed Cache', 'blaminhor-essentials' ),
							'desc'  => __( 'Cached oEmbed responses stored in post metadata.', 'blaminhor-essentials' ),
							'count' => $stats['oembed_postmeta'],
						),
						'duplicated_commentmeta' => array(
							'icon'  => 'admin-comments',
							'label' => __( 'Duplicated Comment Meta', 'blaminhor-essentials' ),
							'desc'  => __( 'Duplicate metadata entries for the same comment and key.', 'blaminhor-essentials' ),
							'count' => $stats['duplicated_commentmeta'],
						),
						'pending_comments' => array(
							'icon'  => 'marker',
							'label' => __( 'Pending Comments', 'blaminhor-essentials' ),
							'desc'  => __( 'Comments awaiting moderation. Review before cleaning.', 'blaminhor-essentials' ),
							'count' => $stats['pending_comments'],
						),
						'orphan_usermeta' => array(
							'icon'  => 'admin-users',
							'label' => __( 'Orphaned User Meta', 'blaminhor-essentials' ),
							'desc'  => __( 'User metadata without associated users.', 'blaminhor-essentials' ),
							'count' => $stats['orphan_usermeta'],
						),
						'duplicated_usermeta' => array(
							'icon'  => 'admin-users',
							'label' => __( 'Duplicated User Meta', 'blaminhor-essentials' ),
							'desc'  => __( 'Duplicate metadata entries for the same user and key.', 'blaminhor-essentials' ),
							'count' => $stats['duplicated_usermeta'],
						),
						'dead_cron' => array(
							'icon'  => 'calendar-alt',
							'label' => __( 'Dead Cron Events', 'blaminhor-essentials' ),
							'desc'  => __( 'Scheduled events past due by more than 24 hours.', 'blaminhor-essentials' ),
							'count' => $stats['dead_cron'],
						),
					);

					foreach ( $cleanup_items as $key => $item ) :
					?>
					<div class="ap-db-cleanup-card" data-type="<?php echo esc_attr( $key ); ?>">
						<div class="ap-db-cleanup-header">
							<span class="dashicons dashicons-<?php echo esc_attr( $item['icon'] ); ?>"></span>
							<h4><?php echo esc_html( $item['label'] ); ?></h4>
						</div>
						<p class="ap-db-cleanup-desc"><?php echo esc_html( $item['desc'] ); ?></p>
						<div class="ap-db-cleanup-footer">
							<span class="ap-db-cleanup-count" data-count="<?php echo esc_attr( $key ); ?>">
								<?php
								printf(
									/* translators: %s: number of items */
									esc_html( _n( '%s item', '%s items', $item['count'], 'blaminhor-essentials' ) ),
									esc_html( number_format_i18n( $item['count'] ) )
								);
								?>
							</span>
							<button type="button" class="button ap-db-clean-btn" data-type="<?php echo esc_attr( $key ); ?>" <?php disabled( $item['count'] === 0 ); ?>>
								<?php esc_html_e( 'Clean', 'blaminhor-essentials' ); ?>
							</button>
						</div>
					</div>
					<?php endforeach; ?>
				</div>
			</div>

			<!-- Tables Tab -->
			<div class="blaminhor-essentials-tab-content" data-tab-content="tables">
				<div class="ap-db-tables-header">
					<p><?php esc_html_e( 'Database tables and their current status. Optimize tables to reclaim disk space and improve performance.', 'blaminhor-essentials' ); ?></p>
					<button type="button" class="button button-primary ap-btn-icon" id="ap-db-optimize-all-tables">
						<span class="dashicons dashicons-performance"></span>
						<?php esc_html_e( 'Optimize All Tables', 'blaminhor-essentials' ); ?>
					</button>
				</div>
				<table class="widefat striped ap-sortable-table" id="ap-db-tables-list">
					<thead>
						<tr>
							<th class="ap-sortable" data-sort="string" data-sort-default="asc"><?php esc_html_e( 'Table', 'blaminhor-essentials' ); ?> <span class="ap-sort-icon"></span></th>
							<th class="ap-sortable" data-sort="number"><?php esc_html_e( 'Rows', 'blaminhor-essentials' ); ?> <span class="ap-sort-icon"></span></th>
							<th class="ap-sortable" data-sort="size"><?php esc_html_e( 'Size', 'blaminhor-essentials' ); ?> <span class="ap-sort-icon"></span></th>
							<th class="ap-sortable" data-sort="size"><?php esc_html_e( 'Overhead', 'blaminhor-essentials' ); ?> <span class="ap-sort-icon"></span></th>
						</tr>
					</thead>
					<tbody>
						<?php foreach ( $stats['tables'] as $table ) : ?>
						<tr data-name="<?php echo esc_attr( $table['name'] ); ?>" data-rows="<?php echo esc_attr( $table['rows'] ); ?>" data-size="<?php echo esc_attr( $table['size'] ); ?>" data-overhead="<?php echo esc_attr( $table['overhead'] ); ?>">
							<td><code><?php echo esc_html( $table['name'] ); ?></code></td>
							<td><?php echo esc_html( number_format_i18n( $table['rows'] ) ); ?></td>
							<td><?php echo esc_html( $this->format_size( $table['size'] ) ); ?></td>
							<td class="<?php echo $table['overhead'] > 0 ? 'ap-db-overhead-warning' : 'ap-db-overhead-ok'; ?>">
								<?php echo esc_html( $table['overhead'] > 0 ? $this->format_size( $table['overhead'] ) : '0 B' ); ?>
							</td>
						</tr>
						<?php endforeach; ?>
					</tbody>
					<tfoot>
						<tr>
							<th><strong><?php esc_html_e( 'Total', 'blaminhor-essentials' ); ?></strong></th>
							<th></th>
							<th><strong><?php echo esc_html( $this->format_size( $stats['total_size'] ) ); ?></strong></th>
							<th><strong><?php echo esc_html( $this->format_size( $stats['total_overhead'] ) ); ?></strong></th>
						</tr>
					</tfoot>
				</table>
			</div>

			<!-- Settings Tab -->
			<div class="blaminhor-essentials-tab-content" data-tab-content="settings">
				<form method="post">
					<?php $this->render_nonce_field(); ?>

					<h3><?php esc_html_e( 'Automatic Cleanup', 'blaminhor-essentials' ); ?></h3>
					<table class="form-table">
						<tr>
							<th scope="row"><?php esc_html_e( 'Enable Auto Cleanup', 'blaminhor-essentials' ); ?></th>
							<td>
								<label>
									<input type="checkbox" name="auto_cleanup" <?php checked( $this->get_setting( 'auto_cleanup', false ) ); ?>>
									<?php esc_html_e( 'Automatically clean the database on schedule', 'blaminhor-essentials' ); ?>
								</label>
							</td>
						</tr>
						<tr>
							<th scope="row"><?php esc_html_e( 'Schedule', 'blaminhor-essentials' ); ?></th>
							<td>
								<select name="schedule">
									<option value="daily" <?php selected( $this->get_setting( 'schedule', 'weekly' ), 'daily' ); ?>><?php esc_html_e( 'Daily', 'blaminhor-essentials' ); ?></option>
									<option value="twicedaily" <?php selected( $this->get_setting( 'schedule', 'weekly' ), 'twicedaily' ); ?>><?php esc_html_e( 'Twice Daily', 'blaminhor-essentials' ); ?></option>
									<option value="weekly" <?php selected( $this->get_setting( 'schedule', 'weekly' ), 'weekly' ); ?>><?php esc_html_e( 'Weekly', 'blaminhor-essentials' ); ?></option>
								</select>
							</td>
						</tr>
					</table>

					<hr>

					<h3><?php esc_html_e( 'Cleanup Options', 'blaminhor-essentials' ); ?></h3>
					<table class="form-table">
						<tr>
							<th scope="row"><?php esc_html_e( 'Revisions to Keep', 'blaminhor-essentials' ); ?></th>
							<td>
								<input type="number" name="revisions_to_keep" value="<?php echo esc_attr( $this->get_setting( 'revisions_to_keep', 5 ) ); ?>" min="0" max="100" class="small-text">
								<p class="description"><?php esc_html_e( 'Number of revisions to keep per post. Set to 0 to delete all revisions.', 'blaminhor-essentials' ); ?></p>
							</td>
						</tr>
					</table>

					<hr>

					<h3><?php esc_html_e( 'Scheduled Cleanup Types', 'blaminhor-essentials' ); ?></h3>
					<p class="description"><?php esc_html_e( 'Select which cleanup types to include in the automatic scheduled cleanup.', 'blaminhor-essentials' ); ?></p>
					<table class="form-table">
						<tr>
							<th scope="row"><?php esc_html_e( 'Duplicated Post Meta', 'blaminhor-essentials' ); ?></th>
							<td>
								<label>
									<input type="checkbox" name="cleanup_duplicated_postmeta" <?php checked( $this->get_setting( 'cleanup_duplicated_postmeta', true ) ); ?>>
									<?php esc_html_e( 'Remove duplicate postmeta entries', 'blaminhor-essentials' ); ?>
								</label>
							</td>
						</tr>
						<tr>
							<th scope="row"><?php esc_html_e( 'oEmbed Cache', 'blaminhor-essentials' ); ?></th>
							<td>
								<label>
									<input type="checkbox" name="cleanup_oembed_postmeta" <?php checked( $this->get_setting( 'cleanup_oembed_postmeta', true ) ); ?>>
									<?php esc_html_e( 'Remove cached oEmbed responses', 'blaminhor-essentials' ); ?>
								</label>
							</td>
						</tr>
						<tr>
							<th scope="row"><?php esc_html_e( 'Duplicated Comment Meta', 'blaminhor-essentials' ); ?></th>
							<td>
								<label>
									<input type="checkbox" name="cleanup_duplicated_commentmeta" <?php checked( $this->get_setting( 'cleanup_duplicated_commentmeta', true ) ); ?>>
									<?php esc_html_e( 'Remove duplicate commentmeta entries', 'blaminhor-essentials' ); ?>
								</label>
							</td>
						</tr>
						<tr>
							<th scope="row"><?php esc_html_e( 'Pending Comments', 'blaminhor-essentials' ); ?></th>
							<td>
								<label>
									<input type="checkbox" name="cleanup_pending_comments" <?php checked( $this->get_setting( 'cleanup_pending_comments', false ) ); ?>>
									<?php esc_html_e( 'Remove comments awaiting moderation', 'blaminhor-essentials' ); ?>
								</label>
								<p class="description ap-desc-danger"><?php esc_html_e( 'Warning: Pending comments may be legitimate. Disabled by default.', 'blaminhor-essentials' ); ?></p>
							</td>
						</tr>
						<tr>
							<th scope="row"><?php esc_html_e( 'Orphaned User Meta', 'blaminhor-essentials' ); ?></th>
							<td>
								<label>
									<input type="checkbox" name="cleanup_orphan_usermeta" <?php checked( $this->get_setting( 'cleanup_orphan_usermeta', true ) ); ?>>
									<?php esc_html_e( 'Remove usermeta without associated users', 'blaminhor-essentials' ); ?>
								</label>
							</td>
						</tr>
						<tr>
							<th scope="row"><?php esc_html_e( 'Duplicated User Meta', 'blaminhor-essentials' ); ?></th>
							<td>
								<label>
									<input type="checkbox" name="cleanup_duplicated_usermeta" <?php checked( $this->get_setting( 'cleanup_duplicated_usermeta', true ) ); ?>>
									<?php esc_html_e( 'Remove duplicate usermeta entries', 'blaminhor-essentials' ); ?>
								</label>
							</td>
						</tr>
						<tr>
							<th scope="row"><?php esc_html_e( 'Dead Cron Events', 'blaminhor-essentials' ); ?></th>
							<td>
								<label>
									<input type="checkbox" name="cleanup_dead_cron" <?php checked( $this->get_setting( 'cleanup_dead_cron', true ) ); ?>>
									<?php esc_html_e( 'Remove scheduled events past due by more than 24 hours', 'blaminhor-essentials' ); ?>
								</label>
							</td>
						</tr>
					</table>

					<?php submit_button(); ?>
				</form>
			</div>
			</div><!-- /.blaminhor-essentials-tabs-wrapper -->
		</div>

		<!-- Backup Confirmation Modal -->
		<?php
		$backup_active = $this->is_backup_module_active();
		$backup_url    = admin_url( 'admin.php?page=blaminhor-essentials-backup' );
		$modules_url   = admin_url( 'admin.php?page=blaminhor-essentials' );
		?>
		<div id="ap-db-backup-modal" class="ap-db-modal ap-hidden">
			<div class="ap-db-modal-overlay"></div>
			<div class="ap-db-modal-content">
				<div class="ap-db-modal-header">
					<span class="dashicons dashicons-shield"></span>
					<h3><?php esc_html_e( 'Important: Back up your database first', 'blaminhor-essentials' ); ?></h3>
				</div>
				<div class="ap-db-modal-body">
					<p id="ap-db-modal-text-clean"><?php esc_html_e( 'Before cleaning your database, we strongly recommend creating a backup. This ensures you can restore your data if something goes wrong.', 'blaminhor-essentials' ); ?></p>
					<p id="ap-db-modal-text-optimize" class="ap-hidden"><?php esc_html_e( 'Before optimizing your tables, we recommend creating a backup. Table optimization rebuilds tables to reclaim disk space, and while generally safe, a backup provides peace of mind.', 'blaminhor-essentials' ); ?></p>
				</div>
				<div class="ap-db-modal-footer">
					<button type="button" class="button button-primary" id="ap-db-modal-create-backup">
						<span class="dashicons dashicons-cloud-saved ap-mt-xs"></span>
						<?php esc_html_e( 'Create Backup Now', 'blaminhor-essentials' ); ?>
					</button>
					<?php if ( $backup_active ) : ?>
						<a href="<?php echo esc_url( $backup_url ); ?>" class="button">
							<span class="dashicons dashicons-external ap-mt-xs"></span>
							<?php esc_html_e( 'Go to Backup Module', 'blaminhor-essentials' ); ?>
						</a>
					<?php else : ?>
						<a href="<?php echo esc_url( $modules_url ); ?>" class="button">
							<span class="dashicons dashicons-admin-plugins ap-mt-xs"></span>
							<?php esc_html_e( 'Activate Backup Module', 'blaminhor-essentials' ); ?>
						</a>
					<?php endif; ?>
					<button type="button" class="button button-secondary" id="ap-db-modal-proceed">
						<?php esc_html_e( 'I have a backup', 'blaminhor-essentials' ); ?>
					</button>
					<button type="button" class="button" id="ap-db-modal-cancel">
						<?php esc_html_e( 'Cancel', 'blaminhor-essentials' ); ?>
					</button>
				</div>
			</div>
		</div>
		<?php
	}
}
// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:enable WordPress.DB.DirectDatabaseQuery.NoCaching
