# ========================================
# execute-migration.ps1
# Schema迁移执行脚本（Windows PowerShell）
# ========================================
# 功能：
# 1. 备份当前数据库
# 2. 依次执行5个迁移脚本
# 3. 验证迁移结果
# 
# 使用方法：
# .\execute-migration.ps1
# ========================================

# 设置错误时停止
$ErrorActionPreference = "Stop"

# 数据库连接信息
$DB_HOST = "localhost"
$DB_PORT = "5432"
$DB_NAME = "ai_clinical_research"
$DB_USER = "postgres"
$DB_PASS = "postgres"

# 设置PostgreSQL密码环境变量
$env:PGPASSWORD = $DB_PASS

# 脚本目录
$SCRIPT_DIR = Split-Path -Parent $MyInvocation.MyCommand.Path

Write-Host "========================================" -ForegroundColor Cyan
Write-Host "Schema迁移执行脚本 - V1.0" -ForegroundColor Cyan
Write-Host "========================================" -ForegroundColor Cyan
Write-Host ""

# ========================================
# 第一步：检查PostgreSQL连接
# ========================================
Write-Host "[1/6] 检查PostgreSQL连接..." -ForegroundColor Yellow

try {
    $testConnection = psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c "SELECT version();" 2>&1
    if ($LASTEXITCODE -eq 0) {
        Write-Host "✅ PostgreSQL连接成功" -ForegroundColor Green
    } else {
        throw "PostgreSQL连接失败"
    }
} catch {
    Write-Host "❌ 无法连接到PostgreSQL数据库" -ForegroundColor Red
    Write-Host "错误信息: $_" -ForegroundColor Red
    Write-Host ""
    Write-Host "请检查：" -ForegroundColor Yellow
    Write-Host "1. PostgreSQL服务是否正在运行" -ForegroundColor Yellow
    Write-Host "2. 数据库连接信息是否正确" -ForegroundColor Yellow
    Write-Host "3. psql命令是否在PATH中" -ForegroundColor Yellow
    exit 1
}

Write-Host ""

# ========================================
# 第二步：备份当前数据库
# ========================================
Write-Host "[2/6] 备份当前数据库..." -ForegroundColor Yellow

$timestamp = Get-Date -Format "yyyyMMdd_HHmmss"
$backupFile = Join-Path $SCRIPT_DIR "backup_before_migration_$timestamp.sql"

Write-Host "备份文件: $backupFile" -ForegroundColor Gray

try {
    $output = pg_dump -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f $backupFile 2>&1
    
    if (Test-Path $backupFile) {
        $fileSize = (Get-Item $backupFile).Length / 1KB
        Write-Host "✅ 数据库备份成功 (大小: $([math]::Round($fileSize, 2)) KB)" -ForegroundColor Green
    } else {
        throw "备份文件未生成"
    }
} catch {
    Write-Host "❌ 数据库备份失败" -ForegroundColor Red
    Write-Host "错误信息: $_" -ForegroundColor Red
    exit 1
}

Write-Host ""

# ========================================
# 第三步：确认执行迁移
# ========================================
Write-Host "[3/6] 迁移确认" -ForegroundColor Yellow
Write-Host ""
Write-Host "即将执行以下操作：" -ForegroundColor White
Write-Host "  1. 创建10个Schema" -ForegroundColor Gray
Write-Host "  2. 迁移platform_schema（1个表）" -ForegroundColor Gray
Write-Host "  3. 迁移aia_schema（5个表）" -ForegroundColor Gray
Write-Host "  4. 迁移pkb_schema（5个表）" -ForegroundColor Gray
Write-Host "  5. 全局验证" -ForegroundColor Gray
Write-Host ""
Write-Host "⚠️  这将修改数据库结构！" -ForegroundColor Red
Write-Host "✅ 数据库已备份到: $backupFile" -ForegroundColor Green
Write-Host ""

$confirmation = Read-Host "确认执行迁移？(输入 YES 继续，其他键取消)"

if ($confirmation -ne "YES") {
    Write-Host "❌ 迁移已取消" -ForegroundColor Yellow
    exit 0
}

Write-Host ""

# ========================================
# 第四步：执行迁移脚本
# ========================================
Write-Host "[4/6] 执行迁移脚本..." -ForegroundColor Yellow
Write-Host ""

# 迁移脚本列表
$migrationScripts = @(
    @{Name="001-create-all-10-schemas.sql"; Description="创建10个Schema"},
    @{Name="002-migrate-platform.sql"; Description="迁移platform_schema"},
    @{Name="003-migrate-aia.sql"; Description="迁移aia_schema"},
    @{Name="004-migrate-pkb.sql"; Description="迁移pkb_schema"},
    @{Name="005-validate-all.sql"; Description="全局验证"}
)

$successCount = 0
$failedScripts = @()

