<?php
/**
 * Reads WooCommerce orders and turns them into one row per customer.
 *
 * Works with both order storage modes: High-Performance Order Storage (wc_orders)
 * and the legacy posts tables. Everything is done with grouped SQL so a store with
 * 100,000 orders is processed in a few queries, not 100,000 order objects.
 *
 * @package Statbird
 */

namespace Statbird;

defined( 'ABSPATH' ) || exit;

/**
 * Customer aggregates straight from the order tables.
 */
class Orders {

	/**
	 * Order statuses that count as a purchase. Refunds are subtracted separately.
	 *
	 * @return string[] Statuses with the wc- prefix.
	 */
	public static function counted_statuses() {
		$paid = function_exists( 'wc_get_is_paid_statuses' ) ? wc_get_is_paid_statuses() : array( 'processing', 'completed' );
		// A fully refunded order keeps its refunded status; it is counted and then netted to zero.
		$paid[] = 'refunded';
		/**
		 * Filters the order statuses Statbird treats as purchases.
		 *
		 * @param string[] $statuses Statuses without the wc- prefix.
		 */
		$paid = (array) apply_filters( 'statbird_counted_statuses', array_unique( $paid ) );
		return array_map(
			static function ( $s ) {
				return 0 === strpos( $s, 'wc-' ) ? $s : 'wc-' . $s;
			},
			$paid
		);
	}

	/**
	 * Whether WooCommerce stores orders in its own tables.
	 *
	 * @return bool
	 */
	public static function uses_hpos() {
		return class_exists( '\Automattic\WooCommerce\Utilities\OrderUtil' )
			&& \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
	}

	/**
	 * The SQL that lists every counted order with its customer key and net amount.
	 *
	 * Each row: ckey (string identity), user_id, email, created (GMT), net (total minus refunds).
	 * Guests are matched to an account when their billing email belongs to a registered user,
	 * so a guest who signs up later is one customer, not two.
	 *
	 * @param string $ckey Optional identity key. When given, only that customer's orders are
	 *                     read, through WooCommerce's customer and email indexes.
	 * @return string SQL with no placeholders left.
	 */
	public static function order_rows_sql( $ckey = '' ) {
		global $wpdb;
		$hpos     = self::uses_hpos();
		$only     = self::customer_filter( $ckey, $hpos );
		$statuses = "'" . implode( "','", array_map( 'esc_sql', self::counted_statuses() ) ) . "'";

		if ( $hpos ) {
			$o = $wpdb->prefix . 'wc_orders';
			return "SELECT
				CASE WHEN o.customer_id > 0 THEN CONCAT('u', o.customer_id)
				     WHEN u.ID IS NOT NULL THEN CONCAT('u', u.ID)
				     ELSE CONCAT('e', LOWER(TRIM(o.billing_email))) END AS ckey,
				CASE WHEN o.customer_id > 0 THEN o.customer_id ELSE COALESCE(u.ID, 0) END AS user_id,
				LOWER(TRIM(o.billing_email)) AS email,
				TRIM(CONCAT(COALESCE(a.first_name, ''), ' ', COALESCE(a.last_name, ''))) AS name,
				o.id AS order_id,
				o.date_created_gmt AS created,
				o.total_amount + COALESCE(r.refunded, 0) AS net
			FROM $o o
			LEFT JOIN {$wpdb->prefix}wc_order_addresses a ON a.order_id = o.id AND a.address_type = 'billing'
			LEFT JOIN {$wpdb->users} u ON o.customer_id = 0 AND u.user_email = o.billing_email
			LEFT JOIN (
				SELECT parent_order_id, SUM(total_amount) AS refunded FROM $o
				WHERE type = 'shop_order_refund' GROUP BY parent_order_id
			) r ON r.parent_order_id = o.id
			WHERE o.type = 'shop_order' AND o.status IN ($statuses)
			  AND ( o.customer_id > 0 OR o.billing_email <> '' ) $only";
		}

		$p = $wpdb->posts;
		$m = $wpdb->postmeta;
		return "SELECT
			CASE WHEN cu.meta_value > 0 THEN CONCAT('u', cu.meta_value)
			     WHEN u.ID IS NOT NULL THEN CONCAT('u', u.ID)
			     ELSE CONCAT('e', LOWER(TRIM(em.meta_value))) END AS ckey,
			CASE WHEN cu.meta_value > 0 THEN cu.meta_value ELSE COALESCE(u.ID, 0) END AS user_id,
			LOWER(TRIM(em.meta_value)) AS email,
			TRIM(CONCAT(COALESCE(fn.meta_value, ''), ' ', COALESCE(ln.meta_value, ''))) AS name,
			o.ID AS order_id,
			o.post_date_gmt AS created,
			CAST(tot.meta_value AS DECIMAL(26,8)) + COALESCE(r.refunded, 0) AS net
		FROM $p o
		LEFT JOIN $m cu ON cu.post_id = o.ID AND cu.meta_key = '_customer_user'
		LEFT JOIN $m em ON em.post_id = o.ID AND em.meta_key = '_billing_email'
		LEFT JOIN $m tot ON tot.post_id = o.ID AND tot.meta_key = '_order_total'
		LEFT JOIN $m fn ON fn.post_id = o.ID AND fn.meta_key = '_billing_first_name'
		LEFT JOIN $m ln ON ln.post_id = o.ID AND ln.meta_key = '_billing_last_name'
		LEFT JOIN {$wpdb->users} u ON ( cu.meta_value IS NULL OR cu.meta_value = 0 ) AND u.user_email = em.meta_value
		LEFT JOIN (
			SELECT rp.post_parent, SUM(CAST(rm.meta_value AS DECIMAL(26,8))) AS refunded
			FROM $p rp JOIN $m rm ON rm.post_id = rp.ID AND rm.meta_key = '_order_total'
			WHERE rp.post_type = 'shop_order_refund' GROUP BY rp.post_parent
		) r ON r.post_parent = o.ID
		WHERE o.post_type = 'shop_order' AND o.post_status IN ($statuses)
		  AND ( cu.meta_value > 0 OR em.meta_value <> '' ) $only";
	}

