using Lada.Framework.Data.Domains.Finance;
using Lada.Framework.Data.Domains.Trucks;
using Lada.Framework.Data.Enums;
using Lada.Framework.Data.Repositories;
using Lada.Framework.DTO;
using Lada.Framework.DTO.SmartTable;
using Lada.Framework.Infrastructures;
using Lada.Framework.Infrastructures.Helpers;
using Ladaer.BackEnd.Infrastructures.DTO.Items.FuelCards;
using Ladaer.BackEnd.Infrastructures.DTO.Models.FuelCards;
using Ladaer.BackEnd.Infrastructures.DTO.Profiles;
using Ladaer.BackEnd.Infrastructures.DTO.Results.FuelCards;
using Microsoft.EntityFrameworkCore;
using OfficeOpenXml;
using System.Globalization;

namespace Ladaer.BackEnd.Infrastructures.Services.FuelCards;

/// <summary>
/// Interface cho service quản lý giao dịch thẻ tín dụng xăng dầu
/// </summary>
public interface IFuelCardTransactionService
{
    /// <summary>
    /// Import sao kê thẻ tín dụng từ Excel
    /// </summary>
    Task<BaseResult> ImportStatementAsync(FuelCardTransactionImportModel model, CurrentUser currentUser);

    /// <summary>
    /// Tìm kiếm giao dịch thẻ
    /// </summary>
    Task<SmartTableResult<FuelCardTransactionItem>> Search(SmartTableParam param, int? fuelCardId = null);

    /// <summary>
    /// Lấy chi tiết giao dịch
    /// </summary>
    Task<FuelCardTransactionItem?> GetByIdAsync(Guid id);

    /// <summary>
    /// Đối soát giao dịch thẻ với lần đổ dầu
    /// </summary>
    Task<BaseResult> ReconcileWithRefillAsync(Guid transactionId, int fuelRefillId, CurrentUser currentUser);

    /// <summary>
    /// Đánh dấu đã thanh toán cho ngân hàng
    /// </summary>
    Task<BaseResult> MarkAsPaidToBankAsync(Guid transactionId, CurrentUser currentUser);

    /// <summary>
    /// Import lịch sử giao dịch thẻ xăng dầu từ Excel.
    /// Tự động mapping thẻ dựa vào số thẻ trong file.
    /// </summary>
    Task<FuelCardTransactionImportResult> ImportTransactionHistoryAsync(FuelCardTransactionHistoryImportModel model, CurrentUser currentUser);

    /// <summary>
    /// Lấy danh sách giao dịch chưa đối soát theo thẻ và ngày
    /// </summary>
    Task<List<FuelCardTransactionItem>> GetUnreconciledTransactionsByCardAndDateAsync(int fuelCardId, DateTimeOffset date);
}

/// <summary>
/// Service quản lý giao dịch thẻ tín dụng xăng dầu
/// </summary>
public class FuelCardTransactionService : IFuelCardTransactionService
{
    private readonly IRepository<FuelCardTransaction> _transactionRepository;
    private readonly IRepository<FuelCardStatementImport> _importRepository;
    private readonly IRepository<FuelCard> _fuelCardRepository;
    private readonly IRepository<FuelRefill> _fuelRefillRepository;

    public FuelCardTransactionService(
        IRepository<FuelCardTransaction> transactionRepository,
        IRepository<FuelCardStatementImport> importRepository,
        IRepository<FuelCard> fuelCardRepository,
        IRepository<FuelRefill> fuelRefillRepository)
    {
        _transactionRepository = transactionRepository;
        _importRepository = importRepository;
        _fuelCardRepository = fuelCardRepository;
        _fuelRefillRepository = fuelRefillRepository;
    }

