using System.Data;
using Dapper;
using Lada.Framework.Data.Domains;
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.Helpers;
using Lada.Framework.Infrastructures.Services;
using Ladaer.BackEnd.Infrastructures.DTO;
using Ladaer.BackEnd.Infrastructures.DTO.Items.Business.Operations;
using Ladaer.BackEnd.Infrastructures.DTO.Items.DriverApp;
using Ladaer.BackEnd.Infrastructures.DTO.Items.Identity;
using Ladaer.BackEnd.Infrastructures.DTO.Items.Orders;
using Ladaer.BackEnd.Infrastructures.DTO.Models.DriverApp;
using Ladaer.BackEnd.Infrastructures.DTO.Models.Orders;
using Ladaer.BackEnd.Infrastructures.DTO.Models.Orders.Updates;
using Ladaer.BackEnd.Infrastructures.DTO.Results.Orders;
using Ladaer.BackEnd.Infrastructures.Services.Categories;
using Ladaer.BackEnd.Infrastructures.Services.Identity;
using Ladaer.BackEnd.Infrastructures.Services.Orders;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json.Linq;
using Xabe.FFmpeg;

namespace Ladaer.BackEnd.Infrastructures.Services.DriverApp
{
    /// <summary>
    /// Interface for Driver App order service.
    /// </summary>
    public interface IDriverAppOrderService
    {
        /// <summary>
        /// Searches for orders based on the provided parameters.
        /// </summary>
        /// <param name="param">Search parameters.</param>
        /// <param name="userId">User ID.</param>
        /// <returns>Search results.</returns>
        Task<SmartTableResult<DriverOrderHistoryItem>> Search(SmartTableParam param, int userId);

        /// <summary>
        /// Tìm kiếm đơn hàng đang hoạt động (đang lấy, lấy thất bại, đang giao, giao thất bại).
        /// </summary>
        /// <param name="param">Tham số tìm kiếm.</param>
        /// <param name="userId">ID người dùng.</param>
        /// <returns>Kết quả tìm kiếm.</returns>
        Task<SmartTableResult<OpsOrderItem>> SearchActiveOrders(SmartTableParam param, int userId);

        /// <summary>
        /// Gets an order by its ID.
        /// </summary>
        /// <param name="id">Order ID.</param>
        /// <param name="userId">User ID.</param>
        /// <returns>Order item.</returns>
        Task<OpsOrderItem> GetById(int id, int userId);

        /// <summary>
        /// Picks up an order.
        /// </summary>
        /// <param name="model">Pickup model.</param>
        /// <returns>Order track result.</returns>
        Task<OrderTrackResult> Pickup(DriverAppPickupModel model);

        /// <summary>
        /// Pickup failed.
        /// </summary>
        /// <param name="model">Fail model.</param>
        /// <returns>Order track result.</returns>
        Task<OrderTrackResult> PickupFail(DriverAppFailModel model);

        /// <summary>
        /// Delivery successful.
        /// </summary>
        /// <param name="model">Delivery model.</param>
        /// <returns>Order track result.</returns>
        Task<OrderTrackResult> Delivery(DriverAppDeliveryModel model);

        /// <summary>
        /// Delivery failed.
        /// </summary>
        /// <param name="model">Fail model.</param>
        /// <returns>Order track result.</returns>
        Task<OrderTrackResult> DeliveryFail(DriverAppFailModel model);

        /// <summary>
        /// Sắp xếp thứ tự hiển thị đơn hàng.
        /// </summary>
        /// <param name="model">Danh sách đơn hàng với thứ tự mới.</param>
        /// <param name="userId">ID người dùng.</param>
        /// <returns>Kết quả sắp xếp.</returns>
        Task<BaseResult> ReorderOrders(ReorderModel model, int userId);
    }

    /// <summary>
    /// Implementation of the Driver App order service.
    /// </summary>
    public class DriverAppOrderService : IDriverAppOrderService
    {
        private readonly IRepository<OrderOps> _orderOpsRepository;
        private readonly IRepository<OrderFin> _orderFinRepository;
        private readonly IRepository<Order> _orderRepository;
        private readonly ILocationService _locationService;
        private readonly IUserService _userService;
        private readonly IUnifiedMediaService _unifiedMediaService;
        private readonly IOrderTrackService _orderTrackService;
        private readonly IDbConnection _dbConnection;
        private readonly IRepository<Media> _mediaRepository;
        private readonly UserManager<User> _userManager;

        /// <summary>
        /// Initializes a new instance of the <see cref="DriverAppOrderService"/> class.
        /// </summary>
        public DriverAppOrderService(ILocationService locationService, IUserService userService,
            IRepository<OrderOps> orderOpsRepository,
            IRepository<OrderFin> orderFinRepository,
            IOrderTrackService orderTrackService,
            IRepository<Order> orderRepository, IUnifiedMediaService unifiedMediaService,
            IDbConnection dbConnection,
            IRepository<Media> mediaRepository,
            UserManager<User> userManager)
        {
            _orderOpsRepository = orderOpsRepository;
            _orderFinRepository = orderFinRepository;
            _orderRepository = orderRepository;
            _locationService = locationService;
            _userService = userService;
            _unifiedMediaService = unifiedMediaService;
            _orderTrackService = orderTrackService;
            _dbConnection = dbConnection;
            _mediaRepository = mediaRepository;
            _userManager = userManager;
        }

