#Requires -RunAsAdministrator
<#
.SYNOPSIS
    Enables Mixed Mode authentication and creates SQL login [scale] with blank password.
#>
param(
    [string]$InstanceName = 'SQLEXPRESS',
    [string]$SaPassword   = '1qaz@WSX',
    [string]$SqlScriptPath
)

$ErrorActionPreference = 'Stop'

function Get-SqlSetupRegistryPath {
    param([string]$InstanceName)
    $instancesRoot = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
    if (-not (Test-Path $instancesRoot)) { return $null }

    $instanceId = (Get-ItemProperty -Path $instancesRoot -Name $InstanceName -ErrorAction SilentlyContinue).$InstanceName
    if (-not $instanceId) { return $null }

    $serverKey = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$instanceId\MSSQLServer"
    if (Test-Path $serverKey) { return $serverKey }
    return $null
}

function Get-SqlCmdPath {
    $candidates = @(
        "${env:ProgramFiles}\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE",
        "${env:ProgramFiles(x86)}\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\SQLCMD.EXE",
        "${env:ProgramFiles}\Microsoft SQL Server\150\Tools\Binn\SQLCMD.EXE",
        "${env:ProgramFiles(x86)}\Microsoft SQL Server\150\Tools\Binn\SQLCMD.EXE",
        "${env:ProgramFiles}\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE"
    )
    foreach ($path in $candidates) {
        if (Test-Path $path) { return $path }
    }
    $found = Get-Command sqlcmd.exe -ErrorAction SilentlyContinue
    if ($found) { return $found.Source }
    return $null
}

function Wait-ForSqlConnection {
    param(
        [string]$Server,
        [string]$SqlCmd,
        [int]$TimeoutSec = 300
    )
    $sw = [Diagnostics.Stopwatch]::StartNew()
    while ($sw.Elapsed.TotalSeconds -lt $TimeoutSec) {
        & $SqlCmd -S $Server -E -Q "SELECT 1" -b 2>$null | Out-Null
        if ($LASTEXITCODE -eq 0) { return }
        Start-Sleep -Seconds 3
    }
    throw "Cannot connect to SQL Server '$Server' within $TimeoutSec seconds."
}

function Invoke-SqlScript {
    param(
        [string]$Server,
        [string]$SqlCmd,
        [string]$ScriptPath,
        [string]$SaPassword
    )

    & $SqlCmd -S $Server -U sa -P $SaPassword -b -f 65001 -i $ScriptPath
    if ($LASTEXITCODE -eq 0) { return }

    Write-Host "sa auth failed; retrying with Windows auth..."
    & $SqlCmd -S $Server -E -b -f 65001 -i $ScriptPath
    if ($LASTEXITCODE -eq 0) { return }

    throw "Failed to execute SQL script. Exit code: $LASTEXITCODE"
}

Write-Host "=== K_Recycle: Configure SQL Authentication ==="

$server = if ($InstanceName -eq 'MSSQLSERVER') { 'localhost' } else { "localhost\$InstanceName" }
$serviceName = if ($InstanceName -eq 'MSSQLSERVER') { 'MSSQLSERVER' } else { "MSSQL`$$InstanceName" }

if (-not $SqlScriptPath) {
    $SqlScriptPath = Join-Path $PSScriptRoot '..\sql\ConfigureAuthenticationAndScaleLogin.sql'
}
$SqlScriptPath = (Resolve-Path $SqlScriptPath).Path

$sqlCmd = Get-SqlCmdPath
if (-not $sqlCmd) {
    throw 'sqlcmd.exe not found. Install SQL Server Command Line Utilities or rerun after SQL Express setup.'
}

$regPath = Get-SqlSetupRegistryPath -InstanceName $InstanceName
if (-not $regPath) {
    throw "SQL instance '$InstanceName' registry path not found."
}

$loginMode = (Get-ItemProperty -Path $regPath -Name 'LoginMode' -ErrorAction SilentlyContinue).LoginMode
if ($loginMode -ne 2) {
    Write-Host "Enabling Mixed Mode authentication (SQL + Windows)..."
    Set-ItemProperty -Path $regPath -Name 'LoginMode' -Value 2

    $svc = Get-Service -Name $serviceName -ErrorAction SilentlyContinue
    if ($svc) {
        if ($svc.Status -eq 'Running') {
            Restart-Service -Name $serviceName -Force
        } else {
            Start-Service -Name $serviceName
        }
    }
} else {
    Write-Host "Mixed Mode authentication already enabled."
}

Wait-ForSqlConnection -Server $server -SqlCmd $sqlCmd
Write-Host "Running SQL script: $SqlScriptPath"
Invoke-SqlScript -Server $server -SqlCmd $sqlCmd -ScriptPath $SqlScriptPath -SaPassword $SaPassword

Write-Host "SQL login [scale] configured successfully."
exit 0
