<?php

class Popular {

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

	const POPULAR_RADIUS = 5000; // 5km

	public function getPopular($db, $gpsdb, $member_id, $lat, $lng, $time, $time_delta=self::TIME_DELTA, $radius=self::POPULAR_RADIUS, $country=self::DEFAULT_COUNTRY) {
		error_log('Popular::getPopular()');
		if ($member_id<1) {
			$loc = self::popularRadiusLocationsMock();
		} else {
			list($loc, $err) = self::popularRadiusLocations($gpsdb, $lat, $lng, $radius, 'start');
		}
		error_log('popularRadiusLocations='.($loc==NULL?"NULL":count($loc)));
		if (!is_array($loc) || count($loc)<1) {
			return [NULL, $err];
		}
		$result = [];
		foreach ($loc as $f) { // location_end, location_start
			$id = self::getPopularTripsID($gpsdb, $f["location_start"], $f["location_end"]);
			if ($id<1) continue;
			$q = "SELECT * FROM parsedemail_item WHERE id=(SELECT dup_id FROM parsedemail_item WHERE id=${id}) UNION SELECT * FROM parsedemail_item WHERE id=${id} ORDER BY dup_id DESC";
			$r = pg_query($db, $q);
			if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
				$result[] = $f;
			}
		}
		if (count($result)) {
			return [$result, NULL];
		}
		return [NULL, "No popular trips nearby found"];
	}

	public function getPopularTripsID($db, $location_start, $location_end) {
		$q = "SELECT id FROM parsedemail_item WHERE location_start='${location_start}' AND location_end='${location_end}' ORDER BY id DESC LIMIT 1";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
			return $f[0];
		}
		return 0;
	}

	public static function popularRadiusLocations($db, $lat, $lng, $radius, $what) {
		$limit_lat = (float)$lat;
		$limit_lng = (float)$lng;
		$limit_rad = $radius;
		$q = "SELECT location_end, location_start, count(*) AS num FROM parsedemail_item WHERE ";
		$q.= "ST_DWithin(location_${what},ST_SetSRID(ST_MakePoint(${limit_lng},${limit_lat}),4326)::geography,${limit_rad})";
		$q.= " GROUP BY location_end, location_start ORDER BY count(*) DESC";
		error_log($q);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$result = [];
			while ($f=pg_fetch_assoc($r)) {
				$result[] = $f;
			}
			return [$result, NULL];
		}
		return [NULL,pg_last_error()];
	}

	/*
	public function popularByRange($db, $member_id, $days, $ids) {
		$db_days = date("w",$db_time) + (int)$days;
		$q = "SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm 
 FROM trackedemail_item a, parsedemail_item b
 WHERE a.id=b.trackedemail_item_id AND a.member_id<>".((int)$member_id)."
 AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days')
 AND b.id IN (".implode(",",$ids).") 
 ORDER BY b.travel_date DESC";
		file_put_contents("/home/savvy/tmp/popularByRange-${member_id}-${days}.sql",$q);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$result = [];
			while ($f=pg_fetch_assoc($r)) {
				$result[] = $f;
			}
			return [$result, NULL];
		}
		return [NULL,pg_last_error()];
	}

	public function popularByRangeAndTime($db, $member_id, $days, $ids, $time, $time_delta=self::TIME_DELTA) {
		$db_time = strtotime($time);
		$db_days = date("w",$db_time) + (int)$days;
		$db_time_delta = (int)$time_delta;
		// Ranges
		$dh = 60*date("G")+date("i");
		$d1 = $dh - $db_time_delta;
		$d2 = $dh + $db_time_delta;
		$q = "SELECT c.* FROM (SELECT b.*, 60*date_part('hour', b.travel_date)+date_part('minute',b.travel_date) AS dm 
 FROM trackedemail_item a, parsedemail_item b
 WHERE a.id=b.trackedemail_item_id AND a.member_id<>".((int)$member_id)."
 AND b.dup_id IS NULL AND b.travel_date_end > (now() - interval '${db_days} days') 
 AND b.id IN (".implode(",",$ids).")) AS c
 WHERE c.dm>=${d1} AND c.dm<=${d2} ORDER BY c.travel_date DESC";
 file_put_contents("/home/savvy/tmp/popularByRangeAndTime-${member_id}-${days}.sql",$q);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$result = [];
			while ($f=pg_fetch_assoc($r)) {
				$result[] = $f;
			}
			return [$result, NULL];
		}
		return [NULL,pg_last_error()];
	}
	*/
	protected  function popularRadiusLocationsMock() {
		return [
			[
				"location_end" => "0101000020E6100000892650C4A2985EC0C029070EC3CE4240", 
				"location_start" => "0101000020E61000004A5B012A419E5EC066A4390DACE34240",
				"num" => 100
			],
			[
				"location_end" => "0101000020E61000007F3CAA5013995EC0A6D590B8C7CE4240", 
				"location_start" => "0101000020E61000004A5B012A419E5EC066A4390DACE34240",
				"num" => 50
			],
			[
				"location_end" => "0101000020E610000023707F89D3985EC098A02BB6EECE4240", 
				"location_start" => "0101000020E61000008B02D8DB419E5EC03360DA8184E34240",
				"num" => 10
			]
		];
	}
}
// vi:ts=2