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

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

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

function Convert-ToIntegerList([string]$Value, [string]$Label) {
    $result = @($Value.Split(',') | ForEach-Object {
        $item = $_.Trim()
        if ($item -notmatch '^\d+$') { throw "Invalid $Label value: $item" }
        [int64]$item
    } | Sort-Object -Unique)
    if ($result.Count -eq 0) { throw "$Label must contain at least one integer." }
    return $result
}

function Invoke-SqlTable([System.Data.SqlClient.SqlConnection]$Connection, [string]$CommandText) {
    $command = $Connection.CreateCommand()
    $command.CommandTimeout = 600
    $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 = 900
    $command.CommandText = $CommandText
    try { [void]$command.ExecuteNonQuery() }
    finally { $command.Dispose() }
}

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
    $newline = [Environment]::NewLine
    $restoreSql = @(
        "RESTORE DATABASE " + (Quote-SqlIdentifier $DatabaseName),
        "FROM DISK = " + (Quote-SqlLiteral $BackupFile),
        "WITH REPLACE, RECOVERY, STATS = 10,",
        ($moves -join (',' + $newline))
    ) -join $newline
    Invoke-SqlNonQuery $Connection $restoreSql
    Invoke-SqlNonQuery $Connection ("ALTER DATABASE " + (Quote-SqlIdentifier $DatabaseName) + " SET READ_ONLY WITH ROLLBACK IMMEDIATE")
}

function Convert-PopulationRow([System.Data.DataRow]$Row) {
    $spells = for ($index = 1; $index -le 8; $index += 1) {
        $spellId = [int]$Row.("spell_" + $index)
        if ($spellId -gt 0) { [ordered]@{ id = $spellId; level = [int]$Row.("spell_level_" + $index) } }
    }
    return [ordered]@{
        spawnId = [int64]$Row.spawn_id
        templateId = [int]$Row.template_id
        zoneId = [int]$Row.zone_id
        roomId = [int]$Row.room_id
        sourcePosition = @([double]$Row.source_x, [double]$Row.source_y, [double]$Row.source_z)
        direction = [double]$Row.direction
        roleName = [string]$Row.role_name
        autoPlot = [string]$Row.auto_plot
        plotClassName = [string]$Row.plot_class_name
        sex = [int]$Row.sex
        nativeLevel = [int]$Row.native_level
        imageId = [int]$Row.image_id
        modelPath = [string]$Row.model_path
        localizedName = [string]$Row.localized_name
        templateScripts = [ordered]@{
            init = [string]$Row.lua_init_script
            display = [string]$Row.lua_display_script
            beginAttack = [string]$Row.lua_begin_attack
            endAttack = [string]$Row.lua_end_attack
            assistMagic = [string]$Row.lua_assist_magic
            attackMagic = [string]$Row.lua_attack_magic
            onDead = [string]$Row.lua_on_dead
            onKill = [string]$Row.lua_on_kill
        }
        spells = @($spells)
    }
}

$zones = Convert-ToIntegerList $ZoneIds "ZoneIds"
$zoneObjectTemplates = Convert-ToIntegerList $ZoneObjectTemplateIds "ZoneObjectTemplateIds"
$zoneSql = $zones -join ','
$zoneObjectSql = $zoneObjectTemplates -join ','
$connectionString = "Server=" + $ServerInstance + ";Database=master;Integrated Security=SSPI;Encrypt=False;TrustServerCertificate=True;Connect Timeout=15;Application Name=HealerMan RuneWaker Batch 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 $globalDatabase $GlobalBackup $dataDirectory $logDirectory
    Restore-ForensicDatabase $connection $objectDatabase $ObjectBackup $dataDirectory $logDirectory

    $globalId = Quote-SqlIdentifier $globalDatabase
    $objectId = Quote-SqlIdentifier $objectDatabase
    $imageColumnTable = Invoke-SqlTable $connection @"
SELECT COLUMN_NAME, DATA_TYPE, ORDINAL_POSITION
FROM $objectId.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'ImageObjectDB'
ORDER BY ORDINAL_POSITION;
"@
    $imageObjectColumns = @($imageColumnTable.Rows | ForEach-Object {
        [ordered]@{ name = [string]$_.COLUMN_NAME; type = [string]$_.DATA_TYPE; ordinal = [int]$_.ORDINAL_POSITION }
    })
    $zoneTable = Invoke-SqlTable $connection @"
SELECT guid, mapfile, mapcount, mapid, mapname1, mapname2, mapname3
FROM $objectId.dbo.ZoneObjectDB
WHERE guid IN ($zoneObjectSql)
ORDER BY guid;
"@
    $populationTable = Invoke-SqlTable $connection @"
