using Dapper;
using FrameworkDAL.DTO.City;
using FrameworkDAL.DTO.Country;
using FrameworkDAL.DTO.Gcm;
using FrameworkDAL.Query.City;
using FrameworkDAL.Query.Country;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Microsoft.Identity.Client;
using Newtonsoft.Json;
using System.Text.Json;

namespace FrameworkDAL.CustomCode.City
{
    public class CityDAL : ICityDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public CityDAL(IQueryExecutor queryExecutor, IValidation IValidation)
        {
            _QueryExecutor = queryExecutor;
            _Validation = IValidation;
        }

        public async Task<CityDTO> GetCity(int cityId, LoginDTO loginDTO)
        {
            try
            {
                string sql = CityQB.GET_CITY;
                var parameters = new { cityid = cityId };
                CityDTO CityDTOs = await _QueryExecutor.QuerySingleAsync<CityDTO>(loginDTO, sql, parameters);
                return CityDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveCity(CityDTO CityDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = CityQB.SAVE_CITY;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, CityDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.SaveErrorMessage}");
                throw new Exception(Error);
            }
        }


        public async Task<int> UpdateCity(CityDTO CityDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = CityQB.UPDATE_CITY;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, CityDTO);
            }
            catch (Exception ex)
            {
                string Error = await _Validation.HandleException(ex, $"{ErrorResponse.UpdateErrorMessage}");
                throw new Exception(Error);
            }
        }

        public async Task<string> GetSelectListCity(
            int FirstNumber,
            int MaxResult,
            LoginDTO loginDTO,
            CriteriaDTO criteriaDTO,
            bool IsCount = false)
        {
            if (IsCount)
                return "0"; // Counting not implemented here

            string sql = CityQB.GET_CITYPICKLIST_FOR_SELECT2;
            var parameters = new DynamicParameters();
            var whereConditions = new List<string>();

            // Pagination parameters
            parameters.Add("@firstnumber", FirstNumber);
            parameters.Add("@maxresult", MaxResult);

            // 🔥 Merge request + user criteria (GB4 behavior)
            var mergedCriteria = MergeCriteria(criteriaDTO, loginDTO.UserCriteriaDTO);

            // 🔥 Apply criteria dynamically
            ApplyCityCriteria(mergedCriteria, whereConditions, parameters);

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

            // Execute query
            var result = await _QueryExecutor.QueryAsync<CityPicklistDTO>(
                loginDTO,
                sql,
                parameters
            );

            return JsonConvert.SerializeObject(result);
        }

        // ===============================
        // CRITERIA MERGE (GB4 CORE LOGIC)
        // ===============================
        private IEnumerable<AttributesCriteriaDTO> MergeCriteria(
            CriteriaDTO requestCriteria,
            CriteriaDTO userCriteria)
        {
            var list = new List<AttributesCriteriaDTO>();

            if (requestCriteria?.SectionCriteriaList != null)
                list.AddRange(requestCriteria.SectionCriteriaList
                    .SelectMany(x => x.AttributesCriteriaList));

            if (userCriteria?.SectionCriteriaList != null)
                list.AddRange(userCriteria.SectionCriteriaList
                    .SelectMany(x => x.AttributesCriteriaList));

            return list;
        }

        // ===============================
        // APPLY CRITERIA → SQL WHERE
        // ===============================
        private void ApplyCityCriteria(
            IEnumerable<AttributesCriteriaDTO> criteria,
            List<string> where,
            DynamicParameters parameters)
        {
            foreach (var c in criteria)
            {
                if (!CityCriteriaMap.TryGetValue(c.FieldName, out var column))
                    continue;

                var value = GetClrValue(c.FieldValue);
                if (value == null) continue;

                switch (c.OperationType)
                {
                    case CriteriaDTO.OperationType.Equal:
                        where.Add($"{column} = @{Param(c.FieldName)}");
                        parameters.Add($"@{Param(c.FieldName)}", value);
                        break;

                    case CriteriaDTO.OperationType.In:
                        if (value is int intValue && intValue < 0) continue;
                        where.Add($"{column} IN @{Param(c.FieldName)}");
                        parameters.Add($"@{Param(c.FieldName)}", c.InArray ?? new[] { value });
                        break;

                    case CriteriaDTO.OperationType.Like:
                        if (string.IsNullOrWhiteSpace(Convert.ToString(value)))
                            continue;
                        where.Add($"{column} LIKE @{Param(c.FieldName)}");
                        parameters.Add($"@{Param(c.FieldName)}", $"%{value}%");
                        break;
                }
            }
        }

        // ===============================
        // FIELD → COLUMN MAP
        // ===============================
        private static readonly Dictionary<string, string> CityCriteriaMap =
            new(StringComparer.OrdinalIgnoreCase)
        {
            { "Id", "city.CITYID" },
            { "Code", "city.CITYCODE" },
            { "Name", "city.CITYNAME" },
            { "Country.Id", "country.COUNTRYID" },
            { "State.Id", "states.STATEID" }
        };

        // ===============================
        // HELPERS
        // ===============================
        private static string Param(string field)
            => field.Replace(".", "").Replace("_", "");

        private static object GetClrValue(object value)
        {
            if (value is JsonElement json)
            {
                return json.ValueKind switch
                {
                    JsonValueKind.Number => json.GetInt64(),
                    JsonValueKind.String => json.GetString(),
                    JsonValueKind.True => true,
                    JsonValueKind.False => false,
                    _ => null
                };
            }
            return value;
        }

        public async Task<string> DeleteCity(int CityId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = CityQB.DELETE_CITY;
                var Parameters = new { Cityid = CityId };
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, Sql, Parameters);

                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);
            }
        }
    }
}