#!/usr/bin/perl
# Import data from old IPDB to new DB
# Started 06/29/04 kdeugau@vianet

#use warnings;
#use CGI::Carp qw/fatalsToBrowser/;
use DBI;
#use DBD::Pg;
#use CommonWeb qw/:ALL/;
#use POSIX qw/ceil/;
use NetAddr::IP;

#use strict;

$null = new NetAddr::IP "255.255.255.255/32";

# Hardcode the masters because it's too messy and slow to
# snag them programmatically *EVERY TIME*
@masters = ((new NetAddr::IP "66.186.64.0/19"),
	(new NetAddr::IP "204.138.172.0/24"),
	(new NetAddr::IP "204.187.88.0/23"),
	(new NetAddr::IP "205.207.184.0/23"),
	(new NetAddr::IP "206.130.64.0/24"),
	(new NetAddr::IP "209.91.128.0/18"),
	(new NetAddr::IP "10.0.0.0/8"),
	(new NetAddr::IP "192.168.0.0/16"),
	);

# First step:  Slurp up data to find "master" blocks
        my $DSN  = "DBI:mysql:ipdb";
        my $user = 'root';
        my $pw   = '';
        $dbh = DBI->connect($DSN, $user, $pw) || return undef if(!$dbh);

$newdbh = DBI->connect("DBI:Pg:dbname=ipdb", "ipdb", "");

# Snag the static IPs (/32 and probably /31 (WTF?)
$sth = $dbh->prepare("select * from blocks where netmask='/32' or netmask='/31'");
$sth->execute;
while (@data = $sth->fetchrow_array) {
  $cidr = new NetAddr::IP "$data[0].$data[2]$data[1]";
  @staticblocks = $null->compact($cidr,@staticblocks);
  $sth2 = $newdbh->prepare("insert into poolips values ('$cidr','$cidr','$data[4]','$data[3]','c','n','$data[7]\n$data[8]')");
  $sth2->execute;
  if ($sth2->err) { print "err on $cidr: ".$sth2->err."\tstring: ".$sth2->errstr."\n"; }
}

#foreach $static (@staticblocks) {
#  print "static: $static\n";
#}

$sth = $newdbh->prepare("insert into masterblocks values (?)");
$sth2 = $newdbh->prepare("insert into freeblocks values (?,?,'Sudbury','n')");
foreach $master (@masters) {
  print "$master\n";
  $sth->execute("$master");
  $sth2->execute("$master",$master->masklen);
}

