<?php

echo "[".date("Y-m-d H:i:s")."] GPS singaport_mrtlrt_stop_fares 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 line,count(*) FROM singapore_bus_stops GROUP BY line';
$r = pg_query($readOnlyReplicaConn, $q);
while ($f=pg_fetch_row($r)) {
	singapore_bus_stops_line($f[0]);
}

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

function singapore_bus_stops_line($id) {
	global $readOnlyReplicaConn;
	$q = "SELECT * FROM singapore_bus_stops WHERE line='${id}'";
	$r = pg_query($readOnlyReplicaConn,$q);
	while ($from=pg_fetch_assoc($r)) {
		singapore_bus_stops_line_fares($id,$from);
	}
}

function singapore_bus_stops_line_fares($id,$from) {
	global $readOnlyReplicaConn;
	$q = "SELECT * FROM singapore_bus_stops WHERE line='${id}' AND id<>".$from["id"]." ORDER BY RANDOM()";
	$r = pg_query($readOnlyReplicaConn,$q);
	while ($to=pg_fetch_assoc($r)) {
		singapore_bus_stops_line_fare($id,$from,$to);
	}
}
/*
CREATE TABLE singapore_bus_stop_fares (id serial, boarding_id int references singapore_bus_stops(id), alight_id int references singapore_bus_stops(id), fare int, distance int, primary key(id), unique(boarding_id,alight_id));
*/
function singapore_bus_stops_line_fare($line,$from,$to) {
	global $conn, $readOnlyReplicaConn;
	echo "from=".$from["code"].", to=".$to["code"]."\n";
	$q = "SELECT * FROM singapore_bus_stop_fares WHERE boarding_id='".$from["id"]."'";
	$q.= " AND alight_id='".$to["id"]."'";
	$r = pg_query($readOnlyReplicaConn,$q);
	if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
		// Update
		if ($f["fare"]>0) {
			$q = "";
		} else {
			$q = "UPDATE singapore_bus_stop_fares SET fare=%d, distance=%d WHERE id=".$f["id"]." RETURNING id";
		}
	} else {
		// Insert
		$q = "INSERT INTO singapore_bus_stop_fares (boarding_id,alight_id,fare,distance) VALUES(";
		$q.= $from["id"].",".$to["id"].",%d,%d) RETURNING id";
	}
	if ($q!="") {
		$entryId = $from["code"];
		$exitId = $to["code"];
		list($res, $err) = getMyTransportSGQuote($line,$entryId,$exitId);
		if (is_array($res) && array_key_exists('data',$res) && is_array($res['data']) && array_key_exists('fare_raw',$res['data']) && $res['data']['fare_raw']>0) {
			$fare_raw = $res['data']['fare_raw'];
			$distance = $res['data']['distance_raw'] * 10;
			echo "--> ${fare_raw} (".$res['data']['distance']." => ${distance})\n";
            $q = sprintf($q, $fare_raw, $distance);
            //echo "$q\n";  
			$r = pg_query($conn, $q);
            if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
                echo "--> success: id=".$f[0]."\n";
            } else {
                echo "--> failure: ".pg_last_error($conn)."\n";
            }
			//var_dump($res);
			//var_dump($err);
		} else {
			echo "ERROR: $err\n";
		}
	}
}

