<?php
/**
 * Filter helper functions for Clicks User Manager.
 *
 * @package WP_User_Management
 */

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

/**
 * Check whether WooCommerce is active.
 *
 * @return bool
 */
function wpum_is_woocommerce_active() {
	return class_exists( 'WooCommerce' );
}

/**
 * Check whether WooCommerce HPOS (custom order tables) is enabled.
 *
 * @return bool
 */
function wpum_is_wc_hpos_enabled() {
	if ( ! wpum_is_woocommerce_active() || ! function_exists( 'wc_get_container' ) ) {
		return false;
	}

	if ( ! class_exists( '\Automattic\WooCommerce\Utilities\OrderUtil' ) ) {
		return false;
	}

	try {
		return \Automattic\WooCommerce\Utilities\OrderUtil::custom_orders_table_usage_is_enabled();
	} catch ( \Throwable $e ) {
		return false;
	}
}

/**
 * Get an array of order counts per customer (user ID => order count).
 *
 * Works with both the WooCommerce HPOS (Custom Order Tables) and the
 * legacy post-based order storage. Only counts orders that belong to
 * a registered user (customer_id / _customer_user > 0) and excludes
 * trashed / auto-draft orders.
 *
 * Result is cached for 5 minutes via a transient to avoid repeating
 * this aggregate query on every page load.
 *
 * @return array<int,int> Map of user_id => order_count.
 */
function wpum_get_customer_order_counts() {
	if ( ! wpum_is_woocommerce_active() ) {
		return array();
	}

	$cached = get_transient( 'wpum_wc_order_counts' );
	if ( false !== $cached && is_array( $cached ) ) {
		return $cached;
	}

	global $wpdb;
	$counts  = array();
	$results = null;

	$hpos_enabled = wpum_is_wc_hpos_enabled();

	if ( $hpos_enabled ) {
		// HPOS: query the dedicated orders table.
		$table = $wpdb->prefix . 'wc_orders';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) );
		if ( $table_exists === $table ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
			$results = $wpdb->get_results(
				"SELECT customer_id, COUNT(*) AS order_count
				 FROM {$table}
				 WHERE customer_id > 0
				 AND type = 'shop_order'
				 AND status NOT IN ('trash','auto-draft','wc-checkout-draft')
				 GROUP BY customer_id"
			);
		} else {
			$hpos_enabled = false;
		}
	}

	if ( ! $hpos_enabled ) {
		// Legacy: orders stored as posts with _customer_user meta.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		$results = $wpdb->get_results(
			"SELECT pm.meta_value AS customer_id, COUNT(*) AS order_count
			 FROM {$wpdb->postmeta} pm
			 INNER JOIN {$wpdb->posts} p ON p.ID = pm.post_id
			 WHERE pm.meta_key = '_customer_user'
			 AND pm.meta_value > 0
			 AND p.post_type = 'shop_order'
			 AND p.post_status NOT IN ('trash','auto-draft')
			 GROUP BY pm.meta_value"
		);
	}

	if ( $results ) {
		foreach ( $results as $row ) {
			$counts[ (int) $row->customer_id ] = (int) $row->order_count;
		}
	}

	set_transient( 'wpum_wc_order_counts', $counts, 5 * MINUTE_IN_SECONDS );

	return $counts;
}

/**
 * Get user IDs that have at least one published post.
 *
 * @param string $post_type Post type. Default 'post'.
 * @return int[] User IDs.
 */
function wpum_get_users_with_published_posts( $post_type = 'post' ) {
	global $wpdb;

	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	$author_ids = $wpdb->get_col(
		$wpdb->prepare(
			"SELECT DISTINCT post_author FROM {$wpdb->posts}
			 WHERE post_status = 'publish' AND post_type = %s AND post_author > 0",
			$post_type
		)
	);

	return array_map( 'absint', $author_ids );
}

/**
 * Count a user's published posts (replaces deprecated count_user_posts third arg).
 *
 * @param int $user_id User ID.
 * @return int
 */
function wpum_count_user_published_posts( $user_id ) {
	global $wpdb;

	// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
	return (int) $wpdb->get_var(
		$wpdb->prepare(
			"SELECT COUNT(*) FROM {$wpdb->posts}
			 WHERE post_author = %d AND post_type = 'post' AND post_status = 'publish'",
			absint( $user_id )
		)
	);
}

