<#
.SYNOPSIS
    TECHS site survey -- one file, no configuration, read-only.

.DESCRIPTION
    Run this ON the customer's TECHS server during a site visit. It answers,
    in one pass, everything we need in order to write the data sync, and it
    writes the answers to two files you carry home.

    Designed for a visit, which means:
      * No config file, no arguments, no install. Right-click -> Run with
        PowerShell, or paste it into a PowerShell window.
      * Windows authentication. You do NOT need the read-only SQL login to
        exist yet -- if you are an administrator on the box, this works.
      * Every section is independently guarded. One failure never stops the
        rest, because there may not be a second visit.
      * It finds the SQL Server instances itself rather than being told.

.NOTES
    READ-ONLY, AND IT TAKES NO BUSINESS DATA.

    It reads system catalogue views, registry values and row-count metadata.
    The only thing it runs against a customer table or view is
    `SELECT TOP (1) 1`, which proves the object can be read without returning
    a single value from it. No 品番, no prices, no customer names are written
    to the report.

    That is deliberate: the report has to be safe to email. Check it before
    sending anyway.

.EXAMPLE
    .\Invoke-TechsSiteSurvey.ps1
    .\Invoke-TechsSiteSurvey.ps1 -OutputDirectory D:\survey
#>
[CmdletBinding()]
param(
    [string] $OutputDirectory = $PSScriptRoot,
    # Extra database names to inspect beyond the auto-detected TECHS ones.
    [string[]] $AlsoInspect = @(),
    # Survey a SQL Server that is NOT on this machine, e.g. 'SV01' or 'SV01\TECHS'.
    # Without this the script surveys every instance installed locally.
    [string] $ServerInstance,
    # Point straight at the TECHS client ini instead of searching for it.
    [string] $ClientIniPath,
    # Search for the TECHS client ini. Off by default: on a server you do not
    # need it, and a filesystem scan at a customer site is time you cannot
    # spend twice.
    [switch] $FindClientIni,
    # Exhaustive C:\ scan for the TECHS client ini. Slow; only if the quick pass fails.
    [switch] $Deep
)

$ErrorActionPreference = 'Continue'
if (-not $OutputDirectory) { $OutputDirectory = (Get-Location).Path }

# Force at least TLS 1.2 for the outbound HTTPS probes below. PS 5.1's
# default SecurityProtocol on an older box can be SSL3/TLS1.0 only, which
# would fail a modern TLS1.2-only endpoint for a completely different
# reason than "not reachable" -- so nail this down before any HTTPS probe
# runs, and record which protocol the handshake actually negotiated.
try {
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 -bor [Net.SecurityProtocolType]::Tls11 -bor [Net.SecurityProtocolType]::Tls
} catch { }
# Accept any server certificate for the reachability probes below -- we WANT
# to see through a corporate TLS-interception proxy (a different issuer than
# expected is exactly the signal worth capturing) rather than have cert
# validation quietly turn that into an indistinguishable "TLS failed". These
# are read-only GETs to two known hosts; nothing sensitive is ever sent.
try {
    [Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }
} catch { }

$stamp  = Get-Date -Format 'yyyyMMdd-HHmmss'
$report = [ordered]@{
    surveyVersion = 2
    collectedAt   = (Get-Date).ToString('o')
    machine       = $null
    techsClient   = $null
    instances     = @()
    reachability  = @()
    proxy         = $null
    privilege     = $null
    residency     = $null
    scheduledTaskFeasibility = $null
    notes         = New-Object System.Collections.ArrayList
}

function Note([string] $m) { [void]$report.notes.Add($m); Write-Host "  ! $m" -ForegroundColor Yellow }
function Section([string] $m) { Write-Host ""; Write-Host $m -ForegroundColor Cyan }

