#!/bin/bash
#
# Copyright (C) 2015 Ana Emilia Machado de Arruda <emiliaarruda at gmail dot com>
#                     Borja Chocarro del Olmo
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
# Description:
#
# A script to list volumes from a Pool that could be used by Bacula for backup
# acording to Bacula rules for volume use and recycling.
#
# This script does not contemplate the following:
#   - how many volumes will be used for a backup job;,
#   - if PurgeOldestVolume or RecycleOldestVolume directives are set;
#   - if automatic or manual prune or purge are configured or intended to be used
#   - if volumes are InChanger or not, just for the case of VolStatus=Append
#
# This script intends to helps on situations as the described above:
#
#   - 54 volumes(tapes) are used for a one year backup of a company.
#   - autochanger has 15 slots available for the backups.
#   - the company has rules that states that tapes that are not in use during
#     the week must be keep in a tape vault storage.
#
# The script could help listing the number of volumes that is suitable to the
# autochanger and acording to the company rules. For example, the following
# commands will list the 5 volumes from File Pool that Bacula could use during
# one week (monday to friday) starting at 2015-10-21 22:00:00, considering
# the use of just one volume for a day of backup:
#
# list_available_volumes --pool=File --vols=5 --date="2015-10-21 22:00:00"
#
# For more information type list_available_volumes --help

declare -r cmdbasename=`basename $0`

showhelp(){
cat << EOF

Usage: $cmdbasename [OPTIONS]
List volumes from a Pool that could be used by Bacula for backup acording to Bacula rules for volume use and recycling.

OPTIONS:

    --pool=POOLNAME           Required. The Pool name for which volumes will be listed.
    --vols=VOLUMESRETRIEVED   Optional. Number of volumes that will be retrieved.
                              If omitted all the volumes from the Pool will be listed.
    --date=EXPECTEDVOLUSE     Optional. Date estimated for the volumes to be used.
                              If omitted current date/time will be considered.
    --host=DBHOST               Optional. The host address of the database server (catalog).
                              If omitted 'locahost' will be used.
    --database=DATABASE       Optional. The name of the Catalog's database.
                              If omitted 'bacula' will be used.
    --user=USER               Optional. The name of the user to use to log into
                              Catalog's database. If omitted 'bacula' will be used.
    --password=PASSWORD       Optional. The password of the user to use to log
                              into Catalog's database. If omitted no password will be used.
    --help                    Display this help and exit.

Examples:

    $cmdbasename --pool=Default --user=bacula
    $cmdbasename --pool=File --vols=5 --user=bacula --date="2015-10-21 22:00:00"

EOF
}

# variables
: ${NUMBEROFVOLUMES:=0}
: ${EXPECTEDVOLUSE:=$(date --utc +"%s")}
: ${DBHOST:=localhost}
: ${DATABASE:=bacula}
AVAILABLEVOLUMES=()
NUMBEROFAVAILABLEVOLUMES=0
# number of columns used in the SELECT for purposes the array dimension
COLS=13

arguments=("$@")
for arg; do
  case "$arg" in
     --help)   showhelp;
               exit 0;;
     --pool=*) POOLNAME=${arg#*=};;
     --vols=*) NUMBEROFVOLUMES=${arg#*=};;
     --date=*) EXPECTEDVOLUSE=$(date --utc --date="${arg#*=}" +"%s");;
     --host=*) DBHOST=${arg#*=};;
     --database=*) DATABASE=${arg#*=};;
     --user=*) USER=${arg#*=};;
     --password=*) PASSWORD=${arg#*=};;
     *) printf "%s: unrecognized option \'%s\'" $cmdbasename ${arg%%=*};
        showhelp;
        exit 1;;
  esac
done

if [ -z "$POOLNAME" ]; then
  printf "%s: missing --pool=POOLNAME option\n" $cmdbasename
  showhelp
  exit 1
fi

