Attribute VB_Name = "TrackingMacro"
'==============================================================================
' WhereParcel - Webhook Tracking Macro
'
' API Endpoints:
'   POST /v2/webhooks/register                  - Register tracking items (max 100/request)
'   GET  /v2/webhooks/subscriptions/:requestId  - Fetch results
'
' Auth: Bearer {apiKey}:{secretKey}
'==============================================================================
Option Explicit

' --- Settings Sheet Layout ---
Private Const CELL_API_KEY As String = "B4"
Private Const CELL_SECRET_KEY As String = "B5"
Private Const CELL_TARGET_SHEET As String = "B8"
Private Const CELL_CARRIER_COL As String = "B9"
Private Const CELL_TRACKING_COL As String = "B10"
Private Const CELL_RESULT_COL As String = "B11"
Private Const CELL_TARGET_MODE As String = "B14"
Private Const CELL_RANGE_MODE As String = "B15"
Private Const CELL_START_ROW As String = "B16"
Private Const CELL_END_ROW As String = "B17"
Private Const HISTORY_START_ROW As Long = 32

' --- Setup Sheet Names ---
Private Const SETUP_SHEET_NAME As String = "Settings"
Private Const SETUP_CARRIER_SHEET_NAME As String = "Carrier Codes"
Private Const SETUP_USAGE_SHEET_NAME As String = "Instructions"

' --- Version ---
Private Const MACRO_VERSION As String = "1.0.0"

' --- API ---
Private Const API_BASE_URL As String = "https://api.whereparcel.com"
Private Const HOMEPAGE_URL As String = "https://www.whereparcel.com"
Private Const BATCH_SIZE As Long = 100

' --- Button Layout ---
Private Const BTN_TOP As Long = 27
Private Const BTN_HEIGHT As Long = 32
Private Const BTN_WIDTH As Long = 120

' --- Result Column Offsets (from result start column) ---
Private Const RES_STATUS As Long = 0           ' Delivery Status
Private Const RES_STATUS_TEXT As Long = 1       ' Status (Text)
Private Const RES_IS_DELIVERED As Long = 2      ' Is Delivered
Private Const RES_RECEIVER As Long = 3          ' Receiver
Private Const RES_PRODUCT As Long = 4           ' Product Name
Private Const RES_DATE_DELIVERED As Long = 5    ' Date Delivered
Private Const RES_DATE_LAST As Long = 6         ' Last Progress
Private Const RES_ERROR As Long = 7             ' Error
Private Const RES_COL_COUNT As Long = 8         ' Total result columns


'==============================================================================
' PUBLIC MACRO: SetupButtons
' Create 3 buttons on the settings sheet (run once)
'==============================================================================
Private Sub SetupButtons(Optional ByVal targetWs As Worksheet = Nothing)
    Dim ws As Worksheet
    Dim shp As Shape
    Dim btn As Shape
    Dim btnLeft As Double
    Dim btnTop As Double
    Dim btns(1 To 3, 1 To 3) As String
    Dim i As Long

    If targetWs Is Nothing Then
        Set ws = ThisWorkbook.Sheets(SETUP_SHEET_NAME)
    Else
        Set ws = targetWs
    End If

    ' Delete existing buttons first
    For Each shp In ws.Shapes
        If shp.Name Like "btn_*" Then shp.Delete
    Next shp

    btnTop = ws.Rows(BTN_TOP).Top + 4

    ' Button definitions: Name, Caption, Macro
    btns(1, 1) = "btn_register": btns(1, 2) = "  Register Tracking  ": btns(1, 3) = "TrackRegister"
    btns(2, 1) = "btn_results":  btns(2, 2) = "  Fetch Results  ":     btns(2, 3) = "TrackResults"
    btns(3, 1) = "btn_clear":    btns(3, 2) = "  Clear Results  ":     btns(3, 3) = "ClearResults"

    btnLeft = ws.Columns("A").Left

    For i = 1 To 3
        Set btn = ws.Shapes.AddFormControl( _
            xlButtonControl, _
            btnLeft, btnTop, BTN_WIDTH, BTN_HEIGHT)
        btn.Name = btns(i, 1)
        btn.TextFrame.Characters.Text = btns(i, 2)
        btn.TextFrame.Characters.Font.Size = 11
        btn.TextFrame.Characters.Font.Name = "Calibri"
        btn.TextFrame.Characters.Font.Bold = True
        btn.OnAction = btns(i, 3)

        btnLeft = btnLeft + BTN_WIDTH + 10
    Next i

    ' --- Utility buttons - far right ---
    Dim utilLeft As Double
    utilLeft = ws.Columns("G").Left

    ' CheckVersion button
    Dim checkBtn As Shape
    Set checkBtn = ws.Shapes.AddFormControl( _
        xlButtonControl, _
        utilLeft, btnTop, 120, BTN_HEIGHT - 4)
    checkBtn.Name = "btn_check"
    checkBtn.TextFrame.Characters.Text = " Check for Updates "
    checkBtn.TextFrame.Characters.Font.Size = 9
    checkBtn.TextFrame.Characters.Font.Name = "Calibri"
    checkBtn.OnAction = "CheckVersion"

    ' SetupAll button
    Dim setupBtn As Shape
    Set setupBtn = ws.Shapes.AddFormControl( _
        xlButtonControl, _
        utilLeft + 128, btnTop, 100, BTN_HEIGHT - 4)
    setupBtn.Name = "btn_setup"
    setupBtn.TextFrame.Characters.Text = " Reset Setup "
    setupBtn.TextFrame.Characters.Font.Size = 9
    setupBtn.TextFrame.Characters.Font.Name = "Calibri"
    setupBtn.OnAction = "SetupAll"

    MsgBox "Buttons have been created!", vbInformation, "Done"
End Sub


'==============================================================================
' SETTING HELPERS
'==============================================================================
Private Function GetSetting(cellAddr As String) As String
    GetSetting = Trim(CStr(ThisWorkbook.Sheets(SETUP_SHEET_NAME).Range(cellAddr).Value))
End Function

Private Function ColLetterToNum(letter As String) As Long
    Dim s As String
    Dim i As Long
    Dim result As Long
    Dim ch As Long

    s = UCase(Trim(letter))

    If Len(s) = 0 Then
        ColLetterToNum = 0
        Exit Function
    End If

    result = 0
    For i = 1 To Len(s)
        ch = Asc(Mid(s, i, 1))
        If ch < 65 Or ch > 90 Then
            ColLetterToNum = 0
            Exit Function
        End If
        result = result * 26 + (ch - 64)
    Next i

    ColLetterToNum = result
End Function


'==============================================================================
' PUBLIC MACRO: TrackRegister
' Register tracking items via POST /v2/webhooks/register
'==============================================================================
Public Sub TrackRegister()
    Dim apiKey As String, secretKey As String
    Dim targetSheetName As String
    Dim courierCol As Long, trackingCol As Long, resultCol As Long
    Dim targetMode As String, rangeMode As String
    Dim ws As Worksheet
    Dim startRow As Long, endRow As Long
    Dim selectedRange As Range

    On Error GoTo ErrorHandler

    ' Read Settings
    apiKey = GetSetting(CELL_API_KEY)
    secretKey = GetSetting(CELL_SECRET_KEY)
    targetSheetName = GetSetting(CELL_TARGET_SHEET)

    If apiKey = "" Or secretKey = "" Then
        MsgBox "Please enter your API Key and Secret Key in the Settings sheet.", vbExclamation, "Missing Settings"
        Exit Sub
    End If
    If targetSheetName = "" Then
        MsgBox "Please specify the target sheet name.", vbExclamation, "Missing Settings"
        Exit Sub
    End If

    ' Validate target sheet exists
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(targetSheetName)
    On Error GoTo ErrorHandler
    If ws Is Nothing Then
        MsgBox "Sheet '" & targetSheetName & "' was not found.", vbExclamation, "Missing Sheet"
        Exit Sub
    End If

    courierCol = ColLetterToNum(GetSetting(CELL_CARRIER_COL))
    trackingCol = ColLetterToNum(GetSetting(CELL_TRACKING_COL))
    resultCol = ColLetterToNum(GetSetting(CELL_RESULT_COL))

    If courierCol = 0 Or trackingCol = 0 Or resultCol = 0 Then
        MsgBox "Column settings are invalid. Please enter letters (A~Z).", vbExclamation, "Invalid Settings"
        Exit Sub
    End If

    targetMode = GetSetting(CELL_TARGET_MODE)
    rangeMode = GetSetting(CELL_RANGE_MODE)

    ' Determine Range
    Select Case rangeMode
        Case "Mouse Selection"
            ws.Activate
            On Error Resume Next
            Set selectedRange = Application.InputBox( _
                "Select the range to track with your mouse." & vbCrLf & _
                "(Press Cancel to abort)", _
                "Select Range", Type:=8)
            On Error GoTo ErrorHandler
            If selectedRange Is Nothing Then Exit Sub
            startRow = selectedRange.Row
            endRow = selectedRange.Row + selectedRange.Rows.Count - 1

        Case "All"
            startRow = 1
            endRow = ws.Cells(ws.Rows.Count, courierCol).End(xlUp).Row

        Case "Specific Range"
            Dim sRow As String, eRow As String
            sRow = GetSetting(CELL_START_ROW)
            eRow = GetSetting(CELL_END_ROW)
            If sRow <> "" Then
                startRow = CLng(sRow)
            Else
                startRow = 1
            End If
            If eRow <> "" Then
                endRow = CLng(eRow)
            Else
                endRow = ws.Cells(ws.Rows.Count, courierCol).End(xlUp).Row
            End If

        Case Else
            MsgBox "Range mode is invalid.", vbExclamation, "Invalid Settings"
            Exit Sub
    End Select

    If endRow < startRow Then
        MsgBox "No data found in the selected range.", vbInformation, "Info"
        Exit Sub
    End If

    ' Collect Items
    Dim items() As String
    Dim itemRows() As Long
    Dim itemCount As Long
    Dim totalSkipped As Long
    ReDim items(1 To endRow - startRow + 1)
    ReDim itemRows(1 To endRow - startRow + 1)
    itemCount = 0
    totalSkipped = 0

    Dim r As Long
    Dim carrier As String, tracking As String
    For r = startRow To endRow
        carrier = NormalizeCarrierCode(Trim(CStr(ws.Cells(r, courierCol).Value)))
        ' Convert to text safely (handles scientific notation like 8.28E+19)
        If IsNumeric(ws.Cells(r, trackingCol).Value) And Not IsEmpty(ws.Cells(r, trackingCol).Value) Then
            tracking = Replace(Replace(Trim(Format(ws.Cells(r, trackingCol).Value, "0")), "-", ""), " ", "")
        Else
            tracking = Replace(Replace(Trim(CStr(ws.Cells(r, trackingCol).Value)), "-", ""), " ", "")
        End If

        If carrier <> "" And tracking <> "" Then
            ' Check target mode: skip rows that already have results
            If targetMode = "Untracked Only" Then
                If Trim(CStr(ws.Cells(r, resultCol).Value)) <> "" Then
                    totalSkipped = totalSkipped + 1
                    GoTo NextRow
                End If
            End If

            itemCount = itemCount + 1
            items(itemCount) = BuildItemJson(carrier, tracking, CStr(r))
            itemRows(itemCount) = r
        End If
