package AppUser::DB::Item::User;
use strict;
use warnings;
use lib '/app/tools/common/lib';

use Common::Assert;
use Common::RSApp;
use Common::DB::Item;
use Digest::SHA qw(sha1_hex);
use Encode qw(encode_utf8);

use base 'Common::DB::Item';

use constant kTable        => 'user';
use constant kHistoryTable => 'user_history';

use constant kUserTypeNormal    => 0;
use constant kUserTypePortal    => 1;

use constant kMaxSavedPasswords => 24;

# user.mfa_type
use constant kMFATypeNone          => 0;
use constant kMFATypeEmail         => 1;
use constant kMFATypeAuthenticator => 2;
use constant kMFATypeSMS           => 3;

use constant kDB           => Common::DB::Item::kCommonDB();

# Overriding the default config hash to make the 'password'
# column magical and special.
#
# !!! This is no longer needed because we're hashing the password
# !!! before saving it to the database with Perl's sha1_hex function since MySQL8
# !!! doesn't support the password() function anymore.
# !!! Should be removed in the future.

# sub _GenerateClassConfig {
#     my ($class) = @_;

#     my $config = $class->SUPER::_GenerateClassConfig();

#     my $colDef = $config->{password};
#     if ( !$colDef ) {
#         $config->{password} = {};
#         $colDef = $config->{password};
#     }
#     $colDef->{_function} = 'password';
#     return $config;
# }

sub _hashPassword {
    my ( $class, $password ) = @_;

    return undef unless $password;

    $password = encode_utf8($password);

    return '*' . uc(sha1_hex(pack("H*", sha1_hex($password))));
}

sub HashPassword {
    my ( $class, $password ) = @_;
    return $class->_hashPassword($password);
}

# traditionally these have been static methods, so I'm going to keep it that way
# even though the original "IsPassword" was a method of a User object.
#
# this requires a hit to the database anyway, so we might as well just pass in
# the userID and password
#
sub HasPassword {
    my ( $class, $userID, $passwordPlain ) = @_;
    return undef if ( !defined $passwordPlain || $passwordPlain eq '' );
    return undef if ( $userID !~ /^\d+$/ );

    my $dbo = Common::RSApp::GetCommonDB();

    my $passwordHashed = $class->_hashPassword($passwordPlain);
    my $sql = "SELECT * FROM " . kTable . " WHERE user_id = $userID AND password = ". $dbo->DBQuote($passwordHashed);

    my $collection = Common::DB::ItemCollection->new(
        class => "AppUser::DB::Item::User",
        query => $sql,
        dbID  => kDB,
    );

    return ( $collection->hasNext() ) ? 1 : undef;
}

sub HasPreviousPassword {
    my ( $class, $userID, $passwordPlain ) = @_;
    return undef if ( !defined $passwordPlain || $passwordPlain eq '' );
    return undef if ( $userID !~ /^\d+$/ );

    my $dbo = Common::RSApp::GetCommonDB();

    return 1 if ( $class->HasPassword( $userID, $passwordPlain ) );

    my $passwordHashed = $class->_hashPassword($passwordPlain);
    my $sql = "SELECT * FROM " . kHistoryTable . " WHERE user_id = $userID AND password = ". $dbo->DBQuote($passwordHashed);
    my $sth = $dbo->DoCmd($sql);

    return ( $sth->rows > 0 ) ? 1 : undef;
}

sub ArchivePassword {
    my ( $class, $userID, $password, $dateCreated ) = @_;
    my $dbo = Common::RSApp::GetCommonDB();

    my @badIDs; # old password ID(s) to prune
    my $sql = "SELECT id FROM " . kHistoryTable . " WHERE user_id = $userID ORDER BY id DESC";
    my $sth = $dbo->DoCmd($sql);
    my $count = 1;
    while( my($id) = $sth->fetchrow_array() ) {
        push @badIDs, $id if ( $count > (kMaxSavedPasswords-1) );
        $count++;
    }

    if ( @badIDs ) {
        my $sql = "DELETE FROM " . kHistoryTable . " WHERE user_id = $userID AND id IN (" . join(',',@badIDs) . ")";
        my $sth = $dbo->DoCmd($sql);
    }

    $sql = "INSERT INTO ". kHistoryTable . "(user_id, password, date_created) VALUES($userID,'$password','$dateCreated')";
    $sth = $dbo->DoCmd($sql);

}

sub UpdatePassword {
    my ( $class, $userID, $password ) = @_;

    my $dbo = Common::RSApp::GetCommonDB();

    my $passwordHashed = $class->_hashPassword($password);

    my $sql = "UPDATE ". kTable . " SET password = ".$dbo->DBQuote($passwordHashed)." WHERE user_id = $userID";
    my $sth = $dbo->DoCmd($sql);
}

sub GetLoginData {
    my ( $class, $userID ) = @_;
    my $dbo = Common::RSApp::GetCommonDB();
    my $sql = "SELECT DATEDIFF(NOW(),date_locked) AS days_locked, "
        . "DATEDIFF(NOW(),last_changed) AS password_age, "
        . "DATEDIFF(NOW(),last_login) AS days_login, "
        . "TIMESTAMPDIFF(MINUTE, date_locked,NOW()), "
        . "TIMESTAMPDIFF(SECOND, mfa_date_created,NOW()) "
        . "FROM " . kTable . " WHERE user_id = $userID";
    my $sth = $dbo->DoCmd($sql);
    my ( $daysLocked, $passwordAge, $daysLogin, $minutesLocked, $mfaPasswordAge ) = $sth->fetchrow_array();
    return { days_locked => $daysLocked, password_age => $passwordAge,
        days_login => $daysLogin, minutes_locked => $minutesLocked,
        mfa_password_age => $mfaPasswordAge  };
}

