#include "data_sqlite.h"
#include "startup.h"

#include <chrono>
#include <cstdint>
#include <filesystem>
#include <memory>
#include <string>
#include <utility>

#include <gtest/gtest.h>

namespace
{

class TemporaryDatabase
{
public:
    /// Allocate a unique temporary database path for one test.
    TemporaryDatabase()
            : path_(
                std::filesystem::path(testing::TempDir()) /
                ("card_collection_" + std::to_string(
                    std::chrono::steady_clock::now()
                        .time_since_epoch().count()) + ".sqlite3"))
    {}

    /// Remove the test database and its SQLite sidecar files.
    ~TemporaryDatabase()
    {
        std::error_code error;
        std::filesystem::remove(path_, error);
        std::filesystem::remove(path_.string() + "-shm", error);
        std::filesystem::remove(path_.string() + "-wal", error);
    }

    /// Return the allocated database path.
    const std::filesystem::path& path() const
    {
        return path_;
    }

private:
    std::filesystem::path path_;
};

Card makeLooseCard(std::uint32_t number, std::string name)
{
    return {
        0,
        {std::nullopt, number},
        std::move(name),
        std::nullopt,
        std::nullopt,
        0,
        "jpg",
        std::nullopt,
        "avif",
        1,
        1,
    };
}

} // namespace

/// Verify the SQLite factory owns an opened connection.
TEST(DataSourceSQLiteTest, OpensDatabase)
{
    auto data_source = DataSourceSQLite::fromFile(":memory:");

    ASSERT_TRUE(data_source);
    EXPECT_NE(*data_source, nullptr);
}

/// Verify a fresh unmigrated connection has no application tables.
TEST(DataSourceSQLiteTest, RejectsReadsBeforeMigration)
{
    auto data_source_result = DataSourceSQLite::fromFile(":memory:");
    ASSERT_TRUE(data_source_result);
    std::unique_ptr<DataSourceSQLite> data_source =
        std::move(*data_source_result);

    EXPECT_FALSE(data_source->getSeries(
        GameContentScope::INCLUDE_INTERNAL));
    EXPECT_FALSE(data_source->getSeries(
        1, GameContentScope::INCLUDE_INTERNAL));
    EXPECT_FALSE(data_source->getGames(
        GameContentScope::INCLUDE_INTERNAL));
}

/// Verify startup prepares a fresh database for its first card-index read.
TEST(DataSourceSQLiteTest, PreparesFreshDatabaseForStartup)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source);

    auto version = (*data_source)->getSchemaVersion();
    ASSERT_TRUE(version);
    EXPECT_EQ(*version, DB_SCHEMA_VERSION);
    auto cards = (*data_source)->getCards(
        GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(cards);
    EXPECT_TRUE(cards->empty());
}

