# Konfiguration
$rootFolder = "C:\sql"

# Mappings
$mapping = @(
    @{ SchemaOld="dbo"; NameOld="sx_pf_COPY_Factory"; SchemaNew="planning"; NameNew="spCOPY_Factory" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_COPY_Format"; SchemaNew="planning"; NameNew="spCOPY_Format" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_COPY_List"; SchemaNew="planning"; NameNew="spCOPY_List" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_COPY_Product"; SchemaNew="planning"; NameNew="spCOPY_Product" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_COPY_ProductLine"; SchemaNew="planning"; NameNew="spCOPY_ProductLine" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_ClusterProperty"; SchemaNew="planning"; NameNew="spDELETE_ClusterProperty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_Factory"; SchemaNew="planning"; NameNew="spDELETE_Factory" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_FactoryProperty"; SchemaNew="planning"; NameNew="spDELETE_FactoryProperty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_Format"; SchemaNew="planning"; NameNew="spDELETE_Format" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_List"; SchemaNew="planning"; NameNew="spDELETE_List" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_ListValue"; SchemaNew="planning"; NameNew="spDELETE_ListValue" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_Product"; SchemaNew="planning"; NameNew="spDELETE_Product" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_ProductLine"; SchemaNew="planning"; NameNew="spDELETE_ProductLine" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_ProductLineProperty"; SchemaNew="planning"; NameNew="spDELETE_ProductLineProperty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_ProductProperty"; SchemaNew="planning"; NameNew="spDELETE_ProductProperty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_TimeID"; SchemaNew="planning"; NameNew="spDELETE_TimeID" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_Value"; SchemaNew="planning"; NameNew="spDELETE_Value" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_ValueSerie"; SchemaNew="planning"; NameNew="spDELETE_ValueSerie" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_EXPORT_Factory"; SchemaNew="planning"; NameNew="spEXPORT_Factory" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_EXPORT_Product"; SchemaNew="planning"; NameNew="spEXPORT_Product" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_EXPORT_Productline"; SchemaNew="planning"; NameNew="spEXPORT_ProductLine" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_AllProductListValues"; SchemaNew="planning"; NameNew="spGET_AllProductListValues" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ClusterProperties"; SchemaNew="planning"; NameNew="spGET_ClusterProperties" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pGET_ClusterPropertiesReadRight"; SchemaNew="planning"; NameNew="spGET_ClusterPropertiesReadRight" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ClusterProperty"; SchemaNew="planning"; NameNew="spGET_ClusterProperty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Factories"; SchemaNew="planning"; NameNew="spGET_Factories" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Factory"; SchemaNew="planning"; NameNew="spGET_Factory" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_FactoryProperties"; SchemaNew="planning"; NameNew="spGET_FactoryProperties" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pGET_FactoryReadRight"; SchemaNew="planning"; NameNew="spGET_FactoryReadRight" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pGET_FactoryWriteRight"; SchemaNew="planning"; NameNew="spGET_FactoryWriteRight" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Format"; SchemaNew="planning"; NameNew="spGET_Format" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Formats"; SchemaNew="planning"; NameNew="spGET_Formats" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_GlobalSearch"; SchemaNew="planning"; NameNew="spGET_GlobalSearch" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Info_AllGlobalAttributeUsage"; SchemaNew="planning"; NameNew="spGET_Info_AllGlobalAttributeUsage" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_List"; SchemaNew="planning"; NameNew="spGET_List" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Lists"; SchemaNew="planning"; NameNew="spGET_Lists" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ListValues"; SchemaNew="planning"; NameNew="spGET_ListValues" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Info_NextFreeFactoryID"; SchemaNew="planning"; NameNew="spGET_NextFreeFactoryID" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Info_NextFreeProductID"; SchemaNew="planning"; NameNew="spGET_NextFreeProductID" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Info_NextFreeProductlineID"; SchemaNew="planning"; NameNew="spGET_NextFreeProductLineID" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Product"; SchemaNew="planning"; NameNew="spGET_Product" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ProductDataTableValues"; SchemaNew="planning"; NameNew="spGET_ProductDataTableValues" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ProductLine"; SchemaNew="planning"; NameNew="spGET_ProductLine" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ProductLineProperties"; SchemaNew="planning"; NameNew="spGET_ProductLineProperties" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pGET_ProductLineReadRight"; SchemaNew="planning"; NameNew="spGET_ProductLineReadRight" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ProductLines"; SchemaNew="planning"; NameNew="spGET_ProductLines" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pGET_ProductLineWriteRight"; SchemaNew="planning"; NameNew="spGET_ProductLineWriteRight" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ProductProperties"; SchemaNew="planning"; NameNew="spGET_ProductProperties" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ProductRanges"; SchemaNew="planning"; NameNew="spGET_ProductRanges" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Products"; SchemaNew="planning"; NameNew="spGET_Products" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Statements"; SchemaNew="planning"; NameNew="spGET_Statements" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_TimeIDs"; SchemaNew="planning"; NameNew="spGET_TimeIDs" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_Value"; SchemaNew="planning"; NameNew="spGET_Value" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ValueSerie"; SchemaNew="planning"; NameNew="spGET_ValueSerie" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ValueSeries"; SchemaNew="planning"; NameNew="spGET_ValueSeries" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_MOVE_Factory"; SchemaNew="planning"; NameNew="spMOVE_Factory" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_MOVE_Format"; SchemaNew="planning"; NameNew="spMOVE_Format" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_MOVE_List"; SchemaNew="planning"; NameNew="spMOVE_List" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_MOVE_Product"; SchemaNew="planning"; NameNew="spMOVE_Product" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_MOVE_ProductLine"; SchemaNew="planning"; NameNew="spMOVE_ProductLine" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_MOVE_ValueSerie"; SchemaNew="planning"; NameNew="spMOVE_ValueSerie" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_ClusterProperty"; SchemaNew="planning"; NameNew="spPOST_ClusterProperty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_Factory"; SchemaNew="planning"; NameNew="spPOST_Factory" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_FactoryProperty"; SchemaNew="planning"; NameNew="spPOST_FactoryProperty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_Format"; SchemaNew="planning"; NameNew="spPOST_Format" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_List"; SchemaNew="planning"; NameNew="spPOST_List" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_ListValue"; SchemaNew="planning"; NameNew="spPOST_ListValue" },
    @{ SchemaOld="control"; NameOld="spParamTables"; SchemaNew="planning"; NameNew="spPOST_ParamTable" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_Product"; SchemaNew="planning"; NameNew="spPOST_Product" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_ProductDataTableValues"; SchemaNew="planning"; NameNew="spPOST_ProductDataTableValues" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_ProductEmpty"; SchemaNew="planning"; NameNew="spPOST_ProductEmpty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_ProductLine"; SchemaNew="planning"; NameNew="spPOST_ProductLine" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_ProductLineProperty"; SchemaNew="planning"; NameNew="spPOST_ProductLineProperty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_ProductProperty"; SchemaNew="planning"; NameNew="spPOST_ProductProperty" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_Statement"; SchemaNew="planning"; NameNew="spPOST_Statement" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_Template"; SchemaNew="planning"; NameNew="spPOST_Template" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_Template_All"; SchemaNew="planning"; NameNew="spPOST_Template_All" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_TimeID"; SchemaNew="planning"; NameNew="spPOST_TimeID" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_Timeline"; SchemaNew="planning"; NameNew="spPOST_Timeline" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_Value"; SchemaNew="planning"; NameNew="spPOST_Value" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_ValueSerie"; SchemaNew="planning"; NameNew="spPOST_ValueSerie" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ClusterAccessRight"; SchemaNew="system"; NameNew="spGET_ClusterAccessRight" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_ClusterWriteRight"; SchemaNew="system"; NameNew="spGET_ClusterWriteRight" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pGET_ClusterWriteRight"; SchemaNew="system"; NameNew="spGET_ClusterWriteRightTransactionUser" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pPOST_API_LogEntry"; SchemaNew="system"; NameNew="spPOST_APILogEntry" },
    @{ SchemaOld="system"; NameOld="pGET_Setting"; SchemaNew="system"; NameNew="spGET_Setting" },
    @{ SchemaOld="system"; NameOld="pGET_Settings"; SchemaNew="system"; NameNew="spGET_Settings" },
    @{ SchemaOld="system"; NameOld="spMaterialize_trUserRights"; SchemaNew="system"; NameNew="spMATERIALIZE_trUserRights" },
    @{ SchemaOld="system"; NameOld="pPOST_Setting"; SchemaNew="system"; NameNew="spPOST_Setting" },
    @{ SchemaOld="agent"; NameOld="pCreateJSONView"; SchemaNew="agent"; NameNew="spCreateJSONView" },
    @{ SchemaOld="agent"; NameOld="pDeleteIntegrationValues"; SchemaNew="agent"; NameNew="spDeleteIntegrationValues" },
    @{ SchemaOld="agent"; NameOld="pDeleteIntegrationValuesGeneric"; SchemaNew="agent"; NameNew="spDeleteIntegrationValuesGeneric" },
    @{ SchemaOld="agent"; NameOld="pMaintenanceIndexes"; SchemaNew="agent"; NameNew="spMaintenanceIndexes" },
    @{ SchemaOld="agent"; NameOld="pMaintenanceJSONViews"; SchemaNew="agent"; NameNew="spMaintenanceJSONViews" },
    @{ SchemaOld="agent"; NameOld="pMaintenanceRecreateViews"; SchemaNew="agent"; NameNew="spMaintenanceRecreateViews" },
    @{ SchemaOld="agent"; NameOld="pPostBulkload"; SchemaNew="agent"; NameNew="spPostBulkload" },
    @{ SchemaOld="agent"; NameOld="pSync"; SchemaNew="agent"; NameNew="spSync" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_Right"; SchemaNew="planning"; NameNew="spDELETE_Right" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_DELETE_Values"; SchemaNew="planning"; NameNew="spDELETE_Values" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_FlexAction"; SchemaNew="planning"; NameNew="spGET_FlexAction" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_FlexDrill"; SchemaNew="planning"; NameNew="spGET_FlexDrill" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_GET_FlexList"; SchemaNew="planning"; NameNew="spGET_FlexList" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_POST_FlexAction"; SchemaNew="planning"; NameNew="spPOST_FlexAction" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_Determine_TransactionUsername"; SchemaNew="system"; NameNew="fDetermineTransactionUsername" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pProtectBoolean"; SchemaNew="system"; NameNew="fProtectBoolean" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pProtectID"; SchemaNew="system"; NameNew="fProtectID" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pProtectInt"; SchemaNew="system"; NameNew="fProtectInt" },
    @{ SchemaOld="dbo"; NameOld="sx_pf_pProtectString"; SchemaNew="system"; NameNew="fProtectString" }
)

foreach ($map in $mapping) {
    $map.FullNameOld = "$($map.SchemaOld).$($map.NameOld)"
    $map.FullNameNew = "$($map.SchemaNew).$($map.NameNew)"
}

# längste Namen zuerst
$mapping = $mapping | Sort-Object { $_.FullNameOld.Length } -Descending

# SQL Dateien laden
$files = Get-ChildItem -Path $rootFolder -Recurse -File -Filter *.sql

# Verarbeitung
foreach ($file in $files) {
    Write-Host "Datei: $($file.FullName)"

    $sql = Get-Content -Path $file.FullName -Raw -Encoding UTF8

    # aktueller Objektname aus Dateiname
    $objectName = [System.IO.Path]::GetFileNameWithoutExtension($file.Name)

    # nur Mapping für dieses Objekt
    $currentMap = $mapping | Where-Object {
        $_.FullNameOld -ieq $objectName
    }

    $lines = $sql -split "`n"

    # Zeilenweise ersetzen
    for ($i = 0; $i -lt $lines.Count; $i++) {
        # EXECUTE AS Zeilen nicht anpassen
        if ($lines[$i] -match "(?i)^\s*WITH\s+EXECUTE\s+AS") {
            continue
        }

        foreach ($map in $mapping) {
            $oldSchema = $map.SchemaOld
            $oldName   = $map.NameOld
            $newSchema = $map.SchemaNew
            $newName   = $map.NameNew

            # diverse Varianten ersetzen
            $lines[$i] = $lines[$i] `
                -replace "\[$oldSchema\]\.\[$oldName\]", "[$newSchema].[$newName]" `
                -replace "$oldSchema\.$oldName", "$newSchema.$newName" `
                -replace "\[$oldSchema\]\.$oldName", "[$newSchema].[$newName]" `
                -replace "$oldSchema\.\[$oldName\]", "$newSchema.$newName" `
                -replace "\[$oldName\]", "[$newSchema].[$newName]"

            # EXEC ohne explizites dbo davor ersetzen
            $lines[$i] = $lines[$i] `
                -replace "EXEC $oldName", "EXEC $newSchema.$newName" `
                -replace "= $oldName", "= $newSchema.$newName"
        }
    }

    $sql = $lines -join "`n"

    # Doku/Metadaten Zeilen anpassen aber nur für die passende Datei
    if ($currentMap) {
        $oldSchema = $currentMap.SchemaOld
        $oldName   = $currentMap.NameOld
        $newSchema = $currentMap.SchemaNew
        $newName   = $currentMap.NameNew

        $lines = $sql -split "`n"

        for ($i = 0; $i -lt $lines.Count; $i++){
            # EXECUTE AS Zeilen nicht anpassen
            if ($lines[$i] -match "(?i)^\s*WITH\s+EXECUTE\s+AS") {
                continue
            }

            $lines[$i] = $lines[$i] `
                -replace "'$($oldSchema)'", "'$($newSchema)'" `
                -replace "'$($oldName)'", "'$($newName)'" `
                -replace "'$($oldName) '", "'$($newName)'"
        }

        $sql = $lines -join "`n"
    }

    # Dateiname aktualisieren
    $newFileName = $file.Name
    if ($currentMap) {
        $newFileName = "$($currentMap.FullNameNew).sql"
    }

    $targetPath = Join-Path $file.DirectoryName $newFileName

    # Datei speichern
    Set-Content -Path $targetPath -Value $sql -Encoding UTF8

    # alte Datei löschen wenn umbenannt
    if ($targetPath -ne $file.FullName) {
        Remove-Item $file.FullName
    }

    Write-Host "Fertig: $newFileName"
}