function Convert-DataFormat {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory, ValueFromPipeline)]
        $InputObject,

        [Parameter(Mandatory)]
        [ValidateSet('Json', 'Csv', 'Xml', 'Yaml', 'HashTable')]
        [string]$ToFormat,

        [int]$Depth = 10
    )

    process {
        switch ($ToFormat) {
            'Json' {
                $InputObject | ConvertTo-Json -Depth $Depth -EnumsAsStrings
            }
            'Csv' {
                if ($InputObject -is [string]) {
                    $InputObject | ConvertFrom-Csv | ConvertTo-Csv -NoTypeInformation
                }
                else {
                    $InputObject | ConvertTo-Csv -NoTypeInformation
                }
            }
            'Xml' {
                $InputObject | ConvertTo-Xml -NoTypeInformation -As Stream
            }
            'HashTable' {
                if ($InputObject -is [string]) {
                    $json = $InputObject
                }
                else {
                    $json = $InputObject | ConvertTo-Json -Depth $Depth -Compress
                }
                [System.Text.Json.JsonSerializer]::Deserialize(
                    $json,
                    [System.Collections.Generic.Dictionary[string, object]],
                    (New-Object System.Text.Json.JsonSerializerOptions -Property @{
                        PropertyNameCaseInsensitive = $true
                    })
                )
            }
            'Yaml' {
                # YAML 没有内置 cmdlet，通过 JSON 中转后手动格式化
                $json = if ($InputObject -is [string]) { $InputObject } else {
                    $InputObject | ConvertTo-Json -Depth $Depth
                }
                $obj = $json | ConvertFrom-Json
                ConvertTo-YamlString -InputObject $obj -Indent 0
            }
        }
    }
}

function ConvertTo-YamlString {
    param($InputObject, [int]$Indent = 0)
    $space = '  ' * $Indent
    $sb = [System.Text.StringBuilder]::new()

    if ($InputObject -is [System.Collections.IEnumerable] -and
        $InputObject -isnot [string]) {
        foreach ($item in $InputObject) {
            $null = $sb.Append("${space}- ")
            if ($item -is [System.Collections.IDictionary] -or
                $item -is [PSCustomObject]) {
                $null = $sb.AppendLine()
                $null = $sb.Append((ConvertTo-YamlString $item ($Indent + 1)))
            }
            else {
                $null = $sb.AppendLine("'$item'")
            }
        }
    }
    elseif ($InputObject -is [System.Collections.IDictionary] -or
        $InputObject -is [PSCustomObject]) {
        $props = if ($InputObject -is [System.Collections.IDictionary]) {
            $InputObject.GetEnumerator()
        }
        else {
            $InputObject.PSObject.Properties
        }
        foreach ($prop in $props) {
            $name = $prop.Key ?? $prop.Name
            $val = $prop.Value ?? $prop
            $null = $sb.Append("${space}${name}: ")
            if ($val -is [System.Collections.IEnumerable] -and
                $val -isnot [string]) {
                $null = $sb.AppendLine()
                $null = $sb.Append((ConvertTo-YamlString $val ($Indent + 1)))
            }
            elseif ($val -is [System.Collections.IDictionary] -or
                $val -is [PSCustomObject]) {
                $null = $sb.AppendLine()
                $null = $sb.Append((ConvertTo-YamlString $val ($Indent + 1)))
            }
            else {
                $null = $sb.AppendLine("'$val'")
            }
        }
    }
    else {
        $null = $sb.AppendLine("${space}'$InputObject'")
    }

    $sb.ToString()
}

# 示例：CSV 转 JSON
$csvData = @'
Name,Department,Salary,StartDate
张三,工程部,25000,2024-03-15
李四,市场部,18000,2023-08-20
王五,工程部,30000,2022-11-01
赵六,人事部,22000,2025-01-10
'@

Write-Host "=== CSV 转 JSON ===" -ForegroundColor Cyan
$jsonOutput = $csvData | ConvertFrom-Csv | Convert-DataFormat -ToFormat Json
$jsonOutput

Write-Host "`n=== JSON 转 HashTable ===" -ForegroundColor Cyan
$jsonOutput | Convert-DataFormat -ToFormat HashTable |
    ForEach-Object { $_.GetEnumerator() } |
    Format-Table Key, Value -AutoSize

Write-Host "`n=== CSV 转 XML ===" -ForegroundColor Cyan
$xmlOutput = $csvData | ConvertFrom-Csv | Convert-DataFormat -ToFormat Xml
$xmlOutput | Select-Object -First 10

