<?php
/**
 * Database optimizer.
 *
 * Performs database cleanup actions such as removing revisions, drafts, and spam.
 *
 * @package MonitorCat\Modules\Speed\Optimizer
 */

declare(strict_types=1);

namespace MonitorCat\Modules\Speed\Optimizer;

defined( 'ABSPATH' ) || exit;

/**
 * Handles database cleanup and optimization operations.
 */
final class DatabaseOptimizer {

	/**
	 * Clean post revisions.
	 *
	 * @return array{count: int, message: string}
	 */
	public function clean_revisions(): array {
		global $wpdb;

		// First delete related meta data for revisions.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				"DELETE pm FROM {$wpdb->postmeta} pm
				INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
				WHERE p.post_type = %s",
				'revision'
			)
		);

		// Then delete the revisions themselves.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->posts} WHERE post_type = %s",
				'revision'
			)
		);

		return array(
			'count'   => $count,
			/* translators: %d: number of deleted revisions */
			'message' => sprintf( __( 'Deleted %d post revision(s).', 'monitor-cat' ), $count ),
		);
	}

	/**
	 * Clean auto-draft posts.
	 *
	 * @return array{count: int, message: string}
	 */
	public function clean_auto_drafts(): array {
		global $wpdb;

		// Delete related meta data first.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				"DELETE pm FROM {$wpdb->postmeta} pm
				INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
				WHERE p.post_status = %s",
				'auto-draft'
			)
		);

		// Delete the auto-drafts.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->posts} WHERE post_status = %s",
				'auto-draft'
			)
		);

		return array(
			'count'   => $count,
			/* translators: %d: number of deleted auto-drafts */
			'message' => sprintf( __( 'Deleted %d auto-draft(s).', 'monitor-cat' ), $count ),
		);
	}

	/**
	 * Clean trashed posts and their meta data.
	 *
	 * @return array{count: int, message: string}
	 */
	public function clean_trashed_posts(): array {
		global $wpdb;

		// Delete related meta data first.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$wpdb->query(
			$wpdb->prepare(
				"DELETE pm FROM {$wpdb->postmeta} pm
				INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
				WHERE p.post_status = %s",
				'trash'
			)
		);

		// Delete the trashed posts.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->query(
			$wpdb->prepare(
				"DELETE FROM {$wpdb->posts} WHERE post_status = %s",
				'trash'
			)
		);

		return array(
			'count'   => $count,
			/* translators: %d: number of deleted trashed posts */
			'message' => sprintf( __( 'Deleted %d trashed post(s) and their meta data.', 'monitor-cat' ), $count ),
		);
	}

	/**
	 * Maximum comments to delete per batch to avoid timeouts.
	 *
	 * @var int
	 */
	private const COMMENT_BATCH_LIMIT = 500;

	/**
	 * Clean spam comments using wp_delete_comment to respect hooks.
	 *
	 * Processes up to COMMENT_BATCH_LIMIT comments per call to avoid timeouts.
	 *
	 * @return array{count: int, message: string}
	 */
	public function clean_spam_comments(): array {
		$comments = get_comments(
			array(
				'status' => 'spam',
				'number' => self::COMMENT_BATCH_LIMIT,
				'fields' => 'ids',
			)
		);

		$count = 0;

		foreach ( $comments as $comment_id ) {
			if ( wp_delete_comment( (int) $comment_id, true ) ) {
				++$count;
			}
		}

		return array(
			'count'   => $count,
			/* translators: %d: number of deleted spam comments */
			'message' => sprintf( __( 'Deleted %d spam comment(s).', 'monitor-cat' ), $count ),
		);
	}

	/**
	 * Clean trashed comments using wp_delete_comment to respect hooks.
	 *
	 * Processes up to COMMENT_BATCH_LIMIT comments per call to avoid timeouts.
	 *
	 * @return array{count: int, message: string}
	 */
	public function clean_trashed_comments(): array {
		$comments = get_comments(
			array(
				'status' => 'trash',
				'number' => self::COMMENT_BATCH_LIMIT,
				'fields' => 'ids',
			)
		);

		$count = 0;

		foreach ( $comments as $comment_id ) {
			if ( wp_delete_comment( (int) $comment_id, true ) ) {
				++$count;
			}
		}

		return array(
			'count'   => $count,
			/* translators: %d: number of deleted trashed comments */
			'message' => sprintf( __( 'Deleted %d trashed comment(s).', 'monitor-cat' ), $count ),
		);
	}

	/**
	 * Clean expired transients.
	 *
	 * Uses the WordPress core function available since 4.9.
	 *
	 * @return array{count: int, message: string}
	 */
	public function clean_expired_transients(): array {
		global $wpdb;

		// Count expired transients before cleanup.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value < %d",
				$wpdb->esc_like( '_transient_timeout_' ) . '%',
				time()
			)
		);

		// Use the WordPress core function for cleanup.
		delete_expired_transients( true );

		return array(
			'count'   => $count,
			/* translators: %d: number of deleted expired transients */
			'message' => sprintf( __( 'Cleaned up %d expired transient(s).', 'monitor-cat' ), $count ),
		);
	}

	/**
	 * Optimize WordPress database tables.
	 *
	 * @return array{count: int, message: string}
	 */
	public function optimize_tables(): array {
		global $wpdb;

		$tables = array(
			$wpdb->posts,
			$wpdb->postmeta,
			$wpdb->comments,
			$wpdb->commentmeta,
			$wpdb->options,
			$wpdb->terms,
			$wpdb->term_taxonomy,
			$wpdb->term_relationships,
			$wpdb->termmeta,
			$wpdb->usermeta,
		);

		$optimized    = 0;
		$known_tables = $wpdb->tables( 'all' );

		foreach ( $tables as $table ) {
			if ( ! in_array( $table, $known_tables, true ) ) {
				continue;
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$result = $wpdb->query( 'OPTIMIZE TABLE `' . esc_sql( $table ) . '`' );
			if ( false !== $result ) {
				++$optimized;
			}
		}

		return array(
			'count'   => $optimized,
			/* translators: %d: number of optimized tables */
			'message' => sprintf( __( 'Optimized %d database table(s).', 'monitor-cat' ), $optimized ),
		);
	}
}
