#!/usr/bin/env bash
# Postgres secondary backup script — run via cron on the EC2 instance.
#
# As of 2026-09-08 both staging and production run on managed AWS RDS, which
# already provides daily automated backups and point-in-time recovery
# (7-day retention) natively — see docs/database-architecture.md. This
# script is now a belt-and-suspenders SECONDARY backup, not the primary
# mechanism: it exists to diversify failure modes RDS's own backups don't
# cover (e.g. accidental instance/snapshot deletion, an account-level
# incident). Runs weekly, not daily, since RDS already covers day-to-day
# recovery needs.
#
# Two-tier backup strategy:
#   1. Local dump  → /opt/backups/musehub/  (fast restore, 14-day rotation)
#   2. R2 upload   → r2://BACKUP_R2_BUCKET/musehub-db/ (off-disk, long retention)
#
# Connects via DATABASE_URL (works against RDS or self-hosted Postgres
# identically — it's just a connection string) rather than `docker exec`
# into a specific container, since there's no longer a local Postgres
# container to exec into on either environment.
#
# R2 upload requires:
#   - rclone installed: sudo apt-get install rclone
#   - rclone configured with an R2 remote named "r2":
#       rclone config  (add remote → S3-compatible → Cloudflare R2)
#   - BACKUP_R2_BUCKET set in /opt/musehub/.env, e.g.:
#       BACKUP_R2_BUCKET=musehub-backups
#   If BACKUP_R2_BUCKET is unset or rclone is not installed, the script
#   continues with local-only backup and emits a warning.
#
# Install (on EC2):
#   sudo mkdir -p /opt/backups/musehub
#   sudo chown ubuntu:ubuntu /opt/backups/musehub
#   chmod +x /opt/musehub/deploy/backup.sh
#   crontab -e
#   # Add this line (runs weekly, Sunday 3 AM):
#   0 3 * * 0 /opt/musehub/deploy/backup.sh >> /var/log/musehub-backup.log 2>&1

set -euo pipefail

APP_DIR="/opt/musehub"
BACKUP_DIR="/opt/backups/musehub"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/musehub_${TIMESTAMP}.sql.gz"
RETAIN_DAYS=14

mkdir -p "$BACKUP_DIR"

echo "[$(date)] Starting backup..."

# DATABASE_URL is written by deploy/secrets.sh (SSM-sourced). psql/pg_dump
# don't understand SQLAlchemy's "+asyncpg" driver suffix -- strip it.
DATABASE_URL=$(grep '^DATABASE_URL=' "$APP_DIR/.env" | cut -d'=' -f2- | sed 's/postgresql+asyncpg/postgresql/')
BACKUP_R2_BUCKET=$(grep '^BACKUP_R2_BUCKET=' "$APP_DIR/.env" 2>/dev/null | cut -d'=' -f2- || true)

sudo docker run --rm postgres:16 pg_dump "$DATABASE_URL" | gzip > "$BACKUP_FILE"

echo "[$(date)] Backup written: $BACKUP_FILE ($(du -sh "$BACKUP_FILE" | cut -f1))"

# ── Off-disk: sync to Cloudflare R2 ──────────────────────────────────────────
# Keeps backups on a separate storage medium — survives disk failure on the
# EC2 instance.  rclone copy is idempotent (skips already-uploaded files).
if [[ -n "${BACKUP_R2_BUCKET:-}" ]] && command -v rclone &>/dev/null; then
    echo "[$(date)] Syncing backup to R2 bucket: ${BACKUP_R2_BUCKET}..."
    rclone copy "$BACKUP_FILE" "r2:${BACKUP_R2_BUCKET}/musehub-db/" \
        --s3-chunk-size=128M \
        --s3-upload-concurrency=4 \
        --stats=30s
    echo "[$(date)] R2 upload complete."

    # Remove R2 copies older than 90 days (long-term retention).
    rclone delete "r2:${BACKUP_R2_BUCKET}/musehub-db/" \
        --min-age=90d \
        --include "musehub_*.sql.gz" || true
    echo "[$(date)] R2 old-backup rotation complete."
else
    echo "[$(date)] WARNING: BACKUP_R2_BUCKET not set or rclone not installed — local-only backup."
    echo "[$(date)]   To enable off-disk backups: install rclone, configure an R2 remote,"
    echo "[$(date)]   and set BACKUP_R2_BUCKET=<your-bucket-name> in $APP_DIR/.env"
fi

echo "[$(date)] Removing local backups older than $RETAIN_DAYS days..."
find "$BACKUP_DIR" -name "musehub_*.sql.gz" -mtime "+$RETAIN_DAYS" -delete

echo "[$(date)] Backup complete. Local files kept:"
ls -lh "$BACKUP_DIR"