<?php

class MultiModal {

	public function getLegById($db, $id) {
		syslog(LOG_WARNING,"MultiModal::getLegById(\$db, $id)");
		list($departure_time_zone,$arrival_time_zone) = self::getLegTimezones($db, $id);
		$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=${departure_time_zone} ";
		$q.= " LEFT JOIN address_timezone c ON c.id=${arrival_time_zone} ";
		$q.= " WHERE a.id=".((int)$id);
		syslog(LOG_WARNING,$q);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			return [$f, NULL];
		}
		return [NULL, pg_last_error($db)];
	}

	public function getAdviceLegs($db, $advice_id) {
		syslog(LOG_WARNING,"MultiModal::getAdviceLegs(\$db, $advice_id)");
		list($departure_time_zone,$arrival_time_zone) = self::getLegTimezones($db, $advice_id);
		$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=${departure_time_zone} ";
		$q.= " LEFT JOIN address_timezone c ON c.id=${arrival_time_zone} ";
		$q.= " WHERE a.parsedemail_item_advice_google_id=".((int)$advice_id);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$result = [];
			while ($f=pg_fetch_assoc($r)) {

				$result[$f['id']] = $f;
			}
			return [$result, NULL];
		}
		return [NULL, pg_last_error($db)];
	}

	public function getLegSteps($db, $id) {
		$result = NULL;
		$q = "SELECT a.id AS sid, a.*,b.*,c.* FROM google_directions_leg_steps a ";
		$q.= " LEFT JOIN google_directions_leg_step_details b ON (b.google_directions_leg_step_id=a.id) ";
		$q.= " LEFT JOIN leg_step_quote c ON (c.google_directions_leg_step_id=a.id) ";
		$q.= " WHERE a.google_directions_leg_id=".((int)$id);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			while ($f=pg_fetch_assoc($r)) {
				if ($f["polyline"]=="") {
					$f["polyline"] = self::getPolyline($db, $f);
				}
				$result[] = $f;
			}
			return [$result, NULL];
		}
		return [NULL, pg_last_error($db)];
	}

	public function getStepQuote($db, $id) {
		$q = "SELECT * FROM leg_step_quote WHERE google_directions_leg_step_id=${id} ORDER BY id DESC LIMIT 1";
		error_log($q);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			return [$f, NULL];
		}
		return [NULL, pg_last_error($db)];
	}

	public function getLegAdviceGoogleById($db, $id) {
		syslog(LOG_WARNING,"MultiModal::getLegAdviceGoogleById(\$db, $id)");
		$q = "SELECT * FROM parsedemail_item_advice_google WHERE id=${id}";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			return [$f, NULL];
		}
		return [NULL, pg_last_error($db)];
	}

	public function getLastGoogleAdviceByTrip($db, $id) {
		$q = "SELECT *,EXTRACT(EPOCH FROM (NOW()-updated)) AS elapsed FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id} ORDER BY updated 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($db)];
	}

	public function getTimezoneById($db, $id) {
		$q = "SELECT timezone FROM address_timezone WHERE id=".((int)$id);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			return $f;
		}
		return $id;
	}

	public function create($db, $trip) {
		list ($id, $err) = self::saveParsedemailItemAdviceGoogle($db, [
			'parsedemail_item_id' => $trip['id'],
			'routes' => count($trip['options']['legs'])
		]);
		if ($id && $id>0) {
			$trip['options']['gid'] = $id;
			error_log('New parsedemail_item_advice_google.id='.$id);
		} else {
			return [NULL, 'Failed to create advice record'];
		}
		$timezones = [];
		$n = count($trip['options']['legs']);
		for ($i=0; $i<$n; $i++) {
			$leg = $trip['options']['legs'][$i];
			$leg_id = $leg['id'];
            $leg_steps = $trip['options']['leg_steps'][$leg_id];
			$leg_fare = $trip['options']['leg_fare'][$leg_id];

			// Timezones
			foreach(array("departure","arrival") as $tzid) {
				$key1 = "${tzid}_time_zone";
				$key2 = "${tzid}_timezone";
				if (array_key_exists($leg[$key1],$timezones)) {
					$leg[$key2] = $timezones[$leg[$key1]];
				} else {
					$tz = self::getTimezoneById($db, $leg[$key1]);
					$timezones[$leg[$key1]] = $tz;
					$leg[$key2] = $tz;
				}
			}

			//error_log(json_encode($leg));
			list ($id, $err) = self::saveGoogleDirectionsLeg($db, [
				'parsedemail_item_advice_google_id' => $trip['options']['gid'],
				'arrival_time' => $leg["arrival_time"],
				'arrival_time_zone' => $leg["arrival_time_zone"],
				'departure_time' => $leg["departure_time"],
				'departure_time_zone' => $leg["departure_time_zone"],
				'distance' => $leg["distance"],
				'duration' => $leg["duration"],
				'steps' => $leg["steps"],
				'fare_raw' => $leg["fare_raw"],
				'polyline' => self::processPolyline($leg["polyline"])
			]);
			if ($id && $id>0) {
				$old_leg_id = $leg_id;
				$leg_id = $id;
				$leg['parsedemail_item_advice_google_id'] = $trip['options']['gid'];
				// Legs
				$leg['id'] = $id;
				$trip['options']['legs'][$i] = $leg;
				// Leg steps
				unset($trip["options"]["leg_steps"][$old_leg_id]);
				$trip['options']['leg_steps'][$leg_id] = $leg_steps;
				// Leg fare
				unset($trip['options']['leg_fare'][$old_leg_id]);
				$trip['options']['leg_fare'][$leg_id] = $leg_fare;
				// ...
				error_log('New google_directions_legs.id='.$id);
			} else {
				self::deleteAdviceGoogle($db, $trip['options']['gid']);
				return [NULL, 'Failed to create leg record: '.$err];
			}
			$m = count($leg_steps);
			for ($j=0; $j<$m; $j++) {
				$step = $leg_steps[$j];
				$data = [
					'google_directions_leg_id' => $leg_id,
					'distance' => $step["distance"],
					'duration' => $step["duration"],
					'travel_mode' => $step["travel_mode"],
					'location_start_lat' => $step["location_start_lat"],
					'location_start_lng' => $step["location_start_lng"],
					'location_end_lat' => $step["location_end_lat"],
					'location_end_lng' => $step["location_end_lng"],
					'html_instructions' => $step["html_instructions"],
					'polyline' => self::processPolyline($step["polyline"])
				];
				list($id, $err) = self::saveGoogleDirectionsLegStep($db, $data);
				if ($id && $id>0) {
					$sid = $id;
					$step['sid'] = $id;
					$leg_steps[$j] = $step;
					if ($data['polyline']=="") { // We do not have a polyline - try get one...
						$data['id'] = $id;
						$data['sid'] = $sid;
						$polyline = self::getPolyline($db, $data);
					}
					error_log('New google_directions_leg_steps.id='.$sid);
				} else {
					self::deleteAdviceGoogle($db, $trip['options']['gid']);
					return [NULL, 'Failed to create leg step record'];
				}
				if (count($step)>20) {
					list($id, $err) = self::saveGoogleDirectionsLegStepDetail($db, [
						'google_directions_leg_step_id' => $sid,
						'num_stops' => $step["num_stops"],
						'line' => $step["line"],
						'vehicle' => $step["vehicle"],
						'departure_stop' => $step["departure_stop"],
						'departure_stop_lat' => $step["departure_stop_lat"],
						'departure_stop_lng' => $step["departure_stop_lng"],
						'arrival_stop' => $step["arrival_stop"],
						'arrival_stop_lat' => $step["arrival_stop_lat"],
						'arrival_stop_lng' => $step["arrival_stop_lng"],
						'headsign' => $step["headsign"],
						'headway' => $step["headway"]
					]);
					if ($id && $id>0) {
						$step['id'] = $id;
						$leg_steps[$j] = $step;
						error_log('New google_directions_leg_step_details.id='.$id);
						error_log(json_encode($step));
						if (array_key_exists("fare_raw",$step)) {
							self::saveLegStepQuote($db, [
								'google_directions_leg_step_id' => $sid,
								'name' => $step["line"],
								'service' => $step["agency"],
								'board' =>  $step["departure_stop"],
								'alight' => $step["arrival_stop"],
								'distance' => sprintf("%0.02f",$step["distance"]/1000),
								'fare' => $step["fare"],
								'fare_raw' => $step["fare_raw"],
								'distance_raw' => $step["distance"]
							]);
						}
					} else {
						self::deleteAdviceGoogle($db, $trip['options']['gid']);
						return [NULL, 'Failed to create leg step detail record'];
					}
				} else {
					error_log('Step #'.$sid.' does not have details');
				}
			} // for
			$trip['options']['leg_steps'][$leg_id] = $leg_steps;
		} // for
		return [$trip, NULL];
	}

	public function getLegStepQuotes($db, $id) {
		$q = "SELECT * FROM leg_step_quote WHERE google_directions_leg_step_id ";
		$q.= " IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id=${id})";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$quotes = [];
			while ($f=pg_fetch_assoc($r)) {
				$quotes[] = $f;
			}
			return [$quotes, NULL];
		}
		return [NULL, pg_last_error($db)];
	}

	public function delete($db, $id) {
		$result = 0;
		$q = "DELETE FROM leg_step_quote WHERE google_directions_leg_step_id ";
		$q.= " IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id ";
		$q.= " IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id "; 
		$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id})))";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		//error_log(pg_last_error());
		$q = "DELETE FROM google_directions_leg_step_details WHERE google_directions_leg_step_id ";
		$q.= " IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id ";
		$q.= " IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id "; 
		$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id})))";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		//error_log(pg_last_error());
		$q = "DELETE FROM google_directions_leg_steps WHERE google_directions_leg_id ";
		$q.= " IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id ";
		$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id}))";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		//error_log(pg_last_error());
		$q = "DELETE FROM google_directions_legs WHERE parsedemail_item_advice_google_id ";
		$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id})";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		//error_log(pg_last_error());
		$q = "DELETE FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id}";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		//error_log(pg_last_error());
		$q = "DELETE FROM parsedemail_item WHERE id=${id}";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		//error_log(pg_last_error());
		return $result;
	}

	public function deleteAdviceGoogle($db, $id) {
		$result = 0;
		$q = "DELETE FROM leg_step_quote WHERE google_directions_leg_step_id ";
		$q.= " IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id ";
		$q.= " IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id "; 
		$q.= " IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=${id})))";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		$q = "DELETE FROM google_directions_leg_step_details WHERE google_directions_leg_step_id
		IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id
		IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id 
		IN (SELECT id FROM parsedemail_item_advice_google WHERE id=${id})))";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		$q = "DELETE FROM google_directions_leg_steps WHERE google_directions_leg_id
		IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id 
		IN (SELECT id FROM parsedemail_item_advice_google WHERE id=${id}))";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		$q = "DELETE FROM google_directions_legs WHERE parsedemail_item_advice_google_id 
		IN (SELECT id FROM parsedemail_item_advice_google WHERE id=${id})";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		$q = "DELETE FROM parsedemail_item_advice_google WHERE id=${id}";
		$r = pg_query($db, $q);
		if ($r && pg_affected_rows($r)) {
			$result++;
		}
		return $result;
	}

	public function saveParsedemailItem($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
		);
		return self::saveGeneric($db, $fields, $data, 'parsedemail_item');
	}

	public function saveParsedemailItemAdviceGoogle($db, $data) {
		$fields = array(
			'parsedemail_item_id' => true,
			'routes' => true
		);
		return self::saveGeneric($db, $fields, $data, 'parsedemail_item_advice_google');
	}

	public function saveGoogleDirectionsLeg($db, $data) {
		$fields = array(
			'parsedemail_item_advice_google_id' => true,
			'arrival_time' => false,
			'arrival_time_zone' => false,
			'departure_time' => false,
			'departure_time_zone' => false,
			'distance' => false,
			'duration' => false,
			'steps' => false,
			'fare_raw' => false,
			'polyline' => false
		);
		return self::saveGeneric($db, $fields, $data, 'google_directions_legs');
	}

	public function saveGoogleDirectionsLegStep($db, $data) {
		$fields = array(
			'google_directions_leg_id' => true,
			'distance' => false,
			'duration' => false,
			'travel_mode' => false,
			'location_start_lat' => false,
			'location_start_lng' => false,
			'location_end_lat' => false,
			'location_end_lng' => false,
			'html_instructions' => false,
			'polyline' => false
		);
		return self::saveGeneric($db, $fields, $data, 'google_directions_leg_steps');
	}

	public function saveGoogleDirectionsLegStepDetail($db, $data) {
		$fields = array(
			'google_directions_leg_step_id' => true,
			'num_stops' => false,
			'line' => false,
			'vehicle' => false,
			'departure_stop' => false,
			'departure_stop_lat' => false,
			'departure_stop_lng' => false,
			'arrival_stop' => false,
			'arrival_stop_lat' => false,
			'arrival_stop_lng' => false,
			'headsign' => false,
			'headway' => false
		);
		return self::saveGeneric($db, $fields, $data, 'google_directions_leg_step_details');
	}

	public function saveLegStepQuote($db, $data) {
		error_log('public function saveLegStepQuote($db, $data)');
		$fields = array(
			'google_directions_leg_step_id' => true,
			'name' => false,
			'service' => false,
			'board' =>  false,
			'alight' => false,
			'distance' => false,
			'fare' => false,
			'fare_raw' => true,
			'distance_raw' => false
		);
		return self::saveGeneric($db, $fields, $data, 'leg_step_quote');
	}

	private function saveGeneric($db, $fields, $data, $table) {
		$q = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = '${table}'";
		$r = pg_query($db, $q);
		$columns = [];
		while ($f=pg_fetch_assoc($r)) {
			$columns[$f['column_name']] = $f;
		}
		$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}";
			if (isset($columns[$key]) && ($columns[$key]["data_type"]=='integer' || $columns[$key]["data_type"]=='bigint')) {
				$field_val_list[] = ($data[$key]==NULL || strlen($data[$key])==0)?"NULL":((int)$data[$key]);
			} else {
				$field_val_list[] = (($data[$key]==NULL || $data[$key]=="")?"NULL":"'".pg_escape_string($data[$key])."'");
			}
		}
		$q = "INSERT INTO ${table} (".implode(",",$field_key_list).") VALUES(".implode(",",$field_val_list).") RETURNING id";
		//error_log($q);
		$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 processPolyline($data) {
		if (!is_array($data)) {
			return $data;
		}
		$points = [];
		foreach ($data as $item) {
			$points = array_merge($points, Polyline::decode($item));
		}
		return Polyline::encode($points);
	}

	public function getPolyline($db, $f) {
		$q = "SELECT polyline FROM google_directions_leg_steps WHERE ";
		$q.= " location_start_lat='".$f["location_start_lat"]."' AND ";
		$q.= " location_start_lng='".$f["location_start_lng"]."' AND ";
		$q.= " location_end_lat='".$f["location_end_lat"]."' AND ";
		$q.= " location_end_lng='".$f["location_end_lng"]."' AND ";
		$q.= " polyline<>'' ORDER BY ID desc LIMIT 1";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $polyline=pg_fetch_row($r)) {
			$f["polyline"] = $polyline[0];
			$q = "UPDATE google_directions_leg_steps SET polyline='".pg_escape_string($f["polyline"])."' WHERE id=".$f['sid']." RETURNING *";
			$r = pg_query($db, $q); // Do not quite care...
			return $f["polyline"];
		}
		$res = MultiModalApi::getPolyline($f["sid"]);
		if (is_array($res) && isset($res["code"]) && $res["code"]==0) {
			if (isset($res["data"]) && isset($res["data"]["polyline"])) {
				return $res["data"]["polyline"]; // No need to update
			}
		}
		return $f["polyline"];
	}

	public function getDate($country) {
        $tz = 'Asia/Singapore';
        if ($country=='US') {
            $tz = 'America/Los_Angeles';
        }
        $timestamp = time();
        $dt = new DateTime("now", new DateTimeZone($tz)); //first argument "must" be a string
        $dt->setTimestamp($timestamp); //adjust the object to correct timestamp
        return $dt->format('Y-m-d, H:i:s');
	}
	
	public function getLegTimezones($db, $id) {
		$departure_time_zone = 1; // Asia/Singapore
		$q = "SELECT departure_time_zone FROM google_directions_legs WHERE id=".((int)$id);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
			if (is_integer($f[0]) && $f[0]>0) {
				$departure_time_zone = $f[0];
			} else {
				$q = "SELECT * FROM address_timezone WHERE lower(timezone)=lower('".$f[0]."')";
				$r = pg_query($db, $q);
				if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
					$departure_time_zone = $f[0];
				}
			}
		}
		$arrival_time_zone = 1; // Asia/Singapore
		$q = "SELECT arrival_time_zone FROM google_directions_legs WHERE id=".((int)$id);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
			if (is_integer($f[0]) && $f[0]>0) {
				$arrival_time_zone = $f[0];
			} else {
				$q = "SELECT * FROM address_timezone WHERE lower(timezone)=lower('".$f[0]."')";
				$r = pg_query($db, $q);
				if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
					$arrival_time_zone = $f[0];
				}
			}
		}
		return [$departure_time_zone, $arrival_time_zone];
	}

	public function getLegTimezonesForAdvice($db, $id) {
		$result = [];
		$q = "SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id=".((int)$id);
		$r = pg_query($db, $q);
		while ($f=pg_fetch_row($r)) {
			$result[$f[0]] = self::getLegTimezones($db, $f[0]);
		}
		return $result;
	}

}

// vi:ts=2