SpreadsheetWrangler.ps1

# SpreadsheetWrangler.ps1
# GUI application for spreadsheet operations and folder backups
# Modular version - dot-sources components from lib/ folder

# Check for ImportExcel module availability
$script:UseImportExcel = $false

# Check if ImportExcel module is installed
if (-not (Get-Module -ListAvailable -Name ImportExcel)) {
    Write-Host "ImportExcel module not found. Attempting to install..." -ForegroundColor Yellow
    try {
        Install-Module -Name ImportExcel -Scope CurrentUser -Force -ErrorAction Stop
        Write-Host "ImportExcel module installed successfully." -ForegroundColor Green
    } catch {
        $errorMessage = $_.Exception.Message
        Write-Host "Failed to install ImportExcel module. Please run 'Install-Module -Name ImportExcel -Scope CurrentUser -Force' manually." -ForegroundColor Red
        Write-Host "Error``: $errorMessage" -ForegroundColor Red
        exit
    }
}

# Import the module
try {
    Import-Module -Name ImportExcel -ErrorAction Stop
    $script:UseImportExcel = $true
    Write-Host "ImportExcel module loaded successfully." -ForegroundColor Green
} catch {
    $errorMessage = $_.Exception.Message
    Write-Host "Failed to load ImportExcel module. Error``: $errorMessage" -ForegroundColor Red
    exit
}

# Load required assemblies
Add-Type -AssemblyName System.Windows.Forms
Add-Type -AssemblyName System.Drawing
Add-Type -AssemblyName System.Xml
Add-Type -AssemblyName System.Xml.Linq

# Dot-source the module files (suppress any stray output)
$libPath = Join-Path -Path $PSScriptRoot -ChildPath "lib"
. (Join-Path -Path $libPath -ChildPath "Utilities.ps1") | Out-Null
. (Join-Path -Path $libPath -ChildPath "Backup.ps1") | Out-Null
. (Join-Path -Path $libPath -ChildPath "Spreadsheet.ps1") | Out-Null
. (Join-Path -Path $libPath -ChildPath "Labels.ps1") | Out-Null
. (Join-Path -Path $libPath -ChildPath "Config.ps1") | Out-Null

# Initialize script variables for label paths
$script:LabelInputFolder = ""
$script:LabelOutputFolder = ""
$script:LabelParamTemplate = ""
$script:LabelPrtTemplate = ""
$script:LabelDymoTemplate = ""

# Create the main form
$script:form = New-Object System.Windows.Forms.Form
$script:form.Text = "Spreadsheet Wrangler"
$script:form.Size = New-Object System.Drawing.Size(900, 870) 
$script:form.MinimumSize = New-Object System.Drawing.Size(800, 750)
$script:form.StartPosition = "CenterScreen"
$script:form.FormBorderStyle = "Sizable"
$script:form.MaximizeBox = $true
$script:form.MinimizeBox = $true
$script:form.Font = New-Object System.Drawing.Font("Segoe UI", 10)
$script:form.KeyPreview = $true

# Track if an operation is running (for cancel functionality)
$script:IsRunning = $false
$script:CancelRequested = $false

# Dark mode state
$script:DarkMode = $false

# Set application icon if logo exists
$logoPath = Join-Path -Path $PSScriptRoot -ChildPath "logo.png"
if (Test-Path -Path $logoPath) {
    try {
        # Load the logo as an icon for the application
        $logo = [System.Drawing.Image]::FromFile($logoPath)
        # Create a simple icon from the logo
        $icon = [System.Drawing.Icon]::FromHandle(($logo.GetThumbnailImage(64, 64, $null, [System.IntPtr]::Zero)).GetHicon())
        $script:form.Icon = $icon
    } catch {
        Write-Log "Error setting application icon: $_" "Yellow"
    }
}

# Create the menu bar
$menuBar = New-Object System.Windows.Forms.MenuStrip
$menuBar.BackColor = [System.Drawing.SystemColors]::Control
$script:form.MainMenuStrip = $menuBar

# File Menu
$fileMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$fileMenu.Text = "File"

# New Configuration
$newConfigMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$newConfigMenuItem.Text = "New Configuration"
$newConfigMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::Control -bor [System.Windows.Forms.Keys]::N
$newConfigMenuItem.Add_Click({
    Write-Log "Resetting to new configuration." "White"
    # Clear text fields and list views
    $script:backupLocations.Items.Clear()
    $script:spreadsheetLocations.Items.Clear()
    $script:destinationLocation.Text = ""
    $script:skuListLocation.Text = ""
    $script:finalOutputLocation.Text = ""
    $script:textBoxSingleSpreadsheetFile.Text = ""
    
    # Reset script-level variables for label paths
    $script:LabelInputFolder = ""
    $script:LabelOutputFolder = ""
    $script:LabelParamTemplate = ""
    $script:LabelPrtTemplate = ""
    $script:LabelDymoTemplate = ""

    # Reset all checkboxes to false
    foreach ($checkbox in $script:optionCheckboxes) {
        $checkbox.Checked = $false
    }
    
    # Reset current config file path
    $script:CurrentConfigFile = $null
    $script:form.Text = "Spreadsheet Wrangler"
    Update-RecentFilesMenu # This will also save app settings if recent files are managed
})
$fileMenu.DropDownItems.Add($newConfigMenuItem) | Out-Null

# Open Configuration
$openConfigMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$openConfigMenuItem.Text = "Open Configuration..."
$openConfigMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::Control -bor [System.Windows.Forms.Keys]::O
$openConfigMenuItem.Add_Click({
    $openFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openFileDialog.Filter = "XML Files (*.xml)|*.xml|All Files (*.*)|*.*"
    $openFileDialog.Title = "Open Configuration"
    $openFileDialog.InitialDirectory = $PSScriptRoot
    
    if ($openFileDialog.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
        Load-Configuration -ConfigPath $openFileDialog.FileName
    }
})
$fileMenu.DropDownItems.Add($openConfigMenuItem) | Out-Null

# Save Configuration
$saveConfigMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$saveConfigMenuItem.Text = "Save Configuration"
$saveConfigMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::Control -bor [System.Windows.Forms.Keys]::S
$saveConfigMenuItem.Add_Click({
    # If we have a current config file, save to it, otherwise prompt for location
    if ($script:CurrentConfigFile -and (Test-Path $script:CurrentConfigFile)) {
        Save-Configuration -ConfigPath $script:CurrentConfigFile
    } else {
        $saveFileDialog = New-Object System.Windows.Forms.SaveFileDialog
        $saveFileDialog.Filter = "XML Files (*.xml)|*.xml|All Files (*.*)|*.*"
        $saveFileDialog.Title = "Save Configuration"
        $saveFileDialog.InitialDirectory = $PSScriptRoot
        $saveFileDialog.DefaultExt = "xml"
        
        if ($saveFileDialog.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
            Save-Configuration -ConfigPath $saveFileDialog.FileName
            $script:CurrentConfigFile = $saveFileDialog.FileName
        }
    }
})
$fileMenu.DropDownItems.Add($saveConfigMenuItem) | Out-Null

# Save Configuration As
$saveAsConfigMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$saveAsConfigMenuItem.Text = "Save Configuration As..."
$saveAsConfigMenuItem.Add_Click({
    $saveFileDialog = New-Object System.Windows.Forms.SaveFileDialog
    $saveFileDialog.Filter = "XML Files (*.xml)|*.xml|All Files (*.*)|*.*"
    $saveFileDialog.Title = "Save Configuration As"
    $saveFileDialog.InitialDirectory = $PSScriptRoot
    $saveFileDialog.DefaultExt = "xml"
    
    if ($saveFileDialog.ShowDialog() -eq [System.Windows.Forms.DialogResult]::OK) {
        Save-Configuration -ConfigPath $saveFileDialog.FileName
        $script:CurrentConfigFile = $saveFileDialog.FileName
    }
})
$fileMenu.DropDownItems.Add($saveAsConfigMenuItem) | Out-Null

# Recent Files submenu
$script:recentFilesMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$script:recentFilesMenuItem.Text = "Recent Files"
$fileMenu.DropDownItems.Add($script:recentFilesMenuItem) | Out-Null

# Initialize with empty item (will be updated by Update-RecentFilesMenu)
$noRecentFilesItem = New-Object System.Windows.Forms.ToolStripMenuItem
$noRecentFilesItem.Text = "(No recent files)"
$noRecentFilesItem.Enabled = $false
$script:recentFilesMenuItem.DropDownItems.Add($noRecentFilesItem) | Out-Null

# Separator
$fileMenu.DropDownItems.Add("-") | Out-Null

# Exit
$exitMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$exitMenuItem.Text = "Exit"
$exitMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::Alt -bor [System.Windows.Forms.Keys]::F4
$exitMenuItem.Add_Click({ $script:form.Close() })
$fileMenu.DropDownItems.Add($exitMenuItem) | Out-Null

# Labels Menu
$labelsMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$labelsMenu.Text = "Labels"

# Create Labels
$createLabelsMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$createLabelsMenuItem.Text = "Create Labels"
$createLabelsMenuItem.Add_Click({
    Show-CreateLabelsDialog
})
$labelsMenu.DropDownItems.Add($createLabelsMenuItem) | Out-Null

# View Menu
$viewMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$viewMenu.Text = "View"

# Dark Mode Toggle
$darkModeMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$darkModeMenuItem.Text = "Dark Mode"
$darkModeMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::Control -bor [System.Windows.Forms.Keys]::D
$darkModeMenuItem.CheckOnClick = $true
$darkModeMenuItem.Add_Click({
    Toggle-DarkMode
})
$viewMenu.DropDownItems.Add($darkModeMenuItem) | Out-Null

# Separator
$viewMenu.DropDownItems.Add("-") | Out-Null

# Preview
$previewMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$previewMenuItem.Text = "Preview Operations"
$previewMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::Control -bor [System.Windows.Forms.Keys]::P
$previewMenuItem.Add_Click({
    Show-Preview
})
$viewMenu.DropDownItems.Add($previewMenuItem) | Out-Null

# Clear Terminal
$clearTerminalMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$clearTerminalMenuItem.Text = "Clear Terminal"
$clearTerminalMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::Control -bor [System.Windows.Forms.Keys]::L
$clearTerminalMenuItem.Add_Click({
    $script:outputTextbox.Clear()
    Write-Log "Terminal cleared." "Gray"
})
$viewMenu.DropDownItems.Add($clearTerminalMenuItem) | Out-Null

# Export Terminal Log
$exportLogMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$exportLogMenuItem.Text = "Export Terminal Log..."
$exportLogMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::Control -bor [System.Windows.Forms.Keys]::E
$exportLogMenuItem.Add_Click({
    Export-TerminalLog
})
$viewMenu.DropDownItems.Add($exportLogMenuItem) | Out-Null

# Separator
$viewMenu.DropDownItems.Add("-") | Out-Null

# Copy Terminal to Clipboard
$copyTerminalMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$copyTerminalMenuItem.Text = "Copy Terminal to Clipboard"
$copyTerminalMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::Control -bor [System.Windows.Forms.Keys]::Shift -bor [System.Windows.Forms.Keys]::C
$copyTerminalMenuItem.Add_Click({
    if ($script:outputTextbox.Text.Length -gt 0) {
        [System.Windows.Forms.Clipboard]::SetText($script:outputTextbox.Text)
        Write-Log "Terminal content copied to clipboard." "Green"
    } else {
        Write-Log "Terminal is empty - nothing to copy." "Yellow"
    }
})
$viewMenu.DropDownItems.Add($copyTerminalMenuItem) | Out-Null

# Tools Menu
$toolsMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$toolsMenu.Text = "Tools"

# Run Operations
$runMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$runMenuItem.Text = "Run Operations"
$runMenuItem.ShortcutKeys = [System.Windows.Forms.Keys]::F5
$runMenuItem.Add_Click({
    if (-not $script:IsRunning) {
        $script:runButton.PerformClick()
    }
})
$toolsMenu.DropDownItems.Add($runMenuItem) | Out-Null

# Cancel Operations
$script:cancelMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$script:cancelMenuItem.Text = "Cancel Operations (Esc)"
$script:cancelMenuItem.Enabled = $false
$script:cancelMenuItem.Add_Click({
    if ($script:IsRunning) {
        $script:CancelRequested = $true
        Write-Log "Cancel requested..." "Yellow"
    }
})
$toolsMenu.DropDownItems.Add($script:cancelMenuItem) | Out-Null

# Separator
$toolsMenu.DropDownItems.Add("-") | Out-Null

# Validate Paths
$validatePathsMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$validatePathsMenuItem.Text = "Validate All Paths"
$validatePathsMenuItem.Add_Click({
    Write-Log "=== Validating Paths ===" "Cyan"
    $allValid = $true
    
    # Check backup locations
    foreach ($item in $script:backupLocations.Items) {
        if (Test-Path $item.Text) {
            Write-Log "  [OK] Backup: $($item.Text)" "Green"
        } else {
            Write-Log "  [MISSING] Backup: $($item.Text)" "Red"
            $allValid = $false
        }
    }
    
    # Check spreadsheet locations
    foreach ($item in $script:spreadsheetLocations.Items) {
        if (Test-Path $item.Text) {
            Write-Log "  [OK] Spreadsheet: $($item.Text)" "Green"
        } else {
            Write-Log "  [MISSING] Spreadsheet: $($item.Text)" "Red"
            $allValid = $false
        }
    }
    
    # Check other paths
    if ($script:destinationLocation.Text -and -not (Test-Path $script:destinationLocation.Text)) {
        Write-Log "  [MISSING] Destination: $($script:destinationLocation.Text)" "Red"
        $allValid = $false
    } elseif ($script:destinationLocation.Text) {
        Write-Log "  [OK] Destination: $($script:destinationLocation.Text)" "Green"
    }
    
    if ($allValid) {
        Write-Log "All paths are valid!" "Green"
    } else {
        Write-Log "Some paths are missing or invalid." "Yellow"
    }
})
$toolsMenu.DropDownItems.Add($validatePathsMenuItem) | Out-Null

# Open Output Folder
$openOutputMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$openOutputMenuItem.Text = "Open Output Folder"
$openOutputMenuItem.Add_Click({
    if ($script:destinationLocation.Text -and (Test-Path $script:destinationLocation.Text)) {
        Start-Process "explorer.exe" -ArgumentList $script:destinationLocation.Text
    } elseif ($script:finalOutputLocation.Text -and (Test-Path $script:finalOutputLocation.Text)) {
        Start-Process "explorer.exe" -ArgumentList $script:finalOutputLocation.Text
    } else {
        Write-Log "No valid output folder to open." "Yellow"
    }
})
$toolsMenu.DropDownItems.Add($openOutputMenuItem) | Out-Null

# Help Menu
$helpMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$helpMenu.Text = "Help"

# Check for Updates
$checkUpdatesMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$checkUpdatesMenuItem.Text = "Check for Updates"
$checkUpdatesMenuItem.Add_Click({
    Check-ForUpdates
})
$helpMenu.DropDownItems.Add($checkUpdatesMenuItem) | Out-Null

# Separator
$helpMenu.DropDownItems.Add("-") | Out-Null

