using GB5Shared.QueryExecutor;
using FrameworkDAL.DTO.DocumentSet;
using FrameworkDAL.Query.DocumentSet;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.PubSub.OutBox;

namespace FrameworkDAL.CustomCode.DocumentSet
{
    public class DocumentSetDAL : IDocumentSetDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IOutBox _outBox;


        public DocumentSetDAL(IQueryExecutor queryExecutor  , IOutBox outBox)
        {
            _queryExecutor = queryExecutor;
            _outBox = outBox;
        }

        public async Task<string> GetDocumentSet(int DocumentSetId, LoginDTO loginDTO)
        {
            try
            {
                var sql = DocumentSetQB.GET_DOCUMENTSET;
                var parameters = new { documentsetid = DocumentSetId };

                var DocumentSetDict = new Dictionary<int, DocumentSetDTO>();

                var result = await _queryExecutor.QueryAsyncArray<DocumentSetDTO, DocumentSetDetailDTO, DocumentSetDTO>(
                   loginDTO,
                   sql,
                   (parent, child) =>
                   {
                        if (!DocumentSetDict.TryGetValue(parent.DocumentSetId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.DocumentSetDetailArray = new List<DocumentSetDetailDTO>();
                           DocumentSetDict[parent.DocumentSetId] = existingParent;
                        }

                        if (child != null && child.DocumentSetDetailId != 0)
                        {
                            existingParent.DocumentSetDetailArray.Add(child);
                        }

                        return existingParent;
                    },
                    parameters,
                    splitOn: "DocumentSetDetailId"
                );

                var finalResult = DocumentSetDict.Values.FirstOrDefault(); 
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve DocumentSet data: {ex.Message}", ex);
            }
        }
        /// <summary>
        /// Retrieves a paginated list of DocumentSet records based on specified filter criteria.
        /// Can optionally return the total count of records instead of the list.
        /// </summary>
        /// <param name="firstNumber">The starting index of records to fetch (for pagination).</param>
        /// <param name="maxResult">The maximum number of records to fetch.</param>
        /// <param name="criteriaDTO">Filter criteria for selecting DocumentSet records.</param>
        /// <param name="loginDTO">The current user's login details.</param>
        /// <param name="isCount">If true, returns the total count of matching records instead of the list.</param>
        /// <returns>A JSON string representing either the list of DocumentSet records or the total count.</returns>
        public async Task<string> GetSelectListDocumentSet(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, bool isCount = false)
        {
            try
            {
                string json = "";

                if (!isCount)
                {
                    string sql = DocumentSetQB.GET_SELECTLIST_DOCUMENTSET;
                    var parameters = new { firstnumber = firstNumber, maxresult = maxResult };
                    List<DocumentSetPickListDTO> list = (await _queryExecutor.QueryAsync<DocumentSetPickListDTO>(loginDTO, sql, parameters)).ToList();
                    json = JsonConvert.SerializeObject(list);
                }
                else
                {
                    int count = 0;
                    json = count.ToString();
                }
                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveDocumentSet(DocumentSetDTO DocumentSetDTO, LoginDTO loginDTO)
        {
            try
            {

                string DocumentSetSql = DocumentSetQB.SAVE_DOCUMENTSET;
                string childSql = DocumentSetQB.SAVE_DOCUMENTSETDETAIL;

                var statements = new List<(string sql, object param)>
                {
                    (DocumentSetSql, DocumentSetDTO)
                };

                if (DocumentSetDTO.DocumentSetDetailArray != null && DocumentSetDTO.DocumentSetDetailArray.Count > 0)
                {
                    foreach (var child in DocumentSetDTO.DocumentSetDetailArray)
                    {
                        child.DocumentSetId = DocumentSetDTO.DocumentSetId;
                        statements.Add((childSql, child));
                    }
                }

                await _queryExecutor.ExecuteInTransactionAsync(loginDTO, statements);

                return DocumentSetDTO.DocumentSetId;
            }
            catch (Exception ex)
            {
                throw new Exception($"{ErrorResponse.SaveErrorMessage}: {ex.Message}", ex);
            }
        }

        public async Task<int> UpdateDocumentSet(DocumentSetDTO DocumentSetDTO, LoginDTO loginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>();

                statements.Add((
                    DocumentSetQB.UPDATE_DOCUMENTSET,
                    DocumentSetDTO
                ));

                var parameters = new { documentsetid = DocumentSetDTO.DocumentSetId };
                var maxId = await _queryExecutor.ExecuteScalarAsync<int>(
                    loginDTO,
                    DocumentSetQB.GET_MAX_DOCUMENTSETDETAILID,
                    parameters
                );

                maxId = maxId - 1;

                if (DocumentSetDTO.DocumentSetDetailArray != null)
                {
                    foreach (var child in DocumentSetDTO.DocumentSetDetailArray)
                    {
                        if (child.DocumentSetDetailId == 0)
                        {
                            child.DocumentSetDetailId = maxId--;
                            statements.Add((
                                DocumentSetQB.SAVE_DOCUMENTSETDETAIL,
                                child
                            ));
                        }
                        else
                        {
                            statements.Add((
                                DocumentSetQB.UPDATE_DOCUMENTSETDETAIL,
                                child
                            ));
                        }
                    }
                }

                await _queryExecutor.ExecuteInTransactionAsync(loginDTO, statements);

                return DocumentSetDTO.DocumentSetId;
            }
            catch (Exception ex)
            {
                throw new Exception($"{ErrorResponse.UpdateErrorMessage}: {ex.Message}", ex);
            }
        }

        public async Task<Result<string>> DeleteDocumentSet(int DocumentSetId, LoginDTO LoginDTO)
        {
            try
            {
                string childsql = DocumentSetQB.DELETE_DOCUMENTSETDETAIL;
                string parentSql = DocumentSetQB.DELETE_DOCUMENTSET;
                var parameter = new { documentsetid = DocumentSetId };
                var childresult = await _queryExecutor.ExecuteAsync(LoginDTO, childsql, parameter);
                var parentresult = await _queryExecutor.ExecuteAsync(LoginDTO, parentSql, parameter);

                if (parentresult > 0)
                {
                    return Result<string>.Success("DocumentSet Details Deleted Successfully.");
                }
                else
                {
                    return Result<string>.Failure("DocumentSet not found or could not be deleted.");
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting DocumentSet Details", ex);
            }
        }
    }
}