<?php

function getAddressId($address)
{
    global $googleKey, $readOnlyReplicaConn;
    // Check the address exsts
    $q = "SELECT id FROM address WHERE lower(address)=lower('${address}') ";
    $q .= "AND latitude<>0 AND longitude<>0 AND geocoding_date IS NOT NULL ";
    $q .= "ORDER BY geocoding_date DESC LIMIT 1";
    error_log($q);
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
        return $f[0];
    }
    return null;
}

/* save data to table address */
function saveAddress($result)
{
    global $conn;

    syslog(LOG_WARNING, 'MessageDataSaver::saveAddress($result)');
    $db_lat = floatval($result["lat"]);
    $db_lng = floatval($result["lng"]);
    $db_address = pg_escape_string($result["address"]);
    $db_postal = pg_escape_string($result["postal"]);
    $db_city = pg_escape_string($result["city"]);
    $db_city_lat = floatval($result["city_lat"]);
    $db_city_lng = floatval($result["city_lng"]);
    $db_country = pg_escape_string($result["country"]);
    $db_tz = getTimeZoneID($result["timeZoneId"]);
    $city_id = getCityId($db_city, $db_country, $db_city_lat, $db_city_lng);
    if (empty($db_postal)) {
        $db_postal = getPostalCode($db_lat, $db_lng, $db_country);
    }
    $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) . ",now(),'${db_postal}','${db_country}',ST_SetSRID(ST_MakePoint(${db_lng},${db_lat}), 4326),'${city_id}')";
    $q .= " RETURNING id";
    error_log($q);
    $r = pg_query($conn, $q);
    if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
        return $f[0];
    }
    return null;
}

function getTzByAddressId($id)
{
    global $readOnlyReplicaConn;

    $q = "SELECT timezone FROM address_timezone WHERE id=(SELECT timezone FROM address WHERE id=" . ((int) $id) . ")";
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
        return $f[0];
    }
    return 'America/Los_Angeles'; // 2
}

function getTimeZoneID($name)
{
    global $readOnlyReplicaConn, $conn;

    $q = "SELECT id FROM address_timezone WHERE timezone='" . pg_escape_string($name) . "'";
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
        return $f[0];
    }
    $q = "INSERT INTO address_timezone (timezone) VALUES('" . pg_escape_string($name) . "') RETURNING id";
    $r = pg_query($conn, $q);
    if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
        return $f[0];
    }
    return null;
}

function getCityId($city, $country_code, $latitude, $longitude)
{
    global $readOnlyReplicaConn, $conn;
    $q = "SELECT id  FROM geofence_area_city WHERE LOWER(city) ILIKE '" . strtolower(pg_escape_string($city)) . "' AND country='" . $country_code . "' OR (latitude='" . $latitude . "' AND longitude='" . $longitude . "') LIMIT 1";
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
        return $f['id'];
    }
    $i = "INSERT INTO geofence_area_city (city,country,latitude,longitude,location) VALUES ('" . pg_escape_string($city) . "','${country_code}',${latitude},${longitude},ST_SetSRID(ST_MakePoint(${longitude},${latitude}), 4326)) RETURNING id";
    $r = pg_query($conn, $i);
    if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
        return $f[0];
    }
    return null;
}

function deleteCities($ids)
{
    global $conn;
    if (is_array($ids) && count($ids) > 0) {
        $q1 = "DELETE FROM geofence_area_city WHERE  id IN (" . implode(",", $ids) . ")";
        $r1 = pg_query($conn, $q1);
        if ($r1 && pg_affected_rows($r1)) {
            return true;
        }
    }
    return false;
}

function deleteDataTable($table_name, $condition = "")
{
    global $conn;
    if (!empty($condition)) {
        $q1 = "DELETE FROM " . $table_name . " WHERE  " . $condition;
        $r1 = pg_query($conn, $q1);
        if ($r1 && pg_affected_rows($r1)) {
            return true;
        }
    }
    return false;
}

function updateDataTable($table_name, $condition, $data)
{
    global $conn, $readOnlyReplicaConn;
    $q = "select column_name, data_type, character_maximum_length from INFORMATION_SCHEMA.COLUMNS where table_name = '" . $table_name . "'";

    $r = pg_query($readOnlyReplicaConn, $q);
    while ($f = pg_fetch_assoc($r)) {
        $columns[$f['column_name']] = $f['data_type'];
    }
    $q = "UPDATE " . $table_name . " 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 . "";
    echo $q . "\n";
    $r = pg_query($conn, $q);

    $updated = pg_affected_rows($r);
    echo "[" . date("Y-m-d H:i:s") . "]  update  {$updated} rows of {$table_name} with {$condition} .\n";
    return $updated;
}

