<?php
// If this file is called directly, abort.
if ( ! defined( 'WPINC' ) ) die;

class AIO_DB_Cleaner {

    public function __construct() {
        // Admin ajax hooks
        add_action( 'wp_ajax_aio_get_db_stats', [$this, 'get_stats'] );
        add_action( 'wp_ajax_aio_clean_db', [$this, 'clean_db'] );

        // Cron Auto-Cleaner
        $settings = get_option('aio_options', []);
        if ( !empty($settings['optEnableAutoCleaner']) ) {
            add_action('init', [$this, 'schedule_auto_cleaner']);
            add_action('aio_weekly_db_cleanup', [$this, 'process_auto_cleaner']);
        } else {
            // Unschedule if disabled
            $timestamp = wp_next_scheduled('aio_weekly_db_cleanup');
            if ($timestamp) {
                wp_unschedule_event($timestamp, 'aio_weekly_db_cleanup');
            }
        }
    }

    public function get_stats() {
        check_ajax_referer('aio-settings-nonce', 'security');
        if (!current_user_can('manage_options')) {
            wp_send_json_error('Unauthorized');
        }

        global $wpdb;

        // Revisions
        $revisions = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts WHERE post_type = 'revision'");
        
        // Auto Drafts
        $auto_drafts = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->posts WHERE post_status = 'auto-draft'");

        // Spam Comments
        $spam_comments = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->comments WHERE comment_approved = 'spam'");

        // Trashed Comments
        $trashed_comments = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->comments WHERE comment_approved = 'trash'");

        // Expired Transients
        $expired_transients = $wpdb->get_var("SELECT COUNT(*) FROM $wpdb->options WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP()");

        wp_send_json_success([
            'revisions' => (int)$revisions,
            'auto_drafts' => (int)$auto_drafts,
            'spam_comments' => (int)$spam_comments,
            'trashed_comments' => (int)$trashed_comments,
            'expired_transients' => (int)$expired_transients,
            'total' => (int)$revisions + (int)$auto_drafts + (int)$spam_comments + (int)$trashed_comments + (int)$expired_transients
        ]);
    }

