#!/usr/bin/perl -w

####################################################################################################

use strict;
use CGI;
use DBI;
use Digest::MD5 qw(md5 md5_hex);

####################################################################################################

BEGIN
{
    use FindBin;
    use lib "$FindBin::Bin/../lib";

    use Env;
    Env::setProjectRoot("$FindBin::Bin/..");
}

####################################################################################################

use Log qw(logAMessage
       );

use DBLib qw(databaseConnect
         databaseDisconnect
         );

use HTML_Templates qw(replaceTagsInTemplate);

####################################################################################################

my $dbUserName = &Env::getVariable('DataBase','username');
my $dbPassword = &Env::getVariable('DataBase','password');
my $templatePath = Env::getVariable("Paths", "templates");

my $query = new CGI;

my %tagsToFill;
my $nextPage;

my $errors = qq();

my $c; # cookie

# extract from the query
my $theUsername = $query->param("username");
my $thePassword = $query->param("password");

# errors
if ( !defined($theUsername) || $theUsername eq qq())
{
    $errors .= qq(Username left blank.<br />);
}
if ( !defined($thePassword) || $thePassword eq qq())
{
    $errors .= qq(Password left blank.<br />);
}

# if no errors, check db
if ( $errors eq qq() )
{
    my $dbh = &databaseConnect($dbUserName, $dbPassword, $dbUserName);

    # check username
    my $usernameSQL = qq(select count(*) from people where username = ?);
    my $sth = $dbh->prepare($usernameSQL);
    $sth->execute($theUsername);
    my $usernameCount;
    $sth->bind_columns(\$usernameCount);
    $sth->fetch();
    $sth->finish();

    if ( $usernameCount < 1 )
    {
        $errors .= qq(Username/Password combination invalid. (username not found**)<br />);
    }

   # if there are still no errors (username exists)
    if ( $errors eq qq() )
    {
    # grab hashed password from db
    my $sql = qq(select password from people where username = ?);
    my $sth2 = $dbh->prepare($sql);
    $sth2->execute($theUsername);

    my $vvPass; # create var to store hashed password
    $sth2->bind_columns(\$vvPass); # insert into var
    $sth2->fetch();
    $sth2->finish();

    # check password
    if ($vvPass eq md5_hex($thePassword))
    {
        # check login table for blocked, then log them in (or not)
        my $sqlBlocked = qq(select blocked from logins where username = ?);
        my $sthBlocked = $dbh->prepare($sqlBlocked);
        $sthBlocked->execute($theUsername); # replace question mark by this, which is sanitized

        my $vvBlocked;
        $sthBlocked->bind_columns(\$vvBlocked);
        $sthBlocked->fetch();
        $sthBlocked->finish();

        if ($vvBlocked eq 1)
        {
        $errors .= qq(Account blocked, contact your administrator for further assistance.<br />);
        }

    }
    else # else : unsuccessful password match
    {
        $errors .= qq(Username/Password combination invalid. (password doesnt match**)<br />);

        # increment unsuccessful login count in db

        my $sqlUnsuccess = qq(update logins set unsuccessful_logins = unsuccessful_logins+1 where username = ?);
            my $sthUnsuccess = $dbh->prepare($sqlUnsuccess);
            $sthUnsuccess->execute($theUsername);
            $sthUnsuccess->finish();

        # if that count reaches 5, disable account (block)
        my $sqlAttempts = qq(select unsuccessful_logins from logins where username = ?);
        my $sthAttempts = $dbh->prepare($sqlAttempts);
        $sthAttempts->execute($theUsername);

        my $vvAttempts;
        $sthAttempts->bind_columns(\$vvAttempts);
        $sthAttempts->fetch();
        $sthAttempts->finish();

        if ($vvAttempts > 4)
        {
        $errors .= qq(Max Attempts reached. Contact Administrator.<br />);

        my $sqlAccBlocked = qq(update logins set blocked='t' where username = ?);
        my $sthAccBlocked = $dbh->prepare($sqlAccBlocked);
        $sthAccBlocked->execute($theUsername);
        $sthAccBlocked->finish();
        }

    }

        # if still no errors, log them in
    if ( $errors eq qq() ) {

        # select first name, last name, last login date
            my $sqlFinal = qq(select first_name, last_name, last_login from people join logins on people.username = logins.username where people.username = ?);
            my $sthFinal = $dbh->prepare($sqlFinal);
        $sthFinal->execute($theUsername);

        my ($vvFirst, $vvLast, $vvLastLogin);
            $sthFinal->bind_columns(\$vvFirst,\$vvLast,\$vvLastLogin);
            $sthFinal->fetch();
            $sthFinal->finish();

        # update logins table
            my $sqlSuccess = qq(update logins set unsuccessful_logins = 0, successful_logins = successful_logins+1, last_login = CURRENT_TIMESTAMP(0) where username = ?);
            my $sthSuccess = $dbh->prepare($sqlSuccess);
            $sthSuccess->execute($theUsername);
            $sthSuccess->finish();

        if ($vvLastLogin eq qq())
        {
        $vvLastLogin = qq(never);
        }

        # prep data for template
        $tagsToFill{"FIRSTNAME"} = ucfirst($vvFirst);
        $tagsToFill{"LASTNAME"} = ucfirst($vvLast);
        $tagsToFill{"LASTLOGIN"} = $vvLastLogin;

        $nextPage = qq(loggedin.html);

        $c = $query->cookie(-name => 'login',
                -value => $theUsername,
                -domain => 'linux-cs.johnabbott.qc.ca',
                -path => '/',
                -expires => '+60M');
    }
    }

    $dbh->commit();
    &databaseDisconnect($dbh);
}


if ( $errors ne qq() )
{
    $tagsToFill{"ERRORS"} = $errors;
    $nextPage = qq(create_error.html);
}

logAMessage( qq(<<$errors>>) );

my $thePage = replaceTagsInTemplate(qq($templatePath/$nextPage), \%tagsToFill);

print $query->header(-cookie=>$c);
print $thePage;