<?php

// GET

if ( ! function_exists( 'wpj_get_job_order' ) ) {
	function wpj_get_job_order( $fields = '*', $where = '', $return = 'row', $between = [], $orderby = '' ) {
		if ( $between ) {
			if ( ! empty( $between['date_min'] ) && empty( $between['date_max'] ) )
				$where['fields_date'] = sprintf( ' AND %s >= %d ', $between['column'], $between['date_min'] );

			else if ( empty( $between['date_min'] ) && ! empty( $between['date_max'] ) )
				$where['fields_date'] = sprintf( ' AND %s <= %d ', $between['column'], $between['date_max'] );

			else if ( $between['date_min'] && $between['date_max'] )
				$where['fields_date'] = sprintf( ' AND %s >= %d AND %s <= %d', $between['column'], $between['date_min'], $between['column'], $between['date_max'] );
		}

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

		return wpj_get_query_results( $args );
	}
}

if ( ! function_exists( 'wpj_get_job_order_post' ) ) {
	function wpj_get_job_order_post( $fields = '*', $where = '', $return = 'row', $between = [], $orderby = '' ) {
		$where['fields_not_strings']['p.ID'] = 'o.pid';

		if ( $between ) {
			if ( ! empty( $between['date_min'] ) && empty( $between['date_max'] ) )
				$where['fields_date'] = sprintf( ' AND %s >= %d ', $between['column'], $between['date_min'] );

			else if ( empty( $between['date_min'] ) && ! empty( $between['date_max'] ) )
				$where['fields_date'] = sprintf( ' AND %s <= %d ', $between['column'], $between['date_max'] );

			else if ( $between['date_min'] && $between['date_max'] )
				$where['fields_date'] = sprintf( ' AND %s >= %d AND %s <= %d', $between['column'], $between['date_min'], $between['column'], $between['date_max'] );
		}

		$args = [
			'table'  => ['posts p', 'job_orders o'],
			'fields' => $fields,
			'where'  => $where,
			'return' => $return
		];

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

		return wpj_get_query_results( $args );
	}
}

if ( ! function_exists( 'wpj_get_job_order_by_id' ) ) {
	function wpj_get_job_order_by_id( $oid = '', $return = '' ) {
		if ( is_object( $oid ) ) {
			return $oid;
		}

		if ( ! $oid ) {
			global $wp_query;

			if ( isset( $wp_query->query_vars['oid'] ) ) {
				$oid = esc_sql( $wp_query->query_vars['oid'] );
			}
		}

		if ( is_numeric( $oid ) && $oid > 0 ) {
			$row = wpj_get_job_order( 'DISTINCT *', ['id' => $oid], 'row' );

			return $return && isset( $row->$return ) ? $row->$return : $row;
		}

		return false;
	}
}

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

		if ( empty( $uid ) === true )
			$uid = get_current_user_id();

		return $wpdb->get_results(
			$wpdb->prepare( "
				SELECT orders.id as order_id
					FROM {$wpdb->prefix}job_orders orders
				INNER JOIN {$wpdb->prefix}posts posts
					ON posts.ID = orders.pid
				WHERE closed = 0
					AND completed = 0
					AND ( orders.uid = %d
						OR posts.post_author = %d )
				GROUP BY orders.ID
			", $uid, $uid )
		);
	}
}

if ( ! function_exists( 'wpj_get_job_orders_total_spent_by_user' ) ) {
	function wpj_get_job_orders_total_spent_by_user( $uid = '', $between = [] ) {
		global $wpdb;

		if ( empty( $uid ) === true ) {
			$uid = get_current_user_id();
		}

		$where = '';

		if ( $between ) {
			$where = sprintf( ' AND %s >= %d AND %s <= %d ', $between['column'], $between['date_min'], $between['column'], $between['date_max'] );
		}

		return $wpdb->get_var(
			$wpdb->prepare( "
				SELECT SUM(sum_amount) AS sum_amount
				FROM
					(
						SELECT SUM(SUBSTRING_INDEX(final_paidamount, '|', -1)) AS sum_amount
						FROM {$wpdb->prefix}job_orders
						WHERE uid = %d
							AND done_seller   =  '0'
							AND done_buyer    =  '0'
							AND date_finished =  '0'
							AND closed        =  '0' AND payment_status != 'pending'
							{$where}

						UNION ALL

						SELECT SUM(SUBSTRING_INDEX(final_paidamount, '|', -1)) AS sum_amount
						FROM {$wpdb->prefix}job_orders
						WHERE uid = %d
							AND completed =  '1'
							{$where}
					)sum_amount
			", $uid, $uid )
		);
	}
}

if ( ! function_exists( 'wpj_get_job_audios' ) ) {
	function wpj_get_job_audios( $pid ) {
		if ( ! $pid ) $pid = wpj_get_post_id( $pid );

		global $wpdb;

		$query = $wpdb->prepare(
			"
			SELECT p.*
			FROM {$wpdb->prefix}posts AS p
			WHERE p.post_parent = %d
				AND p.post_mime_type LIKE %s
				AND p.post_type = 'attachment'
				AND p.post_status = 'inherit'
			ORDER  BY p.post_date ASC;
			",
			$pid,
			'audio/%'
		);

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

if ( ! function_exists( 'wpj_get_job_images' ) ) {
	function wpj_get_job_images( $pid, $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_parent = %d
				AND 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_job'
				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
			",
			$pid,
			'image/%'
		);

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

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



// INSERT

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

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

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

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

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

		return $wpdb->insert_id;
	}
}


// UPDATE

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

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

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

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

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

		return $is_updated;
	}
}