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

namespace MMDAL.CustomCode.MasterSchedule
{
    public class MasterScheduleDAL : IMasterScheduleDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // ── Get single record (header + details) ──────────────────────────

        public async Task<MasterScheduleDTO?> GetMasterSchedule(int MasterScheduleId, LoginDTO loginDTO)
        {
            var param = new { masterScheduleId = MasterScheduleId };

            var header = await _QueryExecutor
                .QuerySingleAsync<MasterScheduleDTO>(loginDTO, MasterScheduleQB.GET_MASTERSCHEDULE, param)
                .ConfigureAwait(false);

            if (header is null) return null;

            var details = await _QueryExecutor
                .QueryAsync<MasterScheduleDetailDTO>(loginDTO, MasterScheduleQB.GET_MASTERSCHEDULE_DETAILS, param)
                .ConfigureAwait(false);

            header.MasterScheduleDetailArray = details?.ToList() ?? [];
            return header;
        }

        // ── Save (insert) header + details ────────────────────────────────

        public async Task<Result<string>> SaveMasterSchedule(
            MasterScheduleDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            dto.MasterScheduleCreatedById  = loginDTO.UserId;
            dto.MasterScheduleCreatedOn    = DateTime.UtcNow;
            dto.MasterScheduleModifiedById = loginDTO.UserId;
            dto.MasterScheduleModifiedOn   = DateTime.UtcNow;

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

            foreach (var detail in dto.MasterScheduleDetailArray)
            {
                detail.MasterScheduleDetailCreatedById  = loginDTO.UserId;
                detail.MasterScheduleDetailCreatedOn    = DateTime.UtcNow;
                detail.MasterScheduleDetailModifiedById = loginDTO.UserId;
                detail.MasterScheduleDetailModifiedOn   = DateTime.UtcNow;
                detail.TenantId                         = loginDTO.ClientId;

                await _QueryExecutor
                    .ExecuteAsync(loginDTO, MasterScheduleQB.SAVE_MASTERSCHEDULEDETAIL, detail, tx)
                    .ConfigureAwait(false);
            }

            return rows > 0
                ? Result<string>.Success("Master schedule saved.")
                : Result<string>.Failure("Failed to save master schedule.");
        }

        // ── Update header + replace details ──────────────────────────────

        public async Task<Result<string>> UpdateMasterSchedule(
            MasterScheduleDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            dto.MasterScheduleModifiedById = loginDTO.UserId;
            dto.MasterScheduleModifiedOn   = DateTime.UtcNow;

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

            await _QueryExecutor
                .ExecuteAsync(loginDTO, MasterScheduleQB.DELETE_MASTERSCHEDULEDETAILS,
                    new { dto.MasterScheduleId }, tx)
                .ConfigureAwait(false);

            foreach (var detail in dto.MasterScheduleDetailArray)
            {
                detail.MasterScheduleDetailCreatedById  = loginDTO.UserId;
                detail.MasterScheduleDetailCreatedOn    = DateTime.UtcNow;
                detail.MasterScheduleDetailModifiedById = loginDTO.UserId;
                detail.MasterScheduleDetailModifiedOn   = DateTime.UtcNow;
                detail.TenantId                         = loginDTO.ClientId;

                await _QueryExecutor
                    .ExecuteAsync(loginDTO, MasterScheduleQB.SAVE_MASTERSCHEDULEDETAIL, detail, tx)
                    .ConfigureAwait(false);
            }

            return rows > 0
                ? Result<string>.Success("Master schedule updated.")
                : Result<string>.Failure("Failed to update master schedule.");
        }

        // ── Delete header (cascade deletes details via FK or explicit delete) ─

        public async Task<Result<string>> DeleteMasterSchedule(
            int masterScheduleId, LoginDTO loginDTO, DbTransaction tx)
        {
            await _QueryExecutor
                .ExecuteAsync(loginDTO, MasterScheduleQB.DELETE_MASTERSCHEDULEDETAILS,
                    new { MasterScheduleId = masterScheduleId }, tx)
                .ConfigureAwait(false);

            int rows = await _QueryExecutor
                .ExecuteAsync(loginDTO, MasterScheduleQB.DELETE_MASTERSCHEDULE,
                    new { MasterScheduleId = masterScheduleId }, tx)
                .ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("Master schedule deleted.")
                : Result<string>.Failure("Master schedule not found or already deleted.");
        }

        // ── Paged list ────────────────────────────────────────────────────

        public async Task<IEnumerable<MasterScheduleListDTO>> GetMasterScheduleList(
            int pageOffset, int pageSize, LoginDTO loginDTO)
        {
            var param = new { PageOffset = pageOffset, PageSize = pageSize };
            return await _QueryExecutor
                .QueryAsync<MasterScheduleListDTO>(loginDTO, MasterScheduleQB.GET_MASTERSCHEDULE_LIST, param)
                .ConfigureAwait(false) ?? [];
        }

