[CmdletBinding()]
param(
    [Parameter(Mandatory = $true)][string]$ServerInstance,
    [Parameter(Mandatory = $true)][string]$ObjectBackup,
    [Parameter(Mandatory = $true)][string]$DatabaseSuffix,
    [Parameter(Mandatory = $true)][string]$RequestFile,
    [Parameter(Mandatory = $true)][string]$OutputFile
)

$ErrorActionPreference = 'Stop'
$safeSuffix = ($DatabaseSuffix -replace '[^A-Za-z0-9_]', '_')
if ($safeSuffix.Length -gt 32) { $safeSuffix = $safeSuffix.Substring(0, 32) }
$database = 'HealerMan_RW_Objects_' + $safeSuffix

function Quote-SqlLiteral([string]$Value) { return "N'" + $Value.Replace("'", "''") + "'" }
function Quote-SqlIdentifier([string]$Value) { return '[' + $Value.Replace(']', ']]') + ']' }

function Invoke-SqlTable([System.Data.SqlClient.SqlConnection]$Connection, [string]$CommandText) {
    $command = $Connection.CreateCommand()
    $command.CommandTimeout = 300
    $command.CommandText = $CommandText
    $table = [System.Data.DataTable]::new()
    $reader = $command.ExecuteReader()
    try { $table.Load($reader) }
    finally { $reader.Dispose(); $command.Dispose() }
    return ,$table
}

function Invoke-SqlNonQuery([System.Data.SqlClient.SqlConnection]$Connection, [string]$CommandText) {
    $command = $Connection.CreateCommand()
    $command.CommandTimeout = 600
    $command.CommandText = $CommandText
    try { [void]$command.ExecuteNonQuery() }
    finally { $command.Dispose() }
}

function Convert-DataRow([System.Data.DataRow]$Row) {
    $result = [ordered]@{}
    foreach ($column in $Row.Table.Columns) {
        $value = $Row[$column.ColumnName]
        $result[$column.ColumnName] = if ($value -is [DBNull]) { $null } else { $value }
    }
    return $result
}

function Remove-ForensicDatabase([System.Data.SqlClient.SqlConnection]$Connection, [string]$DatabaseName) {
    $identifier = Quote-SqlIdentifier $DatabaseName
    Invoke-SqlNonQuery $Connection @"
IF DB_ID($(Quote-SqlLiteral $DatabaseName)) IS NOT NULL
BEGIN
    ALTER DATABASE $identifier SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE $identifier;
END
"@
}

function Restore-ForensicDatabase(
    [System.Data.SqlClient.SqlConnection]$Connection,
    [string]$DatabaseName,
    [string]$BackupFile,
    [string]$DataDirectory,
    [string]$LogDirectory
) {
    if (-not (Test-Path -LiteralPath $BackupFile -PathType Leaf)) { throw "Backup not found: $BackupFile" }
    $files = Invoke-SqlTable $Connection ('RESTORE FILELISTONLY FROM DISK = ' + (Quote-SqlLiteral $BackupFile))
    $moves = [System.Collections.Generic.List[string]]::new()
    $dataIndex = 0
    $logIndex = 0
    foreach ($file in $files.Rows) {
        if ([string]$file.Type -eq 'L') {
            $physical = Join-Path $LogDirectory ($DatabaseName + '_' + $logIndex + '.ldf')
            $logIndex += 1
        } else {
            $extension = if ($dataIndex -eq 0) { '.mdf' } else { '.ndf' }
            $physical = Join-Path $DataDirectory ($DatabaseName + '_' + $dataIndex + $extension)
            $dataIndex += 1
        }
        $moves.Add('MOVE ' + (Quote-SqlLiteral ([string]$file.LogicalName)) + ' TO ' + (Quote-SqlLiteral $physical))
    }
    Remove-ForensicDatabase $Connection $DatabaseName
    $restoreSql = @(
        'RESTORE DATABASE ' + (Quote-SqlIdentifier $DatabaseName),
        'FROM DISK = ' + (Quote-SqlLiteral $BackupFile),
        'WITH REPLACE, RECOVERY, STATS = 10,',
        ($moves -join (',' + [Environment]::NewLine))
    ) -join [Environment]::NewLine
    Invoke-SqlNonQuery $Connection $restoreSql
    Invoke-SqlNonQuery $Connection ('ALTER DATABASE ' + (Quote-SqlIdentifier $DatabaseName) + ' SET READ_ONLY WITH ROLLBACK IMMEDIATE')
}