/// Verify card index reads preserve common and nullable card fields.
TEST(DataSourceSQLiteTest, ReturnsCards)
{
    TemporaryDatabase database;
    auto connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    ASSERT_TRUE((*connection)->execute(
        "CREATE TABLE cards ("
        "id INTEGER PRIMARY KEY, "
        "creator_user_id INTEGER NOT NULL, "
        "game_short_name TEXT, "
        "card_number INTEGER NOT NULL, "
        "name TEXT NOT NULL, "
        "short_description TEXT, "
        "long_description TEXT, "
        "rarity INTEGER NOT NULL, "
        "front_extension TEXT NOT NULL, "
        "foil_extension TEXT, "
        "thumbnail_extension TEXT NOT NULL, "
        "revision INTEGER NOT NULL);"));
    ASSERT_TRUE((*connection)->execute(
        "INSERT INTO cards VALUES (2, 1, 'pkm', 7, 'Moon card', "
        "'Short', 'Long', 4, 'avif', 'webp', 'avif', 3);"));
    ASSERT_TRUE((*connection)->execute(
        "INSERT INTO cards VALUES (1, 1, NULL, 35, 'Loose card', "
        "NULL, NULL, 0, 'jpg', NULL, 'webp', 1);"));
    connection->reset();

    auto data_source = DataSourceSQLite::fromFile(database.path());
    ASSERT_TRUE(data_source);
    auto cards = (*data_source)->getCards(
        GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(cards);
    ASSERT_EQ(cards->size(), 2);

    EXPECT_EQ((*cards)[0].id, 1);
    EXPECT_EQ((*cards)[0].identity.game_short_name, std::nullopt);
    EXPECT_EQ((*cards)[0].identity.card_number, 35);
    EXPECT_EQ((*cards)[0].name, "Loose card");
    EXPECT_EQ((*cards)[0].short_description, std::nullopt);
    EXPECT_EQ((*cards)[0].long_description, std::nullopt);
    EXPECT_EQ((*cards)[0].rarity, 0);
    EXPECT_EQ((*cards)[0].front_extension, "jpg");
    EXPECT_EQ((*cards)[0].foil_extension, std::nullopt);
    EXPECT_EQ((*cards)[0].thumbnail_extension, "webp");
    EXPECT_EQ((*cards)[0].revision, 1);
    EXPECT_EQ((*cards)[0].creator_user_id, 1);

    EXPECT_EQ((*cards)[1].id, 2);
    EXPECT_EQ((*cards)[1].identity.game_short_name, "pkm");
    EXPECT_EQ((*cards)[1].identity.card_number, 7);
    EXPECT_EQ((*cards)[1].name, "Moon card");
    EXPECT_EQ((*cards)[1].short_description, "Short");
    EXPECT_EQ((*cards)[1].long_description, "Long");
    EXPECT_EQ((*cards)[1].rarity, 4);
    EXPECT_EQ((*cards)[1].front_extension, "avif");
    EXPECT_EQ((*cards)[1].foil_extension, "webp");
    EXPECT_EQ((*cards)[1].thumbnail_extension, "avif");
    EXPECT_EQ((*cards)[1].revision, 3);

    auto loose_card = (*data_source)->getCard(
        {std::nullopt, 35}, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(loose_card);
    ASSERT_TRUE(*loose_card);
    EXPECT_EQ((**loose_card).id, 1);
    EXPECT_EQ((**loose_card).name, "Loose card");
}

/// Verify invalid persisted card identities fail the complete read.
TEST(DataSourceSQLiteTest, RejectsInvalidCards)
{
    TemporaryDatabase database;
    auto connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    ASSERT_TRUE((*connection)->execute(
        "CREATE TABLE cards ("
        "id INTEGER, creator_user_id INTEGER, game_short_name TEXT, "
        "card_number INTEGER, "
        "name TEXT, short_description TEXT, long_description TEXT, "
        "rarity INTEGER, front_extension TEXT, foil_extension TEXT, "
        "thumbnail_extension TEXT, revision INTEGER);"));
    ASSERT_TRUE((*connection)->execute(
        "INSERT INTO cards VALUES (1, 1, NULL, -1, 'Broken', NULL, NULL, "
        "0, 'jpg', NULL, 'jpg', 1);"));
    connection->reset();

    auto data_source = DataSourceSQLite::fromFile(database.path());
    ASSERT_TRUE(data_source);
    EXPECT_FALSE((*data_source)->getCards(
        GameContentScope::INCLUDE_INTERNAL));
}

/// Verify a committed transaction persists a complete loose card.
TEST(DataSourceSQLiteTest, InsertsLooseCard)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source);
    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);

    auto exists_before = (*transaction)->looseNumberExists(42);
    ASSERT_TRUE(exists_before);
    EXPECT_FALSE(*exists_before);
    auto card_id = (*transaction)->insertCard(
        makeLooseCard(42, "New card"), {}, {});
    ASSERT_TRUE(card_id);
    EXPECT_GT(*card_id, 0);
    auto exists_after = (*transaction)->looseNumberExists(42);
    ASSERT_TRUE(exists_after);
    EXPECT_TRUE(*exists_after);
    ASSERT_TRUE((*transaction)->commit());

    auto cards = (*data_source)->getCards(
        GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(cards);
    ASSERT_EQ(cards->size(), 1);
    EXPECT_EQ(cards->front().id, *card_id);
    EXPECT_EQ(cards->front().identity.card_number, 42);
    EXPECT_EQ(cards->front().name, "New card");
}

/// Verify transactional reads and updates preserve a loose-card identity.
TEST(DataSourceSQLiteTest, UpdatesLooseCard)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source);
    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    auto card_id = (*transaction)->insertCard(
        makeLooseCard(42, "Original"), {}, {});
    ASSERT_TRUE(card_id);
    ASSERT_TRUE((*transaction)->commit());

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    auto card = (*transaction)->getCardForUpdate(*card_id);
    ASSERT_TRUE(card);
    ASSERT_TRUE(*card);
    (**card).name = "Edited";
    (**card).rarity = 5;
    (**card).foil_extension = "webp";
    (**card).revision = 2;
    ASSERT_TRUE((*transaction)->updateCard(
        **card, {}, {}));
    ASSERT_TRUE((*transaction)->commit());

    auto stored = (*data_source)->getCard(
        {std::nullopt, 42}, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(stored);
    ASSERT_TRUE(*stored);
    EXPECT_EQ((**stored).id, *card_id);
    EXPECT_EQ((**stored).identity.card_number, 42);
    EXPECT_EQ((**stored).name, "Edited");
    EXPECT_EQ((**stored).rarity, 5);
    EXPECT_EQ((**stored).foil_extension, "webp");
    EXPECT_EQ((**stored).revision, 2);
}