function getForeignTables($table_name)
{
    global $readOnlyReplicaConn;
    $q = "SELECT
            tc.table_name, kcu.column_name,
            ccu.table_name AS foreign_table_name,
            ccu.column_name AS foreign_column_name
        FROM
            information_schema.table_constraints AS tc
            JOIN information_schema.key_column_usage AS kcu
            ON tc.constraint_name = kcu.constraint_name
            JOIN information_schema.constraint_column_usage AS ccu
            ON ccu.constraint_name = tc.constraint_name
        WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='" . $table_name . "'";
    $r = pg_query($readOnlyReplicaConn, $q);

    $result = [];
    while ($f = pg_fetch_assoc($r)) {
        $result[] = $f;
    }
    return $result;
}

function getGeoName($city, $country_code)
{
    global $readOnlyReplicaConn;
    $q = "SELECT name,country,latitude,longitude,location FROM geoname WHERE LOWER(name) ILIKE '" . strtolower(pg_escape_string($city)) . "' AND country='" . $country_code . "' LIMIT 1";
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
        return $f;
    } else {
        return null;
    }
}

function insertPostalCode($geoname, $postal, $latitude, $longitude, $country)
{
    global $conn, $readOnlyReplicaConn;

    if (strlen($geoname) > 100) {
        $geoname = substr($geoname, 0, 99);
    }
    $q = "SELECT id  FROM geoname_postal_code WHERE postal ILIKE '" . pg_escape_string($postal) . "' AND  latitude='" . $latitude . "' AND longitude='" . $longitude . "' LIMIT 1";
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
        return $f['id'];
    }
    $i = "INSERT INTO geoname_postal_code (geoname, postal, latitude,longitude, country, geometry) VALUES ('" . pg_escape_string($geoname) . "','" . pg_escape_string($postal) . "',${latitude},${longitude},'" . pg_escape_string($country) . "',ST_SetSRID(ST_MakePoint(${longitude},${latitude}), 4326)) RETURNING id";
    $r = pg_query($conn, $i);
    if ($r && pg_num_rows($r) && $f = pg_fetch_row($r)) {
        return $f[0];
    }
    return null;
}

function getPostalCode($latitude, $longitude, $country, $distance = 50000)
{
    global $readOnlyReplicaConn;

    $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($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f = pg_fetch_assoc($r)) {
        return $f['postal'];
    }

    return null;
}

/**
 * get postal array from latitude, longitude
 */
function getPostalFromLatLng($latitude, $longitude, int $distance_in_km)
{
    global $conn, $readOnlyReplicaConn;
    $earth_radius = 6371;
    $km_per_degree_lat = 111.2;
    $pi = 3.14 / 180;

    $query = " SELECT postal FROM (
            SELECT (
                $earth_radius * acos(
                cos( radians( {$latitude}) )
                * cos( radians( latitude  ) )
                * cos( radians( longitude ) - radians( {$longitude} ) )
                + sin( radians( {$latitude}) )
                * sin( radians( latitude  ) )
            )
            ) AS distance
            , *
            FROM postal_code
            WHERE
            postal IS NOT NULL
            AND latitude BETWEEN " . ($latitude - ($distance_in_km / $km_per_degree_lat)) .
        " AND " . ($latitude + ($distance_in_km / $km_per_degree_lat));

    $delta = round($distance_in_km / ($km_per_degree_lat * COS(deg2rad($longitude))), 10);

    // Bounding box for speed - latitude within range (longtitude to km not linear)
    if (cos($longitude * $pi) > 0) {

        $from = $longitude - $delta;
        $to = $longitude + $delta;

        $query .=
            " AND address.longitude" .
            " BETWEEN " . $from .
            " AND " . $to;

    } else {

        $from = $longitude + $delta;
        $to = $longitude - $delta;

        $query .=
            " AND address.longitude" .
            " BETWEEN " . $from .
            " AND " . $to;

    }

    // distance limit for circle
    $query .= " ) address
            WHERE distance < " . $distance_in_km;

    $rs = pg_query($readOnlyReplicaConn, $query) or die("Cannot execute query: $query\n");

    if (!$rs) {
        echo "An error occurred.\n";
        exit;
    }

    return array_column(pg_fetch_all($rs), 'postal');
}

