' =============================================
' Excel Export (XLSX)
' Created: 2025-01-XX
' Purpose: Generate XLSX files from DataTable
'          Used for Repsly product and promotion exports
'          Requires: ClosedXML NuGet package
' =============================================

Imports System.IO
Imports ClosedXML.Excel
Imports Superbowl_Daily_2.ProjectFunctions
Imports Superbowl_Daily_2.SystemFunctions
Imports Superbowl_Daily_2.Export_Process

Public Class ExcelExport

    Public intRecordsTotal, intRecordsExported, intRecordsFail As Integer
    Public Message As String = ""
    Public DT As DataTable
    Public Filename As String
    Public ProcessName As String = ""
    Public blnError As Boolean = False
    Public ParentProcess As Export_Process

    ' =============================================
    ' Constructor
    ' =============================================
    Public Sub New(ByVal table As DataTable, ByVal filename As String, Optional ep As Export_Process = Nothing, Optional ProcessName As String = "Excel Export")
        Me.Filename = filename
        Me.DT = table
        Me.intRecordsTotal = table.Rows.Count
        Me.intRecordsExported = 0
        Me.intRecordsFail = 0
        Me.ProcessName = ProcessName
        Me.ParentProcess = ep
    End Sub

    ' =============================================
    ' Execute - Generate XLSX File
    ' =============================================
    Public Sub Execute()
        Try
            WriteLog("info", ProcessName, "Excel Export", "Export Start: {0/" & intRecordsTotal & "}")

            ' Create workbook
            Using workbook As New XLWorkbook()
                ' Add worksheet
                Dim worksheet As IXLWorksheet = workbook.Worksheets.Add("Data")

                ' Write headers
                Dim colIndex As Integer = 1
                For Each dc As DataColumn In DT.Columns
                    worksheet.Cell(1, colIndex).Value = dc.ColumnName
                    worksheet.Cell(1, colIndex).Style.Font.Bold = True
                    worksheet.Cell(1, colIndex).Style.Fill.BackgroundColor = XLColor.LightGray
                    colIndex += 1
                Next

                ' Write data rows
                Dim rowIndex As Integer = 2
                For Each dr As DataRow In DT.Rows
                    Try
                        colIndex = 1
                        For Each dc As DataColumn In DT.Columns
                            Dim cellValue As Object = dr(dc.ColumnName)
                            
                            ' Handle null values
                            If IsDBNull(cellValue) OrElse cellValue Is Nothing Then
                                worksheet.Cell(rowIndex, colIndex).Value = ""
                            Else
                                ' Set value based on data type
                                Select Case dc.DataType.Name
                                    Case "String"
                                        worksheet.Cell(rowIndex, colIndex).Value = cellValue.ToString()
                                    Case "Int32", "Int64", "Integer"
                                        worksheet.Cell(rowIndex, colIndex).Value = CInt(cellValue)
                                    Case "Double", "Decimal", "Single"
                                        worksheet.Cell(rowIndex, colIndex).Value = CDbl(cellValue)
                                    Case "DateTime", "Date"
                                        worksheet.Cell(rowIndex, colIndex).Value = CDate(cellValue)
                                        worksheet.Cell(rowIndex, colIndex).Style.DateFormat.Format = "yyyy-mm-dd"
                                    Case "Boolean"
                                        worksheet.Cell(rowIndex, colIndex).Value = CBool(cellValue)
                                    Case Else
                                        worksheet.Cell(rowIndex, colIndex).Value = cellValue.ToString()
                                End Select
                            End If
                            
                            colIndex += 1
                        Next

                        intRecordsExported += 1

                        ' Update progress
                        If ParentProcess IsNot Nothing Then
                            ParentProcess.Progress()
                        End If

                        rowIndex += 1

                    Catch ex As Exception
                        intRecordsFail += 1
                        WriteLog("warning", ProcessName, "Excel Export", "Error writing record: [" & ex.Message & "] {" & rowIndex & "/" & intRecordsTotal & "}")
                    End Try
                Next

                ' Auto-fit columns
                worksheet.Columns().AdjustToContents()

                ' Save workbook
                workbook.SaveAs(Filename)
            End Using

            Me.Message = "Export complete: " & Filename & " | Records: " & intRecordsExported.ToString() & "/" & intRecordsTotal.ToString()
            Me.blnError = False
            WriteLog("info", ProcessName, "Excel Export", "Export Completed: {" & intRecordsExported & "/" & intRecordsTotal & " records exported to " & Filename & "}")

        Catch ex As Exception
            Me.blnError = True
            Me.Message = ex.Message & Chr(13) & Chr(10) & ex.StackTrace
            WriteLog("error", ProcessName, "Excel Export", "Export Failed: " & ex.Message & " :: " & ex.StackTrace)
        End Try
    End Sub

    ' =============================================
    ' Execute with Custom Column Mapping
    ' =============================================
    Public Sub ExecuteWithMapping(columnMappings As Dictionary(Of String, String))
        ' columnMappings: Key = DataTable column name, Value = Excel column header
        Try
            WriteLog("info", ProcessName, "Excel Export", "Export Start with Mapping: {0/" & intRecordsTotal & "}")

            Using workbook As New XLWorkbook()
                Dim worksheet As IXLWorksheet = workbook.Worksheets.Add("Data")

                ' Write headers using mapping
                Dim colIndex As Integer = 1
                For Each dc As DataColumn In DT.Columns
                    Dim headerName As String = dc.ColumnName
                    If columnMappings.ContainsKey(dc.ColumnName) Then
                        headerName = columnMappings(dc.ColumnName)
                    End If
                    
                    worksheet.Cell(1, colIndex).Value = headerName
                    worksheet.Cell(1, colIndex).Style.Font.Bold = True
                    worksheet.Cell(1, colIndex).Style.Fill.BackgroundColor = XLColor.LightGray
                    colIndex += 1
                Next

                ' Write data rows (same as Execute method)
                Dim rowIndex As Integer = 2
                For Each dr As DataRow In DT.Rows
                    Try
                        colIndex = 1
                        For Each dc As DataColumn In DT.Columns
                            Dim cellValue As Object = dr(dc.ColumnName)
                            
                            If IsDBNull(cellValue) OrElse cellValue Is Nothing Then
                                worksheet.Cell(rowIndex, colIndex).Value = ""
                            Else
                                Select Case dc.DataType.Name
                                    Case "String"
                                        worksheet.Cell(rowIndex, colIndex).Value = cellValue.ToString()
                                    Case "Int32", "Int64", "Integer"
                                        worksheet.Cell(rowIndex, colIndex).Value = CInt(cellValue)
                                    Case "Double", "Decimal", "Single"
                                        worksheet.Cell(rowIndex, colIndex).Value = CDbl(cellValue)
                                    Case "DateTime", "Date"
                                        worksheet.Cell(rowIndex, colIndex).Value = CDate(cellValue)
                                        worksheet.Cell(rowIndex, colIndex).Style.DateFormat.Format = "yyyy-mm-dd"
                                    Case "Boolean"
                                        worksheet.Cell(rowIndex, colIndex).Value = CBool(cellValue)
                                    Case Else
                                        worksheet.Cell(rowIndex, colIndex).Value = cellValue.ToString()
                                End Select
                            End If
                            
                            colIndex += 1
                        Next

                        intRecordsExported += 1

                        If ParentProcess IsNot Nothing Then
                            ParentProcess.Progress()
                        End If

                        rowIndex += 1

                    Catch ex As Exception
                        intRecordsFail += 1
                        WriteLog("warning", ProcessName, "Excel Export", "Error writing record: [" & ex.Message & "] {" & rowIndex & "/" & intRecordsTotal & "}")
                    End Try
                Next

                worksheet.Columns().AdjustToContents()
                workbook.SaveAs(Filename)
            End Using

            Me.Message = "Export complete: " & Filename & " | Records: " & intRecordsExported.ToString() & "/" & intRecordsTotal.ToString()
            Me.blnError = False
            WriteLog("info", ProcessName, "Excel Export", "Export Completed: {" & intRecordsExported & "/" & intRecordsTotal & " records exported to " & Filename & "}")

        Catch ex As Exception
            Me.blnError = True
            Me.Message = ex.Message & Chr(13) & Chr(10) & ex.StackTrace
            WriteLog("error", ProcessName, "Excel Export", "Export Failed: " & ex.Message & " :: " & ex.StackTrace)
        End Try
    End Sub

End Class