using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
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.DTO.MRPPartyType;
using MMDAL.Query.MRPPartyType;
using Newtonsoft.Json;

namespace MMDAL.CustomCode.MRPPartyType
{
    public class MRPPartyTypeDAL : IMRPPartyTypeDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetMRPPartyType(int MRPPartyTypeId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = MRPPartyTypeQB.GET_MRPPARTYTYPE;
                var parameters = new { mrppartytypeid = MRPPartyTypeId };

                var MRPPartyTypeDict = new Dictionary<int, MRPPartyTypeDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<MRPPartyTypeDTO, MRPPartyTypeDetailDTO, MRPPartyTypeDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!MRPPartyTypeDict.TryGetValue(parent.MRPPartyTypeId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.MRPPartyTypeDetailArray = new List<MRPPartyTypeDetailDTO>();
                            MRPPartyTypeDict[parent.MRPPartyTypeId] = existingParent;
                        }

                        if (child != null && child.MRPPartyTypeDetailId != 0)
                        {
                            existingParent.MRPPartyTypeDetailArray.Add(child);
                        }

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

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

        public async Task<string> GetSelectListMRPPartyType(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = MRPPartyTypeQB.GET_SELECTLIST_MRPPARTYTYPE;
                var Result = await _QueryExecutor.QueryAsync<MRPPartyTypePicklistDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }



        public async Task<int> SaveMRPPartyType(MRPPartyTypeDTO MRPPartyTypeDTO, LoginDTO LoginDTO)
        {
            try
            {
                // SQL Server queries only
                string MRPPartyTypeSql = MRPPartyTypeQB.SAVE_MRPPARTYTYPE;
                string detailSql = MRPPartyTypeQB.SAVE_MRPPARTYTYPE_DETAIL;
                string countSql = MRPPartyTypeQB.GET_MRPPARTYTYPE_COUNT;

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

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

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

                if (MRPPartyTypeDTO.MRPPartyTypeDetailArray?.Count > 0)
                {
                    foreach (var detail in MRPPartyTypeDTO.MRPPartyTypeDetailArray)
                    {
                        detail.MRPPartyTypeId = MRPPartyTypeDTO.MRPPartyTypeId;
                        statements.Add((detailSql, detail));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

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


        public async Task<int> UpdateMRPPartyType(MRPPartyTypeDTO MRPPartyTypeDTO, LoginDTO login)
        {
            var statements = new List<(string sql, object param)>();
            statements.Add((MRPPartyTypeQB.UPDATE_MRPPARTYTYPE, MRPPartyTypeDTO));

            foreach (var d in MRPPartyTypeDTO.MRPPartyTypeDetailArray)
            {
                d.MRPPartyTypeId = MRPPartyTypeDTO.MRPPartyTypeId;
                if (d.MRPPartyTypeDetailId == 0)
                    statements.Add((MRPPartyTypeQB.SAVE_MRPPARTYTYPE_DETAIL, d));
                else
                    statements.Add((MRPPartyTypeQB.UPDATE_MRPPARTYTYPE_DETAIL, d));
            }

            await _QueryExecutor.ExecuteInTransactionAsync(login, statements);
            return MRPPartyTypeDTO.MRPPartyTypeId;
        }

        public async Task<int> DeleteMRPPartyTypeDetail(int MRPPartyTypeId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    MRPPartyTypeQB.DELETE_MRPPARTYTYPE_DETAIL,
                    new { mrppartytypeid = MRPPartyTypeId }
                );
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }


        public async Task<int> DeleteMRPPartyType(int MRPPartyTypeId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    MRPPartyTypeQB.DELETE_MRPPARTYTYPE,
                    new { mrppartytypeid = MRPPartyTypeId }
                );
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }

    }
}