using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using QMSDAL.DTO;
using QMSDAL.Query.Checklist;
using QMSDAL.Query.ChecklistDetail;
using System.Data.Common;

namespace QMSDAL.CustomCode.ChecklistDetail
{
    public class ChecklistDetailDAL : IChecklistDetailDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        // ── Get all detail rows for a checklist ───────────────────────────────
        // Groups flat rows (one per LOV option) into ChecklistDetailDTOs.

        public async Task<string> GetChecklistDetailAsync(
         int checklistId,
         LoginDTO login,
         CancellationToken ct = default)
        {
            try
            {
                string sql = ChecklistDetailQB.GET_CHECKLISTDETAIL;
                var parameters = new { ChecklistId = checklistId };

                var detailDict = new Dictionary<int, ChecklistDetailDTO>();

                await _QueryExecutor.QueryMultiMapAsync<
                        ChecklistDetailDTO,
                        LovOptionDTO,
                        ChecklistDetailDTO>(
                    login,
                    sql,
                    (parent, child) =>
                    {
                        if (!detailDict.TryGetValue(parent.ChecklistDetailId,
                                out var existing))
                        {
                            existing = parent;
                            existing.LovOptions = new List<LovOptionDTO>();
                            detailDict[existing.ChecklistDetailId] = existing;
                        }

                        if (child != null && child.LovId != 0)
                        {
                            existing.LovOptions.Add(new LovOptionDTO
                            {
                                LovId = child.LovId,
                                LovName = child.LovName
                            });
                        }

                        return existing;
                    },
                    parameters,
                    splitOn: "LovId"
                );

                return JsonConvert.SerializeObject(detailDict.Values.ToList());
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Failed to retrieve Checklist Detail data: {ex.Message}", ex);
            }
        }
    }
}