<?php
/**
 * Report aggregates.
 *
 * A customer report is a set of totals over every order that matched the
 * filters. Building those by loading each order as a WC_Order and reducing them
 * in PHP costs a query pass and a few hundred bytes of object per order, which
 * is what made a long order history slow to open.
 *
 * WooCommerce already keeps the same figures in its Analytics lookup tables, so
 * the aggregates are read straight out of those instead, and the order walk is
 * kept as a fallback for stores where those tables are missing or have not
 * finished importing.
 */

if (!defined('ABSPATH')) {
    exit;
}

/**
 * The shape every report aggregate builder returns.
 */
function acreports_get_empty_report_data() {
    return array(
        'order_ids'                => array(),
        'orders_truncated'         => false,
        'total_orders'             => 0,
        'total_spent'              => 0,
        'total_refunded'           => 0,
        'total_discount'           => 0,
        'total_shipping'           => 0,
        'total_tax'                => 0,
        'largest_order_total'      => 0,
        'smallest_order_total'     => 0,
        'total_products'           => 0,
        'first_order_date'         => null,
        'last_order_date'          => null,
        'first_order_summary'      => null,
        'last_order_summary'       => null,
        'order_timestamps'         => array(),
        'status_amounts'           => array(),
        'payment_method_amounts'   => array(),
        'payment_method_spend'     => array(),
        'payment_method_count'     => array(),
        'product_stats'            => array(),
        'category_stats'           => array(),
        'product_affinity'         => array(),
        'coupon_stats'             => array(),
        'shipping_method_amounts'  => array(),
        'shipping_method_spend'    => array(),
        'shipping_addresses'       => array(),
        'monthly_stats'            => array(),
        'coupon_order_count'       => 0,
        'refunded_order_count'     => 0,
        'cancelled_order_count'    => 0,
        'failed_order_count'       => 0,
        'attribution_origins'      => array(),
        'attribution_campaigns'    => array(),
        'attribution_devices'      => array(),
        'attribution_order_count'  => 0,
    );
}

/**
 * Does a WooCommerce table exist? Memoised, because the answer cannot change
 * within a request and the report asks about several tables.
 */
function acreports_report_table_exists($table) {
    static $exists = array();

    if (isset($exists[$table])) {
        return $exists[$table];
    }

    global $wpdb;
    $table_name = $wpdb->prefix . $table;
    $exists[$table] = $table_name === $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table_name));

    return $exists[$table];
}

/**
 * Are orders stored in WooCommerce's own tables rather than as posts? This
 * decides where the per-order fields that Analytics does not record are read
 * from.
 */
function acreports_report_hpos_enabled() {
    return class_exists('\Automattic\WooCommerce\Utilities\OrderUtil')
        && \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
}

/**
 * Order IDs are bound into IN() lists. A report can cover thousands of orders,
 * so they are queried in chunks rather than one enormous statement.
 */
function acreports_report_id_chunks($order_ids) {
    $chunk_size = max(1, (int) apply_filters('acreports_report_id_chunk_size', 2000));

    return array_chunk(array_values($order_ids), $chunk_size);
}

function acreports_report_in_placeholders($order_ids) {
    return implode(', ', array_fill(0, count($order_ids), '%d'));
}

/**
 * Turn a GMT datetime string from a lookup table into the same site-timezone
 * WC_DateTime that WC_Order::get_date_created() would have returned, so dates
 * format identically no matter which path built the report.
 */
function acreports_report_date_from_gmt($gmt_date) {
    if (empty($gmt_date) || '0000-00-00 00:00:00' === $gmt_date) {
        return null;
    }

    try {
        $date = new WC_DateTime($gmt_date, new DateTimeZone('UTC'));
        $date->setTimezone(new DateTimeZone(wc_timezone_string()));
    } catch (Exception $exception) {
        return null;
    }

    return $date;
}

/**
 * The set of orders a report covers.
 *
 * Resolved once as bare IDs. Every aggregate below is scoped to this list, so
 * both builders describe exactly the same orders and neither can drift from
 * what wc_get_orders() would have matched.
 */
function acreports_get_report_order_ids($order_query_args) {
    $max_report_orders = acreports_get_max_report_orders();

    // One more than the cap, so a store that sits exactly on the cap is not
    // reported as truncated when nothing was actually left out.
    $args = array_merge($order_query_args, array(
        'type'   => 'shop_order',
        'return' => 'ids',
        'limit'  => $max_report_orders ? $max_report_orders + 1 : -1,
        'page'   => 1,
    ));

    $order_ids = array_values(array_unique(array_filter(array_map('absint', (array) wc_get_orders($args)))));
    $truncated = false;

    if ($max_report_orders && count($order_ids) > $max_report_orders) {
        $order_ids = array_slice($order_ids, 0, $max_report_orders);
        $truncated = true;
    }

    return array(
        'ids'       => $order_ids,
        'truncated' => $truncated,
    );
}

/**
 * How many orders missing from wc_order_stats will be read individually before
 * the report gives up on the lookup tables altogether.
 *
 * Analytics imports an order a moment after checkout, so a store is normally a
 * handful of orders behind and reading those few directly is cheap. A store
 * that has never run the import is behind by everything, and there the order
 * walk is the faster way round.
 */
function acreports_get_report_max_unsynced_orders() {
    return max(0, (int) apply_filters('acreports_report_max_unsynced_orders', 500));
}

/**
 * Order-level figures from wc_order_stats, keyed by order ID.
 *
 * Only covers the orders Analytics has imported. Returns null if the table
 * cannot be read at all; anything it simply has not caught up with is filled in
 * from the orders themselves by the caller.
 */
