using GB5Shared.DTO.Framework.Criteria;
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.Checklist
{
    public class ChecklistDAL : IChecklistDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public ChecklistDAL(IQueryExecutor queryExecutor)
        {
            _QueryExecutor = queryExecutor;
        }
        public async Task<ChecklistDTO?> GetChecklistAsync(
    int checklistId,
    LoginDTO loginDTO,
    CancellationToken ct = default)
        {
            var checklistDict = new Dictionary<int, ChecklistDTO>();
            var detailDict = new Dictionary<int, ChecklistDetailDTO>();

            string sql = ChecklistQB.GET_CHECKLIST;
            var parameters = new { ChecklistId = checklistId };

            await _QueryExecutor.QueryMultiMapAsync<
                    ChecklistDTO,
                    ChecklistDetailDTO,
                    LovOptionDTO,
                    ChecklistDTO>(
                loginDTO,
                sql,
                (header, detail, lov) =>
                {
                    // Header
                    if (!checklistDict.TryGetValue(header.ChecklistId, out var currentChecklist))
                    {
                        currentChecklist = header;
                        currentChecklist.ChecklistDetailArray = new List<ChecklistDetailDTO>();
                        checklistDict.Add(currentChecklist.ChecklistId, currentChecklist);
                    }

                    // Detail
                    if (detail != null && detail.ChecklistDetailId > 0)
                    {
                        if (!detailDict.TryGetValue(detail.ChecklistDetailId, out var currentDetail))
                        {
                            currentDetail = detail;
                            currentDetail.LovOptions = new List<LovOptionDTO>();

                            detailDict.Add(currentDetail.ChecklistDetailId, currentDetail);
                            currentChecklist.ChecklistDetailArray.Add(currentDetail);
                        }

                        // LOV Options
                        if (lov != null && lov.LovId > 0)
                        {
                            if (!currentDetail.LovOptions.Any(x => x.LovId == lov.LovId))
                            {
                                currentDetail.LovOptions.Add(lov);
                            }
                        }
                    }

                    return currentChecklist;
                },
                parameters,
                splitOn: "ChecklistDetailId,LovId"
            );

            return checklistDict.Values.FirstOrDefault();
        }
        // ── Get active checklist list ─────────────────────────────────────────

        public async Task<string> GetChecklistListAsync(
    int FirstNumber,
    int MaxResult,
    CriteriaDTO CriteriaDTO,
    LoginDTO login,
    bool IsCount = false,
    CancellationToken ct = default)
        {
            try
            {
                string Json = "";
                if (!IsCount)
                {
                    var parameters = new
                    {
                        firstnumber = FirstNumber,
                        maxresult = MaxResult
                    };

                    IEnumerable<ChecklistDTO> checklistDTO = await _QueryExecutor
                        .QueryAsync<ChecklistDTO>(
                            login,
                            ChecklistQB.GET_CHECKLIST_LIST,
                            parameters,
                            cancellationToken: ct)
                        .ConfigureAwait(false);

                    // ✅ Serialize only the result list — not the full response wrapper
                    Json = JsonConvert.SerializeObject(checklistDTO);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }
        // ── Insert header ─────────────────────────────────────────────────────

        public async Task<int> SaveChecklistAsync(
         ChecklistDTO checklistDTO,
         LoginDTO login)
        {
            try
            {
                // -------------------------------------------------
                // 1. INSERT HEADER
                // -------------------------------------------------

                int newChecklistId =
                    await _QueryExecutor.ExecuteScalarAsync<int>(
                        login,
                        ChecklistQB.SAVE_CHECKLIST,
                        checklistDTO);

                if (newChecklistId <= 0)
                {
                    throw new Exception(
                        "Checklist insert failed. " +
                        "SQL Server did not return the generated ChecklistId.");
                }

                // -------------------------------------------------
                // 2. SET GENERATED ID IN DTO
                // -------------------------------------------------

                checklistDTO.ChecklistId = newChecklistId;

                // -------------------------------------------------
                // 3. INSERT DETAILS
                // -------------------------------------------------

                if (checklistDTO.ChecklistDetailArray != null &&
                    checklistDTO.ChecklistDetailArray.Count > 0)
                {
                    var statements =
                        new List<(string sql, object param)>();

                    foreach (var detail
                             in checklistDTO.ChecklistDetailArray)
                    {
                        // FK to MCHECKLIST
                        detail.ChecklistId = newChecklistId;

                        // ChecklistDetailId should remain 0.
                        // SQL Server will generate it if it is IDENTITY.

                        statements.Add((
                            ChecklistDetailQB.SAVE_CHECKLISTDETAIL,
                            detail));
                    }

                    await _QueryExecutor.ExecuteInTransactionAsync(
                        login,
                        statements);
                }

                return newChecklistId;
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Unable to save Checklist. " +
                    $"Technical detail: {ex.Message}",
                    ex);
            }
        }

        // =========================================================
        // UPDATE CHECKLIST
        // =========================================================

        public async Task<int> UpdateChecklistFullAsync(
            ChecklistDTO checklistDTO,
            LoginDTO login)
        {
            try
            {
                var statements =
                    new List<(string sql, object param)>();

                // -------------------------------------------------
                // 1. UPDATE HEADER
                // -------------------------------------------------

                statements.Add((
                    ChecklistQB.UPDATE_CHECKLIST,
                    checklistDTO));

                // -------------------------------------------------
                // 2. INSERT / UPDATE DETAILS
                // -------------------------------------------------

                if (checklistDTO.ChecklistDetailArray != null)
                {
                    foreach (var detail
                             in checklistDTO.ChecklistDetailArray)
                    {
                        // Always set parent FK
                        detail.ChecklistId =
                            checklistDTO.ChecklistId;

                        // -----------------------------------------
                        // NEW DETAIL
                        // -----------------------------------------

                        if (detail.ChecklistDetailId == 0)
                        {
                            // Do NOT assign ChecklistDetailId.
                            // Database identity will generate it.

                            statements.Add((
                                ChecklistDetailQB.SAVE_CHECKLISTDETAIL,
                                detail));
                        }

                        // -----------------------------------------
                        // EXISTING DETAIL
                        // -----------------------------------------

                        else
                        {
                            statements.Add((
                                ChecklistDetailQB.UPDATE_CHECKLISTDETAIL,
                                detail));
                        }
                    }
                }

                // -------------------------------------------------
                // 3. EXECUTE ALL UPDATE/INSERT STATEMENTS
                // -------------------------------------------------

                await _QueryExecutor.ExecuteInTransactionAsync(
                    login,
                    statements);

                return checklistDTO.ChecklistId;
            }
            catch (Exception ex)
            {
                throw new Exception(
                    $"Unable to update Checklist ID " +
                    $"{checklistDTO.ChecklistId}. " +
                    $"Technical detail: {ex.Message}",
                    ex);
            }
        }

        public async Task<int> DeleteChecklistDetail(int checklistId, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    ChecklistQB.DELETE_CHECKLISTDETAIL,
                    new { checklistid = checklistId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting Checklist detail for ID {checklistId}", ex);
            }
        }
        public async Task<int> DeleteChecklist(int checklistId, LoginDTO loginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    loginDTO,
                    ChecklistQB.DELETE_CHECKLIST,
                    new { checklistid = checklistId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting Checklist with ID {checklistId}", ex);
            }
        }
        public async Task<IEnumerable<ChecklistDTO>> GetSelectListChecklistFull(
    int FirstNumber,
    int MaxResult,
    CriteriaDTO CriteriaDTO,
    LoginDTO LoginDTO,
    bool IsCount = false)
        {
            try
            {
                if (IsCount)
                {
                    return Enumerable.Empty<ChecklistDTO>();
                }

                var checklistDict = new Dictionary<int, ChecklistDTO>();

                var parameters = new
                {
                    firstnumber = FirstNumber,
                    maxresult = MaxResult
                };

                var result = await _QueryExecutor.QueryMultiMapAsync<
                    ChecklistDTO,
                    ChecklistDetailDTO,
                    ChecklistDTO>(
                    LoginDTO,
                    ChecklistQB.GET_SELECTLIST_CHECKLIST_FULL,
                    (parent, detail) =>
                    {
                        if (!checklistDict.TryGetValue(parent.ChecklistId, out var current))
                        {
                            current = parent;
                            current.ChecklistDetailArray = new List<ChecklistDetailDTO>();
                            checklistDict.Add(current.ChecklistId, current);
                        }

                        if (detail != null &&
                            !current.ChecklistDetailArray.Any(x =>
                                x.ChecklistDetailId == detail.ChecklistDetailId))
                        {
                            current.ChecklistDetailArray.Add(detail);
                        }

                        return current;
                    },
                    parameters,
                    splitOn: "ChecklistDetailId"
                );

                return checklistDict.Values.ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}