using Dapper;
using FrameworkDAL.DTO.Parameter;
using FrameworkDAL.DTO.SMS;
using FrameworkDAL.Query.ParameterSet;
using GB5Shared.CriteriaHandler;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System.Diagnostics;

namespace FrameworkDAL.CustomCode.ParameterSet
{
    public class ParameterSetDAL : IParameterSetDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public ParameterSetDAL(IQueryExecutor QueryExecutor,IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }

        public async Task<ParameterSetDTO> GetParameterSet(int parameterSetId, LoginDTO loginDTO)
        {
            try
            {
                string sql = ParameterSetQB.GET_PARAMETERSET;

                var parameter = new
                {
                    parametersetid = parameterSetId
                };

                var parameterSetDict = new Dictionary<int, ParameterSetDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<ParameterSetDTO, ParameterSetDetailDTO, ParameterSetDTO>(
                    loginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!parameterSetDict.TryGetValue(parent.ParameterSetId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.ParameterSetDetailArray = new List<ParameterSetDetailDTO>();
                            parameterSetDict[parent.ParameterSetId] = existingParent;
                        }

                        if (child != null && child.ParameterSetDetailLineId != 0)
                        {
                            existingParent.ParameterSetDetailArray.Add(child);
                        }

                        return existingParent;
                    },
                    parameter,
                    splitOn: "ParameterSetDetailLineId"  
                );

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


        public async Task<string> GetSelectListParameterSet(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                string sql = ParameterSetQB.GetSelectList_ParameterSet;
                var result = await _QueryExecutor.QueryAsync<ParameterSetPicklistDTO>(loginDTO, sql, null!);
                string json = JsonConvert.SerializeObject(result);
                return json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Maps the legacy GB4 Criteria.svc/List FieldNames ("Parameter.Id",
        // "ParameterSet.Id") to the real MPARAMETERSETDETAIL/MPARAMETER columns —
        // also doubles as a whitelist since CriteriaBuilder interpolates FieldName
        // directly into the SQL column reference (not parameterized).
        private static readonly Dictionary<string, string> _parameterSetDetailFilterableFields = new(StringComparer.OrdinalIgnoreCase)
        {
            ["Parameter.Id"] = "PARAMETERID",
            ["ParameterSet.Id"] = "PARAMETERSETID",
        };

        private static void ApplyParameterSetDetailFieldNameWhitelist(CriteriaDTO? criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;

                section.AttributesCriteriaList = section.AttributesCriteriaList
                    .Where(attr => attr?.FieldName != null && _parameterSetDetailFilterableFields.ContainsKey(attr.FieldName))
                    .ToList();

                foreach (var attr in section.AttributesCriteriaList)
                    attr.FieldName = _parameterSetDetailFilterableFields[attr.FieldName];
            }
        }

        public async Task<string> GetParameterSetDetailList(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                ApplyParameterSetDetailFieldNameWhitelist(criteriaDTO);

                var parameters = new DynamicParameters();
                string baseSql = ParameterSetQB.GET_PARAMETERSETDETAIL_LIST;
                var (whereClause, _) = CriteriaBuilder.Build(criteriaDTO, parameters, baseSql);
                string sql = baseSql.Replace("/*CRITERIA*/", whereClause);

                var result = await _QueryExecutor.QueryAsync<ParameterSetDetailDTO>(loginDTO, sql, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveParameterSet(ParameterSetDTO ParameterSetDTO, LoginDTO LoginDTO)
        {
            try
            {
                // SQL Server queries only
                string ParameterSetSql = ParameterSetQB.SAVE_PARAMETERSET;
                string detailSql = ParameterSetQB.SAVE_PARAMETERSET_DETAIL;
                string countSql = ParameterSetQB.GET_PARAMETERSET_COUNT;

                // DUPLICATE CHECK
                int existingCount = await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, countSql,
                    new
                    {
                        ParameterSetDTO.ParameterSetId
                    });

                if (existingCount > 0)
                {
                    throw new MethodNotAllowedException(
                        "Duplicate ParameterSet record exists."
                    );
                }

                // TRANSACTION BUILD
                var statements = new List<(string sql, object param)>
        {
            (ParameterSetSql, ParameterSetDTO)
        };

                if (ParameterSetDTO.ParameterSetDetailArray?.Count > 0)
                {
                    foreach (var detail in ParameterSetDTO.ParameterSetDetailArray)
                    {
                        detail.ParameterSetId = ParameterSetDTO.ParameterSetId;
                        statements.Add((detailSql, detail));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

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


        public async Task<int> UpdateParameterSet(ParameterSetDTO ParameterSetDTO, LoginDTO login)
        {
            var statements = new List<(string sql, object param)>();
            statements.Add((ParameterSetQB.UPDATE_PARAMETERSET, ParameterSetDTO));

            foreach (var d in ParameterSetDTO.ParameterSetDetailArray)
            {
                d.ParameterSetId = ParameterSetDTO.ParameterSetId;
                if (d.ParameterSetDetailLineId == 0)
                    statements.Add((ParameterSetQB.SAVE_PARAMETERSET_DETAIL, d));
                else
                    statements.Add((ParameterSetQB.UPDATE_PARAMETERSET_DETAIL, d));
            }

            await _QueryExecutor.ExecuteInTransactionAsync(login, statements);
            return ParameterSetDTO.ParameterSetId;
        }

        public async Task<int> DeleteParameterSetDetail(int ParameterSetId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    ParameterSetQB.DELETE_PARAMETERSET_DETAIL,
                    new { ParameterSetid = ParameterSetId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting ParameterSet Detail for ID {ParameterSetId}", ex);
            }
        }


        public async Task<int> DeleteParameterSet(int ParameterSetId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    ParameterSetQB.DELETE_PARAMETERSET,
                    new { ParameterSetid = ParameterSetId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting ParameterSet with ID {ParameterSetId}", ex);
            }
        }
    }

}