SELECT
    d.DBID AS spawn_id, d.OrgObjID AS template_id, d.ZoneID AS zone_id, d.RoomID AS room_id,
    CONVERT(float, d.X) AS source_x, CONVERT(float, d.Y) AS source_y,
    CONVERT(float, d.Z) AS source_z, CONVERT(float, d.Dir) AS direction,
    ISNULL(d.RoleName, '') AS role_name, ISNULL(d.AutoPlot, '') AS auto_plot,
    ISNULL(d.PlotClassName, '') AS plot_class_name,
    ISNULL(n.sex, 0) AS sex, ISNULL(n.level, 0) AS native_level, ISNULL(n.imageid, 0) AS image_id,
    ISNULL(CONVERT(nvarchar(4000), image_row.actworld), '') AS model_path,
    ISNULL(CONVERT(nvarchar(4000), n.szluainitscript), '') AS lua_init_script,
    ISNULL(CONVERT(nvarchar(4000), n.szluadisplayscript), '') AS lua_display_script,
    ISNULL(CONVERT(nvarchar(4000), n.luaevent_beginattack), '') AS lua_begin_attack,
    ISNULL(CONVERT(nvarchar(4000), n.luaevent_endattack), '') AS lua_end_attack,
    ISNULL(CONVERT(nvarchar(4000), n.luaevent_onassistmagic), '') AS lua_assist_magic,
    ISNULL(CONVERT(nvarchar(4000), n.luaevent_onattackmagic), '') AS lua_attack_magic,
    ISNULL(CONVERT(nvarchar(4000), n.luaevent_ondead), '') AS lua_on_dead,
    ISNULL(CONVERT(nvarchar(4000), n.luaevent_onkill), '') AS lua_on_kill,
    ISNULL(n.spellmagic1, 0) AS spell_1, ISNULL(n.spellmagiclv1, 0) AS spell_level_1,
    ISNULL(n.spellmagic2, 0) AS spell_2, ISNULL(n.spellmagiclv2, 0) AS spell_level_2,
    ISNULL(n.spellmagic3, 0) AS spell_3, ISNULL(n.spellmagiclv3, 0) AS spell_level_3,
    ISNULL(n.spellmagic4, 0) AS spell_4, ISNULL(n.spellmagiclv4, 0) AS spell_level_4,
    ISNULL(n.spellmagic5, 0) AS spell_5, ISNULL(n.spellmagiclv5, 0) AS spell_level_5,
    ISNULL(n.spellmagic6, 0) AS spell_6, ISNULL(n.spellmagiclv6, 0) AS spell_level_6,
    ISNULL(n.spellmagic7, 0) AS spell_7, ISNULL(n.spellmagiclv7, 0) AS spell_level_7,
    ISNULL(n.spellmagic8, 0) AS spell_8, ISNULL(n.spellmagiclv8, 0) AS spell_level_8,
    ISNULL(s.Content, '') AS localized_name
FROM $globalId.dbo.NPCData d
LEFT JOIN $objectId.dbo.NPCObjectDB n ON n.guid = d.OrgObjID
OUTER APPLY (
    SELECT TOP (1) i.actworld FROM $objectId.dbo.ImageObjectDB i
    WHERE i.guid = n.imageid OR i.imageid = n.imageid
    ORDER BY CASE WHEN i.guid = n.imageid THEN 0 ELSE 1 END, i.guid
) image_row
LEFT JOIN $objectId.dbo.StringDB s ON s.KeyStr = 'Sys' + CAST(d.OrgObjID AS varchar) + '_name'
WHERE d.ZoneID IN ($zoneSql) AND d.IsDelflag = 0
ORDER BY d.ZoneID, d.DBID;
"@

    $zoneObjectsByGuid = @{}
    foreach ($row in $zoneTable.Rows) {
        $zoneObjectsByGuid[[int64]$row.guid] = [ordered]@{
            guid = [int64]$row.guid
            mapFile = [string]$row.mapfile
            mapCount = [int]$row.mapcount
            mapId = [int]$row.mapid
            mapNames = @([string]$row.mapname1, [string]$row.mapname2, [string]$row.mapname3)
        }
    }
    $populationByZone = @{}
    foreach ($row in $populationTable.Rows) {
        $zoneId = [int]$row.zone_id
        if (-not $populationByZone.ContainsKey($zoneId)) { $populationByZone[$zoneId] = [System.Collections.ArrayList]::new() }
        [void]$populationByZone[$zoneId].Add((Convert-PopulationRow $row))
    }
    $payloadZones = for ($index = 0; $index -lt $zones.Count; $index += 1) {
        $zoneId = [int]$zones[$index]
        $zoneObjectTemplateId = [int64]$zoneObjectTemplates[$index]
        $rows = if ($populationByZone.ContainsKey($zoneId)) { @($populationByZone[$zoneId]) } else { @() }
        [ordered]@{
            zoneId = $zoneId
            zoneObjectTemplateId = $zoneObjectTemplateId
            zoneObject = $zoneObjectsByGuid[$zoneObjectTemplateId]
            rowCount = $rows.Count
            rows = $rows
        }
    }
    $payload = [ordered]@{
        schemaVersion = 1
        queryVersion = 2
        imageObjectColumns = $imageObjectColumns
        zones = @($payloadZones)
    }
    $outputDirectory = Split-Path -Parent $OutputFile
    if ($outputDirectory) { [void](New-Item -ItemType Directory -Path $outputDirectory -Force) }
    $payload | ConvertTo-Json -Depth 14 | Set-Content -LiteralPath $OutputFile -Encoding UTF8
    Write-Host ("Exported {0} zone objects and {1} active population rows across {2} zones." -f $zoneTable.Rows.Count, $populationTable.Rows.Count, $zones.Count)
}
finally {
    if ($connection.State -eq [System.Data.ConnectionState]::Open) {
        Remove-ForensicDatabase $connection $objectDatabase
        Remove-ForensicDatabase $connection $globalDatabase
        $connection.Close()
    }
    $connection.Dispose()
}