CREATE TABLE stats (
  day date NOT NULL default'',
  username varchar(100) NOT NULL default '',
  domain varchar(100) default '',
  spam int(20) default '0',
  ham int(20) default '0',
  total int(20) default '0',
  PRIMARY KEY (day,username,domain)
) TYPE=MyISAM

# Configure SQL for statistical storage
use_stats                       1
user_stats_dsn                  DBI:mysql:spamassassin_beta:sql_hostname
user_stats_sql_username         sql_username
user_stats_sql_password         sql_password
user_stats_sql_table            sql_table

# Stats Plugin - store stats in a MySQL DB
#
loadplugin Mail::SpamAssassin::Plugin::Stats

=head1 NAME

package Mail::SpamAssassin::Plugin::Stats - Keep Real Time SpamAssassin Stastics 

=head1 SYNOPSIS

  loadplugin Mail::SpamAssassin::Plugin::Stats

  user_stats_dsn                  DBI:mysql:spamassassin_beta:sql_hostname
  user_stats_sql_username         sql_user
  user_stats_sql_password         sql_pass
  user_stats_sql_table            stats

=head1 DESCRIPTION

This SpamAssassin plugin records real-time, user-level statistics.  The statistics are stored inside of a MySQL database and are rotated daily.  Each entry inside the table contains the current date, user, domain, number of hams (legitimate mail), number of spams (unsolicited mail) and total number of messages for that individual.  There is also a total field which also rotates daily.  It contains the total number of hams, spams, and messages that the system has seen for that day.  This entry is notated with the $TOTALS username and $TOTALS domain name.

=cut
package Mail::SpamAssassin::Plugin::Stats;

use strict;
use warnings;
use bytes;
use Mail::SpamAssassin;
use Mail::SpamAssassin::Logger;

use vars qw(@ISA);
@ISA = qw(Mail::SpamAssassin::Plugin);


sub new {

  my ($class, $mailsa) = @_;
  $class = ref($class) || $class;
  my $self = $class->SUPER::new($mailsa);
  bless ($self, $class);

  $self->set_config($mailsa->{conf});

  $self;
}



sub set_config {

  my ($self, $conf) = @_;
  my @cmds = ();

  push (@cmds, {
        setting => 'use_stats',
        default => 1,
        type => $Mail::SpamAssassin::Conf::CONF_TYPE_NUMERIC,
  });

  push (@cmds, {
        setting => 'user_stats_dsn',
        type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING,
  });

  push (@cmds, {
        setting => 'user_stats_sql_username',
        type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING,
  });

  push (@cmds, {
        setting => 'user_stats_sql_password',
        type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING,
  });

  push (@cmds, {
        setting => 'user_stats_sql_table',
        type => $Mail::SpamAssassin::Conf::CONF_TYPE_STRING,
  });

  $conf->{parser}->register_commands(\@cmds);
}




