<?php
/**
 * SQL for advanced customer search on the Users list.
 *
 * @package AdminPowerups
 */

namespace SmartWP\AdminPowerups\Modules\AdvancedCustomerSearch;

use Automattic\WooCommerce\Utilities\OrderUtil;
// Cross-module dependencies (kept for 1.0, see docs/developer/architecture.md): TotalQuery is a pure
// parser for amount ranges ("5+", "100-300") and works whether Advanced order search is on or off;
// Groups::PREFIX only names the Customer tags taxonomies, and the tag type is offered only while
// that power-up is on.
use SmartWP\AdminPowerups\Modules\AdvancedOrderSearch\TotalQuery;
use SmartWP\AdminPowerups\Modules\CustomerTags\Groups;
use SmartWP\AdminPowerups\Support\Orders;

defined( 'ABSPATH' ) || exit;

/**
 * Builds one prepared condition on `{users}.ID` per search type. Every type is a subquery, so the
 * Users list keeps its own paging and counting.
 *
 * @since 1.0.0
 */
class CustomerQuery {

	/**
	 * All built-in types: key => label.
	 *
	 * @since 1.0.0
	 * @return array<string, string>
	 */
	public static function types() {
		return array(
			'name'     => __( 'Name', 'admin-powerups-for-woocommerce' ),
			'email'    => __( 'Email', 'admin-powerups-for-woocommerce' ),
			'phone'    => __( 'Phone', 'admin-powerups-for-woocommerce' ),
			'company'  => __( 'Company', 'admin-powerups-for-woocommerce' ),
			'address'  => __( 'Address', 'admin-powerups-for-woocommerce' ),
			'city'     => __( 'City', 'admin-powerups-for-woocommerce' ),
			'postcode' => __( 'Postcode', 'admin-powerups-for-woocommerce' ),
			'order'    => __( 'Order number', 'admin-powerups-for-woocommerce' ),
			'sku'      => __( 'SKU purchased', 'admin-powerups-for-woocommerce' ),
			'product'  => __( 'Product purchased', 'admin-powerups-for-woocommerce' ),
			'tag'      => __( 'Customer tag', 'admin-powerups-for-woocommerce' ),
			'orders'   => __( 'Number of orders', 'admin-powerups-for-woocommerce' ),
			'spend'    => __( 'Lifetime spend', 'admin-powerups-for-woocommerce' ),
			'ordered'  => __( 'Ordered in', 'admin-powerups-for-woocommerce' ),
		);
	}

	/**
	 * Types that look inside text or add up orders, which can't use an index.
	 *
	 * @since 1.0.0
	 * @return string[]
	 */
	public static function slow_types() {
		return array( 'name', 'email', 'phone', 'company', 'address', 'city', 'postcode', 'orders', 'spend' );
	}

	/**
	 * Types that read the HPOS orders tables.
	 *
	 * @since 1.0.0
	 * @return string[]
	 */
	public static function order_types() {
		return array( 'order', 'sku', 'product', 'orders', 'spend', 'ordered' );
	}

	/**
	 * Example terms shown as the search box placeholder.
	 *
	 * @since 1.0.0
	 * @return array<string, string>
	 */
	public static function hints() {
		return array(
			'order'   => __( 'e.g. 1042', 'admin-powerups-for-woocommerce' ),
			'sku'     => __( 'Exact SKU', 'admin-powerups-for-woocommerce' ),
			'orders'  => __( 'e.g. 5+ or 2-4 or 0', 'admin-powerups-for-woocommerce' ),
			'spend'   => __( 'e.g. >500 or 100-300', 'admin-powerups-for-woocommerce' ),
			'ordered' => __( 'e.g. last 30 days or 2026-01..2026-03', 'admin-powerups-for-woocommerce' ),
		);
	}

