/**
 * Aelia Currency Switcher for WooCommerce.
 * How to delete order meta in base currency for orders with a "zero" order total. The queries
 * can be used to to clean up order totals, before running the recalculation described in
 * the following knowledge base article: https://aelia.freshdesk.com/a/solutions/articles/3000052833
 *
 * IMPORTANT WARNING
 * The queries delete data from the database, and the deletion is irreversible. We strongly
 * recommend to run them on a staging copy of your database first, and to take a full backup
 * of your data before running them on the production site.
 *
 * DISCLAIMER
 * Aelia and any member of its staff are not responsible for any data loss or damage incurred 
 * when using the code, which you can use at your own risk.
 *
 * GPL DISCLAIMER
 * Because this code program is free of charge, there is no warranty for it, to the extent permitted by applicable law.
 * Except when otherwise stated in writing the copyright holders and/or other parties provide the program "as is"
 * without warranty of any kind, either expressed or implied, including, but not limited to, the implied warranties of
 * merchantability and fitness for a particular purpose. The entire risk as to the quality and performance of the program
 * is with you. should the program prove defective, you assume the cost of all necessary servicing, repair or correction.
 *
 * Need a consultation, or assistance to customise this code? Find us on Codeable: https://aelia.co/hire_us
 */

-- Delete base currency amounts for order items
-- from orders with a base currency total of zero or less
-- SELECT 
-- meta_id,
-- meta_key
DELETE
FROM
 wp_woocommerce_order_itemmeta
WHERE
	(meta_id IN (
		SELECT
			OIM.meta_id
		FROM
				wp_woocommerce_order_itemmeta OIM
				JOIN
				wp_woocommerce_order_items OI ON
					(OI.order_item_id = OIM.order_item_id)
				JOIN
				wp_postmeta PM ON
					(PM.post_id = OI.order_id) AND
					(PM.meta_key = '_order_total_base_currency') AND
					(PM.meta_value <= 0)
		WHERE
			OIM.meta_key like '%_base_currency'
		));

-- Delete base currency amounts for orders
-- with a base currency total of zero or less.
-- SELECT
--     post_id
DELETE
FROM
    wp_postmeta
WHERE
	(meta_key like '%_base_currency%') AND
	(post_id IN (
		SELECT
			post_id
		FROM
				wp_postmeta
		WHERE
				(meta_key = '_order_total_base_currency') AND
				(meta_value <= 0)
	));