#!/bin/bash
#
# avreg-backup - script to backup AVReg configuration
#
# Version 6.3p12 at May 2021
#
# Usage: sudo avreg-backup
#
# Help: avreg-backup --help or man avreg-backup
#
# Copyright: 2021 (C) "Setevye informatsionnye sistemy", Ltd
#                     "Network Information Systems", Ltd
#
# Report bugs: support@avreg.net
#
# TODO:
#  - checksum

set -e

LANG=C
export LANG

VERSION='6.3'

PGSQL_CSV_OPTIONS="DELIMITER ',' NULL '\N' CSV  QUOTE '\"' ESCAPE '\'"
MYSQL_CSV_OPTIONS="FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\\' LINES TERMINATED BY '\n'"
# PSQL_OPTIONS='-v ON_ERROR_STOP=on --single-transaction -b'

OPT_AVREG_CONF='/etc/avreg/avreg.conf'
OPT_BEQUIET='no'
OPT_INCLUDES=
OPT_EXCLUDES=
OPT_SKIP_PROFILES='no'
OPT_LOCK_TABLES='no'
OPT_DO_STOP_AVREG='no'
OPT_DO_START_AVREG='no'

DB_PARAMS='db-type db-host db-port db-name'
PARAMS="server-name $DB_PARAMS storage-dir user group key"
CUR_AVREG_COMMON_VER="$(dpkg-query -W -f='${Version}' avreg-common)"
CUR_AVREG_MAJOR_VER="${CUR_AVREG_COMMON_VER%%\.*}"
CONF_TABLES='cameras users web_layouts local_layouts'
DATA_TABLES='events tree_events'
ALL_TABLES="$CONF_TABLES $DATA_TABLES"
TABLES=
HAVE_AVREGD='no'
AVREGD_RUNNING_NOW='no'
#HAVE_APACHE2='no'
SCRIPTNAME="$(basename $0)"
CUR_DATE="$(date +%F)"
PROFILESDIR='/etc/avreg/profiles'
if [ -d "$PROFILESDIR" ]; then
   AVREGD_PROFILES=$(cd $PROFILESDIR 2>/dev/null && find -type f -regex '\./[A-Za-z0-9][A-Za-z0-9_\.:\-]+' -exec basename {} \; | sort)
else
   AVREGD_PROFILES=
fi
TMPDIR="${TMP:-/tmp}"
CHKSUM='sha1sum'

### if MYSQL @@secure_file_priv used.
# variable of @@secure_file_priv
MYSQL_LOAD_DIR=
# prefix to filename for dumps: ${MYSQL_LOAD_DIR}/${MYSQL_SECURE_FILE_PREFIX}_cameras.sql
# MUST BE empty if secure_file_priv not used
MYSQL_SECURE_FILE_PREFIX=
RANDOM_LENGTH=11

if [ "${DEBUG:-no}" != 'no' ]; then
   set -x
fi

