<?php 

function getDB() {return new SQLite3("db/cuisine.db");}

function getAssocFromQueryString($qs){
	$db = getDB();
	$stmt = $db->prepare($qs);
	$result = $stmt ? $stmt->execute() : false;
	$assocArray = [];
	while ($ligne = $result->fetchArray(SQLITE3_ASSOC)){
		$assocArray[] = $ligne;
	}
	return $assocArray;
}

function getAllIngredients(){
	$requeteListeIngredient = "SELECT nom, id FROM Ingredient ORDER BY nom;";
	$resultatListeIngredient = getAssocFromQueryString($requeteListeIngredient);
	$listeIngredient = [];
	foreach ($resultatListeIngredient as $infoIngredient){
		$listeIngredient[$infoIngredient['id']] = $infoIngredient;
	}
	return $listeIngredient;
}

function getAllUstensiles(){
	$requete = "SELECT nom, id FROM Ustensile ORDER BY nom;";
	$resultatListeUstensile = getAssocFromQueryString($requete);
	$listeUstensile = [];
	foreach ($resultatListeUstensile as $infoUstensile){
		$listeUstensile[$infoUstensile['id']] = $infoUstensile;
	}
	return $listeUstensile;
}

function getAllPreparations(){
	$requete = "SELECT nom, id FROM Preparation ORDER BY nom;";
	$resultatListePreparation = getAssocFromQueryString($requete);
	$listePreparation = [];
	foreach ($resultatListePreparation as $infoPreparation){
		$listePreparation[$infoPreparation['id']] = $infoPreparation;
	}
	return $listePreparation;
}

function getAllRecettes(){
	$requete = "SELECT * FROM Recette ORDER BY nom;";
	$resultatRecettes = getAssocFromQueryString($requete);
	$listeRecette = [];
	foreach($resultatRecettes as $infoRecette){
		$listeRecette[$infoRecette['id']] = $infoRecette;
	}
	return $listeRecette;
}

function getAllGenres(){
	$requete = "SELECT nom, id FROM Genre;";
	$resultatGenre = getAssocFromQueryString($requete);
	$listeGenre = [];
	foreach ($resultatGenre as $infoGenre){
		$listeGenre[$infoGenre['id']] = $infoGenre;
	}
	return $listeGenre;
}

function getAllUnites(){
	$requete = "SELECT * FROM Unite;";
	$resultatUnite = getAssocFromQueryString($requete);
	$listeUnite = [];
	foreach ($resultatUnite as $unite){
		$listeUnite[$unite['id']] = $unite;
	}
	return $listeUnite;
}

function getRecetteFromId($id){
	$requete = "SELECT * FROM Recette WHERE id=$id;";
	$resultat = getAssocFromQueryString($requete);
	return ($resultat ? $resultat[0] : false);
}

function getRecetteFullFromId($id){
	$requete = 
	"SELECT r.nom, r.id idRecette, r.nbPortion portions, u.nom unite, r.realisation, u.id idU, r.tempsTotal tempsTotal, g.nom genre
	FROM Recette r JOIN Unite u ON r.unitePortion = u.id JOIN Genre g ON r.genre = g.id
	WHERE r.id = $id";
	$resultat = getAssocFromQueryString($requete);
	return ($resultat ? $resultat[0] : false);
}

function getAllIngredientsWithRecetteId($id){
	$requete = 
	"SELECT ri.ingredient idIngredient, i.nom nom, ri.quantite quantite, u.nom unite, u.id idU
	FROM Ingredient i JOIN RecetteIngredient ri ON i.id = ri.ingredient JOIN Unite u ON ri.unite = u.id 
	WHERE ri.recette = $id 
	ORDER BY nom;";
	$resultat = getAssocFromQueryString($requete);
	$listeIngredient = [];
	foreach ($resultat as $key => $value) {
		$listeIngredient[] = $value;
	}
	return $listeIngredient;
}

function getAllUstensileWithRecetteId($id){
	$requete =
	"SELECT ru.ustensile idUstensile, u.nom nom, ru.commentaire commentaire
	FROM Ustensile u JOIN RecetteUstensile ru ON u.id = ru.ustensile
	WHERE ru.recette = $id
	ORDER BY nom;";
	$resultat = getAssocFromQueryString($requete);
	$listeUstensile = [];
	foreach ($resultat as $key => $value) {
		$listeUstensile[] = $value;
	}
	return $listeUstensile;
}

function getAllPreparationWithRecetteId($id){
	$requete =
	"SELECT rp.preparation idPreparation, p.nom nom, rp.duree duree, rp.temperature temperature
	FROM Preparation p JOIN RecettePreparation rp ON p.id = rp.preparation
	WHERE rp.recette = $id
	ORDER BY nom;";
	$resultat = getAssocFromQueryString($requete);
	$listePreparation = [];
	foreach ($resultat as $key => $value) {
		$listePreparation[] = $value;
	}
	return $listePreparation;
}

function getGenreWithRecetteId($id){
	$requete = 
	"SELECT g.id id, g.nom nom
	FROM Genre g JOIN Recette r ON g.id = r.genre
	WHERE r.id = $id;";
	$resultat = getAssocFromQueryString($requete);
	return ($resultat ? $resultat[0] : false);
}

