# ====================================
# HR Portal - 簡化版資料庫設定
# ====================================

param(
    [string]$DBHost = "10.1.0.254",
    [string]$DBName = "hr_portal",
    [string]$DBUser = "hr_user",
    [string]$DBPassword = "",
    [string]$PostgresPassword = "",
    [string]$ContainerName = "postgres"
)

Write-Host "=== HR Portal 資料庫設定 ===" -ForegroundColor Cyan
Write-Host ""

# 檢查必要參數
if (-not $DBPassword) {
    $DBPassword = Read-Host "請輸入 hr_user 的密碼" -AsSecureString
    $DBPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto(
        [Runtime.InteropServices.Marshal]::SecureStringToBSTR($DBPassword)
    )
}

if (-not $PostgresPassword) {
    $PostgresPassword = Read-Host "請輸入 postgres 超級用戶密碼" -AsSecureString
    $PostgresPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto(
        [Runtime.InteropServices.Marshal]::SecureStringToBSTR($PostgresPassword)
    )
}

Write-Host ""
Write-Host "設定資訊:" -ForegroundColor Yellow
Write-Host "  資料庫主機: $DBHost"
Write-Host "  資料庫名稱: $DBName"
Write-Host "  資料庫用戶: $DBUser"
Write-Host "  容器名稱: $ContainerName"
Write-Host ""

$continue = Read-Host "是否繼續? (y/N)"
if ($continue -ne 'y' -and $continue -ne 'Y') {
    Write-Host "已取消" -ForegroundColor Yellow
    exit 0
}

Write-Host ""

# === 步驟 1: 創建用戶 ===
Write-Host "步驟 1/4: 創建資料庫用戶..." -ForegroundColor Green

$createUserSQL = @"
DO `$`$
BEGIN
    IF NOT EXISTS (SELECT FROM pg_user WHERE usename = '$DBUser') THEN
        CREATE USER $DBUser WITH PASSWORD '$DBPassword';
        RAISE NOTICE 'User $DBUser created';
    ELSE
        RAISE NOTICE 'User $DBUser already exists';
    END IF;
END
`$`$;
"@

$createUserCmd = "echo `"$createUserSQL`" | docker exec -i $ContainerName psql -U postgres"

try {
    ssh ubuntu@$DBHost $createUserCmd
    Write-Host "✓ 用戶創建完成" -ForegroundColor Green
} catch {
    Write-Host "✗ 用戶創建失敗: $_" -ForegroundColor Red
    exit 1
}

Write-Host ""

# === 步驟 2: 創建資料庫 ===
Write-Host "步驟 2/4: 創建資料庫..." -ForegroundColor Green

$createDbCmd = "docker exec $ContainerName psql -U postgres -c `"CREATE DATABASE $DBName OWNER $DBUser;`" 2>&1 || echo 'Database may already exist'"

try {
    ssh ubuntu@$DBHost $createDbCmd
    Write-Host "✓ 資料庫創建完成" -ForegroundColor Green
} catch {
    Write-Host "⚠ 資料庫可能已存在" -ForegroundColor Yellow
}

Write-Host ""

# === 步驟 3: 授予權限 ===
Write-Host "步驟 3/4: 授予權限..." -ForegroundColor Green

$grantSQL = @"
GRANT ALL PRIVILEGES ON DATABASE $DBName TO $DBUser;
GRANT ALL PRIVILEGES ON SCHEMA public TO $DBUser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO $DBUser;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO $DBUser;
"@

$grantCmd = "echo `"$grantSQL`" | docker exec -i $ContainerName psql -U postgres -d $DBName"

try {
    ssh ubuntu@$DBHost $grantCmd
    Write-Host "✓ 權限授予完成" -ForegroundColor Green
} catch {
    Write-Host "✗ 權限授予失敗: $_" -ForegroundColor Red
}

Write-Host ""

# === 步驟 4: 執行 Schema ===
Write-Host "步驟 4/4: 執行資料庫 Schema..." -ForegroundColor Green

$schemaFile = Join-Path $PSScriptRoot "init-db.sql"

if (Test-Path $schemaFile) {
    Write-Host "  讀取 Schema 檔案..." -ForegroundColor Gray

    # 上傳 SQL 檔案到遠端
    scp $schemaFile ubuntu@${DBHost}:/tmp/hr-portal-schema.sql

    # 執行 SQL
    $execSchemaCmd = "docker exec -i $ContainerName psql -U $DBUser -d $DBName < /tmp/hr-portal-schema.sql"

    try {
        ssh ubuntu@$DBHost $execSchemaCmd
        Write-Host "✓ Schema 執行完成" -ForegroundColor Green

        # 清理臨時檔案
        ssh ubuntu@$DBHost "rm /tmp/hr-portal-schema.sql"
    } catch {
        Write-Host "✗ Schema 執行失敗: $_" -ForegroundColor Red
        exit 1
    }
} else {
    Write-Host "✗ 找不到 Schema 檔案: $schemaFile" -ForegroundColor Red
    exit 1
}

Write-Host ""

# === 驗證 ===
Write-Host "驗證資料庫設定..." -ForegroundColor Yellow

$verifyCmd = "docker exec $ContainerName psql -U $DBUser -d $DBName -c '\dt' 2>&1"

try {
    $tables = ssh ubuntu@$DBHost $verifyCmd

    if ($tables -match "business_units|employees|email_accounts") {
        Write-Host "✓ 資料表已成功建立" -ForegroundColor Green
        Write-Host ""
        Write-Host "找到的資料表:" -ForegroundColor Cyan
        Write-Host $tables -ForegroundColor Gray
    } else {
        Write-Host "⚠ 資料表可能未正確建立" -ForegroundColor Yellow
    }
} catch {
    Write-Host "⚠ 無法驗證資料表" -ForegroundColor Yellow
}

Write-Host ""
Write-Host "=== 設定完成! ===" -ForegroundColor Green
Write-Host ""

# 顯示連接字串
Write-Host "資料庫連接資訊:" -ForegroundColor Cyan
Write-Host "  Host: $DBHost"
Write-Host "  Port: 5432"
Write-Host "  Database: $DBName"
Write-Host "  User: $DBUser"
Write-Host "  Password: $DBPassword"
Write-Host ""

$databaseUrl = "postgresql://${DBUser}:${DBPassword}@${DBHost}:5432/${DBName}"
Write-Host "DATABASE_URL:" -ForegroundColor Yellow
Write-Host "  $databaseUrl" -ForegroundColor White
Write-Host ""

Write-Host "下一步:" -ForegroundColor Green
Write-Host "  1. 複製上面的 DATABASE_URL"
Write-Host "  2. 編輯 backend\.env"
Write-Host "  3. 設定 DATABASE_URL 環境變數"
Write-Host "  4. 啟動後端: uvicorn app.main:app --reload"
Write-Host ""