<?php

class Options {

    public static function getTransportOptions($db, $origin, $destination) {
        return Options::getServicesByLatitudeLongitude($db, $origin["lat"],$origin["lng"]);
    }

    public static function getServicesByLatitudeLongitude($db, $lat, $lng) {
        // $lat,$lng => geofence_area_country
        $geofence_area_country = [];
        $q = "SELECT * FROM (SELECT *,ST_DistanceSphere(location::geometry,ST_SetSRID(ST_MakePoint(";
        $q.= $lng.",".$lat."),4326)::geometry) AS distance FROM geofence_area_country ";
        $q.= " WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(";
        $q.= $lng.",".$lat."),4326)::geography,radius)) AS a ORDER BY a.distance ASC LIMIT 1";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
            $geofence_area_country = $f;
        } else {
            // Fatal
            $err = pg_last_error($db);
            $err = $err ? $err : $q;
            error_log('Options::getTransportOptions => '.$err); 
            throw new Exception("Failed to match input origin to a country");
        }
        // geofence_area_country => country_servies
        $country_services = [];
        if (array_key_exists("id",$geofence_area_country) && $geofence_area_country["id"]>0) {
            $q = "SELECT * FROM country_services WHERE country_id=".$geofence_area_country["id"]." AND status=1";
            $r = pg_query($db, $q);
            if ($r && pg_num_rows($r)) {
                while ($f=pg_fetch_assoc($r)) {
                    $country_services[] = $f;
                }
            } else {
                // Non-fatal, try to get city services next
                $err = pg_last_error($db);
                $err = $err ? $err : $q;
                error_log('Options::getTransportOptions => '.$err); 
            }
        }
        // $lat,$lng => geofence_area_city
        $geofence_area_city = [];
        $q = "SELECT * FROM (SELECT *,ST_DistanceSphere(location::geometry,ST_SetSRID(ST_MakePoint(";
        $q.= $lng.",".$lat."),4326)::geometry) AS distance FROM geofence_area_city ";
        $q.= " WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(";
        $q.= $lng.",".$lat."),4326)::geography,radius)) AS a ORDER BY a.distance ASC LIMIT 1";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
            $geofence_area_city = $f;
        } else {
            // Non-fatal, at least we know the country
            $err = pg_last_error($db);
            $err = $err ? $err : $q;
            error_log('Options::getTransportOptions => '.$err); 
        }
        // geofence_area_city => city_services
        $city_services = [];
        if (array_key_exists("id",$geofence_area_city) && $geofence_area_city["id"]>0) {
            $q = "SELECT * FROM city_services WHERE city_id=".$geofence_area_city["id"]." AND status=1";
            $r = pg_query($db, $q);
            if ($r && pg_num_rows($r)) {
                while ($f=pg_fetch_assoc($r)) {
                    $city_services[] = $f;
                }
            } else {
                // Non-fatal, if we have country services (let upstream decide what to do)
                $err = pg_last_error($db);
                $err = $err ? $err : $q;
                error_log('Options::getTransportOptions => '.$err); 
            }
        }
        if (is_array($country_services) && count($country_services)>0 && (!is_array($city_services) || count($city_services)<1)) {
            $services = $country_services;
        } else if (is_array($city_services) && count($city_services)>0 && (!is_array($country_services) || count($country_services)<1)) {
            $services = $city_services;
        } else {
            $services = $city_services;
            foreach ($country_services as $country_service) {
                $exists = false;
                foreach($services as $service) {
                    if ($service["transport_provider_id"]>0 
                     && $service["transport_provider_id"]==$country_service["transport_provider_id"]) {
                        // The service is available on city level - skip
                        $exists = true;
                        break;
                    }      
                }
                if (!$exists) {
                    // Add country service
                    $services[] = $country_service;
                }
            }
        }
        return [
            $services,
            [
                'geofence_area_country' => $geofence_area_country,
                'country_services' => $country_services,
                'geofence_area_city' => $geofence_area_city,
                'city_services' => $city_services
            ]
        ];
    }

    public static function processGooglePlace($db, $data) {
        // data.address
        // data.place
        // data.lat
        // data.lng
        // data.utc_offset_minutes
        // data.postal
        // data.vicinity
        // data.city
        // data.country
        $db_address = pg_escape_string(html_entity_decode($data["address"],ENT_QUOTES|ENT_HTML5,"UTF-8"));
        $db_lat = floatval($data["lat"]);
        $db_lng = floatval($data["lng"]);
        $db_city = pg_escape_string($data["city"]);
        $db_postal = pg_escape_string($data["postal"]);
        $db_country = pg_escape_string($data["country"]);

        // Step 1: Check address
        $q = "SELECT a.*,b.timezone AS \"timeZoneId\" FROM address a LEFT JOIN address_timezone b ON (b.id=a.timezone) ";
        $q .= " WHERE LOWER(a.address)=LOWER('${db_address}')";
        $q .= "AND latitude<>0 AND longitude<>0 AND geocoding_date IS NOT NULL AND postal IS NOT NULL ";
        $q .= "ORDER BY geocoding_date DESC LIMIT 1";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
		    $f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
            foreach ($f as $key=>$val) {
                $data[$key] = $val;
            }
            return $data;
        }
        // Step 2: Get timezone
        date_default_timezone_set('UTC');
        $seconds = $data["utc_offset_minutes"] * 60;
        $timezone = timezone_name_from_abbr('', $seconds, 1);
        $q = "SELECT * FROM address_timezone WHERE LOWER(timezone)=LOWER('${timezone}')";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
            $data["timezone"] = $f["id"];
            $data["timeZoneId"] = $f["timezone"];
        } else {
            $q = "INSERT INTO address_timezone (timezone) VALUES('".pg_escape_string($timezone)."') RETURNING *";
            $r = pg_query($db, $q);
            if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
                $data["timezone"] = $f["id"];
                $data["timeZoneId"] = $f["timezone"];
            } else { // Most likely this is very wrong...
                $data["timezone"] = 1; // Singapore
                $data["timeZoneId"] = 'Asia/Singapore';
            }
        }
        // Step 3: Get city
        $q = "SELECT id FROM geofence_area_city WHERE LOWER(city) ILIKE '" . strtolower($db_city) . "' AND country='${db_country}' LIMIT 1";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            $data["city_id"] = $f[0];
        } else {
            $q = "INSERT INTO geofence_area_city (city,country,latitude,longitude,location) VALUES (";
            $q.= "'${db_city}','${db_country}',${db_lat},${db_lng},ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}), 4326)) RETURNING id";
            $r = pg_query($db, $q);
            if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
                $data["city_id"] = $f[0];
            } else {            
                // Most likely this is very wrong...
                $data["city_id"] = 1; // Singapore
            }
        }
        // Step 4: Get closest postal if missing...
        $db_tz = $data["timezone"];
        if (empty($db_postal)) {
            $distance = 50000; // 50 km
            $q = "SELECT postal,
            ST_DistanceSphere(geometry::geometry, ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}),4326)) AS distance
              FROM geoname_postal_code
              WHERE ST_DistanceSphere(geometry::geometry, ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}),4326)) BETWEEN 0 AND ${distance} AND country='${db_country}'
              ORDER BY distance
              LIMIT 1;";
                $r = pg_query($db, $q);
                if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
                    $db_postal = $f["postal"];
                    $data["postal"] = $f["postal"];
                }
        }
        // Step 5: Save address
        $q = "INSERT INTO address (address,latitude,longitude,timezone,geocoding_date,postal,country,geometry, city_id) VALUES (";
        $q.= "'${db_address}',${db_lat},${db_lng}," . ($db_tz == null ? "NULL" : $db_tz);
        $q.= ",now(),'${db_postal}','${db_country}',ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}), 4326),'".$data["city_id"]."')";
        $q.= " RETURNING id, address, latitude as lat, longitude as lng, postal, country";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            $data["id"] = $f["id"];
            $data["address"] = $f["address"];
            $data["lat"] = $f["lat"];
            $data["lng"] = $f["lng"];
            $data["geometry"] = $f["geometry"];
            $data["postal"] = $f["postal"];
        }
        return $data;
    }

    public static function checkValidLocations($db, $origin, $destination) {
        // Check for Google places result
        if (is_array($origin) && array_key_exists('place',$origin) && is_array($origin['place'])) {
            $origin = Options::processGooglePlace($db, $origin);
        }
        // Basic input sanity check: origin
        if (!is_array($origin) || !array_key_exists('address',$origin) 
        || !array_key_exists('lat',$origin) || !array_key_exists('lng',$origin)
        || !array_key_exists('id',$origin) || $origin['id']<1) {
            throw new Exception('Invalid input origin');
        }
        // Check for Google places result
        if (is_array($destination) && array_key_exists('place',$destination) && is_array($destination['place'])) {
            $destination = Options::processGooglePlace($db, $destination);
        }
        // Basic input sanity check: destination
        if (!is_array($destination) || !array_key_exists('address',$destination) 
        || !array_key_exists('lat',$destination) || !array_key_exists('lng',$destination)
        || !array_key_exists('id',$destination) || $destination['id']<1) {
            throw new Exception('Invalid input destination');
        }
        // Basic input sanity check: GPS coordinates
        if ($origin['lat']==null || $origin['lng']==null || $destination['lat']==null || $destination['lng']==null
        || ($origin['lat']==0 && $origin['lng']==0) || ($destination['lat']==0 && $destination['lng']==0)) {
            throw new Exception('Invalid origin and/or destination coordinates');
        }
        return [$origin, $destination];
    }

    public static function checkValidOption($db, $option) {
        // Basic input sanity check: option
        if (!is_array($option) || !array_key_exists('name',$option) 
        || !array_key_exists('transport_provider_id',$option) || $option['transport_provider_id']<1
        || !array_key_exists('id',$option) || $option['id']<1) {
            throw new Exception('Invalid transport option');
        }
    }

    public static function checkValidTrip($db, $trip) {
        // TODO!
    }

    public static function createTrip($db, $country, $duration, $distance, $location_start_id, $location_end_id) {
        error_log("Options::createTrip(\$db, $country, $duration, $distance, $location_start_id, $location_end_id)");
        $data = [
            'travel_date' => Options::getDate($country),
            'duration' => ((int)($duration/60)),
            'cost_raw' => NULL,
            'trackedemail_item_id' => NULL,
            'cost' => NULL,
            'distance' => sprintf("%0.02f",$distance/1000.0),
            'transport_provider_id' => NULL,
            'scheduled' => NULL,
            'travel_date_end' => Options::getDate($country),
            'dup_id' => NULL,
            'location_start_id' => $location_start_id,
            'location_end_id' => $location_end_id,
            'private' => 't', /* this is a private entry without the source e-mail */
            'booking' => 't' /* this is a booking entry */
        ];
        list($id, $err) = Options::saveTrip($db->getConnect(), $data);
        if ($id && $id>0) {
            $trip = Options::getTripById($db->getConnect(), $id);
            return $trip;
        }
        return NULL; // WTF?
    }

    public static function getTripById($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 static function saveTrip($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,
            'booking' => 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 static function validateTrip($db, $trip) {
        if (!is_array($trip) || !array_key_exists("id",$trip) || $trip["id"]<1) {
            return false;
        }
        if (!array_key_exists("options",$trip) || !is_array($trip["options"]) || !array_key_exists("legs",$trip["options"]) 
         || !is_array($trip["options"]["legs"]) || count($trip["options"]["legs"])<1 
         || !array_key_exists("id",$trip["options"]["legs"][0]) || $trip["options"]["legs"][0]["id"]<1) {
            return false;
        }
        return true;
    }

    public static function saveTripAdvice($db, $trip) {
        error_log('Options::saveTripAdvice($db, $trip)');
        if (!is_array($trip) || !array_key_exists("id",$trip) || $trip["id"]<1) {
            return NULL;
        }
        $q = "INSERT INTO parsedemail_item_advice_google (parsedemail_item_id,routes,source) VALUES (".$trip["id"].",1,'".$trip["source"]."') RETURNING id";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
            return $f[0];
        }
        return NULL;
    }

    public static function saveTripAdviceLeg($db, $trip, $leg) {
        error_log('Options::saveTripAdviceLeg($db, $trip, $leg)');
        $fare_raw = "NULL";
        if (array_key_exists("fare_raw",$leg) && $leg["fare_raw"]>0) {
            $fare_raw = (int)$leg["fare_raw"];
        }
        $q = "INSERT INTO google_directions_legs (parsedemail_item_advice_google_id,arrival_time,arrival_time_zone,";
        $q.= "departure_time,departure_time_zone,distance,duration,steps,fare_raw,polyline) VALUES(";
        $q.= $leg["parsedemail_item_advice_google_id"].",'".pg_escape_string($leg["arrival_time"])."',".((int)$leg["arrival_time_zone"]);
        $q.= ",'".pg_escape_string($leg["departure_time"])."',".((int)$leg["departure_time_zone"]).",";
        $q.= ((int)$leg["distance"]).",".((int)$leg["duration"]).",1,${fare_raw},'".pg_escape_string($leg["polyline"])."') RETURNING id";
        $r = pg_query($db, $q);
        error_log($q);
        if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
            return $f[0];
        }
        return NULL;
    }

    public static function saveTripAdviceLegStep($db, $trip, $leg, $step) {
        error_log('Options::saveTripAdviceLegStep($db, $trip, $leg, $step)');
        $q = "INSERT INTO google_directions_leg_steps (google_directions_leg_id,distance,duration,travel_mode,";
        $q.= "location_start_lat,location_start_lng,location_end_lat,location_end_lng,html_instructions,polyline) VALUES (";
        $q.= $leg["id"].",".((int)$step["distance"]).",".((int)$step["duration"]).",";
        $q.= "'".pg_escape_string($step["travel_mode"])."',".$step["location_start_lat"].",".$step["location_start_lng"].",";
        $q.= $step["location_end_lat"].",".$step["location_end_lng"].",'".pg_escape_string($step["html_instructions"])."',";
        $q.= "'".pg_escape_string($step["polyline"])."') RETURNING id";
        $r = pg_query($db, $q);
        error_log($q);
        if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
            return $f[0];
        }
        return NULL;
    }

    public static function saveTripAdviceLegStepQuote($db, $trip, $leg, $step) {
        $fare_raw = NULL;
        if (array_key_exists("fare_raw",$step) && $step["fare_raw"]>0) {
            $fare_raw = (int)$step["fare_raw"];
        }
        if ($fare_raw>0) {
            $q = "INSERT INTO leg_step_quote (google_directions_leg_step_id,name,service,board,alight,";
            $q.= "distance,fare,fare_raw,distance_raw) VALUES (".$step["id"].",";
            $q.= "'".pg_escape_string($trip["source"])."','".pg_escape_string($trip["source"])."',";
            $q.= "'".pg_escape_string($trip["location_start"])."','".pg_escape_string($trip["location_end"])."',";
            $q.= "'".sprintf("%0.02f",$step["distance"]/1000.0)."','".sprintf("%0.02f",$fare_raw/100.0)."',";
            $q.= ((int)$fare_raw).",".((int)$step["distance"]).") RETURNING id";
            $r = pg_query($db, $q);
            if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
                return $f[0];
            }
        }
        return NULL;
    }

    public static 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 static function getTimezoneById($db, $id, $def='Asia/Singapore') {
        $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['timezone'];
        }
        return $def; //$id;
    }

}

// vi:ts=2