using GB5Shared.DTO.Framework.SchemaIntrospection;
using Moq;
using SwBLL.Provisioning;
using Xunit;

namespace SwTests;

/// <summary>
/// Covers ReferenceDataScriptGenerator (tracker §36.2) — the generic, registry-driven replacement
/// for hand-authoring a reference/standard-data DdlScript per table. All tests mock
/// ITargetDbExecutor directly, so none touch a real database; GetServerColumnsAsync/
/// ExecuteQueryAsync stand in for live schema introspection and row retrieval.
/// </summary>
public class ReferenceDataScriptGeneratorTests
{
    private static (ReferenceDataScriptGenerator Svc, Mock<ITargetDbExecutor> TargetDbExecutor) BuildService()
    {
        var targetDbExecutor = new Mock<ITargetDbExecutor>();
        return (new ReferenceDataScriptGenerator(targetDbExecutor.Object), targetDbExecutor);
    }

    private static SchemaTableColumnDTO Col(string name, string dataType, int ordinal, bool isPk = false, string tableName = "MPAYMENTTERM") => new()
    {
        SchemaName = "dbo", TableName = tableName, ColumnName = name, DataType = dataType,
        OrdinalPosition = ordinal, IsPrimaryKey = isPk, IsNullable = "NO",
    };

    // ── Tenant-scoped table (has TENANTID) ───────────────────────────────────────────────────

    [Fact]
    public async Task GenerateSeedScriptAsync_TableHasTenantIdColumn_SubstitutesTokenNotSourceValue()
    {
        var (svc, targetDbExecutor) = BuildService();

        var columns = new List<SchemaTableColumnDTO>
        {
            Col("CURRENCYID", "int", 1, isPk: true, tableName: "MCURRENCY"),
            Col("CURRENCYCODE", "nvarchar", 2, tableName: "MCURRENCY"),
            Col("TENANTID", "int", 3, tableName: "MCURRENCY"),
        };
        targetDbExecutor.Setup(t => t.GetServerColumnsAsync("src-conn", 0, default)).ReturnsAsync(columns);
        targetDbExecutor.Setup(t => t.ExecuteQueryAsync("src-conn", "SELECT * FROM MCURRENCY", It.IsAny<Dictionary<string, object?>>(), default))
            .ReturnsAsync(new List<Dictionary<string, object?>>
            {
                new() { ["CURRENCYID"] = -1800000000, ["CURRENCYCODE"] = "INR", ["TENANTID"] = -1800000000 },
            });

        var sql = await svc.GenerateSeedScriptAsync("src-conn", 0, "MCURRENCY", null, default);

        Assert.Contains("{{TENANTID}}", sql);
        Assert.DoesNotContain("-1800000000, N'INR', -1800000000", sql); // the source's own literal TenantId must never leak through
        Assert.Contains("(-1800000000, N'INR', {{TENANTID}})", sql);
    }

    // ── Global table (no TENANTID) ───────────────────────────────────────────────────────────

    [Fact]
    public async Task GenerateSeedScriptAsync_TableHasNoTenantIdColumn_NoSubstitution()
    {
        var (svc, targetDbExecutor) = BuildService();

        var columns = new List<SchemaTableColumnDTO>
        {
            Col("PAYMENTTERMID", "int", 1, isPk: true),
            Col("PAYMENTTERMCODE", "nvarchar", 2),
        };
        targetDbExecutor.Setup(t => t.GetServerColumnsAsync("src-conn", 0, default)).ReturnsAsync(columns);
        targetDbExecutor.Setup(t => t.ExecuteQueryAsync("src-conn", "SELECT * FROM MPAYMENTTERM", It.IsAny<Dictionary<string, object?>>(), default))
            .ReturnsAsync(new List<Dictionary<string, object?>>
            {
                new() { ["PAYMENTTERMID"] = -1900000000, ["PAYMENTTERMCODE"] = "IMMEDIATE" },
            });

        var sql = await svc.GenerateSeedScriptAsync("src-conn", 0, "MPAYMENTTERM", null, default);

        Assert.DoesNotContain("{{TENANTID}}", sql);
        Assert.Contains("(-1900000000, N'IMMEDIATE')", sql);
    }

    // ── Override columns (audit/provenance) always overridden, never copied from source ─────

    [Fact]
    public async Task GenerateSeedScriptAsync_OverrideColumns_AlwaysUseFixedValuesNotSourceRow()
    {
        var (svc, targetDbExecutor) = BuildService();

        var columns = new List<SchemaTableColumnDTO>
        {
            Col("PAYMENTTERMID", "int", 1, isPk: true),
            Col("VERSION", "smallint", 2),
            Col("CREATEDBYID", "int", 3),
            Col("CREATEDON", "datetime", 4),
            Col("MODIFIEDBYID", "int", 5),
            Col("MODIFIEDON", "datetime", 6),
            Col("SOURCETYPE", "tinyint", 7),
        };
        targetDbExecutor.Setup(t => t.GetServerColumnsAsync("src-conn", 0, default)).ReturnsAsync(columns);
        // Source row deliberately carries "wrong" historical values (a real user id, a real
        // historical timestamp, a real edit-count, SOURCETYPE=3) that must never appear in the
        // generated script — confirmed live against GB5DEMO's real MCURRENCY rows, which carry
        // genuinely varying VERSION values (2/3/4) reflecting that source database's own edit
        // history, meaningless for a freshly-delivered row in a different database.
        targetDbExecutor.Setup(t => t.ExecuteQueryAsync("src-conn", "SELECT * FROM MPAYMENTTERM", It.IsAny<Dictionary<string, object?>>(), default))
            .ReturnsAsync(new List<Dictionary<string, object?>>
            {
                new() { ["PAYMENTTERMID"] = 1, ["VERSION"] = (short)7, ["CREATEDBYID"] = 12345, ["CREATEDON"] = new DateTime(2019, 1, 1),
                        ["MODIFIEDBYID"] = 67890, ["MODIFIEDON"] = new DateTime(2019, 1, 1), ["SOURCETYPE"] = (byte)3 },
            });

        var sql = await svc.GenerateSeedScriptAsync("src-conn", 0, "MPAYMENTTERM", null, default);

        Assert.Contains("(1, 0, -1, GETDATE(), -1, GETDATE(), 1)", sql);
        Assert.DoesNotContain("12345", sql);
        Assert.DoesNotContain("67890", sql);
        Assert.DoesNotContain("2019-01-01", sql);
        Assert.DoesNotContain(", 7,", sql);
    }

