#!/bin/bash
#set -o nounset
TRG="/root/backup"

# Set minimum Gromox-Release
G='1.31-49-g32e8f22fb'
V=$(rpm -q --queryformat='%{VERSION}\n' gromox)
printf '%s\n' "$G" "$V" |sort -C -V || { echo >&2 "Error: Gromox not compatible. Minimal version: $G"; exit 1; }

grom_domains() { grommunio-admin domain list |awk '{print $2}' ; }

grom_users() {
  local domains
  if [[ "$#" -ge 1 ]]; then
    domains=$(grep "^${1}" <<< "$(grom_domains)")
  else
    domains=$(grom_domains)
  fi
  for dom in $domains; do 
    domID=$(grommunio-admin domain query -f domainname="${dom}" ID)
    # remove distribution lists (i.e. users without maildir)
    grommunio-admin user query -s username -f domainID="$domID" username maildir | awk '$2 != "" && NR>2 {print $1}'
  done
}

_grom_query_maildir(){
  grommunio-admin user query --filter username="${1}" maildir
}

_grom_query_domainname(){
  grommunio-admin domain query --filter ID="$(grommunio-admin user query --filter username="${1}" domainID)" domainname
}

_grom_cleanup() {
  local maildir

# define function
_grom_users="${_grom_users:=$(grom_users)}"

# Call without parameters, apply default values
if [[ "$#" -eq 0 ]]; then
  MESSAGE="\n\tTo really cleanup use: _grom_cleanup clean\n"
  for user in $_grom_users; do
    _grom_cleanup "${user}" "${SWITCH}"
  done
  echo -e "$MESSAGE"
fi

# Call with user and clean-switch
if [[ "$#" -eq 2 ]]; then
  if [[ "$_grom_users" =~ $1 ]]; then
    user="$1"
    maildir="$(_grom_query_maildir "$user")"

    if [[ "$2" = "clean" ]]; then
      MESSAGE="";
      (
      printf '%s: Cleaning mail directory...\n' "${user}"
      /usr/libexec/gromox/cleaner -d "${maildir}" | grep -e "^" -e "[^ ]*[0-9](M|G)B"
      printf '%s: Vacuuming database...\n' "${user}"
      /usr/sbin/gromox-mbop vacuum "${maildir}"
      ) | logger -t grom_cleanup
    else
      printf '%s: Cleaning mail directory...\n' "${user}"
      /usr/libexec/gromox/cleaner -n -d "${maildir}" | grep -e "^" -e "[^ ]*[0-9](M|G)B"
    fi
  fi
fi
}

grom_cleanup() {
  local maildir
  local usage="Usage: $0 domain [clean]"
  MESSAGE="\n\tTo really cleanup use: grom_cleanup domain clean\n"

  # Retrieve domains globally
  _grom_domains="${_grom_domains:=$(grom_domains)}"

  # Verify if mandatory arguments are set
  if [[ "$#" -eq 0 ]]; then
    echo $"Usage: $0 DOMAIN (clean)"
    exit 1
  else
    # Verify domain
    if ! [[ "$_grom_domains" =~ $1 || "$1" = "all" ]]; then
      echo "Error: Domain '$1' does not exist." >&2; echo $"$usage"; exit 1
    fi
    domain=$1

    # Define _grom_users globally depending on domain parameter
    if [[ "$domain" = "all" ]]; then
      _grom_users="${_grom_users:=$(grom_users)}"
    else
      _grom_users="${_grom_users:=$(grom_users "$domain")}"
    fi

    if [[ "$2" = "clean" ]]; then MESSAGE=""; fi

    # Loop over users
    for user in $_grom_users; do
      _grom_cleanup "${user}" "$2"
    done
    echo -e "$MESSAGE"
  fi
}

_grom_query_purge() {
  local user fields junk_only retention_days 
  #  local mdir sql sel 

  if [[ "$#" -eq 4 ]]; then
    if [[ "$_grom_users" =~ $1 ]]; then
      user="$1"
      mdir="$(_grom_query_maildir "${user}")/exmdb/exchange.sqlite3"
    fi
    if [[ "$2" = "count" ]]; then fields="count(m.message_id)"; else fields="f.folder_id||'|'||m.message_id"; fi
    if [[ "$3" = "0" ]]; then junk_only=0; else junk_only=1; fi
    retention_days=$4

    # basic query
    sqlite3 "${mdir}" << EOSQL
    select ${fields}
--select m.message_id, mp2.propval as message_type, datetime(mp1.propval/10000000-11644473600,'unixepoch') as message_delivery_time, f.propval as folder_type, f2.propval as folder_name
from messages m
-- restrict to email folders (container class=>907214879 must be IPF.Note)
inner join folder_properties f on f.folder_id = m.parent_fid and f.proptag = 907214879 and f.propval = 'IPF.Note'
-- show folder name (join not required for deletion)
--inner join folder_properties f2 on f2.folder_id = m.parent_fid and f2.proptag = 805371935
-- restrict to messages with message_delivery_time=>235274304 older than x days (convert to unix epoch before compare)
inner join message_properties mp1 on mp1.message_id = m.message_id and mp1.proptag = 235274304 and (mp1.propval/10000000-11644473600) < unixepoch('now','-${retention_days} days')
-- show message type (join not required for deletion)
--inner join message_properties mp2 on mp2.message_id = m.message_id and mp2.proptag = 1703967
where 1=1
-- exclude messages in the folder 'Drafts'
and m.parent_fid != 14
-- restrict to messages in the folders 'Deleted Items' or 'Junk Email'
and (${junk_only}} = 0 or m.parent_fid in (11,23))
order by m.message_id asc;

