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

' --- Settings Sheet Layout ---
' Settings sheet is referenced by name (order-independent)
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_COURIER_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 = 30

' --- Setup Sheet Names ---
Private Const SETUP_SHEET_NAME As String = "¼³Á¤"
Private Const SETUP_COURIER_SHEET_NAME As String = "Åùè»çÄÚµå ¸ñ·Ï"
Private Const SETUP_USAGE_SHEET_NAME As String = "»ç¿ë¹ý"

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

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

' --- Button Layout ---
Private Const BTN_TOP As Long = 25
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           ' ¹è¼Û»óÅÂ
Private Const RES_STATUS_TEXT As Long = 1       ' ¹è¼Û»óÅÂ(ÅؽºÆ®)
Private Const RES_IS_DELIVERED As Long = 2      ' ¹è¼Û¿Ï·á¿©ºÎ
Private Const RES_RECEIVER As Long = 3          ' ¼ö·ÉÀÎ
Private Const RES_PRODUCT As Long = 4           ' »óÇ°¸í
Private Const RES_DATE_DELIVERED As Long = 5    ' ¹è¼Û¿Ï·áÀϽÃ
Private Const RES_DATE_LAST As Long = 6         ' ÃÖ±Ù¹è¼ÛÀϽÃ
Private Const RES_ERROR As Long = 7             ' ¿À·ù
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) = "   Á¶È¸ µî·Ï   ": btns(1, 3) = "TrackRegister"
    btns(2, 1) = "btn_results":  btns(2, 2) = "   °á°ú È®ÀÎ   ": btns(2, 3) = "TrackResults"
    btns(3, 1) = "btn_clear":    btns(3, 2) = "   °á°ú ÃʱâÈ­   ": 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 = "¸¼Àº °íµñ"
        btn.TextFrame.Characters.Font.Bold = True
        btn.OnAction = btns(i, 3)

        btnLeft = btnLeft + BTN_WIDTH + 10
    Next i

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

    ' CheckVersion button
    Dim checkBtn As Shape
    Set checkBtn = ws.Shapes.AddFormControl( _
        xlButtonControl, _
        utilLeft, btnTop, 110, BTN_HEIGHT - 4)
    checkBtn.Name = "btn_check"
    checkBtn.TextFrame.Characters.Text = " ÃֽŹöÀü È®ÀÎ "
    checkBtn.TextFrame.Characters.Font.Size = 9
    checkBtn.TextFrame.Characters.Font.Name = "¸¼Àº °íµñ"
    checkBtn.OnAction = "CheckVersion"

    ' SetupAll button
    Dim setupBtn As Shape
    Set setupBtn = ws.Shapes.AddFormControl( _
        xlButtonControl, _
        utilLeft + 118, btnTop, 100, BTN_HEIGHT - 4)
    setupBtn.Name = "btn_setup"
    setupBtn.TextFrame.Characters.Text = " ¼³Á¤ ÃʱâÈ­ "
    setupBtn.TextFrame.Characters.Font.Size = 9
    setupBtn.TextFrame.Characters.Font.Name = "¸¼Àº °íµñ"
    setupBtn.OnAction = "SetupAll"

    MsgBox "¹öÆ°ÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù!", vbInformation, "¿Ï·á"
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 /v1/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 "API Key¿Í Secret Key¸¦ ¼³Á¤ ½ÃÆ®¿¡ ÀÔ·ÂÇØÁÖ¼¼¿ä.", vbExclamation, "¼³Á¤ ¿À·ù"
        Exit Sub
    End If
    If targetSheetName = "" Then
        MsgBox "´ë»ó ½ÃÆ®¸íÀ» ¼³Á¤ÇØÁÖ¼¼¿ä.", vbExclamation, "¼³Á¤ ¿À·ù"
        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 "'" & targetSheetName & "' ½ÃÆ®¸¦ ãÀ» ¼ö ¾ø½À´Ï´Ù.", vbExclamation, "¼³Á¤ ¿À·ù"
        Exit Sub
    End If

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

    If courierCol = 0 Or trackingCol = 0 Or resultCol = 0 Then
        MsgBox "¿­ ¼³Á¤°ªÀÌ ¿Ã¹Ù¸£Áö ¾Ê½À´Ï´Ù. ¾ËÆĺª(A~Z)À» ÀÔ·ÂÇØÁÖ¼¼¿ä.", vbExclamation, "¼³Á¤ ¿À·ù"
        Exit Sub
    End If

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

    ' ¦¡¦¡ Determine Range ¦¡¦¡
    Select Case rangeMode
        Case "¸¶¿ì½º ¼±ÅÃ"
            ws.Activate
            On Error Resume Next
            Set selectedRange = Application.InputBox( _
                "Á¶È¸ÇÒ ¹üÀ§¸¦ ¸¶¿ì½º·Î ¼±ÅÃÇϼ¼¿ä." & vbCrLf & _
                "(Ãë¼Ò ½Ã Áߴܵ˴ϴÙ)", _
                "¹üÀ§ ¼±ÅÃ", Type:=8)
            On Error GoTo ErrorHandler
            If selectedRange Is Nothing Then Exit Sub
            startRow = selectedRange.Row
            endRow = selectedRange.Row + selectedRange.Rows.Count - 1

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

        Case "ƯÁ¤ ¿µ¿ª"
            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 "¿µ¿ª ¼³Á¤ÀÌ ¿Ã¹Ù¸£Áö ¾Ê½À´Ï´Ù.", vbExclamation, "¼³Á¤ ¿À·ù"
            Exit Sub
    End Select

    If endRow < startRow Then
        MsgBox "¼±ÅÃµÈ ¹üÀ§¿¡ µ¥ÀÌÅÍ°¡ ¾ø½À´Ï´Ù.", vbInformation, "¾Ë¸²"
        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 courier As String, tracking As String
    For r = startRow To endRow
        courier = NormalizeCourierCode(Trim(CStr(ws.Cells(r, courierCol).Value)))
        tracking = Trim(CStr(ws.Cells(r, trackingCol).Value))

        If courier <> "" And tracking <> "" Then
            ' Check target mode: skip rows that already have results
            If targetMode = "°á°ú ¾ø´Â Çุ" 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(courier, tracking, CStr(r))
            itemRows(itemCount) = r
        End If
