#!/bin/bash
# ==============================================================================
# RDM RSP API - Database Connection Test Script
# ==============================================================================
# Tests connectivity to the SQL Server database from Docker container
#
# Usage:
#   ./test-db-connection.sh                    # Test database connection
#   ./test-db-connection.sh --from-container   # Test from inside running container
#   ./test-db-connection.sh --host-only        # Test from host only (no Docker)
# ==============================================================================

set -e  # Exit on any error

# ==============================================================================
# Configuration - UPDATE THESE VALUES
# ==============================================================================
DB_SERVER="${DB_SERVER:-your-server-ip}"
DB_PORT="${DB_PORT:-1433}"
DB_USER="${DB_USER:-your-username}"
DB_PASSWORD="${DB_PASSWORD:-your-password}"
DB_NAME="${DB_NAME:-your-database}"

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

# ==============================================================================
# Functions
# ==============================================================================

print_header() {
    echo -e "${BLUE}"
    echo "=============================================================================="
    echo " RDM RSP API - Database Connection Test"
    echo "=============================================================================="
    echo -e "${NC}"
}

print_step() {
    echo -e "${GREEN}[TEST]${NC} $1"
}

print_pass() {
    echo -e "${GREEN}[PASS]${NC} $1"
}

print_fail() {
    echo -e "${RED}[FAIL]${NC} $1"
}

print_warning() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

print_info() {
    echo -e "${BLUE}[INFO]${NC} $1"
}

# ==============================================================================
# Load configuration from .env if available
# ==============================================================================
load_env_config() {
    if [[ -f ".env" ]]; then
        print_info "Loading configuration from .env file..."

        # Extract server from connection string
        local conn_string=$(grep "^DB_CONNECTION=" .env | cut -d'=' -f2-)

        if [[ -n "$conn_string" ]]; then
            # Parse Data Source
            local data_source=$(echo "$conn_string" | grep -oP 'Data Source=\K[^;]+' || true)
            if [[ -n "$data_source" ]]; then
                DB_SERVER="$data_source"
            fi

            # Parse Initial Catalog
            local catalog=$(echo "$conn_string" | grep -oP 'Initial Catalog=\K[^;]+' || true)
            if [[ -n "$catalog" ]]; then
                DB_NAME="$catalog"
            fi

            # Parse User Id
            local user=$(echo "$conn_string" | grep -oP 'User Id=\K[^;]+' || true)
            if [[ -n "$user" ]]; then
                DB_USER="$user"
            fi

            # Parse Password
            local password=$(echo "$conn_string" | grep -oP 'Password=\K[^;]+' || true)
            if [[ -n "$password" ]]; then
                DB_PASSWORD="$password"
            fi
        fi
    fi
}

# ==============================================================================
# Test 1: Basic Network Connectivity (TCP)
# ==============================================================================
test_tcp_connectivity() {
    print_step "Testing TCP connectivity to ${DB_SERVER}:${DB_PORT}..."

    # Try multiple methods
    local connected=false

    # Method 1: Using nc (netcat)
    if command -v nc &> /dev/null; then
        if nc -zv -w5 "$DB_SERVER" "$DB_PORT" 2>&1 | grep -q "succeeded\|open\|Connected"; then
            connected=true
        fi
    fi

    # Method 2: Using timeout + bash
    if [[ "$connected" == false ]]; then
        if timeout 5 bash -c "echo > /dev/tcp/${DB_SERVER}/${DB_PORT}" 2>/dev/null; then
            connected=true
        fi
    fi

    # Method 3: Using telnet
    if [[ "$connected" == false ]] && command -v telnet &> /dev/null; then
        if echo quit | timeout 5 telnet "$DB_SERVER" "$DB_PORT" 2>&1 | grep -q "Connected"; then
            connected=true
        fi
    fi

    # Method 4: Using curl (TCP mode)
    if [[ "$connected" == false ]] && command -v curl &> /dev/null; then
        if curl --connect-timeout 5 "telnet://${DB_SERVER}:${DB_PORT}" 2>&1 | grep -q "Connected"; then
            connected=true
        fi
    fi

    if [[ "$connected" == true ]]; then
        print_pass "TCP connection to ${DB_SERVER}:${DB_PORT} successful"
        return 0
    else
        print_fail "Cannot connect to ${DB_SERVER}:${DB_PORT}"
        print_info "Check firewall rules and network connectivity"
        return 1
    fi
}

