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

namespace AccountsDAL.CustomCode.AccountReports.AccountBudgetVsComparison
{
    public class AccountBudgetVsComparisonReportDAL : IAccountBudgetVsComparisonReportDAL
    {
        // Legacy's "outype"/SubType axis (0=OU,1=Company,2=Branch,3=Division,9=Overall) -- fixed
        // allow-list, never built from raw request input. 9/Overall needs no join at all: MBUDGETDATA
        // is already reached via the shared JOINS fragment, so the pivot query's own GROUP BY on a
        // bare constant collapses every row into one, exactly matching legacy's own @outype=9 case.
        private static readonly Dictionary<byte, (string Join, string Id, string Code, string Name)> OuAxisMap = new()
        {
            [0] = ("LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = BD.OUID",
                   "ISNULL(OU.OUID, -1)", "ISNULL(OU.ORGANIZATIONUNITCODE, '')", "ISNULL(OU.ORGANIZATIONUNITNAME, '')"),
            [1] = ("LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = BD.OUID LEFT JOIN MCOMPANY AX ON OU.COMPANYID = AX.COMPANYID",
                   "ISNULL(AX.COMPANYID, -1)", "ISNULL(AX.COMPANYCODE, '')", "ISNULL(AX.COMPANYNAME, '')"),
            [2] = ("LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = BD.OUID LEFT JOIN MBRANCH AX ON OU.BRANCHID = AX.BRANCHID",
                   "ISNULL(AX.BRANCHID, -1)", "ISNULL(AX.BRANCHCODE, '')", "ISNULL(AX.BRANCHNAME, '')"),
            [3] = ("LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = BD.OUID LEFT JOIN MDIVISION AX ON OU.DIVISIONID = AX.DIVISIONID",
                   "ISNULL(AX.DIVISIONID, -1)", "ISNULL(AX.DIVISIONCODE, '')", "ISNULL(AX.DIVISIONNAME, '')"),
            [9] = ("", "-1", "'Total'", "'Total'"),
        };

        // Legacy's ViewLevel axis (0=Account,2=AccountGroup,3=AccountScheduleNature,
        // 4=AccountGroupNature,9=Overall) -- each entry supplies all ten Account*/AccountGroup*
        // output expressions at once, since choosing a level blanks every finer level below it
        // (e.g. AccountGroup level shows AccountGroupId/Code/Name/Nature but blanks AccountId/
        // Code/Name), matching legacy's own CASE-per-column structure exactly.
        private const string SCHEDULE_NATURE_INT =
            "ISNULL(CAST(" + AccountBudgetVsComparisonReportQB.ACCOUNT_SCHEDULE_NATURE_EXPR + " AS INT), -1)";
        private const string SCHEDULE_NATURE_NAME =
            "CASE " + AccountBudgetVsComparisonReportQB.ACCOUNT_SCHEDULE_NATURE_EXPR + @"
                WHEN 0 THEN 'Liability' WHEN 1 THEN 'Asset' WHEN 2 THEN 'Direct Income'
                WHEN 3 THEN 'Direct Expense' WHEN 4 THEN 'Indirect Income' WHEN 5 THEN 'Indirect Expense'
                ELSE '' END";
        private const string GROUP_NATURE_INT = "ISNULL(CAST(AG.ACCOUNTGROUPNATURE AS INT), -1)";
        private const string GROUP_NATURE_NAME =
            "CASE WHEN AG.ACCOUNTGROUPID IS NULL THEN '' ELSE (" + AccountBudgetVsComparisonReportQB.ACCOUNT_GROUP_NATURE_NAME_EXPR + ") END";

        private static readonly Dictionary<byte, (
            string AccountId, string AccountCode, string AccountName,
            string AccountGroupId, string AccountGroupCode, string AccountGroupName,
            string AccountScheduleNature, string AccountScheduleNatureName,
            string AccountGroupNature, string AccountGroupNatureName)> ViewLevelMap = new()
        {
            [0] = ("ISNULL(ACC.ACCOUNTID, -1)", "ISNULL(ACC.ACCOUNTCODE, '')", "ISNULL(ACC.ACCOUNTNAME, '')",
                   "ISNULL(AG.ACCOUNTGROUPID, -1)", "ISNULL(AG.ACCOUNTGROUPCODE, '')", "ISNULL(AG.ACCOUNTGROUPNAME, '')",
                   SCHEDULE_NATURE_INT, SCHEDULE_NATURE_NAME, GROUP_NATURE_INT, GROUP_NATURE_NAME),
            [2] = ("-1", "''", "''",
                   "ISNULL(AG.ACCOUNTGROUPID, -1)", "ISNULL(AG.ACCOUNTGROUPCODE, '')", "ISNULL(AG.ACCOUNTGROUPNAME, '')",
                   SCHEDULE_NATURE_INT, SCHEDULE_NATURE_NAME, GROUP_NATURE_INT, GROUP_NATURE_NAME),
            // ViewLevel=3 (AccountScheduleNature) -- legacy's own "in(0,1,2,3,4)" GroupNature guard
            // includes 3, so GroupNature stays real here even though GroupId/Code/Name don't
            // ("in(0,1,2)" excludes 3) -- only blank what legacy actually blanks at this level.
            [3] = ("-1", "''", "''", "-1", "''", "''",
                   SCHEDULE_NATURE_INT, SCHEDULE_NATURE_NAME, GROUP_NATURE_INT, GROUP_NATURE_NAME),
            [4] = ("-1", "''", "''", "-1", "''", "''",
                   "-1", "''", GROUP_NATURE_INT, GROUP_NATURE_NAME),
            [9] = ("-1", "''", "''", "-1", "''", "''", "-1", "''", "-1", "''"),
        };

