#!/bin/bash
# PHP Analysis Engine - Analyzes PHP configurations and identifies issues
# Part of Server Toolkit - Phase 2: Analysis
# Dependencies: lib/php-detector.sh, lib/system-detect.sh

# Source guard - prevent re-sourcing (but allow re-initialization if needed)
if [ -n "${_PHP_ANALYZER_LOADED:-}" ]; then
    return 0
fi
readonly _PHP_ANALYZER_LOADED=1

# Source required libraries only if not already loaded
if [ -z "${_PHP_DETECTOR_LOADED:-}" ]; then
    _LIB_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
    source "$_LIB_DIR/php-detector.sh" 2>/dev/null || { echo "ERROR: php-detector.sh not found"; return 1; }
fi

if [ -z "${_SYSTEM_DETECT_LOADED:-}" ]; then
    _LIB_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
    source "$_LIB_DIR/system-detect.sh" 2>/dev/null || { echo "ERROR: system-detect.sh not found"; return 1; }
fi

# ============================================================================
# HELPER FUNCTIONS - PURE BASH OPTIMIZATIONS
# ============================================================================

# Extract field from pipe-delimited string (pure bash - no cut command)
# Usage: get_field "value1|value2|value3" 2
# Returns: value2
get_field() {
    local input="$1"
    local field_num="$2"
    local temp="$input"
    local i=1

    # Skip to the desired field
    while [ $i -lt "$field_num" ]; do
        temp="${temp#*|}"
        i=$((i + 1))
    done

    # Extract the field
    echo "${temp%%|*}"
}

# ============================================================================
# ERROR LOG ANALYSIS
# ============================================================================

# Analyze PHP error logs for memory exhausted errors
# Usage: analyze_memory_exhausted_errors <username> <days>
# Returns: count|file pairs
analyze_memory_exhausted_errors() {
    local username="$1"
    local days="${2:-7}"  # Default last 7 days

    local error_logs
    error_logs=$(find_php_error_logs "$username")

    if [ -z "$error_logs" ]; then
        echo "0|No logs found"
        return
    fi

    local total_count=0
    local results=""

    while IFS= read -r log_file; do
        [ ! -f "$log_file" ] && continue

        # Find errors in last N days
        local count
        count=$(find "$log_file" -mtime -"$days" -exec grep -c "Allowed memory size.*exhausted" {} \; 2>/dev/null || echo "0")
        count="${count:-0}"

        if [ "$count" -gt 0 ]; then
            total_count=$((total_count + count))
            results+="$count|$log_file"$'\n'
        fi
    done <<< "$error_logs"

    echo -e "$total_count|TOTAL\n$results"
}

# Analyze PHP-FPM error logs for max_children errors
# Usage: analyze_max_children_errors <username> <days>
# Returns: count|timestamp|pool format
analyze_max_children_errors() {
    local username="$1"
    local days="${2:-7}"

    local fpm_logs
    fpm_logs=$(find_fpm_error_logs "$username")

    if [ -z "$fpm_logs" ]; then
        echo "0|No FPM logs found"
        return
    fi

    local total_count=0
    local results=""

    while IFS= read -r log_file; do
        [ ! -f "$log_file" ] && continue

        # Find max_children errors with timestamps
        local errors
        errors=$(find "$log_file" -mtime -"$days" -exec grep -E "server reached (pm\.)?max_children" {} \; 2>/dev/null)

        if [ -n "$errors" ]; then
            local count
            count=$(echo "$errors" | wc -l)
            total_count=$((total_count + count))

            # Extract most recent occurrence
            local last_occurrence
            last_occurrence=$(echo "$errors" | tail -1 | grep -oE '\[[0-9]{2}-[A-Za-z]{3}-[0-9]{4} [0-9]{2}:[0-9]{2}:[0-9]{2}\]' | tr -d '[]')

            results+="$count|$last_occurrence|$(basename "$log_file")"$'\n'
        fi
    done <<< "$fpm_logs"

    echo -e "$total_count|TOTAL\n$results"
}

# Analyze slow request logs
# Usage: analyze_slow_requests <username> <days> <threshold_seconds>
# Returns: count|script|duration format
analyze_slow_requests() {
    local username="$1"
    local days="${2:-7}"
    local threshold="${3:-5}"  # Default 5 seconds

    local slow_logs
    slow_logs=$(find_fpm_slow_logs "$username")

    if [ -z "$slow_logs" ]; then
        echo "0|No slow logs found"
        return
    fi

    local total_count=0
    local results=""
    declare -A slow_scripts

    while IFS= read -r log_file; do
        [ ! -f "$log_file" ] && continue

        # Parse slow log format
        # [pool www] pid 12345
        # script_filename = /path/to/script.php
        # [duration] 7.123456

        local entries
        entries=$(find "$log_file" -mtime -"$days" -exec grep -A2 "^\[" {} \; 2>/dev/null)

        if [ -n "$entries" ]; then
            local script=""
            local duration=""

            while IFS= read -r line; do
                if [[ "$line" =~ script_filename.*=\ (.+)$ ]]; then
                    script="${BASH_REMATCH[1]}"
                elif [[ "$line" =~ ^\[.*\]\ ([0-9]+\.[0-9]+)$ ]]; then
                    duration="${BASH_REMATCH[1]}"

                    # Check if exceeds threshold
                    if [ -n "$script" ] && [ -n "$duration" ]; then
                        local duration_int=${duration%.*}
                        if [ "$duration_int" -ge "$threshold" ]; then
                            total_count=$((total_count + 1))

                            # Track slowest occurrence per script
                            local is_slower=$(awk "BEGIN {print (${slow_scripts[$script]:-0} < $duration ? 1 : 0)}" 2>/dev/null || echo 1)
                            if [ -z "${slow_scripts[$script]}" ] || [ "$is_slower" -eq 1 ]; then
                                slow_scripts[$script]="$duration"
                            fi
                        fi
                    fi

                    script=""
                    duration=""
                fi
            done <<< "$entries"
        fi
    done <<< "$slow_logs"

    # Format results
    for script in "${!slow_scripts[@]}"; do
        results+="1|$script|${slow_scripts[$script]}s"$'\n'
    done

    echo -e "$total_count|TOTAL\n$results"
}

# Analyze execution timeout errors
# Usage: analyze_execution_timeout_errors <username> <days>
analyze_execution_timeout_errors() {
    local username="$1"
    local days="${2:-7}"

    local error_logs
    error_logs=$(find_php_error_logs "$username")

    if [ -z "$error_logs" ]; then
        echo "0|No logs found"
        return
    fi

    local total_count=0
    local results=""

    while IFS= read -r log_file; do
        [ ! -f "$log_file" ] && continue

        local count
        count=$(find "$log_file" -mtime -"$days" -exec grep -c "Maximum execution time.*exceeded" {} \; 2>/dev/null || echo "0")

        if [ "$count" -gt 0 ]; then
            total_count=$((total_count + count))
            results+="$count|$log_file"$'\n'
        fi
    done <<< "$error_logs"

    echo -e "$total_count|TOTAL\n$results"
}

