package SQL_OOP_WhereTest;
use strict;
use warnings;
use lib qw(lib);
use lib qw(t/lib);
use base 'Test::Class';
use Test::More;
use SQL::OOP;
use SQL::OOP::Select;
use Tie::IxHash;
	
	__PACKAGE__->runtests;
	
	sub not_in : Test(6) {
		
		my $where = SQL::OOP::Where->new();
		my $in = $where->not_in('col', [1, 2, 3]);
		is($in->to_string, q{"col" NOT IN (?, ?, ?)});
		my @bind = $in->bind;
		is(scalar @bind, 3);
		is(shift @bind, '1');
		is(shift @bind, '2');
		is(shift @bind, '3');

		my $sub = SQL::OOP::Select->new;
		$sub->set(
			$sub->ARG_FIELDS => '*',
			$sub->ARG_FROM => 'tbl',
		);
		$in = $where->not_in('col', $sub);
		is($in->to_string, q{"col" NOT IN (SELECT * FROM tbl)});
	}
	
	sub in : Test(6) {
		
		my $where = SQL::OOP::Where->new();
		my $in = $where->in('col', [1, 2, 3]);
		is($in->to_string, q{"col" IN (?, ?, ?)});
		my @bind = $in->bind;
		is(scalar @bind, 3);
		is(shift @bind, '1');
		is(shift @bind, '2');
		is(shift @bind, '3');

		my $sub = SQL::OOP::Select->new;
		$sub->set(
			$sub->ARG_FIELDS => '*',
			$sub->ARG_FROM => 'tbl',
		);
		$in = $where->in('col', $sub);
		is($in->to_string, q{"col" IN (SELECT * FROM tbl)});
	}
	
	sub cmp_value_undef : Test(1) {
		
		my $where = SQL::OOP::Where->new();
		my $a = $where->cmp('=', 'a', undef);
		is($a, '');
	}
	
	sub cmp_nested : Test(2) {
		
		my $where = SQL::OOP::Where->new();
		my $sql = SQL::OOP::Base->new('test');
		{
			my $a = $where->cmp('=', 'col1', $sql);
			is($a->to_string, '"col1" = test');
		}
		{
			my $a = $where->cmp('=', SQL::OOP::ID->new('col1'), $sql);
			is($a->to_string, '"col1" = test');
		}
	}
	
	sub cmp_nested2 : Test(1) {
		
		my $where = SQL::OOP::Where->new();
		my $a = $where->cmp('=', SQL::OOP::Base->new('func(col1)'),
										SQL::OOP::Base->new('func(col2)'));
		is($a->to_string, q{func(col1) = func(col2)});
	}
	
	sub order_by : Test(3) {
		
		my $where = SQL::OOP::Where->new();
		my $obj = $where->cmp('=', 'key1', 'val1');
		is($obj->to_string, q{"key1" = ?});
		my $obj2 = $where->or();
		$obj2->append($where->cmp('=', 'key2', 'val2'));
		is($obj2->to_string, q{"key2" = ?});
		$obj2->append($where->or(
			$where->cmp('=', 'key3', 'val3'),
			$where->cmp('=', 'key4', 'val4')
		));
		is($obj2->to_string, q{"key2" = ? OR ("key3" = ? OR "key4" = ?)});
	}
	
	sub and : Test(1) {
		
		my $where = SQL::OOP::Where->new;
		my $and = $where->and('a','b');
		is($and->to_string, q{a AND b});
	}
	
	sub and_with_sub : Test(1) {
		
		my $where = SQL::OOP::Where->new;
		my $and = $where->and(sub{'a'},sub{'b'}->());
		is($and->to_string, q{a AND b});
	}
	
	sub abstract_and : Test(1) {
		
		tie(my %seed, 'Tie::IxHash');
		%seed = (
			a => 'b',
			c => 'd',
		);
		my $where = SQL::OOP::Where->and_hash(\%seed);
		is($where->to_string, q{"a" = ? AND "c" = ?});
	}
	
	sub abstract_and_with_op : Test(1) {
		
		tie(my %seed, 'Tie::IxHash');
		%seed = (
			a => 'b',
			c => 'd',
		);
		my $where = SQL::OOP::Where->and_hash(\%seed, "LIKE");
		is($where->to_string, q{"a" LIKE ? AND "c" LIKE ?});
	}
	
	sub abstract_or : Test(1) {
		
		tie(my %seed, 'Tie::IxHash');
		%seed = (
			a => 'b',
			c => 'd',
		);
		my $where = SQL::OOP::Where->or_hash(\%seed);
		is($where->to_string, q{"a" = ? OR "c" = ?});
	}
	
	sub cmp_key_by_array : Test(2) {
		
		my $id = SQL::OOP::ID->new('public','table','c1');
		is($id->to_string, q{"public"."table"."c1"});
		my $where = SQL::OOP::Where->cmp('=', $id, 'val');
		is($where->to_string, q{"public"."table"."c1" = ?});
	}
	
	sub cmp_key_by_array_ref : Test(1) {
		
		my $where = SQL::OOP::Where->cmp('=', ['public','table','c1'], 'val');
		is($where->to_string, q{"public"."table"."c1" = ?});
	}
	
	sub is_null : Test(2) {
		
		my $where = SQL::OOP::Where->is_null('col1');
		is($where->to_string, q{"col1" IS NULL});
		my $where2 = SQL::OOP::Where->is_null(SQL::OOP::ID->new('col1'));
		is($where2->to_string, q{"col1" IS NULL});
	}
	
	sub between : Test(2) {
		
		my $where = SQL::OOP::Where->between('col1', 1, 2);
		is($where->to_string, q{"col1" BETWEEN ? AND ?});
		my $where2 = SQL::OOP::Where->between(SQL::OOP::ID->new('col1'), 1, 2);
		is($where2->to_string, q{"col1" BETWEEN ? AND ?});
	}
	
	sub between_smart : Test(2) {
		
		my $where = SQL::OOP::Where->between('col1', 1, undef);
		is($where->to_string, q{"col1" >= ?});
		my $where2 = SQL::OOP::Where->between(SQL::OOP::ID->new('col1'), 1, undef);
		is($where2->to_string, q{"col1" >= ?});
	}
	
	sub compress_sql {
		
		my $sql = shift;
		$sql =~ s/[\s\r\n]+/ /gs;
		$sql =~ s/[\s\r\n]+$//gs;
		$sql =~ s/\(\s/\(/gs;
		$sql =~ s/\s\)/\)/gs;
		return $sql;
	}