#!/bin/bash

#############################################################################
# System Reference Database
# Quick-lookup database optimized for fast grep/awk queries
# Format: Pipe-delimited structured data
#############################################################################

# Source dependencies
if [ -z "$TOOLKIT_BASE_DIR" ]; then
    SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"

    [ -f "$SCRIPT_DIR/common-functions.sh" ] && source "$SCRIPT_DIR/common-functions.sh" || { echo "ERROR: common-functions.sh not found" >&2; return 1; }
    [ -f "$SCRIPT_DIR/system-detect.sh" ] && source "$SCRIPT_DIR/system-detect.sh" || { echo "ERROR: system-detect.sh not found" >&2; return 1; }
    [ -f "$SCRIPT_DIR/user-manager.sh" ] && source "$SCRIPT_DIR/user-manager.sh" || { echo "ERROR: user-manager.sh not found" >&2; return 1; }
fi

# Reference database location
export SYSREF_DB="${TOOLKIT_BASE_DIR}/.sysref"
export SYSREF_TIMESTAMP="${TOOLKIT_BASE_DIR}/.sysref.timestamp"

#############################################################################
# DATABASE STRUCTURE
#############################################################################
#
# [SYSTEM] - System information
#   SYS|key|value|extra
#
# [USERS] - User accounts
#   USER|username|primary_domain|db_count|domain_count|disk_mb|home_dir
#
# [DATABASES] - Database information
#   DB|db_name|owner|primary_domain|size_mb|table_count
#
# [DOMAINS] - Domain to user mapping
#   DOMAIN|domain_name|owner|log_path|is_primary
#
# [DB_TABLES] - Database tables with plugin identification
#   TABLE|db_name|table_name|plugin|size_mb
#
# [WORDPRESS] - WordPress installations
#   WP|domain|path|db_name|version|plugin_count
#
# [LOGS] - Log file locations
#   LOG|type|domain|owner|path|size_mb
#
# [PROCESSES] - Top processes by user (at build time)
#   PROC|username|pid|cpu|mem|command
#
#############################################################################

#############################################################################
# BUILD DATABASE
#############################################################################

build_reference_database() {
    local start_time=$(date +%s)

    print_info "Building system reference database..."
    echo "# System Reference Database" > "$SYSREF_DB"
    echo "# Generated: $(date)" >> "$SYSREF_DB"
    echo "# Format: Type|Field1|Field2|..." >> "$SYSREF_DB"
    echo "" >> "$SYSREF_DB"

    # System information
    build_system_section

    # User information
    build_users_section

    # Database information
    build_databases_section

    # Domain mapping
    build_domains_section

    # WordPress detection
    build_wordpress_section

    # Log file mapping
    build_logs_section

    # Save timestamp
    date +%s > "$SYSREF_TIMESTAMP"

    local end_time=$(date +%s)
    local duration=$((end_time - start_time))

    print_success "Reference database built in ${duration}s"
    print_info "Database location: $SYSREF_DB"

    # Show stats
    local total_lines=$(wc -l < "$SYSREF_DB")
    local user_count=$(grep -c "^USER|" "$SYSREF_DB" 2>/dev/null || echo 0)
    local db_count=$(grep -c "^DB|" "$SYSREF_DB" 2>/dev/null || echo 0)
    local domain_count=$(grep -c "^DOMAIN|" "$SYSREF_DB" 2>/dev/null || echo 0)
    local wp_count=$(grep -c "^WP|" "$SYSREF_DB" 2>/dev/null || echo 0)

    echo "  - $user_count users"
    echo "  - $db_count databases"
    echo "  - $domain_count domains"
    echo "  - $wp_count WordPress sites"
    echo "  - $total_lines total entries"
}