        public async Task<int> GetMasterScheduleListCount(LoginDTO loginDTO)
        {
            return await _QueryExecutor
                .ExecuteScalarAsync<int>(loginDTO, MasterScheduleQB.GET_MASTERSCHEDULE_LIST_COUNT, null)
                .ConfigureAwait(false);
        }

        // ── Picklist ──────────────────────────────────────────────────────

        public async Task<IEnumerable<MasterSchedulePicklistDTO>> GetSelectListMasterSchedule(LoginDTO loginDTO)
        {
            return await _QueryExecutor
                .QueryAsync<MasterSchedulePicklistDTO>(loginDTO, MasterScheduleQB.GET_SELECTLIST_MASTERSCHEDULE, null)
                .ConfigureAwait(false) ?? [];
        }

        // ── Load master schedule demand ───────────────────────────────────
        // Deletes existing details then reloads from the chosen demand source.
        // startNumber: first auto-number from a batch GetNumberAsync call.

        public async Task LoadMasterSchedule(
            int masterScheduleId, byte sourceType, int startNumber,
            LoginDTO loginDTO, DbTransaction tx)
        {
            var deleteParam = new { MasterScheduleId = masterScheduleId };

            await _QueryExecutor
                .ExecuteAsync(loginDTO, MasterScheduleQB.LOAD_DELETE_EXISTING_DETAILS, deleteParam, tx)
                .ConfigureAwait(false);

            var loadParam = new
            {
                MasterScheduleId = masterScheduleId,
                StartNumber       = startNumber,
                CreatedById       = loginDTO.UserId,
                ModifiedById      = loginDTO.UserId
            };

            if (sourceType == 0)  // Forecast
            {
                await _QueryExecutor
                    .ExecuteAsync(loginDTO, MasterScheduleQB.LOAD_FROM_FORECAST, loadParam, tx)
                    .ConfigureAwait(false);
            }
            else if (sourceType == 1)  // Sales Order
            {
                await _QueryExecutor
                    .ExecuteAsync(loginDTO, MasterScheduleQB.LOAD_FROM_SALESORDER, loadParam, tx)
                    .ConfigureAwait(false);
            }
            else if (sourceType == 2)  // Sales Order + Forecast
            {
                await _QueryExecutor
                    .ExecuteAsync(loginDTO, MasterScheduleQB.LOAD_FROM_FORECAST, loadParam, tx)
                    .ConfigureAwait(false);
                await _QueryExecutor
                    .ExecuteAsync(loginDTO, MasterScheduleQB.LOAD_FROM_SALESORDER, loadParam, tx)
                    .ConfigureAwait(false);
            }
        }

        // ── Set demand priority (batch) ───────────────────────────────────

        public async Task SetDemandPriority(SetDemandPriorityDTO dto, LoginDTO loginDTO)
        {
            foreach (var line in dto.Lines)
            {
                var param = new
                {
                    line.MasterScheduleDetailId,
                    line.Priority,
                    ModifiedById = loginDTO.UserId
                };
                await _QueryExecutor
                    .ExecuteAsync(loginDTO, MasterScheduleQB.SET_DEMAND_PRIORITY, param)
                    .ConfigureAwait(false);
            }
        }

        // ── Set allocation priority ───────────────────────────────────────

        public async Task SetAllocationPriority(SetAllocationPriorityDTO dto, LoginDTO loginDTO)
        {
            var param = new { dto.AllocationId, dto.Priority };

            await _QueryExecutor
                .ExecuteAsync(loginDTO, MasterScheduleQB.SET_ALLOCATION_PRIORITY, param)
                .ConfigureAwait(false);

            if (dto.CascadeToDetails)
            {
                await _QueryExecutor
                    .ExecuteAsync(loginDTO, MasterScheduleQB.CASCADE_ALLOCATION_PRIORITY_TO_DETAILS, param)
                    .ConfigureAwait(false);
            }
        }

        // ── Clear old schedule ────────────────────────────────────────────

        public async Task ClearOldSchedule(ClearScheduleDTO dto, LoginDTO loginDTO)
        {
            var param = new { dto.MasterScheduleId };
            var sql = dto.ClearType == 0
                ? MasterScheduleQB.CLEAR_SCHEDULE_DELETE_DETAILS
                : MasterScheduleQB.CLEAR_SCHEDULE_RESET_BALANCE;

            await _QueryExecutor.ExecuteAsync(loginDTO, sql, param).ConfigureAwait(false);
        }
    }
}