/**
 * Build WP_User_Query args from GET/POST filter parameters.
 *
 * @param array $params Associative array of raw filter params.
 * @return array WP_User_Query compatible args.
 */
function wpum_build_query_args( $params = array() ) {
	$args = array(
		'count_total' => true,
	);

	// ---- Role filter ----
	if ( ! empty( $params['role'] ) ) {
		$args['role'] = sanitize_key( $params['role'] );
	}

	// ---- Search ----
	if ( ! empty( $params['search'] ) ) {
		$search              = sanitize_text_field( wp_unslash( $params['search'] ) );
		$args['search']      = '*' . $search . '*';
		$args['search_columns'] = array( 'user_login', 'user_email', 'display_name', 'user_nicename' );
	}

	// ---- Registration date range ----
	$date_query = array();
	if ( ! empty( $params['date_from'] ) ) {
		$date_query[] = array(
			'column'    => 'user_registered',
			'after'     => sanitize_text_field( wp_unslash( $params['date_from'] ) ) . ' 00:00:00',
			'inclusive' => true,
		);
	}
	if ( ! empty( $params['date_to'] ) ) {
		$date_query[] = array(
			'column'    => 'user_registered',
			'before'    => sanitize_text_field( wp_unslash( $params['date_to'] ) ) . ' 23:59:59',
			'inclusive' => true,
		);
	}
	if ( ! empty( $date_query ) ) {
		$date_query['relation'] = 'AND';
		$args['date_query']     = $date_query;
	}

	// ---- Last login / inactive filter ----
	$meta_query = array();

	if ( ! empty( $params['last_login'] ) ) {
		$last_login = sanitize_key( $params['last_login'] );

		switch ( $last_login ) {
			case 'today':
				$meta_query[] = array(
					'key'     => '_wpum_last_login',
					'value'   => array(
						gmdate( 'Y-m-d' ) . ' 00:00:00',
						gmdate( 'Y-m-d' ) . ' 23:59:59',
					),
					'compare' => 'BETWEEN',
					'type'    => 'DATETIME',
				);
				break;

			case 'this_week':
				$meta_query[] = array(
					'key'     => '_wpum_last_login',
					'value'   => gmdate( 'Y-m-d H:i:s', strtotime( 'monday this week' ) ),
					'compare' => '>=',
					'type'    => 'DATETIME',
				);
				break;

			case 'this_month':
				$meta_query[] = array(
					'key'     => '_wpum_last_login',
					'value'   => gmdate( 'Y-m-01 00:00:00' ),
					'compare' => '>=',
					'type'    => 'DATETIME',
				);
				break;

			case 'never':
				$meta_query[] = array(
					'key'     => '_wpum_last_login',
					'compare' => 'NOT EXISTS',
				);
				break;
		}
	}

	// ---- Inactive filter ----
	if ( ! empty( $params['inactive'] ) ) {
		$days   = absint( $params['inactive'] );
		$cutoff = gmdate( 'Y-m-d H:i:s', strtotime( "-{$days} days" ) );

		if ( $days > 0 ) {
			// Users who have a last_login but it's older than cutoff.
			$meta_query[] = array(
				'relation' => 'OR',
				array(
					'key'     => '_wpum_last_login',
					'value'   => $cutoff,
					'compare' => '<',
					'type'    => 'DATETIME',
				),
				array(
					'key'     => '_wpum_last_login',
					'compare' => 'NOT EXISTS',
				),
			);
		}
	}

	// ---- Pro advanced filters ----
	if ( wpum_is_pro() ) {
		// 1. Last login date range
		if ( ! empty( $params['login_from'] ) || ! empty( $params['login_to'] ) ) {
			$login_query = array(
				'key'  => '_wpum_last_login',
				'type' => 'DATETIME',
			);
			if ( ! empty( $params['login_from'] ) && ! empty( $params['login_to'] ) ) {
				$login_query['value']   = array(
					sanitize_text_field( $params['login_from'] ) . ' 00:00:00',
					sanitize_text_field( $params['login_to'] ) . ' 23:59:59',
				);
				$login_query['compare'] = 'BETWEEN';
			} elseif ( ! empty( $params['login_from'] ) ) {
				$login_query['value']   = sanitize_text_field( $params['login_from'] ) . ' 00:00:00';
				$login_query['compare'] = '>=';
			} else {
				$login_query['value']   = sanitize_text_field( $params['login_to'] ) . ' 23:59:59';
				$login_query['compare'] = '<=';
			}
			$meta_query[] = $login_query;
		}

		// 2. Custom meta filter
		if ( ! empty( $params['meta_key'] ) ) {
			$m_key = sanitize_key( $params['meta_key'] );
			$m_val = isset( $params['meta_value'] ) ? sanitize_text_field( $params['meta_value'] ) : '';
			$meta_query[] = array(
				'key'     => $m_key,
				'value'   => $m_val,
				'compare' => empty( $m_val ) ? 'EXISTS' : 'LIKE',
			);
		}

		// 3. WooCommerce LTV Filter
		if ( wpum_is_woocommerce_active() && ( ! empty( $params['ltv_min'] ) || ! empty( $params['ltv_max'] ) ) ) {
			$ltv_min = ! empty( $params['ltv_min'] ) ? floatval( $params['ltv_min'] ) : 0;
			$ltv_max = ! empty( $params['ltv_max'] ) ? floatval( $params['ltv_max'] ) : 999999999;
			$meta_query[] = array(
				'key'     => '_money_spent',
				'value'   => array( $ltv_min, $ltv_max ),
				'compare' => 'BETWEEN',
				'type'    => 'NUMERIC',
			);
		}

		// 4. WooCommerce AOV Filter
		if ( wpum_is_woocommerce_active() && ( ! empty( $params['aov_min'] ) || ! empty( $params['aov_max'] ) ) ) {
			global $wpdb;
			$aov_min = ! empty( $params['aov_min'] ) ? floatval( $params['aov_min'] ) : 0;
			$aov_max = ! empty( $params['aov_max'] ) ? floatval( $params['aov_max'] ) : 999999999;

			// Query user IDs matching AOV
			$matching_users = $wpdb->get_col(
				$wpdb->prepare(
					"SELECT m1.user_id 
					 FROM {$wpdb->usermeta} m1 
					 INNER JOIN {$wpdb->usermeta} m2 ON m1.user_id = m2.user_id
					 WHERE m1.meta_key = '_money_spent' 
					 AND m2.meta_key = '_order_count' 
					 AND CAST(m2.meta_value AS SIGNED) > 0
					 AND ( CAST(m1.meta_value AS DECIMAL(10,2)) / CAST(m2.meta_value AS DECIMAL(10,2)) ) BETWEEN %f AND %f",
					$aov_min,
					$aov_max
				)
			);

			$matching_users = array_map( 'absint', $matching_users );

			if ( empty( $matching_users ) ) {
				$args['include'] = array( 0 );
			} else {
				if ( ! empty( $args['include'] ) ) {
					$args['include'] = array_intersect( (array) $args['include'], $matching_users );
					if ( empty( $args['include'] ) ) {
						$args['include'] = array( 0 );
					}
				} else {
					$args['include'] = $matching_users;
				}
			}
		}
	}

	if ( ! empty( $meta_query ) ) {
		if ( count( $meta_query ) > 1 ) {
			$meta_query['relation'] = 'AND';
		}
		$args['meta_query'] = $meta_query; // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query
	}

	// ---- WooCommerce order filter ----
	if ( wpum_is_woocommerce_active() && ! empty( $params['orders'] ) ) {
		$order_filter  = sanitize_key( $params['orders'] );
		$order_counts  = wpum_get_customer_order_counts();
		$customer_ids  = array_keys( $order_counts );

		switch ( $order_filter ) {
			case 'has_orders':
				$args['include'] = ! empty( $customer_ids ) ? $customer_ids : array( 0 );
				break;

			case 'no_orders':
				if ( ! empty( $customer_ids ) ) {
					$args['exclude'] = $customer_ids;
				}
				break;

			case '1':
			case '2_5':
			case '6_plus':
				$matching = array();
				foreach ( $order_counts as $uid => $count ) {
					if ( '1' === $order_filter && 1 === $count ) {
						$matching[] = $uid;
					} elseif ( '2_5' === $order_filter && $count >= 2 && $count <= 5 ) {
						$matching[] = $uid;
					} elseif ( '6_plus' === $order_filter && $count >= 6 ) {
						$matching[] = $uid;
					}
				}
				$args['include'] = ! empty( $matching ) ? $matching : array( 0 );
				break;
		}
	}

	// ---- Post count filter ----
	if ( ! empty( $params['post_count'] ) ) {
		$post_count = sanitize_key( $params['post_count'] );
		if ( 'has_posts' === $post_count ) {
			$args['has_published_posts'] = array( 'post' );
		} elseif ( 'no_posts' === $post_count ) {
			$authors_with_posts = wpum_get_users_with_published_posts( 'post' );
			if ( ! empty( $authors_with_posts ) ) {
				$existing          = isset( $args['exclude'] ) ? (array) $args['exclude'] : array();
				$args['exclude']   = array_unique( array_merge( $existing, $authors_with_posts ) );
			}
		}
	}

	// ---- Orderby / order ----
	// UI column keys mapped to WP_User_Query orderby values.
	$orderby_map = array(
		'login'        => 'user_login',
		'email'        => 'user_email',
		'registered'   => 'user_registered',
		'display_name' => 'display_name',
		'last_login'   => 'meta_value',
		'post_count'   => 'post_count',
	);

	$orderby_param = ! empty( $params['orderby'] ) ? sanitize_key( $params['orderby'] ) : 'registered';

	if ( isset( $orderby_map[ $orderby_param ] ) ) {
		$args['orderby'] = $orderby_map[ $orderby_param ];
		if ( 'last_login' === $orderby_param ) {
			$args['meta_key'] = '_wpum_last_login';
		}
	} else {
		$args['orderby'] = 'user_registered';
	}

	$args['order'] = ( ! empty( $params['order'] ) && 'ASC' === strtoupper( $params['order'] ) ) ? 'ASC' : 'DESC';

	// ---- Pagination (always limit unless explicitly disabled, e.g. CSV export) ----
	if ( empty( $params['no_pagination'] ) ) {
		$allowed_per_page = array( 20, 50, 100 );
		$per_page         = isset( $params['per_page'] ) ? absint( $params['per_page'] ) : 20;
		if ( ! in_array( $per_page, $allowed_per_page, true ) ) {
			$per_page = 20;
		}
		$paged  = isset( $params['paged'] ) ? max( 1, absint( $params['paged'] ) ) : 1;
		$offset = ( $paged - 1 ) * $per_page;

		$args['number'] = $per_page;
		$args['offset'] = $offset;
	}

	return $args;
}

