using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Jellyfin.Database.Implementations;
using Jellyfin.Database.Implementations.DbConfiguration;
using Jellyfin.Database.Implementations.Entities;
using Jellyfin.Database.Implementations.Locking;
using Jellyfin.Database.Providers.PostgreSQL;
using Jellyfin.Server.Migrations.Routines;
using Jellyfin.Server.ServerSetupApp;
using MediaBrowser.Controller.Entities;
using MediaBrowser.Controller.Library;
using MediaBrowser.Model.Entities;
using MediaBrowser.Model.Globalization;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Abstractions;
using Moq;
using Npgsql;
using Xunit;

namespace Jellyfin.Server.Tests.Migrations;

/// <summary>
/// Verifies that a PostgreSQL database created by an earlier build reaches the current schema, and that a fresh
/// install ends up with exactly the same schema.
/// </summary>
[Trait("Category", "RequiresDocker")]
public sealed class PostgreSqlUpgradeTests : IAsyncLifetime
{
    private const string BaselineMigrationId = "20260305010333_InitialPostgreSql";
    private const string SchemaUpgradeMigrationId = "20260306000000_UpgradeToServer12Schema";
    private const string UsernameIndexMigrationId = "20260524120336_AddUniqueNormalizedUsernameIndex";

    private const string PlaceholderItemId = "00000000-0000-0000-0000-000000000001";
    private const string OwnerItemId = "11111111-1111-1111-1111-111111111111";
    private const string OwnedItemId = "22222222-2222-2222-2222-222222222222";
    private const string UnparseableOwnerItemId = "33333333-3333-3333-3333-333333333333";
    private const string DanglingOwnerItemId = "44444444-4444-4444-4444-444444444444";
    private const string MalformedOwnerItemId = "55555555-5555-5555-5555-555555555555";
    private const string PlaceholderOwnedItemId = "66666666-6666-6666-6666-666666666666";
    private const string UnratedItemId = "77777777-7777-7777-7777-777777777777";
    private const string EmptyRatingItemId = "88888888-8888-8888-8888-888888888888";
    private const string RatedItemId = "99999999-9999-9999-9999-999999999999";
    private const string AliceId = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa";
    private const string BobId = "bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb";

    // Thirty-two hexadecimal digits with hyphens in the wrong places: a digit count alone accepts it, the uuid cast
    // does not.
    private const string MalformedGuid = "1111111-11111-1111-1111-111111111111";

    private PostgreSqlTestServer _server = null!;

    public async ValueTask InitializeAsync()
    {
        _server = await PostgreSqlTestServer.StartAsync().ConfigureAwait(false);
    }

    public async ValueTask DisposeAsync()
    {
        await _server.DisposeAsync().ConfigureAwait(false);
    }

    [Fact]
    public async Task FreshInstall_AppliesTheWholeChain()
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var connectionString = await _server.CreateDatabaseAsync("fresh_install", cancellationToken);

        await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
        await MigrateAsync(dataSource, null, cancellationToken);

