#!/usr/bin/env pwsh
<#
.SYNOPSIS
    查询3D原图/AI原图/实拍原图/视频截图的资产信息
.DESCRIPTION
    使用 SSH 连接到生产服务器查询数据库
    首次使用需要配置 SSH 密钥或手动输入密码
.PARAMETER Target
    目标系统：production（正式）或 shadow（影子），默认 shadow
.EXAMPLE
    .\scripts\query-3d-original-assets.ps1
.EXAMPLE
    .\scripts\query-3d-original-assets.ps1 -Target production
#>

param(
    [Parameter(Mandatory=$false)]
    [ValidateSet('production', 'shadow')]
    [string]$Target = 'shadow'
)

$ErrorActionPreference = 'Stop'

# 配置
$ServerIP = "192.168.58.20"

# 根据目标选择容器和数据库
if ($Target -eq 'production') {
    $ContainerName = "youyidian-3d-material"
    $DatabaseName = "lobechat"
} else {
    $ContainerName = "youyidian-3d-shadow"
    $DatabaseName = "lobechat_stage"
}

# 使用 here-string 定义 SQL，避免引号问题
$SqlQuery = @'
SELECT 
    m.id, 
    m."materialType", 
    m."productModel", 
    COUNT(a.id) as asset_count, 
    STRING_AGG(DISTINCT a.asset_type, ', ') as asset_types 
FROM materials m 
LEFT JOIN material_assets a ON m.id = a.material_id 
WHERE m."materialType" IN ('任务-3D原图', '任务-AI原图', '任务-实拍原图', '任务-视频截图') 
    AND m."deletedAt" IS NULL 
GROUP BY m.id, m."materialType", m."productModel" 
LIMIT 5;
'@

Write-Host "========================================" -ForegroundColor Cyan
Write-Host "查询3D原图等类型的资产信息" -ForegroundColor Cyan
Write-Host "========================================" -ForegroundColor Cyan
Write-Host "目标系统: $Target" -ForegroundColor Yellow
Write-Host "容器名称: $ContainerName" -ForegroundColor Yellow
Write-Host "数据库名: $DatabaseName" -ForegroundColor Yellow
Write-Host "========================================`n" -ForegroundColor Cyan

try {
    # 使用 Base64 编码
    $QueryBytes = [System.Text.Encoding]::UTF8.GetBytes($SqlQuery)
    $QueryBase64 = [Convert]::ToBase64String($QueryBytes)
    
    # 构建远程命令（通过 PostgreSQL 容器执行查询）
    $RemoteCommand = "echo '$QueryBase64' | base64 -d | docker exec -i gongxiangpan-postgres-1 psql -U gongxiangpan -d $DatabaseName"
    
    Write-Host "执行查询（可能需要输入密码）..." -ForegroundColor Green
    # 使用 ssh（推荐配置密钥以避免每次输入密码）
    $Result = ssh -o StrictHostKeyChecking=no root@$ServerIP $RemoteCommand
    
    if ($LASTEXITCODE -eq 0) {
        Write-Host "`n查询成功！" -ForegroundColor Green
        Write-Host "========================================" -ForegroundColor Cyan
        Write-Host $Result
        Write-Host "========================================" -ForegroundColor Cyan
        Write-Host "`n提示：为避免每次输入密码，可以配置 SSH 密钥：" -ForegroundColor Yellow
        Write-Host "ssh-keygen -t rsa" -ForegroundColor Gray
        Write-Host "ssh-copy-id root@$ServerIP" -ForegroundColor Gray
    } else {
        Write-Host "`n查询失败！退出码: $LASTEXITCODE" -ForegroundColor Red
        Write-Host $Result -ForegroundColor Red
        exit 1
    }
} catch {
    Write-Host "`n错误: $_" -ForegroundColor Red
    Write-Host "`n提示：请确保可以通过 SSH 连接到服务器：" -ForegroundColor Yellow
    Write-Host "ssh root@$ServerIP" -ForegroundColor Gray
    exit 1
}