#/usr/bin/env perl

use DBI;

use Net::CIDR;

my $dbh = DBI->connect("dbi:Pg:dbname=Cables_DB;host=localhost", 'mydatabaseuser', 'secretpassword', {AutoCommit => 0, RaiseError => 0})
        or die("can't connect to DB");

$dbh->do("TRUNCATE geoip");
my $insth = $dbh->prepare_cached("INSERT INTO geoip (netblock, country_code, country_name) VALUES (?,?,?)")
        or die($dbh->errstr);

foreach my $file (qw[GeoIPCountryWhois.csv GeoIPv6.csv]) {
    print "Running on file $file...\n";
    open(my $ifh, '<', $file) or die("Can't open $file");
    my $linecount = 0;

    while((my $line = <$ifh>)) {
        $linecount++;
        chomp $line;
        my ($firstip, $lastip, undef, undef, $countrycode, $countryname) = split/\"\,\ ?\"/, $line;
        $firstip = sanitize($firstip);
        $lastip = sanitize($lastip);
        $countrycode = sanitize($countrycode);
        $countryname = sanitize($countryname);

        my @cidr = Net::CIDR::range2cidr($firstip . '-' . $lastip);

        foreach my $subcidr (@cidr) {
            $insth->execute($subcidr, $countrycode, $countryname) or die("Fail on $linecount $line: " . $dbh->errstr);
        }

    }
    close $ifh;
}

$dbh->commit;

sub sanitize {
    my ($val) = @_;

    $val =~ s/^\"//;
    $val =~ s/\"$//;

    return $val;
}