NextRow:
    Next r

    If itemCount = 0 Then
        Dim msg As String
        msg = "Á¶È¸ÇÒ µ¥ÀÌÅÍ°¡ ¾ø½À´Ï´Ù."
        If totalSkipped > 0 Then msg = msg & vbCrLf & "(ÀÌ¹Ì °á°ú°¡ ÀÖ´Â " & totalSkipped & "°Ç Á¦¿ÜµÊ)"
        MsgBox msg, vbInformation, "¾Ë¸²"
        Exit Sub
    End If

    ' ¦¡¦¡ Confirm ¦¡¦¡
    Dim confirmMsg As String
    confirmMsg = itemCount & "°ÇÀ» µî·ÏÇÕ´Ï´Ù."
    If totalSkipped > 0 Then confirmMsg = confirmMsg & vbCrLf & "(°á°ú ÀÖ´Â " & totalSkipped & "°Ç Á¦¿Ü)"
    confirmMsg = confirmMsg & vbCrLf & vbCrLf & "¿ä±ÝÀÌ ºÎ°úµÉ ¼ö ÀÖ½À´Ï´Ù. ÁøÇàÇϽðڽÀ´Ï±î?"
    If MsgBox(confirmMsg, vbYesNo + vbQuestion, "Á¶È¸ µî·Ï") = vbNo Then Exit Sub

    Application.ScreenUpdating = False
    Application.StatusBar = "Á¶È¸ µî·Ï Áß..."

    ' ¦¡¦¡ 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 = "{""items"":["
        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 = "µî·Ï Áß... ¹èÄ¡ " & batchNum & "/" & totalBatches
        response = HttpPost(API_BASE_URL & "/v1/webhooks/register", apiKey, secretKey, jsonBody)

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

        If requestId <> "" Then
            ' Save to history
            SaveHistory requestId, batchCount, "´ë±â"
            registeredCount = registeredCount + batchCount
        Else
            ' Error
            errMsg = ExtractJsonString(response, "error")
            If errMsg = "" Then errMsg = "¾Ë ¼ö ¾ø´Â ¿À·ù"
            SaveHistory "½ÇÆÐ", 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 & "°Ç µî·Ï ¿Ï·á!" & vbCrLf & _
           totalBatches & "°³ ¹èÄ¡, requestId°¡ ¼³Á¤ ½ÃÆ®¿¡ ±â·ÏµÇ¾ú½À´Ï´Ù." & vbCrLf & vbCrLf & _
           "Àá½Ã ÈÄ [°á°ú È®ÀÎ]À» ½ÇÇàÇØÁÖ¼¼¿ä.", vbInformation, "µî·Ï ¿Ï·á"
    Exit Sub

ErrorHandler:
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "¿À·ù ¹ß»ý: " & Err.Description, vbCritical, "¿À·ù"
End Sub


