<?php

	$servername = "localhost";
	$username = "gamikaze";
	$password = "JUbLVkOi";
	$dbname = "gamikaze";

	$newsarray = array();

	$conn = new mysqli($servername, $username, $password, $dbname);
	if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	}

	if(isset($_REQUEST["console"])){
		$console = $_REQUEST["console"];
		$consoles = explode(",", $console);
		$add_consoles = 'AND (';
		$first = true;
		foreach ($consoles as $c) {
			if (!$first) {
				$add_consoles .= " OR ";
			}
			$add_consoles .= " n.logo_console LIKE '%" . $c . "%' ";
			$first = false;
		}
		$add_consoles .= ')';
		$count = count($consoles);

		$i = 0;

		if(isset($_REQUEST["date"])){
			$date = $_REQUEST["date"];
			$newsarray = array();

			$query = "SELECT n.idnews, n.titre, n.sous_titre, n.date, DATE_FORMAT(n.date,'%Hh%i') AS heure, n.logo_console, n.message, n.idtopic_forum,
					  m.idmembres, m.pseudo, m.email, m.biographie, e.nom as nom_auteur, e.prenom
					  FROM news n
					  LEFT JOIN membres m
					  ON m.idmembres = n.idmembres
				      LEFT JOIN equipe e
					  ON e.idmembres = n.idmembres
					  LEFT JOIN news_has_jeux_videos nh on nh.idnews = n.idnews
					  JOIN jeux_videos jv on jv.idjeux_videos = nh.idjeux_videos
					  JOIN console c on c.idconsole = jv.console
					  WHERE n.date < now() AND DATE(n.date) = STR_TO_DATE('" . $date . "','%Y-%m-%d')
					  ".$add_consoles."
					  ORDER BY n.date DESC";

			$query4 = "SELECT nom_type
					  FROM type_jv
					  WHERE idtype_jv = (SELECT idtype_jv
					  FROM lien_jv_type
					  WHERE idjeux_videos = (SELECT idjeux_videos
					  FROM news_has_jeux_videos
					  WHERE idnews = ";

			$result = $conn->query($query);
			$num_rows = $result->num_rows;

			$result4 = $conn->query($query4 . $idnews . "))");

			if($result4 && $result4->num_rows > 0){

			$row4 = $result4->fetch_assoc();

			$type = $row4["nom_type"];

			}

			if($num_rows > 0) {
				while($row = $result->fetch_assoc()){
					$message = strpbrk(htmlspecialchars($row["message"]), '[');
					$j = 0;
					$imgarray = array();
					$dev = "";
					$saga = "";
					$type = "";

					while($message != false){
						$balise = substr($message, 1, 3);
						$message = substr($message, 4);

						switch($balise){
							case "img" :
								$str = substr($message, 1);
								$pos = strpos($str, '[');
								$img = substr($str, 0, $pos);
								$imgarray[$j] = $img;
								$j++;
								break;
							case "dev" :
								$str = strpbrk($message, ']');
								$str = substr($str, 1);
								$pos = strpos($str, '[');
								$dev = substr($str, 0, $pos);
								break;
							case "saga" :
								$str = strpbrk($message, ']');
								$str = substr($str, 1);
								$pos = strpos($str, '[');
								$saga = substr($str, 0, $pos);
								break;
						}

						$message = strpbrk($message, '[');
					}

					$newsarray[$i] = array(
						"idnews" => $row["idnews"],
						"titre" => $row["titre"],
						"sous_titre" => $row["sous_titre"],
						"date" => $row["date"],
						"heure" => $row["heure"],
						"logo_console" => $row["logo_console"],
						"message" => $row["message"],
						"images" => $imgarray,
						"dev" => $dev,
						"type" => $type,
						"email" => $row["email"],
						"biographie" => $row["biographie"],
						"nom_auteur" => $row["nom_auteur"],
						"prenom" => $row["prenom"],
						"saga" => $saga
					);
					$i++;
				}
			}
			else {
				echo "no results";
				exit(0);
			}
			$conn->close();

			$response = json_encode($newsarray);
			echo $response;
		}
		else{
			$query = "SELECT n.idnews, n.titre, n.sous_titre, n.date, DATE_FORMAT(n.date, '%Hh%i') AS heure, n.logo_console, n.message
					  FROM news
					  ORDER BY date DESC";

			if($count > 0){
			$query = "SELECT n.idnews, n.titre, n.sous_titre, n.date, DATE_FORMAT(n.date,'%Hh%i') AS heure, n.logo_console, n.message, n.idtopic_forum,
					  m.idmembres, m.pseudo, m.email, m.biographie, e.nom as nom_auteur, e.prenom
					  FROM news n
					  LEFT JOIN membres m
					  ON m.idmembres = n.idmembres
				      LEFT JOIN equipe e
					  ON e.idmembres = n.idmembres
					  LEFT JOIN news_has_jeux_videos nh on nh.idnews = n.idnews
					  JOIN jeux_videos jv on jv.idjeux_videos = nh.idjeux_videos
					  JOIN console c on c.idconsole = jv.console
					  WHERE n.date < now()
					  ".$add_consoles."
					  ORDER BY n.date DESC";
			}


			$result = $conn->query($query);
			$num_rows = $result->num_rows;
			$result4 = $conn->query($query4 . $idnews . "))");

			if($result4 && $result4->num_rows > 0){

			$row4 = $result4->fetch_assoc();

			$type = $row4["nom_type"];

			}

			if($num_rows > 0) {
				while($row = $result->fetch_assoc()){
					$message = strpbrk(htmlspecialchars($row["message"]), '[');
					$j = 0;
					$imgarray = array();
					$dev = "";
					$saga = "";
					$type = "";

					while($message != false){
						$balise = substr($message, 1, 3);
						$message = substr($message, 4);

						switch($balise){
							case "img" :
								$str = substr($message, 1);
								$pos = strpos($str, '[');
								$img = substr($str, 0, $pos);
								$imgarray[$j] = $img;
								$j++;
								break;
							case "dev" :
								$str = strpbrk($message, ']');
								$str = substr($str, 1);
								$pos = strpos($str, '[');
								$dev = substr($str, 0, $pos);
								break;
							case "saga" :
								$str = strpbrk($message, ']');
								$str = substr($str, 1);
								$pos = strpos($str, '[');
								$saga = substr($str, 0, $pos);
								break;
						}

						$message = strpbrk($message, '[');
					}

					$newsarray[$i] = array(
						"idnews" => $row["idnews"],
						"titre" => $row["titre"],
						"sous_titre" => $row["sous_titre"],
						"idmembres" => $row["idmembres"],
						"heure" => $row["heure"],
						"date" => $row["date"],
						"logo_console" => $row["logo_console"],
						"message" => htmlspecialchars($row["message"]),
						"idtopic_forum" => $row["idtopic_forum"],
						"imagesarray" => $imgarray,
						"dev" => $dev,
						"saga" => $saga,
						"pseudo" => $row["pseudo"],
						"type" => $type,
						"email" => $row["email"],
						"biographie" => $row["biographie"],
						"nom_auteur" => $row["nom_auteur"],
						"prenom" => $row["prenom"]
					);
					$i++;
				}
			}
			else {
				echo "no results";
				exit(0);
			}
			$conn->close();

			$response = json_encode($newsarray);
			echo $response;
		}
	}
	else{
		echo "no console";
	}
?>