<?php
/**
 * Database clean-up: the WordPress clutter group.
 *
 * @package AdminPowerups
 */

namespace SmartWP\AdminPowerups\Modules\DatabaseCleanup\Definitions;

use SmartWP\AdminPowerups\Modules\DatabaseCleanup\ApiRowsCheck;
use SmartWP\AdminPowerups\Modules\DatabaseCleanup\BaseCheck;
use SmartWP\AdminPowerups\Modules\DatabaseCleanup\Check;
use SmartWP\AdminPowerups\Modules\DatabaseCleanup\Checks;
use SmartWP\AdminPowerups\Modules\DatabaseCleanup\Checks\RelationshipObjects;
use SmartWP\AdminPowerups\Modules\DatabaseCleanup\Module;
use SmartWP\AdminPowerups\Modules\DatabaseCleanup\OrphanRowsCheck;
use SmartWP\AdminPowerups\Support\DevGuard;

defined( 'ABSPATH' ) || exit;

/**
 * Builds the checks of the "WordPress clutter" group.
 *
 * @since 1.0.0
 */
final class WordPressChecks {

	/**
	 * A local date some days ago (post_date is in the site's time).
	 *
	 * @param Check $check Check (its threshold).
	 * @return string
	 */
	private static function local_cutoff( Check $check ): string {
		return (string) wp_date( 'Y-m-d H:i:s', time() - (int) $check->threshold() * DAY_IN_SECONDS );
	}

	/**
	 * Deletes a post, products and variations through WooCommerce.
	 *
	 * @since 1.0.0
	 * @param int $id Post ID.
	 * @return bool
	 */
	public static function delete_post( int $id ): bool {
		$type = get_post_type( $id );
		if ( in_array( $type, array( 'product', 'product_variation' ), true ) && function_exists( 'wc_get_product' ) ) {
			$product = wc_get_product( $id );
			if ( $product instanceof \WC_Product ) {
				return (bool) $product->delete( true );
			}
		}
		return (bool) wp_delete_post( $id, true );
	}