# About
$aboutMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$aboutMenuItem.Text = "About"
$aboutMenuItem.Add_Click({
    $aboutForm = New-Object System.Windows.Forms.Form
    $aboutForm.Text = "About Spreadsheet Wrangler"
    # Adjust the size of the About dialog
    $aboutForm.Size = New-Object System.Drawing.Size(500, 400)
    $aboutForm.StartPosition = "CenterParent"
    $aboutForm.FormBorderStyle = "FixedDialog"
    $aboutForm.MaximizeBox = $false
    $aboutForm.MinimizeBox = $false
    
    $aboutPanel = New-Object System.Windows.Forms.TableLayoutPanel
    $aboutPanel.Dock = "Fill"
    $aboutPanel.RowCount = 4
    $aboutPanel.ColumnCount = 1
    # Adjust space allocation to reduce gaps
    $aboutPanel.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 40)))
    $aboutPanel.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 30)))
    $aboutPanel.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 15)))
    $aboutPanel.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 15)))
    $aboutForm.Controls.Add($aboutPanel) | Out-Null
    
    # Logo
    $logoPanel = New-Object System.Windows.Forms.Panel
    $logoPanel.Dock = "Fill"
    # Reduce padding to 10 pixels
    $logoPanel.Padding = New-Object System.Windows.Forms.Padding(10)
    $aboutPanel.Controls.Add($logoPanel, 0, 0) | Out-Null
    
    # Load the logo image
    $logoPath = Join-Path -Path $PSScriptRoot -ChildPath "logo.png"
    if (Test-Path -Path $logoPath) {
        try {
            $logoImage = [System.Drawing.Image]::FromFile($logoPath)
            $logoPictureBox = New-Object System.Windows.Forms.PictureBox
            $logoPictureBox.Image = $logoImage
            $logoPictureBox.SizeMode = "Zoom"
            $logoPictureBox.Dock = "Fill"
            $logoPanel.Controls.Add($logoPictureBox) | Out-Null
        } catch {
            Write-Log "Error loading logo: $_" "Yellow"
        }
    }
    
    # Main about text
    $aboutLabel = New-Object System.Windows.Forms.Label
    $aboutLabel.Text = "Spreadsheet Wrangler v2.4.0`n`nA powerful tool for backing up folders and combining spreadsheets.`n`nCreated by Bryant Welch`n`n(c) 2025 Bryant Welch. All Rights Reserved"
    $aboutLabel.AutoSize = $false
    $aboutLabel.Dock = "Fill"
    $aboutLabel.TextAlign = "MiddleCenter"
    $aboutPanel.Controls.Add($aboutLabel, 0, 1) | Out-Null
    
    # GitHub link
    $linkLabel = New-Object System.Windows.Forms.LinkLabel
    $linkLabel.Text = "https://github.com/BryantWelch/Spreadsheet-Wrangler"
    $linkLabel.AutoSize = $false
    $linkLabel.Dock = "Fill"
    $linkLabel.TextAlign = "MiddleCenter"
    $linkLabel.LinkColor = [System.Drawing.Color]::Blue
    $linkLabel.ActiveLinkColor = [System.Drawing.Color]::Red
    $linkLabel.Add_LinkClicked({
        param($senderObj, $e)
        Start-Process "https://github.com/BryantWelch/Spreadsheet-Wrangler"
    })
    $aboutPanel.Controls.Add($linkLabel, 0, 2) | Out-Null
    
    # OK button
    $okButton = New-Object System.Windows.Forms.Button
    $okButton.Text = "OK"
    $okButton.DialogResult = [System.Windows.Forms.DialogResult]::OK
    $okButton.Dock = "Fill"
    $okButton.Margin = New-Object System.Windows.Forms.Padding(150, 10, 150, 10)
    $aboutPanel.Controls.Add($okButton, 0, 3) | Out-Null
    $aboutForm.AcceptButton = $okButton
    
    $aboutForm.ShowDialog() | Out-Null
})
$helpMenu.DropDownItems.Add($aboutMenuItem) | Out-Null

# Spreadsheet Menu
$spreadsheetMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$spreadsheetMenu.Text = "Spreadsheet"

# Preview Spreadsheet
$previewSpreadsheetMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$previewSpreadsheetMenuItem.Text = "Preview Spreadsheet..."
$previewSpreadsheetMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx;*.xls)|*.xlsx;*.xls|CSV Files (*.csv)|*.csv|All Files (*.*)|*.*"
    $openDialog.Title = "Select Spreadsheet to Preview"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            Write-Log "=== Previewing: $($openDialog.FileName) ===" "Cyan"
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            
            if ($data.Count -eq 0) {
                Write-Log "Spreadsheet is empty." "Yellow"
                return
            }
            
            # Show first 10 rows
            $previewCount = [Math]::Min(10, $data.Count)
            Write-Log "Showing first $previewCount of $($data.Count) rows:" "White"
            Write-Log "" "White"
            
            # Get column headers
            $headers = $data[0].PSObject.Properties.Name
            Write-Log ("  " + ($headers -join " | ")) "Cyan"
            Write-Log ("  " + ("-" * 60)) "Gray"
            
            for ($i = 0; $i -lt $previewCount; $i++) {
                $row = $data[$i]
                $values = $headers | ForEach-Object { 
                    $val = $row.$_
                    if ($null -eq $val) { "" } else { $val.ToString().Substring(0, [Math]::Min(20, $val.ToString().Length)) }
                }
                Write-Log ("  " + ($values -join " | ")) "White"
            }
            
            if ($data.Count -gt 10) {
                Write-Log "  ... and $($data.Count - 10) more rows" "Gray"
            }
        } catch {
            Write-Log "Error previewing spreadsheet: $_" "Red"
        }
    }
})
$spreadsheetMenu.DropDownItems.Add($previewSpreadsheetMenuItem) | Out-Null

# Show Statistics
$statisticsMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$statisticsMenuItem.Text = "Show Statistics..."
$statisticsMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx;*.xls)|*.xlsx;*.xls|CSV Files (*.csv)|*.csv|All Files (*.*)|*.*"
    $openDialog.Title = "Select Spreadsheet for Statistics"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            Write-Log "=== Statistics: $($openDialog.FileName) ===" "Cyan"
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            
            if ($data.Count -eq 0) {
                Write-Log "Spreadsheet is empty." "Yellow"
                return
            }
            
            $headers = $data[0].PSObject.Properties.Name
            $fileInfo = Get-Item $openDialog.FileName
            
            Write-Log "FILE INFO:" "Yellow"
            Write-Log "  Name: $($fileInfo.Name)" "White"
            Write-Log "  Size: $([Math]::Round($fileInfo.Length / 1KB, 2)) KB" "White"
            Write-Log "  Modified: $($fileInfo.LastWriteTime)" "White"
            Write-Log "" "White"
            
            Write-Log "DATA INFO:" "Yellow"
            Write-Log "  Total Rows: $($data.Count)" "White"
            Write-Log "  Total Columns: $($headers.Count)" "White"
            Write-Log "" "White"
            
            Write-Log "COLUMNS:" "Yellow"
            foreach ($header in $headers) {
                $nonEmpty = ($data | Where-Object { -not [string]::IsNullOrWhiteSpace($_.$header) }).Count
                $emptyCount = $data.Count - $nonEmpty
                Write-Log "  $header - $nonEmpty values ($emptyCount empty)" "White"
            }
            
            # Check for potential duplicates
            $uniqueRows = ($data | Select-Object -Property * -Unique).Count
            if ($uniqueRows -lt $data.Count) {
                Write-Log "" "White"
                Write-Log "WARNING: $($data.Count - $uniqueRows) potential duplicate rows detected" "Yellow"
            }
        } catch {
            Write-Log "Error analyzing spreadsheet: $_" "Red"
        }
    }
})
$spreadsheetMenu.DropDownItems.Add($statisticsMenuItem) | Out-Null

# Detect Duplicates
$detectDuplicatesMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$detectDuplicatesMenuItem.Text = "Detect Duplicates..."
$detectDuplicatesMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx;*.xls)|*.xlsx;*.xls|CSV Files (*.csv)|*.csv|All Files (*.*)|*.*"
    $openDialog.Title = "Select Spreadsheet to Check for Duplicates"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            Write-Log "=== Checking Duplicates: $($openDialog.FileName) ===" "Cyan"
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            
            if ($data.Count -eq 0) {
                Write-Log "Spreadsheet is empty." "Yellow"
                return
            }
            
            # Group by all columns to find duplicates
            $grouped = $data | Group-Object -Property { $_.PSObject.Properties.Value -join "|" }
            $duplicates = $grouped | Where-Object { $_.Count -gt 1 }
            
            if ($duplicates.Count -eq 0) {
                Write-Log "No duplicate rows found!" "Green"
            } else {
                Write-Log "Found $($duplicates.Count) groups of duplicate rows:" "Yellow"
                $totalDupes = 0
                foreach ($group in $duplicates | Select-Object -First 10) {
                    $totalDupes += ($group.Count - 1)
                    $firstRow = $group.Group[0]
                    $preview = ($firstRow.PSObject.Properties | Select-Object -First 3 | ForEach-Object { "$($_.Name): $($_.Value)" }) -join ", "
                    Write-Log "  $($group.Count)x: $preview..." "White"
                }
                if ($duplicates.Count -gt 10) {
                    Write-Log "  ... and $($duplicates.Count - 10) more duplicate groups" "Gray"
                }
                Write-Log "" "White"
                Write-Log "Total duplicate rows that could be removed: $totalDupes" "Cyan"
            }
        } catch {
            Write-Log "Error checking duplicates: $_" "Red"
        }
    }
})
$spreadsheetMenu.DropDownItems.Add($detectDuplicatesMenuItem) | Out-Null

# Separator
$spreadsheetMenu.DropDownItems.Add("-") | Out-Null

# Validate Data
$validateDataMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$validateDataMenuItem.Text = "Validate Data..."
$validateDataMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx;*.xls)|*.xlsx;*.xls|CSV Files (*.csv)|*.csv|All Files (*.*)|*.*"
    $openDialog.Title = "Select Spreadsheet to Validate"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            Write-Log "=== Validating: $($openDialog.FileName) ===" "Cyan"
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            
            if ($data.Count -eq 0) {
                Write-Log "Spreadsheet is empty." "Yellow"
                return
            }
            
            $headers = $data[0].PSObject.Properties.Name
            $issues = @()
            
            # Check for empty columns
            foreach ($header in $headers) {
                $nonEmpty = ($data | Where-Object { -not [string]::IsNullOrWhiteSpace($_.$header) }).Count
                if ($nonEmpty -eq 0) {
                    $issues += "Column '$header' is completely empty"
                }
            }
            
            # Check for rows with all empty values
            $emptyRows = 0
            for ($i = 0; $i -lt $data.Count; $i++) {
                $row = $data[$i]
                $allEmpty = $true
                foreach ($header in $headers) {
                    if (-not [string]::IsNullOrWhiteSpace($row.$header)) {
                        $allEmpty = $false
                        break
                    }
                }
                if ($allEmpty) { $emptyRows++ }
            }
            if ($emptyRows -gt 0) {
                $issues += "$emptyRows completely empty rows found"
            }
            
            # Check for inconsistent data types in columns
            foreach ($header in $headers) {
                $values = $data | Where-Object { -not [string]::IsNullOrWhiteSpace($_.$header) } | ForEach-Object { $_.$header }
                if ($values.Count -gt 0) {
                    $types = $values | ForEach-Object { $_.GetType().Name } | Select-Object -Unique
                    if ($types.Count -gt 1) {
                        $issues += "Column '$header' has mixed data types: $($types -join ', ')"
                    }
                }
            }
            
            if ($issues.Count -eq 0) {
                Write-Log "Validation passed! No issues found." "Green"
            } else {
                Write-Log "Found $($issues.Count) issue(s):" "Yellow"
                foreach ($issue in $issues) {
                    Write-Log "  - $issue" "White"
                }
            }
        } catch {
            Write-Log "Error validating spreadsheet: $_" "Red"
        }
    }
})
$spreadsheetMenu.DropDownItems.Add($validateDataMenuItem) | Out-Null

# Compare Columns
$compareColumnsMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$compareColumnsMenuItem.Text = "Compare Column Headers..."
$compareColumnsMenuItem.Add_Click({
    if ($script:spreadsheetLocations.Items.Count -lt 2) {
        Write-Log "Add at least 2 spreadsheet folders to compare columns." "Yellow"
        return
    }
    
    Write-Log "=== Comparing Column Headers ===" "Cyan"
    $allHeaders = @{}
    
    foreach ($item in $script:spreadsheetLocations.Items) {
        $folderPath = $item.Text
        if (Test-Path $folderPath) {
            $files = Get-ChildItem -Path $folderPath -Filter "*.xlsx" -ErrorAction SilentlyContinue | Select-Object -First 1
            foreach ($file in $files) {
                try {
                    $data = Import-Excel -Path $file.FullName -ErrorAction Stop | Select-Object -First 1
                    if ($data) {
                        $headers = $data.PSObject.Properties.Name
                        $allHeaders[$file.Name] = $headers
                        Write-Log "  $($file.Name): $($headers.Count) columns" "White"
                    }
                } catch {
                    Write-Log "  Error reading $($file.Name): $_" "Red"
                }
            }
        }
    }
    
    if ($allHeaders.Count -ge 2) {
        $firstHeaders = $allHeaders.Values | Select-Object -First 1
        $allMatch = $true
        foreach ($key in $allHeaders.Keys) {
            $diff = Compare-Object -ReferenceObject $firstHeaders -DifferenceObject $allHeaders[$key]
            if ($diff) {
                $allMatch = $false
                Write-Log "  $key has different columns!" "Yellow"
            }
        }
        if ($allMatch) {
            Write-Log "All spreadsheets have matching column headers!" "Green"
        }
    }
})
$spreadsheetMenu.DropDownItems.Add($compareColumnsMenuItem) | Out-Null

# Separator
$spreadsheetMenu.DropDownItems.Add("-") | Out-Null

# Convert to CSV
$convertToCsvMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$convertToCsvMenuItem.Text = "Convert to CSV..."
$convertToCsvMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx;*.xls)|*.xlsx;*.xls"
    $openDialog.Title = "Select Excel File to Convert"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
        $saveDialog.Filter = "CSV Files (*.csv)|*.csv"
        $saveDialog.Title = "Save CSV As"
        $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + ".csv"
        
        if ($saveDialog.ShowDialog() -eq 'OK') {
            try {
                Write-Log "Converting to CSV..." "Cyan"
                $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
                $data | Export-Csv -Path $saveDialog.FileName -NoTypeInformation -Encoding UTF8
                Write-Log "Saved: $($saveDialog.FileName)" "Green"
                Write-Log "Rows exported: $($data.Count)" "White"
            } catch {
                Write-Log "Error converting: $_" "Red"
            }
        }
    }
})
$spreadsheetMenu.DropDownItems.Add($convertToCsvMenuItem) | Out-Null

# Merge Multiple Files
$mergeFilesMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$mergeFilesMenuItem.Text = "Quick Merge Files..."
$mergeFilesMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv"
    $openDialog.Title = "Select Files to Merge"
    $openDialog.Multiselect = $true
    
    if ($openDialog.ShowDialog() -eq 'OK' -and $openDialog.FileNames.Count -gt 1) {
        $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
        $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv"
        $saveDialog.Title = "Save Merged File As"
        $saveDialog.FileName = "Merged_$(Get-Date -Format 'yyyyMMdd_HHmmss').xlsx"
        
        if ($saveDialog.ShowDialog() -eq 'OK') {
            try {
                Write-Log "Merging $($openDialog.FileNames.Count) files..." "Cyan"
                $allData = @()
                $totalRows = 0
                
                foreach ($file in $openDialog.FileNames) {
                    $data = Import-Excel -Path $file -ErrorAction Stop
                    $allData += $data
                    $totalRows += $data.Count
                    Write-Log "  Added $($data.Count) rows from $(Split-Path $file -Leaf)" "White"
                }
                
                if ($saveDialog.FileName -match "\.csv$") {
                    $allData | Export-Csv -Path $saveDialog.FileName -NoTypeInformation -Encoding UTF8
                } else {
                    $allData | Export-Excel -Path $saveDialog.FileName -AutoSize
                }
                
                Write-Log "Merged $totalRows total rows to: $($saveDialog.FileName)" "Green"
            } catch {
                Write-Log "Error merging: $_" "Red"
            }
        }
    } else {
        Write-Log "Select at least 2 files to merge." "Yellow"
    }
})
$spreadsheetMenu.DropDownItems.Add($mergeFilesMenuItem) | Out-Null