function getAllReutiliseeWithRecetteId($id){
	$requete =
	"SELECT reu.utilisee id, rec.nom nom
	FROM Reutilise reu JOIN Recette rec ON reu.utilisee = rec.id
	WHERE reu.utilisant = $id
	ORDER BY nom;";
	$resultat = getAssocFromQueryString($requete);
	$listeReutilisee = [];
	foreach ($resultat as $key => $value) {
		$listePreparation[] = $value;
	}
	return $listeReutilisee;
}

function getRecetteCount(){
	$requete = "SELECT COUNT(*) nb FROM Recette;";
	$ret = getAssocFromQueryString($requete)[0]["nb"];
	return $ret;
}

function safePutIngredient($nom, $dispo){
	$db = getDB();
	$nomSafe = $db->escapeString($nom);
	$requete = "INSERT INTO Ingredient (nom, " . implode(', ', lANNEE) . ") VALUES ('$nomSafe'";
	foreach (lANNEE as $mois){
		$requete .= ", " . ($dispo[$mois] ? "1" : "0");
	} 
	$requete .= ");";
	return $db->exec($requete);

}

function safePutUstensile($nom){
	$db = getDB();
	$nomSafe = $db->escapeString($nom);
	$requete = "INSERT INTO Ustensile (nom) VALUES ('$nomSafe');";
	return $db->exec($requete);
}

function safePutUnite($nom){
	$db = getDB();
	$nomSafe = $db->escapeString($nom);
	$requete = "INSERT INTO Unite (nom) VALUES ('$nomSafe');";
	return $db->exec($requete);
}

function getElementWithElementId($idElement, $table){
	$requeteId = "SELECT * FROM $table WHERE id=$idElement;";
	$resultat = getAssocFromQueryString($requeteId);
	return ($resultat ? $resultat[0] : false);
}

// modification vaut false si on effectue une création de recette et l'id de la recette à modifier sinon
// les listes d'ingrédients, de préparation et d'ustensile sont des tableaux de tableaux indexés sur les id des éléments utilisés.
function insererRecette($infosRecette, $listeIngredients, $listeUstensile, $listePreparation, $listeReutilise, $modification){
	$c = seConnecter();	
	try {
		mysqli_begin_transaction($c);
	
		$nom = echappementBDD($infosRecette['nom']);
		$tempsTotal = calculeMinutesFromTexte($infosRecette['tempsTotal']);
		$genre = $infosRecette['genre'];
		$unitePortion = $infosRecette['unitePortion'];
		$nbPortion = $infosRecette['nbPortion'];
		$realisation = echappementBDD(ecritureVersLecture($infosRecette['realisation']));
		
		if($modification){
			$idRecette = $modification;
			$requetePrincipale = "
				UPDATE Recette
				SET
					nom = '$nom',
					tempsTotal = $tempsTotal,
					genre = $genre,
					unitePortion = $unitePortion,
					nbPortion = $nbPortion,
					realisation = '$realisation'
				WHERE id = $idRecette;
			";
			mysqli_query($c, $requetePrincipale);
			
			$requeteSupprIngredients = "DELETE FROM RecetteIngredient WHERE recette = $idRecette;";
			mysqli_query($c, $requeteSupprIngredients);
			
			$requeteSupprUstensiles = "DELETE FROM RecetteUstensile WHERE recette = $idRecette;";
			mysqli_query($c, $requeteSupprUstensiles);
			
			$requeteSupprPreparations = "DELETE FROM RecettePreparation WHERE recette = $idRecette;";
			mysqli_query($c, $requeteSupprPreparations);
			
			$requeteSupprReutilises = "DELETE FROM Reutilise WHERE utilisant = $idRecette;";
			mysqli_query($c, $requeteSupprReutilises);
			
		}else{
			$requetePrincipale = "
				INSERT INTO Recette (nom, tempsTotal, genre, unitePortion, nbPortion, realisation)
				VALUES (
					'$nom',
					$tempsTotal,
					$genre,
					$unitePortion,
					$nbPortion,
					'$realisation'
				);";
			mysqli_query($c, $requetePrincipale);
			$idRecette = mysqli_insert_id($c);
		}
		
		foreach($listeIngredients as $id => $ingredient){
			$quantite = $ingredient['quantite'];
			$unite = $ingredient['unite'];
			$requete = "
				INSERT INTO RecetteIngredient (ingredient, recette, quantite, unite)
				VALUES (
					$id,
					$idRecette,
					$quantite,
					$unite
					)
				;";
			mysqli_query($c, $requete);
		}
		
		foreach($listeUstensile as $id => $ustensile){
			$commentaire = mysqli_real_escape_string($c, $ustensile['commentaire']);
			$requete = "
				INSERT INTO RecetteUstensile (ustensile, recette, commentaire)
				VALUES (
					$id,
					$idRecette,
					'$commentaire'
					)
				;";
			mysqli_query($c, $requete);
		}
		
		foreach($listePreparation as $id => $preparation){
			$duree = $preparation['duree'];
			$temperature = $preparation['temp'];
			$requete = "
				INSERT INTO RecettePreparation (preparation, recette, duree, temperature)
				VALUES (
					$id,
					$idRecette,
					'$duree',
					'$temperature'
					)
				;";
			mysqli_query($c, $requete);
		}
		
		foreach($listeReutilise as $id => $utilisee){
			$requete = "
				INSERT INTO Reutilise (utilisee, utilisant)
				VALUES (
					$id,
					$idRecette
					)
				;";
			mysqli_query($c, $requete);
		}
		mysqli_commit($c);
	}
	catch (Exception $e){
		mysqli_query('ROLLBACK');
		$echec = true;
	}
	
	return $idRecette;
}


?>