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

namespace AccountsDAL.CustomCode.AccountGroup
{
    public class AccountGroupDAL : IAccountGroupDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation    _Validation;

        public AccountGroupDAL(IQueryExecutor queryExecutor, IValidation validation)
        {
            _QueryExecutor = queryExecutor;
            _Validation    = validation;
        }

        public async Task<AccountGroupDTO> GetAccountGroup(int accountGroupId, LoginDTO loginDTO)
        {
            try
            {
                var sql   = AccountGroupQB.GET_ACCOUNTGROUP;
                var param = new { AccountGroupId = accountGroupId };
                return await _QueryExecutor.QuerySingleAsync<AccountGroupDTO>(loginDTO, sql, param)
                                           .ConfigureAwait(false);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListAccountGroup(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var sql    = AccountGroupQB.GET_SELECTLIST_ACCOUNTGROUP;
                var result = await _QueryExecutor.QueryAsync<AccountGroupPicklistDTO>(loginDTO, sql, null!)
                                                 .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Full-DTO account group list (AccountGroupDTO, not AccountGroupPicklistDTO) — mirrors
        // AccountDAL.GetAccountList exactly: criteria-filtered (search text + status), no paging.
        public async Task<string> GetAccountGroupList(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var sql        = AccountGroupQB.GET_ACCOUNTGROUP_LIST;
                var searchText = ExtractLikeSearchText(criteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(criteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _QueryExecutor.QueryAsync<AccountGroupDTO>(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 — take the first non-empty one found. Mirrors AccountDAL's helper.
        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;
        }

        // Extract an optional Status Equal/NotEqual filter — mirrors AccountDAL's helper.
        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);
        }

        public async Task<string> GetAccountGroupTree(LoginDTO loginDTO, CriteriaDTO criteriaDTO)
        {
            try
            {
                var sql = AccountGroupQB.GET_ACCOUNTGROUP_FLAT_LIST;
                var result = await _QueryExecutor.QueryAsync<AccountGroupPicklistDTO>(loginDTO, sql, null!)
                                           .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> SaveAccountGroup(
            AccountGroupDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.AccountGroupCreatedById  = loginDTO.UserId;
                dto.AccountGroupCreatedOn    = DateTime.UtcNow;
                dto.AccountGroupModifiedById = loginDTO.UserId;
                dto.AccountGroupModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor.ExecuteAsync(loginDTO, AccountGroupQB.SAVE_ACCOUNTGROUP, dto, tx)
                                               .ConfigureAwait(false);
                return rows > 0
                    ? Result<string>.Success("Account group saved successfully.")
                    : Result<string>.Failure("Failed to save account group.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> UpdateAccountGroup(
            AccountGroupDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.AccountGroupModifiedById = loginDTO.UserId;
                dto.AccountGroupModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor.ExecuteAsync(loginDTO, AccountGroupQB.UPDATE_ACCOUNTGROUP, dto, tx)
                                               .ConfigureAwait(false);
                return rows > 0
                    ? Result<string>.Success("Account group updated successfully.")
                    : Result<string>.Failure("Failed to update account group.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> DeleteAccountGroup(
            int accountGroupId, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                var param  = new { AccountGroupId = accountGroupId };
                int result = await _QueryExecutor.ExecuteAsync(loginDTO, AccountGroupQB.DELETE_ACCOUNTGROUP, param, tx)
                                                 .ConfigureAwait(false);
                return result > 0
                    ? Result<string>.Success("Account group deleted successfully.")
                    : Result<string>.Failure("Account group not found or already deleted.");
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}