Usage() {
   local err_msg="$1"

   if [ -n "$err_msg" ]; then
      echo "ERROR: $err_msg" 1>&2
      echo 1>&2
   fi

   cat >&2 <<-__EOF__
Usage: sudo ${SCRIPTNAME} [OPTIONS] [OUTPUT_(DIR|TARGZ_FILE_PATH)]

Options:
  database access options:
    -T, --db-type=SQLTYPE     SQL server type: "mysql" or "pgsql",
                              default "mysql".
    -H, --db-host=HOST        SQL server hostname, default <empty>(=local).
    -N, --db-name=DBNAME      Database name, default "avreg${CUR_AVREG_MAJOR_VER}_db"
    -U, --db-user=USER        Database user name, default <empty>.
    -P, --db-passwd=PASSWORD  Database user password, default <empty>.

   data options:
    -I, --include-tables=LIST List of tables to backup, default:
                              "$(echo ${CONF_TABLES} | tr ' ' ',')"
    -E, --exclude-tables=LIST List of excluded of tables to backup

    --exclude-profiles        Exclude /etc/avreg/profiles/* avregd
                              sub-config files.

  common options:
    -c, --conf=PATH           Path of config file, default
                              "${OPT_AVREG_CONF}".

    -L, --lock-tables         Lock tables.
        --stop-avreg          Stop avreg-sevice if "EVENTS" table is selected.
        --start-avreg         Start avreg-sevice if stopped above.

    -q, --quiet               Be quiet.
    -h, --help                Print usage help and exit.
    -V, --version             Print version and exit.

Examples:
sudo ${SCRIPTNAME} --include-tables=events
   local full backup with EVENTS table data

sudo ${SCRIPTNAME} -T pgsql -H 1.2.3.4 -U sqlrootuser -P sqlpassword --exclude-profiles
   remote backup, only conf tables, PostgreSQL server on 1.2.3.4 host

Report bugs to <support@avreg.net>.
__EOF__
}

# Note that we use `"$@"' to let each command-line parameter expand to a
# separate word. The quotes around `$@' are essential!
# We need TEST as the `eval set --' would nuke the return value of getopt.
set +e
TEST=$(getopt -o 'qhVc:T:H:N:U:P:I:E:L' \
   --longoptions 'quiet,help,version,conf:,db-type:,db-host:,db-name:,db-user:,db-passwd:,include-tables:,exclude-tables:,lock-tables,exclude-profiles,start-avreg,stop-avreg' \
   -n "${SCRIPTNAME}" -- "$@")
if [ $? -ne 0 ]; then
   Usage
   exit 1
fi
set -e

# Note the quotes around `$TEST': they are essential!
eval set -- "$TEST"

pos=0
while [ -n "$*" ]; do
   #echo "cmdopt[$pos]($#) = \"$1\""
   case "$1" in
   '-V' | '--version')
      echo "${SCRIPTNAME} $VERSION"
      exit 0
      ;;
   '-h' | '--help')
      Usage
      exit 0
      ;;
   '-q' | '--quiet')
      OPT_BEQUIET='no'
      shift
      ;;
   '-c' | '--conf')
      OPT_AVREG_CONF="$2"
      shift 2
      ;;
   '-T' | '--db-type')
      db_type="$2"
      shift 2
      ;;
   '-H' | '--db-host')
      db_host="$2"
      shift 2
      ;;
   '-N' | '--db-name')
      db_name="$2"
      shift 2
      ;;
   '-U' | '--db-user')
      db_user="$2"
      shift 2
      ;;
   '-P' | '--db-passwd')
      db_passwd="$2"
      shift 2
      ;;
   '-I' | '--include-tables')
      OPT_INCLUDES=$(echo $2 | tr [:upper:] [:lower:] | tr ',' ' ')
      shift 2
      ;;
   '-E' | '--exclude-tables')
      OPT_EXCLUDES=$(echo $2 | tr [:upper:] [:lower:] | tr ',' ' ')
      shift 2
      ;;
   '-L' | '--lock-tables')
      OPT_LOCK_TABLES='yes'
      shift
      ;;
   '--exclude-profiles')
      OPT_SKIP_PROFILES='yes'
      shift
      ;;
   '--stop-avreg')
      OPT_DO_STOP_AVREG='yes'
      shift
      ;;
   '--start-avreg')
      OPT_DO_START_AVREG='yes'
      shift
      ;;
   '--') shift ;;
   *)
      case "$pos" in
      0)
         OUTPUT_ARG="$1"
         shift
         ;;
      *)
         Usage "Ambiguous option \"$1\""
         exit 1
         ;;
      esac
      pos=$(($pos + 1))
      ;;
   esac
done

cameras_ORDER='bind_mac,cam_nr'
users_ORDER='status,change_time'
web_layouts_ORDER='bind_mac,mon_nr'
local_layouts_ORDER='bind_mac,display,mon_nr'
events_ORDER='dt1,cam_nr,evt_id'

pgsql_get_dump_sql() {
   local out_path_prefix="$1" t= T= fromsql=

   echo "SET NAMES 'utf8';"
   for t in $TABLES; do
      order_var_name="${t}_ORDER"
      order_var_val=$(eval echo \$${order_var_name})
      if [ -n "${order_var_val}" ]; then
         fromsql="(SELECT * FROM ${t} ORDER BY ${order_var_val})"
      else
         fromsql="${t}"
      fi

      cat <<__EOF__
COPY $fromsql TO '${out_path_prefix}${t}.csv' ${PGSQL_CSV_OPTIONS};
__EOF__
   done
}

mysql_get_dump_sql() {
   local out_path_prefix="$1" t= T= fromsql= tables_to_lock= lock_sql= order_var_name= order_var_val=

   echo "SET NAMES 'utf8' COLLATE 'utf8_general_ci';"

   if [ "x$OPT_LOCK_TABLES" = 'xyes' ]; then
      for t in $TABLES; do
         T="$(echo ${t} | tr [:lower:] [:upper:])"
         if [ -n "$tables_to_lock" ]; then
            tables_to_lock="${tables_to_lock},"
         fi
         tables_to_lock="$tables_to_lock ${T} WRITE"
      done
      echo "LOCK TABLES ${tables_to_lock};"
   fi

   for t in $TABLES; do
      T="$(echo ${t} | tr [:lower:] [:upper:])"
      order_var_name="${t}_ORDER"
      order_var_val=$(eval echo \$${order_var_name} | tr [:lower:] [:upper:])
      if [ -n "${order_var_val}" ]; then
         fromsql="SELECT * FROM ${T} ORDER BY ${order_var_val}"
      else
         fromsql="SELECT * from ${T}"
      fi

      cat <<__EOF__
$fromsql INTO OUTFILE '${out_path_prefix}${t}.csv' ${MYSQL_CSV_OPTIONS};
__EOF__
   done

   if [ "x$OPT_LOCK_TABLES" = 'xyes' ]; then
      echo "UNLOCK TABLES;"
   fi
}

if [ ! -r "${OPT_AVREG_CONF}" -o ! -r '/usr/lib/avreg-common/helpers.sh' ]; then
   Usage 'required "avreg-common" package is not istalled'
fi

. '/usr/lib/avreg-common/helpers.sh'

if is_execs_exist avregd 'avreg-service'; then
   HAVE_AVREGD='yes'
fi
#if is_execs_exist apache2; then
#   HAVE_APACHE2='yes'
#fi

parse_avreg_conf $PARAMS

if [ -z "$db_type" ]; then
   db_type='mysql'
fi

. "/usr/lib/avreg-common/${db_type}"
trap ${db_type}_atexit EXIT

${db_type}_prepare_client "$db_host" "$db_port" "$db_admin" "$db_admpass"

if [ -z "$db_name" ]; then
   db_name="avreg${CUR_AVREG_MAJOR_VER}_db"
fi
[ -z "$storage_dir" ] && storage_dir='/var/spool/avreg'
[ -z "$user" ] && user='avreg'
[ -z "$group" ] && group='avreg'

if [ -z "$OUTPUT_ARG" ]; then
   OUTPUT_ARCHIVE_NAME="AVReg_${CUR_AVREG_COMMON_VER}-$(hostname -s)-${db_type}-${db_name}-${CUR_DATE}"
   OUTPUT_ARCHIVE_PATH="$(realpath ${OUTPUT_ARCHIVE_NAME}.tgz)"
elif [ -d "$OUTPUT_ARG" ]; then
   OUTPUT_ARCHIVE_NAME="AVReg_${CUR_AVREG_COMMON_VER}-$(hostname -s)-${db_type}-${db_name}-${CUR_DATE}"
   OUTPUT_ARCHIVE_PATH="$(realpath ${OUTPUT_ARG}/${OUTPUT_ARCHIVE_NAME}.tgz)"
elif [ -f "$OUTPUT_ARG" ]; then
   Usage "File \"$OUTPUT_ARG\" exist, we will not overwrite it."
   exit 1
else
   OUTPUT_ARCHIVE_NAME="${OUTPUT_ARG%%.*}"
   OUTPUT_ARCHIVE_PATH="$(realpath ${OUTPUT_ARCHIVE_NAME}.tgz)"
fi

cleanup() {
   local return_value=$?
   safe_rm_fR "$FSQL" "$OUTDIR"

   if [ -n "$MYSQL_SECURE_FILE_PREFIX" ]; then
      rm ${MYSQL_LOAD_DIR}/${MYSQL_SECURE_FILE_PREFIX}*.csv 2>/dev/null
   fi

   exit $return_value
}
trap "${db_type}_atexit;cleanup" EXIT

FSQL="$(mktemp ${TMPDIR}/${db_name}.${db_type}.XXXXXX.sql)"
OUTDIR="$(mktemp -d ${TMPDIR}/${db_name}.${db_type}.XXXXXX)"
chmod a+rwx "$OUTDIR"

mkdir "$OUTDIR/database"
chmod a+rwx "$OUTDIR/database"

TABLES_=
if [ -n "$OPT_INCLUDES" ]; then
   TABLES_="$TABLES_ $OPT_INCLUDES"
fi
for t in $CONF_TABLES; do
   if [ -z "$OPT_EXLUDES" ] || echo "$OPT_EXLUDES" | grep -qw '${t}'; then
      TABLES_="$TABLES_ ${t}"
   fi
done
TABLES=$(echo "$TABLES_" | tr ' ' "\n" | sort | uniq | tr "\n" ' ' | xargs)

if [ "x$OPT_DO_STOP_AVREG" = 'xyes' -a "$HAVE_AVREGD" = 'yes' ] && echo $TABLES | grep -qw 'events'; then
   if avreg-service status >/dev/null; then
      AVREGD_RUNNING_NOW='yes'
   fi

   if [ "x$AVREGD_RUNNING_NOW" = 'xyes' ]; then
      log 'Stop AVReg service'
      avreg-service stop >/dev/null
   fi
fi

csv_outdir_prefix="$OUTDIR/database/"
if [ "$db_type" = 'mysql' ]; then
   msd=$(echo 'select @@secure_file_priv' | ${db_type}_exec_sql 2>/dev/null)
   if [ -n "$msd" -a -d "$msd" ]; then
      MYSQL_LOAD_DIR=${msd%/}
      MYSQL_SECURE_FILE_PREFIX=$(get_random_string $RANDOM_LENGTH ; echo -n -)
      csv_outdir_prefix="$MYSQL_LOAD_DIR/$MYSQL_SECURE_FILE_PREFIX"
   fi
fi

${db_type}_get_dump_sql "$csv_outdir_prefix" >>"$FSQL"

# cat "${FSQL}"

cp "${FSQL}" "$OUTDIR/dump.sql"
log "Dump \"${db_type}://${db_host:-localhost}/${db_name}\": [$(echo $TABLES | tr ' ' ',')]"
# sudo -u postgres psql ${PSQL_OPTIONS} ${db_name} <"${FSQL}" >/dev/null
${db_type}_exec_sql "${db_name}" "$FSQL" >/dev/null

if [ -n "$MYSQL_SECURE_FILE_PREFIX" ]; then
   # MySQL and @@secure_file_priv is not empty.
   (
      cd "$MYSQL_LOAD_DIR"
      for f in ${MYSQL_SECURE_FILE_PREFIX}*.csv; do
         if [ -f "${f}" ]; then
            g=${f#$MYSQL_SECURE_FILE_PREFIX}
            mv ${f} "$OUTDIR/database/${g}"
         fi
      done
   )
   # reset MYSQL_SECURE_FILE_PREFIX
   MYSQL_SECURE_FILE_PREFIX=
fi

# AVREGD_RUNNING_NOW=no (as default) if no EVENTS dump
if [ "x$AVREGD_RUNNING_NOW" = 'xyes' -a "x$OPT_DO_START_AVREG" = 'xyes' ]; then
   log 'Start AVReg service"'
   avreg-service start >/dev/null
fi

find "$OUTDIR/database" -type f -empty -name '*.csv' -delete

ExternalAuthMapFile=$(update-avreg.conf print avreg-site --param=ExternalAuthMap 2>/dev/null || true)

cat >"$OUTDIR/.date" <<__EOF__
backup_date='$(date -R)'
__EOF__

cat >"$OUTDIR/.meta" <<__EOF__
backup_avreg_common_ver='$CUR_AVREG_COMMON_VER'
backup_db_type='$db_type'
backup_db_name='$db_name'
backup_tables='$TABLES'
backup_key='$key'
backup_ext_auth='${ExternalAuthMapFile##*/}'
backup_checksum_fn='$CHKSUM'
__EOF__