# ============================================================================
# RESOURCE USAGE CALCULATIONS
# ============================================================================

# Calculate average memory per PHP-FPM process
# Usage: calculate_memory_per_process <username>
# Returns: average_kb|total_processes|total_memory_mb
calculate_memory_per_process() {
    local username="$1"

    # Get average KB per process (single number)
    local avg_kb
    avg_kb=$(get_fpm_memory_usage "$username")

    # Get process count
    local process_count
    process_count=$(get_fpm_process_count "$username")

    # Check if no processes found
    if [ -z "$avg_kb" ] || [ "$avg_kb" -eq 0 ] || [ "$process_count" -eq 0 ]; then
        echo "0|0|0"
        return
    fi

    # Calculate total memory in MB
    local total_mb
    total_mb=$((avg_kb * process_count / 1024))

    echo "$avg_kb|$process_count|$total_mb"
}

# Calculate optimal max_children based on available memory
# Usage: calculate_optimal_max_children <username> [reserved_mb]
# Returns: recommended_max_children|reason
calculate_optimal_max_children() {
    local username="$1"
    local reserved_mb="${2:-1024}"  # Reserve 1GB for system by default

    # Get current memory usage
    local memory_stats
    memory_stats=$(calculate_memory_per_process "$username")

    local avg_kb process_count
    avg_kb=$(get_field "$memory_stats" 1)
    process_count=$(get_field "$memory_stats" 2)

    if [ "$avg_kb" -eq 0 ]; then
        echo "0|No active processes to measure"
        return
    fi

    # Get total system memory
    local total_mem_mb
    total_mem_mb=$(free -m | awk '/^Mem:/ {print $2}')

    # Calculate available memory for PHP-FPM
    local available_mb=$((total_mem_mb - reserved_mb))

    # Convert average KB to MB
    local avg_mb=$((avg_kb / 1024))

    # Calculate max children (with 20% safety buffer)
    # Protect against division by zero
    local theoretical_max=5
    if [ "$avg_mb" -gt 0 ]; then
        theoretical_max=$((available_mb / avg_mb))
    fi
    local recommended=$((theoretical_max * 80 / 100))

    # Sanity checks
    if [ "$recommended" -lt 5 ]; then
        recommended=5
        echo "$recommended|Minimum safe value (memory very limited)"
    elif [ "$recommended" -lt "$process_count" ]; then
        echo "$recommended|WARNING: Less than current process count ($process_count)"
    else
        echo "$recommended|Based on ${avg_mb}MB avg per process, ${available_mb}MB available"
    fi
}

# Calculate peak concurrent requests from access logs
# Usage: calculate_peak_concurrent_requests <username> <days>
# Returns: peak_concurrent|timestamp
calculate_peak_concurrent_requests() {
    local username="$1"
    local days="${2:-1}"  # Default last 24 hours

    # Find access logs
    local access_logs
    access_logs=$(find /home/"$username"/*/logs -name "access_log*" -o -name "access.log*" 2>/dev/null)

    if [ -z "$access_logs" ]; then
        echo "0|No access logs found"
        return
    fi

    # Analyze logs in 1-second windows to find peak concurrency
    # This is a simplified estimation based on request timestamps
    local peak=0
    local peak_time=""

    while IFS= read -r log_file; do
        [ ! -f "$log_file" ] && continue

        # Extract timestamps and count requests per second
        local log_data
        if [[ "$log_file" == *.gz ]]; then
            log_data=$(zcat "$log_file" 2>/dev/null || continue)
        else
            log_data=$(cat "$log_file" 2>/dev/null || continue)
        fi

        # Apache/Nginx common log format timestamp extraction
        local per_second
        per_second=$(echo "$log_data" | \
            grep -oE '\[[0-9]{2}/[A-Za-z]{3}/[0-9]{4}:[0-9]{2}:[0-9]{2}:[0-9]{2}' | \
            sort | uniq -c | sort -rn | head -1)

        if [ -n "$per_second" ]; then
            local count timestamp
            count=$(echo "$per_second" | awk '{print $1}')
            timestamp=$(echo "$per_second" | awk '{print $2}' | tr -d '[')

            if [ "$count" -gt "$peak" ]; then
                peak=$count
                peak_time=$timestamp
            fi
        fi
    done <<< "$access_logs"

    echo "$peak|$peak_time"
}

# Calculate requests per minute average
# Usage: calculate_avg_requests_per_minute <username> <hours>
calculate_avg_requests_per_minute() {
    local username="$1"
    local hours="${2:-24}"

    local access_logs
    access_logs=$(find /home/"$username"/*/logs -name "access_log" -o -name "access.log" 2>/dev/null | head -1)

    if [ -z "$access_logs" ]; then
        echo "0|No access logs"
        return
    fi

    # Count total requests in last N hours
    local total_requests
    total_requests=$(find "$access_logs" -mmin -$((hours * 60)) -exec wc -l {} \; 2>/dev/null | awk 'BEGIN {sum=0} {sum+=$1} END {print sum}')

    if [ -z "$total_requests" ] || [ "$total_requests" -eq 0 ]; then
        echo "0|No recent requests"
        return
    fi

    # Calculate average per minute
    local total_minutes=$((hours * 60))
    local avg_per_min=$((total_requests / total_minutes))

    echo "$avg_per_min|Last $hours hours"
}

