using Microsoft.Data.SqlClient;

namespace SwPackagePromote;

public sealed record PromoteResult(
    int DbModelId, int PackageId, bool PackageCreated,
    int UnchangedCount, int PromotedCount, int LinkedCount);

/// <summary>
/// The actual git-style "does the target already have this content" logic — see this project's
/// own .csproj header for the full design. Every DdlScriptId/PackageId is local to whichever
/// Gb5system it's read from; the only thing compared across the two independent installations
/// is CHECKSUM (portable, content-addressed, exactly like a git blob's own hash) and
/// PackageCode/DbModelCode (human-assigned, portable identifiers — the same convention
/// FullBaseSchemaLoader already established for exactly this reason).
/// </summary>
public static class GovernanceImporter
{
    public static async Task<PromoteResult> PromoteAsync(SqlConnection conn, OfflinePackageBundle bundle, PromoteOptions opts)
    {
        int dbModelId = await ResolveDbModelIdAsync(conn, opts.DbModelCode);
        int branchId = await EnsureScriptBranchAsync(conn, opts.BranchId, opts.BranchCode, dbModelId, opts.CreatedById, opts.TenantId);

        int unchanged = 0, promoted = 0;
        var currentDdlScriptIds = new List<int>();

        foreach (var script in bundle.Scripts.OrderBy(s => s.Sequence))
        {
            var objectName = script.ObjectName ?? throw new InvalidOperationException("A script in the bundle has no ObjectName.");
            var checksum = script.Checksum ?? throw new InvalidOperationException($"Script '{objectName}' has no Checksum.");
            var objectType = script.ObjectType;

            // Keyed on (ObjectName, ObjectType), not ObjectName alone — a real, live-caught bug
            // otherwise: two scripts legitimately sharing one ObjectName (e.g. MCURRENCY's own
            // real CREATE TABLE + a separate reference-data seed) would flip-flop each other's
            // checksum comparison within the SAME promotion run, never converging to
            // "unchanged" even on an exact re-run of identical content.
            var tip = await GetCurrentTipAsync(conn, dbModelId, objectName, objectType, opts.TenantId);

            if (tip is not null && string.Equals(tip.Value.Checksum, checksum, StringComparison.OrdinalIgnoreCase))
            {
                // Content already matches the target's own current version — git's own
                // "nothing to commit" case. Skip the write entirely; the existing DdlScriptId
                // is what the package's own link should point at.
                unchanged++;
                currentDdlScriptIds.Add(tip.Value.DdlScriptId);
                Console.WriteLine($"  [{script.Sequence}] {objectName}: unchanged (checksum matches target's current tip) — skipped.");
                continue;
            }

            int newDdlScriptId = await InsertNewScriptVersionAsync(
                conn, dbModelId, branchId, objectName, objectType, script.SqlScript, checksum, opts.CreatedById, opts.TenantId);

            if (tip is not null)
                await ClearCurrentTipAsync(conn, dbModelId, objectName, objectType, opts.CreatedById, opts.TenantId);

            await InsertHistoryVersionAsync(
                conn, dbModelId, objectName, objectType, newDdlScriptId, tip?.DdlScriptId, checksum, opts.CreatedById, opts.TenantId);

            promoted++;
            currentDdlScriptIds.Add(newDdlScriptId);
            Console.WriteLine(tip is null
                ? $"  [{script.Sequence}] {objectName}: new object — registered as DdlScriptId={newDdlScriptId}."
                : $"  [{script.Sequence}] {objectName}: changed (was DdlScriptId={tip.Value.DdlScriptId}) — new version DdlScriptId={newDdlScriptId}.");
        }

        var (packageId, created) = await EnsureUpgradePackageAsync(conn, bundle, dbModelId, opts.SequenceInChain, opts.CreatedById, opts.TenantId);
        int linked = await RelinkPackageAsync(conn, packageId, currentDdlScriptIds, opts.TenantId);

        return new PromoteResult(dbModelId, packageId, created, unchanged, promoted, linked);
    }

