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

namespace MMDAL.CustomCode.PaymentTerm
{
    public class PaymentTermDAL : IPaymentTermDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetPaymentTerm(int PaymentTermId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = PaymentTermQB.GET_PAYMENTTERM;
                var parameters = new { paymenttermid = PaymentTermId };

                var PaymentTermDict = new Dictionary<int, PaymentTermDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<PaymentTermDTO, PaymentTermDetailDTO, PaymentTermDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!PaymentTermDict.TryGetValue(parent.PaymentTermId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.PaymentTermDetailArray = new List<PaymentTermDetailDTO>();
                            PaymentTermDict[parent.PaymentTermId] = existingParent;
                        }

                        if (child != null && child.PaymentTermDetailId != 0)
                        {
                            existingParent.PaymentTermDetailArray.Add(child);
                        }

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

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

        public async Task<int> SavePaymentTerm(PaymentTermDTO PaymentTermDTO, LoginDTO LoginDTO)
        {
            try
            {
                // SQL Server queries only
                string PaymentTermSql = PaymentTermQB.SAVE_PAYMENTTERM;
                string detailSql = PaymentTermQB.SAVE_PAYMENTTERM_DETAIL;
                string countSql = PaymentTermQB.GET_PAYMENTTERM_COUNT;

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

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

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

                if (PaymentTermDTO.PaymentTermDetailArray?.Count > 0)
                {
                    foreach (var detail in PaymentTermDTO.PaymentTermDetailArray)
                    {
                        detail.PaymentTermId = PaymentTermDTO.PaymentTermId;
                        statements.Add((detailSql, detail));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

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


        public async Task<int> UpdatePaymentTerm(PaymentTermDTO PaymentTermDTO, LoginDTO Login)
        {
            var statements = new List<(string sql, object param)>();
            statements.Add((PaymentTermQB.UPDATE_PAYMENTTERM, PaymentTermDTO));

            foreach (var d in PaymentTermDTO.PaymentTermDetailArray)
            {
                d.PaymentTermId = PaymentTermDTO.PaymentTermId;
                if (d.PaymentTermDetailId == 0)
                    statements.Add((PaymentTermQB.SAVE_PAYMENTTERM_DETAIL, d));
                else
                    statements.Add((PaymentTermQB.UPDATE_PAYMENTTERM_DETAIL, d));
            }

            await _QueryExecutor.ExecuteInTransactionAsync(Login, statements);
            return PaymentTermDTO.PaymentTermId;
        }

        public async Task<int> DeletePaymentTermDetail(int PaymentTermId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PaymentTermQB.DELETE_PAYMENTTERM_DETAIL,
                    new { paymenttermid = PaymentTermId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting PaymentTerm detail for ID {PaymentTermId}", ex);
            }
        }


        public async Task<int> DeletePaymentTerm(int PaymentTermId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PaymentTermQB.DELETE_PAYMENTTERM,
                    new { paymenttermid = PaymentTermId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting PaymentTerm with ID {PaymentTermId}", ex);
            }
        }

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