#!/usr/bin/perl -w

use strict;
use DBI;
use Math::Trig;
use CGI qw/:standard :html3/;
use MIME::Lite;

my $dbh = DBI->connect("DBI:mysql:database=qcontinu;host=localhost",'qcontinu','mobius');

if (!defined($dbh)) {
    die "Couldn't open connection to database\n";
}

if (param('op') eq 'list') {
	listTracks(param('username'), param('password'));
} elsif (param('op') eq 'load') {
	loadTrack(param('username'), param('password'), param('trackid'));
} elsif (param('op') eq 'rename') {
	renameTrack(param('username'), param('password'), param('trackid'), param('name'));
} elsif (param('op') eq 'delete') {
	deleteTrack(param('username'), param('password'), param('trackid'));
} elsif (param('op') eq 'register') {
	registerUser(param('username'), param('password'), param('email'));
} elsif (param('op') eq 'confirm') {
	confirmUser(param('username'), param('confirm'));
} elsif (param('op') eq 'validate') {
	validateUser(param('username'), param('password'));
} elsif (param('op') eq 'remind') {
	remindUser(param('email'));
} elsif (param('op') eq 'statistics') {
	displayStatistics();
} elsif (param('op') eq 'export') {
	exportTrack(param('username'), param('password'), param('trackid'), param('format'));
}

$dbh->disconnect();

sub listTracks {
	my ($username, $password) = @_;
	print header(-type => 'text/xml', -cache_control => 'no-cache');
    print "<gpstrack>\n";
	if (validateUser($username, $password)) {
	    my $sth;
	    if ($username eq 'guest') {
		    $sth = $dbh->prepare("SELECT t1.track_id as track_id, trackname,"
	    	. " AVG(t2.latitude) as latitude, AVG(t2.longitude) as longitude, COUNT(*) as points"
			. " FROM gpstrack_track t1"
			. " LEFT JOIN gpstrack_point t2 ON t1.track_id=t2.track_id"
		    . " WHERE username='guest' AND (trackname LIKE 'sample_' OR (creation + INTERVAL 1 DAY)>UTC_TIMESTAMP())"
			. " GROUP BY t1.track_id, trackname"
		    . " ORDER BY creation");
	    } elsif ($username eq 'root') {
	    	$sth = $dbh->prepare("SELECT MIN(t1.track_id) as track_id, trackname,"
	    	. " AVG(t2.latitude) as latitude, AVG(t2.longitude) as longitude, COUNT(*) as points"
			. " FROM gpstrack_track t1"
			. " LEFT JOIN gpstrack_point t2 ON t1.track_id=t2.track_id"
			. " WHERE username NOT IN ('dana','dana\@qcontinuum.org')"
			. " GROUP BY t1.trackname"
			. " HAVING points > 1"
			. " ORDER BY creation DESC"
			. " LIMIT 50");
	    } else {
			$dbh->do("UPDATE gpstrack_user SET access=UTC_TIMESTAMP() WHERE username='$username'");
		    $sth = $dbh->prepare("SELECT t1.track_id as track_id, trackname,"
	    	. " AVG(t2.latitude) as latitude, AVG(t2.longitude) as longitude, COUNT(*) as points"
			. " FROM gpstrack_track t1"
			. " LEFT JOIN gpstrack_point t2 ON t1.track_id=t2.track_id"
			. " WHERE username='$username' AND deletion IS NULL"
			. " GROUP BY t1.track_id, trackname"
			. " ORDER BY creation");
		}
	    $sth->execute();
	    while (my $row = $sth->fetchrow_hashref()) {
			printf('<track id="%s" name="%s" latitude="%f" longitude="%f"/>' . "\n",
			$row->{track_id}, $row->{trackname}, $row->{latitude}, $row->{longitude});
	    }
	    $sth->finish();
	} else {
		print "<message text=\"Username and/or password not valid.\"/>\n";
	}
    print "</gpstrack>\n";
}

