<?php

class Geocode
{
    const GPS_BOX_DECIMAL = 10;
    const COUNTRY_LIMITS = [
        'SG' => null, /* no limit! */
        'US' => [
            [
                'name' => 'San Francisco',
                'latitude' => 37.7126152,
                'longitude' => -122.1754642,
                'radius' => 60000, /* in meters */
            ],
        ],
    ];

    public function getAddressById($db, $id)
    {
        $result = array();
        $q = "SELECT a.*,b.timezone AS \"timeZoneId\" FROM address a LEFT JOIN address_timezone b ON (b.id=a.timezone) ";
				$q.= " WHERE a.id=".((int)$id);
        $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");
            $result = $f;
        }
        return $result;
    }

    public function save($db, $result)
    {
        return Address::save($db, $result);
    }

    public function checkLatLngByAddress($db, $address, $country = GeocodeApi::DEFAULT_COUNTRY_CODE)
    {
        syslog(LOG_WARNING, "Geocode::checkLatLngByAddress(\$db,'${address}',$country)");
        $db_address = pg_escape_string(strtolower($address));
        $db_country = pg_escape_string($country);
        $q = "SELECT a.address, a.latitude AS lat, a.longitude AS lng, b.timezone AS location_start_tz, a.postal, a.timezone, a.id, a.country, b.timezone AS \"timeZoneId\", plus_code ";
        $q .= " FROM address a LEFT JOIN address_timezone b ON b.id=a.timezone LEFT JOIN address_alias c ON a.id=c.address_id ";
        $q .= " WHERE (lower(a.address) = '${db_address}' OR  lower(a.plus_code) = '${db_address}' OR lower(c.name) = '${db_address}') ";
        $q .= " AND a.country='${db_country}' AND a.geocoding_date IS NOT NULL ORDER BY a.geocoding_date DESC LIMIT 1";
        $r = pg_query($db, $q);
        syslog(LOG_WARNING, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            $f["timeZoneId"] = $f["location_start_tz"]; // Case is lost on pg_query()
            unset($f["location_start_tz"]);
			$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
            return [$f, null];
        }
       
        return [null, pg_last_error($db)];
    }

    public function getTimezone($db, $timezone)
    {
        if (is_int($timezone) && $timezone > 0) {
            return $timezone;
        }
        $db_timezone = pg_escape_string($timezone);
        $q = "SELECT id FROM address_timezone WHERE timezone='${db_timezone}'";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            return $f[0];
        }
        $q = "INSERT INTO address_timezone (timezone) VALUES('${db_timezone}') RETURNING id";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            return $f[0];
        }
        return null;
    }

    public function checkDistanceCache($db, $fromLat, $fromLng, $toLat, $toLng)
    {
        $db_fromLat = (float) $fromLat;
        $db_fromLng = (float) $fromLng;
        $db_toLat = (float) $toLat;
        $db_toLng = (float) $toLng;
        $q = "SELECT distance,duration FROM address_distance_cache WHERE ";
        $q .= "start_lat=${db_fromLat} AND start_lng=${db_fromLng} AND end_lat=${db_toLat} AND end_lng=${db_toLng}";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            return array($f, null);
        }
        return array(null, pg_last_error($db));
    }

    public function saveDistanceCache($db, $fromLat, $fromLng, $toLat, $toLng, $data)
    {
        $db_fromLat = (float) $fromLat;
        $db_fromLng = (float) $fromLng;
        $db_toLat = (float) $toLat;
        $db_toLng = (float) $toLng;
        $distance = $data["distance"];
        $duration = $data["duration"];
        $q = "INSERT INTO address_distance_cache (start_lat,start_lng,end_lat,end_lng,distance,duration) VALUES (";
        $q .= "${db_fromLat},${db_fromLng},${db_toLat},${db_toLng},${distance},${duration}) RETURNING id";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            return array($f, null);
        }
        return array(null, pg_last_error($db));
    }

    public function getTransportProvidersByCountry($db, $country)
    {
        $db_country = pg_escape_string($country);
        $q = "SELECT a.id as app_id,a.transport_provider_id,b.name as transport_provider_name,a.country,a.ios_app_id,a.android_app_id,b.timeout,b.retries
			FROM transport_provider_apps a, transport_providers b
			WHERE a.country='${db_country}' AND b.id=a.transport_provider_id AND b.active=1";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r)) {
            $result = [];
            while ($f = pg_fetch_assoc($r)) {
                $result[] = $f;
            }
            return array($result, null);
        }
        return array(null, pg_last_error($db));
    }

    public function reverseGeocodeCache($db, $address, $lat, $lng, $postal = null, $country = "SG", $timezone = null,$plus_code="")
    {
        syslog(LOG_WARNING, "Geocode::reverseGeocodeCache(\$db, '$address', $lat, $lng, $postal, $country, $timezone,$plus_code)");
        $result = [
            "lat" => $lat,
            "lng" => $lng,
            "address" => $address,
            "postal" => $postal,
            "country" => $country,
            "timeZoneId" => $timezone,
            "plus_code" => $plus_code,
        ];
        $address = Address::save($db, $result);
        if (!empty($address)) {
            syslog(LOG_WARNING, 'Address cached!');
        } else {
            syslog(LOG_WARNING, 'Failed to cache address!');
        }
    }

    public function reverseGeocode($db, $lat, $lng)
    {
        syslog(LOG_WARNING, "Geocode::reverseGeocode(\$db, $lat, $lng)");
        $db_lat = floatval($lat);
        $db_lng = floatval($lng);
        // Check our local address table with precisions 5 to 3
        for ($i = 5; $i > 2; $i--) {
            $q = "SELECT * FROM address WHERE round(latitude,${i})=round(${db_lat},${i}) AND round(longitude,${i})=round(${db_lng},${i}) ";
            $q .= " AND latitude<>0 AND longitude<>0 ORDER BY geocoding_date DESC LIMIT 1";
            syslog(LOG_WARNING, $q);
            $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");
                return [$f, null];
            }
        }
        // Check our local Singapore table with precisions 5 to 3
        for ($i = 5; $i > 2; $i--) {
            $q = "SELECT * FROM singapore_buildings WHERE round(latitude,${i})=round(${db_lat},${i}) AND round(longitude,${i})=round(${db_lng},${i})";
            $r = pg_query($db, $q);
            if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
                $tz = 1; // Asia/Singapore - since this is "singapore_buildings" table
                self::reverseGeocodeCache($db, $f['address'], $db_lat, $db_lng, $f['postal'], 'SG', $tz);
                return [$f, null];
            }
        }
        return [null, pg_last_error($db)];
    }

    public function reverseGeocodeGps($db, $gpsdb, $lat, $lng, $radius = 100)
    {
        $db_lat = floatval($lat);
        $db_lng = floatval($lng);
        // Check our gps address table with radius
        $r = pg_query_params($gpsdb, self::SELECT_ADDRESS_IN_RADIUS, [
            $db_lng,
            $db_lat,
            $radius,
        ]);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
						$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
            return [$f, null];
        }

        // Check our gps Singapore table with radius
        $r = pg_query_params($gpsdb, self::SELECT_SINGAPORE_BUILDINGS_IN_RADIUS, [
            $db_lng,
            $db_lat,
            $radius,
        ]);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
						$f["address"] = html_entity_decode ($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
            $q = "SELECT * FROM singapore_buildings WHERE id=" . $f["id"];
            $r = pg_query($db, $q);
            $d = pg_fetch_assoc($r);
            $tz = 1; // Asia/Singapore - since this is "singapore_buildings" table
            self::reverseGeocodeCache($db, $f['address'], $db_lat, $db_lng, $d['postal'], 'SG', $tz);
            return [$f, null];
        }

        return [null, pg_last_error($gpsdb)];
    }

    public function mockGPSLocation($db, $member_id, $lat, $lng, $location = 'default')
    {
        syslog(LOG_WARNING, "Geocode::mockGPSLocation(\$db, $member_id, $lat, $lng, $location)");
        $db_member_id = (int) $member_id;
        $db_location = pg_escape_string($location);
        $q = "SELECT lat,lng FROM mock_gps_location WHERE member_id=${db_member_id} AND location='${db_location}'";
        syslog(LOG_WARNING, $q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            $lat = $f[0];
            $lng = $f[1];
        } else if ($lat == 0 && $lng == 0) {
            $lat = 1.2966426;
            $lng = 103.7763939;
        }
        return [$lat, $lng];
    }

    public function mockGPSCountry($db, $member_id, $country, $location = 'default')
    {
        syslog(LOG_WARNING, "Geocode::mockGPSCountry(\$db, $member_id, $country, $location)");
        $db_member_id = (int) $member_id;
        $db_location = pg_escape_string($location);
        $q = "SELECT country FROM mock_gps_location WHERE member_id=${db_member_id} AND location='${db_location}'";
        syslog(LOG_WARNING, $q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            $country = $f[0];
        } else if ($country == '') {
            $country = 'SG';
        }
        return $country;
    }

    public function getCountry($db, $country)
    {
        $db_country = pg_escape_string(strtolower($country));
        $q = "SELECT * FROM country WHERE lower(code)=lower('${db_country}') OR lower(country)=lower('${db_country}') OR lower(short_name)=lower('${db_country}')";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_array($r)) {
            return $f;
        }
        return null;
    }

    public function getCountryByGPS($db, $location, $country, $defaultCountry) {
        syslog(LOG_WARNING,"Geocode::getCountryByGPS(\$db, \$location, $country, $defaultCountry)");
        if ($country!=null && strlen(trim($country))==2) {
            return $country;
        }
        if (is_array($location) && array_key_exists('lat',$location) && array_key_exists('lng',$location)
        && $location['lat']!=0 && $location['lat']!=0) {
            $fromLat = $location['lat'] - Geocode::GPS_BOX_DECIMAL;
            $fromLng = $location['lng'] - Geocode::GPS_BOX_DECIMAL;
            $toLat = $location['lat'] + Geocode::GPS_BOX_DECIMAL;
            $toLng = $location['lng'] + Geocode::GPS_BOX_DECIMAL;
            // First we try address
            $baseQuery = "SELECT country FROM %s WHERE ";
            $baseQuery.= "(latitude BETWEEN ${fromLat} AND ${toLat}) AND (longitude BETWEEN ${fromLng} AND ${toLng})";
            $baseQuery.= " ORDER BY ST_DistanceSphere(%s::geometry, ST_SetSRID(ST_MakePoint(".$location["lng"].",".$location["lat"]."),4326))";
            $q = sprintf($baseQuery, "address", "geometry");
            $q.= " LIMIT 1";
            syslog(LOG_WARNING,$q);
            $r = pg_query($db, $q);
            if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
                $country = $f[0];
            } else {
                // Then we try geoname
                $q = sprintf($baseQuery, "geoname", "location");
                $q.= " LIMIT 1";
                syslog(LOG_WARNING,$q);
                $r = pg_query($db, $q);
                if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
                    $country = $f[0];
                }
            }
        } else {
            syslog(LOG_WARNING,"No GPS coordinates supplied!");
        }
        if ($country==null || strlen(trim($country))!=2) {
            return $defaultCountry;
        }
        return $country;
    }

    public static function saveGeocodingLog($db, $address, $latitude, $longitude, $address_output,$duration, $client_ip, $client_id)
    {
        $i = "INSERT INTO geocoding_logs (address_input,latitude,longitude,call_dated,address_output, duration, client_ip,client_id) VALUES ('${address}'," . (!empty($latitude)?$latitude: "NULL") . "," . (!empty($longitude)?$longitude: "NULL") . ",NOW(),'${address_output}',${duration},'${client_ip}','${client_id}') RETURNING id";
        $r = pg_query($db, $i);
        syslog(LOG_WARNING,$i);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            $log_id = $f[0];
        }
        return $log_id;
    }

    const SELECT_ADDRESS_IN_RADIUS = "SELECT id,
       address,
       latitude,
       longitude,
       ST_DistanceSphere(location, ST_SetSRID(ST_MakePoint($1, $2),4326)) AS distance
FROM address
WHERE
     ST_DistanceSphere(location, ST_SetSRID(ST_MakePoint($1, $2),4326)) BETWEEN 0 AND $3
ORDER BY distance";

    const SELECT_SINGAPORE_BUILDINGS_IN_RADIUS = "SELECT id,
       address,
       latitude,
       longitude,
       ST_DistanceSphere(location, ST_SetSRID(ST_MakePoint($1, $2),4326)) AS distance
FROM singapore_buildings
WHERE
     ST_DistanceSphere(location, ST_SetSRID(ST_MakePoint($1, $2),4326)) BETWEEN 0 AND $3
ORDER BY distance";

}

// vi:ts=2