#!/bin/bash

# Sharenet Database Migration Script
# This script handles database migrations in production environments

set -e

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

# Configuration
MIGRATIONS_DIR="backend/migrations"
BACKUP_DIR="backups"
DATABASE_URL="${DATABASE_URL:-}"
DRY_RUN="${DRY_RUN:-false}"
VERBOSE="${VERBOSE:-false}"

# Functions
log_info() {
    echo -e "${BLUE}[INFO]${NC} $1"
}

log_success() {
    echo -e "${GREEN}[SUCCESS]${NC} $1"
}

log_warning() {
    echo -e "${YELLOW}[WARNING]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

show_help() {
    cat << EOF
Sharenet Database Migration Script

Usage: $0 [COMMAND] [OPTIONS]

Commands:
    run         Run pending migrations
    status      Show migration status
    create      Create a new migration file
    revert      Revert the last migration
    backup      Create a backup before migration
    restore     Restore from backup

Options:
    --dry-run   Show what would be done without executing
    --verbose   Show detailed output
    --help      Show this help message

Environment Variables:
    DATABASE_URL    Database connection string
    DRY_RUN         Set to 'true' for dry run mode
    VERBOSE         Set to 'true' for verbose output

Examples:
    $0 run                    # Run pending migrations
    $0 status                 # Show migration status
    $0 create add_user_table  # Create new migration
    DRY_RUN=true $0 run       # Dry run migrations
EOF
}

check_dependencies() {
    log_info "Checking dependencies..."
    
    if ! command -v sqlx &> /dev/null; then
        log_error "sqlx CLI not found. Install with: cargo install sqlx-cli"
        exit 1
    fi
    
    if [ -z "$DATABASE_URL" ]; then
        log_error "DATABASE_URL environment variable is required"
        exit 1
    fi
    
    log_success "Dependencies check passed"
}

check_connection() {
    log_info "Testing database connection..."
    
    # Check if we're running in Docker environment
    if [ -f /.dockerenv ] || [ -f /proc/1/cgroup ] && grep -q docker /proc/1/cgroup; then
        log_info "Detected Docker environment"
        # In Docker, use the service name as host
        if [[ "$DATABASE_URL" == *"localhost"* ]] || [[ "$DATABASE_URL" == *"127.0.0.1"* ]]; then
            log_info "Updating DATABASE_URL for Docker environment"
            export DATABASE_URL=$(echo "$DATABASE_URL" | sed 's/localhost/postgres/g' | sed 's/127.0.0.1/postgres/g')
        fi
    fi
    
    if ! sqlx database create --database-url "$DATABASE_URL" 2>/dev/null; then
        log_warning "Database might already exist, continuing..."
    fi
    
    if ! sqlx migrate info --database-url "$DATABASE_URL" >/dev/null 2>&1; then
        log_error "Cannot connect to database. Check DATABASE_URL and network connectivity"
        log_error "DATABASE_URL: $DATABASE_URL"
        exit 1
    fi
    
    log_success "Database connection successful"
}

run_migrations() {
    log_info "Running database migrations..."
    
    if [ "$DRY_RUN" = "true" ]; then
        log_warning "DRY RUN MODE - No changes will be made"
        sqlx migrate info --database-url "$DATABASE_URL"
        return
    fi
    
    # Show current status
    sqlx migrate info --database-url "$DATABASE_URL"
    
    # Run migrations
    if sqlx migrate run --database-url "$DATABASE_URL"; then
        log_success "Migrations completed successfully"
        
        # Show final status
        log_info "Final migration status:"
        sqlx migrate info --database-url "$DATABASE_URL"
    else
        log_error "Migration failed"
        exit 1
    fi
}

show_status() {
    log_info "Migration status:"
    sqlx migrate info --database-url "$DATABASE_URL"
}

create_migration() {
    if [ $# -eq 0 ]; then
        log_error "Migration name is required"
        echo "Usage: $0 create <migration_name>"
        exit 1
    fi
    
    local migration_name="$1"
    log_info "Creating migration: $migration_name"
    
    if [ "$DRY_RUN" = "true" ]; then
        log_warning "DRY RUN MODE - Migration file would be created"
        echo "sqlx migrate add $migration_name"
        return
    fi
    
    if sqlx migrate add --database-url "$DATABASE_URL" "$migration_name"; then
        log_success "Migration file created successfully"
        log_info "Edit the generated file in $MIGRATIONS_DIR/"
    else
        log_error "Failed to create migration file"
        exit 1
    fi
}

revert_migration() {
    log_warning "Reverting last migration..."
    
    if [ "$DRY_RUN" = "true" ]; then
        log_warning "DRY RUN MODE - No changes will be made"
        return
    fi
    
    if sqlx migrate revert --database-url "$DATABASE_URL"; then
        log_success "Migration reverted successfully"
    else
        log_error "Failed to revert migration"
        exit 1
    fi
}

backup_database() {
    # Create backup directory if it doesn't exist
    mkdir -p "$BACKUP_DIR"
    
    local backup_file="$BACKUP_DIR/backup_$(date +%Y%m%d_%H%M%S).sql"
    log_info "Creating database backup: $backup_file"
    
    if [ "$DRY_RUN" = "true" ]; then
        log_warning "DRY RUN MODE - Backup would be created"
        return
    fi
    
    # Extract connection details from DATABASE_URL
    local db_host=$(echo "$DATABASE_URL" | sed -n 's/.*@\([^:]*\).*/\1/p')
    local db_port=$(echo "$DATABASE_URL" | sed -n 's/.*:\([0-9]*\)\/.*/\1/p')
    local db_name=$(echo "$DATABASE_URL" | sed -n 's/.*\/\([^?]*\).*/\1/p')
    local db_user=$(echo "$DATABASE_URL" | sed -n 's/.*:\/\/\([^:]*\):.*/\1/p')
    local db_pass=$(echo "$DATABASE_URL" | sed -n 's/.*:\/\/[^:]*:\([^@]*\)@.*/\1/p')
    
    # Set PGPASSWORD for pg_dump
    export PGPASSWORD="$db_pass"
    
    if pg_dump -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" > "$backup_file"; then
        log_success "Backup created: $backup_file"
    else
        log_error "Backup failed"
        exit 1
    fi
}

restore_database() {
    if [ $# -eq 0 ]; then
        log_error "Backup file is required"
        echo "Usage: $0 restore <backup_file>"
        exit 1
    fi
    
    local backup_file="$1"
    
    # If backup file doesn't have a path, assume it's in BACKUP_DIR
    if [[ "$backup_file" != */* ]]; then
        backup_file="$BACKUP_DIR/$backup_file"
    fi
    
    if [ ! -f "$backup_file" ]; then
        log_error "Backup file not found: $backup_file"
        exit 1
    fi
    
    log_warning "Restoring database from: $backup_file"
    log_warning "This will overwrite the current database!"
    
    if [ "$DRY_RUN" = "true" ]; then
        log_warning "DRY RUN MODE - No changes will be made"
        return
    fi
    
    read -p "Are you sure you want to continue? (y/N): " -n 1 -r
    echo
    if [[ ! $REPLY =~ ^[Yy]$ ]]; then
        log_info "Restore cancelled"
        exit 0
    fi
    
    # Extract connection details from DATABASE_URL
    local db_host=$(echo "$DATABASE_URL" | sed -n 's/.*@\([^:]*\).*/\1/p')
    local db_port=$(echo "$DATABASE_URL" | sed -n 's/.*:\([0-9]*\)\/.*/\1/p')
    local db_name=$(echo "$DATABASE_URL" | sed -n 's/.*\/\([^?]*\).*/\1/p')
    local db_user=$(echo "$DATABASE_URL" | sed -n 's/.*:\/\/\([^:]*\):.*/\1/p')
    local db_pass=$(echo "$DATABASE_URL" | sed -n 's/.*:\/\/[^:]*:\([^@]*\)@.*/\1/p')
    
    # Set PGPASSWORD for psql
    export PGPASSWORD="$db_pass"
    
    if psql -h "$db_host" -p "$db_port" -U "$db_user" -d "$db_name" < "$backup_file"; then
        log_success "Database restored successfully"
    else
        log_error "Restore failed"
        exit 1
    fi
}

# Main script logic
main() {
    local command="${1:-}"
    
    case "$command" in
        run)
            check_dependencies
            check_connection
            run_migrations
            ;;
        status)
            check_dependencies
            check_connection
            show_status
            ;;
        create)
            check_dependencies
            create_migration "${@:2}"
            ;;
        revert)
            check_dependencies
            check_connection
            revert_migration
            ;;
        backup)
            check_dependencies
            check_connection
            backup_database
            ;;
        restore)
            check_dependencies
            check_connection
            restore_database "${@:2}"
            ;;
        help|--help|-h)
            show_help
            ;;
        "")
            log_error "No command provided"
            show_help
            exit 1
            ;;
        *)
            log_error "Unknown command: $command"
            show_help
            exit 1
            ;;
    esac
}

# Run main function with all arguments
main "$@"