# Separator
$spreadsheetMenu.DropDownItems.Add("-") | Out-Null

# Clean Data submenu
$cleanDataMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$cleanDataMenu.Text = "Clean Data"

# Remove Empty Rows
$removeEmptyRowsMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$removeEmptyRowsMenuItem.Text = "Remove Empty Rows..."
$removeEmptyRowsMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File to Clean"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            Write-Log "Removing empty rows..." "Cyan"
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $headers = $data[0].PSObject.Properties.Name
            
            $cleanData = $data | Where-Object {
                $row = $_
                $hasValue = $false
                foreach ($header in $headers) {
                    if (-not [string]::IsNullOrWhiteSpace($row.$header)) {
                        $hasValue = $true
                        break
                    }
                }
                $hasValue
            }
            
            $removed = $data.Count - $cleanData.Count
            
            if ($removed -gt 0) {
                $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_cleaned.xlsx"
                
                if ($saveDialog.ShowDialog() -eq 'OK') {
                    $cleanData | Export-Excel -Path $saveDialog.FileName -AutoSize
                    Write-Log "Removed $removed empty rows. Saved to: $($saveDialog.FileName)" "Green"
                }
            } else {
                Write-Log "No empty rows found." "Green"
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$cleanDataMenu.DropDownItems.Add($removeEmptyRowsMenuItem) | Out-Null

# Remove Duplicates
$removeDuplicatesMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$removeDuplicatesMenuItem.Text = "Remove Duplicates..."
$removeDuplicatesMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File to Remove Duplicates"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            Write-Log "Removing duplicates..." "Cyan"
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $cleanData = $data | Select-Object -Property * -Unique
            
            $removed = $data.Count - $cleanData.Count
            
            if ($removed -gt 0) {
                $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_deduped.xlsx"
                
                if ($saveDialog.ShowDialog() -eq 'OK') {
                    $cleanData | Export-Excel -Path $saveDialog.FileName -AutoSize
                    Write-Log "Removed $removed duplicate rows. Saved to: $($saveDialog.FileName)" "Green"
                }
            } else {
                Write-Log "No duplicates found." "Green"
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$cleanDataMenu.DropDownItems.Add($removeDuplicatesMenuItem) | Out-Null

# Trim Whitespace
$trimWhitespaceMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$trimWhitespaceMenuItem.Text = "Trim Whitespace..."
$trimWhitespaceMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File to Trim Whitespace"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            Write-Log "Trimming whitespace..." "Cyan"
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $headers = $data[0].PSObject.Properties.Name
            $trimCount = 0
            
            foreach ($row in $data) {
                foreach ($header in $headers) {
                    if ($row.$header -is [string]) {
                        $original = $row.$header
                        $trimmed = $row.$header.Trim()
                        if ($original -ne $trimmed) {
                            $row.$header = $trimmed
                            $trimCount++
                        }
                    }
                }
            }
            
            if ($trimCount -gt 0) {
                $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_trimmed.xlsx"
                
                if ($saveDialog.ShowDialog() -eq 'OK') {
                    $data | Export-Excel -Path $saveDialog.FileName -AutoSize
                    Write-Log "Trimmed $trimCount cells. Saved to: $($saveDialog.FileName)" "Green"
                }
            } else {
                Write-Log "No whitespace to trim." "Green"
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$cleanDataMenu.DropDownItems.Add($trimWhitespaceMenuItem) | Out-Null

$spreadsheetMenu.DropDownItems.Add($cleanDataMenu) | Out-Null

# Sort Data
$sortDataMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$sortDataMenuItem.Text = "Sort by Column..."
$sortDataMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File to Sort"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $headers = $data[0].PSObject.Properties.Name
            
            # Show column selection dialog
            $columnForm = New-Object System.Windows.Forms.Form
            $columnForm.Text = "Select Column to Sort By"
            $columnForm.Size = New-Object System.Drawing.Size(300, 200)
            $columnForm.StartPosition = "CenterParent"
            $columnForm.FormBorderStyle = "FixedDialog"
            
            $label = New-Object System.Windows.Forms.Label
            $label.Text = "Sort by column:"
            $label.Location = New-Object System.Drawing.Point(10, 20)
            $label.AutoSize = $true
            $columnForm.Controls.Add($label) | Out-Null
            
            $comboBox = New-Object System.Windows.Forms.ComboBox
            $comboBox.Location = New-Object System.Drawing.Point(10, 45)
            $comboBox.Width = 260
            $comboBox.DropDownStyle = "DropDownList"
            foreach ($h in $headers) { $comboBox.Items.Add($h) | Out-Null }
            $comboBox.SelectedIndex = 0
            $columnForm.Controls.Add($comboBox) | Out-Null
            
            $descendingCheck = New-Object System.Windows.Forms.CheckBox
            $descendingCheck.Text = "Descending order"
            $descendingCheck.Location = New-Object System.Drawing.Point(10, 80)
            $descendingCheck.AutoSize = $true
            $columnForm.Controls.Add($descendingCheck) | Out-Null
            
            $okBtn = New-Object System.Windows.Forms.Button
            $okBtn.Text = "Sort"
            $okBtn.Location = New-Object System.Drawing.Point(100, 120)
            $okBtn.DialogResult = "OK"
            $columnForm.Controls.Add($okBtn) | Out-Null
            $columnForm.AcceptButton = $okBtn
            
            if ($columnForm.ShowDialog() -eq 'OK') {
                $sortColumn = $comboBox.SelectedItem
                Write-Log "Sorting by '$sortColumn'..." "Cyan"
                
                if ($descendingCheck.Checked) {
                    $sortedData = $data | Sort-Object -Property $sortColumn -Descending
                } else {
                    $sortedData = $data | Sort-Object -Property $sortColumn
                }
                
                $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_sorted.xlsx"
                
                if ($saveDialog.ShowDialog() -eq 'OK') {
                    $sortedData | Export-Excel -Path $saveDialog.FileName -AutoSize
                    Write-Log "Sorted by '$sortColumn'. Saved to: $($saveDialog.FileName)" "Green"
                }
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$spreadsheetMenu.DropDownItems.Add($sortDataMenuItem) | Out-Null

# Separator
$spreadsheetMenu.DropDownItems.Add("-") | Out-Null

# Filter Rows submenu
$filterMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$filterMenu.Text = "Filter && Extract"

# Filter Rows by Value
$filterRowsMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$filterRowsMenuItem.Text = "Filter Rows by Value..."
$filterRowsMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File to Filter"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $headers = $data[0].PSObject.Properties.Name
            
            $filterForm = New-Object System.Windows.Forms.Form
            $filterForm.Text = "Filter Rows"
            $filterForm.Size = New-Object System.Drawing.Size(350, 250)
            $filterForm.StartPosition = "CenterParent"
            $filterForm.FormBorderStyle = "FixedDialog"
            
            $colLabel = New-Object System.Windows.Forms.Label
            $colLabel.Text = "Column:"
            $colLabel.Location = New-Object System.Drawing.Point(10, 20)
            $colLabel.AutoSize = $true
            $filterForm.Controls.Add($colLabel) | Out-Null
            
            $colCombo = New-Object System.Windows.Forms.ComboBox
            $colCombo.Location = New-Object System.Drawing.Point(10, 40)
            $colCombo.Width = 310
            $colCombo.DropDownStyle = "DropDownList"
            foreach ($h in $headers) { $colCombo.Items.Add($h) | Out-Null }
            $colCombo.SelectedIndex = 0
            $filterForm.Controls.Add($colCombo) | Out-Null
            
            $opLabel = New-Object System.Windows.Forms.Label
            $opLabel.Text = "Condition:"
            $opLabel.Location = New-Object System.Drawing.Point(10, 75)
            $opLabel.AutoSize = $true
            $filterForm.Controls.Add($opLabel) | Out-Null
            
            $opCombo = New-Object System.Windows.Forms.ComboBox
            $opCombo.Location = New-Object System.Drawing.Point(10, 95)
            $opCombo.Width = 150
            $opCombo.DropDownStyle = "DropDownList"
            @("Contains", "Equals", "Not Equals", "Starts With", "Ends With", "Is Empty", "Is Not Empty") | ForEach-Object { $opCombo.Items.Add($_) | Out-Null }
            $opCombo.SelectedIndex = 0
            $filterForm.Controls.Add($opCombo) | Out-Null
            
            $valLabel = New-Object System.Windows.Forms.Label
            $valLabel.Text = "Value:"
            $valLabel.Location = New-Object System.Drawing.Point(10, 130)
            $valLabel.AutoSize = $true
            $filterForm.Controls.Add($valLabel) | Out-Null
            
            $valText = New-Object System.Windows.Forms.TextBox
            $valText.Location = New-Object System.Drawing.Point(10, 150)
            $valText.Width = 310
            $filterForm.Controls.Add($valText) | Out-Null
            
            $okBtn = New-Object System.Windows.Forms.Button
            $okBtn.Text = "Filter"
            $okBtn.Location = New-Object System.Drawing.Point(120, 185)
            $okBtn.DialogResult = "OK"
            $filterForm.Controls.Add($okBtn) | Out-Null
            $filterForm.AcceptButton = $okBtn
            
            if ($filterForm.ShowDialog() -eq 'OK') {
                $col = $colCombo.SelectedItem
                $op = $opCombo.SelectedItem
                $val = $valText.Text
                
                Write-Log "Filtering by '$col' $op '$val'..." "Cyan"
                
                $filtered = switch ($op) {
                    "Contains" { $data | Where-Object { $_.$col -like "*$val*" } }
                    "Equals" { $data | Where-Object { $_.$col -eq $val } }
                    "Not Equals" { $data | Where-Object { $_.$col -ne $val } }
                    "Starts With" { $data | Where-Object { $_.$col -like "$val*" } }
                    "Ends With" { $data | Where-Object { $_.$col -like "*$val" } }
                    "Is Empty" { $data | Where-Object { [string]::IsNullOrWhiteSpace($_.$col) } }
                    "Is Not Empty" { $data | Where-Object { -not [string]::IsNullOrWhiteSpace($_.$col) } }
                }
                
                if ($filtered.Count -gt 0) {
                    $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                    $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                    $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_filtered.xlsx"
                    
                    if ($saveDialog.ShowDialog() -eq 'OK') {
                        $filtered | Export-Excel -Path $saveDialog.FileName -AutoSize
                        Write-Log "Filtered $($filtered.Count) of $($data.Count) rows. Saved to: $($saveDialog.FileName)" "Green"
                    }
                } else {
                    Write-Log "No rows matched the filter criteria." "Yellow"
                }
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$filterMenu.DropDownItems.Add($filterRowsMenuItem) | Out-Null

# Extract Columns
$extractColumnsMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$extractColumnsMenuItem.Text = "Extract Specific Columns..."
$extractColumnsMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $headers = $data[0].PSObject.Properties.Name
            
            $extractForm = New-Object System.Windows.Forms.Form
            $extractForm.Text = "Select Columns to Extract"
            $extractForm.Size = New-Object System.Drawing.Size(300, 400)
            $extractForm.StartPosition = "CenterParent"
            
            $listBox = New-Object System.Windows.Forms.CheckedListBox
            $listBox.Location = New-Object System.Drawing.Point(10, 10)
            $listBox.Size = New-Object System.Drawing.Size(260, 300)
            foreach ($h in $headers) { $listBox.Items.Add($h, $true) | Out-Null }
            $extractForm.Controls.Add($listBox) | Out-Null
            
            $okBtn = New-Object System.Windows.Forms.Button
            $okBtn.Text = "Extract"
            $okBtn.Location = New-Object System.Drawing.Point(100, 320)
            $okBtn.DialogResult = "OK"
            $extractForm.Controls.Add($okBtn) | Out-Null
            
            if ($extractForm.ShowDialog() -eq 'OK') {
                $selectedCols = @()
                for ($i = 0; $i -lt $listBox.Items.Count; $i++) {
                    if ($listBox.GetItemChecked($i)) {
                        $selectedCols += $listBox.Items[$i]
                    }
                }
                
                if ($selectedCols.Count -gt 0) {
                    $extracted = $data | Select-Object -Property $selectedCols
                    
                    $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                    $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                    $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_extracted.xlsx"
                    
                    if ($saveDialog.ShowDialog() -eq 'OK') {
                        $extracted | Export-Excel -Path $saveDialog.FileName -AutoSize
                        Write-Log "Extracted $($selectedCols.Count) columns. Saved to: $($saveDialog.FileName)" "Green"
                    }
                }
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$filterMenu.DropDownItems.Add($extractColumnsMenuItem) | Out-Null

# Sample Data
$sampleDataMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$sampleDataMenuItem.Text = "Sample Random Rows..."
$sampleDataMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            
            $input = [Microsoft.VisualBasic.Interaction]::InputBox("How many random rows to sample?", "Sample Size", "100")
            if ($input -and $input -match '^\d+$') {
                $sampleSize = [int]$input
                $sampled = $data | Get-Random -Count ([Math]::Min($sampleSize, $data.Count))
                
                $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_sample.xlsx"
                
                if ($saveDialog.ShowDialog() -eq 'OK') {
                    $sampled | Export-Excel -Path $saveDialog.FileName -AutoSize
                    Write-Log "Sampled $($sampled.Count) random rows. Saved to: $($saveDialog.FileName)" "Green"
                }
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$filterMenu.DropDownItems.Add($sampleDataMenuItem) | Out-Null

# Head/Tail
$headTailMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$headTailMenuItem.Text = "Get First/Last N Rows..."
$headTailMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            
            $htForm = New-Object System.Windows.Forms.Form
            $htForm.Text = "Get First/Last Rows"
            $htForm.Size = New-Object System.Drawing.Size(250, 150)
            $htForm.StartPosition = "CenterParent"
            
            $firstRadio = New-Object System.Windows.Forms.RadioButton
            $firstRadio.Text = "First"
            $firstRadio.Location = New-Object System.Drawing.Point(10, 20)
            $firstRadio.Checked = $true
            $htForm.Controls.Add($firstRadio) | Out-Null
            
            $lastRadio = New-Object System.Windows.Forms.RadioButton
            $lastRadio.Text = "Last"
            $lastRadio.Location = New-Object System.Drawing.Point(80, 20)
            $htForm.Controls.Add($lastRadio) | Out-Null
            
            $numBox = New-Object System.Windows.Forms.NumericUpDown
            $numBox.Location = New-Object System.Drawing.Point(10, 50)
            $numBox.Width = 100
            $numBox.Minimum = 1
            $numBox.Maximum = $data.Count
            $numBox.Value = [Math]::Min(100, $data.Count)
            $htForm.Controls.Add($numBox) | Out-Null
            
            $okBtn = New-Object System.Windows.Forms.Button
            $okBtn.Text = "OK"
            $okBtn.Location = New-Object System.Drawing.Point(80, 85)
            $okBtn.DialogResult = "OK"
            $htForm.Controls.Add($okBtn) | Out-Null
            
            if ($htForm.ShowDialog() -eq 'OK') {
                $n = [int]$numBox.Value
                $result = if ($firstRadio.Checked) { $data | Select-Object -First $n } else { $data | Select-Object -Last $n }
                
                $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                $suffix = if ($firstRadio.Checked) { "_first$n" } else { "_last$n" }
                $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "$suffix.xlsx"
                
                if ($saveDialog.ShowDialog() -eq 'OK') {
                    $result | Export-Excel -Path $saveDialog.FileName -AutoSize
                    Write-Log "Extracted $($result.Count) rows. Saved to: $($saveDialog.FileName)" "Green"
                }
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$filterMenu.DropDownItems.Add($headTailMenuItem) | Out-Null

$spreadsheetMenu.DropDownItems.Add($filterMenu) | Out-Null

# Transform submenu
$transformMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$transformMenu.Text = "Transform"

# Find and Replace
$findReplaceMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$findReplaceMenuItem.Text = "Find and Replace..."
$findReplaceMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $headers = $data[0].PSObject.Properties.Name
            
            $frForm = New-Object System.Windows.Forms.Form
            $frForm.Text = "Find and Replace"
            $frForm.Size = New-Object System.Drawing.Size(350, 220)
            $frForm.StartPosition = "CenterParent"
            
            $findLabel = New-Object System.Windows.Forms.Label
            $findLabel.Text = "Find:"
            $findLabel.Location = New-Object System.Drawing.Point(10, 20)
            $frForm.Controls.Add($findLabel) | Out-Null
            
            $findText = New-Object System.Windows.Forms.TextBox
            $findText.Location = New-Object System.Drawing.Point(10, 40)
            $findText.Width = 310
            $frForm.Controls.Add($findText) | Out-Null
            
            $replaceLabel = New-Object System.Windows.Forms.Label
            $replaceLabel.Text = "Replace with:"
            $replaceLabel.Location = New-Object System.Drawing.Point(10, 70)
            $frForm.Controls.Add($replaceLabel) | Out-Null
            
            $replaceText = New-Object System.Windows.Forms.TextBox
            $replaceText.Location = New-Object System.Drawing.Point(10, 90)
            $replaceText.Width = 310
            $frForm.Controls.Add($replaceText) | Out-Null
            
            $caseCheck = New-Object System.Windows.Forms.CheckBox
            $caseCheck.Text = "Case sensitive"
            $caseCheck.Location = New-Object System.Drawing.Point(10, 120)
            $frForm.Controls.Add($caseCheck) | Out-Null
            
            $okBtn = New-Object System.Windows.Forms.Button
            $okBtn.Text = "Replace All"
            $okBtn.Location = New-Object System.Drawing.Point(120, 150)
            $okBtn.DialogResult = "OK"
            $frForm.Controls.Add($okBtn) | Out-Null
            
            if ($frForm.ShowDialog() -eq 'OK') {
                $find = $findText.Text
                $replace = $replaceText.Text
                $replaceCount = 0
                
                foreach ($row in $data) {
                    foreach ($header in $headers) {
                        if ($row.$header -is [string] -and $row.$header -like "*$find*") {
                            if ($caseCheck.Checked) {
                                $row.$header = $row.$header.Replace($find, $replace)
                            } else {
                                $row.$header = $row.$header -replace [regex]::Escape($find), $replace
                            }
                            $replaceCount++
                        }
                    }
                }
                
                if ($replaceCount -gt 0) {
                    $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                    $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                    $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_replaced.xlsx"
                    
                    if ($saveDialog.ShowDialog() -eq 'OK') {
                        $data | Export-Excel -Path $saveDialog.FileName -AutoSize
                        Write-Log "Replaced $replaceCount occurrences. Saved to: $($saveDialog.FileName)" "Green"
                    }
                } else {
                    Write-Log "No matches found for '$find'." "Yellow"
                }
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$transformMenu.DropDownItems.Add($findReplaceMenuItem) | Out-Null

# Add Row Numbers
$addRowNumMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$addRowNumMenuItem.Text = "Add Row Numbers..."
$addRowNumMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            
            $rowNum = 1
            $data | ForEach-Object {
                $_ | Add-Member -NotePropertyName "RowNumber" -NotePropertyValue $rowNum -Force
                $rowNum++
            }
            
            # Reorder to put RowNumber first
            $headers = @("RowNumber") + ($data[0].PSObject.Properties.Name | Where-Object { $_ -ne "RowNumber" })
            $data = $data | Select-Object -Property $headers
            
            $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
            $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
            $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_numbered.xlsx"
            
            if ($saveDialog.ShowDialog() -eq 'OK') {
                $data | Export-Excel -Path $saveDialog.FileName -AutoSize
                Write-Log "Added row numbers. Saved to: $($saveDialog.FileName)" "Green"
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$transformMenu.DropDownItems.Add($addRowNumMenuItem) | Out-Null

# Split by Column Value
$splitByColumnMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$splitByColumnMenuItem.Text = "Split by Column Value..."
$splitByColumnMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File to Split"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $headers = $data[0].PSObject.Properties.Name
            
            $splitForm = New-Object System.Windows.Forms.Form
            $splitForm.Text = "Split by Column"
            $splitForm.Size = New-Object System.Drawing.Size(300, 150)
            $splitForm.StartPosition = "CenterParent"
            
            $label = New-Object System.Windows.Forms.Label
            $label.Text = "Split by column:"
            $label.Location = New-Object System.Drawing.Point(10, 20)
            $splitForm.Controls.Add($label) | Out-Null
            
            $combo = New-Object System.Windows.Forms.ComboBox
            $combo.Location = New-Object System.Drawing.Point(10, 45)
            $combo.Width = 260
            $combo.DropDownStyle = "DropDownList"
            foreach ($h in $headers) { $combo.Items.Add($h) | Out-Null }
            $combo.SelectedIndex = 0
            $splitForm.Controls.Add($combo) | Out-Null
            
            $okBtn = New-Object System.Windows.Forms.Button
            $okBtn.Text = "Split"
            $okBtn.Location = New-Object System.Drawing.Point(100, 80)
            $okBtn.DialogResult = "OK"
            $splitForm.Controls.Add($okBtn) | Out-Null
            
            if ($splitForm.ShowDialog() -eq 'OK') {
                $splitCol = $combo.SelectedItem
                $folderDialog = New-Object System.Windows.Forms.FolderBrowserDialog
                $folderDialog.Description = "Select output folder for split files"
                
                if ($folderDialog.ShowDialog() -eq 'OK') {
                    $groups = $data | Group-Object -Property $splitCol
                    $baseName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName)
                    
                    foreach ($group in $groups) {
                        $safeName = $group.Name -replace '[\\/:*?"<>|]', '_'
                        $outPath = Join-Path $folderDialog.SelectedPath "$baseName`_$safeName.xlsx"
                        $group.Group | Export-Excel -Path $outPath -AutoSize
                        Write-Log "  Created: $outPath ($($group.Count) rows)" "White"
                    }
                    Write-Log "Split into $($groups.Count) files by '$splitCol'." "Green"
                }
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$transformMenu.DropDownItems.Add($splitByColumnMenuItem) | Out-Null

# Rename Columns
$renameColumnsMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$renameColumnsMenuItem.Text = "Rename Columns..."
$renameColumnsMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $headers = $data[0].PSObject.Properties.Name
            
            $renameForm = New-Object System.Windows.Forms.Form
            $renameForm.Text = "Rename Columns"
            $renameForm.Size = New-Object System.Drawing.Size(400, 400)
            $renameForm.StartPosition = "CenterParent"
            
            $panel = New-Object System.Windows.Forms.Panel
            $panel.Location = New-Object System.Drawing.Point(10, 10)
            $panel.Size = New-Object System.Drawing.Size(360, 300)
            $panel.AutoScroll = $true
            $renameForm.Controls.Add($panel) | Out-Null
            
            $textBoxes = @{}
            $y = 0
            foreach ($h in $headers) {
                $lbl = New-Object System.Windows.Forms.Label
                $lbl.Text = $h
                $lbl.Location = New-Object System.Drawing.Point(0, $y)
                $lbl.Width = 150
                $panel.Controls.Add($lbl) | Out-Null
                
                $txt = New-Object System.Windows.Forms.TextBox
                $txt.Text = $h
                $txt.Location = New-Object System.Drawing.Point(160, $y)
                $txt.Width = 180
                $panel.Controls.Add($txt) | Out-Null
                $textBoxes[$h] = $txt
                $y += 25
            }
            
            $okBtn = New-Object System.Windows.Forms.Button
            $okBtn.Text = "Rename"
            $okBtn.Location = New-Object System.Drawing.Point(150, 320)
            $okBtn.DialogResult = "OK"
            $renameForm.Controls.Add($okBtn) | Out-Null
            
            if ($renameForm.ShowDialog() -eq 'OK') {
                $renamed = $data | Select-Object -Property ($headers | ForEach-Object {
                    $oldName = $_
                    $newName = $textBoxes[$_].Text
                    @{Name = $newName; Expression = [scriptblock]::Create("`$_.'$oldName'")}
                })
                
                $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_renamed.xlsx"
                
                if ($saveDialog.ShowDialog() -eq 'OK') {
                    $renamed | Export-Excel -Path $saveDialog.FileName -AutoSize
                    Write-Log "Columns renamed. Saved to: $($saveDialog.FileName)" "Green"
                }
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$transformMenu.DropDownItems.Add($renameColumnsMenuItem) | Out-Null

# Pivot Summary
$pivotMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$pivotMenuItem.Text = "Pivot Summary (Count by Value)..."
$pivotMenuItem.Add_Click({
    $openDialog = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog.Title = "Select File"
    
    if ($openDialog.ShowDialog() -eq 'OK') {
        try {
            $data = Import-Excel -Path $openDialog.FileName -ErrorAction Stop
            $headers = $data[0].PSObject.Properties.Name
            
            $pivotForm = New-Object System.Windows.Forms.Form
            $pivotForm.Text = "Pivot Summary"
            $pivotForm.Size = New-Object System.Drawing.Size(300, 150)
            $pivotForm.StartPosition = "CenterParent"
            
            $label = New-Object System.Windows.Forms.Label
            $label.Text = "Count by column:"
            $label.Location = New-Object System.Drawing.Point(10, 20)
            $pivotForm.Controls.Add($label) | Out-Null
            
            $combo = New-Object System.Windows.Forms.ComboBox
            $combo.Location = New-Object System.Drawing.Point(10, 45)
            $combo.Width = 260
            $combo.DropDownStyle = "DropDownList"
            foreach ($h in $headers) { $combo.Items.Add($h) | Out-Null }
            $combo.SelectedIndex = 0
            $pivotForm.Controls.Add($combo) | Out-Null
            
            $okBtn = New-Object System.Windows.Forms.Button
            $okBtn.Text = "Create Summary"
            $okBtn.Location = New-Object System.Drawing.Point(90, 80)
            $okBtn.DialogResult = "OK"
            $pivotForm.Controls.Add($okBtn) | Out-Null
            
            if ($pivotForm.ShowDialog() -eq 'OK') {
                $pivotCol = $combo.SelectedItem
                $summary = $data | Group-Object -Property $pivotCol | Select-Object @{N='Value';E={$_.Name}}, Count | Sort-Object Count -Descending
                
                Write-Log "=== Summary by '$pivotCol' ===" "Cyan"
                foreach ($row in $summary | Select-Object -First 20) {
                    Write-Log "  $($row.Value): $($row.Count)" "White"
                }
                if ($summary.Count -gt 20) {
                    Write-Log "  ... and $($summary.Count - 20) more values" "Gray"
                }
                
                $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                $saveDialog.FileName = [System.IO.Path]::GetFileNameWithoutExtension($openDialog.FileName) + "_summary.xlsx"
                
                if ($saveDialog.ShowDialog() -eq 'OK') {
                    $summary | Export-Excel -Path $saveDialog.FileName -AutoSize
                    Write-Log "Summary saved to: $($saveDialog.FileName)" "Green"
                }
            }
        } catch {
            Write-Log "Error: $_" "Red"
        }
    }
})
$transformMenu.DropDownItems.Add($pivotMenuItem) | Out-Null

$spreadsheetMenu.DropDownItems.Add($transformMenu) | Out-Null

# Compare submenu
$compareMenu = New-Object System.Windows.Forms.ToolStripMenuItem
$compareMenu.Text = "Compare"

# Compare Two Files
$compareTwoFilesMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$compareTwoFilesMenuItem.Text = "Compare Two Files..."
$compareTwoFilesMenuItem.Add_Click({
    $openDialog1 = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog1.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog1.Title = "Select FIRST File"
    
    if ($openDialog1.ShowDialog() -eq 'OK') {
        $openDialog2 = New-Object System.Windows.Forms.OpenFileDialog
        $openDialog2.Filter = "Excel Files (*.xlsx)|*.xlsx"
        $openDialog2.Title = "Select SECOND File"
        
        if ($openDialog2.ShowDialog() -eq 'OK') {
            try {
                Write-Log "=== Comparing Files ===" "Cyan"
                $data1 = Import-Excel -Path $openDialog1.FileName -ErrorAction Stop
                $data2 = Import-Excel -Path $openDialog2.FileName -ErrorAction Stop
                
                Write-Log "File 1: $(Split-Path $openDialog1.FileName -Leaf) ($($data1.Count) rows)" "White"
                Write-Log "File 2: $(Split-Path $openDialog2.FileName -Leaf) ($($data2.Count) rows)" "White"
                
                $headers1 = $data1[0].PSObject.Properties.Name
                $headers2 = $data2[0].PSObject.Properties.Name
                
                $colDiff = Compare-Object -ReferenceObject $headers1 -DifferenceObject $headers2
                if ($colDiff) {
                    Write-Log "Column differences:" "Yellow"
                    foreach ($d in $colDiff) {
                        $indicator = if ($d.SideIndicator -eq "<=") { "Only in File 1" } else { "Only in File 2" }
                        Write-Log "  $($d.InputObject): $indicator" "White"
                    }
                } else {
                    Write-Log "Columns match!" "Green"
                }
                
                Write-Log "Row count difference: $($data1.Count - $data2.Count)" "White"
            } catch {
                Write-Log "Error: $_" "Red"
            }
        }
    }
})
$compareMenu.DropDownItems.Add($compareTwoFilesMenuItem) | Out-Null

# Find Matching Rows
$findMatchingMenuItem = New-Object System.Windows.Forms.ToolStripMenuItem
$findMatchingMenuItem.Text = "Find Matching Rows..."
$findMatchingMenuItem.Add_Click({
    $openDialog1 = New-Object System.Windows.Forms.OpenFileDialog
    $openDialog1.Filter = "Excel Files (*.xlsx)|*.xlsx"
    $openDialog1.Title = "Select MAIN File"
    
    if ($openDialog1.ShowDialog() -eq 'OK') {
        $openDialog2 = New-Object System.Windows.Forms.OpenFileDialog
        $openDialog2.Filter = "Excel Files (*.xlsx)|*.xlsx"
        $openDialog2.Title = "Select LOOKUP File"
        
        if ($openDialog2.ShowDialog() -eq 'OK') {
            try {
                $data1 = Import-Excel -Path $openDialog1.FileName -ErrorAction Stop
                $data2 = Import-Excel -Path $openDialog2.FileName -ErrorAction Stop
                $headers1 = $data1[0].PSObject.Properties.Name
                
                $matchForm = New-Object System.Windows.Forms.Form
                $matchForm.Text = "Match on Column"
                $matchForm.Size = New-Object System.Drawing.Size(300, 150)
                $matchForm.StartPosition = "CenterParent"
                
                $label = New-Object System.Windows.Forms.Label
                $label.Text = "Match on column:"
                $label.Location = New-Object System.Drawing.Point(10, 20)
                $matchForm.Controls.Add($label) | Out-Null
                
                $combo = New-Object System.Windows.Forms.ComboBox
                $combo.Location = New-Object System.Drawing.Point(10, 45)
                $combo.Width = 260
                $combo.DropDownStyle = "DropDownList"
                foreach ($h in $headers1) { $combo.Items.Add($h) | Out-Null }
                $combo.SelectedIndex = 0
                $matchForm.Controls.Add($combo) | Out-Null
                
                $okBtn = New-Object System.Windows.Forms.Button
                $okBtn.Text = "Find Matches"
                $okBtn.Location = New-Object System.Drawing.Point(90, 80)
                $okBtn.DialogResult = "OK"
                $matchForm.Controls.Add($okBtn) | Out-Null
                
                if ($matchForm.ShowDialog() -eq 'OK') {
                    $matchCol = $combo.SelectedItem
                    Write-Log "Finding matches on '$matchCol'..." "Cyan"
                    
                    $lookupValues = $data2 | ForEach-Object { $_.$matchCol } | Where-Object { $_ }
                    $matches = $data1 | Where-Object { $_.$matchCol -in $lookupValues }
                    $nonMatches = $data1 | Where-Object { $_.$matchCol -notin $lookupValues }
                    
                    Write-Log "Matched: $($matches.Count) rows" "Green"
                    Write-Log "Not matched: $($nonMatches.Count) rows" "Yellow"
                    
                    $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
                    $saveDialog.Filter = "Excel Files (*.xlsx)|*.xlsx"
                    $saveDialog.FileName = "matched_rows.xlsx"
                    
                    if ($saveDialog.ShowDialog() -eq 'OK') {
                        $matches | Export-Excel -Path $saveDialog.FileName -AutoSize
                        Write-Log "Matched rows saved to: $($saveDialog.FileName)" "Green"
                    }
                }
            } catch {
                Write-Log "Error: $_" "Red"
            }
        }
    }
})
$compareMenu.DropDownItems.Add($findMatchingMenuItem) | Out-Null

$spreadsheetMenu.DropDownItems.Add($compareMenu) | Out-Null

# Add menus to menu bar
$menuBar.Items.Add($fileMenu) | Out-Null
$menuBar.Items.Add($viewMenu) | Out-Null
$menuBar.Items.Add($spreadsheetMenu) | Out-Null
$menuBar.Items.Add($toolsMenu) | Out-Null
$menuBar.Items.Add($labelsMenu) | Out-Null
$menuBar.Items.Add($helpMenu) | Out-Null

# Add menu bar to form
$script:form.Controls.Add($menuBar) | Out-Null

# Create a container panel to hold everything below the menu bar
$containerPanel = New-Object System.Windows.Forms.Panel
$containerPanel.Dock = "Fill"
$containerPanel.Padding = New-Object System.Windows.Forms.Padding(0, $menuBar.Height, 0, 0)
$script:form.Controls.Add($containerPanel) | Out-Null

# Create a table layout panel for the main layout
$mainLayout = New-Object System.Windows.Forms.TableLayoutPanel
$mainLayout.Dock = "Fill"
$mainLayout.RowCount = 1
$mainLayout.ColumnCount = 2
$mainLayout.ColumnStyles.Add((New-Object System.Windows.Forms.ColumnStyle([System.Windows.Forms.SizeType]::Percent, 30))) | Out-Null
$mainLayout.ColumnStyles.Add((New-Object System.Windows.Forms.ColumnStyle([System.Windows.Forms.SizeType]::Percent, 70))) | Out-Null
$containerPanel.Controls.Add($mainLayout) | Out-Null

# Initialize current config file variable
$script:CurrentConfigFile = $null

# Create tooltip component for the entire form
$script:toolTip = New-Object System.Windows.Forms.ToolTip
$script:toolTip.AutoPopDelay = 5000
$script:toolTip.InitialDelay = 500
$script:toolTip.ReshowDelay = 200
[void]($script:toolTip.ShowAlways = $true)

#region Left Panel
$leftPanel = New-Object System.Windows.Forms.Panel
$leftPanel.Dock = "Fill"
$leftPanel.Padding = New-Object System.Windows.Forms.Padding(10)
$mainLayout.Controls.Add($leftPanel, 0, 0) | Out-Null

# Create a table layout for the left panel
$leftLayout = New-Object System.Windows.Forms.TableLayoutPanel
$leftLayout.Dock = "Fill"
$leftLayout.RowCount = 6
$leftLayout.ColumnCount = 1
$leftLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 15))) | Out-Null # Backup - adjusted for 1-2 paths
$leftLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 25))) | Out-Null # Spreadsheet - adjusted for 3-4 paths
$leftLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 15))) | Out-Null # Combined
$leftLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 15))) | Out-Null # SKU List
$leftLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 15))) | Out-Null # Final Output
$leftLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 15))) | Out-Null # Run Button
$leftPanel.Controls.Add($leftLayout) | Out-Null

