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

namespace AccountsDAL.CustomCode.AccountReports.AccountFundFlow
{
    public class AccountFundFlowReportDAL : IAccountFundFlowReportDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<FundFlowResultDTO> GetFundFlowReport(
            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);

            var (sql, defaultOrderBy) = criteria.GroupType switch
            {
                1 => (FundFlowReportQB.GET_FUND_FLOW_ACCOUNTGROUP, "AG.ACCOUNTGROUPCODE"),
                2 => (FundFlowReportQB.GET_FUND_FLOW_ACCOUNTSCHEDULE, "AGS.ACCOUNTSCHEDULECODE"),
                _ => (FundFlowReportQB.GET_FUND_FLOW_ACCOUNT, "ACC.ACCOUNTCODE"),
            };

            var orderBy = AccountReportSortBuilder.BuildTrialBalanceOrderBy(sortBy, defaultOrderBy);
            var finalSql = sql.Replace("{DynamicFilter}", filter).Replace("{OrderBy}", orderBy);

            var paged = await _queryExecutor
                .QueryPagedAsync<FundFlowReportDTO>(loginDTO, finalSql, parameters)
                .ConfigureAwait(false);
            var items = (paged.Items ?? Enumerable.Empty<FundFlowReportDTO>()).ToList();

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

            var totalSource = totals?.TotalSource ?? 0;
            var totalApplication = totals?.TotalApplication ?? 0;

            // P&L-for-period balancing row — no natural account/group to attribute it to (unlike
            // Trial Balance/Balance Sheet's Retained Earnings account), so it's always its own
            // standalone row, matching legacy's own unattributed "PandL" column.
            var pandLFilter = AccountReportFilterBuilder.Build(criteria, loginDTO).filter;
            var pandLNet = await _queryExecutor
                .ExecuteScalarAsync<decimal>(loginDTO, FundFlowReportQB.GET_FUND_FLOW_PANDL_BALANCING_ROW
                    .Replace("{DynamicFilter}", pandLFilter), parameters, null, false, default)
                .ConfigureAwait(false);

            if (pandLNet != 0)
            {
                var isSource = pandLNet < 0; // net credit = profit = Source
                var amount = Math.Abs(pandLNet);

                items.Add(new FundFlowReportDTO
                {
                    FlowType = isSource ? "Source" : "Application",
                    Amount = amount,
                    RowLabel = "Profit/(Loss) for the Period"
                });

                if (isSource) totalSource += amount;
                else totalApplication += amount;
            }

            return new FundFlowResultDTO
            {
                Items = items,
                TotalCount = paged.TotalCount + (items.Count - (paged.Items?.Count() ?? 0)),
                TotalSource = totalSource,
                TotalApplication = totalApplication
            };
        }

        private class FundFlowTotalsDTO
        {
            public decimal TotalSource { get; set; }
            public decimal TotalApplication { get; set; }
        }
    }
}