// Full Base Schema loader -- see FullBaseSchemaLoader.csproj's own header comment for context.
//
// Usage:
//   dotnet run -- \
//     --connection-string "Data Source=...;Initial Catalog=Gb5system;User Id=...;Password=...;TrustServerCertificate=True" \
//     --chunks-dir "/path/to/DB/Migrations/20260824_FullBaseSchema_SqlServer" \
//     --dbmodel-id 1 --dbmodel-code ENTITLEMENT_BASELINE_V1 \
//     --branch-id 1 --branch-code MAIN \
//     --package-code FULL_BASE_SCHEMA_V1 --sequence-in-chain 0 \
//     --tenant-id 1 --created-by-id -1 \
//     [--dry-run]
//
// --dbmodel-id/--dbmodel-code should normally match the SAME DbModel an existing baseline
// package (e.g. ENTITLEMENT_BASELINE_V1_PKG) is already registered under -- this package is
// meant to sit ahead of that one in the same provisioning chain (--sequence-in-chain 0, one
// below whatever the existing baseline's own SequenceInChain is), not a separate DbModel.
// If --dbmodel-id doesn't already exist, a new MSWDBMODEL row is created for it (needs
// --dbserver-id in that case).
//
// Every write here is existence-checked first (by natural key), so re-running this tool against
// a target it already partially/fully loaded is safe -- it will only insert what's missing.

using System.Data;
using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json;
using System.Text.RegularExpressions;
using Microsoft.Data.SqlClient;

var opts = Args.Parse(args);
if (opts is null) return 1;

Console.WriteLine($"Chunks dir      : {opts.ChunksDir}");
Console.WriteLine($"DbModel         : id={opts.DbModelId} code={opts.DbModelCode}");
Console.WriteLine($"ScriptBranch    : id={opts.BranchId} code={opts.BranchCode}");
Console.WriteLine($"Package         : code={opts.PackageCode} sequenceInChain={opts.SequenceInChain}");
Console.WriteLine($"TenantId={opts.TenantId} CreatedById={opts.CreatedById} DryRun={opts.DryRun}");
Console.WriteLine();

var scripts = ChunkParser.LoadAllScripts(opts.ChunksDir);
Console.WriteLine($"Parsed {scripts.Count} individual scripts from the manifest-ordered chunk files.");

if (opts.DryRun)
{
    Console.WriteLine("--dry-run: no database connection opened. Sample of first 3 parsed scripts:");
    foreach (var s in scripts.Take(3))
        Console.WriteLine($"  [{s.BatLine}] {s.ObjectName} ({s.SqlScript.Length} chars, sha256={s.Sha256[..12]}...)");
    return 0;
}

await using var conn = new SqlConnection(opts.ConnectionString);
await conn.OpenAsync();
Console.WriteLine($"Connected to {conn.DataSource}/{conn.Database}.");

int dbModelId = await Loader.EnsureDbModelAsync(conn, opts);
int branchId  = await Loader.EnsureScriptBranchAsync(conn, opts, dbModelId);

int firstDdlScriptId = await AutoNumber.ReserveAsync(conn, "SWDDLSCRIPT", scripts.Count);
Console.WriteLine($"Reserved DDLSCRIPTID range [{firstDdlScriptId}, {firstDdlScriptId + scripts.Count - 1}] from MAUTONUMBER.");

int inserted = await Loader.InsertDdlScriptsAsync(conn, opts, dbModelId, branchId, firstDdlScriptId, scripts);
Console.WriteLine($"Inserted {inserted} new SW.MSWDDLSCRIPT rows ({scripts.Count - inserted} already existed, skipped).");

int packageId = await Loader.EnsureUpgradePackageAsync(conn, opts, dbModelId);
int linked = await Loader.LinkPackageDdlAsync(conn, packageId, firstDdlScriptId, scripts.Count, opts.TenantId);
Console.WriteLine($"Linked {linked} SW.MSWUPGRADEPACKAGEDDL rows to package {opts.PackageCode} (id={packageId}).");

Console.WriteLine();
Console.WriteLine("Done. Next step: use SwSL's own GetProvisioningChain(DbModelId) to confirm this");
Console.WriteLine($"package (SequenceInChain={opts.SequenceInChain}) resolves ahead of any existing baseline package.");
return 0;

