<?php
/**
 * API\Reports\Taxes\DataStore class file.
 */

namespace Automattic\WooCommerce\Admin\API\Reports\Taxes;

defined( 'ABSPATH' ) || exit;

use Automattic\WooCommerce\Admin\API\Reports\DataStore as ReportsDataStore;
use Automattic\WooCommerce\Admin\API\Reports\DataStoreInterface;
use Automattic\WooCommerce\Admin\API\Reports\TimeInterval;
use Automattic\WooCommerce\Admin\API\Reports\SqlQuery;
use Automattic\WooCommerce\Admin\API\Reports\Cache as ReportsCache;
use Automattic\WooCommerce\Enums\OrderItemType;

/**
 * API\Reports\Taxes\DataStore.
 */
class DataStore extends ReportsDataStore implements DataStoreInterface {

	/**
	 * Table used to get the data.
	 *
	 * @override ReportsDataStore::$table_name
	 *
	 * @var string
	 */
	protected static $table_name = 'wc_order_tax_lookup';

	/**
	 * Cache identifier.
	 *
	 * @override ReportsDataStore::$cache_key
	 *
	 * @var string
	 */
	protected $cache_key = 'taxes';

	/**
	 * Mapping columns to data type to return correct response types.
	 *
	 * @override ReportsDataStore::$column_types
	 *
	 * @var array
	 */
	protected $column_types = array(
		'tax_rate_id'             => 'intval',
		'name'                    => 'strval',
		'tax_rate'                => 'floatval',
		'country'                 => 'strval',
		'state'                   => 'strval',
		'priority'                => 'intval',
		'total_tax'               => 'floatval',
		'order_tax'               => 'floatval',
		'shipping_tax'            => 'floatval',
		'taxable_amount'          => 'floatval',
		'order_taxable_amount'    => 'floatval',
		'shipping_taxable_amount' => 'floatval',
		'orders_count'            => 'intval',
	);

	/**
	 * Data store context used to pass to filters.
	 *
	 * @override ReportsDataStore::$context
	 *
	 * @var string
	 */
	protected $context = 'taxes';

	/**
	 * Constructor.
	 *
	 * Report on the date type configured in Analytics settings (default `date_paid`),
	 * matching the Orders and Revenue reports. The reporting period is filtered against
	 * the chosen column on `wc_order_stats` so the Taxes report reconciles with them.
	 *
	 * @override ReportsDataStore::__construct()
	 */
	public function __construct() {
		$this->date_column_name = $this->sanitize_date_column_name( get_option( 'woocommerce_date_type' ), 'date_paid' );
		parent::__construct();
	}