NextRow:
    Next r

    If itemCount = 0 Then
        Dim msg As String
        msg = "No data to track."
        If totalSkipped > 0 Then msg = msg & vbCrLf & "(" & totalSkipped & " rows skipped - already have results)"
        MsgBox msg, vbInformation, "Info"
        Exit Sub
    End If

    ' Confirm
    Dim confirmMsg As String
    confirmMsg = itemCount & " items will be registered."
    If totalSkipped > 0 Then confirmMsg = confirmMsg & vbCrLf & "(" & totalSkipped & " rows with results skipped)"
    confirmMsg = confirmMsg & vbCrLf & vbCrLf & "This may incur usage charges. Continue?"
    If MsgBox(confirmMsg, vbYesNo + vbQuestion, "Register Tracking") = vbNo Then Exit Sub

    Application.ScreenUpdating = False
    Application.StatusBar = "Registering tracking items..."

    ' Register in Batches
    Dim batchStart As Long, batchEnd As Long
    Dim batchNum As Long, totalBatches As Long
    Dim registeredCount As Long
    Dim batchCount As Long
    Dim jsonBody As String
    Dim response As String
    Dim requestId As String
    Dim errMsg As String

    totalBatches = Int((itemCount - 1) / BATCH_SIZE) + 1
    registeredCount = 0

    For batchNum = 1 To totalBatches
        batchStart = (batchNum - 1) * BATCH_SIZE + 1
        batchEnd = batchStart + BATCH_SIZE - 1
        If batchEnd > itemCount Then batchEnd = itemCount

        batchCount = batchEnd - batchStart + 1

        ' Build JSON body
        jsonBody = "{""trackingItems"":["
        Dim b As Long
        For b = batchStart To batchEnd
            If b > batchStart Then jsonBody = jsonBody & ","
            jsonBody = jsonBody & items(b)
        Next b
        jsonBody = jsonBody & "],""recurring"":false}"

        ' Send API request
        Application.StatusBar = "Registering... batch " & batchNum & "/" & totalBatches
        response = HttpPost(API_BASE_URL & "/v2/webhooks/register", apiKey, secretKey, jsonBody)

        ' Parse response
        requestId = ExtractJsonString(response, "requestId")

        If requestId <> "" Then
            ' Save to history
            SaveHistory requestId, batchCount, "Registered"
            registeredCount = registeredCount + batchCount
        Else
            ' Error
            errMsg = ExtractJsonString(response, "message")
            If errMsg = "" Then errMsg = ExtractJsonString(response, "error")
            If errMsg = "" Then errMsg = "Unknown error"
            SaveHistory "ERROR", batchCount, errMsg
        End If

        ' Delay between batches
        If batchNum < totalBatches Then
            Application.Wait Now + TimeSerial(0, 0, 1)
        End If
    Next batchNum

    Application.StatusBar = False
    Application.ScreenUpdating = True

    MsgBox registeredCount & " items registered!" & vbCrLf & _
           totalBatches & " batch(es), requestId(s) saved in the Settings sheet." & vbCrLf & vbCrLf & _
           "Please wait a moment, then click [Fetch Results].", vbInformation, "Registration Complete"
    Exit Sub

ErrorHandler:
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "Error: " & Err.Description, vbCritical, "Error"
End Sub


