<?php

	$sortColumn = @$_GET['sort_column'];
	$sortDirection = @$_GET['sort_dir'];
	$limit = @$_GET['limit'];
	$offset = @$_GET['offset'];
	$filters = @$_GET['filter'];
	$searchQuery = @$_GET['query'];
	
	if (!ctype_digit($limit) || !ctype_digit($offset) || $limit < 0 || $offset < 0) {
		header('HTTP/1.1 400 Bad Request');
		exit;
	}
	
	$conn = mysql_connect('localhost', 'geodata', 'geodata');
	mysql_select_db('geodata', $conn);
	
	$query = '
		SELECT SQL_CALC_FOUND_ROWS
			c.City,
			r.Region,
			co.Country,
			c.Latitude,
			c.Longitude,
			co.Population
		FROM cities c
		INNER JOIN countries co
			ON co.countryId = c.CountryID
		INNER JOIN regions r
			ON r.RegionID = c.RegionID
		';
		
	$whereClauses = array();
	
	if (!empty($searchQuery)) {
		$whereClauses[] = 'c.City LIKE \'%' . mysql_real_escape_string($searchQuery, $conn) . '%\'';
	}
	
	if (!empty($filters)) {
		foreach ($filters as $column => $searchQuery) {
			switch ($column) {
				case 'city':
					$whereClauses[] = 'c.City LIKE \'%' . mysql_real_escape_string($searchQuery, $conn) . '%\'';
					break;
				case 'country':
					$whereClauses[] = 'co.Country LIKE \'%' . mysql_real_escape_string($searchQuery, $conn) . '%\'';
					break;
			}
		}
	}
	
	if (!empty($whereClauses)) {
		$query .= 'WHERE ' . implode("\n\t\tAND   ", $whereClauses) . "\n\t\t";
	}
	
	if (!empty($sortColumn)) {
		switch ($sortColumn) {
			case 'region':
				$query .= 'ORDER BY r.Region';
				break;
			case 'country':
				$query .= 'ORDER BY co.Country';
				break;
			case 'city':
			case 'latitude':
			case 'longitude':
				$query .= 'ORDER BY c.' . ucfirst($sortColumn);
				break;
			default:
				$fail = true;
				break;
		}
	
		if (!isset($fail) && $sortDirection === 'desc') {
			$query .= ' DESC';
		}
	}
		
	$query .= '
		LIMIT ' . $limit . ' OFFSET ' . $offset;
	
	$result = mysql_query($query, $conn);
	if (!$result) {
		header('HTTP/1.1 500 Internal Server Error');
		echo 'The database exploded!';
		exit;
	}
	
	$json = new stdClass();
	$json->records = array();
	$json->offset = (int)$offset;
	while ($row = mysql_fetch_assoc($result)) {
		$obj = new stdClass();
	
		$obj->city = $row['City'];
		$obj->latitude = (float)$row['Latitude'];
		$obj->longitude = (float)$row['Longitude'];
		$obj->region = $row['Region'];
		$obj->country = $row['Country'];
		$obj->countryPopulation = (int)$row['Population'];
		
		$json->records[] = $obj;
	}
	
	$json->totalRecordCount = (int)mysql_result(mysql_query('SELECT FOUND_ROWS()', $conn), 0);
	
	header('Content-Type: application/json');
	echo json_encode($json);
	exit;

?>