	/**
	 * Condition for a search, or '1=0' when the term can't match (bad range, unknown type).
	 *
	 * @since 1.0.0
	 * @param string $type Type.
	 * @param string $term Term (unslashed, trimmed).
	 * @return string SQL.
	 */
	public function where( $type, $term ) {
		$term   = trim( (string) $term );
		$method = 'where_' . $type;
		$sql    = '' !== $term && method_exists( $this, $method ) ? (string) $this->$method( $term ) : '';

		/**
		 * Filter the SQL condition for a customer search type. Return a condition on
		 * `{$wpdb->users}.ID`; add types with `apwc_customer_search_types`.
		 *
		 * @since 1.0.0
		 * @param string $sql  Condition ('' for unknown types).
		 * @param string $type Type.
		 * @param string $term Search term.
		 */
		$sql = (string) apply_filters( 'apwc_customer_search_where', $sql, $type, $term );
		return '' === $sql ? '1=0' : $sql;
	}

	/* ------------------------------------------------------------------ helpers */

	/**
	 * `users.ID IN ( subquery )`.
	 *
	 * @param string $sql Prepared subquery.
	 * @return string
	 */
	private function id_in( $sql ) {
		global $wpdb;
		return "{$wpdb->users}.ID IN ( {$sql} )";
	}

