use Test::Exception; # throws_ok()
use Test::More; # see done_testing()
use Test::Deep; # eq_deeply()

use Data::Printer;
use Data::Dumper;
use Path::Tiny;
use 5.26.0;

use FindBin qw($Bin); # warn $Bin;
use lib '/home/raj/perl-lib';

require_ok( 'Local::DB' );
require_ok( 'Local::DBIx::Lite' );

use constant COLS => qw(id username location_id);
use constant TBL  => 'users';

my $db = path($Bin, 'test.sqlite')->realpath; # warn 'DB:'. $db;
my $dsn = "dbi:SQLite:dbname=$db";

my $dbix_simple = Local::DB->dbix({ dsn => $dsn });          # p $dbix_simple;
my $dbix_lite = Local::DBIx::Lite->connect({ dsn => $dsn }); # p $dbix_lite;

{ # hash in scalar context:
	my $a = $dbix_simple->select(TBL, [COLS], { id => 1 })->hash; # p $a;
	my $b = $dbix_lite->table(TBL)->select(COLS)->search({ id => 1 })->hash; # p $b;
	is_deeply($a, $b, 'hash in scalar contex');
}
{ # hash in list context:
	# DBIx::Simple::hash only returns hashref (patched to now use wantarray):
	# my $a = $dbix_simple->select(TBL, [COLS], { id => 1 })->hash; # p $a;
	my %a = $dbix_simple->select(TBL, [COLS])->hash; # p %a;
	my %b = $dbix_lite->table(TBL)->select(COLS)->search({ id => 1 })->hash; # p %b;
	is_deeply(\%a, \%b, 'hash in list contex');
}
{ # array in scalar context:
	my $a = $dbix_simple->select(TBL, [COLS], { id => 1 })->array; # p $a;
	my $b = $dbix_lite->table(TBL)->select(COLS)->search({ id => 1 })->array; # p $b;
	is_deeply($a, $b, 'array in scalar context');
}
{ # array in list context:
	my @a = $dbix_simple->select(TBL, [COLS], { id => 1 })->array; # p @a;	
	my @b = $dbix_lite->table(TBL)->select(COLS)->search({ id => 1 })->array; # p @b;
	is_deeply(\@a, \@b, 'array in list contex');
=begin # patched Local::DBIx::Simple::Result to return in response to wantarray	
	# DBIx::Simple::array only returns arrayref, so @a = list of 1 arrayref:
	ok(! eq_deeply(\@a, \@b), 'array() return structures differ'); # eq_deeply() just returns true or false
	my $a = $dbix_simple->select(TBL, [COLS], { id => 1 })->array; # p $a;	
	is_deeply($a, \@b, 'array in list context');
=cut
}
{ # flat in list context:
	my @a = $dbix_simple->select(TBL, 'username')->flat;      # p @a;
	my @b = $dbix_lite->table(TBL)->select('username')->flat; # p @b;
	is_deeply(\@a, \@b, 'flat in list context');
}
{ # flat in scalar context:
	my $a = $dbix_simple->select(TBL, 'username')->flat;      # p $a;
	my $b = $dbix_lite->table(TBL)->select('username')->flat; # p $b;
	is_deeply($a, $b, 'flat in scalar context');
}
{ # map in list context:
	my %a = $dbix_simple->select(TBL, ['id','username'])->map;    # p %a;
	my %b = $dbix_lite->table(TBL)->select('id','username')->map; # p %b;
	is_deeply(\%a, \%b, 'map in list context');
}
{ # map in scalar context:
	my $a = $dbix_simple->select(TBL, ['id','username'])->map;    # p $a;
	my $b = $dbix_lite->table(TBL)->select('id','username')->map; # p $b;
	is_deeply($a, $b, 'map in scalar context');
}
{ # map_hashes in list context:
	my %a = $dbix_simple->select(TBL, [COLS])->map_hashes('id');    # p %a;
	my %b = $dbix_lite->table(TBL)->select(COLS)->map_hashes('id'); # p %b;
	is_deeply(\%a, \%b, 'map_hashes in list context');
}
{ # map_hashes in scalar context:
	my $a = $dbix_simple->select(TBL, [COLS])->map_hashes('id');    # p $a;
	my $b = $dbix_lite->table(TBL)->select(COLS)->map_hashes('id'); # p $b;
	is_deeply($a, $b, 'map_hashes in scalar context');
}
{ # map arrays in list (hash) context:
	my %a = $dbix_simple->select(TBL, [COLS])->map_arrays(1);    # p %a;
	my %b = $dbix_lite->table(TBL)->select(COLS)->map_arrays(1); # p %b;
	is_deeply(\%a, \%b, 'map_arrays in list (hash) context');
}
{ # map arrays in scalar context:
	my $a = $dbix_simple->select(TBL, [COLS])->map_arrays(1);    # p $a;
	my $b = $dbix_lite->table(TBL)->select(COLS)->map_arrays(1); # p $a;
	is_deeply($a, $b, 'map_arrays in scalar context');
}
{ # list in array context:
	my @a = $dbix_simple->select(TBL, [COLS], { id => 1 })->list; # p @a;
	my @b = $dbix_lite->table(TBL)->select(COLS)->search({ id => 1 })->list; # p @b;
	is_deeply(\@a, \@b, 'list in array context');
}
{ # list in scalar context:
	my $a = $dbix_simple->select(TBL, 'username', { id => 1 })->list; # p $a;
	my $b = $dbix_lite->table(TBL)
		->select('username')
		->search({ id => 1 })
		->list; # p $b;
	is($a, $b, 'list in scalar context');
}
{ # arrays in list context:
	my @a = $dbix_simple->select(TBL, [COLS])->arrays;    # p @a;
	my @b = $dbix_lite->table(TBL)->select(COLS)->arrays; # p @b;
	is_deeply(\@a, \@b, 'arrays in list context');
}
{ # arrays in scalar context:
	my $a = $dbix_simple->select(TBL, [COLS])->arrays;    # p $a;
	my $b = $dbix_lite->table(TBL)->select(COLS)->arrays; # p $b;
	is_deeply($a, $b, 'arrays in scalar context');
}
{ # hashes in list context:
	my @a = $dbix_simple->select(TBL, [COLS])->hashes;    # p @a;
	my @b = $dbix_lite->table(TBL)->select(COLS)->hashes; # p @b;
	is_deeply(\@a, \@b, 'hashes in list context');
}
{ # hashes in scalar context:
	my $a = $dbix_simple->select(TBL, [COLS])->hashes;    # p $a;
	my $b = $dbix_lite->table(TBL)->select(COLS)->hashes; # p $b;
	is_deeply($a, $b, 'hashes in scalar context');
}
{ # into single value:
	$dbix_simple->select(TBL, 'username', { id => 1 })->into(my $a); # p $a;
	$dbix_lite->table(TBL)
		->select('username')
		->search({ id => 1 })
		->into(my $b); # p $b;
	is($a, $b, 'into single value');
}
{ # into multiple values ** cannot do ->into(my @foo) **:
	$dbix_simple->select(TBL, [COLS], { id => 1 })->into( my ($a,$b,$c) ); # p $b;
	$dbix_lite->table(TBL)
		->select(COLS)
		->search({ id => 1 })
		->into( my ($d,$e,$f) ); # p $e;
	is_deeply([$a,$b,$c], [$d,$e,$f], 'into multiple values');
}
{ # omit key col name from map_hashes & key index value from map_arrays:
	throws_ok {	$dbix_simple->select(TBL, [COLS])->map_hashes() }
		qr/Key column name not optional/,
		'omitted key column name (DBIx::Simple)';
	throws_ok {	$dbix_lite->table(TBL)->select(COLS)->map_hashes() }
		qr/Key column name not optional/,
		'omitted key column name (DBIx::Lite)';
	throws_ok {	$dbix_lite->table(TBL)->select(COLS)->map_arrays() }
		qr/Key index value not optional/,
		'omitted key index value';
	# but index value = 0 is ok:
	lives_ok { $dbix_lite->table(TBL)->select(COLS)->map_arrays(0) } 
		'key index value 0 is OK';
}
{ # into multiple values but supply incorrect number of vars:
	throws_ok { 
		$dbix_simple->select(TBL, [COLS], { id => 1 })->into( my ($a,$b) ) }
		qr/bind_columns called with 2 values but 3 are needed/, 
		'DBI error - bind_columns failed';
	throws_ok {
		$dbix_lite->table(TBL)
			->select(COLS)
			->search({ id => 1 })
			->into( my ($a,$b) )
		}
		qr/method called with 2 values but 3 are needed/, 
		'too few vars supplied';	
	# too many vals:
	throws_ok {
		$dbix_lite->table(TBL)
			->select(COLS)
			->search({ id => 1 })
			->into( my ($a,$b,$c,$d) )
		}
		qr/method called with 4 values but 3 are needed/, 
		'too many vars supplied';	
	# supply @array notation instead of list of vars:
	throws_ok {
		$dbix_lite->table(TBL)
			->select(COLS)
			->search({ id => 1 })
			->into( my @array )
		}
		qr/method called with 0 values but 3 are needed/, 
		'array notation supplied instead of list';
}		
{ # incorrect key col name:
	throws_ok {	$dbix_lite->table(TBL)->select(COLS)->map_hashes('user') }
		qr/Key column name "user" not found/,
		'incorrect key column name';
}
{ # incorrect key index value:
	throws_ok {	$dbix_lite->table(TBL)->select(COLS)->map_arrays(3) }
		qr/Key index value "3" not found/,
		'incorrect key index value';
}
{ # find() same as list in scalar:
	my $a = $dbix_lite->table(TBL)->select(COLS)->search({ id => 2 })->hash;
	$dbix_lite->schema->table(TBL)->autopk('id');
	my $b = $dbix_lite->table(TBL)->find(2)->hashref; # p $b;
	is_deeply($a, $b, 'find() same as select() with search()');	
}
{ # table joins:
	my $sql = q!select t1.id, t1.username, t2.name as 'location'
		from users t1 join locations t2 on t1.location_id = t2.id
		order by t1.username!;
	my $a = $dbix_simple->query($sql)->hashes; # p $a;

	my $b = $dbix_lite
		->table(TBL)
		->select('id','username')
		->inner_join('locations', { location_id => 'id' })
		->select_also(['locations.name' => 'location'])
		->order_by('username')
		->hashes; # p $b;
	is_deeply($a, $b, 'simple table join with order by');	
}
{ # define relationships
	$dbix_lite->schema->one_to_many(
		'users.location_id' => 'locations.id', 'locations' );
	$dbix_lite->schema->one_to_many(
		'locations.id' => 'users.location_id', 'users' );

	my $rs = $dbix_lite->table('locations')
		->search({ name => 'keld' })
		->users; # p $_->hashref for @all;
	# cannot call any of hashes(), arrays(), etc methods on rs, have to use all():
	my @a = map $_->hashref, $rs->all; # p @a;

	my $sql = q!select t1.id, t1.username, t1.location_id
		from users t1 join locations t2 on t1.location_id = t2.id
		where t2.name = 'keld'!;
	my @b = $dbix_simple->query($sql)->hashes; # p @b;
	is_deeply(\@a, \@b, 'one_to_many relationship locations => users');	
}
{
	my @users = qw( ruby scarlet sapphire );
	my $rs = $dbix_lite->table('users')
		->search({ username => \@users })
		->locations;
	my @a = map $_->hashref, $rs->all; # p @a;

	my $sql = q!select t2.id, t2.name
		from users t1 join locations t2 on t1.location_id = t2.id
		where t1.username in (??)!;
	my @b = $dbix_simple->query($sql, @users)->hashes; # p @b;
	is_deeply(\@a, \@b, 'one_to_many relationship users => locations');	
}
{ # test new value() method:
	my $a = $dbix_simple->select(TBL, 'username', { id => 1 })->value; # p $a;
	my $b = $dbix_simple->select(TBL, 'username', { id => 1 })->list;  # p $b;
	is($a, $b, 'Local::DBIx::Simple::Result::value() same as DBIx::Simple::list() scalar');	
	my $c = $dbix_lite->table(TBL)->select('username')->search({ id => 1 })->value; # p $c;
	is($a, $c, 'value() equivalent in LDL::ResultSet & LDS::Result');
	# >1 result for value():
	my $d = $dbix_lite->table(TBL)->select(COLS)->search({ id => 1 })->value; # p $d;
	my $e = $dbix_simple->select(TBL, 'location_id', { id => 1 })->value;
	is($d, $e, 'value() returns last item if >1 values selelected'); 
}
{ # test new row() method in scalar context (returns arrayref):
	my $a = $dbix_simple->select(TBL, [COLS], { id => 1 })->row;   # p $a;
	my $b = $dbix_simple->select(TBL, [COLS], { id => 1 })->array; # p $b;
	is_deeply($a, $b, 'Local::DBIx::Simple::Result::row() same as DBIx::Simple::array() aref');
	my $c = $dbix_lite->table(TBL)->select(COLS)->search({ id => 1 })->row; # p $c;
	is($a->[-1], $c, 'row() as scalar equivalent in LDL::ResultSet & LDS::Result');
}
{ # test new row() method in array context:
	my @a = $dbix_simple->select(TBL, [COLS], { id => 1 })->row;  # p @a;
	my @b = $dbix_simple->select(TBL, [COLS], { id => 1 })->list; # p @b;
	is_deeply(\@a, \@b, 'Local::DBIx::Simple::Result::row() same as DBIx::Simple::list() array');
	my @c = $dbix_lite->table(TBL)->select(COLS)->search({ id => 1 })->row; # p @c;
	is_deeply(\@a, \@c, 'row() as array equivalent in LDL::ResultSet & LDS::Result');
}
{ # test new column() method in scalar context:
	my $a = $dbix_simple->select(TBL, 'username')->column; # p $a;
	my $b = $dbix_simple->select(TBL, 'username')->flat;   # p $b;
	is_deeply($a, $b, 'Local::DBIx::Simple::Result::column() same as DBIx::Simple::flat() aref');
	my $c = $dbix_lite->table(TBL)->select('username')->column; # p $c;
	is_deeply($a, $c, 'column() as scalar equivalent in LDL::ResultSet & LDS::Result');
}
{ # test new column() method in array context:
	my @a = $dbix_simple->select(TBL, 'username')->column; # p @a;
	my @b = $dbix_simple->select(TBL, 'username')->flat;   # p @b;
	is_deeply(\@a, \@b, 'Local::DBIx::Simple::Result::column() same as DBIx::Simple::flat() array');
	my @c = $dbix_lite->table(TBL)->select('username')->column; # p @c;
	is_deeply(\@a, \@c, 'column() as array equivalent in LDL::ResultSet & LDS::Result');
}

done_testing(47);