<?php

require '../backend.php';

global $readOnlyReplicaConn, $pgconn_gps;
//PostgreSQL
$gps_db_host = $savvyext->cfgReadChar('gpsdatabase.host');
$gps_db_port = $savvyext->cfgReadLong('gpsdatabase.port');
$gps_db_name = $savvyext->cfgReadChar('gpsdatabase.name');
$gps_db_user = $savvyext->cfgReadChar('gpsdatabase.user');
$gps_db_pass = $savvyext->cfgReadChar('gpsdatabase.pass');
$conn_string_gps = "host=${gps_db_host} port=${gps_db_port} dbname=${gps_db_name} user=${gps_db_user} password=${gps_db_pass}";
$pgconn_gps = pg_connect($conn_string_gps);

$db_host = $savvyext->cfgReadChar('database_replica.host');
$db_name = $savvyext->cfgReadChar('database_replica.name');
$db_user = $savvyext->cfgReadChar('database_replica.user');
$db_pass = $savvyext->cfgReadChar('database_replica.pass');
$db_port = $savvyext->cfgReadLong('database_replica.port');
$readOnlyReplicaConnstr = "host=${db_host} port=${db_port} dbname=${db_name} user=${db_user} password=${db_pass}";
$readOnlyReplicaConn = pg_connect($readOnlyReplicaConnstr);

// Get max id from GPS DB
$q = "SELECT max(id) FROM parsedemail_item";
$r = pg_query($pgconn_gps, $q);
$id = 0;
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
  $id = (int)$f[0];
}

// Get newer records from the main DB
$q = "SELECT a.id,b.latitude AS location_start_lat,b.longitude AS location_start_lng,c.latitude AS location_end_lat,c.longitude AS location_end_lng ";
$q.= " FROM parsedemail_item a, address b, address c WHERE b.id=a.location_start_id AND c.id=a.location_end_id AND a.id>${id} ORDER BY a.id";
$r = pg_query($readOnlyReplicaConn, $q);
while ($f=pg_fetch_assoc($r)) {
  if ($f["location_start_lat"]==0 && $f["location_start_lng"]==0 && $f["location_end_lat"]==0 && $f["location_end_lng"]==0) {
    continue; // Bad GPS coordinates - cannot work with it...
  }
  $q = "INSERT INTO parsedemail_item (id,location_start_lat,location_start_lng,location_start,location_end_lat,location_end_lng,location_end) VALUES(";
  $q.= $f["id"].",".$f["location_start_lat"].",".$f["location_start_lng"].",st_setsrid(st_point(".$f["location_start_lng"].",".$f["location_start_lat"]."),4326),";
  $q.= $f["location_end_lat"].",".$f["location_end_lng"].",st_setsrid(st_point(".$f["location_end_lng"].",".$f["location_end_lat"]."),4326)";
  $q.= ")";
  //echo "$q\n";
  pg_query($pgconn_gps,$q);
}

pg_close($readOnlyReplicaConn);
pg_close($pgconn_gps);