#!/usr/bin/perl
use Test::More;
use lib qw(t);
eval "use DBD::SQLite";
plan skip_all => "DBD::SQLite required for this test" if $@;

plan tests => 90;

use strict;
use warnings;

our $DBNAME = 't/sqlite.db';

unlink $DBNAME if -e $DBNAME;
my $dbh = DBI->connect( "dbi:SQLite:dbname=$DBNAME", "", "" );

$dbh->do(<<"");
CREATE TABLE user (
    name VARCHAR(20),
    password VARCHAR(50)
)

$dbh->do(<<"");
INSERT INTO user VALUES ('user1', '123');

$dbh->do(<<"");
INSERT INTO user VALUES ('user2', 'mQPVY1HNg8SJ2');  # crypt("123", "mQ")


{

    package TestAppDriverDBISimple;

    use base qw(TestAppDriver);

    __PACKAGE__->authen->config(
        DRIVER => [
            [
                'DBI',
                DBH         => $dbh,
                TABLE       => 'user',
                CONSTRAINTS => { 'user.name' => '__CREDENTIAL_1__', 'user.password' => '__CREDENTIAL_2__' },
            ],
            [
                'DBI',
                DBH         => $dbh,
                TABLES      => 'user',
                COLUMNS     => { 'crypt:user.password' => '__CREDENTIAL_2__' },
                CONSTRAINTS => { 'user.name' => '__CREDENTIAL_1__' },
            ],
        ],
        STORE => 'Store::Dummy',
    );

}

TestAppDriverDBISimple->run_authen_tests(
    [ 'authen_username', 'authen_password' ],
    [ 'user1', '123' ],
    [ 'user2', '123' ],
);

$dbh->do(<<"");
DROP TABLE user;



#
# MULTIPLE TABLES
#
$dbh->do(<<"");
CREATE TABLE domain (
    id INTEGER,
    name VARCHAR(20)
);

$dbh->do(<<"");
CREATE TABLE user (
    id INTEGER,
    domainid INTEGER,
    name VARCHAR(20),
    password VARCHAR(50)
)

$dbh->do(<<"");
INSERT INTO domain VALUES (1, 'domain1');

$dbh->do(<<"");
INSERT INTO domain VALUES (2, 'domain2');

$dbh->do(<<"");
INSERT INTO user VALUES (1, 1, 'user1', '123');

$dbh->do(<<"");
INSERT INTO user VALUES (2, 2, 'user1', '234');

$dbh->do(<<"");
INSERT INTO user VALUES (3, 1, 'user2', '345');

$dbh->do(<<"");
INSERT INTO user VALUES (4, 1, 'user3', 'mQPVY1HNg8SJ2');  # crypt("123", "mQ")


{

    package TestAppDriverDBIMultiTable;

    use base qw(TestAppDriver);

    __PACKAGE__->authen->config(
        DRIVER => [
            [
                'DBI',
                DBH         => $dbh,
                TABLES      => [ 'user', 'domain' ],
                JOIN_ON     => 'user.domainid = domain.id',
                CONSTRAINTS => { 'user.name' => '__CREDENTIAL_1__', 'user.password' => '__CREDENTIAL_2__', 'domain.name' => '__CREDENTIAL_3__' }
            ],
            [
                'DBI',
                DBH         => $dbh,
                TABLES      => [ 'user', 'domain' ],
                JOIN_ON     => 'user.domainid = domain.id',
                COLUMNS     => { 'user.password' => '__CREDENTIAL_3__', 'domain.name' => '__CREDENTIAL_2__' },
                CONSTRAINTS => { 'user.name' => '__CREDENTIAL_1__' }
            ],
        ],
        STORE       => 'Store::Dummy',
        CREDENTIALS => [qw(username password domain)],
    );

}

TestAppDriverDBIMultiTable->run_authen_tests(
    [ 'username', 'password', 'domain' ],
    [ 'user1', '123', 'domain1' ],
    [ 'user1', '234', 'domain2' ],
    [ 'user1', 'domain1', '123' ],
    [ 'user1', 'domain2', '234' ],
);