'==============================================================================
' PUBLIC MACRO: TrackResults
' Fetch results via GET /v2/webhooks/subscriptions/:requestId
'==============================================================================
Public Sub TrackResults()
    Dim apiKey As String, secretKey As String
    Dim targetSheetName As String
    Dim resultCol As Long
    Dim ws As Worksheet, wsSetting As Worksheet

    On Error GoTo ErrorHandler

    apiKey = GetSetting(CELL_API_KEY)
    secretKey = GetSetting(CELL_SECRET_KEY)
    targetSheetName = GetSetting(CELL_TARGET_SHEET)

    If apiKey = "" Or secretKey = "" Then
        MsgBox "Please enter your API Key and Secret Key.", vbExclamation, "Missing Settings"
        Exit Sub
    End If

    Set ws = Nothing
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(targetSheetName)
    On Error GoTo ErrorHandler
    If ws Is Nothing Then
        MsgBox "Sheet '" & targetSheetName & "' was not found.", vbExclamation, "Missing Sheet"
        Exit Sub
    End If

    resultCol = ColLetterToNum(GetSetting(CELL_RESULT_COL))

    If resultCol = 0 Then
        MsgBox "Result start column is invalid. Please enter a letter (A~Z).", vbExclamation, "Invalid Settings"
        Exit Sub
    End If

    Set wsSetting = ThisWorkbook.Sheets(SETUP_SHEET_NAME)

    ' Find requestIds with status "Registered"
    Dim lastHistRow As Long
    lastHistRow = wsSetting.Cells(wsSetting.Rows.Count, 1).End(xlUp).Row

    If lastHistRow < HISTORY_START_ROW Then
        MsgBox "No registration history found." & vbCrLf & "Please run [Register Tracking] first.", vbInformation, "Info"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.StatusBar = "Fetching results..."

    Dim totalUpdated As Long, totalPending As Long, totalErrors As Long
    totalUpdated = 0
    totalPending = 0
    totalErrors = 0

    Dim histRow As Long
    Dim processedBatches As Long
    Dim reqId As String, status As String
    Dim response As String
    Dim apiErr As String
    Dim itemsArray As Collection
    Dim allDone As Boolean
    Dim itemJson As Variant
    Dim clientId As String
    Dim targetRow As Long
    Dim currentStatus As String
    Dim itemError As String
    Dim tdBlock As String
    Dim deliveryStatus As String

    processedBatches = 0

    For histRow = HISTORY_START_ROW To lastHistRow
        reqId = Trim(CStr(wsSetting.Cells(histRow, 1).Value))
        status = Trim(CStr(wsSetting.Cells(histRow, 4).Value))

        ' Skip non-pending entries
        If reqId = "" Or reqId = "ERROR" Or status = "Done" Then GoTo NextHist

        processedBatches = processedBatches + 1
        Application.StatusBar = "Fetching results... " & reqId

        ' GET subscription detail
        response = HttpGet(API_BASE_URL & "/v2/webhooks/subscriptions/" & reqId, apiKey, secretKey)

        ' Check for error
        If InStr(response, """success"":false") > 0 Or InStr(response, """isSuccess"":false") > 0 Then
            apiErr = ExtractJsonString(response, "message")
            If apiErr = "" Then apiErr = ExtractJsonString(response, "error")
            wsSetting.Cells(histRow, 4).Value = "Error: " & apiErr
            totalErrors = totalErrors + 1
            GoTo NextHist
        End If

        ' Parse trackingItems array
        Set itemsArray = GetJsonArrayItems(response, "trackingItems")

        If itemsArray Is Nothing Or itemsArray.Count = 0 Then
            ' Fallback: try "items" key
            Set itemsArray = GetJsonArrayItems(response, "items")
        End If

        If itemsArray Is Nothing Or itemsArray.Count = 0 Then
            wsSetting.Cells(histRow, 4).Value = "Parse error"
            GoTo NextHist
        End If

        allDone = True

        For Each itemJson In itemsArray
            clientId = ExtractJsonString(CStr(itemJson), "clientId")

            If clientId <> "" And IsNumeric(clientId) Then
                targetRow = CLng(clientId)

                ' Extract fields
                currentStatus = ExtractJsonString(CStr(itemJson), "status")
                itemError = ExtractJsonString(CStr(itemJson), "error")

                ' If error is a nested object, try to extract message from it
                If itemError = "" Then
                    Dim errObj As String
                    errObj = ExtractNestedObject(CStr(itemJson), "error")
                    If errObj <> "" And errObj <> "null" Then
                        itemError = ExtractJsonString(errObj, "message")
                    End If
                End If

                ' Extract from nested trackingData
                tdBlock = ExtractNestedObject(CStr(itemJson), "trackingData")

                If itemError <> "" Then
                    ' Item has error
                    ws.Cells(targetRow, resultCol + RES_STATUS).Value = currentStatus
                    ws.Cells(targetRow, resultCol + RES_ERROR).Value = itemError
                    ws.Cells(targetRow, resultCol + RES_ERROR).Font.Color = RGB(200, 0, 0)
                    totalErrors = totalErrors + 1
                ElseIf tdBlock <> "" And tdBlock <> "null" Then
                    ' Has tracking data - write results
                    deliveryStatus = ExtractJsonString(tdBlock, "deliveryStatus")
                    If deliveryStatus = "" Then deliveryStatus = currentStatus

                    ws.Cells(targetRow, resultCol + RES_STATUS).Value = deliveryStatus
                    ws.Cells(targetRow, resultCol + RES_STATUS_TEXT).Value = StatusToText(deliveryStatus)

                    If deliveryStatus = "delivered" Then
                        ws.Cells(targetRow, resultCol + RES_IS_DELIVERED).Value = "Y"
                    Else
                        ws.Cells(targetRow, resultCol + RES_IS_DELIVERED).Value = "N"
                    End If

                    ' Receiver (from to.name)
                    Dim toBlock As String
                    toBlock = ExtractNestedObject(tdBlock, "to")
                    If toBlock <> "" And toBlock <> "null" Then
                        ws.Cells(targetRow, resultCol + RES_RECEIVER).Value = ExtractJsonString(toBlock, "name")
                    End If

                    ' Product name (not available in WhereParcel, leave empty)
                    ' ws.Cells(targetRow, resultCol + RES_PRODUCT).Value = ""

                    ' Date delivered - extract from last event if delivered
                    If deliveryStatus = "delivered" Then
                        Dim eventsArray As Collection
                        Set eventsArray = GetJsonArrayItems(tdBlock, "events")
                        If Not eventsArray Is Nothing And eventsArray.Count > 0 Then
                            ws.Cells(targetRow, resultCol + RES_DATE_DELIVERED).Value = _
                                ExtractJsonString(CStr(eventsArray(1)), "timestamp")
                        End If
                    End If

                    ' Last progress
                    ws.Cells(targetRow, resultCol + RES_DATE_LAST).Value = ExtractJsonString(tdBlock, "lastUpdated")

                    ' Color coding
                    Select Case deliveryStatus
                        Case "delivered"
                            ws.Cells(targetRow, resultCol + RES_STATUS).Font.Color = RGB(0, 128, 0)
                            ws.Cells(targetRow, resultCol + RES_STATUS_TEXT).Font.Color = RGB(0, 128, 0)
                        Case "in_transit", "out_for_delivery"
                            ws.Cells(targetRow, resultCol + RES_STATUS).Font.Color = RGB(0, 100, 200)
                            ws.Cells(targetRow, resultCol + RES_STATUS_TEXT).Font.Color = RGB(0, 100, 200)
                        Case "pending"
                            ws.Cells(targetRow, resultCol + RES_STATUS).Font.Color = RGB(180, 120, 0)
                            ws.Cells(targetRow, resultCol + RES_STATUS_TEXT).Font.Color = RGB(180, 120, 0)
                        Case "failed", "returned", "cancelled"
                            ws.Cells(targetRow, resultCol + RES_STATUS).Font.Color = RGB(200, 0, 0)
                            ws.Cells(targetRow, resultCol + RES_STATUS_TEXT).Font.Color = RGB(200, 0, 0)
                    End Select

                    totalUpdated = totalUpdated + 1
                Else
                    ' No tracking data yet (pending)
                    If currentStatus <> "" Then
                        ws.Cells(targetRow, resultCol + RES_STATUS).Value = currentStatus
                    End If
                    allDone = False
                    totalPending = totalPending + 1
                End If
            End If
        Next itemJson

        ' Update history status
        If allDone Then
            wsSetting.Cells(histRow, 4).Value = "Done"
        Else
            wsSetting.Cells(histRow, 4).Value = "Partial"
        End If

NextHist:
    Next histRow

    Application.StatusBar = False
    Application.ScreenUpdating = True

    If processedBatches = 0 Then
        MsgBox "No pending registrations to process.", vbInformation, "Info"
    Else
        Dim resultMsg As String
        resultMsg = "Results fetched!" & vbCrLf & vbCrLf
        resultMsg = resultMsg & "  Updated: " & totalUpdated & " items" & vbCrLf
        If totalPending > 0 Then
            resultMsg = resultMsg & "  Pending: " & totalPending & " items (try again shortly)" & vbCrLf
        End If
        If totalErrors > 0 Then
            resultMsg = resultMsg & "  Errors: " & totalErrors & " items" & vbCrLf
        End If
        MsgBox resultMsg, vbInformation, "Fetch Results"
    End If
    Exit Sub

ErrorHandler:
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "Error: " & Err.Description, vbCritical, "Error"
End Sub


'==============================================================================
' PUBLIC MACRO: ClearResults
' Clear result columns in the target sheet
'==============================================================================
Public Sub ClearResults()
    Dim targetSheetName As String
    Dim resultCol As Long, courierCol As Long
    Dim ws As Worksheet

    On Error GoTo ErrorHandler

    targetSheetName = GetSetting(CELL_TARGET_SHEET)

    Set ws = Nothing
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(targetSheetName)
    On Error GoTo ErrorHandler
    If ws Is Nothing Then
        MsgBox "Sheet '" & targetSheetName & "' was not found.", vbExclamation, "Missing Sheet"
        Exit Sub
    End If

    resultCol = ColLetterToNum(GetSetting(CELL_RESULT_COL))
    courierCol = ColLetterToNum(GetSetting(CELL_COURIER_COL))

    If resultCol = 0 Or courierCol = 0 Then
        MsgBox "Column settings are invalid. Please enter letters (A~Z).", vbExclamation, "Invalid Settings"
        Exit Sub
    End If

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, courierCol).End(xlUp).Row

    If lastRow < 1 Then
        MsgBox "No data found.", vbInformation, "Info"
        Exit Sub
    End If

    If MsgBox("This will clear all result columns (D~K)." & vbCrLf & "Continue?", _
              vbYesNo + vbQuestion, "Clear Results") = vbNo Then Exit Sub

    ws.Range( _
        ws.Cells(1, resultCol), _
        ws.Cells(lastRow, resultCol + RES_COL_COUNT - 1) _
    ).ClearContents

    MsgBox "Results have been cleared.", vbInformation, "Done"
    Exit Sub

ErrorHandler:
    MsgBox "Error: " & Err.Description, vbCritical, "Error"
End Sub


'==============================================================================
' PRIVATE: Save requestId to history
'==============================================================================
Private Sub SaveHistory(requestId As String, itemCount As Long, status As String)
    Dim wsSetting As Worksheet
    Set wsSetting = ThisWorkbook.Sheets(SETUP_SHEET_NAME)

    Dim nextRow As Long
    nextRow = wsSetting.Cells(wsSetting.Rows.Count, 1).End(xlUp).Row + 1
    If nextRow < HISTORY_START_ROW Then nextRow = HISTORY_START_ROW

    wsSetting.Cells(nextRow, 1).Value = requestId
    wsSetting.Cells(nextRow, 2).Value = itemCount
    wsSetting.Cells(nextRow, 3).Value = Format(Now, "yyyy-mm-dd hh:mm:ss")
    wsSetting.Cells(nextRow, 4).Value = status
End Sub


'==============================================================================
' PRIVATE: Convert delivery status to human-readable text
'==============================================================================
Private Function StatusToText(status As String) As String
    Select Case LCase(status)
        Case "pending":           StatusToText = "Pending"
        Case "in_transit":        StatusToText = "In Transit"
        Case "out_for_delivery":  StatusToText = "Out for Delivery"
        Case "delivered":         StatusToText = "Delivered"
        Case "failed":            StatusToText = "Failed"
        Case "returned":          StatusToText = "Returned"
        Case "cancelled":         StatusToText = "Cancelled"
        Case "unknown":           StatusToText = "Unknown"
        Case Else:                StatusToText = status
    End Select
End Function


