#!/usr/bin/env bash
# =============================================================================
# mysql_backup.sh — Nightly live-to-local MySQL backup & redeploy
# =============================================================================
# Dumps a read-only replica/live host, verifies the dump, drops the old local
# database, and restores the fresh snapshot.  Designed to run at 20:00 via
# a systemd timer (see mysql-backup.timer / mysql-backup.service).
#
# CONFIGURATION — edit the block below before first use.
# =============================================================================

set -euo pipefail

# ---------------------------------------------------------------------------
# User-editable settings
# ---------------------------------------------------------------------------
REMOTE_HOST="sql39.cpt3.host-h.net"   # live / read-only replica hostname or IP
REMOTE_PORT="3306"
REMOTE_USER="mfx8g_nud0n"           # read-only DB user on the live host
REMOTE_PASS="64794b8Wo714MD"        # or use ~/.my.cnf / secrets manager
REMOTE_DB="timsheets2"          # database name to dump

LOCAL_HOST="127.0.0.1"
LOCAL_PORT="3307"
LOCAL_USER="admin"                   # local MySQL admin user
LOCAL_PASS="secret"                       # leave empty to use ~/.my.cnf / socket auth
LOCAL_DB="timsheets2_backup"     # local DB name to recreate each night

BACKUP_DIR="/var/backups/mysql"     # where dumps are stored
KEEP_DAYS=7                         # how many days of dumps to retain
LOG_FILE="/var/log/mysql_backup.log"

# Optional: email alert on failure (requires 'mail' / 'sendmail' installed)
ALERT_EMAIL="jaco@overdrive.co.za"                      # e.g. "admin@example.com" — leave empty to skip
# ---------------------------------------------------------------------------

# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
DUMP_FILE="${BACKUP_DIR}/${REMOTE_DB}_${TIMESTAMP}.sql.gz"

log()  { echo "[$(date '+%F %T')] $*" | tee -a "$LOG_FILE"; }
fail() {
    log "ERROR: $*"
    [[ -n "$ALERT_EMAIL" ]] && echo "mysql_backup.sh failed on $(hostname): $*" \
        | mail -s "[BACKUP FAILED] ${REMOTE_DB}" "$ALERT_EMAIL" 2>/dev/null || true
    exit 1
}

# ---------------------------------------------------------------------------
# 1. Ensure backup directory exists
# ---------------------------------------------------------------------------
mkdir -p "$BACKUP_DIR"
log "===== Backup run started ====="
log "Source: ${REMOTE_USER}@${REMOTE_HOST}:${REMOTE_PORT}/${REMOTE_DB}"
log "Target: ${LOCAL_HOST}/${LOCAL_DB}"
log "Dump  : ${DUMP_FILE}"

# ---------------------------------------------------------------------------
# 2. Dump from live / read-only host (compressed)
# ---------------------------------------------------------------------------
log "Dumping remote database..."

MYSQL_DUMP_OPTS=(
    --host="$REMOTE_HOST"
    --port="$REMOTE_PORT"
    --user="$REMOTE_USER"
    --password="$REMOTE_PASS"
    --single-transaction          # consistent snapshot without locking
    --routines                    # include stored procedures & functions
    --triggers                    # include triggers
    --events                      # include scheduled events
    --set-gtid-purged=OFF         # avoids GTID issues on non-replica targets
    --compress                    # compress client-server traffic
    "$REMOTE_DB"
)

mysqldump "${MYSQL_DUMP_OPTS[@]}" | gzip > "$DUMP_FILE" \
    || fail "mysqldump failed — check credentials and connectivity."

# ---------------------------------------------------------------------------
# 3. Verify the dump
# ---------------------------------------------------------------------------
log "Verifying dump integrity..."

[[ -f "$DUMP_FILE" ]] || fail "Dump file not found after mysqldump."

DUMP_SIZE=$(stat -c%s "$DUMP_FILE" 2>/dev/null || stat -f%z "$DUMP_FILE")
[[ "$DUMP_SIZE" -gt 1024 ]] || fail "Dump file is suspiciously small (${DUMP_SIZE} bytes)."

gunzip -t "$DUMP_FILE" || fail "Dump file failed gunzip integrity check."

log "Dump verified: ${DUMP_SIZE} bytes, gzip OK."

# ---------------------------------------------------------------------------
# 4. Build mysql client options for local host
# ---------------------------------------------------------------------------
LOCAL_OPTS=(--host="$LOCAL_HOST" --port="$LOCAL_PORT" --user="$LOCAL_USER")
[[ -n "$LOCAL_PASS" ]] && LOCAL_OPTS+=(--password="$LOCAL_PASS")

# ---------------------------------------------------------------------------
# 5. Drop & recreate the local database
# ---------------------------------------------------------------------------
log "Dropping local database '${LOCAL_DB}'..."
mysql "${LOCAL_OPTS[@]}" -e "DROP DATABASE IF EXISTS \`${LOCAL_DB}\`;" \
    || fail "Could not drop local database."

log "Creating local database '${LOCAL_DB}'..."
mysql "${LOCAL_OPTS[@]}" -e "CREATE DATABASE \`${LOCAL_DB}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" \
    || fail "Could not create local database."

# ---------------------------------------------------------------------------
# 6. Restore the fresh dump
# ---------------------------------------------------------------------------
log "Restoring dump to local database..."
gunzip -c "$DUMP_FILE" | mysql "${LOCAL_OPTS[@]}" "$LOCAL_DB" \
    || fail "Restore failed — local database may be in an inconsistent state."

# ---------------------------------------------------------------------------
# 7. Quick sanity check — count tables
# ---------------------------------------------------------------------------
TABLE_COUNT=$(mysql "${LOCAL_OPTS[@]}" -sN -e "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='${LOCAL_DB}';")
log "Restore complete. Table count in '${LOCAL_DB}': ${TABLE_COUNT}"
[[ "$TABLE_COUNT" -gt 0 ]] || fail "Restore produced 0 tables — something went wrong."

# ---------------------------------------------------------------------------
# 8. Rotate old dumps (keep KEEP_DAYS worth)
# ---------------------------------------------------------------------------
log "Rotating old backups (keeping last ${KEEP_DAYS} days)..."
find "$BACKUP_DIR" -name "${REMOTE_DB}_*.sql.gz" -mtime +"$KEEP_DAYS" -delete

log "===== Backup run finished successfully ====="