using Dapper;
using Lada.Framework.Data.Domains.Crm;
using Lada.Framework.Data.Domains.Finance;
using Lada.Framework.Data.Domains.Identity;
using Lada.Framework.Data.Domains.Medias;
using Lada.Framework.Data.Domains.Orders;
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 Lada.Framework.Infrastructures.Services;
using Ladaer.BackEnd.Infrastructures.DTO.Items.Finance;
using Ladaer.BackEnd.Infrastructures.DTO.Models.Finance;
using Ladaer.BackEnd.Infrastructures.DTO.Models.Orders;
using Ladaer.BackEnd.Infrastructures.DTO.Profiles;
using Ladaer.BackEnd.Infrastructures.DTO.Results.Finance;
using Ladaer.BackEnd.Infrastructures.DTO.StoredProcedureResults;
using Ladaer.BackEnd.Infrastructures.Services.Crm;
using Ladaer.BackEnd.Infrastructures.Services.Identity;
using Ladaer.BackEnd.Infrastructures.Services.Orders;
using Ladaer.BackEnd.Infrastructures.Services.Finances;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System.Data;
using static Lada.Framework.Data.Enums.OrderFinEnum;
using Lada.Framework.Data.Domains.Finance.Wallets;
using Lada.Framework.Data.Domains;

namespace Ladaer.BackEnd.Infrastructures.Services.Finance
{
    public interface ITransferPaymentBillService
    {
        Task<SmartTableResult<CustomerTransferPaymentSummaryItem>> SearchCustomersForTransferPayment(SmartTableParam param, CurrentUser currentUser);
        Task<CustomerTransferableOrdersModel> GetTransferableOrdersByCustomer(int customerId, CurrentUser currentUser);
        Task<SmartTableResult<TransferPaymentBillItem>> Search(SmartTableParam param, CurrentUser currentUser);
        Task<TransferPaymentBillDetailModel?> GetById(int id, CurrentUser currentUser);
        Task<TransferPaymentBillResult> Create(CreateTransferPaymentBillModel model, CurrentUser currentUser);
        Task<TransferPaymentBillResult> Cancel(CancelTransferPaymentBillModel model);
        [Obsolete("Sử dụng Cancel(CancelTransferPaymentBillModel model) thay thế")] Task<TransferPaymentBillResult> Cancel(int id, CurrentUser currentUser);
        Task<TransferPaymentBillResult> Approve(ApproveTransferPaymentBillModel model, CurrentUser currentUser);
        TransferPaymentBillPriority CalculatePriority(DateTimeOffset oldestOrderDate);
    }

    /// <summary>
    /// Service quản lý hóa đơn thanh toán chuyển khoản
    /// </summary>
    public class TransferPaymentBillService : ITransferPaymentBillService
    {
        private readonly IRepository<TransferPaymentBill> _transferPaymentBillRepository;
        private readonly IRepository<TransferPaymentBillOrder> _transferPaymentBillOrderRepository;
        private readonly IRepository<Order> _orderRepository;
        private readonly IRepository<OrderFin> _orderFinRepository;
        private readonly IRepository<Customer> _customerRepository;
        private readonly IRepository<Media> _mediaRepository;
        private readonly IOrderTrackService _orderTrackService;
        private readonly IUnifiedMediaService _unifiedMediaService;
        private readonly IUserService _userService;
        private readonly ICustomerService _customerService;
        private readonly IDbConnection _dbConnection;
        private readonly ILogger<TransferPaymentBillService> _logger;
        private readonly IRepository<Wallet> _walletRepository;
        private readonly IRepository<Transaction> _transactionRepository;
        private readonly IOrderTimeTrackService _orderTimeTrackService;

        public TransferPaymentBillService(
            IRepository<TransferPaymentBill> transferPaymentBillRepository,
            IRepository<TransferPaymentBillOrder> transferPaymentBillOrderRepository,
            IRepository<Order> orderRepository,
            IRepository<OrderFin> orderFinRepository,
            IRepository<Customer> customerRepository,
            IRepository<Media> mediaRepository,
            IOrderTrackService orderTrackService,
            IUnifiedMediaService unifiedMediaService,
            IUserService userService,
            ICustomerService customerService,
            IDbConnection dbConnection,
            ILogger<TransferPaymentBillService> logger,
            IRepository<Wallet> walletRepository,
            IRepository<Transaction> transactionRepository,
            IOrderTimeTrackService orderTimeTrackService)
        {
            _transferPaymentBillRepository = transferPaymentBillRepository;
            _transferPaymentBillOrderRepository = transferPaymentBillOrderRepository;
            _orderRepository = orderRepository;
            _orderFinRepository = orderFinRepository;
            _customerRepository = customerRepository;
            _mediaRepository = mediaRepository;
            _orderTrackService = orderTrackService;
            _unifiedMediaService = unifiedMediaService;
            _userService = userService;
            _customerService = customerService;
            _dbConnection = dbConnection;
            _logger = logger;
            _walletRepository = walletRepository;
            _transactionRepository = transactionRepository;
            _orderTimeTrackService = orderTimeTrackService;
        }

        #region Search & List Operations

