using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using FrameworkDAL.DTO.LoadTemplate;
using FrameworkDAL.Query.LoadTemplate;
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.Text.Json;

namespace FrameworkDAL.CustomCode.LoadTemplate
{
    public class LoadTemplateDAL : ILoadTemplateDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

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

        public async Task<LoadTemplateDTO> GetLoadTemplate(int LoadTemplateId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = LoadTemplateQB.GET_LOADTEMPLATE;
                var parameters = new { loadtemplateid = LoadTemplateId };
                LoadTemplateDTO LoadTemplateDTOs = await _QueryExecutor.QuerySingleAsync<LoadTemplateDTO>(LoginDTO, sql, parameters);
                return LoadTemplateDTOs;
            }
            catch (Exception)
            {
                throw;
            }
        }


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

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



        public async Task<string> DeleteLoadTemplate(int LoadTemplateId, LoginDTO LoginDTO)
        {
            try
            {
                string Sql = LoadTemplateQB.DELETE_LOADTEMPLATE;
                var parameter = new { loadtemplateid = LoadTemplateId };
                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);
            }
        }

        // Ported from GB4 fws/LoadTemplate.svc's SelectList operation — mirrors CityDAL.GetSelectListCity's
        // pattern exactly (dynamic criteria merge + /**WHERE_CLAUSE**/ injection + ROW_NUMBER paging).
        // Unlike City's IsCount branch (stubbed to "0"), this runs a real COUNT query, since GB4's
        // service layer genuinely depends on an accurate Total for the pagination envelope (it calls
        // this twice: once for the page, once with FirstNumber=-1/MaxResult=-1/IsCount=true).
        public async Task<string> GetSelectListLoadTemplate(
            int FirstNumber,
            int MaxResult,
            LoginDTO loginDTO,
            CriteriaDTO criteriaDTO = null!,
            bool IsCount = false)
        {
            var mergedCriteria = MergeCriteria(criteriaDTO, loginDTO.UserCriteriaDTO);
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();

            ApplyLoadTemplateCriteria(mergedCriteria, whereConditions, parameters);

            string whereClause = whereConditions.Any()
                ? "AND " + string.Join(" AND ", whereConditions)
                : string.Empty;

            if (IsCount)
            {
                string countSql = LoadTemplateQB.GET_LOADTEMPLATE_COUNT.Replace("/**WHERE_CLAUSE**/", whereClause);
                int total = await _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, countSql, parameters);
                return total.ToString();
            }

            string sql = LoadTemplateQB.GET_LOADTEMPLATEPICKLIST_FOR_SELECT2.Replace("/**WHERE_CLAUSE**/", whereClause);
            parameters.Add("@firstnumber", FirstNumber);
            parameters.Add("@maxresult", MaxResult);

            var result = await _QueryExecutor.QueryAsync<LoadTemplatePicklistDTO>(loginDTO, sql, parameters);
            return JsonConvert.SerializeObject(result);
        }

        // ===============================
        // CRITERIA MERGE (GB4 CORE LOGIC) — same shape as CityDAL's
        // ===============================
        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 ApplyLoadTemplateCriteria(
            IEnumerable<AttributesCriteriaDTO> criteria,
            List<string> where,
            DynamicParameters parameters)
        {
            foreach (var c in criteria)
            {
                if (!LoadTemplateCriteriaMap.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
        // ===============================
        // "ObjectTypeId" is the key field the Voucher "Load Template" picker filters by
        // (EntityConstant.OBJECTVOUCHER) — ObjectId/OUId are also filterable even though only
        // ObjectId is projected in LoadTemplatePicklistDTO, matching City's pattern of allowing
        // filter-only fields (Country.Id/State.Id) that aren't in CityPicklistDTO either.
        private static readonly Dictionary<string, string> LoadTemplateCriteriaMap =
            new(StringComparer.OrdinalIgnoreCase)
        {
            { "Id", "LT.LOADTEMPLATEID" },
            { "TemplateName", "LT.TEMPLATENAME" },
            { "ObjectId", "LT.OBJECTID" },
            { "ObjectTypeId", "LT.OBJECTTYPEID" },
            { "OUId", "LT.OUID" }
        };

        // ===============================
        // 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;
        }
    }
}