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

namespace AccountsDAL.CustomCode.AccountReports.AccountCashFlow
{
    public class AccountCashFlowReportDAL : IAccountCashFlowReportDAL
    {
        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<CashFlowDirectResultDTO> GetCashFlowDirectReport(
            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 => (CashFlowReportQB.GET_CASH_FLOW_DIRECT_ACCOUNTGROUP, "AG.ACCOUNTGROUPCODE"),
                2 => (CashFlowReportQB.GET_CASH_FLOW_DIRECT_ACCOUNTSCHEDULE, "AGS.ACCOUNTSCHEDULECODE"),
                _ => (CashFlowReportQB.GET_CASH_FLOW_DIRECT_ACCOUNT, "ACC.ACCOUNTCODE"),
            };

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

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

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

            var totalInflow = totals?.TotalInflow ?? 0;
            var totalOutflow = totals?.TotalOutflow ?? 0;

            return new CashFlowDirectResultDTO
            {
                Items = items,
                TotalCount = paged.TotalCount,
                TotalInflow = totalInflow,
                TotalOutflow = totalOutflow,
                NetChangeInCash = totalInflow - totalOutflow
            };
        }

        public async Task<CashFlowIndirectResultDTO> GetCashFlowIndirectReport(
            AccountReportCriteria criteria, LoginDTO loginDTO)
        {
            var (filter, parameters) = AccountReportFilterBuilder.Build(criteria, loginDTO);
            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);

            var netProfit = await Scalar(CashFlowReportQB.GET_CASH_FLOW_INDIRECT_NETPROFIT, filter, parameters, loginDTO).ConfigureAwait(false);
            var currentAssets = await Scalar(CashFlowReportQB.GET_CASH_FLOW_INDIRECT_CURRENT_ASSETS, filter, parameters, loginDTO).ConfigureAwait(false);
            var currentLiabilities = await Scalar(CashFlowReportQB.GET_CASH_FLOW_INDIRECT_CURRENT_LIABILITIES, filter, parameters, loginDTO).ConfigureAwait(false);
            var fixedAssets = await Scalar(CashFlowReportQB.GET_CASH_FLOW_INDIRECT_FIXED_ASSETS, filter, parameters, loginDTO).ConfigureAwait(false);
            var capital = await Scalar(CashFlowReportQB.GET_CASH_FLOW_INDIRECT_CAPITAL, filter, parameters, loginDTO).ConfigureAwait(false);
            var longTermLiabilities = await Scalar(CashFlowReportQB.GET_CASH_FLOW_INDIRECT_LONGTERM_LIABILITIES, filter, parameters, loginDTO).ConfigureAwait(false);
            var actualCashMovement = await Scalar(CashFlowReportQB.GET_CASH_FLOW_ACTUAL_CASH_MOVEMENT, filter, parameters, loginDTO).ConfigureAwait(false);

            var operatingTotal = netProfit + currentAssets + currentLiabilities;
            var investingTotal = fixedAssets;
            var financingTotal = capital + longTermLiabilities;

            var items = new List<CashFlowIndirectLineDTO>
            {
                new() { Section = "Operating", Label = "Net Profit for the Period", Amount = netProfit },
                new() { Section = "Operating", Label = "(Increase)/Decrease in Current Assets", Amount = currentAssets },
                new() { Section = "Operating", Label = "Increase/(Decrease) in Current Liabilities", Amount = currentLiabilities },
                new() { Section = "Investing", Label = "(Increase)/Decrease in Fixed Assets", Amount = fixedAssets },
                new() { Section = "Financing", Label = "Increase/(Decrease) in Capital", Amount = capital },
                new() { Section = "Financing", Label = "Increase/(Decrease) in Long Term Liabilities", Amount = longTermLiabilities },
            };

            return new CashFlowIndirectResultDTO
            {
                Items = items,
                OperatingTotal = operatingTotal,
                InvestingTotal = investingTotal,
                FinancingTotal = financingTotal,
                NetChangeInCash = operatingTotal + investingTotal + financingTotal,
                ActualCashMovement = actualCashMovement
            };
        }

        private async Task<decimal> Scalar(string query, string filter, Dapper.DynamicParameters parameters, LoginDTO loginDTO)
        {
            return await _queryExecutor
                .ExecuteScalarAsync<decimal>(loginDTO, query.Replace("{DynamicFilter}", filter), parameters, null, false, default)
                .ConfigureAwait(false);
        }

        private class CashFlowDirectTotalsDTO
        {
            public decimal TotalInflow { get; set; }
            public decimal TotalOutflow { get; set; }
        }
    }
}