<?php
require '../backend.php';

// We will not limit the SQL we will exhaust the allowed memory
// we will process data in ${hard_limit} batches and hopefully catch up
$hard_limit = 500000;

//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);

$q = "select * from members_tracking where duration<0";
$r = pg_query($pgconn_gps, $q);
if ($r && pg_num_rows($r)) {
    while ($f=pg_fetch_assoc($r)) {
        echo "[".date("Y-m-d H:i:s")."] Processing member_id=".$f["member_id"]."...\n";
        $nid = $f["id"];
        $gps = $f["gps"];
        $ttime = $f["ttime"];
        $device_id = $f["device_id"];
        $member_id = $f["member_id"];
        //if ($f[0]==22) continue; // Crash!
        $q1 = "SELECT id AS pid, ST_Distance(gps,'${gps}'::geometry) AS distance,";
        $q1.= "EXTRACT(epoch FROM '${ttime}'::timestamp-ttime) AS duration ";
        $q1.= "FROM members_tracking ";
        $q1.= "WHERE member_id=${member_id} AND ttime<'${ttime}'::timestamp AND ";
        $q1.= ($device_id>0 ? "device_id=${device_id}" : "device_id IS NULL");
        $q1.= " ORDER BY ttime DESC LIMIT 1";
        $r1 = pg_query($pgconn_gps, $q1);
        if ($r1 && pg_num_rows($r1) && $f1=pg_fetch_assoc($r1)) {
            $q2 = "UPDATE members_tracking SET previous_id=".$f1["pid"].",distance=".((int)$f1["distance"]).",duration=".((int)(1000*$f1["duration"]));
            $q2.= " WHERE id=${nid}";
            $r2 = pg_query($pgconn_gps, $q2);
        } else {
            echo "[".date("Y-m-d H:i:s")."] Failure - ".pg_last_error($pgconn_gps)."\n";
            //echo "$q1\n";
            $q2 = "UPDATE members_tracking SET previous_id=NULL,distance=NULL,duration=NULL WHERE id=${nid}";
            $r2 = pg_query($pgconn_gps, $q2); 
        }
    }
}

pg_close($pgconn_gps);