<?php

class Quote {

    const QUOTE_CHECK_RETRIES = 10;
    const QUOTE_CHECK_TIMEOUT = 3;

    public static function getTransportQuote($db, $origin, $destination, $option, $trip, $route) {
        $quote = [];
        switch ($option["transport_provider_id"]) {
            case 1:
            case 2:
            case 3:
            case 4:
            case 5:
            case 8:
                list($option, $trip, $quote) = Quote::rideshareQuote($db, $origin, $destination, $option, $trip);
                break;
            case 11: // BART
                list($option, $trip, $quote) = Quote::bartQuote($db, $origin, $destination, $option, $trip);
                break;
            case 12: // Muni
                list($option, $trip, $quote) = Quote::muniQuote($db, $origin, $destination, $option, $trip);
                break;
            case 13: // LTA
                list($option, $trip, $quote) = Quote::ltaQuote($db, $origin, $destination, $option, $trip);
                break;
            case 14: // MARTA
                list($option, $trip, $quote) = Quote::martaQuote($db, $origin, $destination, $option, $trip);
                break;
        }
        return [$option, $trip, $quote];
    }

    public static function rideshareQuote($db, $origin, $destination, $option, $trip) {
        $res = [];

        $input = [
            "origin" => $origin,
            "destination" => $destination,
            "member_id" => $option["member_id"],
            "country" => $origin["country"],
            "transport_provider_id" => $option["transport_provider_id"],
            "group_quote_id" => 0,
            "trackedemail_item_id" => 0,
            "prefill" => 'f'
        ];
        
        list($res,$body) = Quote::postServiceCall("/trips/api/quote", json_encode($input));
        $log = [
			'message' => 'BookingQuote::rideshareQuote',
			'function' =>'Quote::postServiceCall()',
            'data' =>$input,
            'response' => $res
		];
		Logger::debug($log);
        if (!is_array($res) || !array_key_exists("id",$res) || $res["id"]<1) {
            if (!is_array($res)) {
              error_log($res);
            }
            error_log($body);
            throw new Exception("Failed to schedule quote");
        }
        if (array_key_exists("complete",$res) && $res["complete"]!=NULL && $res["complete"]!="") {
            if (array_key_exists("cost",$res) && $res["cost"]>0) {
                $trip = Quote::patchRideshareTrip($trip, $res["cost"]);
                return [$option, $trip, $res];
            } else {
                throw new Exception("Failed to get the quote");
                // $res["message"] ?
                // return [$option, $trip, $res]; // Do we want to recover?
            }
        }
        // Will try for 30 seconds?
        $endpoint = "/trips/api/quote/".$res["id"];
        $i = 0;
        while ($i<Quote::QUOTE_CHECK_RETRIES) {
            sleep(Quote::QUOTE_CHECK_TIMEOUT);
            list($res,$body) = Quote::getServiceCall($endpoint);
            $log = [
                'message' => 'BookingQuote::rideshareQuote',
                'function' =>'Quote::getServiceCall()',
                'data' =>$endpoint,
                'response' => $res
            ];
            Logger::debug($log);
            if (!is_array($res) || !array_key_exists("id",$res) || $res["id"]<1) {
                continue; // Try again...
            }
            if (array_key_exists("complete",$res) && $res["complete"]!=NULL && $res["complete"]!="") {
                break; // No mater the result, maybe client will want to recover
            }
            $i++;
        }
        if (!is_array($res) || !array_key_exists("cost",$res) || !isset($res["cost"]) || $res["cost"] == 0) {
          $avgPriceInfo  = Quote::averagePrice($db, $origin, $destination, $option["transport_provider_id"]);
          if (isset($avgPriceInfo) && array_key_exists('cost', $avgPriceInfo)) {
            $res["cost"] = $avgPriceInfo['cost'];
            $res["is_average_cost"] = true;
          };
        }
        $trip = Quote::patchRideshareTrip($trip, $res["cost"]);
        Quote::updateRideshareCosts($db, $trip);
        return [$option, $trip, $res]; // last check quote result
    }

