<?php
/**
 * DB Cleanup - provides AJAX handlers for database optimization.
 *
 * @package Asmith_Speed_Optimizer
 */

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

class Asmith_SO_DB_Cleanup {

	private static $instance = null;

	public static function instance() {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	private function __construct() {
		add_action( 'wp_ajax_asmith_so_db_counts', array( $this, 'ajax_get_counts' ) );
		add_action( 'wp_ajax_asmith_so_db_cleanup', array( $this, 'ajax_cleanup' ) );
	}

	/**
	 * AJAX: Get counts for all cleanup types.
	 */
	public function ajax_get_counts() {
		check_ajax_referer( 'asmith_so_nonce', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( __( 'Unauthorized.', 'a-smith-speed-optimizer' ) );
		}

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

	/**
	 * AJAX: Clean up a specific type of database items.
	 */
	public function ajax_cleanup() {
		check_ajax_referer( 'asmith_so_nonce', 'nonce' );

		if ( ! current_user_can( 'manage_options' ) ) {
			wp_send_json_error( __( 'Unauthorized.', 'a-smith-speed-optimizer' ) );
		}

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

		$valid_types = array( 'revisions', 'auto_drafts', 'trash_posts', 'transients', 'spam_comments', 'trash_comments' );
		if ( ! in_array( $type, $valid_types, true ) ) {
			wp_send_json_error( __( 'Invalid cleanup type.', 'a-smith-speed-optimizer' ) );
		}

		$deleted = $this->cleanup_type( $type );

		wp_send_json_success( array(
			'type'    => $type,
			'deleted' => $deleted,
			'counts'  => $this->get_all_counts(),
		) );
	}

	/**
	 * Get counts for all cleanup item types.
	 *
	 * @return array
	 */
	public function get_all_counts() {
		global $wpdb;

		$counts = array();

		// Revisions.
		$counts['revisions'] = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Performance counting query, caching not beneficial.
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'"
		);

		// Auto-drafts.
		$counts['auto_drafts'] = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Performance counting query, caching not beneficial.
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
		);

		// Trash posts.
		$counts['trash_posts'] = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Performance counting query, caching not beneficial.
			"SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'"
		);

		// Expired transients.
		$counts['transients'] = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Performance counting query, caching not beneficial.
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value < %d",
				$wpdb->esc_like( '_transient_timeout_' ) . '%',
				time()
			)
		);

		// Spam comments.
		$counts['spam_comments'] = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Performance counting query, caching not beneficial.
			"SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'spam'"
		);

		// Trash comments.
		$counts['trash_comments'] = (int) $wpdb->get_var( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Performance counting query, caching not beneficial.
			"SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
		);

		// Total.
		$counts['total'] = array_sum( $counts );

		return $counts;
	}

	/**
	 * Clean up a specific type of database items.
	 *
	 * @param string $type The cleanup type.
	 * @return int Number of items deleted.
	 */
	private function cleanup_type( $type ) {
		global $wpdb;

		$deleted = 0;

		switch ( $type ) {
			case 'revisions':
				$deleted = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cleanup operation requires direct query.
					"DELETE FROM {$wpdb->posts} WHERE post_type = 'revision'"
				);
				break;

			case 'auto_drafts':
				$deleted = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cleanup operation requires direct query.
					"DELETE FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
				);
				break;

			case 'trash_posts':
				$deleted = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cleanup operation requires direct query.
					"DELETE FROM {$wpdb->posts} WHERE post_status = 'trash'"
				);
				break;

			case 'transients':
				$deleted = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cleanup operation requires direct query.
					$wpdb->prepare(
						"DELETE a, b FROM {$wpdb->options} a
						INNER JOIN {$wpdb->options} b ON b.option_name = REPLACE(a.option_name, '_transient_timeout_', '_transient_')
						WHERE a.option_name LIKE %s AND a.option_value < %d",
						$wpdb->esc_like( '_transient_timeout_' ) . '%',
						time()
					)
				);
				break;

			case 'spam_comments':
				$deleted = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cleanup operation requires direct query.
					"DELETE FROM {$wpdb->comments} WHERE comment_approved = 'spam'"
				);
				break;

			case 'trash_comments':
				$deleted = (int) $wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cleanup operation requires direct query.
					"DELETE FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
				);
				break;
		}

		return $deleted;
	}
}