ery( $wpdb->prepare( "DELETE FROM $wpdb->wc_category_lookup WHERE category_tree_id = %d AND category_id IN ({$id_list})", $ancestor ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}

	/**
	 * Updates lookup table data for a category by ID.
	 *
	 * @param int $category_id Category ID to update.
	 */
	protected function update( $category_id ) {
		global $wpdb;

		$ancestors    = get_ancestors( $category_id, 'product_cat', 'taxonomy' );
		$children     = get_term_children( $category_id, 'product_cat' );
		$inserts      = array();
		$inserts[]    = $this->get_insert_sql( $category_id, $category_id );
		$children_ids = array_map( 'intval', array_unique( array_filter( $children ) ) );

		foreach ( $ancestors as $ancestor ) {
			$inserts[] = $this->get_insert_sql( $category_id, $ancestor );

			foreach ( $children_ids as $child_category_id ) {
				$inserts[] = $this->get_insert_sql( $child_category_id, $ancestor );
			}
		}

		$insert_string = implode( ',', $inserts );

		$wpdb->query( "INSERT IGNORE INTO $wpdb->wc_category_lookup (category_id, category_tree_id) VALUES {$insert_string}" ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Get category lookup table values to insert.
	 *
	 * @param int $category_id Category ID to insert.
	 * @param int $category_tree_id Tree to insert into.
	 * @return string
	 */
	protected function get_insert_sql( $category_id, $category_tree_id ) {
		global $wpdb;
		return $wpdb->prepare( '(%d,%d)', $category_id, $category_tree_id );
	}

	/**
	 * Used to construct insert query recursively.
	 *
	 * @param  array $inserts Array of data to insert.
	 * @param  array $terms   Terms to insert.
	 * @param  array $parents Parent IDs the terms belong to.
	 */
	protected function get_term_insert_values( &$inserts, $terms, $parents = array() ) {
		foreach ( $terms as $term ) {
			$insert_parents = array_merge( array( $term['term_id'] ), $parents );

			foreach ( $insert_parents as $parent ) {
				$inserts[] = array(
					$parent,
					$term['term_id'],
				);
			}

			$this->get_term_insert_values( $inserts, $term['descendants'], $insert_parents );
		}
	}

	/**
	 * Convert flat terms array into nested array.
	 *
	 * @param array   $hierarchy Array to put terms into.
	 * @param array   $terms Array of terms (id=>parent).
	 * @param integer $parent Parent ID.
	 */
	protected function unflatten_terms( &$hierarchy, &$terms, $parent = 0 ) {
		foreach ( $terms as $term_id => $parent_id ) {
			if ( (int) $parent_id === $parent ) {
				$hierarchy[ $term_id ] = array(
					'term_id'     => $term_id,
					'descendants' => array(),
				);
				unset( $terms[ $term_id ] );
			}
		}
		foreach ( $hierarchy as $term_id => $terms_array ) {
			$this->unflatten_terms( $hierarchy[ $term_id ]['descendants'], $terms, $term_id );
		}
	}

	/**
	 * Get category descendants.
	 *
	 * @param int $category_id The category ID to lookup.
	 * @return array
	 */
	protected function get_descendants( $category_id ) {
		global $wpdb;

		return wp_parse_id_list(
			$wpdb->get_col(
				$wpdb->prepare(
					"SELECT category_id FROM $wpdb->wc_category_lookup WHERE category_tree_id = %d",
					$category_id
				)
			)
		);
	}

	/**
	 * Return all ancestor category ids for a category.
	 *
	 * @param int $category_id The category ID to lookup.
	 * @return array
	 */
	protected function get_ancestors( $category_id ) {
		global $wpdb;

		return wp_parse_id_list(
			$wpdb->get_col(
				$wpdb->prepare(
					"SELECT category_tree_id FROM $wpdb->wc_category_lookup WHERE category_id = %d",
					$category_id
				)
			)
		);
	}

	/**
	 * Add category lookup table to $wpdb object.
	 */
	public static function define_category_lookup_tables_in_wpdb() {
		global $wpdb;

		// List of tables without prefixes.
		$tables = array(
			'wc_category_lookup' => 'wc_category_lookup',
		);

		foreach ( $tables as $name => $table ) {
			$wpdb->$name    = $wpdb->prefix . $table;
			$wpdb->tables[] = $table;
		}
	}
}