using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

namespace EntityCreationTool;

// ─────────────────────────────────────────────────────────────────────────────
// Config models — bound from appsettings.json "EntityCreation" section
// ─────────────────────────────────────────────────────────────────────────────

public sealed class AutoNumberEntityCodes
{
    // Verify these against: SELECT EntityCode, AutoId FROM MAUTONUMBER
    // If DBObject and DBObjectFields share the same counter, set both to the same value.
    public string DBObject { get; set; } = "DBOBJECT";
    public string DBObjectFields { get; set; } = "DBOBJECT";
    public string Entity { get; set; } = "MENTITY";
}

public sealed class EntitySuffixes
{
    public string DTO { get; set; } = "DTO";
    public string Picklist { get; set; } = "PicklistDTO";
}

public sealed class EntityCategories
{
    public int Poco { get; set; } = 0;
    public int DTO { get; set; } = 1;
    public int Picklist { get; set; } = 2;
}

public sealed class DBObjectDefaults
{
    public int DbObjectType { get; set; } = 0;
    public string Remarks { get; set; } = "NONE";
    public int SortOrder { get; set; } = 9999;
    public int Status { get; set; } = 1;
    public int Version { get; set; } = 1;
    public int SourceType { get; set; } = 1;
    public int IsStandardDataRequired { get; set; } = 1;
    public int DataSourceTypeId { get; set; } = -1;
    public string DisplayName { get; set; } = "";
    public int ObjectNature { get; set; } = 0;
    public int TableType { get; set; } = 0;
    public int IsAnalysis { get; set; } = 0;
    public int BaseObjectId { get; set; } = -1;
    public string Condition { get; set; } = "NULL";
    public int IsSystem { get; set; } = 0;
    public int IsDeveloper { get; set; } = 0;
    public int IsImplementation { get; set; } = 0;
    public int IsAdmin { get; set; } = 0;
    public int IsUser { get; set; } = 0;
    public string Expression { get; set; } = "";
    public string Variables { get; set; } = "";
    public int QueryType { get; set; } = 0;
    public int IsArchive { get; set; } = 0;
    public int ReplayUnit { get; set; } = 0;
    public int ReplayAmount { get; set; } = 0;
    public int ParentObjectId { get; set; } = -1;
    public string DateKey { get; set; } = "";
}

public sealed class EntityDefaults
{
    public int BaseClassId { get; set; } = -1;
    public int SecurityLevel { get; set; } = 1;
    public int EntityType { get; set; } = 0;
    public int IsPersist { get; set; } = 0;
    public int Scope { get; set; } = 0;
    public int ProjectId { get; set; } = -1;
    public int EntityNature { get; set; } = 5;
    public string Section { get; set; } = "";
    public int ImplementationType { get; set; } = 0;
    public int IsCodeApplicable { get; set; } = 1;
    public int MaxCodeSize { get; set; } = 10;
    public int DefaultSize { get; set; } = 10;
    public int IsMultiCategory { get; set; } = 1;
    public int IsAuto { get; set; } = 1;
    public int IsManual { get; set; } = 0;
    public int IsFormula { get; set; } = 1;
    public int OrganizationUnitType { get; set; } = 0;
    public string Remarks { get; set; } = "";
    public int IsReferal { get; set; } = 0;
    public int SortOrder { get; set; } = 9999;
    public int Status { get; set; } = 1;
    public int Version { get; set; } = 1;
    public int SourceType { get; set; } = 1;
    public int IsAddress { get; set; } = 1;
    public int IsContact { get; set; } = 1;
    public int IsSearch { get; set; } = 1;
    public int SaveDeploymentId { get; set; } = -1;
    public int DeleteDeploymentId { get; set; } = -1;
    public int AddonDbObjectId { get; set; } = -1;
    public int MenuId { get; set; } = -1;
    public int WebFormId { get; set; } = -1;
    public int DisplayWebFormId { get; set; } = -1;
    public int IsIcemapRequired { get; set; } = 0;
    public int IsMultiTenant { get; set; } = 1;
    public int PicklistId { get; set; } = -1;
}

public sealed class EntityCreationConfig
{
    public AutoNumberEntityCodes AutoNumberEntityCodes { get; set; } = new();
    public int ServerConfigOffset { get; set; } = 0;
    public EntitySuffixes EntitySuffixes { get; set; } = new();
    public EntityCategories EntityCategories { get; set; } = new();
    public int EntityCodeMaxLength { get; set; } = 35;
    public DBObjectDefaults DBObjectDefaults { get; set; } = new();
    public EntityDefaults EntityDefaults { get; set; } = new();
}

// ─────────────────────────────────────────────────────────────────────────────
// Records
// ─────────────────────────────────────────────────────────────────────────────

public sealed record BatchEntry(string TableName, int ModuleId);

public sealed record BatchResult(
    string TableName,
    bool Success,
    string Message,
    int DbObjectId = 0,
    int DtoId = 0,
    int PocoId = 0,
    int PicklistId = 0);

// ─────────────────────────────────────────────────────────────────────────────
// ConsoleAutoNumber
// Mirrors GB5Shared.GenerateAutoNumber.AutoNumber (SQL Server path only).
// No DI / IQueryExecutor needed — same SQL and math, plain SqlConnection.
// ─────────────────────────────────────────────────────────────────────────────