# --------------------------------------------------------------- machine ---
Section 'machine'
try {
    $os = Get-CimInstance Win32_OperatingSystem -ErrorAction Stop
    $report.machine = [ordered]@{
        hostName      = $env:COMPUTERNAME
        domain        = $env:USERDOMAIN
        os            = $os.Caption
        osVersion     = $os.Version
        psVersion     = $PSVersionTable.PSVersion.ToString()
        dotNetFx      = (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP\v4\Full' -ErrorAction SilentlyContinue).Version
        currentUser   = "$env:USERDOMAIN\$env:USERNAME"
        isElevated    = ([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)
        ipv4          = @(Get-NetIPAddress -AddressFamily IPv4 -ErrorAction SilentlyContinue |
                          Where-Object { $_.IPAddress -notlike '127.*' } | ForEach-Object { $_.IPAddress })
    }
    Write-Host ("  {0}  {1}  PS {2}  elevated={3}" -f $report.machine.hostName, $report.machine.os, $report.machine.psVersion, $report.machine.isElevated)
    if (-not $report.machine.isElevated) { Note 'Not elevated. Some registry reads (TCP settings) may be blocked.' }
} catch { Note "machine info failed: $($_.Exception.Message)" }

# ------------------------------------------------------------- privilege ---
Section 'privilege'
# Decides whether tomorrow's install can register a machine-scope scheduled
# task and write outside the user profile, or has to fall back to a
# per-user install instead.
$privilege = [ordered]@{
    currentUser                 = "$env:USERDOMAIN\$env:USERNAME"
    accountType                 = $null
    isElevated                  = $null
    isLocalAdministratorsMember = $null
    localAdminCheckError        = $null
}
try {
    # Ask the machine directly (Win32_ComputerSystem.PartOfDomain) rather than
    # inferring from $env:USERDOMAIN vs $env:COMPUTERNAME. That heuristic
    # looked sound but was confirmed wrong on a real workgroup box reached
    # over an OpenSSH session: $env:USERDOMAIN there was "WORKGROUP", not the
    # computer name, so the old check concluded 'domain' on a machine that
    # Win32_ComputerSystem.PartOfDomain correctly reports as NOT domain-joined.
    $cs = Get-CimInstance Win32_ComputerSystem -ErrorAction Stop
    $privilege.accountType = $(if ($cs.PartOfDomain) { 'domain' } else { 'local' })
} catch {
    # CIM unavailable for some reason -- fall back to the env-var heuristic
    # rather than leaving accountType null, but it is the less reliable of
    # the two (see above), so only used when the authoritative check fails.
    Note "PartOfDomain check failed, falling back to env-var heuristic for account type: $($_.Exception.Message)"
    try {
        $privilege.accountType = $(if ($env:USERDOMAIN -and $env:COMPUTERNAME -and
            ($env:USERDOMAIN.ToUpperInvariant() -eq $env:COMPUTERNAME.ToUpperInvariant())) { 'local' } else { 'domain' })
    } catch { Note "account type detection failed: $($_.Exception.Message)" }
}
try {
    $principal = [Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()
    $privilege.isElevated = $principal.IsInRole([Security.Principal.WindowsBuiltInRole]::Administrator)
} catch { Note "elevation check failed: $($_.Exception.Message)" }
try {
    # Group MEMBERSHIP, independent of whether THIS process happens to be
    # elevated right now (UAC filters admin rights out of a normal token).
    # WinNT provider is a local, read-only query -- no network, no admin
    # rights needed to ask "am I in that group".
    $group = [ADSI] 'WinNT://./Administrators,group'
    $members = @($group.Invoke('Members') | ForEach-Object {
        try { $_.GetType().InvokeMember('Name', 'GetProperty', $null, $_, $null) } catch { $null }
    }) | Where-Object { $_ }
    $privilege.isLocalAdministratorsMember = ($members -contains $env:USERNAME)
} catch {
    $privilege.localAdminCheckError = $_.Exception.Message.Split("`n")[0]
    Note "local Administrators membership check failed: $($privilege.localAdminCheckError)"
}
Write-Host ("  user={0}  type={1}  elevated={2}  adminGroupMember={3}" -f `
    $privilege.currentUser, $privilege.accountType, $privilege.isElevated, $privilege.isLocalAdministratorsMember)
if ($privilege.isLocalAdministratorsMember -and -not $privilege.isElevated) {
    Note "Current user is in the local Administrators group but this process is NOT elevated (UAC). Re-run elevated (Run as administrator) if tomorrow's install needs machine-scope scheduling."
} elseif ($privilege.isLocalAdministratorsMember -eq $false) {
    Note 'Current user is not a local Administrator. The scheduled task will need to be per-user (Task Scheduler "run only when user is logged on", or a user-scope install), not machine-wide.'
}
$report.privilege = $privilege

# ------------------------------------------------------------- residency ---
Section 'machine residency (daytime workstation vs. always-on box)'
$residency = [ordered]@{
    lastBootTime                  = $null
    uptimeSeconds                 = $null
    activePowerPlan                = $null
    sleepTimeouts                  = $null
    displayTimeouts                = $null
    chassisIsLaptop                 = $null
    chassisTypes                   = @()
    batteryPresent                  = $null
    interactiveSessionCount         = $null
    anyInteractiveSessionLoggedOn   = $null
    techsClientRunning              = $null
}
try {
    $os2 = Get-CimInstance Win32_OperatingSystem -ErrorAction Stop
    $residency.lastBootTime  = $os2.LastBootUpTime.ToString('o')
    $residency.uptimeSeconds = [int]((Get-Date) - $os2.LastBootUpTime).TotalSeconds
    Write-Host ("  last boot : {0}  (uptime {1:N1} h)" -f $residency.lastBootTime, ($residency.uptimeSeconds / 3600.0))
} catch { Note "uptime/last boot read failed: $($_.Exception.Message)" }

try {
    $activeRaw = (& powercfg /getactivescheme) -join "`n"
    # Locale-agnostic on purpose: the "Power Scheme GUID:" label itself is
    # localized (a Japanese box prints "電源設定の GUID:" instead), so anchor
    # on the GUID + parenthesized name shape rather than the English label --
    # confirmed necessary against a real ja-JP box, where the English-label
    # regex silently matched nothing and left activePowerPlan null.
    $m = [regex]::Match($activeRaw, '([0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12})\s*\(([^)]*)\)')
    if ($m.Success) { $residency.activePowerPlan = [ordered]@{ guid = $m.Groups[1].Value; name = $m.Groups[2].Value.Trim() } }
} catch { Note "active power plan read failed: $($_.Exception.Message)" }

function Get-TechsPowerCfgTimeouts([string] $SubgroupAlias, [hashtable] $KnownGuids) {
    # Read-only `powercfg /query` -- never /setactive or any mutating verb.
    # Keys on the GUID rather than the printed label because the label is
    # localized (a Japanese box prints Japanese text there); the GUIDs
    # themselves are stable across every Windows locale.
    #
    # The block/field regexes below are ALSO locale-agnostic, and deliberately
    # so: on a real ja-JP box, "Power Setting GUID:" prints as "電源設定の
    # GUID:" -- the exact same Japanese string powercfg uses for the outer
    # "Power Scheme GUID:" line too, so even a naive translation of the old
    # English-anchored regex would misfire. Instead:
    #   * a "setting" line is identified structurally, by 4-space indentation
    #     (powercfg's nesting: scheme=0, subgroup=2, setting=4, alias=6) plus
    #     the GUID+"(name)" shape, never by the localized label text;
    #   * "AC"/"DC" are matched as bare tokens, because Microsoft leaves those
    #     two acronyms in Latin script in every localization (confirmed on
    #     ja-JP: "現在の AC 電源設定のインデックス: 0x...").
    # Verified against a real Windows 11 ja-JP box: the old English-label
    # regex matched zero blocks and returned {} for sleep/display timeouts.
    $out = [ordered]@{}
    try {
        $raw = (& powercfg /query SCHEME_CURRENT $SubgroupAlias 2>$null) -join "`n"
        $guidPattern = '[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}'
        $blockRe = "(?ms)^ {4}\S[^\r\n]*?($guidPattern)\s*\(([^)]*)\)(.*?)(?=^ {4}\S[^\r\n]*?$guidPattern\s*\(|\z)"
        $blocks = [regex]::Matches($raw, $blockRe)
        foreach ($b in $blocks) {
            $guid  = $b.Groups[1].Value.Trim().ToLowerInvariant()
            $label = $b.Groups[2].Value.Trim()
            $body  = $b.Groups[3].Value
            $ac = [regex]::Match($body, '\bAC\b[^\r\n]*?0x([0-9a-fA-F]+)')
            $dc = [regex]::Match($body, '\bDC\b[^\r\n]*?0x([0-9a-fA-F]+)')
            $acSec = $(if ($ac.Success) { [Convert]::ToInt64($ac.Groups[1].Value, 16) } else { $null })
            $dcSec = $(if ($dc.Success) { [Convert]::ToInt64($dc.Groups[1].Value, 16) } else { $null })
            $key = $(if ($KnownGuids.ContainsKey($guid)) { $KnownGuids[$guid] } else { $guid })
            $out[$key] = [ordered]@{ guid = $guid; label = $label; acSeconds = $acSec; dcSeconds = $dcSec }
        }
    } catch { }
    return $out
}
try {
    $sleepGuids = @{ '29f6c1db-86da-48c5-9fdb-f2b67b1f44da' = 'standbyIdle'; '9d7815a6-7ee4-497e-8888-515a05f02364' = 'hibernateIdle' }
    $videoGuids = @{ '3c0bc021-c8a8-4e07-a973-6b14cbcb2b7e' = 'displayIdle' }
    $residency.sleepTimeouts   = Get-TechsPowerCfgTimeouts 'SUB_SLEEP' $sleepGuids
    $residency.displayTimeouts = Get-TechsPowerCfgTimeouts 'SUB_VIDEO' $videoGuids
} catch { Note "power timeout read failed: $($_.Exception.Message)" }

try {
    $enclosure = Get-CimInstance Win32_SystemEnclosure -ErrorAction Stop | Select-Object -First 1
    $residency.chassisTypes = @($enclosure.ChassisTypes)
    # 8 Portable, 9 Laptop, 10 Notebook, 11 Hand Held, 12 Docking Station,
    # 14 Sub Notebook, 18 Expansion Chassis (some laptop docks), 21/30/31/32 Tablet/Convertible.
    $laptopCodes = @(8, 9, 10, 11, 12, 14, 18, 21, 30, 31, 32)
    $residency.chassisIsLaptop = (@($residency.chassisTypes | Where-Object { $laptopCodes -contains $_ }).Count -gt 0)
} catch { Note "chassis type read failed: $($_.Exception.Message)" }

try {
    $battery = Get-CimInstance Win32_Battery -ErrorAction SilentlyContinue
    $residency.batteryPresent = (@($battery).Count -gt 0)
} catch { Note "battery presence check failed: $($_.Exception.Message)" }

try {
    $quserRaw = & quser 2>$null
    if ($LASTEXITCODE -eq 0 -and $quserRaw) {
        # header line + one line per session
        $sessionLines = @($quserRaw | Select-Object -Skip 1 | Where-Object { $_ -and $_.Trim() })
        $residency.interactiveSessionCount = $sessionLines.Count
        $residency.anyInteractiveSessionLoggedOn = ($sessionLines.Count -gt 0)
    } else {
        # exit code 1 with no output is quser's own way of saying "nobody logged on"
        $residency.interactiveSessionCount = 0
        $residency.anyInteractiveSessionLoggedOn = $false
    }
} catch { Note "interactive session enumeration failed (quser unavailable?): $($_.Exception.Message)" }

try {
    $techsProc = Get-Process -Name 'Technoa.Techs.Menu' -ErrorAction SilentlyContinue
    $residency.techsClientRunning = (@($techsProc).Count -gt 0)
} catch { Note "TECHS client process check failed: $($_.Exception.Message)" }

Write-Host ("  chassis laptop={0}  battery={1}  interactiveSessions={2}  techsRunning={3}" -f `
    $residency.chassisIsLaptop, $residency.batteryPresent, $residency.interactiveSessionCount, $residency.techsClientRunning)
$report.residency = $residency

# ----------------------------------------------------------------- proxy ---
Section 'proxy configuration'
$proxy = [ordered]@{
    winHttp      = $null
    winInet      = $null
    envVars      = $null
    pacUrl       = $null
    authRequired = $null   # not determinable read-only, without attempting to authenticate
}
try {
    $whRaw = (& netsh winhttp show proxy) -join "`n"
    $directOnly = $whRaw -match '(?i)Direct access \(no proxy server\)'
    $proxyServerMatch = [regex]::Match($whRaw, '(?im)^\s*Proxy Server\(s\)\s*:\s*(.+?)\s*$')
    $bypassMatch      = [regex]::Match($whRaw, '(?im)^\s*Bypass List\s*:\s*(.+?)\s*$')
    $proxy.winHttp = [ordered]@{
        configured  = -not $directOnly
        proxyServer = $(if ($proxyServerMatch.Success) { $proxyServerMatch.Groups[1].Value.Trim() } else { $null })
        bypassList  = $(if ($bypassMatch.Success) { $bypassMatch.Groups[1].Value.Trim() } else { $null })
    }
} catch { Note "WinHTTP proxy read failed: $($_.Exception.Message)" }

try {
    $iep = Get-ItemProperty 'HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings' -ErrorAction Stop
    $proxy.winInet = [ordered]@{
        proxyEnabled  = [bool]($iep.ProxyEnable)
        proxyServer   = $(if ($iep.PSObject.Properties.Name -contains 'ProxyServer')   { $iep.ProxyServer }   else { $null })
        proxyOverride = $(if ($iep.PSObject.Properties.Name -contains 'ProxyOverride') { $iep.ProxyOverride } else { $null })
        autoConfigURL = $(if ($iep.PSObject.Properties.Name -contains 'AutoConfigURL') { $iep.AutoConfigURL } else { $null })
        autoDetect    = $(if ($iep.PSObject.Properties.Name -contains 'AutoDetect')    { [bool]($iep.AutoDetect) } else { $null })
    }
    if ($proxy.winInet.autoConfigURL) { $proxy.pacUrl = $proxy.winInet.autoConfigURL }
} catch { Note "WinINet (per-user IE) proxy settings read failed: $($_.Exception.Message)" }

try {
    $proxy.envVars = [ordered]@{
        HTTP_PROXY  = $(if ($env:HTTP_PROXY)  { $env:HTTP_PROXY }  elseif ($env:http_proxy)  { $env:http_proxy }  else { $null })
        HTTPS_PROXY = $(if ($env:HTTPS_PROXY) { $env:HTTPS_PROXY } elseif ($env:https_proxy) { $env:https_proxy } else { $null })
        NO_PROXY    = $(if ($env:NO_PROXY)    { $env:NO_PROXY }    elseif ($env:no_proxy)    { $env:no_proxy }    else { $null })
    }
} catch { Note "proxy environment variable read failed: $($_.Exception.Message)" }

$anyProxyConfigured = ($proxy.winHttp -and $proxy.winHttp.configured) -or
                      ($proxy.winInet -and $proxy.winInet.proxyEnabled) -or
                      $proxy.envVars.HTTP_PROXY -or $proxy.envVars.HTTPS_PROXY -or $proxy.pacUrl
Write-Host ("  WinHTTP configured={0}  WinINet enabled={1}  env proxy set={2}  PAC={3}" -f `
    $(if ($proxy.winHttp) { $proxy.winHttp.configured } else { '?' }),
    $(if ($proxy.winInet) { $proxy.winInet.proxyEnabled } else { '?' }),
    [bool]($proxy.envVars.HTTP_PROXY -or $proxy.envVars.HTTPS_PROXY), [bool]$proxy.pacUrl)
if ($anyProxyConfigured) {
    Note 'A proxy or PAC is configured on this machine. Whether it requires authentication is not determinable read-only without attempting a connection through it -- watch for it in the reachability results below.'
}
$report.proxy = $proxy

# ------------------------------------------------------------ reachability ---
Section 'outbound reachability (phone-home candidates)'
# We have not decided which of these two hosts the self-updating sync agent
# will phone home to, so both are measured. A connection failure/timeout/DNS
# failure is the real negative; for the PMS API's unauthenticated health
# path, HTTP 401 (key required, none supplied) IS success -- it proves TLS,
# routing and the application are all reachable.

function Resolve-TechsHost([string] $HostName) {
    # Deliberately a plain BLOCKING call, not the Begin/End or *Async DNS
    # wrapper. Testing surfaced those legacy async-DNS wrappers hanging past
    # their own WaitOne/Task timeout in some hosting environments (container
    # sandboxes in particular) even though a synchronous lookup on the same
    # box resolved instantly -- exactly the kind of surprise this survey
    # cannot afford on a customer's PC. Windows' own DNS client already
    # applies a bounded per-server timeout and retry count, so a genuinely
    # blackholed DNS server still fails in bounded time (seconds, not
    # forever) without an extra app-level wrapper to get wrong.
    $r = [ordered]@{ success = $false; addresses = @(); elapsedMs = $null; error = $null }
    $sw = [Diagnostics.Stopwatch]::StartNew()
    try {
        $addrs = [System.Net.Dns]::GetHostAddresses($HostName)
        $r.success = $true
        $r.addresses = @($addrs | ForEach-Object { $_.ToString() })
    } catch { $r.error = $_.Exception.Message.Split("`n")[0] }
    $r.elapsedMs = [int]$sw.Elapsed.TotalMilliseconds
    return $r
}

function Connect-TechsTcp([System.Net.IPAddress] $IPAddress, [int] $Port, [int] $TimeoutMs) {
    # Deliberately takes the already-resolved IP, not a hostname.
    # TcpClient.BeginConnect(string,...) resolves DNS again internally on its
    # own async path before connecting -- testing surfaced that path hanging
    # in at least one hosting environment even though a plain synchronous
    # Dns.GetHostAddresses() and BeginConnect(IPAddress,...) both returned
    # immediately. Reusing the DNS stage's own result sidesteps that
    # entirely and keeps "DNS failed" and "TCP failed" cleanly distinct,
    # which is exactly what this probe is supposed to tell apart.
    $r = [ordered]@{ success = $false; elapsedMs = $null; error = $null }
    $sw = [Diagnostics.Stopwatch]::StartNew()
    $client = New-Object System.Net.Sockets.TcpClient
    try {
        $iar = $client.BeginConnect($IPAddress, $Port, $null, $null)
        if (-not $iar.AsyncWaitHandle.WaitOne($TimeoutMs)) { throw "TCP connect to ${IPAddress}:${Port} timed out after ${TimeoutMs}ms" }
        $client.EndConnect($iar)
        $r.success = $true
    } catch { $r.error = $_.Exception.Message.Split("`n")[0] }
    $r.elapsedMs = [int]$sw.Elapsed.TotalMilliseconds
    return @{ result = $r; client = $client }
}

function Test-TechsTls([System.Net.Sockets.TcpClient] $Client, [string] $HostName, [int] $TimeoutMs) {
    $r = [ordered]@{ success = $false; protocol = $null; certSubject = $null; certIssuer = $null; elapsedMs = $null; error = $null }
    $sw = [Diagnostics.Stopwatch]::StartNew()
    try {
        $Client.Client.ReceiveTimeout = $TimeoutMs
        $Client.Client.SendTimeout    = $TimeoutMs
        $sslStream = New-Object System.Net.Security.SslStream(
            $Client.GetStream(), $false, ({ param($sender, $cert, $chain, $errors) $true }))
        $protocols = [Security.Authentication.SslProtocols]::Tls12 -bor [Security.Authentication.SslProtocols]::Tls11 -bor [Security.Authentication.SslProtocols]::Tls
        $sslStream.AuthenticateAsClient($HostName, $null, $protocols, $false)
        $r.success  = $true
        $r.protocol = $sslStream.SslProtocol.ToString()
        $cert = $sslStream.RemoteCertificate
        if ($cert) {
            $cert2 = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2($cert)
            $r.certSubject = $cert2.Subject
            $r.certIssuer  = $cert2.Issuer
        }
        $sslStream.Close()
    } catch { $r.error = $_.Exception.Message.Split("`n")[0] }
    $r.elapsedMs = [int]$sw.Elapsed.TotalMilliseconds
    return $r
}

function Invoke-TechsHttpProbe([string] $Url, [int] $TimeoutMs) {
    $r = [ordered]@{ success = $false; statusCode = $null; elapsedMs = $null; error = $null }
    $sw = [Diagnostics.Stopwatch]::StartNew()
    try {
        $req = [System.Net.HttpWebRequest]::Create($Url)
        $req.Method = 'GET'
        $req.Timeout = $TimeoutMs
        $req.ReadWriteTimeout = $TimeoutMs
        $req.UserAgent = 'fab-forward-techs-site-survey/1'
        try {
            $resp = $req.GetResponse()
            $r.statusCode = [int]$resp.StatusCode
            $r.success = $true
            $resp.Close()
        } catch [System.Net.WebException] {
            if ($_.Exception.Response) {
                # Any HTTP response at all -- even 401/403/500 -- proves TLS,
                # routing and the application are reachable. That is a
                # DIFFERENT question from whether the status is the one we
                # expected; expectedSuccess (set by the caller) answers that.
                $r.statusCode = [int]$_.Exception.Response.StatusCode
                $r.success = $true
                $_.Exception.Response.Close()
            } else {
                throw
            }
        }
    } catch { $r.error = $_.Exception.Message.Split("`n")[0] }
    $r.elapsedMs = [int]$sw.Elapsed.TotalMilliseconds
    return $r
}

function Test-TechsHostReachability([string] $Label, [string] $Url, [int[]] $SuccessStatusCodes) {
    $uri = [Uri] $Url
    $hostName = $uri.Host
    $port = $(if ($uri.Port -gt 0) { $uri.Port } else { 443 })
    $entry = [ordered]@{
        label = $Label; url = $Url; host = $hostName
        dns = $null; tcp = $null; tls = $null; http = $null
        failureStage = $null; totalElapsedMs = $null
    }
    $sw = [Diagnostics.Stopwatch]::StartNew()

    $entry.dns = Resolve-TechsHost $hostName
    if (-not $entry.dns.success) {
        $entry.failureStage = 'dns'; $entry.totalElapsedMs = [int]$sw.Elapsed.TotalMilliseconds
        return $entry
    }

    $ip = $null
    try { $ip = [System.Net.IPAddress]::Parse(@($entry.dns.addresses)[0]) } catch { }
    if (-not $ip) {
        $entry.failureStage = 'tcp'
        $entry.tcp = [ordered]@{ success = $false; elapsedMs = 0; error = 'DNS returned success but no usable address' }
        $entry.totalElapsedMs = [int]$sw.Elapsed.TotalMilliseconds
        return $entry
    }

    $tcpAttempt = Connect-TechsTcp $ip $port 4000
    $entry.tcp = $tcpAttempt.result
    if (-not $entry.tcp.success) {
        $entry.failureStage = 'tcp'; $entry.totalElapsedMs = [int]$sw.Elapsed.TotalMilliseconds
        $tcpAttempt.client.Close()
        return $entry
    }

    $entry.tls = Test-TechsTls $tcpAttempt.client $hostName 5000
    $tcpAttempt.client.Close()
    if (-not $entry.tls.success) {
        $entry.failureStage = 'tls'; $entry.totalElapsedMs = [int]$sw.Elapsed.TotalMilliseconds
        return $entry
    }

    $entry.http = Invoke-TechsHttpProbe $Url 6000
    if (-not $entry.http.success) {
        $entry.failureStage = 'http'
    } else {
        $entry.http.expectedSuccess = ($SuccessStatusCodes -contains $entry.http.statusCode)
    }
    $entry.totalElapsedMs = [int]$sw.Elapsed.TotalMilliseconds
    return $entry
}

$reachabilityTargets = @(
    # 308 included alongside 301/302: confirmed live behaviour is Caddy
    # permanently redirecting '/' to '/fab-forward-dev/' -- a real, stable
    # reachable response, not a failure -- so it belongs with the other
    # redirect codes already treated as success here.
    @{ label = 'distributionPortal'; url = 'https://dev.fab-forward.co.jp/'; successCodes = @(200, 301, 302, 304, 308) }
    # AMBIGUOUS ON PURPOSE: config.example.json's api.baseUrl is a placeholder
    # ("https://pms.example.co.jp"), not a real hostname -- see README.md /
    # config.example.json, neither of which names the deployed host. The
    # real hostname for the live `create` tenant this sync is being built
    # against, per fab-forward-pms/docs/deployment-instances.md, is
    # create.fab-forward.co.jp. Both candidate update/ingest hosts are
    # probed since the design has not committed to one yet.
    @{ label = 'pmsApiCreateTenant'; url = 'https://create.fab-forward.co.jp/api/integrations/techs/ping'; successCodes = @(401, 200) }
)
foreach ($t in $reachabilityTargets) {
    Write-Host "  probing $($t.label) ($($t.url)) ..." -ForegroundColor DarkGray
    try {
        $res = Test-TechsHostReachability $t.label $t.url $t.successCodes
    } catch {
        $res = [ordered]@{
            label = $t.label; url = $t.url; host = $null; dns = $null; tcp = $null; tls = $null; http = $null
            failureStage = 'unexpected'; totalElapsedMs = $null; error = $_.Exception.Message.Split("`n")[0]
        }
        Note "reachability probe for $($t.label) threw unexpectedly: $($res.error)"
    }
    $report.reachability += $res
    if ($res.failureStage) {
        Write-Host ("    FAILED at {0} stage  ({1}ms)" -f $res.failureStage, $res.totalElapsedMs) -ForegroundColor Red
        Note "$($t.label) ($($res.host)) not reachable -- failed at the $($res.failureStage) stage."
    } elseif ($res.http -and $res.http.expectedSuccess) {
        Write-Host ("    OK  HTTP {0}  tls={1}  {2}ms" -f $res.http.statusCode, $res.tls.protocol, $res.totalElapsedMs) -ForegroundColor Green
    } elseif ($res.http) {
        Write-Host ("    reached, but HTTP {0} was not an expected status  ({1}ms)" -f $res.http.statusCode, $res.totalElapsedMs) -ForegroundColor Yellow
        Note "$($t.label) ($($res.host)) reachable but returned unexpected HTTP $($res.http.statusCode)."
    }
    if ($res.tls -and $res.tls.success -and $res.tls.certIssuer -and
        ($res.tls.certIssuer -notmatch "(?i)let'?s encrypt|digicert|sectigo|comodo|amazon|google trust|globalsign|zerossl|isrg")) {
        Note "$($t.label): TLS certificate issuer is '$($res.tls.certIssuer)' -- if that is not a public CA you recognise, a corporate/security proxy is very likely intercepting TLS on this network."
    }
}

# --------------------------------------------------- scheduled-task check ---
Section 'scheduled-task feasibility'
# Read-only: never creates, modifies or deletes a task. Just answers
# "can tomorrow's install register one."
$taskFeasibility = [ordered]@{
    schedulerServiceStatus = $null
    canEnumerateTasks      = $null
    taskCount              = $null
    error                  = $null
}
try {
    $svc = Get-Service -Name Schedule -ErrorAction Stop
    $taskFeasibility.schedulerServiceStatus = "$($svc.Status)"
} catch { Note "Task Scheduler service status read failed: $($_.Exception.Message)" }
try {
    $tasks = Get-ScheduledTask -ErrorAction Stop
    $taskFeasibility.canEnumerateTasks = $true
    $taskFeasibility.taskCount = @($tasks).Count
} catch {
    $taskFeasibility.canEnumerateTasks = $false
    $taskFeasibility.error = $_.Exception.Message.Split("`n")[0]
    Note "cannot enumerate scheduled tasks (ScheduledTasks module missing, or access denied): $($taskFeasibility.error)"
}
Write-Host ("  scheduler service={0}  canEnumerateTasks={1}  taskCount={2}" -f `
    $taskFeasibility.schedulerServiceStatus, $taskFeasibility.canEnumerateTasks, $taskFeasibility.taskCount)
$report.scheduledTaskFeasibility = $taskFeasibility

# ---------------------------------------------------------- TECHS client ---
Section 'TECHS client install'
# On a CLIENT WORKSTATION this is the whole game: the client config names the
# SQL Server, the application server and the TECHS version, so we can survey a
# server we are not sitting at and can tell whether the reverse-engineered
# schema (v6.05) even applies here.
#
# Known layout, from the reverse-engineered client:
#   <install>\Techs.ini                                     version, e.g. 0605.0002.0001.0000
#   <install>\Technoa.Techs.ClientCommonLibrary.dll.config   DataSource / InitialCatalog /
#                                                            WebServiceServer / SIPort / UserId / PassWord
#   <install>\EUCConnection.ini                              EUC tool's own SERVER/DATABASE
#
# CREDENTIALS: that config holds UserId and PassWord in clear text (the
# reference install ships UserId=sa). They are read to know WHETHER they exist,
# and never written to the report.
$RE_VERSION = '0605.0002.0001.0000'   # the version our schema map was built from
$techs = [ordered]@{
    installPath = $null; version = $null; versionMatchesReverseEngineering = $null
    dataSource = $null; initialCatalog = $null
    webServiceServer = $null; webServiceFolder = $null; siPort = $null
    configuredUserId = $null; passwordPresentInPlaintext = $null
    eucServer = $null; eucDatabase = $null; sources = @()
}
try {
    $roots = @()
    if ($ClientIniPath) { $roots += (Split-Path $ClientIniPath -Parent) }
    $roots += @(
        "${env:ProgramFiles(x86)}\Technoa", "$env:ProgramFiles\Technoa",
        'C:\Technoa', 'D:\Technoa', 'C:\TECHS', 'D:\TECHS'
    )
    $roots = $roots | Where-Object { $_ -and (Test-Path $_) } | Select-Object -Unique

    $cfg = $null; $ini = $null; $euc = $null
    foreach ($r in $roots) {
        if (-not $cfg) { $cfg = Get-ChildItem $r -Recurse -Depth 4 -Filter 'Technoa.Techs.ClientCommonLibrary.dll.config' -File -EA SilentlyContinue | Select-Object -First 1 }
        if (-not $ini) { $ini = Get-ChildItem $r -Recurse -Depth 4 -Filter 'Techs.ini' -File -EA SilentlyContinue | Select-Object -First 1 }
        if (-not $euc) { $euc = Get-ChildItem $r -Recurse -Depth 4 -Filter 'EUCConnection.ini' -File -EA SilentlyContinue | Select-Object -First 1 }
        if ($cfg -and $ini -and $euc) { break }
    }
    if (-not ($cfg -or $ini -or $euc) -and $FindClientIni) {
        Write-Host '  not in the usual places; scanning C:\ (slow) ...' -ForegroundColor DarkGray
        $cfg = Get-ChildItem 'C:\' -Recurse -Filter 'Technoa.Techs.ClientCommonLibrary.dll.config' -File -EA SilentlyContinue | Select-Object -First 1
        if ($cfg) { $ini = Get-ChildItem (Split-Path $cfg.FullName -Parent) -Filter 'Techs.ini' -File -EA SilentlyContinue | Select-Object -First 1 }
    }

    if ($ini) {
        $techs.sources += $ini.FullName
        $techs.installPath = Split-Path $ini.FullName -Parent
        $techs.version = ((Get-Content $ini.FullName -EA SilentlyContinue) -join '').Trim()
        $techs.versionMatchesReverseEngineering = ($techs.version -eq $RE_VERSION)
        Write-Host "  install : $($techs.installPath)"
        Write-Host "  version : $($techs.version)"
        if (-not $techs.versionMatchesReverseEngineering) {
            Note "TECHS version is $($techs.version); our schema map was built from $RE_VERSION. Table/view names may differ -- treat the object results below as the authority."
        } else {
            Write-Host "            matches the reverse-engineered $RE_VERSION" -ForegroundColor Green
        }
    } else { Note 'Techs.ini not found -- TECHS version unknown.' }

    if ($cfg) {
        $techs.sources += $cfg.FullName
        $x = Get-Content $cfg.FullName -Raw -EA SilentlyContinue
        function Setting([string] $n) {
            $m = [regex]::Match($x, '<setting name="' + [regex]::Escape($n) + '"[^>]*>\s*(?:<value>)?(.*?)(?:</value>)?\s*</setting>', 'Singleline')
            if ($m.Success) { return $m.Groups[1].Value.Trim() } else { return $null }
        }
        $techs.dataSource       = Setting 'DataSource'
        $techs.initialCatalog   = Setting 'InitialCatalog'
        $techs.webServiceServer = Setting 'WebServiceServer'
        $techs.webServiceFolder = Setting 'WebServiceFolder'
        $techs.siPort           = Setting 'SIPort'
        $techs.configuredUserId = Setting 'UserId'
        $pw = Setting 'PassWord'
        # Recorded as a boolean only. The value itself never leaves this machine.
        $techs.passwordPresentInPlaintext = [bool]($pw)
        Write-Host "  SQL     : DataSource=$($techs.dataSource)  Catalog=$($techs.initialCatalog)"
        Write-Host "  AP      : WebServiceServer=$($techs.webServiceServer)  Folder=$($techs.webServiceFolder)  Port=$($techs.siPort)"
        Write-Host "  login   : UserId=$($techs.configuredUserId)  password stored in plaintext=$($techs.passwordPresentInPlaintext)"
        if ($techs.passwordPresentInPlaintext) {
            Note "This workstation stores a SQL password in clear text in $($cfg.Name) (UserId=$($techs.configuredUserId)). Not copied into this report. Worth raising with the customer."
        }
    } else { Note 'Technoa.Techs.ClientCommonLibrary.dll.config not found -- cannot read the SQL Server name from the client.' }

    if ($euc) {
        $techs.sources += $euc.FullName
        $e = Get-Content $euc.FullName -Raw -EA SilentlyContinue
        $m1 = [regex]::Match($e, '(?im)^\s*SERVER\s*=\s*(.+?)\s*$');   if ($m1.Success) { $techs.eucServer   = $m1.Groups[1].Value }
        $m2 = [regex]::Match($e, '(?im)^\s*DATABASE\s*=\s*(.+?)\s*$'); if ($m2.Success) { $techs.eucDatabase = $m2.Groups[1].Value }
        Write-Host "  EUC     : SERVER=$($techs.eucServer)  DATABASE=$($techs.eucDatabase)"
    }
} catch { Note "TECHS client scan failed: $($_.Exception.Message)" }
$report.techsClient = $techs

# ------------------------------------------------------------- instances ---
Section 'SQL Server instances'
$instanceMap = @{}
try {
    $k = Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' -ErrorAction Stop
    foreach ($p in $k.PSObject.Properties) {
        if ($p.Name -like 'PS*') { continue }
        $instanceMap[$p.Name] = $p.Value
    }
} catch { Note "instance registry unreadable: $($_.Exception.Message)" }

if (-not $ServerInstance) {
    # On a client workstation there is no local engine, but the client config
    # just told us where the server is. Use it rather than making the operator
    # retype a name they have no way to know.
    $fromClient = $report.techsClient.dataSource
    if (-not $fromClient) { $fromClient = $report.techsClient.eucServer }
    if ($fromClient -and $instanceMap.Count -eq 0) {
        $ServerInstance = $fromClient
        Write-Host "  no local engine; using the server named by the TECHS client: $ServerInstance" -ForegroundColor DarkGray
    }
}
if ($ServerInstance) {
    # Explicit target wins: the SQL Server may not be on this machine at all.
    $instanceMap = @{}
    $instanceMap[$ServerInstance] = '(remote/explicit)'
    Write-Host "  targeting $ServerInstance (registry discovery skipped)" -ForegroundColor DarkGray
}
elseif ($instanceMap.Count -eq 0) {
    Note 'No SQL Server instance found in the registry on this machine. If the server is elsewhere, re-run with -ServerInstance <name>.'
}

function Get-InstanceNetwork([string] $name, [string] $key) {
    $out = [ordered]@{ tcpEnabled = $null; staticPort = $null; dynamicPort = $null; portKind = 'unknown' }
    try {
        $base = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$key\MSSQLServer"
        $tcp  = "$base\SuperSocketNetLib\Tcp"
        $out.tcpEnabled = (Get-ItemProperty $tcp -Name Enabled -ErrorAction Stop).Enabled
        $ipAll = Get-ItemProperty "$tcp\IPAll" -ErrorAction Stop
        $out.staticPort  = $ipAll.TcpPort
        $out.dynamicPort = $ipAll.TcpDynamicPorts
        if ($ipAll.TcpPort)             { $out.portKind = 'static' }
        elseif ($ipAll.TcpDynamicPorts) { $out.portKind = 'dynamic' }
    } catch { }
    return $out
}

function Invoke-Sql([string] $dataSource, [string] $database, [string] $sql, [int] $timeout = 30) {
    $cs = "Data Source=$dataSource;Initial Catalog=$database;Integrated Security=True;" +
          "Encrypt=True;TrustServerCertificate=True;Connect Timeout=10;Application Name=fab-forward site survey"
    $conn = New-Object System.Data.SqlClient.SqlConnection $cs
    $conn.Open()
    try {
        $cmd = $conn.CreateCommand()
        $cmd.CommandText = $sql
        $cmd.CommandTimeout = $timeout
        $rows = New-Object System.Collections.ArrayList
        $r = $cmd.ExecuteReader()
        try {
            while ($r.Read()) {
                $row = [ordered]@{}
                for ($i = 0; $i -lt $r.FieldCount; $i++) {
                    $v = $r.GetValue($i)
                    $row[$r.GetName($i)] = $(if ($v -is [DBNull]) { $null } else { $v })
                }
                [void]$rows.Add($row)
            }
        } finally { $r.Close() }
        return , $rows.ToArray()
    } finally { $conn.Close() }
}

# The objects the sync expects, from tools/techs-sync/entities.json. Inlined so
# this stays a single portable file. NOTHING here is confirmed -- proving or
# disproving these names is the main reason for the visit.
$expected = @(
    @{ entity='item';           view='VIWRPTMSTITEMNO';                table='MstItemNo';     keys=@('ItemNo','ItemNoRev') }
    @{ entity='item_extra';     view=$null;                            table='MstItemNoInfo'; keys=@('ItemNo') }
    @{ entity='bom';            view='VIWRPTMSTPARTSCOMPOSE';          table='MstPartsCompose'; keys=@('ItemNo','ComposeSeq') }
    @{ entity='supplier';       view='VIWRPTMSTSUPPLIERBUYUP';         table='MstSupplier';   keys=@('SupplierCd') }
    @{ entity='deal';           view='VIWRPTMSTDEAL';                  table='MstDeal';       keys=@('DealCd') }
    @{ entity='supplier_price'; view='VIWRPTMSTSUPPLIERBUYUP';         table='MstSupplierBuyUp'; keys=@('SupplierCd','ItemNo') }
    @{ entity='drawing';        view='VIWRPTMSTPICTURENO';             table='MstPictureNo';  keys=@('PictureNo','PictureNoRev') }
    @{ entity='order';          view=$null;                            table='TrnOrderH';     keys=@('OrderNo') }
    @{ entity='order_detail';   view=$null;                            table='TrnOrderD';     keys=@('OrderNo') }
    @{ entity='seiban';         view=$null;                            table='TrnSeiban';     keys=@('SeibanNo') }
    @{ entity='inventory';      view='VIWRPTINVSTOCKLISTINFO';         table='TrnInvStock';   keys=@('ItemNo','HouseCd') }
    @{ entity='purchase_order'; view='VIWRPTPARTSORDERSLIPORDERINFO';  table='TrnSOrderH';    keys=@('SOrderNo') }
    @{ entity='receiving';      view='VIWRPTACCEPTLISTINFO';           table='TrnBuyAccept';  keys=@('AcceptNo') }
)
$auditColumns = @('TmStamp','UpdDt','InsDt','DelFlg','UpdUserCd')

foreach ($instName in $instanceMap.Keys) {
    $key = $instanceMap[$instName]
    $ds  = $(if ($ServerInstance) { $ServerInstance }
             elseif ($instName -eq 'MSSQLSERVER') { $env:COMPUTERNAME }
             else { "$env:COMPUTERNAME\$instName" })
    Write-Host ""
    Write-Host "  instance: $instName  ($key)" -ForegroundColor White

    $inst = [ordered]@{
        instanceName = $instName
        registryKey  = $key
        dataSource   = $ds
        network      = $(if ($ServerInstance) { [ordered]@{ tcpEnabled=$null; staticPort=$null; dynamicPort=$null; portKind='n/a (remote)' } }
                         else { Get-InstanceNetwork $instName $key })
        service      = $null
        connected    = $false
        error        = $null
        server       = $null
        databases    = @()
        techsDatabases = @()
    }

    $svcName = $(if ($instName -eq 'MSSQLSERVER') { 'MSSQLSERVER' } else { "MSSQL`$$instName" })
    $svc = $(if ($ServerInstance) { $null } else { Get-Service $svcName -ErrorAction SilentlyContinue })
    if ($svc) { $inst.service = [ordered]@{ name = $svc.Name; status = "$($svc.Status)"; startType = "$($svc.StartType)" } }
    Write-Host ("    service   : {0}  network: tcp={1} port={2}/{3} ({4})" -f `
        $(if ($svc) { $svc.Status } else { 'not found' }), $inst.network.tcpEnabled,
        $inst.network.staticPort, $inst.network.dynamicPort, $inst.network.portKind)

    try {
        $srv = Invoke-Sql $ds 'master' @"
SELECT CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(32))  AS ProductVersion,
       CAST(SERVERPROPERTY('ProductLevel')   AS nvarchar(32))  AS ProductLevel,
       CAST(SERVERPROPERTY('Edition')        AS nvarchar(128)) AS Edition,
       CAST(SERVERPROPERTY('Collation')      AS nvarchar(128)) AS ServerCollation,
       CAST(SERVERPROPERTY('IsIntegratedSecurityOnly') AS int)  AS WindowsAuthOnly,
       CAST(SERVERPROPERTY('InstanceName')   AS nvarchar(128)) AS InstanceName,
       @@SERVERNAME AS ServerName, SUSER_SNAME() AS LoginName,
       IS_SRVROLEMEMBER('sysadmin') AS IsSysadmin
"@
        $inst.connected = $true
        $inst.server = $srv[0]
        Write-Host ("    version   : {0} {1} / {2}" -f $srv[0].ProductVersion, $srv[0].ProductLevel, $srv[0].Edition)
        Write-Host ("    auth      : {0}   as {1} (sysadmin={2})" -f `
            $(if ($srv[0].WindowsAuthOnly -eq 1) { 'Windows only  <-- SQL login will NOT work yet' } else { 'mixed' }),
            $srv[0].LoginName, $srv[0].IsSysadmin)

        $inst.listeners = Invoke-Sql $ds 'master' "SELECT ip_address, port, type_desc, state_desc FROM sys.dm_tcp_listener_states"

        $dbs = Invoke-Sql $ds 'master' @"
SELECT name, database_id, state_desc, collation_name,
       CAST(DATABASEPROPERTYEX(name,'Updateability') AS nvarchar(32)) AS Updateability
FROM sys.databases WHERE database_id > 4 ORDER BY name
"@
        $inst.databases = $dbs
        Write-Host ("    databases : {0}" -f (($dbs | ForEach-Object { $_.name }) -join ', '))

        # Anything that looks like a TECHS database, plus whatever was asked for.
        $targets = @($dbs | Where-Object { $_.name -match '^(TECHS|EUCTOOL)' } | ForEach-Object { $_.name })
        $targets += @($AlsoInspect | Where-Object { $dbs.name -contains $_ })
        $targets = $targets | Select-Object -Unique
        if ($targets.Count -eq 0) { Note "instance $instName has no TECHS*/EUCTOOL database; use -AlsoInspect <name> if it is called something else" }

        foreach ($db in $targets) {
            Write-Host "    inspecting $db ..." -ForegroundColor DarkGray
            $dbInfo = [ordered]@{ database = $db; objects = @(); textColumnTypes = @() }
            foreach ($e in $expected) {
                foreach ($kind in @('view','table')) {
                    $objName = $e[$kind]
                    if (-not $objName) { continue }
                    $o = [ordered]@{ entity=$e.entity; kind=$kind; name=$objName; exists=$false
                                     columnCount=0; approxRows=$null; readable=$null; readError=$null
                                     hasTmStamp=$false; hasUpdDt=$false; hasDelFlg=$false
                                     businessKeyPresent=@(); businessKeyMissing=@() }
                    try {
                        $meta = Invoke-Sql $ds $db @"
SELECT o.type_desc AS ObjectType,
       (SELECT COUNT(*) FROM sys.columns c WHERE c.object_id = o.object_id) AS ColumnCount
FROM sys.objects o WHERE o.name = '$($objName -replace "'","''")' AND o.type IN ('U','V')
"@
                        if ($meta.Count -gt 0) {
                            $o.exists = $true
                            $o.columnCount = $meta[0].ColumnCount
                            $cols = Invoke-Sql $ds $db @"
SELECT c.name AS ColumnName, t.name AS DataType
FROM sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('$($objName -replace "'","''")')
"@
                            $names = @($cols | ForEach-Object { $_.ColumnName })
                            $o.hasTmStamp = $names -contains 'TmStamp'
                            $o.hasUpdDt   = $names -contains 'UpdDt'
                            $o.hasDelFlg  = $names -contains 'DelFlg'
                            foreach ($kcol in $e.keys) {
                                if ($names -contains $kcol) { $o.businessKeyPresent += $kcol } else { $o.businessKeyMissing += $kcol }
                            }
                            if ($meta[0].ObjectType -eq 'USER_TABLE') {
                                $rc = Invoke-Sql $ds $db "SELECT SUM(rows) AS r FROM sys.partitions WHERE object_id = OBJECT_ID('$($objName -replace "'","''")') AND index_id IN (0,1)"
                                $o.approxRows = $rc[0].r
                            }
                            # Proves the object can actually be read -- and, for a
                            # view, that any function it calls is reachable under
                            # ownership chaining. Returns no data by construction.
                            try {
                                [void](Invoke-Sql $ds $db "SELECT TOP (1) 1 AS ok FROM [$($objName -replace ']',']]')]" 20)
                                $o.readable = $true
                            } catch { $o.readable = $false; $o.readError = $_.Exception.Message.Split("`n")[0] }
                        }
                    } catch { $o.readError = $_.Exception.Message.Split("`n")[0] }
                    $dbInfo.objects += $o
                }
            }
            # varchar vs nvarchar decides whether Japanese needs a codepage step
            try {
                $dbInfo.textColumnTypes = Invoke-Sql $ds $db @"
SELECT t.name AS DataType, COUNT(*) AS Columns
FROM sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE t.name IN ('varchar','nvarchar','char','nchar','text','ntext')
GROUP BY t.name ORDER BY COUNT(*) DESC
"@
            } catch { }
            $inst.techsDatabases += $dbInfo

            $present = @($dbInfo.objects | Where-Object { $_.exists })
            $readable = @($present | Where-Object { $_.readable -eq $true })
            $wm = @($present | Where-Object { $_.hasTmStamp -or $_.hasUpdDt })
            Write-Host ("      objects found {0}/{1}, readable {2}, with watermark {3}" -f `
                $present.Count, $dbInfo.objects.Count, $readable.Count, $wm.Count)
        }
    } catch {
        $inst.error = $_.Exception.Message.Split("`n")[0]
        Note "instance $instName not reachable: $($inst.error)"
    }

    $report.instances += $inst
}

# ----------------------------------------------------------------- write ---
Section 'writing report'
$base = Join-Path $OutputDirectory ("techs-survey-{0}-{1}" -f $env:COMPUTERNAME, $stamp)
$jsonPath = "$base.json"
$txtPath  = "$base.txt"

$report | ConvertTo-Json -Depth 12 | Set-Content -Path $jsonPath -Encoding UTF8

$lines = New-Object System.Collections.ArrayList
[void]$lines.Add("TECHS site survey")
[void]$lines.Add("collected : $($report.collectedAt)")
[void]$lines.Add("machine   : $($report.machine.hostName)  $($report.machine.os)  PS $($report.machine.psVersion)")
[void]$lines.Add("user      : $($report.privilege.currentUser)  ($($report.privilege.accountType))  elevated=$($report.privilege.isElevated)  localAdmin=$($report.privilege.isLocalAdministratorsMember)")
[void]$lines.Add("")
[void]$lines.Add("residency : uptime=$([Math]::Round(($report.residency.uptimeSeconds)/3600.0,1))h  lastBoot=$($report.residency.lastBootTime)  laptop=$($report.residency.chassisIsLaptop)  battery=$($report.residency.batteryPresent)  interactiveSessions=$($report.residency.interactiveSessionCount)  techsRunning=$($report.residency.techsClientRunning)")
[void]$lines.Add("scheduler : service=$($report.scheduledTaskFeasibility.schedulerServiceStatus)  canEnumerateTasks=$($report.scheduledTaskFeasibility.canEnumerateTasks)  taskCount=$($report.scheduledTaskFeasibility.taskCount)")
[void]$lines.Add("")
[void]$lines.Add("proxy     : winHttpConfigured=$($report.proxy.winHttp.configured)  winInetEnabled=$($report.proxy.winInet.proxyEnabled)  pac=$($report.proxy.pacUrl)  envProxySet=$([bool]($report.proxy.envVars.HTTP_PROXY -or $report.proxy.envVars.HTTPS_PROXY))")
[void]$lines.Add("")
[void]$lines.Add("reachability (phone-home candidates):")
foreach ($rc in $report.reachability) {
    $rcStatus = $(if ($rc.failureStage) { "FAILED at $($rc.failureStage)" }
                  elseif ($rc.http -and $rc.http.expectedSuccess) { "OK (HTTP $($rc.http.statusCode))" }
                  elseif ($rc.http) { "HTTP $($rc.http.statusCode) (unexpected)" }
                  else { "unknown" })
    [void]$lines.Add("  $($rc.label) [$($rc.host)] : $rcStatus  $($rc.totalElapsedMs)ms  tls=$($rc.tls.protocol)  issuer=$($rc.tls.certIssuer)")
}
if ($report.techsClient -and $report.techsClient.installPath) {
    [void]$lines.Add("")
    [void]$lines.Add("TECHS client")
    [void]$lines.Add("  install : $($report.techsClient.installPath)")
    [void]$lines.Add("  version : $($report.techsClient.version)  (schema map built from $RE_VERSION; match=$($report.techsClient.versionMatchesReverseEngineering))")
    [void]$lines.Add("  SQL     : $($report.techsClient.dataSource) / $($report.techsClient.initialCatalog)")
    [void]$lines.Add("  AP      : $($report.techsClient.webServiceServer) $($report.techsClient.webServiceFolder):$($report.techsClient.siPort)")
    [void]$lines.Add("  EUC     : $($report.techsClient.eucServer) / $($report.techsClient.eucDatabase)")
    [void]$lines.Add("  stored plaintext password on this PC: $($report.techsClient.passwordPresentInPlaintext) (value NOT recorded)")
}
foreach ($i in $report.instances) {
    [void]$lines.Add("")
    [void]$lines.Add("instance  : $($i.instanceName)   dataSource=$($i.dataSource)")
    [void]$lines.Add("  network : tcp=$($i.network.tcpEnabled)  port=$($i.network.staticPort)/$($i.network.dynamicPort)  kind=$($i.network.portKind)")
    if ($i.connected) {
        [void]$lines.Add("  version : $($i.server.ProductVersion)  $($i.server.Edition)")
        [void]$lines.Add("  auth    : windowsAuthOnly=$($i.server.WindowsAuthOnly)  collation=$($i.server.ServerCollation)")
        [void]$lines.Add("  dbs     : " + (($i.databases | ForEach-Object { $_.name }) -join ', '))
        foreach ($d in $i.techsDatabases) {
            [void]$lines.Add("  -- $($d.database) --")
            foreach ($o in $d.objects) {
                $mark = $(if (-not $o.exists) { 'MISSING ' } elseif ($o.readable -eq $false) { 'UNREADABLE' } else { 'ok      ' })
                $wmk  = @(); if ($o.hasTmStamp) { $wmk += 'TmStamp' }; if ($o.hasUpdDt) { $wmk += 'UpdDt' }; if ($o.hasDelFlg) { $wmk += 'DelFlg' }
                [void]$lines.Add(("     {0} {1,-34} {2,-6} cols={3,-4} rows={4,-9} {5}" -f `
                    $mark, $o.name, $o.kind, $o.columnCount, $o.approxRows, ($wmk -join '+')))
                if ($o.readError) { [void]$lines.Add("               ! $($o.readError)") }
                if ($o.exists -and $o.businessKeyMissing.Count -gt 0) {
                    [void]$lines.Add("               ! business key missing: $($o.businessKeyMissing -join ',')")
                }
            }
            [void]$lines.Add("     text column types: " + (($d.textColumnTypes | ForEach-Object { "$($_.DataType)=$($_.Columns)" }) -join '  '))
        }
    } else { [void]$lines.Add("  NOT REACHABLE: $($i.error)") }
}
if ($report.notes.Count) {
    [void]$lines.Add(""); [void]$lines.Add("notes:")
    foreach ($n in $report.notes) { [void]$lines.Add("  - $n") }
}
$lines -join "`r`n" | Set-Content -Path $txtPath -Encoding UTF8

Write-Host ""
Write-Host "  $txtPath"  -ForegroundColor Green
Write-Host "  $jsonPath" -ForegroundColor Green
Write-Host ""
Write-Host "Schema metadata and counts only -- no 品番, prices or customer names." -ForegroundColor Cyan
Write-Host "Open the .txt and skim it before you leave the site." -ForegroundColor Cyan