/*
 * ItemSubselectBase is the Framework Demo base class for all subselects used in
 * repositories and other dynamic query building.
 * This class inherits from WYSE's convertable subselect that enables query result row 
 * conversion (extending DbDataReader) and (Hierarchical) Object Relational Mapping.
 * Usage examples (different overloads):
 * - var select = new AddressSelect()  // Gets all addresses
 * - var select = new AddressSelect(id: new Guid("11111111-1111-1111-1111-111111111111"))  // Get a single address by id
 * - var select = new AddressSelect(isDeleted: true)  // Only gets the deleted addresses
 * - var select = new AddressSelect(new PagingSettings { PageNumber = 3, PageSize = 20 })
 * So a Select with WYSE can be used to encapsulate SQL logic,
 * making it kind of like a coded version of a SqlServer function or view,
 * but much more flexible!
 */
protected ItemSubselectBase(
    ItemTableBase<TItem> sourceTable,
    TraceTree parentTraceTree,
    PagingSettings? pagingSettings,  /* If not null then paging is added to the query */
    Guid? id,  /* If not null then a select by id is done */
    bool? isDeleted)  /* If 'null' then both deleted and not deleted */
{
    var traceTree = parentTraceTree?.AddChild();
    traceTree?.RecordCodeConstructorExecution(typeof(ItemSubselectBase<TSelect, TItem>));

    _sourceListConvert = sourceTable.Convert;

    // Funneling is a concept used in WYSE to expose the columns of a (nested)
    // subselect one level higher in a query, so it can be used in a From or in
    // Where/Join expressions for example.
    Id = /*WYSE*/Funnel(sourceTable.Id);
    Created = /*WYSE*/Funnel(sourceTable.Created);
    LastModified = /*WYSE*/Funnel(sourceTable.LastModified);
    IsDeleted = /*WYSE*/Funnel(sourceTable.IsDeleted);

    var columnExpressions = new List</*WYSE*/IExpression>(
        sourceTable./*WYSE*/ListColumns());

    if (pagingSettings is not null)
    {
        // Add SqlServer's 'COUNT(Id) OVER()' to selected columns.
        // The modifying of the Where clause for row selection is done in
        // the query backbone. Check the traces below.
        sourceTable.CountUnpaged = new /*WYSE*/Count(sourceTable.Id).Over();
        CountUnpaged = /*WYSE*/Funnel(sourceTable.CountUnpaged);

        columnExpressions.Add(sourceTable.CountUnpaged);
    }

    // This makes the statement: SELECT [columnExpressions] FROM [sourceList]
    /*WYSE*/From from = this./*WYSE*/Columns(columnExpressions).From(sourceTable);

    // Determine the applicable where clause sub expressions (based on SQL parameters)
    var whereExpressions = new List</*WYSE*/BooleanExpression>();

    if (id.HasValue)
    {
        whereExpressions.Add(
            sourceTable.Id == new /*WYSE*/UniqueIdentifierParam("Id", id));
    }
    if (isDeleted.HasValue)
    {
        whereExpressions.Add(
            sourceTable.IsDeleted == new /*WYSE*/BitParam("IsDeleted", isDeleted));
    }

    if (whereExpressions.Count > 0)
    {
        // Append a Where clause with a condition that is all the
        // sub expressions 'AND'-ed together
        from.Where(whereExpressions./*WYSE*/And());
    }
}

// Generic mappings are WYSE's way of defining mappings separate from a
// specific Select, in order to facilitate re-use.
public IReadOnlyList</*WYSE*/GenericMapping> GetGenericMappings()
{
    var traceTree = _parentTraceTree?.AddChild();
    traceTree?.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    if (_genericMappings is null)
    {
        // Factory for creating generic mappings starting from a
        // 'DashboardSubselect' source list having a domain object
        // of type 'Dashboard'
        var factory = new /*WYSE*/GenericMappingsFactory<DashboardSubselect, Dashboard>();

        _genericMappings = new /*WYSE*/GenericMappingList(
            /* Join target is a 'WidgetGroupsSubselect'. */
            factory.CreateListMapping<WidgetGroupsSubselect, WidgetGroup>(
                (ds, wgs) => ds.Id == wgs.DashboardId,  /* Join expression */
                /* Maps to a list of WidgetGroup objects */
                dashboard => dashboard.WidgetGroups,
                /* Each WidgetGroup references back to its parent Dashboard.
                   In WYSE this is called Property Entanglement. */
                widgetGroup => widgetGroup.Dashboard));
    }

    return _genericMappings;
}

