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

namespace MMDAL.CustomCode.Specification
{
    public class SpecificationDAL : ISpecificationDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetSpecification(int SpecificationId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = SpecificationQB.GET_SPECIFICATION;
                var parameters = new { Specificationid = SpecificationId };

                var SpecificationDict = new Dictionary<int, SpecificationDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<SpecificationDTO, SpecificationDetailDTO, SpecificationDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!SpecificationDict.TryGetValue(parent.SpecificationId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.SpecificationDetailArray = new List<SpecificationDetailDTO>();
                            SpecificationDict[parent.SpecificationId] = existingParent;
                        }

                        if (child != null && child.SpecificationDetailId != 0)
                        {
                            existingParent.SpecificationDetailArray.Add(child);
                        }

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

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

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


        public async Task<int> SaveSpecification(SpecificationDTO SpecificationDTO, LoginDTO LoginDTO)
        {
            try
            {
                // SQL Server queries only
                string SpecificationSql = SpecificationQB.SAVE_SPECIFICATION;
                string detailSql = SpecificationQB.SAVE_SPECIFICATION_DETAIL;
                string countSql = SpecificationQB.GET_SPECIFICATION_COUNT;

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

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

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

                if (SpecificationDTO.SpecificationDetailArray?.Count > 0)
                {
                    foreach (var detail in SpecificationDTO.SpecificationDetailArray)
                    {
                        detail.SpecificationId = SpecificationDTO.SpecificationId;
                        statements.Add((detailSql, detail));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

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


        public async Task<int> UpdateSpecification(SpecificationDTO SpecificationDTO, LoginDTO LoginDTO)
        {
            var statements = new List<(string sql, object param)>();
            statements.Add((SpecificationQB.UPDATE_SPECIFICATION, SpecificationDTO));

            foreach (var d in SpecificationDTO.SpecificationDetailArray)
            {
                d.SpecificationId = SpecificationDTO.SpecificationId;
                if (d.SpecificationDetailId == 0)
                    statements.Add((SpecificationQB.SAVE_SPECIFICATION_DETAIL,d));
                else
                    statements.Add((SpecificationQB.UPDATE_SPECIFICATION_DETAIL,d));
            }
                
            await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);
            return SpecificationDTO.SpecificationId;
        }

        public async Task<int> DeleteSpecificationDetail(int SpecificationId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    SpecificationQB.DELETE_SPECIFICATION_DETAIL,
                    new { Specificationid = SpecificationId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting Specification detail for ID {SpecificationId}", ex);
            }
        }


        public async Task<int> DeleteSpecification(int SpecificationId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    SpecificationQB.DELETE_SPECIFICATION,
                    new { Specificationid = SpecificationId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting Specification with ID {SpecificationId}", ex);
            }
        }

    }
}