        /// <summary>
        /// Tìm kiếm khách hàng có đơn hàng cần tạo hóa đơn thanh toán chuyển khoản
        /// </summary>
        public async Task<SmartTableResult<CustomerTransferPaymentSummaryItem>> SearchCustomersForTransferPayment(
            SmartTableParam param,
            CurrentUser currentUser)
        {
            // Khởi tạo pagination nếu null
            if (param.Pagination == null)
            {
                param.Pagination = new Pagination { PageIndex = 1, PageSize = 15 };
            }

            var userRoles = await _userService.GetRolesAsync(currentUser.UserId);
            var isFounder = userRoles.Any(r => r.Equals("Founder", StringComparison.OrdinalIgnoreCase));
            var canApprove = isFounder || userRoles.Any(r => r.Equals("TransferPaymentBill.Approve", StringComparison.OrdinalIgnoreCase));
            try
            {
                // Chuẩn bị parameters cho stored procedure
                string searchTerm = null;
                if (param.Search?.PredicateObject != null)
                {
                    dynamic search = param.Search.PredicateObject;
                    if (search.Keyword != null)
                    {
                        searchTerm = search.Keyword;
                        searchTerm = searchTerm?.Trim();
                    }
                }
                var saleId = (int?)null;
                if (!canApprove)
                {
                    saleId = currentUser.UserId;
                }

                var parameters = new DynamicParameters();
                parameters.Add("@SearchTerm", string.IsNullOrEmpty(searchTerm) ? null : searchTerm);
                parameters.Add("@PageIndex", param.Pagination.PageIndex);
                parameters.Add("@PageSize", param.Pagination.PageSize);
                parameters.Add("@SaleId", saleId);

                // Gọi stored procedure
                var spResults = (await _dbConnection.QueryAsync<CustomerTransferPaymentSummaryResult>(
                    "SP_SearchCustomersForTransferPayment",
                    parameters,
                    commandType: CommandType.StoredProcedure)).ToList();

                // Convert từ SP result sang DTO
                var items = spResults.Select(sp => new CustomerTransferPaymentSummaryItem
                {
                    CustomerId = sp.CustomerId,
                    CustomerName = sp.CustomerName,
                    CustomerPhone = sp.CustomerPhone,
                    CustomerEmail = sp.CustomerEmail,
                    CustomerAddress = sp.CustomerAddress,
                    PendingOrderCount = sp.PendingOrderCount,
                    EstimatedNetAmount = sp.TotalPendingAmount,
                    OldestOrderDate = sp.OldestOrderDate,
                    LatestOrderDate = sp.LatestOrderDate,
                    ManagerId = sp.ManagerId ?? 0,
                    ManagerName = sp.ManagerName,
                    CanCreateBill = sp.CanCreateBill
                }).ToList();

                // Lấy thông tin pagination từ kết quả đầu tiên
                var totalRecord = spResults.FirstOrDefault()?.TotalRecord ?? 0;

                return new SmartTableResult<CustomerTransferPaymentSummaryItem>
                {
                    Items = items,
                    TotalRecord = totalRecord,

                };
            }
            catch (Exception ex)
            {
                // Log error và return empty result
                // TODO: Add proper logging
                return new SmartTableResult<CustomerTransferPaymentSummaryItem>
                {
                    Items = new List<CustomerTransferPaymentSummaryItem>(),
                    TotalRecord = 0,
                    NumberOfPages = 0
                };
            }
        }

        /// <summary>
        /// Lấy danh sách đơn hàng có thể tạo hóa đơn thanh toán của khách hàng
        /// </summary>
        public async Task<CustomerTransferableOrdersModel> GetTransferableOrdersByCustomer(
            int customerId,
            CurrentUser currentUser)
        {
            var model = new CustomerTransferableOrdersModel();
            // Lấy thông tin khách hàng
            var customer = await _customerService.GetById(customerId);
            var userRoles = await _userService.GetRolesAsync(currentUser.UserId);
            var isFounder = userRoles.Any(r => r.Equals("Founder", StringComparison.OrdinalIgnoreCase));
            var canApprove = isFounder || userRoles.Any(r => r.Equals("TransferPaymentBill.Approve", StringComparison.OrdinalIgnoreCase));

            if (!canApprove && customer.Manager?.Id != currentUser.UserId)
            {
                return model;
            }
            else
            {
                try
                {
                    // Gọi stored procedure để lấy danh sách đơn hàng
                    var parameters = new DynamicParameters();
                    parameters.Add("@CustomerId", customerId);

                    var spResults = (await _dbConnection.QueryAsync<TransferableOrderResult>(
                        "SP_GetTransferableOrdersByCustomer",
                        parameters,
                        commandType: CommandType.StoredProcedure)).ToList();

                    // Convert từ SP result sang DTO
                    var orderItems = spResults.Select(sp => new TransferPaymentBillOrderItem
                    {
                        OrderId = sp.OrderId,
                        OrderCode = sp.OrderCode,
                        TransferFeeAmount = sp.TransferFeeAmount,
                        OrderCreatedTime = sp.OrderCreatedTime ?? DateTimeOffset.Now,
                        SenderName = sp.SenderName ?? "",
                        SenderAddress = sp.SenderAddress ?? "",
                        SenderPhone = sp.SenderPhone ?? "",
                        ReceiverName = sp.ReceiverName ?? "",
                        ReceiverAddress = sp.ReceiverAddress ?? "",
                        ReceiverPhone = sp.ReceiverPhone ?? "",
                        Status = (OrderStatus)sp.Status,
                        PhysicalStatus = (OrderPhysicalStatus)sp.PhysicalStatus,
                        ProductInfo = sp.ProductInfo ?? ""
                    }).ToList();

                    model = new CustomerTransferableOrdersModel
                    {
                        Customer = new CustomerInfoModel
                        {
                            Id = customerId,
                            FullName = customer?.FullName ?? "",
                            PhoneNumber = customer?.PhoneNumber ?? "",
                            Email = customer?.Email ?? "",
                            Address = customer?.Address ?? "",
                            UserName = customer?.UserName ?? ""
                        },
                        Orders = orderItems
                    };
                }
                catch (Exception ex)
                {
                    // Log error và return empty result
                    // TODO: Add proper logging
                    var orderItems = new List<TransferPaymentBillOrderItem>();

                    model = new CustomerTransferableOrdersModel
                    {
                        Customer = new CustomerInfoModel
                        {
                            Id = customerId,
                            FullName = customer?.FullName ?? "",
                            PhoneNumber = customer?.PhoneNumber ?? "",
                            Email = customer?.Email ?? "",
                            Address = customer?.Address ?? "",
                            UserName = customer?.UserName ?? ""
                        },
                        Orders = orderItems
                    };

                }

                return model;
            }
        }