'==============================================================================
' PUBLIC MACRO: TrackResults
' Fetch results via GET /v1/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 "API Key¿Í Secret Key¸¦ ÀÔ·ÂÇØÁÖ¼¼¿ä.", vbExclamation, "¼³Á¤ ¿À·ù"
        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 "'" & targetSheetName & "' ½ÃÆ®¸¦ ãÀ» ¼ö ¾ø½À´Ï´Ù.", vbExclamation, "¼³Á¤ ¿À·ù"
        Exit Sub
    End If

    resultCol = ColLetterToNum(GetSetting(CELL_RESULT_COL))

    If resultCol = 0 Then
        MsgBox "°á°ú ½ÃÀÛ ¿­ ¼³Á¤°ªÀÌ ¿Ã¹Ù¸£Áö ¾Ê½À´Ï´Ù. ¾ËÆĺª(A~Z)À» ÀÔ·ÂÇØÁÖ¼¼¿ä.", vbExclamation, "¼³Á¤ ¿À·ù"
        Exit Sub
    End If

    Set wsSetting = ThisWorkbook.Sheets(SETUP_SHEET_NAME)

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

    If lastHistRow < HISTORY_START_ROW Then
        MsgBox "Á¶È¸µÈ µî·Ï ÀÌ·ÂÀÌ ¾ø½À´Ï´Ù." & vbCrLf & "¸ÕÀú [Á¶È¸ µî·Ï]À» ½ÇÇàÇØÁÖ¼¼¿ä.", vbInformation, "¾Ë¸²"
        Exit Sub
    End If

    Application.ScreenUpdating = False
    Application.StatusBar = "°á°ú Á¶È¸ Áß..."

    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 isDelivered 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 = "½ÇÆÐ" Or status = "¿Ï·á" Then GoTo NextHist

        processedBatches = processedBatches + 1
        Application.StatusBar = "°á°ú Á¶È¸ Áß... " & reqId

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

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

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

        If itemsArray Is Nothing Then
            wsSetting.Cells(histRow, 4).Value = "ÀÀ´ä ÆÄ½Ì ½ÇÆÐ"
            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), "currentStatus")
                isDelivered = ExtractJsonString(CStr(itemJson), "isDelivered")
                itemError = ExtractJsonString(CStr(itemJson), "error")

                ' 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 = ExtractJsonString(tdBlock, "deliveryStatusText")

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

                    ws.Cells(targetRow, resultCol + RES_RECEIVER).Value = ExtractJsonString(tdBlock, "receiverName")
                    ws.Cells(targetRow, resultCol + RES_PRODUCT).Value = ExtractJsonString(tdBlock, "productName")
                    ws.Cells(targetRow, resultCol + RES_DATE_DELIVERED).Value = ExtractJsonString(tdBlock, "dateDelivered")
                    ws.Cells(targetRow, resultCol + RES_DATE_LAST).Value = ExtractJsonString(tdBlock, "dateLastProgress")

                    ' 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 "PICKUP"
                            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)
                    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 = "¿Ï·á"
        Else
            wsSetting.Cells(histRow, 4).Value = "ÀϺΠ´ë±â"
        End If

NextHist:
    Next histRow

    Application.StatusBar = False
    Application.ScreenUpdating = True

    If processedBatches = 0 Then
        MsgBox "ó¸®ÇÒ ´ë±âÁßÀÎ µî·ÏÀÌ ¾ø½À´Ï´Ù.", vbInformation, "¾Ë¸²"
    Else
        Dim resultMsg As String
        resultMsg = "°á°ú È®ÀÎ ¿Ï·á!" & vbCrLf & vbCrLf
        resultMsg = resultMsg & "  ¾÷µ¥ÀÌÆ®: " & totalUpdated & "°Ç" & vbCrLf
        If totalPending > 0 Then
            resultMsg = resultMsg & "  ´ë±âÁß: " & totalPending & "°Ç (Àá½Ã ÈÄ ´Ù½Ã ½ÇÇà)" & vbCrLf
        End If
        If totalErrors > 0 Then
            resultMsg = resultMsg & "  ¿À·ù: " & totalErrors & "°Ç" & vbCrLf
        End If
        MsgBox resultMsg, vbInformation, "°á°ú È®ÀÎ"
    End If
    Exit Sub

ErrorHandler:
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox "¿À·ù ¹ß»ý: " & Err.Description, vbCritical, "¿À·ù"
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 "'" & targetSheetName & "' ½ÃÆ®¸¦ ãÀ» ¼ö ¾ø½À´Ï´Ù.", vbExclamation, "¼³Á¤ ¿À·ù"
        Exit Sub
    End If

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

    If resultCol = 0 Or courierCol = 0 Then
        MsgBox "¿­ ¼³Á¤°ªÀÌ ¿Ã¹Ù¸£Áö ¾Ê½À´Ï´Ù. ¾ËÆĺª(A~Z)À» ÀÔ·ÂÇØÁÖ¼¼¿ä.", vbExclamation, "¼³Á¤ ¿À·ù"
        Exit Sub
    End If

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

    If lastRow < 1 Then
        MsgBox "µ¥ÀÌÅÍ°¡ ¾ø½À´Ï´Ù.", vbInformation, "¾Ë¸²"
        Exit Sub
    End If

    If MsgBox("°á°ú ¿­(D~K)À» ¸ðµÎ ÃʱâÈ­ÇÕ´Ï´Ù." & vbCrLf & "ÁøÇàÇϽðڽÀ´Ï±î?", _
              vbYesNo + vbQuestion, "°á°ú ÃʱâÈ­") = vbNo Then Exit Sub

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

    MsgBox "°á°ú°¡ ÃʱâÈ­µÇ¾ú½À´Ï´Ù.", vbInformation, "¿Ï·á"
    Exit Sub

