<?php
	require_once('LocalRepositoryInterface.php');
	require_once('Local.php');

	class LocalRepository implements LocalRepositoryInterface {
		protected $db;

		public function __construct($db) {
			$this->db = $db;
		}

		public function registerLocal($local) {
			$status = array();

			$queryLocal = "INSERT INTO locale(id_utente_owner, nomelocale, descrizione, tipologia, lat, lon)
    				VALUES ('".$local->id_utente_owner."',
    						'".$local->nomelocale."',
    						'".$local->descrizione."',
    						'".$local->tipologia."',
    						'".$local->lat."',
    						'".$local->lon."')";
			
			//echo "<br/>".$query."<br/>";

			$imgs = $local->imgs;
			$imgsToInsert = array();

			try {
				$this->db->exec($queryLocal);
				$id_locale = $this->db->lastInsertId();

				/*foreach($imgs as $img) {
					$imgsToInsert[] = '("'.$id_locale.'", "'.$img.'")';
				}

				$queryImgs = "INSERT INTO immagini_locali(id_locale, img) VALUES" . implode(',', $imgsToInsert);

				$this->db->exec($queryImgs);*/

				$status["local_id"] = $id_locale;
				$status["return"] = 0;

				return $status;

			} catch (PDOException $e) {
				echo $e->getMessage();

				$status["local_id"] = -1;
				$status["return"] = 3;

				return $status;
			}
		}

		public function setLocalPosition($id_local, $lat, $lon, $indirizzo) {
			$query = "UPDATE locale
						SET lat=".$lat.",lon=".$lon.",indirizzo='".$indirizzo."'".
						"WHERE id=".$id_local;

			//echo "<br/>".$query."<br/>";

			try {
				return $this->db->exec($query);
			} catch (PDOException $e) {
				echo $e->getMessage();
				return -1;
			}		
		}

		public function setLocalName($id_local, $name) {
			$query = "UPDATE locale
						SET nomelocale='".$name."'".
						"WHERE id=".$id_local;

			//echo "<br/>".$query."<br/>";

			try {
				return $this->db->exec($query);
			} catch (PDOException $e) {
				echo $e->getMessage();
				return -1;
			}		
		}

		public function setLocalDescription($id_local, $description) {
			$query = "UPDATE locale
						SET descrizione='".$description."'".
						"WHERE id=".$id_local;

			try {
				return $this->db->exec($query);
			} catch (PDOException $e) {
				echo $e->getMessage();
				return -1;
			}		
		}

		public function getImages($id_local) {
			$local = new Local();
			$query;

			$sql = "SELECT img 
						FROM immagini_locali 
						WHERE id_locale=".$id_local;

			try {
				$query = $this->db->query($sql);

				if (!$query) return null;

				$rows = $query->fetchAll(PDO::FETCH_ASSOC);

				$local->imgs = array();

				foreach ($rows as $row) {
					array_push($local->imgs, $row["img"]);
				}

				return $local;
			} catch (PDOException $e) {
				echo $e->getMessage();
				return null;
			}	
		}

		/*public function addImage($id_local, $imgs) {
			$sql = "INSERT INTO immagini_locali(id_locale, img)
							VALUES (".$id_local.",'".$img."')";

			try {
				$this->db->exec($sql);
				return 0;
			} catch (PDOException $e) {
				echo $e->getMessage();
				return 1;
			}
		}*/

		public function addImages($id_local, $imgs) {
			try {
				foreach($imgs as $img) {
					$imgsToInsert[] = '("'.$id_local.'", "'.$img.'")';
				}

				$queryImgs = "INSERT INTO immagini_locali(id_locale, img) VALUES" . implode(',', $imgsToInsert);
				//echo "<br/>".$queryImgs."<br/>";

				$this->db->exec($queryImgs);
				return 0;

			} catch (PDOException $e) {
				echo $e->getMessage();
				return 1;
			}
		}

		public function getMinimalLocalInfo($id_local) {
			$local = new Local();
			$queryLocal;

			$sqlLocal = "SELECT id, tipologia, descrizione, indirizzo
					FROM locale
					WHERE id=".$id_local;

			//echo "<br/>".$sql."<br/>";

			try {
				$queryLocal = $this->db->query($sqlLocal);

				if (!$queryLocal) return null;

				$rowLocal = $queryLocal->fetch(PDO::FETCH_ASSOC);

				$local->tipologia 	= $rowLocal["tipologia"];
				$local->descrizione = $rowLocal["descrizione"];
				//$local->stelle 		= $row["stelle"];
				$local->indirizzo	= (!is_null($rowLocal["indirizzo"])) ? $rowLocal["indirizzo"] : "";

				$sqlStelle = "SELECT avg(commenti.voto) as stelle
								FROM commenti
								WHERE commenti.id_locale=".$id_local."
								GROUP BY commenti.id_locale";

				$resultStelle = $this->db->query($sqlStelle);

				$local->stelle 	= ($resultStelle->rowCount()>0) ? $resultStelle->fetchColumn() : 0;

				return $local;

			} catch (PDOException $e) {
				echo $e->getMessage();
				return null;
			}
		}

		public function deleteImage($id_local, $img) {
			$sql = "DELETE FROM immagini_locali WHERE id_locale=".$id_local." AND img='".$img."'";
			try {
				$this->db->exec($sql);
				return 0;
			} catch (PDOException $e) {
				echo $e->getMessage();
				return 1;
			}
		}

		public function getCloseLocals($tipologia, $lat, $lon, $distance) {
			$local_list = array();

			$whereClause = ($tipologia>=0) ? "WHERE tipologia='".$tipologia."'" : "";

			$harvesinFormulaSQL = "SELECT id, lat, lon, tipologia, nomelocale,
									( 6371 * acos( cos( radians(".$lat.") ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(".$lon.") ) + sin( radians(".$lat.") ) * sin( radians( lat ) ) ) ) AS distance
									FROM locale
									".$whereClause."
									HAVING distance < ".$distance/1000.0."
									ORDER BY distance";
									//ORDER BY distance LIMIT 0 , 20"; //nel caso in cui si voglia introdurre il limite di locali vicini

			//echo "<br/>".$harvesinFormulaSQL."<br/>";

			try {
				$query = $this->db->query($harvesinFormulaSQL);

				if (!$query) return null;

				$rows = $query->fetchAll(PDO::FETCH_ASSOC);

				foreach ($rows as $row) {
					$local = array();
					$local["id"]			= $row["id"];
					$local["lat"] 			= $row["lat"];
					$local["lon"] 			= $row["lon"];
					$local["tipologia"]		= $row["tipologia"];
					$local["nomelocale"] 	= $row["nomelocale"];

					array_push($local_list, $local);
				}

				return $local_list;

			} catch (PDOException $e) {
				echo $e->getMessage();
				return null;
			}
		}
	}

?>