#!/bin/bash
# =============================================================
#  scripts/backup_db.sh
#
#  Dumps the STH NRMS database to a timestamped, gzip-compressed
#  file and prunes backups older than $RETENTION_DAYS.
#
#  SETUP (Hostinger hPanel):
#    1. Upload this script anywhere outside public_html (so it's
#       not web-servable), e.g. /home/u955643416/backups/backup_db.sh
#    2. chmod +x backup_db.sh
#    3. hPanel -> Advanced -> Cron Jobs -> add a daily job:
#         0 2 * * *  /bin/bash /home/u955643416/backups/backup_db.sh
#       (runs at 02:00 server time)
#    4. Confirm BACKUP_DIR below is writable and has enough quota —
#       Hostinger shared hosting has a disk quota; monitor it.
#
#  This reads DB credentials from .env (the same file the app uses),
#  so it stays in sync automatically if the password is ever rotated.
# =============================================================
set -euo pipefail

# ── Config ───────────────────────────────────────────────────
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"   # adjust if you move this script elsewhere
ENV_FILE="$PROJECT_ROOT/.env"
BACKUP_DIR="${BACKUP_DIR:-$PROJECT_ROOT/../backups}"   # keep OUTSIDE public_html
RETENTION_DAYS="${RETENTION_DAYS:-14}"

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

# ── Load DB_* vars from .env without exporting the whole file ──
DB_HOST=$(grep -E '^DB_HOST=' "$ENV_FILE" | head -1 | cut -d= -f2-)
DB_USER=$(grep -E '^DB_USER=' "$ENV_FILE" | head -1 | cut -d= -f2-)
DB_PASS=$(grep -E '^DB_PASS=' "$ENV_FILE" | head -1 | cut -d= -f2-)
DB_NAME=$(grep -E '^DB_NAME=' "$ENV_FILE" | head -1 | cut -d= -f2-)

if [ -z "$DB_NAME" ]; then
    echo "backup_db.sh: DB_NAME missing from .env" >&2
    exit 1
fi

mkdir -p "$BACKUP_DIR"

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
OUT_FILE="$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.sql.gz"

echo "backup_db.sh: dumping $DB_NAME -> $OUT_FILE"

mysqldump \
    --host="$DB_HOST" \
    --user="$DB_USER" \
    --password="$DB_PASS" \
    --single-transaction \
    --routines \
    --triggers \
    --quick \
    "$DB_NAME" | gzip > "$OUT_FILE"

echo "backup_db.sh: done ($(du -h "$OUT_FILE" | cut -f1))"

# ── Prune old backups ────────────────────────────────────────
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +"$RETENTION_DAYS" -print -delete

echo "backup_db.sh: pruned backups older than $RETENTION_DAYS days"