using System.Data.Common;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using MMDAL.DTO.PartLevel;
using MMDAL.Query.PartLevel;
using static GB5Shared.GB5Constant.Constant;
using static GB5Shared.QueryExecutor.QueryExecutor;

namespace MMDAL.CustomCode.PartLevel
{
    public class PartDetailDAL : IPartDetailDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public PartDetailDAL(IQueryExecutor queryExecutor)
            => _QueryExecutor = queryExecutor;

        public async Task<PartDetailDTO?> GetPartDetail(int partDetailId, LoginDTO loginDTO, CancellationToken ct)
            => await _QueryExecutor
                .QuerySingleAsync<PartDetailDTO>(loginDTO, PartDetailQB.GET_PARTDETAIL,
                    new { PartDetailId = partDetailId }, cancellationToken: ct)
                .ConfigureAwait(false);

        public async Task<IEnumerable<PartDetailDTO>> GetPartDetailLineage(
            int rootPartDetailId, LoginDTO loginDTO, CancellationToken ct)
            => await _QueryExecutor
                .QueryAsync<PartDetailDTO>(loginDTO, PartDetailQB.GET_PARTDETAIL_LINEAGE,
                    new { RootPartDetailId = rootPartDetailId }, cancellationToken: ct)
                .ConfigureAwait(false) ?? [];

        public async Task<PagedResult<PartDetailListDTO>> GetPartDetailList(
            PartDetailCriteriaDTO criteria, LoginDTO loginDTO, CancellationToken ct)
        {
            var (dataSql, countSql, p) = PartDetailQB.BuildPartDetailList(criteria);
            var items = await _QueryExecutor.QueryAsync<PartDetailListDTO>(loginDTO, dataSql, p, cancellationToken: ct)
                .ConfigureAwait(false);
            var total = await _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, countSql, p, cancellationToken: ct)
                .ConfigureAwait(false);
            return new PagedResult<PartDetailListDTO> { Items = items?.ToList() ?? [], TotalCount = total };
        }

        public async Task SavePartDetail(
            PartDetailDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            dto.CreatedById  = loginDTO.UserId;
            dto.CreatedOn    = DateTime.UtcNow;
            dto.ModifiedById = loginDTO.UserId;
            dto.ModifiedOn   = DateTime.UtcNow;

            await _QueryExecutor
                .ExecuteAsync(loginDTO, PartDetailQB.SAVE_PARTDETAIL, dto, tx, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task DeletePartDetailsByEntity(
            int entityTypeId, int entityId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
            => await _QueryExecutor
                .ExecuteAsync(loginDTO, PartDetailQB.DELETE_PARTDETAILS_BY_ENTITY,
                    new { EntityTypeId = entityTypeId, EntityId = entityId, TenantId = loginDTO.ClientId }, tx, cancellationToken: ct)
                .ConfigureAwait(false);

        public async Task<int> GetFirstPartDetailIdByEntity(
            int entityTypeId, int entityId, LoginDTO loginDTO, CancellationToken ct)
        {
            string sql = loginDTO.DatabaseType switch
            {
                DBType.SQL     => PartDetailQB.GET_FIRST_PARTDETAILID_BY_ENTITY,
                DBType.PostGre => PartDetailQB.GET_FIRST_PARTDETAILID_BY_ENTITY_PG,
                _              => PartDetailQB.GET_FIRST_PARTDETAILID_BY_ENTITY
            };
            return await _QueryExecutor
                .ExecuteScalarAsync<int>(loginDTO, sql,
                    new { EntityTypeId = entityTypeId, EntityId = entityId, TenantId = loginDTO.ClientId },
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task UpdatePartDetailLineage(
            int entityTypeId, int entityId, int? parentPartDetailId, int rootPartDetailId,
            LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            string sql = loginDTO.DatabaseType switch
            {
                DBType.SQL     => PartDetailQB.UPDATE_PARTDETAIL_LINEAGE,
                DBType.PostGre => PartDetailQB.UPDATE_PARTDETAIL_LINEAGE_PG,
                _              => PartDetailQB.UPDATE_PARTDETAIL_LINEAGE
            };
            await _QueryExecutor
                .ExecuteAsync(loginDTO, sql,
                    new { EntityTypeId = entityTypeId, EntityId = entityId,
                          ParentPartDetailId = parentPartDetailId, RootPartDetailId = rootPartDetailId,
                          TenantId = loginDTO.ClientId }, tx, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task UpdatePartDetailStatus(
            int partDetailId, short status, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
            => await _QueryExecutor
                .ExecuteAsync(loginDTO, PartDetailQB.UPDATE_PARTDETAIL_STATUS,
                    new { PartDetailId = partDetailId, Status = status,
                          ModifiedById = loginDTO.UserId, ModifiedOn = DateTime.UtcNow }, tx, cancellationToken: ct)
                .ConfigureAwait(false);

        public async Task SyncPartDetailWIP(
            int partDetailId, decimal wipStock, decimal availableToWork,
            LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
            => await _QueryExecutor
                .ExecuteAsync(loginDTO, PartDetailQB.UPDATE_PARTDETAIL_WIP,
                    new { PartDetailId = partDetailId, WipStock = wipStock, AvailableToWork = availableToWork,
                          ModifiedById = loginDTO.UserId, ModifiedOn = DateTime.UtcNow }, tx, cancellationToken: ct)
                .ConfigureAwait(false);
    }
}