<?php
	define("DATABASE_NAME",		"multimedia1");
	define("DATABASE_USER",		"root");
	define("DATABASE_PASSWORD",	"WeissIchNicht8");
	define("DATABASE_HOST",		"localhost");

	if (isset($_SERVER['HTTP_ORIGIN']))
	{
		header("Access-Control-Allow-Origin: {$_SERVER['HTTP_ORIGIN']}");
		header("Access-Control-Allow-Credentials: true");
		header("Access-Control-Max-Age: 86400");    // cache for 1 day
	}

	// Access-Control headers are received during OPTIONS requests
	if ($_SERVER["REQUEST_METHOD"] == "OPTIONS")
	{
		if (isset($_SERVER["HTTP_ACCESS_CONTROL_REQUEST_METHOD"]))
			header("Access-Control-Allow-Methods: GET, POST, OPTIONS");

		if (isset($_SERVER["HTTP_ACCESS_CONTROL_REQUEST_HEADERS"]))
			header("Access-Control-Allow-Headers:        {$_SERVER['HTTP_ACCESS_CONTROL_REQUEST_HEADERS']}");

		exit(0);
	}

	$module		= $_GET["module"];
	$function	= $_GET["function"];

	if($module == "movie") {
		if ($function == "getList") {
			getMovieList();
		} else if ($function == "getDetails") {
			$movieID = $_GET["movieID"];
			getMovieDetails($movieID);
		} else {
			notImplemented();
		}
	} else if ($module == "tvshow") {
		if ($function == "getList") {
			getTVShowList();
		} else if ($function == "getDetails") {
			$tvshowID = $_GET["tvshowID"];
			getTVShowDetails($tvshowID);
		} else {
			notImplemented();
		}
	} else if ($module == "dbTest") {
		dbTest();
	} else {
		notImplemented();
	}

	function tvShowSort($a, $b) {
		return strtoupper($a["seriesName"]) > strtoupper($b["seriesName"]);
	}

	function seasonSort($a, $b) {
		return $a["seasonNumber"] > $b["seasonNumber"];
	}

	function dbTest() {
	}

	function getTVShowList() {
		try
		{
			$json["error"]			= false;
			$json["errmsg"]			= "";
			$json["minSeason"]		= -1;
			$json["maxSeason"]		= -1;
			$json["episodeCount"]		= array();
			$json["tvshows"]		= array();

			$link				= mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME);

			$sql				= "SELECT MIN(s.seasonNumber) min, MAX(s.seasonNumber) max FROM season s;";

			$res				= mysqli_query($link, $sql);
			$numrows			= mysqli_num_rows($res);

			if($numrows > 0) {
				$array			= mysqli_fetch_assoc($res);
				$json["minSeason"]	= $array["min"];
				$json["maxSeason"]	= $array["max"];
			}

			$sql				= "SELECT MAX(s.cnt) cnt, s.seasonNumber FROM ( SELECT COUNT(1) cnt, e.seasonNumber, e.seriesID FROM episode e WHERE e.seriesID IS NOT NULL GROUP BY e.seasonNumber, e.seriesID) s GROUP BY s.seasonNumber;";

			$res				= mysqli_query($link, $sql);
			$numrows			= mysqli_num_rows($res);

			if($numrows > 0) {
				while($array = mysqli_fetch_assoc($res)) {
					array_push($json["episodeCount"], $array);
				}
			}

			$first				= true;
			$seriesID			= -1;
			$tvShowArray			= array();

			$sql				=	"SELECT 	IFNULL(t.seriesName, '') AS seriesName, " .
								"		IFNULL(t.overview, '') AS overview, " .
								"		IFNULL(t.firstAired, '1970-01-01') AS firstAired, " .
								"		IFNULL(t.resolution, '') AS resolution, " .
								"		IFNULL(t.cliffhanger, '') AS cliffhanger, " .
								"		IFNULL(t.status, '') AS status, " .
								"		IFNULL(ts.state, '') AS state1, " .
								"		IFNULL(e.state, '') AS state " .
								"FROM " .
								"(		SELECT		e.seasonID, " .
								"				e.seriesID,  " .
								"				e.seasonNumber, " .
								"				GROUP_CONCAT(CONCAT(e.episodeNumber, '|', e.state) SEPARATOR ',') state " .
								"		FROM		episode e " .
								"		WHERE		e.seasonID <> 0 " .
								"		GROUP BY	e.seasonID " .
								") e " .
								"JOIN		season s ON (e.seasonID = s.id) " .
								"JOIN		serie t ON (e.seriesID = t.seriesID) " .
								"JOIN " .
								"(		SELECT		es.seriesID, " .
								"				GROUP_CONCAT(CONCAT(es.state, '|', cnt) SEPARATOR ',') state " .
								"		FROM " .
								"		(		SELECT		COUNT(1) cnt, " .
								"						e.state, " .
								"						e.seriesID " .
								"				FROM		episode e " .
								"				WHERE		e.seasonID <> 0 " .
								"				GROUP BY	e.state, " .
								"						e.seriesID " .
								"		) es " .
								"		GROUP BY	es.seriesID " .
								") ts		ON (ts.seriesID = t.seriesID) " .
								"ORDER BY	e.seriesID, " .
								"		e.seasonNumber;";
			$res					= mysqli_query($link, $sql);
			$numrows				= mysqli_num_rows($res);


			if($numrows > 0) {
				while($array = mysqli_fetch_assoc($res)) {
					if($seriesID != $array["seriesID"]) {
						$seriesID = $array["seriesID"];
						if($first == true) {
							$first = false;
						} else {
							array_push($json["tvshows"], $tvShowArray);
						}
						$tvShowArray				= array();
						$tvShowArray["seriesName"]		= $array["seriesName"];
						$tvShowArray["overview"]		= $array["overview"];
						$tvShowArray["firstAired"]		= $array["firstAired"];
						$tvShowArray["resolution"]		= $array["resolution"];
						$tvShowArray["cliffhanger"]		= $array["cliffhanger"];
						$tvShowArray["status"]			= $array["status"];
						$tvShowArray["backdropPath"]		= $array["backdropPath"];
						$tvShowArray["seriesID"]		= $array["seriesID"];
						$tvShowArray["seriesState"]		= $array["state1"];
						$tvShowArray["languages"]		= $array["languages"];
						$tvShowArray["networks"]		= $array["networks"];
						$tvShowArray["originCountries"]		= $array["originCountries"];
						$tvShowArray["productionCompanies"]	= $array["productionCompanies"];
						$tvShowArray["voteAverage"]		= $array["voteAverage"];
						$tvShowArray["voteCount"]		= $array["voteCount"];
						$tvShowArray["cast"]			= $array["cast"];
						$tvShowArray["crew"]			= $array["crew"];
						$tvShowArray["genre"]			= $array["genre"];
						$tvShowArray["download"]		= $array["download"];
						$tvShowArray["localPath"]		= $array["localpath"];
						$tvShowArray["seasons"]			= array();
					}

					$seasonArray["seasonNumber"]			= $array["seasonNumber"];
					$seasonArray["seasonID"]			= $array["seasonID"];
					$seasonArray["state"]				= $array["state"];
					array_push($tvShowArray["seasons"], $seasonArray);
				}
				array_push($json["tvshows"], $tvShowArray);
			}

			mysqli_close($link);

			usort($json["tvshows"], "tvShowSort");
		}
		catch (PDOException $e)
		{
			$json["error"]	= true;
			$json["errmsg"]	=  $e->getMessage();
		}

		header("Content-Type: application/json");
		echo json_encode($json);
	}

	function getTVShowDetails($tvshowID) {
		try
		{
			$json["error"]			= false;
			$json["errmsg"]			= "";
			$json["minSeason"]		= -1;
			$json["maxSeason"]		= -1;
			$json["episodeCount"]		= array();
			$json["tvshows"]		= array();

			$link				= mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME);

			$sql				= "SELECT MIN(s.seasonNumber) min, MAX(s.seasonNumber) max FROM season s;";

			$res				= mysqli_query($link, $sql);
			$numrows			= mysqli_num_rows($res);

			if($numrows > 0) {
				$array			= mysqli_fetch_assoc($res);
				$json["minSeason"]	= $array["min"];
				$json["maxSeason"]	= $array["max"];
			}

			$sql				= 	"SELECT		MAX(s.cnt) cnt, " .
								"		s.seasonNumber " .
								"FROM		( " .
								"			SELECT		COUNT(1) cnt, " .
								"					e.seasonNumber,	" .
								"					e.seriesID " .
								"			FROM		episode e " .
								"			WHERE		e.seriesID IS NOT NULL AND " .
								"					e.seriesID = " . $tvshowID;
								"			GROUP BY	e.seasonNumber, " .
								"					e.seriesID " .
								"		) s " .
								"GROUP BY	s.seasonNumber;";

			$res				= mysqli_query($link, $sql);
			$numrows			= mysqli_num_rows($res);

			if($numrows > 0) {
				while($array = mysqli_fetch_assoc($res)) {
					array_push($json["episodeCount"], $array);
				}
			}

			$first				= true;
			$seriesID			= -1;
			$tvShowArray			= array();

			$sql				=	"SELECT 	IFNULL(t.seriesName, '') AS seriesName, " .
								"		IFNULL(t.overview, '') AS overview, " .
								"		IFNULL(t.firstAired, '1970-01-01') AS firstAired, " .
								"		IFNULL(t.resolution, '') AS resolution, " .
								"		IFNULL(t.cliffhanger, '') AS cliffhanger, " .
								"		IFNULL(t.status, '') AS status, " .
								"		IFNULL(ts.state, '') AS state1, " .
								"		IFNULL(t.backdropPath, '') backdropPath, " .
								"		IFNULL(s.seasonNumber, '0') seasonNumber, " .
								"		IFNULL(e.seasonID, 0) AS seasonID, " .
								"		IFNULL(e.seriesID, 0) AS seriesID, " .
								"		IFNULL(e.state, '') AS state, " .
								"		IFNULL(t.languages, '') AS languages, " .
								"		IFNULL(t.networks, '') AS networks, " .
								"		IFNULL(t.originCountries, '') AS originCountries, " .
								"		IFNULL(t.productionCompanies, '') AS productionCompanies, " .
								"		t.voteAverage, " .
								"		t.voteCount, " .
								"		IFNULL(t.cast, '') AS cast, " .
								"		IFNULL(t.crew, '') AS crew, " .
								"		IFNULL(t.genre, '') AS genre, " .
								"		IFNULL(t.download, '') AS download, " .
								"		IFNULL(t.localPath, '') AS localpath " .
								"FROM " .
								"(		SELECT		e.seasonID, " .
								"				e.seriesID,  " .
								"				e.seasonNumber, " .
								"				GROUP_CONCAT(CONCAT(e.episodeNumber, '|', e.state) SEPARATOR ',') state " .
								"		FROM		episode e " .
								"		WHERE		e.seasonID <> 0 " .
								"		GROUP BY	e.seasonID " .
								") e " .
								"JOIN		season s ON (e.seasonID = s.id) " .
								"JOIN		serie t ON (e.seriesID = t.seriesID) " .
								"JOIN " .
								"(		SELECT		es.seriesID, " .
								"				GROUP_CONCAT(CONCAT(es.state, '|', cnt) SEPARATOR ',') state " .
								"		FROM " .
								"		(		SELECT		COUNT(1) cnt, " .
								"						e.state, " .
								"						e.seriesID " .
								"				FROM		episode e " .
								"				WHERE		e.seasonID <> 0 " .
								"				GROUP BY	e.state, " .
								"						e.seriesID " .
								"		) es " .
								"		GROUP BY	es.seriesID " .
								") ts		ON (ts.seriesID = t.seriesID) " .
								"WHERE		.seriesID = " . $tvshowID;
								"ORDER BY	e.seriesID, " .
								"		e.seasonNumber;";


			$sql					=	"SELECT 	IFNULL(t.seriesName, '') AS seriesName, " .
									"		IFNULL(t.overview, '') AS overview, " .
									"		IFNULL(t.firstAired, '1970-01-01') AS firstAired, " .
									"		IFNULL(t.resolution, '') AS resolution, " .
									"		IFNULL(t.cliffhanger, '') AS cliffhanger, " .
									"		IFNULL(t.status, '') AS status, " .
									"		IFNULL(ts.state, '') AS state1, " .
									"		IFNULL(t.backdropPath, '') backdropPath, " .
									"		IFNULL(s.seasonNumber, '0') seasonNumber, " .
									"		IFNULL(e.seasonID, 0) AS seasonID, " .
									"		IFNULL(e.seriesID, 0) AS seriesID, " .
									"		IFNULL(e.state, '') AS state, " .
									"		IFNULL(t.languages, '') AS languages, " .
									"		IFNULL(t.networks, '') AS networks, " .
									"		IFNULL(t.originCountries, '') AS originCountries, " .
									"		IFNULL(t.productionCompanies, '') AS productionCompanies, " .
									"		t.voteAverage, " .
									"		t.voteCount, " .
									"		IFNULL(t.cast, '') AS cast, " .
									"		IFNULL(t.crew, '') AS crew, " .
									"		IFNULL(t.genre, '') AS genre, " .
									"		IFNULL(t.download, '') AS download, " .
									"		IFNULL(t.localPath, '') AS localpath " .
									"FROM " .
									"(		SELECT		e.seasonID, " .
									"				e.seriesID,  " .
									"				e.seasonNumber, " .
									"				GROUP_CONCAT(CONCAT(e.episodeNumber, '|', e.state) SEPARATOR ',') state " .
									"		FROM		episode e " .
									"		WHERE		e.seasonID <> 0 " .
									"		GROUP BY	e.seasonID " .
									") e " .
									"JOIN		season s ON (e.seasonID = s.id) " .
									"JOIN		serie t ON (e.seriesID = t.seriesID) " .
									"JOIN " .
									"(		SELECT		es.seriesID, " .
									"				GROUP_CONCAT(CONCAT(es.state, '|', cnt) SEPARATOR ',') state " .
									"		FROM " .
									"		(		SELECT		COUNT(1) cnt, " .
									"						e.state, " .
									"						e.seriesID " .
									"				FROM		episode e " .
									"				WHERE		e.seasonID <> 0 " .
									"				GROUP BY	e.state, " .
									"						e.seriesID " .
									"		) es " .
									"		GROUP BY	es.seriesID " .
									") ts		ON (ts.seriesID = t.seriesID) " .
									"ORDER BY	e.seriesID, " .
									"		e.seasonNumber;";


			$res					= mysqli_query($link, $sql);
			$numrows				= mysqli_num_rows($res);


			if($numrows > 0) {
				while($array = mysqli_fetch_assoc($res)) {
					if($seriesID != $array["seriesID"]) {
						$seriesID = $array["seriesID"];
						if($first == true) {
							$first = false;
						} else {
							array_push($json["tvshows"], $tvShowArray);
						}
						$tvShowArray				= array();
						$tvShowArray["seriesName"]		= $array["seriesName"];
						$tvShowArray["overview"]		= $array["overview"];
						$tvShowArray["firstAired"]		= $array["firstAired"];
						$tvShowArray["resolution"]		= $array["resolution"];
						$tvShowArray["cliffhanger"]		= $array["cliffhanger"];
						$tvShowArray["status"]			= $array["status"];
						$tvShowArray["backdropPath"]		= $array["backdropPath"];
						$tvShowArray["seriesID"]		= $array["seriesID"];
						$tvShowArray["seriesState"]		= $array["state1"];
						$tvShowArray["languages"]		= $array["languages"];
						$tvShowArray["networks"]		= $array["networks"];
						$tvShowArray["originCountries"]		= $array["originCountries"];
						$tvShowArray["productionCompanies"]	= $array["productionCompanies"];
						$tvShowArray["voteAverage"]		= $array["voteAverage"];
						$tvShowArray["voteCount"]		= $array["voteCount"];
						$tvShowArray["cast"]			= $array["cast"];
						$tvShowArray["crew"]			= $array["crew"];
						$tvShowArray["genre"]			= $array["genre"];
						$tvShowArray["download"]		= $array["download"];
						$tvShowArray["localPath"]		= $array["localpath"];
						$tvShowArray["seasons"]			= array();
					}

					$seasonArray["seasonNumber"]			= $array["seasonNumber"];
					$seasonArray["seasonID"]			= $array["seasonID"];
					$seasonArray["state"]				= $array["state"];
					array_push($tvShowArray["seasons"], $seasonArray);
				}
				array_push($json["tvshows"], $tvShowArray);
			}

			mysqli_close($link);

			usort($json["tvshows"], "tvShowSort");
		}
		catch (PDOException $e)
		{
			$json["error"]	= true;
			$json["errmsg"]	=  $e->getMessage();
		}

		header("Content-Type: application/json");
		echo json_encode($json);
	}

	function getMovieList()
	{
		try
		{
			$json["error"]		= false;
			$json["errmsg"]		= "";
			$json["data"]		= array();

			$link			= mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME);

			$sql			= 	"SELECT		movieID, " .
							"		IFNULL(movieTitle, '') AS movieTitle, " .
							"		IFNULL(overview, '') AS overview, " .
							"		IFNULL(releaseDate, '1970-01-01') AS releaseDate, " .
							"		IFNULL(resolution, '') AS resolution, " .
							"		IFNULL(state, 0) AS state " .
							"FROM		movie " .
							"ORDER BY	UPPER(movieTitle);";
			$res				= mysqli_query($link, $sql);


			$numrows			= mysqli_num_rows($res);

			if($numrows > 0)
			{
				//check if there is any data
				$namelist	= array();

				while($array = mysqli_fetch_assoc($res))
				{
					array_push($json["data"], $array);
				}
			}
			else
			{
				$json["error"]	= true;
				$json["errmsg"]	= "No data.";
			}

			mysqli_close($link);

		}
		catch (PDOException $e)
		{
			$json["error"]	= true;
			$json["errmsg"]	=  $e->getMessage();
		}

		header("Content-Type: application/json");
		echo json_encode($json);
	}

	function getMovieDetails($movieID)
	{
		try
		{
			$json["error"]		= false;
			$json["errmsg"]		= "";
			$json["data"]		= array();

			$link			= mysqli_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, DATABASE_NAME);

			$sql			= 	"SELECT		movieID, " .
							"		IFNULL(movieTitle, '') AS movieTitle, " .
							"		IFNULL(originalTitle, '') AS originalTitle, " .
							"		IFNULL(overview, '') AS overview, " .
							"		IFNULL(releaseDate, '1970-01-01') AS releaseDate, " .
							"		IFNULL(resolution, '') AS resolution, " .
							"		IFNULL(state, 0) AS state, " .
							"		IFNULL(backdropPath, '') backdropPath, " .
							"		IFNULL(spokenLanguages, '') AS languages, " .
							"		IFNULL(productionCountries, '') AS productionCountries, " .
							"		IFNULL(productionCompanies, '') AS productionCompanies, " .
							"		voteAverage, " .
							"		voteCount, " .
							"		IFNULL(cast, '') AS cast, " .
							"		IFNULL(crew, '') AS crew, " .
							"		IFNULL(genre, '') AS genre, " .
							"		IFNULL(localPath, '') AS localpath " .
							"FROM		movie " .
							"WHERE		movieID = " . $movieID . ";";
			$res				= mysqli_query($link, $sql);


			$numrows			= mysqli_num_rows($res);

			if($numrows > 0)
			{
				//check if there is any data
				$namelist	= array();

				while($array = mysqli_fetch_assoc($res))
				{
					array_push($json["data"], $array);
				}
			}
			else
			{
				$json["error"]	= true;
				$json["errmsg"]	= "No data.";
			}

			mysqli_close($link);

		}
		catch (PDOException $e)
		{
			$json["error"]	= true;
			$json["errmsg"]	=  $e->getMessage();
		}

		header("Content-Type: application/json");
		echo json_encode($json);
	}

	function notImplemented()
	{
		$json["error"]	= true;
		$json["errmsg"]	=  "not implemented";
	
		header("Content-Type: application/json");
		echo json_encode($json);
	}
?>