# Advanced per-domain traffic analysis with 7-day patterns and bot filtering
# Usage: analyze_domain_traffic_advanced <domain> <username> [days]
# Returns: avg_rpm|peak_rpm|95th_percentile|total_requests|legit_percentage|bot_percentage
analyze_domain_traffic_advanced() {
    local domain="$1"
    local username="$2"
    local days="${3:-7}"

    # Source bot signatures if not already loaded
    if [ -z "${LEGIT_BOTS+x}" ]; then
        local lib_dir="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
        source "$lib_dir/bot-signatures.sh" 2>/dev/null || {
            echo "0|0|0|0|100|0|ERROR: bot-signatures.sh not found"
            return 1
        }
    fi

    # Find domain access logs (cPanel format)
    local access_log="/home/$username/access-logs/$domain"

    # Fallback to other common locations
    if [ ! -f "$access_log" ]; then
        access_log=$(find /home/"$username"/*/logs -name "$domain*access*log" 2>/dev/null | head -1)
    fi

    if [ ! -f "$access_log" ]; then
        echo "0|0|0|0|100|0|No access log found for $domain"
        return 1
    fi

    # Calculate time window cutoff date (YYYYMMDD format for fast string comparison)
    local cutoff_date=$(date -d "$days days ago" +%Y%m%d 2>/dev/null || date -v-${days}d +%Y%m%d 2>/dev/null)

    # Arrays to store per-minute request counts and categorization
    declare -A requests_per_minute
    local total_requests=0
    local legit_requests=0
    local bot_requests=0
    local skipped_old=0

    # Process access log
    # Apache log format: IP - - [DD/Mon/YYYY:HH:MM:SS +ZONE] "REQUEST" status size "referer" "user-agent"
    # OPTIMIZED: Pure bash parsing - no external commands in loop
    while IFS= read -r line; do
        # Extract timestamp using pure bash - format: [DD/Mon/YYYY:HH:MM:SS +ZONE]
        # Find position of '[' and ']'
        [[ "$line" =~ \[([^]]+)\] ]] || continue
        local timestamp_raw="${BASH_REMATCH[1]}"

        # Parse: DD/Mon/YYYY:HH:MM:SS +ZONE → extract components
        # Using bash parameter expansion (no external commands!)
        local date_part="${timestamp_raw%%:*}"        # DD/Mon/YYYY
        local time_part="${timestamp_raw#*:}"         # HH:MM:SS +ZONE
        time_part="${time_part%% *}"                  # HH:MM:SS (remove timezone)

        # Parse date: DD/Mon/YYYY
        local day="${date_part%%/*}"                  # DD
        local temp="${date_part#*/}"                  # Mon/YYYY
        local mon="${temp%%/*}"                       # Mon
        local year="${temp#*/}"                       # YYYY

        # Parse time: HH:MM:SS
        local hour="${time_part%%:*}"                 # HH
        local temp2="${time_part#*:}"                 # MM:SS
        local minute="${temp2%%:*}"                   # MM

        # Convert month name to number (no change - this is already optimal)
        case "$mon" in
            Jan) month="01" ;; Feb) month="02" ;; Mar) month="03" ;;
            Apr) month="04" ;; May) month="05" ;; Jun) month="06" ;;
            Jul) month="07" ;; Aug) month="08" ;; Sep) month="09" ;;
            Oct) month="10" ;; Nov) month="11" ;; Dec) month="12" ;;
            *) continue ;;
        esac

        # Create minute bucket: YYYYMMDDHHMM
        local minute_bucket="${year}${month}${day}${hour}${minute}"

        # Skip old entries by comparing YYYYMMDD strings (fast, no date command needed)
        local log_date="${year}${month}${day}"
        if [ "$log_date" -lt "$cutoff_date" ]; then
            skipped_old=$((skipped_old + 1))
            continue
        fi

        # Extract user agent using pure bash regex (last quoted string in line)
        local user_agent=""
        if [[ "$line" =~ \"([^\"]+)\"[[:space:]]*$ ]]; then
            user_agent="${BASH_REMATCH[1]}"
        fi

        total_requests=$((total_requests + 1))
        requests_per_minute[$minute_bucket]=$((${requests_per_minute[$minute_bucket]:-0} + 1))

        # Classify traffic (legitimate vs bot) - optimized with short-circuit
        # Check most common bots first to avoid unnecessary function calls
        local is_bot=0

        # Quick common bot check using string matching (faster than function calls)
        if [[ "$user_agent" == *"bot"* ]] || [[ "$user_agent" == *"Bot"* ]] || \
           [[ "$user_agent" == *"crawler"* ]] || [[ "$user_agent" == *"spider"* ]] || \
           [[ "$user_agent" == *"Google"* ]] || [[ "$user_agent" == *"Bing"* ]]; then
            is_bot=1
        fi

        if [ "$is_bot" -eq 1 ]; then
            bot_requests=$((bot_requests + 1))
        else
            legit_requests=$((legit_requests + 1))
        fi

    done < <(tail -50000 "$access_log" 2>/dev/null)  # Limit to last 50k lines for performance

    # If no requests found
    if [ "$total_requests" -eq 0 ]; then
        echo "0|0|0|0|100|0|No requests in last $days days"
        return 0
    fi

    # Calculate statistics
    local total_minutes=${#requests_per_minute[@]}
    [ "$total_minutes" -eq 0 ] && total_minutes=1

    local avg_rpm=$((total_requests / total_minutes))

    # Calculate peak and 95th percentile
    local -a rpm_values=()
    for minute in "${!requests_per_minute[@]}"; do
        rpm_values+=("${requests_per_minute[$minute]}")
    done

    # Sort values
    local old_IFS="$IFS"
    IFS=$'\n'
    rpm_sorted=($(sort -n <<<"${rpm_values[*]}"))
    IFS="$old_IFS"

    local peak_rpm=${rpm_sorted[-1]:-0}

    # Calculate 95th percentile index
    local count=${#rpm_sorted[@]}
    local percentile_95_index=$(( count * 95 / 100 ))
    [ "$percentile_95_index" -ge "$count" ] && percentile_95_index=$((count - 1))
    local rpm_95th=${rpm_sorted[$percentile_95_index]:-0}

    # Calculate percentages
    local legit_pct=100
    local bot_pct=0
    if [ "$total_requests" -gt 0 ]; then
        legit_pct=$((legit_requests * 100 / total_requests))
        bot_pct=$((bot_requests * 100 / total_requests))
    fi

    echo "$avg_rpm|$peak_rpm|$rpm_95th|$total_requests|$legit_pct|$bot_pct"
}

# ============================================================================
# OPCACHE ANALYSIS
# ============================================================================

# Analyze OPcache effectiveness
# Usage: analyze_opcache_effectiveness <username>
# Returns: status|hit_rate|memory_used_mb|cached_scripts|recommendation
analyze_opcache_effectiveness() {
    local username="$1"

    # Check if OPcache is enabled
    local enabled
    enabled=$(check_opcache_enabled "$username")

    if [ "$enabled" != "1" ]; then
        echo "DISABLED|0|0|0|Enable OPcache for 40-70% performance boost"
        return
    fi

    # Get OPcache statistics
    local stats
    stats=$(get_opcache_stats "$username")

    if [ -z "$stats" ]; then
        echo "ENABLED|0|0|0|Unable to retrieve statistics"
        return
    fi

    # Parse statistics
    local memory_used hits misses cached_scripts max_cached wasted
    memory_used=$(echo "$stats" | grep "memory_usage_mb" | cut -d'=' -f2)
    hits=$(echo "$stats" | grep "^hits=" | cut -d'=' -f2)
    misses=$(echo "$stats" | grep "^misses=" | cut -d'=' -f2)
    cached_scripts=$(echo "$stats" | grep "num_cached_scripts=" | cut -d'=' -f2)
    max_cached=$(echo "$stats" | grep "max_cached_keys=" | cut -d'=' -f2)
    wasted=$(echo "$stats" | grep "wasted_memory_mb=" | cut -d'=' -f2)

    # Calculate hit rate
    local hit_rate
    hit_rate=$(calculate_opcache_hit_rate "$username")

    # Generate recommendation
    local recommendation=""
    local hit_rate_low=$(awk "BEGIN {print ($hit_rate < 90 ? 1 : 0)}" 2>/dev/null || echo 0)
    local wasted_high=$(awk "BEGIN {print ($wasted > 5 ? 1 : 0)}" 2>/dev/null || echo 0)
    local cached_high=$(awk "BEGIN {print ($cached_scripts > $max_cached * 0.8 ? 1 : 0)}" 2>/dev/null || echo 0)

    if [ "$hit_rate_low" -eq 1 ]; then
        recommendation="Hit rate below 90% - Increase opcache.memory_consumption"
    elif [ "$wasted_high" -eq 1 ]; then
        recommendation="High wasted memory (${wasted}MB) - Consider increasing opcache.max_accelerated_files"
    elif [ "$cached_high" -eq 1 ]; then
        recommendation="Cached scripts at 80% capacity - Increase opcache.max_accelerated_files"
    else
        recommendation="OPcache performing optimally"
    fi

    echo "ENABLED|$hit_rate|$memory_used|$cached_scripts|$recommendation"
}

# ============================================================================
# CONFIGURATION ISSUE DETECTION
# ============================================================================

# Detect common PHP configuration issues
# Usage: detect_php_config_issues <username> <domain>
# Returns: multiline issue_type|severity|message|recommendation
detect_php_config_issues() {
    local username="$1"
    local domain="$2"

    local issues=""

    # Get all effective settings
    local settings
    settings=$(get_all_php_settings "$username")

    # Extract key settings
    local memory_limit upload_max post_max max_execution display_errors
    memory_limit=$(echo "$settings" | grep "^memory_limit=" | cut -d'=' -f2)
    upload_max=$(echo "$settings" | grep "^upload_max_filesize=" | cut -d'=' -f2)
    post_max=$(echo "$settings" | grep "^post_max_size=" | cut -d'=' -f2)
    max_execution=$(echo "$settings" | grep "^max_execution_time=" | cut -d'=' -f2)
    display_errors=$(echo "$settings" | grep "^display_errors=" | cut -d'=' -f2)

    # Convert to bytes for comparison
    local upload_bytes post_bytes
    upload_bytes=$(convert_to_bytes "$upload_max")
    post_bytes=$(convert_to_bytes "$post_max")

    # ISSUE 1: post_max_size < upload_max_filesize
    if [ -n "$post_bytes" ] && [ -n "$upload_bytes" ] && [ "$post_bytes" -lt "$upload_bytes" ]; then
        issues+="CONFIG_MISMATCH|CRITICAL|post_max_size ($post_max) < upload_max_filesize ($upload_max)|Set post_max_size >= upload_max_filesize"$'\n'
    fi

    # ISSUE 2: display_errors = On in production
    if [[ "$display_errors" =~ ^(On|1)$ ]]; then
        issues+="SECURITY|HIGH|display_errors is enabled|Set display_errors = Off in production (security risk)"$'\n'
    fi

    # ISSUE 3: memory_limit too low
    local memory_bytes
    memory_bytes=$(convert_to_bytes "$memory_limit")
    if [ -n "$memory_bytes" ] && [ "$memory_bytes" -lt $((128 * 1024 * 1024)) ]; then
        issues+="PERFORMANCE|MEDIUM|memory_limit is very low ($memory_limit)|Consider increasing to at least 128M"$'\n'
    fi

    # ISSUE 4: Check for max_children errors
    local max_children_errors
    max_children_errors=$(analyze_max_children_errors "$username" 7)
    local error_count
    error_count=$(get_field "$(echo "$max_children_errors" | grep "TOTAL")" 1)

    if [ -n "$error_count" ] && [ "$error_count" -gt 0 ]; then
        issues+="CAPACITY|CRITICAL|pm.max_children limit reached $error_count times in last 7 days|Increase pm.max_children setting"$'\n'
    fi

    # ISSUE 5: Check for memory exhausted errors
    local memory_errors
    memory_errors=$(analyze_memory_exhausted_errors "$username" 7)
    local memory_error_count
    memory_error_count=$(get_field "$(echo "$memory_errors" | grep "TOTAL")" 1)
    memory_error_count="${memory_error_count:-0}"

    if [ "$memory_error_count" -gt 0 ]; then
        issues+="MEMORY|HIGH|Memory exhausted errors occurred $memory_error_count times in last 7 days|Increase memory_limit or optimize code"$'\n'
    fi

    # ISSUE 6: OPcache disabled or ineffective
    local opcache_status
    opcache_status=$(analyze_opcache_effectiveness "$username")
    local status hit_rate
    status=$(get_field "$opcache_status" 1)
    hit_rate=$(get_field "$opcache_status" 2)

    if [ "$status" = "DISABLED" ]; then
        issues+="PERFORMANCE|HIGH|OPcache is disabled|Enable OPcache for 40-70% performance improvement"$'\n'
    else
        local hit_rate_low=$(awk "BEGIN {print ($hit_rate < 90 ? 1 : 0)}" 2>/dev/null || echo 0)
        if [ "$hit_rate_low" -eq 1 ]; then
            issues+="PERFORMANCE|MEDIUM|OPcache hit rate is low (${hit_rate}%)|Increase opcache.memory_consumption"$'\n'
        fi
    fi

    # ISSUE 7: Check FPM pool configuration
    local pool_config
    pool_config=$(find_fpm_pool_config "$username")

    if [ -n "$pool_config" ] && [ -f "$pool_config" ]; then
        local pool_settings
        pool_settings=$(parse_fpm_pool_config "$pool_config")

        local pm pm_max_requests
        pm=$(echo "$pool_settings" | grep "^pm=" | cut -d'=' -f2)
        pm_max_requests=$(echo "$pool_settings" | grep "^pm.max_requests=" | cut -d'=' -f2)

        # ISSUE 7a: pm.max_requests = 0 (no process recycling)
        if [ "$pm_max_requests" = "0" ]; then
            issues+="MEMORY_LEAK|MEDIUM|pm.max_requests is disabled (0)|Set to 500-1000 to prevent memory leak accumulation"$'\n'
        fi

        # ISSUE 7b: pm = static on low-traffic site
        if [ "$pm" = "static" ]; then
            local avg_rpm
            avg_rpm=$(calculate_avg_requests_per_minute "$username" 24 | cut -d'|' -f1)

            if [ "$avg_rpm" -lt 10 ]; then
                issues+="RESOURCE_WASTE|LOW|pm=static on low-traffic site ($avg_rpm req/min)|Consider pm=dynamic or pm=ondemand to save memory"$'\n'
            fi
        fi
    fi

    # Return all issues
    if [ -z "$issues" ]; then
        echo "NONE|INFO|No critical issues detected|Configuration appears healthy"
    else
        echo -e "$issues"
    fi
}

# ============================================================================
# COMPREHENSIVE DOMAIN ANALYSIS
# ============================================================================

# Perform complete analysis for a domain
# Usage: analyze_domain_php <username> <domain>
# Returns: JSON-like formatted comprehensive report
analyze_domain_php() {
    local username="$1"
    local domain="$2"

    echo "=== PHP Analysis Report for $domain ==="
    echo ""

    # 1. PHP Version
    echo "PHP VERSION:"
    local php_version
    php_version=$(detect_php_version_for_domain "$username" "$domain")
    echo "  Version: $php_version"
    echo ""

    # 2. Configuration Files
    echo "CONFIGURATION HIERARCHY:"
    local configs
    configs=$(find_all_php_configs "$username" "$domain")
    local priority=1
    while IFS= read -r config; do
        [ -z "$config" ] && continue
        echo "  Priority $priority: $config"
        priority=$((priority + 1))
    done <<< "$configs"
    echo ""

    # 3. Key Settings
    echo "EFFECTIVE SETTINGS:"
    local memory_limit upload_max post_max max_exec
    memory_limit=$(get_effective_php_setting "$username" "memory_limit")
    upload_max=$(get_effective_php_setting "$username" "upload_max_filesize")
    post_max=$(get_effective_php_setting "$username" "post_max_size")
    max_exec=$(get_effective_php_setting "$username" "max_execution_time")

    echo "  memory_limit: $memory_limit"
    echo "  upload_max_filesize: $upload_max"
    echo "  post_max_size: $post_max"
    echo "  max_execution_time: $max_exec"
    echo ""

    # 4. PHP-FPM Pool
    echo "PHP-FPM POOL:"
    local pool_config
    pool_config=$(find_fpm_pool_config "$username")

    if [ -n "$pool_config" ] && [ -f "$pool_config" ]; then
        local pool_settings
        pool_settings=$(parse_fpm_pool_config "$pool_config")

        echo "  Config: $pool_config"
        while IFS= read -r setting; do
            [ -z "$setting" ] && continue
            echo "  $setting"
        done <<< "$pool_settings"
    else
        echo "  Status: No FPM pool config found (using mod_php?)"
    fi
    echo ""

    # 5. Process & Memory Stats
    echo "RESOURCE USAGE:"
    local memory_stats
    memory_stats=$(calculate_memory_per_process "$username")
    local avg_kb process_count total_mb
    avg_kb=$(get_field "$memory_stats" 1)
    process_count=$(get_field "$memory_stats" 2)
    total_mb=$(get_field "$memory_stats" 3)

    echo "  Current Processes: $process_count"
    echo "  Avg Memory/Process: $((avg_kb / 1024))MB"
    echo "  Total Memory: ${total_mb}MB"
    echo ""

    # 6. OPcache Status
    echo "OPCACHE STATUS:"
    local opcache_status
    opcache_status=$(analyze_opcache_effectiveness "$username")
    local status hit_rate memory_used cached_scripts recommendation
    status=$(get_field "$opcache_status" 1)
    hit_rate=$(get_field "$opcache_status" 2)
    memory_used=$(get_field "$opcache_status" 3)
    cached_scripts=$(get_field "$opcache_status" 4)
    recommendation=$(get_field "$opcache_status" 5)

    echo "  Status: $status"
    if [ "$status" = "ENABLED" ]; then
        echo "  Hit Rate: ${hit_rate}%"
        echo "  Memory Used: ${memory_used}MB"
        echo "  Cached Scripts: $cached_scripts"
    fi
    echo "  Recommendation: $recommendation"
    echo ""

    # 7. Traffic Analysis
    echo "TRAFFIC ANALYSIS (Last 24h):"
    local avg_rpm peak_concurrent
    avg_rpm=$(calculate_avg_requests_per_minute "$username" 24)
    peak_concurrent=$(calculate_peak_concurrent_requests "$username" 1)

    echo "  Avg Requests/Min: $(echo "$avg_rpm" | cut -d'|' -f1)"
    echo "  Peak Concurrent: $(echo "$peak_concurrent" | cut -d'|' -f1)"
    echo ""

    # 8. Error Analysis
    echo "ERROR ANALYSIS (Last 7 days):"
    local memory_errors max_children_errors timeout_errors slow_requests

    memory_errors=$(get_field "$(analyze_memory_exhausted_errors "$username" 7 | grep "TOTAL")" 1)
    max_children_errors=$(get_field "$(analyze_max_children_errors "$username" 7 | grep "TOTAL")" 1)
    timeout_errors=$(get_field "$(analyze_execution_timeout_errors "$username" 7 | grep "TOTAL")" 1)
    slow_requests=$(get_field "$(analyze_slow_requests "$username" 7 5 | grep "TOTAL")" 1)

    echo "  Memory Exhausted: $memory_errors"
    echo "  Max Children Reached: $max_children_errors"
    echo "  Execution Timeouts: $timeout_errors"
    echo "  Slow Requests (>5s): $slow_requests"
    echo ""

    # 9. Issues & Recommendations
    echo "ISSUES DETECTED:"
    local issues
    issues=$(detect_php_config_issues "$username" "$domain")

    while IFS='|' read -r issue_type severity message recommendation; do
        [ -z "$issue_type" ] && continue
        echo "  [$severity] $issue_type: $message"
        echo "      → $recommendation"
    done <<< "$issues"
    echo ""

    # 10. Optimization Recommendations
    echo "OPTIMIZATION RECOMMENDATIONS:"

    # Calculate optimal max_children
    local optimal_max_children
    optimal_max_children=$(calculate_optimal_max_children "$username" 1024)
    local recommended reason
    recommended=$(get_field "$optimal_max_children" 1)
    reason=$(get_field "$optimal_max_children" 2)

    local current_max_children
    if [ -n "$pool_config" ] && [ -f "$pool_config" ]; then
        current_max_children=$(grep "^pm.max_children" "$pool_config" | awk -F'=' '{print $2}' | tr -d ' ')

        if [ -n "$current_max_children" ] && [ "$recommended" -ne "$current_max_children" ]; then
            echo "  1. Adjust pm.max_children from $current_max_children to $recommended"
            echo "     Reason: $reason"
        fi
    fi

    echo ""
    echo "=== End of Report ==="
}

# ============================================================================
# HELPER FUNCTIONS
# ============================================================================

# Convert human-readable size to bytes
# Usage: convert_to_bytes "128M"
convert_to_bytes() {
    local size="$1"

    # Remove whitespace
    size=$(echo "$size" | tr -d ' ')

    # Extract number and unit
    local number="${size//[^0-9]/}"
    local unit="${size//[0-9]/}"

    # Default to bytes if no unit
    [ -z "$unit" ] && echo "$number" && return

    # Convert based on unit
    case "${unit^^}" in
        K|KB)
            echo $((number * 1024))
            ;;
        M|MB)
            echo $((number * 1024 * 1024))
            ;;
        G|GB)
            echo $((number * 1024 * 1024 * 1024))
            ;;
        *)
            echo "$number"
            ;;
    esac
}

