<#
.SYNOPSIS
    Free Windows server backup: SQL Server (incl. Express) + folders, encrypted,
    uploaded off-site, with MonitorSpider backup monitoring built in.

.DESCRIPTION
    Backs up every online database of a SQL Server instance (BACKUP ... WITH
    CHECKSUM, then RESTORE VERIFYONLY), plus any folders you list, into an
    AES-256-encrypted split 7z set with encrypted headers, uploads the set over
    FTP / FTPS / SFTP (WinSCP .NET assembly), applies retention remotely and
    locally, and finally pings your MonitorSpider backup monitor with
    success/fail + bytes + duration - so you are alerted when a backup fails
    AND when it silently stops running.

    Sets are named <prefix>-yyyy-MM-dd-HHmm-weekly, or -monthly for the first
    run of each calendar month (kept longer by retention).

.NOTES
    Config:  backup-config.json next to this script by default - see
             windows-backup-config.example.json.
    Tools:   sqlcmd, 7-Zip (7za.exe) and WinSCP (WinSCPnet.dll + WinSCP.exe).
             Run preflight.ps1 first: it checks everything and, with -Install,
             downloads portable, hash-verified copies into .\tools - no
             system-wide installs needed.
    Run as:  a scheduled task under SYSTEM (SYSTEM needs backup rights on the
             SQL instance) or an admin account.
    From:    https://monitorspider.com/backup-kits/ - MIT licensed, no phoning
             home beyond the ping URL you configure.