    private static async Task<int> ResolveDbModelIdAsync(SqlConnection conn, string dbModelCode)
    {
        await using var cmd = new SqlCommand("SELECT DBMODELID FROM SW.MSWDBMODEL WHERE DBMODELCODE = @Code", conn);
        cmd.Parameters.AddWithValue("@Code", dbModelCode);
        var result = await cmd.ExecuteScalarAsync()
            ?? throw new InvalidOperationException(
                $"No SW.MSWDBMODEL row with DBMODELCODE='{dbModelCode}' exists on this target — register it first " +
                "(this tool deliberately never creates one; which physical server/database a DbModel maps to is a " +
                "real operational decision for whoever manages this target's own SqlWorkbench, not something to " +
                "infer from a package file).");
        return Convert.ToInt32(result);
    }

    private static async Task<int> EnsureScriptBranchAsync(
        SqlConnection conn, int branchId, string branchCode, int dbModelId, int createdById, int tenantId)
    {
        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", branchId);
            check.Parameters.AddWithValue("@Code", branchCode);
            check.Parameters.AddWithValue("@DbModelId", dbModelId);
            var existing = await check.ExecuteScalarAsync();
            if (existing is not null) 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", branchId);
        insert.Parameters.AddWithValue("@DbModelId", dbModelId);
        insert.Parameters.AddWithValue("@Code", branchCode);
        insert.Parameters.AddWithValue("@Desc", "Created by SwPackagePromote");
        insert.Parameters.AddWithValue("@CreatedById", createdById);
        insert.Parameters.AddWithValue("@Now", now);
        insert.Parameters.AddWithValue("@TenantId", tenantId);
        await insert.ExecuteNonQueryAsync();
        return branchId;
    }