// ============================================================================================
sealed record ParsedScript(int BatLine, string SourcePath, string ObjectName, string SqlScript, string Sha256);

sealed class Args
{
    public required string ConnectionString;
    public required string ChunksDir;
    public required int DbModelId;
    public required string DbModelCode;
    public int? DbServerId;
    public required int BranchId;
    public required string BranchCode;
    public required string PackageCode;
    public required byte SequenceInChain;
    public required int TenantId;
    public required int CreatedById;
    public bool DryRun;

    public static Args? Parse(string[] argv)
    {
        string? Get(string name) =>
            argv.SkipWhile(a => a != name).Skip(1).FirstOrDefault();

        var connStr = Get("--connection-string");
        var chunksDir = Get("--chunks-dir");
        var dryRun = argv.Contains("--dry-run");

        if (chunksDir is null || !Directory.Exists(chunksDir))
        {
            Console.Error.WriteLine("ERROR: --chunks-dir is required and must point at an existing directory " +
                "(DB/Migrations/20260824_FullBaseSchema_SqlServer).");
            return null;
        }
        if (!dryRun && string.IsNullOrWhiteSpace(connStr))
        {
            Console.Error.WriteLine("ERROR: --connection-string is required unless --dry-run is passed.");
            return null;
        }

        int ParseIntOr(string name, int def) =>
            int.TryParse(Get(name), out var v) ? v : def;

        return new Args
        {
            ConnectionString = connStr ?? string.Empty,
            ChunksDir        = chunksDir,
            DbModelId        = ParseIntOr("--dbmodel-id", 1),
            DbModelCode      = Get("--dbmodel-code") ?? "ENTITLEMENT_BASELINE_V1",
            DbServerId       = int.TryParse(Get("--dbserver-id"), out var dsid) ? dsid : null,
            BranchId         = ParseIntOr("--branch-id", 1),
            BranchCode       = Get("--branch-code") ?? "MAIN",
            PackageCode      = Get("--package-code") ?? "FULL_BASE_SCHEMA_V1",
            SequenceInChain  = (byte)ParseIntOr("--sequence-in-chain", 0),
            TenantId         = ParseIntOr("--tenant-id", 1),
            CreatedById      = ParseIntOr("--created-by-id", -1),
            DryRun           = dryRun,
        };
    }
}

static class ChunkParser
{
    // Matches this repo's own established marker convention, e.g.:
    // -- ===== SOURCE: devsys\MCLIENT.txt (createtable.bat line 18) =====
    private static readonly Regex MarkerRegex = new(
        @"^-- =====\s*SOURCE:\s*(?<path>.+?)\s*\(createtable\.bat line (?<line>\d+)\)\s*=====\s*$",
        RegexOptions.Multiline | RegexOptions.Compiled);

    public static List<ParsedScript> LoadAllScripts(string chunksDir)
    {
        var manifestPath = Path.Combine(chunksDir, "manifest.json");
        using var manifestDoc = JsonDocument.Parse(File.ReadAllText(manifestPath));
        var expectedHashes = new Dictionary<int, string>(); // bat_line -> sha256, best-effort cross-check only
        foreach (var chunk in manifestDoc.RootElement.GetProperty("chunks").EnumerateArray())
        foreach (var item in chunk.GetProperty("items").EnumerateArray())
            expectedHashes[item.GetProperty("bat_line").GetInt32()] = item.GetProperty("sha256").GetString() ?? "";

        var chunkFiles = Directory.GetFiles(chunksDir, "chunk_*_of_*.sql").OrderBy(f => f, StringComparer.Ordinal).ToArray();
        var results = new List<ParsedScript>();
        int hashMismatches = 0;

        foreach (var file in chunkFiles)
        {
            var text = File.ReadAllText(file);
            var matches = MarkerRegex.Matches(text).ToArray();
            for (int i = 0; i < matches.Length; i++)
            {
                var m = matches[i];
                int start = m.Index + m.Length;
                int end = i + 1 < matches.Length ? matches[i + 1].Index : text.Length;
                var sqlScript = text[start..end].Trim();
                if (sqlScript.Length == 0) continue;

                int batLine = int.Parse(m.Groups["line"].Value, CultureInfo.InvariantCulture);
                var sourcePath = m.Groups["path"].Value.Trim();
                var objectName = Path.GetFileNameWithoutExtension(sourcePath.Replace('\\', '/'))
                    .ToUpperInvariant();

                // Best-effort integrity cross-check against the manifest's own recorded hash --
                // the manifest hashes the ORIGINAL individual source file bytes, which may not
                // byte-for-byte match this chunk-extracted, trimmed text (line-ending/whitespace
                // normalization during chunk assembly) -- so a mismatch here is logged, not fatal.
                var actualHash = Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(sqlScript))).ToLowerInvariant();
                if (expectedHashes.TryGetValue(batLine, out var expected) && !string.IsNullOrEmpty(expected) && expected != actualHash)
                    hashMismatches++;

                results.Add(new ParsedScript(batLine, sourcePath, objectName, sqlScript, actualHash));
            }
        }

        if (hashMismatches > 0)
            Console.WriteLine($"NOTE: {hashMismatches}/{results.Count} scripts' content hash didn't match the manifest's " +
                "recorded source-file hash (expected -- manifest hashes original file bytes, chunk extraction trims/normalizes " +
                "whitespace). Content itself is the real, captured legacy DDL either way; not treated as fatal.");

        return results.OrderBy(r => r.BatLine).ToList();
    }
}

