<?php

class TripInsights {
	
	public function cheaperAlternatives($db, $gpsdb, $data) {
		error_log('TripInsights::cheaperAlternatives($db, $gpsdb, $data) ');

		$err = "";
		$member_id = (int)$data['member_id'];
		$trackedemail_item_id = (int)$data['trackedemail_item_id'];
		//$member_id = 3; // DEBUG
		if ($member_id<1) {
			error_log("Invalid member_id='${member_id}'");
			return NULL;
		}

		$q.= "SELECT b.*,c.name AS transport_provder_name,c.category,d.flag,f.root_id,";
		$q.= " CASE WHEN e.id IS NULL THEN '0' ELSE '1' END AS surge_price, ";
		$q.= " CASE WHEN (f.id IS NOT NULL AND f.cost>f.average) THEN ROUND(f.average/f.cost,2) ELSE 0 END AS cheaper_alternative, ";
		$q.= " (SELECT min(cost) FROM trip_price_comparison WHERE root_id=(SELECT root_id FROM trip_price_comparison WHERE data_source_id=b.id AND data_source=1)) AS min_cost ";
		$q.= ", g.address AS location_start_address,g.description AS location_start_description ";
		$q.= ", h.address AS location_end_address,h.description AS location_end_description ";
		$q.= " FROM trackedemail_item a, transport_providers c, parsedemail_item b ";
		$q.= " LEFT JOIN parsedemail_item_advice_result d ON (d.parsedemail_item_id=b.id) ";
		$q.= " LEFT JOIN trip_surge_price e ON (e.data_source_id=b.id AND e.data_source=1) ";
		$q.= " LEFT JOIN trip_price_comparison f ON (f.data_source_id=b.id AND e.data_source=1) ";
		$q.= " LEFT JOIN address g ON (g.id=b.location_start_id) ";
		$q.= " LEFT JOIN address h ON (h.id=b.location_end_id) ";
		$q.= " WHERE a.id=b.trackedemail_item_id AND a.member_id=".$member_id." AND c.id=b.transport_provider_id ";
		//$q.= " AND b.dup_id IS NULL ";
		$q.= " AND a.id=${trackedemail_item_id} ";
		//$q.= " ORDER BY b.travel_date DESC"; // LIMIT ?
		/*
		address        | character varying(200) |
		latitude       | numeric                |
		longitude      | numeric                |
		timezone       | integer                |
		geocoding_date | date                   |
		postal         | character varying(40)  |
		country        | character varying(2)   | default 'SG'::character varying
		geometry       | geography(Point,4326)  |
		description    | character varying(100) |
		*/
		error_log($q);
		$trip = [];
		$alternatives = [];
		$r = pg_query($db, $q);
		if ($r && pg_num_rows($r) && $trip=pg_fetch_assoc($r)) {
			$f["location_start_address"] = html_entity_decode ($f["location_start_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
      $f["location_end_address"] = html_entity_decode ($f["location_end_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");

			// Cheaper alternatives
			error_log("root_id=".$trip["root_id"]);
			//SELECT min(f.cost),b.transport_provider_id FROM trip_price_comparison f, parsedemail_item b WHERE f.root_id=92 AND f.cost<13.00 AND b.id=f.parsedemail_item_id GROUP BY b.transport_provider_id;
			$q = "SELECT min(f.cost) AS min_cost,b.transport_provider_id FROM trip_price_comparison f, parsedemail_item b ";
			$q.= " WHERE f.root_id=".$trip["root_id"]." AND f.cost<".$trip["cost"]." AND b.id=f.parsedemail_item_id";
			$q.= " GROUP BY b.transport_provider_id";
			$r = pg_query($db, $q);
			if ($r && pg_num_rows($r)) {
				while ($f=pg_fetch_assoc($r)) {
					// SELECT f.root_id,b.*,c.name AS transport_provder_name,c.category FROM trip_price_comparison f, parsedemail_item b LEFT JOIN transport_providers c ON (c.id=b.transport_provider_id) WHERE f.root_id=92 AND f.cost=9.00 AND b.id=f.parsedemail_item_id ORDER BY b.travel_date DESC LIMIT 1;
					$q0 = "SELECT f.root_id,b.*,c.name AS transport_provder_name,c.category ";
					$q0.= ", g.address AS location_start_address,g.description AS location_start_description ";
					$q0.= ", h.address AS location_end_address,h.description AS location_end_description ";
					$q0.= " FROM trip_price_comparison f, parsedemail_item b ";
					$q0.= " LEFT JOIN transport_providers c ON (c.id=b.transport_provider_id) ";
					$q0.= " LEFT JOIN address g ON (g.id=b.location_start_id) ";
					$q0.= " LEFT JOIN address h ON (h.id=b.location_end_id) ";		
					$q0.= " WHERE f.root_id=".$trip["root_id"]." AND f.cost=".$f["min_cost"];
					$q0.= " AND b.id=f.parsedemail_item_id AND b.cost=".$f["min_cost"];
					$q0.= " ORDER BY b.travel_date DESC LIMIT 1";
					
					error_log($q0);
					$r0 = pg_query($db, $q0);
					$f0 = pg_fetch_assoc($r0);
      		$f["location_start_address"] = html_entity_decode ($f["location_start_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
      		$f["location_end_address"] = html_entity_decode ($f["location_end_address"],ENT_QUOTES|ENT_HTML5,"UTF-8");
					$alternatives[] = $f0;
				}
			}
		} else {
			$err = pg_last_error($db);
		}
		$res = [
			"name" => "Cheaper Alternatives",
			"member_id" => $member_id,
			"trackedemail_item_id" => $trackedemail_item_id,
			"trip" => $trip,
			"alternatives" => $alternatives
		];
		return [$res, $err];
	}

}

// vi:ts=2