Set-DbCredentials.ps1

<#
.SYNOPSIS
    Configures the SQL Server credentials in appsettings.json for TaskMonitor on a target system.

.DESCRIPTION
    Updates the Database section of the TaskMonitor appsettings.json with the server,
    database, and authentication details for the system being installed. The rest of
    the configuration (sync behaviour, scheduler, workflow toggles) is preserved.

    Authentication modes:
      - Windows integrated (SSPI):  -UseIntegratedSecurity  (recommended; no stored password)
      - SQL authentication:         -UseSqlAuthentication -UserName <login> -Password <pwd>
                                    (leave -Password empty to defer it to the runtime
                                     TASKMONITOR_DB_PASSWORD environment variable)

.EXAMPLE
    # Windows auth against a local default instance
    .\Set-DbCredentials.ps1 -Server ".\SQLEXPRESS" -Database "PVSQLDBCN" -UseIntegratedSecurity -TestConnection

.EXAMPLE
    # SQL login, password stored in the local (gitignored) config
    .\Set-DbCredentials.ps1 -Server "10.0.0.5" -Database "PVSQLDBCN" -UseSqlAuthentication -UserName "sa" -Password "YourPassword123" -ActivationApproved $true -TestConnection

.EXAMPLE
    # SQL login, password supplied at runtime via environment variable
    .\Set-DbCredentials.ps1 -Server "lentsrv" -Database "PVSQLDBCN" -UseSqlAuthentication -UserName "sa" -UseEnvironmentPassword -ActivationApproved $false

.EXAMPLE
    # Point at a published install's config instead of the source tree
    .\Set-DbCredentials.ps1 -SettingsPath "D:\TaskMonitor\appsettings.json" -Server "db1" -Database "PVSQLDBCN" -UseSqlAuthentication -UserName "sa" -Password "secret"
#>
[CmdletBinding()]
param(
    [Parameter(Mandatory = $false)]
    [string]$SettingsPath = "",

    [Parameter(Mandatory = $true)]
    [string]$Server,

    [Parameter(Mandatory = $true)]
    [string]$Database,

    [Parameter(Mandatory = $false)]
    [switch]$UseIntegratedSecurity,

    [Parameter(Mandatory = $false)]
    [switch]$UseSqlAuthentication,

    [Parameter(Mandatory = $false)]
    [string]$UserName = "",

    [Parameter(Mandatory = $false)]
    [string]$Password = "",

    [Parameter(Mandatory = $false)]
    [switch]$UseEnvironmentPassword,

    [Parameter(Mandatory = $false)]
    [bool]$Encrypt = $true,

    [Parameter(Mandatory = $false)]
    [bool]$TrustServerCertificate = $true,

    [Parameter(Mandatory = $false)]
    [nullable[bool]]$ActivationApproved,

    [Parameter(Mandatory = $false)]
    [switch]$TestConnection
)

$ErrorActionPreference = "Stop"

function Resolve-SettingsPath {
    param([string]$Path)
    if (-not [string]::IsNullOrWhiteSpace($Path)) { return $Path }
    $scriptRoot = if ([string]::IsNullOrWhiteSpace($PSScriptRoot)) { (Get-Location).Path } else { $PSScriptRoot }
    return Join-Path $scriptRoot "TaskMonitor.Desktop\appsettings.json"
}

function Write-Result {
    param([string]$Message, [string]$Color = "Green")
    Write-Host $Message -ForegroundColor $Color
}

# ---------- Validate parameters ----------
if ($UseIntegratedSecurity -and $UseSqlAuthentication) {
    throw "Choose only one of -UseIntegratedSecurity or -UseSqlAuthentication."
}
if (-not $UseIntegratedSecurity -and -not $UseSqlAuthentication) {
    throw "Specify an authentication mode: -UseIntegratedSecurity or -UseSqlAuthentication."
}
if ($UseSqlAuthentication -and [string]::IsNullOrWhiteSpace($UserName)) {
    throw "SQL authentication requires -UserName."
}
if ($UseSqlAuthentication -and $UseEnvironmentPassword) {
    $Password = $env:TASKMONITOR_DB_PASSWORD
    if ([string]::IsNullOrWhiteSpace($Password)) {
        throw "TASKMONITOR_DB_PASSWORD is not set in this session. Set it before running, or supply -Password."
    }
}

