using System.IO.Pipelines;
using Expecto.CSharp;
using Expecto;
using BitBadger.Documents.Sqlite;
using Microsoft.Data.Sqlite;

namespace BitBadger.Documents.Tests.CSharp;

using static Runner;

/// <summary>
/// C# tests for the extensions on the <c>SqliteConnection</c> class
/// </summary>
public static class SqliteCSharpExtensionTests
{
    private static async Task LoadDocs(SqliteConnection conn)
    {
        foreach (var doc in JsonDocument.TestDocuments) await conn.Insert(SqliteDb.TableName, doc);
    }

    /// <summary>Verify a JSON array begins with "[" and ends with "]"</summary>
    private static void VerifyBeginEnd(string json)
    {
        Expect.stringStarts(json, "[", "The array should have started with `[`");
        Expect.stringEnds(json, "]", "The array should have ended with `]`");
    }

    /// <summary>Verify an empty JSON array</summary>
    private static void VerifyEmpty(string json) =>
        Expect.equal(json, "[]", "There should be no documents returned");

    /// <summary>Verify an empty JSON document</summary>
    private static void VerifyNoDoc(string json) =>
        Expect.equal(json, "{}", "There should be no document returned");

    /// <summary>Set up a stream writer for a test</summary>
    private static PipeWriter WriteStream(Stream stream) =>
        PipeWriter.Create(stream, new StreamPipeWriterOptions(leaveOpen: true));

    /// <summary>Get the text of the given stream</summary>
    private static string StreamText(Stream stream)
    {
        stream.Position = 0L;
        using StreamReader reader = new(stream);
        return reader.ReadToEnd();
    }

    /// Verify the presence of any of the given documents in the given JSON
    private static void VerifyAny(string json, IEnumerable<string> docs)
    {
        var theDocs = docs.ToList();
        if (theDocs.Any(json.Contains)) return;
        var anyDocs = string.Join(" | ", theDocs);
        Expect.isTrue(false, $"Could not find any of |{anyDocs}| in {json}");
    }

