#!/bin/bash

################################################################################
# MySQL/MariaDB File-Based Restore to SQL Dump
################################################################################
# Purpose: Convert restored MySQL/MariaDB data files to usable .sql dumps
# Use Case: Restore InnoDB databases from file-based backups (Acronis, etc.)
#
# Features:
# - Multi-control panel support (cPanel, Plesk, InterWorx, standalone)
# - Interactive guided workflow
# - Validates MySQL data directory structure
# - Starts second MySQL instance for safe extraction
# - Creates SQL dumps from restored files
# - Handles InnoDB system tablespace requirements
# - Optional force-recovery mode for corrupted databases
################################################################################

# Path resolution (modules/backup/script.sh → ../../)
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/../.." && pwd)"
source "$SCRIPT_DIR/lib/common-functions.sh"
source "$SCRIPT_DIR/lib/system-detect.sh"

# Root check
if [ "$EUID" -ne 0 ]; then
    echo ""
    print_error "PERMISSION DENIED: This script must be run as root"
    echo ""
    echo "Why root is required:"
    echo "  - Read access to live MySQL data directory (/var/lib/mysql)"
    echo "  - Create directories in /home (for temporary restore location)"
    echo "  - Change file ownership to mysql:mysql"
    echo "  - Start MySQL daemon (mysqld) process"
    echo "  - Access system configuration files"
    echo ""
    echo "To run this script:"
    echo "  sudo $0 $*"
    echo ""
    exit 1
fi

# Detect control panel for proper directory paths
# This sets SYS_USER_HOME_BASE which determines restore directory location
detect_control_panel || true

################################################################################
# GLOBAL VARIABLES
################################################################################

RESTORE_DIR=""
DATABASE_NAME=""
LIVE_DATADIR=""
TEMP_DATADIR=""
TICKET_NUMBER=""
FORCE_RECOVERY=""
MYSQL_VERSION=""
MYSQL_VARIANT=""  # mysql or mariadb
SECOND_INSTANCE_RUNNING=0  # Track if second instance is running

# PHASE 3: State tracking for menu loop and recovery escalation
RECOVERY_ATTEMPTS=0  # Count total dump attempts
TRIED_MODES=()  # Array of recovery modes that have been tried
DATADIR_CONFIRMED=0  # User confirmed live MySQL datadir?
RESTORE_CONFIRMED=0  # User confirmed restore location?
DATABASE_CONFIRMED=0  # User confirmed database selection?
CURRENT_STEP=0  # Which step user is currently on (1-5)

# Cleanup trap for interruption/exit
cleanup_on_exit() {
    if [ "$SECOND_INSTANCE_RUNNING" -eq 1 ] && [ -n "$TEMP_DATADIR" ]; then
        echo ""
        print_warning "Script interrupted - cleaning up second MySQL instance..."

        if [ -S "$TEMP_DATADIR/socket.mysql" ]; then
            # Graceful shutdown with validation
            mysqladmin -h localhost -S "$TEMP_DATADIR/socket.mysql" shutdown 2>/dev/null || true

            # Wait for socket to disappear (max 5 seconds in cleanup)
            local cleanup_wait=0
            while [ -S "$TEMP_DATADIR/socket.mysql" ] && [ "$cleanup_wait" -lt 5 ]; do
                sleep 1
                cleanup_wait=$((cleanup_wait + 1))
            done

            # Force cleanup if socket still exists
            if [ -S "$TEMP_DATADIR/socket.mysql" ]; then
                # Get PID and force kill
                if [ -f "$TEMP_DATADIR/mysql.pid" ]; then
                    kill -9 $(cat "$TEMP_DATADIR/mysql.pid" 2>/dev/null) 2>/dev/null || true
                fi
                rm -f "$TEMP_DATADIR/socket.mysql" "$TEMP_DATADIR/mysql.lock" 2>/dev/null || true
            fi

            # Clean up PID file if it still exists (BUG FIX: stale PID cleanup)
            rm -f "$TEMP_DATADIR/mysql.pid" 2>/dev/null || true

            # Clean up error log backups to prevent accumulation (BUG FIX: mysql.err.old cleanup)
            rm -f "$TEMP_DATADIR/mysql.err.old" 2>/dev/null || true

            print_success "Second instance cleaned up"
        fi
    fi
}

# Set trap for signals
trap cleanup_on_exit EXIT INT TERM

################################################################################
# DEPENDENCY CHECKING
################################################################################

# Verify all required binaries exist before proceeding
# Returns 1 if any critical dependency is missing
check_dependencies() {
    local missing_deps=0
    local missing_list=""

    # Critical binaries required for script operation
    local required_binaries=(
        "mysqld:MySQL server daemon (required to start second instance)"
        "mysql:MySQL client (required for database queries)"
        "mysqldump:MySQL backup tool (required to create SQL dump)"
        "mysqladmin:MySQL admin tool (required for shutdown)"
    )

    print_info "Verifying required dependencies..."

    for bin_info in "${required_binaries[@]}"; do
        local bin="${bin_info%:*}"
        local description="${bin_info#*:}"

        # Try to find the binary
        if ! command -v "$bin" &> /dev/null; then
            print_error "  Missing: $bin - $description"
            missing_deps=$((missing_deps + 1))
            missing_list="$missing_list  - $bin\n"
        else
            print_success "  Found: $bin"
        fi
    done

    if [ "$missing_deps" -gt 0 ]; then
        echo ""
        print_error "MISSING $missing_deps REQUIRED DEPENDENCY/IES"
        echo ""
        echo "Please install the following packages:"
        echo -e "$missing_list"
        echo ""
        echo "On CentOS/RHEL: yum install mysql mysql-server"
        echo "On Debian/Ubuntu: apt-get install mysql-client mysql-server"
        echo "On AlmaLinux: dnf install mysql mysql-server"
        return 1
    fi

    print_success "All required dependencies found"
    return 0
}

################################################################################
# PHASE 3 IMPROVEMENTS: Menu Loop & Auto-Escalation Strategy
################################################################################

# Issue #5: Track recovery mode attempts and suggest next mode
# Maintains history of failed modes and recommends intelligent escalation
track_recovery_attempt() {
    local current_mode="${1:-0}"

    RECOVERY_ATTEMPTS=$((RECOVERY_ATTEMPTS + 1))

    # Check if mode already attempted
    local mode_already_tried=0
    for tried_mode in "${TRIED_MODES[@]}"; do
        if [ "$tried_mode" -eq "$current_mode" ]; then
            mode_already_tried=1
            break
        fi
    done

    # Add to tried modes if not already there
    if [ "$mode_already_tried" -eq 0 ]; then
        TRIED_MODES+=("$current_mode")
    fi

    return 0
}

# Issue #5: Auto-escalate recovery mode based on attempt history
# Returns next mode to try, or 0 if all modes exhausted
get_next_recovery_mode() {
    local current_mode="${1:-0}"

    # Smart escalation path: 0 → 1 → 4 → 5 → 6
    # (skips 2, 3 as they're less effective intermediates)
    case $current_mode in
        0)
            echo "1"
            return 0
            ;;
        1)
            echo "4"
            return 0
            ;;
        4)
            echo "5"
            return 0
            ;;
        5)
            echo "6"
            return 0
            ;;
        6)
            echo "6"  # Stuck at max, return 6
            return 1  # Signal: cannot escalate further
            ;;
        *)
            echo "0"
            return 1
            ;;
    esac
}

# Issue #6: Display current recovery session state
# Shows what user has selected and current recovery progress
show_current_state() {
    echo ""
    echo "════════════════════════════════════════════════════════════════"
    print_banner "Current Session State"
    echo "════════════════════════════════════════════════════════════════"
    echo ""

    echo "Step 1: Live MySQL Data Directory"
    if [ -z "$LIVE_DATADIR" ]; then
        echo "  Status: Not set"
    else
        echo "  Status: ✓ Set"
        echo "  Value:  $LIVE_DATADIR"
    fi
    echo ""

    echo "Step 2: Restore Location"
    if [ -z "$TEMP_DATADIR" ]; then
        echo "  Status: Not set"
    else
        echo "  Status: ✓ Set"
        echo "  Value:  $TEMP_DATADIR"
    fi
    echo ""

    echo "Step 3: Database to Restore"
    if [ -z "$DATABASE_NAME" ]; then
        echo "  Status: Not set"
    else
        echo "  Status: ✓ Set"
        echo "  Value:  $DATABASE_NAME"
    fi
    echo ""

    echo "Step 4: Recovery Options"
    if [ -n "$TICKET_NUMBER" ]; then
        echo "  Ticket: $TICKET_NUMBER"
    fi
    echo "  Current recovery mode: ${FORCE_RECOVERY:-0}"

    if [ ${#TRIED_MODES[@]} -gt 0 ]; then
        echo "  Modes attempted: ${TRIED_MODES[*]}"
        echo "  Total attempts: $RECOVERY_ATTEMPTS"
    fi
    echo ""

    echo "════════════════════════════════════════════════════════════════"
    echo ""
    return 0
}

# Issue #6: Display interactive menu loop
# Allows user to navigate between steps and review state
show_step_menu() {
    echo ""
    echo "════════════════════════════════════════════════════════════════"
    print_banner "Restore Workflow Menu"
    echo "════════════════════════════════════════════════════════════════"
    echo ""

    echo "Completed steps:"
    [ -n "$LIVE_DATADIR" ] && echo "  [✓] Step 1: Live MySQL Directory detected"
    [ -n "$TEMP_DATADIR" ] && echo "  [✓] Step 2: Restore location configured"
    [ -n "$DATABASE_NAME" ] && echo "  [✓] Step 3: Database selected"

    echo ""
    echo "Choose action:"
    echo "  [1] Go to Step 1 (Detect live MySQL data directory)"
    echo "  [2] Go to Step 2 (Set restore data location)"
    echo "  [3] Go to Step 3 (Select database)"
    echo "  [4] Go to Step 4 (Configure restore options)"
    echo "  [5] Go to Step 5 (Create SQL dump)"
    echo "  [G] Guided process (walks through all steps automatically)"
    echo "  [C] Compare original vs recovered database"
    echo "  [R] Review current state"
    echo "  [0] Back to main menu"
    echo ""
    echo -n "Select action (1-5, G, C, R, 0): "
    return 0
}

# Guided Process Mode: Walks user through all 5 steps automatically
# Returns 0 on success, 1 if user cancels at any step
run_guided_process() {
    echo ""
    echo "════════════════════════════════════════════════════════════════"
    print_banner "GUIDED PROCESS - Automatic Workflow"
    echo "════════════════════════════════════════════════════════════════"
    echo ""
    echo "This will walk you through all 5 steps automatically."
    echo "You can cancel at any step by typing '0' when prompted."
    echo ""
    press_enter

    # Step 1
    print_banner "Step 1 of 5: Detect Live MySQL Data Directory"
    if ! step1_detect_datadir; then
        print_warning "Step 1 cancelled or failed. Returning to menu."
        return 1
    fi

    # Step 2
    print_banner "Step 2 of 5: Set Restore Data Location"
    if ! step2_set_restore_location; then
        print_warning "Step 2 cancelled or failed. Returning to menu."
        return 1
    fi

    # Step 3
    print_banner "Step 3 of 5: Select Database to Restore"
    if ! step3_select_database; then
        print_warning "Step 3 cancelled or failed. Returning to menu."
        return 1
    fi

    # Step 4
    print_banner "Step 4 of 5: Configure Restore Options"
    if ! step4_configure_options; then
        print_warning "Step 4 cancelled or failed. Returning to menu."
        return 1
    fi

    # Step 5
    print_banner "Step 5 of 5: Create SQL Dump"
    if ! step5_create_dump; then
        print_warning "Step 5 failed. Returning to menu to retry with different options."
        return 1
    fi

    print_success "GUIDED PROCESS COMPLETED SUCCESSFULLY!"
    echo ""
    press_enter
    return 0
}

# Issue #6: Validate if workflow can proceed to given step
# Ensures all prerequisite steps are complete
can_proceed_to_step() {
    local target_step=$1

    case $target_step in
        1)
            return 0  # Always can do step 1
            ;;
        2)
            if [ -z "$LIVE_DATADIR" ]; then
                print_error "Please complete Step 1 first (detect MySQL directory)"
                return 1
            fi
            return 0
            ;;
        3)
            if [ -z "$LIVE_DATADIR" ]; then
                print_error "Please complete Step 1 first"
                return 1
            fi
            if [ -z "$TEMP_DATADIR" ]; then
                print_error "Please complete Step 2 first"
                return 1
            fi
            return 0
            ;;
        4)
            if [ -z "$DATABASE_NAME" ]; then
                print_error "Please complete Step 3 first (select database)"
                return 1
            fi
            return 0
            ;;
        5)
            if [ -z "$DATABASE_NAME" ]; then
                print_error "Please complete Step 3 first"
                return 1
            fi
            return 0
            ;;
        *)
            return 1
            ;;
    esac
}

################################################################################
# UTILITY FUNCTIONS
################################################################################

# Detect MySQL version and variant
detect_mysql_version() {
    print_info "Detecting MySQL version and variant..."

    # Try to get version from running instance
    if systemctl is-active --quiet mysqld || systemctl is-active --quiet mariadb; then
        local version_output=$(mysql -V 2>/dev/null || mysqld --version 2>/dev/null | head -1)

        if echo "$version_output" | grep -qi "mariadb"; then
            MYSQL_VARIANT="mariadb"
            MYSQL_VERSION=$(echo "$version_output" | grep -oP '\d+\.\d+\.\d+' | head -1)
            echo "  Detected: MariaDB $MYSQL_VERSION"
        else
            MYSQL_VARIANT="mysql"
            MYSQL_VERSION=$(echo "$version_output" | grep -oP '\d+\.\d+\.\d+' | head -1)
            echo "  Detected: MySQL $MYSQL_VERSION"
        fi
        return 0
    fi

    # Fallback: Check binaries
    if command -v mariadb --version &> /dev/null; then
        MYSQL_VARIANT="mariadb"
        MYSQL_VERSION=$(mariadb --version 2>/dev/null | grep -oP '\d+\.\d+\.\d+' | head -1)
        echo "  Detected: MariaDB $MYSQL_VERSION"
    elif command -v mysqld &> /dev/null; then
        MYSQL_VARIANT="mysql"
        MYSQL_VERSION=$(mysqld --version 2>/dev/null | grep -oP '\d+\.\d+\.\d+' | head -1)
        echo "  Detected: MySQL $MYSQL_VERSION"
    else
        print_warning "Could not detect MySQL variant/version"
        MYSQL_VARIANT="unknown"
        MYSQL_VERSION="unknown"
        return 1
    fi

    return 0
}

