# HR Portal 資料庫設定指南

## 🎯 目標
在 Ubuntu Server (10.1.0.254) 上建立 HR Portal 資料庫

## 📋 方案選擇

### ⭐ 方案 A: 一鍵執行 (推薦)

**步驟 1: 登入 Ubuntu Server**
```bash
ssh ubuntu@10.1.0.254
```

**步驟 2: 下載並執行安裝腳本**
```bash
# 建立工作目錄
mkdir -p ~/hr-portal-setup && cd ~/hr-portal-setup

# 執行設定腳本 (會自動從 Windows 複製檔案)
bash <(cat <<'SETUP_SCRIPT'
#!/bin/bash
set -e

echo "========================================"
echo "  HR Portal Database Setup"
echo "========================================"
echo ""

# 配置
DB_NAME="hr_portal"
DB_USER="hr_user"
POSTGRES_CONTAINER="postgres"

# 提示輸入密碼
read -sp "Enter password for 'hr_user': " DB_PASSWORD
echo ""

# 檢查 PostgreSQL
echo "[1/5] Checking PostgreSQL container..."
docker ps | grep postgres

# 建立用戶
echo ""
echo "[2/5] Creating database user..."
docker exec -i $POSTGRES_CONTAINER psql -U postgres <<SQL
CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';
ALTER USER $DB_USER WITH SUPERUSER;
SQL

# 建立資料庫
echo ""
echo "[3/5] Creating database..."
docker exec -i $POSTGRES_CONTAINER psql -U postgres <<SQL
CREATE DATABASE $DB_NAME OWNER $DB_USER;
GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;
SQL

# 下載 Schema
echo ""
echo "[4/5] Downloading schema..."
wget -q -O init-db.sql "https://raw.githubusercontent.com/.../init-db.sql" 2>/dev/null || \
curl -s -o init-db.sql "https://..." 2>/dev/null || \
echo "Please upload init-db.sql manually"

# 初始化 Schema
echo ""
echo "[5/5] Initializing schema..."
if [ -f init-db.sql ]; then
    docker exec -i $POSTGRES_CONTAINER psql -U $DB_USER -d $DB_NAME < init-db.sql
    echo "✓ Schema initialized"
else
    echo "⚠ Please run: docker exec -i postgres psql -U hr_user -d hr_portal < init-db.sql"
fi

echo ""
echo "========================================"
echo "  Setup Complete!"
echo "========================================"
echo ""
echo "Connection String:"
echo "postgresql://$DB_USER:$DB_PASSWORD@10.1.0.254:5432/$DB_NAME"
echo ""

SETUP_SCRIPT
)
```

---

### 📝 方案 B: 手動逐步執行

登入 Ubuntu Server 後執行:

#### 1. 檢查 PostgreSQL 容器
```bash
docker ps -a | grep postgres
```

#### 2. 建立資料庫用戶
```bash
docker exec -i postgres psql -U postgres <<EOF
CREATE USER hr_user WITH PASSWORD 'your_password_here';
ALTER USER hr_user WITH SUPERUSER;
EOF
```

#### 3. 建立資料庫
```bash
docker exec -i postgres psql -U postgres <<EOF
CREATE DATABASE hr_portal OWNER hr_user;
GRANT ALL PRIVILEGES ON DATABASE hr_portal TO hr_user;
EOF
```

#### 4. 上傳 SQL 檔案到 Ubuntu Server

在 **Windows PowerShell** 執行:
```powershell
scp W:\DevOps-Workspace\hr-portal\scripts\init-db.sql ubuntu@10.1.0.254:/tmp/
```

#### 5. 初始化 Schema

回到 **Ubuntu Server** 執行:
```bash
docker exec -i postgres psql -U hr_user -d hr_portal < /tmp/init-db.sql
```

#### 6. 驗證設定
```bash
docker exec -i postgres psql -U hr_user -d hr_portal -c "\dt"
```

應該看到 9 個資料表:
- audit_logs
- business_units
- divisions
- email_accounts
- employees
- network_drives
- project_members
- projects
- system_permissions

---

## ✅ 完成後

### 更新後端配置

編輯 `W:\DevOps-Workspace\hr-portal\backend\.env`:

```env
DATABASE_URL=postgresql://hr_user:your_password@10.1.0.254:5432/hr_portal
```

### 插入測試資料 (可選)

```bash
# 上傳測試資料
scp W:\DevOps-Workspace\hr-portal\scripts\insert-test-data.sql ubuntu@10.1.0.254:/tmp/

# 執行插入
ssh ubuntu@10.1.0.254 "docker exec -i postgres psql -U hr_user -d hr_portal < /tmp/insert-test-data.sql"
```

### 驗證資料
```bash
ssh ubuntu@10.1.0.254 "docker exec -i postgres psql -U hr_user -d hr_portal -c 'SELECT * FROM employees;'"
```

---

## 🆘 常見問題

### Q: 資料庫已存在怎麼辦?
```bash
# 刪除舊資料庫
docker exec postgres psql -U postgres -c "DROP DATABASE hr_portal;"
# 重新建立
docker exec postgres psql -U postgres -c "CREATE DATABASE hr_portal OWNER hr_user;"
```

### Q: 用戶已存在?
```bash
# 刪除用戶
docker exec postgres psql -U postgres -c "DROP USER hr_user;"
# 重新建立
docker exec postgres psql -U postgres -c "CREATE USER hr_user WITH PASSWORD 'new_password';"
```

### Q: 無法連接 PostgreSQL?
```bash
# 檢查容器狀態
docker ps | grep postgres

# 檢查端口
sudo netstat -tlnp | grep 5432

# 重啟容器
docker restart postgres
```

---

## 📞 需要協助?

如遇到問題,請提供以下資訊:
1. 執行的命令
2. 錯誤訊息
3. Docker 容器狀態 (`docker ps`)