#!/usr/bin/perl

use strict;
use Archive::Zip;
use Data::Dumper;
use DBI;
my $dbh = DBI->connect('dbi:Pg:dbname=hellas',undef,undef,
    {AutoCommit => 0, RaiseError => 1});

my @files = @ARGV;
my $countries = 'fips_countries.txt';
my %countries;

my $sth = $dbh->prepare("select id, fips from country");
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
	$countries{lc($row->{fips})} = $row->{id};
}

my %types;
my $sth = $dbh->prepare("select id, code from feature");
$sth->execute;
while (my $row = $sth->fetchrow_hashref) {
	# warn "$row->{code}:$row->{id}\n";
        $types{$row->{code}} = $row->{id};
}

my $count = 0;
my $counted = 0;
foreach my $arch (@files) {
	my $zip = Archive::Zip->new;
	warn("$arch\n");
	$zip->read( $arch );
	my $code = $arch;
	$code =~ s/\.zip//;	
	my @members = $zip->members;
	my $txt;	
	foreach (@members) {
		#warn(Dumper($_));
		$txt = $zip->extractMember($_);
	}
	use File::Basename;
	my $filename = basename($code) .".txt";
	print $filename, "\n";
	open(FILE, "<:utf8", $filename);
	scalar <FILE>; # throw away header 
	while (<FILE>) {	
		
		my @fields = split("\t",$_); 
		my ($RC,$UFI,$LAT,$LONG,$FC,$DSG,$ADM1,$ADM2,
		    $GENERIC,$FULL_NAME,$FULL_NAME_ND) = 
			@fields[0,1,3,4,9,10,13,14,20,22,23];
		# $UFI =~ s/\D//g;
		my $sql = qq/
		    INSERT INTO place (name,name_nd,country,ufi,
					feature_type,lat,lon) 
		    values (?, ?, ?, ?, ?, ?, ?)/;
		$DSG =~ s/\W//g;
		my $type = $types{$DSG};
		    # or die "Can't find type '$DSG'\n";
		$dbh->do($sql, {}, $FULL_NAME, $FULL_NAME_ND,
			$countries{$code}, 
			$UFI, $type, $LAT, $LONG );
		$count++;
		if ($count > 5000) {
			$dbh->commit;
			$count = 0;	
		}
	}
	close FILE;
	unlink $filename;
	#exit;	   	
}
$dbh->commit;
$dbh->disconnect;