<?php
	if($_POST["submit"] != "yep") {
		echo "0Forbidden";
		exit();
	}

	$servername = "localhost";
	$username = "upld";
	$password = "upld";
	$dbname = "upld";

	// Create connection
	$conn = new mysqli($servername, $username, $password, $dbname);
	// Check connection
	if ($conn->connect_error) {
		echo("0Connection failed: " . $conn->connect_error);
		exit();
	}

	if($_POST["what"] == "all") {
		$results = array();

		$sql = "SELECT * FROM snippets";
		$result = $conn->query($sql);

		if ($result->num_rows > 0) {

			while($row = $result->fetch_assoc()) {
				$r = array();

				$snip_id = $row["id"];
				$snip_listing = $row["listing"];
				$snip_text = $row["text"];
				$snip_title = $row["title"];
				$snip_tags = array();

				$sql_tags = "SELECT tags.name, tags.is_minor FROM tags INNER JOIN snippets_tags ON snippets_tags.tags_id=tags.id WHERE snippets_tags.snippets_id=" . $snip_id;
				$result_tags = $conn->query($sql_tags);

				if ($result_tags->num_rows > 0) {
					while($row_tags = $result_tags->fetch_assoc()) {
						$tag = array("name" => $row_tags["name"], "is_minor" => $row_tags["is_minor"]);
						array_push($snip_tags, $tag);
					}
				}

				$r["id"] = 	$snip_id;
				$r["title"] = $snip_title;
				$r["listing"] = $snip_listing;
				$r["tags"] = $snip_tags;
				$r["text"] = $snip_text;

				array_push($results, $r);
			}
		}
		else {
			//echo "2no_results";
		}

		echo json_encode($results);
	}

	else if($_POST["what"] == "get_tags") {
		$results = array();
		$results["major"] = array();
		$results["minor"] = array();

		$sql = "SELECT `name` FROM tags WHERE `is_minor`=false";
		$result = $conn->query($sql);

		if ($result->num_rows > 0) {

			while($row = $result->fetch_assoc()) {
				array_push($results["major"], $row["name"]);
			}
		}

		$sql = "SELECT `name` FROM tags WHERE `is_minor`=true";
		$result = $conn->query($sql);

		if ($result->num_rows > 0) {

			while($row = $result->fetch_assoc()) {
				array_push($results["minor"], $row["name"]);
			}
		}

		echo json_encode($results);
	}

	else if($_POST["what"] == "add") {
		if(md5($_POST["secret"]) !== "ec9a58037686a96ba89cccc5446ede22") {
			echo "0Wrong secret!";
		}
		else {
			$input_title = $_POST["title"];
			$input_listing = $_POST["listing"];
			$input_text = $_POST["text"];
			$input_major_tags = $_POST["major_tags"];
			$input_minor_tags = $_POST["minor_tags"];

			$tags_ids = array();

			$input_listing = $conn->real_escape_string($input_listing);

			foreach($input_major_tags as $v) {
				$result = $conn->query("SELECT * FROM tags WHERE name='" . $v . "'");
				$num_rows = $result->num_rows;

				if ($num_rows > 0) {
					$row = $result->fetch_assoc();
					array_push($tags_ids, intval($row["id"]));
				}
				else {
					$conn->query("INSERT INTO tags (name, is_minor) VALUES ('" . $v ."', 0)");
					array_push($tags_ids, intval($conn->insert_id));
				}
			}

			foreach($input_minor_tags as $v) {
				$result = $conn->query("SELECT * FROM tags WHERE name='" . $v . "'");
				$num_rows = $result->num_rows;

				if ($num_rows > 0) {
					$row = $result->fetch_assoc();
					array_push($tags_ids, intval($row["id"]));
				}
				else {
					$conn->query("INSERT INTO tags (name, is_minor) VALUES ('" . $v ."', 1)");
					array_push($tags_ids, intval($conn->insert_id));
				}
			}

			$conn->query("INSERT INTO snippets (title, listing, text) VALUES ('" . $input_title . "', '" . $input_listing . "', '" . $input_text . "')");

			$snippet_id = $conn->insert_id;

			foreach($tags_ids as $v) {
				$conn->query("INSERT INTO snippets_tags (snippets_id, tags_id) VALUES ('" . $snippet_id ."', '" . $v . "')");
			}

			if(!empty($conn->error)) {
				echo "0" . $conn->error;
			}
			else {
				echo "1Added successfully!";
			}

		}
	}

	else if($_POST["what"] == "search") {
		$search_query = $_POST["search"];

		$results = array();

		//search by title
		{
			//$sql = "SELECT * FROM snippets WHERE title LIKE '%" . $search_query ."%'";
			$sql = "SELECT * FROM snippets WHERE REPLACE(title, ' ', '') LIKE REPLACE('%" . $search_query . "%', ' ', '')";
			$result = $conn->query($sql);

			if ($result->num_rows > 0) {

				while($row = $result->fetch_assoc()) {
					$r = array();

					$snip_id = $row["id"];
					$snip_listing = $row["listing"];
					$snip_text = $row["text"];
					$snip_title = $row["title"];
					$snip_tags = array();

					$sql_tags = "SELECT tags.name, tags.is_minor FROM tags INNER JOIN snippets_tags ON snippets_tags.tags_id=tags.id WHERE snippets_tags.snippets_id=" . $snip_id;
					$result_tags = $conn->query($sql_tags);

					if ($result_tags->num_rows > 0) {
						while($row_tags = $result_tags->fetch_assoc()) {
							$tag = array("name" => $row_tags["name"], "is_minor" => $row_tags["is_minor"]);
							array_push($snip_tags, $tag);
						}
					}

					$r["id"] = 	$snip_id;
					$r["title"] = $snip_title;
					$r["listing"] = $snip_listing;
					$r["tags"] = $snip_tags;
					$r["text"] = $snip_text;

					array_push($results, $r);
				}
			}
			else {
				//echo "2no_results";
			}
		}

		//search by tag name
		{
			//	$sql = "SELECT * FROM tags WHERE name LIKE '%" . $search_query ."%'";
			$sql = "SELECT * FROM tags WHERE REPLACE(name, ' ', '') LIKE REPLACE('%" . $search_query . "%', ' ', '')";
			$result = $conn->query($sql);

			$tags_ids = array();

			if ($result->num_rows > 0) {
				while($row = $result->fetch_assoc()) {
					array_push($tags_ids, $row["id"]);
				}
			}

			foreach($tags_ids as $v) {
				$sql = "SELECT snippets.id, snippets.title, snippets.listing, snippets.text FROM snippets INNER JOIN snippets_tags ON snippets_tags.snippets_id=snippets.id WHERE snippets_tags.tags_id=" . $v;
				$result = $conn->query($sql);

				if ($result->num_rows > 0) {
					while($row = $result->fetch_assoc()) {
						$r = array();

						$snip_id = $row["id"];
						$snip_listing = $row["listing"];
						$snip_text = $row["text"];
						$snip_title = $row["title"];
						$snip_tags = array();

						$sql_tags = "SELECT tags.name, tags.is_minor FROM tags INNER JOIN snippets_tags ON snippets_tags.tags_id=tags.id WHERE snippets_tags.snippets_id=" . $snip_id;
						$result_tags = $conn->query($sql_tags);

						if ($result_tags->num_rows > 0) {
							while($row_tags = $result_tags->fetch_assoc()) {
								$tag = array("name" => $row_tags["name"], "is_minor" => $row_tags["is_minor"]);
								array_push($snip_tags, $tag);
							}
						}

						$r["id"] = 	$snip_id;
						$r["title"] = $snip_title;
						$r["listing"] = $snip_listing;
						$r["tags"] = $snip_tags;
						$r["text"] = $snip_text;

						array_push($results, $r);
					}
				}
			}
		}

		$results_unqiue = array_unique($results, SORT_REGULAR);
		$results_return = array_values($results_unqiue);



		//send
		$results_json = json_encode($results_return);

		//header('Content-Length: ' . strlen(gzencode($results_json)));
		echo $results_json;




	}

	else {
		echo "0Unknown request!";
	}



	$conn->close();
?>