# AI多语言资讯系统 - Windows PowerShell 部署脚本
# 适用于PostgreSQL/Supabase环境

param(
    [string]$DBHost = "localhost",
    [string]$DBPort = "5432", 
    [string]$DBName = "ai_news",
    [string]$DBUser = "postgres",
    [switch]$Help
)

# 显示帮助信息
if ($Help) {
    Write-Host "AI多语言资讯系统 - 部署脚本" -ForegroundColor Cyan
    Write-Host ""
    Write-Host "用法: .\deploy.ps1 [选项]" -ForegroundColor White
    Write-Host ""
    Write-Host "选项:" -ForegroundColor Yellow
    Write-Host "  -DBHost      数据库主机 (默认: localhost)" -ForegroundColor White
    Write-Host "  -DBPort      数据库端口 (默认: 5432)" -ForegroundColor White
    Write-Host "  -DBName      数据库名称 (默认: ai_news)" -ForegroundColor White
    Write-Host "  -DBUser      数据库用户 (默认: postgres)" -ForegroundColor White
    Write-Host "  -Help        显示此帮助信息" -ForegroundColor White
    Write-Host ""
    Write-Host "示例:" -ForegroundColor Green
    Write-Host "  .\deploy.ps1 -DBHost 'localhost' -DBName 'my_news_db'" -ForegroundColor White
    exit 0
}

# 获取脚本所在目录
$ScriptDir = Split-Path -Parent $MyInvocation.MyCommand.Path

# 打印带颜色的消息函数
function Write-ColorMessage {
    param(
        [string]$Message,
        [string]$Color = "White"
    )
    Write-Host $Message -ForegroundColor $Color
}

# 检查PostgreSQL连接
function Test-DatabaseConnection {
    Write-ColorMessage "🔍 检查数据库连接..." "Blue"
    
    # 检查psql是否可用
    try {
        $psqlVersion = psql --version 2>$null
        if ($LASTEXITCODE -eq 0) {
            Write-ColorMessage "✅ 找到PostgreSQL客户端工具" "Green"
        } else {
            throw "psql not found"
        }
    } catch {
        Write-ColorMessage "⚠️  未找到psql命令，请安装PostgreSQL客户端工具或手动执行SQL文件" "Yellow"
        return $false
    }
    
    # 测试连接
    try {
        $env:PGPASSWORD = Read-Host "请输入数据库密码" -AsSecureString | ConvertFrom-SecureString -AsPlainText
        $connectionTest = psql -h $DBHost -p $DBPort -d $DBName -U $DBUser -c '\q' 2>$null
        if ($LASTEXITCODE -eq 0) {
            Write-ColorMessage "✅ 数据库连接成功" "Green"
            return $true
        } else {
            Write-ColorMessage "❌ 数据库连接失败，请检查连接参数" "Red"
            return $false
        }
    } catch {
        Write-ColorMessage "❌ 数据库连接失败: $_" "Red"
        return $false
    }
}

# 执行SQL文件
function Invoke-SqlFile {
    param(
        [string]$SqlFile,
        [string]$Description
    )
    
    Write-ColorMessage "📊 执行 $Description..." "Blue"
    
    if (-not (Test-Path $SqlFile)) {
        Write-ColorMessage "❌ 文件不存在: $SqlFile" "Red"
        return $false
    }
    
    try {
        psql -h $DBHost -p $DBPort -d $DBName -U $DBUser -f $SqlFile
        if ($LASTEXITCODE -eq 0) {
            Write-ColorMessage "✅ $Description 执行成功" "Green"
            return $true
        } else {
            Write-ColorMessage "❌ $Description 执行失败 (退出代码: $LASTEXITCODE)" "Red"
            return $false
        }
    } catch {
        Write-ColorMessage "❌ $Description 执行异常: $_" "Red"
        return $false
    }
}

