<#
.SYNOPSIS
  Prove deploy-from-source on a fresh SQL instance (never restore dev DB).
.DESCRIPTION
  1. Optionally creates SovereignRepository on target instance
  2. Runs npm run migrate against versioned schema scripts only
  3. Verifies core objects + resolver + empty/minimal fleet
  4. Writes report to _readiness/deploy-from-source-<timestamp>/
.EXAMPLE
  .\scripts\deploy-from-source.ps1 -SqlServer .
  .\scripts\deploy-from-source.ps1 -SqlServer localhost\SQLEXPRESS -CreateDatabase
#>
[CmdletBinding()]
param(
    [string]$SqlServer = '.',
    [string]$Database = 'SovereignRepository',
    [switch]$CreateDatabase,
    [switch]$SkipMigrate
)

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

function Write-Step($msg) { Write-Host "  $msg" -ForegroundColor Gray }
function Add-Result($name, $status, $detail) {
    $script:results += [pscustomobject]@{ Gate = $name; Status = $status; Detail = $detail }
}

$script:results = @()
$report = @()

Write-Host '=== Deploy-from-source proof ===' -ForegroundColor Cyan
Write-Host "Target: $SqlServer / $Database"

if ($CreateDatabase) {
    Write-Step 'Creating database if missing...'
    $createSql = @"
IF DB_ID(N'$Database') IS NULL
BEGIN
  CREATE DATABASE [$Database];
END
"@
    sqlcmd -S $SqlServer -E -d master -Q $createSql -b
    if ($LASTEXITCODE -ne 0) { throw 'CREATE DATABASE failed' }
    Add-Result 'CreateDatabase' 'PASS' 'Database exists or was created'
} else {
    Add-Result 'CreateDatabase' 'SKIP' 'Use -CreateDatabase to create on fresh instance'
}

$conn = "Server=$SqlServer;Database=$Database;Trusted_Connection=Yes;"
$env:SOVEREIGN_DB_CONNECTION = $conn

if (-not $SkipMigrate) {
    Write-Step 'Running npm run migrate (versioned scripts only)...'
    Push-Location $repoRoot
    try {
        npm run migrate 2>&1 | Tee-Object -FilePath (Join-Path $outDir 'migrate.log')
        if ($LASTEXITCODE -ne 0) { throw 'migrate failed' }
        Add-Result 'Migrate' 'PASS' 'All schema scripts applied'
    } finally {
        Pop-Location
    }
}

Write-Step 'Verifying core objects...'
$verifySql = @"
SET NOCOUNT ON;
SELECT
  (SELECT COUNT(*) FROM sys.schemas WHERE name IN ('ctl','sec','perf','inv','patch')) AS CoreSchemas,
  OBJECT_ID('ctl.Servers','U') AS HasServers,
  OBJECT_ID('ctl.vw_ResolvedConnections','V') AS HasResolverView,
  OBJECT_ID('ctl.usp_ResolveCollectionTargets','P') AS HasResolverProc,
  OBJECT_ID('ctl.CredentialReference','U') AS HasCredRef,
  OBJECT_ID('deploy.SoftwareRelease','U') AS HasReleases,
  (SELECT COUNT(*) FROM ctl.Servers) AS ServerCount;
"@
$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 7) {
    $coreSchemas = [int]$parts[0].Trim()
    if ($coreSchemas -ge 5 -and $parts[1].Trim() -and $parts[2].Trim() -and $parts[3].Trim()) {
        Add-Result 'SchemaObjects' 'PASS' "Core schemas=$coreSchemas resolver+cred catalog present"
    } else {
        Add-Result 'SchemaObjects' 'FAIL' ($verifyOut -join ' ')
    }
} else {
    Add-Result 'SchemaObjects' 'WARN' 'Could not parse verification output'
}

Write-Step 'Testing collection resolver via SQL (source of truth)...'
$resolverSql = @"
SET NOCOUNT ON;
EXEC ctl.usp_ResolveCollectionTargets @ServerId = NULL, @IncludeIneligible = 0;
"@
try {
    $resolverOut = sqlcmd -S $SqlServer -E -d $Database -Q $resolverSql -s '|' -W -h -1 2>&1
    $resolverOut | Out-File (Join-Path $outDir 'verify-resolver-sql.txt')
    $rowCount = @($resolverOut | Where-Object { $_ -and $_ -notmatch '^-+$' -and $_ -notmatch 'rows affected' }).Count
    if ($LASTEXITCODE -eq 0) {
        Add-Result 'ResolverSql' 'PASS' "usp_ResolveCollectionTargets returned $rowCount row(s)"
    } else {
        Add-Result 'ResolverSql' 'FAIL' ($resolverOut -join ' ')
    }
} catch {
    Add-Result 'ResolverSql' 'FAIL' $_.Exception.Message
}

Write-Step 'Testing collection-targets resolver via API (optional)...'
try {
    $resp = Invoke-RestMethod -Uri 'http://localhost:3041/api/fleet/collection-targets' -TimeoutSec 5
    if ($resp.error) {
        if ($resp.error -match 'uniqueidentifier') {
            Add-Result 'ResolverApi' 'WARN' 'API stale — restart required: npm run restart:sovereign'
        } else {
            Add-Result 'ResolverApi' 'FAIL' $resp.error
        }
    } elseif ($null -ne $resp.targets) {
        Add-Result 'ResolverApi' 'PASS' "API returned $($resp.targets.Count) target(s)"
    } else {
        Add-Result 'ResolverApi' 'WARN' 'Unexpected API response shape'
    }
} catch {
    $msg = $_.Exception.Message
    if ($msg -match 'Unable to connect|connection refused|No connection|actively refused') {
        Add-Result 'ResolverApi' 'WARN' 'API not running — npm run start (optional; SQL resolver already verified)'
    } elseif ($msg -match '500|Internal Server Error|uniqueidentifier') {
        Add-Result 'ResolverApi' 'WARN' 'API stale — npm run restart:sovereign (SQL resolver already verified)'
    } else {
        Add-Result 'ResolverApi' 'WARN' "API check skipped: $msg"
    }
}

$hardGates = @('Migrate', 'SchemaObjects', 'ResolverSql')
$hardFail = @($script:results | Where-Object { $_.Gate -in $hardGates -and $_.Status -eq 'FAIL' })
$verdict = if ($hardFail.Count) { 'FAIL' }
  elseif (@($script:results | Where-Object { $_.Status -match '^(WARN|SKIP)$' }).Count) { 'WARN' }
  else { 'PASS' }

$md = @"
# Deploy-from-source proof

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

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

This run used **migrate from source scripts only** — not a restore of the dev database.
"@

$md | 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 }
