<?php
/**
 * Campaign segment queries for manual send-now campaigns.
 *
 * @package CartCue
 */

namespace CartCue;

defined( 'ABSPATH' ) || exit;

final class CampaignSegments {

	/**
	 * @return array<string, string>
	 */
	public static function labels(): array {
		return [
			'marketing_opt_in'   => __( 'Marketing opt-in (includes guest subscribers)', 'cartcue-sales-automation' ),
			'all'                => __( 'All registered customers', 'cartcue-sales-automation' ),
			'customers'          => __( 'Role: Customer', 'cartcue-sales-automation' ),
			'no_purchase'        => __( 'Registered — never purchased', 'cartcue-sales-automation' ),
			'purchased'          => __( 'Has at least one completed order', 'cartcue-sales-automation' ),
			'inactive_30'        => __( 'No order in last 30 days', 'cartcue-sales-automation' ),
			'inactive_60'        => __( 'No order in last 60 days', 'cartcue-sales-automation' ),
			'payment_method'     => __( 'Used payment method (any order)', 'cartcue-sales-automation' ),
			'used_coupon'        => __( 'Used coupon code (any order)', 'cartcue-sales-automation' ),
			'email_inactive'     => __( 'No CartCue email opens recently', 'cartcue-sales-automation' ),
		];
	}

	/**
	 * @param array<string, string> $params Segment params (payment_method, coupon_code, email_inactive_days).
	 * @return list<array{user_id: int, email: string, first_name: string, last_name: string}>
	 */
	public static function get_recipients( string $segment, int $limit, array $params = [] ): array {
		$limit = self::cap_limit( $limit );

		switch ( $segment ) {
			case 'marketing_opt_in':
				return self::recipients_marketing_opt_in( $limit );
			case 'payment_method':
				return CampaignRecipient::from_user_ids(
					self::users_by_payment_method( (string) ( $params['payment_method'] ?? '' ), $limit )
				);
			case 'used_coupon':
				return CampaignRecipient::from_user_ids(
					self::users_by_coupon( (string) ( $params['coupon_code'] ?? '' ), $limit )
				);
			case 'email_inactive':
				$days = max( 1, (int) ( $params['email_inactive_days'] ?? 90 ) );
				return CampaignRecipient::from_user_ids( self::users_email_inactive( $days, $limit ) );
			case 'no_purchase':
				return CampaignRecipient::from_user_ids( self::users_never_purchased( $limit ) );
			case 'purchased':
				return CampaignRecipient::from_user_ids( self::users_with_orders( $limit ) );
			case 'inactive_30':
				return CampaignRecipient::from_user_ids( self::users_inactive_days( 30, $limit ) );
			case 'inactive_60':
				return CampaignRecipient::from_user_ids( self::users_inactive_days( 60, $limit ) );
			case 'customers':
			case 'role_customer':
				return CampaignRecipient::from_user_ids( self::users_by_role( $limit ) );
			default:
				return CampaignRecipient::from_user_ids( self::users_all( $limit ) );
		}
	}

	/**
	 * @param array<string, string> $params
	 * @return array{count: int, registered: int, guests: int}
	 */
	public static function count_details( string $segment, array $params = [] ): array {
		$recipients = self::get_recipients( $segment, 5000, $params );
		$registered = 0;
		$guests     = 0;
		foreach ( $recipients as $row ) {
			if ( ! empty( $row['user_id'] ) ) {
				$registered++;
			} else {
				$guests++;
			}
		}

		return [
			'count'      => count( $recipients ),
			'registered' => $registered,
			'guests'     => $guests,
		];
	}

	/**
	 * @param array<string, string> $params Segment params (payment_method, coupon_code, email_inactive_days).
	 * @return int[]
	 */
	public static function get_user_ids( string $segment, int $limit, array $params = [] ): array {
		$ids = [];
		foreach ( self::get_recipients( $segment, $limit, $params ) as $row ) {
			if ( ! empty( $row['user_id'] ) ) {
				$ids[] = (int) $row['user_id'];
			}
		}
		return $ids;
	}

	public static function count( string $segment, array $params = [] ): int {
		return self::count_details( $segment, $params )['count'];
	}

	private static function cap_limit( int $limit ): int {
		if ( $limit <= 0 ) {
			return 5000;
		}
		return min( 5000, $limit );
	}