# ==============================================================================
# Test 2: DNS Resolution
# ==============================================================================
test_dns_resolution() {
    print_step "Testing DNS resolution..."

    # Test if we can resolve Google DNS (basic connectivity check)
    if host "google.com" &> /dev/null || nslookup "google.com" &> /dev/null || ping -c 1 "google.com" &> /dev/null 2>&1; then
        print_pass "DNS resolution working"
        return 0
    else
        print_warning "DNS resolution may have issues"
        return 1
    fi
}

# ==============================================================================
# Test 3: SQL Server Connection (using sqlcmd or Docker)
# ==============================================================================
test_sql_connection() {
    print_step "Testing SQL Server connection to ${DB_NAME}..."

    # Method 1: Using sqlcmd if available
    if command -v sqlcmd &> /dev/null; then
        if sqlcmd -S "${DB_SERVER},${DB_PORT}" -U "$DB_USER" -P "$DB_PASSWORD" -d "$DB_NAME" -Q "SELECT 1" -h -1 &> /dev/null; then
            print_pass "Connected to ${DB_NAME} via sqlcmd"
            return 0
        fi
    fi

    # Method 2: Using Docker with SQL Server tools
    if docker info &> /dev/null 2>&1; then
        local result=$(docker run --rm --network host \
            mcr.microsoft.com/mssql-tools:latest \
            /opt/mssql-tools/bin/sqlcmd \
            -S "${DB_SERVER},${DB_PORT}" \
            -U "$DB_USER" \
            -P "$DB_PASSWORD" \
            -d "$DB_NAME" \
            -Q "SELECT 'CONNECTION_OK'" \
            -h -1 2>&1)

        if echo "$result" | grep -q "CONNECTION_OK"; then
            print_pass "Connected to ${DB_NAME} via Docker sqlcmd"
            return 0
        else
            print_fail "Failed to connect to ${DB_NAME}"
            echo "Error: $result"
            return 1
        fi
    fi

    print_warning "sqlcmd not available, skipping SQL connection test"
    return 0
}