/// Verify series can be created, edited, listed, and deleted transactionally.
TEST(DataSourceSQLiteTest, MutatesSeries)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source);

    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->insertGame(
        {"gh", "Going Home", "", GameVisibility::PUBLIC, 1}));
    auto series_id = (*transaction)->insertSeries(
        {0, "gh", "First series", "Description"});
    ASSERT_TRUE(series_id);
    ASSERT_TRUE((*transaction)->commit());

    auto all_series = (*data_source)->getSeries(
        GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(all_series);
    ASSERT_EQ(all_series->size(), 1);
    EXPECT_EQ(all_series->front().id, *series_id);
    auto series = (*data_source)->getSeries(
        *series_id, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(series);
    ASSERT_TRUE(*series);
    (**series).name = "Edited series";

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->updateSeries(**series));
    ASSERT_TRUE((*transaction)->commit());
    series = (*data_source)->getSeries(
        *series_id, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(series);
    ASSERT_TRUE(*series);
    EXPECT_EQ((**series).name, "Edited series");

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->deleteSeries(*series_id));
    ASSERT_TRUE((*transaction)->commit());
    series = (*data_source)->getSeries(
        *series_id, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(series);
    EXPECT_FALSE(*series);
}

TEST(DataSourceSQLiteTest, ScopesInternalGameContentAndPulls)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source) << data_source.error().msg();

    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->insertGame(
        {"public", "Public", "", GameVisibility::PUBLIC, 1}));
    ASSERT_TRUE((*transaction)->insertGame(
        {"internal", "Internal", "", GameVisibility::INTERNAL, 1}));
    auto public_series = (*transaction)->insertSeries(
        {0, "public", "Public series", ""});
    auto internal_series = (*transaction)->insertSeries(
        {0, "internal", "Internal series", ""});
    ASSERT_TRUE(public_series);
    ASSERT_TRUE(internal_series);
    auto public_number = (*transaction)->allocateGameNumber("public");
    auto internal_number = (*transaction)->allocateGameNumber("internal");
    ASSERT_TRUE(public_number);
    ASSERT_TRUE(internal_number);
    Card public_card = makeLooseCard(*public_number, "Public card");
    public_card.identity.game_short_name = "public";
    public_card.rarity = 1;
    Card internal_card = makeLooseCard(*internal_number, "Internal card");
    internal_card.identity.game_short_name = "internal";
    internal_card.rarity = 1;
    auto public_id = (*transaction)->insertCard(
        public_card, {}, {*public_series});
    auto internal_id = (*transaction)->insertCard(
        internal_card, {}, {*internal_series});
    ASSERT_TRUE(public_id);
    ASSERT_TRUE(internal_id);
    ASSERT_TRUE((*transaction)->incrementHolding(1, *public_id));
    ASSERT_TRUE((*transaction)->incrementHolding(1, *internal_id));
    ASSERT_TRUE((*transaction)->commit());

    auto public_games = (*data_source)->getGames(
        GameContentScope::PUBLIC_ONLY);
    auto all_games = (*data_source)->getGames(
        GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(public_games);
    ASSERT_TRUE(all_games);
    ASSERT_EQ(public_games->size(), 1);
    ASSERT_EQ(all_games->size(), 2);
    EXPECT_EQ(public_games->front().short_name, "public");
    EXPECT_EQ((*all_games)[0].visibility, GameVisibility::INTERNAL);
    EXPECT_EQ((*all_games)[1].visibility, GameVisibility::PUBLIC);

    auto public_cards = (*data_source)->getCards(
        GameContentScope::PUBLIC_ONLY);
    auto all_cards = (*data_source)->getCards(
        GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(public_cards);
    ASSERT_TRUE(all_cards);
    ASSERT_EQ(public_cards->size(), 1);
    ASSERT_EQ(all_cards->size(), 2);
    EXPECT_EQ(public_cards->front().name, "Public card");
    auto hidden_card = (*data_source)->getCard(
        {"internal", *internal_number}, GameContentScope::PUBLIC_ONLY);
    ASSERT_TRUE(hidden_card);
    EXPECT_FALSE(*hidden_card);
    auto hidden_definition = (*data_source)->getGameDefinition(
        "internal", GameContentScope::PUBLIC_ONLY);
    ASSERT_TRUE(hidden_definition);
    EXPECT_FALSE(*hidden_definition);
    auto hidden_fields = (*data_source)->getCardFieldValues(
        *internal_id, GameContentScope::PUBLIC_ONLY);
    ASSERT_TRUE(hidden_fields);
    EXPECT_FALSE(*hidden_fields);

    auto public_series_list = (*data_source)->getSeries(
        GameContentScope::PUBLIC_ONLY);
    auto public_collection = (*data_source)->getCollection(
        1, GameContentScope::PUBLIC_ONLY);
    auto pool = (*data_source)->getPoolCards();
    ASSERT_TRUE(public_series_list);
    ASSERT_TRUE(public_collection);
    ASSERT_TRUE(pool);
    EXPECT_EQ(public_series_list->size(), 1);
    EXPECT_EQ(public_collection->size(), 1);
    EXPECT_EQ(pool->size(), 1);

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    auto locked_pool = (*transaction)->getPoolCardsForUpdate();
    ASSERT_TRUE(locked_pool);
    EXPECT_EQ(locked_pool->size(), 1);
    auto visible = (*transaction)->updateGame(
        "internal", "Internal", "", GameVisibility::PUBLIC, 1);
    ASSERT_TRUE(visible);
    ASSERT_TRUE(*visible);
    ASSERT_TRUE((*transaction)->commit());

    public_cards = (*data_source)->getCards(GameContentScope::PUBLIC_ONLY);
    public_collection = (*data_source)->getCollection(
        1, GameContentScope::PUBLIC_ONLY);
    pool = (*data_source)->getPoolCards();
    ASSERT_TRUE(public_cards);
    ASSERT_TRUE(public_collection);
    ASSERT_TRUE(pool);
    EXPECT_EQ(public_cards->size(), 2);
    EXPECT_EQ(public_collection->size(), 2);
    EXPECT_EQ(pool->size(), 2);
}

/// Verify dynamic fields, numbering, values, and memberships persist.
TEST(DataSourceSQLiteTest, PersistsDynamicGameCards)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source);

    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->insertGame(
        {"test", "Test Game", "Description",
         GameVisibility::PUBLIC, 1}));
    auto hp_id = (*transaction)->insertGameField(
        {0, "test", "hp", "HP", GameFieldType::INTEGER, 0, {}}, {});
    auto note_id = (*transaction)->insertGameField(
        {0, "test", "note", "Note", GameFieldType::STRING, 1, {}}, {});
    auto route_id = (*transaction)->insertGameField(
        {0, "test", "route", "Route", GameFieldType::CHOICE, 2, {}},
        {{"Forest", 0}, {"Coast", 1}});
    ASSERT_TRUE(hp_id);
    ASSERT_TRUE(note_id);
    ASSERT_TRUE(route_id);
    auto series_id = (*transaction)->insertSeries(
        {0, "test", "Core set", "The first set."});
    ASSERT_TRUE(series_id);
    auto number = (*transaction)->allocateGameNumber("test");
    ASSERT_TRUE(number);
    Card card = {
        0, {"test", *number}, "Dynamic card", std::nullopt,
        std::nullopt, 2, "avif", std::nullopt, "avif", 1, 1};
    auto card_id = (*transaction)->insertCard(
        card,
        {{*hp_id, GameFieldType::INTEGER, std::int64_t{60}},
         {*note_id, GameFieldType::STRING, std::string("Near home")},
         {*route_id, GameFieldType::CHOICE, std::string("Forest")}},
        {*series_id});
    ASSERT_TRUE(card_id);
    ASSERT_TRUE((*transaction)->commit());

    auto stored = (*data_source)->getCard(
        {"test", 1}, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(stored);
    ASSERT_TRUE(*stored);
    auto values = (*data_source)->getCardFieldValues(
        *card_id, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(values);
    ASSERT_TRUE(*values);
    ASSERT_EQ((**values).definition.fields.size(), 3);
    ASSERT_EQ((**values).values.size(), 3);
    EXPECT_EQ(std::get<std::int64_t>((**values).values[0].value), 60);
    EXPECT_EQ(std::get<std::string>((**values).values[1].value), "Near home");
    EXPECT_EQ(std::get<std::string>((**values).values[2].value), "Forest");

    Card updated = **stored;
    updated.revision = 2;
    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->updateCard(
        updated,
        {{*hp_id, GameFieldType::INTEGER, std::int64_t{70}}},
        {}));
    ASSERT_TRUE((*transaction)->commit());
    values = (*data_source)->getCardFieldValues(
        *card_id, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(values);
    ASSERT_TRUE(*values);
    ASSERT_EQ((**values).values.size(), 1);
    EXPECT_EQ(std::get<std::int64_t>((**values).values[0].value), 70);

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    auto later_id = (*transaction)->insertGameField(
        {0, "test", "later", "Later", GameFieldType::STRING, 3, {}},
        {});
    ASSERT_TRUE(later_id);
    ASSERT_TRUE((*transaction)->commit());
    values = (*data_source)->getCardFieldValues(
        *card_id, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(values);
    ASSERT_TRUE(*values);
    EXPECT_EQ((**values).definition.fields.size(), 4);
    EXPECT_EQ((**values).values.size(), 1);
}

/// Verify destroying an uncommitted transaction rolls card insertion back.
TEST(DataSourceSQLiteTest, RollsBackLooseCard)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source);
    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->insertCard(
        makeLooseCard(91, "Temporary card"), {}, {}));

    transaction->reset();

    auto cards = (*data_source)->getCards(
        GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(cards);
    EXPECT_TRUE(cards->empty());
}