# Backup Locations Section
$backupPanel = New-Object System.Windows.Forms.GroupBox
$backupPanel.Text = "Backup Locations"
$backupPanel.Dock = "Fill"
$leftLayout.Controls.Add($backupPanel, 0, 0) | Out-Null

$backupLayout = New-Object System.Windows.Forms.TableLayoutPanel
$backupLayout.Dock = "Fill"
$backupLayout.RowCount = 2
$backupLayout.ColumnCount = 1
$backupLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 80))) | Out-Null
$backupLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Absolute, 35))) | Out-Null
$backupPanel.Controls.Add($backupLayout) | Out-Null

# List of backup locations
$script:backupLocations = New-Object System.Windows.Forms.ListView
$script:backupLocations.View = "Details"
$script:backupLocations.FullRowSelect = $true
# HorizontalScrollbar is not a valid ListView property - scrolling is automatic
$script:backupLocations.Scrollable = $true
$script:backupLocations.HeaderStyle = "None" # Remove header to save space
$script:backupLocations.Columns.Add("Folder Path", 400) | Out-Null # Set explicit width to force horizontal scrollbar
$script:backupLocations.Dock = "Fill"
$script:backupLocations.MinimumSize = New-Object System.Drawing.Size(0, 50) # Increased minimum height

# Context menu for backup locations
$backupContextMenu = New-Object System.Windows.Forms.ContextMenuStrip
$backupAddItem = New-Object System.Windows.Forms.ToolStripMenuItem
$backupAddItem.Text = "Add Folder..."
$backupAddItem.Add_Click({
    $folderPath = Select-FolderDialog
    if ($folderPath) {
        $item = New-Object System.Windows.Forms.ListViewItem($folderPath)
        $script:backupLocations.Items.Add($item) | Out-Null
        Write-Log "Added backup location: $folderPath" "Green"
        Update-StatusBar
    }
})
$backupContextMenu.Items.Add($backupAddItem) | Out-Null

