$ErrorActionPreference = "Stop"

$workbookPath = (Get-ChildItem -LiteralPath "." -Filter "*.xlsx" | Where-Object { $_.Name -notlike "*.backup-*" } | Select-Object -First 1).FullName
if ([string]::IsNullOrWhiteSpace($workbookPath)) {
    throw "No xlsx workbook found in current directory."
}
$payloadPath = (Resolve-Path ".\scripts\member_flow_cases.json").Path
$payload = Get-Content -LiteralPath $payloadPath -Raw -Encoding UTF8 | ConvertFrom-Json

$timestamp = Get-Date -Format "yyyyMMdd-HHmmss"
$backupPath = Join-Path (Split-Path $workbookPath) ("e2e-cases.backup-$timestamp.xlsx")
Copy-Item -LiteralPath $workbookPath -Destination $backupPath

$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $false
$wb = $null

function Set-RowValues {
    param(
        [object]$Worksheet,
        [int]$Row,
        [object[]]$Values
    )
    for ($i = 0; $i -lt $Values.Count; $i++) {
        $Worksheet.Cells.Item($Row, $i + 1).Value2 = [string]$Values[$i]
    }
    $Worksheet.Rows.Item($Row).WrapText = $true
    $Worksheet.Rows.Item($Row).VerticalAlignment = -4160
}

function Append-Rows {
    param(
        [object]$Workbook,
        [string]$SheetName,
        [object[]]$Rows
    )
    $ws = $Workbook.Worksheets.Item($SheetName)
    foreach ($values in $Rows) {
        $row = $ws.UsedRange.Rows.Count + 1
        [void]$ws.Rows.Item($row - 1).Copy()
        [void]$ws.Rows.Item($row).PasteSpecial(-4122)
        Set-RowValues -Worksheet $ws -Row $row -Values $values
    }
}

try {
    $wb = $excel.Workbooks.Open($workbookPath)

    foreach ($sheetName in $payload.caseSheets) {
        Append-Rows -Workbook $wb -SheetName ([string]$sheetName) -Rows $payload.caseRows
    }

    foreach ($sheet in $payload.extraSheets.PSObject.Properties) {
        Append-Rows -Workbook $wb -SheetName ([string]$sheet.Name) -Rows $sheet.Value
    }

    $wb.Save()
    $wb.Close($true)
    Write-Output "UPDATED|$workbookPath"
    Write-Output "BACKUP|$backupPath"
}
finally {
    if ($wb -ne $null) {
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($wb) | Out-Null
    }
    $excel.Quit()
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
}