    public function clean_db() {
        check_ajax_referer('aio-settings-nonce', 'security');
        if (!current_user_can('manage_options')) {
            wp_send_json_error('Unauthorized');
        }

        $type = isset($_POST['type']) ? sanitize_text_field($_POST['type']) : '';
        if (empty($type)) {
            wp_send_json_error('Invalid type');
        }

        global $wpdb;
        $deleted = 0;

        switch ($type) {
            case 'revisions':
                $wpdb->query("DELETE FROM $wpdb->posts WHERE post_type = 'revision'");
                $wpdb->query("DELETE FROM $wpdb->postmeta WHERE post_id NOT IN (SELECT ID FROM $wpdb->posts)");
                $wpdb->query("DELETE FROM $wpdb->term_relationships WHERE object_id NOT IN (SELECT ID FROM $wpdb->posts)");
                break;
            case 'auto_drafts':
                $wpdb->query("DELETE FROM $wpdb->posts WHERE post_status = 'auto-draft'");
                $wpdb->query("DELETE FROM $wpdb->postmeta WHERE post_id NOT IN (SELECT ID FROM $wpdb->posts)");
                $wpdb->query("DELETE FROM $wpdb->term_relationships WHERE object_id NOT IN (SELECT ID FROM $wpdb->posts)");
                break;
            case 'spam_comments':
                $wpdb->query("DELETE FROM $wpdb->comments WHERE comment_approved = 'spam'");
                $wpdb->query("DELETE FROM $wpdb->commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM $wpdb->comments)");
                break;
            case 'trashed_comments':
                $wpdb->query("DELETE FROM $wpdb->comments WHERE comment_approved = 'trash'");
                $wpdb->query("DELETE FROM $wpdb->commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM $wpdb->comments)");
                break;
            case 'expired_transients':
                $wpdb->query("DELETE a, b FROM $wpdb->options a, $wpdb->options b WHERE a.option_name LIKE '\_transient\_%' AND a.option_name NOT LIKE '\_transient\_timeout\_%' AND b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12)) AND b.option_value < UNIX_TIMESTAMP()");
                $wpdb->query("DELETE FROM $wpdb->options WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP()");
                break;
            case 'all':
                // Do all
                $wpdb->query("DELETE FROM $wpdb->posts WHERE post_type = 'revision'");
                $wpdb->query("DELETE FROM $wpdb->posts WHERE post_status = 'auto-draft'");
                $wpdb->query("DELETE FROM $wpdb->postmeta WHERE post_id NOT IN (SELECT ID FROM $wpdb->posts)");
                $wpdb->query("DELETE FROM $wpdb->term_relationships WHERE object_id NOT IN (SELECT ID FROM $wpdb->posts)");
                $wpdb->query("DELETE FROM $wpdb->comments WHERE comment_approved = 'spam' OR comment_approved = 'trash'");
                $wpdb->query("DELETE FROM $wpdb->commentmeta WHERE comment_id NOT IN (SELECT comment_ID FROM $wpdb->comments)");
                $wpdb->query("DELETE a, b FROM $wpdb->options a, $wpdb->options b WHERE a.option_name LIKE '\_transient\_%' AND a.option_name NOT LIKE '\_transient\_timeout\_%' AND b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12)) AND b.option_value < UNIX_TIMESTAMP()");
                $wpdb->query("DELETE FROM $wpdb->options WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP()");
                break;
            default:
                wp_send_json_error('Unknown cleanup type');
        }

        wp_send_json_success(['message' => __('Cleaned up successfully!', 'aio')]);
    }

    /**
     * Schedule the Auto-Cleaner
     */
    public function schedule_auto_cleaner() {
        if (!wp_next_scheduled('aio_weekly_db_cleanup')) {
            wp_schedule_event(time(), 'weekly', 'aio_weekly_db_cleanup');
        }
    }

    /**
     * Process DB Cleanup
     */
    public function process_auto_cleaner() {
        global $wpdb;

        // Clean Post Revisions
        $wpdb->query("DELETE a,b,c FROM $wpdb->posts a LEFT JOIN $wpdb->term_relationships b ON (a.ID = b.object_id) LEFT JOIN $wpdb->postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'");

        // Clean Auto Drafts
        $wpdb->query("DELETE a,b,c FROM $wpdb->posts a LEFT JOIN $wpdb->term_relationships b ON (a.ID = b.object_id) LEFT JOIN $wpdb->postmeta c ON (a.ID = c.post_id) WHERE a.post_status = 'auto-draft'");

        // Clean Trashed Posts
        $wpdb->query("DELETE a,b,c FROM $wpdb->posts a LEFT JOIN $wpdb->term_relationships b ON (a.ID = b.object_id) LEFT JOIN $wpdb->postmeta c ON (a.ID = c.post_id) WHERE a.post_status = 'trash'");

        // Clean Spam Comments
        $wpdb->query("DELETE FROM $wpdb->comments WHERE comment_approved = 'spam'");

        // Clean Trashed Comments
        $wpdb->query("DELETE FROM $wpdb->comments WHERE comment_approved = 'trash'");

        // Clean Expired Transients
        $wpdb->query("DELETE a, b FROM $wpdb->options a, $wpdb->options b WHERE a.option_name LIKE '\_transient\_%' AND a.option_name NOT LIKE '\_transient\_timeout\_%' AND b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, 12)) AND b.option_value < UNIX_TIMESTAMP()");
        $wpdb->query("DELETE FROM $wpdb->options WHERE option_name LIKE '\_transient\_timeout\_%' AND option_value < UNIX_TIMESTAMP()");
    }
}

new AIO_DB_Cleaner();