#!/usr/bin/env bash
#
# A&B Hub — Database Backup Script
#
# Usage: ./backup-database.sh
# Cron:  0 2 * * *  /var/www/ab-hub-backend/deploy/backup-database.sh >> /var/log/ab-hub-backup.log 2>&1
#
# Reads DB credentials from the app's own .env (never hardcode them
# here), dumps to a timestamped, gzip-compressed file, and deletes
# local backups older than RETENTION_DAYS. For real production use,
# also sync BACKUP_DIR to off-server storage (S3, another host, etc.)
# — a backup that lives on the same disk as the database it backs up
# does not protect against disk/server failure, only against bad
# migrations or accidental deletes.

set -euo pipefail

APP_ROOT="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
ENV_FILE="${APP_ROOT}/.env"
BACKUP_DIR="${APP_ROOT}/storage/backups"
RETENTION_DAYS=14

if [ ! -f "$ENV_FILE" ]; then
    echo "ERROR: .env not found at ${ENV_FILE}" >&2
    exit 1
fi

get_env() {
    grep -E "^${1}=" "$ENV_FILE" | head -1 | cut -d '=' -f2- | sed -e 's/^"//' -e 's/"$//'
}

DB_HOST="$(get_env DB_HOST)"
DB_PORT="$(get_env DB_PORT)"
DB_DATABASE="$(get_env DB_DATABASE)"
DB_USERNAME="$(get_env DB_USERNAME)"
DB_PASSWORD="$(get_env DB_PASSWORD)"

mkdir -p "$BACKUP_DIR"

TIMESTAMP="$(date +%Y-%m-%d_%H-%M-%S)"
OUTFILE="${BACKUP_DIR}/ab-hub-${TIMESTAMP}.sql.gz"

echo "[$(date)] Starting backup of ${DB_DATABASE} to ${OUTFILE}"

MYSQL_PWD="$DB_PASSWORD" mysqldump \
    --host="$DB_HOST" \
    --port="$DB_PORT" \
    --user="$DB_USERNAME" \
    --single-transaction \
    --quick \
    --routines \
    --triggers \
    --add-drop-table \
    "$DB_DATABASE" | gzip > "$OUTFILE"

echo "[$(date)] Backup complete: $(du -h "$OUTFILE" | cut -f1)"

echo "[$(date)] Pruning backups older than ${RETENTION_DAYS} days"
find "$BACKUP_DIR" -name "ab-hub-*.sql.gz" -mtime "+${RETENTION_DAYS}" -delete

echo "[$(date)] Done."