#!/usr/bin/perl
# (C) 2005 Dave Hansen <dave@sr71.net>
# 
# This code is licenced under the GPL.
#

#ALTER TABLE coord_attribute ADD CONSTRAINT valid_coord_id FOREIGN KEY (coord_id) REFERENCES gps_coordinate (id) MATCH FULL ON DELETE CASCADE;
#ALTER TABLE coord_attribute ADD CONSTRAINT valid_attr_id FOREIGN KEY (attr_id) REFERENCES attribute (id) MATCH FULLON DELETE CASCADE;

# list all tracks which have points
# SELECT attribute.value,count(coord_attribute.*) from attribute,coord_attribute where type_id in (SELECT id from attribute_type where name='track') and attribute.id = coord_attribute.attr_id group by attribute.value;

# list all tracks, and their number of points, even if empty
# SELECT attribute.value,attribute.type_id,count(coord_attribute.*) from attribute left outer join coord_attribute on (attribute.id = coord_attribute.attr_id) where type_id in (SELECT id from attribute_type where name='track')  group by attribute.value,attribute.type_id ;

# create view pretty_attribute as SELECT attribute.id as attr_id, attribute_type.name, attribute.value from attribute, attribute_type where attribute.type_id = attribute_type.id;

# make all points with the attribute with id 8574 also have the attribute 
# that has id 11264
# insert into coord_attribute SELECT coord_id, 11264 as attr_id from coord_attribute where attr_id = 8574;

# move all coordinates with track_seq_nr <= 51 from
# Kyes_Peak_work track to Kyes_Peak_car_to_camp
# update coord_attribute 
# 	set attr_id = (select attr_id from pretty_attribute
# 		       where name = 'track' 
# 		         and value = 'Kyes_Peak_car_to_camp')
# where coord_id in (SELECT id from gps_coordinate_by_track
# 		     where track_name = 'Kyes_Peak_work' and track_seq_nr <= 51)
#   and attr_id = (SELECT attr_id from pretty_attribute where name = 'track'
#   		   and value = 'Kyes_Peak_work');

#create table gps_coordinate (id serial not null primary key, longitude float ,  latitude float, name varchar(40), UNIQUE (latitude, longitude));

#cat WA.summit.html.1.txt  | perl -pe 's/[\137\010]//g' | perl -pe 's/\s+\|/\|/g' | grep -v gif | awk -F\| '{print "insert into gps_coordinate (longitude, latitude) values (" "'\''" $5 "'\''" ", " "'\''" $4 "'\'');"}' > gps_coords.sql

#cat WA.summit.html.1.txt  | perl -pe 's/[\137\010]//g' | perl -pe 's/\s+\|/\|/g' | grep -v gif | awk -F\| '{print "insert into coord_attribute (coord_id, attr_id) values ((select id from gps_coordinate where longitude=" "'\''" $5 "'\''" " and latitude=" "'\''" $4 "'\''), (select id from attribute where value = " "'\''" $3 "'\'' ));"}' > gps_coords.1.sql

#for i in `cat WA.summit.html.1.txt  | perl -pe 's/[\137\010]//g' | perl -pe 's/\s+\|/\|/g' | grep -v gif | awk -F\| '{print $2}' | sort | uniq`; do psql gps -c "insert into attribute (type_id, value) values (4, '$i')"; done

#set all names
#cat WA.summit.html.1.txt | perl -pe 's/[\137\010]//g' | perl -pe 's/\s+\|/\|/g' | grep -v gif | awk -F\| '{print $2}' | sort | uniq | while read i; do echo "insert into attribute (type_id, value) values (4, '${i/\'/\'}');"; done > gps_coords.set_names.sql



use DBI;
use strict;

my $user = `whoami`;
chomp $user;
$user =~ s/-//g;

my $DB_TYPE = "Pg";
my $DB_NAME = "gps";
my $DB_USER = $user;

sub dprint
{
#	print @_;
}

sub db_connect {
	my $dbname_override = shift;
	if ($dbname_override) {
		$DB_NAME = $dbname_override;
	}
	#my $DB_CONNECT="DBI:$DB_TYPE:dbname=$DB_NAME";
	my ($dbh);
	if ($DB_USER =~ /wwwdata/) {
		my $DB_CONNECT="DBI:$DB_TYPE:dbname=$DB_NAME;host=127.0.0.1";
	        $dbh = DBI->connect($DB_CONNECT, $DB_USER, '3rRqoRnb');
	} else {
		my $DB_CONNECT="DBI:$DB_TYPE:dbname=$DB_NAME";
	        $dbh = DBI->connect($DB_CONNECT, $DB_USER);
	}
        $dbh->{AutoCommit} = 0;

        if (!$dbh) {
                die "Cannot connect to Postgres server: $DBI::errstr\n";
        }
        return $dbh;
}

