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

namespace AccountsDAL.CustomCode.AccountReports.AccountTrialBalance
{
    public class AccountTrialBalanceReportDAL : IAccountTrialBalanceReportDAL
    {
        // Fixed Retained Earnings / P&L account seeded by migration
        // (20260803_AccountTrialBalance_RetainedEarnings_*.sql) — see TrialBalanceReportQB.cs's class
        // header comment for why this is a real MACCOUNT row rather than a config value.
        private const int RETAINED_EARNINGS_ACCOUNT_ID = -1399900600;

        // Fixed lookup for the Comparative axis — never raw user SQL, same safety pattern as
        // AccountReportSortBuilder's allow-listed field->column map. 0=OU, 1=Company, 2=Branch,
        // 3=Division, 4=OUGroup.
        private static readonly Dictionary<byte, (string Join, string IdColumn, string CodeColumn, string NameColumn)> ComparativeAxisMap = new()
        {
            [0] = ("LEFT JOIN MORGANIZATIONUNIT OU0 ON V.OUID = OU0.OUID",
                   "OU0.OUID", "OU0.ORGANIZATIONUNITCODE", "OU0.ORGANIZATIONUNITNAME"),
            [1] = ("LEFT JOIN MORGANIZATIONUNIT OU0 ON V.OUID = OU0.OUID LEFT JOIN MCOMPANY AX ON OU0.COMPANYID = AX.COMPANYID",
                   "OU0.COMPANYID", "AX.COMPANYCODE", "AX.COMPANYNAME"),
            [2] = ("LEFT JOIN MORGANIZATIONUNIT OU0 ON V.OUID = OU0.OUID LEFT JOIN MBRANCH AX ON OU0.BRANCHID = AX.BRANCHID",
                   "OU0.BRANCHID", "AX.BRANCHCODE", "AX.BRANCHNAME"),
            [3] = ("LEFT JOIN MORGANIZATIONUNIT OU0 ON V.OUID = OU0.OUID LEFT JOIN MDIVISION AX ON OU0.DIVISIONID = AX.DIVISIONID",
                   "OU0.DIVISIONID", "AX.DIVISIONCODE", "AX.DIVISIONNAME"),
            // OU Group — an OU can belong to more than one group; a voucher's OU matching multiple
            // MORGANIZATIONGROUPDETAIL rows deliberately fans out into one row per group it belongs
            // to (same tolerance-for-overlap already accepted elsewhere in this report family's own
            // OU-access-rights UNION). INNER JOIN is intentional: an OU with no group membership at
            // all simply doesn't appear under this axis.
            [4] = ("INNER JOIN MORGANIZATIONGROUPDETAIL OGD2 ON OGD2.OUID = V.OUID LEFT JOIN MORGANIZATIONGROUP OG ON OGD2.ORGANIZATIONGROUPID = OG.ORGANIZATIONGROUPID",
                   "OGD2.ORGANIZATIONGROUPID", "OG.ORGANIZATIONGROUPCODE", "OG.ORGANIZATIONGROUPNAME"),
        };

        private readonly IQueryExecutor _queryExecutor;

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

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

            // Same SqlDateTime-overflow avoidance as Outstanding/Ageing/Collection Projection — Build()
            // unconditionally adds PeriodFromDate/PeriodToDate, and TB never sets them (only AsOnDate),
            // so they default to DateTime.MinValue. Even though this report's SQL text never references
            // @PeriodFromDate/@PeriodToDate, Dapper's DynamicParameters still converts every added value
            // to its SqlDbType at bind time — DateTime.MinValue (0001-01-01) is below SqlDateTime's
            // minimum (1753-01-01) and throws regardless of whether the parameter is actually used.
            // Confirmed live 2026-08-03: HTTP 200 with GeneralErrors "SqlDateTime overflow" before this fix.
            parameters.Add("PeriodFromDate", criteria.AsOnDate);
            parameters.Add("PeriodToDate", criteria.AsOnDate);
            parameters.Add("AsOnDate", criteria.AsOnDate);
            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);

