#!/usr/bin/perl -I.
# vim:set tabstop=2 shiftwidth=2 expandtab filetype=perl:
use strict;
use warnings;

use Test::More tests => 21;
use SQL::Abstract::Test import => [qw( is_same_sql is_same_bind )];
{
  no warnings 'once';
  $SQL::Abstract::Test::parenthesis_significant = ''; # false
}

$main::sql = "";

sub set_sql
{
  my ( $class, $name, $sql ) = @_;
  no strict 'refs';
  *{ "$class\::sql_$name" } =
    sub
    {
      my ( $class, $where ) = @_;
      ( $main::sql = sprintf $sql, $where ) =~ s/^\s+//mg;
      return $class;
    };
}

sub retrieve_from_sql {} # Make plugin believe we're inheriting from Class::DBI

sub select_val
{
  shift;
  return @_;
}

sub columns { return qw( artist title release updated ) }

sub _croak
{
  shift;
  die ": _croak(): '@_'\n";
}

# If we can't be free, at least we can be cheap...
{
  package artist;
  sub accessor { return 'artist_name' }
}
{
  package title;
  sub accessor { return 'album_title' }
}
{
  package release;
  sub accessor { return 'release_date' }
}
{
  package updated;
  sub accessor { return 'last_change' }
}

use_ok('Class::DBI::Plugin::AbstractCount');

# Test simple where-clause
my @bind_params = __PACKAGE__->count_search_where({
  artist => 'Frank Zappa'
});
is_same_sql(
  $main::sql,
  'SELECT COUNT(*) FROM __TABLE__ WHERE ( artist = ? )',
  'sql statement 1',
);
is_same_bind( \@bind_params, [ 'Frank Zappa' ], 'bind param list 1' );

# Test more complex where-clause
@bind_params = __PACKAGE__->count_search_where({
  artist  => 'Frank Zappa',
  title   => { like => '%Shut Up \'n Play Yer Guitar%' },
  release => { between => [ 1980, 1982 ] },
});
is_same_sql(
  $main::sql, q{
    SELECT COUNT(*)
    FROM __TABLE__
    WHERE ( ( artist = ? AND ( release BETWEEN ? AND ? ) AND title LIKE ? ) )
  },
  'sql statement 2',
);
is_same_bind(
  \@bind_params, [
    'Frank Zappa',
    '1980',
    '1982',
    '%Shut Up \'n Play Yer Guitar%',
  ],
  'bind param list 2',
);

# Test where-clause with accessors
@bind_params = __PACKAGE__->count_search_where({
  artist_name  => 'Steve Vai',
  album_title  => { like => 'Flexable%' },
  release_date => { between => [ 1983, 1984 ] },
});
is_same_sql(
  $main::sql, q{
    SELECT COUNT(*)
    FROM __TABLE__
    WHERE ( ( artist = ? AND ( release BETWEEN ? AND ? ) AND title LIKE ? ) )
  },
  'sql statement 3',
);
is_same_bind(
  \@bind_params, [
    'Steve Vai',
    '1983',
    '1984',
    'Flexable%',
  ],
  'bind param list 3',
);

# Test where-clause with simple function-call on column name
@bind_params = __PACKAGE__->count_search_where({
  artist            => 'Adrian Belew',
  'YEAR( release )' => { '=', 2005 },
});
is_same_sql(
  $main::sql, q{
    SELECT COUNT(*)
    FROM __TABLE__
    WHERE ( ( YEAR( release ) = ? AND artist = ? ) )
  },
  'sql statement 4',
);
is_same_bind(
  \@bind_params, [
    '2005',
    'Adrian Belew'
  ],
  'bind param list 4',
);

# Test where-clause with more complex (nested) function-call on column name
@bind_params = __PACKAGE__->count_search_where({
  artist                       => 'Adrian Belew',
  'COALESCE( release, NOW() )' => { '=', 2005 },
});
is_same_sql(
  $main::sql, q{
    SELECT COUNT(*)
    FROM __TABLE__
    WHERE ( ( COALESCE( release, NOW() ) = ? AND artist = ? ) )
  },
  'sql statement 5',
);
is_same_bind(
  \@bind_params, [
    '2005',
    'Adrian Belew'
  ],
  'bind param list 5',
);

# Test where-clause with simple function-call on accessor
@bind_params = __PACKAGE__->count_search_where({
  artist_name            => 'Adrian Belew',
  'YEAR( release_date )' => { '=', 2005 },
});
is_same_sql(
  $main::sql, q{
    SELECT COUNT(*)
    FROM __TABLE__
    WHERE ( ( YEAR( release ) = ? AND artist = ? ) )
  },
  'sql statement 6',
);
is_same_bind(
  \@bind_params, [
    '2005',
    'Adrian Belew'
  ],
  'bind param list 6',
);

# Test where-clause with more complex (nested) function-call on accessor
@bind_params = __PACKAGE__->count_search_where({
  artist_name                       => 'Adrian Belew',
  'COALESCE( release_date, NOW() )' => { '=', 2005 },
});
is_same_sql(
  $main::sql, q{
    SELECT COUNT(*)
    FROM __TABLE__
    WHERE ( ( COALESCE( release, NOW() ) = ? AND artist = ? ) )
  },
  'sql statement 7',
);
is_same_bind(
  \@bind_params, [
    '2005',
    'Adrian Belew'
  ],
  'bind param list 7',
);

# Test where-clause with more complex (nested) function-call on multiple
# column names
@bind_params = __PACKAGE__->count_search_where({
  artist                                => 'Adrian Belew',
  'COALESCE( release, updated, NOW() )' => { '=', 2005 },
});
is_same_sql(
  $main::sql, q{
    SELECT COUNT(*)
    FROM __TABLE__
    WHERE ( ( COALESCE( release, updated, NOW() ) = ? AND artist = ? ) )
  },
  'sql statement 8',
);
is_same_bind(
  \@bind_params, [
    '2005',
    'Adrian Belew'
  ],
  'bind param list 8',
);

# Test where-clause with more complex (nested) function-call on mixed
# column and accessor names
@bind_params = __PACKAGE__->count_search_where({
  artist                                    => 'Adrian Belew',
  'COALESCE( release, last_change, NOW() )' => { '=', 2005 },
});
is_same_sql(
  $main::sql, q{
    SELECT COUNT(*)
    FROM __TABLE__
    WHERE ( ( COALESCE( release, updated, NOW() ) = ? AND artist = ? ) )
  },
  'sql statement 9',
);
is_same_bind(
  \@bind_params, [
    '2005',
    'Adrian Belew'
  ],
  'bind param list 9',
);

# Test complex where-clause
@bind_params = __PACKAGE__->count_search_where(
  -and => [
    artist => 'System Of A Down',
    -nest  => [
      -and => [
        title   => { like => '%ize' },
        release => 2005,
      ],
      -and => [
        title   => { like => '%ize' },
        release => 2006,
      ],
    ],
  ],
);
is_same_sql(
  $main::sql, q{
    SELECT COUNT(*)
    FROM __TABLE__
    WHERE ( ( artist = ? AND ( ( title LIKE ? AND release = ? )
                            OR ( title LIKE ? AND release = ? ) ) ) )
  },
  'sql statement 10',
);
is_same_bind(
  \@bind_params, [
    'System Of A Down',
    '%ize',
    '2005',
    '%ize',
    '2006',
  ],
  'bind param list 10',
);

__END__