<?php
/**
 * Analytics order filters: SQL conditions.
 *
 * @package AdminPowerups
 */

namespace SmartWP\AdminPowerups\Modules\AnalyticsFilters;

use SmartWP\AdminPowerups\Support\Orders;

defined( 'ABSPATH' ) || exit;

/**
 * Builds one prepared condition on WooCommerce's `wc_order_stats` table from normalized params
 * (see Params). Every filter is a subquery on the order ID, so it works for the Orders table
 * query and the stats (totals and intervals) queries alike. Refunds have their own stats rows;
 * they follow their parent order: `IF( parent_id > 0, parent_id, order_id ) IN ( … )`.
 *
 * @since 1.0.0
 */
final class Conditions {

	/**
	 * The whole condition, or '' when there is nothing to filter.
	 *
	 * @since 1.0.0
	 * @param array<string, array<int, string>> $params  Normalized params (Params::parse()).
	 * @param string                            $match_mode 'all' or 'any' (WooCommerce's match setting).
	 * @param string                            $address 'billing' or 'shipping'.
	 * @param bool|null                         $hpos    Order storage (null = current).
	 * @return string Prepared SQL, e.g. "( ( … ) AND ( … ) )".
	 */
	public static function build( array $params, $match_mode, $address, $hpos = null ) {
		$fragments = self::fragments( $params, $address, null === $hpos ? Orders::hpos() : (bool) $hpos );

		/**
		 * Filter the SQL conditions of the Analytics order filters, one per param key. Values must
		 * be prepared SQL on the `{prefix}wc_order_stats` table (unaliased); they are joined with
		 * AND (match all) or OR (match any) and added to WooCommerce's own conditions with AND.
		 *
		 * @since 1.0.0
		 * @param array<string, string>             $fragments Param key => prepared SQL.
		 * @param array<string, array<int, string>> $params    Normalized params.
		 */
		$fragments = (array) apply_filters( 'apwc_analytics_filters_conditions', $fragments, $params );
		$fragments = array_filter( $fragments, static fn( $sql ) => is_string( $sql ) && '' !== trim( $sql ) );
		if ( ! $fragments ) {
			return '';
		}
		$glue = 'any' === $match_mode ? ' OR ' : ' AND ';
		return '( ' . implode( $glue, array_map( static fn( $sql ) => '( ' . $sql . ' )', $fragments ) ) . ' )';
	}

	/**
	 * One condition per param key.
	 *
	 * @since 1.0.0
	 * @param array<string, array<int, string>> $params  Normalized params.
	 * @param string                            $address 'billing' or 'shipping'.
	 * @param bool                              $hpos    Orders in the HPOS tables.
	 * @return array<string, string> Param key => prepared SQL.
	 */
	public static function fragments( array $params, $address, $hpos ) {
		$address = 'shipping' === $address ? 'shipping' : 'billing';
		$out     = array();
		foreach ( Params::FILTERS as $filter => $rules ) {
			foreach ( $rules as $rule ) {
				$key = Params::key( $filter, $rule );
				if ( empty( $params[ $key ] ) ) {
					continue;
				}
				$values = array_values( array_map( 'strval', $params[ $key ] ) );
				if ( in_array( Params::NOTHING, $values, true ) ) {
					$out[ $key ] = '1=0';
					continue;
				}
				$positive = Params::positive( $rule );
				if ( 'role' === $filter ) {
					$out[ $key ] = self::role( $values, $positive );
					continue;
				}
				$sub         = self::subquery( $filter, $values, $address, $hpos );
				$out[ $key ] = self::subject() . ( $positive ? ' IN ( ' : ' NOT IN ( ' ) . $sub . ' )';
			}
		}
		return $out;
	}

	/**
	 * The order a stats row belongs to: refunds follow their parent.
	 *
	 * @since 1.0.0
	 * @return string
	 */
	public static function subject() {
		global $wpdb;
		$table = self::stats_table();
		return $wpdb->prepare( 'IF( %i.parent_id > 0, %i.parent_id, %i.order_id )', $table, $table, $table );
	}

