#!/usr/bin/perl

use DBI;

#---------------------------------------------------
# Global variables
#---------------------------------------------------
my $DOMAIN = ".";
my $DOMAIN_NAME = "[NO NAME]";
my $DATABASE = "linkchecker";
my $USER = "linkchecker";
my $PASSWORD = "linkchecker123";

#---------------------------------------------------
# Initialize Date and Hashes
#---------------------------------------------------
my $SCAN_DATE = getDate();
my %SITES;
my %PAGES;
my %LINKS;
my %LINK_COUNT;

#---------------------------------------------------
# Initialize DB connection
#---------------------------------------------------

my $dbh = DBI->connect("dbi:mysql:database=${DATABASE}", $USER, $PASSWORD);

#my $sth = $dbh->prepare("describe $table");
#$sth->execute();

#---------------------------------------------------
# Prepare statement handles for reuse
#---------------------------------------------------
my $sth_updatePage = $dbh->prepare("update page set links = ? where id = ?");
my $sth_updateSite = $dbh->prepare("update site set last_scan_id = ? where id = ?");
my $sth_getSiteID = $dbh->prepare("select id from site where lower(url) = lower(?)");
my $sth_insertSite = $dbh->prepare("insert into site (name, url, first_scan, last_scan) values (?, ?, ?, ?)");
my $sth_getLastInsertID = $dbh->prepare("select LAST_INSERT_ID()");
my $sth_getPageID = $dbh->prepare("select id from page where lower(url) = lower(?) and scan_id = ? and site_id = ?");
my $sth_insertPage = $dbh->prepare("insert into page (site_id, scan_id, url) values (?, ?, ?)");
my $sth_getLinkID = $dbh->prepare("select id from link where lower(url) = lower(?) and page_id = ? and broken = ?");
my $sth_insertLink = $dbh->prepare("insert into link (page_id, url, internal, broken) values (?, ?, ?, ?)");
my $sth_insertScan = $dbh->prepare("insert into scan (date) values (?)");

#---------------------------------------------------
# Create Scan
#---------------------------------------------------
my $scan_id = newScan($SCAN_DATE);


#---------------------------------------------------
# Parse File
#---------------------------------------------------
my ($page, $page_id);

while (<STDIN>)
{
	my $link;
	my $broken = 0;

	if (m/^PAGE\s*(.*?)$/)
	{
		$page = $1;
		$page =~ s/\?.*$//;

		if ($page =~ m#(http(s)*://(.*?)${DOMAIN})#i)
		{
			my $site = $1;
			my $site_id = getSiteIDByURL($site);
			$page_id = getPageIDByURL($site_id, $scan_id, $page);
			$LINK_COUNT{$page_id} = 0;
		}
		else
		{
			die "bad page $page\n";
		}
	}
	elsif (m/^LINK\s*(.*?)$/)
	{
		$link = $1;
		$LINK_COUNT{$page_id}++;
	}
	elsif (m/^BROKEN\s*(.*?)\s*\-\>\s*(.*?)$/)
	{
		$link = $2;
		$broken = 1;
		$link =~ s/\?.*$//;

		my $link_id = getLinkIDByURL($page_id, $link, $broken);
	}
	else
	{
		die "Unknown line: $_";
	}


}

foreach my $page_id (keys %LINK_COUNT)
{
	$sth_updatePage->execute($LINK_COUNT{$page_id}, $page_id);
}

foreach my $site_id (values %SITES)
{
	$sth_updateSite->execute($scan_id, $site_id);
}


$sth_updatePage->finish();
$sth_updateSite->finish();
$sth_getSiteID->finish();
$sth_insertSite->finish();
$sth_getLastInsertID->finish();
$sth_getPageID->finish();
$sth_insertPage->finish();
$sth_getLinkID->finish();
$sth_insertLink->finish();
$sth_insertScan->finish();

$dbh->disconnect();


sub getSiteIDByURL($)
{
	my $site_url = shift;

	if (exists($SITES{$site_url}))
	{
		return $SITES{$site_url};
	}

	$sth_getSiteID->execute($site_url);
	my $site_id;

	if ($sth_getSiteID->rows <= 0)
	{
		# Must create the site
		my $site_name;

		if ($site_url =~ m#(http(s)*://(.*?)\.*${DOMAIN})#i)
		{
			$site_name = lc($3);
		}
		else
		{
			die "Unexpected input: $site_url\n";
		}


		if ($site_name eq "")
		{
			$site_name = $DOMAIN_NAME;
		}

		$sth_insertSite->execute($site_name, $site_url, $SCAN_DATE, $SCAN_DATE);

		$sth_getLastInsertID->execute();

		my @array = $sth_getLastInsertID->fetchrow_array;

		$SITES{$site_url} = $array[0];

		return $array[0];
	}
	else
	{
		my @array = $sth_getSiteID->fetchrow_array;

		$SITES{$site_url} = $array[0];

		return $array[0];
	}
}

sub getPageIDByURL($$$)
{
	my ($site_id, $scan_id, $page_url) = @_;

	my $token = join("|", @_);

	if (exists($PAGES{$token}))
	{
		return $PAGES{$token};
	}

	$sth_getPageID->execute($page_url, $scan_id, $site_id);

	my $page_id;

	if ($sth_getPageID->rows <= 0)
	{
		# Must create the page

		$sth_insertPage->execute($site_id, $scan_id, $page_url);

		$sth_getLastInsertID->execute();

		my @array = $sth_getLastInsertID->fetchrow_array;

		$PAGES{$token} = $array[0];

		return $array[0];
	}
	else
	{
		my @array = $sth_getPageID->fetchrow_array;

		$PAGES{$token} = $array[0];

		return $array[0];
	}
}


sub getLinkIDByURL($$$)
{
	my ($page_id, $link_url, $broken) = @_;

	my $token = join("|", $page_id, $link_url, $broken);

	if (exists($LINKS{$token}))
	{
		return $LINKS{$token};
	}

	$sth_getLinkID->execute($link_url, $page_id, $broken);

	if ($sth_getLinkID->rows <= 0)
	{
		# Must create the page

		my $internal = 0;

		if ($link_url =~ m#(http(s)*://(.*?)\.*${DOMAIN})#i)
		{
			$internal = 1;
		}


		$sth_insertLink->execute($page_id, $link_url, $internal, $broken);

		$sth_getLastInsertID->execute();

		my @array = $sth_getLastInsertID->fetchrow_array;

		$LINKS{$token} = $array[0];

		return $array[0];
	}
	else
	{
		my @array = $sth_getLinkID->fetchrow_array;

		$LINKS{$token} = $array[0];

		return $array[0];
	}
}


sub getDate()
{
	my @array = localtime(time);
	$array[5] += 1900;
	$array[4]++;

	foreach my $ref ($array[4], $array[3], $array[2], $array[1], $array[0])
	{
		if ($ref < 9)
		{
			$ref = "0" . $ref;
		}
	}

	return join("-", $array[5], $array[4], $array[3]) . " " . join(":", $array[2], $array[1], $array[0]);
}


sub newScan($)
{
	my ($scan_date, $site_id) = @_;

	$sth_insertScan->execute($SCAN_DATE);

	$sth_getLastInsertID->execute();

	my @array = $sth_getLastInsertID->fetchrow_array;

	return $array[0];

}
