/**********************************/
QUERY - TO DISPLAY THUMBNAIL IMAGE
/**********************************/
SELECT tournaments.tournament_id,
          tournaments.match_id,
          tournaments.venue_id,
          tournaments.playing_country_id_first,
          C1.country_name AS country_played_first,
          tournaments.playing_country_id_sec,
          C2.country_name AS country_played_sec,
          tournaments.match_date,
          tournaments.team_first_score,
          tournaments.team_sec_score,
          tournaments.team_sec_req_target,
          tournaments.final_result,
          IF ( LENGTH(venues.venue_city) > 0,  CONCAT(venues.venue_name, ", ", venues.venue_city), venues.venue_name) AS venue_name,
          IF(user_library._id > 0, 1, 0) AS is_library_image FROM
          tournaments
          INNER JOIN countries AS C1 ON tournaments.playing_country_id_first = C1.country_id
          INNER JOIN countries AS C2 ON tournaments.playing_country_id_sec = C2.country_id
          INNER JOIN venues          ON tournaments.venue_id = venues.venue_id
          LEFT OUTER JOIN user_library ON (tournaments.tournament_id = user_library.tournament_id
          AND tournaments.match_id = user_library.match_id and user_library._id = 48)
          WHERE
          tournament_type = 5
          AND tournament_max_over = 20
          AND playing_country_id_first = 37
          AND playing_country_id_sec = 30 LIMIT 4
 OFFSET 0

/**********************************/
QUERY - TO DISPLAY ONLY LIBRARY IMAGE
/**********************************/
This query will give all fields including "is_library_image" field to change the color of the image.
NOTE - user_library._id = 55 used as hard coded, in actual use the id of the user who is logged in and requesting to see the dream team.

SELECT tournaments.tournament_id, tournaments.match_id, tournaments.venue_id,
tournaments.playing_country_id_first, C1.country_name AS country_played_first,
tournaments.playing_country_id_sec, C2.country_name AS country_played_sec,
tournaments.match_date, tournaments.team_first_score, tournaments.team_sec_score,
tournaments.team_sec_req_target, tournaments.final_result,
IF ( LENGTH(venues.venue_city) > 0,  CONCAT(venues.venue_name, ", ", venues.venue_city), venues.venue_name) AS venue_name,
IF(user_library._id > 0, 1, 0) AS is_library_image
FROM tournaments
INNER JOIN countries AS C1
ON
tournaments.playing_country_id_first = C1.country_id
INNER JOIN countries AS C2
ON
tournaments.playing_country_id_sec = C2.country_id
INNER JOIN venues
ON
tournaments.venue_id = venues.venue_id
INNER JOIN user_library
ON tournaments.tournament_id = user_library.tournament_id AND tournaments.match_id = user_library.match_id
AND user_library._id = 55

/***************************************/
QUERY TO UPDATE THE user wallet
/***************************************/
UPDATE users SET wallet_value = wallet_value - '" + viewAmount + "' WHERE _id = '" + user_id + "'"


/***************************************/
QUERY TO GET THE RESPONSE FROM SERVER
/***************************************/
1) Get the image path
SELECT dream_team_image_path FROM tournaments WHERE tournament_id = '" + tournament_id + "' AND  match_id = '" + match_id + "'"

2) Check the table user_library. 
Is there any record for a given tournament_id and match_id for a given user? Use the below query for it.
SELECT COUNT(*) FROM user_library WHERE tournament_id = '" + tournament_id + "' AND  match_id = '" + match_id + "'"
AND _id = 55;
If there is a record in the user_library, Send the corresponding image to the user from the path as captured from the above mentioned query without no charge.

If  there is no record in user_library, Check the existence of image. If it exists
Check the wallet amount. Is it less than to the viewAmount ?
Send message response that "No sufficient amount in your wallet".
else 
Add item in user_library. Deduct the viewAmount from wallet_value. Send the corresponding image to the user from the path as captured from the above mentioned query.

/*****************************************************/
QUERY TO CHECK THE SUFFICIENT AMOUNT IN USER'S WALLET
/*****************************************************/

SELECT IF ( wallet_value >= 1, 1, 0) AS is_sufficient_amount  FROM users WHERE _id = 49


/*****************************************************/
QUERY TO ADD ENTRY IN user_library
/*****************************************************/

function addImageIntoUserLibrary(user_id, order_id, callback) {
console.log("addImageIntoUserLibrary is called....................");
	pool.getConnection(function (err, myConnection) {
		if (!err) {			
			let mysql_query = mysql.format("INSERT INTO user_library(_id, tournament_id, match_id) VALUES('" + user_id + "', '" + tournament_id + ", , '" + match_id + "')");
			myConnection.query(mysql_query, function (err) {
				myConnection.release(); // return the connection to pool
				if (!err) {
					callback(err);
				}
				else {
					debug.log('DB query failed: ', mysql_query);
					callback(err);
				}
			});
		}
		else {
			debug.log('pool.getConnection failed.');
			callback(err);
		}
	});
}


/*****************************************************/
QUERY TO GET TOTAL USERS AND ONLINE USERS
/*****************************************************/
SELECT COUNT(*) total_users, (SELECT COUNT(*) FROM users WHERE is_loggedin='y') AS online_users
FROM users;