### ----------------------------------------------------
### -- SQL::Builder
### -- Licenses: Artistic-2.0
### -- Authors: Adrian Kreher
### -- File: t/01-basic.rakutest
### ----------------------------------------------------
use Test;
use SQL::Builder;

my $sql = SQL::Builder.new;

subtest 'select clause', {
    given $sql.from('table').select('foo').build {
        is .sql, 'SELECT "foo" FROM "table"';
        is .bind, [];
    }

    given $sql.from('table').select('table.foo').build {
        is .sql, 'SELECT "table"."foo" FROM "table"';
        is .bind, [];
    }

    given $sql.from('table').select(:all).build {
        is .sql, 'SELECT * FROM "table"';
        is .bind, [];
    }
};

subtest 'where clause', {
    given $sql.from('table').where(:bar<bar>).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE "bar" = ?';
        is .bind, ['bar'];
    }
    given $sql.from('table').where(<bar = bar>).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE "bar" = ?';
        is .bind, ['bar'];
    }
    given $sql.from('table').where([['bar', '=', 'bar'],]).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE "bar" = ?';
        is .bind, ['bar'];
    }
    given $sql.from('table').where(['bar', '=', 'bar']).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE "bar" = ?';
        is .bind, ['bar'];
    }
    given $sql.from('table').where([:bar<bar>]).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE "bar" = ?';
        is .bind, ['bar'];
    }

    given $sql.from('table').where([:bar<bar>, :baz<baz>]).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE "bar" = ? AND "baz" = ?';
        is .bind, <bar baz>;
    }
    given $sql.from('table').where(\(:or[:bar<bar>, :baz<baz>])).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE ("bar" = ? OR "baz" = ?)';
        is .bind, <bar baz>;
    }

    given $sql.from('table').where([<foo = 1>, <bar = 2>]).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE "foo" = ? AND "bar" = ?';
        is .bind, [<1 2>];
    }
    given $sql.from('table').where(\(:or[<foo = 1>, <bar = 2>])).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE ("foo" = ? OR "bar" = ?)';
        is .bind, [<1 2>];
    }

    given $sql.from('table').where(:foo(Nil)).select('foo').build {
        is .sql, 'SELECT "foo" FROM "table" WHERE "foo" IS NULL';
        is .bind, [];
    }
}

subtest 'subselect', {
    my $inner = $sql.from('table').select('id');
    given $sql.from($inner, :as<inner>).select(:all).build {
        is .sql, 'SELECT * FROM (SELECT "id" FROM "table") AS "inner"';
    }

    given $sql.from('table').select(:all).where(['id', 'IN', $inner]).build  {
        is .sql, 'SELECT * FROM "table" WHERE "id" IN (SELECT "id" FROM "table")';
        is .bind, [];
    }
};

subtest 'join', {
    given $sql.from('t1').join('t2', :on('t1.foo', '=', Identifier.new('t2.foo'))).select('t1.foo').build {
        is .sql, 'SELECT "t1"."foo" FROM "t1" JOIN "t2" ON "t1"."foo" = "t2"."foo"';
        is .bind, [];
    }

    given $sql.from('t1').join(:left, 't2', :on('t1.foo', '=', Identifier.new('t2.foo'))).select('t1.foo').build {
        is .sql, 'SELECT "t1"."foo" FROM "t1" LEFT JOIN "t2" ON "t1"."foo" = "t2"."foo"';
        is .bind, [];
    }

    given $sql.from('t1').join('t2', :on(['t1.foo', '=', Identifier.new('t2.foo')], ['t1.bar', '=', 'hello world'])).select('t1.foo').build {
        is .sql, 'SELECT "t1"."foo" FROM "t1" JOIN "t2" ON "t1"."foo" = "t2"."foo" AND "t1"."bar" = ?';
        is .bind, ['hello world'];
    }

    given $sql.from('t1').join('t2', :on(\(:or[['t1.foo', '=', Identifier.new('t2.foo')], ['t1.bar', '=', 'hello world']]))).select('t1.foo').build {
        is .sql, 'SELECT "t1"."foo" FROM "t1" JOIN "t2" ON ("t1"."foo" = "t2"."foo" OR "t1"."bar" = ?)';
        is .bind, ['hello world'];
    }
};

subtest 'column alias', {
    given $sql.from('t1').select(\('a', :as<b>)).build {
        is .sql, 'SELECT "a" AS "b" FROM "t1"';
        is .bind, [];
    }

    given $sql.from('t1').select(\("a", :as<b>), \("b", :as<c>)).build {
        is .sql, 'SELECT "a" AS "b", "b" AS "c" FROM "t1"';
        is .bind, [];
    }
};

subtest 'sink error', {
    dies-ok {
        $sql.delete('t1').select('a').build;
    }, 'selectbuilder build';
};

done-testing;


