using Dapper;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using MMDAL.DTO.ItemSubCategory;
using MMDAL.Query.ItemSubCategory;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace MMDAL.CustomCode.ItemSubCategory
{
    public class ItemSubCategoryDAL : IItemSubCategoryDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<ItemSubCategoryDTO> GetItemSubCategory(int ItemSubCategoryId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = ItemSubCategoryQB.GET_ITEMSUBCATEGORY;
                var parameters = new { itemsubcategoryid = ItemSubCategoryId };
                ItemSubCategoryDTO ItemSubCategoryDTOs = await _QueryExecutor.QuerySingleAsync<ItemSubCategoryDTO>(LoginDTO, sql, parameters);
                return ItemSubCategoryDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListItemSubCategory(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                string sql = ItemSubCategoryQB.GET_SELECTLIST_ITEMSUBCATEGORY;

                DynamicParameters parameters = new DynamicParameters();

                int? itemCategoryId = null;

                if (criteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in criteriaDTO.SectionCriteriaList)
                    {
                        foreach (var criteria in section.AttributesCriteriaList)
                        {
                            if (criteria.FieldName.Equals("ItemSubCategoryItemCategory.Id", StringComparison.OrdinalIgnoreCase))
                            {
                                if (criteria.FieldValue is JsonElement jsonElement)
                                {
                                    string value = jsonElement.ToString();

                                    if (!string.IsNullOrWhiteSpace(value))
                                    {
                                        itemCategoryId = Convert.ToInt32(value);
                                    }
                                }
                            }
                        }
                    }
                }

                if (itemCategoryId.HasValue)
                {
                    sql += " WHERE ISC.ITEMCATEGORYID = @ItemCategoryId";
                    parameters.Add("@ItemCategoryId", itemCategoryId.Value);
                }

                var result = await _QueryExecutor.QueryAsync<ItemSubCategoryPicklistDTO>(
                    loginDTO,
                    sql,
                    parameters);

                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }


        public async Task<int> SaveItemSubCategory(ItemSubCategoryDTO ItemSubCategoryDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = ItemSubCategoryQB.SAVE_ITEMSUBCATEGORY;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, ItemSubCategoryDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<int> UpdateItemSubCategory(ItemSubCategoryDTO ItemSubCategoryDTO, LoginDTO LoginDTO)
        {
            try
            {
                const string Sql = ItemSubCategoryQB.UPDATE_ITEMSUBCATEGORY;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, ItemSubCategoryDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }



        public async Task<string> DeleteItemSubCategory(int ItemSubCategoryId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = ItemSubCategoryQB.DELETE_ITEMSUBCATEGORY;
                var parameter = new { itemsubcategoryid = ItemSubCategoryId };
                var result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, parameter);

                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.DeleteErrorMessage}");
                throw new Exception(Error);
            }
        }
    }
}