#! /usr/bin/perl
use DBI;
use CGI;
use Digest::MD5 (md5_hex);
use Data::Dumper;

my $dsn = "DBI:mysql:database=clickit;host=";
my $dbh = DBI->connect($dsn, "apache", "apache");
my $cgi = new CGI();
my $my_url = "http://www.grajagan.org/clickit.cgi";
my $img_url = "http://www.grajagan.org/posted/1x1.gif";
my ($md5, $sql, $sth);

if ($cgi->param("add") ne "") {

	my $url = $cgi->param("add");
	$md5 = md5_hex($url);
	$sql = "insert into urls values(null, '$md5', ?, now(), 0)";
	$sth = $dbh->prepare($sql);
	$sth->execute($url);

	print $cgi->header();
	print "$my_url?url=$md5";

} elsif ($cgi->param("url") ne "" ||
	     ($cgi->param("view") ne "" && $cgi->param("view") ne "all") && $cgi->remote_host ne "activx.com") {

	$md5 = $cgi->param("url");
	$md5 |= $cgi->param("view");
	$sql = "select urls_id, url from urls where md5 = '$md5'";
	$sth = $dbh->prepare($sql);
	$sth->execute();
	my ($urls_id, $url) = $sth->fetchrow_array();

	if ($url ne "") {
		$sql = "insert into clicks(urls_id, clicked_ts, remote_host, referer, user_agent)  values($urls_id, now(), ?, ?, ?)";
		$sth = $dbh->prepare($sql);
		$sth->execute($cgi->remote_host(), $cgi->referer(), $cgi->user_agent());

		print $cgi->redirect(-uri => $url);

	} else {
		print $cgi->redirect(-uri => "http://www.google.com/");
	}
} elsif ($cgi->url(-path => 1, -full => 1) ne $cgi->url(-full => 1)) {

	print $cgi->redirect(-uri => $img_url);
	$md5 = $cgi->url(-path => 1, -full => 1);
	$md5 =~ s/.*\///;
	$md5 =~ s/\....$//;
	$dbh->do("update urls set views = views + 1 where md5 = '$md5'");

} elsif ($cgi->param("view") eq "all") {
	print $cgi->header();

	$sql = "select md5, url, views, count(c.urls_id), created_ts, max(clicked_ts)
		    from urls u left join clicks c on (u.urls_id = c.urls_id)
			group by url order by clicked_ts desc";
	$sth = $dbh->prepare($sql);
	$sth->execute();
	print "<table border='1' cellspacing='2' cellpadding='2'>\n<tr><th>URL</th><th>views</th><th>clicks</th><th>created</th><th>last clicked</th></tr>\n";
	while ((($md5, $url, $views, $n, $created, $date) = $sth->fetchrow_array())) {
		$full_url = $url;
		$url =~ s/(.{45}).*/\1 .../;
		if ($n > 0) {
			print "<tr><td><a href='$my_url?view=$md5' alt='$full_url'>$url</a></td><td>$views</td><td>$n</td><td>$created</td><td>$date</td></tr>\n";
		} else {
			print "<tr><td>$url</td><td>$views</td><td>$n</td><td>$created</td><td>&nbsp;</td></tr>\n";
		}
	}
	print "</table>";
} elsif ($cgi->param("view") ne "") {
	print $cgi->header();

	$md5 = $cgi->param("view");
	$sql = "select url from urls where md5 = '$md5'";
	$sth = $dbh->prepare($sql);
	$sth->execute();
	($url) = $sth->fetchrow_array();
	$sth->finish();

	$sql = "select clicked_ts, remote_host, referer, user_agent from urls u left join clicks c on (u.urls_id = c.urls_id) where md5 = '$md5' order by clicked_ts desc";
	$sth = $dbh->prepare($sql);
	$sth->execute();
	print "<table width='100%' border='1' cellspacing='2' cellpadding='2'>\n<tr><th colspan='4'><a href='$url'>$url</a></th></tr>\n<tr><th>clicked</th><th>remote host</th><th>referer</th><th>user agent</th></tr>\n";
	while ((($date, $remote_host, $referer, $user_agent) = $sth->fetchrow_array())) {
		print "<tr><td>$date</td><td>$remote_host</td><td><a href='$referer'>CLICK</a></td><td>$user_agent</td></tr>\n";
	}
	print "</table>";

}