        Assert.Equal(
            new[] { BaselineMigrationId, SchemaUpgradeMigrationId, UsernameIndexMigrationId },
            await AppliedMigrationsAsync(dataSource, cancellationToken));
        await AssertServer12SchemaAsync(dataSource, cancellationToken);
    }

    [Fact]
    public async Task LegacyDatabase_UpgradesToServer12Schema()
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var connectionString = await _server.CreateDatabaseAsync("legacy_upgrade", cancellationToken);

        await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
        await CreateLegacyDatabaseAsync(dataSource, cancellationToken);

        // The database knows only the migration the previous build shipped, and the two that carry it forward are the
        // ones the startup sequence has to pick up.
        Assert.Equal(new[] { BaselineMigrationId }, await AppliedMigrationsAsync(dataSource, cancellationToken));
        Assert.Equal(
            new[] { SchemaUpgradeMigrationId, UsernameIndexMigrationId },
            await PendingMigrationsAsync(dataSource, cancellationToken));

        await RunUpgradeInStartupOrderAsync(dataSource, cancellationToken);

        Assert.Empty(await PendingMigrationsAsync(dataSource, cancellationToken));
        await AssertServer12SchemaAsync(dataSource, cancellationToken);

        // The code migration that the startup sequence runs between the two schema migrations has to have filled the
        // new column in, otherwise the unique index could not have been created.
        Assert.Equal("ALICE", await ScalarAsync<string>(dataSource, $"""SELECT "NormalizedUsername" FROM "Users" WHERE "Id" = '{AliceId}'""", cancellationToken));
        Assert.Equal("BOB", await ScalarAsync<string>(dataSource, $"""SELECT "NormalizedUsername" FROM "Users" WHERE "Id" = '{BobId}'""", cancellationToken));

        // Owner and version ids were text and are now uuid: parseable values survive, unparseable ones are cleared
        // and owners that point at nothing are repointed at the placeholder so the new foreign key holds.
        Assert.Equal(new Guid(OwnerItemId), await ScalarAsync<Guid>(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{OwnedItemId}'""", cancellationToken));
        Assert.Equal(new Guid(OwnerItemId), await ScalarAsync<Guid>(dataSource, $"""SELECT "PrimaryVersionId" FROM "BaseItems" WHERE "Id" = '{OwnedItemId}'""", cancellationToken));
        Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{UnparseableOwnerItemId}'""", cancellationToken));
        Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "PrimaryVersionId" FROM "BaseItems" WHERE "Id" = '{UnparseableOwnerItemId}'""", cancellationToken));
        Assert.Equal(new Guid(PlaceholderItemId), await ScalarAsync<Guid>(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{DanglingOwnerItemId}'""", cancellationToken));

        // ExtraIds and OriginalLanguage are unrelated columns, so the extra ids must not have been carried over.
        Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "OriginalLanguage" FROM "BaseItems" WHERE "Id" = '{UnparseableOwnerItemId}'""", cancellationToken));

        // UserId is no longer nullable, so the rows that never had an owner had to be removed.
        Assert.Equal(1L, await ScalarAsync<long>(dataSource, """SELECT count(*) FROM "Permissions" """, cancellationToken));
        Assert.Equal(1L, await ScalarAsync<long>(dataSource, """SELECT count(*) FROM "Preferences" """, cancellationToken));

        await AssertContextMatchesSchemaAsync(dataSource, cancellationToken);
    }

    [Fact]
    public async Task LegacyDatabaseAndFreshInstall_EndUpWithTheModelSchema()
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var modelConnectionString = await _server.CreateDatabaseAsync("schema_model", cancellationToken);
        var freshConnectionString = await _server.CreateDatabaseAsync("schema_fresh", cancellationToken);
        var upgradedConnectionString = await _server.CreateDatabaseAsync("schema_upgraded", cancellationToken);

        // The model built straight from the context is the reference: comparing the two migrated databases against
        // each other only proves they drifted together.
        await using var modelDataSource = new NpgsqlDataSourceBuilder(modelConnectionString).Build();
        await CreateFromModelAsync(modelDataSource, cancellationToken);

        await using var freshDataSource = new NpgsqlDataSourceBuilder(freshConnectionString).Build();
        await MigrateAsync(freshDataSource, null, cancellationToken);

        await using var upgradedDataSource = new NpgsqlDataSourceBuilder(upgradedConnectionString).Build();
        await CreateLegacyDatabaseAsync(upgradedDataSource, cancellationToken);
        await RunUpgradeInStartupOrderAsync(upgradedDataSource, cancellationToken);

        var modelColumns = await DescribeColumnsAsync(modelDataSource, cancellationToken);
        var modelIndexes = await DescribeIndexesAsync(modelDataSource, cancellationToken);
        var modelConstraints = await DescribeConstraintsAsync(modelDataSource, cancellationToken);

        Assert.Equal(modelColumns, await DescribeColumnsAsync(freshDataSource, cancellationToken));
        Assert.Equal(modelIndexes, await DescribeIndexesAsync(freshDataSource, cancellationToken));
        Assert.Equal(modelConstraints, await DescribeConstraintsAsync(freshDataSource, cancellationToken));

        Assert.Equal(modelColumns, await DescribeColumnsAsync(upgradedDataSource, cancellationToken));
        Assert.Equal(modelIndexes, await DescribeIndexesAsync(upgradedDataSource, cancellationToken));
        Assert.Equal(modelConstraints, await DescribeConstraintsAsync(upgradedDataSource, cancellationToken));
    }

    [Fact]
    public async Task LegacyDatabase_MalformedGuidIds_AreCleared()
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var connectionString = await _server.CreateDatabaseAsync("malformed_guid", cancellationToken);

        await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
        await CreateLegacyDatabaseAsync(dataSource, cancellationToken);
        await InsertLegacyItemAsync(dataSource, MalformedOwnerItemId, "Malformed", MalformedGuid, MalformedGuid, cancellationToken);

        await RunUpgradeInStartupOrderAsync(dataSource, cancellationToken);

        Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{MalformedOwnerItemId}'""", cancellationToken));
        Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "PrimaryVersionId" FROM "BaseItems" WHERE "Id" = '{MalformedOwnerItemId}'""", cancellationToken));
        await AssertServer12SchemaAsync(dataSource, cancellationToken);
    }

    [Theory]
    [InlineData(true)]
    [InlineData(false)]
    public async Task LegacyDatabase_UpgradesWhetherOrNotThePlaceholderItemExists(bool placeholderExists)
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var connectionString = await _server.CreateDatabaseAsync(placeholderExists ? "placeholder_present" : "placeholder_missing", cancellationToken);

        await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
        await CreateLegacyDatabaseAsync(dataSource, cancellationToken);
        if (!placeholderExists)
        {
            await ExecuteAsync(dataSource, $"""DELETE FROM "BaseItems" WHERE "Id" = '{PlaceholderItemId}'""", cancellationToken);
        }

        await RunUpgradeInStartupOrderAsync(dataSource, cancellationToken);

        // The dangling owner is repointed at the placeholder, so the foreign key only holds if the placeholder is there.
        Assert.Equal(1L, await ScalarAsync<long>(dataSource, $"""SELECT count(*) FROM "BaseItems" WHERE "Id" = '{PlaceholderItemId}'""", cancellationToken));
        Assert.Equal(new Guid(PlaceholderItemId), await ScalarAsync<Guid>(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{DanglingOwnerItemId}'""", cancellationToken));
        await AssertServer12SchemaAsync(dataSource, cancellationToken);
    }

    [Fact]
    public async Task LegacyDatabase_ItemOwnedByThePlaceholder_SurvivesCleanupOrphanedExtras()
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var connectionString = await _server.CreateDatabaseAsync("placeholder_owner", cancellationToken);

        await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
        await CreateLegacyDatabaseAsync(dataSource, cancellationToken);
        await InsertLegacyItemAsync(dataSource, PlaceholderOwnedItemId, "Owned by the placeholder", PlaceholderItemId, null, cancellationToken);

        await RunUpgradeInStartupOrderAsync(dataSource, cancellationToken);

        // The SQLite chain clears an owner that already is the placeholder, so the item is not mistaken for an orphan.
        Assert.Null(await NullableScalarAsync(dataSource, $"""SELECT "OwnerId" FROM "BaseItems" WHERE "Id" = '{PlaceholderOwnedItemId}'""", cancellationToken));

        var deletedItemIds = new List<Guid>();
        var libraryManager = new Mock<ILibraryManager>();
        libraryManager
            .Setup(manager => manager.DeleteItemsUnsafeFast(It.IsAny<IReadOnlyCollection<BaseItem>>(), It.IsAny<bool>()))
            .Callback<IReadOnlyCollection<BaseItem>, bool>((items, _) => deletedItemIds.AddRange(items.Select(item => item.Id)));

        await new CleanupOrphanedExtras(
            Mock.Of<IStartupLogger<CleanupOrphanedExtras>>(),
            new SingleContextFactory(dataSource),
            libraryManager.Object).PerformAsync(cancellationToken);

        Assert.Contains(new Guid(DanglingOwnerItemId), deletedItemIds);
        Assert.DoesNotContain(new Guid(PlaceholderOwnedItemId), deletedItemIds);
        Assert.Equal(1L, await ScalarAsync<long>(dataSource, $"""SELECT count(*) FROM "BaseItems" WHERE "Id" = '{PlaceholderOwnedItemId}'""", cancellationToken));
    }

    [Fact]
    public async Task MigrateRatingLevels_RecalculatesEveryRating()
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var connectionString = await _server.CreateDatabaseAsync("rating_levels", cancellationToken);

        await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
        await MigrateAsync(dataSource, null, cancellationToken);

        // A NULL and an empty rating are what make the migration issue an update while it is still
        // walking the distinct rating list, which a connection that cannot multiplex commands rejects.
        await InsertRatedItemAsync(dataSource, UnratedItemId, null, cancellationToken);
        await InsertRatedItemAsync(dataSource, EmptyRatingItemId, string.Empty, cancellationToken);
        await InsertRatedItemAsync(dataSource, RatedItemId, "PG-13", cancellationToken);

        var localizationManager = new Mock<ILocalizationManager>();
        localizationManager
            .Setup(manager => manager.GetRatingScore("PG-13", null))
            .Returns(new ParentalRatingScore(13, 2));

        new MigrateRatingLevels(
            new SingleContextFactory(dataSource),
            new NullStartupLogger<MigrateRatingLevels>(),
            localizationManager.Object).Perform();

        Assert.Null(await InheritedRatingAsync(dataSource, UnratedItemId, "InheritedParentalRatingValue", cancellationToken));
        Assert.Null(await InheritedRatingAsync(dataSource, UnratedItemId, "InheritedParentalRatingSubValue", cancellationToken));
        Assert.Null(await InheritedRatingAsync(dataSource, EmptyRatingItemId, "InheritedParentalRatingValue", cancellationToken));
        Assert.Null(await InheritedRatingAsync(dataSource, EmptyRatingItemId, "InheritedParentalRatingSubValue", cancellationToken));
        Assert.Equal(13, await InheritedRatingAsync(dataSource, RatedItemId, "InheritedParentalRatingValue", cancellationToken));
        Assert.Equal(2, await InheritedRatingAsync(dataSource, RatedItemId, "InheritedParentalRatingSubValue", cancellationToken));
    }

    [Fact]
    public async Task UniqueUsernameIndex_CannotBeAppliedBeforeTheUsernameCodeMigration()
    {
        var cancellationToken = TestContext.Current.CancellationToken;
        var connectionString = await _server.CreateDatabaseAsync("username_index_order", cancellationToken);

        await using var dataSource = new NpgsqlDataSourceBuilder(connectionString).Build();
        await CreateLegacyDatabaseAsync(dataSource, cancellationToken);

        // Every existing user starts out with the same empty normalized username, so the index can only be built once
        // UpdateNormalizedUsername has filled the column in. That is why the index is a migration of its own.
        await Assert.ThrowsAsync<PostgresException>(() => MigrateAsync(dataSource, null, cancellationToken));
    }

    private static JellyfinDbContext CreateContext(NpgsqlDataSource dataSource)
    {
        var optionsBuilder = new DbContextOptionsBuilder<JellyfinDbContext>();
        var provider = new PostgreSqlDatabaseProvider(dataSource);
        provider.Initialise(optionsBuilder, new DatabaseConfigurationOptions { DatabaseType = "PostgreSQL" });
        return new JellyfinDbContext(
            optionsBuilder.Options,
            NullLogger<JellyfinDbContext>.Instance,
            provider,
            new NoLockBehavior(NullLogger<NoLockBehavior>.Instance));
    }

    private static async Task MigrateAsync(NpgsqlDataSource dataSource, string? targetMigration, CancellationToken cancellationToken)
    {
        var context = CreateContext(dataSource);
        await using (context.ConfigureAwait(false))
        {
            await context.GetService<IMigrator>().MigrateAsync(targetMigration, cancellationToken).ConfigureAwait(false);
        }
    }

    /// <summary>
    /// Creates the schema straight from the current model, without going through any migration.
    /// </summary>
    private static async Task CreateFromModelAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
        var context = CreateContext(dataSource);
        await using (context.ConfigureAwait(false))
        {
            await context.Database.EnsureCreatedAsync(cancellationToken).ConfigureAwait(false);
        }
    }

    private static async Task<IReadOnlyList<string>> AppliedMigrationsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
        var context = CreateContext(dataSource);
        await using (context.ConfigureAwait(false))
        {
            return [.. await context.Database.GetAppliedMigrationsAsync(cancellationToken).ConfigureAwait(false)];
        }
    }

    private static async Task<IReadOnlyList<string>> PendingMigrationsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
        var context = CreateContext(dataSource);
        await using (context.ConfigureAwait(false))
        {
            return [.. await context.Database.GetPendingMigrationsAsync(cancellationToken).ConfigureAwait(false)];
        }
    }

    private static async Task ExecuteAsync(NpgsqlDataSource dataSource, string sql, CancellationToken cancellationToken)
    {
        await using var command = dataSource.CreateCommand(sql);
        await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
    }

    private static async Task<T> ScalarAsync<T>(NpgsqlDataSource dataSource, string sql, CancellationToken cancellationToken)
    {
        var value = await NullableScalarAsync(dataSource, sql, cancellationToken).ConfigureAwait(false);
        Assert.NotNull(value);
        return (T)value;
    }

    private static async Task<object?> NullableScalarAsync(NpgsqlDataSource dataSource, string sql, CancellationToken cancellationToken)
    {
        await using var command = dataSource.CreateCommand(sql);
        var value = await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false);
        return value is null or DBNull ? null : value;
    }

    private static async Task<IReadOnlyList<string>> QueryLinesAsync(NpgsqlDataSource dataSource, string sql, CancellationToken cancellationToken)
    {
        var lines = new List<string>();
        await using var command = dataSource.CreateCommand(sql);
        await using var reader = await command.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false);
        while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
        {
            var values = new string[reader.FieldCount];
            for (var i = 0; i < reader.FieldCount; i++)
            {
                values[i] = await reader.IsDBNullAsync(i, cancellationToken).ConfigureAwait(false)
                    ? "<null>"
                    : Convert.ToString(reader.GetValue(i), CultureInfo.InvariantCulture) ?? "<null>";
            }

            lines.Add(string.Join('|', values));
        }

        return lines;
    }

    private static Task<IReadOnlyList<string>> DescribeColumnsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
        => QueryLinesAsync(
            dataSource,
            """
            SELECT table_name, column_name, data_type, is_nullable, coalesce(character_maximum_length, -1), coalesce(column_default, '')
            FROM information_schema.columns
            WHERE table_schema = 'public' AND table_name <> '__EFMigrationsHistory'
            ORDER BY table_name, column_name
            """,
            cancellationToken);

    private static Task<IReadOnlyList<string>> DescribeIndexesAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
        => QueryLinesAsync(
            dataSource,
            """
            SELECT tablename, indexname, indexdef
            FROM pg_indexes
            WHERE schemaname = 'public' AND tablename <> '__EFMigrationsHistory'
            ORDER BY tablename, indexname
            """,
            cancellationToken);

    private static Task<IReadOnlyList<string>> DescribeConstraintsAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
        => QueryLinesAsync(
            dataSource,
            """
            SELECT relation.relname, constraint_.conname, pg_get_constraintdef(constraint_.oid)
            FROM pg_constraint AS constraint_
            JOIN pg_class AS relation ON relation.oid = constraint_.conrelid
            JOIN pg_namespace AS namespace_ ON namespace_.oid = relation.relnamespace
            WHERE namespace_.nspname = 'public' AND relation.relname <> '__EFMigrationsHistory'
            ORDER BY relation.relname, constraint_.conname
            """,
            cancellationToken);

    /// <summary>
    /// Adds one more item to a legacy database, with the owner and version ids still held as text.
    /// </summary>
    private static Task InsertLegacyItemAsync(
        NpgsqlDataSource dataSource,
        string itemId,
        string name,
        string? ownerId,
        string? primaryVersionId,
        CancellationToken cancellationToken)
        => ExecuteAsync(
            dataSource,
            $"""
            INSERT INTO "BaseItems" ("Id", "Type", "IsMovie", "IsLocked", "IsSeries", "IsRepeat", "IsInMixedFolder",
                "IsFolder", "IsVirtualItem", "Name", "Path", "OwnerId", "PrimaryVersionId")
            VALUES ('{itemId}', 'MediaBrowser.Controller.Entities.Video', false, false, false, false, false, false, false,
                '{name}', '/media/{itemId}.mkv', {Literal(ownerId)}, {Literal(primaryVersionId)});
            """,
            cancellationToken);

    private static string Literal(string? value) => value is null ? "NULL" : $"'{value}'";

    /// <summary>
    /// Adds an item carrying a rating and an already populated inherited rating, so that clearing it is visible.
    /// </summary>
    private static Task InsertRatedItemAsync(
        NpgsqlDataSource dataSource,
        string itemId,
        string? officialRating,
        CancellationToken cancellationToken)
        => ExecuteAsync(
            dataSource,
            $"""
            INSERT INTO "BaseItems" ("Id", "Type", "IsMovie", "IsLocked", "IsSeries", "IsRepeat", "IsInMixedFolder",
                "IsFolder", "IsVirtualItem", "Name", "Path", "OfficialRating",
                "InheritedParentalRatingValue", "InheritedParentalRatingSubValue")
            VALUES ('{itemId}', 'MediaBrowser.Controller.Entities.Movies.Movie', true, false, false, false, false, false, false,
                'Rated {itemId}', '/media/{itemId}.mkv', {Literal(officialRating)}, 99, 98);
            """,
            cancellationToken);

    private static async Task<int?> InheritedRatingAsync(NpgsqlDataSource dataSource, string itemId, string column, CancellationToken cancellationToken)
    {
        var value = await NullableScalarAsync(
            dataSource,
            $"""SELECT "{column}" FROM "BaseItems" WHERE "Id" = '{itemId}'""",
            cancellationToken).ConfigureAwait(false);
        return (int?)value;
    }

    /// <summary>
    /// Builds a database that looks like one written by the previous build: only the baseline migration applied, and
    /// data that only the old column types could hold.
    /// </summary>
    private static async Task CreateLegacyDatabaseAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
        await MigrateAsync(dataSource, BaselineMigrationId, cancellationToken).ConfigureAwait(false);

        await ExecuteAsync(
            dataSource,
            $"""
            INSERT INTO "Users" ("Id", "Username", "MustUpdatePassword", "AuthenticationProviderId", "PasswordResetProviderId",
                "InvalidLoginAttemptCount", "MaxActiveSessions", "SubtitleMode", "PlayDefaultAudioTrack", "DisplayMissingEpisodes",
                "DisplayCollectionsView", "EnableLocalPassword", "HidePlayedInLatest", "RememberAudioSelections",
                "RememberSubtitleSelections", "EnableNextEpisodeAutoPlay", "EnableAutoLogin", "EnableUserPreferenceAccess",
                "InternalId", "SyncPlayAccess", "RowVersion")
            VALUES
                ('{AliceId}', 'alice', false, 'provider', 'provider', 0, 0, 0, true, false, true, false, false, true, true, true, false, true, 1, 0, 0),
                ('{BobId}', 'bob', false, 'provider', 'provider', 0, 0, 0, true, false, true, false, false, true, true, true, false, true, 2, 0, 0);

            INSERT INTO "BaseItems" ("Id", "Type", "IsMovie", "IsLocked", "IsSeries", "IsRepeat", "IsInMixedFolder",
                "IsFolder", "IsVirtualItem", "Name", "Path", "OwnerId", "PrimaryVersionId", "ExtraIds")
            VALUES
                ('{OwnerItemId}', 'MediaBrowser.Controller.Entities.Movies.Movie', true, false, false, false, false, false, false, 'Owner', '/media/owner.mkv', NULL, NULL, NULL),
                ('{OwnedItemId}', 'MediaBrowser.Controller.Entities.Video', false, false, false, false, false, false, false, 'Trailer', '/media/owner-trailer.mkv', '11111111111111111111111111111111', '11111111-1111-1111-1111-111111111111', NULL),
                ('{UnparseableOwnerItemId}', 'MediaBrowser.Controller.Entities.Video', false, false, false, false, false, false, false, 'Junk', '/media/junk.mkv', '', '00000000-0000-0000-0000-000000000000', 'aaaa|bbbb'),
                ('{DanglingOwnerItemId}', 'MediaBrowser.Controller.Entities.Video', false, false, false, false, false, false, false, 'Dangling', '/media/dangling.mkv', '99999999-9999-9999-9999-999999999999', NULL, NULL);

            INSERT INTO "Permissions" ("UserId", "Kind", "Value", "RowVersion")
            VALUES ('{AliceId}', 0, true, 0), (NULL, 0, true, 0);

            INSERT INTO "Preferences" ("UserId", "Kind", "Value", "RowVersion")
            VALUES ('{BobId}', 0, 'kept', 0), (NULL, 0, 'orphaned', 0);
            """,
            cancellationToken).ConfigureAwait(false);
    }

    /// <summary>
    /// Runs the migrations in the order the startup migration service picks them: schema and code migration ids are
    /// sorted into one list, which puts the username code migration between the two schema migrations.
    /// </summary>
    private static async Task RunUpgradeInStartupOrderAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
        await MigrateAsync(dataSource, SchemaUpgradeMigrationId, cancellationToken).ConfigureAwait(false);
        await new UpdateNormalizedUsername(new SingleContextFactory(dataSource)).PerformAsync(cancellationToken).ConfigureAwait(false);
        await MigrateAsync(dataSource, UsernameIndexMigrationId, cancellationToken).ConfigureAwait(false);
    }

    private static async Task AssertServer12SchemaAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
        Assert.Equal("character varying", await ColumnTypeAsync(dataSource, "Users", "NormalizedUsername", cancellationToken).ConfigureAwait(false));
        Assert.Equal("uuid", await ColumnTypeAsync(dataSource, "BaseItems", "OwnerId", cancellationToken).ConfigureAwait(false));
        Assert.Equal("uuid", await ColumnTypeAsync(dataSource, "BaseItems", "PrimaryVersionId", cancellationToken).ConfigureAwait(false));
        Assert.Equal("text", await ColumnTypeAsync(dataSource, "BaseItems", "OriginalLanguage", cancellationToken).ConfigureAwait(false));
        Assert.Equal("boolean", await ColumnTypeAsync(dataSource, "MediaStreamInfos", "IsOriginal", cancellationToken).ConfigureAwait(false));
        Assert.Null(await ColumnTypeAsync(dataSource, "BaseItems", "ExtraIds", cancellationToken).ConfigureAwait(false));
        Assert.Null(await ColumnTypeAsync(dataSource, "Permissions", "Permission_Permissions_Guid", cancellationToken).ConfigureAwait(false));
        Assert.Null(await ColumnTypeAsync(dataSource, "Preferences", "Preference_Preferences_Guid", cancellationToken).ConfigureAwait(false));

        Assert.Equal("NO", await ScalarAsync<string>(
            dataSource,
            """SELECT is_nullable FROM information_schema.columns WHERE table_name = 'Permissions' AND column_name = 'UserId'""",
            cancellationToken).ConfigureAwait(false));
        Assert.Equal("NO", await ScalarAsync<string>(
            dataSource,
            """SELECT is_nullable FROM information_schema.columns WHERE table_name = 'Preferences' AND column_name = 'UserId'""",
            cancellationToken).ConfigureAwait(false));

        Assert.True(await ScalarAsync<bool>(
            dataSource,
            """SELECT indisunique FROM pg_index WHERE indexrelid = 'public."IX_Users_NormalizedUsername"'::regclass""",
            cancellationToken).ConfigureAwait(false));

        // The model declares no database defaults, so none may be left over from filling the new columns in.
        Assert.Equal(0L, await ScalarAsync<long>(
            dataSource,
            """
            SELECT count(*) FROM information_schema.columns
            WHERE table_schema = 'public' AND column_default IS NOT NULL
              AND (table_name, column_name) IN (
                  ('Users', 'NormalizedUsername'), ('Permissions', 'UserId'),
                  ('Preferences', 'UserId'), ('MediaStreamInfos', 'IsOriginal'))
            """,
            cancellationToken).ConfigureAwait(false));

        // Playlists may hold the same child more than once, which the primary key has to allow for.
        Assert.Equal(
            "ParentId,SortOrder",
            await ScalarAsync<string>(
                dataSource,
                """
                SELECT string_agg(attribute.attname, ',' ORDER BY key.ordinality)
                FROM pg_constraint AS constraint_
                CROSS JOIN LATERAL unnest(constraint_.conkey) WITH ORDINALITY AS key(attnum, ordinality)
                JOIN pg_attribute AS attribute
                  ON attribute.attrelid = constraint_.conrelid AND attribute.attnum = key.attnum
                WHERE constraint_.conname = 'PK_LinkedChildren'
                """,
                cancellationToken).ConfigureAwait(false));

        Assert.Equal(1L, await ScalarAsync<long>(
            dataSource,
            """SELECT count(*) FROM pg_constraint WHERE conname = 'FK_BaseItems_BaseItems_OwnerId'""",
            cancellationToken).ConfigureAwait(false));
    }

    private static async Task<string?> ColumnTypeAsync(NpgsqlDataSource dataSource, string table, string column, CancellationToken cancellationToken)
    {
        var value = await NullableScalarAsync(
            dataSource,
            $"""SELECT data_type FROM information_schema.columns WHERE table_schema = 'public' AND table_name = '{table}' AND column_name = '{column}'""",
            cancellationToken).ConfigureAwait(false);
        return (string?)value;
    }

    /// <summary>
    /// Writes and reads an entity through the context to prove the migrated schema really matches the current model.
    /// </summary>
    private static async Task AssertContextMatchesSchemaAsync(NpgsqlDataSource dataSource, CancellationToken cancellationToken)
    {
        var context = CreateContext(dataSource);
        await using (context.ConfigureAwait(false))
        {
            Assert.False(context.Database.HasPendingModelChanges());

            var user = new User("carol", "provider", "provider");
            context.Users.Add(user);
            await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);

            var reloaded = await context.Users.AsNoTracking()
                .FirstAsync(u => u.Id.Equals(user.Id), cancellationToken)
                .ConfigureAwait(false);
            Assert.Equal("CAROL", reloaded.NormalizedUsername);
        }
    }

    /// <summary>
    /// Stands in for the startup logger. Moq cannot proxy a logger whose category is an internal migration.
    /// </summary>
    private sealed class NullStartupLogger<TCategory> : IStartupLogger<TCategory>
    {
        public StartupLogTopic? Topic => null;

        public IDisposable? BeginScope<TState>(TState state)
            where TState : notnull
            => NullLogger.Instance.BeginScope(state);

        public bool IsEnabled(LogLevel logLevel) => false;

        public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception? exception, Func<TState, Exception?, string> formatter)
        {
        }

        IStartupLogger IStartupLogger.BeginGroup(FormattableString logEntry) => this;

        IStartupLogger<TOther> IStartupLogger.BeginGroup<TOther>(FormattableString logEntry) => new NullStartupLogger<TOther>();

        IStartupLogger<TCategory> IStartupLogger<TCategory>.BeginGroup(FormattableString logEntry) => this;

        IStartupLogger IStartupLogger.With(Microsoft.Extensions.Logging.ILogger logger) => this;

        IStartupLogger<TOther> IStartupLogger.With<TOther>(Microsoft.Extensions.Logging.ILogger logger) => new NullStartupLogger<TOther>();

        IStartupLogger<TCategory> IStartupLogger<TCategory>.With(Microsoft.Extensions.Logging.ILogger logger) => this;
    }

    private sealed class SingleContextFactory : IDbContextFactory<JellyfinDbContext>
    {
        private readonly NpgsqlDataSource _dataSource;

        public SingleContextFactory(NpgsqlDataSource dataSource)
        {
            _dataSource = dataSource;
        }

        public JellyfinDbContext CreateDbContext() => CreateContext(_dataSource);
    }
}