user-system/scripts/ops/drill-sqlite-backup-restore.ps1

param(
  [string]$SourceDb = 'D:\project\data\user_management.db',
  [int]$ProbePort = 18080,
  [string]$EvidenceDate = (Get-Date -Format 'yyyy-MM-dd')
)

$ErrorActionPreference = 'Stop'

$projectRoot = (Resolve-Path (Join-Path $PSScriptRoot '..\..')).Path
$evidenceRoot = Join-Path $projectRoot "docs\evidence\ops\$EvidenceDate\backup-restore"
$goCacheRoot = Join-Path $projectRoot '.cache'
$goBuildCache = Join-Path $goCacheRoot 'go-build'
$goModCache = Join-Path $goCacheRoot 'gomod'
$goPath = Join-Path $goCacheRoot 'gopath'
$timestamp = Get-Date -Format 'yyyyMMdd-HHmmss'
$drillRoot = Join-Path $evidenceRoot $timestamp
$backupDb = Join-Path $drillRoot 'user_management.backup.db'
$restoredDb = Join-Path $drillRoot 'user_management.restored.db'
$sourceSnapshot = Join-Path $drillRoot 'source-snapshot.json'
$restoredSnapshot = Join-Path $drillRoot 'restored-snapshot.json'
$tempConfig = Join-Path $drillRoot 'config.restore.yaml'
$serverExe = Join-Path $drillRoot 'server-restore.exe'
$serverStdOut = Join-Path $drillRoot 'server.stdout.log'
$serverStdErr = Join-Path $drillRoot 'server.stderr.log'
$reportPath = Join-Path $drillRoot 'BACKUP_RESTORE_DRILL.md'

New-Item -ItemType Directory -Force $evidenceRoot, $drillRoot, $goBuildCache, $goModCache, $goPath | Out-Null

function Test-UrlReady {
  param(
    [Parameter(Mandatory = $true)][string]$Url
  )

  try {
    $response = Invoke-WebRequest $Url -UseBasicParsing -TimeoutSec 2
    return $response.StatusCode -ge 200 -and $response.StatusCode -lt 500
  } catch {
    return $false
  }
}

function Wait-UrlReady {
  param(
    [Parameter(Mandatory = $true)][string]$Url,
    [Parameter(Mandatory = $true)][string]$Label,
    [int]$RetryCount = 120,
    [int]$DelayMs = 500
  )

  for ($i = 0; $i -lt $RetryCount; $i++) {
    if (Test-UrlReady -Url $Url) {
      return
    }
    Start-Sleep -Milliseconds $DelayMs
  }

  throw "$Label did not become ready: $Url"
}

function Invoke-GoTool {
  param(
    [Parameter(Mandatory = $true)][string[]]$Arguments,
    [Parameter(Mandatory = $true)][string]$OutputPath
  )

  Push-Location $projectRoot
  try {
    $env:GOCACHE = $goBuildCache
    $env:GOMODCACHE = $goModCache
    $env:GOPATH = $goPath
    $output = & go @Arguments 2>&1 | Out-String
    if ($LASTEXITCODE -ne 0) {
      throw $output
    }
    Set-Content -Path $OutputPath -Value $output -Encoding UTF8
  } finally {
    Pop-Location
    Remove-Item Env:GOCACHE, Env:GOMODCACHE, Env:GOPATH -ErrorAction SilentlyContinue
  }
}

function Build-RestoreConfig {
  param(
    [Parameter(Mandatory = $true)][string]$TemplatePath,
    [Parameter(Mandatory = $true)][string]$OutputPath,
    [Parameter(Mandatory = $true)][string]$RestoredDbPath,
    [Parameter(Mandatory = $true)][int]$Port
  )

  $content = Get-Content $TemplatePath -Raw
  $dbPath = ($RestoredDbPath -replace '\\', '/')
  $content = $content -replace '(?m)^  port: \d+$', "  port: $Port"
  $content = [regex]::Replace(
    $content,
    '(?ms)(sqlite:\s*\r?\n\s*path:\s*).+?(\r?\n)',
    "`$1`"$dbPath`"`$2"
  )
  Set-Content -Path $OutputPath -Value $content -Encoding UTF8
}

if (-not (Test-Path $SourceDb)) {
  throw "source db not found: $SourceDb"
}

Invoke-GoTool -Arguments @('run', '.\tools\sqlite_snapshot_check.go', '-db', $SourceDb, '-json') -OutputPath $sourceSnapshot

Copy-Item $SourceDb $backupDb -Force
Copy-Item $backupDb $restoredDb -Force

$sourceHash = (Get-FileHash $SourceDb -Algorithm SHA256).Hash
$backupHash = (Get-FileHash $backupDb -Algorithm SHA256).Hash
$restoredHash = (Get-FileHash $restoredDb -Algorithm SHA256).Hash

Invoke-GoTool -Arguments @('run', '.\tools\sqlite_snapshot_check.go', '-db', $restoredDb, '-json') -OutputPath $restoredSnapshot

$sourceSnapshotObject = Get-Content $sourceSnapshot -Raw | ConvertFrom-Json
$restoredSnapshotObject = Get-Content $restoredSnapshot -Raw | ConvertFrom-Json

if ($sourceHash -ne $backupHash -or $backupHash -ne $restoredHash) {
  throw 'backup/restore hash mismatch'
}