public static class ConsoleAutoNumber
{
    // Atomically increments MAUTONUMBER.AutoId by noOfIds.
    // Returns (firstNumber, lastNumber) — same semantics as AutoNumberDTO.
    // firstNumber = newAutoId - noOfIds + serverConfigOffset  (matches GetNumberAsync math)
    public static async Task<(int StartNumber, int EndNumber)> GetAutoNumberAsync(
        SqlConnection conn, SqlTransaction tx,
        string entityCode, int noOfIds,
        int serverConfigOffset = 0)
    {
        if (noOfIds <= 0)
            throw new ArgumentException($"noOfIds must be > 0 (got {noOfIds}).", nameof(noOfIds));

        const string sql = @"
            DECLARE @OutputTable TABLE (AutoId INT);
            UPDATE MAUTONUMBER
               SET AutoId = AutoId + @NoOfId
            OUTPUT INSERTED.AutoId INTO @OutputTable
             WHERE EntityCode = @EntityCode;
            SELECT AutoId FROM @OutputTable;";

        await using var cmd = new SqlCommand(sql, conn, tx);
        cmd.Parameters.AddWithValue("@EntityCode", entityCode);
        cmd.Parameters.AddWithValue("@NoOfId", noOfIds);

        var scalar = await cmd.ExecuteScalarAsync();

        if (scalar is null or DBNull)
            throw new Exception(
                $"MAUTONUMBER row not found for EntityCode='{entityCode}'. " +
                $"Run: SELECT EntityCode, AutoId FROM MAUTONUMBER  — then update appsettings.json " +
                $"AutoNumberEntityCodes to match the correct EntityCode for this counter.");

        int newAutoId = Convert.ToInt32(scalar);
        int firstNumber = (newAutoId - noOfIds) + serverConfigOffset;
        int lastNumber = firstNumber + noOfIds - 1;

        Log($"  [AutoNumber] {entityCode,-22} +{noOfIds,3} → start={firstNumber}", ConsoleColor.DarkGray);

        return (firstNumber, lastNumber);
    }

    // Mirrors AutoNumber.cs RollbackAutoNumber.
    // Must be called on a FRESH connection — original transaction is already rolled back.
    public static async Task RollbackAutoNumberAsync(
        SqlConnection conn,
        string entityCode,
        int allocatedNumber,
        int serverConfigOffset = 0)
    {
        int rawAllocatedValue = allocatedNumber - serverConfigOffset;

        const string sql = @"
            UPDATE MAUTONUMBER
               SET    AutoId = @RollbackTarget
             WHERE  EntityCode = @EntityCode
               AND  AutoId     = @RollbackGuard";

        await using var cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@RollbackTarget", rawAllocatedValue - 1);
        cmd.Parameters.AddWithValue("@EntityCode", entityCode);
        cmd.Parameters.AddWithValue("@RollbackGuard", rawAllocatedValue);
        await cmd.ExecuteNonQueryAsync();

        Log($"  [AutoNumber] Rolled back '{entityCode}' → {rawAllocatedValue - 1}", ConsoleColor.DarkYellow);
    }

    private static void Log(string msg, ConsoleColor color)
    {
        Console.ForegroundColor = color;
        Console.WriteLine(msg);
        Console.ResetColor();
    }
}

// ─────────────────────────────────────────────────────────────────────────────
// EntityCreationEngine
// ─────────────────────────────────────────────────────────────────────────────

public sealed class EntityCreationEngine
{
    private readonly string _connectionString;
    private readonly EntityCreationConfig _cfg;

    // Whether DBObject and DBObjectFields share the same MAUTONUMBER counter.
    // When true, we do ONE combined allocation (1 + N) instead of two separate calls
    // so the IDs form a contiguous block: [dbObjectId, field1, field2, ... fieldN].
    private bool SharedCounter =>
        _cfg.AutoNumberEntityCodes.DBObject == _cfg.AutoNumberEntityCodes.DBObjectFields;

    public EntityCreationEngine(string connectionString, IConfiguration configuration)
    {
        _connectionString = connectionString
            ?? throw new ArgumentNullException(nameof(connectionString));

        _cfg = new EntityCreationConfig();
        configuration.GetSection("EntityCreation").Bind(_cfg);
    }

    // Result message constants — used by Program.cs to bucket Created / Skipped
    public const string MSG_CREATED = "CREATED";
    public const string MSG_SKIPPED = "SKIPPED";

    // ── DBOBJECT SQL ─────────────────────────────────────────────────────────

    private const string SQL_CHECK_DBOBJECT = @"
SELECT TOP 1 DBOBJECTID
FROM   DBOBJECT
WHERE  DBOBJECTNAME = @TableName";

    // Count how many INFORMATION_SCHEMA columns are NOT yet in DBOBJECTFIELDS
    // for a specific DbObjectId.
    private const string SQL_COUNT_MISSING_FIELDS = @"
SELECT COUNT(*)
FROM   INFORMATION_SCHEMA.COLUMNS A
LEFT   JOIN DBOBJECTFIELDS         C ON C.DBOBJECTID = @DbObjectId
                                    AND C.DBFIELDNAME = A.COLUMN_NAME
WHERE  A.TABLE_NAME  = @TableName
  AND  C.DBFIELDNAME IS NULL";

