<?php
// IV1 WP Tools – DB-Bereinigung AJAX & Funktionen
defined("ABSPATH") || exit;

// AJAX: DB-Bereinigung – Scannen
// ────────────────────────────────────────────────
add_action('wp_ajax_iv1wpt_cleanup_scan', 'iv1wpt_cleanup_scan_ajax');
function iv1wpt_cleanup_scan_ajax(): void {
    check_ajax_referer('iv1wpt_ajax', '_ajax_nonce');
    if (!current_user_can('manage_options')) {
        wp_send_json_error(__('No permission.', 'iv1-wp-tools'));
    }

    $key = sanitize_text_field($_POST['cleanup_key'] ?? '');
    $result = iv1wpt_cleanup_count($key);

    if ($result === null) {
        wp_send_json_error(__('Unknown cleanup type.', 'iv1-wp-tools'));
    }

    wp_send_json_success($result);
}

// ────────────────────────────────────────────────
// AJAX: DB-Bereinigung – Ausführen
// ────────────────────────────────────────────────
add_action('wp_ajax_iv1wpt_cleanup_run', 'iv1wpt_cleanup_run_ajax');
function iv1wpt_cleanup_run_ajax(): void {
    check_ajax_referer('iv1wpt_ajax', '_ajax_nonce');
    if (!current_user_can('manage_options')) {
        wp_send_json_error(__('No permission.', 'iv1-wp-tools'));
    }

    $key = sanitize_text_field($_POST['cleanup_key'] ?? '');
    $result = iv1wpt_cleanup_execute($key);

    if ($result === null) {
        wp_send_json_error(__('Unknown cleanup type.', 'iv1-wp-tools'));
    }

    wp_send_json_success($result);
}

// ────────────────────────────────────────────────
// DB-Bereinigung: Zählen / Größe ermitteln
// ────────────────────────────────────────────────
function iv1wpt_cleanup_count(string $key): ?array {
    global $wpdb;

    switch ($key) {
        case 'revisions':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'revision'"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(post_content) + LENGTH(post_title)) FROM {$wpdb->posts} WHERE post_type = 'revision'"
            );
            break;

        case 'auto_drafts':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(post_content) + LENGTH(post_title)) FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
            );
            break;

        case 'trashed_posts':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_status = 'trash'"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(post_content) + LENGTH(post_title)) FROM {$wpdb->posts} WHERE post_status = 'trash'"
            );
            break;

        case 'spam_comments':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'spam'"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(comment_content)) FROM {$wpdb->comments} WHERE comment_approved = 'spam'"
            );
            break;

        case 'trashed_comments':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(comment_content)) FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
            );
            break;

        case 'pingbacks':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->comments} WHERE comment_type IN ('pingback', 'trackback')"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(comment_content)) FROM {$wpdb->comments} WHERE comment_type IN ('pingback', 'trackback')"
            );
            break;

        case 'orphan_postmeta':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID WHERE p.ID IS NULL"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(pm.meta_key) + LENGTH(pm.meta_value)) FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID WHERE p.ID IS NULL"
            );
            break;

        case 'orphan_commentmeta':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID WHERE c.comment_ID IS NULL"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(cm.meta_key) + LENGTH(cm.meta_value)) FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID WHERE c.comment_ID IS NULL"
            );
            break;

        case 'orphan_usermeta':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->usermeta} um LEFT JOIN {$wpdb->users} u ON um.user_id = u.ID WHERE u.ID IS NULL"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(um.meta_key) + LENGTH(um.meta_value)) FROM {$wpdb->usermeta} um LEFT JOIN {$wpdb->users} u ON um.user_id = u.ID WHERE u.ID IS NULL"
            );
            break;

        case 'orphan_termmeta':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->termmeta} tm LEFT JOIN {$wpdb->terms} t ON tm.term_id = t.term_id WHERE t.term_id IS NULL"
            );
            $size = iv1wpt_cleanup_data_size(
                "SELECT SUM(LENGTH(tm.meta_key) + LENGTH(tm.meta_value)) FROM {$wpdb->termmeta} tm LEFT JOIN {$wpdb->terms} t ON tm.term_id = t.term_id WHERE t.term_id IS NULL"
            );
            break;

        case 'orphan_relationships':
            $count = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->term_relationships} tr LEFT JOIN {$wpdb->posts} p ON tr.object_id = p.ID WHERE p.ID IS NULL"
            );
            $size = '—';
            break;

        case 'expired_transients':
            $time = time();
            $count = (int) $wpdb->get_var($wpdb->prepare(
                "SELECT COUNT(*) FROM {$wpdb->options} a INNER JOIN {$wpdb->options} b
                 ON b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, CHAR_LENGTH('_transient_') + 1))
                 WHERE a.option_name LIKE %s AND b.option_value < %d",
                $wpdb->esc_like('_transient_') . '%',
                $time
            ));
            $size = iv1wpt_cleanup_data_size($wpdb->prepare(
                "SELECT SUM(LENGTH(a.option_value)) FROM {$wpdb->options} a INNER JOIN {$wpdb->options} b
                 ON b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, CHAR_LENGTH('_transient_') + 1))
                 WHERE a.option_name LIKE %s AND b.option_value < %d",
                $wpdb->esc_like('_transient_') . '%',
                $time
            ));
            break;

        case 'optimize_tables':
            $tables = $wpdb->get_results("SHOW TABLE STATUS", ARRAY_A);
            $overhead = 0;
            foreach ($tables as $t) {
                $overhead += (int) ($t['Data_free'] ?? 0);
            }
            $count = count($tables);
            $size = size_format($overhead);
            break;

        case 'repair_tables':
            $tables = $wpdb->get_col("SHOW TABLES");
            $count = count($tables);
            $size = '—';
            break;

        default:
            return null;
    }

    $result = [
        'key'   => $key,
        'count' => $count,
        'size'  => is_string($size) ? $size : size_format($size),
    ];

    return $result;
}

