param(
    [switch]$UseDoneFolder,
    [switch]$LeaveSuccessLogs,
    [switch]$DeleteSuccessSql
)

# Importa configurazione
. "$PSScriptRoot\conf.ps1"

$scriptName = $MyInvocation.MyCommand.Name
$logFile = Join-Path $env:logDir "${scriptName}.log"	# for debug
"start" | Out-File -FilePath $logFile

# List of SQL files or folders to execute in order
$sqlFiles = @(
    # "base\C6SC01_creadb.sql",      # file singolo
    # "C6SC08_creauser.sql",
    # "base\StampeCentralizzateBackupRidotto.sql",
    # "base\enable_sa_full.sql",
    # "base\add_linked_server.sql",
    # "base\enable_F701264_full.sql",
    # "C6MartPeriodico_GESTIONE_SELECT_S80_FASE2_CU_20190206_preMioFoglio.sql",
    "stored\viste",                  # cartella
    # "tables_from_select",      # cartella
    ""
)

$sqlFiles = $sqlFiles[0..($sqlFiles.Count-2)]
$filesToExecute = @()

# Crea la directory done se richiesto
if ($UseDoneFolder) {
    $doneDir = Join-Path $env:sqlDir "stored\done"
    if (-not (Test-Path $doneDir)) {
        New-Item -ItemType Directory -Path $doneDir | Out-Null
        Write-Host "Creata directory: $doneDir" -ForegroundColor Yellow
    }
}

# Prepara la lista completa dei file da eseguire
foreach ($item in $sqlFiles) {
    $itemPath = Join-Path $env:sqlDir $item
    
    # Verifica se l'elemento esiste
    if (-not (Test-Path $itemPath)) {
        Write-Host "Non trovato: $itemPath" -ForegroundColor Red
        continue
    }
    
    # Se è una cartella, aggiungi tutti i file .sql al suo interno
    if (Test-Path $itemPath -PathType Container) {
        Write-Host "Aggiunta cartella: $item" -ForegroundColor Yellow
        $folderFiles = Get-ChildItem -Path $itemPath -Filter "*.sql" | Sort-Object Name
        foreach ($file in $folderFiles) {
            $filesToExecute += $file.FullName
            Write-Host "  + $($file.Name)" -ForegroundColor Gray
        }
    }
    # Se è un file, aggiungilo direttamente
    else {
        $filesToExecute += $itemPath
        Write-Host "Aggiunto file: $item" -ForegroundColor Yellow
    }
}

Write-Host "`nVerranno eseguiti $($filesToExecute.Count) file SQL in ordine" -ForegroundColor Green