    public async Task<BaseResult> ImportStatementAsync(FuelCardTransactionImportModel model, CurrentUser currentUser)
    {
        var result = new BaseResult();

        try
        {
            // Validate fuel card exists
            var fuelCard = await _fuelCardRepository.Query()
                .FirstOrDefaultAsync(x => x.Id == model.FuelCardId && !x.IsDeleted);

            if (fuelCard == null)
            {
                result.Message = "Không tìm thấy thẻ xăng dầu";
                return result;
            }

            // TODO: Implement Excel parsing logic
            // Similar to BankStatementParser
            // 1. Read Excel file
            // 2. Parse rows (format: Tài khoản thẻ, Số tiền, Loại tiền, Loại giao dịch, Địa điểm, Tình trạng)
            // 3. Create FuelCardStatementImport
            // 4. Create FuelCardTransaction for each row
            // 5. Update FuelCard.CurrentDebt

            result.Result = Result.Failed;
            result.Message = "Chức năng import sao kê đang được phát triển";
        }
        catch (Exception ex)
        {
            result.Message = $"Lỗi: {ex.Message}";
        }

        return result;
    }

    public async Task<SmartTableResult<FuelCardTransactionItem>> Search(SmartTableParam param, int? fuelCardId = null)
    {
        param ??= new SmartTableParam();

        var query = _transactionRepository.Query()
            .Include(x => x.FuelCard)
            .Where(x => !x.IsDeleted);

        if (fuelCardId.HasValue)
        {
            query = query.Where(x => x.FuelCardId == fuelCardId.Value);
        }

        // Search
        if (param.Search?.PredicateObject != null)
        {
            dynamic search = param.Search.PredicateObject;
            if (search.Keyword != null)
            {
                string keyword = search.Keyword.ToString().ToLower().Trim();
                if (!string.IsNullOrEmpty(keyword))
                {
                    query = query.Where(x =>
                        x.FuelCard.CardNumber.ToLower().Contains(keyword) ||
                        x.Location.ToLower().Contains(keyword));
                }
            }

            if (search.TransactionStart != null && search.TransactionEnd != null)
            {
                DateTimeOffset start = search.TransactionStart;
                DateTimeOffset end = search.TransactionEnd;
                DateTimeOffset startParam = start.ToLocalTime().StartOfDay();
                DateTimeOffset endParam = end.ToLocalTime().EndOfDay();
                query = query.Where(x => x.TransactionDate >= startParam && x.TransactionDate <= endParam);
            }

            // Filter by HasRefill (đã đổ dầu hay chưa)
            if (search.HasRefill != null)
            {
                bool hasRefill = search.HasRefill;
                if (hasRefill)
                {
                    // Lọc các giao dịch đã có đổ dầu (FuelRefillId > 0)
                    query = query.Where(x => x.FuelRefillId.HasValue && x.FuelRefillId.Value > 0);
                }
                else
                {
                    // Lọc các giao dịch chưa có đổ dầu (FuelRefillId == null hoặc == 0)
                    query = query.Where(x => !x.FuelRefillId.HasValue || x.FuelRefillId.Value == 0);
                }
            }
        }

        // Sorting
        param.Sort = new Sort() { Predicate = "TransactionDate", Reverse = true };
        
        // Use ToSmartTableResultAsync with ToItem() extension method
        var gridData = await query.ToSmartTableResultAsync(param, x => x.ToItem());

        return gridData;
    }

    public async Task<FuelCardTransactionItem?> GetByIdAsync(Guid id)
    {
        var entity = await _transactionRepository.Query()
            .Include(x => x.FuelCard)
            .FirstOrDefaultAsync(x => x.Id == id && !x.IsDeleted);

        return entity?.ToItem();
    }

    public async Task<BaseResult> ReconcileWithRefillAsync(Guid transactionId, int fuelRefillId, CurrentUser currentUser)
    {
        var result = new BaseResult();

        try
        {
            var transaction = await _transactionRepository.Query()
                .FirstOrDefaultAsync(x => x.Id == transactionId && !x.IsDeleted);

            if (transaction == null)
            {
                result.Message = "Không tìm thấy giao dịch";
                return result;
            }

            var fuelRefill = await _fuelRefillRepository.Query()
                .FirstOrDefaultAsync(x => x.Id == fuelRefillId && !x.IsDeleted);

            if (fuelRefill == null)
            {
                result.Message = "Không tìm thấy lần đổ dầu";
                return result;
            }

            transaction.FuelRefillId = fuelRefillId;
            transaction.IsReconciled = true;
            transaction.UpdatedTime = DateTimeOffset.Now;
            transaction.UpdatedBy = currentUser.UserId;
            transaction.UpdatedByUserName = currentUser.UserName;

            fuelRefill.FuelCardTransactionId = transactionId;
            fuelRefill.UpdatedTime = DateTimeOffset.Now;
            fuelRefill.UpdatedBy = currentUser.UserId;
            fuelRefill.UpdatedByUserName = currentUser.UserName;

            await _transactionRepository.UpdateAsync(transaction);
            await _fuelRefillRepository.UpdateAsync(fuelRefill);

            result.Result = Result.Success;
            result.Message = "Đối soát thành công";
        }
        catch (Exception ex)
        {
            result.Message = $"Lỗi: {ex.Message}";
        }

        return result;
    }

