ferrero-opentext/Python-Version/database/restore.sh

#!/bin/bash
#
# PostgreSQL Restore Script for Ferrero Automation
# Restores database from pg_dump or pg_basebackup
#
# Usage:
#   ./database/restore.sh <backup-file>
#   ./database/restore.sh backups/dumps/ferrero_tracking_2025-11-11_02-00.sql.gz
#

set -e  # Exit on error

# Configuration
DB_NAME="ferrero_tracking"
DB_USER="ferrero_user"
DB_PASSWORD="ferrero_pass_2025"
DOCKER_CONTAINER="ferrero-tracking-db"

# Directories
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
PROJECT_DIR="$(dirname "$SCRIPT_DIR")"
LOG_DIR="$PROJECT_DIR/logs"

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

# Logging
log() {
    echo -e "${GREEN}[$(date +'%Y-%m-%d %H:%M:%S')]${NC} $1"
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_DIR/restore.log"
}

log_error() {
    echo -e "${RED}[$(date +'%Y-%m-%d %H:%M:%S')] ERROR:${NC} $1" >&2
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] ERROR: $1" >> "$LOG_DIR/restore.log"
}

log_warning() {
    echo -e "${YELLOW}[$(date +'%Y-%m-%d %H:%M:%S')] WARNING:${NC} $1"
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] WARNING: $1" >> "$LOG_DIR/restore.log"
}

# Create safety backup before restore
create_safety_backup() {
    local safety_dir="$PROJECT_DIR/backups/safety"
    mkdir -p "$safety_dir"

    local safety_file="$safety_dir/pre_restore_${DB_NAME}_$(date +%Y-%m-%d_%H-%M).sql.gz"

    log_warning "Creating safety backup before restore..."

    if docker exec "$DOCKER_CONTAINER" pg_dump -U "$DB_USER" "$DB_NAME" | gzip > "$safety_file"; then
        local size=$(du -h "$safety_file" | cut -f1)
        log "✓ Safety backup created: $safety_file ($size)"
        return 0
    else
        log_error "Failed to create safety backup"
        return 1
    fi
}

# Restore from SQL dump
restore_from_dump() {
    local backup_file="$1"

    log "Restoring from SQL dump: $backup_file"

    # Validate file exists
    if [ ! -f "$backup_file" ]; then
        log_error "Backup file not found: $backup_file"
        exit 1
    fi

    # Create safety backup
    if ! create_safety_backup; then
        log_error "Cannot proceed without safety backup"
        exit 1
    fi

    # Confirmation prompt
    echo ""
    echo -e "${YELLOW}⚠️  WARNING: This will REPLACE the current database!${NC}"
    echo "Backup file: $backup_file"
    echo "Database: $DB_NAME"
    echo ""
    read -p "Are you sure you want to continue? (yes/no): " confirm

    if [ "$confirm" != "yes" ]; then
        log "Restore cancelled by user"
        exit 0
    fi

    log "Dropping and recreating database..."

    # Drop and recreate database
    docker exec "$DOCKER_CONTAINER" psql -U "$DB_USER" -d postgres << EOF
DROP DATABASE IF EXISTS ${DB_NAME};
CREATE DATABASE ${DB_NAME} WITH OWNER = ${DB_USER};
EOF

    log "Database recreated. Restoring data..."

    # Restore from backup
    if gunzip -c "$backup_file" | docker exec -i "$DOCKER_CONTAINER" psql -U "$DB_USER" -d "$DB_NAME"; then
        log "✓ Database restored successfully from: $backup_file"

        # Verify restoration
        local row_count=$(docker exec "$DOCKER_CONTAINER" psql -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM master_assets;")
        log "Verification: master_assets table has $row_count rows"

        return 0
    else
        log_error "Restore failed"
        return 1
    fi
}

# List available backups
list_backups() {
    # Set DUMP_DIR and BASEBACKUP_DIR relative to script
    local DUMP_DIR="$PROJECT_DIR/backups/dumps"
    local BASEBACKUP_DIR="$PROJECT_DIR/backups/basebackups"

    echo ""
    echo "========================================="
    echo "Available Backups"
    echo "========================================="
    echo ""

    echo "Daily Backups (pg_dump):"
    if [ -d "$DUMP_DIR" ] && ls "$DUMP_DIR"/*.sql.gz 1> /dev/null 2>&1; then
        ls -lh "$DUMP_DIR"/*.sql.gz | awk '{print "  " $9 " (" $5 ", " $6 " " $7 ")"}'
    else
        echo "  No daily backups found in: $DUMP_DIR"
    fi

    echo ""
    echo "Weekly Backups (pg_basebackup):"
    if [ -d "$BASEBACKUP_DIR" ] && ls "$BASEBACKUP_DIR"/*.tar.gz 1> /dev/null 2>&1; then
        ls -lh "$BASEBACKUP_DIR"/*.tar.gz | awk '{print "  " $9 " (" $5 ", " $6 " " $7 ")"}'
    else
        echo "  No weekly backups found in: $BASEBACKUP_DIR"
    fi

    echo ""
}

# Display usage
usage() {
    echo "PostgreSQL Restore Script"
    echo ""
    echo "Usage:"
    echo "  $0 <backup-file>          Restore from specific backup"
    echo "  $0 --list                 List available backups"
    echo ""
    echo "Examples:"
    echo "  $0 backups/dumps/ferrero_tracking_2025-11-11_02-00.sql.gz"
    echo "  $0 --list"
    echo ""
    echo "Note: A safety backup will be created before restore"
    exit 1
}

# Main
main() {
    log "========================================="
    log "Ferrero Database Restore"
    log "========================================="

    # Parse arguments
    if [ $# -eq 0 ]; then
        usage
    fi

    case "$1" in
        --list)
            list_backups
            exit 0
            ;;
        --help|-h)
            usage
            ;;
        *)
            # Assume it's a backup file path
            if [[ "$1" == *.sql.gz ]]; then
                restore_from_dump "$1"
            elif [[ "$1" == *.tar.gz ]]; then
                log_error "pg_basebackup restore not yet implemented"
                log_error "Use SQL dump restore for now"
                exit 1
            else
                log_error "Unknown backup format: $1"
                log_error "Supported formats: .sql.gz"
                exit 1
            fi
            ;;
    esac

    log "========================================="
    log "Restore completed"
    log "========================================="
}

# Run main
main "$@"