ErrorHandler:
    MsgBox "¿À·ù ¹ß»ý: " & Err.Description, vbCritical, "¿À·ù"
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: 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(courierCode As String, trackingNumber As String, clientId As String) As String
    BuildItemJson = "{""courierCode"":""" & EscapeJson(courierCode) & _
                    """,""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
' ¼³Á¤ ½ÃÆ® + Åùè»çÄÚµå ¸ñ·Ï + ¹öÆ° ÀÚµ¿ »ý¼º
' ±âÁ¸ ½ÃÆ®¸¦ ±³Ã¼·Î µ¿ÀÛÇÏ´Â (¼³Á¤ ¾÷µ¥ÀÌÆ®¿ë)
'==============================================================================
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

    ' --- ±âÁ¸ ½ÃÆ® À̸§ º¯°æ ---
    oldCount = 0
    Dim sheetNames As Variant
    sheetNames = Array(SETUP_SHEET_NAME, SETUP_COURIER_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

    ' ========================================
    ' ¼³Á¤ ½ÃÆ® »ý¼º (ù ¹ø° ½ÃÆ®·Î)
    ' ========================================
    Set wsS = ThisWorkbook.Sheets.Add(Before:=ThisWorkbook.Sheets(1))
    wsS.Name = SETUP_SHEET_NAME

    wsS.Columns("A").ColumnWidth = 16
    wsS.Columns("B").ColumnWidth = 22
    wsS.Columns("C").ColumnWidth = 14
    wsS.Columns("D").ColumnWidth = 14
    wsS.Columns("E").ColumnWidth = 32
    wsS.Columns("G").ColumnWidth = 44

    ' Title
    wsS.Range("A1:D1").Merge
    SetStyleTitle wsS, "A1", "Åùè Á¶È¸ ¸ÅÅ©·Î ¼³Á¤"
    wsS.Rows(1).RowHeight = 32

    ' --- API ¼³Á¤ ---
    SetStyleSection wsS, "A3", "[ API ¼³Á¤ ]"
    SetStyleLabel wsS, "A4", "API Key"
    wsS.Range("B4:C4").Merge
    SetStyleInput wsS, "B4", "pk_demo_002_do_not_use_in_production"
    SetStyleHint wsS, "E4", "pk_live_xxxx Çü½Ä"
    SetStyleLabel wsS, "A5", "Secret Key"
    wsS.Range("B5:C5").Merge
    SetStyleInput wsS, "B5", "sk_demo_002_do_not_use_in_production"
    SetStyleHint wsS, "E5", "sk_client_xxxx Çü½Ä"

    ' --- ½ÃÆ® ¼³Á¤ ---
    SetStyleSection wsS, "A7", "[ ½ÃÆ® ¼³Á¤ ]"
    SetStyleLabel wsS, "A8", "´ë»ó ½ÃÆ®¸í"
    SetStyleInput wsS, "B8", "ÅùèÁ¶È¸"
    SetStyleHint wsS, "E8", "µ¥ÀÌÅÍ°¡ ÀÖ´Â ½ÃÆ® À̸§"
    SetStyleLabel wsS, "A9", "Åùè»çÄÚµå ¿­"
    SetStyleInput wsS, "B9", "B"
    SetStyleHint wsS, "E9", "¾ËÆĺª (A, B, C...)"
    SetStyleLabel wsS, "A10", "¼ÛÀå¹øÈ£ ¿­"
    SetStyleInput wsS, "B10", "C"
    SetStyleHint wsS, "E10", "¾ËÆĺª (A, B, C...)"
    SetStyleLabel wsS, "A11", "°á°ú ½ÃÀÛ ¿­"
    SetStyleInput wsS, "B11", "D"
    SetStyleHint wsS, "E11", "D¿­ºÎÅÍ °á°ú°¡ ä¿öÁü"

    ' --- ½ÇÇà ¿É¼Ç ---
    SetStyleSection wsS, "A13", "[ ½ÇÇà ¿É¼Ç ]"
    SetStyleLabel wsS, "A14", "Ÿ°Ù"
    SetStyleInput wsS, "B14", "Àüü ÃßÀû"
    SetStyleLabel wsS, "A15", "¿µ¿ª"
    SetStyleInput wsS, "B15", "¸¶¿ì½º ¼±ÅÃ"
    SetStyleLabel wsS, "A16", "½ÃÀÛ Çà"
    SetStyleInput wsS, "B16", ""
    SetStyleHint wsS, "E16", "ƯÁ¤ ¿µ¿ªÀÏ ¶§¸¸ (ºñ¿ì¸é óÀ½ºÎÅÍ)"
    SetStyleLabel wsS, "A17", "³¡ Çà"
    SetStyleInput wsS, "B17", ""
    SetStyleHint wsS, "E17", "ƯÁ¤ ¿µ¿ªÀÏ ¶§¸¸ (ºñ¿ì¸é ³¡±îÁö)"

    ' Dropdowns
    wsS.Range("B14").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="Àüü ÃßÀû,°á°ú ¾ø´Â Çุ"
    wsS.Range("B15").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="¸¶¿ì½º ¼±ÅÃ,Àüü,ƯÁ¤ ¿µ¿ª"

    ' --- ¸ÅÅ©·Î ½ÇÇà ---
    SetStyleSection wsS, "A19", "[ ¸ÅÅ©·Î ½ÇÇà ]"
    SetStyleHint wsS, "A20", "Alt+F8 ¶Ç´Â ¾Æ·¡ ¹öÆ°À¸·Î ½ÇÇà"
    SetStyleNormal wsS, "A21", "  TrackRegister  = Á¶È¸ µî·Ï"
    SetStyleNormal wsS, "A22", "  TrackResults   = °á°ú È®ÀÎ"
    SetStyleNormal wsS, "A23", "  ClearResults   = °á°ú ÃʱâÈ­"

    ' --- ¸ÅÅ©·Î ¾÷µ¥ÀÌÆ® ¾È³» ---
    SetStyleSection wsS, "G19", "[ ¸ÅÅ©·Î °ü¸® ]"
    SetStyleHint wsS, "G20", "°¡²û [ÃֽŹöÀü È®ÀÎ] ¹öÆ°À» ´­·¯ÁÖ¼¼¿ä."
    SetStyleHint wsS, "G21", "¾÷µ¥ÀÌÆ®°¡ ÀÖ´Ù¸é ÀÚµ¿/¼öµ¿ Áß ¼±ÅÃÇÒ ¼ö ÀÖ½À´Ï´Ù."
    SetStyleHint wsS, "G22", "¾÷µ¥ÀÌÆ® ÈÄ [¼³Á¤ ÃʱâÈ­]¸¦ ´­·¯ÁÖ¼¼¿ä."
    SetStyleHint wsS, "G23", "ÇöÀç ¹öÀü: v" & MACRO_VERSION

    ' --- µî·Ï ÀÌ·Â ---
    SetStyleSection wsS, "A28", "[ µî·Ï ÀÌ·Â ]"
    Dim hdrTexts As Variant
    hdrTexts = Array("requestId", "µî·Ï°Ç¼ö", "µî·Ï½Ã°¢", "»óÅÂ")
    Dim hc As Long
    For hc = 0 To 3
        With wsS.Cells(29, hc + 1)
            .Value = hdrTexts(hc)
            .Font.Name = "¸¼Àº °íµñ"
            .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

    ' ========================================
    ' Åùè»çÄÚµå ¸ñ·Ï ½ÃÆ® »ý¼º
    ' ========================================
    Set wsC = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsC.Name = SETUP_COURIER_SHEET_NAME

    wsC.Columns("A").ColumnWidth = 18
    wsC.Columns("B").ColumnWidth = 24
    wsC.Columns("C").ColumnWidth = 40

    With wsC.Range("A1")
        .Value = "Åùè»çÄÚµå"
        .Font.Name = "¸¼Àº °íµñ"
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(45, 55, 72)
    End With
    With wsC.Range("B1")
        .Value = "Åùè»ç¸í"
        .Font.Name = "¸¼Àº °íµñ"
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(45, 55, 72)
    End With
    With wsC.Range("C1")
        .Value = "»ç¿ë °¡´ÉÇÑ ÀÔ·Â"
        .Font.Name = "¸¼Àº °íµñ"
        .Font.Bold = True
        .Font.Color = RGB(255, 255, 255)
        .Interior.Color = RGB(45, 55, 72)
    End With

    WriteCourierRow wsC, 2, "cj", "CJ´ëÇÑÅë¿î", "´ëÇÑÅë¿î, CJÅùè, CJ´ëÇÑÅë¿î"
    WriteCourierRow wsC, 3, "lotte", "·Ôµ¥Åùè", "·Ôµ¥, ·Ôµ¥Åùè, lotteÅùè"
    WriteCourierRow wsC, 4, "post", "¿ìü±¹Åùè", "¿ìü±¹, ¿ìü±¹Åùè, ¿ìÆí"
    WriteCourierRow wsC, 5, "hanjin", "ÇÑÁøÅùè", "ÇÑÁø, ÇÑÁøÅùè, hanjinÅùè"
    WriteCourierRow wsC, 6, "logen", "·ÎÁ¨Åùè", "·ÎÁ¨, ·ÎÁ¨Åùè, logenÅùè"
    WriteCourierRow wsC, 7, "cu", "CUÆíÀÇÁ¡Åùè", "CUÅùè, CUÆíÀÇÁ¡, CUÆíÀÇÁ¡Åùè"
    WriteCourierRow wsC, 8, "gspost", "GSÆíÀÇÁ¡Åùè", "GSÅùè, GSÆíÀÇÁ¡, GSÆíÀÇÁ¡Åùè"
    WriteCourierRow wsC, 9, "kdexp", "°æµ¿Åùè", "°æµ¿, °æµ¿Åùè, kdexpÅùè"
    WriteCourierRow wsC, 10, "daesin", "´ë½ÅÅùè", "´ë½Å, ´ë½ÅÅùè, daesinÅùè"
    WriteCourierRow wsC, 11, "ilyang", "ÀϾç·ÎÁö½º", "ÀϾç, ÀϾç·ÎÁö½º, ilyangÅùè"
    WriteCourierRow wsC, 12, "chunil", "õÀÏÅùè", "õÀÏ, õÀÏÅùè, chunilÅùè"
    WriteCourierRow wsC, 13, "homebandi", "Ȩ¹êµðÅùè", "Ȩ¹êµð, Ȩ¹êµðÅùè, homebandiÅùè"
    WriteCourierRow wsC, 14, "iparcel", "i-parcel", "i-parcel, ¾ÆÀÌÆļ¿"
    WriteCourierRow wsC, 15, "ems", "EMS", "±¹Á¦¿ìÆí"


    ' ========================================
    ' »ç¿ë¹ý ½ÃÆ® »ý¼º
    ' ========================================
    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, "[ Åùè Á¶È¸ ¸ÅÅ©·Î »ç¿ë¹ý ]"
    WriteUsageLine wsU, 3, "1. Ãʱ⠼³Á¤ (¼³Á¤ ½ÃÆ®)"
    WriteUsageLine wsU, 4, "   - API Key, Secret Key ÀÔ·Â"
    WriteUsageLine wsU, 5, "   - ´ë»ó ½ÃÆ®¸í, Åùè»çÄÚµå ¿­, ¼ÛÀå¹øÈ£ ¿­, °á°ú ½ÃÀÛ ¿­ ¼³Á¤"
    WriteUsageLine wsU, 6, "   - Ÿ°Ù: Àüü ÃßÀû / °á°ú ¾ø´Â Çุ"
    WriteUsageLine wsU, 7, "   - ¿µ¿ª: ¸¶¿ì½º ¼±Åà / Àüü / ƯÁ¤ ¿µ¿ª"
    WriteUsageLine wsU, 9, "2. VBA ¸ÅÅ©·Î ¼³Á¤"
    WriteUsageLine wsU, 10, "   - Alt+F11 > ÆÄÀÏ > ÆÄÀÏ °¡Á®¿À±â > TrackingMacro.bas"
    WriteUsageLine wsU, 11, "   - .xlsm Çü½ÄÀ¸·Î ÀúÀå"
    WriteUsageLine wsU, 13, "3. Ãʱ⠼³Á¤ (ÃÖÃÊ 1ȸ)"
    WriteUsageLine wsU, 14, "   - Alt+F8 > SetupAll ½ÇÇà"
    WriteUsageLine wsU, 15, "   - ¼³Á¤ ½ÃÆ® + Åùè»çÄÚµå ¸ñ·Ï + »ç¿ë¹ý + ¹öÆ° ÀÚµ¿ »ý¼º"
    WriteUsageLine wsU, 17, "4. Á¶È¸ µî·Ï (TrackRegister)"
    WriteUsageLine wsU, 18, "   - Alt+F8 > TrackRegister ¶Ç´Â ¹öÆ° Ŭ¸¯"
    WriteUsageLine wsU, 19, "   - 100°Ç¾¿ ³ª´©¾î ÀÚµ¿ µî·Ï"
    WriteUsageLine wsU, 20, "   - ¼³Á¤ ½ÃÆ® ÇÏ´Ü¿¡ requestId ±â·ÏµÊ"
    WriteUsageLine wsU, 22, "5. °á°ú È®ÀÎ (TrackResults)"
    WriteUsageLine wsU, 23, "   - µî·Ï ÈÄ 5~10ÃÊ ´ë±â ±ÇÀå"
    WriteUsageLine wsU, 24, "   - Alt+F8 > TrackResults ¶Ç´Â ¹öÆ° Ŭ¸¯"
    WriteUsageLine wsU, 26, "6. °á°ú ÃʱâÈ­ (ClearResults)"
    WriteUsageLine wsU, 27, "   - °á°ú ¿­À» ¸ðµÎ ºñ¿ò"
    WriteUsageLine wsU, 29, "[ ÁÖÀÇ»çÇ× ]"
    WriteUsageLine wsU, 30, "- µî·Ï°ú °á°ú È®ÀÎ »çÀÌ¿¡ µ¥ÀÌÅÍ ÇàÀ» Ãß°¡/»èÁ¦ÇÏÁö ¸¶¼¼¿ä"
    WriteUsageLine wsU, 31, "- ¿ä±ÝÀÌ ºÎ°úµÉ ¼ö ÀÖÀ¸´Ï ºÒÇÊ¿äÇÑ ¹Ýº¹ µî·ÏÀ» ÇÇÇØÁÖ¼¼¿ä"
    WriteUsageLine wsU, 32, "- ÇÑ ¹ø¿¡ ÃÖ´ë 100°Ç¾¿ µî·ÏµË´Ï´Ù (ÀÚµ¿ ºÐÇÒ)"
    WriteUsageLine wsU, 34, "[ ¼³Á¤ ¾÷µ¥ÀÌÆ® ]"
    WriteUsageLine wsU, 35, "- »õ TrackingMacro.bas¸¦ °¡Á®¿Â ÈÄ SetupAll ½ÇÇà"
    WriteUsageLine wsU, 37, "[ µ¥¸ð Å° ¾È³» ]"
    WriteUsageLine wsU, 38, "- ±âº» ÀÔ·ÂµÈ µ¥¸ð Å°´Â Å×½ºÆ®¿ëÀÌ¸ç ¾ðÁ¦µç Áß´ÜµÉ ¼ö ÀÖ½À´Ï´Ù"
    WriteUsageLine wsU, 39, "- ½ÇÁ¦ ¿î¿µ ½Ã https://www.deliveryapi.co.kr ¿¡¼­ Å°¸¦ ¹ß±Þ¹Þ¾Æ »ç¿ëÇϼ¼¿ä"


    ' --- ±âÁ¸ ½ÃÆ® »èÁ¦ ---
    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

    ' ========================================
    ' ¹öÆ° »ý¼º
    ' ========================================
    wsS.Activate
    SetupButtons wsS

    Application.ScreenUpdating = True

    MsgBox "¼³Á¤ ½ÃÆ® + Åùè»çÄÚµå ¸ñ·Ï + »ç¿ë¹ý + ¹öÆ°ÀÌ »ý¼ºµÇ¾ú½À´Ï´Ù!", _
           vbInformation, "¼³Á¤ ¿Ï·á"
    Exit Sub

SetupError:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "¼³Á¤ Áß ¿À·ù°¡ ¹ß»ýÇß½À´Ï´Ù: " & Err.Description, vbCritical, "¿À·ù"
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 = "¸¼Àº °íµñ"
        .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 = "¸¼Àº °íµñ"
        .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 = "¸¼Àº °íµñ"
        .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 = "¸¼Àº °íµñ"
        .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 = "¸¼Àº °íµñ"
        .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 = "¸¼Àº °íµñ"
        .Font.Size = 10
    End With
End Sub



Private Sub WriteCourierRow(ws As Worksheet, r As Long, code As String, cName As String, Optional aliases As String = "")
    With ws.Cells(r, 1)
        .Value = code
        .Font.Name = "¸¼Àº °íµñ"
        .Font.Size = 10
    End With
    With ws.Cells(r, 2)
        .Value = cName
        .Font.Name = "¸¼Àº °íµñ"
        .Font.Size = 10
    End With
    If aliases <> "" Then
        With ws.Cells(r, 3)
            .Value = aliases
            .Font.Name = "¸¼Àº °íµñ"
            .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 = "¸¼Àº °íµñ"
            .Font.Bold = True
            .Font.Size = 12
            .Font.Color = RGB(45, 55, 72)
        Else
            .Font.Name = "¸¼Àº °íµñ"
            .Font.Size = 10
        End If
    End With
End Sub


'==============================================================================
' PRIVATE: Normalize courier code from various input formats
' e.g. "·Ôµ¥Åùè", "·Ôµ¥", "lotteÅùè" -> "lotte"
'==============================================================================
Private Function NormalizeCourierCode(ByVal raw As String) As String
    Dim s As String
    s = LCase(Trim(raw))

    Select Case s
        Case "cj´ëÇÑÅë¿î", "´ëÇÑÅë¿î", "cjÅùè"
            NormalizeCourierCode = "cj"
        Case "·Ôµ¥Åùè", "·Ôµ¥", "lotteÅùè"
            NormalizeCourierCode = "lotte"
        Case "¿ìü±¹Åùè", "¿ìü±¹", "¿ìÆí"
            NormalizeCourierCode = "post"
        Case "ÇÑÁøÅùè", "ÇÑÁø", "hanjinÅùè"
            NormalizeCourierCode = "hanjin"
        Case "·ÎÁ¨Åùè", "·ÎÁ¨", "logenÅùè"
            NormalizeCourierCode = "logen"
        Case "cuÆíÀÇÁ¡Åùè", "cuÅùè", "cuÆíÀÇÁ¡"
            NormalizeCourierCode = "cu"
        Case "gsÆíÀÇÁ¡Åùè", "gsÅùè", "gsÆíÀÇÁ¡"
            NormalizeCourierCode = "gspost"
        Case "°æµ¿Åùè", "°æµ¿", "kdexpÅùè"
            NormalizeCourierCode = "kdexp"
        Case "´ë½ÅÅùè", "´ë½Å", "daesinÅùè"
            NormalizeCourierCode = "daesin"
        Case "ÀϾç·ÎÁö½º", "ÀϾç", "ilyangÅùè"
            NormalizeCourierCode = "ilyang"
        Case "õÀÏÅùè", "õÀÏ", "chunilÅùè"
            NormalizeCourierCode = "chunil"
        Case "Ȩ¹êµðÅùè", "Ȩ¹êµð", "homebandiÅùè"
            NormalizeCourierCode = "homebandi"
        Case "i-parcel", "¾ÆÀÌÆļ¿"
            NormalizeCourierCode = "iparcel"
        Case "±¹Á¦¿ìÆí"
            NormalizeCourierCode = "ems"
        Case Else
            NormalizeCourierCode = raw
    End Select
End Function


'==============================================================================
' 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 "¹öÀü È®Àο¡ ½ÇÆÐÇß½À´Ï´Ù." & vbCrLf & _
               "ÀÎÅÍ³Ý ¿¬°áÀ» È®ÀÎÇØÁÖ¼¼¿ä.", vbExclamation, "¹öÀü È®ÀÎ"
        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 "ÃֽŠ¹öÀüÀÔ´Ï´Ù! (v" & MACRO_VERSION & ")", vbInformation, "¹öÀü È®ÀÎ"
        Exit Sub
    End If

    MsgBox "»õ ¹öÀüÀÌ ÀÖ½À´Ï´Ù!" & vbCrLf & vbCrLf & _
           "  ÇöÀç ¹öÀü: v" & MACRO_VERSION & vbCrLf & _
           "  ÃֽŠ¹öÀü: v" & serverVersion & vbCrLf & vbCrLf & _
           "¾Æ·¡ 2°¡Áö ¹æ¹ý Áß Æí¸®ÇÑ °ÍÀ» ¼±ÅÃÇÏ¿© ¾÷µ¥ÀÌÆ®ÇØÁÖ¼¼¿ä:" & vbCrLf & vbCrLf & _
           "¹æ¹ý 1) ÀÚµ¿ ¾÷µ¥ÀÌÆ®" & vbCrLf & _
           "  - Alt+F8 > UpdateMacro ½ÇÇà" & vbCrLf & _
           "  - ÃÖÃÊ 1ȸ º¸¾È ¼³Á¤ ÇÊ¿ä:" & vbCrLf & _
           "    ÆÄÀÏ > ¿É¼Ç > º¸¾È ¼¾ÅÍ > º¸¾È ¼¾ÅÍ ¼³Á¤" & vbCrLf & _
           "    > ¸ÅÅ©·Î ¼³Á¤ > VBA ÇÁ·ÎÁ§Æ® °³Ã¼ ¸ðµ¨ ¾×¼¼½º ½Å·Ú üũ" & vbCrLf & vbCrLf & _
           "¹æ¹ý 2) ¼öµ¿ ´Ù¿î·Îµå" & vbCrLf & _
           "  - " & HOMEPAGE_URL & "/tools/excel-tracker ¿¡¼­" & vbCrLf & _
           "    .bas ÆÄÀÏ ´Ù¿î·Îµå" & vbCrLf & _
           "  - Alt+F11 > ±âÁ¸ TrackingMacro »èÁ¦ > ÆÄÀÏ °¡Á®¿À±â" & vbCrLf & vbCrLf & _
           "¡Ø ¾÷µ¥ÀÌÆ® ÈÄ ¹Ýµå½Ã [¼³Á¤ ÃʱâÈ­] ¹öÆ°À» ´­·¯ÁÖ¼¼¿ä.", _
           vbInformation, "¾÷µ¥ÀÌÆ® ¾È³»"
    Exit Sub

ErrHandler:
    MsgBox "¹öÀü È®ÀÎ Áß ¿À·ù: " & Err.Description, vbCritical, "¿À·ù"
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 ÇÁ·ÎÁ§Æ® Á¢±Ù ±ÇÇÑÀÌ ÇÊ¿äÇÕ´Ï´Ù." & vbCrLf & vbCrLf & _
               "¼³Á¤ ¹æ¹ý:" & vbCrLf & _
               "ÆÄÀÏ > ¿É¼Ç > º¸¾È ¼¾ÅÍ > º¸¾È ¼¾ÅÍ ¼³Á¤" & vbCrLf & _
               "> ¸ÅÅ©·Î ¼³Á¤ > ""VBA ÇÁ·ÎÁ§Æ® °³Ã¼ ¸ðµ¨¿¡ ´ëÇÑ" & vbCrLf & _
               "  ¾×¼¼½º¸¦ ½Å·ÚÇÒ ¼ö ÀÖµµ·Ï ¼³Á¤"" üũ" & vbCrLf & vbCrLf & _
               "¼³Á¤ÀÌ ¾î·Á¿ì½Ã¸é [¾Æ´Ï¿À]¸¦ ¼±ÅÃÇÏ¿©" & vbCrLf & _
               "¼öµ¿ ´Ù¿î·Îµå¸¦ ÀÌ¿ëÇØÁÖ¼¼¿ä.", _
               vbExclamation, "±ÇÇÑ ÇÊ¿ä"
        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 "ÆÄÀÏ ´Ù¿î·Îµå¿¡ ½ÇÆÐÇß½À´Ï´Ù.", vbCritical, "¾÷µ¥ÀÌÆ®"
        Exit Sub
    End If

    ' Save to temp (binary to preserve CP949)
    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 "¾÷µ¥ÀÌÆ®°¡ ¿Ï·áµÇ¾ú½À´Ï´Ù!" & vbCrLf & vbCrLf & _
           "[¼³Á¤ ÃʱâÈ­] ¹öÆ°À» ´­·¯ ½ÃÆ®¸¦ °»½ÅÇØÁÖ¼¼¿ä.", vbInformation, "¾÷µ¥ÀÌÆ® ¿Ï·á"
    Exit Sub

ErrHandler:
    MsgBox "¾÷µ¥ÀÌÆ® Áß ¿À·ù: " & Err.Description, vbCritical, "¿À·ù"
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 "´Ù¿î·Îµå¿¡ ½ÇÆÐÇß½À´Ï´Ù.", vbCritical, "´Ù¿î·Îµå"
        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 ¸¦ ´Ù¿î·ÎµåÇß½À´Ï´Ù!" & vbCrLf & vbCrLf & _
           "¼öµ¿ °¡Á®¿À±â ¹æ¹ý:" & vbCrLf & _
           "  1. Alt+F11 (VBA ÆíÁý±â ¿­±â)" & vbCrLf & _
           "  2. ¿ÞÂÊ ¸ñ·Ï¿¡¼­ ±âÁ¸ TrackingMacro ¿ìŬ¸¯ > »èÁ¦" & vbCrLf & _
           "  3. ÆÄÀÏ > ÆÄÀÏ °¡Á®¿À±â > ¹ÙÅÁÈ­¸éÀÇ TrackingMacro.bas" & vbCrLf & vbCrLf & _
           "°¡Á®¿À±â ÈÄ [¼³Á¤ ÃʱâÈ­]¸¦ ½ÇÇàÇØÁÖ¼¼¿ä.", vbInformation, "´Ù¿î·Îµå ¿Ï·á"
    Exit Sub

ErrHandler:
    MsgBox "´Ù¿î·Îµå Áß ¿À·ù: " & Err.Description, vbCritical, "¿À·ù"
End Sub


' 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