<?php

class HourlyAverages {

	public static function get($db, $from_address, $to_address, $city, $country) {
		$message = NULL;
		$city_id = (int)$city;
		$country_code = pg_escape_string($country);

		$from_areas = [];
		$to_areas = [];

		$postal_areas = [];
		$radius_areas = [];
		$plygon_areas = [];

		// Load areas for the city and country
		$q = "SELECT * FROM geofence_area WHERE city_id=${city_id} AND country='${country_code}'";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			while ($f=pg_fetch_assoc($r)) {
				if ($f["type"]=="postal") {
					$postal_areas[] = $f;
				}
				if ($f["type"]=="radius") {
					$radius_areas[] = $f;
				}
				if ($f["type"]=="polygon") {
					$plygon_areas[] = $f;
				}
				$raw_areas[] = $f;
			}
		} else {
			return [NULL,pg_last_error()];
		}
		if (count($postal_areas)<1 && count($radius_areas)<1 && count($plygon_areas)<1) {
			return [NULL,"No areas defined for the city"];
		}

		$from_areas = AddressToArea::getAreasForAddress($db,$from_address,$postal_areas,$radius_areas,$plygon_areas);
		if (!is_array($from_areas) || count($from_areas)<1) {
			return [NULL,"Cannot geocode from address to an area"];
		}

		$to_areas = AddressToArea::getAreasForAddress($db,$to_address,$postal_areas,$radius_areas,$plygon_areas);
		if (!is_array($to_areas) || count($to_areas)<1) {
			return [NULL,"Cannot geocode to address to an area"];
		}

		$data = [];

		$q = "SELECT * FROM geofence_area_average_quotes ";
		$q.= " WHERE area_start_id=".$from_areas[0]["id"]." AND area_end_id=".$to_areas[0]["id"];
		$q.= " ORDER BY transport_provider_id, hour";
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r)) {
			while ($f=pg_fetch_assoc($r)) {
				if (!array_key_exists($f['transport_provider_id'],$data)) {
					$data[$f['transport_provider_id']] = [];
				}
				$data[$f['transport_provider_id']][$f['hour']] = $f;
			}
		}
		$certainty = HourlyAverages::calculateCertainty($data);

		$result_areas = [
			'from_areas' => $from_areas,
			'to_areas' => $to_areas,
			'hourlyaverages' => $data,
			'certainty' => $certainty
		];

		return [$result_areas, $message];
	}


	public static function calculateCertainty($data) {
		$vendors = []; // Vendors to process
		$hours = []; // Transposition matrix
		$Pi = []; // Certainity by vendor (current hour)
		$Wi = []; // Weight by vendor (current hour)
		$Si = []; // Sum by vendor per hout
		$Ri = []; // Certainty per hour
		foreach ($data as $key=>$val) {
			$vendors[] = $key;
			$Pi[$key] = 0;
			$Wi[$key] = 0;
		}
		for ($i=0; $i<24; $i++) {
			$hours[$i] = [];
			$Si[$i] = 0;
			$Ri[$i] = 0;
			foreach ($vendors as $vendor) {
				$hours[$i][$vendor] = NULL;
			}
		}
		foreach ($data as $key=>$val) {
			foreach ($val as $hour=>$f) {
				$hours[$f['hour']][$f['transport_provider_id']] = [
					'average_cost' => $f['average_cost'],
					'average_total' => $f['average_total'],
					'average_count' => $f['average_count']
				];
				$Si[$f['hour']] += $f['average_count'];
			}
		}
		for ($i=0; $i<24; $i++) {
			$d = $hours[$i];

			foreach ($d as $vendor=>$f) {
				if ($f===NULL) {
					$Pi[$vendor] = 0;
				} else {
					if ($f['average_count']<10) {
						$Pi[$vendor] = 0;
					} else {
						$Pi[$vendor] = 1-10/$f['average_count'];
					}
					if ($Si[$i]!==0) {
						$Wi[$vendor] = $f['average_count']/$Si[$i];
					} else {
						$Wi[$vendor] = 0;
					}
				}
				$Ri[$i] += 100.0 * $Pi[$vendor] * $Wi[$vendor];
			}
		}
		return $Ri;
	}

	public static function getCityInfo($db, $cityId) {
    $result = [];
	  $q = "SELECT gac.city, gac.country, atz.timezone
          FROM geofence_area_city gac
          LEFT JOIN address_view av on address_search @@ to_tsquery('simple', array_to_string(string_to_array(gac.city, ' '), ':* & ') || ':* & ' || gac.country)
          LEFT JOIN address_timezone atz on av.timezone = atz.id
          WHERE gac.id =${cityId}
          LIMIT 1
         ";
    $r = pg_query( $db, $q );
    if ( $r && pg_num_rows( $r ) && $f = pg_fetch_assoc( $r ) ) {
      $result = $f;
    }
    return $result;
  }

}
// vi:ts=2