    public static function patchRideshareTrip($trip, $cost) {
        // Do we save the quote?
        $fare_raw = ((int)(100.0*$cost));
        if (array_key_exists("options",$trip)) {
            $gid = $trip["options"]["gid"];
            $trip["options"]["legs"][0]["fare_raw"] = $fare_raw;
            $trip["options"]["leg_fare"][$gid] = $fare_raw;
            $trip["options"]["leg_steps"][$gid][0]["fare_raw"] = $fare_raw;
        } else {
            $trip["options"] = [
                "gid" => "1",
                "routes" => 1,
                "legs" => [
                    [
                        "id" => 1,
                        "fare_raw" => $fare_raw
                    ]
                ],
                "leg_steps" => [
                    1 => [
                        "fare_raw" => $fare_raw
                    ]
                ],
                "leg_fare" => [
                    1 => $fare_raw
                ]
            ];
        }
        return $trip;
    }

    public static function updateRideshareCosts($db, $trip) {
        if (array_key_exists("options",$trip)) {
            $gid = $trip["options"]["legs"][0]["id"];
            if ($gid>1 && $trip["options"]["legs"][0]["fare_raw"]>0) {
                $q = "UPDATE google_directions_legs SET fare_raw=".((int)$trip["options"]["legs"][0]["fare_raw"]);
                $q.= " WHERE id=".((int)$gid);
                $r = pg_query($db, $q);
                Options::saveTripAdviceLegStepQuote(
                    $db, $trip, $trip["options"]["legs"][0], $trip["options"]["leg_steps"][$gid][0]);
            }
        }
    }

    /*
    {
        "id": "224583",
        "transport_provider_id": "4",
        "automation_id": "206441",
        "cost_raw": null,
        "cost": null,
        "created": "2020-01-09 13:09:01.072305",
        "completed": null,
        "member_id": "13",
        "location_start_id": "4426",
        "location_end_id": "4386",
        "quote_group_id": "0",
        "prefill": "f",
        "location_start": "97 Meyer Rd, 93, Singapore 437918",
        "location_start_lat": "1.2970849",
        "location_start_lng": "103.8925712",
        "location_start_tz": "1",
        "location_geocoding_date": "2019-10-11",
        "location_end": "1 Fusionopolis Way, #01-07 & #02-14, Connexis, Singapore 138632",
        "location_end_lat": "1.2987049",
        "location_end_lng": "103.7875699",
        "deeplink": "ComfortDelGroTaxi:\/\/\/?action=setBooking&endingLat=1.298705&endingLong=103.787570&startingLat=1.297085&startingLong=103.892571"
    }
    */

    /*
    {
        "id": 224583,
        "location_start": "97 Meyer Rd, 93, Singapore 437918",
        "location_end": "1 Fusionopolis Way, #01-07 & #02-14, Connexis, Singapore 138632",
        "cost_raw": "14.6",
        "trackedemail_item_id": 0,
        "cost": "14.60",
        "transport_provider_id": 4,
        "location_start_lat": "1.2970576",
        "location_start_lng": "103.8925856",
        "location_end_lat": "1.2987049",
        "location_end_lng": "103.7875699",
        "request_date": "2020-01-09T13:09:00.955Z",
        "started": "2020-01-09T13:09:04.203Z",
        "complete": "2020-01-09T13:09:04.743Z",
        "status": 0,
        "message": "android_automation_job_detail",
        "attempts": 0,
        "automation_id": 206441,
        "completed": "2020-01-09T13:09:04.743Z",
        "created": "2020-01-09 13:09:01.072305",
        "location_start_id": "4426",
        "location_end_id": "4386",
        "member_id": "13",
        "deeplink": "ComfortDelGroTaxi:\/\/\/?action=setBooking&endingLat=1.298705&endingLong=103.787570&startingLat=1.297085&startingLong=103.892571"
    }
    */

    public static function bartQuote($db, $origin, $destination, $option, $trip) {
        $quote = [];
        
        $quote["cost_raw"] = NULL;
        $quote["transport_provider_id"] = $option["transport_provider_id"];
        $quote["deeplink"] = NULL;
        //$quote["leg_fare"] = $trip["options"]["leg_fare"];

        $leg = [
            "fare_raw" => 1
        ];
        // Select leg
        //error_log(">>>>>>>>> ".json_encode($trip));
        foreach ($trip["options"]["legs"] as $leg) {
            break;
        }
        $quote["cost"] = sprintf("%0.02f",$leg["fare_raw"]/100.0);
        
        return [$option, $trip, $quote];
    }