    // Count ALL columns in INFORMATION_SCHEMA for a table (used for new DBObjects
    // before the DBOBJECT row exists, so we cannot join via DBOBJECTFIELDS yet).
    private const string SQL_COUNT_ALL_COLUMNS = @"
SELECT COUNT(*)
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = @TableName";

    // Insert missing fields — uses pre-allocated @FieldStartId from MAUTONUMBER.
    // SLNO continues from the highest existing SLNO for the DBObject.
    private const string SQL_INSERT_DBOBJECT_FIELDS = @"
INSERT INTO DBOBJECTFIELDS
(DBOBJECTFIELDID, DbObjectId, SLNO, DBFIELDNAME, Remarks, DBFIELDATATYPE,
 DBFIELDSIZE, DBPRECISION, DBSCALE, SECTION, ISLOGICALKEY, ISCRITERIA, ISPRIMARYKEY,
 DISPLAYNAME, COLUMNTYPE, FIELDNATURE, EXPRESSION, VARIABLES, DIMENSIONID,
 DIMENSIONLEVEL, SORTON, AGGREGATIONTYPE, AGGREGATION, DIMENSIONWISE, ISFILTER,
 CRITERIAATTRIBUTEID, ISWRITEBACK, ISCURRENCY, SOURCETYPE)
SELECT
    (@FieldStartId + ROW_NUMBER() OVER (ORDER BY A.ORDINAL_POSITION) - 1) AS DBOBJECTFIELDID,
    @DbObjectId AS DbObjectId,
    (ISNULL((SELECT MAX(SLNO) FROM DBOBJECTFIELDS WHERE DBOBJECTID = @DbObjectId), 0)
      + ROW_NUMBER() OVER (ORDER BY A.ORDINAL_POSITION))                   AS SLNO,
    A.COLUMN_NAME AS DBFIELDNAME,
    A.COLUMN_NAME AS Remarks,
    CASE A.DATA_TYPE
        WHEN 'numeric'  THEN 0
        WHEN 'varchar'  THEN 1
        WHEN 'nvarchar' THEN 2
        WHEN 'datetime' THEN 3
        WHEN 'int'      THEN 6
        WHEN 'tinyint'  THEN 7
        WHEN 'smallint' THEN 8
        ELSE 0
    END AS DBFIELDATATYPE,
    CASE A.DATA_TYPE
        WHEN 'varchar'  THEN A.CHARACTER_OCTET_LENGTH
        WHEN 'nvarchar' THEN A.CHARACTER_OCTET_LENGTH
        WHEN 'datetime' THEN 8
        ELSE                 A.NUMERIC_PRECISION
    END AS DBFIELDSIZE,
    CASE A.DATA_TYPE WHEN 'numeric' THEN A.NUMERIC_PRECISION ELSE 0 END AS DBPRECISION,
    CASE A.DATA_TYPE WHEN 'numeric' THEN A.NUMERIC_SCALE     ELSE 0 END AS DBSCALE,
    A.TABLE_NAME                       AS SECTION,
    1    AS ISLOGICALKEY,
    1    AS ISCRITERIA,
    1    AS ISPRIMARYKEY,
    A.COLUMN_NAME                      AS DISPLAYNAME,
    0    AS COLUMNTYPE,
    0    AS FIELDNATURE,
    A.TABLE_NAME + '.' + A.COLUMN_NAME AS EXPRESSION,
    ''   AS VARIABLES,
    -1   AS DIMENSIONID,
    -1   AS DIMENSIONLEVEL,
    'NULL' AS SORTON,
    0    AS AGGREGATIONTYPE,
    0    AS AGGREGATION,
    'NULL' AS DIMENSIONWISE,
    0    AS ISFILTER,
    -1   AS CRITERIAATTRIBUTEID,
    0    AS ISWRITEBACK,
    0    AS ISCURRENCY,
    1    AS SOURCETYPE
FROM   INFORMATION_SCHEMA.COLUMNS A
LEFT   JOIN DBOBJECTFIELDS         C ON C.DBOBJECTID = @DbObjectId
                                    AND C.DBFIELDNAME = A.COLUMN_NAME
WHERE  A.TABLE_NAME  = @TableName
  AND  C.DBFIELDNAME IS NULL";

    // ── ENTITY SQL ───────────────────────────────────────────────────────────

    private const string SQL_CHECK_ENTITY = @"
SELECT TOP 1 ENTITYID
FROM   MENTITY
WHERE  ENTITYNAME     = @EntityName
  AND  ENTITYCATEGORY = @Category";