# IsValidPassword - check if password meets our security requirements
#
# Must not contain spaces:
#        $pw !~ /\s/ &&
#
# Must be at least 12 characters
#        length($pw) >= 12 &&
#
# No repeating characters
#        $pw !~ /(.)\1{1}/s &&
#
# Must contain three or more of the following:
#   * Lowercase letter
#   * Uppercase letter
#   * Number
#   * Special character (anything not in the previous three categories).
#        ($pw =~ /[a-z]/ + $pw =~ /[A-Z]/ + $pw =~ /[0-9]/ + $pw =~ /[^a-zA-Z0-9]/) >= 3;
#
# IMPORTANT: The front-end template should perform similar validation within the browser.
# See common/production/javascript/validatePasswordCommon.js for more information.
sub IsValidPassword {
    my ( $class, $pw ) = @_;
    return
        $pw !~ /\s/ &&
        length($pw) >= 12 &&
        $pw !~ /(.)\1{1}/s &&
        ($pw =~ /[a-z]/ + $pw =~ /[A-Z]/ + $pw =~ /[0-9]/ + $pw =~ /[^a-zA-Z0-9]/) >= 3;
}

sub GetClientUser {
    my ( $class, $clientID, $email ) = @_;
    return undef if ( !$clientID || !$email );

    my $dbo = Common::RSApp::GetCommonDB();

    my $sql =
        "SELECT u.* FROM "
        . kTable
        . " u LEFT JOIN user_access ua ON u.user_id=ua.user_id"
        . " WHERE u.email="
        . $dbo->DBQuote($email)
        . " AND (ua.client_id=0 OR ua.client_id=$clientID)";

    my $coll = Common::DB::ItemCollection->new(
        class => "AppUser::DB::Item::User",
        query => $sql,
        dbID  => kDB);

    return ( $coll->hasNext() ) ? $coll->next : undef;
}

sub GetByAccessLevel {
    my ( $class, @levels ) = @_;
    return undef if ( !@levels );

    my $dbo = Common::RSApp::GetCommonDB();

    my $sql =
        "SELECT user.* FROM "
      . kTable
      . " user LEFT JOIN user_access ua on user.user_id=ua.user_id"
      . " WHERE (ua.client_id=0 OR ua.client_id="
      . Common::RSApp::GetClientID() . ")"
      . " AND ua.access_level IN ("
      . join( ',', @levels )
      . ") ORDER BY user.last_name, user.first_name";

    my $collection = Common::DB::ItemCollection->new(
        class => "AppUser::DB::Item::User",
        query => $sql,
        dbID  => kDB,
    );

    return $collection;
}

sub Get {
    my ( $class, %args ) = @_;

    my $order;
    if ( $args{order} ) {
        $order = $args{order};
    } else {
        $order = "user.last_name, user.first_name";
    }

    my $where = "(ua.client_id=0 OR ua.client_id=" . Common::RSApp::GetClientID() . ")";

    # Allow RoyaltyShare access level users to be excluded.
    if ( $args{excludeRS} ) {
        $where .= " AND ua.access_level NOT IN (1,2,9) ";
    }

    # primary filter
    if ( !exists $args{userStatus} ) {                                     # default state
        $where .= " AND user.disabled = 0";
    } elsif ( exists $args{userStatus} && defined $args{userStatus} ) {    # an option has been choosen
        $where .= " AND user.disabled = 0" if $args{userStatus} == 0;
        $where .= " AND user.disabled = 1" if $args{userStatus} == 1;
    }

    # slave filter
    if ( $args{userType} ) {
        $where .= " AND ua.access_level = $args{userType}" if $args{userType} =~ /^\d+$/;
    }

    my $dbo = Common::RSApp::GetCommonDB();

    my $sql = qq/
        SELECT user.*, ua.access_level
        FROM ${\kTable} user
        LEFT JOIN user_access ua ON user.user_id=ua.user_id
        WHERE $where
        ORDER BY $order
    /;

    return $class->SUPER::GetAll($sql);
}

sub Search {
    my ( $class, $searchTerm, $excludeRSFlag ) = @_;

    my $whereSearch;
    my $dbo = Common::RSApp::GetCommonDB();

    if ($searchTerm) {
        $whereSearch = "AND ("
          . $class->SearchSyntax( field => "first_name", value => $searchTerm ) . " OR "
          . $class->SearchSyntax( field => "last_name",  value => $searchTerm ) . " OR "
          . $class->SearchSyntax( field => "email",      value => $searchTerm ) . ")";
    } else {
        die "no valid param passed to album search";
    }

    if ($excludeRSFlag) {
        $whereSearch .= " AND ua.access_level NOT IN (1,2,9) ";
    }

    my $sql =
        "SELECT user.* FROM "
      . kTable
      . " user LEFT JOIN user_access ua on user.user_id=ua.user_id"
      . " WHERE (ua.client_id=0 OR ua.client_id="
      . Common::RSApp::GetClientID() . ")"
      . " $whereSearch ORDER BY user.last_name, user.first_name";

    return $class->SUPER::GetAll($sql);
}

1;