#!/bin/sh
set -e

# Error handling function
error_exit() {
    echo "ERROR: $1" >&2
    exit 1
}

# Cleanup function
cleanup() {
    if [ -f "/tmp/prod_dump.sql" ]; then
        echo "Cleaning up temporary files..."
        rm -f /tmp/prod_dump.sql
    fi
}

# Set trap for cleanup on exit
trap cleanup EXIT

echo "Starting production database sync..."

# Validate required environment variables
[ -z "$SBA_DB_USER" ] && error_exit "SBA_DB_USER environment variable not set"
[ -z "$SBA_DATABASE" ] && error_exit "SBA_DATABASE environment variable not set"

echo "Production server: akamai"
echo "Target database: ${SBA_DATABASE}"
echo "Target user: ${SBA_DB_USER}"

# Install necessary packages
echo "Installing required packages..."
apk add --no-cache openssh-client postgresql-client || error_exit "Failed to install required packages"

# Test SSH connection first
echo "Testing SSH connection to production server..."
ssh -o StrictHostKeyChecking=no -o ConnectTimeout=10 akamai "echo 'SSH connection successful'" || error_exit "Failed to connect to production server"

# Test local database connection
echo "Testing local database connection..."
pg_isready -h sba_postgres -U ${SBA_DB_USER} -d ${SBA_DATABASE} || error_exit "Local database is not ready"

# Create dump from production server first (safer than direct pipe)
# Using --clean to drop existing objects before recreating them
echo "Creating dump from production server..."
ssh -o StrictHostKeyChecking=no akamai \
    "cd container-data/sba-database && source .env && docker exec -e PGPASSWORD=\$SBA_DB_USER_PASSWORD sba_postgres pg_dump --clean -U \$SBA_DB_USER -d \$SBA_DATABASE" \
    > /tmp/prod_dump.sql || error_exit "Failed to create production database dump"

# Verify dump file is not empty
[ ! -s "/tmp/prod_dump.sql" ] && error_exit "Production dump file is empty"

echo "Dump created successfully ($(wc -l < /tmp/prod_dump.sql) lines)"

# Restore to local database
echo "Restoring to local database..."
PGPASSWORD=${SBA_DB_USER_PASSWORD} psql -h sba_postgres -U ${SBA_DB_USER} -d ${SBA_DATABASE} -f /tmp/prod_dump.sql || error_exit "Failed to restore database dump"

echo "Database sync completed successfully!"