#!/usr/bin/perl
#
# User audit report builder.
#
use strict;
use utf8;
use Getopt::Long;
use File::Path qw(make_path);
use File::Spec;
use Time::Local;
use Time::HiRes qw(time);

require "/usr/local/teamfile/www/bin/mt-lib.pl";

my $AUDIT_ROOT = '/var/lib/teamfile/audit';
my $CONFIG_FILE = $AUDIT_ROOT . '/userauditor.conf';
my $LOCK_FILE = getLogPath() . '/userauditor.pid';
my $LOG_FILE  = getJobsWorkPath() . '/userauditor.log';

my $F_LOG = undef;
my $opt_h = undef;
my $opt_v = undef;
my $opt_f = undef;
my $opt_hour = undef;
my $opt_min = undef;
my $opt_target_date = undef;
my $opt_location = undef;

my $DEFAULT_HOUR = 3;
my $DEFAULT_MIN = 20;
my $UNUSED_DAYS = 90;
my $ALLOWHOSTS_MAJOR_RATE = 80;
my $AUDIT_OWNER = 'teamfile';
my %EXCLUDED_USERS = (
	'._guest_' => 1,
	'._sysexec_' => 1,
);
my $PSQL = '/usr/local/teamfile/tfpgsql/bin/psql';

my $ret = GetOptions(
	"h" => \$opt_h,
	"v" => \$opt_v,
	"f" => \$opt_f,
	"hour=i" => \$opt_hour,
	"min=i" => \$opt_min,
	"target-date=s" => \$opt_target_date,
	"location=s" => \$opt_location,
);
if (!$ret || $opt_h) {
	usage();
	exit($ret ? 0 : 1);
}
chdir('/');

exit(main());

sub main {
	ensure_audit_config();
	my %conf = read_audit_config();
	my $hour = defined($opt_hour) ? $opt_hour : ($conf{hour} || $DEFAULT_HOUR);
	my $min = defined($opt_min) ? $opt_min : ($conf{min} || $DEFAULT_MIN);
	my $target_date = normalize_target_date($opt_target_date || $conf{target_date} || yesterday_ymd());
	my $now = time;

	if (!$opt_f && !isItTimeToRunJob($now, $hour, $min)) {
		printerr("Skipped. schedule=${hour}:${min}\n");
		return 0;
	}

	my $cRetry = 0;
	while (!isRunning(getApachePidPath())) {
		sleep 1;
		if ($cRetry++ >= 30) {
			printerr("Apache had been stopped. we give up execution of user auditor.\n");
			return 1;
		}
	}

	if ((my $pid = lockScript($LOCK_FILE)) != 0) {
		printerr("This script had been already started by $pid.\n");
		return 1;
	}

	initLog();
	ensure_audit_dir($AUDIT_ROOT);

	my %gparams = readTfGlobalSettings();
	my @lcfiles = getLocationConflist();
	my $ok_count = 0;
	my $fail_count = 0;

	for my $lcfile (@lcfiles) {
		my %lcparams = parseLocationConf($lcfile, \%gparams);
		next if !%lcparams || !$lcparams{Location};
		next if $opt_location && $opt_location ne $lcparams{Location};

		my %dbparams = parse_db_location_conf($lcfile);
		my $ok = audit_location(\%lcparams, \%dbparams, $target_date);
		if ($ok) {
			$ok_count++;
		}
		else {
			$fail_count++;
		}
	}

	writeLog("Finished user auditor. ok=$ok_count failed=$fail_count target_date=$target_date");
	closeLog();
	unlockScript($LOCK_FILE);
	return $fail_count ? 1 : 0;
}