        #endregion

        #region Create Operation

        /// <summary>
        /// Tạo hóa đơn thanh toán chuyển khoản
        /// </summary>
        public async Task<TransferPaymentBillResult> Create(CreateTransferPaymentBillModel model, CurrentUser currentUser)
        {
            try
            {
                var result = new TransferPaymentBillResult();
                var userRoles = await _userService.GetRolesAsync(currentUser.UserId);
                var isFounder = userRoles.Any(r => r.Equals("Founder", StringComparison.OrdinalIgnoreCase));
                var customer = await _customerService.GetById(model.CustomerId);
                if (!isFounder && customer.Manager?.Id != currentUser.UserId)
                {
                    result.Result = Result.Failed;
                    result.Message = "Bạn không được tạo phiếu thanh toán khi khách hàng có quản lý không phải bạn!";

                    return result;
                }
                else
                {
                    // Validate orders
                    var orders = await _orderRepository.Query()
                        .Include(x => x.OrderFin)
                        .Where(x => model.OrderIds.Contains(x.Id))
                        .ToListAsync();

                    if (orders.Count != model.OrderIds.Count)
                    {
                        return new TransferPaymentBillResult
                        {
                            Result = Result.Failed,
                            Message = "Một số đơn hàng không tồn tại"
                        };
                    }

                    // Validate all orders belong to the same customer and are eligible
                    var invalidOrders = orders.Where(x =>
                        x.CustomerId != model.CustomerId ||
                        x.OrderFin?.PayFeeType != PayFeeType.BankTransfer ||
                        x.OrderFin?.TransferFeeStage != OrderFeeStage.NotCollected ||
                        x.OrderFin?.TransferFee <= 0).ToList();

                    if (invalidOrders.Any())
                    {
                        return new TransferPaymentBillResult
                        {
                            Result = Result.Failed,
                            Message = "Một số đơn hàng không đủ điều kiện tạo hóa đơn thanh toán"
                        };
                    }

                    // Validate image file (exactly 1 image required)
                    if (model.Files == null || model.Files.Count != 1)
                    {
                        return new TransferPaymentBillResult
                        {
                            Result = Result.Failed,
                            Message = "Cần upload đúng 1 ảnh chứng từ chuyển khoản"
                        };
                    }

                    // Validate image content trước khi tạo phiếu để tránh rollback sau này
                    var uploadedImageFile = model.Files[0];
                    var isValidImage = await _unifiedMediaService.ValidateImageAsync(uploadedImageFile);
                    if (!isValidImage)
                    {
                        return new TransferPaymentBillResult
                        {
                            Result = Result.Failed,
                            Message = "File tải lên không phải là ảnh hợp lệ"
                        };
                    }

                    // Generate unique bill code
                    var billCode = await GenerateTransferPaymentBillCode();

                    // Calculate totals
                    var totalAmount = orders.Sum(x => x.OrderFin!.TransferFee);
                    var orderCount = orders.Count;

                    // Calculate priority based on oldest order
                    var oldestOrderDate = orders.Min(x => x.CreatedTime ?? DateTimeOffset.Now);
                    var priority = CalculatePriority(oldestOrderDate);

                    // Create bill entity
                    var bill = model.ToEntity();
                    bill.Code = billCode;
                    bill.TotalAmount = totalAmount;
                    bill.OrderCount = orderCount;
                    bill.Priority = priority;
                    // Logic tự động duyệt dựa trên business rules (có thể mở rộng sau)
                    bill.Status = TransferPaymentBillStatus.Draft;
                    bill.CreatedBy = currentUser.UserId;
                    bill.CreatedByUserName = currentUser.UserName;
                    bill.UpdatedBy = currentUser.UserId;
                    bill.UpdatedByUserName = currentUser.UserName;
                    bill.CreatedTime = DateTimeOffset.Now;
                    bill.UpdatedTime = DateTimeOffset.Now;

                    // Không tự động duyệt - luôn tạo ở trạng thái Draft
                    // Logic duyệt sẽ được thực hiện qua API riêng biệt

                    await _transferPaymentBillRepository.InsertAsync(bill);

                    // Create bill order items
                    foreach (var order in orders)
                    {
                        var billOrder = new TransferPaymentBillOrder
                        {
                            TransferPaymentBillId = bill.Id,
                            OrderId = order.Id,
                            OrderCode = order.Code,
                            TransferFeeAmount = order.OrderFin!.TransferFee,
                            CreatedBy = currentUser.UserId,
                            CreatedByUserName = currentUser.UserName,
                            UpdatedBy = currentUser.UserId,
                            UpdatedByUserName = currentUser.UserName,
                            CreatedTime = DateTimeOffset.Now,
                            UpdatedTime = DateTimeOffset.Now
                        };

                        await _transferPaymentBillOrderRepository.InsertAsync(billOrder);

                        // Update order TransferFeeCashier info
                        order.OrderFin!.TransferFeeCashierId = currentUser.UserId;
                        order.OrderFin!.TransferFeeCashierUsername = currentUser.UserName;
                        order.OrderFin!.TransferFeeStage = OrderFeeStage.Sale;

                        await _orderFinRepository.UpdateAsync(order.OrderFin);

                        // Create order track
                        await _orderTrackService.CreateAsync(new OrderTrackModel
                        {
                            OrderId = order.Id,
                            OrderCode = order.Code,
                            Name = $"Đã tạo hóa đơn nhận tiền từ khách với mã hóa đơn {billCode}",
                            TrackActionType = TrackActionType.Add, // Sẽ thêm enum mới sau
                            CreatedBy = currentUser.UserId,
                            CreatedByUserName = currentUser.UserName,
                            PhoneContact = "" // CurrentUser không có PhoneNumber
                        });
                    }

                    // Process and save image
                    var imageFile = model.Files[0];
                    var processResult = await _unifiedMediaService.ProcessImageAsync(imageFile, ImageType.Document);

                    if (processResult.Result != Result.Success)
                    {
                        // Rollback: Delete the created bill if image processing fails
                        await _transferPaymentBillRepository.DeleteAsync(bill);
                        return new TransferPaymentBillResult
                        {
                            Result = Result.Failed,
                            Message = $"Lỗi khi xử lý ảnh: {processResult.Message}"
                        };
                    }

                    // Save media record to database
                    var media = new Media
                    {
                        Id = Guid.NewGuid(),
                        SystemPath = processResult.SystemPath,
                        AltText = $"Chứng từ chuyển khoản - {billCode}",
                        Caption = $"Ảnh chứng từ chuyển khoản cho hóa đơn {billCode}",
                        DisplayOrder = 1,
                        CreatedTime = DateTimeOffset.Now,
                        UpdatedTime = DateTimeOffset.Now,
                        CreatedBy = currentUser.UserId,
                        CreatedByUserName = currentUser.UserName,
                        UpdatedBy = currentUser.UserId,
                        UpdatedByUserName = currentUser.UserName
                    };

                    await _mediaRepository.InsertAsync(media);

                    // Cập nhật MediaId cho TransferPaymentBill
                    bill.MediaId = media.Id;
                    await _transferPaymentBillRepository.UpdateAsync(bill);

                    return new TransferPaymentBillResult
                    {
                        Result = Result.Success,
                        Message = "Tạo hóa đơn thanh toán thành công",
                        Id = bill.Id,
                        Code = billCode,
                        ProcessedOrderCount = orderCount,
                        TotalAmount = totalAmount
                    };
                }

            }
            catch (Microsoft.EntityFrameworkCore.DbUpdateException ex) when (ex.InnerException?.Message?.Contains("duplicate key") == true)
            {
                // Retry một lần nữa với code mới khi gặp duplicate key
                try
                {
                    var newBillCode = await GenerateTransferPaymentBillCode();
                    var retryBill = model.ToEntity();
                    retryBill.Code = newBillCode;

                    // Copy lại các thông tin đã tính toán
                    var orders = await _orderRepository.Query()
                        .Include(x => x.OrderFin)
                        .Where(x => model.OrderIds.Contains(x.Id))
                        .ToListAsync();

                    var totalAmount = orders.Sum(x => x.OrderFin!.TransferFee);
                    var orderCount = orders.Count;
                    var oldestOrderDate = orders.Min(x => x.CreatedTime ?? DateTimeOffset.Now);
                    var priority = CalculatePriority(oldestOrderDate);

                    retryBill.TotalAmount = totalAmount;
                    retryBill.OrderCount = orderCount;
                    retryBill.Priority = priority;
                    retryBill.Status = TransferPaymentBillStatus.Draft;
                    retryBill.CreatedBy = currentUser.UserId;
                    retryBill.CreatedByUserName = currentUser.UserName;
                    retryBill.UpdatedBy = currentUser.UserId;
                    retryBill.UpdatedByUserName = currentUser.UserName;
                    retryBill.CreatedTime = DateTimeOffset.Now;
                    retryBill.UpdatedTime = DateTimeOffset.Now;

                    await _transferPaymentBillRepository.InsertAsync(retryBill);

                    return new TransferPaymentBillResult
                    {
                        Result = Result.Success,
                        Message = "Tạo hóa đơn thanh toán thành công",
                        Id = retryBill.Id,
                        Code = newBillCode,
                        ProcessedOrderCount = orderCount,
                        TotalAmount = totalAmount
                    };
                }
                catch (Exception retryEx)
                {
                    return new TransferPaymentBillResult
                    {
                        Result = Result.Failed,
                        Message = $"Lỗi khi tạo hóa đơn (retry failed): {retryEx.Message}"
                    };
                }
            }
            catch (Exception ex)
            {
                return new TransferPaymentBillResult
                {
                    Result = Result.Failed,
                    Message = $"Lỗi khi tạo hóa đơn: {ex.Message}"
                };
            }
        }

