<?php

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

require('./lock.php');
$lock_file = lock_pid_file();

require('../backend.php');

$httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');

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

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

if ($con===FALSE || $readOnlyReplicaConn===FALSE) {
  unlock_pid_file($lock_file);
  die ("Could not connect to server\n");
}
$rate = 1.1;
$numberOfDays = 365; // This is handled by update cheapest data job now...

/*
// Init
INSERT INTO global_settings (key,description,value) VALUES ('last_surge_price_parsedemail_item','',0);
INSERT INTO global_settings (key,description,value) VALUES ('last_surge_price_quote','',0);
// Reset
TRUNCATE TABLE trip_surge_price;
UPDATE global_settings SET value='0' WHERE key='last_surge_price_parsedemail_item';
UPDATE global_settings SET value='0' WHERE key='last_surge_price_quote';
*/

// 1. Box the data set (box is previous data and max compared items)
$min_parsedemail_item_id = get_global_setting('last_surge_price_parsedemail_item',0);
$min_quote_id = get_global_setting('last_surge_price_quote',0);
$max_parsedemail_item_id = get_global_setting('last_compared_parsedemail_item',0);
$max_quote_id = get_global_setting('last_compared_quote',0);

if ($min_parsedemail_item_id>=$max_parsedemail_item_id && $min_quote_id>=$max_quote_id) {
  echo "[".date("Y-m-d H:i:s")."] No new records to compare. Did you run the update_cheapest_data job?\n";
  unlock_pid_file($lock_file);
  exit();
}

// 1.1. Select all the trips bove average inside the boxed set
$q = "SELECT * FROM trip_price_comparison ";
$q.= " WHERE ";
$q.= " ((data_source=1 AND data_source_id>${min_parsedemail_item_id} AND data_source_id<=${max_parsedemail_item_id}) ";
$q.= " OR (data_source=2 AND data_source_id>${min_quote_id} AND data_source_id<=${max_quote_id})) ";
$q.= " AND cost>=${rate}*average";

$r = pg_query($readOnlyReplicaConn, $q);
$i = 1;

// 2. Iterate through the trips
if ($r && pg_num_rows($r)) {
  $total = pg_num_rows($r);
  echo "[".date("Y-m-d H:i:s")."] Processing ${total} new records\n";
  while ($f = pg_fetch_assoc($r)) {

    echo "[".date("Y-m-d H:i:s")."] Processing record ${i}/${total} (".sprintf("%0.02f",100.0*$i/$total)."%)\n";

    // 3. Insert record into trip_surge_price table
    $query = "
          INSERT INTO trip_surge_price (data_source_id, data_source)
          VALUES('".((int)$f["data_source_id"])."', '".((int)$f["data_source"])."') RETURNING id
        ";
    $result = pg_query($con, $query);
    if ($result && pg_num_rows($result) && $data=pg_fetch_row($result)) {
      echo "[".date("Y-m-d H:i:s")."] New surge record: id=".$data[0]." (" . $f["data_source_id"] . "," . $f["data_source"] . ")\n";
    } else {
      echo "[".date("Y-m-d H:i:s")."] Failed to create new surge record: ".pg_last_error($con)."\n";
      echo "[".date("Y-m-d H:i:s")."] ".trim($query)."\n";
    }
    $i++;
    //if ($i>100) break; // DEBUG;
  }
}

update_global_setting('last_surge_price_parsedemail_item',$max_parsedemail_item_id);
update_global_setting('last_surge_price_quote',$max_quote_id);

pg_close($con);
pg_close($readOnlyReplicaConn);

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

/*********************************** Function ***********************************/

function get_global_setting($key,$val=0) {
  global $readOnlyReplicaConn;
  $q = "SELECT value FROM global_settings WHERE lower(key)=lower('".pg_escape_string($key)."')";
  $r = pg_query($readOnlyReplicaConn,$q);
  if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
    return $f[0];
  }
  return $val;
}

function update_global_setting($key,$val) {
  global $con;
  $q = "UPDATE global_settings SET value='".pg_escape_string($val)."' WHERE lower(key)=lower('".pg_escape_string($key)."')";
  $r = pg_query($con,$q);
  if ($r) {
    return pg_affected_rows($r);
  }
  return 0;
}

unlock_pid_file($lock_file);

?>