    // ── Idempotency guard shape ───────────────────────────────────────────────────────────────

    [Fact]
    public async Task GenerateSeedScriptAsync_EmitsWhereNotExistsGuardOnPrimaryKey()
    {
        var (svc, targetDbExecutor) = BuildService();
        var columns = new List<SchemaTableColumnDTO> { Col("PAYMENTTERMID", "int", 1, isPk: true) };
        targetDbExecutor.Setup(t => t.GetServerColumnsAsync("src-conn", 0, default)).ReturnsAsync(columns);
        targetDbExecutor.Setup(t => t.ExecuteQueryAsync("src-conn", "SELECT * FROM MPAYMENTTERM", It.IsAny<Dictionary<string, object?>>(), default))
            .ReturnsAsync(new List<Dictionary<string, object?>> { new() { ["PAYMENTTERMID"] = 1 } });

        var sql = await svc.GenerateSeedScriptAsync("src-conn", 0, "MPAYMENTTERM", null, default);

        Assert.Contains("WHERE NOT EXISTS (SELECT 1 FROM MPAYMENTTERM x WHERE x.PAYMENTTERMID = v.PAYMENTTERMID);", sql);
    }

    // ── Row filter — passed through, validated ───────────────────────────────────────────────

    [Fact]
    public async Task GenerateSeedScriptAsync_RowFilterSupplied_AppliedToSelect()
    {
        var (svc, targetDbExecutor) = BuildService();
        var columns = new List<SchemaTableColumnDTO> { Col("PAYMENTTERMID", "int", 1, isPk: true) };
        targetDbExecutor.Setup(t => t.GetServerColumnsAsync("src-conn", 0, default)).ReturnsAsync(columns);
        targetDbExecutor.Setup(t => t.ExecuteQueryAsync("src-conn", "SELECT * FROM MPAYMENTTERM WHERE SOURCETYPE IN (1,2,3)", It.IsAny<Dictionary<string, object?>>(), default))
            .ReturnsAsync(new List<Dictionary<string, object?>> { new() { ["PAYMENTTERMID"] = 1 } });

        var sql = await svc.GenerateSeedScriptAsync("src-conn", 0, "MPAYMENTTERM", "SOURCETYPE IN (1,2,3)", default);

        Assert.Contains("(1)", sql);
        targetDbExecutor.Verify(t => t.ExecuteQueryAsync("src-conn", "SELECT * FROM MPAYMENTTERM WHERE SOURCETYPE IN (1,2,3)", It.IsAny<Dictionary<string, object?>>(), default), Times.Once);
    }

    [Fact]
    public async Task GenerateSeedScriptAsync_RowFilterContainsDisallowedKeyword_ThrowsWithoutQuerying()
    {
        var (svc, targetDbExecutor) = BuildService();

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            svc.GenerateSeedScriptAsync("src-conn", 0, "MPAYMENTTERM", "1=1; DROP TABLE MPAYMENTTERM", default));

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

    // ── Guard clauses ─────────────────────────────────────────────────────────────────────────

    [Fact]
    public async Task GenerateSeedScriptAsync_TableNotFound_Throws()
    {
        var (svc, targetDbExecutor) = BuildService();
        targetDbExecutor.Setup(t => t.GetServerColumnsAsync("src-conn", 0, default)).ReturnsAsync(new List<SchemaTableColumnDTO>());

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            svc.GenerateSeedScriptAsync("src-conn", 0, "MNOTATABLE", null, default));
    }

    [Fact]
    public async Task GenerateSeedScriptAsync_NoRowsMatchFilter_Throws()
    {
        var (svc, targetDbExecutor) = BuildService();
        var columns = new List<SchemaTableColumnDTO> { Col("PAYMENTTERMID", "int", 1, isPk: true) };
        targetDbExecutor.Setup(t => t.GetServerColumnsAsync("src-conn", 0, default)).ReturnsAsync(columns);
        targetDbExecutor.Setup(t => t.ExecuteQueryAsync("src-conn", "SELECT * FROM MPAYMENTTERM", It.IsAny<Dictionary<string, object?>>(), default))
            .ReturnsAsync(new List<Dictionary<string, object?>>());

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            svc.GenerateSeedScriptAsync("src-conn", 0, "MPAYMENTTERM", null, default));
    }

    [Fact]
    public async Task GenerateSeedScriptAsync_NoSingleColumnPrimaryKey_Throws()
    {
        var (svc, targetDbExecutor) = BuildService();
        var columns = new List<SchemaTableColumnDTO> { Col("PAYMENTTERMCODE", "nvarchar", 1, isPk: false) };
        targetDbExecutor.Setup(t => t.GetServerColumnsAsync("src-conn", 0, default)).ReturnsAsync(columns);

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            svc.GenerateSeedScriptAsync("src-conn", 0, "MPAYMENTTERM", null, default));
    }
}