function getMyTransportSGQuote($line,$entryId,$exitId) {

	$url = 'https://www.mytransport.sg/content/mytransport/home/commuting/busservices/jcr:content/par/mtp_generic_tab/mtp_generic_tab6/distancefare.busFare?busServiceId='.$line.'&entryMarkerId='.$entryId.'&exitMarkerId='.$exitId.'&ticketType=30&tripContextParameter=&tripId=1';
    $ch = curl_init($url);

    //Create And Save Cookies
/*
    $tmpfname = dirname(__FILE__).'/cookie.txt';
    curl_setopt($ch, CURLOPT_COOKIEJAR, $tmpfname);
    curl_setopt($ch, CURLOPT_COOKIEFILE, $tmpfname);
*/
/*
Host: www.mytransport.sg
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0) Gecko/20100101 Firefox/68.0
Accept: *//*
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate, br
Referer: https://www.mytransport.sg/content/mytransport/home/commuting/busservices.html
X-Requested-With: XMLHttpRequest
Connection: keep-alive
Cookie: mtp_last_visit=Fare Calculator|/content/mytransport/home/commuting/busservices.html#Fare_Calculator,Bus Arrival Time|/content/mytransport/home/commuting/busservices.html#Bus_Arrival_Time,Bus Services|/content/mytransport/home/commuting/busservices.html; s_fid=1A0D8455C75F0629-03AFA1ACC259519D; s_cc=true
*/
    $cookies = 'mtp_last_visit=Fare Calculator|/content/mytransport/home/commuting/busservices.html#Fare_Calculator,Bus Arrival Time|/content/mytransport/home/commuting/busservices.html#Bus_Arrival_Time,Bus Services|/content/mytransport/home/commuting/busservices.html; s_fid=1A0D8455C75F0629-03AFA1ACC259519D; s_cc=true';
    //'mtp_last_visit=Fare Calculator|/content/mytransport/home/commuting/busservices.html#Fare_Calculator,Bus Services|/content/mytransport/home/commuting/busservices.html';
    $headers = [
		'Accept: */*',
		'Accept-Language: en-US,en;q=0.5',
		'X-Requested-With: XMLHttpRequest',
		'Accept-Encoding: gzip, deflate, br'
    ];
    curl_setopt($ch, CURLOPT_HEADER, false);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    //curl_setopt($ch, CURLOPT_HEADER, true);
    curl_setopt($ch, CURLOPT_FOLLOWLOCATION, true);
    curl_setopt($ch, CURLOPT_AUTOREFERER, true);
    curl_setopt($ch, CURLOPT_REFERER, 'https://www.mytransport.sg/content/mytransport/home/commuting/busservices.html');
    curl_setopt($ch, CURLOPT_USERAGENT, 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:68.0) Gecko/20100101 Firefox/68.0');
    //curl_setopt($ch, CURLINFO_HEADER_OUT, true);
    curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
    curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
    curl_setopt($ch, CURLOPT_HTTP_VERSION, CURL_HTTP_VERSION_1_1);
    curl_setopt($ch, CURLOPT_COOKIE, $cookies);
    curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
    //https://stackoverflow.com/questions/310650/decode-gzipped-web-page-retrieved-via-curl-in-php
    curl_setopt($ch, CURLOPT_ENCODING , "gzip");

    // EXECUTE
    sleep(1);
    $html = curl_exec($ch);
    //error_log($url);
    //error_log($html);
    //echo trim($html);
    curl_close($ch);

    $dom = new DOMDocument;
    libxml_use_internal_errors(true);
    $dom->loadHTML(trim($html));
    $finder = new DomXPath($dom);

    // distance_fare_result_nm => Trip 1
    // distance_fare_result_sv => Bus Svc 41
    // distance_fare_result_bd => 28641 - Blk 284
    // distance_fare_result_al => 42209 - Aft Toh Tuck Rise
    // distance_fare_result_dt => 7.7 km
    // distance_fare_result_fr => $1.29
    // fare_raw => 129
    // distance_raw => 770

    $data = array(
      'name'         => getItem($finder,'distance_fare_result_nm'),
      'service'      => getItem($finder,'distance_fare_result_sv'),
      'board'        => getItem($finder,'distance_fare_result_bd'),
      'alight'       => getItem($finder,'distance_fare_result_al'),
      'distance'     => getItem($finder,'distance_fare_result_dt'),
      'fare'         => getItem($finder,'distance_fare_result_fr'),
      'fare_raw'     => getItem($finder,'fare_raw'),
      'distance_raw' => getItem($finder,'distance_raw')
    );

    return [['data'=>$data],$html];
}

function getItem($finder,$class,$default='') {
    $nlist = $finder->query("//*[@class='${class}']");
    if ($nlist->length>0) {
        $item = $nlist->item(0);
        return $item->textContent;
    }
    return $default;
}