Imports System.IO
Imports System.Runtime.InteropServices
Imports System.Text
Imports System.Windows.Forms
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private savedFilePaths As New Dictionary(Of String, String)()
    Private folderPaths As New Dictionary(Of String, String)()
    Private fileDict As New Dictionary(Of String, String)()
    Private excelDataCache As New Dictionary(Of String, List(Of List(Of String)))()
    Private WithEvents excelApp As Excel.Application
    Private openExcelInstances As New List(Of Excel.Application)()


    Private Sub SaveFilePathsToFile()
        Dim filePath As String = Path.Combine(Application.StartupPath, "selected_files.txt")

        Using sw As New StreamWriter(filePath)
            For Each item As String In ListBox1.Items
                Dim filePathForItem As String = savedFilePaths.FirstOrDefault(Function(kvp) kvp.Key = item).Value

                If filePathForItem IsNot Nothing Then
                    Dim line As New StringBuilder()
                    line.Append(item).Append("|").Append(filePathForItem)
                    sw.WriteLine(line.ToString())
                End If
            Next
        End Using
    End Sub
    Private Function GetFormattedRowData(row As Excel.Range, columnIndices As Dictionary(Of String, Integer), maxColumnWidths As Dictionary(Of String, Integer)) As String
        Dim rowData As String = ""
        For Each header As String In maxColumnWidths.Keys
            If columnIndices.ContainsKey(header) Then
                Dim cell As Excel.Range = row.Cells(1, columnIndices(header))
                Dim cellValue As String = If(cell.Value IsNot Nothing, cell.Value.ToString(), "")
                rowData += cellValue.PadRight(maxColumnWidths(header) + 2)
            End If
        Next
        Return rowData
    End Function
    Private Function PadCellValue(value As String, width As Integer) As String
        Return value.PadRight(width)
    End Function

    Private Sub DisplayExcelData(excelData As List(Of List(Of String)), dataGridView As DataGridView)
        dataGridView.Rows.Clear()
        For Each row As List(Of String) In excelData
            If row.Count >= 4 Then
                dataGridView.Rows.Add(row(0), row(1), row(2), row(3))
            End If
        Next
    End Sub


    Private Function ReadAndCacheExcelData(filePath As String) As List(Of List(Of String))
        Dim app As Excel.Application = New Excel.Application()
        Dim wb As Excel.Workbook = app.Workbooks.Open(filePath)
        Dim ws As Excel.Worksheet = Nothing

        For Each sheet As Excel.Worksheet In wb.Sheets
            If sheet.Name = "Kabelliste" Then
                ws = sheet
                Exit For
            End If
        Next

        If ws Is Nothing Then
            MessageBox.Show("Sheet 'data' not found in the selected Excel file.")
            Return New List(Of List(Of String))()
        End If

        Dim columnLetters As String() = {"K", "C", "A", "D"}
        Dim cachedData As New List(Of List(Of String))()

        For i As Integer = 2 To ws.UsedRange.Rows.Count
            Dim rowData As New List(Of String)()
            For Each columnLetter As String In columnLetters
                Dim cell As Excel.Range = ws.Range(columnLetter & i.ToString())
                Dim cellValue As String = If(cell.Value IsNot Nothing, cell.Value.ToString(), "")
                rowData.Add(cellValue)
            Next
            cachedData.Add(rowData)
        Next

        wb.Close(False)
        app.Quit()

        ReleaseObject(ws)
        ReleaseObject(wb)
        ReleaseObject(app)

        Return cachedData
    End Function




    Private Sub SaveExcelPaths()
        Dim lines As New List(Of String)
        For Each key As String In fileDict.Keys
            lines.Add($"{key}={fileDict(key)}")
        Next
        File.WriteAllLines("excel.txt", lines)
    End Sub
    Private Sub LoadExcelPaths()
        If Not File.Exists("excel.txt") Then
            Return
        End If

        Dim lines As String() = File.ReadAllLines("excel.txt")
        For Each line As String In lines
            Dim parts As String() = line.Split("=")
            If parts.Length = 2 Then
                fileDict(parts(0)) = parts(1)
            End If
        Next
    End Sub
    Private Sub SaveExcelFilePath(key As String, filePath As String)
        fileDict(key) = filePath
        SaveExcelPaths()
    End Sub

    Private selectedItem As String = ""


    Private Sub ListBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListBox1.SelectedIndexChanged
        If ListBox1.SelectedIndex <> -1 Then
            selectedItem = ListBox1.SelectedItem.ToString()
        End If
    End Sub

    Private Sub ListBox1_DoubleClick(sender As Object, e As EventArgs) Handles ListBox1.DoubleClick
        If ListBox1.SelectedIndex <> -1 Then
            OpenExcelFileDialog(ListBox1.SelectedItem.ToString())
        End If
    End Sub




    Private Sub OpenExcelFileDialog(selectedItem As String)
        Using openFileDialog As New OpenFileDialog()
            openFileDialog.Filter = "Excel Files|*.xlsx;*.xls"
            openFileDialog.Title = "Select an Excel File"

            If openFileDialog.ShowDialog() = DialogResult.OK Then
                Dim filePath As String = openFileDialog.FileName
                SaveExcelFilePath(selectedItem, filePath)
                excelDataCache(selectedItem) = ReadAndCacheExcelData(filePath)
                DisplayExcelData(excelDataCache(selectedItem), DataGridView1)
            End If
        End Using
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        LoadPaths()
        LoadFiles()
        LoadExcelPaths()
        Label1.Text = "Select harness from the list to show KAB."
        For Each column As DataGridViewColumn In DataGridView1.Columns
            column.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
        Next

        DataGridView1.Columns(1).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
        ProgressBar1.Visible = False
    End Sub
    Private Sub RadioButton1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton1.Click
        LoadFile("C:\Users\filip\Desktop\Harnesses\c2.txt")
    End Sub

    Private Sub RadioButton2_Click(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton2.Click
        LoadFile("C:\Users\filip\Desktop\Harnesses\PF.txt")
    End Sub

    Private Sub RadioButton3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton3.Click
        LoadFile("C:\Users\filip\Desktop\Harnesses\COMPONENTS.txt")
    End Sub

    Private Sub radioButton1_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton1.CheckedChanged
        If RadioButton1.Checked Then
            RadioButton1_Click(sender, e)
        End If
    End Sub

    Private Sub radioButton2_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton2.CheckedChanged
        If RadioButton2.Checked Then
            RadioButton2_Click(sender, e)
        End If
    End Sub

    Private Sub radioButton3_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs) Handles RadioButton3.CheckedChanged
        If RadioButton3.Checked Then
            RadioButton3_Click(sender, e)
        End If
    End Sub

    Private Sub TextBox1_KeyDown(sender As Object, e As KeyEventArgs) Handles TextBox1.KeyDown
        If e.KeyCode = Keys.Enter Then
            Dim searchText As String = TextBox1.Text.ToLower()
            For i As Integer = 0 To ListBox1.Items.Count - 1
                Dim currentItem As String = ListBox1.Items(i).ToString().ToLower()
                If currentItem.Contains(searchText) Then
                    ListBox1.SelectedIndex = i
                    Exit For
                End If
            Next
        End If
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim selectedItem As String = ListBox1.SelectedItem.ToString()

        ' Check if folder path is already set
        If folderPaths.ContainsKey(selectedItem) Then
            ' Ask user if they want to change the default folder location
            Dim result As DialogResult = MessageBox.Show($"Are you sure you want to change the default folder location for {selectedItem}?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question)

            If result = DialogResult.Yes Then
                ' Prompt user to select a new folder location
                Using folderBrowser As New FolderBrowserDialog()
                    folderBrowser.Description = $"Select a folder for {selectedItem}"

                    If folderBrowser.ShowDialog() = DialogResult.OK Then
                        ' Update folder path and save to file
                        folderPaths(selectedItem) = folderBrowser.SelectedPath
                        SavePaths()
                        MessageBox.Show($"Default folder location updated for {selectedItem}")
                    End If
                End Using
            End If
        Else
            ' If folder path is not set, inform user to double-click on the item
            MessageBox.Show($"Please double-click on {selectedItem} to set the default folder location.")
        End If
    End Sub

    Private Sub OpenFolder(ByVal folderPath As String)
        If folderPath <> "" Then
            Try
                Process.Start("explorer.exe", folderPath)
            Catch ex As Exception
                MessageBox.Show($"Error opening folder: {ex.Message}")
            End Try
        End If
    End Sub

    Private Sub SavePaths()
        Dim lines As New List(Of String)
        For Each key As String In folderPaths.Keys
            lines.Add($"{key}={folderPaths(key)}")
        Next
        File.WriteAllLines("paths.txt", lines)
    End Sub

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim selectedItem As String = ListBox1.SelectedItem.ToString()

        ' Check if folder path is already set
        If folderPaths.ContainsKey(selectedItem) Then
            ' Open folder in File Explorer
            Try
                Process.Start("explorer.exe", folderPaths(selectedItem))
            Catch ex As Exception
                MessageBox.Show($"Error opening folder: {ex.Message}")
            End Try
            Return
        End If

        ' If folder path is not set, ask user to select folder
        Using folderBrowser As New FolderBrowserDialog()
            folderBrowser.Description = $"Select a folder for {selectedItem}"

            If folderBrowser.ShowDialog() = DialogResult.OK Then
                ' Set folder path and open folder in File Explorer
                folderPaths(selectedItem) = folderBrowser.SelectedPath
                SavePaths()
                Try
                    Process.Start("explorer.exe", folderBrowser.SelectedPath)
                Catch ex As Exception
                    MessageBox.Show($"Error opening folder: {ex.Message}")
                End Try
            End If
        End Using
    End Sub
    Private Function GetSelectedRadioButton() As RadioButton
        If RadioButton1.Checked Then
            Return RadioButton1
        ElseIf RadioButton2.Checked Then
            Return RadioButton2
        ElseIf RadioButton3.Checked Then
            Return RadioButton3
        Else
            Return Nothing
        End If
    End Function
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim selectedRadioButton As RadioButton = GetSelectedRadioButton()

        If selectedRadioButton Is Nothing Then
            MessageBox.Show("Please select a radio button")
            Return
        End If

        Dim filePath As String = Path.Combine("C:\Users\filip\Desktop\Harnesses", selectedRadioButton.Text & ".txt")
        Dim textToWrite As String = TextBox1.Text & vbCrLf
        File.AppendAllText(filePath, textToWrite)

        MessageBox.Show("Harness added to " & selectedRadioButton.Text)

        ListBox1.Items.Clear()
        LoadFile(filePath)
    End Sub


    Private Sub SaveFiles()
        ' Write the contents of the dictionary to the files.txt file
        Dim lines As New List(Of String)()
        For Each kvp As KeyValuePair(Of String, String) In fileDict
            lines.Add(kvp.Key & "," & kvp.Value)
        Next
        File.WriteAllLines("files.txt", lines)
    End Sub

    Private Sub LoadFiles()
        ' Check if the files.txt file exists
        If Not File.Exists("files.txt") Then
            Return
        End If

        ' Read the contents of the files.txt file and add the entries to the dictionary
        Dim lines As String() = File.ReadAllLines("files.txt")
        For Each line As String In lines
            Dim parts As String() = line.Split(",")
            If parts.Length = 2 Then
                fileDict(parts(0)) = parts(1)
            End If
        Next
    End Sub
    Private Sub LoadFile(filePath As String)
        If Not File.Exists(filePath) Then
            MessageBox.Show("File not found: " & filePath)
            Return
        End If
        Dim lines As String() = File.ReadAllLines(filePath)
        ListBox1.Items.Clear()
        ListBox1.Items.AddRange(lines)
    End Sub

    Private Sub LoadPaths()
        If File.Exists("paths.txt") Then
            Dim lines() As String = File.ReadAllLines("paths.txt")
            For Each line As String In lines
                Dim parts() As String = line.Split("=")
                If parts.Length = 2 Then
                    folderPaths(parts(0)) = parts(1)
                End If
            Next
        End If
    End Sub
    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        ' Get the selected item from ListBox1
        Dim selectedItem As String = ListBox1.SelectedItem.ToString()

        ' Check if file location is already set
        If fileDict.ContainsKey(selectedItem) Then
            ' Open file using stored location
            Try
                Dim startInfo As New ProcessStartInfo()
                startInfo.FileName = fileDict(selectedItem)
                startInfo.UseShellExecute = True
                Process.Start(startInfo)
            Catch ex As Exception
                MessageBox.Show("Error opening file: " & ex.Message)
            End Try
            Return
        End If

        ' If file location is not set, ask user to select file
        Dim selectedFile As String = ""

        Using openFileDialog As New OpenFileDialog()
            openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
            openFileDialog.Filter = "All Files (*.*)|*.*"
            openFileDialog.FilterIndex = 1

            If openFileDialog.ShowDialog() = DialogResult.OK Then
                selectedFile = openFileDialog.FileName
            End If
        End Using

        ' Check if a file was selected
        If String.IsNullOrEmpty(selectedFile) Then
            MessageBox.Show("No file selected.")
            Return
        End If

        ' Set file location and save to file
        fileDict(selectedItem) = selectedFile
        SaveFiles()

        ' Open the file using the default application
        Try
            Dim startInfo As New ProcessStartInfo()
            startInfo.FileName = selectedFile
            startInfo.UseShellExecute = True
            Process.Start(startInfo)
        Catch ex As Exception
            MessageBox.Show("Error opening file: " & ex.Message)
        End Try
    End Sub
    Private Sub ListBox3_DragEnter(sender As Object, e As DragEventArgs) Handles ListBox3.DragEnter
        If e.Data.GetDataPresent(DataFormats.FileDrop) Then
            e.Effect = DragDropEffects.Copy
        Else
            e.Effect = DragDropEffects.None
        End If
    End Sub

    Private Sub ListBox3_DragDrop(sender As Object, e As DragEventArgs) Handles ListBox3.DragDrop
        Dim files() As String = e.Data.GetData(DataFormats.FileDrop)
        For Each file In files
            If Path.GetExtension(file) = ".xls" Or Path.GetExtension(file) = ".xlsx" Then
                ListBox3.Items.Add(file)
            End If
        Next
    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        If ListBox3.Items.Count < 1 Then
            MessageBox.Show("Ubaci MACH liste!")
            Return
        End If

        ProgressBar1.Value = 0 ' Reset the progress bar
        ProgressBar1.Maximum = ListBox3.Items.Count
        ProgressBar1.Step = 1
        ProgressBar1.Visible = True


        Dim app As Excel.Application = New Excel.Application()
        app.Visible = False ' Make the Excel application visible

        Dim wb As Excel.Workbook = app.Workbooks.Add()
        Dim ws As Excel.Worksheet = wb.Worksheets(1)

        ' Copy header row
        Dim headerRange As Excel.Range = ws.Range("A1:CD1")
        headerRange.Value = "Header"

        Dim rowCount As Integer = 2 ' Start copying data from second row
        Dim headerCopied As Boolean = False
        For Each file As String In ListBox3.Items
            If Path.GetFileName(file) <> "Import-Export.xls" Then
                If Not headerCopied Then
                    Dim wbHeader As Excel.Workbook = app.Workbooks.Open(file)
                    Dim wsHeader As Excel.Worksheet = wbHeader.Worksheets("Data")
                    Dim headerToCopy As Excel.Range = wsHeader.Range("A1", "CD1")
                    headerToCopy.Copy()
                    headerRange.PasteSpecial(Excel.XlPasteType.xlPasteAll)
                    wbHeader.Close(False)
                    headerCopied = True
                End If
                Try
                    Dim wbCopy As Excel.Workbook = app.Workbooks.Open(file)
                    Dim wsCopy As Excel.Worksheet = wbCopy.Worksheets("Data")
                    Dim rangeToCopy As Excel.Range = wsCopy.Range("A2", "CD" & wsCopy.Range("A2").End(Excel.XlDirection.xlDown).Row)

                    rangeToCopy.Copy()
                    Dim nextRow As Excel.Range = ws.Range("A" & rowCount)
                    nextRow.PasteSpecial(Excel.XlPasteType.xlPasteAll)

                    rowCount += rangeToCopy.Rows.Count

                    ' Copy a blank cell
                    wsCopy.Range("A1").Copy()

                    wbCopy.Close(False)

                    ' Clear the clipboard
                    app.CutCopyMode = False
                Catch comEx As System.Runtime.InteropServices.COMException
                    MessageBox.Show("An error occurred while processing the file: " & file & vbCrLf & "Error: The 'Data' worksheet was not found in the file.")
                    Return
                Catch ex As Exception
                    MessageBox.Show("An error occurred while processing the file: " & file & vbCrLf & "Error message: " & ex.Message)
                    Return
                End Try
            End If
            ProgressBar1.PerformStep()
        Next
        ProgressBar1.Value = 0
        ProgressBar1.Visible = False
        Dim columnRange As Excel.Range = ws.UsedRange.Columns
        columnRange.AutoFit()
        app.Visible = True
        openExcelInstances.Add(app)
        ExcelCheckTimer.Enabled = True
        ExcelCheckTimer.Start()
        AddHandler app.WorkbookBeforeClose, AddressOf WorkbookBeforeClose
        excelApp = app
        ' Release objects
        ReleaseObject(ws)
        ReleaseObject(wb)


        ' Do not release the app object here since it will be released in the excelApp_WorkbookBeforeClose method
    End Sub
    Private Sub ExcelCheckTimer_Tick(sender As Object, e As EventArgs) Handles ExcelCheckTimer.Tick
        If excelApp IsNot Nothing AndAlso IsNothing(excelApp.Workbooks) Then
            ExcelCheckTimer.Stop()
            ExcelCheckTimer.Enabled = False
        Else
            Return
        End If

        ' If no Excel workbooks are open, quit the Excel application and release its resources
        If excelApp IsNot Nothing Then
            excelApp.Quit()
            Marshal.ReleaseComObject(excelApp)
            excelApp = Nothing
        End If
    End Sub

    Private Sub WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean)
        Dim app As Excel.Application = Wb.Application
        Wb.Saved = True ' Mark the workbook as saved to prevent any save prompts
        If app IsNot Nothing Then
            app.Quit()
            Marshal.ReleaseComObject(app)
        End If
    End Sub

    Private Sub App_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean)
        Dim app As Excel.Application = Wb.Application
        If app IsNot Nothing Then
            app.Quit()
            Marshal.ReleaseComObject(app)
        End If
    End Sub
    Private Sub excelApp_WorkbookBeforeClose(ByVal Wb As Excel.Workbook, ByRef Cancel As Boolean) Handles excelApp.WorkbookBeforeClose
        If excelApp IsNot Nothing Then
            excelApp.Quit()
            Marshal.ReleaseComObject(excelApp)
            excelApp = Nothing
        End If
    End Sub

    Private Sub ReleaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
        Catch ex As Exception
            Console.WriteLine("Error releasing object: " & ex.Message)
        Finally
            obj = Nothing
            GC.Collect()
        End Try
    End Sub
    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        ListBox3.Items.Clear()
        ProgressBar1.Value = 0
    End Sub

    Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click
        If ListBox1.SelectedIndex <> -1 Then
            Dim selectedItem As String = ListBox1.SelectedItem.ToString()

            If fileDict.ContainsKey(selectedItem) Then
                ListBox3.Items.Add(fileDict(selectedItem))
            Else
                MessageBox.Show("Selected item does not have a file location.")
            End If
        Else
            MessageBox.Show("Please select an item from the list.")
        End If
    End Sub

    Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click
        If ListBox1.SelectedIndex <> -1 Then
            Dim selectedItem As String = ListBox1.SelectedItem.ToString()

            If fileDict.ContainsKey(selectedItem) Then
                Dim filePath As String = fileDict(selectedItem)
                If File.Exists(filePath) Then
                    If Not excelDataCache.ContainsKey(selectedItem) Then
                        excelDataCache(selectedItem) = ReadAndCacheExcelData(filePath)
                    End If
                    DisplayExcelData(excelDataCache(selectedItem), DataGridView1)
                Else
                    MessageBox.Show("File has been removed or moved! Double-click on the harness to select a new KAB list.")
                End If

                Label1.Visible = True
                Label1.Text = $"KAB lista za: {ListBox1.SelectedItem}"
            Else
                MessageBox.Show("Ovaj harness nema KAB listu! Double-click on the harness to select a KAB list.")
            End If
        Else
            MessageBox.Show("Please select an item from the list.")
        End If
    End Sub
    Private Sub Button10_Click(sender As Object, e As EventArgs) Handles Button10.Click
        If ListBox1.SelectedIndex <> -1 Then
            Dim selectedItem As String = ListBox1.SelectedItem.ToString()

            Dim openFileDialog As OpenFileDialog = New OpenFileDialog()
            openFileDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm"
            openFileDialog.Title = "Select a KAB List Excel File"

            If openFileDialog.ShowDialog() = DialogResult.OK Then
                Dim newFilePath As String = openFileDialog.FileName

                If fileDict.ContainsKey(selectedItem) Then
                    fileDict(selectedItem) = newFilePath
                Else
                    fileDict.Add(selectedItem, newFilePath)
                End If

                MessageBox.Show("Location for " & selectedItem & " changed.")
            End If
        Else
            MessageBox.Show("Please select a harness from the list.")
        End If
    End Sub

End Class