#!/usr/bin/env bash
# Nightly backup. Cron:
#   15 1 * * * /var/www/pcea/deploy/backup.sh >> /var/log/pcea-backup.log 2>&1
#
# A backup nobody has restored is a hope, not a backup. This script verifies the
# dump loads into a scratch database before it counts the night a success —
# a truncated dump is otherwise indistinguishable from a good one until the day
# you need it.
set -euo pipefail

ROOT="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
[ -f "$ROOT/.env" ] && set -a && . "$ROOT/.env" && set +a

DIR="${BACKUP_DIR:-$ROOT/storage/backups}"
KEEP_DAILY=14
KEEP_MONTHLY=12
STAMP="$(date +%Y%m%d-%H%M%S)"
FILE="$DIR/pcea-$STAMP.sql.gz"

mkdir -p "$DIR"

echo "$(date -Is) dumping $DB_NAME"
mysqldump \
  --host="${DB_HOST:-127.0.0.1}" --port="${DB_PORT:-3306}" \
  --user="$DB_USER" --password="$DB_PASS" \
  --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 \
  "$DB_NAME" | gzip -9 > "$FILE"

SIZE=$(stat -c%s "$FILE")
if [ "$SIZE" -lt 51200 ]; then
  echo "$(date -Is) ABORT: dump is only ${SIZE} bytes — that is not a real backup"
  exit 1
fi

# Prove it restores.
#
# DRILL_DB, if set in .env, names a database provisioned in advance
# through cPanel's own MySQL Databases screen and granted to this same
# database user — used as-is, its tables dropped and recreated each run,
# rather than a fresh CREATE DATABASE. A correctly scoped cPanel database
# user cannot CREATE or DROP a database at all; that is a control-panel
# operation there, not something even full privileges on one database
# grants. Unset (the default), this behaves exactly as it always has: a
# freshly created, freshly dropped scratch database, which needs a user
# with that broader privilege — the normal case for a VPS or a host where
# this script runs as the database's own superuser.
if [ -n "${DRILL_DB:-}" ]; then
  SCRATCH="$DRILL_DB"
  echo "$(date -Is) verifying restore into pre-provisioned $SCRATCH"
  mysql --host="${DB_HOST:-127.0.0.1}" --port="${DB_PORT:-3306}" --user="$DB_USER" --password="$DB_PASS" \
        -e "USE \`$SCRATCH\`; SET FOREIGN_KEY_CHECKS=0; $(mysql -N -B --host="${DB_HOST:-127.0.0.1}" --port="${DB_PORT:-3306}" \
            --user="$DB_USER" --password="$DB_PASS" \
            -e "SELECT CONCAT('DROP ', IF(TABLE_TYPE='VIEW','VIEW','TABLE'), ' IF EXISTS \\\`', TABLE_NAME, '\\\`;')
                  FROM information_schema.tables WHERE table_schema='$SCRATCH'
                  ORDER BY (TABLE_TYPE='BASE TABLE')" | tr -d '\n')"
else
  SCRATCH="${DB_NAME}_verify_$$"
  echo "$(date -Is) verifying restore into $SCRATCH"
  mysql --host="${DB_HOST:-127.0.0.1}" --port="${DB_PORT:-3306}" --user="$DB_USER" --password="$DB_PASS" \
        -e "CREATE DATABASE \`$SCRATCH\` CHARACTER SET utf8mb4;"
fi
if gunzip -c "$FILE" | mysql --host="${DB_HOST:-127.0.0.1}" --port="${DB_PORT:-3306}" --user="$DB_USER" \
       --password="$DB_PASS" "$SCRATCH"; then
  ROWS=$(mysql -N -B --host="${DB_HOST:-127.0.0.1}" --port="${DB_PORT:-3306}" --user="$DB_USER" --password="$DB_PASS" \
         -e "SELECT COUNT(*) FROM \`$SCRATCH\`.contribution;")
  echo "$(date -Is) restore OK — $ROWS contributions present"
  touch "$DIR/.restore-tested"
else
  echo "$(date -Is) ABORT: the dump does not restore"
  if [ -z "${DRILL_DB:-}" ]; then
    mysql --host="${DB_HOST:-127.0.0.1}" --port="${DB_PORT:-3306}" --user="$DB_USER" --password="$DB_PASS" \
          -e "DROP DATABASE IF EXISTS \`$SCRATCH\`;"
  fi
  exit 1
fi
if [ -z "${DRILL_DB:-}" ]; then
  mysql --host="${DB_HOST:-127.0.0.1}" --port="${DB_PORT:-3306}" --user="$DB_USER" --password="$DB_PASS" \
        -e "DROP DATABASE \`$SCRATCH\`;"
fi

# Keep 14 daily, and the first of each month for a year.
find "$DIR" -name 'pcea-*.sql.gz' -mtime +$KEEP_DAILY \
  ! -name "pcea-????????01-*" -delete
find "$DIR" -name 'pcea-????????01-*.sql.gz' -mtime +$((KEEP_MONTHLY * 31)) -delete

echo "$(date -Is) done: $FILE ($(numfmt --to=iec $SIZE))"

# Copy off the machine. A backup on the same disk as the database survives a
# mistake but not a fire, a theft, or a failed drive.
if [ -n "${BACKUP_REMOTE:-}" ]; then
  rsync -a "$FILE" "$BACKUP_REMOTE" && echo "$(date -Is) copied offsite"
fi