#!/bin/bash
#
# PostgreSQL Database Backup Script with Rotation
# Backs up database and maintains 7 days of history
#

set -e  # Exit on error

# Configuration
BACKUP_DIR="/media/pts/Website/Church_HOP_MusicData/backups"
DB_NAME="church_songlyric"
DB_USER="songlyric_user"
DB_HOST="192.168.10.130"
DB_PORT="5432"
RETENTION_DAYS=7
TIMESTAMP=$(date +"%Y%m%d_%H%M%S")
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_${TIMESTAMP}.sql.gz"
LOG_FILE="$BACKUP_DIR/backup.log"

# Colors for output
GREEN='\033[0;32m'
RED='\033[0;31m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# Logging function
log() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

# Create backup directory if it doesn't exist
mkdir -p "$BACKUP_DIR"

log "=========================================="
log "Starting database backup"
log "Database: $DB_NAME"
log "Timestamp: $TIMESTAMP"

# Check if PostgreSQL client is installed
if ! command -v pg_dump &> /dev/null; then
    log "ERROR: pg_dump not found. Install postgresql-client"
    exit 1
fi

# Check database connectivity
if ! PGPASSWORD="$POSTGRES_PASSWORD" psql -h "$DB_HOST" -U "$DB_USER" -d "$DB_NAME" -c "\q" 2>/dev/null; then
    log "WARNING: Cannot connect to database. Check credentials in .env"
fi

# Perform backup
log "Creating backup..."
if PGPASSWORD="$POSTGRES_PASSWORD" pg_dump \
    -h "$DB_HOST" \
    -U "$DB_USER" \
    -d "$DB_NAME" \
    --verbose \
    --format=plain \
    --no-owner \
    --no-privileges \
    2>> "$LOG_FILE" | gzip > "$BACKUP_FILE"; then
    
    BACKUP_SIZE=$(du -h "$BACKUP_FILE" | cut -f1)
    log "✓ Backup created successfully: $BACKUP_FILE"
    log "  Size: $BACKUP_SIZE"
else
    log "✗ Backup failed"
    exit 1
fi

# Verify backup integrity
log "Verifying backup integrity..."
if gzip -t "$BACKUP_FILE"; then
    log "✓ Backup file integrity verified"
else
    log "✗ Backup file is corrupted"
    exit 1
fi

# Clean up old backups
log "Cleaning up old backups (keeping last $RETENTION_DAYS days)..."
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -type f -mtime +$RETENTION_DAYS -delete
OLD_COUNT=$(find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -type f | wc -l)
log "✓ Retained $OLD_COUNT backup file(s)"

# Create a 'latest' symlink
ln -sf "$BACKUP_FILE" "$BACKUP_DIR/${DB_NAME}_latest.sql.gz"
log "✓ Updated latest backup symlink"

# Display backup summary
echo ""
echo -e "${GREEN}=========================================="
echo -e "BACKUP COMPLETED SUCCESSFULLY"
echo -e "==========================================${NC}"
echo "Backup file: $BACKUP_FILE"
echo "Size: $BACKUP_SIZE"
echo "Retention: $RETENTION_DAYS days"
echo "Total backups: $OLD_COUNT"
echo ""

# Optional: Upload to cloud storage (uncomment and configure)
# log "Uploading to cloud storage..."
# aws s3 cp "$BACKUP_FILE" "s3://your-bucket/backups/" || log "Cloud upload failed"

log "Backup process completed"
log "=========================================="

exit 0