build_system_section() {
    echo "[SYSTEM]" >> "$SYSREF_DB"
    echo "SYS|CONTROL_PANEL|$SYS_CONTROL_PANEL|$SYS_CONTROL_PANEL_VERSION" >> "$SYSREF_DB"
    echo "SYS|OS|$SYS_OS_TYPE|$SYS_OS_VERSION" >> "$SYSREF_DB"
    echo "SYS|WEB_SERVER|$SYS_WEB_SERVER|$SYS_WEB_SERVER_VERSION" >> "$SYSREF_DB"
    echo "SYS|DATABASE|$SYS_DB_TYPE|$SYS_DB_VERSION" >> "$SYSREF_DB"
    echo "SYS|LOG_DIR|$SYS_LOG_DIR|" >> "$SYSREF_DB"
    echo "SYS|USER_HOME|$SYS_USER_HOME_BASE|" >> "$SYSREF_DB"
    echo "SYS|CPU_CORES|$CPU_CORES|" >> "$SYSREF_DB"
    echo "SYS|HOSTNAME|$(hostname)|" >> "$SYSREF_DB"

    # PHP versions
    for php_ver in "${SYS_PHP_VERSIONS[@]}"; do
        echo "SYS|PHP_VERSION|$php_ver|" >> "$SYSREF_DB"
    done

    echo "" >> "$SYSREF_DB"
}

