#!/usr/bin/env bash
# Daily MySQL backup for the VPS stack — see docs/PROJECT_PLAN.md §29 and
# §0B.22 (a database-only backup is not a complete NAS backup; pair this
# with backup-storage.sh, run on the Raspberry Pi, for a complete one).
#
# Runs `docker compose exec mysql mysqldump` against the running
# container (no separate DB credentials to manage on the host — reuses
# whatever docker-compose.yml already configured) and applies the
# 7-daily/4-weekly/6-monthly retention from lib/rotate-backups.sh.
#
# Usage: ./backup-database.sh [backup-dir]
# Typical cron entry (as root, from the repo root on the VPS):
#   0 2 * * * cd /opt/private-cloud-nas && infrastructure/scripts/backup-database.sh >> /var/log/nas-backup-database.log 2>&1
set -euo pipefail

script_dir="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
repo_root="$(cd "$script_dir/../.." && pwd)"
backup_dir="${1:-$repo_root/backups/database}"

source "$script_dir/lib/rotate-backups.sh"
source "$script_dir/lib/env.sh"

if [[ ! -f "$repo_root/.env" ]]; then
  echo "error: $repo_root/.env not found — run this from a real VPS deployment, not a dev checkout" >&2
  exit 1
fi

MYSQL_DATABASE="$(env_var "$repo_root/.env" MYSQL_DATABASE)"
MYSQL_USER="$(env_var "$repo_root/.env" MYSQL_USER)"
MYSQL_PASSWORD="$(env_var "$repo_root/.env" MYSQL_PASSWORD)"

mkdir -p "$backup_dir"
stamp="$(date +%Y%m%d-%H%M%S)"
out="$backup_dir/db-$stamp.sql.gz"
tmp_out="$out.partial"

echo "Backing up database '$MYSQL_DATABASE' to $out"

if ! docker compose -f "$repo_root/docker-compose.yml" exec -T mysql \
  mysqldump --single-transaction --routines --triggers --no-tablespaces \
  -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" "$MYSQL_DATABASE" | gzip > "$tmp_out"; then
  echo "error: mysqldump failed — leaving no partial backup behind" >&2
  rm -f "$tmp_out"
  exit 1
fi

# Sanity check: a truncated/empty dump is worse than no backup, because
# it looks like a successful run. gzip of an empty stream is still a few
# bytes of gzip header, so require a bit more than that.
if [[ ! -s "$tmp_out" ]] || [[ "$(wc -c < "$tmp_out")" -lt 100 ]]; then
  echo "error: backup output implausibly small — treating as a failed backup" >&2
  rm -f "$tmp_out"
  exit 1
fi

mv "$tmp_out" "$out"
echo "Wrote $out ($(du -h "$out" | cut -f1))"

rotate_backups "$backup_dir" "db-*.sql.gz" 7 4 6

echo "Done. $(find "$backup_dir" -maxdepth 1 -name 'db-*.sql.gz' -type f | wc -l | tr -d ' ') backup(s) retained."