#!/usr/bin/env bash
# =============================================================================
# prod-tunnel: Manage SSH tunnel to production database
# =============================================================================
# Usage:
#   ./devtools/prod-tunnel start   - Start tunnel (port 54333 -> prod:5433)
#   ./devtools/prod-tunnel stop    - Stop tunnel
#   ./devtools/prod-tunnel status  - Check if tunnel is running
#   ./devtools/prod-tunnel test    - Test database connection
# =============================================================================

set -euo pipefail

LOCAL_PORT=54333
REMOTE_PORT=5433
REMOTE_HOST="93.95.228.142"
SSH_KEY="$HOME/.ssh/id_ed25519_1984"

# Colors
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[0;33m'
NC='\033[0m'

get_tunnel_pid() {
    lsof -ti :$LOCAL_PORT 2>/dev/null || true
}

cmd_start() {
    local pid=$(get_tunnel_pid)
    if [[ -n "$pid" ]]; then
        echo -e "${YELLOW}Tunnel already running (PID: $pid)${NC}"
        return 0
    fi

    echo -e "Starting SSH tunnel: localhost:$LOCAL_PORT -> $REMOTE_HOST:$REMOTE_PORT"
    ssh -o IdentitiesOnly=yes \
        -i "$SSH_KEY" \
        -fN \
        -L "$LOCAL_PORT:localhost:$REMOTE_PORT" \
        "root@$REMOTE_HOST"

    sleep 1
    pid=$(get_tunnel_pid)
    if [[ -n "$pid" ]]; then
        echo -e "${GREEN}Tunnel started (PID: $pid)${NC}"
    else
        echo -e "${RED}Failed to start tunnel${NC}"
        return 1
    fi
}

cmd_stop() {
    local pid=$(get_tunnel_pid)
    if [[ -z "$pid" ]]; then
        echo -e "${YELLOW}No tunnel running${NC}"
        return 0
    fi

    echo "Stopping tunnel (PID: $pid)..."
    kill "$pid"
    echo -e "${GREEN}Tunnel stopped${NC}"
}

cmd_status() {
    local pid=$(get_tunnel_pid)
    if [[ -n "$pid" ]]; then
        echo -e "${GREEN}Tunnel running${NC} (PID: $pid, port: $LOCAL_PORT)"
        return 0
    else
        echo -e "${RED}Tunnel not running${NC}"
        return 1
    fi
}

cmd_test() {
    local pid=$(get_tunnel_pid)
    if [[ -z "$pid" ]]; then
        echo -e "${RED}Tunnel not running. Start it first: $0 start${NC}"
        return 1
    fi

    echo "Testing database connection..."

    # Get credentials from .env or use defaults
    local db_user="conversation"
    local db_name="conversation_assistant"
    local db_pass=""

    if [[ -f "server/.env" ]]; then
        db_pass=$(grep -E "^DB_PASSWORD=" server/.env | cut -d'=' -f2 || true)
    fi

    if [[ -z "$db_pass" ]]; then
        echo -e "${YELLOW}No DB_PASSWORD in server/.env, fetching from prod...${NC}"
        db_pass=$(ssh -o IdentitiesOnly=yes -i "$SSH_KEY" "root@$REMOTE_HOST" \
            "docker exec conversation-assistant-postgres env | grep POSTGRES_PASSWORD | cut -d'=' -f2")
    fi

    if PGPASSWORD="$db_pass" psql -h localhost -p "$LOCAL_PORT" -U "$db_user" -d "$db_name" \
        -c "SELECT COUNT(*) as conversations FROM conversations;" 2>/dev/null; then
        echo -e "${GREEN}Database connection successful${NC}"
    else
        echo -e "${RED}Database connection failed${NC}"
        return 1
    fi
}

# Main
case "${1:-status}" in
    start)  cmd_start ;;
    stop)   cmd_stop ;;
    status) cmd_status ;;
    test)   cmd_test ;;
    *)
        echo "Usage: $0 {start|stop|status|test}"
        exit 1
        ;;
esac
