#!/bin/bash
# SneakerPicks nightly database backup
# Creates a compressed pg_dump and keeps last 7 days
# Cron: daily at 00:45 (before nightly sync at 01:00)

set -euo pipefail

BACKUP_DIR="/var/www/sneakerpicks/backups"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/sneakerpicks_${TIMESTAMP}.sql.gz"
KEEP_DAYS=7

mkdir -p "$BACKUP_DIR"

echo "=== SneakerPicks DB Backup at $(date) ==="

# Dump via docker exec into supabase-db container
echo "[1/3] Creating backup..."
docker exec supabase-db pg_dump -U supabase_admin -d sneakerpicks \
    --no-owner --no-privileges \
    --exclude-table='products_backup_*' \
    --exclude-table='product_offers_backup_*' \
    | gzip > "$BACKUP_FILE"

FILESIZE=$(stat -c%s "$BACKUP_FILE" 2>/dev/null || echo "0")
echo "  Created: $BACKUP_FILE ($(numfmt --to=iec $FILESIZE))"

# Verify backup is valid (not empty)
if [ "$FILESIZE" -lt 1000 ]; then
    echo "ERROR: Backup file suspiciously small ($FILESIZE bytes). Check for errors."
    exit 1
fi

# Cleanup old backups
echo "[2/3] Cleaning up backups older than ${KEEP_DAYS} days..."
DELETED=$(find "$BACKUP_DIR" -name "sneakerpicks_*.sql.gz" -mtime +${KEEP_DAYS} -delete -print | wc -l)
echo "  Deleted: $DELETED old backups"

# Show remaining backups
echo "[3/3] Current backups:"
ls -lh "$BACKUP_DIR"/sneakerpicks_*.sql.gz 2>/dev/null | tail -10

TOTAL_SIZE=$(du -sh "$BACKUP_DIR" 2>/dev/null | cut -f1)
echo "  Total backup size: $TOTAL_SIZE"
echo "=== Backup complete at $(date) ==="