sub audit_location {
	my ($lcparams_ref, $dbparams_ref, $target_date) = @_;
	my %lcparams = %{$lcparams_ref || {}};
	my %dbparams = %{$dbparams_ref || {}};
	my $location = $lcparams{Location} || '';
	my $started_at = time;

	if (!$location) {
		writeLog("Skipped location because location name is missing.");
		return 0;
	}
	if (!has_db_config(\%dbparams)) {
		write_error($location, "Database configuration is incomplete.");
		return 0;
	}

	writeLog("Start user audit. location=$location target_date=$target_date");

	my @users = eval { query_users(\%dbparams) };
	if ($@) {
		my $err = $@;
		chomp($err);
		write_error($location, "Database query failed. $err");
		return 0;
	}
	if (!@users) {
		write_error($location, "No users were loaded from database.");
		return 0;
	}

	my $target_epoch = ymd_to_epoch_end($target_date);
	my @unused_users;
	my @allowhosts_missing_users;
	my @admin_allowhosts_missing_users;
	my @issues;
	my $total_count = 0;
	my $active_count = 0;
	my $allowhosts_count = 0;

	for my $user (@users) {
		next if is_excluded_user($user->{userid});
		$total_count++;
		next if !$user->{active};
		$active_count++;

		my $allowhosts = normalize_allowhosts($user->{allowhosts} || '');
		my $has_allowhosts = ($allowhosts ne '') ? 1 : 0;
		$allowhosts_count++ if $has_allowhosts;

		my $last_epoch = $user->{lastaccess_epoch} || 0;
		my $unused_days = $last_epoch ? int(($target_epoch - $last_epoch) / (24 * 3600)) : undef;

		if (!$last_epoch || $unused_days >= $UNUSED_DAYS) {
			push @unused_users, user_item($user, {
				reason => !$last_epoch ? 'no_last_access' : 'unused_days',
				unused_days => defined($unused_days) ? $unused_days : undef,
				allowhosts => $allowhosts,
			});
		}

		if (!$has_allowhosts) {
			my $item = user_item($user, {
				reason => 'allowhosts_missing',
				allowhosts => '',
			});
			push @allowhosts_missing_users, $item;
			if ($user->{usertype} eq 'admin' || $user->{usertype} eq 'groupleader') {
				push @admin_allowhosts_missing_users, $item;
			}
		}
	}

	my $allowhosts_rate = ($active_count > 0) ? int(($allowhosts_count * 100 + ($active_count / 2)) / $active_count) : 0;

	if (@admin_allowhosts_missing_users) {
		push @issues, issue_item('critical', 'admin_allowhosts_missing', scalar(@admin_allowhosts_missing_users) . ' administrator or group leader users have no IP restriction.');
	}
	if ($allowhosts_rate >= $ALLOWHOSTS_MAJOR_RATE && @allowhosts_missing_users) {
		push @issues, issue_item('warn', 'allowhosts_major_missing', 'IP restriction rate is ' . $allowhosts_rate . '%, but ' . scalar(@allowhosts_missing_users) . ' active users have no IP restriction.');
	}
	if (@unused_users) {
		push @issues, issue_item('caution', 'unused_users', scalar(@unused_users) . ' active users have no access for ' . $UNUSED_DAYS . '+ days or have unknown last access.');
	}
	if (!@issues) {
		push @issues, issue_item('ok', 'no_problem', 'No major findings.');
	}

	my $elapsed_ms = int((time - $started_at) * 1000);
	my $result = {
		version => 1,
		type => 'user-audit',
		location => $location,
		generated_at => iso8601_now(),
		target_date => $target_date,
		elapsed_ms => $elapsed_ms,
		status => 'ok',
		rules => {
			unused_days => $UNUSED_DAYS,
			allowhosts_major_rate => $ALLOWHOSTS_MAJOR_RATE,
		},
		summary => {
			total_users => $total_count,
			active_users => $active_count,
			allowhosts_users => $allowhosts_count,
			allowhosts_rate => $allowhosts_rate,
			unused_users => scalar(@unused_users),
			allowhosts_missing_users => scalar(@allowhosts_missing_users),
			admin_allowhosts_missing_users => scalar(@admin_allowhosts_missing_users),
		},
		issues => \@issues,
		details => {
			unused_users => \@unused_users,
			allowhosts_missing_users => \@allowhosts_missing_users,
			admin_allowhosts_missing_users => \@admin_allowhosts_missing_users,
		},
	};

	write_result($location, $result);
	writeLog("Finished user audit. location=$location active=$active_count allowhosts_rate=$allowhosts_rate elapsed_ms=$elapsed_ms");
	return 1;
}

