<?php

// GET

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

		return wpj_get_query_results( $args );
	}
}

if ( ! function_exists( 'wpj_get_preview_pms_list' ) ) {
	function wpj_get_preview_pms_list( $uid ) {
		global $wpdb;

		return $wpdb->get_results( "
			SELECT id, rd, initiator, other, user, content, datemade, archived
			FROM (
				SELECT id, rd, initiator, other, user, content, MAX(datemade) AS datemade, archived
				FROM (
					SELECT id, rd, user AS other, user AS initiator, user AS user, content, datemade, archived_to_source AS archived
					FROM {$wpdb->prefix}job_pm
					WHERE initiator = {$uid}
					AND show_to_source = '1'

					UNION

					SELECT id, rd, initiator AS other, initiator AS initiator, user AS user, content, datemade, archived_to_destination AS archived
					FROM {$wpdb->prefix}job_pm
					WHERE user = {$uid}
					AND show_to_destination = '1'

					ORDER BY datemade DESC
				) tmp
				GROUP BY other
				ORDER BY datemade DESC
			) tmp2
			WHERE archived = '0'
			LIMIT 10
		" );
	}
}

if ( ! function_exists( 'wpj_get_messages_by_keyword' ) ) {
	function wpj_get_messages_by_keyword( $keyword, $interlocutor_id ) {
		global $wpdb;

		return $wpdb->get_results( $wpdb->prepare(
			"
			SELECT *
			FROM (
				SELECT * FROM {$wpdb->prefix}job_pm
				WHERE (
					user = %d AND initiator = %d AND show_to_source = 1
				)
				OR (
					initiator = %d AND user = %d AND show_to_destination = 1
				)
				ORDER BY datemade DESC
			) AS t1
			WHERE content LIKE %s
			ORDER BY datemade ASC
			",
			get_current_user_id(), $interlocutor_id, get_current_user_id(), $interlocutor_id, '%' . $wpdb->esc_like( $keyword ) . '%'
		) );
	}
}

if ( ! function_exists( 'wpj_get_tangential_users_by_user_id' ) ) {
	function wpj_get_tangential_users_by_user_id( $user_id ) {
		global $wpdb;

		return $wpdb->get_results( "
			SELECT orders.uid as user FROM {$wpdb->prefix}job_orders orders INNER JOIN {$wpdb->prefix}posts posts ON posts.iD = orders.pid WHERE posts.post_author = {$user_id} GROUP BY orders.uid

			UNION ALL

			SELECT posts.post_author as user FROM {$wpdb->prefix}posts posts INNER JOIN {$wpdb->prefix}job_orders orders ON posts.ID = orders.pid WHERE orders.uid = {$user_id} GROUP BY posts.post_author

			UNION ALL

			SELECT initiator as user FROM {$wpdb->prefix}job_pm WHERE user = {$user_id} GROUP BY initiator

			UNION ALL

			SELECT user as user FROM {$wpdb->prefix}job_pm WHERE initiator = {$user_id} GROUP BY user
		" );
	}
}

if ( ! function_exists( 'wpj_get_private_messages_by_users' ) ) {
	function wpj_get_private_messages_by_users( $user1 = '', $user2 = '', $ret_type = 'row', $limit = false, $additional_conditions = '' ) {
		if ( ! $user1 )
			$user1 = get_current_user_id();

		if ( ! $user2 )
			$user2 = wpj_get_pm_interlocutor_from_url( 'id' );

		global $wpdb;

		$limit_sql = $limit && is_numeric( $limit ) ? ' LIMIT ' . $limit : '';

		$query = $wpdb->prepare(
			"
			SELECT *
			FROM (
				SELECT * FROM {$wpdb->prefix}job_pm
				WHERE (
					( user = %d AND initiator = %d AND show_to_source = 1 )
					OR ( initiator = %d AND user = %d AND show_to_destination = 1 )
				) {$additional_conditions}
				ORDER BY datemade DESC
			) AS t1 ORDER BY datemade ASC
			{$limit_sql}",
			$user1, $user2, $user1, $user2
		);

		if ( $ret_type == 'query' )
			$message = $query;

		else
			$message = $wpdb->get_results( $query );

		return $message;
	}
}

/* Get conversations query */
if ( ! function_exists( 'wpj_get_conversations_by_type' ) ) {
	function wpj_get_conversations_by_type( $type = 'all', $ret_type = 'row' ) {
		global $wpdb;

		$type = WPJ_Form::get( 'type', $type );

		$uid = get_current_user_id();

		if ( $type == 'archived' ) {
			$query = $wpdb->prepare(
				"
				SELECT id, other, content, datemade, author, rd, max_custom_offer, min_custom_offer
				FROM (
					SELECT id, other, content, MAX(datemade) AS datemade, author, rd, MAX(custom_offer) AS max_custom_offer, MIN(custom_offer) AS min_custom_offer, archived
					FROM (
						SELECT id, user AS other, initiator AS author, content, datemade, rd, custom_offer, archived_to_source AS archived
						FROM {$wpdb->prefix}job_pm
						WHERE initiator = %d AND show_to_source = '1'

						UNION

						SELECT id, initiator AS other, initiator AS author, content, datemade, rd, custom_offer, archived_to_destination AS archived
						FROM {$wpdb->prefix}job_pm
						WHERE user = %d AND show_to_destination = '1'
						ORDER BY datemade DESC
					) tmp
					GROUP BY other
					ORDER BY datemade DESC) tmp2
				WHERE archived = '1'
				",
				$uid, $uid
			);

		} elseif ( $type == 'archive' ) {
			$query = $wpdb->prepare(
				"
				SELECT * FROM (
					SELECT id, count( id ) as cnt, initiator, MAX(datemade) AS datemade, archived, content
					FROM (
						SELECT id, initiator AS initiator, datemade, content, archived_to_destination AS archived
						FROM {$wpdb->prefix}job_pm
						WHERE user = %d
							AND show_to_destination = '1'
							AND rd = '0'
						ORDER BY datemade DESC
						LIMIT 10
					) tmp
					GROUP BY initiator
					ORDER BY datemade DESC
				) tmp2
				WHERE archived = '0'
				",
				$uid
			);

		} else {
			$query = $wpdb->prepare(
				"
				SELECT id, other, content, datemade, author, rd, max_custom_offer, min_custom_offer
				FROM (
					SELECT id, other, content, MAX(datemade) AS datemade, author, rd, MAX(custom_offer) AS max_custom_offer, MIN(custom_offer) AS min_custom_offer, archived
					FROM (
						SELECT id, user AS other, initiator AS author, content, datemade, rd, custom_offer, archived_to_source AS archived
						FROM {$wpdb->prefix}job_pm
						WHERE initiator = %d AND show_to_source = '1'

						UNION

						SELECT id, initiator AS other, initiator AS author, content, datemade, rd, custom_offer, archived_to_destination AS archived
						FROM {$wpdb->prefix}job_pm
						WHERE user = %d AND show_to_destination = '1'

						ORDER BY datemade DESC
					) tmp
					GROUP BY other
					ORDER BY datemade DESC) tmp2
				WHERE archived = '0'
				",
				$uid, $uid
			);

		}

		if ( $ret_type == 'query' )
			$conversations = $query;

		else
			$conversations = $wpdb->get_results( $query );

		return $conversations;
	}
}

if ( ! function_exists( 'wpj_is_conversation_archived' ) ) {
	function wpj_is_conversation_archived( $user_id = '', $current_user_id = '' ) {
		if ( ! $current_user_id ) {
			$current_user_id = get_current_user_id();
		}

		if ( ! $user_id ) {
			return false;
		}

		global $wpdb;

		$row = $wpdb->get_row( "
			SELECT initiator, user, archived_to_source, archived_to_destination
			FROM {$wpdb->prefix}job_pm
			WHERE ( user = '{$user_id}' AND initiator = '{$current_user_id}' )
				OR ( initiator = '{$user_id}' AND user = '{$current_user_id}' )
			LIMIT 1
		" );

		if ( $row && (
			( $row->initiator == $current_user_id && $row->archived_to_source == 1 ) ||
			( $row->user == $current_user_id && $row->archived_to_destination == 1 )
		) ) {
			return true;
		}

		return false;
	}
}

if ( ! function_exists( 'wpj_users_have_conversations' ) ) {
	function wpj_users_have_conversations( $usr1, $usr2 ) {
		if ( $usr1 && $usr2 ) {
			global $wpdb;

			$row = $wpdb->get_row( "
				SELECT id
				FROM {$wpdb->prefix}job_pm
				WHERE ( user = '{$usr1}' AND initiator = '{$usr2}' )
					OR ( initiator = '{$usr1}' AND user = '{$usr2}' )
				LIMIT 1
			" );

			if ( $row ) {
				return true;
			}
		}

		return false;
	}
}


// INSERT

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

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

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

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

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

		return $wpdb->insert_id;
	}
}


// UPDATE

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

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

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

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

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

		return $is_updated;
	}
}

