using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using Moq;
using SwBLL.ClientDatabase;
using SwBLL.DbServer;
using SwBLL.DdlScript;
using SwBLL.Provisioning;
using SwBLL.UpgradePackage;
using SwBLL.UserResourceRole;
using SwDAL.DTO.ClientDatabase;
using SwDAL.DTO.Provisioning;
using SwDAL.DTO.UpgradePackage;
using SwDAL.Enums;
using SwDAL.Query.Provisioning;
using Xunit;

namespace SwTests;

/// <summary>
/// Covers ProvisioningBLL.GetCurrentVersion — resolves the currently-applied release version
/// for a ClientDatabaseId by finding the most recently fully-applied package (via
/// IQueryExecutor + ProvisioningLogQB directly, matching this class's own "no foreign DALs"
/// architecture rule — same pattern LogProvisioningAsync already uses for this table's writes),
/// then walking SupersedesPackageId back through the DbModel's lineage (IUpgradePackageBLL.GetLineage)
/// to the nearest package carrying a non-null ReleaseVersion. "Unversioned" (no ReleaseVersion
/// ever reached) is a legitimate result, not an exception — most of these tests assert that.
///
/// IDdlScriptBLL/IDbServerBLL/ITargetDbExecutor/IUserResourceRoleBLL are passed null! —
/// GetCurrentVersion never touches them (only ProvisionClientDatabase does), same "safe because
/// unused" convention as UpgradePackageBLLTests.
/// </summary>
public class ProvisioningBLLTests
{
    private static (ProvisioningBLL Svc, Mock<IClientDatabaseBLL> ClientDatabaseBLL,
        Mock<IUpgradePackageBLL> UpgradePackageBLL, Mock<IQueryExecutor> QueryExecutor) BuildService()
    {
        var clientDatabaseBLL = new Mock<IClientDatabaseBLL>();
        var upgradePackageBLL = new Mock<IUpgradePackageBLL>();
        var queryExecutor     = new Mock<IQueryExecutor>();

        var svc = new ProvisioningBLL(
            clientDatabaseBLL.Object,
            ddlScriptBLL: null!,
            upgradePackageBLL.Object,
            dbServerBLL: null!,
            targetDbExecutor: null!,
            userResourceRoleBLL: null!,
            queryExecutor.Object);

        return (svc, clientDatabaseBLL, upgradePackageBLL, queryExecutor);
    }

    private static readonly LoginDTO Login = new() { ClientId = 42, UserId = 1 };

    private static void SetupMostRecentlyApplied(Mock<IQueryExecutor> queryExecutor, AppliedPackageDTO? result)
        => queryExecutor.Setup(q => q.QuerySingleAsync<AppliedPackageDTO>(
                It.IsAny<LoginDTO>(), ProvisioningLogQB.GET_MOST_RECENTLY_APPLIED_PACKAGE,
                It.IsAny<object>(), null, false, It.IsAny<CancellationToken>()))
            .ReturnsAsync(result!);

    [Fact]
    public async Task GetCurrentVersion_ZeroClientDbId_ThrowsWithoutTouchingDeps()
    {
        var (svc, clientDatabaseBLL, upgradePackageBLL, queryExecutor) = BuildService();

        await Assert.ThrowsAsync<ArgumentException>(() => svc.GetCurrentVersion(0, Login, default));

        clientDatabaseBLL.Verify(c => c.GetById(It.IsAny<int>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
        upgradePackageBLL.Verify(u => u.GetLineage(It.IsAny<int>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    [Fact]
    public async Task GetCurrentVersion_ClientDbNotFound_Throws()
    {
        var (svc, clientDatabaseBLL, _, _) = BuildService();
        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default)).ReturnsAsync((ClientDatabaseDTO?)null);

        await Assert.ThrowsAsync<InvalidOperationException>(() => svc.GetCurrentVersion(5, Login, default));
    }

    [Fact]
    public async Task GetCurrentVersion_NothingEverApplied_ReturnsUnversionedNotError()
    {
        var (svc, clientDatabaseBLL, _, queryExecutor) = BuildService();
        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default)).ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, DbModelId = 7 });
        SetupMostRecentlyApplied(queryExecutor, null);

        var result = await svc.GetCurrentVersion(5, Login, default);

