Imports System.ComponentModel
Imports System.IO
Imports OfficeOpenXml

Public Class frmMain

#Region " Variables and Constants "

    Private monthCalendar As MonthCalendar

    Private Const ID_COLUMN_NAME As String = "idColumn"
    Private Const TOTALS_FLAG_COLUMN_NAME As String = "totalsFlagColumn"
    Private ReadOnly DataSheetName As String = "Data"

    Private idColumnIndex As Integer
    Private totalsFlagColumnIndex As Integer
    Private dateColumnIndex As Integer
    Private dateFinishColumnIndex As Integer

    Private clickedRowIndex As Integer = -1

    Private undoStack As New Stack(Of DataGridViewAction)()
    Private redoStack As New Stack(Of DataGridViewAction)()

    Private originalRows As New List(Of DataRowModel)()

    Private nextId As Integer = 1

    Private lastSortColumn As DataGridViewColumn = Nothing
    Private lastSortDirection As ListSortDirection = ListSortDirection.Ascending

    Private undoImageOriginal As Image
    Private redoImageOriginal As Image
    Private undoImageGrayed As Image
    Private redoImageGrayed As Image

    Private customToolTip As CustomToolTipForm = Nothing

    Private isToolTipVisible As Boolean = False
    Private lastTooltipDataHashCode As Integer = 0
    Private lastToolTipPosition As Point = Point.Empty

#End Region

#Region " Form Initialization "

    Public Sub New()
        InitializeComponent()
    End Sub

    Private Sub frmMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        InitializeContextMenu()
        InitializeDataGridView()

        undoImageOriginal = btnUndo.Image
        redoImageOriginal = btnRedo.Image

        undoImageGrayed = MakeGrayscale(undoImageOriginal)
        redoImageGrayed = MakeGrayscale(redoImageOriginal)

        LoadData()

        PopulateNameComboBox()

        DTPInfo.Value = New DateTime(2024, 9, 15)
        DTPInfo2.Value = Date.Now

        DisplayAllData()

        UpdateUndoRedoMenuItems()

        AddHandler DGInfo.CellEndEdit, AddressOf DGInfo_CellEndEdit
        AddHandler DTPInfo.ValueChanged, AddressOf DTPInfo_ValueChanged
        AddHandler DTPInfo2.ValueChanged, AddressOf DTPInfo2_ValueChanged

        DGInfo.Sort(DGInfo.Columns("dateColumn"), ListSortDirection.Ascending)
    End Sub

    Private Sub PopulateNameComboBox()
        CboName.Items.Clear()

        Dim names As List(Of String) = originalRows.
            Select(Function(r) r.Name).
            Distinct().
            OrderBy(Function(n) n).
            ToList()

        For Each name As String In names
            CboName.Items.Add(name)
        Next
    End Sub

#End Region

#Region " DataGridView Initialization "

    Private Sub InitializeDataGridView()
        SetupDataGridViewColumns()

        DGInfo.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None

        DGInfo.Columns("totalTimeColumn").ReadOnly = True
        DGInfo.Columns("dateFinishColumn").ReadOnly = False
        DGInfo.Columns("paidColumn").ReadOnly = False

        DGInfo.AllowUserToAddRows = False

        For Each column As DataGridViewColumn In DGInfo.Columns
            column.SortMode = DataGridViewColumnSortMode.Automatic
        Next

        DGInfo.ShowCellToolTips = True

        ' Ensure the CellFormatting event is connected
        AddHandler DGInfo.CellFormatting, AddressOf DGInfo_CellFormatting
    End Sub

    Private Sub SetupDataGridViewColumns()
        If Not DGInfo.Columns.Contains(ID_COLUMN_NAME) Then
            Dim idColumn As New DataGridViewTextBoxColumn() With {
        .Name = ID_COLUMN_NAME,
        .HeaderText = "ID",
        .Visible = False
    }
            DGInfo.Columns.Insert(0, idColumn)
        End If

        If Not DGInfo.Columns.Contains(TOTALS_FLAG_COLUMN_NAME) Then
            Dim totalsFlagColumn As New DataGridViewTextBoxColumn() With {
        .Name = TOTALS_FLAG_COLUMN_NAME,
        .HeaderText = "IsTotalsRow",
        .Visible = False
    }
            DGInfo.Columns.Insert(1, totalsFlagColumn)
        End If

        idColumnIndex = DGInfo.Columns(ID_COLUMN_NAME).Index
        totalsFlagColumnIndex = DGInfo.Columns(TOTALS_FLAG_COLUMN_NAME).Index
        dateColumnIndex = DGInfo.Columns("dateColumn").Index
        dateFinishColumnIndex = DGInfo.Columns("dateFinishColumn").Index
    End Sub

#End Region

#Region " Context Menu Initialization "

    Private Sub InitializeContextMenu()
        DGInfo.ContextMenuStrip = contextMenuMain

        menuItemUndo.Enabled = False
        menuItemRedo.Enabled = False

        btnUndo.Enabled = False
        btnRedo.Enabled = False
    End Sub

#End Region

#Region " PictureBox Event Handlers "

    Private Sub PictureBox_MouseEnter(sender As Object, e As EventArgs) Handles btnDelete.MouseEnter, btnAdd.MouseEnter, btnUndo.MouseEnter, btnRedo.MouseEnter, btnDuplicate.MouseEnter, btnFB.MouseEnter, btnInfo.MouseEnter, btnGo.MouseEnter, btnInvoice.MouseEnter, btnExcel.MouseEnter
        Dim pictureBox = DirectCast(sender, PictureBox)
        pictureBox.Padding = New Padding(3)
        pictureBox.BackColor = Color.LightGray
    End Sub

    Private Sub PictureBox_MouseLeave(sender As Object, e As EventArgs) Handles btnAdd.MouseLeave, btnDelete.MouseLeave, btnUndo.MouseLeave, btnRedo.MouseLeave, btnDuplicate.MouseLeave, btnFB.MouseLeave, btnGo.MouseLeave, btnInfo.MouseLeave, btnInvoice.MouseLeave, btnExcel.MouseLeave
        Dim pictureBox = DirectCast(sender, PictureBox)
        pictureBox.Padding = New Padding(0)
        pictureBox.BackColor = Color.Transparent
    End Sub

#End Region

#Region " Image Processing "

    Private Function MakeGrayscale(original As Image) As Image
        Dim newBitmap As New Bitmap(original.Width, original.Height)
        Using g As Graphics = Graphics.FromImage(newBitmap)
            Dim colorMatrix As New Imaging.ColorMatrix(
                New Single()() {
                    New Single() {0.3, 0.3, 0.3, 0, 0},
                    New Single() {0.59, 0.59, 0.59, 0, 0},
                    New Single() {0.11, 0.11, 0.11, 0, 0},
                    New Single() {0, 0, 0, 1, 0},
                    New Single() {0, 0, 0, 0, 1}
                })
            Using attributes As New Imaging.ImageAttributes()
                attributes.SetColorMatrix(colorMatrix)
                g.DrawImage(original, New Rectangle(0, 0, original.Width, original.Height),
                    0, 0, original.Width, original.Height, GraphicsUnit.Pixel, attributes)
            End Using
        End Using
        Return newBitmap
    End Function

#End Region

