FirebirdSchemaIntegrationTests.cs

using FirebirdSql.Data.FirebirdClient;
using JD.Efcpt.Build.Tasks.Schema;
using JD.Efcpt.Build.Tasks.Schema.Providers;
using JD.Efcpt.Build.Tests.Infrastructure;
using Testcontainers.FirebirdSql;
using TinyBDD;
using TinyBDD.Xunit;
using Xunit;
using Xunit.Abstractions;
using Task = System.Threading.Tasks.Task;

namespace JD.Efcpt.Build.Tests.Integration;

/// <summary>
/// Integration tests for FirebirdSchemaReader using Testcontainers.
/// These tests verify that the reader correctly reads schema from a real Firebird database.
/// </summary>
[Feature("FirebirdSchemaReader: reads and fingerprints Firebird schema using Testcontainers")]
[Collection(nameof(AssemblySetup))]
public sealed partial class FirebirdSchemaIntegrationTests(ITestOutputHelper output) : TinyBddXunitBase(output)
{
    private sealed record TestContext(
        FirebirdSqlContainer Container,
        string ConnectionString) : IDisposable
    {
        public void Dispose()
        {
            Container.DisposeAsync().AsTask().GetAwaiter().GetResult();
        }
    }

    private sealed record SchemaResult(TestContext Context, SchemaModel Schema);
    private sealed record FingerprintResult(TestContext Context, string Fingerprint1, string Fingerprint2);

    // ========== Setup Methods ==========

    private static async Task<TestContext> SetupEmptyDatabase()
    {
        var container = new FirebirdSqlBuilder("jacobalberty/firebird:v4.0")
            .Build();

        await ContainerStartup.StartOrSkipAsync(container);
        return new TestContext(container, container.GetConnectionString());
    }

    private static async Task<TestContext> SetupDatabaseWithSchema()
    {
        var ctx = await SetupEmptyDatabase();
        await CreateTestSchema(ctx);
        return ctx;
    }

    private static async Task CreateTestSchema(TestContext ctx)
    {
        await using var connection = new FbConnection(ctx.ConnectionString);
        await connection.OpenAsync();

        // Firebird requires individual statements
        var statements = new[]
        {
            """
            CREATE TABLE customers (
                id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            """,
            """
            CREATE TABLE products (
                id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                name VARCHAR(200) NOT NULL,
                price DECIMAL(10, 2) NOT NULL,
                stock INTEGER DEFAULT 0
            )
            """,
            """
            CREATE TABLE orders (
                id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                customer_id INTEGER NOT NULL,
                product_id INTEGER NOT NULL,
                quantity INTEGER NOT NULL,
                CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
                CONSTRAINT fk_orders_product FOREIGN KEY (product_id) REFERENCES products(id)
            )
            """,
            "CREATE INDEX idx_products_name ON products(name)",
            "CREATE INDEX idx_orders_customer ON orders(customer_id)"
        };

        foreach (var sql in statements)
        {
            await using var command = connection.CreateCommand();
            command.CommandText = sql;
            await command.ExecuteNonQueryAsync();
        }
    }

    private static async Task AddColumn(TestContext ctx)
    {
        await using var connection = new FbConnection(ctx.ConnectionString);
        await connection.OpenAsync();

        await using var command = connection.CreateCommand();
        command.CommandText = "ALTER TABLE customers ADD phone VARCHAR(20)";
        await command.ExecuteNonQueryAsync();
    }

    // ========== Execute Methods ==========

    private static SchemaResult ExecuteReadSchema(TestContext ctx)
    {
        var reader = new FirebirdSchemaReader();
        var schema = reader.ReadSchema(ctx.ConnectionString);
        return new SchemaResult(ctx, schema);
    }

    private static SchemaResult ExecuteReadSchemaViaFactory(TestContext ctx)
    {
        var reader = DatabaseProviderFactory.CreateSchemaReader("firebird");
        var schema = reader.ReadSchema(ctx.ConnectionString);
        return new SchemaResult(ctx, schema);
    }

    private static SchemaResult ExecuteReadSchemaViaFbAlias(TestContext ctx)
    {
        var reader = DatabaseProviderFactory.CreateSchemaReader("fb");
        var schema = reader.ReadSchema(ctx.ConnectionString);
        return new SchemaResult(ctx, schema);
    }

    private static FingerprintResult ExecuteComputeFingerprint(TestContext ctx)
    {
        var reader = new FirebirdSchemaReader();
        var schema1 = reader.ReadSchema(ctx.ConnectionString);
        var schema2 = reader.ReadSchema(ctx.ConnectionString);
        var fp1 = SchemaFingerprinter.ComputeFingerprint(schema1);
        var fp2 = SchemaFingerprinter.ComputeFingerprint(schema2);
        return new FingerprintResult(ctx, fp1, fp2);
    }

    private static async Task<FingerprintResult> ExecuteComputeFingerprintWithChange(TestContext ctx)
    {
        var reader = new FirebirdSchemaReader();
        var schema1 = reader.ReadSchema(ctx.ConnectionString);
        var fp1 = SchemaFingerprinter.ComputeFingerprint(schema1);

        await AddColumn(ctx);

        var schema2 = reader.ReadSchema(ctx.ConnectionString);
        var fp2 = SchemaFingerprinter.ComputeFingerprint(schema2);

        return new FingerprintResult(ctx, fp1, fp2);
    }

