using AccountsDAL.DTO.AccountsReports;
using AccountsDAL.Query.AccountReports;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AccountsDAL.CustomCode.AccountReports.AccountLedger
{
    public class AccountLedgerSummaryDAL : IAccountLedgerSummaryDAL
    {
        private readonly IQueryExecutor _queryExecutor;

        public AccountLedgerSummaryDAL(IQueryExecutor queryExecutor)
        {
            _queryExecutor = queryExecutor;
        }

        public async Task<AccountLedgerSummaryResultDTO> GetAccountLedgerSummaryReport(
            int firstNumber, int maxResult, AccountReportCriteria criteria,
            List<AccountReportSortOptionDTO> sortBy, LoginDTO loginDTO)
        {
            var (filter, parameters) = AccountReportFilterBuilder.Build(criteria, loginDTO);
            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);
            parameters.Add("Offset", Math.Max(0, firstNumber - 1));
            parameters.Add("PageSize", maxResult);

            // Same alias-remap as AccountLedgerReportDAL — Build()'s fragment hardcodes V/VD/ACC/BTT,
            // but the opening-balance subquery self-joins under V2/VD2/ACC2/BTT2. Reusing the fragment
            // verbatim here correlates it to the outer (grouped) row instead of the subquery's own
            // alias, which SQL Server rejects as an invalid select-list column outside GROUP BY.
            var openingFilter = filter
                .Replace("VD.", "VD2.")
                .Replace("ACC.", "ACC2.")
                .Replace("BTT.", "BTT2.")
                .Replace("V.", "V2.");

            var orderBy = AccountReportSortBuilder.BuildLedgerSummaryOrderBy(sortBy);
            var sql = AccountLedgerReportsQB.GET_ACCOUNT_LEDGER_SUMMARY
                .Replace("{DynamicFilter}", filter)
                .Replace("{OpeningDynamicFilter}", openingFilter)
                .Replace("{OrderBy}", orderBy);
            var paged = await _queryExecutor
                .QueryPagedAsync<AccountLedgerSummaryDTO>(loginDTO, sql, parameters)
                .ConfigureAwait(false);

            var totalsSql = AccountLedgerReportsQB.GET_ACCOUNT_LEDGER_SUMMARY_TOTALS.Replace("{DynamicFilter}", filter);
            var totals = await _queryExecutor
                .QuerySingleAsync<AccountRegisterTotalsDTO>(loginDTO, totalsSql, parameters)
                .ConfigureAwait(false);

            return new AccountLedgerSummaryResultDTO
            {
                Items = paged.Items ?? Enumerable.Empty<AccountLedgerSummaryDTO>(),
                TotalCount = paged.TotalCount,
                TotalDebit = totals?.TotalDebit ?? 0,
                TotalCredit = totals?.TotalCredit ?? 0
            };
        }
    }
}