using AccountsDAL.CustomCode.PartyBranch;
using AccountsDAL.DTO.Party;
using AccountsDAL.Query.Party;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using Newtonsoft.Json;
using System.Data.Common;
using static GB5Shared.GB5Constant.Constant;

namespace AccountsDAL.CustomCode.Party
{
    public class PartyDAL : IPartyDAL
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly IPartyBranchDAL _partyBranchDAL;

        public PartyDAL(IQueryExecutor queryExecutor, IPartyBranchDAL partyBranchDAL)
        {
            _queryExecutor = queryExecutor;
            _partyBranchDAL = partyBranchDAL;
        }

        public async Task<string> GetParty(int partyId, LoginDTO loginDTO)
        {
            var dto = await GetPartyDTO(partyId, loginDTO).ConfigureAwait(false);
            return JsonConvert.SerializeObject(dto);
        }

        public async Task<PartyDTO?> GetPartyDTO(int partyId, LoginDTO loginDTO)
        {
            var dto = await _queryExecutor
                .QuerySingleAsync<PartyDTO>(loginDTO, PartyQB.GET_PARTY, new { PartyId = partyId })
                .ConfigureAwait(false);

            if (dto != null)
            {
                dto.PartyBranchList = await _partyBranchDAL.GetPartyBranchesOnParty(partyId, loginDTO)
                    .ConfigureAwait(false);
            }

            return dto;
        }

        public async Task<string> GetSelectListParty(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            var searchText = ExtractLikeSearchText(criteriaDTO);
            var list = await _queryExecutor
                .QueryAsync<object>(loginDTO, PartyQB.GET_SELECTLIST_PARTY,
                    new { FirstNumber = firstNumber, MaxResult = maxResult, SearchText = searchText })
                .ConfigureAwait(false);
            return JsonConvert.SerializeObject(list);
        }

        // Picklist search-as-you-type sends the typed text as a Like-operation attribute on each
        // searchable field (PartyCode/PartyName) 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;
        }

        public async Task<Result<string>> SaveParty(PartyDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            dto.PartyCreatedById = loginDTO.UserId;
            dto.PartyCreatedOn = DateTime.UtcNow;
            dto.PartyModifiedById = loginDTO.UserId;
            dto.PartyModifiedOn = DateTime.UtcNow;

            int rows = await _queryExecutor.ExecuteAsync(loginDTO, PartyQB.SAVE_PARTY, dto, tx)
                .ConfigureAwait(false);
            return rows > 0
                ? Result<string>.Success("Party saved successfully.")
                : Result<string>.Failure("Failed to save party.");
        }

        public async Task<Result<string>> UpdateParty(PartyDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            dto.PartyModifiedById = loginDTO.UserId;
            dto.PartyModifiedOn = DateTime.UtcNow;

            int rows = await _queryExecutor.ExecuteAsync(loginDTO, PartyQB.UPDATE_PARTY, dto, tx)
                .ConfigureAwait(false);
            return rows > 0
                ? Result<string>.Success("Party updated successfully.")
                : Result<string>.Failure("Failed to update party.");
        }

        public async Task<Result<string>> DeleteParty(int partyId, LoginDTO loginDTO, DbTransaction tx)
        {
            var param = new { PartyId = partyId };

            // Capture branch ids before deleting anything — MPARTYBRANCH.DEFAULTADDRESSID is FK'd to
            // MADDRESS, so once branches are deleted we can no longer look them up to find their addresses.
            var branchIds = (await _queryExecutor
                .QueryAsync<int>(loginDTO, PartyQB.GET_PARTY_BRANCH_IDS, param, tx)
                .ConfigureAwait(false)).ToList();

            await _queryExecutor.ExecuteAsync(loginDTO, PartyQB.DELETE_PARTY_ACCOUNT_OU, param, tx)
                .ConfigureAwait(false);
            await _queryExecutor.ExecuteAsync(loginDTO, PartyQB.DELETE_PARTY_BRANCH_ADDON, param, tx)
                .ConfigureAwait(false);

            // Branch rows (the FK child) must be deleted before their referenced address rows (the FK parent).
            await _queryExecutor.ExecuteAsync(loginDTO, PartyQB.DELETE_PARTY_BRANCHES, param, tx)
                .ConfigureAwait(false);

            if (branchIds.Count > 0)
            {
                await _queryExecutor.ExecuteAsync(loginDTO, PartyQB.DELETE_PARTY_BRANCH_ADDRESS,
                    new { ObjectTypeId = EntityConstant.OBJECTPARTYBRANCH, BranchIds = branchIds }, tx)
                    .ConfigureAwait(false);
            }

            int rows = await _queryExecutor.ExecuteAsync(loginDTO, PartyQB.DELETE_PARTY, param, tx)
                .ConfigureAwait(false);

            return rows > 0
                ? Result<string>.Success("Party deleted successfully.")
                : Result<string>.Failure("Party not found.");
        }
    }
}