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

		return $wpdb->get_results(
			"
			SELECT ID, post_title, post_content
			FROM {$wpdb->prefix}posts p
			LEFT JOIN {$wpdb->prefix}postmeta AS wpm ON ( p.ID = wpm.post_ID AND wpm.meta_key = 'active' )
			WHERE ( ( post_title LIKE '%" . $keyword . "%' )
					OR ( post_content LIKE '%" . $keyword . "%' ) )
				AND post_type = 'job'
				AND post_status = 'publish'
				AND wpm.meta_value = '1'
			ORDER BY post_title ASC
			LIMIT 6
			"
		);
	}
}

if ( ! function_exists( 'wpj_get_jobs_keywords_occurances' ) ) {
	function wpj_get_jobs_keywords_occurances( $keywords ) {
		global $wpdb;

		$split_stemmed = split( ' ', $keywords );

		$sql = "
			SELECT DISTINCT COUNT(*) as occurences, {$wpdb->prefix}posts.post_title, {$wpdb->prefix}posts.ID
			FROM {$wpdb->prefix}posts, {$wpdb->prefix}postmeta
			WHERE {$wpdb->prefix}posts.post_status = 'publish'
				AND {$wpdb->prefix}posts.post_type = 'job'
				AND {$wpdb->prefix}posts.ID = {$wpdb->prefix}postmeta.post_id
				AND {$wpdb->prefix}postmeta.meta_key = 'closed'
				AND {$wpdb->prefix}postmeta.meta_value = '0'

		AND (";

			while ( list ( $key, $val ) = each ( $split_stemmed ) ) {
				if ( $val != '' && strlen ( $val ) > 0 ) {
					$sql .= "(" . $wpdb->prefix . "posts.post_title LIKE '%" . $val . "%' OR " . $wpdb->prefix . "posts.post_content LIKE '%" . $val . "%' ) OR";
				}
			}

			$sql = substr ( $sql,0, ( strlen ( $sql )-3 ) ); //this will eat the last OR

		$sql .= ") GROUP BY " . $wpdb->prefix . "posts.post_title ORDER BY occurences DESC LIMIT 10";

		return $wpdb->get_results( $sql, ARRAY_A );
	}
}

if ( ! function_exists( 'wpj_get_job_search_query' ) ) {
	function wpj_get_job_search_query( $args = [] ) {
		$uid = ! empty( $args['uid'] ) ? $args['uid'] : '';

		$post_not_in = ! empty( $args['query_params']['post__not_in'] ) ? $args['query_params']['post__not_in'] : [];

		if ( $post_not_in ) $post_not_in = implode( ',', $post_not_in );

		$featured = isset( $args['query_params']['featured'] ) && is_numeric( $args['query_params']['featured'] ) ? $args['query_params']['featured'] : 1;

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

		$location_default = $lat_default = $lng_default = '';

		if ( wpj_is_page( 'wpjobster_advanced_search_id' ) ) {
			if ( wpj_get_option( 'wpjobster_html5_geolocation_enable' ) == 'yes' && wpj_get_option( 'wpjobster_location' ) == 'yes' ) {
				$location_default = WPJ_Form::cookie( 'wpj_location', '' );
				$lat_default      = WPJ_Form::cookie( 'wpj_lat', '' );
				$lng_default      = WPJ_Form::cookie( 'wpj_lng', '' );
			}
		}

		$category     = ! empty( $args['query_params']['category'] ) ? $args['query_params']['category'] : '';
		$archive_val  = isset( get_queried_object()->slug ) ? get_queried_object()->slug : $category;
		$category_get = WPJ_Form::get( 'category', $archive_val );

		$term_get        = WPJ_Form::get( 'term' );
		$tag_get         = WPJ_Form::get( 'tag' );
		$min_price_get   = WPJ_Form::get( 'min_price' );
		$max_price_get   = WPJ_Form::get( 'max_price' );
		$radius_get      = WPJ_Form::get( 'radius' );
		$location_get    = WPJ_Form::get( 'location', $location_default );
		$latitude_get    = WPJ_Form::get( 'latitude', $lat_default );
		$longitude_get   = WPJ_Form::get( 'longitude', $lng_default );
		$rating_get      = WPJ_Form::get( 'min_rating' );
		$price_type_get  = WPJ_Form::get( 'price_type' );
		$max_days_get    = WPJ_Form::get( 'max_days' );
		$no_deliv_get    = WPJ_Form::get( 'without_delivery' );
		$user_level_get  = WPJ_Form::get( 'user_level' );
		$user_status_get = WPJ_Form::get( 'user_status' );

		$units  = wpj_get_option( 'wpjobster_locations_unit' ) == 'kilometers' ? 6371 : 3959;
		$radius = ! empty( $radius_get ) ? $radius_get : wpj_get_option( 'wpj_locations_radius_default' );

		if ( ! $radius ) {
			$radius = 10;
		}

		$feature_enabled = $featured && wpj_get_option( 'wpjobster_featured_enable' ) == 'yes';

		if ( $category_get ) {
			if ( is_numeric( $category_get ) ) $cats = get_term_by( 'term_id', $category_get, 'job_cat' );
			else $cats = get_term_by( 'slug', $category_get, 'job_cat' );

			$featured_meta_key = 'category_featured_now';

			if ( isset( $cats->parent ) && $cats->parent > 0 ) {
				$featured_meta_key = 'subcategory_featured_now';
			}

			if ( ! empty( $cats->term_id ) ) {
				$cat_childrens   = get_terms( $cats->taxonomy, [ 'parent' => $cats->term_id, 'hide_empty' => false, 'fields' => 'ids' ] );
				$cat_childrens[] = $cats->term_id;
				$categories      = implode( ',', $cat_childrens );
			}

		} else $featured_meta_key = 'home_featured_now';

		if ( $tag_get ) {

			if ( ! is_array( $tag_get ) ) $tag_get = [$tag_get];

			$tag_childrens = [];

			foreach ( $tag_get as $key => $tag ) {
				$tgs = get_term_by( 'slug', $tag, 'post_tag' );

				if ( ! empty( $tgs->term_id ) ) {
					$tag_childrens[] = $tgs->term_id;
				}
			}

			if ( $tag_childrens ) {
				$tags = implode( ',', $tag_childrens );
			}
		}

		$min_price = $min_price_get ? str_replace( ",", ".", $min_price_get ) : 0;
		$max_price = $max_price_get ? str_replace( ",", ".", $max_price_get ) : '';

		$delivery_comparator = $no_deliv_get ? '=' : '<>';
		$delivery_logical_op = $no_deliv_get ? 'OR' : 'AND';

		/* BUILD QUERY */

		global $wpdb;

		$query = "SELECT p.ID, p.post_title, p.post_content, p.post_author";

		if ( $order == 'price_lowest' || $order == 'price_highest' )
			$query .= ", MIN( CAST( pm_price.meta_value AS UNSIGNED ) ) AS min_price ";

		if ( ( $location_get && $latitude_get && $longitude_get ) || ( $order == 'distance' && $latitude_get && $longitude_get ) )
			$query .= ", {$units} * acos( cos( radians( {$latitude_get} ) ) * cos( radians( pm_lat.meta_value ) ) * cos( radians ( pm_long.meta_value ) - radians( {$longitude_get} ) ) + sin( radians( {$latitude_get} ) ) * sin( radians ( pm_lat.meta_value ) ) ) AS distance";

		$query .= " FROM {$wpdb->prefix}posts p ";
		$query .= " INNER JOIN {$wpdb->prefix}postmeta pm ON pm.post_id = p.id ";

		if ( $location_get || ( $order == 'distance' && $latitude_get && $longitude_get ) ) {
			$query .= " INNER JOIN {$wpdb->prefix}postmeta pm_lat ON ( pm.post_id = pm_lat.post_id AND pm_lat.meta_key = 'lat' ) ";
			$query .= " INNER JOIN {$wpdb->prefix}postmeta pm_long ON ( pm.post_id = pm_long.post_id AND pm_long.meta_key = 'long' ) ";
		}

		if ( $category_get )
			$query .= " INNER JOIN {$wpdb->prefix}term_relationships tr_c ON tr_c.object_id = pm.post_id ";

		if ( $tag_get )
			$query .= " INNER JOIN {$wpdb->prefix}term_relationships tr_t ON tr_t.object_id = pm.post_id ";

		if ( $rating_get || $order == 'rating' )
			$query .= " INNER JOIN {$wpdb->prefix}postmeta pm_rating ON pm.post_id = pm_rating.post_id AND pm_rating.meta_key = 'job_rating' ";

		if ( $order == 'reviews' )
			$query .= " INNER JOIN {$wpdb->prefix}postmeta pm_reviews ON pm.post_id = pm_reviews.post_id AND pm_reviews.meta_key = 'job_reviews' ";

		if ( $max_days_get )
			$query .= " INNER JOIN {$wpdb->prefix}postmeta pm_max_days ON pm.post_id = pm_max_days.post_id AND pm_max_days.meta_key = 'max_days' ";

		if ( is_numeric( $user_level_get ) )
			$query .= " INNER JOIN {$wpdb->prefix}usermeta um_level ON p.post_author = um_level.user_id AND um_level.meta_key = 'user_level' ";

		if ( $user_status_get && wpj_get_option( 'wpjobster_en_user_online_status' ) != 'no' )
			$query .= " INNER JOIN {$wpdb->prefix}usermeta um_status ON p.post_author = um_status.user_id AND um_status.meta_key = 'last_user_login' ";

		if ( $order == 'price_lowest' || $order == 'price_highest' || $price_type_get == 'package' || $min_price_get || $max_price_get )
			$query .= " INNER JOIN {$wpdb->prefix}postmeta pm_package ON pm.post_id = pm_package.post_id ";

		if ( $price_type_get )
			$query .= " LEFT JOIN {$wpdb->prefix}postmeta pm_pricetype ON pm.post_id = pm_pricetype.post_id AND pm_pricetype.meta_key = 'price_type' ";

		if ( $order == 'price_lowest' || $order == 'price_highest' || $min_price_get || $max_price_get ) {
			$include_price_sql = $price_type_get != 'package' ? " pm_price.meta_key = 'price' OR " : '';

			$query .= " INNER JOIN {$wpdb->prefix}postmeta pm_price ON pm.post_id = pm_price.post_id AND ( {$include_price_sql} pm_price.meta_key = 'package_price_1' OR pm_price.meta_key = 'package_price_2' OR pm_price.meta_key = 'package_price_3' ) ";

		}

		if ( $order == 'views' )
			$query .= " INNER JOIN {$wpdb->prefix}postmeta pm_views ON pm.post_id = pm_views.post_id AND pm_views.meta_key = 'job_views_counter' ";

		if ( $feature_enabled )
			$query .= " LEFT JOIN {$wpdb->prefix}postmeta pm_featured ON pm.post_id = pm_featured.post_id AND pm_featured.meta_key = '{$featured_meta_key}' ";

		$query .= " WHERE p.ID = pm.post_id AND p.post_type = 'job' AND p.post_status = 'publish' AND ( pm.meta_key = 'active' AND pm.meta_value = '1' ) ";

			if ( $uid )
				$query .= " AND p.post_author = {$uid} ";

			if ( $post_not_in )
				$query .= " AND p.ID NOT IN ({$post_not_in}) ";

			if ( $term_get )
				$query .= " AND ( p.post_title LIKE '%{$term_get}%' OR p.post_content LIKE '%{$term_get}%' ) ";

			if ( $category_get && ! empty( $categories ) )
				$query .= " AND tr_c.term_taxonomy_id IN ({$categories}) ";

			if ( $tag_get && ! empty( $tags ) )
				$query .= " AND tr_t.term_taxonomy_id IN ({$tags}) ";

			if ( $rating_get )
				$query .= " AND ( pm_rating.meta_key = 'job_rating' AND pm_rating.meta_value >= {$rating_get} ) ";

			if ( $price_type_get ) {
				if ( $price_type_get == 'fixed' )
					$query .= " AND ( pm_pricetype.post_id IS NULL OR pm_pricetype.meta_value = 'fixed' OR pm_pricetype.meta_value = '' ) ";

				elseif ( $price_type_get == 'package' )
					$query .= " AND ( pm_package.meta_key = 'job_packages' AND pm_package.meta_value = 'yes' ) ";

				else
					$query .= " AND ( pm_pricetype.meta_value = '{$price_type_get}' ) ";

			}

			if ( is_numeric( $user_level_get ) )
				$query .= " AND um_level.meta_value = {$user_level_get} ";

			if ( $user_status_get && wpj_get_option( 'wpjobster_en_user_online_status' ) != 'no' )
				$query .= " AND '{$user_status_get}' = (
					CASE
						WHEN TIMESTAMPDIFF(MINUTE, FROM_UNIXTIME(um_status.meta_value), CURRENT_TIMESTAMP()) BETWEEN 0 AND 5 THEN 'online'
						ELSE 'offline'
					END
				) ";

			if ( ! $min_price && is_numeric( $max_price ) ) {

				$query .= " AND ( ";

					if ( $price_type_get != 'package' )
						$query .= " (
							pm_package.meta_key = 'job_packages' AND pm_package.meta_value <> 'yes' AND pm_price.meta_key = 'price'
							AND ( pm_price.meta_value <= {$max_price} OR pm_price.meta_value = '' OR pm_price.meta_value = 0 )
						) ";

					if ( ! $price_type_get ) { $query .= " OR ( "; }

						if ( ! $price_type_get || $price_type_get == 'package' )
							$query .= "
								( pm_package.meta_key = 'job_packages' AND pm_package.meta_value = 'yes' )
								AND (
									( pm_price.meta_key = 'package_price_1' AND pm_price.meta_value <= {$max_price} )
									OR ( pm_price.meta_key = 'package_price_2' AND pm_price.meta_value <= {$max_price} )
									OR ( pm_price.meta_key = 'package_price_3' AND pm_price.meta_value <= {$max_price} )
								)
							";

					if ( ! $price_type_get ) { $query .= " ) "; }

				$query .= " ) ";

			} elseif ( $min_price_get && is_numeric( $min_price ) && ! $max_price ) {

				$include_empty_sql = $min_price == 0 ? " OR pm_price.meta_value = '' OR pm_price.meta_value = 0 " : "";

				$query .= " AND ( ";

					if ( $price_type_get != 'package' )
						$query .= " (
							pm_package.meta_key = 'job_packages' AND pm_package.meta_value <> 'yes' AND pm_price.meta_key = 'price'
							AND ( pm_price.meta_value >= {$min_price} {$include_empty_sql} )
						) ";

					if ( ! $price_type_get ) { $query .= " OR ( "; }

						if ( ! $price_type_get || $price_type_get == 'package' )
							$query .= "
								( pm_package.meta_key = 'job_packages' AND pm_package.meta_value = 'yes' )
								AND (
									( pm_price.meta_key = 'package_price_1' AND pm_price.meta_value >= {$min_price} )
									OR ( pm_price.meta_key = 'package_price_2' AND pm_price.meta_value >= {$min_price} )
									OR ( pm_price.meta_key = 'package_price_3' AND pm_price.meta_value >= {$min_price} )
								)
							";

					if ( ! $price_type_get ) { $query .= " ) "; }

				$query .= " ) ";

			} elseif ( is_numeric( $min_price ) && is_numeric( $max_price ) ) {

				$query .= " AND ( ";

					if ( $price_type_get != 'package' )
						$query .= " (
							pm_package.meta_key = 'job_packages' AND pm_package.meta_value <> 'yes' AND pm_price.meta_key = 'price'
							AND ( pm_price.meta_value BETWEEN {$min_price} AND {$max_price} )
						) ";

					if ( ! $price_type_get ) { $query .= " OR ( "; }

						if ( ! $price_type_get || $price_type_get == 'package' )
							$query .= "
								( pm_package.meta_key = 'job_packages' AND pm_package.meta_value = 'yes' )
								AND (
									( pm_price.meta_key = 'package_price_1' AND pm_price.meta_value BETWEEN {$min_price} AND {$max_price} )
									OR ( pm_price.meta_key = 'package_price_2' AND pm_price.meta_value BETWEEN {$min_price} AND {$max_price} )
									OR ( pm_price.meta_key = 'package_price_3' AND pm_price.meta_value BETWEEN {$min_price} AND {$max_price} )
								)
							";

					if ( ! $price_type_get ) { $query .= " ) "; }

				$query .= " ) ";

			}

			if ( $max_days_get )
				$query .= "
					AND (
						pm_max_days.meta_value <= {$max_days_get}
						{$delivery_logical_op} pm_max_days.meta_value {$delivery_comparator} 0
						{$delivery_logical_op} pm_max_days.meta_value {$delivery_comparator} ''
					)
				";

			if ( $order == 'price_lowest' || $order == 'price_highest' ) {

				$query .= " AND ( ";

					if ( $price_type_get != 'package' )
						$query .= " ( pm_package.meta_key = 'job_packages' AND pm_package.meta_value <> 'yes' AND pm_price.meta_key = 'price' ) ";

					if ( ! $price_type_get ) { $query .= " OR ( "; }

						if ( ! $price_type_get || $price_type_get == 'package' )
							$query .= " ( pm_package.meta_key = 'job_packages' AND pm_package.meta_value = 'yes' )
								AND ( pm_price.meta_key = 'package_price_1' OR pm_price.meta_key = 'package_price_2' OR pm_price.meta_key = 'package_price_3' ) ";

					if ( ! $price_type_get ) { $query .= " ) "; }

				$query .= " ) ";

			}

		$query .= " GROUP BY p.ID ";

		if ( $location_get && $latitude_get && $longitude_get && ! $uid )
			$query .= " HAVING distance < {$radius} ";

		if ( $order == 'new' ) {
			$query .= " ORDER BY";

			if ( $feature_enabled ) {
				$query .= " CASE WHEN pm_featured.meta_value = 'y' THEN 0 ELSE 1 END,";
			}

			$query .= " p.post_date DESC";

		} elseif ( $order == 'old' ) {
			$query .= " ORDER BY";

			if ( $feature_enabled ) {
				$query .= " CASE WHEN pm_featured.meta_value = 'y' THEN 0 ELSE 1 END,";
			}

			$query .= " p.post_date ASC";

		} elseif ( $order == 'rating' )
			$query .= " ORDER BY ( pm_rating.meta_value + 0 ) DESC";

		elseif ( $order == 'reviews' )
			$query .= " ORDER BY ( pm_reviews.meta_value + 0 ) DESC";

		elseif ( $order == 'views' )
			$query .= " ORDER BY ( pm_views.meta_value + 0 ) DESC";

		elseif ( $order == 'distance' && $latitude_get && $longitude_get )
			$query .= " ORDER BY distance ASC";

		elseif ( $order == 'price_lowest' || $order == 'price_highest' ) {
			if ( $order == 'price_lowest' )
				$query .= " ORDER BY min_price ASC, ( pm_price.meta_value + 0 ) ASC";

			else
				$query .= " ORDER BY min_price DESC, ( pm_price.meta_value + 0 ) DESC";

		} else {
			$query .= " ORDER BY";

			if ( $feature_enabled ) {
				$query .= " CASE WHEN pm_featured.meta_value = 'y' THEN 0 ELSE 1 END,";
			}

			$random_seed = intval(time() / 600);
			$query .= " RAND({$random_seed}) ";

		}

		return $query;
	}
}