# ============================================================================
# SERVER-WIDE MEMORY CAPACITY ANALYSIS
# ============================================================================

# Calculate total memory required if all PHP-FPM pools hit max capacity
# Usage: calculate_server_memory_capacity
# Returns: total_required_mb|total_ram_mb|percentage|status|details
calculate_server_memory_capacity() {
    # Get total system memory
    local total_ram_mb
    total_ram_mb=$(free -m | awk '/^Mem:/ {print $2}')

    # Get all users
    local users
    users=$(list_all_users)

    if [ -z "$users" ]; then
        echo "0|$total_ram_mb|0|ERROR|No users found"
        return 1
    fi

    # Track totals
    local total_required_mb=0
    local total_max_children=0
    local pool_count=0
    local details=""

    # Iterate through all users and their domains
    while IFS= read -r username; do
        [ -z "$username" ] && continue

        # Get all domains for this user
        local user_domains
        user_domains=$(get_user_domains "$username")

        while IFS= read -r domain; do
            [ -z "$domain" ] && continue

            # Find FPM pool config for this domain
            local pool_config
            pool_config=$(find_fpm_pool_config "$username" "$domain")

            if [ -z "$pool_config" ] || [ ! -f "$pool_config" ]; then
                continue
            fi

            pool_count=$((pool_count + 1))

            # Get max_children from pool config
            local max_children
            max_children=$(grep "^pm.max_children" "$pool_config" | awk -F'=' '{print $2}' | tr -d ' ')

            if [ -z "$max_children" ] || [ "$max_children" -eq 0 ]; then
                max_children=5  # Safe default if not set
            fi

            # Get average memory per process for this username
            local avg_kb=0
            local memory_stats
            memory_stats=$(calculate_memory_per_process "$username")
            avg_kb=$(get_field "$memory_stats" 1)

            if [ -z "$avg_kb" ] || [ "$avg_kb" -eq 0 ]; then
                # No active processes, estimate 50MB per process (conservative)
                avg_kb=$((50 * 1024))
            fi

            local avg_mb=$((avg_kb / 1024))

            # Calculate max memory for this pool
            local pool_max_mb=$((max_children * avg_mb))
            total_required_mb=$((total_required_mb + pool_max_mb))
            total_max_children=$((total_max_children + max_children))

            # Add to details
            details+="$domain|$username|$max_children|${avg_mb}MB|${pool_max_mb}MB"$'\n'

        done <<< "$user_domains"
    done <<< "$users"

    # Add MySQL memory usage to total (with timeout to prevent hanging)
    local mysql_memory_mb=0
    local mysql_status
    local mysql_info
    mysql_info=$(timeout 5 detect_mysql_memory_usage 2>/dev/null)
    if [ $? -eq 0 ] && [ -n "$mysql_info" ]; then
        mysql_memory_mb=$(echo "$mysql_info" | cut -d'|' -f3 || echo "0")
        mysql_status=$(echo "$mysql_info" | cut -d'|' -f4)
        # Ensure mysql_memory_mb is numeric
        mysql_memory_mb=${mysql_memory_mb:-0}
        [ -z "$mysql_memory_mb" ] && mysql_memory_mb=0
        total_required_mb=$((total_required_mb + mysql_memory_mb))
    fi

    # Calculate percentage
    local percentage=$((total_required_mb * 100 / total_ram_mb))

    # Determine status
    local status
    if [ "$percentage" -gt 90 ]; then
        status="CRITICAL"
    elif [ "$percentage" -gt 75 ]; then
        status="WARNING"
    elif [ "$percentage" -gt 60 ]; then
        status="CAUTION"
    else
        status="HEALTHY"
    fi

    # Return formatted result - first line is summary
    if [ "${mysql_memory_mb:-0}" -gt 0 ]; then
        echo "$total_required_mb|$total_ram_mb|$percentage|$status|$pool_count pools|$total_max_children max_children|MySQL: ${mysql_memory_mb}MB"
    else
        echo "$total_required_mb|$total_ram_mb|$percentage|$status|$pool_count pools|$total_max_children max_children"
    fi

    # Return details as additional lines (not to stderr)
    echo "$details"
}