$backupRemoveItem = New-Object System.Windows.Forms.ToolStripMenuItem
$backupRemoveItem.Text = "Remove Selected"
$backupRemoveItem.Add_Click({
    if ($script:backupLocations.SelectedItems.Count -gt 0) {
        foreach ($item in $script:backupLocations.SelectedItems) {
            Write-Log "Removed backup location: $($item.Text)" "Yellow"
            $script:backupLocations.Items.Remove($item)
        }
        Update-StatusBar
    }
})
$backupContextMenu.Items.Add($backupRemoveItem) | Out-Null

$backupContextMenu.Items.Add("-") | Out-Null

$backupOpenItem = New-Object System.Windows.Forms.ToolStripMenuItem
$backupOpenItem.Text = "Open in Explorer"
$backupOpenItem.Add_Click({
    if ($script:backupLocations.SelectedItems.Count -gt 0) {
        $path = $script:backupLocations.SelectedItems[0].Text
        if (Test-Path $path) {
            Start-Process "explorer.exe" -ArgumentList $path
        }
    }
})
$backupContextMenu.Items.Add($backupOpenItem) | Out-Null

$backupClearItem = New-Object System.Windows.Forms.ToolStripMenuItem
$backupClearItem.Text = "Clear All"
$backupClearItem.Add_Click({
    if ($script:backupLocations.Items.Count -gt 0) {
        $result = [System.Windows.Forms.MessageBox]::Show("Clear all backup locations?", "Confirm", "YesNo", "Question")
        if ($result -eq "Yes") {
            $script:backupLocations.Items.Clear()
            Write-Log "Cleared all backup locations." "Yellow"
            Update-StatusBar
        }
    }
})
$backupContextMenu.Items.Add($backupClearItem) | Out-Null

$script:backupLocations.ContextMenuStrip = $backupContextMenu

# Set tooltip for backup locations using the tooltip component
$script:toolTip.SetToolTip($script:backupLocations, "List of folders to back up. Select an item and press Delete or use the minus button to remove it.")
$script:backupLocations.Add_KeyDown({
    param($senderObj, $e)
    # Delete selected item when Delete key is pressed
    if ($e.KeyCode -eq 'Delete' -and $script:backupLocations.SelectedItems.Count -gt 0) {
        foreach ($item in $script:backupLocations.SelectedItems) {
            Write-Log "Removed backup location: $($item.Text)" "Yellow"
            $script:backupLocations.Items.Remove($item)
        }
    }
})
$backupLayout.Controls.Add($script:backupLocations, 0, 0) | Out-Null

# Button panel for backup locations
$backupButtonPanel = New-Object System.Windows.Forms.FlowLayoutPanel
$backupButtonPanel.Dock = "Fill"
$backupButtonPanel.FlowDirection = "RightToLeft"
$backupButtonPanel.WrapContents = $false
$backupButtonPanel.Padding = New-Object System.Windows.Forms.Padding(5, 2, 5, 2)
$backupLayout.Controls.Add($backupButtonPanel, 0, 1) | Out-Null

# Remove button for backup locations
$removeBackupBtn = New-Object System.Windows.Forms.Button
$removeBackupBtn.Text = "-"
$removeBackupBtn.Width = 40
$removeBackupBtn.Height = 25
$removeBackupBtn.Margin = New-Object System.Windows.Forms.Padding(3, 0, 3, 0)
$removeBackupBtn.FlatStyle = "Flat"
$script:toolTip.SetToolTip($removeBackupBtn, "Remove selected backup location")
$removeBackupBtn.Add_Click({
    if ($script:backupLocations.SelectedItems.Count -gt 0) {
        foreach ($item in $script:backupLocations.SelectedItems) {
            Write-Log "Removed backup location: $($item.Text)" "Yellow"
            $script:backupLocations.Items.Remove($item)
        }
    } else {
        Write-Log "Please select a backup location to remove" "Yellow"
    }
})
$backupButtonPanel.Controls.Add($removeBackupBtn) | Out-Null

# Add button for backup locations
$addBackupBtn = New-Object System.Windows.Forms.Button
$addBackupBtn.Text = "+"
$addBackupBtn.Width = 40
$addBackupBtn.Height = 25
$addBackupBtn.Margin = New-Object System.Windows.Forms.Padding(3, 0, 3, 0)
$addBackupBtn.FlatStyle = "Flat"
$script:toolTip.SetToolTip($addBackupBtn, "Add a new folder to back up")
$addBackupBtn.Add_Click({
    $folderPath = Select-FolderDialog
    if ($folderPath) {
        $item = New-Object System.Windows.Forms.ListViewItem($folderPath)
        $script:backupLocations.Items.Add($item)
        Write-Log "Added backup location: $folderPath"
    }
})
$backupButtonPanel.Controls.Add($addBackupBtn) | Out-Null

# Spreadsheet Folders Section
$spreadsheetPanel = New-Object System.Windows.Forms.GroupBox
$spreadsheetPanel.Text = "Spreadsheet Folder Locations"
$spreadsheetPanel.Dock = "Fill"
$leftLayout.Controls.Add($spreadsheetPanel, 0, 1) | Out-Null

$spreadsheetLayout = New-Object System.Windows.Forms.TableLayoutPanel
$spreadsheetLayout.Dock = "Fill"
$spreadsheetLayout.RowCount = 2
$spreadsheetLayout.ColumnCount = 1
$spreadsheetLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 85))) | Out-Null
$spreadsheetLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Absolute, 35))) | Out-Null
$spreadsheetPanel.Controls.Add($spreadsheetLayout) | Out-Null

# List of spreadsheet folder locations
$script:spreadsheetLocations = New-Object System.Windows.Forms.ListView
$script:spreadsheetLocations.View = "Details"
$script:spreadsheetLocations.FullRowSelect = $true
# HorizontalScrollbar is not a valid ListView property - scrolling is automatic
$script:spreadsheetLocations.Scrollable = $true
$script:spreadsheetLocations.HeaderStyle = "None" # Remove header to save space
$script:spreadsheetLocations.Columns.Add("Folder Path", 400) | Out-Null # Set explicit width to force horizontal scrollbar
$script:spreadsheetLocations.Dock = "Fill"
$script:spreadsheetLocations.MinimumSize = New-Object System.Drawing.Size(0, 90) # Maintain minimum height for 3-4 rows

# Context menu for spreadsheet locations
$spreadsheetContextMenu = New-Object System.Windows.Forms.ContextMenuStrip
$spreadsheetAddItem = New-Object System.Windows.Forms.ToolStripMenuItem
$spreadsheetAddItem.Text = "Add Folder..."
$spreadsheetAddItem.Add_Click({
    $folderPath = Select-FolderDialog
    if ($folderPath) {
        $item = New-Object System.Windows.Forms.ListViewItem($folderPath)
        $script:spreadsheetLocations.Items.Add($item) | Out-Null
        Write-Log "Added spreadsheet folder: $folderPath" "Green"
        Update-StatusBar
    }
})
$spreadsheetContextMenu.Items.Add($spreadsheetAddItem) | Out-Null

$spreadsheetRemoveItem = New-Object System.Windows.Forms.ToolStripMenuItem
$spreadsheetRemoveItem.Text = "Remove Selected"
$spreadsheetRemoveItem.Add_Click({
    if ($script:spreadsheetLocations.SelectedItems.Count -gt 0) {
        foreach ($item in $script:spreadsheetLocations.SelectedItems) {
            Write-Log "Removed spreadsheet folder: $($item.Text)" "Yellow"
            $script:spreadsheetLocations.Items.Remove($item)
        }
        Update-StatusBar
    }
})
$spreadsheetContextMenu.Items.Add($spreadsheetRemoveItem) | Out-Null

$spreadsheetContextMenu.Items.Add("-") | Out-Null

$spreadsheetOpenItem = New-Object System.Windows.Forms.ToolStripMenuItem
$spreadsheetOpenItem.Text = "Open in Explorer"
$spreadsheetOpenItem.Add_Click({
    if ($script:spreadsheetLocations.SelectedItems.Count -gt 0) {
        $path = $script:spreadsheetLocations.SelectedItems[0].Text
        if (Test-Path $path) {
            Start-Process "explorer.exe" -ArgumentList $path
        }
    }
})
$spreadsheetContextMenu.Items.Add($spreadsheetOpenItem) | Out-Null

$spreadsheetCountItem = New-Object System.Windows.Forms.ToolStripMenuItem
$spreadsheetCountItem.Text = "Show File Count"
$spreadsheetCountItem.Add_Click({
    if ($script:spreadsheetLocations.SelectedItems.Count -gt 0) {
        $path = $script:spreadsheetLocations.SelectedItems[0].Text
        if (Test-Path $path) {
            $xlsxCount = (Get-ChildItem -Path $path -Filter "*.xlsx" -ErrorAction SilentlyContinue).Count
            $xlsCount = (Get-ChildItem -Path $path -Filter "*.xls" -ErrorAction SilentlyContinue).Count
            $csvCount = (Get-ChildItem -Path $path -Filter "*.csv" -ErrorAction SilentlyContinue).Count
            Write-Log "File count for $path`:" "Cyan"
            Write-Log "  .xlsx: $xlsxCount | .xls: $xlsCount | .csv: $csvCount" "White"
        }
    }
})
$spreadsheetContextMenu.Items.Add($spreadsheetCountItem) | Out-Null

$spreadsheetContextMenu.Items.Add("-") | Out-Null

$spreadsheetClearItem = New-Object System.Windows.Forms.ToolStripMenuItem
$spreadsheetClearItem.Text = "Clear All"
$spreadsheetClearItem.Add_Click({
    if ($script:spreadsheetLocations.Items.Count -gt 0) {
        $result = [System.Windows.Forms.MessageBox]::Show("Clear all spreadsheet folders?", "Confirm", "YesNo", "Question")
        if ($result -eq "Yes") {
            $script:spreadsheetLocations.Items.Clear()
            Write-Log "Cleared all spreadsheet folders." "Yellow"
            Update-StatusBar
        }
    }
})
$spreadsheetContextMenu.Items.Add($spreadsheetClearItem) | Out-Null

$script:spreadsheetLocations.ContextMenuStrip = $spreadsheetContextMenu

