<?php
$job_name = pathinfo(__FILE__, PATHINFO_FILENAME);
echo "[".date("Y-m-d H:i:s")."] ".$job_name." job is starting.\n";

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

set_time_limit(0); // No limit!

require('../../backend.php');
require('../common/QuoteApi.php');
$httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
$encryptionAlg = $savvyext->cfgReadChar('encryption.algorithm');
$encryptionKey = $savvyext->cfgReadChar('encryption.key');
$encryptionIV = $savvyext->cfgReadChar('encryption.iv');

$baseURL = $savvyext->cfgReadChar('system.api_url');

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

$member_id = 0;
$country = 'SG';
$timezone = 'UTC';
$distance = 1.2; // km 
$city_id = 1; // Singapore
$pool = 1; // Main pool - does not matter (we do not have pools here...)
$areas_count = 28;

$transport_providers = [3,4,5];

CONST SELECT_DISTINCT_AREAS = <<< EOS
WITH whole_hours AS (
    SELECT hour, area_start_id, area_end_id, transport_provider_id
    FROM generate_series(0, 23) hour,
         generate_series(1, $1) area_start_id,
         generate_series(1, $1) area_end_id,
         unnest(ARRAY[3,4,5]) transport_provider_id
    ORDER BY transport_provider_id, area_start_id, area_end_id, hour
)
SELECT DISTINCT ah.area_start_id as area_id  FROM whole_hours ah
                                                          LEFT JOIN geofence_area_average_quotes gaaq ON ah.hour = gaaq.hour
    AND ah.area_start_id = gaaq.area_start_id AND ah.area_end_id = gaaq.area_end_id AND ah.transport_provider_id =gaaq.transport_provider_id
WHERE gaaq.hour IS NULL
  AND ah.hour = date_part('hour', timezone($2, now()))
UNION
SELECT DISTINCT ah.area_end_id as area_id FROM whole_hours ah
                                           LEFT JOIN geofence_area_average_quotes gaaq ON ah.hour = gaaq.hour
    AND ah.area_start_id = gaaq.area_start_id AND ah.area_end_id = gaaq.area_end_id AND ah.transport_provider_id =gaaq.transport_provider_id
WHERE gaaq.hour IS NULL
  AND ah.hour = date_part('hour', timezone($2, now()))
ORDER BY area_id
EOS;

CONST SELECT_MISSED_AREAS = <<< EOS
WITH whole_hours AS (
    SELECT hour, area_start_id, area_end_id, transport_provider_id
    FROM generate_series(0, 23) hour,
         generate_series(1, $1) area_start_id,
         generate_series(1, $1) area_end_id,
         unnest(ARRAY[3,4,5]) transport_provider_id
    ORDER BY transport_provider_id, area_start_id, area_end_id, hour
)
SELECT DISTINCT ah.* FROM whole_hours ah
                     LEFT JOIN geofence_area_average_quotes gaaq ON ah.hour = gaaq.hour
    AND ah.area_start_id = gaaq.area_start_id AND ah.area_end_id = gaaq.area_end_id AND ah.transport_provider_id =gaaq.transport_provider_id
WHERE gaaq.hour IS NULL
  AND ah.hour = date_part('hour', timezone($2, now()))
ORDER BY ah.transport_provider_id, ah.area_start_id, ah.area_end_id, ah.hour
EOS;

