using AnalyticsDAL.DTO.Analysis;
using AnalyticsDAL.DTO.BaseFrame;
using AnalyticsDAL.DTO.DBJoin;
using AnalyticsDAL.DTO.ExecutionRouting;
using AnalyticsDAL.Query.Analysis;
using AnalyticsDAL.Query.AnalysisFields;
using Dapper;
using GB5Shared.DALCache;
using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.DBObject;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Report;
using GB5Shared.Extensions;
using GB5Shared.GB5CommonFunction;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.Telemetry;
using GB5Shared.Validation;
using Microsoft.Extensions.Logging;
using SwBLL.Provisioning;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.Json;
using static GB5Shared.GB5Constant.Constant;

namespace AnalyticsDAL.CustomCode.Analysis
{
    public class AnalysisDAL : IAnalysisDAL
    {
        private readonly IQueryExecutor _queryexecutor;
        private readonly IGB5CommonFunction _gb5Common;
        private readonly ILogger<AnalysisDAL> _logger;
        private readonly IValidation _Validation;
        private readonly IDALCache _dalCache;
        private readonly ITargetDbExecutor _targetDbExecutor;
        private readonly CacheKeyGeneration _keyGen = new();

        public AnalysisDAL(
            IQueryExecutor queryExecutor,
            IGB5CommonFunction gb5Common,
            ILogger<AnalysisDAL> logger,
            IValidation validation,
            IDALCache dalCache,
            ITargetDbExecutor targetDbExecutor)
        {
            _queryexecutor = queryExecutor;
            _gb5Common = gb5Common;
            _logger = logger;
            _Validation = validation;
            _dalCache = dalCache;
            _targetDbExecutor = targetDbExecutor;
        }

        // ─────────────────────────────────────────────────────────────────────
        // Public interface
        // ─────────────────────────────────────────────────────────────────────

        public async Task<int?> GetAnalysisTenantId(int analysisId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryexecutor.QueryAsync<int>(
                login,
                AnalysisQB.GET_ANALYSIS_TENANTID,
                new { AnalysisId = analysisId },
                cancellationToken: ct).ConfigureAwait(false);

            return rows.Select(r => (int?)r).FirstOrDefault();
        }

        public async Task<int?> GetAnalysisIdForQuery(int analysisQueryId, LoginDTO login, CancellationToken ct)
        {
            var rows = await _queryexecutor.QueryAsync<int>(
                login,
                AnalysisQB.GET_ANALYSISID_FOR_QUERY,
                new { AnalysisQueryId = analysisQueryId },
                cancellationToken: ct).ConfigureAwait(false);

            return rows.Select(r => (int?)r).FirstOrDefault();
        }

