<?php

// GET

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

		return wpj_get_query_results( $args );
	}
}

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

		return $wpdb->get_results( $wpdb->prepare(
			"
			SELECT DISTINCT *
			FROM {$wpdb->prefix}job_chatbox
			WHERE oid = %d
			GROUP BY datemade, uid, content
			ORDER BY id ASC
			",
			$orderid
		) );
	}
}

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

		return $wpdb->get_row( $wpdb->prepare(
			"
			SELECT
				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->prefix}posts posts
			WHERE chatbox.oid = orders.id
				AND posts.ID = orders.pid
				AND chatbox.id = %d
			",
			$notification_id
		) );
	}
}


// INSERT

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

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

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

        $format[] = '%d';

        // Begin transaction
        $wpdb->query( 'START TRANSACTION' );

        $row_exist = $wpdb->get_var(
            $wpdb->prepare(
                "
                SELECT id
                FROM {$wpdb->prefix}job_chatbox
                WHERE datemade = %d
                    AND uid = %d
                    AND oid = %d
                    AND content = %s
                ",
                $data['datemade'],
                $data['uid'],
                $data['oid'],
                $data['content']
            )
        );

        if ( ! empty( $row_exist ) ) {
            // Rollback the transaction
            $wpdb->query( 'ROLLBACK' );

            return 0;
        }

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

        if ( $is_inserted === false ) {
            // Rollback the transaction
            $wpdb->query( 'ROLLBACK' );
            
            return 0;
        }

        // Commit the transaction
        $wpdb->query( 'COMMIT' );

        return $wpdb->insert_id;
    }
}


// UPDATE

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

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

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

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

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

		return $is_updated;
	}
}

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

		$wpdb->query(
			"
			UPDATE {$wpdb->prefix}job_chatbox
			SET rd_receiver = '1'
			WHERE id IN ({$ids})
			"
		);
	}
}

if ( ! function_exists( 'wpj_update_order_notifications_by_order_id' ) ) {
	function wpj_update_order_notifications_by_order_id( $oid, $uid, $ids ) {
		global $wpdb;

		$wpdb->query(
			$wpdb->prepare( "
				UPDATE {$wpdb->prefix}job_chatbox
				SET rd_receiver = '1'
				WHERE oid = %d
				AND uid != %d
				AND ( uid > 0 OR uid IN ({$ids}))",
				$oid, $uid
			)
		);
	}
}


// DELETE

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

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

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

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

		return $is_deleted;
	}
}