EOSQL

#    # replace variables with values
#    # Junk folders only
#    sel=${sql/\#\#fields\#\#/$fields}
#    sel=${sel/\#\#junk_only\#\#/$junk_only}
#    sel=${sel/\#\#retention_days\#\#/$retention_days}
#    #echo "SQL=$sel"
#    printf '%s' "$(sqlite3 "${mdir}" "${sel}")"

  fi

}

_grom_query_purge_count() {
  local junk_only all_retention_days junk_retention_days
  local user retention_days

  ## Set default values
  # Purge folders 'Deleted Items' or 'Junk Email' after 30 days
  junk_retention_days=30
  # Purge all folders after 180 days
  all_retention_days=180

  # define function
  _grom_users="${_grom_users:=$(grom_users)}"

  if [[ "$#" -eq 0 ]]; then
    for user in $_grom_users; do
      # Junk folders only
      junk_only=1
      printf '%s;%s junk\n' "$user" "$(_grom_query_purge_count "${user}" "${junk_only}" "${junk_retention_days}")"

      # All folders
      junk_only=0
      printf '%s;%s old\n' "$user" "$(_grom_query_purge_count "${user}" "${junk_only}" "${all_retention_days}")"

    done
  fi

  if [[ "$#" -eq 3 ]]; then
    if [[ "$_grom_users" =~ $1 ]]; then 
      user="$1"
      if [[ "$2" = "0" ]]; then junk_only=0; else junk_only=1; fi
      retention_days=$3
      _grom_query_purge "${user}" "count" "${junk_only}" "${retention_days}"
    fi
  fi

}

_grom_purge_messages() {
  local junk_only all_retention_days junk_retention_days
  local user obj msgid fid retention_days msg_type
  local cnt=0

  ## Set default values
  # Purge folders 'Deleted Items' or 'Junk Email' after 30 days
  junk_retention_days=30
  # Purge all folders after 180 days
  all_retention_days=180

  # define function
  _grom_users="${_grom_users:=$(grom_users)}"

  # Call without parameters, apply default values
  if [[ "$#" -eq 0 ]]; then
    for user in $_grom_users; do
      # Junk folders only
      junk_only=1
      _grom_purge_messages "${user}" "${junk_only}" "${junk_retention_days}"

      # All folders
      junk_only=0
      _grom_purge_messages "${user}" "${junk_only}" "${all_retention_days}"
    done
  fi

  # Call with user, junk-flag and retention_days
  if [[ "$#" -eq 3 ]]; then
    if [[ "$_grom_users" =~ $1 ]]; then
      user="$1"
      if [[ "$2" = "0" ]]; then junk_only=0; msg_type="old"; else junk_only=1; msg_type="junk"; fi
      retention_days=$3

      # log message count
      cnt=$(_grom_query_purge "${user}" "count" "${junk_only}" "${retention_days}")
      logger -t grom_purge_messages \
        "$user: $cnt ${msg_type} messages to remove"

      # retrieve message ids
      for obj in $(_grom_query_purge "${user}" "data" "${junk_only}" "${retention_days}"); do
        # split obj into folder-id and message-id
        IFS=\| read -r fid msgid <<< "$obj"

        # delete the message
        (
        printf '%s: %s/%s\n' "${user}" "${fid}" "${msgid}"
        /usr/libexec/gromox/delmsg -u "${user}" -f "${fid}" "${msgid}"
        ) | logger -t grom_purge_messages

      done
      echo "$cnt"
    fi
  fi
}