        public async Task<AnalysisDTO?> GetAnalysisHeader(int analysisId,LoginDTO login,CancellationToken ct)
        {
            return await _queryexecutor
                .QuerySingleAsync<AnalysisDTO>(
                    login,
                    AnalysisQB.GET_ANALYSIS_ID,
                    new
                    {
                        AnalysisId = analysisId,
                        TenantId = login.ClientId
                    },
                    cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task<IEnumerable<AnalysisObjectDTO>> GetAnalysisObjects(int analysisId,LoginDTO login,CancellationToken ct)
        {
            return await _queryexecutor
                .QueryAsync<AnalysisObjectDTO>(
                    login,
                    AnalysisQB.GET_ANALYSIS_OBJECTS,
                    new
                    {
                        AnalysisId = analysisId,
                        TenantId = login.ClientId
                    },
                    cancellationToken: ct)
                .ConfigureAwait(false)
                ?? [];
        }

        public async Task<IEnumerable<AnalysisFieldsDTO>> GetAnalysisFields(int analysisId,LoginDTO login,CancellationToken ct)
        {
            return await _queryexecutor
                .QueryAsync<AnalysisFieldsDTO>(
                    login,
                    AnalysisQB.GET_ANALYSIS_FIELDS,
                    new
                    {
                        AnalysisId = analysisId,
                        TenantId = login.ClientId
                    },
                    cancellationToken: ct)
                .ConfigureAwait(false)
                ?? [];
        }

        public async Task<string> GetSelectListAnalysis(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            try
            {
                var result = await _queryexecutor.QueryAsync<AnalysisPicklistDTO>(
                    login,
                    AnalysisQB.GET_SELECTLIST_ANALYSIS,
                    new { firstnumber = firstNumber, maxresult = maxResult },
                    cancellationToken: ct).ConfigureAwait(false);

                return JsonSerializer.Serialize(result);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "GetSelectListAnalysis failed");
                throw;
            }
        }

        public async Task<List<Dictionary<string, object>>> DynamicOutput(int analysisQueryId, int reportViewId, int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct, ExecutionRouteDTO? route = null)
        {
            try
            {
                var aqKey = _keyGen.KeyGeneration(analysisQueryId, EntityConstant.OBJECTANALYSISQUERY, CacheKeyLevel.CLIENT_LEVEL, login);
                var query = await _dalCache.GetOrSetAsync<AnalysisQueryDTO>(
                    aqKey,
                    async innerCt => await _queryexecutor.QuerySingleAsync<AnalysisQueryDTO>(
                        login,
                        AnalysisQB.GET_ANALYSISQUERY,
                        new { analysisqueryid = analysisQueryId, TenantId = login.ClientId },
                        cancellationToken: innerCt).ConfigureAwait(false),
                    DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                    ct).ConfigureAwait(false);

                // External routing (SqlWorkbench-managed client DB): a self-contained separate
                // method, deliberately NOT sharing code with the local-execution path below —
                // this method is a large, heavily-tuned, already-in-production query engine, and
                // duplicating steps 1-5 here (rather than extracting/refactoring the tested body)
                // eliminates any regression risk to the local path, which is what 100% of current
                // production traffic uses. route is null for every caller that existed before this
                // parameter was added, so the local path below is completely unchanged.
                if (route is { IsExternal: true })
                {
                    return await DynamicOutputExternal(
                        analysisQueryId, reportViewId, firstNumber, maxResult, criteriaDTO, login, query, route, ct)
                        .ConfigureAwait(false);
                }

                // Use an explicit DbTransaction so every step in this method —
                // TVF temp tables in DynamicQuery, SELECT INTO #dynamicanalysisQuery,
                // and the final SELECT — all run on the exact same physical connection.
                // HttpContext-based session methods cannot guarantee this across async
                // continuations, which caused "Invalid object name '#dynamicanalysisQuery'".
                var tran = await _queryexecutor.BeginTransactionAsync(login).ConfigureAwait(false);
                try
                {
                    string sql;
                    var filterDynParams = new DynamicParameters();

                    // ── Step 1: Build the main SELECT-INTO SQL ────────────────────────
                    // Child span ends (and exports) as soon as SQL-building finishes —
                    // visible in Zipkin well before the query itself executes.
                    var buildQuerySection = GB5Trace.BeginSection("analysis:build-query");
                    try
                    {
                        if (query.AnalysisQueryType == AnalysisQueryType.SELECTION_BASED)
                        {
                            sql = await DynamicQuery(analysisQueryId, criteriaDTO, login, reportViewId, tran, ct).ConfigureAwait(false);
                        }
                        else if (query.AnalysisQueryType == AnalysisQueryType.DIRECT)
                        {
                            if (string.IsNullOrWhiteSpace(query.AnalysisQuery))
                                throw new InvalidOperationException("Please provide query.");
                            sql = query.AnalysisQuery;
                        }
                        else if (query.AnalysisQueryType == AnalysisQueryType.MDX)
                        {
                            throw new NotSupportedException("MDX query type is not supported.");
                        }
                        else
                        {
                            throw new InvalidOperationException($"Unknown AnalysisQueryType: {query.AnalysisQueryType}");
                        }
                    }
                    finally
                    {
                        buildQuerySection?.Dispose();
                    }

                    // ── Step 2: Load allQueryFields (single load, reused below) ──────

                    // FIX #10: Load once here; reused for FieldType==2 dynamic column
                    // expansion below — eliminates the second identical DB round-trip.
                    var queryFieldParams = new { analysisqueryid = analysisQueryId, reportviewid = reportViewId, TenantId = login.ClientId };
                    var aqfKey = _keyGen.KeyGeneration($"{analysisQueryId}_{reportViewId}", EntityConstant.OBJECTANALYSISQUERYFIELDS, CacheKeyLevel.CLIENT_LEVEL, login);
                    var allQueryFields = (await _dalCache.GetOrSetAsync<List<DynamicAnalysisQueryDTO>>(
                        aqfKey,
                        async innerCt => (await _queryexecutor.QueryAsync<DynamicAnalysisQueryDTO>(
                            login, AnalysisQB.GET_ANALYSISQUERY_FIELDS, queryFieldParams, cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                        DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                        ct).ConfigureAwait(false)) ?? [];

                    // ── Step 3: Build filter conditions ──────────────────────────────

                    var buildFiltersSection = GB5Trace.BeginSection("analysis:build-filters");
                    try
                    {
                    if (criteriaDTO?.SectionCriteriaList?.Count > 0)
                    {
                        // FIX #13: DynamicParameters filters only apply to type=1.
                        // type=0 uses template substitution (single-quote escaped).
                        if (query.AnalysisQueryType == AnalysisQueryType.SELECTION_BASED)
                        {
                            bool isFilterRequired = !allQueryFields.Any(f => f.Periodtype != 0);

                            var afKey = _keyGen.KeyGeneration(analysisQueryId, EntityConstant.OBJECTANALYSISFILTER, CacheKeyLevel.CLIENT_LEVEL, login);
                            var filterFields = (await _dalCache.GetOrSetAsync<List<DynamicAnalysisQueryDTO>>(
                                afKey,
                                async innerCt => (await _queryexecutor.QueryAsync<DynamicAnalysisQueryDTO>(
                                    login, AnalysisQB.GET_ANALYSISFILTER_FIELDS,
                                    new { analysisqueryid = analysisQueryId, TenantId = login.ClientId },
                                    cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                                DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                                ct).ConfigureAwait(false)) ?? [];

                            var conditionBuilder = new StringBuilder(); // FIX: StringBuilder, not +=
                            var criteriaList = criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList;

                            for (int i = 0; i < criteriaList.Count; i++)
                            {
                                var attr = criteriaList[i];
                                string criteriaFieldName = attr.FieldName.ToLower();

                                for (int j = 0; j < filterFields.Count; j++)
                                {
                                    if (criteriaFieldName != filterFields[j].DbFieldName.ToLower()) continue;
                                    if (!isFilterRequired && filterFields[j].CriteriaAttributeType == 4) continue;

                                    string filterExpr = filterFields[j].FilterCondition;
                                    string paramKey = $"fp_{i}_{j}";
                                    object paramValue;

                                    // FIX #5: IN/NOT IN use InArray for proper Dapper list expansion.
                                    // When the client leaves InArray empty and packs the list into
                                    // FieldValue as a comma-separated string instead, split it —
                                    // otherwise Dapper binds the whole string as a single IN value
                                    // and SQL Server fails converting it to the column's type.
                                    if (attr.OperationType is CriteriaDTO.OperationType.In or CriteriaDTO.OperationType.NotIn)
                                    {
                                        paramValue = attr.InArray?.Length > 0
                                            ? attr.InArray.Select(v => Convert.ToString(v) ?? "").ToArray()
                                            : (Convert.ToString(attr.FieldValue) ?? "")
                                                .Split(',', StringSplitOptions.TrimEntries | StringSplitOptions.RemoveEmptyEntries);
                                    }
                                    else if (attr.CriteriaAttributeType == 4) // date
                                    {
                                        var element = (JsonElement)attr.FieldValue;
                                        string rawVal = element.ValueKind == JsonValueKind.String
                                            ? element.GetString()!
                                            : element.GetRawText();
                                        paramValue = ParseEpochOrDate(rawVal);
                                    }
                                    else
                                    {
                                        paramValue = Convert.ToString(attr.FieldValue) ?? "";
                                    }

                                    // FIX #5: IN/NOT IN use @param without parens — Dapper expands IEnumerable
                                    string condition = attr.OperationType switch
                                    {
                                        CriteriaDTO.OperationType.Equal => $" and {filterExpr} = @{paramKey}",
                                        CriteriaDTO.OperationType.NotEqual => $" and {filterExpr} <> @{paramKey}",
                                        CriteriaDTO.OperationType.GreaterThan => $" and {filterExpr} > @{paramKey}",
                                        CriteriaDTO.OperationType.LessThan => $" and {filterExpr} < @{paramKey}",
                                        CriteriaDTO.OperationType.GreaterThanOrEqualTo => $" and {filterExpr} >= @{paramKey}",
                                        CriteriaDTO.OperationType.LessThanOrEqualTo => $" and {filterExpr} <= @{paramKey}",
                                        CriteriaDTO.OperationType.In => $" and {filterExpr} IN @{paramKey}",
                                        CriteriaDTO.OperationType.NotIn => $" and {filterExpr} NOT IN @{paramKey}",
                                        CriteriaDTO.OperationType.Like => $" and {filterExpr} LIKE @{paramKey}",
                                        CriteriaDTO.OperationType.StartWith => $" and {filterExpr} LIKE @{paramKey}",
                                        CriteriaDTO.OperationType.EndsWith => $" and {filterExpr} LIKE @{paramKey}",
                                        _ => ""
                                    };

                                    if (condition == "") continue;

                                    if (attr.OperationType == CriteriaDTO.OperationType.Like)
                                        paramValue = $"%{Convert.ToString(paramValue)}%";
                                    else if (attr.OperationType == CriteriaDTO.OperationType.StartWith)
                                        paramValue = $"{Convert.ToString(paramValue)}%";
                                    else if (attr.OperationType == CriteriaDTO.OperationType.EndsWith)
                                        paramValue = $"%{Convert.ToString(paramValue)}";

                                    filterDynParams.Add(paramKey, paramValue);
                                    conditionBuilder.Append(condition).Append("\r\n");
                                }
                            }

                            sql = sql.Replace("@dynamicfiltercondition", conditionBuilder.ToString());
                        }
                        else // DIRECT — type=0: template substitution with escaping
                        {
                            // FIX #1: Single quotes in string values escaped to prevent injection.
                            // Dates and numbers are safe by construction (formatting / epoch conversion).
                            // TODO: Route through SqlWorkbench safe executor to fully eliminate risk
                            //       for non-numeric string parameters.
                            sql = await ApplyTemplateSubstitution(sql, criteriaDTO).ConfigureAwait(false);
                        }
                    }
                    }
                    finally
                    {
                        buildFiltersSection?.Dispose();
                    }

                    // Clear any remaining @dynamicfiltercondition placeholder (type=0 or no criteria)
                    sql = sql.Replace("@dynamicfiltercondition", "");

                    // ── Step 4 (prep): Build the final SELECT from temp table ─────────
                    // FIX #10: Reuse allQueryFields loaded in step 2
                    var dynamicFieldDTOs = allQueryFields.Where(f => f.FieldType == 2).ToList();
                    string finalSql = BuildFinalSql(dynamicFieldDTOs, firstNumber, maxResult);

                    // ── Step 5 (prep): Load report view field definitions ──────────────
                    // Read-only lookup — no transaction needed, uses its own connection.
                    var rvKey = _keyGen.KeyGeneration(reportViewId, EntityConstant.OBJECTREPORTVIEW, CacheKeyLevel.CLIENT_LEVEL, login);
                    var reportViewFields = (await _dalCache.GetOrSetAsync<List<ReportViewFieldsDTO>>(
                        rvKey,
                        async innerCt => (await _queryexecutor.QueryAsync<ReportViewFieldsDTO>(
                            login, AnalysisQB.GET_REPORTVIEW_VISIBLE_FIELDS_BASED_ON_REPORTVIEWID,
                            new { reportviewid = reportViewId }, cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                        DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                        ct).ConfigureAwait(false)) ?? [];

                    // ── Steps 4+6: SELECT INTO then SELECT FROM in a single SQL batch ──
                    // Both statements run in one batch on tran.Connection — the same SQL
                    // Server session that holds any TVF temp tables created in DynamicQuery.
                    // SELECT INTO produces no result set; Dapper automatically reads the
                    // rows from the SELECT * that follows in the same batch.
                    // Two separate IQueryExecutor calls — even on the same DbTransaction —
                    // were landing on different SQL Server sessions, causing
                    // "Invalid object name '#dynamicanalysisQuery'".
                    string combinedSql = sql + "\r\n;\r\n" + finalSql;

                    // The one real DB round-trip that returns data — this span ends the moment
                    // the query finishes, independent of the PDF/Excel/CSV conversion that follows
                    // it back up in BaseEndpoint/ReportExport, so "query done" is visible in Zipkin
                    // well before the whole export request completes.
                    List<dynamic> dynamicRows;
                    var executeQuerySection = GB5Trace.BeginSection("analysis:execute-query");
                    try
                    {
                        dynamicRows = (await _queryexecutor.QueryAsync<dynamic>(
                            login, combinedSql, filterDynParams, tran, cancellationToken: ct).ConfigureAwait(false)).ToList();
                        executeQuerySection?.SetTag("gb5.rows.count", dynamicRows.Count);
                    }
                    finally
                    {
                        executeQuerySection?.Dispose();
                    }

                    var finalObject = new List<Dictionary<string, object>>(dynamicRows.Count);
                    var projectRowsSection = GB5Trace.BeginSection("analysis:project-rows");
                    try
                    {
                        foreach (IDictionary<string, object> rowDict in dynamicRows)
                        {
                            var row = new Dictionary<string, object>(reportViewFields.Count);
                            foreach (var field in reportViewFields)
                                row[field.ReportVsFieldsFieldName] = rowDict.TryGetValue(field.ReportVsFieldsFieldName, out var v) ? (v ?? "") : "";
                            finalObject.Add(row);
                        }
                    }
                    finally
                    {
                        projectRowsSection?.Dispose();
                    }

                    await _queryexecutor.CommitAsync(tran).ConfigureAwait(false);
                    return finalObject;
                }
                catch
                {
                    await _queryexecutor.RollbackAsync(tran).ConfigureAwait(false);
                    throw;
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "DynamicOutput failed for AnalysisQueryId {Id}", analysisQueryId);
                throw;
            }
        }

        // External-execution counterpart of DynamicOutput's local path. Deliberately duplicates
        // steps 1-5 of the local path (rather than sharing code via extraction) to keep the
        // heavily-tuned, already-in-production local path above completely untouched — see the
        // comment at DynamicOutput's route branch for the reasoning. Runs no local transaction:
        // there is no local temp table dependency, since the whole SELECT INTO #temp / SELECT
        // sequence (plus any TVF dumps) is folded into ONE batch string and executed as a single
        // command on the external connection via ITargetDbExecutor.ExecuteQueryBatchAsync.
        private async Task<List<Dictionary<string, object>>> DynamicOutputExternal(
            int analysisQueryId, int reportViewId, int firstNumber, int maxResult,
            CriteriaDTO criteriaDTO, LoginDTO login, AnalysisQueryDTO query, ExecutionRouteDTO route, CancellationToken ct)
        {
            try
            {
                string sql;
                var filterDynParams = new DynamicParameters();
                var tvfStatements = new List<string>();

                // ── Step 1: Build the main SELECT-INTO SQL (TVF dumps collected, not executed) ──
                if (query.AnalysisQueryType == AnalysisQueryType.SELECTION_BASED)
                {
                    sql = await DynamicQuery(analysisQueryId, criteriaDTO, login, reportViewId, tran: null, ct, tvfCollector: tvfStatements).ConfigureAwait(false);
                }
                else if (query.AnalysisQueryType == AnalysisQueryType.DIRECT)
                {
                    if (string.IsNullOrWhiteSpace(query.AnalysisQuery))
                        throw new InvalidOperationException("Please provide query.");
                    sql = query.AnalysisQuery;
                }
                else if (query.AnalysisQueryType == AnalysisQueryType.MDX)
                {
                    throw new NotSupportedException("MDX query type is not supported.");
                }
                else
                {
                    throw new InvalidOperationException($"Unknown AnalysisQueryType: {query.AnalysisQueryType}");
                }

                // ── Step 2: Load allQueryFields (single load, reused below) ──────
                var queryFieldParams = new { analysisqueryid = analysisQueryId, reportviewid = reportViewId, TenantId = login.ClientId };
                var aqfKey = _keyGen.KeyGeneration($"{analysisQueryId}_{reportViewId}", EntityConstant.OBJECTANALYSISQUERYFIELDS, CacheKeyLevel.CLIENT_LEVEL, login);
                var allQueryFields = (await _dalCache.GetOrSetAsync<List<DynamicAnalysisQueryDTO>>(
                    aqfKey,
                    async innerCt => (await _queryexecutor.QueryAsync<DynamicAnalysisQueryDTO>(
                        login, AnalysisQB.GET_ANALYSISQUERY_FIELDS, queryFieldParams, cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                    DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                    ct).ConfigureAwait(false)) ?? [];

                // ── Step 3: Build filter conditions ──────────────────────────────
                if (criteriaDTO?.SectionCriteriaList?.Count > 0)
                {
                    if (query.AnalysisQueryType == AnalysisQueryType.SELECTION_BASED)
                    {
                        bool isFilterRequired = !allQueryFields.Any(f => f.Periodtype != 0);

                        var afKey = _keyGen.KeyGeneration(analysisQueryId, EntityConstant.OBJECTANALYSISFILTER, CacheKeyLevel.CLIENT_LEVEL, login);
                        var filterFields = (await _dalCache.GetOrSetAsync<List<DynamicAnalysisQueryDTO>>(
                            afKey,
                            async innerCt => (await _queryexecutor.QueryAsync<DynamicAnalysisQueryDTO>(
                                login, AnalysisQB.GET_ANALYSISFILTER_FIELDS,
                                new { analysisqueryid = analysisQueryId, TenantId = login.ClientId },
                                cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                            DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                            ct).ConfigureAwait(false)) ?? [];

                        var conditionBuilder = new StringBuilder();
                        var criteriaList = criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList;

                        for (int i = 0; i < criteriaList.Count; i++)
                        {
                            var attr = criteriaList[i];
                            string criteriaFieldName = attr.FieldName.ToLower();

                            for (int j = 0; j < filterFields.Count; j++)
                            {
                                if (criteriaFieldName != filterFields[j].DbFieldName.ToLower()) continue;
                                if (!isFilterRequired && filterFields[j].CriteriaAttributeType == 4) continue;

                                string filterExpr = filterFields[j].FilterCondition;
                                string paramKey = $"fp_{i}_{j}";
                                object paramValue;

                                if (attr.OperationType is CriteriaDTO.OperationType.In or CriteriaDTO.OperationType.NotIn)
                                {
                                    paramValue = attr.InArray?.Length > 0
                                        ? attr.InArray.Select(v => Convert.ToString(v) ?? "").ToArray()
                                        : (Convert.ToString(attr.FieldValue) ?? "")
                                            .Split(',', StringSplitOptions.TrimEntries | StringSplitOptions.RemoveEmptyEntries);
                                }
                                else if (attr.CriteriaAttributeType == 4) // date
                                {
                                    var element = (JsonElement)attr.FieldValue;
                                    string rawVal = element.ValueKind == JsonValueKind.String
                                        ? element.GetString()!
                                        : element.GetRawText();
                                    paramValue = ParseEpochOrDate(rawVal);
                                }
                                else
                                {
                                    paramValue = Convert.ToString(attr.FieldValue) ?? "";
                                }

                                string condition = attr.OperationType switch
                                {
                                    CriteriaDTO.OperationType.Equal => $" and {filterExpr} = @{paramKey}",
                                    CriteriaDTO.OperationType.NotEqual => $" and {filterExpr} <> @{paramKey}",
                                    CriteriaDTO.OperationType.GreaterThan => $" and {filterExpr} > @{paramKey}",
                                    CriteriaDTO.OperationType.LessThan => $" and {filterExpr} < @{paramKey}",
                                    CriteriaDTO.OperationType.GreaterThanOrEqualTo => $" and {filterExpr} >= @{paramKey}",
                                    CriteriaDTO.OperationType.LessThanOrEqualTo => $" and {filterExpr} <= @{paramKey}",
                                    CriteriaDTO.OperationType.In => $" and {filterExpr} IN @{paramKey}",
                                    CriteriaDTO.OperationType.NotIn => $" and {filterExpr} NOT IN @{paramKey}",
                                    CriteriaDTO.OperationType.Like => $" and {filterExpr} LIKE @{paramKey}",
                                    CriteriaDTO.OperationType.StartWith => $" and {filterExpr} LIKE @{paramKey}",
                                    CriteriaDTO.OperationType.EndsWith => $" and {filterExpr} LIKE @{paramKey}",
                                    _ => ""
                                };

                                if (condition == "") continue;

                                if (attr.OperationType == CriteriaDTO.OperationType.Like)
                                    paramValue = $"%{Convert.ToString(paramValue)}%";
                                else if (attr.OperationType == CriteriaDTO.OperationType.StartWith)
                                    paramValue = $"{Convert.ToString(paramValue)}%";
                                else if (attr.OperationType == CriteriaDTO.OperationType.EndsWith)
                                    paramValue = $"%{Convert.ToString(paramValue)}";

                                filterDynParams.Add(paramKey, paramValue);
                                conditionBuilder.Append(condition).Append("\r\n");
                            }
                        }

                        sql = sql.Replace("@dynamicfiltercondition", conditionBuilder.ToString());
                    }
                    else // DIRECT — type=0: template substitution with escaping
                    {
                        sql = await ApplyTemplateSubstitution(sql, criteriaDTO).ConfigureAwait(false);
                    }
                }

                sql = sql.Replace("@dynamicfiltercondition", "");

                // ── Step 4: Build the final SELECT from temp table ─────────
                var dynamicFieldDTOs = allQueryFields.Where(f => f.FieldType == 2).ToList();
                string finalSql = BuildFinalSql(dynamicFieldDTOs, firstNumber, maxResult);

                // ── Step 5: Load report view field definitions ──────────────
                var rvKey = _keyGen.KeyGeneration(reportViewId, EntityConstant.OBJECTREPORTVIEW, CacheKeyLevel.CLIENT_LEVEL, login);
                var reportViewFields = (await _dalCache.GetOrSetAsync<List<ReportViewFieldsDTO>>(
                    rvKey,
                    async innerCt => (await _queryexecutor.QueryAsync<ReportViewFieldsDTO>(
                        login, AnalysisQB.GET_REPORTVIEW_VISIBLE_FIELDS_BASED_ON_REPORTVIEWID,
                        new { reportviewid = reportViewId }, cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                    DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                    ct).ConfigureAwait(false)) ?? [];

                // ── Fold every TVF dump + main SELECT-INTO + final SELECT into ONE batch ──
                var batchBuilder = new StringBuilder();
                foreach (var tvfSql in tvfStatements)
                    batchBuilder.Append(tvfSql).Append("\r\n;\r\n");
                batchBuilder.Append(sql).Append("\r\n;\r\n").Append(finalSql);

                var paramDict = new Dictionary<string, object?>();
                foreach (var paramName in filterDynParams.ParameterNames)
                    paramDict[paramName] = filterDynParams.Get<dynamic?>(paramName);

                var rows = await _targetDbExecutor.ExecuteQueryBatchAsync(
                    route.ConnectionString!, batchBuilder.ToString(), paramDict, rowCapOverride: null, ct).ConfigureAwait(false);

                var finalObject = new List<Dictionary<string, object>>();
                foreach (var rowDict in rows)
                {
                    var row = new Dictionary<string, object>(reportViewFields.Count);
                    foreach (var field in reportViewFields)
                        row[field.ReportVsFieldsFieldName] = rowDict.TryGetValue(field.ReportVsFieldsFieldName, out var v) ? (v ?? "") : "";
                    finalObject.Add(row);
                }
                return finalObject;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "DynamicOutputExternal failed for AnalysisQueryId {Id}", analysisQueryId);
                throw;
            }
        }

        // ─────────────────────────────────────────────────────────────────────
        // Private helpers
        // ─────────────────────────────────────────────────────────────────────

        // Builds the final SELECT from #dynamicanalysisQuery, including dynamic columns and paging.
        // FIX #6: firstNumber/maxResult now applied via OFFSET/FETCH.
        private static string BuildFinalSql(List<DynamicAnalysisQueryDTO> dynamicFields, int firstNumber, int maxResult)
        {
            string sql = AnalysisQB.FINAL_DYNAMIC_QUERY;

            if (dynamicFields.Count == 0)
            {
                sql = sql.Replace(":otherfields", "");
            }
            else
            {
                var sb = new StringBuilder(",");
                for (int i = 0; i < dynamicFields.Count; i++)
                {
                    sb.Append(dynamicFields[i].Exression)
                      .Append(" as ")
                      .Append(dynamicFields[i].DisplayName);
                    if (i < dynamicFields.Count - 1) sb.Append(',');
                }
                sql = sql.Replace(":otherfields", sb.ToString());
            }

            // -1/-1 is the "return all" sentinel (matches GET_SELECTLIST_ANALYSIS convention)
            if (firstNumber > 0 && maxResult >= firstNumber && firstNumber != -1 && maxResult != -1)
            {
                int skip = firstNumber - 1;
                int take = maxResult - firstNumber + 1;
                sql += $"\r\nORDER BY (SELECT NULL)\r\nOFFSET {skip} ROWS FETCH NEXT {take} ROWS ONLY";
            }

            return sql;
        }

        // Builds the SELECT ... INTO #dynamicanalysisQuery SQL for type=1 (Selection Based) queries.
        // tran must be the same transaction used for the later ExecuteAsync (SELECT INTO) so that
        // TVF temp tables created here are visible on the same physical connection.
        // Table and column expressions come from admin-controlled MANALYSISOBJECT/MANALYSISFIELDS —
        // not from user input — so structural template substitution is safe here.
        // tvfCollector: when null (the default, used by the local-execution path), a
        // table-valued-function's dump SQL is executed immediately via tran, exactly as before
        // this parameter was added. When non-null (the external-execution path, which has no
        // local tran at all), the dump SQL is appended to tvfCollector instead of being executed
        // here — the caller folds it into one batch sent to the external connection.
        private async Task<string> DynamicQuery(int analysisQueryId, CriteriaDTO criteriaDTO, LoginDTO login, int reportViewId, System.Data.Common.DbTransaction? tran, CancellationToken ct, List<string>? tvfCollector = null)
        {
            try
            {
                var queryFieldParams = new { analysisqueryid = analysisQueryId, reportviewid = reportViewId, TenantId = login.ClientId };
                var dqAqfKey = _keyGen.KeyGeneration($"{analysisQueryId}_{reportViewId}", EntityConstant.OBJECTANALYSISQUERYFIELDS, CacheKeyLevel.CLIENT_LEVEL, login);
                var queryDTOs = (await _dalCache.GetOrSetAsync<List<DynamicAnalysisQueryDTO>>(
                    dqAqfKey,
                    async innerCt => (await _queryexecutor.QueryAsync<DynamicAnalysisQueryDTO>(
                        login, AnalysisQB.GET_ANALYSISQUERY_FIELDS, queryFieldParams, cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                    DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                    ct).ConfigureAwait(false)) ?? [];

                var resolvePeriodSection = GB5Trace.BeginSection("analysis:resolve-period-dates");
                try
                {
                    queryDTOs = await ReformDynamicQueryDTO(analysisQueryId, queryDTOs, criteriaDTO, login, ct).ConfigureAwait(false);
                }
                finally
                {
                    resolvePeriodSection?.Dispose();
                }

                if (queryDTOs.Count == 0)
                    throw new InvalidOperationException("No analysis query fields found. Check analysis configuration.");

                var tableParams = new { analysisid = queryDTOs[0].AnalysisId, TenantId = login.ClientId };
                var tdKey = _keyGen.KeyGeneration(queryDTOs[0].AnalysisId, EntityConstant.OBJECTANALYSIS, CacheKeyLevel.CLIENT_LEVEL, login);
                var dbObjects = (await _dalCache.GetOrSetAsync<List<DBObjectDTO>>(
                    tdKey,
                    async innerCt => (await _queryexecutor.QueryAsync<DBObjectDTO>(
                        login, AnalysisQB.GET_TABLE_DETAILS_BASED_ON_ANALYSISID_WITH_SAME_TABLE_MULTIPLE_TIME, tableParams, cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                    DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                    ct).ConfigureAwait(false)) ?? [];

                // FIX #10: joins are only ever looked up between pairs within dbObjects (the nested
                // loop below never runs when dbObjects.Count < 2), and GET_DBJOIN_BASED_ON_FROMDBOBJECTID
                // used to have no WHERE clause at all — a full unfiltered DBJOIN table scan (thousands
                // of rows) on every cache miss, even for a single-object/materialized-view analysis that
                // needs zero joins. Skip the fetch entirely when no join is even possible, and otherwise
                // filter by this analysis's actual DBObjectIds instead of loading the whole table.
                List<DBJoinDTO> allJoins = [];
                if (dbObjects.Count >= 2)
                {
                    var dbObjectIds = dbObjects.Select(o => o.DBObjectId).Distinct().ToList();
                    var djKey = _keyGen.KeyGeneration(queryDTOs[0].AnalysisId, EntityConstant.OBJECTDBJOIN, CacheKeyLevel.CLIENT_LEVEL, login);
                    allJoins = (await _dalCache.GetOrSetAsync<List<DBJoinDTO>>(
                        djKey,
                        async innerCt => (await _queryexecutor.QueryAsync<DBJoinDTO>(
                            login, AnalysisQB.GET_DBJOIN_BASED_ON_FROMDBOBJECTID, new { DbObjectIds = dbObjectIds }, cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                        DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                        ct).ConfigureAwait(false)) ?? [];
                }

                // ── Build FROM clause and JOIN conditions ──────────────────────────
                // Can issue extra DB round-trips materializing TVF temp tables, so it gets its
                // own span rather than being lumped into the outer "build-query" section.

                string dynamicCondition = "";
                var dynamicFromBuilder = new StringBuilder();
                var indirectTables = new HashSet<string>(StringComparer.OrdinalIgnoreCase);

                var buildJoinsSection = GB5Trace.BeginSection("analysis:build-joins");
                try
                {
                for (int k = 0; k < dbObjects.Count; k++)
                {
                    string objName = dbObjects[k].DBObjectName;

                    if (objName.Length > 2 && objName[..2].Equals("fn", StringComparison.OrdinalIgnoreCase))
                    {
                        // Table-valued function — materialize into a temp table.
                        // ExecuteAsync with tran ensures it lands on the same physical connection
                        // used for the subsequent SELECT INTO #dynamicanalysisQuery.
                        string funcName = objName.Split('(')[0];
                        string tempName = "#" + funcName;
                        string substituted = await ApplyTemplateSubstitution(objName, criteriaDTO).ConfigureAwait(false);
                        string tempDumpSql = $"SELECT * INTO {tempName} FROM {substituted}";
                        if (tvfCollector != null)
                            tvfCollector.Add(tempDumpSql);
                        else
                            await _queryexecutor.ExecuteAsync(login, tempDumpSql, null!, tran).ConfigureAwait(false);
                        dynamicFromBuilder.Append(',').Append(tempName).Append(" as ").Append(funcName);
                    }
                    else
                    {
                        dynamicFromBuilder.Append(',');
                        if (dbObjects[k].ParentObjectName.Equals("NONE", StringComparison.OrdinalIgnoreCase))
                            dynamicFromBuilder.Append(objName);
                        else
                            dynamicFromBuilder.Append(dbObjects[k].ParentObjectName).Append(' ').Append(objName);
                    }

                    if (!string.IsNullOrEmpty(dbObjects[k].FilterExpression) &&
                        !dbObjects[k].FilterExpression.Equals("none", StringComparison.OrdinalIgnoreCase))
                    {
                        dynamicCondition += $" and {dbObjects[k].FilterExpression}\r\n";
                    }

                    // Find join conditions between dbObjects[k] and each subsequent object
                    for (int temp = k + 1; temp < dbObjects.Count; temp++)
                    {
                        int fromId = dbObjects[k].DBObjectId;
                        int toId   = dbObjects[temp].DBObjectId;

                        var join = allJoins.FirstOrDefault(j =>
                            (j.FromDBObjectId == fromId && j.ToObjectId == toId) ||
                            (j.FromDBObjectId == toId   && j.ToObjectId == fromId));

                        if (join != null && !string.IsNullOrEmpty(join.DBJoinExpression))
                            dynamicCondition += $" and {join.DBJoinExpression}\r\n";
                    }
                }
                }
                finally
                {
                    buildJoinsSection?.Dispose();
                }

                // FIX: Exclude FieldType==2 (dynamic) fields from the temp-table SELECT — those are
                // computed separately by BuildFinalSql's ":otherfields" expansion against
                // #dynamicanalysisQuery once it already exists, same split as Gb4's Dyanamic Select.
                var displayFields = queryDTOs.Where(f => f.FieldType != 2).ToList();

                // ── Build SELECT list + GROUP BY, mirroring Gb4's DisplayType/AggregationType rules ──
                // DisplayType: 0=Horizontal, 1=Vertical, 5=plain — selected as-is.
                //              2=Info — hidden grouping dimension, never selected.
                //              3=Summary — wrapped in an aggregate function per AggregationType.
                //              4=Filter-only — excluded (no case below => no select, no group-by).
                // Gb4's ReportViewId-restricted variant (select only reportview-visible fields) is not
                // ported: DynamicOutput already trims the final projection down to reportViewFields
                // (see the row-mapping loop after the combined SQL executes), so restricting here too
                // would be redundant and would risk dropping columns still needed for GROUP BY.
                bool hasSummaryFields = displayFields.Any(f => f.DisplayType == 3);

                var selectBuilder = new StringBuilder();
                void AppendSelect(string expr, string alias)
                {
                    if (selectBuilder.Length > 0) selectBuilder.Append(',');
                    selectBuilder.Append(expr).Append(" AS [").Append(alias).Append(']');
                }

                foreach (var f in displayFields)
                {
                    switch (f.DisplayType)
                    {
                        case 0: // Horizontal
                        case 1: // Vertical
                        case 5:
                            AppendSelect(f.Exression, f.DisplayName);
                            break;
                        case 2: // Info — grouping dimension only, not selected
                            break;
                        case 3: // Summary — 0=NONE,1=SUM,2=DISTINCT,3=AVG,4=DISTINCT AVG,5=COUNT,6=DISTINCT COUNT,7=MAX,8=MIN
                            string? aggExpr = f.AggregationType switch
                            {
                                1 => $"sum({f.Exression})",
                                3 => $"AVG({f.Exression})",
                                5 => $"COUNT({f.Exression})",
                                7 => $"MAX({f.Exression})",
                                8 => $"MIN({f.Exression})",
                                // 0=NONE just parenthesizes the raw expression; the DISTINCT variants
                                // (2,4,6) were unimplemented in Gb4 too — left as a no-op for parity.
                                0 => $"({f.Exression})",
                                _ => null
                            };
                            if (aggExpr != null) AppendSelect(aggExpr, f.DisplayName);
                            break;
                    }
                }

                string groupByKeyword = "";
                string groupByColumns = "";
                if (hasSummaryFields)
                {
                    // Any aggregate forces every non-aggregated dimension into GROUP BY — including
                    // DisplayType 2 (Info) even though it has no SELECT column of its own.
                    var groupFields = displayFields.Where(f => f.DisplayType is 0 or 1 or 2).ToList();
                    if (groupFields.Count > 0)
                    {
                        groupByKeyword = "group by ";
                        groupByColumns = string.Join(",", groupFields.Select(f => f.Exression));
                    }
                }

                // @dynamicfiltercondition is intentionally left for DynamicOutput (step 3) to fill in
                // with the user's filter conditions and Dapper params. Replacing it with "" here
                // was causing all SELECTION_BASED filter criteria to be silently ignored.
                return AnalysisQB.GENERATE_DYNAMIC_QUERY
                    .Replace("@dynamicselect",    selectBuilder.ToString())
                    .Replace("@dynamicfrom",      dynamicFromBuilder.ToString().TrimStart(','))
                    .Replace("@dynamiccondition", dynamicCondition)
                    .Replace("@groupby",          groupByKeyword)
                    .Replace("@groupvalues",      groupByColumns);
            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<List<DynamicAnalysisQueryDTO>> ReformDynamicQueryDTO(int analysisQueryId, List<DynamicAnalysisQueryDTO> queryDTOs, CriteriaDTO criteriaDTO, LoginDTO login, CancellationToken ct)
        {
            try
            {
                // FIX #8: Local copies — never write back to login
                DateTime periodFrom = login.PeriodFromDate;
                DateTime periodTo = login.PeriodToDate;

                // FIX (was SQL injection): parameterized MPERIOD lookup
                string periodSql = "SELECT FromDate AS CreatedOn, ToDate AS ModifiedOn FROM MPERIOD WHERE PeriodId = @periodid";
                var periodRows = (await _queryexecutor.QueryAsync<BaseFrameDTO>(
                    login, periodSql, new { periodid = login.WorkPeriodId }, cancellationToken: ct).ConfigureAwait(false)).ToList();

                if (periodRows.Count > 0)
                {
                    periodFrom = periodRows[0].CreatedOn;
                    periodTo = periodRows[0].ModifiedOn;
                }

                DateTime criteriaFromDate = new(1899, 1, 1);
                DateTime criteriaToDate = new(1899, 1, 1);
                DateTime comparativeFrom = new(1899, 1, 1);
                DateTime comparativeTo = new(1899, 1, 1);
                string fromExpression = "";
                string toExpression = "";
                string cFromExpression = "";
                string cToExpression = "";

                var rfAfKey = _keyGen.KeyGeneration(analysisQueryId, EntityConstant.OBJECTANALYSISFILTER, CacheKeyLevel.CLIENT_LEVEL, login);
                var filterFields = (await _dalCache.GetOrSetAsync<List<DynamicAnalysisQueryDTO>>(
                    rfAfKey,
                    async innerCt => (await _queryexecutor.QueryAsync<DynamicAnalysisQueryDTO>(
                        login, AnalysisQB.GET_ANALYSISFILTER_FIELDS,
                        new { analysisqueryid = analysisQueryId, TenantId = login.ClientId },
                        cancellationToken: innerCt).ConfigureAwait(false)).ToList(),
                    DALCache.TtlForLevel(CacheKeyLevel.CLIENT_LEVEL),
                    ct).ConfigureAwait(false)) ?? [];

                if (criteriaDTO?.SectionCriteriaList?.Count > 0)
                {
                    if (criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList == null)
                        throw new InvalidOperationException("Supply at least one section for Report");

                    foreach (var attr in criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList)
                    {
                        if (attr.CriteriaAttributeType != 4) continue; // only date criteria affect period resolution

                        string criteriaFieldName = attr.FieldName.ToLower();
                        foreach (var ff in filterFields)
                        {
                            if (criteriaFieldName != ff.DbFieldName.ToLower()) continue;

                            string rawVal = attr.FieldValue is JsonElement jel
                                ? (jel.ValueKind == JsonValueKind.String ? jel.GetString()! : jel.GetRawText())
                                : Convert.ToString(attr.FieldValue)!;
                            DateTime resolvedDate = ParseEpochOrDate(rawVal);

                            string attrName = ff.CriteriaAttributeName.ToLower();
                            if (attrName == "periodfrom") { criteriaFromDate = resolvedDate; fromExpression = ff.FilterCondition; }
                            else if (attrName == "periodto") { criteriaToDate = resolvedDate; toExpression = ff.FilterCondition; }
                            else if (attrName == "cperiodfrom") { comparativeFrom = resolvedDate; cFromExpression = ff.FilterCondition; }
                            else if (attrName == "cperiodto") { comparativeTo = resolvedDate; cToExpression = ff.FilterCondition; }
                        }
                    }
                }

                bool hasPeriodFields = queryDTOs.Any(f => f.Periodtype != 0);
                if (hasPeriodFields)
                {
                    if (criteriaFromDate.Year == 1899 && criteriaToDate.Year == 1899)
                        throw new InvalidOperationException("No date filter supplied — PeriodType cannot be set other than NONE.");

                    if (criteriaFromDate.Year == 1899) { criteriaFromDate = criteriaToDate; fromExpression = toExpression; }
                    if (criteriaToDate.Year == 1899) { criteriaToDate = criteriaFromDate; toExpression = fromExpression; }
                    if (comparativeFrom.Year != 1899 && comparativeTo.Year == 1899) { comparativeTo = criteriaFromDate; cToExpression = cFromExpression; }
                    if (comparativeFrom.Year == 1899 && comparativeTo.Year != 1899) { comparativeFrom = criteriaToDate; cFromExpression = cToExpression; }
                }

                var result = new List<DynamicAnalysisQueryDTO>(queryDTOs.Count);
                foreach (var dto in queryDTOs)
                {
                    if (dto.Periodtype != 0)
                    {
                        // FIX #8: Pass periodFrom/periodTo directly — not via login mutation
                        (string fromDate, string toDate) = ResolvePeriodDates(
                            dto.Periodtype, login.DatabaseOffset, periodFrom, periodTo,
                            criteriaFromDate, criteriaToDate, comparativeFrom, comparativeTo);

                        // Date strings from ResolvePeriodDates are admin-period or UTC-now derived — not user input
                        dto.Exression = $"(case when {fromExpression} >= '{fromDate}' and {toExpression} <= '{toDate}' then {dto.Exression} else 0 end)";
                    }
                    result.Add(dto);
                }
                return result;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "ReformDynamicQueryDTO failed for AnalysisQueryId {Id}", analysisQueryId);
                throw;
            }
        }

        // FIX #8: Takes databaseOffset, periodFrom, periodTo as explicit params — not LoginDTO.
        // FIX #11: PreviousWeekToDate end date fixed (was now.AddYears(-1), now now.AddDays(-7)).
        // FIX #12: Period type 18 end date fixed (was now.AddYears(-1), now now.AddMonths(-1)).
        private static (string from, string to) ResolvePeriodDates(
            int periodType,
            int databaseOffset,
            DateTime periodFrom,
            DateTime periodTo,
            DateTime criteriaFrom,
            DateTime criteriaTo,
            DateTime comparativeFrom,
            DateTime comparativeTo)
        {
            var now = DateTime.UtcNow.AddMinutes(databaseOffset);

            return periodType switch
            {
                1 => (now.ToString("yyyy/MM/dd"), now.ToString("yyyy/MM/dd")),                                               // DTD
                2 => (DateTimeExtensions.StartOfWeek(DateTime.UtcNow, DayOfWeek.Sunday).ToString("yyyy/MM/dd"),
                       DateTime.UtcNow.ToString("yyyy/MM/dd")),                                                               // WTD
                3 => (new DateTime(now.Year, now.Month, 1).ToString("yyyy/MM/dd"), now.ToString("yyyy/MM/dd")),              // MTD
                4 => (periodFrom.ToString("yyyy/MM/dd"), now.ToString("yyyy/MM/dd")),                                        // YTD
                5 => (periodFrom.ToString("yyyy/MM/dd"), periodTo.ToString("yyyy/MM/dd")),                                   // Current Year
                6 => (new DateTime(now.Year, now.Month, 1).ToString("yyyy/MM/dd"),
                       new DateTime(now.Year, now.Month, DateTime.DaysInMonth(now.Year, now.Month)).ToString("yyyy/MM/dd")),  // Current Month
                7 => (now.AddDays(-1).ToString("yyyy/MM/dd"), now.AddDays(-1).ToString("yyyy/MM/dd")),                       // Yesterday
                8 => (now.AddYears(-1).ToString("yyyy/MM/dd"), now.AddYears(-1).ToString("yyyy/MM/dd")),                     // Prev Year DTD
                9 => (DateTimeExtensions.StartOfWeek(now.AddYears(-1), DayOfWeek.Sunday).ToString("yyyy/MM/dd"),
                       now.AddYears(-1).ToString("yyyy/MM/dd")),                                                              // Prev Year WTD
                10 => (new DateTime(now.AddYears(-1).Year, now.AddYears(-1).Month, 1).ToString("yyyy/MM/dd"),
                       now.AddYears(-1).ToString("yyyy/MM/dd")),                                                              // Prev Year MTD
                11 => (periodFrom.AddYears(-1).ToString("yyyy/MM/dd"), now.AddYears(-1).ToString("yyyy/MM/dd")),              // Prev Year YTD
                12 => (periodFrom.AddYears(-1).ToString("yyyy/MM/dd"), periodTo.AddYears(-1).ToString("yyyy/MM/dd")),         // Previous Year
                13 => PreviousMonth(now),
                14 => PreviousYearMonth(now),
                15 => PreviousWeek(DateTime.UtcNow),
                16 => PreviousWeek(DateTime.UtcNow.AddYears(-1)),
                17 => PreviousWeekToDate(DateTime.UtcNow),                                                                    // FIX #11
                18 => PreviousMonthToDate(now),                                                                               // FIX #12
                19 => comparativeFrom.Year == 1899 && comparativeTo.Year == 1899
                        ? throw new InvalidOperationException("No comparative date filter supplied.")
                        : (comparativeFrom.ToString("yyyy/MM/dd"), comparativeTo.ToString("yyyy/MM/dd")),                    // Comparative
                20 => (criteriaFrom.ToString("yyyy/MM/dd"), criteriaTo.ToString("yyyy/MM/dd")),                              // Selected
                _ => ("", "")
            };
        }

        private static (string, string) PreviousMonth(DateTime now)
        {
            var prev = now.AddMonths(-1);
            return (new DateTime(prev.Year, prev.Month, 1).ToString("yyyy/MM/dd"),
                    new DateTime(prev.Year, prev.Month, DateTime.DaysInMonth(prev.Year, prev.Month)).ToString("yyyy/MM/dd"));
        }

        private static (string, string) PreviousYearMonth(DateTime now)
        {
            var prev = now.AddYears(-1);
            return (new DateTime(prev.Year, prev.Month, 1).ToString("yyyy/MM/dd"),
                    new DateTime(prev.Year, prev.Month, DateTime.DaysInMonth(prev.Year, prev.Month)).ToString("yyyy/MM/dd"));
        }

        private static (string, string) PreviousWeek(DateTime anchor)
        {
            while (anchor.DayOfWeek != DayOfWeek.Sunday) anchor = anchor.AddDays(-1);
            var start = anchor.AddDays(-7);
            return (start.ToString("yyyy/MM/dd"), anchor.AddDays(-1).ToString("yyyy/MM/dd"));
        }

        // FIX #11: End date was now.AddYears(-1) — corrected to now.AddDays(-7)
        // (same day-of-week as today, but in the previous week)
        private static (string, string) PreviousWeekToDate(DateTime now)
        {
            var anchor = now;
            while (anchor.DayOfWeek != DayOfWeek.Sunday) anchor = anchor.AddDays(-1);
            return (anchor.AddDays(-7).ToString("yyyy/MM/dd"), now.AddDays(-7).ToString("yyyy/MM/dd"));
        }

        // FIX #12: Period type 18 "Prev Month To Date" — end date was now.AddYears(-1),
        // corrected to now.AddMonths(-1) (.NET handles end-of-month edge cases correctly).
        private static (string, string) PreviousMonthToDate(DateTime now)
        {
            var prevMonth = now.AddMonths(-1);
            return (new DateTime(prevMonth.Year, prevMonth.Month, 1).ToString("yyyy/MM/dd"),
                    prevMonth.ToString("yyyy/MM/dd"));
        }

        // Template substitution for admin-configured SQL templates (function parameters, type=0 stored queries).
        // FIX #1: String values have single quotes escaped (Replace("'","''")) to prevent injection.
        // Date values come through FromEpoch and are formatted — safe. Numeric values are formatted — safe.
        // TODO: Route type=0 execution through SqlWorkbench safe executor to fully close this path.
        private async Task<string> ApplyTemplateSubstitution(string sql, CriteriaDTO? criteriaDTO)
        {
            if (criteriaDTO?.SectionCriteriaList == null || criteriaDTO.SectionCriteriaList.Count == 0)
                return sql;

            foreach (var attr in criteriaDTO.SectionCriteriaList[0].AttributesCriteriaList ?? Enumerable.Empty<AttributesCriteriaDTO>())
            {
                string fieldName = attr.FieldName.ToLower();
                string fieldValue;

                if (attr.CriteriaAttributeType == 4) // Date — epoch or ISO string → formatted string
                {
                    var element = (JsonElement)attr.FieldValue;
                    string rawVal = element.ValueKind == JsonValueKind.String
                        ? element.GetString()!
                        : element.GetRawText();
                    DateTime date = ParseEpochOrDate(rawVal);
                    fieldValue = date.ToString("yyyy/MM/dd");
                }
                else if (long.TryParse(Convert.ToString(attr.FieldValue), out long numericVal))
                {
                    fieldValue = numericVal.ToString(); // numeric literal — safe
                }
                else
                {
                    // FIX #1: Escape single quotes to prevent injection in string literal context
                    fieldValue = (Convert.ToString(attr.FieldValue) ?? "").Replace("'", "''");
                }

                sql = sql.Replace("@" + fieldName, fieldValue);
            }
            return sql;
        }

        // Accepts either an ISO date string ("2026-06-01", "2026-06-01T00:00:00")
        // or a Unix epoch long (seconds since 1970-01-01 UTC).
        private static DateTime ParseEpochOrDate(string value)
        {
            if (DateTime.TryParse(value, out DateTime dt))
                return dt;

            if (long.TryParse(value, out long epoch))
                return new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddSeconds(epoch);

            throw new ArgumentException($"Invalid date value: '{value}'");
        }

        // Returns true when the expression contains SQL constructs too complex to parse
        // for indirect table reference extraction.
        private static bool ContainsComplexExpression(string expression)
        {
            string lower = expression.ToLower();
            return lower.Contains("case when") || lower.Contains("replace (") ||
                   lower.Contains("row_number()") || lower.Contains("[dbo]");
        }

        // FIX #7: Populates a HashSet of table aliases extracted from a JOIN/field expression.
        // Using a set eliminates the duplicate-table risk of the previous string-append approach.
        private static void CollectIndirectTables(string expression, HashSet<string> target)
        {
            string[] parts = expression.Split('=');
            var segments = parts.Length > 1 ? parts : new[] { expression };

            foreach (var segment in segments)
            {
                if (string.IsNullOrWhiteSpace(segment)) continue;
                string alias = segment.Trim().Split('.')[0].Trim();
                if (!string.IsNullOrWhiteSpace(alias))
                    target.Add(alias);
            }
        }




        public async Task<int> SaveAnalysis(AnalysisDTO analysisDTO, LoginDTO LoginDTO, DbTransaction dbTransaction)
        {
            try
            {
                string analysisSql = AnalysisQB.SAVE_ANALYSIS;

                int result = await _queryexecutor.ExecuteAsync(
                    LoginDTO,
                    analysisSql,
                    analysisDTO,
                    dbTransaction);

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<int> UpdateAnalysis(AnalysisDTO analysisDTO, LoginDTO LoginDTO, DbTransaction dbTransaction)
        {
            try
            {
                string analysisSql = AnalysisQB.UPDATE_ANALYSIS;

                int result = await _queryexecutor.ExecuteAsync(
                    LoginDTO,
                    analysisSql,
                    analysisDTO,
                    dbTransaction);

                return result;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task DeleteAnalysis(int analysisId, LoginDTO login, CancellationToken ct)
        {
            await _queryexecutor.ExecuteAsync(
                login,
                AnalysisQB.DELETE_ANALYSIS,
                new { AnalysisId = analysisId, TenantId = login.ClientId, ModifiedById = login.UserId },
                cancellationToken: ct).ConfigureAwait(false);
        }
    }
}