$settingsPath = Resolve-SettingsPath -Path $SettingsPath
if (-not (Test-Path -LiteralPath $settingsPath)) {
    Write-Result "Settings file not found; creating a new one at: $settingsPath" "Yellow"
}

# ---------- Load or create settings ----------
$settings = @{}
if (Test-Path -LiteralPath $settingsPath) {
    $raw = Get-Content -LiteralPath $settingsPath -Raw
    if (-not [string]::IsNullOrWhiteSpace($raw)) {
        $settings = $raw | ConvertFrom-Json
    }
}
if ($null -eq $settings.Database) { $settings | Add-Member -NotePropertyName "Database" -NotePropertyValue @{} }
if ($null -eq $settings.TaskMonitor) { $settings | Add-Member -NotePropertyName "TaskMonitor" -NotePropertyValue @{} }

# ---------- Apply database credentials ----------
$settings.Database.Server = $Server
$settings.Database.DatabaseName = $Database
$settings.Database.UseIntegratedSecurity = [bool]$UseIntegratedSecurity
$settings.Database.Encrypt = $Encrypt
$settings.Database.TrustServerCertificate = $TrustServerCertificate

if ($UseIntegratedSecurity) {
    $settings.Database.UserName = ""
    $settings.Database.Password = ""
}
else {
    $settings.Database.UserName = $UserName
    # A password supplied here is stored in the local (gitignored) config only.
    # The app never writes it back on save and prefers TASKMONITOR_DB_PASSWORD at runtime.
    $settings.Database.Password = $Password
}

# Database mode and activation are applied only when explicitly requested.
$settings.Mode = "Database"
if ($null -ne $ActivationApproved) {
    $settings.ActivationApproved = $ActivationApproved
}

# ---------- Write settings ----------
$json = $settings | ConvertTo-Json -Depth 10
$parent = Split-Path -Path $settingsPath -Parent
if (-not (Test-Path -LiteralPath $parent)) { New-Item -ItemType Directory -Path $parent -Force | Out-Null }
Set-Content -LiteralPath $settingsPath -Value $json -Encoding UTF8

$authText = if ($UseIntegratedSecurity) { "Windows integrated security (SSPI)" }
            elseif ([string]::IsNullOrWhiteSpace($Password)) { "SQL authentication (password via TASKMONITOR_DB_PASSWORD)" }
            else { "SQL authentication (password stored locally)" }

Write-Result "TaskMonitor database configuration updated:"
Write-Result "  Settings file : $settingsPath"
Write-Result "  Server        : $Server"
Write-Result "  Database      : $Database"
Write-Result "  Authentication: $authText"
Write-Result "  Encrypt       : $Encrypt"
Write-Result "  Trust cert    : $TrustServerCertificate"
if ($null -ne $ActivationApproved) { Write-Result "  Approval      : $ActivationApproved" }

# ---------- Optional connection test ----------
if ($TestConnection) {
    Write-Result "" 
    Write-Result "Testing SQL Server connection..." -Color "Yellow"
    $connectionString = "Server=$Server;Database=$Database;TrustServerCertificate=$TrustServerCertificate;Encrypt=$Encrypt;Connect Timeout=8"
    if ($UseIntegratedSecurity) {
        $connectionString += ";Integrated Security=True"
    }
    else {
        $connectionString += ";User ID=$UserName;Password=$Password"
    }

    try {
        $connection = New-Object System.Data.SqlClient.SqlConnection $connectionString
        $connection.Open()
        Write-Result "Connection successful: $($connection.DataSource) / $($connection.Database) / SQL Server $($connection.ServerVersion)" -Color "Green"
        $connection.Close()
    }
    catch {
        Write-Result "Connection FAILED: $($_.Exception.Message)" -Color "Red"
        exit 1
    }
}