	/**
	 * The checks.
	 *
	 * @since 1.0.0
	 * @param Module $module Power-up (settings).
	 * @return Check[]
	 */
	public static function build( Module $module ): array {
		global $wpdb;
		$posts       = array(
			'group'         => 'wordpress',
			'object_cap'    => 'delete_others_posts',
			'tables'        => array( $wpdb->posts ),
			'sample_map'    => static fn( array $rows ): array => Helpers::post_sample( $rows, __( 'Parent ID', 'admin-powerups-for-woocommerce' ) ),
			'delete'        => static fn( int $id ): bool => (bool) wp_delete_post( $id, true ),
			'api_batch'     => 100,
			'sample_select' => '',
		);
		$order_types = Checks::order_types();
		$checks      = array();

		// Revisions (autosaves included) older than the threshold; Site Editor history kept.
		$checks[] = new ApiRowsCheck(
			array_merge(
				$posts,
				array(
					'id'            => 'wp-revisions',
					'id_column'     => 'r.ID',
					'label'         => __( 'Old revisions', 'admin-powerups-for-woocommerce' ),
					'description'   => __( 'Saved earlier versions (and autosaves) of posts, pages and products. Site Editor history (templates, styles, navigation, patterns) is kept.', 'admin-powerups-for-woocommerce' ),
					'consequence'   => __( 'Deletes those revisions for good, so you can’t restore those versions any more. The posts themselves stay.', 'admin-powerups-for-woocommerce' ),
					'risk'          => DevGuard::REVIEW,
					...Helpers::threshold( $module, 'revision_days' ),
					'min_days'      => 7,
					'from'          => static function ( Check $check ): array {
						global $wpdb;
						$types = Checks::revision_excluded_types();
						return array(
							'FROM %i r LEFT JOIN %i parent ON parent.ID = r.post_parent WHERE r.post_type = %s AND r.post_date < %s AND ( parent.ID IS NULL OR parent.post_type NOT IN (' . BaseCheck::placeholders( count( $types ), '%s' ) . ') )',
							array_merge( array( $wpdb->posts, $wpdb->posts, 'revision', self::local_cutoff( $check ) ), $types ),
						);
					},
					'delete'        => static fn( int $id ): bool => wp_delete_post_revision( $id ) instanceof \WP_Post,
					'sample_select' => 'r.ID, r.post_type, r.post_title, r.post_parent, r.post_date',
				)
			)
		);

		$checks[] = new ApiRowsCheck(
			array_merge(
				$posts,
				array(
					'id'            => 'wp-auto-drafts',
					'id_column'     => 'a.ID',
					'label'         => __( 'Old auto-drafts', 'admin-powerups-for-woocommerce' ),
					'description'   => __( 'Empty drafts WordPress creates when an editor is opened, older than 7 days (WordPress’s own rule). Orders are never included.', 'admin-powerups-for-woocommerce' ),
					'consequence'   => __( 'Deletes those auto-drafts. They were never saved by anyone.', 'admin-powerups-for-woocommerce' ),
					'risk'          => DevGuard::SAFE,
					'days'          => static fn() => 7,
					'min_days'      => 7,
					'from'          => static function ( Check $check ) use ( $order_types ): array {
						global $wpdb;
						return array(
							'FROM %i a WHERE a.post_status = %s AND a.post_date < %s AND a.post_type NOT IN (' . BaseCheck::placeholders( count( $order_types ), '%s' ) . ')',
							array_merge( array( $wpdb->posts, 'auto-draft', self::local_cutoff( $check ) ), $order_types ),
						);
					},
					'sample_select' => 'a.ID, a.post_type, a.post_title, a.post_parent, a.post_date',
				)
			)
		);

		$checks[] = new ApiRowsCheck(
			array_merge(
				$posts,
				array(
					'id'            => 'wp-trash-posts',
					'id_column'     => 't.ID',
					'label'         => __( 'Old trashed posts', 'admin-powerups-for-woocommerce' ),
					'description'   => __( 'Posts, pages and products in the trash for longer than the threshold. Orders are never included.', 'admin-powerups-for-woocommerce' ),
					'consequence'   => __( 'Empties those items from the trash for good, as WordPress would after its trash period.', 'admin-powerups-for-woocommerce' ),
					'risk'          => DevGuard::REVIEW,
					...Helpers::threshold( $module, 'trash_days', Helpers::trash_days( $module ) ),
					'min_days'      => 7,
					'api_batch'     => 25,
					'from'          => static function ( Check $check ): array {
						global $wpdb;
						$types = Checks::trash_excluded_types();
						return array(
							'FROM %i t INNER JOIN %i m ON m.post_id = t.ID AND m.meta_key = %s WHERE t.post_status = %s AND CAST(m.meta_value AS UNSIGNED) < %d AND t.post_type NOT IN (' . BaseCheck::placeholders( count( $types ), '%s' ) . ')',
							array_merge( array( $wpdb->posts, $wpdb->postmeta, '_wp_trash_meta_time', 'trash', time() - (int) $check->threshold() * DAY_IN_SECONDS ), $types ),
						);
					},
					'delete'        => array( self::class, 'delete_post' ),
					'sample_select' => 't.ID, t.post_type, t.post_title, t.post_parent, t.post_date',
				)
			)
		);

		$checks[] = self::comments( $module, 'spam' );
		$checks[] = self::comments( $module, 'trash' );

		$checks[] = new OrphanRowsCheck(
			array(
				'id'          => 'wp-orphan-postmeta',
				'label'       => __( 'Post meta without a post', 'admin-powerups-for-woocommerce' ),
				'description' => __( 'Custom fields of posts, products and legacy orders that no longer exist. Rows not attached to any post (ID 0) are left alone.', 'admin-powerups-for-woocommerce' ),
				'consequence' => __( 'Deletes those rows. They belong to nothing, so nothing shows them.', 'admin-powerups-for-woocommerce' ),
				'risk'        => DevGuard::SAFE,
				'object_cap'  => 'delete_others_posts',
				'slow'        => true,
				'targets'     => array(
					array(
						'table' => $wpdb->postmeta,
						'key'   => 'meta_id',
						'fk'    => 'post_id',
						'where' => 'positive',
					),
				),
				'parents'     => array(
					array(
						'table'  => $wpdb->posts,
						'column' => 'ID',
					),
				),
				'forget'      => Helpers::forget_group( 'post_meta' ),
				'sample'      => Helpers::meta_sample( 'meta_id', 'post_id', __( 'Missing post ID', 'admin-powerups-for-woocommerce' ) ),
			)
		);
		$checks[] = new OrphanRowsCheck(
			array(
				'id'          => 'wp-orphan-commentmeta',
				'label'       => __( 'Comment meta without a comment', 'admin-powerups-for-woocommerce' ),
				'description' => __( 'Extra data of comments, reviews and order notes that no longer exist.', 'admin-powerups-for-woocommerce' ),
				'consequence' => __( 'Deletes those rows. They belong to nothing, so nothing shows them.', 'admin-powerups-for-woocommerce' ),
				'risk'        => DevGuard::SAFE,
				'object_cap'  => 'moderate_comments',
				'slow'        => true,
				'targets'     => array(
					array(
						'table' => $wpdb->commentmeta,
						'key'   => 'meta_id',
						'fk'    => 'comment_id',
						'where' => 'positive',
					),
				),
				'parents'     => array(
					array(
						'table'  => $wpdb->comments,
						'column' => 'comment_ID',
					),
				),
				'forget'      => Helpers::forget_group( 'comment_meta' ),
				'sample'      => Helpers::meta_sample( 'meta_id', 'comment_id', __( 'Missing comment ID', 'admin-powerups-for-woocommerce' ) ),
			)
		);
		$checks[] = new OrphanRowsCheck(
			array(
				'id'          => 'wp-orphan-termmeta',
				'label'       => __( 'Term meta without a term', 'admin-powerups-for-woocommerce' ),
				'description' => __( 'Extra data of categories, tags and attributes that no longer exist.', 'admin-powerups-for-woocommerce' ),
				'consequence' => __( 'Deletes those rows. They belong to nothing, so nothing shows them.', 'admin-powerups-for-woocommerce' ),
				'risk'        => DevGuard::SAFE,
				'slow'        => true,
				'targets'     => array(
					array(
						'table' => $wpdb->termmeta,
						'key'   => 'meta_id',
						'fk'    => 'term_id',
						'where' => 'positive',
					),
				),
				'parents'     => array(
					array(
						'table'  => $wpdb->terms,
						'column' => 'term_id',
					),
				),
				'forget'      => Helpers::forget_group( 'term_meta' ),
				'sample'      => Helpers::meta_sample( 'meta_id', 'term_id', __( 'Missing term ID', 'admin-powerups-for-woocommerce' ) ),
			)
		);
		$checks[] = new OrphanRowsCheck(
			array(
				'id'          => 'wp-orphan-relationships',
				'label'       => __( 'Term links to missing terms', 'admin-powerups-for-woocommerce' ),
				'description' => __( 'Links between posts (or other objects) and categories, tags or attributes that no longer exist.', 'admin-powerups-for-woocommerce' ),
				'consequence' => __( 'Deletes those links. The terms are gone, so nothing shows them.', 'admin-powerups-for-woocommerce' ),
				'risk'        => DevGuard::SAFE,
				'targets'     => array(
					array(
						'table' => $wpdb->term_relationships,
						'key'   => 'object_id',
						'fk'    => 'term_taxonomy_id',
					),
				),
				'parents'     => array(
					array(
						'table'  => $wpdb->term_taxonomy,
						'column' => 'term_taxonomy_id',
					),
				),
				'forget'      => static function (): void {
					wp_cache_set_terms_last_changed();
				},
				'sample'      => array(
					'columns' => array(
						'object_id'        => __( 'Object ID', 'admin-powerups-for-woocommerce' ),
						'term_taxonomy_id' => __( 'Missing term taxonomy ID', 'admin-powerups-for-woocommerce' ),
					),
				),
			)
		);
		$checks[] = new RelationshipObjects(
			array(
				'id'          => 'wp-orphan-relationship-objects',
				'label'       => __( 'Term links to missing posts', 'admin-powerups-for-woocommerce' ),
				'description' => __( 'Category, tag and attribute links of posts and products that no longer exist. Only taxonomies used by post types alone are checked; user taxonomies such as Customer tags are skipped.', 'admin-powerups-for-woocommerce' ),
				'consequence' => __( 'Deletes those links and recounts the terms, which can change the counts shown for categories and tags.', 'admin-powerups-for-woocommerce' ),
				'risk'        => DevGuard::REVIEW,
				'object_cap'  => 'delete_others_posts',
				'tables'      => array( $wpdb->term_relationships, $wpdb->term_taxonomy, $wpdb->posts ),
			)
		);
		$checks[] = new OrphanRowsCheck(
			array(
				'id'          => 'wp-orphan-usermeta',
				'label'       => __( 'User meta without a user', 'admin-powerups-for-woocommerce' ),
				'description' => is_multisite()
					? __( 'Profile data of user accounts that no longer exist. User data is shared by the whole network, so this runs from the main site only.', 'admin-powerups-for-woocommerce' )
					: __( 'Profile data of user accounts that no longer exist.', 'admin-powerups-for-woocommerce' ),
				'consequence' => __( 'Deletes those rows. The accounts are gone, so nothing shows them.', 'admin-powerups-for-woocommerce' ),
				'risk'        => is_multisite() ? DevGuard::ADVANCED : DevGuard::REVIEW,
				'backup'      => is_multisite(),
				'slow'        => true,
				'unavailable' => static function (): string {
					return is_multisite() && ! is_main_site() ? __( 'Runs from the main site: user data is shared by the whole network.', 'admin-powerups-for-woocommerce' ) : '';
				},
				'targets'     => array(
					array(
						'table' => $wpdb->usermeta,
						'key'   => 'umeta_id',
						'fk'    => 'user_id',
						'where' => 'positive',
					),
				),
				'parents'     => array(
					array(
						'table'  => $wpdb->users,
						'column' => 'ID',
					),
				),
				'forget'      => Helpers::forget_group( 'user_meta' ),
				'sample'      => Helpers::meta_sample( 'umeta_id', 'user_id', __( 'Missing user ID', 'admin-powerups-for-woocommerce' ) ),
			)
		);
		return $checks;
	}