grom_query_purge_count() {
  local domain user junk_only retention_days
  local usage="Usage: $0 domain (junk|all) retention_days"

  # Retrieve domains globally
  _grom_domains="${_grom_domains:=$(grom_domains)}"

  # Verify if mandatory arguments are set
  if [[ "$#" -ne 3 ]]; then
    echo $"$usage"
    exit 1
  else
    # Verify domain
    if ! [[ "$_grom_domains" =~ $1 || "$1" = "all" ]]; then
      echo "Error: Domain '$1' does not exist." >&2; echo $"$usage"; exit 1
    fi
    domain=$1

    # Define _grom_users globally depending on domain parameter
    if [[ "$domain" = "all" ]]; then
      _grom_users="${_grom_users:=$(grom_users)}"
    else
      _grom_users="${_grom_users:=$(grom_users "$domain")}"

    fi      

    # Verify junk-flag
    case "$2" in
      "junk") junk_only=1;;
      "all")  junk_only=0;;
      *) echo "Error: Message type '$2' is not allowed." >&2; echo $"$usage"; exit 1
    esac

    # Verify if retention days is an integer
    retention_days=$3
    re='^[0-9]+$'
    if ! [[ $retention_days =~ $re ]] ; then
      echo "Error: Retention days '$3' is not an integer." >&2; echo $"$usage"; exit 1
    fi

    # Loop over users
    for user in $_grom_users; do
      printf '%s;%s\n' "$user" "$(_grom_query_purge_count "${user}" "${junk_only}" "${retention_days}")"
    done
  fi
}

grom_purge_messages() {
  local domain user junk_only retention_days
  local usage="Usage: $0 domain (junk|all) retention_days"
  local cnt=0

  # Retrieve domains globally
  _grom_domains="${_grom_domains:=$(grom_domains)}"

  # Verify if mandatory arguments are set
  if [[ "$#" -ne 3 ]]; then
    echo $"$usage"; exit 1
  else
    # Verify domain
    if ! [[ "$_grom_domains" =~ $1 || "$1" = "all" ]]; then
      echo "Error: Domain '$1' does not exist." >&2; echo $"$usage"; exit 1
    fi
    domain=$1

    # Define _grom_users globally depending on domain parameter
    if [[ "$domain" = "all" ]]; then
      _grom_users="${_grom_users:=$(grom_users)}"
    else
      _grom_users="${_grom_users:=$(grom_users "$domain")}"
    fi

    # Verify junk-flag
    case "$2" in
      "junk") junk_only=1;;
      "all")  junk_only=0;;
      *) echo "Error: Message type '$2' is not allowed." >&2; echo $"$usage"; exit 1
    esac

    # Verify if retention days is an integer
    retention_days=$3
    re='^[0-9]+$'
    if ! [[ $retention_days =~ $re ]] ; then
      echo "Error: Retention days '$3' is not an integer." >&2; echo $"$usage"; exit 1
    fi

    # Loop over users
    for user in $_grom_users; do
      cnt=$(_grom_purge_messages "${user}" "${junk_only}" "${retention_days}")

      # If necessary clean the maildir
      if [[ $cnt -gt 0 ]]; then
        _grom_cleanup "${user}" "clean"
      fi
    done
  fi
}

_grom_query_backup() {
  local user fields message_type
  #  local mdir sql sel

  if [[ "$#" -eq 3 ]]; then
    if [[ "$_grom_users" =~ $1 ]]; then
      user="$1"
      mdir="$(_grom_query_maildir "${user}")/exmdb/exchange.sqlite3"
    fi
    if [[ "$2" = "count" ]]; then
      fields="count(m.message_id)"
    else
      fields="mp1.propval||'|'||replace(replace(substr(f.propval,5)||'s','Appointments','Calendar'),'Journals','Journal')||'|'||m.message_id"
    fi
    message_type="$3"

    # basic query
    sqlite3 "${mdir}" << EOSQL
select ${fields}
from messages m
inner join folder_properties f on f.folder_id = m.parent_fid and f.proptag = 907214879
     and f.propval in ('IPF.Appointment','IPF.Journal','IPF.StickyNote','IPF.Task','IPF.Contact')
inner join message_properties mp1 on mp1.message_id = m.message_id and mp1.proptag = 1703967
     and mp1.propval in ('IPM.Appointment','IPM.Activity','IPM.StickyNote','IPM.Task','IPM.Contact')
inner join folder_properties f2 on f2.folder_id = m.parent_fid and f2.proptag = 805371935
where 1=1
and ('${message_type}' = 'all' or mp1.propval = '${message_type}')
and m.parent_fid not in (11,14,23)
order by m.message_id asc;

EOSQL

#    # replace variables with values
#    sel=${sql/\#\#fields\#\#/$fields}
#    sel=$(sed 's/\#\#message_type\#\#/'"$message_type"'/g' <<<"$sel")
#    #echo "SQL=$sel"
#    printf '%s' "$(sqlite3 "${mdir}" "${sel}")"

  fi

}

