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

namespace PayRollDAL.CustomeCode.AdvanceRepayment
{
    public class AdvanceRepaymentDAL : IAdvanceRepaymentDAL
    {
        private readonly IQueryExecutor _QueryExecutor;

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

        public async Task<string> GetAdvanceRepayment(int AdvanceRepaymentId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = AdvanceRepaymentQB.GET_ADVANCEREPAYMENT;
                var parameters = new { advancerepaymentid = AdvanceRepaymentId };
                AdvanceRepaymentDTO AdvanceRepaymentDTOs = await _QueryExecutor.QuerySingleAsync<AdvanceRepaymentDTO>(LoginDTO, sql, parameters);
                return JsonConvert.SerializeObject(AdvanceRepaymentDTOs);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListAdvanceRepayment(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = AdvanceRepaymentQB.GET_SELECTLIST_ADVANCEREPAYMENT;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    List<AdvanceRepaymentPicklistDTO> AdvanceRepaymentPicklistDTO = (await _QueryExecutor.QueryAsync<AdvanceRepaymentPicklistDTO>(LoginDTO, SQL, parameter)).ToList();

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

        // Whitelist of caller-facing FieldNames this endpoint accepts. CriteriaBuilder
        // resolves each FieldName to its owning table alias itself (via the SELECT-list
        // "AS <FieldName>" it finds in GET_ADVANCEREPAYMENT_LIST) and also uses FieldName
        // verbatim as the Dapper parameter name — so entries here must be bare, unprefixed
        // names that match a SELECT alias exactly, never "alias.column".
        private static readonly HashSet<string> _filterableFields = new(StringComparer.OrdinalIgnoreCase)
        {
            "AdvanceRepaymentId",
            "OUId",
            "AdvanceId",
            "BizTransactionTypeId",
            "BIZTransactionClassId",
            "AdvanceRepaymentRepaymentNumber",
            "AdvanceRepaymentRepaymentDate",
            "PaymentTypeId",
            "BankId",
            "AdvanceRepaymentStatus",
            "EmployeeId",
            // PeriodFromDate/PeriodToDate are handled separately in
            // ExtractPeriodDateRange — CriteriaBuilder can only filter on a column
            // whose real name equals the FieldName itself (it uses the SELECT-list
            // alias only to find the owning table prefix, never to remap the column
            // name), and there is no physical PERIODFROMDATE/PERIODTODATE column —
            // both logical filters resolve to the single A.ADVANCEDATE column.
        };

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

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

                section.AttributesCriteriaList = section.AttributesCriteriaList
                    .Where(attr => attr?.FieldName != null && _filterableFields.Contains(attr.FieldName))
                    .ToList();
            }
        }

        // GB4 clients still send PeriodFromDate/PeriodToDate as Unix-epoch seconds
        // (string/number) rather than literal dates.
        private static DateTime? ParseEpochSecondsDate(object? fieldValue)
        {
            if (fieldValue != null && long.TryParse(Convert.ToString(fieldValue), out long epochSeconds))
                return DateTimeOffset.FromUnixTimeSeconds(epochSeconds).UtcDateTime;
            return null;
        }

        // Pulls PeriodFromDate/PeriodToDate out of the criteria (see the comment on
        // _filterableFields) and returns a manually-built "A.ADVANCEDATE BETWEEN ..."
        // clause, since CriteriaBuilder cannot express "two different FieldNames,
        // one physical column" — both would otherwise collide on parameter name
        // ADVANCEDATE if run through CriteriaBuilder as-is.
        private static string ExtractPeriodDateRange(CriteriaDTO? criteriaDTO, DynamicParameters parameters)
        {
            DateTime? fromDate = null;
            DateTime? toDate = null;

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

                    section.AttributesCriteriaList = section.AttributesCriteriaList
                        .Where(attr =>
                        {
                            if (attr?.FieldName == null) return true;

                            if (attr.FieldName.Equals("PeriodFromDate", StringComparison.OrdinalIgnoreCase))
                            {
                                fromDate = ParseEpochSecondsDate(attr.FieldValue);
                                return false;
                            }
                            if (attr.FieldName.Equals("PeriodToDate", StringComparison.OrdinalIgnoreCase))
                            {
                                toDate = ParseEpochSecondsDate(attr.FieldValue);
                                return false;
                            }
                            return true;
                        })
                        .ToList();
                }
            }

            if (fromDate == null && toDate == null) return string.Empty;

            if (fromDate != null && toDate != null)
            {
                parameters.Add("periodfromdate", fromDate.Value);
                parameters.Add("periodtodate", toDate.Value);
                return "AND A.ADVANCEDATE BETWEEN @periodfromdate AND @periodtodate";
            }
            if (fromDate != null)
            {
                parameters.Add("periodfromdate", fromDate.Value);
                return "AND A.ADVANCEDATE >= @periodfromdate";
            }
            parameters.Add("periodtodate", toDate!.Value);
            return "AND A.ADVANCEDATE <= @periodtodate";
        }

        public async Task<string> GetAdvanceRepaymentList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new DynamicParameters();
                string periodClause = ExtractPeriodDateRange(CriteriaDTO, parameters);
                ApplyFieldNameWhitelist(CriteriaDTO);

                string baseSql = AdvanceRepaymentQB.GET_ADVANCEREPAYMENT_LIST;
                var (whereClause, _) = CriteriaBuilder.Build(CriteriaDTO, parameters, baseSql);
                string sql = baseSql.Replace("/*CRITERIA*/", whereClause + " " + periodClause);

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

        public async Task<int> SaveAdvanceRepayment(AdvanceRepaymentDTO AdvanceRepaymentDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                string AdvanceRepaymentSql = AdvanceRepaymentQB.SAVE_ADVANCEREPAYMENT;
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, AdvanceRepaymentSql, AdvanceRepaymentDTO, DbTransaction);

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> UpdateAdvanceRepayment(AdvanceRepaymentDTO AdvanceRepaymentDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                string AdvanceRepaymentSql = AdvanceRepaymentQB.UPDATE_ADVANCEREPAYMENT;
                int result = await _QueryExecutor.ExecuteAsync(LoginDTO, AdvanceRepaymentSql, AdvanceRepaymentDTO, DbTransaction);

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> DeleteAdvanceRepayment(int AdvanceRepaymentId, LoginDTO LoginDTO)
        {
            try
            {
                string AdvanceRepaymentSql = AdvanceRepaymentQB.DELETE_ADVANCEREPAYMENT;
                var Parameters = new { advancerepaymentid = AdvanceRepaymentId };

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

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