<?php

// GET

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

		return wpj_get_query_results( $args );
	}
}

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

		return wpj_get_query_results( $args );
	}
}

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

		return $wpdb->get_results(
			$wpdb->prepare( "
				SELECT *
				FROM {$wpdb->prefix}users u, {$wpdb->prefix}usermeta um
				WHERE u.ID = um.user_id
					AND um.meta_key = 'last_user_login'
					AND u.ID != %d
				ORDER BY um.meta_value DESC
			", get_current_user_id() )
		);
	}
}

if ( ! function_exists( 'wpj_get_user_avg_response_time' ) ) {
	function wpj_get_user_avg_response_time( $uid = '' ) {
		if ( ! $uid ) $uid = wpj_get_user_id();

		global $wpdb;

		$art_query = "
			SELECT AVG( TIMESTAMPDIFF( SECOND, FROM_UNIXTIME( datemade ), FROM_UNIXTIME( readdate ) ) )
			AS total
			FROM {$wpdb->prefix}job_pm
			WHERE FROM_UNIXTIME( datemade ) BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE()
			AND readdate != 0
			AND user = '{$uid}'
		";

		$results = $wpdb->get_results( $art_query );

		return ! empty( $results ) && isset( $results[0]->total ) && $results[0]->total != 0 ? wpj_seconds_to_words( $results[0]->total ) : __( 'Not enough messages', 'wpjobster' );
	}
}

if ( ! function_exists( 'wpj_get_user_recent_delivery' ) ) {
	function wpj_get_user_recent_delivery( $uid = '' ) {
		if ( ! $uid ) $uid = wpj_get_user_id();

		global $wpdb;

		$rd_query = "
			SELECT MAX( date_finished ) AS recent_delivery
			FROM {$wpdb->prefix}job_orders orders,
				 {$wpdb->prefix}posts posts
			WHERE posts.post_author = '{$uid}'
				AND posts.ID = orders.pid
				AND orders.done_seller = '1'
			ORDER BY orders.id DESC
		";

		$results = $wpdb->get_results( $rd_query );

		return ! empty( $results ) && isset( $results[0]->recent_delivery ) && $results[0]->recent_delivery != 0 ? wpj_seconds_to_words( time() - $results[0]->recent_delivery, false, true ) : __( 'No delivery', 'wpjobster' );
	}
}

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

		$query = "
			SELECT *
			FROM {$wpdb->prefix}job_orders
			WHERE done_seller = '0'
				AND done_buyer = '0'
				AND date_finished = '0'
				AND closed = '0'
				AND payment_status != 'pending'

			UNION ALL

			SELECT *
			FROM {$wpdb->prefix}job_orders
			WHERE completed = '1'
				OR done_seller = '1'
		";

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

if ( ! function_exists( 'wpj_get_portfolio_images' ) ) {
	function wpj_get_portfolio_images( $uid, $limit = '' ) {
		global $wpdb;

		$query = $wpdb->prepare(
			"
			SELECT p.*
			FROM {$wpdb->prefix}posts AS p
			INNER JOIN {$wpdb->prefix}postmeta ON (p.id = {$wpdb->prefix}postmeta.post_id)
			INNER JOIN {$wpdb->prefix}postmeta AS mt2 ON (p.id = mt2.post_id)
			WHERE p.post_author = %d
				AND (p.post_mime_type LIKE %s OR p.post_mime_type LIKE %s)
				AND (
					({$wpdb->prefix}postmeta.meta_key = 'images_order')
					OR
					NOT EXISTS (
						SELECT 1 FROM {$wpdb->prefix}postmeta mt1
						WHERE p.id = mt1.post_id
						AND mt1.meta_key = 'images_order'
					)
				)
				AND mt2.meta_key = 'is_portfolio'
				AND mt2.meta_value = '1'
				AND p.post_type = 'attachment'
				AND p.post_status = 'inherit'
			GROUP BY p.id
			ORDER BY
				CASE
					WHEN {$wpdb->prefix}postmeta.meta_key IS NOT NULL THEN CAST({$wpdb->prefix}postmeta.meta_value AS SIGNED)
					ELSE 0
				END,
				p.post_date DESC
			",
			$uid,
			'image/%',
			'video/%',
		);

		if ( $limit ) $query .= " LIMIT " . $limit;

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


// DELETE

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

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

		$wpdb->delete( $wpdb->prefix . 'job_affiliate_transactions', ['user_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_affiliate_transactions', ['referral_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_badge_orders', ['user_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_credits_balance_log', ['uid' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_discounts', ['user_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_discount_codes', ['seller_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_featured_orders', ['user_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_pm', ['user' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_pm', ['initiator' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_subscriptions', ['user_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_subscription_orders', ['user_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_topup_orders', ['user_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_transactions', ['uid' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_uservacation', ['user_id' => $uid], ['%d'] );
		$wpdb->delete( $wpdb->prefix . 'job_withdraw', ['uid' => $uid], ['%d'] );

		return true;
	}
}
