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.Pack;
using MMDAL.Query.PackSet;
using Newtonsoft.Json;

namespace MMDAL.CustomCode.PackSet
{
    public class PackSetDAL : IPackSetDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetPackSet(int PackSetId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = PackSetQB.GET_PACKSET;
                var parameters = new { packsetid = PackSetId };

                var PackSetDict = new Dictionary<int, PackSetDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<PackSetDTO, PackSetDetailDTO, PackSetDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!PackSetDict.TryGetValue(parent.PackSetId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.PackSetDetailArray = new List<PackSetDetailDTO>();
                            PackSetDict[parent.PackSetId] = existingParent;
                        }

                        if (child != null && child.PackSetDetailId != 0)
                        {
                            existingParent.PackSetDetailArray.Add(child);
                        }

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

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

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



        public async Task<int> SavePackSet(PackSetDTO PackSetDTO, LoginDTO LoginDTO)
        {
            try
            {
                // SQL Server queries only
                string PackSetSql = PackSetQB.SAVE_PACKSET;
                string detailSql = PackSetQB.SAVE_PACKSET_DETAIL;
                string countSql = PackSetQB.GET_PACKSET_COUNT;

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

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

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

                if (PackSetDTO.PackSetDetailArray?.Count > 0)
                {
                    foreach (var detail in PackSetDTO.PackSetDetailArray)
                    {
                        detail.PackSetId = PackSetDTO.PackSetId;
                        statements.Add((detailSql, detail));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(LoginDTO, statements);

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


        public async Task<int> UpdatePackSet(PackSetDTO PackSetDTO, LoginDTO login)
        {
            var statements = new List<(string sql, object param)>();
            statements.Add((PackSetQB.UPDATE_PACKSET, PackSetDTO));

            foreach (var d in PackSetDTO.PackSetDetailArray)
            {
                d.PackSetId = PackSetDTO.PackSetId;
                if (d.PackSetDetailId == 0)
                    statements.Add((PackSetQB.SAVE_PACKSET_DETAIL, d));
                else
                    statements.Add((PackSetQB.UPDATE_PACKSET_DETAIL, d));
            }

            await _QueryExecutor.ExecuteInTransactionAsync(login, statements);
            return PackSetDTO.PackSetId;
        }

        public async Task<int> DeletePackSetDetail(int PackSetId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PackSetQB.DELETE_PACKSET_DETAIL,
                    new { packsetid = PackSetId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting PackSet detail for ID {PackSetId}", ex);
            }
        }


        public async Task<int> DeletePackSet(int PackSetId, LoginDTO LoginDTO)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PackSetQB.DELETE_PACKSET,
                    new { packsetid = PackSetId }
                );
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while deleting PackSet with ID {PackSetId}", ex);
            }
        }
    }
}