function Invoke-DataCleanse {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory, ValueFromPipeline)]
        [PSCustomObject]$InputObject,

        # 字段映射表：旧名 → 新名
        [Parameter(Mandatory)]
        [hashtable]$FieldMap,

        # 需要转换为 DateTime 的字段
        [string[]]$DateFields,

        # 需要转换为数值的字段
        [string[]]$NumericFields,

        # 默认空值填充
        $DefaultNullValue = 'N/A'
    )

    begin {
        $seen = [System.Collections.Generic.HashSet[string]]::new()
        $duplicateCount = 0
        $totalProcessed = 0
    }

    process {
        $totalProcessed++

        # 构建去重键（使用所有字段值的拼接）
        $keyParts = $InputObject.PSObject.Properties.Value |
            Where-Object { $_ } |
            ForEach-Object { $_.ToString() }
        $dedupKey = $keyParts -join '|'

        if (-not $seen.Add($dedupKey)) {
            $duplicateCount++
            return
        }

        $result = [ordered]@{}

        foreach ($prop in $InputObject.PSObject.Properties) {
            $fieldName = $prop.Name
            $value = $prop.Value

            # 字段名映射
            if ($FieldMap.ContainsKey($fieldName)) {
                $fieldName = $FieldMap[$fieldName]
            }

            # 空值处理
            if ([string]::IsNullOrWhiteSpace($value)) {
                $value = $DefaultNullValue
            }
            else {
                $value = $value.Trim()

                # 日期字段转换
                if ($DateFields -and $fieldName -in $DateFields) {
                    if ($value -ne $DefaultNullValue) {
                        if ([datetime]::TryParse($value, [ref]$parsed)) {
                            $value = $parsed
                        }
                    }
                }

                # 数值字段转换
                if ($NumericFields -and $fieldName -in $NumericFields) {
                    if ($value -ne $DefaultNullValue) {
                        $cleaned = $value -replace '[,，]', ''
                        if ([double]::TryParse($cleaned, [ref]$num)) {
                            $value = $num
                        }
                    }
                }
            }

            $result[$fieldName] = $value
        }

        [PSCustomObject]$result
    }

    end {
        Write-Verbose "处理完成: 总计 $totalProcessed 条, 去除重复 $duplicateCount 条, 输出 $($totalProcessed - $duplicateCount) 条"
    }
}

# 模拟原始脏数据
$rawData = @'
emp_name,dept,salary,start_date,email
张三,工程部,25000,2024-03-15,zhangsan@corp.com
李四,市场部,18000,2023-08-20,lisi@corp.com
张三,工程部,25000,2024-03-15,zhangsan@corp.com
王五,,30000,,
赵六,人事部,22,000,2025-01-10,zhaoliu@corp.com
孙七,工程部,28000,invalid-date,sunqi@corp.com
'@

$fieldMapping = @{
    'emp_name'   = 'EmployeeName'
    'dept'       = 'Department'
    'salary'     = 'AnnualSalary'
    'start_date' = 'StartDate'
    'email'      = 'ContactEmail'
}

$cleaned = $rawData | ConvertFrom-Csv |
    Invoke-DataCleanse `
        -FieldMap $fieldMapping `
        -DateFields 'StartDate' `
        -NumericFields 'AnnualSalary' `
        -DefaultNullValue '未知' `
        -Verbose

Write-Host "=== 清洗结果 ===" -ForegroundColor Cyan
$cleaned | Format-Table -AutoSize

Write-Host "`n=== 数据类型验证 ===" -ForegroundColor Cyan
$cleaned | Get-Member -MemberType NoteProperty |
    Select-Object Name, @{
        N = 'SampleType'
        E = {
            $val = $cleaned[0].($_.Name)
            if ($null -ne $val) { $val.GetType().Name } else { 'null' }
        }
    } | Format-Table -AutoSize