sub loadTrack {
    my ($username, $password, $reqtrackid) = @_;
	print header(-type => 'text/xml', -cache_control => 'no-cache');
    print "<gpstrack>\n";
	if (validateUser($username, $password)) {
	    my $trackdata = $dbh->selectrow_hashref("SELECT track_id, trackname FROM gpstrack_track WHERE track_id = $reqtrackid");
	    printf('<track id="%s" name="%s">' . "\n", $trackdata->{track_id}, $trackdata->{trackname});
	    my $sth = $dbh->prepare("SELECT latitude, longitude FROM gpstrack_point WHERE track_id = $reqtrackid ORDER BY point_id");
	    $sth->execute();
	    my $distance = 0;
	    my $seq = 0;
	    my ($lastx,$lasty) = (undef,undef);
	    while (my $row = $sth->fetchrow_hashref()) {
	    	$seq++;
			printf('<point latitude="%f" longitude="%f"/>' . "\n", $row->{latitude}, $row->{longitude});
			if ($seq >= 2) {
				$distance += getDistance($lastx, $lasty, $row->{latitude}, $row->{longitude});
			}
			($lastx,$lasty) = ($row->{latitude}, $row->{longitude});
	    }
	    print "</track>\n";
	    printf('<distance km="%.2f" miles="%.2f"/>' . "\n", $distance, $distance / 1.609344);
	    $sth->finish();
	} else {
		print "<message text=\"Username and/or password not valid.\"/>\n";
	}
    print "</gpstrack>\n";
}

sub renameTrack {
    my ($username, $password, $reqtrackid, $name) = @_;
	print header(-type => 'text/xml', -cache_control => 'no-cache');
    print "<gpstrack>\n";
	if ($username eq 'guest') {
		print "<message text=\"Guest account cannot rename tracks.\"/>\n";
	} elsif (validateUser($username, $password)) {
		$dbh->do("UPDATE gpstrack_track SET trackname='$name' WHERE track_id=$reqtrackid");
	} else {
		print "<message text=\"Username and/or password not valid.\"/>\n";
	}
    print "</gpstrack>\n";
}

sub deleteTrack {
    my ($username, $password, $reqtrackid) = @_;
	print header(-type => 'text/xml', -cache_control => 'no-cache');
	print "<gpstrack>\n";
	if ($username eq 'guest') {
		print "<message text=\"Guest account cannot manually delete tracks.\nTracks are automatically deleted after 24 hours.\"/>\n";
	} elsif (validateUser($username, $password)) {
		$dbh->do("UPDATE gpstrack_track SET deletion=UTC_TIMESTAMP() WHERE track_id=$reqtrackid");
	} else {
		print "<message text=\"Username and/or password not valid.\"/>\n";
	}
    print "</gpstrack>\n";
}

sub eraseTrack {
    my ($username, $password, $reqtrackid) = @_;
	print header(-type => 'text/xml', -cache_control => 'no-cache');
    print "<gpstrack>\n";
	if (validateUser($username, $password)) {
		$dbh->do("DELETE FROM gpstrack_track WHERE track_id = $reqtrackid");
	    $dbh->do("DELETE FROM gpstrack_point WHERE track_id = $reqtrackid");
	} else {
		print "<message text=\"Username and/or password not valid.\"/>\n";
	}
    print "</gpstrack>\n";
}

sub registerUser {
	my ($username, $password, $email) = @_;
	print header(-type => 'text/xml', -cache_control => 'no-cache');
    print "<gpstrack>\n";
    $dbh->do("DELETE FROM gpstrack_user WHERE confirm IS NOT NULL"
    . " AND (username='$username' OR (creation + INTERVAL 1 DAY)<UTC_TIMESTAMP())");
	my $sth = $dbh->prepare("SELECT COUNT(*) FROM gpstrack_user WHERE username='$username'");
	$sth->execute();
	my $count;
	($count) = $sth->fetchrow_array;
	$sth->finish();
    my $confirm = int(rand(22768)) + 10000;
	if ($count == 0) {
		$dbh->do("INSERT INTO gpstrack_user (creation, username, password, email, confirm)"
		. " VALUES (UTC_TIMESTAMP(), '$username', '$password', '$email', $confirm)");
		sendRegisterEmail($username, $email, $confirm);
		print "<message text=\"An email message has been sent to $email.\n"
		. "Please click on the link in the message to confirm registration.\"/>\n";
	} else {
		print "<message text=\"Username '$username' has already been assigned.\n"
		. "Please choose a different username.\"/>\n";
	}
    print "</gpstrack>\n";
}

sub sendRegisterEmail {
	my ($username, $email, $confirm) = @_;
	my $from_address = "\"GPS Track\" <gpstrack\@qcontinuum.org>";
	my $subject = "GPS Track registration";
	my $msg = MIME::Lite->new (
	  From => $from_address,
	  To => $email,
	  Subject => $subject,
	  Data => "To confirm registration for \"$username\", please visit:\nhttp://www.qcontinuum.org/scripts/gpstrackviewer.cgi"
	  . "?op=confirm&username=$username&confirm=$confirm"
	);
	$msg->send('sendmail', '/usr/sbin/sendmail -t -oi -oem');
}

