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

namespace MMDAL.CustomCode.Scheduling
{
    public class SchedulingRunDAL : ISchedulingRunDAL
    {
        private const int BulkChunkSize = 5000;

        private readonly IQueryExecutor _QueryExecutor;

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

        public async Task SaveSchedulingRunLogAsync(
            SchedulingRunLogDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.SAVE_SCHEDULINGRUNLOG, dto, tx)
                    .ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateSchedulingRunStatusAsync(int runId, byte status, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_SCHEDULINGRUNLOG_STATUS,
                    new { SchedulingRunId = runId, Status = status }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateSchedulingRunCompleteAsync(SchedulingRunLogDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_SCHEDULINGRUNLOG_COMPLETE, dto)
                    .ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<SchedulingRunLogDTO?> GetSchedulingRunLogAsync(
            int runId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QuerySingleAsync<SchedulingRunLogDTO>(
                    loginDTO, SchedulingRunQB.GET_SCHEDULINGRUNLOG,
                    new { SchedulingRunId = runId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<SchedulingRunLogDTO>> GetSchedulingRunLogListAsync(
            LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<SchedulingRunLogDTO>(
                    loginDTO, SchedulingRunQB.GET_SCHEDULINGRUNLOG_LIST,
                    new { },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        // Chunked at 5,000 rows — never loop single ExecuteAsync for large result sets
        public async Task BulkInsertResourcePlanAsync(
            IEnumerable<ResourcePlanInsertDTO> rows, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                foreach (var chunk in rows.Chunk(BulkChunkSize))
                    await _QueryExecutor.BulkInsertAsync(
                        loginDTO, SchedulingRunQB.BULK_INSERT_RESOURCEPLAN, chunk)
                        .ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task BulkInsertBatchChainAsync(
            IEnumerable<BatchChainInsertDTO> rows, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                foreach (var chunk in rows.Chunk(BulkChunkSize))
                    await _QueryExecutor.BulkInsertAsync(
                        loginDTO, SchedulingRunQB.BULK_INSERT_BATCHCHAIN, chunk)
                        .ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task DeleteFutureUnlockedResourcePlanAsync(
            DateTime horizonFrom, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.DELETE_FUTURE_UNLOCKED_RESOURCEPLAN,
                    new { HorizonFrom = horizonFrom.Date }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<ResourcePlanInsertDTO>> GetResourcePlanByIndentAsync(
            int indentDetailId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<ResourcePlanInsertDTO>(
                    loginDTO, SchedulingRunQB.GET_RESOURCEPLAN_BY_INDENT,
                    new { IndentDetailId = indentDetailId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<ResourcePlanInsertDTO>> GetResourcePlanByMachineAsync(
            int machineId, DateTime dateFrom, DateTime dateTo, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<ResourcePlanInsertDTO>(
                    loginDTO, SchedulingRunQB.GET_RESOURCEPLAN_BY_MACHINE,
                    new { MachineId = machineId, DateFrom = dateFrom, DateTo = dateTo },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateIsLockedAsync(int resourcePlanId, byte isLocked, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_ISLOCKED,
                    new { ResourcePlanId = resourcePlanId, IsLocked = isLocked }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task SaveMountTaskAsync(MountTaskDTO dto, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.SAVE_MOUNT_TASK,
                    new { dto.TaskId, dto.MachineId, dto.MountStart, dto.MountEnd, dto.TenantId }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateResourcePlanMountIdAsync(int resourcePlanId, int taskId, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_RESOURCEPLAN_MOUNTID,
                    new { ResourcePlanId = resourcePlanId, TaskId = taskId,TenantId=loginDTO.ClientId }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateNestingPlanScheduleAsync(
            int nestingPlanId, DateTime start, DateTime end, int combineId, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_NESTINGPLAN_SCHEDULE,
                    new { NestingPlanId = nestingPlanId, Start = start, End = end, CombineId = combineId }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateIndentDetailMonitorGroupAsync(
            int indentDetailId, string monitorGroup, short sequence, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_INDENTDETAIL_MONITORGROUP,
                    new { IndentDetailId = indentDetailId, MonitorGroup = monitorGroup, MonitorGroupSequence = sequence }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task UpdateIndentDetailScheduleStatusAsync(int indentDetailId, byte status, LoginDTO loginDTO)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_INDENTDETAIL_SCHEDULESTATUS,
                    new { IndentDetailId = indentDetailId, ScheduleStatus = status }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task ResetIndentDetailScheduleStatusForHorizonAsync(
            DateTime horizonFrom, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.RESET_INDENTDETAIL_SCHEDULESTATUS_FOR_DELETED_PLANS,
                    new { HorizonFrom = horizonFrom.Date }).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task BulkInsertDecisionLogAsync(
            IEnumerable<EUDecisionLogDTO> rows, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                foreach (var chunk in rows.Chunk(BulkChunkSize))
                    await _QueryExecutor.BulkInsertAsync(
                        loginDTO, SchedulingRunQB.BULK_INSERT_DECISIONLOG, chunk)
                        .ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task BulkInsertTraceAsync(
            IEnumerable<SchedulingTraceEntryDTO> rows, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                foreach (var chunk in rows.Chunk(BulkChunkSize))
                    await _QueryExecutor.BulkInsertAsync(
                        loginDTO, SchedulingRunQB.BULK_INSERT_TRACE, chunk)
                        .ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<SchedulingTraceEntryDTO>> GetTraceForRunAsync(
            int runId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<SchedulingTraceEntryDTO>(
                    loginDTO, SchedulingRunQB.GET_TRACE_FOR_RUN,
                    new { SchedulingRunId = runId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<EUDecisionLogDTO>> GetDecisionLogForRunAsync(
            int runId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<EUDecisionLogDTO>(
                    loginDTO, SchedulingRunQB.GET_DECISIONLOG_FOR_RUN,
                    new { SchedulingRunId = runId, TenantId = loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        // ── Plan Lifecycle ────────────────────────────────────────────────────

        public async Task FirmPlanByIdsAsync(
            IEnumerable<int> resourcePlanIds, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_FIRM_PLAN,
                    new { ResourcePlanIds = resourcePlanIds, TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task FirmPlanByRunAsync(
            int schedulingRunId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_FIRM_PLAN_BY_RUN,
                    new { SchedulingRunId = schedulingRunId, TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task FirmPlanByWorkCenterAsync(
            int workCenterId, DateTime horizonFrom, DateTime horizonTo,
            LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_FIRM_PLAN_BY_WC,
                    new { WorkCenterId = workCenterId, HorizonFrom = horizonFrom.Date, HorizonTo = horizonTo.Date, TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task ReleasePlanAsync(
            IEnumerable<int> resourcePlanIds, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var planIds = resourcePlanIds.ToList();

                // Resolve IndentDetailIds before the transaction
                var indentDetailIds = (await _QueryExecutor.QueryAsync<int>(
                    loginDTO, SchedulingRunQB.GET_INDENTDETAILIDS_FOR_PLANS,
                    new { ResourcePlanIds = planIds, TenantId = loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false)).ToList();

                await using var tx = await _QueryExecutor.BeginTransactionAsync(loginDTO).ConfigureAwait(false);
                try
                {
                    // Remove stale TINDENTRESOURCE rows from prior plan runs
                    await _QueryExecutor.ExecuteAsync(
                        loginDTO, SchedulingRunQB.DELETE_STALE_INDENTRESOURCE,
                        new { IndentDetailIds = indentDetailIds, TenantId = loginDTO.ClientId }, tx)
                        .ConfigureAwait(false);

                    // Mark resource plan rows as Released
                    await _QueryExecutor.ExecuteAsync(
                        loginDTO, SchedulingRunQB.UPDATE_RELEASE_PLAN,
                        new { ResourcePlanIds = planIds, TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId }, tx)
                        .ConfigureAwait(false);

                    // Write to TINDENTRESOURCE for shop-floor dispatch
                    await _QueryExecutor.ExecuteAsync(
                        loginDTO, SchedulingRunQB.INSERT_INDENTRESOURCE_FROM_PLAN,
                        new { ResourcePlanIds = planIds, TenantId = loginDTO.ClientId }, tx)
                        .ConfigureAwait(false);

                    await tx.CommitAsync(ct).ConfigureAwait(false);
                }
                catch
                {
                    await tx.RollbackAsync(ct).ConfigureAwait(false);
                    throw;
                }
            }
            catch (Exception) { throw; }
        }

        public async Task StartWorkOrderAsync(
            int resourcePlanId, DateTime actualStart, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_START_WORKORDER,
                    new { ResourcePlanId = resourcePlanId, ActualStart = actualStart, TenantId = loginDTO.ClientId, ModifiedById = loginDTO.UserId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task StopWorkOrderAsync(
            StopWorkOrderDTO dto, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO, SchedulingRunQB.UPDATE_STOP_WORKORDER,
                    new
                    {
                        ResourcePlanId  = dto.ResourcePlanId,
                        ReleaseResource = dto.ReleaseResource ? 1 : 0,
                        ActualEnd       = DateTime.UtcNow,
                        StopReasonId    = dto.StopReasonId,
                        StopRemarks     = dto.StopRemarks,
                        TenantId        = loginDTO.ClientId,
                        ModifiedById    = loginDTO.UserId
                    },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }

        public async Task<PlanMachineDetailDTO?> GetPlanMachineAndDetailAsync(
            int resourcePlanId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                var rows = await _QueryExecutor.QueryAsync<PlanMachineDetailDTO>(
                    loginDTO, SchedulingRunQB.GET_PLAN_MACHINE_AND_DETAIL,
                    new { ResourcePlanId = resourcePlanId, TenantId = loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
                return rows.FirstOrDefault();
            }
            catch (Exception) { throw; }
        }

        public async Task<IEnumerable<PreBookedSlotDTO>> GetLockedFutureSlotsAsync(
            DateTime horizonFrom, DateTime horizonTo, LoginDTO loginDTO, CancellationToken ct = default)
        {
            try
            {
                return await _QueryExecutor.QueryAsync<PreBookedSlotDTO>(
                    loginDTO, SchedulingRunQB.GET_LOCKED_FUTURE_SLOTS,
                    new { HorizonFrom = horizonFrom.Date, HorizonTo = horizonTo.Date, TenantId = loginDTO.ClientId },
                    cancellationToken: ct).ConfigureAwait(false);
            }
            catch (Exception) { throw; }
        }
    }
}