#!/usr/bin/perl -w
use strict;
$|++;

## start config

my $HEIGHT = 500;
my $WIDTH = 1000;
my $TIMEFRAME = 86400 * 3;

my $DATABASE = "/var/log/pfstats.sqlite";
my $OUTPUT = "/web/htdocs/web-blue-stats2.png";

my $COUNT = int($WIDTH/10);     # controls desired barsize
my $LABELS = int($WIDTH/20);    # function of fontsize
my @PLOT = qw(OTHER
              stonehenge-inbound
              stonehenge-outbound
              webstonehenge
              geekcruises
             );
sub MAPPING {
  local $_ = shift;
  s/^(webstonehenge|geekcruises)(-.*)?/$1/
    or s/^((stonehenge)-(in|out)bound)(-.*)?/$1/
      or s/.*/OTHER/;
  return $_;
};
my @COLORS = qw(pink
                lorange
                orange
                lgreen
                lblue
               );

## end config

require DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=$DATABASE","","",
                       {RaiseError => 1, PrintError => 0})
  or die $DBI::error;

$dbh->func("mapping", 1, \&MAPPING, "create_function");

my $high = time;
my $low = $high - $TIMEFRAME;
my $gap = ($high-$low)/$COUNT;

my @graphdata = [map time_to_label($low + $_ * $gap), 0..($COUNT-1)];
my %graph_col_map = map {$PLOT[$_] => $_ + 1} 0..$#PLOT;

my $sth = $dbh->prepare
  (q{SELECT round((stamp - ?) / ? - 0.5), mapping(type), avg(bytes)
     FROM stats
     WHERE stamp BETWEEN ? AND ?
     GROUP BY 1, 2}
  );
$sth->execute($low, $gap, $low, $high);
while (my ($row, $type, $bytes) = $sth->fetchrow_array) {
  my $graph_col_map = $graph_col_map{$type};
  $graph_col_map = 10 unless defined $graph_col_map;
  ## (bytes) / (300 seconds per sample) = Bytes per Second
  ## so multiply that by seconds per month divided by 1G
  $graphdata[$graph_col_map][$row] = ($bytes / 300) * (86400 * 31 / 1e9);
}

sub time_to_label {
  my $time = shift;
  my @values = localtime($time);
  sprintf "%02d/%02d-%02d", $values[4]+1, $values[3], $values[2];
}

require GD::Graph::bars;
my $g = GD::Graph::bars->new($WIDTH, $HEIGHT) or die;
$g->set(
        x_label => 'month/day-hour (pacific time)',
        x_label_skip => $COUNT/$LABELS,
        x_labels_vertical => 1,
        y_label => 'average gigabytes per month',
        y_long_ticks => 1,
        title => 'blue.stonehenge.com traffic by customer',
        cumulate => 1,
        dclrs => \@COLORS,
       );
$g->set_legend(@PLOT);
my $gd = $g->plot(\@graphdata) or die;

open OUT, ">$OUTPUT.tmp" or die "$OUTPUT.tmp: $!";
print OUT $gd->png;
close OUT;

rename "$OUTPUT.tmp", $OUTPUT or die "rename: $!";
exit 0;