#!/bin/bash

# read cfg. file
. rsnapshot-backup-stats-eval.cfg

if [ -z "$BACKUPSERVER" ]; then
    echo "BACKUPSERVER unset. Use BACKUPSERVER=... $0"
    echo "valid values for BACKUPSERVER would be some hostname or ip address"
    exit 1;
fi

if [ "$MODE" = "FLATFILE" ]; then

    rsync -vaSHAX root@${BACKUPSERVER}:/var/log/rsnapshot-backup/00-stats/status-????????-?????? .

    rm      status.sqlite
    sqlite3 status.sqlite 'create table status (d int, s int, v varchar(255), bc varchar(255), bset varchar(10));'

    for i in status-* ; do
        d=${i#status-}
        d=${d%-*}
        echo -n $d ""
        awk -v d=$d 'BEGIN{
                print "begin transaction;";
            }
            { print "insert into status(d,s,v) values ("d","$1",\""$2"\");" }
            END {
                print "commit;";
            }
            ' $i |
            sqlite3 status.sqlite
    done
    echo

elif [ "$MODE" = "XML" ]; then
    rsync -vaSHAX root@${BACKUPSERVER}:/var/log/rsnapshot-backup/00-stats/20??-??-??T??:??:??.xml .

    rm      status.sqlite
    sqlite3 status.sqlite 'create table status (d int, s int, v varchar(255), bc varchar(255), bset varchar(10));'

    for i in 20??-??-??T??:??:??.xml ; do
        d=${i%.xml}
        echo -n $d ""
        cnt=$( xmlstarlet sel -t -v "count(/rsnapshot-stats/du)" < $i )
        echo -n $cnt ""
        (  # generate sql
            echo "begin transaction;"
            for j in $( seq 1 $cnt ); do
                sys=$(
                    xmlstarlet sel -t -v "/rsnapshot-stats/du[$j]/@sys" < $i
                )
                #dev# echo -n $j $sys "" 1>&2
                du_data=$(
                    xmlstarlet sel -t -v "/rsnapshot-stats/du[$j]" < $i
                )
                while read s bset ; do
                    if [ -z "$s" ]; then continue ; fi  ## skip empty lines
                    bc=${sys##*/}
                    #dev# echo "BC: $sys -> $bc" 1>&2
                    echo "insert into status(d,s,v,bc,bset) values (strftime('%Y%m%d','$d'),'$s','$sys/$bset','$bc','$bset');"
                done <<< "$du_data"
            done
            echo "commit;"
        ) | tee -a sql | sqlite3 status.sqlite
        #dev# echo -n ": "
    done
    echo

else
    echo "MODE unset. Use MODE=... $0"
    echo "valid values for MODE are: FLATFILE, XML"
    exit 1;
fi

echo "*** DB is loaded ***"

# normalize:
sqlite3 status.sqlite "update status set v = '/u/vhost15/daily.0' where v like '/u/213.178.162.74_vhost15/daily.0';"
sqlite3 status.sqlite "update status set v = '/u/vhost14/daily.0' where v like '/u/213.178.162.72_vhost14/daily.0';"
sqlite3 status.sqlite "update status set v = '/u/vhost13/daily.0' where v like '/u/213.178.162.70_vhost13/daily.0';"
sqlite3 status.sqlite "update status set v = '/u/vhost12/daily.0' where v like '/u/213.178.162.82_vhost12/daily.0';"

sqlite3 status.sqlite "update status set v = '/u/vhost15/daily.1' where v like '/u/213.178.162.74_vhost15/daily.1';"
sqlite3 status.sqlite "update status set v = '/u/vhost14/daily.1' where v like '/u/213.178.162.72_vhost14/daily.1';"
sqlite3 status.sqlite "update status set v = '/u/vhost13/daily.1' where v like '/u/213.178.162.70_vhost13/daily.1';"
sqlite3 status.sqlite "update status set v = '/u/vhost12/daily.1' where v like '/u/213.178.162.82_vhost12/daily.1';"

DAILY0=$(
    sqlite3 status.sqlite "select distinct(v) from status where v like '%daily.0';"
)
echo "DAILY0: $DAILY0"

rm tab.*_D0 img.*_D0.png

for i in $DAILY0 ; do
  h=${i%/*}
  h=${h##*/}_D0
  echo "### $i -> $h ###"
  sqlite3 status.sqlite -separator " " "select d,s from status where v like '$i';" > tab.$h
  gnuplot << ..EOF
  set key below
  set xdata time
  set timefmt "%Y%m%d"
  set format x "%d.%m.\n%Y"
  set terminal png medium size 1200,400
  set output "img.$h.png"
  plot "tab.$h" using 1:2 title "$h"
..EOF

done


DAILY1=$(
    sqlite3 status.sqlite "select distinct(v) from status where v like '%daily.1';"
)
echo "DAILY1: $DAILY1"

rm tab.*_D1 img.*_D1.png

for i in $DAILY1 ; do
  h=${i%/*}
  h=${h##*/}_D1
  echo "### $i -> $h ###"
  sqlite3 status.sqlite -separator " " "select d,s from status where v like '$i';" > tab.$h
  gnuplot << ..EOF
  set key below
  set xdata time
  set timefmt "%Y%m%d"
  set format x "%d.%m.\n%Y"
  set terminal png medium size 1200,400
  set output "img.$h.png"
  plot "tab.$h" using 1:2 title "$h"
..EOF

done

# print hit lists:
echo "### absolute sizes ###"
sqlite3 status.sqlite -separator " " "select max(s)/1E6,'GB',v from status where v like '%daily.0' group by v order by s;" | tail -10
echo "### incremental sizes ###"
sqlite3 status.sqlite -separator " " "select max(s)/1E6,'GB',v from status where v like '%daily.1' group by v order by s;" | tail -10