/// Verify game sequence numbers increase and survive committed transactions.
TEST(DataSourceSQLiteTest, AllocatesGameNumbers)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source);
    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->insertGame(
        {"gh", "Going Home", "", GameVisibility::PUBLIC, 1}));
    auto first = (*transaction)->allocateGameNumber("gh");
    auto second = (*transaction)->allocateGameNumber("gh");
    ASSERT_TRUE(first);
    ASSERT_TRUE(second);
    EXPECT_EQ(*first, 1);
    EXPECT_EQ(*second, 2);
    ASSERT_TRUE((*transaction)->commit());

    auto next_transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(next_transaction);
    auto third = (*next_transaction)->allocateGameNumber("gh");
    ASSERT_TRUE(third);
    EXPECT_EQ(*third, 3);
}

/// Verify the loose-card unique index rejects a repeated number.
TEST(DataSourceSQLiteTest, RejectsDuplicateLooseNumber)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source);
    auto first_transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(first_transaction);
    ASSERT_TRUE((*first_transaction)->insertCard(
        makeLooseCard(8, "First"), {}, {}));
    ASSERT_TRUE((*first_transaction)->commit());

    auto second_transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(second_transaction);
    auto exists = (*second_transaction)->looseNumberExists(8);
    ASSERT_TRUE(exists);
    EXPECT_TRUE(*exists);
    EXPECT_FALSE((*second_transaction)->insertCard(
        makeLooseCard(8, "Duplicate"), {}, {}));
}