function insertActionLog($table_name="quotes",$operation="",$input_data="", $response_data="", $url="")
{
    global $conn;

    $input_data = is_array($input_data) || is_object($input_data) ? json_encode($input_data) : $input_data;
    $response_data = is_array($response_data) || is_object($response_data) ? json_encode($response_data) : $response_data;
    $q = "INSERT INTO action_logs (created,table_name,operation,input_data,response_data, url) ";
    $q.= " VALUES(NOW(),'".$table_name."','".$operation."',";
    $q.= "'".pg_escape_string($input_data)."','".pg_escape_string($response_data)."','".$url."') RETURNING id";
    $r = pg_query($conn, $q);
    if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
        return true;
    }
    return null;
}

function parseAddressByCountry($address = "", $country_code = "")
{
    $city = $state = $country = "";
    if ($country_code == "AE") { //United Arab Emirates
        $address_info = explode("-", $address);
    } else {
        $address_info = explode(",", $address);
    }

    switch ($country_code) {
        case "AU": //Australia
            $country = trim(array_pop($address_info));
            $state_city = explode(" ", trim(array_pop($address_info)));
            $postal = trim(array_pop($state_city));
            $state = trim(array_pop($state_city));
            $city = trim(array_pop($state_city));
            break;
        case "BR": //Brazil
            $country = trim(array_pop($address_info));
            $state = trim(array_pop($address_info));
            $city = array_pop($address_info);
            $city = explode('-', $city);
            $city = trim($city[0]);
            break;
        case "SG":
            $country = $city = "Singapore";
            break;
        case "HK":
            $country = $city = "Hong Kong";
            break;
        case "US":
            $country = trim(array_pop($address_info));
            $state = trim(array_pop($address_info));
            $city = $state;
            if (preg_replace('/[^0-9]/', '', $state) != "") {
                $city = trim(array_pop($address_info));
            }
            break;
        case "CA": //Canada
        case "IN": //India
        case "ZA": //South Africa
        case "MY": //Malaysia
        case "PH": //Philippines
            $country = trim(array_pop($address_info));
            $state = trim(array_pop($address_info));
            $city = trim(array_pop($address_info));
            if ($country_code == "MY") {
                $city = parseName($city);
            }
            break;
        case "ID": //Indonesia
            $country = trim(array_pop($address_info));
            $city = trim(array_pop($address_info));
            $city = parseName($city);
            $city_info = explode(" ", $city);
            $city = trim(array_pop($city_info));
            break;
        case "AT": //Autria
        case "FI": //Finland
        case "SE": //Sweden
        case "BE": //Belgium
        case "DE": //Germany
        case "FR": //France
        case "TH": //Thailand
        case "NP": //Nepal
            $country = trim(array_pop($address_info));
            $city = trim(array_pop($address_info));
            $city = parseName($city);
            break;

        case "NZ": //New Zealand
            $country = trim(array_pop($address_info));
            $city = trim(array_pop($address_info));
            $city = parseName($city);
            if ($city == "") {
                $city = trim(array_pop($address_info));
            }
            break;

        case "NL": //Netherlands
            $country = trim(array_pop($address_info));
            $city = trim(array_pop($address_info));
            $city = parseName($city);
            $city = explode(' ', $city);
            $city = trim(array_pop($city));
            break;

        /*case "TR"://Turkey
        $country = trim(array_pop($address_info));
        $city = array_pop($address_info);
        $city = explode('/', $city);
        $city = trim(array_pop($city));
        break;*/
        case "UA": //Ukraina
            $count_address = count($address_info);

            if ($count_address < 5) {
                $country = trim(array_pop($address_info));
                $city = trim(array_pop($address_info));
            } else {
                if ($count_address == 5) {
                    $country = $address_info[$count_address - 2];
                    $city = $address_info[$count_address - 3];
                } else {
                    $country = $address_info[$count_address - 2];
                    $city = $address_info[$count_address - 4];
                }
            }
            $city = parseName($city);
            break;
        case "GB": //Great Britain
            $country = trim(array_pop($address_info));
            $city = trim(array_pop($address_info));
            $city = explode(' ', $city);
            $city = trim($city[0]);
            break;

        case "UA": //Ukraina
        case "TR": //Turkey
        case "PL": //Poland
        case "RO": //Romania
            $city = "";
            break;
        case "TW": //Taiwan
            $country = trim(array_pop($address_info));
            $city = array_pop($address_info);
            break;
        default:
            $country = trim(array_pop($address_info));
            $city = trim(array_pop($address_info));
    }

    return [
        "address" => $address,
        "city" => $city,
        "state" => $state,
        "country_code" => $country_code,
        "country" => $country,
    ];

}

function parseName($name)
{
    $name = trim(preg_replace('/[0-9]+/', '', $name));
    return $name;
}