# ============================================
# 修復 PostgreSQL 端口綁定
# ============================================
#
# 複製以下命令到 Ubuntu Server 執行
# SSH 登入: ssh ubuntu@10.1.0.254
#
# ============================================

# 步驟 1: 檢查現有配置
echo "Current PostgreSQL configuration:"
docker ps | grep postgres
docker port postgres

# 步驟 2: 獲取 postgres 密碼 (如果不知道,設定一個新的)
# 例如: POSTGRES_PASSWORD="yourpassword"
POSTGRES_PASSWORD="DC1qaz2wsx"

# 步驟 3: 檢查資料卷
VOLUME_NAME=$(docker inspect postgres --format '{{range .Mounts}}{{if eq .Destination "/var/lib/postgresql/data"}}{{.Name}}{{end}}{{end}}' 2>/dev/null || echo "postgres-data")
echo "Using volume: $VOLUME_NAME"

# 步驟 4: 停止並移除舊容器 (資料不會遺失,在 volume 中)
docker stop postgres
docker rm postgres

# 步驟 5: 重新啟動,綁定到所有網路介面 (0.0.0.0:5432)
docker run -d \
  --name postgres \
  --restart unless-stopped \
  -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" \
  -e POSTGRES_INITDB_ARGS="--encoding=UTF-8 --locale=en_US.UTF-8" \
  -e TZ=Asia/Taipei \
  -p 0.0.0.0:5432:5432 \
  -v ${VOLUME_NAME}:/var/lib/postgresql/data \
  postgres:16

# 步驟 6: 等待啟動
echo "Waiting for PostgreSQL to start..."
sleep 5

# 步驟 7: 驗證
echo ""
echo "Verification:"
docker ps | grep postgres
docker port postgres
docker exec postgres pg_isready -U postgres

# 步驟 8: 檢查資料庫
echo ""
echo "Databases:"
docker exec postgres psql -U postgres -lqt | cut -d \| -f 1 | grep -v "^$"

echo ""
echo "Testing hr_portal database:"
docker exec postgres psql -U hr_user -d hr_portal -c "\dt"

echo ""
echo "=========================================="
echo "  PostgreSQL is now accessible!"
echo "=========================================="
echo "Connection: postgresql://hr_user:DC1qaz2wsx@10.1.0.254:5432/hr_portal"
echo ""