sub parse_db_location_conf {
	my $lcfile = shift || '';
	my %params;
	my $path = File::Spec->catfile(getConfigPath(), $lcfile);
	return %params if !-f $path;

	if (open(my $fh, '<', $path)) {
		while (my $line = <$fh>) {
			next if $line =~ /^[ \t]*#/;
			if ($line =~ /^[ \t]*TfDbDbmstype[ \t]+(.+?)\s*$/) {
				$params{dbmstype} = $1;
			}
			elsif ($line =~ /^[ \t]*TfDbHostname[ \t]+(.+?)\s*$/) {
				$params{hostname} = $1;
			}
			elsif ($line =~ /^[ \t]*TfDbHostport[ \t]+(.+?)\s*$/) {
				$params{hostport} = $1;
			}
			elsif ($line =~ /^[ \t]*TfDbDbname[ \t]+(.+?)\s*$/) {
				$params{dbname} = $1;
			}
			elsif ($line =~ /^[ \t]*TfDbUsername[ \t]+(.+?)\s*$/) {
				$params{username} = $1;
			}
			elsif ($line =~ /^[ \t]*TfDbPassword[ \t]+(.+?)\s*$/) {
				$params{password} = $1;
			}
		}
		close($fh);
	}

	return %params;
}

sub has_db_config {
	my $dbparams_ref = shift || {};
	my %dbparams = %{$dbparams_ref};
	return 0 if ($dbparams{dbmstype} || '') ne 'pg';
	return $dbparams{hostname} && $dbparams{hostport} && $dbparams{dbname} && $dbparams{username};
}

sub query_users {
	my $dbparams_ref = shift || {};
	my %dbparams = %{$dbparams_ref};
	die "$PSQL was not found." if !-x $PSQL;

	my $sql = <<"_SQL_";
SELECT
  usr_usr_id_vc,
  replace(replace(COALESCE(usr_fullname_vc, ''), E'\\t', ' '), E'\\n', ' '),
  replace(replace(COALESCE(usr_mailaddr_vc, ''), E'\\t', ' '), E'\\n', ' '),
  COALESCE(usr_admin_mod_i, 0),
  COALESCE(usr_extended_status_c, ''),
  replace(replace(COALESCE(usr_allowhosts_vc, ''), E'\\t', ' '), E'\\n', ' '),
  COALESCE(usr_last_access_bi, 0),
  replace(replace(COALESCE(usr_last_accesscl_vc, ''), E'\\t', ' '), E'\\n', ' '),
  COALESCE(usr_regist_c, ''),
  COALESCE(usr_update_c, '')
FROM divy_usr
ORDER BY usr_usr_id_vc
_SQL_

	local $ENV{PGPASSWORD} = $dbparams{password} || '';
	local $ENV{PGCLIENTENCODING} = 'UTF8';

	open(my $fh, '-|',
		$PSQL,
		'-h', $dbparams{hostname},
		'-p', $dbparams{hostport},
		'-d', $dbparams{dbname},
		'-U', $dbparams{username},
		'-At',
		'-F', "\t",
		'-c', $sql) or die "Failed to execute psql: $!";

	my @users;
	while (my $line = <$fh>) {
		chomp($line);
		my @cols = split(/\t/, $line, -1);
		next if !defined($cols[0]) || $cols[0] eq '';
		push @users, build_user_from_db_row(@cols);
	}
	close($fh);
	if ($? != 0) {
		die "psql failed. ret=$?";
	}

	return @users;
}

sub build_user_from_db_row {
	my @cols = @_;
	my $userid = $cols[0] || '';
	my $name = $cols[1] || '';
	my $mailaddr = $cols[2] || '';
	my $adminmode = $cols[3] || 0;
	my $extstatus = $cols[4] || 'a--wvt-----*********************';
	my $allowhosts = $cols[5] || '';
	my $lastaccess = $cols[6] || 0;
	my $lastaccessclient = $cols[7] || '';
	my $regist = $cols[8] || '';
	my $update = $cols[9] || '';
	my $active = extstatus_on($extstatus, 0, 'a') ? 1 : 0;
	my $groupleader = extstatus_on($extstatus, 8, 'L') ? 1 : 0;
	my $usertype = $adminmode == 1 ? 'admin' : ($groupleader ? 'groupleader' : 'normal');

	return {
		userid => $userid,
		name => $name,
		mailaddr => $mailaddr,
		usertype => $usertype,
		active => $active,
		allowhosts => $allowhosts,
		lastaccess_epoch => $lastaccess,
		lastaccessdt => epoch_to_iso8601($lastaccess),
		lastaccessclient => $lastaccessclient,
		regist => $regist,
		update => $update,
	};
}