$request = Get-Content -Raw -LiteralPath $RequestFile | ConvertFrom-Json
$imageIds = @($request.imageIds | ForEach-Object { [int]$_ } | Where-Object { $_ -gt 0 } | Sort-Object -Unique)
if (-not $imageIds.Count) { throw 'Paperdoll request contains no positive image IDs.' }
$idList = $imageIds -join ','
$connectionString = 'Server=' + $ServerInstance + ';Database=master;Integrated Security=SSPI;Encrypt=False;TrustServerCertificate=True;Connect Timeout=15;Application Name=HealerMan RuneWaker Paperdoll Forensics'
$connection = [System.Data.SqlClient.SqlConnection]::new($connectionString)
try {
    $connection.Open()
    $paths = Invoke-SqlTable $connection @"
SELECT CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS nvarchar(4000)) AS DataPath,
       CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS nvarchar(4000)) AS LogPath,
       (SELECT TOP (1) physical_name FROM sys.master_files WHERE database_id = 1 AND file_id = 1) AS MasterFile;
"@
    $dataDirectory = [string]$paths.Rows[0].DataPath
    $logDirectory = [string]$paths.Rows[0].LogPath
    if ([string]::IsNullOrWhiteSpace($dataDirectory)) { $dataDirectory = Split-Path -Parent ([string]$paths.Rows[0].MasterFile) }
    if ([string]::IsNullOrWhiteSpace($logDirectory)) { $logDirectory = $dataDirectory }
    Restore-ForensicDatabase $connection $database $ObjectBackup $dataDirectory $logDirectory
    $objectId = Quote-SqlIdentifier $database
    $table = Invoke-SqlTable $connection @"
SELECT guid,imageid,actworld,skincolor,haircolor,
 paperdollfacename,paperdollhairname,paperdollheadname,paperdollshoudername,paperdollclothesname,
 paperdollglovesname,paperdollbeltname,paperdollpantsname,paperdollshoesname,paperdollbackname,
 paperdollfacemaincolor,paperdollfaceoffcolor,paperdollhairmaincolor,paperdollhairoffcolor,
 paperdollheadmaincolor,paperdollheadoffcolor,paperdollshoudermaincolor,paperdollshouderoffcolor,
 paperdollclothesmaincolor,paperdollclothesoffcolor,paperdollglovesmaincolor,paperdollglovesoffcolor,
 paperdollbeltmaincolor,paperdollbeltoffcolor,paperdollpantsmaincolor,paperdollpantsoffcolor,
 paperdollshoesmaincolor,paperdollshoesoffcolor,paperdollbackmaincolor,paperdollbackoffcolor
FROM $objectId.dbo.ImageObjectDB
WHERE guid IN ($idList) OR imageid IN ($idList)
ORDER BY guid;
"@
    $payload = [ordered]@{
        schemaVersion = 1
        queryVersion = 1
        requestedImageIds = $imageIds
        rows = @($table.Rows | ForEach-Object { Convert-DataRow $_ })
    }
    $directory = Split-Path -Parent $OutputFile
    if ($directory) { [void](New-Item -ItemType Directory -Path $directory -Force) }
    $payload | ConvertTo-Json -Depth 8 | Set-Content -LiteralPath $OutputFile -Encoding UTF8
    Write-Host ('Exported {0} candidate ImageObjectDB rows.' -f $table.Rows.Count)
}
finally {
    if ($connection.State -eq [System.Data.ConnectionState]::Open) {
        Remove-ForensicDatabase $connection $database
        $connection.Close()
    }
    $connection.Dispose()
}