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

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

# Imposta il percorso di output nella cartella sql
$OutputPath = Join-Path $env:workdir "sql\tables_from_select"

# Verifica che la directory di output esista, altrimenti la crea
if (-not (Test-Path -Path $OutputPath)) {
    New-Item -ItemType Directory -Path $OutputPath | Out-Null
}

try {
    # Carica l'assembly System.Data
    Add-Type -AssemblyName System.Data

    # Definizione manuale dell'elenco delle tabelle da elaborare
    # Formato: ogni elemento è un oggetto con proprietà DbName, SchemaName e TableName
    $tables = @(
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "AnAlberatura" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "AnCompagnia" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "AnEmittente" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "AssetClass" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "CatalogoProdotti" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "CATINAREA" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "Mappatura" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "NeedArea" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "ProdottiNucleo" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "TB_ESG_ANAG_STRUM" }, 
        @{ DbName = "CatalogoProdotti"; SchemaName = "dbo"; TableName = "VDivPtfConCatalogo" }, 
        @{ DbName = "consulenzaEvoluta"; SchemaName = "dbo"; TableName = "Parentela" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "anagrafica_promotori" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Area" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "AreaModello" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Cliente" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "ClientePB" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Cointestatari" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "CointestatariDettaglioDiritto" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Contratto" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DComune" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Delegato" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "dettaglioClientePB" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettaglioOperazione" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettaglioPosizionePortafoglio" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettAltriProdotti" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettImmobiliare" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettPatrAziendale" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DettProdottiTerzi" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "dfinalita" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DistribuzionePtfModello_Tattici" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Dnazione" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "DNazioneEstera" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "dprofilo" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "dtipoimmobile" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "eventiCliente" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "GradoParentela" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "MappaturaPT" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "param" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PatrimonioImmobiliareStaging" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PatrimonioTerzi" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Pianificazione" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PiramideModello" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "portafogliomodello" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PosizioniPortafoglio" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PosizioniProposta" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PRAM_NOBPREZ" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "ProgettiPiramideModello" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Progetto" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "PromotoreBancario" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Proposta" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "REP_ASSET_1_E_2_ORDINATE" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "REP_S4_S5_S13_TRAD_UNTIL_LIV3" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "REP_S80_EVOL_STAMPA_C6" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "Robo_ProgettoRobo" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "token" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "v_servizi_aggiuntivi" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "VCONTRATTIFINEAVANZATO" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "vinfocliente" }, 
        @{ DbName = "consulenzaUnica"; SchemaName = "dbo"; TableName = "W6ASSETPERC" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CaricamentoMassivo" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CaricamentoMassivoC6SC" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_DbContrattoBase" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_DbContrattoSei" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_DbVariazFreqContrSei" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_DbVariazStmrepContrSei" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_HistDatiIngresso" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_Scarti" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_STAMPAREPORT" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "CBS_STAMPAREPORT_TEST" }, 
        @{ DbName = "consulenzaUnicafl"; SchemaName = "dbo"; TableName = "ClienteCambioReteContratto" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "Immobiliare_Cliente" }, 
        @{ DbName = "consulenzaUnicafl"; SchemaName = "dbo"; TableName = "Immobiliare_RichiesteMonitoraggioCatastoCliente" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "logBatchGenerale" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "RDR_VALUTE_DB_DTGIORNALIERI" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "RICODIF_CF_PIVA" }, 
        @{ DbName = "consulenzaUnicaFL"; SchemaName = "dbo"; TableName = "Semaforo" }, 
        @{ DbName = "SEIAlertDW"; SchemaName = "dbo"; TableName = "Vis_Mifid_Report" }
    )

    Write-Host "Trovate $($tables.Count) tabelle"
    "Trovate $($tables.Count) tabelle" | Out-File -FilePath $logFile -Append

    # Funzione per formattare il nome del file
    function Format-FileName {
        param([string]$name)
        # Sostituisce caratteri non validi con underscore
        $invalidChars = [IO.Path]::GetInvalidFileNameChars()
        $invalidChars += '[', ']', ' ', '(', ')', '{', '}', '´', '`', '''', '"'
        $result = $name
        foreach ($char in $invalidChars) {
            $result = $result.Replace($char, '_')
        }
        return $result
    }

    # Per ogni tabella
    foreach ($table in $tables) {
        $dbName = $table.dbName
        $schemaName = $table.SchemaName
        $tableName = $table.TableName
        $formattedDbName = Format-FileName $dbName
        $formattedSchemaName = Format-FileName $schemaName
        $formattedTableName = Format-FileName $tableName

        Write-Host "Elaboro ${dbName}.${schemaName}.${tableName}..."
        "Elaboro ${dbName}.${schemaName}.${tableName}..." | Out-File -FilePath $logFile -Append

        # Query per ottenere la struttura della tabella basata su SELECT *
        $columnQuery = @"
        SELECT 
            c.name as ColumnName,
            t.name as DataType,
            c.max_length,
            c.precision,
            c.scale,
            c.is_nullable,
            CASE 
                WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar') 
                    THEN t.name + '(' + 
                        CASE 
                            WHEN c.max_length = -1 THEN 'MAX'
                            WHEN t.name LIKE 'n%' THEN CAST(c.max_length/2 AS VARCHAR)
                            ELSE CAST(c.max_length AS VARCHAR)
                        END + ')'
                WHEN t.name IN ('decimal', 'numeric') 
                    THEN t.name + '(' + CAST(c.[precision] AS VARCHAR) + ',' + CAST(c.[scale] AS VARCHAR) + ')'
                ELSE t.name
            END as FullDataType
        FROM [$env:dbHostInput].[$dbName].sys.columns c
        INNER JOIN [$env:dbHostInput].[$dbName].sys.types t ON c.user_type_id = t.user_type_id
        INNER JOIN [$env:dbHostInput].[$dbName].sys.tables tbl ON c.object_id = tbl.object_id
        WHERE SCHEMA_NAME(tbl.schema_id) = '$schemaName'
        AND tbl.name = '$tableName'
        ORDER BY c.column_id
"@

        # Ottieni la struttura delle colonne
        $connectionString = "Server=$($env:dbHostInput);Database=$($env:dbNameInput);User Id=$($env:dbUserInput);Password=$($env:dbPasswordInput);TrustServerCertificate=True"
        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
        $command = New-Object System.Data.SqlClient.SqlCommand($columnQuery, $connection)
        $adapter = New-Object System.Data.SqlClient.SqlDataAdapter($command)
        $dataset = New-Object System.Data.DataSet
        $adapter.Fill($dataset) | Out-Null
        $columns = $dataset.Tables[0]

        # Costruisci la CREATE TABLE
        $createTableScript = "CREATE TABLE [$dbName].[$schemaName].[$tableName] (`n"
        $columnDefinitions = @()

        foreach ($column in $columns) {
            $nullability = if ($column.is_nullable) { "NULL" } else { "NOT NULL" }
            $columnDefinitions += "    [$($column.ColumnName)] $($column.FullDataType) $nullability"
        }

        $createTableScript += $columnDefinitions -join ",`n"
        $createTableScript += "`n)"

        # Crea il nome del file
        $fileName = Join-Path $OutputPath "${formattedDbName}_${formattedSchemaName}_${formattedTableName}.sql"

        # Aggiungi la SELECT dopo la CREATE TABLE
        $content = @"
$createTableScript;
GO

-- Popolamento tabella
-- INSERT INTO [$dbName].[$schemaName].[$tableName]
-- SELECT * FROM [$env:dbHostInput].[$dbName].[$schemaName].[$tableName];
-- GO
"@

        try {
            # Scrivi il contenuto nel file con codifica UTF8 con BOM
            $utf8WithBom = New-Object System.Text.UTF8Encoding($true)
            [System.IO.File]::WriteAllText($fileName, $content, $utf8WithBom)

            Write-Host "Creato file per tabella: ${dbName}.${schemaName}.${tableName}" -ForegroundColor Green
            "Creato file per tabella: ${dbName}.${schemaName}.${tableName}" | Out-File -FilePath $logFile -Append
        }
        catch {
            $errorMessage = "ERRORE durante la scrittura del file per ${dbName}.${schemaName}.${tableName}: $($_.Exception.Message)"
            Write-Host $errorMessage -ForegroundColor Red
            $errorMessage | Out-File -FilePath $logFile -Append
        }
    }

    Write-Host "`nGenerazione script completata con successo!"
    "Generazione script completata con successo!" | Out-File -FilePath $logFile -Append

} catch {
    $errorMessage = "Errore durante l'elaborazione: $_"
    Write-Host $errorMessage -ForegroundColor Red
    $errorMessage | Out-File -FilePath $logFile -Append
    exit 1
}