        /// <summary>
        /// Searches for orders based on the provided parameters.
        /// </summary>
        /// <param name="param">Search parameters.</param>
        /// <param name="userId">User ID.</param>
        /// <returns>Search results.</returns>
        public async Task<SmartTableResult<DriverOrderHistoryItem>> Search(SmartTableParam param, int userId)
        {
            SmartTableResult<DriverOrderHistoryItem> gridData = new SmartTableResult<DriverOrderHistoryItem>();
            var query = _orderOpsRepository.Query().Where(o => o.PickedupById == userId || o.DeliveredById == userId);
            if (param != null)
            {
                if (param.Search != null)
                {
                    if (param.Search.PredicateObject != null)
                    {
                        dynamic search = param.Search.PredicateObject;
                        if (search.Keyword != null)
                        {
                            string keyword = search.Keyword;
                            keyword = keyword.Trim();
                            if (!string.IsNullOrWhiteSpace(keyword))
                                query = query.Where(x =>
                                    x.Order.Code.Contains(keyword)
                                    || x.Order.Addresses.Any(a =>
                                        (a.PhoneNumber1 != null && a.PhoneNumber1.Contains(keyword))
                                        || (a.PhoneNumber2 != null && a.PhoneNumber2.Contains(keyword))
                                    )
                                );
                        }
                        string dateFilterType = search.DateFilterType ?? "pickup";

                        // Thu hẹp điều kiện theo loại lọc: pickup chỉ lấy đơn do userId lấy hàng, delivery chỉ lấy đơn do userId giao
                        if (dateFilterType == "delivery")
                            query = query.Where(x => x.DeliveredById == userId);
                        else
                            query = query.Where(x => x.PickedupById == userId);

                        if (search.DateFrom != null)
                        {
                            DateTimeOffset dateFrom = search.DateFrom;
                            dateFrom = dateFrom.ToLocalTime().DateTime.StartOfDay();
                            if (dateFilterType == "delivery")
                                query = query.Where(x => x.Order.OrderTimeTrack.DeliveryDate >= dateFrom);
                            else
                                query = query.Where(x => x.Order.OrderTimeTrack.PickupTime >= dateFrom);
                        }
                        if (search.DateTo != null)
                        {
                            DateTimeOffset dateTo = search.DateTo;
                            dateTo = dateTo.ToLocalTime().DateTime.EndOfDay();
                            if (dateFilterType == "delivery")
                                query = query.Where(x => x.Order.OrderTimeTrack.DeliveryDate <= dateTo);
                            else
                                query = query.Where(x => x.Order.OrderTimeTrack.PickupTime <= dateTo);
                        }
                    }
                }

                // Mặc định sắp xếp theo thời gian tạo giảm dần (lịch sử)
                param.Sort = new Sort { Predicate = "CreatedTime", Reverse = true };

                if (param?.Search?.PredicateObject != null)
                {
                    dynamic searchD = param.Search.PredicateObject;
                    if (searchD.DateFrom != null || searchD.DateTo != null)
                    {
                        string sortFilterType = searchD.DateFilterType ?? "pickup";
                        if (sortFilterType == "delivery")
                            param.Sort = new Sort { Predicate = "Order.OrderTimeTrack.DeliveryDate", Reverse = true };
                        else
                            param.Sort = new Sort { Predicate = "Order.OrderTimeTrack.PickupTime", Reverse = true };
                    }
                }

                gridData = await query.Include(o => o.Order.OrderFin)
                .Include(o => o.Order.Addresses)
                .Include(o => o.Order.Packages)
                .Include(o => o.Order.OrderTimeTrack)
                .ToSmartTableResultAsync(param, x => x.ToDriverOrderHistoryItem());
                if (gridData.Items.Any())
                {
                    var orderIds = gridData.Items.Select(x => x.OrderId).ToList();
                    var addresses = gridData.Items.Where(i => i.Addresses != null).SelectMany(i => i.Addresses).ToList();
                    var wardIds = addresses.Where(a => a.WardId > 0).Select(a => a.WardId).Distinct().ToList();
                    var fullAddresss = await _locationService.GetFullAddress(wardIds, 3);

                    var mediaRecords = await _mediaRepository.Query()
                        .Where(m => orderIds.Contains(m.OrderId) && m.CreatedBy == userId)
                        .Select(m => new { m.OrderId, m.SystemPath })
                        .ToListAsync();

                    // Batch lấy FullName cho tài xế lấy/giao
                    var pickedupByIds = gridData.Items.Select(x => x.PickedupByUserName).Where(u => !string.IsNullOrEmpty(u)).Distinct().ToList();
                    var deliveredByIds = gridData.Items.Select(x => x.DeliveredByUserName).Where(u => !string.IsNullOrEmpty(u)).Distinct().ToList();
                    var allUserNames = pickedupByIds.Union(deliveredByIds).Distinct().ToList();
                    var userFullNames = new Dictionary<string, string>();
                    if (allUserNames.Any())
                    {
                        userFullNames = await _userManager.Users
                            .Where(u => allUserNames.Contains(u.UserName))
                            .ToDictionaryAsync(u => u.UserName, u => u.FullName ?? "");
                    }

                    foreach (var order in gridData.Items)
                    {
                        // Populate FullName cho tài xế
                        if (!string.IsNullOrEmpty(order.PickedupByUserName) && userFullNames.TryGetValue(order.PickedupByUserName, out var pickupFullName))
                            order.PickedupByFullName = pickupFullName;
                        if (!string.IsNullOrEmpty(order.DeliveredByUserName) && userFullNames.TryGetValue(order.DeliveredByUserName, out var deliveryFullName))
                            order.DeliveredByFullName = deliveryFullName;

                        var senderAddress = order.Addresses?.FirstOrDefault(a => a.AddressType == AddressType.Source);
                        if (senderAddress != null)
                        {
                            order.SenderName = senderAddress.Name;
                            order.SenderPhone = string.IsNullOrEmpty(senderAddress.PhoneNumber1) ? senderAddress.PhoneNumber2 : senderAddress.PhoneNumber1;
                            if (senderAddress.WardId > 0)
                            {
                                var fullAddress = fullAddresss.FirstOrDefault(f => f.Id == senderAddress.WardId);
                                if (fullAddress != null)
                                {
                                    order.SenderAddress = $"{senderAddress.Address1}, {fullAddress.FullAddress}";
                                }
                                else order.SenderAddress = senderAddress.Address1;
                            }
                            else order.SenderAddress = senderAddress.Address1;
                        }

                        var receiverAddress = order.Addresses?.FirstOrDefault(a => a.AddressType == AddressType.Dest);
                        if (receiverAddress != null)
                        {
                            order.ReceiverName = receiverAddress.Name;
                            order.ReceiverPhone = string.IsNullOrEmpty(receiverAddress.PhoneNumber1) ? receiverAddress.PhoneNumber2 : receiverAddress.PhoneNumber1;
                            if (receiverAddress.WardId > 0)
                            {
                                var fullAddress = fullAddresss.FirstOrDefault(f => f.Id == receiverAddress.WardId);
                                if (fullAddress != null)
                                {
                                    order.ReceiverAddress = $"{receiverAddress.Address1}, {fullAddress.FullAddress}";
                                }
                                else order.ReceiverAddress = receiverAddress.Address1;
                            }
                            else order.ReceiverAddress = receiverAddress.Address1;
                        }

                        if (order.Packages != null && order.Packages.Any())
                        {
                            order.TotalPackages = order.Packages.Count;
                            var packageNames = order.Packages.Select(p => p.Name).Where(n => !string.IsNullOrEmpty(n));
                            order.PackageContent = string.Join(", ", packageNames);
                        }

                        var sysPaths = mediaRecords.Where(m => m.OrderId == order.OrderId).Select(m => m.SystemPath).ToList();
                        if (sysPaths.Any())
                        {
                            order.MediaUrls = await _unifiedMediaService.GetUrlsBySystemPathsAsync(sysPaths);
                        }
                    }
                }
            }
            return gridData;
        }

