Attribute VB_Name = "IqbalCRM"
' =============================================================
' Iqbal CRM - Macro de sincronizacion Excel -> Plataforma
' =============================================================
'
' INSTRUCCIONES DE USO
' --------------------
' 1. Abre tu libro maestro (ventas o inventarios).
' 2. Activa pestaña Desarrollador -> Visual Basic.
' 3. Importa este archivo .bas (File > Import File).
' 4. Configura la constante API_BASE con la URL de tu CRM.
'    Ejemplo:  https://iqbal-crm.totalum.app
' 5. La primera hoja debe llamarse "Ventas" o "Inventario" segun
'    el tipo de carga, con los encabezados de la plantilla.
' 6. Asigna las macros a un botón de cinta:
'       - PushVentas   -> sube las filas de la hoja "Ventas"
'       - PushInventario -> sube las filas de la hoja "Inventario"
'
' La macro empaqueta cada fila como JSON y la envía al endpoint
' /api/import. El CRM responde con cantidad insertada y errores.
' =============================================================

Option Explicit

Private Const API_BASE As String = "https://TU-DOMINIO.totalum.app"
Private Const IMPORT_PATH As String = "/api/import"

' ---- Botón 1: Subir ventas del mes ----
Public Sub PushVentas()
    PushSheet "Ventas", "sales", Array( _
        "year", "month", "client_code", "product_code", _
        "quantity", "sale_value", "sale_price")
End Sub

' ---- Botón 2: Subir inventario / lotes ----
Public Sub PushInventario()
    PushSheet "Inventario", "inventory", Array( _
        "product_code", "lot", "expiration_date", _
        "stock_available", "unit_cost", "total_cost", "warehouse")
End Sub

' ---- Núcleo: lee hoja, arma JSON y POSTea al CRM ----
Private Sub PushSheet(ByVal sheetName As String, ByVal importType As String, ByVal headers As Variant)
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(sheetName)
    On Error GoTo 0
    If ws Is Nothing Then
        MsgBox "No encuentro la hoja '" & sheetName & "'. Renombra tu pestaña.", vbCritical
        Exit Sub
    End If

    Dim lastRow As Long, lastCol As Long, i As Long, j As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = UBound(headers) - LBound(headers) + 1

    If lastRow < 2 Then
        MsgBox "La hoja '" & sheetName & "' no tiene filas de datos.", vbExclamation
        Exit Sub
    End If

    Dim rowsJson As String
    rowsJson = "["

    Dim total As Long
    total = 0

    For i = 2 To lastRow
        Dim rowJson As String
        rowJson = "{"
        Dim hasValue As Boolean: hasValue = False

        For j = 0 To UBound(headers)
            Dim header As String, valStr As String, raw As Variant
            header = CStr(headers(j))
            raw = ws.Cells(i, j + 1).Value
            valStr = SafeValue(raw)
            If valStr <> """""" And valStr <> "" Then hasValue = True

            rowJson = rowJson & """" & header & """:" & valStr
            If j < UBound(headers) Then rowJson = rowJson & ","
        Next j

        rowJson = rowJson & "}"
        If hasValue Then
            If total > 0 Then rowsJson = rowsJson & ","
            rowsJson = rowsJson & rowJson
            total = total + 1
        End If
    Next i

    rowsJson = rowsJson & "]"

    If total = 0 Then
        MsgBox "No hay filas con datos para enviar.", vbExclamation
        Exit Sub
    End If

    Dim payload As String
    payload = "{""type"":""" & importType & """,""rows"":" & rowsJson & "}"

    Dim url As String
    url = API_BASE & IMPORT_PATH

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "POST", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.send payload

    If http.Status <> 200 Then
        MsgBox "Error HTTP " & http.Status & ": " & vbCrLf & http.responseText, vbCritical
        Exit Sub
    End If

    Dim respText As String
    respText = http.responseText

    Dim insertedCount As Long, errCount As Long
    insertedCount = ExtractIntField(respText, """inserted"":")
    errCount = CountOccurrences(respText, """errors"":")

    MsgBox "Importacion lista." & vbCrLf & _
           "Filas enviadas: " & total & vbCrLf & _
           "Insertadas:     " & insertedCount & vbCrLf & _
           "Tipo:           " & importType, vbInformation, "Iqbal CRM"
End Sub

' ---- Conversion segura a JSON value ----
Private Function SafeValue(ByVal v As Variant) As String
    If IsEmpty(v) Or IsNull(v) Then
        SafeValue = """"""
        Exit Function
    End If

    If IsNumeric(v) Then
        SafeValue = Replace(CStr(v), ",", ".")
        Exit Function
    End If

    If IsDate(v) Then
        SafeValue = """" & Format(v, "yyyy-mm-dd") & """"
        Exit Function
    End If

    Dim s As String
    s = CStr(v)
    s = Replace(s, "\", "\\")
    s = Replace(s, """", "\""")
    s = Replace(s, vbCrLf, " ")
    s = Replace(s, vbLf, " ")
    s = Replace(s, vbCr, " ")
    SafeValue = """" & s & """"
End Function

Private Function ExtractIntField(ByVal text As String, ByVal field As String) As Long
    Dim p As Long, q As Long
    p = InStr(1, text, field)
    If p = 0 Then Exit Function
    p = p + Len(field)
    q = p
    Do While q <= Len(text) And InStr("0123456789-", Mid$(text, q, 1)) > 0
        q = q + 1
    Loop
    If q > p Then ExtractIntField = CLng(Mid$(text, p, q - p))
End Function

Private Function CountOccurrences(ByVal text As String, ByVal needle As String) As Long
    Dim p As Long, c As Long
    p = 1
    Do
        p = InStr(p, text, needle)
        If p = 0 Then Exit Do
        c = c + 1
        p = p + Len(needle)
    Loop
    CountOccurrences = c
End Function