build_users_section() {
    echo "[USERS]" >> "$SYSREF_DB"

    local users=($(list_all_users))
    local total_users=${#users[@]}
    local current=0

    for user in "${users[@]}"; do
        current=$((current + 1))
        show_progress $current $total_users "Indexing users..."

        local primary_domain=$(get_user_domains "$user" | head -1)
        local domain_count=$(get_user_domains "$user" | grep -v "^$" | wc -l)
        local db_count=$(get_user_databases "$user" | grep -v "^$" | wc -l)

        # Get disk usage (quick du)
        local home_dir=$(get_user_info "$user" | grep "^HOME_DIR=" | cut -d= -f2)
        local disk_mb=0
        if [ -n "$home_dir" ] && [ -d "$home_dir" ]; then
            disk_mb=$(du -sm "$home_dir" 2>/dev/null | awk '{print $1}')
        fi

        echo "USER|$user|$primary_domain|$db_count|$domain_count|$disk_mb|$home_dir" >> "$SYSREF_DB"
    done

    finish_progress
    echo "" >> "$SYSREF_DB"
}

build_databases_section() {
    echo "[DATABASES]" >> "$SYSREF_DB"

    if [ "$SYS_DB_TYPE" = "none" ]; then
        echo "" >> "$SYSREF_DB"
        return
    fi

    # Build MySQL command with credentials if needed
    local mysql_cmd="mysql"
    if [ "$SYS_CONTROL_PANEL" = "plesk" ] && [ -f /etc/psa/.psa.shadow ]; then
        export MYSQL_PWD=$(cat /etc/psa/.psa.shadow)
        mysql_cmd="mysql -uadmin"
    fi

    local total_dbs=$($mysql_cmd -Ns -e "SHOW DATABASES" 2>/dev/null | grep -v "^information_schema$\|^mysql$\|^performance_schema$\|^sys$" | wc -l)
    local current=0

    # Use process substitution instead of pipe to avoid subshell shadowing (fixes current variable loss)
    while IFS= read -r db; do
        [ -z "$db" ] && continue
        current=$((current + 1))
        show_progress $current $total_dbs "Indexing databases..."

        local owner=$(get_database_owner "$db")
        local domain=$(get_database_domain "$db")

        local size_mb=$($mysql_cmd -Ns -e "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2)
                                       FROM information_schema.TABLES
                                       WHERE table_schema=\`$db\`" 2>/dev/null)
        [ -z "$size_mb" ] && size_mb=0

        local table_count=$($mysql_cmd -Ns "$db" -e "SHOW TABLES" 2>/dev/null | wc -l)

        echo "DB|$db|$owner|$domain|$size_mb|$table_count" >> "$SYSREF_DB"
    done < <($mysql_cmd -Ns -e "SHOW DATABASES" 2>/dev/null | grep -v "^information_schema$\|^mysql$\|^performance_schema$\|^sys$")

    finish_progress
    echo "" >> "$SYSREF_DB"

    # Clean up password environment variable
    unset MYSQL_PWD
}

# Check domain HTTP/HTTPS status codes
# Returns: http_code|https_code|status_summary
check_domain_status() {
    local domain="$1"
    local http_code="000"
    local https_code="000"
    local status_summary="unchecked"

    # Skip if curl not available
    if ! command -v curl &>/dev/null; then
        echo "000|000|no_curl"
        return 0
    fi

    # Skip obviously invalid domains
    if [ -z "$domain" ] || [[ ! "$domain" =~ \. ]]; then
        echo "000|000|invalid_domain"
        return 0
    fi

    # Try HTTP (timeout 3 seconds, max 2 redirects, check for valid response)
    http_code=$(timeout 3 curl -s -o /dev/null -w "%{http_code}" --max-redirs 2 -m 3 "http://$domain" 2>/dev/null)
    if [ $? -ne 0 ] || [ -z "$http_code" ]; then
        http_code="timeout"
    fi

    # Try HTTPS (timeout 3 seconds, max 2 redirects, ignore cert errors)
    https_code=$(timeout 3 curl -s -o /dev/null -w "%{http_code}" --max-redirs 2 -m 3 -k "https://$domain" 2>/dev/null)
    if [ $? -ne 0 ] || [ -z "$https_code" ]; then
        https_code="timeout"
    fi

    # Determine overall status
    if [ "$http_code" = "200" ] || [ "$https_code" = "200" ]; then
        status_summary="200_OK"
    elif [ "$http_code" = "403" ] || [ "$https_code" = "403" ]; then
        status_summary="403_FORBIDDEN"
    elif [ "$http_code" = "404" ] || [ "$https_code" = "404" ]; then
        status_summary="404_NOT_FOUND"
    elif [ "$http_code" = "500" ] || [ "$https_code" = "500" ]; then
        status_summary="500_ERROR"
    elif [ "$http_code" = "502" ] || [ "$https_code" = "502" ]; then
        status_summary="502_BAD_GATEWAY"
    elif [ "$http_code" = "503" ] || [ "$https_code" = "503" ]; then
        status_summary="503_UNAVAILABLE"
    elif [[ "$http_code" =~ ^30[0-9]$ ]] || [[ "$https_code" =~ ^30[0-9]$ ]]; then
        status_summary="REDIRECT"
    elif [ "$http_code" = "timeout" ] && [ "$https_code" = "timeout" ]; then
        status_summary="TIMEOUT"
    elif [ "$http_code" = "000" ] && [ "$https_code" = "000" ]; then
        status_summary="UNREACHABLE"
    else
        status_summary="OTHER"
    fi

    echo "${http_code}|${https_code}|${status_summary}"
}

build_domains_section() {
    echo "[DOMAINS]" >> "$SYSREF_DB"

    # Track domains we've already added
    declare -A seen_domains

    local users=($(list_all_users))

    # Count total domains for progress
    local total_domains=0
    for user in "${users[@]}"; do
        local userdata_dir="${SYS_CPANEL_USERDATA_DIR:-/var/cpanel/userdata}/${user}"
        if [ -d "$userdata_dir" ]; then
            total_domains=$((total_domains + $(find "$userdata_dir" -type f ! -name "*.cache" ! -name "*.yaml" ! -name "*.json" ! -name "main*" ! -name "cache" ! -name "*_SSL" 2>/dev/null | wc -l)))
        fi
    done

    local current_domain=0

    # Get detailed domain information from cPanel userdata (if available)
    for user in "${users[@]}"; do
        local userdata_dir="${SYS_CPANEL_USERDATA_DIR:-/var/cpanel/userdata}/${user}"

        if [ -d "$userdata_dir" ]; then
            # Parse each domain configuration file in userdata
            for config_file in "$userdata_dir"/*; do
                [ ! -f "$config_file" ] && continue
                local basename=$(basename "$config_file")

                # Skip cache files and special files
                [[ "$basename" =~ \.cache$ ]] && continue
                [[ "$basename" =~ \.yaml$ ]] && continue
                [[ "$basename" =~ \.json$ ]] && continue
                [[ "$basename" =~ ^main ]] && continue
                [[ "$basename" =~ ^cache$ ]] && continue
                [[ "$basename" =~ _SSL$ ]] && continue

                # Extract domain info from config
                local domain="$basename"
                local doc_root=$(grep "^documentroot:" -- "$config_file" | awk '{print $2}' || true)
                local log_path=$(grep "target:.*domlogs" -- "$config_file" | head -1 | awk '{print $2}' || true)
                local server_alias=$(grep "^serveralias:" -- "$config_file" | awk '{print $2}' || true)
                local php_version=$(grep "^phpversion:" -- "$config_file" | awk '{print $2}' || true)

                # Determine if primary domain
                local is_primary="no"
                local primary_domain=$(get_user_domains "$user" | head -1)
                [ "$domain" = "$primary_domain" ] && is_primary="yes"

                # Determine domain type (addon, parked, subdomain, primary)
                local domain_type="addon"
                if [ "$is_primary" = "yes" ]; then
                    domain_type="primary"
                elif [[ "$domain" =~ \. ]] && [[ "$domain" =~ ^[^.]+\. ]]; then
                    # Check if it's a subdomain of the primary
                    local base_domain=$(echo "$domain" | rev | cut -d. -f1-2 | rev)
                    if [ "$base_domain" = "$primary_domain" ]; then
                        domain_type="subdomain"
                    fi
                fi

                # Check HTTP/HTTPS status codes (only for primary and addon domains, skip aliases/subdomains)
                current_domain=$((current_domain + 1))
                local http_code="000"
                local https_code="000"
                local status_summary="skipped"

                if [ "$domain_type" = "primary" ] || [ "$domain_type" = "addon" ]; then
                    show_progress $current_domain $total_domains "Checking domain status codes..."
                    local status_result=$(check_domain_status "$domain")
                    IFS='|' read -r http_code https_code status_summary <<< "$status_result"
                fi

                # Format: DOMAIN|domain|owner|doc_root|log_path|php_version|is_primary|type|aliases|http_code|https_code|status_summary
                echo "DOMAIN|$domain|$user|$doc_root|$log_path|$php_version|$is_primary|$domain_type|$server_alias|$http_code|$https_code|$status_summary" >> "$SYSREF_DB"
                seen_domains["$domain"]=1

                # Also add aliases as separate entries
                if [ -n "$server_alias" ]; then
                    # Convert space-separated aliases to newline-separated for safe iteration
                    echo "$server_alias" | tr ' ' '\n' | while IFS= read -r alias; do
                        [ -z "$alias" ] && continue
                        [ -n "${seen_domains[$alias]:-}" ] && continue

                        # Alias points to same document root and logs (inherit status from parent)
                        echo "DOMAIN|$alias|$user|$doc_root|$log_path|$php_version|no|alias|$domain|$http_code|$https_code|alias_of_$status_summary" >> "$SYSREF_DB"
                        seen_domains["$alias"]=1
                    done
                fi
            done
        else
            # Fallback for non-cPanel or if userdata not available
            local primary_domain=$(get_user_domains "$user" | head -1)

            # Use while read to safely iterate over domains (handles spaces)
            get_user_domains "$user" | while IFS= read -r domain; do
                [ -z "$domain" ] && continue
                [ -n "${seen_domains[$domain]:-}" ] && continue

                local is_primary="no"
                [ "$domain" = "$primary_domain" ] && is_primary="yes"

                # Find log path
                local log_path="${SYS_LOG_DIR}/${domain}"
                [ ! -f "$log_path" ] && log_path="${SYS_LOG_DIR}/${domain}.log"

                # Check status for non-cPanel domains
                current_domain=$((current_domain + 1))
                show_progress $current_domain $total_domains "Checking domain status codes..."
                local status_result=$(check_domain_status "$domain")
                IFS='|' read -r http_code https_code status_summary <<< "$status_result"

                # Simple format for non-cPanel (with status codes)
                echo "DOMAIN|$domain|$user||$log_path||$is_primary|local||$http_code|$https_code|$status_summary" >> "$SYSREF_DB"
                seen_domains["$domain"]=1
            done
        fi
    done

    finish_progress

    # Check /etc/localdomains (cPanel local domains not yet added)
    if [ -f "/etc/localdomains" ]; then
        while read -r domain; do
            [ -z "$domain" ] && continue
            [ -n "${seen_domains[$domain]:-}" ] && continue

            local owner=$(grep "^${domain}:" /etc/trueuserdomains 2>/dev/null | cut -d: -f2 | xargs || true)
            [ -z "$owner" ] && owner="unknown"

            local log_path="${SYS_LOG_DIR}/${domain}"

            # Check status
            local status_result=$(check_domain_status "$domain")
            IFS='|' read -r http_code https_code status_summary <<< "$status_result"

            echo "DOMAIN|$domain|$owner||$log_path||unknown|local||$http_code|$https_code|$status_summary" >> "$SYSREF_DB"
            seen_domains["$domain"]=1
        done < /etc/localdomains
    fi

    # Check /etc/remotedomains (cPanel remote MX domains - no status check for remote MX)
    if [ -f "/etc/remotedomains" ]; then
        while read -r domain; do
            [ -z "$domain" ] && continue
            [ -n "${seen_domains[$domain]:-}" ] && continue

            local owner=$(grep "^${domain}:" /etc/trueuserdomains 2>/dev/null | cut -d: -f2 | xargs || true)
            [ -z "$owner" ] && owner="unknown"

            echo "DOMAIN|$domain|$owner||||unknown|remote||000|000|remote_mx" >> "$SYSREF_DB"
            seen_domains["$domain"]=1
        done < /etc/remotedomains
    fi

    echo "" >> "$SYSREF_DB"
}

build_wordpress_section() {
    echo "[WORDPRESS]" >> "$SYSREF_DB"

    # Find all wp-config.php files using process substitution (fixes subshell shadowing)
    while IFS= read -r wp_config; do
        [ -z "$wp_config" ] && continue
        local wp_dir=$(dirname "$wp_config")

        # Extract username from path (/home/username/...)
        local username=$(echo "$wp_dir" | cut -d'/' -f3)

        # Try to get domain from path - check if it's in a subdomain or addon domain folder
        local path_after_home=$(echo "$wp_dir" | sed "s|^/home/$username/||")
        local domain=""

        # Check for common domain folder patterns
        if [[ "$path_after_home" == public_html ]]; then
            # This is the primary domain - get it from user info
            domain=$(grep "USER|${username}|" "$SYSREF_DB" 2>/dev/null | cut -d'|' -f3 || true)
        elif [[ "$path_after_home" =~ ^public_html/(.+) ]]; then
            # Could be subdomain or subdirectory - extract folder name
            local folder=$(echo "$path_after_home" | cut -d'/' -f2)
            domain="${folder}"
        else
            # Might be addon/parked domain with own directory
            domain=$(echo "$path_after_home" | cut -d'/' -f1)
        fi

        # Try to get actual domain from WP database options (more reliable)
        local db_name=$(grep "DB_NAME" "$wp_config" | grep -oP "'[^']+'" 2>/dev/null | tail -1 | tr -d "'" || true)
        local db_user=$(grep "DB_USER" "$wp_config" | grep -oP "'[^']+'" 2>/dev/null | tail -1 | tr -d "'" || true)
        local db_host=$(grep "DB_HOST" "$wp_config" | grep -oP "'[^']+'" 2>/dev/null | tail -1 | tr -d "'" || true)

        # Try to get site URL from wp-config defines
        local site_url=$(grep -E "WP_SITEURL|WP_HOME" "$wp_config" | head -1 | grep -oP "https?://\K[^/'\"]+" 2>/dev/null || true)
        if [ -n "$site_url" ]; then
            domain="$site_url"
        fi

        # Get WP version
        local version=""
        if [ -f "${wp_dir}/wp-includes/version.php" ]; then
            version=$(grep "\$wp_version" "${wp_dir}/wp-includes/version.php" | grep -oP "'\K[^']+" 2>/dev/null | head -1 || true)
        fi

        # Count plugins
        local plugin_count=0
        if [ -d "${wp_dir}/wp-content/plugins" ]; then
            plugin_count=$(find "${wp_dir}/wp-content/plugins" -maxdepth 1 -type d 2>/dev/null | wc -l)
            plugin_count=$((plugin_count - 1)) # Exclude parent dir
        fi

        # Count themes
        local theme_count=0
        if [ -d "${wp_dir}/wp-content/themes" ]; then
            theme_count=$(find "${wp_dir}/wp-content/themes" -maxdepth 1 -type d 2>/dev/null | wc -l)
            theme_count=$((theme_count - 1)) # Exclude parent dir
        fi

        # Format: WP|domain|owner|path|db_name|db_user|version|plugin_count|theme_count
        echo "WP|$domain|$username|$wp_dir|$db_name|$db_user|$version|$plugin_count|$theme_count" >> "$SYSREF_DB"
    done < <(find "$SYS_USER_HOME_BASE" -name "wp-config.php" -type f 2>/dev/null)

    echo "" >> "$SYSREF_DB"
}

build_logs_section() {
    echo "[LOGS]" >> "$SYSREF_DB"

    # Apache/Web server logs
    # Temporarily disabled - causes hangs with large log directories
    # TODO: Implement log scanning with progress indicator and limits

    echo "" >> "$SYSREF_DB"
}

#############################################################################
# QUERY DATABASE
#############################################################################

# Quick lookup functions - optimized for speed

# Get user info
db_get_user() {
    local username="$1"
    grep "^USER|${username}|" "$SYSREF_DB" 2>/dev/null
}

# Get all users
db_get_all_users() {
    grep "^USER|" "$SYSREF_DB" 2>/dev/null
}

# Get user's databases
db_get_user_databases() {
    local username="$1"
    grep "^DB|.*|${username}|" "$SYSREF_DB" 2>/dev/null
}

# Get user's domains
db_get_user_domains() {
    local username="$1"
    grep "^DOMAIN|.*|${username}|" "$SYSREF_DB" 2>/dev/null
}

# Get database owner
db_get_database_owner() {
    local db_name="$1"
    grep "^DB|${db_name}|" "$SYSREF_DB" 2>/dev/null | cut -d'|' -f3
}

# Get all WordPress sites
db_get_all_wordpress() {
    grep "^WP|" "$SYSREF_DB" 2>/dev/null
}

# Get WordPress site by domain
db_get_wordpress_by_domain() {
    local domain="$1"
    grep "^WP|${domain}|" "$SYSREF_DB" 2>/dev/null
}

# Get log files for domain
db_get_domain_logs() {
    local domain="$1"
    grep "^LOG|.*|${domain}|" "$SYSREF_DB" 2>/dev/null
}

# Get system info
db_get_system_info() {
    local key="$1"
    if [ -n "$key" ]; then
        grep "^SYS|${key}|" "$SYSREF_DB" 2>/dev/null | cut -d'|' -f3-
    else
        grep "^SYS|" "$SYSREF_DB" 2>/dev/null
    fi
}

# Get health/session metric
db_get_health_metric() {
    local metric="$1"
    grep "^HEALTH|${metric}|" "$SYSREF_DB" 2>/dev/null | cut -d'|' -f3 | head -1
}

# Check if system is under load (for cross-module intelligence)
db_is_system_under_load() {
    local cpu_load=$(db_get_health_metric "CPU_LOAD_1MIN")
    local cpu_cores=$(db_get_health_metric "CPU_CORES")
    local mem_percent=$(db_get_health_metric "MEMORY_USED_PERCENT")

    # Consider system under load if CPU > 80% or memory > 90%
    if [ -n "$cpu_load" ] && [ -n "$cpu_cores" ]; then
        local load_percent=$(awk "BEGIN {printf \"%.0f\", ($cpu_load / $cpu_cores) * 100}" 2>/dev/null || echo "0")
        if [ "$load_percent" -gt 80 ] || [ "${mem_percent:-0}" -gt 90 ]; then
            return 0  # True - system is under load
        fi
    fi
    return 1  # False - system not under load
}

# Check if network has issues (for cross-module intelligence)
db_has_network_issues() {
    local tcp_retrans=$(db_get_health_metric "TCP_RETRANS_PERCENT")
    local rx_errors=$(db_get_health_metric "NETWORK_RX_ERRORS")
    local tx_errors=$(db_get_health_metric "NETWORK_TX_ERRORS")

    # Consider network problematic if retrans > 5% or errors > 100
    if [ -n "$tcp_retrans" ]; then
        local retrans_high=$(awk "BEGIN {print ($tcp_retrans > 5 ? 1 : 0)}" 2>/dev/null || echo 0)
        if [ "$retrans_high" -eq 1 ] || \
           [ "${rx_errors:-0}" -gt 100 ] || [ "${tx_errors:-0}" -gt 100 ]; then
            return 0  # True - network has issues
        fi
    fi
    return 1  # False - network OK
}

# Check if under attack (for cross-module intelligence)
db_is_under_attack() {
    local ssh_today=$(db_get_health_metric "SSH_ATTACKS_TODAY")
    # Consider under attack if > 100 failed SSH attempts today
    if [ "${ssh_today:-0}" -gt 100 ]; then
        return 0  # True - under attack
    fi
    return 1  # False - not under attack
}

# Get all health metrics (for display/debugging)
db_get_all_health() {
    grep "^HEALTH|" "$SYSREF_DB" 2>/dev/null
}

#############################################################################
# DATABASE MANAGEMENT
#############################################################################

# Check if database exists and is fresh (< 1 hour old)
db_is_fresh() {
    if [ ! -f "$SYSREF_DB" ] || [ ! -f "$SYSREF_TIMESTAMP" ]; then
        return 1
    fi

    local db_age=$(( $(date +%s) - $(cat "$SYSREF_TIMESTAMP") ))
    local max_age=3600 # 1 hour

    [ $db_age -lt $max_age ]
}

# Rebuild database if needed
db_ensure_fresh() {
    if ! db_is_fresh; then
        print_info "Reference database is stale or missing, rebuilding..."
        build_reference_database
    fi
}

# Force rebuild
db_rebuild() {
    build_reference_database
}

# Show database stats
db_show_stats() {
    if [ ! -f "$SYSREF_DB" ]; then
        print_error "Reference database does not exist. Run 'build' first."
        return 1
    fi

    print_banner "System Reference Database Stats"

    local db_age=$(( $(date +%s) - $(cat "$SYSREF_TIMESTAMP") ))
    local age_str=$(format_duration $db_age)

    echo "Database Age: $age_str"
    echo "Database Size: $(du -sh "$SYSREF_DB" | awk '{print $1}')"
    echo ""

    echo -e "${BOLD}Content Summary:${NC}"
    printf "  %-20s %s\n" "Users:" "$(grep -c '^USER|' "$SYSREF_DB" 2>/dev/null || echo 0)"
    printf "  %-20s %s\n" "Databases:" "$(grep -c '^DB|' "$SYSREF_DB" 2>/dev/null || echo 0)"
    printf "  %-20s %s\n" "Domains:" "$(grep -c '^DOMAIN|' "$SYSREF_DB" 2>/dev/null || echo 0)"
    printf "  %-20s %s\n" "WordPress Sites:" "$(grep -c '^WP|' "$SYSREF_DB" 2>/dev/null || echo 0)"
    printf "  %-20s %s\n" "Log Files:" "$(grep -c '^LOG|' "$SYSREF_DB" 2>/dev/null || echo 0)"
    printf "  %-20s %s\n" "Total Entries:" "$(wc -l < "$SYSREF_DB")"
    echo ""
}

# Export functions
export -f db_get_user
export -f db_get_all_users
export -f db_get_user_databases
export -f db_get_user_domains
export -f db_get_database_owner
#############################################################################
# SIMPLE KEY-VALUE STORE (for cross-module session data)
#############################################################################

# Store a key-value pair in the reference database
store_reference() {
    local key="$1"
    local value="$2"

    if [ -z "$key" ] || [ -z "$value" ]; then
        return 1
    fi

    # Use REF prefix for simple key-value pairs
    echo "REF|$key|$value" >> "$SYSREF_DB"
}

# Retrieve the most recent value for a key
get_reference() {
    local key="$1"

    if [ -z "$key" ] || [ ! -f "$SYSREF_DB" ]; then
        return 1
    fi

    # Get the most recent value (last occurrence)
    grep "^REF|$key|" "$SYSREF_DB" 2>/dev/null | tail -1 | cut -d'|' -f3
}

# Get domain status from reference database
# Usage: get_domain_status "domain.com"
# Returns: http_code|https_code|status_summary or empty if not found
get_domain_status() {
    local domain="$1"

    if [ -z "$domain" ] || [ ! -f "$SYSREF_DB" ]; then
        return 1
    fi

    # Get domain record (DOMAIN|domain|owner|doc_root|log_path|php|primary|type|alias|http|https|status)
    local record=$(grep "^DOMAIN|${domain}|" "$SYSREF_DB" 2>/dev/null | head -1)

    if [ -z "$record" ]; then
        return 1
    fi

    # Extract fields 10, 11, 12 (http_code, https_code, status_summary)
    echo "$record" | awk -F'|' '{print $10"|"$11"|"$12}'
}

# Get all domains with their status codes
# Returns: domain|http_code|https_code|status_summary (one per line)
get_all_domain_statuses() {
    if [ ! -f "$SYSREF_DB" ]; then
        return 1
    fi

    grep "^DOMAIN|" "$SYSREF_DB" 2>/dev/null | awk -F'|' '{print $2"|"$10"|"$11"|"$12}'
}

# Check if domain is healthy (200 OK on either HTTP or HTTPS)
# Usage: is_domain_healthy "domain.com" && echo "healthy"
is_domain_healthy() {
    local domain="$1"
    local status=$(get_domain_status "$domain")

    [ -z "$status" ] && return 1

    # Parse status
    IFS='|' read -r http_code https_code status_summary <<< "$status"

    # Healthy if either HTTP or HTTPS returns 200
    if [ "$http_code" = "200" ] || [ "$https_code" = "200" ]; then
        return 0
    fi

    return 1
}

export -f store_reference
export -f get_reference
export -f db_get_all_wordpress
export -f db_get_system_info
export -f db_get_health_metric
export -f db_is_system_under_load
export -f db_has_network_issues
export -f db_is_under_attack
export -f db_get_all_health
export -f db_is_fresh
export -f db_ensure_fresh
export -f db_rebuild
export -f get_domain_status
export -f get_all_domain_statuses
export -f is_domain_healthy
