using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using MMDAL.DTO.StockReservation;
using MMDAL.Query.StockReservation;
using Newtonsoft.Json;
using System.Data;
using System.Data.Common;

namespace MMDAL.CustomCode.StockReservation;

public class StockReservationDAL : IStockReservationDAL
{
    private readonly IQueryExecutor _qe;

    public StockReservationDAL(IQueryExecutor queryExecutor) => _qe = queryExecutor;

    public async Task<string> GetReservationAsync(
     int stockReservationId, LoginDTO loginDTO)
    {
        var dto = await GetReservationDtoAsync(stockReservationId, loginDTO, CancellationToken.None)
            .ConfigureAwait(false);

        return JsonConvert.SerializeObject(dto);
    }

    public async Task<StockReservationDTO?> GetReservationDtoAsync(
        int stockReservationId, LoginDTO loginDTO, CancellationToken ct)
    {
        return await _qe.QuerySingleAsync<StockReservationDTO>(
            loginDTO,
            StockReservationQB.GET_RESERVATION,
            new { StockReservationId = stockReservationId },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<PagedResult<StockReservationListDTO>> GetReservationListAsync(
        int ouId, byte? statusFilter, int? itemId,
        int page, int size,
        LoginDTO loginDTO, CancellationToken ct)
    {
        var param = new { TenantId = loginDTO.ClientId, OUId = ouId, StatusFilter = statusFilter, ItemId = itemId, PageOffset = page, PageSize = size };

        var rows  = await _qe.QueryAsync<StockReservationListDTO>(
            loginDTO, StockReservationQB.GET_RESERVATION_LIST, param, cancellationToken: ct).ConfigureAwait(false);
        int total = await _qe.ExecuteScalarAsync<int>(
            loginDTO, StockReservationQB.GET_RESERVATION_LIST_COUNT, param, cancellationToken: ct).ConfigureAwait(false);

        return new PagedResult<StockReservationListDTO> { Items = rows, TotalCount = total };
    }

    public async Task<IEnumerable<StockReservationListDTO>> GetReservationsByMrpRunAsync(
        int mrpRunId, LoginDTO loginDTO, CancellationToken ct)
    {
        return await _qe.QueryAsync<StockReservationListDTO>(
            loginDTO,
            StockReservationQB.GET_RESERVATIONS_BY_MRP_RUN,
            new { TenantId = loginDTO.ClientId, MrpRunId = mrpRunId },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<IEnumerable<StoreAvailabilityDTO>> GetStockAvailabilityAsync(
        int ouId, int itemId, int skuId, LoginDTO loginDTO, CancellationToken ct)
    {
        return await _qe.QueryAsync<StoreAvailabilityDTO>(
            loginDTO,
            StockReservationQB.GET_STOCK_AVAILABILITY,
            new { OUId = ouId, ItemId = itemId, SKUId = skuId },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> CheckDuplicateActiveAsync(
        int forObjectHeaderId, int forObjectId, int itemId, int skuId,
        LoginDTO loginDTO, CancellationToken ct)
    {
        return await _qe.ExecuteScalarAsync<int>(
            loginDTO,
            StockReservationQB.CHECK_DUPLICATE_ACTIVE,
            new { TenantId = loginDTO.ClientId, ForObjectHeaderId = forObjectHeaderId, ForObjectId = forObjectId, ItemId = itemId, SKUId = skuId },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> InsertReservationAsync(
    StockReservationDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        dto.CreatedById = loginDTO.UserId;
        dto.CreatedOn = DateTime.UtcNow;
        dto.ModifiedById = loginDTO.UserId;
        dto.ModifiedOn = DateTime.UtcNow;

        return await _qe.QuerySingleAsync<int>(
            loginDTO,
            StockReservationQB.INSERT_RESERVATION,
            dto,
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }
    public async Task UpdateReservationNumberAsync(
    int stockReservationId, string reservationNumber,
    LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.UPDATE_RESERVATION_NUMBER,
            new { StockReservationId = stockReservationId, ReservationNumber = reservationNumber, TenantId = loginDTO.ClientId },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> IncrementStockReservedAsync(
        int ouId, int itemId, int skuId, int storeId, decimal quantity,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.INCREMENT_STOCK_RESERVED,
            new { OUId = ouId, ItemId = itemId, SKUId = skuId, StoreId = storeId, Quantity = quantity },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> DecrementStockReservedAsync(
        int ouId, int itemId, int skuId, int storeId, decimal quantity,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.DECREMENT_STOCK_RESERVED,
            new { OUId = ouId, ItemId = itemId, SKUId = skuId, StoreId = storeId, Quantity = quantity },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> DecrementPipelinePendingAsync(
        int fromAllotedAllocationId, decimal quantity,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.DECREMENT_PIPELINE_PENDING,
            new { FromAllotedAllocationId = fromAllotedAllocationId, Quantity = quantity, TenantId = loginDTO.ClientId },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> IncrementPipelinePendingAsync(
        int fromAllotedAllocationId, decimal quantity,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.INCREMENT_PIPELINE_PENDING,
            new { FromAllotedAllocationId = fromAllotedAllocationId, Quantity = quantity, TenantId = loginDTO.ClientId },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> ReleaseReservationAsync(
        int stockReservationId, short version, string reason,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.RELEASE_RESERVATION,
            new
            {
                StockReservationId = stockReservationId,
                TenantId    = loginDTO.ClientId,
                Version     = version,
                DeletedOn   = DateTime.UtcNow,
                DeletedById = loginDTO.UserId,
                DeleteReason = reason,
                ModifiedById = loginDTO.UserId,
                ModifiedOn  = DateTime.UtcNow
            },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> ReduceReservedQuantityAsync(
        int stockReservationId, short version, decimal releaseQuantity,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.REDUCE_RESERVED_QUANTITY,
            new
            {
                StockReservationId = stockReservationId,
                TenantId       = loginDTO.ClientId,
                Version        = version,
                ReleaseQuantity = releaseQuantity,
                ModifiedById   = loginDTO.UserId,
                ModifiedOn     = DateTime.UtcNow
            },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task SaveFreeTrackAsync(
        int newId, string reservationNumber,
        StockReservationFreeTrackDTO dto,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.SAVE_FREE_TRACK,
            new
            {
                StockReservationId      = newId,
                TenantId                = loginDTO.ClientId,
                OUId                    = dto.OUId,
                ReservationNumber       = reservationNumber,
                AssignmentLevel         = (byte)AssignmentLevel.Document,
                ForObjectHeaderTypeId   = dto.ForObjectHeaderTypeId,
                ForObjectHeaderId       = dto.ForObjectHeaderId,
                ForObjectTypeId         = dto.ForObjectTypeId,
                ForObjectId             = dto.ForObjectId,
                ForAllotedAllocationId  = dto.ForAllotedAllocationId,
                ItemId                  = dto.ItemId,
                SKUId                   = dto.SKUId,
                Reason                  = dto.Reason,
                FreeTrackById           = loginDTO.UserId,
                CreatedById             = loginDTO.UserId,
                ModifiedById            = loginDTO.UserId
            },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> MarkReassignedAsync(
        int stockReservationId, short version,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.MARK_REASSIGNED,
            new
            {
                StockReservationId = stockReservationId,
                TenantId     = loginDTO.ClientId,
                Version      = version,
                ModifiedById = loginDTO.UserId,
                ModifiedOn   = DateTime.UtcNow
            },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> ConfirmTentativeByMrpRunAsync(
        int mrpRunId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.CONFIRM_TENTATIVE,
            new { TenantId = loginDTO.ClientId, MrpRunId = mrpRunId, ModifiedById = loginDTO.UserId, ModifiedOn = DateTime.UtcNow },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> ConfirmOneTentativeAsync(
        int stockReservationId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.CONFIRM_ONE_TENTATIVE,
            new { StockReservationId = stockReservationId, TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId, ModifiedOn = DateTime.UtcNow },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> UpdateIssuedQuantityAsync(
        int stockReservationId, decimal issuedQty,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.UPDATE_ISSUED_QUANTITY,
            new
            {
                StockReservationId = stockReservationId,
                TenantId     = loginDTO.ClientId,
                IssuedQty    = issuedQty,
                ModifiedById = loginDTO.UserId,
                ModifiedOn   = DateTime.UtcNow
            },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> MarkSubconOutAsync(
        StockReservationSubconDTO dto,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.MARK_SUBCON_OUT,
            new
            {
                StockReservationId          = dto.StockReservationId,
                TenantId                    = loginDTO.ClientId,
                Version                     = dto.Version,
                SubconObjectHeaderTypeId    = dto.SubconObjectHeaderTypeId,
                SubconObjectHeaderId        = dto.SubconObjectHeaderId,
                SubconLocation              = dto.SubconLocation,
                SubconSentOut               = dto.SentOutDate,
                SubconExpectedReturn        = dto.ExpectedReturnDate,
                ModifiedById                = loginDTO.UserId,
                ModifiedOn                  = DateTime.UtcNow
            },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<int> MarkSubconReturnedAsync(
        int stockReservationId, short version, decimal actualReturnedQty,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        return await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.MARK_SUBCON_RETURNED,
            new
            {
                StockReservationId  = stockReservationId,
                TenantId            = loginDTO.ClientId,
                Version             = version,
                ActualReturnedQty   = actualReturnedQty,
                ModifiedById        = loginDTO.UserId,
                ModifiedOn          = DateTime.UtcNow
            },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task InsertReassignmentLogAsync(
        ReassignmentLogDTO dto, int newLogId,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        await _qe.ExecuteAsync(
            loginDTO,
            StockReservationQB.INSERT_REASSIGNMENT_LOG,
            new
            {
                ReassignmentLogId           = newLogId,
                TenantId                    = loginDTO.ClientId,
                OriginalReservationId       = dto.OriginalReservationId,
                OrigForObjectHeaderTypeId   = (int?)null,
                OrigForObjectHeaderId       = (int?)null,
                OrigForObjectTypeId         = (int?)null,
                OrigForObjectId             = (int?)null,
                NewReservationId            = dto.NewReservationId,
                NewForObjectHeaderTypeId    = (int?)null,
                NewForObjectHeaderId        = (int?)null,
                NewForObjectTypeId          = (int?)null,
                NewForObjectId              = (int?)null,
                ReassignedQuantity          = dto.ReassignedQuantity,
                Reason                      = dto.Reason,
                ReassignedById              = loginDTO.UserId
            },
            tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<IEnumerable<ReassignmentLogDTO>> GetReassignmentLogAsync(
        int stockReservationId, LoginDTO loginDTO, CancellationToken ct)
    {
        return await _qe.QueryAsync<ReassignmentLogDTO>(
            loginDTO,
            StockReservationQB.GET_REASSIGNMENT_LOG,
            new { TenantId = loginDTO.ClientId, StockReservationId = stockReservationId },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task<IEnumerable<SubconTrackingReportDTO>> GetSubconTrackingAsync(
        int ouId, LoginDTO loginDTO, CancellationToken ct)
    {
        return await _qe.QueryAsync<SubconTrackingReportDTO>(
            loginDTO,
            StockReservationQB.GET_SUBCON_TRACKING,
            new { TenantId = loginDTO.ClientId, OUId = ouId },
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task BulkInsertReservationsAsync(
        IEnumerable<StockReservationDTO> reservations,
        LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
    {
        var now = DateTime.UtcNow;
        foreach (var r in reservations)
        {
            r.CreatedById  = loginDTO.UserId;
            r.CreatedOn    = now;
            r.ModifiedById = loginDTO.UserId;
            r.ModifiedOn   = now;
        }

        await _qe.BulkInsertAsync(
            loginDTO,
            StockReservationQB.INSERT_RESERVATION,
            reservations,
            tx).ConfigureAwait(false);
    }

    public async Task<string> GetSelectListStockReservation(
    int FirstNumber,
    int MaxResult,
    CriteriaDTO criteriaDTO,
    LoginDTO loginDTO,
    bool IsCount = false)
    {
        try
        {
            string json = "";

            if (!IsCount)
            {
                string sql = StockReservationQB.GET_SELECTLIST_STOCKRESERVATION;

                var parameter = new
                {
                    firstnumber = FirstNumber,
                    maxresult = MaxResult
                };

                List<StockReservationPicklistDTO> list =
                    (await _qe.QueryAsync<StockReservationPicklistDTO>(
                        loginDTO,
                        sql,
                        parameter)).ToList();

                json = JsonConvert.SerializeObject(list);
            }
            else
            {
                int count = 0; // Replace with count query later
                json = count.ToString();
            }

            return json;
        }
        catch (Exception)
        {
            throw;
        }
    }
}