#!/usr/bin/perl -w
use strict;
 
use XML::Smart;
use LWP::Simple;
use DBI;

# Database configuration
my $DB_host = "";
my $DB_login = "";
my $DB_passwd = "";
my $DB_name = "ece452";
my $DB_table = "Yahoo_REST_traffic";
my $DB_link = DBI->connect("dbi:mysql:database=$DB_name;host=$DB_host", $DB_login, $DB_passwd)
	or die "Couldn't connect to database: ".DBI->errstr;

# Yahoo global REST configuration
my $REST_appid = "RJJfeHTV34EUbavNATCBrthe6GEGcUos58paVHEc8Vv8c8Z1doTg6ea.muhP.wZD53M-";
my $REST_severity = "1";
my $REST_radius = "100";
my $REST_output = "xml";
my @REST_urls;

my @REST_zips = ("07724",
		"07013");

foreach my $REST_zip (@REST_zips) {
	push (@REST_urls, "http://local.yahooapis.com/MapsService/V1/trafficData?appid=$REST_appid&severity=$REST_severity&radius=$REST_radius&output=$REST_output&zip=$REST_zip");
}

foreach my $REST_url (@REST_urls) {
	my $REST_data = get $REST_url;
	die "Couldn't get $REST_url" unless defined $REST_data;

	my $REST_xml = XML::Smart->new($REST_data);
	$REST_xml = $REST_xml->cut_root();

	my $insert_count = 0;
	my $update_count = 0;
	my $loop_count = 0;

	foreach my $REST_results ( @{$REST_xml->{Result}} ) {
		my $Title = $REST_results->{Title}->content;
		my $Description = $REST_results->{Description}->content;
		my $Latitude = $REST_results->{Latitude}->content;
		my $Longitude = $REST_results->{Longitude}->content;
		my $Direction = $REST_results->{Direction}->content;
		my $Severity = $REST_results->{Severity}->content;
		my $Category = $REST_results->{type}->content;
		my $ReportDate = $REST_results->{ReportDate}->content;
		my $UpdateDate = $REST_results->{UpdateDate}->content;
		my $EndDate = $REST_results->{EndDate}->content;

		my $dupe_check_query = $DB_link->prepare(qq{
			SELECT id
			FROM `$DB_table`
			WHERE ( `_Title_HASH` = MD5("$Title")
				AND `ReportDate` = FROM_UNIXTIME("$ReportDate")
				AND `Latitude` = "$Latitude"
				AND `Longitude` = "$Longitude" );
		});
                $dupe_check_query->execute()
			or die "Couldn't run dupe_check_query: ".DBI->errstr;

		if ($dupe_check_query->rows == 0) {
			my $insert_query = $DB_link->do(qq{
				INSERT INTO `$DB_table`
					(`_Created`, `_Title_HASH`, `_Description_HASH`, `Title`, `Description`, 
					`Latitude`, `Longitude`, `Direction`, `Severity`, `Category`, `ReportDate`, 
					`UpdateDate`, `EndDate`)
				VALUES (NOW(), MD5("$Title"), MD5("$Description"), "$Title", "$Description", 
					"$Latitude", "$Longitude", "$Direction", "$Severity", "$Category", 
					FROM_UNIXTIME("$ReportDate"), FROM_UNIXTIME("$UpdateDate"), 
					FROM_UNIXTIME("$EndDate"));
			})
				or die "Couldn't run insert_query: ".DBI->errstr;
			$insert_count = $insert_count + $insert_query;
			$loop_count++;
		}

		else {
			my $dupe_id = $dupe_check_query->fetchrow_array();
			my $update_query = $DB_link->do(qq{
				UPDATE `$DB_table`
				SET `_Description_HASH` = md5("$Description"), `Description` = "$Description",
					`Direction` =  "$Direction", `Severity` = "$Severity",
					`Category` = "$Category", `UpdateDate` = FROM_UNIXTIME("$UpdateDate"), 
					`EndDate` = FROM_UNIXTIME("$EndDate")
				WHERE ( `id` = "$dupe_id"
					AND `UpdateDate` < FROM_UNIXTIME("$UpdateDate") );
			})
				or die "Couldn't run update_query: ".DBI->errstr;
			$update_count = $update_count + $update_query;
			$loop_count++;
		}
	}

	print "Statistics for zip code: ".substr($REST_url, -5, length($REST_url))."\n";
	print "------------------------------\n";
	print "Total records received: ".$loop_count."\n";
	print "New records inserted: ".$insert_count."\n";
	print "Old records updated: ".$update_count."\n";
	print "\n[sleeping for 2 seconds]\n\n";
	sleep(2);
}

$DB_link->disconnect();
