using Dapper;
using FrameworkDAL.DTO.Region;
using FrameworkDAL.DTO.State;
using FrameworkDAL.Query.RegionType;
using FrameworkDAL.Query.State;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Validation;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.CustomCode.State
{
    public class StateDAL : IStateDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<string> GetSelectListState(
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO)
        {
            try
            {
                string sql = StateQB.GET_SELECTLIST_STATE;
                var parameters = new DynamicParameters();
                var whereConditions = new List<string>();

                if (criteriaDTO?.SectionCriteriaList?.Any() == true)
                {
                    foreach (var section in criteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if ((attr.FieldName == "Country.Id" || attr.FieldName == "CountryId")
                                && attr.FieldValue != null)
                            {
                                long countryId = GetLongValue(attr.FieldValue);

                                if (attr.OperationType == CriteriaDTO.OperationType.In)
                                {
                                    whereConditions.Add("S.COUNTRYID IN @CountryIds");
                                    parameters.Add("@CountryIds", new long[] { countryId });
                                }
                                else if (attr.OperationType == CriteriaDTO.OperationType.Equal)
                                {
                                    whereConditions.Add("S.COUNTRYID = @CountryId");
                                    parameters.Add("@CountryId", countryId);
                                }
                            }
                        }
                    }
                }

                sql = sql.Replace(
                    "/**WHERE_CLAUSE**/",
                    whereConditions.Any()
                        ? "WHERE " + string.Join(" AND ", whereConditions)
                        : string.Empty
                );

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

                return JsonConvert.SerializeObject(result);
            }
            catch
            {
                throw;
            }
        }
        private static long GetLongValue(object value)
        {
            if (value is System.Text.Json.JsonElement json)
            {
                if (json.ValueKind == System.Text.Json.JsonValueKind.Number)
                    return json.GetInt64();
            }

            return Convert.ToInt64(value);
        }


        public async Task<string> GetState(int StateId, LoginDTO LoginDTO)
        {
            string json = "";
            try
            {
                string sql = StateQB.GET_STATE;
                var parameters = new { stateId = StateId };
                StateDTO StateDTOs = await _QueryExecutor.QuerySingleAsync<StateDTO>(LoginDTO, sql, parameters);
                json = JsonConvert.SerializeObject(StateDTOs);
                return json;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                json = null;
            }
        }


        public async Task<int> SaveState(StateDTO stateDTO, LoginDTO loginDTO)
        {
            try
            {
                string sql = StateQB.SAVE_STATE;
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, stateDTO);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(error);
            }
        }

        public async Task<int> UpdateState(StateDTO stateDTO, LoginDTO loginDTO)
        {
            try
            {
                string sql = StateQB.UPDATE_STATE;
                return await _QueryExecutor.ExecuteAsync(loginDTO, sql, stateDTO);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(error);
            }
        }
        public async Task<string> DeleteState(int StateId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = StateQB.DELETE_State;
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, new { StateId });

                if (result > 0)
                {
                    return ("State deleted successfully.");
                }
                else
                {
                    return ("State not found or could not be deleted.");
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting State", ex);
            }
        }
    }
}