function Invoke-DataEtlPipeline {
    [CmdletBinding()]
    param(
        [string]$OutputPath = './etl-output',
        [switch]$IncludeSummary
    )

    # ---- Extract 阶段：从多源提取数据 ----

    Write-Host "[Extract] 从 CSV 提取服务器资产数据..." -ForegroundColor Yellow
    $serverCsv = @'
Hostname,IP,OS,CPU_CORES,RAM_GB,STATUS
WEB-01,192.168.1.10,Ubuntu 22.04,4,16,Running
WEB-02,192.168.1.11,Ubuntu 22.04,4,16,Running
DB-01,192.168.1.20,CentOS 7,8,64,Running
DB-02,192.168.1.21,CentOS 7,8,64,Stopped
CACHE-01,192.168.1.30,Ubuntu 22.04,2,8,Running
'@

    $servers = $serverCsv | ConvertFrom-Csv
    Write-Host "  提取 $($servers.Count) 条服务器记录"

    Write-Host "[Extract] 从 JSON 提取监控指标数据..." -ForegroundColor Yellow
    $metricsJson = @'
[
    {"host": "WEB-01", "cpu_pct": 45.2, "mem_pct": 62.1, "disk_pct": 33.7, "timestamp": "2026-02-23T08:00:00Z"},
    {"host": "WEB-02", "cpu_pct": 12.8, "mem_pct": 45.3, "disk_pct": 28.9, "timestamp": "2026-02-23T08:00:00Z"},
    {"host": "DB-01", "cpu_pct": 78.5, "mem_pct": 85.2, "disk_pct": 71.4, "timestamp": "2026-02-23T08:00:00Z"},
    {"host": "CACHE-01", "cpu_pct": 23.1, "mem_pct": 91.5, "disk_pct": 15.2, "timestamp": "2026-02-23T08:00:00Z"}
]
'@

    $metrics = $metricsJson | ConvertFrom-Json
    Write-Host "  提取 $($metrics.Count) 条监控指标"

    Write-Host "[Extract] 从 XML 提取告警配置..." -ForegroundColor Yellow
    $alertXml = @'
<alerts>
    <rule name="cpu_high" threshold="70" severity="critical" target_pattern="DB-*"/>
    <rule name="mem_warning" threshold="80" severity="warning" target_pattern="*"/>
    <rule name="disk_high" threshold="60" severity="warning" target_pattern="DB-*"/>
</alerts>
'@

    [xml]$xmlDoc = $alertXml
    $alertRules = $xmlDoc.alerts.rule | ForEach-Object {
        [PSCustomObject]@{
            RuleName      = $_.name
            Threshold     = [int]$_.threshold
            Severity      = $_.severity
            TargetPattern = $_.target_pattern
        }
    }
    Write-Host "  提取 $($alertRules.Count) 条告警规则"

    # ---- Transform 阶段：关联、转换、计算 ----

    Write-Host "`n[Transform] 关联服务器资产与监控指标..." -ForegroundColor Green

    $enriched = foreach ($srv in $servers) {
        $metric = $metrics | Where-Object { $_.host -eq $srv.Hostname }
        $triggeredAlerts = @()

        foreach ($rule in $alertRules) {
            $pattern = $rule.TargetPattern -replace '\*', '.*'
            if ($srv.Hostname -match "^$pattern$") {
                $violated = $false
                $fieldMap = @{
                    'cpu_high'    = 'cpu_pct'
                    'mem_warning' = 'mem_pct'
                    'disk_high'   = 'disk_pct'
                }
                $fieldName = $fieldMap[$rule.RuleName]
                if ($fieldName -and $metric -and
                    $metric.$fieldName -gt $rule.Threshold) {
                    $violated = $true
                }
                if ($violated) {
                    $triggeredAlerts += "[{0}] {1} ({2}% > {3}%)" -f
                        $rule.Severity, $rule.RuleName,
                        $metric.$fieldName, $rule.Threshold
                }
            }
        }

        [PSCustomObject][ordered]@{
            Hostname   = $srv.Hostname
            IPAddress  = $srv.IP
            OS         = $srv.OS
            CPU_Cores  = [int]$srv.CPU_CORES
            RAM_GB     = [int]$srv.RAM_GB
            Status     = $srv.STATUS
            CPU_Usage  = if ($metric) { $metric.cpu_pct } else { $null }
            MEM_Usage  = if ($metric) { $metric.mem_pct } else { $null }
            DISK_Usage = if ($metric) { $metric.disk_pct } else { $null }
            AlertCount = $triggeredAlerts.Count
            Alerts     = if ($triggeredAlerts) {
                $triggeredAlerts -join '; '
            } else { '无' }
        }
    }

    # ---- Load 阶段：输出到多种目标格式 ----

    $null = New-Item -ItemType Directory -Path $OutputPath -Force -ErrorAction SilentlyContinue

    Write-Host "`n[Load] 导出为 CSV 报表..." -ForegroundColor Magenta
    $csvFile = Join-Path $OutputPath 'server-report.csv'
    $enriched | ConvertTo-Csv -NoTypeInformation | Out-File $csvFile -Encoding utf8
    Write-Host "  已写入: $csvFile"

    Write-Host "[Load] 导出为 JSON..." -ForegroundColor Magenta
    $jsonFile = Join-Path $OutputPath 'server-report.json'
    $enriched | ConvertTo-Json -Depth 5 | Out-File $jsonFile -Encoding utf8
    Write-Host "  已写入: $jsonFile"

    Write-Host "[Load] 导出为 HTML 报表..." -ForegroundColor Magenta
    $htmlFile = Join-Path $OutputPath 'server-report.html'
    $htmlParams = @{
        Title  = '服务器状态报表'
        Body   = '<h1>服务器状态报表</h1>' +
                 '<p>生成时间: {0}</p>' -f (Get-Date -Format 'yyyy-MM-dd HH:mm:ss')
        Head   = '<style>table { border-collapse: collapse; } ' +
                 'td, th { border: 1px solid #ccc; padding: 6px; } ' +
                 '.critical { color: red; font-weight: bold; }</style>'
    }
    $enriched | ConvertTo-Html @htmlParams | Out-File $htmlFile -Encoding utf8
    Write-Host "  已写入: $htmlFile"

    if ($IncludeSummary) {
        Write-Host "`n=== ETL 汇总 ===" -ForegroundColor Cyan
        Write-Host ("  服务器总数: {0}" -f $enriched.Count)
        Write-Host ("  运行中: {0}" -f ($enriched | Where-Object Status -eq 'Running').Count)
        Write-Host ("  已停机: {0}" -f ($enriched | Where-Object Status -eq 'Stopped').Count)
        Write-Host ("  触发告警的服务器: {0}" -f ($enriched | Where-Object AlertCount -gt 0).Count)
        Write-Host ("  平均 CPU 使用率: {0:N1}%" -f (
            ($enriched | Where-Object CPU_Usage | Measure-Object -Property CPU_Usage -Average).Average
        ))
        Write-Host ("  平均内存使用率: {0:N1}%" -f (
            ($enriched | Where-Object MEM_Usage | Measure-Object -Property MEM_Usage -Average).Average
        ))
    }

    return $enriched
}

