<?php

class Address
{

    public function getAddressById($db, $id)
    {
        syslog(LOG_WARNING, "Address::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);
        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");
            $result = $f;
        }
        return $result;
    }

    public function getAddress($db, $data, $prefix = "location_start")
    {
        syslog(LOG_WARNING, "Address::getAddress(\$db, \$data, $prefix)");
        $db_address = pg_escape_string(trim(html_entity_decode($data[$prefix],ENT_QUOTES|ENT_HTML5,"UTF-8")));
        error_log(json_encode($data));
        $db_lat = (float) $data["${prefix}_lat"];
        $db_lng = (float) $data["${prefix}_lng"];
        $q = "SELECT id FROM address WHERE address='${db_address}' AND latitude=${db_lat} AND longitude=${db_lng}";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            return $f["id"];
        }
        $q = "SELECT id FROM address WHERE address='${db_address}'";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            return $f["id"];
        }
        $db_timezone = (int) $data["${prefix}_tz"];
        if ($db_timezone == 0) {
            $db_timezone = 1;
        }
        // Asia/Singapore
        $db_postal = pg_escape_string($data["${prefix}_postal"]);
        if ($db_postal == "") {
            if (($pos = strrpos($db_address, " ")) !== false) {
                $db_postal = trim(substr($db_address, $pos));
            }
        }
        $db_country = ""; // SG?
        if (array_key_exists("${prefix}_country", $data)) {
            $db_country = pg_escape_string($data["${prefix}_country"]);
        }
        $q = "INSERT INTO address (address,latitude,longitude,timezone,geocoding_date,postal,country,geometry) VALUES(";
        $q .= "'${db_address}',${db_lat},${db_lng},${db_timezone},'" . date("Y-m-d") . "','${db_postal}','${db_country}',ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}), 4326)";
        $q .= ") RETURNING id";
        syslog(LOG_WARNING, $q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            return $f["id"];
        }
        return null;
    }

    public function save($db, $result)
    {
        // Check the address exists
        $db_address = pg_escape_string(html_entity_decode($result["address"],ENT_QUOTES|ENT_HTML5,"UTF-8"));
        $db_address_input = isset($result["address_input"])?pg_escape_string(html_entity_decode($result["address_input"],ENT_QUOTES|ENT_HTML5,"UTF-8")):"";
        $q = "SELECT id, address, latitude as lat, longitude as lng, postal, country, timezone, plus_code FROM address WHERE lower(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";
        error_log($q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            return $f;
        }
        syslog(LOG_WARNING, 'Address::save($db, $result)');
        $db_lat = floatval($result["lat"]);
        $db_lng = floatval($result["lng"]);
        $db_postal = pg_escape_string($result["postal"]);
        $db_plus_code = pg_escape_string($result["plus_code"]);
        $db_city = pg_escape_string($result["city"]);
        $db_country = pg_escape_string($result["country"]);
        $db_tz = Geocode::getTimezone($db, $result["timeZoneId"]);
        $db_city_lat = floatval($result["city_lat"]);
        $db_city_lng = floatval($result["city_lng"]);
        $city_id = Address::getCity($db, $db_city, $db_country,$db_city_lat, $db_city_lng);
        if (empty($city_id)) {
            $city_id = Address::insertCity($db, $db_city, $db_country, $db_city_lat, $db_city_lng);
        }

        if (empty($db_postal)) {
            $db_postal = Address::getClosestPostalCode($db_lat, $db_lng, $db_country);
        }

        $q = "INSERT INTO address (address,latitude,longitude,timezone,geocoding_date,postal,country,geometry, city_id, plus_code) VALUES (";
        $q .= "'${db_address}',${db_lat},${db_lng}," . ($db_tz == null ? "NULL" : $db_tz) . ",now(),'${db_postal}','${db_country}',ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}), 4326),'${city_id}','${db_plus_code}')";
        $q .= " RETURNING id, address, latitude as lat, longitude as lng, postal, country, timezone, city_id, plus_code";

        error_log($q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            if(!empty($db_address_input) && $db_plus_code!=$db_address_input){
                Address::saveAliasAddress($db, $f['id'], $db_address_input);
            }
            return $f;
        }
        return null;
    }
    
    public function saveAliasAddress($db, $address_id, $address, $source='Google'){
        $q = "SELECT id, address_id, name FROM address_alias WHERE lower(name)=lower('${address}') ";
        $q .= "ORDER BY geocoding_date DESC LIMIT 1";
        error_log($q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) { 
            return $f;
        }

        $q = "INSERT INTO address_alias (address_id,name,geocoding_date,source) VALUES (";
        $q .= "${address_id},'${address}', now() ,'${source}')";
        $q .= " RETURNING address_id, name";
        error_log($q);
        
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            return $f;
        }
        return null;

    }

    public function update($db, $condition, $data) {
        syslog(LOG_WARNING,'Address::update(): '.json_encode($data));
        $q = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = 'address'";
        $r = pg_query($db, $q);
        while ($f = pg_fetch_assoc($r)) {
            $columns[$f['column_name']] = $f['data_type'];
        }
        if(count($data)<1) return null;
        
        $q = "UPDATE address SET id=id ";
        foreach ($data as $key => $val) {
            if ($key == "id") {
                continue;
            }
            if (array_key_exists($key, $data) && !in_array($columns[$key], ['character varying', 'date', 'timestamp with time zone', 'timestamp without time zone'])) {
                $q .= ", ${key} = " . ($val == "" ? "NULL" : $val);
            } else {
                $q .= ", ${key} = '" . pg_escape_string($val) . "'";
            }
        }
        $q .= " WHERE " . $condition . "";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
			return $f;
		}
		return array(NULL, pg_last_error($db));
	}

	public function searchInView($db, $country, $address) {
    $db_country = pg_escape_string($country); // is not used for now...
    $db_address = pg_escape_string(html_entity_decode($address,ENT_QUOTES|ENT_HTML5,"UTF-8"));
    $words = preg_split("/[\s,]+/", $db_address);
    $tsquery = implode(':* & ', $words). ':* & '.$db_country;

    $q  = "SELECT * FROM address_view a
                WHERE address_search @@ to_tsquery('simple', '${tsquery}')
            ORDER BY ts_rank(address_search,to_tsquery('simple', '${tsquery}')) desc
            LIMIT 10";
    $r = pg_query($db, $q);
    $results = [];
    if ($r && pg_num_rows($r)) {
      while ($f = pg_fetch_assoc($r)) {
        $f["address"] = html_entity_decode($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
        $results[] = $f;
      }
      if (count($results) > 9) {
        return $results;
      }
    }
    return $results;
  }


    public function search($db, $country, $address)
    {
        //syslog(LOG_WARNING,"Address::search(\$db, $country, $address)");
        $db_country = pg_escape_string($country); // is not used for now...
        $db_address = pg_escape_string(html_entity_decode($address,ENT_QUOTES|ENT_HTML5,"UTF-8"));
        $q = "SELECT a.*, b.timezone AS \"timeZoneId\" FROM address a LEFT JOIN address_timezone b ON (b.id=a.timezone) ";
        $q .= " WHERE UPPER(a.address) ILIKE ('%${db_address}%') AND a.country='${db_country}' LIMIT 10";
        //error_log($q);
        $r = pg_query($db, $q);
        $results = [];
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {
								$f["address"] = html_entity_decode($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
                $results[] = $f;
            }
            if (count($results) > 9) {
                return $results;
            }
        }
        if ($country == 'SG') {
            $q = "SELECT id as singapore_buildings_id, address, latitude, longitude, 1 AS timezone, NOW() as geocoding_date, postal, 'SG' AS country, building AS description, 'Asia/Singapore' AS \"timeZoneId\" ";
            $q .= " FROM singapore_buildings WHERE UPPER(address) ILIKE ('%${db_address}%') LIMIT 10";
            $r = pg_query($db, $q);
            if ($r && pg_num_rows($r)) {
                while ($f = pg_fetch_assoc($r)) {
										$f["address"] = html_entity_decode($f["address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
                    $results[] = $f;
                }
                return $results;
            }
        }
        /*
        savvy=> \d singapore_buildings
        Table "public.singapore_buildings"
        Column   |          Type          |                            Modifiers
        -----------+------------------------+------------------------------------------------------------------
        id        | integer                | not null default nextval('singapore_buildings_id_seq'::regclass)
        address   | character varying(150) | not null
        blk_no    | character varying(10)  |
        building  | character varying(100) |
        latitude  | numeric                |
        longitude | numeric                |
        postal    | character varying(6)   | not null
        road_name | character varying(40)  | not null
        searchval | character varying(100) | not null
        x         | numeric                |
        y         | numeric                |
        Indexes:
        "singapore_buildings_pkey" PRIMARY KEY, btree (id)

        savvy=> \d address
        Table "public.address"
        Column     |          Type          |                      Modifiers
        ----------------+------------------------+------------------------------------------------------
        id             | bigint                 | not null default nextval('address_id_seq'::regclass)
        address        | character varying(200) |
        latitude       | numeric                |
        longitude      | numeric                |
        timezone       | integer                |
        geocoding_date | date                   |
        postal         | character varying(40)  |
        country        | character varying(2)   | default 'SG'::character varying
        geometry       | geography(Point,4326)  |
        description    | character varying(100) |
         */
        return $results;
    }

    public function recentTrips($db, $member_id, $country, $address)
    {
        $db_country = pg_escape_string($country); // is not used for now...
        $db_address = pg_escape_string(html_entity_decode($address,ENT_QUOTES|ENT_HTML5,"UTF-8"));
        $q = "SELECT a.location_start_id, a.location_end_id ";
        $q .= " FROM trackedemail_item b, parsedemail_item a ";
        $q .= " LEFT JOIN address c ON (c.id=a.location_start_id) LEFT JOIN address d ON (d.id=a.location_end_id) ";
        $q .= " WHERE b.id=a.trackedemail_item_id AND b.member_id=" . ((int) $member_id);
        $q .= " AND (c.country='${db_country}' OR d.country='${db_country}')";
        $q .= " ORDER BY a.travel_date DESC LIMIT 10";
        //error_log($q);
        $r = pg_query($db, $q);
        $results = [];
        $address = [];
        if ($r && pg_num_rows($r)) {
            while ($f = pg_fetch_assoc($r)) {
                if (!array_key_exists($f["location_start_id"], $address)) {
                    $results[] = self::getAddressById($db, $f["location_start_id"]);
                    $address[$f["location_start_id"]] = 1;
                }
                if (!array_key_exists($f["location_end_id"], $address)) {
                    $results[] = self::getAddressById($db, $f["location_end_id"]);
                    $address[$f["location_end_id"]] = 1;
                }
            }
            return $results;
        }
        return null;
    }

    public function favourites($db, $member_id, $country, $address)
    {
        // Not implemented
        return null;
    }

    public static function getTzById($db, $id)
    {
        syslog(LOG_WARNING, "Address::getTzById(\$db, $id)");
        $q = "SELECT timezone FROM address_timezone WHERE id=" . ((int) $id);
        //syslog(LOG_WARNING,$q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            return $f[0];
        }
        return 'America/Los_Angeles'; // 2
    }

    public static function getTzByAddressId($db, $id)
    {
        syslog(LOG_WARNING, "Address::getTzByAddressId(\$db, $id)");
        $q = "SELECT timezone FROM address_timezone WHERE id=(SELECT timezone FROM address WHERE id=" . ((int) $id) . ")";
        //syslog(LOG_WARNING,"ActivityApi: ".$q);
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            //syslog(LOG_WARNING,"ActivityApi: ".$f[0]);
            return $f[0];
        } else {
            //syslog(LOG_WARNING,"ActivityApi: ".pg_last_error());
        }
        return 'America/Los_Angeles'; // 2
    }

    public static function getCity($db, $city, $country_code, $latitude='', $longitude='')
    {
        $q = "SELECT id  FROM geofence_area_city WHERE (LOWER(city) ILIKE '" . strtolower($city) . "' AND country='" . $country_code . "') OR (latitude = '${latitude}' AND longitude ='${longitude}') LIMIT 1";
        $r = pg_query($db, $q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            return $f['id'];
        } else {
            return null;
        }

    }

    public static function insertCity($db, $city, $country_code, $latitude, $longitude)
    {
        $i = "INSERT INTO geofence_area_city (city,country,latitude,longitude,location) VALUES ('${city}','${country_code}',${latitude},${longitude},ST_SetSRID(ST_MakePoint(${longitude},${latitude}), 4326)) RETURNING id";
        $city_id=null;
        $r = pg_query($db, $i);
        if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
            $city_id = $f[0];
        }
        return $city_id;
    }

    public function getClosestPostalCode($latitude, $longitude, $country, $distance = 50000)
    {
        $q = "SELECT postal,
    ST_DistanceSphere(geometry::geometry, ST_SetSRID(ST_MakePoint(" . $longitude . ", " . $latitude . "),4326)) AS distance
      FROM geoname_postal_code
      WHERE ST_DistanceSphere(geometry::geometry, ST_SetSRID(ST_MakePoint(" . $longitude . ", " . $latitude . "),4326)) BETWEEN 0 AND " . $distance . " AND country='" . $country . "'
      ORDER BY distance
      LIMIT 1;";
        $r = pg_query($q);
        if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
            return $f['postal'];
        }

        return null;
    }

}

// vi:ts=2