<?php require_once("../../db.php"); ?>
<?php try {
		
		$core = Core::getInstance();
		//$stmt = $core->dbh->prepare("SELECT mess_client_id, client_id, client_number, mess_whosent  FROM messages LEFT JOIN myclients on mess_client_id = client_id WHERE ag = :ag and mess_id in (SELECT MAX(mess_id) FROM messages GROUP BY mess_client_id) and (mess_date between NOW() - INTERVAL 45 MINUTE AND NOW() - INTERVAL 15 MINUTE) group by mess_client_id order by mess_date DESC");
		$stmt = $core->dbh->prepare("SELECT mess_client_id, mess_date FROM messages m INNER JOIN (SELECT mess_client_id, MAX(mess_date) as mess_date FROM messages WHERE mess_whosent = '0' GROUP BY mess_client_id) as mymax USING(mess_client_id, mess_date) left join myclients on myclients.client_id = m.mess_client_id WHERE ag = :ag");
		$stmt -> bindParam("ag", $_GET["ag"], PDO::PARAM_INT);
		
		// SELECT n.*, mymax.* FROM myclients n left join (SELECT m.* FROM messages m INNER JOIN (SELECT mess_client_id, MAX(mess_date) as mess_date FROM messages WHERE mess_whosent = '0' GROUP BY mess_client_id) as mymax USING(mess_client_id, mess_date)) as myout on myout.mess_client_id = n.client_id
		
		
		
		// SELECT n.* FROM messages n full OUTER JOIN (SELECT m.* FROM messages m INNER join (SELECT mess_client_id, MAX(mess_date) as mess_date FROM messages WHERE mess_whosent = '0' GROUP BY mess_client_id) as max USING(mess_client_id, mess_date)) AS my USING (mess_client_id) WHERE my.mess_whosent = '1'
		
		$stmt -> execute();
		$results = $stmt -> fetchAll(PDO::FETCH_ASSOC);
		
		$output_final = array();
		$i = 0;
		foreach ($results as $key => $value) {
			
			
			
			// now we do query loop unfortunately
			$mystmt = $core->dbh->prepare("SELECT client_number, mess_date, client_id FROM messages LEFT JOIN myclients on client_id = mess_client_id WHERE mess_client_id = :mess_client_id and mess_date > :mess_date AND mess_whosent = '1' ORDER BY mess_date ASC LIMIT 1");
			$mystmt -> bindParam("mess_client_id", $value["mess_client_id"], PDO::PARAM_INT);
			$mystmt -> bindParam("mess_date", $value["mess_date"], PDO::PARAM_STR);
			$mystmt -> execute();
			$sum = $mystmt -> rowCount();
			$myresults = $mystmt -> fetchAll(PDO::FETCH_ASSOC);
			
			
			if ($sum > 0) {
				$starttime = strtotime("-45 minutes", time());
				$endtime = strtotime("-15 minutes", time());
				
				$date_from_db = strtotime($myresults[0]["mess_date"]);
				if ($date_from_db >= $starttime && $date_from_db <= $endtime) {
					$output_final[] = $myresults[0];
				}
			}
			
			$i++;
		}
		
		
		echo json_encode($output_final);
		
	} catch(PDOException $pe) {
		
		trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
		
	}
	
	// SELECT * FROM messages WHERE mess_client_id in (SELECT m.mess_client_id FROM messages m INNER JOIN (SELECT mess_client_id, MAX(mess_date) as mess_date FROM messages WHERE mess_whosent = '0' GROUP BY mess_client_id) as max USING(mess_client_id, mess_date)) and mess_whosent = '1'
?>