using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FrameworkDAL.DTO.DrillDown;
using FrameworkDAL.Query.DrillDown;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace FrameworkDAL.CustomCode.DrillDown
{
    public class DrillDownDAL : IDrillDownDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetDrillDown(int DrillDownId, LoginDTO LoginDTO)
        {
            try
            {
                var sql = DrillDownQB.GET_DRILLDOWN;
                var parameters = new { drilldownid = DrillDownId };

                var DrillDownDict = new Dictionary<int, DrillDownDTO>();

                var result = await _QueryExecutor.QueryMultiMapAsync<DrillDownDTO, DrillDownDetailDTO, DrillDownDTO>(
                    LoginDTO,
                    sql,
                    (parent, child) =>
                    {
                        if (!DrillDownDict.TryGetValue(parent.DrillDownId, out var existingParent))
                        {
                            existingParent = parent;
                            existingParent.DrillDownDetailArray = new List<DrillDownDetailDTO>();
                            DrillDownDict[parent.DrillDownId] = existingParent;
                        }

                        if (child != null && child.DrillDownDetailId != 0)
                        {
                            existingParent.DrillDownDetailArray.Add(child);
                        }

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

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

        public async Task<int> SaveDrillDown(DrillDownDTO DrillDownDTO, LoginDTO loginDTO)
        {
            try
            {

                string DrillDownSql = DrillDownQB.SAVE_DRILLDOWN;
                string childSql = DrillDownQB.SAVE_DRILLDOWNDETAIL;

                var statements = new List<(string sql, object param)>
                {
                    (DrillDownSql, DrillDownDTO)
                };

                if (DrillDownDTO.DrillDownDetailArray != null && DrillDownDTO.DrillDownDetailArray.Count > 0)
                {
                    foreach (var child in DrillDownDTO.DrillDownDetailArray)
                    {
                        child.DrillDownId = DrillDownDTO.DrillDownId;
                        statements.Add((childSql, child));
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(loginDTO, statements);

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

        public async Task<int> UpdateDrillDown(DrillDownDTO DrillDownDTO, LoginDTO loginDTO)
        {
            try
            {
                var statements = new List<(string sql, object param)>();

                statements.Add((
                    DrillDownQB.UPDATE_DRILLDOWN,
                    DrillDownDTO
                ));

                var parameters = new { drilldownid = DrillDownDTO.DrillDownId };
                var maxId = await _QueryExecutor.ExecuteScalarAsync<int>(
                    loginDTO,
                    DrillDownQB.GET_MAX_DRILLDOWNDETAILID,
                    parameters
                );

                maxId = maxId - 1;

                if (DrillDownDTO.DrillDownDetailArray != null)
                {
                    foreach (var child in DrillDownDTO.DrillDownDetailArray)
                    {
                        if (child.DrillDownDetailId == 0)
                        {
                            child.DrillDownDetailId = maxId--;
                            statements.Add((
                                DrillDownQB.SAVE_DRILLDOWNDETAIL,
                                child
                            ));
                        }
                        else
                        {
                            statements.Add((
                                DrillDownQB.UPDATE_DRILLDOWNDETAIL,
                                child
                            ));
                        }
                    }
                }

                await _QueryExecutor.ExecuteInTransactionAsync(loginDTO, statements);

                return DrillDownDTO.DrillDownId;
            }
            catch (Exception ex)
            {
                throw new Exception($"{ErrorResponse.UpdateErrorMessage}: {ex.Message}", ex);
            }
        }

        public async Task<Result<string>> DeleteDrillDown(int DrillDownId, LoginDTO LoginDTO)
        {
            try
            {
                string childsql = DrillDownQB.DELETE_DRILLDOWNDETAIL;
                string parentSql = DrillDownQB.DELETE_DRILLDOWN;
                var parameter = new { drilldownid = DrillDownId };
                var childresult = await _QueryExecutor.ExecuteAsync(LoginDTO, childsql, parameter);
                var parentresult = await _QueryExecutor.ExecuteAsync(LoginDTO, parentSql, parameter);

                if (parentresult > 0)
                {
                    return Result<string>.Success("DrillDown Details Deleted Successfully.");
                }
                else
                {
                    return Result<string>.Failure("DrillDown not found or could not be deleted.");
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting DrillDown Details", ex);
            }
        }

        public async Task<string> GetSelectListDrillDown(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = DrillDownQB.GET_SELECTLIST_DRILLDOWN;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    List<DrillDownPicklistDTO> DrillDownPicklistDTO = (await _QueryExecutor.QueryAsync<DrillDownPicklistDTO>(LoginDTO, SQL, parameter)).ToList();

                    Json = JsonConvert.SerializeObject(DrillDownPicklistDTO);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}