<?php

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

require '../backend.php';

global $pgconn;

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

$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 travel_time is measured with seconds*/
$total = $success = $failed = 0;

$r=get_negative_durations();
while ($f = pg_fetch_array($r, NULL, PGSQL_ASSOC)) {
    $total++;
    $id = $f['id'];
    $duration = $f['duration'];
    $travel_date = $f['travel_date'];
    $transport_provider_id = $f['transport_provider_id'];
    
    if (in_array($transport_provider_id, [1,5])) {//Uber, GOJEK
        $travel_date_end = $f['new_travel_date_end'];
       if(empty($duration)){
           $duration = $f['new_duration'];
       }
    } else {
        $new_travel_date_end = (new DateTime($f['travel_date']))->modify('+'.$duration.' minutes');
        $travel_date_end=$new_travel_date_end->format('Y-m-d H:i:s');
    }

    $condition = "id=".$id;
        $data = [
        "duration" => $duration,
        "transport_provider_id" => $transport_provider_id,
        "travel_date" => $travel_date,
        "travel_date_end" => $travel_date_end
    ];
    $updated = update_data($data, $condition);
    if ($updated > 0) {
        $success++;
    } else {
        $failed++;
    }
   
}

echo "Total: " . $total . ".\n";
echo "Success: : " . $success . ".\n";
echo "Failed: : " . $failed . ".\n";
pg_close($pgconn);

echo "[" . date("Y-m-d H:i:s") . "] parsedemail_item_fix_time_travel job complete.\n";

function get_negative_durations(){
    global $readOnlyReplicaConn;
    $q = "SELECT p.id,
            duration,
            DATE_PART('hour', travel_date_end - travel_date)*60+DATE_PART('minute', travel_date_end - travel_date) AS new_duration,
            travel_date,
            travel_date_end,
            travel_date_end + interval '1' day as new_travel_date_end,
            transport_provider_id,
            message,
            message_date
            FROM parsedemail_item p INNER JOIN trackedemail_item t on p.trackedemail_item_id=t.id
            WHERE transport_provider_id IS NOT NULL 
            GROUP BY p.id,
                duration,
                DATE_PART('hour', travel_date_end - travel_date)*60+DATE_PART('minute', travel_date_end - travel_date),
                travel_date,
                travel_date_end,
                travel_date_end + interval '1' day,
                transport_provider_id,
                message,
                message_date
            HAVING DATE_PART('hour', travel_date_end - travel_date)*60+DATE_PART('minute', travel_date_end - travel_date)<0
            ORDER BY transport_provider_id ASC";
    return pg_query($readOnlyReplicaConn, $q);
}

function update_data($data, $condition="1=1")
{
    global $pgconn;
    if (count($data)>0) {
        $data_update="updated=NOW()";
        foreach ($data as $field => $val) {
            $data_update .= ", ${field}=" . ($val == 'NULL' ? "NULL" : "'${val}'");
        }
        $q = "UPDATE parsedemail_item SET " . $data_update . " WHERE " . $condition . "";
        $r = pg_query($pgconn, $q);
        if ($r) {
            return pg_affected_rows($r);
        }
    }
    return 0;
}