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.MaterialGrade;
using MMDAL.DTO.Grade;
using MMDAL.DTO.MaterialGrade;
using MMDAL.DTO.ParameterSet;
using MMDAL.DTO.Specification;
using MMDAL.Query.Grade;
using MMDAL.Query.MaterialGrade;
using MMDAL.Query.SKU;
using MMDAL.Query.Specification;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MMDAL.CustomCode.Grade
{
    public class GradeDAL : IGradeDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<IEnumerable<MaterialGradeDTO>> GetGrade(LoginDTO loginDTO,string sql,object parameters)
        {
            var gradeDict = new Dictionary<long, MaterialGradeDTO>();

            var result = await _QueryExecutor.QueryMultiMapAsync<MaterialGradeDTO,SpecificationDTO,SpecificationDetailDTO,MaterialGradeDTO>(loginDTO,sql,
                (grade, spec, detail) =>
                {
                    // 🔹 Parent
                    if (!gradeDict.TryGetValue(grade.MaterialGradeId, out var currentGrade))
                    {
                        currentGrade = grade;
                        currentGrade.SpecificationArray = new List<SpecificationDTO>();
                        gradeDict.Add(currentGrade.MaterialGradeId, currentGrade);
                    }

                    // 🔹 Child
                    if (spec != null && spec.SpecificationId != 0)
                    {
                        var currentSpec = currentGrade.SpecificationArray
                            .FirstOrDefault(s => s.SpecificationId == spec.SpecificationId);

                        if (currentSpec == null)
                        {
                            currentSpec = spec;
                            currentSpec.SpecificationDetailArray = new List<SpecificationDetailDTO>();
                            currentGrade.SpecificationArray.Add(currentSpec);
                        }

                        // 🔹 Sub-child
                        if (detail != null && detail.SpecificationDetailId != 0 &&
                            !currentSpec.SpecificationDetailArray
                                .Any(d => d.SpecificationDetailId == detail.SpecificationDetailId))
                        {
                            currentSpec.SpecificationDetailArray.Add(detail);
                        }
                    }

                    return currentGrade;
                },
                parameters,
                splitOn: "SpecificationId,SpecificationDetailId"
            );

            return gradeDict.Values.ToList();
        }

        public async Task<string> GetSelectListGrade(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string SQL = GradeQB.GET_SELECTLIST_GRADE;
                var Result = await _QueryExecutor.QueryAsync<GradePickListDTO>(LoginDTO, SQL, null!);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<int> SaveGrade(GradeDTO GradeDTO, LoginDTO LoginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>();

                /* -------- Material Grade -------- */
                statements.Add((MaterialGradeQB.SAVE_MATERIALGRADE, GradeDTO.MaterialGrade));

                /* -------- Specification -------- */
                statements.Add((SpecificationQB.SAVE_SPECIFICATION, GradeDTO.Specification));

                /* -------- Specification Details -------- */
                if (GradeDTO.Specification.SpecificationDetailArray?.Count > 0)
                {
                    foreach (var d in GradeDTO.Specification.SpecificationDetailArray)
                    {
                        statements.Add((SpecificationQB.SAVE_SPECIFICATION_DETAIL, d));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);
                return 1;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while saving Grade: {ex.Message}", ex);
            }
        }
        public async Task<int> UpdateGrade(GradeDTO GradeDTO, LoginDTO LoginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>();

                /* -------- MaterialGrade UPDATE -------- */
                statements.Add((MaterialGradeQB.UPDATE_MATERIALGRADE, GradeDTO.MaterialGrade));

                /* -------- Specification UPDATE -------- */
                statements.Add((SpecificationQB.UPDATE_SPECIFICATION, GradeDTO.Specification));

                /* -------- SpecificationDetail INSERT / UPDATE -------- */
                if (GradeDTO.Specification.SpecificationDetailArray?.Count > 0)
                {
                    foreach (var d in GradeDTO.Specification.SpecificationDetailArray)
                    {
                        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 1;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while updating Grade: {ex.Message}", ex);
            }
        }
        public async Task<int> DeleteSpecificationDetail(int MaterialGradeId, LoginDTO loginDTO)
        {
            return await _QueryExecutor.ExecuteAsync(
                loginDTO,
                GradeQB.DELETE_SPECIFICATION_DETAIL,
                new { gradeid = MaterialGradeId }
            );
        }

        public async Task<int> DeleteSpecification(int MaterialGradeId, LoginDTO loginDTO)
        {
            return await _QueryExecutor.ExecuteAsync(
                loginDTO,
                GradeQB.DELETE_SPECIFICATION,
                new { gradeid = MaterialGradeId }
            );
        }

        public async Task<int> DeleteGrade(int MaterialGradeId, LoginDTO loginDTO)
        {
            return await _QueryExecutor.ExecuteAsync(
                loginDTO,
                GradeQB.DELETE_MATERIAL_GRADE,
                new { gradeid = MaterialGradeId }
            );
        }
        public async Task<IEnumerable<MaterialGradeDTO>> GetGradeList(LoginDTO loginDTO,string sql)
        {
            var gradeDictionary =
                new Dictionary<long, MaterialGradeDTO>();

            await _QueryExecutor.QueryMultiMapAsync<
                MaterialGradeDTO,
                SpecificationDTO,
                SpecificationDetailDTO,
                MaterialGradeDTO>
            (
                loginDTO,
                sql,

                (grade, specification, specificationDetail) =>
                {
                    // =====================================
                    // MATERIAL GRADE
                    // =====================================

                    if (!gradeDictionary.TryGetValue(
                        grade.MaterialGradeId,
                        out var currentGrade))
                    {
                        currentGrade = grade;

                        currentGrade.SpecificationArray =
                            new List<SpecificationDTO>();

                        gradeDictionary.Add(
                            currentGrade.MaterialGradeId,
                            currentGrade
                        );
                    }

                    // =====================================
                    // SPECIFICATION
                    // =====================================

                    if (
                        specification != null &&
                        specification.SpecificationId > 0
                    )
                    {
                        var currentSpecification =
                            currentGrade
                            .SpecificationArray
                            .FirstOrDefault(
                                x =>
                                x.SpecificationId ==
                                specification.SpecificationId
                            );

                        if (currentSpecification == null)
                        {
                            currentSpecification =
                                specification;

                            currentSpecification
                            .SpecificationDetailArray =
                                new List<
                                    SpecificationDetailDTO
                                >();

                            currentGrade
                            .SpecificationArray
                            .Add(
                                currentSpecification
                            );
                        }

                        // =================================
                        // SPECIFICATION DETAIL
                        // =================================

                        if (
                            specificationDetail != null &&
                            specificationDetail
                            .SpecificationDetailId > 0
                        )
                        {
                            bool detailExists =
                                currentSpecification
                                .SpecificationDetailArray
                                .Any(
                                    x =>
                                    x.SpecificationDetailId ==
                                    specificationDetail
                                    .SpecificationDetailId
                                );

                            if (!detailExists)
                            {
                                currentSpecification
                                .SpecificationDetailArray
                                .Add(
                                    specificationDetail
                                );
                            }
                        }
                    }

                    return currentGrade;
                },

                null,

                splitOn:
                "SpecificationId,SpecificationDetailId"
            );

            return gradeDictionary
                .Values
                .ToList();
        }

    }
}