<?php

class Quotes {

	const BASE_SQL_DATA = "SELECT a.*,ls.address AS location_start,ls.latitude AS location_start_lat,ls.longitude AS location_start_lng,ls.timezone AS location_start_tz,ls.geocoding_date AS location_geocoding_date,le.address AS location_end,le.latitude AS location_end_lat,le.longitude AS location_end_lng ";
	const BASE_SQL_FROM = " FROM quotes a, address ls, address le WHERE ls.id=a.location_start_id AND le.id=a.location_end_id";

	const VENDOR_UBER = 1;
	const VENDOR_LYFT = 2;
	const VENDOR_GRAB = 3;
	const VENDOR_COMFORTDELGRO = 4; // ComfortDelGro
	const VENDOR_GOJEK = 5;
	const VENDOR_TURO = 6;
	const VENDOR_GETAROUND = 7;
	const VENDOR_AUTOCAB = 8;

	public static function getById($db, $id) {
		syslog(LOG_WARNING,"Quote::getById(\$db, $id)");
        Logger::debug("Quote::getById(\$db, $id)");
		$result = [];
		$q = Quotes::BASE_SQL_DATA . Quotes::BASE_SQL_FROM . " AND a.id=${id} ";
		$r = pg_query($db, $q);
		//syslog(LOG_WARNING,$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 static function getByOriginDestinationId($db, $origin, $destination, $tpid, $limit, $offset) {
		syslog(LOG_WARNING,"Quote::getByOriginDestinationId(\$db, $origin, $destination, $tpid, $limit, $offset)");
		$results = [];
		$total = 0;
		$db_origin = pg_escape_string(strtolower($origin));
		$db_destination = pg_escape_string(strtolower($destination));
		$q = "SELECT count(*) ".Quotes::BASE_SQL_FROM." AND lower(ls.address)='${db_origin}' 
		AND lower(le.address)='${db_destination}'";
		if ($tpid>0) {
			$q.= " AND a.transport_provider_id=${tpid}";
		}
        $q.= " AND a.cost>0";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
			$total = $f[0];
		}
		if ($total>0 && $offset<$total) {
			$q = Quotes::BASE_SQL_DATA . Quotes::BASE_SQL_FROM . " AND lower(ls.address)='${db_origin}' ";
			$q.= " AND lower(le.address)='${db_destination}' ".($tpid>0?" AND a.transport_provider_id=${tpid}":"");
            $q.= " AND a.cost>0";
			$q.= " ORDER BY a.created DESC LIMIT ${limit} OFFSET ${offset}";
			$r = pg_query($db, $q);
			if ($r && pg_num_rows($r)) {
				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");
					$results[] = $f;
				}
			}
		}
		return array($total, $results);		
	}

  public static function getLastQuoteByOriginDestinationId($db, $origin, $destination, $tpid, $cacheTimeout = 300) {
    $db_origin = pg_escape_string(strtolower($origin));
    $db_destination = pg_escape_string(strtolower($destination));

    $q = Quotes::BASE_SQL_DATA . Quotes::BASE_SQL_FROM;
    $q.= " AND a.completed > now() - ( ${cacheTimeout} ||' seconds')::interval ";
    $q.= " AND lower(ls.address)='${db_origin}' ";
    $q.= " AND lower(le.address)='${db_destination}' ".($tpid>0?" AND a.transport_provider_id=${tpid}":"");
    $q.= " AND a.cost>0";
    $q.= " ORDER BY a.created DESC LIMIT 1 OFFSET 0";
    $result = NULL;
    $r = pg_query($db, $q);
    if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
      $result = $f;
    }
    return $result;
	}

  public static function getLastQuoteByOriginDestinationCoordinates($db, $origin, $destination, $tpId, $cacheTimeout = 300) {
	  if (!is_array($origin) || !is_array($destination)
        || !isset($origin['lat'])|| !isset($origin['lng'])
        || !isset($destination['lat'])|| !isset($destination['lng'])
    ) {
	    return NULL;
    }

    $origin_lat = $origin['lat'];
    $origin_lng = $origin['lng'];
    $destination_lat = $destination['lat'];
    $destination_lng = $destination['lng'];

    $q = Quotes::BASE_SQL_DATA . Quotes::BASE_SQL_FROM;
    $q.= " AND a.completed > now() - ( ${cacheTimeout} ||' seconds')::interval ";
    $q.= " AND ls.geometry = ST_SetSRID(ST_MakePoint( ${origin_lng}, ${origin_lat}), 4326)::geography ";
    $q.= " AND le.geometry = ST_SetSRID(ST_MakePoint( ${destination_lng}, ${destination_lat}), 4326)::geography";
    $q.= ($tpId>0?" AND a.transport_provider_id=${tpId}":"");
    $q.= " AND a.cost>0";
    $q.= " ORDER BY a.created DESC LIMIT 1 OFFSET 0";
    $result = NULL;
    $r = pg_query($db, $q);
    if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
      $result = $f;
    }
    return $result;
	}

	public static function getByQuoteGroupId($db, $id) {
		$q = Quotes::BASE_SQL_DATA . Quotes::BASE_SQL_FROM . " AND quote_group_id>0 AND quote_group_id=".((int)$id);
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			$results = [];
			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");
				$results[] = $f;
			}
			return array($results, NULL);
		}
		return array(NULL,pg_last_error($db));
	}

	public static function create($db, $data) {
		syslog(LOG_WARNING,'Quote::create($db, $data)');
		$location_start_id = Address::getAddress($db, $data, "location_start");
		$location_end_id = Address::getAddress($db, $data, "location_end");
		if ($location_start_id==NULL || $location_end_id==NULL) {
			return array(NULL,"Failed to save address");
		}
		$q = "INSERT INTO quotes (transport_provider_id,automation_id,member_id,location_start_id,location_end_id, quote_group_id, prefill, pool) ";
		$q.= " VALUES(".((int)$data["transport_provider_id"]).",".((int)$data["automation_id"]).",".((int)$data["member_id"]).",";
		$q.= "${location_start_id},${location_end_id},".((int)$data["quote_group_id"]).",".($data["prefill"]!='t'?"'f'":"'t'");
		$q.= ",".($data["pool"]!=2?"1":"2");
		$q.= ") RETURNING id";
		$log = [
			'message' => 'insert quote',
			'data' =>$data,
			'query' =>$q
		];
		Logger::debug($log);
		syslog(LOG_WARNING,'***************************************************************************');
		syslog(LOG_WARNING,$q);
		syslog(LOG_WARNING,'***************************************************************************');
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			return [Quotes::getById($db, $f["id"]),NULL];
		}
		syslog(LOG_WARNING,pg_last_error($db));
		return [NULL,pg_last_error($db)];
	}

	public static function update($db, $data) {
		syslog(LOG_WARNING,'Quotes::update(): '.json_encode($data));
		// cost_raw='".$data["cost_raw"]."',
		// created='".$data["created"]."',
		// member_id='".$data["member_id"]."'
		$q = "UPDATE quotes SET 
 			transport_provider_id=".((int)$data["transport_provider_id"]).",
 			automation_id=".((int)$data["automation_id"]).",
			cost=".((float)$data["cost"]).",
			completed='".$data["completed"]."',
			travel_date='".$data["travel_date"]."',
			location_start_id=".$data["location_start_id"].",
			location_end_id=".$data["location_end_id"]." 
			WHERE id='".$data["id"]."'";
		//error_log($q);
		syslog(LOG_WARNING,$q);
		$r = pg_query($db, $q);
		/* $log = [
			'affected_row' => pg_affected_rows($r),
			'message' => 'update quote',
			'data' =>$data,
			'query' =>$q
		];
		Logger::debug($log); */
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			return array(self::getById($db, $data["id"]),NULL);
		}
		return array(NULL, pg_last_error($db));
	}

	public static function getLegStepQuoteBySID($db, $sid) {
		$q = "SELECT * FROM leg_step_quote WHERE google_directions_leg_step_id=".((int)$sid);
		$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 static function deeplink($db, $quote) {
		syslog(LOG_WARNING,"Quote::deeplink(\$db, \$quote)");
        Logger::debug("Quote::deeplink(\$db, \$quote)");
		$url = "";
		switch ($quote["transport_provider_id"]) {
			case Quotes::VENDOR_UBER: // Uber
				// https://developer.uber.com/docs/riders/ride-requests/tutorials/deep-links/introduction
				$url = sprintf("uber://?client_id=IrcAu0gxX7EsPKWg3QJe8reR5Q8w1jDm&action=setPickup&pickup[latitude]=%f&pickup[longitude]=%f&dropoff[latitude]=%f&dropoff[longitude]=%f",
					$quote["location_start_lat"], $quote["location_start_lng"],
					$quote["location_end_lat"], $quote["location_end_lng"]
				);
				break;
			case Quotes::VENDOR_LYFT: // Lyft
				// https://developer.lyft.com/docs/deeplinking
				// &partner=GZbtPgh56RQb
				// &credits=<my_custom_promo_code>
				$url = sprintf("lyft://ridetype?id=lyft&pickup[latitude]=%f&pickup[longitude]=%f&destination[latitude]=%f&destination[longitude]=%f",
					$quote["location_start_lat"], $quote["location_start_lng"],
					$quote["location_end_lat"], $quote["location_end_lng"]
				);
				break;
			case Quotes::VENDOR_GRAB: // Grab
				// TODO: rotate the sources!
				$url = sprintf("grab://open?screenType=BOOKING&sourceId=raZbevC7RB&sourceAppName=SingaporeAir&dropOffLatitude=%f&dropOffLongitude=%f&pickUpLatitude=%f&pickUpLongitude=%f",
					$quote["location_end_lat"], $quote["location_end_lng"],
					$quote["location_start_lat"], $quote["location_start_lng"]
				);
				break;
			case Quotes::VENDOR_COMFORTDELGRO: // ComfortDelGro
				$url = sprintf("ComfortDelGroTaxi:///?action=setBooking&endingLat=%f&endingLong=%f&startingLat=%f&startingLong=%f",
					$quote["location_end_lat"], $quote["location_end_lng"],
					$quote["location_start_lat"], $quote["location_start_lng"]
				);
				break;
			case Quotes::VENDOR_GOJEK: // Gojek
				$url = sprintf("gojek://sggocar/gocar?product_id=car&pickup_latitude=%f&pickup_longitude=%f&dropoff_latitude=%f&dropoff_longitude=%f",
					$quote["location_start_lat"], $quote["location_start_lng"],
					$quote["location_end_lat"], $quote["location_end_lng"]
				);
				break;
		}
		//syslog(LOG_WARNING,"url=${url}");
		return $url;
	}

	public static function checkQuote($id) {
		global $savvyext;
		$httpAuthToken = $savvyext->cfgReadChar('system.automation_api_token');
		$automation_api_url = $savvyext->cfgReadChar('system.automation_api_url');

		$url = $automation_api_url."automation-job/" . $id;
		$opts = array(
			'http' => array(
				'method' => "GET",
				'header'  =>
					"Content-Type: application/json\r\n" .
					"Accept: application/json\r\n" .
					"Authorization: Server-Token ${httpAuthToken}\r\n"
			),
			"ssl" => array(
				"verify_peer"=>false,
				"verify_peer_name"=>false,
			)
		);
		$context = stream_context_create($opts);
		$body = file_get_contents($url, false, $context);
		$quote = json_decode($body,true);
		if (is_array($quote) && isset($quote["id"])) {
			return array($quote, NULL);
		} else if (is_array($quote) && isset($quote["message"])) {
			$body = $quote["message"];
			error_log(isset($quote["error"]) ? json_encode($quote["error"]) : $quote["message"]);
		}
		return array(NULL, $body);
	}

	public static function getQuoteDetails($id) {
		global $savvyext;
		$httpAuthToken = $savvyext->cfgReadChar('system.automation_api_token');
		$automation_api_url = $savvyext->cfgReadChar('system.automation_api_url');

		$url = $automation_api_url."automation-job-detail/" . $id;
		$opts = array(
			'http' => array(
				'method' => "GET",
				'header'  =>
					"Content-Type: application/json\r\n" .
					"Accept: application/json\r\n" .
					"Authorization: Server-Token ${httpAuthToken}\r\n"
			),
			"ssl" => array(
				"verify_peer"=>false,
				"verify_peer_name"=>false,
			)
		);
		$context = stream_context_create($opts);
		$body = file_get_contents($url, false, $context);
		$details = json_decode($body,true);
		if (is_array($details) && count($details)>0 && isset($details[0]["id"])) {
			return array($details, NULL);
		} else if (is_array($details) && isset($details["message"])) {
			$body = $details["message"];
			error_log(isset($details["error"]) ? json_encode($details["error"]) : $details["message"]);
		}
		return array(NULL, $body);
	}


}

// vi:ts=2