#Region " Event Handlers "

    Private Sub DGInfo_MouseDown(sender As Object, e As MouseEventArgs) Handles DGInfo.MouseDown
        If e.Button = MouseButtons.Right Then
            Dim hitTestInfo As DataGridView.HitTestInfo = DGInfo.HitTest(e.X, e.Y)

            Select Case hitTestInfo.Type
                Case DataGridViewHitTestType.Cell
                    If hitTestInfo.RowIndex >= 0 AndAlso hitTestInfo.RowIndex < DGInfo.Rows.Count Then
                        clickedRowIndex = hitTestInfo.RowIndex
                        DGInfo.ClearSelection()
                        DGInfo.Rows(clickedRowIndex).Selected = True
                        DGInfo.CurrentCell = DGInfo.Rows(clickedRowIndex).Cells(hitTestInfo.ColumnIndex)
                    Else
                        clickedRowIndex = -1
                    End If

                Case DataGridViewHitTestType.ColumnHeader
                    MessageBox.Show("Right-clicked on column header: " & DGInfo.Columns(hitTestInfo.ColumnIndex).HeaderText)

                Case DataGridViewHitTestType.RowHeader
                    MessageBox.Show("Right-clicked on row header: " & hitTestInfo.RowIndex.ToString())

                Case Else
                    clickedRowIndex = -1
            End Select
        End If
    End Sub

    Private Sub DGInfo_CellBeginEdit(sender As Object, e As DataGridViewCellCancelEventArgs) Handles DGInfo.CellBeginEdit
        If e.RowIndex >= 0 AndAlso e.ColumnIndex >= 0 Then
            Dim cell = DGInfo.Rows(e.RowIndex).Cells(e.ColumnIndex)
            cell.Tag = cell.Value
        End If
    End Sub

    Private Sub DGInfo_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DGInfo.CellEndEdit
        If e.RowIndex < 0 OrElse e.ColumnIndex < 0 Then Return

        Dim cell = DGInfo.Rows(e.RowIndex).Cells(e.ColumnIndex)
        Dim previousValue = cell.Tag
        Dim newValue = cell.Value?.ToString()

        Dim currentRow As DataGridViewRow = DGInfo.Rows(e.RowIndex)
        If IsTotalsRow(currentRow) Then
            Return
        End If

        Dim rowId As Integer = Integer.Parse(currentRow.Cells(idColumnIndex).Value.ToString())
        Dim dataRow = originalRows.FirstOrDefault(Function(r) r.Id = rowId)
        If dataRow IsNot Nothing Then
            UpdateDataRowModel(dataRow, DGInfo.Columns(e.ColumnIndex).Name, newValue)
            UpdateTotals()
            SaveDataWithRetry()
        End If

        Dim action As New DataGridViewAction With {
    .Type = DataGridViewAction.ActionType.CellEdit,
    .RowIndex = e.RowIndex,
    .ColumnIndex = e.ColumnIndex,
    .OldValue = previousValue,
    .NewValue = cell.Value,
    .RowId = currentRow.Cells(idColumnIndex).Value.ToString()
}

        undoStack.Push(action)
        redoStack.Clear()
        UpdateUndoRedoMenuItems()
    End Sub

    Private Sub DGInfo_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DGInfo.CellValueChanged
        If e.RowIndex >= 0 AndAlso e.ColumnIndex >= 0 Then
            Dim currentRow As DataGridViewRow = DGInfo.Rows(e.RowIndex)
            If IsTotalsRow(currentRow) Then
                Return
            End If

            Dim rowId As Integer = Integer.Parse(currentRow.Cells(idColumnIndex).Value.ToString())
            Dim dataRow As DataRowModel = originalRows.FirstOrDefault(Function(r) r.Id = rowId)
            If dataRow IsNot Nothing Then
                UpdateDataRowModel(dataRow, DGInfo.Columns(e.ColumnIndex).Name, currentRow.Cells(e.ColumnIndex).Value?.ToString())
                UpdateTotals()
                SaveData()
            End If
        End If
    End Sub

    Private Sub DGInfo_UserDeletingRow(sender As Object, e As DataGridViewRowCancelEventArgs) Handles DGInfo.UserDeletingRow
        If IsTotalsRow(e.Row) Then
            MessageBox.Show("The totals row cannot be deleted.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            e.Cancel = True
        Else
            If e.Row.Index >= 0 AndAlso e.Row.Index < DGInfo.Rows.Count Then
                Dim rowData As DataGridViewRow = e.Row
                Dim dataRowModel As DataRowModel = ConvertDataGridViewRowToDataRowModel(rowData)

                Dim action As New DataGridViewAction With {
                    .Type = DataGridViewAction.ActionType.RowDeleted,
                    .RowId = dataRowModel.Id.ToString(),
                    .DataRow = dataRowModel
                }
                undoStack.Push(action)
                redoStack.Clear()
                UpdateUndoRedoMenuItems()

                originalRows.RemoveAll(Function(r) r.Id = dataRowModel.Id)
            End If
        End If
    End Sub

    Private Sub DGInfo_Sorted(sender As Object, e As EventArgs) Handles DGInfo.Sorted
        Dim totalsRow As DataGridViewRow = GetTotalsRow()
        If totalsRow IsNot Nothing Then
            DGInfo.Rows.Remove(totalsRow)
            DGInfo.Rows.Add(totalsRow)
        End If
    End Sub

    Private Sub DGInfo_SortCompare(sender As Object, e As DataGridViewSortCompareEventArgs) Handles DGInfo.SortCompare
        Dim dgv As DataGridView = CType(sender, DataGridView)
        Dim row1 As DataGridViewRow = dgv.Rows(e.RowIndex1)
        Dim row2 As DataGridViewRow = dgv.Rows(e.RowIndex2)

        Dim isRow1Totals As Boolean = IsTotalsRow(row1)
        Dim isRow2Totals As Boolean = IsTotalsRow(row2)

        If isRow1Totals AndAlso Not isRow2Totals Then
            e.SortResult = 1
            e.Handled = True
            Return
        ElseIf Not isRow1Totals AndAlso isRow2Totals Then
            e.SortResult = -1
            e.Handled = True
            Return
        ElseIf isRow1Totals AndAlso isRow2Totals Then
            e.SortResult = 0
            e.Handled = True
            Return
        End If

        If e.Column.Name = "dateColumn" Then
            Dim date1, date2 As Date
            e.SortResult = If(Date.TryParse(e.CellValue1?.ToString(), date1) AndAlso Date.TryParse(e.CellValue2?.ToString(), date2),
                date1.CompareTo(date2),
                String.Compare(e.CellValue1?.ToString(), e.CellValue2?.ToString()))
            e.Handled = True
        ElseIf e.Column.Name = "startTimeColumn" OrElse e.Column.Name = "finishTimeColumn" Then
            Dim time1, time2 As TimeSpan
            e.SortResult = If(TimeSpan.TryParse(e.CellValue1?.ToString(), time1) AndAlso TimeSpan.TryParse(e.CellValue2?.ToString(), time2),
                time1.CompareTo(time2),
                String.Compare(e.CellValue1?.ToString(), e.CellValue2?.ToString()))
            e.Handled = True
        ElseIf e.Column.Name = "amountColumn" Then
            Dim amount1, amount2 As Decimal
            Dim amountStr1 As String = e.CellValue1?.ToString().Replace("£", "")
            Dim amountStr2 As String = e.CellValue2?.ToString().Replace("£", "")
            e.SortResult = If(Decimal.TryParse(amountStr1, amount1) AndAlso Decimal.TryParse(amountStr2, amount2),
                amount1.CompareTo(amount2),
                String.Compare(e.CellValue1?.ToString(), e.CellValue2?.ToString()))
            e.Handled = True
        Else
            e.Handled = False
        End If
    End Sub

    Private Sub DGInfo_ColumnHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DGInfo.ColumnHeaderMouseClick
        Dim clickedColumn As DataGridViewColumn = DGInfo.Columns(e.ColumnIndex)

        lastSortDirection = If(lastSortColumn Is clickedColumn,
        If(lastSortDirection = ListSortDirection.Ascending, ListSortDirection.Descending, ListSortDirection.Ascending),
        ListSortDirection.Ascending)

        lastSortColumn = clickedColumn

        DGInfo.Sort(clickedColumn, lastSortDirection)
    End Sub

    Private Sub DGInfo_CellMouseEnter(sender As Object, e As DataGridViewCellEventArgs) Handles DGInfo.CellMouseEnter
        If e.RowIndex >= 0 AndAlso e.ColumnIndex = DGInfo.Columns("nameColumn").Index Then
            Dim currentRow As DataGridViewRow = DGInfo.Rows(e.RowIndex)

            If IsTotalsRow(currentRow) Then
                Return
            End If

            Dim name As String = currentRow.Cells(e.ColumnIndex).Value.ToString()

            If String.IsNullOrWhiteSpace(name) Then
                Return
            End If

            Dim personData = originalRows.Where(Function(r) r.Name = name)

            Dim jobCount As Integer = personData.Count()
            Dim totalDuration As TimeSpan = TimeSpan.FromTicks(personData.Sum(Function(r) r.TotalTime.Ticks))
            Dim totalAmount As Decimal = personData.Sum(Function(r) r.Amount)

            Dim totalHours As Integer = Math.Floor(totalDuration.TotalHours)
            Dim totalMinutes As Integer = totalDuration.Minutes
            Dim totalTimeString As String = $"{totalHours}:{totalMinutes:D2}"

            Dim tooltipData As New Dictionary(Of String, String)
            tooltipData("Jobs") = jobCount.ToString()
            tooltipData("Hours") = totalTimeString
            tooltipData("Total Pay") = "£" & totalAmount.ToString("0.00")

            Dim cursorPosition As Point = Control.MousePosition

            ShowCustomTooltip(name, tooltipData, cursorPosition)
        End If
    End Sub

    Private Sub DGInfo_CellMouseLeave(sender As Object, e As DataGridViewCellEventArgs) Handles DGInfo.CellMouseLeave
        HideCustomTooltip()
    End Sub

    Private Sub btnGo_Click(sender As Object, e As EventArgs) Handles btnGo.Click
        DTPInfo.Value = New DateTime(2024, 9, 15)
        DTPInfo2.Value = Date.Now

        DisplayAllData()
        CboName.SelectedIndex = -1
    End Sub

    Private Sub DGInfo_DataError(sender As Object, e As DataGridViewDataErrorEventArgs) Handles DGInfo.DataError
        If e.Exception IsNot Nothing AndAlso TypeOf e.Exception Is InvalidCastException Then
            e.ThrowException = False
            e.Cancel = True
            DGInfo(e.ColumnIndex, e.RowIndex).Value = False
        End If
    End Sub

#End Region

#Region " Custom Tooltip Methods "

    Private Sub ShowCustomTooltip(ByVal nameText As String, ByVal data As Dictionary(Of String, String), ByVal cursorPosition As Point)
        Dim desiredPosition As New Point(cursorPosition.X + 2, cursorPosition.Y + 2)

        Dim dataHashCode As Integer = data.GetHashCode() Xor nameText.GetHashCode()

        If dataHashCode <> lastTooltipDataHashCode OrElse desiredPosition <> lastToolTipPosition OrElse Not isToolTipVisible Then
            If customToolTip Is Nothing Then
                customToolTip = New CustomToolTipForm()
            End If

            customToolTip.UpdateContent(nameText, data)

            customToolTip.Location = desiredPosition

            If Not isToolTipVisible Then
                customToolTip.Show(Me)
                isToolTipVisible = True
            End If

            lastTooltipDataHashCode = dataHashCode
            lastToolTipPosition = desiredPosition
        End If
    End Sub

    Private Sub HideCustomTooltip()
        If customToolTip IsNot Nothing AndAlso isToolTipVisible Then
            customToolTip.Hide()
            isToolTipVisible = False
        End If
    End Sub

#End Region

#Region " Context Menu Event Handlers and Button Clicks "

    Private Sub menuItemAddRow_Click(sender As Object, e As EventArgs) Handles menuItemAddRow.Click, btnAdd.Click
        Dim dataRow As DataRowModel = AddRow()

        Dim action As New DataGridViewAction With {
        .Type = DataGridViewAction.ActionType.RowAdded,
        .RowId = dataRow.Id.ToString(),
        .DataRow = dataRow
    }
        undoStack.Push(action)
        redoStack.Clear()
        UpdateUndoRedoMenuItems()

        ReapplyLastSort()
    End Sub

    Private Sub menuItemDeleteRow_Click(sender As Object, e As EventArgs) Handles menuItemDeleteRow.Click, btnDelete.Click
        Dim rowIndexToDelete As Integer = GetSelectedRowIndex(sender)
        DeleteRow(rowIndexToDelete)

        ReapplyLastSort()
    End Sub

    Private Sub menuItemDuplicateRow_Click(sender As Object, e As EventArgs) Handles menuItemDuplicateRow.Click, btnDuplicate.Click
        Dim rowIndexToDuplicate = GetSelectedRowIndex(sender)
        DuplicateRow(rowIndexToDuplicate)

        ReapplyLastSort()
    End Sub

    Private Function AddRow() As DataRowModel
        Dim dataRow As New DataRowModel With {
        .Id = nextId,
        .Name = "",
        .Type = "",
        .DateValue = Date.Now,
        .DateFinish = Date.Now.AddDays(7),
        .StartTime = TimeSpan.FromHours(Date.Now.Hour).Add(TimeSpan.FromMinutes(Date.Now.Minute)),
        .FinishTime = TimeSpan.FromHours(Date.Now.AddHours(1).Hour).Add(TimeSpan.FromMinutes(Date.Now.Minute)),
        .TotalTime = TimeSpan.FromHours(1),
        .Amount = 0D,
        .Comment = "",
        .Paid = False
    }
        nextId += 1

        originalRows.Add(dataRow)

        DisplayAllData()

        UpdateTotals()
        SaveData()

        Return dataRow
    End Function

    Private Sub DeleteRow(rowIndexToDelete As Integer)
        If rowIndexToDelete >= 0 AndAlso rowIndexToDelete < DGInfo.Rows.Count Then
            Dim selectedRow As DataGridViewRow = DGInfo.Rows(rowIndexToDelete)
            If IsTotalsRow(selectedRow) Then
                MessageBox.Show("The totals row cannot be deleted.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return
            End If

            Dim dataRowModel As DataRowModel = ConvertDataGridViewRowToDataRowModel(selectedRow)

            Dim action As New DataGridViewAction With {
            .Type = DataGridViewAction.ActionType.RowDeleted,
            .RowId = dataRowModel.Id.ToString(),
            .DataRow = dataRowModel
        }
            undoStack.Push(action)
            redoStack.Clear()
            UpdateUndoRedoMenuItems()

            originalRows.RemoveAll(Function(r) r.Id = dataRowModel.Id)

            DisplayAllData()

            UpdateTotals()
            SaveData()
        End If
    End Sub

    Private Sub DuplicateRow(rowIndexToDuplicate As Integer)
        If rowIndexToDuplicate >= 0 AndAlso rowIndexToDuplicate < DGInfo.Rows.Count Then
            Dim selectedRow As DataGridViewRow = DGInfo.Rows(rowIndexToDuplicate)
            If IsTotalsRow(selectedRow) Then
                MessageBox.Show("The totals row cannot be duplicated.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return
            End If

            Dim originalDataRow As DataRowModel = ConvertDataGridViewRowToDataRowModel(selectedRow)

            Dim duplicatedDataRow As New DataRowModel With {
            .Id = nextId,
            .Name = originalDataRow.Name,
            .Type = originalDataRow.Type,
            .DateValue = originalDataRow.DateValue,
            .StartTime = originalDataRow.StartTime,
            .FinishTime = originalDataRow.FinishTime,
            .TotalTime = originalDataRow.TotalTime,
            .Amount = originalDataRow.Amount,
            .Comment = originalDataRow.Comment,
            .DateFinish = originalDataRow.DateFinish,
            .Paid = originalDataRow.Paid
        }
            nextId += 1

            originalRows.Add(duplicatedDataRow)

            DisplayAllData()

            UpdateTotals()

            Dim action As New DataGridViewAction With {
            .Type = DataGridViewAction.ActionType.RowDuplicated,
            .RowId = duplicatedDataRow.Id.ToString(),
            .DataRow = duplicatedDataRow
        }
            undoStack.Push(action)
            redoStack.Clear()
            UpdateUndoRedoMenuItems()

            SaveData()
        End If
    End Sub

    Private Sub menuItemUndo_Click(sender As Object, e As EventArgs) Handles menuItemUndo.Click, btnUndo.Click
        If undoStack.Count > 0 Then
            Dim action = undoStack.Pop()
            PerformUndo(action)
            redoStack.Push(action)
            UpdateUndoRedoMenuItems()
        End If
    End Sub

    Private Sub menuItemRedo_Click(sender As Object, e As EventArgs) Handles menuItemRedo.Click, btnRedo.Click
        If redoStack.Count > 0 Then
            Dim action = redoStack.Pop()
            PerformRedo(action)
            undoStack.Push(action)
            UpdateUndoRedoMenuItems()
        End If
    End Sub

#End Region

#Region " Data Management Methods "
    Private Sub LoadData()
        originalRows.Clear()
        nextId = 1

        Dim filePath As String = GetFilePath("Data", "Data.xlsx")

        If File.Exists(filePath) Then
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial

            Using package As New ExcelPackage(New FileInfo(filePath))
                Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets(DataSheetName)
                If worksheet IsNot Nothing Then
                    For rowNum As Integer = 2 To worksheet.Dimension.End.Row
                        Dim name As String = worksheet.Cells(rowNum, 1).Text
                        Dim type As String = worksheet.Cells(rowNum, 2).Text
                        Dim dateValue As Date = Date.MinValue
                        Dim dateFinish As Date = Date.MinValue
                        Dim startTime As TimeSpan = TimeSpan.Zero
                        Dim finishTime As TimeSpan = TimeSpan.Zero
                        Dim totalTime As TimeSpan = TimeSpan.Zero
                        Dim amount As Decimal = 0D
                        Dim paid As Boolean = False
                        Dim comment As String = worksheet.Cells(rowNum, 10).Text

                        If Date.TryParse(worksheet.Cells(rowNum, 3).Text, dateValue) Then
                            dateValue = dateValue
                        End If

                        If Date.TryParse(worksheet.Cells(rowNum, 4).Text, dateFinish) Then
                            dateFinish = dateFinish
                        End If

                        If TimeSpan.TryParse(worksheet.Cells(rowNum, 5).Text, startTime) Then
                            startTime = startTime
                        End If

                        If TimeSpan.TryParse(worksheet.Cells(rowNum, 6).Text, finishTime) Then
                            finishTime = finishTime
                        End If

                        If TimeSpan.TryParse(worksheet.Cells(rowNum, 7).Text, totalTime) Then
                            totalTime = totalTime
                        End If

                        If Decimal.TryParse(worksheet.Cells(rowNum, 8).Text.Replace("£", "").Replace("Â", ""), amount) Then
                            amount = amount
                        End If

                        If Boolean.TryParse(worksheet.Cells(rowNum, 9).Text, paid) Then
                            paid = paid
                        End If

                        Dim dataRow As New DataRowModel With {
                        .Id = nextId,
                        .Name = name,
                        .Type = type,
                        .DateValue = dateValue,
                        .DateFinish = dateFinish,
                        .StartTime = startTime,
                        .FinishTime = finishTime,
                        .TotalTime = totalTime,
                        .Amount = amount,
                        .Comment = comment,
                        .Paid = paid
                    }
                        nextId += 1

                        originalRows.Add(dataRow)
                    Next
                End If
            End Using
        End If
    End Sub

    Private Sub SaveDataWithRetry()
        Dim filePath As String = GetFilePath("Data", "Data.xlsx")
        Dim retryCount As Integer = 0
        Dim maxRetries As Integer = 3
        Dim retryDelay As Integer = 2000

        While retryCount < maxRetries
            Try
                SaveData()
                Return
            Catch ex As IOException When ex.Message.Contains("being used by another process")
                retryCount += 1
                If retryCount < maxRetries Then
                    MessageBox.Show("The file is open in another process. Please close it and try again.", "File in Use", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                    Threading.Thread.Sleep(retryDelay)
                Else
                    MessageBox.Show("The file is still open in another process. Please close it and try again later.", "File in Use", MessageBoxButtons.OK, MessageBoxIcon.Error)
                    Return
                End If
            Catch ex As Exception
                MessageBox.Show("An error occurred while saving data: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Return
            End Try
        End While
    End Sub

    Private Sub SaveData()
        Dim filePath As String = GetFilePath("Data", "Data.xlsx")

        Try
            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial

            Using package As New ExcelPackage(New FileInfo(filePath))
                Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets(DataSheetName)
                If worksheet Is Nothing Then
                    worksheet = package.Workbook.Worksheets.Add(DataSheetName)
                    worksheet.Cells(1, 1).Value = "Name"
                    worksheet.Cells(1, 2).Value = "Type"
                    worksheet.Cells(1, 3).Value = "Date"
                    worksheet.Cells(1, 4).Value = "Date Finish"
                    worksheet.Cells(1, 5).Value = "Start Time"
                    worksheet.Cells(1, 6).Value = "Finish Time"
                    worksheet.Cells(1, 7).Value = "Total Time"
                    worksheet.Cells(1, 8).Value = "Amount"
                    worksheet.Cells(1, 9).Value = "Paid"
                    worksheet.Cells(1, 10).Value = "Comment"
                End If

                worksheet.DeleteRow(2, originalRows.Count + 1)

                For rowNum As Integer = 0 To originalRows.Count - 1
                    Dim dataRow As DataRowModel = originalRows(rowNum)
                    worksheet.Cells(rowNum + 2, 1).Value = dataRow.Name
                    worksheet.Cells(rowNum + 2, 2).Value = dataRow.Type
                    worksheet.Cells(rowNum + 2, 3).Value = dataRow.DateValue.ToString("d")
                    worksheet.Cells(rowNum + 2, 4).Value = dataRow.DateFinish.ToString("d")
                    worksheet.Cells(rowNum + 2, 5).Value = dataRow.StartTime.ToString("hh\:mm")
                    worksheet.Cells(rowNum + 2, 6).Value = dataRow.FinishTime.ToString("hh\:mm")
                    worksheet.Cells(rowNum + 2, 7).Value = $"{CInt(Math.Floor(dataRow.TotalTime.TotalHours))}:{dataRow.TotalTime.Minutes:D2}"
                    worksheet.Cells(rowNum + 2, 8).Value = dataRow.Amount
                    worksheet.Cells(rowNum + 2, 9).Value = dataRow.Paid
                    worksheet.Cells(rowNum + 2, 10).Value = dataRow.Comment

                    worksheet.Cells(rowNum + 2, 8).Style.Numberformat.Format = "0.00"
                    worksheet.Cells(rowNum + 2, 9).Style.Numberformat.Format = "General"

                    Debug.WriteLine($"Writing row {rowNum + 2}: DateFinish={dataRow.DateFinish}, Paid={dataRow.Paid}")
                Next

                package.Save()
            End Using
        Catch ex As IOException
            If MessageBox.Show("An error occurred while saving data: " & vbCrLf & ex.Message & vbCrLf & "Would you like to close Excel and try again?", "Error", MessageBoxButtons.YesNo, MessageBoxIcon.Error) = DialogResult.Yes Then
                CloseExcel()
                SaveDataWithRetry()
            End If
        End Try
    End Sub

    Private Sub CloseExcel()
        Try
            Dim excelProcesses() As Process = Process.GetProcessesByName("EXCEL")
            For Each proc As Process In excelProcesses
                proc.Kill()
                proc.WaitForExit()
            Next
        Catch ex As Exception
            MessageBox.Show("An error occurred while trying to close Excel: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Function IsFileOpen(filePath As String) As Boolean
        Try
            Using fs As New FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.None)
            End Using
            Return False
        Catch ex As IOException
            Return True
        End Try
    End Function

    Private Sub btnCheckFormat_Click(sender As Object, e As EventArgs) Handles btnCheckFormat.Click
        CheckAndCorrectFormatting()
        LoadData()
        DisplayAllData()
        ReapplyLastSort()
    End Sub

    Private Sub CheckAndCorrectFormatting()
        Try
            Dim filePath As String = GetFilePath("Data", "Data.xlsx")

            ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial

            Using package As New ExcelPackage(New FileInfo(filePath))
                Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets(DataSheetName)
                If worksheet IsNot Nothing Then
                    For rowNum As Integer = 2 To worksheet.Dimension.End.Row
                        worksheet.Cells(rowNum, 7).Style.Numberformat.Format = "0.00"

                        Dim startTimeStr As String = worksheet.Cells(rowNum, 5).Text
                        Dim finishTimeStr As String = worksheet.Cells(rowNum, 6).Text
                        Dim totalTimeStr As String = worksheet.Cells(rowNum, 7).Text

                        Dim startTime As TimeSpan
                        Dim finishTime As TimeSpan
                        Dim totalTime As TimeSpan

                        If TimeSpan.TryParse(startTimeStr, startTime) AndAlso TimeSpan.TryParse(finishTimeStr, finishTime) AndAlso TimeSpan.TryParse(totalTimeStr, totalTime) Then
                            totalTime = If(finishTime >= startTime, finishTime - startTime, TimeSpan.FromHours(24) - startTime + finishTime)

                            worksheet.Cells(rowNum, 7).Value = $"{CInt(Math.Floor(totalTime.TotalHours))}:{totalTime.Minutes:D2}"
                        End If

                        Dim dateStartStr As String = worksheet.Cells(rowNum, 3).Text
                        Dim dateFinishStr As String = worksheet.Cells(rowNum, 4).Text

                        Dim dateStart As Date
                        Dim dateFinish As Date

                        If Date.TryParse(dateStartStr, dateStart) AndAlso Date.TryParse(dateFinishStr, dateFinish) Then
                            If finishTime < startTime Then
                                dateFinish = dateStart.AddDays(1)
                                worksheet.Cells(rowNum, 4).Value = dateFinish.ToString("d")
                            Else
                                dateFinish = dateStart
                                worksheet.Cells(rowNum, 4).Value = dateFinish.ToString("d")
                            End If
                        End If
                    Next

                    package.Save()
                End If
            End Using

            MessageBox.Show("Formatting checked and corrected successfully.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            If MessageBox.Show("An error occurred while saving data: " & ex.Message & vbCrLf & "Would you like to close Excel and try again?", "Error", MessageBoxButtons.YesNo, MessageBoxIcon.Error) = DialogResult.Yes Then
                CloseExcel()
                CheckAndCorrectFormatting()
            End If
        End Try
    End Sub

    Public Function GetFilePath(subDirectory As String, fileName As String) As String
        Dim dir As New DirectoryInfo(Application.StartupPath)
        For i As Integer = 1 To 4
            If dir.Parent IsNot Nothing Then
                dir = dir.Parent
            End If
        Next
        Return Path.Combine(dir.FullName, "Resources", subDirectory, fileName)
    End Function

    Private Sub UpdateTotalTimeForRow(currentRow As DataGridViewRow)
        Dim startTimeStr = currentRow.Cells("startTimeColumn").Value?.ToString()
        Dim finishTimeStr = currentRow.Cells("finishTimeColumn").Value?.ToString()
        Dim startTime As TimeSpan
        Dim finishTime As TimeSpan

        If Not TimeSpan.TryParse(startTimeStr, startTime) Then
            MessageBox.Show("Invalid format for Start Time. Please use HH:mm.", "Format Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If

        If Not TimeSpan.TryParse(finishTimeStr, finishTime) Then
            MessageBox.Show("Invalid format for Finish Time. Please use HH:mm.", "Format Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If

        Dim totalTime As TimeSpan

        If startTime = TimeSpan.Zero AndAlso finishTime = TimeSpan.Zero Then
            totalTime = TimeSpan.Zero
        ElseIf finishTime > startTime Then
            totalTime = finishTime - startTime
        Else
            totalTime = TimeSpan.FromHours(24) - startTime + finishTime
        End If

        currentRow.Cells("totalTimeColumn").Value = $"{CInt(Math.Floor(totalTime.TotalHours))}:{totalTime.Minutes:D2}"

        Dim rowId As Integer = Integer.Parse(currentRow.Cells(idColumnIndex).Value.ToString())
        Dim dataRow As DataRowModel = originalRows.FirstOrDefault(Function(r) r.Id = rowId)

        If dataRow IsNot Nothing Then
            dataRow.TotalTime = totalTime

            If finishTime < startTime Then
                dataRow.DateFinish = dataRow.DateFinish.AddDays(1)
                currentRow.Cells("dateFinishColumn").Value = dataRow.DateFinish.ToString("d")
            End If
        End If
    End Sub

#End Region

#Region " Undo and Redo Functionality "

    Private Sub PerformUndo(action As DataGridViewAction)
        If action.RowId Is Nothing OrElse action.RowId = "TotalsRowId" Then
            Return
        End If

        Select Case action.Type
            Case DataGridViewAction.ActionType.CellEdit
                Dim dataRow As DataRowModel = originalRows.FirstOrDefault(Function(r) r.Id = Integer.Parse(action.RowId))
                If dataRow IsNot Nothing Then
                    SetPropertyValue(dataRow, action.ColumnIndex, action.OldValue)
                End If

            Case DataGridViewAction.ActionType.RowAdded
                originalRows.RemoveAll(Function(r) r.Id = Integer.Parse(action.RowId))

            Case DataGridViewAction.ActionType.RowDeleted
                If action.DataRow IsNot Nothing Then
                    originalRows.Add(action.DataRow)
                End If

            Case DataGridViewAction.ActionType.RowDuplicated
                originalRows.RemoveAll(Function(r) r.Id = Integer.Parse(action.RowId))
        End Select

        DisplayAllData()
        SaveData()
        UpdateUndoRedoMenuItems()
    End Sub

    Private Sub PerformRedo(action As DataGridViewAction)
        If action.RowId Is Nothing OrElse action.RowId = "TotalsRowId" Then
            Return
        End If

        Select Case action.Type
            Case DataGridViewAction.ActionType.CellEdit
                Dim dataRow As DataRowModel = originalRows.FirstOrDefault(Function(r) r.Id = Integer.Parse(action.RowId))
                If dataRow IsNot Nothing Then
                    SetPropertyValue(dataRow, action.ColumnIndex, action.NewValue)
                End If

            Case DataGridViewAction.ActionType.RowAdded
                If action.DataRow IsNot Nothing Then
                    originalRows.Add(action.DataRow)
                End If

            Case DataGridViewAction.ActionType.RowDeleted
                originalRows.RemoveAll(Function(r) r.Id = Integer.Parse(action.RowId))

            Case DataGridViewAction.ActionType.RowDuplicated
                If action.DataRow IsNot Nothing Then
                    originalRows.Add(action.DataRow)
                End If
        End Select

        DisplayAllData()
        SaveData()
        UpdateUndoRedoMenuItems()
    End Sub

    Private Sub UpdateUndoRedoMenuItems()
        Dim hasData As Boolean = HasDataRows()

        Dim canUndo As Boolean = (undoStack.Count > 0) And hasData
        Dim canRedo As Boolean = (redoStack.Count > 0) And hasData

        menuItemUndo.Enabled = canUndo
        menuItemRedo.Enabled = canRedo

        btnUndo.Enabled = canUndo
        btnRedo.Enabled = canRedo

        btnUndo.Image = If(canUndo, undoImageOriginal, undoImageGrayed)
        btnRedo.Image = If(canRedo, redoImageOriginal, redoImageGrayed)
    End Sub

#End Region

#Region " Helper Methods "

    Private Function HasDataRows() As Boolean
        Return originalRows.Any()
    End Function

    Private Function IsTotalsRow(row As DataGridViewRow) As Boolean
        Return row.Cells(totalsFlagColumnIndex).Value IsNot Nothing AndAlso row.Cells(totalsFlagColumnIndex).Value.ToString() = "True"
    End Function

    Private Function GetTotalsRow() As DataGridViewRow
        For Each row As DataGridViewRow In DGInfo.Rows
            If IsTotalsRow(row) Then
                Return row
            End If
        Next
        Return Nothing
    End Function

    Private Sub UpdateOriginalRowFromDataGridViewRow(dgvRow As DataGridViewRow)
        Dim idValue As Integer = Integer.Parse(dgvRow.Cells(idColumnIndex).Value.ToString())
        Dim dataRow As DataRowModel = originalRows.FirstOrDefault(Function(r) r.Id = idValue)
        If dataRow IsNot Nothing Then
            UpdateDataRowModel(dataRow, "nameColumn", dgvRow.Cells("nameColumn").Value?.ToString())
            UpdateDataRowModel(dataRow, "typeColumn", dgvRow.Cells("typeColumn").Value?.ToString())
            UpdateDataRowModel(dataRow, "dateColumn", dgvRow.Cells("dateColumn").Value?.ToString())
            UpdateDataRowModel(dataRow, "startTimeColumn", dgvRow.Cells("startTimeColumn").Value?.ToString())
            UpdateDataRowModel(dataRow, "finishTimeColumn", dgvRow.Cells("finishTimeColumn").Value?.ToString())
            UpdateDataRowModel(dataRow, "totalTimeColumn", dgvRow.Cells("totalTimeColumn").Value?.ToString())
            UpdateDataRowModel(dataRow, "amountColumn", dgvRow.Cells("amountColumn").Value?.ToString())
            UpdateDataRowModel(dataRow, "commentColumn", dgvRow.Cells("commentColumn").Value?.ToString())
            UpdateDataRowModel(dataRow, "dateFinishColumn", dgvRow.Cells("dateFinishColumn").Value?.ToString())
            UpdateDataRowModel(dataRow, "paidColumn", dgvRow.Cells("paidColumn").Value?.ToString())
        End If
    End Sub

    Private Function GetSelectedRowIndex(sender As Object) As Integer
        Dim rowIndex As Integer = -1

        If TypeOf sender Is ToolStripMenuItem Then
            rowIndex = clickedRowIndex
        ElseIf TypeOf sender Is PictureBox OrElse TypeOf sender Is Button Then
            If DGInfo.SelectedRows.Count > 0 Then
                rowIndex = DGInfo.SelectedRows(0).Index
            ElseIf DGInfo.CurrentCell IsNot Nothing Then
                rowIndex = DGInfo.CurrentCell.RowIndex
            Else
                MessageBox.Show("Please select a row.", "No Row Selected", MessageBoxButtons.OK, MessageBoxIcon.Warning)
            End If
        End If

        Return rowIndex
    End Function

    Private Function ConvertDataGridViewRowToDataRowModel(row As DataGridViewRow) As DataRowModel
        Dim dataRow As New DataRowModel With {
        .Id = Integer.Parse(row.Cells(idColumnIndex).Value.ToString()),
        .Name = row.Cells("nameColumn").Value?.ToString(),
        .Type = row.Cells("typeColumn").Value?.ToString()
    }
        UpdateDataRowModel(dataRow, "dateColumn", row.Cells("dateColumn").Value?.ToString())
        UpdateDataRowModel(dataRow, "startTimeColumn", row.Cells("startTimeColumn").Value?.ToString())
        UpdateDataRowModel(dataRow, "finishTimeColumn", row.Cells("finishTimeColumn").Value?.ToString())
        UpdateDataRowModel(dataRow, "totalTimeColumn", row.Cells("totalTimeColumn").Value?.ToString())
        UpdateDataRowModel(dataRow, "amountColumn", row.Cells("amountColumn").Value?.ToString())
        UpdateDataRowModel(dataRow, "commentColumn", row.Cells("commentColumn").Value?.ToString())
        UpdateDataRowModel(dataRow, "dateFinishColumn", row.Cells("dateFinishColumn").Value?.ToString())
        UpdateDataRowModel(dataRow, "paidColumn", row.Cells("paidColumn").Value?.ToString())
        Return dataRow
    End Function

    Private Function ConvertDataRowModelToObjectArray(dataRow As DataRowModel) As Object()
        Dim totalColumns As Integer = DGInfo.Columns.Count
        Dim rowValues As Object() = New Object(totalColumns - 1) {}

        rowValues(DGInfo.Columns("nameColumn").Index) = dataRow.Name
        rowValues(DGInfo.Columns("typeColumn").Index) = dataRow.Type
        rowValues(DGInfo.Columns("dateColumn").Index) = dataRow.DateValue.ToString("d")
        rowValues(DGInfo.Columns("dateFinishColumn").Index) = dataRow.DateFinish.ToString("d")
        rowValues(DGInfo.Columns("startTimeColumn").Index) = dataRow.StartTime.ToString("hh\:mm")
        rowValues(DGInfo.Columns("finishTimeColumn").Index) = dataRow.FinishTime.ToString("hh\:mm")
        rowValues(DGInfo.Columns("totalTimeColumn").Index) = $"{CInt(Math.Floor(dataRow.TotalTime.TotalHours))}:{dataRow.TotalTime.Minutes:D2}"
        rowValues(DGInfo.Columns("amountColumn").Index) = "£" & dataRow.Amount.ToString("0.00")
        rowValues(DGInfo.Columns("paidColumn").Index) = dataRow.Paid
        rowValues(DGInfo.Columns("commentColumn").Index) = dataRow.Comment
        rowValues(DGInfo.Columns(TOTALS_FLAG_COLUMN_NAME).Index) = ""
        rowValues(DGInfo.Columns(ID_COLUMN_NAME).Index) = dataRow.Id.ToString()

        Return rowValues
    End Function

    Private Sub SetPropertyValue(dataRow As DataRowModel, columnIndex As Integer, value As Object)
        Dim columnName As String = DGInfo.Columns(columnIndex).Name
        Select Case columnName
            Case "nameColumn"
                dataRow.Name = value?.ToString()
            Case "typeColumn"
                dataRow.Type = value?.ToString()
            Case "dateColumn"
                If Date.TryParse(value?.ToString(), dataRow.DateValue) Then
                End If
            Case "startTimeColumn"
                If TimeSpan.TryParse(value?.ToString(), dataRow.StartTime) Then
                End If
            Case "finishTimeColumn"
                If TimeSpan.TryParse(value?.ToString(), dataRow.FinishTime) Then
                End If
            Case "totalTimeColumn"
                If TimeSpan.TryParse(value?.ToString(), dataRow.TotalTime) Then
                End If
            Case "amountColumn"
                If Decimal.TryParse(value?.ToString().Replace("£", ""), dataRow.Amount) Then
                End If
            Case "commentColumn"
                dataRow.Comment = value?.ToString()
            Case "dateFinishColumn"
                If Date.TryParse(value?.ToString(), dataRow.DateFinish) Then
                End If
            Case "paidColumn"
                If Boolean.TryParse(value?.ToString(), dataRow.Paid) Then
                End If
        End Select
    End Sub

    Private Sub UpdateDataRowModel(dataRow As DataRowModel, columnName As String, value As String)
        Select Case columnName
            Case "nameColumn"
                dataRow.Name = value
            Case "typeColumn"
                dataRow.Type = value
            Case "dateColumn"
                If Date.TryParse(value, dataRow.DateValue) Then
                End If
            Case "startTimeColumn"
                If TimeSpan.TryParse(value, dataRow.StartTime) Then
                End If
            Case "finishTimeColumn"
                If TimeSpan.TryParse(value, dataRow.FinishTime) Then
                End If
            Case "totalTimeColumn"
                If TimeSpan.TryParse(value, dataRow.TotalTime) Then
                End If
            Case "amountColumn"
                If Decimal.TryParse(value.Replace("£", ""), dataRow.Amount) Then
                End If
            Case "commentColumn"
                dataRow.Comment = value
            Case "dateFinishColumn"
                If Date.TryParse(value, dataRow.DateFinish) Then
                End If
            Case "paidColumn"
                If Boolean.TryParse(value, dataRow.Paid) Then
                End If
        End Select
    End Sub

#End Region

#Region " CboName "

    Private Sub CboName_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CboName.SelectedIndexChanged
        If CboName.SelectedItem IsNot Nothing Then
            FilterDataGrid(CboName.SelectedItem.ToString(), DTPInfo.Value, DTPInfo2.Value)
        Else
            DisplayAllData()
        End If
    End Sub

    Private Sub FilterDataGrid(name As String, startDate As Date, endDate As Date)
        RemoveHandler DGInfo.CellValueChanged, AddressOf DGInfo_CellValueChanged
        RemoveHandler DGInfo.CellEndEdit, AddressOf DGInfo_CellEndEdit

        DGInfo.Rows.Clear()

        For Each dataRow As DataRowModel In originalRows
            If (String.IsNullOrEmpty(name) OrElse dataRow.Name = name) AndAlso
               dataRow.DateValue.Date >= startDate.Date AndAlso
               dataRow.DateValue.Date <= endDate.Date Then
                Dim rowValues As Object() = ConvertDataRowModelToObjectArray(dataRow)
                DGInfo.Rows.Add(rowValues)
            End If
        Next

        AddTotalsRow()
        UpdateTotals()

        AddHandler DGInfo.CellValueChanged, AddressOf DGInfo_CellValueChanged
        AddHandler DGInfo.CellEndEdit, AddressOf DGInfo_CellEndEdit

        ReapplyLastSort()
    End Sub

    Private Sub DisplayAllData()
        RemoveHandler DGInfo.CellValueChanged, AddressOf DGInfo_CellValueChanged
        RemoveHandler DGInfo.CellEndEdit, AddressOf DGInfo_CellEndEdit

        DGInfo.Rows.Clear()

        For Each dataRow As DataRowModel In originalRows
            Dim rowValues As Object() = ConvertDataRowModelToObjectArray(dataRow)
            DGInfo.Rows.Add(rowValues)
        Next

        AddTotalsRow()
        UpdateTotals()

        AddHandler DGInfo.CellValueChanged, AddressOf DGInfo_CellValueChanged
        AddHandler DGInfo.CellEndEdit, AddressOf DGInfo_CellEndEdit

        ReapplyLastSort()
    End Sub

#End Region

#Region " Other Methods "

    Private Sub ReapplyLastSort()
        If lastSortColumn IsNot Nothing Then
            DGInfo.Sort(lastSortColumn, lastSortDirection)
        End If
    End Sub

    Private Sub UpdateTotals()
        Dim totalDuration As TimeSpan = TimeSpan.Zero
        Dim totalAmount As Decimal = 0D

        For Each row As DataGridViewRow In DGInfo.Rows
            If Not IsTotalsRow(row) Then
                Dim totalTimeCellValue As String = row.Cells("totalTimeColumn").Value?.ToString()
                Dim amountCellValue As String = row.Cells("amountColumn").Value?.ToString()

                If TimeSpan.TryParse(totalTimeCellValue, Nothing) Then
                    totalDuration += TimeSpan.Parse(totalTimeCellValue)
                End If

                If Decimal.TryParse(amountCellValue?.Replace("£", ""), Nothing) Then
                    totalAmount += Decimal.Parse(amountCellValue.Replace("£", ""))
                End If
            End If
        Next

        Dim totalsRow As DataGridViewRow = GetTotalsRow()
        If totalsRow IsNot Nothing Then
            totalsRow.Cells("totalTimeColumn").Value = $"{CInt(Math.Floor(totalDuration.TotalHours))}:{totalDuration.Minutes:D2}"
            totalsRow.Cells("amountColumn").Value = "£" & totalAmount.ToString("0.00")
        End If
    End Sub

    Private Sub AddTotalsRow()
        Dim existingTotalsRow As DataGridViewRow = GetTotalsRow()
        If existingTotalsRow IsNot Nothing Then
            DGInfo.Rows.Remove(existingTotalsRow)
        End If

        Dim totalColumns As Integer = DGInfo.Columns.Count
        Dim totalsRowValues As Object() = New Object(totalColumns - 1) {}

        For i As Integer = 0 To totalColumns - 1
            Select Case DGInfo.Columns(i).Name
                Case "nameColumn"
                    totalsRowValues(i) = "Total"
                Case "totalTimeColumn"
                    totalsRowValues(i) = "0:00"
                Case "amountColumn"
                    totalsRowValues(i) = "£0.00"
                Case TOTALS_FLAG_COLUMN_NAME
                    totalsRowValues(i) = "True"
                Case Else
                    totalsRowValues(i) = ""
            End Select
        Next

        DGInfo.Rows.Add(totalsRowValues)

        Dim totalsRowView As DataGridViewRow = DGInfo.Rows(DGInfo.Rows.Count - 1)

        For Each cell As DataGridViewCell In totalsRowView.Cells
            cell.ReadOnly = True
        Next

        totalsRowView.DefaultCellStyle.BackColor = Color.LightGray
        totalsRowView.DefaultCellStyle.Font = New Font(DGInfo.Font, FontStyle.Bold)

        totalsRowView.Cells(totalsFlagColumnIndex).Value = "True"

        ' Refresh the DataGridView to reapply the CellFormatting logic
        DGInfo.Refresh()
    End Sub

    Private Sub DGInfo_CellFormatting(sender As Object, e As DataGridViewCellFormattingEventArgs) Handles DGInfo.CellFormatting
        If e.RowIndex >= 0 AndAlso e.ColumnIndex >= 0 Then
            Dim currentRow As DataGridViewRow = DGInfo.Rows(e.RowIndex)
            If IsTotalsRow(currentRow) AndAlso DGInfo.Columns(e.ColumnIndex) Is DGInfo.Columns("paidColumn") Then
                e.Value = DBNull.Value ' or Nothing
                e.FormattingApplied = True
            End If
        End If
    End Sub

    Private Sub btnInfo_Click(sender As Object, e As EventArgs) Handles btnInfo.Click
        Dim selectedName As String = If(CboName.SelectedItem IsNot Nothing, CboName.SelectedItem.ToString(), "")
        Dim Frm As New FrmContact(selectedName)
        Frm.Show()
    End Sub

#End Region

#Region " DateTimePicker Event Handler "

    Private Sub DTPInfo_ValueChanged(sender As Object, e As EventArgs) Handles DTPInfo.ValueChanged
        FilterDataGrid(If(CboName.SelectedItem IsNot Nothing, CboName.SelectedItem.ToString(), ""), DTPInfo.Value, DTPInfo2.Value)
    End Sub

    Private Sub DTPInfo2_ValueChanged(sender As Object, e As EventArgs) Handles DTPInfo2.ValueChanged
        FilterDataGrid(If(CboName.SelectedItem IsNot Nothing, CboName.SelectedItem.ToString(), ""), DTPInfo.Value, DTPInfo2.Value)
    End Sub

#End Region

    Private Sub btnInvoice_Click_1(sender As Object, e As EventArgs) Handles btnInvoice.Click
        Dim selectedName As String = If(CboName.SelectedItem IsNot Nothing, CboName.SelectedItem.ToString(), "")
        Dim frmInvoice As New frmInvoice(selectedName)
        frmInvoice.Show()
    End Sub

    Private Sub btnExcel_Click(sender As Object, e As EventArgs) Handles btnExcel.Click
        Try
            Dim filePath As String = GetFilePath("Data", "Data.xlsx")

            If File.Exists(filePath) Then
                Process.Start(New ProcessStartInfo(filePath) With {
                .UseShellExecute = True
            })
            Else
                MessageBox.Show("The spreadsheet file does not exist.", "File Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End If
        Catch ex As Exception
            MessageBox.Show("An error occurred while trying to open the spreadsheet: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

#Region " MonthCalendar for Date Selection "

    Private Sub ShowMonthCalendar(rowIndex As Integer, columnIndex As Integer)
        If rowIndex < 0 OrElse DGInfo.Rows(rowIndex).IsNewRow Then
            Return
        End If

        If monthCalendar Is Nothing Then
            monthCalendar = New MonthCalendar()
            AddHandler monthCalendar.DateSelected, AddressOf MonthCalendar_DateSelected
            monthCalendar.MaxSelectionCount = 1
        End If

        Dim cellRectangle As Rectangle = DGInfo.GetCellDisplayRectangle(columnIndex, rowIndex, True)

        Dim formPoint As Point = DGInfo.PointToClient(DGInfo.PointToScreen(New Point(cellRectangle.Left, cellRectangle.Bottom)))

        formPoint.Y += DGInfo.Rows(rowIndex).Height
        formPoint.Y += 15

        If formPoint.Y + monthCalendar.Height > ClientSize.Height Then
            formPoint.Y = DGInfo.PointToClient(DGInfo.PointToScreen(New Point(cellRectangle.Left, cellRectangle.Top))).Y
            formPoint.Y -= monthCalendar.Height
            formPoint.Y += 35
        End If

        monthCalendar.Location = formPoint

        If DGInfo.Rows(rowIndex).Cells(columnIndex).Value IsNot Nothing Then
            Dim cellDate As Date
            If Date.TryParse(DGInfo.Rows(rowIndex).Cells(columnIndex).Value.ToString(), cellDate) Then
                monthCalendar.SetDate(cellDate)
            End If
        End If

        monthCalendar.Visible = True
        Controls.Add(monthCalendar)
        monthCalendar.BringToFront()
    End Sub

    Private Sub DGInfo_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGInfo.CellClick
        If e.ColumnIndex = DGInfo.Columns("dateColumn").Index OrElse (e.ColumnIndex = DGInfo.Columns("dateFinishColumn").Index AndAlso e.RowIndex >= 0) Then
            ShowMonthCalendar(e.RowIndex, e.ColumnIndex)
        Else
            If monthCalendar IsNot Nothing AndAlso monthCalendar.Visible Then
                monthCalendar.Visible = False
                Controls.Remove(monthCalendar)
            End If
        End If
    End Sub
    Private Sub MonthCalendar_DateSelected(sender As Object, e As DateRangeEventArgs)
        Dim selectedDate As Date = e.Start

        If DGInfo.CurrentCell.ColumnIndex = DGInfo.Columns("dateColumn").Index OrElse DGInfo.CurrentCell.ColumnIndex = DGInfo.Columns("dateFinishColumn").Index Then
            DGInfo.CurrentCell.Value = selectedDate.ToString("d")
            SaveData()
        End If

        monthCalendar.Visible = False
        Controls.Remove(monthCalendar)
    End Sub
#End Region

    Private Sub btnFB_Click(sender As Object, e As EventArgs) Handles btnFB.Click
        Dim psi As New System.Diagnostics.ProcessStartInfo With {
            .FileName = "https://www.facebook.com/profile.php?id=61565337577476",
            .UseShellExecute = True
        }
        Process.Start(psi)
    End Sub

End Class


I'm trying to hide the CheckBox in the Paid Column in the totals row. Can this be achieved?