# 验证部署结果
function Test-DeploymentResult {
    Write-ColorMessage "🧪 验证部署结果..." "Blue"
    
    try {
        # 检查表数量
        $tableCount = psql -h $DBHost -p $DBPort -d $DBName -U $DBUser -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public' AND table_name LIKE 'ak_%';" 2>$null
        $tableCount = $tableCount.Trim()
        
        if ([int]$tableCount -gt 15) {
            Write-ColorMessage "✅ 数据库表创建成功 ($tableCount 个表)" "Green"
        } else {
            Write-ColorMessage "⚠️  数据库表数量异常 ($tableCount 个表)" "Yellow"
        }
        
        # 检查RLS策略
        $policyCount = psql -h $DBHost -p $DBPort -d $DBName -U $DBUser -t -c "SELECT COUNT(*) FROM pg_policies WHERE schemaname = 'public';" 2>$null
        $policyCount = $policyCount.Trim()
        
        if ([int]$policyCount -gt 5) {
            Write-ColorMessage "✅ RLS安全策略创建成功 ($policyCount 个策略)" "Green"
        } else {
            Write-ColorMessage "⚠️  RLS策略数量异常 ($policyCount 个策略)" "Yellow"
        }
        
        # 检查索引
        $indexCount = psql -h $DBHost -p $DBPort -d $DBName -U $DBUser -t -c "SELECT COUNT(*) FROM pg_indexes WHERE schemaname = 'public' AND indexname LIKE 'idx_%';" 2>$null
        $indexCount = $indexCount.Trim()
        
        if ([int]$indexCount -gt 15) {
            Write-ColorMessage "✅ 性能索引创建成功 ($indexCount 个索引)" "Green"
        } else {
            Write-ColorMessage "⚠️  索引数量异常 ($indexCount 个索引)" "Yellow"
        }
        
    } catch {
        Write-ColorMessage "⚠️  验证过程中出现错误: $_" "Yellow"
    }
}

# 主部署流程
function Start-Deployment {
    Write-ColorMessage "===========================================" "Blue"
    Write-ColorMessage "  AI多语言资讯系统 - 自动部署工具" "Blue"
    Write-ColorMessage "==========================================" "Blue"
    Write-ColorMessage ""
    
    # 显示连接信息
    Write-ColorMessage "📋 连接信息:" "Cyan"
    Write-ColorMessage "   主机: $DBHost" "White"
    Write-ColorMessage "   端口: $DBPort" "White"
    Write-ColorMessage "   数据库: $DBName" "White"
    Write-ColorMessage "   用户: $DBUser" "White"
    Write-ColorMessage ""
    
    # 检查SQL文件是否存在
    $mainSqlFile = Join-Path $ScriptDir "ai_multilingual_news_database.sql"
    if (-not (Test-Path $mainSqlFile)) {
        Write-ColorMessage "❌ 主数据库文件不存在: ai_multilingual_news_database.sql" "Red"
        exit 1
    }
    
    # 测试数据库连接
    $connectionOk = Test-DatabaseConnection
    
    # 部署步骤1: 创建数据库结构
    Write-ColorMessage "📊 步骤1: 创建数据库结构..." "Yellow"
    if ($connectionOk) {
        $result1 = Invoke-SqlFile -SqlFile $mainSqlFile -Description "数据库结构创建"
    } else {
        Write-ColorMessage "请手动执行: ai_multilingual_news_database.sql" "Yellow"
        $result1 = $false
    }
    
    # 部署步骤2: 插入演示数据（可选）
    $demoDataFile = Join-Path $ScriptDir "topics_comments_init_data.sql"
    if (Test-Path $demoDataFile) {
        Write-ColorMessage "📝 步骤2: 插入演示数据..." "Yellow"
        $insertDemo = Read-Host "是否插入演示数据? (y/N)"
        if ($insertDemo -match '^[Yy是]') {
            if ($connectionOk) {
                $result2 = Invoke-SqlFile -SqlFile $demoDataFile -Description "演示数据插入"
            } else {
                Write-ColorMessage "请手动执行: topics_comments_init_data.sql" "Yellow"
            }
        } else {
            Write-ColorMessage "⏭️  跳过演示数据插入" "Blue"
        }
    }
    
    # 验证部署
    if ($connectionOk) {
        Test-DeploymentResult
    }
    
    Write-ColorMessage ""
    Write-ColorMessage "===========================================" "Green"
    Write-ColorMessage "🎉 部署完成！" "Green"
    Write-ColorMessage "===========================================" "Green"
    Write-ColorMessage ""
    Write-ColorMessage "💡 系统功能包括:" "Blue"
    Write-ColorMessage "   - 多语言内容管理和AI翻译" "White"
    Write-ColorMessage "   - 专题、评论、收藏、转发功能" "White"
    Write-ColorMessage "   - 用户行为分析和推荐系统" "White"
    Write-ColorMessage "   - 智能聊天和成本控制" "White"
    Write-ColorMessage "   - 完整的安全访问控制(RLS)" "White"
    Write-ColorMessage ""
    Write-ColorMessage "📚 相关文档:" "Blue"
    Write-ColorMessage "   - SQL_DEPLOYMENT_GUIDE.md (详细部署指南)" "White"
    Write-ColorMessage "   - FINAL_DEPLOYMENT_VALIDATION.md (验证报告)" "White"
    Write-ColorMessage "   - INDEX_ERROR_SOLUTION.md (错误解决方案)" "White"
    Write-ColorMessage "===========================================" "Green"
}

# 运行主程序
Start-Deployment