public async Task<IReadOnlyList<TItem>> GetItemTreesAsync<TItem>(
    ISelect select,
    Converter<DbDataReader, TItem> converter,
    IReadOnlyList<Mapping> mappings,
    TraceTree parentTraceTree)
    where TItem : IKeyEquatable
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    return await Get(
        select,
        reader => reader./*WYSE*/GetItemTrees(select, converter, mappings).ToList(),
        parentTraceTree);
}

private async Task<TItem> Get<TItem>(
    /*WYSE*/IExecutable executable,  /* A Select or a Script */
    Func<DbDataReader, TItem> getResult,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    using SqlConnection connection = _connectionFactory.Create();
    connection.Open();

    // Aliases and parameter names can be given custom values.
    // If omitted, unique identifer values are supplied
    // In the case of this Demo, these generated values are overwritten,
    // based on the current render settings, with more legible values.
    // WYSE supports several kinds of Resetting/renaming.
    ResetIdentifiers(executable);

    using SqlCommand command = connection.CreateCommand()
        // A WYSE Render context determines certain (overridable) render settings.
        // Here the context is SqlServer. More SQL flavors are supported.
        // A WYSE render builder is used for stringifying
        // the strongly typed SQL statements.
        /*WYSE*/.For(executable, _renderContextFactory.Create(), _createRenderBuilder);

    try
    {
        using var reader = await command.ExecuteReaderAsync();

        var result = getResult(reader);

        traceTree.RecordDbCommandExecutionSuccess(
            command.CommandText,
            command.Parameters.ToDictionary(),
            result);

        return result;
    }
    catch (Exception exception)
    {
        traceTree.RecordDbCommandExecutionFailure(
            command.CommandText,
            command.Parameters.ToDictionary(),
            exception);

        return default;
    }
}

SELECT DISTINCT
    [_DashboardSubselect].[56154_FrameworkDemoSystems_dbo_Dashboard_Name] [_StringFunnel_1],
    [_DashboardSubselect].[56154_FrameworkDemoSystems_dbo_Dashboard_Id] [_GuidFunnel_1],
    [_DashboardSubselect].[56154_FrameworkDemoSystems_dbo_Dashboard_Created] [_DateTimeFunnel_1],
    [_DashboardSubselect].[56154_FrameworkDemoSystems_dbo_Dashboard_LastModified] [_DateTimeFunnel_2],
    [_DashboardSubselect].[56154_FrameworkDemoSystems_dbo_Dashboard_IsDeleted] [_BooleanFunnel_1],
    [_WidgetGroupsSubselect].[56154_FrameworkDemoSystems_dbo_WidgetGroups_Name] [_StringFunnel_2],
    [_WidgetGroupsSubselect].[56154_FrameworkDemoSystems_dbo_WidgetGroups_Index] [_IntegerFunnel_1],
    [_WidgetGroupsSubselect].[56154_FrameworkDemoSystems_dbo_WidgetGroups_DashboardId] [_GuidFunnel_2],
    [_WidgetGroupsSubselect].[56154_FrameworkDemoSystems_dbo_WidgetGroups_Id] [_GuidFunnel_3],
    [_WidgetGroupsSubselect].[56154_FrameworkDemoSystems_dbo_WidgetGroups_Created] [_DateTimeFunnel_3],
    [_WidgetGroupsSubselect].[56154_FrameworkDemoSystems_dbo_WidgetGroups_LastModified] [_DateTimeFunnel_4],
    [_WidgetGroupsSubselect].[56154_FrameworkDemoSystems_dbo_WidgetGroups_IsDeleted] [_BooleanFunnel_2],
    [_WidgetSubselect].[56154_FrameworkDemoSystems_dbo_Widget_Name] [_StringFunnel_3],
    [_WidgetSubselect].[56154_FrameworkDemoSystems_dbo_Widget_Type] [_IntegerFunnel_2],
    [_WidgetSubselect].[56154_FrameworkDemoSystems_dbo_Widget_WidgetGroupId] [_GuidFunnel_4],
    [_WidgetSubselect].[56154_FrameworkDemoSystems_dbo_Widget_Id] [_GuidFunnel_5],
    [_WidgetSubselect].[56154_FrameworkDemoSystems_dbo_Widget_Created] [_DateTimeFunnel_5],
    [_WidgetSubselect].[56154_FrameworkDemoSystems_dbo_Widget_LastModified] [_DateTimeFunnel_6],
    [_WidgetSubselect].[56154_FrameworkDemoSystems_dbo_Widget_IsDeleted] [_BooleanFunnel_3]
