CREATE OR REPLACE FUNCTION public.build_includes(includes jsonb, parent_table text, parent_alias text)
 RETURNS TABLE(select_clause text, join_clause text, alias_mapping jsonb)
 LANGUAGE plpgsql
AS $function$
DECLARE
    include JSONB;
    attributes JSONB;
    table_name TEXT;
    join_type TEXT;
    join_condition TEXT;
    alias TEXT;
    col JSONB;
    nested_include JSONB;
    nested_result RECORD;
    local_select_clause TEXT := '';
    local_join_clause TEXT := '';
    local_alias_mapping JSONB := '{}';
BEGIN
    FOR include IN SELECT * FROM jsonb_array_elements(includes) LOOP
        attributes := include->'attributes';
        table_name := include->>'table_name';
        join_type := include->>'join_type';
        join_condition := include->>'join_condition';
        alias := include->>'alias';

        -- Build the SELECT clause
        FOR col IN SELECT * FROM jsonb_array_elements(attributes) LOOP
            IF local_select_clause <> '' THEN
                local_select_clause := local_select_clause || ', ';
            END IF;

			IF col->>0 ~ 'json_agg|SUM|COUNT|AVG|MIN|MAX' THEN
                local_select_clause := local_select_clause || (col->>0) || ' AS ' || quote_ident((col->>1));
            ELSE
                local_select_clause := local_select_clause || table_name || '.' || (col->>0) || ' AS ' || quote_ident((col->>1));
            END IF;
            
            local_alias_mapping := local_alias_mapping || jsonb_build_object(quote_ident((col->>1)), table_name || '.' || (col->>0));
        END LOOP;

        -- Build the JOIN clause
        local_join_clause := local_join_clause || ' ' || join_type || ' JOIN ' || table_name || ' ON ' || join_condition;

        -- Handle nested includes
        IF include ? 'includes' THEN
            nested_include := include->'includes';
            FOR nested_result IN SELECT * FROM build_includes(nested_include, table_name, alias) LOOP
                local_select_clause := local_select_clause || ', ' || nested_result.select_clause;
                local_join_clause := local_join_clause || ' ' || nested_result.join_clause;
                local_alias_mapping := local_alias_mapping || nested_result.alias_mapping;
            END LOOP;
        END IF;
    END LOOP;

    RETURN QUERY SELECT local_select_clause, local_join_clause, local_alias_mapping;
END;
$function$