<?php

echo "[".date("Y-m-d H:i:s")."] parsedemail_item_cleanup 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);

$q = "SELECT t.member_id,travel_date ,transport_provider_id,location_start_id,location_end_id, duration, cost, count(*) 
FROM parsedemail_item p INNER JOIN trackedemail_item t on p.trackedemail_item_id=t.id
WHERE cost > 0 and member_id>0 AND dup_id is NULL
GROUP BY t.member_id,travel_date ,transport_provider_id,location_start_id,location_end_id, duration, cost
HAVING count(*)>1
ORDER BY count(*) DESC,member_id ASC";
//$q = "SELECT id FROM parsedemail_item WHERE id>20209";
$r = pg_query($pgconn, $q);
while ($f=pg_fetch_assoc($r)) {
    $condition = "travel_date ='".$f['travel_date']."' and duration=".$f['duration']." and cost=".$f['cost']." and transport_provider_id=".$f['transport_provider_id']." AND member_id=".$f['member_id'];
    $keep = getKeepId($condition);
    
    while ($k = pg_fetch_assoc($keep)) {
        $id = $k['id'];
        $conditionUpdate = $condition.=" AND p.id!=".$id;
        updateDuplicate($conditionUpdate,$id);
        //var_dump($k);exit;
    }

}

pg_close($pgconn); 
function getKeepId($condition)
{
    global $pgconn;
    $q = "SELECT p.id,member_id, t.id as trackedemail_item_id,travel_date, travel_date_end,transport_provider_id,location_start_id,location_end_id 
    FROM parsedemail_item p INNER JOIN trackedemail_item t on p.trackedemail_item_id=t.id 
    WHERE ".$condition."
    ORDER BY p.id ASC LIMIT 1";echo $q."\n";
    $r = pg_query($pgconn, $q);
    return $r;
}

function updateDuplicate($condition,$dup_id){
    global $pgconn;
    $q="UPDATE 
            parsedemail_item p
        SET 
            dup_id=".$dup_id."
        FROM 
            trackedemail_item t
        WHERE 
            p.trackedemail_item_id = t.id
        AND ".$condition."";
        echo $q."\n";
    $r = pg_query($pgconn, $q);
    return $r;

}

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