<#
.SYNOPSIS
  Discover SQL Server instance connect strings on this Windows host.
.NOTES
  Reads Instance Names\SQL registry keys. Default instance -> COMPUTERNAME;
  named instances -> COMPUTERNAME\InstanceName.
#>
function Get-LocalSqlInstanceConnectNames {
    [CmdletBinding()]
    param(
        [string]$ComputerName = $env:COMPUTERNAME
    )

    $paths = @(
        'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
        'HKLM:\SOFTWARE\WOW6432Node\Microsoft\Microsoft SQL Server\Instance Names\SQL'
    )

    $found = @{}
    foreach ($path in $paths) {
        if (-not (Test-Path $path)) { continue }
        $reg = Get-ItemProperty $path -ErrorAction SilentlyContinue
        if (-not $reg) { continue }
        foreach ($prop in $reg.PSObject.Properties) {
            if ($prop.Name -match '^PS') { continue }
            $iname = [string]$prop.Name
            $connect = if ($iname -eq 'MSSQLSERVER') { $ComputerName } else { "$ComputerName\$iname" }
            $found[$connect] = [pscustomobject]@{
                InstanceName = $iname
                ConnectName  = $connect
                ServiceName  = if ($iname -eq 'MSSQLSERVER') { 'MSSQLSERVER' } else { "MSSQL`$$iname" }
            }
        }
    }

    return @($found.Values | Sort-Object ConnectName)
}

function Test-SqlInstanceReachable {
    param(
        [Parameter(Mandatory)][string]$ConnectName,
        [System.Management.Automation.PSCredential]$Credential
    )
    $query = "SET NOCOUNT ON; SELECT CAST(SERVERPROPERTY('ServerName') AS NVARCHAR(128));"
    $sqlArgs = @('-S', $ConnectName, '-Q', $query, '-W', '-h-1')
    if ($Credential) {
        $net = $Credential.GetNetworkCredential()
        $sqlArgs = @('-S', $ConnectName, '-U', $net.UserName, '-P', $net.Password, '-Q', $query, '-W', '-h-1')
    } else {
        $sqlArgs = @('-S', $ConnectName, '-E', '-Q', $query, '-W', '-h-1')
    }
    try {
        $out = & sqlcmd @sqlArgs 2>&1
        if ($LASTEXITCODE -ne 0) { return $null }
    } catch {
        return $null
    }
    $name = ($out | Where-Object { $_ -and $_ -notmatch 'rows affected' } | Select-Object -First 1)
    if ($name) { return [string]$name.Trim() }
    return $null
}

function Start-SqlInstanceServiceIfStopped {
    param([string]$ServiceName)
    if (-not $ServiceName) { return $false }
    $state = (sc.exe query $ServiceName 2>&1 | Select-String 'STATE').ToString()
    if ($state -match 'RUNNING') { return $true }
    if ($state -notmatch 'STOPPED') { return $false }
    Write-Host "Starting SQL service $ServiceName..." -ForegroundColor Yellow
    sc.exe start $ServiceName | Out-Null
    Start-Sleep -Seconds 8
    $state2 = (sc.exe query $ServiceName 2>&1 | Select-String 'STATE').ToString()
    return ($state2 -match 'RUNNING')
}

function Get-PilotInstanceConfig {
    param([string]$ConfigPath)
    if (-not (Test-Path $ConfigPath)) { return $null }
    return Get-Content $ConfigPath -Raw | ConvertFrom-Json
}