sub BUG_ON
{
	my $condition = shift;

	if ($condition) {
		die "BUG_ON";
	}
}

sub print_rows
{
	my $sth = shift;
        if ($sth->{'Active'}) {
                my $res;
                my $state = 1;

                while ($res = $sth->fetchrow_hashref()) {
                        if ($state) {
                                foreach (keys %$res) {
                                        print "$_\t";
                                }
                                print "\n";
                                $state = 0;
                        }
                        foreach (keys %$res) {
                                my $str = $res->{$_} ? $res->{$_} : "";
                                print "$str\t";
                        }
                        print "\n";
                }
        }
}

sub query_nr_rows
{
	my $dbh = shift;
	my $query = shift;
	
	my $query_handle = $dbh->prepare($query);
	$query_handle->execute;
	return $query_handle->rows;
}

sub attribute_type_exists
{
	my $dbh = shift;
	my $type_name = shift;
	my $query = "SELECT * from attribute_type where name = '$type_name';";

	if (query_nr_rows($dbh, $query) > 0) {
		return 1;
	} else {
		return 0;
	}
}

sub create_attribute_type
{
	my $dbh = shift;
	my $type_name = shift;
	my $query = "insert into attribute_type (name) values ('$type_name');";

	my $query_handle = $dbh->prepare($query);
	$query_handle->execute;
}

sub get_attribute_type_id
{
	my $dbh = shift;
	my $name = shift;

	my $query = "SELECT id from attribute_type where name = '$name';";
	my @row_ary = $dbh->selectrow_array($query);

	return $row_ary[0];
}

sub create_attribute
{
	my $dbh = shift;
	my $type_id = shift;
	my $attr_value = shift;
	my $query = "insert into attribute (type_id, value) ".
		    "values ($type_id, '$attr_value');";

	my $query_handle = $dbh->prepare($query);
	$query_handle->execute;
}

sub get_attribute_id
{
	my $dbh = shift;
	my $type_id = shift;
	my $attr_value = shift;

	my $query = "SELECT id ".
		    "FROM attribute ".
		    "WHERE value = '$attr_value' ".
		    "  AND type_id = $type_id;";
	my @row_ary = $dbh->selectrow_array($query);

	return $row_ary[0];
}

sub attribute_exists
{
	my $dbh = shift;
	my $attr_type_id = shift;
	my $attr_value = shift;

	my $ret = get_attribute_id($dbh, $attr_type_id, $attr_value);
	dprint "attribute_exists: (id: $attr_type_id val: $attr_value) '$ret' -- '".defined($ret)."' \n";
	return defined($ret);
}


sub create_coordinate
{
	my $dbh = shift;
	my(%coordinate) = %{$_[0]};
	my $lat = %coordinate->{lat};
	my $lon = %coordinate->{lon};
	my $query = "insert into gps_coordinate (latitude, longitude) values ($lat, $lon);";

	my $query_handle = $dbh->prepare($query);
	my $ret = $query_handle->execute;
	if (!$ret) {
		print "create_coordinate(): bad query: '$query'\n";
	}
	return $ret;
}


sub get_coordinate_id
{
	my $dbh = shift;
	my(%coordinate) = %{$_[0]}; shift;
	my $lat = %coordinate->{lat};
	my $lon = %coordinate->{lon};

	my $query = "SELECT id from gps_coordinate where latitude = $lat and longitude = $lon;";
	my @row_ary  = $dbh->selectrow_array($query);

	return $row_ary[0];
}

sub get_coordinate_from_id
{
	my $dbh = shift;
	my $coordinate_id = shift;

	my $query = "SELECT latitude,longitude from gps_coordinate where id = $coordinate_id;";
	dprint "get_coordinate_from_id query: '$query'\n";
	my $hashref = $dbh->selectrow_hashref($query);

	my %coordinate = (lat => $hashref->{latitude},
			  lon => $hashref->{longitude});
	return \%coordinate;
}

