using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WiDAL.DTOs;
using WiDAL.Query.Wi;

namespace WiDAL.CustomCode.WiStep
{

    public class WiStepDAL : IWiStepDAL
    {
        private readonly IQueryExecutor _qe;
        private readonly IValidation _Validation;

        public WiStepDAL(IQueryExecutor qe, IValidation validation)
        {
            _qe = qe;
            _Validation = validation;
        }

        public async Task<string> GetById(int WiStepId, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QuerySingleAsync<WiStepDTO>(login, WiStepQB.GET_BY_ID,
                new { wiStepId = WiStepId, TenantId = login.ClientId });
            return JsonConvert.SerializeObject(result);
        }

        public async Task<int> Save(WiStepDTO dto, bool isNew, LoginDTO login, CancellationToken ct)
        {
            try
            {
                string sql = isNew
                    ? WiStepQB.INSERT
                    : WiStepQB.UPDATE;

                var rows = await _qe.ExecuteAsync(login, sql, dto);

                // 🔥 important check
                if (rows == 0)
                {
                    throw new Exception(isNew
                        ? "Insert failed!"
                        : "Update failed! No matching record.");
                }

                return dto.WiStepId;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex,
                    isNew ? ErrorResponse.SaveErrorMessage : ErrorResponse.UpdateErrorMessage);

                throw new Exception(error);
            }
        }
        public async Task<string> GetSelectListWiStep(CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            var result = await _qe.QueryAsync<dynamic>(
                login, WiStepQB.GET_SELECTLIST_WISTEP);
            return JsonConvert.SerializeObject(result);
        }
        public async Task<string> DeleteWiStep(int WiStepId, LoginDTO loginDTO)
        {
            try
            {
                string sql = WiStepQB.DELETE_WISTEP;

                var parameter = new { wiStepId = WiStepId };

                var result = await _qe.ExecuteAsync(loginDTO, sql, parameter);

                if (result > 0)
                    return SuccessResponse.DeleteSuccessMessage;
                else
                    return ErrorResponse.DeleteNotFoundMessage;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, ErrorResponse.DeleteErrorMessage);
                throw new Exception(error);
            }
        }

        public async Task<int> SaveStepWithContent(WiStepDTO dto, bool isNew, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var statements = new List<(string sql, object param)>();

                // ── 1. Parent — map to anonymous object (strips Contents list) ──
                statements.Add((
                    isNew ? WiStepQB.INSERT : WiStepQB.UPDATE,
                    new
                    {
                        dto.WiStepId,
                        dto.WiId,
                        dto.WiSectionId,
                        dto.WiStepSlNo,
                        dto.StepDisplayNo,
                        dto.StepTitle,
                        dto.WarningLevel,
                        dto.StdDurationSecs,
                        dto.ToolsRequired,
                        dto.IsOptional,
                        dto.RequireSignOff,
                        dto.AutoAdvanceSecs,
                        dto.IsActive,
                        dto.ContentId
                    }
                ));

                // ── 2. Children ───────────────────────────────────────────
                if (dto.Contents != null && dto.Contents.Any())
                {
                    foreach (var content in dto.Contents)
                    {
                        content.WiStepId = dto.WiStepId;

                        statements.Add((isNew? WiStepContentQB.INSERT: (content.WiStepContentId > 0 ? WiStepContentQB.UPDATE : WiStepContentQB.INSERT),
                            new
                            {
                                content.WiStepContentId,
                                content.WiStepId,
                                content.WiStepContentSlNo,
                                content.ContentType,
                                content.ContentSource,
                                content.ContentRef,
                                content.MediaCaption,
                                content.DisplayDurationSecs,
                                content.LoopMedia,
                                content.ZoomRegion,
                                content.ApplicableModelIds,
                                content.IsActive
                            }
                        ));
                    }

                    if (!isNew)
                    {
                        var activeIds = dto.Contents
                            .Where(x => x.WiStepContentId > 0)
                            .Select(x => x.WiStepContentId)
                            .ToList();

                        statements.Add((
                            WiStepContentQB.DELETE_BY_STEPID,
                            new
                            {
                                wiStepId = dto.WiStepId,
                                ActiveIds = activeIds
                            }
                        ));
                    }
                }

                await _qe.ExecuteInTransactionAsync(login, statements);

                return dto.WiStepId;
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(
                    ex,
                    isNew
                        ? ErrorResponse.SaveErrorMessage
                        : ErrorResponse.UpdateErrorMessage);

                throw new Exception(error);
            }
        }

    }
}