#!/bin/bash
# Automated PostgreSQL backup — run this from a cPanel cron job.
#
# Cron setup (in cPanel > Cron Jobs):
#   Command: bash /home/YOURUSER/schoolly-multitenant/scripts/backup.sh
#   Schedule: daily, e.g. at 2:00 AM — minute=0 hour=2 every day
#
# Reads DB credentials from .env in the same folder as this script.
# Keeps the last 14 daily backups automatically; older ones are deleted.

set -e
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
cd "$SCRIPT_DIR"

# Load .env
if [ -f .env ]; then
  export $(grep -v '^#' .env | xargs)
fi

BACKUP_DIR="$SCRIPT_DIR/backups"
mkdir -p "$BACKUP_DIR"

# Pre-flight check: your database user needs BYPASSRLS to back up tables
# protected by Row-Level Security (every school-data table in this app).
# This is a ONE-TIME setup step — ask your hosting provider/support to run:
#   ALTER ROLE your_db_user WITH BYPASSRLS;
HAS_BYPASSRLS=$(PGPASSWORD="$DB_PASSWORD" psql -h "$DB_HOST" -p "${DB_PORT:-5432}" -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT rolbypassrls FROM pg_roles WHERE rolname = current_user;" 2>/dev/null | tr -d '[:space:]')
if [ "$HAS_BYPASSRLS" != "t" ]; then
  echo "❌ Backup cannot proceed: database user '$DB_USER' does not have BYPASSRLS."
  echo "   This app uses Row-Level Security to isolate each school's data, which"
  echo "   also blocks pg_dump from reading it unless the backup role can bypass it."
  echo ""
  echo "   Fix (one-time, ask your hosting provider/support to run this SQL):"
  echo "     ALTER ROLE $DB_USER WITH BYPASSRLS;"
  exit 1
fi

TIMESTAMP=$(date +%Y-%m-%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/schoolly_backup_${TIMESTAMP}.sql.gz"

echo "Backing up database '$DB_NAME' to $BACKUP_FILE ..."
PGPASSWORD="$DB_PASSWORD" pg_dump -h "$DB_HOST" -p "${DB_PORT:-5432}" -U "$DB_USER" -d "$DB_NAME" \
  --clean --if-exists --no-owner --no-privileges | gzip > "$BACKUP_FILE"

echo "✅ Backup complete: $BACKUP_FILE ($(du -h "$BACKUP_FILE" | cut -f1))"

# Keep only the last 14 backups
cd "$BACKUP_DIR"
ls -1t schoolly_backup_*.sql.gz 2>/dev/null | tail -n +15 | xargs -r rm --
echo "Backups retained: $(ls -1 schoolly_backup_*.sql.gz 2>/dev/null | wc -l)"