#!/usr/bin/perl -w
use strict;
use warnings;
use lib qw(t);

use Test::More;
use TestLib qw(connect prove_reqs show_reqs test_dir default_recommended);

use Clone qw(clone);
use Params::Util qw(_CODE _ARRAY);

my ( $required, $recommended ) = prove_reqs( { default_recommended(), ( MLDBM => 0 ) } );
show_reqs( $required, $recommended );
my @test_dbds = ( 'SQL::Statement', grep { /^dbd:/i } keys %{$recommended} );
my $testdir = test_dir();

my @massValues = map { [ $_, ( "a" .. "f" )[ int rand 6 ], int rand 10 ] } ( 1 .. 3999 );

SKIP:
foreach my $test_dbd (@test_dbds)
{
    my $dbh;
    note("Running tests for $test_dbd");
    my $temp = "";
    # XXX
    # my $test_dbd_tbl = "${test_dbd}::Table";
    # $test_dbd_tbl->can("fetch") or $temp = "$temp";
    $test_dbd eq "DBD::File"      and $temp = "TEMP";
    $test_dbd eq "SQL::Statement" and $temp = "TEMP";

    my %extra_args;
    if ( $test_dbd eq "DBD::DBM" )
    {
        if ( $recommended->{MLDBM} )
        {
            $extra_args{dbm_mldbm} = "Storable";
        }
        else
        {
            skip( 'DBD::DBM test runs without MLDBM', 1 );
        }
    }
    elsif( $test_dbd eq "DBD::CSV" )
    {
	$extra_args{csv_null} = 1;
    }

    $dbh = connect(
                    $test_dbd,
                    {
                       PrintError => 0,
                       RaiseError => 0,
                       f_dir      => $testdir,
                       %extra_args,
                    }
                  );

    my $vsql = "SELECT * FROM multi_fruit ORDER BY dKey DESC";
    my $vsth = $dbh->prepare($vsql);
    ok($vsth, "prepare <$vsql> using '$test_dbd'") or diag($dbh->errstr || 'unknown error');

    # evil hack to avoid full dbi emulating in TestLib
    my %store;
    defined $dbh->{stmt} and $store{stmt} = $dbh->{stmt};
    defined $dbh->{sth} and $store{sth} = $dbh->{sth};

    # basic tests taken from DBD::DBM simple tests - should work overall
    my @tests = (
    	"DROP TABLE IF EXISTS multi_fruit", -1,
	"CREATE $temp TABLE multi_fruit (dKey INT, dVal VARCHAR(10), qux INT)", '0E0',
	"INSERT INTO  multi_fruit VALUES (1,'oranges'  , 11 )", 1,
	"INSERT INTO  multi_fruit VALUES (2,'to_change',  0 )", 1,
	"INSERT INTO  multi_fruit VALUES (3, NULL      , 13 )", 1,
	"INSERT INTO  multi_fruit VALUES (4,'to_delete', 14 )", 1,
	undef, [
	    [ 4, 'to_delete', 14 ],
	    [ 3, undef, 13 ],
	    [ 2, 'to_change', 0 ],
	    [ 1, 'oranges', 11 ],
	],
	"INSERT INTO  multi_fruit VALUES (?,?,?); #5,via placeholders,15", 1,
	undef, [
	    [ 5, 'via placeholders', 15 ],
	    [ 4, 'to_delete', 14 ],
	    [ 3, undef, 13 ],
	    [ 2, 'to_change', 0 ],
	    [ 1, 'oranges', 11 ],
	],
	"INSERT INTO  multi_fruit VALUES (6,'to_delete', 16 )", 1,
	"INSERT INTO  multi_fruit VALUES (7,'to delete', 17 )", 1,
	"INSERT INTO  multi_fruit VALUES (8,'to remove', 18 )", 1,
	"UPDATE multi_fruit SET dVal='apples', qux='12' WHERE dKey=2", 1,
	undef, [
	    [ 8, 'to remove', 18 ],
	    [ 7, 'to delete', 17 ],
	    [ 6, 'to_delete', 16 ],
	    [ 5, 'via placeholders', 15 ],
	    [ 4, 'to_delete', 14 ],
	    [ 3, undef, 13 ],
	    [ 2, 'apples', 12 ],
	    [ 1, 'oranges', 11 ],
	],
	"DELETE FROM  multi_fruit WHERE dVal='to_delete'", 2,
	"DELETE FROM  multi_fruit WHERE qux=17", 1,
	"DELETE FROM  multi_fruit WHERE dKey=8", 1,
	undef, [
	    [ 5, 'via placeholders', 15 ],
	    [ 3, undef, 13 ],
	    [ 2, 'apples', 12 ],
	    [ 1, 'oranges', 11 ],
	],
	"DELETE FROM multi_fruit", 4,
	"SELECT COUNT(*) FROM multi_fruit", [ [ 0 ] ],
	"DROP TABLE multi_fruit", -1,
    );

    SKIP:
    for my $idx ( 0 .. $#tests ) {
	$idx % 2 and next;
	my $sql = $tests[$idx];
	my $result = $tests[$idx+1];
	my ($comment, $sth, @bind);

        if( defined $sql )
	{
	    $sql =~ s/;$//;
	    $sql =~ s/\s*;\s*(?:#(.*))//;
	    $comment = $1;
	    $comment and @bind = split /,/, $comment;

	    $sth = $dbh->prepare($sql);
	    ok($sth, "prepare <$sql> using '$test_dbd'") or diag($dbh->errstr || 'unknown error');
	}
	else
	{
	    $sql = $vsql;
	    $sth = $vsth;
	    $comment = undef;
	    # evil hack to avoid full dbi emulating in TestLib
	    defined $store{stmt} and $dbh->{stmt} = $store{stmt};
	    defined $store{sth} and $dbh->{sth} = $store{sth};
	}

        # if execute errors we will handle it, not PrintError:
        my $n = $sth->execute(@bind);
        ok($n, "execute <$sql> using '$test_dbd'") or diag($sth->errstr || 'unknown error');
        next if (!defined($n));

	is( $n, $result, "execute($sql) == $result using '$test_dbd'") unless( 'ARRAY' eq ref $result );
	TODO: {
	    local $TODO = "AUTOPROXY drivers might throw away sth->rows()" if($ENV{DBI_AUTOPROXY});
	    is( $n, $sth->rows(), "\$sth->execute($sql) == \$sth->rows using $test_dbd") if( $sql =~ m/^(?:UPDATE|DELETE)/ );
	}
        next unless $sql =~ /SELECT/;
	my $allrows = $sth->fetch_rows();
	my $expected_rows = $result;
	is( $sth->rows, scalar( @{$expected_rows} ), "execute <$sql> == " . scalar( @{$expected_rows} ) . " using '$test_dbd'" );
	is_deeply( $allrows, $expected_rows, "SELECT results for $sql using $test_dbd" );

	# run SELECT 2nd time to test bug from RT#81523
	$sth->finish();
        $n = $sth->execute(@bind);
        ok($n, "execute <$sql> using '$test_dbd' 2nd time") or diag($sth->errstr || 'unknown error');

	is( $n, $result, "execute($sql) == $result using '$test_dbd'") unless( 'ARRAY' eq ref $result );
	$allrows = $sth->fetch_rows();
	$expected_rows = $result;
	is( $sth->rows, scalar( @{$expected_rows} ), "execute <$sql> == " . scalar( @{$expected_rows} ) . " using '$test_dbd'" );
	is_deeply( $allrows, $expected_rows, "SELECT results for $sql using '$test_dbd' 2nd time" );
    }

    my $i_sql = "INSERT INTO  test_tbl VALUES (?,?)";
    my $s_sql = "SELECT dKey, dVal FROM test_tbl WHERE dKey=?";

    my @rows = (
	[ 1, "Perl" ],
	[ 2, "DBI" ],
	[ 3, "SQL::Statement" ],
	[ 4, "DBD::File" ],
	[ 5, "DBD::CSV" ],
	[ 6, "DBD::DBM" ],
	[ 7, "DBD::ODBC" ],
	[ 8, "DBD::SQLite" ],
    );

    my @sqls = (
    	"DROP TABLE IF EXISTS test_tbl", -1,
	"CREATE $temp TABLE test_tbl (dKey INT, dVal VARCHAR(23))", '0E0',
	( $i_sql, 1,
	  $s_sql, 1, ) x scalar(@rows),
	"DROP TABLE test_tbl", -1,
    );

    my %prepared;
    foreach my $sql ($i_sql, $s_sql)
    {
	my $sth = $dbh->prepare($sql);
	ok($sth, "prepare <$sql> using '$test_dbd'") or diag($dbh->errstr || 'unknown error');

	# evil hack to avoid full dbi emulating in TestLib
	my %store;
	defined $dbh->{stmt} and $store{stmt} = $dbh->{stmt};
	defined $dbh->{sth} and $store{sth} = $dbh->{sth};

	$prepared{$sql} = \%store;
    }

    my $row_idx = 0;

    SKIP:
    for my $idx ( 0 .. $#sqls ) {
	$idx % 2 and next;
	my $sql = $sqls[$idx];
	my $result = $sqls[$idx+1];
	my ($sth, @bind);

        if( defined $prepared{$sql} )
	{
	    $sth = $dbh; # evil hack in TestLib - $sth == $dbh (wrapper classes!)
	    # evil hack to avoid full dbi emulating in TestLib
	    defined $prepared{$sql}{stmt} and $dbh->{stmt} = $prepared{$sql}{stmt};
	    defined $prepared{$sql}{sth} and $dbh->{sth} = $prepared{$sql}{sth};

	    $sth->command() eq "SELECT" and $result = [$rows[$row_idx++]] and @bind = ($result->[0][0]);
	    $sth->command() eq "INSERT" and $result = 1 and @bind = @{$rows[$row_idx]};

	    $sql .= " [" . join(", ", @bind) . "]";
	}
	else
	{
	    $sql =~ s/;$//;
	    $sql =~ s/\s*;\s*(?:#(.*))//;
	    my $comment = $1;
	    $comment and @bind = split /,/, $comment;

	    $sth = $dbh->prepare($sql);
	    ok($sth, "prepare <$sql> using '$test_dbd'") or diag($dbh->errstr || 'unknown error');
	}

        # if execute errors we will handle it, not PrintError:
        my $n = $sth->execute(@bind);
        ok($n, "execute <$sql> using '$test_dbd'") or diag($sth->errstr || 'unknown error');
        next if (!defined($n));

	is( $n, $result, "execute($sql) == $result using '$test_dbd'") unless( 'ARRAY' eq ref $result );
        next unless $sql =~ /SELECT/;
	my $allrows = $sth->fetch_rows();
	my $expected_rows = $result;
	is( $sth->rows, scalar( @{$expected_rows} ), "execute <$sql> == " . scalar( @{$expected_rows} ) . " using '$test_dbd'" );
	is_deeply( $allrows, $expected_rows, "SELECT results for $sql using $test_dbd" );

	# run SELECT 2nd time to test bug from RT#81523
	$sth->finish();
        $n = $sth->execute(@bind);
        ok($n, "execute <$sql> using '$test_dbd' 2nd time") or diag($sth->errstr || 'unknown error');

	is( $n, $result, "execute($sql) == $result using '$test_dbd'") unless( 'ARRAY' eq ref $result );
	$allrows = $sth->fetch_rows();
	$expected_rows = $result;
	is( $sth->rows, scalar( @{$expected_rows} ), "execute <$sql> == " . scalar( @{$expected_rows} ) . " using '$test_dbd'" );
	is_deeply( $allrows, $expected_rows, "SELECT results for $sql using '$test_dbd' 2nd time" );
    }
}

done_testing();