sub set_coordinate_attribute
{
	my $dbh = shift;
	my(%coordinate) = %{$_[0]}; shift;
	my $attribute_type = shift;
	my $attribute_value = shift;

	if (!attribute_type_exists($dbh, $attribute_type)) {
		create_attribute_type($dbh, $attribute_type);
	}
	my $attribute_type_id = get_attribute_type_id($dbh, $attribute_type);
	dprint "attribute_type_id: $attribute_type_id\n";

	if (!attribute_exists($dbh, $attribute_type_id, $attribute_value)) {
		create_attribute($dbh, $attribute_type_id, $attribute_value);
	}
	my $attr_id = get_attribute_id($dbh, $attribute_type_id, $attribute_value);
	dprint "attr_id: $attr_id\n";

	my $coord_id = get_coordinate_id($dbh, \%coordinate);
	dprint "coord_id: $coord_id\n";

	my $query = "insert into coord_attribute (coord_id, attr_id) ".
		    "values ($coord_id, $attr_id);";
	my $query_handle = $dbh->prepare($query);
        my $ret = $query_handle->execute;
	if (!$ret) {
		print "set_coordinate_attribute(): bad query: '$query'\n";
	}
	return $ret;
}

sub get_coordinate_attributes
{
	my $dbh = shift;
	my $coordinate_id = shift;
	
	my $query = "SELECT name, value, attribute.id as attr_id ".
		    "FROM attribute, attribute_type, coord_attribute, gps_coordinate ".
		    "WHERE attribute.type_id = attribute_type.id ".
		    "  AND coord_attribute.attr_id = attribute.id ".
		    "  AND coord_attribute.coord_id = gps_coordinate.id ".
		    "  AND gps_coordinate.id = $coordinate_id;";

	dprint "get_coordinate_attributes query: '$query'\n";
	my $hash_ref = $dbh->selectall_hashref($query, "attr_id");
	return $hash_ref;
}

sub print_coordinate_attributes
{
	my $dbh = shift;
	my $coordinate_id = shift;

	my $hashref = get_coordinate_attributes($dbh, $coordinate_id);
	foreach my $key (keys %{$hashref}) {
		print $hashref->{$key}->{name}."=".
			$hashref->{$key}->{value}."\n";
	}
}

sub print_coordinate_xml
{
	my $dbh = shift;
	my $tag_name = shift;
	my $coordinate_id = shift;

	my $hashref = get_coordinate_attributes($dbh, $coordinate_id);
	my $coordinate = get_coordinate_from_id($dbh, $coordinate_id);
	print "\t<$tag_name ".
		'lat="'.$coordinate->{lat}.'" '.
	        'lon="'.$coordinate->{lon}.'" '.
	        'id="'.$coordinate_id.'" ';
	my %namehash;
	foreach my $key (keys %{$hashref}) {
		my $value = $hashref->{$key}->{value};
		my $name = $hashref->{$key}->{name};
		if ($namehash{$name}++ > 0 ) {
			$name .= $namehash{$name};
		}
		$value =~ s/\"/\\"/g;
		print $name."=\"$value\" ";
	}
	print "/>\n";
}

sub get_coordinates_ids_with_attributes
{
	my $dbh = shift;
	my $attrs = shift;
	
	my $query = "";
	foreach my $attr_name (keys %$attrs) {
		dprint "get_query_for_attribute($attr_name,".$attrs->{$attr_name}.");\n";
		if (length($query)) {
			$query .= " INTERSECT ";
		}
		$query .= get_query_for_attribute($dbh, $attr_name,
						  $attrs->{$attr_name});
	}
	
	$query .= ";";
	dprint "$query\n";

	return $dbh->selectcol_arrayref($query);
}

sub get_query_for_attribute
{
	my $dbh = shift;
	my $attr_name = shift;
	my $attr_val = shift;
	my $attr_name_quoted = $dbh->quote($attr_name);
	my $attr_val_quoted = $dbh->quote($attr_val);

	my $query = "SELECT gps_coordinate.id ".
		    "  FROM attribute,attribute_type,coord_attribute,gps_coordinate ".
		    " WHERE attribute.type_id = attribute_type.id ".
		    "   AND coord_attribute.attr_id = attribute.id ".
		    "   AND coord_attribute.coord_id = gps_coordinate.id ".
		    "   AND name = $attr_name_quoted ";
	if ($attr_val) {
		$query .= "AND value = $attr_val_quoted";
	}
	#$query .= " ORDER BY gps_coordinate.id ASC";
}

1