<#
.SYNOPSIS
  Cold-build proof: empty throwaway DB, migrate v1→v28 from scratch, verify, tear down.
.EXAMPLE
  .\scripts\deploy-cold.ps1 -SqlServer .
#>
[CmdletBinding()]
param(
    [string]$SqlServer = '.',
    [string]$Database = 'SovereignRepository_ColdProof',
    [switch]$KeepDatabase
)

$ErrorActionPreference = 'Stop'
$repoRoot = Split-Path $PSScriptRoot -Parent
$stamp = Get-Date -Format 'yyyyMMdd-HHmmss'
$outDir = Join-Path $repoRoot "_readiness\deploy-cold-$stamp"
New-Item -ItemType Directory -Path $outDir -Force | Out-Null

function Add-Result($name, $status, $detail) {
    $script:results += [pscustomobject]@{ Gate = $name; Status = $status; Detail = $detail }
}
$script:results = @()

Write-Host '=== Cold deploy proof (empty DB → v1→v28) ===' -ForegroundColor Cyan
Write-Host "Target: $SqlServer / $Database"

Write-Host '  Dropping prior cold-proof database if present...' -ForegroundColor Gray
sqlcmd -S $SqlServer -E -d master -Q @"
IF DB_ID(N'$Database') IS NOT NULL
BEGIN
  ALTER DATABASE [$Database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE [$Database];
END
"@ -b | Out-Null

Write-Host '  Running migrate from empty instance (expect v1 applied, not skipped)...' -ForegroundColor Gray
$env:SOVEREIGN_DB_CONNECTION = "Server=$SqlServer;Database=master;Trusted_Connection=Yes;"
Push-Location $repoRoot
try {
    npm run migrate -- --database $Database 2>&1 | Tee-Object -FilePath (Join-Path $outDir 'migrate.log')
    if ($LASTEXITCODE -ne 0) { throw 'migrate failed' }
} finally {
    Pop-Location
}

$migrateLog = Get-Content (Join-Path $outDir 'migrate.log') -Raw
if ($migrateLog -match 'base schema \(v1\) applied') {
    Add-Result 'ColdV1Applied' 'PASS' "v1 base schema applied to fresh $Database"
} elseif ($migrateLog -match 'already applied') {
    Add-Result 'ColdV1Applied' 'FAIL' 'v1 was skipped — not a cold build'
} else {
    Add-Result 'ColdV1Applied' 'FAIL' 'Could not confirm v1 application in migrate log'
}

$verifySql = @"
SET NOCOUNT ON;
SELECT
  OBJECT_ID('ctl.Servers','U') AS HasServers,
  OBJECT_ID('ctl.vw_ResolvedConnections','V') AS HasResolver,
  OBJECT_ID('ctl.CredentialReference','U') AS HasCredRef,
  OBJECT_ID('deploy.SoftwareRelease','U') AS HasReleases;
"@
$verifyOut = sqlcmd -S $SqlServer -E -d $Database -Q $verifySql -s '|' -W -h -1
$verifyOut | Out-File (Join-Path $outDir 'verify-objects.txt')
$parts = ($verifyOut | Where-Object { $_ -match '\|' } | Select-Object -First 1) -split '\|'
if ($parts.Count -ge 4 -and $parts[0].Trim() -and $parts[1].Trim() -and $parts[2].Trim() -and $parts[3].Trim()) {
    Add-Result 'ColdSchema' 'PASS' 'ctl.Servers + resolver + cred catalog + releases present'
} else {
    Add-Result 'ColdSchema' 'FAIL' ($verifyOut -join ' ')
}

$resolverOut = sqlcmd -S $SqlServer -E -d $Database -Q 'EXEC ctl.usp_ResolveCollectionTargets @ServerId=NULL,@IncludeIneligible=0' -W -h -1 2>&1
$resolverOut | Out-File (Join-Path $outDir 'verify-resolver.txt')
if ($LASTEXITCODE -eq 0) {
    Add-Result 'ColdResolver' 'PASS' 'usp_ResolveCollectionTargets OK on cold DB'
} else {
    Add-Result 'ColdResolver' 'FAIL' ($resolverOut -join ' ')
}

if (-not $KeepDatabase) {
    Write-Host '  Tearing down cold-proof database...' -ForegroundColor Gray
    sqlcmd -S $SqlServer -E -d master -Q @"
IF DB_ID(N'$Database') IS NOT NULL
BEGIN
  ALTER DATABASE [$Database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE [$Database];
END
"@ -b | Out-Null
    Add-Result 'Teardown' 'PASS' "Dropped $Database"
} else {
    Add-Result 'Teardown' 'SKIP' "-KeepDatabase set"
}

$hardGates = @('ColdV1Applied', 'ColdSchema', 'ColdResolver')
$hardFail = @($script:results | Where-Object { $_.Gate -in $hardGates -and $_.Status -eq 'FAIL' })
$teardownFail = @($script:results | Where-Object { $_.Gate -eq 'Teardown' -and $_.Status -eq 'FAIL' })
$verdict = if ($hardFail.Count -or $teardownFail.Count) { 'FAIL' } elseif ($script:results.Status -contains 'SKIP') { 'WARN' } else { 'PASS' }

@"
# Cold deploy proof

**Date:** $(Get-Date -Format o)
**Target:** $SqlServer / $Database
**Verdict:** $verdict

| Gate | Status | Detail |
|------|--------|--------|
$(($script:results | ForEach-Object { "| $($_.Gate) | $($_.Status) | $($_.Detail) |" }) -join "`n")

Proves **greenfield install**: empty database → v1 base schema applied → v2–v28 → resolver → teardown.
"@ | Out-File (Join-Path $outDir 'README.md') -Encoding utf8

Write-Host ''
Write-Host "Verdict: $verdict" -ForegroundColor $(if ($verdict -eq 'PASS') { 'Green' } elseif ($verdict -eq 'WARN') { 'Yellow' } else { 'Red' })
Write-Host "Report: $outDir\README.md"
if ($verdict -eq 'FAIL') { exit 1 }
