' =============================================
' Repsly Customer Export Process
' Created: 2025-12-03
' Purpose: Export customers to Repsly API
'          - Query active customers from mstCustomer (RGBC) and mstNot_RGBC_Customer (Non-RGBC)
'          - Check queue table for new/updated customers
'          - Generate XLSX file
'          - Upload to FTP
'          - Push to Repsly API (clientList endpoint)
'          - Update queue table
'          - Process in batches of 200 (Repsly API limit)
' =============================================

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 RepslyCustomerExport_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 customers)
		'3. Query customers to process
		'4. Generate XLSX file
		'5. Upload to FTP
		'6. Push to Repsly API
		'7. Update queue table
		'8. Log complete

		Me.LogProcessStart()

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

			'3. Query customers to process
			Dim customersToProcess As DataSet = GetCustomersToProcess()
			Me.intRecordsTotal = customersToProcess.Tables(0).Rows.Count

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

			Me.setProgress()

			'4. Generate XLSX file
			Dim xlsxFile As String = My.Settings.dirExports & Me.Filename
			If Not GenerateXLSXFile(customersToProcess, 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
			Dim apiClient As New RepslyAPIClient(Me.Name)
			PushToRepslyAPI(customersToProcess, apiClient)

			'7. Update queue table (handled in PushToRepslyAPI)

		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 customers
	' Handles both RGBC (mstCustomer) and Non-RGBC (mstNot_RGBC_Customer) customers
	' Only processes active customers (blnActive = 1)
	' =============================================
	Private Sub InitializeQueue()
		Try
			WriteLog("info", Me.Name, "Initialize Queue", "Checking for new/updated active customers (RGBC and Non-RGBC)...")

			Dim xdb As New db

			' Add new RGBC customers (active only, not in queue)
			Dim sqlNewRGBC As String = "INSERT INTO sysCronJobCustomers (strCustomerNo, CustomerType, Status, LastUpdated) " &
												  "SELECT strCustomerNo, 'RGBC', 'Pending', GETDATE() " &
												  "FROM mstCustomer " &
												  "WHERE blnActive = 1 " &
												  "AND strCustomerNo NOT IN (SELECT strCustomerNo FROM sysCronJobCustomers)"

			xdb.doQuery(sqlNewRGBC)
			If xdb.intRows > 0 Then
				WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " new RGBC customer(s) added to queue")
			End If

			' Add new Non-RGBC customers (active only, not in queue)
			Dim sqlNewNonRGBC As String = "INSERT INTO sysCronJobCustomers (strCustomerNo, CustomerType, Status, LastUpdated) " &
													  "SELECT strCustomerNo, 'NonRGBC', 'Pending', GETDATE() " &
													  "FROM mstNot_RGBC_Customer " &
													  "WHERE blnActive = 1 " &
													  "AND strCustomerNo NOT IN (SELECT strCustomerNo FROM sysCronJobCustomers)"

			xdb.doQuery(sqlNewNonRGBC)
			If xdb.intRows > 0 Then
				WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " new Non-RGBC customer(s) added to queue")
			End If

			' Mark updated RGBC customers (dtStamp changed since LastProcessed)
			' Note: dtStamp may be nvarchar(12) in format YYYYMMDDHHMM (includes time)
			' Convert format: 202511281535 -> 2025-11-28 15:35:00
			' SQL Server 2008 compatible (no TRY_CONVERT) - validate format first, then convert
			Dim sqlUpdatedRGBC As String = "UPDATE sysCronJobCustomers " &
													  "SET Status = 'Updated', LastUpdated = GETDATE() " &
													  "FROM sysCronJobCustomers q " &
													  "INNER JOIN mstCustomer c ON q.strCustomerNo = c.strCustomerNo " &
													  "WHERE q.CustomerType = 'RGBC' " &
													  "AND q.Status = 'Completed' " &
													  "AND c.blnActive = 1 " &
													  "AND (q.LastProcessed IS NULL OR " &
													  "     (LEN(c.dtStamp) = 12 AND " &
													  "      ISNUMERIC(c.dtStamp) = 1 AND " &
													  "      CONVERT(datetime, " &
													  "        LEFT(c.dtStamp, 4) + '-' + " &
													  "        SUBSTRING(c.dtStamp, 5, 2) + '-' + " &
													  "        SUBSTRING(c.dtStamp, 7, 2) + ' ' + " &
													  "        SUBSTRING(c.dtStamp, 9, 2) + ':' + " &
													  "        SUBSTRING(c.dtStamp, 11, 2) + ':00', 120) > q.LastProcessed))"

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

			' Mark updated Non-RGBC customers (dtStamp changed since LastProcessed)
			Dim sqlUpdatedNonRGBC As String = "UPDATE sysCronJobCustomers " &
														 "SET Status = 'Updated', LastUpdated = GETDATE() " &
														 "FROM sysCronJobCustomers q " &
														 "INNER JOIN mstNot_RGBC_Customer c ON q.strCustomerNo = c.strCustomerNo " &
														 "WHERE q.CustomerType = 'NonRGBC' " &
														 "AND q.Status = 'Completed' " &
														 "AND c.blnActive = 1 " &
														 "AND (q.LastProcessed IS NULL OR " &
														 "     (LEN(c.dtStamp) = 12 AND " &
														 "      ISNUMERIC(c.dtStamp) = 1 AND " &
														 "      CONVERT(datetime, " &
														 "        LEFT(c.dtStamp, 4) + '-' + " &
														 "        SUBSTRING(c.dtStamp, 5, 2) + '-' + " &
														 "        SUBSTRING(c.dtStamp, 7, 2) + ' ' + " &
														 "        SUBSTRING(c.dtStamp, 9, 2) + ':' + " &
														 "        SUBSTRING(c.dtStamp, 11, 2) + ':00', 120) > q.LastProcessed))"

			xdb.doQuery(sqlUpdatedNonRGBC)
			If xdb.intRows > 0 Then
				WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " Non-RGBC customer(s) marked as updated")
			End If

			' Reset failed customers for retry
			Dim sqlRetry As String = "UPDATE sysCronJobCustomers " &
												 "SET Status = 'Pending', ErrorLog = NULL " &
												 "WHERE Status = 'Failed'"

			xdb.doQuery(sqlRetry)
			If xdb.intRows > 0 Then
				WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " failed customer(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 Customers to Process
	' Combines RGBC and Non-RGBC customers using UNION ALL
	' Only processes active customers (blnActive = 1)
	' =============================================
	Private Function GetCustomersToProcess() As DataSet
		' Query customers that need to be processed
		' Mapping per Repsly clientList API requirements:
		' strCustomerNo -> Code
		' strCustomerName -> Name
		' blnActive -> Active
		' refRepID -> RepresentativeCode (direct, no mapping needed)
		' strRepName -> RepresentativeName (from mstRep table)
		' strRegionDesc2 -> Territory (from mstBranch table)
		' keyAccountID -> AccountCode (from mstKeyAccount table via mstKeyAccount_Group)
		' strKeyAccount -> AccountName (from mstKeyAccount table, not in API, but included in Excel for reference)
		' Segmentation fields -> Tag (from mstChannelSegment table: strSegmentPrimary, strSegment, strChannelPrimary, strChannel)

		' RGBC Customers Query
		Dim sqlRGBC As String = "SELECT " &
									 "c.strCustomerNo AS Code, " &
									 "ISNULL(c.strCustomerName, '') AS Name, " &
									 "CAST(ISNULL(c.blnActive, 0) AS BIT) AS Active, " &
									 "CAST(ISNULL(c.refRepID, 0) AS INT) AS RepresentativeCode, " &
									 "ISNULL(r.strRepName, '') AS RepresentativeName, " &
									 "ISNULL(b.strRegionDesc2, '') AS Territory, " &
									 "ISNULL(k.keyAccountID, '') AS AccountCode, " &
									 "ISNULL(k.strKeyAccount, '') AS AccountName, " &
									 "ISNULL(cs.strSegmentPrimary, '') AS strSegmentPrimary, " &
									 "ISNULL(cs.strSegment, '') AS strSegment, " &
									 "ISNULL(cs.strChannelPrimary, '') AS strChannelPrimary, " &
									 "ISNULL(cs.strChannel, '') AS strChannel, " &
									 "q.QueueID, " &
									 "q.strCustomerNo " &
									 "FROM sysCronJobCustomers q " &
									 "INNER JOIN mstCustomer c ON q.strCustomerNo = c.strCustomerNo " &
									 "LEFT JOIN mstRep r ON c.refRepID = r.RepID " &
									 "LEFT JOIN mstKeyAccount_Group kg ON c.refKeyAccount_GroupID = kg.KeyAccount_GroupID " &
									 "LEFT JOIN mstKeyAccount k ON kg.refKeyAccountID = k.KeyAccountID " &
									 "LEFT JOIN mstBranch b ON c.refBranchID = b.BranchID " &
									 "LEFT JOIN mstChannelSegment cs ON c.refChannelSegmentID = cs.ChannelSegmentID " &
									 "WHERE q.Status IN ('Pending', 'Updated') " &
									 "AND q.CustomerType = 'RGBC' " &
									 "AND c.blnActive = 1 "

		' Non-RGBC Customers Query
		Dim sqlNonRGBC As String = "SELECT " &
									 "c.strCustomerNo AS Code, " &
									 "ISNULL(c.strCustomerName, '') AS Name, " &
									 "CAST(ISNULL(c.blnActive, 0) AS BIT) AS Active, " &
									 "CAST(ISNULL(c.refRepID, 0) AS INT) AS RepresentativeCode, " &
									 "ISNULL(r.strRepName, '') AS RepresentativeName, " &
									 "ISNULL(b.strRegionDesc2, '') AS Territory, " &
									 "ISNULL(k.keyAccountID, '') AS AccountCode, " &
									 "ISNULL(k.strKeyAccount, '') AS AccountName, " &
									 "ISNULL(cs.strSegmentPrimary, '') AS strSegmentPrimary, " &
									 "ISNULL(cs.strSegment, '') AS strSegment, " &
									 "ISNULL(cs.strChannelPrimary, '') AS strChannelPrimary, " &
									 "ISNULL(cs.strChannel, '') AS strChannel, " &
									 "q.QueueID, " &
									 "q.strCustomerNo " &
									 "FROM sysCronJobCustomers q " &
									 "INNER JOIN mstNot_RGBC_Customer c ON q.strCustomerNo = c.strCustomerNo " &
									 "LEFT JOIN mstRep r ON c.refRepID = r.RepID " &
									 "LEFT JOIN mstKeyAccount_Group kg ON c.refKeyAccount_GroupID = kg.KeyAccount_GroupID " &
									 "LEFT JOIN mstKeyAccount k ON kg.refKeyAccountID = k.KeyAccountID " &
									 "LEFT JOIN mstBranch b ON c.refBranchID = b.BranchID " &
									 "LEFT JOIN mstChannelSegment cs ON c.refChannelSegmentID = cs.ChannelSegmentID " &
									 "WHERE q.Status IN ('Pending', 'Updated') " &
									 "AND q.CustomerType = 'NonRGBC' " &
									 "AND c.blnActive = 1 "

		' Combine both queries with UNION ALL
		Dim sql As String = sqlRGBC & " UNION ALL " & sqlNonRGBC & " ORDER BY QueueID"

		Return db.doQuery(sql)
	End Function

	' =============================================
	' Generate XLSX File
	' =============================================
	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
	' Processes customers in batches of 200 (Repsly API limit)
	' =============================================
	Private Sub PushToRepslyAPI(ds As DataSet, apiClient As RepslyAPIClient)
		Try
			Dim totalCustomers As Integer = ds.Tables(0).Rows.Count
			WriteLog("info", Me.Name, "Push to API", "Pushing " & totalCustomers & " customer(s) to Repsly API in batches of 200")

			' Repsly API limit: 200 customers per request
			Const BATCH_SIZE As Integer = 200
			Dim batchNumber As Integer = 1
			Dim totalBatches As Integer = CInt(Math.Ceiling(totalCustomers / BATCH_SIZE))
			Dim totalSuccess As Integer = 0
			Dim totalFailed As Integer = 0

			' Process customers in batches
			For batchStart As Integer = 0 To totalCustomers - 1 Step BATCH_SIZE
				Dim batchEnd As Integer = Math.Min(batchStart + BATCH_SIZE - 1, totalCustomers - 1)
				Dim batchSize As Integer = batchEnd - batchStart + 1

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

				' Build clients array for this batch
				Dim clientsList 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)

					' Build client dictionary
					Dim client As Dictionary(Of String, Object) = BuildClientDictionary(dr)
					clientsList.Add(client)
				Next

				' Create JSON payload for this batch
				Dim payload As New Dictionary(Of String, Object)
				payload("Clients") = clientsList
				Dim jsonData As String = JsonConvert.SerializeObject(payload)

				' Call API for this batch
				Dim response As RepslyAPIResponse = apiClient.PostAsync("/import/clientList", jsonData)

				' Extract ImportJobID from response message
				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

				' Update queue table for this batch
				If response.Code = 0 Then
					' Success - mark as completed
					For Each dr As DataRow In batchRows
						UpdateQueueRecord(dr, "Completed", importJobID, BuildClientDictionary(dr))
					Next
					totalSuccess += batchSize
					WriteLog("info", Me.Name, "Push to API", "Batch " & batchNumber & " completed successfully. ImportJobID: " & importJobID)
				Else
					' Failed - mark as failed
					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

			' Summary log
			WriteLog("info", Me.Name, "Push to API", "Batch processing complete. Success: " & totalSuccess & ", Failed: " & totalFailed & " out of " & totalCustomers & " total customers")

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

	' =============================================
	' Build Client Dictionary from DataRow
	' =============================================
	Private Function BuildClientDictionary(dr As DataRow) As Dictionary(Of String, Object)
		Dim client As New Dictionary(Of String, Object)

		' Required fields
		client("Code") = dr("Code").ToString()
		client("Name") = dr("Name").ToString()
		client("Active") = CBool(dr("Active"))

		' Representative Code (use refRepID directly)
		If Not IsDBNull(dr("RepresentativeCode")) AndAlso CInt(dr("RepresentativeCode")) > 0 Then
			client("RepresentativeCode") = CInt(dr("RepresentativeCode")).ToString()
		End If

		' Representative Name
		If Not String.IsNullOrEmpty(dr("RepresentativeName").ToString()) Then
			client("RepresentativeName") = dr("RepresentativeName").ToString()
		End If

		' Territory (single value, not hierarchy)
		If Not String.IsNullOrEmpty(dr("Territory").ToString()) Then
			client("Territory") = dr("Territory").ToString()
		End If

		' Tag field - Customer segmentation
		' Format: "strSegmentPrimary, strSegment, strChannelPrimary, strChannel"
		Dim tagParts As New List(Of String)
		
		' Add each segmentation field if not empty
		Dim strSegmentPrimary As String = If(IsDBNull(dr("strSegmentPrimary")), "", dr("strSegmentPrimary").ToString().Trim())
		Dim strSegment As String = If(IsDBNull(dr("strSegment")), "", dr("strSegment").ToString().Trim())
		Dim strChannelPrimary As String = If(IsDBNull(dr("strChannelPrimary")), "", dr("strChannelPrimary").ToString().Trim())
		Dim strChannel As String = If(IsDBNull(dr("strChannel")), "", dr("strChannel").ToString().Trim())
		
		' Build tag string in the specified format
		If Not String.IsNullOrEmpty(strSegmentPrimary) Then tagParts.Add(strSegmentPrimary)
		If Not String.IsNullOrEmpty(strSegment) Then tagParts.Add(strSegment)
		If Not String.IsNullOrEmpty(strChannelPrimary) Then tagParts.Add(strChannelPrimary)
		If Not String.IsNullOrEmpty(strChannel) Then tagParts.Add(strChannel)
		
		' Only add Tag field if at least one part exists
		If tagParts.Count > 0 Then
			client("Tag") = String.Join(", ", tagParts)
		End If

		' Account Code (optional - only include if not empty and not 0)
		Dim accountCode As String = dr("AccountCode").ToString()
		If Not String.IsNullOrEmpty(accountCode) AndAlso accountCode <> "0" Then
			client("AccountCode") = accountCode
		End If

		Return client
	End Function

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

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

			' Try to update with RequestPayload column, if it doesn't exist, catch and use ErrorLog
			Try
				db.doQuery(sqlUpdate)
			Catch ex As Exception
				' Fallback: if RequestPayload column doesn't exist, use ErrorLog
				If ex.Message.Contains("Invalid column name 'RequestPayload'") AndAlso status = "Completed" Then
					sqlUpdate = "UPDATE sysCronJobCustomers " &
										 "SET Status = 'Completed', " &
										 "    ImportJobID = '" & db.CleanString(importJobID) & "', " &
										 "    LastProcessed = GETDATE(), " &
										 "    LastUpdated = GETDATE(), " &
										 "    ErrorLog = 'RequestPayload: ' + '" & db.CleanString(clientJson) & "', " &
										 "    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 ' Re-throw if it's a different error
				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