$i=0;
$sth = $dbh->prepare("select * from blocks where netmask !='/32' and netmask !='/31'");
$sth->execute;
CIDR: while (@data = $sth->fetchrow_array) {
#  $blockdata[$i] = join '|',@data;
#  $cidrlist[$i] = new NetAddr::IP "$data[0].$data[2]$data[1]";
  # data contains ipclass,netmask,subnet,city,custid,name,swip,description,notes
  $cidr = new NetAddr::IP "$data[0].$data[2]$data[1]";

# Munge single-quotes in name, desc and notes
  $data[5] =~ s/'/''/g;
  $data[7] =~ s/'/''/g;
  $data[8] =~ s/'/''/g;

# Here's a better idea.  Instead of just upcasing the city, mangle it "properly".
# This also lets us correct mistakes in the source data... sort of.
#  $data[3] =~ tr/a-z/A-Z/;
  if ($data[3] =~ /BARRIE/i) {
    $data[3] = "Barrie";
  } elsif ($data[3] =~ /BEAMSVILLE/i) {
    $data[3] = "Beamsville";
  } elsif ($data[3] =~ /BLIND RIVER/i) {
    $data[3] = "Blind River";
  } elsif ($data[3] =~ /BRACEBRIDGE/i) {
    $data[3] = "Bracebridge";
  } elsif ($data[3] =~ /BURK'?S FALLS/i) {
    $data[3] = "Burk''s Falls";
  } elsif ($data[3] =~ /BURLINGTON/i) {
    $data[3] = "Burlington";
  } elsif ($data[3] =~ /CAMBRIDGE/i) {
    $data[3] = "Cambridge";
  } elsif ($data[3] =~ /CHAPLEAU/i) {
    $data[3] = "Chapleau";
  } elsif ($data[3] =~ /ELLIOT LAKE/i) {
    $data[3] = "Elliot Lake";
  } elsif ($data[3] =~ /ESPANOLA/i) {
    $data[3] = "Espanola";
  } elsif ($data[3] =~ /GRAVENHURST/i) {
    $data[3] = "Gravenhurst";
  } elsif ($data[3] =~ /HAMILTON/i) {
    $data[3] = "Hamilton";
  } elsif ($data[3] =~ /HUNTSVILLE/i) {
    $data[3] = "Huntsville";
  } elsif ($data[3] =~ /INGERSOLL/i) {
    $data[3] = "Ingersol";
  } elsif ($data[3] =~ /KAPUSKASING/i) {
    $data[3] = "Kapuskasing";
  } elsif ($data[3] =~ /KIRKLAND LAKE/i) {
    $data[3] = "Kirkland Lake";
  } elsif ($data[3] =~ /LINDSAY/i) {
    $data[3] = "Lindsay";
  } elsif ($data[3] =~ /LITTLE CURRENT/i) {
    $data[3] = "Little Current";
  } elsif ($data[3] =~ /LIVELY/i) {
    $data[3] = "Lively";
  } elsif ($data[3] =~ /LONDON/i) {
    $data[3] = "London";
  } elsif ($data[3] =~ /MANITOULIN/i) {
    $data[3] = "Manitoulin";
  } elsif ($data[3] =~ /MARATHON/i) {
    $data[3] = "Marathon";
  } elsif ($data[3] =~ /MARKHAM/i) {
    $data[3] = "Markham";
  } elsif ($data[3] =~ /MISSISAUGA/i) {
    $data[3] = "Missisauga";
  } elsif ($data[3] =~ /MOONBEAM/i) {
    $data[3] = "Moonbeam";
  } elsif ($data[3] =~ /NEPEAN (OTTOWA)/i) {
    $data[3] = "Nepean (Ottawa)";
  } elsif ($data[3] =~ /NIPIGON/i) {
    $data[3] = "Nipigon";
  } elsif ($data[3] =~ /NORTH BAY/i) {
    $data[3] = "North Bay";
  } elsif ($data[3] =~ /^OTTAWA$/i) {
    $data[3] = "Ottawa";
  } elsif ($data[3] =~ /PARRY SOUND/i) {
    $data[3] = "Parry Sound";
  } elsif ($data[3] =~ /PEMBROKE/i) {
    $data[3] = "Pembroke";
  } elsif ($data[3] =~ /PORT CARLING/i) {
    $data[3] = "Port Carling";
  } elsif ($data[3] =~ /SAULT STe?.? MARIE/i) {
    $data[3] = "Sault Ste. Marie";
  } elsif ($data[3] =~ /STURGEON FALLS/i) {
    $data[3] = "Sturgeon Falls";
  } elsif ($data[3] =~ /SUCKER CREEK/i) {
    $data[3] = "Sucker Creek";
  } elsif ($data[3] =~ /SUDBURY/i) {
    $data[3] = "Sudbury";
  } elsif ($data[3] =~ /SUNDRIDGE/i) {
    $data[3] = "Sundridge";
  } elsif ($data[3] =~ /THESSALON/i) {
    $data[3] = "Thessalon";
  } elsif ($data[3] =~ /THUNDER BAY/i) {
    $data[3] = "Thunder Bay";
  } elsif ($data[3] =~ /TIMMINS/i) {
    $data[3] = "Timmins";
  } elsif ($data[3] =~ /TORONTO/i) {
    $data[3] = "Toronto";
  } elsif ($data[3] =~ /VALLEY/i) {
    $data[3] = "Valley";
  } elsif ($data[3] =~ /WALDEN/i) {
    $data[3] = "Walden";
  } elsif ($data[3] =~ /WARREN/i) {
    $data[3] = "Warren";
  } elsif ($data[3] =~ /WAWA/i) {
    $data[3] = "Wawa";
  } elsif ($data[3] =~ /WINDSOR/i) {
    $data[3] = "Windsor";
  }
  $cities{$data[3]}=1;

# figger out where the allocation comes from
  $sth2 = $newdbh->prepare("select * from freeblocks");
  $sth2->execute;
  while (@data2 = $sth2->fetchrow_array) {
    $free = new NetAddr::IP "$data2[0]";
    if ($free->contains($cidr)) {
      if ($free->masklen == $cidr->masklen) {
	# delete from freeblocks
	# add to allocations
	$sth2 = $newdbh->prepare("delete from freeblocks where cidr='$cidr'");
	$sth2->execute;
if ($sth2->err) { print "error on delete $cidr from freeblocks: ".$sth2->errstr."\n"; }
	# All data to be inserted as customer network.  Cleanup will have to
	# be manual due to the messiness of existing data.  >:(
	$sth2 = $newdbh->prepare("insert into allocations values ('$cidr','$data[4]',".
                "'cn','$data[3]','$data[5]','$data[7]\n$data[8]',".$cidr->masklen.")");
	$sth2->execute;
#if ($sth2->err) { print "error on insert $cidr allocation: ".$sth2->errstr."\n"; }
      } else { # end free == cidr
        $searchme = $free;
        $sth2 = $newdbh->prepare("delete from freeblocks where cidr='$searchme'");
        $sth2->execute;
	undef @freelist;
        while ($searchme->masklen < $cidr->masklen) {
          @temp = $searchme->split($searchme->masklen+1);
          if ($temp[0]->contains($cidr)) {
            $newfree = $temp[1];
	    $searchme = $temp[0];
          } else {
            $newfree = $temp[0];
	    $searchme = $temp[1];
          }
          push @freelist, ($newfree);
        } #while
	$sth2 = $newdbh->prepare("insert into allocations values ('$cidr','$data[4]',".
                "'cn','$data[3]','$data[5]','$data[7]\n$data[8]',".$cidr->masklen.")");
	$sth2->execute;
if ($sth2->err) { print "error on insert $cidr allocation: ".$sth2->errstr."\n"; }
	$sth2 = $newdbh->prepare("insert into freeblocks values (?,?,'Sudbury','n')");
	foreach $ip (@freelist) {
	  $sth2->execute("$ip",$ip->masklen);
#print "insert into freeblocks values ('$ip',".$ip->masklen.",'Sudbury','n')\n";
if ($sth2->err) { print "error on insert $ip freeblock: ".$sth2->errstr."\n"; }
	}

      } # free != cidr
      next CIDR;
    } # free contains cidr
  }

  $i++;
}

