using EnablementDAL.DTO;
using EnablementDAL.Query.Show;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using GB5Shared.Resource.Response;

namespace EnablementDAL.CustomCode.Show
{
    // Moved from GB5Framework/FrameworkDAL/CustomCode/TourShow/TourShowDAL.cs — logic unchanged.
    public class ShowDAL : IShowDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public ShowDAL(IQueryExecutor QueryExecutor, IValidation IValidation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = IValidation;
        }

        /// <summary>
        /// Get Show with nested steps and step details
        /// </summary>
        public async Task<IEnumerable<TShowDTO>> GetShow(LoginDTO loginDTO, string sql, object parameters)
        {
            var showDict = new Dictionary<long, TShowDTO>();

            var result = await _QueryExecutor.QueryMultiMapAsync<TShowDTO, TShowStepDTO, TShowStepDetailDTO, TShowDTO>(
                loginDTO,
                sql,
                (show, step, detail) =>
                {
                    // Parent
                    if (!showDict.TryGetValue(show.ShowId, out var currentShow))
                    {
                        currentShow = show;
                        currentShow.ShowStepArray = new List<TShowStepDTO>();
                        showDict.Add(currentShow.ShowId, currentShow);
                    }

                    // Child
                    if (step != null)
                    {
                        var currentStep = currentShow.ShowStepArray
                            .FirstOrDefault(s => s.ShowStepId == step.ShowStepId);

                        if (currentStep == null)
                        {
                            currentStep = step;
                            currentStep.ShowStepDetailArray = new List<TShowStepDetailDTO>();
                            currentShow.ShowStepArray.Add(currentStep);
                        }

                        // Subchild
                        if (detail != null &&
                            !currentStep.ShowStepDetailArray.Any(d => d.ShowStepDetailId == detail.ShowStepDetailId))
                        {
                            currentStep.ShowStepDetailArray.Add(detail);
                        }
                        ;
                    }

                    return currentShow;
                },
                parameters,
                splitOn: "ShowStepId,ShowStepDetailId"
            );

            return showDict.Values.ToList();
        }

        /// <summary>
        /// Get Select List of Shows with nested steps and step details
        /// </summary>
        public async Task<IEnumerable<TShowDTO>> GetSelectListShow(GB5Shared.DTO.Framework.Criteria.CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            var showDict = new Dictionary<long, TShowDTO>();

            var result = await _QueryExecutor.QueryMultiMapAsync<TShowDTO, TShowStepDTO, TShowStepDetailDTO, TShowDTO>(
                loginDTO,
                ShowQB.GET_SELECTLIST_SHOW,
                (show, step, detail) =>
                {
                    if (!showDict.TryGetValue(show.ShowId, out var currentShow))
                    {
                        currentShow = show;
                        currentShow.ShowStepArray = new List<TShowStepDTO>();
                        showDict.Add(currentShow.ShowId, currentShow);
                    }

                    if (step != null)
                    {
                        var currentStep = currentShow.ShowStepArray
                            .FirstOrDefault(s => s.ShowStepId == step.ShowStepId);

                        if (currentStep == null)
                        {
                            currentStep = step;
                            currentStep.ShowStepDetailArray = new List<TShowStepDetailDTO>();
                            currentShow.ShowStepArray.Add(currentStep);
                        }

                        if (detail != null &&
                            !currentStep.ShowStepDetailArray.Any(d => d.ShowStepDetailId == detail.ShowStepDetailId))
                        {
                            currentStep.ShowStepDetailArray.Add(detail);
                        }
                    }

                    return currentShow;
                },
                null,
                splitOn: "ShowStepId,ShowStepDetailId"
            );

            return showDict.Values.ToList();
        }

        /// <summary>
        /// Save Show along with its related entities
        /// </summary>
        public async Task<int> SaveCompleteShow(TShowDTO showDTO, LoginDTO loginDTO)
        {
            // -1 (this DTO's "not applicable" sentinel, matching the FE form's own convention)
            // must become a real NULL — CMSCHANNELID is a nullable FK, not a -1-sentinel column.
            if (showDTO.CmsChannelId is -1) showDTO.CmsChannelId = null;

            var trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);