# Set tooltip for spreadsheet locations using the tooltip component
$script:toolTip.SetToolTip($script:spreadsheetLocations, "List of folders containing spreadsheets to combine. Select an item and press Delete or use the minus button to remove it.")
$script:spreadsheetLocations.Add_KeyDown({
    param($senderObj, $e)
    # Delete selected item when Delete key is pressed
    if ($e.KeyCode -eq 'Delete' -and $script:spreadsheetLocations.SelectedItems.Count -gt 0) {
        foreach ($item in $script:spreadsheetLocations.SelectedItems) {
            Write-Log "Removed spreadsheet folder location: $($item.Text)" "Yellow"
            $script:spreadsheetLocations.Items.Remove($item)
        }
    }
})
$spreadsheetLayout.Controls.Add($script:spreadsheetLocations, 0, 0) | Out-Null

# Button panel for spreadsheet locations
$spreadsheetButtonPanel = New-Object System.Windows.Forms.FlowLayoutPanel
$spreadsheetButtonPanel.Dock = "Fill"
$spreadsheetButtonPanel.FlowDirection = "RightToLeft"
$spreadsheetButtonPanel.WrapContents = $false
$spreadsheetButtonPanel.Padding = New-Object System.Windows.Forms.Padding(5, 2, 5, 2)
$spreadsheetLayout.Controls.Add($spreadsheetButtonPanel, 0, 1) | Out-Null

# Remove button for spreadsheet locations
$removeSpreadsheetBtn = New-Object System.Windows.Forms.Button
$removeSpreadsheetBtn.Text = "-"
$removeSpreadsheetBtn.Width = 40
$removeSpreadsheetBtn.Height = 25
$removeSpreadsheetBtn.Margin = New-Object System.Windows.Forms.Padding(3, 0, 3, 0)
$removeSpreadsheetBtn.FlatStyle = "Flat"
$script:toolTip.SetToolTip($removeSpreadsheetBtn, "Remove selected spreadsheet folder location")
$removeSpreadsheetBtn.Add_Click({
    if ($script:spreadsheetLocations.SelectedItems.Count -gt 0) {
        foreach ($item in $script:spreadsheetLocations.SelectedItems) {
            Write-Log "Removed spreadsheet folder location: $($item.Text)" "Yellow"
            $script:spreadsheetLocations.Items.Remove($item)
        }
    } else {
        Write-Log "Please select a spreadsheet folder location to remove" "Yellow"
    }
})
$spreadsheetButtonPanel.Controls.Add($removeSpreadsheetBtn) | Out-Null

# Add button for spreadsheet locations
$addSpreadsheetBtn = New-Object System.Windows.Forms.Button
$addSpreadsheetBtn.Text = "+"
$addSpreadsheetBtn.Width = 40
$addSpreadsheetBtn.Height = 25
$addSpreadsheetBtn.Margin = New-Object System.Windows.Forms.Padding(3, 0, 3, 0)
$addSpreadsheetBtn.FlatStyle = "Flat"
$script:toolTip.SetToolTip($addSpreadsheetBtn, "Add a new folder containing spreadsheets to combine")
$addSpreadsheetBtn.Add_Click({
    $folderPath = Select-FolderDialog
    if ($folderPath) {
        $item = New-Object System.Windows.Forms.ListViewItem($folderPath)
        $script:spreadsheetLocations.Items.Add($item)
        Write-Log "Added spreadsheet folder location: $folderPath"
    }
})
$spreadsheetButtonPanel.Controls.Add($addSpreadsheetBtn) | Out-Null

# --- START: UI Elements for Single Spreadsheet Option ---
# Textbox for single spreadsheet file path
$script:textBoxSingleSpreadsheetFile = New-Object System.Windows.Forms.TextBox
$script:textBoxSingleSpreadsheetFile.ReadOnly = $true
$script:textBoxSingleSpreadsheetFile.Dock = [System.Windows.Forms.DockStyle]::Fill
$script:textBoxSingleSpreadsheetFile.BackColor = [System.Drawing.Color]::White
$script:toolTip.SetToolTip($script:textBoxSingleSpreadsheetFile, "Path to the single spreadsheet file to process")
$script:textBoxSingleSpreadsheetFile.Visible = $false # Initially hidden
$spreadsheetLayout.Controls.Add($script:textBoxSingleSpreadsheetFile, 0, 0) | Out-Null # Added to layout, will toggle visibility with $spreadsheetLocations

# Browse button for single spreadsheet file
$buttonBrowseSingleSpreadsheetFile = New-Object System.Windows.Forms.Button
$buttonBrowseSingleSpreadsheetFile.Text = "Browse..."
$buttonBrowseSingleSpreadsheetFile.Width = 80 # Standard browse button width
$buttonBrowseSingleSpreadsheetFile.Height = 25
$buttonBrowseSingleSpreadsheetFile.Margin = New-Object System.Windows.Forms.Padding(3, 0, 3, 0) # Consistent margin
$buttonBrowseSingleSpreadsheetFile.FlatStyle = [System.Windows.Forms.FlatStyle]::Flat
$script:toolTip.SetToolTip($buttonBrowseSingleSpreadsheetFile, "Select the single spreadsheet file to process")
$buttonBrowseSingleSpreadsheetFile.Visible = $false # Initially hidden
$buttonBrowseSingleSpreadsheetFile.Add_Click({
    $filePath = Select-FileDialog -Filter "Spreadsheet Files (*.xlsx;*.xls;*.csv)|*.xlsx;*.xls;*.csv|All files (*.*)|*.*" -Title "Select Single Spreadsheet File"
    if ($filePath) {
        $script:textBoxSingleSpreadsheetFile.Text = $filePath
        Write-Log "Selected single spreadsheet: $filePath"
    }
})
$spreadsheetButtonPanel.Controls.Add($buttonBrowseSingleSpreadsheetFile) | Out-Null # Add to existing button panel
# --- END: UI Elements for Single Spreadsheet Option ---

# Combined Destination Location Section
$destinationPanel = New-Object System.Windows.Forms.GroupBox
$destinationPanel.Text = "Combined Destination Location"
$destinationPanel.Dock = "Fill"
$leftLayout.Controls.Add($destinationPanel, 0, 2) | Out-Null

$destinationLayout = New-Object System.Windows.Forms.TableLayoutPanel
$destinationLayout.Dock = "Fill"
$destinationLayout.RowCount = 2
$destinationLayout.ColumnCount = 1
$destinationLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 85))) | Out-Null
$destinationLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Absolute, 35))) | Out-Null
$destinationPanel.Controls.Add($destinationLayout) | Out-Null

# Destination location display
$script:destinationLocation = New-Object System.Windows.Forms.TextBox
$script:destinationLocation.ReadOnly = $true
$script:destinationLocation.Dock = "Fill"
$script:destinationLocation.BackColor = [System.Drawing.Color]::White
$script:toolTip.SetToolTip($script:destinationLocation, "Location where combined spreadsheets will be saved")
$destinationLayout.Controls.Add($script:destinationLocation, 0, 0) | Out-Null

# Browse button for destination location
$browseDestinationBtn = New-Object System.Windows.Forms.Button
$browseDestinationBtn.Text = "Browse..."
$browseDestinationBtn.Dock = "Right"
$browseDestinationBtn.Width = 80
$browseDestinationBtn.Height = 25
$browseDestinationBtn.Margin = New-Object System.Windows.Forms.Padding(3, 2, 3, 2)
$browseDestinationBtn.FlatStyle = "Flat"
$script:toolTip.SetToolTip($browseDestinationBtn, "Select a folder where combined spreadsheets will be saved")
$browseDestinationBtn.Add_Click({
    $folderPath = Select-FolderDialog
    if ($folderPath) {
        $script:destinationLocation.Text = $folderPath
        Write-Log "Set combined destination location: $folderPath"
    }
})
$destinationLayout.Controls.Add($browseDestinationBtn, 0, 1) | Out-Null

# Run Button
$script:runButton = New-Object System.Windows.Forms.Button
$script:runButton.Text = "Run (F5)"
$script:runButton.Dock = "Fill"
$script:runButton.BackColor = [System.Drawing.Color]::FromArgb(0, 120, 215)
$script:runButton.ForeColor = [System.Drawing.Color]::White
$script:runButton.FlatStyle = "Flat"
$script:runButton.Font = New-Object System.Drawing.Font("Segoe UI", 12, [System.Drawing.FontStyle]::Bold)
$script:toolTip.SetToolTip($script:runButton, "Start the backup and spreadsheet combining process (F5)")
$script:runButton.Add_Click({
    # Check if this is a reset from "Finished!" state
    if ($script:runButton.Text -eq "Finished!") {
        # Reset button appearance
        $script:runButton.Text = "Run (F5)"
        $script:runButton.BackColor = [System.Drawing.Color]::FromArgb(0, 120, 215) # Blue
        $script:toolTip.SetToolTip($script:runButton, "Start the backup and spreadsheet combining process (F5)")
        $script:statusLabelLeft.Text = "Ready"
        return
    }
    
    # Set running state
    $script:IsRunning = $true
    $script:CancelRequested = $false
    $script:startTime = Get-Date
    
    # Change button appearance to "Running..."
    $script:runButton.Text = "Running... (Esc to cancel)"
    $script:runButton.BackColor = [System.Drawing.Color]::FromArgb(255, 204, 0) # Yellow/Orange
    $script:toolTip.SetToolTip($script:runButton, "Processing in progress... Press Escape to cancel")
    $script:statusLabelLeft.Text = "Processing..."
    $script:form.Refresh() # Force UI update
    
    # Clear previous output
    $script:outputTextbox.Clear()
    
    # Initialize log file if logging is enabled
    if ($script:optionCheckboxes[8].Checked) { # Log to File option
        $logFileName = "SpreadsheetWrangler_Log_$(Get-TimeStampString).txt"
        $script:LogFilePath = Join-Path -Path $PWD.Path -ChildPath $logFileName
        
        # Create the log file with header
        "Spreadsheet Wrangler Log - Started at $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')" | Out-File -FilePath $script:LogFilePath
        "--------------------------------------------------------------" | Out-File -FilePath $script:LogFilePath -Append
        
        Write-Log "Logging to file: $script:LogFilePath" "Cyan"
    } else {
        $script:LogFilePath = $null
    }
    
    Write-Log "Starting operations..." "Cyan"
    
    # Start backup process if not skipped
    if (-not $script:optionCheckboxes[0].Checked) {
        Start-BackupProcess
    } else {
        Write-Log "Backup process skipped due to 'Skip Backup' option." "Yellow"
    }
    
    # Start spreadsheet combining process if not skipped
    $combineSuccess = $true
    if (-not $script:optionCheckboxes[1].Checked) {
        $combineSuccess = Start-SpreadsheetCombiningProcess
    } else {
        Write-Log "Spreadsheet combining process skipped due to 'Skip Combine' option." "Yellow"
    }
    
    # Process SKU list if spreadsheet combining was successful (or skipped) and SKU list path is provided
    # When combining is skipped, we still need to have a valid destination location
    # Check if SKU processing should be skipped (checkboxSkipSkuProcessing is $optionCheckboxes[10] as it's the 11th checkbox, 0-indexed)
    $skipSkuProcessing = $script:optionCheckboxes[2].Checked

    if (-not $skipSkuProcessing -and $combineSuccess -and -not [string]::IsNullOrWhiteSpace($script:skuListLocation.Text) -and 
        -not [string]::IsNullOrWhiteSpace($script:finalOutputLocation.Text) -and 
        (-not $script:optionCheckboxes[1].Checked -or -not [string]::IsNullOrWhiteSpace($script:destinationLocation.Text))) {
        Write-Log "Starting SKU list processing..." "Cyan"
        $skuListSuccess = Process-SKUList -CombinedSpreadsheetPath $script:destinationLocation.Text -SKUListPath $script:skuListLocation.Text -FinalOutputPath $script:finalOutputLocation.Text
        
        if ($skuListSuccess) {
            Write-Log "SKU list processing completed successfully." "Green"
        } else {
            Write-Log "SKU list processing completed with errors." "Red"
        }
    } elseif ($skipSkuProcessing) {
        Write-Log "SKU list processing skipped due to 'Skip Sku Processing' option." "Yellow"
    } elseif ($combineSuccess) {
        if ([string]::IsNullOrWhiteSpace($script:skuListLocation.Text)) {
            Write-Log "SKU list processing skipped - No SKU list file specified." "Yellow"
        }
        if ([string]::IsNullOrWhiteSpace($script:finalOutputLocation.Text)) {
            Write-Log "SKU list processing skipped - No final output location specified." "Yellow"
        }
    }
    
    # Calculate elapsed time
    $elapsedTime = (Get-Date) - $script:startTime
    $elapsedFormatted = "{0:mm\:ss}" -f $elapsedTime
    
    Write-Log "All operations completed in $elapsedFormatted." "Cyan"
    
    # Add final log entry if logging is enabled
    if ($script:LogFilePath -and (Test-Path $script:LogFilePath)) {
        Write-Log "Log file saved to: $script:LogFilePath" "Yellow"
        "--------------------------------------------------------------" | Out-File -FilePath $script:LogFilePath -Append
        "Spreadsheet Wrangler Log - Completed at $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')" | Out-File -FilePath $script:LogFilePath -Append
    }
    
    # Reset running state
    $script:IsRunning = $false
    
    # Check if cancelled
    if ($script:CancelRequested) {
        $script:runButton.Text = "Cancelled"
        $script:runButton.BackColor = [System.Drawing.Color]::FromArgb(220, 53, 69) # Red
        $script:toolTip.SetToolTip($script:runButton, "Operation was cancelled. Click to reset.")
        $script:statusLabelLeft.Text = "Cancelled after $elapsedFormatted"
        Write-Log "Operation was cancelled by user." "Yellow"
    } else {
        # Change button appearance to "Finished!"
        $script:runButton.Text = "Finished!"
        $script:runButton.BackColor = [System.Drawing.Color]::FromArgb(40, 167, 69) # Green
        $script:toolTip.SetToolTip($script:runButton, "Operations completed. Click to reset.")
        $script:statusLabelLeft.Text = "Completed in $elapsedFormatted"
        
        # Play completion sound
        Play-CompletionSound
    }
})
# SKU List Location Section
$skuListPanel = New-Object System.Windows.Forms.GroupBox
$skuListPanel.Text = "SKU List Location"
$skuListPanel.Dock = "Fill"
$leftLayout.Controls.Add($skuListPanel, 0, 3) | Out-Null

$skuListLayout = New-Object System.Windows.Forms.TableLayoutPanel
$skuListLayout.Dock = "Fill"
$skuListLayout.RowCount = 2
$skuListLayout.ColumnCount = 1
$skuListLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 85))) | Out-Null
$skuListLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Absolute, 35))) | Out-Null
$skuListPanel.Controls.Add($skuListLayout) | Out-Null

# SKU List location display
$script:skuListLocation = New-Object System.Windows.Forms.TextBox
$script:skuListLocation.ReadOnly = $true
$script:skuListLocation.Dock = "Fill"
$script:skuListLocation.BackColor = [System.Drawing.Color]::White
$script:toolTip.SetToolTip($script:skuListLocation, "Location of the SKUList.csv file")
$skuListLayout.Controls.Add($script:skuListLocation, 0, 0) | Out-Null

# Browse button for SKU List location
$browseSkuListBtn = New-Object System.Windows.Forms.Button
$browseSkuListBtn.Text = "Browse..."
$browseSkuListBtn.Dock = "Right"
$browseSkuListBtn.Width = 80
$browseSkuListBtn.Height = 25
$browseSkuListBtn.Margin = New-Object System.Windows.Forms.Padding(3, 2, 3, 2)
$browseSkuListBtn.FlatStyle = "Flat"
$script:toolTip.SetToolTip($browseSkuListBtn, "Select the SKUList.csv file")
$browseSkuListBtn.Add_Click({
    $filePath = Select-FileDialog -Filter "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*" -Title "Select SKU List File"
    if ($filePath) {
        $script:skuListLocation.Text = $filePath
        Write-Log "Set SKU List location: $filePath"
    }
})
$skuListLayout.Controls.Add($browseSkuListBtn, 0, 1) | Out-Null