$sth = $dbh->prepare("select * from blocks where netmask ='/24'");
$sth->execute;
while (@data = $sth->fetchrow_array) {
#  $blockdata[$i] = join '|',@data;
#  $cidrlist[$i] = new NetAddr::IP "$data[0].$data[2]$data[1]";
  # data contains ipclass,netmask,subnet,city,custid,name,swip,description,notes
  $cidr = new NetAddr::IP "$data[0].$data[2]$data[1]";

# Here's a better idea.  Instead of just upcasing the city, mangle it "properly".
# This also lets us correct mistakes in the source data... sort of.
#  $data[3] =~ tr/a-z/A-Z/;
  if ($data[3] =~ /BARRIE/i) {
    $data[3] = "Barrie";
  } elsif ($data[3] =~ /BEAMSVILLE/i) {
    $data[3] = "Beamsville";
  } elsif ($data[3] =~ /BLIND RIVER/i) {
    $data[3] = "Blind River";
  } elsif ($data[3] =~ /BRACEBRIDGE/i) {
    $data[3] = "Bracebridge";
  } elsif ($data[3] =~ /BURK'?S FALLS/i) {
    $data[3] = "Burk's Falls";
  } elsif ($data[3] =~ /BURLINGTON/i) {
    $data[3] = "Burlington";
  } elsif ($data[3] =~ /CAMBRIDGE/i) {
    $data[3] = "Cambridge";
  } elsif ($data[3] =~ /CHAPLEAU/i) {
    $data[3] = "Chapleau";
  } elsif ($data[3] =~ /ELLIOT LAKE/i) {
    $data[3] = "Elliot Lake";
  } elsif ($data[3] =~ /ESPANOLA/i) {
    $data[3] = "Espanola";
  } elsif ($data[3] =~ /GRAVENHURST/i) {
    $data[3] = "Gravenhurst";
  } elsif ($data[3] =~ /HAMILTON/i) {
    $data[3] = "Hamilton";
  } elsif ($data[3] =~ /HUNTSVILLE/i) {
    $data[3] = "Huntsville";
  } elsif ($data[3] =~ /INGERSOLL/i) {
    $data[3] = "Ingersol";
  } elsif ($data[3] =~ /KAPUSKASING/i) {
    $data[3] = "Kapuskasing";
  } elsif ($data[3] =~ /KIRKLAND LAKE/i) {
    $data[3] = "Kirkland Lake";
  } elsif ($data[3] =~ /LINDSAY/i) {
    $data[3] = "Lindsay";
  } elsif ($data[3] =~ /LITTLE CURRENT/i) {
    $data[3] = "Little Current";
  } elsif ($data[3] =~ /LIVELY/i) {
    $data[3] = "Lively";
  } elsif ($data[3] =~ /LONDON/i) {
    $data[3] = "London";
  } elsif ($data[3] =~ /MANITOULIN/i) {
    $data[3] = "Manitoulin";
  } elsif ($data[3] =~ /MARATHON/i) {
    $data[3] = "Marathon";
  } elsif ($data[3] =~ /MARKHAM/i) {
    $data[3] = "Markham";
  } elsif ($data[3] =~ /MISSISAUGA/i) {
    $data[3] = "Missisauga";
  } elsif ($data[3] =~ /MOONBEAM/i) {
    $data[3] = "Moonbeam";
  } elsif ($data[3] =~ /NEPEAN (OTTOWA)/i) {
    $data[3] = "Nepean (Ottawa)";
  } elsif ($data[3] =~ /NIPIGON/i) {
    $data[3] = "Nipigon";
  } elsif ($data[3] =~ /NORTH BAY/i) {
    $data[3] = "North Bay";
  } elsif ($data[3] =~ /^OTTAWA$/i) {
    $data[3] = "Ottawa";
  } elsif ($data[3] =~ /PARRY SOUND/i) {
    $data[3] = "Parry Sound";
  } elsif ($data[3] =~ /PEMBROKE/i) {
    $data[3] = "Pembroke";
  } elsif ($data[3] =~ /PORT CARLING/i) {
    $data[3] = "Port Carling";
  } elsif ($data[3] =~ /SAULT STe?.? MARIE/i) {
    $data[3] = "Sault Ste. Marie";
  } elsif ($data[3] =~ /STURGEON FALLS/i) {
    $data[3] = "Sturgeon Falls";
  } elsif ($data[3] =~ /SUCKER CREEK/i) {
    $data[3] = "Sucker Creek";
  } elsif ($data[3] =~ /SUDBURY/i) {
    $data[3] = "Sudbury";
  } elsif ($data[3] =~ /SUNDRIDGE/i) {
    $data[3] = "Sundridge";
  } elsif ($data[3] =~ /THESSALON/i) {
    $data[3] = "Thessalon";
  } elsif ($data[3] =~ /THUNDER BAY/i) {
    $data[3] = "Thunder Bay";
  } elsif ($data[3] =~ /TIMMINS/i) {
    $data[3] = "Timmins";
  } elsif ($data[3] =~ /TORONTO/i) {
    $data[3] = "Toronto";
  } elsif ($data[3] =~ /VALLEY/i) {
    $data[3] = "Valley";
  } elsif ($data[3] =~ /WALDEN/i) {
    $data[3] = "Walden";
  } elsif ($data[3] =~ /WARREN/i) {
    $data[3] = "Warren";
  } elsif ($data[3] =~ /WAWA/i) {
    $data[3] = "Wawa";
  } elsif ($data[3] =~ /WINDSOR/i) {
    $data[3] = "Windsor";
  }

# insert entry in routed table for /24 allocations.  We'll clean it up later.
  if ($cidr->masklen == 24) {
    $sth2 = $newdbh->prepare("insert into routed values ".
	"('$cidr',".$cidr->masklen.",'$data[3]')");
#print "insert into routed values ('$cidr',".$cidr->masklen.",'$data[3]')\n";
    $sth2->execute;
    if ($sth2->err) { print "error on insert $cidr route: ".$sth2->errstr."\n"; }
  }
}