# ==============================================================================
# Test 4: Test from Running Container
# ==============================================================================
test_from_container() {
    print_step "Testing connectivity from inside Docker container..."

    local container_name="rdm-rsp-api"

    # Check if container is running
    if ! docker ps --format '{{.Names}}' | grep -q "^${container_name}$"; then
        print_warning "Container ${container_name} is not running"
        print_info "Start the container first: docker-compose up -d"
        return 1
    fi

    # Test TCP connectivity from inside container
    print_step "Testing TCP from container to ${DB_SERVER}:${DB_PORT}..."

    local tcp_result=$(docker exec "$container_name" \
        timeout 5 bash -c "echo > /dev/tcp/${DB_SERVER}/${DB_PORT} && echo 'OK'" 2>&1 || echo "FAIL")

    if [[ "$tcp_result" == "OK" ]]; then
        print_pass "TCP connectivity from container successful"
    else
        print_fail "TCP connectivity from container failed"
        print_info "Check Docker network configuration"
        return 1
    fi

    # Test swagger endpoint
    print_step "Testing application swagger endpoint..."

    local health_result=$(docker exec "$container_name" \
        curl -sf http://localhost:8080/swagger/index.html 2>&1 && echo "OK" || echo "UNHEALTHY")

    if [[ "$health_result" == *"OK"* ]]; then
        print_pass "Application swagger check passed"
    else
        print_fail "Application swagger check failed"
        print_info "Check application logs: docker logs ${container_name}"
        return 1
    fi

    return 0
}

# ==============================================================================
# Test 5: Connection String Validation
# ==============================================================================
test_connection_strings() {
    print_step "Validating connection string format..."

    local env_file=".env"

    if [[ ! -f "$env_file" ]]; then
        print_warning ".env file not found, skipping connection string validation"
        return 0
    fi

    local conn_string=$(grep "^DB_CONNECTION=" "$env_file" | cut -d'=' -f2-)

    if [[ -z "$conn_string" ]]; then
        print_fail "DB_CONNECTION not found in .env"
        return 1
    fi

    # Check if connection string contains placeholder values
    if echo "$conn_string" | grep -qi "your-server-ip\|your-database\|your-username\|your-password"; then
        print_fail "Connection string contains placeholder values"
        print_info "Please update DB_CONNECTION in .env file"
        return 1
    fi

    # Check if connection string contains TrustServerCertificate
    if echo "$conn_string" | grep -qi "TrustServerCertificate=True"; then
        print_pass "Connection string has TrustServerCertificate=True"
    else
        print_warning "Connection string missing TrustServerCertificate=True"
    fi

    # Check for Data Source
    if echo "$conn_string" | grep -qi "Data Source="; then
        print_pass "Connection string has Data Source configured"
    else
        print_fail "Connection string missing Data Source"
        return 1
    fi

    return 0
}

# ==============================================================================
# Test 6: Firewall Check
# ==============================================================================
test_firewall_hints() {
    print_step "Checking common firewall issues..."

    # Check if we're on Linux with iptables
    if command -v iptables &> /dev/null; then
        local drop_rules=$(iptables -L OUTPUT -n 2>/dev/null | grep -c "DROP\|REJECT" || echo "0")
        if [[ "$drop_rules" -gt 0 ]]; then
            print_warning "Found ${drop_rules} outbound DROP/REJECT rules in iptables"
            print_info "Ensure outbound connections to ${DB_SERVER}:${DB_PORT} are allowed"
        fi
    fi

    # Check if ufw is active
    if command -v ufw &> /dev/null; then
        if ufw status 2>/dev/null | grep -q "Status: active"; then
            print_info "UFW firewall is active"
            print_info "Ensure outbound to ${DB_SERVER}:${DB_PORT} is allowed"
        fi
    fi

    print_pass "Firewall hint check completed"
}

# ==============================================================================
# Summary Report
# ==============================================================================
print_summary() {
    local tcp_status=$1
    local dns_status=$2
    local sql_status=$3

    echo ""
    echo -e "${BLUE}=============================================================================="
    echo " TEST SUMMARY"
    echo "==============================================================================${NC}"
    echo ""
    echo "Target Server:    ${DB_SERVER}:${DB_PORT}"
    echo "Database:         ${DB_NAME}"
    echo ""
    echo "Results:"

    if [[ "$tcp_status" == "0" ]]; then
        echo -e "  TCP Connectivity:    ${GREEN}PASS${NC}"
    else
        echo -e "  TCP Connectivity:    ${RED}FAIL${NC}"
    fi

    if [[ "$dns_status" == "0" ]]; then
        echo -e "  DNS Resolution:      ${GREEN}PASS${NC}"
    else
        echo -e "  DNS Resolution:      ${YELLOW}WARN${NC}"
    fi

    if [[ "$sql_status" == "0" ]]; then
        echo -e "  SQL Connection:      ${GREEN}PASS${NC}"
    else
        echo -e "  SQL Connection:      ${RED}FAIL${NC}"
    fi

    echo ""

    if [[ "$tcp_status" == "0" && "$sql_status" == "0" ]]; then
        echo -e "${GREEN}All critical tests passed! Database connectivity is working.${NC}"
    else
        echo -e "${RED}Some tests failed. Review the output above for details.${NC}"
        echo ""
        echo "Troubleshooting tips:"
        echo "  1. Verify the database server is running and accessible"
        echo "  2. Check firewall rules on both client and server"
        echo "  3. Verify credentials are correct"
        echo "  4. Ensure SQL Server is listening on port ${DB_PORT}"
        echo "  5. Check if the database exists and user has permissions"
    fi
    echo ""
}

# ==============================================================================
# Main
# ==============================================================================

print_header

# Parse arguments
FROM_CONTAINER=false
HOST_ONLY=false

while [[ $# -gt 0 ]]; do
    case $1 in
        --from-container)
            FROM_CONTAINER=true
            shift
            ;;
        --host-only)
            HOST_ONLY=true
            shift
            ;;
        --help)
            echo "Usage: $0 [OPTIONS]"
            echo ""
            echo "Options:"
            echo "  (none)              Run all tests"
            echo "  --from-container    Test from inside running container"
            echo "  --host-only         Only test from host (no Docker)"
            echo "  --help              Show this help"
            exit 0
            ;;
        *)
            echo "Unknown option: $1"
            exit 1
            ;;
    esac
done

# Load configuration from .env
load_env_config

# Run tests
tcp_status=0
dns_status=0
sql_status=0

if [[ "$FROM_CONTAINER" == true ]]; then
    test_from_container
    exit $?
fi

echo ""
echo "=== Network Tests ==="
echo ""

test_tcp_connectivity || tcp_status=1
test_dns_resolution || dns_status=1

echo ""
echo "=== Database Connection Tests ==="
echo ""

if [[ "$HOST_ONLY" == false ]]; then
    test_sql_connection || sql_status=1
else
    print_info "Skipping SQL connection tests (--host-only mode)"
fi

echo ""
echo "=== Configuration Tests ==="
echo ""

test_connection_strings
test_firewall_hints

# Print summary
print_summary "$tcp_status" "$dns_status" "$sql_status"

# Exit with appropriate code
if [[ "$tcp_status" == "0" ]]; then
    exit 0
else
    exit 1
fi