<?php

class Trips {

	public function getAll($db, $members_id, $date1, $date2, $limit, $offset) {
		$result = array();
		$total = 0;

		$q = "SELECT count(*) FROM trackedemail_item, parsedemail_item ";
		$q.= " WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id AND trackedemail_item.member_id IN (${members_id})";
		$q.= " AND ((parsedemail_item.travel_date BETWEEN '${date1}' AND '${date2}') OR (parsedemail_item.travel_date_end BETWEEN '${date1}' AND '${date2}')) ";
		//$q.= " AND parsedemail_item.location_start_tz='Asia/Singapore';";
		$q.= " AND parsedemail_item.dup_id IS NULL";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
			$total = $f[0];
		}
		if ($total==0) {
			return array(0,null);
		}
		$q = "SELECT parsedemail_item.*,parsedemail_item_advice_google.id AS gid,parsedemail_item_advice_google.routes,trackedemail_item.id as tid,trackedemail_item.member_id ";
		$q.= " FROM trackedemail_item, parsedemail_item ";
		$q.= " LEFT JOIN parsedemail_item_advice_google ON parsedemail_item.id=parsedemail_item_advice_google.parsedemail_item_id ";
		$q.= " WHERE trackedemail_item.id=parsedemail_item.trackedemail_item_id AND trackedemail_item.member_id IN (${members_id})";
		$q.= " AND ((parsedemail_item.travel_date BETWEEN '${date1}' AND '${date2}') OR (parsedemail_item.travel_date_end BETWEEN '${date1}' AND '${date2}')) ";
		//$q.= " AND parsedemail_item.location_start_tz='Asia/Singapore';";
		$q.= " AND parsedemail_item.dup_id IS NULL";
		$q.= " ORDER BY parsedemail_item.travel_date DESC, parsedemail_item.travel_date_end DESC";
		$q.= " LIMIT ${limit} OFFSET ${offset}";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			while($f=pg_fetch_assoc($r)) {
				$result[] = $f;
			}
		}
		return array($total, $result);
	}

	public function getById($db, $id) {
		$result = array();
		$q = "SELECT a.*,
		b.address AS location_start, b.latitude AS location_start_lat, b.longitude AS location_start_lng,
		c.address AS location_end, c.latitude AS location_end_lat, c.longitude AS location_end_lng,
		a.id AS parsedemail_item_id 
		FROM parsedemail_item a 
		LEFT JOIN address b ON (b.id=a.location_start_id) 
		LEFT JOIN address c ON (c.id=a.location_end_id)
		WHERE a.id=${id}";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			$f["location_start"] = html_entity_decode ($f["location_start"],ENT_QUOTES|ENT_HTML5,"UTF-8");
			$f["location_end"] = html_entity_decode ($f["location_end"],ENT_QUOTES|ENT_HTML5,"UTF-8");
			$result = $f;
		}
		return $result;
	}

	public function getByLocations($db, $origin, $destination, $limit=1) {
		$result = array();
		$db_lat = is_numeric($origin["lat"]) ? $origin["lat"] : 0;
		$db_lng = is_numeric($origin["lng"]) ? $origin["lng"] : 0;
		$db_destination = pg_escape_string(strtolower($destination));
		$q = "SELECT a.*,
			b.address AS location_start, b.latitude AS location_start_lat, b.longitude AS location_start_lng,
			c.address AS location_end, c.latitude AS location_end_lat, c.longitude AS location_end_lng,
			a.id AS parsedemail_item_id 
			FROM parsedemail_item a 
			LEFT JOIN address b ON (b.id=a.location_start_id) 
			LEFT JOIN address c ON (c.id=a.location_end_id) 
			WHERE ROUND(b.latitude,3)=ROUND(${db_lat},3) AND ROUND(b.longitude,3)=ROUND(${db_lng},3)
			AND LOWER(c.address) LIKE '%${db_destination}%' ORDER BY a.travel_date_end DESC LIMIT ${limit}";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			if ($limit==1) {
				$f = pg_fetch_assoc($r);
      	$f["location_start"] = html_entity_decode ($f["location_start"],ENT_QUOTES|ENT_HTML5,"UTF-8");
      	$f["location_end"] = html_entity_decode ($f["location_end"],ENT_QUOTES|ENT_HTML5,"UTF-8");
				return $f;
			}
			$result = [];
			while ($f = pg_fetch_assoc($r)) {
      	$f["location_start"] = html_entity_decode ($f["location_start"],ENT_QUOTES|ENT_HTML5,"UTF-8");
      	$f["location_end"] = html_entity_decode ($f["location_end"],ENT_QUOTES|ENT_HTML5,"UTF-8");
				$result[] = $f;
			}
		}
		return $result;
	}

	public function getByCoordinates($db, $origin, $destination, $limit=1) {
		$result = array();
		$dbo_lat = is_numeric($origin["lat"]) ? $origin["lat"] : 0;
		$dbo_lng = is_numeric($origin["lng"]) ? $origin["lng"] : 0;
		$dbd_lat = is_numeric($destination["lat"]) ? $destination["lat"] : 0;
		$dbd_lng = is_numeric($destination["lng"]) ? $destination["lng"] : 0;
		$q = "SELECT a.*,
 b.address AS location_start, b.latitude AS location_start_lat, b.longitude AS location_start_lng,
 c.address AS location_end, c.latitude AS location_end_lat, c.longitude AS location_end_lng,
 a.id AS parsedemail_item_id 
 FROM parsedemail_item a 
 LEFT JOIN address b ON (b.id=a.location_start_id) 
 LEFT JOIN address c ON (c.id=a.location_end_id) 
 WHERE ROUND(b.latitude,3)=ROUND(${dbo_lat},3) AND ROUND(b.longitude,3)=ROUND(${dbo_lng},3)
 AND ROUND(c.latitude,3)=ROUND(${dbd_lat},3) AND ROUND(c.longitude,3)=ROUND(${dbd_lng},3) 
 ORDER BY a.travel_date_end DESC LIMIT ${limit}";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			if ($limit==1) {
				$f = pg_fetch_assoc($r);
      	$f["location_start"] = html_entity_decode ($f["location_start"],ENT_QUOTES|ENT_HTML5,"UTF-8");
      	$f["location_end"] = html_entity_decode ($f["location_end"],ENT_QUOTES|ENT_HTML5,"UTF-8");
				return $f;
			}
			$result = [];
			while ($f = pg_fetch_assoc($r)) {
				$f["location_start"] = html_entity_decode ($f["location_start"],ENT_QUOTES|ENT_HTML5,"UTF-8");
      	$f["location_end"] = html_entity_decode ($f["location_end"],ENT_QUOTES|ENT_HTML5,"UTF-8");
				$result[] = $f;
			}
		}
		return $result;
	}

	public function getOptionsById($db, $id) {
		syslog(LOG_WARNING,"Trips::getOptionsById(\$db, $id)");
		$result = array("routes" => 0, "gid" => 0);
		$q = "SELECT id AS gid, routes FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id}";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			$result = $f;
		}
		if ($result["routes"]>0 && $result["gid"]>0) {
			$result["legs"] = array();
		} else {
			return NULL; // Data not found
		}
		$timezones = MultiModal::getLegTimezonesForAdvice($db, $result["gid"]);
		/* $q = "SELECT a.*,b.timezone AS departure_timezone,c.timezone AS arrival_timezone FROM google_directions_legs a ";
		$q.= " LEFT JOIN address_timezone b ON b.id=CAST(a.departure_time_zone AS INTEGER) ";
		$q.= " LEFT JOIN address_timezone c ON c.id=CAST(a.arrival_time_zone AS INTEGER) ";
		$q.= " WHERE a.parsedemail_item_advice_google_id=".$result["gid"]; */
		$q = "SELECT * FROM google_directions_legs WHERE parsedemail_item_advice_google_id=".$result["gid"];
		syslog(LOG_WARNING,$q);
		$r = pg_query($db, $q);
		while ($f=pg_fetch_assoc($r)) {
			unset($f["parsedemail_item_advice_google_id"]); // We have it as $result["gid"]
			if (is_array($timezones) && array_key_exists($f["id"],$timezones)) {
				$f["departure_timezone"] = $timezones[$f["id"]][0];
				$f["arrival_timezone"] = $timezones[$f["id"]][1];
			}
			$result["legs"][] = $f;
			$q1 = "SELECT a.id AS sid,a.*,b.* FROM google_directions_leg_steps a LEFT JOIN google_directions_leg_step_details b ON a.id=b.google_directions_leg_step_id ";
			$q1.= " WHERE a.google_directions_leg_id=".$f["id"];
			$r1 = pg_query($db, $q1);
			if ($r1 && pg_num_rows($r1)) {
				$result["leg_steps"][$f["id"]] = array();
				$result["leg_fare"][$f["id"]] = 0;
			}
			$fare = 0; $i = 0;
			while ($f1=pg_fetch_assoc($r1)) {
				if (array_key_exists("polyline",$f1) && $f1["polyline"]=="") {
					$f1["polyline"] = MultiModal::getPolyline($db, $f1);
				}
				unset($f1["google_directions_leg_id"]);
				unset($f1["google_directions_leg_step_id"]);
				foreach ($f1 as $key=>$val) {
					if ($val===NULL) unset($f1[$key]);
				}
				$q2 = "SELECT * FROM leg_step_quote WHERE google_directions_leg_step_id=".$f1["sid"];
				$r2 = pg_query($db, $q2);
				if ($r2 && pg_num_rows($r2) && $f2 = pg_fetch_assoc($r2)) {
					syslog(LOG_WARNING,$q2);
					if (is_array($f2) && isset($f2["fare"]) && $f2["fare"]!="") {
						$fare += $f2["fare_raw"];
					}
					$i++;
				}
				//$f1["q"] = $q2;
				$result["leg_steps"][$f["id"]][] = $f1;
			}
			if ($fare==0) $fare = $f["fare_raw"];
			$result["leg_fare"][$f["id"]] = $fare;
		}
		ActivityApi::debugTrips([['multimodal'=>['options'=>$result]]],'22222222');
		return $result;
	}

	public function create($db, $data) {
		$fields = array(
			'travel_date' => false,
			'duration' => false,
			'cost_raw' => false,
			'trackedemail_item_id' => false,
			'cost' => false,
			'updated' => false,
			'distance' => false,
			'transport_provider_id' => false,
			'scheduled' => false,
			'travel_date_end' => false,
			'dup_id' => false,
			'location_start_id' => true,
			'location_end_id' => true,
			'private' => false
		);
		$field_key_list = [];
		$field_val_list = [];
		foreach ($fields as $key=>$required) {
			if ($required && !isset($data[$key])) {
				return [NULL, "Missing required field '${key}'"];
			}
			if (!isset($data[$key])) continue;
			$field_key_list[] = "${key}";
			$field_val_list[] = (($data[$key]==NULL || $data[$key]=="")?"NULL":"'".pg_escape_string($data[$key])."'");
		}
		$q = "INSERT INTO parsedemail_item (".implode(",",$field_key_list).")
				 VALUES(".implode(",",$field_val_list).") RETURNING id";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$f = pg_fetch_row($r);
			return [$f[0], NULL];
		}
		return [NULL, pg_last_error($db)];
	}

	public function tripByLocations($db, $location_start_id, $location_end_id) {
		$q = "SELECT * FROM parsedemail_item WHERE location_start_id=${location_start_id} AND location_end_id=${location_end_id}";
		$q.= " AND dup_id IS NULL ORDER BY travel_date DESC LIMIT 1";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			return [$f, NULL];
		}
		return [NULL, pg_last_error()];
	}
}

// vi:ts=2