using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Newtonsoft.Json;
using PayrollDAL.DTO.AdditionDeductionDetail;
using PayRollDAL.Query.AdditionDeductionDetail;

namespace PayRollDAL.CustomeCode.AdditionDeductionDetail
{
    public class AdditionDeductionDetailDAL : IAdditionDeductionDetailDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public AdditionDeductionDetailDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        public async Task<string> GetAdditionDeductionDetail(int additionDeductionDetailId, LoginDTO loginDTO)
        {
            try
            {
                string sql = AdditionDeductionDetailQB.GET_ADDITIONDEDUCTIONDETAIL;
                var parameters = new { AdditionDeductionDetailId = additionDeductionDetailId };

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

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

        public async Task<int> SaveAdditionDeductionDetail(AdditionDeductionDetailDTO dto, LoginDTO loginDTO)
        {
            try
            {
                const string sql = AdditionDeductionDetailQB.SAVE_ADDITIONDEDUCTIONDETAIL;
                return await _queryExecutor.ExecuteAsync(loginDTO, sql, dto);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while saving AdditionDeductionDetail", ex);
            }
        }

        public async Task<int> UpdateAdditionDeductionDetail(AdditionDeductionDetailDTO dto, LoginDTO loginDTO)
        {
            try
            {
                const string sql = AdditionDeductionDetailQB.UPDATE_ADDITIONDEDUCTIONDETAIL;
                return await _queryExecutor.ExecuteAsync(loginDTO, sql, dto);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while updating AdditionDeductionDetail", ex);
            }
        }

        public async Task<int> DeleteAdditionDeductionDetail(int additionDeductionDetailId, LoginDTO loginDTO)
        {
            try
            {
                const string sql = AdditionDeductionDetailQB.DELETE_ADDITIONDEDUCTIONDETAIL;
                var parameters = new { AdditionDeductionDetailId = additionDeductionDetailId };

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

        public async Task<string> GetSelectListAdditionDeductionDetail(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool isCount = false)
        {
            try
            {
                if (!isCount)
                {
                    var parameters = new { FirstNumber = firstNumber, MaxResult = maxResult };

                    IEnumerable<AdditionDeductionDetailPicklistDTO> result =
                        await _queryExecutor.QueryAsync<AdditionDeductionDetailPicklistDTO>(
                            loginDTO, AdditionDeductionDetailQB.GET_SELECTLIST_ADDITIONDEDUCTIONDETAIL, parameters);

                    return JsonConvert.SerializeObject(result);
                }
                else
                {
                    int count = await _queryExecutor.ExecuteScalarAsync<int>(
                        loginDTO, AdditionDeductionDetailQB.GET_SELECTLIST_ADDITIONDEDUCTIONDETAIL_COUNT, null);

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

        public async Task<string> GetAdditionDeductionDetailListAsync(
            int firstNumber,
            int maxResult,
            CriteriaDTO criteriaDTO,
            LoginDTO loginDTO,
            bool isCount = false)
        {
            try
            {
                var (employeeId, payPeriodId) = ParseCriteria(criteriaDTO);

                if (!isCount)
                {
                    string sql = AdditionDeductionDetailQB.GET_ADDITIONDEDUCTIONDETAIL_LIST;

                    var parameters = new
                    {
                        FirstNumber = firstNumber,
                        MaxResult = maxResult,
                        EmployeeId = employeeId,
                        PayPeriodId = payPeriodId
                    };

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

                    return JsonConvert.SerializeObject(result);
                }
                else
                {
                    var parameters = new { EmployeeId = employeeId, PayPeriodId = payPeriodId };

                    int count = await _queryExecutor.ExecuteScalarAsync<int>(
                        loginDTO, AdditionDeductionDetailQB.GET_ADDITIONDEDUCTIONDETAIL_LIST_COUNT, parameters);

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

        // EmployeeId/PayPeriodId are optional filters — a criteria that omits either (or both)
        // defaults to -1, meaning "no filter on that column", so callers can list every detail row
        // as well as scope down to a single employee/period.
        private (int EmployeeId, int PayPeriodId) ParseCriteria(CriteriaDTO criteriaDTO)
        {
            int employeeId = -1;
            int payPeriodId = -1;

            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, "EmployeeId", StringComparison.OrdinalIgnoreCase))
                        {
                            employeeId = ExtractIntValue(attr.FieldValue);
                        }
                        else if (string.Equals(attr.FieldName, "PayPeriodId", StringComparison.OrdinalIgnoreCase))
                        {
                            payPeriodId = ExtractIntValue(attr.FieldValue);
                        }
                    }
                }
            }

            return (employeeId, payPeriodId);
        }

        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.ToObject<int>();
                }
                else
                {
                    return Convert.ToInt32(value);
                }
            }
            catch
            {
                return -1;
            }

            return -1;
        }
    }
}