using GB5Shared.DTO.Framework.Login;
using Moq;
using SwBLL.DbServer;
using SwBLL.DdlScript;
using SwBLL.Provisioning;
using SwBLL.RefDataSource;
using SwBLL.UpgradePackage;
using SwDAL.CustomCode.RefDataSource;
using SwDAL.DTO.DbServer;
using SwDAL.DTO.DdlScript;
using SwDAL.DTO.RefDataSource;
using SwDAL.DTO.UpgradePackage;
using Xunit;

namespace SwTests;

/// <summary>
/// Covers RefDataSourceBLL (tracker §36) — the registry that turns "which tables are in scope for
/// reference/standard-data delivery" into a config row instead of a new C# caller per table, the
/// orchestration that resolves a registry row + a source DbServer/database into a real connection
/// string (never a caller-supplied raw connection string) and hands it to
/// ReferenceDataScriptGenerator, and the checksum-diff regeneration check (§36.4) that skips
/// creating a new Draft script when nothing changed since the DbModel's current Released chain.
/// AutoNumber is passed null! for tests that never reach the auto-numbered insert path — same
/// "safe because unused" convention already established in UpgradePackageBLLTests/ProvisioningBLLTests.
/// </summary>
public class RefDataSourceBLLTests
{
    private static (RefDataSourceBLL Svc, Mock<IRefDataSourceDAL> Dal, Mock<IDdlScriptBLL> DdlScriptBLL,
        Mock<IUpgradePackageBLL> UpgradePackageBLL, Mock<IDbServerBLL> DbServerBLL,
        Mock<ITargetDbExecutor> TargetDbExecutor, Mock<IReferenceDataScriptGenerator> Generator) BuildService()
    {
        var dal              = new Mock<IRefDataSourceDAL>();
        var ddlScript         = new Mock<IDdlScriptBLL>();
        var upgradePackage    = new Mock<IUpgradePackageBLL>();
        var dbServer          = new Mock<IDbServerBLL>();
        var targetDbExecutor  = new Mock<ITargetDbExecutor>();
        var generator         = new Mock<IReferenceDataScriptGenerator>();

        var svc = new RefDataSourceBLL(
            dal.Object, autoNumber: null!, ddlScript.Object, upgradePackage.Object,
            dbServer.Object, targetDbExecutor.Object, generator.Object);

        return (svc, dal, ddlScript, upgradePackage, dbServer, targetDbExecutor, generator);
    }

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

    private static void SetupSourceResolution(
        Mock<IRefDataSourceDAL> dal, Mock<IDbServerBLL> dbServer, Mock<ITargetDbExecutor> targetDbExecutor,
        Mock<IReferenceDataScriptGenerator> generator, RefDataSourceDTO source, string generatedSql)
    {
        dal.Setup(d => d.GetById(source.RefDataSourceId, Login, default)).ReturnsAsync(source);
        dbServer.Setup(d => d.GetByIdWithCredentials(1, Login, default))
            .ReturnsAsync(new DbServerDTO { DbServerId = 1, DbType = 0 });
        targetDbExecutor.Setup(t => t.BuildConnectionString(It.IsAny<DbServerDTO>(), "SourceDb", null, null))
            .Returns("conn-str");
        generator.Setup(g => g.GenerateSeedScriptAsync("conn-str", 0, source.TableName, source.RowFilterSql, default))
            .ReturnsAsync(generatedSql);
    }

    // ── Save — guard clauses ─────────────────────────────────────────────────────────────────