# Calculate optimal memory allocation per user
# Usage: calculate_balanced_memory_allocation
# Returns: recommendations for each user to fit within system limits
calculate_balanced_memory_allocation() {
    # Get total system memory
    local total_ram_mb
    total_ram_mb=$(free -m | awk '/^Mem:/ {print $2}')

    # Reserve memory for system (minimum 2GB or 20% of RAM, whichever is higher)
    local reserved_mb
    reserved_mb=$((total_ram_mb * 20 / 100))
    [ "$reserved_mb" -lt 2048 ] && reserved_mb=2048

    # Account for MySQL memory usage
    local mysql_memory_mb=0
    local mysql_info
    mysql_info=$(detect_mysql_memory_usage 2>/dev/null)
    if [ $? -eq 0 ]; then
        mysql_memory_mb=$(echo "$mysql_info" | cut -d'|' -f3)
        reserved_mb=$((reserved_mb + mysql_memory_mb))
    fi

    local available_mb=$((total_ram_mb - reserved_mb))

    # Get all users with FPM pools
    local users
    users=$(list_all_users)

    if [ -z "$users" ]; then
        echo "ERROR|No users found"
        return 1
    fi

    # Collect pool data
    declare -A pool_traffic    # Avg requests per minute
    declare -A pool_memory     # Avg MB per process
    declare -A pool_max        # Current max_children
    declare -A pool_config_file

    local total_traffic=0
    local pool_count=0

    while IFS= read -r username; do
        [ -z "$username" ] && continue

        # Find pool config
        local pool_config
        pool_config=$(find_fpm_pool_config "$username")

        [ -z "$pool_config" ] || [ ! -f "$pool_config" ] && continue

        pool_count=$((pool_count + 1))
        pool_config_file[$username]="$pool_config"

        # Get current max_children (pure bash - no external commands)
        local max_children=""
        while IFS='=' read -r key value; do
            if [[ "$key" =~ ^[[:space:]]*pm\.max_children ]]; then
                max_children="${value// /}"  # Remove all spaces
                break
            fi
        done < "$pool_config"
        pool_max[$username]=$max_children

        # Get average memory per process
        local memory_stats
        memory_stats=$(calculate_memory_per_process "$username")
        # Pure bash field extraction (no cut command)
        local avg_kb="${memory_stats%%|*}"

        if [ "$avg_kb" -eq 0 ]; then
            avg_kb=$((50 * 1024))  # Default 50MB
        fi

        pool_memory[$username]=$((avg_kb / 1024))

        # Get traffic stats (pure bash field extraction)
        local traffic
        local traffic_result
        traffic_result=$(calculate_avg_requests_per_minute "$username" 24 2>/dev/null || echo "1")
        traffic="${traffic_result%%|*}"  # Extract first field without cut
        [ "$traffic" -eq 0 ] && traffic=1  # Minimum 1 req/min

        pool_traffic[$username]=$traffic
        total_traffic=$((total_traffic + traffic))

    done <<< "$users"

    if [ "$pool_count" -eq 0 ]; then
        echo "ERROR|No PHP-FPM pools found"
        return 1
    fi

    # Calculate proportional allocation based on traffic
    echo "USER|CURRENT_MAX|AVG_MB|TRAFFIC_RPM|RECOMMENDED_MAX|ALLOCATED_MB|REASON"

    for username in "${!pool_traffic[@]}"; do
        local traffic=${pool_traffic[$username]}
        local avg_mb=${pool_memory[$username]}
        local current_max=${pool_max[$username]}

        # Calculate proportional share of available memory based on traffic
        local traffic_percentage
        if [ "$total_traffic" -gt 0 ]; then
            traffic_percentage=$((traffic * 100 / total_traffic))
        else
            traffic_percentage=$((100 / pool_count))  # Equal distribution if no traffic
        fi
        local allocated_mb=$((available_mb * traffic_percentage / 100))

        # Calculate recommended max_children for this allocation
        # Protect against division by zero
        local recommended_max=5
        if [ "$avg_mb" -gt 0 ]; then
            recommended_max=$((allocated_mb / avg_mb))
        fi

        # Apply limits
        [ "$recommended_max" -lt 5 ] && recommended_max=5           # Minimum 5
        [ "$recommended_max" -gt 200 ] && recommended_max=200       # Maximum 200

        # Determine reason
        local reason
        if [ "$recommended_max" -lt "$current_max" ]; then
            reason="REDUCE (prevent OOM)"
        elif [ "$recommended_max" -gt "$current_max" ]; then
            reason="INCREASE (traffic demands)"
        else
            reason="OPTIMAL"
        fi

        echo "$username|$current_max|${avg_mb}MB|$traffic|$recommended_max|${allocated_mb}MB|$reason"
    done
}