    private const string SQL_INSERT_ENTITY = @"
INSERT INTO MENTITY
(ENTITYID, ENTITYCODE, ENTITYNAME, BASECLASSID, SECURITYLEVEL, ENTITYTYPE, ISPERSIST, SCOPE,
 PROJECTID, MODULEID, DBOBJECTID, ENTITYNATURE, SECTION, IMPLEMENTATIONTYPE, ASSEMBLYID,
 ISCODEAPPLICABLE, MAXCODESIZE, DEFAULTSIZE, ISMULTICATEGORY, ISAUTO, ISMANUAL, ISFORMULA,
 ORGANIZATIONUNITTYPE, ENTITYDEFINEDIN, REMARKS, ISREFERAL, ENTITYCATEGORY, SORTORDER,
 STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
 POCOTODTOLINKID, POCOTOPICKLISTLINKID, CATEGORYMEMBERID,
 ISADDRESS, ISCONTACT, ISSEARCH,
 SAVEDEPLOYMENTID, DELETEDEPLOYMENTID, ADDONDBOBJECTID,
 MENUID, WEBFORMID, DISPLAYWEBFORMID,
 ISICEMAPREQUIRED, ISMULTITENANT, PICKLISTID)
VALUES
(@EntityId, @EntityCode, @EntityName, @BaseClassId, @SecurityLevel, @EntityType, @IsPersist, @Scope,
 @ProjectId, @ModuleId, @DbObjectId, @EntityNature, @Section, @ImplementationType, @AssemblyId,
 @IsCodeApplicable, @MaxCodeSize, @DefaultSize, @IsMultiCategory, @IsAuto, @IsManual, @IsFormula,
 @OrganizationUnitType, @EntityDefinedIn, @Remarks, @IsReferal, @EntityCategory, @SortOrder,
 @Status, @Version, @SourceType, @UserId, GETDATE(), @UserId, GETDATE(),
 -1, -1, -1,
 @IsAddress, @IsContact, @IsSearch,
 @SaveDeploymentId, @DeleteDeploymentId, @AddonDbObjectId,
 @MenuId, @WebFormId, @DisplayWebFormId,
 @IsIcemapRequired, @IsMultiTenant, @PicklistId)";

    // Only updates when at least one link value actually differs — safe to re-run
    private const string SQL_LINK_POCO = @"
UPDATE MENTITY SET
    POCOTODTOLINKID      = @DtoEntityId,
    POCOTOPICKLISTLINKID = @PicklistEntityId
WHERE  ENTITYID = @PocoEntityId
  AND (POCOTODTOLINKID      <> @DtoEntityId
    OR POCOTOPICKLISTLINKID <> @PicklistEntityId)";

    // ═════════════════════════════════════════════════════════════════════════
    // BATCH ENTRY POINT
    // ═════════════════════════════════════════════════════════════════════════

    public async Task<List<BatchResult>> CreateBatchAsync(
        IEnumerable<BatchEntry> entries, int assemblyId, int userId)
    {
        var results = new List<BatchResult>();
        int index = 0;
        int total = entries.Count();

        foreach (var entry in entries)
        {
            index++;
            Log($"\n[{index}/{total}] Processing '{entry.TableName}' (ModuleId={entry.ModuleId}) ...", ConsoleColor.White);

            try
            {
                var result = await CreateOneAsync(entry.TableName, entry.ModuleId, assemblyId, userId);
                results.Add(result);
            }
            catch (Exception ex)
            {
                Log($"  [FAILED] {ex.Message}", ConsoleColor.Red);
                results.Add(new BatchResult(entry.TableName, false, ex.Message));
            }
        }

        return results;
    }

    // ─────────────────────────────────────────────────────────────────────────
    // SINGLE TABLE — own connection + transaction per table.
    //
    // Skip rules:
    //   DBOBJECT      → skipped if DBOBJECTNAME already exists.
    //                   Even when skipped, missing DBOBJECTFIELDS are patched.
    //   MENTITY       → skipped if (ENTITYNAME + ENTITYCATEGORY) already exists.
    //   POCO link     → only fires UPDATE when links differ from stored values.
    //   Final status  → MSG_SKIPPED only when ALL four artefacts were pre-existing.
    //
    // Rollback rules (mirrors SaveDAPointGroup catch block):
    //   tx.Rollback() kills all inserts inside the transaction.
    //   MAUTONUMBER counters incremented inside the tx are walked back on a
    //   FRESH connection because the original tx is already dead.
    // ─────────────────────────────────────────────────────────────────────────

