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

namespace AccountsDAL.CustomCode.AccountReports.AccountBalanceSheet
{
    public class AccountBalanceSheetReportDAL : IAccountBalanceSheetReportDAL
    {
        // Fixed plug accounts — RE0001 seeded by Trial Balance's own migration
        // (20260803_AccountTrialBalance_RetainedEarnings_*.sql), RE0002 seeded by this report's own
        // migration (20260804_AccountBalanceSheet_CurrentYearPandL_*.sql). Both live under RESERVES
        // AND SURPLUS (ACCOUNTGROUPID -1899999999).
        private const int RETAINED_EARNINGS_ACCOUNT_ID = -1399900600;
        private const int CURRENT_YEAR_PANDL_ACCOUNT_ID = -1399900601;

        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<BalanceSheetResultDTO> GetBalanceSheetReport(
            int firstNumber, int maxResult, AccountReportCriteria criteria,
            List<AccountReportSortOptionDTO> sortBy, LoginDTO loginDTO)
        {
            var (filter, parameters) = AccountReportFilterBuilder.Build(criteria, loginDTO);

            parameters.Add("AsOnDate", criteria.AsOnDate);
            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);
            parameters.Add("Offset", Math.Max(0, firstNumber - 1));
            parameters.Add("PageSize", maxResult);

            var fyStartDate = await _queryExecutor
                .ExecuteScalarAsync<DateTime?>(loginDTO, BalanceSheetReportQB.GET_FY_START_DATE,
                    new { criteria.AsOnDate }, null, false, default)
                .ConfigureAwait(false) ?? new DateTime(1900, 1, 1);
            parameters.Add("FYStartDate", fyStartDate);

            var (sql, defaultOrderBy) = criteria.GroupType switch
            {
                1 => (BalanceSheetReportQB.GET_BALANCE_SHEET_ACCOUNTGROUP, "AG.ACCOUNTGROUPCODE"),
                2 => (BalanceSheetReportQB.GET_BALANCE_SHEET_ACCOUNTSCHEDULE, "AGS.ACCOUNTSCHEDULECODE"),
                _ => (BalanceSheetReportQB.GET_BALANCE_SHEET_ACCOUNT, "ACC.ACCOUNTCODE"),
            };

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

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

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

            var totalAssets = (totals?.AssetDebit ?? 0) - (totals?.AssetCredit ?? 0);
            var totalLiabilitiesAndEquity = (totals?.LiabilityCredit ?? 0) - (totals?.LiabilityDebit ?? 0);

            // Two plug lines, same "append to existing row at this GroupType's grain, else append a
            // new row" merge logic as Trial Balance's own Retained Earnings plug.
            var plugFilter = AccountReportFilterBuilder.Build(criteria, loginDTO).filter;

            await AddPlugAsync(BalanceSheetReportQB.GET_RETAINED_EARNINGS_PLUG, plugFilter, parameters,
                criteria, loginDTO, RETAINED_EARNINGS_ACCOUNT_ID, "Retained Earnings (Prior Years)",
                items, amount => { totalLiabilitiesAndEquity += amount; }).ConfigureAwait(false);

            await AddPlugAsync(BalanceSheetReportQB.GET_CURRENT_YEAR_PANDL_PLUG, plugFilter, parameters,
                criteria, loginDTO, CURRENT_YEAR_PANDL_ACCOUNT_ID, "Current Year Profit/(Loss)",
                items, amount => { totalLiabilitiesAndEquity += amount; }).ConfigureAwait(false);

            return new BalanceSheetResultDTO
            {
                Items = items,
                TotalCount = paged.TotalCount + (items.Count - (paged.Items?.Count() ?? 0)),
                TotalAssets = totalAssets,
                TotalLiabilitiesAndEquity = totalLiabilitiesAndEquity
            };
        }

        // Plug net sign convention: positive = net Debit (a loss, reduces equity), negative = net
        // Credit (a profit, increases equity) — same as Trial Balance. Equity/Liability side shows a
        // profit as a Credit-column figure, so plugNet<0 (profit) contributes +(-plugNet) to
        // TotalLiabilitiesAndEquity's Credit total, and a loss (plugNet>0) contributes -plugNet
        // (reduces it) — net effect handled below via the signed `amount` passed to the callback.
        private async Task AddPlugAsync(
            string plugQuery, string plugFilter, Dapper.DynamicParameters parameters,
            AccountReportCriteria criteria, LoginDTO loginDTO, int plugAccountId, string rowLabel,
            List<BalanceSheetReportDTO> items, Action<decimal> addToLiabilitiesAndEquity)
        {
            var plugNet = await _queryExecutor
                .ExecuteScalarAsync<decimal>(loginDTO, plugQuery.Replace("{DynamicFilter}", plugFilter),
                    parameters, null, false, default)
                .ConfigureAwait(false);

            if (plugNet == 0) return;

            var plugInfo = await _queryExecutor
                .QuerySingleAsync<BalanceSheetReportDTO>(loginDTO, BalanceSheetReportQB.GET_PLUG_ACCOUNT_INFO,
                    new { PlugAccountId = plugAccountId }, null, false, default)
                .ConfigureAwait(false);

            var plugDebit = plugNet > 0 ? plugNet : 0;
            var plugCredit = plugNet < 0 ? -plugNet : 0;

            var matchKey = (Func<BalanceSheetReportDTO, bool>)(criteria.GroupType switch
            {
                1 => row => row.AccountGroupId == plugInfo.AccountGroupId,
                2 => row => row.AccountScheduleId == plugInfo.AccountScheduleId,
                _ => row => row.AccountId == plugInfo.AccountId,
            });

            var existing = items.FirstOrDefault(matchKey);
            if (existing != null)
            {
                existing.Debit += plugDebit;
                existing.Credit += plugCredit;
            }
            else
            {
                items.Add(new BalanceSheetReportDTO
                {
                    AccountId = criteria.GroupType == 0 ? plugInfo.AccountId : 0,
                    AccountCode = criteria.GroupType == 0 ? plugInfo.AccountCode : null,
                    AccountName = criteria.GroupType == 0 ? plugInfo.AccountName : null,
                    AccountGroupId = plugInfo.AccountGroupId,
                    AccountGroupCode = plugInfo.AccountGroupCode,
                    AccountGroupName = plugInfo.AccountGroupName,
                    AccountScheduleId = plugInfo.AccountScheduleId,
                    AccountScheduleCode = plugInfo.AccountScheduleCode,
                    AccountScheduleName = plugInfo.AccountScheduleName,
                    Nature = 0,
                    Debit = plugDebit,
                    Credit = plugCredit,
                    RowLabel = rowLabel
                });
            }

            addToLiabilitiesAndEquity(plugCredit - plugDebit);
        }

        private class BalanceSheetTotalsDTO
        {
            public decimal AssetDebit { get; set; }
            public decimal AssetCredit { get; set; }
            public decimal LiabilityDebit { get; set; }
            public decimal LiabilityCredit { get; set; }
        }
    }
}