<?php //SELECT mess_id, mess_client_id, mess_message, mess_date FROM `messages` LEFT join myclients ON mess_client_id = client_id  WHERE ag = 1 and (mess_date between '2022-10-10 00:00:01' AND '2022-10-20 23:59:59') and mess_client_id not in (SELECT mess_client_id FROM messages LEFT JOIN myclients ON mess_client_id = client_id WHERE ag = 1 and DATE(mess_date) = CURDATE() group by mess_client_id) group by mess_client_id; 
require_once("../../db.php"); ?>
<?php try {
		$addquery_premium = "";
		if (isset($_POST["blk_premium"]) && $_POST["blk_premium"] == "1") {
			$addquery_premium = " AND premium = '1' ";
		}
		
		$core = Core::getInstance();
		// first get all records!!!! mess_whosent 1 agent, 0 user
		// option value "svima koji su poslali poruku u odabranom vremenskom intervalu", a nisu pisali danas i koji nisu blokirani niti pauzirani
		if ($_POST["blk_sel_option"] == "0") {
			//$stmt = $core -> dbh -> prepare("SELECT mess_id, mess_client_id, mess_message, mess_date, client_number FROM messages LEFT join myclients ON mess_client_id = client_id  WHERE ag = :ag_1 and (mess_date between :date_from AND :date_to) and mess_whosent = '0' and status ='0' ".$addquery_premium." and mess_client_id not in (SELECT mess_client_id FROM messages LEFT JOIN myclients ON mess_client_id = client_id WHERE ag = :ag_2 and status = '0' and DATE(mess_date) = CURDATE() and mess_whosent ='0' group by mess_client_id) group by mess_client_id");
			$stmt = $core -> dbh -> prepare("SELECT mess_id, mess_client_id, mess_message, mess_date, client_number FROM messages LEFT join myclients ON mess_client_id = client_id  WHERE ag = :ag_1 and (mess_date between :date_from AND :date_to) and mess_whosent = '0' and status ='0' ".$addquery_premium." group by mess_client_id");
			// option value "svima registriranima u određenom intervalu"
			$stmt -> bindParam("ag_1", $_GET["ag"], PDO::PARAM_INT);
			$stmt -> bindParam("date_from", $_POST["blk_date_from"], PDO::PARAM_STR);
			$stmt -> bindParam("date_to", $_POST["blk_date_to"], PDO::PARAM_STR);
		} else if ($_POST["blk_sel_option"] == "1") {
			$stmt = $core -> dbh -> prepare("SELECT mess_id, mess_client_id, mess_message, mess_date, client_number FROM messages LEFT join myclients ON mess_client_id = client_id  WHERE ag = :ag_1 and (date_in between :date_from AND :date_to) and status ='0' ".$addquery_premium." and mess_client_id not in (SELECT mess_client_id FROM messages LEFT JOIN myclients ON mess_client_id = client_id WHERE ag = :ag_2 and status = '0' and DATE(mess_date) = CURDATE() and mess_whosent ='0' group by mess_client_id) group by mess_client_id");
			$stmt -> bindParam("ag_1", $_GET["ag"], PDO::PARAM_INT);
			$stmt -> bindParam("date_from", $_POST["blk_date_from"], PDO::PARAM_STR);
			$stmt -> bindParam("date_to", $_POST["blk_date_to"], PDO::PARAM_STR);
			$stmt -> bindParam("ag_2", $_GET["ag"], PDO::PARAM_INT);
		} else if ($_POST["blk_sel_option"] == "2") {
			// "svim novim korisnicima u zadnjih 30 dana
			$stmt = $core -> dbh -> prepare("SELECT client_id as mess_client_id, client_number FROM myclients WHERE date_in BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() and ag = :ag ".$addquery_premium."");
			$stmt -> bindParam("ag", $_GET["ag"], PDO::PARAM_INT);
		} else if ($_POST["blk_sel_option"] == "4") {
			// svim blokiranim
			$stmt = $core -> dbh -> prepare("SELECT client_id as mess_client_id, client_number FROM myclients WHERE status = '2' and ag = :ag ".$addquery_premium."");
			$stmt -> bindParam("ag", $_GET["ag"], PDO::PARAM_INT);
		} else if ($_POST["blk_sel_option"] == "5") {
			// svima koji su pisali danas
			$stmt = $core -> dbh -> prepare("SELECT * FROM `messages` LEFT JOIN myclients ON mess_client_id = client_id WHERE mess_whosent = '0' and date(mess_date) = CURDATE() and ag = :ag ".$addquery_premium."");
			$stmt -> bindParam("ag", $_GET["ag"], PDO::PARAM_INT);
		}
		
		if ($_POST["blk_sel_option"] == 3) {
			// svim neaktivnim
			$stmt = $core->dbh->prepare("SELECT mess_client_id, mess_date, client_number 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 ".$addquery_premium."");
			$stmt -> bindParam("ag", $_GET["ag"], PDO::PARAM_INT);
			$stmt -> execute();
			$results = $stmt -> fetchAll(PDO::FETCH_ASSOC);
			$sum = $stmt -> rowCount();
			
			$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 as mess_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++;
			}
			
			// set up results as $output_final
			$results = array();
			$results = $output_final;
		} else {
			$stmt -> execute();
			$results = $stmt -> fetchAll(PDO::FETCH_ASSOC);
			$sum = $stmt -> rowCount();	
		}
		
		
		if ($sum == 0) {
			echo '{"send":"false","reason":"Ne postoje zapisi kojima se šalje"}';
		} else {
			
			foreach($results as $key => $value) {
				try {
					$core_s = Core::getInstance();
					$stmt_s = $core_s->dbh->prepare("INSERT INTO messages (mess_client_id, mess_message, mess_whosent, mess_blk) values (:mess_client_id, :mess_message, :mess_whosent, :mess_blk)");
					$mess_whosent = "1";
					$mess_blk = "1";
					$stmt_s -> bindParam("mess_client_id", $value["mess_client_id"], PDO::PARAM_INT);
					$stmt_s -> bindParam("mess_message", $_POST["blk_message"], PDO::PARAM_STR);
					$stmt_s -> bindParam("mess_whosent", $mess_whosent, PDO::PARAM_STR);
					$stmt_s -> bindParam("mess_blk", $mess_blk, PDO::PARAM_STR);
					$stmt_s -> execute();
					$last_insert_id = $core->dbh->lastInsertId();
					//$client_number = getClientNumber($_POST["inp_reply_clientID"]);
				   
					pushSMS($_GET["ag"], $value["client_number"], $_POST["blk_message"], $last_insert_id);
				} catch(PDOException $pe) {
					trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
				}
			}
			
			echo '{"send":"true","reason":"Ukupno poslano poruka: '.$sum.'"}';
		}
	}  catch(PDOException $pe) {
		trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
	}
?>