using Lada.Framework.Data.Domains.Finance;
using Lada.Framework.Data.Enums;
using Lada.Framework.Data.Repositories;
using Ladaer.BackEnd.Infrastructures.DTO.Models.Finances;
using Microsoft.EntityFrameworkCore;
using OfficeOpenXml;
using System.Globalization;

namespace Ladaer.BackEnd.Infrastructures.Services.Finances
{
    /// <summary>
    /// Interface cho service parse sao kê ngân hàng
    /// </summary>
    public interface IBankStatementParser
    {
        Task<BankStatementParseResult> ParseBankStatement(BankStatementImportModel model, string bankCode, int userId = 0, string userName = "System");
    }

    /// <summary>
    /// Service parse sao kê ngân hàng
    /// </summary>
    public class BankStatementParser : IBankStatementParser
    {
        private readonly IRepository<BankTransaction> _transactionRepository;

        public BankStatementParser(IRepository<BankTransaction> transactionRepository)
        {
            _transactionRepository = transactionRepository;
        }

        public async Task<BankStatementParseResult> ParseBankStatement(BankStatementImportModel model, string bankCode, int userId = 0, string userName = "System")
        {
            var result = new BankStatementParseResult();

            try
            {
                using var stream = model.ExcelFile.OpenReadStream();
                using var package = new ExcelPackage(stream);

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

                var worksheet = package.Workbook.Worksheets[0];

                var parser = GetBankParser(bankCode);
                result = await parser.Parse(worksheet, model, userId, userName);
            }
            catch (Exception ex)
            {
                result.IsSuccess = false;
                result.ErrorMessage = $"Lỗi khi đọc file Excel: {ex.Message}";
            }

            return result;
        }

        private IBankSpecificParser GetBankParser(string bankCode)
        {
            return bankCode switch
            {
                "VPBank" => new VPBankParser(_transactionRepository),
                "Techcombank" => new TechcombankParser(_transactionRepository),
                "SHINHANBANK" => new ShinhanBankParser(_transactionRepository),
                "HDBank" => new HDBankParser(_transactionRepository),
                _ => throw new NotSupportedException($"Ngân hàng {bankCode} chưa được hỗ trợ")
            };
        }
    }

    /// <summary>
    /// Interface cho parser riêng từng ngân hàng
    /// </summary>
    public interface IBankSpecificParser
    {
        Task<BankStatementParseResult> Parse(ExcelWorksheet worksheet, BankStatementImportModel model, int userId = 0, string userName = "System");
    }

    /// <summary>
    /// Parser cho VPBank
    /// </summary>
    public class VPBankParser : IBankSpecificParser
    {
        private readonly IRepository<BankTransaction> _transactionRepository;

        public VPBankParser(IRepository<BankTransaction> transactionRepository)
        {
            _transactionRepository = transactionRepository;
        }