        /// <summary>
        /// Tìm kiếm đơn hàng đang hoạt động (đang lấy, lấy thất bại, đang giao, giao thất bại).
        /// </summary>
        /// <param name="param">Tham số tìm kiếm.</param>
        /// <param name="userId">ID người dùng.</param>
        /// <returns>Kết quả tìm kiếm.</returns>
        public async Task<SmartTableResult<OpsOrderItem>> SearchActiveOrders(SmartTableParam param, int userId)
        {
            SmartTableResult<OpsOrderItem> gridData = new SmartTableResult<OpsOrderItem>();
            var query = _orderOpsRepository.Query().Where(o => o.PicId == userId);

            // Lọc chỉ các đơn hàng đang hoạt động (9 tổ hợp trạng thái)
            query = query.Where(o =>
                // Đang lấy hàng
                (o.Status == OrderStatus.New && o.PhysicalStatus == OrderPhysicalStatus.AssignedPickup)
                || (o.Status == OrderStatus.New && o.PhysicalStatus == OrderPhysicalStatus.OnPicking)
                // Lấy thất bại
                || (o.Status == OrderStatus.New && o.PhysicalStatus == OrderPhysicalStatus.PickFailed)
                || (o.Status == OrderStatus.PickupFail && o.PhysicalStatus == OrderPhysicalStatus.OnPicking)
                || (o.Status == OrderStatus.PickupFail && o.PhysicalStatus == OrderPhysicalStatus.PickFailed)
                // Đang giao hàng
                || (o.Status == OrderStatus.Received && o.PhysicalStatus == OrderPhysicalStatus.OutboundForDelivery)
                || (o.Status == OrderStatus.Received && o.PhysicalStatus == OrderPhysicalStatus.OnDelivery)
                // Giao thất bại
                || (o.Status == OrderStatus.DeliveryFail && o.PhysicalStatus == OrderPhysicalStatus.OutboundForDelivery)
                || (o.Status == OrderStatus.DeliveryFail && o.PhysicalStatus == OrderPhysicalStatus.OnDelivery)
            );

            if (param != null)
            {
                if (param.Search != null)
                {
                    if (param.Search.PredicateObject != null)
                    {
                        dynamic search = param.Search.PredicateObject;
                        if (search.Keyword != null)
                        {
                            string keyword = search.Keyword;
                            keyword = keyword.Trim();
                            if (!string.IsNullOrWhiteSpace(keyword))
                                query = query.Where(x => x.Order.Code.Contains(keyword));
                        }
                    }
                }

                // Luôn sắp xếp theo DisplayOrder ASC
                param.Sort = new Sort { Predicate = "DisplayOrder", Reverse = false };

                gridData = await query.Include(o => o.Order.OrderFin)
                .Include(o => o.Order.Addresses)
                .Include(o => o.Order.Packages)
                .Include(o => o.Order.OrderTimeTrack)
                .ToSmartTableResultAsync(param, x => x.ToOpsOrderItem());
                if (gridData.Items.Any())
                {
                    var addresses = gridData.Items.SelectMany(i => i.Addresses).ToList();
                    var wardIds = addresses.Where(a => a.WardId > 0).Select(a => a.WardId).Distinct().ToList();
                    var fullAddresss = await _locationService.GetFullAddress(wardIds, 3);

                    var saleIds = gridData.Items.Select(i => i.SaleId).ToList();
                    var saleItems = new List<StaffItem>();
                    if (saleIds != null && saleIds.Any())
                    {
                        saleItems = await _userService.GetByListIdAsync(saleIds);
                    }

                    foreach (var order in gridData.Items)
                    {
                        if (order.SaleId > 0)
                        {
                            var sale = saleItems.FirstOrDefault(s => s.Id == order.SaleId);
                            if (sale != null && sale.Id > 0)
                            {
                                order.SaleUserName = sale.UserName;
                            }
                        }
                        int addIndex = 1;
                        foreach (var address in order.Addresses)
                        {
                            if (address.WardId > 0)
                            {
                                var fullAddress = fullAddresss.FirstOrDefault(f => f.Id == address.WardId);
                                if (fullAddress != null && fullAddress.Id > 0)
                                {
                                    address.FullAddress = $"{address.Address1}, {fullAddress.FullAddress}";
                                    if (addIndex == 1)
                                    {
                                        order.RouteName += fullAddress.CityName;
                                    }
                                    else
                                    {
                                        order.RouteName += $" - {fullAddress.CityName}";
                                    }
                                    addIndex++;
                                }
                            }
                        }
                    }
                }
            }
            return gridData;
        }

