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

namespace AccountsDAL.CustomCode.AccountSchedule
{
    public class AccountScheduleDAL : IAccountScheduleDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation    _Validation;

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

        //public async Task<string> GetAccountSchedule(int AccountScheduleId, LoginDTO LoginDTO)
        //{
        //    try
        //    {
        //        var sql        = AccountScheduleQB.GET_ACCOUNTSCHEDULE;
        //        var parameters = new { AccountScheduleid = AccountScheduleId };
        //        var dto        = await _QueryExecutor.QuerySingleAsync<AccountScheduleDTO>(LoginDTO, sql, parameters)
        //                                             .ConfigureAwait(false);
        //        return JsonConvert.SerializeObject(dto);
        //    }
        //    catch (Exception)
        //    {
        //        throw;
        //    }
        //}
        public async Task<string> GetAccountSchedule(int id, LoginDTO loginDTO)
        {
            var sql = AccountScheduleQB.GET_ACCOUNTSCHEDULE;

            var param = new
            {
                AccountScheduleId = id   // ✅ FIXED naming
            };

            var result = await _QueryExecutor.QuerySingleAsync<AccountScheduleDTO>(
                loginDTO,
                sql,
                param
            );

            return JsonConvert.SerializeObject(result);
        }

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

                var result = await _QueryExecutor
                    .QueryAsync<AccountSchedulePicklistDTO>(
                        LoginDTO,
                        AccountScheduleQB.GET_SELECTLIST_ACCOUNTSCHEDULE,
                        param
                    )
                    .ConfigureAwait(false);
                                             
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Full-DTO account schedule list (AccountScheduleDTO, not AccountSchedulePicklistDTO) —
        // mirrors AccountDAL.GetAccountList / AccountGroupDAL.GetAccountGroupList exactly:
        // criteria-filtered (search text + status), no paging.
        public async Task<string> GetAccountScheduleList(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var sql        = AccountScheduleQB.GET_ACCOUNTSCHEDULE_LIST;
                var searchText = ExtractLikeSearchText(criteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(criteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _QueryExecutor.QueryAsync<AccountScheduleDTO>(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<Result<string>> SaveAccountSchedule(
            AccountScheduleDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.AccountScheduleCreatedById = loginDTO.UserId;
                dto.AccountScheduleCreatedOn = DateTime.UtcNow;
                dto.AccountScheduleModifiedById = loginDTO.UserId;
                dto.AccountScheduleModifiedOn = DateTime.UtcNow;

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

        public async Task<Result<string>> UpdateAccountSchedule(
            AccountScheduleDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.AccountScheduleModifiedById = loginDTO.UserId;
                dto.AccountScheduleModifiedOn   = DateTime.UtcNow;

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

        public async Task<Result<string>> DeleteAccountSchedule(
            int accountScheduleId, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                var param  = new { AccountScheduleId = accountScheduleId };
                int result = await _QueryExecutor.ExecuteAsync(loginDTO, AccountScheduleQB.DELETE_ACCOUNTSCHEDULE, param, tx)
                                                 .ConfigureAwait(false);
                return result > 0
                    ? Result<string>.Success("Account schedule deleted successfully.")
                    : Result<string>.Failure("Account schedule not found or already deleted.");
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}