    public async Task<BaseResult> MarkAsPaidToBankAsync(Guid transactionId, CurrentUser currentUser)
    {
        var result = new BaseResult();

        try
        {
            var transaction = await _transactionRepository.Query()
                .Include(x => x.FuelCard)
                .FirstOrDefaultAsync(x => x.Id == transactionId && !x.IsDeleted);

            if (transaction == null)
            {
                result.Message = "Không tìm thấy giao dịch";
                return result;
            }

            if (transaction.IsPaidToBank)
            {
                result.Message = "Giao dịch đã được đánh dấu thanh toán";
                return result;
            }

            transaction.IsPaidToBank = true;
            transaction.PaidToBank = DateTimeOffset.Now;
            transaction.UpdatedTime = DateTimeOffset.Now;
            transaction.UpdatedBy = currentUser.UserId;
            transaction.UpdatedByUserName = currentUser.UserName;

            await _transactionRepository.UpdateAsync(transaction);

            // Update card debt
            var card = transaction.FuelCard;
            if (card != null)
            {
                card.CurrentDebt -= transaction.Amount;
                if (card.CurrentDebt < 0) card.CurrentDebt = 0;
                card.UpdatedTime = DateTimeOffset.Now;
                card.UpdatedBy = currentUser.UserId;
                card.UpdatedByUserName = currentUser.UserName;

                await _fuelCardRepository.UpdateAsync(card);
            }

            result.Result = Result.Success;
            result.Message = "Đánh dấu đã thanh toán thành công";
        }
        catch (Exception ex)
        {
            result.Message = $"Lỗi: {ex.Message}";
        }

        return result;
    }

