<?php
/**
 * Database cleanup: fewer rows = faster queries when the page
 * is not cached (logged-in users, cart, first visit).
 */

defined( 'ABSPATH' ) || exit;

class CTPS_Database {

	public static function items() {
		return array(
			'revisions'  => __( 'Post revisions', 'celero-adaptive-speed' ),
			'autodrafts' => __( 'Auto-drafts', 'celero-adaptive-speed' ),
			'trash'      => __( 'Trashed posts', 'celero-adaptive-speed' ),
			'spam'       => __( 'Spam and trashed comments', 'celero-adaptive-speed' ),
			'transients' => __( 'Expired transients', 'celero-adaptive-speed' ),
			'orphanmeta' => __( 'Orphaned metadata', 'celero-adaptive-speed' ),
		);
	}

	public static function counts() {
		global $wpdb;
		$now = time();
		return array(
			'revisions'  => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type='revision'" ), // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin-only database cleanup, works on live data
			'autodrafts' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status='auto-draft'" ),
			'trash'      => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status='trash'" ),
			'spam'       => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved IN ('spam','trash')" ),
			'transients' => (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_' ) . '%', $now ) ),
			'orphanmeta' => (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" ),
		);
	}

	public static function clean( $type ) {
		global $wpdb;
		switch ( $type ) {
			case 'revisions':
				$ids = $wpdb->get_col( "SELECT ID FROM {$wpdb->posts} WHERE post_type='revision' LIMIT 1000" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin-only database cleanup, works on live data
				foreach ( $ids as $id ) {
					wp_delete_post_revision( (int) $id );
				}
				return count( $ids );
			case 'autodrafts':
			case 'trash':
				$status = 'autodrafts' === $type ? 'auto-draft' : 'trash';
				$ids    = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_status=%s LIMIT 1000", $status ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin-only database cleanup, works on live data
				foreach ( $ids as $id ) {
					wp_delete_post( (int) $id, true );
				}
				return count( $ids );
			case 'spam':
				$ids = $wpdb->get_col( "SELECT comment_ID FROM {$wpdb->comments} WHERE comment_approved IN ('spam','trash') LIMIT 1000" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin-only database cleanup, works on live data
				foreach ( $ids as $id ) {
					wp_delete_comment( (int) $id, true );
				}
				return count( $ids );
			case 'transients':
				if ( function_exists( 'delete_expired_transients' ) ) {
					$before = self::counts()['transients'];
					delete_expired_transients( true );
					return $before;
				}
				return 0;
			case 'orphanmeta':
				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" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- admin-only database cleanup, works on live data
		}
		return 0;
	}
}