<?php

class Destinations {

	const TIME_DELTA = 180; // min (3 hrs)
	const TIME_LIMIT = 1262322000; // 2010-01-01
	const DEFAULT_COUNTRY = 'SG';
	const DEFAULT_OPTIONS = 5;
	const CITY_CURVATURE = 1.25;
	const CITY_SPEED = 0.667; // km/min (0.0667 = 40 km/h)
	const MIN_ACTIVITY_DISTANCE = 0.05; // 50m
	const MAX_ACTIVITY_DISTANCE = 50.0; // 50km

	const ACTIVITY_RADIUS = 100000; // 100km

	public static function byActivity($db, $gpsdb, $member_id, $lat, $lng, $radius, $time, $time_delta=Destinations::TIME_DELTA, $country=Destinations::DEFAULT_COUNTRY) {
		syslog(LOG_WARNING,"Activity::getActivity(\$db, \$gpsdb, $member_id, $lat, $lng, $radius, $time, $time_delta, $country)");
		$all_time_days = Destinations::getAllTimeDays();
		$res = [];

	    list($res1, $err1) = Destinations::activityByRangeAndTimeAndRadius(
			$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'start', $time, $time_delta);
		if ($res1 && count($res1)>0) {
			$res = Destinations::uniqueTrips(array_merge($res,$res1));
			if (count($res)>=Destinations::DEFAULT_OPTIONS) {
				return [$res, NULL];
			}
		}
		syslog(LOG_WARNING,$err1);

		list($res2, $err2) = Destinations::activityByRangeAndTimeAndRadius(
			$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'end', $time, $time_delta);
		if ($res2 && count($res2)>0) {
			$res = Destinations::uniqueTrips(array_merge($res,$res2));
			if (count($res)>=Destinations::DEFAULT_OPTIONS) {
				return [$res, NULL];
			}
		}
		syslog(LOG_WARNING,$err2);

		list($res3, $err3) = Destinations::activityByRangeAndRadius(
			$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'start');
		if ($res3 && count($res3)>0) {
			$res = Destinations::uniqueTrips(array_merge($res,$res3));
			if (count($res)>=Destinations::DEFAULT_OPTIONS) {
				return [$res, NULL];
			}
		}
		syslog(LOG_WARNING,$err3);

		list($res4, $err4) = Destinations::activityByRangeAndRadius(
			$gpsdb, $db, $member_id, $all_time_days, $lat, $lng, $radius, 'end');
		if ($res4 && count($res4)>0) {
			$res = Destinations::uniqueTrips(array_merge($res,$res4));
			if (count($res)>=Destinations::DEFAULT_OPTIONS) {
				return [$res, NULL];
			}
		}
		syslog(LOG_WARNING,$err4);

		if ($country=='SG') {
			// For US it may produce the results too far apart
			list($res1, $err1) = Destinations::activityByRangeAndTime($db, $member_id, 7, $time, $time_delta, $country);
			if ($res1 && count($res1)>0) {
				$res = Destinations::uniqueTrips(array_merge($res,$res1));
				if (count($res)>=Destinations::DEFAULT_OPTIONS) {
					return [$res, NULL];
				}
			}
			list($res2, $err2) = Destinations::activityByRangeAndTime($db, $member_id, 30, $time, $time_delta, $country);
			if ($res2 && count($res2)>0) {
				$res = Destinations::uniqueTrips(array_merge($res,$res2));
				if (count($res)>=Destinations::DEFAULT_OPTIONS) {
					return [$res, NULL];
				}
			}
			list($res3, $err3) = Destinations::activityByRangeAndTime($db, $member_id, $all_time_days, $time, $time_delta, $country);
			if ($res3 && count($res3)>0) {
				$res = Destinations::uniqueTrips(array_merge($res,$res3));
				if (count($res)>=Destinations::DEFAULT_OPTIONS) {
					return [$res, NULL];
				}
			}
			list($res4, $err4) = Destinations::activityByRange($db, $member_id, 7, $country);
			if ($res4 && count($res4)>0) {
				$res = Destinations::uniqueTrips(array_merge($res,$res4));
				if (count($res)>=Destinations::DEFAULT_OPTIONS) {
					return [$res, NULL];
				}
			}
			list($res5, $err5) = Destinations::activityByRange($db, $member_id, 30, $country);
			if ($res5 && count($res5)>0) {
				$res = Destinations::uniqueTrips(array_merge($res,$res5));
				if (count($res)>=Destinations::DEFAULT_OPTIONS) {
					return [$res, NULL];
				}
			}
			list($res6, $err6) = Destinations::activityByRange($db, $member_id, $all_time_days, $country);
			if ($res6 && count($res6)>0) {
				$res = Destinations::uniqueTrips(array_merge($res,$res6));
				if (count($res)>=Destinations::DEFAULT_OPTIONS) {
					return [$res, NULL];
				}
			}
		}
		//*/
		if ($res && count($res)>0) {
			return [$res, NULL];
		}
		return [NULL, "We could not find any recent activity in this area"];
	}

