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

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

$q = "SELECT b.id FROM parsedemail_item b LEFT JOIN address c ON (c.id=b.location_start_id) LEFT JOIN address d ON (d.id=b.location_end_id) WHERE (c.country='US' AND d.country='SG') OR (c.country='SG' AND d.country='US')";
//$q = "SELECT id FROM parsedemail_item WHERE id>20209";
$r = pg_query($readOnlyReplicaConn, $q);
while ($f=pg_fetch_row($r)) {
    echo "[".date("Y-m-d H:i:s")."] Cleaning up => ".$f[0]."\n";
    $q = "DELETE FROM parsedemail_item_advice_schedule WHERE parsedemail_item_id=".$f[0];
    $s = pg_query($pgconn, $q);
    echo "\t\tparsedemail_item_advice_schedule => ".pg_affected_rows($s)."\n";
    $q = "DELETE FROM google_directions_leg_step_details WHERE google_directions_leg_step_id IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=".$f[0].")))";
    $s = pg_query($pgconn, $q);
    echo "\t\tgoogle_directions_leg_step_details => ".pg_affected_rows($s)."\n";
    $q = "DELETE FROM leg_step_quote WHERE google_directions_leg_step_id IN (SELECT id FROM google_directions_leg_steps WHERE google_directions_leg_id IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=".$f[0].")))";
    $s = pg_query($pgconn, $q);
    echo "\t\tleg_step_quote => ".pg_affected_rows($s)."\n";
    $q = "DELETE FROM google_directions_leg_steps WHERE google_directions_leg_id IN (SELECT id FROM google_directions_legs WHERE parsedemail_item_advice_google_id IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=".$f[0]."))";
    $s = pg_query($pgconn, $q);
    echo "\t\tgoogle_directions_leg_stesp => ".pg_affected_rows($s)."\n";
    $q = "DELETE FROM google_directions_legs WHERE parsedemail_item_advice_google_id IN (SELECT id FROM parsedemail_item_advice_google WHERE parsedemail_item_id=".$f[0].")";
    $s = pg_query($pgconn, $q);
    echo "\t\tgoogle_directions_legs => ".pg_affected_rows($s)."\n";
    $q = "DELETE FROM parsedemail_item_advice_google WHERE parsedemail_item_id=".$f[0];
    $s = pg_query($pgconn, $q);
    echo "\t\tparsedemail_item_advice_google => ".pg_affected_rows($s)."\n";
    $q = "DELETE FROM parsedemail_item_extra WHERE parsedemail_item_id=".$f[0];
    $s = pg_query($pgconn, $q);
    echo "\t\tparsedemail_item_extra => ".pg_affected_rows($s)."\n";
    $q = "DELETE FROM parsedemail_item WHERE id=".$f[0];
    $s = pg_query($pgconn, $q);
    echo "\t\tparsedemail_item => ".pg_affected_rows($s)."\n";
    echo pg_last_error($pgconn)."\n";
}

pg_close($pgconn);
pg_close($readOnlyReplicaConn);

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