        #endregion

        #region Helper Methods

        /// <summary>
        /// Tính mức độ ưu tiên dựa trên ngày tạo đơn hàng cũ nhất
        /// </summary>
        public TransferPaymentBillPriority CalculatePriority(DateTimeOffset oldestOrderDate)
        {
            var daysDiff = (DateTimeOffset.Now - oldestOrderDate).TotalDays;

            if (daysDiff >= 10)
                return TransferPaymentBillPriority.High;
            else if (daysDiff >= 5)
                return TransferPaymentBillPriority.Medium;
            else
                return TransferPaymentBillPriority.Low;
        }

        /// <summary>
        /// Generate mã hóa đơn không trùng lặp với retry logic
        /// </summary>
        private async Task<string> GenerateTransferPaymentBillCode()
        {
            var today = DateTimeOffset.Now;
            var prefix = $"TPB{today:yyMMdd}"; // TPB241220

            // Retry logic để tránh duplicate key khi có concurrent requests
            for (int attempt = 0; attempt < 5; attempt++)
            {
                var lastCode = await _transferPaymentBillRepository.QueryAll()
                    .Where(r => r.Code.StartsWith(prefix))
                    .OrderByDescending(r => r.Code)
                    .Select(r => r.Code)
                    .FirstOrDefaultAsync();

                int sequence = 1;
                if (!string.IsNullOrEmpty(lastCode) && lastCode.Length >= prefix.Length + 3)
                {
                    var sequenceStr = lastCode.Substring(prefix.Length);
                    if (int.TryParse(sequenceStr, out int lastSequence))
                    {
                        sequence = lastSequence + 1;
                    }
                }

                var newCode = $"{prefix}{sequence:D3}"; // TPB241220001, TPB241220002, ...

                // Kiểm tra xem code đã tồn tại chưa
                var exists = await _transferPaymentBillRepository.Query()
                    .AnyAsync(r => r.Code == newCode);

                if (!exists)
                {
                    return newCode;
                }

                // Nếu code đã tồn tại, đợi một chút rồi thử lại
                await Task.Delay(50 * (attempt + 1)); // 50ms, 100ms, 150ms, 200ms, 250ms
            }

            // Fallback: sử dụng timestamp để đảm bảo unique
            var timestamp = DateTimeOffset.Now.ToString("HHmmssfff");
            return $"{prefix}{timestamp}";
        }

