<#
.SYNOPSIS
  Prove uninstall-sovereign.ps1 fully removes a throwaway install footprint.
.DESCRIPTION
  Cold-builds a throwaway DB, creates a test collector login, runs uninstall with
  -DropDatabase -DropCollectorLogin, asserts both are gone.
#>
[CmdletBinding()]
param(
    [string]$Root = 'K:\RHOYLLE BUSINESS EMPIRE LLC\Business Plans\PRODUCTS\DBAOps-Sovereign',
    [string]$SqlServer = '.',
    [string]$CollectorLogin = 'dbaops_collector_proof'
)

$ErrorActionPreference = 'Stop'
if (-not (Get-Command Invoke-Sqlcmd -ErrorAction SilentlyContinue)) {
    throw 'SqlServer module missing. Install-Module SqlServer -Scope CurrentUser'
}
if (-not (Get-Command node -ErrorAction SilentlyContinue)) { throw 'node not found on PATH' }

$apiDir = Join-Path $Root 'src\api'
$migRel = 'scripts/migrate.js'
$uninstall = Join-Path $Root 'scripts\uninstall-sovereign.ps1'
if (-not (Test-Path $uninstall)) { throw "Missing $uninstall" }

function Q([string]$q, [string]$db = 'master') {
    Invoke-Sqlcmd -ServerInstance $SqlServer -Database $db -Query $q -TrustServerCertificate -QueryTimeout 120 -ErrorAction Stop
}

$stamp = Get-Date -Format 'yyyyMMdd_HHmmss'
$proofDb = "SovereignUninstallProof_$stamp"
$connMaster = "Server=$SqlServer;Database=master;Integrated Security=SSPI;TrustServerCertificate=True;"
$connBak = $env:SOVEREIGN_DB_CONNECTION
$passed = $false

Write-Host '=============================================================' -ForegroundColor Cyan
Write-Host 'Uninstall Teardown Proof' -ForegroundColor Cyan
Write-Host "  throwaway DB : $proofDb"
Write-Host "  test login   : $CollectorLogin"
Write-Host '=============================================================' -ForegroundColor Cyan

try {
    Q "CREATE DATABASE [$proofDb];" 'master' | Out-Null
    $env:SOVEREIGN_DB_CONNECTION = $connMaster
    Push-Location $apiDir
    $null = & node $migRel --database $proofDb 2>&1 | Out-String
    Pop-Location

    Q @"
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'$CollectorLogin')
  CREATE LOGIN [$CollectorLogin] WITH PASSWORD = N'ShipProofOnly!', CHECK_POLICY = OFF;
"@ 'master' | Out-Null

    $dbBefore = (Q "SELECT DB_ID(N'$proofDb') AS id;" 'master').id
    $loginBefore = (Q "SELECT COUNT(*) c FROM sys.server_principals WHERE name = N'$CollectorLogin';" 'master').c
    if (-not $dbBefore -or $loginBefore -lt 1) { throw 'Precondition failed: DB or login not created' }
    Write-Host '  Footprint before uninstall: DB present, login present' -ForegroundColor Gray

    & $uninstall -SqlServer $SqlServer -Database $proofDb -DropDatabase -DropCollectorLogin -CollectorLogin $CollectorLogin

    $dbAfter = (Q "SELECT CASE WHEN DB_ID(N'$proofDb') IS NULL THEN 0 ELSE 1 END AS present;" 'master').present
    $loginAfter = (Q "SELECT COUNT(*) c FROM sys.server_principals WHERE name = N'$CollectorLogin';" 'master').c
    if ($dbAfter -ne 0 -or $loginAfter -gt 0) {
        throw "Teardown incomplete: dbPresent=$dbAfter loginCount=$loginAfter"
    }

    $passed = $true
    Write-Host ''
    Write-Host 'PROVEN - uninstall removed throwaway DB and collector login.' -ForegroundColor Green
}
catch {
    Write-Host "FAIL: $($_.Exception.Message)" -ForegroundColor Red
}
finally {
    $env:SOVEREIGN_DB_CONNECTION = $connBak
    try {
        Q "IF DB_ID('$proofDb') IS NOT NULL BEGIN ALTER DATABASE [$proofDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE [$proofDb]; END" 'master' | Out-Null
    }
    catch { }
    try {
        Q "IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'$CollectorLogin') DROP LOGIN [$CollectorLogin];" 'master' | Out-Null
    }
    catch { }
    Write-Host '  Cleanup complete.' -ForegroundColor Green
}

if (-not $passed) { exit 1 }
