#!/usr/bin/perl

use strict;
use DBI;

my $dsn = "DBI:mysql:database=$ENV{DBNAME};host=$ENV{DBHOST}";
my $dbh = DBI->connect ($dsn, $ENV{DBUSER}, $ENV{DBPASS});
die DBI->errstr if !$dbh->do ("show tables");

for ("create table traffic (traffic_ip char(15) primary key)",
     "alter table traffic add traffic_in_current bigint unsigned not null default 0",
     "alter table traffic add traffic_out_current bigint unsigned not null default 0",
     "alter table traffic add traffic_in_last bigint unsigned not null default 0",
     "alter table traffic add traffic_out_last bigint unsigned not null default 0",
     "alter table traffic add traffic_in_archive bigint unsigned not null default 0",
     "alter table traffic add traffic_out_archive bigint unsigned not null default 0",
     "create table traffic_history (traffic_history_ip char(15))",
     "alter table traffic_history add traffic_history_date date",
     "alter table traffic_history add traffic_history_month int",
     "alter table traffic_history add key traffic_history_ip (traffic_history_ip)",
     "alter table traffic_history add key traffic_history_date (traffic_history_date)",
     "alter table traffic_history add key traffic_history_month (traffic_history_month)",
     "alter table traffic_history add unique traffic_history_ip_date (traffic_history_ip, traffic_history_date)",
     "alter table traffic_history add key traffic_history_ip_month (traffic_history_ip, traffic_history_month)",
     "alter table traffic_history add traffic_history_in bigint unsigned not null default 0",
     "alter table traffic_history add traffic_history_out bigint unsigned not null default 0",
     "alter table traffic_history add traffic_history_total bigint unsigned not null default 0")
{
    local $dbh->{PrintError} = 0;
    $dbh->do ($_);
}

my $ipreg = "[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+";

my $lastip;
my %in;
my %out;
while(<>)
{
    my ($ruleno, $packets, $bytes, $ruletext) = split (/\s+/, $_, 4);
    if ($ruletext =~ /^count ip from any to ($ipreg)/) {
	$in{$1} = $bytes;
    }
    elsif ($ruletext =~ /^count ip from ($ipreg) to any/) {
	$out{$1} = $bytes;
    }
    elsif ($ruletext =~ /^count ip from any to any in/) {
	$in{"0.0.0.0"} = $bytes;
    }
    elsif ($ruletext =~ /^count ip from any to any out/) {
	$out{"0.0.0.0"} = $bytes;
    }
}

my $insert = $dbh->prepare ("insert ignore into traffic (traffic_ip) values (?)");
my $update = $dbh->prepare ("update traffic set traffic_in_current=?, traffic_out_current=? where traffic_ip=?");

for my $ip (sort keys %in)
{
    print STDERR "." if -t STDERR;
    if (defined $in{$ip} && defined $out{$ip})
    {
	$insert->execute ($ip);
	$update->execute ($in{$ip}, $out{$ip}, $ip);
    }
}
print STDERR "+" if -t STDERR;
$dbh->do ("update traffic set
 traffic_in_last=traffic_in_current,
 traffic_out_last=traffic_out_current
 where traffic_in_last <= traffic_in_current
  and traffic_out_last <= traffic_out_current");

print STDERR "+" if -t STDERR;
$dbh->do ("update traffic set
 traffic_in_archive = traffic_in_archive + traffic_in_last,
 traffic_out_archive = traffic_out_archive + traffic_out_last,
 traffic_in_last = traffic_in_current,
 traffic_out_last = traffic_out_current
 where traffic_in_last > traffic_in_current
   or traffic_out_last > traffic_out_current");

print STDERR "+" if -t STDERR;
$dbh->do ("replace into traffic_history
 (traffic_history_ip, traffic_history_date, traffic_history_month, traffic_history_in, traffic_history_out, traffic_history_total)
 select
   traffic_ip, now(), extract(year_month from now()),
   traffic_in_archive + traffic_in_current, traffic_out_archive + traffic_out_current,
   traffic_in_archive + traffic_in_current + traffic_out_archive + traffic_out_current
 from traffic");

print STDERR "\n" if -t STDERR;