    [Fact]
    public async Task Save_ZeroDbModelId_ThrowsWithoutTouchingDal()
    {
        var (svc, dal, _, _, _, _, _) = BuildService();

        await Assert.ThrowsAsync<ArgumentException>(() =>
            svc.Save(new RefDataSourceDTO { DbModelId = 0, TableName = "MCURRENCY" }, Login, default));

        dal.Verify(d => d.Insert(It.IsAny<RefDataSourceDTO>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    [Fact]
    public async Task Save_EmptyTableName_ThrowsWithoutTouchingDal()
    {
        var (svc, dal, _, _, _, _, _) = BuildService();

        await Assert.ThrowsAsync<ArgumentException>(() =>
            svc.Save(new RefDataSourceDTO { DbModelId = 1, TableName = "" }, Login, default));

        dal.Verify(d => d.Insert(It.IsAny<RefDataSourceDTO>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    [Fact]
    public async Task Save_UniversalScopeWithScopeValue_ThrowsWithoutTouchingDal()
    {
        var (svc, dal, _, _, _, _, _) = BuildService();

        await Assert.ThrowsAsync<ArgumentException>(() =>
            svc.Save(new RefDataSourceDTO { DbModelId = 1, TableName = "MCURRENCY", ScopeType = 0, ScopeValue = "ACME" }, Login, default));

        dal.Verify(d => d.Insert(It.IsAny<RefDataSourceDTO>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    [Fact]
    public async Task Save_NonUniversalScopeWithoutScopeValue_ThrowsWithoutTouchingDal()
    {
        var (svc, dal, _, _, _, _, _) = BuildService();

        await Assert.ThrowsAsync<ArgumentException>(() =>
            svc.Save(new RefDataSourceDTO { DbModelId = 1, TableName = "Z_ACME_CUSTOM", ScopeType = 4, ScopeValue = null }, Login, default));

        dal.Verify(d => d.Insert(It.IsAny<RefDataSourceDTO>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    // ── Save — "Z_" custom-object convention ─────────────────────────────────────────────────

    [Theory]
    [InlineData("Z_ACME_CUSTOMTABLE")]
    [InlineData("z_acme_customtable")] // case-insensitive
    public async Task Save_ZPrefixedTableWithUniversalScope_ThrowsWithoutTouchingDal(string tableName)
    {
        var (svc, dal, _, _, _, _, _) = BuildService();

        await Assert.ThrowsAsync<ArgumentException>(() =>
            svc.Save(new RefDataSourceDTO { DbModelId = 1, TableName = tableName, ScopeType = 0 }, Login, default));

        dal.Verify(d => d.Insert(It.IsAny<RefDataSourceDTO>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    [Fact]
    public async Task Save_ZPrefixedTableWithClientScope_Succeeds()
    {
        // Uses the Update path (non-zero RefDataSourceId), not Insert — Insert needs a real
        // AutoNumber (a concrete, non-virtual class Moq can't intercept), matching this file's
        // own established "AutoNumber passed null! for tests that don't reach the insert path"
        // convention. Update proves the same validation-passes-through behavior without it.
        var (svc, dal, _, _, _, _, _) = BuildService();
        dal.Setup(d => d.Update(It.IsAny<RefDataSourceDTO>(), Login, default)).ReturnsAsync(1);

        var result = await svc.Save(
            new RefDataSourceDTO { RefDataSourceId = 9, DbModelId = 1, TableName = "Z_ACME_CUSTOMTABLE", ScopeType = 4, ScopeValue = "ACME" },
            Login, default);

        dal.Verify(d => d.Update(It.IsAny<RefDataSourceDTO>(), Login, default), Times.Once);
        Assert.Contains("Updated", result);
    }

    [Fact]
    public async Task Save_NonZPrefixedTableWithClientScope_Succeeds()
    {
        // A genuinely universal table can still legitimately have a client-specific override
        // package — the "Z_" rule only forbids the OPPOSITE (custom table registered Universal).
        var (svc, dal, _, _, _, _, _) = BuildService();
        dal.Setup(d => d.Update(It.IsAny<RefDataSourceDTO>(), Login, default)).ReturnsAsync(1);

        var result = await svc.Save(
            new RefDataSourceDTO { RefDataSourceId = 9, DbModelId = 1, TableName = "MCURRENCY", ScopeType = 4, ScopeValue = "ACME" },
            Login, default);

        dal.Verify(d => d.Update(It.IsAny<RefDataSourceDTO>(), Login, default), Times.Once);
        Assert.Contains("Updated", result);
    }

    [Fact]
    public async Task Save_ExistingRow_CallsUpdateNotInsert()
    {
        var (svc, dal, _, _, _, _, _) = BuildService();
        dal.Setup(d => d.Update(It.IsAny<RefDataSourceDTO>(), Login, default)).ReturnsAsync(1);

        var result = await svc.Save(new RefDataSourceDTO { RefDataSourceId = 5, DbModelId = 1, TableName = "MCURRENCY" }, Login, default);

        dal.Verify(d => d.Update(It.IsAny<RefDataSourceDTO>(), Login, default), Times.Once);
        dal.Verify(d => d.Insert(It.IsAny<RefDataSourceDTO>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
        Assert.Contains("Updated", result);
    }

    // ── GenerateAndRegisterAsync ──────────────────────────────────────────────────────────────

    [Fact]
    public async Task GenerateAndRegisterAsync_RegistryRowNotFound_ThrowsWithoutGenerating()
    {
        var (svc, dal, _, _, _, _, generator) = BuildService();
        dal.Setup(d => d.GetById(99, Login, default)).ReturnsAsync((RefDataSourceDTO?)null);

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            svc.GenerateAndRegisterAsync(99, sourceDbServerId: 1, "SourceDb", branchId: 1, Login, default));

        generator.Verify(g => g.GenerateSeedScriptAsync(
            It.IsAny<string>(), It.IsAny<byte>(), It.IsAny<string>(), It.IsAny<string?>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    [Fact]
    public async Task GenerateAndRegisterAsync_DbServerNotFound_ThrowsWithoutGenerating()
    {
        var (svc, dal, _, _, dbServer, _, generator) = BuildService();
        dal.Setup(d => d.GetById(5, Login, default)).ReturnsAsync(new RefDataSourceDTO { RefDataSourceId = 5, DbModelId = 7, TableName = "MCURRENCY" });
        dbServer.Setup(d => d.GetByIdWithCredentials(1, Login, default)).ReturnsAsync((DbServerDTO?)null);

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            svc.GenerateAndRegisterAsync(5, sourceDbServerId: 1, "SourceDb", branchId: 1, Login, default));

        generator.Verify(g => g.GenerateSeedScriptAsync(
            It.IsAny<string>(), It.IsAny<byte>(), It.IsAny<string>(), It.IsAny<string?>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    [Fact]
    public async Task GenerateAndRegisterAsync_ResolvesConnectionServerSide_NeverAcceptsRawConnectionString()
    {
        var (svc, dal, ddlScriptBLL, _, dbServer, targetDbExecutor, generator) = BuildService();
        var source = new RefDataSourceDTO { RefDataSourceId = 5, DbModelId = 7, TableName = "MCURRENCY", RowFilterSql = "SOURCETYPE IN (1,2)" };
        SetupSourceResolution(dal, dbServer, targetDbExecutor, generator, source, "INSERT INTO MCURRENCY ...");

        DdlScriptDTO? capturedScript = null;
        ddlScriptBLL.Setup(d => d.Save(It.IsAny<DdlScriptDTO>(), Login, default))
            .Callback<DdlScriptDTO, LoginDTO, CancellationToken>((dto, _, _) => { capturedScript = dto; dto.DdlScriptId = 12345; })
            .ReturnsAsync("saved");

        var resultId = await svc.GenerateAndRegisterAsync(5, sourceDbServerId: 1, "SourceDb", branchId: 3, Login, default);

        Assert.Equal(12345, resultId);
        Assert.Equal(7, capturedScript!.DbModelId);
        Assert.Equal(3, capturedScript.BranchId);
        Assert.Equal("MCURRENCY", capturedScript.ObjectName);
        Assert.Equal(-1, capturedScript.ObjectGroupId);
        // The connection string never crosses this BLL's own public API — only DbServerId/DatabaseName do.
        dbServer.Verify(d => d.GetByIdWithCredentials(1, Login, default), Times.Once);
        targetDbExecutor.Verify(t => t.BuildConnectionString(It.IsAny<DbServerDTO>(), "SourceDb", null, null), Times.Once);
    }

    // ── RegenerateIfChangedAsync ──────────────────────────────────────────────────────────────

    [Fact]
    public async Task RegenerateIfChangedAsync_NoReleasedPackageTouchesThisTable_TreatsAsChanged()
    {
        var (svc, dal, ddlScriptBLL, upgradePackageBLL, dbServer, targetDbExecutor, generator) = BuildService();
        var source = new RefDataSourceDTO { RefDataSourceId = 5, DbModelId = 7, TableName = "MCURRENCY" };
        SetupSourceResolution(dal, dbServer, targetDbExecutor, generator, source, "INSERT INTO MCURRENCY ...");
        upgradePackageBLL.Setup(u => u.GetProvisioningChain(7, null, Login, default))
            .ReturnsAsync(Array.Empty<PackageChainItemDTO>());
        ddlScriptBLL.Setup(d => d.Save(It.IsAny<DdlScriptDTO>(), Login, default))
            .Callback<DdlScriptDTO, LoginDTO, CancellationToken>((dto, _, _) => dto.DdlScriptId = 999)
            .ReturnsAsync("saved");

        var result = await svc.RegenerateIfChangedAsync(5, sourceDbServerId: 1, "SourceDb", branchId: 1, Login, default);

        Assert.True(result.Changed);
        Assert.Equal(999, result.NewDdlScriptId);
        Assert.Null(result.PriorChecksum);
        ddlScriptBLL.Verify(d => d.Save(It.IsAny<DdlScriptDTO>(), Login, default), Times.Once);
    }

    [Fact]
    public async Task RegenerateIfChangedAsync_IdenticalChecksumInReleasedChain_SkipsWithoutSavingNewScript()
    {
        var (svc, dal, ddlScriptBLL, upgradePackageBLL, dbServer, targetDbExecutor, generator) = BuildService();
        var source = new RefDataSourceDTO { RefDataSourceId = 5, DbModelId = 7, TableName = "MCURRENCY" };
        const string sql = "INSERT INTO MCURRENCY (X) VALUES (1);\n";
        SetupSourceResolution(dal, dbServer, targetDbExecutor, generator, source, sql);
        string sameChecksum = DdlScriptBLL.ComputeChecksum(sql);

        upgradePackageBLL.Setup(u => u.GetProvisioningChain(7, null, Login, default))
            .ReturnsAsync(new[] { new PackageChainItemDTO { PackageId = 100 } });
        upgradePackageBLL.Setup(u => u.GetDdlLines(100, Login, default))
            .ReturnsAsync(new[] { new UpgradePackageDdlDTO { PackageId = 100, DdlScriptId = 55, ObjectName = "MCURRENCY" } });
        ddlScriptBLL.Setup(d => d.GetById(55, Login, default))
            .ReturnsAsync(new DdlScriptDTO { DdlScriptId = 55, ObjectName = "MCURRENCY", Checksum = sameChecksum });

        var result = await svc.RegenerateIfChangedAsync(5, sourceDbServerId: 1, "SourceDb", branchId: 1, Login, default);

        Assert.False(result.Changed);
        Assert.Null(result.NewDdlScriptId);
        Assert.Equal(sameChecksum, result.PriorChecksum);
        Assert.Equal(sameChecksum, result.NewChecksum);
        ddlScriptBLL.Verify(d => d.Save(It.IsAny<DdlScriptDTO>(), It.IsAny<LoginDTO>(), It.IsAny<CancellationToken>()), Times.Never);
    }

    [Fact]
    public async Task RegenerateIfChangedAsync_DifferentChecksumInReleasedChain_SavesNewDraftScript()
    {
        var (svc, dal, ddlScriptBLL, upgradePackageBLL, dbServer, targetDbExecutor, generator) = BuildService();
        var source = new RefDataSourceDTO { RefDataSourceId = 5, DbModelId = 7, TableName = "MCURRENCY" };
        SetupSourceResolution(dal, dbServer, targetDbExecutor, generator, source, "INSERT INTO MCURRENCY (X) VALUES (2);\n");

        upgradePackageBLL.Setup(u => u.GetProvisioningChain(7, null, Login, default))
            .ReturnsAsync(new[] { new PackageChainItemDTO { PackageId = 100 } });
        upgradePackageBLL.Setup(u => u.GetDdlLines(100, Login, default))
            .ReturnsAsync(new[] { new UpgradePackageDdlDTO { PackageId = 100, DdlScriptId = 55, ObjectName = "MCURRENCY" } });
        ddlScriptBLL.Setup(d => d.GetById(55, Login, default))
            .ReturnsAsync(new DdlScriptDTO { DdlScriptId = 55, ObjectName = "MCURRENCY", Checksum = "stale-checksum-from-an-older-release" });
        ddlScriptBLL.Setup(d => d.Save(It.IsAny<DdlScriptDTO>(), Login, default))
            .Callback<DdlScriptDTO, LoginDTO, CancellationToken>((dto, _, _) => dto.DdlScriptId = 777)
            .ReturnsAsync("saved");

        var result = await svc.RegenerateIfChangedAsync(5, sourceDbServerId: 1, "SourceDb", branchId: 1, Login, default);

        Assert.True(result.Changed);
        Assert.Equal(777, result.NewDdlScriptId);
        Assert.Equal("stale-checksum-from-an-older-release", result.PriorChecksum);
        ddlScriptBLL.Verify(d => d.Save(It.IsAny<DdlScriptDTO>(), Login, default), Times.Once);
    }

    [Fact]
    public async Task RegenerateIfChangedAsync_ReleasedPackageHasNoScriptForThisTable_SkipsToNextPackage()
    {
        var (svc, dal, ddlScriptBLL, upgradePackageBLL, dbServer, targetDbExecutor, generator) = BuildService();
        var source = new RefDataSourceDTO { RefDataSourceId = 5, DbModelId = 7, TableName = "MCURRENCY" };
        const string sql = "INSERT INTO MCURRENCY (X) VALUES (1);\n";
        SetupSourceResolution(dal, dbServer, targetDbExecutor, generator, source, sql);
        string sameChecksum = DdlScriptBLL.ComputeChecksum(sql);

        // Two packages in the chain (e.g. a Universal one plus a scoped overlay) — only the
        // second one happens to carry a script for MCURRENCY.
        upgradePackageBLL.Setup(u => u.GetProvisioningChain(7, null, Login, default))
            .ReturnsAsync(new[] { new PackageChainItemDTO { PackageId = 100 }, new PackageChainItemDTO { PackageId = 200 } });
        upgradePackageBLL.Setup(u => u.GetDdlLines(100, Login, default))
            .ReturnsAsync(new[] { new UpgradePackageDdlDTO { PackageId = 100, DdlScriptId = 1, ObjectName = "MUOM" } });
        upgradePackageBLL.Setup(u => u.GetDdlLines(200, Login, default))
            .ReturnsAsync(new[] { new UpgradePackageDdlDTO { PackageId = 200, DdlScriptId = 55, ObjectName = "MCURRENCY" } });
        ddlScriptBLL.Setup(d => d.GetById(55, Login, default))
            .ReturnsAsync(new DdlScriptDTO { DdlScriptId = 55, ObjectName = "MCURRENCY", Checksum = sameChecksum });

        var result = await svc.RegenerateIfChangedAsync(5, sourceDbServerId: 1, "SourceDb", branchId: 1, Login, default);

        Assert.False(result.Changed);
        ddlScriptBLL.Verify(d => d.GetById(1, Login, default), Times.Never); // MUOM script never fetched — ObjectName didn't match
    }

    [Fact]
    public async Task RegenerateIfChangedAsync_ClientScopedRegistryRow_WalksClientChainNotUniversal()
    {
        // A "Z_" custom-table registry row (Client-scoped) must compare against that client's
        // own overlay chain — walking the Universal chain instead would never find a match
        // (no Universal package ever carries a client-custom script) and would regenerate a new
        // Draft on every single call, defeating the whole point of the checksum-diff check.
        var (svc, dal, ddlScriptBLL, upgradePackageBLL, dbServer, targetDbExecutor, generator) = BuildService();
        var source = new RefDataSourceDTO
        {
            RefDataSourceId = 5, DbModelId = 7, TableName = "Z_ACME_CUSTOMTABLE", ScopeType = 4, ScopeValue = "ACME",
        };
        const string sql = "INSERT INTO Z_ACME_CUSTOMTABLE (X) VALUES (1);\n";
        SetupSourceResolution(dal, dbServer, targetDbExecutor, generator, source, sql);
        string sameChecksum = DdlScriptBLL.ComputeChecksum(sql);

        upgradePackageBLL.Setup(u => u.GetProvisioningChain(7,
                It.Is<PackageScopeFilterDTO>(s => s != null && s.ClientCode == "ACME"), Login, default))
            .ReturnsAsync(new[] { new PackageChainItemDTO { PackageId = 300 } });
        upgradePackageBLL.Setup(u => u.GetDdlLines(300, Login, default))
            .ReturnsAsync(new[] { new UpgradePackageDdlDTO { PackageId = 300, DdlScriptId = 60, ObjectName = "Z_ACME_CUSTOMTABLE" } });
        ddlScriptBLL.Setup(d => d.GetById(60, Login, default))
            .ReturnsAsync(new DdlScriptDTO { DdlScriptId = 60, ObjectName = "Z_ACME_CUSTOMTABLE", Checksum = sameChecksum });

        var result = await svc.RegenerateIfChangedAsync(5, sourceDbServerId: 1, "SourceDb", branchId: 1, Login, default);

        Assert.False(result.Changed);
        upgradePackageBLL.Verify(u => u.GetProvisioningChain(7, null, Login, default), Times.Never);
    }
}