    private static async Task<(int DdlScriptId, string Checksum)?> GetCurrentTipAsync(
        SqlConnection conn, int dbModelId, string objectName, int objectType, int tenantId)
    {
        await using var cmd = new SqlCommand(@"
            SELECT DDLSCRIPTID, CHECKSUM FROM SW.MSWDDLOBJECTHISTORY
            WHERE DBMODELID = @DbModelId AND SCHEMANAME = 'dbo' AND OBJECTNAME = @ObjectName
              AND OBJECTTYPE = @ObjectType AND ISCURRENTTIP = 1 AND TENANTID = @TenantId", conn);
        cmd.Parameters.AddWithValue("@DbModelId", dbModelId);
        cmd.Parameters.AddWithValue("@ObjectName", objectName);
        cmd.Parameters.AddWithValue("@ObjectType", objectType);
        cmd.Parameters.AddWithValue("@TenantId", tenantId);

        await using var reader = await cmd.ExecuteReaderAsync();
        if (!await reader.ReadAsync()) return null;
        return (reader.GetInt32(0), reader.GetString(1));
    }

    private static async Task<int> InsertNewScriptVersionAsync(
        SqlConnection conn, int dbModelId, int branchId, string objectName, int objectType, string sqlScript, string checksum,
        int createdById, int tenantId)
    {
        int ddlScriptId = await AutoNumber.ReserveAsync(conn, "SWDDLSCRIPT", 1);
        var now = DateTime.UtcNow;

        await using var insert = new SqlCommand(@"
            INSERT INTO SW.MSWDDLSCRIPT
                (DDLSCRIPTID, DBMODELID, BRANCHID, OBJECTGROUPID, OBJECTTYPE, SCHEMANAME,
                 OBJECTNAME, SQLSCRIPT, ROLLBACKSQL, CHECKSUM, SCRIPTSTATUS, APPROVALREQUIRED,
                 APPROVEDBYID, APPROVEDON, SEQUENCE,
                 VERSION, STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
                 SOURCETYPE, TENANTID, CHANGEREQUESTID)
            VALUES
                (@Id, @DbModelId, @BranchId, -1, @ObjectType, 'dbo',
                 @ObjectName, @SqlScript, NULL, @Checksum, 2, 0,
                 @CreatedById, @Now, 1,
                 0, 1, 9999, @CreatedById, @Now, @CreatedById, @Now,
                 5, @TenantId, NULL)", conn);
        insert.Parameters.AddWithValue("@Id", ddlScriptId);
        insert.Parameters.AddWithValue("@DbModelId", dbModelId);
        insert.Parameters.AddWithValue("@BranchId", branchId);
        insert.Parameters.AddWithValue("@ObjectName", objectName);
        insert.Parameters.AddWithValue("@ObjectType", objectType);
        insert.Parameters.AddWithValue("@SqlScript", sqlScript);
        insert.Parameters.AddWithValue("@Checksum", checksum);
        insert.Parameters.AddWithValue("@CreatedById", createdById);
        insert.Parameters.AddWithValue("@Now", now);
        insert.Parameters.AddWithValue("@TenantId", tenantId);
        await insert.ExecuteNonQueryAsync();

        return ddlScriptId;
    }

    private static async Task ClearCurrentTipAsync(SqlConnection conn, int dbModelId, string objectName, int objectType, int modifiedById, int tenantId)
    {
        await using var cmd = new SqlCommand(@"
            UPDATE SW.MSWDDLOBJECTHISTORY
            SET ISCURRENTTIP = 0, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @Now
            WHERE DBMODELID = @DbModelId AND SCHEMANAME = 'dbo' AND OBJECTNAME = @ObjectName
              AND OBJECTTYPE = @ObjectType AND ISCURRENTTIP = 1 AND TENANTID = @TenantId", conn);
        cmd.Parameters.AddWithValue("@DbModelId", dbModelId);
        cmd.Parameters.AddWithValue("@ObjectName", objectName);
        cmd.Parameters.AddWithValue("@ObjectType", objectType);
        cmd.Parameters.AddWithValue("@ModifiedById", modifiedById);
        cmd.Parameters.AddWithValue("@Now", DateTime.UtcNow);
        cmd.Parameters.AddWithValue("@TenantId", tenantId);
        await cmd.ExecuteNonQueryAsync();
    }

    private static async Task InsertHistoryVersionAsync(
        SqlConnection conn, int dbModelId, string objectName, int objectType, int ddlScriptId, int? previousDdlScriptId,
        string checksum, int createdById, int tenantId)
    {
        int historyId = await AutoNumber.ReserveAsync(conn, "SWDDLOBJECTHISTORY", 1);
        var now = DateTime.UtcNow;

        await using var insert = new SqlCommand(@"
            INSERT INTO SW.MSWDDLOBJECTHISTORY
                (DDLOBJECTHISTORYID, DBMODELID, SCHEMANAME, OBJECTNAME, OBJECTTYPE, DDLSCRIPTID, PREVIOUSDDLSCRIPTID,
                 CHECKSUM, ISCURRENTTIP, VERSION, STATUS, SORTORDER,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
            VALUES
                (@Id, @DbModelId, 'dbo', @ObjectName, @ObjectType, @DdlScriptId, @PreviousDdlScriptId,
                 @Checksum, 1, 0, 1, 9999,
                 @CreatedById, @Now, @CreatedById, @Now, 5, @TenantId)", conn);
        insert.Parameters.AddWithValue("@Id", historyId);
        insert.Parameters.AddWithValue("@DbModelId", dbModelId);
        insert.Parameters.AddWithValue("@ObjectName", objectName);
        insert.Parameters.AddWithValue("@ObjectType", objectType);
        insert.Parameters.AddWithValue("@DdlScriptId", ddlScriptId);
        insert.Parameters.AddWithValue("@PreviousDdlScriptId", (object?)previousDdlScriptId ?? DBNull.Value);
        insert.Parameters.AddWithValue("@Checksum", checksum);
        insert.Parameters.AddWithValue("@CreatedById", createdById);
        insert.Parameters.AddWithValue("@Now", now);
        insert.Parameters.AddWithValue("@TenantId", tenantId);
        await insert.ExecuteNonQueryAsync();
    }

    private static async Task<(int PackageId, bool Created)> EnsureUpgradePackageAsync(
        SqlConnection conn, OfflinePackageBundle bundle, int dbModelId, byte sequenceInChain, int createdById, int tenantId)
    {
        var packageCode = bundle.PackageCode ?? throw new InvalidOperationException("Bundle has no PackageCode.");

        await using (var check = new SqlCommand(
            "SELECT PACKAGEID FROM SW.MSWUPGRADEPACKAGE WHERE PACKAGECODE = @Code AND DBMODELID = @DbModelId", conn))
        {
            check.Parameters.AddWithValue("@Code", packageCode);
            check.Parameters.AddWithValue("@DbModelId", dbModelId);
            var existing = await check.ExecuteScalarAsync();
            if (existing is not null) return (Convert.ToInt32(existing), false);
        }

        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, @ReleaseVersion,
                 0, 100, @ContentType, 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", packageCode);
        insert.Parameters.AddWithValue("@Desc", $"Promoted from another Gb5system by SwPackagePromote (source PackageId={bundle.PackageId})");
        insert.Parameters.AddWithValue("@Seq", sequenceInChain);
        insert.Parameters.AddWithValue("@ReleaseVersion", (object?)bundle.ReleaseVersion ?? DBNull.Value);
        insert.Parameters.AddWithValue("@ContentType", bundle.PackageContentType);
        insert.Parameters.AddWithValue("@CreatedById", createdById);
        insert.Parameters.AddWithValue("@Now", now);
        insert.Parameters.AddWithValue("@TenantId", tenantId);
        await insert.ExecuteNonQueryAsync();

        return (packageId, true);
    }

    private static async Task<int> RelinkPackageAsync(SqlConnection conn, int packageId, List<int> ddlScriptIds, int tenantId)
    {
        // Delete-then-reinsert — same semantics as UpgradePackageDAL.ReplaceDdlLines, applied
        // here since this is a fresh, standalone tool with no shared DAL reference. Idempotent:
        // re-running this against a target it already promoted to just rewrites the same links.
        await using (var delete = new SqlCommand("DELETE FROM SW.MSWUPGRADEPACKAGEDDL WHERE PACKAGEID = @PackageId", conn))
        {
            delete.Parameters.AddWithValue("@PackageId", packageId);
            await delete.ExecuteNonQueryAsync();
        }

        int linked = 0;
        for (int i = 0; i < ddlScriptIds.Count; i++)
        {
            await using var insert = new SqlCommand(@"
                INSERT INTO SW.MSWUPGRADEPACKAGEDDL (PACKAGEDDLID, PACKAGEID, DDLSCRIPTID, SEQUENCE, TENANTID)
                VALUES (@Id, @PackageId, @DdlScriptId, @Sequence, @TenantId)", conn);
            insert.Parameters.AddWithValue("@Id", packageId * 10000 + (i + 1));
            insert.Parameters.AddWithValue("@PackageId", packageId);
            insert.Parameters.AddWithValue("@DdlScriptId", ddlScriptIds[i]);
            insert.Parameters.AddWithValue("@Sequence", (short)Math.Min(i + 1, short.MaxValue));
            insert.Parameters.AddWithValue("@TenantId", tenantId);
            await insert.ExecuteNonQueryAsync();
            linked++;
        }

        return linked;
    }
}

/// <summary>Same MAUTONUMBER reservation SQL as FullBaseSchemaLoader's own AutoNumber helper —
/// duplicated, not shared, per this tool's own zero-live-dependency rule (no project reference
/// to another standalone tool, same reasoning as OfflinePackageDecryptor's duplication).</summary>
internal static class AutoNumber
{
    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}' on this target — seed it first.");
        long newAutoId = Convert.ToInt64(result);
        return (int)(newAutoId - noOfId);
    }
}