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

namespace AccountsDAL.CustomCode.Account
{
    public class AccountDAL : IAccountDAL
    {



        private readonly IQueryExecutor       _queryExecutor;
        private readonly Dapr.Client.DaprClient _daprClient;

        public AccountDAL(IQueryExecutor queryExecutor, Dapr.Client.DaprClient daprClient)
        {
            _queryExecutor = queryExecutor;
            _daprClient    = daprClient;
        }

        // ── Existing read methods ─────────────────────────────────────────────

        public async Task<string> GetAccount(int AccountId, LoginDTO LoginDTO)
        {
            try
            {
                var sql        = AccountQB.GET_ACCOUNT_ID;
                var parameters = new { accountid = AccountId };
                var dto        = await _queryExecutor.QuerySingleAsync<AccountDTO>(LoginDTO, sql, parameters)
                                                     .ConfigureAwait(false);
                return JsonConvert.SerializeObject(dto);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListAccount(
            int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            try
            {
                if (IsCount) return "0";

                var sql        = AccountQB.Get_Select_List_Account;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    firstnumber = FirstNumber,
                    maxresult = MaxResult,
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list      = (await _queryExecutor.QueryAsync<AccountPicklistDTO>(LoginDTO, sql, parameter)
                                                     .ConfigureAwait(false)).ToList();
                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // Full-DTO list (AccountDTO, not AccountPicklistDTO) — same criteria filtering as
        // GetSelectListAccount above, but no FirstNumber/MaxResult paging, mirroring
        // ShowScheduleBLL/DAL.GetShowScheduleList's shape (a plain CriteriaDTO-filtered List call).
        public async Task<string> GetAccountList(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                var sql        = AccountQB.GET_ACCOUNT_LIST;
                var searchText = ExtractLikeSearchText(CriteriaDTO);
                var (statusEquals, statusNotEquals) = ExtractStatusFilter(CriteriaDTO);
                var parameter = new
                {
                    SearchText = searchText,
                    StatusEquals = statusEquals,
                    StatusNotEquals = statusNotEquals
                };
                var list = (await _queryExecutor.QueryAsync<AccountDTO>(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 (AccountCode/AccountName) 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;
        }

        // 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.
        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> GetAccountParameter(int AccountId, LoginDTO LoginDTO)
        {
            try
            {
                var sql    = AccountQB.GET_ACCOUNT_PARAMETER;
                var param  = new { accountid = AccountId };
                var result = await _queryExecutor.QueryAsync<AccountParameterDTO>(LoginDTO, sql, param)
                                                 .ConfigureAwait(false);
                return JsonConvert.SerializeObject(result);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Usage check ───────────────────────────────────────────────────────

        //public async Task<bool> AccountExistsInVouchers(int accountId, LoginDTO loginDTO)
        //{
        //    try
        //    {
        //        var sql    = AccountQB.CHECK_ACCOUNT_IN_VOUCHERS;
        //        var param  = new { accountid = accountId };
        //        int result = await _queryExecutor.ExecuteScalarAsync<int>(loginDTO, sql, param)
        //                                         .ConfigureAwait(false);
        //        return result == 1;
        //    }
        //    catch (Exception)
        //    {
        //        throw;
        //    }
        //}

        // ── Transactional write methods ───────────────────────────────────────

        public async Task<Result<string>> SaveAccount(AccountDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.AccountCreatedById  = loginDTO.UserId;
                dto.AccountCreatedOn    = DateTime.UtcNow;
                dto.AccountModifiedById = loginDTO.UserId;
                dto.AccountModifiedOn   = DateTime.UtcNow;

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

        public async Task<Result<string>> UpdateAccount(AccountDTO dto, LoginDTO loginDTO, DbTransaction tx)
        {
            try
            {
                dto.AccountModifiedById = loginDTO.UserId;
                dto.AccountModifiedOn   = DateTime.UtcNow;

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

        //public async Task<Result<string>> DeleteAccount(int accountId, LoginDTO loginDTO, DbTransaction tx)
        //{
        //    try
        //    {
        //        var param  = new { AccountId = accountId };
        //        int result = await _queryExecutor.ExecuteAsync(loginDTO, AccountQB.DELETE_ACCOUNT, param, tx)
        //                                         .ConfigureAwait(false);
        //        //return result > 0
        //        //    ? Result<string>.Success("Account deleted successfully.")
        //        //    : Result<string>.Failure("Account not found or already deleted.");
        //        if (result <= 0)
        //            return Result<string>.Failure("Account not found.");

        //        return Result<string>.Success("Account deleted successfully.");
        //    }
        //    catch (Exception)
        //    {
        //        throw;
        //    }
        //}
        //public async Task<Result<string>> DeleteAccount(
        //        int accountId,
        //        LoginDTO loginDTO,
        //        DbTransaction tx)
        //{
        //    try
        //    {
        //        var param = new { AccountId = accountId };

        //        // Check voucher usage
        //        int voucherCount = await _queryExecutor.ExecuteScalarAsync<int>(
        //            loginDTO,
        //            @"SELECT COUNT(VOUCHERACCOUNTID)
        //      FROM TVOUCHERDETAIL
        //      WHERE VOUCHERACCOUNTID = @AccountId",
        //            param,
        //            tx);

        //        if (voucherCount > 0)
        //        {
        //            return Result<string>.Failure(
        //                "Account is used in vouchers and cannot be deleted.");
        //        }

        //        // Delete balance
        //        await _queryExecutor.ExecuteAsync(
        //            loginDTO,
        //            @"DELETE FROM MACCOUNTBALANCE
        //      WHERE ACCOUNTID = @AccountId",
        //            param,
        //            tx);

        //        // Delete account post
        //        await _queryExecutor.ExecuteAsync(
        //            loginDTO,
        //            @"DELETE FROM TACCOUNTPOST
        //      WHERE ACCOUNTID = @AccountId",
        //            param,
        //            tx);

        //        // Delete account
        //        int result = await _queryExecutor.ExecuteAsync(
        //            loginDTO,
        //            @"DELETE FROM dbo.MACCOUNT
        //      WHERE ACCOUNTID = @AccountId",
        //            param,
        //            tx);

        //        return result > 0
        //            ? Result<string>.Success("Account deleted successfully.")
        //            : Result<string>.Failure("Account not found.");
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //}

        // ── Additional read queries ───────────────────────────────────────────

        public async Task<string> GetAccountDetailsSQL(int accountId, LoginDTO loginDTO)
        {
            try
            {
                var sql   = AccountQB.GET_ACCOUNT_DETAILS_SQL;
                var param = new { AccountId = accountId };
                var dto   = await _queryExecutor.QuerySingleAsync<AccountCustomDTO>(loginDTO, sql, param)
                                                .ConfigureAwait(false);
                return JsonConvert.SerializeObject(dto);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetAccountPicklistForAccountPopup(CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                var sql   = AccountQB.GET_ACCOUNT_PICKLIST_FOR_POPUP;
                var param = new { SearchKeyword = criteriaDTO?.SearchKeyword ?? "" };
                var list  = await _queryExecutor.QueryAsync<AccountPicklistDTO>(loginDTO, sql, param)
                                                .ConfigureAwait(false);
                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListAccountVsCompany(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                int OUId = -1;
                int Status = -1;
                string AccountsCode = null;
                string AccountsName = null;

                if (criteriaDTO?.SectionCriteriaList != null)
                {
                    foreach (var section in criteriaDTO.SectionCriteriaList)
                    {
                        foreach (var attribute in section.AttributesCriteriaList ?? new())
                        {
                            var value = NormalizeCriteriaValue(attribute.FieldValue);
                            switch (attribute.FieldName?.ToLower())
                            {
                                case "ouid":
                                    if (value != null) OUId = Convert.ToInt32(value);
                                    break;
                                case "status":
                                    if (value != null) Status = Convert.ToInt32(value);
                                    break;
                                case "accountscode":
                                    AccountsCode = value?.ToString();
                                    break;
                                case "accountsname":
                                    AccountsName = value?.ToString();
                                    break;
                            }
                        }
                    }
                }

                if (OUId == -1)
                {
                    OUId = loginDTO.WorkOUId;
                }

                var sql   = AccountQB.GET_ACCOUNT_VS_COMPANY_SELECTLIST;
                var param = new
                {
                    firstnumber = firstNumber,
                    maxresult = maxResult,
                    ouid = OUId,
                    status = Status,
                    accountscode = string.IsNullOrWhiteSpace(AccountsCode) ? null : AccountsCode,
                    accountsname = string.IsNullOrWhiteSpace(AccountsName) ? null : AccountsName
                };
                var list  = (await _queryExecutor.QueryAsync<AccountPicklistDTO>(loginDTO, sql, param)
                                                 .ConfigureAwait(false)).ToList();
                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // CriteriaDTO.FieldValue deserializes as a boxed System.Text.Json.JsonElement — unwrap it
        // to a native CLR value before using it, otherwise Convert.ToInt32 fails with
        // "Unable to cast object of type 'JsonElement' to type 'IConvertible'".
        private static object NormalizeCriteriaValue(object value)
        {
            if (value is not System.Text.Json.JsonElement je)
                return value;

            return je.ValueKind switch
            {
                System.Text.Json.JsonValueKind.String => je.GetString(),
                System.Text.Json.JsonValueKind.Number => je.TryGetInt32(out var i) ? i : je.GetDouble(),
                System.Text.Json.JsonValueKind.True => true,
                System.Text.Json.JsonValueKind.False => false,
                System.Text.Json.JsonValueKind.Null => null,
                _ => je.ToString()
            };
        }

        public async Task<string> GetPopUpDetails(int accountId, LoginDTO loginDTO)
        {
            try
            {
                var sql   = AccountQB.GET_POPUP_DETAILS;
                var param = new { AccountId = accountId };
                var dto   = await _queryExecutor.QuerySingleAsync<AccountPopUpDTO>(loginDTO, sql, param)
                                                .ConfigureAwait(false);
                return JsonConvert.SerializeObject(dto);
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetControlAccountPicklist(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Id = "";
                string Code = "";
                string Name = "";

                if (CriteriaDTO != null && CriteriaDTO.SectionCriteriaList?.Count > 0)
                {
                    var attributes = CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList;

                    if (attributes != null)
                    {
                        foreach (var item in attributes)
                        {
                            if (item.FieldName.ToLower() == "id")
                            {
                                Id = item.FieldValue?.ToString();
                            }
                            else if (item.FieldName.ToLower() == "code")
                            {
                                Code = item.FieldValue?.ToString();
                            }
                            else if (item.FieldName.ToLower() == "name")
                            {
                                Name = item.FieldValue?.ToString();
                            }
                        }
                    }
                }
                string SQL = AccountQB.GET_SELECTLIST_CONTROLACCOUNT;
                var parameter = new { Id, Code, Name };
                var Result = await _queryExecutor.QueryAsync<AccountPicklistDTO>(LoginDTO, SQL, parameter);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<string> GetSelectListGeneralLedger(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string Id = "";
                string Code = "";
                string Name = "";

                if (CriteriaDTO != null && CriteriaDTO.SectionCriteriaList?.Count > 0)
                {
                    var attributes = CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList;

                    if (attributes != null)
                    {
                        foreach (var item in attributes)
                        {
                            if (item.FieldName.ToLower() == "id")
                            {
                                Id = item.FieldValue?.ToString();
                            }
                            else if (item.FieldName.ToLower() == "code")
                            {
                                Code = item.FieldValue?.ToString();
                            }
                            else if (item.FieldName.ToLower() == "name")
                            {
                                Name = item.FieldValue?.ToString();
                            }
                        }
                    }
                }
                string SQL = AccountQB.GET_SELECTLIST_GENERALLEDGER;
                var parameter = new { Id, Code, Name };
                var Result = await _queryExecutor.QueryAsync<GeneralLedgerPicklistDTO>(LoginDTO, SQL, parameter);
                string Json = JsonConvert.SerializeObject(Result);
                return Json;
            }
            catch (Exception)
            {
                throw;
            }
        }
        public async Task<string> GetAccountSelectlistFromQueryParametarized(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            try
            {
                int isvendor = 1, iscustomer = 1, onlyparty = 1;
                int pluspartybutton = 1;
                string OUId = "";
                string accountcode = "", accountname = "";

                if (criteriaDTO != null && criteriaDTO.SectionCriteriaList?.Count > 0)
                {
                    foreach (var section in criteriaDTO.SectionCriteriaList)
                    {
                        if (section?.AttributesCriteriaList == null) continue;
                        
                        foreach (var attr in section.AttributesCriteriaList)
                        {
                            var fieldName = attr.FieldName?.ToLower();
                            if (fieldName == "isvendor")
                            {
                                if (int.TryParse(attr.FieldValue?.ToString(), out var val))
                                    isvendor = val;
                            }
                            else if (fieldName == "iscustomer")
                            {
                                if (int.TryParse(attr.FieldValue?.ToString(), out var val))
                                    iscustomer = val;
                            }
                            else if (fieldName == "onlyparty")
                            {
                                if (int.TryParse(attr.FieldValue?.ToString(), out var val))
                                    onlyparty = val;
                            }
                            else if (fieldName == "pluspartybutton")
                            {
                                if (int.TryParse(attr.FieldValue?.ToString(), out var val))
                                    pluspartybutton = val;
                            }
                            else if (fieldName == "accountscode")
                            {
                                accountcode = attr.FieldValue?.ToString() ?? "";
                            }
                            else if (fieldName == "accountsname")
                            {
                                accountname = attr.FieldValue?.ToString() ?? "";
                            }
                            else if (fieldName == "ouid")
                            {
                                var val = attr.FieldValue?.ToString();
                                if (val == null)
                                    throw new ArgumentException("OUId is passing null. Please refresh the form.");
                                OUId = val;
                            }
                        }
                    }
                }

                if (pluspartybutton == 0)
                {
                    return await GetPartyListFromSQLParametarized(firstNumber, maxResult, criteriaDTO, loginDTO);
                }

                int skip = firstNumber;
                int take = maxResult;

                int? ouidParam = null;
                if (OUId != null && OUId != "" && OUId != "0")
                {
                    if (int.TryParse(OUId, out var ouidVal))
                        ouidParam = ouidVal;
                }

                var param = new
                {
                    skip,
                    take,
                    ouid = ouidParam,
                    isvendor = isvendor,
                    iscustomer = iscustomer,
                    onlyparty = onlyparty,
                    accountcode = string.IsNullOrEmpty(accountcode) ? (string)null : accountcode,
                    accountname = string.IsNullOrEmpty(accountname) ? (string)null : accountname
                };

                var list = (await _queryExecutor.QueryAsync<AccountPicklistDTO>(loginDTO, AccountQB.GET_ACCOUNT_PICKLIST_FOR_COMPANY_VS_ACCOUNT_NEW, param)
                                              .ConfigureAwait(false)).ToList();
                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<string> GetPartyListFromSQLParametarized(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            string accountcode = "", accountname = "", routeids = "";
            int OUId = 0;

            if (criteriaDTO != null && criteriaDTO.SectionCriteriaList?.Count > 0)
            {
                foreach (var section in criteriaDTO.SectionCriteriaList)
                {
                    if (section?.AttributesCriteriaList == null) continue;
                    
                    foreach (var attr in section.AttributesCriteriaList)
                    {
                        var fieldName = attr.FieldName?.ToLower();
                        if (fieldName == "accountscode")
                        {
                            accountcode = attr.FieldValue?.ToString() ?? "";
                        }
                        else if (fieldName == "accountsname")
                        {
                            accountname = attr.FieldValue?.ToString() ?? "";
                        }
                        else if (fieldName == "ouid")
                        {
                            if (int.TryParse(attr.FieldValue?.ToString(), out var val))
                                OUId = val;
                        }
                        else if (fieldName == "routeids")
                        {
                            routeids = attr.FieldValue?.ToString() ?? "";
                        }
                    }
                }
            }

            int skip = firstNumber;
            int take = maxResult;

            var param = new
            {
                skip,
                take,
                ouid = OUId == 0 ? (int?)null : OUId,
                accountcode,
                accountname
            };

            var list = (await _queryExecutor.QueryAsync<AccountPicklistDTO>(loginDTO, AccountQB.GET_PARTY_FROM_BUTTON_FOR_ACCOUNT_PICKLIST, param)
                                              .ConfigureAwait(false)).ToList();
            return JsonConvert.SerializeObject(list);
        }

        public async Task<bool> AccountExistsInVouchers(
        int accountId,
        LoginDTO loginDTO)
        {
            var param = new
            {
                AccountId = accountId
            };

            int result = await _queryExecutor
                .ExecuteScalarAsync<int>(
                    loginDTO,
                    AccountQB.CHECK_ACCOUNT_IN_VOUCHERS,
                    param)
                .ConfigureAwait(false);

            return result > 0;
        }

        public async Task<bool> AccountLinkedToParty(int accountId, LoginDTO loginDTO)
        {
            int result = await _queryExecutor
                .ExecuteScalarAsync<int>(
                    loginDTO,
                    AccountQB.CHECK_ACCOUNT_LINKED_TO_PARTY,
                    new { AccountId = accountId })
                .ConfigureAwait(false);

            return result > 0;
        }

        public async Task<bool> AccountExists(int accountId, LoginDTO loginDTO, DbTransaction tx)
        {
            int result = await _queryExecutor
                .ExecuteScalarAsync<int>(
                    loginDTO,
                    AccountQB.CHECK_ACCOUNT_EXISTS,
                    new { AccountId = accountId },
                    tx)
                .ConfigureAwait(false);

            return result > 0;
        }

        public async Task<Result<string>> DeleteAccount(
            int accountId,
            LoginDTO loginDTO,
            DbTransaction tx)
        {
            var param = new
            {
                AccountId = accountId
            };

            await _queryExecutor.ExecuteAsync(
                loginDTO,
                AccountQB.DELETE_ACCOUNT_BALANCE,
                param,
                tx);

            await _queryExecutor.ExecuteAsync(
                loginDTO,
                AccountQB.DELETE_ACCOUNT_POST,
                param,
                tx);
            await _queryExecutor.ExecuteAsync(
               loginDTO,
               AccountQB.DELETE_PARTY_ACCOUNT_OU,
               param,
               tx);

            int result = await _queryExecutor.ExecuteAsync(
                loginDTO,
                AccountQB.DELETE_ACCOUNT,
                param,
                tx);
           
            return result > 0
                ? Result<string>.Success("Account deleted successfully.")
                : Result<string>.Failure("Account not found.");
        }
    }
}