        #endregion

        #region Search & Get Operations

        /// <summary>
        /// Tìm kiếm danh sách hóa đơn thanh toán chuyển khoản
        /// </summary>
        public async Task<SmartTableResult<TransferPaymentBillItem>> Search(SmartTableParam param, CurrentUser currentUser)
        {
            var userRoles = await _userService.GetRolesAsync(currentUser.UserId);
            var isFounder = userRoles.Any(r => r.Equals("Founder", StringComparison.OrdinalIgnoreCase));

            var canApprove = isFounder || userRoles.Any(r => r.Equals("TransferPaymentBill.Approve", StringComparison.OrdinalIgnoreCase));

            IQueryable<TransferPaymentBill> query = _transferPaymentBillRepository.Query()
                .Include(x => x.Customer);

            // Filter theo quyền: Founder xem tất cả, user thường chỉ xem phiếu do mình tạo hoặc của khách hàng do mình quản lý
            if (!isFounder && !canApprove)
            {
                query = query.Where(x => x.CreatedBy == currentUser.UserId || x.Customer.ManagerId == currentUser.UserId);
            }

            // Apply search filters
            if (param.Search?.PredicateObject != null)
            {
                dynamic search = param.Search.PredicateObject;

                if (search.Keyword != null)
                {
                    string keyword = search.Keyword;
                    keyword = keyword.Trim().ToLower();
                    if (!string.IsNullOrEmpty(keyword))
                    {
                        query = query.Where(x =>
                            x.Code.ToLower().Contains(keyword) ||
                            x.Customer.FullName.ToLower().Contains(keyword) ||
                            x.Customer.PhoneNumber.Contains(keyword));
                    }
                }

                if (search.Status != null)
                {
                    TransferPaymentBillStatus status = search.Status;
                    query = query.Where(x => x.Status == status);
                }

                if (search.Priority != null)
                {
                    TransferPaymentBillPriority priority = search.Priority;
                    query = query.Where(x => x.Priority == priority);
                }

                if (search.CustomerId != null)
                {
                    int customerId = search.CustomerId;
                    query = query.Where(x => x.CustomerId == customerId);
                }

                if (search.CreatedStart != null && search.CreatedEnd != null)
                {
                    DateTimeOffset start = search.CreatedStart;
                    DateTimeOffset end = search.CreatedEnd;
                    DateTimeOffset startParam = start.Date;
                    DateTimeOffset endParam = end.Date.AddDays(1).AddTicks(-1);
                    query = query.Where(x => x.CreatedTime >= startParam && x.CreatedTime <= endParam);
                }

                if (search.AmountFrom != null)
                {
                    decimal amountFrom = search.AmountFrom;
                    query = query.Where(x => x.TotalAmount >= amountFrom);
                }

                if (search.AmountTo != null)
                {
                    decimal amountTo = search.AmountTo;
                    query = query.Where(x => x.TotalAmount <= amountTo);
                }
            }

            // Apply default sorting
            param.Sort = new Sort() { Predicate = "CreatedTime", Reverse = true };
            var gridData = await query.ToSmartTableResultAsync(param, x => x.ToItem());

            // Calculate permissions for each item
            if (gridData.Items.Any())
            {

                foreach (var item in gridData.Items)
                {
                    item.CanEdit = item.Status == TransferPaymentBillStatus.Draft && item.CreatedBy == currentUser.UserId;
                    item.CanCancel = item.Status == TransferPaymentBillStatus.Draft && item.CreatedBy == currentUser.UserId;
                    item.CanApprove = item.Status == TransferPaymentBillStatus.Draft && canApprove;
                }
            }

            return gridData;
        }

