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

namespace AccountsDAL.CustomCode.AccountReports.AccountReceivableDashboard
{
    public class AccountReceivableDashboardDAL : IAccountReceivableDashboardDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<AccountReceivableDashboardResultDTO> GetReceivableDashboard(
            AccountReportCriteria criteria, LoginDTO loginDTO, CancellationToken ct)
        {
            var (sharedFilter, parameters) = AccountReportFilterBuilder.Build(criteria, loginDTO);
            var (outstandingFilter, outstandingParameters) = AccountReportFilterBuilder.BuildOutstandingOnly(criteria);
            parameters.AddDynamicParams(outstandingParameters);

            // Finance Analytics Dashboards, Phase 0c — BizDimension (V1-V5) filter, reusing the
            // same technique AccountRegister's own detail report already proves live: every query
            // this dashboard runs (KPI/InchargeWise/AgeingWise) joins TVOUCHERDETAIL as VD, same as
            // AccountRegister's detail query, so the EXISTS(...VD.VOUCHERDETAILID...) fragment
            // applies unchanged.
            var (bizDimensionFilter, bizDimensionParameters) = AccountReportFilterBuilder.BuildBizDimensionOnly(criteria);
            parameters.AddDynamicParams(bizDimensionParameters);

            // Same fix already established for Outstanding/Ageing: Build() unconditionally adds
            // PeriodFromDate/PeriodToDate as DateTime params -- Outstanding/Ageing-family reports
            // never set them, leaving DateTime.MinValue, which ADO.NET rejects as "SqlDateTime
            // overflow" the moment the parameter is bound.
            parameters.Add("PeriodFromDate", criteria.AsOnDate);
            parameters.Add("PeriodToDate", criteria.AsOnDate);
            parameters.Add("AsOnDate", criteria.AsOnDate);
            parameters.Add("InfoRequired", criteria.IncludeInformational ? 0 : 1);

            var filter = sharedFilter + outstandingFilter + bizDimensionFilter;

            // Piece 1 -- KPI: whole-book as-of-date aggregate, reusing the already-verified Outstanding
            // Summary's own totals query verbatim.
            var kpiSql = AccountOutstandingReportsQB.GET_OUTSTANDING_DETAIL_TOTALS.Replace("{DynamicFilter}", filter);
            var kpi = await _queryExecutor
                .QuerySingleAsync<OutstandingTotalsDTO>(loginDTO, kpiSql, parameters, cancellationToken: ct)
                .ConfigureAwait(false) ?? new OutstandingTotalsDTO();

            // Piece 2 -- incharge-wise: reuses Outstanding Summary's own GroupType=3 (InCharge) query
            // and GroupBy fragment verbatim.
            var isList = criteria.OutstandingReportMode == 0;
            var (groupBySelect, groupByColumns) = AccountOutstandingGroupByBuilder.Build(3, isList);
            var inchargeSql = AccountOutstandingReportsQB.GET_OUTSTANDING_SUMMARY
                .Replace("{GroupBySelect}", groupBySelect)
                .Replace("{GroupByColumns}", groupByColumns)
                .Replace("{DynamicFilter}", filter)
                .Replace("{OrderBy}", "Receivable DESC")
                .Replace("OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY", "");
            var inchargeWise = (await _queryExecutor
                .QueryAsync<OutstandingSummaryReportDTO>(loginDTO, inchargeSql, parameters, cancellationToken: ct)
                .ConfigureAwait(false)).ToList();

            // Piece 3 -- ageing-wise: one overall bucket-total row (no grouping dimension), reusing
            // the already-verified, fully caller-configurable AccountAgeingBucketBuilder.
            var (bucketColumns, bucketParameters) = AccountAgeingBucketBuilder.Build(criteria, aggregate: true);
            parameters.AddDynamicParams(bucketParameters);
            var ageingSql = AccountReceivableDashboardQB.GET_AGEING_OVERALL
                .Replace("{AgeBucketColumns}", bucketColumns)
                .Replace("{DynamicFilter}", filter);
            var ageingWise = await _queryExecutor
                .QuerySingleAsync<AgeingReportDTO>(loginDTO, ageingSql, parameters, cancellationToken: ct)
                .ConfigureAwait(false) ?? new AgeingReportDTO();

            return new AccountReceivableDashboardResultDTO
            {
                Kpi = kpi,
                InchargeWise = inchargeWise,
                AgeingWise = ageingWise
            };
        }
    }
}