<# 
    AdHoc-Backup.ps1
#>

#Requires -Version 5.1

param(
    [string]$Config,
    [switch]$Quiet
)

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

try {
    [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 -bor [Net.SecurityProtocolType]::Tls13
} catch {
    try { [Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12 } catch {}
}
#region ============================= Percorsi script & conf =========================

$ScriptPath = $MyInvocation.MyCommand.Path
$ScriptDir  = Split-Path -Parent $ScriptPath
$MailScript = Join-Path $ScriptDir 'send-mail.ps1'

# Layout dell'installer GUI (se presente): .\bin\{RClone,7Zip,conf}
$InstallerBinRoot   = Join-Path $ScriptDir 'bin'
$InstallerConfDir   = Join-Path $InstallerBinRoot 'conf'
$InstallerRcloneExe = Join-Path $InstallerBinRoot 'RClone\rclone.exe'
$Installer7zExe     = Join-Path $InstallerBinRoot '7Zip\7z.exe'
$Installer7zrExe    = Join-Path $InstallerBinRoot '7Zip\7zr.exe'
$InstallerRcloneConf= Join-Path $InstallerConfDir 'rclone.conf'

# Se passato -Config usa quello, altrimenti priorità a .\bin\conf\backup.conf, poi .\backup.conf
if ($PSBoundParameters.ContainsKey('Config') -and -not [string]::IsNullOrWhiteSpace($Config)) {
    try {
        $ConfPath = (Resolve-Path -LiteralPath $Config).Path
    } catch {
        throw "Il file di configurazione specificato con -Config non esiste: $Config"
    }
} elseif (Test-Path -LiteralPath (Join-Path $InstallerConfDir 'backup.conf')) {
    $ConfPath = Join-Path $InstallerConfDir 'backup.conf'
} else {
    $ConfPath = Join-Path $ScriptDir 'backup.conf'
}

#endregion ==========================================================================


#region ============================= Config loader =================================

function New-BackupConfTemplate {
    param([string]$Path)

    $tpl = @"
# backup.conf - Configurazione backup (esempi)
BackupRoot=C:\Backups_AdHoc
# Retention per logs e _sql_stage
RetentionDays=7

# SORGENTI FILE (separa con |). Esempio:
#ArchiveSources=C:\Zucchetti\ahr90|D:\Dati\Export|\\nas01\vol1\archivi
ArchiveSources=C:\Zucchetti\ahr90

# BACKUP FILE / SQL
EnableFileBackup=true
EnableSqlBackup=true
SqlInstance=localhost\SQLEXPRESS
SqlUseWindowsAuth=true
SqlUser=sa
SqlPassword=YourPassword!
DbInclude=
DbExclude=master|model|msdb|tempdb
SqlCompressStage=true
SqlDropBakAfterZip=true

# 7-ZIP
SevenZipCompressionLevel=3

# RCLONE UPLOAD
EnableRcloneUpload=true
RcloneRemoteDest=Backups_AdHoc:Backups/%COMPUTERNAME%
RcloneBwl=
RcloneExtraArgs=

# CONSERVAZIONE LOCALE
# true  = mantieni copie locali (sposta in \Files e \Databases)
# false = non mantenere copie locali (cancella gli archivi dopo l'upload o a fine job)
KeepLocalArchives=true

# RETENTION
LocalRetentionDaysFiles=14
LocalRetentionDaysDb=30
RemoteRetentionDays=60

# EMAIL
MailEnabled=true
MailSmtpHost=relay.poloinformatico.it
MailSmtpPort=587
MailUseAuth=true
MailUser=username@example.com
MailPassword=PASSWORD
MailFrom=username@example.com
MailTo=it@poloinformatico.it
MailSubjectPref=[BACKUP ADHOC] 
"@

    $tpl | Out-File -LiteralPath $Path -Encoding UTF8 -Force
}

function ConvertTo-Bool {
    param([string]$s)
    if ($null -eq $s) { return $false }
    switch ($s.Trim().ToLowerInvariant()) {
        '1' { return $true }
        'true' { return $true }
        'yes' { return $true }
        'y' { return $true }
        default { return $false }
    }
}

function ConvertTo-Int  {
    param([string]$s,[int]$default=0)
    try {
        if ([string]::IsNullOrWhiteSpace($s)) { $default } else { [int]$s.Trim() }
    } catch { $default }
}

function Split-List {
    param([string]$s)
    if ([string]::IsNullOrWhiteSpace($s)) { @() } else { @($s -split '\|',0 | ForEach-Object { $_.Trim() } | Where-Object { $_ }) }
}

function Expand-Env {
    param([string]$s)
    if ($null -eq $s) { '' } else { [Environment]::ExpandEnvironmentVariables($s) }
}

function Load-Config {
    param([string]$Path)
    if (-not (Test-Path -LiteralPath $Path)) {
        New-BackupConfTemplate -Path $Path
        throw "File di configurazione non trovato. Ho creato un template: $Path. Compilalo e riesegui lo script."
    }

    $map = @{}
    Get-Content -LiteralPath $Path | ForEach-Object {
        $line = $_.Trim()
        if ($line.Length -eq 0) { return }
        if ($line.StartsWith('#') -or $line.StartsWith(';')) { return }
        $idx = $line.IndexOf('=')
        if ($idx -lt 1) { return }
        $key = $line.Substring(0, $idx).Trim()
        $val = $line.Substring( $idx + 1).Trim()
        $val = ($val -replace '[\s]+#.*$', '').Trim()
        if (($val.StartsWith('"') -and $val.EndsWith('"')) -or ($val.StartsWith("'") -and $val.EndsWith("'"))) {
            $val = $val.Substring(1, $val.Length - 2)
        }
        $map[$key] = $val
    }

    $script:BackupRoot    = Expand-Env ($map['BackupRoot']); if ([string]::IsNullOrWhiteSpace($script:BackupRoot)) { throw "Parametro obbligatorio mancante: BackupRoot" }
    $script:RetentionDays = ConvertTo-Int ($map['RetentionDays'])

    $script:ArchiveSources = Split-List ($map['ArchiveSources'])

    $script:EnableFileBackup   = if ($map.ContainsKey('EnableFileBackup')) { ConvertTo-Bool ($map['EnableFileBackup']) } else { $true }
    $script:EnableSqlBackup    = ConvertTo-Bool ($map['EnableSqlBackup'])
    $script:SqlInstance        = Expand-Env ($map['SqlInstance'])
    $script:SqlUseWindowsAuth  = ConvertTo-Bool ($map['SqlUseWindowsAuth'])
    $script:SqlUser            = $map['SqlUser']
    $script:SqlPassword        = $map['SqlPassword']
    $script:DbInclude          = Split-List ($map['DbInclude'])
    $script:DbExclude          = if ($map.ContainsKey('DbExclude')) { Split-List ($map['DbExclude']) } else { @('master','model','msdb','tempdb') }
    $script:SqlCompressStage   = ConvertTo-Bool ($map['SqlCompressStage'])
    $script:SqlDropBakAfterZip = ConvertTo-Bool ($map['SqlDropBakAfterZip'])

    if ($EnableSqlBackup) {
        if ([string]::IsNullOrWhiteSpace($SqlInstance)) { throw "SqlInstance obbligatorio quando EnableSqlBackup=true" }
        if (-not $SqlUseWindowsAuth -and ([string]::IsNullOrWhiteSpace($SqlUser) -or [string]::IsNullOrWhiteSpace($SqlPassword))) {
            throw "SqlUser/SqlPassword obbligatori quando SqlUseWindowsAuth=false"
        }
    }

    $script:SevenZipCompressionLevel = ConvertTo-Int ($map['SevenZipCompressionLevel'])

    $script:EnableRcloneUpload = if ($map.ContainsKey('EnableRcloneUpload')) { ConvertTo-Bool ($map['EnableRcloneUpload']) } else { $true }
    $script:RcloneRemoteDest = Expand-Env ($map['RcloneRemoteDest'])
    $script:RcloneBwl        = Expand-Env ($map['RcloneBwl'])
    $script:RcloneExtraArgs  = Split-List ($map['RcloneExtraArgs'])

    # Conservazione locale
    $script:KeepLocalArchives = if ($map.ContainsKey('KeepLocalArchives')) { ConvertTo-Bool ($map['KeepLocalArchives']) } else { $true }

    # Retention knobs
    $script:LocalRetentionDaysFiles = if ($map.ContainsKey('LocalRetentionDaysFiles')) { ConvertTo-Int ($map['LocalRetentionDaysFiles']) } else { 14 }
    $script:LocalRetentionDaysDb    = if ($map.ContainsKey('LocalRetentionDaysDb'))    { ConvertTo-Int ($map['LocalRetentionDaysDb']) }    else { 30 }
    $script:RemoteRetentionDays     = if ($map.ContainsKey('RemoteRetentionDays'))     { ConvertTo-Int ($map['RemoteRetentionDays']) }     else { -1 }

    $script:MailEnabled     = ConvertTo-Bool ($map['MailEnabled'])
    $script:MailSmtpHost    = $map['MailSmtpHost']
    $script:MailSmtpPort    = ConvertTo-Int ($map['MailSmtpPort'], 587)
    $script:MailUseAuth     = ConvertTo-Bool ($map['MailUseAuth'])
    $script:MailUser        = $map['MailUser']
    $script:MailPassword    = $map['MailPassword']
    $script:MailFrom        = $map['MailFrom']
    $script:MailTo          = Split-List ($map['MailTo'])
    $script:MailSubjectPref = $map['MailSubjectPref']
}
Load-Config -Path $ConfPath

#endregion ==========================================================================


#region ============================= Paths, Globals ================================

function Now-IT { (Get-Date).ToString('dd-MM-yyyy HH:mm:ss') }

$DateStamp = (Get-Date).ToString('dd-MM-yyyy_HHmmss')
$HostName  = $env:COMPUTERNAME

$Paths = [ordered]@{
    Root       = $BackupRoot
    Bin        = Join-Path $BackupRoot 'Bin'
    Bin7z      = Join-Path $BackupRoot 'Bin\7zip'
    BinRclone  = Join-Path $BackupRoot 'Bin\RClone'
    Logs       = Join-Path $BackupRoot 'logs'
    Out        = Join-Path $BackupRoot 'out'
    SqlStage   = Join-Path $BackupRoot '_sql_stage'
    StoreFiles = Join-Path $BackupRoot 'Files'
    StoreDb    = Join-Path $BackupRoot 'Databases'
}

$Files = [ordered]@{
    Log         = Join-Path $Paths.Logs ("backup_$DateStamp.log")
    SevenZipExe = Join-Path $Paths.Bin7z '7z.exe'
    SevenZip7zr = Join-Path $Paths.Bin7z '7zr.exe'
    RcloneExe   = Join-Path $Paths.BinRclone 'rclone.exe'
    RcloneZip   = Join-Path $Paths.BinRclone 'rclone-current-windows-amd64.zip'
    RcloneConf  = Join-Path $Paths.BinRclone 'rclone.conf'
}

#endregion ==========================================================================# Preferisci gli strumenti nel layout dell'installer GUI, se presenti
if (Test-Path -LiteralPath $Installer7zExe) { $Files.SevenZipExe = $Installer7zExe }
elseif (Test-Path -LiteralPath $Installer7zrExe) { $Files.SevenZip7zr = $Installer7zrExe }

if (Test-Path -LiteralPath $InstallerRcloneExe) { $Files.RcloneExe = $InstallerRcloneExe }

# rclone.conf: prima quello in .\bin\conf, altrimenti quello sotto BackupRoot\Bin
if (Test-Path -LiteralPath $InstallerRcloneConf) { $Files.RcloneConf = $InstallerRcloneConf }


#region ============================= Utils & Logging ===============================

function Ensure-Dir {
    param([string]$Path)
    if (-not (Test-Path -LiteralPath $Path)) {
        New-Item -ItemType Directory -Path $Path -Force | Out-Null
    }
}

function Write-Log {
    param(
        [ValidateSet('INFO','WARN','ERROR')][string]$Level,
        [string]$Message
    )
    $line = "[{0}] [{1}] {2}" -f (Now-IT), $Level, $Message
    if (-not $Quiet) { Write-Host $line }
    
    # Usa SEMPRE lo scope di script per il file log
    Add-Content -LiteralPath $script:Files.Log -Value $line
}

function Download-File {
    param(
        [Parameter(Mandatory=$true)][string]$Url,
        [Parameter(Mandatory=$true)][string]$Destination
    )
    Write-Log -Level INFO -Message "Scarico: $Url -> $Destination"
    try {
        $wc = New-Object System.Net.WebClient
        $wc.DownloadFile($Url, $Destination)
    } catch {
        throw "Download fallito: $Url - $_"
    }
    if (-not (Test-Path -LiteralPath $Destination)) {
        throw "Download non riuscito (manca $Destination)"
    }
}

#endregion ==========================================================================


#region ============================= Tooling: 7-Zip ================================

$SevenZipPortableUrl = 'https://www.7-zip.org/a/7zr.exe'

function Ensure-7Zip {
    Ensure-Dir $Paths.Bin7z
    if (Test-Path -LiteralPath $Files.SevenZipExe) {
        Write-Log INFO "7-Zip: $($Files.SevenZipExe)"
        return $Files.SevenZipExe
    }
    if (Test-Path -LiteralPath $Files.SevenZip7zr) {
        Write-Log INFO "7zr: $($Files.SevenZip7zr)"
        return $Files.SevenZip7zr
    }
    Write-Log INFO "Scarico 7zr.exe..."
    Download-File -Url $SevenZipPortableUrl -Destination $Files.SevenZip7zr
    if (Test-Path -LiteralPath $Files.SevenZip7zr) { return $Files.SevenZip7zr }
    throw "Impossibile preparare 7-Zip in $($Paths.Bin7z)."
}

function Invoke-7ZipArchive {
    param(
        [Parameter(Mandatory=$true)][string]$ArchivePath,
        [Parameter(Mandatory=$true)][string[]]$InputPaths
    )

    $exe = Ensure-7Zip

    # Argomenti non-interattivi: -y (assume Yes), -bd (no progress), -bso0/-bsp0 (silenzio), -bse1 (errori)
    $a7zCommon = @('-y','-bd','-bso0','-bsp0','-bse1')

    if ($exe -like '*\7zr.exe') {
        $a7z = @('a',$ArchivePath) + $InputPaths + @("-mx=$SevenZipCompressionLevel") + $a7zCommon
    } else {
        $a7z = @('a','-t7z',"-mx=$SevenZipCompressionLevel",'-mmt=on','-r',$ArchivePath) + $InputPaths + $a7zCommon
    }

    Write-Log INFO "7-Zip cmd: `"$exe`" $($a7z -join ' ')"

    & $exe @a7z
    $ec = $LASTEXITCODE

    switch ($ec) {
        0 { Write-Log INFO "7-Zip OK" }
        1 { Write-Log WARN "7-Zip WARNING (ExitCode=1)" }
        default { throw "Errore compressione (ExitCode=$ec)" }
    }

    if (-not (Test-Path -LiteralPath $ArchivePath)) { throw "Archivio non creato: $ArchivePath" }
    return $ArchivePath
}

#endregion ==========================================================================


#region ============================= Tooling: rclone ================================

function Ensure-Rclone {
    Ensure-Dir $Paths.BinRclone
    if (Test-Path -LiteralPath $Files.RcloneExe) {
        Write-Log INFO "rclone: $($Files.RcloneExe)"
        return $Files.RcloneExe
    }
    $url='https://downloads.rclone.org/rclone-current-windows-amd64.zip'
    Write-Log INFO "Scarico rclone..."
    Download-File -Url $url -Destination $Files.RcloneZip
    try { Add-Type -AssemblyName System.IO.Compression.FileSystem | Out-Null } catch {}
    [System.IO.Compression.ZipFile]::ExtractToDirectory($Files.RcloneZip, $Paths.BinRclone)
    $found = Get-ChildItem -LiteralPath $Paths.BinRclone -Recurse -Filter 'rclone.exe' | Select-Object -First 1
    if (-not $found) { throw "rclone.exe non trovato nello zip." }
    Copy-Item -LiteralPath $found.FullName -Destination $Files.RcloneExe -Force
    Get-ChildItem -LiteralPath $Paths.BinRclone -Directory | ForEach-Object { Remove-Item $_.FullName -Recurse -Force }
    Write-Log INFO "rclone pronto: $($Files.RcloneExe)"
    return $Files.RcloneExe
}

function Ensure-RcloneConfigOrLaunch {
    if (-not (Test-Path -LiteralPath $Files.RcloneConf)) {
        Write-Log WARN "rclone.conf assente. Apro 'rclone config' in una nuova finestra..."
        $rclone = Ensure-Rclone
        $cmd = " & '$rclone' --config '$($Files.RcloneConf)' config "
        Start-Process -FilePath "powershell.exe" -ArgumentList "-NoExit","-Command",$cmd -WindowStyle Normal | Out-Null
        throw "Completa la configurazione nella finestra aperta, poi riesegui lo script."
    }
}

function Invoke-RcloneCopyTo {
    param([string]$LocalFile,[string]$RemoteDest)
    $rclone = Ensure-Rclone
    $rArgs = @('copyto',$LocalFile,$RemoteDest,'--config',$Files.RcloneConf,'--transfers','4','--checkers','8','--retries','3','--low-level-retries','10','--stats','10s')
    if ($RcloneBwl) { $rArgs += @('--bwlimit',$RcloneBwl) }
    if ($RcloneExtraArgs -and $RcloneExtraArgs.Count -gt 0) { $rArgs += $RcloneExtraArgs }
    Write-Log INFO "rclone copyto `"$LocalFile`" -> `"$RemoteDest`""
    $p = Start-Process -FilePath $rclone -ArgumentList $rArgs -PassThru -Wait -NoNewWindow
    if ($p.ExitCode -ne 0) { throw "rclone copyto fallito (ExitCode=$($p.ExitCode)) per $LocalFile" }
}

function Apply-RemoteRetention {
    param([int]$Days)
    if ($Days -lt 0) { return }
    if (-not $EnableRcloneUpload) { return }
    $rclone = Ensure-Rclone
    $age = "{0}d" -f $Days
    $delArgs = @('delete',$RcloneRemoteDest,'--config',$Files.RcloneConf,'--min-age',$age,'--fast-list')
    if ($RcloneExtraArgs -and $RcloneExtraArgs.Count -gt 0) { $delArgs += $RcloneExtraArgs }
    Write-Log INFO "rclone retention remoto: delete --min-age $age su $RcloneRemoteDest"
    $p1 = Start-Process -FilePath $rclone -ArgumentList $delArgs -PassThru -Wait -NoNewWindow
    if ($p1.ExitCode -ne 0) { Write-Log WARN "rclone delete (retention) ExitCode=$($p1.ExitCode)" }

    $rdArgs = @('rmdirs',$RcloneRemoteDest,'--config',$Files.RcloneConf,'--leave-root','--fast-list')
    if ($RcloneExtraArgs -and $RcloneExtraArgs.Count -gt 0) { $rdArgs += $RcloneExtraArgs }
    $p2 = Start-Process -FilePath $rclone -ArgumentList $rdArgs -PassThru -Wait -NoNewWindow
    if ($p2.ExitCode -ne 0) { Write-Log WARN "rclone rmdirs (retention) ExitCode=$($p2.ExitCode)" }
}

#endregion ==========================================================================


#region ============================= SQL Backup ====================================

function Resolve-Sqlcmd {
    $cmd = Get-Command sqlcmd.exe -ErrorAction SilentlyContinue
    if ($cmd -and (Test-Path -LiteralPath $cmd.Path)) { return $cmd.Path }
    foreach ($root in @('C:\Program Files\Microsoft SQL Server\*','C:\Program Files (x86)\Microsoft SQL Server\*')) {
        $paths = Get-ChildItem -Path $root -Directory -ErrorAction SilentlyContinue | ForEach-Object { $_.FullName }
        foreach ($p in $paths) {
            $maybe = Join-Path $p 'Tools\Binn\sqlcmd.exe'
            if (Test-Path -LiteralPath $maybe) { return $maybe }
            $maybe2 = Join-Path $p 'Client SDK\ODBC\*\Tools\Binn\SQLCMD.EXE'
            $hit = Get-ChildItem -Path $maybe2 -ErrorAction SilentlyContinue | Select-Object -First 1
            if ($hit) { return $hit.FullName }
        }
    }
    return $null
}

function Build-SqlcmdArgs {
    param([bool]$WindowsAuth,[string]$User,[string]$Password,[string]$Instance,[string]$InputFile,[string]$InlineQuery,[bool]$NoHeaderForQuery=$true)

    $list = @()

    if ($WindowsAuth) {
        $list += '-E'
    } else {
        $list += @('-U',"$User",'-P',"$Password")
    }

    if (-not [string]::IsNullOrWhiteSpace($Instance)) {
        $list += @('-S',"$Instance")
    }

    if (-not [string]::IsNullOrWhiteSpace($InputFile)) {
        $list += @('-i',"$InputFile")
    }

    if (-not [string]::IsNullOrWhiteSpace($InlineQuery)) {
        if ($NoHeaderForQuery) {
            $list += @('-h','-1','-W')
        }
        $list += @('-Q',"$InlineQuery")
    }

    $list = @($list | ForEach-Object { if ($_ -ne $null) { $_.ToString().Trim() } else { $null } } | Where-Object { -not [string]::IsNullOrWhiteSpace($_) })

    return ,$list
}

function Log-SqlcmdArgsSafe {
    param([string[]]$Args)
    $disp = @()
    for ($i=0; $i -lt $Args.Count; $i++) {
        if ($Args[$i] -eq '-P' -and ($i+1) -lt $Args.Count) {
            $disp += '-P *****'
            $i++
        } else {
            $disp += $Args[$i]
        }
    }
    Write-Log INFO ("sqlcmd args: " + ($disp -join ' '))
}

function Get-OnlineUserDatabases {
    param([string]$SqlcmdExe)
    $sql = "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE state = 0 AND database_id > 4 ORDER BY name;"
    $sqlArgs = Build-SqlcmdArgs -WindowsAuth:$SqlUseWindowsAuth -User $SqlUser -Password $SqlPassword -Instance $SqlInstance -InlineQuery $sql -NoHeaderForQuery:$true
    Log-SqlcmdArgsSafe -Args $sqlArgs
    $res = @(& $SqlcmdExe @sqlArgs 2>$null)
    if ($LASTEXITCODE -ne 0) { throw "sqlcmd fallito nell'enumerazione DB." }
    $names = @()
    foreach ($line in @($res)) {
        $n = ($line | ForEach-Object { $_.Trim() })
        if ($n) { $names += $n }
    }
    return $names
}

function Backup-SqlDatabases {
    Ensure-Dir $Paths.SqlStage
    try {
        Write-Log INFO "Concedo '(OI)(CI)M' su '$($Paths.SqlStage)' a Everyone."
        $acl = Get-Acl $Paths.SqlStage
        $rule = New-Object System.Security.AccessControl.FileSystemAccessRule('Everyone','Modify','ContainerInherit, ObjectInherit','None','Allow')
        $acl.SetAccessRule($rule); Set-Acl -LiteralPath $Paths.SqlStage -AclObject $acl
    } catch { Write-Log WARN "ACL non impostate su $($Paths.SqlStage): $_" }

    $sqlcmd = Resolve-Sqlcmd
    if (-not $sqlcmd) { throw "sqlcmd.exe non trovato (PATH o Client Tools mancanti)." }
    Write-Log INFO "sqlcmd: $sqlcmd"

    $dbList = @()
    if (@($DbInclude).Count -gt 0) {
        $dbList = @($DbInclude)
    } else {
        $dbList = @(Get-OnlineUserDatabases -SqlcmdExe $sqlcmd)
        if (@($DbExclude).Count -gt 0) {
            $dbList = @($dbList | Where-Object { @($DbExclude) -notcontains $_ })
        }
    }

    if (@($dbList).Count -eq 0) {
        Write-Log WARN "Nessuna DB utente online trovata da backup."
        return $null
    }
    Write-Log INFO ("DB trovati: " + (@($dbList) -join ', '))

    $bakPaths = @()
    foreach ($db in @($dbList)) {
        $bak = Join-Path $Paths.SqlStage ("{0}_{1}.bak" -f $db, $DateStamp)
        $sql = @"
BACKUP DATABASE [$db]
TO DISK = N'$bak'
WITH COPY_ONLY, INIT, SKIP, NOFORMAT, NAME = N'$db-FULL-$DateStamp', STATS=5;
GO
"@
        $tmpSql = Join-Path $Paths.SqlStage ("backup_{0}.sql" -f $db)
        $sql | Out-File -LiteralPath $tmpSql -Encoding ASCII -Force

        Write-Log INFO "BACKUP [$db] -> $bak"
        $sqlArgs = Build-SqlcmdArgs -WindowsAuth:$SqlUseWindowsAuth -User $SqlUser -Password $SqlPassword -Instance $SqlInstance -InputFile $tmpSql
        Log-SqlcmdArgsSafe -Args $sqlArgs
        & $sqlcmd @sqlArgs
        if ($LASTEXITCODE -ne 0) { throw "Errore backup DB [$db] (sqlcmd ExitCode=$LASTEXITCODE)." }
        if (-not (Test-Path -LiteralPath $bak)) { throw "File .bak non trovato dopo backup: $bak" }

        $bakPaths += $bak
        Remove-Item -LiteralPath $tmpSql -Force -ErrorAction SilentlyContinue
    }

    if ($SqlCompressStage -and $bakPaths.Count -gt 0) {
        Ensure-Dir $Paths.Out
        $sqlArchive = Join-Path $Paths.Out ("SQL_{0}_{1}.7z" -f $HostName, $DateStamp)
        Invoke-7ZipArchive -ArchivePath $sqlArchive -InputPaths @($Paths.SqlStage)
        if ($SqlDropBakAfterZip) {
            Get-ChildItem -LiteralPath $Paths.SqlStage -File -Filter '*.bak' -ErrorAction SilentlyContinue | Remove-Item -Force -ErrorAction SilentlyContinue
        }
        return $sqlArchive
    } else {
        # Non compresso: .bak resta in _sql_stage (gestito da retention)
        return $null
    }
}

#endregion ==========================================================================


#region ============================= File/Folder Archiving =========================

function Archive-FileSources {
    $sources = @()
    foreach ($s in @($ArchiveSources)) {
        $s2 = $s.Trim('"')
        if (-not (Test-Path -LiteralPath $s2)) { Write-Log WARN "Sorgente non trovata: $s2 (ignoro)"; continue }
        $sources += $s2
    }
    $sources = $sources | Select-Object -Unique
    if (@($sources).Count -eq 0) {
        Write-Log INFO "Nessuna sorgente file valida per l'archiviazione."
        return $null
    }
    Ensure-Dir $Paths.Out
    $archive = Join-Path $Paths.Out ("FILES_{0}_{1}.7z" -f $HostName, $DateStamp)
    Invoke-7ZipArchive -ArchivePath $archive -InputPaths $sources
    return $archive
}

#endregion ==========================================================================


#region ============================= Move / Cleanup out =============================

function Get-OutFiles {
    if (-not (Test-Path -LiteralPath $Paths.Out)) { return @() }
    return @(Get-ChildItem -LiteralPath $Paths.Out -File -ErrorAction SilentlyContinue)
}

function Move-OutFiles-ToStores {
    $outFiles = Get-OutFiles
    $moved = 0
    if ($outFiles.Count -eq 0) { return 0 }
    Ensure-Dir $Paths.StoreFiles
    Ensure-Dir $Paths.StoreDb
    foreach ($f in $outFiles) {
        $destDir = if ($f.Name -like 'FILES_*') { $Paths.StoreFiles } else { $Paths.StoreDb }
        $dest = Join-Path $destDir $f.Name
        try { Move-Item -LiteralPath $f.FullName -Destination $dest -Force; $moved++; Write-Log INFO "Spostato: $($f.Name) -> $destDir" } catch { Write-Log WARN "Move fallito per $($f.Name): $_" }
    }
    return $moved
}

function Delete-OutFiles {
    $outFiles = Get-OutFiles
    $deleted = 0
    foreach ($f in $outFiles) {
        try { Remove-Item -LiteralPath $f.FullName -Force -ErrorAction Stop; $deleted++ } catch { Write-Log WARN "Delete fallito per $($f.Name): $_" }
    }
    if ($deleted -gt 0) { Write-Log INFO "Eliminati da 'out': $deleted file" }
}

function Empty-OutFolder {
    if (-not (Test-Path -LiteralPath $Paths.Out)) { return }
    try {
        Get-ChildItem -LiteralPath $Paths.Out -File -ErrorAction SilentlyContinue | ForEach-Object {
            try { Remove-Item -LiteralPath $_.FullName -Force -ErrorAction Stop } catch {}
        }
        Get-ChildItem -LiteralPath $Paths.Out -Directory -ErrorAction SilentlyContinue | ForEach-Object {
            try { Remove-Item -LiteralPath $_.FullName -Recurse -Force -ErrorAction Stop } catch {}
        }
        Write-Log INFO "Cartella 'out' svuotata."
    } catch {
        Write-Log WARN "Impossibile svuotare 'out': $_"
    }
}

#endregion ==========================================================================


#region ============================= Retention =====================================

function Apply-LocalArchivesRetention {
    param([int]$DaysFiles,[int]$DaysDb)

    if ($DaysFiles -ge 0) {
        foreach ($dir in @($Paths.StoreFiles)) {
            if (-not (Test-Path -LiteralPath $dir)) { continue }
            $cut = (Get-Date).AddDays(-$DaysFiles)
            Get-ChildItem -LiteralPath $dir -File -ErrorAction SilentlyContinue |
                Where-Object { $_.LastWriteTime -lt $cut } | ForEach-Object {
                    try {
                        Remove-Item -LiteralPath $_.FullName -Force -ErrorAction Stop
                        Write-Log INFO "Retention FILES: eliminato $($_.FullName)"
                    } catch {
                        Write-Log WARN "Retention FILES: non riesco a eliminare $($_.FullName): $_"
                    }
                }
        }
    }

    if ($DaysDb -ge 0) {
        foreach ($dir in @($Paths.StoreDb)) {
            if (-not (Test-Path -LiteralPath $dir)) { continue }
            $cut = (Get-Date).AddDays(-$DaysDb)
            Get-ChildItem -LiteralPath $dir -File -ErrorAction SilentlyContinue |
                Where-Object { $_.LastWriteTime -lt $cut } | ForEach-Object {
                    try {
                        Remove-Item -LiteralPath $_.FullName -Force -ErrorAction Stop
                        Write-Log INFO "Retention DB: eliminato $($_.FullName)"
                    } catch {
                        Write-Log WARN "Retention DB: non riesco a eliminare $($_.FullName): $_"
                    }
                }
        }
    }
}

function Apply-GenericRetention {
    param([int]$DaysToKeep)
    if ($DaysToKeep -lt 0) { return }
    $cut = (Get-Date).AddDays(-$DaysToKeep)
    foreach ($p in @($Paths.Logs, $Paths.SqlStage)) {
        if (-not (Test-Path -LiteralPath $p)) { continue }
        Get-ChildItem -LiteralPath $p -Recurse -File -ErrorAction SilentlyContinue |
            Where-Object { $_.LastWriteTime -lt $cut } | ForEach-Object {
                try {
                    Remove-Item -LiteralPath $_.FullName -Force -ErrorAction Stop
                    Write-Log INFO "Retention (generico): eliminato $($_.FullName)"
                } catch {
                    Write-Log WARN "Retention (generico): impossibile eliminare $($_.FullName): $_"
                }
            }
    }
}

#endregion ==========================================================================


#region ============================= MAIN ==========================================

foreach ($d in @($Paths.Root,$Paths.Bin,$Paths.Bin7z,$Paths.BinRclone,$Paths.Logs,$Paths.Out,$Paths.SqlStage,$Paths.StoreFiles,$Paths.StoreDb)) { Ensure-Dir $d }

Write-Log INFO ("==== Avvio backup {0} ====" -f $DateStamp)
Write-Log INFO ("BackupRoot: {0} | KeepLocal: {1} | Ret(logs/stage): {2} | Ret(Files): {3} | Ret(DB): {4} | RemoteRet: {5}" -f $BackupRoot, $KeepLocalArchives, $RetentionDays, $LocalRetentionDaysFiles, $LocalRetentionDaysDb, $RemoteRetentionDays)

$summary = New-Object System.Text.StringBuilder
$summary.AppendLine("Host: $HostName") | Out-Null
$summary.AppendLine("Start: $(Now-IT)") | Out-Null
$summary.AppendLine("") | Out-Null

$hadWarning = $false

try {
    # Preflight tool
    $need7z = ($EnableFileBackup -eq $true) -or (($EnableSqlBackup -eq $true) -and ($SqlCompressStage -eq $true))
    if ($need7z) { $null = Ensure-7Zip }

    if ($EnableRcloneUpload) {
        $null = Ensure-Rclone
        Ensure-RcloneConfigOrLaunch
    } else {
        Write-Log INFO "Upload rclone disattivato da config."
    }

    # SQL
    if ($EnableSqlBackup) {
        $sqlArchive = Backup-SqlDatabases
        if ($sqlArchive) { $summary.AppendLine("Archivio SQL: $sqlArchive") | Out-Null }
        else { $summary.AppendLine("SQL: nessun archivio creato (vedi log).") | Out-Null }
    } else {
        Write-Log INFO "Backup SQL disattivato."
    }

    # Files
    if ($EnableFileBackup) {
        $filesArchive = Archive-FileSources
        if ($filesArchive) { $summary.AppendLine("Archivio FILES: $filesArchive") | Out-Null }
    } else {
        Write-Log INFO "Backup file disattivato."
    }

    # Rileva cosa c'è davvero in \out
    $outList = Get-OutFiles
    Write-Log INFO ("File in 'out': " + $outList.Count)
    if ($outList.Count -eq 0) { Write-Log WARN "Nessun archivio in 'out' da processare." }

    # Upload
    if ($EnableRcloneUpload -and $outList.Count -gt 0) {
        if ([string]::IsNullOrWhiteSpace($RcloneRemoteDest) -or ($RcloneRemoteDest -notmatch ':')) {
            throw "RcloneRemoteDest non valido in backup.conf. Esempio: 's3aruba:bucket/path'."
        }
        foreach ($f in $outList) {
            $remote = ($RcloneRemoteDest.TrimEnd('/') + '/' + $DateStamp + '/' + $f.Name)
            Invoke-RcloneCopyTo -LocalFile $f.FullName -RemoteDest $remote
        }
        $summary.AppendLine("Upload completati con rclone verso: $RcloneRemoteDest") | Out-Null
    }

    # ULTIMO: Move/Delete da out
    if ($KeepLocalArchives) {
        $m = Move-OutFiles-ToStores
        Write-Log INFO "Spostati in locale: $m file"
        Empty-OutFolder
    } else {
        Delete-OutFiles
        Empty-OutFolder
    }

    # Retention
    if ($EnableRcloneUpload) { Apply-RemoteRetention -Days $RemoteRetentionDays }
    Apply-LocalArchivesRetention -DaysFiles $LocalRetentionDaysFiles -DaysDb $LocalRetentionDaysDb
    Apply-GenericRetention -DaysToKeep $RetentionDays

    $status = if ($hadWarning) { 'COMPLETATO CON WARNING' } else { 'COMPLETATO' }
    Write-Log INFO "Backup $status."
    $summary.Insert(0, "ESITO: $status`r`n") | Out-Null

    if (Test-Path -LiteralPath $MailScript) { . $MailScript -Subject ($MailSubjectPref + "OK " + $HostName) -Body $summary.ToString() } else { Write-Log WARN "Script di invio mail non trovato: $MailScript" }

} catch {
    $msg = $_.Exception.Message
    Write-Log ERROR $msg
    $summary.Insert(0, "ESITO: FALLITO`r`n") | Out-Null
    $summary.AppendLine("") | Out-Null
    $summary.AppendLine("ERRORE: $msg") | Out-Null
    if (Test-Path -LiteralPath $MailScript) { . $MailScript -Subject ($MailSubjectPref + "ERRORE " + $HostName) -Body $summary.ToString() } else { Write-Log WARN "Script di invio mail non trovato: $MailScript" }
    throw
} finally {
    $summary.AppendLine("") | Out-Null
    $summary.AppendLine("Fine: $(Now-IT)") | Out-Null
}
#endregion ==========================================================================