# 执行完整 ETL 管道
$result = Invoke-DataEtlPipeline -IncludeSummary

Write-Host "`n=== 最终报表 ===" -ForegroundColor Cyan
$result | Format-Table Hostname, IPAddress, OS, Status,
    @{N='CPU%';E={'{0:N1}' -f $_.CPU_Usage}},
    @{N='MEM%';E={'{0:N1}' -f $_.MEM_Usage}},
    @{N='DISK%';E={'{0:N1}' -f $_.DISK_Usage}},
    AlertCount -AutoSize

# 创建示例 CSV：员工信息表
$employeeCsv = @"
Id,Name,Department,Salary,City
1,张三,工程部,25000,北京
2,李四,市场部,18000,上海
3,王五,工程部,30000,北京
4,赵六,人事部,15000,广州
5,钱七,市场部,22000,深圳
6,孙八,工程部,28000,北京
7,周九,人事部,16000,上海
8,吴十,市场部,20000,广州
"@ | Out-String

$employeeCsv.Trim() | Set-Content -Path ".\employees.csv" -Encoding UTF8

# 创建示例 CSV：部门预算表
$budgetCsv = @"
Department,Budget,Year
工程部,500000,2025
市场部,300000,2025
人事部,150000,2025
"@ | Out-String

$budgetCsv.Trim() | Set-Content -Path ".\budgets.csv" -Encoding UTF8

Write-Host "示例 CSV 文件已创建"

function Invoke-CsvSqlQuery {
    param(
        [Parameter(Mandatory)]
        [string[]]$CsvPath,

        [Parameter(Mandatory)]
        [string]$Query,

        [string]$Delimiter = ","
    )

    # 获取 CSV 文件所在目录作为数据源
    $directory = (Resolve-Path (Split-Path $CsvPath[0] -Parent)).Path

    # 构建 OLE DB 连接字符串
    $connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$directory;Extended Properties=`"Text;HDR=YES;FMT=Delimited($Delimiter);Charset=UTF8`""

    try {
        $connection = New-Object System.Data.OleDb.OleDbConnection($connectionString)
        $connection.Open()

        $command = New-Object System.Data.OleDb.OleDbCommand($Query, $connection)
        $adapter = New-Object System.Data.OleDb.OleDbDataAdapter($command)
        $dataset = New-Object System.Data.DataSet
        $adapter.Fill($dataset) | Out-Null

        $dataset.Tables[0]
    }
    finally {
        if ($connection.State -eq 'Open') {
            $connection.Close()
        }
    }
}

# 查询薪资大于 20000 的员工
$results = Invoke-CsvSqlQuery -CsvPath ".\employees.csv" -Query "SELECT Name, Department, Salary FROM employees.csv WHERE Salary > 20000 ORDER BY Salary DESC"
$results | Format-Table -AutoSize

function Invoke-CsvDataTableQuery {
    param(
        [Parameter(Mandatory)]
        [string]$CsvPath,

        [Parameter(Mandatory)]
        [string]$FilterExpression,

        [string[]]$SortColumns
    )

    # 将 CSV 导入 DataTable
    $data = Import-Csv -Path $CsvPath
    $dataTable = [System.Data.DataTable]::new("CsvData")

    # 根据第一行数据推断列类型
    $firstRow = $data[0]
    foreach ($property in $firstRow.PSObject.Properties) {
        $column = [System.Data.DataColumn]::new($property.Name)
        $dataTable.Columns.Add($column)
    }

    # 填充数据行
    foreach ($row in $data) {
        $dataRow = $dataTable.NewRow()
        foreach ($property in $row.PSObject.Properties) {
            $dataRow[$property.Name] = $property.Value
        }
        $dataTable.Rows.Add($dataRow)
    }

    # 执行查询
    $sortExpression = if ($SortColumns) { $SortColumns -join ", " } else { $null }
    $dataTable.Select($FilterExpression, $sortExpression) |
        ForEach-Object {
            $hashtable = @{}
            foreach ($col in $dataTable.Columns.ColumnName) {
                $hashtable[$col] = $_[$col]
            }
            [PSCustomObject]$hashtable
        }
}

# 查询北京的工程部员工并按薪资降序排列
$results = Invoke-CsvDataTableQuery -CsvPath ".\employees.csv" -FilterExpression "City = '北京' AND Department = '工程部'" -SortColumns "Salary DESC"
$results | Format-Table -AutoSize

