using System.Runtime.CompilerServices;
using AccountsDAL.DTO.AccountsReports;
using AccountsDAL.Query.AccountReports;
using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;

namespace AccountsDAL.CustomCode.AccountReports.AccountLedger
{
    public class AccountLedgerReportDAL : IAccountLedgerReportDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        // AccountReportFilterBuilder.Build()'s fragment hardcodes aliases V/VD/ACC/BTT. The opening-
        // balance OUTER APPLY/subquery uses V2/VD2/ACC2/BTT2 for its own self-join — remapping longer
        // aliases first (VD before V) so "V." substitution never clobbers an already-remapped "VD2.".
        private static string RemapAliasesForOpeningBalance(string filter) =>
            filter
                .Replace("VD.", "VD2.")
                .Replace("ACC.", "ACC2.")
                .Replace("BTT.", "BTT2.")
                .Replace("V.", "V2.");

        private static (string filter, string openingFilter, DynamicParameters parameters) BuildFilterAndParams(
            AccountReportCriteria criteria, LoginDTO loginDTO)
        {
            var (sharedFilter, parameters) = AccountReportFilterBuilder.Build(criteria, loginDTO);
            var (detailFilter, detailParameters) = AccountReportFilterBuilder.BuildDetailOnly(criteria);
            parameters.AddDynamicParams(detailParameters);

            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);
            parameters.Add("ReportCurrencyId", criteria.ReportCurrencyId);

            // Opening balance only ever scopes by account/OU-group/biztype/costcenter (Build()'s
            // fragment) — BuildDetailOnly()'s BillType/dimension filters are per-voucher narrative
            // filters that don't belong in an "opening balance as of a date" calculation.
            var openingFilter = RemapAliasesForOpeningBalance(sharedFilter);