    // ========== Tests ==========

    [Scenario("Reads tables from Firebird database")]
    [SkippableFact]
    public async Task Reads_tables_from_database()
    {
        var ctx = await SetupDatabaseWithSchema();
        await Given("a Firebird container with test schema", () => Task.FromResult(ctx))
            .When("schema is read", ExecuteReadSchema)
            .Then("returns test tables", r => r.Schema.Tables.Count >= 3)
            .And("contains customers table", r => r.Schema.Tables.Any(t => t.Name.Equals("CUSTOMERS", StringComparison.OrdinalIgnoreCase)))
            .And("contains products table", r => r.Schema.Tables.Any(t => t.Name.Equals("PRODUCTS", StringComparison.OrdinalIgnoreCase)))
            .And("contains orders table", r => r.Schema.Tables.Any(t => t.Name.Equals("ORDERS", StringComparison.OrdinalIgnoreCase)))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Reads columns with correct metadata")]
    [SkippableFact]
    public async Task Reads_columns_with_metadata()
    {
        var ctx = await SetupDatabaseWithSchema();
        await Given("a Firebird container with test schema", () => Task.FromResult(ctx))
            .When("schema is read", ExecuteReadSchema)
            .Then("customers table has correct column count", r =>
                r.Schema.Tables.First(t => t.Name.Equals("CUSTOMERS", StringComparison.OrdinalIgnoreCase)).Columns.Count == 4)
            .And("products table has correct column count", r =>
                r.Schema.Tables.First(t => t.Name.Equals("PRODUCTS", StringComparison.OrdinalIgnoreCase)).Columns.Count == 4)
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Reads indexes from Firebird database")]
    [SkippableFact]
    public async Task Reads_indexes_from_database()
    {
        var ctx = await SetupDatabaseWithSchema();
        await Given("a Firebird container with test schema", () => Task.FromResult(ctx))
            .When("schema is read", ExecuteReadSchema)
            .Then("products table has indexes", r =>
                r.Schema.Tables.First(t => t.Name.Equals("PRODUCTS", StringComparison.OrdinalIgnoreCase)).Indexes.Count > 0)
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Computes deterministic fingerprint")]
    [SkippableFact]
    public async Task Computes_deterministic_fingerprint()
    {
        var ctx = await SetupDatabaseWithSchema();
        await Given("a Firebird container with test schema", () => Task.FromResult(ctx))
            .When("fingerprint computed twice", ExecuteComputeFingerprint)
            .Then("fingerprints are equal", r => string.Equals(r.Fingerprint1, r.Fingerprint2, StringComparison.Ordinal))
            .And("fingerprint is not empty", r => !string.IsNullOrEmpty(r.Fingerprint1))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Fingerprint changes when schema changes")]
    [SkippableFact]
    public async Task Fingerprint_changes_when_schema_changes()
    {
        var ctx = await SetupDatabaseWithSchema();
        await Given("a Firebird container with test schema", () => Task.FromResult(ctx))
            .When("schema is modified", ExecuteComputeFingerprintWithChange)
            .Then("fingerprints are different", r => !string.Equals(r.Fingerprint1, r.Fingerprint2, StringComparison.Ordinal))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Uses factory to create reader")]
    [SkippableFact]
    public async Task Factory_creates_correct_reader()
    {
        var ctx = await SetupDatabaseWithSchema();
        await Given("a Firebird container with test schema", () => Task.FromResult(ctx))
            .When("schema read via factory", ExecuteReadSchemaViaFactory)
            .Then("returns valid schema", r => r.Schema.Tables.Count >= 3)
            .And("contains customers table", r => r.Schema.Tables.Any(t => t.Name.Equals("CUSTOMERS", StringComparison.OrdinalIgnoreCase)))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("fb alias works")]
    [SkippableFact]
    public async Task Fb_alias_works()
    {
        var ctx = await SetupDatabaseWithSchema();
        await Given("a Firebird container with test schema", () => Task.FromResult(ctx))
            .When("schema read via fb alias", ExecuteReadSchemaViaFbAlias)
            .Then("returns valid schema", r => r.Schema.Tables.Count >= 3)
            .And("contains customers table", r => r.Schema.Tables.Any(t => t.Name.Equals("CUSTOMERS", StringComparison.OrdinalIgnoreCase)))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }

    [Scenario("Excludes system tables")]
    [SkippableFact]
    public async Task Excludes_system_tables()
    {
        var ctx = await SetupDatabaseWithSchema();
        await Given("a Firebird container with test schema", () => Task.FromResult(ctx))
            .When("schema is read", ExecuteReadSchema)
            .Then("no RDB$ tables included", r =>
                !r.Schema.Tables.Any(t => t.Name.StartsWith("RDB$", StringComparison.OrdinalIgnoreCase)))
            .And("no MON$ tables included", r =>
                !r.Schema.Tables.Any(t => t.Name.StartsWith("MON$", StringComparison.OrdinalIgnoreCase)))
            .Finally(r => r.Context.Dispose())
            .AssertPassed();
    }
}