-module(galchemy@examples@advanced_select).
-compile([no_auto_import, nowarn_unused_vars, nowarn_unused_function, nowarn_nomatch, inline]).
-define(FILEPATH, "src\\galchemy\\examples\\advanced_select.gleam").
-export([build_union_feed/0, build_derived_totals/0, build_activity_report/0, main/0]).

-file("src\\galchemy\\examples\\advanced_select.gleam", 43).
-spec build_union_feed() -> galchemy@ast@expression:select_query().
build_union_feed() ->
    Active_users = galchemy@dsl@table:as_(
        galchemy@dsl@table:table(<<"users"/utf8>>),
        <<"u"/utf8>>
    ),
    Active_user_name = galchemy@dsl@table:text(Active_users, <<"name"/utf8>>),
    Archived_users = galchemy@dsl@table:as_(
        galchemy@dsl@table:table(<<"archived_users"/utf8>>),
        <<"a"/utf8>>
    ),
    Archived_user_name = galchemy@dsl@table:text(
        Archived_users,
        <<"name"/utf8>>
    ),
    Active_branch = begin
        _pipe = galchemy@dsl@select:select(
            [galchemy@dsl@expr:item(galchemy@dsl@expr:col(Active_user_name)),
                galchemy@dsl@expr:item(
                    galchemy@dsl@expr:text(<<"active"/utf8>>)
                )]
        ),
        galchemy@dsl@select:from(_pipe, Active_users)
    end,
    Archived_branch = begin
        _pipe@1 = galchemy@dsl@select:select(
            [galchemy@dsl@expr:item(galchemy@dsl@expr:col(Archived_user_name)),
                galchemy@dsl@expr:item(
                    galchemy@dsl@expr:text(<<"archived"/utf8>>)
                )]
        ),
        galchemy@dsl@select:from(_pipe@1, Archived_users)
    end,
    galchemy@dsl@select:union_all(Active_branch, Archived_branch).

-file("src\\galchemy\\examples\\advanced_select.gleam", 90).
-spec active_users_cte() -> galchemy@ast@expression:select_query().
active_users_cte() ->
    Users = galchemy@dsl@table:as_(
        galchemy@dsl@table:table(<<"users"/utf8>>),
        <<"u"/utf8>>
    ),
    User_id = galchemy@dsl@table:int(Users, <<"id"/utf8>>),
    User_name = galchemy@dsl@table:text(Users, <<"name"/utf8>>),
    Active = galchemy@dsl@table:bool(Users, <<"active"/utf8>>),
    _pipe = galchemy@dsl@select:select(
        [galchemy@dsl@expr:item(galchemy@dsl@expr:col(User_id)),
            galchemy@dsl@expr:item(galchemy@dsl@expr:col(User_name))]
    ),
    _pipe@1 = galchemy@dsl@select:from(_pipe, Users),
    galchemy@dsl@select:where_(
        _pipe@1,
        galchemy@dsl@predicate:eq(
            galchemy@dsl@expr:col(Active),
            galchemy@dsl@expr:bool(true)
        )
    ).

-file("src\\galchemy\\examples\\advanced_select.gleam", 104).
-spec post_counts_query() -> galchemy@ast@expression:select_query().
post_counts_query() ->
    Posts = galchemy@dsl@table:as_(
        galchemy@dsl@table:table(<<"posts"/utf8>>),
        <<"p"/utf8>>
    ),
    User_id = galchemy@dsl@table:int(Posts, <<"user_id"/utf8>>),
    Published = galchemy@dsl@table:bool(Posts, <<"published"/utf8>>),
    _pipe = galchemy@dsl@select:select(
        [galchemy@dsl@expr:item(galchemy@dsl@expr:col(User_id)),
            galchemy@dsl@expr:as_(
                galchemy@dsl@expr:count_all(),
                <<"post_count"/utf8>>
            )]
    ),
    _pipe@1 = galchemy@dsl@select:from(_pipe, Posts),
    _pipe@2 = galchemy@dsl@select:where_(
        _pipe@1,
        galchemy@dsl@predicate:eq(
            galchemy@dsl@expr:col(Published),
            galchemy@dsl@expr:bool(true)
        )
    ),
    galchemy@dsl@select:group_by(_pipe@2, galchemy@dsl@expr:col(User_id)).

-file("src\\galchemy\\examples\\advanced_select.gleam", 66).
-spec build_derived_totals() -> galchemy@ast@expression:select_query().
build_derived_totals() ->
    Totals = galchemy@dsl@table:as_(
        galchemy@dsl@table:table(<<"totals"/utf8>>),
        <<"totals"/utf8>>
    ),
    Total_posts = galchemy@dsl@table:int(Totals, <<"post_count"/utf8>>),
    _pipe = galchemy@dsl@select:select(
        [galchemy@dsl@expr:as_(
                galchemy@dsl@expr:sum(galchemy@dsl@expr:col(Total_posts)),
                <<"total_published_posts"/utf8>>
            )]
    ),
    galchemy@dsl@select:from_derived(
        _pipe,
        post_counts_query(),
        <<"totals"/utf8>>
    ).