    private async Task<BatchResult> CreateOneAsync(
        string tableName, int moduleId, int assemblyId, int userId)
    {
        // Tracks IDs allocated from MAUTONUMBER so they can be rolled back on failure.
        // null = not allocated (skipped or not yet reached).
        int? allocatedDbObjectId = null;
        int? allocatedDtoId = null;
        int? allocatedPocoId = null;
        int? allocatedPicklistId = null;

        await using var conn = new SqlConnection(_connectionString);
        await conn.OpenAsync();
        using SqlTransaction tx = conn.BeginTransaction();

        try
        {
            // 1. DBOBJECT + DBOBJECTFIELDS
            var (dbObjectId, dbObjectIsNew, allocDbObj) =
                await EnsureDBObjectAsync(conn, tx, tableName, moduleId, userId);
            allocatedDbObjectId = allocDbObj;

            // 2. Three MENTITY rows: DTO → Poco → Picklist
            var (dtoEntityId, dtoIsNew, allocDto) = await EnsureEntityAsync(
                conn, tx,
                tableName + _cfg.EntitySuffixes.DTO,
                _cfg.EntityCategories.DTO,
                dbObjectId, moduleId, assemblyId, userId);
            allocatedDtoId = allocDto;

            var (pocoEntityId, pocoIsNew, allocPoco) = await EnsureEntityAsync(
                conn, tx,
                tableName,
                _cfg.EntityCategories.Poco,
                dbObjectId, moduleId, assemblyId, userId);
            allocatedPocoId = allocPoco;

            var (picklistEntityId, picklistIsNew, allocPicklist) = await EnsureEntityAsync(
                conn, tx,
                tableName + _cfg.EntitySuffixes.Picklist,
                _cfg.EntityCategories.Picklist,
                dbObjectId, moduleId, assemblyId, userId);
            allocatedPicklistId = allocPicklist;

            // 3. Link Poco → DTO + Picklist (no-op if already correct)
            await LinkPocoAsync(conn, tx, pocoEntityId, dtoEntityId, picklistEntityId);

            tx.Commit();

            bool anyNew = dbObjectIsNew || dtoIsNew || pocoIsNew || picklistIsNew;
            string msg = anyNew ? MSG_CREATED : MSG_SKIPPED;

            if (anyNew)
                Log($"  DONE  DBObject={dbObjectId}  DTO={dtoEntityId}  Poco={pocoEntityId}  Picklist={picklistEntityId}", ConsoleColor.Green);
            else
                Log($"  SKIPPED — all artefacts already existed  (DBObject={dbObjectId}  DTO={dtoEntityId}  Poco={pocoEntityId}  Picklist={picklistEntityId})", ConsoleColor.Yellow);

            return new BatchResult(tableName, true, msg, dbObjectId, dtoEntityId, pocoEntityId, picklistEntityId);
        }
        catch
        {
            tx.Rollback();

            // Walk back MAUTONUMBER counters on a fresh connection
            try
            {
                await using var rb = new SqlConnection(_connectionString);
                await rb.OpenAsync();

                int offset = _cfg.ServerConfigOffset;
                string dbCode = _cfg.AutoNumberEntityCodes.DBObject;
                string entCode = _cfg.AutoNumberEntityCodes.Entity;

                if (allocatedDbObjectId.HasValue)
                    await ConsoleAutoNumber.RollbackAutoNumberAsync(rb, dbCode, allocatedDbObjectId.Value, offset);
                if (allocatedDtoId.HasValue)
                    await ConsoleAutoNumber.RollbackAutoNumberAsync(rb, entCode, allocatedDtoId.Value, offset);
                if (allocatedPocoId.HasValue)
                    await ConsoleAutoNumber.RollbackAutoNumberAsync(rb, entCode, allocatedPocoId.Value, offset);
                if (allocatedPicklistId.HasValue)
                    await ConsoleAutoNumber.RollbackAutoNumberAsync(rb, entCode, allocatedPicklistId.Value, offset);
            }
            catch (Exception rbEx)
            {
                Log($"  [AutoNumber Rollback FAILED] {rbEx.Message}", ConsoleColor.Red);
            }

            throw;
        }
    }

    // ─────────────────────────────────────────────────────────────────────────
    // EnsureDBObjectAsync
    // Returns (DbObjectId, IsNew, AllocatedId).
    // AllocatedId is non-null only when a new DBOBJECT row was inserted.
    //
    // KEY: when DBObject and DBObjectFields share the same MAUTONUMBER counter
    // (SharedCounter = true), we do ONE combined call: allocate (1 + columnCount)
    // IDs in a single UPDATE so they form a contiguous block with no gap:
    //   firstId         → DBOBJECTID
    //   firstId+1 .. N → DBOBJECTFIELDID values
    // This avoids the collision that would happen with two separate calls.
    // ─────────────────────────────────────────────────────────────────────────

