package dinfo;

use Carp;

use PApp::SQL;

# 2031616: 0:435514 1:44163 2:107528 3:127671 4:8311 5:2028604 6:117468 7:8210 8:11754 9:24860 14:4344 
# 4128768: 0:650846 1:92556 2:183027 3:170894 4:8605 5:4122242 6:147178 7:8238 8:11802 9:31453 14:6460 

sub new {
   my ($class, $dsn, $user, $pass) = @_;

   my $self = bless {
      dbh => (PApp::SQL::connect_cached "dinfo::", $dsn, $user, $pass),
   }, $class;

   $self;
}

my @cvtid = (
  [name    => 0],
  [vorname => 1],
  [zusatz1 => 2],
  [zusatz2 => 3],
  [zusatz3 => 4],
  [vorwahl => 5],
  # nummer
  [strasse => 7],
  [haus    => 8],
  [plz     => 9],
  [ort     => 10],
  #[ort     => 10], # Ortsteil
  [branche => 11],
  [typ     => 12],
);

sub import {
   my ($self, $path) = @_;

   local $PApp::SQL::DBH = $self->{dbh};

   my @sql;
   my $flush_sql = sub {
       sql_exec "insert into row values " . (join ", ", ("(?, ?, ?,  ?, ?, ?,  ?, ?, ?,  ?, ?, ?)") x @sql),
                map @$_, @sql;
       @sql = ();
   };

   sql_exec "truncate $_"
      for qw(branche haus name ort plz row strasse typ vorname vorwahl zusatz1 zusatz2 zusatz3);

   sql_exec "lock tables " . join ", ", map "$_ write",
          qw(branche haus name ort plz row strasse typ vorname vorwahl zusatz1 zusatz2 zusatz3);

   open my $fh, "<", $path
      or croak "$path: $!";

   while (<$fh>) {
      chomp;
      my @data = split /\t/;
      
      $data[3] =~ s/^, //;
      $data[7] =~ s/\.$// && $data[6] =~ s/ Geb$/Geb./;

      $data[6] =~ /^((?:[a-zA-Z][-.\/a-zA-Z0-9]*)?) (?:\s*\+)? \s* ([0-9\ ]+)$/x or do {
         warn "ERR: unparseable telnr. '$data[6]'";
         next;
      };

      $data[6] = $2;
      $data[12] = $1;

      ($data[5] eq substr $data[6], 0, length $data[5])
         and substr $data[6], 0, length $data[5], "";

      if (length $data[6] > 12) {
         warn "ERR: number too long '$data[6]'";
         next;
      }

      for (@cvtid) {
         my ($name, $idx) = @$_;
         $data[$idx] = ($cache[$idx]{$data[$idx]}
                        ||= sql_insertid sql_exec
                               "insert into $name (id, data) values (NULL, ?)", "$data[$idx]");
      }

      $data[12] < 16 or die "ERR: too many typ's";

      $data[6] =~ s/ //g;
      $data[6] = pack "H*", (substr "$data[6]000000000", 0, 12) . sprintf "%x%x", 12 - length $data[6], $data[12];
      #warn unpack "H*", $data[6];
      
      #name vorname zusatz1 zusatz2 zusatz3 vorwahl nummer strasse haus plz ort branche 

      push @sql, [
               $data[0], $data[1], $data[2], $data[3], $data[4],
               $data[5], $data[6],
               $data[7], $data[8], $data[9], $data[10], $data[11],
                 ];

      &$flush_sql if @sql > 1000;

      $count++ & 4095 or warn time . " $count";

      if (0) {
      $freq[$_]{$data[$_]}++ for (0..9, 14);

      unless (++$x & 65535) {
         print "$x: ";
         for (0..9, 14) {
            print "$_:".(scalar keys %{$freq[$_]})." ";
         }
         print "\n";
      }
      }
   }

   &$flush_sql if @sql;
}

my $db = new dinfo "DBI:mysql:dinfo";

$db->import("main.tsv");