    public static function muniQuote($db, $origin, $destination, $option, $trip) {
        $quote = [];

        $quote["cost_raw"] = NULL;
        $quote["transport_provider_id"] = $option["transport_provider_id"];
        $quote["deeplink"] = NULL;
        //$quote["leg_fare"] = $trip["options"]["leg_fare"];

        $leg = [
            "fare_raw" => 1
        ];
        // Select leg
        //error_log(">>>>>>>>> ".json_encode($trip));
        foreach ($trip["options"]["legs"] as $leg) {
            break;
        }
        $quote["cost"] = sprintf("%0.02f",$leg["fare_raw"]/100.0);
        
        return [$option, $trip, $quote];
    }

    public static function ltaQuote($db, $origin, $destination, $option, $trip) {
        $quote = [];

        $cost = 0;
        
        $quote["cost_raw"] = NULL;
        $quote["transport_provider_id"] = $option["transport_provider_id"];
        $quote["deeplink"] = NULL;

        $legs = $trip["options"]["legs"];
        $leg_steps = $trip["options"]["leg_steps"];
        $leg_fare = $trip["options"]["leg_fare"];

        foreach ($legs as $i=>$leg) {
            $leg_id = $leg["id"];
            if (array_key_exists($leg_id,$leg_fare) && $leg_fare[$leg_id]>0) {
                if ($cost==0) $cost = $leg["fare_raw"];
                continue;
            }
            if (array_key_exists("fare_raw",$leg) && $leg["fare_raw"]>0) {
                if ($cost==0) $cost = $leg["fare_raw"];
                continue;
            }
            list ($leg, $leg_steps) = Quote::legStepsQuote($db, $origin, $destination, $option, $trip, $leg, $leg_steps);
            $leg_fare[$leg_id] = $leg["fare_raw"];
            if ($cost==0 && $leg["fare_raw"]>0) $cost = $leg["fare_raw"];
            $legs[$i] = $leg;
        }
        $trip["options"]["legs"] = $legs;
        $trip["options"]["leg_steps"] = $leg_steps;
        $trip["options"]["leg_fare"] = $leg_fare;

        $quote["cost"] = $cost;
        //$quote["leg_fare"] = $leg_fare;

        // Update model to save the quote
        Quote::updateQuotes($db, $trip);

        return [$option, $trip, $quote];
    }

    public static function martaQuote($db, $origin, $destination, $option, $trip) {
        $quote = [];

        $quote["cost_raw"] = NULL;
        $quote["transport_provider_id"] = $option["transport_provider_id"];
        $quote["deeplink"] = NULL;
        //$quote["leg_fare"] = $trip["options"]["leg_fare"];

        $leg = [
            "fare_raw" => 1
        ];
        // Select leg
        //error_log(">>>>>>>>> ".json_encode($trip));
        foreach ($trip["options"]["legs"] as $leg) {
            break;
        }
        $quote["cost"] = sprintf("%0.02f",$leg["fare_raw"]/100.0);
        
        return [$option, $trip, $quote];
    }