	/**
	 * Extra WHERE clause that limits order_rows_sql() to one customer's orders.
	 *
	 * An account's orders are the ones placed while logged in plus guest orders that used the
	 * account's email, matching how keys are assigned.
	 *
	 * @param string $ckey Identity key, or '' for every customer.
	 * @param bool   $hpos Whether orders live in wc_orders.
	 * @return string Prepared SQL fragment starting with AND, or ''.
	 */
	private static function customer_filter( $ckey, $hpos ) {
		global $wpdb;
		if ( '' === $ckey ) {
			return '';
		}
		$cust  = $hpos ? 'o.customer_id' : 'cu.meta_value';
		$email = $hpos ? 'o.billing_email' : 'em.meta_value';
		if ( 'u' === $ckey[0] ) {
			$user = get_userdata( (int) substr( $ckey, 1 ) );
			$mail = $user ? $user->user_email : '';
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- column names are constants above.
			return $wpdb->prepare( " AND ( $cust = %d OR ( ( $cust = 0 OR $cust IS NULL ) AND $email = %s AND %s <> '' ) )", (int) substr( $ckey, 1 ), $mail, $mail );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- column names are constants above.
		return $wpdb->prepare( " AND ( $cust = 0 OR $cust IS NULL ) AND $email = %s", substr( $ckey, 1 ) );
	}

	/**
	 * SELECT ... FROM for per-customer aggregates. Fully refunded orders (net 0) are not
	 * purchases: they don't count as an order or move the first and last purchase dates.
	 *
	 * @param string $ckey Optional identity key to aggregate just one customer.
	 * @return string
	 */
	private static function aggregate_select( $ckey = '' ) {
		return 'SELECT MD5(ckey) AS customer_key, MAX(user_id) AS user_id, MAX(email) AS email, MAX(name) AS name,
				MIN(CASE WHEN net > 0 THEN created END) AS first_order,
				MAX(CASE WHEN net > 0 THEN created END) AS last_order,
				SUM(net > 0) AS orders, SUM(GREATEST(net, 0)) AS spent
			FROM ( ' . self::order_rows_sql( $ckey ) . ' ) t';
	}

	/**
	 * Aggregates a page of customers, ordered by key, for the background rebuild.
	 *
	 * @param string $after Return customers whose hashed key sorts after this one ('' to start).
	 * @param int    $limit Customers per page.
	 * @return array[] Customer rows ready for Store::upsert().
	 */
	public static function customers_page( $after, $limit ) {
		global $wpdb;
		$sql = self::aggregate_select() . '
			GROUP BY ckey
			HAVING orders > 0 AND customer_key > %s
			ORDER BY customer_key
			LIMIT %d';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- the inner query is built from esc_sql()'d constants.
		return (array) $wpdb->get_results( $wpdb->prepare( $sql, $after, $limit ), ARRAY_A );
	}

	/**
	 * Aggregates one customer, for refreshing after an order changes.
	 *
	 * @param string $ckey Identity key ('u123' or 'eemail@example.com').
	 * @return array|null Customer row, or null when they have no counted orders left.
	 */
	public static function customer( $ckey ) {
		global $wpdb;
		$sql = self::aggregate_select( $ckey ) . '
			WHERE ckey = %s
			GROUP BY ckey
			HAVING orders > 0';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- see customers_page().
		$row = $wpdb->get_row( $wpdb->prepare( $sql, $ckey ), ARRAY_A );
		return $row ? $row : null;
	}

	/**
	 * The identity key for an order's customer, matching order_rows_sql().
	 *
	 * @param \WC_Order|\WC_Abstract_Order $order Order.
	 * @return string Key, or '' for an order with no customer and no email.
	 */
	public static function key_for_order( $order ) {
		$uid = (int) $order->get_customer_id();
		if ( $uid > 0 ) {
			return 'u' . $uid;
		}
		$email = strtolower( trim( (string) $order->get_billing_email() ) );
		if ( '' === $email ) {
			return '';
		}
		$user = get_user_by( 'email', $email );
		return $user ? 'u' . $user->ID : 'e' . $email;
	}

	/**
	 * Number of counted orders, for progress reporting.
	 *
	 * @return int
	 */
	public static function count() {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- see customers_page().
		return (int) $wpdb->get_var( 'SELECT COUNT(*) FROM ( ' . self::order_rows_sql() . ' ) t' );
	}
}