# Final Output Location Section
$finalOutputPanel = New-Object System.Windows.Forms.GroupBox
$finalOutputPanel.Text = "Final Output Location"
$finalOutputPanel.Dock = "Fill"
$leftLayout.Controls.Add($finalOutputPanel, 0, 4) | Out-Null

$finalOutputLayout = New-Object System.Windows.Forms.TableLayoutPanel
$finalOutputLayout.Dock = "Fill"
$finalOutputLayout.RowCount = 2
$finalOutputLayout.ColumnCount = 1
$finalOutputLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 85))) | Out-Null
$finalOutputLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Absolute, 35))) | Out-Null
$finalOutputPanel.Controls.Add($finalOutputLayout) | Out-Null

# Final Output location display
$script:finalOutputLocation = New-Object System.Windows.Forms.TextBox
$script:finalOutputLocation.ReadOnly = $true
$script:finalOutputLocation.Dock = "Fill"
$script:finalOutputLocation.BackColor = [System.Drawing.Color]::White
$script:toolTip.SetToolTip($script:finalOutputLocation, "Final GS##.xlsx file location")
$finalOutputLayout.Controls.Add($script:finalOutputLocation, 0, 0) | Out-Null

# Browse button for Final Output location
$browseFinalOutputBtn = New-Object System.Windows.Forms.Button
$browseFinalOutputBtn.Text = "Browse..."
$browseFinalOutputBtn.Dock = "Right"
$browseFinalOutputBtn.Width = 80
$browseFinalOutputBtn.Height = 25
$browseFinalOutputBtn.Margin = New-Object System.Windows.Forms.Padding(3, 2, 3, 2)
$browseFinalOutputBtn.FlatStyle = "Flat"
$script:toolTip.SetToolTip($browseFinalOutputBtn, "Select a folder for the final GS##.xlsx file")
$browseFinalOutputBtn.Add_Click({
    $folderPath = Select-FolderDialog
    if ($folderPath) {
        $script:finalOutputLocation.Text = $folderPath
        Write-Log "Set Final Output location: $folderPath"
    }
})
$finalOutputLayout.Controls.Add($browseFinalOutputBtn, 0, 1) | Out-Null

$leftLayout.Controls.Add($script:runButton, 0, 5) | Out-Null
#endregion

#region Right Panel
$rightPanel = New-Object System.Windows.Forms.Panel
$rightPanel.Dock = "Fill"
$rightPanel.Padding = New-Object System.Windows.Forms.Padding(10)
$mainLayout.Controls.Add($rightPanel, 1, 0) | Out-Null

# Create a table layout for the right panel
$rightLayout = New-Object System.Windows.Forms.TableLayoutPanel
$rightLayout.Dock = "Fill"
$rightLayout.RowCount = 3
$rightLayout.ColumnCount = 1
$rightLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 20))) | Out-Null
$rightLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 70))) | Out-Null
$rightLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 10))) | Out-Null
$rightPanel.Controls.Add($rightLayout) | Out-Null

# Options Panel
$optionsPanel = New-Object System.Windows.Forms.GroupBox
$optionsPanel.Text = "Options"
$optionsPanel.Dock = "Fill"
$rightLayout.Controls.Add($optionsPanel, 0, 0) | Out-Null

$optionsLayout = New-Object System.Windows.Forms.TableLayoutPanel
$optionsLayout.Dock = "Fill"
$optionsLayout.RowCount = 4 # Changed from 3
$optionsLayout.ColumnCount = 3 # Changed from 4
$optionsLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 25))) | Out-Null
$optionsLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 25))) | Out-Null
$optionsLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 25))) | Out-Null
$optionsLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 25))) | Out-Null
$optionsLayout.ColumnStyles.Add((New-Object System.Windows.Forms.ColumnStyle([System.Windows.Forms.SizeType]::Percent, 33.33))) | Out-Null
$optionsLayout.ColumnStyles.Add((New-Object System.Windows.Forms.ColumnStyle([System.Windows.Forms.SizeType]::Percent, 33.33))) | Out-Null
$optionsLayout.ColumnStyles.Add((New-Object System.Windows.Forms.ColumnStyle([System.Windows.Forms.SizeType]::Percent, 33.33))) | Out-Null
$optionsPanel.Controls.Add($optionsLayout) | Out-Null

# Create checkboxes for options with specific functionality
$script:optionCheckboxes = @()

# Option 1: Skip backup process
$script:optionCheckboxes += $checkbox1 = New-Object System.Windows.Forms.CheckBox
$checkbox1.Name = "checkboxSkipBackup"
$checkbox1.Text = "Skip Backup"
$checkbox1.Dock = "Fill"
$checkbox1.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox1, "Skip the backup process and only combine spreadsheets")
$optionsLayout.Controls.Add($checkbox1, 0, 0) | Out-Null

# Option 2: Skip Combine - Skip the spreadsheet combining process
$script:optionCheckboxes += $checkbox2 = New-Object System.Windows.Forms.CheckBox
$checkbox2.Name = "checkboxSkipCombine"
$checkbox2.Text = "Skip Combine"
$checkbox2.Dock = "Fill"
$checkbox2.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox2, "Skip the spreadsheet combining process and only perform backup and/or SKU list processing")
$optionsLayout.Controls.Add($checkbox2, 1, 0) | Out-Null

# Option 3: Exclude headers
$script:optionCheckboxes += $checkbox3 = New-Object System.Windows.Forms.CheckBox
$checkbox3.Name = "checkboxSkipSkuProcessing"
$checkbox3.Text = "Skip Sku Processing"
$checkbox3.Dock = "Fill"
$checkbox3.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox3, "If checked, the SKU list processing step will be skipped.")
$optionsLayout.Controls.Add($checkbox3, 2, 0) | Out-Null

# Option 4: Duplicate rows based on value in 'Add to Quantity' column
$script:optionCheckboxes += $checkbox4 = New-Object System.Windows.Forms.CheckBox
$checkbox4.Name = "checkboxDuplicateByQty"
$checkbox4.Text = "Duplicate by Qty"
$checkbox4.Dock = "Fill"
$checkbox4.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox4, "Duplicate rows based on the numeric value in the 'Add to Quantity' column. For example, a value of 7 will create 7 total rows.")
$optionsLayout.Controls.Add($checkbox4, 0, 1) | Out-Null

# Option 5: Normalize all quantities to '1'
$script:optionCheckboxes += $checkbox5 = New-Object System.Windows.Forms.CheckBox
$checkbox5.Name = "checkboxNormalizeQty"
$checkbox5.Text = "Normalize Qty to 1"
$checkbox5.Dock = "Fill"
$checkbox5.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox5, "Change all values in 'Add to Quantity' column to '1' (runs after duplication)")
$optionsLayout.Controls.Add($checkbox5, 1, 1) | Out-Null

# Option 6: Support multiple file formats
$script:optionCheckboxes += $checkbox6 = New-Object System.Windows.Forms.CheckBox
$checkbox6.Name = "checkboxAllFormats"
$checkbox6.Text = "All Formats"
$checkbox6.Dock = "Fill"
$checkbox6.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox6, "Process all spreadsheet formats (.xlsx, .xls, .csv)")
$optionsLayout.Controls.Add($checkbox6, 2, 1) | Out-Null

# Option 7: BLANK - Insert separator rows between spreadsheets
$script:optionCheckboxes += $checkbox7 = New-Object System.Windows.Forms.CheckBox
$checkbox7.Name = "checkboxBlankSeparator"
$checkbox7.Text = "BLANK"
$checkbox7.Dock = "Fill"
$checkbox7.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox7, "Insert 'BLANK' rows between data from different spreadsheets")
$optionsLayout.Controls.Add($checkbox7, 0, 2) | Out-Null

# Option 8: Reverse, Reverse - Reverse the order of data rows
$script:optionCheckboxes += $checkbox8 = New-Object System.Windows.Forms.CheckBox
$checkbox8.Name = "checkboxReverseRows"
$checkbox8.Text = "Reverse, Reverse"
$checkbox8.Dock = "Fill"
$checkbox8.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox8, "Reverse the order of data rows in the final combined spreadsheet")
$optionsLayout.Controls.Add($checkbox8, 1, 2) | Out-Null

# Option 9: Log to File
$script:optionCheckboxes += $checkbox9 = New-Object System.Windows.Forms.CheckBox
$checkbox9.Name = "checkboxLogToFile"
$checkbox9.Text = "Log to File"
$checkbox9.Dock = "Fill"
$checkbox9.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox9, "Save terminal output to a log file in the application directory")
$optionsLayout.Controls.Add($checkbox9, 2, 2) | Out-Null

# Option 10: Single Spreadsheet Duplication
$script:optionCheckboxes += ($checkbox10 = New-Object System.Windows.Forms.CheckBox)
$checkbox10.Name = "checkboxSingleSpreadsheet"
$checkbox10.Text = "Single Spreadsheet"
$checkbox10.Dock = "Fill"
$checkbox10.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox10, "Enable Single Spreadsheet mode. This changes the UI for spreadsheet selection and disables the 'No Headers' option.")
$optionsLayout.Controls.Add($checkbox10, 0, 3) | Out-Null

# Option 11: No Headers
$script:optionCheckboxes += ($checkboxSkipSkuProcessing = New-Object System.Windows.Forms.CheckBox)
$checkboxSkipSkuProcessing.Name = "checkboxNoHeaders"
$checkboxSkipSkuProcessing.Text = "No Headers"
$checkboxSkipSkuProcessing.Dock = "Fill"
$checkboxSkipSkuProcessing.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkboxSkipSkuProcessing, "Exclude headers when combining spreadsheets")
$optionsLayout.Controls.Add($checkboxSkipSkuProcessing, 1, 3) | Out-Null

# Option 12: Reserved for future use
$script:optionCheckboxes += $checkbox12 = New-Object System.Windows.Forms.CheckBox
$checkbox12.Name = "checkboxOption12"
$checkbox12.Text = "Option 12"
$checkbox12.Dock = "Fill"
$checkbox12.Margin = New-Object System.Windows.Forms.Padding(5)
$script:toolTip.SetToolTip($checkbox12, "Reserved for future use")
$optionsLayout.Controls.Add($checkbox12, 2, 3) | Out-Null

# Event handler for Single Spreadsheet checkbox ($checkbox10, which is $optionCheckboxes[9])
$checkbox10.Add_CheckedChanged({
    param($senderObj, $e)
    if ($senderObj.Checked) {
        # Single Spreadsheet mode ENABLED
        $spreadsheetPanel.Text = "Single Spreadsheet Location:"
        
        $script:spreadsheetLocations.Visible = $false
        $addSpreadsheetBtn.Visible = $false
        $removeSpreadsheetBtn.Visible = $false
        
        $script:textBoxSingleSpreadsheetFile.Visible = $true
        $buttonBrowseSingleSpreadsheetFile.Visible = $true
        
        # Disable and uncheck "No Headers" ($optionCheckboxes[10] is $checkboxSkipSkuProcessing)
        $script:optionCheckboxes[10].Enabled = $false
        $script:optionCheckboxes[10].Checked = $false
        $script:toolTip.SetToolTip($script:optionCheckboxes[10], "No Headers option is not applicable with Single Spreadsheet mode.")

        # Disable and uncheck "BLANK" ($optionCheckboxes[6] is $checkbox7)
        $script:optionCheckboxes[6].Enabled = $false
        $script:optionCheckboxes[6].Checked = $false
        $script:toolTip.SetToolTip($script:optionCheckboxes[6], "BLANK option is not applicable with Single Spreadsheet mode.")

    } else {
        # Single Spreadsheet mode DISABLED (Multi-folder mode)
        $spreadsheetPanel.Text = "Spreadsheet Folder Locations:"
        
        $script:spreadsheetLocations.Visible = $true
        $addSpreadsheetBtn.Visible = $true
        $removeSpreadsheetBtn.Visible = $true
        
        $script:textBoxSingleSpreadsheetFile.Visible = $false
        $buttonBrowseSingleSpreadsheetFile.Visible = $false
        
        # Enable "No Headers" ($optionCheckboxes[10] is $checkboxSkipSkuProcessing)
        $script:optionCheckboxes[10].Enabled = $true
        $originalNoHeadersTooltip = "Exclude headers when combining spreadsheets"
        $script:toolTip.SetToolTip($script:optionCheckboxes[10], $originalNoHeadersTooltip)

        # Enable "BLANK" ($optionCheckboxes[6] is $checkbox7)
        $script:optionCheckboxes[6].Enabled = $true
        $originalBlankTooltip = "Insert 'BLANK' rows between data from different spreadsheets"
        $script:toolTip.SetToolTip($script:optionCheckboxes[6], $originalBlankTooltip)
    }
})

# Output Panel
$outputPanel = New-Object System.Windows.Forms.GroupBox
$outputPanel.Text = "Terminal Output"
$outputPanel.Dock = "Fill"
$rightLayout.Controls.Add($outputPanel, 0, 1) | Out-Null

# Create a layout for the output panel to include a clear button
$outputLayout = New-Object System.Windows.Forms.TableLayoutPanel
$outputLayout.Dock = "Fill"
$outputLayout.RowCount = 2
$outputLayout.ColumnCount = 1
$outputLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Percent, 100))) | Out-Null
$outputLayout.RowStyles.Add((New-Object System.Windows.Forms.RowStyle([System.Windows.Forms.SizeType]::Absolute, 30))) | Out-Null
$outputPanel.Controls.Add($outputLayout) | Out-Null

# Terminal output textbox
$script:outputTextbox = New-Object System.Windows.Forms.RichTextBox
$script:outputTextbox.Dock = "Fill"
$script:outputTextbox.ReadOnly = $true
$script:outputTextbox.BackColor = [System.Drawing.Color]::Black
$script:outputTextbox.ForeColor = [System.Drawing.Color]::LightGreen
$script:outputTextbox.Font = New-Object System.Drawing.Font("Consolas", 10)
$script:toolTip.SetToolTip($script:outputTextbox, "Displays real-time progress and status information")
$outputLayout.Controls.Add($script:outputTextbox, 0, 0) | Out-Null

# Clear Terminal button
$clearTerminalBtn = New-Object System.Windows.Forms.Button
$clearTerminalBtn.Text = "Clear Terminal"
$clearTerminalBtn.Dock = "Right"
$clearTerminalBtn.Width = 100
$clearTerminalBtn.Margin = New-Object System.Windows.Forms.Padding(5, 2, 5, 2)
$script:toolTip.SetToolTip($clearTerminalBtn, "Clear all terminal output")
$clearTerminalBtn.Add_Click({
    $script:outputTextbox.Clear()
    Write-Log "Terminal cleared." "Gray"
})
$outputLayout.Controls.Add($clearTerminalBtn, 0, 1) | Out-Null

# Progress Bar
$script:progressBar = New-Object System.Windows.Forms.ProgressBar
$script:progressBar.Dock = "Fill"
$script:progressBar.Style = "Continuous"
$script:progressBar.Value = 0
$script:toolTip.SetToolTip($script:progressBar, "Shows overall progress of the current operation")
$rightLayout.Controls.Add($script:progressBar, 0, 2) | Out-Null
#endregion

#region Status Bar
$script:statusBar = New-Object System.Windows.Forms.StatusStrip
$script:statusLabelLeft = New-Object System.Windows.Forms.ToolStripStatusLabel
$script:statusLabelLeft.Text = "Ready"
$script:statusLabelLeft.Spring = $true
$script:statusLabelLeft.TextAlign = "MiddleLeft"
$script:statusLabelCenter = New-Object System.Windows.Forms.ToolStripStatusLabel
$script:statusLabelCenter.Text = ""
$script:statusLabelRight = New-Object System.Windows.Forms.ToolStripStatusLabel
$script:statusLabelRight.Text = ""
$script:statusBar.Items.Add($script:statusLabelLeft) | Out-Null
$script:statusBar.Items.Add($script:statusLabelCenter) | Out-Null
$script:statusBar.Items.Add($script:statusLabelRight) | Out-Null
$script:form.Controls.Add($script:statusBar) | Out-Null
#endregion

