use Test::More;
use Test::Exception;

use strict;

# modified DBIx::Simple sqlite.t - to test LIMS::Local::DBIxSimple

BEGIN {
    eval { require DBD::SQLite; 1 }
        or plan skip_all => 'DBD::SQLite required';
    eval { DBD::SQLite->VERSION >= 1 }
        or plan skip_all => 'DBD::SQLite >= 1.00 required';

    plan tests => 23;

    use_ok('LIMS::Local::DBIxSimple');
}

use Data::Dumper;

# In memory database! No file permission troubles, no I/O slowness.
# http://use.perl.org/~tomhukins/journal/31457 ++

my $db = LIMS::Local::DBIxSimple->connect('dbi:SQLite:dbname=:memory:');

ok($db);

eval { $db->query('SYNTAX ERR0R !@#!@#') }; # warn Dumper $@;
like($@, qr/prepare failed/);  # test RaiseError

# original (modified) setup + tests:
#===============================================================================
ok($db->query('CREATE TABLE xyz (wun, too, tree)'));
ok($db->query('INSERT INTO xyz (wun, too, tree) VALUES (?, ?, ?)', qw(a b c)));
ok($db->query('INSERT INTO xyz VALUES (??)', qw(d e f)));
is_deeply([ $db->query('SELECT * FROM xyz')->flat ], [ qw(a b c d e f) ]);
#===============================================================================

{ # 1 individual placeholders
    my $sql = 'SELECT * FROM xyz WHERE wun = ?';
    my $result = $db->query($sql, qw/a/ )->flat;
    is_deeply($result, [ qw(a b c) ]);
}

{ # 2 individual placeholders
    my $sql = 'SELECT * FROM xyz WHERE wun = ? and too = ?';
    my $result = $db->query($sql, qw/a b/ )->flat;
    is_deeply($result, [ qw(a b c) ]);
}

{ # variant of above, negated val
    my $sql = 'SELECT * FROM xyz WHERE wun = ? and too != ?';
    my $result = $db->query($sql, qw/a e/ )->flat;
    is_deeply($result, [ qw(a b c) ]);
}

{ # 3 individual placeholders
    my $sql = 'SELECT * FROM xyz WHERE wun = ? and too = ? and tree = ?';
    my $result = $db->query($sql, qw/d e f/ )->flat;
    is_deeply($result, [ qw(d e f) ]);
}

{ # 1 placeholder, 1 omniplaceholder
    my $sql = 'SELECT * FROM xyz WHERE wun = ? and too IN (??)';
    my $result = $db->query($sql, qw/a b z/ )->flat;
    is_deeply($result, [ qw(a b c) ]);
}

{ # 1 omniplaceholder, 1 placeholder
    my $sql = 'SELECT * FROM xyz WHERE wun IN (??) and tree = ?';
    my $result = $db->query($sql, qw/a z c/ )->flat;
    is_deeply($result, [ qw(a b c) ]);
}

{  # 2 placeholders, 1 omniplaceholder
    my $sql = 'SELECT * FROM xyz WHERE wun = ? and too = ? and tree IN (??)';
    my $result = $db->query($sql, qw/d e z f/ )->flat; # warn Dumper $result;
    is_deeply($result, [ qw(d e f) ]);
}

{  # 2 placeholders, 1 omniplaceholder (negative)
    my $sql = 'SELECT * FROM xyz WHERE wun = ? and too = ? and tree NOT IN (??)';
    my $result = $db->query($sql, qw/a b y z/ )->flat; # warn Dumper $result;
    is_deeply($result, [ qw(a b c) ]);
}

{ # 1 placeholder, 1 omniplaceholder, 1 placeholder
    my $sql = 'SELECT * FROM xyz WHERE wun = ? and too IN (??) and tree = ?';
    my $result = $db->query($sql, qw/a b z c/ )->flat; # warn Dumper $result;
    is_deeply($result, [ qw(a b c) ]);
}

{ # 1 omniplaceholder, 2 placeholders
    my $sql = 'SELECT * FROM xyz WHERE wun IN (??) and too = ? and tree = ?';
    my $result = $db->query($sql, qw/d z e f/ )->flat; # warn Dumper $result;
    is_deeply($result, [ qw(d e f) ]);
}

{ # 1 omniplaceholder with many opts, 1 placeholder
    my $sql = 'SELECT * FROM xyz WHERE wun IN (??) and too = ?';
    my $result = $db->query($sql, qw/a n m o p q r s t b/ )->flat; # warn Dumper $result;
    is_deeply($result, [ qw(a b c) ]);
}

{ # 2 onmiplaceholders - fatal:
    my $sql = 'SELECT * FROM xyz WHERE wun IN (??) and too IN (??)';
    dies_ok {
        $db->query($sql, qw/d z e z/ )->flat;
    } 'there can be only one omniholder';
}

ok($db->query('INSERT INTO xyz (wun, too, tree) VALUES (?, ?, ?)', qw(g h), 'is this a question?'));
is_deeply([ $db->query('SELECT * FROM xyz')->flat ], [ qw(a b c d e f g h), 'is this a question?' ]);

{ # can cope with question in query:
    my $sql = 'SELECT * FROM xyz WHERE wun IN (??) and tree = ?';
    my $result = $db->query($sql, qw(g x y z), 'is this a question?')->flat; # warn Dumper $result;
    is_deeply($result, [ qw(g h), 'is this a question?' ]);
}

ok($db->disconnect);