<#
.SYNOPSIS
    DBAOps Sovereign — Ship-Gap Finder (what's still needed for 100%)
.DESCRIPTION
    READ-ONLY by default. Evaluates the 9 enterprise-readiness items and emits an
    ordered PUNCH LIST of only what still needs building/doing, each with a
    concrete fix and a category (PACKAGING / BUILD / DOC / SECURITY / PROOF /
    ATTEST / DECLARE). Prints a completion percentage. Passing items are
    summarized, not detailed — the focus is the remaining gap.

    Corrections vs. the first verifier (removes known false positives):
      - Item 3 hardcode scan now excludes compiled build output (**/build/**,
        minified bundles) and the scanner/portability files themselves, so a
        string INSIDE a portability checker no longer counts as contamination.
      - Item 6 license enforcement is detected in the NODE layer
        (src/api/{lib,middleware}) where it actually lives, not only in
        sys.sql_modules.
      - Item 9 EULA check ignores .js/.jsx (a dashboard page named *SLA* is not a
        customer licence).
      - Item 2 cold-build harness self-diagnoses the "0 objects" failure and can
        drive migrate.js via a scoped, backup-restored .env (-WriteScopedEnv)
        for apps that read config through dotenv rather than the process env.

    The only writes are gated behind -RunColdBuildProof (creates+drops a
    throwaway DB) and, additionally, -WriteScopedEnv (temporarily writes a
    backed-up .env into the api dir and restores it in a finally block).
.NOTES
    Requires SqlServer module for the DB-side and cold-build checks.
#>
[CmdletBinding()]
param(
    [string]$Root      = 'K:\RHOYLLE BUSINESS EMPIRE LLC\Business Plans\PRODUCTS\DBAOps-Sovereign',
    [string]$SqlServer = '.',
    [string]$Database  = 'SovereignRepository',
    [string]$ReportDir = $null,
    [switch]$RunColdBuildProof,
    [switch]$WriteScopedEnv,
    [string[]]$BuildTrees = @('releases','pilot-before-website','delivery','dist',
                              'node_modules','.git','.github','_backups',
                              '_ship_readiness','_readiness','_discovery',
                              'coverage','.nyc_output','bin','obj','.vs')
)

$ErrorActionPreference = 'Stop'
if (-not (Test-Path -LiteralPath $Root)) { throw "Root not found: $Root" }
if (-not $ReportDir) { $ReportDir = Join-Path $Root '_ship_readiness' }
if (-not (Test-Path -LiteralPath $ReportDir)) { New-Item -Path $ReportDir -ItemType Directory | Out-Null }

$stamp      = Get-Date -Format 'yyyyMMdd_HHmmss'
$reportPath = Join-Path $ReportDir "ShipGaps_$stamp.txt"
$attestPath = Join-Path $ReportDir 'SHIP-ATTESTATION.md'
$log        = New-Object System.Collections.Generic.List[string]
$gaps       = New-Object System.Collections.Generic.List[object]   # remaining work
$passes     = New-Object System.Collections.Generic.List[string]   # what's done

function W([string]$s, [string]$c = 'Gray') { Write-Host $s -ForegroundColor $c; $log.Add($s) }
function Gap($Item, $Category, $What, $Fix) {
    $gaps.Add([pscustomobject]@{ Item=$Item; Category=$Category; What=$What; Fix=$Fix })
}
function Pass($s) { $passes.Add($s) }
function Have-Sql { [bool](Get-Command Invoke-Sqlcmd -ErrorAction SilentlyContinue) }
function Q([string]$query, [string]$db = $Database) {
    Invoke-Sqlcmd -ServerInstance $SqlServer -Database $db -Query $query -TrustServerCertificate -QueryTimeout 120 -ErrorAction Stop
}

# ---- source set (build trees excluded) ------------------------------------
$escaped   = $BuildTrees | ForEach-Object { [regex]::Escape($_) }
$excludeRx = '(^|\\)(' + ($escaped -join '|') + ')(\\|$)'
# additionally treat any '\build\' segment and minified bundles as build output
$buildArtifactRx = '(\\build\\|\\web\\build\\|\.min\.js$|\\static\\js\\main\.[0-9a-f]+\.js$)'
# the scanner/portability files themselves are not product contamination
$selfFileRx = '(?i)(verify-portability|Verify-ShipReady|Find-ShipGaps|ShipReadiness|ShipGate)'

$allSrc = Get-ChildItem -LiteralPath $Root -Recurse -File -ErrorAction SilentlyContinue |
          Where-Object { $_.FullName.Substring($Root.Length) -notmatch $excludeRx }
$srcFiles = $allSrc | Where-Object {
    $_.FullName -notmatch $buildArtifactRx -and $_.Name -notmatch $selfFileRx
}
$textExt = '.ps1','.psm1','.psd1','.sql','.md','.txt','.json','.js','.jsx','.ts','.tsx','.mjs','.cjs','.yml','.yaml','.config','.env','.example'
$srcText = $srcFiles | Where-Object { $textExt -contains $_.Extension.ToLower() }
function InSource([string]$p) { $srcText | Select-String -Pattern $p -AllMatches -ErrorAction SilentlyContinue }
function FindFile([string]$rx, [string[]]$ext) {
    $srcFiles | Where-Object { $_.Name -match $rx -and ($null -eq $ext -or $_.Extension -in $ext) } | Select-Object -First 5
}

W "=============================================================" Cyan
W "DBAOps Sovereign — Ship-Gap Finder" Cyan
W "Root   : $Root"
W "Source : $($srcFiles.Count) files (build output + scanner files excluded)"
W "Cold-build proof: $(if($RunColdBuildProof){'ENABLED'}else{'disabled (-RunColdBuildProof)'})"
W "Time   : $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')"
W "=============================================================" Cyan

# ============================================================================
# ITEM 1 - Packaging and release integrity
# ============================================================================
$gi = Join-Path $Root '.gitignore'
$needIgnored = @('releases','pilot-before-website','delivery','node_modules','.env','__tests__','_backups','build')
if (Test-Path $gi) {
    $giTxt = Get-Content $gi -Raw
    $miss = $needIgnored | Where-Object { $giTxt -notmatch [regex]::Escape($_) }
    if ($miss) { Gap 1 'PACKAGING' ".gitignore does not exclude: $($miss -join ', ')" "Add those lines to .gitignore so build output and env files never enter the repo/package." }
    else { Pass 'Item 1: .gitignore excludes build/output/env' }
} else { Gap 1 'PACKAGING' 'No .gitignore at root' 'Create .gitignore excluding build output, node_modules, .env, __tests__.' }

# stale bundle versions on disk
$pkgJson = Join-Path $Root 'package.json'
$pj = if (Test-Path $pkgJson) { try { Get-Content $pkgJson -Raw | ConvertFrom-Json } catch { $null } } else { $null }
$verFile = Join-Path $Root 'VERSION'
$verText = if (Test-Path $verFile) { (Get-Content $verFile -Raw).Trim() } else { $null }
$bundleVers = @()
foreach ($dir in @($Root, (Join-Path $Root 'pilot-before-website'), (Join-Path $Root 'releases'))) {
    Get-ChildItem -LiteralPath $dir -Directory -ErrorAction SilentlyContinue |
        ForEach-Object { if ($_.Name -match '(\d+\.\d+\.\d+)') { $bundleVers += $matches[1] } }
}
$bundleVers = $bundleVers | Select-Object -Unique
$targetVer = if ($pj) { $pj.version } elseif ($verText) { $verText } else { $null }
$staleVers = $bundleVers | Where-Object { $_ -ne $targetVer }
if ($staleVers) { Gap 1 'PACKAGING' "Stale bundle version(s) on disk: $($staleVers -join ', ') (target is $targetVer)" "Delete the stale bundle dir(s); keep only $targetVer. Reconcile VERSION + package.json + bundle name." }
else { Pass "Item 1: single version ($targetVer), no stale bundles" }

# ============================================================================
# ITEM 2 - Deploy-from-source proof
# ============================================================================
$migrate = $srcFiles | Where-Object { $_.Name -eq 'migrate.js' } | Select-Object -First 1
if (-not $migrate) {
    Gap 2 'PROOF' 'migrate.js not found in source' 'Ensure the migration runner ships in source.'
} elseif (-not $RunColdBuildProof) {
    Gap 2 'PROOF' 'Deploy-from-source cold build not yet proven' 'Re-run with -RunColdBuildProof (add -WriteScopedEnv if migrate reads .env). Incremental migrate against the dev DB is NOT proof.'
} else {
    if (-not (Have-Sql)) { Gap 2 'PROOF' 'SqlServer module unavailable for cold build' 'Install-Module SqlServer -Scope CurrentUser, then rerun.' }
    else {
        $proofDb = "SovereignShipProof_$stamp"
        $migrateJs = Join-Path $Root 'src\api\scripts\migrate.js'
        $connBak = $env:SOVEREIGN_DB_CONNECTION
        W "  Cold build -> creating [$proofDb]..." Yellow
        try {
            Q "CREATE DATABASE [$proofDb];" 'master' | Out-Null
            $env:SOVEREIGN_DB_CONNECTION = "Server=$SqlServer;Database=master;Trusted_Connection=Yes;TrustServerCertificate=True;"
            W "  Running migrate.js --database $proofDb from repo root..." Yellow
            Push-Location $Root
            $migOut = & node $migrateJs --database $proofDb 2>&1 | Out-String
            $migExit = $LASTEXITCODE
            Pop-Location
            $env:SOVEREIGN_DB_CONNECTION = $connBak
            if ($migExit -ne 0) { throw "migrate.js exit $migExit : $migOut" }
            $objCount = (Q "SELECT COUNT(*) c FROM sys.objects WHERE is_ms_shipped=0;" $proofDb).c
            $refCount = (Q "SELECT COUNT(*) c FROM sys.objects WHERE is_ms_shipped=0;" $Database).c
            $pct = if ($refCount -gt 0) { [math]::Round(100.0 * $objCount / $refCount, 1) } else { 0 }
            if ($objCount -gt 0 -and $pct -ge 95) {
                Pass ('Item 2: cold build from empty -> {0} objects ({1} pct of dev repo)' -f $objCount, $pct)
            } elseif ($objCount -eq 0) {
                Gap 2 'PROOF' 'Cold build produced 0 objects' 'migrate.js exited 0 but created no schema; inspect stdout.'
            } else {
                Gap 2 'PROOF' ('Cold build incomplete: {0} objects' -f $objCount) 'Schema does not fully build from source.'
            }
        } catch {
            Gap 2 'PROOF' ('Cold build errored: {0}' -f $_.Exception.Message) 'Run from repo root with node migrate.js --database throwaway (see Verify-ShipReady.ps1).'
        } 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; W "  Throwaway DB dropped." Green }
            catch { W "  WARNING: drop [$proofDb] manually." Red }
        }
    }
}

# ============================================================================
# ITEM 3 - Zero hardcoded environment (build output + self-files excluded)
# ============================================================================
$jb = InSource '(?i)\bJBGWALLY\b'
if (@($jb).Count -eq 0) { Pass 'Item 3: no JBGWALLY in shippable source' }
else {
    $files = ($jb | Select-Object -Expand Path -Unique | ForEach-Object { $_.Replace($Root,'.') }) -join '; '
    Gap 3 'BUILD' "JBGWALLY still in shippable source ($(@($jb).Count) hit(s)): $files" 'Neutralize to localhost/. or a required parameter; replace canvas demo data with generic placeholders.'
}
$az = InSource '(?i)(AZDBAOPS|AZAPPPROD|AZODSSQL|AZTRACS|AZDWVAULT|AZDPS|INTERNAL\\|AGDOMC|10\.98\.|ZPSTW|E:\\DBAOps|DBAOpsRepository)'
if (@($az).Count -eq 0) { Pass 'Item 3: no AZDPS identifiers in source' }
else { Gap 3 'BUILD' "AZDPS identifier(s) in source ($(@($az).Count))" 'Remove immediately - IP-separation blocker.' }

# ============================================================================
# ITEM 4 - Credential and connection security
# ============================================================================
$sa = InSource '(?i)(-UserName\s+["'']sa["'']|UserName\s*=\s*["'']sa["''])'
if (@($sa).Count -eq 0) { Pass 'Item 4: no sa default' } else { Gap 4 'SECURITY' "$(@($sa).Count) sa default(s) in source" 'Default to integrated auth; SQL auth explicit opt-in.' }
if (FindFile '(?i)Get-.*Credential.*\.ps1$' $null) { Pass 'Item 4: credential resolution helper present' }
else { Gap 4 'SECURITY' 'No credential-resolution helper' 'Add Get-*Credential*.ps1 that resolves from Cred Manager/DPAPI/Key Vault.' }
$enc = InSource '(?i)Encrypt\s*=\s*(true|yes)'
if (@($enc).Count -gt 0) { Pass 'Item 4: Encrypt=True posture present' }
else { Gap 4 'SECURITY' 'No Encrypt=True default posture' 'Default connections to Encrypt=True for enterprise; TrustServerCertificate only as a documented dev fallback.' }

# ============================================================================
# ITEM 5 - Least-privilege install / uninstall
# ============================================================================
if (FindFile '(?i)^(uninstall|remove|teardown)\.(ps1|sh|cmd|js|sql)$' $null) { Pass 'Item 5: uninstall script present' }
else { Gap 5 'BUILD' 'No uninstall SCRIPT (a UNINSTALL.md guide is not a script)' 'Write an uninstall.ps1/.sql that drops the repo DB, the login/role, agent jobs, and reverses installer changes.' }
if (($srcFiles | Where-Object { $_.Name -match '(?i)(permission|least.?priv|security)' -and $_.Extension -in '.md','.txt' })) { Pass 'Item 5: permissions doc present' }
else { Gap 5 'DOC' 'No least-privilege/permissions doc' 'Document the minimum grants required per target and for the repository.' }
$sysadmin = InSource '(?i)(ALTER SERVER ROLE\s*\[?sysadmin|sp_addsrvrolemember.*sysadmin)'
if (@($sysadmin).Count -eq 0) { Pass 'Item 5: installer avoids sysadmin grant' }
else { Gap 5 'SECURITY' 'Installer grants sysadmin' 'Replace with a scoped role + explicit grants.' }

# ============================================================================
# ITEM 6 - Licensing and edition enforcement (NODE layer)
# ============================================================================
$apiDir = Join-Path $Root 'src\api'
$enforceHit = $null
if (Test-Path $apiDir) {
    $enforceHit = Get-ChildItem -LiteralPath $apiDir -Recurse -File -Include *.js -ErrorAction SilentlyContinue |
        Where-Object { $_.FullName -match '(\\lib\\|\\middleware\\)' -and $_.FullName -notmatch '__tests__' } |
        Select-String -Pattern '(?i)(InstancesAllowed|seatLimit|licenseGate|enforce|InstancesUsed)' -List -ErrorAction SilentlyContinue
}
if ($enforceHit) { Pass "Item 6: license enforcement in Node layer ($((($enforceHit.Path | ForEach-Object { Split-Path $_ -Leaf }) | Select-Object -Unique) -join ', '))" }
else { Gap 6 'BUILD' 'No license enforcement found in src/api/{lib,middleware}' 'Enforce InstancesAllowed / edition on the request path (middleware) and handle expiry gracefully.' }
if (Have-Sql) {
    try { $ent = (Q "SELECT COUNT(*) c FROM ctl.LicenseEntitlements;").c; if ($ent -gt 0) { Pass "Item 6: entitlements populated ($ent rows)" } else { Gap 6 'BUILD' 'ctl.LicenseEntitlements empty' 'Seed entitlement rows.' } } catch {}
}

# ============================================================================
# ITEM 7 - Tenancy model declared
# ============================================================================
$tenDoc = $srcFiles | Where-Object { $_.Extension -in '.md','.txt' -and (Select-String -LiteralPath $_.FullName -Pattern '(?i)single[- ]tenant' -Quiet -ErrorAction SilentlyContinue) } | Select-Object -First 1
if ($tenDoc) { Pass "Item 7: single-tenant model declared ($($tenDoc.Name))" }
else { Gap 7 'DECLARE' 'Deployment/tenancy model not declared' 'Add a short doc stating single-tenant-per-customer (recommended for v1). Leaves multi-tenant scaffolding for later.' }

# ============================================================================
# ITEM 8 - Documentation set
# ============================================================================
$docNeeds = @{
    'Fresh-install guide' = '(?i)(install|deploy|getting.?started)'
    'Config reference'    = '(?i)(config|configuration).*(reference|guide)|CONFIG-REFERENCE'
    'Upgrade guide'       = '(?i)(upgrade|migration).?(guide)?'
    'Uninstall guide'     = '(?i)uninstall'
    'Security/permissions'= '(?i)(security|permission|least.?priv)'
    'Troubleshooting'     = '(?i)(troubleshoot|faq|known.?issues)'
    'Diagnostics bundle'  = '(?i)(diagnostic|support.?bundle|collect.?logs)'
    'Release notes'       = '(?i)(changelog|release.?notes)'
}
foreach ($k in $docNeeds.Keys) {
    $hit = $srcFiles | Where-Object { $_.Name -match $docNeeds[$k] -and $_.Extension -in '.md','.txt','.ps1','.js','.sh' } | Select-Object -First 1
    if ($hit) { Pass "Item 8: $k present" } else { Gap 8 'DOC' "Missing: $k" "Author $k." }
}

# ============================================================================
# ITEM 9 - Legal and commercial (EULA must be a document, not a .js page)
# ============================================================================
$eula = $srcFiles | Where-Object { $_.Name -match '(?i)^(EULA|CUSTOMER.?EULA|CUSTOMER.?LICENSE|LICENSE.?AGREEMENT|TERMS)' -and $_.Extension -in '.md','.txt','.pdf','.docx' } | Select-Object -First 3
if ($eula) { Pass "Item 9: customer EULA/terms document present ($(($eula|%{$_.Name}) -join ', '))" }
else { Gap 9 'DOC' 'No customer EULA/terms DOCUMENT (a dashboard .js page is not a licence)' 'Add a customer-facing EULA/SLA doc, ideally reviewed by counsel.' }

# attestations
if (Test-Path $attestPath) {
    $al = Get-Content $attestPath | Where-Object { $_ -match '^\[.\]' }
    $done = @($al | Where-Object { $_ -match '^\[x\]' }).Count; $tot = @($al).Count
    if ($tot -gt 0 -and $done -eq $tot) { Pass "Item 9: attestations signed ($done/$tot)" }
    else { Gap 9 'ATTEST' "Attestations $done/$tot signed" 'Verify each line personally, then mark [x] in SHIP-ATTESTATION.md.' }
} else { Gap 9 'ATTEST' 'No attestation file' 'Run the full verifier once to generate SHIP-ATTESTATION.md, then sign it.' }

# ============================================================================
# OUTPUT - punch list + completion
# ============================================================================
$itemNames = @{1='Packaging';2='Deploy-from-source proof';3='No hardcoded env';4='Credential/conn security';5='Least-priv install/uninstall';6='License enforcement';7='Tenancy model';8='Documentation';9='Legal/commercial'}
$itemsWithGaps = @($gaps | Select-Object -Expand Item -Unique)
$itemsDone = 9 - $itemsWithGaps.Count
$pct = [math]::Round(100.0 * $itemsDone / 9, 0)

W ""
W "=============================================================" Cyan
W ("REMAINING WORK TO 100% — {0}/9 items clear ({1}%)" -f $itemsDone, $pct) Cyan
W "=============================================================" Cyan
if ($gaps.Count -eq 0) {
    W "No gaps detected by automated checks. If attestations are signed, this is shippable." Green
} else {
    $order = 'PROOF','SECURITY','BUILD','PACKAGING','DOC','DECLARE','ATTEST'
    $n = 0
    foreach ($cat in $order) {
        $catGaps = $gaps | Where-Object Category -eq $cat
        if (-not $catGaps) { continue }
        W ""
        W "[$cat]" Yellow
        foreach ($g in $catGaps) {
            $n++
            W ("  {0}. (Item {1} — {2}) {3}" -f $n, $g.Item, $itemNames[[int]$g.Item], $g.What) Red
            W ("       FIX: {0}" -f $g.Fix)
        }
    }
}

W ""
W "----- Already clear -----" Green
$passes | ForEach-Object { W "  [done] $_" Green }

# CSV punch list
$csv = Join-Path $ReportDir "ShipGaps_$stamp.csv"
$gaps | Export-Csv -Path $csv -NoTypeInformation -Encoding UTF8
$log | Set-Content -Path $reportPath -Encoding UTF8
W ""
W ("Gaps: {0}   Items clear: {1}/9   Completion: {2}%" -f $gaps.Count, $itemsDone, $pct) Cyan
W "Punch list CSV : $csv"
W "Report         : $reportPath"
Write-Host "`nDone. Work the [PROOF] and [SECURITY] categories first — they fail enterprise review. Paste the punch list back." -ForegroundColor Cyan
