sharenet/scripts/validate_migrations.sh

#!/bin/bash

# Sharenet Migration Validation Script
# This script validates that all migration files referenced in the database exist in the filesystem

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"
DATABASE_URL="${DATABASE_URL:-}"
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 Migration Validation Script

Usage: $0 [OPTIONS]

Options:
    --database-url URL    Database connection string
    --verbose            Show detailed output
    --help               Show this help message

Environment Variables:
    DATABASE_URL         Database connection string
    VERBOSE              Set to 'true' for verbose output

Examples:
    $0                                    # Validate with default database
    $0 --database-url postgres://...      # Validate with specific database
    VERBOSE=true $0                       # Show detailed output
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 [ ! -d "$MIGRATIONS_DIR" ]; then
        log_error "Migrations directory not found: $MIGRATIONS_DIR"
        exit 1
    fi
    
    log_success "Dependencies check passed"
}

check_database_connection() {
    log_info "Testing database connection..."
    
    if [ -z "$DATABASE_URL" ]; then
        log_error "DATABASE_URL environment variable is required"
        exit 1
    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"
}

validate_migration_files() {
    log_info "Validating migration files..."
    
    local has_errors=false
    local missing_files=()
    local extra_files=()
    
    # Get list of applied migrations from database
    local applied_migrations=$(sqlx migrate info --database-url "$DATABASE_URL" | grep -E '^[0-9]{14}' | awk '{print $1}' || true)
    
    if [ "$VERBOSE" = "true" ]; then
        log_info "Applied migrations in database:"
        echo "$applied_migrations" | while read migration; do
            if [ -n "$migration" ]; then
                echo "  - $migration"
            fi
        done
    fi
    
    # Check that all applied migrations exist as files
    while IFS= read -r migration; do
        if [ -n "$migration" ]; then
            local migration_file=$(find "$MIGRATIONS_DIR" -name "${migration}_*.sql" -print -quit)
            if [ -z "$migration_file" ]; then
                log_error "Missing migration file for applied migration: $migration"
                missing_files+=("$migration")
                has_errors=true
            elif [ "$VERBOSE" = "true" ]; then
                log_success "Found migration file: $migration_file"
            fi
        fi
    done <<< "$applied_migrations"
    
    # Check for migration files that don't match applied migrations
    local migration_files=$(find "$MIGRATIONS_DIR" -name "*.sql" -exec basename {} \; | sort)
    
    if [ "$VERBOSE" = "true" ]; then
        log_info "Migration files in filesystem:"
        echo "$migration_files" | while read file; do
            if [ -n "$file" ]; then
                echo "  - $file"
            fi
        done
    fi
    
    # Validate migration file naming convention
    while IFS= read -r file; do
        if [ -n "$file" ]; then
            if [[ ! "$file" =~ ^[0-9]{14}_.*\.sql$ ]]; then
                log_error "Invalid migration file naming convention: $file"
                log_error "Expected format: YYYYMMDDHHMMSS_description.sql"
                has_errors=true
            fi
        fi
    done <<< "$migration_files"
    
    # Check for duplicate migration timestamps
    local timestamps=$(find "$MIGRATIONS_DIR" -name "*.sql" -exec basename {} \; | sed 's/^\([0-9]\{14\}\)_.*\.sql$/\1/' | sort)
    local duplicate_timestamps=$(echo "$timestamps" | uniq -d)
    
    if [ -n "$duplicate_timestamps" ]; then
        log_error "Duplicate migration timestamps found:"
        echo "$duplicate_timestamps" | while read timestamp; do
            if [ -n "$timestamp" ]; then
                log_error "  - $timestamp"
                find "$MIGRATIONS_DIR" -name "${timestamp}_*.sql" -exec basename {} \;
            fi
        done
        has_errors=true
    fi
    
    # Summary
    if [ "$has_errors" = true ]; then
        log_error "Migration validation failed!"
        if [ ${#missing_files[@]} -gt 0 ]; then
            log_error "Missing migration files:"
            for file in "${missing_files[@]}"; do
                log_error "  - $file"
            done
        fi
        exit 1
    else
        log_success "All migration files are valid and consistent"
    fi
}

validate_migration_content() {
    log_info "Validating migration file content..."
    
    local has_errors=false
    
    # Check each migration file for basic SQL syntax
    while IFS= read -r file; do
        if [ -n "$file" ]; then
            local filepath="$MIGRATIONS_DIR/$file"
            
            # Basic SQL validation (check for common issues)
            if grep -q "CREATE TABLE" "$filepath"; then
                if ! grep -q ";" "$filepath"; then
                    log_warning "Migration file $file may be missing semicolons"
                fi
            fi
            
            # Check for potential issues
            if grep -q "DROP TABLE" "$filepath"; then
                log_warning "Migration file $file contains DROP TABLE - ensure this is intentional"
            fi
            
            if grep -q "DROP DATABASE" "$filepath"; then
                log_error "Migration file $file contains DROP DATABASE - this is not allowed"
                has_errors=true
            fi
            
        fi
    done < <(find "$MIGRATIONS_DIR" -name "*.sql" -exec basename {} \; | sort)
    
    if [ "$has_errors" = true ]; then
        log_error "Migration content validation failed!"
        exit 1
    else
        log_success "Migration content validation passed"
    fi
}

# Main validation function
main() {
    local database_url=""
    
    # Parse command line arguments
    while [[ $# -gt 0 ]]; do
        case $1 in
            --database-url)
                database_url="$2"
                shift 2
                ;;
            --verbose)
                VERBOSE="true"
                shift
                ;;
            --help|-h)
                show_help
                exit 0
                ;;
            *)
                log_error "Unknown option: $1"
                show_help
                exit 1
                ;;
        esac
    done
    
    # Set database URL if provided
    if [ -n "$database_url" ]; then
        export DATABASE_URL="$database_url"
    fi
    
    log_info "Starting migration validation..."
    
    check_dependencies
    check_database_connection
    validate_migration_files
    validate_migration_content
    
    log_success "Migration validation completed successfully"
}

# Handle interrupts gracefully
trap 'log_error "Validation interrupted"; exit 1' INT TERM

# Run main function
main "$@"