-file("src\\galchemy\\examples\\advanced_select.gleam", 118).
-spec published_user_ids_subquery() -> galchemy@ast@expression:select_query().
published_user_ids_subquery() ->
    Posts = galchemy@dsl@table:as_(
        galchemy@dsl@table:table(<<"posts"/utf8>>),
        <<"p2"/utf8>>
    ),
    User_id = galchemy@dsl@table:int(Posts, <<"user_id"/utf8>>),
    Published = galchemy@dsl@table:bool(Posts, <<"published"/utf8>>),
    _pipe = galchemy@dsl@select:select(
        [galchemy@dsl@expr:item(galchemy@dsl@expr:col(User_id))]
    ),
    _pipe@1 = galchemy@dsl@select:from(_pipe, Posts),
    _pipe@2 = galchemy@dsl@select:where_(
        _pipe@1,
        galchemy@dsl@predicate:eq(
            galchemy@dsl@expr:col(Published),
            galchemy@dsl@expr:bool(true)
        )
    ),
    galchemy@dsl@select:distinct(_pipe@2).

-file("src\\galchemy\\examples\\advanced_select.gleam", 10).
-spec build_activity_report() -> galchemy@ast@expression:select_query().
build_activity_report() ->
    Active_users = galchemy@dsl@table:as_(
        galchemy@dsl@table:table(<<"active_users"/utf8>>),
        <<"au"/utf8>>
    ),
    Active_user_id = galchemy@dsl@table:int(Active_users, <<"id"/utf8>>),
    Active_user_name = galchemy@dsl@table:text(Active_users, <<"name"/utf8>>),
    Post_counts = galchemy@dsl@table:as_(
        galchemy@dsl@table:table(<<"post_counts"/utf8>>),
        <<"pc"/utf8>>
    ),
    Post_counts_user_id = galchemy@dsl@table:int(
        Post_counts,
        <<"user_id"/utf8>>
    ),
    Post_counts_total = galchemy@dsl@table:int(
        Post_counts,
        <<"post_count"/utf8>>
    ),
    _pipe = galchemy@dsl@select:select(
        [galchemy@dsl@expr:item(galchemy@dsl@expr:col(Active_user_id)),
            galchemy@dsl@expr:item(galchemy@dsl@expr:col(Active_user_name)),
            galchemy@dsl@expr:as_(
                galchemy@dsl@expr:coalesce(
                    [galchemy@dsl@expr:col(Post_counts_total),
                        galchemy@dsl@expr:int(0)]
                ),
                <<"published_post_count"/utf8>>
            ),
            galchemy@dsl@expr:as_(
                galchemy@dsl@expr:over(
                    galchemy@dsl@expr:row_number(),
                    [],
                    [galchemy@dsl@select:asc(
                            galchemy@dsl@expr:col(Active_user_id)
                        )]
                ),
                <<"row_number"/utf8>>
            )]
    ),
    _pipe@1 = galchemy@dsl@select:with_cte(
        _pipe,
        <<"active_users"/utf8>>,
        active_users_cte()
    ),
    _pipe@2 = galchemy@dsl@select:from(_pipe@1, Active_users),
    _pipe@3 = galchemy@dsl@select:left_join_derived(
        _pipe@2,
        post_counts_query(),
        <<"pc"/utf8>>,
        galchemy@dsl@predicate:eq(
            galchemy@dsl@expr:col(Active_user_id),
            galchemy@dsl@expr:col(Post_counts_user_id)
        )
    ),
    galchemy@dsl@select:where_(
        _pipe@3,
        galchemy@dsl@predicate:in_subquery(
            galchemy@dsl@expr:col(Active_user_id),
            published_user_ids_subquery()
        )
    ).

-file("src\\galchemy\\examples\\advanced_select.gleam", 76).
-spec main() -> nil.
main() ->
    gleam_stdlib:println(<<"Advanced select example"/utf8>>),
    gleam_stdlib:println(<<"CTE + derived join + subquery + window:"/utf8>>),
    gleam_stdlib:println(
        gleam@string:inspect(
            galchemy:compile({select, build_activity_report()})
        )
    ),
    gleam_stdlib:println(<<"UNION ALL:"/utf8>>),
    gleam_stdlib:println(
        gleam@string:inspect(galchemy:compile({select, build_union_feed()}))
    ),
    gleam_stdlib:println(<<"FROM derived:"/utf8>>),
    gleam_stdlib:println(
        gleam@string:inspect(galchemy:compile({select, build_derived_totals()}))
    ).