sub confirmUser {
	print header(-cache_control => 'no-cache'),
		start_html("GPS Track Registration"),
		h1("GPS Track");
	my ($username, $confirm) = @_;
	if ($dbh->do("UPDATE gpstrack_user SET confirm=NULL "
		. " WHERE username='$username' AND confirm=$confirm") > 0) {
		print "Thank you for confirming the registration of new user \"$username\".\n";
		print "<br><br><a href=\"http://www.qcontinuum.org/map/\">Login</a>\n";
	} else {
		print "Sorry, registration of new user \"$username\" failed.\n";
	}
	print end_html();
}

sub validateUser {
	my ($username, $password) = @_;
	my $sth = $dbh->prepare("SELECT COUNT(*) FROM gpstrack_user"
	. " WHERE username='$username' AND password='$password' AND confirm IS NULL");
	$sth->execute();
	my ($count) = $sth->fetchrow_array;
	$sth->finish();
	if ($count > 0) {
		return 1;
	}
}

sub remindUser {
    my ($email) = @_;
	print header(-type => 'text/xml', -cache_control => 'no-cache');
    print "<gpstrack>\n";
	my $sth = $dbh->prepare("SELECT username, password FROM gpstrack_user WHERE email='$email' AND confirm IS NULL");
	$sth->execute();
	my $count = 0;
    while (my $row = $sth->fetchrow_hashref()) {
    	$count++;
		my ($username,$password) = ($row->{username}, $row->{password});
		my $from_address = "\"GPS Track\" <gpstrack\@qcontinuum.org>";
		my $subject = "GPS Track password reminder";
		my $msg = MIME::Lite->new (
		  From => $from_address,
		  To => $email,
		  Subject => $subject,
		  Data => "You requested the following reminder:\n\nUsername:\t$username\nPassword:\t$password\n\n"
		  . "Login at: http://www.qcontinuum.org/map"
		);
		$msg->send;
    }
	$sth->finish();
	if ($count == 0) {
		print "<message text=\"The address $email is not registered.\"/>\n";
	} else {
		print "<message text=\"A password reminder has been sent to $email.\"/>\n";
	}
    print "</gpstrack>\n";
}

sub displayStatistics {
	my @row;
	print header(), "\n";
	print start_html("GPS Track Statistics"), "\n";
	print h1("GPS Track Statistics"), "\n";
	print h2("Phone Models"), "\n";
	my $sth = $dbh->prepare("SELECT platform, count(*) FROM gpstrack_track GROUP BY platform ORDER BY platform");
	$sth->execute();
	print "<table border=1>\n";
	print Tr(th("Phone Name"), th("Tracks")), "\n";
	while (@row = $sth->fetchrow_array()) {
		print Tr( td( \@row ) ), "\n";
	}
	print "</table>\n";
	$sth->finish();
	print h2("GPS Models"), "\n";
	$sth = $dbh->prepare("SELECT gps, count(*) FROM gpstrack_track GROUP BY gps ORDER BY gps");
	$sth->execute();
	print "<table border=1>\n";
	print Tr(th("GPS Name"), th("Tracks")), "\n";
	while (@row = $sth->fetchrow_array()) {
		print Tr( td( \@row ) ), "\n";
	}
	print "</table>\n";
	print end_html(), "\n";
}

sub getDistance {
    my ($xdeg1,$ydeg1,$xdeg2,$ydeg2) = @_;
    my ($x1,$y1,$x2,$y2) = (deg2rad($xdeg1), deg2rad($ydeg1), deg2rad($xdeg2), deg2rad($ydeg2));
    my $radius = 6371; 
    my $latdistance = $x2 - $x1;
    my $lngdistance = $y2 - $y1;
    my $a = sin($latdistance/2) * sin($latdistance/2) + cos($x1) * cos($x2) * sin($lngdistance/2) * sin($lngdistance/2);
    my $c = 2 * atan2(sqrt($a), sqrt(1-$a));
    my $d = $radius * $c;
    return $d;
}

sub exportTrack {
    my ($username, $password, $reqtrackid, $format) = @_;
	if (validateUser($username, $password)) {
		my $trackdata = $dbh->selectrow_hashref("SELECT track_id, trackname FROM gpstrack_track WHERE track_id = $reqtrackid");
		my $trackname = $trackdata->{trackname};
		if ($format eq 'kml') {
			print header(-type => 'application/vnd.google-earth.kml+xml xml', -attachment => "$trackname.kml");
		} elsif ($format eq 'gpx') {
			print header(-type => 'text/xml', -attachment => "$trackname.gpx");
		} elsif ($format eq 'csv') {
			print header(-type => 'text/csv', -attachment => "$trackname.csv");
		}
		system('./gpstrackexport.pl', "-trackid=$reqtrackid", "-format=$format");
	}
}