using AccountsDAL.CustomCode.AccountReports;
using AccountsDAL.DTO.AccountsReports;
using AccountsDAL.Query.AccountReports;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Threading.Tasks;

namespace AccountsDAL.CustomCode.AccountReports.Register
{
    public class AccountRegisterReportDAL : IAccountRegisterReportDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        // ============================================================
        // UI PAGED METHOD
        // ============================================================

        public async Task<AccountRegisterDetailReportResultDTO>GetAccountRegisterDetailReport(int firstNumber,int maxResult,AccountReportCriteria criteria,List<AccountReportSortOptionDTO> sortBy,LoginDTO loginDTO,CancellationToken ct = default)
        {
            var (sharedFilter, parameters) =AccountReportFilterBuilder.Build(criteria, loginDTO);
            var (detailFilter, detailParameters) =AccountReportFilterBuilder.BuildDetailOnly(criteria);
            parameters.AddDynamicParams(detailParameters);
            string filter =sharedFilter + detailFilter;

            parameters.Add("Offset",Math.Max(0, firstNumber - 1));
            parameters.Add("PageSize",maxResult);
            string orderBy =AccountReportSortBuilder.BuildDetailOrderBy(sortBy);

            string sql =AccountReportsQB.GET_ACCOUNT_REGISTER_DETAIL_REPORT.Replace("{DynamicFilter}",filter).Replace("{OrderBy}",orderBy);
            var paged = await _queryExecutor.QueryPagedAsync<AccountRegisterDetailReportDTO>(loginDTO,sql,parameters).ConfigureAwait(false);

            var rows =(paged.Items?? Enumerable.Empty<AccountRegisterDetailReportDTO>()).ToList();

            string totalsSql =AccountReportsQB.GET_ACCOUNT_REGISTER_DETAIL_TOTALS.Replace("{DynamicFilter}",filter);

            var totals = await _queryExecutor
                .QuerySingleAsync<
                    AccountRegisterTotalsDTO>(
                    loginDTO,
                    totalsSql,
                    parameters)
                .ConfigureAwait(false);

            await LoadAdditionalDetailsAsync(
                rows,
                criteria,
                loginDTO);

            return new AccountRegisterDetailReportResultDTO
            {
                Items = rows,

                TotalCount =
                    paged.TotalCount,

                TotalDebit =
                    totals?.TotalDebit ?? 0,

                TotalCredit =
                    totals?.TotalCredit ?? 0
            };
        }

        // ============================================================
        // REPORT STREAM METHOD
        // ============================================================

        public async IAsyncEnumerable<AccountRegisterDetailReportDTO>GetAccountRegisterDetailReportStream(int firstNumber,int maxResult,AccountReportCriteria criteria,List<AccountReportSortOptionDTO> sortBy,LoginDTO loginDTO,
        [EnumeratorCancellation]
        CancellationToken ct = default)
        {
            // 1. Build filters
            var (sharedFilter, parameters) =AccountReportFilterBuilder.Build(criteria,loginDTO);

            var (detailFilter, detailParameters) =AccountReportFilterBuilder.BuildDetailOnly(criteria);

            parameters.AddDynamicParams(detailParameters);

            string filter =
                sharedFilter + detailFilter;

            // 2. Build sorting
            string orderBy =AccountReportSortBuilder.BuildDetailOrderBy(sortBy ?? new List<AccountReportSortOptionDTO>());

            // 3. IMPORTANT:
            // Add the parameters required by OFFSET/FETCH.
            int offset = Math.Max(0,firstNumber - 1);

            int pageSize = maxResult > 0? maxResult: 500;

            parameters.Add(
                "Offset",
                offset);

            parameters.Add(
                "PageSize",
                pageSize);

            // 4. Build SQL
            string sql =AccountReportsQB.GET_ACCOUNT_REGISTER_DETAIL_REPORT.Replace("{DynamicFilter}",filter).Replace("{OrderBy}",orderBy);

            // 5. Stream records, then batch-attach child arrays (Instrument/TDS/BillAllocation/etc.)
            // the same way the UI-paged method does. Buffering the whole stream into one list before
            // calling LoadAdditionalDetailsAsync is safe today ONLY because the caller
            // (AccountRegisterReportBLL) already caps this stream at DEFAULT_MAX_RESULT_CAP (500)
            // rows via the same Offset/PageSize params the UI grid uses — a separate, pre-existing
            // gap relative to BaseReportEndpoint's "return ALL rows" export contract, not fixed here.
            // If that cap is ever removed, this method must batch in chunks instead of buffering the
            // whole result.
            var rows =new List<AccountRegisterDetailReportDTO>();
            await foreach (var row in _queryExecutor.QueryStreamAsync<AccountRegisterDetailReportDTO>(loginDTO,sql,parameters,ct).ConfigureAwait(false))
            {
                rows.Add(row);
            }

            await LoadAdditionalDetailsAsync(rows,criteria,loginDTO).ConfigureAwait(false);

            foreach (var row in rows)
            {
                yield return row;
            }
        }

        // ============================================================
        // ADDITIONAL DETAIL LOADING
        // ============================================================

        private async Task LoadAdditionalDetailsAsync(List<AccountRegisterDetailReportDTO> rows,AccountReportCriteria criteria,LoginDTO loginDTO)
        {
            if (rows.Count == 0)
            {
                return;
            }

            var voucherDetailIds =rows.Select(row => row.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(item =>item.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(item =>item.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(item =>item.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(item =>item.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(item =>item.VoucherDetailId);

                foreach (var row in rows)
                {
                    row.VoucherTDSArray =tds[row.VoucherDetailId].ToList();
                }
            }
        }
    }
}