$dbh->do(<<"");
DROP TABLE domain;

$dbh->do(<<"");
DROP TABLE user;





#
# ENCODED FIELDS
#
$dbh->do(<<"");
CREATE TABLE user (
    name VARCHAR(20),
    password VARCHAR(50)
)

$dbh->do(<<"");
INSERT INTO user VALUES ('user1', 'mQPVY1HNg8SJ2');  # crypt("123", "mQ")

$dbh->do(<<"");
INSERT INTO user VALUES ('user2', '202cb962ac59075b964b07152d234b70');  # md5_hex("123")


{

    package TestAppDriverDBIEncode;

    use base qw(TestAppDriver);

    __PACKAGE__->authen->config(
        DRIVER => [
            [
                'DBI',
                DBH         => $dbh,
                TABLE       => 'user',
                COLUMNS     => { 'crypt:password' => '__CREDENTIAL_2__' },
                CONSTRAINTS => { 'user.name' => '__CREDENTIAL_1__' }
            ],
            [
                'DBI',
                DBH         => $dbh,
                TABLE       => 'user',
                CONSTRAINTS => { 'user.name' => '__CREDENTIAL_1__', 'MD5:password' => '__CREDENTIAL_2__' }
            ],
        ],
        STORE       => 'Store::Dummy',
        CREDENTIALS => [qw(username password)],
    );

}

TestAppDriverDBIEncode->run_authen_tests(
    [ 'username', 'password' ],
    [ 'user1', '123' ],
    [ 'user2', '123' ],
);

$dbh->do(<<"");
DROP TABLE user;




#
# ENCODED FIELDS
#
$dbh->do(<<"");
CREATE TABLE user (
    name VARCHAR(20),
    password VARCHAR(50),
    active INTEGER
)

$dbh->do(<<"");
INSERT INTO user VALUES ('user1', '123', 1);

$dbh->do(<<"");
INSERT INTO user VALUES ('user2', '123', 0);


{

    package TestAppDriverDBIEncode;

    use base qw(TestAppDriver);

    __PACKAGE__->authen->config(
        DRIVER => [ 'DBI',
            DBH         => $dbh,
            TABLE       => 'user',
            CONSTRAINTS => {
                'user.name'     => '__CREDENTIAL_1__',
                'user.password' => '__CREDENTIAL_2__',
                'active'        => '1'
            },
        ],
        STORE       => 'Store::Dummy',
        CREDENTIALS => [qw(username password)],
    );

}

TestAppDriverDBIEncode->run_authen_tests(
    [ 'username', 'password' ],
    [ 'user1', '123' ],
);

TestAppDriverDBIEncode->run_authen_failure_tests(
    [ 'username', 'password' ],
    [ 'user2', '123' ],
);

$dbh->do(<<"");
DROP TABLE user;


#
# ORDER BY
#
$dbh->do(<<"");
CREATE TABLE user (
    id INTEGER,
    name VARCHAR(20),
    password VARCHAR(50),
    created TIMESTAMP
)

$dbh->do(<<"");
INSERT INTO user VALUES (1, 'user1', '123', '2009-01-01');

$dbh->do(<<"");
INSERT INTO user VALUES (2, 'user2', '123', '2009-01-01');

$dbh->do(<<"");
INSERT INTO user VALUES (3, 'user1', '321', '2009-01-02');

$dbh->do(<<"");
INSERT INTO user VALUES (4, 'user2', '321', '2009-01-02');


{

    package TestAppDriverDBIEncode;

    use base qw(TestAppDriver);

    __PACKAGE__->authen->config(
        DRIVER => [ 'DBI',
            DBH         => $dbh,
            TABLE       => 'user',
            COLUMNS     => {
                'user.password' => '__CREDENTIAL_2__'
            },
            CONSTRAINTS => {
                'user.name'     => '__CREDENTIAL_1__',
            },
            ORDER_BY    => 'created DESC',
            LIMIT       => 1,
        ],
        STORE       => 'Store::Dummy',
        CREDENTIALS => [qw(username password)],
    );

}