        Assert.False(result.IsVersioned);
        Assert.Null(result.CurrentReleaseVersion);
        Assert.Null(result.MostRecentlyAppliedPackageId);
    }

    [Fact]
    public async Task GetCurrentVersion_MostRecentPackageItselfVersioned_ReturnsItsVersionDirectly()
    {
        var (svc, clientDatabaseBLL, upgradePackageBLL, queryExecutor) = BuildService();
        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default)).ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, DbModelId = 7 });
        SetupMostRecentlyApplied(queryExecutor, new AppliedPackageDTO { PackageId = 100, ReleaseVersion = "4.2.0", DbModelId = 7 });
        upgradePackageBLL.Setup(u => u.GetLineage(7, Login, default)).ReturnsAsync(new[]
        {
            new LineageNodeDTO { PackageId = 100, ReleaseVersion = "4.2.0", SupersedesPackageId = null },
        });

        var result = await svc.GetCurrentVersion(5, Login, default);

        Assert.True(result.IsVersioned);
        Assert.Equal("4.2.0", result.CurrentReleaseVersion);
        Assert.Equal(100, result.ResolvedFromPackageId);
        Assert.Equal(100, result.MostRecentlyAppliedPackageId);
    }

    [Fact]
    public async Task GetCurrentVersion_MostRecentPackageUnversioned_WalksToNearestVersionedAncestor()
    {
        var (svc, clientDatabaseBLL, upgradePackageBLL, queryExecutor) = BuildService();
        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default)).ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, DbModelId = 7 });
        // Most recently applied is a client feature-bundle (unversioned) that supersedes an
        // intermediate unversioned hotfix, which in turn supersedes the release boundary.
        SetupMostRecentlyApplied(queryExecutor, new AppliedPackageDTO { PackageId = 102, ReleaseVersion = null, DbModelId = 7 });
        upgradePackageBLL.Setup(u => u.GetLineage(7, Login, default)).ReturnsAsync(new[]
        {
            new LineageNodeDTO { PackageId = 100, ReleaseVersion = "4.2.0", SupersedesPackageId = null },
            new LineageNodeDTO { PackageId = 101, ReleaseVersion = null,    SupersedesPackageId = 100 },
            new LineageNodeDTO { PackageId = 102, ReleaseVersion = null,    SupersedesPackageId = 101 },
        });

        var result = await svc.GetCurrentVersion(5, Login, default);

        Assert.True(result.IsVersioned);
        Assert.Equal("4.2.0", result.CurrentReleaseVersion);
        Assert.Equal(100, result.ResolvedFromPackageId);
        Assert.Equal(102, result.MostRecentlyAppliedPackageId);
    }

    [Fact]
    public async Task GetCurrentVersion_ChainRootReachedWithNoVersion_ReturnsUnversioned()
    {
        var (svc, clientDatabaseBLL, upgradePackageBLL, queryExecutor) = BuildService();
        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default)).ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, DbModelId = 7 });
        SetupMostRecentlyApplied(queryExecutor, new AppliedPackageDTO { PackageId = 101, ReleaseVersion = null, DbModelId = 7 });
        upgradePackageBLL.Setup(u => u.GetLineage(7, Login, default)).ReturnsAsync(new[]
        {
            new LineageNodeDTO { PackageId = 100, ReleaseVersion = null, SupersedesPackageId = null },
            new LineageNodeDTO { PackageId = 101, ReleaseVersion = null, SupersedesPackageId = 100 },
        });

        var result = await svc.GetCurrentVersion(5, Login, default);

        Assert.False(result.IsVersioned);
        Assert.Null(result.CurrentReleaseVersion);
        Assert.Equal(101, result.MostRecentlyAppliedPackageId);
    }

    [Fact]
    public async Task GetCurrentVersion_MissingLineageNode_StopsWalkGracefully()
    {
        var (svc, clientDatabaseBLL, upgradePackageBLL, queryExecutor) = BuildService();
        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default)).ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, DbModelId = 7 });
        // The most-recently-applied package itself isn't present in the fetched lineage —
        // a data-integrity edge case (e.g. cross-DbModel drift) — must not throw.
        SetupMostRecentlyApplied(queryExecutor, new AppliedPackageDTO { PackageId = 999, ReleaseVersion = null, DbModelId = 7 });
        upgradePackageBLL.Setup(u => u.GetLineage(7, Login, default)).ReturnsAsync(new[]
        {
            new LineageNodeDTO { PackageId = 100, ReleaseVersion = "4.2.0", SupersedesPackageId = null },
        });

        var result = await svc.GetCurrentVersion(5, Login, default);

        Assert.False(result.IsVersioned);
        Assert.Null(result.CurrentReleaseVersion);
    }

    [Fact]
    public async Task GetCurrentVersion_CyclicSupersedesChain_TerminatesViaVisitedGuard()
    {
        var (svc, clientDatabaseBLL, upgradePackageBLL, queryExecutor) = BuildService();
        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default)).ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, DbModelId = 7 });
        SetupMostRecentlyApplied(queryExecutor, new AppliedPackageDTO { PackageId = 100, ReleaseVersion = null, DbModelId = 7 });
        // 100 -> 101 -> 100 -> ... a corrupt/cyclic SupersedesPackageId graph, neither ever versioned.
        upgradePackageBLL.Setup(u => u.GetLineage(7, Login, default)).ReturnsAsync(new[]
        {
            new LineageNodeDTO { PackageId = 100, ReleaseVersion = null, SupersedesPackageId = 101 },
            new LineageNodeDTO { PackageId = 101, ReleaseVersion = null, SupersedesPackageId = 100 },
        });

        var result = await svc.GetCurrentVersion(5, Login, default);

        Assert.False(result.IsVersioned);
        Assert.Null(result.CurrentReleaseVersion);
    }

    // ── ProvisionClientDatabase — {{TENANTID}} substitution (tracker §35 Pillar 3) ──────────

    private static (ProvisioningBLL Svc, Mock<IClientDatabaseBLL> ClientDatabaseBLL,
        Mock<IDdlScriptBLL> DdlScriptBLL, Mock<IUpgradePackageBLL> UpgradePackageBLL,
        Mock<IDbServerBLL> DbServerBLL, Mock<ITargetDbExecutor> TargetDbExecutor,
        Mock<IUserResourceRoleBLL> UserResourceRoleBLL) BuildServiceForProvisioning()
    {
        var clientDatabaseBLL = new Mock<IClientDatabaseBLL>();
        var ddlScriptBLL      = new Mock<IDdlScriptBLL>();
        var upgradePackageBLL = new Mock<IUpgradePackageBLL>();
        var dbServerBLL       = new Mock<IDbServerBLL>();
        var targetDbExecutor  = new Mock<ITargetDbExecutor>();
        var userResourceRoleBLL = new Mock<IUserResourceRoleBLL>();
        var queryExecutor     = new Mock<IQueryExecutor>();

        var svc = new ProvisioningBLL(
            clientDatabaseBLL.Object,
            ddlScriptBLL.Object,
            upgradePackageBLL.Object,
            dbServerBLL.Object,
            targetDbExecutor.Object,
            userResourceRoleBLL.Object,
            queryExecutor.Object);

        return (svc, clientDatabaseBLL, ddlScriptBLL, upgradePackageBLL, dbServerBLL, targetDbExecutor, userResourceRoleBLL);
    }

    [Fact]
    public async Task ProvisionClientDatabase_ScriptContainsTenantIdToken_SubstitutesRealTenantIdBeforeExecuting()
    {
        var (svc, clientDatabaseBLL, ddlScriptBLL, upgradePackageBLL, dbServerBLL, targetDbExecutor, _) = BuildServiceForProvisioning();

        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default))
            .ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, DbServerId = 1, DatabaseName = "TestDb", TenantId = -1399999706 });
        dbServerBLL.Setup(d => d.GetByIdWithCredentials(1, Login, default))
            .ReturnsAsync(new SwDAL.DTO.DbServer.DbServerDTO { DbServerId = 1 });
        targetDbExecutor.Setup(t => t.BuildConnectionString(It.IsAny<SwDAL.DTO.DbServer.DbServerDTO>(), "TestDb", null, null))
            .Returns("fake-conn-str");
        upgradePackageBLL.Setup(u => u.GetDdlLines(10, Login, default))
            .ReturnsAsync(new[] { new UpgradePackageDdlDTO { PackageDdlId = 1, PackageId = 10, DdlScriptId = 200, Sequence = 1 } });
        ddlScriptBLL.Setup(d => d.GetById(200, Login, default))
            .ReturnsAsync(new SwDAL.DTO.DdlScript.DdlScriptDTO
            {
                DdlScriptId = 200,
                ScriptStatus = (byte)DdlScriptStatus.Approved,
                SqlScript = "INSERT INTO MCURRENCY (TENANTID) VALUES ({{TENANTID}})",
            });

        string? capturedSql = null;
        targetDbExecutor.Setup(t => t.ExecuteScriptAsync("fake-conn-str", It.IsAny<string>(), default))
            .Callback<string, string, CancellationToken>((_, sql, _) => capturedSql = sql)
            .ReturnsAsync(1);

        await svc.ProvisionClientDatabase(5, 10, Login, default);

        Assert.Equal("INSERT INTO MCURRENCY (TENANTID) VALUES (-1399999706)", capturedSql);
    }

    // ── ProvisionClientDatabase — per-user role-grant gate (SW.MSWUSERRESOURCEROLE) ────────
    // Provisioning always uses the flat DbServer admin connection (contained users don't exist
    // yet) — the gate is one level up, fail-closed ONLY when an explicit, insufficient grant is
    // on record; no grant configured at all is fail-open, same posture as the module's other
    // enforcement call sites.

    private static void SetupMinimalProvisioning(
        Mock<IClientDatabaseBLL> clientDatabaseBLL, Mock<IDbServerBLL> dbServerBLL,
        Mock<IUpgradePackageBLL> upgradePackageBLL, Mock<IDdlScriptBLL> ddlScriptBLL,
        Mock<ITargetDbExecutor> targetDbExecutor)
    {
        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default))
            .ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, DbServerId = 1, DatabaseName = "TestDb" });
        dbServerBLL.Setup(d => d.GetByIdWithCredentials(1, Login, default))
            .ReturnsAsync(new SwDAL.DTO.DbServer.DbServerDTO { DbServerId = 1 });
        targetDbExecutor.Setup(t => t.BuildConnectionString(It.IsAny<SwDAL.DTO.DbServer.DbServerDTO>(), "TestDb", null, null))
            .Returns("fake-conn-str");
        upgradePackageBLL.Setup(u => u.GetDdlLines(10, Login, default))
            .ReturnsAsync(new[] { new UpgradePackageDdlDTO { PackageDdlId = 1, PackageId = 10, DdlScriptId = 200, Sequence = 1 } });
        ddlScriptBLL.Setup(d => d.GetById(200, Login, default))
            .ReturnsAsync(new SwDAL.DTO.DdlScript.DdlScriptDTO
            {
                DdlScriptId = 200,
                ScriptStatus = (byte)DdlScriptStatus.Approved,
                SqlScript = "SELECT 1",
            });
        targetDbExecutor.Setup(t => t.ExecuteScriptAsync("fake-conn-str", It.IsAny<string>(), default))
            .ReturnsAsync(1);
    }

    [Fact]
    public async Task ProvisionClientDatabase_NoGrantConfigured_FailsOpen_Proceeds()
    {
        var (svc, clientDatabaseBLL, ddlScriptBLL, upgradePackageBLL, dbServerBLL, targetDbExecutor, userResourceRoleBLL) = BuildServiceForProvisioning();
        SetupMinimalProvisioning(clientDatabaseBLL, dbServerBLL, upgradePackageBLL, ddlScriptBLL, targetDbExecutor);
        userResourceRoleBLL.Setup(u => u.GetEffectiveRole(It.IsAny<int>(), It.IsAny<int?>(), It.IsAny<int?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDbLoginRole?)null);

        await svc.ProvisionClientDatabase(5, 10, Login, default);

        targetDbExecutor.Verify(t => t.ExecuteScriptAsync("fake-conn-str", It.IsAny<string>(), default), Times.Once);
    }

    [Fact]
    public async Task ProvisionClientDatabase_DbaGrant_Proceeds()
    {
        var (svc, clientDatabaseBLL, ddlScriptBLL, upgradePackageBLL, dbServerBLL, targetDbExecutor, userResourceRoleBLL) = BuildServiceForProvisioning();
        SetupMinimalProvisioning(clientDatabaseBLL, dbServerBLL, upgradePackageBLL, ddlScriptBLL, targetDbExecutor);
        userResourceRoleBLL.Setup(u => u.GetEffectiveRole(It.IsAny<int>(), It.IsAny<int?>(), It.IsAny<int?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDbLoginRole?)ClientDbLoginRole.Dba);

        await svc.ProvisionClientDatabase(5, 10, Login, default);

        targetDbExecutor.Verify(t => t.ExecuteScriptAsync("fake-conn-str", It.IsAny<string>(), default), Times.Once);
    }

    [Fact]
    public async Task ProvisionClientDatabase_InsufficientExplicitGrant_ThrowsWithoutExecuting()
    {
        var (svc, clientDatabaseBLL, ddlScriptBLL, upgradePackageBLL, dbServerBLL, targetDbExecutor, userResourceRoleBLL) = BuildServiceForProvisioning();
        clientDatabaseBLL.Setup(c => c.GetById(5, Login, default))
            .ReturnsAsync(new ClientDatabaseDTO { ClientDbId = 5, DbServerId = 1, DatabaseName = "TestDb" });
        dbServerBLL.Setup(d => d.GetByIdWithCredentials(1, Login, default))
            .ReturnsAsync(new SwDAL.DTO.DbServer.DbServerDTO { DbServerId = 1 });
        userResourceRoleBLL.Setup(u => u.GetEffectiveRole(It.IsAny<int>(), It.IsAny<int?>(), It.IsAny<int?>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()))
            .ReturnsAsync((ClientDbLoginRole?)ClientDbLoginRole.ReadOnly);

        await Assert.ThrowsAsync<UnauthorizedAccessException>(() => svc.ProvisionClientDatabase(5, 10, Login, default));

        targetDbExecutor.Verify(t => t.ExecuteScriptAsync(It.IsAny<string>(), It.IsAny<string>(), It.IsAny<CancellationToken>()), Times.Never);
    }
}