#!/usr/bin/env bash
# wp-restore-test.sh, monthly verified-restore check for the off-server backup.
# Pulls the latest restic snapshot to a scratch dir, loads the SQL into a scratch
# database, runs basic sanity checks, then cleans up.
# Install at /usr/local/sbin/wp-restore-test.sh, run by hand or from cron.
# Source: https://techearl.com/wordpress-offsite-backups-3-2-1-with-verification
# Site:   https://techearl.com/
set -euo pipefail

SITE="example.com"
SCRATCH="/var/lib/backup/restore-test"
DB_TEST="wp_restore_test"

export RESTIC_REPOSITORY="s3:s3.us-east-1.amazonaws.com/example-backups/${SITE}"
export RESTIC_PASSWORD_FILE="/root/.restic.password"
export AWS_ACCESS_KEY_ID=$(cat /root/.restic.aws-key)
export AWS_SECRET_ACCESS_KEY=$(cat /root/.restic.aws-secret)

mkdir -p "$SCRATCH"
rm -rf "${SCRATCH:?}/"*

# 1) Restore the most recent snapshot
echo "restoring latest snapshot to ${SCRATCH}..."
restic restore latest --target "$SCRATCH"

# 2) Find the database dump and load it into a scratch DB
DUMP=$(find "$SCRATCH" -name '*.sql.gz' | sort | tail -1)
echo "loading ${DUMP} into ${DB_TEST}..."

mysql --defaults-extra-file=/root/.my-restore.cnf -e "DROP DATABASE IF EXISTS ${DB_TEST}; CREATE DATABASE ${DB_TEST};"
zcat "$DUMP" | mysql --defaults-extra-file=/root/.my-restore.cnf "$DB_TEST"

# 3) Verify the restore by checking row counts and a known option
ROWS=$(mysql --defaults-extra-file=/root/.my-restore.cnf -BN -e "SELECT COUNT(*) FROM ${DB_TEST}.wp_posts;")
HOME_URL=$(mysql --defaults-extra-file=/root/.my-restore.cnf -BN -e "SELECT option_value FROM ${DB_TEST}.wp_options WHERE option_name='siteurl';")

echo "wp_posts row count: ${ROWS}"
echo "siteurl: ${HOME_URL}"

if [ "$ROWS" -lt 10 ]; then
  echo "FAIL: wp_posts has fewer than 10 rows; restore is incomplete"
  exit 1
fi

if [ -z "$HOME_URL" ]; then
  echo "FAIL: siteurl is empty"
  exit 1
fi

echo "PASS: restore verification complete"

# 4) Clean up
mysql --defaults-extra-file=/root/.my-restore.cnf -e "DROP DATABASE ${DB_TEST};"
rm -rf "${SCRATCH:?}/"*