#!/usr/bin/perl
use strict;
use warnings;

use Test::More;

BEGIN {
    use_ok( 'SQL::Abstract::Query::Select' );
    use_ok( 'SQL::Abstract::Query' );
}

my $query = SQL::Abstract::Query->new();

# JOIN

{
    my $select = $query->select(
        ['users.user_id', 'email' ],
        ['users', 'user_emails'],
        { 'user_emails.user_id' => \'= users.user_id' },
    );

    is(
        $select->sql(),
        'SELECT "users"."user_id", "email" FROM "users", "user_emails" WHERE ( "user_emails"."user_id" = users.user_id )',
        'implicit joins',
    );
}

{
    my $select = $query->select(
        ['users.user_id', 'email'],
        [
            'users',
            { name => 'user_emails', using => 'user_id' },
        ],
    );

    is(
        $select->sql(),
        'SELECT "users"."user_id", "email" FROM "users" JOIN "user_emails" ON ( "user_emails"."user_id" = "users"."user_id" )',
        'explicit joins',
    );
}

# GROUP BY

{
    my $select = $query->select(
        ['gender', \'AVG(age)'],
        'users',
        undef,
        { group_by => 'gender' },
    );

    is(
        $select->sql(),
        'SELECT "gender", AVG(age) FROM "users" GROUP BY "gender"',
        'scalar group by',
    );
}

{
    my $select = $query->select(
        ['gender', 'title', \'AVG(age)'],
        'users',
        undef,
        { group_by => ['gender', 'title'] },
    );

    is(
        $select->sql(),
        'SELECT "gender", "title", AVG(age) FROM "users" GROUP BY "gender", "title"',
        'array group by',
    );
}

# ORDER BY

{
    my $select = $query->select(
        ['name', 'age'],
        'users',
        undef,
        { order_by => 'age' },
    );

    is(
        $select->sql(),
        'SELECT "name", "age" FROM "users" ORDER BY "age"',
        'scalar order by',
    );
}

{
    my $select = $query->select(
        ['name', 'age', 'height'],
        'users',
        undef,
        { order_by => [{'age' => 'desc'}, 'height'] },
    );

    is(
        $select->sql(),
        'SELECT "name", "age", "height" FROM "users" ORDER BY "age" DESC, "height"',
        'hash order by',
    );
}

# LIMIT

{
    my $query = SQL::Abstract::Query->new( SQL::Dialect->new(limit=>'offset') );

    my $select = $query->select(
        ['user_id'],
        'users',
        undef,
        { limit => 20, offset => 100 },
    );

    is(
        $select->sql(),
        'SELECT "user_id" FROM "users" LIMIT ? OFFSET ?',
        'offset limit dialect sql',
    );

    is_deeply(
        [ $select->original_values() ],
        [20, 100],
        'offset limit dialect values',
    );
}

{
    my $query = SQL::Abstract::Query->new( SQL::Dialect->new(limit=>'xy') );

    my $select = $query->select(
        ['user_id'],
        'users',
        undef,
        { limit => 20, offset => 100 },
    );

    is(
        $select->sql(),
        'SELECT "user_id" FROM "users" LIMIT ?, ?',
        'xy limit dialect sql',
    );

    is_deeply(
        [ $select->original_values() ],
        [100, 20],
        'xy limit dialect values',
    );
}

{
    my $query = SQL::Abstract::Query->new( SQL::Dialect->new(rownum=>1) );

    my $select = $query->select(
        ['user_id'],
        'users',
        undef,
        { limit => 20, offset => 100 },
    );

    is(
        $select->sql(),
        'SELECT * FROM ( SELECT "A".*, ROWNUM "r" FROM ( SELECT "user_id" FROM "users" ) "A" WHERE ROWNUM <= ? + ? ) "B" WHERE "r" > ?',
        'rownum limit dialect sql',
    );

    is_deeply(
        [ $select->original_values() ],
        [20, 100, 100],
        'rownum limit dialect values',
    );
}

# ALL TOGETHER NOW!

{
    my $query = SQL::Abstract::Query->new( SQL::Dialect->new(limit=>'offset') );

    my $select = $query->select(
        ['u.user_id', 'e.address', \'MAX(logs.level)'],
        [ {'users' => 'u'}, {name=>'user_emails', as=>'e', on=>{'e.user_id' => \'= u.user_id'}}, 'logs' ],
        { 'logs.user_id' => \'= u.user_id' },
        { order_by => 'u.user_id', group_by => [qw( u.user_id e.adress )], limit => 20, offset => 100 },
    );

    is(
        $select->sql(),
        'SELECT "u"."user_id", "e"."address", MAX(logs.level) FROM "users" "u" JOIN "user_emails" "e" ON ( "e"."user_id" = u.user_id ), "logs" WHERE ( "logs"."user_id" = u.user_id ) GROUP BY "u"."user_id", "e"."adress" ORDER BY "u"."user_id" LIMIT ? OFFSET ?',
        'joins, ordery by, group by, and limit',
    );
}

done_testing;