	/**
	 * Users with any of these meta keys containing the term.
	 *
	 * @param string[] $keys  Meta keys.
	 * @param string   $term  Term.
	 * @param string   $value SQL expression for the value (default meta_value).
	 * @return string
	 */
	private function meta_contains( array $keys, $term, $value = 'meta_value' ) {
		global $wpdb;
		$in = implode( ',', array_fill( 0, count( $keys ), '%s' ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		return $this->id_in( $wpdb->prepare( "SELECT user_id FROM {$wpdb->usermeta} WHERE meta_key IN ( {$in} ) AND {$value} LIKE %s", array_merge( $keys, array( '%' . $wpdb->esc_like( $term ) . '%' ) ) ) );
	}

	/**
	 * Real order statuses (no checkout drafts or trash). Always passed to prepare() as values,
	 * never as already-prepared SQL inside another format string.
	 *
	 * @return string[]
	 */
	private function statuses() {
		$statuses = array_values( array_diff( array_keys( wc_get_order_statuses() ), array( 'wc-checkout-draft' ) ) );
		return $statuses ? array_map( 'strval', $statuses ) : array( '' );
	}

	/**
	 * Paid statuses.
	 *
	 * @return string[]
	 */
	private function paid_statuses() {
		$statuses = array_map( static fn( $s ) => 'wc-' . $s, (array) wc_get_is_paid_statuses() );
		return $statuses ? $statuses : array( '' );
	}

	/**
	 * `%s,%s,…` for a list of values.
	 *
	 * @param string[] $values Values.
	 * @return string
	 */
	private static function placeholders( array $values ) {
		return implode( ',', array_fill( 0, count( $values ), '%s' ) );
	}

	/**
	 * Customers who bought any of these products or variations.
	 *
	 * @param int[] $ids Product or variation IDs.
	 * @return string
	 */
	private function bought( array $ids ) {
		global $wpdb;
		$ids = array_values( array_filter( array_map( 'intval', $ids ) ) );
		if ( ! $ids ) {
			return '1=0';
		}
		$in       = implode( ',', $ids );
		$orders   = OrderUtil::get_table_for_orders();
		$statuses = $this->statuses();
		$ph       = self::placeholders( $statuses );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- table names, integer IDs and %s placeholders only.
		return $this->id_in(
			$wpdb->prepare(
				"SELECT o.customer_id FROM {$wpdb->prefix}wc_order_product_lookup l INNER JOIN `{$orders}` o ON o.id = l.order_id
				WHERE ( l.product_id IN ( {$in} ) OR l.variation_id IN ( {$in} ) ) AND o.customer_id > 0 AND o.status IN ( {$ph} )",
				$statuses
			)
		);
		// phpcs:enable
	}

	/**
	 * Customers whose order count or spend is in a range; a range that includes 0 also matches
	 * customers with no such orders.
	 *
	 * @param string                                 $aggregate SQL aggregate (a literal).
	 * @param string[]                               $statuses  Order statuses.
	 * @param array{0: float|int, 1: float|int}|null $range     [ min, max ].
	 * @return string
	 */
	private function aggregate_between( $aggregate, array $statuses, $range ) {
		global $wpdb;
		if ( ! $range || ! is_finite( (float) $range[0] ) || ! is_finite( (float) $range[1] ) ) {
			return '1=0'; // INF would print as a bare word in %f.
		}
		$orders = OrderUtil::get_table_for_orders();
		$ph     = self::placeholders( $statuses );
		$from   = "SELECT customer_id FROM `{$orders}` WHERE type = 'shop_order' AND customer_id > 0 AND status IN ( {$ph} )";
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- table name, a literal aggregate and %s placeholders only; values passed as one array.
		$sql = $this->id_in( $wpdb->prepare( "{$from} GROUP BY customer_id HAVING {$aggregate} BETWEEN %f AND %f", array_merge( $statuses, array( $range[0], $range[1] ) ) ) );
		if ( $range[0] <= 0 ) {
			$sql = "( {$sql} OR {$wpdb->users}.ID NOT IN ( " . $wpdb->prepare( $from, $statuses ) . ' ) )';
		}
		// phpcs:enable
		return $sql;
	}

	/* ------------------------------------------------------------------ types */

	/**
	 * Name: every word must be in a first/last name (account, billing or shipping) or the display name.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_name( $term ) {
		global $wpdb;
		$keys  = array( 'first_name', 'last_name', 'billing_first_name', 'billing_last_name', 'shipping_first_name', 'shipping_last_name' );
		$parts = array();
		foreach ( array_slice( array_filter( preg_split( '/\s+/', $term ) ), 0, 4 ) as $word ) {
			$parts[] = '( ' . $this->meta_contains( $keys, $word ) . $wpdb->prepare( " OR {$wpdb->users}.display_name LIKE %s", '%' . $wpdb->esc_like( $word ) . '%' ) . ' )';
		}
		return $parts ? '( ' . implode( ' AND ', $parts ) . ' )' : '1=0';
	}

	/**
	 * Email: account or billing email.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_email( $term ) {
		global $wpdb;
		return '( ' . $wpdb->prepare( "{$wpdb->users}.user_email LIKE %s", '%' . $wpdb->esc_like( $term ) . '%' ) . ' OR ' . $this->meta_contains( array( 'billing_email' ), $term ) . ' )';
	}

	/**
	 * Phone: digits match whatever formatting was saved ("+44 20 7946 0000" finds "020 7946").
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_phone( $term ) {
		$digits = preg_replace( '/\D+/', '', $term );
		if ( strlen( $digits ) < 3 ) {
			return $this->meta_contains( array( 'billing_phone', 'shipping_phone' ), $term );
		}
		$strip = "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(meta_value,' ',''),'-',''),'(',''),')',''),'.',''),'+','')";
		return $this->meta_contains( array( 'billing_phone', 'shipping_phone' ), ltrim( $digits, '0' ) ? ltrim( $digits, '0' ) : $digits, $strip );
	}

	/**
	 * Company.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_company( $term ) {
		return $this->meta_contains( array( 'billing_company', 'shipping_company' ), $term );
	}

	/**
	 * Street address lines.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_address( $term ) {
		return $this->meta_contains( array( 'billing_address_1', 'billing_address_2', 'shipping_address_1', 'shipping_address_2' ), $term );
	}

	/**
	 * City.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_city( $term ) {
		return $this->meta_contains( array( 'billing_city', 'shipping_city' ), $term );
	}

	/**
	 * Postcode, ignoring spaces ("NW16XE" finds "NW1 6XE").
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_postcode( $term ) {
		return $this->meta_contains( array( 'billing_postcode', 'shipping_postcode' ), str_replace( ' ', '', $term ), "REPLACE(meta_value,' ','')" );
	}

	/**
	 * Order number: the customer of that order (ID, or a custom number from sequential-number plugins).
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_order( $term ) {
		global $wpdb;
		$term   = ltrim( $term, '#' );
		$keys   = Orders::number_meta_keys();
		$orders = OrderUtil::get_table_for_orders();
		$parts  = array();
		if ( ctype_digit( $term ) ) {
			$parts[] = $wpdb->prepare( 'id = %d', (int) $term );
		}
		if ( $keys ) {
			$in      = implode( ',', array_fill( 0, count( $keys ), '%s' ) );
			$meta    = OrderUtil::get_table_for_order_meta();
			$parts[] = $wpdb->prepare( "id IN ( SELECT order_id FROM `{$meta}` WHERE meta_key IN ( {$in} ) AND meta_value = %s )", array_merge( $keys, array( $term ) ) ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
		}
		if ( ! $parts ) {
			return '1=0';
		}
		return $this->id_in( "SELECT customer_id FROM `{$orders}` WHERE type = 'shop_order' AND customer_id > 0 AND ( " . implode( ' OR ', $parts ) . ' )' );
	}

	/**
	 * SKU purchased (exact; products and variations).
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_sku( $term ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$ids = $wpdb->get_col( $wpdb->prepare( "SELECT product_id FROM {$wpdb->prefix}wc_product_meta_lookup WHERE sku = %s", $term ) );
		return $this->bought( array_map( 'intval', $ids ) );
	}

	/**
	 * Product purchased, by name.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_product( $term ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$ids = $wpdb->get_col( $wpdb->prepare( "SELECT ID FROM {$wpdb->posts} WHERE post_type = 'product' AND post_status NOT IN ( 'trash', 'auto-draft' ) AND post_title LIKE %s LIMIT 500", '%' . $wpdb->esc_like( $term ) . '%' ) );
		return $this->bought( array_map( 'intval', $ids ) );
	}

	/**
	 * Customer tag (any group), by tag name. Needs the Customer tags power-up.
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_tag( $term ) {
		global $wpdb;
		// Term names are stored HTML-encoded ("R&amp;D"), so match the typed and the encoded form.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery
		$ttids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT tt.term_taxonomy_id FROM {$wpdb->term_taxonomy} tt INNER JOIN {$wpdb->terms} t ON t.term_id = tt.term_id WHERE tt.taxonomy LIKE %s AND ( t.name LIKE %s OR t.name LIKE %s )",
				$wpdb->esc_like( Groups::PREFIX ) . '%',
				'%' . $wpdb->esc_like( $term ) . '%',
				'%' . $wpdb->esc_like( esc_html( $term ) ) . '%'
			)
		);
		// phpcs:enable
		$ttids = array_values( array_filter( array_map( 'intval', $ttids ) ) );
		return $ttids ? $this->id_in( "SELECT object_id FROM {$wpdb->term_relationships} WHERE term_taxonomy_id IN ( " . implode( ',', $ttids ) . ' )' ) : '1=0';
	}

	/**
	 * Number of orders: "5", "5+", ">3", "2-4", "0".
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_orders( $term ) {
		$range = self::count_range( $term );
		return $this->aggregate_between( 'COUNT(*)', $this->statuses(), $range );
	}

	/**
	 * Lifetime spend (paid orders): "500", ">500", "100-300".
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_spend( $term ) {
		$range = TotalQuery::parse( self::plus( $term ), 0.0, wc_get_price_decimal_separator() );
		return $this->aggregate_between( 'SUM(total_amount)', $this->paid_statuses(), $range );
	}

	/**
	 * Ordered in a period (store time).
	 *
	 * @param string $term Term.
	 * @return string
	 */
	private function where_ordered( $term ) {
		global $wpdb;
		$range = self::date_range( $term, time(), wp_timezone() );
		if ( ! $range ) {
			return '1=0';
		}
		$orders   = OrderUtil::get_table_for_orders();
		$statuses = $this->statuses();
		$ph       = self::placeholders( $statuses );
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- table name and placeholders only; values passed as one array.
		return $this->id_in(
			$wpdb->prepare(
				"SELECT customer_id FROM `{$orders}` WHERE type = 'shop_order' AND customer_id > 0 AND status IN ( {$ph} ) AND date_created_gmt BETWEEN %s AND %s",
				array_merge( $statuses, array( gmdate( 'Y-m-d H:i:s', $range[0] ), gmdate( 'Y-m-d H:i:s', $range[1] ) ) )
			)
		);
		// phpcs:enable
	}

	/* ------------------------------------------------------------------ parsers (pure) */

	/**
	 * "5+" means "5 or more".
	 *
	 * @since 1.0.0
	 * @param string $term Term.
	 * @return string
	 */
	public static function plus( $term ) {
		return preg_replace( '/^\s*([\d.,]+)\s*\+\s*$/', '>=$1', (string) $term );
	}

	/**
	 * Whole-number range for order counts.
	 *
	 * @since 1.0.0
	 * @param string $term Term.
	 * @return array{0: int, 1: int}|null
	 */
	public static function count_range( $term ) {
		$range = TotalQuery::parse( self::plus( $term ) );
		if ( ! $range ) {
			return null;
		}
		return array( max( 0, (int) ceil( $range[0] - 1e-9 ) ), (int) floor( $range[1] + 1e-9 ) );
	}

	/**
	 * Period to [ start, end ] timestamps (inclusive), read in the store's time zone.
	 * Accepts: "today", "yesterday", "this week|month|year", "last N days|weeks|months|years",
	 * "2026", "2026-03", "2026-03-15", and "A..B" / "A to B" of those dates.
	 *
	 * @since 1.0.0
	 * @param string        $term Term.
	 * @param int           $now  Current timestamp.
	 * @param \DateTimeZone $tz   Store time zone.
	 * @return array{0: int, 1: int}|null
	 */
	public static function date_range( $term, $now, \DateTimeZone $tz ) {
		$term  = strtolower( trim( (string) $term ) );
		$today = ( new \DateTimeImmutable( '@' . $now ) )->setTimezone( $tz )->setTime( 0, 0 );
		$end   = static fn( \DateTimeImmutable $d ) => $d->setTime( 23, 59, 59 )->getTimestamp();

		if ( 'today' === $term ) {
			return array( $today->getTimestamp(), $end( $today ) );
		}
		if ( 'yesterday' === $term ) {
			$y = $today->modify( '-1 day' );
			return array( $y->getTimestamp(), $end( $y ) );
		}
		if ( preg_match( '/^this (week|month|year)$/', $term, $m ) ) {
			$start = 'week' === $m[1] ? $today->modify( 'monday this week' ) : ( 'month' === $m[1] ? $today->modify( 'first day of this month' ) : $today->setDate( (int) $today->format( 'Y' ), 1, 1 ) );
			return array( $start->getTimestamp(), $end( $today ) );
		}
		if ( preg_match( '/^(?:last|past)\s+(\d{1,4})\s+(day|week|month|year)s?$/', $term, $m ) ) {
			$start = $today->modify( '-' . ( (int) $m[1] ) . ' ' . $m[2] )->modify( '+1 day' );
			return array( $start->getTimestamp(), $end( $today ) );
		}

		$parts = preg_split( '/\s*(?:\.\.|\bto\b|–|—)\s*/', $term );
		if ( count( $parts ) > 2 ) {
			return null;
		}
		$from = self::date_bounds( $parts[0], $tz );
		$to   = self::date_bounds( $parts[1] ?? $parts[0], $tz );
		if ( ! $from || ! $to || $from[0] > $to[1] ) {
			return null;
		}
		return array( $from[0], $to[1] );
	}

	/**
	 * First and last second of "2026", "2026-03" or "2026-03-15".
	 *
	 * @param string        $date Date.
	 * @param \DateTimeZone $tz   Time zone.
	 * @return array{0: int, 1: int}|null
	 */
	private static function date_bounds( $date, \DateTimeZone $tz ) {
		if ( ! preg_match( '/^(\d{4})(?:-(\d{1,2})(?:-(\d{1,2}))?)?$/', trim( $date ), $m ) ) {
			return null;
		}
		$y  = (int) $m[1];
		$mo = isset( $m[2] ) ? (int) $m[2] : 0;
		$d  = isset( $m[3] ) ? (int) $m[3] : 0;
		if ( ( $mo && ( $mo < 1 || $mo > 12 ) ) || ( $d && ! checkdate( $mo, $d, $y ) ) ) {
			return null;
		}
		$start = ( new \DateTimeImmutable( 'now', $tz ) )->setDate( $y, $mo ? $mo : 1, $d ? $d : 1 )->setTime( 0, 0 );
		$last  = $d ? $start : ( $mo ? $start->modify( 'last day of this month' ) : $start->setDate( $y, 12, 31 ) );
		return array( $start->getTimestamp(), $last->setTime( 23, 59, 59 )->getTimestamp() );
	}
}