using LCU.Library.Internal.CoreApiWrapperLib.CoreDbUtilities;
using LCU.Library.Internal.CoreApiWrapperLib.Utilities;
using LCU.Library.Internal.CoreApiWrapperModel.CoreModels.Agreements;
using LCU.Library.Internal.CoreApiWrapperModel.CoreModels.MemberAgreements;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace LCU.Library.Internal.CoreApiWrapperLib.DataAccess
{
    public class AgreeDao: IAgreeDao
    {
        private readonly ICoreDbExecution _coreDbExecution;
        private const string AgreeUserByAccountsQuery = "AgreeUserByAccountsQuery";
        private const string AgreeUserByPersonNumberQuery = "AgreeUserByPersonNumberQuery";
        private const string AgreeUserByOrganizationNumberQuery = "AgreeUserByOrganizationNumberQuery";

        private const string AgreementByUserIdQuery = "Agreement/AgreementByUserIdQuery";
        private const string AgreementByAgreementIdQuery = "Agreement/AgreementByAgreementIdQuery";
        private const string AgreementsByAccountIdQuery = "Agreement/AgreementsByAccountIdQuery";

        private const string AccountByAccountIdQuery = "Agreement/AccountByAccountIdQuery";
        private const string AccountsByAgreementUserIdQuery = "Agreement/AccountsByAgreementUserIdQuery";
        private const string AccountsByOrganizationIdQuery = "Agreement/AccountsByOrganizationIdQuery";
        private const string AgreementOwnerByAccountsQuery = "Agreement/AgreementOwnerByAccountsQuery";

        private const string AuditActiveSubActiveForAccountChangesQuery = "Agreement/Audit/AuditActiveSubActiveForAccountChangesQuery";
        private const string AuditActiveSubActiveForAgreementChangesQuery = "Agreement/Audit/AuditActiveSubActiveForAgreementChangesQuery";
        private const string AuditActiveSubActiveForOrganizationChangesQuery = "Agreement/Audit/AuditActiveSubActiveForOrganizationChangesQuery";

        private const string AccountNumbersHolder = "{AccountNumbersHolder}";
        private const string DnaTimeFormat = "yyyy-MM-dd HH:mm:ss";

        public AgreeDao(ICoreDbExecution coreDbExecution)
        {
            _coreDbExecution = coreDbExecution;
        }

        public async Task<IEnumerable<AgreeUserAccount>> FindAgreeUsersByAccountNumbers(List<string> accountNumbers)
        {
            var query = QueryUtilities.GetQuery(AgreeUserByAccountsQuery);
            var accountNumbersStringList = QueryUtilities.BuildParameterListString(accountNumbers);
            var alteredQuery = query.Replace(AccountNumbersHolder, accountNumbersStringList);

            var parameterDictionary = QueryUtilities.BuildDictionaryParameter(accountNumbers);
            var result = await _coreDbExecution.ExecuteQuery<AgreeUserAccount, Dictionary<string, object>>(alteredQuery, parameterDictionary);
            return result;
        }
        
        public async Task<IEnumerable<string>> FindAgreeUserByPersonNumber(long personNumber)
        {
            var query = QueryUtilities.GetQuery(AgreeUserByPersonNumberQuery);
            var personNumberParam = new { personNumber };
            var agreementUser = await _coreDbExecution.ExecuteQuery<string, object>(query, personNumberParam);
            return agreementUser;
        }
        
        public async Task<string> FindAgreeUserByOrganizationNumber(long organizationNumber)
        {
            var query = QueryUtilities.GetQuery(AgreeUserByOrganizationNumberQuery);
            var organizationNumberParam = new { organizationNumber };
            var agreementUser = await _coreDbExecution.ExecuteQuery<string, object>(query, organizationNumberParam);
            return agreementUser.FirstOrDefault();
        }
        
        public async Task<Agreement> FindAgreementByUserId(string userId)
        {
            var query = QueryUtilities.GetQuery(AgreementByUserIdQuery);
            var userIdParam = new { userId };
            var agreement = await _coreDbExecution.ExecuteQuery<Agreement, object>(query, userIdParam);
            return agreement.FirstOrDefault();
        }
        
        public async Task<Agreement> FindAgreementByAgreementId(long agreementId)
        {
            var query = QueryUtilities.GetQuery(AgreementByAgreementIdQuery);
            var agreementIdParam = new { agreementId };
            var agreement = await _coreDbExecution.ExecuteQuery<Agreement, object>(query, agreementIdParam);
            return agreement.FirstOrDefault();
        }
        
        public async Task<IEnumerable<Agreement>> FindAgreementsByAccountId(long accountNumber)
        {
            var query = QueryUtilities.GetQuery(AgreementsByAccountIdQuery);
            var accountNumberParams = new { accountNumber };
            return await _coreDbExecution.ExecuteQuery<Agreement, object>(query, accountNumberParams);
        }
        
        public async Task<IEnumerable<AgreementAccount>> FindAgreementAccountsByUserId(string userId)
        {
            var query = QueryUtilities.GetQuery(AccountsByAgreementUserIdQuery);
            var userIdParams = new { userId };
            return await _coreDbExecution.ExecuteQuery<AgreementAccount, object>(query, userIdParams);
        }

        public async Task<IEnumerable<AgreementAccount>> FindAgreementAccountsByOrganizationId(long organizationId)
        {
            var query = QueryUtilities.GetQuery(AccountsByOrganizationIdQuery);
            var organizationIdParams = new { organizationId };
            return await _coreDbExecution.ExecuteQuery<AgreementAccount, object>(query, organizationIdParams);
        }
        
        public async Task<AgreementAccount> FindAgreementAccountByAccountId(long accountNumber)
        {
            var query = QueryUtilities.GetQuery(AccountByAccountIdQuery);
            var accountNumberParams = new { accountNumber };
            var account = await _coreDbExecution.ExecuteQuery<AgreementAccount, object>(query, accountNumberParams);
            return account.FirstOrDefault();
        }
        
        public async Task<IEnumerable<AgreementAccountOwner>> FindAgreementOwnerByAccountNumbers(IEnumerable<long> accountNumbers)
        {
            var query = QueryUtilities.GetQuery(AgreementOwnerByAccountsQuery);
            var accountNumbersStringList = string.Join(",", accountNumbers);
            var alteredQuery = query.Replace(AccountNumbersHolder, accountNumbersStringList);            
            return await _coreDbExecution.ExecuteQuery<AgreementAccountOwner, object>(alteredQuery, null);
        }
        
        public async Task<AgreementAccountOwner> FindAgreementOwnerByAccountNumber(long accountNumber)
        {
            var query = QueryUtilities.GetQuery(AgreementOwnerByAccountsQuery);
            var alteredQuery = query.Replace(AccountNumbersHolder, accountNumber.ToString());            
            var accountOwner = await _coreDbExecution.ExecuteQuery<AgreementAccountOwner, object>(alteredQuery, null);
            return accountOwner.FirstOrDefault();
        }
        
        public async Task<IEnumerable<long>> AuditAccountDeliveryMethodAndOwnerChangesByDateLastMaintained(DateTime maintenanceDate)
        {
            var query = QueryUtilities.GetQuery(AuditActiveSubActiveForAccountChangesQuery);
            var dateLastMaintained = maintenanceDate.ToString(DnaTimeFormat);
            var maintenanceDateParam = new { dateLastMaintained };
            return await _coreDbExecution.ExecuteQuery<long, object>(query, maintenanceDateParam);
        }
        
        public async Task<IEnumerable<long>> AuditAccountAgreementChangesByDateLastMaintained(DateTime maintenanceDate)
        {
            var query = QueryUtilities.GetQuery(AuditActiveSubActiveForAgreementChangesQuery);
            var dateLastMaintained = maintenanceDate.ToString(DnaTimeFormat);
            var maintenanceDateParam = new { dateLastMaintained };
            return await _coreDbExecution.ExecuteQuery<long, object>(query, maintenanceDateParam);
        }
        
        public async Task<IEnumerable<long>> AuditAccountOrganizationChangesByDateLastMaintained(DateTime maintenanceDate)
        {
            var query = QueryUtilities.GetQuery(AuditActiveSubActiveForOrganizationChangesQuery);
            var dateLastMaintained = maintenanceDate.ToString(DnaTimeFormat);
            var maintenanceDateParam = new { dateLastMaintained };
            return await _coreDbExecution.ExecuteQuery<long, object>(query, maintenanceDateParam);
        }

    }
}