#!/usr/bin/perl -w

# TJ: 2013/09/05-0

use strict;
use warnings;

# Source: http://svn.apache.org/repos/asf/spamassassin/trunk/tools/convert_awl_dbm_to_sql

use DB_File;
use vars qw( %h );

my $username = 'debian-spamd';

{
  my $awl_dbname = "./auto-whitelist";
  my %seen;

  tie %h, "DB_File", $awl_dbname, O_RDONLY, 0600
    or die "Cannot open file $awl_dbname: $!\n";

  for my $key ( grep(!/totscore$/, keys(%h) ) ) 
  {
    #print "koi: $key\n";

    my $totscore = $h{"$key|totscore"};
    my $count = $h{$key};

    if(defined($totscore)) {
      my ($email, $ip) = split(/\|ip=/, $key);

      $email =~ s/^ //g;
      $email =~ s/ $//g;

      # Attention aux doublons !
      my $k_prim="$username\\$email\\$ip";

      if ( $seen{$k_prim} ) {
        print STDERR "WARNING: $key\n";
        print STDERR "       : Deja vu $seen{$k_prim} fois\n";
        $seen{$k_prim}++ ;
      }
      elsif ($email && $ip) {
        $seen{$k_prim}++ ;

        ## my $rc = $sth->execute($opt{'username'}, $email, $ip, $count, $totscore);
        printf "INSERT INTO awl (username,email,ip,count,totscore) VALUES ('%s','%s','%s',%s,%s) ;\n",
               $username,
               $email,
               $ip,
               $count,
               $totscore
               ;
        #printf "% 8.1f %15s  --  %s\n", $totscore/$count, (sprintf "(%.1f/%d)",$totscore/$count, $count), $key;
      }
      else
      {
        print STDERR "WARNING: $key\n";
        print STDERR "       : \$email or \$ip\n";
      }
    }
    else
    {
      print STDERR "WARNING: $key\n";
      print STDERR "       : undefined \$totscore\n";
    }

  }

  untie %h;
}