FROM
(
    SELECT
        [56154_FrameworkDemoSystems_dbo_Dashboard].[Name] [56154_FrameworkDemoSystems_dbo_Dashboard_Name],
        [56154_FrameworkDemoSystems_dbo_Dashboard].[Id] [56154_FrameworkDemoSystems_dbo_Dashboard_Id],
        [56154_FrameworkDemoSystems_dbo_Dashboard].[Created] [56154_FrameworkDemoSystems_dbo_Dashboard_Created],
        [56154_FrameworkDemoSystems_dbo_Dashboard].[LastModified] [56154_FrameworkDemoSystems_dbo_Dashboard_LastModified],
        [56154_FrameworkDemoSystems_dbo_Dashboard].[IsDeleted] [56154_FrameworkDemoSystems_dbo_Dashboard_IsDeleted]
    FROM [56154_FrameworkDemoSystems].[dbo].[Dashboard] [56154_FrameworkDemoSystems_dbo_Dashboard]
    WHERE
    (
        ([56154_FrameworkDemoSystems_dbo_Dashboard].[Id] = @Id)
        AND
        ([56154_FrameworkDemoSystems_dbo_Dashboard].[IsDeleted] = @IsDeleted1)
    )
) [_DashboardSubselect]
LEFT JOIN
(
    SELECT
        [56154_FrameworkDemoSystems_dbo_WidgetGroups].[Name] [56154_FrameworkDemoSystems_dbo_WidgetGroups_Name],
        [56154_FrameworkDemoSystems_dbo_WidgetGroups].[Index] [56154_FrameworkDemoSystems_dbo_WidgetGroups_Index],
        [56154_FrameworkDemoSystems_dbo_WidgetGroups].[DashboardId] [56154_FrameworkDemoSystems_dbo_WidgetGroups_DashboardId],
        [56154_FrameworkDemoSystems_dbo_WidgetGroups].[Id] [56154_FrameworkDemoSystems_dbo_WidgetGroups_Id],
        [56154_FrameworkDemoSystems_dbo_WidgetGroups].[Created] [56154_FrameworkDemoSystems_dbo_WidgetGroups_Created],
        [56154_FrameworkDemoSystems_dbo_WidgetGroups].[LastModified] [56154_FrameworkDemoSystems_dbo_WidgetGroups_LastModified],
        [56154_FrameworkDemoSystems_dbo_WidgetGroups].[IsDeleted] [56154_FrameworkDemoSystems_dbo_WidgetGroups_IsDeleted]
    FROM [56154_FrameworkDemoSystems].[dbo].[WidgetGroups] [56154_FrameworkDemoSystems_dbo_WidgetGroups]
    WHERE ([56154_FrameworkDemoSystems_dbo_WidgetGroups].[IsDeleted] = @IsDeleted2)
) [_WidgetGroupsSubselect] ON ([_DashboardSubselect].[56154_FrameworkDemoSystems_dbo_Dashboard_Id] = [_WidgetGroupsSubselect].[56154_FrameworkDemoSystems_dbo_WidgetGroups_DashboardId])
LEFT JOIN
(
    SELECT
        [56154_FrameworkDemoSystems_dbo_Widget].[Name] [56154_FrameworkDemoSystems_dbo_Widget_Name],
        [56154_FrameworkDemoSystems_dbo_Widget].[Type] [56154_FrameworkDemoSystems_dbo_Widget_Type],
        [56154_FrameworkDemoSystems_dbo_Widget].[WidgetGroupId] [56154_FrameworkDemoSystems_dbo_Widget_WidgetGroupId],
        [56154_FrameworkDemoSystems_dbo_Widget].[Id] [56154_FrameworkDemoSystems_dbo_Widget_Id],
        [56154_FrameworkDemoSystems_dbo_Widget].[Created] [56154_FrameworkDemoSystems_dbo_Widget_Created],
        [56154_FrameworkDemoSystems_dbo_Widget].[LastModified] [56154_FrameworkDemoSystems_dbo_Widget_LastModified],
        [56154_FrameworkDemoSystems_dbo_Widget].[IsDeleted] [56154_FrameworkDemoSystems_dbo_Widget_IsDeleted]
    FROM [56154_FrameworkDemoSystems].[dbo].[Widget] [56154_FrameworkDemoSystems_dbo_Widget]
    WHERE ([56154_FrameworkDemoSystems_dbo_Widget].[IsDeleted] = @IsDeleted3)
) [_WidgetSubselect] ON ([_WidgetGroupsSubselect].[56154_FrameworkDemoSystems_dbo_WidgetGroups_Id] = [_WidgetSubselect].[56154_FrameworkDemoSystems_dbo_Widget_WidgetGroupId])
WHERE ([_DashboardSubselect].[56154_FrameworkDemoSystems_dbo_Dashboard_Id] IN
(
    SELECT [56154_FrameworkDemoSystems_demo_DashboardsXUsers].[DashboardId] [56154_FrameworkDemoSystems_demo_DashboardsXUsers_DashboardId]
    FROM [56154_FrameworkDemoSystems].[demo].[DashboardsXUsers] [56154_FrameworkDemoSystems_demo_DashboardsXUsers]
    WHERE ([56154_FrameworkDemoSystems_demo_DashboardsXUsers].[UserId] = @UserId)
    UNION ALL
    SELECT [56154_FrameworkDemoSystems_demo_DashboardsXRoles].[DashboardId] [56154_FrameworkDemoSystems_demo_DashboardsXRoles_DashboardId]
    FROM [56154_FrameworkDemoSystems].[demo].[DashboardsXRoles] [56154_FrameworkDemoSystems_demo_DashboardsXRoles]
    INNER JOIN [56154_FrameworkDemoSystems].[demo].[RolesXUsers] [56154_FrameworkDemoSystems_demo_RolesXUsers] ON ([56154_FrameworkDemoSystems_demo_DashboardsXRoles].[RoleId] = [56154_FrameworkDemoSystems_demo_RolesXUsers].[RoleId])
    INNER JOIN
    (
        SELECT
            [56154_FrameworkDemoSystems_dbo_Role].[Name] [56154_FrameworkDemoSystems_dbo_Role_Name],
            [56154_FrameworkDemoSystems_dbo_Role].[Id] [56154_FrameworkDemoSystems_dbo_Role_Id],
            [56154_FrameworkDemoSystems_dbo_Role].[Created] [56154_FrameworkDemoSystems_dbo_Role_Created],
            [56154_FrameworkDemoSystems_dbo_Role].[LastModified] [56154_FrameworkDemoSystems_dbo_Role_LastModified],
            [56154_FrameworkDemoSystems_dbo_Role].[IsDeleted] [56154_FrameworkDemoSystems_dbo_Role_IsDeleted]
        FROM [56154_FrameworkDemoSystems].[dbo].[Role] [56154_FrameworkDemoSystems_dbo_Role]
        WHERE ([56154_FrameworkDemoSystems_dbo_Role].[IsDeleted] = @IsDeleted4)
    ) [_RoleSubselect] ON ([56154_FrameworkDemoSystems_demo_RolesXUsers].[RoleId] = [_RoleSubselect].[56154_FrameworkDemoSystems_dbo_Role_Id])
    WHERE ([56154_FrameworkDemoSystems_demo_RolesXUsers].[UserId] = @UserId)
))