        private readonly IQueryExecutor _queryExecutor;

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

        public async Task<List<BudgetVsComparisonDTO>> GetBudgetVsComparisonReport(
            int budgetId, byte type, byte viewLevel, byte ouAxis, byte accountNature,
            List<int>? ouIds, int ouGroupId, LoginDTO loginDTO, CancellationToken ct)
        {
            // Unlike P&L/Ratio/Receivable, this report has no accessible-OU allow-list of its own
            // today (it's scoped by BudgetId, not by a caller-chosen OU set) — so there's no ceiling
            // to intersect against here, just an optional narrowing filter on top of BD.OUID.
            var resolvedOuIds = await AccountReportFilterBuilder
                .ResolveOuIdsWithGroupAsync(_queryExecutor, loginDTO, ouIds, ouGroupId, ct)
                .ConfigureAwait(false);
            var dynamicOuFilter = resolvedOuIds is { Count: > 0 } ? " AND BD.OUID IN @OuIds" : "";

            if (type == 1)
            {
                if (!OuAxisMap.TryGetValue(ouAxis, out var axis)) axis = OuAxisMap[0];
                if (!ViewLevelMap.TryGetValue(viewLevel, out var level)) level = ViewLevelMap[0];

                // SQL Server rejects a GROUP BY expression that reduces to a bare literal constant
                // (e.g. OuAxis=9/"Overall" resolves OuId/OuCode/OuName to -1/'Total'/'Total') -- same
                // "Each GROUP BY expression must contain at least one column that is not an outer
                // reference" restriction already hit once for the P&L Dashboard's "Total" bucket. Every
                // SELECT column is wrapped in MIN(...) in the QB (always safe), and only the
                // placeholders that resolved to a REAL column expression for this axis/level go into
                // GROUP BY -- a value is "constant" here iff it's the literal -1 sentinel or a quoted
                // string literal, which is exactly (and only) how OuAxisMap/ViewLevelMap represent a
                // blanked-out field.
                static bool IsConstantExpr(string expr) =>
                    expr == "-1" || (expr.Length >= 2 && expr[0] == '\'' && expr[^1] == '\'');

                var groupByCandidates = new[]
                {
                    axis.Id, axis.Code, axis.Name,
                    level.AccountGroupNature, level.AccountGroupNatureName,
                    level.AccountScheduleNature, level.AccountScheduleNatureName,
                    level.AccountGroupId, level.AccountGroupCode, level.AccountGroupName,
                    level.AccountId, level.AccountCode, level.AccountName,
                };
                var realGroupByCols = groupByCandidates.Where(c => !IsConstantExpr(c)).ToList();
                var groupByColumns = realGroupByCols.Count > 0 ? string.Join(", ", realGroupByCols) : "()";

                var pivotSql = AccountBudgetVsComparisonReportQB.GET_PIVOT_COMPARISON
                    .Replace("{OuAxisJoin}", axis.Join)
                    .Replace("{OuId}", axis.Id).Replace("{OuCode}", axis.Code).Replace("{OuName}", axis.Name)
                    .Replace("{AccountId}", level.AccountId).Replace("{AccountCode}", level.AccountCode).Replace("{AccountName}", level.AccountName)
                    .Replace("{AccountGroupId}", level.AccountGroupId).Replace("{AccountGroupCode}", level.AccountGroupCode).Replace("{AccountGroupName}", level.AccountGroupName)
                    .Replace("{AccountScheduleNature}", level.AccountScheduleNature).Replace("{AccountScheduleNatureName}", level.AccountScheduleNatureName)
                    .Replace("{AccountGroupNature}", level.AccountGroupNature).Replace("{AccountGroupNatureName}", level.AccountGroupNatureName)
                    .Replace("{GroupByColumns}", groupByColumns)
                    .Replace("{DynamicOuFilter}", dynamicOuFilter);

                var pivotRows = await _queryExecutor
                    .QueryAsync<BudgetVsComparisonDTO>(loginDTO, pivotSql,
                        new { BudgetId = budgetId, AccountNature = accountNature, OuIds = resolvedOuIds }, cancellationToken: ct)
                    .ConfigureAwait(false);

                return pivotRows.ToList();
            }

            var accountLevelSql = AccountBudgetVsComparisonReportQB.GET_ACCOUNT_LEVEL_COMPARISON
                .Replace("{DynamicOuFilter}", dynamicOuFilter);
            var rows = await _queryExecutor
                .QueryAsync<BudgetVsComparisonDTO>(loginDTO, accountLevelSql,
                    new { BudgetId = budgetId, OuIds = resolvedOuIds }, cancellationToken: ct)
                .ConfigureAwait(false);

            return rows.ToList();
        }
    }
}