use strict;
use warnings;
use xt::Utils::mysql;
use Mock::Basic;
use Test::More;

foreach my $plugin (qw(MySQLFoundRows Count CountOrMySQLFoundRows)) {
        Mock::Basic->load_plugin('SearchBySQLAbstractMore::Pager::' . $plugin);

        my $dbh = t::Utils->setup_dbh();
        $dbh->do('DROP TABLE IF EXISTS mock_basic');
        $dbh->do('DROP TABLE IF EXISTS mock_basic2');

        my $db = Mock::Basic->new({dbh => $dbh});
        $db->setup_test_db;

        $db->insert('mock_basic',{
            id   => 1,
            name => 'perl',
        });
        $db->insert('mock_basic',{
            id   => 2,
            name => 'python',
        });
        $db->insert('mock_basic',{
            id   => 3,
            name => 'java',
        });

        $db->insert('mock_basic2',{
            id   => 1,
            mock_basic_id => 1,
            name => 'perl2',
        });
        $db->insert('mock_basic2',{
            id   => 2,
            mock_basic_id => 2,
            name => 'python2',
        });
        $db->insert('mock_basic2',{
            id   => 3,
            mock_basic_id => 3,
            name => 'java2',
        });

        subtest 'search_with_having' => sub {
            my ($rows, $pager) = $db->search_by_sql_abstract_more_with_pager('mock_basic',{
            }, {
                -columns  => ['id % 2 as id_mod'],
                -group_by => ['id % 2'],
                -having   => [{id_mod => { '<' => '1' }}],
                -order_by => ['id_mod'],
                -page     => 1,
                -rows     => 2,
               });
            is ref $pager, 'Data::Page';
            is scalar @$rows, 1, 'total_entries';

            my $row = $rows->[0];
            isa_ok $row, 'Teng::Row';

            is $row->get_column('id_mod'), '0';

            is $pager->current_page, 1;
            is $pager->total_entries, 1;
            is $pager->first, 1;
            is $pager->last, 1;

        };

        subtest 'search_with_group_by' => sub {
            my ($rows, $pager) = $db->search_by_sql_abstract_more_with_pager('mock_basic',{
            }, {
                -columns => ['id % 2 as id_mod'],
                -from => ['mock_basic'],
                -group_by => ['id % 2'],
                -order_by => ['id_mod'],
                -page     => 1,
                -rows     => 2,
               });

            is ref $pager, 'Data::Page';
            is scalar @$rows, 2, 'total_entries';

            my $row = $rows->[0];
            isa_ok $row, 'Teng::Row';

            is $row->get_column('id_mod'), '0';

            my $row2 = $rows->[1];
            isa_ok $row2, 'Teng::Row';

            is $row2->get_column('id_mod'), '1';

            is $pager->current_page, 1;
            is $pager->total_entries, 2;
            is $pager->first, 1;
            is $pager->last, 2;

        };

        subtest 'search with join' => sub {
            my ($rows, $pager) = $db->search_by_sql_abstract_more_with_pager('mock_basic',{
            }, {
                -columns => ['a.id', 'b.name'],
                -from => [-join =>
                          'mock_basic|a',
                          'a.id=b.mock_basic_id',
                          'mock_basic2|b',
                         ],
                -order_by => ['a.id'],
                -page     => 1,
                -rows     => 2,
               });

            is ref $pager, 'Data::Page';
            is scalar @$rows, 2, 'total_entries';

            my $row = $rows->[0];
            isa_ok $row, 'Teng::Row';

            is $row->id, 1;
            is $row->name, 'perl2';

            my $row2 = $rows->[1];
            isa_ok $row2, 'Teng::Row';

            is $row2->id, 2;
            is $row2->name, 'python2';

            is $pager->current_page, 1;
            is $pager->first, 1;
            is $pager->last, 2;
        };

        subtest 'search original with join' => sub {
            my ($rows, $pager) = $db->search_by_sql_abstract_more_with_pager
              ({
                -where => {},
                -columns => ['a.id', 'b.name'],
                -from => [-join =>
                          'mock_basic|a',
                          'a.id=b.mock_basic_id',
                          'mock_basic2|b',
                         ],
                -order_by => ['a.id'],
                -page     => 2,
                -rows     => 2,
               });

            is ref $pager, 'Data::Page';
            is scalar @$rows, 1, 'total_entries';

            my $row = $rows->[0];
            isa_ok $row, 'Teng::Row', 'row object';

            is $row->id, 3;
            is $row->name, 'java2', 'column value';

            is $pager->first, 3, 'first page';
            is $pager->last, 3, 'last page';
        };
}

subtest 'search with join_and_hint_columns' => sub {
    my ($rows, $pager) = $db->search_by_sql_abstract_more_with_pager('mock_basic',{
    }, {
        -hint_columns => ['a.id'],
        -columns => ['a.id', 'b.name'],
        -from => [-join =>
                  'mock_basic|a',
                  'a.id=b.mock_basic_id',
                  'mock_basic2|b',
                 ],
        -order_by => ['a.id'],
        -page     => 1,
        -rows     => 2,
       });

    is ref $pager, 'Data::Page';
    is scalar @$rows, 2, 'total_entries';

    my $row = $rows->[0];
    isa_ok $row, 'Teng::Row';

    is $row->id, 1;
    is $row->name, 'perl2';

    my $row2 = $rows->[1];
    isa_ok $row2, 'Teng::Row';

    is $row2->id, 2;
    is $row2->name, 'python2';

    is $pager->current_page, 1;
    is $pager->first, 1;
    is $pager->last, 2;
};

subtest 'search original with join_and_hint_columns' => sub {
    my ($rows, $pager) = $db->search_by_sql_abstract_more_with_pager
      ({
        -where => {},
        -hint_columns => ['a.id'],
        -columns => ['a.id', 'b.name'],
        -from => [-join =>
                  'mock_basic|a',
                  'a.id=b.mock_basic_id',
                  'mock_basic2|b',
                 ],
        -order_by => ['a.id'],
        -page     => 2,
        -rows     => 2,
       });

    is ref $pager, 'Data::Page';
    is scalar @$rows, 1, 'total_entries';

    my $row = $rows->[0];
    isa_ok $row, 'Teng::Row';

    is $row->id, 3;
    is $row->name, 'java2';

    is $pager->first, 3;
    is $pager->last, 3;
};

done_testing;