<?php
if ( ! function_exists( 'wpj_get_users_by_name_or_description' ) ) {
	function wpj_get_users_by_name_or_description( $keyword ) {
		global $wpdb;

		$user_query = "
			SELECT *
			FROM {$wpdb->prefix}users wu, {$wpdb->prefix}usermeta wum
			WHERE wu.ID = wum.user_ID
				AND (
					( wum.meta_key = 'first_name' AND wum.meta_value LIKE '%{$keyword}%' )
					OR ( wum.meta_key = 'last_name' AND wum.meta_value LIKE '%{$keyword}%' )
					OR ( wum.meta_key = 'description' AND wum.meta_value LIKE '%{$keyword}%' )
					OR ( wum.meta_key = 'user_company' AND wum.meta_value LIKE '%{$keyword}%' )
					OR wu.user_login LIKE '%{$keyword}%'
				)
			GROUP BY user_login
			ORDER BY wum.meta_key = 'completed_sales' DESC
			LIMIT 3
		";

		$user_query = apply_filters( 'wpj_suggested_users_query_filter', $user_query, $keyword );

		return $wpdb->get_results( $user_query );
	}
}

if ( ! function_exists( 'wpj_get_user_search_query' ) ) {
	function wpj_get_user_search_query( $args = [] ) {
		global $wpdb;

		if ( ! empty( $args['query_params'] ) ) {
			$args = array_merge( $args, $args['query_params'] );
		}

		$defaults = [
			'term'      => '',
			'location'  => '',
			'radius'    => '',
			'latitude'  => '',
			'longitude' => '',
			'skills'    => '',
			'level'     => '',
			'order'     => '',
		];

		$args = wp_parse_args( $args, $defaults );

		$term_get      = WPJ_Form::request( 'term', $args['term'] );
		$location_get  = WPJ_Form::request( 'location', $args['location'] );
		$radius_get    = WPJ_Form::request( 'radius', $args['radius'] );
		$latitude_get  = WPJ_Form::request( 'latitude', $args['latitude'] );
		$longitude_get = WPJ_Form::request( 'longitude', $args['longitude'] );
		$skills        = WPJ_Form::request( 'skills', $args['skills'] );
		$level         = WPJ_Form::request( 'level', $args['level'] );

		$order = ! empty( $args['query_params']['order'] ) ? $args['query_params']['order'] : wpj_get_current_post_sort_order( 'user' );

		$units = wpj_get_option( 'wpjobster_locations_unit' ) == 'kilometers' ? 6371 : 3959;

		if ( empty( $radius_get ) ) $radius_get = wpj_get_option( 'wpj_locations_radius_default' );
		if ( empty( $radius_get ) ) $radius_get = 10;

		if ( ! $location_get ) {
			$latitude_get  = '';
			$longitude_get = '';
			$radius_get    = '';
		}

		$user_table      = defined( 'CUSTOM_USER_TABLE' ) ? CUSTOM_USER_TABLE : $wpdb->prefix . 'users';
		$user_meta_table = defined( 'CUSTOM_USER_META_TABLE' ) ? CUSTOM_USER_META_TABLE : $wpdb->prefix . 'usermeta';

		$query = "SELECT u.*, um.*";

		if ( $latitude_get && $longitude_get ) {

			$query .= ", {$units} * acos( cos( radians( {$latitude_get} ) ) * cos( radians( um_lat.meta_value ) ) * cos( radians ( um_long.meta_value ) - radians( {$longitude_get} ) ) + sin( radians( {$latitude_get} ) ) * sin( radians ( um_lat.meta_value ) ) ) AS distance";

		}

		if ( $order == 'sales' ) {

			$query .= ",
				CASE
					WHEN o.sales IS NULL THEN 0
					ELSE o.sales
				END AS sales_number
			";

		}

		if ( $order == 'rating' ) {

			$query .= ",
				CASE
					WHEN r.total_rating IS NULL THEN 0
					ELSE ((r.total_rating * r.average_rating + 50 * 2.5) / (r.total_rating + 50))
				END AS weighted_rating
			";

		}

		$query .= " FROM {$user_table} u, {$user_meta_table} um ";

		if ( $order == 'sales' ) {

			$query .= "
				LEFT JOIN (
					SELECT uid, COUNT(*) AS sales
					FROM {$wpdb->prefix}job_orders
					WHERE done_seller = 1
					AND done_buyer = 1
					AND closed = 0
					GROUP BY uid
				) o ON um.user_id = o.uid
			";

		}

		if ( $order == 'rating' ) {

			$query .= "
				LEFT JOIN (
					SELECT uid, COUNT(*) AS total_rating, AVG(grade) AS average_rating
					FROM {$wpdb->prefix}job_ratings
					GROUP BY uid
				) r ON um.user_id = r.uid
			";

		}

		if ( $latitude_get && $longitude_get ) {

			$query .= " INNER JOIN {$user_meta_table} um_lat ON ( um.user_id = um_lat.user_ID AND um_lat.meta_key = 'wpj_user_latitude' ) ";

			$query .= " INNER JOIN {$user_meta_table} um_long ON ( um.user_id = um_long.user_ID AND um_long.meta_key = 'wpj_user_longitude' ) ";

		}

		if ( ! preg_match( '/,/', $location_get ) && $location_get ) {

			$query .= " INNER JOIN {$user_meta_table} um_location ON ( um.user_id = um_location.user_ID AND ( um_location.meta_key = 'city' OR um_location.meta_key = 'country' ) ) ";

		}

		if ( $skills ) {

			$query .= " INNER JOIN {$user_meta_table} um_skills ON um.user_id = um_skills.user_id AND um_skills.meta_key = 'user_skills' ";

		}

		if ( $level || $level == 0 ) {

			$query .= " INNER JOIN {$user_meta_table} um_level ON um.user_id = um_level.user_id AND um_level.meta_key = 'user_level' ";

		}

		$query .= " WHERE u.ID = um.user_id ";

		if ( $term_get ) {

			$query .= " AND ( u.user_login LIKE '%{$term_get}%'
				OR u.display_name LIKE '%{$term_get}%'
				OR ( um.meta_key IN ( 'first_name', 'last_name', 'description', 'user_company' ) AND um.meta_value LIKE '%{$term_get}%' )
			) ";

		}

		if ( ! preg_match( '/,/', $location_get ) && $location_get ) {

			$query .= " AND um_location.meta_value LIKE '%{$location_get}%' ";

		}

		if ( $skills ) {
			$skills = explode( '|', $skills );

			$query .= " AND ( ";

			foreach ( $skills as $key => $skill ) {
				if ( $key > 0 ) {
					$query .= " AND ";
				}

				$query .= "um_skills.meta_value REGEXP '" . sprintf( '^%1$s$|s:%2$u:"%1$s";', $skill, strlen( $skill ) ) . "'";
			}

			$query .= ") ";
		}

		if ( $level || $level == 0 ) {

			$query .= " AND um_level.meta_value = " . $level . " ";
			
		}

		$query .= apply_filters( 'wpj_user_search_before_group_by_query_filter', " GROUP BY u.ID " );

		if ( $latitude_get && $longitude_get ) {

			$query .= " HAVING distance < {$radius_get} ";

		}

		if ( $order == 'sales' ) {

			$query .= " ORDER BY sales_number DESC";

		} elseif ( $order == 'rating' ) {

			$query .= " ORDER BY weighted_rating DESC";

		} elseif ( $order == 'new' ) {

			$query .= " ORDER BY user_registered DESC";

		} elseif ( $order == 'old' ) {

			$query .= " ORDER BY user_registered ASC";

		} else {

			$query .= " ORDER BY u.ID ASC";

		}

		return $query;
	}
}