# Calculate optimal memory allocation per DOMAIN (cPanel-specific)
# Usage: calculate_balanced_memory_allocation_per_domain
# Returns: recommendations for each domain to fit within system limits
calculate_balanced_memory_allocation_per_domain() {
    # Verify this is cPanel
    if [ "$SYS_CONTROL_PANEL" != "cpanel" ]; then
        echo "ERROR|This function only supports cPanel. Use calculate_balanced_memory_allocation for other panels."
        return 1
    fi

    # Get total system memory
    local total_ram_mb
    total_ram_mb=$(free -m | awk '/^Mem:/ {print $2}')

    # Reserve memory for system (minimum 2GB or 20% of RAM, whichever is higher)
    local reserved_mb
    reserved_mb=$((total_ram_mb * 20 / 100))
    [ "$reserved_mb" -lt 2048 ] && reserved_mb=2048

    # Account for MySQL memory usage
    local mysql_memory_mb=0
    local mysql_info
    mysql_info=$(detect_mysql_memory_usage 2>/dev/null)
    if [ $? -eq 0 ]; then
        mysql_memory_mb=$(echo "$mysql_info" | cut -d'|' -f3)
        reserved_mb=$((reserved_mb + mysql_memory_mb))
    fi

    local available_mb=$((total_ram_mb - reserved_mb))

    # Get all users
    local users
    users=$(list_all_users)

    if [ -z "$users" ]; then
        echo "ERROR|No users found"
        return 1
    fi

    # Collect per-domain pool data
    declare -A domain_traffic      # Avg requests per minute
    declare -A domain_memory       # Avg MB per process
    declare -A domain_max          # Current max_children
    declare -A domain_pool_config  # Pool config path
    declare -A domain_username     # Username for domain
    declare -A domain_php_version  # PHP version

    local total_traffic=0
    local pool_count=0

    # Iterate through all users and their domains
    while IFS= read -r username; do
        [ -z "$username" ] && continue

        # Get all domains for this user
        local user_domains
        user_domains=$(get_user_domains "$username")

        while IFS= read -r domain; do
            [ -z "$domain" ] && continue

            # Detect PHP version for this domain
            local php_version
            php_version=$(detect_php_version_for_domain "$domain" "$username")

            # Find pool config (domain-specific for cPanel)
            local pool_config
            pool_config=$(find_fpm_pool_config "$username" "$domain" "$php_version")

            # Skip if no pool config found (domain may not use FPM)
            [ -z "$pool_config" ] || [ ! -f "$pool_config" ] && continue

            pool_count=$((pool_count + 1))
            domain_pool_config[$domain]="$pool_config"
            domain_username[$domain]="$username"
            domain_php_version[$domain]="$php_version"

            # Get current max_children
            local max_children
            # Read max_children (pure bash - no external commands)
            max_children=""
            while IFS='=' read -r key value; do
                if [[ "$key" =~ ^[[:space:]]*pm\.max_children ]]; then
                    max_children="${value// /}"
                    break
                fi
            done < "$pool_config"
            [ -z "$max_children" ] && max_children=5
            domain_max[$domain]=$max_children

            # Get average memory per process
            local memory_stats
            memory_stats=$(calculate_memory_per_process "$username")
            local avg_kb
            avg_kb=$(get_field "$memory_stats" 1)

            if [ -z "$avg_kb" ] || [ "$avg_kb" -eq 0 ]; then
                avg_kb=$((50 * 1024))  # Default 50MB
            fi

            domain_memory[$domain]=$((avg_kb / 1024))

            # Get advanced traffic stats for this domain (7-day, bot-filtered, 95th percentile)
            local traffic
            # Try fast method first (current process count)
            local current_processes
            current_processes=$(pgrep -u "$username" php-fpm 2>/dev/null | wc -l)

            if [ "$current_processes" -gt 0 ]; then
                # Use current process count as baseline (fast, no log parsing)
                traffic=$((current_processes * 2))  # Assume processes can handle ~2 req/min each
            else
                # Fallback to traffic analysis only if no processes found
                local traffic_stats
                traffic_stats=$(analyze_domain_traffic_advanced "$domain" "$username" 7 2>/dev/null)

                if [ -n "$traffic_stats" ] && [[ ! "$traffic_stats" =~ ERROR ]]; then
                    # Extract 95th percentile RPM (field 3) - pure bash
                    local rpm_95th
                    local temp="${traffic_stats#*|}"  # Remove field 1
                    temp="${temp#*|}"                  # Remove field 2
                    rpm_95th="${temp%%|*}"            # Extract field 3

                    # Add 20% headroom to 95th percentile for burst capacity
                    traffic=$(( rpm_95th * 120 / 100 ))

                    # Minimum 1 req/min
                    [ "$traffic" -eq 0 ] && traffic=1
                else
                    # Ultimate fallback
                    traffic=5  # Conservative default
                fi
            fi

            domain_traffic[$domain]=$traffic
            total_traffic=$((total_traffic + traffic))

        done <<< "$user_domains"
    done <<< "$users"

    if [ "$pool_count" -eq 0 ]; then
        echo "ERROR|No PHP-FPM pools found"
        return 1
    fi

    # Calculate proportional allocation based on traffic
    echo "DOMAIN|USERNAME|PHP_VER|CURRENT_MAX|AVG_MB|TRAFFIC_RPM|RECOMMENDED_MAX|ALLOCATED_MB|REASON"

    for domain in "${!domain_traffic[@]}"; do
        local traffic=${domain_traffic[$domain]}
        local avg_mb=${domain_memory[$domain]}
        local current_max=${domain_max[$domain]}
        local username=${domain_username[$domain]}
        local php_version=${domain_php_version[$domain]}

        # Calculate proportional share of available memory based on traffic
        local traffic_percentage
        if [ "$total_traffic" -gt 0 ]; then
            traffic_percentage=$((traffic * 100 / total_traffic))
        else
            traffic_percentage=$((100 / pool_count))  # Equal distribution if no traffic data
        fi

        local allocated_mb=$((available_mb * traffic_percentage / 100))

        # Ensure minimum allocation
        [ "$allocated_mb" -lt "$((avg_mb * 5))" ] && allocated_mb=$((avg_mb * 5))

        # Calculate recommended max_children for this allocation
        # Protect against division by zero
        local recommended_max=5
        if [ "$avg_mb" -gt 0 ]; then
            recommended_max=$((allocated_mb / avg_mb))
        fi

        # Apply limits
        [ "$recommended_max" -lt 5 ] && recommended_max=5           # Minimum 5
        [ "$recommended_max" -gt 200 ] && recommended_max=200       # Maximum 200

        # Determine reason
        local reason
        if [ "$recommended_max" -lt "$current_max" ]; then
            reason="REDUCE (prevent OOM)"
        elif [ "$recommended_max" -gt "$current_max" ]; then
            reason="INCREASE (traffic demands)"
        else
            reason="OPTIMAL"
        fi

        echo "$domain|$username|$php_version|$current_max|${avg_mb}MB|$traffic|$recommended_max|${allocated_mb}MB|$reason"
    done
}