        /// <summary>
        /// Lấy chi tiết hóa đơn thanh toán theo ID
        /// </summary>
        public async Task<TransferPaymentBillDetailModel?> GetById(int id, CurrentUser currentUser)
        {
            var userRoles = await _userService.GetRolesAsync(currentUser.UserId);
            var isFounder = userRoles.Any(r => r.Equals("Founder", StringComparison.OrdinalIgnoreCase));

            var bill = await _transferPaymentBillRepository.Query()
                .Include(x => x.Customer)
                .Include(x => x.Orders)
                .ThenInclude(x => x.Order)
                .ThenInclude(x => x.Addresses)
                .Include(x => x.Orders)
                .ThenInclude(x => x.Order)
                .ThenInclude(x => x.OrderOps)
                .Include(x => x.Media)
                .FirstOrDefaultAsync(x => x.Id == id);

            if (bill == null)
                return null;

            // Check permission - người tạo hoặc có quyền duyệt có thể xem


            var result = new TransferPaymentBillDetailModel
            {
                Id = bill.Id,
                Code = bill.Code,
                CustomerId = bill.CustomerId,
                CustomerName = bill.Customer?.FullName ?? "",
                CustomerPhone = bill.Customer?.PhoneNumber ?? "",
                CustomerEmail = bill.Customer?.Email ?? "",
                CustomerAddress = bill.Customer?.Address ?? "",

                TotalAmount = bill.TotalAmount,
                OrderCount = bill.OrderCount,
                Status = bill.Status,
                Priority = bill.Priority,

                Note = bill.Note,
                TransferInfo = bill.TransferInfo,
                CreatedBy = bill.CreatedBy,
                CreatedByUserName = bill.CreatedByUserName,
                CreatedTime = bill.CreatedTime ?? DateTimeOffset.Now,
                UpdatedBy = bill.UpdatedBy,
                UpdatedByUserName = bill.UpdatedByUserName,
                UpdatedTime = bill.UpdatedTime ?? DateTimeOffset.Now,
                ApprovedBy = bill.ApprovedBy,
                ApprovedByUsername = bill.ApprovedByUsername,
                ApprovedDate = bill.ApprovedDate,
                TransactionId = bill.TransactionId,
                Orders = bill.Orders.Select(o =>
                {
                    // Lấy địa chỉ nguồn và đích
                    var sourceAddress = o.Order?.Addresses?.FirstOrDefault(a => a.AddressType == AddressType.Source);
                    var destAddress = o.Order?.Addresses?.FirstOrDefault(a => a.AddressType == AddressType.Dest);

                    return new TransferPaymentBillOrderItem
                    {
                        OrderId = o.OrderId,
                        OrderCode = o.OrderCode,
                        TransferFeeAmount = o.TransferFeeAmount,
                        OrderCreatedTime = o.Order?.CreatedTime ?? DateTimeOffset.Now,
                        SenderName = sourceAddress?.Name ?? "",
                        SenderAddress = sourceAddress?.Address1 ?? "",
                        ReceiverName = destAddress?.Name ?? "",
                        ReceiverAddress = destAddress?.Address1 ?? "",
                        Status = o.Order?.OrderOps?.Status ?? OrderStatus.Unknown,
                        PhysicalStatus = o.Order?.OrderOps?.PhysicalStatus ?? OrderPhysicalStatus.Unknown
                    };
                }).ToList(),
                MediaId = bill.MediaId,
                MediaUrl = bill.Media != null && !string.IsNullOrEmpty(bill.Media.SystemPath)
                    ? await _unifiedMediaService.GetUrlBySystemPathAsync(bill.Media.SystemPath)
                    : "",
                CanEdit = false, // Sẽ được tính toán sau
                CanCancel = false, // Sẽ được tính toán sau
                CanApprove = false // Sẽ được tính toán sau
            };

            // Cập nhật permissions (sử dụng lại userRoles và isFounder đã khai báo ở trên)
            var canApprove = isFounder || userRoles.Any(r => r.Equals("TransferPaymentBill.Approve", StringComparison.OrdinalIgnoreCase));

            result.CanEdit = bill.Status == TransferPaymentBillStatus.Draft && bill.CreatedBy == currentUser.UserId;
            result.CanCancel = bill.Status == TransferPaymentBillStatus.Draft && bill.CreatedBy == currentUser.UserId;
            result.CanApprove = bill.Status == TransferPaymentBillStatus.Draft && canApprove;

            return result;
        }

        #endregion

        #region Cancel & Approve Operations

