All Submissions:

• Have you followed the WooCommerce Contributing guideline?
• Does your code follow the WordPress' coding standards?
• Have you checked to ensure there aren't other open Pull Requests for the same update/change?

Changes proposed in this Pull Request:

• Redesign the custom orders table settings UI and behavior according to the latest agreed specification (p7bje6-3LN-p2#comment-6886).
• Add SQL queries to get the count of orders that need to be synchronized, and their ids.

The settings page (WooCommerce > Settings > Advanced > Custom data stores) now looks like this when there aren't any orders out of sync:

These settings can be freely modified.

Now, let's assume that sync is disabled and there are some orders of out of sync. The settings page will now look like this:

Note how the authoritative table can't be changed. There's also a check in the backend to prevent that setting from being changed if there are out of sync orders (an exception will be thrown).

Setting Keep the posts table and the orders tables synchronized and saving will start a synchronization process (not implemented yet), this will be done using Action Scheduler and each run will syncrhonize a batch of orders. The settings will change as follows once that happens:

Note how the warning message now says that sync is in progress, and includes the count of orders that were out of sync when the process started (this UI can be refined later by adding an actual progress bar or similar). Notice also the new Switch to using the orders/posts table as the authoritative data store for orders when sync finishes option: if set, the authoritative table for storing orders will be switched (from posts to orders or viceversa) once no more out of sync orders remain.

Worth noting that the Switch to using the orders/posts table as the authoritative data store for orders when sync finishes option will be automatically turned off once the table switch has happened. It will also be turned off if Keep the posts table and the orders tables synchronized itself is turned off. This is to prevent accidental/unexpected table switches.

There's one case in which orders can get out of sync even when we have automatic sync turned on: when some 3rd party code does direct modifications in the posts table. Automatic sync ensures that changes in an order propagate to the backup table only when the changes is made by WooCommerce itself.

When that happens, a synchronization process can be triggered by just saving the settings with the Keep the posts table and the orders tables synchronized setting on, even if it was already on (no need to turn it off, save, and turn it on again).

Closes #31769.
Closes #31770.
Closes #31771.

How to test the changes in this Pull Request:

Initial setup

1. Enable the feature by adding the following code snippet somewhere, for example at the end of woocommerce.php:

wc_get_container()
   ->get('Automattic\WooCommerce\Internal\DataStores\Orders\CustomOrdersTableController')
   ->show_feature();

2. If you hadn't created the orders table previously, go to Status - Tools and run the "Create and fill custom orders table" tool.

3. You can use the following SQL commands to populate the orders table with data from the posts table and force some of them to be out of sync (adjust the LIMIT clauses and the number of (null) entries as desired):

/* Create some synchronized entries in orders table */
INSERT INTO wp_wc_orders (post_id,date_created_gmt,date_updated_gmt)
SELECT ID,post_date_gmt,post_modified_gmt from wp_posts 
WHERE post_type='shop_order' AND post_status != 'auto-draft' LIMIT 10;

/* Create some entries in orders table that don't exist in posts table */
INSERT INTO wp_wc_orders (post_id) VALUES (null),(null),(null);

/* Make some of the entries that exist in both tables to be out of sync */
UPDATE wp_wc_orders SET date_updated_gmt=now() WHERE post_id IS NOT null LIMIT 2;

Testing settings and scheduled actions with the fake out of sync orders count

This pull request introduces a mechanism to declare a fake count of orders out of sync, intended to ease the testing of the feature while it's in development. This mechanism is an option named woocommerce_fake_orders_pending_sync_count. If it exists (it must be a number >= 0) the following will happen:

• The count of orders out of sync returned by DataSynchronizer::get_current_orders_pending_sync_count will be the value of this option, no actual database access will be performed.
• The DataSynchronizer::do_pending_orders_synchronization method, which will be run by Action Scheduler, will simply update the value of this option with its current value minus one, until it reaches zero; no actual database access will be performed (note however that at the time of submitting this pull request the actual synchronization isn't implemented yet, so no database access would have been performed anyway).

So with this, here's how to test:

1. Set the fake out of sync orders count to zero (you can simply use wp option set woocommerce_fake_orders_pending_sync_count 0).
2. Go to WooCommerce > Settings > Advanced > Custom data stores, verify that there's no warning about out of sync orders and that you can modiify all the settings. Make sure to leave the 'Keep the posts table and the orders tables synchronized' setting turned off.
3. Set the fake count to any non-zero value and reload the settings pages. Now you'll see the warning about out of sync orders and you aren't able to modify the 'Data store for orders' setting.
4. Set the 'Keep the posts table and the orders tables synchronized' settting on and reload the settings page. Now the warning message will include "Synchronization for these orders is currently in progress" as well as the count of orders that you had initially set in the option.
5. Let Action Scheduler do its job (e.g. reload the settings page). You'll see that the out of sync orders count in the warning message decreases (but the initial count doesn't). Eventually it will reach zero and you'll go back to the initial state (no warning, you can change the authorittative table).
6. Repeat from 3 but this time, when the settings page reloads after you have enabled the syncrhonization, set the 'Switch to using the posts/orders table as the authoritative data store for orders when sync finishes' setting on and save. Now once the syncrhonization has finished you'll see that the authoritative table ('Data store for orders') has changed. Repeat to verify that the process is symmetrical.
7. Repeat from 3 but this time set 'Keep the posts table and the orders tables synchronized' off and save in the middle of the synchronization process. You'll see that "Synchronization for these orders is currently in progress" in the warning disappears. Set it on and save again, the synchronization will resume from the point in which it was turned off (the new initial out of sync orders count will be the current count as it was when it was turned off).
8. Repeat 6 and then set 'Keep the posts table and the orders tables synchronized' off and save. Turn sync on and verify that 'Switch to using the posts/orders table as the authoritative data store for orders when sync finishes' has been turned off automatically.

Testing the query for out of sync order ids

Temporarily modify DataSynchronizer::get_ids_of_orders_pending_sync so that it's public and run it from CLI as follows:

wp eval 'echo var_dump(wc_get_container()->get(\Automattic\WooCommerce\Internal\DataStores\Orders\DataSynchronizer::class)->get_ids_of_orders_pending_sync(type, limit));'

where type is the value of one of the ID_TYPE_... constants (see the header of get_ids_of_orders_pending_sync for details) and limit is the maximum number of ids to return.

Other information:

• Have you added an explanation of what your changes do and why you'd like us to include them?
• Have you written new tests for your changes, as applicable?
• Have you successfully run tests with your changes locally?

Changelog entry

Add - Queries for non-synchronized orders

FOR PR REVIEWER ONLY:

• I have reviewed that everything is sanitized/escaped appropriately for any SQL or XSS injection possibilities. I made sure Linting is not ignored or disabled.