using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using AnalyticsDAL.CustomCode.Analysis;
using AnalyticsDAL.CustomCode.AnalysisField;
using AnalyticsDAL.CustomCode.AnalysisObject;
using AnalyticsDAL.CustomCode.AnalysisQuery;
using AnalyticsDAL.CustomCode.DBJoin;
using AnalyticsDAL.DTO.Analysis;
using AnalyticsDAL.DTO.DBJoin;
// Disambiguated: AnalyticsDAL.DTO.Analysis also declares its own (unrelated, legacy) AnalysisQueryDTO —
// IAnalysisQueryDAL/AnalysisQueryDAL operate on the AnalyticsDAL.DTO.AnalysisQuery one, so that's the
// one this class must build and pass in.
using AnalysisQueryDTO = AnalyticsDAL.DTO.AnalysisQuery.AnalysisQueryDTO;
using AnalysisQueryFieldsDTO = AnalyticsDAL.DTO.AnalysisQuery.AnalysisQueryFieldsDTO;
using AnalyticsDAL.DTO.Wizard;
using GB5Shared.DBObject;
using GB5Shared.SchemaIntrospection;
using GB5Shared.DALCache;
using GB5Shared.DaprCache;
using GB5Shared.DTO.Framework.AutoNumber;
using GB5Shared.DTO.Framework.DBObject;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.SchemaIntrospection;
using GB5Shared.EventLogPublish;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using GB5Shared.Telemetry;
using GB5Shared.Validation;
using Microsoft.Extensions.Logging;
using SwBLL.ClientDatabase;
using SwBLL.DbServer;
using SwBLL.Provisioning;
using SwDAL.DTO.ClientDatabase;
using SwDAL.DTO.DbServer;
using SwDAL.Enums;
using static GB5Shared.GB5Constant.Constant;

namespace AnalyticsBLL.Wizard
{
    /// <summary>Phase 2 orchestration layer for the Analytics Catalog Wizard — turns discovered
    /// schema + user selections into real MANALYSIS/MANALYSISOBJECT/MANALYSISFIELDS/DBJOIN
    /// (/MANALYSISQUERY) rows in one transaction.
    /// <para>
    /// <strong>Accepted v1 limitation:</strong> registering a new DBOBJECT (+DBOBJECTFIELDS) for a
    /// table that isn't already catalogued is done via the framework-owned
    /// <see cref="IDBObjectBLL.SaveDBObject"/>, which is NOT transaction-aware and manages its own
    /// commit. If QuickBuildAsync fails after one or more new DBOBJECT rows have already been
    /// created but before/while the Analytics-side transaction commits, those DBOBJECT rows are
    /// NOT rolled back — they are orphaned (harmless, but not automatically cleaned up). This is
    /// logged loudly (ILogger + GB5Trace) rather than silently swallowed; solving it properly
    /// would require either giving DBObjectBLL a transaction-aware overload or a compensating
    /// delete, both out of scope for this phase.
    /// </para></summary>
    public class AnalyticsWizardBLL : IAnalyticsWizardBLL
    {
        private readonly IOwnDbSchemaBLL _ownDbSchemaBLL;
        private readonly IClientDatabaseBLL _clientDatabaseBLL;
        private readonly IDbServerBLL _dbServerBLL;
        private readonly ITargetDbExecutor _targetDbExecutor;
        private readonly IDBObjectBLL _dbObjectBLL;
        private readonly IAnalysisDAL _analysisDAL;
        private readonly IAnalysisObjectDAL _analysisObjectDAL;
        private readonly IAnalysisFieldDAL _analysisFieldDAL;
        private readonly IAnalysisQueryDAL _analysisQueryDAL;
        private readonly IDBJoinDAL _dbJoinDAL;
        private readonly AutoNumber _autoNumber;
        private readonly IQueryExecutor _queryExecutor;
        private readonly IValidation _validation;
        private readonly IDALCache _dalCache;
        private readonly KeyInvalidate _keyInvalidate;
        private readonly EventLogPublish _eventLogPublish;
        private readonly ILogger<AnalyticsWizardBLL> _logger;
        private readonly CacheKeyGeneration _keyGen = new();

