using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Validation;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using PayrollDAL.DTO.AdditionDeduction;
using PayRollDAL.Query.AdditionDeduction;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.AdditionDeduction
{
   

    public class AdditionDeductionDAL : IAdditionDeductionDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IValidation _validation;

        public AdditionDeductionDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _queryExecutor = queryExecutor;
            _validation = validation;
        }

        public async Task<string> GetAdditionDeductionListAsync(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool isCount = false)
        {
            try
            {
                string json = "";

                // Parse criteria values
                var (nature, applicableType) = ParseCriteria(criteriaDTO);

                // Validate parsed values
                if (nature == -1 || applicableType == -1)
                {
                    return isCount ? "0" : "[]";
                }

                if (!isCount)
                {
                    string sql = AdditionDeductionQB.GET_ADDITIONDEDUCTION_LIST;

                    var parameters = new
                    {
                        FirstNumber = firstNumber,
                        MaxResult = maxResult,
                        Nature = nature,
                        ApplicableType = applicableType
                    };

                    IEnumerable<AdditionDeductionDTO> result =
                        await _queryExecutor.QueryAsync<AdditionDeductionDTO>(loginDTO, sql, parameters);

                    json = JsonConvert.SerializeObject(result);
                }
                else
                {
                    string sql = AdditionDeductionQB.GET_ADDITIONDEDUCTION_COUNT;

                    var parameters = new
                    {
                        FirstNumber = -1,
                        MaxResult = -1,
                        Nature = nature,
                        ApplicableType = applicableType
                    };

                    var result = await _queryExecutor.QueryAsync<dynamic>(loginDTO, sql, parameters);
                    int count = result.FirstOrDefault()?.TotalCount ?? 0;
                    json = count.ToString();
                }

                return json;
            }
            catch (Exception ex)
            {
                throw new Exception($"Error in GetAdditionDeductionListAsync DAL: {ex.Message}", ex);
            }
        }

        public async Task<string> GetSelectListAdditionDeduction(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool isCount = false)
        {
            try
            {
                if (!isCount)
                {
                    var parameters = new { FirstNumber = firstNumber, MaxResult = maxResult };
                    IEnumerable<AdditionDeductionPicklistDTO> result =
                        await _queryExecutor.QueryAsync<AdditionDeductionPicklistDTO>(loginDTO, AdditionDeductionQB.GET_SELECTLIST_ADDITIONDEDUCTION, parameters);
                    return JsonConvert.SerializeObject(result);
                }
                else
                {
                    var result = await _queryExecutor.QueryAsync<dynamic>(loginDTO, AdditionDeductionQB.GET_SELECTLIST_ADDITIONDEDUCTION_COUNT, null);
                    int count = result.FirstOrDefault()?.TotalCount ?? 0;
                    return count.ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception($"Error in GetSelectListAdditionDeduction DAL: {ex.Message}", ex);
            }
        }

        public async Task<string> GetAdditionDeduction(int additionDeductionId, LoginDTO loginDTO)
        {
            try
            {
                string sql = AdditionDeductionQB.GET_ADDITIONDEDUCTION;
                var parameters = new { AdditionDeductionId = additionDeductionId };

                AdditionDeductionDTO data =
                    await _queryExecutor.QuerySingleAsync<AdditionDeductionDTO>(loginDTO, sql, parameters);

                return JsonConvert.SerializeObject(data);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveAdditionDeduction(AdditionDeductionDTO dto, LoginDTO loginDTO)
        {
            try
            {
                const string sql = AdditionDeductionQB.SAVE_ADDITIONDEDUCTION;
                return await _queryExecutor.ExecuteAsync(loginDTO, sql, dto);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while saving AdditionDeduction", ex);
            }
        }

        public async Task<int> UpdateAdditionDeduction(AdditionDeductionDTO dto, LoginDTO loginDTO)
        {
            try
            {
                const string sql = AdditionDeductionQB.UPDATE_ADDITIONDEDUCTION;
                return await _queryExecutor.ExecuteAsync(loginDTO, sql, dto);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while updating AdditionDeduction", ex);
            }
        }

        public async Task<int> DeleteAdditionDeduction(int additionDeductionId, LoginDTO loginDTO)
        {
            try
            {
                const string sql = AdditionDeductionQB.DELETE_ADDITIONDEDUCTION;
                var parameters = new { AdditionDeductionId = additionDeductionId };

                return await _queryExecutor.ExecuteAsync(loginDTO, sql, parameters);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting AdditionDeduction", ex);
            }
        }

        private (int Nature, int ApplicableType) ParseCriteria(CriteriaDTO criteriaDTO)
        {
            int nature = -1;
            int applicableType = -1;

            if (criteriaDTO?.SectionCriteriaList != null)
            {
                foreach (var section in criteriaDTO.SectionCriteriaList)
                {
                    if (section.AttributesCriteriaList != null)
                    {
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            if (string.Equals(attr.FieldName, "Nature", StringComparison.OrdinalIgnoreCase))
                            {
                                nature = ExtractIntValue(attr.FieldValue);
                            }
                            else if (string.Equals(attr.FieldName, "ApplicableType", StringComparison.OrdinalIgnoreCase))
                            {
                                applicableType = ExtractIntValue(attr.FieldValue);
                            }
                        }
                    }
                }
            }

            return (nature, applicableType);
        }

        private int ExtractIntValue(object value)
        {
            if (value == null)
                return -1;

            try
            {
                if (value is System.Text.Json.JsonElement jsonElement)
                {
                    switch (jsonElement.ValueKind)
                    {
                        case System.Text.Json.JsonValueKind.Number:
                            return jsonElement.GetInt32();
                        case System.Text.Json.JsonValueKind.String:
                            string strValue = jsonElement.GetString();
                            if (int.TryParse(strValue, out int intValue))
                                return intValue;
                            break;
                        default:
                            string rawText = jsonElement.GetRawText();
                            if (int.TryParse(rawText, out int parsedValue))
                                return parsedValue;
                            break;
                    }
                }
                else if (value is Newtonsoft.Json.Linq.JToken jToken)
                {
                    return jToken.Value<int>();
                }
                else
                {
                    return Convert.ToInt32(value);
                }
            }
            catch
            {
                return -1;
            }

            return -1;
        }

        public async Task<List<AddOnFieldDTO>> GetAdditionDeductionFields(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            // GB4 parity: GB4's real criteria field is "PayIds" (comma-separated, matched via
            // Equal-as-IN-list against the exploded PAYCONFIGURATIONIDS row). "PayConfigurationId"
            // (single value) is kept as an accepted alias for backward compatibility with existing
            // GB5 callers of this endpoint.
            var payIds = new List<int>();
            if (criteriaDTO?.SectionCriteriaList != null)
            {
                foreach (var section in criteriaDTO.SectionCriteriaList)
                {
                    if (section.AttributesCriteriaList == null) continue;
                    foreach (var attr in section.AttributesCriteriaList)
                    {
                        if (string.Equals(attr.FieldName, "PayIds", StringComparison.OrdinalIgnoreCase))
                        {
                            string raw = attr.FieldValue?.ToString() ?? string.Empty;
                            foreach (var part in raw.Split(',', StringSplitOptions.RemoveEmptyEntries | StringSplitOptions.TrimEntries))
                                if (int.TryParse(part, out int id)) payIds.Add(id);
                        }
                        else if (string.Equals(attr.FieldName, "PayConfigurationId", StringComparison.OrdinalIgnoreCase))
                        {
                            int single = ExtractIntValue(attr.FieldValue);
                            if (single != -1) payIds.Add(single);
                        }
                    }
                }
            }

            var parameters = new Dapper.DynamicParameters();
            string payIdsFilter = string.Empty;
            if (payIds.Count > 0)
            {
                var paramNames = new List<string>();
                for (int i = 0; i < payIds.Count; i++)
                {
                    string name = $"PayId{i}";
                    paramNames.Add($"@{name}");
                    parameters.Add(name, payIds[i]);
                }
                payIdsFilter = $"AND b.DATA IN ({string.Join(", ", paramNames)})";
            }

            string sql = AdditionDeductionQB.GET_ADDITIONDEDUCTION_FIELDS.Replace(":payidsfilter", payIdsFilter);

            var result = await _queryExecutor.QueryAsync<AddOnFieldDTO>(loginDTO, sql, parameters);
            return result.ToList();
        }
    }
}