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

namespace AccountsDAL.CustomCode.AccountReports.AccountOutstanding
{
    public class AccountOutstandingReportDAL : IAccountOutstandingReportDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<OutstandingDetailReportResultDTO> GetOutstandingDetailReport(
            int firstNumber, int maxResult, AccountReportCriteria criteria,
            List<AccountReportSortOptionDTO> sortBy, LoginDTO loginDTO)
        {
            var (sharedFilter, parameters) = AccountReportFilterBuilder.Build(criteria, loginDTO);
            var (outstandingFilter, outstandingParameters) = AccountReportFilterBuilder.BuildOutstandingOnly(criteria);
            parameters.AddDynamicParams(outstandingParameters);

            // Build() unconditionally adds PeriodFromDate/PeriodToDate (unused by this report's SQL, but
            // still bound by Dapper/ADO.NET as real DATETIME parameters) defaulting to DateTime.MinValue
            // for a report that never sets them — SqlParameter throws "SqlDateTime overflow" the moment
            // that value is assigned, before the SQL text (which never references either param) is even
            // considered. Overwrite with a valid date; DynamicParameters.Add on an existing name replaces
            // its value rather than throwing. Found live (HTTP 200 wrapping a 400 body), not by build/sqlcmd.
            parameters.Add("PeriodFromDate", criteria.AsOnDate);
            parameters.Add("PeriodToDate", criteria.AsOnDate);
            parameters.Add("AsOnDate", criteria.AsOnDate);
            // @InfoRequired's polarity is inverted from IncludeInformational — confirmed live, see
            // AccountOutstandingReportsQB.cs header comment.
            parameters.Add("InfoRequired", criteria.IncludeInformational ? 0 : 1);
            parameters.Add("Offset", Math.Max(0, firstNumber - 1));
            parameters.Add("PageSize", maxResult);

            var filter = sharedFilter + outstandingFilter;
            var orderBy = AccountReportSortBuilder.BuildOutstandingDetailOrderBy(sortBy);
            var sql = AccountOutstandingReportsQB.GET_OUTSTANDING_DETAIL
                .Replace("{DynamicFilter}", filter)
                .Replace("{OrderBy}", orderBy);

            var paged = await _queryExecutor
                .QueryPagedAsync<OutstandingDetailReportDTO>(loginDTO, sql, parameters)
                .ConfigureAwait(false);

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

            return new OutstandingDetailReportResultDTO
            {
                Items = paged.Items ?? Enumerable.Empty<OutstandingDetailReportDTO>(),
                TotalCount = paged.TotalCount,
                TotalReceivable = totals?.TotalReceivable ?? 0,
                TotalPayable = totals?.TotalPayable ?? 0,
                TotalBalance = totals?.TotalBalance ?? 0
            };
        }
    }
}