        private const string ANALYSISOBJECT_ENTITY = "MANALYSISOBJECT";
        private const string ANALYSISFIELDS_ENTITY = "MANALYSISFIELDS";
        private const string ANALYSISQUERY_ENTITY = "MANALYSISQUERY";
        private const string ANALYSISQUERYFIELDS_ENTITY = "MANALYSISQUERYFIELDS";

        public AnalyticsWizardBLL(
            IOwnDbSchemaBLL ownDbSchemaBLL,
            IClientDatabaseBLL clientDatabaseBLL,
            IDbServerBLL dbServerBLL,
            ITargetDbExecutor targetDbExecutor,
            IDBObjectBLL dbObjectBLL,
            IAnalysisDAL analysisDAL,
            IAnalysisObjectDAL analysisObjectDAL,
            IAnalysisFieldDAL analysisFieldDAL,
            IAnalysisQueryDAL analysisQueryDAL,
            IDBJoinDAL dbJoinDAL,
            AutoNumber autoNumber,
            IQueryExecutor queryExecutor,
            IValidation validation,
            IDALCache dalCache,
            KeyInvalidate keyInvalidate,
            EventLogPublish eventLogPublish,
            ILogger<AnalyticsWizardBLL> logger)
        {
            _ownDbSchemaBLL = ownDbSchemaBLL;
            _clientDatabaseBLL = clientDatabaseBLL;
            _dbServerBLL = dbServerBLL;
            _targetDbExecutor = targetDbExecutor;
            _dbObjectBLL = dbObjectBLL;
            _analysisDAL = analysisDAL;
            _analysisObjectDAL = analysisObjectDAL;
            _analysisFieldDAL = analysisFieldDAL;
            _analysisQueryDAL = analysisQueryDAL;
            _dbJoinDAL = dbJoinDAL;
            _autoNumber = autoNumber;
            _queryExecutor = queryExecutor;
            _validation = validation;
            _dalCache = dalCache;
            _keyInvalidate = keyInvalidate;
            _eventLogPublish = eventLogPublish;
            _logger = logger;
        }

        public async Task<SchemaCatalogResultDTO> DiscoverAsync(WizardDiscoverRequestDTO req, LoginDTO login, CancellationToken ct)
        {
            if (req == null) throw new ArgumentNullException(nameof(req));

            GB5Trace.Step("wizard-discover", new { req.SourceKind, req.SourceClientDbId });

            List<SchemaTableColumnDTO> columns;
            List<SchemaForeignKeyDTO> foreignKeys;

            if (req.SourceKind == 0)
            {
                columns = (await _ownDbSchemaBLL.GetOwnDbColumnsAsync(login, ct).ConfigureAwait(false)).ToList();
                foreignKeys = (await _ownDbSchemaBLL.GetOwnDbForeignKeysAsync(login, ct).ConfigureAwait(false)).ToList();
            }
            else if (req.SourceKind == 1)
            {
                var (server, clientDb) = await ResolveExternalTargetAsync(req.SourceClientDbId, login, ct).ConfigureAwait(false);
                var connectionString = await _targetDbExecutor.BuildConnectionStringForRoleAsync(
                    server, clientDb, ClientDbLoginRole.ReadOnly, ct).ConfigureAwait(false);

                columns = (await _targetDbExecutor.GetServerColumnsAsync(connectionString, server.DbType, ct).ConfigureAwait(false)).ToList();
                foreignKeys = (await _targetDbExecutor.GetServerForeignKeysAsync(connectionString, server.DbType, ct).ConfigureAwait(false)).ToList();
            }
            else
            {
                throw new ArgumentException($"Unsupported SourceKind {req.SourceKind}. Expected 0 (Own) or 1 (ExternalClientDb).", nameof(req));
            }

            if (!string.IsNullOrWhiteSpace(req.TableNameFilter))
            {
                columns = columns
                    .Where(c => c.TableName != null && c.TableName.Contains(req.TableNameFilter, StringComparison.OrdinalIgnoreCase))
                    .ToList();

                var filteredTableKeys = columns
                    .Select(c => $"{c.SchemaName}.{c.TableName}")
                    .ToHashSet(StringComparer.OrdinalIgnoreCase);

                foreignKeys = foreignKeys
                    .Where(fk => filteredTableKeys.Contains($"{fk.FromSchema}.{fk.FromTable}")
                              || filteredTableKeys.Contains($"{fk.ToSchema}.{fk.ToTable}"))
                    .ToList();
            }

            return new SchemaCatalogResultDTO { Columns = columns, ForeignKeys = foreignKeys };
        }

