Attribute VB_Name = "modImport"
Option Compare Database
Option Explicit

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function RunImport() As Long
    Dim filePath As String
    Dim batchID As Long
    Dim isBankFile As Boolean
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlSheet As Object
    Dim r As Long
    Dim lastRow As Long
    Dim rowCount As Long
    Dim errCount As Long
    Dim db As DAO.Database
    Dim fileName As String
    Dim attempt As Integer
    Const MAX_ATTEMPTS As Integer = 3

    filePath = Forms!frmImport!txtFilePath.Value
    fileName = Dir(filePath)
    isBankFile = (InStr(1, fileName, "BankStatement", vbTextCompare) > 0)

    Set db = CurrentDb()
    batchID = modUtil.GetNextBatchNumber()

    attempt = 0
    Do
        attempt = attempt + 1
        On Error Resume Next
        Err.Clear
        Set xlApp = CreateObject("Excel.Application")
        xlApp.Visible = False
        Set xlWb = xlApp.Workbooks.Open(filePath, ReadOnly:=True)
        On Error GoTo 0
        If Err.Number = 70 And attempt < MAX_ATTEMPTS Then
            Set xlWb = Nothing
            xlApp.Quit
            Set xlApp = Nothing
            Sleep 2000
        Else
            Exit Do
        End If
    Loop

    If xlWb Is Nothing Then
        modUtil.LogError batchID, 0, "Could not open file after " & MAX_ATTEMPTS & " attempts: " & filePath
        Forms!frmImport!lblStatus.Caption = "Import failed: file locked."
        RunImport = batchID
        Exit Function
    End If

    Set xlSheet = xlWb.Sheets(1)
    ' Column 2 (TxnDate), not column 1: column 1 (Branch) is intentionally blank
    ' in BankStatement files (no branch applies to bank rows), so scanning column
    ' 1 for the last used row finds nothing and lastRow ends up < 2, skipping the
    ' import loop entirely. TxnDate is always populated in both file types.
    lastRow = xlSheet.Cells(xlSheet.Rows.Count, 2).End(-4162).Row ' -4162 = xlUp (late-bound)
    rowCount = 0
    errCount = 0

    For r = 2 To lastRow
        Dim t As clsTransaction
        Set t = New clsTransaction
        t.TransID = 0
        t.TxnDate = xlSheet.Cells(r, 2).Value
        t.Amount = xlSheet.Cells(r, 3).Value
        t.RefNo = CStr(xlSheet.Cells(r, 4).Value)
        t.Branch = IIf(isBankFile, "BANK", xlSheet.Cells(r, 1).Value)

        If Not t.IsValid() Then
            errCount = errCount + 1
            modUtil.LogError batchID, r, "Invalid row: Amount/RefNo/TxnDate failed validation"
        Else
            If isBankFile Then
                db.Execute "INSERT INTO tblBankStatement (TxnDate, Amount, BankRefNo, ImportBatchID) VALUES (" & _
                           modUtil.FormatDateForSql(t.TxnDate) & ", " & modUtil.FormatNumberForSql(t.Amount) & ", '" & _
                           Replace(t.RefNo, "'", "''") & "', " & batchID & ")"
            Else
                db.Execute "INSERT INTO tblTransactions (Branch, TxnDate, Amount, RefNo, ImportBatchID) VALUES ('" & _
                           t.Branch & "', " & modUtil.FormatDateForSql(t.TxnDate) & ", " & modUtil.FormatNumberForSql(t.Amount) & ", '" & _
                           Replace(t.RefNo, "'", "''") & "', " & batchID & ")"
            End If
            rowCount = rowCount + 1
        End If
    Next r

    xlWb.Close SaveChanges:=False
    xlApp.Quit
    Set xlWb = Nothing
    Set xlApp = Nothing

    db.Execute "INSERT INTO tblImportBatch (FileName, ImportDate, [RowCount], [Status]) VALUES ('" & _
               Replace(fileName, "'", "''") & "', Now(), " & rowCount & ", 'Imported')"

    modUtil.gCurrentBatchID = batchID
    Forms!frmImport!lblStatus.Caption = "Imported " & rowCount & " rows, " & errCount & " errors. Batch #" & batchID
    RunImport = batchID
    Set db = Nothing
End Function

Public Sub BrowseForFile()
    Dim fd As Object
    Set fd = Application.FileDialog(3) ' msoFileDialogFilePicker
    fd.Filters.Clear
    fd.Filters.Add "Excel Files", "*.xlsx"
    If fd.Show = -1 Then
        Forms!frmImport!txtFilePath.Value = fd.SelectedItems(1)
    End If
    Set fd = Nothing
End Sub