#!/usr/bin/perl -w

use strict;

use Geo::GeoNames;
use Data::Dumper;
use DBI;
# use DBD::Pg;
use DBD::mysql;

# my $dbh = DBI->connect("dbi:Pg:dbname=nsrc_loc;host=localhost", "nsrc_loc", "");
my $dbh = DBI->connect("dbi:mysql:dbname=nsrc_loc;host=localhost", "nsrc_loc", "nsrc_loc");

# import('old/afr_ix.html', 'ixp');
update_lat_lon();

# Subs

sub import() {

	my ($file, $type) = @_;

	open(OLDMAP, "< $file") || die "Couldn't open $file";

	# If this was properly formatted XML, we'd use xpath...
	my $country = '';
	my $name = '';
	my $url = '';
	my $descr = '';
	my $established = '';
	my $kind_id = '';

	my $sth;

	$sth = $dbh->prepare("SELECT id FROM kind WHERE type='$type'");
	$sth->execute;
	while ( my @row = $sth->fetchrow_array ) {
	 	$kind_id = $row[0];
	}

	$sth = $dbh->prepare("INSERT INTO data(kind_id, country, url, descr, name, established) VALUES (?,?,?,?,?,?)");

	while(<OLDMAP>) {
		if (/<!-- (.*?) -->/) {
			$country = $1;
			print "$country\n";
		}
	
		if (m|area shape|) {
			if (m|.*href="(.*?)".*onMouseOver="window.status='(.*?); (.*?)(?:[,;])\s+Established (.*?)'|) {
			# "
				$url = $1;
				$descr = $2;
				$name = $3;
				$established = $4;
				print "  $url | $descr | $name | $established\n";

				$sth->execute($kind_id, $country, $url, $descr, $name, $established);
			}
		}
	}
	close(OLDMAP);
}

sub update_lat_lon() {

	my $geo = new Geo::GeoNames();

	# We should grab the city and not the country
	my $sth = $dbh->prepare("SELECT id,country,city from data");
	$sth->execute;

	while ( my @row = $sth->fetchrow_array ) {

		my $id = $row[0];
		my $country = $row[1];
		my $city = $row[2];

		my $result;

		if ($city) {
			$result = $geo->search(q => "$city, $country", maxRows => 1);
		}  else {
			$result = $geo->search(q => $country, maxRows => 1);
		}

		my $lat = $result->[0]->{lat};
		my $lon = $result->[0]->{lng};

		$dbh->do("UPDATE data SET lat = $lat, lon = $lon WHERE id = $id");

		# print " Name: " . $result->[0]->{name} . "\n";
		# print " Longitude: " . $result->[0]->{lng} . "\n";
		# print " Lattitude: " . $result->[0]->{lat} . "\n";
	}

}