        public async Task<AnalyticsWizardBuildResultDTO> QuickBuildAsync(AnalyticsWizardBuildRequestDTO req, LoginDTO login, CancellationToken ct)
        {
            if (req == null) throw new ArgumentNullException(nameof(req));

            GB5Trace.Step("wizard-validate", new { req.AnalysisCode, req.SourceKind });

            await _validation.NotEmpty(req.AnalysisCode, nameof(req.AnalysisCode)).ConfigureAwait(false);
            await _validation.NotEmpty(req.AnalysisName, nameof(req.AnalysisName)).ConfigureAwait(false);

            if (req.SelectedTables == null || req.SelectedTables.Count == 0)
                throw new ArgumentException("At least one table must be selected.", nameof(req));

            if (req.SelectedTables.Any(t => t.SelectedColumnNames == null || t.SelectedColumnNames.Count == 0))
                throw new ArgumentException("Every selected table must have at least one selected column.", nameof(req));

            var acceptedJoins = req.AcceptedJoins ?? new List<WizardJoinSelectionDTO>();
            var warnings = new List<string>();

            // ── Defense in depth: re-validate the target server for external sources — never
            // trust the FE alone, even though DiscoverAsync already enforced this earlier. ──
            DbServerDTO? server = null;
            ClientDatabaseDTO? clientDb = null;
            string? connectionString = null;

            if (req.SourceKind == 1)
            {
                (server, clientDb) = await ResolveExternalTargetAsync(req.SourceClientDbId, login, ct).ConfigureAwait(false);
            }

            // ── Resolve/register a DBOBJECT (+DBOBJECTFIELDS) per selected table. Framework-owned,
            // not part of our own transaction — see class remarks. ──
            var tableRegistrations = new List<(WizardTableSelectionDTO Selection, DBObjectDTO DBObject, bool WasNewlyCreated)>();
            List<SchemaTableColumnDTO>? allColumns = null;

            foreach (var table in req.SelectedTables)
            {
                GB5Trace.Step("wizard-resolve-dbobject", new { table.SchemaName, table.TableName });

                var existing = await _dbObjectBLL.FindBySourceAndNameAsync(
                    table.TableName, req.SourceKind, req.SourceClientDbId, login, ct).ConfigureAwait(false);

                bool wasNewlyCreated = false;
                DBObjectDTO dbObject;

                if (existing != null)
                {
                    dbObject = existing;
                }
                else
                {
                    if (allColumns == null)
                    {
                        if (req.SourceKind == 0)
                        {
                            allColumns = (await _ownDbSchemaBLL.GetOwnDbColumnsAsync(login, ct).ConfigureAwait(false)).ToList();
                        }
                        else
                        {
                            connectionString ??= await _targetDbExecutor.BuildConnectionStringForRoleAsync(
                                server!, clientDb!, ClientDbLoginRole.ReadOnly, ct).ConfigureAwait(false);
                            allColumns = (await _targetDbExecutor.GetServerColumnsAsync(connectionString, server!.DbType, ct).ConfigureAwait(false)).ToList();
                        }
                    }

                    var tableColumns = allColumns
                        .Where(c => string.Equals(c.TableName, table.TableName, StringComparison.OrdinalIgnoreCase)
                                 && (string.IsNullOrWhiteSpace(table.SchemaName)
                                     || string.Equals(c.SchemaName, table.SchemaName, StringComparison.OrdinalIgnoreCase)))
                        .Where(c => table.SelectedColumnNames.Contains(c.ColumnName, StringComparer.OrdinalIgnoreCase))
                        .ToList();

                    var newDto = new DBObjectDTO
                    {
                        DBObjectId = 0,
                        DBObjectName = table.TableName,
                        DBObjectDisplayName = table.TableName,
                        DBObjectSection = "Analytics",
                        DBObjectType = 0, // Tables
                        SourceDbObjectKind = req.SourceKind,
                        SourceClientDbId = req.SourceClientDbId,
                        DBObjectFields = tableColumns
                            .Select((c, i) => new DBObjectFieldsDTO
                            {
                                DBObjectFieldsSlNo = (short)(i + 1),
                                DBObjectFieldsName = c.ColumnName,
                                DBObjectFieldsDisplayName = c.ColumnName,
                                DBObjectFieldsDataType = MapSqlDataType(c.DataType),
                                DBObjectFieldsIsPrimaryKey = (byte)(c.IsPrimaryKey ? 1 : 0),
                                DBObjectFieldsRemarks = string.Equals(c.IsNullable, "NO", StringComparison.OrdinalIgnoreCase) ? "NOT NULL" : ""
                            })
                            .ToList()
                    };

                    if (newDto.DBObjectFields.Count == 0)
                    {
                        warnings.Add($"No matching columns were found on '{table.TableName}' for the selected column names — registered DBOBJECT with zero fields.");
                    }

                    await _dbObjectBLL.SaveDBObject(newDto, login).ConfigureAwait(false);
                    wasNewlyCreated = true;
                    dbObject = newDto; // DBObjectId mutated in place by SaveDBObject
                }

                tableRegistrations.Add((table, dbObject, wasNewlyCreated));
            }

            // Re-fetch each DBObject WITH its fields populated — SaveDBObject doesn't return
            // generated DBOBJECTFIELDS.DBOBJECTFIELDSID (an identity column) back onto the
            // in-memory DTOs, so a read-back is required to match columns by name.
            var dbObjectFieldsByDbObjectId = new Dictionary<int, Dictionary<string, int>>();
            foreach (var (_, dbObject, _) in tableRegistrations)
            {
                if (dbObjectFieldsByDbObjectId.ContainsKey(dbObject.DBObjectId))
                    continue;

                var json = await _dbObjectBLL.GetDBObject(dbObject.DBObjectId, login).ConfigureAwait(false);
                var full = Newtonsoft.Json.JsonConvert.DeserializeObject<DBObjectDTO>(json);

                var byName = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
                if (full?.DBObjectFields != null)
                {
                    foreach (var f in full.DBObjectFields)
                        byName[f.DBObjectFieldsName] = f.DBObjectFieldsId;
                }

                dbObjectFieldsByDbObjectId[dbObject.DBObjectId] = byName;
            }

            // ── Pre-transaction: allocate every AutoNumber batch this build needs ──
            var totalSelectedColumns = req.SelectedTables.Sum(t => t.SelectedColumnNames.Count);

            AutoNumberDTO? analysisAuto = null;
            AutoNumberDTO? analysisObjectAuto = null;
            AutoNumberDTO? dbJoinAuto = null;
            AutoNumberDTO? analysisFieldAuto = null;
            AutoNumberDTO? analysisQueryAuto = null;
            AutoNumberDTO? analysisQueryFieldAuto = null;

            try
            {
                analysisAuto = await _autoNumber.GetAutoNumber(1, AUTONUMBERCONSTANT.MANALYSIS, login).ConfigureAwait(false);
                analysisObjectAuto = await _autoNumber.GetAutoNumber(req.SelectedTables.Count, ANALYSISOBJECT_ENTITY, login).ConfigureAwait(false);

                if (acceptedJoins.Count > 0)
                    dbJoinAuto = await _autoNumber.GetAutoNumber(acceptedJoins.Count, AUTONUMBERCONSTANT.DBJOIN, login).ConfigureAwait(false);

                analysisFieldAuto = await _autoNumber.GetAutoNumber(Math.Max(totalSelectedColumns, 1), ANALYSISFIELDS_ENTITY, login).ConfigureAwait(false);

                if (req.AutoCreateDefaultQuery)
                {
                    analysisQueryAuto = await _autoNumber.GetAutoNumber(1, ANALYSISQUERY_ENTITY, login).ConfigureAwait(false);
                    analysisQueryFieldAuto = await _autoNumber.GetAutoNumber(Math.Max(totalSelectedColumns, 1), ANALYSISQUERYFIELDS_ENTITY, login).ConfigureAwait(false);
                }
            }
            catch (Exception ex)
            {
                GB5Trace.MarkFailed("wizard-autonumber-failed", ex);
                _logger.LogError(ex, "QuickBuildAsync AutoNumber allocation failed for AnalysisCode {Code}", req.AnalysisCode);
                await RollbackAllAutoNumbersAsync(login,
                    (AUTONUMBERCONSTANT.MANALYSIS, analysisAuto),
                    (ANALYSISOBJECT_ENTITY, analysisObjectAuto),
                    (AUTONUMBERCONSTANT.DBJOIN, dbJoinAuto),
                    (ANALYSISFIELDS_ENTITY, analysisFieldAuto),
                    (ANALYSISQUERY_ENTITY, analysisQueryAuto),
                    (ANALYSISQUERYFIELDS_ENTITY, analysisQueryFieldAuto)).ConfigureAwait(false);
                throw;
            }

            var result = new AnalyticsWizardBuildResultDTO { Warnings = warnings };
            var trans = await _queryExecutor.BeginTransactionAsync(login).ConfigureAwait(false);

            try
            {
                // 1. Analysis header
                var analysisDTO = new AnalysisDTO
                {
                    AnalysisId = analysisAuto!.StartNumber,
                    AnalysisCode = req.AnalysisCode,
                    AnalysisName = req.AnalysisName,
                    AnalysisDescription = req.AnalysisName,
                    BaseObjectId = tableRegistrations[0].DBObject.DBObjectId,
                    AnalysisCreatedById = login.UserId,
                    AnalysisCreatedOn = DateTime.UtcNow,
                    AnalysisModifiedById = login.UserId,
                    AnalysisModifiedOn = DateTime.UtcNow,
                    AnalysisStatus = 1,
                    AnalysisVersion = 1,
                    AnalysisSourceType = 5
                };

                GB5Trace.Step("wizard-save-analysis", new { analysisDTO.AnalysisId, analysisDTO.AnalysisCode });
                await _analysisDAL.SaveAnalysis(analysisDTO, login, trans).ConfigureAwait(false);
                result.AnalysisId = analysisDTO.AnalysisId;

                // 2. AnalysisObject per table
                var analysisObjectIdByTableKey = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
                var nextAnalysisObjectId = analysisObjectAuto!.StartNumber;
                short objectSlNo = 1;

                foreach (var (selection, dbObject, _) in tableRegistrations)
                {
                    var analysisObjectDTO = new AnalysisObjectDTO
                    {
                        AnalysisObjectId = nextAnalysisObjectId,
                        AnalysisId = analysisDTO.AnalysisId,
                        AnalysisObjectSlNo = objectSlNo,
                        DBObjectId = dbObject.DBObjectId,
                        AnalysisObjectFilterExpression = "",
                        AnalysisObjectVariables = "",
                        TenantId = login.ClientId,
                        WorkspaceId = req.WorkspaceId
                    };

                    GB5Trace.Step("wizard-save-analysisobject", new { analysisObjectDTO.AnalysisObjectId, analysisObjectDTO.DBObjectId });
                    await _analysisObjectDAL.SaveAnalysisObject(analysisObjectDTO, login, ct, trans).ConfigureAwait(false);

                    result.AnalysisObjectIds.Add(analysisObjectDTO.AnalysisObjectId);

                    var tableKey = $"{selection.SchemaName}.{selection.TableName}";
                    analysisObjectIdByTableKey[tableKey] = analysisObjectDTO.AnalysisObjectId;
                    analysisObjectIdByTableKey[selection.TableName] = analysisObjectDTO.AnalysisObjectId;

                    nextAnalysisObjectId++;
                    objectSlNo++;
                }

                // 3. DBJoin per accepted join — resolved by table name, not DBObjectId: a
                // brand-new table's DBObjectId is only known now (it was assigned during table
                // registration above, in this same call), so the caller cannot supply it in
                // advance. tableRegistrations already carries the real, final DBObjectId per
                // selected table.
                var dbObjectIdByTableName = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
                foreach (var (selection, dbObject, _) in tableRegistrations)
                    dbObjectIdByTableName[selection.TableName] = dbObject.DBObjectId;

                if (dbJoinAuto != null)
                {
                    var nextDbJoinId = dbJoinAuto.StartNumber;
                    foreach (var join in acceptedJoins)
                    {
                        if (!dbObjectIdByTableName.TryGetValue(join.FromTableName, out var fromDbObjectId))
                            throw new InvalidOperationException(
                                $"Join references FromTableName '{join.FromTableName}', which is not one of the selected tables.");
                        if (!dbObjectIdByTableName.TryGetValue(join.ToTableName, out var toDbObjectId))
                            throw new InvalidOperationException(
                                $"Join references ToTableName '{join.ToTableName}', which is not one of the selected tables.");

                        var dbJoinDTO = new DBJoinDTO
                        {
                            DBJoinId = nextDbJoinId,
                            FromDBObjectId = fromDbObjectId,
                            ToObjectId = toDbObjectId,
                            DBJoinExpression = join.Expression,
                            DBJoinDescription = $"Wizard join: {join.FromTableName} -> {join.ToTableName}",
                            DBJoinRelationType = 0,
                            DBJoinJoinType = 0,
                            DBJoinSourceType = 5,
                            DBJoinStatus = 1,
                            DBJoinVersion = 1,
                            DBJoinCreatedById = login.UserId,
                            DBJoinCreatedOn = DateTime.UtcNow,
                            DBJoinModifiedById = login.UserId,
                            DBJoinModifiedOn = DateTime.UtcNow
                        };

                        GB5Trace.Step("wizard-save-dbjoin", new { dbJoinDTO.DBJoinId, dbJoinDTO.FromDBObjectId, dbJoinDTO.ToObjectId });
                        await _dbJoinDAL.SaveDBJoin(dbJoinDTO, login, trans).ConfigureAwait(false);

                        result.DBJoinIds.Add(dbJoinDTO.DBJoinId);
                        nextDbJoinId++;
                    }
                }

                // 4. AnalysisFields per selected column
                var nextAnalysisFieldId = analysisFieldAuto!.StartNumber;
                var analysisQueryFieldSources = new List<(int AnalysisFieldId, string DisplayName)>();

                foreach (var (selection, dbObject, _) in tableRegistrations)
                {
                    var tableKey = $"{selection.SchemaName}.{selection.TableName}";
                    var analysisObjectId = analysisObjectIdByTableKey.TryGetValue(tableKey, out var aoId)
                        ? aoId
                        : analysisObjectIdByTableKey[selection.TableName];

                    var fieldsByName = dbObjectFieldsByDbObjectId[dbObject.DBObjectId];

                    foreach (var columnName in selection.SelectedColumnNames)
                    {
                        if (!fieldsByName.TryGetValue(columnName, out var dbObjectFieldsId))
                        {
                            warnings.Add($"Column '{columnName}' on table '{selection.TableName}' was not found on the registered DBOBJECT — skipped.");
                            continue;
                        }

                        var analysisFieldsDTO = new AnalysisFieldsDTO
                        {
                            AnalysisFieldsId = nextAnalysisFieldId,
                            AnalysisId = analysisDTO.AnalysisId,
                            AnalysisObjectId = analysisObjectId,
                            DBObjectFieldsId = dbObjectFieldsId,
                            DBObjectId = dbObject.DBObjectId,
                            AnalysisFieldsDisplayName = columnName,
                            AnalysisFieldsFilterExpression = "",
                            AnalysisFieldsVariables = "",
                            AnalysisFieldsExpression = "",
                            AnalysisFieldsSlNo = (short)(result.AnalysisFieldIds.Count + 1),
                            AnalysisFieldsTenantId = login.ClientId,
                            AnalyifledsWorkSpaceId = req.WorkspaceId,
                            AnalysisFieldsCreatedById = login.UserId,
                            AnalysisFieldsCreatedOn = DateTime.UtcNow,
                            AnalysisFieldsModifiedById = login.UserId,
                            AnalysisFieldsModifiedOn = DateTime.UtcNow,
                            AnalysisFieldsStatus = 1,
                            AnalysisFieldsVersion = 1
                        };

                        GB5Trace.Step("wizard-save-analysisfield", new { analysisFieldsDTO.AnalysisFieldsId, analysisFieldsDTO.DBObjectFieldsId });
                        await _analysisFieldDAL.SaveAnalysisFields(analysisFieldsDTO, isNew: true, login, ct, trans).ConfigureAwait(false);

                        result.AnalysisFieldIds.Add(analysisFieldsDTO.AnalysisFieldsId);
                        analysisQueryFieldSources.Add((analysisFieldsDTO.AnalysisFieldsId, columnName));

                        nextAnalysisFieldId++;
                    }
                }

                // 5. Default AnalysisQuery covering every selected field
                if (req.AutoCreateDefaultQuery && analysisQueryAuto != null)
                {
                    var analysisQueryDTO = new AnalysisQueryDTO
                    {
                        AnalysisQueryId = analysisQueryAuto.StartNumber,
                        AnalysisId = analysisDTO.AnalysisId,
                        AnalysisQueryQueryName = $"{req.AnalysisName} - Default",
                        AnalysisQueryQueryType = 1, // SELECTION_BASED
                        AnalysisQueryQuery = "",
                        AnalysisQueryOrderBy = "",
                        AnalysisQueryStatus = 1,
                        AnalysisQueryVersion = 1,
                        AnalysisQuerySourceType = 5,
                        AnalysisQueryCreatedById = login.UserId,
                        AnalysisQueryModifiedById = login.UserId,
                        AnalysisQueryWorkspaceId = req.WorkspaceId,
                        AnalysisQueryTenantId = login.ClientId
                    };

                    var nextQueryFieldId = analysisQueryFieldAuto!.StartNumber;
                    foreach (var (analysisFieldId, displayName) in analysisQueryFieldSources)
                    {
                        analysisQueryDTO.AnalysisQueryFieldDTOs.Add(new AnalysisQueryFieldsDTO
                        {
                            AnalysisQueryFieldsId = nextQueryFieldId,
                            AnalysisQueryId = analysisQueryDTO.AnalysisQueryId,
                            AnalysisQueryFieldsAnalysisFieldId = analysisFieldId,
                            AnalysisQueryFieldsDisplayType = 0, // Horizontal
                            AnalysisQueryFieldsDisplayName = displayName,
                            AnalysisQueryFieldsFilterCondition = ""
                        });
                        nextQueryFieldId++;
                    }

                    GB5Trace.Step("wizard-save-analysisquery", new { analysisQueryDTO.AnalysisQueryId, FieldCount = analysisQueryDTO.AnalysisQueryFieldDTOs.Count });
                    await _analysisQueryDAL.SaveAnalysisQuery(analysisQueryDTO, login, isNew: true, ct, trans).ConfigureAwait(false);

                    result.AnalysisQueryId = analysisQueryDTO.AnalysisQueryId;
                }

                await _queryExecutor.CommitAsync(trans).ConfigureAwait(false);

                // ── Post-commit: event log + cache invalidation ──
                GB5Trace.Step("wizard-event-publish", new { EventTypeConstant.SAVEANALYSISEVENTTYPEID, analysisDTO.AnalysisId });
                await _eventLogPublish.PublishEventLogAsync(
                    "Analysis created via Catalog Wizard", analysisDTO, EventTypeConstant.SAVEANALYSISEVENTTYPEID,
                    analysisDTO.AnalysisId, login, ct: ct).ConfigureAwait(false);

                var analysisCacheKey = _keyGen.KeyGeneration(analysisDTO.AnalysisId, EntityConstant.OBJECTANALYSIS, CacheKeyLevel.CLIENT_LEVEL, login);
                var cacheInvalidationTasks = new List<Task>
                {
                    _keyInvalidate.AllInvalidateCache(analysisCacheKey),
                    _dalCache.InvalidateAsync(analysisCacheKey, ct)
                };

                if (result.DBJoinIds.Count > 0)
                {
                    var dbJoinCacheKey = _keyGen.KeyGeneration("all", EntityConstant.OBJECTDBJOIN, CacheKeyLevel.CLIENT_LEVEL, login);
                    cacheInvalidationTasks.Add(_dalCache.InvalidateAsync(dbJoinCacheKey, ct));
                }

                await Task.WhenAll(cacheInvalidationTasks).ConfigureAwait(false);

                return result;
            }
            catch (Exception ex)
            {
                await _queryExecutor.RollbackAsync(trans).ConfigureAwait(false);

                GB5Trace.MarkFailed("wizard-quickbuild-failed", ex);
                _logger.LogError(ex, "QuickBuildAsync failed for AnalysisCode {Code}", req.AnalysisCode);

                if (tableRegistrations.Any(t => t.WasNewlyCreated))
                {
                    _logger.LogWarning(
                        "QuickBuildAsync rolled back after registering {Count} new DBOBJECT row(s) for AnalysisCode {Code} — those DBOBJECT/DBOBJECTFIELDS rows are framework-owned and were NOT rolled back; manual cleanup may be required.",
                        tableRegistrations.Count(t => t.WasNewlyCreated), req.AnalysisCode);
                }

                await RollbackAllAutoNumbersAsync(login,
                    (AUTONUMBERCONSTANT.MANALYSIS, analysisAuto),
                    (ANALYSISOBJECT_ENTITY, analysisObjectAuto),
                    (AUTONUMBERCONSTANT.DBJOIN, dbJoinAuto),
                    (ANALYSISFIELDS_ENTITY, analysisFieldAuto),
                    (ANALYSISQUERY_ENTITY, analysisQueryAuto),
                    (ANALYSISQUERYFIELDS_ENTITY, analysisQueryFieldAuto)).ConfigureAwait(false);

                throw;
            }
        }

