<?php
/**
 * Database Cleaner class.
 *
 * @package SpeedNest
 */

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

/**
 * Class WPSCO_Database_Cleaner
 *
 * Provides optional cleanup for revisions, drafts, trash, spam, and transients.
 * NEVER deletes WooCommerce orders, customers, products, users, or payments.
 */
class WPSCO_Database_Cleaner {

    /**
     * Get cleanup counts for display.
     *
     * @return array
     */
    public function get_cleanup_counts() {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to count post revisions.
        $revisions_count = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'"
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to count auto-draft posts.
        $drafts_count = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to count trashed posts excluding WooCommerce orders.
        $trash_count = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash' AND post_type NOT IN (%s, %s)",
                'shop_order',
                'shop_order_refund'
            )
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to count spam comments.
        $spam_count = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'spam'"
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to count trashed comments.
        $trashed_comments_count = (int) $wpdb->get_var(
            "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to count expired transients.
        $expired_transients_count = (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_' ) . '%',
                time()
            )
        );

        return array(
            'revisions'          => $revisions_count,
            'drafts'             => $drafts_count,
            'auto_drafts'        => $drafts_count,
            'trash'              => $trash_count,
            'trashed_posts'      => $trash_count,
            'spam'               => $spam_count + $trashed_comments_count,
            'spam_comments'      => $spam_count,
            'trashed_comments'   => $trashed_comments_count,
            'transients'         => $expired_transients_count,
            'expired_transients' => $expired_transients_count,
        );
    }

    /**
     * Clean post revisions.
     *
     * @return int Number of revisions deleted.
     */
    public function clean_post_revisions() {
        return $this->clean( 'revisions' );
    }

    /**
     * Clean auto-draft posts.
     *
     * @return int Number of drafts deleted.
     */
    public function clean_auto_drafts() {
        return $this->clean( 'drafts' );
    }

    /**
     * Clean trashed posts.
     *
     * @return int Number of posts deleted.
     */
    public function clean_trash_posts() {
        return $this->clean( 'trash' );
    }

    /**
     * Clean spam comments.
     *
     * @return int Number of comments deleted.
     */
    public function clean_spam_comments() {
        return $this->clean( 'spam' );
    }

    /**
     * Clean expired transients.
     *
     * @return int Number of transients deleted.
     */
    public function clean_expired_transients() {
        return $this->clean( 'transients' );
    }

    /**
     * Clean a specific type.
     *
     * @param string $type Cleanup type.
     * @return int Number of items deleted.
     */
    public function clean( $type ) {
        global $wpdb;

        switch ( $type ) {
            case 'revisions':
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to delete post revisions.
                return (int) $wpdb->query(
                    "DELETE FROM {$wpdb->posts} WHERE post_type = 'revision'"
                );

            case 'auto_drafts':
            case 'drafts':
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to delete auto-draft posts.
                return (int) $wpdb->query(
                    "DELETE FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
                );

            case 'trashed_posts':
            case 'trash':
                // Exclude WooCommerce orders.
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to empty trash while preserving WooCommerce orders.
                return (int) $wpdb->query(
                    $wpdb->prepare(
                        "DELETE FROM {$wpdb->posts} WHERE post_status = 'trash' AND post_type NOT IN (%s, %s, %s)",
                        'shop_order',
                        'shop_order_refund',
                        'shop_subscription'
                    )
                );

            case 'spam_comments':
            case 'spam':
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to delete spam comments.
                $count  = (int) $wpdb->query(
                    "DELETE FROM {$wpdb->comments} WHERE comment_approved = 'spam'"
                );
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to delete trashed comments.
                $count += (int) $wpdb->query(
                    "DELETE FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
                );
                return $count;

            case 'trashed_comments':
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct database query required to delete trashed comments.
                return (int) $wpdb->query(
                    "DELETE FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
                );

            case 'expired_transients':
            case 'transients':
                $purger = new WPSCO_Cache_Purger();
                $result = $purger->purge_expired_transients();
                return isset( $result['message'] ) ? $result['message'] : 0;

            default:
                return 0;
        }
    }

    /**
     * Clean all safe types.
     *
     * @return array Results.
     */
    public function clean_all() {
        $results = array();
        $types   = array( 'revisions', 'auto_drafts', 'trashed_posts', 'spam_comments', 'trashed_comments', 'expired_transients' );

        foreach ( $types as $type ) {
            $deleted = $this->clean( $type );
            $results[ $type ] = $deleted;
        }

        wpsco_log( 'Database cleanup completed.' );

        return $results;
    }
}