sub is_excluded_user {
	my $userid = shift || '';
	return $EXCLUDED_USERS{$userid} ? 1 : 0;
}

sub extstatus_on {
	my ($extstatus, $index, $on_char) = @_;
	return 0 if !defined($extstatus) || length($extstatus) <= $index;
	return substr($extstatus, $index, 1) eq $on_char;
}

sub write_result {
	my ($location, $result_ref) = @_;
	my $dir = location_audit_dir($location);
	ensure_audit_dir($dir);

	my $tmp = File::Spec->catfile($dir, 'latest-user-audit.json.tmp.' . $$);
	my $out = File::Spec->catfile($dir, 'latest-user-audit.json');

	open(my $fh, '>', $tmp) or die "Failed to open $tmp: $!";
	print $fh to_json($result_ref);
	close($fh);
	set_audit_path_attrs($tmp, 0);
	rename($tmp, $out) or die "Failed to rename $tmp to $out: $!";
	set_audit_path_attrs($out, 0);
	my $err = File::Spec->catfile($dir, 'latest-user-audit.err');
	unlink($err) if -f $err;
}

sub write_error {
	my ($location, $message) = @_;
	my $dir = location_audit_dir($location || 'unknown');
	ensure_audit_dir($dir);
	my $err = File::Spec->catfile($dir, 'latest-user-audit.err');
	if (open(my $fh, '>', $err)) {
		print $fh iso8601_now() . ' ' . ($message || '') . "\n";
		close($fh);
		set_audit_path_attrs($err, 0);
	}
	writeLog("ERROR location=$location $message");
}

sub location_audit_dir {
	my $location = shift || 'unknown';
	$location =~ s/[^A-Za-z0-9_-]/_/g;
	return File::Spec->catdir($AUDIT_ROOT, $location);
}

sub user_item {
	my ($user_ref, $extra_ref) = @_;
	my %user = %{$user_ref || {}};
	my %extra = %{$extra_ref || {}};
	return {
		userid => $user{userid} || '',
		name => $user{name} || '',
		mailaddr => $user{mailaddr} || '',
		usertype => $user{usertype} || '',
		state => $user{active} ? 'active' : 'inactive',
		reason => $extra{reason} || '',
		unused_days => $extra{unused_days},
		lastaccessdt => $user{lastaccessdt} || '',
		lastaccessclient => $user{lastaccessclient} || '',
		allowhosts => $extra{allowhosts} || '',
	};
}

sub issue_item {
	my ($level, $code, $message) = @_;
	return {
		level => $level || '',
		code => $code || '',
		message => $message || '',
	};
}

sub read_audit_config {
	my %conf;
	return %conf if !-f $CONFIG_FILE;

	if (open(my $fh, '<', $CONFIG_FILE)) {
		while (my $line = <$fh>) {
			chomp($line);
			next if $line =~ /^\s*#/ || $line =~ /^\s*$/;
			if ($line =~ /^\s*([A-Za-z0-9_-]+)\s*=\s*(.*?)\s*$/) {
				$conf{$1} = $2;
			}
		}
		close($fh);
	}
	return %conf;
}

sub ensure_audit_config {
	ensure_audit_dir($AUDIT_ROOT);
	return if -f $CONFIG_FILE;

	if (open(my $fh, '>', $CONFIG_FILE)) {
		print $fh "# TeamFile user audit settings\n";
		print $fh "# hour/min define the daily audit execution time.\n";
		print $fh "hour=$DEFAULT_HOUR\n";
		print $fh "min=$DEFAULT_MIN\n";
		print $fh "# Optional fixed target date for testing: YYYY-MM-DD\n";
		print $fh "# target_date=\n";
		close($fh);
		set_audit_path_attrs($CONFIG_FILE, 0);
	}
}

