using System.Data.Common;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using QMSDAL.DTO;
using QMSDAL.Query.PcsCardMaterial;

namespace QMSDAL.CustomCode.PcsCardMaterial
{
    public class PcsCardMaterialDAL : IPcsCardMaterialDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public PcsCardMaterialDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }

        public async Task<IEnumerable<PcsCardMaterialDTO>> GetByStageIdAsync(
            int pcsCardStaeId, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var param  = new { PcsCardStaeId = pcsCardStaeId };
                var result = await _QueryExecutor
                    .QueryAsync<PcsCardMaterialDTO>(login, PcsCardMaterialQB.GET_BY_STAGE, param, cancellationToken: ct)
                    .ConfigureAwait(false);
                return result ?? Enumerable.Empty<PcsCardMaterialDTO>();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<IEnumerable<PcsCardMaterialDTO>> GetByCardIdAsync(
            int pcsCardId, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var param  = new { PcsCardId = pcsCardId };
                var result = await _QueryExecutor
                    .QueryAsync<PcsCardMaterialDTO>(login, PcsCardMaterialQB.GET_BY_CARD, param, cancellationToken: ct)
                    .ConfigureAwait(false);
                return result ?? Enumerable.Empty<PcsCardMaterialDTO>();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> HasMaterialsForStageAsync(
            int pcsCardStaeId, LoginDTO login, CancellationToken ct = default)
        {
            try
            {
                var param = new { PcsCardStaeId = pcsCardStaeId };
                int count = await _QueryExecutor
                    .ExecuteScalarAsync<int>(login, PcsCardMaterialQB.HAS_MATERIALS_FOR_STAGE, param)
                    .ConfigureAwait(false);
                return count > 0;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task SaveAllAsync(
            int pcsCardStaeId, IEnumerable<PcsCardMaterialDTO> rows,
            LoginDTO login, DbTransaction tx, CancellationToken ct = default)
        {
            try
            {
                // Delete existing rows for this stage then re-insert
                await _QueryExecutor
                    .ExecuteAsync(login, PcsCardMaterialQB.DELETE_BY_STAGE, new { PcsCardStaeId = pcsCardStaeId }, tx, ct)
                    .ConfigureAwait(false);

                short slNo = 1;
                foreach (var row in rows)
                {
                    row.PcsCardStaeId      = pcsCardStaeId;
                    row.SlNo               = slNo++;
                    // Compute ISEXPIRED server-side; ignore client-supplied value
                    row.IsExpired          = (row.DateOfExpiry.HasValue && row.DateOfExpiry.Value.Date < DateTime.UtcNow.Date)
                                            ? (byte)1 : (byte)0;

                    await _QueryExecutor
                        .ExecuteAsync(login, PcsCardMaterialQB.INSERT_MATERIAL, row, tx, ct)
                        .ConfigureAwait(false);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}