<?php

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

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;
// GPS coordinate precision to assume the same location
$precision = 3;
$id = 0;

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

$q = 'select max(last_id) from members_tracking_duration';
$r = pg_query($q);
if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
    //$id = (int)$f[0];
}
echo "[".date("Y-m-d H:i:s")."] Last processed ID is ${id}\n";

/*
0 - id
1 - lat
2 - lng
3 - ttime
4 - member_id
5 - device_id
6 - gps
*/
$q = "select id,round(lat,${precision}) as lat,round(lng,${precision}) as lng,ttime,member_id,device_id,gps ";
$q.= " from members_tracking where id>${id} ";
$q.= " and member_id=22"; // DEBUG!
$q.= " order by member_id,device_id,ttime limit ${hard_limit}";
echo "\n$q\n";
$r = pg_query($q);

if ($r && pg_num_rows($r)) {
    echo "[".date("Y-m-d H:i:s")."] Records to process: ".pg_num_rows($r)."\n";
} else {
    echo "[".date("Y-m-d H:i:s")."] No new records to process. Stop.\n";
    pg_close($conn);
    exit();
}

echo "[".date("Y-m-d H:i:s")."] Processing";
$first_data = NULL;
$last_data = NULL;
$pool_data = [];
while ($f=pg_fetch_row($r)) {
    echo ".";
    if ($last_data!=NULL) {
        // member id
        if ($last_data[4]!=$f[4] || $last_data[5]!=$f[5] || // device_id
            $last_data[1]!=$f[1] || $last_data[2]!=$f[2]) { // gps coordinates
            // Process
            $q = "INSERT INTO members_tracking_duration (member_id,lat,lng,gps,first_id,last_id,first_time,last_time,duration,device_id) VALUES(";
            $q.= $last_data[4].",".$last_data[1].",".$last_data[2].","; // member_id,lat,lng
            $q.= "ST_SetSRID(ST_MakePoint(".$last_data[1].",".$last_data[2]."), 4326)::geography"; // GPS
            $q.= ",".$first_data[0].",".$last_data[0].",'".$first_data[3]."','".$last_data[3]."',"; // fist_id,last_id,first_time,last_time,duration
            $q.= "'".$last_data[3]."'::timestamp - '".$first_data[3]."'::timestamp,".($f[5]>0?$f[5]:"NULL").")"; // duration, device_id
            pg_query($q);
            //echo pg_last_error()."\n";
            $clear_data = [];
            foreach ($pool_data as $id) {
                if ($id!=$first_data[0] && $id!=$last_data[0]) {
                    $clear_data[] = $id;
                }
            }
            if (count($clear_data)>0) {
                $q1 = "DELETE FROM members_tracking WHERE id IN (".implode(",",$clear_data).")";
                $r1 = pg_query($q1);
                if ($r1 && pg_affected_rows($r1)) {
                    $q2 = "UPDATE members_tracking SET previous_id=".$first_data[0].",";
                    $q2.= "distance=ST_Distance(gps,'".$first_data[6]."'::geometry),";
                    $q2.= "duration=EXTRACT(epoch FROM ttime-'".$first_data[3]."'::timestamp)";
                    $q2.= " WHERE previous_id IN (".implode(",",$clear_data).",".$last_data[0].")";
                    $r2 = pg_query($q2);
                    echo "\n${q2}\n";
                    //echo pg_last_error()."\n";
                } else {
                    echo "\n";
                    echo "[".date("Y-m-d H:i:s")."] Failed - ".pg_last_error()."\n";
                    echo "${q1}\n";
                }
            }
            $first_data = NULL;
        }
    }
    if ($first_data==NULL) {
        $first_data = $f;
        $pool_data = [];
    }
    $pool_data[] = $f[0]; // id
    $last_data = $f;
}
echo "\n";

pg_close($conn);
echo "[".date("Y-m-d H:i:s")."] GPS member_tracking_duration job complete.\n";