using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using PayRollDAL.DTO.EmiChangeRequest;
using PayRollDAL.Query.EmiChangeRequest;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace PayRollDAL.CustomeCode.EmiChangeRequest
{
    public class EmiChangeRequestDAL : IEmiChangeRequestDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

        public EmiChangeRequestDAL(IQueryExecutor IQueryExecutor)
        {
            _QueryExecutor = IQueryExecutor;
        }

        public async Task<string> GetEmiChangeRequest(int EmiChangeRequestId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = EmiChangeRequestQB.GET_EMICHANGEREQUEST;
                var parameters = new { emichangerequestid = EmiChangeRequestId };
                EmiChangeRequestDTO EmiChangeRequestDTOs = await _QueryExecutor.QuerySingleAsync<EmiChangeRequestDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(EmiChangeRequestDTOs);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListEmiChangeRequest(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = EmiChangeRequestQB.GET_SELECTLIST_EMICHANGEREQUEST;

                    int OUId = -1, PayPeriodId = -1, BizTransactionTypeId = -1;
                    string Number = "", EmployeeName = "";

                    if (CriteriaDTO != null && CriteriaDTO.SectionCriteriaList != null && CriteriaDTO.SectionCriteriaList.Count > 0)
                    {
                        foreach (var Attribute in CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList)
                        {
                            object? Value = NormalizeValue(Attribute.FieldValue);
                            if (Value == null) continue;

                            switch (Attribute.FieldName?.ToLower())
                            {
                                case "ouid":
                                    OUId = Convert.ToInt32(Value);
                                    break;
                                case "payperiodid":
                                    PayPeriodId = Convert.ToInt32(Value);
                                    break;
                                case "biztransactiontypeid":
                                    BizTransactionTypeId = Convert.ToInt32(Value);
                                    break;
                                case "number":
                                    Number = Convert.ToString(Value);
                                    break;
                                case "employeename":
                                    EmployeeName = Convert.ToString(Value);
                                    break;
                            }
                        }
                    }

                    var parameter = new
                    {
                        firstnumber = FirstNumber,
                        maxresult = MaxResult,
                        ouid = OUId,
                        payperiodid = PayPeriodId,
                        biztransactiontypeid = BizTransactionTypeId,
                        number = Number ?? "",
                        employeename = EmployeeName ?? ""
                    };

                    List<EmiChangeRequestPicklistDTO> EmiChangeRequestPicklistDTOs = (await _QueryExecutor.QueryAsync<EmiChangeRequestPicklistDTO>(LoginDTO, SQL, parameter)).ToList();

                    Json = JsonConvert.SerializeObject(EmiChangeRequestPicklistDTOs);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> CheckSaveUpdationPossible(int OUId, int PayPeriodId, int EmployeeId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = EmiChangeRequestQB.CHECK_SAVE_UPDATION_POSSIBLE;
                var parameters = new { ouid = OUId, payperiodid = PayPeriodId, employeeid = EmployeeId };
                return await _QueryExecutor.ExecuteScalarAsync<int>(LoginDTO, sql, parameters);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<(DateTime FromDate, DateTime ToDate)> GetPeriodDateRange(int PeriodId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = EmiChangeRequestQB.GET_PERIOD_DATE_RANGE;
                var parameters = new { periodid = PeriodId };
                var Result = await _QueryExecutor.QuerySingleAsync<PeriodDateRangeDTO>(LoginDTO, sql, parameters);
                return (Result.FromDate, Result.ToDate);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> SaveEmiChangeRequest(EmiChangeRequestDTO EmiChangeRequestDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                string EmiChangeRequestSql = EmiChangeRequestQB.SAVE_EMICHANGEREQUEST;
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, EmiChangeRequestSql, EmiChangeRequestDTO, DbTransaction);
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> UpdateEmiChangeRequest(EmiChangeRequestDTO EmiChangeRequestDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                string EmiChangeRequestSql = EmiChangeRequestQB.UPDATE_EMICHANGEREQUEST;
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, EmiChangeRequestSql, EmiChangeRequestDTO, DbTransaction);
                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> DeleteEmiChangeRequest(int EmiChangeRequestId, LoginDTO LoginDTO)
        {
            try
            {
                string EmiChangeRequestSql = EmiChangeRequestQB.DELETE_EMICHANGEREQUEST;
                var Parameters = new { emichangerequestid = EmiChangeRequestId };

                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, EmiChangeRequestSql, Parameters);

                if (result > 0)
                {
                    return SuccessResponse.DeleteSuccessMessage;
                }
                else
                {
                    return ErrorResponse.DeleteNotFoundMessage;
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        private struct PeriodDateRangeDTO
        {
            public DateTime FromDate { get; set; }
            public DateTime ToDate { get; set; }
        }

        // System.Text.Json deserializes CriteriaDTO.AttributesCriteriaDTO.FieldValue (object)
        // as a boxed JsonElement, not a primitive — Convert.ToInt32/ToString throw
        // InvalidCastException ("JsonElement" does not implement IConvertible) unless
        // unwrapped first. Mirrors GB5Shared.CriteriaHandler.CriteriaBuilder.NormalizeValue,
        // which is private to that class and not reusable from here.
        private static object? NormalizeValue(object? value)
        {
            if (value is not JsonElement je)
                return value;

            return je.ValueKind switch
            {
                JsonValueKind.String => je.GetString(),

                JsonValueKind.Number =>
                    je.TryGetInt32(out var i) ? (object)i :
                    je.TryGetInt64(out var l) ? (object)l :
                    je.TryGetDecimal(out var d) ? (object)d :
                    (object)je.GetDouble(),

                JsonValueKind.True => (object)true,
                JsonValueKind.False => (object)false,
                JsonValueKind.Null => null,
                _ => (object)je.ToString()
            };
        }
    }
}