/**
 * Output role dropdown options.
 *
 * @param string $selected Currently selected role.
 */
function wpum_role_dropdown_options( $selected = '' ) {
	echo '<option value="">' . esc_html__( 'All Roles', 'clicks-user-manager' ) . '</option>';
	foreach ( wp_roles()->roles as $role_key => $role ) {
		$role_name = translate_user_role( $role['name'] );
		echo '<option value="' . esc_attr( $role_key ) . '" ' . selected( $selected, $role_key, false ) . '>' . esc_html( $role_name ) . '</option>';
	}
}
/**
 * Render the Pro version upsell banner.
 */
function wpum_upsell_banner() {
	if ( wpum_is_pro() ) {
		return;
	}

	$upgrade_url = '';
	if ( function_exists( 'cum_fs' ) ) {
		$upgrade_url = cum_fs()->get_upgrade_url();
	}
	if ( empty( $upgrade_url ) ) {
		$upgrade_url = admin_url( 'admin.php?page=clicks-user-manager-about' );
	}
	?>
	<div class="wpum-pro-banner">
		<span class="wpum-pro-icon">🚀</span>
		<div class="wpum-pro-text">
			<strong><?php esc_html_e( 'Upgrade to Pro', 'clicks-user-manager' ); ?></strong>
			<?php esc_html_e( '— Unlock advanced user notes, smart segments, WooCommerce lifetime spent and AOV filters, custom meta quick-editing, secure login impersonation, cron-scheduled exports via Webhook/Email, and more!', 'clicks-user-manager' ); ?>
			<a href="<?php echo esc_url( $upgrade_url ); ?>" style="margin-left: 10px; text-decoration: underline; font-weight: bold;"><?php esc_html_e( 'Upgrade Now &rarr;', 'clicks-user-manager' ); ?></a>
		</div>
	</div>
	<?php
}
