' =============================================
' Repsly Promotion Export Process
' Created: 2025-01-XX
' Purpose: Export promotions to Repsly API (using same product endpoint as product export)
'          - Export list from promoActivity only (not dwfPromo); one row per activity
'          - Check queue table for new/updated promotion activities
'          - Map promotion fields to Repsly product schema
'          - Generate XLSX file (product column layout)
'          - Upload to FTP
'          - Push to Repsly API (/import/productList)
'          - Update queue table
' =============================================

Imports Superbowl_Daily_2.SystemFunctions
Imports Superbowl_Daily_2.ProjectFunctions
Imports Superbowl_Daily_2.db
Imports Superbowl_Daily_2.Export_Process
Imports Superbowl_Daily_2.ExcelExport
Imports Superbowl_Daily_2.FTPClient
Imports Superbowl_Daily_2.RepslyAPIClient
Imports Newtonsoft.Json
Imports System.IO
Imports System.Collections.Generic

Public Class RepslyPromotionExport_Process : Inherits Export_Process

   ' =============================================
   ' Constructor
   ' =============================================
   Sub New(ExportProcess As Export_Process)
      MyBase.New(ExportProcess.Name, ExportProcess.Filename, ExportProcess.Path, ExportProcess.SQL)
      Me.barProcess = ExportProcess.barProcess
   End Sub

   ' =============================================
   ' Override Run Method
   ' =============================================
   Public Shadows Sub Run()
      '1. Log start
      '2. Initialize queue (add new/updated promotions)
      '3. Query promotions to process (product-shaped)
      '4. Generate XLSX file
      '5. Upload to FTP
      '6. Push to Repsly API (product endpoint)
      '7. Update queue table
      '8. Log complete

      Me.LogProcessStart()

      Try
         '2. Initialize queue - add new/updated promotions
         InitializeQueue()

         '3. Query promotions to process (product-shaped columns)
         Dim promotionsToProcess As DataSet = GetPromotionsToProcess()
         Me.intRecordsTotal = promotionsToProcess.Tables(0).Rows.Count

         If Me.intRecordsTotal = 0 Then
            WriteLog("info", Me.Name, "Process", "No promotions to process")
            Me.LogProcessCompleted()
            Return
         End If

         Me.setProgress()

         '4. Generate XLSX file (product column layout)
         Dim xlsxFile As String = My.Settings.dirExports & Me.Filename
         If Not GenerateXLSXFile(promotionsToProcess, xlsxFile) Then
            WriteLog("error", Me.Name, "Process", "Failed to generate XLSX file")
            Me.LogProcessCompleted()
            Return
         End If

         '5. Upload to FTP
         Dim ftpClient As New FTPClient(Me.Name)
         If Not ftpClient.UploadFile(xlsxFile, Me.Filename) Then
            WriteLog("error", Me.Name, "Process", "Failed to upload file to FTP")
            ' Continue anyway - API push can still work
         End If

         '6. Push to Repsly API (same endpoint as product export)
         Dim apiClient As New RepslyAPIClient(Me.Name)
         PushToRepslyAPI(promotionsToProcess, apiClient)

      Catch ex As Exception
         WriteLog("error", Me.Name, "Process Exception", ex.Message & " :: " & ex.StackTrace)
      Finally
         Me.LogProcessCompleted()
      End Try
   End Sub

   ' =============================================
   ' Initialize Queue - Add new/updated promotions
   ' =============================================
   Private Sub InitializeQueue()
      Try
         WriteLog("info", Me.Name, "Initialize Queue", "Checking for new/updated promotions...")

         ' Add new promotion activities (from promoActivity only; one row per ActivityID)
         Dim sqlNew As String = "INSERT INTO sysCronJobPromotionActivities (ActivityID, Status, LastUpdated) " &
                                   "SELECT a.ActivityID, 'Pending', GETDATE() " &
                                   "FROM promoActivity a " &
                                   "WHERE a.ActivityID NOT IN (SELECT ActivityID FROM sysCronJobPromotionActivities)"

         Dim xdb As New db
         xdb.doQuery(sqlNew)
         If xdb.intRows > 0 Then
            WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " new promotion(s) added to queue")
         End If

         ' Mark updated promotion activities (promoActivity.dtStamp changed since LastProcessed)
         ' Note: dtStamp is nvarchar(12) in format YYYYMMDDHHMM
         Dim sqlUpdated As String = "UPDATE sysCronJobPromotionActivities " &
                                       "SET Status = 'Updated', LastUpdated = GETDATE() " &
                                       "FROM sysCronJobPromotionActivities q " &
                                       "INNER JOIN promoActivity a ON q.ActivityID = a.ActivityID " &
                                       "WHERE q.Status = 'Completed' " &
                                       "AND (q.LastProcessed IS NULL OR " &
                                       "     (LEN(a.dtStamp) = 12 AND " &
                                       "      ISNUMERIC(a.dtStamp) = 1 AND " &
                                       "      CONVERT(datetime, " &
                                       "        LEFT(a.dtStamp, 4) + '-' + " &
                                       "        SUBSTRING(a.dtStamp, 5, 2) + '-' + " &
                                       "        SUBSTRING(a.dtStamp, 7, 2) + ' ' + " &
                                       "        SUBSTRING(a.dtStamp, 9, 2) + ':' + " &
                                       "        SUBSTRING(a.dtStamp, 11, 2) + ':00', 120) > q.LastProcessed))"

         xdb.doQuery(sqlUpdated)
         If xdb.intRows > 0 Then
            WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " promotion(s) marked as updated")
         End If

         ' Reset failed promotion activities for retry
         Dim sqlRetry As String = "UPDATE sysCronJobPromotionActivities " &
                                     "SET Status = 'Pending', ErrorLog = NULL " &
                                     "WHERE Status = 'Failed' " &
                                     "AND ActivityID IN (SELECT ActivityID FROM promoActivity)"

         xdb.doQuery(sqlRetry)
         If xdb.intRows > 0 Then
            WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " failed promotion(s) reset for retry")
         End If

      Catch ex As Exception
         WriteLog("error", Me.Name, "Initialize Queue Exception", ex.Message & " :: " & ex.StackTrace)
      End Try
   End Sub

   ' =============================================
   ' Get Promotions to Process (product-shaped for /import/productList)
   ' Source: promoActivity only (not dwfPromo). One row per activity. Code = "PR" + ActivityID.
   ' Includes both active and inactive; Active column = promoActivity.blnActive.
   ' =============================================
   Private Function GetPromotionsToProcess() As DataSet
      ' Query from queue + promoActivity only; Code = PR + 4-digit zero-padded ActivityID (e.g. PR0290, PR0005)
      Dim sql As String = "SELECT " &
                            "'PR' + RIGHT('0000' + CAST(q.ActivityID AS VARCHAR), 4) AS Code, " &
                            "ISNULL(a.strActivityName, '') AS Name, " &
                            "CAST(ISNULL(a.refBrandID, 0) AS VARCHAR) AS ProductGroupCode, " &
                            "ISNULL(b.strBrand, '') AS ProductGroupName, " &
                            "CAST(ISNULL(a.blnActive, 0) AS BIT) AS Active, " &
                            "CAST(0 AS DECIMAL(18,4)) AS UnitPrice, " &
                            "'' AS EAN, " &
                            "0 AS Size, " &
                            "0 AS Package, " &
                            "'' AS CBarcode, " &
                            "'' AS GBarcode, " &
                            "REPLACE(LTRIM(RTRIM(ISNULL(a.strTags, ''))), '|', ', ') AS Tag, " &
                            "q.QueueID, " &
                            "'PR' + RIGHT('0000' + CAST(q.ActivityID AS VARCHAR), 4) AS strProdCode " &
                            "FROM sysCronJobPromotionActivities q " &
                            "INNER JOIN promoActivity a ON q.ActivityID = a.ActivityID " &
                            "LEFT JOIN mstBrand b ON a.refBrandID = b.BrandID " &
                            "WHERE q.Status IN ('Pending', 'Updated') " &
                            "ORDER BY q.QueueID"

      Return db.doQuery(sql)
   End Function

   ' =============================================
   ' Generate XLSX File (product column layout, same as product export)
   ' =============================================
   Private Function GenerateXLSXFile(ds As DataSet, filePath As String) As Boolean
      Try
         WriteLog("info", Me.Name, "Generate XLSX", "Generating XLSX file: " & filePath)

         Dim excelExport As New ExcelExport(ds.Tables(0), filePath, Me, Me.Name)
         excelExport.Execute()

         If excelExport.blnError Then
            WriteLog("error", Me.Name, "Generate XLSX", "Failed: " & excelExport.Message)
            Return False
         Else
            WriteLog("info", Me.Name, "Generate XLSX", "Success: " & excelExport.intRecordsExported & " records exported")
            Return True
         End If

      Catch ex As Exception
         WriteLog("error", Me.Name, "Generate XLSX Exception", ex.Message & " :: " & ex.StackTrace)
         Return False
      End Try
   End Function

   ' =============================================
   ' Push to Repsly API
   ' Uses same endpoint as product export: /import/productList, batches of 200
   ' =============================================
   Private Sub PushToRepslyAPI(ds As DataSet, apiClient As RepslyAPIClient)
      Try
         Dim totalPromotions As Integer = ds.Tables(0).Rows.Count
         WriteLog("info", Me.Name, "Push to API", "Pushing " & totalPromotions & " promotion(s) to Repsly API in batches of 200")

         Const BATCH_SIZE As Integer = 200
         Dim batchNumber As Integer = 1
         Dim totalBatches As Integer = CInt(Math.Ceiling(totalPromotions / BATCH_SIZE))
         Dim totalSuccess As Integer = 0
         Dim totalFailed As Integer = 0

         For batchStart As Integer = 0 To totalPromotions - 1 Step BATCH_SIZE
            Dim batchEnd As Integer = Math.Min(batchStart + BATCH_SIZE - 1, totalPromotions - 1)
            Dim batchSize As Integer = batchEnd - batchStart + 1

            WriteLog("info", Me.Name, "Push to API", "Processing batch " & batchNumber & " of " & totalBatches & " (" & batchSize & " promotions)")

            Dim productsList As New List(Of Object)
            Dim batchRows As New List(Of DataRow)

            For i As Integer = batchStart To batchEnd
               Dim dr As DataRow = ds.Tables(0).Rows(i)
               batchRows.Add(dr)
               Dim product As Dictionary(Of String, Object) = BuildProductDictionary(dr)
               productsList.Add(product)
            Next

            Dim payload As New Dictionary(Of String, Object)
            payload("Products") = productsList
            Dim jsonData As String = JsonConvert.SerializeObject(payload)

            Dim response As RepslyAPIResponse = apiClient.PostAsync("/import/productList", jsonData)

            Dim importJobID As String = ""
            If response.Code = 0 AndAlso response.Message.Contains("ID:") Then
               Dim idStart As Integer = response.Message.IndexOf("ID:") + 4
               Dim idEnd As Integer = response.Message.IndexOf(" ", idStart)
               If idEnd = -1 Then idEnd = response.Message.Length
               importJobID = response.Message.Substring(idStart, idEnd - idStart).Trim()
            End If

            If response.Code = 0 Then
               For Each dr As DataRow In batchRows
                  UpdateQueueRecord(dr, "Completed", importJobID, BuildProductDictionary(dr))
               Next
               totalSuccess += batchSize
               WriteLog("info", Me.Name, "Push to API", "Batch " & batchNumber & " completed successfully. ImportJobID: " & importJobID)
            Else
               For Each dr As DataRow In batchRows
                  UpdateQueueRecord(dr, "Failed", "", Nothing, response.Message)
               Next
               totalFailed += batchSize
               WriteLog("error", Me.Name, "Push to API", "Batch " & batchNumber & " failed: " & response.Message)
            End If

            batchNumber += 1
         Next

         WriteLog("info", Me.Name, "Push to API", "Batch processing complete. Success: " & totalSuccess & ", Failed: " & totalFailed & " out of " & totalPromotions & " total promotions")

      Catch ex As Exception
         WriteLog("error", Me.Name, "Push to API Exception", ex.Message & " :: " & ex.StackTrace)
      End Try
   End Sub

   ' =============================================
   ' Build Product Dictionary from DataRow (same shape as product export)
   ' =============================================
   Private Function BuildProductDictionary(dr As DataRow) As Dictionary(Of String, Object)
      Dim product As New Dictionary(Of String, Object)

      product("Code") = dr("Code").ToString()
      product("Name") = dr("Name").ToString()
      product("ProductGroupCode") = dr("ProductGroupCode").ToString()
      product("ProductGroupName") = dr("ProductGroupName").ToString()
      product("Active") = CBool(dr("Active"))
      product("UnitPrice") = CDec(dr("UnitPrice"))

      If Not String.IsNullOrEmpty(dr("EAN").ToString()) Then
         product("EAN") = dr("EAN").ToString()
      End If

      ' CustomAttributes: promotions use defaults (0/empty), so omit for promotions
      Dim customAttrs As New List(Of Object)
      If Not IsDBNull(dr("Size")) AndAlso CInt(dr("Size")) > 0 Then
         Dim attr As New Dictionary(Of String, Object)
         attr("AttributeTitle") = "Size"
         attr("Value") = CInt(dr("Size"))
         customAttrs.Add(attr)
      End If
      If Not IsDBNull(dr("Package")) AndAlso CInt(dr("Package")) > 0 Then
         Dim attr As New Dictionary(Of String, Object)
         attr("AttributeTitle") = "Package"
         attr("Value") = CInt(dr("Package"))
         customAttrs.Add(attr)
      End If
      If Not String.IsNullOrEmpty(dr("CBarcode").ToString()) Then
         Dim attr As New Dictionary(Of String, Object)
         attr("AttributeTitle") = "CBarcode"
         attr("Value") = dr("CBarcode").ToString()
         customAttrs.Add(attr)
      End If
      If Not String.IsNullOrEmpty(dr("GBarcode").ToString()) Then
         Dim attr As New Dictionary(Of String, Object)
         attr("AttributeTitle") = "GBarcode"
         attr("Value") = dr("GBarcode").ToString()
         customAttrs.Add(attr)
      End If
      If customAttrs.Count > 0 Then
         product("CustomAttributes") = customAttrs
      End If

      ' Tag - promoActivity.strTags (pipe -> comma in SQL); omit when empty
      Dim tagVal As String = If(IsDBNull(dr("Tag")), "", dr("Tag").ToString().Trim())
      If tagVal.StartsWith(",") Then tagVal = tagVal.TrimStart(","c).Trim()
      If Not String.IsNullOrEmpty(tagVal) Then
         product("Tag") = tagVal
      End If

      Return product
   End Function

   ' =============================================
   ' Update Queue Record (sysCronJobPromotionActivities)
   ' =============================================
   Private Sub UpdateQueueRecord(dr As DataRow, status As String, importJobID As String, Optional productDict As Dictionary(Of String, Object) = Nothing, Optional errorMessage As String = "")
      Try
         Dim productJson As String = ""
         If productDict IsNot Nothing Then
            productJson = JsonConvert.SerializeObject(productDict)
         End If

         Dim sqlUpdate As String = ""
         If status = "Completed" Then
            sqlUpdate = "UPDATE sysCronJobPromotionActivities " &
                           "SET Status = 'Completed', " &
                           "    ImportJobID = '" & db.CleanString(importJobID) & "', " &
                           "    LastProcessed = GETDATE(), " &
                           "    LastUpdated = GETDATE(), " &
                           "    RequestPayload = '" & db.CleanString(productJson) & "', " &
                           "    dtLastEdit = GETDATE() " &
                           "WHERE QueueID = " & dr("QueueID").ToString()
         Else
            sqlUpdate = "UPDATE sysCronJobPromotionActivities " &
                           "SET Status = 'Failed', " &
                           "    ErrorLog = '" & db.CleanString(errorMessage) & "', " &
                           "    LastUpdated = GETDATE(), " &
                           "    dtLastEdit = GETDATE() " &
                           "WHERE QueueID = " & dr("QueueID").ToString()
         End If

         Try
            db.doQuery(sqlUpdate)
         Catch ex As Exception
            If ex.Message.Contains("Invalid column name 'RequestPayload'") AndAlso status = "Completed" Then
               sqlUpdate = "UPDATE sysCronJobPromotionActivities " &
                               "SET Status = 'Completed', " &
                               "    ImportJobID = '" & db.CleanString(importJobID) & "', " &
                               "    LastProcessed = GETDATE(), " &
                               "    LastUpdated = GETDATE(), " &
                               "    ErrorLog = 'RequestPayload: ' + '" & db.CleanString(productJson) & "', " &
                               "    dtLastEdit = GETDATE() " &
                               "WHERE QueueID = " & dr("QueueID").ToString()
               db.doQuery(sqlUpdate)
               WriteLog("warning", Me.Name, "Push to API", "RequestPayload column not found, stored in ErrorLog instead. Please run script 05_Add_RequestPayload_Column.sql")
            Else
               Throw
            End If
         End Try
      Catch ex As Exception
         WriteLog("error", Me.Name, "Update Queue Record Exception", "QueueID: " & dr("QueueID").ToString() & " - " & ex.Message)
      End Try
   End Sub

End Class