#!/bin/sh
# Daily pg_dump backup of the production Supabase Postgres DB, run via NAS
# crontab (03:00).
#
# Dumps via a throwaway postgres:17-alpine container - matches the Supabase
# server's Postgres 17.6, and this NAS has no postgresql-client in its host
# PATH (same reason Prisma/Node tooling here also runs in a throwaway
# container - see CLAUDE.md). Uses DIRECT_URL (the non-pooled port-5432
# connection); pg_dump against the pgbouncer-fronted DATABASE_URL is
# unreliable. Custom format (-Fc): compressed, restorable selectively via
# `pg_restore -j <n>` for parallelism.
#
# Retention: 30 days, enforced by deleting older *.dump files after each run.
#
# Every run is logged to docker/backup.log (this NAS has no local mail
# delivery set up for cron failures - see docker/run-cron.sh for the same
# issue/fix on the app-cron side).
set -eu

SCRIPT_DIR="$(cd "$(dirname "$0")" && pwd)"
ENV_FILE="$SCRIPT_DIR/.env"
LOG_FILE="$SCRIPT_DIR/backup.log"
BACKUP_DIR="/srv/dev-disk-by-uuid-ad295d7f-a870-4f70-9de2-dfded1fabf7f/BACKUPALL/pawfeed"
RETENTION_DAYS=30
PG_IMAGE="postgres:17-alpine"

log() {
  printf '%s [backup-db] %s\n' "$(date -Iseconds)" "$1" >> "$LOG_FILE"
}

if [ ! -f "$ENV_FILE" ]; then
  log "ERROR: $ENV_FILE not found"
  exit 1
fi

DIRECT_URL="$(grep -E '^DIRECT_URL=' "$ENV_FILE" | head -n1 | cut -d= -f2- | sed -e 's/^"//' -e 's/"$//')"
if [ -z "$DIRECT_URL" ]; then
  log "ERROR: DIRECT_URL not set in $ENV_FILE"
  exit 1
fi

mkdir -p "$BACKUP_DIR"

STAMP="$(date +%Y-%m-%d_%H-%M-%S)"
FILE_NAME="pawfeed-$STAMP.dump"
PART_NAME="$FILE_NAME.part"

if docker run --rm \
  --user "$(id -u):$(id -g)" \
  -e DB_URL="$DIRECT_URL" \
  -e FILE_NAME="$PART_NAME" \
  -v "$BACKUP_DIR:/backup" \
  "$PG_IMAGE" \
  sh -c 'pg_dump "$DB_URL" -Fc -f "/backup/$FILE_NAME"' \
  >> "$LOG_FILE" 2>&1
then
  mv "$BACKUP_DIR/$PART_NAME" "$BACKUP_DIR/$FILE_NAME"
  SIZE="$(du -h "$BACKUP_DIR/$FILE_NAME" | cut -f1)"
  log "OK: $FILE_NAME ($SIZE)"
else
  rm -f "$BACKUP_DIR/$PART_NAME"
  log "ERROR: pg_dump failed, see output above"
  exit 1
fi

DELETED=0
for old in $(find "$BACKUP_DIR" -name 'pawfeed-*.dump' -mtime "+$RETENTION_DAYS"); do
  rm -f "$old"
  DELETED=$((DELETED + 1))
done
log "retention: removed $DELETED backup(s) older than ${RETENTION_DAYS}d"