        /// <summary>
        /// Hủy hóa đơn thanh toán chuyển khoản với thông tin chi tiết
        /// </summary>
        public async Task<TransferPaymentBillResult> Cancel(CancelTransferPaymentBillModel model)
        {
            try
            {
                // Validate model
                var validationErrors = model.Validate();
                if (validationErrors.Any())
                {
                    return new TransferPaymentBillResult
                    {
                        Result = Result.Failed,
                        Message = string.Join("; ", validationErrors)
                    };
                }

                var userRoles = await _userService.GetRolesAsync(model.CancelledBy);
                var isFounder = userRoles.Any(r => r.Equals("Founder", StringComparison.OrdinalIgnoreCase));
                var canApprove = isFounder || userRoles.Any(r => r.Equals("TransferPaymentBill.Approve", StringComparison.OrdinalIgnoreCase));

                var bill = await _transferPaymentBillRepository.Query()
                    .Include(x => x.Orders)
                    .ThenInclude(x => x.Order)
                    .ThenInclude(x => x.OrderFin)
                    .FirstOrDefaultAsync(x => x.Id == model.TransferPaymentBillId);

                if (bill == null)
                {
                    return new TransferPaymentBillResult
                    {
                        Result = Result.Failed,
                        Message = "Không tìm thấy hóa đơn"
                    };
                }

                // Check permission - chỉ người tạo hoặc có quyền approve mới có thể hủy
                if (bill.CreatedBy != model.CancelledBy && !isFounder && !canApprove)
                {
                    return new TransferPaymentBillResult
                    {
                        Result = Result.Failed,
                        Message = "Bạn không có quyền hủy hóa đơn này"
                    };
                }

                // Validate status
                if (bill.Status != TransferPaymentBillStatus.Draft)
                {
                    return new TransferPaymentBillResult
                    {
                        Result = Result.Failed,
                        Message = "Chỉ có thể hủy hóa đơn ở trạng thái Nháp"
                    };
                }

                // Update bill status và thông tin hủy
                bill.Status = TransferPaymentBillStatus.Cancelled;
                bill.UpdatedBy = model.CancelledBy;
                bill.UpdatedByUserName = model.CancelledByUsername;
                bill.UpdatedTime = model.CancelledTime;

                // Cập nhật Note với lý do hủy
                bill.Note = string.IsNullOrEmpty(bill.Note)
                    ? $"Hủy: {model.CancelReason}"
                    : $"{bill.Note}\nHủy: {model.CancelReason}";

                if (!string.IsNullOrEmpty(model.Note))
                {
                    bill.Note += $"\nGhi chú: {model.Note}";
                }

                await _transferPaymentBillRepository.UpdateAsync(bill);

                // Reset TransferFeeCashier info for all orders
                foreach (var billOrder in bill.Orders)
                {
                    if (billOrder.Order?.OrderFin != null)
                    {
                        billOrder.Order.OrderFin.TransferFeeCashierId = 0;
                        billOrder.Order.OrderFin.TransferFeeCashierUsername = "";
                        billOrder.Order.OrderFin.TransferFeeStage = OrderFeeStage.NotCollected;
                        await _orderFinRepository.UpdateAsync(billOrder.Order.OrderFin);
                    }
                }

                // Tạo OrderTrack nếu được yêu cầu
                foreach (var billOrder in bill.Orders)
                {
                    await _orderTrackService.CreateAsync(new OrderTrackModel
                    {
                        OrderId = billOrder.OrderId,
                        OrderCode = billOrder.OrderCode,
                        Name = $"Hủy hóa đơn thanh toán: {model.CancelReason}",
                        TrackActionType = TrackActionType.Cancel,
                        ActionSource = ActionSource.InternalWeb,
                        OrderTrackType = OrderTrackType.ForInternal,
                        CreatedBy = model.CancelledBy,
                        CreatedByUserName = model.CancelledByUsername,
                        PhoneContact = ""
                    });
                }



                return new TransferPaymentBillResult
                {
                    Result = Result.Success,
                    Message = "Hủy hóa đơn thành công",
                    Id = bill.Id,
                    Code = bill.Code
                };
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Lỗi khi hủy hóa đơn thanh toán chuyển khoản: {Message}", ex.Message);
                return new TransferPaymentBillResult
                {
                    Result = Result.Failed,
                    Message = "Có lỗi xảy ra khi hủy hóa đơn"
                };
            }
        }

        /// <summary>
        /// Hủy hóa đơn thanh toán chuyển khoản (phương thức cũ - deprecated)
        /// </summary>
        [Obsolete("Sử dụng Cancel(CancelTransferPaymentBillModel model) thay thế")]
        public async Task<TransferPaymentBillResult> Cancel(int id, CurrentUser currentUser)
        {
            // Gọi method mới với model được tạo từ tham số cũ
            var model = new CancelTransferPaymentBillModel
            {
                TransferPaymentBillId = id,
                CancelReason = "Hủy hóa đơn từ hệ thống",
                Note = "",
                CancelledBy = currentUser.UserId,
                CancelledByUsername = currentUser.UserName,
                CancelledTime = DateTimeOffset.UtcNow,
                CreateOrderTrack = true,
                SendNotification = false
            };

            return await Cancel(model);
        }