	public static function getAddressActivity($db, $address, $member_id, $time, $time_delta=Destinations::TIME_DELTA) {
		// TODO: radius search

	}
	/*
 id             | bigint                 | not null default nextval('address_id_seq'::regclass)
 address        | character varying(200) |
 latitude       | numeric                |
 longitude      | numeric                |
 timezone       | integer                |
 geocoding_date | date                   |
 postal         | character varying(40)  |
 country        | character varying(2)   | default 'SG'::character varying
 geometry       | geography(Point,4326)  |
 description    | character varying(100) |
 city_id        | integer                |
	*/

	public static function activityByRange($db, $member_id, $days, $country=Destinations::DEFAULT_COUNTRY) {
		$db_time = strtotime($time);
		$db_days = date("w",$db_time) + (int)$days;
		$db_country = pg_escape_string($country);
		$q = "SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm ";
		/////// Start Locations ///////
		$q.= ", c.address AS location_start_address, c.latitude AS location_start_lat, c.longitude AS location_start_lng";
		$q.= ", c.timezone AS location_start_timezone, c.postal AS location_start_postal, c.country AS location_start_country";
		$q.= ", c.description AS location_start_description, c.city_id AS location_start_city_id, c.geometry AS location_start_geometry ";
		$q.= ", d.address AS location_end_address, d.latitude AS location_end_lat, d.longitude AS location_end_lng";
		$q.= ", d.timezone AS location_end_timezone, d.postal AS location_end_postal, d.country AS location_end_country";
		$q.= ", d.description AS location_end_description, d.city_id AS location_end_city_id, d.geometry AS location_end_geometry ";
		/////// End Locations ///////
		$q.= " FROM trackedemail_item a, parsedemail_item b ";
		$q.= " LEFT JOIN address c ON (c.id=b.location_start_id) ";
		$q.= " LEFT JOIN address d ON (d.id=b.location_end_id) ";
 		$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".((int)$member_id);
		$q.= " AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days') ";
		$q.= " AND (c.country='${db_country}' OR d.country='${db_country}') ";  
 		$q.= " ORDER BY b.travel_date DESC LIMIT ".Destinations::DEFAULT_OPTIONS;
 		syslog(LOG_WARNING,$q);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$result = [];
			while ($f=pg_fetch_assoc($r)) {
      	$f["location_start_address"] = html_entity_decode ($f["location_start_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
      	$f["location_end_address"] = html_entity_decode ($f["location_end_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
				$result[] = $f;
			}
			return [$result, NULL];
		}
		return [NULL,pg_last_error()];
	}

	public static function activityByRangeAndRadius($gpsdb, $db, $member_id, $days, $lat, $lng, $radius, $what) {
		syslog(LOG_WARNING,"Activity::activityByRangeAndRadius(\$gpsdb, \$db, $member_id, $days, $lat, $lng, $radius, $what)");
		$limit_lat = (float)$lat;
		$limit_lng = (float)$lng;
		$limit_rad = $radius;
		$db_days = (int)$days;
		$q = "SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm ";
		/////// Start Locations ///////
		$q.= ", c.address AS location_start_address, c.latitude AS location_start_lat, c.longitude AS location_start_lng";
		$q.= ", c.timezone AS location_start_timezone, c.postal AS location_start_postal, c.country AS location_start_country";
		$q.= ", c.description AS location_start_description, c.city_id AS location_start_city_id, c.geometry AS location_start_geometry ";
		$q.= ", d.address AS location_end_address, d.latitude AS location_end_lat, d.longitude AS location_end_lng";
		$q.= ", d.timezone AS location_end_timezone, d.postal AS location_end_postal, d.country AS location_end_country";
		$q.= ", d.description AS location_end_description, d.city_id AS location_end_city_id, d.geometry AS location_end_geometry ";
		/////// End Locations ///////
		$q.= " FROM trackedemail_item a, parsedemail_item b ";
		$q.= " LEFT JOIN address c ON (c.id=b.location_start_id) ";
		$q.= " LEFT JOIN address d ON (d.id=b.location_end_id) ";
		$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".((int)$member_id);
		$q.= " AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days') ";
		//$q.= " AND (b.location_start_id IN (%d,%d) OR b.location_end_id IN (%d,%d)) "; 
		$q.= " AND (";
		$q.= "ST_DWithin(c.geometry,ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";
		$q.= " OR ";
		$q.= "ST_DWithin(d.geometry,ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";
		$q.= ") ORDER BY b.travel_date DESC LIMIT ".Destinations::DEFAULT_OPTIONS;
		syslog(LOG_WARNING,$q);
		$r = pg_query($db, $q);		
		if ($r && pg_num_rows($r)) {
			$cache_from = [];
			$cache_to = [];
			$result = [];
			while ($f = pg_fetch_assoc($r)) {
				if (array_key_exists($f["location_start_geometry"],$cache_from) && $f["location_end_geometry"]==$cache_from[$f["location_start_geometry"]]) {
					continue; // We have similar trip in result set - let's skip it
				}
				if (array_key_exists($f["location_end_geometry"],$cache_to) && $f["location_start_geometry"]==$cache_to[$f["location_end_geometry"]]) {
					continue; // We have similar trip in result set - let's skip it
				}
        $f["location_start_address"] = html_entity_decode ($f["location_start_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
        $f["location_end_address"] = html_entity_decode ($f["location_end_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
				$result[] = $f;
				$cache_from[$f["location_start_geometry"]] = $f["location_end_geometry"];
				$cache_to[$f["location_end_geometry"]] = $f["location_start_geometry"];
			}
			unset($cache_from); // clear
			unset($cache_to); // clear
			return [$result, NULL];
		}
		return [NULL,pg_last_error()];
	}

	public static function activityByRangeAndTimeAndRadius($gpsdb, $db, $member_id, $days, $lat, $lng, $radius, $what, $time, $time_delta=Destinations::TIME_DELTA) {
		syslog(LOG_WARNING,"Activity::activityByRangeAndTimeAndRadius(\$gpsdb, \$db, $member_id, $days, $lat, $lng, $radius, $what, $time, $time_delta)");
		$limit_lat = (float)$lat;
		$limit_lng = (float)$lng;
		$limit_rad = $radius;
		$db_time = strtotime($time);
		$db_days = date("w",$db_time) + (int)$days;
		$db_time_delta = (int)$time_delta;
		syslog(LOG_WARNING,"${db_time}=".date("Y-m-d H:i",$db_time));
		// Ranges
		$dh = 60*date("G",$db_time)+date("i",$db_time);
		$d1 = $dh - $db_time_delta;
		$d2 = $dh + $db_time_delta;
		syslog(LOG_WARNING,"${d1} <= x <= ${d2}");
		$q = "SELECT e.* FROM (SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm ";
		/////// Start Locations ///////
		$q.= ", c.address AS location_start_address, c.latitude AS location_start_lat, c.longitude AS location_start_lng";
		$q.= ", c.timezone AS location_start_timezone, c.postal AS location_start_postal, c.country AS location_start_country";
		$q.= ", c.description AS location_start_description, c.city_id AS location_start_city_id, c.geometry AS location_start_geometry ";
		$q.= ", d.address AS location_end_address, d.latitude AS location_end_lat, d.longitude AS location_end_lng";
		$q.= ", d.timezone AS location_end_timezone, d.postal AS location_end_postal, d.country AS location_end_country";
		$q.= ", d.description AS location_end_description, d.city_id AS location_end_city_id, d.geometry AS location_end_geometry ";
		/////// End Locations ///////
		$q.= " FROM trackedemail_item a, parsedemail_item b ";
		$q.= " LEFT JOIN address c ON (c.id=b.location_start_id) ";
		$q.= " LEFT JOIN address d ON (d.id=b.location_end_id) ";
		$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".((int)$member_id);
		$q.= " AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days') ";
		$q.= " AND (";
		$q.= "ST_DWithin(c.geometry,ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";
		$q.= " OR ";
		$q.= "ST_DWithin(d.geometry,ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";		
		$q.= ")) AS e ";
		$q.= " WHERE e.dm>=${d1} AND e.dm<=${d2} ";
		$q.= " ORDER BY e.travel_date DESC LIMIT ".Destinations::DEFAULT_OPTIONS;
		syslog(LOG_WARNING,$q);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			syslog(LOG_WARNING,"parsedemail_item(s) to process: ".pg_num_rows($r));
			$cache_from = [];
			$cache_to = [];
			$result = [];
			while ($f=pg_fetch_assoc($r)) {
				if (array_key_exists($f["location_start_geometry"],$cache_from) && $f["location_end_geometry"]==$cache_from[$f["location_start_geometry"]]) {
					continue; // We have similar trip in result set - let's skip it
				}
				if (array_key_exists($f["location_end_geometry"],$cache_to) && $f["location_start_geometry"]==$cache_to[$f["location_end_geometry"]]) {
					continue; // We have similar trip in result set - let's skip it
				}
        $f["location_start_address"] = html_entity_decode ($f["location_start_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
        $f["location_end_address"] = html_entity_decode ($f["location_end_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
				$result[] = $f;
			}
			unset($cache_from); // clear
			unset($cache_to); // clear
			return [$result, NULL];
		}
		return [NULL,pg_last_error()];
	}

	public static function activityByRangeAndTime($db, $member_id, $days, $time, $time_delta=Destinations::TIME_DELTA, $country=Destinations::DEFAULT_COUNTRY) {
		syslog(LOG_WARNING,"Activity::activityByRangeAndTime(\$db, $member_id, $days, $time, $time_delta");
		$db_time = strtotime($time);
		$db_days = date("w",$db_time) + (int)$days;
		$db_time_delta = (int)$time_delta;
		$db_country = pg_escape_string($country);
		syslog(LOG_WARNING,"${db_time}=".date("Y-m-d H:i",$db_time));
		// Ranges
		$dh = 60*date("G",$db_time)+date("i",$db_time);
		$d1 = $dh - $db_time_delta;
		$d2 = $dh + $db_time_delta;
		syslog(LOG_WARNING,"${d1} <= x <= ${d2}");
		$q = "SELECT e.* FROM (SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm ";
		/////// Start Locations ///////
		$q.= ", c.address AS location_start_address, c.latitude AS location_start_lat, c.longitude AS location_start_lng";
		$q.= ", c.timezone AS location_start_timezone, c.postal AS location_start_postal, c.country AS location_start_country";
		$q.= ", c.description AS location_start_description, c.city_id AS location_start_city_id, c.geometry AS location_start_geometry ";
		$q.= ", d.address AS location_end_address, d.latitude AS location_end_lat, d.longitude AS location_end_lng";
		$q.= ", d.timezone AS location_end_timezone, d.postal AS location_end_postal, d.country AS location_end_country";
		$q.= ", d.description AS location_end_description, d.city_id AS location_end_city_id, d.geometry AS location_end_geometry ";
		/////// End Locations ///////
		$q.= " FROM trackedemail_item a, parsedemail_item b ";
		$q.= " LEFT JOIN address c ON (c.id=b.location_start_id) ";
		$q.= " LEFT JOIN address d ON (d.id=b.location_end_id) ";
 		$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".((int)$member_id);
		$q.= " AND (c.country='${db_country}' OR d.country='${db_country}') ";
 		$q.= " AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days')) AS e ";
		$q.= " WHERE e.dm>=${d1} AND e.dm<=${d2} ";
		$q.= " ORDER BY e.travel_date DESC LIMIT ".Destinations::DEFAULT_OPTIONS;
 		syslog(LOG_WARNING,$q);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$result = [];
			while ($f=pg_fetch_assoc($r)) {
        $f["location_start_address"] = html_entity_decode ($f["location_start_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
        $f["location_end_address"] = html_entity_decode ($f["location_end_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
				$result[] = $f;
			}
			return [$result, NULL];
		}
		return [NULL,pg_last_error()];
	}

	protected static function getAllTimeDays() {
		$datediff = time() - Destinations::TIME_LIMIT;
		return round($datediff / (60 * 60 * 24));
	}

	protected static function uniqueTrips($res) {
		$cache = [];
		$result = [];
		foreach ($res as $trip) {
			$key1ids = $trip["location_start_id"]."_".$trip["location_end_id"];
      $key1gps = $trip["location_start_lat"]."_".$trip["location_start_lng"]."_".$trip["location_end_lat"]."_".$trip["location_end_lng"];
			$key2ids = $trip["location_end_id"]."_".$trip["location_start_id"];
			$key2gps = $trip["location_end_lat"]."_".$trip["location_end_lng"]."_".$trip["location_start_lat"]."_".$trip["location_start_lng"];
			if (array_key_exists($key1ids, $cache) || array_key_exists($key2ids, $cache)) {
				continue;
			}
			if (array_key_exists($key1gps, $cache) || array_key_exists($key2gps, $cache)) {
        continue;
			}
			$cache[$key1ids] = $key2ids;
			$cache[$key1gps] = $key2gps;
			$result[] = $trip;
		}
		return $result;
	}
}
// vi:ts=2