<?php

class GeofenceAreaCityModel {

	public static function get($db, $params) {
		$query = "SELECT geofence_city.latitude,
			geofence_city.longitude,
			geofence_city.id,
			geofence_city.location,
			geofence_city.city,
			geofence_city.country,
			geofence_city.radius,
			settings.status,
			settings.geofence_area_city,
			settings.image_url
			FROM geofence_area_city as geofence_city LEFT JOIN geofence_area_city_settings as settings ON settings.geofence_area_city = geofence_city.id";

		if ($params && count($params) > 0) {
			$lat = $params['lat'] ?? '';
			$lng = $params['lng'] ?? '';
			$radius = $params['radius'] ?? '';
			$whereQuery = [];

			$city = $params['city'] ?? '';
			$country = $params['country'] ?? '';
			$status = $params['status'] ?? '';

			if (!empty($status)) {
				$whereQuery[] = "settings.status=$status";
			}
			if (!empty($city)) {
				$whereQuery[] = "LOWER(geofence_city.city)='" . pg_escape_string($city) ."'";
			}
			if (!empty($country)) {
				$whereQuery[] = "LOWER(geofence_city.country)='" . pg_escape_string($country) . "'";
			}

			$whereQuery = implode(" AND ", $whereQuery);

			if (!empty($lat) && !empty($lng) && !empty($radius)) {
				$earth_radius = 6371;
				$km_per_degree_lat = 111.2;
				$pi = 3.14/180;
				$latitude = $params["lat"];
				$longitude = $params["lng"];
				$distance_in_km = $params["radius"];

				$query =  " SELECT geofence_city.latitude, geofence_city.longitude, geofence_city.id, geofence_city.location, geofence_city.city, geofence_city.country, geofence_city.radius, settings.status, settings.geofence_area_city, settings.image_url FROM (
					SELECT (
						$earth_radius * acos(
								cos( radians( {$latitude}) )
							* cos( radians( latitude  ) )
							* cos( radians( longitude ) - radians( {$longitude} ) )
							+ sin( radians( {$latitude}) )
							* sin( radians( latitude  ) ))
						) AS distance,
						id,
						city,
						country,
						latitude,
						longitude,
						location,
						radius
					FROM geofence_area_city
					WHERE latitude BETWEEN "
				. ($latitude - ($distance_in_km / $km_per_degree_lat))
				. " AND " . ($latitude + ($distance_in_km / $km_per_degree_lat));

				$delta = round($distance_in_km  / ($km_per_degree_lat  * COS(deg2rad($longitude))), 10);

				// Bounding box for speed - latitude within range (longtitude to km not linear)
				if(cos($longitude * $pi) > 0) {
					$from =  $longitude - $delta;
					$to = $longitude + $delta;

					$query .= 
						" AND longitude" .
						" BETWEEN " . $from .
						" AND " . $to;

				} else {

						$from =  $longitude + $delta;
						$to = $longitude - $delta;

						$query .= 
							" AND longitude" .
							" BETWEEN " . $from .
							" AND " . $to;
				}

				// distance limit for circle
				$query .= " ) geofence_city JOIN geofence_area_city_settings AS settings ON geofence_city.id = settings.geofence_area_city WHERE distance < " . $distance_in_km . "AND $whereQuery " . "ORDER BY distance ASC";
			} else {
				$query .= " WHERE $whereQuery";
			}
		}

		$r = pg_query($db, $query);
		if ($r && pg_num_rows($r)) {
			$result = pg_fetch_all($r);
		} else {
			return [NULL, "City not found"];
		}

		return [$result, NULL];
	}
}