# Detect live MySQL data directory
detect_mysql_datadir() {
    print_info "Detecting MySQL data directory..."

    # Method 1: Check if MySQL is running
    if systemctl is-active --quiet mysqld || systemctl is-active --quiet mariadb; then
        LIVE_DATADIR=$(mysql -NBe 'SELECT @@datadir;' 2>/dev/null)
        if [ -n "$LIVE_DATADIR" ]; then
            echo "  Detected from running MySQL: $LIVE_DATADIR"
            # Verify we can read this directory
            if [ ! -r "$LIVE_DATADIR" ]; then
                print_error "Cannot read MySQL data directory: Permission denied"
                print_info "Try running this script with: sudo $0"
                return 1
            fi
            return 0
        fi
    fi

    # Method 2: Check configuration files
    local config_dir=$(grep -r "^datadir" /etc/my.cnf /etc/my.cnf.d/* 2>/dev/null | head -1 | cut -d'=' -f2 | tr -d ' ')
    if [ -n "$config_dir" ]; then
        LIVE_DATADIR="$config_dir"
        echo "  Detected from config: $LIVE_DATADIR"
        # Verify we can read this directory
        if [ ! -r "$LIVE_DATADIR" ]; then
            print_error "Cannot read MySQL data directory: Permission denied"
            print_info "Try running this script with: sudo $0"
            return 1
        fi
        return 0
    fi

    # Method 3: Default location
    if [ -d "/var/lib/mysql" ]; then
        LIVE_DATADIR="/var/lib/mysql"
        echo "  Using default: $LIVE_DATADIR"
        # Verify we can read this directory
        if [ ! -r "$LIVE_DATADIR" ]; then
            print_error "Cannot read MySQL data directory: Permission denied"
            print_info "Try running this script with: sudo $0"
            return 1
        fi
        return 0
    fi

    print_warning "Could not auto-detect MySQL data directory"
    return 1
}

# Validate restored data directory structure
validate_restore_structure() {
    local dir="$1"
    local missing_files=()

    print_info "Validating restored data structure..."

    # Check for InnoDB system tablespace
    if [ ! -f "$dir/ibdata1" ]; then
        missing_files+=("ibdata1")
    fi

    # Check for redo logs (version-specific)
    # IMPORTANT: MySQL 8.0.30+ changed redo log architecture
    # MySQL 8.0.30+: #innodb_redo directory with #ib_redoN files
    # MySQL 8.0.0-8.0.29: ib_logfile0/ib_logfile1
    # MySQL 5.7 and MariaDB: ib_logfile0/ib_logfile1
    local major_version=$(echo "$MYSQL_VERSION" | cut -d'.' -f1)
    local minor_version=$(echo "$MYSQL_VERSION" | cut -d'.' -f2)

    if [ "$MYSQL_VARIANT" = "mysql" ] && [ -n "$major_version" ] && [ "$major_version" -ge 8 ]; then
        # Check if MySQL 8.0.30+ (new redo log format)
        if [ -n "$minor_version" ] && [ "$major_version" -eq 8 ] && [ "$minor_version" -ge 0 ]; then
            # Try to detect 8.0.30+ by checking patch version or directory existence
            if [ -d "$dir/#innodb_redo" ]; then
                # MySQL 8.0.30+: #innodb_redo directory exists
                print_info "Detected MySQL 8.0.30+ redo log format (#innodb_redo)"
            elif [ -f "$dir/ib_logfile0" ]; then
                # MySQL 8.0.0-8.0.29: old format
                print_info "Detected MySQL 8.0.0-8.0.29 redo log format (ib_logfile)"
            else
                missing_files+=("ib_logfile0 OR #innodb_redo directory (MySQL 8.0)")
            fi
        else
            # MySQL 9.0+ or other major versions
            if [ ! -d "$dir/#innodb_redo" ]; then
                missing_files+=("#innodb_redo directory (MySQL 8.0.30+/9.0+)")
            fi
        fi
    else
        # MySQL 5.7 and MariaDB: Always use ib_logfile0/ib_logfile1
        if [ ! -f "$dir/ib_logfile0" ]; then
            missing_files+=("ib_logfile0 (MySQL 5.7/MariaDB)")
        fi
        if [ ! -f "$dir/ib_logfile1" ]; then
            print_warning "ib_logfile1 not found (may be optional)"
        fi
    fi

    # Check for mysql system database
    if [ ! -d "$dir/mysql" ] && [ ! -f "$dir/mysql.ibd" ]; then
        missing_files+=("mysql directory or mysql.ibd")
    fi

    # Check for target database
    if [ -n "$DATABASE_NAME" ] && [ ! -d "$dir/$DATABASE_NAME" ]; then
        missing_files+=("$DATABASE_NAME directory")
    fi

    if [ ${#missing_files[@]} -gt 0 ]; then
        print_error "Missing required files/directories:"
        for file in "${missing_files[@]}"; do
            echo "    - $file"
        done
        return 1
    fi

    print_success "Data structure validation passed"
    return 0
}

################################################################################
# PHASE 1 CRITICAL IMPROVEMENTS: Pre-Flight & System Validation
################################################################################

# Issue #1: Validate backup files BEFORE starting MySQL instance
# Checks for critical files, readability, and permissions
validate_backup_files() {
    local datadir="$1"
    local issues=0
    local warnings=0

    print_info "Performing pre-flight file validation..."
    echo ""

    # Check ibdata1 (InnoDB system tablespace)
    if [ ! -f "$datadir/ibdata1" ]; then
        print_error "  ✗ ibdata1 NOT FOUND"
        echo "    This is the InnoDB system tablespace - REQUIRED"
        issues=$((issues + 1))
    elif [ ! -r "$datadir/ibdata1" ]; then
        print_error "  ✗ ibdata1 EXISTS but NOT READABLE"
        echo "    Permission issue: $(ls -ld "$datadir/ibdata1" | awk '{print $1,$3,$4}')"
        issues=$((issues + 1))
    else
        local size=$(du -h "$datadir/ibdata1" | awk '{print $1}')
        print_success "  ✓ ibdata1 found ($size)"
    fi

    # Check redo logs (version-specific)
    local redo_status=""
    if [ -d "$datadir/#innodb_redo" ]; then
        # MySQL 8.0.30+
        if [ -r "$datadir/#innodb_redo" ]; then
            print_success "  ✓ #innodb_redo directory found (MySQL 8.0.30+)"
            redo_status="found"
        else
            print_error "  ✗ #innodb_redo directory NOT READABLE"
            issues=$((issues + 1))
            redo_status="unreadable"
        fi
    elif [ -f "$datadir/ib_logfile0" ]; then
        # MySQL 5.7/MariaDB/MySQL 8.0.0-8.0.29
        if [ ! -r "$datadir/ib_logfile0" ]; then
            print_error "  ✗ ib_logfile0 EXISTS but NOT READABLE"
            issues=$((issues + 1))
            redo_status="unreadable"
        else
            local size=$(du -h "$datadir/ib_logfile0" | awk '{print $1}')
            print_success "  ✓ ib_logfile0 found ($size)"
            redo_status="found"

            # Check ib_logfile1
            if [ -f "$datadir/ib_logfile1" ]; then
                if [ ! -r "$datadir/ib_logfile1" ]; then
                    print_warning "  ⚠ ib_logfile1 EXISTS but NOT READABLE"
                    warnings=$((warnings + 1))
                else
                    print_success "  ✓ ib_logfile1 found"
                fi
            else
                print_warning "  ⚠ ib_logfile1 not found (may be optional)"
                warnings=$((warnings + 1))
            fi
        fi
    else
        print_error "  ✗ Redo logs NOT FOUND (ib_logfile0 or #innodb_redo)"
        echo "    Needed for InnoDB recovery"
        issues=$((issues + 1))
        redo_status="missing"
    fi

    # Check mysql system database
    if [ -d "$datadir/mysql" ]; then
        if [ ! -r "$datadir/mysql" ]; then
            print_error "  ✗ mysql/ directory NOT READABLE"
            echo "    This contains critical system tables"
            issues=$((issues + 1))
        else
            # Check for key system table files
            local mysql_tables=$(find "$datadir/mysql" -maxdepth 1 -type f -readable 2>/dev/null | wc -l)
            print_success "  ✓ mysql/ directory found ($mysql_tables files)"
        fi
    elif [ -f "$datadir/mysql.ibd" ]; then
        if [ ! -r "$datadir/mysql.ibd" ]; then
            print_error "  ✗ mysql.ibd EXISTS but NOT READABLE"
            issues=$((issues + 1))
        else
            print_success "  ✓ mysql.ibd found"
        fi
    else
        print_error "  ✗ System database NOT FOUND (mysql/ or mysql.ibd)"
        echo "    This contains system metadata required for recovery"
        issues=$((issues + 1))
    fi

    # Check target database directory
    if [ -n "$DATABASE_NAME" ]; then
        if [ ! -d "$datadir/$DATABASE_NAME" ]; then
            print_error "  ✗ Database '$DATABASE_NAME' directory NOT FOUND"
            echo "    Expected at: $datadir/$DATABASE_NAME"
            issues=$((issues + 1))
        elif [ ! -r "$datadir/$DATABASE_NAME" ]; then
            print_error "  ✗ Database '$DATABASE_NAME' directory NOT READABLE"
            issues=$((issues + 1))
        else
            local table_files=$(find "$datadir/$DATABASE_NAME" -maxdepth 1 -type f 2>/dev/null | wc -l)
            print_success "  ✓ Database '$DATABASE_NAME' found ($table_files files)"
        fi
    fi

    # Check directory permissions
    if [ ! -x "$datadir" ]; then
        print_error "  ✗ Directory $datadir NOT EXECUTABLE (traversable)"
        echo "    Cannot access files inside this directory"
        issues=$((issues + 1))
    fi

    # Check ownership
    local dir_owner=$(stat -c '%U:%G' "$datadir" 2>/dev/null || stat -f '%OLp:%OLg' "$datadir" 2>/dev/null || echo "unknown")
    if [ "$dir_owner" != "mysql:mysql" ] && [ "$dir_owner" != "root:root" ]; then
        print_warning "  ⚠ Directory owned by $dir_owner (expected mysql:mysql or root:root)"
        warnings=$((warnings + 1))
    fi

    echo ""

    # Report results
    if [ "$issues" -gt 0 ]; then
        print_error "PRE-FLIGHT VALIDATION FAILED: $issues critical issue(s)"
        echo ""
        echo "Cannot proceed with recovery due to missing or unreadable files."
        echo ""
        echo "To fix:"
        echo "  1. Verify backup files were extracted correctly"
        echo "  2. Check file permissions: chown -R mysql:mysql $datadir"
        echo "  3. Ensure sufficient disk space"
        echo ""
        return 1
    fi

    if [ "$warnings" -gt 0 ]; then
        print_warning "Pre-flight validation passed with $warnings warning(s)"
        echo ""
    else
        print_success "Pre-flight validation PASSED - all critical files present"
        echo ""
    fi

    return 0
}

# Issue #2: Enhanced database discovery with diagnostics
# Lists all databases found and explains why target database might be missing
discover_and_report_databases() {
    local datadir="$1"
    local target_db="$2"

    print_info "Discovering databases in second instance..."
    echo ""

    # Get list of all databases
    local db_list=$(mysql -h localhost -S "$datadir/socket.mysql" -NBe "SHOW DATABASES;" 2>/dev/null)

    if [ -z "$db_list" ]; then
        print_error "Could not query database list"
        return 1
    fi

    # Display found databases
    print_info "Found the following databases:"
    echo "$db_list" | while read -r db; do
        if [ "$db" = "$target_db" ]; then
            echo "  ✓ $db (TARGET - FOUND)"
        else
            echo "  ▪ $db"
        fi
    done
    echo ""

    # Check if target was found
    if ! echo "$db_list" | grep -q "^$target_db$"; then
        print_error "Target database '$target_db' NOT FOUND in instance"
        echo ""
        echo "Diagnosing why..."
        echo ""

        # Check if system tables are accessible
        print_info "Testing system table accessibility..."

        # Test mysql.db table
        if mysql -h localhost -S "$datadir/socket.mysql" -NBe "SELECT COUNT(*) FROM mysql.db LIMIT 1;" 2>/dev/null >/dev/null; then
            print_success "  ✓ mysql.db table is accessible"
        else
            print_error "  ✗ mysql.db table is NOT ACCESSIBLE or CORRUPTED"
            echo ""
            echo "This explains why '$target_db' is not visible:"
            echo "  The mysql.db table stores database metadata"
            echo "  If corrupted, databases cannot be discovered"
            echo ""
            return 1
        fi

        # Test mysql.innodb_table_stats
        if mysql -h localhost -S "$datadir/socket.mysql" -NBe "SELECT COUNT(*) FROM mysql.innodb_table_stats LIMIT 1;" 2>/dev/null >/dev/null; then
            print_success "  ✓ mysql.innodb_table_stats table is accessible"
        else
            print_warning "  ⚠ mysql.innodb_table_stats table is NOT accessible"
            echo "    (This may affect performance but not visibility)"
        fi

        # Test information_schema
        if mysql -h localhost -S "$datadir/socket.mysql" -NBe "SELECT COUNT(*) FROM information_schema.schemata;" 2>/dev/null >/dev/null; then
            print_success "  ✓ information_schema.schemata is accessible"
        else
            print_error "  ✗ information_schema.schemata is NOT accessible"
            echo ""
            echo "System tables are severely corrupted - database cannot be recovered"
            return 1
        fi

        echo ""
        echo "Recovery Recommendations:"
        echo "  1. Check if system tables need recovery:"
        echo "     - InnoDB system table corruption requires higher recovery modes"
        echo "     - Try recovery mode 4 or higher (skip checksums/log)"
        echo ""
        echo "  2. Or restore mysql/ directory from backup separately:"
        echo "     - Restore mysql/ directory alone"
        echo "     - Then re-run this script"
        echo ""

        return 1
    fi

    print_success "Target database '$target_db' found and accessible"
    return 0
}

# Issue #3: Test system table accessibility AFTER instance starts
# Validates that critical system tables are readable
test_system_tables() {
    local datadir="$1"

    print_info "Testing system table accessibility..."
    echo ""

    local tests_passed=0
    local tests_failed=0

    # Test 1: mysql.db table (metadata for database permissions)
    if mysql -h localhost -S "$datadir/socket.mysql" -NBe "SELECT COUNT(*) FROM mysql.db LIMIT 1;" 2>/dev/null >/dev/null; then
        print_success "  ✓ mysql.db table accessible"
        tests_passed=$((tests_passed + 1))
    else
        print_error "  ✗ mysql.db table FAILED"
        echo "    This table stores database information"
        tests_failed=$((tests_failed + 1))
    fi

    # Test 2: mysql.innodb_table_stats (InnoDB statistics)
    if mysql -h localhost -S "$datadir/socket.mysql" -NBe "SELECT COUNT(*) FROM mysql.innodb_table_stats LIMIT 1;" 2>/dev/null >/dev/null; then
        print_success "  ✓ mysql.innodb_table_stats table accessible"
        tests_passed=$((tests_passed + 1))
    else
        print_warning "  ⚠ mysql.innodb_table_stats table FAILED (may affect performance)"
        tests_failed=$((tests_failed + 1))
    fi

    # Test 3: information_schema.schemata (database list)
    if mysql -h localhost -S "$datadir/socket.mysql" -NBe "SELECT COUNT(*) FROM information_schema.schemata;" 2>/dev/null >/dev/null; then
        print_success "  ✓ information_schema.schemata accessible"
        tests_passed=$((tests_passed + 1))
    else
        print_error "  ✗ information_schema.schemata FAILED"
        echo "    This affects database discovery"
        tests_failed=$((tests_failed + 1))
    fi

    echo ""

    if [ "$tests_failed" -gt 0 ]; then
        print_error "System table tests: $tests_passed passed, $tests_failed FAILED"
        print_error "System tables may be corrupted - recovery may fail"
        echo ""
        return 1
    fi

    print_success "All system table tests passed"
    return 0
}

################################################################################
# PHASE 2 IMPROVEMENTS: Error Monitoring & Recovery Mode Guidance
################################################################################

# Issue #4: Analyze error log and check for critical startup errors
# Returns 0 if no critical errors, 1 if critical errors found
check_error_log_for_issues() {
    local error_log="$1"

    if [ ! -f "$error_log" ]; then
        return 0  # No error log yet
    fi

    # Check for critical errors that need recovery mode escalation
    local critical_errors=0

    # Check for missing files/tablespaces
    if tail -100 "$error_log" 2>/dev/null | grep -qi "Cannot find space id\|Cannot open tablespace\|missing"; then
        print_error "  ✗ Missing files or tablespaces detected in error log"
        critical_errors=$((critical_errors + 1))
    fi

    # Check for corruption
    if tail -100 "$error_log" 2>/dev/null | grep -qi "Corrupted\|Database page corruption\|corruption detected"; then
        print_error "  ✗ Data corruption detected in error log"
        critical_errors=$((critical_errors + 1))
    fi

    # Check for redo log issues
    if tail -100 "$error_log" 2>/dev/null | grep -qi "redo log.*incompatible\|redo log.*different"; then
        print_error "  ✗ Redo log incompatibility detected"
        critical_errors=$((critical_errors + 1))
    fi

    # Check for insert buffer issues
    if tail -100 "$error_log" 2>/dev/null | grep -qi "insert buffer\|ibuf.*merge"; then
        print_warning "  ⚠ Insert buffer issues detected (may need recovery mode 4+)"
        critical_errors=$((critical_errors + 1))
    fi

    if [ "$critical_errors" -gt 0 ]; then
        return 1
    fi

    return 0
}

# Issue #4: Suggest recovery mode based on error log analysis
# Examines errors and recommends appropriate recovery mode
suggest_recovery_mode_from_errors() {
    local error_log="$1"
    local current_mode="${2:-0}"

    if [ ! -f "$error_log" ]; then
        echo "0"
        return 0
    fi

    local suggested_mode="$current_mode"

    # Check error patterns in order of severity
    if tail -100 "$error_log" 2>/dev/null | grep -qi "Corrupted\|corruption detected"; then
        # Corruption detected
        if [ "$current_mode" -lt 1 ]; then
            suggested_mode=1
            echo "corruption:1"
        elif [ "$current_mode" -lt 5 ]; then
            suggested_mode=5
            echo "corruption:5"
        else
            suggested_mode=6
            echo "corruption:6"
        fi
        return 0
    fi

    if tail -100 "$error_log" 2>/dev/null | grep -qi "Cannot find space id\|Cannot open tablespace"; then
        # Missing files
        if [ "$current_mode" -lt 1 ]; then
            suggested_mode=1
            echo "missing_files:1"
        elif [ "$current_mode" -lt 4 ]; then
            suggested_mode=4
            echo "missing_files:4"
        else
            suggested_mode=5
            echo "missing_files:5"
        fi
        return 0
    fi

    if tail -100 "$error_log" 2>/dev/null | grep -qi "insert buffer\|ibuf"; then
        # Insert buffer issues
        if [ "$current_mode" -lt 4 ]; then
            suggested_mode=4
            echo "insert_buffer:4"
        else
            suggested_mode=5
            echo "insert_buffer:5"
        fi
        return 0
    fi

    if tail -100 "$error_log" 2>/dev/null | grep -qi "redo log.*incompatible"; then
        # Redo log incompatibility
        suggested_mode=5
        echo "redo_incompatible:5"
        return 0
    fi

    # Auto-escalate if stuck at same mode
    if [ "$current_mode" -gt 0 ]; then
        suggested_mode=$((current_mode + 1))
        if [ "$suggested_mode" -gt 6 ]; then
            suggested_mode=6
        fi
        echo "escalation:$suggested_mode"
        return 0
    fi

    echo "0"
    return 0
}

# Issue #7: Prompt user to retry with different recovery mode
# Offers suggestion based on error analysis
# Returns 0 if user wants to retry, 1 if cancel
prompt_retry_with_recovery_mode() {
    local current_mode="${1:-0}"
    local error_log="${2:-}"

    echo ""
    print_warning "Recovery attempt with mode $current_mode did not succeed"
    echo ""

    # Suggest next mode if error log available
    if [ -n "$error_log" ] && [ -f "$error_log" ]; then
        local suggestion=$(suggest_recovery_mode_from_errors "$error_log" "$current_mode")
        local suggested_mode=$(echo "$suggestion" | cut -d':' -f2)
        local error_category=$(echo "$suggestion" | cut -d':' -f1)

        if [ -n "$suggested_mode" ] && [ "$suggested_mode" -ne "$current_mode" ]; then
            echo "Error Analysis:"
            echo "  Category: $error_category"
            echo "  Current recovery mode: $current_mode"
            echo "  Recommended next mode: $suggested_mode"
            echo ""
            echo "Mode $suggested_mode will:"
            case $suggested_mode in
                1) echo "  - Ignore individual page corruption (Level 1)" ;;
                2) echo "  - Prevent background operations (Level 2)" ;;
                3) echo "  - Prevent transaction rollbacks (Level 3)" ;;
                4) echo "  - Prevent insert buffer merge (Level 4)" ;;
                5) echo "  - Skip redo log recovery (Level 5)" ;;
                6) echo "  - Skip page checksums (Level 6 - most aggressive)" ;;
            esac
            echo ""
            echo -n "Try again with mode $suggested_mode? (y/n): "
            read -r choice

            if [ "$choice" = "y" ]; then
                FORCE_RECOVERY="$suggested_mode"
                print_warning "Retrying with recovery mode $suggested_mode..."
                return 0
            fi
        fi
    fi

    # Ask user if they want to try different mode manually
    echo -n "Would you like to try a different recovery mode? (y/n): "
    read -r choice

    if [ "$choice" = "y" ]; then
        echo ""
        echo "Recovery mode levels:"
        echo "  0 = No recovery (default)"
        echo "  1 = Ignore corrupt pages"
        echo "  2 = Prevent background operations"
        echo "  3 = Prevent transaction rollbacks"
        echo "  4 = Prevent insert buffer merge"
        echo "  5 = Skip log redo (aggressive)"
        echo "  6 = Skip page checksums (most aggressive)"
        echo ""
        echo -n "Enter recovery mode (0-6): "
        read -r new_mode

        if [ -n "$new_mode" ] && { [ "$new_mode" -ge 0 ] && [ "$new_mode" -le 6 ]; } 2>/dev/null; then
            FORCE_RECOVERY="$new_mode"
            print_warning "Will retry with recovery mode $new_mode"
            return 0
        else
            print_error "Invalid mode. Cancelling."
            return 1
        fi
    fi

    return 1
}

################################################################################
# Detect error type from InnoDB log and recommend recovery level
detect_recovery_level_from_errors() {
    local error_log="$1"
    local last_recovery_level="${2:-0}"

    if [ ! -f "$error_log" ]; then
        echo "0"  # No errors = no recovery needed
        return 0
    fi

    local log_content=$(cat "$error_log" 2>/dev/null)

    # Error type detection (in order of severity/recovery level needed)
    local error_type=""
    local recommended_level=0

    # Check for MISSING FILES (missing tablespaces, unopenable files)
    # These need Level 1 (ignore corrupt pages) - missing files aren't corrupt, just absent
    if echo "$log_content" | grep -qiE "Cannot open tablespace|Tablespace.*missing|was not found at|Cannot find space id"; then
        error_type="missing_files"
        recommended_level=1

    # Check for REDO LOG INCOMPATIBILITY (version mismatch, format issues)
    # These need Level 5 (skip log redo) or higher
    elif echo "$log_content" | grep -qiE "redo log.*incompatible|redo log.*different|redo log format.*does not match"; then
        error_type="redo_incompatible"
        recommended_level=5

    # Check for CORRUPTION (page corruption, corrupted data)
    # These need Level 1-4 depending on severity
    elif echo "$log_content" | grep -qiE "Corrupted|Database page corruption|Corruption detected"; then
        error_type="corruption"
        # Start with Level 1 if fresh, escalate if retry
        if [ "$last_recovery_level" -eq 0 ]; then
            recommended_level=1
        elif [ "$last_recovery_level" -eq 1 ]; then
            recommended_level=4
        else
            recommended_level=6
        fi

    # Check for INSERT BUFFER ISSUES (insert buffer merge failures)
    # These need Level 4 (prevent insert buffer merge)
    elif echo "$log_content" | grep -qiE "insert buffer|ibuf|buffer pool.*error"; then
        error_type="insert_buffer"
        recommended_level=4

    # Check for MEMORY ISSUES (allocation failures, OOM)
    # These need system fix, not recovery mode
    elif echo "$log_content" | grep -qiE "Cannot allocate memory|Out of memory|memory error"; then
        error_type="memory_issue"
        recommended_level=0

    # Check for ROLLBACK ISSUES (transaction rollback problems)
    # These need Level 3 (prevent transaction rollbacks)
    elif echo "$log_content" | grep -qiE "rollback.*error|Cannot rollback|Rollback failed"; then
        error_type="rollback_issue"
        recommended_level=3
    fi

    # Auto-escalate if retry at same level
    if [ "$last_recovery_level" -gt 0 ] && [ "$recommended_level" -eq "$last_recovery_level" ]; then
        recommended_level=$((last_recovery_level + 1))
        if [ "$recommended_level" -gt 6 ]; then
            recommended_level=6
        fi
    fi

    echo "$recommended_level|$error_type"
    return 0
}

# Check error log for InnoDB startup issues (returns error type)
check_innodb_errors() {
    local error_log="$1"
    local check_recent="${2:-no}"  # "yes" = only check recent errors, "no" = full check
    local selected_db="${3:-}"     # Database name to filter on (optional)

    if [ ! -f "$error_log" ]; then
        return 0  # No error log yet, assume OK
    fi

    local errors_found=0
    local critical_errors=()

    # CRITICAL error patterns that ALWAYS fail (not specific to any database)
    # These indicate fundamental InnoDB corruption or system issues
    # NOTE: Missing tablespaces are NOT included - instance can still work with force recovery
    local critical_patterns=(
        "InnoDB: Corrupted"
        "InnoDB: Database page corruption"
        "InnoDB: Cannot allocate memory"
        "InnoDB: Redo log.*corrupt"
        "InnoDB:.*redo log.*incompatible"
        "InnoDB: Plugin initialization aborted"
    )

    # If checking recent errors, only look at last 50 lines
    local log_content
    if [ "$check_recent" = "yes" ]; then
        log_content=$(tail -50 "$error_log" 2>/dev/null)
    else
        log_content=$(cat "$error_log" 2>/dev/null)
    fi

    # Check CRITICAL patterns first (always fail if found)
    # These are truly blocking issues that prevent any recovery
    for pattern in "${critical_patterns[@]}"; do
        if echo "$log_content" | grep -qE "$pattern"; then
            local error_line=$(echo "$log_content" | grep -E "$pattern" | tail -1)
            critical_errors+=("$error_line")
            errors_found=$((errors_found + 1))
        fi
    done

    if [ -n "$errors_found" ] && [ "$errors_found" -gt 0 ]; then
        print_error "InnoDB errors detected in $error_log:"
        for err in "${critical_errors[@]}"; do
            echo "  - ${err:0:120}..."
        done
        return 1
    fi

    return 0
}

# Show intelligent recovery options based on error type
show_recovery_options() {
    local datadir="$1"
    local current_recovery="${2:-0}"
    local selected_database="${3:-}"  # The database user wants to restore

    print_banner "Recovery Options"

    # Analyze the error log to determine failure type
    local error_log="$datadir/mysql.err"

    # First, use error-based detection to determine root cause and recommended level
    if [ -f "$error_log" ]; then
        local detection_result=$(detect_recovery_level_from_errors "$error_log" "$current_recovery")
        local recommended_level=$(echo "$detection_result" | cut -d'|' -f1)
        local error_type=$(echo "$detection_result" | cut -d'|' -f2)

        if [ -n "$error_type" ]; then
            echo "Based on error log analysis:"
            echo "  Error Type: $error_type"
            echo "  Recommended Recovery Level: $recommended_level"
            echo ""
        fi
    fi
    local missing_files=""
    local corruption_detected=""
    local redo_incompatible=""
    local memory_issue=""
    local missing_from_selected_db=""
    local missing_from_other_dbs=""

    if [ -f "$error_log" ]; then
        if grep -qE "Cannot open tablespace|Tablespace.*missing|Tablespace.*was not found|Unable to open" "$error_log"; then
            missing_files="yes"

            # Check if missing files are from the selected database or other databases
            if [ -n "$selected_database" ]; then
                if grep -q "was not found at \./$selected_database/" "$error_log"; then
                    missing_from_selected_db="yes"
                fi
                # Check if any missing files are NOT from the selected database
                while IFS= read -r line; do
                    if ! echo "$line" | grep -q "\./$selected_database/"; then
                        missing_from_other_dbs="yes"
                        break
                    fi
                done < <(grep "was not found at" "$error_log")
            fi
        fi
        if grep -qE "Corrupted|Database page corruption" "$error_log"; then
            corruption_detected="yes"
        fi
        if grep -qE "redo log.*incompatible|redo log.*different|redo log format" "$error_log"; then
            redo_incompatible="yes"
        fi
        if grep -qE "Cannot allocate memory|Out of memory" "$error_log"; then
            memory_issue="yes"
        fi
    fi

    # Provide targeted guidance based on error type
    if [ -n "$missing_files" ]; then
        # Smart detection: if missing files are ONLY from other databases (not the selected one)
        if [ -z "$missing_from_selected_db" ] && [ -n "$missing_from_other_dbs" ] && [ -n "$selected_database" ]; then
            print_warning "SMART DETECTION: Missing files are from OTHER databases, not '$selected_database'"
            echo ""
            print_success "Your selected database '$selected_database' appears to have all files!"
            echo ""
            print_warning "RECOMMENDED ACTION: Use Force Recovery Level 1"
            echo ""
            echo "The ibdata1 file contains references to databases you didn't restore."
            echo "Force Recovery Level 1 will:"
            echo "  ✓ Ignore missing databases (safe - you don't have them anyway)"
            echo "  ✓ Start MySQL successfully"
            echo "  ✓ Allow you to dump '$selected_database' with NO data loss"
            echo ""
            echo "This is the CORRECT approach for selective database restoration."
            echo ""
            print_info "Re-run this script and select Force Recovery Level 1 in Step 4"
            echo ""
            return 0
        fi

        print_error "DIAGNOSIS: Missing or unopenable tablespace files"
        echo ""

        # Use error-based detection to confirm recovery level recommendation
        local detection_result=$(detect_recovery_level_from_errors "$error_log" "$current_recovery")
        local recommended_level=$(echo "$detection_result" | cut -d'|' -f1)
        echo "Error analysis recommends: Force Recovery Level $recommended_level"
        echo ""

        # Parse error log to find EXACT missing files
        echo "Analyzing error log for missing files..."
        echo ""

        local missing_list=()
        local missing_count=0

        # Extract tablespace names from various error patterns
        while IFS= read -r error_line; do
            # Pattern 1: "Cannot open tablespace 'db/table'" or "Tablespace N was not found at ./db/table.ibd"
            if echo "$error_line" | grep -qE "Cannot open|Unable to open|Tablespace.*missing|was not found at"; then
                # Try to extract path from "was not found at ./path/table.ibd"
                local tablespace=$(echo "$error_line" | grep -oE '\./[^/]+/[^.]+\.ibd' | sed 's|\./||;s|\.ibd||' | head -1)

                # Fallback: try to extract from quoted tablespace name
                if [ -z "$tablespace" ]; then
                    tablespace=$(echo "$error_line" | grep -oE "'[^']+'" | tr -d "'" | head -1)
                fi

                if [ -n "$tablespace" ]; then
                    missing_list+=("$tablespace")
                    missing_count=$((missing_count + 1))
                fi
            fi

            # Pattern 2: "Cannot find space id N in the tablespace memory cache"
            if echo "$error_line" | grep -qE "Cannot find space id.*in the tablespace"; then
                local space_id=$(echo "$error_line" | grep -oE "space id [0-9]+" | awk '{print $3}')
                if [ -n "$space_id" ]; then
                    missing_list+=("space_id_$space_id")
                    missing_count=$((missing_count + 1))
                fi
            fi
        done < <(grep -iE "Cannot open|Unable to open|Tablespace.*missing|was not found at|Cannot find space id" "$error_log" 2>/dev/null)

        if [ "$missing_count" -gt 0 ]; then
            print_warning "MISSING FILES DETECTED ($missing_count found):"
            echo ""

            # Remove duplicates and display
            local unique_missing=($(printf '%s\n' "${missing_list[@]}" | sort -u))
            local file_num=1

            for item in "${unique_missing[@]}"; do
                if [[ "$item" == *"/"* ]]; then
                    # Format: database/table
                    local db_name=$(echo "$item" | cut -d'/' -f1)
                    local table_name=$(echo "$item" | cut -d'/' -f2)
                    echo "  $file_num) Table: $table_name (in database: $db_name)"
                    echo "     File needed: $datadir/$db_name/${table_name}.ibd"
                    echo "     Backup path: /var/lib/mysql/$db_name/${table_name}.ibd"
                elif [[ "$item" == space_id_* ]]; then
                    echo "  $file_num) Unknown tablespace (space ID: ${item#space_id_})"
                    echo "     Action: Restore entire database directory to ensure all files present"
                else
                    echo "  $file_num) $item"
                fi
                echo ""
                file_num=$((file_num + 1))
            done
        else
            print_warning "Could not parse specific missing files from error log"
            echo "Showing raw error lines:"
            echo ""
            grep -iE "Cannot open|Unable to open|Tablespace.*missing|was not found at" "$error_log" 2>/dev/null | head -10
            echo ""
        fi

        echo "Common causes:"
        echo "  - Not all database table files (.ibd) were restored"
        echo "  - Table files are in wrong location"
        echo "  - Permissions issue (not mysql:mysql)"
        echo ""
        print_warning "RECOMMENDED ACTIONS:"
        echo ""
        echo "  Option 1: Restore Missing Files (RECOMMENDED)"
        echo "  ────────────────────────────────────────────────"
        if [ "$missing_count" -gt 0 ]; then
            echo "    1. Restore the $missing_count file(s) listed above from your backup"
            echo ""
            echo "    2. Use Acronis/rsync/cp to copy .ibd files:"
            echo "       Example commands:"
            for item in "${unique_missing[@]}"; do
                if [[ "$item" == *"/"* ]]; then
                    local db_name=$(echo "$item" | cut -d'/' -f1)
                    local table_name=$(echo "$item" | cut -d'/' -f2)
                    echo "       cp /backup/path/$db_name/${table_name}.ibd $datadir/$db_name/"
                fi
            done
            echo ""
            echo "    3. Fix ownership:"
            echo "       chown mysql:mysql \"$datadir/$DATABASE_NAME\"/*.ibd"
        else
            echo "    1. Check error log manually:"
            echo "       grep -i 'cannot open\\|missing' $error_log"
            echo ""
            echo "    2. Restore identified .ibd files from backup"
        fi
        echo ""
        echo "    4. Re-run this script (will detect newly added files)"
        echo ""
        echo "  Option 2: Restore Entire Database Directory"
        echo "  ────────────────────────────────────────────────"
        echo "    If you're missing many files, easier to restore all:"
        echo ""
        echo "    1. Remove partial database directory:"
        echo "       rm -rf \"$datadir/$DATABASE_NAME\""
        echo ""
        echo "    2. Restore complete database directory from backup:"
        echo "       cp -r /backup/path/$DATABASE_NAME \"$datadir/\""
        echo ""
        echo "    3. Fix ownership:"
        echo "       chown -R mysql:mysql \"$datadir/$DATABASE_NAME\""
        echo ""
        echo "    4. Re-run this script"
        echo ""
        echo "  Option 3: Start Completely Fresh"
        echo "  ────────────────────────────────────────────────"
        echo "    1. Clear the entire restore directory:"
        echo "       rm -rf $datadir/*"
        echo ""
        echo "    2. Restore ALL files from backup (complete set):"
        echo "       - ibdata1"
        echo "       - redo logs (ib_logfile* or #innodb_redo/)"
        echo "       - mysql/ directory"
        echo "       - All database directories"
        echo ""
        echo "    3. Re-run this script from the beginning"
        echo ""

    elif [ -n "$redo_incompatible" ]; then
        print_error "DIAGNOSIS: Redo log incompatibility"
        echo ""

        # Use error-based detection to recommend appropriate recovery level
        local detection_result=$(detect_recovery_level_from_errors "$error_log" "$current_recovery")
        local recommended_level=$(echo "$detection_result" | cut -d'|' -f1)
        local error_type=$(echo "$detection_result" | cut -d'|' -f2)

        echo "Common causes:"
        echo "  - Backup from different MySQL version"
        echo "  - Mixed redo log formats (8.0.30 vs older)"
        echo "  - Partial restore (old + new redo logs mixed)"
        echo ""
        print_warning "RECOMMENDED ACTIONS:"
        echo ""
        echo "  Option 1: Start Fresh with Correct Redo Logs (PREFERRED)"
        echo "  ────────────────────────────────────────────────"
        echo "    1. Remove current redo logs:"
        if [ -d "$datadir/#innodb_redo" ]; then
            echo "       rm -rf $datadir/#innodb_redo"
        else
            echo "       rm -f $datadir/ib_logfile*"
        fi
        echo ""
        echo "    2. Restore redo logs from SAME backup date:"
        echo "       - Must match the ibdata1 file exactly"
        echo "       - Check backup timestamp carefully"
        echo ""
        echo "    3. Re-run this script"
        echo ""
        echo "  Option 2: Force Recovery (if redo logs are lost/unavailable)"
        echo "  ────────────────────────────────────────────────"
        echo "    Error analysis recommends: Force Recovery Level $recommended_level"
        echo "    Re-run script and select recovery mode $recommended_level"
        echo ""
        if [ "$recommended_level" -ge 5 ]; then
            echo "    WARNING: This recovery level will skip log redo operations"
            echo "    Some recent transactions may be lost or incomplete"
        fi
        echo ""

    elif [ -n "$corruption_detected" ]; then
        print_error "DIAGNOSIS: InnoDB corruption detected"
        echo ""

        # Use error-based detection to recommend appropriate recovery level
        local detection_result=$(detect_recovery_level_from_errors "$error_log" "$current_recovery")
        local recommended_level=$(echo "$detection_result" | cut -d'|' -f1)
        local error_type=$(echo "$detection_result" | cut -d'|' -f2)

        # Build escalation path based on corruption type
        local level_1_desc="Ignores corrupt pages (most conservative)"
        local level_4_desc="Prevents insert buffer merge operations"
        local level_6_desc="Skips page checksums (maximum recovery, most data loss risk)"

        print_warning "RECOMMENDED ACTION (from error analysis):"
        echo "  ✓ Try Force Recovery Level $recommended_level"
        echo ""
        print_warning "STEP-BY-STEP PROGRESSION:"
        echo ""

        # Show all levels up to and including the recommended level
        # This helps user understand the escalation path if needed

        # Level 1
        if [ "$recommended_level" -ge 1 ]; then
            echo "  Step 1: Try Force Recovery Level 1"
            echo "  ────────────────────────────────────────────────"
            echo "    Re-run script → Step 4 → Select recovery mode 1"
            echo "    $level_1_desc"
            if [ "$recommended_level" -eq 1 ]; then
                echo "    ^ RECOMMENDED (error analysis suggests this level)"
            fi
            echo ""
        fi

        # Level 4 (skip 2 and 3 as they're less commonly needed for corruption)
        if [ "$recommended_level" -ge 4 ]; then
            echo "  Step 2: If Level 1 Fails, Try Force Recovery Level 4"
            echo "  ────────────────────────────────────────────────"
            echo "    Re-run script → Step 4 → Select recovery mode 4"
            echo "    $level_4_desc"
            if [ "$recommended_level" -eq 4 ]; then
                echo "    ^ RECOMMENDED (error analysis suggests this level)"
            fi
            echo ""
        fi

        # Level 6 (last resort)
        if [ "$recommended_level" -ge 6 ]; then
            echo "  Step 3: If Level 4 Fails, Try Force Recovery Level 6 (LAST RESORT)"
            echo "  ────────────────────────────────────────────────"
            echo "    Re-run script → Step 4 → Select recovery mode 6"
            echo "    $level_6_desc"
            echo "    ^ RECOMMENDED (error analysis suggests this level - MAX DATA RISK)"
            echo ""
        fi

        echo "  Step 4: If All Recovery Levels Fail"
        echo "  ────────────────────────────────────────────────"
        echo "    1. Corruption may be in the backup itself"
        echo "    2. Try restoring from an older backup date"
        echo "    3. Clear directory: rm -rf $datadir/*"
        echo "    4. Restore from different backup snapshot"
        echo ""

    elif [ -n "$memory_issue" ]; then
        print_error "DIAGNOSIS: Memory allocation failure"
        echo ""
        print_warning "RECOMMENDED ACTIONS:"
        echo ""
        echo "  1. Check available memory: free -h"
        echo "  2. Stop other MySQL instances: systemctl stop mysqld"
        echo "  3. Re-run this script"
        echo ""

    else
        # Generic troubleshooting
        print_warning "TROUBLESHOOTING STEPS:"
        echo ""
        echo "  1. Review Error Log"
        echo "  ────────────────────────────────────────────────"
        echo "     tail -100 $error_log | less"
        echo ""
        echo "  2. Verify File Structure"
        echo "  ────────────────────────────────────────────────"
        echo "     ls -laR $datadir/ | less"
        echo "     Look for: ibdata1, redo logs, mysql/, database/"
        echo ""
        echo "  3. Check Ownership"
        echo "  ────────────────────────────────────────────────"
        echo "     stat -c '%U:%G' $datadir/ibdata1"
        echo "     Should be: mysql:mysql"
        echo ""
        echo "  4. Try Force Recovery"
        echo "  ────────────────────────────────────────────────"
        echo "     Re-run script → Step 4 → Select recovery mode 1-6"
        echo ""
        echo "  5. Start Fresh"
        echo "  ────────────────────────────────────────────────"
        echo "     rm -rf $datadir/*"
        echo "     Restore complete file set from backup"
        echo "     Re-run script"
        echo ""
    fi

    # Always show the error log location
    echo ""
    print_info "Full error log location:"
    echo "  $error_log"
    echo ""

    # Prompt user for action
    print_info "What would you like to do?"
    echo ""
    echo "  [1] Retry with recovery mode 1 (ignore corrupt pages)"
    echo "  [2] Retry with recovery mode 2 (prevent background ops)"
    echo "  [3] Retry with recovery mode 3 (prevent rollbacks)"
    echo "  [4] Retry with recovery mode 4 (prevent insert buffer merge)"
    echo "  [5] Retry with recovery mode 5 (skip redo log)"
    echo "  [6] Retry with recovery mode 6 (skip page checksums)"
    echo "  [A] Auto-escalate (let script choose next mode)"
    echo ""
    echo -n "Select: "
    read -r recovery_choice

    case "$recovery_choice" in
        [1-6])
            # User selected specific recovery mode
            FORCE_RECOVERY="$recovery_choice"
            print_warning "Will retry with recovery mode $FORCE_RECOVERY"
            return 0
            ;;
        A|a)
            # Auto-escalate
            print_warning "Will auto-escalate to next recovery mode"
            return 0
            ;;
        *)
            print_error "Invalid selection. Returning to menu."
            return 1
            ;;
    esac
}

# Quick Retry Menu - shown when dump fails to let user pick recovery mode
# Returns 0 if user selects recovery mode, 1 if user wants to exit to menu
show_quick_retry_menu() {
    echo ""
    echo "════════════════════════════════════════════════════════════════"
    echo "Dump failed. Which recovery mode would you like to try?"
    echo "════════════════════════════════════════════════════════════════"
    echo ""
    echo "  [1] Recovery mode 1 (ignore corrupt pages)"
    echo "  [2] Recovery mode 2 (prevent background operations)"
    echo "  [3] Recovery mode 3 (prevent transaction rollbacks)"
    echo "  [4] Recovery mode 4 (prevent insert buffer merge)"
    echo "  [5] Recovery mode 5 (skip redo log)"
    echo "  [6] Recovery mode 6 (skip page checksums - most aggressive)"
    echo "  [A] Auto-escalate to next mode"
    echo ""
    echo -n "Select: "
    read -r recovery_choice

    case "$recovery_choice" in
        [1-6])
            FORCE_RECOVERY="$recovery_choice"
            print_warning "Will retry with recovery mode $FORCE_RECOVERY"
            return 0
            ;;
        A|a)
            print_warning "Will auto-escalate to next recovery mode"
            return 0
            ;;
        *)
            print_error "Invalid selection. Returning to menu."
            return 1
            ;;
    esac
}

# Check available disk space (CRITICAL SAFETY CHECK #3)
check_disk_space() {
    local target_dir="$1"
    local estimated_size_mb="${2:-100}"  # Minimum 100MB default

    print_info "Checking available disk space..."

    # Get available space in MB
    local available_mb=$(df -BM "$target_dir" | awk 'NR==2 {print $4}' | tr -d 'M')
    local available_gb=$(awk "BEGIN {printf \"%.2f\", $available_mb/1024}")

    # Require at least 2x the estimated size (safety margin)
    local required_mb=$((estimated_size_mb * 2))
    local required_gb=$(awk "BEGIN {printf \"%.2f\", $required_mb/1024}")

    echo "  Available space: ${available_gb}GB (${available_mb}MB)"
    echo "  Recommended minimum: ${required_gb}GB"

    # Check if we have enough space
    if [ -n "$available_mb" ] && [ "$available_mb" -lt "$required_mb" ]; then
        print_error "Insufficient disk space!"
        echo ""
        echo "  Available: ${available_gb}GB"
        echo "  Required: ${required_gb}GB"
        echo "  Shortage: $(awk "BEGIN {printf \"%.2f\", ($required_mb - $available_mb)/1024}")GB"
        echo ""
        echo "Free up space or use a different directory."
        return 1
    fi

    print_success "Sufficient disk space available (${available_gb}GB free)"
    return 0
}

# Validate force recovery level warnings (CRITICAL SAFETY CHECK #6)
warn_force_recovery() {
    local level="${1:-0}"

    if [ -z "$level" ] || [ "$level" -eq 0 ]; then
        return 0
    fi

    echo ""
    print_warning "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
    print_warning "  FORCE RECOVERY MODE ACTIVE: Level $level"
    print_warning "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
    echo ""

    case $level in
        1)
            echo "Level 1: Ignore Corrupt Pages"
            echo "  - Data Loss: Minimal (only corrupt pages skipped)"
            echo "  - Safety: Relatively safe"
            ;;
        2)
            echo "Level 2: Prevent Background Operations"
            echo "  - Data Loss: Minimal"
            echo "  - Safety: Moderate"
            ;;
        3)
            echo "Level 3: Prevent Transaction Rollbacks"
            echo "  - Data Loss: Some uncommitted data may persist"
            echo "  - Safety: Moderate risk"
            ;;
        4)
            echo "Level 4: Prevent Insert Buffer Merge"
            echo "  - Data Loss: Moderate (recent inserts may be lost)"
            echo "  - Safety: Higher risk"
            ;;
        5)
            echo "Level 5: Skip Log Redo"
            echo "  - Data Loss: HIGH (recent transactions may be incomplete)"
            echo "  - Safety: HIGH RISK"
            print_error "  WARNING: May result in inconsistent data!"
            ;;
        6)
            echo "Level 6: Skip Page Checksums (MAXIMUM RECOVERY)"
            echo "  - Data Loss: VERY HIGH (corrupted data WILL be included)"
            echo "  - Safety: VERY HIGH RISK"
            print_error "  CRITICAL: Use only as LAST RESORT!"
            print_error "  Exported data WILL contain corrupted/invalid records!"
            ;;
    esac

    echo ""
    if [ "$level" -ge 5 ]; then
        print_error "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
        print_error "  DANGEROUS RECOVERY LEVEL!"
        print_error "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
        echo ""
        echo "The resulting SQL dump may contain:"
        echo "  ✗ Incomplete transactions"
        echo "  ✗ Corrupted data"
        echo "  ✗ Invalid foreign key relationships"
        echo "  ✗ Inconsistent table states"
        echo ""
        print_warning "Type 'I UNDERSTAND THE RISKS' to continue:"
        echo -n "> "
        read -r risk_confirm

        if [ "$risk_confirm" != "I UNDERSTAND THE RISKS" ]; then
            print_error "Recovery cancelled for safety"
            echo "Consider using a lower recovery level or older backup."
            return 1
        fi

        echo ""
        print_success "Risk acknowledgment confirmed"
    fi

    echo ""
    return 0
}

# Start second MySQL instance
start_second_instance() {
    local datadir="$1"
    local force_recovery="${2:-}"

    print_info "Starting second MySQL instance..."
    print_warning "This may take a few moments..."

    # CRITICAL SAFETY CHECK: Ensure we're not using live MySQL data directory
    if [ "$datadir" = "/var/lib/mysql" ] || [ "$datadir" = "$LIVE_DATADIR" ]; then
        print_error "CRITICAL SAFETY ERROR: Attempting to use LIVE MySQL data directory!"
        echo ""
        echo "Data directory specified: $datadir"
        echo "Live MySQL directory: $LIVE_DATADIR"
        echo ""
        print_error "This script must use a SEPARATE restore directory"
        print_error "NEVER run on the live MySQL data directory"
        echo ""
        echo "Expected restore directory format:"
        echo "  /home/temp/restore*/mysql"
        echo "  /root/restore*/mysql"
        echo "  Any path OTHER than $LIVE_DATADIR"
        echo ""
        return 1
    fi

    # Display isolation confirmation
    echo ""
    print_success "Safety checks passed:"
    echo "  Second instance datadir: $datadir"
    echo "  Second instance socket: $datadir/socket.mysql"
    echo "  Live MySQL datadir: $LIVE_DATADIR"
    echo "  Live MySQL socket: /var/lib/mysql/mysql.sock (PROTECTED)"
    echo ""

    # Clear or backup old error log
    if [ -f "$datadir/mysql.err" ]; then
        print_info "Backing up old error log..."
        mv "$datadir/mysql.err" "$datadir/mysql.err.old" 2>/dev/null || true
    fi

    # Check if socket already exists (instance already running)
    if [ -S "$datadir/socket.mysql" ]; then
        print_warning "Socket file already exists. Attempting to shut down existing instance..."

        # Use proper shutdown validation (same as stop_second_instance)
        mysqladmin -h localhost -S "$datadir/socket.mysql" shutdown 2>/dev/null || true

        # Wait for socket to disappear (up to 5 seconds)
        local cleanup_wait=0
        while [ -S "$datadir/socket.mysql" ] && [ "$cleanup_wait" -lt 5 ]; do
            sleep 1
            cleanup_wait=$((cleanup_wait + 1))
        done

        # If socket still exists, try force removal
        if [ -S "$datadir/socket.mysql" ]; then
            print_warning "Existing instance didn't shut down cleanly. Force removing socket..."
            rm -f "$datadir/socket.mysql" "$datadir/mysql.lock" 2>/dev/null || true
        fi
    fi

    # Build mysqld command
    local mysqld_cmd="/usr/libexec/mysqld"
    if ! command -v "$mysqld_cmd" &> /dev/null; then
        mysqld_cmd="mysqld"
    fi

    # Start in background - build args array to avoid eval
    local mysqld_args=(
        "--datadir=$datadir"
        "--socket=$datadir/socket.mysql"
        "--pid-file=$datadir/mysql.pid"
        "--log-error=$datadir/mysql.err"
        "--skip-grant-tables"
        "--skip-networking"
        "--user=mysql"
    )

    if [ -n "$force_recovery" ]; then
        mysqld_args+=("--innodb-force-recovery=$force_recovery")
        print_warning "Using InnoDB force recovery mode: $force_recovery"
    fi

    # Start in background
    "$mysqld_cmd" "${mysqld_args[@]}" &
    local pid=$!

    # Wait for instance to start (max 30 seconds)
    local count=0
    while [ "$count" -lt 30 ]; do
        if [ -S "$datadir/socket.mysql" ]; then
            print_success "Second MySQL instance started (PID: $pid)"

            # Give InnoDB a moment to initialize
            sleep 2

            # Check for InnoDB errors in the error log
            # Pass the selected database name so we only fail on relevant errors
            echo ""
            print_info "Checking InnoDB startup status..."
            if ! check_innodb_errors "$datadir/mysql.err" "yes" "$DATABASE_NAME"; then
                print_error "InnoDB initialization encountered errors"
                echo ""
                print_warning "Attempting to shut down second instance..."

                # Use proper shutdown validation instead of fire-and-forget
                mysqladmin -h localhost -S "$datadir/socket.mysql" shutdown 2>/dev/null || true

                # Wait for socket to disappear (up to 5 seconds)
                local error_cleanup_wait=0
                while [ -S "$datadir/socket.mysql" ] && [ "$error_cleanup_wait" -lt 5 ]; do
                    sleep 1
                    error_cleanup_wait=$((error_cleanup_wait + 1))
                done

                # Remove stale socket/lock if still present
                if [ -S "$datadir/socket.mysql" ]; then
                    rm -f "$datadir/socket.mysql" "$datadir/mysql.lock" 2>/dev/null || true
                fi

                echo ""
                print_info "Review full error log:"
                echo "  tail -100 $datadir/mysql.err"
                echo ""
                print_info "Consider using force recovery mode (re-run script, option 1-6)"
                return 1
            fi

            print_success "InnoDB initialized successfully - no critical errors detected"

            # Mark second instance as running for cleanup trap
            SECOND_INSTANCE_RUNNING=1

            return 0
        fi
        sleep 1
        count=$((count + 1))
    done

    # Check if process is still running
    if ! kill -0 "$pid" 2>/dev/null; then
        print_error "Second MySQL instance failed to start"
        echo ""

        # Check for InnoDB errors (pass selected database for filtering)
        if ! check_innodb_errors "$datadir/mysql.err" "no" "$DATABASE_NAME"; then
            echo ""
        fi

        print_info "Full error log (last 50 lines):"
        if [ -f "$datadir/mysql.err" ]; then
            tail -50 "$datadir/mysql.err"
        fi
        return 1
    fi

    print_warning "Instance started but socket not detected. Check error log:"
    echo "  tail -50 $datadir/mysql.err"
    return 1
}

# Stop second MySQL instance with proper validation
stop_second_instance() {
    local datadir="$1"

    if [ ! -S "$datadir/socket.mysql" ]; then
        # Socket doesn't exist, instance likely already stopped
        SECOND_INSTANCE_RUNNING=0
        return 0
    fi

    print_info "Shutting down second MySQL instance..."

    # Get the PID from pid file if available
    local pid=""
    if [ -f "$datadir/mysql.pid" ]; then
        pid=$(cat "$datadir/mysql.pid" 2>/dev/null)
    fi

    # Send graceful shutdown
    mysqladmin -h localhost -S "$datadir/socket.mysql" shutdown 2>/dev/null || true

    # CRITICAL FIX: Verify shutdown actually happened (not just fire-and-forget)
    # Wait up to 15 seconds for socket to disappear (indicates clean shutdown)
    local wait_count=0
    while [ -S "$datadir/socket.mysql" ] && [ "$wait_count" -lt 15 ]; do
        sleep 1
        wait_count=$((wait_count + 1))
    done

    # If socket still exists, attempt force kill
    if [ -S "$datadir/socket.mysql" ]; then
        print_warning "Socket still exists after shutdown. Forcing termination..."

        # Try to kill the process if we have the PID
        if [ -n "$pid" ] && kill -0 "$pid" 2>/dev/null; then
            kill -9 "$pid" 2>/dev/null || true
            sleep 1
        fi

        # Remove stale socket and lock files
        rm -f "$datadir/socket.mysql" "$datadir/mysql.lock" 2>/dev/null || true
    fi

    # Verify process is actually dead
    if [ -n "$pid" ] && kill -0 "$pid" 2>/dev/null; then
        print_warning "MySQL process still running after shutdown attempt. Will retry on exit."
    else
        print_success "Second instance shut down successfully"
    fi

    # Mark as no longer running
    SECOND_INSTANCE_RUNNING=0
    return 0
}

# Validate SQL dump integrity
validate_sql_dump() {
    local sql_file="$1"
    local dbname="$2"
    local datadir="$3"

    print_info "Validating SQL dump integrity..."

    local validation_errors=0

    # Check 1: File exists and is not empty
    if [ ! -f "$sql_file" ]; then
        print_error "SQL dump file not found: $sql_file"
        return 1
    fi

    local file_size=$(stat -c%s "$sql_file" 2>/dev/null || stat -f%z "$sql_file" 2>/dev/null)
    if [ -z "$file_size" ] || [ "$file_size" -lt 100 ]; then
        print_error "SQL dump file is too small ($file_size bytes) - likely incomplete"
        return 1
    fi
    print_success "  File size: $(du -h "$sql_file" | awk '{print $1}')"

    # Check 2: Completion marker
    if grep -q "Dump completed on" "$sql_file"; then
        local completion_date=$(grep "Dump completed on" "$sql_file" | tail -1)
        print_success "  Dump completion marker found: ${completion_date:0:60}..."
    else
        print_error "  Missing 'Dump completed' marker - dump may be incomplete"
        validation_errors=$((validation_errors + 1))
    fi

    # Check 3: Database name in dump
    if grep -q "^-- Database: \`$dbname\`" "$sql_file" || grep -q "^USE \`$dbname\`" "$sql_file"; then
        print_success "  Database name '$dbname' found in dump"
    else
        print_warning "  Database name '$dbname' not explicitly found in dump (may be OK)"
    fi

    # Check 4: Count CREATE TABLE statements
    local table_count=$(grep -c "^CREATE TABLE" "$sql_file" || echo "0")
    if [ "$table_count" -gt 0 ]; then
        print_success "  Found $table_count CREATE TABLE statements"
    else
        print_warning "  No CREATE TABLE statements found - database may be empty or dump incomplete"
    fi

    # Check 5: Count INSERT statements (data)
    local insert_count=$(grep -c "^INSERT INTO" "$sql_file" || echo "0")
    if [ "$insert_count" -gt 0 ]; then
        print_success "  Found $insert_count INSERT INTO statements"
    else
        print_warning "  No INSERT statements found - database may be empty or tables have no data"
    fi

    # Check 6: SQL syntax spot check (no unclosed quotes in first 100 lines)
    local syntax_errors=$(head -100 "$sql_file" | grep -E "^\s*['\"].*[^'\"];?\s*$" | wc -l || echo "0")
    if [ "$syntax_errors" -eq 0 ]; then
        print_success "  SQL syntax spot check passed"
    else
        print_warning "  Potential SQL syntax issues detected (may be false positive)"
    fi

    # Check 7: Compare with source database (if second instance still running)
    if [ -S "$datadir/socket.mysql" ]; then
        print_info "  Comparing dump with source database..."

        # Get table count from source
        local source_tables=$(mysql -h localhost -S "$datadir/socket.mysql" -NBe "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=\`$dbname\`;" 2>/dev/null || echo "0")

        if [ -n "$source_tables" ] && [ "$source_tables" -gt 0 ]; then
            if [ "$table_count" -eq "$source_tables" ]; then
                print_success "  Table count matches: $table_count tables"
            else
                print_error "  Table count mismatch: Dump has $table_count, source has $source_tables"
                validation_errors=$((validation_errors + 1))
            fi

            # Get approximate data size from source
            local source_size=$(mysql -h localhost -S "$datadir/socket.mysql" -NBe "SELECT ROUND(SUM(data_length + index_length)/1024/1024, 2) FROM information_schema.TABLES WHERE TABLE_SCHEMA=\`$dbname\`;" 2>/dev/null || echo "0")
            local dump_size_mb=$(awk "BEGIN {printf \"%.2f\", $file_size/1024/1024}")

            if [ -n "$source_size" ]; then
                print_info "  Source database size: ${source_size}MB (data+indexes)"
                print_info "  Dump file size: ${dump_size_mb}MB (uncompressed SQL)"

                # Dump is usually 1-3x the data size (reasonable range)
                local size_ratio=$(awk "BEGIN {if ($source_size > 0) printf \"%.1f\", $dump_size_mb/$source_size; else print 0}")
                if [ -n "$size_ratio" ]; then
                    print_info "  Size ratio: ${size_ratio}x (1-3x is normal for text SQL)"
                fi
            fi
        fi
    fi

    if [ -n "$validation_errors" ] && [ "$validation_errors" -gt 0 ]; then
        echo ""
        print_error "Validation completed with $validation_errors errors"
        return 1
    fi

    echo ""
    print_success "SQL dump validation PASSED - dump appears clean and complete"
    return 0
}

# Dump database from second instance
dump_database() {
    local datadir="$1"
    local dbname="$2"
    local output_file="$3"

    print_info "Creating SQL dump of database: $dbname"
    print_warning "This may take some time for large databases..."

    # PHASE 1: Enhanced database discovery (Issue #2)
    # Lists found databases and diagnoses why target might be missing
    if ! discover_and_report_databases "$datadir" "$dbname"; then
        print_error "Database discovery failed - cannot proceed with dump"
        return 1
    fi
    echo ""

    # Get table count before dump
    local table_count=$(mysql -h localhost -S "$datadir/socket.mysql" -NBe "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA=\`$dbname\`;" 2>/dev/null || echo "0")
    print_info "Database contains $table_count tables"

    # Perform dump
    echo ""
    # BUG FIX: Capture mysqldump stderr to show errors if dump fails
    local dump_stderr=$(mktemp)
    if mysqldump -h localhost -S "$datadir/socket.mysql" --single-transaction "$dbname" > "$output_file" 2>"$dump_stderr"; then
        rm -f "$dump_stderr"
        # Verify dump completed
        if grep -q "Dump completed on" "$output_file"; then
            local size=$(du -h "$output_file" | awk '{print $1}')
            print_success "Dump created: $output_file ($size)"

            # Validate the dump
            echo ""
            if ! validate_sql_dump "$output_file" "$dbname" "$datadir"; then
                print_warning "Dump created but validation found issues"
                echo ""
                echo -n "Continue anyway? (y/n): "
                read -r continue_choice
                if [ "$continue_choice" != "y" ]; then
                    return 1
                fi
            fi

            return 0
        else
            print_error "Dump appears incomplete (missing completion marker)"
            return 1
        fi
    else
        # BUG FIX: Show mysqldump errors instead of silently failing
        print_error "mysqldump failed with exit code $?"
        if [ -f "$dump_stderr" ] && [ -s "$dump_stderr" ]; then
            print_error "Error details:"
            while IFS= read -r line; do
                echo "  $line" | sed 's/^[[:space:]]*/  /'
            done < "$dump_stderr"
            rm -f "$dump_stderr"
        fi
        return 1
    fi
}

################################################################################
# INTERACTIVE WORKFLOW
################################################################################

# Display the welcome banner and script overview to the user
# Explains what the script does and shows required steps
show_intro() {
    clear
    print_banner "MySQL/MariaDB File-Based Restore"

    # Detect MySQL version first
    detect_mysql_version

    echo ""
    echo "This tool helps restore MySQL/MariaDB databases from file-based backups"
    echo "(such as Acronis) when InnoDB tables are involved."
    echo ""

    if [ "$MYSQL_VARIANT" != "unknown" ]; then
        echo "Detected Database: $MYSQL_VARIANT $MYSQL_VERSION"
        echo ""
    fi

    echo "Process Overview:"
    echo "  1. Detect live MySQL data directory (read-only check)"
    echo "  2. Validate restored data files"
    echo "  3. Start SECOND MySQL instance using restored files"
    echo "  4. Create SQL dump from second instance"
    echo "  5. Shutdown second instance and output .sql file"
    echo ""
    print_success "SAFETY GUARANTEES:"
    echo "  ✓ Uses SEPARATE MySQL instance (isolated socket/pid/datadir)"
    echo "  ✓ Your LIVE MySQL is NEVER touched, stopped, or modified"
    echo "  ✓ Second instance uses --skip-networking (no port 3306 conflicts)"
    echo "  ✓ Automatic shutdown of second instance on completion or failure"
    echo "  ✓ Second instance only reads restored files, never touches live data"
    echo ""
    print_warning "PREREQUISITES:"
    echo "  - Restored MySQL data files must already be on this server"

    # Version-specific file requirements (2025 updated)
    if [ "$MYSQL_VARIANT" = "mysql" ]; then
        local major_ver=$(echo "$MYSQL_VERSION" | cut -d'.' -f1)
        local patch_ver=$(echo "$MYSQL_VERSION" | cut -d'.' -f3)

        if [ -n "$major_ver" ] && [ "$major_ver" -ge 9 ]; then
            echo "  - Files: ibdata1, #innodb_redo/, #innodb_temp/ (opt), mysql/, sys/ (opt), target DB/"
        elif [ -n "$major_ver" ] && [ "$major_ver" -eq 8 ] && [ -n "$patch_ver" ] && [ "$patch_ver" -ge 30 ]; then
            echo "  - Files: ibdata1, #innodb_redo/, #innodb_temp/ (opt), mysql/, sys/ (opt), target DB/"
        elif [ -n "$major_ver" ] && [ "$major_ver" -ge 8 ]; then
            echo "  - Files: ibdata1, ib_logfile0/1, #innodb_temp/ (opt), mysql/, sys/ (opt), target DB/"
        else
            echo "  - Files: ibdata1, ib_logfile0/1, mysql/, sys/ (opt), target DB/"
        fi
    else
        echo "  - Files: ibdata1, ib_logfile0/1, mysql/, sys/ (opt), target DB/"
    fi

    echo "  - Files must be owned by mysql:mysql"
    echo "  - Sufficient disk space for SQL dumps"
    echo ""
    return 0
}

# Step 1: Auto-detect or prompt for live MySQL data directory
# Looks for running MySQL instance or attempts to find config file
# Sets LIVE_DATADIR variable for use in later steps
step1_detect_datadir() {
    print_banner "Step 1: Detect Live MySQL Data Directory"

    detect_mysql_datadir

    echo ""
    echo "Live MySQL Data Directory: $LIVE_DATADIR"
    echo ""
    echo -n "Is this correct? (y/n, or 0 to cancel): "
    read -r confirm

    if [ "$confirm" = "0" ]; then
        echo "Operation cancelled."
        press_enter
        return 1
    fi

    if [ "$confirm" != "y" ]; then
        echo ""
        echo -n "Enter MySQL data directory path (or 0 to cancel): "
        read -r custom_dir

        if [ -z "$custom_dir" ] || [ "$custom_dir" = "0" ]; then
            echo "Operation cancelled."
            press_enter
            return 1
        fi

        # SECURITY: Validate path to prevent traversal
        if [[ "$custom_dir" == *"../"* ]] || [[ "$custom_dir" == *"/.."* ]]; then
            print_error "Invalid path: contains path traversal sequence (..)"
            press_enter
            return 1
        fi

        if [ ! -d "$custom_dir" ]; then
            print_error "Directory does not exist: $custom_dir"
            press_enter
            return 1
        fi

        # Resolve to absolute path
        local resolved_custom=$(cd "$custom_dir" && pwd)
        LIVE_DATADIR="$resolved_custom"
        print_success "Updated data directory: $LIVE_DATADIR"
    fi

    echo ""
    press_enter
    return 0
}

# Step 2: Configure temporary location for restored MySQL data
# Allows user to choose suggested directory or provide custom path
# Validates path for safety (no traversal, not live MySQL dir)
# Sets TEMP_DATADIR variable for second MySQL instance
step2_set_restore_location() {
    print_banner "Step 2: Set Restored Data Location"

    echo "Let's set up the restore directory."
    echo ""

    # Use control panel-specific home base, fallback to /home
    local home_base="${SYS_USER_HOME_BASE:-/home}"

    # Offer to create a timestamped directory
    local suggested_dir="${home_base}/temp/restore$(date +%Y%m%d)/mysql"
    echo "Suggested directory: $suggested_dir"
    echo ""
    echo "  1) Use suggested directory (will create if needed)"
    echo "  2) Enter custom path"
    echo "  0) Cancel"
    echo ""
    echo -n "Select option: "
    read -r dir_choice

    case $dir_choice in
        0)
            echo "Operation cancelled."
            press_enter
            return 1
            ;;
        1)
            TEMP_DATADIR="$suggested_dir"
            ;;
        2)
            echo ""
            echo -n "Enter path to restored data directory (or 0 to cancel): "
            read -r restore_path

            if [ -z "$restore_path" ] || [ "$restore_path" = "0" ]; then
                echo "Operation cancelled."
                press_enter
                return 1
            fi

            # SECURITY: Validate path to prevent traversal and system directory access
            if [[ "$restore_path" == *"../"* ]] || [[ "$restore_path" == *"/.."* ]]; then
                print_error "Invalid path: contains path traversal sequence (..)"
                press_enter
                return 1
            fi

            # Prevent using live database directories
            if [ "$restore_path" = "/var/lib/mysql" ] || [[ "$restore_path" == "/var/lib/mysql/"* ]]; then
                print_error "Invalid path: cannot use live MySQL data directory (/var/lib/mysql)"
                press_enter
                return 1
            fi

            # Get absolute path for validation
            local resolved_path
            if [ -d "$restore_path" ]; then
                resolved_path=$(cd "$restore_path" && pwd)
            else
                # Path doesn't exist yet, resolve parent directory
                local parent_path=$(dirname "$restore_path")
                if [ ! -d "$parent_path" ]; then
                    print_error "Parent directory does not exist: $parent_path"
                    press_enter
                    return 1
                fi
                resolved_path=$(cd "$parent_path" && pwd)/$(basename "$restore_path")
            fi

            TEMP_DATADIR="$resolved_path"
            ;;
        *)
            print_error "Invalid option"
            press_enter
            return 1
            ;;
    esac

    # Create directory if it doesn't exist
    if [ ! -d "$TEMP_DATADIR" ]; then
        echo ""
        print_info "Creating directory: $TEMP_DATADIR"

        if mkdir -p "$TEMP_DATADIR"; then
            chown mysql:mysql "$TEMP_DATADIR"
            chmod 751 "$TEMP_DATADIR"

            # Also ensure parent temp directory has correct permissions
            local parent_temp="${home_base}/temp"
            if [ -d "$parent_temp" ]; then
                chmod 751 "$parent_temp" 2>/dev/null || true
            fi

            print_success "Directory created with mysql:mysql ownership"
        else
            print_error "Failed to create directory"
            press_enter
            return 1
        fi
    fi

    # CRITICAL: Verify directory has write permissions before using it
    if [ ! -w "$TEMP_DATADIR" ]; then
        print_error "Directory exists but is not writable: $TEMP_DATADIR"
        print_info "Please check permissions or choose a different directory"
        press_enter
        return 1
    fi

    # Show required files list
    echo ""
    print_banner "Required Files to Restore"
    echo ""
    echo "You need to restore the following files from your backup to:"
    echo "  $TEMP_DATADIR"
    echo ""
    print_warning "REQUIRED FILES:"
    echo ""
    echo "1. InnoDB System Tablespace:"
    echo "   📁 $TEMP_DATADIR/ibdata1"
    echo ""

    # Version-specific redo log files (2025 updated)
    local major_version=$(echo "$MYSQL_VERSION" | cut -d'.' -f1)
    local minor_version=$(echo "$MYSQL_VERSION" | cut -d'.' -f2)
    local patch_version=$(echo "$MYSQL_VERSION" | cut -d'.' -f3)

    if [ "$MYSQL_VARIANT" = "mysql" ] && [ -n "$major_version" ] && [ "$major_version" -ge 8 ]; then
        # Detect if MySQL 8.0.30+ or MySQL 9.0+
        local use_new_redo=0
        if [ "$major_version" -ge 9 ]; then
            use_new_redo=1
        elif [ "$major_version" -eq 8 ] && [ -n "$patch_version" ] && [ "$patch_version" -ge 30 ]; then
            use_new_redo=1
        fi

        if [ "$use_new_redo" -eq 1 ]; then
            echo "2. InnoDB Redo Logs (MySQL 8.0.30+/9.0+):"
            echo "   📁 $TEMP_DATADIR/#innodb_redo/   (entire directory)"
            echo "      Contains: #ib_redo0, #ib_redo1, ... #ib_redoN files"
        else
            echo "2. InnoDB Redo Logs (MySQL 8.0.0-8.0.29):"
            echo "   📁 $TEMP_DATADIR/ib_logfile0"
            echo "   📁 $TEMP_DATADIR/ib_logfile1"
        fi
    else
        echo "2. InnoDB Redo Logs (MySQL 5.7/MariaDB):"
        echo "   📁 $TEMP_DATADIR/ib_logfile0"
        echo "   📁 $TEMP_DATADIR/ib_logfile1"
    fi
    echo ""
    echo "3. InnoDB Temporary Tablespace (if exists):"
    echo "   📁 $TEMP_DATADIR/#innodb_temp/   (optional, contains temp_N.ibt files)"
    echo "   📁 $TEMP_DATADIR/ibtmp1   (optional, global temp tablespace)"
    echo ""
    echo "4. MySQL System Database:"
    echo "   📁 $TEMP_DATADIR/mysql/   (entire directory)"
    echo "   OR"
    echo "   📁 $TEMP_DATADIR/mysql.ibd   (single file, if using)"
    echo ""
    echo "5. Optional: System Schema (if exists in backup):"
    echo "   📁 $TEMP_DATADIR/sys/   (entire directory - recommended)"
    echo ""
    echo "6. Your Target Database(s):"
    echo "   📁 $TEMP_DATADIR/<database_name>/   (entire directory)"
    echo "   Example: $TEMP_DATADIR/myuser_wordpress/"
    echo ""
    print_info "NOTE: performance_schema is NOT needed (recreated automatically)"
    echo ""
    print_info "TIP: Use Acronis, rsync, or cp to restore these files"
    echo ""
    echo -n "Have you finished restoring all required files? (y/n, or 0 to cancel): "
    read -r files_ready

    if [ "$files_ready" = "0" ]; then
        echo "Operation cancelled - returning to menu."
        press_enter
        return
    fi

    if [ "$files_ready" != "y" ]; then
        echo ""
        print_warning "Please restore the files listed above, then re-run this script."
        press_enter
        return 1
    fi

    # Validate structure
    echo ""
    if ! validate_restore_structure "$TEMP_DATADIR"; then
        echo ""
        print_error "Data structure validation failed"
        echo ""
        print_info "Required files:"
        echo "  - ibdata1 (InnoDB system tablespace)"
        echo "  - ib_logfile0 and ib_logfile1 (MySQL 5.7/MariaDB)"
        echo "    OR #innodb_redo/ directory (MySQL 8.0+)"
        echo "  - mysql/ directory (system database)"
        echo "  - <database_name>/ directory (your target database)"
        echo ""
        press_enter
        return 1
    fi

    # Check ownership
    echo ""
    print_info "Checking file ownership..."
    local owner=$(stat -c '%U:%G' "$TEMP_DATADIR/ibdata1" 2>/dev/null || echo "unknown")

    if [ "$owner" != "mysql:mysql" ]; then
        print_warning "Files are not owned by mysql:mysql (current: $owner)"
        echo ""
        echo -n "Fix ownership now? (y/n, or 0 to cancel): "
        read -r fix_ownership

        if [ "$fix_ownership" = "0" ]; then
            echo "Operation cancelled - returning to menu."
            press_enter
            return
        fi

        if [ "$fix_ownership" = "y" ]; then
            print_info "Running: chown -R mysql:mysql $TEMP_DATADIR"
            chown -R mysql:mysql "$TEMP_DATADIR"
            print_success "Ownership updated"
        fi
    else
        print_success "File ownership is correct (mysql:mysql)"
    fi

    echo ""
    press_enter
    return 0
}