/// Verify administrator metadata is immutable and exactly one row is kept.
TEST(DataSourceSQLiteTest, ReconcilesImmutableAdministrator)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(
        database.path(), "Admin@Example.com", "admin@example.com",
        100, 0);
    ASSERT_TRUE(data_source) << data_source.error().msg();

    auto administrator = (*data_source)->getUserByEmailKey(
        "admin@example.com");
    ASSERT_TRUE(administrator);
    ASSERT_TRUE(*administrator);
    EXPECT_EQ((**administrator).email, "Admin@Example.com");
    EXPECT_EQ((**administrator).role, UserRole::ADMINISTRATOR);
    EXPECT_EQ((**administrator).stored_pulls, 1);

    auto same = (*data_source)->reconcileAdministrator(
        "admin@example.com", "admin@example.com", 200, 1);
    ASSERT_TRUE(same);
    EXPECT_EQ(same->id, (**administrator).id);
    EXPECT_FALSE((*data_source)->reconcileAdministrator(
        "other@example.com", "other@example.com", 200, 1));

    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    User second_administrator = {
        0,
        "second@example.com",
        "second@example.com",
        std::nullopt,
        UserRole::ADMINISTRATOR,
        1,
        0,
        100};
    EXPECT_FALSE((*transaction)->insertUser(second_administrator));
}