    public static function updateQuotes($db, $trip) {
        $legs = $trip["options"]["legs"];
        $leg_steps = $trip["options"]["leg_steps"];
        $leg_fare = $trip["options"]["leg_fare"];
        foreach ($legs as $leg) {
            $leg_id = $leg["id"];
            $steps = $leg_steps[$leg_id];
            foreach ($steps as $step) {
                if (array_key_exists("fare_raw",$step) && $step["fare_raw"]>0) {
                    $q = "SELECT id FROM leg_step_quote WHERE google_directions_leg_step_id=".$step["id"];
                    $r = pg_query($db, $q);
                    if ($r && pg_num_rows($r) && $f=pg_fetch_row($r)) {
                        $q = "UPDATE leg_step_quote SET fare_raw=".((int)$step["fare_raw"])." WHERE id=".$f[0];
                        $r = pg_query($db, $q);
                    } else {
                        $q = "INSERT INTO leg_step_quote (google_directions_leg_step_id,name,service,board,alight,distance,fare,fare_raw,distance_raw) VALUES(";
                        $q.= $step["id"].",'".pg_escape_string($step["short_line"])."','".pg_escape_string($step["line"])."',";
                        $q.= "'".pg_escape_string($step["departure_stop"])."','".pg_escape_string($step["arrival_stop"])."',";
                        $q.= "'".sprintf("%0.02f",$step["distance"]/100.0)."','".sprintf("%0.02f",$step["fare_raw"]/100.0)."',";
                        $q.= ((int)$step["fare_raw"]).",".((int)$step["distance"]).")";
                        $r = pg_query($db, $q);
                    }
                }
            }
            if (array_key_exists("fare_raw",$leg) && $leg["fare_raw"]>0) {
                $q = "UPDATE google_directions_legs SET fare_raw=".((int)$leg["fare_raw"])." WHERE id=${leg_id}";
                $r = pg_query($db, $q);
                continue;
            }
            if (array_key_exists($leg_id,$leg_fare) && $leg_fare[$leg_id]>0) {
                $q = "UPDATE google_directions_legs SET fare_raw=".((int)$leg["fare_raw"])." WHERE id=${leg_id}";
                $r = pg_query($db, $q);
            }
        }
    }

    public static function legStepsQuote($db, $origin, $destination, $option, $trip, $leg, $leg_steps) {
        $leg_id = $leg["id"];
        $steps = $leg_steps[$leg_id];

        $fare_raw = 0;
        foreach ($steps as $i=>$step) {
            if (array_key_exists("fare_raw",$step) && $step["fare_raw"]>0) {
                $fare_raw += $step["fare_raw"];
                continue;
            }
            $step = Quote::stepQuote($db, $origin, $destination, $option, $trip, $step);
            if (array_key_exists("fare_raw",$step) && $step["fare_raw"]>0) {
                $fare_raw += $step["fare_raw"];
            }
            $steps[$i] = $step;
        }
        $leg["fare_raw"] = $fare_raw;
        $leg_steps[$leg_id] = $steps;
        return [$leg, $leg_steps];
    }

    public static function stepQuote($db, $origin, $destination, $option, $trip, $step) {
        switch ($step["travel_mode"]) {
            case "": break;
            case "BIKE":
                $bike_time = $step['duration'];
                $country = $origin['country'];
                list($price,$err) = SGBikeApi::quote($db, $bike_time, $country,$step);
                if (!empty($price) && $price>0) {
                     $step["fare_raw"] = $price; 
                }
                break;
            case "WALKING": break;
            case "NUS_TRANSIT": 
                // NUS Internal Shuttle Buses are free of charge
                // http://www.nus.edu.sg/celc/symposium/Symposium%20Documents/Symposium%202016%20Visitors'%20Guide.pdf
                break;
            case "PARK": break;
            case "SCOOTER": 
                // No scooter quotes right now
                // list($res,$err) = ScooterApi::quote($db, $scooter_time, $country);
                break; 
            case "TAXI":
                // We do not know how to quote "TAXI" in another countries
                if ($origin["country"]=="SG") {
                    // Reverse geocode the "TAXI" step
                    $input = [
                        "latitude" => $step["location_start_lat"],
                        "longitude" => $step["location_start_lng"],
                        "country" => $origin["country"]
                    ];
                    list($originTaxi, $body) = Quote::putServiceCall("/trips/api/geocode",$input);
                    $input = [
                        "latitude" => $step["location_end_lat"],
                        "longitude" => $step["location_end_lng"],
                        "country" => $destination["country"]
                    ];
                    list($destinationTaxi, $body) = Quote::putServiceCall("/trips/api/geocode",$input);
                    if (!is_array($originTaxi) || !array_key_exists("address",$originTaxi) 
                     || !is_array($destinationTaxi) || !array_key_exists("address",$destinationTaxi)) {
                        break;
                    }
                    $option["transport_provider_id"] = 4; // ComfortDelGro
                    list($optionTaxi, $tripTaxi, $quoteTaxi) = 
                        Quote::rideshareQuote($db, $originTaxi, $destinationTaxi, $option, $trip);
                    if (is_array($quoteTaxi) && array_key_exists("cost",$quoteTaxi) && $quoteTaxi["cost"]>0) {
                        $step["fare_raw"] = (int)(100.0*$quoteTaxi["cost"]); // fare_raw is in cents
                    }
                }
                break;
            case "TRANSIT":
                // We do not know how to quote "TRANSIT" in another countries from here (must be specific)
                if ($origin["country"]=="SG") {
                    $step_id = $step["id"];
                    $result = Quote::mytansportsgServiceStepQuote($step_id);
                    error_log('***********************************************************************');
                    error_log(json_encode($result));
                    error_log('***********************************************************************');
                    // {"code":0,"data":{"307575":{"total":92}}}
                    if (is_array($result) && array_key_exists("code",$result) && $result["code"]===0 && array_key_exists("data",$result)) {
                        $data = $result["data"];
                        if (is_array($data) && array_key_exists($step_id,$data) && is_array($data[$step_id]) && array_key_exists('total',$data[$step_id]) && $data[$step_id]['total']>0) {
                            // Conversion ???
                            $step["fare_raw"] = (int)$data[$step_id]['total']; // fare_raw is in cents
                        }
                    }
                }
                break;
        }
        return $step;
    }
    /*
        BIKE        |   4743
        NUS_TRANSIT |    367
        PARK        |   1796
        SCOOTER     |   1023
        TAXI        |   2060
        TRANSIT     |  88781
        WALKING     | 154766
                    |      3
    */