sub check_end {
  my ($self, $params) = @_;
  my $pms = $params->{permsgstatus};

  return 0 unless ($pms->{conf}->{use_stats});

  dbg("stats: Executing stats-plugin");

  my $dsn = $self->{main}->{conf}->{user_stats_dsn};
  if (!defined($dsn)) {
        dbg("stats: no DSN specified; HALT!");
        return 1;
  }

  require DBI;

  my $main = $self->{main};
  my $dbuser = $main->{conf}->{user_stats_sql_username};
  my $dbpass = $main->{conf}->{user_stats_sql_password};
  my $table = $main->{conf}->{user_stats_sql_table};

  my $f_spam = 'spam';
  my $f_ham = 'ham';
  my $f_total = 'total';
  my $f_username = 'username';
  my $f_domain = 'domain';
  my $f_day = 'day';
  my $isspam;
  my $user;
  my $domain;
  my $tot_user = '$TOTALS';
  my $tot_domain = '$TOTALS';

  my $username = $self->{main}->{username};
  $username = lc($username);
  my $score = $pms->{score};
  my $required_score = $main->{conf}->{required_score};

  dbg("stats: Splitting $username based on @");
  ($user,$domain) =  split /@/,$username;
  if (!defined($domain)) {
        $domain = '';
  }

  dbg("stats: User: $user Domain: $domain");
  dbg("stats: Message Score: $score out of $required_score");

  if ($score >= $required_score ) {
        $isspam = 1;
  }
  else {
        $isspam = 0;
  }

  dbg("stats: IsSpam is $isspam");

  my $dbh = DBI->connect($dsn, $dbuser, $dbpass, {'PrintError' => 0});
  my $user_exists = &get_current_entry($user, $domain, $dbh, $table, $f_username, $f_domain, $f_day);
  my $total_exists = &get_current_entry($tot_user, $tot_domain, $dbh, $table, $f_username, $f_domain, $f_day);

  if ($dbh) {
        &execute_stats($user, $domain, $dbh, $table, $f_spam, $f_ham, $f_total, $f_username, $f_domain, $f_day, $isspam, $user_exists);
        &execute_stats($tot_user, $tot_domain, $dbh, $table, $f_spam, $f_ham, $f_total, $f_username, $f_domain, $f_day, $isspam, $total_exists);
        $dbh->disconnect();
  }
  else {
        die "stats: SQL error: " . DBI->errstr . "\n";
  }
0;
}




sub get_current_entry {
  my ($user, $domain, $dbh, $table, $f_username, $f_domain, $f_day) = @_;
  my $sql = "SELECT $f_username from $table where $f_username = '$user' and $f_domain = '$domain' and $f_day = curdate()";
  dbg("stats: Executing $sql");
  my $sth = $dbh->prepare($sql);
        if ($sth) {
                my $rv = $sth->execute();
                dbg("stats: rv contains $rv");
                if ($rv eq "0E0") {
                        dbg("stats: Entry does not exist for $user\@$domain");
                        return 0;
                }
                else {
                        dbg("stats: Entry already exists for $user\@$domain");
                        return 1;
                }
        }
        else {
                die "stats: SQL error: " . $dbh->errstr . "\n";
        }
}




sub execute_stats {
  my ($user, $domain, $dbh, $table, $f_spam, $f_ham, $f_total, $f_username, $f_domain, $f_day, $isspam, $exists) = @_;
  my $sql;

  if ($exists) {
        $sql = "UPDATE $table SET $f_total = $f_total + 1 WHERE $f_username = '$user' and $f_domain = '$domain' and $f_day = curdate()";
  }
  else {
        $sql = "INSERT into $table ($f_day,$f_username,$f_domain,$f_spam,$f_ham,$f_total) VALUES (curdate(),'$user','$domain',0,0,1)";
  }

  dbg("stats: config: SQL executing $sql");
  my $sth = $dbh->prepare($sql);

  if ($sth) {
        my $rv = $sth->execute();

        if ($rv) {
                dbg("stats: Stats change for $user\@$domain");
                if ($isspam) {
                        $sql = "UPDATE $table SET $f_spam = $f_spam + 1 WHERE $f_username = '$user' and $f_domain = '$domain' and $f_day = curdate()";
                }
                else {
                        $sql = "UPDATE $table SET $f_ham = $f_ham + 1 WHERE $f_username = '$user' and $f_domain = '$domain' and $f_day = curdate()";
                }
                $sth = $dbh->prepare($sql);
                if ($sth) {
                        $rv = $sth->execute();
                        if ($rv) {
                                dbg("stats: Updated Spam Type $sql")
                        }
                        else {
                                die "stats: SQL error $sql\n".$sth->errstr."\n";
                        }
                }
                else {
                        die "stats: SQL error $sql\n".$sth->errstr."\n";
                }
        }
        else {
                die "stats: SQL error $sql\n".$sth->errstr."\n";
        }
  }
  else {
        die "stats: SQL error: " . $dbh->errstr . "\n";
  }

}


1;