        /// <summary>
        /// Duyệt hóa đơn thanh toán chuyển khoản
        /// </summary>
        public async Task<TransferPaymentBillResult> Approve(ApproveTransferPaymentBillModel model, CurrentUser currentUser)
        {
            try
            {
                var bill = await _transferPaymentBillRepository.Query()
                    .Include(x => x.Orders)
                    .ThenInclude(x => x.Order)
                    .ThenInclude(x => x.OrderFin)
                    .FirstOrDefaultAsync(x => x.Id == model.Id);

                if (bill == null)
                {
                    return new TransferPaymentBillResult
                    {
                        Result = Result.Failed,
                        Message = "Không tìm thấy hóa đơn"
                    };
                }

                // Check permission - chỉ Founder hoặc có quyền TransferPaymentBill.Approve
                var userRoles = await _userService.GetRolesAsync(currentUser.UserId);
                var isFounder = userRoles.Any(r => r.Equals("Founder", StringComparison.OrdinalIgnoreCase));
                var canConfirm = isFounder || userRoles.Any(r => r.Equals("TransferPaymentBill.Approve", StringComparison.OrdinalIgnoreCase));

                if (!canConfirm)
                {
                    return new TransferPaymentBillResult
                    {
                        Result = Result.Failed,
                        Message = "Bạn không có quyền duyệt hóa đơn này"
                    };
                }

                // Validate status
                if (bill.Status != TransferPaymentBillStatus.Draft)
                {
                    return new TransferPaymentBillResult
                    {
                        Result = Result.Failed,
                        Message = "Chỉ có thể duyệt hóa đơn ở trạng thái Nháp"
                    };
                }

                // Update bill status and approval info
                bill.Status = TransferPaymentBillStatus.Approved;
                bill.ApprovedBy = currentUser.UserId;
                bill.ApprovedByUsername = currentUser.UserName;
                bill.ApprovedDate = DateTimeOffset.Now;
                bill.UpdatedBy = currentUser.UserId;
                bill.UpdatedByUserName = currentUser.UserName;
                bill.UpdatedTime = DateTimeOffset.Now;

                // Update transfer info if provided
                //if (!string.IsNullOrEmpty(model.TransferInfo))
                //{
                //    bill.TransferInfo = model.TransferInfo;
                //}

                // Tạo giao dịch cộng tiền vào ví người duyệt và liên kết TransactionId với bill
                using (var dbtransaction = _walletRepository.BeginTransaction())
                {
                    try
                    {
                        var wallet = await _walletRepository.Query()
                            .FirstOrDefaultAsync(w => w.UserId == currentUser.UserId && w.WalletType == WalletType.Personal);
                        if (wallet == null)
                        {
                            wallet = new Wallet
                            {
                                UserId = currentUser.UserId,
                                Balance = 0,
                                WalletType = WalletType.Personal,
                                WalletStatus = WalletStatus.Active
                            };
                            wallet = wallet.UpdateCommonInt(currentUser.UserId, currentUser.UserName);
                            await _walletRepository.InsertAsync(wallet);
                        }

                        var transaction = new Transaction
                        {
                            Description = $"Nhận cước chuyển khoản từ hóa đơn {bill.Code}",
                            WalletId = wallet.Id,
                            Amount = bill.TotalAmount,
                            CashAmount = 0,
                            TransferAmount = bill.TotalAmount,
                            Note = bill.Note,
                            FromUserId = bill.CustomerId,
                            TransactionType = TransactionType.CollectFromCustomer,
                            TransactionDate = DateTimeOffset.Now,
                            TransactionDirection = TransactionDirection.Income
                        };
                        transaction = transaction.UpdateCommonGuid(currentUser.UserId, currentUser.UserName);

                        await _transactionRepository.InsertAsync(transaction);

                        // Cập nhật số dư ví
                        wallet.Balance += bill.TotalAmount;
                        await _walletRepository.UpdateAsync(wallet);

                        // Liên kết giao dịch với hóa đơn
                        bill.TransactionId = transaction.Id;
                        await _transferPaymentBillRepository.UpdateAsync(bill);

                        await dbtransaction.CommitAsync();
                    }
                    catch (Exception ex)
                    {
                        await dbtransaction.RollbackAsync();
                        return new TransferPaymentBillResult
                        {
                            Result = Result.Failed,
                            Message = $"Lỗi khi tạo giao dịch ví: {ex.Message}"
                        };
                    }
                }

                // Update TransferFeeCashier info for all orders
                foreach (var billOrder in bill.Orders)
                {
                    if (billOrder.Order?.OrderFin != null)
                    {
                        billOrder.Order.OrderFin.TransferFeeCashierId = currentUser.UserId;
                        billOrder.Order.OrderFin.TransferFeeCashierUsername = currentUser.UserName;
                        billOrder.Order.OrderFin.TransferFeeStage = OrderFeeStage.Head; // Cập nhật stage thành Head
                        await _orderFinRepository.UpdateAsync(billOrder.Order.OrderFin);

                        // Cập nhật thời gian nhận cước trong OrderTimeTrack
                        await _orderTimeTrackService.UpdateFinancialTimesAsync(
                            orderId: billOrder.OrderId,
                            transferFeeStage: OrderFeeStage.Head,
                            updatedBy: currentUser.UserId,
                            updatedByUserName: currentUser.UserName,
                            currentTime: DateTimeOffset.Now
                        );

                        // Create order track
                        await _orderTrackService.CreateAsync(new OrderTrackModel
                        {
                            OrderId = billOrder.OrderId,
                            OrderCode = billOrder.OrderCode,
                            Name = $"Đã nhận tiền cước chuyển khoản từ hóa đơn {bill.Code}",
                            TrackActionType = TrackActionType.CollectMoney,
                            CreatedBy = currentUser.UserId,
                            CreatedByUserName = currentUser.UserName,
                            PhoneContact = ""
                        });
                    }
                }

                return new TransferPaymentBillResult
                {
                    Result = Result.Success,
                    Message = "Duyệt hóa đơn thành công",
                    Id = bill.Id,
                    Code = bill.Code,
                    ProcessedOrderCount = bill.OrderCount,
                    TotalAmount = bill.TotalAmount
                };
            }
            catch (Exception ex)
            {
                return new TransferPaymentBillResult
                {
                    Result = Result.Failed,
                    Message = $"Lỗi khi duyệt hóa đơn: {ex.Message}"
                };
            }
        }

        #endregion
    }
}