static class AutoNumber
{
    // Mirrors GB5Shared.GenerateAutoNumber.AutoNumber.GetNumberAsync's own SQL exactly (SQL
    // Server branch) -- same OUTPUT-INTO-table-variable trigger-safe pattern, same
    // newAutoId-minus-noOfId-plus-1 first-number arithmetic -- so this tool's own reservations
    // stay byte-for-byte consistent with the live app's own AutoNumber semantics.
    public static async Task<int> ReserveAsync(SqlConnection conn, string entityCode, int noOfId)
    {
        const string sql = @"
            DECLARE @OutputTable TABLE (AutoId BIGINT);
            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);
        cmd.Parameters.AddWithValue("@NoOfId", noOfId);
        cmd.Parameters.AddWithValue("@EntityCode", entityCode);
        var result = await cmd.ExecuteScalarAsync()
            ?? throw new InvalidOperationException($"MAUTONUMBER has no row for EntityCode='{entityCode}' -- seed it first.");
        long newAutoId = Convert.ToInt64(result);
        return (int)(newAutoId - noOfId);
    }
}

static class Loader
{
    public static async Task<int> EnsureDbModelAsync(SqlConnection conn, Args opts)
    {
        await using (var check = new SqlCommand(
            "SELECT DBMODELID FROM SW.MSWDBMODEL WHERE DBMODELID = @Id OR DBMODELCODE = @Code", conn))
        {
            check.Parameters.AddWithValue("@Id", opts.DbModelId);
            check.Parameters.AddWithValue("@Code", opts.DbModelCode);
            var existing = await check.ExecuteScalarAsync();
            if (existing is not null)
            {
                Console.WriteLine($"MSWDBMODEL '{opts.DbModelCode}' already exists (id={existing}) -- reusing.");
                return Convert.ToInt32(existing);
            }
        }

        if (opts.DbServerId is null)
            throw new InvalidOperationException(
                $"MSWDBMODEL '{opts.DbModelCode}' (id={opts.DbModelId}) doesn't exist yet -- pass --dbserver-id to create it.");

        var now = DateTime.UtcNow;
        await using var insert = new SqlCommand(@"
            INSERT INTO SW.MSWDBMODEL
                (DBMODELID, DBMODELCODE, DBMODELNAME, DESCRIPTION, DBSERVERID, DATABASENAME,
                 VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
            VALUES
                (@Id, @Code, @Code, @Desc, @ServerId, @Code,
                 0, 1, 9999, @CreatedById, @Now, @CreatedById, @Now, 5, @TenantId)", conn);
        insert.Parameters.AddWithValue("@Id", opts.DbModelId);
        insert.Parameters.AddWithValue("@Code", opts.DbModelCode);
        insert.Parameters.AddWithValue("@Desc", "Created by FullBaseSchemaLoader");
        insert.Parameters.AddWithValue("@ServerId", opts.DbServerId.Value);
        insert.Parameters.AddWithValue("@CreatedById", opts.CreatedById);
        insert.Parameters.AddWithValue("@Now", now);
        insert.Parameters.AddWithValue("@TenantId", opts.TenantId);
        await insert.ExecuteNonQueryAsync();
        Console.WriteLine($"Created new MSWDBMODEL '{opts.DbModelCode}' (id={opts.DbModelId}).");
        return opts.DbModelId;
    }

    public static async Task<int> EnsureScriptBranchAsync(SqlConnection conn, Args opts, int dbModelId)
    {
        await using (var check = new SqlCommand(
            "SELECT BRANCHID FROM SW.MSWSCRIPTBRANCH WHERE BRANCHID = @Id OR (BRANCHCODE = @Code AND DBMODELID = @DbModelId)", conn))
        {
            check.Parameters.AddWithValue("@Id", opts.BranchId);
            check.Parameters.AddWithValue("@Code", opts.BranchCode);
            check.Parameters.AddWithValue("@DbModelId", dbModelId);
            var existing = await check.ExecuteScalarAsync();
            if (existing is not null)
            {
                Console.WriteLine($"MSWSCRIPTBRANCH '{opts.BranchCode}' already exists (id={existing}) -- reusing.");
                return Convert.ToInt32(existing);
            }
        }

        var now = DateTime.UtcNow;
        await using var insert = new SqlCommand(@"
            INSERT INTO SW.MSWSCRIPTBRANCH
                (BRANCHID, DBMODELID, BRANCHCODE, BRANCHNAME, BRANCHTYPE, BRANCHSTATUS, PARENTBRANCHID,
                 DESCRIPTION, VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
            VALUES
                (@Id, @DbModelId, @Code, @Code, 0, 0, NULL,
                 @Desc, 0, 1, 9999, @CreatedById, @Now, @CreatedById, @Now, 5, @TenantId)", conn);
        insert.Parameters.AddWithValue("@Id", opts.BranchId);
        insert.Parameters.AddWithValue("@DbModelId", dbModelId);
        insert.Parameters.AddWithValue("@Code", opts.BranchCode);
        insert.Parameters.AddWithValue("@Desc", "Created by FullBaseSchemaLoader");
        insert.Parameters.AddWithValue("@CreatedById", opts.CreatedById);
        insert.Parameters.AddWithValue("@Now", now);
        insert.Parameters.AddWithValue("@TenantId", opts.TenantId);
        await insert.ExecuteNonQueryAsync();
        Console.WriteLine($"Created new MSWSCRIPTBRANCH '{opts.BranchCode}' (id={opts.BranchId}).");
        return opts.BranchId;
    }

    public static async Task<int> InsertDdlScriptsAsync(
        SqlConnection conn, Args opts, int dbModelId, int branchId, int firstDdlScriptId, List<ParsedScript> scripts)
    {
        // Skip any script whose OBJECTNAME + DBMODELID already has a row -- makes a retried/
        // partial run safe without needing a separate "have I already run this" flag file.
        var existingNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        await using (var existing = new SqlCommand(
            "SELECT OBJECTNAME FROM SW.MSWDDLSCRIPT WHERE DBMODELID = @DbModelId AND BRANCHID = @BranchId", conn))
        {
            existing.Parameters.AddWithValue("@DbModelId", dbModelId);
            existing.Parameters.AddWithValue("@BranchId", branchId);
            await using var reader = await existing.ExecuteReaderAsync();
            while (await reader.ReadAsync())
                existingNames.Add(reader.GetString(0));
        }

        var toInsert = scripts.Where(s => !existingNames.Contains(s.ObjectName)).ToList();
        if (toInsert.Count == 0) return 0;

        var table = new DataTable();
        table.Columns.Add("DDLSCRIPTID", typeof(int));
        table.Columns.Add("DBMODELID", typeof(int));
        table.Columns.Add("BRANCHID", typeof(int));
        table.Columns.Add("OBJECTGROUPID", typeof(int));
        table.Columns.Add("OBJECTTYPE", typeof(byte));
        table.Columns.Add("SCHEMANAME", typeof(string));
        table.Columns.Add("OBJECTNAME", typeof(string));
        table.Columns.Add("SQLSCRIPT", typeof(string));
        table.Columns.Add("ROLLBACKSQL", typeof(string));
        table.Columns.Add("CHECKSUM", typeof(string));
        table.Columns.Add("SCRIPTSTATUS", typeof(byte));
        table.Columns.Add("APPROVALREQUIRED", typeof(byte));
        table.Columns.Add("APPROVEDBYID", typeof(int));
        table.Columns.Add("APPROVEDON", typeof(DateTime));
        table.Columns.Add("SEQUENCE", typeof(short));
        table.Columns.Add("VERSION", typeof(short));
        table.Columns.Add("STATUS", typeof(byte));
        table.Columns.Add("SORTORDER", typeof(short));
        table.Columns.Add("CREATEDBYID", typeof(int));
        table.Columns.Add("CREATEDON", typeof(DateTime));
        table.Columns.Add("MODIFIEDBYID", typeof(int));
        table.Columns.Add("MODIFIEDON", typeof(DateTime));
        table.Columns.Add("SOURCETYPE", typeof(byte));
        table.Columns.Add("TENANTID", typeof(int));
        table.Columns.Add("CHANGEREQUESTID", typeof(int));
        table.Columns["CHANGEREQUESTID"]!.AllowDBNull = true;

        var now = DateTime.UtcNow;
        // scripts is the FULL ordered list -- each script's DDLSCRIPTID must correspond to its
        // own position in that full list, not just among the ones we're inserting, so a
        // retried run keeps every script's DDLSCRIPTID stable across runs. Keyed on BatLine
        // (guaranteed unique per parsed script) rather than the record itself.
        var idByBatLine = scripts.Select((s, idx) => (s.BatLine, id: firstDdlScriptId + idx))
            .ToDictionary(t => t.BatLine, t => t.id);

        int seq = 1;
        foreach (var s in toInsert)
        {
            var row = table.NewRow();
            row["DDLSCRIPTID"]     = idByBatLine[s.BatLine];
            row["DBMODELID"]       = dbModelId;
            row["BRANCHID"]        = branchId;
            row["OBJECTGROUPID"]   = -1; // the already-seeded "NONE" sentinel (033_MSWOBJECTGROUP_SeedNoneSentinel.sql)
            row["OBJECTTYPE"]      = (byte)0; // no strict enum exists for this column; 0 = unspecified/generic
            row["SCHEMANAME"]      = "dbo";
            row["OBJECTNAME"]      = s.ObjectName;
            row["SQLSCRIPT"]       = s.SqlScript;
            row["ROLLBACKSQL"]     = DBNull.Value;
            row["CHECKSUM"]        = s.Sha256; // matches DdlScriptBLL.ComputeChecksum's own algorithm (lowercase hex SHA-256, trimmed)
            row["SCRIPTSTATUS"]    = (byte)2; // Approved -- bulk historical load of already-vetted legacy DDL
            row["APPROVALREQUIRED"] = (byte)0;
            row["APPROVEDBYID"]    = opts.CreatedById;
            row["APPROVEDON"]      = now;
            row["SEQUENCE"]        = (short)Math.Min(seq++, short.MaxValue);
            row["VERSION"]         = (short)0;
            row["STATUS"]          = (byte)1;
            row["SORTORDER"]       = (short)9999;
            row["CREATEDBYID"]     = opts.CreatedById;
            row["CREATEDON"]       = now;
            row["MODIFIEDBYID"]    = opts.CreatedById;
            row["MODIFIEDON"]      = now;
            row["SOURCETYPE"]      = (byte)5;
            row["TENANTID"]        = opts.TenantId;
            row["CHANGEREQUESTID"] = DBNull.Value; // nullable -- this bulk historical load isn't tied to any Change Request
            table.Rows.Add(row);
        }

        using var bulk = new SqlBulkCopy(conn) { DestinationTableName = "SW.MSWDDLSCRIPT", BulkCopyTimeout = 300 };
        foreach (DataColumn col in table.Columns)
            bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
        await bulk.WriteToServerAsync(table);

        return toInsert.Count;
    }

    public static async Task<int> EnsureUpgradePackageAsync(SqlConnection conn, Args opts, int dbModelId)
    {
        await using (var check = new SqlCommand(
            "SELECT PACKAGEID FROM SW.MSWUPGRADEPACKAGE WHERE PACKAGECODE = @Code AND DBMODELID = @DbModelId", conn))
        {
            check.Parameters.AddWithValue("@Code", opts.PackageCode);
            check.Parameters.AddWithValue("@DbModelId", dbModelId);
            var existing = await check.ExecuteScalarAsync();
            if (existing is not null)
            {
                Console.WriteLine($"MSWUPGRADEPACKAGE '{opts.PackageCode}' already exists (id={existing}) -- reusing.");
                return Convert.ToInt32(existing);
            }
        }

        int packageId = await AutoNumber.ReserveAsync(conn, "SWUPGRADEPACKAGE", 1);
        var now = DateTime.UtcNow;
        await using var insert = new SqlCommand(@"
            INSERT INTO SW.MSWUPGRADEPACKAGE
                (PACKAGEID, DBMODELID, PACKAGENAME, PACKAGECODE, PKGSTATUS, DESCRIPTION,
                 SEQUENCEINCHAIN, SUPERSEDESPACKAGEID, SCOPETYPE, SCOPEVALUE, RELEASEVERSION,
                 ROLLOUTTYPE, ROLLOUTPERCENT, PACKAGECONTENTTYPE, REQUIREDFEATURECODE,
                 VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
                 SOURCETYPE, TENANTID)
            VALUES
                (@Id, @DbModelId, @Code, @Code, 2, @Desc,
                 @Seq, NULL, 0, NULL, NULL,
                 0, 100, 0, NULL,
                 0, 1, 9999, @CreatedById, @Now, @CreatedById, @Now,
                 5, @TenantId)", conn);
        insert.Parameters.AddWithValue("@Id", packageId);
        insert.Parameters.AddWithValue("@DbModelId", dbModelId);
        insert.Parameters.AddWithValue("@Code", opts.PackageCode);
        insert.Parameters.AddWithValue("@Desc", "Full legacy base schema (1,534 scripts) -- loaded by FullBaseSchemaLoader");
        insert.Parameters.AddWithValue("@Seq", opts.SequenceInChain);
        insert.Parameters.AddWithValue("@CreatedById", opts.CreatedById);
        insert.Parameters.AddWithValue("@Now", now);
        insert.Parameters.AddWithValue("@TenantId", opts.TenantId);
        await insert.ExecuteNonQueryAsync();
        Console.WriteLine($"Created new MSWUPGRADEPACKAGE '{opts.PackageCode}' (id={packageId}), Released, SequenceInChain={opts.SequenceInChain}.");
        return packageId;
    }

    public static async Task<int> LinkPackageDdlAsync(SqlConnection conn, int packageId, int firstDdlScriptId, int scriptCount, int tenantId)
    {
        var existingLinks = new HashSet<int>();
        await using (var existing = new SqlCommand(
            "SELECT DDLSCRIPTID FROM SW.MSWUPGRADEPACKAGEDDL WHERE PACKAGEID = @PackageId", conn))
        {
            existing.Parameters.AddWithValue("@PackageId", packageId);
            await using var reader = await existing.ExecuteReaderAsync();
            while (await reader.ReadAsync())
                existingLinks.Add(reader.GetInt32(0));
        }

        var table = new DataTable();
        table.Columns.Add("PACKAGEDDLID", typeof(int));
        table.Columns.Add("PACKAGEID", typeof(int));
        table.Columns.Add("DDLSCRIPTID", typeof(int));
        table.Columns.Add("SEQUENCE", typeof(short));
        table.Columns.Add("TENANTID", typeof(int));

        // PackageDdlId scheme: packageId * 10000 + sequence -- same packageId-prefixed synthetic
        // key idea UpgradePackageDAL.ReplaceDdlLines already uses (packageId * 1000 + sequence),
        // just with a wider per-package ID space (*10000) since this package alone carries 1,534
        // lines, more than the *1000 scheme could safely hold without colliding with the next
        // package's own range.
        int linked = 0;
        for (int i = 0; i < scriptCount; i++)
        {
            int ddlScriptId = firstDdlScriptId + i;
            if (existingLinks.Contains(ddlScriptId)) continue;

            var row = table.NewRow();
            row["PACKAGEDDLID"] = packageId * 10000 + (i + 1);
            row["PACKAGEID"]    = packageId;
            row["DDLSCRIPTID"]  = ddlScriptId;
            row["SEQUENCE"]     = (short)Math.Min(i + 1, short.MaxValue);
            row["TENANTID"]     = tenantId;
            table.Rows.Add(row);
            linked++;
        }

        if (linked == 0) return 0;

        using var bulk = new SqlBulkCopy(conn) { DestinationTableName = "SW.MSWUPGRADEPACKAGEDDL", BulkCopyTimeout = 300 };
        foreach (DataColumn col in table.Columns)
            bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
        await bulk.WriteToServerAsync(table);

        return linked;
    }
}