# Fields returned by MySQL SELECT
# ${VOLUMES[COLS]}                              : MediaId
# ${VOLUMES[COLS+1]}                            : VolumeName
# ${VOLUMES[COLS+2]}                            : VolStatus
# ${VOLUMES[COLS+3]} + ${VOLUMES[COLS+4]}       : FirstWritten
# ${VOLUMES[COLS+5]}                            : Unix Timestamp for FirstWritten
# ${VOLUMES[COLS+6]} + ${VOLUMES[COLS+7]}       : LastWritten
# ${VOLUMES[COLS+8]}                            : Unix Timestamp for LastWritten
# ${VOLUMES[COLS+9]}                            : VolUseDuration
# ${VOLUMES[COLS+10]}                           : VolRetention
# ${VOLUMES[COLS+11]}                           : Recycle
# ${VOLUMES[COLS+12]}                           : InChanger
VOLUMES=($(mysql --host=$DBHOST --database=$DATABASE --user=$USER --password=$PASSWORD -N -e "SELECT MediaId, VolumeName, VolStatus, FirstWritten, UNIX_TIMESTAMP(CONVERT_TZ(FirstWritten, '+00:00', @@session.time_zone)), LastWritten, UNIX_TIMESTAMP(CONVERT_TZ(LastWritten, '+00:00', @@session.time_zone)), Media.VolUseDuration, Media.VolRetention, Media.Recycle, Media.InChanger FROM Media,Pool WHERE (Pool.Name LIKE \"$POOLNAME\" AND Media.PoolId=Pool.PoolId AND Media.Enabled=\"1\")"))

# if SELECT returns nothing, tells it and exit.
if [ ${#VOLUMES[*]} -eq 0 ]; then
  printf 'There is no volume that matches the specified parameters\n'
  exit 1
else
  VOLUMESRETRIEVED=$((${#VOLUMES[*]}/COLS))
fi

# if the number of volumes expected to be retrieved is zero,
# the script will consider the total volumes in the pool.
if [ $NUMBEROFVOLUMES -eq 0 ]; then
  NUMBEROFVOLUMES=$VOLUMESRETRIEVED
fi

function SortByMediaIdAsc () {
  for ((i=$2-1; i>=$1; i--)) do
    for ((j=$1+1; j<=i; j++)) do
      if [ ${AVAILABLEVOLUMES[(j-1)*COLS]} -gt ${AVAILABLEVOLUMES[j*COLS]} ]; then
        for ((k=0; k<COLS; k++)) do
          TEMP=${AVAILABLEVOLUMES[(j-1)*COLS+k]}
          AVAILABLEVOLUMES[(j-1)*COLS+k]=${AVAILABLEVOLUMES[j*COLS+k]}
          AVAILABLEVOLUMES[j*COLS+k]=$TEMP
        done
      fi
    done
  done
}

function SortByElapsedTimeSinceLastWrittenDesc {
  for ((i=$2-1; i>=$1; i--)) do
    for ((j=$1+1; j<=i; j++)) do
      if [ ${AVAILABLEVOLUMES[(j-1)*COLS+8]} -gt ${AVAILABLEVOLUMES[j*COLS+8]} ]; then
        for ((k=0; k<COLS; k++)) do
          TEMP=${AVAILABLEVOLUMES[(j-1)*COLS+k]}
          AVAILABLEVOLUMES[(j-1)*COLS+k]=${AVAILABLEVOLUMES[j*COLS+k]}
          AVAILABLEVOLUMES[j*COLS+k]=$TEMP
        done
      elif [ ${AVAILABLEVOLUMES[(j-1)*COLS+8]} -eq ${AVAILABLEVOLUMES[j*COLS+8]} ]; then
        if [ ${AVAILABLEVOLUMES[(j-1)*COLS]} -lt ${AVAILABLEVOLUMES[j*COLS]} ]; then
          for ((k=0; k<COLS; k++)) do
            TEMP=${AVAILABLEVOLUMES[(j-1)*COLS+k]}
            AVAILABLEVOLUMES[(j-1)*COLS+k]=${AVAILABLEVOLUMES[j*COLS+k]}
            AVAILABLEVOLUMES[j*COLS+k]=$TEMP
          done
        fi
      fi
    done
  done
}


for ((i=0; i<VOLUMESRETRIEVED; i++)) do
# set VolStatus=Used for the volumes that match VolStatus=Append and FirstWritten is not NULL if
# VolUseDuration is not zero and is reached on EXPECTEDVOLUSE datetime
  if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} != 0000-00-00 ]; then
    if [ ${VOLUMES[((i*COLS+9))]} != 0 ] && [ $((${VOLUMES[((i*COLS+5))]}+${VOLUMES[((i*COLS+9))]})) -lt $EXPECTEDVOLUSE ] ; then
      VOLUMES[((i*COLS+2))]="Used";
    fi
  fi
# if VolStatus=Used OR VolStatus=Full AND Recycle=yes
# if LastWritten+Retention < EXPECTEDVOLUSE then set VolStatus=Purged
  if [ ${VOLUMES[((i*COLS+2))]} = Used ] || [ ${VOLUMES[((i*COLS+2))]} = Full ] && [ ${VOLUMES[((i*COLS+11))]} = 1 ]; then
    if [ $((${VOLUMES[((i*COLS+8))]}+${VOLUMES[((i*COLS+10))]})) -lt $EXPECTEDVOLUSE ]; then
      VOLUMES[((i*COLS+2))]="Purged";
    fi
  fi
done

# find all volumes for Append (already written) and InChanger and order them by LastWritten descendent order
STARTFROM=0
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
  if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} != 0000-00-00 ] && [ ${VOLUMES[((i*COLS+12))]} = 1 ]; then
    for ((j=0; j<COLS; j++)) do
      AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
    done
    ((NUMBEROFAVAILABLEVOLUMES++))
  fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all volumes for Append, never written and InChanger and order them by MediaId
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
  if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} = 0000-00-00 ] && [ ${VOLUMES[((i*COLS+12))]} = 1 ]; then
    for ((j=0; j<COLS; j++)) do
      AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
    done
    ((NUMBEROFAVAILABLEVOLUMES++))
  fi