if [ "$OPT_SKIP_PROFILES" = 'no' -a -n "$AVREGD_PROFILES" ]; then
   log 'Copy avregd profile configs'
   mkdir "$OUTDIR/profiles"
   chmod a+rwx "$OUTDIR/profiles"
   for p in $AVREGD_PROFILES; do
      cp "/etc/avreg/profiles/$p" "$OUTDIR/profiles/"
   done
fi

if [ -n "$key" -a -r "$key" ]; then
   log 'Copy license key file'
   cp "$key" "$OUTDIR/"
fi

var_lib_obj=$(find /var/lib/avreg -mindepth 1 -maxdepth 1 \( -type d -not -empty -o -type f -not -size 0 \) -a -not -name '\.*')
if [ -n "$var_lib_obj" ]; then
   log 'Copy /var/lib/avreg/*'
   mkdir -p "$OUTDIR/var/lib/avreg"
   chmod a+rwx "$OUTDIR/var/lib/avreg"

   cp -aRPx $var_lib_obj "$OUTDIR/var/lib/avreg/"

   (cd "$OUTDIR/var/lib/avreg" && getfacl -R . >'../../../var_lib_avreg_facl.lst')
fi

if [ -r "$ExternalAuthMapFile" ]; then
   log 'Copy ExternalAuthMap file'
   cp "$ExternalAuthMapFile" "$OUTDIR/"
fi

update-avreg.conf print --strict >"$OUTDIR/avreg.conf"

log "Make ${CHKSUM}"
(cd $OUTDIR && find * -type f -exec ${CHKSUM} {} \; | sort -k 2 >$OUTDIR/.${CHKSUM}s)

(cd $OUTDIR && tar czf ${OUTPUT_ARCHIVE_PATH} .meta .date .${CHKSUM}s *)

log "Done: ${OUTPUT_ARCHIVE_PATH}"

if [ "x$OPT_BEQUIET" != 'xyes' ]; then
   cat <<__EOF__

Restore examples:
  (current) : sudo avreg-restore path/to/${OUTPUT_ARCHIVE_NAME}.tgz
  PostgreSQL: sudo avreg-restore --db-type=pgsql path/to/${OUTPUT_ARCHIVE_NAME}.tgz
  MySQL     : sudo avreg-restore --db-type=mysql path/to/${OUTPUT_ARCHIVE_NAME}.tgz
__EOF__
fi