    /// <summary>
    /// Import lịch sử giao dịch thẻ xăng dầu từ file Excel.
    /// Tự động mapping thẻ dựa vào cột "Tài khoản thẻ/ Số thẻ".
    /// </summary>
    public async Task<FuelCardTransactionImportResult> ImportTransactionHistoryAsync(
        FuelCardTransactionHistoryImportModel model,
        CurrentUser currentUser)
    {
        var result = new FuelCardTransactionImportResult();

        try
        {
            // Load all active fuel cards for mapping
            var allCards = await _fuelCardRepository.Query()
                .Where(x => !x.IsDeleted)
                .ToListAsync();

            // Create a dictionary for fast lookup by card number
            var cardLookup = new Dictionary<string, FuelCard>(StringComparer.OrdinalIgnoreCase);
            foreach (var card in allCards)
            {
                // Normalize card number for lookup (remove spaces, dashes)
                var normalizedNumber = NormalizeCardNumber(card.CardNumber);
                if (!cardLookup.ContainsKey(normalizedNumber))
                {
                    cardLookup[normalizedNumber] = card;
                }
            }

            using var stream = model.File.OpenReadStream();
            using var package = new ExcelPackage(stream);

            if (package.Workbook.Worksheets.Count == 0)
            {
                result.Message = "File Excel không có worksheet nào";
                return result;
            }

            var worksheet = package.Workbook.Worksheets[0];
            var endRow = worksheet.Dimension?.End.Row ?? 0;
            var endCol = worksheet.Dimension?.End.Column ?? 0;

            if (endRow < 2)
            {
                result.Message = "File Excel không có dữ liệu";
                return result;
            }

            // Find column indices from header row
            var columnMap = MapColumnIndices(worksheet, endCol);

            if (columnMap.CardNumberCol == 0)
            {
                result.Message = "Không tìm thấy cột 'Tài khoản thẻ' hoặc 'Số thẻ' trong file Excel";
                return result;
            }

            // Track cards that need debt update
            var cardsToUpdate = new Dictionary<int, FuelCard>();
            var unknownCards = new HashSet<string>();

            // Process each row starting from row 2 (skip header)
            for (int row = 2; row <= endRow; row++)
            {
                try
                {
                    var cardNumberText = worksheet.Cells[row, columnMap.CardNumberCol].Text?.Trim();

                    // Skip empty rows
                    if (string.IsNullOrEmpty(cardNumberText))
                    {
                        result.SkippedRows++;
                        continue;
                    }

                    // Normalize and lookup card
                    var normalizedCardNumber = NormalizeCardNumber(cardNumberText);
                    if (!cardLookup.TryGetValue(normalizedCardNumber, out var fuelCard))
                    {
                        // Try partial match (last 4 digits or masked format)
                        fuelCard = FindCardByPartialMatch(allCards, cardNumberText);
                    }

                    if (fuelCard == null)
                    {
                        unknownCards.Add(cardNumberText);
                        result.SkippedRows++;
                        result.Warnings.Add($"Dòng {row}: Không tìm thấy thẻ '{cardNumberText}' trong hệ thống");
                        continue;
                    }

                    // Parse transaction date
                    var transactionDate = ParseTransactionDate(worksheet, row, columnMap);
                    if (transactionDate == null)
                    {
                        result.ErrorRows++;
                        result.ErrorMessages.Add($"Dòng {row}: Không thể parse ngày giao dịch");
                        continue;
                    }

                    // Filter by date range if specified
                    if (model.FromDate.HasValue && transactionDate < model.FromDate.Value)
                    {
                        result.SkippedRows++;
                        continue;
                    }
                    if (model.ToDate.HasValue && transactionDate > model.ToDate.Value)
                    {
                        result.SkippedRows++;
                        continue;
                    }

                    // Parse amount
                    var rawAmount = ParseAmount(worksheet, row, columnMap);
                    var amount = Math.Abs(rawAmount);

                    // Parse other fields
                    var currency = columnMap.CurrencyCol > 0
                        ? worksheet.Cells[row, columnMap.CurrencyCol].Text?.Trim() ?? "VND"
                        : "VND";
                    var transactionType = columnMap.TransactionTypeCol > 0
                        ? worksheet.Cells[row, columnMap.TransactionTypeCol].Text?.Trim() ?? ""
                        : "";
                    var location = columnMap.LocationCol > 0
                        ? worksheet.Cells[row, columnMap.LocationCol].Text?.Trim() ?? ""
                        : "";
                    var statusText = columnMap.StatusCol > 0
                        ? worksheet.Cells[row, columnMap.StatusCol].Text?.Trim() ?? ""
                        : "";

                    // Build raw data for reference
                    var rawData = BuildRawData(worksheet, row, endCol);

                    // Check for duplicate
                    var exists = await _transactionRepository.Query()
                        .AnyAsync(x => x.FuelCardId == fuelCard.Id &&
                                      x.TransactionDate.Date == transactionDate.Value.Date &&
                                      x.RawAmount == rawAmount &&
                                      !x.IsDeleted);

                    if (exists && !model.OverwriteExisting)
                    {
                        result.DuplicateRows++;
                        continue;
                    }

                    // Create transaction
                    var transaction = new FuelCardTransaction
                    {
                        Id = Guid.NewGuid(),
                        FuelCardId = fuelCard.Id,
                        TransactionDate = transactionDate.Value,
                        Amount = amount,
                        RawAmount = rawAmount,
                        Currency = string.IsNullOrEmpty(currency) ? "VND" : currency,
                        TransactionType = TruncateString(transactionType, 50),
                        Location = TruncateString(location, 512),
                        Status = ParseTransactionStatus(statusText),
                        RawData = TruncateString(rawData, 4000),
                        ImportRowNumber = row,
                        IsReconciled = false,
                        IsPaidToBank = false,
                        CreatedTime = DateTimeOffset.Now,
                        UpdatedTime = DateTimeOffset.Now,
                        CreatedBy = currentUser.UserId,
                        CreatedByUserName = currentUser.UserName,
                        UpdatedBy = currentUser.UserId,
                        UpdatedByUserName = currentUser.UserName
                    };

                    if (exists && model.OverwriteExisting)
                    {
                        // Find and update existing transaction
                        var existing = await _transactionRepository.Query()
                            .FirstOrDefaultAsync(x => x.FuelCardId == fuelCard.Id &&
                                                     x.TransactionDate.Date == transactionDate.Value.Date &&
                                                     x.RawAmount == rawAmount &&
                                                     !x.IsDeleted);
                        if (existing != null)
                        {
                            existing.Amount = amount;
                            existing.Currency = transaction.Currency;
                            existing.TransactionType = transaction.TransactionType;
                            existing.Location = transaction.Location;
                            existing.Status = transaction.Status;
                            existing.RawData = transaction.RawData;
                            existing.UpdatedTime = DateTimeOffset.Now;
                            existing.UpdatedBy = currentUser.UserId;
                            existing.UpdatedByUserName = currentUser.UserName;

                            await _transactionRepository.UpdateAsync(existing);
                        }
                    }
                    else
                    {
                        await _transactionRepository.InsertAsync(transaction);

                        // Track card for debt update
                        if (!cardsToUpdate.ContainsKey(fuelCard.Id))
                        {
                            cardsToUpdate[fuelCard.Id] = fuelCard;
                        }
                        cardsToUpdate[fuelCard.Id].CurrentDebt += amount;
                    }

                    result.ProcessedRows++;
                    result.TotalAmountImported += amount;
                }
                catch (Exception ex)
                {
                    result.ErrorRows++;
                    result.ErrorMessages.Add($"Dòng {row}: {ex.Message}");
                }

                result.TotalRows++;
            }

            // Update card debts
            foreach (var card in cardsToUpdate.Values)
            {
                card.UpdatedTime = DateTimeOffset.Now;
                card.UpdatedBy = currentUser.UserId;
                card.UpdatedByUserName = currentUser.UserName;
                await _fuelCardRepository.UpdateAsync(card);
            }

            result.UnknownCards = unknownCards.ToList();
            result.UnknownCardsCount = unknownCards.Count;

            result.Result = Result.Success;
            result.Message = $"Import thành công: {result.ProcessedRows}/{result.TotalRows} giao dịch";

            if (result.UnknownCardsCount > 0)
            {
                result.Message += $". Có {result.UnknownCardsCount} số thẻ không tìm thấy trong hệ thống.";
            }
        }
        catch (Exception ex)
        {
            result.Result = Result.Failed;
            result.Message = $"Lỗi khi import: {ex.Message}";
        }

        return result;
    }

