<?php

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

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) or die ("Could not connect to server\n");

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

/*
// Init
INSERT INTO global_settings (key,description,value) VALUES ('last_holiday_parsedemail_item','',0);
INSERT INTO global_settings (key,description,value) VALUES ('last_holiday_quote','',0);
// Reset
TRUNCATE TABLE trip_holidays;
UPDATE global_settings SET value='0' WHERE key='last_holiday_parsedemail_item';
UPDATE global_settings SET value='0' WHERE key='last_holiday_quote';
*/

// 1. Box the data set (select min id from trip_holidays and select max id on parsedemail_item and quotes)
$min_parsedemail_item_id = get_global_setting('last_holiday_parsedemail_item',0);
$min_quote_id = get_global_setting('last_holiday_quote',0);
$max_parsedemail_item_id = get_max_id('parsedemail_item');
$max_quote_id = get_max_id('quotes');

// 1.1. Select all the trips order by date from the oldest to the newest inside the boxed set
$q = "SELECT  a.data_source_id AS data_source_id,
              a.data_source    AS data_source,
              a.cost           AS normal_cost,
              h.id             AS holiday_id";
$q.= " FROM union_trip_and_quote_view_table a ";
$q.= " LEFT JOIN address a1 ON a1.id = a.location_start_id ";
$q.= " LEFT JOIN address a2 ON a2.id = a.location_end_id ";
$q.= " LEFT JOIN holidays h ON (h.date=travel_date::DATE AND h.country=a1.country) ";
$q.= " WHERE ((a.data_source=1 AND a.data_source_id>${min_parsedemail_item_id} AND a.data_source_id<=${max_parsedemail_item_id})";
$q.= " OR (a.data_source=2 AND a.data_source_id>${min_quote_id} AND a.data_source_id<=${max_quote_id}))";
$q.= " AND a.cost>0 AND h.id IS NOT NULL ORDER BY a.travel_date ASC";

$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_holidays (data_source_id, data_source, holiday_id, normal_cost)
          VALUES( '".((int)$f["data_source_id"])."', '".((int)$f["data_source"])."', 
                  '".((int)$f["holiday_id"])."', NULL) 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 holiday 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 holidays 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_holiday_parsedemail_item',$max_parsedemail_item_id);
update_global_setting('last_holiday_quote',$max_quote_id);

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

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

function get_max_id($what) {
  global $readOnlyReplicaConn;
  $q = "SELECT max(id) FROM ${what}";
  $r = pg_query($readOnlyReplicaConn,$q);
  if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
    return $f[0];
  }
  return 0;
}
?>