#!/bin/sh

dbfile=/var/cache/stats.s3db
iface=eth0

time=$(date +%s)
rx=$(awk "/$iface/{print int(\$2/125)}" /proc/net/dev)
tx=$(awk "/$iface/{print int(\$10/125)}" /proc/net/dev)

query=

if ! [ -f $dbfile ];
then
sqlite3 $dbfile <<EOF
CREATE TABLE network(rx INTEGER, tx INTEGER);
CREATE TABLE cpu(usage INTEGER);

CREATE TABLE network24(rx INTEGER, tx INTEGER);
CREATE TABLE cpu24(usage INTEGER);

CREATE TABLE lastnet(rx INTEGER, tx INTEGER, time INTEGER);
CREATE TABLE lastcpu(usage INTEGER);

INSERT INTO lastnet(rx, tx, time) VALUES($rx, $tx, $time);
EOF

exit
fi

lastnet=$(sqlite3 $dbfile 'SELECT * FROM lastnet;')

orx=$(echo $lastnet |cut '-d|' -f1)
otx=$(echo $lastnet |cut '-d|' -f2)
otime=$(echo $lastnet |cut '-d|' -f3)

drx=$((rx - orx))
dtx=$((tx - otx))
dtime=$((time - otime))

# add (2^32 * 8 / 1000) for overlap
[ $drx -lt 0 ] && drx=$((drx + 34359738))
[ $dtx -lt 0 ] && dtx=$((dtx + 34359738))

drx=$((drx / dtime))
dtx=$((dtx / dtime))

[ $(date +%d) -eq 1 ] && query="$query;DELETE FROM network;"

[ $(date +%k) -eq 0 ] && query="$query;INSERT INTO network(rx, tx) SELECT avg(rx), avg(tx) FROM network24; DELETE FROM network24;"

query="$query;INSERT INTO network24(rx, tx) VALUES($drx, $dtx);"

query="$query;UPDATE lastnet SET rx=$rx, tx=$tx, time=$time;"

echo "$query" >>/tmp/stat.sql

sqlite3 $dbfile "$query"