    #region Excel Parsing Helpers

    private class ColumnMapping
    {
        public int CardNumberCol { get; set; }
        public int DateCol { get; set; }
        public int AmountCol { get; set; }
        public int CurrencyCol { get; set; }
        public int TransactionTypeCol { get; set; }
        public int LocationCol { get; set; }
        public int StatusCol { get; set; }
    }

    private ColumnMapping MapColumnIndices(ExcelWorksheet worksheet, int endCol)
    {
        var map = new ColumnMapping();

        for (int col = 1; col <= endCol; col++)
        {
            var header = worksheet.Cells[1, col].Text?.ToLower().Trim() ?? "";

            if (header.Contains("tài khoản thẻ") || header.Contains("số thẻ") || header.Contains("card number"))
                map.CardNumberCol = col;
            else if (header.Contains("ngày") || header.Contains("date"))
                map.DateCol = col;
            else if (header.Contains("loại tiền") || header.Contains("currency"))
                map.CurrencyCol = col;
            else if (header.Contains("số tiền") || header.Contains("amount") || header.Contains("thành tiền") || header.Contains("giá trị"))
                map.AmountCol = col;
            else if (header.Contains("loại giao dịch") || header.Contains("transaction type") || header.Contains("loại"))
                map.TransactionTypeCol = col;
            else if (header.Contains("địa điểm") || header.Contains("location") || header.Contains("nơi"))
                map.LocationCol = col;
            else if (header.Contains("tình trạng") || header.Contains("status") || header.Contains("trạng thái"))
                map.StatusCol = col;
        }

        return map;
    }