    /// <summary>
    /// Integration tests for the SQLite extension methods
    /// </summary>
    [Tests]
    public static readonly Test Integration = TestList("Sqlite.C#.Extensions",
    [
        TestList("CustomList",
        [
            TestCase("succeeds when data is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var docs = await conn.CustomList(Query.Find(SqliteDb.TableName), Parameters.None,
                    Results.FromData<JsonDocument>);
                Expect.equal(docs.Count, 5, "There should have been 5 documents returned");
            }),
            TestCase("succeeds when data is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var docs = await conn.CustomList(
                    $"SELECT data FROM {SqliteDb.TableName} WHERE data ->> 'NumValue' > @value", [new("@value", 100)],
                    Results.FromData<JsonDocument>);
                Expect.isEmpty(docs, "There should have been no documents returned");
            })
        ]),
        TestList("CustomJsonArray",
        [
            TestCase("succeeds when data is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var json = await conn.CustomJsonArray(Query.Find(SqliteDb.TableName), [], Results.JsonFromData);
                VerifyBeginEnd(json);
                Expect.stringContains(json, JsonDocument.One, "Document ID `one` should have been found");
                Expect.stringContains(json, JsonDocument.Two,"Document ID `two` should have been found");
                Expect.stringContains(json, JsonDocument.Three, "Document ID `three` should have been found");
                Expect.stringContains(json, JsonDocument.Four, "Document ID `four` should have been found");
                Expect.stringContains(json, JsonDocument.Five, "Document ID `five` should have been found");
            }),
            TestCase("succeeds when data is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                VerifyEmpty(await conn.CustomJsonArray(
                    $"SELECT data FROM {SqliteDb.TableName} WHERE data ->> 'NumValue' > @value",
                    [new SqliteParameter("@value", 100)], Results.JsonFromData));
            })
        ]),
        TestList("WriteCustomJsonArray",
        [
            TestCase("succeeds when data is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteCustomJsonArray(Query.Find(SqliteDb.TableName), [], writer, Results.JsonFromData);
                    var json = StreamText(stream);
                    VerifyBeginEnd(json);
                    Expect.stringContains(json, JsonDocument.One, "Document ID `one` should have been found");
                    Expect.stringContains(json, JsonDocument.Two, "Document ID `two` should have been found");
                    Expect.stringContains(json, JsonDocument.Three, "Document ID `three` should have been found");
                    Expect.stringContains(json, JsonDocument.Four, "Document ID `four` should have been found");
                    Expect.stringContains(json, JsonDocument.Five, "Document ID `five` should have been found");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when data is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteCustomJsonArray(
                        $"SELECT data FROM {SqliteDb.TableName} WHERE data ->> 'NumValue' > @value",
                        [new SqliteParameter("@value", 100)], writer, Results.JsonFromData);
                    VerifyEmpty(StreamText(stream));
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            })
        ]),
        TestList("CustomSingle",
        [
            TestCase("succeeds when a row is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var doc = await conn.CustomSingle($"SELECT data FROM {SqliteDb.TableName} WHERE data ->> 'Id' = @id",
                    [Parameters.Id("one")], Results.FromData<JsonDocument>);
                Expect.isNotNull(doc, "There should have been a document returned");
                Expect.equal(doc!.Id, "one", "The incorrect document was returned");
            }),
            TestCase("succeeds when a row is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var doc = await conn.CustomSingle($"SELECT data FROM {SqliteDb.TableName} WHERE data ->> 'Id' = @id",
                    [Parameters.Id("eighty")], Results.FromData<JsonDocument>);
                Expect.isNull(doc, "There should not have been a document returned");
            })
        ]),
        TestList("CustomJsonSingle",
        [
            TestCase("succeeds when a row is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var json = await conn.CustomJsonSingle(
                    $"SELECT data FROM {SqliteDb.TableName} WHERE data ->> 'Id' = @id",
                    [new SqliteParameter("@id", "one")], Results.JsonFromData);
                Expect.equal(json, JsonDocument.One, "The JSON document is incorrect");
            }),
            TestCase("succeeds when a row is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                VerifyNoDoc(await conn.CustomJsonSingle(
                    $"SELECT data FROM {SqliteDb.TableName} WHERE data ->> 'Id' = @id",
                    [new SqliteParameter("@id", "eighty")], Results.JsonFromData));
            })
        ]),
        TestList("CustomNonQuery",
        [
            TestCase("succeeds when operating on data", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.CustomNonQuery($"DELETE FROM {SqliteDb.TableName}", Parameters.None);

                var remaining = await conn.CountAll(SqliteDb.TableName);
                Expect.equal(remaining, 0L, "There should be no documents remaining in the table");
            }),
            TestCase("succeeds when no data matches where clause", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.CustomNonQuery($"DELETE FROM {SqliteDb.TableName} WHERE data ->> 'NumValue' > @value",
                    [new("@value", 100)]);

                var remaining = await conn.CountAll(SqliteDb.TableName);
                Expect.equal(remaining, 5L, "There should be 5 documents remaining in the table");
            })
        ]),
        TestCase("CustomScalar succeeds", async () =>
        {
            await using var db = await SqliteDb.BuildDb();
            await using var conn = Sqlite.Configuration.DbConn();

            var nbr = await conn.CustomScalar("SELECT 5 AS test_value", Parameters.None, rdr => rdr.GetInt32(0));
            Expect.equal(nbr, 5, "The query should have returned the number 5");
        }),
        TestCase("EnsureTable succeeds", async () =>
        {
            await using var db = await SqliteDb.BuildDb();
            await using var conn = Sqlite.Configuration.DbConn();

            var exists = await ItExists("ensured");
            var alsoExists = await ItExists("idx_ensured_key");
            Expect.isFalse(exists, "The table should not exist already");
            Expect.isFalse(alsoExists, "The key index should not exist already");

            await conn.EnsureTable("ensured");

            exists = await ItExists("ensured");
            alsoExists = await ItExists("idx_ensured_key");
            Expect.isTrue(exists, "The table should now exist");
            Expect.isTrue(alsoExists, "The key index should now exist");
            return;

            Task<bool> ItExists(string name) =>
                conn.CustomScalar($"SELECT EXISTS (SELECT 1 FROM {SqliteDb.Catalog} WHERE name = @name) AS it",
                    [new("@name", name)], Results.ToExists);
        }),
        TestCase("EnsureFieldIndex succeeds", async () =>
        {
            await using var db = await SqliteDb.BuildDb();
            await using var conn = Sqlite.Configuration.DbConn();

            var exists = await IndexExists();
            Expect.isFalse(exists, "The index should not exist already");

            await conn.EnsureTable("ensured");
            await conn.EnsureFieldIndex("ensured", "test", ["Id", "Category"]);
            exists = await IndexExists();
            Expect.isTrue(exists, "The index should now exist");
            return;

            Task<bool> IndexExists() =>
                conn.CustomScalar(
                    $"SELECT EXISTS (SELECT 1 FROM {SqliteDb.Catalog} WHERE name = 'idx_ensured_test') AS it",
                    Parameters.None, Results.ToExists);
        }),
        TestList("Insert",
        [
            TestCase("succeeds", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                var before = await conn.FindAll<SubDocument>(SqliteDb.TableName);
                Expect.isEmpty(before, "There should be no documents in the table");
                await conn.Insert(SqliteDb.TableName,
                    new JsonDocument { Id = "turkey", Sub = new() { Foo = "gobble", Bar = "gobble" } });
                var after = await conn.FindAll<JsonDocument>(SqliteDb.TableName);
                Expect.equal(after.Count, 1, "There should have been one document inserted");
            }),
            TestCase("fails for duplicate key", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await conn.Insert(SqliteDb.TableName, new JsonDocument { Id = "test" });
                try
                {
                    await Document.Insert(SqliteDb.TableName, new JsonDocument { Id = "test" });
                    Expect.isTrue(false, "An exception should have been raised for duplicate document ID insert");
                }
                catch (Exception)
                {
                    // This is what is supposed to happen
                }
            })
        ]),
        TestList("Save",
        [
            TestCase("succeeds when a document is inserted", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                var before = await conn.FindAll<JsonDocument>(SqliteDb.TableName);
                Expect.isEmpty(before, "There should be no documents in the table");

                await conn.Save(SqliteDb.TableName,
                    new JsonDocument { Id = "test", Sub = new() { Foo = "a", Bar = "b" } });
                var after = await conn.FindAll<JsonDocument>(SqliteDb.TableName);
                Expect.equal(after.Count, 1, "There should have been one document inserted");
            }),
            TestCase("succeeds when a document is updated", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await conn.Insert(SqliteDb.TableName,
                    new JsonDocument { Id = "test", Sub = new() { Foo = "a", Bar = "b" } });

                var before = await conn.FindById<string, JsonDocument>(SqliteDb.TableName, "test");
                Expect.isNotNull(before, "There should have been a document returned");
                Expect.equal(before!.Id, "test", "The document is not correct");
                Expect.isNotNull(before.Sub, "There should have been a sub-document");
                Expect.equal(before.Sub!.Foo, "a", "The document is not correct");
                Expect.equal(before.Sub.Bar, "b", "The document is not correct");

                await conn.Save(SqliteDb.TableName, new JsonDocument { Id = "test" });
                var after = await conn.FindById<string, JsonDocument>(SqliteDb.TableName, "test");
                Expect.isNotNull(after, "There should have been a document returned post-update");
                Expect.equal(after!.Id, "test", "The updated document is not correct");
                Expect.isNull(after.Sub, "There should not have been a sub-document in the updated document");
            })
        ]),
        TestCase("CountAll succeeds", async () =>
        {
            await using var db = await SqliteDb.BuildDb();
            await using var conn = Sqlite.Configuration.DbConn();
            await LoadDocs(conn);

            var theCount = await conn.CountAll(SqliteDb.TableName);
            Expect.equal(theCount, 5L, "There should have been 5 matching documents");
        }),
        TestCase("CountByField succeeds", async () =>
        {
            await using var db = await SqliteDb.BuildDb();
            await using var conn = Sqlite.Configuration.DbConn();
            await LoadDocs(conn);

            var theCount = await conn.CountByFields(SqliteDb.TableName, FieldMatch.Any,
                [Field.Equal("Value", "purple")]);
            Expect.equal(theCount, 2L, "There should have been 2 matching documents");
        }),
        TestList("ExistsById",
        [
            TestCase("succeeds when a document exists", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var exists = await conn.ExistsById(SqliteDb.TableName, "three");
                Expect.isTrue(exists, "There should have been an existing document");
            }),
            TestCase("succeeds when a document does not exist", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var exists = await conn.ExistsById(SqliteDb.TableName, "seven");
                Expect.isFalse(exists, "There should not have been an existing document");
            })
        ]),
        TestList("ExistsByFields",
        [
            TestCase("succeeds when documents exist", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var exists = await conn.ExistsByFields(SqliteDb.TableName, FieldMatch.Any,
                    [Field.GreaterOrEqual("NumValue", 10)]);
                Expect.isTrue(exists, "There should have been existing documents");
            }),
            TestCase("succeeds when no matching documents exist", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var exists =
                    await conn.ExistsByFields(SqliteDb.TableName, FieldMatch.Any, [Field.Equal("Nothing", "none")]);
                Expect.isFalse(exists, "There should not have been any existing documents");
            })
        ]),
        TestList("FindAll",
        [
            TestCase("succeeds when there is data", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();

                await conn.Insert(SqliteDb.TableName, new JsonDocument { Id = "one", Value = "two" });
                await conn.Insert(SqliteDb.TableName, new JsonDocument { Id = "three", Value = "four" });
                await conn.Insert(SqliteDb.TableName, new JsonDocument { Id = "five", Value = "six" });

                var results = await conn.FindAll<JsonDocument>(SqliteDb.TableName);
                Expect.equal(results.Count, 3, "There should have been 3 documents returned");
            }),
            TestCase("succeeds when there is no data", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                var results = await conn.FindAll<JsonDocument>(SqliteDb.TableName);
                Expect.isEmpty(results, "There should have been no documents returned");
            })
        ]),
        TestList("FindAllOrdered",
        [
            TestCase("succeeds when ordering numerically", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var results = await conn.FindAllOrdered<JsonDocument>(SqliteDb.TableName, [Field.Named("n:NumValue")]);
                Expect.hasLength(results, 5, "There should have been 5 documents returned");
                Expect.equal(string.Join('|', results.Select(x => x.Id)), "one|three|two|four|five",
                    "The documents were not ordered correctly");
            }),
            TestCase("succeeds when ordering numerically descending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var results =
                    await conn.FindAllOrdered<JsonDocument>(SqliteDb.TableName, [Field.Named("n:NumValue DESC")]);
                Expect.hasLength(results, 5, "There should have been 5 documents returned");
                Expect.equal(string.Join('|', results.Select(x => x.Id)), "five|four|two|three|one",
                    "The documents were not ordered correctly");
            }),
            TestCase("succeeds when ordering alphabetically", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var results = await conn.FindAllOrdered<JsonDocument>(SqliteDb.TableName, [Field.Named("Id DESC")]);
                Expect.hasLength(results, 5, "There should have been 5 documents returned");
                Expect.equal(string.Join('|', results.Select(x => x.Id)), "two|three|one|four|five",
                    "The documents were not ordered correctly");
            })
        ]),
        TestList("FindById",
        [
            TestCase("succeeds when a document is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var doc = await conn.FindById<string, JsonDocument>(SqliteDb.TableName, "two");
                Expect.isNotNull(doc, "There should have been a document returned");
                Expect.equal(doc!.Id, "two", "The incorrect document was returned");
            }),
            TestCase("succeeds when a document is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var doc = await conn.FindById<string, JsonDocument>(SqliteDb.TableName, "eighty-seven");
                Expect.isNull(doc, "There should not have been a document returned");
            })
        ]),
        TestList("FindByFields",
        [
            TestCase("succeeds when documents are found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var docs = await conn.FindByFields<JsonDocument>(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Greater("NumValue", 15)]);
                Expect.equal(docs.Count, 2, "There should have been two documents returned");
            }),
            TestCase("succeeds when documents are not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var docs = await conn.FindByFields<JsonDocument>(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Equal("Value", "mauve")]);
                Expect.isEmpty(docs, "There should have been no documents returned");
            })
        ]),
        TestList("ByFieldsOrdered",
        [
            TestCase("succeeds when documents are found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var docs = await conn.FindByFieldsOrdered<JsonDocument>(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Greater("NumValue", 15)], [Field.Named("Id")]);
                Expect.equal(string.Join('|', docs.Select(x => x.Id)), "five|four",
                    "There should have been two documents returned");
            }),
            TestCase("succeeds when documents are not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var docs = await conn.FindByFieldsOrdered<JsonDocument>(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Greater("NumValue", 15)], [Field.Named("Id DESC")]);
                Expect.equal(string.Join('|', docs.Select(x => x.Id)), "four|five",
                    "There should have been two documents returned");
            })
        ]),
        TestList("FindFirstByFields",
        [
            TestCase("succeeds when a document is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var doc = await conn.FindFirstByFields<JsonDocument>(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Equal("Value", "another")]);
                Expect.isNotNull(doc, "There should have been a document returned");
                Expect.equal(doc!.Id, "two", "The incorrect document was returned");
            }),
            TestCase("succeeds when multiple documents are found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var doc = await conn.FindFirstByFields<JsonDocument>(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Equal("Sub.Foo", "green")]);
                Expect.isNotNull(doc, "There should have been a document returned");
                Expect.contains(["two", "four"], doc!.Id, "An incorrect document was returned");
            }),
            TestCase("succeeds when a document is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var doc = await conn.FindFirstByFields<JsonDocument>(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Equal("Value", "absent")]);
                Expect.isNull(doc, "There should not have been a document returned");
            })
        ]),
        TestList("FindFirstByFieldsOrdered",
        [
            TestCase("succeeds when sorting ascending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var doc = await conn.FindFirstByFieldsOrdered<JsonDocument>(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Equal("Sub.Foo", "green")], [Field.Named("Sub.Bar")]);
                Expect.isNotNull(doc, "There should have been a document returned");
                Expect.equal("two", doc!.Id, "An incorrect document was returned");
            }),
            TestCase("succeeds when sorting descending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var doc = await conn.FindFirstByFieldsOrdered<JsonDocument>(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Equal("Sub.Foo", "green")], [Field.Named("Sub.Bar DESC")]);
                Expect.isNotNull(doc, "There should have been a document returned");
                Expect.equal("four", doc!.Id, "An incorrect document was returned");
            })
        ]),
        TestList("JsonAll",
        [
            TestCase("succeeds when there is data", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();

                await conn.Insert(SqliteDb.TableName, new SubDocument { Foo = "one", Bar = "two" });
                await conn.Insert(SqliteDb.TableName, new SubDocument { Foo = "three", Bar = "four" });
                await conn.Insert(SqliteDb.TableName, new SubDocument { Foo = "five", Bar = "six" });

                var json = await conn.JsonAll(SqliteDb.TableName);
                VerifyBeginEnd(json);
                Expect.stringContains(json, """{"Foo":"one","Bar":"two"}""", "The first document was not found");
                Expect.stringContains(json, """{"Foo":"three","Bar":"four"}""", "The second document was not found");
                Expect.stringContains(json, """{"Foo":"five","Bar":"six"}""", "The third document was not found");
            }),
            TestCase("succeeds when there is no data", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                VerifyEmpty(await conn.JsonAll(SqliteDb.TableName));
            })
        ]),
        TestList("JsonAllOrdered",
        [
            TestCase("succeeds when ordering numerically", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(await conn.JsonAllOrdered(SqliteDb.TableName, [Field.Named("n:NumValue")]),
                    $"[{JsonDocument.One},{JsonDocument.Three},{JsonDocument.Two},{JsonDocument.Four},{JsonDocument.Five}]",
                    "The documents were not ordered correctly");
            }),
            TestCase("succeeds when ordering numerically descending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(await conn.JsonAllOrdered(SqliteDb.TableName, [Field.Named("n:NumValue DESC")]),
                    $"[{JsonDocument.Five},{JsonDocument.Four},{JsonDocument.Two},{JsonDocument.Three},{JsonDocument.One}]",
                    "The documents were not ordered correctly");
            }),
            TestCase("succeeds when ordering alphabetically", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(await conn.JsonAllOrdered(SqliteDb.TableName, [Field.Named("Id DESC")]),
                    $"[{JsonDocument.Two},{JsonDocument.Three},{JsonDocument.One},{JsonDocument.Four},{JsonDocument.Five}]",
                    "The documents were not ordered correctly");
            })
        ]),
        TestList("JsonById",
        [
            TestCase("succeeds when a document is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(await conn.JsonById(SqliteDb.TableName, "two"), JsonDocument.Two,
                    "The incorrect document was returned");
            }),
            TestCase("succeeds when a document is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                VerifyNoDoc(await conn.JsonById(SqliteDb.TableName, "three hundred eighty-seven"));
            })
        ]),
        TestList("JsonByFields",
        [
            TestCase("succeeds when documents are found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var json = await conn.JsonByFields(SqliteDb.TableName, FieldMatch.Any, [Field.Greater("NumValue", 15)]);
                VerifyBeginEnd(json);
                Expect.stringContains(json, JsonDocument.Four, "Document `four` should have been returned");
                Expect.stringContains(json, JsonDocument.Five, "Document `five` should have been returned");
            }),
            TestCase("succeeds when documents are found using IN with numeric field", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(
                    await conn.JsonByFields(SqliteDb.TableName, FieldMatch.All, [Field.In("NumValue", [2, 4, 6, 8])]),
                    $"[{JsonDocument.Three}]", "There should have been one document returned");
            }),
            TestCase("succeeds when documents are not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                VerifyEmpty(await conn.JsonByFields(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Greater("NumValue", 100)]));
            }),
            TestCase("succeeds for InArray when matching documents exist", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await conn.EnsureTable(SqliteDb.TableName);
                foreach (var doc in ArrayDocument.TestDocuments) await conn.Insert(SqliteDb.TableName, doc);

                var json = await conn.JsonByFields(SqliteDb.TableName, FieldMatch.All,
                    [Field.InArray("Values", SqliteDb.TableName, ["c"])]);
                VerifyBeginEnd(json);
                Expect.stringContains(json, """{"Id":"first","Values":["a","b","c"]}""",
                    "Document `first` should have been returned");
                Expect.stringContains(json, """{"Id":"second","Values":["c","d","e"]}""",
                    "Document `second` should have been returned");
            }),
            TestCase("succeeds for InArray when no matching documents exist", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await conn.EnsureTable(SqliteDb.TableName);
                foreach (var doc in ArrayDocument.TestDocuments) await conn.Insert(SqliteDb.TableName, doc);
                VerifyEmpty(await conn.JsonByFields(SqliteDb.TableName, FieldMatch.All,
                    [Field.InArray("Values", SqliteDb.TableName, ["j"])]));
            })
        ]),
        TestList("JsonByFieldsOrdered",
        [
            TestCase("succeeds when sorting ascending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(
                    await conn.JsonByFieldsOrdered(SqliteDb.TableName, FieldMatch.Any, [Field.Greater("NumValue", 15)],
                        [Field.Named("Id")]), $"[{JsonDocument.Five},{JsonDocument.Four}]",
                    "Incorrect documents were returned");
            }),
            TestCase("succeeds when sorting descending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(
                    await conn.JsonByFieldsOrdered(SqliteDb.TableName, FieldMatch.Any, [Field.Greater("NumValue", 15)],
                        [Field.Named("Id DESC")]), $"[{JsonDocument.Four},{JsonDocument.Five}]",
                    "Incorrect documents were returned");
            }),
            TestCase("succeeds when sorting case-sensitively", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(
                    await conn.JsonByFieldsOrdered(SqliteDb.TableName, FieldMatch.All,
                        [Field.LessOrEqual("NumValue", 10)], [Field.Named("Value")]),
                    $"[{JsonDocument.Three},{JsonDocument.One},{JsonDocument.Two}]", "Documents not ordered correctly");
            }),
            TestCase("succeeds when sorting case-insensitively", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(
                    await conn.JsonByFieldsOrdered(SqliteDb.TableName, FieldMatch.All,
                        [Field.LessOrEqual("NumValue", 10)], [Field.Named("i:Value")]),
                    $"[{JsonDocument.Three},{JsonDocument.Two},{JsonDocument.One}]", "Documents not ordered correctly");
            })
        ]),
        TestList("JsonFirstByFields",
        [
            TestCase("succeeds when a document is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(
                    await conn.JsonFirstByFields(SqliteDb.TableName, FieldMatch.Any, [Field.Equal("Value", "another")]),
                    JsonDocument.Two, "The incorrect document was returned");
            }),
            TestCase("succeeds when multiple documents are found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var json = await conn.JsonFirstByFields(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Equal("Sub.Foo", "green")]);
                Expect.notEqual(json, "{}", "There should have been a document returned");
                VerifyAny(json, [JsonDocument.Two, JsonDocument.Four]);
            }),
            TestCase("succeeds when a document is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                VerifyNoDoc(await conn.JsonFirstByFields(SqliteDb.TableName, FieldMatch.Any,
                    [Field.Equal("Value", "absent")]));
            })
        ]),
        TestList("JsonFirstByFieldsOrdered",
        [
            TestCase("succeeds when sorting ascending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(
                    await conn.JsonFirstByFieldsOrdered(SqliteDb.TableName, FieldMatch.Any,
                        [Field.Equal("Sub.Foo", "green")], [Field.Named("Sub.Bar")]), JsonDocument.Two,
                    "An incorrect document was returned");
            }),
            TestCase("succeeds when sorting descending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);
                Expect.equal(
                    await conn.JsonFirstByFieldsOrdered(SqliteDb.TableName, FieldMatch.Any,
                        [Field.Equal("Sub.Foo", "green")], [Field.Named("Sub.Bar DESC")]), JsonDocument.Four,
                    "An incorrect document was returned");
            })
        ]),
        TestList("WriteJsonAll",
        [
            TestCase("succeeds when there is data", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();

                await conn.Insert(SqliteDb.TableName, new SubDocument { Foo = "one", Bar = "two" });
                await conn.Insert(SqliteDb.TableName, new SubDocument { Foo = "three", Bar = "four" });
                await conn.Insert(SqliteDb.TableName, new SubDocument { Foo = "five", Bar = "six" });

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonAll(SqliteDb.TableName, writer);
                    var json = StreamText(stream);
                    VerifyBeginEnd(json);
                    Expect.stringContains(json, """{"Foo":"one","Bar":"two"}""", "The first document was not found");
                    Expect.stringContains(json, """{"Foo":"three","Bar":"four"}""",
                        "The second document was not found");
                    Expect.stringContains(json, """{"Foo":"five","Bar":"six"}""", "The third document was not found");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when there is no data", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonAll(SqliteDb.TableName, writer);
                    VerifyEmpty(StreamText(stream));
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            })
        ]),
        TestList("WriteJsonAllOrdered",
        [
            TestCase("succeeds when ordering numerically", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonAllOrdered(SqliteDb.TableName, writer, [Field.Named("n:NumValue")]);
                    Expect.equal(StreamText(stream),
                        $"[{JsonDocument.One},{JsonDocument.Three},{JsonDocument.Two},{JsonDocument.Four},{JsonDocument.Five}]",
                        "The documents were not ordered correctly");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when ordering numerically descending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonAllOrdered(SqliteDb.TableName, writer, [Field.Named("n:NumValue DESC")]);
                    Expect.equal(StreamText(stream),
                        $"[{JsonDocument.Five},{JsonDocument.Four},{JsonDocument.Two},{JsonDocument.Three},{JsonDocument.One}]",
                        "The documents were not ordered correctly");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when ordering alphabetically", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonAllOrdered(SqliteDb.TableName, writer, [Field.Named("Id DESC")]);
                    Expect.equal(StreamText(stream),
                        $"[{JsonDocument.Two},{JsonDocument.Three},{JsonDocument.One},{JsonDocument.Four},{JsonDocument.Five}]",
                        "The documents were not ordered correctly");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            })
        ]),
        TestList("WriteJsonById",
        [
            TestCase("succeeds when a document is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonById(SqliteDb.TableName, writer, "two");
                    Expect.equal(StreamText(stream), JsonDocument.Two, "The incorrect document was returned");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when a document is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonById(SqliteDb.TableName, writer, "three hundred eighty-seven");
                    VerifyNoDoc(StreamText(stream));
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            })
        ]),
        TestList("WriteJsonByFields",
        [
            TestCase("succeeds when documents are found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonByFields(SqliteDb.TableName, writer, FieldMatch.Any,
                        [Field.Greater("NumValue", 15)]);
                    var json = StreamText(stream);
                    VerifyBeginEnd(json);
                    Expect.stringContains(json, JsonDocument.Four, "Document `four` should have been returned");
                    Expect.stringContains(json, JsonDocument.Five, "Document `five` should have been returned");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when documents are found using IN with numeric field", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonByFields(SqliteDb.TableName, writer, FieldMatch.All,
                        [Field.In("NumValue", [2, 4, 6, 8])]);
                    Expect.equal(StreamText(stream), $"[{JsonDocument.Three}]",
                        "There should have been one document returned");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when documents are not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonByFields(SqliteDb.TableName, writer, FieldMatch.Any,
                        [Field.Greater("NumValue", 100)]);
                    VerifyEmpty(StreamText(stream));
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds for InArray when matching documents exist", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await conn.EnsureTable(SqliteDb.TableName);
                foreach (var doc in ArrayDocument.TestDocuments) await conn.Insert(SqliteDb.TableName, doc);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonByFields(SqliteDb.TableName, writer, FieldMatch.All,
                        [Field.InArray("Values", SqliteDb.TableName, ["c"])]);
                    var json = StreamText(stream);
                    VerifyBeginEnd(json);
                    Expect.stringContains(json, """{"Id":"first","Values":["a","b","c"]}""",
                        "Document `first` should have been returned");
                    Expect.stringContains(json, """{"Id":"second","Values":["c","d","e"]}""",
                        "Document `second` should have been returned");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds for InArray when no matching documents exist", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await conn.EnsureTable(SqliteDb.TableName);
                foreach (var doc in ArrayDocument.TestDocuments) await conn.Insert(SqliteDb.TableName, doc);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonByFields(SqliteDb.TableName, writer, FieldMatch.All,
                        [Field.InArray("Values", SqliteDb.TableName, ["j"])]);
                    VerifyEmpty(StreamText(stream));
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            })
        ]),
        TestList("WriteJsonByFieldsOrdered",
        [
            TestCase("succeeds when sorting ascending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonByFieldsOrdered(SqliteDb.TableName, writer, FieldMatch.Any,
                        [Field.Greater("NumValue", 15)], [Field.Named("Id")]);
                    Expect.equal(StreamText(stream), $"[{JsonDocument.Five},{JsonDocument.Four}]",
                        "Incorrect documents were returned");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when sorting descending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonByFieldsOrdered(SqliteDb.TableName, writer, FieldMatch.Any,
                        [Field.Greater("NumValue", 15)], [Field.Named("Id DESC")]);
                    Expect.equal(StreamText(stream), $"[{JsonDocument.Four},{JsonDocument.Five}]",
                        "Incorrect documents were returned");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when sorting case-sensitively", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonByFieldsOrdered(SqliteDb.TableName, writer, FieldMatch.All,
                        [Field.LessOrEqual("NumValue", 10)], [Field.Named("Value")]);
                    Expect.equal(StreamText(stream), $"[{JsonDocument.Three},{JsonDocument.One},{JsonDocument.Two}]",
                        "Documents not ordered correctly");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when sorting case-insensitively", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonByFieldsOrdered(SqliteDb.TableName, writer, FieldMatch.All,
                        [Field.LessOrEqual("NumValue", 10)], [Field.Named("i:Value")]);
                    Expect.equal(StreamText(stream), $"[{JsonDocument.Three},{JsonDocument.Two},{JsonDocument.One}]",
                        "Documents not ordered correctly");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            })
        ]),
        TestList("WriteJsonFirstByFields",
        [
            TestCase("succeeds when a document is found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonFirstByFields(SqliteDb.TableName, writer, FieldMatch.Any,
                        [Field.Equal("Value", "another")]);
                    Expect.equal(StreamText(stream), JsonDocument.Two, "The incorrect document was returned");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when multiple documents are found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonFirstByFields(SqliteDb.TableName, writer, FieldMatch.Any,
                        [Field.Equal("Sub.Foo", "green")]);
                    var json = StreamText(stream);
                    Expect.notEqual(json, "{}", "There should have been a document returned");
                    VerifyAny(json, [JsonDocument.Two, JsonDocument.Four]);
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when a document is not found", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonFirstByFields(SqliteDb.TableName, writer, FieldMatch.Any,
                        [Field.Equal("Value", "absent")]);
                    VerifyNoDoc(StreamText(stream));
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            })
        ]),
        TestList("WriteJsonFirstByFieldsOrdered",
        [
            TestCase("succeeds when sorting ascending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonFirstByFieldsOrdered(SqliteDb.TableName, writer, FieldMatch.Any,
                        [Field.Equal("Sub.Foo", "green")], [Field.Named("Sub.Bar")]);
                    Expect.equal(StreamText(stream), JsonDocument.Two, "An incorrect document was returned");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            }),
            TestCase("succeeds when sorting descending", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                using MemoryStream stream = new();
                var writer = WriteStream(stream);
                try
                {
                    await conn.WriteJsonFirstByFieldsOrdered(SqliteDb.TableName, writer, FieldMatch.Any,
                        [Field.Equal("Sub.Foo", "green")], [Field.Named("Sub.Bar DESC")]);
                    Expect.equal(StreamText(stream), JsonDocument.Four, "An incorrect document was returned");
                }
                finally
                {
                    await writer.CompleteAsync();
                }
            })
        ]),
        TestList("UpdateById",
        [
            TestCase("succeeds when a document is updated", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                var testDoc = new JsonDocument { Id = "one", Sub = new() { Foo = "blue", Bar = "red" } };
                await conn.UpdateById(SqliteDb.TableName, "one", testDoc);
                var after = await conn.FindById<string, JsonDocument>(SqliteDb.TableName, "one");
                Expect.isNotNull(after, "There should have been a document returned post-update");
                Expect.equal(after.Id, "one", "The updated document is not correct");
                Expect.isNotNull(after.Sub, "The updated document should have had a sub-document");
                Expect.equal(after.Sub!.Foo, "blue", "The updated sub-document is not correct");
                Expect.equal(after.Sub.Bar, "red", "The updated sub-document is not correct");
            }),
            TestCase("succeeds when no document is updated", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                var before = await conn.FindAll<JsonDocument>(SqliteDb.TableName);
                Expect.isEmpty(before, "There should have been no documents returned");

                // This not raising an exception is the test
                await conn.UpdateById(SqliteDb.TableName, "test",
                    new JsonDocument { Id = "x", Sub = new() { Foo = "blue", Bar = "red" } });
            })
        ]),
        TestList("UpdateByFunc",
        [
            TestCase("succeeds when a document is updated", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.UpdateByFunc(SqliteDb.TableName, doc => doc.Id,
                    new JsonDocument { Id = "one", Value = "le un", NumValue = 1 });
                var after = await conn.FindById<string, JsonDocument>(SqliteDb.TableName, "one");
                Expect.isNotNull(after, "There should have been a document returned post-update");
                Expect.equal(after.Id, "one", "The updated document is incorrect");
                Expect.equal(after.Value, "le un", "The updated document is incorrect");
                Expect.equal(after.NumValue, 1, "The updated document is incorrect");
                Expect.isNull(after.Sub, "The updated document should not have a sub-document");
            }),
            TestCase("succeeds when no document is updated", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                var before = await conn.FindAll<JsonDocument>(SqliteDb.TableName);
                Expect.isEmpty(before, "There should have been no documents returned");

                // This not raising an exception is the test
                await conn.UpdateByFunc(SqliteDb.TableName, doc => doc.Id,
                    new JsonDocument { Id = "one", Value = "le un", NumValue = 1 });
            })
        ]),
        TestList("PatchById",
        [
            TestCase("succeeds when a document is updated", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.PatchById(SqliteDb.TableName, "one", new { NumValue = 44 });
                var after = await conn.FindById<string, JsonDocument>(SqliteDb.TableName, "one");
                Expect.isNotNull(after, "There should have been a document returned post-update");
                Expect.equal(after.Id, "one", "The updated document is not correct");
                Expect.equal(after.NumValue, 44, "The updated document is not correct");
            }),
            TestCase("succeeds when no document is updated", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                var before = await conn.FindAll<JsonDocument>(SqliteDb.TableName);
                Expect.isEmpty(before, "There should have been no documents returned");

                // This not raising an exception is the test
                await conn.PatchById(SqliteDb.TableName, "test", new { Foo = "green" });
            })
        ]),
        TestList("PatchByFields",
        [
            TestCase("succeeds when a document is updated", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.PatchByFields(SqliteDb.TableName, FieldMatch.Any, [Field.Equal("Value", "purple")],
                    new { NumValue = 77 });
                var after = await conn.CountByFields(SqliteDb.TableName, FieldMatch.Any, [Field.Equal("NumValue", 77)]);
                Expect.equal(after, 2L, "There should have been 2 documents returned");
            }),
            TestCase("succeeds when no document is updated", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                var before = await conn.FindAll<JsonDocument>(SqliteDb.TableName);
                Expect.isEmpty(before, "There should have been no documents returned");

                // This not raising an exception is the test
                await conn.PatchByFields(SqliteDb.TableName, FieldMatch.Any, [Field.Equal("Value", "burgundy")],
                    new { Foo = "green" });
            })
        ]),
        TestList("RemoveFieldsById",
        [
            TestCase("succeeds when fields are removed", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.RemoveFieldsById(SqliteDb.TableName, "two", ["Sub", "Value"]);
                var updated = await Find.ById<string, JsonDocument>(SqliteDb.TableName, "two");
                Expect.isNotNull(updated, "The updated document should have been retrieved");
                Expect.equal(updated.Value, "", "The string value should have been removed");
                Expect.isNull(updated.Sub, "The sub-document should have been removed");
            }),
            TestCase("succeeds when a field is not removed", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                // This not raising an exception is the test
                await conn.RemoveFieldsById(SqliteDb.TableName, "two", ["AFieldThatIsNotThere"]);
            }),
            TestCase("succeeds when no document is matched", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();

                // This not raising an exception is the test
                await conn.RemoveFieldsById(SqliteDb.TableName, "two", ["Value"]);
            })
        ]),
        TestList("RemoveFieldsByFields",
        [
            TestCase("succeeds when a field is removed", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.RemoveFieldsByFields(SqliteDb.TableName, FieldMatch.Any, [Field.Equal("NumValue", 17)],
                    ["Sub"]);
                var updated = await Find.ById<string, JsonDocument>(SqliteDb.TableName, "four");
                Expect.isNotNull(updated, "The updated document should have been retrieved");
                Expect.isNull(updated.Sub, "The sub-document should have been removed");
            }),
            TestCase("succeeds when a field is not removed", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                // This not raising an exception is the test
                await conn.RemoveFieldsByFields(SqliteDb.TableName, FieldMatch.Any, [Field.Equal("NumValue", 17)],
                    ["Nothing"]);
            }),
            TestCase("succeeds when no document is matched", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();

                // This not raising an exception is the test
                await conn.RemoveFieldsByFields(SqliteDb.TableName, FieldMatch.Any,
                    [Field.NotEqual("Abracadabra", "apple")], ["Value"]);
            })
        ]),
        TestList("DeleteById",
        [
            TestCase("succeeds when a document is deleted", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.DeleteById(SqliteDb.TableName, "four");
                var remaining = await conn.CountAll(SqliteDb.TableName);
                Expect.equal(remaining, 4L, "There should have been 4 documents remaining");
            }),
            TestCase("succeeds when a document is not deleted", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.DeleteById(SqliteDb.TableName, "thirty");
                var remaining = await conn.CountAll(SqliteDb.TableName);
                Expect.equal(remaining, 5L, "There should have been 5 documents remaining");
            })
        ]),
        TestList("DeleteByFields",
        [
            TestCase("succeeds when documents are deleted", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.DeleteByFields(SqliteDb.TableName, FieldMatch.Any, [Field.NotEqual("Value", "purple")]);
                var remaining = await conn.CountAll(SqliteDb.TableName);
                Expect.equal(remaining, 2L, "There should have been 2 documents remaining");
            }),
            TestCase("succeeds when documents are not deleted", async () =>
            {
                await using var db = await SqliteDb.BuildDb();
                await using var conn = Sqlite.Configuration.DbConn();
                await LoadDocs(conn);

                await conn.DeleteByFields(SqliteDb.TableName, FieldMatch.Any, [Field.Equal("Value", "crimson")]);
                var remaining = await conn.CountAll(SqliteDb.TableName);
                Expect.equal(remaining, 5L, "There should have been 5 documents remaining");
            })
        ]),
        TestCase("Clean up database", () => Sqlite.Configuration.UseConnectionString("data source=:memory:"))
    ]);
}