<?php
/**
 * BaseCloud Boost — Database Optimizer
 *
 * @package BaseCloud\Boost\Database
 */

declare( strict_types=1 );

namespace BaseCloud\Boost\Database;

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

/**
 * Provides database cleanup and optimization routines.
 */
class DB_Optimizer {

	/**
	 * Remove all post revisions.
	 *
	 * @return int Number of revisions deleted.
	 */
	public function clean_post_revisions(): int {
		global $wpdb;

		$ids = $wpdb->get_col(
			"SELECT ID FROM {$wpdb->posts} WHERE post_type = 'revision'"
		);

		$deleted = 0;
		foreach ( $ids as $id ) {
			if ( wp_delete_post_revision( (int) $id ) ) {
				$deleted++;
			}
		}

		return $deleted;
	}

	/**
	 * Remove all auto-draft posts.
	 *
	 * @return int Number deleted.
	 */
	public function clean_auto_drafts(): int {
		global $wpdb;

		return (int) $wpdb->query(
			"DELETE FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
		);
	}

	/**
	 * Remove all posts in the trash.
	 *
	 * @return int Number deleted.
	 */
	public function clean_trashed_posts(): int {
		global $wpdb;

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

		$deleted = 0;
		foreach ( $ids as $id ) {
			if ( wp_delete_post( (int) $id, true ) ) {
				$deleted++;
			}
		}

		return $deleted;
	}

	/**
	 * Remove all spam comments.
	 *
	 * @return int Number deleted.
	 */
	public function clean_spam_comments(): int {
		global $wpdb;

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

		$deleted = 0;
		foreach ( $ids as $id ) {
			if ( wp_delete_comment( (int) $id, true ) ) {
				$deleted++;
			}
		}

		return $deleted;
	}

	/**
	 * Remove all expired transients.
	 *
	 * @return int Number deleted.
	 */
	public function clean_expired_transients(): int {
		global $wpdb;

		$time    = time();
		$deleted = 0;

		// Get all expired transient timeout keys.
		$timeouts = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT option_name FROM {$wpdb->options}
				WHERE option_name LIKE %s AND option_value < %d",
				'_transient_timeout_%',
				$time
			)
		);

		foreach ( $timeouts as $timeout_key ) {
			$name = str_replace( '_transient_timeout_', '', $timeout_key );
			delete_transient( $name );
			$deleted++;
		}

		// Site transients (multisite).
		if ( is_multisite() ) {
			$site_timeouts = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT meta_key FROM {$wpdb->sitemeta}
					WHERE meta_key LIKE %s AND meta_value < %d",
					'_site_transient_timeout_%',
					$time
				)
			);

			foreach ( $site_timeouts as $timeout_key ) {
				$name = str_replace( '_site_transient_timeout_', '', $timeout_key );
				delete_site_transient( $name );
				$deleted++;
			}
		}

		return $deleted;
	}

	/**
	 * Remove orphaned postmeta rows (where the post no longer exists).
	 *
	 * @return int Number of rows deleted.
	 */
	public function clean_orphaned_postmeta(): int {
		global $wpdb;

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

	/**
	 * Run OPTIMIZE TABLE on all WordPress database tables.
	 *
	 * @return string[] List of tables that were optimized.
	 */
	public function optimize_tables(): array {
		global $wpdb;

		$tables = $wpdb->get_col( "SHOW TABLES LIKE '{$wpdb->prefix}%'" );
		$optimized = [];

		foreach ( $tables as $table ) {
			$result = $wpdb->query( "OPTIMIZE TABLE `{$table}`" );
			if ( $result !== false ) {
				$optimized[] = $table;
			}
		}

		return $optimized;
	}

	/**
	 * Run the unattended-safe cleanup subset for the scheduled cron.
	 *
	 * Deliberately excludes destructive/heavy operations that should stay manual:
	 * trashed posts (user may want to restore them) and OPTIMIZE TABLE (can lock
	 * large tables). Hooked to BCBOOST_db_cleanup.
	 *
	 * @return array<string, int>
	 */
	public function scheduled_cleanup(): array {
		return [
			'revisions'  => $this->clean_post_revisions(),
			'auto_drafts' => $this->clean_auto_drafts(),
			'spam'       => $this->clean_spam_comments(),
			'transients' => $this->clean_expired_transients(),
			'postmeta'   => $this->clean_orphaned_postmeta(),
		];
	}

	/**
	 * (Re)schedule — or cancel — the automatic cleanup cron to match the
	 * db_cleanup_schedule option. Safe to call repeatedly (idempotent).
	 *
	 * @return void
	 */
	public static function reschedule(): void {
		$hook     = 'BCBOOST_db_cleanup';
		$schedule = (string) BCBOOST_get_option( 'db_cleanup_schedule', 'off' );

		// Cancel when disabled or set to an unsupported recurrence.
		if ( ! in_array( $schedule, [ 'daily', 'weekly' ], true ) ) {
			$ts = wp_next_scheduled( $hook );
			if ( $ts ) {
				wp_unschedule_event( $ts, $hook );
			}
			return;
		}

		// If already scheduled with a different recurrence, clear it first.
		$current = function_exists( 'wp_get_scheduled_event' ) ? wp_get_scheduled_event( $hook ) : false;
		if ( $current && $current->schedule !== $schedule ) {
			wp_unschedule_event( $current->timestamp, $hook );
			$current = false;
		}

		if ( ! $current && ! wp_next_scheduled( $hook ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, $schedule, $hook );
		}
	}

	/**
	 * Run all cleanup operations and return counts.
	 *
	 * @return array<string, int|string[]>
	 */
	public function run_all_cleanup(): array {
		return [
			'revisions'    => $this->clean_post_revisions(),
			'auto_drafts'  => $this->clean_auto_drafts(),
			'trash'        => $this->clean_trashed_posts(),
			'spam'         => $this->clean_spam_comments(),
			'transients'   => $this->clean_expired_transients(),
			'postmeta'     => $this->clean_orphaned_postmeta(),
			'tables'       => $this->optimize_tables(),
		];
	}
}
