WooCommerce Buyback custom Plugin: How to update user Wallet?

I am trying to create a buyback calculator when a user buys something normally and when the order is completed the user can go for a buyback with the SKU there will be certain amount deducted when the buyback is completed, but remaining amount will go in the wallet. When the user tries to purchase new product the wallet amount should be deducted I am able to deduct the amount but in the database the amount is not getting deducted.

// Function to create the buy_back_calculator table
function create_buy_back_calculator_table()
{
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();
    $table_name = $wpdb->prefix . 'buy_back_calculator';
    $total_wallet_amount = $wpdb->prefix . 'total_wallet_amount';

    // SQL statement to create the buy_back_calculator table
    $sql = "CREATE TABLE $table_name (
        id INT AUTO_INCREMENT PRIMARY KEY,
        sku VARCHAR(255) NOT NULL,
        user_name VARCHAR(255) NOT NULL,
        user_email VARCHAR(255) NOT NULL,
        user_billing_address VARCHAR(255) NOT NULL,
        user_phone_number VARCHAR(20) NOT NULL,
        date_of_product_purchase DATE NOT NULL,
        date_of_buyback DATE NOT NULL,
        product_price DECIMAL(10, 2) NOT NULL,
        number_of_days INT NOT NULL,
        buy_back_price DECIMAL(10, 2) NOT NULL,
        amount_in_wallet_remaining DECIMAL(10, 2) NOT NULL,
        transit_status VARCHAR(255) NOT NULL,
        image_path VARCHAR(255) NOT NULL
    ) $charset_collate;";

    // Execute the SQL query to create the buy_back_calculator table
    require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
    dbDelta($sql);

    // SQL statement to create the total_wallet_amount table
    $sql = "CREATE TABLE $total_wallet_amount (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_email VARCHAR(255) NOT NULL,
        total_wallet_amount DECIMAL(19, 4) NOT NULL
    ) $charset_collate;";

    // Execute the SQL query to create the total_wallet_amount table
    dbDelta($sql);
}

I tried the below function, but it is deducing the total checkout price from the wallet, but not updating the total_wallet_amount in the database.

function adjust_order_total_with_deduction()
{
    if (is_user_logged_in()) {
        global $wpdb;
        $current_user = wp_get_current_user();
        $user_email = $current_user->user_email;
        $total_wallet_amount_table = $wpdb->prefix . 'total_wallet_amount';
        $user_remaining_sum = $wpdb->get_var($wpdb->prepare("SELECT total_wallet_amount FROM $total_wallet_amount_table WHERE user_email = %s", $user_email));

        $deduction_amount = $user_remaining_sum; // Fixed amount to deduct
        if ($deduction_amount > 0) {
            WC()->cart->add_fee(__('Deduction Amount', 'your-text-domain'), -$deduction_amount);
        }
    }
}
add_action('woocommerce_cart_calculate_fees', 'adjust_order_total_with_deduction');

Solution

The following revisited code, that will deduce from cart total the available user wallet amount in checkout. Then when order is placed and when order status will be set to processing or completed, the deduced amount will be removed from the user wallet:

// Utility function: Get user wallet amount
function get_total_wallet_amount( $user_email ) {
    global $wpdb;
    return $wpdb->get_var($wpdb->prepare("
    SELECT total_wallet_amount FROM {$wpdb->prefix}total_wallet_amount WHERE user_email = %s
    ", $user_email));
}

// Utility function: Update user wallet amount
function update_total_wallet_amount( $user_email, $new_total ) {
    global $wpdb;
    return $wpdb->query($wpdb->prepare("
    UPDATE {$wpdb->prefix}total_wallet_amount twa 
    SET total_wallet_amount = '%s' WHERE twa.user_email = %s;
    ", $new_total, $user_email));
}

// Add a discount (related to user wallet available amount)
add_action('woocommerce_cart_calculate_fees', 'adjust_order_total_with_deduction');
function adjust_order_total_with_deduction( $cart ){
    if ( is_admin() && ! defined( 'DOING_AJAX' ) )
        return;

    global $current_user;
    
    if ($current_user->ID > 0) {
        $total_wallet_amount   = get_total_wallet_amount( $current_user->user_email );
        $cart_contents_total  = $cart->get_cart_contents_total(); 
        $cart_contents_total += $cart->get_cart_contents_tax(); 

        if ($total_wallet_amount > 0) {
            // calculations
            if( $total_wallet_amount >= $cart_contents_total ) {
                $deduction_amount = -$cart_contents_total;
            } else {
                $deduction_amount = -$total_wallet_amount;
            }
            $cart->add_fee(__('Deduction Amount', 'your-text-domain'), $deduction_amount);
        }
    }
}

// Update user wallet amount after order is placed (on processing or completed status)
add_action('woocommerce_order_status_processing', 'update_total_wallet_amount_from_order', 10, 2 );
add_action('woocommerce_order_status_completed', 'update_total_wallet_amount_from_order', 10, 2 );
function update_total_wallet_amount_from_order( $order_id, $order ){
    $wallet_updated = $order->get_meta('_user_wallet_updated'); // Check if user wallet has been updated yet

    if ( ! $wallet_updated ) {
        $current_user  = $order->get_user();
        $wallet_amount = (float) get_total_wallet_amount( $current_user->user_email ); // get user Wallet amount

        foreach( $order->get_items('fee') as $item_fee ) {
            if( $item_fee->get_name() === 'Deduction Amount' && $item_fee->get_total() < 0 ) {
                // Update wallet amount
                update_total_wallet_amount( $current_user->user_email, $wallet_amount + floatval($item_fee->get_total()) );
                // Tag order as "_user_wallet_updated" true
                $order->update_meta_data('_user_wallet_updated', '1'); 
                $order->save(); // save
                break;
            }
        }
    }
}

Code goes in functions.php file of your child theme (or in a plugin). Tested and works.