            // Financial-year start for the nature-aware P&L reset (see TrialBalanceReportQB.cs). If
            // @AsOnDate doesn't fall inside any MPERIOD row (e.g. a date before the earliest FY setup),
            // fall back to DateTime.MinValue-safe SQL floor (1900-01-01) — every P&L-nature row will
            // then look "within the current FY," i.e. no reset applied, which is the safe default when
            // the FY can't be determined rather than silently zeroing everything out.
            var fyStartDate = await _queryExecutor
                .ExecuteScalarAsync<DateTime?>(loginDTO, TrialBalanceReportQB.GET_FY_START_DATE,
                    new { criteria.AsOnDate }, null, false, default)
                .ConfigureAwait(false) ?? new DateTime(1900, 1, 1);
            parameters.Add("FYStartDate", fyStartDate);

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

            // Default display order follows each level's own master-table SORTORDER (the hierarchical
            // Type->Schedule->Group->Account view groups client-side off this row order, so both the
            // group headers' order and the leaf accounts' order within a group come from here) — not
            // alphabetical by code, which is what these defaulted to before.
            var (sql, defaultOrderBy) = criteria.GroupType switch
            {
                1 => (TrialBalanceReportQB.GET_TRIAL_BALANCE_ACCOUNTGROUP, "AGS.SORTORDER, AG.SORTORDER"),
                2 => (TrialBalanceReportQB.GET_TRIAL_BALANCE_ACCOUNTSCHEDULE, "AGS.SORTORDER"),
                3 => (TrialBalanceReportQB.GET_TRIAL_BALANCE_SUBACCOUNT, "AGS.SORTORDER, AG.SORTORDER, CTRL.SORTORDER"),
                _ => (TrialBalanceReportQB.GET_TRIAL_BALANCE_ACCOUNT, "AGS.SORTORDER, AG.SORTORDER, ACC.SORTORDER"),
            };

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

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

            var totalsSql = TrialBalanceReportQB.GET_TRIAL_BALANCE_TOTALS.Replace("{DynamicFilter}", filter);
            var totals = await _queryExecutor
                .QuerySingleAsync<TrialBalanceTotalsDTO>(loginDTO, totalsSql, parameters)
                .ConfigureAwait(false);
            var totalDebit = totals?.TotalDebit ?? 0;
            var totalCredit = totals?.TotalCredit ?? 0;

            // Retained Earnings plug — cumulative net P&L strictly before the FY start, attributed to
            // the fixed RE account. Merged into an existing row at the requested GroupType's grain if
            // one already exists (e.g. another real account later added to the same group/schedule),
            // otherwise appended as its own row. Nothing is ever posted to this account by voucher, so
            // this is always synthesized here, never returned by the main paged query itself.
            var plugFilter = AccountReportFilterBuilder.Build(criteria, loginDTO).filter;
            var plugNet = await _queryExecutor
                .ExecuteScalarAsync<decimal>(loginDTO, TrialBalanceReportQB.GET_RETAINED_EARNINGS_PLUG
                    .Replace("{DynamicFilter}", plugFilter), parameters, null, false, default)
                .ConfigureAwait(false);