/// Verify challenges, sessions, limits, users, and holdings are transactional.
TEST(DataSourceSQLiteTest, PersistsMvpAccountAndCollectionState)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source) << data_source.error().msg();
    auto administrator = (*data_source)->getUserByEmailKey(
        "admin@example.com");
    ASSERT_TRUE(administrator);
    ASSERT_TRUE(*administrator);

    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    User player = {
        0,
        "Player@Example.com",
        "player@example.com",
        std::nullopt,
        UserRole::PLAYER,
        1,
        0,
        10};
    auto player_id = (*transaction)->insertUser(player);
    ASSERT_TRUE(player_id) << player_id.error().msg();
    ASSERT_TRUE((*transaction)->commit());

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->updateUsername(
        *player_id, "Player", "player"));
    ASSERT_TRUE((*transaction)->promoteUser(*player_id));
    auto reservation = (*transaction)->reserveAuthenticationEmail(
        "player@example.com", 100, true, 0, 1);
    ASSERT_TRUE(reservation) << reservation.error().msg();
    EXPECT_EQ(
        reservation->status,
        AuthenticationReservationStatus::RESERVED);
    ASSERT_TRUE((*transaction)->commit());

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    reservation = (*transaction)->reserveAuthenticationEmail(
        "player@example.com", 130, true, 0, 1);
    ASSERT_TRUE(reservation);
    EXPECT_EQ(
        reservation->status,
        AuthenticationReservationStatus::EMAIL_LIMITED);
    EXPECT_EQ(reservation->retry_after, 30);
    ASSERT_TRUE((*transaction)->commit());

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    reservation = (*transaction)->reserveAuthenticationEmail(
        "new@example.com", 161, true, 0, 1);
    ASSERT_TRUE(reservation);
    EXPECT_EQ(
        reservation->status,
        AuthenticationReservationStatus::GLOBAL_LIMITED);
    ASSERT_TRUE((*transaction)->commit());

    const TokenHash challenge_hash(32, 7);
    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    reservation = (*transaction)->reserveAuthenticationEmail(
        "new@example.com", 161, false, 0, 1);
    ASSERT_TRUE(reservation);
    EXPECT_EQ(
        reservation->status,
        AuthenticationReservationStatus::RESERVED);
    auto challenge_id = (*transaction)->insertAuthenticationChallenge(
        "new@example.com", "new@example.com", challenge_hash, 161, 761);
    ASSERT_TRUE(challenge_id) << challenge_id.error().msg();
    ASSERT_TRUE((*transaction)->commit());
    auto challenge = (*data_source)->getAuthenticationChallenge(
        challenge_hash, 200);
    ASSERT_TRUE(challenge);
    EXPECT_FALSE(*challenge);

    const TokenHash session_hash(32, 9);
    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->markAuthenticationChallengeDelivered(
        *challenge_id, 170));
    auto consumed = (*transaction)->consumeAuthenticationChallenge(
        challenge_hash, 200);
    ASSERT_TRUE(consumed);
    ASSERT_TRUE(*consumed);
    auto session_id = (*transaction)->insertSession(
        *player_id, session_hash, std::string(64, 'c'), 200, 400);
    ASSERT_TRUE(session_id) << session_id.error().msg();
    ASSERT_TRUE((*transaction)->commit());

    auto session = (*data_source)->getSession(session_hash, 300);
    ASSERT_TRUE(session);
    ASSERT_TRUE(*session);
    EXPECT_EQ((**session).user.id, *player_id);
    EXPECT_EQ((**session).user.role, UserRole::CREATOR);
    EXPECT_EQ((**session).csrf_token, std::string(64, 'c'));
    auto expired = (*data_source)->getSession(session_hash, 400);
    ASSERT_TRUE(expired);
    EXPECT_FALSE(*expired);

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    Card card = makeLooseCard(123, "Collectible");
    card.creator_user_id = (**administrator).id;
    auto card_id = (*transaction)->insertCard(card, {}, {});
    ASSERT_TRUE(card_id) << card_id.error().msg();
    auto quantity = (*transaction)->incrementHolding(*player_id, *card_id);
    ASSERT_TRUE(quantity);
    EXPECT_EQ(*quantity, 1);
    quantity = (*transaction)->incrementHolding(*player_id, *card_id);
    ASSERT_TRUE(quantity);
    EXPECT_EQ(*quantity, 2);
    ASSERT_TRUE((*transaction)->commit());

    auto collection = (*data_source)->getCollection(
        *player_id, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(collection);
    ASSERT_EQ(collection->size(), 1);
    EXPECT_EQ(collection->front().quantity, 2);
    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->deleteCard(*card_id));
    ASSERT_TRUE((*transaction)->commit());
    collection = (*data_source)->getCollection(
        *player_id, GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(collection);
    EXPECT_TRUE(collection->empty());
}

/// Verify unreleased prototype databases fail with actionable guidance.
TEST(DataSourceSQLiteTest, RejectsObsoletePrototypeSchema)
{
    TemporaryDatabase database;
    auto connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    ASSERT_TRUE((*connection)->execute(
        "CREATE TABLE cards(id INTEGER PRIMARY KEY);"));
    ASSERT_TRUE((*connection)->execute("PRAGMA user_version = 1;"));
    connection->reset();

    auto data_source = DataSourceSQLite::fromFile(database.path());
    ASSERT_TRUE(data_source);
    auto version = (*data_source)->getSchemaVersion();
    ASSERT_FALSE(version);
    EXPECT_NE(version.error().msg().find("delete and recreate"),
              std::string::npos);
}

TEST(DataSourceSQLiteTest, RejectsVersionOneWithoutVisibility)
{
    TemporaryDatabase database;
    auto connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    ASSERT_TRUE((*connection)->execute(
        "CREATE TABLE users(id INTEGER PRIMARY KEY);"));
    ASSERT_TRUE((*connection)->execute(
        "CREATE TABLE games(short_name TEXT PRIMARY KEY);"));
    ASSERT_TRUE((*connection)->execute("PRAGMA user_version = 1;"));
    connection->reset();

    auto data_source = DataSourceSQLite::fromFile(database.path());
    ASSERT_TRUE(data_source);
    auto version = (*data_source)->getSchemaVersion();
    ASSERT_FALSE(version);
    EXPECT_NE(version.error().msg().find("delete and recreate"),
              std::string::npos);

    data_source->reset();
    connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    auto columns = (*connection)->evalToValue<std::int64_t>(
        "SELECT COUNT(*) FROM pragma_table_info('games');");
    ASSERT_TRUE(columns);
    EXPECT_EQ(*columns, 1);
}