done
SortByMediaIdAsc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all volumes for Append, never written and not InChanger and order them by MediaId
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
  if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} = 0000-00-00 ] && [ ${VOLUMES[((i*COLS+12))]} != 1 ]; then
    for ((j=0; j<COLS; j++)) do
      AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
    done
    ((NUMBEROFAVAILABLEVOLUMES++))
  fi
done
SortByMediaIdAsc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all volumes for Append (already written) and not InChanger and order them by LastWritten descendent order
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
  if [ ${VOLUMES[((i*COLS+2))]} = Append ] && [ ${VOLUMES[((i*COLS+3))]} != 0000-00-00 ] && [ ${VOLUMES[((i*COLS+12))]} != 1 ]; then
    for ((j=0; j<COLS; j++)) do
      AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
    done
    ((NUMBEROFAVAILABLEVOLUMES++))
  fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all volumes with VolStatus=Recycle and order them by LastWritten descendent order
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
  if [ ${VOLUMES[((i*COLS+2))]} = Recycle ]; then
    for ((j=0; j<COLS; j++)) do
      AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
    done
    ((NUMBEROFAVAILABLEVOLUMES++))
  fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all purged volumes and order them by LastWritten descendent order
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
  if [ ${VOLUMES[((i*COLS+2))]} = Purged ] && [ ${VOLUMES[((i*COLS+11))]} = 1 ]; then
    for ((j=0; j<COLS; j++)) do
      AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
    done
    ((NUMBEROFAVAILABLEVOLUMES++))
  fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# find all Used and Full volumes and order them by LastWritten descendent order
STARTFROM=$NUMBEROFAVAILABLEVOLUMES
for ((i=0; i<VOLUMESRETRIEVED; i++)) do
  if ([ ${VOLUMES[((i*COLS+2))]} = Used ] || [ ${VOLUMES[((i*COLS+2))]} = Full ]) && [ ${VOLUMES[((i*COLS+11))]} = 1 ]; then
    for ((j=0; j<COLS; j++)) do
      AVAILABLEVOLUMES[((NUMBEROFAVAILABLEVOLUMES*COLS+j))]=${VOLUMES[((i*COLS+j))]}
    done
    ((NUMBEROFAVAILABLEVOLUMES++))
  fi
done
SortByElapsedTimeSinceLastWrittenDesc $STARTFROM $NUMBEROFAVAILABLEVOLUMES

# Lists available volumes
printf 'MediaId\tVolumeName\tVolStatus\tLastWritten\t\tVolRetention\tInChanger\n'
for ((i=0; i<NUMBEROFVOLUMES; i++)) do
  printf '%s\t%s\t%s\t\t%s %s\t%s\t\t%s\n'  ${AVAILABLEVOLUMES[((i*COLS))]} ${AVAILABLEVOLUMES[((i*COLS+1))]} ${AVAILABLEVOLUMES[((i*COLS+2))]} ${AVAILABLEVOLUMES[((i*COLS+6))]} ${AVAILABLEVOLUMES[((i*COLS+7))]} ${AVAILABLEVOLUMES[((i*COLS+10))]} ${AVAILABLEVOLUMES[((i*COLS+12))]}
done

exit $?