        private async Task<(DbServerDTO Server, ClientDatabaseDTO ClientDb)> ResolveExternalTargetAsync(
            int? sourceClientDbId, LoginDTO login, CancellationToken ct)
        {
            if (!sourceClientDbId.HasValue || sourceClientDbId.Value <= 0)
                throw new ArgumentException("SourceClientDbId is required when SourceKind is ExternalClientDb (1).");

            var clientDb = await _clientDatabaseBLL.GetById(sourceClientDbId.Value, login, ct).ConfigureAwait(false)
                ?? throw new ArgumentException($"ClientDatabase {sourceClientDbId} was not found.");

            var server = await _dbServerBLL.GetById(clientDb.DbServerId, login, ct).ConfigureAwait(false)
                ?? throw new ArgumentException($"DbServer {clientDb.DbServerId} was not found.");

            if (server.DbType != (byte)DbType.SqlServer)
                throw new NotSupportedException(
                    $"The Analytics Catalog Wizard only supports SQL Server target databases today (DbServerId {server.DbServerId} is DbType {server.DbType}).");

            return (server, clientDb);
        }

        /// <summary>Rolls a batch allocated via AutoNumber.GetAutoNumber back to its pre-allocation
        /// state. RollbackAutoNumber only undoes a single reserved id per call (guarded on the
        /// current AUTOID matching exactly), so a batch of N consecutive ids must be unwound one
        /// at a time, in descending order (EndNumber down to StartNumber) — each call's guard only
        /// holds if the previous call in the loop has already run.</summary>
        private async Task RollbackAllAutoNumbersAsync(LoginDTO login, params (string EntityCode, AutoNumberDTO? Batch)[] allocations)
        {
            foreach (var (entityCode, batch) in allocations)
            {
                if (batch == null) continue;

                for (var id = batch.EndNumber; id >= batch.StartNumber; id--)
                {
                    try
                    {
                        await _autoNumber.RollbackAutoNumber(entityCode, id, login).ConfigureAwait(false);
                    }
                    catch (Exception rollbackEx)
                    {
                        _logger.LogError(rollbackEx, "Failed to roll back AutoNumber {EntityCode} id {Id}", entityCode, id);
                    }
                }
            }
        }

        /// <summary>Maps a SQL Server column data type name to DBOBJECTFIELDS.DBFIELDATATYPE's own
        /// numbering: Int(0), String(1), Long(2), Date(3), Boolean(4), Object(5), Double(6), Byte(7).</summary>
        private static byte MapSqlDataType(string? sqlDataType)
        {
            if (string.IsNullOrWhiteSpace(sqlDataType))
                return 1; // String — safe fallback

            return sqlDataType.ToLowerInvariant() switch
            {
                "int" or "smallint" => 0,
                "bigint" => 2,
                "date" or "datetime" or "datetime2" or "smalldatetime" or "time" or "datetimeoffset" => 3,
                "bit" => 4,
                "float" or "real" or "decimal" or "numeric" or "money" or "smallmoney" => 6,
                "tinyint" => 7,
                _ => 1 // varchar, nvarchar, char, nchar, text, ntext, uniqueidentifier, etc.
            };
        }
    }
}