@@ echo off
@@ echo Invoking a Powershell script...
@@ setlocal
@@ set PS_WRAPPER_ARGS=%*
@@ set PS_WRAPPER_PATH=%~f0
@@ if defined PS_WRAPPER_ARGS set PS_WRAPPER_ARGS=%PS_WRAPPER_ARGS:"="%
@@ PowerShell -sta -Command Invoke-Expression $('$args=@(^&{$args} %PS_WRAPPER_ARGS%);'+[String]::Join([Environment]::NewLine,$((Get-Content '%PS_WRAPPER_PATH%') -notmatch '^^@@^|^^:^|^^cls'))) & endlocal & cmd /k

Add-Type -AssemblyName System.Windows.Forms, PresentationFramework, WindowsBase

Add-Type @"
using System;
using System.Runtime.InteropServices;

public class DarkMode {
    [DllImport("dwmapi.dll")]
    public static extern int DwmSetWindowAttribute(IntPtr hwnd, int attr, ref int attrValue, int attrSize);
}
"@

# Dark mode detection
function Get-WindowsTheme {
    try {
        $path = "HKCU:\SOFTWARE\Microsoft\Windows\CurrentVersion\Themes\Personalize"
        $value = Get-ItemProperty -Path $path -Name "AppsUseLightTheme" -ErrorAction Stop
        return $value.AppsUseLightTheme -eq 0
    } catch {
        return $false
    }
}

# Initialize theme-specific colors
$script:isDarkMode = Get-WindowsTheme
$darkBackColor = [System.Drawing.Color]::FromArgb(32, 32, 32)
$darkForeColor = [System.Drawing.Color]::FromArgb(240, 240, 240)
$darkControlBackColor = [System.Drawing.Color]::FromArgb(45, 45, 45)

# Fix: Use theme-specific accent colors for proper visibility
$script:accentColor = if ($script:isDarkMode) { 
    [System.Drawing.Color]::FromArgb(120, 250, 174)  # Light green for dark mode
} else { 
    [System.Drawing.Color]::FromArgb(0, 120, 50)     # Darker green for light mode
}

# Character replacement mapping (using character codes to avoid UTF-8 encoding issues)
$script:charMap = @{
    [char]0x00E4 = 'ae'; [char]0x00F6 = 'oe'; [char]0x00FC = 'ue'; [char]0x00E9 = 'e'
    [char]0x00E8 = 'e'; [char]0x00EA = 'e'; [char]0x011B = 'e'; [char]0x00E0 = 'a'
    [char]0x00C4 = 'AE'; [char]0x00D6 = 'OE'; [char]0x00DC = 'UE'; [char]0x00C9 = 'E'
    [char]0x00C8 = 'E'; [char]0x00CA = 'E'; [char]0x011A = 'E'; [char]0x00C0 = 'A'
    [char]0x00E1 = 'a'; [char]0x010D = 'c'; [char]0x010F = 'd'; [char]0x00ED = 'i'
    [char]0x0148 = 'n'; [char]0x00F3 = 'o'; [char]0x0159 = 'r'; [char]0x0161 = 's'
    [char]0x0165 = 't'; [char]0x016F = 'u'; [char]0x00FA = 'u'; [char]0x00FD = 'y'
    [char]0x017E = 'z'; [char]0x0142 = 'l'; [char]0x0111 = 'd'
    [char]0x00C1 = 'A'; [char]0x010C = 'C'; [char]0x010E = 'D'; [char]0x00CD = 'I'
    [char]0x0147 = 'N'; [char]0x00D3 = 'O'; [char]0x0158 = 'R'; [char]0x0160 = 'S'
    [char]0x0164 = 'T'; [char]0x016E = 'U'; [char]0x00DA = 'U'; [char]0x00DD = 'Y'
    [char]0x017D = 'Z'; [char]0x0141 = 'L'; [char]0x0110 = 'D'
    [char]0x00DF = 'ss'; ',' = '_'; ' ' = '_'; '.' = '_'; '!' = '_'; [char]0x00A7 = '_'
    "'" = '_'; [char]0x00B4 = '_'; [char]0x02C7 = '_'; ';' = '_'; '(' = '_'; ')' = '_'; '-' = '_'
}

# Function to sanitize sheet names
function Sanitize-SheetName {
    param([string]$name)
    
    $result = $name
    foreach ($key in $script:charMap.Keys) {
        $result = $result -replace [regex]::Escape($key), $script:charMap[$key]
    }
    return $result
}

