using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.Associations.OwnedNavigations;
using Microsoft.EntityFrameworkCore.TestUtilities;
using System;
using System.Threading.Tasks;
using Xunit;
using Xunit.Abstractions;

namespace EntityFrameworkCore.Jet.FunctionalTests.Query.Associations.OwnedNavigations;

public class OwnedNavigationsSetOperationsJetTest(
    OwnedNavigationsJetFixture fixture,
    ITestOutputHelper testOutputHelper)
    : OwnedNavigationsSetOperationsRelationalTestBase<OwnedNavigationsJetFixture>(fixture, testOutputHelper)
{
    public override async Task Over_associate_collections()
    {
        await base.Over_associate_collections();

        AssertSql(
            """
SELECT [r].[Id], [r].[Name], [o].[RootEntityId], [o0].[AssociateTypeRootEntityId], [o1].[AssociateTypeRootEntityId], [r2].[RootEntityId], [r3].[AssociateTypeRootEntityId], [r4].[AssociateTypeRootEntityId], [s].[RootEntityId], [s].[Id], [s].[Int], [s].[Ints], [s].[Name], [s].[String], [s].[AssociateTypeRootEntityId], [s].[AssociateTypeId], [s].[AssociateTypeRootEntityId0], [s].[AssociateTypeId0], [s].[AssociateTypeRootEntityId1], [s].[AssociateTypeId1], [s].[Id0], [s].[Int0], [s].[Ints0], [s].[Name0], [s].[String0], [s].[Id1], [s].[Int1], [s].[Ints1], [s].[Name1], [s].[String1], [s].[Id2], [s].[Int2], [s].[Ints2], [s].[Name2], [s].[String2], [o].[Id], [o].[Int], [o].[Ints], [o].[Name], [o].[String], [o2].[AssociateTypeRootEntityId], [o2].[Id], [o2].[Int], [o2].[Ints], [o2].[Name], [o2].[String], [o0].[Id], [o0].[Int], [o0].[Ints], [o0].[Name], [o0].[String], [o1].[Id], [o1].[Int], [o1].[Ints], [o1].[Name], [o1].[String], [r2].[Id], [r2].[Int], [r2].[Ints], [r2].[Name], [r2].[String], [r9].[AssociateTypeRootEntityId], [r9].[Id], [r9].[Int], [r9].[Ints], [r9].[Name], [r9].[String], [r3].[Id], [r3].[Int], [r3].[Ints], [r3].[Name], [r3].[String], [r4].[Id], [r4].[Int], [r4].[Ints], [r4].[Name], [r4].[String]
FROM [RootEntity] AS [r]
LEFT JOIN [OptionalRelated] AS [o] ON [r].[Id] = [o].[RootEntityId]
LEFT JOIN [OptionalRelated_OptionalNested] AS [o0] ON [o].[RootEntityId] = [o0].[AssociateTypeRootEntityId]
LEFT JOIN [OptionalRelated_RequiredNested] AS [o1] ON [o].[RootEntityId] = [o1].[AssociateTypeRootEntityId]
LEFT JOIN [RequiredRelated] AS [r2] ON [r].[Id] = [r2].[RootEntityId]
LEFT JOIN [RequiredRelated_OptionalNested] AS [r3] ON [r2].[RootEntityId] = [r3].[AssociateTypeRootEntityId]
LEFT JOIN [RequiredRelated_RequiredNested] AS [r4] ON [r2].[RootEntityId] = [r4].[AssociateTypeRootEntityId]
LEFT JOIN (
    SELECT [r5].[RootEntityId], [r5].[Id], [r5].[Int], [r5].[Ints], [r5].[Name], [r5].[String], [r6].[AssociateTypeRootEntityId], [r6].[AssociateTypeId], [r7].[AssociateTypeRootEntityId] AS [AssociateTypeRootEntityId0], [r7].[AssociateTypeId] AS [AssociateTypeId0], [r8].[AssociateTypeRootEntityId] AS [AssociateTypeRootEntityId1], [r8].[AssociateTypeId] AS [AssociateTypeId1], [r8].[Id] AS [Id0], [r8].[Int] AS [Int0], [r8].[Ints] AS [Ints0], [r8].[Name] AS [Name0], [r8].[String] AS [String0], [r6].[Id] AS [Id1], [r6].[Int] AS [Int1], [r6].[Ints] AS [Ints1], [r6].[Name] AS [Name1], [r6].[String] AS [String1], [r7].[Id] AS [Id2], [r7].[Int] AS [Int2], [r7].[Ints] AS [Ints2], [r7].[Name] AS [Name2], [r7].[String] AS [String2]
    FROM [RelatedCollection] AS [r5]
    LEFT JOIN [RelatedCollection_OptionalNested] AS [r6] ON [r5].[RootEntityId] = [r6].[AssociateTypeRootEntityId] AND [r5].[Id] = [r6].[AssociateTypeId]
    LEFT JOIN [RelatedCollection_RequiredNested] AS [r7] ON [r5].[RootEntityId] = [r7].[AssociateTypeRootEntityId] AND [r5].[Id] = [r7].[AssociateTypeId]
    LEFT JOIN [RelatedCollection_NestedCollection] AS [r8] ON [r5].[RootEntityId] = [r8].[AssociateTypeRootEntityId] AND [r5].[Id] = [r8].[AssociateTypeId]
) AS [s] ON [r].[Id] = [s].[RootEntityId]
LEFT JOIN [OptionalRelated_NestedCollection] AS [o2] ON [o].[RootEntityId] = [o2].[AssociateTypeRootEntityId]
LEFT JOIN [RequiredRelated_NestedCollection] AS [r9] ON [r2].[RootEntityId] = [r9].[AssociateTypeRootEntityId]
WHERE (
    SELECT COUNT(*)
    FROM (
        SELECT 1 AS empty
        FROM [RelatedCollection] AS [r0]
        WHERE [r].[Id] = [r0].[RootEntityId] AND [r0].[Int] = 8
        UNION ALL
        SELECT 1 AS empty
        FROM [RelatedCollection] AS [r1]
        WHERE [r].[Id] = [r1].[RootEntityId] AND [r1].[String] = N'foo'
    ) AS [u]) = 4
ORDER BY [r].[Id], [o].[RootEntityId], [o0].[AssociateTypeRootEntityId], [o1].[AssociateTypeRootEntityId], [r2].[RootEntityId], [r3].[AssociateTypeRootEntityId], [r4].[AssociateTypeRootEntityId], [s].[RootEntityId], [s].[Id], [s].[AssociateTypeRootEntityId], [s].[AssociateTypeId], [s].[AssociateTypeRootEntityId0], [s].[AssociateTypeId0], [s].[AssociateTypeRootEntityId1], [s].[AssociateTypeId1], [s].[Id0], [o2].[AssociateTypeRootEntityId], [o2].[Id], [r9].[AssociateTypeRootEntityId]
""");
    }

    public override Task Over_associate_collection_projected(QueryTrackingBehavior queryTrackingBehavior)
        => Assert.ThrowsAnyAsync<Exception>(() => base.Over_associate_collection_projected(queryTrackingBehavior));

    public override async Task Over_assocate_collection_Select_nested_with_aggregates_projected(QueryTrackingBehavior queryTrackingBehavior)
    {
        await base.Over_assocate_collection_Select_nested_with_aggregates_projected(queryTrackingBehavior);

        AssertSql(
            """
SELECT (
    SELECT COALESCE(SUM([s].[value]), 0)
    FROM (
        SELECT [r0].[RootEntityId], [r0].[Id]
        FROM [RelatedCollection] AS [r0]
        WHERE [r].[Id] = [r0].[RootEntityId] AND [r0].[Int] = 8
        UNION ALL
        SELECT [r1].[RootEntityId], [r1].[Id]
        FROM [RelatedCollection] AS [r1]
        WHERE [r].[Id] = [r1].[RootEntityId] AND [r1].[String] = N'foo'
    ) AS [u]
    OUTER APPLY (
        SELECT COALESCE(SUM([r2].[Int]), 0) AS [value]
        FROM [RelatedCollection_NestedCollection] AS [r2]
        WHERE [u].[RootEntityId] = [r2].[AssociateTypeRootEntityId] AND [u].[Id] = [r2].[AssociateTypeId]
    ) AS [s])
FROM [RootEntity] AS [r]
""");
    }

    public override async Task Over_nested_associate_collection()
    {
        await base.Over_nested_associate_collection();

        AssertSql(
            """
SELECT [r].[Id], [r].[Name], [r0].[RootEntityId], [o].[RootEntityId], [o0].[AssociateTypeRootEntityId], [o1].[AssociateTypeRootEntityId], [r3].[AssociateTypeRootEntityId], [r4].[AssociateTypeRootEntityId], [s].[RootEntityId], [s].[Id], [s].[Int], [s].[Ints], [s].[Name], [s].[String], [s].[AssociateTypeRootEntityId], [s].[AssociateTypeId], [s].[AssociateTypeRootEntityId0], [s].[AssociateTypeId0], [s].[AssociateTypeRootEntityId1], [s].[AssociateTypeId1], [s].[Id0], [s].[Int0], [s].[Ints0], [s].[Name0], [s].[String0], [s].[Id1], [s].[Int1], [s].[Ints1], [s].[Name1], [s].[String1], [s].[Id2], [s].[Int2], [s].[Ints2], [s].[Name2], [s].[String2], [o].[Id], [o].[Int], [o].[Ints], [o].[Name], [o].[String], [o2].[AssociateTypeRootEntityId], [o2].[Id], [o2].[Int], [o2].[Ints], [o2].[Name], [o2].[String], [o0].[Id], [o0].[Int], [o0].[Ints], [o0].[Name], [o0].[String], [o1].[Id], [o1].[Int], [o1].[Ints], [o1].[Name], [o1].[String], [r0].[Id], [r0].[Int], [r0].[Ints], [r0].[Name], [r0].[String], [r9].[AssociateTypeRootEntityId], [r9].[Id], [r9].[Int], [r9].[Ints], [r9].[Name], [r9].[String], [r3].[Id], [r3].[Int], [r3].[Ints], [r3].[Name], [r3].[String], [r4].[Id], [r4].[Int], [r4].[Ints], [r4].[Name], [r4].[String]
FROM [RootEntity] AS [r]
LEFT JOIN [RequiredRelated] AS [r0] ON [r].[Id] = [r0].[RootEntityId]
LEFT JOIN [OptionalRelated] AS [o] ON [r].[Id] = [o].[RootEntityId]
LEFT JOIN [OptionalRelated_OptionalNested] AS [o0] ON [o].[RootEntityId] = [o0].[AssociateTypeRootEntityId]
LEFT JOIN [OptionalRelated_RequiredNested] AS [o1] ON [o].[RootEntityId] = [o1].[AssociateTypeRootEntityId]
LEFT JOIN [RequiredRelated_OptionalNested] AS [r3] ON [r0].[RootEntityId] = [r3].[AssociateTypeRootEntityId]
LEFT JOIN [RequiredRelated_RequiredNested] AS [r4] ON [r0].[RootEntityId] = [r4].[AssociateTypeRootEntityId]
LEFT JOIN (
    SELECT [r5].[RootEntityId], [r5].[Id], [r5].[Int], [r5].[Ints], [r5].[Name], [r5].[String], [r6].[AssociateTypeRootEntityId], [r6].[AssociateTypeId], [r7].[AssociateTypeRootEntityId] AS [AssociateTypeRootEntityId0], [r7].[AssociateTypeId] AS [AssociateTypeId0], [r8].[AssociateTypeRootEntityId] AS [AssociateTypeRootEntityId1], [r8].[AssociateTypeId] AS [AssociateTypeId1], [r8].[Id] AS [Id0], [r8].[Int] AS [Int0], [r8].[Ints] AS [Ints0], [r8].[Name] AS [Name0], [r8].[String] AS [String0], [r6].[Id] AS [Id1], [r6].[Int] AS [Int1], [r6].[Ints] AS [Ints1], [r6].[Name] AS [Name1], [r6].[String] AS [String1], [r7].[Id] AS [Id2], [r7].[Int] AS [Int2], [r7].[Ints] AS [Ints2], [r7].[Name] AS [Name2], [r7].[String] AS [String2]
    FROM [RelatedCollection] AS [r5]
    LEFT JOIN [RelatedCollection_OptionalNested] AS [r6] ON [r5].[RootEntityId] = [r6].[AssociateTypeRootEntityId] AND [r5].[Id] = [r6].[AssociateTypeId]
    LEFT JOIN [RelatedCollection_RequiredNested] AS [r7] ON [r5].[RootEntityId] = [r7].[AssociateTypeRootEntityId] AND [r5].[Id] = [r7].[AssociateTypeId]
    LEFT JOIN [RelatedCollection_NestedCollection] AS [r8] ON [r5].[RootEntityId] = [r8].[AssociateTypeRootEntityId] AND [r5].[Id] = [r8].[AssociateTypeId]
) AS [s] ON [r].[Id] = [s].[RootEntityId]
LEFT JOIN [OptionalRelated_NestedCollection] AS [o2] ON [o].[RootEntityId] = [o2].[AssociateTypeRootEntityId]
LEFT JOIN [RequiredRelated_NestedCollection] AS [r9] ON [r0].[RootEntityId] = [r9].[AssociateTypeRootEntityId]
WHERE (
    SELECT COUNT(*)
    FROM (
        SELECT 1 AS empty
        FROM [RequiredRelated_NestedCollection] AS [r1]
        WHERE [r0].[RootEntityId] = [r1].[AssociateTypeRootEntityId] AND [r1].[Int] = 8
        UNION ALL
        SELECT 1 AS empty
        FROM [RequiredRelated_NestedCollection] AS [r2]
        WHERE [r0].[RootEntityId] = [r2].[AssociateTypeRootEntityId] AND [r2].[String] = N'foo'
    ) AS [u]) = 4
ORDER BY [r].[Id], [r0].[RootEntityId], [o].[RootEntityId], [o0].[AssociateTypeRootEntityId], [o1].[AssociateTypeRootEntityId], [r3].[AssociateTypeRootEntityId], [r4].[AssociateTypeRootEntityId], [s].[RootEntityId], [s].[Id], [s].[AssociateTypeRootEntityId], [s].[AssociateTypeId], [s].[AssociateTypeRootEntityId0], [s].[AssociateTypeId0], [s].[AssociateTypeRootEntityId1], [s].[AssociateTypeId1], [s].[Id0], [o2].[AssociateTypeRootEntityId], [o2].[Id], [r9].[AssociateTypeRootEntityId]
""");
    }

    public override async Task Over_different_collection_properties()
    {
        await base.Over_different_collection_properties();

        AssertSql();
    }

    [ConditionalFact]
    public virtual void Check_all_tests_overridden()
        => TestHelpers.AssertAllMethodsOverridden(GetType());
}