    public static function postServiceCall($endpoint, $payload) {
        global $savvyext;

        $httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
        $encryptionAlg = $savvyext->cfgReadChar('encryption.algorithm');
        $encryptionKey = $savvyext->cfgReadChar('encryption.key');
        $encryptionIV = $savvyext->cfgReadChar('encryption.iv');
        $api_url = $savvyext->cfgReadChar('system.api_url');

        $encrypted_payload = bin2hex(
            openssl_encrypt(
                    $payload, $encryptionAlg, $encryptionKey, OPENSSL_RAW_DATA, $encryptionIV
        ));

        $postdata = "{\"encrypted_payload\": \"${encrypted_payload}\"}";

        $url = $api_url . $endpoint;

        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
        curl_setopt($ch, CURLOPT_POSTFIELDS, $postdata);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_VERBOSE, false);
        curl_setopt($ch, CURLOPT_HTTPHEADER, array(
            'Content-Type: application/json',
            'Authorization: Server-Token ' . $httpAuthToken)
        );

        $body = curl_exec($ch);
        $result = json_decode($body, true);

        $payload = openssl_decrypt(
                hex2bin(
                        $result['payload']
                ), $encryptionAlg, $encryptionKey, OPENSSL_RAW_DATA, $encryptionIV
        );
        $payload = json_decode($payload, true);
        return [$payload, $body];
    }

    public static function putServiceCall($endpoint, $payload) {
        global $savvyext;

        $httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
        $encryptionAlg = $savvyext->cfgReadChar('encryption.algorithm');
        $encryptionKey = $savvyext->cfgReadChar('encryption.key');
        $encryptionIV = $savvyext->cfgReadChar('encryption.iv');
        $api_url = $savvyext->cfgReadChar('system.api_url');

        /* $encrypted_payload = bin2hex(
            openssl_encrypt(
                    $payload, $encryptionAlg, $encryptionKey, OPENSSL_RAW_DATA, $encryptionIV
        ));
        $postdata = "{\"encrypted_payload\": \"${encrypted_payload}\"}"; */
        $postdata = http_build_query($payload);

        $url = $api_url . $endpoint;

        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "PUT");
        curl_setopt($ch, CURLOPT_POSTFIELDS, $postdata);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_VERBOSE, false);
        curl_setopt($ch, CURLOPT_HTTPHEADER, array(
            'Content-Type: application/x-www-form-urlencoded',
            'Authorization: Server-Token ' . $httpAuthToken)
        );

        $body = curl_exec($ch);
        $result = json_decode($body, true);

        $payload = openssl_decrypt(
                hex2bin(
                        $result['payload']
                ), $encryptionAlg, $encryptionKey, OPENSSL_RAW_DATA, $encryptionIV
        );
        $payload = json_decode($payload, true);
        return [$payload, $body];
    }

    public static function getServiceCall($endpoint, $shouldDecrypt = true) {
        global $savvyext;

        $httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
        $encryptionAlg = $savvyext->cfgReadChar('encryption.algorithm');
        $encryptionKey = $savvyext->cfgReadChar('encryption.key');
        $encryptionIV = $savvyext->cfgReadChar('encryption.iv');
        $api_url = $savvyext->cfgReadChar('system.api_url');

        $url = $api_url . $endpoint;

        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "GET");
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
        curl_setopt($ch, CURLOPT_VERBOSE, false);
        curl_setopt($ch, CURLOPT_HTTPHEADER, array(
            'Content-Type: application/json',
            'Authorization: Server-Token ' . $httpAuthToken)
        );

        $body = curl_exec($ch);
        if ($shouldDecrypt) {
          $result = json_decode($body,true);
          $payload = openssl_decrypt(
              hex2bin(
                  $result['payload']
              ),
              $encryptionAlg,
              $encryptionKey,
              OPENSSL_RAW_DATA,
              $encryptionIV
          );
        } else {
          $payload = $body;
        }
        $payload = json_decode($payload, true);
        return [$payload, $body];
    }

    public static function mytansportsgServiceStepQuote($id) {
        global $savvyext;
        $httpAuthToken = $savvyext->cfgReadChar('system.oauth2_token');
        $oauth2_url = $savvyext->cfgReadChar('system.oauth2_url');
        $url = $oauth2_url."mytransportsg/?step_id=" . $id;
        $opts = array(
            'http' => array(
                'method' => "GET",
                'timeout' => 60, /* 1 minute */
                'header'  =>
                    "Content-Type: application/x-www-form-urlencoded\r\n" .
                    "Accept: application/json\r\n" .
                    "Authorization: Server-Token ${httpAuthToken}\r\n",
            ),
            "ssl" => array(
                "verify_peer"=>false,
                "verify_peer_name"=>false,
            )
        );
        $context = stream_context_create($opts);
        $body = file_get_contents($url, false, $context);
        $advice = json_decode($body,true);
        return $advice;
    }

    public static function averagePrice($db, $origin, $destination, $providerId) {

      if (!array_key_exists('id', $origin) || !isset($origin['id'])
          || !array_key_exists('country', $origin) || !isset($origin['country'])
          || !array_key_exists('timeZoneId', $origin) || !isset($origin['timeZoneId'])
          || !array_key_exists('id', $destination) || !isset($destination['id']) ) {
        throw new Exception('Quote::averagePrices. Invalid input origin or destination');
      }

      $result = ['cost'=>0];
      $timeZoneId = $origin['timeZoneId'];
      $country = $origin['country'];
      $originId = $origin['id'];
      $destinationId = $destination['id'];

      $endpoint = "/geofencearea/api/addresstoarea/?country=${country}&address_id=";
      list($res,$body) = Quote::getServiceCall($endpoint.$originId, false);
      if(count($res) == 0 || array_key_exists('error', $res) && isset($res['error'])) {
        return $result;
      }
      $originAreaId =  $res['areas'][0]['id'];
      list($res,$body) = Quote::getServiceCall($endpoint.$destinationId, false);
      if(count($res) == 0 || array_key_exists('error', $res) && isset($res['error'])) {
        return $result;
      }
      $destinationAreaId =  $res['areas'][0]['id'];

      $dt = new DateTime('now', new DateTimeZone($timeZoneId));
      $currentHour = $dt->format('G');

      $q = "SELECT average_cost, hour, last_quotes_id, last_updated FROM geofence_area_average_quotes
                WHERE transport_provider_id=${providerId}
                    AND area_start_id=${originAreaId}
                    AND area_end_id=${destinationAreaId}
                    AND hour=${currentHour}
            ";
      $r = pg_query($db, $q);
      if ($r && pg_num_rows($r) && $f=pg_fetch_assoc($r)) {
        $result['cost'] = $f['average_cost'];
      }
      return $result;
    }
}

// vi:ts=2