	/**
	 * Assign report columns once full table name has been assigned.
	 *
	 * @override ReportsDataStore::assign_report_columns()
	 */
	protected function assign_report_columns() {
		global $wpdb;
		$table_name = self::get_db_table_name();

		// Using wp_woocommerce_tax_rates table limits the result to only the existing tax rates and
		// omits the historical records which differs from the purpose of wp_wc_order_tax_lookup table.
		// So in order to get the same data present in wp_woocommerce_tax_rates without breaking the
		// API contract the values are now retrieved from wp_woocommerce_order_items and wp_woocommerce_order_itemmeta.
		// And given that country, state and priority are not separate columns within the woocommerce_order_items,
		// a split to order_item_name column value is required to separate those values. This is not ideal,
		// but given this query is paginated and cached, then it is not a big deal. There is always room for
		// improvements here.
		$this->report_columns = array(
			'tax_rate_id'             => "{$table_name}.tax_rate_id",
			'name'                    => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-2), '-', 1) as name",
			'tax_rate'                => 'CAST(itemmeta_rate_percent.meta_value AS DECIMAL(7,4)) as tax_rate',
			'country'                 => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',1) as country",
			'state'                   => "SUBSTRING_INDEX(SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-3), '-', 1) as state",
			'priority'                => "SUBSTRING_INDEX({$wpdb->prefix}woocommerce_order_items.order_item_name,'-',-1) as priority",
			'total_tax'               => 'SUM(total_tax) as total_tax',
			'order_tax'               => 'SUM(order_tax) as order_tax',
			'shipping_tax'            => 'SUM(shipping_tax) as shipping_tax',
			'taxable_amount'          => 'SUM(taxable_amount) as taxable_amount',
			'order_taxable_amount'    => self::taxable_amount_part_column( 'order_taxable_amount' ),
			'shipping_taxable_amount' => self::taxable_amount_part_column( 'shipping_taxable_amount' ),
			// parent_id stays unqualified: wc_order_stats is the only joined table carrying it, and
			// this string is carried by the public woocommerce_admin_report_columns filter, so it must
			// match the released form for extension callbacks that inspect or rewrite it.
			'orders_count'            => "COUNT( DISTINCT ( CASE WHEN parent_id = 0 THEN {$table_name}.order_id END ) ) as orders_count",
		);

		// Guard against a column not existing yet: the report otherwise breaks entirely on a
		// site where the upgrade routine has not run (or could not run) the schema update.
		if ( ! static::has_taxable_amount_column() ) {
			unset( $this->report_columns['taxable_amount'] );
		}

		if ( ! static::has_taxable_amount_split_columns() ) {
			unset( $this->report_columns['order_taxable_amount'], $this->report_columns['shipping_taxable_amount'] );
		}
	}

	/**
	 * SQL summing one part of the taxable amount of a tax rate.
	 *
	 * Selects NULL while the rate holds a row recorded before the split (the part is NULL), since
	 * summing it in would report a part that is short with nothing saying so.
	 *
	 * @param string $column Column holding the part, `order_taxable_amount` or `shipping_taxable_amount`.
	 * @return string
	 */
	private static function taxable_amount_part_column( string $column ): string {
		return "CASE WHEN MAX( {$column} IS NULL ) = 1 THEN NULL ELSE SUM({$column}) END as {$column}";
	}

	/**
	 * SQL condition matching the tax codes of a list of locations.
	 *
	 * A location is a country code (`GB`) or a country and state pair (`US:CA`). A tax code is
	 * `COUNTRY-STATE-NAME-PRIORITY`, so a location matches as a prefix of it.
	 *
	 * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
	 * @since 11.3.0
	 *
	 * @param string $locations  Comma separated list of locations.
	 * @param bool   $is_include True to match the locations, false to match everything else.
	 * @return string SQL condition. An include naming no location matches nothing; an exclude
	 *                naming none is an empty string, so it filters nothing.
	 */
	public static function get_location_condition( string $locations, bool $is_include ): string {
		global $wpdb;

		$column     = $wpdb->prefix . 'woocommerce_order_items.order_item_name';
		$conditions = array();

		foreach ( explode( ',', $locations ) as $location ) {
			$parts   = explode( ':', $location, 2 );
			$country = self::normalize_country_code( $parts[0] );

			if ( '' === $country ) {
				continue;
			}

			$state = isset( $parts[1] ) ? self::normalize_state_code( $parts[1] ) : '';

			// `US:` is not `US`. Falling back to the country prefix would widen an include to
			// every row of that country instead of narrowing it.
			if ( isset( $parts[1] ) && '' === $state ) {
				continue;
			}

			$prefix = '' === $state ? "{$country}-" : "{$country}-{$state}-";

			$conditions[] = $wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Column name cannot be prepared.
				"{$column} LIKE %s",
				$wpdb->esc_like( $prefix ) . '%'
			);
		}

		if ( ! $conditions ) {
			return $is_include ? '1 = 0' : '';
		}

		$condition = '( ' . implode( ' OR ', $conditions ) . ' )';

		return $is_include ? $condition : "NOT {$condition}";
	}

	/**
	 * Normalize a country code the way `WC_Tax` writes it.
	 *
	 * `WC_Tax::format_tax_rate_country()` only uppercases it.
	 *
	 * @param string $code Country code.
	 * @return string
	 */
	private static function normalize_country_code( string $code ): string {
		return strtoupper( trim( $code ) );
	}

	/**
	 * Normalize a state code the way `WC_Tax` writes it.
	 *
	 * `WC_Tax::prepare_tax_rate()` runs a state through `sanitize_key()`, which drops the space of
	 * a code such as `HONG KONG`. Without the same pass the filter looks for a code the store
	 * never wrote.
	 *
	 * @param string $code State code.
	 * @return string
	 */
	private static function normalize_state_code( string $code ): string {
		return strtoupper( sanitize_key( $code ) );
	}

	/**
	 * SQL condition for the location filter of a report query.
	 *
	 * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
	 * @since 11.3.0
	 *
	 * @param array $query_args Query arguments supplied by the user.
	 * @return string SQL condition, or an empty string when the query carries no location filter.
	 */
	public static function get_location_filter_condition( array $query_args ): string {
		$conditions = array();

		foreach ( array(
			'location_includes' => true,
			'location_excludes' => false,
		) as $param => $is_include ) {
			if ( empty( $query_args[ $param ] ) ) {
				continue;
			}

			$locations = $query_args[ $param ];
			$locations = is_array( $locations ) ? implode( ',', $locations ) : (string) $locations;
			$condition = self::get_location_condition( $locations, $is_include );

			if ( '' !== $condition ) {
				$conditions[] = $condition;
			}
		}

		return $conditions ? implode( ' AND ', $conditions ) : '';
	}

	/**
	 * The location filter of a report query, as a condition on the lookup table alone.
	 *
	 * The tax code lives on the tax order item, which the stats queries do not join: joining it
	 * would repeat a summed row for every line of the order sharing its rate, so an EXISTS check
	 * reads the code instead. A row keyed by its tax line reads that one item by its primary key.
	 * Only a legacy row at the zero default has to search the order's tax items for its rate.
	 *
	 * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
	 * @since 11.3.0
	 *
	 * @param array $query_args Query arguments supplied by the user.
	 * @return string SQL condition, or an empty string when the query carries no location filter.
	 */
	public static function get_location_filter_subquery( array $query_args ): string {
		global $wpdb;

		$condition = self::get_location_filter_condition( $query_args );

		if ( '' === $condition ) {
			return '';
		}

		$table_name = self::get_db_table_name();
		$items      = $wpdb->prefix . 'woocommerce_order_items';
		$item_meta  = $wpdb->prefix . 'woocommerce_order_itemmeta';
		$tax_type   = OrderItemType::TAX;

		return "( ( {$table_name}.order_item_id > 0 AND EXISTS ( SELECT 1 FROM {$items}
				WHERE {$items}.order_item_id = {$table_name}.order_item_id
				AND {$condition} ) )
			OR ( {$table_name}.order_item_id = 0 AND EXISTS ( SELECT 1 FROM {$items}
				JOIN {$item_meta} location_rate_id ON location_rate_id.order_item_id = {$items}.order_item_id AND location_rate_id.meta_key = 'rate_id'
				WHERE {$items}.order_id = {$table_name}.order_id
				AND {$items}.order_item_type = '{$tax_type}'
				AND location_rate_id.meta_value = {$table_name}.tax_rate_id
				AND {$condition} ) ) )";
	}

	/**
	 * Check if the wc_order_tax_lookup table has the taxable_amount column.
	 *
	 * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
	 * @since 11.2.0
	 *
	 * @return bool
	 */
	public static function has_taxable_amount_column() {
		return self::lookup_has_column( 'taxable_amount' );
	}

	/**
	 * Check if the wc_order_tax_lookup table has the taxable_amount column and the columns
	 * holding its order and shipping parts.
	 *
	 * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
	 * @since 11.3.0
	 *
	 * @return bool
	 */
	public static function has_taxable_amount_split_columns() {
		return static::has_taxable_amount_column()
			&& self::lookup_has_column( 'order_taxable_amount' )
			&& self::lookup_has_column( 'shipping_taxable_amount' );
	}

	/**
	 * Columns of the lookup table, read once per request and keyed by blog id.
	 *
	 * @var array<int, string[]>
	 */
	private static $lookup_columns = array();

	/**
	 * Forget the columns this request has read, so the next check asks the schema again.
	 *
	 * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
	 * @since 11.3.0
	 *
	 * @return void
	 */
	public static function flush_lookup_columns_cache() {
		self::$lookup_columns = array();
	}

	/**
	 * Check if the wc_order_tax_lookup table has a column.
	 *
	 * @param string $column Column name.
	 * @return bool
	 */
	private static function lookup_has_column( string $column ): bool {
		$blog_id = get_current_blog_id();

		if ( ! isset( self::$lookup_columns[ $blog_id ] ) ) {
			self::$lookup_columns[ $blog_id ] = self::read_lookup_columns();
		}

		return in_array( $column, self::$lookup_columns[ $blog_id ], true );
	}

	/**
	 * The columns the wc_order_tax_lookup table holds.
	 *
	 * Asks the schema rather than an option, so a column dropped or restored behind WooCommerce's
	 * back corrects the report by itself.
	 *
	 * @return string[] Column names, empty while the table does not exist.
	 */
	private static function read_lookup_columns(): array {
		global $wpdb;

		$table_name = self::get_db_table_name();

		// If the table itself does not exist yet, reading its columns would be a DB error.
		$table_exists = $wpdb->get_var(
			$wpdb->prepare(
				'SHOW TABLES LIKE %s',
				$table_name
			)
		);

		if ( ! $table_exists ) {
			return array();
		}

		return $wpdb->get_col(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name cannot be prepared.
			"SHOW COLUMNS FROM `{$table_name}`"
		);
	}

	/**
	 * Set up all the hooks for maintaining and populating table data.
	 */
	public static function init() {
		add_action( 'woocommerce_analytics_delete_order_stats', array( __CLASS__, 'sync_on_order_delete' ), 15 );
	}

	/**
	 * Fills FROM clause of SQL request based on user supplied parameters.
	 *
	 * @param array  $query_args          Query arguments supplied by the user.
	 * @param string $order_status_filter Order status subquery. Retained for signature compatibility; the wc_order_stats join is now always added.
	 */
	protected function add_from_sql_params( $query_args, $order_status_filter ) {
		global $wpdb;
		$table_name = self::get_db_table_name();

		// Always join wc_order_stats: the reporting period is filtered against its
		// configured date column (date_paid by default) and the status subquery relies on it.
		$this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}wc_order_stats ON {$table_name}.order_id = {$wpdb->prefix}wc_order_stats.order_id" );

		$this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}woocommerce_order_items ON {$table_name}.order_id = {$wpdb->prefix}woocommerce_order_items.order_id AND {$wpdb->prefix}woocommerce_order_items.order_item_type = 'tax'" );
		$this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta_rate_id ON itemmeta_rate_id.order_item_id = {$wpdb->prefix}woocommerce_order_items.order_item_id AND itemmeta_rate_id.meta_key = 'rate_id'" );
		$this->subquery->add_sql_clause( 'join', "JOIN {$wpdb->prefix}woocommerce_order_itemmeta itemmeta_rate_percent ON itemmeta_rate_percent.order_item_id = {$wpdb->prefix}woocommerce_order_items.order_item_id AND itemmeta_rate_percent.meta_key = 'rate_percent'" );
	}

	/**
	 * Updates the database query with parameters used for Taxes report: categories and order status.
	 *
	 * @see Automattic\WooCommerce\Admin\API\Reports\Taxes\Stats\DataStore::update_sql_query_params()
	 * @param array $query_args Query arguments supplied by the user.
	 */
	protected function add_sql_query_params( $query_args ) {
		global $wpdb;

		$order_tax_lookup_table = self::get_db_table_name();
		$order_stats_table      = $wpdb->prefix . 'wc_order_stats';

		// Filter the reporting period against the configured date type on wc_order_stats
		// (date_paid by default) rather than the lookup's date_created, so the Taxes report
		// reconciles with the Orders and Revenue reports.
		$this->add_time_period_sql_params( $query_args, $order_stats_table );
		$this->get_limit_sql_params( $query_args );
		$this->add_order_by_sql_params( $query_args );
		$order_status_filter = $this->get_status_subquery( $query_args );
		$this->add_from_sql_params( $query_args, $order_status_filter );

		$this->subquery->add_sql_clause( 'where', "AND itemmeta_rate_id.meta_value = {$order_tax_lookup_table}.tax_rate_id" );

		/*
		 * Narrow the rate match to the single tax line the row was written for. The rate id on its
		 * own fans one lookup row out across every line of the order that shares it, which is how
		 * an order carrying several lines on one rate id came to report the wrong tax.
		 *
		 * Rows recorded before the lookup held one row per tax order item sit at the column's zero
		 * default and have no line to narrow to, so they go on matching the rate id alone, which is
		 * how the report read them all along, for as long as no per-line row has replaced them.
		 * See get_legacy_row_condition(). OrderTaxLookupMigrator rebuilds them in the
		 * background.
		 */
		$this->subquery->add_sql_clause( 'where', "AND ( {$order_tax_lookup_table}.order_item_id = {$wpdb->prefix}woocommerce_order_items.order_item_id OR " . self::get_legacy_row_condition() . ' )' );

		if ( isset( $query_args['taxes'] ) && ! empty( $query_args['taxes'] ) ) {
			$allowed_taxes = self::get_filtered_ids( $query_args, 'taxes' );
			$this->subquery->add_sql_clause( 'where', "AND {$order_tax_lookup_table}.tax_rate_id IN ({$allowed_taxes})" );
		}

		// The tax order items are already joined here, so this reads their names directly rather
		// than through the EXISTS check the stats queries need.
		$location_filter = self::get_location_filter_condition( $query_args );

		if ( '' !== $location_filter ) {
			$this->subquery->add_sql_clause( 'where', "AND {$location_filter}" );
		}

		if ( $order_status_filter ) {
			$this->subquery->add_sql_clause( 'where', "AND ( {$order_status_filter} )" );
		}
	}

	/**
	 * Get the default query arguments to be used by get_data().
	 * These defaults are only partially applied when used via REST API, as that has its own defaults.
	 *
	 * @override ReportsDataStore::get_default_query_vars()
	 *
	 * @return array Query parameters.
	 */
	public function get_default_query_vars() {
		$defaults                      = parent::get_default_query_vars();
		$defaults['orderby']           = 'tax_rate_id';
		$defaults['taxes']             = array();
		$defaults['location_includes'] = '';
		$defaults['location_excludes'] = '';

		return $defaults;
	}

	/**
	 * Returns the report data based on normalized parameters.
	 * Will be called by `get_data` if there is no data in cache.
	 *
	 * @override ReportsDataStore::get_noncached_data()
	 *
	 * @see get_data
	 * @param array $query_args Query parameters.
	 * @return stdClass|WP_Error Data object `{ totals: *, intervals: array, total: int, pages: int, page_no: int }`, or error.
	 */
	public function get_noncached_data( $query_args ) {
		global $wpdb;

		$this->initialize_queries();

		$data = (object) array(
			'data'    => array(),
			'total'   => 0,
			'pages'   => 0,
			'page_no' => 0,
		);

		// While a taxable amount column is missing its report column is unset, so ordering
		// by it would be a SQL error. Fall back to the default order.
		$orderby = $query_args['orderby'] ?? '';
		if ( in_array( $orderby, array( 'taxable_amount', 'order_taxable_amount', 'shipping_taxable_amount' ), true ) && ! isset( $this->report_columns[ $orderby ] ) ) {
			$query_args['orderby'] = 'tax_rate_id';
		}

		$this->add_sql_query_params( $query_args );
		$params = $this->get_limit_params( $query_args );

		// The selected tax codes stand in for the row count, but only while nothing else can drop
		// a row. A location filter can, so counting them would report pages that hold nothing.
		$counts_the_selected_taxes = isset( $query_args['taxes'] )
			&& is_array( $query_args['taxes'] )
			&& ! empty( $query_args['taxes'] )
			&& '' === self::get_location_filter_condition( $query_args );

		if ( $counts_the_selected_taxes ) {
			$total_results = count( $query_args['taxes'] );
			$total_pages   = (int) ceil( $total_results / $params['per_page'] );
		} else {
			$db_records_count = (int) $wpdb->get_var(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- cache ok, DB call ok, unprepared SQL ok.
				"SELECT COUNT(*) FROM ( {$this->subquery->get_query_statement()} ) AS tt"
			);

			$total_results = $db_records_count;
			$total_pages   = (int) ceil( $db_records_count / $params['per_page'] );

			if ( $query_args['page'] < 1 || $query_args['page'] > $total_pages ) {
				return $data;
			}
		}

		$this->subquery->clear_sql_clause( 'select' );
		$this->subquery->add_sql_clause( 'select', $this->selected_columns( $query_args ) );
		if ( in_array( $query_args['orderby'], array( 'total_tax', 'order_tax', 'shipping_tax', 'taxable_amount', 'order_taxable_amount', 'shipping_taxable_amount', 'orders_count' ), true ) ) {
			$this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) . ', tax_rate_id' );
		} else {
			$this->subquery->add_sql_clause( 'order_by', $this->get_sql_clause( 'order_by' ) );
		}
		$this->subquery->add_sql_clause( 'limit', $this->get_sql_clause( 'limit' ) );

		$taxes_query = $this->subquery->get_query_statement();

		$tax_data = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- cache ok, DB call ok, unprepared SQL ok.
			$taxes_query,
			ARRAY_A
		);

		if ( null === $tax_data ) {
			return $data;
		}

		$tax_data = array_map( array( $this, 'drop_unknown_taxable_amount_parts' ), $tax_data );
		$tax_data = array_map( array( $this, 'cast_numbers' ), $tax_data );
		$data     = (object) array(
			'data'    => $tax_data,
			'total'   => $total_results,
			'pages'   => $total_pages,
			'page_no' => (int) $query_args['page'],
		);

		return $data;
	}

	/**
	 * Drop the taxable amount parts selected as NULL, before the float cast turns them into zeros.
	 *
	 * @param array $row Single report row.
	 * @return array
	 */
	private function drop_unknown_taxable_amount_parts( $row ) {
		foreach ( array( 'order_taxable_amount', 'shipping_taxable_amount' ) as $column ) {
			if ( array_key_exists( $column, $row ) && null === $row[ $column ] ) {
				unset( $row[ $column ] );
			}
		}

		return $row;
	}

	/**
	 * Maps ordering specified by the user to columns in the database/fields in the data.
	 *
	 * @override ReportsDataStore::normalize_order_by()
	 *
	 * @param string $order_by Sorting criterion.
	 * @return string
	 */
	protected function normalize_order_by( $order_by ) {
		global $wpdb;

		if ( 'tax_code' === $order_by ) {
			return "{$wpdb->prefix}woocommerce_order_items.order_item_name";
		} elseif ( 'rate' === $order_by ) {
			return 'tax_rate';
		}

		return $order_by;
	}

	/**
	 * Cache of lookup_is_keyed_by_order_item(). Only `true` sticks: the re-key can land while a
	 * request runs (the "Verify base database tables" tool re-keys right before the tools list
	 * re-renders), and a cached `false` would outlive it.
	 *
	 * @var bool|null
	 */
	private static $lookup_keyed_by_order_item = null;

	/**
	 * Whether the lookup's primary key includes the tax order item.
	 *
	 * The re-key in `WC_Install::create_tables()` can fail on a large store, and dbDelta adds the
	 * `order_item_id` column either way. Writing a real tax order item id into a table still keyed
	 * on (order_id, tax_rate_id) collapses the lines sharing a rate into one row that the report
	 * then matches to a single line, which reads worse than it did before the column existed.
	 *
	 * `OrderTaxLookupMigrator` reads this too: a rebuild over such a table would write every row
	 * back at zero while stepping the cursor past it, so it waits instead.
	 *
	 * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
	 * @since 11.2.0
	 *
	 * @return bool
	 */
	public static function lookup_is_keyed_by_order_item(): bool {
		global $wpdb;

		if ( true !== self::$lookup_keyed_by_order_item ) {
			$table_name = self::get_db_table_name();
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
			self::$lookup_keyed_by_order_item = (bool) $wpdb->get_var( "SHOW KEYS FROM `{$table_name}` WHERE Key_name = 'PRIMARY' AND Column_name = 'order_item_id'" );
		}

		return self::$lookup_keyed_by_order_item;
	}

	/**
	 * SQL condition matching a lookup row at the tax order item column's zero default that the
	 * reports should still read.
	 *
	 * A row at zero stands in for every tax line of its order that shares its rate, which is how
	 * the report read the lookup before the column existed. Rows written per tax line say the same
	 * thing line by line, so an order holding both shapes for one rate reports that rate twice.
	 * The per-line rows are the ones the sync maintains, so a legacy row they already cover is
	 * left out.
	 *
	 * A store ends up holding both shapes when code outside WooCommerce writes the lookup on the
	 * (order_id, tax_rate_id) key the table was released with: such a write lands on the zero
	 * default instead of replacing the rows WooCommerce holds for the order. A prune that could
	 * not run in `sync_order_taxes()` leaves the same shape behind.
	 *
	 * Once the lookup is keyed by tax order item it only holds rows at zero until the rebuild has
	 * been through it, so the check costs nothing on a store that is through it:
	 * `order_item_id > 0` is true and the subquery is never reached. It is paid for while the
	 * rebuild runs, where every row at zero takes an index lookup to find no per-line row beside
	 * it. Measured on a 2M row lookup over a 30 day report, that is 177ms against 595ms, and it
	 * goes away as the rebuild does.
	 *
	 * @internal For exclusive usage of WooCommerce core, backwards compatibility not guaranteed.
	 * @since 11.2.0
	 *
	 * @return string
	 */
	public static function get_legacy_row_condition(): string {
		$table_name = self::get_db_table_name();

		// Nothing can have replaced a row while the lookup is still keyed on
		// (order_id, tax_rate_id): the sync writes every row at zero and `OrderTaxLookupMigrator`
		// waits for the re-key. Unlike a rebuild that is still queued, that state does not clear
		// itself, so the subquery would read the report range on every uncached report and come
		// back empty for good.
		if ( ! self::lookup_is_keyed_by_order_item() ) {
			return "{$table_name}.order_item_id = 0";
		}

		return "( {$table_name}.order_item_id = 0 AND NOT EXISTS ( SELECT 1 FROM {$table_name} per_line WHERE per_line.order_id = {$table_name}.order_id AND per_line.tax_rate_id = {$table_name}.tax_rate_id AND per_line.order_item_id > 0 ) )";
	}

	/**
	 * Create or update an entry in the wc_order_tax_lookup table for an order.
	 *
	 * Writes one row per tax order item, in a single statement so that a write that does not land
	 * rebuilds none of the order's tax lines rather than some of them, then drops the rows the
	 * order held before and no longer carries.
	 *
	 * @param int $order_id Order ID.
	 * @return int|bool Returns -1 if order won't be processed, or a boolean indicating processing success.
	 */
	public static function sync_order_taxes( $order_id ) {
		global $wpdb;

		$order = wc_get_order( $order_id );

		// An order with no creation date has nothing to date its lookup rows by, and
		// `WC_Data::set_date_prop()` leaves the date null for a zero datetime, not only for a
		// missing one. `OrdersScheduler::import()` leaves such an order out of the reports for the
		// same reason.
		if ( ! $order || ! $order->get_date_created( 'edit' ) ) {
			return -1;
		}

		$table_name   = self::get_db_table_name();
		$date_created = $order->get_date_created( 'edit' )->date( TimeInterval::$sql_datetime_format );
		/**
		 * Tax line items of the order.
		 *
		 * @var \WC_Order_Item_Tax[] $tax_items
		 */
		$tax_items     = $order->get_items( OrderItemType::TAX );
		$keyed_by_item = self::lookup_is_keyed_by_order_item();

		// Read the rows the order already holds, so that the prune below names the rows this sync
		// found, the way the Products and Coupons stores do. Deleting everything outside the
		// snapshot instead would let two syncs that read different tax lines delete each other's
		// rows and leave the order with none.
		$existing_rows = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is not user input.
				"SELECT tax_rate_id, order_item_id FROM {$table_name} WHERE order_id = %d",
				$order->get_id()
			),
			ARRAY_A
		);

		// Nothing has been written yet, so the order keeps the rows it came in with. Carrying on
		// would prune against an empty snapshot, which leaves every row the order no longer
		// carries in place for the reports to go on counting.
		if ( $wpdb->last_error ) {
			wc_get_logger()->error(
				"Could not read the analytics tax lookup rows of order {$order->get_id()}. The order keeps the rows it had and reports the way it did before.",
				array( 'source' => 'wc-order-tax-lookup' )
			);

			return false;
		}

		$stale  = array();
		$rows   = array();
		$values = array();

		foreach ( $existing_rows as $existing_row ) {
			$stale[ $existing_row['tax_rate_id'] . '-' . $existing_row['order_item_id'] ] = array(
				(int) $existing_row['tax_rate_id'],
				(int) $existing_row['order_item_id'],
			);
		}

		// Guard against a column not existing yet: a write naming an unknown column fails
		// whole, which would silently drop the order from the Taxes report.
		$has_taxable_amount_column = static::has_taxable_amount_column();
		$has_split_columns         = static::has_taxable_amount_split_columns();
		$taxable_amounts           = array();
		$taxable_amount_parts      = array();

		// Also skip orders without tax lines: computing bases would hydrate every
		// line item, fee and shipping row for a write that never happens.
		if ( $has_taxable_amount_column && ! empty( $tax_items ) ) {
			// A refund's tax items re-derive the compound flag from the live rate, which can
			// be gone by refund time; the parent's tax items carry the flags as charged, and
			// the refund base must mirror them or a refunded order stops netting to zero.
			$flag_items = $tax_items;
			if ( $order instanceof \WC_Order_Refund ) {
				$parent = wc_get_order( $order->get_parent_id() );
				if ( $parent ) {
					/**
					 * Tax line items of the parent order.
					 *
					 * @var \WC_Order_Item_Tax[] $parent_tax_items
					 */
					$parent_tax_items = $parent->get_items( OrderItemType::TAX );
					if ( ! empty( $parent_tax_items ) ) {
						$flag_items = $parent_tax_items;
					}
				}
			}

			$compound_rate_ids = array();
			foreach ( $flag_items as $tax_item ) {
				if ( $tax_item->get_compound() ) {
					$compound_rate_ids[] = $tax_item->get_rate_id();
				}
			}
			// Both stay called so a subclass overriding either one keeps working.
			$taxable_amounts = static::get_taxable_amounts_by_rate( $order, $compound_rate_ids );

			if ( $has_split_columns ) {
				$taxable_amount_parts = static::get_taxable_amount_parts_by_rate( $order, $compound_rate_ids );
			}
		}

		foreach ( $tax_items as $tax_item ) {
			// Leaving the column at zero on a table the re-key never reached keeps the row in the
			// shape the released report reads, rather than collapsing the order's tax lines into
			// one row the report matches to a single line.
			$order_item_id = $keyed_by_item ? $tax_item->get_id() : 0;
			$tax_rate_id   = (int) $tax_item->get_rate_id();

			// A row this sync is about to write is not stale. The key is the rate and the item
			// together, so a line whose rate id has changed leaves behind the row it held before.
			unset( $stale[ $tax_rate_id . '-' . $order_item_id ] );

			array_push(
				$values,
				$order->get_id(),
				$date_created,
				$tax_rate_id,
				$order_item_id,
				$tax_item->get_shipping_tax_total(),
				$tax_item->get_tax_total(),
				(float) $tax_item->get_tax_total() + (float) $tax_item->get_shipping_tax_total()
			);

			$placeholders = '(%d, %s, %d, %d, %f, %f, %f';

			// When tax lines share a rate only the first row carries the rate's bases. On an
			// unkeyed table the lines collapse into one row, so every write carries them.
			if ( $has_taxable_amount_column ) {
				$values[]      = $taxable_amounts[ $tax_rate_id ] ?? 0;
				$placeholders .= ', %f';
				if ( $keyed_by_item ) {
					unset( $taxable_amounts[ $tax_rate_id ] );
				}
			}

			if ( $has_split_columns ) {
				$parts         = $taxable_amount_parts[ $tax_rate_id ] ?? array();
				$values[]      = $parts['order'] ?? 0;
				$values[]      = $parts['shipping'] ?? 0;
				$placeholders .= ', %f, %f';
				if ( $keyed_by_item ) {
					unset( $taxable_amount_parts[ $tax_rate_id ] );
				}
			}

			$rows[] = $placeholders . ')';
		}

		// One statement for the whole order. Rebuilding only some of its lines would leave a row
		// still on the order item column's zero default beside the rows written next to it, and
		// that row stands in for every line of the order sharing its rate, so the reports would
		// count those lines twice.
		if ( $rows ) {
			$columns = 'order_id, date_created, tax_rate_id, order_item_id, shipping_tax, order_tax, total_tax';
			if ( $has_taxable_amount_column ) {
				$columns .= ', taxable_amount';
			}
			if ( $has_split_columns ) {
				$columns .= ', order_taxable_amount, shipping_taxable_amount';
			}

			$written = $wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Table name is not user input, and the value placeholders are built above, one set per tax line.
					"REPLACE INTO {$table_name} ({$columns}) VALUES " . implode( ', ', $rows ),
					$values
				)
			);

			if ( false === $written ) {
				wc_get_logger()->error(
					"Could not write the analytics tax lookup rows of order {$order->get_id()}. The order keeps the rows it had and reports the way it did before.",
					array( 'source' => 'wc-order-tax-lookup' )
				);

				return false;
			}
		}

		// Drop the rows the order came in with that it no longer carries, which includes the rows
		// written before the order item column existed, since those sit at zero. Prune only once
		// the writes have landed, so a write that did not land leaves the order with the rows it
		// came in with.
		if ( $stale ) {
			$keys       = array();
			$key_values = array( $order->get_id() );

			foreach ( $stale as $stale_key ) {
				$keys[] = '(%d, %d)';
				array_push( $key_values, $stale_key[0], $stale_key[1] );
			}

			$deleted = $wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- Table name is not user input, and the key placeholders are built above, one pair per stale row.
					"DELETE FROM {$table_name} WHERE order_id = %d AND (tax_rate_id, order_item_id) IN (" . implode( ', ', $keys ) . ')',
					$key_values
				)
			);

			// A row the order no longer carries goes on being counted by the reports, so a prune
			// that failed is not a sync that succeeded.
			if ( false === $deleted ) {
				wc_get_logger()->error(
					"Could not drop the analytics tax lookup rows order {$order->get_id()} no longer carries. Its old rows stand beside the rows just written, so the reports count those tax lines twice until the order is synced again.",
					array( 'source' => 'wc-order-tax-lookup' )
				);

				return false;
			}
		}

		foreach ( $tax_items as $tax_item ) {
			/**
			 * Fires when tax's reports are updated.
			 *
			 * @param int $tax_rate_id Tax Rate ID.
			 * @param int $order_id    Order ID.
			 *
			 * @since 4.0.0
			 */
			do_action( 'woocommerce_analytics_update_tax', $tax_item->get_rate_id(), $order->get_id() );
		}

		return true;
	}

	/**
	 * Sum the net totals of the order parts (line items, fees, shipping) each tax rate applied to.
	 *
	 * @since 11.2.0
	 *
	 * @param \WC_Order|\WC_Order_Refund $order             Order object.
	 * @param int[]                      $compound_rate_ids Ids of the order's compound tax rates.
	 * @return array Map of tax rate id => taxable amount.
	 */
	protected static function get_taxable_amounts_by_rate( $order, $compound_rate_ids = array() ) {
		return array_map(
			static function ( $parts ) {
				return $parts['order'] + $parts['shipping'];
			},
			static::get_taxable_amount_parts_by_rate( $order, $compound_rate_ids )
		);
	}

	/**
	 * Sum the net totals each tax rate applied to, split into the order part (line items and
	 * fees) and the shipping part.
	 *
	 * Computed from the items rather than derived from the tax amounts, so zero-rated
	 * sales still record the base amount they were taxed on. A compound rate is applied
	 * on top of the other taxes of the same item, so its base includes them.
	 *
	 * @since 11.3.0
	 *
	 * @param \WC_Order|\WC_Order_Refund $order             Order object.
	 * @param int[]                      $compound_rate_ids Ids of the order's compound tax rates.
	 * @return array Map of tax rate id => `array( 'order' => float, 'shipping' => float )`.
	 */
	protected static function get_taxable_amount_parts_by_rate( $order, $compound_rate_ids = array() ) {
		$amounts = array();
		/**
		 * Taxable line items of the order.
		 *
		 * @var \WC_Order_Item_Product[]|\WC_Order_Item_Fee[]|\WC_Order_Item_Shipping[] $items
		 */
		$items = $order->get_items( array( OrderItemType::LINE_ITEM, OrderItemType::FEE, OrderItemType::SHIPPING ) );

		foreach ( $items as $item ) {
			$taxes = $item->get_taxes();
			if ( empty( $taxes['total'] ) || ! is_array( $taxes['total'] ) ) {
				continue;
			}

			$part = OrderItemType::SHIPPING === $item->get_type() ? 'shipping' : 'order';

			$non_compound_tax = 0.0;
			foreach ( $taxes['total'] as $rate_id => $tax ) {
				if ( is_numeric( $tax ) && ! in_array( (int) $rate_id, $compound_rate_ids, true ) ) {
					$non_compound_tax += (float) $tax;
				}
			}

			// Mirror WC_Tax::calc_exclusive_tax(): a compound rate is applied over the item
			// total plus all non-compound taxes plus the compound taxes applied before it.
			// Assumes the item tax data keeps core's rate order (compound rates after the
			// rates they compound over); a third-party engine writing another order would
			// only mis-split the base between multiple compound rates, not the total.
			$compound_running_tax = 0.0;
			foreach ( $taxes['total'] as $rate_id => $tax ) {
				// Admin saves without recalculating store '' for rates that never applied
				// to the item. Numeric zero still counts (zero-rated sales).
				if ( ! is_numeric( $tax ) ) {
					continue;
				}

				$base = (float) $item->get_total();
				if ( in_array( (int) $rate_id, $compound_rate_ids, true ) ) {
					$base                 += $non_compound_tax + $compound_running_tax;
					$compound_running_tax += (float) $tax;
				}

				if ( ! isset( $amounts[ $rate_id ] ) ) {
					$amounts[ $rate_id ] = array(
						'order'    => 0.0,
						'shipping' => 0.0,
					);
				}

				$amounts[ $rate_id ][ $part ] += $base;
			}
		}

		return $amounts;
	}

	/**
	 * Clean taxes data when an order is deleted.
	 *
	 * @param int $order_id Order ID.
	 */
	public static function sync_on_order_delete( $order_id ) {
		global $wpdb;

		$wpdb->delete( self::get_db_table_name(), array( 'order_id' => $order_id ) );

		/**
		 * Fires when tax's reports are removed from database.
		 *
		 * @param int $tax_rate_id Tax Rate ID.
		 * @param int $order_id    Order ID.
		 */
		do_action( 'woocommerce_analytics_delete_tax', 0, $order_id );

		ReportsCache::invalidate();
	}

	/**
	 * Initialize query objects.
	 */
	protected function initialize_queries() {
		global $wpdb;

		$this->clear_all_clauses();
		$this->subquery = new SqlQuery( $this->context . '_subquery' );
		$this->subquery->add_sql_clause( 'select', self::get_db_table_name() . '.tax_rate_id' );
		$this->subquery->add_sql_clause( 'from', self::get_db_table_name() );
		$this->subquery->add_sql_clause( 'group_by', self::get_db_table_name() . '.tax_rate_id' );
		$this->subquery->add_sql_clause( 'group_by', ", {$wpdb->prefix}woocommerce_order_items.order_item_name, itemmeta_rate_percent.meta_value" );
	}
}