foreach ($script in $migrationScripts) {
    $scriptPath = Join-Path $SCRIPT_DIR $script.Name
    Write-Host "  执行: $($script.Name) - $($script.Description)..." -ForegroundColor Cyan
    
    if (-not (Test-Path $scriptPath)) {
        Write-Host "  ❌ 脚本文件不存在: $scriptPath" -ForegroundColor Red
        $failedScripts += $script.Name
        continue
    }
    
    try {
        # 执行SQL脚本
        $output = psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -f $scriptPath 2>&1
        
        if ($LASTEXITCODE -eq 0) {
            Write-Host "  ✅ 成功" -ForegroundColor Green
            $successCount++
        } else {
            throw "脚本执行返回错误代码: $LASTEXITCODE"
        }
    } catch {
        Write-Host "  ❌ 失败" -ForegroundColor Red
        Write-Host "  错误: $_" -ForegroundColor Red
        $failedScripts += $script.Name
    }
    
    Write-Host ""
}

# ========================================
# 第五步：迁移结果总结
# ========================================
Write-Host "[5/6] 迁移结果总结" -ForegroundColor Yellow
Write-Host ""

Write-Host "总计: $($migrationScripts.Count) 个脚本" -ForegroundColor White
Write-Host "成功: $successCount 个" -ForegroundColor Green
Write-Host "失败: $($failedScripts.Count) 个" -ForegroundColor $(if ($failedScripts.Count -gt 0) {"Red"} else {"Green"})

if ($failedScripts.Count -gt 0) {
    Write-Host ""
    Write-Host "失败的脚本：" -ForegroundColor Red
    foreach ($failed in $failedScripts) {
        Write-Host "  - $failed" -ForegroundColor Red
    }
    Write-Host ""
    Write-Host "⚠️  迁移未完全成功！" -ForegroundColor Red
    Write-Host "建议：检查错误日志，修复问题后重新执行" -ForegroundColor Yellow
    Write-Host "回滚：可使用备份文件恢复 -> $backupFile" -ForegroundColor Yellow
} else {
    Write-Host ""
    Write-Host "✅ 所有迁移脚本执行成功！" -ForegroundColor Green
}

Write-Host ""

# ========================================
# 第六步：验证迁移结果
# ========================================
if ($successCount -eq $migrationScripts.Count) {
    Write-Host "[6/6] 验证迁移结果..." -ForegroundColor Yellow
    Write-Host ""
    
    # 验证Schema数量
    Write-Host "验证Schema创建..." -ForegroundColor Cyan
    $schemaQuery = @"
SELECT COUNT(*) FROM information_schema.schemata
WHERE schema_name IN (
    'platform_schema', 'aia_schema', 'pkb_schema',
    'asl_schema', 'common_schema', 'dc_schema',
    'rvw_schema', 'admin_schema', 'ssa_schema', 'st_schema'
);
"@
    
    try {
        $schemaCount = psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c $schemaQuery 2>&1
        $schemaCount = $schemaCount.Trim()
        
        if ($schemaCount -eq "10") {
            Write-Host "✅ 10个Schema全部创建成功" -ForegroundColor Green
        } else {
            Write-Host "⚠️  Schema数量异常: 预期10个，实际${schemaCount}个" -ForegroundColor Yellow
        }
    } catch {
        Write-Host "⚠️  无法验证Schema数量" -ForegroundColor Yellow
    }
    
    Write-Host ""
    
    # 验证数据迁移
    Write-Host "验证数据迁移..." -ForegroundColor Cyan
    
    $tables = @(
        @{Schema="platform_schema"; Table="users"},
        @{Schema="aia_schema"; Table="projects"},
        @{Schema="aia_schema"; Table="conversations"},
        @{Schema="pkb_schema"; Table="knowledge_bases"},
        @{Schema="pkb_schema"; Table="documents"}
    )
    
    foreach ($tbl in $tables) {
        try {
            $countQuery = "SELECT COUNT(*) FROM $($tbl.Schema).$($tbl.Table);"
            $count = psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -t -c $countQuery 2>&1
            $count = $count.Trim()
            Write-Host "  $($tbl.Schema).$($tbl.Table): $count 条记录" -ForegroundColor Gray
        } catch {
            Write-Host "  ⚠️  无法查询 $($tbl.Schema).$($tbl.Table)" -ForegroundColor Yellow
        }
    }
    
    Write-Host ""
    Write-Host "========================================" -ForegroundColor Cyan
    Write-Host "✅ Schema迁移执行完成！" -ForegroundColor Green
    Write-Host "========================================" -ForegroundColor Cyan
    Write-Host ""
    Write-Host "下一步操作：" -ForegroundColor Yellow
    Write-Host "1. 更新Prisma配置 (backend/prisma/schema.prisma)" -ForegroundColor Gray
    Write-Host "2. 生成Prisma Client (npx prisma generate)" -ForegroundColor Gray
    Write-Host "3. 更新代码以使用新Schema" -ForegroundColor Gray
    Write-Host "4. 测试现有功能" -ForegroundColor Gray
    Write-Host ""
    Write-Host "备份文件保存在: $backupFile" -ForegroundColor Cyan
    Write-Host ""
} else {
    Write-Host "[6/6] 跳过验证（因为迁移未完全成功）" -ForegroundColor Yellow
    Write-Host ""
}

# 清理环境变量
Remove-Item Env:\PGPASSWORD

Write-Host "脚本执行完成！" -ForegroundColor Green