# Set Execution Policy
Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope Process -Force

# Define file paths
$input_file_path = 'C:\Users\FlorianBaar\XXX.csv'
$output_file_path = 'C:\Users\FlorianBaar\XXX.csv'
$winscp_exe_path = 'C:\Users\FlorianBaar\AppData\Local\Programs\WinSCP\WinSCP.exe'
$winscp_log_path = 'C:\Users\FlorianBaar\XXX\WinSCP.log'

# WinSCP connection details
$sftp_server = '31.24.145.67'
$sftp_port = '2022'
$sftp_user = 'bXXXXXXXXXXXXXX' # Username from BEARING X
$sftp_password = 'XXXXXXXXXXXXXXXXX' # Password from BEARING X
$remote_path = '/Stockupload - BEARING X.csv'  # Replace with the actual remote path

# Import the CSV data
Write-Output "Importing CSV data from: $input_file_path"
$input_data = Import-Csv -Delimiter ';' -Path $input_file_path

# Process the data and select the required columns
Write-Output "Processing CSV data"
$output_data = $input_data | Select-Object @{
    Name='buySell';Expression={'S'}
}, @{
    Name='OrderType';Expression={'Order'}
}, @{
    Name='quantity';Expression={$_.'Verfügbarer_Bestand'}
}, @{
    Name='pricePerUnit';Expression={$_.'Nettobetrag'}
}, @{
    Name='minimumPartialQuantity';Expression={$_.'Mindestabnahme'}
}, @{
    Name='itemNumber';Expression={$_.'Artikelbez1'}
}, @{
    Name='brand';Expression={$_.'Hersteller'}
}, @{
    Name='location';Expression={'Germany'}
}, @{
    Name='productionYear';Expression={''}
}, @{
    Name='origin';Expression={$_.'Ursprungsland'}
}, @{
    Name='articleNumber';Expression={''}
}, @{
    Name='fixedPrice';Expression={''}
}

# Convert the processed data to CSV format with semicolon delimiter
Write-Output "Converting processed data to CSV format"
$csv_data = $output_data | ConvertTo-Csv -NoTypeInformation -Delimiter ';' | % { $_ -replace '"', '' }

# Write the CSV data to the output file without BOM
Write-Output "Writing CSV data to: $output_file_path"
$utf8NoBomEncoding = New-Object System.Text.UTF8Encoding($false)
[System.IO.File]::WriteAllText($output_file_path, $csv_data -join [Environment]::NewLine, $utf8NoBomEncoding)

# Create WinSCP script content with proper variable interpolation
$winscp_script = @"
open sftp://${sftp_user}:${sftp_password}@${sftp_server}:${sftp_port}
option transfer ascii
put `"$output_file_path`" `"$remote_path`"
exit
"@

# Save the WinSCP script to a temporary file
$temp_winscp_script_path = [System.IO.Path]::GetTempFileName() + ".txt"
Set-Content -Path $temp_winscp_script_path -Value $winscp_script

# Upload the CSV file using WinSCP with logging enabled
Write-Output "Uploading CSV data to SFTP server"
$winscp_command = "`"$winscp_exe_path`" /log=`"$winscp_log_path`" /script=`"$temp_winscp_script_path`""
Start-Process -FilePath $winscp_exe_path -ArgumentList "/log=$winscp_log_path /script=$temp_winscp_script_path" -NoNewWindow -Wait

# Clean up temporary WinSCP script file
Remove-Item -Path $temp_winscp_script_path

# Output completion message
Write-Output 'Processing and upload complete.'