	/**
	 * WooCommerce's order stats table.
	 *
	 * @since 1.0.0
	 * @return string
	 */
	public static function stats_table() {
		global $wpdb;
		return $wpdb->prefix . 'wc_order_stats';
	}

	/**
	 * Subquery returning order IDs for a filter.
	 *
	 * @param string             $filter  Filter.
	 * @param array<int, string> $values  Clean values.
	 * @param string             $address Address type.
	 * @param bool               $hpos    HPOS.
	 * @return string Prepared SQL.
	 */
	private static function subquery( $filter, array $values, $address, $hpos ) {
		switch ( $filter ) {
			case 'country':
			case 'city':
				return self::address_field( $filter, $values, $address, $hpos );
			case 'state':
				return self::state( $values, $address, $hpos );
			case 'payment':
				return self::payment( $values, $hpos );
			case 'shipping':
				return self::shipping( $values );
		}
		return 'SELECT 0 FROM DUAL WHERE 1=0';
	}

	/**
	 * Orders whose address country or city is one of the values (exact, the column's collation
	 * decides case and accents).
	 *
	 * @param string             $field   'country' or 'city'.
	 * @param array<int, string> $values  Values.
	 * @param string             $address Address type.
	 * @param bool               $hpos    HPOS.
	 * @return string
	 */
	private static function address_field( $field, array $values, $address, $hpos ) {
		global $wpdb;
		$in = implode( ', ', array_fill( 0, count( $values ), '%s' ) );
		if ( $hpos ) {
			$column = 'country' === $field ? 'country' : 'city';
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $in is one %s placeholder per value, passed after the fixed arguments.
			return $wpdb->prepare( "SELECT order_id FROM %i WHERE address_type = %s AND %i IN ( {$in} )", $wpdb->prefix . 'wc_order_addresses', $address, $column, ...$values );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $in is one %s placeholder per value, passed after the fixed arguments.
		return $wpdb->prepare( "SELECT post_id FROM %i WHERE meta_key = %s AND meta_value IN ( {$in} )", $wpdb->postmeta, '_' . $address . '_' . $field, ...$values );
	}

	/**
	 * Orders whose address is in one of the states ("CC:ST").
	 *
	 * @param array<int, string> $values  Values.
	 * @param string             $address Address type.
	 * @param bool               $hpos    HPOS.
	 * @return string
	 */
	private static function state( array $values, $address, $hpos ) {
		global $wpdb;
		$pairs = array();
		foreach ( $values as $value ) {
			list( $country, $state ) = array_pad( explode( ':', $value, 2 ), 2, '' );
			$pairs[]                 = $country;
			$pairs[]                 = $state;
		}
		if ( $hpos ) {
			$or = implode( ' OR ', array_fill( 0, count( $values ), '( country = %s AND state = %s )' ) );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $or holds only %s placeholders, one set per value, passed after the fixed arguments.
			return $wpdb->prepare( "SELECT order_id FROM %i WHERE address_type = %s AND ( {$or} )", $wpdb->prefix . 'wc_order_addresses', $address, ...$pairs );
		}
		$or = implode( ' OR ', array_fill( 0, count( $values ), '( c.meta_value = %s AND s.meta_value = %s )' ) );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $or holds only %s placeholders, one set per value, passed after the fixed arguments.
		return $wpdb->prepare( "SELECT c.post_id FROM %i c JOIN %i s ON s.post_id = c.post_id AND s.meta_key = %s WHERE c.meta_key = %s AND ( {$or} )", $wpdb->postmeta, $wpdb->postmeta, '_' . $address . '_state', '_' . $address . '_country', ...$pairs );
	}

	/**
	 * Orders paid with one of the gateways.
	 *
	 * @param array<int, string> $values Gateway IDs.
	 * @param bool               $hpos   HPOS.
	 * @return string
	 */
	private static function payment( array $values, $hpos ) {
		global $wpdb;
		$in = implode( ', ', array_fill( 0, count( $values ), '%s' ) );
		if ( $hpos ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $in is one %s placeholder per value, passed after the fixed arguments.
			return $wpdb->prepare( "SELECT id FROM %i WHERE payment_method IN ( {$in} )", $wpdb->prefix . 'wc_orders', ...$values );
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $in is one %s placeholder per value, passed after the fixed arguments.
		return $wpdb->prepare( "SELECT post_id FROM %i WHERE meta_key = '_payment_method' AND meta_value IN ( {$in} )", $wpdb->postmeta, ...$values );
	}

	/**
	 * Orders with a shipping line of one of the methods: a type ("flat_rate", any zone) or a
	 * type and instance ("flat_rate:3"). Old orders stored "flat_rate:3" as the method ID.
	 * Order items are the same tables for HPOS and legacy storage.
	 *
	 * @param array<int, string> $values Method types or type:instance.
	 * @return string
	 */
	private static function shipping( array $values ) {
		global $wpdb;
		$meta  = $wpdb->prefix . 'woocommerce_order_itemmeta';
		$parts = array();
		foreach ( $values as $value ) {
			list( $type, $id ) = array_pad( explode( ':', $value, 2 ), 2, '' );
			if ( '' === $id ) {
				$parts[] = $wpdb->prepare( '( m.meta_value = %s OR m.meta_value LIKE %s )', $type, $wpdb->esc_like( $type . ':' ) . '%' );
				continue;
			}
			// The instance as an uncorrelated subquery: MySQL runs it once instead of joining a second
			// itemmeta row to every shipping line.
			$parts[] = $wpdb->prepare(
				"( ( m.meta_value = %s AND oi.order_item_id IN ( SELECT order_item_id FROM %i WHERE meta_key = 'instance_id' AND meta_value = %s ) ) OR m.meta_value = %s )",
				$type,
				$meta,
				$id,
				$type . ':' . $id
			);
		}
		$sql = $wpdb->prepare( "SELECT oi.order_id FROM %i oi JOIN %i m ON m.order_item_id = oi.order_item_id AND m.meta_key = 'method_id' WHERE oi.order_item_type = 'shipping'", $wpdb->prefix . 'woocommerce_order_items', $meta );
		return $sql . ' AND ( ' . implode( ' OR ', $parts ) . ' )';
	}

	/**
	 * Customer role on the order's customer (current role; "apwc_guest" = no account, including
	 * deleted accounts). Negative rules keep orders without a customer.
	 *
	 * @param array<int, string> $values   Roles.
	 * @param bool               $positive Is (true) or is not (false).
	 * @return string
	 */
	private static function role( array $values, $positive ) {
		global $wpdb;
		$lookup   = $wpdb->prefix . 'wc_customer_lookup';
		$table    = self::stats_table();
		$customer = $wpdb->prepare( '%i.customer_id', $table );
		$roles    = array_values( array_diff( $values, array( Params::GUEST ) ) );
		$parts    = array();
		if ( $roles ) {
			$likes = array_map( static fn( $role ) => '%' . $wpdb->esc_like( '"' . $role . '"' ) . '%', $roles );
			$or    = implode( ' OR ', array_fill( 0, count( $roles ), 'um.meta_value LIKE %s' ) );
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- $or holds only %s placeholders, one set per value, passed after the fixed arguments.
			$from = $wpdb->prepare( "FROM %i cl JOIN %i um ON um.user_id = cl.user_id WHERE um.meta_key = %s AND ( {$or} )", $lookup, $wpdb->usermeta, $wpdb->get_blog_prefix() . 'capabilities', ...$likes );
			// Negative: NOT EXISTS keeps orders without a customer (NULL never matches) and lets MySQL use an anti-join.
			$parts[] = $positive
				? "{$customer} IN ( SELECT cl.customer_id {$from} )"
				: "NOT EXISTS ( SELECT 1 {$from} AND cl.customer_id = {$customer} )";
		}
		if ( in_array( Params::GUEST, $values, true ) ) {
			$from    = $wpdb->prepare( 'FROM %i cl WHERE cl.user_id IS NOT NULL', $lookup );
			$parts[] = $positive
				? "NOT EXISTS ( SELECT 1 {$from} AND cl.customer_id = {$customer} )"
				: "{$customer} IN ( SELECT cl.customer_id {$from} )";
		}
		if ( ! $parts ) {
			return '1=0';
		}
		return '( ' . implode( $positive ? ' OR ' : ' AND ', $parts ) . ' )';
	}
}