function Get-CsvAggregate {
    param(
        [Parameter(Mandatory)]
        [string]$CsvPath,

        [Parameter(Mandatory)]
        [string]$GroupByColumn,

        [ValidateSet("Sum", "Average", "Count", "Min", "Max")]
        [string]$AggregateFunction = "Count",

        [string]$AggregateColumn
    )

    $data = Import-Csv -Path $CsvPath

    # 验证聚合列是否存在
    if ($AggregateColumn -and $AggregateFunction -ne "Count") {
        $sample = $data[0].PSObject.Properties.Name
        if ($AggregateColumn -notin $sample) {
            throw "列 '$AggregateColumn' 在 CSV 中不存在。可用列: $($sample -join ', ')"
        }
    }

    # 分组
    $groups = $data | Group-Object -Property $GroupByColumn

    $results = foreach ($group in $groups) {
        $aggregateValue = switch ($AggregateFunction) {
            "Count" { $group.Count }
            "Sum"   { ($group.Group | Measure-Object -Property $AggregateColumn -Sum).Sum }
            "Average" { [math]::Round(($group.Group | Measure-Object -Property $AggregateColumn -Average).Average, 2) }
            "Min"   { ($group.Group | Measure-Object -Property $AggregateColumn -Minimum).Minimum }
            "Max"   { ($group.Group | Measure-Object -Property $AggregateColumn -Maximum).Maximum }
        }

        [PSCustomObject]@{
            $GroupByColumn     = $group.Name
            "$AggregateFunction`($AggregateColumn)" = $aggregateValue
            RecordCount        = $group.Count
        }
    }

    $results
}

# 按部门统计平均薪资
Write-Host "`n=== 按部门统计平均薪资 ==="
Get-CsvAggregate -CsvPath ".\employees.csv" -GroupByColumn Department -AggregateFunction Average -AggregateColumn Salary |
    Format-Table -AutoSize

# 按城市统计人数
Write-Host "`n=== 按城市统计人数 ==="
Get-CsvAggregate -CsvPath ".\employees.csv" -GroupByColumn City -AggregateFunction Count |
    Format-Table -AutoSize

function Join-CsvData {
    param(
        [Parameter(Mandatory)]
        [string]$LeftCsvPath,

        [Parameter(Mandatory)]
        [string]$RightCsvPath,

        [Parameter(Mandatory)]
        [string]$LeftJoinColumn,

        [Parameter(Mandatory)]
        [string]$RightJoinColumn,

        [ValidateSet("Inner", "Left")]
        [string]$JoinType = "Inner"
    )

    $leftData = Import-Csv -Path $LeftCsvPath
    $rightData = Import-Csv -Path $RightCsvPath

    # 构建右侧数据的哈希表索引
    $rightIndex = @{}
    foreach ($rightRow in $rightData) {
        $key = $rightRow.$RightJoinColumn
        if (-not $rightIndex.ContainsKey($key)) {
            $rightIndex[$key] = @()
        }
        $rightIndex[$key] += $rightRow
    }

    # 获取列名集合
    $leftColumns = $leftData[0].PSObject.Properties.Name
    $rightColumns = $rightData[0].PSObject.Properties.Name | Where-Object { $_ -ne $RightJoinColumn }

    $results = foreach ($leftRow in $leftData) {
        $key = $leftRow.$LeftJoinColumn
        $matched = $rightIndex[$key]

        if ($matched) {
            foreach ($rightRow in $matched) {
                $row = [ordered]@{}
                foreach ($col in $leftColumns) {
                    $row[$col] = $leftRow.$col
                }
                foreach ($col in $rightColumns) {
                    $row[$col] = $rightRow.$col
                }
                [PSCustomObject]$row
            }
        }
        elseif ($JoinType -eq "Left") {
            $row = [ordered]@{}
            foreach ($col in $leftColumns) {
                $row[$col] = $leftRow.$col
            }
            foreach ($col in $rightColumns) {
                $row[$col] = $null
            }
            [PSCustomObject]$row
        }
    }

    $results
}

# 关联员工表和部门预算表
Write-Host "`n=== 员工与部门预算关联 ==="
Join-CsvData -LeftCsvPath ".\employees.csv" -RightCsvPath ".\budgets.csv" -LeftJoinColumn Department -RightJoinColumn Department |
    Format-Table Name, Department, Salary, Budget -AutoSize

