<?php

// GET

if ( ! function_exists( 'wpj_get_job_rating' ) ) {
	function wpj_get_job_rating( $fields = '*', $where = '', $return = 'row' ) {
		$args = [
			'table'  => 'job_ratings',
			'fields' => $fields,
			'where'  => $where,
			'return' => $return
		];

		return wpj_get_query_results( $args );
	}
}

if ( ! function_exists( 'wpj_get_job_seller_rating' ) ) {
	function wpj_get_job_seller_rating( $fields = '*', $where = '', $return = 'row' ) {
		$args = [
			'table'  => 'job_ratings_by_seller',
			'fields' => $fields,
			'where'  => $where,
			'return' => $return
		];

		return wpj_get_query_results( $args );
	}
}

if ( ! function_exists( 'wpj_get_order_rating' ) ) {
	function wpj_get_order_rating( $fields = '*', $table = [], $where = '', $return = 'row', $limit = '', $orderby = '' ) {
		$where['fields_not_strings']['posts.ID']  = 'orders.pid';
		$where['fields_not_strings']['orders.id'] = 'ratings.orderid';

		$args = [
			'table'  => $table,
			'fields' => $fields,
			'where'  => $where,
			'return' => $return
		];

		if ( $limit ) {
			$args['limit'] = $limit;
		}

		if ( $orderby ) {
			$args['order'] = $orderby;
		}

		return wpj_get_query_results( $args );
	}
}

if ( ! function_exists( 'wpj_get_order_reviews' ) ) {
	function wpj_get_order_reviews( $type = '', $uid = '', $return = 'row' ) {
		global $wpdb;

		$query = false;

		if ( $type == 'to_award' ) {
			$query = $wpdb->prepare(
				"
				SELECT DISTINCT *, ratings.id AS id
				FROM {$wpdb->prefix}job_ratings ratings,
					 {$wpdb->prefix}job_orders orders
				WHERE ratings.awarded = '0'
					AND orders.id = ratings.orderid
					AND orders.uid = %d
					AND orders.closed != 1
					AND orders.completed = 1
					AND orders.done_seller = 1
				ORDER BY ratings.id DESC
				",
				$uid
			);

		} elseif ( $type == 'to_receive' ) {
			$query = $wpdb->prepare(
				"
				SELECT DISTINCT *
				FROM {$wpdb->prefix}job_ratings ratings,
					 {$wpdb->prefix}job_orders orders,
					 {$wpdb->prefix}posts posts
				WHERE posts.ID = orders.pid
					AND ratings.awarded = '0'
					AND orders.id = ratings.orderid
					AND posts.post_author = %d
					AND orders.closed != 1
				ORDER BY ratings.id DESC
				",
				$uid
			);

		} elseif ( $type == 'received' ) {
			$query = $wpdb->prepare(
				"
				SELECT
					ratings.id,
					ratings.orderid,
					ratings.grade,
					ratings.datemade,
					ratings.reason,
					ratings.awarded,
					ratings.uid,
					ratings.pid,
					ratings.sample
				FROM {$wpdb->prefix}job_ratings ratings,
					 {$wpdb->prefix}job_orders orders,
					 {$wpdb->prefix}posts posts
				WHERE posts.ID = orders.pid
					AND ratings.awarded = '1'
					AND orders.id = ratings.orderid
					AND posts.post_author = %d

				UNION

				SELECT
					ratings.id,
					ratings.orderid,
					ratings.grade,
					ratings.datemade,
					ratings.reason,
					ratings.awarded,
					ratings.uid,
					ratings.pid,
					null
				FROM {$wpdb->prefix}job_ratings_by_seller ratings,
					 {$wpdb->prefix}job_orders orders,
					 {$wpdb->prefix}posts posts
				WHERE posts.ID = orders.pid
					AND ratings.awarded = '1'
					AND orders.id = ratings.orderid
					AND ratings.uid = %d
				ORDER BY datemade DESC
				",
				$uid, $uid
			);

		}

		if ( $return == 'query' ) {
			return $query;
		}

		return $wpdb->{'get_' . $return}( $query );
	}
}