    private string NormalizeCardNumber(string cardNumber)
    {
        if (string.IsNullOrEmpty(cardNumber)) return "";

        // Remove spaces, dashes, underscores, and other separators
        return cardNumber.Replace(" ", "")
                         .Replace("-", "")
                         .Replace("_", "")
                         .Replace("*", "")
                         .Trim();
    }

    private FuelCard? FindCardByPartialMatch(List<FuelCard> cards, string inputCardNumber)
    {
        var normalized = NormalizeCardNumber(inputCardNumber);
        if (string.IsNullOrEmpty(normalized)) return null;

        // Try exact match first
        foreach (var card in cards)
        {
            var cardNormalized = NormalizeCardNumber(card.CardNumber);
            if (cardNormalized == normalized)
                return card;
        }

        // Try matching last 4 digits
        if (normalized.Length >= 4)
        {
            var last4 = normalized.Substring(normalized.Length - 4);
            foreach (var card in cards)
            {
                var cardNormalized = NormalizeCardNumber(card.CardNumber);
                if (cardNormalized.EndsWith(last4))
                    return card;
            }
        }

        // Try contains match (for masked numbers like 5234****5261)
        foreach (var card in cards)
        {
            var cardNormalized = NormalizeCardNumber(card.CardNumber);
            if (cardNormalized.Contains(normalized) || normalized.Contains(cardNormalized))
                return card;
        }

        return null;
    }

    private DateTimeOffset? ParseTransactionDate(ExcelWorksheet worksheet, int row, ColumnMapping columnMap)
    {
        if (columnMap.DateCol == 0) return DateTimeOffset.Now;

        var cell = worksheet.Cells[row, columnMap.DateCol];

        // Try to get as DateTime directly (Excel stores dates as numbers)
        if (cell.Value is DateTime dt)
        {
            return new DateTimeOffset(dt);
        }

        if (cell.Value is double excelDate)
        {
            try
            {
                var date = DateTime.FromOADate(excelDate);
                return new DateTimeOffset(date);
            }
            catch { }
        }

        // Parse from text
        var dateText = cell.Text?.Trim();
        if (string.IsNullOrEmpty(dateText)) return null;

        // Try various date formats
        string[] formats = new[]
        {
            "dd/MM/yyyy",
            "dd/MM/yyyy HH:mm",
            "dd/MM/yyyy HH:mm:ss",
            "d/M/yyyy",
            "yyyy-MM-dd",
            "MM/dd/yyyy",
            "dd-MM-yyyy"
        };

        foreach (var format in formats)
        {
            if (DateTime.TryParseExact(dateText, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out var parsed))
            {
                return new DateTimeOffset(parsed);
            }
        }

        // Try general parse
        if (DateTime.TryParse(dateText, CultureInfo.GetCultureInfo("vi-VN"), DateTimeStyles.None, out var viParsed))
        {
            return new DateTimeOffset(viParsed);
        }

        return null;
    }