function Get-IisLogStatistics {
    param(
        [Parameter(Mandatory)]
        [string]$LogFilePath,

        [int]$TopN = 10
    )

    # 解析 IIS 日志（跳过注释行）
    $logData = Get-Content -Path $LogFilePath |
        Where-Object { $_ -notlike "#*" -and $_.Trim() -ne "" } |
        ConvertFrom-Csv -Delimiter " "

    Write-Host "`n=== 状态码分布 ==="
    $logData | Group-Object -Property "sc-status" |
        Sort-Object Count -Descending |
        Select-Object Count, Name -First 5 |
        Format-Table @{Label="次数"; Expression={$_.Count}}, @{Label="状态码"; Expression={$_.Name}} -AutoSize

    Write-Host "`n=== 访问量前 $TopN 的 URI ==="
    $logData | Group-Object -Property "cs-uri-stem" |
        Sort-Object Count -Descending |
        Select-Object Count, Name -First $TopN |
        Format-Table @{Label="次数"; Expression={$_.Count}}, @{Label="URI"; Expression={$_.Name}} -AutoSize

    Write-Host "`n=== 5xx 错误详情 ==="
    $logData | Where-Object { $_."sc-status" -like "5*" } |
        Select-Object -First $TopN |
        Format-Table date, time, "c-ip", "cs-method", "cs-uri-stem", "sc-status" -AutoSize
}

# 模拟 IIS 日志进行分析（实际使用时传入真实日志路径）
$logContent = @"
date time c-ip cs-method cs-uri-stem sc-status time-taken
2025-08-11 08:01:10 192.168.1.100 GET /api/users 200 45
2025-08-11 08:01:11 192.168.1.101 GET /api/orders 500 1200
2025-08-11 08:01:12 192.168.1.100 GET /api/users 200 38
2025-08-11 08:01:13 10.0.0.50 POST /api/login 401 15
2025-08-11 08:01:14 192.168.1.102 GET /api/products 200 92
2025-08-11 08:01:15 192.168.1.101 GET /api/orders 503 3500
"@

$logContent | Set-Content -Path ".\iis-test.log" -Encoding UTF8
Get-IisLogStatistics -LogFilePath ".\iis-test.log" -TopN 5

# 场景：从遗留系统导出的 CSV 数据质量差，需要清洗后再导入新系统

$rawCsv = @"
EmployeeID,Name,Department,Salary,HireDate,Status
E001,张三,技术部,15000.50,2020-03-15,Active
E002,李四,市场部,,2021-07-20,Active
E003,王五,技术部,18000,2022-01-10,Inactive
E004,,财务部,12000,2023/06/01,Active
E005,赵六,技术部,20000.99,invalid,Active
E006,钱七,,16500,2020-11-30,Active
"@

function Import-AndCleanCsv {
    param(
        [Parameter(Mandatory)]
        [string[]]$CsvLines,

        [string]$DefaultDepartment = "未分配",

        [decimal]$DefaultSalary = 0
    )

    # 导入 CSV
    $data = $CsvLines | ConvertFrom-Csv

    $cleaned = foreach ($row in $data) {
        # 清洗姓名：去除空值
        $name = if ([string]::IsNullOrWhiteSpace($row.Name)) {
            "未知员工($($row.EmployeeID))"
        } else {
            $row.Name.Trim()
        }

        # 清洗部门：空值使用默认值
        $dept = if ([string]::IsNullOrWhiteSpace($row.Department)) {
            $DefaultDepartment
        } else {
            $row.Department.Trim()
        }

        # 清洗薪资：非数字使用默认值
        $salary = if ($row.Salary -match '^\d+\.?\d*$') {
            [decimal]$row.Salary
        } else {
            $DefaultSalary
        }

        # 清洗日期：多种格式统一转换
        $hireDate = $null
        if ($row.HireDate -match '^\d{4}-\d{2}-\d{2}$') {
            $hireDate = [datetime]::ParseExact($row.HireDate, "yyyy-MM-dd", $null)
        } elseif ($row.HireDate -match '^\d{4}/\d{2}/\d{2}$') {
            $hireDate = [datetime]::ParseExact($row.HireDate, "yyyy/MM/dd", $null)
        }

        # 输出清洗后的对象
        [PSCustomObject]@{
            EmployeeID = $row.EmployeeID
            Name       = $name
            Department = $dept
            Salary     = $salary
            HireDate   = if ($hireDate) { $hireDate.ToString("yyyy-MM-dd") } else { "N/A" }
            Status     = $row.Status
            Cleaned    = $true
        }
    }

    return $cleaned
}

$cleanData = Import-AndCleanCsv -CsvLines ($rawCsv -split "`n")
$cleanData | Format-Table -AutoSize

# 场景：将扁平的 CSV 数据转换为嵌套的 JSON 结构供 API 消费

$flatData = @"
ID,Name,Email,DeptID,DeptName,RoleID,RoleName
U001,张三,zhangsan@contoso.com,D001,技术部,R001,开发工程师
U002,李四,lisi@contoso.com,D001,技术部,R002,测试工程师
U003,王五,wangwu@contoso.com,D002,市场部,R003,市场经理
U004,赵六,zhaoliu@contoso.com,D002,市场部,R003,市场经理
"@

