<?php

echo "[" . date("Y-m-d H:i:s") . "] GPS geocode_fix_address job is starting.\n";

require 'common/utils.php';
require 'common/config.php';
require 'common/address_city.php';
require 'common/Geocode.php';

$conditions = [
    "address ilike '%123 Yishun Street 11, Singapore%'",
];
$tables = getForeignTables('address');
foreach ($conditions as $condition) {

    $addresses = getAddressesByCondition($condition);
    $ids = [];
    while ($fa = pg_fetch_assoc($addresses)) {
        $ids[] = $fa['id'];
    }
    $id_keep = array_shift($ids);
    if (count($ids) > 0) {
        $sql = '';
        foreach ($tables as $ft) {
            $table_name = $ft['table_name'];
            $field = $ft['column_name'];
            if ($table_name == 'tourist_attraction') {
                continue;
            }

            $data_update = [
                $field => $id_keep,
            ];
            $condition_update_foreigns = "{$field} IN (" . implode(",", $ids) . ")";
            updateDataTable($table_name, $condition_update_foreigns, $data_update);
        }
        deleteDataTable('tourist_attraction', ' address_id IN(' . implode(",", $ids) . ')');
        deleteDataTable('address', ' id IN(' . implode(",", $ids) . ')');
    }
}
echo "[" . date("Y-m-d H:i:s") . "] GPS geocode_fix_address job complete.\n";

function getAddressesByCondition($condition)
{
    global $readOnlyReplicaConn;
    $q = "SELECT *
            FROM address
            WHERE " . $condition."
            ORDER BY id desc";
    $r = pg_query($readOnlyReplicaConn, $q);
    return $r;
}