        public async Task<BankStatementParseResult> Parse(ExcelWorksheet worksheet, BankStatementImportModel model, int userId = 0, string userName = "System")
        {
            var result = new BankStatementParseResult();
            var transactions = new List<BankTransaction>();

            // VPBank format: Số hóa đơn | Ngày ghi nợ | Số tiền ghi có | Số tiền ghi nợ | Nội dung | Số dư | Thời gian
            var startRow = 2; // Bỏ qua header
            var endRow = worksheet.Dimension?.End.Row ?? 0;

            for (int row = startRow; row <= endRow; row++)
            {
                try
                {
                    var transactionCode = worksheet.Cells[row, 2].Text?.Trim();
                    if (string.IsNullOrEmpty(transactionCode)) continue;

                    var dateText = worksheet.Cells[row, 3].Text?.Trim();
                    var creditText = worksheet.Cells[row, 4].Text?.Trim();
                    var debitText = worksheet.Cells[row, 5].Text?.Trim();
                    var description = worksheet.Cells[row, 6].Text?.Trim();
                    var balanceText = worksheet.Cells[row, 7].Text?.Trim();
                    var timeText = worksheet.Cells[row, 8].Text?.Trim();

                    var transaction = new BankTransaction
                    {
                        Id = Guid.NewGuid(),
                        BankAccountId = model.BankAccountId,
                        TransactionCode = TruncateString(transactionCode, 512),
                        TransactionDate = ParseVPBankDate(dateText),
                        CreditAmount = ParseAmount(creditText),
                        DebitAmount = ParseAmount(debitText),
                        Balance = ParseAmount(balanceText),
                        Description = description ?? "",
                        DetailedTime = ParseVPBankDateTime(timeText),
                        RawData = $"{transactionCode}|{dateText}|{creditText}|{debitText}|{description}|{balanceText}|{timeText}",
                        ProcessStatus = TransactionProcessStatus.Processed,
                        ImportRowNumber = row,
                        DisplayOrder = row + 5,
                        TransactionType = DetermineTransactionType(description, ParseAmount(creditText), ParseAmount(debitText)),
                        CreatedTime = DateTimeOffset.Now,
                        UpdatedTime = DateTimeOffset.Now,
                        CreatedBy = userId,
                        CreatedByUserName = userName,
                        UpdatedBy = userId,
                        UpdatedByUserName = userName
                    };

                    // Lọc theo ngày nếu có
                    if (model.FromDate.HasValue && transaction.TransactionDate < model.FromDate.Value)
                        continue;
                    if (model.ToDate.HasValue && transaction.TransactionDate > model.ToDate.Value)
                        continue;

                    // Check duplicate
                    var exists = await _transactionRepository.Query()
                        .AnyAsync(x => x.BankAccountId == model.BankAccountId &&
                                      x.TransactionCode == transactionCode &&
                                      x.TransactionDate.Date == transaction.TransactionDate.Date
                                      );

                    if (!exists || model.OverwriteExisting)
                    {
                        if (exists && model.OverwriteExisting)
                        {
                            // Update existing
                            var existing = await _transactionRepository.Query()
                                .FirstAsync(x => x.BankAccountId == model.BankAccountId &&
                                               x.TransactionCode == transactionCode &&
                                               x.TransactionDate.Date == transaction.TransactionDate.Date);

                            existing.CreditAmount = transaction.CreditAmount;
                            existing.DebitAmount = transaction.DebitAmount;
                            existing.Balance = transaction.Balance;
                            existing.Description = transaction.Description;
                            existing.DetailedTime = transaction.DetailedTime;
                            existing.RawData = transaction.RawData;
                            existing.DisplayOrder = transaction.DisplayOrder;
                            existing.TransactionType = transaction.TransactionType;
                            existing.UpdatedTime = DateTimeOffset.Now;
                            existing.UpdatedBy = userId;
                            existing.UpdatedByUserName = userName;

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

                        transactions.Add(transaction);
                        result.ProcessedRows++;
                    }
                    else
                    {
                        result.ErrorRows++;
                        result.ErrorMessages.Add($"Dòng {row}: Giao dịch đã tồn tại - {transactionCode}");
                    }
                }
                catch (Exception ex)
                {
                    result.ErrorRows++;
                    result.ErrorMessages.Add($"Dòng {row}: {ex.Message}");
                }

                result.TotalRows++;
            }

            result.Transactions = transactions;
            result.IsSuccess = true;

            return result;
        }

        private DateTimeOffset ParseVPBankDate(string? dateText)
        {
            if (string.IsNullOrEmpty(dateText))
                throw new FormatException("Ngày giao dịch không được để trống");

            if (DateTime.TryParseExact(dateText, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date))
            {
                return new DateTimeOffset(date);
            }

            throw new FormatException($"Định dạng ngày không hợp lệ: {dateText}");
        }

        private DateTimeOffset? ParseVPBankDateTime(string? timeText)
        {
            if (string.IsNullOrEmpty(timeText)) return null;

            if (DateTime.TryParseExact(timeText, "dd/MM/yyyy HH:mm", CultureInfo.InvariantCulture, DateTimeStyles.None, out var dateTime))
            {
                return new DateTimeOffset(dateTime);
            }
            return null;
        }

        private decimal ParseAmount(string? amountText)
        {
            if (string.IsNullOrEmpty(amountText)) return 0;

            // Remove dots (thousand separators) and parse
            var cleanAmount = amountText.Replace(".", "").Replace(",", ".");
            if (decimal.TryParse(cleanAmount, NumberStyles.Number, CultureInfo.InvariantCulture, out var amount))
            {
                return amount;
            }
            return 0;
        }

        private BankTransactionType DetermineTransactionType(string? description, decimal creditAmount, decimal debitAmount)
        {
            if (string.IsNullOrEmpty(description))
                return BankTransactionType.Other;

            var desc = description.ToLower();

            if (creditAmount > 0)
            {
                if (desc.Contains("chuyen khoan") || desc.Contains("transfer"))
                    return BankTransactionType.TransferIn;
                if (desc.Contains("nop tien") || desc.Contains("deposit"))
                    return BankTransactionType.Deposit;
                if (desc.Contains("lai") || desc.Contains("interest"))
                    return BankTransactionType.Interest;
            }
            else if (debitAmount > 0)
            {
                if (desc.Contains("chuyen khoan") || desc.Contains("transfer"))
                    return BankTransactionType.TransferOut;
                if (desc.Contains("rut tien") || desc.Contains("withdrawal"))
                    return BankTransactionType.Withdrawal;
                if (desc.Contains("phi") || desc.Contains("fee"))
                    return BankTransactionType.ServiceFee;
                if (desc.Contains("thanh toan") || desc.Contains("payment"))
                    return BankTransactionType.BillPayment;
            }

            return BankTransactionType.Other;
        }

        /// <summary>
        /// Truncate string to specified length
        /// </summary>
        private static string TruncateString(string? input, int maxLength)
        {
            if (string.IsNullOrEmpty(input))
                return string.Empty;

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

    /// <summary>
    /// Parser cho Techcombank
    /// </summary>
    public class TechcombankParser : IBankSpecificParser
    {
        private readonly IRepository<BankTransaction> _transactionRepository;

        public TechcombankParser(IRepository<BankTransaction> transactionRepository)
        {
            _transactionRepository = transactionRepository;
        }

        public async Task<BankStatementParseResult> Parse(ExcelWorksheet worksheet, BankStatementImportModel model, int userId = 0, string userName = "System")
        {
            var result = new BankStatementParseResult();
            var transactions = new List<BankTransaction>();

            // Techcombank format: NGAY | DIEN GIAI | CHI TIET | NO | CO | SO DU
            var startRow = 2; // Bỏ qua header
            var endRow = worksheet.Dimension?.End.Row ?? 0;

            for (int row = startRow; row <= endRow; row++)
            {
                try
                {
                    var dateText = worksheet.Cells[row, 1].Text?.Trim();        // NGAY
                    var description = worksheet.Cells[row, 2].Text?.Trim();  // DIEN GIAI = Nội dung giao dịch
                    var transactionCode = worksheet.Cells[row, 3].Text?.Trim(); // CHI TIET = Mã giao dịch
                    var debitText = worksheet.Cells[row, 4].Text?.Trim();    // NO = Ghi nợ
                    var creditText = worksheet.Cells[row, 5].Text?.Trim();   // CO = Ghi có
                    var balanceText = worksheet.Cells[row, 6].Text?.Trim();  // SO DU = Số dư

                    if (string.IsNullOrEmpty(transactionCode)) continue;

                    var debitAmount = ParseAmount(debitText);   // NO - Ghi nợ
                    var creditAmount = ParseAmount(creditText); // CO - Ghi có

                    var transaction = new BankTransaction
                    {
                        Id = Guid.NewGuid(),
                        BankAccountId = model.BankAccountId,
                        TransactionCode = StringExtensions.TruncateString(transactionCode, 512),
                        TransactionDate = ParseTechcombankDate(dateText),
                        CreditAmount = creditAmount,
                        DebitAmount = debitAmount,
                        Balance = ParseAmount(balanceText),
                        Description = description ?? "",
                        RawData = $"{dateText}|{description}|{transactionCode}|{debitText}|{creditText}|{balanceText}",
                        ProcessStatus = TransactionProcessStatus.Processed,
                        ImportRowNumber = row,
                        DisplayOrder = row + 5,
                        TransactionType = DetermineTransactionType(description, creditAmount, debitAmount),
                        CreatedTime = DateTimeOffset.Now,
                        UpdatedTime = DateTimeOffset.Now,
                        CreatedBy = userId,
                        CreatedByUserName = userName,
                        UpdatedBy = userId,
                        UpdatedByUserName = userName
                    };

                    // Lọc theo ngày nếu có
                    if (model.FromDate.HasValue && transaction.TransactionDate < model.FromDate.Value)
                        continue;
                    if (model.ToDate.HasValue && transaction.TransactionDate > model.ToDate.Value)
                        continue;

                    // Check duplicate
                    var exists = await _transactionRepository.Query()
                        .AnyAsync(x => x.BankAccountId == model.BankAccountId &&
                                      x.TransactionCode == transactionCode &&
                                      x.TransactionDate.Date == transaction.TransactionDate.Date);

                    if (!exists || model.OverwriteExisting)
                    {
                        if (exists && model.OverwriteExisting)
                        {
                            // Update existing
                            var existing = await _transactionRepository.Query()
                                .FirstAsync(x => x.BankAccountId == model.BankAccountId &&
                                               x.TransactionCode == transactionCode &&
                                               x.TransactionDate.Date == transaction.TransactionDate.Date);

                            existing.CreditAmount = transaction.CreditAmount;
                            existing.DebitAmount = transaction.DebitAmount;
                            existing.Balance = transaction.Balance;
                            existing.Description = transaction.Description;
                            existing.RawData = transaction.RawData;
                            existing.DisplayOrder = transaction.DisplayOrder;
                            existing.TransactionType = transaction.TransactionType;
                            existing.UpdatedTime = DateTimeOffset.Now;
                            existing.UpdatedBy = userId;
                            existing.UpdatedByUserName = userName;

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

                        transactions.Add(transaction);
                        result.ProcessedRows++;
                    }
                    else
                    {
                        result.ErrorRows++;
                        result.ErrorMessages.Add($"Dòng {row}: Giao dịch đã tồn tại - {transactionCode}");
                    }
                }
                catch (Exception ex)
                {
                    result.ErrorRows++;
                    result.ErrorMessages.Add($"Dòng {row}: {ex.Message}");
                }

                result.TotalRows++;
            }

            result.Transactions = transactions;
            result.IsSuccess = true;

            return result;
        }

        private DateTimeOffset ParseTechcombankDate(string? dateText)
        {
            if (string.IsNullOrEmpty(dateText))
                throw new FormatException("Ngày giao dịch không được để trống");

            if (DateTime.TryParseExact(dateText, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date))
            {
                return new DateTimeOffset(date);
            }

            throw new FormatException($"Định dạng ngày không hợp lệ: {dateText}");
        }

        private decimal ParseAmount(string? amountText)
        {
            if (string.IsNullOrEmpty(amountText)) return 0;

            // Remove commas (thousand separators) and parse
            var cleanAmount = amountText.Replace(",", "");
            if (decimal.TryParse(cleanAmount, NumberStyles.Number, CultureInfo.InvariantCulture, out var amount))
            {
                return amount;
            }
            return 0;
        }

        private BankTransactionType DetermineTransactionType(string? description, decimal creditAmount, decimal debitAmount)
        {
            if (string.IsNullOrEmpty(description))
                return BankTransactionType.Other;

            var desc = description.ToLower();

            if (creditAmount > 0)
            {
                if (desc.Contains("chuyen khoan") || desc.Contains("transfer"))
                    return BankTransactionType.TransferIn;
                if (desc.Contains("nop tien") || desc.Contains("deposit"))
                    return BankTransactionType.Deposit;
                if (desc.Contains("lai") || desc.Contains("interest"))
                    return BankTransactionType.Interest;
            }
            else if (debitAmount > 0)
            {
                if (desc.Contains("chuyen khoan") || desc.Contains("transfer"))
                    return BankTransactionType.TransferOut;
                if (desc.Contains("rut tien") || desc.Contains("withdrawal"))
                    return BankTransactionType.Withdrawal;
                if (desc.Contains("phi") || desc.Contains("fee"))
                    return BankTransactionType.ServiceFee;
                if (desc.Contains("thanh toan") || desc.Contains("payment"))
                    return BankTransactionType.BillPayment;
            }

            return BankTransactionType.Other;
        }

        /// <summary>
        /// Truncate string to specified length
        /// </summary>
        private static string TruncateString(string? input, int maxLength)
        {
            if (string.IsNullOrEmpty(input))
                return string.Empty;

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

    /// <summary>
    /// Parser cho Shinhan Bank
    /// </summary>
    public class ShinhanBankParser : IBankSpecificParser
    {
        private readonly IRepository<BankTransaction> _transactionRepository;

        public ShinhanBankParser(IRepository<BankTransaction> transactionRepository)
        {
            _transactionRepository = transactionRepository;
        }

        public async Task<BankStatementParseResult> Parse(ExcelWorksheet worksheet, BankStatementImportModel model, int userId = 0, string userName = "System")
        {
            var result = new BankStatementParseResult();
            var transactions = new List<BankTransaction>();

            // Shinhan Bank format: Ngày | Loại giao dịch | Ghi nợ | Ghi có | Số dư | Số tham chiếu | Ghi chú
            var startRow = 2; // Bỏ qua header
            var endRow = worksheet.Dimension?.End.Row ?? 0;

            for (int row = startRow; row <= endRow; row++)
            {
                try
                {
                    var dateText = worksheet.Cells[row, 1].Text?.Trim();              // Ngày
                    var transactionType = worksheet.Cells[row, 2].Text?.Trim();       // Loại giao dịch
                    var debitText = worksheet.Cells[row, 3].Text?.Trim();             // Ghi nợ
                    var creditText = worksheet.Cells[row, 4].Text?.Trim();            // Ghi có
                    var balanceText = worksheet.Cells[row, 5].Text?.Trim();           // Số dư
                    var transactionCode = worksheet.Cells[row, 6].Text?.Trim();       // Số tham chiếu (Mã GD)
                    var description = worksheet.Cells[row, 7].Text?.Trim();           // Ghi chú

                    // Skip nếu không có mã giao dịch hoặc ngày
                    if (string.IsNullOrEmpty(transactionCode) || string.IsNullOrEmpty(dateText))
                        continue;

                    var debitAmount = ParseAmount(debitText);   // Ghi nợ
                    var creditAmount = ParseAmount(creditText); // Ghi có

                    var transaction = new BankTransaction
                    {
                        Id = Guid.NewGuid(),
                        BankAccountId = model.BankAccountId,
                        TransactionCode = StringExtensions.TruncateString(transactionCode, 512),
                        TransactionDate = ParseShinhanBankDate(dateText),
                        CreditAmount = creditAmount,
                        DebitAmount = debitAmount,
                        Balance = ParseAmount(balanceText),
                        Description = CombineDescription(transactionType, description),
                        RawData = $"{dateText}|{transactionType}|{debitText}|{creditText}|{balanceText}|{transactionCode}|{description}",
                        ProcessStatus = TransactionProcessStatus.Processed,
                        ImportRowNumber = row,
                        DisplayOrder = row + 5,
                        TransactionType = DetermineTransactionType(transactionType, description, creditAmount, debitAmount),
                        CreatedTime = DateTimeOffset.Now,
                        UpdatedTime = DateTimeOffset.Now,
                        CreatedBy = userId,
                        CreatedByUserName = userName,
                        UpdatedBy = userId,
                        UpdatedByUserName = userName
                    };

                    // Lọc theo ngày nếu có
                    if (model.FromDate.HasValue && transaction.TransactionDate < model.FromDate.Value)
                        continue;
                    if (model.ToDate.HasValue && transaction.TransactionDate > model.ToDate.Value)
                        continue;

                    // Check duplicate
                    var exists = await _transactionRepository.Query()
                        .AnyAsync(x => x.BankAccountId == model.BankAccountId &&
                                      x.TransactionCode == transactionCode &&
                                      x.TransactionDate.Date == transaction.TransactionDate.Date);

                    if (!exists || model.OverwriteExisting)
                    {
                        if (exists && model.OverwriteExisting)
                        {
                            // Update existing
                            var existing = await _transactionRepository.Query()
                                .FirstAsync(x => x.BankAccountId == model.BankAccountId &&
                                               x.TransactionCode == transactionCode &&
                                               x.TransactionDate.Date == transaction.TransactionDate.Date);

                            existing.CreditAmount = transaction.CreditAmount;
                            existing.DebitAmount = transaction.DebitAmount;
                            existing.Balance = transaction.Balance;
                            existing.Description = transaction.Description;
                            existing.RawData = transaction.RawData;
                            existing.DisplayOrder = transaction.DisplayOrder;
                            existing.TransactionType = transaction.TransactionType;
                            existing.UpdatedTime = DateTimeOffset.Now;
                            existing.UpdatedBy = userId;
                            existing.UpdatedByUserName = userName;

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

                        transactions.Add(transaction);
                        result.ProcessedRows++;
                    }
                    else
                    {
                        result.ErrorRows++;
                        result.ErrorMessages.Add($"Dòng {row}: Giao dịch đã tồn tại - {transactionCode}");
                    }
                }
                catch (Exception ex)
                {
                    result.ErrorRows++;
                    result.ErrorMessages.Add($"Dòng {row}: {ex.Message}");
                }

                result.TotalRows++;
            }

            result.Transactions = transactions;
            result.IsSuccess = true;

            return result;
        }

        private DateTimeOffset ParseShinhanBankDate(string? dateText)
        {
            if (string.IsNullOrEmpty(dateText))
                throw new FormatException("Ngày giao dịch không được để trống");

            if (DateTime.TryParseExact(dateText, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date))
            {
                return new DateTimeOffset(date);
            }

            throw new FormatException($"Định dạng ngày không hợp lệ: {dateText}");
        }

        private decimal ParseAmount(string? amountText)
        {
            if (string.IsNullOrEmpty(amountText) || amountText == "-") return 0;

            // Shinhan Bank không có dấu phân cách hàng nghìn, chỉ là số thuần
            if (decimal.TryParse(amountText, NumberStyles.Number, CultureInfo.InvariantCulture, out var amount))
            {
                return amount;
            }
            return 0;
        }

        private string CombineDescription(string? transactionType, string? description)
        {
            var parts = new List<string>();
            if (!string.IsNullOrEmpty(transactionType))
                parts.Add(transactionType);
            if (!string.IsNullOrEmpty(description))
                parts.Add(description);

            return string.Join(" - ", parts);
        }

        private BankTransactionType DetermineTransactionType(string? transactionType, string? description, decimal creditAmount, decimal debitAmount)
        {
            var fullText = $"{transactionType} {description}".ToLower();

            if (creditAmount > 0)
            {
                if (fullText.Contains("chuyen khoan") || fullText.Contains("transfer") || fullText.Contains("chuyen tien"))
                    return BankTransactionType.TransferIn;
                if (fullText.Contains("nop tien") || fullText.Contains("deposit"))
                    return BankTransactionType.Deposit;
                if (fullText.Contains("lai") || fullText.Contains("interest"))
                    return BankTransactionType.Interest;
                if (fullText.Contains("atm"))
                    return BankTransactionType.TransferIn;
            }
            else if (debitAmount > 0)
            {
                if (fullText.Contains("chuyen khoan") || fullText.Contains("transfer") || fullText.Contains("chuyen tien"))
                    return BankTransactionType.TransferOut;
                if (fullText.Contains("rut tien") || fullText.Contains("withdrawal"))
                    return BankTransactionType.Withdrawal;
                if (fullText.Contains("phi") || fullText.Contains("fee"))
                    return BankTransactionType.ServiceFee;
                if (fullText.Contains("thanh toan") || fullText.Contains("payment"))
                    return BankTransactionType.BillPayment;
                if (fullText.Contains("atm"))
                    return BankTransactionType.TransferOut;
            }

            return BankTransactionType.Other;
        }
    }

    /// <summary>
    /// Parser cho HDBank
    /// </summary>
    public class HDBankParser : IBankSpecificParser
    {
        private readonly IRepository<BankTransaction> _transactionRepository;

        public HDBankParser(IRepository<BankTransaction> transactionRepository)
        {
            _transactionRepository = transactionRepository;
        }

        public async Task<BankStatementParseResult> Parse(ExcelWorksheet worksheet, BankStatementImportModel model, int userId = 0, string userName = "System")
        {
            var result = new BankStatementParseResult();
            var transactions = new List<BankTransaction>();

            // HDBank format: STT | Ngày giao dịch | Ngày hiệu lực | Giao dịch | Số tiền rút | Số tiền gửi | Số dư
            var startRow = 2; // Bỏ qua header
            var endRow = worksheet.Dimension?.End.Row ?? 0;

            for (int row = startRow; row <= endRow; row++)
            {
                try
                {
                    var stt = worksheet.Cells[row, 1].Text?.Trim();                    // STT
                    var transactionDateText = worksheet.Cells[row, 2].Text?.Trim();    // Ngày giao dịch
                    var effectiveDateText = worksheet.Cells[row, 3].Text?.Trim();      // Ngày hiệu lực
                    var transactionText = worksheet.Cells[row, 4].Text?.Trim();        // Giao dịch (multi-line)
                    var debitText = worksheet.Cells[row, 5].Text?.Trim();              // Số tiền rút
                    var creditText = worksheet.Cells[row, 6].Text?.Trim();             // Số tiền gửi
                    var balanceText = worksheet.Cells[row, 7].Text?.Trim();            // Số dư

                    // Skip nếu không có ngày giao dịch hoặc text giao dịch
                    if (string.IsNullOrEmpty(transactionDateText) || string.IsNullOrEmpty(transactionText))
                        continue;

                    // Parse multi-line transaction text
                    var transactionInfo = ParseTransactionText(transactionText);
                    if (string.IsNullOrEmpty(transactionInfo.TransactionCode))
                        continue;

                    var debitAmount = ParseAmount(debitText);   // Số tiền rút
                    var creditAmount = ParseAmount(creditText); // Số tiền gửi

                    var transaction = new BankTransaction
                    {
                        Id = Guid.NewGuid(),
                        BankAccountId = model.BankAccountId,
                        TransactionCode = StringExtensions.TruncateString(transactionInfo.TransactionCode, 512),
                        TransactionDate = ParseHDBankDate(transactionDateText),
                        CreditAmount = creditAmount,
                        DebitAmount = debitAmount,
                        Balance = ParseAmount(balanceText),
                        Description = BuildDescription(transactionInfo),
                        RawData = $"{transactionDateText}|{effectiveDateText}|{transactionText}|{debitText}|{creditText}|{balanceText}",
                        ProcessStatus = TransactionProcessStatus.Processed,
                        ImportRowNumber = row,
                        DisplayOrder = row + 5,
                        TransactionType = DetermineTransactionType(transactionInfo, creditAmount, debitAmount),
                        CreatedTime = DateTimeOffset.Now,
                        UpdatedTime = DateTimeOffset.Now,
                        CreatedBy = userId,
                        CreatedByUserName = userName,
                        UpdatedBy = userId,
                        UpdatedByUserName = userName
                    };

                    // Lọc theo ngày nếu có
                    if (model.FromDate.HasValue && transaction.TransactionDate < model.FromDate.Value)
                        continue;
                    if (model.ToDate.HasValue && transaction.TransactionDate > model.ToDate.Value)
                        continue;

                    // Check duplicate
                    var exists = await _transactionRepository.Query()
                        .AnyAsync(x => x.BankAccountId == model.BankAccountId &&
                                      x.TransactionCode == transactionInfo.TransactionCode &&
                                      x.TransactionDate.Date == transaction.TransactionDate.Date);

                    if (!exists || model.OverwriteExisting)
                    {
                        if (exists && model.OverwriteExisting)
                        {
                            // Update existing
                            var existing = await _transactionRepository.Query()
                                .FirstAsync(x => x.BankAccountId == model.BankAccountId &&
                                               x.TransactionCode == transactionInfo.TransactionCode &&
                                               x.TransactionDate.Date == transaction.TransactionDate.Date);

                            existing.CreditAmount = transaction.CreditAmount;
                            existing.DebitAmount = transaction.DebitAmount;
                            existing.Balance = transaction.Balance;
                            existing.Description = transaction.Description;
                            existing.RawData = transaction.RawData;
                            existing.DisplayOrder = transaction.DisplayOrder;
                            existing.TransactionType = transaction.TransactionType;
                            existing.UpdatedTime = DateTimeOffset.Now;
                            existing.UpdatedBy = userId;
                            existing.UpdatedByUserName = userName;

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

                        transactions.Add(transaction);
                        result.ProcessedRows++;
                    }
                    else
                    {
                        result.ErrorRows++;
                        result.ErrorMessages.Add($"Dòng {row}: Giao dịch đã tồn tại - {transactionInfo.TransactionCode}");
                    }
                }
                catch (Exception ex)
                {
                    result.ErrorRows++;
                    result.ErrorMessages.Add($"Dòng {row}: {ex.Message}");
                }

                result.TotalRows++;
            }

            result.Transactions = transactions;
            result.IsSuccess = true;

            return result;
        }

        /// <summary>
        /// Parse multi-line transaction text từ HDBank
        /// Format: 
        /// "Loại giao dịch: TTOAN THE TD - ATM/POS/EBANK
        ///  So giao dich: 93297
        ///  Noi dung: THANH TOAN DU NO THE TIN DUNG HDBANK 9439, 7939"
        /// </summary>
        private HDBankTransactionInfo ParseTransactionText(string transactionText)
        {
            var info = new HDBankTransactionInfo();
            var lines = transactionText.Split(new[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);

            foreach (var line in lines)
            {
                var trimmedLine = line.Trim();

                if (trimmedLine.StartsWith("Loai giao dich:", StringComparison.OrdinalIgnoreCase) ||
                    trimmedLine.StartsWith("Loại giao dịch:", StringComparison.OrdinalIgnoreCase))
                {
                    info.TransactionType = trimmedLine.Substring(trimmedLine.IndexOf(':') + 1).Trim();
                }
                else if (trimmedLine.StartsWith("So giao dich:", StringComparison.OrdinalIgnoreCase) ||
                         trimmedLine.StartsWith("Số giao dịch:", StringComparison.OrdinalIgnoreCase))
                {
                    info.TransactionCode = trimmedLine.Substring(trimmedLine.IndexOf(':') + 1).Trim();
                }
                else if (trimmedLine.StartsWith("Noi dung:", StringComparison.OrdinalIgnoreCase) ||
                         trimmedLine.StartsWith("Nội dung:", StringComparison.OrdinalIgnoreCase))
                {
                    info.Content = trimmedLine.Substring(trimmedLine.IndexOf(':') + 1).Trim();
                }
            }

            return info;
        }

        private string BuildDescription(HDBankTransactionInfo info)
        {
            var parts = new List<string>();

            if (!string.IsNullOrEmpty(info.TransactionType))
                parts.Add(info.TransactionType);

            if (!string.IsNullOrEmpty(info.Content))
                parts.Add(info.Content);

            return string.Join(" - ", parts);
        }

        private DateTimeOffset ParseHDBankDate(string? dateText)
        {
            if (string.IsNullOrEmpty(dateText))
                throw new FormatException("Ngày giao dịch không được để trống");

            if (DateTime.TryParseExact(dateText, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out var date))
            {
                return new DateTimeOffset(date);
            }

            throw new FormatException($"Định dạng ngày không hợp lệ: {dateText}");
        }

        private decimal ParseAmount(string? amountText)
        {
            if (string.IsNullOrEmpty(amountText) || amountText == "-") return 0;

            // HDBank không có dấu phân cách hàng nghìn trong số
            if (decimal.TryParse(amountText, NumberStyles.Number, CultureInfo.InvariantCulture, out var amount))
            {
                return amount;
            }
            return 0;
        }

        private BankTransactionType DetermineTransactionType(HDBankTransactionInfo info, decimal creditAmount, decimal debitAmount)
        {
            var fullText = $"{info.TransactionType} {info.Content}".ToLower();

            if (creditAmount > 0)
            {
                if (fullText.Contains("credit for c2c") || fullText.Contains("chuyen khoan") || fullText.Contains("transfer"))
                    return BankTransactionType.TransferIn;
                if (fullText.Contains("nop tien") || fullText.Contains("deposit"))
                    return BankTransactionType.Deposit;
                if (fullText.Contains("lai") || fullText.Contains("interest"))
                    return BankTransactionType.Interest;
            }
            else if (debitAmount > 0)
            {
                if (fullText.Contains("ttoan the td") || fullText.Contains("atm") || fullText.Contains("pos") || fullText.Contains("ebank"))
                    return BankTransactionType.TransferOut;
                if (fullText.Contains("thanh toan du no") || fullText.Contains("thanh toan the"))
                    return BankTransactionType.BillPayment;
                if (fullText.Contains("rut tien") || fullText.Contains("withdrawal"))
                    return BankTransactionType.Withdrawal;
                if (fullText.Contains("phi") || fullText.Contains("fee"))
                    return BankTransactionType.ServiceFee;
            }

            return BankTransactionType.Other;
        }

        /// <summary>
        /// Class lưu thông tin giao dịch HDBank đã parse
        /// </summary>
        private class HDBankTransactionInfo
        {
            public string TransactionType { get; set; } = string.Empty;
            public string TransactionCode { get; set; } = string.Empty;
            public string Content { get; set; } = string.Empty;
        }
    }

    /// <summary>
    /// Truncate string to specified length
    /// </summary>
    public static class StringExtensions
    {
        public static string TruncateString(string? input, int maxLength)
        {
            if (string.IsNullOrEmpty(input))
                return string.Empty;

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