#region Keyboard Shortcuts
$script:form.Add_KeyDown({
    param($senderObj, $e)
    
    # Ctrl+S - Save Configuration
    if ($e.Control -and $e.KeyCode -eq 'S') {
        $e.SuppressKeyPress = $true
        if ($script:CurrentConfigFile) {
            Save-Configuration -ConfigPath $script:CurrentConfigFile
            Write-Log "Configuration saved to: $script:CurrentConfigFile" "Green"
        } else {
            $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
            $saveDialog.Filter = "XML Files (*.xml)|*.xml"
            $saveDialog.Title = "Save Configuration"
            if ($saveDialog.ShowDialog() -eq 'OK') {
                Save-Configuration -ConfigPath $saveDialog.FileName
                $script:CurrentConfigFile = $saveDialog.FileName
                Write-Log "Configuration saved to: $($saveDialog.FileName)" "Green"
            }
        }
    }
    
    # Ctrl+O - Open Configuration
    if ($e.Control -and $e.KeyCode -eq 'O') {
        $e.SuppressKeyPress = $true
        $openDialog = New-Object System.Windows.Forms.OpenFileDialog
        $openDialog.Filter = "XML Files (*.xml)|*.xml"
        $openDialog.Title = "Open Configuration"
        if ($openDialog.ShowDialog() -eq 'OK') {
            Load-Configuration -ConfigPath $openDialog.FileName
            $script:CurrentConfigFile = $openDialog.FileName
            Add-RecentFile -FilePath $openDialog.FileName
            Update-RecentFilesMenu
            Write-Log "Configuration loaded from: $($openDialog.FileName)" "Green"
        }
    }
    
    # F5 - Run
    if ($e.KeyCode -eq 'F5' -and -not $script:IsRunning) {
        $e.SuppressKeyPress = $true
        $script:runButton.PerformClick()
    }
    
    # Escape - Cancel (if running)
    if ($e.KeyCode -eq 'Escape' -and $script:IsRunning) {
        $e.SuppressKeyPress = $true
        $script:CancelRequested = $true
        Write-Log "Cancel requested... waiting for current operation to complete." "Yellow"
        $script:statusLabelLeft.Text = "Cancelling..."
    }
    
    # Ctrl+L - Clear Terminal
    if ($e.Control -and $e.KeyCode -eq 'L') {
        $e.SuppressKeyPress = $true
        $script:outputTextbox.Clear()
        Write-Log "Terminal cleared." "Gray"
    }
    
    # Ctrl+D - Toggle Dark Mode
    if ($e.Control -and $e.KeyCode -eq 'D') {
        $e.SuppressKeyPress = $true
        Toggle-DarkMode
    }
    
    # Ctrl+E - Export Terminal Log
    if ($e.Control -and $e.KeyCode -eq 'E') {
        $e.SuppressKeyPress = $true
        Export-TerminalLog
    }
    
    # Ctrl+P - Preview Mode
    if ($e.Control -and $e.KeyCode -eq 'P') {
        $e.SuppressKeyPress = $true
        Show-Preview
    }
})
#endregion

#region Dark Mode Function
function Toggle-DarkMode {
    $script:DarkMode = -not $script:DarkMode
    
    if ($script:DarkMode) {
        # Dark mode colors
        $bgColor = [System.Drawing.Color]::FromArgb(45, 45, 48)
        $fgColor = [System.Drawing.Color]::White
        $controlBg = [System.Drawing.Color]::FromArgb(60, 60, 65)
        $script:form.BackColor = $bgColor
        $script:form.ForeColor = $fgColor
        Write-Log "Dark mode enabled. Press Ctrl+D to toggle." "Cyan"
    } else {
        # Light mode colors
        $bgColor = [System.Drawing.SystemColors]::Control
        $fgColor = [System.Drawing.SystemColors]::ControlText
        $script:form.BackColor = $bgColor
        $script:form.ForeColor = $fgColor
        Write-Log "Light mode enabled. Press Ctrl+D to toggle." "Cyan"
    }
    $script:statusLabelRight.Text = if ($script:DarkMode) { "Dark Mode" } else { "" }
}
#endregion

#region Export Terminal Log Function
function Export-TerminalLog {
    $saveDialog = New-Object System.Windows.Forms.SaveFileDialog
    $saveDialog.Filter = "Text Files (*.txt)|*.txt|All Files (*.*)|*.*"
    $saveDialog.Title = "Export Terminal Log"
    $saveDialog.FileName = "SpreadsheetWrangler_Log_$(Get-Date -Format 'yyyyMMdd_HHmmss').txt"
    
    if ($saveDialog.ShowDialog() -eq 'OK') {
        try {
            $script:outputTextbox.Text | Out-File -FilePath $saveDialog.FileName -Encoding UTF8
            Write-Log "Terminal log exported to: $($saveDialog.FileName)" "Green"
            [System.Windows.Forms.MessageBox]::Show("Log exported successfully!", "Export Complete", "OK", "Information") | Out-Null
        } catch {
            Write-Log "Error exporting log: $_" "Red"
        }
    }
}
#endregion

#region Preview Function
function Show-Preview {
    $previewText = "=== PREVIEW - What will be processed ===`n`n"
    
    # Backup folders
    $previewText += "BACKUP FOLDERS:`n"
    if ($script:backupLocations.Items.Count -eq 0) {
        $previewText += "  (none)`n"
    } else {
        foreach ($item in $script:backupLocations.Items) {
            $folderPath = $item.Text
            if (Test-Path $folderPath) {
                $fileCount = (Get-ChildItem -Path $folderPath -Recurse -File -ErrorAction SilentlyContinue).Count
                $previewText += "  - $folderPath ($fileCount files)`n"
            } else {
                $previewText += "  - $folderPath (NOT FOUND)`n"
            }
        }
    }
    
    # Spreadsheet folders
    $previewText += "`nSPREADSHEET FOLDERS:`n"
    if ($script:optionCheckboxes[9].Checked) {
        # Single spreadsheet mode
        $filePath = $script:textBoxSingleSpreadsheetFile.Text
        if ($filePath -and (Test-Path $filePath)) {
            $previewText += "  - $filePath (Single file mode)`n"
        } else {
            $previewText += "  (no file selected)`n"
        }
    } else {
        if ($script:spreadsheetLocations.Items.Count -eq 0) {
            $previewText += "  (none)`n"
        } else {
            foreach ($item in $script:spreadsheetLocations.Items) {
                $folderPath = $item.Text
                if (Test-Path $folderPath) {
                    $xlsxCount = (Get-ChildItem -Path $folderPath -Filter "*.xlsx" -ErrorAction SilentlyContinue).Count
                    $previewText += "  - $folderPath ($xlsxCount .xlsx files)`n"
                } else {
                    $previewText += "  - $folderPath (NOT FOUND)`n"
                }
            }
        }
    }
    
    # Options
    $previewText += "`nACTIVE OPTIONS:`n"
    $optionNames = @("Skip Backup", "Skip Combine", "No Headers", "Duplicate by Qty", "Normalize Qty", "All Formats", "BLANK Rows", "Reverse", "Log to File", "Single Spreadsheet", "Skip SKU Processing")
    for ($i = 0; $i -lt $script:optionCheckboxes.Count; $i++) {
        if ($script:optionCheckboxes[$i].Checked) {
            $previewText += "  - $($optionNames[$i])`n"
        }
    }
    
    # Destinations
    $previewText += "`nDESTINATIONS:`n"
    $previewText += "  Combined: $($script:destinationLocation.Text)`n"
    $previewText += "  SKU List: $($script:skuListLocation.Text)`n"
    $previewText += "  Final Output: $($script:finalOutputLocation.Text)`n"
    
    Write-Log $previewText "Cyan"
}
#endregion

#region Drag and Drop Support
# Enable drag and drop on backup locations list
$script:backupLocations.AllowDrop = $true
$script:backupLocations.Add_DragEnter({
    param($senderObj, $e)
    if ($e.Data.GetDataPresent([System.Windows.Forms.DataFormats]::FileDrop)) {
        $e.Effect = [System.Windows.Forms.DragDropEffects]::Copy
    }
})
$script:backupLocations.Add_DragDrop({
    param($senderObj, $e)
    $files = $e.Data.GetData([System.Windows.Forms.DataFormats]::FileDrop)
    foreach ($file in $files) {
        if (Test-Path -Path $file -PathType Container) {
            $item = New-Object System.Windows.Forms.ListViewItem($file)
            $script:backupLocations.Items.Add($item) | Out-Null
            Write-Log "Added backup location: $file" "Green"
        }
    }
    Update-StatusBar
})

# Enable drag and drop on spreadsheet locations list
$script:spreadsheetLocations.AllowDrop = $true
$script:spreadsheetLocations.Add_DragEnter({
    param($senderObj, $e)
    if ($e.Data.GetDataPresent([System.Windows.Forms.DataFormats]::FileDrop)) {
        $e.Effect = [System.Windows.Forms.DragDropEffects]::Copy
    }
})
$script:spreadsheetLocations.Add_DragDrop({
    param($senderObj, $e)
    $files = $e.Data.GetData([System.Windows.Forms.DataFormats]::FileDrop)
    foreach ($file in $files) {
        if (Test-Path -Path $file -PathType Container) {
            $item = New-Object System.Windows.Forms.ListViewItem($file)
            $script:spreadsheetLocations.Items.Add($item) | Out-Null
            Write-Log "Added spreadsheet folder: $file" "Green"
        }
    }
    Update-StatusBar
})
#endregion

#region Double-Click to Edit
$script:backupLocations.Add_DoubleClick({
    if ($script:backupLocations.SelectedItems.Count -gt 0) {
        $selectedItem = $script:backupLocations.SelectedItems[0]
        $folderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog
        $folderBrowser.SelectedPath = $selectedItem.Text
        $folderBrowser.Description = "Select new folder location"
        if ($folderBrowser.ShowDialog() -eq 'OK') {
            $oldPath = $selectedItem.Text
            $selectedItem.Text = $folderBrowser.SelectedPath
            Write-Log "Changed backup location: $oldPath -> $($folderBrowser.SelectedPath)" "Yellow"
        }
    }
})

$script:spreadsheetLocations.Add_DoubleClick({
    if ($script:spreadsheetLocations.SelectedItems.Count -gt 0) {
        $selectedItem = $script:spreadsheetLocations.SelectedItems[0]
        $folderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog
        $folderBrowser.SelectedPath = $selectedItem.Text
        $folderBrowser.Description = "Select new folder location"
        if ($folderBrowser.ShowDialog() -eq 'OK') {
            $oldPath = $selectedItem.Text
            $selectedItem.Text = $folderBrowser.SelectedPath
            Write-Log "Changed spreadsheet folder: $oldPath -> $($folderBrowser.SelectedPath)" "Yellow"
        }
    }
})
#endregion

#region Status Bar Update Function
function Update-StatusBar {
    $backupCount = $script:backupLocations.Items.Count
    $spreadsheetCount = $script:spreadsheetLocations.Items.Count
    $script:statusLabelCenter.Text = "Backup: $backupCount | Spreadsheets: $spreadsheetCount"
}

# Initial status bar update
Update-StatusBar
#endregion

#region Sound Notification Function
function Play-CompletionSound {
    try {
        [System.Media.SystemSounds]::Exclamation.Play()
    } catch {
        # Silently fail if sound cannot be played
    }
}
#endregion

#region Window Position Save/Restore
$script:form.Add_Load({
    # Try to restore window position from settings
    if (Test-Path $script:AppSettingsFile) {
        try {
            $xmlDoc = New-Object System.Xml.XmlDocument
            $xmlDoc.Load($script:AppSettingsFile)
            $windowNode = $xmlDoc.SelectSingleNode("//WindowPosition")
            if ($windowNode) {
                $x = [int]$windowNode.SelectSingleNode("X").InnerText
                $y = [int]$windowNode.SelectSingleNode("Y").InnerText
                $width = [int]$windowNode.SelectSingleNode("Width").InnerText
                $height = [int]$windowNode.SelectSingleNode("Height").InnerText
                
                # Validate position is on screen
                $screen = [System.Windows.Forms.Screen]::FromPoint((New-Object System.Drawing.Point($x, $y)))
                if ($screen.Bounds.Contains($x, $y)) {
                    $script:form.StartPosition = "Manual"
                    $script:form.Location = New-Object System.Drawing.Point($x, $y)
                    $script:form.Size = New-Object System.Drawing.Size($width, $height)
                }
            }
        } catch {
            # Silently fail - use default position
        }
    }
})

$script:form.Add_FormClosing({
    param($senderObj, $e)
    # Save window position to settings
    try {
        $xmlDoc = New-Object System.Xml.XmlDocument
        if (Test-Path $script:AppSettingsFile) {
            $xmlDoc.Load($script:AppSettingsFile)
        } else {
            $xmlDeclaration = $xmlDoc.CreateXmlDeclaration("1.0", "UTF-8", $null)
            $xmlDoc.AppendChild($xmlDeclaration) | Out-Null
            $root = $xmlDoc.CreateElement("AppSettings")
            $xmlDoc.AppendChild($root) | Out-Null
        }
        
        # Remove existing window position node
        $existingNode = $xmlDoc.SelectSingleNode("//WindowPosition")
        if ($existingNode) {
            $existingNode.ParentNode.RemoveChild($existingNode) | Out-Null
        }
        
        # Add new window position
        $windowNode = $xmlDoc.CreateElement("WindowPosition")
        $xNode = $xmlDoc.CreateElement("X")
        $xNode.InnerText = $script:form.Location.X
        $yNode = $xmlDoc.CreateElement("Y")
        $yNode.InnerText = $script:form.Location.Y
        $widthNode = $xmlDoc.CreateElement("Width")
        $widthNode.InnerText = $script:form.Size.Width
        $heightNode = $xmlDoc.CreateElement("Height")
        $heightNode.InnerText = $script:form.Size.Height
        
        $windowNode.AppendChild($xNode) | Out-Null
        $windowNode.AppendChild($yNode) | Out-Null
        $windowNode.AppendChild($widthNode) | Out-Null
        $windowNode.AppendChild($heightNode) | Out-Null
        
        $xmlDoc.DocumentElement.AppendChild($windowNode) | Out-Null
        $xmlDoc.Save($script:AppSettingsFile)
    } catch {
        # Silently fail
    }
})
#endregion

# Initialize application settings and Recent Files menu
Write-Log "Loading application settings..." "Cyan"
Load-AppSettings | Out-Null
Update-RecentFilesMenu

# Display welcome and helpful information
Write-Log "=== Spreadsheet Wrangler v2.4.0 ===" "Cyan"
Write-Log "Application initialized and ready to use." "Green"

# Keyboard shortcuts section
Write-Log "KEYBOARD SHORTCUTS:" "Yellow"
Write-Log "  F5          - Run operations" "White"
Write-Log "  Escape      - Cancel running operation" "White"
Write-Log "  Ctrl+S      - Save configuration" "White"
Write-Log "  Ctrl+O      - Open configuration" "White"
Write-Log "  Ctrl+L      - Clear terminal" "White"
Write-Log "  Ctrl+E      - Export terminal log" "White"
Write-Log "  Ctrl+P      - Preview what will be processed" "White"
Write-Log "  Ctrl+D      - Toggle dark mode" "White"

# Tips section
Write-Log "TIPS:" "Yellow"
Write-Log "- Drag & drop folders onto the lists" "White"
Write-Log "- Double-click list items to edit them" "White"
Write-Log "- Window position is saved automatically" "White"
Write-Log "- Sound plays when operations complete" "White"

# Show the form
$script:form.ShowDialog() | Out-Null