using System.Data.Common;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using MMDAL.DTO.Scheduling;
using MMDAL.Query.Scheduling;

namespace MMDAL.CustomCode.Scheduling
{
    public class WorkCenterShiftMapDAL : IWorkCenterShiftMapDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        public async Task<WorkCenterShiftMapDTO?> GetShiftMap(
            int shiftMapId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<WorkCenterShiftMapDTO>(
                    loginDTO, WorkCenterShiftMapQB.GET_SHIFTMAP,
                    new { ShiftMapId = shiftMapId, TenantId = loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<WorkCenterShiftMapDTO>> GetShiftMapList(
            int workCenterId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<WorkCenterShiftMapDTO>(
                    loginDTO, WorkCenterShiftMapQB.GET_SHIFTMAP_LIST,
                    new { WorkCenterId = workCenterId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        // Called by CalendarService during engine runs — returns all effective shifts for a date
        public async Task<IEnumerable<WorkCenterShiftMapDTO>> GetShiftMapForDate(
            int workCenterId, DateTime scheduleDate, byte dayOfWeek,
            LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<WorkCenterShiftMapDTO>(
                    loginDTO, WorkCenterShiftMapQB.GET_SHIFTMAP_FOR_DATE,
                    new { WorkCenterId = workCenterId, ScheduleDate = scheduleDate.Date, DayOfWeek = dayOfWeek, TenantId=loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<ShiftPatternDTO>> GetShiftPatternsForHorizonAsync(
            IEnumerable<int> workCenterIds, DateTime horizonFrom, DateTime horizonTo,
            LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<ShiftPatternDTO>(
                    loginDTO, WorkCenterShiftMapQB.GET_SHIFTMAPS_FOR_HORIZON,
                    new { WorkCenterIds = workCenterIds, HorizonFrom = horizonFrom.Date, HorizonTo = horizonTo.Date, TenantId = loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<Result<string>> SaveShiftMap(
            WorkCenterShiftMapDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.CreatedById  = loginDTO.UserId;
                dto.CreatedOn    = DateTime.UtcNow;
                dto.ModifiedById = loginDTO.UserId;
                dto.ModifiedOn   = DateTime.UtcNow;
                dto.TenantId     = loginDTO.ClientId;

                int rows = await _QueryExecutor.ExecuteAsync(
                    loginDTO, WorkCenterShiftMapQB.SAVE_SHIFTMAP, dto, tx).ConfigureAwait(false);

                return rows > 0
                    ? Result<string>.Success("Shift map saved.")
                    : Result<string>.Failure("Failed to save shift map.");
            }
            catch (Exception) { throw; }
        }

        public async Task<Result<string>> UpdateShiftMap(
            WorkCenterShiftMapDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.ModifiedById = loginDTO.UserId;
                dto.ModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor.ExecuteAsync(
                    loginDTO, WorkCenterShiftMapQB.UPDATE_SHIFTMAP, dto, tx).ConfigureAwait(false);

                return rows > 0
                    ? Result<string>.Success("Shift map updated.")
                    : Result<string>.Failure("Shift map not found.");
            }
            catch (Exception) { throw; }
        }

        public async Task<Result<string>> DeleteShiftMap(
            int shiftMapId, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                int rows = await _QueryExecutor.ExecuteAsync(
                    loginDTO, WorkCenterShiftMapQB.DELETE_SHIFTMAP,
                    new { ShiftMapId = shiftMapId }, tx).ConfigureAwait(false);

                return rows > 0
                    ? Result<string>.Success("Shift map deleted.")
                    : Result<string>.Failure("Shift map not found or already deleted.");
            }
            catch (Exception) { throw; }
        }
    }
}