// Hilfsfunktion: Datengröße aus Query
function iv1wpt_cleanup_data_size(string $query): string {
    global $wpdb;
    $bytes = (int) $wpdb->get_var($query);
    return size_format(max(0, $bytes));
}

// ────────────────────────────────────────────────
// DB-Bereinigung: Ausführen
// ────────────────────────────────────────────────
function iv1wpt_cleanup_execute(string $key): ?array {
    global $wpdb;

    switch ($key) {
        case 'revisions':
            // Zugehörige Meta-Daten zuerst löschen
            $wpdb->query(
                "DELETE pm FROM {$wpdb->postmeta} pm INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID WHERE p.post_type = 'revision'"
            );
            $deleted = $wpdb->query(
                "DELETE FROM {$wpdb->posts} WHERE post_type = 'revision'"
            );
            break;

        case 'auto_drafts':
            $wpdb->query(
                "DELETE pm FROM {$wpdb->postmeta} pm INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID WHERE p.post_status = 'auto-draft'"
            );
            $deleted = $wpdb->query(
                "DELETE FROM {$wpdb->posts} WHERE post_status = 'auto-draft'"
            );
            break;

        case 'trashed_posts':
            $wpdb->query(
                "DELETE pm FROM {$wpdb->postmeta} pm INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID WHERE p.post_status = 'trash'"
            );
            $deleted = $wpdb->query(
                "DELETE FROM {$wpdb->posts} WHERE post_status = 'trash'"
            );
            break;

        case 'spam_comments':
            $wpdb->query(
                "DELETE cm FROM {$wpdb->commentmeta} cm INNER JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID WHERE c.comment_approved = 'spam'"
            );
            $deleted = $wpdb->query(
                "DELETE FROM {$wpdb->comments} WHERE comment_approved = 'spam'"
            );
            break;

        case 'trashed_comments':
            $wpdb->query(
                "DELETE cm FROM {$wpdb->commentmeta} cm INNER JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID WHERE c.comment_approved = 'trash'"
            );
            $deleted = $wpdb->query(
                "DELETE FROM {$wpdb->comments} WHERE comment_approved = 'trash'"
            );
            break;

        case 'pingbacks':
            $wpdb->query(
                "DELETE cm FROM {$wpdb->commentmeta} cm INNER JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID WHERE c.comment_type IN ('pingback', 'trackback')"
            );
            $deleted = $wpdb->query(
                "DELETE FROM {$wpdb->comments} WHERE comment_type IN ('pingback', 'trackback')"
            );
            break;

        case 'orphan_postmeta':
            $deleted = $wpdb->query(
                "DELETE pm FROM {$wpdb->postmeta} pm LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID WHERE p.ID IS NULL"
            );
            break;

        case 'orphan_commentmeta':
            $deleted = $wpdb->query(
                "DELETE cm FROM {$wpdb->commentmeta} cm LEFT JOIN {$wpdb->comments} c ON cm.comment_id = c.comment_ID WHERE c.comment_ID IS NULL"
            );
            break;

        case 'orphan_usermeta':
            $deleted = $wpdb->query(
                "DELETE um FROM {$wpdb->usermeta} um LEFT JOIN {$wpdb->users} u ON um.user_id = u.ID WHERE u.ID IS NULL"
            );
            break;

        case 'orphan_termmeta':
            $deleted = $wpdb->query(
                "DELETE tm FROM {$wpdb->termmeta} tm LEFT JOIN {$wpdb->terms} t ON tm.term_id = t.term_id WHERE t.term_id IS NULL"
            );
            break;

        case 'orphan_relationships':
            $deleted = $wpdb->query(
                "DELETE tr FROM {$wpdb->term_relationships} tr LEFT JOIN {$wpdb->posts} p ON tr.object_id = p.ID WHERE p.ID IS NULL"
            );
            break;

        case 'expired_transients':
            $time = time();
            // Timeout-Einträge finden
            $expired = $wpdb->get_col($wpdb->prepare(
                "SELECT SUBSTRING(a.option_name, CHAR_LENGTH('_transient_') + 1) FROM {$wpdb->options} a
                 INNER JOIN {$wpdb->options} b
                 ON b.option_name = CONCAT('_transient_timeout_', SUBSTRING(a.option_name, CHAR_LENGTH('_transient_') + 1))
                 WHERE a.option_name LIKE %s AND b.option_value < %d",
                $wpdb->esc_like('_transient_') . '%',
                $time
            ));
            $deleted = 0;
            foreach ($expired as $transient) {
                if (delete_transient($transient)) {
                    $deleted++;
                }
            }
            break;

        case 'optimize_tables':
            $tables = $wpdb->get_col("SHOW TABLES");
            $deleted = 0;
            foreach ($tables as $table) {
                $wpdb->query("OPTIMIZE TABLE `$table`");
                $deleted++;
            }
            break;

        case 'repair_tables':
            $tables = $wpdb->get_col("SHOW TABLES");
            $deleted = 0;
            foreach ($tables as $table) {
                $wpdb->query("REPAIR TABLE `$table`");
                $deleted++;
            }
            break;

        default:
            return null;
    }

    $labels = [
        'revisions'            => 'Revisionen',
        'auto_drafts'          => 'Auto-Entwürfe',
        'trashed_posts'        => 'Papierkorb-Beiträge',
        'spam_comments'        => 'Spam-Kommentare',
        'trashed_comments'     => 'Papierkorb-Kommentare',
        'pingbacks'            => 'Pingbacks/Trackbacks',
        'orphan_postmeta'      => __('orphaned post meta', 'iv1-wp-tools'),
        'orphan_commentmeta'   => __('orphaned comment meta', 'iv1-wp-tools'),
        'orphan_usermeta'      => __('orphaned user meta', 'iv1-wp-tools'),
        'orphan_termmeta'      => __('orphaned term meta', 'iv1-wp-tools'),
        'orphan_relationships' => __('orphaned relationships', 'iv1-wp-tools'),
        'expired_transients'   => 'abgelaufene Transients',
        'optimize_tables'      => 'Tabellen optimiert',
        'repair_tables'        => 'Tabellen repariert',
    ];

    $label = $labels[$key] ?? $key;
	iv1wpt_plugin_log(
		sprintf(
			/* translators: 1: number of deleted items, 2: cleanup label/type */
			__('IV1WPT Cleanup: %1$d %2$s cleaned up', 'iv1-wp-tools'),
			$deleted,
			$label
		)
	);	

    return [
        'key'     => $key,
        'deleted' => $deleted,
        /* translators: 1: number of deleted items, 2: label/category name */
        'message' => sprintf(__('%1$d %2$s cleaned.', 'iv1-wp-tools'), $deleted, $label),
    ];
}