foreach ($fileSql in $filesToExecute) {
    if (-not (Test-Path $fileSql)) {
        Write-Host "File non trovato: $fileSql" -ForegroundColor Red
        continue
    }

    # Estrai il nome del file dal percorso completo
    $fileName = [System.IO.Path]::GetFileName($fileSql)
    
    (Get-Date).ToString("yyyy-MM-dd_HH:mm:ss")+" eseguo item: $fileName " | Out-File -FilePath $logFile -Append
    Write-Host "Elaboro $fileSql"
    $fileLog = Join-Path $env:logDir ($fileName + ".log")
    
    $maxRetries = 3
    $retryCount = 0
    $success = $false
    
    $success = $false
    while (-not $success -and $retryCount -lt $maxRetries) {
        try {
            # Execute SQL and capture all output
            $output = & sqlcmd -S $env:dbHostOutput -U $env:dbUserOutput -P $env:dbPasswordOutput -d $env:dbNameOutput -i $fileSql -b 2>&1
            $esitoComando = $LASTEXITCODE

            # Save output to log file and process it
            $output | Out-File -FilePath $fileLog
            $logContent = Get-Content -Path $fileLog -Raw

            # Check for SQL errors
            if ($logContent -match "(Msg \d+,.*?)(?=Msg \d+,|$)|(Il nome della colonna.*?)(?=Msg \d+,|$)|(Errore di sintassi.*?)(?=Msg \d+,|$)") {
                "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') ERRORE nell'esecuzione di $fileName" | Out-File -FilePath $logFile -Append
                "------------------" | Out-File -FilePath $logFile -Append
                $Matches[0].Trim() | Out-File -FilePath $logFile -Append
                "----------------------------------------" | Out-File -FilePath $logFile -Append
                break
            }

            if ($esitoComando -ne 0) {
                "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') ERRORE nell'esecuzione di $fileName (Exit Code: $esitoComando)" | Out-File -FilePath $logFile -Append
                "------------------" | Out-File -FilePath $logFile -Append
                $logContent | Out-File -FilePath $logFile -Append
                "----------------------------------------" | Out-File -FilePath $logFile -Append
                break
            }

            $success = $true
        }
        catch {
            "$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss') ERRORE nell'esecuzione di $fileName" | Out-File -FilePath $logFile -Append
            "------------------" | Out-File -FilePath $logFile -Append
            $_.Exception.Message | Out-File -FilePath $logFile -Append
            "----------------------------------------" | Out-File -FilePath $logFile -Append
            break
        }

        # Leggi il contenuto del file di log
        $logContent = Get-Content -Path $fileLog -Raw
        
        # Controlla vari tipi di messaggi e errori
        $isObjectExistsError = $logContent -match "There is already an object named"
        $hasWarnings = $logContent -match "(?m)^Warning:"
        $hasErrors = $logContent -match "(?m)^Msg \d+, Level \d+, State \d+" -or 
                     $logContent -match "Incorrect syntax" -or
                     $logContent -match "Invalid object name" -or
                     $logContent -match "Could not find stored procedure" -or
                     $logContent -match "Login failed"
        $warningLines = if ($hasWarnings) {
            $logContent -split "`n" | Where-Object { $_ -match "^Warning:" }
        } else { @() }
        $errorLines = if ($hasErrors) {
            ($logContent -split "`n" | Where-Object { $_ -match "^Msg \d+,|^Incorrect syntax|^Invalid object|^Could not find|^Login failed" }) -join "`n"
        } else { "" }
        "logSqlApp: $logSqlApp esitoComando: $esitoComando" | Out-File -FilePath $logFile -Append
            
        IF (($esitoComando -eq 0 -or $isObjectExistsError) -and -not $hasErrors) {
            # Determina lo stato dell'esecuzione
            $status = if ($isObjectExistsError) { 
                "SKIP" 
            } elseif ($hasWarnings) { 
                "WARNING" 
            } else { 
                "OK" 
            }
            
            "run $fileName [$status]" | Out-File -FilePath $logFile -Append
            
            # Mostra il messaggio appropriato
            if ($isObjectExistsError) {
                Write-Host "ATTENZIONE: Oggetto già esistente in $fileName - Continuo l'esecuzione" -ForegroundColor Yellow
            } elseif ($hasWarnings) {
                Write-Host "Eseguito $fileName con warning:" -ForegroundColor Yellow
                foreach ($warning in $warningLines) {
                    Write-Host "  $warning" -ForegroundColor Yellow
                }
            } else {
                Write-Host "Eseguito con successo $fileName" -ForegroundColor Green
                # Cancella il log file se non è richiesto di mantenerlo e non ci sono warning ed errori
                if (-not $LeaveSuccessLogs) {
                    Remove-Item -Path $fileLog -Force
                }
                
                # Gestione del file SQL dopo l'esecuzione con successo
                if ($DeleteSuccessSql) {
                    Remove-Item -Path $fileSql -Force
                    Write-Host "  File SQL eliminato" -ForegroundColor Gray
                } elseif ($UseDoneFolder) {
                    $destPath = Join-Path $doneDir $fileName
                    Move-Item -Path $fileSql -Destination $destPath -Force
                    Write-Host "  File spostato in done/" -ForegroundColor Gray
                }
            }
            (Get-Date).ToString("yyyy-MM-dd_HH:mm:ss")+"  item: $fileSql completed" | Out-File -FilePath $logFile -Append
        } else {
            Write-Host "ERRORE nell'esecuzione di $fileName" -ForegroundColor Red
            Write-Host "Log dell'errore:" -ForegroundColor Red
            if ($errorLines) {
                Write-Host $errorLines -ForegroundColor Red
            } else {
                Write-Host $logContent -ForegroundColor Red
            }
            Write-Host "----------------------------------------" -ForegroundColor Red
            
            $(Get-Date -Format "yyyy-MM-dd_HH:mm:ss")+" ERROR per $fileSql" | Out-File -FilePath $logFile -Append
            $(Get-Date -Format "yyyy-MM-dd_HH:mm:ss")+" $logContent" | Out-File -FilePath $logFile -Append
            $(Get-Date -Format "yyyy-MM-dd_HH:mm:ss")+" processo bloccato " | Out-File -FilePath $logFile -Append
            exit 1
        }
        $success = $true
    }
    # }
    # catch {
    #     $msg = [string]::Format("Error executing {0}: {1}", $fileName, $_.Exception.Message)
    #     Write-Host $msg -ForegroundColor Red
    # }

    Write-Host "----------------------------------------"
}
"fine lavori"  | Out-File -FilePath $logFile -Append