# Step 3: Allow user to select which database to extract from the restored data
# Lists available databases from TEMP_DATADIR and prompts for selection
# Validates database directory exists before proceeding
# Sets DATABASE_NAME variable for dump operation
step3_select_database() {
    print_banner "Step 3: Select Database to Restore"

    echo "Available databases in restored data:"
    echo ""

    # List directories (exclude system databases and special files)
    local databases=()
    while IFS= read -r dir; do
        local dbname=$(basename "$dir")
        # Skip system databases and special directories
        if [[ "$dbname" != "mysql" ]] && [[ "$dbname" != "sys" ]] && \
           [[ "$dbname" != "performance_schema" ]] && [[ "$dbname" != "information_schema" ]] && \
           [[ "$dbname" != "#"* ]]; then
            databases+=("$dbname")
        fi
    done < <(find "$TEMP_DATADIR" -mindepth 1 -maxdepth 1 -type d 2>/dev/null)

    if [ ${#databases[@]} -eq 0 ]; then
        print_error "No user databases found in $TEMP_DATADIR"
        press_enter
        return 1
    fi

    local i=1
    for db in "${databases[@]}"; do
        echo "  $i) $db"
        i=$((i + 1))
    done
    echo ""
    echo "  0) Cancel"
    echo ""
    echo -n "Select database number (or enter name manually): "
    read -r selection

    if [ "$selection" = "0" ]; then
        echo "Operation cancelled."
        press_enter
        return 1
    fi

    # Check if numeric selection
    if [[ "$selection" =~ ^[0-9]+$ ]] && [ "$selection" -ge 1 ] && [ "$selection" -le "${#databases[@]}" ]; then
        DATABASE_NAME="${databases[$((selection - 1))]}"
    else
        # Manual entry - validate to prevent path traversal
        if [[ "$selection" == *"/"* ]] || [[ "$selection" == *".."* ]]; then
            print_error "Invalid database name: contains invalid characters (/, ..)"
            press_enter
            return 1
        fi
        DATABASE_NAME="$selection"
    fi

    # Validate database exists
    if [ ! -d "$TEMP_DATADIR/$DATABASE_NAME" ]; then
        print_error "Database directory not found: $TEMP_DATADIR/$DATABASE_NAME"
        press_enter
        return 1
    fi

    print_success "Selected database: $DATABASE_NAME"
    echo ""
    press_enter
    return 0
}

# Step 4: Configure InnoDB recovery options and ticket information
# Allows user to set InnoDB force recovery level if needed (0-6)
# Prompts for optional ticket number for tracking purposes
# Shows analysis-based recovery recommendations from error logs
step4_configure_options() {
    print_banner "Step 4: Configure Restore Options"

    echo "Database: $DATABASE_NAME"
    echo "Data Directory: $TEMP_DATADIR"
    echo ""
    echo "Optional Settings:"
    echo ""

    # Ticket number (optional)
    echo -n "Ticket number (optional, press Enter to skip): "
    read -r ticket
    if [ -n "$ticket" ]; then
        # SECURITY: Validate ticket contains only alphanumeric and common safe chars
        if [[ "$ticket" =~ ^[a-zA-Z0-9_\-]+$ ]]; then
            TICKET_NUMBER="$ticket"
        else
            print_warning "Ticket number contains invalid characters, skipping"
        fi
    fi

    # Force recovery mode
    echo ""
    echo "InnoDB Force Recovery Mode:"
    echo "  0) No force recovery (default)"
    echo "  1) Ignore corrupt pages"
    echo "  2) Prevent background operations"
    echo "  3) Prevent transaction rollbacks"
    echo "  4) Prevent insert buffer merge"
    echo "  5) Skip log redo"
    echo "  6) Skip page checksums"
    echo ""
    echo -n "Select recovery mode (0-6, or press Enter for 0): "
    read -r recovery_mode

    if [ -n "$recovery_mode" ]; then
        # CRITICAL: Validate recovery mode is numeric and in valid range (0-6)
        if ! { [ "$recovery_mode" -ge 0 ] && [ "$recovery_mode" -le 6 ]; } 2>/dev/null; then
            print_error "Invalid recovery mode: $recovery_mode"
            print_warning "Recovery mode must be numeric value between 0 and 6"
            FORCE_RECOVERY=""
        elif [ "$recovery_mode" != "0" ]; then
            FORCE_RECOVERY="$recovery_mode"
            print_warning "Will use --innodb-force-recovery=$FORCE_RECOVERY"
            echo ""

            # Show force recovery warnings and get confirmation
            if ! warn_force_recovery "$FORCE_RECOVERY"; then
                echo ""
                print_info "Recovery mode cancelled. Returning to default (level 0)."
                FORCE_RECOVERY=""
            fi
        fi
    fi

    echo ""
    press_enter
    return 0
}

# Step 5: Create SQL dump from the restored database using second MySQL instance
# Starts isolated MySQL instance, dumps selected database, validates integrity
# Generates .sql file with optional ticket number in filename
# Cleans up second instance and provides import instructions
step5_create_dump() {
    print_banner "Step 5: Create SQL Dump"

    echo "Summary:"
    echo "  Database: $DATABASE_NAME"
    echo "  Data Directory: $TEMP_DATADIR"
    if [ -n "$TICKET_NUMBER" ]; then
        echo "  Ticket: $TICKET_NUMBER"
    fi
    if [ -n "$FORCE_RECOVERY" ]; then
        echo "  Force Recovery: Level $FORCE_RECOVERY"
    fi
    echo ""
    echo "This will:"
    echo "  1. Start a second MySQL instance using the restored data"
    echo "  2. Create an SQL dump of the database"
    echo "  3. Save the dump to the current directory"
    echo ""

    print_warning "The second MySQL instance will run on a separate socket."
    print_warning "Your live MySQL instance will NOT be affected."

    echo ""
    echo -n "Proceed with dump creation? (y/n): "
    read -r confirm

    if [ "$confirm" != "y" ]; then
        echo "Operation cancelled."
        press_enter
        return 1
    fi

    echo ""
    echo "════════════════════════════════════════════════════════════════"
    echo "STARTING RESTORE PROCESS"
    echo "════════════════════════════════════════════════════════════════"
    echo ""

    # PHASE 1: Pre-flight validation (Issue #1)
    if ! validate_backup_files "$TEMP_DATADIR"; then
        print_error "Pre-flight validation failed"
        press_enter
        return 1
    fi
    echo ""

    # Check disk space before proceeding
    print_info "Checking available disk space..."
    if ! check_disk_space "$(pwd)" 500; then
        press_enter
        return 1
    fi
    echo ""

    # Start second instance
    if ! start_second_instance "$TEMP_DATADIR" "$FORCE_RECOVERY"; then
        print_error "Failed to start second MySQL instance"
        echo ""

        # Show quick retry menu first - lets user pick recovery mode directly
        # - 0 = user wants to retry (FORCE_RECOVERY updated by function)
        # - 1 = user wants to return to menu
        if show_quick_retry_menu; then
            # User chose to retry with specific mode - return 2 to signal "retry immediately"
            # (bypass auto-escalation in menu loop)
            echo ""
            return 2
        else
            # User wants to return to menu or see full troubleshooting
            # Ask if they want to see full recovery options
            echo ""
            echo -n "Would you like to see full troubleshooting options? (y/n): "
            read -r show_full
            if [ "$show_full" = "y" ]; then
                echo ""
                if show_recovery_options "$TEMP_DATADIR" "$FORCE_RECOVERY" "$DATABASE_NAME"; then
                    echo ""
                    return 2
                fi
            fi
            echo ""
            return 1
        fi
    fi

    echo ""

    # PHASE 2: Error log monitoring (Issue #4)
    local error_log="$TEMP_DATADIR/mysql.err"
    print_info "Checking error log for critical issues..."
    if ! check_error_log_for_issues "$error_log"; then
        print_warning "Error log shows potential issues"
        echo ""
        local suggest=$(suggest_recovery_mode_from_errors "$error_log" "$FORCE_RECOVERY")
        if [ -n "$suggest" ] && [ "$suggest" != "0" ]; then
            local suggested_mode=$(echo "$suggest" | cut -d':' -f2)
            print_warning "Consider trying recovery mode $suggested_mode"
        fi
        echo ""
        echo -n "Continue with dump attempt? (y/n): "
        read -r continue_choice
        if [ "$continue_choice" != "y" ]; then
            stop_second_instance "$TEMP_DATADIR"
            print_warning "You can retry with a different recovery mode when you re-run the script."
            press_enter
            return 1
        fi
        echo ""
    fi
    echo ""

    # PHASE 1: System table validation (Issue #3)
    if ! test_system_tables "$TEMP_DATADIR"; then
        print_warning "System table checks detected issues"
        echo ""
        echo -n "Continue anyway? (y/n): "
        read -r continue_choice
        if [ "$continue_choice" != "y" ]; then
            stop_second_instance "$TEMP_DATADIR"
            press_enter
            return 1
        fi
        echo ""
    fi
    echo ""

    # Generate output filename - save to parent directory of TEMP_DATADIR
    # e.g., if TEMP_DATADIR is /home/temp/restore20251210/mysql
    # then output goes to /home/temp/restore20251210/
    local timestamp=$(date +%Y%m%d_%H%M%S)
    local output_dir="$(dirname "$TEMP_DATADIR")"
    local output_file="${output_dir}/${DATABASE_NAME}_restored_${timestamp}.sql"
    if [ -n "$TICKET_NUMBER" ]; then
        output_file="${output_dir}/${DATABASE_NAME}_ticket${TICKET_NUMBER}_${timestamp}.sql"
    fi

    print_info "SQL dump will be saved to: $output_file"
    echo ""

    # Create dump
    if ! dump_database "$TEMP_DATADIR" "$DATABASE_NAME" "$output_file"; then
        print_error "Failed to create dump"
        stop_second_instance "$TEMP_DATADIR"
        return 1
    fi

    echo ""

    # Stop second instance
    stop_second_instance "$TEMP_DATADIR"

    echo ""
    echo "════════════════════════════════════════════════════════════════"
    print_success "RESTORE COMPLETE!"
    echo "════════════════════════════════════════════════════════════════"
    echo ""
    echo "SQL Dump Created: $output_file"
    echo ""
    echo "Next Steps:"
    echo "  1. Verify dump integrity:"
    echo "     grep 'Dump completed on' '$output_file'"
    echo ""
    echo "  2. Import to live database:"
    echo "     mysql $DATABASE_NAME < '$output_file'"
    echo ""
    echo "  3. Or create fresh database first:"
    echo "     mysql -e 'DROP DATABASE IF EXISTS $DATABASE_NAME;'"
    echo "     mysql -e 'CREATE DATABASE $DATABASE_NAME;'"
    echo "     mysql $DATABASE_NAME < '$output_file'"
    echo ""

    press_enter
    return 0
}

################################################################################
# DATABASE COMPARISON: Verify Recovered Data Matches Original
################################################################################

# Compare databases: Original live MySQL vs recovered data (in temp second instance)
# Returns 0 if all tables match, 1 if discrepancies found
compare_databases() {
    local original_db="$1"
    local recovered_db="$2"
    local comparison_report="/tmp/db-comparison-report-$TICKET_NUMBER-$$.txt"

    if [ -z "$original_db" ] || [ -z "$recovered_db" ]; then
        print_error "Usage: compare_databases ORIGINAL_DATABASE RECOVERED_DATABASE"
        return 1
    fi

    print_section "DATABASE COMPARISON: Original vs Recovered"
    print_info "Original database: $original_db (live MySQL)"
    print_info "Recovered database: $recovered_db (second instance)"
    echo ""

    # Verify both databases exist
    if ! mysql -e "USE $original_db" 2>/dev/null; then
        print_error "Original database '$original_db' not found or not accessible in live MySQL"
        echo "  Check: Is live MySQL running? Is database visible? Do you have permissions?"
        return 1
    fi

    if ! mysql -S "$TEMP_DATADIR/socket.mysql" -e "USE $recovered_db" 2>/dev/null; then
        print_error "Recovered database '$recovered_db' not found in second instance"
        return 1
    fi

    # Get list of tables from both databases
    local original_tables=$(mysql -N -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$original_db' ORDER BY TABLE_NAME" 2>/dev/null)
    local recovered_tables=$(mysql -N -S "$TEMP_DATADIR/socket.mysql" -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='$recovered_db' ORDER BY TABLE_NAME" 2>/dev/null)

    local original_table_count=$(echo "$original_tables" | wc -l)
    local recovered_table_count=$(echo "$recovered_tables" | wc -l)

    echo "════════════════════════════════════════════════════════════════"
    echo "SCHEMA COMPARISON"
    echo "════════════════════════════════════════════════════════════════"
    echo ""
    printf "%-50s %-20s\n" "Metric" "Result"
    echo "────────────────────────────────────────────────────────────────"
    printf "%-50s %-20s\n" "Original table count" "$original_table_count"
    printf "%-50s %-20s\n" "Recovered table count" "$recovered_table_count"

    local schema_match=1
    if [ "$original_table_count" -ne "$recovered_table_count" ]; then
        schema_match=0
        print_warning "Table count mismatch!"
    else
        echo "✓ Table count matches"
    fi

    echo ""

    # Check for missing/extra tables
    local missing_tables=""
    local extra_tables=""

    for table in $original_tables; do
        if ! echo "$recovered_tables" | grep -q "^$table$"; then
            missing_tables="$missing_tables $table"
            schema_match=0
        fi
    done

    for table in $recovered_tables; do
        if ! echo "$original_tables" | grep -q "^$table$"; then
            extra_tables="$extra_tables $table"
            schema_match=0
        fi
    done

    if [ -n "$missing_tables" ]; then
        print_warning "Missing tables in recovered database:$missing_tables"
    fi

    if [ -n "$extra_tables" ]; then
        print_warning "Extra tables in recovered database:$extra_tables"
    fi

    if [ $schema_match -eq 1 ]; then
        echo "✓ All tables present in both databases"
    fi

    echo ""
    echo "════════════════════════════════════════════════════════════════"
    echo "ROW COUNT COMPARISON"
    echo "════════════════════════════════════════════════════════════════"
    echo ""

    local row_match=1
    local total_original_rows=0
    local total_recovered_rows=0
    local discrepancy_count=0
    local discrepancy_details=""

    for table in $original_tables; do
        # Skip extra tables
        if echo "$extra_tables" | grep -q "^$table$"; then
            continue
        fi

        local original_rows=$(mysql -N -e "SELECT COUNT(*) FROM \`$original_db\`.\`$table\`" 2>/dev/null || echo "ERROR")
        local recovered_rows=$(mysql -N -S "$TEMP_DATADIR/socket.mysql" -e "SELECT COUNT(*) FROM \`$recovered_db\`.\`$table\`" 2>/dev/null || echo "ERROR")

        total_original_rows=$((total_original_rows + ${original_rows:-0}))
        total_recovered_rows=$((total_recovered_rows + ${recovered_rows:-0}))

        if [ "$original_rows" != "$recovered_rows" ]; then
            row_match=0
            discrepancy_count=$((discrepancy_count + 1))
            local row_diff=$((recovered_rows - original_rows))
            local percent_diff=0
            if [ "$original_rows" -gt 0 ]; then
                percent_diff=$(( (row_diff * 100) / original_rows ))
            fi

            discrepancy_details="$discrepancy_details
  ✗ $table
    Original: $original_rows rows
    Recovered: $recovered_rows rows
    Difference: $row_diff rows ($percent_diff%)"
        fi
    done

    printf "%-50s %-20s %-20s\n" "Table" "Original Rows" "Recovered Rows"
    echo "────────────────────────────────────────────────────────────────────────────────"

    for table in $original_tables; do
        if echo "$extra_tables" | grep -q "^$table$"; then
            continue
        fi

        local original_rows=$(mysql -N -e "SELECT COUNT(*) FROM \`$original_db\`.\`$table\`" 2>/dev/null || echo "ERROR")
        local recovered_rows=$(mysql -N -S "$TEMP_DATADIR/socket.mysql" -e "SELECT COUNT(*) FROM \`$recovered_db\`.\`$table\`" 2>/dev/null || echo "ERROR")

        if [ "$original_rows" = "$recovered_rows" ]; then
            printf "%-50s %-20s %-20s\n" "$table" "$original_rows" "$recovered_rows ✓"
        else
            printf "%-50s %-20s %-20s\n" "$table" "$original_rows" "$recovered_rows ✗"
        fi
    done

    echo ""
    echo "Total rows:"
    printf "  Original:  %,d rows\n" "$total_original_rows" 2>/dev/null || printf "  Original:  %d rows\n" "$total_original_rows"
    printf "  Recovered: %,d rows\n" "$total_recovered_rows" 2>/dev/null || printf "  Recovered: %d rows\n" "$total_recovered_rows"

    if [ $row_match -eq 1 ]; then
        echo ""
        print_success "✓ All table row counts match!"
    else
        echo ""
        print_error "✗ Row count mismatches found ($discrepancy_count tables affected)"
        echo "$discrepancy_details"
    fi

    echo ""
    echo "════════════════════════════════════════════════════════════════"
    echo "SUMMARY"
    echo "════════════════════════════════════════════════════════════════"
    echo ""

    if [ $schema_match -eq 1 ] && [ $row_match -eq 1 ]; then
        print_success "✓ DATABASES MATCH - Recovery appears successful!"
        echo ""
        echo "The recovered database has:"
        echo "  • All tables present ($original_table_count tables)"
        echo "  • Matching row counts in all tables"
        echo "  • Total of $total_recovered_rows rows recovered"
        echo ""
        echo "Safe to import recovered dump into production database."
        return 0
    else
        print_warning "⚠ DISCREPANCIES DETECTED"
        echo ""
        echo "Issues found:"
        [ $schema_match -eq 0 ] && echo "  • Schema differences (missing/extra tables)"
        [ $row_match -eq 0 ] && echo "  • Row count differences ($discrepancy_count tables)"
        echo ""
        echo "Next steps:"
        echo "  1. Review the discrepancies above"
        echo "  2. If minor (1-2 rows), likely temporary/session data - safe to import"
        echo "  3. If major, try a higher recovery mode (higher forces better recovery)"
        echo "  4. Run comparison again after re-recovery with different mode"
        return 1
    fi
}

################################################################################
# MAIN EXECUTION
################################################################################

# Main entry point: orchestrates the 5-step workflow to extract SQL from restored backup
# Detects MySQL location, validates restore files, starts second instance,
# creates SQL dump, and provides usage instructions
# Handles errors and signal interrupts with proper cleanup
main() {
    # CRITICAL: Check all required dependencies before proceeding
    if ! check_dependencies; then
        press_enter
        exit 1
    fi

    # Show intro and loop until user confirms
    local intro_loop=0
    while [ "$intro_loop" -eq 0 ]; do
        echo ""
        show_intro
        echo -n "Continue? (y/n): "
        read -r start

        if [ "$start" = "y" ]; then
            intro_loop=1  # Exit intro loop, enter menu loop
        else
            echo "Please type 'y' to continue, or select [0] to Exit from the menu."
            press_enter
        fi
    done

    # PHASE 3: Menu loop (Issue #6)
    # Replace linear 5-step workflow with interactive menu
    # Allows jumping between steps and running multiple recoveries
    local menu_choice=""

    while true; do
        # Infinite loop - only exits with explicit "return 0" on option [0]
        show_step_menu
        read -r menu_choice

        # Ensure menu_choice is not empty (handle EOF/Ctrl-D)
        if [ -z "$menu_choice" ]; then
            print_error "Invalid option (empty input). Returning to menu."
            press_enter
            continue
        fi

        case $menu_choice in
            1)
                # Step 1: Detect live data directory
                CURRENT_STEP=1
                while ! step1_detect_datadir; do
                    echo ""
                    echo -n "Retry? (y/n): "
                    read -r retry
                    if [ "$retry" != "y" ]; then
                        break
                    fi
                done
                ;;
            2)
                # Step 2: Set restore location
                if ! can_proceed_to_step 2; then
                    press_enter
                    continue
                fi
                CURRENT_STEP=2
                while ! step2_set_restore_location; do
                    echo ""
                    echo -n "Retry? (y/n): "
                    read -r retry
                    if [ "$retry" != "y" ]; then
                        break
                    fi
                done
                ;;
            3)
                # Step 3: Select database
                if ! can_proceed_to_step 3; then
                    press_enter
                    continue
                fi
                CURRENT_STEP=3
                while ! step3_select_database; do
                    echo ""
                    echo -n "Retry? (y/n): "
                    read -r retry
                    if [ "$retry" != "y" ]; then
                        break
                    fi
                done
                ;;
            4)
                # Step 4: Configure options
                if ! can_proceed_to_step 4; then
                    press_enter
                    continue
                fi
                CURRENT_STEP=4
                step4_configure_options
                ;;
            5)
                # PHASE 3: Step 5 with auto-escalation (Issue #5)
                # Step 5: Create dump with automatic recovery mode escalation
                if ! can_proceed_to_step 5; then
                    press_enter
                    continue
                fi
                CURRENT_STEP=5

                while true; do
                    # Track the attempt
                    track_recovery_attempt "$FORCE_RECOVERY"

                    step5_create_dump
                    local dump_result=$?

                    if [ "$dump_result" -eq 0 ]; then
                        # Success - exit step 5 loop
                        break
                    elif [ "$dump_result" -eq 2 ]; then
                        # User chose specific recovery mode - continue loop to retry immediately
                        # (skip auto-escalation logic)
                        echo ""
                        echo "════════════════════════════════════════════════════════════════"
                        print_info "Retrying with user-selected recovery mode $FORCE_RECOVERY..."
                        echo "════════════════════════════════════════════════════════════════"
                        echo ""
                        continue
                    fi

                    # Dump failed (return code 1) - check if auto-escalation should happen
                    print_warning "Dump creation failed"
                    echo ""

                    # PHASE 3: Auto-escalation (Issue #5)
                    # For repeated failures, auto-suggest next mode
                    if [ "$RECOVERY_ATTEMPTS" -gt 1 ]; then
                        # Multiple attempts - try auto-escalation
                        local next_mode=$(get_next_recovery_mode "$FORCE_RECOVERY")

                        if [ "$next_mode" != "$FORCE_RECOVERY" ]; then
                            print_warning "Auto-escalating recovery mode: ${FORCE_RECOVERY:-0} → $next_mode"
                            FORCE_RECOVERY="$next_mode"
                            echo ""
                            print_info "Retrying dump creation with recovery mode $FORCE_RECOVERY..."
                            echo ""
                            continue
                        else
                            print_error "Cannot escalate recovery mode further (already at mode 6)"
                            print_error "Recovery not possible with available modes"
                            break
                        fi
                    else
                        # First failure - offer user choice
                        if prompt_retry_with_recovery_mode "$FORCE_RECOVERY" "$TEMP_DATADIR/mysql.err"; then
                            # User wants to retry with different mode
                            echo ""
                            print_info "Retrying dump creation with recovery mode $FORCE_RECOVERY..."
                            continue
                        else
                            # User doesn't want to retry
                            break
                        fi
                    fi
                done

                # After step 5, return to menu
                echo ""
                ;;
            C|c)
                # Compare original vs recovered database
                if [ -z "$DATABASE_NAME" ]; then
                    print_error "No database selected. Complete Step 3 first."
                    press_enter
                else
                    # Check if second instance is running
                    if [ ! -S "$TEMP_DATADIR/socket.mysql" ]; then
                        print_warning "Second instance not running. Starting temporary instance..."
                        echo ""
                        if ! start_second_instance "$TEMP_DATADIR"; then
                            print_error "Failed to start second instance for comparison"
                            press_enter
                        else
                            echo ""
                            # Run comparison
                            if compare_databases "$DATABASE_NAME" "$DATABASE_NAME"; then
                                print_success "Comparison complete - databases match!"
                            else
                                print_warning "Comparison complete - discrepancies found"
                            fi
                            echo ""
                            # Ask if user wants to keep instance running or stop it
                            echo -n "Keep second instance running? (y/n): "
                            read -r keep_running
                            if [ "$keep_running" != "y" ]; then
                                stop_second_instance "$TEMP_DATADIR"
                            fi
                            press_enter
                        fi
                    else
                        # Instance already running, proceed with comparison
                        echo ""
                        if compare_databases "$DATABASE_NAME" "$DATABASE_NAME"; then
                            print_success "Comparison complete - databases match!"
                        else
                            print_warning "Comparison complete - discrepancies found"
                        fi
                        press_enter
                    fi
                fi
                ;;
            R|r)
                # Review current state
                show_current_state
                press_enter
                ;;
            G|g)
                # Guided process mode - walks through all steps automatically
                run_guided_process
                ;;
            0)
                # Exit to main menu
                echo ""
                print_info "Returning to main menu..."
                sleep 1
                return 0
                ;;
            *)
                print_error "Invalid option: $menu_choice"
                press_enter
                ;;
        esac
    done

    # Loop is infinite - script never exits naturally
}

# Run main function
main
