include qw/Rex::Database::MySQL::Admin/;

use Rex::Logger;
use Data::Dumper;
use Time::Piece;
use IO::File;

set mysql => user => 'root';

user "root";
password "root_password";

group marketing_servers => qw/
    xx.xx.xx.xx
    xx.xx.xxx.x
    xxx.xx.xx.x
/;

group test => "xx.xx.xx.xx";

desc "Get the timestamp when the users logged in for the last time";
task last_logged_in => sub {
    my $connection = connection();
    my $ip         = $connection->{server}->{name};

    Rex::Logger::info( $ip );

    my $result = Rex::Database::MySQL::Admin::execute({
        schema => 'otrs',
        sql    => qq~
            SELECT u.login, p.preferences_value
            FROM user_preferences p
                LEFT OUTER JOIN users u
                    ON p.user_id = u.id
            WHERE preferences_key = 'UserLastLogin'
        ~,
    });

    my @lines = split /\n/, $result;
    shift @lines;

    for my $line ( @lines ) {
        my ($user, $time) = split /\s+/, $line;

        my $date = localtime $time;

        my $fh = IO::File->new( './logged_in.csv', 'a' );
        $fh->say( join ';', map{ qq~"$_"~ }($ip, $user, $date->dmy('.') . ' ' . $date->hms()) );
        $fh->close;
    }

    Rex::Logger::info( "ok" );
};