            if (plugNet != 0)
            {
                var reInfo = await _queryExecutor
                    .QuerySingleAsync<TrialBalanceReportDTO>(loginDTO,
                        TrialBalanceReportQB.GET_RETAINED_EARNINGS_ACCOUNT_INFO,
                        new { RetainedEarningsAccountId = RETAINED_EARNINGS_ACCOUNT_ID }, null, false, default)
                    .ConfigureAwait(false);

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

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

                var existing = items.FirstOrDefault(matchKey);
                if (existing != null)
                {
                    existing.Debit += plugDebit;
                    existing.Credit += plugCredit;
                }
                else
                {
                    items.Add(new TrialBalanceReportDTO
                    {
                        AccountId = criteria.GroupType is 0 or 3 ? reInfo.AccountId : 0,
                        AccountCode = criteria.GroupType is 0 or 3 ? reInfo.AccountCode : null,
                        AccountName = criteria.GroupType is 0 or 3 ? reInfo.AccountName : null,
                        AccountGroupId = reInfo.AccountGroupId,
                        AccountGroupCode = reInfo.AccountGroupCode,
                        AccountGroupName = reInfo.AccountGroupName,
                        AccountScheduleId = reInfo.AccountScheduleId,
                        AccountScheduleCode = reInfo.AccountScheduleCode,
                        AccountScheduleName = reInfo.AccountScheduleName,
                        Debit = plugDebit,
                        Credit = plugCredit,
                        RowLabel = "Retained Earnings (Prior Year P&L)"
                    });
                }

                totalDebit += plugDebit;
                totalCredit += plugCredit;
            }

            // Opening-difference diagnostic — should be 0 once the plug above is in place (per the
            // user's own framing); a nonzero value here means a genuine initial-data-setup gap, not a
            // recurring reconciliation concern. Surfaced as its own row, not attributed to any account.
            var openingDifference = totalDebit - totalCredit;
            if (openingDifference != 0)
            {
                items.Add(new TrialBalanceReportDTO
                {
                    Debit = openingDifference > 0 ? openingDifference : 0,
                    Credit = openingDifference < 0 ? -openingDifference : 0,
                    RowLabel = "Difference in Opening Balance"
                });
                if (openingDifference > 0) totalCredit += openingDifference;
                else totalDebit += -openingDifference;
            }