'==============================================================================
' PRIVATE: HTTP POST request
'==============================================================================
Private Function HttpPost(url As String, apiKey As String, secretKey As String, jsonBody As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    http.Open "POST", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & apiKey & ":" & secretKey
    http.Send jsonBody

    HttpPost = http.responseText
    Set http = Nothing
End Function


'==============================================================================
' PRIVATE: HTTP GET request
'==============================================================================
Private Function HttpGet(url As String, apiKey As String, secretKey As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    http.Open "GET", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & apiKey & ":" & secretKey
    http.Send

    HttpGet = http.responseText
    Set http = Nothing
End Function


'==============================================================================
' PRIVATE: Build JSON for a single tracking item
'==============================================================================
Private Function BuildItemJson(carrier As String, trackingNumber As String, clientId As String) As String
    BuildItemJson = "{""carrier"":""" & EscapeJson(carrier) & _
                    """,""trackingNumber"":""" & EscapeJson(trackingNumber) & _
                    """,""clientId"":""" & EscapeJson(clientId) & """}"
End Function


'==============================================================================
' JSON PARSING HELPERS (lightweight, no external dependencies)
'==============================================================================

' Extract a string/number/boolean value from JSON by key
Private Function ExtractJsonString(json As String, key As String) As String
    Dim searchStr As String
    Dim pos As Long, startPos As Long, endPos As Long

    searchStr = """" & key & """:"
    pos = InStr(json, searchStr)

    If pos = 0 Then
        ExtractJsonString = ""
        Exit Function
    End If

    startPos = pos + Len(searchStr)

    ' Skip whitespace
    Do While startPos <= Len(json) And Mid(json, startPos, 1) = " "
        startPos = startPos + 1
    Loop

    Dim ch As String
    ch = Mid(json, startPos, 1)

    If ch = """" Then
        ' String value
        startPos = startPos + 1
        endPos = startPos
        Do While endPos <= Len(json)
            If Mid(json, endPos, 1) = """" And Mid(json, endPos - 1, 1) <> "\" Then Exit Do
            endPos = endPos + 1
        Loop
        ExtractJsonString = Mid(json, startPos, endPos - startPos)
    ElseIf ch = "n" Then
        ' null
        ExtractJsonString = ""
    ElseIf ch = "{" Or ch = "[" Then
        ' Object or array - return empty (use ExtractNestedObject for objects)
        ExtractJsonString = ""
    Else
        ' Number or boolean
        endPos = startPos
        Do While endPos <= Len(json)
            Dim c As String
            c = Mid(json, endPos, 1)
            If c = "," Or c = "}" Or c = "]" Or c = " " Then Exit Do
            endPos = endPos + 1
        Loop
        ExtractJsonString = Mid(json, startPos, endPos - startPos)
    End If
End Function


' Extract a nested JSON object as a string
Private Function ExtractNestedObject(json As String, key As String) As String
    Dim searchStr As String
    Dim pos As Long, startPos As Long

    searchStr = """" & key & """:"
    pos = InStr(json, searchStr)

    If pos = 0 Then
        ExtractNestedObject = ""
        Exit Function
    End If

    startPos = pos + Len(searchStr)

    ' Skip whitespace
    Do While startPos <= Len(json) And Mid(json, startPos, 1) = " "
        startPos = startPos + 1
    Loop

    If Mid(json, startPos, 1) = "n" Then
        ExtractNestedObject = "null"
        Exit Function
    End If

    If Mid(json, startPos, 1) <> "{" Then
        ExtractNestedObject = ""
        Exit Function
    End If

    Dim endPos As Long
    endPos = FindMatchingBrace(json, startPos)

    If endPos > 0 Then
        ExtractNestedObject = Mid(json, startPos, endPos - startPos + 1)
    Else
        ExtractNestedObject = ""
    End If
End Function


' Get JSON array items as a Collection of strings
Private Function GetJsonArrayItems(json As String, arrayKey As String) As Collection
    Dim result As New Collection
    Dim searchStr As String
    Dim pos As Long, arrStart As Long

    searchStr = """" & arrayKey & """:"
    pos = InStr(json, searchStr)

    If pos = 0 Then
        Set GetJsonArrayItems = result
        Exit Function
    End If

    arrStart = pos + Len(searchStr)

    ' Skip whitespace
    Do While arrStart <= Len(json) And Mid(json, arrStart, 1) = " "
        arrStart = arrStart + 1
    Loop

    If Mid(json, arrStart, 1) <> "[" Then
        Set GetJsonArrayItems = result
        Exit Function
    End If

    ' Find each top-level object in the array
    Dim i As Long
    Dim depth As Long
    Dim inString As Boolean
    Dim objStart As Long
    Dim ch As String

    i = arrStart + 1
    objStart = 0

    Do While i <= Len(json)
        ch = Mid(json, i, 1)

        ' Handle string literals
        If ch = """" And (i = 1 Or Mid(json, i - 1, 1) <> "\") Then
            inString = Not inString
        End If

        If Not inString Then
            If ch = "{" Then
                If depth = 0 Then objStart = i
                depth = depth + 1
            ElseIf ch = "}" Then
                depth = depth - 1
                If depth = 0 And objStart > 0 Then
                    result.Add Mid(json, objStart, i - objStart + 1)
                    objStart = 0
                End If
            ElseIf ch = "]" And depth = 0 Then
                Exit Do
            End If
        End If

        i = i + 1
    Loop

    Set GetJsonArrayItems = result
End Function


' Find matching closing brace
Private Function FindMatchingBrace(json As String, startPos As Long) As Long
    Dim i As Long
    Dim depth As Long
    Dim inString As Boolean
    Dim ch As String

    depth = 0
    For i = startPos To Len(json)
        ch = Mid(json, i, 1)

        If ch = """" And (i = 1 Or Mid(json, i - 1, 1) <> "\") Then
            inString = Not inString
        End If

        If Not inString Then
            If ch = "{" Then
                depth = depth + 1
            ElseIf ch = "}" Then
                depth = depth - 1
                If depth = 0 Then
                    FindMatchingBrace = i
                    Exit Function
                End If
            End If
        End If
    Next i

    FindMatchingBrace = 0
End Function


'==============================================================================
' PUBLIC MACRO: SetupAll
' Create Settings + Carrier Codes + Instructions sheets + buttons
'==============================================================================
Public Sub SetupAll()
    Dim wsS As Worksheet
    Dim wsC As Worksheet
    Dim oldSheets() As String
    Dim oldCount As Long
    Dim si As Long

    On Error GoTo SetupError
    Application.ScreenUpdating = False

    ' --- Save old sheet names ---
    oldCount = 0
    Dim sheetNames As Variant
    sheetNames = Array(SETUP_SHEET_NAME, SETUP_CARRIER_SHEET_NAME, SETUP_USAGE_SHEET_NAME)
    ReDim oldSheets(1 To 3)
    On Error Resume Next
    For si = 0 To 2
        Dim tmpWs As Worksheet
        Set tmpWs = Nothing
        Set tmpWs = ThisWorkbook.Sheets(sheetNames(si))
        If Not tmpWs Is Nothing Then
            oldCount = oldCount + 1
            tmpWs.Name = "_old_" & oldCount
            oldSheets(oldCount) = "_old_" & oldCount
        End If
    Next si
    On Error GoTo SetupError

    ' ========================================
    ' Settings Sheet
    ' ========================================
    Set wsS = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
    wsS.Name = SETUP_SHEET_NAME

    wsS.Columns("A").ColumnWidth = 18
    wsS.Columns("B").ColumnWidth = 48
    wsS.Columns("C").ColumnWidth = 14
    wsS.Columns("D").ColumnWidth = 14
    wsS.Columns("E").ColumnWidth = 36

    ' Title
    wsS.Range("A1:D1").Merge
    SetStyleTitle wsS, "A1", "WhereParcel Tracking Macro Settings"
    wsS.Rows(1).RowHeight = 32

    ' --- API Settings ---
    SetStyleSection wsS, "A3", "[ API Settings ]"
    SetStyleLabel wsS, "A4", "API Key"
    wsS.Range("B4:C4").Merge
    SetStyleInput wsS, "B4", "wp_test_public_demo_key_do_not_use_in_production"
    SetStyleHint wsS, "E4", "Demo key - get yours at whereparcel.com"
    SetStyleLabel wsS, "A5", "Secret Key"
    wsS.Range("B5:C5").Merge
    SetStyleInput wsS, "B5", "sk_test_public_demo_secret_do_not_use_in_production"
    SetStyleHint wsS, "E5", "Demo key - get yours at whereparcel.com"

    ' --- Sheet Settings ---
    SetStyleSection wsS, "A7", "[ Sheet Settings ]"
    SetStyleLabel wsS, "A8", "Target Sheet"
    SetStyleInput wsS, "B8", "Tracking"
    SetStyleHint wsS, "E8", "Sheet name containing your data"
    SetStyleLabel wsS, "A9", "Carrier Col"
    SetStyleInput wsS, "B9", "B"
    SetStyleHint wsS, "E9", "Column letter (A, B, C...)"
    SetStyleLabel wsS, "A10", "Tracking # Col"
    SetStyleInput wsS, "B10", "C"
    SetStyleHint wsS, "E10", "Column letter (A, B, C...)"
    SetStyleLabel wsS, "A11", "Result Start Col"
    SetStyleInput wsS, "B11", "D"
    SetStyleHint wsS, "E11", "Results fill from this column onward"

    ' --- Execution Options ---
    SetStyleSection wsS, "A13", "[ Execution Options ]"
    SetStyleLabel wsS, "A14", "Target"
    SetStyleInput wsS, "B14", "Track All"
    SetStyleLabel wsS, "A15", "Range"
    SetStyleInput wsS, "B15", "Mouse Selection"
    SetStyleLabel wsS, "A16", "Start Row"
    SetStyleInput wsS, "B16", ""
    SetStyleHint wsS, "E16", "For Specific Range only (blank = first row)"
    SetStyleLabel wsS, "A17", "End Row"
    SetStyleInput wsS, "B17", ""
    SetStyleHint wsS, "E17", "For Specific Range only (blank = last row)"

    ' Dropdowns
    wsS.Range("B14").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="Track All,Untracked Only"
    wsS.Range("B15").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="Mouse Selection,All,Specific Range"

    ' --- Run Macros ---
    SetStyleSection wsS, "A19", "[ Run Macros ]"
    SetStyleHint wsS, "A20", "Press Alt+F8 or use the buttons below"
    SetStyleNormal wsS, "A21", "  TrackRegister    = Register tracking items with the API"
    SetStyleNormal wsS, "A22", "  TrackResults     = Fetch results for registered items"
    SetStyleNormal wsS, "A23", "  ClearResults     = Clear all result columns"
    SetStyleNormal wsS, "A24", "  CheckVersion     = Check for macro updates"
    SetStyleNormal wsS, "A25", "  SetupAll         = Reset all sheets and buttons"

    ' --- Version & Update Info ---
    SetStyleSection wsS, "G19", "[ Macro Updates ]"
    SetStyleHint wsS, "G20", "Click [Check for Updates] to see if a new version is available."
    SetStyleHint wsS, "G21", "If an update exists, you can auto-update or download manually."
    SetStyleHint wsS, "G22", "After updating, click [Reset Setup] to refresh all sheets."
    SetStyleHint wsS, "G23", "Current version: v" & MACRO_VERSION

    ' --- Registration History ---
    SetStyleSection wsS, "A30", "[ Registration History ]"
    Dim hdrTexts As Variant
    hdrTexts = Array("requestId", "Count", "Registered At", "Status")
    Dim hc As Long
    For hc = 0 To 3
        With wsS.Cells(31, hc + 1)
            .Value = hdrTexts(hc)
            .Font.Name = "Calibri"
            .Font.Bold = True
            .Font.Color = RGB(255, 255, 255)
            .Font.Size = 11
            .Interior.Color = RGB(45, 55, 72)
            .HorizontalAlignment = xlCenter
            .Borders.LineStyle = xlContinuous
            .Borders.Color = RGB(203, 213, 224)
        End With
    Next hc

    ' ========================================
    ' Tracking Sheet (create if not exists)
    ' ========================================
    Dim wsT As Worksheet
    Dim trackingSheetName As String
    trackingSheetName = GetSetting(CELL_TARGET_SHEET)
    If trackingSheetName = "" Then trackingSheetName = "Tracking"

    Set wsT = Nothing
    On Error Resume Next
    Set wsT = ThisWorkbook.Sheets(trackingSheetName)
    On Error GoTo SetupError

    If wsT Is Nothing Then
        ' Create new Tracking sheet only if it doesn't exist (never touch existing user data)
        Set wsT = ThisWorkbook.Sheets.Add(After:=wsS)
        wsT.Name = trackingSheetName

        ' Headers
        Dim tHeaders As Variant
        tHeaders = Array("No", "Carrier Code", "Tracking Number", _
                         "Status", "Status Text", "Is Delivered", _
                         "Receiver", "Product", "Date Delivered", "Last Progress", "Error")
        Dim tWidths As Variant
        tWidths = Array(6, 14, 22, 14, 16, 12, 14, 14, 20, 20, 28)
        Dim ti As Long
        For ti = 0 To UBound(tHeaders)
            With wsT.Cells(1, ti + 1)
                .Value = tHeaders(ti)
                .Font.Name = "Calibri"
                .Font.Bold = True
                .Font.Color = RGB(255, 255, 255)
                .Font.Size = 11
                .Interior.Color = RGB(45, 55, 72)
                .HorizontalAlignment = xlCenter
                .VerticalAlignment = xlCenter
                .Borders.LineStyle = xlContinuous
                .Borders.Color = RGB(203, 213, 224)
            End With
            wsT.Columns(ti + 1).ColumnWidth = tWidths(ti)
        Next ti
        wsT.Rows(1).RowHeight = 28

        ' Set Tracking Number column (C) to Text format
        wsT.Columns("C").NumberFormat = "@"

        ' Sample data
        Dim samples As Variant
        samples = Array( _
            Array(1, "us.ups", ""), _
            Array(2, "us.usps", ""), _
            Array(3, "us.fedex", ""), _
            Array(4, "kr.cj", ""), _
            Array(5, "jp.yamato", ""), _
            Array(6, "de.dhl", ""), _
            Array(7, "gb.royalmail", ""), _
            Array(8, "intl.dhl", ""))
        Dim si2 As Long
        For si2 = 0 To UBound(samples)
            wsT.Cells(si2 + 2, 1).Value = samples(si2)(0)
            wsT.Cells(si2 + 2, 1).HorizontalAlignment = xlCenter
            wsT.Cells(si2 + 2, 2).Value = samples(si2)(1)
            wsT.Cells(si2 + 2, 2).HorizontalAlignment = xlCenter
            wsT.Cells(si2 + 2, 3).Value = samples(si2)(2)
            Dim sc As Long
            For sc = 1 To UBound(tHeaders) + 1
                With wsT.Cells(si2 + 2, sc)
                    .Font.Name = "Calibri"
                    .Font.Size = 10
                    .Borders.LineStyle = xlContinuous
                    .Borders.Color = RGB(203, 213, 224)
                End With
            Next sc
        Next si2

        ' Freeze header row
        wsT.Range("A2").Select
        ActiveWindow.FreezePanes = True
    End If

    ' ========================================
    ' Carrier Codes Sheet
    ' ========================================
    Set wsC = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsC.Name = SETUP_CARRIER_SHEET_NAME

    wsC.Columns("A").ColumnWidth = 22
    wsC.Columns("B").ColumnWidth = 36
    wsC.Columns("C").ColumnWidth = 20

    With wsC.Range("A1")
        .Value = "Carrier Code"
        .Font.Name = "Calibri"
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(45, 55, 72)
    End With
    With wsC.Range("B1")
        .Value = "Carrier Name"
        .Font.Name = "Calibri"
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(45, 55, 72)
    End With
    With wsC.Range("C1")
        .Value = "Country"
        .Font.Name = "Calibri"
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(45, 55, 72)
    End With

    Dim cr As Long
    cr = 2

    ' --- AU ---
    WriteCarrierSection wsC, cr, "Australia (AU)": cr = cr + 1
    WriteCourierRow wsC, cr, "au.post", "Australia Post", "AU": cr = cr + 1
    WriteCourierRow wsC, cr, "au.startrack", "StarTrack", "AU": cr = cr + 1
    WriteCourierRow wsC, cr, "au.toll", "Toll Group", "AU": cr = cr + 1
    WriteCourierRow wsC, cr, "au.aramex", "Aramex Australia", "AU": cr = cr + 1
    WriteCourierRow wsC, cr, "au.couriersplease", "CouriersPlease", "AU": cr = cr + 1
    WriteCourierRow wsC, cr, "au.sendle", "Sendle", "AU": cr = cr + 1
    WriteCourierRow wsC, cr, "au.tnt", "TNT Australia", "AU": cr = cr + 1
    WriteCourierRow wsC, cr, "au.hunter", "Hunter Express", "AU": cr = cr + 1
    WriteCourierRow wsC, cr, "au.fastway", "Fastway Australia", "AU": cr = cr + 1

    ' --- CA ---
    WriteCarrierSection wsC, cr, "Canada (CA)": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.post", "Canada Post", "CA": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.fedex", "FedEx Canada", "CA": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.purolator", "Purolator", "CA": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.ups", "UPS Canada", "CA": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.canpar", "Canpar Express", "CA": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.intelcom", "Intelcom", "CA": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.loomis", "Loomis Express", "CA": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.ics", "ICS Courier", "CA": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.dicom", "Dicom", "CA": cr = cr + 1
    WriteCourierRow wsC, cr, "ca.gobolt", "GoBolt", "CA": cr = cr + 1

    ' --- CN ---
    WriteCarrierSection wsC, cr, "China (CN)": cr = cr + 1
    WriteCourierRow wsC, cr, "cn.sf", "SF Express", "CN": cr = cr + 1

    ' --- DE ---
    WriteCarrierSection wsC, cr, "Germany (DE)": cr = cr + 1
    WriteCourierRow wsC, cr, "de.dhl", "DHL Germany", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.hermes", "Hermes Germany", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.dpd", "DPD Germany *", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.gls", "GLS Germany *", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.ups", "UPS Germany", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.post", "Deutsche Post", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.tnt", "TNT Germany", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.fedex", "FedEx Germany", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.amazon", "Amazon Logistics Germany", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.transoFlex", "trans-o-flex", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.go", "GO! Express & Logistics", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.ilg", "ILG", "DE": cr = cr + 1
    WriteCourierRow wsC, cr, "de.nightstar", "Night Star Express", "DE": cr = cr + 1

    ' --- ES ---
    WriteCarrierSection wsC, cr, "Spain (ES)": cr = cr + 1
    WriteCourierRow wsC, cr, "es.correos", "Correos", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.seur", "SEUR", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.mrw", "MRW", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.nacex", "Nacex", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.gls", "GLS Spain", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.dhl", "DHL Spain", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.fedex", "FedEx Spain", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.ups", "UPS Spain", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.celeritas", "Celeritas", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.cttExpress", "CTT Express", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.tipsa", "Tipsa", "ES": cr = cr + 1
    WriteCourierRow wsC, cr, "es.envialia", "Envialia", "ES": cr = cr + 1

    ' --- GB ---
    WriteCarrierSection wsC, cr, "United Kingdom (GB)": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.royalmail", "Royal Mail", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.parcelforce", "Parcelforce Worldwide", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.dpd", "DPD UK", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.hermes", "Hermes UK", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.evri", "Evri", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.yodel", "Yodel", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.dhl", "DHL UK", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.ups", "UPS UK", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.fedex", "FedEx UK", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.tnt", "TNT UK", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.royalmail.special", "Royal Mail Special Delivery", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.amazon", "Amazon Logistics UK", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.collectplus", "CollectPlus", "GB": cr = cr + 1
    WriteCourierRow wsC, cr, "gb.myhermes", "myHermes", "GB": cr = cr + 1

    ' --- HK ---
    WriteCarrierSection wsC, cr, "Hong Kong (HK)": cr = cr + 1
    WriteCourierRow wsC, cr, "hk.post", "Hongkong Post", "HK": cr = cr + 1
    WriteCourierRow wsC, cr, "hk.sf", "SF Express HK", "HK": cr = cr + 1
    WriteCourierRow wsC, cr, "hk.kerry", "Kerry Express HK", "HK": cr = cr + 1
    WriteCourierRow wsC, cr, "hk.dhl", "DHL Hong Kong", "HK": cr = cr + 1
    WriteCourierRow wsC, cr, "hk.lalamove", "Lalamove HK", "HK": cr = cr + 1
    WriteCourierRow wsC, cr, "hk.sfintl", "S.F. International", "HK": cr = cr + 1

    ' --- IN ---
    WriteCarrierSection wsC, cr, "India (IN)": cr = cr + 1
    WriteCourierRow wsC, cr, "in.post", "India Post", "IN": cr = cr + 1
    WriteCourierRow wsC, cr, "in.bluedart", "BlueDart", "IN": cr = cr + 1
    WriteCourierRow wsC, cr, "in.delhivery", "Delhivery", "IN": cr = cr + 1
    WriteCourierRow wsC, cr, "in.dtdc", "DTDC", "IN": cr = cr + 1
    WriteCourierRow wsC, cr, "in.ecom", "Ecom Express", "IN": cr = cr + 1
    WriteCourierRow wsC, cr, "in.ekart", "Ekart (Flipkart)", "IN": cr = cr + 1
    WriteCourierRow wsC, cr, "in.xpressbees", "XpressBees", "IN": cr = cr + 1
    WriteCourierRow wsC, cr, "in.professional", "Professional Couriers", "IN": cr = cr + 1
    WriteCourierRow wsC, cr, "in.gati", "Gati", "IN": cr = cr + 1
    WriteCourierRow wsC, cr, "in.shadowfax", "Shadowfax", "IN": cr = cr + 1

    ' --- IE ---
    WriteCarrierSection wsC, cr, "Ireland (IE)": cr = cr + 1
    WriteCourierRow wsC, cr, "ie.anpost", "An Post", "IE": cr = cr + 1
    WriteCourierRow wsC, cr, "ie.dpd", "DPD Ireland", "IE": cr = cr + 1
    WriteCourierRow wsC, cr, "ie.fastway", "Fastway Ireland", "IE": cr = cr + 1
    WriteCourierRow wsC, cr, "ie.gls", "GLS Ireland", "IE": cr = cr + 1
    WriteCourierRow wsC, cr, "ie.dhl", "DHL Ireland", "IE": cr = cr + 1
    WriteCourierRow wsC, cr, "ie.ups", "UPS Ireland", "IE": cr = cr + 1

    ' --- IT ---
    WriteCarrierSection wsC, cr, "Italy (IT)": cr = cr + 1
    WriteCourierRow wsC, cr, "it.tnt", "TNT Italy", "IT": cr = cr + 1
    WriteCourierRow wsC, cr, "it.brt", "BRT (Bartolini)", "IT": cr = cr + 1
    WriteCourierRow wsC, cr, "it.poste", "Poste Italiane", "IT": cr = cr + 1
    WriteCourierRow wsC, cr, "it.gls", "GLS Italy", "IT": cr = cr + 1
    WriteCourierRow wsC, cr, "it.dhl", "DHL Italy", "IT": cr = cr + 1
    WriteCourierRow wsC, cr, "it.sda", "SDA Express", "IT": cr = cr + 1
    WriteCourierRow wsC, cr, "it.fedex", "FedEx Italy", "IT": cr = cr + 1
    WriteCourierRow wsC, cr, "it.ups", "UPS Italy", "IT": cr = cr + 1
    WriteCourierRow wsC, cr, "it.dpd", "DPD Italy", "IT": cr = cr + 1
    WriteCourierRow wsC, cr, "it.nexive", "Nexive", "IT": cr = cr + 1

    ' --- JP ---
    WriteCarrierSection wsC, cr, "Japan (JP)": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.yamato", "Yamato Transport", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.sagawa", "Sagawa Express", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.post", "Japan Post", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.nittsu", "Nippon Express", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.ems", "EMS Japan", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.epacket", "ePacket", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.seino", "Seino Transportation", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.fukuyama", "Fukuyama Transporting", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.tonami", "Tonami Transportation", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.seino.express", "Seino Super Express", "JP": cr = cr + 1
    WriteCourierRow wsC, cr, "jp.kix", "KIX Airport Express", "JP": cr = cr + 1

    ' --- KR ---
    WriteCarrierSection wsC, cr, "South Korea (KR)": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.post", "Korea Post", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.cj", "CJ Logistics", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.lotte", "Lotte Global Logistics", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.hanjin", "Hanjin Express", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.logen", "Logen", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.kdexp", "Kyungdong Express", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.daesin", "Daesin Express", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.cvsnet", "CVSnet", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.epost", "ePost", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.hdexp", "Hadong Express", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.homepick", "Homepick", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.ilyanglogis", "Ilyang Logistics", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.kunyoung", "Kunyoung", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.slx", "SLX Express", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.gsmnton", "GS Postbox", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.swgexp", "Seongwon Global Cargo", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.chunil", "Chunil Express", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.honam", "Honam Express", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.kgbls", "KGB Express", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.gigaepost", "Giga Express", "KR": cr = cr + 1
    WriteCourierRow wsC, cr, "kr.wonpower", "Wonpower Logistics", "KR": cr = cr + 1

    ' --- NZ ---
    WriteCarrierSection wsC, cr, "New Zealand (NZ)": cr = cr + 1
    WriteCourierRow wsC, cr, "nz.post", "NZ Post", "NZ": cr = cr + 1
    WriteCourierRow wsC, cr, "nz.courierpost", "CourierPost", "NZ": cr = cr + 1
    WriteCourierRow wsC, cr, "nz.aramex", "Aramex NZ", "NZ": cr = cr + 1
    WriteCourierRow wsC, cr, "nz.dhl", "DHL New Zealand", "NZ": cr = cr + 1
    WriteCourierRow wsC, cr, "nz.posthaste", "Post Haste", "NZ": cr = cr + 1
    WriteCourierRow wsC, cr, "nz.castle", "Castle Parcels", "NZ": cr = cr + 1

    ' --- PH ---
    WriteCarrierSection wsC, cr, "Philippines (PH)": cr = cr + 1
    WriteCourierRow wsC, cr, "ph.lbc", "LBC Express", "PH": cr = cr + 1
    WriteCourierRow wsC, cr, "ph.jt", "J&T Express PH", "PH": cr = cr + 1
    WriteCourierRow wsC, cr, "ph.ninjavan", "Ninja Van PH", "PH": cr = cr + 1
    WriteCourierRow wsC, cr, "ph.2go", "2GO Express", "PH": cr = cr + 1
    WriteCourierRow wsC, cr, "ph.post", "PHLPost", "PH": cr = cr + 1
    WriteCourierRow wsC, cr, "ph.flash", "Flash Express PH", "PH": cr = cr + 1
    WriteCourierRow wsC, cr, "ph.grab", "Grab Express PH", "PH": cr = cr + 1

    ' --- SG ---
    WriteCarrierSection wsC, cr, "Singapore (SG)": cr = cr + 1
    WriteCourierRow wsC, cr, "sg.post", "SingPost", "SG": cr = cr + 1
    WriteCourierRow wsC, cr, "sg.ninjavan", "Ninja Van SG", "SG": cr = cr + 1
    WriteCourierRow wsC, cr, "sg.jt", "J&T Express SG", "SG": cr = cr + 1
    WriteCourierRow wsC, cr, "sg.qxpress", "Qxpress", "SG": cr = cr + 1
    WriteCourierRow wsC, cr, "sg.taqbin", "Ta-Q-Bin SG", "SG": cr = cr + 1
    WriteCourierRow wsC, cr, "sg.dhl", "DHL Singapore", "SG": cr = cr + 1
    WriteCourierRow wsC, cr, "sg.janio", "Janio Asia", "SG": cr = cr + 1

    ' --- TR ---
    WriteCarrierSection wsC, cr, "Turkey (TR)": cr = cr + 1
    WriteCourierRow wsC, cr, "tr.yurtici", "Yurtici Kargo", "TR": cr = cr + 1
    WriteCourierRow wsC, cr, "tr.ptt", "PTT (Turkey Post)", "TR": cr = cr + 1
    WriteCourierRow wsC, cr, "tr.aras", "Aras Kargo", "TR": cr = cr + 1
    WriteCourierRow wsC, cr, "tr.mng", "MNG Kargo", "TR": cr = cr + 1
    WriteCourierRow wsC, cr, "tr.surat", "Surat Kargo", "TR": cr = cr + 1
    WriteCourierRow wsC, cr, "tr.trendyol", "Trendyol Express", "TR": cr = cr + 1
    WriteCourierRow wsC, cr, "tr.hepjet", "HepsiJet", "TR": cr = cr + 1

    ' --- ZA ---
    WriteCarrierSection wsC, cr, "South Africa (ZA)": cr = cr + 1
    WriteCourierRow wsC, cr, "za.courierguy", "The Courier Guy", "ZA": cr = cr + 1
    WriteCourierRow wsC, cr, "za.post", "South African Post Office", "ZA": cr = cr + 1
    WriteCourierRow wsC, cr, "za.ram", "RAM Hand-to-Hand", "ZA": cr = cr + 1
    WriteCourierRow wsC, cr, "za.aramex", "Aramex South Africa", "ZA": cr = cr + 1
    WriteCourierRow wsC, cr, "za.dawnwing", "Dawn Wing", "ZA": cr = cr + 1
    WriteCourierRow wsC, cr, "za.pargo", "Pargo", "ZA": cr = cr + 1
    WriteCourierRow wsC, cr, "za.dhl", "DHL South Africa", "ZA": cr = cr + 1

    ' --- US ---
    WriteCarrierSection wsC, cr, "United States (US)": cr = cr + 1
    WriteCourierRow wsC, cr, "us.usps", "USPS", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.fedex", "FedEx", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.ups", "UPS", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.dhl", "DHL", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.dhl.express", "DHL Express", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.dhl.ecommerce", "DHL eCommerce", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.ontrac", "OnTrac", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.ca.ontrac", "OnTrac (California)", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.lasership", "LaserShip", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.ny.lasership", "LaserShip (New York)", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.lso", "Lone Star Overnight", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.tx.lso", "Lone Star Overnight (Texas)", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.amazon", "Amazon Logistics", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.eastern", "Eastern Connection", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.gso", "Golden State Overnight", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.courier", "Courier Express", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.dynamex", "Dynamex", "US": cr = cr + 1
    WriteCourierRow wsC, cr, "us.pilot", "Pilot Freight Services", "US": cr = cr + 1

    ' --- INTL ---
    WriteCarrierSection wsC, cr, "International (INTL)": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.dhl", "DHL Express", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.fedex", "FedEx", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.ups", "UPS", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.cainiao", "Cainiao", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.sf-express", "SF Express International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.4px", "4PX Express", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.yunexpress", "Yunexpress", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.gls", "GLS", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.purolator", "Purolator International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.kerry", "Kerry Express International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.ems", "EMS", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.usps", "USPS International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.postnl", "PostNL International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.singapore-post", "Singapore Post International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.china-post", "China Post International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.korea-post", "Korea Post International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.deutsche-post", "Deutsche Post International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.tnt", "TNT Express", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.aramex", "Aramex", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.chronopost", "Chronopost International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.yanwen", "Yanwen", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.sagawa", "Sagawa Global", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.bluedart", "BlueDart", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.auspost", "Australia Post International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.royal-mail", "Royal Mail International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.canada-post", "Canada Post International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.la-poste", "La Poste International", "INTL": cr = cr + 1
    WriteCourierRow wsC, cr, "intl.swiss-post", "Swiss Post International", "INTL": cr = cr + 1

    ' Note about postal code
    SetStyleHint wsC, "A" & cr + 1, "* = Postal code may be required for this carrier"

    ' ========================================
    ' Instructions Sheet
    ' ========================================
    Dim wsU As Worksheet
    Set wsU = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsU.Name = SETUP_USAGE_SHEET_NAME
    wsU.Columns("A").ColumnWidth = 80

    WriteUsageLine wsU, 1, "[ WhereParcel Tracking Macro - Instructions ]"
    WriteUsageLine wsU, 3, "1. Initial Setup (Settings sheet)"
    WriteUsageLine wsU, 4, "   - Enter your API Key and Secret Key"
    WriteUsageLine wsU, 5, "   - Set the target sheet name, carrier column, tracking # column, result start column"
    WriteUsageLine wsU, 6, "   - Target: Track All / Untracked Only"
    WriteUsageLine wsU, 7, "   - Range: Mouse Selection / All / Specific Range"
    WriteUsageLine wsU, 9, "2. VBA Macro Setup"
    WriteUsageLine wsU, 10, "   - Press Alt+F11 > File > Import File > select TrackingMacro.bas"
    WriteUsageLine wsU, 11, "   - Save the workbook as .xlsm format"
    WriteUsageLine wsU, 13, "3. First-time Setup (run once)"
    WriteUsageLine wsU, 14, "   - Press Alt+F8 > run SetupAll"
    WriteUsageLine wsU, 15, "   - This creates Settings + Carrier Codes + Instructions sheets + buttons"
    WriteUsageLine wsU, 17, "4. Register Tracking (TrackRegister)"
    WriteUsageLine wsU, 18, "   - Press Alt+F8 > TrackRegister, or click the button"
    WriteUsageLine wsU, 19, "   - Items are batched automatically (max 100 per batch)"
    WriteUsageLine wsU, 20, "   - requestId is saved in the Settings sheet history"
    WriteUsageLine wsU, 22, "5. Fetch Results (TrackResults)"
    WriteUsageLine wsU, 23, "   - Wait 5-10 seconds after registration"
    WriteUsageLine wsU, 24, "   - Press Alt+F8 > TrackResults, or click the button"
    WriteUsageLine wsU, 25, "   - If results are not ready, wait and try again"
    WriteUsageLine wsU, 27, "6. Clear Results (ClearResults)"
    WriteUsageLine wsU, 28, "   - Clears all result columns in the target sheet"
    WriteUsageLine wsU, 30, "7. Check for Updates (CheckVersion)"
    WriteUsageLine wsU, 31, "   - Compares your macro version with the latest on the server"
    WriteUsageLine wsU, 32, "   - If a new version is available, guides you through updating"
    WriteUsageLine wsU, 33, "   - Auto-update or manual download options available"
    WriteUsageLine wsU, 35, "8. Reset Setup (SetupAll)"
    WriteUsageLine wsU, 36, "   - Recreates all sheets (Settings, Tracking, Carrier Codes, Instructions)"
    WriteUsageLine wsU, 37, "   - Recreates buttons on the Settings sheet"
    WriteUsageLine wsU, 38, "   - Run this after updating the macro to apply changes"
    WriteUsageLine wsU, 40, "[ Important Notes ]"
    WriteUsageLine wsU, 41, "- Do not add or delete data rows between registration and fetching results"
    WriteUsageLine wsU, 42, "- Usage charges may apply - avoid unnecessary repeated registrations"
    WriteUsageLine wsU, 43, "- Max 100 items per batch (auto-split for larger sets)"
    WriteUsageLine wsU, 44, "- Carrier codes are listed in the 'Carrier Codes' sheet"
    WriteUsageLine wsU, 46, "[ Demo Key Warning ]"
    WriteUsageLine wsU, 47, "- The default keys are demo keys with limited usage"
    WriteUsageLine wsU, 48, "- Get your production keys at https://whereparcel.com"

    ' --- Delete old sheets ---
    If oldCount > 0 Then
        Application.DisplayAlerts = False
        On Error Resume Next
        For si = 1 To oldCount
            ThisWorkbook.Sheets(oldSheets(si)).Delete
        Next si
        On Error GoTo SetupError
        Application.DisplayAlerts = True
    End If

    ' ========================================
    ' Buttons
    ' ========================================
    wsS.Activate
    SetupButtons wsS

    Application.ScreenUpdating = True

    MsgBox "Settings, Carrier Codes, Instructions, and buttons have been created!", _
           vbInformation, "Setup Complete"
    Exit Sub

SetupError:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Setup error: " & Err.Description, vbCritical, "Error"
End Sub


'==============================================================================
' PRIVATE: Style helpers for SetupAll
'==============================================================================
Private Sub SetStyleTitle(ws As Worksheet, addr As String, txt As String)
    With ws.Range(addr)
        .Value = txt
        .Font.Name = "Calibri"
        .Font.Bold = True
        .Font.Size = 14
        .Font.Color = RGB(45, 55, 72)
    End With
End Sub

Private Sub SetStyleSection(ws As Worksheet, addr As String, txt As String)
    With ws.Range(addr)
        .Value = txt
        .Font.Name = "Calibri"
        .Font.Bold = True
        .Font.Size = 11
        .Font.Color = RGB(43, 108, 176)
    End With
End Sub

Private Sub SetStyleLabel(ws As Worksheet, addr As String, txt As String)
    With ws.Range(addr)
        .Value = txt
        .Font.Name = "Calibri"
        .Font.Bold = True
        .Font.Size = 10
        .Font.Color = RGB(45, 55, 72)
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
    End With
End Sub

Private Sub SetStyleInput(ws As Worksheet, addr As String, txt As String)
    With ws.Range(addr)
        .Value = txt
        .Font.Name = "Calibri"
        .Font.Size = 10
        .Interior.Color = RGB(255, 255, 240)
        .Borders.LineStyle = xlContinuous
        .Borders.Color = RGB(203, 213, 224)
    End With
End Sub

Private Sub SetStyleHint(ws As Worksheet, addr As String, txt As String)
    With ws.Range(addr)
        .Value = txt
        .Font.Name = "Calibri"
        .Font.Size = 9
        .Font.Color = RGB(113, 128, 150)
    End With
End Sub

Private Sub SetStyleNormal(ws As Worksheet, addr As String, txt As String)
    With ws.Range(addr)
        .Value = txt
        .Font.Name = "Calibri"
        .Font.Size = 10
    End With
End Sub


Private Sub WriteCarrierSection(ws As Worksheet, r As Long, title As String)
    With ws.Range("A" & r & ":C" & r)
        .Merge
        .Value = "-- " & title & " --"
        .Font.Name = "Calibri"
        .Font.Bold = True
        .Font.Size = 10
        .Font.Color = RGB(43, 108, 176)
        .Interior.Color = RGB(237, 242, 247)
    End With
End Sub

Private Sub WriteCourierRow(ws As Worksheet, r As Long, code As String, cName As String, Optional country As String = "")
    With ws.Cells(r, 1)
        .Value = code
        .Font.Name = "Calibri"
        .Font.Size = 10
    End With
    With ws.Cells(r, 2)
        .Value = cName
        .Font.Name = "Calibri"
        .Font.Size = 10
    End With
    If country <> "" Then
        With ws.Cells(r, 3)
            .Value = country
            .Font.Name = "Calibri"
            .Font.Size = 10
            .Font.Color = RGB(113, 128, 150)
        End With
    End If
End Sub

Private Sub WriteUsageLine(ws As Worksheet, r As Long, txt As String)
    With ws.Cells(r, 1)
        .Value = txt
        If Left(txt, 1) = "[" Then
            .Font.Name = "Calibri"
            .Font.Bold = True
            .Font.Size = 12
            .Font.Color = RGB(45, 55, 72)
        Else
            .Font.Name = "Calibri"
            .Font.Size = 10
        End If
    End With
End Sub


'==============================================================================
' PUBLIC MACRO: CheckVersion
' Check for updates and offer auto-update or manual download
'==============================================================================
Public Sub CheckVersion()
    On Error GoTo ErrHandler

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", HOMEPAGE_URL & "/downloads/version.txt", False
    http.Send

    If http.Status <> 200 Then
        MsgBox "Failed to check for updates." & vbCrLf & _
               "Please check your internet connection.", vbExclamation, "Version Check"
        Exit Sub
    End If

    Dim serverVersion As String
    serverVersion = Trim(Replace(Replace(http.responseText, vbLf, ""), vbCr, ""))
    Set http = Nothing

    If serverVersion = MACRO_VERSION Then
        MsgBox "You are on the latest version! (v" & MACRO_VERSION & ")", vbInformation, "Version Check"
        Exit Sub
    End If

    MsgBox "A new version is available!" & vbCrLf & vbCrLf & _
           "  Current: v" & MACRO_VERSION & vbCrLf & _
           "  Latest:  v" & serverVersion & vbCrLf & vbCrLf & _
           "Choose one of the following methods to update:" & vbCrLf & vbCrLf & _
           "Option 1) Auto-update" & vbCrLf & _
           "  - Press Alt+F8 > UpdateMacro" & vbCrLf & _
           "  - One-time setup required:" & vbCrLf & _
           "    File > Options > Trust Center > Trust Center Settings" & vbCrLf & _
           "    > Macro Settings > Trust access to the VBA project object model" & vbCrLf & vbCrLf & _
           "Option 2) Manual download" & vbCrLf & _
           "  - Visit " & HOMEPAGE_URL & "/tools/excel-tracker" & vbCrLf & _
           "    and download the .bas file" & vbCrLf & _
           "  - Alt+F11 > Delete old TrackingMacro > Import new file" & vbCrLf & vbCrLf & _
           "After updating, please run [Reset Setup] to refresh sheets.", _
           vbInformation, "Update Available"
    Exit Sub

ErrHandler:
    MsgBox "Error checking version: " & Err.Description, vbCritical, "Error"
End Sub

' Alias for backwards compatibility (Alt+F8 > UpdateMacro)
Public Sub UpdateMacro()
    CheckVersion
End Sub


'==============================================================================
' PRIVATE: Auto-update - download and replace module code
'==============================================================================
Private Sub DoAutoUpdate()
    ' Test VBA project access
    On Error Resume Next
    Dim testCount As Long
    testCount = ThisWorkbook.VBProject.VBComponents.Count
    If Err.Number <> 0 Then
        Err.Clear
        On Error GoTo 0
        MsgBox "VBA project access permission is required." & vbCrLf & vbCrLf & _
               "Setup steps:" & vbCrLf & _
               "File > Options > Trust Center > Trust Center Settings" & vbCrLf & _
               "> Macro Settings > ""Trust access to the VBA" & vbCrLf & _
               "  project object model"" checkbox" & vbCrLf & vbCrLf & _
               "If setup is difficult, press [No] and" & vbCrLf & _
               "use manual download instead.", _
               vbExclamation, "Permission Required"
        Exit Sub
    End If
    On Error GoTo ErrHandler

    ' Download .bas file
    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", HOMEPAGE_URL & "/downloads/TrackingMacro.bas", False
    http.Send

    If http.Status <> 200 Then
        MsgBox "Failed to download the file.", vbCritical, "Update"
        Exit Sub
    End If

    ' Save to temp (binary to preserve encoding)
    Dim tempPath As String
    tempPath = Environ("TEMP") & "\TrackingMacro_update.bas"

    Dim stream As Object
    Set stream = CreateObject("ADODB.Stream")
    stream.Type = 1
    stream.Open
    stream.Write http.responseBody
    stream.SaveToFile tempPath, 2
    stream.Close
    Set stream = Nothing
    Set http = Nothing

    ' Read and strip Attribute lines
    Dim fso As Object, ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile(tempPath, 1, False)
    Dim fileContent As String
    fileContent = ts.ReadAll
    ts.Close

    Dim lines() As String
    lines = Split(fileContent, vbCrLf)
    Dim cleanCode As String
    cleanCode = ""
    Dim li As Long
    Dim pastAttributes As Boolean
    pastAttributes = False
    For li = 0 To UBound(lines)
        If Not pastAttributes Then
            If Left(lines(li), 10) = "Attribute " Then GoTo NextAutoLine
            pastAttributes = True
        End If
        If cleanCode <> "" Then cleanCode = cleanCode & vbCrLf
        cleanCode = cleanCode & lines(li)
NextAutoLine:
    Next li

    ' Replace module code
    Dim vbComp As Object
    Set vbComp = ThisWorkbook.VBProject.VBComponents("TrackingMacro")
    With vbComp.CodeModule
        If .CountOfLines > 0 Then .DeleteLines 1, .CountOfLines
        .AddFromString cleanCode
    End With

    fso.DeleteFile tempPath, True

    MsgBox "Update complete!" & vbCrLf & vbCrLf & _
           "Please run [Reset Setup] to refresh sheets.", vbInformation, "Update Complete"
    Exit Sub

ErrHandler:
    MsgBox "Error during update: " & Err.Description, vbCritical, "Error"
End Sub


'==============================================================================
' PRIVATE: Download .bas file to Desktop for manual import
'==============================================================================
Private Sub DownloadMacroToDesktop()
    On Error GoTo ErrHandler

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", HOMEPAGE_URL & "/downloads/TrackingMacro.bas", False
    http.Send

    If http.Status <> 200 Then
        MsgBox "Download failed.", vbCritical, "Download"
        Exit Sub
    End If

    Dim desktopPath As String
    desktopPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\TrackingMacro.bas"

    Dim stream As Object
    Set stream = CreateObject("ADODB.Stream")
    stream.Type = 1
    stream.Open
    stream.Write http.responseBody
    stream.SaveToFile desktopPath, 2
    stream.Close
    Set stream = Nothing
    Set http = Nothing

    MsgBox "TrackingMacro.bas downloaded to Desktop!" & vbCrLf & vbCrLf & _
           "Manual import steps:" & vbCrLf & _
           "  1. Alt+F11 (open VBA editor)" & vbCrLf & _
           "  2. Right-click old TrackingMacro > Remove" & vbCrLf & _
           "  3. File > Import File > select TrackingMacro.bas from Desktop" & vbCrLf & vbCrLf & _
           "After import, please run [Reset Setup].", vbInformation, "Download Complete"
    Exit Sub

ErrHandler:
    MsgBox "Error during download: " & Err.Description, vbCritical, "Error"
End Sub


'==============================================================================
' PRIVATE: Normalize carrier code from various input formats
' e.g. "ups" -> "us.ups", "cj" -> "kr.cj", "yamato" -> "jp.yamato"
' If the code already contains a dot, it is returned as-is.
'==============================================================================
Private Function NormalizeCarrierCode(ByVal raw As String) As String
    Dim s As String
    s = LCase(Trim(raw))

    ' Already has dot notation (e.g. "kr.cj") - pass through
    If InStr(s, ".") > 0 Then
        NormalizeCarrierCode = raw
        Exit Function
    End If

    Select Case s
        ' --- Korea (KR) ---
        Case "cj", "cj logistics"
            NormalizeCarrierCode = "kr.cj"
        Case "lotte", "lotte logistics"
            NormalizeCarrierCode = "kr.lotte"
        Case "hanjin"
            NormalizeCarrierCode = "kr.hanjin"
        Case "logen"
            NormalizeCarrierCode = "kr.logen"
        Case "kdexp", "kyungdong"
            NormalizeCarrierCode = "kr.kdexp"
        Case "daesin"
            NormalizeCarrierCode = "kr.daesin"
        Case "cvsnet"
            NormalizeCarrierCode = "kr.cvsnet"
        Case "epost"
            NormalizeCarrierCode = "kr.epost"
        Case "chunil"
            NormalizeCarrierCode = "kr.chunil"
        Case "honam"
            NormalizeCarrierCode = "kr.honam"

        ' --- US ---
        Case "ups"
            NormalizeCarrierCode = "us.ups"
        Case "usps"
            NormalizeCarrierCode = "us.usps"
        Case "fedex"
            NormalizeCarrierCode = "us.fedex"
        Case "amazon", "amazon logistics"
            NormalizeCarrierCode = "us.amazon"
        Case "ontrac"
            NormalizeCarrierCode = "us.ontrac"
        Case "lasership"
            NormalizeCarrierCode = "us.lasership"

        ' --- Japan ---
        Case "yamato"
            NormalizeCarrierCode = "jp.yamato"
        Case "sagawa"
            NormalizeCarrierCode = "jp.sagawa"
        Case "japan post"
            NormalizeCarrierCode = "jp.post"

        ' --- International / Global ---
        Case "dhl"
            NormalizeCarrierCode = "intl.dhl"
        Case "dhl express"
            NormalizeCarrierCode = "intl.dhl"
        Case "tnt"
            NormalizeCarrierCode = "intl.tnt"
        Case "aramex"
            NormalizeCarrierCode = "intl.aramex"
        Case "ems"
            NormalizeCarrierCode = "intl.ems"
        Case "cainiao"
            NormalizeCarrierCode = "intl.cainiao"
        Case "gls"
            NormalizeCarrierCode = "intl.gls"

        ' --- UK ---
        Case "royal mail", "royalmail"
            NormalizeCarrierCode = "gb.royalmail"
        Case "evri"
            NormalizeCarrierCode = "gb.evri"
        Case "yodel"
            NormalizeCarrierCode = "gb.yodel"
        Case "parcelforce"
            NormalizeCarrierCode = "gb.parcelforce"

        ' --- Germany ---
        Case "hermes"
            NormalizeCarrierCode = "de.hermes"
        Case "dpd"
            NormalizeCarrierCode = "de.dpd"
        Case "deutsche post"
            NormalizeCarrierCode = "de.post"

        ' --- Spain ---
        Case "correos"
            NormalizeCarrierCode = "es.correos"
        Case "seur"
            NormalizeCarrierCode = "es.seur"
        Case "mrw"
            NormalizeCarrierCode = "es.mrw"
        Case "nacex"
            NormalizeCarrierCode = "es.nacex"
        Case "ctt express", "cttexpress"
            NormalizeCarrierCode = "es.cttExpress"

        ' --- Canada ---
        Case "canada post"
            NormalizeCarrierCode = "ca.post"
        Case "purolator"
            NormalizeCarrierCode = "ca.purolator"

        ' --- Australia ---
        Case "australia post", "auspost"
            NormalizeCarrierCode = "au.post"
        Case "startrack"
            NormalizeCarrierCode = "au.startrack"

        ' --- China ---
        Case "sf express", "sf", "순풍"
            NormalizeCarrierCode = "cn.sf"

        ' --- India ---
        Case "bluedart"
            NormalizeCarrierCode = "in.bluedart"
        Case "delhivery"
            NormalizeCarrierCode = "in.delhivery"

        ' --- Singapore ---
        Case "singpost"
            NormalizeCarrierCode = "sg.post"
        Case "ninja van", "ninjavan"
            NormalizeCarrierCode = "sg.ninjavan"

        ' --- Philippines ---
        Case "lbc", "lbc express"
            NormalizeCarrierCode = "ph.lbc"
        Case "j&t", "j&t express", "jt express"
            NormalizeCarrierCode = "ph.jt"

        ' --- Not matched: return as-is ---
        Case Else
            NormalizeCarrierCode = raw
    End Select
End Function


' Escape special characters for JSON
Private Function EscapeJson(s As String) As String
    Dim result As String
    result = s
    result = Replace(result, "\", "\\")
    result = Replace(result, """", "\""")
    result = Replace(result, vbCr, "")
    result = Replace(result, vbLf, "")
    result = Replace(result, vbTab, "")
    EscapeJson = result
End Function