TEST(DataSourceSQLiteTest, RejectsInvalidGameVisibility)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source) << data_source.error().msg();
    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);

    EXPECT_FALSE((*transaction)->insertGame({
        "invalid", "Invalid", "",
        static_cast<GameVisibility>(2), 1}));
}

/// Verify fresh initialization and reopening do not create any games.
TEST(DataSourceSQLiteTest, StartsAndReopensWithoutGames)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source) << data_source.error().msg();

    auto games = (*data_source)->getGames(
        GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(games);
    EXPECT_TRUE(games->empty());
    data_source->reset();

    data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source) << data_source.error().msg();
    games = (*data_source)->getGames(
        GameContentScope::INCLUDE_INTERNAL);
    ASSERT_TRUE(games);
    EXPECT_TRUE(games->empty());
}

/// Verify failed initialization rolls back newly attempted schema objects.
TEST(DataSourceSQLiteTest, RollsBackFailedInitialization)
{
    TemporaryDatabase database;
    auto connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    ASSERT_TRUE((*connection)->execute(
        "CREATE TABLE games(existing_marker TEXT);"));
    connection->reset();

    auto data_source = DataSourceSQLite::fromFile(database.path());
    ASSERT_TRUE(data_source);
    EXPECT_FALSE((*data_source)->migrateToLatest());
    data_source->reset();

    connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    auto users = (*connection)->evalToValue<int>(
        "SELECT EXISTS(SELECT 1 FROM sqlite_schema "
        "WHERE type = 'table' AND name = 'users');");
    ASSERT_TRUE(users);
    EXPECT_EQ(*users, 0);
    auto marker = (*connection)->evalToValue<int>(
        "SELECT EXISTS(SELECT 1 FROM sqlite_schema "
        "WHERE type = 'table' AND name = 'games');");
    ASSERT_TRUE(marker);
    EXPECT_EQ(*marker, 1);
}

/// Verify future schema versions are rejected without modifying their data.
TEST(DataSourceSQLiteTest, RejectsFutureSchemaWithoutModification)
{
    TemporaryDatabase database;
    auto connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    ASSERT_TRUE((*connection)->execute(
        "CREATE TABLE future_marker(value TEXT);"));
    ASSERT_TRUE((*connection)->execute(
        "INSERT INTO future_marker VALUES ('kept');"));
    ASSERT_TRUE((*connection)->execute("PRAGMA user_version = 2;"));
    connection->reset();

    auto data_source = DataSourceSQLite::fromFile(database.path());
    ASSERT_TRUE(data_source);
    EXPECT_FALSE((*data_source)->migrateToLatest());
    data_source->reset();

    connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    auto value = (*connection)->evalToValue<std::string>(
        "SELECT value FROM future_marker;");
    ASSERT_TRUE(value);
    EXPECT_EQ(*value, "kept");
    auto version = (*connection)->evalToValue<std::int64_t>(
        "PRAGMA user_version;");
    ASSERT_TRUE(version);
    EXPECT_EQ(*version, 2);
}

/// Verify startup rejects a version-1 game missing its number sequence.
TEST(DataSourceSQLiteTest, RejectsMissingGameSequence)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source) << data_source.error().msg();
    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->insertGame(
        {"test", "Test Game", "", GameVisibility::PUBLIC, 1}));
    ASSERT_TRUE((*transaction)->commit());
    data_source->reset();

    auto connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    ASSERT_TRUE((*connection)->execute(
        "DELETE FROM game_sequences WHERE game_short_name = 'test';"));
    connection->reset();

    auto reopened = prepareDataSource(database.path());
    ASSERT_FALSE(reopened);
    EXPECT_NE(reopened.error().msg().find("missing its card-number sequence"),
              std::string::npos);
}