function acreports_get_report_order_stats($order_ids) {
    global $wpdb;

    $order_stats_table = $wpdb->prefix . 'wc_order_stats';
    $stats = array();

    foreach (acreports_report_id_chunks($order_ids) as $chunk) {
        $placeholders = acreports_report_in_placeholders($chunk);
        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders holds only %d placeholders, one per order ID, and every value is bound by acreports_prepare_sql().
        $rows = $wpdb->get_results(acreports_prepare_sql(
            "SELECT order_id, status, date_created_gmt, total_sales, tax_total, num_items_sold
            FROM {$order_stats_table}
            WHERE order_id IN ({$placeholders})",
            $chunk
        ), ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        if (!empty($wpdb->last_error) || !is_array($rows)) {
            return null;
        }

        foreach ($rows as $row) {
            $stats[(int) $row['order_id']] = $row;
        }
    }

    return $stats;
}

/**
 * Fill the report's per-order maps for orders Analytics has not imported yet,
 * by reading those orders directly.
 *
 * Keeps a report exact while the lookup tables are catching up, instead of
 * sending the whole thing back to the order walk over a couple of stragglers.
 */
function acreports_fill_report_maps_from_orders($missing_ids, &$order_stats, &$refund_totals, &$order_fields, &$item_names, &$line_items) {
    $address_keys = array('address_1', 'address_2', 'city', 'state', 'postcode', 'country');
    $attribution_keys = acreports_get_report_attribution_meta_keys();
    $orders = wc_get_orders(array(
        'type'    => 'shop_order',
        'include' => array_values($missing_ids),
        'limit'   => count($missing_ids),
    ));

    $orders = is_array($orders) ? $orders : array();
    $loaded = array();

    foreach ($orders as $order) {
        if (is_a($order, 'WC_Abstract_Order')) {
            $loaded[$order->get_id()] = $order;
        }
    }

    // Not every order data store honours an include filter, so anything the
    // query did not hand back is fetched by ID rather than silently dropped.
    foreach ($missing_ids as $missing_id) {
        if (isset($loaded[$missing_id])) {
            continue;
        }

        $order = wc_get_order($missing_id);

        if ($order && is_a($order, 'WC_Abstract_Order')) {
            $loaded[$missing_id] = $order;
        }
    }

    foreach ($loaded as $order_id => $order) {
        if (method_exists($order, 'get_type') && 'shop_order' !== $order->get_type()) {
            continue;
        }

        $order_date = $order->get_date_created();

        $order_stats[$order_id] = array(
            'order_id'         => $order_id,
            'status'           => 'wc-' . $order->get_status(),
            'date_created_gmt' => $order_date ? gmdate('Y-m-d H:i:s', $order_date->getTimestamp()) : '',
            'total_sales'      => (float) $order->get_total(),
            'tax_total'        => (float) $order->get_total_tax(),
            'num_items_sold'   => $order->get_item_count(),
        );

        $refund_totals[$order_id] = method_exists($order, 'get_total_refunded') ? (float) $order->get_total_refunded() : 0;

        $fields = array(
            'payment_method' => method_exists($order, 'get_payment_method') ? (string) $order->get_payment_method() : '',
            'discount_gross' => acreports_get_order_discount_gross($order),
            'shipping_gross' => acreports_get_order_shipping_gross($order),
            'shipping'       => array(),
            'billing'        => array(),
        );

        foreach ($address_keys as $address_key) {
            $shipping_getter = 'get_shipping_' . $address_key;
            $billing_getter = 'get_billing_' . $address_key;
            $fields['shipping'][$address_key] = method_exists($order, $shipping_getter) ? (string) $order->{$shipping_getter}() : '';
            $fields['billing'][$address_key] = method_exists($order, $billing_getter) ? (string) $order->{$billing_getter}() : '';
        }

        foreach ($attribution_keys as $meta_key => $field) {
            $value = $order->get_meta($meta_key);

            if (is_array($value) || is_object($value) || null === $value || '' === $value) {
                continue;
            }

            $fields['attribution_meta'][$meta_key] = $value;
        }

        $order_fields[$order_id] = $fields;

        $item_names[$order_id] = array(
            'shipping' => array($order->get_shipping_method()),
            'coupons'  => $order->get_coupon_codes(),
        );

        $line_items[$order_id] = array();

        foreach ($order->get_items() as $item) {
            $line_items[$order_id][] = array(
                'product_id' => $item->get_product_id(),
                'quantity'   => $item->get_quantity(),
                'total'      => acreports_get_order_item_gross_total($item),
            );
        }
    }
}

/**
 * Total refunded per order.
 *
 * Read from the refunds themselves rather than from the negative rows Analytics
 * writes, so the figure matches WC_Order::get_total_refunded() exactly.
 */
function acreports_get_report_refund_totals($order_ids) {
    global $wpdb;

    $refunds = array();
    $hpos = acreports_report_hpos_enabled();
    $orders_table = $wpdb->prefix . 'wc_orders';

    foreach (acreports_report_id_chunks($order_ids) as $chunk) {
        $placeholders = acreports_report_in_placeholders($chunk);

        if ($hpos) {
            $sql = "SELECT parent_order_id AS order_id, COALESCE(SUM(ABS(total_amount)), 0) AS refunded
                FROM {$orders_table}
                WHERE type = 'shop_order_refund' AND parent_order_id IN ({$placeholders})
                GROUP BY parent_order_id";
        } else {
            $sql = "SELECT p.post_parent AS order_id, COALESCE(SUM(ABS(pm.meta_value)), 0) AS refunded
                FROM {$wpdb->posts} p
                INNER JOIN {$wpdb->postmeta} pm ON pm.post_id = p.ID AND pm.meta_key = '_refund_amount'
                WHERE p.post_type = 'shop_order_refund' AND p.post_parent IN ({$placeholders})
                GROUP BY p.post_parent";
        }

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders holds only %d placeholders, one per order ID, and every value is bound by acreports_prepare_sql().
        $rows = $wpdb->get_results(acreports_prepare_sql($sql, $chunk), ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        foreach ((array) $rows as $row) {
            $refunds[(int) $row['order_id']] = (float) $row['refunded'];
        }
    }

    return $refunds;
}

/**
 * The attribution meta keys a report reads, as stored against an order.
 */
function acreports_get_report_attribution_meta_keys() {
    $prefix = acreports_get_order_attribution_meta_prefix();
    $fields = array(
        'source_type',
        'referrer',
        'utm_campaign',
        'utm_source',
        'utm_medium',
        'utm_content',
        'utm_id',
        'utm_term',
        'utm_source_platform',
        'utm_creative_format',
        'utm_marketing_tactic',
        'session_entry',
        'session_start_time',
        'session_pages',
        'session_count',
        'device_type',
    );

    $keys = array();

    foreach ($fields as $field) {
        $keys[$prefix . $field] = $field;
    }

    return $keys;
}

/**
 * Payment method, discount and shipping totals, and the addresses the report
 * groups by. None of these are in the Analytics tables, so they are read in
 * bulk from wherever orders are stored.
 */
function acreports_get_report_order_fields($order_ids) {
    global $wpdb;

    $fields = array();
    $attribution_keys = acreports_get_report_attribution_meta_keys();

    if (acreports_report_hpos_enabled()) {
        $orders_table = $wpdb->prefix . 'wc_orders';
        $operational_table = $wpdb->prefix . 'wc_order_operational_data';
        $addresses_table = $wpdb->prefix . 'wc_order_addresses';
        $meta_table = $wpdb->prefix . 'wc_orders_meta';

        foreach (acreports_report_id_chunks($order_ids) as $chunk) {
            $placeholders = acreports_report_in_placeholders($chunk);

            // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders holds only %d placeholders, one per order ID, and every value is bound by acreports_prepare_sql().
            $rows = $wpdb->get_results(acreports_prepare_sql(
                "SELECT o.id AS order_id,
                    o.payment_method,
                    od.shipping_total_amount,
                    od.shipping_tax_amount,
                    od.discount_total_amount,
                    od.discount_tax_amount
                FROM {$orders_table} o
                LEFT JOIN {$operational_table} od ON od.order_id = o.id
                WHERE o.id IN ({$placeholders})",
                $chunk
            ), ARRAY_A);

            foreach ((array) $rows as $row) {
                $order_id = (int) $row['order_id'];
                $fields[$order_id]['payment_method'] = (string) $row['payment_method'];
                $fields[$order_id]['discount_gross'] = (float) $row['discount_total_amount'] + (float) $row['discount_tax_amount'];
                $fields[$order_id]['shipping_gross'] = (float) $row['shipping_total_amount'] + (float) $row['shipping_tax_amount'];
            }

            $address_rows = $wpdb->get_results(acreports_prepare_sql(
                "SELECT order_id, address_type, address_1, address_2, city, state, postcode, country
                FROM {$addresses_table}
                WHERE order_id IN ({$placeholders})",
                $chunk
            ), ARRAY_A);

            foreach ((array) $address_rows as $row) {
                $type = 'shipping' === $row['address_type'] ? 'shipping' : 'billing';
                $fields[(int) $row['order_id']][$type] = array(
                    'address_1' => (string) $row['address_1'],
                    'address_2' => (string) $row['address_2'],
                    'city'      => (string) $row['city'],
                    'state'     => (string) $row['state'],
                    'postcode'  => (string) $row['postcode'],
                    'country'   => (string) $row['country'],
                );
            }

            $key_placeholders = implode(', ', array_fill(0, count($attribution_keys), '%s'));
            // Ordered so the oldest row for a key comes first, which is what
            // reading a single meta value off an order gives you. An order should
            // never carry the same attribution key twice, and where one does the
            // order tables are read without a tie break, so WooCommerce itself
            // has no settled answer. Taking the first recorded value at least
            // makes the report's answer the same one every time.
            $meta_rows = $wpdb->get_results(acreports_prepare_sql(
                "SELECT order_id, meta_key, meta_value
                FROM {$meta_table}
                WHERE order_id IN ({$placeholders}) AND meta_key IN ({$key_placeholders})
                ORDER BY id ASC",
                array_merge($chunk, array_keys($attribution_keys))
            ), ARRAY_A);
            // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

            foreach ((array) $meta_rows as $row) {
                if (isset($fields[(int) $row['order_id']]['attribution_meta'][$row['meta_key']])) {
                    continue;
                }

                $fields[(int) $row['order_id']]['attribution_meta'][$row['meta_key']] = $row['meta_value'];
            }
        }

        return $fields;
    }

    // Legacy storage keeps all of it in postmeta, so one pivot covers the lot.
    $address_keys = array('address_1', 'address_2', 'city', 'state', 'postcode', 'country');
    $meta_map = array(
        '_payment_method'     => 'payment_method',
        '_order_shipping'     => 'shipping_total',
        '_order_shipping_tax' => 'shipping_tax',
        '_cart_discount'      => 'discount_total',
        '_cart_discount_tax'  => 'discount_tax',
    );

    foreach ($address_keys as $address_key) {
        $meta_map['_shipping_' . $address_key] = 'shipping.' . $address_key;
        $meta_map['_billing_' . $address_key] = 'billing.' . $address_key;
    }

    $wanted_keys = array_merge(array_keys($meta_map), array_keys($attribution_keys));

    foreach (acreports_report_id_chunks($order_ids) as $chunk) {
        $placeholders = acreports_report_in_placeholders($chunk);
        $key_placeholders = implode(', ', array_fill(0, count($wanted_keys), '%s'));

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders and $key_placeholders hold only placeholders, and every value is bound by acreports_prepare_sql().
        // Ordered so the oldest row for a key comes first, because reading a
        // single meta value off an order returns the first one recorded.
        $rows = $wpdb->get_results(acreports_prepare_sql(
            "SELECT post_id AS order_id, meta_key, meta_value
            FROM {$wpdb->postmeta}
            WHERE post_id IN ({$placeholders}) AND meta_key IN ({$key_placeholders})
            ORDER BY meta_id ASC",
            array_merge($chunk, $wanted_keys)
        ), ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        foreach ((array) $rows as $row) {
            $order_id = (int) $row['order_id'];
            $meta_key = $row['meta_key'];

            if (isset($attribution_keys[$meta_key])) {
                if (!isset($fields[$order_id]['attribution_meta'][$meta_key])) {
                    $fields[$order_id]['attribution_meta'][$meta_key] = $row['meta_value'];
                }
                continue;
            }

            if (!isset($meta_map[$meta_key])) {
                continue;
            }

            $target = $meta_map[$meta_key];

            if (false !== strpos($target, '.')) {
                list($address_type, $address_key) = explode('.', $target);

                if (!isset($fields[$order_id][$address_type][$address_key])) {
                    $fields[$order_id][$address_type][$address_key] = (string) $row['meta_value'];
                }
                continue;
            }

            if (!isset($fields[$order_id][$target])) {
                $fields[$order_id][$target] = $row['meta_value'];
            }
        }
    }

    foreach ($fields as $order_id => $order_fields) {
        $fields[$order_id]['payment_method'] = isset($order_fields['payment_method']) ? (string) $order_fields['payment_method'] : '';
        $fields[$order_id]['discount_gross'] = (float) (isset($order_fields['discount_total']) ? $order_fields['discount_total'] : 0)
            + (float) (isset($order_fields['discount_tax']) ? $order_fields['discount_tax'] : 0);
        $fields[$order_id]['shipping_gross'] = (float) (isset($order_fields['shipping_total']) ? $order_fields['shipping_total'] : 0)
            + (float) (isset($order_fields['shipping_tax']) ? $order_fields['shipping_tax'] : 0);
    }

    return $fields;
}

/**
 * Order line items the report needs by name rather than by figure: the shipping
 * method each order used, and the coupon codes applied to it.
 */
function acreports_get_report_order_item_names($order_ids) {
    global $wpdb;

    $items_table = $wpdb->prefix . 'woocommerce_order_items';
    $names = array();

    foreach (acreports_report_id_chunks($order_ids) as $chunk) {
        $placeholders = acreports_report_in_placeholders($chunk);

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders holds only %d placeholders, one per order ID, and every value is bound by acreports_prepare_sql().
        $rows = $wpdb->get_results(acreports_prepare_sql(
            "SELECT order_id, order_item_type, order_item_name
            FROM {$items_table}
            WHERE order_id IN ({$placeholders}) AND order_item_type IN ('shipping', 'coupon')
            ORDER BY order_item_id ASC",
            $chunk
        ), ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        foreach ((array) $rows as $row) {
            $order_id = (int) $row['order_id'];

            if ('shipping' === $row['order_item_type']) {
                $names[$order_id]['shipping'][] = (string) $row['order_item_name'];
                continue;
            }

            $names[$order_id]['coupons'][] = (string) $row['order_item_name'];
        }
    }

    return $names;
}

/**
 * A line item's tax, worked out the way WC_Order_Item_Product does.
 *
 * The item's _line_tax meta is rounded to the store's display precision, but the
 * order item object ignores it and re-adds the per rate figures in
 * _line_tax_data, rounding each one only when the store is not set to round tax
 * at subtotal. Reading _line_tax directly would put the product, category and
 * coupon totals a fraction of a unit out per line sold.
 */
function acreports_get_report_line_item_tax($line_tax_data, $round_at_subtotal) {
    $tax_data = maybe_unserialize($line_tax_data);

    if (!is_array($tax_data) || empty($tax_data['total']) || empty($tax_data['subtotal'])) {
        return 0;
    }

    $totals = array_map('wc_format_decimal', $tax_data['total']);

    if ($round_at_subtotal) {
        return (float) array_sum($totals);
    }

    return (float) array_sum(array_map('wc_round_tax_total', $totals));
}

/**
 * The line items of every order in the set, keyed by order ID.
 *
 * Read from the order item tables rather than from wc_order_product_lookup,
 * whose product_net_revenue is rounded to the store's display precision. A line
 * stored as 166.666667 is recorded there as 166.67, which would shift the
 * product, category and coupon totals by a fraction of a unit per line sold.
 */
function acreports_get_report_line_items($order_ids) {
    global $wpdb;

    $items_table = $wpdb->prefix . 'woocommerce_order_items';
    $itemmeta_table = $wpdb->prefix . 'woocommerce_order_itemmeta';
    $round_at_subtotal = 'yes' === get_option('woocommerce_tax_round_at_subtotal');
    $line_items = array();

    foreach (acreports_report_id_chunks($order_ids) as $chunk) {
        $placeholders = acreports_report_in_placeholders($chunk);

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders holds only %d placeholders, one per order ID, and every value is bound by acreports_prepare_sql().
        $rows = $wpdb->get_results(acreports_prepare_sql(
            "SELECT oi.order_id,
                MAX(CASE WHEN oim.meta_key = '_product_id' THEN oim.meta_value END) AS product_id,
                MAX(CASE WHEN oim.meta_key = '_qty' THEN oim.meta_value END) AS quantity,
                MAX(CASE WHEN oim.meta_key = '_line_total' THEN oim.meta_value END) AS line_total,
                MAX(CASE WHEN oim.meta_key = '_line_tax_data' THEN oim.meta_value END) AS line_tax_data
            FROM {$items_table} oi
            INNER JOIN {$itemmeta_table} oim ON oim.order_item_id = oi.order_item_id
            WHERE oi.order_id IN ({$placeholders})
                AND oi.order_item_type = 'line_item'
                AND oim.meta_key IN ('_product_id', '_qty', '_line_total', '_line_tax_data')
            GROUP BY oi.order_id, oi.order_item_id
            ORDER BY oi.order_item_id ASC",
            $chunk
        ), ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        foreach ((array) $rows as $row) {
            $line_items[(int) $row['order_id']][] = array(
                'product_id' => (int) $row['product_id'],
                // Passed through the same filter the order item setter uses, so
                // a store running decimal quantities is counted its way.
                'quantity'   => wc_stock_amount($row['quantity']),
                'total'      => (float) $row['line_total'] + acreports_get_report_line_item_tax($row['line_tax_data'], $round_at_subtotal),
            );
        }
    }

    return $line_items;
}

/**
 * Which of these product IDs still point at a product.
 *
 * An order item keeps the ID of a product that has since been deleted, but the
 * order item object refuses to hand that ID back, so the report has always
 * gathered those lines under a product of 0 and left them out of the products
 * table. Checked in one query here to keep it that way, rather than filling the
 * table with rows that have no name and no link.
 */
function acreports_get_report_valid_product_ids($product_ids) {
    global $wpdb;

    $product_ids = array_values(array_unique(array_filter(array_map('absint', $product_ids))));
    $valid = array();

    if (empty($product_ids)) {
        return $valid;
    }

    foreach (acreports_report_id_chunks($product_ids) as $chunk) {
        $placeholders = acreports_report_in_placeholders($chunk);

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders holds only %d placeholders, one per product ID, and every value is bound by acreports_prepare_sql().
        $rows = $wpdb->get_col(acreports_prepare_sql(
            "SELECT ID FROM {$wpdb->posts} WHERE ID IN ({$placeholders}) AND post_type = 'product'",
            $chunk
        ));
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        foreach ((array) $rows as $product_id) {
            $valid[(int) $product_id] = true;
        }
    }

    return $valid;
}

/**
 * The product_cat term IDs of every product the report touched, in one query
 * rather than a term lookup per product.
 */
function acreports_get_report_product_categories($product_ids) {
    global $wpdb;

    $product_ids = array_values(array_unique(array_filter(array_map('absint', $product_ids))));
    $categories = array();

    if (empty($product_ids)) {
        return $categories;
    }

    foreach (acreports_report_id_chunks($product_ids) as $chunk) {
        $placeholders = acreports_report_in_placeholders($chunk);

        // phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- $placeholders holds only %d placeholders, one per product ID, and every value is bound by acreports_prepare_sql().
        $rows = $wpdb->get_results(acreports_prepare_sql(
            "SELECT tr.object_id AS product_id, tt.term_id
            FROM {$wpdb->term_relationships} tr
            INNER JOIN {$wpdb->term_taxonomy} tt ON tt.term_taxonomy_id = tr.term_taxonomy_id
            WHERE tr.object_id IN ({$placeholders}) AND tt.taxonomy = 'product_cat'
            ORDER BY tt.term_id ASC",
            $chunk
        ), ARRAY_A);
        // phpcs:enable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        foreach ((array) $rows as $row) {
            $categories[(int) $row['product_id']][] = (int) $row['term_id'];
        }
    }

    return $categories;
}

/**
 * Rebuild one order's attribution from raw meta, matching what reading it off
 * the order object would have produced.
 */
function acreports_get_report_order_attribution($attribution_meta, $attribution_keys) {
    $attribution = array();

    foreach ($attribution_keys as $meta_key => $field) {
        if (!isset($attribution_meta[$meta_key])) {
            continue;
        }

        $value = maybe_unserialize($attribution_meta[$meta_key]);

        if (is_array($value) || is_object($value) || null === $value || '' === $value) {
            continue;
        }

        $attribution[$field] = (string) $value;
    }

    if (empty($attribution)) {
        return array();
    }

    $attribution['origin'] = acreports_get_order_attribution_origin_label(
        isset($attribution['source_type']) ? $attribution['source_type'] : '',
        isset($attribution['utm_source']) ? $attribution['utm_source'] : ''
    );

    return $attribution;
}

/**
 * Build the report aggregates from the Analytics lookup tables.
 *
 * Returns null when the tables are missing or do not cover every order in the
 * set, leaving the caller to fall back to walking the orders.
 */
function acreports_build_report_data_from_lookup($order_ids, $is_premium) {
    if (!acreports_report_table_exists('wc_order_stats')) {
        return null;
    }

    // The per-order fields Analytics does not record are read from the order
    // tables directly, so those have to be there too.
    if (acreports_report_hpos_enabled()
        && (!acreports_report_table_exists('wc_orders')
            || !acreports_report_table_exists('wc_order_operational_data')
            || !acreports_report_table_exists('wc_order_addresses'))) {
        return null;
    }

    $order_stats = acreports_get_report_order_stats($order_ids);

    if (null === $order_stats) {
        return null;
    }

    // Anything Analytics has not imported yet is read from the order itself, so
    // long as there are few enough of them to be worth doing that way.
    $missing_ids = array_values(array_diff($order_ids, array_keys($order_stats)));
    $max_unsynced = acreports_get_report_max_unsynced_orders();

    if (count($missing_ids) > $max_unsynced) {
        return null;
    }

    $data = acreports_get_empty_report_data();
    $refund_totals = acreports_get_report_refund_totals($order_ids);
    $order_fields = acreports_get_report_order_fields($order_ids);
    $item_names = acreports_get_report_order_item_names($order_ids);
    $line_items = acreports_get_report_line_items($order_ids);
    $attribution_keys = acreports_get_report_attribution_meta_keys();

    if (!empty($missing_ids)) {
        acreports_fill_report_maps_from_orders($missing_ids, $order_stats, $refund_totals, $order_fields, $item_names, $line_items);

        // An order that could not be read at all is left out rather than
        // counted as an empty one.
        $order_ids = array_values(array_filter($order_ids, function($order_id) use ($order_stats) {
            return isset($order_stats[$order_id]);
        }));
    }

    $line_item_product_ids = array();

    foreach ($line_items as $order_line_items) {
        foreach ($order_line_items as $line_item) {
            $line_item_product_ids[$line_item['product_id']] = true;
        }
    }

    // A line whose product has been deleted counts towards the order totals but
    // not towards any one product, which is how the order item itself reports it.
    $valid_product_ids = acreports_get_report_valid_product_ids(array_keys($line_item_product_ids));

    foreach ($line_items as $line_order_id => $order_line_items) {
        foreach ($order_line_items as $line_index => $line_item) {
            if ($line_item['product_id'] > 0 && !isset($valid_product_ids[$line_item['product_id']])) {
                $line_items[$line_order_id][$line_index]['product_id'] = 0;
            }
        }
    }

    $product_categories = acreports_get_report_product_categories(array_keys($valid_product_ids));
    $data['order_ids'] = $order_ids;

    // Walked in the order the IDs came back in, newest first, so the breakdowns
    // that are shown unsorted list their rows the same way they always have.
    foreach ($order_ids as $order_id) {
        $stats_row = $order_stats[$order_id];
        $fields = isset($order_fields[$order_id]) ? $order_fields[$order_id] : array();

        $order_status = (string) $stats_row['status'];
        $order_status = 0 === strpos($order_status, 'wc-') ? substr($order_status, 3) : $order_status;
        $order_date = acreports_report_date_from_gmt($stats_row['date_created_gmt']);
        $order_total = (float) $stats_row['total_sales'];
        $order_tax = (float) $stats_row['tax_total'];
        $order_items = (int) $stats_row['num_items_sold'];
        $order_refunded = isset($refund_totals[$order_id]) ? $refund_totals[$order_id] : 0;
        $order_discount = isset($fields['discount_gross']) ? (float) $fields['discount_gross'] : 0;
        $order_shipping = isset($fields['shipping_gross']) ? (float) $fields['shipping_gross'] : 0;
        $order_attribution = acreports_get_report_order_attribution(
            isset($fields['attribution_meta']) ? $fields['attribution_meta'] : array(),
            $attribution_keys
        );

        if ($order_date) {
            $data['order_timestamps'][] = $order_date->getTimestamp();
            // date_i18n() formats a supplied timestamp against UTC, so the walk
            // buckets by UTC month. Matched here so both paths agree, and so a
            // bucket's key always matches the label printed beside it.
            $month_key = gmdate('Y-m', $order_date->getTimestamp());

            if (!isset($data['monthly_stats'][$month_key])) {
                $data['monthly_stats'][$month_key] = array(
                    'label'     => date_i18n('M Y', $order_date->getTimestamp()),
                    'orders'    => 0,
                    'gross'     => 0,
                    'refunds'   => 0,
                    'discounts' => 0,
                );
            }

            $data['monthly_stats'][$month_key]['orders'] += 1;
            $data['monthly_stats'][$month_key]['gross'] += $order_total;
            $data['monthly_stats'][$month_key]['refunds'] += $order_refunded;
            $data['monthly_stats'][$month_key]['discounts'] += $order_discount;

            $order_summary = array(
                'id'          => $order_id,
                'date'        => $order_date,
                'total'       => $order_total,
                'items'       => $order_items,
                'status'      => $order_status,
                'attribution' => $order_attribution,
            );

            if (null === $data['last_order_date'] || $order_date->getTimestamp() > $data['last_order_date']->getTimestamp()) {
                $data['last_order_date'] = $order_date;
                $data['last_order_summary'] = $order_summary;
            }

            if (null === $data['first_order_date'] || $order_date->getTimestamp() < $data['first_order_date']->getTimestamp()) {
                $data['first_order_date'] = $order_date;
                $data['first_order_summary'] = $order_summary;
            }
        }

        $data['total_orders']++;
        $data['total_spent'] += $order_total;
        $data['total_refunded'] += $order_refunded;
        $data['total_discount'] += $order_discount;
        $data['total_shipping'] += $order_shipping;
        $data['total_tax'] += $order_tax;
        $data['total_products'] += $order_items;

        $coupons = isset($item_names[$order_id]['coupons']) ? $item_names[$order_id]['coupons'] : array();

        if (!empty($coupons)) {
            $data['coupon_order_count']++;
        }

        if ($order_total > $data['largest_order_total']) {
            $data['largest_order_total'] = $order_total;
        }

        if ($order_total > 0 && (!$data['smallest_order_total'] || $order_total < $data['smallest_order_total'])) {
            $data['smallest_order_total'] = $order_total;
        }

        if (!isset($data['status_amounts'][$order_status])) {
            $data['status_amounts'][$order_status] = 0;
        }
        $data['status_amounts'][$order_status] += 1;

        if ($order_refunded > 0 || 'refunded' === $order_status) {
            $data['refunded_order_count']++;
        }

        if ('cancelled' === $order_status) {
            $data['cancelled_order_count']++;
        }

        if ('failed' === $order_status) {
            $data['failed_order_count']++;
        }

        $payment_method = isset($fields['payment_method']) ? (string) $fields['payment_method'] : '';

        if (!isset($data['payment_method_amounts'][$payment_method])) {
            $data['payment_method_amounts'][$payment_method] = 0;
            $data['payment_method_spend'][$payment_method] = 0;
            $data['payment_method_count'][$payment_method] = 0;
        }
        $data['payment_method_amounts'][$payment_method] += 1;
        $data['payment_method_spend'][$payment_method] += $order_total;
        $data['payment_method_count'][$payment_method] += 1;

        if (!empty($order_attribution)) {
            $data['attribution_order_count']++;

            acreports_add_attribution_bucket($data['attribution_origins'], $order_attribution['origin'], $order_total, array(
                'source_type' => isset($order_attribution['source_type']) ? $order_attribution['source_type'] : '',
            ));

            if ($is_premium) {
                $utm_campaign = isset($order_attribution['utm_campaign']) ? $order_attribution['utm_campaign'] : '';
                $utm_source = isset($order_attribution['utm_source']) ? $order_attribution['utm_source'] : '';
                $utm_medium = isset($order_attribution['utm_medium']) ? $order_attribution['utm_medium'] : '';

                if ('' !== $utm_campaign || '' !== $utm_source || '' !== $utm_medium) {
                    acreports_add_attribution_bucket(
                        $data['attribution_campaigns'],
                        strtolower($utm_campaign . '|' . $utm_source . '|' . $utm_medium),
                        $order_total,
                        array(
                            'campaign' => $utm_campaign,
                            'source'   => $utm_source,
                            'medium'   => $utm_medium,
                        )
                    );
                }

                if (!empty($order_attribution['device_type'])) {
                    acreports_add_attribution_bucket($data['attribution_devices'], $order_attribution['device_type'], $order_total);
                }
            }
        }

        // The product, category and coupon breakdowns are all folded in per line
        // item, so that an order contributes to each of them on exactly the
        // terms it would if the order object had been walked.
        $products_in_order = array();
        $product_order_recorded = array();
        $category_order_recorded = array();
        $coupon_discount_recorded = array();

        foreach ((isset($line_items[$order_id]) ? $line_items[$order_id] : array()) as $line_item) {
            $product_id = $line_item['product_id'];
            $quantity = $line_item['quantity'];
            $total = $line_item['total'];

            if (!isset($data['product_stats'][$product_id])) {
                $data['product_stats'][$product_id] = array(
                    'quantity'       => 0,
                    'total'          => 0,
                    'order_count'    => 0,
                    'last_purchased' => null,
                );
            }
            $data['product_stats'][$product_id]['quantity'] += $quantity;
            $data['product_stats'][$product_id]['total'] += $total;

            if (empty($product_order_recorded[$product_id])) {
                $data['product_stats'][$product_id]['order_count'] += 1;
                $product_order_recorded[$product_id] = true;
            }

            if ($order_date && (!$data['product_stats'][$product_id]['last_purchased'] || $order_date->getTimestamp() > $data['product_stats'][$product_id]['last_purchased']->getTimestamp())) {
                $data['product_stats'][$product_id]['last_purchased'] = $order_date;
            }

            $item_categories = isset($product_categories[$product_id]) ? $product_categories[$product_id] : array();

            foreach ($item_categories as $category_id) {
                if (!isset($data['category_stats'][$category_id])) {
                    $data['category_stats'][$category_id] = array(
                        'quantity'    => 0,
                        'total'       => 0,
                        'order_count' => 0,
                    );
                }
                $data['category_stats'][$category_id]['quantity'] += $quantity;
                $data['category_stats'][$category_id]['total'] += $total;

                if (empty($category_order_recorded[$category_id])) {
                    $data['category_stats'][$category_id]['order_count'] += 1;
                    $category_order_recorded[$category_id] = true;
                }
            }

            foreach ($coupons as $coupon) {
                if (!isset($data['coupon_stats'][$coupon])) {
                    $data['coupon_stats'][$coupon] = array(
                        'quantity'    => 0,
                        'total'       => 0,
                        'order_count' => 0,
                        'discount'    => 0,
                        'last_used'   => $order_date,
                    );
                }

                $data['coupon_stats'][$coupon]['quantity'] += $quantity;
                $data['coupon_stats'][$coupon]['total'] += $total;

                if (empty($coupon_discount_recorded[$coupon])) {
                    $data['coupon_stats'][$coupon]['order_count'] += 1;
                    $data['coupon_stats'][$coupon]['discount'] += $order_discount;
                    $coupon_discount_recorded[$coupon] = true;
                }
            }

            if ($is_premium) {
                $products_in_order[] = $product_id;
            }
        }

        if ($is_premium) {
            $products_in_order = array_values(array_unique(array_filter($products_in_order)));

            if (count($products_in_order) > 1) {
                sort($products_in_order);
                $products_in_order_count = count($products_in_order);

                for ($first_product_index = 0; $first_product_index < $products_in_order_count; $first_product_index++) {
                    for ($second_product_index = $first_product_index + 1; $second_product_index < $products_in_order_count; $second_product_index++) {
                        $pair_key = $products_in_order[$first_product_index] . '-' . $products_in_order[$second_product_index];

                        if (!isset($data['product_affinity'][$pair_key])) {
                            $data['product_affinity'][$pair_key] = 0;
                        }

                        $data['product_affinity'][$pair_key]++;
                    }
                }
            }
        }

        $shipping = isset($fields['shipping']) ? $fields['shipping'] : array();
        $billing = isset($fields['billing']) ? $fields['billing'] : array();
        $shipping_address = acreports_build_report_address_line($shipping);

        if (!$shipping_address) {
            $shipping_address = acreports_build_report_address_line($billing);
        }

        if (!isset($data['shipping_addresses'][$shipping_address])) {
            $data['shipping_addresses'][$shipping_address] = array(
                'latest_order_date' => $order_date,
                'total_orders'      => 0,
                'total_spend'       => 0,
            );
        }
        $data['shipping_addresses'][$shipping_address]['total_orders'] += 1;
        $data['shipping_addresses'][$shipping_address]['total_spend'] += $order_total;

        $shipping_method = isset($item_names[$order_id]['shipping']) ? implode(', ', $item_names[$order_id]['shipping']) : '';

        if (!isset($data['shipping_method_amounts'][$shipping_method])) {
            $data['shipping_method_amounts'][$shipping_method] = 0;
            $data['shipping_method_spend'][$shipping_method] = 0;
        }
        $data['shipping_method_amounts'][$shipping_method] += 1;
        $data['shipping_method_spend'][$shipping_method] += $order_total;
    }

    return $data;
}

/**
 * The single comma separated line the shipping breakdown groups orders by.
 */
function acreports_build_report_address_line($address) {
    $line = isset($address['address_1']) ? (string) $address['address_1'] : '';

    foreach (array('address_2', 'city', 'state', 'postcode', 'country') as $key) {
        if (!empty($address[$key])) {
            $line .= ', ' . $address[$key];
        }
    }

    return $line;
}

/**
 * Build the report aggregates by reading the orders themselves.
 *
 * The fallback for stores without the Analytics lookup tables, or whose tables
 * have not caught up with every order in the set. Orders are read in batches of
 * whole objects: asking for IDs and then calling wc_get_order() per ID makes
 * WooCommerce read every order on its own, whereas handing it a batch lets it
 * read them in a single pass and prime the item and meta caches.
 */
function acreports_build_report_data_from_orders($order_query_args, $is_premium) {
    $data = acreports_get_empty_report_data();
    $order_batch_size = acreports_get_report_batch_size();
    $max_report_orders = acreports_get_max_report_orders();
    $product_category_cache = array();

    $order_batch = array();
    $order_batch_page = 1;
    $order_batches_exhausted = false;

    while (true) {

        if (empty($order_batch)) {
            if ($order_batches_exhausted) { break; }

            $order_batch = wc_get_orders(array_merge($order_query_args, array(
                'type'  => 'shop_order',
                'limit' => $order_batch_size,
                'page'  => $order_batch_page,
            )));
            $order_batch_page++;

            if (!is_array($order_batch)) { $order_batch = []; }
            if (count($order_batch) < $order_batch_size) { $order_batches_exhausted = true; }
            if (empty($order_batch)) { break; }
        }

        $order = array_shift($order_batch);

        if(!$order || !is_a($order, 'WC_Abstract_Order') || !$order->get_id()) { continue; }

        // Refunds are returned alongside orders when no customer is selected,
        // and they do not carry the address or payment data read below.
        if(method_exists($order, 'get_type') && 'shop_order' !== $order->get_type()) { continue; }

        // A report walks every matching order, so a store-wide report is capped
        // to keep the request finite. The reader is told when this happens.
        if ($max_report_orders && count($data['order_ids']) >= $max_report_orders) {
            $data['orders_truncated'] = true;
            break;
        }

        $data['order_ids'][] = $order->get_id();

        $order_date = $order->get_date_created();
        $order_total = (float) $order->get_total();
        $order_refunded = method_exists($order, 'get_total_refunded') ? (float) $order->get_total_refunded() : 0;
        $order_discount = acreports_get_order_discount_gross($order);
        $order_shipping = acreports_get_order_shipping_gross($order);
        $order_tax = (float) $order->get_total_tax();
        // Read before the first/latest comparison below so both summaries can
        // carry the origin the order was won through.
        $order_attribution = acreports_get_order_attribution($order);

        if ($order_date) {
            $data['order_timestamps'][] = $order_date->getTimestamp();
            $month_key = date_i18n('Y-m', $order_date->getTimestamp());

            if (!isset($data['monthly_stats'][$month_key])) {
                $data['monthly_stats'][$month_key] = array(
                    'label' => date_i18n('M Y', $order_date->getTimestamp()),
                    'orders' => 0,
                    'gross' => 0,
                    'refunds' => 0,
                    'discounts' => 0,
                );
            }

            $data['monthly_stats'][$month_key]['orders'] += 1;
            $data['monthly_stats'][$month_key]['gross'] += $order_total;
            $data['monthly_stats'][$month_key]['refunds'] += $order_refunded;
            $data['monthly_stats'][$month_key]['discounts'] += $order_discount;
        }

        // Compared by date rather than taken from the ends of the loop, so the
        // first and latest order stay correct no matter what order the batches
        // arrive in, and even if an order was backdated.
        if ($order_date) {
            $order_summary = array(
                'id' => $order->get_id(),
                'date' => $order_date,
                'total' => $order_total,
                'items' => $order->get_item_count(),
                'status' => $order->get_status(),
                'attribution' => $order_attribution,
            );

            if (null === $data['last_order_date'] || $order_date->getTimestamp() > $data['last_order_date']->getTimestamp()) {
                $data['last_order_date'] = $order_date;
                $data['last_order_summary'] = $order_summary;
            }

            if (null === $data['first_order_date'] || $order_date->getTimestamp() < $data['first_order_date']->getTimestamp()) {
                $data['first_order_date'] = $order_date;
                $data['first_order_summary'] = $order_summary;
            }
        }

        $data['total_orders']++;
        $data['total_spent'] += $order_total;
        $data['total_refunded'] += $order_refunded;
        $data['total_discount'] += $order_discount;
        $data['total_shipping'] += $order_shipping;
        $data['total_tax'] += $order_tax;
        $data['total_products'] += $order->get_item_count();

        $products_in_order = [];
        $product_order_recorded = [];
        $category_order_recorded = [];
        $coupons = $order->get_coupon_codes();
        $coupon_discount_recorded = [];

        if (!empty($coupons)) {
            $data['coupon_order_count']++;
        }

        // Largest Order Checker
        if($order_total > $data['largest_order_total']) {
            $data['largest_order_total'] = $order_total;
        }

        // Smallest Order Checker
        if($order_total > 0 && (!$data['smallest_order_total'] || $order_total < $data['smallest_order_total'])) {
            $data['smallest_order_total'] = $order_total;
        }

        // Get order status and total number of orders
        $order_status = $order->get_status();
        if (!isset($data['status_amounts'][$order_status])) {
            $data['status_amounts'][$order_status] = 0;
        }
        $data['status_amounts'][$order_status] += 1;

        if ($order_refunded > 0 || 'refunded' === $order_status) {
            $data['refunded_order_count']++;
        }

        if ('cancelled' === $order_status) {
            $data['cancelled_order_count']++;
        }

        if ('failed' === $order_status) {
            $data['failed_order_count']++;
        }

        // Get payment method and total number of orders

        // if get_payment_method method does not exist
        if(method_exists($order, 'get_payment_method')) {
            $payment_method = $order->get_payment_method();
            if (!isset($data['payment_method_amounts'][$payment_method])) {
                $data['payment_method_amounts'][$payment_method] = 0;
                $data['payment_method_spend'][$payment_method] = 0;
                $data['payment_method_count'][$payment_method] = 0;
            }
            $data['payment_method_amounts'][$payment_method] += 1;
            $data['payment_method_spend'][$payment_method] += $order_total;
            $data['payment_method_count'][$payment_method] += 1;
        }

        // Where the order came from. Orders with nothing recorded are left out
        // of the breakdowns entirely and counted instead, so the percentages
        // below describe the orders that actually carry attribution.
        if (!empty($order_attribution)) {
            $data['attribution_order_count']++;

            acreports_add_attribution_bucket($data['attribution_origins'], $order_attribution['origin'], $order_total, array(
                'source_type' => isset($order_attribution['source_type']) ? $order_attribution['source_type'] : '',
            ));

            if ($is_premium) {

                $utm_campaign = isset($order_attribution['utm_campaign']) ? $order_attribution['utm_campaign'] : '';
                $utm_source = isset($order_attribution['utm_source']) ? $order_attribution['utm_source'] : '';
                $utm_medium = isset($order_attribution['utm_medium']) ? $order_attribution['utm_medium'] : '';

                if ('' !== $utm_campaign || '' !== $utm_source || '' !== $utm_medium) {
                    acreports_add_attribution_bucket(
                        $data['attribution_campaigns'],
                        strtolower($utm_campaign . '|' . $utm_source . '|' . $utm_medium),
                        $order_total,
                        array(
                            'campaign' => $utm_campaign,
                            'source' => $utm_source,
                            'medium' => $utm_medium,
                        )
                    );
                }

                if (!empty($order_attribution['device_type'])) {
                    acreports_add_attribution_bucket($data['attribution_devices'], $order_attribution['device_type'], $order_total);
                }

            }
        }

        // Order notes are fetched once after this loop. Looking them up per
        // order meant a comments query for every order walked, even when the
        // customer had no notes at all.

        // Loop through each product in the order
        foreach ($order->get_items() as $item) {

            $product_id = $item->get_product_id();
            $quantity = $item->get_quantity();
            $total = acreports_get_order_item_gross_total($item);

            // Get product stats
            if (!isset($data['product_stats'][$product_id])) {
                $data['product_stats'][$product_id] = [
                    'quantity' => 0,
                    'total' => 0,
                    'order_count' => 0,
                    'last_purchased' => null,
                ];
            }
            $data['product_stats'][$product_id]['quantity'] += $quantity;
            $data['product_stats'][$product_id]['total'] += $total;

            if (empty($product_order_recorded[$product_id])) {
                $data['product_stats'][$product_id]['order_count'] += 1;
                $product_order_recorded[$product_id] = true;
            }

            if ($order_date && (!$data['product_stats'][$product_id]['last_purchased'] || $order_date->getTimestamp() > $data['product_stats'][$product_id]['last_purchased']->getTimestamp())) {
                $data['product_stats'][$product_id]['last_purchased'] = $order_date;
            }

            // Get product category stats
            if (!array_key_exists($product_id, $product_category_cache)) {
                $product_category_cache[$product_id] = get_the_terms($product_id, 'product_cat');
            }
            $product_categories = $product_category_cache[$product_id];
            if ($product_categories && !is_wp_error($product_categories)) {
                foreach ($product_categories as $category) {
                    if (!isset($data['category_stats'][$category->term_id])) {
                        $data['category_stats'][$category->term_id] = [
                            'quantity' => 0,
                            'total' => 0,
                            'order_count' => 0,
                        ];
                    }
                    $data['category_stats'][$category->term_id]['quantity'] += $quantity;
                    $data['category_stats'][$category->term_id]['total'] += $total;

                    if (empty($category_order_recorded[$category->term_id])) {
                        $data['category_stats'][$category->term_id]['order_count'] += 1;
                        $category_order_recorded[$category->term_id] = true;
                    }
                }
            }

            // Get List of Coupons Used
            if($coupons) {
                foreach($coupons as $coupon) {
                    if (!isset($data['coupon_stats'][$coupon])) {
                        $data['coupon_stats'][$coupon] = [
                            'quantity' => 0,
                            'total' => 0,
                            'order_count' => 0,
                            'discount' => 0,
                            'last_used' => $order->get_date_created(),
                        ];
                    }
                    $data['coupon_stats'][$coupon]['quantity'] += $quantity;
                    $data['coupon_stats'][$coupon]['total'] += $total;

                    if (empty($coupon_discount_recorded[$coupon])) {
                        $data['coupon_stats'][$coupon]['order_count'] += 1;
                        $data['coupon_stats'][$coupon]['discount'] += $order_discount;
                        $coupon_discount_recorded[$coupon] = true;
                    }
                }
            }

            if($is_premium) {

                // Get product affinity stats
                $products_in_order[] = $product_id;

            }

        }

        if($is_premium) {
            $products_in_order = array_values(array_unique(array_filter($products_in_order)));

            if (count($products_in_order) > 1) {
                sort($products_in_order);
                $products_in_order_count = count($products_in_order);

                for ($first_product_index = 0; $first_product_index < $products_in_order_count; $first_product_index++) {
                    for ($second_product_index = $first_product_index + 1; $second_product_index < $products_in_order_count; $second_product_index++) {
                        $pair_key = $products_in_order[$first_product_index] . '-' . $products_in_order[$second_product_index];

                        if (!isset($data['product_affinity'][$pair_key])) {
                            $data['product_affinity'][$pair_key] = 0;
                        }

                        $data['product_affinity'][$pair_key]++;
                    }
                }
            }
        }

        // Get Shipping Addresses - Seperate Lines If Exist By Comma
        $shipping_address = $order->get_shipping_address_1();
        $shipping_address .= $order->get_shipping_address_2() ? ', ' . $order->get_shipping_address_2() : '';
        $shipping_address .= $order->get_shipping_city() ? ', ' . $order->get_shipping_city() : '';
        $shipping_address .= $order->get_shipping_state() ? ', ' . $order->get_shipping_state() : '';
        $shipping_address .= $order->get_shipping_postcode() ? ', ' . $order->get_shipping_postcode() : '';
        $shipping_address .= $order->get_shipping_country() ? ', ' . $order->get_shipping_country() : '';
        if(!$shipping_address) {
            // Get Billing Address - Seperate Lines If Exist By Comma
            $shipping_address = $order->get_billing_address_1();
            $shipping_address .= $order->get_billing_address_2() ? ', ' . $order->get_billing_address_2() : '';
            $shipping_address .= $order->get_billing_city() ? ', ' . $order->get_billing_city() : '';
            $shipping_address .= $order->get_billing_state() ? ', ' . $order->get_billing_state() : '';
            $shipping_address .= $order->get_billing_postcode() ? ', ' . $order->get_billing_postcode() : '';
            $shipping_address .= $order->get_billing_country() ? ', ' . $order->get_billing_country() : '';
        }

        // Save address along with latest order date for that address, and total times address used
        if (!isset($data['shipping_addresses'][$shipping_address])) {
            $data['shipping_addresses'][$shipping_address] = [
                'latest_order_date' => $order->get_date_created(),
                'total_orders' => 0,
                'total_spend' => 0,
            ];
        }
        $data['shipping_addresses'][$shipping_address]['total_orders'] += 1;
        $data['shipping_addresses'][$shipping_address]['total_spend'] += $order_total;

        // Shipping Methods
        $shipping_method = $order->get_shipping_method();
        // Amounts
        if (!isset($data['shipping_method_amounts'][$shipping_method])) {
            $data['shipping_method_amounts'][$shipping_method] = 0;
        }
        $data['shipping_method_amounts'][$shipping_method] += 1;
        // Spend
        if (!isset($data['shipping_method_spend'][$shipping_method])) {
            $data['shipping_method_spend'][$shipping_method] = 0;
        }
        $data['shipping_method_spend'][$shipping_method] += $order_total;

    }

    return $data;
}

/**
 * Every figure a customer report is built from.
 *
 * Reads the Analytics lookup tables where it can, and walks the orders where it
 * cannot. Both produce the same structure, so the report itself does not need
 * to know which one ran.
 */
function acreports_get_report_data($order_query_args) {
    // Read once rather than per order and per line item, which is how often the
    // report loop used to ask.
    $is_premium = acreports_fs()->can_use_premium_code();

    if (acreports_report_table_exists('wc_order_stats')) {
        $order_set = acreports_get_report_order_ids($order_query_args);

        if (empty($order_set['ids'])) {
            $data = acreports_get_empty_report_data();
            $data['orders_truncated'] = $order_set['truncated'];

            return $data;
        }

        $data = acreports_build_report_data_from_lookup($order_set['ids'], $is_premium);

        if (null !== $data) {
            $data['orders_truncated'] = $order_set['truncated'];

            return $data;
        }
    }

    return acreports_build_report_data_from_orders($order_query_args, $is_premium);
}