namespace GmRelay.Bot.Tests.Web;

public sealed class PortfolioSessionDeletionSourceTests
{
    [Fact]
    public async Task SharedDeleteSessionHandler_ShouldLockSessionBeforeUnpublishingLinkedPortfolioCardAndDeletingSession()
    {
        var source = NormalizeSql(await ReadRepositoryFileAsync(
            "src/GmRelay.Shared/Features/Sessions/ListSessions/DeleteSessionHandler.cs"));

        const string sessionLock =
            "FROM sessions s WHERE s.id = @SessionId FOR UPDATE OF s";
        const string advisoryLock =
            "SELECT pg_advisory_xact_lock(20260530, 108)";
        const string unpublish =
            "UPDATE portfolio_games pg SET is_public = false, updated_at = now() FROM portfolio_game_sessions pgs WHERE pgs.portfolio_game_id = pg.id AND pgs.session_id = @SessionId AND pg.is_public = true";

        Assert.Contains(advisoryLock, source, StringComparison.Ordinal);
        Assert.Contains(sessionLock, source, StringComparison.Ordinal);
        Assert.Contains(unpublish, source, StringComparison.Ordinal);
        Assert.True(
            source.IndexOf(advisoryLock, StringComparison.Ordinal) <
            source.IndexOf(sessionLock, StringComparison.Ordinal),
            "The shared delete path must acquire the portfolio advisory lock before locking the session.");
        Assert.True(
            source.IndexOf(sessionLock, StringComparison.Ordinal) <
            source.IndexOf(unpublish, StringComparison.Ordinal),
            "The shared delete path must lock the session before locking a linked portfolio card.");
        Assert.True(
            source.IndexOf(unpublish, StringComparison.Ordinal) <
            source.IndexOf("DELETE FROM sessions WHERE id = @Id", StringComparison.Ordinal),
            "Linked public portfolio cards must be unpublished before deleting the session.");
    }

    [Fact]
    public async Task DiscordDeleteSessionHandler_ShouldLockGuildSessionBeforeUnpublishingLinkedPortfolioCardAndDeletingSession()
    {
        var source = NormalizeSql(await ReadRepositoryFileAsync(
            "src/GmRelay.DiscordBot/Features/Sessions/DiscordDeleteSessionHandler.cs"));

        const string sessionLock =
            "SELECT s.id FROM sessions s JOIN game_groups g ON g.id = s.group_id WHERE s.id = @SessionId AND g.platform = 'Discord' AND g.external_group_id = @GuildId FOR UPDATE OF s";
        const string advisoryLock =
            "SELECT pg_advisory_xact_lock(20260530, 108)";
        const string unpublish =
            "UPDATE portfolio_games pg SET is_public = false, updated_at = now() FROM portfolio_game_sessions pgs JOIN sessions s ON s.id = pgs.session_id JOIN game_groups g ON g.id = s.group_id WHERE pgs.portfolio_game_id = pg.id AND s.id = @SessionId AND g.platform = 'Discord' AND g.external_group_id = @GuildId AND pg.is_public = true";

        Assert.Contains(advisoryLock, source, StringComparison.Ordinal);
        Assert.Contains(sessionLock, source, StringComparison.Ordinal);
        Assert.Contains(unpublish, source, StringComparison.Ordinal);
        Assert.Contains("AND p.platform = 'Discord'", source, StringComparison.Ordinal);
        Assert.True(
            source.IndexOf(advisoryLock, StringComparison.Ordinal) <
            source.IndexOf(sessionLock, StringComparison.Ordinal),
            "The Discord delete path must acquire the portfolio advisory lock before locking the guild-scoped session.");
        Assert.True(
            source.IndexOf(sessionLock, StringComparison.Ordinal) <
            source.IndexOf(unpublish, StringComparison.Ordinal),
            "The Discord delete path must lock the guild-scoped session before locking a linked portfolio card.");
        Assert.True(
            source.IndexOf(unpublish, StringComparison.Ordinal) <
            source.IndexOf("DELETE FROM sessions s", StringComparison.Ordinal),
            "Discord cards must be unpublished before deleting the session.");
    }

    private static string NormalizeSql(string sql)
    {
        return string.Join(' ', sql.Split((char[]?)null, StringSplitOptions.RemoveEmptyEntries))
            .Replace("( ", "(", StringComparison.Ordinal)
            .Replace(" )", ")", StringComparison.Ordinal);
    }

    private static async Task<string> ReadRepositoryFileAsync(string relativePath)
    {
        var directory = new DirectoryInfo(AppContext.BaseDirectory);
        while (directory is not null)
        {
            var candidate = Path.Combine(directory.FullName, relativePath);
            if (File.Exists(candidate))
            {
                return await File.ReadAllTextAsync(candidate);
            }

            directory = directory.Parent;
        }

        throw new FileNotFoundException($"Could not locate repository file '{relativePath}'.");
    }
}