$sourceTablesJson = ($sourceSnapshotObject.Tables | ConvertTo-Json -Compress)
$restoredTablesJson = ($restoredSnapshotObject.Tables | ConvertTo-Json -Compress)
$sourceExistingTables = @($sourceSnapshotObject.existing_tables)
$restoredExistingTables = @($restoredSnapshotObject.existing_tables)
$sourceMissingTables = @($sourceSnapshotObject.missing_tables)
$restoredMissingTables = @($restoredSnapshotObject.missing_tables)
if ($sourceTablesJson -ne $restoredTablesJson) {
  throw "restored table counts mismatch: source=$sourceTablesJson restored=$restoredTablesJson"
}
if (($sourceExistingTables -join ',') -ne ($restoredExistingTables -join ',')) {
  throw "restored existing table set mismatch: source=$($sourceExistingTables -join ',') restored=$($restoredExistingTables -join ',')"
}
if (($sourceMissingTables -join ',') -ne ($restoredMissingTables -join ',')) {
  throw "restored missing table set mismatch: source=$($sourceMissingTables -join ',') restored=$($restoredMissingTables -join ',')"
}

Build-RestoreConfig `
  -TemplatePath (Join-Path $projectRoot 'configs\config.yaml') `
  -OutputPath $tempConfig `
  -RestoredDbPath $restoredDb `
  -Port $ProbePort

Push-Location $projectRoot
try {
  $env:GOCACHE = $goBuildCache
  $env:GOMODCACHE = $goModCache
  $env:GOPATH = $goPath
  & go build -o $serverExe .\cmd\server
  if ($LASTEXITCODE -ne 0) {
    throw 'build restore server failed'
  }
} finally {
  Pop-Location
  Remove-Item Env:GOCACHE, Env:GOMODCACHE, Env:GOPATH -ErrorAction SilentlyContinue
}

$previousConfigPath = $env:UMS_CONFIG_PATH
$env:UMS_CONFIG_PATH = $tempConfig
$serverProcess = $null

try {
  Remove-Item $serverStdOut, $serverStdErr -Force -ErrorAction SilentlyContinue
  $serverProcess = Start-Process `
    -FilePath $serverExe `
    -WorkingDirectory $projectRoot `
    -PassThru `
    -WindowStyle Hidden `
    -RedirectStandardOutput $serverStdOut `
    -RedirectStandardError $serverStdErr

  Wait-UrlReady -Url "http://127.0.0.1:$ProbePort/health" -Label 'restore health endpoint'
  Wait-UrlReady -Url "http://127.0.0.1:$ProbePort/health/ready" -Label 'restore readiness endpoint'
  $capabilitiesResponse = Invoke-RestMethod "http://127.0.0.1:$ProbePort/api/v1/auth/capabilities" -TimeoutSec 5
} finally {
  if ($serverProcess -and -not $serverProcess.HasExited) {
    Stop-Process -Id $serverProcess.Id -Force -ErrorAction SilentlyContinue
  }
  if ([string]::IsNullOrWhiteSpace($previousConfigPath)) {
    Remove-Item Env:UMS_CONFIG_PATH -ErrorAction SilentlyContinue
  } else {
    $env:UMS_CONFIG_PATH = $previousConfigPath
  }
}

$sourceMissingSummary = if ($sourceMissingTables.Count -gt 0) { $sourceMissingTables -join ', ' } else { 'none' }
$restoredMissingSummary = if ($restoredMissingTables.Count -gt 0) { $restoredMissingTables -join ', ' } else { 'none' }
$sampleUsersSummary = @($sourceSnapshotObject.sample_users) -join ', '
$capabilitiesJson = ($capabilitiesResponse.data | ConvertTo-Json -Compress)
$sourceSnapshotName = Split-Path $sourceSnapshot -Leaf
$restoredSnapshotName = Split-Path $restoredSnapshot -Leaf
$serverStdOutName = Split-Path $serverStdOut -Leaf
$serverStdErrName = Split-Path $serverStdErr -Leaf
$tempConfigName = Split-Path $tempConfig -Leaf

$reportLines = @(
  '# Backup Restore Drill',
  '',
  "- Generated at: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss zzz')",
  "- Source DB: $SourceDb",
  "- Backup DB: $backupDb",
  "- Restored DB: $restoredDb",
  "- Probe port: $ProbePort",
  '',
  '## Hash Validation',
  '',
  "- source sha256: $sourceHash",
  "- backup sha256: $backupHash",
  "- restored sha256: $restoredHash",
  '',
  '## Snapshot Comparison',
  '',
  "- source tables: $sourceTablesJson",
  "- restored tables: $restoredTablesJson",
  "- source existing tables: $($sourceExistingTables -join ', ')",
  "- restored existing tables: $($restoredExistingTables -join ', ')",
  "- source missing tables: $sourceMissingSummary",
  "- restored missing tables: $restoredMissingSummary",
  "- sample users: $sampleUsersSummary",
  '',
  '## Restore Service Verification',
  '',
  '- GET /health: pass',
  '- GET /health/ready: pass',
  '- GET /api/v1/auth/capabilities: pass',
  "- auth capabilities payload: $capabilitiesJson",
  '',
  '## Evidence Files',
  '',
  "- $sourceSnapshotName",
  "- $restoredSnapshotName",
  "- $serverStdOutName",
  "- $serverStdErrName",
  "- $tempConfigName",
  ''
)

Set-Content -Path $reportPath -Value ($reportLines -join [Environment]::NewLine) -Encoding UTF8
Get-Content $reportPath