using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using MMDAL.CustomCode.ParameterSet;
using MMDAL.DTO.Grade;
using MMDAL.DTO.ParameterSet;
using MMDAL.Query.ParameterSet;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.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, int? partyId, int? itemId, LoginDTO loginDTO)
        {
            try
            {
                string sql = ParameterSetQB.GET_PARAMETERSET;

                var parameter = new
                {
                    parametersetid = parameterSetId,
                    partyid = partyId,
                    itemid = itemId
                };

                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;
            }
        }

        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);
            }
        }
    }

}