    private decimal ParseAmount(ExcelWorksheet worksheet, int row, ColumnMapping columnMap)
    {
        if (columnMap.AmountCol == 0) return 0;

        var cell = worksheet.Cells[row, columnMap.AmountCol];
        var amountText = cell.Text?.Trim();

        // If we have text, parse it (more reliable than cell.Value due to locale issues)
        if (!string.IsNullOrEmpty(amountText))
        {
            // Check for negative sign BEFORE cleaning
            var isNegative = amountText.Contains("-") || amountText.Contains("(") || amountText.Contains(")");

            // Remove currency symbols, whitespace, and negative signs for clean parsing
            var cleanAmount = amountText
                .Replace("VND", "", StringComparison.OrdinalIgnoreCase)
                .Replace("USD", "", StringComparison.OrdinalIgnoreCase)
                .Replace("đ", "", StringComparison.OrdinalIgnoreCase)
                .Replace("$", "")
                .Replace("(", "")
                .Replace(")", "")
                .Replace("-", "")
                .Replace(" ", "")
                .Trim();

            if (!string.IsNullOrEmpty(cleanAmount))
            {
                var lastDot = cleanAmount.LastIndexOf('.');
                var lastComma = cleanAmount.LastIndexOf(',');

                if (lastDot >= 0 && lastComma >= 0)
                {
                    if (lastDot > lastComma) // 1,234.56 (US style)
                    {
                        cleanAmount = cleanAmount.Replace(",", "");
                    }
                    else // 1.234,56 (VN/EU style)
                    {
                        cleanAmount = cleanAmount.Replace(".", "").Replace(",", ".");
                    }
                }
                else if (lastComma >= 0) // Only comma (e.g. 364,000 or 1,5)
                {
                    var parts = cleanAmount.Split(',');
                    // If ALL parts after the first have exactly 3 digits, it's a thousand separator
                    // e.g. 1,234,567 -> all parts after first are 3 digits
                    // e.g. 364,000 -> "000" is 3 digits
                    // e.g. 1,5 -> "5" is 1 digit (decimal)
                    bool isThousandSeparator = parts.Length > 1;
                    for (int i = 1; i < parts.Length && isThousandSeparator; i++)
                    {
                        if (parts[i].Length != 3)
                            isThousandSeparator = false;
                    }

                    if (isThousandSeparator)
                    {
                        cleanAmount = cleanAmount.Replace(",", "");
                    }
                    else
                    {
                        cleanAmount = cleanAmount.Replace(",", ".");
                    }
                }
                else if (lastDot >= 0) // Only dot
                {
                    var parts = cleanAmount.Split('.');
                    if (parts.Length > 2) // 1.234.567 (thousand separators)
                    {
                        cleanAmount = cleanAmount.Replace(".", "");
                    }
                }

                if (decimal.TryParse(cleanAmount, NumberStyles.Any, CultureInfo.InvariantCulture, out var amount))
                {
                    return isNegative ? -Math.Abs(amount) : amount;
                }
            }
        }

        // Fallback to cell.Value if text parsing didn't work
        if (cell.Value is double d) return (decimal)d;
        if (cell.Value is decimal dec) return dec;
        if (cell.Value is int intVal) return (decimal)intVal;
        if (cell.Value is long longVal) return (decimal)longVal;

        return 0;
    }

    private FuelCardTransactionStatus ParseTransactionStatus(string statusText)
    {
        if (string.IsNullOrEmpty(statusText))
            return FuelCardTransactionStatus.Success;

        var lower = statusText.ToLower();

        if (lower.Contains("success") || lower.Contains("thành công") || lower.Contains("đã xử lý"))
            return FuelCardTransactionStatus.Success;
        if (lower.Contains("pending") || lower.Contains("chờ"))
            return FuelCardTransactionStatus.Pending;
        if (lower.Contains("cancel") || lower.Contains("hủy") || lower.Contains("fail") || lower.Contains("lỗi") || lower.Contains("thất bại"))
            return FuelCardTransactionStatus.Failed;

        return FuelCardTransactionStatus.Success;
    }

    private string BuildRawData(ExcelWorksheet worksheet, int row, int endCol)
    {
        var parts = new List<string>();
        for (int col = 1; col <= Math.Min(endCol, 20); col++)
        {
            var value = worksheet.Cells[row, col].Text?.Trim() ?? "";
            if (!string.IsNullOrEmpty(value))
            {
                parts.Add(value);
            }
        }
        return string.Join("|", parts);
    }


    private static string TruncateString(string? input, int maxLength)
    {
        if (string.IsNullOrEmpty(input))
            return string.Empty;

        return input.Length <= maxLength ? input : input.Substring(0, maxLength);
    }

    public async Task<List<FuelCardTransactionItem>> GetUnreconciledTransactionsByCardAndDateAsync(int fuelCardId, DateTimeOffset date)
    {
        // Convert date to local time and get start/end of day
        DateTimeOffset startOfDay = date.ToLocalTime().StartOfDay();
        DateTimeOffset endOfDay = date.ToLocalTime().EndOfDay();

        var transactions = await _transactionRepository.Query()
            .Include(x => x.FuelCard)
            .Where(x => !x.IsDeleted &&
                        x.FuelCardId == fuelCardId &&
                        x.TransactionDate >= startOfDay &&
                        x.TransactionDate <= endOfDay &&
                        x.FuelRefillId == null)
            .OrderBy(x => x.TransactionDate)
            .ToListAsync();

        return transactions.Select(x => x.ToItem()).ToList();
    }

    #endregion
}

