using System.Data.Common;
using AccountsDAL.DTO.CostCenterPattern;
using AccountsDAL.DTO.CostCenterPatternDetail;
using AccountsDAL.Query.CostCenterPattern;
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.CostCenterPattern
{
    public class CostCenterPatternDAL : ICostCenterPatternDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation    _Validation;

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

        public async Task<string> GetCostCenterPattern(int costCenterPatternId, LoginDTO loginDTO)
        {
            try
            {
                var dto = await _QueryExecutor
                    .QuerySingleAsync<CostCenterPatternDTO>(loginDTO,
                        CostCenterPatternQB.GET_COSTCENTERPATTERN,
                        new { CostCenterPatternId = costCenterPatternId,
                              TenantId = loginDTO.ClientId
                        })
                    .ConfigureAwait(false);

                var details = await _QueryExecutor
                    .QueryAsync<CostCenterPatternDetailDTO>(loginDTO,
                        CostCenterPatternQB.GET_COSTCENTERPATTERN_DETAILS,
                        new { CostCenterPatternId = costCenterPatternId,
                               TenantId = loginDTO.ClientId
                        })
                    .ConfigureAwait(false);

                if (dto != null)
                    dto.CostCenterPatternDetailArray = details?.ToList() ?? [];

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

        public async Task<Result<string>> SaveCostCenterPattern(
            CostCenterPatternDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.CostCenterPatternCreatedById  = loginDTO.UserId;
                dto.CostCenterPatternCreatedOn    = DateTime.UtcNow;
                dto.CostCenterPatternModifiedById = loginDTO.UserId;
                dto.CostCenterPatternModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor
                    .ExecuteAsync(loginDTO, CostCenterPatternQB.SAVE_COSTCENTERPATTERN, dto, tx)
                    .ConfigureAwait(false);
                return rows > 0
                    ? Result<string>.Success("Cost center pattern saved successfully.")
                    : Result<string>.Failure("Failed to save cost center pattern.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> UpdateCostCenterPattern(
            CostCenterPatternDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.CostCenterPatternModifiedById = loginDTO.UserId;
                dto.CostCenterPatternModifiedOn   = DateTime.UtcNow;

                int rows = await _QueryExecutor
                    .ExecuteAsync(loginDTO, CostCenterPatternQB.UPDATE_COSTCENTERPATTERN, dto, tx)
                    .ConfigureAwait(false);
                return rows > 0
                    ? Result<string>.Success("Cost center pattern updated successfully.")
                    : Result<string>.Failure("Failed to update cost center pattern.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> SaveCostCenterPatternDetail(
            CostCenterPatternDetailDTO detail, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                int rows = await _QueryExecutor
                    .ExecuteAsync(loginDTO, CostCenterPatternQB.SAVE_COSTCENTERPATTERN_DETAIL, detail, tx)
                    .ConfigureAwait(false);
                return rows > 0
                    ? Result<string>.Success("Cost center pattern detail saved.")
                    : Result<string>.Failure("Failed to save cost center pattern detail.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> DeleteCostCenterPatternDetails(
            int costCenterPatternId, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                await _QueryExecutor
                    .ExecuteAsync(loginDTO, CostCenterPatternQB.DELETE_COSTCENTERPATTERN_DETAILS,
                        new { CostCenterPatternId = costCenterPatternId,
                            TenantId = loginDTO.ClientId
                        }, tx)
                    .ConfigureAwait(false);
                return Result<string>.Success("Cost center pattern details deleted.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<Result<string>> DeleteCostCenterPattern(
            int costCenterPatternId, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                int rows = await _QueryExecutor
                    .ExecuteAsync(loginDTO, CostCenterPatternQB.DELETE_COSTCENTERPATTERN,
                        new { CostCenterPatternId = costCenterPatternId ,
                            TenantId = loginDTO.ClientId
                        }, tx)
                    .ConfigureAwait(false);
                return rows > 0
                    ? Result<string>.Success("Cost center pattern deleted successfully.")
                    : Result<string>.Failure("Cost center pattern not found or already deleted.");
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListCostCenterPattern(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var result = await _QueryExecutor
                .QueryAsync<CostCenterPatternPicklistDTO>(
                    loginDTO,
                    CostCenterPatternQB.GET_SELECTLIST_COSTCENTERPATTERN,
                    new
                    {
                        TenantId = loginDTO.ClientId
                    })
                .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Full-DTO cost center pattern list (CostCenterPatternDTO, not CostCenterPatternPicklistDTO) —
        // same criteria filtering as GetSelectListCostCenterPattern above, but no FirstNumber/MaxResult
        // paging, mirroring AccountDAL.GetAccountList's shape (a plain CriteriaDTO-filtered List call).
        public async Task<string> GetCostCenterPatternList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql        = CostCenterPatternQB.GET_COSTCENTERPATTERN_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    TenantId = LoginDTO.ClientId,
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _QueryExecutor.QueryAsync<CostCenterPatternDTO>(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;
        }

        // Picklist 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. 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);
        }
    }
}