$i=0;
#foreach $data (@blockdata) {
#  @bits = split /\|/,$data;
#  # ipclass,mask,subnet,city,custid,customer name,swip,desc,notes
##  print $data[0];
#  if ($bits[0] =~ /\d{1,3}\.\d{1,3}\.\d{1,3}/) {
##    $bits[0] .= ".0/24";
#    $cidrlist[$i++] = new NetAddr::IP "$bits[0].$bits[2]$bits[1]";
##    print $cidrlist[$i-1]."\n";
#  } else {
#    print "odd: $data[0].$data[2]$data[1]\n";
#  }
##  $bits[3] = s/\s+//g;
#  $bits[3] = tr/a-z/A-Z/;
#  $cities{$bits[3]}=1;
#}

@citylist = keys %cities;

print "done first slurp\n";

# snag master blocks from list of IPs.
#$i=0;
#$sth = $dbh->prepare("select * from ips");
#$sth->execute;
#while (@data = $sth->fetchrow_array) {
#  my $cidr = new NetAddr::IP "$data[0]/32";
#  if ($data[1] eq 1) {
#    @usedlist = $cidr->compact(@usedlist);
#  } else {
#    @freelist = $cidr->compact(@freelist);
#  }
#  $i++;
#  if (($i%500)==0) { print "."; }
#}
#@masters2 = $null->compact(@freelist,@usedlist);
#foreach $temp (@masters2) {
#  print "altmaster: $temp\n";
#}

