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

namespace PayRollDAL.CustomeCode.PayGroup
{
    public class PayGroupDAL : IPayGroupDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public PayGroupDAL(IQueryExecutor QueryExecutor, IValidation validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = validation;
        }

        public async Task<string> GetSelectListPayGroup(
    int FirstNumber,
    int MaxResult,
    CriteriaDTO CriteriaDTO,
    LoginDTO LoginDTO)
        {
            try
            {
                string SQL = PayGroupQB.GET_SELECTLIST_PAYGROUP;

                var parameter = new
                {
                    firstnumber = FirstNumber,
                    maxresult = MaxResult
                };

                var Result = await _QueryExecutor.QueryAsync<PayGroupPicklistDTO>(
                    LoginDTO,
                    SQL,
                    parameter
                );

                string Json = JsonConvert.SerializeObject(Result);

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

        // ── Get single record ─────────────────────────────────────────────

        public async Task<PayGroupDTO> GetPayGroup(int PayGroupId, LoginDTO LoginDTO)
        {
            try
            {
                var parameter = new { paygroupid = PayGroupId };

                return await _QueryExecutor.QuerySingleAsync<PayGroupDTO>(
                    LoginDTO,
                    PayGroupQB.GET_PAYGROUP,
                    parameter);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Insert ────────────────────────────────────────────────────────

        public async Task<int> SavePayGroup(PayGroupDTO PayGroupDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PayGroupQB.SAVE_PAYGROUP,
                    PayGroupDTO,
                    DbTransaction);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Update ────────────────────────────────────────────────────────

        public async Task<int> UpdatePayGroup(PayGroupDTO PayGroupDTO, LoginDTO LoginDTO, DbTransaction DbTransaction)
        {
            try
            {
                return await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PayGroupQB.UPDATE_PAYGROUP,
                    PayGroupDTO,
                    DbTransaction);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Delete ────────────────────────────────────────────────────────

        public async Task<string> DeletePayGroup(int PayGroupId, LoginDTO LoginDTO)
        {
            try
            {
                var parameter = new { paygroupid = PayGroupId };

                int result = await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    PayGroupQB.DELETE_PAYGROUP,
                    parameter);

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

        // ── List (unpaged, criteria-filtered) ───────────────────────────────

        public async Task<string> GetPayGroupList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);

                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };

                var list = (await _QueryExecutor.QueryAsync<PayGroupDTO>(
                    LoginDTO,
                    PayGroupQB.GET_PAYGROUP_LIST,
                    parameter)).ToList();

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

        // Picklist search-as-you-type sends the typed text as a Like-operation attribute on each
        // searchable field with the same FieldValue repeated per field — take the first non-empty
        // one found. Copied from CostCenterDAL.ExtractLikeSearchText.
        private static string? ExtractLikeSearchText(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return null;
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr?.OperationType == CriteriaDTO.OperationType.Like && attr.FieldValue != null)
                    {
                        var text = attr.FieldValue.ToString();
                        if (!string.IsNullOrWhiteSpace(text)) return text;
                    }
                }
            }
            return null;
        }

        // Extracts a Status attribute as (Equals, NotEquals) so both directions are honored;
        // whichever one wasn't sent stays null and its corresponding SQL condition is a no-op.
        // Copied from CostCenterDAL.ExtractStatusFilter.
        private static (int? EqualTo, int? NotEqualTo) ExtractStatusFilter(CriteriaDTO criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return (null, null);
            foreach (var section in criteriaDTO.SectionCriteriaList)
            {
                if (section?.AttributesCriteriaList == null) continue;
                foreach (var attr in section.AttributesCriteriaList)
                {
                    if (attr == null || !string.Equals(attr.FieldName, "Status", StringComparison.OrdinalIgnoreCase))
                        continue;
                    if (attr.FieldValue == null || !int.TryParse(attr.FieldValue.ToString(), out int value))
                        continue;

                    if (attr.OperationType == CriteriaDTO.OperationType.Equal)
                        return (value, null);
                    if (attr.OperationType == CriteriaDTO.OperationType.NotEqual)
                        return (null, value);
                }
            }
            return (null, null);
        }
    }
}