<?php

// GET

if ( ! function_exists( 'wpj_get_preview_notifications_list' ) ) {
	function wpj_get_preview_notifications_list( $buyer_uid, $seller_uid ) {
		global $wpdb;

		$uid = get_current_user_id();

		return $wpdb->get_results( "
			SELECT chatbox.id id, chatbox.rd_receiver, orders.id oid, posts.ID pid, posts.post_author sid, orders.uid bid, chatbox.uid code, chatbox.datemade tm, orders.date_made otm
			FROM {$wpdb->prefix}job_chatbox chatbox, {$wpdb->prefix}job_orders orders, $wpdb->posts posts
			WHERE chatbox.oid = orders.id
				AND posts.ID = orders.pid
				AND chatbox.uid != '{$uid}'
				AND ( posts.post_author = '{$uid}' OR orders.uid = '{$uid}' )
				AND ( chatbox.uid > 0
					OR ( posts.post_author = '{$uid}' AND (chatbox.uid IN ( {$seller_uid} ) ) )
					OR ( orders.uid = '{$uid}' AND ( chatbox.uid IN ( {$buyer_uid} ) ) )
			)
			GROUP BY chatbox.datemade
			ORDER BY chatbox.rd_receiver ASC, tm DESC
			LIMIT 10
		" );
	}
}

if ( ! function_exists( 'wpj_get_notifications' ) ) {
	function wpj_get_notifications( $args = null, $ret_type = '' ) {
		global $wpdb;

		$defaults = [
			'limit'   => 10,
			'offset'  => 0,
			'orderby' => 'tm',
			'order'   => 'DESC',
			'uid'     => '',
			'status'  => '',
		];

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

		$limit   = esc_sql( $args['limit'] );
		$offset  = esc_sql( $args['offset'] );
		$orderby = esc_sql( $args['orderby'] );
		$order   = esc_sql( $args['order'] );
		$uid     = esc_sql( $args['uid'] );
		$status  = $args['status'];

		if ( $status == 'unread' ) $status_sql = 'AND chatbox.rd_receiver = "0"';
		elseif ( $status == 'read' ) $status_sql = 'AND chatbox.rd_receiver = "1"';
		else $status_sql = '';

		if ( ! $uid ) $uid = get_current_user_id();

		$buyer_uid  = implode( ', ', wpj_get_chatbox_uid_by_user_type( 'buyer' ) );
		$seller_uid = implode( ', ', wpj_get_chatbox_uid_by_user_type( 'seller' ) );

		$query = "
			SELECT DISTINCT chatbox.id id, orders.id oid, posts.ID pid, posts.post_author sid, orders.uid bid, chatbox.uid code, chatbox.datemade tm, chatbox.rd_receiver rd, orders.date_made otm
			FROM {$wpdb->prefix}job_chatbox chatbox, {$wpdb->prefix}job_orders orders, $wpdb->posts posts
			WHERE chatbox.oid = orders.id AND posts.ID = orders.pid
			AND chatbox.uid != '{$uid}' {$status_sql}
			AND ( posts.post_author = '{$uid}' OR orders.uid = '{$uid}' )
			AND ( chatbox.uid > 0
				OR ( posts.post_author = '{$uid}' AND (chatbox.uid IN ( {$seller_uid} ) ) )
				OR ( orders.uid = '{$uid}' AND (chatbox.uid IN ( {$buyer_uid} ) ) )
			)
			GROUP BY chatbox.datemade
			ORDER BY {$orderby} {$order}
		";

		if ( is_numeric( $limit ) && $limit > 0 ) $query .= ' LIMIT ' . $limit;
		if ( is_numeric( $offset ) && $offset > 0 ) $query .= ' OFFSET ' . $offset;

		if ( $ret_type == 'query' ) $notifications = $query;
		else $notifications = $wpdb->get_results( $query );

		return $notifications;
	}
}