function ConvertFrom-FlatToNestedJson {
    param(
        [Parameter(Mandatory)]
        [string[]]$CsvLines
    )

    $data = $CsvLines | ConvertFrom-Csv

    # 按部门分组，构建嵌套结构
    $nested = $data | Group-Object -Property DeptID | ForEach-Object {
        $deptGroup = $_

        # 按角色分组
        $roles = $deptGroup.Group | Group-Object -Property RoleID | ForEach-Object {
            $roleGroup = $_
            $members = $roleGroup.Group | ForEach-Object {
                @{
                    id    = $_.ID
                    name  = $_.Name
                    email = $_.Email
                }
            }

            @{
                roleId   = $roleGroup.Name
                roleName = ($roleGroup.Group | Select-Object -First 1).RoleName
                members  = @($members)
            }
        }

        @{
            departmentId   = $deptGroup.Name
            departmentName = ($deptGroup.Group | Select-Object -First 1).DeptName
            roles          = @($roles)
        }
    }

    $result = @{
        exportDate = (Get-Date).ToString("yyyy-MM-ddTHH:mm:ss")
        totalCount = $data.Count
        departments = @($nested)
    }

    return $result | ConvertTo-Json -Depth 5
}

$jsonOutput = ConvertFrom-FlatToNestedJson -CsvLines ($flatData -split "`n")
Write-Host $jsonOutput

# 场景：从 API 获取的嵌套 JSON 需要展开为扁平的 CSV 供报表使用

$apiJson = @"
{
    "users": [
        {
            "id": "U001",
            "profile": {
                "name": "张三",
                "age": 28,
                "address": {
                    "city": "北京",
                    "district": "海淀区"
                }
            },
            "skills": ["PowerShell", "Python", "Docker"],
            "active": true
        },
        {
            "id": "U002",
            "profile": {
                "name": "李四",
                "age": 32,
                "address": {
                    "city": "上海",
                    "district": "浦东新区"
                }
            },
            "skills": ["C#", "SQL Server"],
            "active": false
        }
    ]
}
"@

function Expand-NestedJsonToCsv {
    param(
        [Parameter(Mandatory)]
        [string]$JsonString
    )

    $data = $JsonString | ConvertFrom-Json

    $flatRows = foreach ($user in $data.users) {
        [PSCustomObject]@{
            ID       = $user.id
            Name     = $user.profile.name
            Age      = $user.profile.age
            City     = $user.profile.address.city
            District = $user.profile.address.district
            Skills   = ($user.skills -join "; ")
            SkillCount = $user.skills.Count
            Active   = $user.active
        }
    }

    return $flatRows
}

$flatRows = Expand-NestedJsonToCsv -JsonString $apiJson
$flatRows | Export-Csv -Path "$env:TEMP\users_flat.csv" -NoTypeInformation -Encoding UTF8
Write-Host "已导出到 $env:TEMP\users_flat.csv"
Get-Content "$env:TEMP\users_flat.csv"

# 场景：定期从源系统迁移数据到目标系统，只迁移新增或变更的记录

function Start-IncrementalMigration {
    param(
        [Parameter(Mandatory)]
        [string]$SourcePath,

        [Parameter(Mandatory)]
        [string]$TargetPath,

        [Parameter(Mandatory)]
        [string]$KeyField,

        [string]$CompareField = "UpdatedAt"
    )

    # 加载源数据
    $source = Import-Csv -Path $SourcePath -Encoding UTF8
    $target = if (Test-Path $TargetPath) {
        Import-Csv -Path $TargetPath -Encoding UTF8
    } else {
        @()
    }

    # 构建目标数据的索引
    $targetIndex = @{}
    foreach ($row in $target) {
        $targetIndex[$row.$KeyField] = $row
    }

    $stats = @{
        Total    = $source.Count
        Inserted = 0
        Updated  = 0
        Skipped  = 0
    }

    $result = foreach ($srcRow in $source) {
        $key = $srcRow.$KeyField

        if ($targetIndex.ContainsKey($key)) {
            # 记录已存在，检查是否需要更新
            $tgtRow = $targetIndex[$key]
            $srcVal = $srcRow.$CompareField
            $tgtVal = $tgtRow.$CompareField

            if ($srcVal -gt $tgtVal) {
                # 源数据更新，覆盖目标
                $srcRow | Add-Member -MemberType NoteProperty -Name "_action" -Value "UPDATE" -Force
                $stats.Updated++
                $srcRow
            } else {
                # 无变化，跳过
                $stats.Skipped++
            }
        } else {
            # 新记录，插入
            $srcRow | Add-Member -MemberType NoteProperty -Name "_action" -Value "INSERT" -Force
            $stats.Inserted++
            $srcRow
        }
    }

    # 合并数据：保留未变更的目标记录 + 更新/新增的记录
    $unchanged = $target | Where-Object {
        $key = $_.$KeyField
        $srcKeys = $source | ForEach-Object { $_.$KeyField }
        $key -in $srcKeys -and -not ($result | Where-Object { $_.$KeyField -eq $key })
    }

    $finalData = @($unchanged) + @($result | Where-Object { $_._action -eq "UPDATE" }) +
                  @($result | Where-Object { $_._action -eq "INSERT" })

    # 移除内部字段后导出
    $finalData | ForEach-Object { $_.PSObject.Properties.Remove("_action") }
    $finalData | Export-Csv -Path $TargetPath -NoTypeInformation -Encoding UTF8

    Write-Host "迁移完成：" -ForegroundColor Green
    Write-Host "  总记录数：$($stats.Total)"
    Write-Host "  新增：$($stats.Inserted)" -ForegroundColor Cyan
    Write-Host "  更新：$($stats.Updated)" -ForegroundColor Yellow
    Write-Host "  跳过（无变化）：$($stats.Skipped)" -ForegroundColor Gray

    return $stats
}