            return (sharedFilter + detailFilter, openingFilter, parameters);
        }

        public async Task<AccountLedgerDetailReportResultDTO> GetAccountLedgerDetailReport(
            int firstNumber, int maxResult, AccountReportCriteria criteria,
            List<AccountReportSortOptionDTO> sortBy, LoginDTO loginDTO)
        {
            var (filter, openingFilter, parameters) = BuildFilterAndParams(criteria, loginDTO);

            parameters.Add("Offset", Math.Max(0, firstNumber - 1));
            parameters.Add("PageSize", maxResult);

            var orderBy = AccountReportSortBuilder.BuildLedgerOrderBy(sortBy);
            var sql = AccountLedgerReportsQB.GET_ACCOUNT_LEDGER_DETAIL_REPORT
                .Replace("{DynamicFilter}", filter)
                .Replace("{OpeningDynamicFilter}", openingFilter)
                .Replace("{OrderBy}", orderBy);
            var paged = await _queryExecutor
                .QueryPagedAsync<AccountLedgerDetailReportDTO>(loginDTO, sql, parameters)
                .ConfigureAwait(false);
            var rows = (paged.Items ?? Enumerable.Empty<AccountLedgerDetailReportDTO>()).ToList();

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

            if (rows.Count > 0)
            {
                var voucherDetailIds = rows.Select(r => r.VoucherDetailId).ToList();

                if (criteria.NeedBillAllocation > 0)
                {
                    var bills = (await _queryExecutor
                        .QueryAsync<AccountRegisterBillDTO>(loginDTO, AccountReportsQB.GET_ACCOUNT_REGISTER_BILL_ALLOCATION,
                            new { VoucherDetailIds = voucherDetailIds })
                        .ConfigureAwait(false)).ToLookup(b => b.VoucherDetailId);
                    foreach (var row in rows)
                        row.VoucherBillAllocationArray = bills[row.VoucherDetailId].ToList();
                }

                if (criteria.NeedCostCenter > 0)
                {
                    var costCenters = (await _queryExecutor
                        .QueryAsync<AccountRegisterCCDTO>(loginDTO, AccountReportsQB.GET_ACCOUNT_REGISTER_COST_CENTER,
                            new { VoucherDetailIds = voucherDetailIds })
                        .ConfigureAwait(false)).ToLookup(c => c.VoucherDetailId);
                    foreach (var row in rows)
                        row.VoucherCostCenterArray = costCenters[row.VoucherDetailId].ToList();
                }

                if (criteria.NeedInstrument > 0)
                {
                    var instruments = (await _queryExecutor
                        .QueryAsync<AccountRegisterInstrumentDTO>(loginDTO, AccountReportsQB.GET_ACCOUNT_REGISTER_INSTRUMENT,
                            new { VoucherDetailIds = voucherDetailIds })
                        .ConfigureAwait(false)).ToLookup(i => i.VoucherDetailId);
                    foreach (var row in rows)
                        row.VoucherInstrumentArray = instruments[row.VoucherDetailId].ToList();
                }

                if (criteria.NeedAddon > 0)
                {
                    var addons = (await _queryExecutor
                        .QueryAsync<AccountRegisterAddonDTO>(loginDTO, AccountReportsQB.GET_ACCOUNT_REGISTER_ADDON,
                            new { VoucherDetailIds = voucherDetailIds })
                        .ConfigureAwait(false)).ToLookup(a => a.VoucherDetailId);
                    foreach (var row in rows)
                        row.VoucherAddonArray = addons[row.VoucherDetailId].ToList();
                }

                if (criteria.NeedTDS > 0)
                {
                    var tds = (await _queryExecutor
                        .QueryAsync<AccountRegisterTDSDTO>(loginDTO, AccountReportsQB.GET_ACCOUNT_REGISTER_TDS,
                            new { VoucherDetailIds = voucherDetailIds })
                        .ConfigureAwait(false)).ToLookup(t => t.VoucherDetailId);
                    foreach (var row in rows)
                        row.VoucherTDSArray = tds[row.VoucherDetailId].ToList();
                }
            }

            return new AccountLedgerDetailReportResultDTO
            {
                Items = rows,
                TotalCount = paged.TotalCount,
                TotalDebit = totals?.TotalDebit ?? 0,
                TotalCredit = totals?.TotalCredit ?? 0
            };
        }

        // Export/bulk path — no OFFSET/FETCH in the underlying SQL; child-detail arrays (bill/cost
        // center/instrument/addon/TDS) are intentionally NOT attached here, matching
        // DayBookReportDAL.GetDayBooksStream's precedent of keeping the stream to one row per
        // voucher-detail-line without N+1 child lookups on a potentially unbounded row count.
        public async IAsyncEnumerable<AccountLedgerDetailReportDTO> GetAccountLedgerDetailStream(
            AccountReportCriteria criteria, LoginDTO loginDTO,
            [EnumeratorCancellation] CancellationToken ct)
        {
            var (filter, openingFilter, parameters) = BuildFilterAndParams(criteria, loginDTO);
            var sql = AccountLedgerReportsQB.GET_ACCOUNT_LEDGER_DETAIL_STREAM
                .Replace("{DynamicFilter}", filter)
                .Replace("{OpeningDynamicFilter}", openingFilter)
                .Replace("{OrderBy}", AccountReportSortBuilder.BuildLedgerOrderBy(null));

            await foreach (var row in _queryExecutor
                .QueryStreamAsync<AccountLedgerDetailReportDTO>(loginDTO, sql, parameters, ct)
                .ConfigureAwait(false))
            {
                yield return row;
            }
        }

        public async Task<int> GetDistinctAccountCount(AccountReportCriteria criteria, LoginDTO loginDTO)
        {
            var (filter, _, parameters) = BuildFilterAndParams(criteria, loginDTO);
            var sql = AccountLedgerReportsQB.GET_DISTINCT_ACCOUNT_COUNT.Replace("{DynamicFilter}", filter);
            return await _queryExecutor
                .ExecuteScalarAsync<int>(loginDTO, sql, parameters)
                .ConfigureAwait(false);
        }
    }
}