if ( ! function_exists( 'wpj_get_custom_offer_rating' ) ) {
	function wpj_get_custom_offer_rating( $args ) {
		global $wpdb;

		$defaults = [
			'pid'              => 0,
			'fields'           => '*',
			'extra_conditions' => [],
			'limit'            => '',
			'table'            => 'job_ratings',
			'return'           => 'results'
		];

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

		$extra_conditions = '';

		if ( ! empty( $args['extra_conditions'] ) ) {
			foreach ( $args['extra_conditions'] as $extra_condition ) {
				list( $column, $relation, $value ) = $extra_condition;

				$extra_conditions .= sprintf( ' AND %s %s %d', $column, $relation, $value );
			}
		}

		$query = $wpdb->prepare(
			"
			SELECT
				{$args['fields']}
			FROM
				{$wpdb->prefix}{$args['table']} ratings,
				{$wpdb->prefix}job_orders orders,
				{$wpdb->prefix}posts posts
			WHERE
				posts.ID = orders.pid
				AND orders.id = ratings.orderid
				AND (
					posts.ID = %d
					OR ratings.pid IN (
						SELECT
							custom_offer
						FROM
							{$wpdb->prefix}job_pm pm,
							{$wpdb->prefix}{$args['table']} jr
						WHERE
							associate_job_id = %d
							AND custom_offer = jr.pid
					)
				)
				AND ratings.awarded = 1
				{$extra_conditions}
				ORDER BY datemade DESC
			",
			$args['pid'],
			$args['pid']
		);

		if ( $args['limit'] ) {
			$query .= ' LIMIT ' . $args['limit'];
		}

		if ( $args['return'] == 'query' ) {
			return $query;
		}

		return $wpdb->{'get_' . $args['return']}( $query );
	}
}

if ( ! function_exists( 'wpj_get_job_review_response' ) ) {
	function wpj_get_job_review_response( $order_id, $pid, $post_author ) {
		return wpj_get_custom_offer_rating( [
			'fields' => 'orders.pid, ratings.datemade, ratings.grade, ratings.reason',
			'table' => 'job_ratings_by_seller',
			'pid' => $pid,
			'extra_conditions' => [
				['posts.post_author', '=', $post_author],
				['orders.id', '=', $order_id]
			],
			'return' => 'row'
		] );
	}
}

if ( ! function_exists( 'wpj_get_job_review_images' ) ) {
	function wpj_get_job_review_images( $pid, $limit = '' ) {
		if ( ! $pid ) {
			$pid = wpj_get_post_id( $pid );
		}

		return wpj_get_custom_offer_rating( [
			'pid' => $pid,
			'fields' => 'ratings.sample as sample_id, ratings.reason, ratings.datemade, orders.uid',
			'extra_conditions' => [
				['posts.post_author', '=', get_post_field( 'post_author', $pid )],
				['ratings.sample', '!=', '']
			],
			'limit'  => $limit,
			'return' => 'results'
		] );
	}
}

if ( ! function_exists( 'wpj_get_awarded_job_ratings_between_by_user' ) ) {
	function wpj_get_awarded_job_ratings_between_by_user( $fields = '*', $uid = '', $date_min = '', $date_max = '', $return_type = '' ) {
		global $wpdb;

		$orders_table  = $wpdb->prefix . 'job_orders';
		$ratings_table = $wpdb->prefix . 'job_ratings';
		$posts_table   = $wpdb->prefix . 'posts';

		$query = $wpdb->prepare(
			"
			SELECT
				DISTINCT $fields
			FROM
				$ratings_table ratings,
				$posts_table posts,
				$orders_table orders
			WHERE
				posts.ID = ratings.pid
				AND orders.id = ratings.orderid
				AND ratings.awarded = 1
				AND posts.post_author = %d
			",
			$uid
		);

		if ( $date_min ) $query .= " AND orders.date_completed > $date_min";
		if ( $date_max ) $query .= " AND orders.date_completed < $date_max";

		$query .= " ORDER BY ratings.id DESC";

		if ( $return_type == 'row' )
			return $wpdb->get_row( $query );

		else
			return $wpdb->get_results( $query );
	}
}