# 使用示例（模拟源数据）
$sourceData = @"
ID,Name,Department,UpdatedAt
E001,张三,技术部,2025-08-01
E002,李四,市场部,2025-08-05
E003,王五,财务部,2025-08-06
"@

$targetData = @"
ID,Name,Department,UpdatedAt
E001,张三,技术部,2025-07-15
E002,李四,市场部,2025-08-05
"@

$sourceData | Set-Content "$env:TEMP\source.csv" -Encoding UTF8
$targetData | Set-Content "$env:TEMP\target.csv" -Encoding UTF8

Start-IncrementalMigration -SourcePath "$env:TEMP\source.csv" `
    -TargetPath "$env:TEMP\target.csv" `
    -KeyField "ID" `
    -CompareField "UpdatedAt"

# 场景：批量合并多个 CSV 文件，统一格式后输出为 JSON

function Merge-MultipleCsv {
    param(
        [Parameter(Mandatory)]
        [string]$InputFolder,

        [string]$Filter = "*.csv",

        [string]$OutputPath,

        [string[]]$FieldMapping
    )

    $allData = @()
    $files = Get-ChildItem -Path $InputFolder -Filter $Filter -File

    Write-Host "找到 $($files.Count) 个文件待合并" -ForegroundColor Cyan

    foreach ($file in $files) {
        Write-Host "  处理：$($file.Name)" -ForegroundColor Gray

        $csv = Import-Csv -Path $file.FullName -Encoding UTF8

        foreach ($row in $csv) {
            # 如果指定了字段映射，只提取需要的字段
            if ($FieldMapping) {
                $mappedRow = @{}
                foreach ($field in $FieldMapping) {
                    if ($row.PSObject.Properties[$field]) {
                        $mappedRow[$field] = $row.$field
                    }
                }
                $mappedRow["_sourceFile"] = $file.Name
                $allData += [PSCustomObject]$mappedRow
            } else {
                $row | Add-Member -MemberType NoteProperty -Name "_sourceFile" `
                    -Value $file.Name -Force
                $allData += $row
            }
        }
    }

    Write-Host "合并完成，共 $($allData.Count) 条记录" -ForegroundColor Green

    # 根据输出路径的扩展名决定格式
    if ($OutputPath) {
        $ext = [System.IO.Path]::GetExtension($OutputPath).ToLower()
        switch ($ext) {
            ".json" {
                $allData | ConvertTo-Json -Depth 3 | Set-Content $OutputPath -Encoding UTF8
            }
            ".csv" {
                $allData | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8
            }
            ".xml" {
                $allData | ConvertTo-Xml -NoTypeInformation | Select-Object -ExpandProperty OuterXml |
                    Set-Content $OutputPath -Encoding UTF8
            }
            default {
                $allData | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8
            }
        }
        Write-Host "已输出到：$OutputPath" -ForegroundColor Green
    }

    return $allData
}

# 批量格式转换工具
function Convert-DataFormat {
    param(
        [Parameter(Mandatory)]
        [string]$InputPath,

        [Parameter(Mandatory)]
        [string]$OutputPath
    )

    $ext = [System.IO.Path]::GetExtension($InputPath).ToLower()

    # 读取数据
    $data = switch ($ext) {
        ".csv"  { Import-Csv -Path $InputPath -Encoding UTF8 }
        ".json" { Get-Content $InputPath -Raw | ConvertFrom-Json }
        default { throw "不支持的输入格式：$ext" }
    }

    # 如果 JSON 顶层是数组，ConvertFrom-Json 已经返回数组
    # 如果是单个对象，包装为数组
    if ($data -isnot [System.Array]) {
        $data = @($data)
    }

    # 写入目标格式
    $outExt = [System.IO.Path]::GetExtension($OutputPath).ToLower()
    switch ($outExt) {
        ".json" {
            $data | ConvertTo-Json -Depth 5 | Set-Content $OutputPath -Encoding UTF8
        }
        ".csv" {
            $data | Export-Csv -Path $OutputPath -NoTypeInformation -Encoding UTF8
        }
        default {
            throw "不支持的输出格式：$outExt"
        }
    }

    Write-Host "转换完成：$InputPath -> $OutputPath" -ForegroundColor Green
}

# 示例
Convert-DataFormat -InputPath "$env:TEMP\source.csv" -OutputPath "$env:TEMP\source.json"