<?php
/**
 * Database cleanup. Only removes data WordPress itself considers disposable,
 * and always goes through core functions where one exists so related meta,
 * caches and hooks are handled correctly.
 */

defined( 'ABSPATH' ) || exit;

class GSSO_Database {

	const HOOK = 'gsso_db_cleanup';

	public static function init() {
		add_filter( 'cron_schedules', array( __CLASS__, 'schedules' ) );
		add_action( self::HOOK, array( __CLASS__, 'scheduled_run' ) );
	}

	public static function schedules( $schedules ) {
		if ( ! isset( $schedules['gsso_monthly'] ) ) {
			$schedules['gsso_monthly'] = array(
				'interval' => 30 * DAY_IN_SECONDS,
				'display'  => 'Once a month (GlobeSign)',
			);
		}
		if ( ! isset( $schedules['weekly'] ) ) {
			$schedules['weekly'] = array(
				'interval' => WEEK_IN_SECONDS,
				'display'  => 'Once a week',
			);
		}
		return $schedules;
	}

	public static function reschedule() {
		wp_clear_scheduled_hook( self::HOOK );
		$map  = array(
			'daily'   => 'daily',
			'weekly'  => 'weekly',
			'monthly' => 'gsso_monthly',
		);
		$mode = GSSO_Settings::get( 'db_schedule' );
		if ( isset( $map[ $mode ] ) ) {
			wp_schedule_event( time() + HOUR_IN_SECONDS, $map[ $mode ], self::HOOK );
		}
	}

	/** The items that can be cleaned, with labels. */
	public static function items() {
		return array(
			'db_revisions'        => 'Post revisions',
			'db_autodrafts'       => 'Auto-drafts',
			'db_trashed_posts'    => 'Trashed posts',
			'db_spam_comments'    => 'Spam comments',
			'db_trashed_comments' => 'Trashed comments',
			'db_transients'       => 'Expired transients',
			'db_optimize_tables'  => 'Optimize tables',
		);
	}

	public static function counts() {
		global $wpdb;
		$now = time();
		// phpcs:disable WordPress.DB.DirectDatabaseQuery
		return array(
			'db_revisions'        => (int) $wpdb->get_var( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_type = 'revision'" ),
			'db_autodrafts'       => (int) $wpdb->get_var( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'" ),
			'db_trashed_posts'    => (int) $wpdb->get_var( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_status = 'trash'" ),
			'db_spam_comments'    => (int) $wpdb->get_var( "SELECT COUNT(comment_ID) FROM {$wpdb->comments} WHERE comment_approved = 'spam'" ),
			'db_trashed_comments' => (int) $wpdb->get_var( "SELECT COUNT(comment_ID) FROM {$wpdb->comments} WHERE comment_approved = 'trash'" ),
			'db_transients'       => (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(option_id) FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value < %d", $wpdb->esc_like( '_transient_timeout_' ) . '%', $now ) ),
			'db_optimize_tables'  => count( self::tables() ),
		);
		// phpcs:enable
	}

	private static function tables() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$tables = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $wpdb->prefix ) . '%' ) );
		return is_array( $tables ) ? $tables : array();
	}

	public static function scheduled_run() {
		$items = array();
		foreach ( array_keys( self::items() ) as $key ) {
			if ( GSSO_Settings::get( $key ) ) {
				$items[] = $key;
			}
		}
		self::clean( $items );
	}

	/**
	 * @param string[] $items Keys from items().
	 * @return array<string,int> Number removed per item.
	 */
	public static function clean( array $items ) {
		global $wpdb;
		$done  = array();
		$start = time();
		$limit = 30; // seconds; stop early on huge databases and finish next run.

		// phpcs:disable WordPress.DB.DirectDatabaseQuery
		foreach ( $items as $item ) {
			$count = 0;
			switch ( $item ) {
				case 'db_revisions':
					$ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'revision' LIMIT 2000" );
					foreach ( $ids as $id ) {
						if ( wp_delete_post_revision( (int) $id ) ) {
							$count++;
						}
						if ( time() - $start > $limit ) {
							break;
						}
					}
					break;

				case 'db_autodrafts':
					$ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->posts} WHERE post_status = 'auto-draft' LIMIT 2000" );
					foreach ( $ids as $id ) {
						if ( wp_delete_post( (int) $id, true ) ) {
							$count++;
						}
						if ( time() - $start > $limit ) {
							break;
						}
					}
					break;

				case 'db_trashed_posts':
					$ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->posts} WHERE post_status = 'trash' LIMIT 2000" );
					foreach ( $ids as $id ) {
						if ( wp_delete_post( (int) $id, true ) ) {
							$count++;
						}
						if ( time() - $start > $limit ) {
							break;
						}
					}
					break;

				case 'db_spam_comments':
				case 'db_trashed_comments':
					$status = 'db_spam_comments' === $item ? 'spam' : 'trash';
					$ids    = $wpdb->get_col( $wpdb->prepare( "SELECT comment_ID FROM {$wpdb->comments} WHERE comment_approved = %s LIMIT 5000", $status ) );
					foreach ( $ids as $id ) {
						if ( wp_delete_comment( (int) $id, true ) ) {
							$count++;
						}
						if ( time() - $start > $limit ) {
							break;
						}
					}
					break;

				case 'db_transients':
					$before = self::counts()['db_transients'];
					delete_expired_transients( true );
					$count = $before;
					break;

				case 'db_optimize_tables':
					foreach ( self::tables() as $table ) {
						$wpdb->query( 'OPTIMIZE TABLE `' . esc_sql( $table ) . '`' );
						$count++;
					}
					break;
			}
			$done[ $item ] = $count;
		}
		// phpcs:enable

		GSSO_Logger::log( 'Database cleanup: ' . wp_json_encode( $done ) );
		return $done;
	}
}