using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FrameworkDAL.Query.CriteriaConfig;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Google.Protobuf.WellKnownTypes;
using Newtonsoft.Json;
using static Google.Protobuf.Reflection.ExtensionRangeOptions.Types;

namespace FrameworkDAL.CustomCode.CriteriaConfig
{
    public class CriteriaConfigDAL : ICriteriaConfigDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetCriteriaConfig(int CriteriaConfigId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = CriteriaConfigQB.GET_CRITERIACONFIG;
                var parameters = new { criteriaconfigid = CriteriaConfigId };

                var CriteriaConfigDict = new Dictionary<int, CriteriaConfigDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<CriteriaConfigDTO, CriteriaConfigSectionDTO, CriteriaConfigAttributeDTO, CriteriaConfigDTO>(
                    LoginDTO,
                    sql,
                    (parent, child, subchild) =>
                    {
                        if (!CriteriaConfigDict.TryGetValue(parent.CriteriaConfigId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.CriteriaConfigSectionArray = new List<CriteriaConfigSectionDTO>();
                            CriteriaConfigDict[parent.CriteriaConfigId] = existingParent;
                        }

                        if (child != null)
                        {
                            var existingChild = existingParent.CriteriaConfigSectionArray
                                .FirstOrDefault(parent => parent.CriteriaConfigId == child.CriteriaConfigId);

                            if (existingChild == null)
                            {
                                existingChild = child;
                                existingChild.CriteriaConfigAttributeArray = new List<CriteriaConfigAttributeDTO>();
                                existingParent.CriteriaConfigSectionArray.Add(existingChild);
                            }

                            if (subchild != null &&
                                !existingChild.CriteriaConfigAttributeArray.Any(sub => sub.CriteriaConfigAttributeId == subchild.CriteriaConfigAttributeId))
                            {
                                existingChild.CriteriaConfigAttributeArray.Add(subchild);
                            }
                            ;
                        }

                        return existingParent;
                    },
                    parameters,
                    splitOn: "CriteriaConfigSectionId,CriteriaConfigAttributeId"
                );

                var finalResult = CriteriaConfigDict.Values.FirstOrDefault();
                return JsonConvert.SerializeObject(finalResult);
            }
            catch (Exception ex)
            {
                throw new Exception($"Failed to retrieve CriteriaConfig data: {ex.Message}", ex);
            }
        }

        public async Task<int> SaveCriteriaConfig(CriteriaConfigDTO CriteriaConfigDTO, LoginDTO LoginDTO)
        {
            try
            {

                string CriteriaConfigSql = CriteriaConfigQB.SAVE_CRITERIACONFIG;

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

                if (CriteriaConfigDTO.CriteriaConfigSectionArray != null)
                {
                    foreach (var section in CriteriaConfigDTO.CriteriaConfigSectionArray)
                    {
                        section.CriteriaConfigId = CriteriaConfigDTO.CriteriaConfigId;
                        statements.Add((
                            CriteriaConfigQB.SAVE_CRITERIACONFIGSECTION,
                            section));

                        if (section.CriteriaConfigAttributeArray != null)
                        {
                            foreach (var attribute in section.CriteriaConfigAttributeArray)
                            {
                                attribute.CriteriaSectionId = section.CriteriaConfigSectionId;
                                statements.Add((
                                    CriteriaConfigQB.SAVE_CRITERIACONFIGATTRIBUTE,
                                    attribute));
                            }
                        }
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

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

        public async Task<int> UpdateCriteriaConfig(CriteriaConfigDTO CriteriaConfigDTO, LoginDTO LoginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>();
                statements.Add((
                    CriteriaConfigQB.UPDATE_CRITERIACONFIG,
                    CriteriaConfigDTO
                ));

                var parameters = new { criteriaconfigid = CriteriaConfigDTO.CriteriaConfigId };
                var maxSectionId = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, CriteriaConfigQB.GET_MAX_CRITERIACONFIGSECTIONID, parameters);
                maxSectionId = maxSectionId - 1;
                var maxAttributeId = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, CriteriaConfigQB.GET_MAX_CRITERIACONFIGATTRIBUTEID, parameters);
                maxAttributeId = maxAttributeId - 1;

                if (CriteriaConfigDTO.CriteriaConfigSectionArray != null)
                {
                    foreach (var section in CriteriaConfigDTO.CriteriaConfigSectionArray)
                    {
                        section.CriteriaConfigId = CriteriaConfigDTO.CriteriaConfigId;
                        if (section.CriteriaConfigSectionId == 0)
                        {
                            section.CriteriaConfigSectionId = maxSectionId--;
                            statements.Add((
                                CriteriaConfigQB.SAVE_CRITERIACONFIGSECTION,
                                section
                            ));
                        }
                        else
                        {
                            statements.Add((
                                CriteriaConfigQB.UPDATE_CRITERIACONFIGSECTION,
                                section
                            ));
                        }
                        if (section.CriteriaConfigAttributeArray != null)
                        {
                            foreach (var attribute in section.CriteriaConfigAttributeArray)
                            {
                                attribute.CriteriaSectionId = section.CriteriaConfigSectionId;
                                if (attribute.CriteriaConfigAttributeId == 0)
                                {
                                    attribute.CriteriaConfigAttributeId = maxAttributeId--;
                                    statements.Add((
                                        CriteriaConfigQB.SAVE_CRITERIACONFIGATTRIBUTE,
                                        attribute
                                    ));
                                }
                                else
                                {
                                    statements.Add((
                                        CriteriaConfigQB.UPDATE_CRITERIACONFIGATTRIBUTE,
                                        attribute
                                    ));
                                }
                            }
                        }
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

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

        public async Task<Result<string>> DeleteCriteriaConfig(int CriteriaConfigId, LoginDTO LoginDTO)
        {
            try
            {
                string subchildsql = CriteriaConfigQB.DELETE_CRITERIACONFIGATTRIBUTE;
                string childsql = CriteriaConfigQB.DELETE_CRITERIACONFIGSECTION;
                string parentSql = CriteriaConfigQB.DELETE_CRITERIACONFIG;
                var parameter = new { criteriaconfigid = CriteriaConfigId };
                var subchildresult = await _QueryExecutor.ExecuteAsync(LoginDTO, subchildsql, parameter);
                var childresult = await _QueryExecutor.ExecuteAsync(LoginDTO, childsql, parameter);
                var parentresult = await _QueryExecutor.ExecuteAsync(LoginDTO, parentSql, parameter);

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

        public async Task<string> GetSelectListCriteriaConfig(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = CriteriaConfigQB.GET_SELECTLIST_CRITERIACONFIG;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    List<CriteriaConfigPicklistDTO> CriteriaConfigPicklistDTO = (await _QueryExecutor.QueryAsync<CriteriaConfigPicklistDTO>(LoginDTO, SQL, parameter)).ToList();

                    Json = JsonConvert.SerializeObject(CriteriaConfigPicklistDTO);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task UpdateUserVsPortlet(int criteriaConfigId, int userVsPortletId, LoginDTO loginDTO)
        {
            var parameter = new{CriteriaConfigId = criteriaConfigId, UserVsPortletId = userVsPortletId};
            await _QueryExecutor.ExecuteAsync(loginDTO, CriteriaConfigQB.UPDATE_USERVSPORTLET, parameter);
        }
    }
}