_grom_backup_messages() {
  local target_folder message_type
  local user obj msgtype fname msgid
  local cnt=0

  # define function
  _grom_users="${_grom_users:=$(grom_users)}"

  # Call without parameters, apply default values
  if [[ "$#" -eq 0 ]]; then
    for user in $_grom_users; do
      # All message_types
      message_type="all"
      target_folder="${TRG}"
      _grom_backup_messages "${user}" "${target_folder}" "${message_type}"
    done
  fi

  # Call with user, target_folder and message_type
  if [[ "$#" -eq 3 ]]; then
    if [[ "$_grom_users" =~ $1 ]]; then
      user="$1"
      target_folder="$2"
      message_type="$3"

      # log message count
      cnt=$(_grom_query_backup "${user}" "count" "${message_type}")
      logger -t grom_backup_messages \
        "$user: $cnt items to backup"

      # retrieve message ids
      for obj in $(_grom_query_backup "${user}" "data" "${message_type}"); do
        # split obj into folder-name and message-id
        IFS=\| read -r msgtype fname msgid <<< "$obj"

        # backup the message
        (
        mkdir -p "${target_folder}/${fname}"
        # print a line per message
        #printf '%s: %s/%s\n' "${user}" "${fname}" "${msgid}"

        # binary depends on msgtype
        case "$msgtype" in
          "IPM.Appointment")  gromox-exm2ical -u "${user}" "${msgid}" > "${target_folder}/${fname}/${msgid}.ics" 2>/dev/null;;
          "IPM.Contact")      gromox-exm2vcf -u "${user}" "${msgid}" > "${target_folder}/${fname}/${msgid}.vcf" 2>/dev/null;;
          *)                  gromox-exm2eml -u "${user}" "${msgid}" > "${target_folder}/${fname}/${msgid}.eml" 2>/dev/null;;
        esac
        ) | logger -t grom_backup_messages

      done
      echo "$cnt"
    fi
  fi
}

grom_backup_messages() {
  local domain user target_folder message_type
  local uid domname
  local tempdir
  local usage="Usage: $0 domain target_folder message_type"
  local cnt=0

  # Verify if required binaries exist
  command -v gromox-exm2eml >/dev/null 2>&1 || { echo >&2 "Error: gromox-exm2eml is required but not installed."; exit 1; }
  command -v gromox-exm2ical >/dev/null 2>&1 || { echo >&2 "Error: gromox-exm2ical is required but not installed."; exit 1; }
  command -v gromox-exm2vcf >/dev/null 2>&1 || { echo >&2 "Error: gromox-exm2vcf is required but not installed."; exit 1; }
  command -v bzip2 >/dev/null 2>&1 || { echo >&2 "Error: bzip2 is required but not installed."; exit 1; }

  # Retrieve domains globally
  _grom_domains="${_grom_domains:=$(grom_domains)}"

  # Verify if mandatory arguments are set
  if [[ "$#" -ne 3 ]]; then
    echo $"$usage"; exit 1
  else
    # Verify domain
    if ! [[ "$_grom_domains" =~ $1 || "$1" = "all" ]]; then
      echo "Error: Domain '$1' does not exist." >&2; echo $"$usage"; exit 1
    fi
    domain=$1

    # Define _grom_users globally depending on domain parameter
    if [[ "$domain" = "all" ]]; then
      _grom_users="${_grom_users:=$(grom_users)}"
    else
      _grom_users="${_grom_users:=$(grom_users "$domain")}"
    fi

    # Verify target_folder
    if ! [[ -d "$2" ]]; then
      echo "Error: Target folder '$2' does not exist." >&2; echo $"$usage"; exit 1
    fi
    target_folder=$2

    # Verify message_type
    case "$3" in
      "calendar")    message_type="IPM.Appointment";;
      "contacts")    message_type="IPM.Contact";;
      "journal")     message_type="IPM.Activity";;
      "tasks")       message_type="IPM.Task";;
      "stickynotes") message_type="IPM.StickyNote";;
      #      "all")         message_type="all";;
      *) echo "Error: Message type '$3' is not allowed." >&2; echo $"$usage"; exit 1
    esac

    # Create temp directory
    tempdir=$(mktemp -d)
    trap 'rm -rf -- "$tempdir"' EXIT

    # Loop over users
    for user in $_grom_users; do
      # split user into uid and domname parts
      IFS=\@ read -r uid domname <<< "$user"
      cnt=$(_grom_backup_messages "${user}" "$tempdir/$domname/$uid" "${message_type}")
    done
    # tar and compress the output and store it to target folder
    tar -cjf "${target_folder}/backup-$1-$3_$(date +'%Y%m%d-%H%M%S').tar.bz2" -C "$tempdir" .

  fi
}