#>
[CmdletBinding()]
param(
    [string]$ConfigPath = (Join-Path $PSScriptRoot 'backup-config.json')
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

if (-not (Test-Path $ConfigPath)) {
    throw "Config file not found: $ConfigPath. Copy windows-backup-config.example.json there and fill it in."
}
$config = Get-Content -Raw -Path $ConfigPath | ConvertFrom-Json

function Get-ConfigValue {
    param($Object, [string]$Name, $Default = $null)
    if ($null -ne $Object -and $Object.PSObject.Properties[$Name] -and $null -ne $Object.$Name) { return $Object.$Name }
    return $Default
}

$prefix  = Get-ConfigValue $config 'SetPrefix' 'server'
$stamp   = Get-Date
$tag     = if ($stamp.Day -le 7) { 'monthly' } else { 'weekly' }
$setName = '{0}-{1:yyyy-MM-dd-HHmm}-{2}' -f $prefix, $stamp, $tag
$startedAt = Get-Date

$stagingDir = Get-ConfigValue $config 'StagingDir' 'C:\server-backups'
$workDir    = Join-Path $stagingDir 'work'
$archiveDir = Join-Path $stagingDir 'archives'
$logDir     = Join-Path $stagingDir 'logs'
foreach ($dir in @($workDir, $archiveDir, $logDir)) {
    New-Item -ItemType Directory -Force -Path $dir | Out-Null
}

$failures = New-Object System.Collections.Generic.List[string]

function Write-Log {
    param([string]$Message)
    # Write-Host, not Write-Output: log lines must never leak into function
    # return values, and transcripts capture the host stream anyway.
    Write-Host ('[{0:HH:mm:ss}] {1}' -f (Get-Date), $Message)
}

# Resolve a tool: explicit config path > .\tools (preflight -Install puts them
# there) > PATH > well-known install locations.
function Resolve-Tool {
    param([string]$ConfigValue, [string[]]$Candidates, [string]$DisplayName)
    if ($ConfigValue -and (Test-Path $ConfigValue)) { return $ConfigValue }
    foreach ($c in $Candidates) {
        if ($c -and (Test-Path $c)) { return $c }
    }
    throw "$DisplayName not found. Run preflight.ps1 -Install, or set its path in $ConfigPath."
}

$toolsDir = Join-Path $PSScriptRoot 'tools'
$sevenZip = Resolve-Tool (Get-ConfigValue $config 'SevenZipPath') @(
    (Join-Path $toolsDir '7zip\7za.exe'),
    'C:\Program Files\7-Zip\7z.exe',
    'C:\Program Files (x86)\7-Zip\7z.exe'
) '7-Zip (7za.exe/7z.exe)'
$winScpDll = Resolve-Tool (Get-ConfigValue $config 'WinScpDllPath') @(
    (Join-Path $toolsDir 'winscp\WinSCPnet.dll'),
    'C:\Program Files (x86)\WinSCP\WinSCPnet.dll',
    'C:\Program Files\WinSCP\WinSCPnet.dll'
) 'WinSCP .NET assembly (WinSCPnet.dll)'
$sqlInstance = Get-ConfigValue $config 'SqlInstance' ''
$sqlcmdExe = $null
if ($sqlInstance) {
    $pathSqlcmd = (Get-Command sqlcmd -ErrorAction SilentlyContinue).Source
    $sqlcmdExe = Resolve-Tool (Get-ConfigValue $config 'SqlcmdPath') @(
        (Join-Path $toolsDir 'sqlcmd.exe'),
        $pathSqlcmd
    ) 'sqlcmd'
}

# ---- MonitorSpider ping -----------------------------------------------------
# Create a "Backup (push)" monitor at https://monitorspider.com/dashboard/ and
# paste its ping URL into the config. The ping goes out in the finally block,
# success or fail, so the monitor also catches this script dying mid-run
# (no ping at all -> "didn't run" alert after your expected interval + grace).
$pingUrl = Get-ConfigValue (Get-ConfigValue $config 'MonitorSpider') 'PingUrl' ''

function Send-MonitorSpiderPing {
    param([string]$Status, [string]$Message = '', [long]$Bytes = -1, [int]$DurationS = -1)
    if (-not $pingUrl) { return }
    $body = @{ status = $Status; set = $setName }
    if ($Message)        { $body.message    = $Message }
    if ($Bytes -ge 0)    { $body.bytes      = $Bytes }
    if ($DurationS -ge 0){ $body.duration_s = $DurationS }
    try {
        Invoke-RestMethod -Method Post -Uri $pingUrl -ContentType 'application/json' `
            -Body ($body | ConvertTo-Json) -TimeoutSec 30 | Out-Null
        Write-Log "MonitorSpider ping sent: $Status"
    }
    catch {
        # Never let the watchdog break the backup; the missed ping itself
        # becomes the alert on the MonitorSpider side.
        Write-Log "MonitorSpider ping FAILED: $_"
    }
}

function Invoke-SqlBackup {
    param([string]$Instance, [string[]]$ExcludeDatabases, [string]$OutputDir)

    # PS 5.1: native stderr + 2>&1 under EAP Stop throws NativeCommandError.
    $ErrorActionPreference = 'Continue'

    $excluded = @('master', 'model', 'msdb', 'tempdb') + $ExcludeDatabases
    $inList = ($excluded | ForEach-Object { "N'" + ($_ -replace "'", "''") + "'" }) -join ', '
    $query = "SET NOCOUNT ON; SELECT name FROM sys.databases " +
        "WHERE state_desc = N'ONLINE' AND name NOT IN ($inList) ORDER BY name;"

    # -C: trust the instance's self-signed certificate (sqlcmd/ODBC 18+
    # defaults to mandatory encryption and rejects it otherwise).
    $raw = @(& $sqlcmdExe -S $Instance -E -C -h -1 -W -b -Q $query 2>&1 | ForEach-Object { "$_" })
    if ($LASTEXITCODE -ne 0) {
        throw "Could not list databases on $Instance. sqlcmd said: $($raw -join ' | ')"
    }
    $databases = @($raw | Where-Object { $_ -and $_.Trim() } | ForEach-Object { $_.Trim() })
    Write-Log "Backing up $($databases.Count) databases from $Instance"

    foreach ($db in $databases) {
        $bakPath = Join-Path $OutputDir "$db.bak"
        # Express edition does not support WITH COMPRESSION; 7z compresses later.
        $backupQuery = "BACKUP DATABASE [$db] TO DISK = N'$bakPath' WITH INIT, CHECKSUM; " +
            "RESTORE VERIFYONLY FROM DISK = N'$bakPath' WITH CHECKSUM;"
        try {
            $bakOutput = @(& $sqlcmdExe -S $Instance -E -C -b -Q $backupQuery 2>&1 | ForEach-Object { "$_" })
            if ($LASTEXITCODE -ne 0) { throw "sqlcmd said: $($bakOutput -join ' | ')" }
            $size = (Get-Item $bakPath -ErrorAction Stop).Length
            if ($size -le 0) { throw 'backup file is empty' }
            Write-Log ("  {0}: {1:N1} MB, verified" -f $db, ($size / 1MB))
        }
        catch {
            $failures.Add("SQL backup failed for [$db]: $_")
            Write-Log "  $db FAILED: $_"
        }
    }
    return $databases.Count
}

function New-BackupArchive {
    param([string]$ArchiveBase, [string[]]$Sources, [int]$VolumeSizeMB, [string]$Password)

    $sevenZipArgs = @('a', '-t7z', "$ArchiveBase.7z") + $Sources +
        @("-v${VolumeSizeMB}m", '-mx=5', '-mhe=on', "-p$Password", '-ssw', '-bd', '-y')
    & $sevenZip @sevenZipArgs | Out-Null
    if ($LASTEXITCODE -gt 1) { throw "7-Zip failed with exit code $LASTEXITCODE." }

    $volumes = @(Get-ChildItem -Path "$ArchiveBase.7z.*" | Sort-Object Name)
    if ($volumes.Count -eq 0) { throw 'No archive volumes were produced.' }
    $totalGb = ($volumes | Measure-Object Length -Sum).Sum / 1GB
    Write-Log ('Archive: {0} volumes, {1:N2} GB total' -f $volumes.Count, $totalGb)
    return $volumes
}

function Get-RemoteSession {
    param($Upload)

    Add-Type -Path $winScpDll
    $sessionOptions = New-Object WinSCP.SessionOptions
    $protocol = Get-ConfigValue $Upload 'Protocol' 'Ftp'   # Ftp | Sftp
    $sessionOptions.Protocol = [WinSCP.Protocol]$protocol
    $sessionOptions.HostName = $Upload.HostName
    $sessionOptions.UserName = $Upload.UserName
    $sessionOptions.Password = $Upload.Password
    if ($protocol -eq 'Ftp') {
        # FtpSecure: None | Explicit | Implicit (Explicit = FTPS on port 21)
        $sessionOptions.FtpSecure = [WinSCP.FtpSecure](Get-ConfigValue $Upload 'FtpSecure' 'Explicit')
        # Shared hosts often present a TLS cert for the server's hostname, not
        # your domain; pin its fingerprint (shown by the WinSCP GUI) to use
        # FTPS anyway.
        $tlsFp = Get-ConfigValue $Upload 'TlsCertificateFingerprint' ''
        if ($tlsFp) { $sessionOptions.TlsHostCertificateFingerprint = $tlsFp }
    }
    if ($protocol -eq 'Sftp') {
        $sshFp = Get-ConfigValue $Upload 'SshHostKeyFingerprint' ''
        if (-not $sshFp) {
            throw "SFTP needs Upload.SshHostKeyFingerprint (e.g. ""ssh-ed25519 255 xx:xx:..."" - shown by the WinSCP GUI on first connect, or run: WinSCP.com /command ""open sftp://user@host"" )."
        }
        $sessionOptions.SshHostKeyFingerprint = $sshFp
    }
    $session = New-Object WinSCP.Session
    $session.Open($sessionOptions)
    return $session
}

function Remove-ExpiredRemoteSets {
    param($Session, [string]$RemoteDir, [int]$WeeklyDays, [int]$MonthlyDays, [string]$CurrentSet)

    $pattern = '^(' + [regex]::Escape($prefix) + '-(\d{4}-\d{2}-\d{2})-\d{4}-(weekly|monthly))\.'
    $remoteFiles = $Session.ListDirectory($RemoteDir).Files | Where-Object { -not $_.IsDirectory }

    $sets = @{}
    foreach ($file in $remoteFiles) {
        if ($file.Name -match $pattern) {
            if (-not $sets.ContainsKey($Matches[1])) {
                $sets[$Matches[1]] = [pscustomobject]@{
                    Date  = [datetime]::ParseExact($Matches[2], 'yyyy-MM-dd', $null)
                    Tag   = $Matches[3]
                    Files = New-Object System.Collections.Generic.List[string]
                }
            }
            $sets[$Matches[1]].Files.Add($file.Name)
        }
    }

    $now = Get-Date
    foreach ($name in $sets.Keys) {
        if ($name -eq $CurrentSet) { continue }
        $set = $sets[$name]
        $maxAgeDays = if ($set.Tag -eq 'monthly') { $MonthlyDays } else { $WeeklyDays }
        if ($set.Date -lt $now.AddDays(-$maxAgeDays)) {
            Write-Log "Retention: deleting remote set $name ($($set.Files.Count) files)"
            foreach ($fileName in $set.Files) {
                $remotePath = [WinSCP.RemotePath]::Combine($RemoteDir, $fileName)
                $Session.RemoveFiles([WinSCP.RemotePath]::EscapeFileMask($remotePath)).Check()
            }
        }
    }
}

function Remove-ExpiredLocalSets {
    param([string]$ArchiveDir, [int]$KeepSets)

    $prefixes = @(Get-ChildItem -Path $ArchiveDir -File |
        Where-Object { $_.Name -match ('^(' + [regex]::Escape($prefix) + '-.+-(weekly|monthly))\.') } |
        ForEach-Object { $Matches[1] } | Sort-Object -Unique -Descending)
    foreach ($p in ($prefixes | Select-Object -Skip $KeepSets)) {
        Write-Log "Retention: deleting local set $p"
        Remove-Item -Path (Join-Path $ArchiveDir "$p.*") -Force
    }
}

# --- Main -------------------------------------------------------------------
Start-Transcript -Path (Join-Path $logDir "$setName.log") | Out-Null
$totalBytes = [long]-1
$dbCount = 0
try {
    Write-Log "Starting backup set $setName"
    Send-MonitorSpiderPing -Status 'start'
    $freeGb = (Get-PSDrive -Name ($stagingDir[0])).Free / 1GB
    Write-Log ('Free space on staging drive: {0:N1} GB' -f $freeGb)

    # Fresh work area every run.
    Get-ChildItem -Path $workDir | Remove-Item -Recurse -Force

    $sources = @()
    if ($sqlInstance) {
        $sqlDir = Join-Path $workDir 'sql'
        New-Item -ItemType Directory -Force -Path $sqlDir | Out-Null
        $dbCount = Invoke-SqlBackup -Instance $sqlInstance `
            -ExcludeDatabases @(Get-ConfigValue $config 'ExcludeDatabases' @()) -OutputDir $sqlDir
        $sources += "$workDir\*"
    }
    foreach ($folder in @(Get-ConfigValue $config 'Folders' @())) {
        if (Test-Path $folder) { $sources += $folder }
        else { $failures.Add("Configured folder not found: $folder") }
    }
    if ($sources.Count -eq 0) { throw 'Nothing to back up: set SqlInstance and/or Folders in the config.' }

    $archiveBase = Join-Path $archiveDir $setName
    $volumes = New-BackupArchive -ArchiveBase $archiveBase -Sources $sources `
        -VolumeSizeMB (Get-ConfigValue $config 'VolumeSizeMB' 500) `
        -Password $config.ArchivePassword
    $totalBytes = [long](($volumes | Measure-Object Length -Sum).Sum)

    $manifestPath = "$archiveBase.sha256"
    $volumes | Get-FileHash -Algorithm SHA256 |
        ForEach-Object { '{0} *{1}' -f $_.Hash, (Split-Path -Leaf $_.Path) } |
        Set-Content -Path $manifestPath -Encoding Ascii

    $upload = $config.Upload
    Write-Log "Uploading to $($upload.HostName) ($(Get-ConfigValue $upload 'Protocol' 'Ftp'))"
    $session = Get-RemoteSession -Upload $upload
    try {
        foreach ($file in ($volumes + (Get-Item $manifestPath))) {
            $remotePath = [WinSCP.RemotePath]::Combine($upload.RemoteDir, $file.Name)
            $session.PutFiles([WinSCP.RemotePath]::EscapeFileMask($file.FullName), $remotePath).Check()
            Write-Log "  uploaded $($file.Name)"
        }
        $retention = Get-ConfigValue $config 'Retention'
        Remove-ExpiredRemoteSets -Session $session -RemoteDir $upload.RemoteDir `
            -WeeklyDays (Get-ConfigValue $retention 'WeeklyDays' 28) `
            -MonthlyDays (Get-ConfigValue $retention 'MonthlyDays' 180) `
            -CurrentSet $setName
    }
    finally { $session.Dispose() }

    Remove-ExpiredLocalSets -ArchiveDir $archiveDir `
        -KeepSets (Get-ConfigValue (Get-ConfigValue $config 'Retention') 'KeepLocalSets' 1)
    Get-ChildItem -Path $logDir -Filter '*.log' |
        Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-180) } | Remove-Item -Force
}
catch {
    $failures.Add("Fatal: $_")
}
finally {
    $durationS = [int]((Get-Date) - $startedAt).TotalSeconds

    if ($failures.Count -gt 0) {
        Write-Log "Backup finished with $($failures.Count) error(s):"
        $failures | ForEach-Object { Write-Log "  $_" }
        Send-MonitorSpiderPing -Status 'fail' -DurationS $durationS `
            -Message (($failures | Select-Object -First 3) -join ' | ')
    }
    else {
        Write-Log 'Backup completed successfully.'
        $okMsg = if ($dbCount -gt 0) { "$dbCount databases verified" } else { 'files archived' }
        Send-MonitorSpiderPing -Status 'success' -Bytes $totalBytes -DurationS $durationS `
            -Message "$okMsg; uploaded to $($config.Upload.HostName)"
    }
    Stop-Transcript | Out-Null
}

exit ([int]($failures.Count -gt 0))