<?php
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}
// phpcs:disable WordPress.WP.AlternativeFunctions -- Cache plugin: direct file operations are limited to wp-content/cache/velox, its own config/drop-in and the site .htaccess; WP_Filesystem is unsuitable for per-request cache writes.

/**
 * Database cleanup tools.
 */
class Velox_Database {

	public static function labels() {
		return array(
			'revisions'   => __( 'Post revisions', 'angelforce-cache' ),
			'auto_drafts' => __( 'Auto drafts', 'angelforce-cache' ),
			'trashed'     => __( 'Trashed posts', 'angelforce-cache' ),
			'spam'        => __( 'Spam comments', 'angelforce-cache' ),
			'trash_comm'  => __( 'Trashed comments', 'angelforce-cache' ),
			'transients'  => __( 'Expired transients', 'angelforce-cache' ),
			'orphan_meta' => __( 'Orphaned post meta', 'angelforce-cache' ),
		);
	}

	public static function counts() {
		global $wpdb;
		$old                = gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );
		$out                = array();
		$out['revisions']   = (int) $wpdb->get_var( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_type = 'revision'" );
		$out['auto_drafts'] = (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_status = 'auto-draft' AND post_modified_gmt < %s", $old ) );
		$out['trashed']     = (int) $wpdb->get_var( "SELECT COUNT(ID) FROM {$wpdb->posts} WHERE post_status = 'trash'" );
		$out['spam']        = (int) $wpdb->get_var( "SELECT COUNT(comment_ID) FROM {$wpdb->comments} WHERE comment_approved = 'spam'" );
		$out['trash_comm']  = (int) $wpdb->get_var( "SELECT COUNT(comment_ID) FROM {$wpdb->comments} WHERE comment_approved IN ('trash','post-trashed')" );
		$out['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_' ) . '%',
				time()
			)
		);
		$out['orphan_meta'] = (int) $wpdb->get_var( "SELECT COUNT(pm.meta_id) FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL" );
		return $out;
	}

	/** Deletes posts through the WordPress API (meta, terms, caches handled), in batches. */
	private static function delete_posts( $where, $args = array() ) {
		global $wpdb;
		$done = 0;
		for ( $i = 0; $i < 20; $i++ ) {
			$ids = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE {$where} LIMIT %d", array_merge( $args, array( 200 ) ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL
			if ( empty( $ids ) ) {
				break;
			}
			foreach ( $ids as $id ) {
				wp_delete_post( (int) $id, true );
				++$done;
			}
		}
		return $done;
	}

	private static function delete_comments( $where ) {
		global $wpdb;
		$done = 0;
		for ( $i = 0; $i < 20; $i++ ) {
			$ids = $wpdb->get_col( "SELECT comment_ID FROM {$wpdb->comments} WHERE {$where} LIMIT 200" ); // phpcs:ignore WordPress.DB.PreparedSQL
			if ( empty( $ids ) ) {
				break;
			}
			foreach ( $ids as $id ) {
				wp_delete_comment( (int) $id, true );
				++$done;
			}
		}
		return $done;
	}

	public static function clean( $item ) {
		global $wpdb;
		$old = gmdate( 'Y-m-d H:i:s', time() - DAY_IN_SECONDS );
		switch ( $item ) {
			case 'revisions':
				self::delete_posts( "post_type = 'revision'" );
				break;
			case 'auto_drafts':
				self::delete_posts( "post_status = 'auto-draft' AND post_modified_gmt < %s", array( $old ) );
				break;
			case 'trashed':
				self::delete_posts( "post_status = 'trash'" );
				break;
			case 'spam':
				self::delete_comments( "comment_approved = 'spam'" );
				break;
			case 'trash_comm':
				self::delete_comments( "comment_approved IN ('trash','post-trashed')" );
				break;
			case 'transients':
				// Portable: find expired timeout rows, then delete both rows through the API.
				$names = $wpdb->get_col(
					$wpdb->prepare(
						"SELECT option_name FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value < %d LIMIT 2000",
						$wpdb->esc_like( '_transient_timeout_' ) . '%',
						time()
					)
				);
				foreach ( (array) $names as $name ) {
					delete_transient( substr( $name, strlen( '_transient_timeout_' ) ) );
				}
				if ( function_exists( 'delete_expired_transients' ) ) {
					delete_expired_transients( true );
				}
				break;
			case 'orphan_meta':
				$wpdb->query( "DELETE FROM {$wpdb->postmeta} WHERE post_id NOT IN (SELECT ID FROM {$wpdb->posts})" );
				wp_cache_flush();
				break;
			case 'optimize':
				self::optimize_tables();
				break;
			case 'all':
				foreach ( array_keys( self::labels() ) as $k ) {
					self::clean( $k );
				}
				break;
		}
	}

	public static function optimize_tables() {
		global $wpdb;
		if ( defined( 'DB_ENGINE' ) && 'sqlite' === DB_ENGINE ) {
			return; // OPTIMIZE TABLE is MySQL-only.
		}
		$tables = $wpdb->get_col( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $wpdb->prefix ) . '%' ) );
		foreach ( (array) $tables as $t ) {
			$t = preg_replace( '/[^A-Za-z0-9_]/', '', $t );
			if ( $t ) {
				$wpdb->query( "OPTIMIZE TABLE `{$t}`" ); // phpcs:ignore WordPress.DB.PreparedSQL
			}
		}
	}

	public static function run_scheduled() {
		self::clean( 'all' );
	}

	public static function reschedule() {
		wp_clear_scheduled_hook( 'velox_db_cleanup' );
		$mode = Velox_Settings::get( 'db_schedule' );
		if ( 'weekly' === $mode ) {
			wp_schedule_event( time() + DAY_IN_SECONDS, 'weekly', 'velox_db_cleanup' );
		} elseif ( 'monthly' === $mode ) {
			wp_schedule_event( time() + DAY_IN_SECONDS, 'velox_monthly', 'velox_db_cleanup' );
		}
	}
}