#open FREELIST,">freelist";
#foreach $freeip (@freelist) {
#  print FREELIST "$freeip\n";
#}
#open USEDLIST,">usedlist";
#foreach $usedip (@usedlist) {
#  print USEDLIST "$usedip\n";
#}

exit 0;

# Hokay.  Playing with the raw data doesn't seem to be working.  :/

$sth = $dbh->prepare("select * from blocks where city=? and netmask != '/32'");
foreach $city (@citylist) {
  print "$city\n";
  $sth->execute($city);
  my $i=0;
  my @routelist;
  while (@data = $sth->fetchrow_array) {
    $cidr = new NetAddr::IP "$data[0].$data[2]$data[1]";
#    print "  $cidr\n";
    $errcode = eval { @routelist = $null->compact($cidr,@routelist); };
    if ($@) { print "compact $cidr broke: ($errcode) $@\n"; }
#print "$cidr:\n";
#    print "\t$data[0]\n";
#print "$data[0] $cidr\n";
  }
#  foreach $route (@routelist) {
#    print "   $route\n";
#  }
  #eval { unset $routelist; };
}

#foreach $city (@citylist) {
#  foreach $cidrcitylist (@cidrlist{$city}) {
#    print "$city: $cidrcitylist[1]\n";
#  }
#}

exit 0;

foreach $block (@blockdata) {
  @bits = split /\|/, $block;
#print $bits[3]."\t";
  foreach $city (@citylist) {
#    print "$city:\n";
    if ($city eq $bits[3]) {
      $cidr = new NetAddr::IP "$bits[0].$bits[2]$bits[1]";
      @roughroutes{$city} = $null->compact($cidr, @roughroutes{$city});
#print $block."\n";
#print "$#roughroutes $roughroutes[0] $cidr\n";
#print "$city\t$bits[3]\n";
#      print $city."\n";
    }
  }
#  print "$city: $#(roughroutes{$city})\n";
#  unset $roughroutes;
}

foreach $city (@citylist) {
  foreach $route (@roughroutes{$city}) {
    print "$city route: $route\n";
  }
}
