using Dapper;
using GB5Shared.CriteriaHandler;
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.Advance;
using PayRollDAL.DTO.Leave;
using PayRollDAL.Query.Advance;
using PayRollDAL.Query.Leave;
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.Advance
{
    public class AdvanceDAL : IAdvanceDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        public async Task<string> GetAdvance(int AdvanceId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = AdvanceQB.GET_ADVANCE;
                var parameters = new { advanceid = AdvanceId };
                AdvanceDTO AdvanceDTOs = await _QueryExecutor.QuerySingleAsync<AdvanceDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(AdvanceDTOs);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListAdvance(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string baseSql = AdvanceQB.GET_SELECTLIST_ADVANCE;

                    var parameters = new DynamicParameters();
                    parameters.Add("firstnumber", FirstNumber);
                    parameters.Add("maxresult", MaxResult);

                    var (whereClause, _) = CriteriaBuilder.Build(CriteriaDTO, parameters, baseSql);
                    string SQL = baseSql.Replace("/*CRITERIA*/", whereClause);

                    List<AdvancePicklistDTO> AdvancePicklistDTO = (await _QueryExecutor.QueryAsync<AdvancePicklistDTO>(LoginDTO, SQL, parameters)).ToList();

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



        public async Task<string> GetSelectListAdvanceEmployee(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string searchText = "";
                if (CriteriaDTO?.SectionCriteriaList?.Count > 0)
                {
                    var attributes = CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList;
                    if (attributes != null)
                    {
                        foreach (var item in attributes)
                        {
                            if (item.FieldName.ToLower() == "searchtext" || item.FieldName.ToLower() == "advancenumber")
                            {
                                searchText = Convert.ToString(item.FieldValue is JsonElement je ? je.GetString() : item.FieldValue) ?? "";
                            }
                        }
                    }
                }

                string baseSql = AdvanceQB.GET_SELECTLIST_ADVANCEEMPLOYEE;

                var parameters = new DynamicParameters();
                parameters.Add("firstnumber", -1);
                parameters.Add("maxresult", -1);
                parameters.Add("searchtext", searchText);

                var (whereClause, _) = CriteriaBuilder.Build(CriteriaDTO, parameters, baseSql);
                string SQL = baseSql.Replace("/*CRITERIA*/", whereClause);

                List<TempAdvanceDTO> TempAdvanceDTOs = (await _QueryExecutor.QueryAsync<TempAdvanceDTO>(LoginDTO, SQL, parameters)).ToList();
                return JsonConvert.SerializeObject(TempAdvanceDTOs);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetAdvanceReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                NormalizeAdvanceReportCriteria(CriteriaDTO);

                string baseSql = AdvanceQB.GET_ADVANCE_LIST;

                var parameters = new DynamicParameters();
                parameters.Add("firstnumber", FirstNumber);
                parameters.Add("maxresult", MaxResult);

                var (whereClause, _) = CriteriaBuilder.Build(CriteriaDTO, parameters, baseSql);
                string SQL = baseSql.Replace("/*CRITERIA*/", whereClause);

                var result = await _QueryExecutor.QueryAsync<AdvanceDTO>(LoginDTO, SQL, parameters);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Ported from GB4 CommonFunctionFrameDAL.ApplyCriteria/FromEpoch quirks that
        // the legacy Advance report client still relies on:
        //  - PeriodFromDate/PeriodToDate arrive as Unix-epoch seconds (string/number),
        //    not literal dates — GB4 ran them through FromEpoch() before comparing.
        //  - EmployeeId (and other In/NotIn fields) may arrive as a scalar FieldValue
        //    instead of InArray — GB4's ApplyCriteria never read InArray at all, it
        //    substituted FieldValue directly into "IN(...)". GB5's CriteriaBuilder
        //    requires InArray for In/NotIn, so a lone FieldValue is promoted here.
        private static readonly HashSet<string> _epochSecondsDateFields =
            new(StringComparer.OrdinalIgnoreCase) { "PeriodFromDate", "PeriodToDate" };

        private static void NormalizeAdvanceReportCriteria(CriteriaDTO CriteriaDTO)
        {
            if (CriteriaDTO?.SectionCriteriaList == null) return;

            foreach (var section in CriteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;

                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr?.FieldName == null) continue;

                    if (_epochSecondsDateFields.Contains(attr.FieldName)
                        && attr.FieldValue != null
                        && long.TryParse(Convert.ToString(attr.FieldValue), out long epochSeconds))
                    {
                        attr.FieldValue = DateTimeOffset.FromUnixTimeSeconds(epochSeconds).UtcDateTime;
                    }

                    if ((attr.OperationType == GB5Shared.DTO.Framework.Criteria.CriteriaDTO.OperationType.In
                         || attr.OperationType == GB5Shared.DTO.Framework.Criteria.CriteriaDTO.OperationType.NotIn)
                        && (attr.InArray == null || attr.InArray.Length == 0)
                        && attr.FieldValue != null)
                    {
                        attr.InArray = new[] { attr.FieldValue };
                    }
                }
            }
        }

        public async Task<int> SaveAdvance(AdvanceDTO AdvanceDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {

                string AdvanceSql = AdvanceQB.SAVE_ADVANCE;
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, AdvanceSql, AdvanceDTO, DbTransaction);
               
                return result;
            }
            catch (Exception)
            {
            
                throw;
            }
        }


        public async Task<int> UpdateAdvance(AdvanceDTO AdvanceDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {

           
            try
            {
                string AdvanceSql = AdvanceQB.UPDATE_ADVANCE;
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, AdvanceSql, AdvanceDTO, DbTransaction);
                
                return result;
            }
            catch (Exception)
            {
            
                throw;
            }
        }




        public async Task<string> DeleteAdvance(int AdvanceId, LoginDTO LoginDTO)
        {
           

            try
            {
                string AdvanceSql = AdvanceQB.DELETE_ADVANCE;
                var Parameters = new { advanceid = AdvanceId };

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

                

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

    }
}