#!/usr/bin/perl -w

use strict;

use CGI qw(:standard);

use DBI;
use DBD::Pg;
# use DBD::mysql;

# The key should be in the DB
# NSRC.org
my $key = 'AIzaSyAmnLrbIO4U3X0K3ch5nVTYpOYMBXXFiZk';

# The HTML template for the page
# my $template = '../templates/template.html';
my $template = '/var/www/share/nsrc.org/AFRICA/nsrc_loc/templates/template.html';

my %rgba = ('RED' => '255,0,0',
			'GREEN' => '0,255,0',
			'YELLOW' => '255,255,0',
			'GRAY'	=> '128,128,128');


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

# We should grab the city and not the country
my $sth = $dbh->prepare("SELECT data.id,data.state_id,lat,lon,name,descr,established,url,img,type,color,city,country from data, kind WHERE kind.id = data.kind_id AND visible = 'true' ORDER by country,city ASC");
$sth->execute;

print "content-type: text/html\n\n";

my @table;
my @map;

open (TMPL, "<$template") || die;
while (<TMPL>) {

	# Replace API key with real value
	if (! /<!-- %MAP% -->/ ) {
		s/%KEY%/$key/;
		print;

	# Replace template values
	} else {

		my $alt = 0;

		my ($id, $state, $lat, $lon, $name, $descr, $established, $url, $img, $type, $color, $city, $country);
		while ( my @row = $sth->fetchrow_array ) {

			($id, $state, $lat, $lon, $name, $descr, $established, $url, $img, $type, $color, $city, $country) = @row;

			# There may be more than one URL, we store these in the url column
			# separated with ||. Yes, hack.

			my (@urls, $urls);
			if ($url) {
				if ($url =~/||/) {
					@urls = split(/\|\|/, $url);
				} else {
					push @urls, $url;
				}
				foreach (@urls) { $urls .= "<a href='$_'>$_</a><br>" };
			} else {
				$urls = '';
			}

 			if ($img) {
				$img = "<IMG SRC=\"$img\">";
			} else {
				$img = '';
			}
			my $type_code = uc(substr($type,0,1));
			# Ok, normally we get the color via the kind table - instead,
			# since we ended up only using this for IXPs, we might as well
			# forget the KIND table, and instead use the state_id attribute
			# to determine the color
			if ($state == 1) {
				$color = 'GREEN';
			} elsif ($state == 2) {
				$color = 'YELLOW';
			} elsif ($state == 3) {
				$color = 'RED';
			} else {
				$color = 'GRAY';
			}

			# Alternate bg colors colors on the table rows
			my $background;
			if ($alt == 0) {
				$background = 'lightgray'; $alt = 1;
			} else {
				$background = 'white'; $alt = 0;
			}
	
# XXX Instead of hardcoding MINI, pull the marker type from the DB (TBD)
		push @map, <<EOF;
	<a id="$id" title="$name" href="http://maps.google.com/maps?ll=$lat,$lon&hl=en">
		$color MINI
	</a>
	<div title="Info">
		$name<br>
		$descr<br>
		$established<br>
		$urls
	</div>
	<div title="Photo">
		$img<br>
	</div>
EOF

			# XXX generalize this: populate data structure, and do in place for both 
			# table and map
			my $bgcolor = $rgba{$color};
			push @table, "<tr style='background: $background;'>
			<td style=\"background: rgba($bgcolor,0.5); text-align: center;\"><a href=\"#$id\" class=\"ZOOM\">$name</a></td>
			<td>$descr</td>
			<td>$established</td>
			<td>$urls</td>
			<td>$city</td>
			<td>$country</td>
			</tr>\n";

		}
	}
	if (/<!-- %MAP% -->/) {
		foreach (@map) {
			print;
		}
	}
	if (/<!-- %TABLE% -->/) {
		foreach (@table) {
			print;
		}
	}
}