if ( ! function_exists( 'wpj_get_job_rating_percent' ) ) {
	function wpj_get_job_rating_percent( $pid ) {
		return wpj_get_custom_offer_rating( ['fields' => 'ROUND(((SUM(ratings.grade) * 100) / COUNT(*)) / 5)', 'pid' => $pid, 'return' => 'var'] );
	}
}


if ( ! function_exists( 'wpj_get_job_rating_avg' ) ) {
	function wpj_get_job_rating_avg( $pid ) {
		return wpj_get_custom_offer_rating( ['fields' => 'AVG(ratings.grade)', 'pid' => $pid, 'return' => 'var'] );
	}
}

if ( ! function_exists( 'wpj_get_job_reviews_number' ) ) {
	function wpj_get_job_reviews_number( $pid ) {
		return wpj_get_custom_offer_rating( ['fields' => 'COUNT( * )', 'pid' => $pid, 'return' => 'var'] );
	}
}


// INSERT

if ( ! function_exists( 'wpj_insert_job_rating' ) ) {
	function wpj_insert_job_rating( $data, $format ) {
		global $wpdb;

		if ( empty( $data ) === true ) return 0;

		if ( empty( $format ) === true ) return 0;

		$is_inserted = $wpdb->insert( $wpdb->prefix . 'job_ratings', $data, $format );

		if ( $is_inserted === false ) return 0;

		return $wpdb->insert_id;
	}
}

if ( ! function_exists( 'wpj_insert_job_seller_rating' ) ) {
	function wpj_insert_job_seller_rating( $data, $format ) {
		global $wpdb;

		if ( empty( $data ) === true ) return 0;

		if ( empty( $format ) === true ) return 0;

		$data['awarded']  = 1;
		$data['datemade'] = current_time( 'timestamp', 1 );

		$format[] = '%d';
		$format[] = '%d';

		$is_inserted = $wpdb->insert( $wpdb->prefix . 'job_ratings_by_seller', $data, $format );

		if ( $is_inserted === false ) return 0;

		return $wpdb->insert_id;
	}
}


// UPDATE

if ( ! function_exists( 'wpj_update_job_rating' ) ) {
	function wpj_update_job_rating( $data, $where ) {
		global $wpdb;

		if ( empty( $data ) === true ) return 0;

		if ( empty( $where ) === true ) return 0;

		$data['datemade'] = current_time( 'timestamp', 1 );

		$is_updated = $wpdb->update( $wpdb->prefix . 'job_ratings', $data, $where );

		if ( $is_updated === false ) return 0;

		return $is_updated;
	}
}

if ( ! function_exists( 'wpj_update_job_rating_metas' ) ) {
	function wpj_update_job_rating_metas( $pid ) {
		$rating = wpj_get_custom_offer_rating( ['fields' => 'AVG(ratings.grade)', 'pid' => $pid, 'return' => 'var'] );

		update_post_meta( $pid, 'job_rating', $rating );
		update_post_meta( $pid, 'job_reviews', wpj_get_job_reviews_number( $pid ) );

		return $rating;
	}
}


// DELETE
if ( ! function_exists( 'wpj_delete_job_rating' ) ) {
	function wpj_delete_job_rating( $where ) {
		global $wpdb;

		if ( empty( $where ) === true ) return 0;

		$is_deleted = $wpdb->delete( $wpdb->prefix . 'job_ratings', $where );

		if ( $is_deleted === false ) return 0;

		return $is_deleted;
	}
}

if ( ! function_exists( 'wpj_delete_job_seller_rating' ) ) {
	function wpj_delete_job_seller_rating( $where ) {
		global $wpdb;

		if ( empty( $where ) === true ) return 0;

		$is_deleted = $wpdb->delete( $wpdb->prefix . 'job_ratings_by_seller', $where );

		if ( $is_deleted === false ) return 0;

		return $is_deleted;
	}
}