### ----------------------------------------------------
### -- SQL::Builder
### -- Licenses: Artistic-2.0
### -- Authors: Adrian Kreher
### -- File: t/90-from-acoustics.rakutest
### ----------------------------------------------------
use Test;
use SQL::Builder;

my $sql = SQL::Builder.new;

# These SQL queries are extracted from another project to have complex examples
subtest 'search-history', {
    my $ratings-who = 'ak';
    my $q = $sql.from('history').
        select(['songs.song-id', Raw.fmt('{} / 2.0 AS rating', Identifier.new('ratings.rating')), 'history.who::jsonb']).
        join('songs', :on(['songs.song-id', '=', Identifier.new('history.song-id')])).
        join(:left, 'ratings', :on(['songs.song-id', '=', Identifier.new('ratings.song-id')], ['ratings.who', '=', $ratings-who])).
        order-by(\("song-start", :desc));

    with $q.build {
        is .sql, q{
        SELECT "songs"."song-id", "ratings"."rating" / 2.0 AS rating, "history"."who"::jsonb
        FROM "history" JOIN "songs" ON "songs"."song-id" = "history"."song-id"
        LEFT JOIN "ratings" ON "songs"."song-id" = "ratings"."song-id" AND "ratings"."who" = ?
        ORDER BY "song-start" DESC
        }.subst(:g, /\s+/, ' ').trim;
        is .bind, [$ratings-who];
    }
};

subtest 'history-dates', {
    my $tz = 'America/Chicago';
    my $date-trunc = Fn.new('date_trunc', Value.new('day'), Fn.new('timezone', Value.new($tz), 'song-start'));
    my $q = $sql.from('history').
        select([
            \($date-trunc, :as<day>),
            \(Fn.new('COUNT', $date-trunc), :as<count>),
        ]).
        group-by('day').
        order-by(Raw.new('"day" DESC'));
    is $q.build.sql, q{SELECT date_trunc('day', timezone('America/Chicago', "song-start")) AS "day", COUNT(date_trunc('day', timezone('America/Chicago', "song-start"))) AS "count" FROM "history" GROUP BY "day" ORDER BY "day" DESC};
};

subtest 'recent-songs', {
    my $q = $sql.from('songs').
        select(<song-id artist album title>).
        where(:online(1)).
        order-by(Raw.fmt('{} DESC', Identifier.new('created-at'))).
        limit(20);

    with $q.build {
        is .sql, q{SELECT "song-id", "artist", "album", "title" FROM "songs" WHERE "online" = ? ORDER BY "created-at" DESC LIMIT ?};
        is .bind, [1, 20];
    }
};

subtest 'random-songs', {
    my $inner-q = $sql.from('songs').select('song-id').where(:online(1)).order-by(Fn.new('RANDOM')).limit(10);
    my $inner-sql = q{SELECT "song-id" FROM "songs" WHERE "online" = ? ORDER BY RANDOM() LIMIT ?};
    with $inner-q.build {
        is .sql, $inner-sql;
        is .bind, [1, 10];
    }

    my $outer-q = $sql.from($inner-q, :as<random-songs>).
        join('songs', :on('songs.song-id', '=', Identifier.new('random-songs.song-id'))).
        join(:left, 'ratings', :on(['songs.song-id', '=', Identifier.new('ratings.song-id')], ['ratings.who', '=', 'myuser'])).
        select(<songs.song-id artist album title>, Raw.fmt('{} / 2.0 AS rating', Identifier.new("rating")));

    my $outer-sql = qq{
        SELECT "songs"."song-id", "artist", "album", "title", "rating" / 2.0 AS rating
        FROM ($inner-sql) AS "random-songs"
        JOIN "songs" ON "songs"."song-id" = "random-songs"."song-id"
        LEFT JOIN "ratings" ON "songs"."song-id" = "ratings"."song-id" AND "ratings"."who" = ?
    }.subst(:g, /\s+/, ' ').trim;

    with $outer-q.build {
        is .sql, $outer-sql;
        is .bind, [1, 10, 'myuser'];
    }
};

subtest 'load-songs', {
    my $join-expr = Raw.fmt('unnest({}::uuid[]) WITH ORDINALITY t(id, ord)', Placeholder.new(Array.new('1234')));
    my $q = $sql.from('songs').
        join($join-expr, :on('t.id', '=', Identifier.new('songs.song-id'))).
        join(:left, 'ratings', :on(['songs.song-id', '=', Identifier.new('ratings.song-id')], ['ratings.who', '=', 'myuser'])).
        select(<song-id artist album>).
        order-by('t.ord');


    with $q.build {
        is .sql, q{
            SELECT "song-id", "artist", "album" FROM "songs"
            JOIN unnest(?::uuid[]) WITH ORDINALITY t(id, ord) ON "t"."id" = "songs"."song-id"
            LEFT JOIN "ratings" ON "songs"."song-id" = "ratings"."song-id" AND "ratings"."who" = ?
            ORDER BY "t"."ord"
        }.subst(:g, /\s+/, ' ').trim;
        is .bind, [1234, 'myuser'];
    }
};

subtest 'get-playlists', {
    my $inner-playlist-contents = $sql.from('playlists').
        select(['playlist-id', \(Fn.new('jsonb_array_elements_text', 'contents'), :as<contents>)]).
        group-by('playlist-id');

    my $inner-playlist-sql = q{
        SELECT "playlist-id", jsonb_array_elements_text("contents") AS "contents"
        FROM "playlists"
        GROUP BY "playlist-id"
    }.subst(:g, /\s+/, ' ').trim;

    with $inner-playlist-contents.build {
        is .sql, $inner-playlist-sql;
        is .bind, [];
    }

    my $songs-of-playlists = $sql.from('songs').
        join($inner-playlist-contents, :as('inner-playlist-contents'), :on('songs.song-id', '=', Identifier.new('inner-playlist-contents.contents::uuid'))).
        select(<song-id length path inner-playlist-contents.playlist-id art-id>);

    my $songs-of-playlists-sql = qq{
        SELECT "song-id", "length", "path", "inner-playlist-contents"."playlist-id", "art-id"
        FROM "songs" JOIN ($inner-playlist-sql)
        AS "inner-playlist-contents"
        ON "songs"."song-id" = "inner-playlist-contents"."contents"::uuid
    }.subst(:g, /\s+/, ' ').trim;

    with $songs-of-playlists.build {
        is .sql, $songs-of-playlists-sql;
        is .bind, [];
    }

    my $playlists-with-art = $sql.from('playlists').
        join(:left, $songs-of-playlists, :using('playlist-id'), :as('data')).
        select([
            \(Fn.new('SUM', 'data.length'), :as<length>),
            \(Fn.new('COUNT', 'data'), :as<count>),
            |<playlists.playlist-id title who>,
            \(Raw.fmt('mode() WITHIN GROUP(ORDER BY {})', Identifier.new('data.art-id')), :as<art-id>)]).
        group-by('playlists.playlist-id').
        order-by('playlists.title');

    with $playlists-with-art.build {
        is .sql, qq{
            SELECT SUM("data"."length") AS "length", COUNT("data") AS "count", "playlists"."playlist-id", "title", "who", mode() WITHIN GROUP(ORDER BY "data"."art-id") AS "art-id"
            FROM "playlists" LEFT JOIN ($songs-of-playlists-sql)
            AS "data" USING ("playlist-id")
            GROUP BY "playlists"."playlist-id"
            ORDER BY "playlists"."title"
        }.subst(:g, /\s+/, ' ').trim;
        is .bind, [];
    }
};

done-testing;