# Detect MySQL/MariaDB memory usage
# Usage: detect_mysql_memory_usage
# Returns: buffer_pool_mb|total_connections|estimated_total_mb|status
detect_mysql_memory_usage() {
    # Check if MySQL/MariaDB is running
    if ! pgrep -x "mysqld\|mariadbd" >/dev/null 2>&1; then
        echo "0|0|0|NOT_RUNNING"
        return 1
    fi

    # Try to get actual memory usage from ps
    local mysql_rss_kb
    mysql_rss_kb=$(ps aux | grep -E "[m]ysqld|[m]ariadbd" | awk 'BEGIN {sum=0} {sum+=$6} END {print sum}')

    if [ -n "$mysql_rss_kb" ] && [ "$mysql_rss_kb" -gt 0 ]; then
        local mysql_rss_mb=$((mysql_rss_kb / 1024))

        # Try to get buffer pool size from MySQL
        local buffer_pool_mb=0
        local max_connections=150  # Default

        if command -v mysql >/dev/null 2>&1; then
            # Try to query MySQL directly (with 2 second timeout to prevent hanging)
            buffer_pool_mb=$(timeout 2 mysql -Nse "SELECT ROUND(@@innodb_buffer_pool_size/1024/1024)" 2>/dev/null || echo "0")
            max_connections=$(timeout 2 mysql -Nse "SELECT @@max_connections" 2>/dev/null || echo "150")
        fi

        # If we couldn't get it from MySQL, try my.cnf
        if [ "$buffer_pool_mb" -eq 0 ]; then
            local my_cnf="/etc/my.cnf"
            [ ! -f "$my_cnf" ] && my_cnf="/etc/mysql/my.cnf"

            if [ -f "$my_cnf" ]; then
                # Parse innodb_buffer_pool_size
                local buffer_pool_setting
                buffer_pool_setting=$(grep -E "^innodb_buffer_pool_size" "$my_cnf" | awk -F'=' '{print $2}' | tr -d ' ')

                if [ -n "$buffer_pool_setting" ]; then
                    # Convert to MB (handle G, M, K suffixes)
                    if [[ "$buffer_pool_setting" =~ ([0-9]+)G ]]; then
                        buffer_pool_mb=$((${BASH_REMATCH[1]} * 1024))
                    elif [[ "$buffer_pool_setting" =~ ([0-9]+)M ]]; then
                        buffer_pool_mb=${BASH_REMATCH[1]}
                    elif [[ "$buffer_pool_setting" =~ ([0-9]+)K ]]; then
                        buffer_pool_mb=$((${BASH_REMATCH[1]} / 1024))
                    elif [[ "$buffer_pool_setting" =~ ^[0-9]+$ ]]; then
                        # Raw bytes
                        buffer_pool_mb=$((buffer_pool_setting / 1024 / 1024))
                    fi
                fi
            fi
        fi

        # Use actual RSS as the estimated total
        local estimated_total_mb=$mysql_rss_mb

        # Determine status
        local status="RUNNING"
        if [ "$estimated_total_mb" -gt 4096 ]; then
            status="HIGH_MEMORY"
        elif [ "$estimated_total_mb" -gt 2048 ]; then
            status="MODERATE_MEMORY"
        else
            status="LOW_MEMORY"
        fi

        echo "$buffer_pool_mb|$max_connections|$estimated_total_mb|$status"
        return 0
    fi

    echo "0|0|0|UNKNOWN"
    return 1
}

# Export all functions
export -f analyze_memory_exhausted_errors
export -f analyze_max_children_errors
export -f analyze_slow_requests
export -f analyze_execution_timeout_errors
export -f calculate_memory_per_process
export -f calculate_optimal_max_children
export -f calculate_peak_concurrent_requests
export -f calculate_avg_requests_per_minute
export -f analyze_domain_traffic_advanced
export -f analyze_opcache_effectiveness
export -f detect_php_config_issues
export -f analyze_domain_php
export -f convert_to_bytes
export -f calculate_server_memory_capacity
export -f calculate_balanced_memory_allocation
export -f calculate_balanced_memory_allocation_per_domain
export -f detect_mysql_memory_usage