/// Verify database constraints reject cross-game and mistyped field values.
TEST(DataSourceSQLiteTest, EnforcesDynamicFieldInvariants)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source) << data_source.error().msg();
    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->insertGame(
        {"one", "One", "", GameVisibility::PUBLIC, 1}));
    ASSERT_TRUE((*transaction)->insertGame(
        {"two", "Two", "", GameVisibility::PUBLIC, 1}));
    auto integer_id = (*transaction)->insertGameField(
        {0, "one", "score", "Score", GameFieldType::INTEGER, 0, {}},
        {});
    auto first_choice_id = (*transaction)->insertGameField(
        {0, "one", "route", "Route", GameFieldType::CHOICE, 1, {}},
        {{"Shared", 0}});
    auto second_choice_id = (*transaction)->insertGameField(
        {0, "one", "color", "Color", GameFieldType::CHOICE, 2, {}},
        {{"Other", 0}});
    ASSERT_TRUE(integer_id);
    ASSERT_TRUE(first_choice_id);
    ASSERT_TRUE(second_choice_id);
    ASSERT_TRUE((*transaction)->commit());

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    auto one_number = (*transaction)->allocateGameNumber("one");
    ASSERT_TRUE(one_number);
    Card one_card = makeLooseCard(*one_number, "One card");
    one_card.identity.game_short_name = "one";
    EXPECT_FALSE((*transaction)->insertCard(
        one_card,
        {{*integer_id, GameFieldType::STRING, std::string("5")}},
        {}));
    transaction->reset();

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    auto two_number = (*transaction)->allocateGameNumber("two");
    ASSERT_TRUE(two_number);
    Card two_card = makeLooseCard(*two_number, "Two card");
    two_card.identity.game_short_name = "two";
    EXPECT_FALSE((*transaction)->insertCard(
        two_card,
        {{*integer_id, GameFieldType::INTEGER, std::int64_t{5}}},
        {}));
    transaction->reset();

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    one_number = (*transaction)->allocateGameNumber("one");
    ASSERT_TRUE(one_number);
    one_card.identity.card_number = *one_number;
    EXPECT_FALSE((*transaction)->insertCard(
        one_card,
        {{*first_choice_id,
          GameFieldType::CHOICE,
          std::string("Other")}},
        {}));
}

/// Verify definition identities are immutable and card values cascade.
TEST(DataSourceSQLiteTest, PreservesDynamicIdentitiesAndCascadesValues)
{
    TemporaryDatabase database;
    auto data_source = prepareDataSource(database.path());
    ASSERT_TRUE(data_source) << data_source.error().msg();
    auto transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->insertGame(
        {"fixed", "Fixed", "", GameVisibility::PUBLIC, 1}));
    auto field_id = (*transaction)->insertGameField(
        {0, "fixed", "score", "Score", GameFieldType::INTEGER, 0, {}},
        {});
    auto choice_id = (*transaction)->insertGameField(
        {0, "fixed", "route", "Route", GameFieldType::CHOICE, 1, {}},
        {{"Home", 0}});
    ASSERT_TRUE(field_id);
    ASSERT_TRUE(choice_id);
    auto number = (*transaction)->allocateGameNumber("fixed");
    ASSERT_TRUE(number);
    Card card = makeLooseCard(*number, "Fixed card");
    card.identity.game_short_name = "fixed";
    auto card_id = (*transaction)->insertCard(
        card,
        {{*field_id, GameFieldType::INTEGER, std::int64_t{9}}},
        {});
    ASSERT_TRUE(card_id);
    ASSERT_TRUE((*transaction)->commit());

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    auto stored = (*transaction)->getCardForUpdate(*card_id);
    ASSERT_TRUE(stored);
    ASSERT_TRUE(*stored);
    (**stored).identity.card_number = 99;
    EXPECT_FALSE((*transaction)->updateCard(
        **stored,
        {{*field_id, GameFieldType::INTEGER, std::int64_t{10}}},
        {}));
    transaction->reset();

    transaction = (*data_source)->beginTransaction();
    ASSERT_TRUE(transaction);
    ASSERT_TRUE((*transaction)->deleteCard(*card_id));
    ASSERT_TRUE((*transaction)->commit());
    data_source->reset();

    auto connection = mw::SQLite::connectFile(database.path().string());
    ASSERT_TRUE(connection);
    ASSERT_TRUE((*connection)->execute("PRAGMA foreign_keys = ON;"));
    EXPECT_FALSE((*connection)->execute(
        "UPDATE games SET short_name = 'changed' "
        "WHERE short_name = 'fixed';"));
    EXPECT_FALSE((*connection)->execute(
        "UPDATE game_fields SET key = 'changed' "
        "WHERE id = 1;"));
    EXPECT_FALSE((*connection)->execute(
        "INSERT INTO game_field_choices(field_id, value, position) "
        "VALUES (1, 'Invalid', 0);"));
    EXPECT_FALSE((*connection)->execute(
        "UPDATE game_field_choices SET value = 'Changed' "
        "WHERE field_id = 2 AND value = 'Home';"));
    auto value_count = (*connection)->evalToValue<std::int64_t>(
        "SELECT COUNT(*) FROM card_field_values;");
    ASSERT_TRUE(value_count);
    EXPECT_EQ(*value_count, 0);
}