        /// <summary>
        /// Gets an order by its ID.
        /// </summary>
        /// <param name="id">Order ID.</param>
        /// <param name="userId">User ID.</param>
        /// <returns>Order item.</returns>
        public async Task<OpsOrderItem> GetById(int id, int userId)
        {
            var result = new OpsOrderItem();
            var query = _orderOpsRepository.Query().Where(o => o.PicId == userId && o.Id == id);
            result = await query.Include(o => o.Order.OrderFin)
                .Include(o => o.Order.Addresses)
                .Include(o => o.Order.Packages)
                .Include(o => o.Order.OrderTimeTrack)
                .Select(o => o.ToOpsOrderItem())
                .FirstOrDefaultAsync();
            if (result != null && result.Id > 0)
            {
                var addresses = result.Addresses;
                var wardIds = addresses.Where(a => a.WardId > 0).Select(a => a.WardId).Distinct().ToList();
                var fullAddresss = await _locationService.GetFullAddress(wardIds, 3);

                var saleId = result.SaleId;

                if (result.SaleId > 0)
                {
                    var sale = await _userService.GetByIdAsync(saleId);
                    if (sale != null && sale.Id > 0)
                    {
                        result.SaleUserName = sale.UserName;
                    }
                }
                int addIndex = 1;
                foreach (var address in result.Addresses)
                {
                    if (address.WardId > 0)
                    {
                        var fullAddress = fullAddresss.FirstOrDefault(f => f.Id == address.WardId);
                        if (fullAddress != null && fullAddress.Id > 0)
                        {
                            address.FullAddress = $"{address.Address1}, {fullAddress.FullAddress}";
                            if (addIndex == 1)
                            {
                                result.RouteName += fullAddress.CityName;
                            }
                            else
                            {
                                result.RouteName += $" - {fullAddress.CityName}";
                            }
                            addIndex++;
                        }
                    }
                }
            }
            else
            {
                result = new OpsOrderItem();
            }
            return result;
        }

