using System.Data.Common;
using AccountsDAL.DTO.CostCenter;
using AccountsDAL.Query.AccountGroup;
using AccountsDAL.Query.CostCenter;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Newtonsoft.Json;

namespace AccountsDAL.CustomCode.CostCenter
{
    public class CostCenterDAL : ICostCenterDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;

        public CostCenterDAL(IQueryExecutor QueryExecutor, IValidation Validation)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
        }


        public async Task<string> GetSelectListCostCenter(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                string Json = "";
                if (IsCount == false)
                {
                    string SQL = CostCenterQB.GET_SELECTLIST_COSTCENTER;
                    var parameter = new { firstnumber = FirstNumber, maxresult = MaxResult };
                    var Result = await _QueryExecutor.QueryAsync<CostCenterPicklistDTO>(LoginDTO, SQL, parameter);
                    Json = JsonConvert.SerializeObject(Result);
                }
                else
                {
                    int Count = 0;
                    Json = Count.ToString();
                }
                return Json;
            }
            catch (Exception)
            {
                throw;
            }



        }

        public async Task<Result<string>> SaveCostCenter(
        CostCenterDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            dto.CostCenterCreatedById = loginDTO.UserId;
            dto.CostCenterCreatedOn = DateTime.UtcNow;
            dto.CostCenterModifiedById = loginDTO.UserId;
            dto.CostCenterModifiedOn = DateTime.UtcNow;

            int rows = await _QueryExecutor.ExecuteAsync(loginDTO, CostCenterQB.SAVE_COSTCENTER, dto, tx)
                .ConfigureAwait(false);
            return rows > 0
                ? Result<string>.Success("Cost Center Saved Successfully.")
                : Result<string>.Failure("Failed to save Cost Center.");
        }

        public async Task<Result<string>> UpdateCostCenter(
            CostCenterDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            dto.CostCenterModifiedById = loginDTO.UserId;
            dto.CostCenterModifiedOn = DateTime.UtcNow;

            int rows = await _QueryExecutor.ExecuteAsync(loginDTO, CostCenterQB.UPDATE_COSTCENTER, dto, tx)
                .ConfigureAwait(false);
            return rows > 0
                ? Result<string>.Success("Cost Center Saved Successfully.")
                : Result<string>.Failure("Failed to save Cost Center.");
        }

        public async Task<string> GetCostCenter(int costCenterId, LoginDTO loginDTO)
        {
            try
            {
                var dto = await _QueryExecutor
                    .QuerySingleAsync<CostCenterDTO>(loginDTO, CostCenterQB.GET_COSTCENTER,
                        new { costcenterid = costCenterId })
                    .ConfigureAwait(false);
                return JsonConvert.SerializeObject(dto);
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<string> DeleteCostCenter(
           int costCenterId, LoginDTO loginDTO)
        {
            try
            {
                var param = new { CostCenterId = costCenterId };
                int result = await _QueryExecutor.ExecuteAsync(loginDTO, CostCenterQB.DELETE_COSTCENTER, param)
                                                 .ConfigureAwait(false);
                if (result > 0)
                {
                    return $"{SuccessResponse.DeleteSuccessMessage}";
                }
                else
                {
                    return $"{ErrorResponse.DeleteNotFoundMessage}";
                }

            }
            catch (Exception)
            {
                throw;
            }
        }

        // Full-DTO cost center list (CostCenterDTO, not CostCenterPicklistDTO) — same criteria
        // filtering shape as AccountDAL.GetAccountList, but no FirstNumber/MaxResult paging.
        public async Task<string> GetCostCenterList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql        = CostCenterQB.GET_COSTCENTER_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _QueryExecutor.QueryAsync<CostCenterDTO>(LoginDTO, sql, parameter)
                                                .ConfigureAwait(false)).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 AccountDAL.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 AccountDAL.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);
        }

    }
}