# Function to process a single Excel file
function Process-ExcelFile {
    param(
        [string]$filePath,
        [System.Windows.Forms.TextBox]$logBox = $null
    )
    
    $excel = $null
    $workbook = $null
    
    try {
        # Validate file
        if (-not (Test-Path $filePath)) {
            $msg = "ERROR: File not found: $filePath"
            if ($logBox) { $logBox.AppendText("$msg`r`n") } else { Write-Host $msg }
            return
        }
        
        if ($filePath -notmatch "\.(xlsx|xlsm|xls)$") {
            $msg = "ERROR: Not an Excel file: $filePath"
            if ($logBox) { $logBox.AppendText("$msg`r`n") } else { Write-Host $msg }
            return
        }
        
        $msg = "Processing: $filePath"
        if ($logBox) { $logBox.AppendText("$msg`r`n") } else { Write-Host $msg }
        
        # Create output folder
        $fileInfo = Get-Item $filePath
        $folderName = [System.IO.Path]::GetFileNameWithoutExtension($fileInfo.Name)
        $outputFolder = Join-Path $fileInfo.DirectoryName $folderName
        
        if (-not (Test-Path $outputFolder)) {
            [void](New-Item -ItemType Directory -Path $outputFolder -Force)
            $msg = "Created folder: $outputFolder"
            if ($logBox) { $logBox.AppendText("$msg`r`n") } else { Write-Host $msg }
        }
        
        # Open Excel
        $excel = New-Object -ComObject Excel.Application
        $excel.Visible = $false
        $excel.DisplayAlerts = $false
        $excel.ScreenUpdating = $false
        
        $workbook = $excel.Workbooks.Open($filePath)
        $sheetCount = $workbook.Sheets.Count
        
        $msg = "Found $sheetCount sheet(s)"
        if ($logBox) { $logBox.AppendText("$msg`r`n") } else { Write-Host $msg }
        
        # Get system list separator for proper CSV format
        $listSeparator = (Get-Culture).TextInfo.ListSeparator
        
        # Export each sheet and collect info for markdown
        $sheetInfo = @()
        for ($i = 1; $i -le $sheetCount; $i++) {
            $sheet = $workbook.Sheets.Item($i)
            $sheetName = $sheet.Name
            $sanitizedName = Sanitize-SheetName $sheetName
            $csvPath = Join-Path $outputFolder "$sanitizedName.csv"
            
            $msg = "  Exporting sheet '$sheetName' -> '$sanitizedName.csv'"
            if ($logBox) { 
                $logBox.AppendText("$msg`r`n")
                [System.Windows.Forms.Application]::DoEvents()
            } else { 
                Write-Host $msg 
            }
            
            # Manual CSV export to preserve formatting and use system separator
            $usedRange = $sheet.UsedRange
            if ($usedRange -ne $null) {
                $rowCount = $usedRange.Rows.Count
                $colCount = $usedRange.Columns.Count
                
                # Build CSV content line by line
                $csvLines = @()
                for ($row = 1; $row -le $rowCount; $row++) {
                    $rowValues = @()
                    for ($col = 1; $col -le $colCount; $col++) {
                        $cell = $sheet.Cells.Item($row, $col)
                        $text = $cell.Text  # Get formatted display value (preserves dates as DD.MM.YYYY)
                        
                        # Escape if contains separator, quotes, or newlines
                        if ($text -match "[$([regex]::Escape($listSeparator))`"`r`n]") {
                            $text = '"' + ($text -replace '"', '""') + '"'
                        }
                        $rowValues += $text
                        
                        # Release cell COM object
                        [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($cell)
                    }
                    $csvLines += ($rowValues -join $listSeparator)
                }
                
                # Write CSV with UTF-8 encoding
                $csvLines -join "`r`n" | Set-Content -Path $csvPath -Encoding UTF8
                
                # Release range COM object
                [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($usedRange)
            }
            
            # Store info for markdown
            $sheetInfo += [PSCustomObject]@{
                OriginalName = $sheetName
                SanitizedName = $sanitizedName
                CsvPath = $csvPath
            }
            
            # Release sheet COM object
            [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet)
        }
        
        # Create combined markdown file
        $msg = "`r`n  Creating combined markdown file..."
        if ($logBox) { 
            $logBox.AppendText("$msg`r`n")
            [System.Windows.Forms.Application]::DoEvents()
        } else { 
            Write-Host $msg 
        }
        
        $markdownPath = Join-Path $outputFolder "$folderName.md"
        $markdownContent = ""
        
        foreach ($info in $sheetInfo) {
            $markdownContent += "# $($info.OriginalName)`r`n"
            if (Test-Path $info.CsvPath) {
                $csvContent = Get-Content -Path $info.CsvPath -Raw -Encoding UTF8
                $markdownContent += $csvContent.TrimEnd() + "`r`n`r`n"
            }
        }
        
        $markdownContent | Set-Content -Path $markdownPath -Encoding UTF8
        
        $msg = "`r`nSuccess! Exported $sheetCount sheet(s) to: $outputFolder`r`n"
        $msg += "  - Individual CSV files: $sheetCount`r`n"
        $msg += "  - Combined markdown: $folderName.md`r`n"
        $msg += "  - Using delimiter: '$listSeparator'`r`n"
        if ($logBox) { $logBox.AppendText("$msg") } else { Write-Host $msg }
        
    } catch {
        $msg = "ERROR: $_"
        if ($logBox) { $logBox.AppendText("$msg`r`n") } else { Write-Host $msg }
    } finally {
        # Cleanup COM objects (critical!)
        if ($workbook -ne $null) {
            $workbook.Close($false)
            [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook)
        }
        
        if ($excel -ne $null) {
            $excel.Quit()
            [void][System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
        }
        
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
        Start-Sleep -Milliseconds 500
    }
}

# Main logic: Check if file was drag-dropped
if ($args.Count -gt 0 -and $args[0]) {
    # Silent mode - no GUI
    $filePath = $args[0]
    Process-ExcelFile -filePath $filePath
    
} else {
    # GUI mode - no file provided
    
    # Create main form
    $form = New-Object Windows.Forms.Form
    $form.Text = "Excel to CSV Exporter"
    $form.Size = New-Object Drawing.Size(700, 500)
    $form.StartPosition = "CenterScreen"
    $form.AllowDrop = $true
    
    # Create log textbox
    $logBox = New-Object Windows.Forms.TextBox
    $logBox.Multiline = $true
    $logBox.ScrollBars = "Vertical"
    $logBox.Dock = "Fill"
    $logBox.Font = New-Object Drawing.Font("Consolas", 10)
    $logBox.ReadOnly = $true
    
    # Create button
    $button = New-Object Windows.Forms.Button
    $button.Text = "Select Excel File(s)"
    $button.Dock = "Bottom"
    $button.Height = 50
    $button.FlatStyle = "Flat"
    $button.Font = New-Object Drawing.Font($button.Font.FontFamily, 11, [System.Drawing.FontStyle]::Bold)
    
    # Apply theme colors
    if ($script:isDarkMode) {
        $form.BackColor = $darkBackColor
        $form.ForeColor = $darkForeColor
        $logBox.BackColor = $darkControlBackColor
        $logBox.ForeColor = $darkForeColor
        $button.BackColor = $darkControlBackColor
        $button.ForeColor = $script:accentColor
        $button.FlatAppearance.BorderColor = $script:accentColor
    } else {
        $button.ForeColor = $script:accentColor
    }
    
    # Apply dark mode to title bar (must be in Shown event)
    $form.Add_Shown({
        if ($script:isDarkMode) {
            $darkModeValue = 1
            [void][DarkMode]::DwmSetWindowAttribute($form.Handle, 20, [ref]$darkModeValue, 4)
        }
    })
    
    # Button click handler
    $button.Add_Click({
        $openFileDialog = New-Object Windows.Forms.OpenFileDialog
        $openFileDialog.Filter = "Excel files (*.xlsx;*.xls;*.xlsm)|*.xlsx;*.xls;*.xlsm"
        $openFileDialog.Multiselect = $true
        
        if ($openFileDialog.ShowDialog() -eq 'OK') {
            $button.Enabled = $false
            $logBox.Clear()
            
            foreach ($file in $openFileDialog.FileNames) {
                Process-ExcelFile -filePath $file -logBox $logBox
            }
            
            $button.Enabled = $true
        }
    })
    
    # Drag & drop handlers
    $form.Add_DragEnter({
        if ($_.Data.GetDataPresent([Windows.Forms.DataFormats]::FileDrop)) {
            $_.Effect = [Windows.Forms.DragDropEffects]::Copy
        }
    })
    
    $form.Add_DragDrop({
        $files = $_.Data.GetData([Windows.Forms.DataFormats]::FileDrop)
        $excelFiles = $files | Where-Object { $_ -match "\.(xlsx|xls|xlsm)$" }
        
        if ($excelFiles.Count -eq 0) {
            $logBox.AppendText("Please drop Excel file(s) (.xlsx, .xls, or .xlsm)`r`n")
            return
        }
        
        $button.Enabled = $false
        $logBox.Clear()
        
        foreach ($file in $excelFiles) {
            Process-ExcelFile -filePath $file -logBox $logBox
        }
        
        $button.Enabled = $true
    })
    
    # Initial instructions
    $logBox.AppendText("EXCEL TO CSV EXPORTER`r`n")
    $logBox.AppendText("====================`r`n`r`n")
    $logBox.AppendText("This tool exports all sheets from Excel files to CSV format.`r`n`r`n")
    $logBox.AppendText("For each Excel file:`r`n")
    $logBox.AppendText("  1. Creates a folder with the same name as the file`r`n")
    $logBox.AppendText("  2. Exports each sheet to a separate CSV file`r`n")
    $logBox.AppendText("  3. Sanitizes sheet names (replaces special characters)`r`n`r`n")
    $logBox.AppendText("Drag & drop Excel files here or click the button below.`r`n")
    
    # Add controls to form
    $form.Controls.Add($logBox)
    $form.Controls.Add($button)
    
    # Show the form
    [void]$form.ShowDialog()
}