        /// <summary>
        /// Picks up an order.
        /// </summary>
        /// <param name="model">Pickup model.</param>
        /// <returns>Order track result.</returns>
        public async Task<OrderTrackResult> Pickup(DriverAppPickupModel model)
        {
            var result = new OrderTrackResult();

            // 1. Validate input
            if (model.Id <= 0 || model.UpdatedBy <= 0 || string.IsNullOrEmpty(model.UpdatedByUserName))
            {
                result.Result = Result.Failed;
                result.Message = "Thông tin đầu vào không hợp lệ";
                return result;
            }

            try
            {
                // 2. Validate business rules (C# validation)
                var orderOps = await _orderOpsRepository.Query().FirstOrDefaultAsync(c => c.Id == model.Id && c.PicId == model.UpdatedBy);
                if (orderOps == null || orderOps.Id <= 0)
                {
                    result.Result = Result.Failed;
                    result.Message = "Không tìm thấy đơn hàng yêu cầu";
                    return result;
                }

                // Validate status transition
                var validUpdateStatus = ValidateUpdateStatus(orderOps.Status, orderOps.PhysicalStatus,
                    OrderStatus.Received, OrderPhysicalStatus.Picked);
                if (!validUpdateStatus)
                {
                    result.Result = Result.Failed;
                    result.Message = $"Trạng thái cập nhật không hợp lệ, trạng thái hiện tại đang là {orderOps.Status.GetDescription()} và {orderOps.PhysicalStatus.GetDescription()}";
                    return result;
                }

                // Get agency ID
                var agencyId = await _userService.GetAgencyIdByIdAsync(model.UpdatedBy);
                if (agencyId <= 0)
                {
                    result.Result = Result.Failed;
                    result.Message = "Không xác định được đại lý của người dùng";
                    return result;
                }

                // 3. Call stored procedure for atomic update
                var parameters = new DynamicParameters();
                parameters.Add("@OrderId", model.Id);
                parameters.Add("@PicId", model.UpdatedBy);
                parameters.Add("@PicUserName", model.UpdatedByUserName);
                parameters.Add("@AgencyId", agencyId);
                parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.Output);
                parameters.Add("@Message", dbType: DbType.String, direction: ParameterDirection.Output, size: 500);
                parameters.Add("@OrderTrackId", dbType: DbType.Guid, direction: ParameterDirection.Output);

                await _dbConnection.ExecuteAsync("SP_UpdateOrderPickup", parameters, commandType: CommandType.StoredProcedure);

                // 4. Get results from stored procedure
                var spResult = parameters.Get<int>("@Result");
                var spMessage = parameters.Get<string>("@Message");
                var orderTrackId = parameters.Get<Guid>("@OrderTrackId");

                result.Result = (Result)spResult;
                result.Message = spMessage;
                result.Id = orderTrackId;

                // 5. Handle media if SP was successful and media exists
                if (result.Result == Result.Success && model.MediaList != null && model.MediaList.Any())
                {
                    // Get order code for media processing
                    var orderCommon = await _orderRepository.Query().FirstOrDefaultAsync(c => c.Id == model.Id);
                    if (orderCommon != null)
                    {
                        var mediaResults = await _unifiedMediaService.SaveOrderTrackMediaAsync(
                            model.MediaList, result.Id, model.Id, orderCommon.Code,
                            model.UpdatedBy, model.UpdatedByUserName);

                        // Check for media save errors
                        var failedResult = mediaResults.FirstOrDefault(r => r.Result != Lada.Framework.DTO.Result.Success);
                        if (failedResult != null)
                        {
                            result.Result = Lada.Framework.DTO.Result.SystemError;
                            result.Message = $"Lỗi lưu ảnh: {failedResult.Message}";
                            return result;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result.Result = Result.SystemError;
                result.Message = $"Lỗi hệ thống: {ex.Message}";
            }

            return result;
        }

        /// <summary>
        /// Pickup failed.
        /// </summary>
        /// <param name="model">Fail model.</param>
        /// <returns>Order track result.</returns>
        public async Task<OrderTrackResult> PickupFail(DriverAppFailModel model)
        {
            var result = new OrderTrackResult();

            // 1. Validate input
            if (model.Id <= 0 || model.UpdatedBy <= 0 || string.IsNullOrEmpty(model.UpdatedByUserName))
            {
                result.Result = Result.Failed;
                result.Message = "Thông tin đầu vào không hợp lệ";
                return result;
            }

            try
            {
                // 2. Validate business rules (C# validation)
                var orderOps = await _orderOpsRepository.Query().FirstOrDefaultAsync(c => c.Id == model.Id && c.PicId == model.UpdatedBy);
                if (orderOps == null || orderOps.Id <= 0)
                {
                    result.Result = Result.Failed;
                    result.Message = "Không tìm thấy đơn hàng yêu cầu";
                    return result;
                }

                // Validate status transition
                var validUpdateStatus = ValidateUpdateStatus(orderOps.Status, orderOps.PhysicalStatus,
                    OrderStatus.PickupFail, OrderPhysicalStatus.OnPicking);
                if (!validUpdateStatus)
                {
                    result.Result = Result.Failed;
                    result.Message = $"Trạng thái cập nhật không hợp lệ, trạng thái hiện tại đang là {orderOps.Status.GetDescription()} và {orderOps.PhysicalStatus.GetDescription()}";
                    return result;
                }

                // Get agency ID
                var agencyId = await _userService.GetAgencyIdByIdAsync(model.UpdatedBy);
                if (agencyId <= 0)
                {
                    result.Result = Result.Failed;
                    result.Message = "Không xác định được đại lý của người dùng";
                    return result;
                }

                // 3. Call stored procedure for atomic update
                var parameters = new DynamicParameters();
                parameters.Add("@OrderId", model.Id);
                parameters.Add("@PicId", model.UpdatedBy);
                parameters.Add("@PicUserName", model.UpdatedByUserName);
                parameters.Add("@AgencyId", agencyId);
                parameters.Add("@Reason", model.Reason ?? "");
                parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.Output);
                parameters.Add("@Message", dbType: DbType.String, direction: ParameterDirection.Output, size: 500);
                parameters.Add("@OrderTrackId", dbType: DbType.Guid, direction: ParameterDirection.Output);

                await _dbConnection.ExecuteAsync("SP_UpdateOrderPickupFail", parameters, commandType: CommandType.StoredProcedure);

                // 4. Get results from stored procedure
                var spResult = parameters.Get<int>("@Result");
                var spMessage = parameters.Get<string>("@Message");
                var orderTrackId = parameters.Get<Guid>("@OrderTrackId");

                result.Result = (Result)spResult;
                result.Message = spMessage;
                result.Id = orderTrackId;

                // 5. Handle media if SP was successful and media exists
                if (result.Result == Result.Success && model.MediaList != null && model.MediaList.Any())
                {
                    var orderCommon = await _orderRepository.Query().FirstOrDefaultAsync(c => c.Id == model.Id);
                    if (orderCommon != null)
                    {
                        var mediaResults = await _unifiedMediaService.SaveOrderTrackMediaAsync(
                            model.MediaList, result.Id, model.Id, orderCommon.Code,
                            model.UpdatedBy, model.UpdatedByUserName);

                        var failedResult = mediaResults.FirstOrDefault(r => r.Result != Lada.Framework.DTO.Result.Success);
                        if (failedResult != null)
                        {
                            result.Result = Lada.Framework.DTO.Result.SystemError;
                            result.Message = $"Lỗi lưu ảnh: {failedResult.Message}";
                            return result;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result.Result = Result.SystemError;
                result.Message = $"Lỗi hệ thống: {ex.Message}";
            }

            return result;
        }

        /// <summary>
        /// Delivery successful.
        /// </summary>
        /// <param name="model">Delivery model.</param>
        /// <returns>Order track result.</returns>
        public async Task<OrderTrackResult> Delivery(DriverAppDeliveryModel model)
        {
            var result = new OrderTrackResult();

            // 1. Validate input
            if (model.Id <= 0 || model.UpdatedBy <= 0 || string.IsNullOrEmpty(model.UpdatedByUserName))
            {
                result.Result = Result.Failed;
                result.Message = "Thông tin đầu vào không hợp lệ";
                return result;
            }

            try
            {
                // 2. Validate business rules (C# validation)
                var orderOps = await _orderOpsRepository.Query().FirstOrDefaultAsync(c => c.Id == model.Id && c.PicId == model.UpdatedBy);
                if (orderOps == null || orderOps.Id <= 0)
                {
                    result.Result = Result.Failed;
                    result.Message = "Không tìm thấy đơn hàng yêu cầu";
                    return result;
                }

                // Validate status transition
                var validUpdateStatus = ValidateUpdateStatus(orderOps.Status, orderOps.PhysicalStatus,
                    OrderStatus.Delivered, OrderPhysicalStatus.Delivered);
                if (!validUpdateStatus)
                {
                    result.Result = Result.Failed;
                    result.Message = $"Trạng thái cập nhật không hợp lệ, trạng thái hiện tại đang là {orderOps.Status.GetDescription()} và {orderOps.PhysicalStatus.GetDescription()}";
                    return result;
                }

                // Get agency ID
                var agencyId = await _userService.GetAgencyIdByIdAsync(model.UpdatedBy);
                if (agencyId <= 0)
                {
                    result.Result = Result.Failed;
                    result.Message = "Không xác định được đại lý của người dùng";
                    return result;
                }

                // 3. Call stored procedure for atomic update
                var parameters = new DynamicParameters();
                parameters.Add("@OrderId", model.Id);
                parameters.Add("@PicId", model.UpdatedBy);
                parameters.Add("@PicUserName", model.UpdatedByUserName);
                parameters.Add("@AgencyId", agencyId);
                parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.Output);
                parameters.Add("@Message", dbType: DbType.String, direction: ParameterDirection.Output, size: 500);
                parameters.Add("@OrderTrackId", dbType: DbType.Guid, direction: ParameterDirection.Output);

                await _dbConnection.ExecuteAsync("SP_UpdateOrderDelivery", parameters, commandType: CommandType.StoredProcedure);

                // 4. Get results from stored procedure
                var spResult = parameters.Get<int>("@Result");
                var spMessage = parameters.Get<string>("@Message");
                var orderTrackId = parameters.Get<Guid>("@OrderTrackId");

                result.Result = (Result)spResult;
                result.Message = spMessage;
                result.Id = orderTrackId;

                // 5. Handle media if SP was successful and media exists
                if (result.Result == Result.Success && model.MediaList != null && model.MediaList.Any())
                {
                    // Get order code for media processing
                    var orderCommon = await _orderRepository.Query().FirstOrDefaultAsync(c => c.Id == model.Id);
                    if (orderCommon != null)
                    {
                        var mediaResults = await _unifiedMediaService.SaveOrderTrackMediaAsync(
                            model.MediaList, result.Id, model.Id, orderCommon.Code,
                            model.UpdatedBy, model.UpdatedByUserName);

                        // Check for media save errors
                        var failedResult = mediaResults.FirstOrDefault(r => r.Result != Lada.Framework.DTO.Result.Success);
                        if (failedResult != null)
                        {
                            result.Result = Lada.Framework.DTO.Result.SystemError;
                            result.Message = $"Lỗi lưu ảnh: {failedResult.Message}";
                            return result;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result.Result = Result.SystemError;
                result.Message = $"Lỗi hệ thống: {ex.Message}";
            }

            return result;
        }

        /// <summary>
        /// Delivery failed.
        /// </summary>
        /// <param name="model">Fail model.</param>
        /// <returns>Order track result.</returns>
        public async Task<OrderTrackResult> DeliveryFail(DriverAppFailModel model)
        {
            var result = new OrderTrackResult();

            // 1. Validate input
            if (model.Id <= 0 || model.UpdatedBy <= 0 || string.IsNullOrEmpty(model.UpdatedByUserName))
            {
                result.Result = Result.Failed;
                result.Message = "Thông tin đầu vào không hợp lệ";
                return result;
            }

            try
            {
                // 2. Validate business rules (C# validation)
                var orderOps = await _orderOpsRepository.Query().FirstOrDefaultAsync(c => c.Id == model.Id && c.PicId == model.UpdatedBy);
                if (orderOps == null || orderOps.Id <= 0)
                {
                    result.Result = Result.Failed;
                    result.Message = "Không tìm thấy đơn hàng yêu cầu";
                    return result;
                }

                // Validate status transition
                var validUpdateStatus = ValidateUpdateStatus(orderOps.Status, orderOps.PhysicalStatus,
                    OrderStatus.DeliveryFail, OrderPhysicalStatus.OnDelivery);
                if (!validUpdateStatus)
                {
                    result.Result = Result.Failed;
                    result.Message = $"Trạng thái cập nhật không hợp lệ, trạng thái hiện tại đang là {orderOps.Status.GetDescription()} và {orderOps.PhysicalStatus.GetDescription()}";
                    return result;
                }

                // Get agency ID
                var agencyId = await _userService.GetAgencyIdByIdAsync(model.UpdatedBy);
                if (agencyId <= 0)
                {
                    result.Result = Result.Failed;
                    result.Message = "Không xác định được đại lý của người dùng";
                    return result;
                }

                // 3. Call stored procedure for atomic update
                var parameters = new DynamicParameters();
                parameters.Add("@OrderId", model.Id);
                parameters.Add("@PicId", model.UpdatedBy);
                parameters.Add("@PicUserName", model.UpdatedByUserName);
                parameters.Add("@AgencyId", agencyId);
                parameters.Add("@Reason", model.Reason ?? "");
                parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.Output);
                parameters.Add("@Message", dbType: DbType.String, direction: ParameterDirection.Output, size: 500);
                parameters.Add("@OrderTrackId", dbType: DbType.Guid, direction: ParameterDirection.Output);

                await _dbConnection.ExecuteAsync("SP_UpdateOrderDeliveryFail", parameters, commandType: CommandType.StoredProcedure);

                // 4. Get results from stored procedure
                var spResult = parameters.Get<int>("@Result");
                var spMessage = parameters.Get<string>("@Message");
                var orderTrackId = parameters.Get<Guid>("@OrderTrackId");

                result.Result = (Result)spResult;
                result.Message = spMessage;
                result.Id = orderTrackId;

                // 5. Handle media if SP was successful and media exists
                if (result.Result == Result.Success && model.MediaList != null && model.MediaList.Any())
                {
                    var orderCommon = await _orderRepository.Query().FirstOrDefaultAsync(c => c.Id == model.Id);
                    if (orderCommon != null)
                    {
                        var mediaResults = await _unifiedMediaService.SaveOrderTrackMediaAsync(
                            model.MediaList, result.Id, model.Id, orderCommon.Code,
                            model.UpdatedBy, model.UpdatedByUserName);

                        var failedResult = mediaResults.FirstOrDefault(r => r.Result != Lada.Framework.DTO.Result.Success);
                        if (failedResult != null)
                        {
                            result.Result = Lada.Framework.DTO.Result.SystemError;
                            result.Message = $"Lỗi lưu ảnh: {failedResult.Message}";
                            return result;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result.Result = Result.SystemError;
                result.Message = $"Lỗi hệ thống: {ex.Message}";
            }

            return result;
        }

        /// <summary>
        /// Sắp xếp thứ tự hiển thị đơn hàng.
        /// </summary>
        /// <param name="model">Danh sách đơn hàng với thứ tự mới.</param>
        /// <param name="userId">ID người dùng.</param>
        /// <returns>Kết quả sắp xếp.</returns>
        public async Task<BaseResult> ReorderOrders(ReorderModel model, int userId)
        {
            var result = new BaseResult { Result = Result.Success };

            if (model.Items == null || !model.Items.Any())
            {
                result.Result = Result.Failed;
                result.Message = "Danh sách sắp xếp không được để trống";
                return result;
            }

            try
            {
                var orderIds = model.Items.Select(i => i.OrderId).ToList();

                // Lấy tất cả đơn hàng cần sắp xếp (chỉ những đơn thuộc user hiện tại)
                var orders = await _orderOpsRepository.Query()
                    .Where(o => orderIds.Contains(o.Id) && o.PicId == userId && !o.IsDeleted)
                    .ToListAsync();

                if (!orders.Any())
                {
                    result.Result = Result.Failed;
                    result.Message = "Không tìm thấy đơn hàng nào để sắp xếp";
                    return result;
                }

                // Cập nhật DisplayOrder cho từng đơn
                foreach (var item in model.Items)
                {
                    var order = orders.FirstOrDefault(o => o.Id == item.OrderId);
                    if (order != null)
                    {
                        order.DisplayOrder = item.DisplayOrder;
                    }
                }

                foreach (var order in orders)
                {
                    await _orderOpsRepository.UpdateAsync(order);
                }
            }
            catch (Exception ex)
            {
                result.Result = Result.SystemError;
                result.Message = $"Lỗi hệ thống: {ex.Message}";
            }

            return result;
        }

        #region Utilities
        /// <summary>
        /// Validates the update status of an order.
        /// </summary>
        /// <param name="currentStatus">Current order status.</param>
        /// <param name="currentPhysicalStatus">Current physical status.</param>
        /// <param name="newStatus">New order status.</param>
        /// <param name="newPhysicalStatus">New physical status.</param>
        /// <returns>True if the update is valid, otherwise false.</returns>
        private bool ValidateUpdateStatus(OrderStatus currentStatus, OrderPhysicalStatus currentPhysicalStatus, OrderStatus newStatus, OrderPhysicalStatus newPhysicalStatus)
        {
            var result = false;
            switch (newStatus)
            {
                case OrderStatus.New:
                    if (currentStatus == OrderStatus.Cancelled && currentPhysicalStatus == OrderPhysicalStatus.Cancelled)
                    {
                        result = true;
                    }
                    break;
                case OrderStatus.Cancelled:
                    if (currentStatus == OrderStatus.New &&
                        (currentPhysicalStatus == OrderPhysicalStatus.WaitToPickup ||
                        currentPhysicalStatus == OrderPhysicalStatus.OnPicking))
                    {
                        result = true;
                    }
                    break;
                case OrderStatus.Received:
                    if ((currentStatus == OrderStatus.New || currentStatus == OrderStatus.PickupFail)
                        && (currentPhysicalStatus == OrderPhysicalStatus.WaitToPickup || currentPhysicalStatus == OrderPhysicalStatus.OnPicking))
                    {
                        result = true;
                    }
                    break;
                case OrderStatus.PickupFail:
                    if ((currentStatus == OrderStatus.New || currentStatus == OrderStatus.PickupFail)
                        && (currentPhysicalStatus == OrderPhysicalStatus.WaitToPickup || currentPhysicalStatus == OrderPhysicalStatus.OnPicking))
                    {
                        result = true;
                    }
                    break;
                case OrderStatus.DeliveryFail:
                    if ((currentStatus == OrderStatus.Received || currentStatus == OrderStatus.DeliveryFail)
                        && (currentPhysicalStatus == OrderPhysicalStatus.OnDelivery || currentPhysicalStatus == OrderPhysicalStatus.OutboundForDelivery))
                    {
                        result = true;
                    }
                    break;
                case OrderStatus.Delivered:
                    if ((currentStatus == OrderStatus.Received || currentStatus == OrderStatus.DeliveryFail)
                        && (currentPhysicalStatus == OrderPhysicalStatus.OnDelivery || currentPhysicalStatus == OrderPhysicalStatus.OutboundForDelivery))
                    {
                        result = true;
                    }
                    break;

                case OrderStatus.WaitToReturn:
                    break;
                case OrderStatus.ReturnFail:
                    break;
                case OrderStatus.Returned:
                    break;
                case OrderStatus.Unknown:
                    break;
                default:
                    break;
            }
            return result;
        }
        #endregion
    }
}