$transport_providers_param = "{".implode(",", $transport_providers). "}";
$area_ids = [];
$resp = pg_query_params($conn, SELECT_DISTINCT_AREAS, [
	$areas_count,
	$timezone,
//	$transport_providers_param
]);
if ($resp && pg_num_rows($resp)) {
	while ($row=pg_fetch_row($resp)) {
		$area_ids[] = $row[0];
	}
}
QuoteApi::$job_name=$job_name;
if (count($area_ids)) {
// Step 1 & 2: area locations
	$locations_start = [];
	$locations_end = [];
	$area_locations = [];
	$q = "SELECT id, latitude, longitude, name, boundaries FROM geofence_area WHERE country='${country}' AND city_id=${city_id} AND district='t' AND id IN (".implode (", ", $area_ids).")  ORDER BY id";
	$r = pg_query($conn, $q);
	if ($r && pg_num_rows($r)) {
		$q1 = "select * from address where country='${country}' AND (%s) order by random() limit 2";
		while ($f=pg_fetch_row($r)) {
			// Singapore areas are postal!
			$b = json_decode($f[4],true); // {"postal_code":["60","61","62","63","64"]}
			$q2 = [];
			foreach ($b["postal_code"] as $code) {
				$q2[] = "LEFT(postal,".strlen($code).")='${code}'";
			}
			$r1 = pg_query($conn, sprintf($q1,implode(" OR ",$q2)));
			if ($r1 && pg_num_rows($r1)>1 && $f1=pg_fetch_assoc($r1)) {
				$locations_start[$f[0]] = $f1;
				$f1 = pg_fetch_assoc($r1);
				$locations_end[$f[0]] = $f1;
			}
		}
	}

	$providers_areas = [];
	$resp = pg_query_params($conn, SELECT_MISSED_AREAS, [
		$areas_count,
		$timezone,
	]);
	if ($resp && pg_num_rows($resp)) {
		while ($row=pg_fetch_row($resp)) {

			$star_area_id = $row[1];
			$end_area_id = $row[2];
			$transport_provider = $row[3];

			if (!array_key_exists($transport_provider,$providers_areas)) {
				$providers_areas[$transport_provider] = [];
			}
			$d = distanceBetweenTwoGpsCoordinates(
				$locations_start[$star_area_id]["latitude"], $locations_start[$star_area_id]["longitude"],
				$locations_end[$end_area_id]["latitude"], $locations_end[$end_area_id]["longitude"],
				"K"
			);
			if ($locations_start[$star_area_id]["id"] != $locations_end[$end_area_id]["id"]
			    && $d > $distance || $star_area_id == $end_area_id ) {
				array_push($providers_areas[$transport_provider],
					[
						"start_area" => $locations_start[$star_area_id],
						"end_area" => $locations_end[$end_area_id]
					]
				);
			}
		}
//		echo json_encode($providers_areas, JSON_PRETTY_PRINT) .PHP_EOL;
	}

	if (count($providers_areas) > 0) {
// Step 4: Schedule quotes
		$checkQuotes = [];
		foreach ($providers_areas as $provider_id => $provider_areas) {
			foreach ($provider_areas as $areas) {
				$origin_id = $areas["start_area"]["id"];
				$destination_id = $areas["end_area"]["id"];
				$origin = $areas["start_area"]["address"];
				$destination = $areas["end_area"]["address"];

				$pool = $provider_id == 3 ? 2 : 1;

				$q = "SELECT * FROM quotes ";
				$q.= " WHERE location_start_id=${origin_id} AND location_end_id=${destination_id} ";
				$q.= " AND transport_provider_id=${provider_id} AND cost>0  AND pool=${pool} ";
				$q.= " AND completed > (now() - '1 hour'::interval) ";
				$q.= " ORDER BY completed DESC LIMIT 1";
				$r = pg_query($conn, $q);
				if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
					echo "[".date("Y-m-d H:i:s")."] Quote ".$f["cost"]." already exists ID #".$f["id"]."\n";
					continue;
				}
				list($res,$id) = QuoteApi::schedule_quote($origin,$destination,$country,$member_id,$provider_id, 0,'t', $pool);
				if ($res>0) {
					if ($res==2) {
						echo "[".date("Y-m-d H:i:s")."] Quote complete! ID #${id}\n";
					} else if ($res==1) {
						echo "[".date("Y-m-d H:i:s")."] Scheduled quote ID #${id}\n";
						$checkQuotes[] = $id;
					} else {
						echo "[".date("Y-m-d H:i:s")."] Unexpected result for ID #${id} ($res)\n";
					}
				} else {
					echo "[".date("Y-m-d H:i:s")."] schedule_quote failed: ${res}.\n";
					echo "[".date("Y-m-d H:i:s")."] $origin,$destination,$country,$member_id,$provider_id\n";
				}
			}
		}
		// Step 5: Check quotes
		foreach ($checkQuotes as $id) {
			list($res,$cost) = QuoteApi::check_quote($id);
			echo "[".date("Y-m-d H:i:s")."] Checking quote ID #${id} ($res) cost=${cost}\n";
		}
	}
}

function distanceBetweenTwoGpsCoordinates($lat1,$lon1,$lat2,$lon2,$unit) {
    //error_log("public function distanceBetweenTwoGpsCoordinates($lat1,$lon1,$lat2,$lon2,$unit)");
    if (($lat1 == $lat2) && ($lon1 == $lon2)) {
        return 0;
    }
    $theta = $lon1 - $lon2;
    $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
    $dist = acos($dist);
    $dist = rad2deg($dist);
    $miles = $dist * 60 * 1.1515;
    $unit = strtoupper($unit);
    if ($unit == "K") {
        return ($miles * 1.609344);
    }
    if ($unit == "N") {
        ($miles * 0.8684);
    }
    return $miles;
}

unlock_pid_file($lock_file);

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