if ( ! function_exists( 'wpj_update_conversation' ) ) {
	function wpj_update_conversation( $user1, $user2, $action ) {
		global $wpdb;

		if ( $action == 'archive' ) {
			$column1 = 'archived_to_source';
			$column2 = 'archived_to_destination';
			$value   = '1';

		} else if ( $action == 'unarchive' ) {
			$column1 = 'archived_to_source';
			$column2 = 'archived_to_destination';
			$value   = '0';

		} else if ( $action == 'delete' ) {
			$column1 = 'show_to_source';
			$column2 = 'show_to_destination';
			$value   = '0';

		}

		if ( ! empty( $column1 ) && ! empty( $column2 ) ) {
			$wpdb->query( "
				UPDATE {$wpdb->prefix}job_pm
				SET
					" . $column1 . " = CASE WHEN initiator = '{$user1}' THEN '{$value}' ELSE " . $column1 . " END,
					" . $column2 . " = CASE WHEN user = '{$user1}' THEN '{$value}' ELSE " . $column2 . " END
				WHERE ( initiator = '{$user1}' AND user = '{$user2}' )
				OR ( initiator = '{$user2}' AND user = '{$user1}' )
			" );
		}
	}
}


// DELETE

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

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

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

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

		return $is_deleted;
	}
}