sub ensure_audit_dir {
	my $dir = shift || '';
	return if !$dir;
	make_path($dir) if !-d $dir;
	set_audit_path_attrs($dir, 1);
}

sub set_audit_path_attrs {
	my ($path, $is_dir) = @_;
	return if !$path;
	my ($uid, $gid) = (getpwnam($AUDIT_OWNER))[2, 3];
	chown($uid, $gid, $path) if defined($uid) && defined($gid);
	chmod($is_dir ? 0750 : 0640, $path);
}

sub normalize_allowhosts {
	my $allowhosts = shift || '';
	$allowhosts =~ s/^\s+//;
	$allowhosts =~ s/\s+$//;
	$allowhosts =~ s/\s*,\s*/, /g;
	return $allowhosts;
}

sub normalize_target_date {
	my $date = shift || '';
	if ($date =~ /^(\d{4})-(\d{2})-(\d{2})$/) {
		return "$1-$2-$3";
	}
	if ($date =~ /^(\d{4})(\d{2})(\d{2})$/) {
		return "$1-$2-$3";
	}
	return yesterday_ymd();
}

sub yesterday_ymd {
	my ($sec, $min, $hour, $mday, $mon, $year) = localtime(time - 24 * 3600);
	return sprintf('%04d-%02d-%02d', $year + 1900, $mon + 1, $mday);
}

sub ymd_to_epoch_end {
	my $date = shift || yesterday_ymd();
	my ($y, $m, $d) = ($date =~ /^(\d{4})-(\d{2})-(\d{2})$/);
	return timelocal(59, 59, 23, $d, $m - 1, $y - 1900);
}

sub epoch_to_iso8601 {
	my $epoch = shift || 0;
	return '' if !$epoch || $epoch !~ /^\d+$/;
	my ($sec, $min, $hour, $mday, $mon, $year) = localtime($epoch);
	return sprintf('%04d-%02d-%02dT%02d:%02d:%02d', $year + 1900, $mon + 1, $mday, $hour, $min, $sec);
}

sub iso8601_now {
	my ($sec, $min, $hour, $mday, $mon, $year) = localtime(time);
	return sprintf('%04d-%02d-%02dT%02d:%02d:%02d', $year + 1900, $mon + 1, $mday, $hour, $min, $sec);
}

sub to_json {
	my $v = shift;
	if (!defined($v)) {
		return 'null';
	}
	if (ref($v) eq 'HASH') {
		return '{' . join(',', map { json_string($_) . ':' . to_json($v->{$_}) } sort keys %{$v}) . '}';
	}
	if (ref($v) eq 'ARRAY') {
		return '[' . join(',', map { to_json($_) } @{$v}) . ']';
	}
	if (!ref($v) && $v =~ /^-?\d+(?:\.\d+)?$/) {
		return $v;
	}
	return json_string($v);
}

sub json_string {
	my $s = shift;
	$s = '' if !defined($s);
	$s =~ s/\\/\\\\/g;
	$s =~ s/"/\\"/g;
	$s =~ s/\n/\\n/g;
	$s =~ s/\r/\\r/g;
	$s =~ s/\t/\\t/g;
	return '"' . $s . '"';
}

sub usage {
	print <<"_EOF_";
Usage: userauditor [-v] [-f] [--hour H --min M] [--target-date YYYY-MM-DD] [--location NAME] | [-h]
       -v                  Show detail status
       -f                  Force execution regardless of configured time
       --hour H --min M    Scheduled execution time (default: 03:20)
       --target-date DATE  Audit target date. Default is yesterday.
       --location NAME     Audit only one location
       -h                  Show usage
_EOF_
}

sub printerr {
	my $msg = shift || '';
	print STDERR $msg if $opt_v && $msg;
}

sub initLog {
	make_path(getJobsWorkPath()) if !-d getJobsWorkPath();
	open $F_LOG, ">", $LOG_FILE;
}

sub writeLog {
	my $msg = shift || '';
	return if !$msg;
	my $ctime = localtime(time);
	print $F_LOG $ctime . ' : ' . $msg . "\n" if $F_LOG;
	printerr($msg . "\n");
}

sub closeLog {
	close $F_LOG if $F_LOG;
}
