/*
 * 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());
    }
}

protected async Task<TItem> GetItem<TItem>(
    Select select,
    Converter<DbDataReader, TItem> converter,
    TraceTree parentTraceTree)
    where TItem : ItemBase
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    // A scriptable can be anything in the WYSE language that can be part of a script:
    // - Executables; like Selects, Inserts, Updates, Deletes, Merges (SqlServer only)
    // - Variable and Temp table declarations
    // - If/Elses
    // ...and more.
    var scriptables = new List</*WYSE*/IScriptable>();

    _queryBackbone.Using(traceTree).Modify(select, scriptables);

    return await _sqlExecuter.GetScriptItemAsync(
        // Create new script combining scriptables with select
        new /*WYSE*/Script(scriptables) + select,
        converter,
        parentTraceTree);
}

public async Task<TItem> GetScriptItemAsync<TItem>(
    /*WYSE*/IScript script,
    Converter<DbDataReader, TItem> converter,
    TraceTree parentTraceTree)
{
    var traceTree = parentTraceTree.AddChild();
    traceTree.RecordCodeMethodExecution(MethodBase.GetCurrentMethod());

    return await Get(
        script,
        reader => reader./*WYSE*/GetItem(converter),
        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;
    }
}

DECLARE @UserDivisionIds AS TABLE (
    [DivisionId] uniqueidentifier NOT NULL
);

INSERT INTO @UserDivisionIds
([DivisionId])
SELECT [56154_FrameworkDemoSystems_dbo_GetDescendants].[DivisionId] [DivisionId_1]
FROM [56154_FrameworkDemoSystems].[dbo].[GetDescendants]('11111111-1111-1111-1111-111111111111') [56154_FrameworkDemoSystems_dbo_GetDescendants];

SELECT
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_FullName] [_StringFunnel],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_DivisionId] [_GuidFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_HomeAddressId] [_GuidFunnel_2],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_BillingAddressId] [_NullableGuidFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_Id] [_GuidFunnel_3],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_Created] [_DateTimeFunnel_1],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_LastModified] [_DateTimeFunnel_2],
    [_CustomerSubselect].[56154_FrameworkDemoCustomers_demo_Customer_IsDeleted] [_BooleanFunnel]
FROM
(
    SELECT
        [56154_FrameworkDemoCustomers_demo_Customer].[FullName] [56154_FrameworkDemoCustomers_demo_Customer_FullName],
        [56154_FrameworkDemoCustomers_demo_Customer].[DivisionId] [56154_FrameworkDemoCustomers_demo_Customer_DivisionId],
        [56154_FrameworkDemoCustomers_demo_Customer].[HomeAddressId] [56154_FrameworkDemoCustomers_demo_Customer_HomeAddressId],
        [56154_FrameworkDemoCustomers_demo_Customer].[BillingAddressId] [56154_FrameworkDemoCustomers_demo_Customer_BillingAddressId],
        [56154_FrameworkDemoCustomers_demo_Customer].[Id] [56154_FrameworkDemoCustomers_demo_Customer_Id],
        [56154_FrameworkDemoCustomers_demo_Customer].[Created] [56154_FrameworkDemoCustomers_demo_Customer_Created],
        [56154_FrameworkDemoCustomers_demo_Customer].[LastModified] [56154_FrameworkDemoCustomers_demo_Customer_LastModified],
        [56154_FrameworkDemoCustomers_demo_Customer].[IsDeleted] [56154_FrameworkDemoCustomers_demo_Customer_IsDeleted]
    FROM [56154_FrameworkDemoCustomers].[demo].[Customer] [56154_FrameworkDemoCustomers_demo_Customer]
    INNER JOIN
    (
        SELECT [UserDivisionIds].[DivisionId] [UserDivisionIds_DivisionId]
        FROM @UserDivisionIds [UserDivisionIds]
    ) [_Select_2] ON ([56154_FrameworkDemoCustomers_demo_Customer].[DivisionId] = [_Select_2].[UserDivisionIds_DivisionId])
    WHERE
    (
        ([56154_FrameworkDemoCustomers_demo_Customer].[Id] = @Id)
        AND
        ([56154_FrameworkDemoCustomers_demo_Customer].[IsDeleted] = @IsDeleted)
    )
) [_CustomerSubselect];