' =============================================
' Repsly Customer Export Process
' Created: 2025-12-03
' Purpose: Export customers to Repsly API
'          - Query all customers from mstCustomer (active and inactive; Active in payload = blnActive)
'          - Check queue table for new/updated customers (incl. Territory drift vs strLastSentTerritory)
'          - 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
	' Uses mstCustomer only. Active and inactive (Repsly Active follows blnActive).
	' =============================================
	Private Sub InitializeQueue()
		Try
			WriteLog("info", Me.Name, "Initialize Queue", "Checking for new/updated customers...")

			Dim xdb As New db

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

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

			' Mark updated 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 sqlUpdated As String = "UPDATE sysCronJobCustomers " &
													  "SET Status = 'Updated', LastUpdated = GETDATE() " &
													  "FROM sysCronJobCustomers q " &
													  "INNER JOIN mstCustomer c ON q.strCustomerNo = c.strCustomerNo " &
													  "WHERE q.Status = 'Completed' " &
													  "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(sqlUpdated)
			If xdb.intRows > 0 Then
				WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " customer(s) marked as updated")
			End If

			' Re-queue when current Territory (rep sales manager) differs from last value sent to Repsly (requires strLastSentTerritory; see script 16).
			Dim sqlTerritoryResync As String = "UPDATE q " &
														  "SET Status = 'Updated', LastUpdated = GETDATE() " &
														  "FROM sysCronJobCustomers q " &
														  "INNER JOIN mstCustomer c ON q.strCustomerNo = c.strCustomerNo " &
														  "LEFT JOIN mstRep r ON c.refRepID = r.RepID " &
														  "LEFT JOIN mstRepManager rm ON r.refSalesManagerID = rm.RepManagerID " &
														  "WHERE q.Status = 'Completed' " &
														  "AND q.LastProcessed IS NOT NULL " &
														  "AND (q.strLastSentTerritory IS NULL OR q.strLastSentTerritory <> ISNULL(rm.strRepManager, ''))"

			Try
				xdb.doQuery(sqlTerritoryResync)
				If xdb.intRows > 0 Then
					WriteLog("info", Me.Name, "Initialize Queue", xdb.intRows & " customer(s) re-queued (Territory out of sync with last Repsly push)")
				End If
			Catch exTerritory As Exception
				If exTerritory.Message.Contains("strLastSentTerritory") Then
					WriteLog("warning", Me.Name, "Initialize Queue", "strLastSentTerritory missing; run Database_Scripts/16_Add_LastSentTerritory_To_Customer_Queue.sql")
				Else
					Throw
				End If
			End Try

			' 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
	' Single query from mstCustomer. Active and inactive; Active column = blnActive for Repsly.
	' =============================================
	Private Function GetCustomersToProcess() As DataSet
		' Mapping per Repsly clientList API requirements:
		' strCustomerNo -> Code, strCustomerName -> Name, blnActive -> Active
		' refRepID -> RepresentativeCode, strRepName -> RepresentativeName (mstRep)
		' Territory = Rep Manager (mstRepManager via mstRep.refSalesManagerID), State = Region (mstBranch.strRegionDesc2)
		' Country = mstCustomer.strCountry, keyAccountID/strKeyAccount (mstKeyAccount)
		' Tag: mstChannelSegment (strSegmentPrimary, strSegment, strChannelPrimary, strChannel) + mstCustomer.strTags (pipe -> comma)

		Dim sql 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(rm.strRepManager, '') AS Territory, " &
									 "ISNULL(b.strRegionDesc2, '') AS State, " &
									 "ISNULL(c.strCountry, '') AS Country, " &
									 "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, " &
									 "ISNULL(c.strTags, '') AS strTags, " &
									 "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 mstRepManager rm ON r.refSalesManagerID = rm.RepManagerID " &
									 "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') " &
									 "ORDER BY q.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)

			' Add Tag column (segmentation + strTags, comma-separated) for Excel export
			Dim dt As DataTable = ds.Tables(0)
			dt.Columns.Add("Tag", GetType(String))
			For Each row As DataRow In dt.Rows
				row("Tag") = GetTagFromRow(row)
			Next

			Dim excelExport As New ExcelExport(dt, 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 = Rep Manager (single value, no hierarchy)
		If Not String.IsNullOrEmpty(dr("Territory").ToString()) Then
			client("Territory") = dr("Territory").ToString()
		End If

		' State = Region (mstBranch.strRegionDesc2)
		If Not String.IsNullOrEmpty(dr("State").ToString()) Then
			client("State") = dr("State").ToString()
		End If

		' Country = mstCustomer.strCountry
		If Not String.IsNullOrEmpty(dr("Country").ToString()) Then
			client("Country") = dr("Country").ToString()
		End If

		' Tag field - Customer segmentation + mstCustomer.strTags (same value used in Excel export)
		Dim fullTag As String = GetTagFromRow(dr)
		If Not String.IsNullOrEmpty(fullTag) Then
			client("Tag") = fullTag
		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

	' =============================================
	' Get Tag string from DataRow (segmentation + strTags, comma-separated). Used for API and Excel.
	' =============================================
	Private Function GetTagFromRow(dr As DataRow) As String
		Dim tagParts As New List(Of String)
		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())
		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)
		Dim segmentTag As String = If(tagParts.Count > 0, String.Join(", ", tagParts), "")
		Dim strTagsRaw As String = If(IsDBNull(dr("strTags")), "", dr("strTags").ToString().Trim())
		Dim strTagsNormalized As String = If(String.IsNullOrEmpty(strTagsRaw), "", strTagsRaw.Replace("|", ", ").Trim())
		If strTagsNormalized.StartsWith(",") Then strTagsNormalized = strTagsNormalized.TrimStart(","c).Trim()
		If Not String.IsNullOrEmpty(segmentTag) AndAlso Not String.IsNullOrEmpty(strTagsNormalized) Then
			Return segmentTag & ", " & strTagsNormalized
		ElseIf Not String.IsNullOrEmpty(segmentTag) Then
			Return segmentTag
		ElseIf Not String.IsNullOrEmpty(strTagsNormalized) Then
			Return strTagsNormalized
		End If
		Return ""
	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 = ""
			Dim lastSentTerritory As String = ""
			If status = "Completed" Then
				lastSentTerritory = If(IsDBNull(dr("Territory")), "", dr("Territory").ToString())
				sqlUpdate = "UPDATE sysCronJobCustomers " &
									"SET Status = 'Completed', " &
									"    ImportJobID = '" & db.CleanString(importJobID) & "', " &
									"    LastProcessed = GETDATE(), " &
									"    LastUpdated = GETDATE(), " &
									"    RequestPayload = '" & db.CleanString(clientJson) & "', " &
									"    strLastSentTerritory = '" & db.CleanString(lastSentTerritory) & "', " &
									"    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 full Completed update (RequestPayload + strLastSentTerritory); fall back if columns missing
			Try
				db.doQuery(sqlUpdate)
			Catch ex As Exception
				If status <> "Completed" Then
					Throw
				End If
				If ex.Message.Contains("strLastSentTerritory") 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()
					Try
						db.doQuery(sqlUpdate)
						WriteLog("warning", Me.Name, "Push to API", "strLastSentTerritory column not found; run script 16. Please run Database_Scripts/16_Add_LastSentTerritory_To_Customer_Queue.sql")
					Catch ex2 As Exception
						If ex2.Message.Contains("Invalid column name 'RequestPayload'") 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
						End If
					End Try
				ElseIf ex.Message.Contains("Invalid column name 'RequestPayload'") Then
					sqlUpdate = "UPDATE sysCronJobCustomers " &
										"SET Status = 'Completed', " &
										"    ImportJobID = '" & db.CleanString(importJobID) & "', " &
										"    LastProcessed = GETDATE(), " &
										"    LastUpdated = GETDATE(), " &
										"    ErrorLog = 'RequestPayload: ' + '" & db.CleanString(clientJson) & "', " &
										"    strLastSentTerritory = '" & db.CleanString(lastSentTerritory) & "', " &
										"    dtLastEdit = GETDATE() " &
										"WHERE QueueID = " & dr("QueueID").ToString()
					Try
						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")
					Catch ex3 As Exception
						If ex3.Message.Contains("strLastSentTerritory") 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 stored in ErrorLog; strLastSentTerritory missing — run script 16.")
						Else
							Throw
						End If
					End Try
				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