	/**
	 * Spam comments older than the threshold (by comment date), or trashed comments in the trash
	 * longer than it (by `_wp_trash_meta_time`, as WordPress empties its trash). Never order
	 * notes or webhook delivery logs.
	 *
	 * @param Module $module Power-up.
	 * @param string $status `spam` or `trash`.
	 * @return ApiRowsCheck
	 */
	private static function comments( Module $module, string $status ): ApiRowsCheck {
		global $wpdb;
		$spam = 'spam' === $status;
		return new ApiRowsCheck(
			array(
				'id'            => $spam ? 'wp-spam-comments' : 'wp-trash-comments',
				'id_column'     => 'c.comment_ID',
				'group'         => 'wordpress',
				'label'         => $spam ? __( 'Old spam comments', 'admin-powerups-for-woocommerce' ) : __( 'Old trashed comments', 'admin-powerups-for-woocommerce' ),
				'description'   => $spam
					? __( 'Comments and reviews marked as spam. Order notes are never included.', 'admin-powerups-for-woocommerce' )
					: __( 'Comments and reviews in the trash for longer than the threshold. Order notes are never included.', 'admin-powerups-for-woocommerce' ),
				'consequence'   => $spam
					? __( 'Deletes that spam for good.', 'admin-powerups-for-woocommerce' )
					: __( 'Empties those comments and reviews from the trash for good.', 'admin-powerups-for-woocommerce' ),
				'risk'          => $spam ? DevGuard::SAFE : DevGuard::REVIEW,
				'object_cap'    => 'moderate_comments',
				'tables'        => array( $wpdb->comments ),
				...Helpers::threshold( $module, 'comment_days' ),
				'min_days'      => 7,
				'api_batch'     => 100,
				'from'          => static function ( Check $check ) use ( $spam ): array {
					global $wpdb;
					$cutoff = time() - (int) $check->threshold() * DAY_IN_SECONDS;
					if ( $spam ) {
						return array(
							'FROM %i c WHERE c.comment_approved = %s AND c.comment_date_gmt < %s AND c.comment_type NOT IN (%s, %s)',
							array( $wpdb->comments, 'spam', gmdate( 'Y-m-d H:i:s', $cutoff ), 'order_note', 'webhook_delivery' ),
						);
					}
					return array(
						'FROM %i c INNER JOIN %i m ON m.comment_id = c.comment_ID AND m.meta_key = %s WHERE c.comment_approved = %s AND CAST(m.meta_value AS UNSIGNED) < %d AND c.comment_type NOT IN (%s, %s)',
						array( $wpdb->comments, $wpdb->commentmeta, '_wp_trash_meta_time', 'trash', $cutoff, 'order_note', 'webhook_delivery' ),
					);
				},
				'delete'        => static fn( int $id ): bool => (bool) wp_delete_comment( $id, true ),
				'sample_select' => 'c.comment_ID, c.comment_post_ID, c.comment_type, c.comment_date_gmt',
				'sample_map'    => static fn( array $rows ): array => array(
					'columns' => array(
						'id'   => __( 'Comment ID', 'admin-powerups-for-woocommerce' ),
						'post' => __( 'Post ID', 'admin-powerups-for-woocommerce' ),
						'type' => __( 'Type', 'admin-powerups-for-woocommerce' ),
						'date' => __( 'Date (UTC)', 'admin-powerups-for-woocommerce' ),
					),
					'rows'    => array_map(
						static fn( $row ) => array(
							'id'   => BaseCheck::cell( $row->comment_ID ),
							'post' => BaseCheck::cell( $row->comment_post_ID ),
							'type' => BaseCheck::cell( $row->comment_type ),
							'date' => BaseCheck::cell( $row->comment_date_gmt ),
						),
						$rows
					),
				),
			)
		);
	}
}