            try
            {
                // INSERT TSHOW
                showDTO.ShowId = await _QueryExecutor.QuerySingleAsync<int>(loginDTO, ShowQB.SAVE_SHOW, showDTO, trans);

                if (showDTO.ShowId <= 0)
                    throw new Exception("TSHOW insert failed.");

                // INSERT TSHOWSTEP
                foreach (var step in showDTO.ShowStepArray)
                {
                    step.ShowId = showDTO.ShowId;

                    step.ShowStepId = await _QueryExecutor.QuerySingleAsync<int>(loginDTO, ShowQB.SAVE_SHOWSTEP, step, trans);

                    if (step.ShowStepId <= 0)
                        throw new Exception("TSHOWSTEP insert failed.");

                    // INSERT TSHOWSTEPDETAIL
                    foreach (var detail in step.ShowStepDetailArray)
                    {
                        detail.ShowStepId = step.ShowStepId;

                        detail.ShowStepDetailId =
                            await _QueryExecutor.QuerySingleAsync<long>(loginDTO, ShowQB.SAVE_SHOWSTEPDETAIL, detail, trans);

                        if (detail.ShowStepDetailId <= 0)
                            throw new Exception("TSHOWSTEPDETAIL insert failed.");
                    }
                }

                await _QueryExecutor.CommitAsync(trans);

                return showDTO.ShowId;
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(trans);
                throw;
            }
        }

        /// <summary>
        /// Update Show along with its related entities
        /// </summary>
        public async Task UpdateCompleteShow(TShowDTO showDTO, LoginDTO loginDTO)
        {
            if (showDTO.CmsChannelId is -1) showDTO.CmsChannelId = null;

            var trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);

            try
            {
                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    ShowQB.UPDATE_SHOW,
                    showDTO,
                    trans);

                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    ShowQB.DELETE_SHOWSTEPDETAIL,
                    new { ShowId = showDTO.ShowId },
                    trans);

                await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    ShowQB.DELETE_SHOWSTEP,
                    new { ShowId = showDTO.ShowId },
                    trans);

                foreach (var step in showDTO.ShowStepArray)
                {
                    step.ShowId = showDTO.ShowId;

                    step.ShowStepId = await _QueryExecutor.QuerySingleAsync<int>(
                        loginDTO,
                        ShowQB.SAVE_SHOWSTEP,
                        step,
                        trans);

                    foreach (var detail in step.ShowStepDetailArray)
                    {
                        detail.ShowStepId = step.ShowStepId;

                        await _QueryExecutor.QuerySingleAsync<long>(
                            loginDTO,
                            ShowQB.SAVE_SHOWSTEPDETAIL,
                            detail,
                            trans);
                    }
                }

                await _QueryExecutor.CommitAsync(trans);
            }
            catch
            {
                await _QueryExecutor.RollbackAsync(trans);
                throw;
            }
        }

        /// <summary>
        /// Count of TSHOWRUN rows currently InProgress (RUNSTATE = 0) for this show —
        /// used to block deletion while a user is mid-flow.
        /// </summary>
        public async Task<int> GetActiveRunCountForShow(int showId, LoginDTO loginDTO)
            => await _QueryExecutor.ExecuteScalarAsync<int>(
                loginDTO,
                ShowQB.CHECK_ACTIVE_RUNS_FOR_SHOW,
                new { ShowId = showId });

        /// <summary>
        /// Soft delete — sets STATUS = 0 rather than a hard DELETE, since TSHOW is
        /// referenced by non-cascading FKs from other Enablement tables.
        /// </summary>
        public async Task<int> SoftDeleteShow(int showId, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    ShowQB.SOFT_DELETE_SHOW,
                    new { ShowId = showId, ModifiedById = loginDTO.UserId, ModifiedOn = DateTime.UtcNow });
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(error);
            }
        }

        /// <summary>
        /// Aggregated dropdown data for the Show authoring form. Space/Channel are
        /// same-project tables; ContentCategory/Module are owned by other modules
        /// (CMS/Framework) — queried here by raw SQL only, no cross-project type
        /// dependency.
        /// </summary>
        public async Task<TShowPicklistDTO> GetShowPicklist(LoginDTO loginDTO)
        {
            var tenantParam = new { TenantId = loginDTO.ClientId };

            var modules = await _QueryExecutor.QueryAsync<TShowPicklistItemDTO>(
                loginDTO, ShowQB.GET_MODULE_PICKLIST);
            var spaces = await _QueryExecutor.QueryAsync<TShowPicklistItemDTO>(
                loginDTO, ShowQB.GET_SPACE_PICKLIST, tenantParam);
            var channels = await _QueryExecutor.QueryAsync<TShowPicklistItemDTO>(
                loginDTO, ShowQB.GET_CHANNEL_PICKLIST, tenantParam);
            var contentCategories = await _QueryExecutor.QueryAsync<TShowPicklistItemDTO>(
                loginDTO, ShowQB.GET_CONTENTCATEGORY_PICKLIST, tenantParam);
            var userGroups = await _QueryExecutor.QueryAsync<TShowPicklistItemDTO>(
                loginDTO, ShowQB.GET_USERGROUP_PICKLIST);

            var picklist = new TShowPicklistDTO
            {
                Modules = modules.ToList(),
                Spaces = spaces.ToList(),
                Channels = channels.ToList(),
                ContentCategories = contentCategories.ToList(),
                UserGroups = userGroups.ToList()
            };

            return picklist;
        }
    }
}