            return new TrialBalanceResultDTO
            {
                Items = items,
                TotalCount = paged.TotalCount + (items.Count - (paged.Items?.Count() ?? 0)),
                TotalDebit = totalDebit,
                TotalCredit = totalCredit
            };
        }

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

            var periodFromDate = criteria.PeriodFromDate;
            var periodToDate = criteria.PeriodToDate;

            // Periodic mode: caller didn't supply an explicit range, so resolve one from
            // MGBPERIOD/MGBPERIODDETAIL via PeriodType + AsOnDate (see GET_PERIOD_RANGE).
            if (periodFromDate == DateTime.MinValue || periodToDate == DateTime.MinValue)
            {
                var range = await _queryExecutor
                    .QuerySingleAsync<PeriodRangeDTO>(loginDTO, TrialBalanceReportQB.GET_PERIOD_RANGE,
                        new { criteria.PeriodType, criteria.AsOnDate }, null, false, default)
                    .ConfigureAwait(false);

                if (range == null)
                    throw new InvalidOperationException(
                        $"No MGBPERIODDETAIL sub-period found for PeriodType={criteria.PeriodType} containing {criteria.AsOnDate:yyyy-MM-dd}.");

                periodFromDate = range.PeriodFromDate;
                periodToDate = range.PeriodToDate;
            }

            var fyStartDate = await _queryExecutor
                .ExecuteScalarAsync<DateTime?>(loginDTO, TrialBalanceReportQB.GET_FY_START_DATE,
                    new { AsOnDate = periodToDate }, null, false, default)
                .ConfigureAwait(false) ?? new DateTime(1900, 1, 1);

            // Same SqlDateTime-overflow avoidance as the Summary method above — Build() unconditionally
            // adds PeriodFromDate/PeriodToDate; overwrite with the real resolved range immediately.
            parameters.Add("PeriodFromDate", periodFromDate);
            parameters.Add("PeriodToDate", periodToDate);
            parameters.Add("FYStartDate", fyStartDate);
            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);
            parameters.Add("Offset", Math.Max(0, firstNumber - 1));
            parameters.Add("PageSize", maxResult);

            // Same SORTORDER-driven default as GetTrialBalanceReport above — see that comment.
            var (sql, defaultOrderBy) = criteria.GroupType switch
            {
                1 => (TrialBalanceReportQB.GET_TRIAL_BALANCE_DETAIL_ACCOUNTGROUP, "AGS.SORTORDER, AG.SORTORDER"),
                2 => (TrialBalanceReportQB.GET_TRIAL_BALANCE_DETAIL_ACCOUNTSCHEDULE, "AGS.SORTORDER"),
                3 => (TrialBalanceReportQB.GET_TRIAL_BALANCE_DETAIL_SUBACCOUNT, "AGS.SORTORDER, AG.SORTORDER, CTRL.SORTORDER"),
                _ => (TrialBalanceReportQB.GET_TRIAL_BALANCE_DETAIL_ACCOUNT, "AGS.SORTORDER, AG.SORTORDER, ACC.SORTORDER"),
            };

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

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

            // Retained Earnings plug — same figure as the Summary method, but here it's a STANDING
            // balance (part of the account's position before this period even started), so it goes
            // into Opening AND Closing, never into the period's own Debit/Credit movement. Assumes
            // the resolved period never crosses an FY boundary (true by construction for month/
            // quarter/half-year PeriodTypes, especially the FN- financial-year-aligned ones).
            var plugFilter = AccountReportFilterBuilder.Build(criteria, loginDTO).filter;
            var plugNet = await _queryExecutor
                .ExecuteScalarAsync<decimal>(loginDTO, TrialBalanceReportQB.GET_RETAINED_EARNINGS_PLUG
                    .Replace("{DynamicFilter}", plugFilter), parameters, null, false, default)
                .ConfigureAwait(false);

            if (plugNet != 0)
            {
                var reInfo = await _queryExecutor
                    .QuerySingleAsync<TrialBalanceReportDTO>(loginDTO,
                        TrialBalanceReportQB.GET_RETAINED_EARNINGS_ACCOUNT_INFO,
                        new { RetainedEarningsAccountId = RETAINED_EARNINGS_ACCOUNT_ID }, null, false, default)
                    .ConfigureAwait(false);

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

                var existing = items.FirstOrDefault(matchKey);
                if (existing != null)
                {
                    existing.OpeningBalance += plugNet;
                    existing.ClosingBalance += plugNet;
                }
                else
                {
                    items.Add(new TrialBalanceDetailReportDTO
                    {
                        AccountId = criteria.GroupType is 0 or 3 ? reInfo.AccountId : 0,
                        AccountCode = criteria.GroupType is 0 or 3 ? reInfo.AccountCode : null,
                        AccountName = criteria.GroupType is 0 or 3 ? reInfo.AccountName : null,
                        AccountGroupId = reInfo.AccountGroupId,
                        AccountGroupCode = reInfo.AccountGroupCode,
                        AccountGroupName = reInfo.AccountGroupName,
                        AccountScheduleId = reInfo.AccountScheduleId,
                        AccountScheduleCode = reInfo.AccountScheduleCode,
                        AccountScheduleName = reInfo.AccountScheduleName,
                        OpeningBalance = plugNet,
                        ClosingBalance = plugNet,
                        RowLabel = "Retained Earnings (Prior Year P&L)"
                    });
                }
            }

            var totalDebit = items.Sum(i => i.Debit);
            var totalCredit = items.Sum(i => i.Credit);

            return new TrialBalanceDetailResultDTO
            {
                Items = items,
                TotalCount = paged.TotalCount + (items.Count - (paged.Items?.Count() ?? 0)),
                TotalDebit = totalDebit,
                TotalCredit = totalCredit,
                PeriodFromDate = periodFromDate,
                PeriodToDate = periodToDate
            };
        }

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

            parameters.Add("PeriodFromDate", criteria.AsOnDate);
            parameters.Add("PeriodToDate", criteria.AsOnDate);
            parameters.Add("AsOnDate", criteria.AsOnDate);
            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);

            var fyStartDate = await _queryExecutor
                .ExecuteScalarAsync<DateTime?>(loginDTO, TrialBalanceReportQB.GET_FY_START_DATE,
                    new { criteria.AsOnDate }, null, false, default)
                .ConfigureAwait(false) ?? new DateTime(1900, 1, 1);
            parameters.Add("FYStartDate", fyStartDate);
            parameters.Add("Offset", Math.Max(0, firstNumber - 1));
            parameters.Add("PageSize", maxResult);

            if (!ComparativeAxisMap.TryGetValue(criteria.ComparativeAxis, out var axis))
                axis = ComparativeAxisMap[0];

            // Comparative's own default order (axis then account code) — not reusing
            // BuildTrialBalanceOrderBy's allow-list since AxisValueCode isn't one of its columns.
            var orderBy = "AxisValueCode, ACC.ACCOUNTCODE";
            var finalSql = TrialBalanceReportQB.GET_TRIAL_BALANCE_COMPARATIVE
                .Replace("{DynamicFilter}", filter)
                .Replace("{AxisJoin}", axis.Join)
                .Replace("{AxisIdColumn}", axis.IdColumn)
                .Replace("{AxisCodeColumn}", axis.CodeColumn)
                .Replace("{AxisNameColumn}", axis.NameColumn)
                .Replace("{OrderBy}", orderBy);

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

            return new TrialBalanceComparativeResultDTO
            {
                Items = paged.Items ?? Enumerable.Empty<TrialBalanceComparativeReportDTO>(),
                TotalCount = paged.TotalCount
            };
        }

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

            var periodFromDate = criteria.PeriodFromDate;
            var periodToDate = criteria.PeriodToDate;

            // No explicit range supplied — default to current-financial-year-to-date, reusing the
            // existing GET_FY_START_DATE lookup rather than inventing a new default-resolution query.
            if (periodFromDate == DateTime.MinValue || periodToDate == DateTime.MinValue)
            {
                periodToDate = DateTime.Today;
                periodFromDate = await _queryExecutor
                    .ExecuteScalarAsync<DateTime?>(loginDTO, TrialBalanceReportQB.GET_FY_START_DATE,
                        new { AsOnDate = periodToDate }, null, false, default)
                    .ConfigureAwait(false) ?? new DateTime(periodToDate.Year, 1, 1);
            }

            // Overwrite whatever Build() may have bound (only happens when criteria.PeriodFromDate/
            // PeriodToDate were already non-default) with the resolved range — same pattern as
            // GetTrialBalanceDetailReport above.
            parameters.Add("PeriodFromDate", periodFromDate);
            parameters.Add("PeriodToDate", periodToDate);
            parameters.Add("PeriodType", criteria.PeriodType);
            parameters.Add("CurrencyBasis", criteria.CurrencyBasis);
            parameters.Add("Offset", Math.Max(0, firstNumber - 1));
            parameters.Add("PageSize", maxResult);

            // Same SORTORDER-driven default as GetTrialBalanceReport/GetTrialBalanceDetailReport —
            // this is the row source the Periodic Pivot (and its hierarchical Type->Schedule->Group
            // grouping) is built from, so its group-header order flows from here too.
            var orderBy = AccountReportSortBuilder.BuildTrialBalanceOrderBy(sortBy, "AGS.SORTORDER, AG.SORTORDER, ACC.SORTORDER");
            var finalSql = TrialBalanceReportQB.GET_TRIAL_BALANCE_PERIODIC
                .Replace("{DynamicFilter}", filter)
                .Replace("{OrderBy}", orderBy);

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

            return new TrialBalancePeriodicResultDTO
            {
                Items = paged.Items ?? Enumerable.Empty<TrialBalancePeriodicReportDTO>(),
                TotalCount = paged.TotalCount,
                PeriodFromDate = periodFromDate,
                PeriodToDate = periodToDate
            };
        }

        private class PeriodRangeDTO
        {
            public DateTime PeriodFromDate { get; set; }
            public DateTime PeriodToDate { get; set; }
        }
    }
}