<?php

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

$q = "SELECT a.fare_raw,a.distance_raw,a.distance,a.board,a.alight,a.service,b.distance as bdistance ";
$q.= " FROM leg_step_quote a, google_directions_leg_steps b WHERE a.fare_raw>0 AND b.id=a.google_directions_leg_step_id AND b.travel_mode='TRANSIT' ";
$q.= " AND (b.location_start_lat BETWEEN 1.207 AND 1.471) AND (b.location_start_lng BETWEEN 103.610 AND 104.060) ";
$q.= " AND (b.location_end_lat BETWEEN 1.207 AND 1.471) AND (b.location_end_lng BETWEEN 103.610 AND 104.060)";
echo $q;
$r = pg_query($readOnlyReplicaConn, $q);
while ($f=pg_fetch_assoc($r)) {
    if ($f["service"]=="MRT/LRT") {
        //process_past_transit_quote_mrtlrt($f);
    }
    if (substr($f["service"],0,8)=="Bus Svc ") {
        process_past_transit_quote_bus($f);
    }
}
/*
 fare_raw | distance_raw | distance |               board               |               alight               |   service   | bdistance
----------+--------------+----------+-----------------------------------+------------------------------------+-------------+-----------
      113 |          560 | 5.6 km   | 41049 - Opp Coronation Plaza      | 09179 - Royal Thai Embassy         | Bus Svc 174 |      5492
      100 |          200 | 2.0 km   | 13029 - Bef Tyersall Ave          | 14539 - Beach Station Bus Terminal | Bus Svc 123 |     14621
      100 |          200 | 2.0 km   | 13029 - Bef Tyersall Ave          | 14539 - Beach Station Bus Terminal | Bus Svc 123 |     14621
       83 |          170 | 1.7 km   | 91061 - Bet Hse No. 767/767A      | 92041 - Opp Parkway Parade         | Bus Svc 196 |      1787
       83 |          130 | 1.3 km   | 82051 - Tg Katong Rd Sth P/G      | 92041 - Opp Parkway Parade         | Bus Svc 48  |      1437
       83 |          240 | 2.4 km   | 11209 - Opp Peirce Rd             | 09179 - Royal Thai Embassy         | Bus Svc 7   |      2465
      135 |          860 | 8.6 km   | Buona Vista (EW21 / CC22)         | Raffles Place (NS26 / EW14)        | MRT/LRT     |      8585
      135 |          910 | 9.1 km   | Bishan (NS17 / CC15)              | Raffles Place (NS26 / EW14)        | MRT/LRT     |      9765
*/
echo "[".date("Y-m-d H:i:s")."] load_past_transit_quotes complete.\n";

pg_close($conn);

/*
 boarding_id | integer |
 alight_id   | integer |
 fare        | integer |
 distance    | integer |
*/
function process_past_transit_quote_mrtlrt($f) {
    global $conn;
    echo "[".date("Y-m-d H:i:s")."] process_past_transit_quote_mrtlrt()\n";
    // singapore_mrtlrt_stop_fares
    $boarding_id = get_mrtlrt_stop_id($f["board"]);
    $alight_id = get_mrtlrt_stop_id($f["board"]);
    if ($boarding_id>0 && $alight_id>0 && $f["fare_raw"]>0) {
        $q = "SELECT * FROM singapore_mrtlrt_stop_fares WHERE boarding_id=${boarding_id} AND alight_id=${alight_id}";
        $r = pg_query($readOnlyReplicaConn, $q);
        if ($r && pg_num_rows($r) && $o=pg_fetch_assoc($r)) {
            // UP?
            if ($o["fare"]>0) {
                $q = "";
            } else {
                $q = "UPDATE SET singapore_mrtlrt_stop_fares SET fare=%s,distance=%s WHERE id=".$o["id"];
            }
        } else {
            $q = "INSERT INTO singapore_mrtlrt_stop_fares (boarding_id,alight_id,fare,distance) VALUES (";
            $q.= "${boarding_id},${alight_id},%d,%d)";
        }
        if ($q!="") {
            $d = $f["distance_raw"]>0?((int)(10*$f["distance_raw"])):$f["bdistance"];
            $q = sprintf($q,(int)$f["fare_raw"],$d);
            pg_query($conn,$q);
            echo "q=${q}\n";
        } else {
            echo "Update is not required? fare=".$o["fare"]."\n";
        }
    } else {
        echo "ERROR: board(${boarding_id})='".$f["board"]."', alight(${alight_id})='".$f["alight"]."'\n";
    }
}

function process_past_transit_quote_bus($f) {
    echo "[".date("Y-m-d H:i:s")."] process_past_transit_quote_bus()\n";
    global $conn, $readOnlyReplicaConn;
    // singapore_bus_stop_fares
    $line = substr($f["service"],8);
    $boarding_id = get_bus_stop_id($f["board"],$f["service"]);
    $alight_id = get_bus_stop_id($f["alight"],$f["service"]);
    if ($boarding_id>0 && $alight_id>0 && $f["fare_raw"]>0) {
        if ($r && pg_num_rows($r) && $o=pg_fetch_assoc($r)) {
            // UP?
            if ($o["fare"]>0) {
                $q = "";
            } else {
                $q = "UPDATE SET singapore_bus_stop_fares SET fare=%s,distance=%s WHERE id=".$o["id"];
            }
        } else {
            $q = "INSERT INTO singapore_bus_stop_fares (boarding_id,alight_id,fare,distance) VALUES (";
            $q.= "${boarding_id},${alight_id},%d,%d)";
        }
        if ($q!="") {
            $d = $f["distance_raw"]>0?((int)(10*$f["distance_raw"])):$f["bdistance"];
            $q = sprintf($q,(int)$f["fare_raw"],$d);
            $r = pg_query($conn,$q);
            echo "q=${q}\n";
        } else {
            echo "Update is not required? fare=".$o["fare"]."\n";
        }    
    } else {
        echo "ERROR: board(${boarding_id})='".$f["board"]."', alight(${alight_id})='".$f["alight"]."'\n";
    }
}

function get_mrtlrt_stop_id($name) {
    global $readOnlyReplicaConn;
    $q = "select id from singapore_mrtlrt_stops_mytransport where name='${name}'";
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
        return $f[0];
    }
    return 0;
}

function get_bus_stop_id($str,$service) {
    global $readOnlyReplicaConn;
    echo "'${service}' => '${str}'\n";
    $line = substr($service,8);
    $pos = strpos($str,'-');
    if ($pos!==false) {
        $stop_code = trim(substr($str,0,$pos-1));
        $name = trim(substr($str,$pos+1));
    } else {
        $name = $str; // Will not work most likely...
        $stop_code = "";
    }
    $q = "select id from singapore_bus_stops where name='${name}' and stop_code='${stop_code}' and line='${line}'";
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
        return $f[0];
    }
    $q = "select id from singapore_bus_stops where stop_code='${stop_code}' and line='${line}'";
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
        return $f[0];
    } 
    $q = "select id from singapore_bus_stops where name='${name}' and line='${line}'";
    $r = pg_query($readOnlyReplicaConn, $q);
    if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
        return $f[0];
    }
    return 0;
}