TestAppDriverDBIEncode->run_authen_tests(
    [ 'username', 'password' ],
    [ 'user1', '321' ],
    [ 'user2', '321' ],
);

TestAppDriverDBIEncode->run_authen_failure_tests(
    [ 'username', 'password' ],
    [ 'user1', '123' ],
    [ 'user2', '123' ],
);


$dbh->do(<<"");
DROP TABLE user;



#
# ALL TOGETHER
#
$dbh->do(<<"");
CREATE TABLE user (
    id INTEGER,
    name VARCHAR(20),
    password VARCHAR(50)
)

$dbh->do(<<"");
CREATE TABLE dailycode (
    id INTEGER,
    userid INTEGER,
    date DATE DEFAULT 'CURRENT_DATE',
    code VARCHAR(20)
);

$dbh->do(<<"");
INSERT INTO user VALUES (1, 'user1', 'mQPVY1HNg8SJ2');  # crypt("123", "mQ")

$dbh->do(<<"");
INSERT INTO user VALUES (2, 'user2', 'mQPVY1HNg8SJ2');  # crypt("123", "mQ")

$dbh->do(<<"");
INSERT INTO dailycode VALUES (1, 1, 'CURRENT_DATE', '202CB962AC59075B964B07152D234B70');  # uc(md5_hex("123"))

$dbh->do(<<"");
INSERT INTO dailycode VALUES (2, 2, '2000-01-01', '202CB962AC59075B964B07152D234B70');  # uc(md5_hex("123"))


{

    package TestAppDriverDBIEncode;

    use base qw(TestAppDriver);

    __PACKAGE__->authen->config(
        DRIVER => [ 'DBI',
            DBH         => $dbh,
            TABLES      => ['user U', 'dailycode D'],
            JOIN_ON     => 'U.id = D.userid',
            COLUMNS     => {
                'crypt:U.password' => '__CREDENTIAL_2__'
            },
            CONSTRAINTS => {
                'U.name'  => '__CREDENTIAL_1__',
                'uc:MD5_hex:D.code' => '__CREDENTIAL_3__',
                'D.date'     => 'CURRENT_DATE'
            },
        ],
        STORE       => 'Store::Dummy',
        CREDENTIALS => [qw(username password dailycode)],
    );

}

TestAppDriverDBIEncode->run_authen_tests(
    [ 'username', 'password', 'dailycode' ],
    [ 'user1', '123', '123' ],
);

TestAppDriverDBIEncode->run_authen_failure_tests(
    [ 'username', 'password', 'dailycode' ],
    [ 'user1', '123', 'xxx' ],
    [ 'user1', 'xxx', '123' ],
    [ 'user2', '123', '123' ],
);

$dbh->do(<<"");
DROP TABLE user;

$dbh->do(<<"");
DROP TABLE dailycode;



$dbh->do(<<"");
CREATE TABLE user (
    name VARCHAR(20),
    password VARCHAR(50)
)

$dbh->do(<<"");
INSERT INTO user VALUES ('user1', '123');



SKIP: {
    eval "use DBI;";
    skip "DBI not available", 6 if $@;

    {

        package TestAppDriverDBIDBH;

        use base qw(TestAppDriver);

        use Test::More;
        eval "use CGI::Application::Plugin::DBH (qw/dbh dbh_config/);";
        skip "CGI::Application::Plugin::DBH not available", 6 if $@;

        sub cgiapp_init {
            my $self = shift;
            $self->dbh_config($dbh);

            $self->authen->config(
                DRIVER => [
                    'DBI',
                    TABLE       => 'user',
                    CONSTRAINTS => { 'user.name' => '__CREDENTIAL_1__', 'user.password' => '__CREDENTIAL_2__' },
                ],
                STORE => 'Store::Dummy',
                CREDENTIALS => [qw(authen_username authen_password)],
            );

        }

    }

    TestAppDriverDBIDBH->run_authen_tests(
        [ 'authen_username', 'authen_password' ],
        [ 'user1', '123' ],
    );

}

$dbh->do(<<"");
DROP TABLE user;


undef $dbh;

unlink $DBNAME if -e $DBNAME;