    private async Task<(int DbObjectId, bool IsNew, int? AllocatedId)> EnsureDBObjectAsync(
        SqlConnection conn, SqlTransaction tx,
        string tableName, int moduleId, int userId)
    {
        int? allocatedId = null;
        var d = _cfg.DBObjectDefaults;
        int offset = _cfg.ServerConfigOffset;

        // ── Check existing ──────────────────────────────────────────────────
        await using var checkCmd = new SqlCommand(SQL_CHECK_DBOBJECT, conn, tx);
        checkCmd.Parameters.AddWithValue("@TableName", tableName);
        var existing = await checkCmd.ExecuteScalarAsync();

        if (existing is not null and not DBNull)
        {
            int existingId = Convert.ToInt32(existing);
            Log($"  [DBObject] Already exists DBOBJECTID={existingId} — checking fields ...", ConsoleColor.Yellow);
            await EnsureDBObjectFieldsAsync(conn, tx, tableName, existingId, preallocatedStartId: null);
            return (existingId, false, null);
        }

        // ── Count all columns (new DBObject — no DBOBJECTFIELDS row exists yet) ──
        await using var countCmd = new SqlCommand(SQL_COUNT_ALL_COLUMNS, conn, tx);
        countCmd.Parameters.AddWithValue("@TableName", tableName);
        int columnCount = Convert.ToInt32(await countCmd.ExecuteScalarAsync());

        if (columnCount == 0)
            Log($"  [DBObject] Warning: '{tableName}' not found in INFORMATION_SCHEMA — verify the table name.", ConsoleColor.Yellow);

        // ── Allocate IDs from MAUTONUMBER ────────────────────────────────────
        int dbObjectId = 0;
        int fieldStartId = 0;

        if (SharedCounter && columnCount > 0)
        {
            // Single combined allocation: 1 (DBObject) + N (fields) from same counter
            int totalNeeded = 1 + columnCount;
            var (firstId, _) = await ConsoleAutoNumber.GetAutoNumberAsync(
                conn, tx, _cfg.AutoNumberEntityCodes.DBObject, totalNeeded, offset);
            dbObjectId = firstId;           // first ID in the block → DBObject
            fieldStartId = firstId + 1;       // remainder → fields
            allocatedId = dbObjectId;
            Log($"  [AutoNumber] Combined block: DBObject={dbObjectId}  Fields={fieldStartId}..{fieldStartId + columnCount - 1}", ConsoleColor.DarkGray);
        }
        else
        {
            // Separate counters — allocate independently
            var (dbo, _) = await ConsoleAutoNumber.GetAutoNumberAsync(
                conn, tx, _cfg.AutoNumberEntityCodes.DBObject, 1, offset);
            dbObjectId = dbo;
            allocatedId = dbObjectId;

            if (columnCount > 0)
            {
                var (fld, _) = await ConsoleAutoNumber.GetAutoNumberAsync(
                    conn, tx, _cfg.AutoNumberEntityCodes.DBObjectFields, columnCount, offset);
                fieldStartId = fld;
            }
        }

        // ── Insert DBOBJECT ─────────────────────────────────────────────────
        const string insertSql = @"
INSERT INTO DBOBJECT
(DBOBJECTID, DBOBJECTNAME, MODULEID, SECTION, DBOBJECTTYPE, REMARKS, SORTORDER,
 STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
 ISSTANDARDDATAREQUIRED, DATASOURCETYPEID, DISPLAYNAME, OBJECTNATURE, TABLETYPE,
 ISANALYSIS, BASEOBJECTID, CONDITION, ISSYSTEM, ISDEVELOPER, ISIMPLEMENTATION,
 ISADMIN, ISUSER, EXPRESSION, VARIABLES, QUERYTYPE, ISARCHIVE, REPLAYUNIT,
 REPLAYAMOUNT, PARENTOBJECTID, GROUPDBOBJECTID, DATEKEY)
VALUES
(@DbObjectId, @TableName, @ModuleId, @TableName,
 @DbObjectType, @Remarks, @SortOrder,
 @Status, @Version, @SourceType, @UserId, GETDATE(), @UserId, GETDATE(),
 @IsStandardDataRequired, @DataSourceTypeId, @DisplayName, @ObjectNature, @TableType,
 @IsAnalysis, @BaseObjectId, @Condition, @IsSystem, @IsDeveloper, @IsImplementation,
 @IsAdmin, @IsUser, @Expression, @Variables, @QueryType, @IsArchive, @ReplayUnit,
 @ReplayAmount, @ParentObjectId, @DbObjectId, @DateKey)";

        await using var insertCmd = new SqlCommand(insertSql, conn, tx);
        insertCmd.Parameters.AddWithValue("@DbObjectId", dbObjectId);
        insertCmd.Parameters.AddWithValue("@TableName", tableName);
        insertCmd.Parameters.AddWithValue("@ModuleId", moduleId);
        insertCmd.Parameters.AddWithValue("@UserId", userId);
        insertCmd.Parameters.AddWithValue("@DbObjectType", d.DbObjectType);
        insertCmd.Parameters.AddWithValue("@Remarks", d.Remarks);
        insertCmd.Parameters.AddWithValue("@SortOrder", d.SortOrder);
        insertCmd.Parameters.AddWithValue("@Status", d.Status);
        insertCmd.Parameters.AddWithValue("@Version", d.Version);
        insertCmd.Parameters.AddWithValue("@SourceType", d.SourceType);
        insertCmd.Parameters.AddWithValue("@IsStandardDataRequired", d.IsStandardDataRequired);
        insertCmd.Parameters.AddWithValue("@DataSourceTypeId", d.DataSourceTypeId);
        insertCmd.Parameters.AddWithValue("@DisplayName", d.DisplayName);
        insertCmd.Parameters.AddWithValue("@ObjectNature", d.ObjectNature);
        insertCmd.Parameters.AddWithValue("@TableType", d.TableType);
        insertCmd.Parameters.AddWithValue("@IsAnalysis", d.IsAnalysis);
        insertCmd.Parameters.AddWithValue("@BaseObjectId", d.BaseObjectId);
        insertCmd.Parameters.AddWithValue("@Condition", d.Condition);
        insertCmd.Parameters.AddWithValue("@IsSystem", d.IsSystem);
        insertCmd.Parameters.AddWithValue("@IsDeveloper", d.IsDeveloper);
        insertCmd.Parameters.AddWithValue("@IsImplementation", d.IsImplementation);
        insertCmd.Parameters.AddWithValue("@IsAdmin", d.IsAdmin);
        insertCmd.Parameters.AddWithValue("@IsUser", d.IsUser);
        insertCmd.Parameters.AddWithValue("@Expression", d.Expression);
        insertCmd.Parameters.AddWithValue("@Variables", d.Variables);
        insertCmd.Parameters.AddWithValue("@QueryType", d.QueryType);
        insertCmd.Parameters.AddWithValue("@IsArchive", d.IsArchive);
        insertCmd.Parameters.AddWithValue("@ReplayUnit", d.ReplayUnit);
        insertCmd.Parameters.AddWithValue("@ReplayAmount", d.ReplayAmount);
        insertCmd.Parameters.AddWithValue("@ParentObjectId", d.ParentObjectId);
        insertCmd.Parameters.AddWithValue("@DateKey", d.DateKey);
        await insertCmd.ExecuteNonQueryAsync();
        Log($"  [DBObject] Inserted DBOBJECTID={dbObjectId}", ConsoleColor.Cyan);

        // ── Insert DBOBJECTFIELDS ───────────────────────────────────────────
        if (columnCount > 0)
            await EnsureDBObjectFieldsAsync(conn, tx, tableName, dbObjectId,
                preallocatedStartId: fieldStartId);

        return (dbObjectId, true, allocatedId);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // EnsureDBObjectFieldsAsync
    // Inserts columns from INFORMATION_SCHEMA that are not yet in DBOBJECTFIELDS.
    //
    // preallocatedStartId != null → IDs already reserved (new DBObject path).
    // preallocatedStartId == null → count missing first, then allocate fresh IDs.
    // ─────────────────────────────────────────────────────────────────────────

    private async Task EnsureDBObjectFieldsAsync(
        SqlConnection conn, SqlTransaction tx,
        string tableName, int dbObjectId,
        int? preallocatedStartId)
    {
        // Count columns not yet mapped for this DbObjectId
        await using var countCmd = new SqlCommand(SQL_COUNT_MISSING_FIELDS, conn, tx);
        countCmd.Parameters.AddWithValue("@DbObjectId", dbObjectId);
        countCmd.Parameters.AddWithValue("@TableName", tableName);
        int missing = Convert.ToInt32(await countCmd.ExecuteScalarAsync());

        if (missing == 0)
        {
            Log($"  [DBObjectFields] All fields already mapped — no changes.", ConsoleColor.Yellow);
            return;
        }

        int fieldStartId;

        if (preallocatedStartId.HasValue)
        {
            // IDs were pre-allocated as part of the combined DBObject allocation
            fieldStartId = preallocatedStartId.Value;
        }
        else
        {
            // Existing DBObject — allocate fresh IDs for however many are missing
            var (startId, _) = await ConsoleAutoNumber.GetAutoNumberAsync(
                conn, tx,
                _cfg.AutoNumberEntityCodes.DBObjectFields,
                missing,
                _cfg.ServerConfigOffset);
            fieldStartId = startId;
        }

        await using var fieldsCmd = new SqlCommand(SQL_INSERT_DBOBJECT_FIELDS, conn, tx);
        fieldsCmd.Parameters.AddWithValue("@FieldStartId", fieldStartId);
        fieldsCmd.Parameters.AddWithValue("@DbObjectId", dbObjectId);
        fieldsCmd.Parameters.AddWithValue("@TableName", tableName);
        int inserted = await fieldsCmd.ExecuteNonQueryAsync();

        Log($"  [DBObjectFields] Inserted {inserted} field(s)  (IDs {fieldStartId}..{fieldStartId + inserted - 1})", ConsoleColor.Cyan);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // EnsureEntityAsync
    // Returns (EntityId, IsNew, AllocatedId).
    // AllocatedId is non-null only when a new MENTITY row was inserted.
    // Skips insert if (EntityName + EntityCategory) already exists.
    // ─────────────────────────────────────────────────────────────────────────

    private async Task<(int EntityId, bool IsNew, int? AllocatedId)> EnsureEntityAsync(
        SqlConnection conn, SqlTransaction tx,
        string entityName, int entityCategory,
        int dbObjectId, int moduleId, int assemblyId, int userId)
    {
        int? allocatedId = null;
        var e = _cfg.EntityDefaults;

        string label = entityCategory == _cfg.EntityCategories.DTO ? "DTO"
                     : entityCategory == _cfg.EntityCategories.Poco ? "Poco"
                     : entityCategory == _cfg.EntityCategories.Picklist ? "Picklist"
                     : entityCategory.ToString();

        // ── Check existing ──────────────────────────────────────────────────
        await using var checkCmd = new SqlCommand(SQL_CHECK_ENTITY, conn, tx);
        checkCmd.Parameters.AddWithValue("@EntityName", entityName);
        checkCmd.Parameters.AddWithValue("@Category", entityCategory);
        var existing = await checkCmd.ExecuteScalarAsync();

        if (existing is not null and not DBNull)
        {
            int existingId = Convert.ToInt32(existing);
            Log($"  [Entity/{label}] Already exists ENTITYID={existingId} (skip)", ConsoleColor.Yellow);
            return (existingId, false, null);
        }

        // ── Allocate ID from MAUTONUMBER ─────────────────────────────────────
        var (entityId, _) = await ConsoleAutoNumber.GetAutoNumberAsync(
            conn, tx, _cfg.AutoNumberEntityCodes.Entity, 1, _cfg.ServerConfigOffset);
        allocatedId = entityId;

        // EntityCode truncated to configurable max length
        string entityCode = entityName.Length > _cfg.EntityCodeMaxLength
            ? entityName[.._cfg.EntityCodeMaxLength]
            : entityName;

        // ── Insert MENTITY ──────────────────────────────────────────────────
        // ENTITYNATURE: Poco=5, DTO=1, Picklist=1  (mirrors reference data)
        int entityNature = entityCategory == _cfg.EntityCategories.Poco ? e.EntityNature : 1;
        // ISPERSIST: Poco=1, DTO=0, Picklist=1
        int isPersist = entityCategory == _cfg.EntityCategories.DTO ? 0 : 1;

        await using var insertCmd = new SqlCommand(SQL_INSERT_ENTITY, conn, tx);
        insertCmd.Parameters.AddWithValue("@EntityId", entityId);
        insertCmd.Parameters.AddWithValue("@EntityCode", entityCode);
        insertCmd.Parameters.AddWithValue("@EntityName", entityName);
        insertCmd.Parameters.AddWithValue("@ModuleId", moduleId);
        insertCmd.Parameters.AddWithValue("@DbObjectId", dbObjectId);
        insertCmd.Parameters.AddWithValue("@AssemblyId", assemblyId);
        insertCmd.Parameters.AddWithValue("@EntityCategory", entityCategory);
        insertCmd.Parameters.AddWithValue("@EntityDefinedIn", entityName);
        insertCmd.Parameters.AddWithValue("@UserId", userId);
        insertCmd.Parameters.AddWithValue("@BaseClassId", e.BaseClassId);
        insertCmd.Parameters.AddWithValue("@SecurityLevel", e.SecurityLevel);
        insertCmd.Parameters.AddWithValue("@EntityType", e.EntityType);
        insertCmd.Parameters.AddWithValue("@IsPersist", isPersist);
        insertCmd.Parameters.AddWithValue("@Scope", e.Scope);
        insertCmd.Parameters.AddWithValue("@ProjectId", e.ProjectId);
        insertCmd.Parameters.AddWithValue("@EntityNature", entityNature);
        insertCmd.Parameters.AddWithValue("@Section", e.Section);
        insertCmd.Parameters.AddWithValue("@ImplementationType", e.ImplementationType);
        insertCmd.Parameters.AddWithValue("@IsCodeApplicable", e.IsCodeApplicable);
        insertCmd.Parameters.AddWithValue("@MaxCodeSize", e.MaxCodeSize);
        insertCmd.Parameters.AddWithValue("@DefaultSize", e.DefaultSize);
        insertCmd.Parameters.AddWithValue("@IsMultiCategory", e.IsMultiCategory);
        insertCmd.Parameters.AddWithValue("@IsAuto", e.IsAuto);
        insertCmd.Parameters.AddWithValue("@IsManual", e.IsManual);
        insertCmd.Parameters.AddWithValue("@IsFormula", e.IsFormula);
        insertCmd.Parameters.AddWithValue("@OrganizationUnitType", e.OrganizationUnitType);
        insertCmd.Parameters.AddWithValue("@Remarks", e.Remarks);
        insertCmd.Parameters.AddWithValue("@IsReferal", e.IsReferal);
        insertCmd.Parameters.AddWithValue("@SortOrder", e.SortOrder);
        insertCmd.Parameters.AddWithValue("@Status", e.Status);
        insertCmd.Parameters.AddWithValue("@Version", e.Version);
        insertCmd.Parameters.AddWithValue("@SourceType", e.SourceType);
        insertCmd.Parameters.AddWithValue("@IsAddress", e.IsAddress);
        insertCmd.Parameters.AddWithValue("@IsContact", e.IsContact);
        insertCmd.Parameters.AddWithValue("@IsSearch", e.IsSearch);
        insertCmd.Parameters.AddWithValue("@SaveDeploymentId", e.SaveDeploymentId);
        insertCmd.Parameters.AddWithValue("@DeleteDeploymentId", e.DeleteDeploymentId);
        insertCmd.Parameters.AddWithValue("@AddonDbObjectId", e.AddonDbObjectId);
        insertCmd.Parameters.AddWithValue("@MenuId", e.MenuId);
        insertCmd.Parameters.AddWithValue("@WebFormId", e.WebFormId);
        insertCmd.Parameters.AddWithValue("@DisplayWebFormId", e.DisplayWebFormId);
        insertCmd.Parameters.AddWithValue("@IsIcemapRequired", e.IsIcemapRequired);
        insertCmd.Parameters.AddWithValue("@IsMultiTenant", e.IsMultiTenant);
        insertCmd.Parameters.AddWithValue("@PicklistId", e.PicklistId);
        await insertCmd.ExecuteNonQueryAsync();

        Log($"  [Entity/{label}] Inserted ENTITYID={entityId} '{entityName}'", ConsoleColor.Cyan);
        return (entityId, true, allocatedId);
    }

    // ─────────────────────────────────────────────────────────────────────────
    // LinkPocoAsync — only fires UPDATE when link values actually differ
    // ─────────────────────────────────────────────────────────────────────────

    private static async Task LinkPocoAsync(
        SqlConnection conn, SqlTransaction tx,
        int pocoEntityId, int dtoEntityId, int picklistEntityId)
    {
        await using var cmd = new SqlCommand(SQL_LINK_POCO, conn, tx);
        cmd.Parameters.AddWithValue("@PocoEntityId", pocoEntityId);
        cmd.Parameters.AddWithValue("@DtoEntityId", dtoEntityId);
        cmd.Parameters.AddWithValue("@PicklistEntityId", picklistEntityId);
        int rows = await cmd.ExecuteNonQueryAsync();

        if (rows > 0)
            Log($"  [Entity/Poco] Linked → DTO={dtoEntityId}  Picklist={picklistEntityId}", ConsoleColor.Cyan);
        else
            Log($"  [Entity/Poco] Links already correct — no update needed.", ConsoleColor.Yellow);
    }

    private static void Log(string msg, ConsoleColor color)
    {
        Console.ForegroundColor = color;
        Console.WriteLine(msg);
        Console.ResetColor();
    }
}