	/**
	 * @return list<array{user_id: int, email: string, first_name: string, last_name: string}>
	 */
	private static function recipients_marketing_opt_in( int $limit ): array {
		global $wpdb;

		$by_email = [];

		$user_query = new \WP_User_Query(
			[
				'number'     => $limit * 2,
				'meta_key'   => MarketingConsent::USER_META_KEY,
				'meta_value' => '1',
				'fields'     => 'all',
			]
		);

		foreach ( $user_query->get_results() as $user ) {
			if ( ! $user instanceof \WP_User ) {
				continue;
			}
			$row = CampaignRecipient::from_user_id( $user->ID );
			if ( ! $row || ! self::is_sendable_opt_in( $row['email'] ) ) {
				continue;
			}
			$by_email[ strtolower( $row['email'] ) ] = $row;
			if ( count( $by_email ) >= $limit ) {
				return array_values( $by_email );
			}
		}

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$guest_emails = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT email FROM %i WHERE status = 1 ORDER BY granted_at DESC LIMIT %d',
				DB::consents(),
				$limit * 2
			)
		);

		foreach ( (array) $guest_emails as $raw_email ) {
			$email = strtolower( trim( (string) $raw_email ) );
			if ( isset( $by_email[ $email ] ) || ! self::is_sendable_opt_in( $email ) ) {
				continue;
			}

			$user = get_user_by( 'email', $email );
			if ( $user ) {
				$row = CampaignRecipient::from_user_id( $user->ID );
			} else {
				$row = [
					'user_id'    => 0,
					'email'      => $email,
					'first_name' => '',
					'last_name'  => '',
				];
			}

			if ( $row && is_email( $row['email'] ) ) {
				$by_email[ $email ] = $row;
			}

			if ( count( $by_email ) >= $limit ) {
				break;
			}
		}

		return array_values( $by_email );
	}

	private static function is_sendable_opt_in( string $email ): bool {
		$email = strtolower( trim( $email ) );
		if ( ! is_email( $email ) ) {
			return false;
		}
		if ( Unsubscribe::is_suppressed( $email ) ) {
			return false;
		}
		return MarketingConsent::has_consent( $email );
	}

	/** @return int[] */
	private static function users_all( int $limit ): array {
		$result = get_users(
			[
				'fields'   => 'ID',
				'number'   => $limit,
				'role__in' => [ 'customer', 'subscriber', 'administrator' ],
			]
		);
		return array_map( 'intval', is_array( $result ) ? $result : [] );
	}

	/** @return int[] */
	private static function users_by_role( int $limit ): array {
		$result = get_users(
			[
				'fields'   => 'ID',
				'number'   => $limit,
				'role__in' => [ 'customer', 'subscriber' ],
			]
		);
		return array_map( 'intval', is_array( $result ) ? $result : [] );
	}

	/** @return int[] */
	private static function users_inactive_days( int $days, int $limit ): array {
		$result = get_users(
			[
				'fields'         => 'ID',
				'number'         => $limit,
				'role__in'       => [ 'customer', 'subscriber' ],
				'meta_key'       => '_wc_last_order_date',
				'meta_value'     => gmdate( 'Y-m-d', strtotime( '-' . $days . ' days' ) ),
				'meta_compare'   => '<=',
			]
		);
		return array_map( 'intval', is_array( $result ) ? $result : [] );
	}

	/** @return int[] */
	private static function users_never_purchased( int $limit ): array {
		global $wpdb;
		$purchaser_emails = self::purchaser_emails();
		$users            = get_users(
			[
				'fields'   => [ 'ID', 'user_email' ],
				'role__in' => [ 'customer', 'subscriber' ],
				'number'   => $limit * 3,
			]
		);
		$never            = [];
		foreach ( $users as $u ) {
			if ( ! in_array( strtolower( $u->user_email ), $purchaser_emails, true ) ) {
				$never[] = (int) $u->ID;
				if ( count( $never ) >= $limit ) {
					break;
				}
			}
		}
		return $never;
	}

	/** @return int[] */
	private static function users_with_orders( int $limit ): array {
		global $wpdb;
		$purchaser_emails = self::purchaser_emails();
		if ( empty( $purchaser_emails ) ) {
			return [];
		}
		$ids = [];
		foreach ( array_chunk( $purchaser_emails, 200 ) as $chunk ) {
			$placeholders = implode( ',', array_fill( 0, count( $chunk ), '%s' ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
			$found = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT ID FROM {$wpdb->users} WHERE LOWER(user_email) IN ($placeholders) LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
					array_merge( $chunk, [ $limit ] ) // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
				)
			);
			foreach ( (array) $found as $id ) {
				$ids[] = (int) $id;
				if ( count( $ids ) >= $limit ) {
					return $ids;
				}
			}
		}
		return $ids;
	}

	/** @return string[] */
	private static function purchaser_emails(): array {
		global $wpdb;
		if ( class_exists( '\Automattic\WooCommerce\Utilities\OrderUtil' )
			&& \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ) {
			$table = $wpdb->prefix . 'wc_orders';
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- HPOS table from $wpdb->prefix.
			$emails = $wpdb->get_col(
				"SELECT DISTINCT billing_email FROM {$table} WHERE type = 'shop_order' AND billing_email != '' AND status NOT IN ('trash', 'auto-draft')" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- HPOS table from $wpdb->prefix.
			);
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$emails = $wpdb->get_col(
				"SELECT DISTINCT pm.meta_value FROM {$wpdb->postmeta} pm
				 INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
				 WHERE pm.meta_key = '_billing_email' AND p.post_type = 'shop_order' AND pm.meta_value != ''"
			);
		}
		return array_values( array_unique( array_map( 'strtolower', array_filter( (array) $emails ) ) ) );
	}

	/** @return int[] */
	private static function users_by_payment_method( string $method, int $limit ): array {
		$method = sanitize_key( $method );
		if ( '' === $method ) {
			return [];
		}
		global $wpdb;
		$emails = self::emails_with_order_meta( '_payment_method', $method, $limit * 2 );
		return self::user_ids_from_emails( $emails, $limit );
	}

	/** @return int[] */
	private static function users_by_coupon( string $code, int $limit ): array {
		$code = wc_format_coupon_code( sanitize_text_field( $code ) );
		if ( '' === $code ) {
			return [];
		}
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$order_ids = $wpdb->get_col(
			$wpdb->prepare(
				"SELECT order_id FROM {$wpdb->prefix}woocommerce_order_items oi
				 INNER JOIN {$wpdb->prefix}woocommerce_order_itemmeta oim ON oim.order_item_id = oi.order_item_id
				 WHERE oi.order_item_type = 'coupon' AND oim.meta_key = 'discount_amount' AND oi.order_item_name = %s
				 LIMIT %d",
				$code,
				$limit * 5
			)
		);
		$emails = [];
		foreach ( array_unique( array_map( 'intval', (array) $order_ids ) ) as $order_id ) {
			$order = wc_get_order( $order_id );
			if ( $order ) {
				$emails[] = strtolower( $order->get_billing_email() );
			}
		}
		return self::user_ids_from_emails( $emails, $limit );
	}

	/** @return int[] */
	private static function users_email_inactive( int $days, int $limit ): array {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$active = $wpdb->get_col(
			$wpdb->prepare(
				'SELECT DISTINCT customer_email FROM %i
				 WHERE opened_at IS NOT NULL AND sent_at >= DATE_SUB(NOW(), INTERVAL %d DAY)',
				DB::email_tracking(),
				$days
			)
		);
		$active = array_map( 'strtolower', array_filter( (array) $active ) );
		$users  = get_users(
			[
				'fields'   => [ 'ID', 'user_email' ],
				'role__in' => [ 'customer', 'subscriber' ],
				'number'   => $limit * 3,
			]
		);
		$ids = [];
		foreach ( $users as $u ) {
			$email = strtolower( $u->user_email );
			if ( ! in_array( $email, $active, true ) ) {
				$ids[] = (int) $u->ID;
				if ( count( $ids ) >= $limit ) {
					break;
				}
			}
		}
		return $ids;
	}

	/**
	 * @param string[] $emails
	 * @return int[]
	 */
	private static function user_ids_from_emails( array $emails, int $limit ): array {
		global $wpdb;
		$emails = array_values( array_unique( array_filter( array_map( 'strtolower', $emails ) ) ) );
		if ( empty( $emails ) ) {
			return [];
		}
		$ids = [];
		foreach ( array_chunk( $emails, 100 ) as $chunk ) {
			$placeholders = implode( ',', array_fill( 0, count( $chunk ), '%s' ) );
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
			$found = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT ID FROM {$wpdb->users} WHERE LOWER(user_email) IN ($placeholders)", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
					$chunk // phpcs:ignore WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
				)
			);
			foreach ( (array) $found as $id ) {
				$ids[] = (int) $id;
				if ( count( $ids ) >= $limit ) {
					return $ids;
				}
			}
		}
		return $ids;
	}

	/** @return string[] */
	private static function emails_with_order_meta( string $meta_key, string $meta_value, int $limit ): array {
		global $wpdb;
		if ( class_exists( '\Automattic\WooCommerce\Utilities\OrderUtil' )
			&& \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled() ) {
			$table = $wpdb->prefix . 'wc_orders';
			$col   = '_payment_method' === $meta_key ? 'payment_method' : 'payment_method';
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- HPOS table/column from fixed whitelist.
			return $wpdb->get_col(
				$wpdb->prepare(
					"SELECT DISTINCT billing_email FROM {$table} WHERE type = 'shop_order' AND {$col} = %s AND billing_email != '' LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- HPOS table/column from fixed whitelist.
					$meta_value,
					$limit
				)
			);
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		return $wpdb->get_col(
			$wpdb->prepare(
				"SELECT DISTINCT pm2.meta_value FROM {$wpdb->postmeta} pm
				 INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
				 INNER JOIN {$wpdb->postmeta} pm2 ON pm2.post_id = p.ID AND pm2.meta_key = '_billing_email'
				 WHERE pm.meta_key = %s AND pm.meta_value = %s AND p.post_type = 'shop_order' AND pm2.meta_value != ''
				 LIMIT %d",
				$meta_key,
				$meta_value,
				$limit
			)
		);
	}
}
