using System.Data.Common;
using AccountsDAL.DTO.Account;
using AccountsDAL.DTO.BankBranch;
using AccountsDAL.Query.Account;
using AccountsDAL.Query.BankBranch;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Microsoft.Identity.Client;
using Newtonsoft.Json;
using static GB5Shared.DTO.GOP.GBGatewayDTO;

namespace AccountsDAL.CustomCode.BankBranch
{
    public class BankBranchDAL : IBankBranchDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation    _Validation;

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

        public async Task<string> GetBankBranch(int bankBranchId, LoginDTO loginDTO)
        {
            try
            {
                //var dto = await _QueryExecutor
                //    .QuerySingleAsync<BankBranchDTO>(loginDTO, BankBranchQB.GET_BANKBRANCH,
                //        new { BankBranchId = bankBranchId, TenantId = loginDTO.ClientId })

                //    .ConfigureAwait(false);
                //return JsonConvert.SerializeObject(dto);
                var sql = BankBranchQB.GET_BANKBRANCH;
                var parameters = new { BankBranchId = bankBranchId, TenantId = loginDTO.ClientId };
                var dto = await _QueryExecutor.QuerySingleAsync<BankBranchDTO>(loginDTO, sql, parameters)
                                                     .ConfigureAwait(false);
                return JsonConvert.SerializeObject(dto);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> SaveBankBranch(BankBranchDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                dto.BankBranchCreatedById  = loginDTO.UserId;
                dto.BankBranchCreatedOn    = DateTime.UtcNow;
                dto.BankBranchModifiedById = loginDTO.UserId;
                dto.BankBranchModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor
                    .ExecuteAsync(loginDTO, BankBranchQB.SAVE_BANKBRANCH, dto, tx)
                    .ConfigureAwait(false);
                return rows > 0
                    ? Result<string>.Success("Bank branch saved successfully.")
                    : Result<string>.Failure("Failed to save bank branch.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> UpdateBankBranch(BankBranchDTO dto, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            try
            {
                dto.BankBranchModifiedById = loginDTO.UserId;
                dto.BankBranchModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor
                    .ExecuteAsync(loginDTO, BankBranchQB.UPDATE_BANKBRANCH, dto, tx)
                    .ConfigureAwait(false);
                return rows > 0
                    ? Result<string>.Success("Bank branch updated successfully.")
                    : Result<string>.Failure("Failed to update bank branch.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> DeleteBankBranch(int bankBranchId, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                int rows = await _QueryExecutor
                    .ExecuteAsync(loginDTO, BankBranchQB.DELETE_BANKBRANCH,
                        new { BankBranchId = bankBranchId
                        }, tx)
                    .ConfigureAwait(false);
                return rows > 0
                    ? Result<string>.Success("Bank branch deleted successfully.")
                    : Result<string>.Failure("Bank branch not found or already deleted.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListBankBranch(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                //var param = new
                //{
                //    TenantId = loginDTO.ClientId
                //};

                var result = await _QueryExecutor
                    .QueryAsync<BankBranchPicklistDTO>(
                        loginDTO,
                        BankBranchQB.GET_SELECTLIST_BANKBRANCH
                    )
                    .ConfigureAwait(false);

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

        // Full-DTO bank branch list (BankBranchDTO, not BankBranchPicklistDTO) — same criteria
        // filtering shape as AccountDAL.GetAccountList, but no FirstNumber/MaxResult paging.
        public async Task<string> GetBankBranchList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql        = BankBranchQB.GET_BANKBRANCH_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _QueryExecutor.QueryAsync<BankBranchDTO>(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.
        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;
        }

        // Also sends a Status attribute (e.g. NotEqual 5 = exclude Archived) alongside the search
        // text — extract it as (Equals, NotEquals) so both directions are honored; whichever one
        // wasn't sent stays null and its corresponding SQL condition is a no-op.
        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);
        }
    }
}